mg-dbop - npm Package Compare versions

Comparing version 0.0.10 to 0.0.11

dbOp.js

@@ -16,5 +16,5 @@ /**
     this.joinTables = [];
+    this.fromTables = [];
     this.selectSql = "";
     this.whereSql = "";
-    this.fromSql = "FROM ";
     this.orderbySql = "";
@@ -25,4 +25,9 @@ this.limitSql = "";
     this.console = false;
+    this.jsonColumnMap = null;
+    this.rowFilterRemoveErantPeriod = false;
+    this.rowFilterRemoveNullRow = false;
   }
 
+
+
   /**
@@ -41,4 +46,2 @@ * joinStruct is the { "<table>.<alias>.<column>" : "<table>.<alias>.<column>" } for defining the tables
 
-    const tableFromSql = [];
-
     for (let t in joinStruct) {
@@ -60,3 +63,3 @@ const left = t.split(".");
 
-        tableFromSql.push({
+        this.fromTables.push({
           alias: leftTable.alias,
@@ -78,3 +81,3 @@ from: "`" + leftTable.name + "` " + leftTable.alias,
 
-          tableFromSql.push({
+          this.fromTables.push({
             alias: rightTable.alias,
@@ -138,5 +141,5 @@ from: "`" + rightTable.name + "` " + rightTable.alias,
           // Create the LEFT JOIN
-          for ( let t of tableFromSql ){
-            if ( t.alias == leftTable.alias ){
-              t.joins.push( " LEFT JOIN " + rightTable.name + " " + rightTable.alias + " ON " + leftTable.alias + ".`" + left[2] + "` = " + rightTable.alias + ".`" + right[2] + "`" );
+          for (let t of this.fromTables) {
+            if (t.alias == leftTable.alias) {
+              t.joins.push(" LEFT JOIN " + rightTable.name + " " + rightTable.alias + " ON " + leftTable.alias + ".`" + left[2] + "` = " + rightTable.alias + ".`" + right[2] + "`");
               break;
@@ -149,15 +152,2 @@ }
 
-    // Now we need to create the FROM SQL
-    for ( let t of tableFromSql ){
-      this.fromSql += t.from;
-      if ( t.joins.length > 0 ){
-        this.fromSql += t.joins.join(" ");
-      }
-      this.fromSql += ",";
-    }
-    if (this.fromSql.endsWith(",")) {
-      this.fromSql = this.fromSql.substring(0, this.fromSql.length - 1);
-    }
-
-
     if (this.whereSql.endsWith(" AND ")) {
@@ -170,2 +160,17 @@ this.whereSql = this.whereSql.substring(0, this.whereSql.lastIndexOf(" AND"));
 
+  getFrom(){
+    return this.fromTables;
+  }
+
+  setFrom(_fromTables){
+    this.fromTables = _fromTables;
+  }
+
+  setOptions(config) {
+    this.jsonColumnMap = config.dataTableJsonColumnMap ? config.dataTableJsonColumnMap : null;
+    this.rowFilterRemoveErantPeriod = config.rowFilterRemoveErantPeriod ? config.rowFilterRemoveErantPeriod : false;
+    this.rowFilterRemoveNullRow = config.rowFilterRemoveNullRow ? config.rowFilterRemoveNullRow : false;
+  }
+
+
   selectAll() {
@@ -270,3 +275,3 @@ // Go through the core tables
 
-    return "SELECT " + this.selectSql + " " + this.fromSql + " " + this.whereSql + " " + this.orderbySql + " " + this.limitSql;
+    return "SELECT " + this.selectSql + " " + generateFromStatement(this.fromTables) + " " + this.whereSql + " " + this.orderbySql + " " + this.limitSql;
   }
@@ -279,3 +284,3 @@
 
-    let sql = "SELECT " + this.selectSql + " " + this.fromSql + " " + this.whereSql + " " + this.orderbySql + " " + this.limitSql;
+    const sql = "SELECT " + this.selectSql + " " + generateFromStatement(this.fromTables) + " " + this.whereSql + " " + this.orderbySql + " " + this.limitSql;
 
@@ -286,3 +291,6 @@ if (this.console) {
 
-    return await this.dbConn.query({ sql: sql, nestTables: "." }, this.values);
+    return this.filterRows(await this.dbConn.query({
+      sql: sql,
+      nestTables: "."
+    }, this.values));
   }
@@ -296,3 +304,3 @@
   async count() {
-    let sql = "SELECT count(*) as t " + this.fromSql + " " + this.whereSql;
+    const sql = "SELECT count(*) as t " + generateFromStatement(this.fromTables) + " " + this.whereSql;
     const row = await this.dbConn.query(sql, this.values);
@@ -302,3 +310,28 @@ return (row == null || row.length == 0) ? 0 : row[0].t;
 
+
   /**
+   * Applies the clean up to the rows before it is sent back
+   */
+  filterRows(rows) {
+    if ((this.rowFilterRemoveErantPeriod == false && this.rowFilterRemoveNullRow == false) || rows.length == 0)
+      return rows;
+
+    for (let row of rows) {
+      for (let col in row) {
+        if (this.rowFilterRemoveNullRow && row[col] == null) {
+          delete row[col];
+          continue;
+        }
+
+        if (this.rowFilterRemoveErantPeriod && col.charAt(0) == '.') {
+          row[col.substring(1)] = row[col];
+          delete row[col];
+        }
+      }
+    }
+
+    return rows;
+  }
+
+  /**
    * For handling the DataTables
@@ -312,6 +345,8 @@ * https://datatables.net/manual/server-side
   dataTableFilter(req) {
+    if (typeof req.query == "undefined")
+      return;
 
     // AutoFilter; for fields that are part of the string
-    // AutoFilter; for fields that are part of the string
     const filteredColumns = {};
+
     for (let tableName in this.tables) {
@@ -328,2 +363,15 @@ const table = this.tables[tableName];
       }
+
+      // Check to see if any of the query params are for this query for the custom JSon map
+      if (this.jsonColumnMap == null)
+        continue;
+
+      for (let prefix in this.jsonColumnMap) {
+        for (let queryParam in req.query) {
+          if (queryParam.startsWith(table.alias + "." + prefix)) {
+            this.where(table.alias + ".`" + this.jsonColumnMap[prefix] + "` -> \"$." + queryParam.substring(queryParam.indexOf(prefix) + prefix.length) + "\"=?", req.query[queryParam]);
+            filteredColumns[queryParam] = true;
+          }
+        }
+      }
     }
@@ -345,3 +393,24 @@
           if (col.searchable == "true" && !_.has(filteredColumns, col.data)) {
-            where += this.transformColumn(col.data);
+            const columnName = transformColumn(col.data);
+
+            if (this.jsonColumnMap != null && columnName.indexOf(".") > 0) {
+              const tableAlias = columnName.substring(0,columnName.indexOf("."));
+              let bFound = false;
+              for (let prefix in this.jsonColumnMap) {
+                if (columnName.startsWith(tableAlias + "." + prefix)) {
+                  where += tableAlias + ".`" + this.jsonColumnMap[prefix] + "` -> ";
+                  where += "\"$." + columnName.substring(columnName.indexOf(prefix) + prefix.length) + "\"";
+                  where += " LIKE ? OR ";
+                  whereVals.push("%" + searchVal + "%");
+                  bFound = true;
+                  break;
+                }
+              }
+
+              // If we added in this column we don't want to put it as part of the core search
+              if ( bFound )
+                continue;
+            }
+
+            where += columnName;
             where += " LIKE ? OR ";
@@ -362,3 +431,3 @@ whereVals.push("%" + searchVal + "%");
       this.selectSql = req.query.selectcolumns;
-    } else if (req.query.fields) {
+    } else if (req.query && req.query.fields) {
       this.selectSql = req.query.fields;
@@ -370,3 +439,3 @@ }
       const colOrderIndex = req.query.order[0].column * 1;
-      const colOrderName = this.transformColumn(req.query.columns[colOrderIndex].data);
+      const colOrderName = transformColumn(req.query.columns[colOrderIndex].data);
       this.orderbySql = " ORDER BY " + colOrderName + " " + ((req.query.order[0].dir == "asc") ? "asc" : "desc");
@@ -392,17 +461,36 @@ }
 
-  transformColumn(colName) {
-    let s = colName.indexOf("\\");
-    if (s >= 0) {
-      colName = colName.substring(0, s) + colName.substring(s + 1);
-    }
 
-    s = colName.indexOf("_");
-    if (s >= 0) {
-      return colName.substring(0, s) + "." + colName.substring(s + 1);
+}
+
+module.exports = dbOp;
+
+
+//------------------------------------------------------------
+//- Suporting functions
+
+function generateFromStatement(fromArray) {
+  let fromSql = "FROM ";
+
+  // Now we need to create the FROM SQL
+  for (let t of fromArray) {
+    fromSql += t.from;
+    if (t.joins.length > 0) {
+      fromSql += t.joins.join(" ");
     }
+    fromSql += ",";
+  }
+  if (fromSql.endsWith(",")) {
+    fromSql = fromSql.substring(0, fromSql.length - 1);
+  }
 
-    return colName;
+  return fromSql;
+}
+
+
+function transformColumn(colName) {
+  let s = colName.indexOf("\\");
+  if (s >= 0) {
+    colName = colName.substring(0, s) + colName.substring(s + 1);
   }
+  return colName;
 }
-
-module.exports = dbOp;

package.json

 {
   "name": "mg-dbop",
   "private" : false,
-  "version": "0.0.10",
+  "version": "0.0.11",
   "homepage": "https://github.com/MacLaurinGroup/dbOp",
@@ -6,0 +6,0 @@ "description": "Query builder, and validator, for MySQL and node that utilizes the underlying table to provide a level of checking",

readme.md

@@ -240,2 +240,5 @@ ## dbOp
 * .dataTableExecute()   // executes the query, creating a struct that DataTable wants
+* .setOptions( {} )  // See below
+* .getFrom()     // Gets inner data object for all the tables in the FROM statement
+* .setFrom([])   // Sets the inner data object; allowing to augment the tables
 * async .run()
@@ -245,5 +248,18 @@ * async .runFirstRow()
 
+```
+.setOptions({
+  dataTableJsonColumnMap : {
+    "__":"jsColumnName"       // for auto JSon search within a JSON type of field; co.__year will search for 'year' in the JSon column
+  },
+  "rowFilterRemoveErantPeriod" : true,   // Remove period in column name in the result that starts with .
+  "rowFilterRemoveNullRow" : true,       // Remove any null values in the columns
+})
+```
 
 ## Updates
 
+* 2019-03-30
+  * Added in setOptions() for cleaning up rows
+  * Added JSon searching in the dataTableFilter()
+  * Added ability to add to the from table list
 * 2019-03-28
@@ -250,0 +266,0 @@ * Fixed bug with the order to which the LEFT JOIN is added into the SQL

New alerts

License Policy Violation

License

This package is not allowed per your license policy. Review the package's license to ensure compliance.

Found 1 instance in 1 package

Fixed alerts

License Policy Violation

License

This package is not allowed per your license policy. Review the package's license to ensure compliance.

Found 1 instance in 1 package

Improved metrics

Total package byte prevSize

47206

increased by7.96%

Lines of code

759

increased by9.84%

Number of lines in readme file

280

increased by6.06%

No dependency changes