BigQueryConnector.js

var debug = require("debug")("node-database-connectors:bigquery");
const { BigQuery } = require("@google-cloud/bigquery");

const utils = require("./utils.js");
//connect
var fieldIdentifier_left = "`",
  fieldIdentifier_right = "`";

exports.connectPool = function (json, cb) {
  return connectPool(json, cb);
};

exports.connect = function (json, cb) {
  return connect(json, cb);
};

function connectPool(json, cb) {
  __validateConnJSON(json);
  if (json.keyType == "filepath") {
    const bigqueryClient = new BigQuery({
      keyFilename: json.keyValue,
      projectId: json.projectId,
    });
    if (cb) cb(null, bigqueryClient);
    return bigqueryClient;
  } else if (json.keyType == "env_variable") {
    if (!process.env[json.keyValue]) {
      console.log("No Env variable found with value " + json.keyValue);
      throw new Error("No Env variable found with value " + json.keyValue);
    }
    const bigqueryClient = new BigQuery({
      keyFilename: process.env[json.keyValue],
      projectId: json.projectId,
    });
    if (cb) cb(null, bigqueryClient);
    return bigqueryClient;
  }
  // var numConnections = json.connectionLimit || 0;
  // var pool = db.createPool({
  //   acquireTimeout: json.acquireTimeout || 2 * 1000,
  //   connectionLimit: numConnections,
  //   host: json.host,
  //   port: json.port,
  //   user: json.user,
  //   database: json.database,
  //   password: json.password,
  //   multipleStatements: json.connectionLimit === false ? false : true,
  // });
  // if (cb) cb(null, bigqueryClient);
  // return bigqueryClient;

  // acquireConnection(0);

  // function acquireConnection(index) {
  //   if (index >= numConnections) {
  //     return;
  //   }
  //   debug("acquiring connection ", index, json);
  //   pool.getConnection(function (err, connection) {
  //     if (err) {
  //       debug("error in acquiring connection", index, err, json);
  //     } else {
  //       debug("releasing connection ", index, json);
  //       connection.release();
  //     }
  //     acquireConnection(index + 1);
  //   });
  // }
  // return pool;
}

function connect(json, cb) {
  __validateConnJSON(json);
  if (json.keyType == "filepath") {
    const bigqueryClient = new BigQuery({
      keyFilename: json.keyValue,
      projectId: json.projectId,
    });
    if (cb) cb(null, bigqueryClient);
    return bigqueryClient;
  } else if (json.keyType == "env_variable") {
    if (!process.env[json.keyValue]) {
      console.log("No Env variable found with value " + json.keyValue);
      throw new Error("No Env variable found with value " + json.keyValue);
    }
    const bigqueryClient = new BigQuery({
      keyFilename: process.env[json.keyValue],
      projectId: json.projectId,
    });
    if (cb) cb(null, bigqueryClient);
    return bigqueryClient;
  }

  // var connection = db.createConnection({
  //   host: json.host,
  //   port: json.port,
  //   user: json.user,
  //   database: json.database,
  //   password: json.password,
  //   multipleStatements: json.connectionLimit === false ? false : true,
  // });
  // console.log("CONNECTION CREATED...", connection.state, connection.threadId);
  // connection.connect(function (err) {
  //   if (err) {
  //     debug("error-A");
  //     debug(["c.connect", err]);
  //   } else {
  //     connection.on("error", function (e) {
  //       debug("error-B");
  //       debug(["error", e]);
  //     });
  //   }
  //   if (cb) cb(err, connection);
  // });
  // return connection;
}

//disconnect
exports.disconnect = function () {
  return disconnect(arguments[0]);
};

function disconnect(connection) {
  connection.end();
}

function __validateConnJSON(json) {
  if (json == null || json == undefined) {
    console.log("Connection options to bigquery can not be empty or null");
    throw new Error("Connection options to bigquery can not be empty or null");
  }

  if (typeof json != "object") {
    console.log("Connecting json to bigquery should be an object.");
    throw new Error("Connecting json to bigquery should be an object.");
  }

  if (!json.hasOwnProperty("keyType")) {
    console.log("keyType property required while connecting to BigQuery");
    throw new Error("Expecting keyType while connecting to Bigquey.");
  }

  if (!json.hasOwnProperty("keyValue")) {
    console.log("keyValue property required while connecting to BigQuery");
    throw new Error("Expecting keyValue while connecting to Bigquey.");
  }

  if (!json.hasOwnProperty("projectId")) {
    console.log("projectId property required while connecting to BigQuery");
    throw new Error("Expecting projectId while connecting to Bigquey.");
  }

  if (json.keyType != "filepath" && json.keyType != "env_variable") {
    console.log(
      "Unexpected value for keyType found. It should be filepath or env_variable."
    );
    throw new Error(
      "keyType values can either be filepath or env_variable for connecting to BigQuery."
    );
  }
}

//prepare query
exports.prepareQuery = function () {
  return prepareQuery(arguments[0]);
};

function createInsertQuery(json) {
  var table = json.table ? json.table : null;
  var vInsert = json.insert ? json.insert : null;
  var arrInsert = [];
  arrInsert = createInsert(vInsert);
  var query = "";
  if (!Array.isArray(vInsert)) {
    if (!Array.isArray(vInsert.fValue[0])) {
      query =
        "INSERT INTO " +
        table +
        "(" +
        arrInsert.fieldArr.join() +
        ") VALUES(" +
        arrInsert.valueArr.join() +
        ")";
    } else {
      query =
        "INSERT INTO " +
        table +
        "(" +
        arrInsert.fieldArr.join() +
        ") VALUES " +
        arrInsert.valueArr.join() +
        "";
    }
  } else {
    query =
      "INSERT INTO " +
      table +
      "(" +
      arrInsert.fieldArr.join() +
      ") VALUES(" +
      arrInsert.valueArr.join() +
      ")";
  }
  return query + ";";
}

function createUpdateQuery(json) {
  var arrUpdate = [],
    arrFilter = [],
    strJOIN = "";
  var table = json.table ? json.table : null;
  var vUpdate = json.update ? json.update : null,
    vFilter = json.filter ? json.filter : null,
    join = json.join ? json.join : null;

  if (vFilter != null) arrFilter = createFilter(vFilter);
  if (join != null) {
    strJOIN = createJOIN(join);
    if (strJOIN.length > 0) {
      table = strJOIN;
    } else {
      if (table != null) {
        table =
          encloseField(table) + (fromTblAlias ? " as " + fromTblAlias : "");
      } else {
        table = fromTblAlias ? " as " + fromTblAlias : "";
      }
    }
  }

  arrUpdate = createUpdate(vUpdate);
  query = "UPDATE " + table + " SET " + arrUpdate.join() + "";
  if (arrFilter.length > 0) {
    query += " WHERE " + arrFilter.join("");
  }
  return query + ";";
}

