[SR-Users] CockroachDB and Kamailio

Daniel-Constantin Mierla miconda at gmail.com
Tue Aug 25 15:28:48 CEST 2020 

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/62aad6591423e1f693397d33ddefd234938d1293/utils/kamctl/kamdbctl.pgsql#L137,
> 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 at gmail.com
>> <mailto:noahmehl at 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 at gmail.com <mailto:miconda at 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 at skalatan.de
>>>>> <mailto:hw at 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 at gmail.com <mailto:noahmehl at gmail.com>>
>>>>> Sent: Thursday, August 20, 2020 6:35 PM
>>>>> To: Henning Westerholt <hw at skalatan.de <mailto:hw at skalatan.de>>
>>>>> Cc: Kamailio (SER) - Users Mailing List
>>>>> <sr-users at lists.kamailio.org <mailto:sr-users at 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 at skalatan.de
>>>>>> <mailto:hw at 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 at lists.kamailio.org> On Behalf Of
>>>>>> Noah
>>>>>> Mehl
>>>>>> Sent: Wednesday, August 19, 2020 10:13 PM
>>>>>> To: sr-users at 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 at lists.kamailio.org
>>>>>> https://lists.kamailio.org/cgi-bin/mailman/listinfo/sr-users
>>>>>
>>>>
>>>>
>>>> _______________________________________________
>>>> 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
>>
>
-- 
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/20200825/285d2017/attachment.htm>

More information about the sr-users mailing list