StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>What Noah is talking about has been included into <a href="http://code.google.com/p/sqlite-manager/" rel="nofollow">SQLite Manager</a> add-on for Firefox.</p> <p>When you start this add-on, you can click the icon labeled <em>f(x)</em> to open the User-defined functions tab. From there, select a directory in which you have an SQLite database named smFunctions.sqlite, with the following schema:</p> <pre><code>CREATE TABLE "functions" ( "name" TEXT PRIMARY KEY NOT NULL , "body" TEXT NOT NULL , "argLength" INTEGER , "aggregate" INTEGER NOT NULL DEFAULT 0 , "enabled" INTEGER NOT NULL DEFAULT 1 , "extraInfo" TEXT ); CREATE TABLE "aggregateFunctions" ( "name" TEXT PRIMARY KEY NOT NULL , "argLength" INTEGER , "onStepBody" TEXT , "onFinalBody" TEXT , "enabled" INTEGER NOT NULL DEFAULT 1 , "extraInfo" TEXT ); </code></pre> <p>Inside that table you can define custom functions. Parameters will be passed as an array named <code>aValues</code>. For example:</p> <pre><code>INSERT INTO "functions" ("name", "body", "argLength", "aggregate", "enabled", "extraInfo") VALUES('regexp_replace' ,'// exemple : SELECT regexp_replace(''FOOBAR'',''o+'',''a'',''gi'') var input = new String(aValues.getString(0)); var regex = new String(aValues.getString(1)); var substitute = new String(aValues.getString(2)); var flags = new String(aValues.getString(3)); return input.replace(new RegExp(regex,flags), substitute); ' ,4 ,0 ,1 ,'' ); </code></pre> <ul> <li>if <code>argLength</code> == -1, then there is no limit on the number of arguments. You can get the count with <code>aValues.numEntries</code>.</li> <li>You can use <code>aValues.getTypeOfIndex(i)</code> to know the type of the argument: 0 => NULL, 1 => Integer (<code>aValues.getInt64(i)</code>), 2 => Real (<code>aValues.getDouble(i)</code>), 3 => String, see example.</li> </ul> <p>For aggregate functions you can use <code>this._store</code> as an initially empty array to push the elements during the onStepBody phase, and read from it in <code>onStepFinal</code> to compute the final result.</p> <p>Below is a bash script that will create <code>smFunctions.sqlite</code> with some custom functions (this is a <code>.dump</code> of my own smFunctions.sqlite):</p> <pre><code>sqlite smFunctions.sqlite << EOF PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; CREATE TABLE "functions" ("name" TEXT PRIMARY KEY NOT NULL, "body" TEXT NOT NULL, "argLength" INTEGER, "aggregate" INTEGER NOT NULL DEFAULT 0, "enabled" INTEGER NOT NULL DEFAULT 1, "extraInfo" TEXT); INSERT INTO "functions" VALUES('accumulate','var sum = 0; for (var j = 0; j < aValues.numEntries; j++) { sum += aValues.getInt32(j); } return sum; ',-1,0,1,NULL); INSERT INTO "functions" VALUES('concatenate','var valArr = []; var delim = new String(aValues.getString(0)); for (var j = 1; j < aValues.numEntries; j++) { switch (aValues.getTypeOfIndex(j)) { case 0: //NULL valArr.push(null); break; case 1: //INTEGER valArr.push(aValues.getInt64(j)); break; case 2: //REAL valArr.push(aValues.getDouble(j)); break; case 3: //TEXT default: valArr.push(aValues.getString(j)); } } return valArr.join(delim);',-1,0,1,NULL); INSERT INTO "functions" VALUES('regexp_match','var regExp = new RegExp(aValues.getString(0)); var strVal = new String(aValues.getString(1)); if (strVal.match(regExp)) { return 1; } else { return 0; } ',2,0,1,NULL); INSERT INTO "functions" VALUES('regexp_replace','// exemple : regexp_replace(''toto'',''o+'',''a'',''g'') var input = new String(aValues.getString(0)); var regex = new String(aValues.getString(1)); var substitute = new String(aValues.getString(2)); var flags = new String(aValues.getString(3)); return input.replace(new RegExp(regex,flags), substitute); ',4,0,1,NULL); INSERT INTO "functions" VALUES('instr','var char = new String(aValues.getString(0)); var str = new String(aValues.getString(1)); return str.indexOf(char, 0) + 1;',2,0,1,NULL); INSERT INTO "functions" VALUES('rinstr','var char = new String(aValues.getString(0)); var str = new String(aValues.getString(1)); return str.lastIndexOf(char) + 1; ',2,0,1,NULL); CREATE TABLE "aggregateFunctions" ("name" TEXT PRIMARY KEY NOT NULL, "argLength" INTEGER, "onStepBody" TEXT, "onFinalBody" TEXT, "enabled" INTEGER NOT NULL DEFAULT 1, "extraInfo" TEXT); INSERT INTO "aggregateFunctions" VALUES('stdDev',1,'this._store.push(aValues.getInt32(0));','var iLength = this._store.length; let total = 0; this._store.forEach(function(elt) { total += elt }); let mean = total / iLength; let data = this._store.map(function(elt) { let value = elt - mean; return value * value; }); total = 0; data.forEach(function(elt) { total += elt }); this._store = []; return Math.sqrt(total / iLength);',1,NULL); INSERT INTO "aggregateFunctions" VALUES('longest_prefix',1,'this._store.push(aValues.getString(0));','if (this._store.length == 0) { return ""; } var prefix = this._store[0]; var prefixLen = prefix.length; for (var i = 1; i < this._store.length && prefixLen > 0; i++) { var word = this._store[i]; // The next line assumes 1st char of word and prefix always match. // Initialize matchLen to -1 to test entire word. var matchLen = 0; var maxMatchLen = Math.min(word.length, prefixLen); while (++matchLen < maxMatchLen) { if (word.charAt(matchLen) != prefix.charAt(matchLen)) { break; } } prefixLen = matchLen; } return prefix.substring(0, prefixLen);',1,NULL); COMMIT; EOF </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload