<commit_before>/*!
 * node-datatable
 * https://github.com/jpravetz/node-datatable
 * Copyright(c) 2012-2013 <NAME> <<EMAIL>>
 * node-datatable may be freely distributed under the MIT license.
 */

var _u = require('underscore');

var DEFAULT_LIMIT = 100;

/**
 * Constructor
 * @param options Refer to README.md for a list of properties
 * @return {Object}
 */
module.exports = function (options) {

    var self = {
        sTableName: options.sTableName,
        sCountColumnName: options.sCountColumnName,   // Name of column to use when counting total number of rows. Defaults to "id"
        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)
        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) {
        var dateSql = buildDatePartial();
        var result = "SELECT COUNT(";
        result += self.sSelectSql ? "*" : (self.sCountColumnName ? self.sCountColumnName : "id");
        result += ") FROM ";
        result += self.sFromSql ? self.sFromSql : self.sTableName;
        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)  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 searches = [],
            colSearches = buildSearchArray(requestQuery),
            globalSearches = buildSearchArray(requestQuery, true);

        if (colSearches.length){
            searches.push('(' + colSearches.join(" AND ") + ')');
        }

        if (globalSearches.length){
            searches.push('(' + globalSearches.join(" OR ") + ')');
        }

        return searches.join(" AND ");
    }

    /**
     * (private) Builds an array of LIKE / ILIKE statements to be added to the WHERE clause
     * @param {Object} requestQuery The datatable parameters that are generated by the client
     * @param {*} [global] If truthy, build a global search array. If falsy, build a column search array
     * @returns {Array} An array of LIKE / ILIKE statements
     */
    function buildSearchArray(requestQuery, global) {
        var searchArray = [];

        _u.each(requestQuery.columns, function(column, i){
            var colName = sanitize(self.aSearchColumns[i] || column.data),
                searchVal = sanitize(global ? requestQuery.search.value : column.search.value);

            if (colName && searchVal && column.searchable === 'true'){
                searchArray.push(self.dbType === 'postgres' ?
                                 buildILIKESearch(colName, searchVal) :
                                 buildLIKESearch(colName, searchVal));
            }
        });

        return searchArray;
    }

    /**
     * (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) 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);<commit_msg>Use columns[i].name to build WHERE clauses so that columns[i].data can be used for its intended purpose (see https://datatables.net/reference/option/columns.data for advanced usage)<commit_after>/*!
 * node-datatable
 * https://github.com/jpravetz/node-datatable
 * Copyright(c) 2012-2013 <NAME> <<EMAIL>>
 * node-datatable may be freely distributed under the MIT license.
 */

var _u = require('underscore');

var DEFAULT_LIMIT = 100;

/**
 * Constructor
 * @param options Refer to README.md for a list of properties
 * @return {Object}
 */
module.exports = function (options) {

    var self = {
        sTableName: options.sTableName,
        sCountColumnName: options.sCountColumnName,   // Name of column to use when counting total number of rows. Defaults to "id"
        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)
        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) {
        var dateSql = buildDatePartial();
        var result = "SELECT COUNT(";
        result += self.sSelectSql ? "*" : (self.sCountColumnName ? self.sCountColumnName : "id");
        result += ") FROM ";
        result += self.sFromSql ? self.sFromSql : self.sTableName;
        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)  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 searches = [],
            colSearches = buildSearchArray(requestQuery),
            globalSearches = buildSearchArray(requestQuery, true);

        if (colSearches.length){
            searches.push('(' + colSearches.join(" AND ") + ')');
        }

        if (globalSearches.length){
            searches.push('(' + globalSearches.join(" OR ") + ')');
        }

        return searches.join(" AND ");
    }

    /**
     * (private) Builds an array of LIKE / ILIKE statements to be added to the WHERE clause
     * @param {Object} requestQuery The datatable parameters that are generated by the client
     * @param {*} [global] If truthy, build a global search array. If falsy, build a column search array
     * @returns {Array} An array of LIKE / ILIKE statements
     */
    function buildSearchArray(requestQuery, global) {
        var searchArray = [];

        _u.each(requestQuery.columns, function(column, i){
            var colName = sanitize(self.aSearchColumns[i] || column.name),
                searchVal = sanitize(global ? requestQuery.search.value : column.search.value);

            if (colName && searchVal && column.searchable === 'true'){
                searchArray.push(self.dbType === 'postgres' ?
                                 buildILIKESearch(colName, searchVal) :
                                 buildLIKESearch(colName, searchVal));
            }
        });

        return searchArray;
    }

    /**
     * (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) 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.name) {
                query.push(column.name + " " + 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);
{'fraction_non_alphanumeric': 0.10599909788001805, 'fraction_numerical': 0.0015787099684258007, 'mean_word_length': 2.237860533041256, 'pattern_counts': {'":': 0, '<': 13, '<?xml version=': 0, '>': 11, 'https://': 3, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 4, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '6133431', 'n_tokens_mistral': 4908, 'n_tokens_neox': 4755, 'n_words': 1493}