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,
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@lists.freeswitch.org [mailto:freeswitch-users-bounces@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,
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@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@lists.freeswitch.org [mailto: freeswitch-users-bounces@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@freeswitch.org http://www.freeswitchsolutions.com FreeSWITCH-powered IP PBX: The CudaTel Communication Server http://www.cudatel.com Official FreeSWITCH Sites http://www.freeswitch.org http://wiki.freeswitch.org http://www.cluecon.com FreeSWITCH-users mailing list FreeSWITCH-users@lists.freeswitch.org http://lists.freeswitch.org/mailman/listinfo/freeswitch-users UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users http://www.freeswitch.org
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@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@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@lists.freeswitch.org [mailto: freeswitch-users-bounces@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@freeswitch.org http://www.freeswitchsolutions.com
FreeSWITCH-powered IP PBX: The CudaTel Communication Server http://www.cudatel.com
Official FreeSWITCH Sites http://www.freeswitch.org http://wiki.freeswitch.org http://www.cluecon.com
FreeSWITCH-users mailing list FreeSWITCH-users@lists.freeswitch.org http://lists.freeswitch.org/mailman/listinfo/freeswitch-users UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users http://www.freeswitch.org
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@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@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@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@lists.freeswitch.org [mailto:freeswitch-users-bounces@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@freeswitch.org http://www.freeswitchsolutions.com
FreeSWITCH-powered IP PBX: The CudaTel Communication Server http://www.cudatel.com
Official FreeSWITCH Sites http://www.freeswitch.org http://wiki.freeswitch.org http://www.cluecon.com
FreeSWITCH-users mailing list FreeSWITCH-users@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@freeswitch.org http://www.freeswitchsolutions.com
FreeSWITCH-powered IP PBX: The CudaTel Communication Server http://www.cudatel.com
Official FreeSWITCH Sites http://www.freeswitch.org http://wiki.freeswitch.org http://www.cluecon.com
FreeSWITCH-users mailing list FreeSWITCH-users@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
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@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@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@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@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@lists.freeswitch.org [ mailto:freeswitch-users-bounces@lists.freeswitch.org<freeswitch-users-bounces@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@freeswitch.org http://www.freeswitchsolutions.com
FreeSWITCH-powered IP PBX: The CudaTel Communication Server http://www.cudatel.com
Official FreeSWITCH Sites http://www.freeswitch.org http://wiki.freeswitch.org http://www.cluecon.com
FreeSWITCH-users mailing list FreeSWITCH-users@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@freeswitch.org http://www.freeswitchsolutions.com
FreeSWITCH-powered IP PBX: The CudaTel Communication Server http://www.cudatel.com
Official FreeSWITCH Sites http://www.freeswitch.org http://wiki.freeswitch.org http://www.cluecon.com
FreeSWITCH-users mailing list FreeSWITCH-users@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@freeswitch.org http://www.freeswitchsolutions.com
FreeSWITCH-powered IP PBX: The CudaTel Communication Server http://www.cudatel.com
Official FreeSWITCH Sites http://www.freeswitch.org http://wiki.freeswitch.org http://www.cluecon.com
FreeSWITCH-users mailing list FreeSWITCH-users@lists.freeswitch.org http://lists.freeswitch.org/mailman/listinfo/freeswitch-users UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users http://www.freeswitch.org
Ok that’s enough Yiftach... No need to me a schmuck... Tony asked that the previous thread die for a reason... There is no policy about don’t say something different then what we did, when the fact is there are probably 10 different ways to do most things actually working within the framework that is freeswitch.... What you were asked to do is quit advocating for implementing things that break the established protocols... Enough is enough let it die... Further comments on that subject will result in moderation... K On 11/18/12 2:04 PM, "Yiftach Golan" <yiftah@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 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@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@gmail.com <http://8f27e956@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@gmail.com <http://8f27e956@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@besharp.at <http://gerald.weber@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@lists.freeswitch.org <http://freeswitch-users-bounces@lists.freeswitch.org> [mailto:freeswitch-users-bounces@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@freeswitch.org <http://consulting@freeswitch.org> http://www.freeswitchsolutions.com
FreeSWITCH-powered IP PBX: The CudaTel Communication Server http://www.cudatel.com
Official FreeSWITCH Sites http://www.freeswitch.org http://wiki.freeswitch.org http://www.cluecon.com
FreeSWITCH-users mailing list FreeSWITCH-users@lists.freeswitch.org <http://FreeSWITCH-users@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@freeswitch.org <http://consulting@freeswitch.org> http://www.freeswitchsolutions.com
FreeSWITCH-powered IP PBX: The CudaTel Communication Server http://www.cudatel.com
Official FreeSWITCH Sites http://www.freeswitch.org http://wiki.freeswitch.org http://www.cluecon.com
FreeSWITCH-users mailing list FreeSWITCH-users@lists.freeswitch.org <http://FreeSWITCH-users@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
On 18 November 2012 15:04, Yiftach Golan <yiftah@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@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@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@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@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@lists.freeswitch.org [ mailto:freeswitch-users-bounces@lists.freeswitch.org<freeswitch-users-bounces@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@freeswitch.org http://www.freeswitchsolutions.com
FreeSWITCH-powered IP PBX: The CudaTel Communication Server http://www.cudatel.com
Official FreeSWITCH Sites http://www.freeswitch.org http://wiki.freeswitch.org http://www.cluecon.com
FreeSWITCH-users mailing list FreeSWITCH-users@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@freeswitch.org http://www.freeswitchsolutions.com
FreeSWITCH-powered IP PBX: The CudaTel Communication Server http://www.cudatel.com
Official FreeSWITCH Sites http://www.freeswitch.org http://wiki.freeswitch.org http://www.cluecon.com
FreeSWITCH-users mailing list FreeSWITCH-users@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@freeswitch.org http://www.freeswitchsolutions.com
FreeSWITCH-powered IP PBX: The CudaTel Communication Server http://www.cudatel.com
Official FreeSWITCH Sites http://www.freeswitch.org http://wiki.freeswitch.org http://www.cluecon.com
FreeSWITCH-users mailing list FreeSWITCH-users@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@freeswitch.org http://www.freeswitchsolutions.com
FreeSWITCH-powered IP PBX: The CudaTel Communication Server http://www.cudatel.com
Official FreeSWITCH Sites http://www.freeswitch.org http://wiki.freeswitch.org http://www.cluecon.com
FreeSWITCH-users mailing list FreeSWITCH-users@lists.freeswitch.org http://lists.freeswitch.org/mailman/listinfo/freeswitch-users UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users http://www.freeswitch.org
On 18 November 2012 10:41, Ken Rice <krice@freeswitch.org> wrote:
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...
The offered sql "upsert" stmt pair achieves natively what you're saying, without the need for discrete check and delete and then insert logic c- or p-language. The sql is undergoing conversational refinement (than's Gerald) to -- hopefully -- alight on the portable sql. The reflection version of your statement is that in order for -- the user -- to have harmonized hash-db behavior then -- the user -- has to do all you prescribe -- in script or xml -- to one-only method (which is, in fact, I've done now in my running use of persistent storage db dp_tool).
However, Keep in mind that what you are calling a bug,
Two things (1) In the op, I did concede "NOTWITHSTANDING reasons-unknown-to-me;" and (2) I did not call it a "bug." I, (we), were discussing harmonization of 'hash' and 'db' with specific regard to the INSERT method -- GIVEN THAT the unbalanced db user interface where SELECT method RETURNS one record only and DELETE method shot gun removes ALL the realm/data_key records. There *may* be compelling reasons not to. The "almost" work-a-like nature of the hash-db tools /suggests/ to me that possibly at the once-upon-a-time of code commit an sql technique just simply wasn't then known. Perhaps -- and possibly a still-fledgling perhaps at that -- a sql technique has emerged. Perhaps not. Whatever, I guess, if there's no traction, then there's no traction. It's a common-enough sql challange that, perhaps, the sql fragments will help someone somewhere in the future when google sussess this convo thread. Cheers, On 11/18/12 7:26 AM, "Scott" <8f27e956@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.
:-)
I think this is already the intended behavior of the db app. under insert it does the following: sql = switch_mprintf("delete from db_data where realm='%q' and data_key='%q'", argv[1], argv[2]); then: sql = switch_mprintf("insert into db_data (hostname, realm, data_key, data) values('%q','%q','%q','%q');", globals.hostname, argv[1], argv[2], argv[3]); If you are getting differing results, can you report it under Jira and post examples on how to reproduce using GIT HEAD so we can get to the bottom of it. There must be a bug if this is happening. Also .. Yiftach Golan. Why do you continue to make childish comments? Especially on other people's thread. Asking people to go offline with you so you can feed them FUD about how we don't listen to people, meanwhile here we are every day answering hundreds of emails. Do you really not understand? I am seriously trying to help you here. You need to learn a few things about etiquette to work in a community setting. There is a difference between saying your opinion and arguing a moot point into the ground when its not even your thread...... Being annoyed because we want to use Jira which is the intended resource for development and issue tracking makes no sense and is a bit selfish. If we are going to share our project with you, the least you can do is follow our minimal set of guidelines to make it easier for a handful of people to manage a community of several thousand people..... =/ On Sat, Nov 17, 2012 at 7:42 PM, Scott <8f27e956@gmail.com> wrote:
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@freeswitch.org http://www.freeswitchsolutions.com
FreeSWITCH-powered IP PBX: The CudaTel Communication Server http://www.cudatel.com
Official FreeSWITCH Sites http://www.freeswitch.org http://wiki.freeswitch.org http://www.cluecon.com
FreeSWITCH-users mailing list FreeSWITCH-users@lists.freeswitch.org http://lists.freeswitch.org/mailman/listinfo/freeswitch-users UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users http://www.freeswitch.org
-- Anthony Minessale II FreeSWITCH http://www.freeswitch.org/ ClueCon http://www.cluecon.com/ Twitter: http://twitter.com/FreeSWITCH_wire AIM: anthm MSN:anthony_minessale@hotmail.com GTALK/JABBER/PAYPAL:anthony.minessale@gmail.com IRC: irc.freenode.net #freeswitch FreeSWITCH Developer Conference sip:888@conference.freeswitch.org googletalk:conf+888@conference.freeswitch.org pstn:+19193869900
participants (6)
-
Anthony Minessale
-
Gerald Weber
-
Ken Rice
-
S. Scott
-
Scott
-
Yiftach Golan