@synatic/sql-to-mongo - npm Package Compare versions

Comparing version 1.1.17 to 1.1.18

lib/canQuery.js

 const _allowableFunctions = require('./MongoFunctions');
 const {isSelectAll: checkIfIsSelectAll} = require('./isSelectAll');
 const {parseSQLtoAST} = require('./parseSQLtoAST');
-
 /**
@@ -96,3 +95,4 @@ * Checks whether the expression is null or its type is null
     const hasGroupBy = !!ast.groupby;
-    const hasDistinct = !_checkNullOrEmptyType(ast.distinct);
+    const hasDistinct =
+        ast.distinct === 'DISTINCT' || !_checkNullOrEmptyType(ast.distinct);
     const containsAllowedAggregateFunctions =
@@ -118,2 +118,3 @@ !isSelectAll &&
     const hasTableAlias = !!(ast.from && ast.from[0] && ast.from[0].as);
+    // const hasForcedGroupBy = forceGroupBy(ast);
 
@@ -134,5 +135,20 @@ const isAggregate =
         hasTableAlias;
+
     return !isAggregate;
 }
 
+function isAllowableType(expr) {
+    if (expr.type === 'function' && expr.args) {
+        if (expr.args.type === 'expr_list') {
+            return expr.args.value.every((val) => isAllowableType(val));
+        } else {
+            return false;
+        }
+    } else if (expr.type === 'column_ref') {
+        return true;
+    } else {
+        return ['number', 'string', 'single_quote_string'].includes(expr.type);
+    }
+}
+
 /**
@@ -155,6 +171,5 @@ * Checks whether the expression statement contains other tables to execute a sub select
     if (expr.type === 'expr_list') {
-        return !expr.value.every((val) =>
-            ['number', 'string', 'single_quote_string'].includes(val.type)
-        );
+        return !expr.value.every((val) => isAllowableType(val));
     }
+
     return false;
@@ -161,0 +176,0 @@ }

lib/make/index.js

@@ -28,3 +28,3 @@ const $check = require('check-types');
         ),
-        collections: getTables(ast),
+        collections: getTables(ast).filter((x, i, a) => a.indexOf(x) === i),
         type: 'aggregate',
@@ -71,2 +71,6 @@ };
 
+    if (subAst._next) {
+        tables = tables.concat(getTables(subAst._next));
+    }
+
     return tables;
@@ -73,0 +77,0 @@ }

lib/make/makeAggregatePipeline.js

@@ -9,2 +9,4 @@ const groupByColumnParserModule = require('./groupByColumnParser');
 const {createResultObject} = require('./createResultObject');
+const {forceGroupBy} = require('./forceGroupBy');
+
 const {
@@ -14,5 +16,4 @@ getWhereAstQueries,
 } = require('./filter-queries');
-const _allowableFunctions = require('../MongoFunctions');
+
 const $copy = require('clone-deep');
-const $json = require('@synatic/json-magic');
 
@@ -29,33 +30,3 @@ exports.makeAggregatePipeline = makeAggregatePipeline;
  */
-function _forceGroupBy(ast) {
-    if (ast.groupby) {
-        return false;
-    }
 
-    const potentialFuncs = [];
-    $json.walk(ast, (val, path) => {
-        const pathParts = path.split('/').slice(1);
-        if (val === 'aggr_func') {
-            potentialFuncs.push(
-                pathParts.slice(0, pathParts.length - 1).join('.')
-            );
-        }
-    });
-    let forceGroupBy = false;
-
-    for (const potentialFunc of potentialFuncs) {
-        const funcObj = $json.get(ast, potentialFunc);
-        if (funcObj && funcObj.name && !potentialFunc.startsWith('from.')) {
-            const definition = _allowableFunctions.functionByNameAndType(
-                funcObj.name,
-                'aggr_func'
-            );
-            forceGroupBy =
-                forceGroupBy || (definition && definition.forceGroup);
-        }
-    }
-
-    return forceGroupBy;
-}
-
 // function getAggrFunctions(columns) {
@@ -214,3 +185,3 @@ //     const potentialFuncs = [];
 
-    const checkForceGroupBy = _forceGroupBy(ast);
+    const checkForceGroupBy = forceGroupBy(ast);
 