function createSelectQuery(json, selectAll) {
  var arrSelect = [],
    arrSortBy = [],
    arrFilter = [],
    arrGroupBy = [],
    arrHaving = [],
    strJOIN = "";
  var table = json.table ? json.table : null;
  var fromTblAlias = json.alias ? json.alias : json.table;
  var sortby = json.sortby ? json.sortby : null,
    limit = json.limit ? json.limit : null,
    join = json.join ? json.join : null;
  var vSelect = json.select ? json.select : null,
    vFilter = json.filter ? json.filter : null,
    vGroupby = json.groupby ? json.groupby : null,
    vHaving = json.having ? json.having : null;

  if (vHaving != null) arrHaving = createAggregationFilter(vHaving);
  if (vGroupby != null) arrGroupBy = createSelect(vGroupby, false);
  if (vFilter != null) arrFilter = createFilter(vFilter);

  arrSelect = createSelect(vSelect, true);

  //from/join
  strJOIN = createJOIN(join);
  if (strJOIN.length > 0) {
    table = strJOIN;
  } else {
    table = encloseField(table);
  }

  //order by
  if (sortby != null) {
    for (var s = 0; s < sortby.length; s++) {
      var encloseFieldFlag =
        sortby[s].encloseField != undefined ? sortby[s].encloseField : true;
      var sortField = encloseField(sortby[s].field, encloseFieldFlag);
      var sortTable =
        sortby[s].table != undefined ? encloseField(sortby[s].table) : null;
      var sortOrder = sortby[s].order ? sortby[s].order : "ASC";
      if (sortTable == null) arrSortBy.push(sortField + " " + sortOrder);
      else arrSortBy.push(sortTable + "." + sortField + " " + sortOrder);
    }
  }

  var query = "SELECT " + arrSelect.join();
  if (table != "") {
    query += " FROM " + table + "";
  }
  if (arrFilter.length > 0) {
    query += " WHERE " + arrFilter.join("");
  }
  if (arrGroupBy.length > 0) {
    query += " GROUP BY " + arrGroupBy.join();
  }
  if (arrHaving.length > 0) {
    query += " HAVING " + arrHaving.join("");
  }
  if (arrSortBy.length > 0) {
    query += " ORDER BY " + arrSortBy.join();
  }
  if (limit != null) {
    query += " LIMIT " + limit;
  }
  return query + ";";
}

function createDeleteQuery(json) {
  var table = json.table ? json.table : null;
  var arrFilter = [];
  var vFilter = json.filter ? json.filter : null;
  if (vFilter != null) {
    arrFilter = createFilter(vFilter);
  }
  var query = "";
  if (arrFilter.length > 0) {
    query = "DELETE FROM " + table + " WHERE" + arrFilter.join("");
  } else {
    query = "DELETE FROM " + table + " WHERE 1=1";
  }
  return query + ";";
}

function validateJson(json) {
  return utils.validateJson(json);
}

function prepareQuery(json) {
  var validate = validateJson(json);

  if (validate !== "") {
    return validate;
  } else {
    var query = "";
    (vInsert = json.insert ? json.insert : null),
      (vSelect = json.select ? json.select : null),
      (vUpdate = json.update ? json.update : null),
      (vDelete = json.delete ? json.delete : null);

    //INSERT
    if (vInsert != null) {
      return createInsertQuery(json);
    }
    //UPDATE
    else if (vUpdate != null) {
      return createUpdateQuery(json);
    }
    //DELETE
    else if (vDelete != null) {
      return createDeleteQuery(json);
    }
    //SELECT
    else if (vSelect != null) {
      return createSelectQuery(json);
    }
  }
}

