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

[Scott]

On 18 November 2012 10:41, Ken Rice <krice at 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 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.
>
> :-)
>
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.freeswitch.org/pipermail/freeswitch-users/attachments/20121118/16622226/attachment.html