<html><head></head><body bgcolor="#FFFFFF"><div><div>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.</div>
<div><br></div><div>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.</div>
<div><br></div><div>Darn shame sqlite3 doesn't support stored procedures 'cause then it's more easily abstracted for portability up the SQL server engine curve.</div><div><br>On 2012-11-18, at 3:53, Gerald Weber <<a href="mailto:gerald.weber@besharp.at">gerald.weber@besharp.at</a>> wrote:<br>
<br></div><div></div><blockquote type="cite"><div><meta http-equiv="Content-Type" content="text/html; charset=us-ascii"><meta name="Generator" content="Microsoft Word 14 (filtered medium)"><style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman","serif";}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
span.E-MailFormatvorlage17
{mso-style-type:personal-reply;
font-family:"Calibri","sans-serif";
color:#1F497D;}
.MsoChpDefault
{mso-style-type:export-only;
font-family:"Calibri","sans-serif";
mso-fareast-language:EN-US;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:70.85pt 70.85pt 2.0cm 70.85pt;}
div.WordSection1
{page:WordSection1;}
--></style><div class="WordSection1"><p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d">AFAIK, „if not exists“ and „or replace“ is not ansi sql syntax, so you will get in trouble if </span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d">someone uses odbc to a db that doesn’t support non standard syntax.</span></p><p class="MsoNormal">
<span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d"> </span></p><p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d">The insert or replace could be replaced with the merge statement.</span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d"> </span></p><p class="MsoNormal"><b><span lang="DE" style="font-size:10.0pt;font-family:"Tahoma","sans-serif"">Von:</span></b><span lang="DE" style="font-size:10.0pt;font-family:"Tahoma","sans-serif""> <a href="mailto:freeswitch-users-bounces@lists.freeswitch.org">freeswitch-users-bounces@lists.freeswitch.org</a> [mailto:<a href="mailto:freeswitch-users-bounces@lists.freeswitch.org">freeswitch-users-bounces@lists.freeswitch.org</a>] <b>Im Auftrag von </b>Scott<br>
<b>Gesendet:</b> Sonntag, 18. November 2012 02:42<br><b>An:</b> FreeSWITCH Users Help<br><b>Betreff:</b> [Freeswitch-users] Suggestion to harmonize 'hash' & 'db' insert method</span></p><p class="MsoNormal">
</p><p class="MsoNormal" style="margin-bottom:12.0pt">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. <br>
<br>NOTWITHSTANDING reasons-unknow-to-me, a remedy to harmonize the non-persistent 'hash' with the persistent 'db' I *think* is straight forward ...<br><br>(1) At the call_limit.db schema-level, a one-time create UNIQUE COMPOUND index, as follows,<br>
<br>CREATE UNIQUE INDEX IF NOT EXISTS 'idx_db_data_HostRealmDK' ON 'db_data' ('hostname','realm','data_key');<br><br>(1) At the c-language embedded SQL string-level,<br><br>change the existing INSERT to be the following,<br>
<br>INSERT OR REPLACE INTO db_data (hostname,realm,data_key,data) VALUES (%s,%s,%s,%s);<br><br>The index will help speed UP the reads. Neither change is "exotic" and should be portable across standard sql implementations.<br>
<br>;-) ... thoughts ?<br><br>Thanks,<br><br><br></p></div></div></blockquote><blockquote type="cite"><div><span>_________________________________________________________________________</span><br><span>Professional FreeSWITCH Consulting Services:</span><br>
<span><a href="mailto:consulting@freeswitch.org">consulting@freeswitch.org</a></span><br><span><a href="http://www.freeswitchsolutions.com">http://www.freeswitchsolutions.com</a></span><br><span></span><br><span>FreeSWITCH-powered IP PBX: The CudaTel Communication Server</span><br>
<span><a href="http://www.cudatel.com">http://www.cudatel.com</a></span><br><span></span><br><span>Official FreeSWITCH Sites</span><br><span><a href="http://www.freeswitch.org">http://www.freeswitch.org</a></span><br><span><a href="http://wiki.freeswitch.org">http://wiki.freeswitch.org</a></span><br>
<span><a href="http://www.cluecon.com">http://www.cluecon.com</a></span><br><span></span><br><span>FreeSWITCH-users mailing list</span><br><span><a href="mailto:FreeSWITCH-users@lists.freeswitch.org">FreeSWITCH-users@lists.freeswitch.org</a></span><br>
<span><a href="http://lists.freeswitch.org/mailman/listinfo/freeswitch-users">http://lists.freeswitch.org/mailman/listinfo/freeswitch-users</a></span><br><span>UNSUBSCRIBE:<a href="http://lists.freeswitch.org/mailman/options/freeswitch-users">http://lists.freeswitch.org/mailman/options/freeswitch-users</a></span><br>
<span><a href="http://www.freeswitch.org">http://www.freeswitch.org</a></span><br></div></blockquote></div><div><span></span></div></body></html>