[SR-Users] Cockroachdb and kamailio 5.4
Daniel-Constantin Mierla
miconda at gmail.com
Mon Mar 1 11:57:05 CET 2021
Hello,
probably the rand() function was used in the past by kamctl, likely
never was used from the C code.
The kamctl lcr has no longer the add/insert subcommand, so I think the
postgres/sqlite/... rand() function can be removed. If it is discovered
that is needed, it can be added back.
Not sure where concat() was used, but could be (re-)evaluated and
removed if no longer necessary.
Anyhow, most of the modules do not need these sql functions, so one can
create tables one by one from the sql scripts in the
utils/kamctl/postgres/ folder.
Cheers,
Daniel
On 01.03.21 10:35, Jonathan Hunter wrote:
>
> Noah,
>
>
>
> Completely understand.
>
>
>
> Good news however is with 5.4 and secure cockroach, it works for my
> requirements with the postgres api for location and subscriber table
> interaction, having created tables using scripts.
>
>
>
> I will be using an API for other routing, and I can make sure of
> permissions and dispatcher files whilst in a containerised environment.
>
>
>
> Will let you know if I run into any other issues. Thanks again!
>
>
>
> Jon
>
>
>
> Sent from Mail <https://go.microsoft.com/fwlink/?LinkId=550986> for
> Windows 10
>
>
>
> *From: *Noah Mehl <mailto:noahmehl at gmail.com>
> *Sent: *26 February 2021 14:17
> *To: *Jonathan Hunter <mailto:hunterj91 at hotmail.com>
> *Cc: *Kamailio (SER) - Users Mailing List
> <mailto:sr-users at lists.kamailio.org>; Henning Westerholt
> <mailto:hw at skalatan.de>
> *Subject: *Re: Cockroachdb and kamailio 5.4
>
>
>
> Jon,
>
>
>
> Realistically, this is alpha functionality, at best. If you, and/or
> your organization, do not have the expertise, resources, and/or risk
> tolerance for this, I couldn’t in good faith recommend that you do it.
>
>
>
> There are many other clustering technologies for MySQL and PostgreSQL
> that are mature, and *should* be compatible with the existing
> implementation. Perhaps someone on the list can let us know if
> they’re using one of the following:
>
>
>
> - MySQL Galera
>
> - PostgreSQL replication (either using Pacemaker/Corosync or Zookeeper
> to provide automated failover and/or Master/Master)
>
> - Vitess: https://github.com/vitessio/vitess
> <https://emea01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fvitessio%2Fvitess&data=04%7C01%7C%7C50369d445ce04ed280d608d8da614417%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637499458574111672%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=pPBfQYkCw801LNTrAj%2FxouPuUH7cClWm1YeyeR9YKAA%3D&reserved=0>
>
> - Some other solution not listed here?
>
>
>
> That being said, I’ve spend as much time on this as I can. I solved
> the permissions module issue in this way:
>
>
>
> *diff --git a/utils/kamctl/postgres/permissions-create.sql
> b/utils/kamctl/postgres/permissions-create.sql*
>
> *index f397ca22f4..66de10ddcb 100644*
>
> *--- a/utils/kamctl/postgres/permissions-create.sql*
>
> *+++ b/utils/kamctl/postgres/permissions-create.sql*
>
> @@ -20,10 +20,10 @@CREATE SEQUENCE address_id_seq;
>
>
>
> CREATE TABLE address (
>
> id integer PRIMARY KEY NOT NULL DEFAULT nextval('address_id_seq'),
>
> - grp INTEGER DEFAULT 1 NOT NULL,
>
> + grp INT4 DEFAULT 1 NOT NULL,
>
> ip_addr VARCHAR(50) NOT NULL,
>
> - mask INTEGER DEFAULT 32 NOT NULL,
>
> - port SMALLINT DEFAULT 0 NOT NULL,
>
> + mask INT4 DEFAULT 32 NOT NULL,
>
> + port INT2 DEFAULT 0 NOT NULL,
>
> tag VARCHAR(64)
>
> );
>
>
>
> *diff --git a/src/modules/db_postgres/km_res.c
> b/src/modules/db_postgres/km_res.c*
>
> *index 13ac138c1a..f4813cafcc 100644*
>
> *--- a/src/modules/db_postgres/km_res.c*
>
> *+++ b/src/modules/db_postgres/km_res.c*
>
> @@ -151,11 +151,11 @@int db_postgres_get_columns(const db1_con_t *_h,
> db1_res_t *_r)
>
> case VARCHAROID:
>
> case NAMEOID:
>
> case BPCHAROID:
>
> + case TEXTOID:
>
> LM_DBG("use DB1_STRING result type\n");
>
> RES_TYPES(_r)[col] = DB1_STRING;
>
> break;
>
>
>
> - case TEXTOID:
>
> case BYTEAOID:
>
> LM_DBG("use DB1_BLOB result type\n");
>
> RES_TYPES(_r)[col] = DB1_BLOB;
>
>
>
> For the schema update, it becomes CockroachDB specific, hence the need
> to start creating CockroachDB specific migration files. For the
> km_res.c change, this is where things are probably not ideal. I’m
> hoping that Henning and/or Daniel can respond. Essentially,
> CockroachDB stores all character/string datatypes the same way, and
> then presents this as a TEXTOID datatype. I think the problem with
> moving this to the DB1_STRING type is that we *could* have memory
> overruns? That’s just my guess. Maybe it’s completely safe?
>
>
>
> Thanks!
>
>
>
> ~Noah
>
>
>
> On Feb 26, 2021, at 5:15 AM, Jonathan Hunter
> <hunterj91 at hotmail.com <mailto:hunterj91 at hotmail.com>> wrote:
>
>
>
> Hi Noah,
>
>
>
> Hope you are well?
>
>
>
> Realistically do you think you will be able to spend some time on
> this or do you think I need to look at other options in the short
> term?
>
>
>
> I guess the primary requirements for me are being able to use
> these modules which require database interaction (mainly
> Permissions,userloc,dispatcher).
>
>
>
> I have started to work through it but I am pondering if in the
> short term I need to focus on a support database and look to
> migrate when available.
>
>
>
> 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%7C50369d445ce04ed280d608d8da614417%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637499458574121672%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=7KtoBKFV2TYoeRZzQegxwRd%2Fevy4eKoJ4OESn8HevU4%3D&reserved=0> for
> Windows 10
>
>
>
> *From: *Jonathan Hunter <mailto:hunterj91 at hotmail.com>
> *Sent: *22 February 2021 11:20
> *To: *Henning Westerholt <mailto:hw at skalatan.de>; Noah Mehl
> <mailto:noahmehl at gmail.com>
> *Cc: *Kamailio (SER) - Users Mailing List
> <mailto:sr-users at lists.kamailio.org>
> *Subject: *Re: [SR-Users] Cockroachdb and kamailio 5.4
>
>
>
> Hi Noah and Henning,
>
>
>
> Thank you for your responses, I am currently digesting them!
>
>
>
> If I can be of any help testing/working on this please let me know
> as Im very keen to implement it so will review and also happy to
> take direction as I will be testing this week on it.
>
>
>
> Thanks again
>
>
>
> 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%7C50369d445ce04ed280d608d8da614417%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637499458574121672%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=7KtoBKFV2TYoeRZzQegxwRd%2Fevy4eKoJ4OESn8HevU4%3D&reserved=0> for
> Windows 10
>
>
>
> *From: *Henning Westerholt <mailto:hw at skalatan.de>
> *Sent: *22 February 2021 07:44
> *To: *Noah Mehl <mailto:noahmehl at gmail.com>; Jonathan Hunter
> <mailto:hunterj91 at hotmail.com>
> *Cc: *Kamailio (SER) - Users Mailing List
> <mailto:sr-users at lists.kamailio.org>
> *Subject: *RE: Cockroachdb and kamailio 5.4
>
>
>
> Hi Noah,
>
>
>
> sure – let me give you some pointers. So basically, the SQL files
> are generated from the XSL infrastructure in the quoted directory.
> This file e.g. is for postgres:
>
> https://github.com/kamailio/kamailio/blob/5.4/doc/stylesheets/dbschema/xsl/postgres.xsl
> <https://emea01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fkamailio%2Fkamailio%2Fblob%2F5.4%2Fdoc%2Fstylesheets%2Fdbschema%2Fxsl%2Fpostgres.xsl&data=04%7C01%7C%7C50369d445ce04ed280d608d8da614417%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637499458574131660%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=y2muH6O94jI47TjjIFm9M6pzRchGO6oiq1YBo%2BD8pv8%3D&reserved=0>
>
>
>
> You basically need to copy it to a new file and adapt the types in
> it to the cockroachdb types. If you execute “make dbschema” in the
> kamailio source tree, it will generate all the SQL files. Then you
> could generate the appropriate SQL files also for your database
> and it will stay in sync after future changes. There might be also
> a small extension necessary in the Makefile, but we can have a
> look to this later on.
>
>
>
> About the questions why the SQL files are then also checked in
> after creation – because otherwise everybody needs to install the
> xstl dependencies just for installing Kamailio.
>
>
>
> About the rand()/random() topic – I did not find anything in the
> LCR module as well. It might be obsolete. I would consider
> dropping this, maybe after asking on the sr-dev list for this again.
>
>
>
> 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%7C50369d445ce04ed280d608d8da614417%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637499458574131660%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=ukJaXXYyg8bKjIdbAkbVGb60iI5HwlSfa7X7Nx7pvYI%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%7C50369d445ce04ed280d608d8da614417%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637499458574141653%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=vfm8oizLUW7eFUVDSMiq8jrQP2kOI9hDroRbWgiPazI%3D&reserved=0>
>
>
>
> *From:* Noah Mehl <noahmehl at gmail.com <mailto:noahmehl at gmail.com>>
> *Sent:* Sunday, February 21, 2021 8:47 PM
> *To:* Jonathan Hunter <hunterj91 at hotmail.com
> <mailto:hunterj91 at hotmail.com>>
> *Cc:* Henning Westerholt <hw at skalatan.de <mailto:hw at skalatan.de>>;
> Kamailio (SER) - Users Mailing List <sr-users at lists.kamailio.org
> <mailto:sr-users at lists.kamailio.org>>
> *Subject:* Re: Cockroachdb and kamailio 5.4
>
>
>
> 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://emea01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fkamailio%2Fkamailio%2Ftree%2F5.4%2Fdoc%2Fstylesheets%2Fdbschema&data=04%7C01%7C%7C50369d445ce04ed280d608d8da614417%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637499458574151647%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=H%2Fq%2BAZuqIhA1KjV0EjurWTjkgmOKboZ25Q0sUhf9XAU%3D&reserved=0> 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://emea01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fkamailio%2Fkamailio%2Fcompare%2F5.4...reperio%3Acockroachdb-compat%3Fexpand%3D1&data=04%7C01%7C%7C50369d445ce04ed280d608d8da614417%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637499458574151647%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=HeXGbp24Rc7lYLtmB0CLLFNSW26S9NWJBTbUopiXKBc%3D&reserved=0>:
>
>
>
> 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 <mailto: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://emea01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgo.microsoft.com%2Ffwlink%2F%3FLinkId%3D550986&data=04%7C01%7C%7C50369d445ce04ed280d608d8da614417%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637499458574161650%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=qbDLutqGaqHUQVXK99dKn8xVjyIGIQBp%2BewB780dI54%3D&reserved=0> 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%7C50369d445ce04ed280d608d8da614417%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637499458574161650%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=YW5KC1GPUviXFbfdWAgnoVyIAm8EGrYvUgbW9yNiJkk%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%7C50369d445ce04ed280d608d8da614417%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637499458574171639%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=xrehnLzn8FxOyzYWAlhzHIyr%2Br03fKaQe9e9hSdOCuI%3D&reserved=0>
>
>
>
> *From:* sr-users <sr-users-bounces at lists.kamailio.org
> <mailto: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 <mailto: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%7C50369d445ce04ed280d608d8da614417%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637499458574171639%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=vzc8t3QW2%2Bz5Jp9Bax1jgRqqJH15vfT46mk4ocPRpxQ%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%7C50369d445ce04ed280d608d8da614417%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637499458574181632%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=%2FbskNnL14N2kesmKC9R3YFQWSV%2FgTT8J6d7BDd3W50g%3D&reserved=0> for
> Windows 10
>
>
>
>
>
>
> _______________________________________________
> Kamailio (SER) - Users Mailing List
> sr-users at lists.kamailio.org
> https://lists.kamailio.org/cgi-bin/mailman/listinfo/sr-users
--
Daniel-Constantin Mierla -- www.asipto.com
www.twitter.com/miconda -- www.linkedin.com/in/miconda
Funding: https://www.paypal.me/dcmierla
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.kamailio.org/pipermail/sr-users/attachments/20210301/3f996ff9/attachment.htm>
More information about the sr-users
mailing list