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

[Yiftach Golan]

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
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

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
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.freeswitch.org/pipermail/freeswitch-users/attachments/20121118/0df848d2/attachment-0001.html