[Freeswitch-users] Suggestion to harmonize 'hash' & 'db' insert method
Ken Rice
krice at freeswitch.org
Mon Nov 19 00:16:15 MSK 2012
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 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
> 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 <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 at 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 at 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 at lists.freeswitch.org
>>>>> <http://freeswitch-users-bounces@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,
>>>>>
>>>>>
>>>>> _________________________________________________________________________
>>>>> Professional FreeSWITCH Consulting Services:
>>>>> consulting at freeswitch.org <http://consulting@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://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 at freeswitch.org <http://consulting@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://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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.freeswitch.org/pipermail/freeswitch-users/attachments/20121118/aec80745/attachment-0001.html
Join us at ClueCon 2011 Aug 9-11, 2011
More information about the FreeSWITCH-users
mailing list