[Freeswitch-users] Suggestion to harmonize 'hash' & 'db' insert method

Mon Nov 19 02:32:10 MSK 2012

On 18 November 2012 15:04, Yiftach Golan <yiftah at choochee.com> wrote:

> We have the exact same problem and we solve it pretty nicely but due to
> the policy "do not say anything that is different than what we did" I will
> not put it in public if you want Scott we can continue with you and Ken in
> private
>

Cool and kind of you.  Thanks, Yiftach.

Right now, on our easy stuff, we're ok with our work-around.  We just
delete THEN insert.  But we have more coming up and we enjoy seeing others'
cleverness-es!

    <entry action="menu-exec-app" digits="0" param="db
delete/ss_fmfm/${caller_id_number}"/>
    <entry action="menu-exec-app" digits="0" param="db
insert/ss_fmfm/${caller_id_number}/off"/>
    <entry action="menu-exec-app" digits="0" param="playback shout://
translate.google.com/translate_tts?tl=en&q=Option:+now+$db(select/..."/>
    <entry action="menu-exec-app" digits="1" param="db
delete/ss_fmfm/${caller_id_number}"/>
    <entry action="menu-exec-app" digits="1" param="db
insert/ss_fmfm/${caller_id_number}/on"/>
    <entry action="menu-exec-app" digits="1" param="playback shout://
translate.google.com/translate_tts?tl=en&q=Option:+now+${db(select/.."/>

Also I would please like to ask you not to use inappropriate words like
> "MySQL" in this forum as it may cause people to feel uncomfortable

Hmm?  I had no idea -- and how could one.  In my defense, I didn't assert
"its" use.  I just showed (intended) its comparative syntax and the rest
was English just being funny-that-way about nouns.  ;-)
Not-so-understood, but nevertheless acknowledged and guided accordingly.

(I'm actually a postgreSQL and huge DB2 EXPRESS-C fan!)

Again, thank you, on both counts.
/Scott

> On Sun, Nov 18, 2012 at 7:41 AM, Ken Rice <krice at freeswitch.org> wrote:
>
>>  You have to keep in mind that we have to support more then just mysql
>> or sqlite with the the db interface... This means using SQL that’s common
>> to ALL platforms include MSSQL, PostgreSQL, Oracle and any other database
>> someone might want to use via ODBC...
>>
>> So to do this completely would require modification to the code on insert
>> to first check if the row exists for the key, then deleting it, then
>> inserting the new row...  This is going to be the most generic supportable
>> method to make them work consistently across the board...
>>
>> However, Keep in mind that what you are calling a bug, could be treated
>> by many people as a feature... Don’t allow me to create/replace and already
>> existing key...
>>
>>
>>
>>
>> On 11/18/12 7:26 AM, "Scott" <8f27e956 at gmail.com> wrote:
>>
>> OK, with the one-time CREATE UNIQUE INDEX on 'table'
>> (hostname,realm,data_key); stmt in place at the schema-level, a portable
>> work-a-like of INSERT OR REPLACE stmt is the UPDATE-INSERT (i.e. "UPSERT"
>> lol) stmt pair that follows,
>>
>> BEGIN
>> TRANSACTION;
>>
>> UPDATE 'table' SET data_value='value_data' WHERE
>> hostname='value_hostname' AND realm='value_realm' AND
>> data_key='value_data_key' ;
>> INSERT INTO 'table' (hostname,realm,data_key,data_value) SELECT
>> 'value_hostname', 'value_realm', 'value_data_key', 'value_data_value'
>>    WHERE NOT EXISTS (SELECT 1 FROM 'table' WHERE
>> hostname='value_hostname' AND realm='value_realm' AND
>> data_key='value_data_key' );
>> COMMIT;
>>
>>
>> The two WHERE clauses must be identical and the variables should be in
>> the same order as the unique compound index is ordered.
>> The UPDATE fails quietly and benignly if the record does NOT already
>> exist and succeeds if it does.
>> The INSERT ... WHERE NOT EXISTS stmt succeeds if the record does NOT
>> already exist fails quietly and benignly if it does.
>> The BEGIN and COMMIT aren't typically necessary but they're over-safe
>> just in case the same record is attempted by another thread.
>>
>> I think this get's it done.
>>
>> :-)
>>
>>
>> On 18 November 2012 06:25, S. Scott <8f27e956 at gmail.com> wrote:
>>
>> The IF NOT EXITS term is easily omitted.  It's a helper term for batch
>> scripted SQL to prevent an error being thrown in the case of the index
>> already exists as in the case where the make schema script is run more than
>> once.  Easily omitted and, perhaps more properly, replaced with a DROP
>> INDEX stmt and CREATE UNIQUE INDEX stmt pair.
>>
>> Yeah, i checked further, the OR REPLACE modifier is a little more
>> Hit/miss.  The capability is there typically present, but the syntax varies
>> -- e.g sqlite3 ON REPLACE is mySQL's ON DUPLICATE KEY UPDATE.
>>
>> Darn shame sqlite3 doesn't support stored procedures 'cause then it's
>> more easily abstracted for portability up the SQL server engine curve.
>>
>> On 2012-11-18, at 3:53, Gerald Weber <gerald.weber at besharp.at> wrote:
>>
>> AFAIK, „if not exists“ and „or replace“ is not ansi sql  syntax, so you
>> will get in trouble if
>> someone uses odbc to a db that doesn’t support non standard syntax.
>>
>> The insert or replace could be replaced with the merge statement.
>>
>> *Von:* freeswitch-users-bounces at lists.freeswitch.org [
>> mailto:freeswitch-users-bounces at lists.freeswitch.org<freeswitch-users-bounces at lists.freeswitch.org>]
>> *Im Auftrag von *Scott
>> *Gesendet:* Sonntag, 18. November 2012 02:42
>> *An:* FreeSWITCH Users Help
>> *Betreff:* [Freeswitch-users] Suggestion to harmonize 'hash' & 'db'
>> insert method
>>
>> Are there reasons why the function 'hash' (non-persistent storage) and
>> function 'db' (persistent storage) share a look-a-like user interface (e.g.
>> select/insert/delete) but do not work-a-like.  In particular, in 'hash',
>> insert overwrites an identical realm/data_key pair whereas 'db' , in so far
>> as I can tell, just blindly adds, and adds, adds, the insert(s).  However,
>> the 'db' select method returns one record only even if the db has
>> accumulated many realm/data_key records, including data_value duplicates.
>>
>> NOTWITHSTANDING reasons-unknow-to-me, a remedy to harmonize the
>> non-persistent 'hash' with the persistent 'db'  I *think* is straight
>> forward ...
>>
>> (1) At the call_limit.db schema-level, a one-time create UNIQUE COMPOUND
>> index, as follows,
>>
>> CREATE UNIQUE INDEX IF NOT EXISTS 'idx_db_data_HostRealmDK' ON 'db_data'
>> ('hostname','realm','data_key');
>>
>> (1) At the c-language embedded SQL string-level,
>>
>> change the existing INSERT to be the following,
>>
>> INSERT OR REPLACE INTO db_data (hostname,realm,data_key,data) VALUES
>> (%s,%s,%s,%s);
>>
>> The index will help speed UP the reads.  Neither change is "exotic" and
>> should be portable across standard sql implementations.
>>
>> ;-) ... thoughts ?
>>
>> Thanks,
>>
>>
>> _________________________________________________________________________
>> Professional FreeSWITCH Consulting Services:
>> consulting at freeswitch.org
>> http://www.freeswitchsolutions.com
>>
>> 
>> 
>>
>> Official FreeSWITCH Sites
>> http://www.freeswitch.org
>> http://wiki.freeswitch.org
>> http://www.cluecon.com
>>
>> FreeSWITCH-users mailing list
>> FreeSWITCH-users at lists.freeswitch.org
>> http://lists.freeswitch.org/mailman/listinfo/freeswitch-users
>> UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users
>> http://www.freeswitch.org
>>
>>
>>
>> ------------------------------
>> _________________________________________________________________________
>> Professional FreeSWITCH Consulting Services:
>> consulting at freeswitch.org
>> http://www.freeswitchsolutions.com
>>
>> 
>> 
>>
>> Official FreeSWITCH Sites
>> http://www.freeswitch.org
>> http://wiki.freeswitch.org
>> http://www.cluecon.com
>>
>> FreeSWITCH-users mailing list
>> FreeSWITCH-users at lists.freeswitch.org
>> http://lists.freeswitch.org/mailman/listinfo/freeswitch-users
>> UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users
>> http://www.freeswitch.org
>>
>>
>> --
>> Ken
>> *http://www.FreeSWITCH.org
>> http://www.ClueCon.com
>> http://www.OSTAG.org
>> *irc.freenode.net #freeswitch
>>
>> _________________________________________________________________________
>> Professional FreeSWITCH Consulting Services:
>> consulting at freeswitch.org
>> http://www.freeswitchsolutions.com
>>
>> 
>> 
>>
>> Official FreeSWITCH Sites
>> http://www.freeswitch.org
>> http://wiki.freeswitch.org
>> http://www.cluecon.com
>>
>> FreeSWITCH-users mailing list
>> FreeSWITCH-users at lists.freeswitch.org
>> http://lists.freeswitch.org/mailman/listinfo/freeswitch-users
>> UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users
>> http://www.freeswitch.org
>>
>>
>
> _________________________________________________________________________
> Professional FreeSWITCH Consulting Services:
> consulting at freeswitch.org
> http://www.freeswitchsolutions.com
>
> 
> 
>
> Official FreeSWITCH Sites
> http://www.freeswitch.org
> http://wiki.freeswitch.org
> http://www.cluecon.com
>
> FreeSWITCH-users mailing list
> FreeSWITCH-users at lists.freeswitch.org
> http://lists.freeswitch.org/mailman/listinfo/freeswitch-users
> UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users
> http://www.freeswitch.org
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.freeswitch.org/pipermail/freeswitch-users/attachments/20121118/c0b82f76/attachment-0001.html 