[asterisk-dev] AstDB mySQL implementation
Corey Farrell
git at cfware.com
Mon Oct 30 14:06:32 CDT 2017
I've opened ASTERISK-27383 so this isn't forgotten. About the best
practices document I'm not sure if anything current exists (I got the
impression from Astridevcon discussions that there isn't).
On 10/30/2017 02:19 PM, Nir Simionovich wrote:
> Definite +1 on the Documentation side - for sure. Here is a stupid
> question, is there a "Best Practices" coding document somewhere?
> I remember that many years ago there was something really basic, but
> much has changed since then.
>
> On Mon, Oct 30, 2017 at 6:53 PM Corey Farrell <git at cfware.com
> <mailto:git at cfware.com>> wrote:
>
> 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>
>>
> --
>
> 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/c98b4ac3/attachment-0001.html>
More information about the asterisk-dev
mailing list