<div class="gmail_quote">On 18 November 2012 10:41, Ken Rice <span dir="ltr"><<a href="mailto:krice@freeswitch.org" target="_blank">krice@freeswitch.org</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div>
<font face="Monaco, Courier New"><span style="font-size:11pt">
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...</span></font></div></blockquote><div><br>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.<br>
<br>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).<br>
<br></div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div><font face="Monaco, Courier New"><span style="font-size:11pt">However, Keep in mind that what you are calling a bug, <br>
<div><div class="h5"><br></div></div></span></font></div></blockquote><div class="h5"><br>Two things (1) In the op, I did concede "<font face="Monaco, Courier New"><span style="font-size:11pt">NOTWITHSTANDING reasons-unknown-to-me;"</span></font> and (2) I did not call it a "bug."<br>
<br>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.<br>
<br>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. <br>
<br>Cheers,<br>
<br>
<br>
On 11/18/12 7:26 AM, "Scott" <<a href="http://8f27e956@gmail.com" target="_blank">8f27e956@gmail.com</a>> wrote:<br>
<br>
</div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div><font face="Monaco, Courier New"><span style="font-size:11pt"></span></font><blockquote><div><div class="h5">
<font face="Monaco, Courier New"><span style="font-size:11pt">OK, with the one-time </span></font><span style="font-size:11pt"><font face="Courier New">CREATE UNIQUE INDEX on 'table' (hostname,realm,data_key); </font><font face="Monaco, Courier New">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,<br>
<br>
</font><font face="Courier New">BEGIN TRANSACTION; <br>
UPDATE 'table' SET data_value='value_data' WHERE hostname='value_hostname' AND realm='value_realm' AND data_key='value_data_key' ;<br>
INSERT INTO 'table' (hostname,realm,data_key,data_value) SELECT 'value_hostname', 'value_realm', 'value_data_key', 'value_data_value'<br>
WHERE NOT EXISTS (SELECT 1 FROM 'table' WHERE hostname='value_hostname' AND realm='value_realm' AND data_key='value_data_key' );<br>
COMMIT; <br>
</font><font face="Monaco, Courier New"><br>
The two WHERE clauses must be identical and the variables should be in the same order as the unique compound index is ordered.<br>
The UPDATE fails quietly and benignly if the record does NOT already exist and succeeds if it does.<br>
The INSERT ... WHERE NOT EXISTS stmt succeeds if the record does NOT already exist fails quietly and benignly if it does. <br>
The BEGIN and COMMIT aren't typically necessary but they're over-safe just in case the same record is attempted by another thread.<br>
<br>
I think this get's it done.<br>
<br>
:-)</font></span><br><blockquote><span style="font-size:11pt"><font face="Monaco, Courier New"></font></span><blockquote><span style="font-size:11pt"></span><font face="Monaco, Courier New"><font><span style="font-size:10pt"></span></font><span style="font-size:11pt"><br>
</span></font></blockquote></blockquote></div></div><font face="Monaco, Courier New"><span style="font-size:11pt"></span></font></blockquote><span class="HOEnZb"><font color="#888888"><font><font face="Consolas, Courier New, Courier"><span style="font-size:10pt"></span></font></font><font face="Monaco, Courier New"><span style="font-size:11pt">
</span></font>
</font></span></div></blockquote></div>