import { assert } from "chai" import _ from "lodash" import { default as Schema } from "../src/Schema" import { default as ExprCleaner } from "../src/ExprCleaner" import * as fixtures from "./fixtures" import canonical from "canonical-json" import { Expr, FieldExpr, OpExpr, ScalarExpr, SubqueryExpr, Table, Variable } from "../src" import produce from "immer" function compare(actual: any, expected: any) { assert.equal( canonical(actual), canonical(expected), "\ngot: " + canonical(actual) + "\nexp: " + canonical(expected) + "\n" ) } const variables: Variable[] = [ { id: "varenum", name: { _base: "en", en: "Varenum" }, type: "enum", enumValues: [ { id: "a", name: { _base: "en", en: "A" } }, { id: "b", name: { _base: "en", en: "B" } } ] }, { id: "varnumber", name: { _base: "en", en: "Varnumber" }, type: "number" }, { id: "varnumberexpr", name: { _base: "en", en: "Varnumberexpr" }, type: "number", table: "t1" }, { id: "varid", name: { _base: "en", en: "Varid" }, type: "id", idTable: "t1" } ] describe("ExprCleaner", function () { beforeEach(function () { this.schema = fixtures.simpleSchema().addVariables(variables, {}) this.exprCleaner = new ExprCleaner(this.schema) this.clean = (expr: any, expected: any, options: any) => { compare(this.exprCleaner.cleanExpr(expr, options), expected) } }) describe("cleanExpr", function () { it("nulls if wrong table", function () { assert.isNull(this.exprCleaner.cleanExpr({ type: "field", table: "t1", column: "text" }, { table: "t2" })) }) it("nulls if wrong type", function () { const field = { type: "field", table: "t1", column: "enum" } assert.isNull(this.exprCleaner.cleanExpr(field, { types: ["text"] })) }) it("nulls if wrong idTable", function () { const field = { type: "id", table: "t1" } assert(this.exprCleaner.cleanExpr(field, { types: ["id"], idTable: "t1" })) assert.isNull(this.exprCleaner.cleanExpr(field, { types: ["id"], idTable: "t2" })) }) it("nulls if wrong enums", function () { const field = { type: "field", table: "t1", column: "enum" } assert.isNotNull(this.exprCleaner.cleanExpr(field, { enumValueIds: ["a", "b", "c"] })) assert.isNull(this.exprCleaner.cleanExpr(field, { enumValueIds: ["a"] })) }) it("nulls if wrong enums expression", function () { const field = { type: "field", table: "t1", column: "expr_enum" } assert.isNotNull(this.exprCleaner.cleanExpr(field, { enumValueIds: ["a", "b", "c"] })) assert.isNull(this.exprCleaner.cleanExpr(field, { enumValueIds: ["a"] })) }) it("nulls if missing variable", function () { assert.isNotNull(this.exprCleaner.cleanExpr({ type: "variable", variableId: "varnumber" }, { table: "t2" })) assert.isNull(this.exprCleaner.cleanExpr({ type: "variable", variableId: "varxyz" }, { table: "t2" })) }) it("allows variable if right id table", function () { assert.isNotNull( this.exprCleaner.cleanExpr({ type: "variable", variableId: "varid" }, { table: "t2", idTable: "t1" }) ) assert.isNull( this.exprCleaner.cleanExpr({ type: "variable", variableId: "varid" }, { table: "t2", idTable: "t2" }) ) }) it("leaves in extra op exprs if moreExprType is set", function () { const after = this.exprCleaner.cleanExpr({ type: "op", table: "t1", op: "concat", exprs: [ { type: "field", table: "t1", column: "text" }, { type: "field", table: "t1", column: "text" }, { type: "field", table: "t1", column: "text" } ] }) assert.equal(after.exprs.length, 3) }) it("leaves in single extra date/datetime op expr if ordering is allowed", function () { const after = this.exprCleaner.cleanExpr({ type: "op", table: "t1", op: "last", exprs: [ { type: "field", table: "t1", column: "text" }, { type: "field", table: "t1", column: "datetime" } ] }, { aggrStatuses: ["aggregate"] }) assert.equal(after.exprs.length, 2) assert.equal(after.exprs[0].type, "field") assert.deepEqual(after.exprs[1], { type: "field", table: "t1", column: "datetime" }) }) it("cleans extra non-ordering op exprs if ordering is allowed, removing extra exprs", function () { const after = this.exprCleaner.cleanExpr({ type: "op", table: "t1", op: "last", exprs: [ { type: "field", table: "t1", column: "text" }, null ] }, { aggrStatuses: ["aggregate"] }) assert.equal(after.exprs.length, 1) }) it("nulls recursive field expr", function () { const table: Table = this.schema.getTable("t1") const newTable = produce(table, (draft) => { draft.contents.push({ id: "expr_recursive", name: { _base: "en", en: "Expr Recursive" }, type: "expr", expr: { type: "field", table: "t1", column: "expr_recursive" } }) }) const schema = this.schema.addTable(newTable) const exprCleaner = new ExprCleaner(schema) assert.isNull(exprCleaner.cleanExpr({ type: "field", table: "t1", column: "expr_recursive" })) }) it("nulls if expr is invalid", function () { const table: Table = this.schema.getTable("t1") const newTable = produce(table, (draft) => { draft.contents.push( { id: "expr_invalid", name: { _base: "en", en: "Expr Invalid" }, type: "expr", expr: { type: "field", table: "t1", column: "nonsuch" } }, { id: "expr_valid", name: { _base: "en", en: "Expr Invalid" }, type: "expr", expr: { type: "field", table: "t1", column: "enum" } } ) }) const schema = this.schema.addTable(newTable) const exprCleaner = new ExprCleaner(schema) assert.isNull(exprCleaner.cleanExpr({ type: "field", table: "t1", column: "expr_invalid" })) assert(exprCleaner.cleanExpr({ type: "field", table: "t1", column: "expr_valid" })) }) it("cleans aggregate op", function () { const expr = { type: "op", table: "t1", op: "and", exprs: [ { type: "op", op: "=", table: "t1", exprs: [{ type: "op", table: "t1", op: "count", exprs: [] }, null] }, null ] } compare( this.exprCleaner.cleanExpr(expr, { aggrStatuses: ["aggregate", "individual", "literal"], types: ["boolean"] }), expr ) }) describe("aggregation", function () { it("aggregates if required", function () { const field = { type: "field", table: "t1", column: "number" } compare(this.exprCleaner.cleanExpr(field, { aggrStatuses: ["aggregate"] }), { type: "op", op: "sum", table: "t1", exprs: [field] }) }) it("nulls if aggregate and should not be", function () { const field = { type: "field", table: "t1", column: "number" } assert(this.exprCleaner.cleanExpr(field, { aggrStatuses: ["individual"] }), "is individual") assert.isNull(this.exprCleaner.cleanExpr(field, { aggrStatuses: ["literal"] })) const aggr = { type: "op", table: "t1", op: "sum", exprs: [field] } assert.isNull(this.exprCleaner.cleanExpr(aggr), "is aggregate") assert.isNull(this.exprCleaner.cleanExpr(aggr, { aggrStatuses: ["literal"] })) assert(this.exprCleaner.cleanExpr(aggr, { aggrStatuses: ["aggregate"] }), "should allow aggregate") }) it("nulls inner expr if wrong aggregation status", function () { const field = { type: "field", table: "t1", column: "number" } const expr = { type: "op", table: "t1", op: "sum", exprs: [{ type: "op", op: "sum", exprs: [field] }] } compare(this.exprCleaner.cleanExpr(expr, { aggrStatuses: ["aggregate"] }), { type: "op", op: "sum", table: "t1", exprs: [null] }) }) it("passes types through aggregation", function () { const field = { type: "field", table: "t1", column: "number" } const expr = { type: "op", table: "t1", op: "sum", exprs: [field] } compare(this.exprCleaner.cleanExpr(expr, { types: ["number"], aggrStatuses: ["aggregate"] }), expr) compare(this.exprCleaner.cleanExpr(expr, { types: ["text"], aggrStatuses: ["aggregate"] }), null) }) it("allows no args for count", function () { const field = { type: "field", table: "t1", column: "number" } const expr = { type: "op", table: "t1", op: "count", exprs: [field] } compare(this.exprCleaner.cleanExpr(expr, { types: ["number"], aggrStatuses: ["aggregate"] }), { type: "op", table: "t1", op: "count", exprs: [] }) }) }) describe("fixing expression types", function () { it("creates boolean from enum", function () { const expr = { type: "field", table: "t1", column: "enum" } compare(this.exprCleaner.cleanExpr(expr, { types: ["boolean"] }), { type: "op", table: "t1", op: "= any", exprs: [expr, null] }) }) it("creates aggregate when needed", function () { const expr = { type: "field", table: "t1", column: "enum" } compare( this.exprCleaner.cleanExpr(expr, { types: ["number"], aggrStatuses: ["aggregate", "literal"] }), { type: "op", op: "count distinct", table: "t1", exprs: [expr] } ) }) it("creates boolean inside percent where from enum", function () { const expr = { type: "op", table: "t1", op: "percent where", exprs: [{ type: "field", table: "t1", column: "enum" }] } compare( this.exprCleaner.cleanExpr(expr, { types: ["number"], aggrStatuses: ["aggregate", "literal"] }), { type: "op", op: "percent where", table: "t1", exprs: [ { type: "op", table: "t1", op: "= any", exprs: [{ type: "field", table: "t1", column: "enum" }, null] }, null ] } ) }) }) describe("op", function () { it("preserves 'and' by cleaning child expressions with boolean type", function () { const expr = { type: "op", op: "and", table: "t1", exprs: [ { type: "field", table: "t1", column: "enum" }, { type: "field", table: "t1", column: "boolean" } ] } compare(this.exprCleaner.cleanExpr(expr), { type: "op", op: "and", table: "t1", exprs: [ // Booleanized { type: "op", table: "t1", op: "= any", exprs: [{ type: "field", table: "t1", column: "enum" }, null] }, // Untouched { type: "field", table: "t1", column: "boolean" } ] }) }) it("simplifies and", function () { let expr = { type: "op", op: "and", table: "t1", exprs: [{ type: "field", table: "t1", column: "boolean" }] } compare(this.exprCleaner.cleanExpr(expr), { type: "field", table: "t1", column: "boolean" }) expr = { type: "op", op: "and", table: "t1", exprs: [] } compare(this.exprCleaner.cleanExpr(expr), null) }) it("allows empty 'and' children", function () { const expr = { type: "op", op: "and", table: "t1", exprs: [{}, {}] } compare(this.exprCleaner.cleanExpr(expr), expr) }) it("allows empty '+' children", function () { const expr = { type: "op", op: "+", table: "t1", exprs: [{}, {}] } compare(this.exprCleaner.cleanExpr(expr), expr) }) it("nulls if wrong type", function () { const expr = { type: "op", op: "and", table: "t1", exprs: [{}, {}] } compare(this.exprCleaner.cleanExpr(expr, { types: ["number"] }), null) }) it("nulls if missing lhs of non-+/*/and/or expr", function () { let expr = { type: "op", op: "= any", table: "t1", exprs: [null, {}] } compare(this.exprCleaner.cleanExpr(expr), null) expr = { type: "op", op: "=", table: "t1", exprs: [null, {}] } compare(this.exprCleaner.cleanExpr(expr), null) expr = { type: "op", op: "=", table: "t1", exprs: [null, null] } compare(this.exprCleaner.cleanExpr(expr), null) }) it("allows math on aggregates", function () { const field: FieldExpr = { type: "field", table: "t1", column: "number" } let expr: Expr = { type: "op", table: "t1", op: "sum", exprs: [field] } expr = { type: "op", op: "+", table: "t1", exprs: [expr, expr] } compare(this.exprCleaner.cleanExpr(expr, { types: ["number"], aggrStatuses: ["aggregate"] }), expr) }) it("allows building math on aggregates", function () { const field: FieldExpr = { type: "field", table: "t1", column: "number" } let expr: Expr = { type: "op", table: "t1", op: "sum", exprs: [field] } expr = { type: "op", op: "+", table: "t1", exprs: [expr, null] } compare(this.exprCleaner.cleanExpr(expr, { types: ["number"], aggrStatuses: ["aggregate"] }), expr) }) it("does not allow enum = enumset", function () { const expr = { type: "op", op: "=", table: "t1", exprs: [ { type: "field", table: "t1", column: "enum" }, { type: "literal", valueType: "enumset", value: ["a"] } ] } compare(this.exprCleaner.cleanExpr(expr).exprs[1], null) }) it("defaults op if lhs changes", function () { const expr = { type: "op", op: "= any", table: "t1", exprs: [ { type: "field", table: "t1", column: "number" }, { type: "literal", valueType: "enumset", value: ["a"] } ] } compare(this.exprCleaner.cleanExpr(expr), { type: "op", op: "=", table: "t1", exprs: [{ type: "field", table: "t1", column: "number" }, null] }) }) it("removes extra exprs", function () { const expr = { type: "op", op: "=", table: "t1", exprs: [{ type: "field", table: "t1", column: "number" }, null, null] } compare(this.exprCleaner.cleanExpr(expr), { type: "op", op: "=", table: "t1", exprs: [{ type: "field", table: "t1", column: "number" }, null] }) }) it("adds missing exprs", function () { const expr = { type: "op", op: "=", table: "t1", exprs: [{ type: "field", table: "t1", column: "number" }] } compare(this.exprCleaner.cleanExpr(expr), { type: "op", op: "=", table: "t1", exprs: [{ type: "field", table: "t1", column: "number" }, null] }) }) it("allows null=wildcard unary expressions", function () { const expr = { type: "op", op: "is null", table: "t1", exprs: [{ type: "field", table: "t1", column: "number" }, null] } compare(this.exprCleaner.cleanExpr(expr), { type: "op", op: "is null", table: "t1", exprs: [{ type: "field", table: "t1", column: "number" }] }) }) it("removes invalid enums on rhs", function () { const expr = { type: "op", op: "= any", table: "t1", exprs: [ { type: "field", table: "t1", column: "enum" }, { type: "literal", valueType: "enumset", value: ["a", "x"] } ] } compare(this.exprCleaner.cleanExpr(expr), { type: "op", op: "= any", table: "t1", exprs: [ { type: "field", table: "t1", column: "enum" }, { type: "literal", valueType: "enumset", value: ["a"] } ] }) }) // x is gone it("removes invalid id table on rhs", function () { let expr = { type: "op", op: "=", table: "t1", exprs: [ { type: "id", table: "t1" }, { type: "literal", valueType: "id", idTable: "t1", value: "123" } ] } compare(this.exprCleaner.cleanExpr(expr), expr) expr = { type: "op", op: "=", table: "t1", exprs: [ { type: "id", table: "t1" }, { type: "literal", valueType: "id", idTable: "t2", value: "123" } ] } compare(this.exprCleaner.cleanExpr(expr), { type: "op", op: "=", table: "t1", exprs: [{ type: "id", table: "t1" }, null] }) }) it("allows empty lhs for prefix-type expressions", function () { const expr = { type: "op", op: "distance", table: "t1", exprs: [] } compare(this.exprCleaner.cleanExpr(expr), { type: "op", op: "distance", table: "t1", exprs: [null, null] }) }) it("allows null-type lhs for prefix-type expressions", function () { const expr = { type: "op", op: "distance", table: "t1", exprs: [{ type: "scalar", table: "t1", joins: ["1-2"], expr: null }, null] } compare(this.exprCleaner.cleanExpr(expr), expr) }) it("removes invalid lhs", function () { const expr = { type: "op", op: "=", table: "t1", exprs: [{ type: "field", table: "t1", column: "NONSUCH" }, null] } compare(this.exprCleaner.cleanExpr(expr), null) }) it("allows date/datetime comparison", function () { const expr = { type: "op", op: ">", table: "t1", exprs: [{ type: "field", table: "t1", column: "date" }, { type: "field", table: "t1", column: "datetime" }] } compare(this.exprCleaner.cleanExpr(expr), expr) }) it("removes mixed individual and aggregate", function () { const expr = { type: "op", op: "+", table: "t1", exprs: [{ type: "field", table: "t1", column: "number" }, { type: "op", table: "t1", op: "sum", exprs: [{ type: "field", table: "t1", column: "number" }] }] } const expected = { type: "op", op: "+", table: "t1", exprs: [{ type: "field", table: "t1", column: "number" }, null] } compare(this.exprCleaner.cleanExpr(expr, { aggrStatuses: ["aggregate", "individual"] }), expected) }) it("cleans mixed aggregate and individual", function () { const expr = { type: "op", op: "+", table: "t1", exprs: [{ type: "op", table: "t1", op: "sum", exprs: [{ type: "field", table: "t1", column: "number" }] }, { type: "field", table: "t1", column: "number" }] } const expected = { type: "op", op: "+", table: "t1", exprs: [{ type: "op", table: "t1", op: "sum", exprs: [{ type: "field", table: "t1", column: "number" }] }, { type: "op", table: "t1", op: "sum", exprs: [{ type: "field", table: "t1", column: "number" }] }] } compare(this.exprCleaner.cleanExpr(expr, { aggrStatuses: ["aggregate", "individual"] }), expected) }) it("allows mixed individual and literal", function () { const expr = { type: "op", op: "+", table: "t1", exprs: [{ type: "field", table: "t1", column: "number" }, { type: "literal", valueType: "number", value: 123 }] } compare(this.exprCleaner.cleanExpr(expr, { aggrStatuses: ["aggregate", "individual"] }), expr) }) }) describe("case", function () { it("cleans else", function () { const expr = { type: "case", table: "t1", cases: [ { when: { type: "literal", valueType: "boolean", value: true }, then: { type: "literal", valueType: "number", value: 123 } } ], else: { type: "literal", valueType: "text", value: "abc" } } compare(this.exprCleaner.cleanExpr(expr, { types: ["number"] }), { type: "case", table: "t1", cases: [ { when: { type: "literal", valueType: "boolean", value: true }, then: { type: "literal", valueType: "number", value: 123 } } ], else: null }) }) it("cleans whens as booleans", function () { const expr = { type: "case", table: "t1", cases: [ { when: { type: "literal", valueType: "number", value: 123 }, then: { type: "literal", valueType: "number", value: 123 } } ] } compare(this.exprCleaner.cleanExpr(expr, { types: ["number"] }), { type: "case", table: "t1", cases: [ { when: { type: "op", op: "=", exprs: [{ type: "literal", valueType: "number", value: 123 }, null] }, then: { type: "literal", valueType: "number", value: 123 } } ], else: null }) }) it("simplifies if no cases", function () { const expr = { type: "case", table: "t1", cases: [], else: { type: "literal", valueType: "text", value: "abc" } } compare(this.exprCleaner.cleanExpr(expr, { types: ["text"] }), { type: "literal", valueType: "text", value: "abc" }) }) it("cleans thens as specified type", function () { const expr = { type: "case", table: "t1", cases: [ { when: { type: "literal", valueType: "boolean", value: true }, then: { type: "literal", valueType: "number", value: 123 } } ], else: null } compare(this.exprCleaner.cleanExpr(expr, { types: ["text"] }), { type: "case", table: "t1", cases: [{ when: { type: "literal", valueType: "boolean", value: true }, then: null }], else: null }) }) it("cleans thens as specified enumValueIds", function () { const expr = { type: "case", table: "t1", cases: [ { when: { type: "literal", valueType: "boolean", value: true }, then: { type: "literal", valueType: "enum", value: "x" } } ], else: null } compare(this.exprCleaner.cleanExpr(expr, { types: ["enum"], enumValueIds: ["a"] }), { type: "case", table: "t1", cases: [{ when: { type: "literal", valueType: "boolean", value: true }, then: null }], else: null }) }) it("ensures all thens are of same type", function () { const expr = { type: "case", table: "t1", cases: [ { when: { type: "literal", valueType: "boolean", value: true }, then: { type: "literal", valueType: "text", value: "abc" } }, { when: { type: "literal", valueType: "boolean", value: false }, then: { type: "literal", valueType: "number", value: 123 } } ], else: { type: "literal", valueType: "number", value: 123 } } compare(this.exprCleaner.cleanExpr(expr, { types: ["number", "text"] }), { type: "case", table: "t1", cases: [ { when: { type: "literal", valueType: "boolean", value: true }, then: { type: "literal", valueType: "text", value: "abc" } }, { when: { type: "literal", valueType: "boolean", value: false }, then: null } ], else: null }) }) it("removes mixed individual and aggregate in then/else", function () { const expr = { type: "case", table: "t1", cases: [ { when: { type: "literal", valueType: "boolean", value: true }, then: { type: "field", table: "t1", column: "number" } // individual } ], else: { type: "op", op: "sum", table: "t1", exprs: [{ type: "field", table: "t1", column: "number" }] } // aggregate } compare(this.exprCleaner.cleanExpr(expr, { aggrStatuses: ["individual", "literal", "aggregate"] }), { type: "case", table: "t1", cases: [ { when: { type: "literal", valueType: "boolean", value: true }, then: { type: "field", table: "t1", column: "number" } } ], else: null // Should be nulled since then was individual }) }) it("cleans mixed aggregate and individual in then/else by auto-aggregating", function () { // When aggregate comes first, individual expressions are auto-aggregated (wrapped in sum) const expr = { type: "case", table: "t1", cases: [ { when: { type: "literal", valueType: "boolean", value: true }, then: { type: "op", op: "sum", table: "t1", exprs: [{ type: "field", table: "t1", column: "number" }] } // aggregate } ], else: { type: "field", table: "t1", column: "number" } // individual } compare(this.exprCleaner.cleanExpr(expr, { aggrStatuses: ["individual", "literal", "aggregate"] }), { type: "case", table: "t1", cases: [ { when: { type: "literal", valueType: "boolean", value: true }, then: { type: "op", op: "sum", table: "t1", exprs: [{ type: "field", table: "t1", column: "number" }] } } ], else: { type: "op", op: "sum", table: "t1", exprs: [{ type: "field", table: "t1", column: "number" }] } // Auto-aggregated to match }) }) it("allows mixed individual and literal in case", function () { const expr = { type: "case", table: "t1", cases: [ { when: { type: "literal", valueType: "boolean", value: true }, then: { type: "field", table: "t1", column: "number" } // individual } ], else: { type: "literal", valueType: "number", value: 123 } // literal } compare(this.exprCleaner.cleanExpr(expr, { aggrStatuses: ["individual", "literal", "aggregate"] }), expr) }) }) describe("score", function () { it("cleans input", function () { let expr = { type: "score", table: "t1", input: { type: "field", table: "t1", column: "text" }, scores: {} } this.clean(expr, { type: "score", table: "t1", input: null, scores: {} }) expr = { type: "score", table: "t1", input: { type: "field", table: "t1", column: "enum" }, scores: {} } this.clean(expr, expr) }) it("removes invalid scores keys", function () { const expr = { type: "score", table: "t1", input: { type: "field", table: "t1", column: "enum" }, scores: { a: 3, nonsuch: 4 } } this.clean(expr, { type: "score", table: "t1", input: { type: "field", table: "t1", column: "enum" }, scores: { a: 3 } }) }) it("cleans score values", function () { let expr = { type: "score", table: "t1", input: { type: "field", table: "t1", column: "enum" }, scores: { a: { type: "field", table: "t1", column: "number" } } } // Untouched since was number this.clean(expr, expr) expr = { type: "score", table: "t1", input: { type: "field", table: "t1", column: "enum" }, scores: { a: { type: "field", table: "t1", column: "text" } } } this.clean(expr, { type: "score", table: "t1", input: { type: "field", table: "t1", column: "enum" }, scores: {} }) }) it("removes all scores if no input", function () { const expr = { type: "score", table: "t1", input: null, scores: { a: 3, nonsuch: 4 } } this.clean(expr, { type: "score", table: "t1", input: null, scores: {} }) }) }) describe("build enumset", function () { it("cleans values", function () { let expr = { type: "build enumset", table: "t1", values: { a: { type: "field", table: "t1", column: "boolean" } } } this.clean(expr, expr) expr = { type: "build enumset", table: "t1", values: { a: { type: "field", table: "t1", column: "xyz" } } } this.clean(expr, { type: "build enumset", table: "t1", values: {} }) }) it("removes invalid value keys", function () { const expr = { type: "build enumset", table: "t1", values: { a: { type: "literal", valueType: "boolean", value: true }, b: { type: "literal", valueType: "boolean", value: false } } } compare(this.exprCleaner.cleanExpr(expr, { types: ["enumset"], enumValueIds: ["a"] }), { type: "build enumset", table: "t1", values: { a: { type: "literal", valueType: "boolean", value: true } } }) }) }) describe("literal", function () { it("cleans invalid literal enum valueIds", function () { const expr = { type: "literal", valueType: "enum", value: "a" } compare(this.exprCleaner.cleanExpr(expr, { enumValueIds: ["a", "b"] }), expr) compare(this.exprCleaner.cleanExpr(expr, { enumValueIds: ["b"] }), null) compare(this.exprCleaner.cleanExpr(expr, { enumValueIds: ["a", "b", "c"] }), expr) }) it("cleans invalid field enum valueIds", function () { const expr = { type: "field", table: "t1", column: "enum" } compare(this.exprCleaner.cleanExpr(expr, { enumValueIds: ["a", "b"] }), expr) compare(this.exprCleaner.cleanExpr(expr, { enumValueIds: ["b"] }), null) }) }) describe("scalar", function () { it("leaves valid one alone", function () { const fieldExpr = { type: "op", table: "t2", op: "sum", exprs: [{ type: "field", table: "t2", column: "number" }] } const scalarExpr = { type: "scalar", table: "t1", joins: ["1-2"], expr: fieldExpr } compare(scalarExpr, this.exprCleaner.cleanExpr(scalarExpr)) }) it("moves aggr to expr", function () { const fieldExpr = { type: "field", table: "t2", column: "number" } let scalarExpr = { type: "scalar", table: "t1", joins: ["1-2"], expr: fieldExpr, aggr: "sum" } scalarExpr = this.exprCleaner.cleanExpr(scalarExpr) compare(scalarExpr, { type: "scalar", table: "t1", joins: ["1-2"], expr: { type: "op", op: "sum", table: "t2", exprs: [fieldExpr] } }) }) it("defaults aggr if needed", function () { const fieldExpr = { type: "field", table: "t2", column: "text" } let scalarExpr = { type: "scalar", table: "t1", joins: ["1-2"], expr: fieldExpr } scalarExpr = this.exprCleaner.cleanExpr(scalarExpr) compare(scalarExpr, { type: "scalar", table: "t1", joins: ["1-2"], expr: { type: "op", op: "last", table: "t2", exprs: [fieldExpr] } }) }) it("strips where if wrong table", function () { const fieldExpr: OpExpr = { type: "op", op: "sum", table: "t2", exprs: [{ type: "field", table: "t2", column: "number" }] } const whereExpr = { type: "logical", table: "t1" } let scalarExpr: ScalarExpr = { type: "scalar", table: "t1", joins: ["1-2"], expr: fieldExpr } scalarExpr = this.exprCleaner.cleanExpr(scalarExpr) assert(scalarExpr.expr, "Should keep expr") assert(!scalarExpr.where, "Should remove where") }) it("strips if invalid join", function () { const fieldExpr = { type: "op", op: "sum", exprs: [{ type: "field", table: "t2", column: "number" }] } let scalarExpr = { type: "scalar", table: "t1", joins: ["xyz"], expr: fieldExpr } scalarExpr = this.exprCleaner.cleanExpr(scalarExpr) assert(!scalarExpr) }) it("simplifies if no joins", function () { const fieldExpr = { type: "field", table: "t1", column: "number" } let scalarExpr = { type: "scalar", table: "t1", joins: [], expr: fieldExpr } scalarExpr = this.exprCleaner.cleanExpr(scalarExpr) compare(fieldExpr, scalarExpr) }) it("simplifies if id and single join", function () { const fieldExpr = { type: "id", table: "t2" } const scalarExpr = { type: "scalar", table: "t1", joins: ["1-2"], expr: fieldExpr } const expr = this.exprCleaner.cleanExpr(scalarExpr) compare(expr, { type: "field", table: "t1", column: "1-2" }) }) }) }) describe("subquery", function () { it("leaves valid one alone", function () { const expr: SubqueryExpr = { type: "subquery", table: "t1", from: "t2", select: { type: "field", table: "t2", column: "number" }, outerRefs: [{ id: "var1", expr: { type: "field", table: "t1", column: "number" } }], orderBys: [{ expr: { type: "field", table: "t2", column: "number" }, dir: "desc" }], where: { type: "op", op: ">", table: "t2", exprs: [ { type: "field", table: "t2", column: "number" }, { type: "variable", variableId: "var1" } ] }, } compare(expr, this.exprCleaner.cleanExpr(expr)) }) it("removes invalid expr", function () { const expr = { type: "subquery", table: "t1", from: "nonsuch", expr: { type: "field", table: "t1", column: "nonsuch" }, orderBys: [], where: null, outerRefs: [], } compare(null, this.exprCleaner.cleanExpr(expr)) }) it("cleans invalid inner expr", function () { const expr = { type: "subquery", table: "t1", from: "t2", select: { type: "field", table: "t2", column: "nonsuch" }, where: null, orderBys: [], outerRefs: [], } compare({ type: "subquery", table: "t1", from: "t2", select: null, where: null, orderBys: [], outerRefs: [], }, this.exprCleaner.cleanExpr(expr)) }) }) // describe "spatial join", -> // it "leaves valid one alone", -> // expr = { // type: "spatial join" // valueExpr: { type: "op", op: "count", table: "t2", exprs: [] } // table: "t1" // toTable: "t2" // fromGeometryExpr: { type: "field", table: "t1", column: "geometry" } // toGeometryExpr: { type: "field", table: "t2", column: "geometry" } // radiusExpr: { type: "literal", valueType: "number", value: 10 } // filterExpr: { type: "field", table: "t2", column: "boolean" } // } // compare(expr, @exprCleaner.cleanExpr(expr)) // it "removes invalid filters", -> // expr = { // type: "spatial join" // valueExpr: { type: "op", op: "count", table: "t2", exprs: [] } // table: "t1" // toTable: "t2" // fromGeometryExpr: { type: "field", table: "t1", column: "geometry" } // toGeometryExpr: { type: "field", table: "t2", column: "geometry" } // radiusExpr: { type: "literal", valueType: "number", value: 10 } // filterExpr: { type: "field", table: "t1", column: "boolean" } // } // compare(_.extend({}, expr, { filterExpr: null }), @exprCleaner.cleanExpr(expr)) // Version 1 expression should be upgraded to version 2 describe("upgrade", function () { it("count becomes id", function () { this.clean( { type: "scalar", table: "t1", aggr: "count", joins: ["1-2"], expr: { type: "count", table: "t2" } }, { type: "scalar", table: "t1", joins: ["1-2"], expr: { type: "op", op: "count", table: "t2", exprs: [] } } ) }) it("scalar count becomes id", function () { this.clean( { type: "scalar", table: "t1", expr: { type: "count", table: "t1" }, joins: [] }, { type: "id", table: "t1" } ) }) it("scalar is simplified", function () { this.clean( { type: "scalar", table: "t1", joins: [], expr: { type: "field", table: "t1", column: "number" } }, { type: "field", table: "t1", column: "number" } ) }) it("logical becomes op", function () { this.clean( { type: "logical", op: "and", table: "t1", exprs: [ { type: "field", table: "t1", column: "boolean" }, { type: "field", table: "t1", column: "boolean" } ] }, { type: "op", op: "and", table: "t1", exprs: [ { type: "field", table: "t1", column: "boolean" }, { type: "field", table: "t1", column: "boolean" } ] } ) }) it("comparison becomes op", function () { this.clean( { type: "comparison", table: "t1", lhs: { type: "field", table: "t1", column: "text" }, op: "~*", rhs: { type: "literal", valueType: "text", value: "x" } }, { type: "op", op: "~*", table: "t1", exprs: [ { type: "field", table: "t1", column: "text" }, { type: "literal", valueType: "text", value: "x" } ] } ) }) it("= true is simplified", function () { this.clean( { type: "comparison", table: "t1", lhs: { type: "field", table: "t1", column: "boolean" }, op: "= true" }, { type: "field", table: "t1", column: "boolean" } ) }) it("= false becomes 'not'", function () { this.clean( { type: "comparison", table: "t1", lhs: { type: "field", table: "t1", column: "boolean" }, op: "= false" }, { type: "op", op: "not", table: "t1", exprs: [{ type: "field", table: "t1", column: "boolean" }] } ) }) it("enum[] becomes enumset", function () { this.clean( { type: "literal", valueType: "enum[]", value: ["a", "b"] }, { type: "literal", valueType: "enumset", value: ["a", "b"] } ) }) it("between becomes 3 parameters date", function () { this.clean( { type: "comparison", table: "t1", lhs: { type: "field", table: "t1", column: "date" }, op: "between", rhs: { type: "literal", valueType: "daterange", value: ["2014-01-01", "2014-12-31"] } }, { type: "op", op: "between", table: "t1", exprs: [ { type: "field", table: "t1", column: "date" }, { type: "literal", valueType: "date", value: "2014-01-01" }, { type: "literal", valueType: "date", value: "2014-12-31" } ] } ) }) it("between becomes 3 parameters datetime", function () { this.clean( { type: "comparison", table: "t1", lhs: { type: "field", table: "t1", column: "datetime" }, op: "between", rhs: { type: "literal", valueType: "datetimerange", value: ["2014-01-01", "2014-12-31"] } }, { type: "op", op: "between", table: "t1", exprs: [ { type: "field", table: "t1", column: "datetime" }, { type: "literal", valueType: "datetime", value: "2014-01-01" }, { type: "literal", valueType: "datetime", value: "2014-12-31" } ] } ) }) it("between becomes 3 parameters date if were datetime on date", function () { this.clean( { type: "comparison", table: "t1", lhs: { type: "field", table: "t1", column: "date" }, op: "between", rhs: { type: "literal", valueType: "datetimerange", value: ["2014-01-01T01:02:04", "2014-12-31T01:02:04"] } }, { type: "op", op: "between", table: "t1", exprs: [ { type: "field", table: "t1", column: "date" }, { type: "literal", valueType: "date", value: "2014-01-01" }, { type: "literal", valueType: "date", value: "2014-12-31" } ] } ) }) it("upgrades legacy entity join references", function () { let schema = this.schema.addTable({ id: "entities.wwmc_visit", contents: [{ id: "site", type: "join", join: { type: "n-1", toTable: "entities.surface_water" } }] }) schema = schema.addTable({ id: "entities.surface_water", contents: [{ id: "location", type: "geometry" }] }) const exprCleaner = new ExprCleaner(schema) const clean = (expr: any, expected: any, options?: any) => { compare(exprCleaner.cleanExpr(expr, options), expected) } clean( { type: "scalar", expr: { type: "field", table: "entities.surface_water", column: "location" }, joins: ["entities.wwmc_visit.site"], table: "entities.wwmc_visit" }, { type: "scalar", expr: { type: "field", table: "entities.surface_water", column: "location" }, joins: ["site"], table: "entities.wwmc_visit" } ) }) it("upgrades complex expression with legacy literals", function () { const expr1 = { type: "comparison", table: "t1", op: "=", lhs: { type: "field", table: "t1", column: "number" }, rhs: { type: "literal", valueType: "integer", value: 4 } } const expr2 = { type: "comparison", table: "t1", op: "=", lhs: { type: "field", table: "t1", column: "number" }, rhs: { type: "literal", valueType: "integer", value: 5 } } const value = { type: "logical", table: "t1", op: "and", exprs: [expr1, expr2] } this.clean(value, { type: "op", op: "and", table: "t1", exprs: [ { type: "op", table: "t1", op: "=", exprs: [ { type: "field", table: "t1", column: "number" }, { type: "literal", valueType: "number", value: 4 } ] }, { type: "op", table: "t1", op: "=", exprs: [ { type: "field", table: "t1", column: "number" }, { type: "literal", valueType: "number", value: 5 } ] } ] }) }) }) })