Is it possible to create a javascript User-defined function in sqlite
Yes. It is possible to call javascript functions
//(thanks to Mirnal Kant, SQLManager)//Version 2 -- Prevent Firefox crashing // -- Suspect a problem with continual creation of Regex objectsvar g_RegExpString = null;var g_RegExp = null;//functions to be created for the dbvar smDbFunctions = { // (0) = Regex Expression // (1) = Column value to test regexp: { onFunctionCall: function(val) { if (g_RegExp == null || val.getString(0) != g_RegExpString) { g_RegExpString = val.getString(0); g_RegExp = new RegExp(g_RegExpString); } if (val.getString(1).match(g_RegExp)) return 1; else return 0; } }}; after instantiating a SQLite instance:
Database.createFunction("REGEXP", 2, smDbFunctions.regexp);
What Noah is talking about has been included into SQLite Manager add-on for Firefox.
When you start this add-on, you can click the icon labeled f(x) 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:
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 );Inside that table you can define custom functions. Parameters will be passed as an array named aValues. For example:
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 ,'' );- if
argLength== -1, then there is no limit on the number of arguments. You can get the count withaValues.numEntries. - You can use
aValues.getTypeOfIndex(i)to know the type of the argument: 0 => NULL, 1 => Integer (aValues.getInt64(i)), 2 => Real (aValues.getDouble(i)), 3 => String, see example.
For aggregate functions you can use this._store as an initially empty array to push the elements during the onStepBody phase, and read from it in onStepFinal to compute the final result.
Below is a bash script that will create smFunctions.sqlite with some custom functions (this is a .dump of my own smFunctions.sqlite):
sqlite smFunctions.sqlite << EOFPRAGMA 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