Create user defined function on mozIStorageConnection

Add-ons Development
#1

I am trying to define some functions in SQLite. I have a working mozIStorageConnection instance, on which I can perform various queries. I am trying to create a udf like this:

var func = new Function('aArguments', 'var value, result; value = aArguments.getInt32(0); result = value * value; return result;');
conn.createFunction('myfunc', 1, func);

This runs without throwing. Then if I try to execute a query like:

SELECT myfunc(2)

Firefox crashes immediately. Which is odd, considering in the same profile I have SQLitemanager installed (and as far as I can tell it creates udfs in basically the same way) and udfs defined there work just fine.

What am I missing?

#2

Did you look into sqlite.jsm? https://developer.mozilla.org/en-US/docs/Mozilla/JavaScript_code_modules/Sqlite.jsm#close()

It might help I’m not sure. But I hear its the recommended way to go if you want to use sqlite.

For your above example I think you can do it like this. Don’t do it like that, why are you enclosing the function body contents with single quotes.

Do it how everyone else does:

You should be able to put in conn.createStatement and conn.executeStatuement calls in there no problem.

Github is my friend: https://github.com/search?l=javascript&q=conn+createFunction+-filename%3Atest_access_db_connection&ref=searchresults&type=Code&utf8=✓

#3

I am using Sqlite.jsm otherwise (which uses mozIStorageAsyncConnection internally (mostly the same interface as mozIStorageConnection (eg.: it also has a createFunction() method))). This is just to see whether I could create udfs or not, and if yes, then measure their speed versus processing values after they have been fetched from SQLite; but it’s neither here, nor there.

I was using the function constructor, because I couldn’t get it working with function expressions, and then I saw that sqlitemanager uses the constructor version, so I thought maybe there is some quirk at work here, so I tried that as well, but I cannot get it working either way.

I have set up the following basic example (which still does not work with udfs (but works with other statements like ‘SELECT 1’, etc)):

test.xul:

<?xml version="1.0" encoding="UTF-8"?>
<?xml-stylesheet type="text/css" href="chrome://global/skin/"?>
<window id="udf-test" title="UDF test" orient="vertical" xmlns="http://www.mozilla.org/keymaster/gatekeeper/there.is.only.xul">
    <script type="application/javascript" src="chrome://path/to/test.js"/>
    <button label="UDF!" oncommand="udfTest.test();"/>
</window>

test.js:

var udfTest = (function() {
    const Cu = Components.utils;
    
    const utils = {};
    Cu.import('resource://gre/modules/Services.jsm', utils);
    Cu.import('resource://gre/modules/FileUtils.jsm', utils);
    
    var main = {
        test: function test() {
            var filePath,
                dbConn,
                stmt;
            try {
                filePath = '/path/to/test.sqlite';
                dbConn = utils.Services.storage.openDatabase(
                        new utils.FileUtils.File(filePath)
                    );
                dbConn.createFunction('joinValues', -1, function (aValues) {
                        var valArr = [];
                        for (var j = 0; 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(',');
                    });
                stmt = dbConn.createStatement('SELECT joinValues(1, 2, 3) as result');
                stmt.executeAsync({
                        handleResult: function(aResultSet) {
                            for (let row = aResultSet.getNextRow();
                                     row;
                                     row = aResultSet.getNextRow()) {
                                
                                let value = row.getResultByName("result");
                                console.log('result', value);
                            }
                        },
                        
                        handleError: function(aError) {
                            console.log("Error: " + aError.message);
                            dbConn.asyncClose(function() {
                                    console.log('dbConn closed');
                                });
                        },
                        
                        handleCompletion: function(aReason) {
                            if (aReason != Components.interfaces.mozIStorageStatementCallback.REASON_FINISHED) {
                                console.log("Query canceled or aborted!");
                            }
                            dbConn.asyncClose(function() {
                                    console.log('dbConn closed');
                                });
                        }
                    });
            }
            catch (ex) {
                console.log(ex);
                dbConn.asyncClose(function() {
                        console.log('dbConn closed');
                    });
            }
        }
    };
    return  {
        test: main.test.bind(main)
    }
})();

I have copied the joinValues function from sqlitemanager (where it works as expected). With this setup, as soon as I press the button, firefox crashes. I still don’t understand why it is not working, or how could I get more information about why it is not working. It is just frustrating.

Edit:
The statement gets prepared (if I don’t create the function beforehand, then createStatement throws, otherwise it does not). The crash happens when I try to execute the statement.

Edit 2:
It seems like statements with udfs only work with synchronous execution : (
Both mozIStorageConnection.executeAsync() and mozIStorageStatement.executeAsync() (and mozIStorageConnection.executeSimpleSQL() as well) crashes, when the statement contains udfs; only mozIStorageStatement.executeStep() seems to work with udfs. Should I report it as a bug?

1 Like
#4

I learned a lot from reading that. Awesome research there. Hopefully some sqlite experts can help you out. I’ll msg some on irc i think might know.

#5

I reported a bug, if anyone is interested in the issue.

1 Like