@@ -419,5 +390,18 @@ if (ast.groupby || checkForceGroupBy) {
         const otherPipeline = makeAggregatePipeline(ast._next, options);
+        const unionCollection =
+            ast._next.from[0].table ||
+            (ast._next.from[0].expr &&
+            ast._next.from[0].expr.ast &&
+            ast._next.from[0].expr.ast.from &&
+            ast._next.from[0].expr.ast.from[0] &&
+            ast._next.from[0].expr.ast.from[0].table
+                ? ast._next.from[0].expr.ast.from[0].table
+                : null) ||
+            null;
+        if (!unionCollection) {
+            throw new Error('No collection for union with');
+        }
         pipeline.push({
             $unionWith: {
-                coll: ast._next.from[0].table,
+                coll: unionCollection,
                 pipeline: otherPipeline,
@@ -429,5 +413,18 @@ },
         const otherPipeline = makeAggregatePipeline(ast._next, options);
+        const unionCollection =
+            ast._next.from[0].table ||
+            (ast._next.from[0].expr &&
+            ast._next.from[0].expr.ast &&
+            ast._next.from[0].expr.ast.from &&
+            ast._next.from[0].expr.ast.from[0] &&
+            ast._next.from[0].expr.ast.from[0].table
+                ? ast._next.from[0].expr.ast.from[0].table
+                : null) ||
+            null;
+        if (!unionCollection) {
+            throw new Error('No collection for union with');
+        }
         pipeline.push({
             $unionWith: {
-                coll: ast._next.from[0].table,
+                coll: unionCollection,
                 pipeline: otherPipeline,
@@ -434,0 +431,0 @@ },

lib/make/makeFilterCondition.js

 const makeProjectionExpressionPartModule = require('./makeProjectionExpressionPart');
 const {sqlStringToRegex} = require('./sqlStringToRegex');
 const makeQueryPart = require('./makeQueryPart');
+const $check = require('check-types');
 
@@ -25,2 +26,4 @@ exports.makeFilterCondition = makeFilterCondition;
     '*': '$multiply',
+    IN: '$in',
+    'NOT IN': '$nin',
 };
@@ -91,11 +94,12 @@ /**
         if (queryOperatorMap[queryPart.operator]) {
+            const left = makeQueryPart.makeQueryPart(
+                queryPart.left,
+                false,
+                [],
+                includeThis
+            );
             return {
                 [queryOperatorMap[queryPart.operator]]: [
+                    $check.string(left) ? `$${left}` : left,
                     makeQueryPart.makeQueryPart(
-                        queryPart.left,
-                        false,
-                        [],
-                        includeThis
-                    ),
-                    makeQueryPart.makeQueryPart(
                         queryPart.right,
@@ -102,0 +106,0 @@ false,

lib/make/makeJoinForPipeline.js

@@ -17,4 +17,17 @@ const makeFilterConditionModule = require('./makeFilterCondition');
 
+    const aliases = ast.from
+        .map((f) => {
+            if (f.as) {
+                return f.as;
+            } else if (f.table) {
+                return f.table;
+            } else {
+                return null;
+            }
+        })
+        .filter((f) => !!f)
+        .map((a) => a.split('|')[0]);
+
     for (let i = 1; i < ast.from.length; i++) {
-        makeJoinPart(ast.from[i], ast.from[i - 1], pipeline);
+        makeJoinPart(ast.from[i], ast.from[i - 1], aliases, pipeline);
     }
@@ -26,10 +39,11 @@
 /**
+ * Makes a single join part
  *
- *
  * @param {import('../types').TableDefinition} join
  * @param {import('../types').TableDefinition} previousJoin
+ * @param {string[]} aliases - the aliases used int he joins
  * @param {import('../types').PipelineFn[]} pipeline
  * @returns {void}
  */
-function makeJoinPart(join, previousJoin, pipeline) {
+function makeJoinPart(join, previousJoin, aliases, pipeline) {
     let toTable = join.table || '';
@@ -50,2 +64,4 @@ let toAs = join.as || '';
         join.on.type === 'binary_expr' &&
+        join.on.left.type === 'column_ref' &&
+        join.on.right.type === 'column_ref' &&
         join.on.operator === '='
@@ -80,2 +96,11 @@ ) {
             replacePaths.push({path: path, newVal: `$$${varName}`});
+        } else if (
+            $check.string(val) &&
+            aliases.find(
+                (a) => a !== toAs && a !== toTable && val.startsWith(`$${a}.`)
+            )
+        ) {
+            const varName = val.substring(1).replace(/[.-]/g, '_');
+            inputVars[varName] = `$${val.substring(1)}`;
+            replacePaths.push({path: path, newVal: `$$${varName}`});
         }
@@ -82,0 +107,0 @@ });

lib/make/makeQueryPart.js

@@ -5,2 +5,4 @@ const makeProjectionExpressionPartModule = require('./makeProjectionExpressionPart');
 const makeCastPartModule = require('./makeCastPart');
+const {makeCaseCondition} = require('./makeCaseCondition');
+const {isValueType} = require('./isValueType');
 
@@ -42,3 +44,3 @@ exports.makeQueryPart = makeQueryPart;
                     ? `${table}.${queryPartToUse.column}`
-                    : queryPartToUse.column)
+                    : `${queryPartToUse.column}`)
             );
@@ -65,6 +67,9 @@ } else {
         );
-        if ($check.string(left) && !left.startsWith('$')) {
+
+        if ($check.string(left) && isValueType(queryPart.right.type, right)) {
             return {[left]: {[op]: right}};
         } else {
-            return {$expr: {[op]: [left, right]}};
+            return {
+                $expr: {[op]: [$check.string(left) ? `$${left}` : left, right]},
+            };
         }
@@ -241,2 +246,7 @@ };
 
+    // case
+    if (queryPart.type === 'case') {
+        return makeCaseCondition(queryPart);
+    }
+
     const columnNameOrValue = getColumnNameOrVal(queryPart);
@@ -246,3 +256,3 @@ if (queryPart.type !== 'null' && !$check.assigned(columnNameOrValue)) {
     }
-    return getColumnNameOrVal(queryPart);
+    return columnNameOrValue;
 }

package.json

 {
   "name": "@synatic/sql-to-mongo",
-  "version": "1.1.17",
+  "version": "1.1.18",
   "description": "Convert SQL to mongo queries or aggregates",
@@ -5,0 +5,0 @@ "main": "index.js",

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

228427

increased by2.69%

Number of package files

28

increased by7.69%

Lines of code

4956

increased by3.77%

No dependency changes