//Create select expression
function createSelect(arr, selectAll) {
  var tempArr = [];
  if (arr != null) {
    if (arr.length == 0 && selectAll == true) {
      tempArr.push("*");
    } else {
      for (var s = 0; s < arr.length; s++) {
        var obj = arr[s];
        if (typeof obj === "string") {
          obj = { field: obj };
        }
        if (
          obj.encloseField != undefined &&
          typeof obj.encloseField != "boolean"
        ) {
          obj.encloseField = obj.encloseField == "false" ? false : true;
        }
        var encloseFieldFlag =
          obj.encloseField != undefined ? obj.encloseField : true;
        var field = encloseField(obj.field, encloseFieldFlag);
        var table = obj.table ? encloseField(obj.table) : null;
        var hasAlias = obj.alias ? true : false;
        var alias = encloseField(obj.alias ? obj.alias : obj.field);
        var expression = obj.expression ? obj.expression : null;
        var aggregation = obj.aggregation ? obj.aggregation : null;
        var dataType = obj.dataType ? obj.dataType : null;
        var format = obj.format ? obj.format : null;
        var selectText = "";
        if (expression != null) {
          selectText = "(CASE ";
          var cases = expression.cases;
          var defaultCase = expression["default"];
          var defaultValue = "";
          for (var e = 0; e < cases.length; e++) {
            var operator = cases[e].operator;
            var value = cases[e].value;
            var out = cases[e].out;
            var outVal = "";
            if (out.hasOwnProperty("value")) {
              outVal = out.value;
            } else {
              outVal = encloseField(out.table) + "." + encloseField(out.field);
            }
            var strOperatorSign = "";
            strOperatorSign = operatorSign(operator, value);
            if (strOperatorSign.indexOf("IN") > -1) {
              //IN condition has different format
              selectText +=
                " WHEN " +
                table +
                "." +
                field +
                " " +
                strOperatorSign +
                ' ("' +
                value.join('","') +
                '") THEN ' +
                outVal;
            } else {
              selectText +=
                " WHEN " +
                table +
                "." +
                field +
                " " +
                strOperatorSign +
                ' "' +
                value +
                '" THEN ' +
                outVal;
            }
          }
          if (defaultCase.hasOwnProperty("value")) {
            defaultValue = defaultCase.value;
          } else {
            defaultValue =
              encloseField(defaultCase.table) +
              "." +
              encloseField(defaultCase.field);
          }
          selectText += " ELSE " + defaultValue + " END)";
        } else {
          if (dataType != null) {
            if (dataType.toString().toLowerCase() == "datetime") {
              selectText =
                " DATE_FORMAT(" + table + "." + field + ",'" + format + "') ";
            } else {
              if (encloseFieldFlag == false || encloseFieldFlag == "false")
                selectText = field;
              else selectText = table + "." + field;
            }
          } else {
            if (encloseFieldFlag == false || encloseFieldFlag == "false") {
              selectText = field;
            } else {
              if (table != null) {
                selectText = table + "." + field;
              } else {
                selectText = field;
              }
            }
          }
        }
        if (aggregation != null) {
          //CBT:this is for nested aggregation if aggregation key contains Array
          if (
            Object.prototype.toString.call(aggregation).toLowerCase() ===
            "[object array]"
          ) {
            var aggregationText = "";
            aggregation.forEach(function (d) {
              aggregationText = aggregationText + d + "(";
            });
            selectText = aggregationText + selectText;
            aggregationText = "";
            aggregation.forEach(function (d) {
              aggregationText = aggregationText + ")";
            });
            selectText = selectText + aggregationText;
          } else {
            selectText = aggregation + "(" + selectText + ")";
          }
        }
        if (hasAlias) selectText += " as " + alias;
        tempArr.push(selectText);
        selectText = null;
      }
    }
  }

  // else {
  //   tempArr.push('*');
  // }
  return tempArr;
}

function createInsert(arr) {
  var tempJson = {
    fieldArr: [],
    valueArr: [],
  };
  if (arr == null) {
    console.error("createInsert -> blank arr found");
  } else {
    if (!Array.isArray(arr)) {
      for (var y = 0; y < arr.field.length; y++) {
        var obj = arr.field[y];
        var field = encloseField(obj, encloseFieldFlag);
        tempJson.fieldArr.push(field);
      }
      // arr.forEach(function(obj){
      //   var field = encloseField(obj, encloseFieldFlag)
      //   tempJson.fieldArr.push(field);
      // });
      for (var z = 0; z < arr.fValue.length; z++) {
        var obj = arr.fValue[z];
        if (Array.isArray(obj)) {
          subValueArr = [];
          for (var k = 0; k < obj.length; k++) {
            var objSub = obj[k];
            var fValue = objSub;
            fValue = replaceSingleQuote(fValue);
            if (fValue != null) {
              subValueArr.push("'" + fValue + "'");
            } else {
              subValueArr.push("null");
            }
          }
          if (tempJson.valueArr !== []) {
            tempJson.valueArr.push("(" + subValueArr.join() + ")");
          } else {
            tempJson.valueArr.push(", (" + subValueArr.join() + ")");
          }
        } else {
          var fValue = replaceSingleQuote(obj);
          tempJson.valueArr.push("'" + fValue + "'");
        }
      }
    } else {
      for (var s = 0; s < arr.length; s++) {
        var obj = arr[s];
        if (
          obj.encloseField != undefined &&
          typeof obj.encloseField != "boolean"
        ) {
          obj.encloseField = obj.encloseField == "false" ? false : true;
        }
        var encloseFieldFlag =
          obj.encloseField != undefined ? obj.encloseField : true;
        var field = encloseField(obj.field, encloseFieldFlag);
        var table = encloseField(obj.table ? obj.table : "");
        var fValue = obj.fValue; // ? obj.fValue : '';
        fValue = fValue == null ? fValue : replaceSingleQuote(fValue);
        tempJson.fieldArr.push(field);
        if (fValue != null) {
          tempJson.valueArr.push("'" + fValue + "'");
        } else {
          tempJson.valueArr.push("null");
        }
      }
    }
    return tempJson;
  }
}

