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.

:-)