Is it possible to create a javascript User-defined function in sqlite

Question

BACKGROUND:

• Firefox 3 includes SQLite version 3.5.9. Firefox also allows extensions, which are written in javascript and can call the embedded SQLite engine.

• As expected, executing the following SQL statement 'SELECT "TEXT" REGEXP "T*";' gives an error, since there is no REGEXP function natively included in SQLite.

• javascript includes a built in regexp function.

• SQLite allows loadable extensions via SELECT load_extension('filename');

QUESTION: Is it possible to a load an extension in SQLite which is written in javascript that can do REGEXP?

Answer 1

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 objects

var g_RegExpString = null;
var g_RegExp = null;

//functions to be created for the db
var 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);