README
queryT
A query text builder that depends upon the available query parameters. queryT.js is small, it has no dependencies and it is inspired by the simplicity and efficiency of the doT.
Tokens
queryT.tokens = {
start: '[[', //evaluation start token
end: ']]', //evaluation end token
separators:[',', 'AND', 'OR'] //a list of separators
};
Parameters
The name of the parameters complies with the MsSql rules for variables. It must begin with an at (@) sign.
Evaluation
Evaluation consists in checking the availability of the parameters used. An evaluation is passed only if all the parameters are available, regardless sub-evaluations. For example:
SELECT * FROM Table
[[WHERE Field1 = @Field1 [[AND Field2 = @Field2]]]]
if only the @Field2 is passed then the result will be:
SELECT * FROM Table
if only the @Field1 is passed then the result will be:
SELECT * FROM Table
WHERE Field1 = @Field1
if both parameters are passed then the result will be:
SELECT * FROM Table
WHERE Field1 = @Field1 AND Field2 = @Field2
Separators
Separators helps composing a correct statement. In an evaluation, each sub-evaluation depends on previous sub-evaluations. If one of the previous sub-evaluations was significant then the separator should stay, otherwise it will be removed.
template
SELECT * FROM Table
[[WHERE [[Field1 = @Field1]] [[AND Field2 = @Field2]] [[AND Field3 = @Field3]]]]
Suppose the @Field1 parameter is not available, but @Field2 and @Field3 are. The result will be:
SELECT * FROM Table
WHERE Field2 = @Field2 AND Field3 = @Field3
Notice that the 'AND' separator was removed from [[AND Field2 = @Field2]]
, but not from [[AND Field3 = @Field3]]
.
Usage
var parameters = ['@Param1', '@Param3'],
template = 'SELECT * FROM Table [[WHERE [[Field1 = @Param1]] [[AND Field2 = @Param2]] [[AND Field3 = @Param3]]]]',
options = {
hasParameter : function(name){
return parameters.indexOf(name) !== -1;
},
rewriteParameter : function(name, index){
return name;
}
};
var result = queryT.template(template, options);
Result
SELECT * FROM Table WHERE Field1 = @Param1 AND Field3 = @Param3
Parameters rewriting
Not all sql server have the same notation rule for parameters. For example, in MySql the notation for parameters is '?'. In this case, the rewriteParameter function should be provided.
var parameters = ['@Param1', '@Param3'],
template = 'SELECT * FROM Table [[WHERE [[Field1 = @Param1]] [[AND Field2 = @Param2]] [[AND Field3 = @Param3]]]]',
options = {
hasParameter : function(name){
return parameters.indexOf(name) !== -1;
},
rewriteParameter : function(name, index){
return '?';
}
};
var result = queryT.template(template, options);
Result
SELECT * FROM Table WHERE Field1 = ? AND Field3 = ?
Joins
var parameters = ['@Param1', '@Param3'],
template = 'SELECT * FROM Table [[LEFT JOIN Table2 ON Table.Field1 = Table2.Field1 AND Table2.Field2 = @Param2]] [[WHERE [[Table1.Field1 = @Param1]] [[AND Table1.Field2 = @Param3]]]]',
options = {
hasParameter : function(name){
return parameters.indexOf(name) !== -1;
},
rewriteParameter : function(name, index){
return name;
}
};
var result = queryT.template(template, options);
Result
SELECT * FROM Table WHERE Table1.Field1 = @Param1 AND Table1.Field2 = @Param3
Fields on select clause depending on parameters
For fields, an workaround is needed.
var parameters = ['@Param1', '@Param3'],
template = 'SELECT Table.*[[, Table2.Field2 @Param2Field]] FROM Table [[LEFT JOIN Table2 ON Table.Field1 = Table2.Field1 AND Table2.Field2 = @Param2]] [[WHERE [[Table1.Field1 = @Param1]] [[AND Table1.Field2 = @Param3]]]]',
options = {
hasParameter : function(name){
return (parameters.indexOf(name) >= 0) || (parameters.indexOf(name + 'Field') >= 0);
},
rewriteParameter : function(name, index){
if (name.substr(-5) === 'Field')
return '';
return name;
}
};
var result = queryT.template(template, options);
Result
SELECT Table.* FROM Table WHERE Table1.Field1 = @Param1 AND Table1.Field2 = @Param3
Author
Mihai Slobozeanu
License
queryT is licensed under the MIT License.