@fitzy/jsql - npm Package Compare versions

Comparing version 1.0.0 to 1.0.1

index.js

@@ -6,6 +6,3 @@ "use strict";
     static select(query, data) {
-        // Get data source table (FROM)
-        let fromIdx = query.toUpperCase().indexOf('FROM', 6);
-        if (fromIdx < 0) throw new Error('invalid SQL syntax. No FROM found');
-        const table = query.substring(fromIdx + 4, query.indexOf(' ', fromIdx + 5)).trim();
+        const table = query.match(/\sfrom\s+(\w)+($|\s)/gi)[0].trim().split(/\s/)[1];
 
@@ -15,3 +12,3 @@ // Get fields we are selecting
         if (selectIdx < 0) throw new Error('invalid SQL syntax. No SELECT found');
-        let fieldsRgx = /(\s([\w*]+[,\s]+)+)(?=FROM)/gi;
+        let fieldsRgx = /(\s([\w\.*]+[,\s]+)+)(?=FROM)/gi;
         const fields = query.match(fieldsRgx)[0].split(',').map(f => f.trim());
@@ -21,4 +18,5 @@ if (fields.includes('*')) fields.push(...Object.keys(data[table][0]));
         // Get any conditions
-        let re = /(\s\w+\s)(?:<>)(\s[\w\d]+)|(\s\w+\s)(?:<=)(\s[\w\d]+)|(?:>=)(\s[\w\d]+)|(\s\w+\s)[=><](\s[\w\d]+)|(\s\w+\s)like(\s[\w"%]+)|(\s\w+\s)in(\s\(.+\))|(\s\w+\s)(between)(\s[\w\d]+){3}/gi;
-        const conditions = query.match(re).map(c => c.trim());
+        let re = /(\s*[\w\.]+\s*)((<=)|(>=)|(<>)|[<>=])(\s*[\w\d"']+)|(\s*[\w\.]+\s*)like(\s[\w"'%]+)|(\s*[\w\.]+\s*)between(\s[\w\d]+){3}|(\s*[\w\.]+\s*)in(\s\(.+\))/gi
+        const conditionsMatch = query.match(re);
+        let conditions = conditionsMatch === null ? null : conditionsMatch.map(c => c.trim().replace(/'|"/g, ''));
 
@@ -42,5 +40,7 @@ // Get order
         let filters = [];
-        conditions.forEach(c => {
-            filters.push(this.getCondition(c));
-        });
+        if (conditions !== null) {
+            conditions.forEach(c => {
+                filters.push(this.getCondition(c));
+            });
+        }
 
@@ -65,13 +65,30 @@ // Do the filtering and return result
             // need to get two values
-            return { field: elements[0], comparison: elements[1], value1: !isNaN(+elements[2]) ? elements[2] : +elements[2], value2: !isNaN(+elements[4]) ? elements[4] : +elements[4] };
+            return { 
+                field: elements[0], 
+                comparison: elements[1], 
+                value1: !isNaN(+elements[2]) ? elements[2] : +elements[2], 
+                value2: !isNaN(+elements[4]) ? elements[4] : +elements[4] 
+            };
         }
         if (elements[1].toLowerCase() === 'in') {
             // get the list
-            return { field: elements[0], comparison: elements[1], value1: conditionString.match(/\([\d\w,\s]+\)/)[0] };
+            return { 
+                field: elements[0], 
+                comparison: elements[1], 
+                value1: conditionString.match(/\([\d\w,\s]+\)/)[0].toLowerCase()
+            };
         }
         if (elements[1].toLowerCase() === 'like') {
             // get the expression
-            return { field: elements[0], comparison: elements[1], value1: elements[2].replace(/["']/g, '') };
+            return { 
+                field: elements[0], 
+                comparison: elements[1], 
+                value1: elements[2].replace(/["']/g, '').toLowerCase()
+            };
         }
-        return { field: elements[0], comparison: elements[1], value1: !isNaN(+elements[2]) ? elements[2] : +elements[2] };
+        return { 
+            field: elements[0], 
+            comparison: elements[1], 
+            value1: isNaN(+elements[2]) ? elements[2].toLowerCase() : +elements[2] 
+        };
     }
@@ -89,31 +106,42 @@
             filters.forEach(f => {
+                // Handles any nested objects
+                let keys = f.field.split('.');
+                let datum = row[keys[0]];
+                if (keys.length > 1) {
+                    for (let i = 1; i < keys.length; i++) {
+                        datum = datum[keys[i]];
+                    }
+                }
+                if (datum === undefined) return acc;
+                datum = isNaN(+datum) ? datum.toLowerCase() : +datum;
+
                 switch (f.comparison.toLowerCase()) {
                     case '=':
-                        pass *= +(row[f.field] === f.value1);
+                        pass *= +(datum === f.value1);
                         break;
                     case '<>':
-                        pass *= +(row[f.field] !== f.value1);
+                        pass *= +(datum !== f.value1);
                         break;
                     case '>':
-                        pass *= +(row[f.field] > f.value1);
+                        pass *= +(datum > f.value1);
                         break;
                     case '<':
-                        pass *= +(row[f.field] < f.value1);
+                        pass *= +(datum < f.value1);
                         break;
                     case '<=':
-                        pass *= +(row[f.field] <= f.value1);
+                        pass *= +(datum <= f.value1);
                         break;
                     case '>=':
-                        pass *= +(row[f.field] >= f.value1);
+                        pass *= +(datum >= f.value1);
                         break;
                     case 'like':
                         let term = f.value1.replace(/%/g, '.*');
-                        let re = new RegExp(`${f.value1.charAt(0) === '%' ? '' : '^'}${term}${f.value1.charAt(f.value1.length - 1) === '%' ? '' : '$'}`)
-                        pass *= +(row[f.field].match(re) !== null);
+                        let re = new RegExp(`${f.value1.charAt(0) === '%' ? '' : '^'}${term}${f.value1.charAt(f.value1.length - 1) === '%' ? '' : '$'}`, 'i')
+                        pass *= +(datum.match(re) !== null);
                         break;
                     case 'in':
-                        pass *= +(f.value1.replace(/[\(\)]/g, '').split(/\s*,\s*/).includes(row[f.field].toString()));
+                        pass *= +(f.value1.replace(/[\(\)]/g, '').split(/\s*,\s*/).includes(datum));
                         break;
                     case 'between':
-                        pass *= +(row[f.field] >= f.value1 && row[f.field] <= f.value2);
+                        pass *= +(datum >= f.value1 && row[f.field] <= f.value2);
                         break;
@@ -124,5 +152,16 @@ }
                 let mappedRow = {};
-                Object.keys(row).forEach(key => {
-                    if (fields.includes(key)) {
-                        mappedRow[key] = row[key];
+                fields.forEach(field => {
+                    let split = field.split('.');
+                    if (row[split[0]] !== undefined) {
+                        // gonna be included
+                        let val = row[split[0]];
+                        let key = split[0];
+                        if (split.length > 1) {
+                            // nested
+                            for (let i = 1; i < split.length; i++) {
+                                val = val[split[i]];
+                                key = split[i];
+                            }
+                        }
+                        mappedRow[key] = val;
                     }
@@ -129,0 +168,0 @@ });

package.json

 {
   "name": "@fitzy/jsql",
-  "version": "1.0.0",
+  "version": "1.0.1",
   "description": "A utility to allow using SQL statements on js arrays of objects",
   "main": "index.js",
   "scripts": {
-    "test": "echo \"Error: no test specified\" && exit 1",
-    "wp": "npx webpack"
+    "wp": "npx webpack",
+    "test": "jasmine",
+    "pub": "npm publish --access public"
   },
@@ -17,2 +18,3 @@ "author": "Andrew Fitzgerald <[e-mail]>",
   "devDependencies": {
+    "jasmine": "^3.5.0",
     "webpack": "^4.41.1",
@@ -19,0 +21,0 @@ "webpack-cli": "^3.3.9"

README.md

@@ -6,3 +6,5 @@ # jSQL
 ### Install
+```
 $ npm i @fitzy/jsql
+```
 
@@ -14,2 +16,3 @@ ### Usage
 jsql.select('SELECT * FROM myData', { myData: [{a: 1, b: 'foo'}, {a: 4, b: 'bar'}, ...], relatedData: [] });
+```
 
@@ -19,2 +22,14 @@ The data is passed in as an array to:
 b) allow me to expand this to have joins later
-```
+
+### Supported Syntax
+* SELECT
+* FROM
+* WHERE (=, <=, >=, <, >, <>, like, in, between)
+* ORDER BY
+
+### Yet to Implement
+* GROUP BY
+* HAVING
+* Aggregate functions (avg, count, etc)
+* Dates
+* Aliasing (i.e. AS)

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

Major refactor

Supply chain risk

Package has recently undergone a major refactor. It may be unstable or indicate significant internal changes. Use caution when updating to versions that include significant changes.

Found 1 instance in 1 package

Filesystem access

Supply chain risk

Accesses the file system, and could potentially read sensitive data.

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

No tests

Quality

Package does not have any tests. This is a strong signal of a poorly maintained or low quality package.

Found 1 instance in 1 package

Improved metrics

Total package byte prevSize

29181

increased by290.59%

Number of package files

11

increased by266.67%

Lines of code

702

increased by394.37%

Number of low quality alerts

0

decreased by-100%

Number of lines in readme file

32

increased by77.78%

Worsened metrics

Dev dependency count

3

increased by50%

Number of low supply chain risk alerts

1

increased byInfinity%

Number of medium supply chain risk alerts

1

increased byInfinity%

No dependency changes