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

No alert changes

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