node-datatable
node-datatable copied to clipboard
Published
20 hours ago •
jpravetz
jpravetz
Regex functionality is not documented in README
Well, there are a few references, but builder.js has some references in
/* Example datatable querystring ....
Regex wasn't working for me. We were using this for SQLite. I realized we'd need to filter based on regex after running the full query. I was going to submit a pull request, but it turns out I modified a previous version of the code. Here is what we use:
/*!
* node-datatable
* https://github.com/jpravetz/node-datatable
* Copyright(c) 2012-2013 Jim Pravetz <[email protected]>
* node-datatable may be freely distributed under the MIT license.
*
* Note: We are not compatible with newer versions of this since
* the newer version doesn't seem to support RegEx. In face,
* we modified the default regex support functionality such that it
* was supported after the query ran. In other words, the filtering
* occurs after the query runs.
*/
// var _u = require('underscore');
var DEFAULT_LIMIT = 5000;
/**
* Constructor
* @param options Refer to README.md for a list of properties
* @return {Object}
*/
// module.exports = function (options) {
var QueryBuilder = function ( options ) {
var self = {
sTableName: options.sTableName,
sCountTableName: options.sCountTableName, // Name of table to use when counting total number of rows. Defaults to sCountColumnName
sCountColumnName: options.sCountColumnName, // Name of column to use when counting total number of rows. Defaults to options.sCountColumnName
sDatabaseOrSchema: options.sDatabaseOrSchema, // If set, then do "USE useDatabase;" prior to query
aSearchColumns: options.aSearchColumns || [], // Used to determine names of columns to search
sSelectSql: options.sSelectSql, // alternate select statement
sFromSql: options.sFromSql, // alternate select statement
sWhereAndSql: options.sWhereAndSql, // Custom caller SQL, added as AND where to add date range or other checks (caller must write the SQL)
sGroupBySql: options.sGroupBySql || [], // Custom caller SQL, added as GROUP BY statement
sDateColumnName: options.sDateColumnName, // If set then only get entries within the range (can use sWhereSql instead)
dateFrom: options.dateFrom, // Only retrieve content from before this date. sDateColumnName must be set.
dateTo: options.dateTo, // Only retrieve content from after this date. sDateColumnName must be set.
oRequestQuery: options.oRequestQuery, // Usually passed in with buildQuery
sAjaxDataProp: 'data', // The name of the data prop to set on the return value
dbType: options.dbType, // "postgres" or "oracle", defaults to MySQL syntax
buildQuery: buildQuery,
parseResponse: parseResponse,
filteredResult: filteredResult
};
/**
* (private) Build an optional "USE sDatabaseOrSchema" for MySQL / Postgres or
* "ALTER SESSION SET CURRENT_SCHEMA = sDatabaseOrSchema" statement for Oracle if sDatabaseOrSchema is set.
* @return {string|undefined} The SQL statement or undefined
*/
function buildSetDatabaseOrSchemaStatement() {
if (self.sDatabaseOrSchema){
if (self.dbType === 'oracle'){
return 'ALTER SESSION SET CURRENT_SCHEMA = ' + self.sDatabaseOrSchema;
}
else{
return "USE " + self.sDatabaseOrSchema;
}
}
}
/**
* (private) Build the date partial that is used in a WHERE clause
* @return {*}
*/
function buildDatePartial() {
if (self.sDateColumnName && self.dateFrom || self.dateTo) {
// console.log( "DateFrom %s to %s", self.dateFrom, self.dateTo );
if (self.dateFrom && self.dateTo) {
return self.sDateColumnName + " BETWEEN '" + self.dateFrom.toISOString() + "' AND '" + self.dateTo.toISOString() + "'";
} else if (self.dateFrom) {
return self.sDateColumnName + " >= '" + self.dateFrom.toISOString() + "'";
} else if (self.dateTo) {
return self.sDateColumnName + " <= '" + self.dateTo.toISOString() + "'";
}
}
return undefined;
}
/**
* (private) Build a complete SELECT statement that counts the number of entries.
* @param searchString If specified then produces a statement to count the filtered list of records.
* Otherwise the statement counts the unfiltered list of records.
* @return {String} A complete SELECT statement
*/
function buildCountStatement(requestQuery, countType) {
var dateSql = buildDatePartial();
var result;
var countTable;
if(self.sCountTableName) {
countTable = self.sCountTableName;
} else {
countTable = self.sTableName;
}
if(self.sGroupBySql.length) {
result = "SELECT COUNT(*) " + countType + " FROM (";
result += "SELECT COUNT(" + self.sGroupBySql[0] + ") as " + countType + " FROM ";
result += self.sFromSql ? self.sFromSql : countTable;
result += buildWherePartial(requestQuery);
result += buildGroupByPartial();
result += ") temp";
} else {
result = "SELECT COUNT(";
result += self.sSelectSql ? "*" : (self.sCountColumnName ? self.sCountColumnName : "id");
result += ") AS " + countType + " FROM ";
result += self.sFromSql ? self.sFromSql : countTable;
result += buildWherePartial(requestQuery);
}
// var sSearchQuery = buildSearchPartial( sSearchString );
// var sWheres = sSearchQuery ? [ sSearchQuery ] : [];
// if( self.sWhereAndSql )
// sWheres.push( self.sWhereAndSql )
// if( dateSql )
// sWheres.push( dateSql );
// if( sWheres.length )
// result += " WHERE (" + sWheres.join( ") AND (" ) + ")";
return result;
}
/**
* (private) Build the WHERE clause
* otherwise uses aoColumnDef mData property.
* @param searchString
* @return {String}
*/
function buildWherePartial(requestQuery) {
var sWheres = [];
var searchQuery = buildSearchPartial(requestQuery);
if (searchQuery)
sWheres.push(searchQuery);
if (self.sWhereAndSql)
sWheres.push(self.sWhereAndSql);
var dateSql = buildDatePartial();
if (dateSql)
sWheres.push(dateSql);
if (sWheres.length)
return " WHERE (" + sWheres.join(") AND (") + ")";
return "";
}
/**
* (private) Build the GROUP BY clause
* @return {String}
*/
function buildGroupByPartial() {
if (self.sGroupBySql.length)
return " GROUP BY " + self.sGroupBySql.join(',') + " ";
return "";
}
/**
* (private) Builds the search portion of the WHERE clause using LIKE (or ILIKE for PostgreSQL).
* @param {Object} requestQuery The datatable parameters that are generated by the client
* @return {String} A portion of a WHERE clause that does a search on all searchable row entries.
*/
function buildSearchPartial(requestQuery) {
var query = [];
var globalQuery = [];
var queryString;
var globalString = "";
var returnValue;
for (var sdx = 0; sdx < requestQuery.columns.length; ++sdx) {
var globalSearch = null,
columnSearch = null,
column = requestQuery.columns[sdx],
colName = self.aSearchColumns[sdx] || column.data;
if (column.searchable === true){
if (requestQuery.search.value){
globalSearch = self.dbType === 'postgres' ?
buildILIKESearch(colName, requestQuery.search.value) :
buildLIKESearch(colName, requestQuery.search.value);
}
if (column.search.value){
if( self.dbType === 'postgres' ) {
columnSearch = buildILIKESearch(colName, column.search.value);
} else {
if( column.search.regex ) {
// Regex is not cross-browser supported in SQLite, so it will be handled after the query runs
// columnSearch = buildREGEXPSearch(colName, column.search.value);
} else {
columnSearch = buildLIKESearch(colName, column.search.value);
}
}
}
if (globalSearch && columnSearch){
query.push(columnSearch);
globalQuery.push(globalSearch);
} else if (globalSearch){
globalQuery.push(globalSearch);
}
else if (columnSearch){
query.push(columnSearch);
}
}
}
if( query.length ) {
queryString = "(" + query.join(" AND ") + ")";
}
if( globalQuery.length ) {
globalString = "(" + globalQuery.join(" OR ") + ")";
}
if(query.length && globalQuery.length) {
returnValue = queryString + " AND " + globalString;
} else if (query.length) {
returnValue = queryString;
} else if (globalString.length) {
returnValue = globalString;
} else {
returnValue = undefined;
}
return returnValue;
}
/**
* (private) Builds the search portion of the WHERE clause using ILIKE
* @param {string} colName The column to search
* @param {string} searchVal The value to search for
* @returns {string} An ILIKE statement to be added to the where clause
*/
function buildILIKESearch(colName, searchVal) {
return "CAST(" + colName + " as text)" + " ILIKE '%" + searchVal + "%'";
}
/**
* (private) Builds the search portion of the WHERE clause using LIKE
* @param {string} colName The column to search
* @param {string} searchVal The value to search for
* @returns {string} A LIKE statement to be added to the where clause
*/
function buildLIKESearch(colName, searchVal) {
return colName + " LIKE '%" + searchVal + "%'";
}
/**
* (private) Builds the search portion of the WHERE clause using LIKE
* @param {string} colName The column to search
* @param {string} searchVal The value to search for
* @returns {string} A LIKE statement to be added to the where clause
*/
function buildREGEXPSearch(colName, searchVal) {
return colName + " REGEXP '" + searchVal + "'";
}
/**
* (private) Adds an ORDER clause
* @param requestQuery The Datatable query string (we look at sort direction and sort columns)
* @return {String} The ORDER clause
*/
function buildOrderingPartial(requestQuery) {
var query = [];
for (var fdx = 0; fdx < requestQuery.order.length; ++fdx) {
var order = requestQuery.order[fdx],
column = requestQuery.columns[order.column];
if (column.orderable === true && column.data) {
query.push(column.data + " " + order.dir);
}
}
if (query.length)
return " ORDER BY " + query.join(", ");
return "";
}
/**
* Build a LIMIT clause
* @param requestQuery The Datatable query string (we look at length and start)
* @return {String} The LIMIT clause
*/
function buildLimitPartial(requestQuery) {
var sLimit = "";
if (requestQuery && requestQuery.start !== undefined && self.dbType !== 'oracle') {
var start = parseInt(requestQuery.start, 10);
if (start >= 0) {
var len = parseInt(requestQuery.length, 10);
sLimit = (self.dbType === 'postgres') ? " OFFSET " + String(start) + " LIMIT " : " LIMIT " + String(start) + ", ";
sLimit += ( len > 0 ) ? String(len) : String(DEFAULT_LIMIT);
}
}
return sLimit;
}
/**
* Build the base SELECT statement.
* @return {String} The SELECT partial
*/
function buildSelectPartial() {
var query = "SELECT ";
query += self.sSelectSql ? self.sSelectSql : "*";
query += " FROM ";
query += self.sFromSql ? self.sFromSql : self.sTableName;
return query;
}
/**
* Build an array of query strings based on the Datatable parameters
* @param requestQuery The datatable parameters that are generated by the client
* @return {Object} An array of query strings, each including a terminating semicolon.
*/
function buildQuery(requestQuery) {
var queries = {};
if (typeof requestQuery !== 'object')
return queries;
var searchString = sanitize(requestQuery.search.value);
self.oRequestQuery = requestQuery;
var useStmt = buildSetDatabaseOrSchemaStatement();
if (useStmt) {
queries.use = useStmt;
}
queries.recordsTotal = buildCountStatement(requestQuery, "recordsTotal");
if (searchString) {
queries.recordsFiltered = buildCountStatement(requestQuery, "recordsFiltered");
}
var query = buildSelectPartial();
query += buildWherePartial(requestQuery);
query += buildGroupByPartial();
query += buildOrderingPartial(requestQuery);
// Due to the fact that regex filtering must occur after the query has run, we can't paginate until after the regex has been applied (which means a potentially large query result set up until pagination)
// query += buildLimitPartial(requestQuery);
if (self.dbType === 'oracle'){
var start = parseInt(requestQuery.start, 10);
var len = parseInt(requestQuery.length, 10);
if (len >= 0 && start >= 0) {
query = 'SELECT * FROM (SELECT a.*, ROWNUM rnum FROM (' + query + ') ';
query += 'a WHERE ROWNUM <= ' + (start + len) + ') WHERE rnum >= ' + start;
}
}
queries.select = query;
return queries;
}
/**
* Parse the responses from the database and build a Datatable response object.
* @param queryResult An array of SQL response objects, each of which must, in order, correspond with a query string
* returned by buildQuery.
* @return {Object} A Datatable reply that is suitable for sending in a response to the client.
*/
function parseResponse(queryResult) {
var oQuery = self.oRequestQuery;
var result = { recordsFiltered: 0, recordsTotal: 0 };
if (oQuery && typeof oQuery.draw === 'string') {
// Cast for security reasons, as per http://datatables.net/usage/server-side
result.draw = parseInt(oQuery.draw,10);
} else {
result.draw = 0;
}
if (_u.isObject(queryResult) && _u.keys(queryResult).length > 1) {
result.recordsFiltered = result.recordsTotal = extractCount(queryResult.recordsTotal);
if (queryResult.recordsFiltered) {
result.recordsFiltered = extractCount(queryResult.recordsFiltered);
}
result.data = queryResult.select;
}
return result;
}
/**
* (private)
* @param obj
* @return {*}
*/
function extractCount(obj) {
var values;
if (obj && obj.length)
values = _u.values(obj[0]);
if (values && values.length)
return values[0];
return 0;
}
/**
* Debug, reduced size object for display
* @param obj
* @return {*}
*/
function filteredResult(obj, count) {
if (obj) {
var result = _u.omit(obj, self.sAjaxDataProp );
result.aaLength = obj[self.sAjaxDataProp] ? obj[self.sAjaxDataProp].length : 0;
result[self.sAjaxDataProp] = [];
var count = count ? Math.min(count, result.aaLength) : result.aaLength;
for (var idx = 0; idx < count; ++idx) {
result[self.sAjaxDataProp].push(obj[self.sAjaxDataProp][idx]);
}
return result;
}
return null;
}
return self;
}
/**
* Sanitize to prevent SQL injections.
* @param str
* @return {*}
*/
function sanitize(str, len) {
len = len || 256;
if (!str || typeof str === 'string' && str.length < 1)
return str;
if (typeof str !== 'string' || str.length > len)
return null;
return str.replace(/[\0\x08\x09\x1a\n\r"'\\\%]/g, function (char) {
switch (char) {
case "\0":
return "\\0";
case "\x08":
return "\\b";
case "\x09":
return "\\t";
case "\x1a":
return "\\z";
case "\n":
return "\\n";
case "\r":
return "\\r";
case "\"":
case "'":
case "\\":
case "%":
return "\\" + char; // prepends a backslash to backslash, percent,
// and double/single quotes
}
});
}
