[SR-Users] Cockroachdb and kamailio 5.4

[Noah Mehl]

Jon,

I’m not sure what would get my branch accepted.  Henning mentioned on 9/16/2020 that the .sql files are generated from XML/XLST scripts, I have found: https://github.com/kamailio/kamailio/tree/5.4/doc/stylesheets/dbschema <https://github.com/kamailio/kamailio/tree/5.4/doc/stylesheets/dbschema> which was updated just 3 days ago.  However, I don’t understand how this is used to generate the .sql files for Postgres. I’m also confused as to why the .sql files are checked into the repository if they’re generated?  Henning, can you point me in the right direction?

That being said, this is what’s changed in the branch <https://github.com/kamailio/kamailio/compare/5.4...reperio:cockroachdb-compat?expand=1>:

kamdbctl.pgsql

- I’ve updated the psql command for my preferences regarding output
- I’ve made the function checking more verbose
- I’ve added the gen_random_uuid() function (by adding pgcrypto to Postgres, it’s native in CockroachDB), this isn’t required, but we are using UUID for usrloc in production
- I’ve updated the GRANT commands so they’re compatible with both Postgres and CockroachDB

Some things to note:

- concat() is native to CockroachDB, so the CREATE FUNCTION is only necessary for Postgres
- rand() is the native function name in MySQL, but random() is the function name in Postgres and CockroachDB.  This is where I’m most concerned because the file says it’s used in the lcr module, but I cannot find where it is used.  Does anyone know how to ascertain this?  Anyways, they’re the same function, so it’s a little silly to require a CREATE FUNCTION duplicating the exact functionality of an existing native function.

The rest of the changes have to do with modifying the create statements to not use SERIAL, but use the more verbose SEQUENCE + nextval().  It’s identical in practice, so there’s 0 risk there.

I think overall risk is low for the branch, as the branch only changes the utility that creates the DBs.  As for production use, it’s worked great for us, there have been no issues.

~Noah

> On Feb 18, 2021, at 2:45 PM, Jonathan Hunter <hunterj91 at hotmail.com> wrote:
> 
> Hi Noah,
>  
> Hope you are well?
>  
> I work as a consultant for a company in the UK, and I am building a new hosted telephony platform for them in docker initially, and as we are deploying across multiple servers they want to use cockroachdb to allow easy management of a cluster environment.
>  
> I could see from your posts you got it working using your own branch, and I wondered what changes you made to make things work correctly and what would be needed to get the kamailio dev’s to accept it into the main stream of code? (Happy to help here where I can).
>  
> Unless you will maintain your branch forever 😊 Or are the changes small so its not too much of a concern? I just want to assess the risk really, and like you I think mainly we will just be using userloc and dispatcher for DB interaction so its positive to hear they work fine.  We would also be using rtpengine, routing data will be done via API so that should be fine. FYI Id like to run kamailio 5.4 initially.
>  
> I see your comments about table creation (in previous posts), that is the initial problem I am seeing when trying to use postgres based commands direct from a pgdump to create the kamailio database structure, does your branch contain all the creation scripts for the db/tables that I can use for testing?
>  
> Thanks again in advance  for the response!
>  
> Jon
>  
> Sent from Mail <https://go.microsoft.com/fwlink/?LinkId=550986> for Windows 10
>  
> From: Henning Westerholt <mailto:hw at skalatan.de>
> Sent: 17 February 2021 16:18
> To: Kamailio (SER) - Users Mailing List <mailto:sr-users at lists.kamailio.org>
> Cc: Jonathan Hunter <mailto:hunterj91 at hotmail.com>; Noah Mehl <mailto:noahmehl at gmail.com>
> Subject: RE: Cockroachdb and kamailio 5.4
>  
> Hi Jonathan,
>  
> no – I do not think that there has been more work done so far, apart from the discussion that you referenced below.
> If you are also interested in getting this forward, why not reaching out to the other guy starting this discussion earlier? Just to see if you can maybe join forces to get something of this work into a pull request for review and later a possible merge into our code base.
>  
> Cheers,
>  
> Henning
>  
> -- 
> Henning Westerholt – https://skalatan.de/blog/ <https://emea01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fskalatan.de%2Fblog%2F&data=04%7C01%7C%7C548d23e7d9544667296a08d8d35fb5d3%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637491755307382972%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=WVL5vzihg1kUYxeDwnDwHPksVaf0EaM6ANXuBBBthZE%3D&reserved=0>
> Kamailio services – https://gilawa.com <https://emea01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgilawa.com%2F&data=04%7C01%7C%7C548d23e7d9544667296a08d8d35fb5d3%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637491755307392967%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=Zkxs8506K%2FpvHX%2FPcHEj3fyYBei%2BnpuzPmpHdtkFfn4%3D&reserved=0>
>  
> From: sr-users <sr-users-bounces at lists.kamailio.org> On Behalf Of Jonathan Hunter
> Sent: Wednesday, February 17, 2021 1:59 PM
> To: Kamailio (SER) - Users Mailing List <sr-users at lists.kamailio.org>
> Subject: [SR-Users] Cockroachdb and kamailio 5.4
>  
> Hi Guys,
>  
> Hope all are well?
>  
> We are looking to implement kamailio with cockroachdb due to the advantages it gives us in terms of postgres management and clustering.
>  
> I can see from a previous string http://sip-router.1086192.n5.nabble.com/CockroachDB-and-Kamailio-td189233.html <https://emea01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fsip-router.1086192.n5.nabble.com%2FCockroachDB-and-Kamailio-td189233.html&data=04%7C01%7C%7C548d23e7d9544667296a08d8d35fb5d3%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637491755307392967%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=8E15g5pgP%2BpY%2BgEz%2FtP%2BaLcv6r4s60hCwk5MV0yGn4I%3D&reserved=0> that someone has attempted this and even created their own branch.
>  
> Has any further work been done on this, and if so has anyone got any advice/tips relating to it? As I am nervous about potentially using a branch and not a general release of kamailio moving forwards.
>  
> I appreciate you cant support all database types but I would just be interested to hear people’s thoughts on the topic.
>  
> Many thanks!
>  
> Jon
>  
> Sent from Mail <https://emea01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgo.microsoft.com%2Ffwlink%2F%3FLinkId%3D550986&data=04%7C01%7C%7C548d23e7d9544667296a08d8d35fb5d3%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637491755307402965%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=tCO%2FU3IF9d01%2Fwc7APzZZvr2kcFiqIsZox3p%2BjwegyM%3D&reserved=0> for Windows 10

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.kamailio.org/pipermail/sr-users/attachments/20210221/def2a873/attachment.htm>