How can I create a JavaScript function from SQL where clause to pass it into filter function of JavaScript arrays as a predicate?

Question

Scenario:

I've a JavaScript based application which uses web sql database to store its data

I've developed a custom entity set.

It has a filter method which accepts string that contains sql query.

for example: People.filter("Name = 'Test' and ...")

My entity sets have two mode in their behaviors, InMemory & NonInMemory

InMemory: filter method will filter the source array in memory, without round trip to db.

NonInMemory: filter method will read the source from db again with new filter.

To have an easier development, I want to make differences of these behaviors from developers transparent.

And I want to make my filter method works on both modes (InMemory & NonInMemory) with the same code.

I've thousands of these filters, which are working fine in .net & sql lite.

and I want to migrate them to JavaScript & web sql as easy as possible.

My question:

Is there any JavaScript library that can handle this situation ?

Which accepts Sql queries and create a JavaScript function as a predicate for me ?

Thanks

Answer 1

i am actually working on an sql-based project for javascript that uses a generated function for the where clause. Part of this is converting the SQL syntax differences to JS where feasable (AND>&&, etc), and the other part is to provide SQL functions to the clauses.The project is not complete and does not aim for 100% or even 80% compatibility, just to allow the most useful sql features and syntax to work in javascript. the where-function making routine below is not a parser or complex AST builder, just a semi-naive yet fast and sufficient string transformer.

it's suppports a wide chunk of where-ish SQL now and should be quite simple to expand upon yourself; not a lot of black magic going on here.

the resulting functions are ideal to filter() an array of objects.

here is a port of the relevant piece of the project, which is quite large, the where-clause builder:

// cache RegExps pseudo-globally for much better perf in query routine in webkit:
var rxOr = /\sOR\s/g,
    rxAnd = /\sAND\s/g,
    rxIn = /\sIN\(([\w\.\,\s]+)\)/g,
    rxSep = /\s*\,\s*/,
    rxDoubleEqual = /([^=])=([^=])/g,
    asRx = /\s+AS\s+/,
    eqRx = /(\w+)=/;
var fCache = {}; //

var SQLREPS = [
    [/([^=])=([^=])/g, "$1==$2"],
    [/\sAND\s/g, ") && ("],
    [/\sOR\s/g, ") || ("],
    [/\bUCASE\(/g, " ''.toUpperCase.call("],
    [/\bLCASE\(/g, " ''.toLowerCase.call("],
    [/\bUPPER\(/g, " ''.toUpperCase.call("],
    [/\bLOWER\(/g, " ''.toLowerCase.call("],
    [/\bINSTR\(/g, " 1+''.indexOf.call("],
    [/\bCONCAT\(/g, " ''.concat("],
    [/\bLTRIM\(/g, " ''.trimLeft.call("],
    [/\bRTRIM\(/g, " ''.trimRight.call("],
    [/\bTRIM\(/g, " ''.trim.call("],
    [/\bQUOTE\(/g, " JSON.stringify("],
    [/\bSPACE\(/g, " ' '.repeat("],
    [/\bREPLACE\(/g, " (function(s,n,r){return s.split(n).join('r');})("],
    [/\bRPAD\(/g, " (function(s,n,p){return (s+p.repeat(n)).slice(0,n)})("],
    [/\bASCII\(/g, " ''.charCodeAt.call("],
    [/\bBIN\(/g, " ''.charCodeAt.call("],
    [/\bLENGTH\(/g, " [].push.call("],
    [/\bSUBSTRING_INDEX\(/g, "(function(s,n,p){s=s.split(n);s=p>0?s.slice(0,p):s.slice(p);return s.join(n);})("],
    [/\bSUBSTRING\(/g, '(function(a,b,c){return b=[b>0?b-1:b],"".substr.apply(a,arguments.length==3?b.concat(c):b)})('],
    [/\bSUBSTR\(/g, '(function(a,b,c){return b=[b>0?b-1:b],"".substr.apply(a,arguments.length==3?b.concat(c):b)})('],
    [/\bMID\(/g, '(function(a,b,c){return b=[b>0?b-1:b],"".substr.apply(a,arguments.length==3?b.concat(c):b)})('],
    [/\bLOCATE\(/g, " (function(t,s,n){return 1+s.indexOf(t,n)})("],
    [/\bPOSITION\(/g, " (function(t,s,n){return 1+s.indexOf(t,n)})("],
    [/\bFIND_IN_SET\(/g, "(function(s,l){return l.split(',').indexOf(s)+1})("],
    [/\bREVERSE\(/g, " (function(s){return s.split('').reverse().join('');})("],
    [/\bLEFT\(/g, "(function(s,n){return s.slice(0,n)})("],
    [/ NOT /g, " ! "]
];



function rewrite(s) {

    var os = s;

    SQLREPS.forEach(function(a) {
        s = s.replace(a[0], a[1]);
    });

    s = s.replace(rxIn, function repIn(j, a) {
        return " in { " + a.split(rxSep).map(function mapIn(a) {
            return JSON.stringify(a)
        }).join(":1,") + ":1} ";
        return a;
    });

    return s;
}

function Function2(a, b, blnNoRewrite) {
    var c;
    if (!b.match(/return/)) {
        b = "return " + b;
    }
    if (c = fCache[a + b]) {
        return c;
    }
    return fCache[a + b] = Function(a, blnNoRewrite ? b : rewrite(b));
}


function sql(term) {
    return Function2("me,index,all", "return (" + rewrite(term) + ");");
}




//example strings and resulting functions:
sql(" UPPER(  me.gender  ) =='F' "); // function anonymous(me,index,all){return(''.toUpperCase.call(me.gender)=='F');}

sql(" me.gender IN(M,F,O)"); //  function anonymous(me,index,all){return(me.gender in{"M":1,"F":1,"O":1});}

sql("(me.name> 'j' AND me.age > 50) &&  NOT me.inActive "); // function anonymous(me,index,all){return((me.name>'j')&&(me.age>50)&&!me.inActive);}

most of the js sql libs out there are somewhat skimpy on WHERE functionality and are hard to expand, hopefully you and others can get some use or inspiration from this. consider it public domain by author, me.

Answer 2

It seems the OP just wants to query a JavaScript array of object using a SQL where clause, whether or not he obtains the filter function.

AlaSQL is doing exactly that. However, it can be too big for your app bundle.

The maintainers of the package also note that (as of Dec 2019):

AlaSQL project is very young and still in an active development phase, therefore it may have bugs.