function replaceSingleQuote(aValue) {
  if (aValue != undefined && typeof aValue === "string") {
    aValue = aValue.replace(/\'/gi, "\\'");
    return aValue;
  } else {
    return aValue;
  }
}

function createUpdate(arr) {
  var tempArr = [];
  if (arr != null) {
    for (var s = 0; s < arr.length; s++) {
      var obj = arr[s];
      if (
        obj.encloseField != undefined &&
        typeof obj.encloseField != "boolean"
      ) {
        obj.encloseField = obj.encloseField == "false" ? false : true;
      }
      var encloseFieldFlag =
        obj.encloseField != undefined ? obj.encloseField : true;
      var field = encloseField(obj.field, encloseFieldFlag);
      var table = obj.table ? encloseField(obj.table) : null;
      var fValue = obj.fValue; // ? obj.fValue : '';
      fValue = fValue == null ? fValue : replaceSingleQuote(fValue);
      var selectText = "";
      if (fValue != null) {
        if (table != null) {
          selectText = table + "." + field + "=" + "'" + fValue + "'";
        } else {
          selectText = field + "=" + "'" + fValue + "'";
        }
      } else {
        if (encloseFieldFlag == true) {
          if (table != null) {
            selectText = table + "." + field + "=null";
          } else {
            selectText = field + "=null";
          }
        } else {
          selectText = field;
        }
      }
      tempArr.push(selectText);
    }
    return tempArr;
  }
}
//Create select expression
function createAggregationFilter(obj) {
  var tempHaving = [];
  if (obj != null) {
    tempHaving = createFilter(obj);
  }
  return tempHaving;
}

exports.createFilter = function (arr) {
  return createFilter(arr);
};

//Create filter conditions set
function createFilter(arr) {
  var tempArrFilter = [];
  if (arr != null) {
    if (
      arr.hasOwnProperty("and") ||
      arr.hasOwnProperty("AND") ||
      arr.hasOwnProperty("or") ||
      arr.hasOwnProperty("OR")
    ) {
      //multiple conditions
      tempArrFilter = createMultipleConditions(arr);
    } else {
      //single condition
      var conditiontext = createSingleCondition(arr);
      tempArrFilter.push(conditiontext);
    }
  }
  return tempArrFilter;
}

function createMultipleConditions(obj) {
  var tempArrFilters = [];
  var conditionType = Object.keys(obj)[0]; //AND/OR/NONE
  if (conditionType.toString().toLowerCase() != "none") {
    var listOfConditions = obj[conditionType]; //all conditions
    for (var c = 0; c < listOfConditions.length; c++) {
      var tempConditionType = Object.keys(listOfConditions[c])[0];
      //console.log('*************' + tempConditionType + '*******************');
      if (
        tempConditionType.toString().toLowerCase() == "and" ||
        tempConditionType.toString().toLowerCase() == "or"
      ) {
        tempArrFilters.push(createMultipleConditions(listOfConditions[c]));
      } else if (tempConditionType.toString().toLowerCase() == "none") {
        var conditiontext = createSingleCondition(listOfConditions[c].none);
        tempArrFilters.push(conditiontext);
      } else {
        var conditiontext = createSingleCondition(listOfConditions[c]);
        tempArrFilters.push(conditiontext);
      }
    }
  } else {
    //single condition
    if (listOfConditions.length > 0) {
      var conditiontext = createSingleCondition(listOfConditions.none);
      tempArrFilters.push(conditiontext);
    }
  }
  var tempConditionSet =
    "(" + tempArrFilters.join(" " + conditionType + " ") + ")";
  tempArrFilters = [];
  tempArrFilters.push(tempConditionSet);
  return tempArrFilters;
}

function encloseField(a, flag) {
  if (flag == undefined || (flag != undefined && flag == true))
    return fieldIdentifier_left + a + fieldIdentifier_right;
  else return a;
}

function operatorSign(operator, value) {
  var sign = "";
  if (operator.toString().toLowerCase() == "eq") {
    if (Object.prototype.toString.call(value) === "[object Array]") {
      sign = "IN";
    } else if (typeof value === "undefined" || value == null) {
      sign = "IS";
    } else if (typeof value == "string") {
      sign = "=";
    } else {
      sign = "=";
    }
  } else if (operator.toString().toLowerCase() == "noteq") {
    if (Object.prototype.toString.call(value) === "[object Array]") {
      sign = "NOT IN";
    } else if (typeof value === "undefined" || value == null) {
      sign = "IS NOT";
    } else if (typeof value == "string") {
      sign = "<>";
    } else {
      sign = "<>";
    }
  } else if (operator.toString().toLowerCase() == "match") {
    sign = "LIKE";
  } else if (operator.toString().toLowerCase() == "notmatch") {
    sign = "NOT LIKE";
  } else if (operator.toString().toLowerCase() == "gt") {
    sign = ">";
  } else if (operator.toString().toLowerCase() == "lt") {
    sign = "<";
  } else if (operator.toString().toLowerCase() == "gteq") {
    sign = ">=";
  } else if (operator.toString().toLowerCase() == "lteq") {
    sign = "<=";
  } else {
    throw Error("Unknow operator '%s'", operator);
  }
  return sign;
}

function createSingleCondition(obj) {
  var field = obj.field,
    table = obj.table ? encloseField(obj.table) : null,
    aggregation = obj.aggregation ? obj.aggregation : null,
    operator = obj.operator,
    value = obj.value,
    encloseFieldFlag = obj.encloseField;
  if (encloseFieldFlag != undefined && typeof encloseFieldFlag != "boolean") {
    encloseFieldFlag = encloseFieldFlag == "false" ? false : true;
  }
  var conditiontext = "";
  if (aggregation != null) {
    if (encloseFieldFlag == false) {
      //CBT:this is for nested aggregation if aggregation key contains Array
      if (
        Object.prototype.toString.call(aggregation).toLowerCase() ===
        "[object array]"
      ) {
        var aggregationText = "";
        aggregation.forEach(function (d) {
          aggregationText = aggregationText + d + "(";
        });
        conditiontext = aggregationText + field;
        aggregationText = "";
        aggregation.forEach(function (d) {
          aggregationText = aggregationText + ")";
        });
        conditiontext = conditiontext + aggregationText;
      } else {
        conditiontext = aggregation + "(" + field + ")";
      }
    } else {
      if (
        Object.prototype.toString.call(aggregation).toLowerCase() ===
        "[object array]"
      ) {
        var aggregationText = "";
        aggregation.forEach(function (d) {
          aggregationText = aggregationText + d + "(";
        });
        if (table != null) {
          conditiontext = aggregationText + table + "." + encloseField(field);
        } else {
          conditiontext = aggregationText + encloseField(field);
        }
        aggregationText = "";
        aggregation.forEach(function (d) {
          aggregationText = aggregationText + ")";
        });
        conditiontext = conditiontext + aggregationText;
      } else {
        if (table != null) {
          conditiontext = aggregation + "(" + table;
          "." + encloseField(field) + ")";
        } else {
          conditiontext = aggregation + "(" + encloseField(field) + ")";
        }
      }
    }
  } else {
    if (encloseFieldFlag == false) {
      conditiontext = field;
    } else {
      if (table != null) {
        conditiontext = "" + table + "." + encloseField(field) + "";
      } else {
        conditiontext = "" + encloseField(field) + "";
      }
    }
  }

  if (operator != undefined) {
    if(Array.isArray(value) && value.length ==1)
     {
       const updatedValue = value[0];
       value = updatedValue;
     }
    var sign = operatorSign(operator, value);
    if (sign.indexOf("IN") > -1) {
      //IN condition has different format
      var tempValue = value
        .map((d) => (d != null ? d.toString().replace(/\'/gi, "\\'") : d))
        .join("','");
      conditiontext += " " + sign + " ('" + tempValue + "')";
    } else {
      var tempValue = "";
      if (typeof value === "undefined" || value == null) {
        tempValue = "null";
      } else if (typeof value === "object") {
        sign = operatorSign(operator, "");
        if (value.hasOwnProperty("field")) {
          var rTable = value.table ? value.table : "";
          tempValue = encloseField(rTable) + "." + encloseField(value.field);
        }
      } else {
        if (typeof value == "string") {
          tempValue = "'" + replaceSingleQuote(value) + "'";
        } else {
          tempValue = value;
        }
      }
      conditiontext += " " + sign + " " + tempValue;
    }
  }
  return conditiontext;
}

//create join conditions
function createJOIN(join) {
  var joinText = "";
  if (join != null) {
    var fromTbl = join.table;
    var fromTblAlias = join.alias;
    var joinwith = join.joinwith;
    // var strJoinConditions = '';
    joinText +=
      encloseField(fromTbl) + (fromTblAlias ? " as " + fromTblAlias : "");
    for (var j = 0; j < joinwith.length; j++) {
      var table = joinwith[j].table,
        tableAlias = joinwith[j].alias,
        type = joinwith[j].type ? joinwith[j].type : "INNER",
        joincondition = joinwith[j].joincondition;

      //            for (var jc = 0; jc < joincondition.length; jc++) {
      //                strJoinConditions += joincondition[jc].on + ' ' + operatorSign(joincondition[jc].operator, joincondition[jc].value) + ' ' + joincondition[jc].value + ' ';
      //            }
      joinText +=
        " " +
        type.toString().toUpperCase() +
        " JOIN " +
        encloseField(table) +
        (tableAlias ? " as " + tableAlias : "") +
        " ON " +
        createFilter(joincondition).join("");
    }
  }
  return joinText;
}

//run query
exports.execQuery = function (query, connection, cb) {
  return execQuery(query, connection, cb);
};

function execQuery(query, connection, cb) {
  // var query = arguments[0][0];
  // var connection = null;
  // var format = null;
  // if (arguments[0].length > 1) {
  //   format = arguments[0][2];
  // }
  // if (arguments[0].length > 0) {
  // connection = arguments[0][1];
  //Commenting pipe and returning full JSON;
  //return connection.query(query).stream({ highWaterMark: 5 }).pipe(objectToCSV(format));
  connection.query(query, function (err, result, fields) {
    cb(err, result, fields);
  });
  // } else {
  //   return {
  //     status: false,
  //     content: {
  //       result: 'Connection not specified.'
  //     }
  //   };
  // }
}
/*
function objectToCSV(format) {
var stream = require('stream')
var liner = new stream.Transform({ objectMode: true })
var csv = [];
var isFirstChunk = true;
liner._transform = function (chunk, encoding, done) {
if (format == 'csv') {
var keys = Object.keys(chunk);
csv = [];
if (isFirstChunk == true) {
for (var i = 0; i < keys.length; i++) {
csv.push(keys[i]);
}
}
else {
for (var i = 0; i < keys.length; i++) {
csv.push(chunk[keys[i]]);
}
}
this.push(csv.join());
}
else if (format == 'jsonArray') {
var strChunk = '';
if (isFirstChunk == true) {
strChunk += '[' + JSON.stringify(chunk);
}
else {
strChunk += ',' + JSON.stringify(chunk);
}
this.push(strChunk);
}
else {
this.push(chunk);
}
isFirstChunk = false;
done()
}

liner._flush = function (done) {
if (format == 'jsonArray') {
this.push(']');
}
done()
}

return liner;
}
*/