[asterisk-dev] AstDB mySQL implementation
Corey Farrell
git at cfware.com
Mon Oct 30 11:52:55 CDT 2017
I think astdb itself is an inappropriate place to deal with this. astdb
is initialized well before module preloads so it would be nearly
impossible for modules to provide the astdb implementation. In my
opinion astdb is a database "implementation", not a connector (and that
shouldn't change). For higher level connectors with configurable
backends we have realtime and sorcery. If someone were to write a redis
or memcached connector for Asterisk I would expect it to be a realtime
or sorcery driver. If func_sorcery can be expanded to perform
writes/deletes maybe it could be used in place of func_db? I suspect
that dialplan use of astdb is a bigger problem than the ways that
Asterisk uses astdb directly on it's own.
Documentation: Maybe we need to add a warning to xmldoc for the astdb
app/func/AMI/AGI that all astdb operations are serialized (dblock global
mutex) and thus performance could suffer if used too much from too many
threads? Do we have any guides/sample files showing how to replace
astdb operations with alternatives (func_odbc for example)?
On 10/29/2017 10:17 AM, Nir Simionovich wrote:
> Seems like I have under estimated the task at hand, as this part of
> Asterisk requires some
> more intricate familiarity with how AstDB truly works. It would be one
> thing to "change the backend"
> it would a far more complex task to "make two backends selectable".
>
> Conclusion - not sure this is worth the effort at this point in time,
> maybe in a later stage. :-(
>
>
>
> On Thu, Oct 26, 2017 at 6:01 PM Nir Simionovich
> <nir.simionovich at gmail.com <mailto:nir.simionovich at gmail.com>> wrote:
>
> Correction, seems like this requires a bit more architecture than
> I anticipated.
>
> Basically, we need to separate this into several files and turn
> the entire AstDB concept
> into a pluggable module type module.
>
> But, as AstDB is a mandatory module for Asterisk, can we create a
> situation where
> a pluggable module is a mandatory requirement for Asterisk to
> launch correctly?
>
> Is there anything like that in Asterisk? can someone point me in
> some proper example
> or preferably, something that I can look at and learn from?
>
>
>
> On Thu, Oct 26, 2017 at 4:47 PM Nir Simionovich
> <nir.simionovich at gmail.com <mailto:nir.simionovich at gmail.com>> wrote:
>
> I'll have a look at it.
>
> If I read the code correctly, the AstDB is invoked from the
> asterisk.c file, when asterisk is launched.
> I think the best would be to add a configuration file like
> astdb.conf, which will look something like this:
>
> [general]
> engine=builtin ; values can be either builtin, redis or
> memcache (or others in the future)
>
> ;[redis]
> ;server = 127.0.0.1
> ;port = 6379
> ;database = 15 ; By default AstDB will use Redis database
> number 15
>
> ;[memcache]
> ;server = 127.0.0.1
> ;port = 11211
> ;prefix = asterisk
>
> Then, inside the db.c file add the proper statements and
> backend to support each of these. I'm confident
> that from a design perspective, this is not optimal, but it
> would serve as a nice PoC to indicate if the task
> is feasible or not.
>
> For example, the following:
>
> DEFINE_SQL_STATEMENT(put_stmt, "INSERT OR REPLACE INTO astdb
> (key, value) VALUES (?, ?)")
> DEFINE_SQL_STATEMENT(get_stmt, "SELECT value FROM astdb WHERE
> key=?")
> DEFINE_SQL_STATEMENT(del_stmt, "DELETE FROM astdb WHERE key=?")
> DEFINE_SQL_STATEMENT(deltree_stmt, "DELETE FROM astdb WHERE
> key || '/' LIKE ? || '/' || '%'")
> DEFINE_SQL_STATEMENT(deltree_all_stmt, "DELETE FROM astdb")
> DEFINE_SQL_STATEMENT(gettree_stmt, "SELECT key, value FROM
> astdb WHERE key || '/' LIKE ? || '/' || '%' ORDER BY key")
> DEFINE_SQL_STATEMENT(gettree_all_stmt, "SELECT key, value FROM
> astdb ORDER BY key")
> DEFINE_SQL_STATEMENT(showkey_stmt, "SELECT key, value FROM
> astdb WHERE key LIKE '%' || '/' || ? ORDER BY key")
> DEFINE_SQL_STATEMENT(create_astdb_stmt, "CREATE TABLE IF NOT
> EXISTS astdb(key VARCHAR(256), value VARCHAR(256), PRIMARY
> KEY(key))")
>
> Can be augmented with something like the following:
>
> DEFINE_REDIS_STATEMENT(put_redis_stmt, "");
> DEFINE_REDIS_STATEMENT(get_redis_stmt, "");
> DEFINE_REDIS_STATEMENT(del_redis_stmt, "");
>
> Following this, we can simply point to the proper statements
> following the engine selection.
>
> What do you think, sounds reasonable?
>
>
> On Thu, Oct 26, 2017 at 4:30 PM Olle E. Johansson
> <oej at edvina.net <mailto:oej at edvina.net>> wrote:
>
>> On 26 Oct 2017, at 15:25, Nir Simionovich
>> <nir.simionovich at gmail.com
>> <mailto:nir.simionovich at gmail.com>> wrote:
>>
>> I suspect the original code didn't change the overall
>> mechanism dramatically, it's mainly a new implementation.
>> This thing is this - replacing the implementation seems
>> straight forward enough, making it configurable, seems
>> like a pain in the butt.
> Look for the “appleraisin” branch if you want to see code :-)
>
> /O
>>
>>
>>
>> On Thu, Oct 26, 2017 at 4:23 PM Olle E. Johansson
>> <oej at edvina.net <mailto:oej at edvina.net>> wrote:
>>
>>> On 26 Oct 2017, at 15:20, Nir Simionovich
>>> <nir.simionovich at gmail.com
>>> <mailto:nir.simionovich at gmail.com>> wrote:
>>>
>>> Just looked into the code, this is not a simple task
>>> to put a new backend for astdb. The code isn't even
>>> designed
>>> for something like that. Judging from what I can
>>> tell, and tell me if I'm wrong - turning this into a
>>> configurable thing
>>> would be more or less an open-heart surgery.
>> My patch wasn’t that bad, but it was before sqlite.
>>
>> /O
>>
>>>
>>>
>>>
>>> On Thu, Oct 26, 2017 at 4:16 PM Olle E. Johansson
>>> <oej at edvina.net <mailto:oej at edvina.net>> wrote:
>>>
>>> Somewhere in Asterisk space, there’s an old
>>> patch where I added ASTDB over realtime, meaning
>>> you can use
>>> any realtime storage. If I remember correctly
>>> there was a bit of chicken-and-egg problem with
>>> some astdb
>>> calls happening before realtime got launched,
>>> but otherwise it worked just fine in production
>>> for a long time.
>>>
>>> /O
>>>
>>>> On 26 Oct 2017, at 15:13, Nir Simionovich
>>>> <nir.simionovich at gmail.com
>>>> <mailto:nir.simionovich at gmail.com>> wrote:
>>>>
>>>> I'd like to +1 on that idea.
>>>>
>>>> While I'm somewhat reluctant to using mySQL as
>>>> the base of such a change, as mySQL is an
>>>> overkill for AstDB,
>>>> having a proper AstDB configurable backend is
>>>> an interesting thing. Personally speaking, I
>>>> would actually prefer
>>>> something like Memcache or preferably Redis.
>>>> Both are similar in function and usability to
>>>> AstDB, both are fairly
>>>> scalable (Redis specifically) and both are
>>>> fairly simplistic in nature.
>>>>
>>>> I do admit that this got me intrigued...
>>>>
>>>>
>>>>
>>>> On Tue, Sep 26, 2017 at 12:45 AM Matt
>>>> Fredrickson <creslin at digium.com
>>>> <mailto:creslin at digium.com>> wrote:
>>>>
>>>> On Fri, Sep 22, 2017 at 12:12 PM, Ryan
>>>> Wagoner <rswagoner at gmail.com
>>>> <mailto:rswagoner at gmail.com>> wrote:
>>>>
>>>> I've been scaling out FreePBX
>>>> horizontally with Kamailio and custom
>>>> FreePBX modules mainly to handle call
>>>> center outbound dialing (around 20k
>>>> calls per day). One of the issues I ran
>>>> into was FreePBX uses the AstDB
>>>> extensively and will write changes to
>>>> it from the dialplan or the FreePBX
>>>> user control panel.
>>>>
>>>> To overcome this I either needed to
>>>> scrap FreePBX and build a new GUI using
>>>> Asterisk realtime, heavily modify
>>>> FreePBX (not an option), or rewrite
>>>> AstDB to use a database like mySQL. I
>>>> choose the last option and have had the
>>>> code in production for just over a
>>>> month. I'm backing it with a two node
>>>> MariaDB Galera cluster with HAProxy
>>>> providing failover for the client DB
>>>> connections.
>>>>
>>>> I realize that SQLite was chosen for
>>>> AstDB for performance reasons. However
>>>> mySQL seems to perform just fine in the
>>>> above scenario. Right now I have a db.c
>>>> file that just has the mySQL code. Does
>>>> anybody else have any interest in using
>>>> mySQL for the AstDB backend? I'm
>>>> debating if it would make sense to have
>>>> the option to select your AstDB backend.
>>>>
>>>>
>>>> Hey Ryan,
>>>>
>>>> First off, thanks for letting us know about
>>>> the fun project you embarked upon. I think
>>>> Josh already answered some of your
>>>> questions, but with regards to the work you
>>>> did - I believe that in the past there have
>>>> been others who have wanted an ODBC AstDB
>>>> driver as well. If your code can be made
>>>> configurable, it may be a good contribution.
>>>>
>>>> Anyways, hope you are doing well, and
>>>> perhaps we'll see your code up on gerrit at
>>>> some time in the future. :-)
>>>>
>>>> --
>>>> Matthew Fredrickson
>>>> Digium, Inc. | Engineering Manager
>>>> 445 Jan Davis Drive NW - Huntsville, AL
>>>> 35806 - USA
>>>> <https://maps.google.com/?q=445+Jan+Davis+Drive+NW+-+Huntsville,+AL+35806+-+USA&entry=gmail&source=g>
>>>> --
>>>> _____________________________________________________________________
>>>> -- Bandwidth and Colocation Provided by
>>>> http://www.api-digital.com
>>>> <http://www.api-digital.com/> --
>>>>
>>>> asterisk-dev mailing list
>>>> To UNSUBSCRIBE or update options visit:
>>>> http://lists.digium.com/mailman/listinfo/asterisk-dev
>>>>
>>>> --
>>>> Kind Regards,
>>>> Nir Simionovich
>>>> GreenfieldTech
>>>> (schedule) http://nirsimionovich.appointy.com/
>>>> (w)http://www.greenfieldtech.net
>>>> <http://www.greenfieldtech.net/>
>>>> (p) +972-73-2557799 <tel:073-255-7799>
>>>> (MSN):nirs at greenfieldtech.net
>>>> <mailto:nirs at greenfieldtech.net>
>>>> (m) +972-54-6982826 <tel:054-698-2826>
>>>> (GTALK):nir.simionovich at gmail.com
>>>> <mailto:nir.simionovich at gmail.com>
>>>> (f) +972-73-2557202 <tel:073-255-7202>
>>>> (SKYPE): greenfieldtech.nir
>>>>
>>>> ----------------------------------------------------------
>>>> Zero Your Inbox
>>>> <https://mailstrom.co/referral/ARZJE> | Cloud
>>>> Servers
>>>> <https://www.digitalocean.com/?refcode=97eeea09917a>
>>>> ----------------------------------------------------------
>>>>
>>>>
>>>> *Disclaimer:*
>>>> This e-mail is intended solely for the person
>>>> to whom it is addressed and may contain
>>>> confidential or legally privileged information.
>>>> Access to this e-mail by anyone else is
>>>> unauthorized. If an addressing or transmission
>>>> error has misdirected this e-mail, please
>>>> notify the author by replying to this e-mail
>>>> and destroy this e-mail and any attachments.
>>>> E-mail may be susceptible to data corruption,
>>>> interception, unauthorized amendment, viruses
>>>> and delays or the consequences thereof. If you
>>>> are not the intended recipient, be advised that
>>>> you have received this email in error and that
>>>> any use, dissemination, forwarding, printing or
>>>> copying of this email is strictly prohibited.
>>>>
>>>> --
>>>> _____________________________________________________________________
>>>> -- Bandwidth and Colocation Provided by
>>>> http://www.api-digital.com
>>>> <http://www.api-digital.com/> --
>>>>
>>>> asterisk-dev mailing list
>>>> To UNSUBSCRIBE or update options visit:
>>>> http://lists.digium.com/mailman/listinfo/asterisk-dev
>>>
>>> --
>>> _____________________________________________________________________
>>> -- Bandwidth and Colocation Provided by
>>> http://www.api-digital.com
>>> <http://www.api-digital.com/> --
>>>
>>> asterisk-dev mailing list
>>> To UNSUBSCRIBE or update options visit:
>>> http://lists.digium.com/mailman/listinfo/asterisk-dev
>>>
>>> --
>>> Kind Regards,
>>> Nir Simionovich
>>> GreenfieldTech
>>> (schedule) http://nirsimionovich.appointy.com/
>>> (w)http://www.greenfieldtech.net
>>> <http://www.greenfieldtech.net/>
>>> (p) +972-73-2557799 <tel:073-255-7799>
>>> (MSN):nirs at greenfieldtech.net
>>> <mailto:nirs at greenfieldtech.net>
>>> (m) +972-54-6982826 <tel:054-698-2826>
>>> (GTALK):nir.simionovich at gmail.com
>>> <mailto:nir.simionovich at gmail.com>
>>> (f) +972-73-2557202 <tel:073-255-7202> (SKYPE):
>>> greenfieldtech.nir
>>>
>>> ----------------------------------------------------------
>>> Zero Your Inbox
>>> <https://mailstrom.co/referral/ARZJE> | Cloud
>>> Servers
>>> <https://www.digitalocean.com/?refcode=97eeea09917a>
>>> ----------------------------------------------------------
>>>
>>>
>>> *Disclaimer:*
>>> This e-mail is intended solely for the person to
>>> whom it is addressed and may contain confidential or
>>> legally privileged information. Access to this
>>> e-mail by anyone else is unauthorized. If an
>>> addressing or transmission error has misdirected
>>> this e-mail, please notify the author by replying to
>>> this e-mail and destroy this e-mail and any attachments.
>>> E-mail may be susceptible to data corruption,
>>> interception, unauthorized amendment, viruses and
>>> delays or the consequences thereof. If you are not
>>> the intended recipient, be advised that you have
>>> received this email in error and that any use,
>>> dissemination, forwarding, printing or copying of
>>> this email is strictly prohibited.
>>>
>>> --
>>> _____________________________________________________________________
>>> -- Bandwidth and Colocation Provided by
>>> http://www.api-digital.com
>>> <http://www.api-digital.com/> --
>>>
>>> asterisk-dev mailing list
>>> To UNSUBSCRIBE or update options visit:
>>> http://lists.digium.com/mailman/listinfo/asterisk-dev
>> --
>> _____________________________________________________________________
>> -- Bandwidth and Colocation Provided by
>> http://www.api-digital.com
>> <http://www.api-digital.com/> --
>>
>> asterisk-dev mailing list
>> To UNSUBSCRIBE or update options visit:
>> http://lists.digium.com/mailman/listinfo/asterisk-dev
>>
>> --
>> Kind Regards,
>> Nir Simionovich
>> GreenfieldTech
>> (schedule) http://nirsimionovich.appointy.com/
>> (w)http://www.greenfieldtech.net
>> <http://www.greenfieldtech.net/>
>> (p) +972-73-2557799 <tel:073-255-7799>
>> (MSN):nirs at greenfieldtech.net
>> <mailto:nirs at greenfieldtech.net>
>> (m) +972-54-6982826 <tel:054-698-2826>
>> (GTALK):nir.simionovich at gmail.com
>> <mailto:nir.simionovich at gmail.com>
>> (f) +972-73-2557202 <tel:073-255-7202> (SKYPE):
>> greenfieldtech.nir
>>
>> ----------------------------------------------------------
>> Zero Your Inbox <https://mailstrom.co/referral/ARZJE> |
>> Cloud Servers
>> <https://www.digitalocean.com/?refcode=97eeea09917a>
>> ----------------------------------------------------------
>>
>>
>> *Disclaimer:*
>> This e-mail is intended solely for the person to whom it
>> is addressed and may contain confidential or legally
>> privileged information. Access to this e-mail by anyone
>> else is unauthorized. If an addressing or transmission
>> error has misdirected this e-mail, please notify the
>> author by replying to this e-mail and destroy this e-mail
>> and any attachments.
>> E-mail may be susceptible to data corruption,
>> interception, unauthorized amendment, viruses and delays
>> or the consequences thereof. If you are not the intended
>> recipient, be advised that you have received this email
>> in error and that any use, dissemination, forwarding,
>> printing or copying of this email is strictly prohibited.
>>
>
> --
>
> Kind Regards,
>
> Nir Simionovich
>
> GreenfieldTech
>
> (schedule) http://nirsimionovich.appointy.com/
>
> (w)http://www.greenfieldtech.net
> <http://www.greenfieldtech.net/>
>
> (p) +972-73-2557799 <tel:073-255-7799>
> (MSN):nirs at greenfieldtech.net <mailto:nirs at greenfieldtech.net>
>
> (m) +972-54-6982826 <tel:054-698-2826>
> (GTALK):nir.simionovich at gmail.com
> <mailto:nir.simionovich at gmail.com>
>
> (f) +972-73-2557202 <tel:073-255-7202> (SKYPE):
> greenfieldtech.nir
>
>
> ----------------------------------------------------------
>
> Zero Your Inbox <https://mailstrom.co/referral/ARZJE> | Cloud
> Servers <https://www.digitalocean.com/?refcode=97eeea09917a>
>
> ----------------------------------------------------------
>
>
> *Disclaimer:*
>
> This e-mail is intended solely for the person to whom it is
> addressed and may contain confidential or legally privileged
> information. Access to this e-mail by anyone else is
> unauthorized. If an addressing or transmission error has
> misdirected this e-mail, please notify the author by replying
> to this e-mail and destroy this e-mail and any attachments.
> E-mail may be susceptible to data corruption, interception,
> unauthorized amendment, viruses and delays or the consequences
> thereof. If you are not the intended recipient, be advised
> that you have received this email in error and that any use,
> dissemination, forwarding, printing or copying of this email
> is strictly prohibited.
>
> --
>
> Kind Regards,
>
> Nir Simionovich
>
> GreenfieldTech
>
> (schedule) http://nirsimionovich.appointy.com/
>
> (w)http://www.greenfieldtech.net <http://www.greenfieldtech.net/>
>
> (p) +972-73-2557799 <tel:073-255-7799>
> (MSN):nirs at greenfieldtech.net <mailto:nirs at greenfieldtech.net>
>
> (m) +972-54-6982826 <tel:054-698-2826>
> (GTALK):nir.simionovich at gmail.com <mailto:nir.simionovich at gmail.com>
>
> (f) +972-73-2557202 <tel:073-255-7202> (SKYPE): greenfieldtech.nir
>
>
> ----------------------------------------------------------
>
> Zero Your Inbox <https://mailstrom.co/referral/ARZJE> | Cloud
> Servers <https://www.digitalocean.com/?refcode=97eeea09917a>
>
> ----------------------------------------------------------
>
>
> *Disclaimer:*
>
> This e-mail is intended solely for the person to whom it is
> addressed and may contain confidential or legally privileged
> information. Access to this e-mail by anyone else is unauthorized.
> If an addressing or transmission error has misdirected this
> e-mail, please notify the author by replying to this e-mail and
> destroy this e-mail and any attachments.
> E-mail may be susceptible to data corruption, interception,
> unauthorized amendment, viruses and delays or the consequences
> thereof. If you are not the intended recipient, be advised that
> you have received this email in error and that any use,
> dissemination, forwarding, printing or copying of this email is
> strictly prohibited.
>
> --
>
> Kind Regards,
>
> Nir Simionovich
>
> GreenfieldTech
>
> (schedule) http://nirsimionovich.appointy.com/
>
> (w)http://www.greenfieldtech.net <http://www.greenfieldtech.net/>
>
> (p) +972-73-2557799 (MSN):nirs at greenfieldtech.net
> <mailto:nirs at greenfieldtech.net>
>
> (m) +972-54-6982826 (GTALK):nir.simionovich at gmail.com
> <mailto:nir.simionovich at gmail.com>
>
> (f) +972-73-2557202 (SKYPE): greenfieldtech.nir
>
>
> ----------------------------------------------------------
>
> Zero Your Inbox <https://mailstrom.co/referral/ARZJE> | Cloud Servers
> <https://www.digitalocean.com/?refcode=97eeea09917a>
>
> ----------------------------------------------------------
>
>
> *Disclaimer:*
>
> This e-mail is intended solely for the person to whom it is addressed
> and may contain confidential or legally privileged information. Access
> to this e-mail by anyone else is unauthorized. If an addressing or
> transmission error has misdirected this e-mail, please notify the
> author by replying to this e-mail and destroy this e-mail and any
> attachments.
> E-mail may be susceptible to data corruption, interception,
> unauthorized amendment, viruses and delays or the consequences
> thereof. If you are not the intended recipient, be advised that you
> have received this email in error and that any use, dissemination,
> forwarding, printing or copying of this email is strictly prohibited.
>
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.digium.com/pipermail/asterisk-dev/attachments/20171030/43d9046a/attachment-0001.html>
More information about the asterisk-dev
mailing list