forks
/
arsd
mirror of https://github.com/adamdruppe/arsd.git
1
0
Fork 0
Code Issues Packages Projects Releases Wiki Activity
arsd/querygenerator.d

553 lines
24 KiB
D
Raw Permalink Blame History

/**
* Create SQL queries from string containing D expressions.
* This module allows you to generate SQL from a template, containing D expressions. Depending on these expressions
* parts of the template will be included in the SQL or not.
* Authors: Robert Klotzner, robert.klotzner at gmail.com
* Date: January 7, 2013
* License: GNU General Public License version 3 <http://www.gnu.org/licenses/>
* Copyright: 2013 Robert Klotzner
* Bugs: Unicode not really supported. I haven't really explored that yet, but
* I believe that at the moment string processing will fail for multibyte
* utf-8 characters in the input string.
*/
module arsd.querygenerator;
import std.exception;
import std.uni;
import std.string;
import std.variant;
import std.conv;
import std.typetuple;
/**
* The generated query + args.
*
* It offers support for concatenation, so you can create your query in parts and concatenate them afterwards.
* Ths string prepend will be inserted inbetween two CreatedQuery.query strings at concatation if and only if both query strings are non empty.
* The resulting CreatedQuery has a prepend string equal to the one of the left side of the '~' operation.
* Beware that because of the handling of the prepend string, the result of multiple concatenations depends on the order of evaluation (if empty strings are involved).
* You might need parantheses or splitting
* it up in multiple statements to achieve the desired effect.
*/
struct CreatedQuery {
/**
* Concatenation with automatically inserted prepend string in between.
* The prepend string gets inserted inbetween the two query strings if and only if both query strings are non empty.
* The prepend string of the resulting object is the one of the left object. Thus the resulting object depends on the order of execution if empty strings are involved.
* See the unittest to this struct for details.
*/
CreatedQuery opBinary(string op)(CreatedQuery right) if(op=="~") {
CreatedQuery res=this;
res~=right;
return res;
}
CreatedQuery opBinary(string op)(string right) if(op=="~") {
CreatedQuery res=this;
res.query~=right;
return res;
}
CreatedQuery opBinaryRight(string op)(string left) if(op=="~") {
CreatedQuery res=this;
if(left.length && query.length)
left~=prepend;
res.query=left~res.query;
return res;
}
ref CreatedQuery opOpAssign(string op)(string right) if(op=="~") {
query~=right;
return this;
}
ref CreatedQuery opOpAssign(string op)(CreatedQuery right) if(op=="~") {
query~=(query.length && right.query.length) ? right.prepend : "";
query~=right.query;
args~=right.args;
return this;
}
/// Currently not const because of bug:
/// http://d.puremagic.com/issues/show_bug.cgi?id=8759
bool opEquals( CreatedQuery other) {
return query==other.query && args==other.args && prepend==other.prepend;
}
string query;
Variant[] args;
string prepend;
}
unittest {
auto q1=CreatedQuery("select * from table");
CreatedQuery q2=CreatedQuery("", [], " where ");
auto q3=CreatedQuery("col1=? and col2=?", [Variant(7), Variant("huhu")], " and ");
auto q4=(q1~(q2~q3));
q1~=q2~q3;
import std.stdio;
writefln("q4: %s, q1: %s", q4, q1);
assert(q4==q1);
assert(q1==CreatedQuery("select * from table where col1=? and col2=?", [Variant(7), Variant("huhu")], ""));
}
/**
* This method generates D code from the preSql data, which can be mixed in for generating the resulting sql and the resulting Variant[] array.
* The variable sql_ will contain the generated SQL.
* The variable args_ is the Variant[] array.
* Use createQuery for directly generating the resulting SQL and args.
* Params:
* preSql = The to be processed SQL containing D expressions in #{} blocks, sub blocks in {} and declarations in ${}.
* Returns: D code which can be mixed in.
**/
string createQueryGenerator(string preSql) {
string out_cmd;
out_cmd~="string sql_;\n";
out_cmd~="Variant[] args_;\n";
out_cmd~="with(data_) {\n";
out_cmd~=preSqlParser(preSql);
out_cmd~="\n}";
return out_cmd;
}
/**
* Uses createQueryGenerator for actually doing the job.
* data_ will be made available with D's 'with' statement to the embedded code in queryString. So you can access the elements just as regular variables.
*
* Params:
* queryString = An SQL template. An SQL template consists of nested blocks. The
* uppermost block is the queryString itself, you create subblocks by
* enclosing it in braces '{}'.
__Declaration part__
Each block might start with a declaration part. It does
so if the first character in the block is a '$'. The
declaration part ends with a colon ':'.
It consists of declarations which start with '${' and end
with '}'. Two types of declarations are allowed:
1. variable assignment of the form: ${a=5} This will
result in a D expression in the generated code of the
form 'auto a=5;'.
2. Foreach loop declaration. This one might only occur
once in the declaration part and causes the block in
which it is declared to be executed in a loop.
It looks like: ${a in someArray}
3. Special assignment of ${queryGenSep=" or "} : This one
is specially treated. If you specified a loop the
generated string of each iteration will be concatenated
and the queryGenSep will be used to separate them. If you
don't specify one, the default will be " or ".
Variable assignments before the optional loop declaration
will be declared outside the loop, the ones after the
loop declaration will be declared within the loop and
might access the loop variable, ('a' in our case).
Multiple declarations of the form '${}' might be present
within the declaration part, they may be separated by
white space.
__Body part__
If no declaration part is present, then the whole content
of the block is the body part. (This is, if the first
character is no '$'.) Otherwise it starts after the colon
at the end of the declaration part.
Everything in the body part will be echoed to the
resulting SQL so you can just write plain SQL now. There
are only a few exceptions:
1. A '{' will introduce a child block.
2. A '#{' will start a D expression, which ends with
another '}'
3. A '$' will trigger an error as it is only valid in the
declaration part.
4. SQL string which can either start with " or with '. It
might contain any of '{' '}' '$' '#' they all will be
ignored within a string. Apart from this, no escaping of
these characters is possible at the moment.
__D expression__
A D expression might occur any amount of times within the
body part of a block. It contents will be evaluated as D
code. Which must evaluate to some value. If this value is
different from the value's types init value it is
considered valid, then it will be included in the Variant[] array and a '?'
gets inserted into the resulting SQL. If the returned value equals the type's
init value then no '?' gets inserted and the value will not be present in the
args array.
So if the D expression evaluates to a string and is the
empty string, it will not be included.
The expression is contained within #{ D expression }.
The D code has access to the passed params via params[0]
.. params[n], all declarations made in the declarations
parts of the blocks above it and the contents of the data_ struct.
It is made available with D's 'with' statement so
data_.foo will just be 'foo'.
__Blocks__
Blocks are the building blocks of the resulting
expression. If a block contains a D expression or a
subblock which contains D expressions, then its contents
will only be included in the output if at least one of
the D expressions are valid or if it contains a text only sub block.
So if you have a SQL expression of the
form {someColumn=#{some D code}} there will be no output
at all if #{some D code} was not valid. If a block does
not contain any D code and neither do any sub blocks then
its output will be included in the output of the
containing block. If you want to ensure that a particular
text does not get dropped, include it in its own block.
The second property that makes blocks useful is that if a
block produces no output, for the above mentioned
reasons, then the text before it will be dropped if there
were no valid sub blocks found already in the parent block.
It will also be dropped if the preceding block vanished. This way you can
concatenate blocks with ' and ' and ' or ' and they will
be dropped if not applicable. A leading ' where ' on the
other hand would not be dropped in any case, except the
whole output vanishes.
For examples, see the unittests, a more complex example, I used in reality is here:
`({${db=params[0]} : `
`{`
`${d in datespan} ${arData=db.autoRunData(d.autorun)}: {({date>=#{d.from}} and {date<=#{d.to}} and `
`{({task_run.id>=#{arData.firstId==long.max ? 0 : arData.firstId}} and {task_run.id<=#{arData.lastId}} and {comment like #{d.autorun.length ? "%:Autorun:"~d.autorun~":Autorun:%" : ""}})})}`
`}`
`})`
Right at the start I am starting the uppermost block with a
'(' which means it has no declaration part, then I start
the first sub block, followed by declaration I needed.
Then I start another block with '{' followed by a loop
declaration (d will iterate over the datespan array which
comes from the data_ struct) followed by an inner
variable declaration of arData, you can see both d (the
looping variable) and db from the outer block are
accessible. Afterwards the body starts with a
block containing '(' and subblocks of comparisons with a D
expression like {({date>=#{d.from}})}.
If all D expressions in the block like d.from evaluate to
an empty string, the whole containing block will not produce
any output. So not even '()' this is because they are
enclused in a subblock. The 'and' between {date>=#{d.from}}
and {date<=#{d.to}} for example will only appear in the
output if both d.from and d.to contained a valid non empty
string.
The outputs of each loop iteration will be separated with " or " by default,
you can change this, by setting queryGenSep in the declaration part.
data_ = The contents of data_ will be made available to the D expressions and declarations in the queryString. (The code gets embedded withing a with(data_) { ... })
params = Additional data you need in the queryString. You can access it via params[0], params[1], ...
If you happen to have a member of data_ which is named params, you will have to access it via data_.params.
*/
CreatedQuery createQuery(string queryString, StructT, Params...)(StructT data_, Params params) {
debug(queryGenerator) import std.stdio;
mixin(createQueryGenerator(queryString));
debug(queryGenerator) writeln("Generated: ", createQueryGenerator(queryString));
return CreatedQuery(sql_, args_);
//return CreatedQuery.init;
}
unittest {
import std.stdio;
struct Test1 {
string a;
string b;
}
struct Test {
Test1[] foo;
string bar;
int k;
}
Test t1;
CreatedQuery myQuery(Test t1) {
return createQuery!`select * from testtable where
{({${queryGenSep=" or "} ${f in foo} : ({col1=#{f.a}} and {col2=#{f.b}})})} or
{col3>#{k}}`(t1);
}
auto res=myQuery(t1);
writeln("Result with empty args: ", res.query);
assert(res.args==[]);
t1.k=9;
res=myQuery(t1);
writefln("Result with k set: %s, args: %s", res.query, res.args);
assert(res.args==[Variant(9)]);
t1.foo~=Test1(string.init, "Hallo du da!");
res=myQuery(t1);
writeln("Result with foo.b and k set: ", res.query);
assert(res.args==[Variant("Hallo du da!"), Variant(9)]);
t1.foo~=Test1("Hallo du da!", string.init);
res=myQuery(t1);
writeln("Result with foo0.b and foo1.a and k set: ", res.query);
assert(res.args==[Variant("Hallo du da!"), Variant("Hallo du da!"), Variant(9)]);
t1.foo~=Test1("Hello!", "Cu!");
res=myQuery(t1);
writeln("Result with foo0.b and foo1.a and foo2.a and foo2.b and k set: ", res.query);
assert(res.args==[Variant("Hallo du da!"), Variant("Hallo du da!"), Variant("Hello!"), Variant("Cu!"), Variant(9)]);
}
private:
void dropWhite(ref string buf) {
buf=buf.stripLeft();
}
/// D's indexOf seemed not to work at compile time.
size_t indexOf(string heap, char needle) {
foreach(i, c; heap) {
if(needle==c) {
return i;
}
}
return -1;
}
//pragma(msg, createQueryGenerator( " (${item in datespan} : ( { date>=#{item.from} and} {date<=#{item.to} and} {comment like #{\"%:Autorun:\"~item.autorun~\":Autorun:%\"}})"));
//pragma(msg, createQueryGenerator( " Hello this is a test!"));
//pragma(msg, createQueryGenerator( ` ${item in datespan} : ( { date>=#{item.from} } and {date<=#{item.to} } and {comment like #{"%:Autorun:"~item.autorun~":Autorun:%"}})`));
//pragma(msg, createQueryGenerator(`select * from testtable where
//{({${queryGenSep=" or "} ${f in foo} : ({col1=#{f.a}} and {col2=#{f.b}})})} or
//{col3>k}`));
//Syntax:
// ${a=3} ${i in array} ${u=i.c} : some data #{D expression} { same again }
// presql : [decls :] body
// decls : [${variable=assignment}]* [${variable in array}] [${variable=assignment}]*
// body : [some_string | #{D expression} | {presql}]*
/// private
/// Handles the part before the ":" colon. (Variable declarations + loop declaration)
private static string doVariableProcessing(ref string part, int level) {
string output;
string for_each_decl;
string after_output;
bool queryGenSepFound=false;
string buf="buf"~to!string(level);
dropWhite(part);
assert(part);
immutable isFirst="isFirst"~to!string(level);
if(part[0]!='$') {
return "bool "~isFirst~"=false;\n"~"{\nstring "~buf~";\n";
}
output~="bool "~isFirst~"=true;\n";
while(true) {
assert(part.length>2);
assert(part[0]=='$', "Only declarations enclosed in '${' are allowed in declarations block. Invalid data found: "~part);
assert(part[1]=='{', "'{' in '${' is mandatory! Found at:"~part);
string var_name;
part=part[2..$];
foreach(i,c; part) {
if(!c.isAlpha()) {
var_name=part[0..i];
if(var_name=="queryGenSep") {
var_name="queryGenSep"~to!string(level);
queryGenSepFound=true;
}
part=part[i..$];
break;
}
}
dropWhite(part);
assert(part.length, "Unexpected end of data, expected '=' or 'in'");
enum Operation {
assignment,
in_array
}
Operation op;
switch(part[0]) {
case '=': op=Operation.assignment;
part=part[1..$];
break;
case 'i':
assert(part.length>1 && part[1]=='n', "Expected 'n' after 'i' forming 'in', got: "~part);
part=part[2..$];
op=Operation.in_array;
break;
default: assert(false, "Unexpected operation: Only variable assignment ('=') and array loop ('in') are supported, found at: "~part);
}
dropWhite(part);
string right_side;
foreach(i, c; part) {
if(c=='}') {
right_side=part[0..i];
part=part[i+1..$];
break;
}
}
if(op==Operation.assignment) {
string buff="auto "~var_name~"="~right_side~";\n";
if(for_each_decl)
after_output~=buff;
else
output~=buff;
}
else {
if(for_each_decl)
assert(false, "Only one foreach declaration allowed, found second at: "~part);
for_each_decl="\nforeach("~var_name~"; "~right_side~") {\n";
}
dropWhite(part);
if(part[0]==':') {
part=part[1..$];
if(!queryGenSepFound) {
output~="immutable queryGenSep"~to!string(level)~"=\" or \";\n";
}
for_each_decl = for_each_decl==[] ? "{\n" : for_each_decl;
after_output~="string "~buf~";\n";
after_output~="if(!"~isFirst~") {\n";
after_output~=buf~"=queryGenSep"~to!string(level)~";\n}\n";
return output~for_each_decl~after_output;
}
else {
assert(part, "Unexpected end of string, expected another declaration '${}' or ':'.");
assert(part[0]=='$', "Expected ':' or another variable assignment ('$'), at: "~part);
}
}
}
// Extracts a D expression (#{}) from the string.
string dExpressionParser(ref string data, string buf, string validCount, string slevel, ref int count) {
assert(data.length>2 && data[0]=='#' && data[1]=='{');
string out_cmd;
data=data[2..$];
auto end=data.indexOf('}');
assert(end>0, "Empty or non closed D expression found at: "~data);
string val="val"~slevel~"_"~to!string(count);
out_cmd~="auto "~val~"="~data[0..end]~";\n";
out_cmd~="if("~val~"!=typeof("~val~").init) {\n";
out_cmd~="debug(queryGenerator) writeln(\"Found valid value: \", "~val~");\n";
out_cmd~=validCount~"++;\n";
out_cmd~=buf~"~=\"?\";\n";
out_cmd~="args_~=Variant("~val~");\n}\n";
out_cmd~="else {\ndebug(queryGenerator) writeln(\"Found invalid value: \", "~val~");\n}\n";
data=data[end+1..$];
return out_cmd;
}
// do the parsing of an sql string ('....' or ".....") Also handles escaping ('') or ("")
string processString(ref string data, string buf) {
assert(data[0]=='\'' || data[0]=='"', "Expected ' or \"");
char begin=data[0];
data=data[1..$];
string out_cmd;
while(data.length) {
foreach(i, c; data) {
if(c==begin && ((i+1)>=data.length || data[i+1]!=begin) ) { // End of string (Ignore escaped end in string)
out_cmd~=buf~"~=`"~begin~data[0..i]~begin~"`;\n";
data=data[i+1 .. $];
break;
}
}
}
return out_cmd;
}
// Parsing starts here.
string preSqlParser(ref string data, int level=0) {
auto out_cmd="{\n";
out_cmd~=doVariableProcessing(data, level);
debug(querygenerator) import std.stdio;
// dropWhite(data); //(Maybe intended, so don't drop it)
immutable slevel=to!string(level);
immutable buf="buf"~slevel;
immutable text="text"~slevel;
string upperBuf;
string upperWasValid;
int dexprCount=0;
if(level>0) {
upperBuf="buf"~(to!string(level-1));
upperWasValid="wasValid"~(to!string(level-1));
}
else
upperBuf="sql_";
immutable validCount="validCount"~slevel;
immutable wasValid="wasValid"~slevel;
immutable isFirst="isFirst"~to!string(level); // Defined in doVariableProcessing
out_cmd~="int "~validCount~"=-1;\n";
out_cmd~="int "~wasValid~";\n"; // validCount of inner level. (But never -1, if it would be -1 it is 1)
out_cmd~="string "~text~";\n";
while(data.length) {
auto end=data.length;
foreach(i, c; data) {
if(c=='{' || c=='}' || c=='#' || c=='\'' || c=='"' || c=='$') {
end=i;
break;
}
}
out_cmd~=text~"=`"~data[0..end]~"`;\n";
out_cmd~=buf~"~=`"~data[0..end]~"`;\n";
data=data[end..$];
if(data.length==0)
break;
debug(querygenerator) writefln("Remaining (level: %s) data: %s", level, data);
switch(data[0]) {
case '{' :
assert(data.length>2, "Expected some data after '{' at: "~data);
data=data[1..$];
out_cmd~="if("~validCount~"==0) {\n";
out_cmd~=buf~"="~buf~"[0..$-"~text~".length];\n}\n";
out_cmd~=wasValid~"=0;\n"; // Reset, because inner level will just add to it.
out_cmd~=preSqlParser(data, level+1);
assert(data[0]=='}', "Expected closing '}', got: "~data);
data=data[1..$];
out_cmd~="if("~wasValid~"==0 && "~validCount~">0) {\n"; // validCount has to be greater than 0 otherwise we have removed the data already. (See above)
out_cmd~=buf~"="~buf~"[0..$-"~text~".length];\n}\n";
out_cmd~=`debug(queryGenerator) writefln("Received data from level: `~to!string(level+1)~` on level: `~slevel~`, data was valid: %s, current valid count: %s", `~wasValid~`, `~validCount~`);`~"\n";
out_cmd~=validCount~"="~validCount~"==-1 ? "~wasValid~" : "~validCount~"+"~wasValid~";\n";
out_cmd~=`debug(queryGenerator) writefln("Updated valid count is now: %s", `~validCount~`);`~"\n";
break;
case '}' :
goto finish;
case '#' :
out_cmd~="if("~validCount~"==-1) {\n\t";
out_cmd~=validCount~"=0;\n}\n";
out_cmd~=dExpressionParser(data, buf, validCount, slevel, dexprCount);
dexprCount++;
break;
case '\'':
case '"':
out_cmd~=processString(data, buf);
break;
case '$': assert(false, "Unexpected '$'. The variable declaration block must start with a '${}' declaration otherwise you are already in the body and '${}' declarations are invalid.\n Found at: "~data);
default:
assert(false, "WTF? The if should have made this impossible!");
}
}
finish:
out_cmd~=`debug(queryGenerator) writefln("Leaving level: %s, valid count: %s, buffer: %s", `~slevel~`, `~validCount~`, `~buf~" );\n";
out_cmd~="if("~validCount~"==-1 || "~validCount~">0) {\n";
out_cmd~="debug(queryGenerator) writeln(\"Appended "~buf~" with contents: \", "~buf~");\n";
out_cmd~=isFirst~"=false;\n"; // No longer the first valid run, so separator should be inserted.
out_cmd~=upperBuf~"~="~buf~";\n}\n";
if(level>0) {
out_cmd~=upperWasValid~"+="~validCount~"<0 ? 1 : "~validCount~";\n";
}
// End of loop:
out_cmd~="}\n";
// End of block:
out_cmd~="}\n";
return out_cmd;
}
Reference in New Issue View Git Blame Copy Permalink