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.
