Hi Noah,
today I quickly looked into your branch, thanks for sharing it. I noticed that several
commits are similar (like conversion of SERIAL to SEQUENCE). As you might or might not
know, the .sql files are autogenerated from XML/XSLT scripts. If you want to introduce the
changes into our repository, we should change this instead of the .sql files.
I noticed that there were quite some changes in the files. So maybe a better idea would be
to just leave the PostgreSQL as it is, and create a new target just for cockroachdb,
similar as we have e.g. for Oracle if there is general interest in this DB.
Cheers,
Henning
--
Henning Westerholt –
https://skalatan.de/blog/
Kamailio services –
https://gilawa.com<https://gilawa.com/>
From: Noah Mehl <noahmehl(a)gmail.com>
Sent: Wednesday, August 26, 2020 3:49 PM
To: miconda(a)gmail.com
Cc: Kamailio (SER) - Users Mailing List <sr-users(a)lists.kamailio.org>rg>; Henning
Westerholt <hw(a)skalatan.de>
Subject: Re: [SR-Users] CockroachDB and Kamailio
Daniel,
Yes, sorry, there are two issues at discussion here.
1. Use of UUID’s instead of INT. This is kinda moot for us right now. We switched
subscriber to autogenerate UUID, and it’s working great for us. We don’t really *need* to
do this anywhere else currently. I don’t know when I would have time to test all of the
other modules/use cases.
2. I’d love to PR back the kamdbctl updates that make CockroachDB work out of the box with
the existing PGSQL scripts. I have a branch that has some small updates that works for
both PostgreSQL and CockroachDB:
https://github.com/reperio/kamailio/tree/cockroachdb-compat. However, there is one thing
that I don’t really know if it can be fixed. The kamdbctl scripts for PostgreSQL creates
a function in the kamailio (created) db called “rand()”. The comment in the script seems
to point to something in the lcr module. But I can’t seem to find where the lcr module is
using rand(). Basically, I’m wondering if this is still needed? Or, if this could be
abstracted so that rand() is used for MySQL and random() is used for PostgreSQL?
Thanks!
~Noah
On Aug 25, 2020, at 9:28 AM, Daniel-Constantin Mierla
<miconda@gmail.com<mailto:miconda@gmail.com>> wrote:
Hello,
somehow I understood that you want to replace id filed in tables from integer (auto
increment) to some sort of string uid. Some modules expect that field to be integer, so
changing its type can break them. If you change to use random unique values instead of
auto-increment, then I expect to work.
Cheers,
Daniel
On 21.08.20 23:47, Noah Mehl wrote:
Daniel/Henning,
I have created a new branch that is much more CockroachDB compatible:
https://github.com/reperio/kamailio/tree/cockroachdb-compat
So far, the only thing I’ve noticed that isn’t compatible is:
https://github.com/reperio/kamailio/blob/62aad6591423e1f693397d33ddefd23493…,
as the concat() function actually exists in PostgreSQL > 9ish and CockroachDB. That
being said, the only difference I can find between MySQL rand() and PostgreSQL random() is
just the name. Is this still an issue with the lcr module? If so, can you point me to
where it’s being used?
Otherwise, I have tested this update with PostgreSQL and CockroachDB.
Thanks!
~Noah
On Aug 21, 2020, at 2:38 PM, Noah Mehl
<noahmehl@gmail.com<mailto:noahmehl@gmail.com>> wrote:
Daniel,
Thanks for the thoughtful reply. I can, at the very least, try and work on the stock
pgsql scripts to work OOTB with CockroachDB (minus the create functions).
The only table we really care about UUID right now is subscriber, and we can just track
that ourselves.
I will give kamcli a try, and hopefully will be able to help in the future.
Thanks!
~Noah
On Aug 21, 2020, at 4:34 AM, Daniel-Constantin Mierla
<miconda@gmail.com<mailto:miconda@gmail.com>> wrote:
Hello,
the default kamailio.cfg is aiming to offer a starting point for building more complex
configuration/SIP routing policies, not to offer all the options we support in Kamailio.
MySQL is provided there to show how to connect to database, being chosen because it was
the first database connect module that was developed and it is kept because it is still
very popular. You are more than welcome to add a sample config of using postgress, which
can be placed somewhere in the misc/examples/. Making the default config too complex may
result in "scaring" the people trying to use Kamailio for first time.
Using string UUID instead of the auto-increment integer id it will break at least lcr and
msilo, iirc. Most of the modules do not use id column, but some do it. Siremis, the web
management interface is also using the id field, but it doesn't support Postgres at
this moment.
If you have some Python knowledge (and spare time), maybe you can help adding support for
it in kamcli:
*
https://github.com/kamailio/kamcli
kamcli aims to be a more modern alternative to kamctl/kamdbctl (e.g., better input
validation, flexibility in output formatting, internal interactive shell with
auto-completion, ...), eventually replacing them in the future. So far I was focusing on
MySQL, being the database type I use. Most of the commands should just work for Postgres,
because db operations are done using SqlAlchemy package, but a few commands (from the
kamcli db ... subcommand) use the cli tool of the database system. At the end these can be
skipped, iirc, also for kamctl, some of the corresponding subcommands are only for mysql
(like kamctl db connect), but testing and seeing if it works or not with Postgres or
CockroachDB would be appreciated.
Cheers,
Daniel
On 20.08.20 22:42, Noah Mehl wrote:
Henning,
So, for the default config, it only has the option for: WITH_MYSQL. I was wondering if a
WITH_PGSQL would be accepted.
As for the kamdbctl scripts, there are a few things I’ve noticed:
I would prefer UUID vs SERIAL. This actually is a little more annoying when dealing with
the SEQUENCE entity in Postgres. The only change required, is to load the pgcrypto
extension and switch to uuid instead of SERIAL. I have a tracking branch here:
https://github.com/reperio/kamailio/tree/postgres_uuid
The other reason is that for cockroachdb, using gen_random_uuid() is documented to be more
efficient<https://www.cockroachlabs.com/docs/stable/create-sequence.html… (in
addition to being a preference).
As for cockroachdb, I have a tracking branch (based on the uuid branch) that seems to be
working well:
https://github.com/reperio/kamailio/tree/cockroach
So far, the only issue in the creation/managment of the schema is: CREATE FUNCTION. But
it looks like maybe concat() and random() are already supported by cockroackdb:
https://www.cockroachlabs.com/docs/stable/functions-and-operators.html. I will have to
dig deeper into the lcr module to see where/if this is an issue.
Thanks!
~Noah
On Aug 20, 2020, at 2:23 PM, Henning Westerholt
<hw@skalatan.de<mailto:hw@skalatan.de>> wrote:
Hi Noah,
if you find something that does not work with the default PostgreSQL schema from kamdbctl,
create an issue. It some cases it is just a matter of formatting and it can work for
PostgreSQL and CockroachDB. This is probably the easier path, from an maintenance point of
view.
What do you mean by default configuration?
Cheers,
Henning
--
Henning Westerholt -
https://skalatan.de/blog/
Kamailio services -
https://gilawa.com<https://gilawa.com/>
-----Original Message-----
From: Noah Mehl <noahmehl@gmail.com<mailto:noahmehl@gmail.com>>
Sent: Thursday, August 20, 2020 6:35 PM
To: Henning Westerholt <hw@skalatan.de<mailto:hw@skalatan.de>>
Cc: Kamailio (SER) - Users Mailing List
<sr-users@lists.kamailio.org<mailto:sr-users@lists.kamailio.org>>
Subject: Re: [SR-Users] CockroachDB and Kamailio
Henning,
Thanks for the reply! I am testing away. I will update with my findings.
That being said, some things might be slightly different. Should I add a cockroachdb
option to the kamdbctl and default configs as a PR?
~Noah
On Aug 20, 2020, at 2:35 AM, Henning Westerholt
<hw@skalatan.de<mailto:hw@skalatan.de>> wrote:
Dear Noah,
it was probably not discussed on the public list, at least I don't remember it.
Cockroachdb claims to be compatible with PostgreSQL, so it should work with this DB
Kamailio module.
If you encounter issues, report on this list, or open a bug report if its something
related to problems in the Kamailio db_postgres module.
Cheers,
Henning
--
Henning Westerholt -
https://skalatan.de/blog/ Kamailio services -
https://gilawa.com<https://gilawa.com/>
-----Original Message-----
From: sr-users
<sr-users-bounces@lists.kamailio.org><mailto:sr-users-bounces@lists.kamailio.org>
On Behalf Of Noah
Mehl
Sent: Wednesday, August 19, 2020 10:13 PM
To: sr-users@lists.kamailio.org<mailto:sr-users@lists.kamailio.org>
Subject: [SR-Users] CockroachDB and Kamailio
Has anyone been down this path before? We are trying to test this out and the results are
pretty promising so far.
I realize the lack of Stored Procedures and Triggers make this untenable for many Postgres
based implementations.
Thanks!
~Noah
_______________________________________________
Kamailio (SER) - Users Mailing List
sr-users@lists.kamailio.org<mailto:sr-users@lists.kamailio.org>
https://lists.kamailio.org/cgi-bin/mailman/listinfo/sr-users
_______________________________________________
Kamailio (SER) - Users Mailing List
sr-users@lists.kamailio.org<mailto:sr-users@lists.kamailio.org>
https://lists.kamailio.org/cgi-bin/mailman/listinfo/sr-users
--
Daniel-Constantin Mierla --
www.asipto.com<http://www.asipto.com/>
www.twitter.com/miconda<http://www.twitter.com/miconda> --
www.linkedin.com/in/miconda<http://www.linkedin.com/in/miconda>
Funding:
https://www.paypal.me/dcmierla
--
Daniel-Constantin Mierla --
www.asipto.com<http://www.asipto.com/>
www.twitter.com/miconda<http://www.twitter.com/miconda> --
www.linkedin.com/in/miconda<http://www.linkedin.com/in/miconda>
Funding:
https://www.paypal.me/dcmierla