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

[Gerald Weber]

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


-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.freeswitch.org/pipermail/freeswitch-users/attachments/20121118/b94e1104/attachment.html