Huge News!Announcing our $40M Series B led by Abstract Ventures.Learn More →
Socket
Sign inDemoInstall
Socket
Sign inDemoInstall

@uwdata/mosaic-sql

Package Overview
Dependencies
Maintainers
1
Versions
18
Alerts
File Explorer

Advanced tools

License
Socket logo

Install Socket

Detect and block malicious and high-risk dependencies

Install

@uwdata/mosaic-sql - npm Package Compare versions

Comparing version 0.1.0 to 0.2.0

src/aggregates.js
src/cast.js
src/desc.js
src/functions.js
src/load/create.js
src/load/csv.js
src/load/json.js
src/load/parameters.js
src/load/parquet.js
src/load/sql-from.js
src/operators.js
src/repeat.js
src/windows.js

839

dist/mosaic-sql.js
// src/ref.js
var Ref = class {
/**
* Create a new Ref instance.
* @param {string|Ref|null} table The table name.
* @param {string|null} column The column name.
*/
constructor(table, column2) {

@@ -9,12 +14,20 @@ if (table)

}
/**
* Get the list of referenced columns. Either a single element array
* if column is non-null, otherwise an empty array.
*/
get columns() {
return this.column ? [this.column] : [];
}
/**
* Generate a SQL string for this reference.
* @returns {string} The SQL string.
*/
toString() {
const { table, column: column2 } = this;
if (column2) {
const col = column2 === "*" ? column2 : `"${column2}"`;
return (table ? `"${table}".` : "") + col;
const col = column2.startsWith("*") ? column2 : `"${column2}"`;
return `${table ? `"${table}".` : ""}${col}`;
} else {
return `"${table}"`;
return table ? `"${table}"` : "NULL";
}

@@ -36,3 +49,7 @@ }

function column(table, column2) {
return arguments.length === 1 ? new Ref(null, table) : new Ref(table, column2);
if (arguments.length === 1) {
column2 = table;
table = null;
}
return new Ref(table, column2);
}

@@ -53,2 +70,4 @@ function all(table) {

return `'${value}'`;
case "number":
return Number.isFinite(value) ? String(value) : "NULL";
default:

@@ -58,3 +77,9 @@ if (value == null) {

} else if (value instanceof Date) {
return `MAKE_DATE(${value.getUTCFullYear()}, ${value.getUTCMonth() + 1}, ${value.getUTCDate()})`;
const ts = +value;
if (Number.isNaN(ts))
return "NULL";
const y = value.getUTCFullYear();
const m = value.getUTCMonth();
const d = value.getUTCDate();
return ts === Date.UTC(y, m, d) ? `MAKE_DATE(${y}, ${m + 1}, ${d})` : `EPOCH_MS(${ts})`;
} else if (value instanceof RegExp) {

@@ -69,31 +94,82 @@ return `'${value.source}'`;

// src/expression.js
var isParamLike = (e) => typeof e?.addEventListener === "function";
function isExpression(e) {
return e instanceof SQLExpression;
var isParamLike = (value) => typeof value?.addEventListener === "function";
function isSQLExpression(value) {
return value instanceof SQLExpression;
}
var SQLExpression = class {
constructor(sql2, columns, label) {
this.expr = sql2;
this.label = label;
this.columns = columns || [];
/**
* Create a new SQL expression instance.
* @param {(string|SQLExpression|Ref)[]} parts The parts of the expression.
* @param {string[]} [columns=[]] The column dependencies
* @param {object} [props] Additional properties for this expression.
*/
constructor(parts, columns, props) {
this._expr = Array.isArray(parts) ? parts : [parts];
this._deps = columns || [];
this.annotate(props);
const params = this._expr.filter((part) => isParamLike(part));
if (params.length > 0) {
this._params = Array.from(new Set(params));
this._params.forEach((param) => {
param.addEventListener("value", () => update(this, this.map?.get("value")));
});
} else {
this.addEventListener = void 0;
}
}
toString() {
return `${this.expr}`;
/**
* A reference to this expression.
* Provides compatibility with param-like objects.
*/
get value() {
return this;
}
};
var ParameterizedSQLExpression = class extends SQLExpression {
constructor(parts, columns, label) {
const paramSet = /* @__PURE__ */ new Set();
for (const part of parts) {
if (isParamLike(part))
paramSet.add(part);
/**
* The column dependencies of this expression.
* @returns {string[]} The columns dependencies.
*/
get columns() {
const { _params, _deps } = this;
if (_params) {
const pset = new Set(_params.flatMap((p) => {
const cols = p.value?.columns;
return Array.isArray(cols) ? cols : [];
}));
if (pset.size) {
const set = new Set(_deps);
pset.forEach((col) => set.add(col));
return Array.from(set);
}
}
paramSet.forEach((param) => {
param.addEventListener("value", () => this.update());
});
super(parts, columns, label);
return _deps;
}
/**
* The first column dependency in this expression, or undefined if none.
* @returns {string} The first column dependency.
*/
get column() {
return this._deps.length ? this._deps[0] : this.columns[0];
}
/**
* Annotate this expression instance with additional properties.
* @param {object[]} [props] One or more objects with properties to add.
* @returns {this} This SQL expression.
*/
annotate(...props) {
return Object.assign(this, ...props);
}
/**
* Generate a SQL code string corresponding to this expression.
* @returns {string} A SQL code string.
*/
toString() {
return this.expr.map((p) => isParamLike(p) ? literalToSQL(p.value) : p).join("");
return this._expr.map((p) => isParamLike(p) && !isSQLExpression(p) ? literalToSQL(p.value) : p).join("");
}
/**
* Add an event listener callback for the provided event type.
* @param {string} type The event type to listen for (for example, "value").
* @param {(a: SQLExpression) => Promise?} callback The callback function to
* invoke upon updates. A callback may optionally return a Promise that
* upstream listeners may await before proceeding.
*/
addEventListener(type, callback) {

@@ -104,26 +180,11 @@ const map = this.map || (this.map = /* @__PURE__ */ new Map());

}
update() {
this.map?.get("value")?.forEach((callback) => callback(this));
};
function update(expr, callbacks) {
if (callbacks?.size) {
return Promise.allSettled(Array.from(callbacks, (fn) => fn(expr)));
}
};
function exprParams(parts, columns, label) {
return new ParameterizedSQLExpression(parts, columns, label);
}
function expr(sql2, columns, label) {
return new SQLExpression(sql2, columns, label);
}
function desc(e) {
return Object.assign(
expr(`${asColumn(e)} DESC NULLS LAST`, e?.columns, e?.label),
{ desc: true }
);
}
function transform(func2, label) {
return (value) => expr(func2(value), asColumn(value).columns, label);
}
// src/sql-tag.js
function sql(strings, ...exprs) {
function parseSQL(strings, exprs) {
const spans = [strings[0]];
const colset = /* @__PURE__ */ new Set();
const cols = /* @__PURE__ */ new Set();
const n = exprs.length;

@@ -135,6 +196,6 @@ for (let i = 0, k = 0; i < n; ) {

} else {
if (Array.isArray(e.columns)) {
e.columns.forEach((col) => colset.add(col));
if (Array.isArray(e?.columns)) {
e.columns.forEach((col) => cols.add(col));
}
spans[k] += String(e);
spans[k] += typeof e === "string" ? e : literalToSQL(e);
}

@@ -148,248 +209,327 @@ const s = strings[++i];

}
const columns = Array.from(colset);
return spans.length > 1 ? exprParams(spans, columns) : expr(spans[0], columns);
return { spans, cols: Array.from(cols) };
}
function sql(strings, ...exprs) {
const { spans, cols } = parseSQL(strings, exprs);
return new SQLExpression(spans, cols);
}
// src/desc.js
function desc(expr) {
const e = asColumn(expr);
return sql`${e} DESC NULLS LAST`.annotate({ label: e?.label, desc: true });
}
// src/literal.js
var Literal = class {
constructor(value) {
this.value = value;
}
toString() {
return literalToSQL(this.value);
}
};
var literal = (value) => new Literal(value);
var literal = (value) => ({
value,
toString: () => literalToSQL(value)
});
// src/compare.js
function extractColumns(...args) {
return args.flat().flatMap((arg) => arg?.columns || []);
// src/operators.js
function visit(callback) {
callback(this.op, this);
this.children?.forEach((v) => v.visit(callback));
}
var Compare1 = class {
constructor(op, a) {
this.op = op;
this.a = asColumn(a);
}
get columns() {
return extractColumns(this.a);
}
visit(callback) {
callback(this.op, this);
}
toString() {
const { op, a } = this;
return `(${toSQL(a)} ${op})`;
}
};
function compare1(op) {
return (a) => new Compare1(op, a);
function logical(op, clauses) {
const children = clauses.filter((x) => x != null).map(asColumn);
const strings = children.map((c, i) => i ? ` ${op} ` : "");
if (clauses.length)
strings.push("");
return sql(strings, ...children).annotate({ op, children, visit });
}
var not = compare1("NOT");
var isNull = compare1("IS NULL");
var isNotNull = compare1("IS NOT NULL");
var Compare2 = class {
constructor(op, a, b) {
this.op = op;
this.a = asColumn(a);
this.b = asColumn(b);
var and = (...clauses) => logical("AND", clauses.flat());
var or = (...clauses) => logical("OR", clauses.flat());
var unaryOp = (op) => (a) => sql`(${op} ${asColumn(a)})`.annotate({ op, a, visit });
var not = unaryOp("NOT");
var unaryPostOp = (op) => (a) => sql`(${asColumn(a)} ${op})`.annotate({ op, a, visit });
var isNull = unaryPostOp("IS NULL");
var isNotNull = unaryPostOp("IS NOT NULL");
var binaryOp = (op) => (a, b) => sql`(${asColumn(a)} ${op} ${asColumn(b)})`.annotate({ op, a, b, visit });
var eq = binaryOp("=");
var neq = binaryOp("<>");
var lt = binaryOp("<");
var gt = binaryOp(">");
var lte = binaryOp("<=");
var gte = binaryOp(">=");
var isDistinct = binaryOp("IS DISTINCT FROM");
var isNotDistinct = binaryOp("IS NOT DISTINCT FROM");
function rangeOp(op, a, range, exclusive) {
a = asColumn(a);
const prefix2 = op.startsWith("NOT ") ? "NOT " : "";
const expr = !range ? sql`` : exclusive ? sql`${prefix2}(${range[0]} <= ${a} AND ${a} < ${range[1]})` : sql`(${a} ${op} ${range[0]} AND ${range[1]})`;
return expr.annotate({ op, visit, field: a, range });
}
var isBetween = (a, range, exclusive) => rangeOp("BETWEEN", a, range, exclusive);
var isNotBetween = (a, range, exclusive) => rangeOp("NOT BETWEEN", a, range, exclusive);
// src/repeat.js
function repeat(length2, str) {
return Array.from({ length: length2 }, () => str);
}
// src/functions.js
function functionCall(op, type) {
return (...values) => {
const args = values.map(asColumn);
const cast2 = type ? `::${type}` : "";
const expr = args.length ? sql([`${op}(`, ...repeat(args.length - 1, ", "), `)${cast2}`], ...args) : sql`${op}()${cast2}`;
return expr.annotate({ func: op, args });
};
}
var regexp_matches = functionCall("REGEXP_MATCHES");
var contains = functionCall("CONTAINS");
var prefix = functionCall("PREFIX");
var suffix = functionCall("SUFFIX");
var lower = functionCall("LOWER");
var upper = functionCall("UPPER");
var length = functionCall("LENGTH");
var isNaN = functionCall("ISNAN");
var isFinite = functionCall("ISFINITE");
var isInfinite = functionCall("ISINF");
// src/windows.js
var WindowFunction = class extends SQLExpression {
constructor(op, func, type, name, group = "", order = "", frame = "") {
let expr;
const noWindowParams = !(group || order || frame);
if (name && noWindowParams) {
expr = name ? sql`${func} OVER "${name}"` : sql`${func} OVER ()`;
} else {
const s1 = group && order ? " " : "";
const s2 = (group || order) && frame ? " " : "";
expr = sql`${func} OVER (${name ? `"${name}" ` : ""}${group}${s1}${order}${s2}${frame})`;
}
if (type) {
expr = sql`(${expr})::${type}`;
}
const { _expr, _deps } = expr;
super(_expr, _deps, { window: op, func, type, name, group, order, frame });
}
get columns() {
return extractColumns(this.a, this.b);
get basis() {
return this.column;
}
visit(callback) {
callback(this.op, this);
get label() {
const { func } = this;
return func.label ?? func.toString();
}
toString() {
const { op, a, b } = this;
return `(${toSQL(a)} ${op} ${toSQL(b)})`;
over(name) {
const { window: op, func, type, group, order, frame } = this;
return new WindowFunction(op, func, type, name, group, order, frame);
}
};
function compare2(op) {
return (a, b) => new Compare2(op, a, b);
}
var eq = compare2("=");
var neq = compare2("<>");
var lt = compare2("<");
var gt = compare2(">");
var lte = compare2("<=");
var gte = compare2(">=");
var isDistinct = compare2("IS DISTINCT FROM");
var isNotDistinct = compare2("IS NOT DISTINCT FROM");
var Range = class {
constructor(op, expr2, value) {
this.op = op;
this.expr = asColumn(expr2);
this.value = value?.map(asColumn);
partitionby(...expr) {
const exprs = expr.flat().filter((x) => x).map(asColumn);
const group = sql(
["PARTITION BY ", repeat(exprs.length - 1, ", "), ""],
...exprs
);
const { window: op, func, type, name, order, frame } = this;
return new WindowFunction(op, func, type, name, group, order, frame);
}
get columns() {
return extractColumns(this.expr, this.value);
orderby(...expr) {
const exprs = expr.flat().filter((x) => x).map(asColumn);
const order = sql(
["ORDER BY ", repeat(exprs.length - 1, ", "), ""],
...exprs
);
const { window: op, func, type, name, group, frame } = this;
return new WindowFunction(op, func, type, name, group, order, frame);
}
visit(callback) {
callback(this.op, this);
rows(expr) {
const frame = windowFrame("ROWS", expr);
const { window: op, func, type, name, group, order } = this;
return new WindowFunction(op, func, type, name, group, order, frame);
}
toString() {
const { op, expr: expr2, value } = this;
if (!value)
return "";
const [a, b] = value;
return `(${toSQL(expr2)} ${op} ${toSQL(a)} AND ${toSQL(b)})`;
range(expr) {
const frame = windowFrame("RANGE", expr);
const { window: op, func, type, name, group, order } = this;
return new WindowFunction(op, func, type, name, group, order, frame);
}
};
function range(op) {
return (a, range2) => new Range(op, a, range2);
}
var isBetween = range("BETWEEN");
var isNotBetween = range("NOT BETWEEN");
var CompareN = class {
constructor(op, value) {
this.op = op;
this.value = value.map(asColumn);
function windowFrame(type, frame) {
if (isParamLike(frame)) {
const expr = sql`${frame}`;
expr.toString = () => `${type} ${frameToSQL(frame.value)}`;
return expr;
}
get columns() {
return extractColumns(this.value);
}
visit(callback) {
callback(this.op, this);
this.value?.forEach((v) => v.visit(callback));
}
toString() {
const { op, value } = this;
return !value || value.length === 0 ? "" : value.length === 1 ? toSQL(value[0]) : `(${value.map(toSQL).filter((x) => x).join(` ${op} `)})`;
}
};
function and(...clauses) {
return new CompareN("AND", clauses.flat());
return `${type} ${frameToSQL(frame)}`;
}
function or(...clauses) {
return new CompareN("OR", clauses.flat());
function frameToSQL(frame) {
const [prev, next] = frame;
const a = prev === 0 ? "CURRENT ROW" : Number.isFinite(prev) ? `${Math.abs(prev)} PRECEDING` : "UNBOUNDED PRECEDING";
const b = next === 0 ? "CURRENT ROW" : Number.isFinite(next) ? `${Math.abs(next)} FOLLOWING` : "UNBOUNDED FOLLOWING";
return `BETWEEN ${a} AND ${b}`;
}
function winf(op, type) {
return (...values) => {
const func = functionCall(op)(...values);
return new WindowFunction(op, func, type);
};
}
var row_number = winf("ROW_NUMBER", "INTEGER");
var rank = winf("RANK", "INTEGER");
var dense_rank = winf("DENSE_RANK", "INTEGER");
var percent_rank = winf("PERCENT_RANK");
var cume_dist = winf("CUME_DIST");
var ntile = winf("NTILE");
var lag = winf("LAG");
var lead = winf("LEAD");
var first_value = winf("FIRST_VALUE");
var last_value = winf("LAST_VALUE");
var nth_value = winf("NTH_VALUE");
// src/function-call.js
var FunctionCall = class {
constructor(func2, args) {
this.func = func2;
this.args = (args || []).map(asColumn);
// src/aggregates.js
function agg(strings, ...exprs) {
return sql(strings, ...exprs).annotate({ aggregate: true });
}
var AggregateFunction = class extends SQLExpression {
constructor(op, args, type, isDistinct2, filter) {
args = (args || []).map(asColumn);
const { strings, exprs } = aggExpr(op, args, type, isDistinct2, filter);
const { spans, cols } = parseSQL(strings, exprs);
super(spans, cols, { aggregate: op, args, type, isDistinct: isDistinct2, filter });
}
get column() {
return this.columns[0];
get basis() {
return this.column;
}
get columns() {
return this.args.flatMap((a) => a.columns || []);
get label() {
const { aggregate: op, args, isDistinct: isDistinct2 } = this;
const dist = isDistinct2 ? "DISTINCT" + (args.length ? " " : "") : "";
const tail = args.length ? `(${dist}${args.map(unquoted).join(", ")})` : "";
return `${op.toLowerCase()}${tail}`;
}
toString() {
const { func: func2, args } = this;
return `${func2}(${args.map(toSQL).join(", ")})`;
distinct() {
const { aggregate: op, args, type, filter } = this;
return new AggregateFunction(op, args, type, true, filter);
}
};
function func(op) {
return (...args) => new FunctionCall(op, args);
}
var regexp_matches = func("regexp_matches");
var contains = func("contains");
var prefix = func("prefix");
var suffix = func("suffix");
var lower = func("lower");
var upper = func("upper");
var length = func("length");
var isNaN = func("isnan");
var isFinite = func("isfinite");
var isInfinite = func("isinf");
// src/aggregate.js
var Aggregate = class {
constructor(op, args) {
this.aggregate = op;
this.args = (args || []).map(asColumn);
where(filter) {
const { aggregate: op, args, type, isDistinct: isDistinct2 } = this;
return new AggregateFunction(op, args, type, isDistinct2, filter);
}
get label() {
return this.aggregate.toLowerCase() + (this.args.length ? ` ${this.columns.join(", ")}` : "");
window() {
const { aggregate: op, args, type, isDistinct: isDistinct2 } = this;
const func = new AggregateFunction(op, args, null, isDistinct2);
return new WindowFunction(op, func, type);
}
get column() {
return this.columns[0];
partitionby(...expr) {
return this.window().partitionby(...expr);
}
get columns() {
return this.args.flatMap((a) => a.columns || []);
orderby(...expr) {
return this.window().orderby(...expr);
}
distinct() {
this.isDistinct = true;
return this;
rows(prev, next) {
return this.window().rows(prev, next);
}
where(expr2) {
this.filter = expr2;
return this;
range(prev, next) {
return this.window().range(prev, next);
}
toString() {
const { aggregate, args, isDistinct: isDistinct2, filter } = this;
const arg = args.length === 0 ? "*" : args.map(toSQL).join(", ");
const distinct = isDistinct2 ? "DISTINCT " : "";
const where = filter ? ` FILTER (WHERE ${toSQL(filter)})` : "";
const cast = aggregate === "COUNT" ? "::INTEGER" : "";
return where && cast ? `(${aggregate}(${distinct}${arg})${where})${cast}` : `${aggregate}(${distinct}${arg})${where}${cast}`;
};
function aggExpr(op, args, type, isDistinct2, filter) {
const close = `)${type ? `::${type}` : ""}`;
let strings = [`${op}(${isDistinct2 ? "DISTINCT " : ""}`];
let exprs = [];
if (args.length) {
strings = strings.concat([
...repeat(args.length - 1, ", "),
`${close}${filter ? " FILTER (WHERE " : ""}`,
...filter ? [")"] : []
]);
exprs = [...args, ...filter ? [filter] : []];
} else {
strings[0] += "*" + close;
}
};
function agg(op) {
return (...args) => new Aggregate(op, args);
return { exprs, strings };
}
var count = agg("COUNT");
var avg = agg("AVG");
var mean = agg("AVG");
var mad = agg("MAD");
var max = agg("MAX");
var min = agg("MIN");
var sum = agg("SUM");
var product = agg("PRODUCT");
var median = agg("MEDIAN");
var quantile = agg("QUANTILE");
var mode = agg("MODE");
var variance = agg("VARIANCE");
var stddev = agg("STDDEV");
var skewness = agg("SKEWNESS");
var kurtosis = agg("KURTOSIS");
var entropy = agg("ENTROPY");
var varPop = agg("VAR_POP");
var stddevPop = agg("STDDEV_POP");
var corr = agg("CORR");
var covarPop = agg("COVAR_POP");
var regrIntercept = agg("REGR_INTERCEPT");
var regrSlope = agg("REGR_SLOPE");
var regrCount = agg("REGR_COUNT");
var regrR2 = agg("REGR_R2");
var regrSYY = agg("REGR_SYY");
var regrSXX = agg("REGR_SXX");
var regrSXY = agg("REGR_SXY");
var regrAvgX = agg("REGR_AVGX");
var regrAvgY = agg("REGR_AVGY");
var first = agg("FIRST");
var last = agg("LAST");
var argmin = agg("ARG_MIN");
var argmax = agg("ARG_MAX");
var stringAgg = agg("STRING_AGG");
var arrayAgg = agg("ARRAY_AGG");
function unquoted(value) {
const s = literalToSQL(value);
return s && s.startsWith('"') && s.endsWith('"') ? s.slice(1, -1) : s;
}
function aggf(op, type) {
return (...args) => new AggregateFunction(op, args, type);
}
var count = aggf("COUNT", "INTEGER");
var avg = aggf("AVG");
var mean = aggf("AVG");
var mad = aggf("MAD");
var max = aggf("MAX");
var min = aggf("MIN");
var sum = aggf("SUM", "DOUBLE");
var product = aggf("PRODUCT");
var median = aggf("MEDIAN");
var quantile = aggf("QUANTILE");
var mode = aggf("MODE");
var variance = aggf("VARIANCE");
var stddev = aggf("STDDEV");
var skewness = aggf("SKEWNESS");
var kurtosis = aggf("KURTOSIS");
var entropy = aggf("ENTROPY");
var varPop = aggf("VAR_POP");
var stddevPop = aggf("STDDEV_POP");
var corr = aggf("CORR");
var covarPop = aggf("COVAR_POP");
var regrIntercept = aggf("REGR_INTERCEPT");
var regrSlope = aggf("REGR_SLOPE");
var regrCount = aggf("REGR_COUNT");
var regrR2 = aggf("REGR_R2");
var regrSYY = aggf("REGR_SYY");
var regrSXX = aggf("REGR_SXX");
var regrSXY = aggf("REGR_SXY");
var regrAvgX = aggf("REGR_AVGX");
var regrAvgY = aggf("REGR_AVGY");
var first = aggf("FIRST");
var last = aggf("LAST");
var argmin = aggf("ARG_MIN");
var argmax = aggf("ARG_MAX");
var stringAgg = aggf("STRING_AGG");
var arrayAgg = aggf("ARRAY_AGG");
// src/datetime.js
var epoch_ms = transform(
(d) => `(1000 * (epoch(${d}) - second(${d})) + millisecond(${d}))::DOUBLE`
);
// src/list.js
function unnest(arg) {
return {
op: "UNNEST",
arg,
toString() {
return `UNNEST(${Array.isArray(arg) ? `[${arg.join(", ")}]` : arg})`;
// src/cast.js
function cast(expr, type) {
const arg = asColumn(expr);
const e = sql`CAST(${arg} AS ${type})`;
Object.defineProperty(e, "label", {
enumerable: true,
get() {
return expr.label;
}
};
});
Object.defineProperty(e, "aggregate", {
enumerable: true,
get() {
return expr.aggregate || false;
}
});
return e;
}
var castDouble = (expr) => cast(expr, "DOUBLE");
var castInteger = (expr) => cast(expr, "INTEGER");
// src/datetime.js
var epoch_ms = (expr) => {
const d = asColumn(expr);
return sql`(1000 * (epoch(${d}) - second(${d})) + millisecond(${d}))::DOUBLE`;
};
var dateMonth = (expr) => {
const d = asColumn(expr);
return sql`MAKE_DATE(2012, MONTH(${d}), 1)`.annotate({ label: "month" });
};
var dateMonthDay = (expr) => {
const d = asColumn(expr);
return sql`MAKE_DATE(2012, MONTH(${d}), DAY(${d}))`.annotate({ label: "date" });
};
var dateDay = (expr) => {
const d = asColumn(expr);
return sql`MAKE_DATE(2012, 1, DAY(${d}))`.annotate({ label: "date" });
};
// src/Query.js
var Query = class {
static select(...expr2) {
return new Query().select(...expr2);
static select(...expr) {
return new Query().select(...expr);
}
static from(...expr2) {
return new Query().from(...expr2);
static from(...expr) {
return new Query().from(...expr);
}
static with(...expr2) {
return new Query().with(...expr2);
static with(...expr) {
return new Query().with(...expr);
}

@@ -426,5 +566,5 @@ static union(...queries) {

}
with(...expr2) {
with(...expr) {
const { query } = this;
if (expr2.length === 0) {
if (expr.length === 0) {
return query.with;

@@ -438,3 +578,3 @@ } else {

};
expr2.flat().forEach((e) => {
expr.flat().forEach((e) => {
if (e == null) {

@@ -453,9 +593,9 @@ } else if (e.as && e.query) {

}
select(...expr2) {
select(...expr) {
const { query } = this;
if (expr2.length === 0) {
if (expr.length === 0) {
return query.select;
} else {
const list = [];
expr2.flat().forEach((e) => {
for (const e of expr.flat()) {
if (e == null) {

@@ -473,3 +613,3 @@ } else if (typeof e === "string") {

}
});
}
query.select = query.select.concat(list);

@@ -479,5 +619,5 @@ return this;

}
$select(...expr2) {
$select(...expr) {
this.query.select = [];
return this.select(...expr2);
return this.select(...expr);
}

@@ -488,9 +628,9 @@ distinct(value = true) {

}
from(...expr2) {
from(...expr) {
const { query } = this;
if (expr2.length === 0) {
if (expr.length === 0) {
return query.from;
} else {
const list = [];
expr2.flat().forEach((e) => {
expr.flat().forEach((e) => {
if (e == null) {

@@ -501,3 +641,3 @@ } else if (typeof e === "string") {

list.push({ as: e.table, from: e });
} else if (isQuery(e) || isExpression(e)) {
} else if (isQuery(e) || isSQLExpression(e)) {
list.push({ from: e });

@@ -516,7 +656,7 @@ } else if (Array.isArray(e)) {

}
$from(...expr2) {
$from(...expr) {
this.query.from = [];
return this.from(...expr2);
return this.from(...expr);
}
sample(value) {
sample(value, method) {
const { query } = this;

@@ -528,3 +668,3 @@ if (arguments.length === 0) {

if (typeof value === "number") {
spec = value > 0 && value < 1 ? { perc: 100 * value } : { rows: Math.round(value) };
spec = value > 0 && value < 1 ? { perc: 100 * value, method } : { rows: Math.round(value, method) };
}

@@ -535,9 +675,9 @@ query.sample = spec;

}
where(...expr2) {
where(...expr) {
const { query } = this;
if (expr2.length === 0) {
if (expr.length === 0) {
return query.where;
} else {
query.where = query.where.concat(
expr2.flat().filter((x) => x)
expr.flat().filter((x) => x)
);

@@ -547,13 +687,13 @@ return this;

}
$where(...expr2) {
$where(...expr) {
this.query.where = [];
return this.where(...expr2);
return this.where(...expr);
}
groupby(...expr2) {
groupby(...expr) {
const { query } = this;
if (expr2.length === 0) {
if (expr.length === 0) {
return query.groupby;
} else {
query.groupby = query.groupby.concat(
expr2.flat().filter((x) => x).map(asColumn)
expr.flat().filter((x) => x).map(asColumn)
);

@@ -563,9 +703,9 @@ return this;

}
having(...expr2) {
having(...expr) {
const { query } = this;
if (expr2.length === 0) {
if (expr.length === 0) {
return query.having;
} else {
query.having = query.having.concat(
expr2.flat().filter((x) => x)
expr.flat().filter((x) => x)
);

@@ -575,9 +715,9 @@ return this;

}
window(...expr2) {
window(...expr) {
const { query } = this;
if (expr2.length === 0) {
if (expr.length === 0) {
return query.window;
} else {
const list = [];
expr2.flat().forEach((e) => {
expr.flat().forEach((e) => {
if (e == null) {

@@ -594,9 +734,9 @@ } else {

}
qualify(...expr2) {
qualify(...expr) {
const { query } = this;
if (expr2.length === 0) {
if (expr.length === 0) {
return query.qualify;
} else {
query.qualify = query.qualify.concat(
expr2.flat().filter((x) => x)
expr.flat().filter((x) => x)
);

@@ -606,9 +746,9 @@ return this;

}
orderby(...expr2) {
orderby(...expr) {
const { query } = this;
if (expr2.length === 0) {
if (expr.length === 0) {
return query.orderby;
} else {
query.orderby = query.orderby.concat(
expr2.flat().filter((x) => x).map(asColumn)
expr.flat().filter((x) => x).map(asColumn)
);

@@ -673,3 +813,3 @@ return this;

const sels = select.map(
({ as, expr: expr2 }) => isColumnRefFor(expr2, as) && !expr2.table ? `${expr2}` : `${expr2} AS "${as}"`
({ as, expr }) => isColumnRefFor(expr, as) && !expr.table ? `${expr}` : `${expr} AS "${as}"`
);

@@ -684,2 +824,7 @@ sql2.push(`SELECT${distinct ? " DISTINCT" : ""} ${sels.join(", ")}`);

}
if (where.length) {
const clauses = where.map(String).filter((x) => x).join(" AND ");
if (clauses)
sql2.push(`WHERE ${clauses}`);
}
if (sample) {

@@ -691,7 +836,2 @@ const { rows, perc, method, seed } = sample;

}
if (where.length) {
const clauses = where.map(String).filter((x) => x).join(" AND ");
if (clauses)
sql2.push(`WHERE ${clauses}`);
}
if (groupby.length) {

@@ -706,3 +846,3 @@ sql2.push(`GROUP BY ${groupby.join(", ")}`);

if (window.length) {
const windows = window.map(({ as, expr: expr2 }) => `"${as}" AS (${expr2})`);
const windows = window.map(({ as, expr }) => `"${as}" AS (${expr})`);
sql2.push(`WINDOW ${windows.join(", ")}`);

@@ -738,9 +878,9 @@ }

}
orderby(...expr2) {
orderby(...expr) {
const { query } = this;
if (expr2.length === 0) {
if (expr.length === 0) {
return query.orderby;
} else {
query.orderby = query.orderby.concat(
expr2.flat().filter((x) => x).map(asColumn)
expr.flat().filter((x) => x).map(asColumn)
);

@@ -798,5 +938,68 @@ return this;

}
// src/load/create.js
function create(name, query, options = {}) {
const { temp, replace, type = "TABLE" } = options;
const create2 = `CREATE${replace ? " OR REPLACE" : ""}`;
const spec = `${temp ? "TEMP " : ""}${type}${replace ? "" : " IF NOT EXISTS"}`;
return `${create2} ${spec} ${name} AS ${query}`;
}
// src/load/parameters.js
function parameters(options) {
return Object.entries(options).map(([key, value]) => {
const t = typeof value;
const v = t === "boolean" ? String(value) : t === "string" ? `'${value}'` : value;
return `${key}=${v}`;
}).join(", ");
}
// src/load/csv.js
function loadCSV(tableName, fileName, options = {}) {
const { select = ["*"], temp, replace, ...csvOptions } = options;
const params = parameters({ auto_detect: true, sample_size: -1, ...csvOptions });
const query = `SELECT ${select.join(", ")} FROM read_csv('${fileName}', ${params})`;
return create(tableName, query, { temp, replace });
}
// src/load/json.js
function loadJSON(tableName, fileName, options = {}) {
const { select = ["*"], temp, replace, ...jsonOptions } = options;
const params = parameters({ auto_detect: true, json_format: "auto", ...jsonOptions });
const query = `SELECT ${select.join(", ")} FROM read_json('${fileName}', ${params})`;
return create(tableName, query, { temp, replace });
}
// src/load/parquet.js
function loadParquet(tableName, fileName, options = {}) {
const { select = ["*"], ...tableOptions } = options;
const query = `SELECT ${select.join(", ")} FROM read_parquet('${fileName}')`;
return create(tableName, query, tableOptions);
}
// src/load/sql-from.js
function sqlFrom(data, {
columns = Object.keys(data?.[0] || {})
} = {}) {
let keys = [];
if (Array.isArray(columns)) {
keys = columns;
columns = keys.reduce((m, k) => (m[k] = k, m), {});
} else if (columns) {
keys = Object.keys(columns);
}
if (!keys.length) {
throw new Error("Can not create table from empty column set.");
}
const subq = [];
for (const datum of data) {
const sel = keys.map((k) => `${literalToSQL(datum[k])} AS "${columns[k]}"`);
subq.push(`(SELECT ${sel.join(", ")})`);
}
return subq.join(" UNION ALL ");
}
export {
Query,
Ref,
agg,
all,

@@ -810,2 +1013,5 @@ and,

avg,
cast,
castDouble,
castInteger,
column,

@@ -816,2 +1022,8 @@ contains,

covarPop,
create,
cume_dist,
dateDay,
dateMonth,
dateMonthDay,
dense_rank,
desc,

@@ -821,5 +1033,4 @@ entropy,

eq,
expr,
exprParams,
first,
first_value,
gt,

@@ -836,8 +1047,16 @@ gte,

isNull,
isParamLike,
isQuery,
isSQLExpression,
kurtosis,
lag,
last,
last_value,
lead,
length,
literal,
literalToSQL,
loadCSV,
loadJSON,
loadParquet,
lower,

@@ -854,6 +1073,10 @@ lt,

not,
nth_value,
ntile,
or,
percent_rank,
prefix,
product,
quantile,
rank,
regexp_matches,

@@ -870,4 +1093,6 @@ regrAvgX,

relation,
row_number,
skewness,
sql,
sqlFrom,
stddev,

@@ -879,4 +1104,2 @@ stddevPop,

toSQL,
transform,
unnest,
upper,

@@ -883,0 +1106,0 @@ varPop,

2

dist/mosaic-sql.min.js

@@ -1,1 +0,1 @@

var x=class{constructor(t,r){t&&(this.table=String(t)),r&&(this.column=r)}get columns(){return this.column?[this.column]:[]}toString(){let{table:t,column:r}=this;if(r){let n=r==="*"?r:`"${r}"`;return(t?`"${t}".`:"")+n}else return`"${t}"`}};function W(e,t){return e instanceof x&&e.column===t}function u(e){return typeof e=="string"?k(e):e}function R(e){return typeof e=="string"?Q(e):e}function Q(e){return new x(e)}function k(e,t){return arguments.length===1?new x(null,e):new x(e,t)}function tt(e){return new x(e,"*")}function f(e){return typeof e=="string"?`"${e}"`:y(e)}function y(e){switch(typeof e){case"boolean":return e?"TRUE":"FALSE";case"string":return`'${e}'`;default:return e==null?"NULL":e instanceof Date?`MAKE_DATE(${e.getUTCFullYear()}, ${e.getUTCMonth()+1}, ${e.getUTCDate()})`:e instanceof RegExp?`'${e.source}'`:String(e)}}var q=e=>typeof e?.addEventListener=="function";function H(e){return e instanceof A}var A=class{constructor(t,r,n){this.expr=t,this.label=n,this.columns=r||[]}toString(){return`${this.expr}`}},C=class extends A{constructor(t,r,n){let s=new Set;for(let o of t)q(o)&&s.add(o);s.forEach(o=>{o.addEventListener("value",()=>this.update())}),super(t,r,n)}toString(){return this.expr.map(t=>q(t)?y(t.value):t).join("")}addEventListener(t,r){let n=this.map||(this.map=new Map);(n.get(t)||(n.set(t,new Set),n.get(t))).add(r)}update(){this.map?.get("value")?.forEach(t=>t(this))}};function F(e,t,r){return new C(e,t,r)}function T(e,t,r){return new A(e,t,r)}function rt(e){return Object.assign(T(`${u(e)} DESC NULLS LAST`,e?.columns,e?.label),{desc:!0})}function G(e,t){return r=>T(e(r),u(r).columns,t)}function et(e,...t){let r=[e[0]],n=new Set,s=t.length;for(let c=0,p=0;c<s;){let h=t[c];q(h)?r[++p]=h:(Array.isArray(h.columns)&&h.columns.forEach(b=>n.add(b)),r[p]+=String(h));let E=e[++c];q(r[p])?r[++p]=E:r[p]+=E}let o=Array.from(n);return r.length>1?F(r,o):T(r[0],o)}var P=class{constructor(t){this.value=t}toString(){return y(this.value)}},nt=e=>new P(e);function L(...e){return e.flat().flatMap(t=>t?.columns||[])}var U=class{constructor(t,r){this.op=t,this.a=u(r)}get columns(){return L(this.a)}visit(t){t(this.op,this)}toString(){let{op:t,a:r}=this;return`(${f(r)} ${t})`}};function Y(e){return t=>new U(e,t)}var st=Y("NOT"),ot=Y("IS NULL"),it=Y("IS NOT NULL"),M=class{constructor(t,r,n){this.op=t,this.a=u(r),this.b=u(n)}get columns(){return L(this.a,this.b)}visit(t){t(this.op,this)}toString(){let{op:t,a:r,b:n}=this;return`(${f(r)} ${t} ${f(n)})`}};function w(e){return(t,r)=>new M(e,t,r)}var ct=w("="),ut=w("<>"),lt=w("<"),at=w(">"),pt=w("<="),ft=w(">="),ht=w("IS DISTINCT FROM"),mt=w("IS NOT DISTINCT FROM"),j=class{constructor(t,r,n){this.op=t,this.expr=u(r),this.value=n?.map(u)}get columns(){return L(this.expr,this.value)}visit(t){t(this.op,this)}toString(){let{op:t,expr:r,value:n}=this;if(!n)return"";let[s,o]=n;return`(${f(r)} ${t} ${f(s)} AND ${f(o)})`}};function K(e){return(t,r)=>new j(e,t,r)}var gt=K("BETWEEN"),xt=K("NOT BETWEEN"),I=class{constructor(t,r){this.op=t,this.value=r.map(u)}get columns(){return L(this.value)}visit(t){t(this.op,this),this.value?.forEach(r=>r.visit(t))}toString(){let{op:t,value:r}=this;return!r||r.length===0?"":r.length===1?f(r[0]):`(${r.map(f).filter(n=>n).join(` ${t} `)})`}};function $t(...e){return new I("AND",e.flat())}function St(...e){return new I("OR",e.flat())}var _=class{constructor(t,r){this.func=t,this.args=(r||[]).map(u)}get column(){return this.columns[0]}get columns(){return this.args.flatMap(t=>t.columns||[])}toString(){let{func:t,args:r}=this;return`${t}(${r.map(f).join(", ")})`}};function $(e){return(...t)=>new _(e,t)}var dt=$("regexp_matches"),wt=$("contains"),yt=$("prefix"),Et=$("suffix"),Nt=$("lower"),Rt=$("upper"),qt=$("length"),At=$("isnan"),Tt=$("isfinite"),bt=$("isinf");var v=class{constructor(t,r){this.aggregate=t,this.args=(r||[]).map(u)}get label(){return this.aggregate.toLowerCase()+(this.args.length?` ${this.columns.join(", ")}`:"")}get column(){return this.columns[0]}get columns(){return this.args.flatMap(t=>t.columns||[])}distinct(){return this.isDistinct=!0,this}where(t){return this.filter=t,this}toString(){let{aggregate:t,args:r,isDistinct:n,filter:s}=this,o=r.length===0?"*":r.map(f).join(", "),c=n?"DISTINCT ":"",p=s?` FILTER (WHERE ${f(s)})`:"",h=t==="COUNT"?"::INTEGER":"";return p&&h?`(${t}(${c}${o})${p})${h}`:`${t}(${c}${o})${p}${h}`}};function i(e){return(...t)=>new v(e,t)}var It=i("COUNT"),Lt=i("AVG"),Dt=i("AVG"),Ot=i("MAD"),Ct=i("MAX"),Ft=i("MIN"),Gt=i("SUM"),Pt=i("PRODUCT"),Ut=i("MEDIAN"),Mt=i("QUANTILE"),jt=i("MODE"),Yt=i("VARIANCE"),_t=i("STDDEV"),vt=i("SKEWNESS"),Xt=i("KURTOSIS"),Bt=i("ENTROPY"),Vt=i("VAR_POP"),Wt=i("STDDEV_POP"),Qt=i("CORR"),kt=i("COVAR_POP"),Ht=i("REGR_INTERCEPT"),Kt=i("REGR_SLOPE"),zt=i("REGR_COUNT"),Jt=i("REGR_R2"),Zt=i("REGR_SYY"),tr=i("REGR_SXX"),rr=i("REGR_SXY"),er=i("REGR_AVGX"),nr=i("REGR_AVGY"),sr=i("FIRST"),or=i("LAST"),ir=i("ARG_MIN"),cr=i("ARG_MAX"),ur=i("STRING_AGG"),lr=i("ARRAY_AGG");var ar=G(e=>`(1000 * (epoch(${e}) - second(${e})) + millisecond(${e}))::DOUBLE`);function pr(e){return{op:"UNNEST",arg:e,toString(){return`UNNEST(${Array.isArray(e)?`[${e.join(", ")}]`:e})`}}}var S=class{static select(...t){return new S().select(...t)}static from(...t){return new S().from(...t)}static with(...t){return new S().with(...t)}static union(...t){return new d("UNION",t.flat())}static unionAll(...t){return new d("UNION ALL",t.flat())}static intersect(...t){return new d("INTERSECT",t.flat())}static except(...t){return new d("EXCEPT",t.flat())}constructor(){this.query={with:[],select:[],from:[],where:[],groupby:[],having:[],window:[],qualify:[],orderby:[]}}clone(){let t=new S;return t.query={...this.query},t}with(...t){let{query:r}=this;if(t.length===0)return r.with;{let n=[],s=(o,c)=>{let p=c.clone();p.cteFor=this,n.push({as:o,query:p})};return t.flat().forEach(o=>{if(o!=null)if(o.as&&o.query)s(o.as,o.query);else for(let c in o)s(c,o[c])}),r.with=r.with.concat(n),this}}select(...t){let{query:r}=this;if(t.length===0)return r.select;{let n=[];return t.flat().forEach(s=>{if(s!=null)if(typeof s=="string")n.push({as:s,expr:u(s)});else if(s instanceof x)n.push({as:s.column,expr:s});else if(Array.isArray(s))n.push({as:s[0],expr:s[1]});else for(let o in s)n.push({as:D(o),expr:u(s[o])})}),r.select=r.select.concat(n),this}}$select(...t){return this.query.select=[],this.select(...t)}distinct(t=!0){return this.query.distinct=!!t,this}from(...t){let{query:r}=this;if(t.length===0)return r.from;{let n=[];return t.flat().forEach(s=>{if(s!=null)if(typeof s=="string")n.push({as:s,from:R(s)});else if(s instanceof x)n.push({as:s.table,from:s});else if(O(s)||H(s))n.push({from:s});else if(Array.isArray(s))n.push({as:D(s[0]),from:R(s[1])});else for(let o in s)n.push({as:D(o),from:R(s[o])})}),r.from=r.from.concat(n),this}}$from(...t){return this.query.from=[],this.from(...t)}sample(t){let{query:r}=this;if(arguments.length===0)return r.sample;{let n=t;return typeof t=="number"&&(n=t>0&&t<1?{perc:100*t}:{rows:Math.round(t)}),r.sample=n,this}}where(...t){let{query:r}=this;return t.length===0?r.where:(r.where=r.where.concat(t.flat().filter(n=>n)),this)}$where(...t){return this.query.where=[],this.where(...t)}groupby(...t){let{query:r}=this;return t.length===0?r.groupby:(r.groupby=r.groupby.concat(t.flat().filter(n=>n).map(u)),this)}having(...t){let{query:r}=this;return t.length===0?r.having:(r.having=r.having.concat(t.flat().filter(n=>n)),this)}window(...t){let{query:r}=this;if(t.length===0)return r.window;{let n=[];return t.flat().forEach(s=>{if(s!=null)for(let o in s)n.push({as:D(o),expr:s[o]})}),r.window=r.window.concat(n),this}}qualify(...t){let{query:r}=this;return t.length===0?r.qualify:(r.qualify=r.qualify.concat(t.flat().filter(n=>n)),this)}orderby(...t){let{query:r}=this;return t.length===0?r.orderby:(r.orderby=r.orderby.concat(t.flat().filter(n=>n).map(u)),this)}limit(t){let{query:r}=this;return arguments.length===0?r.limit:(r.limit=Number.isFinite(t)?t:void 0,this)}offset(t){let{query:r}=this;return arguments.length===0?r.offset:(r.offset=Number.isFinite(t)?t:void 0,this)}get subqueries(){let{query:t,cteFor:r}=this,s=(r?.query||t).with?.reduce((c,{as:p,query:h})=>(c[p]=h,c),{}),o=[];return t.from.forEach(({from:c})=>{if(O(c))o.push(c);else if(s[c.table]){let p=s[c.table];o.push(p)}}),o}toString(){let{select:t,distinct:r,from:n,sample:s,where:o,groupby:c,having:p,window:h,qualify:E,orderby:b,limit:X,offset:B,with:V}=this.query,m=[];if(V.length){let l=V.map(({as:a,query:g})=>`"${a}" AS (${g})`);m.push(`WITH ${l.join(", ")}`)}let z=t.map(({as:l,expr:a})=>W(a,l)&&!a.table?`${a}`:`${a} AS "${l}"`);if(m.push(`SELECT${r?" DISTINCT":""} ${z.join(", ")}`),n.length){let l=n.map(({as:a,from:g})=>{let N=O(g)?`(${g})`:`${g}`;return!a||a===g.table?N:`${N} AS "${a}"`});m.push(`FROM ${l.join(", ")}`)}if(s){let{rows:l,perc:a,method:g,seed:N}=s,J=l?`${l} ROWS`:`${a} PERCENT`,Z=g?` (${g}${N!=null?`, ${N}`:""})`:"";m.push(`USING SAMPLE ${J}${Z}`)}if(o.length){let l=o.map(String).filter(a=>a).join(" AND ");l&&m.push(`WHERE ${l}`)}if(c.length&&m.push(`GROUP BY ${c.join(", ")}`),p.length){let l=p.map(String).filter(a=>a).join(" AND ");l&&m.push(`HAVING ${l}`)}if(h.length){let l=h.map(({as:a,expr:g})=>`"${a}" AS (${g})`);m.push(`WINDOW ${l.join(", ")}`)}if(E.length){let l=E.map(String).filter(a=>a).join(" AND ");l&&m.push(`QUALIFY ${l}`)}return b.length&&m.push(`ORDER BY ${b.join(", ")}`),Number.isFinite(X)&&m.push(`LIMIT ${X}`),Number.isFinite(B)&&m.push(`OFFSET ${B}`),m.join(" ")}},d=class{constructor(t,r){this.op=t,this.queries=r.map(n=>n.clone()),this.query={orderby:[]}}clone(){let t=new d(this.op,this.queries);return t.query={...this.query},t}orderby(...t){let{query:r}=this;return t.length===0?r.orderby:(r.orderby=r.orderby.concat(t.flat().filter(n=>n).map(u)),this)}limit(t){let{query:r}=this;return arguments.length===0?r.limit:(r.limit=Number.isFinite(t)?t:void 0,this)}offset(t){let{query:r}=this;return arguments.length===0?r.offset:(r.offset=Number.isFinite(t)?t:void 0,this)}get subqueries(){let{queries:t,cteFor:r}=this;return r&&t.forEach(n=>n.cteFor=r),t}toString(){let{op:t,queries:r,query:{orderby:n,limit:s,offset:o}}=this,c=[r.join(` ${t} `)];return n.length&&c.push(`ORDER BY ${n.join(", ")}`),Number.isFinite(s)&&c.push(`LIMIT ${s}`),Number.isFinite(o)&&c.push(`OFFSET ${o}`),c.join(" ")}};function O(e){return e instanceof S||e instanceof d}function D(e){return fr(e)?e.slice(1,-1):e}function fr(e){return e[0]==='"'&&e[e.length-1]==='"'}export{S as Query,x as Ref,tt as all,$t as and,cr as argmax,ir as argmin,lr as arrayAgg,u as asColumn,R as asRelation,Lt as avg,k as column,wt as contains,Qt as corr,It as count,kt as covarPop,rt as desc,Bt as entropy,ar as epoch_ms,ct as eq,T as expr,F as exprParams,sr as first,at as gt,ft as gte,gt as isBetween,ht as isDistinct,Tt as isFinite,bt as isInfinite,At as isNaN,xt as isNotBetween,mt as isNotDistinct,it as isNotNull,ot as isNull,O as isQuery,Xt as kurtosis,or as last,qt as length,nt as literal,y as literalToSQL,Nt as lower,lt,pt as lte,Ot as mad,Ct as max,Dt as mean,Ut as median,Ft as min,jt as mode,ut as neq,st as not,St as or,yt as prefix,Pt as product,Mt as quantile,dt as regexp_matches,er as regrAvgX,nr as regrAvgY,zt as regrCount,Ht as regrIntercept,Jt as regrR2,tr as regrSXX,rr as regrSXY,Zt as regrSYY,Kt as regrSlope,Q as relation,vt as skewness,et as sql,_t as stddev,Wt as stddevPop,ur as stringAgg,Et as suffix,Gt as sum,f as toSQL,G as transform,pr as unnest,Rt as upper,Vt as varPop,Yt as variance};
var y=class{constructor(t,e){t&&(this.table=String(t)),e&&(this.column=e)}get columns(){return this.column?[this.column]:[]}toString(){let{table:t,column:e}=this;if(e){let n=e.startsWith("*")?e:`"${e}"`;return`${t?`"${t}".`:""}${n}`}else return t?`"${t}"`:"NULL"}};function X(r,t){return r instanceof y&&r.column===t}function l(r){return typeof r=="string"?W(r):r}function D(r){return typeof r=="string"?V(r):r}function V(r){return new y(r)}function W(r,t){return arguments.length===1&&(t=r,r=null),new y(r,t)}function tt(r){return new y(r,"*")}function et(r){return typeof r=="string"?`"${r}"`:d(r)}function d(r){switch(typeof r){case"boolean":return r?"TRUE":"FALSE";case"string":return`'${r}'`;case"number":return Number.isFinite(r)?String(r):"NULL";default:if(r==null)return"NULL";if(r instanceof Date){let t=+r;if(Number.isNaN(t))return"NULL";let e=r.getUTCFullYear(),n=r.getUTCMonth(),o=r.getUTCDate();return t===Date.UTC(e,n,o)?`MAKE_DATE(${e}, ${n+1}, ${o})`:`EPOCH_MS(${t})`}else return r instanceof RegExp?`'${r.source}'`:String(r)}}var b=r=>typeof r?.addEventListener=="function";function M(r){return r instanceof A}var A=class{constructor(t,e,n){this._expr=Array.isArray(t)?t:[t],this._deps=e||[],this.annotate(n);let o=this._expr.filter(s=>b(s));o.length>0?(this._params=Array.from(new Set(o)),this._params.forEach(s=>{s.addEventListener("value",()=>rt(this,this.map?.get("value")))})):this.addEventListener=void 0}get value(){return this}get columns(){let{_params:t,_deps:e}=this;if(t){let n=new Set(t.flatMap(o=>{let s=o.value?.columns;return Array.isArray(s)?s:[]}));if(n.size){let o=new Set(e);return n.forEach(s=>o.add(s)),Array.from(o)}}return e}get column(){return this._deps.length?this._deps[0]:this.columns[0]}annotate(...t){return Object.assign(this,...t)}toString(){return this._expr.map(t=>b(t)&&!M(t)?d(t.value):t).join("")}addEventListener(t,e){let n=this.map||(this.map=new Map);(n.get(t)||(n.set(t,new Set),n.get(t))).add(e)}};function rt(r,t){if(t?.size)return Promise.allSettled(Array.from(t,e=>e(r)))}function Y(r,t){let e=[r[0]],n=new Set,o=t.length;for(let s=0,i=0;s<o;){let c=t[s];b(c)?e[++i]=c:(Array.isArray(c?.columns)&&c.columns.forEach(x=>n.add(x)),e[i]+=typeof c=="string"?c:d(c));let p=r[++s];b(e[i])?e[++i]=p:e[i]+=p}return{spans:e,cols:Array.from(n)}}function u(r,...t){let{spans:e,cols:n}=Y(r,t);return new A(e,n)}function nt(r){let t=l(r);return u`${t} DESC NULLS LAST`.annotate({label:t?.label,desc:!0})}var ot=r=>({value:r,toString:()=>d(r)});function C(r){r(this.op,this),this.children?.forEach(t=>t.visit(r))}function Q(r,t){let e=t.filter(o=>o!=null).map(l),n=e.map((o,s)=>s?` ${r} `:"");return t.length&&n.push(""),u(n,...e).annotate({op:r,children:e,visit:C})}var st=(...r)=>Q("AND",r.flat()),it=(...r)=>Q("OR",r.flat()),ct=r=>t=>u`(${r} ${l(t)})`.annotate({op:r,a:t,visit:C}),at=ct("NOT"),k=r=>t=>u`(${l(t)} ${r})`.annotate({op:r,a:t,visit:C}),ut=k("IS NULL"),lt=k("IS NOT NULL"),T=r=>(t,e)=>u`(${l(t)} ${r} ${l(e)})`.annotate({op:r,a:t,b:e,visit:C}),pt=T("="),ft=T("<>"),mt=T("<"),ht=T(">"),gt=T("<="),xt=T(">="),Et=T("IS DISTINCT FROM"),dt=T("IS NOT DISTINCT FROM");function v(r,t,e,n){t=l(t);let o=r.startsWith("NOT ")?"NOT ":"";return(e?n?u`${o}(${e[0]} <= ${t} AND ${t} < ${e[1]})`:u`(${t} ${r} ${e[0]} AND ${e[1]})`:u``).annotate({op:r,visit:C,field:t,range:e})}var $t=(r,t,e)=>v("BETWEEN",r,t,e),Nt=(r,t,e)=>v("NOT BETWEEN",r,t,e);function q(r,t){return Array.from({length:r},()=>t)}function g(r,t){return(...e)=>{let n=e.map(l),o=t?`::${t}`:"";return(n.length?u([`${r}(`,...q(n.length-1,", "),`)${o}`],...n):u`${r}()${o}`).annotate({func:r,args:n})}}var yt=g("REGEXP_MATCHES"),St=g("CONTAINS"),wt=g("PREFIX"),Rt=g("SUFFIX"),At=g("LOWER"),Tt=g("UPPER"),bt=g("LENGTH"),qt=g("ISNAN"),Lt=g("ISFINITE"),Ot=g("ISINF");var $=class extends A{constructor(t,e,n,o,s="",i="",c=""){let p;if(o&&!(s||i||c))p=o?u`${e} OVER "${o}"`:u`${e} OVER ()`;else{let P=s&&i?" ":"",U=(s||i)&&c?" ":"";p=u`${e} OVER (${o?`"${o}" `:""}${s}${P}${i}${U}${c})`}n&&(p=u`(${p})::${n}`);let{_expr:R,_deps:_}=p;super(R,_,{window:t,func:e,type:n,name:o,group:s,order:i,frame:c})}get basis(){return this.column}get label(){let{func:t}=this;return t.label??t.toString()}over(t){let{window:e,func:n,type:o,group:s,order:i,frame:c}=this;return new $(e,n,o,t,s,i,c)}partitionby(...t){let e=t.flat().filter(R=>R).map(l),n=u(["PARTITION BY ",q(e.length-1,", "),""],...e),{window:o,func:s,type:i,name:c,order:p,frame:x}=this;return new $(o,s,i,c,n,p,x)}orderby(...t){let e=t.flat().filter(R=>R).map(l),n=u(["ORDER BY ",q(e.length-1,", "),""],...e),{window:o,func:s,type:i,name:c,group:p,frame:x}=this;return new $(o,s,i,c,p,n,x)}rows(t){let e=H("ROWS",t),{window:n,func:o,type:s,name:i,group:c,order:p}=this;return new $(n,o,s,i,c,p,e)}range(t){let e=H("RANGE",t),{window:n,func:o,type:s,name:i,group:c,order:p}=this;return new $(n,o,s,i,c,p,e)}};function H(r,t){if(b(t)){let e=u`${t}`;return e.toString=()=>`${r} ${K(t.value)}`,e}return`${r} ${K(t)}`}function K(r){let[t,e]=r,n=t===0?"CURRENT ROW":Number.isFinite(t)?`${Math.abs(t)} PRECEDING`:"UNBOUNDED PRECEDING",o=e===0?"CURRENT ROW":Number.isFinite(e)?`${Math.abs(e)} FOLLOWING`:"UNBOUNDED FOLLOWING";return`BETWEEN ${n} AND ${o}`}function N(r,t){return(...e)=>{let n=g(r)(...e);return new $(r,n,t)}}var It=N("ROW_NUMBER","INTEGER"),Dt=N("RANK","INTEGER"),Ct=N("DENSE_RANK","INTEGER"),_t=N("PERCENT_RANK"),Pt=N("CUME_DIST"),Ut=N("NTILE"),Mt=N("LAG"),Ft=N("LEAD"),Gt=N("FIRST_VALUE"),jt=N("LAST_VALUE"),Yt=N("NTH_VALUE");function Bt(r,...t){return u(r,...t).annotate({aggregate:!0})}var L=class extends A{constructor(t,e,n,o,s){e=(e||[]).map(l);let{strings:i,exprs:c}=Xt(t,e,n,o,s),{spans:p,cols:x}=Y(i,c);super(p,x,{aggregate:t,args:e,type:n,isDistinct:o,filter:s})}get basis(){return this.column}get label(){let{aggregate:t,args:e,isDistinct:n}=this,o=n?"DISTINCT"+(e.length?" ":""):"",s=e.length?`(${o}${e.map(Vt).join(", ")})`:"";return`${t.toLowerCase()}${s}`}distinct(){let{aggregate:t,args:e,type:n,filter:o}=this;return new L(t,e,n,!0,o)}where(t){let{aggregate:e,args:n,type:o,isDistinct:s}=this;return new L(e,n,o,s,t)}window(){let{aggregate:t,args:e,type:n,isDistinct:o}=this,s=new L(t,e,null,o);return new $(t,s,n)}partitionby(...t){return this.window().partitionby(...t)}orderby(...t){return this.window().orderby(...t)}rows(t,e){return this.window().rows(t,e)}range(t,e){return this.window().range(t,e)}};function Xt(r,t,e,n,o){let s=`)${e?`::${e}`:""}`,i=[`${r}(${n?"DISTINCT ":""}`],c=[];return t.length?(i=i.concat([...q(t.length-1,", "),`${s}${o?" FILTER (WHERE ":""}`,...o?[")"]:[]]),c=[...t,...o?[o]:[]]):i[0]+="*"+s,{exprs:c,strings:i}}function Vt(r){let t=d(r);return t&&t.startsWith('"')&&t.endsWith('"')?t.slice(1,-1):t}function a(r,t){return(...e)=>new L(r,e,t)}var Wt=a("COUNT","INTEGER"),Qt=a("AVG"),kt=a("AVG"),vt=a("MAD"),Ht=a("MAX"),Kt=a("MIN"),zt=a("SUM","DOUBLE"),Jt=a("PRODUCT"),Zt=a("MEDIAN"),te=a("QUANTILE"),ee=a("MODE"),re=a("VARIANCE"),ne=a("STDDEV"),oe=a("SKEWNESS"),se=a("KURTOSIS"),ie=a("ENTROPY"),ce=a("VAR_POP"),ae=a("STDDEV_POP"),ue=a("CORR"),le=a("COVAR_POP"),pe=a("REGR_INTERCEPT"),fe=a("REGR_SLOPE"),me=a("REGR_COUNT"),he=a("REGR_R2"),ge=a("REGR_SYY"),xe=a("REGR_SXX"),Ee=a("REGR_SXY"),de=a("REGR_AVGX"),$e=a("REGR_AVGY"),Ne=a("FIRST"),ye=a("LAST"),Se=a("ARG_MIN"),we=a("ARG_MAX"),Re=a("STRING_AGG"),Ae=a("ARRAY_AGG");function B(r,t){let e=l(r),n=u`CAST(${e} AS ${t})`;return Object.defineProperty(n,"label",{enumerable:!0,get(){return r.label}}),Object.defineProperty(n,"aggregate",{enumerable:!0,get(){return r.aggregate||!1}}),n}var Te=r=>B(r,"DOUBLE"),be=r=>B(r,"INTEGER");var qe=r=>{let t=l(r);return u`(1000 * (epoch(${t}) - second(${t})) + millisecond(${t}))::DOUBLE`},Le=r=>{let t=l(r);return u`MAKE_DATE(2012, MONTH(${t}), 1)`.annotate({label:"month"})},Oe=r=>{let t=l(r);return u`MAKE_DATE(2012, MONTH(${t}), DAY(${t}))`.annotate({label:"date"})},Ie=r=>{let t=l(r);return u`MAKE_DATE(2012, 1, DAY(${t}))`.annotate({label:"date"})};var S=class{static select(...t){return new S().select(...t)}static from(...t){return new S().from(...t)}static with(...t){return new S().with(...t)}static union(...t){return new w("UNION",t.flat())}static unionAll(...t){return new w("UNION ALL",t.flat())}static intersect(...t){return new w("INTERSECT",t.flat())}static except(...t){return new w("EXCEPT",t.flat())}constructor(){this.query={with:[],select:[],from:[],where:[],groupby:[],having:[],window:[],qualify:[],orderby:[]}}clone(){let t=new S;return t.query={...this.query},t}with(...t){let{query:e}=this;if(t.length===0)return e.with;{let n=[],o=(s,i)=>{let c=i.clone();c.cteFor=this,n.push({as:s,query:c})};return t.flat().forEach(s=>{if(s!=null)if(s.as&&s.query)o(s.as,s.query);else for(let i in s)o(i,s[i])}),e.with=e.with.concat(n),this}}select(...t){let{query:e}=this;if(t.length===0)return e.select;{let n=[];for(let o of t.flat())if(o!=null)if(typeof o=="string")n.push({as:o,expr:l(o)});else if(o instanceof y)n.push({as:o.column,expr:o});else if(Array.isArray(o))n.push({as:o[0],expr:o[1]});else for(let s in o)n.push({as:F(s),expr:l(o[s])});return e.select=e.select.concat(n),this}}$select(...t){return this.query.select=[],this.select(...t)}distinct(t=!0){return this.query.distinct=!!t,this}from(...t){let{query:e}=this;if(t.length===0)return e.from;{let n=[];return t.flat().forEach(o=>{if(o!=null)if(typeof o=="string")n.push({as:o,from:D(o)});else if(o instanceof y)n.push({as:o.table,from:o});else if(G(o)||M(o))n.push({from:o});else if(Array.isArray(o))n.push({as:F(o[0]),from:D(o[1])});else for(let s in o)n.push({as:F(s),from:D(o[s])})}),e.from=e.from.concat(n),this}}$from(...t){return this.query.from=[],this.from(...t)}sample(t,e){let{query:n}=this;if(arguments.length===0)return n.sample;{let o=t;return typeof t=="number"&&(o=t>0&&t<1?{perc:100*t,method:e}:{rows:Math.round(t,e)}),n.sample=o,this}}where(...t){let{query:e}=this;return t.length===0?e.where:(e.where=e.where.concat(t.flat().filter(n=>n)),this)}$where(...t){return this.query.where=[],this.where(...t)}groupby(...t){let{query:e}=this;return t.length===0?e.groupby:(e.groupby=e.groupby.concat(t.flat().filter(n=>n).map(l)),this)}having(...t){let{query:e}=this;return t.length===0?e.having:(e.having=e.having.concat(t.flat().filter(n=>n)),this)}window(...t){let{query:e}=this;if(t.length===0)return e.window;{let n=[];return t.flat().forEach(o=>{if(o!=null)for(let s in o)n.push({as:F(s),expr:o[s]})}),e.window=e.window.concat(n),this}}qualify(...t){let{query:e}=this;return t.length===0?e.qualify:(e.qualify=e.qualify.concat(t.flat().filter(n=>n)),this)}orderby(...t){let{query:e}=this;return t.length===0?e.orderby:(e.orderby=e.orderby.concat(t.flat().filter(n=>n).map(l)),this)}limit(t){let{query:e}=this;return arguments.length===0?e.limit:(e.limit=Number.isFinite(t)?t:void 0,this)}offset(t){let{query:e}=this;return arguments.length===0?e.offset:(e.offset=Number.isFinite(t)?t:void 0,this)}get subqueries(){let{query:t,cteFor:e}=this,o=(e?.query||t).with?.reduce((i,{as:c,query:p})=>(i[c]=p,i),{}),s=[];return t.from.forEach(({from:i})=>{if(G(i))s.push(i);else if(o[i.table]){let c=o[i.table];s.push(c)}}),s}toString(){let{select:t,distinct:e,from:n,sample:o,where:s,groupby:i,having:c,window:p,qualify:x,orderby:R,limit:_,offset:P,with:U}=this.query,h=[];if(U.length){let f=U.map(({as:m,query:E})=>`"${m}" AS (${E})`);h.push(`WITH ${f.join(", ")}`)}let z=t.map(({as:f,expr:m})=>X(m,f)&&!m.table?`${m}`:`${m} AS "${f}"`);if(h.push(`SELECT${e?" DISTINCT":""} ${z.join(", ")}`),n.length){let f=n.map(({as:m,from:E})=>{let I=G(E)?`(${E})`:`${E}`;return!m||m===E.table?I:`${I} AS "${m}"`});h.push(`FROM ${f.join(", ")}`)}if(s.length){let f=s.map(String).filter(m=>m).join(" AND ");f&&h.push(`WHERE ${f}`)}if(o){let{rows:f,perc:m,method:E,seed:I}=o,J=f?`${f} ROWS`:`${m} PERCENT`,Z=E?` (${E}${I!=null?`, ${I}`:""})`:"";h.push(`USING SAMPLE ${J}${Z}`)}if(i.length&&h.push(`GROUP BY ${i.join(", ")}`),c.length){let f=c.map(String).filter(m=>m).join(" AND ");f&&h.push(`HAVING ${f}`)}if(p.length){let f=p.map(({as:m,expr:E})=>`"${m}" AS (${E})`);h.push(`WINDOW ${f.join(", ")}`)}if(x.length){let f=x.map(String).filter(m=>m).join(" AND ");f&&h.push(`QUALIFY ${f}`)}return R.length&&h.push(`ORDER BY ${R.join(", ")}`),Number.isFinite(_)&&h.push(`LIMIT ${_}`),Number.isFinite(P)&&h.push(`OFFSET ${P}`),h.join(" ")}},w=class{constructor(t,e){this.op=t,this.queries=e.map(n=>n.clone()),this.query={orderby:[]}}clone(){let t=new w(this.op,this.queries);return t.query={...this.query},t}orderby(...t){let{query:e}=this;return t.length===0?e.orderby:(e.orderby=e.orderby.concat(t.flat().filter(n=>n).map(l)),this)}limit(t){let{query:e}=this;return arguments.length===0?e.limit:(e.limit=Number.isFinite(t)?t:void 0,this)}offset(t){let{query:e}=this;return arguments.length===0?e.offset:(e.offset=Number.isFinite(t)?t:void 0,this)}get subqueries(){let{queries:t,cteFor:e}=this;return e&&t.forEach(n=>n.cteFor=e),t}toString(){let{op:t,queries:e,query:{orderby:n,limit:o,offset:s}}=this,i=[e.join(` ${t} `)];return n.length&&i.push(`ORDER BY ${n.join(", ")}`),Number.isFinite(o)&&i.push(`LIMIT ${o}`),Number.isFinite(s)&&i.push(`OFFSET ${s}`),i.join(" ")}};function G(r){return r instanceof S||r instanceof w}function F(r){return De(r)?r.slice(1,-1):r}function De(r){return r[0]==='"'&&r[r.length-1]==='"'}function O(r,t,e={}){let{temp:n,replace:o,type:s="TABLE"}=e,i=`CREATE${o?" OR REPLACE":""}`,c=`${n?"TEMP ":""}${s}${o?"":" IF NOT EXISTS"}`;return`${i} ${c} ${r} AS ${t}`}function j(r){return Object.entries(r).map(([t,e])=>{let n=typeof e,o=n==="boolean"?String(e):n==="string"?`'${e}'`:e;return`${t}=${o}`}).join(", ")}function Ce(r,t,e={}){let{select:n=["*"],temp:o,replace:s,...i}=e,c=j({auto_detect:!0,sample_size:-1,...i}),p=`SELECT ${n.join(", ")} FROM read_csv('${t}', ${c})`;return O(r,p,{temp:o,replace:s})}function _e(r,t,e={}){let{select:n=["*"],temp:o,replace:s,...i}=e,c=j({auto_detect:!0,json_format:"auto",...i}),p=`SELECT ${n.join(", ")} FROM read_json('${t}', ${c})`;return O(r,p,{temp:o,replace:s})}function Pe(r,t,e={}){let{select:n=["*"],...o}=e,s=`SELECT ${n.join(", ")} FROM read_parquet('${t}')`;return O(r,s,o)}function Ue(r,{columns:t=Object.keys(r?.[0]||{})}={}){let e=[];if(Array.isArray(t)?(e=t,t=e.reduce((o,s)=>(o[s]=s,o),{})):t&&(e=Object.keys(t)),!e.length)throw new Error("Can not create table from empty column set.");let n=[];for(let o of r){let s=e.map(i=>`${d(o[i])} AS "${t[i]}"`);n.push(`(SELECT ${s.join(", ")})`)}return n.join(" UNION ALL ")}export{S as Query,y as Ref,Bt as agg,tt as all,st as and,we as argmax,Se as argmin,Ae as arrayAgg,l as asColumn,D as asRelation,Qt as avg,B as cast,Te as castDouble,be as castInteger,W as column,St as contains,ue as corr,Wt as count,le as covarPop,O as create,Pt as cume_dist,Ie as dateDay,Le as dateMonth,Oe as dateMonthDay,Ct as dense_rank,nt as desc,ie as entropy,qe as epoch_ms,pt as eq,Ne as first,Gt as first_value,ht as gt,xt as gte,$t as isBetween,Et as isDistinct,Lt as isFinite,Ot as isInfinite,qt as isNaN,Nt as isNotBetween,dt as isNotDistinct,lt as isNotNull,ut as isNull,b as isParamLike,G as isQuery,M as isSQLExpression,se as kurtosis,Mt as lag,ye as last,jt as last_value,Ft as lead,bt as length,ot as literal,d as literalToSQL,Ce as loadCSV,_e as loadJSON,Pe as loadParquet,At as lower,mt as lt,gt as lte,vt as mad,Ht as max,kt as mean,Zt as median,Kt as min,ee as mode,ft as neq,at as not,Yt as nth_value,Ut as ntile,it as or,_t as percent_rank,wt as prefix,Jt as product,te as quantile,Dt as rank,yt as regexp_matches,de as regrAvgX,$e as regrAvgY,me as regrCount,pe as regrIntercept,he as regrR2,xe as regrSXX,Ee as regrSXY,ge as regrSYY,fe as regrSlope,V as relation,It as row_number,oe as skewness,u as sql,Ue as sqlFrom,ne as stddev,ae as stddevPop,Re as stringAgg,Rt as suffix,zt as sum,et as toSQL,Tt as upper,ce as varPop,re as variance};

4

package.json
{
"name": "@uwdata/mosaic-sql",
"version": "0.1.0",
"version": "0.2.0",
"description": "SQL query construction and analysis.",

@@ -28,3 +28,3 @@ "keywords": [

},
"gitHead": "a7967c35349bdf7f00abb113ce1dd9abb233cd62"
"gitHead": "e53cd914c807f99aabe78dcbe618dd9543e2f438"
}

28

src/datetime.js

@@ -1,5 +0,25 @@

import { transform } from './expression.js';
import { sql } from './expression.js';
import { asColumn } from './ref.js';
export const epoch_ms = transform(
d => `(1000 * (epoch(${d}) - second(${d})) + millisecond(${d}))::DOUBLE`
);
export const epoch_ms = expr => {
const d = asColumn(expr);
return sql`(1000 * (epoch(${d}) - second(${d})) + millisecond(${d}))::DOUBLE`;
};
export const dateMonth = expr => {
const d = asColumn(expr);
return sql`MAKE_DATE(2012, MONTH(${d}), 1)`
.annotate({ label: 'month' });
};
export const dateMonthDay = expr => {
const d = asColumn(expr);
return sql`MAKE_DATE(2012, MONTH(${d}), DAY(${d}))`
.annotate({ label: 'date' });
};
export const dateDay = expr => {
const d = asColumn(expr);
return sql`MAKE_DATE(2012, 1, DAY(${d}))`
.annotate({ label: 'date' });
};

167

src/expression.js

@@ -1,40 +0,113 @@

import { asColumn } from './ref.js';
import { literalToSQL } from './to-sql.js';
export const isParamLike = e => typeof e?.addEventListener === 'function';
/**
* Test if a value is parameter-like. Parameters have addEventListener methods.
* @param {*} value The value to test.
* @returns True if the value is param-like, false otherwise.
*/
export const isParamLike = value => typeof value?.addEventListener === 'function';
export function isExpression(e) {
return e instanceof SQLExpression;
/**
* Test if a value is a SQL expression instance.
* @param {*} value The value to test.
* @returns {boolean} True if value is a SQL expression, false otherwise.
*/
export function isSQLExpression(value) {
return value instanceof SQLExpression;
}
/**
* Base class for all SQL expressions. Most callers should use the `sql`
* template tag rather than instantiate this class.
*/
export class SQLExpression {
constructor(sql, columns, label) {
this.expr = sql;
this.label = label;
this.columns = columns || [];
/**
* Create a new SQL expression instance.
* @param {(string|SQLExpression|Ref)[]} parts The parts of the expression.
* @param {string[]} [columns=[]] The column dependencies
* @param {object} [props] Additional properties for this expression.
*/
constructor(parts, columns, props) {
this._expr = Array.isArray(parts) ? parts : [parts];
this._deps = columns || [];
this.annotate(props);
const params = this._expr.filter(part => isParamLike(part));
if (params.length > 0) {
this._params = Array.from(new Set(params));
this._params.forEach(param => {
param.addEventListener('value', () => update(this, this.map?.get('value')));
});
} else {
// do not support event listeners if not needed
// this causes the expression instance to NOT be param-like
this.addEventListener = undefined;
}
}
toString() {
return `${this.expr}`;
/**
* A reference to this expression.
* Provides compatibility with param-like objects.
*/
get value() {
return this;
}
}
export class ParameterizedSQLExpression extends SQLExpression{
constructor(parts, columns, label) {
const paramSet = new Set;
for (const part of parts) {
if (isParamLike(part)) paramSet.add(part);
/**
* The column dependencies of this expression.
* @returns {string[]} The columns dependencies.
*/
get columns() {
const { _params, _deps } = this;
if (_params) {
// pull latest dependencies, as they may change across updates
const pset = new Set(_params.flatMap(p => {
const cols = p.value?.columns;
return Array.isArray(cols) ? cols : [];
}));
if (pset.size) {
const set = new Set(_deps);
pset.forEach(col => set.add(col));
return Array.from(set);
}
}
paramSet.forEach(param => {
param.addEventListener('value', () => this.update());
});
super(parts, columns, label);
// if no params, return fixed dependencies
return _deps;
}
/**
* The first column dependency in this expression, or undefined if none.
* @returns {string} The first column dependency.
*/
get column() {
return this._deps.length ? this._deps[0] : this.columns[0];
}
/**
* Annotate this expression instance with additional properties.
* @param {object[]} [props] One or more objects with properties to add.
* @returns {this} This SQL expression.
*/
annotate(...props) {
return Object.assign(this, ...props);
}
/**
* Generate a SQL code string corresponding to this expression.
* @returns {string} A SQL code string.
*/
toString() {
return this.expr
.map(p => isParamLike(p) ? literalToSQL(p.value) : p)
return this._expr
.map(p => isParamLike(p) && !isSQLExpression(p) ? literalToSQL(p.value) : p)
.join('');
}
/**
* Add an event listener callback for the provided event type.
* @param {string} type The event type to listen for (for example, "value").
* @param {(a: SQLExpression) => Promise?} callback The callback function to
* invoke upon updates. A callback may optionally return a Promise that
* upstream listeners may await before proceeding.
*/
addEventListener(type, callback) {

@@ -45,25 +118,43 @@ const map = this.map || (this.map = new Map());

}
}
update() {
this.map?.get('value')?.forEach(callback => callback(this));
function update(expr, callbacks) {
if (callbacks?.size) {
return Promise.allSettled(Array.from(callbacks, fn => fn(expr)));
}
}
export function exprParams(parts, columns, label) {
return new ParameterizedSQLExpression(parts, columns, label);
}
export function parseSQL(strings, exprs) {
const spans = [strings[0]];
const cols = new Set;
const n = exprs.length;
for (let i = 0, k = 0; i < n;) {
const e = exprs[i];
if (isParamLike(e)) {
spans[++k] = e;
} else {
if (Array.isArray(e?.columns)) {
e.columns.forEach(col => cols.add(col));
}
spans[k] += typeof e === 'string' ? e : literalToSQL(e);
}
const s = strings[++i];
if (isParamLike(spans[k])) {
spans[++k] = s;
} else {
spans[k] += s;
}
}
export function expr(sql, columns, label) {
return new SQLExpression(sql, columns, label);
return { spans, cols: Array.from(cols) };
}
export function desc(e) {
return Object.assign(
expr(`${asColumn(e)} DESC NULLS LAST`, e?.columns, e?.label),
{ desc: true }
);
/**
* Tag function for SQL expressions. Interpolated values
* may be strings, other SQL expression objects (such as column
* references), or parameterized values.
*/
export function sql(strings, ...exprs) {
const { spans, cols } = parseSQL(strings, exprs);
return new SQLExpression(spans, cols);
}
export function transform(func, label) {
return value => expr(func(value), asColumn(value).columns, label);
}

91

src/index.js

@@ -11,18 +11,12 @@ export {

export {
transform,
desc,
expr,
exprParams
isSQLExpression,
isParamLike,
sql
} from './expression.js';
export {
sql
} from './sql-tag.js';
desc
} from './desc.js';
export {
toSQL,
literalToSQL
} from './to-sql.js';
export {
literal

@@ -35,4 +29,2 @@ } from './literal.js';

not,
isNull,
isNotNull,
eq,

@@ -44,22 +36,12 @@ neq,

gte,
isBetween,
isNotBetween,
isDistinct,
isNotDistinct,
isBetween,
isNotBetween
} from './compare.js';
isNull,
isNotNull
} from './operators.js';
export {
regexp_matches,
contains,
prefix,
suffix,
lower,
upper,
length,
isNaN,
isFinite,
isInfinite
} from './function-call.js'
export {
agg,
argmax,

@@ -100,15 +82,58 @@ argmin,

varPop
} from './aggregate.js';
} from './aggregates.js';
export {
epoch_ms
cast,
castDouble,
castInteger
} from './cast.js';
export {
epoch_ms,
dateMonth,
dateMonthDay,
dateDay
} from './datetime.js';
export {
unnest
} from './list.js';
regexp_matches,
contains,
prefix,
suffix,
lower,
upper,
length,
isNaN,
isFinite,
isInfinite
} from './functions.js';
export {
row_number,
rank,
dense_rank,
percent_rank,
cume_dist,
ntile,
lag,
lead,
first_value,
last_value,
nth_value
} from './windows.js';
export {
Query,
isQuery
} from './Query.js';
export {
toSQL,
literalToSQL
} from './to-sql.js';
export { create } from './load/create.js';
export { loadCSV } from './load/csv.js';
export { loadJSON } from './load/json.js';
export { loadParquet } from './load/parquet.js';
export { sqlFrom } from './load/sql-from.js';

15

src/literal.js
import { literalToSQL } from './to-sql.js';
export class Literal {
constructor(value) {
this.value = value;
}
toString() {
return literalToSQL(this.value);
}
}
export const literal = value => new Literal(value);
export const literal = value => ({
value,
toString: () => literalToSQL(value)
});

26

src/Query.js

@@ -1,2 +0,2 @@

import { isExpression } from './expression.js';
import { isSQLExpression } from './expression.js';
import { asColumn, asRelation, isColumnRefFor, Ref } from './ref.js';

@@ -87,3 +87,3 @@

const list = [];
expr.flat().forEach(e => {
for (const e of expr.flat()) {
if (e == null) {

@@ -102,3 +102,3 @@ // do nothing

}
});
}
query.select = query.select.concat(list);

@@ -132,3 +132,3 @@ return this;

list.push({ as: e.table, from: e });
} else if (isQuery(e) || isExpression(e)) {
} else if (isQuery(e) || isSQLExpression(e)) {
list.push({ from: e });

@@ -153,3 +153,3 @@ } else if (Array.isArray(e)) {

sample(value) {
sample(value, method) {
const { query } = this;

@@ -162,4 +162,4 @@ if (arguments.length === 0) {

spec = value > 0 && value < 1
? { perc: 100 * value }
: { rows: Math.round(value) };
? { perc: 100 * value, method }
: { rows: Math.round(value, method) };
}

@@ -323,2 +323,8 @@ query.sample = spec;

// WHERE
if (where.length) {
const clauses = where.map(String).filter(x => x).join(' AND ');
if (clauses) sql.push(`WHERE ${clauses}`);
}
// SAMPLE

@@ -332,8 +338,2 @@ if (sample) {

// WHERE
if (where.length) {
const clauses = where.map(String).filter(x => x).join(' AND ');
if (clauses) sql.push(`WHERE ${clauses}`);
}
// GROUP BY

@@ -340,0 +340,0 @@ if (groupby.length) {

65

src/ref.js

@@ -0,2 +1,10 @@

/**
* Class representing a table and/or column reference.
*/
export class Ref {
/**
* Create a new Ref instance.
* @param {string|Ref|null} table The table name.
* @param {string|null} column The column name.
*/
constructor(table, column) {

@@ -7,2 +15,6 @@ if (table) this.table = String(table);

/**
* Get the list of referenced columns. Either a single element array
* if column is non-null, otherwise an empty array.
*/
get columns() {

@@ -12,9 +24,13 @@ return this.column ? [this.column] : [];

/**
* Generate a SQL string for this reference.
* @returns {string} The SQL string.
*/
toString() {
const { table, column } = this;
if (column) {
const col = column === '*' ? column : `"${column}"`;
return (table ? `"${table}".` : '') + col;
const col = column.startsWith('*') ? column : `"${column}"`;
return `${table ? `"${table}".` : ''}${col}`;
} else {
return `"${table}"`;
return table ? `"${table}"` : 'NULL';
}

@@ -24,2 +40,9 @@ }

/**
* Test is a reference refers to a given column name.
* @param {*} ref The reference to test.
* @param {string} name The column name to check for.
* @returns {boolean} True if ref is a Ref instance that refers to
* the given column name. False otherwise.
*/
export function isColumnRefFor(ref, name) {

@@ -29,2 +52,8 @@ return ref instanceof Ref && ref.column === name;

/**
* Interpret a value, defaulting to a column reference.
* @param {*} value The value to interpret. If string-typed,
* a new column reference will be returned.
* @returns {*} A column reference or the input value.
*/
export function asColumn(value) {

@@ -34,2 +63,8 @@ return typeof value === 'string' ? column(value) : value;

/**
* Interpret a value, defaulting to a table (relation) reference.
* @param {*} value The value to interpret. If string-typed,
* a new table (relation) reference will be returned.
* @returns {*} A table reference or the input value.
*/
export function asRelation(value) {

@@ -39,2 +74,7 @@ return typeof value === 'string' ? relation(value) : value;

/**
* Create a table (relation) reference.
* @param {string} name The table (relation) name.
* @returns {Ref} The generated table reference.
*/
export function relation(name) {

@@ -44,10 +84,23 @@ return new Ref(name);

/**
* Create a column reference.
* @param {string} [table] The table name (optional).
* @param {string} column The column name.
* @returns {Ref} The generated column reference.
*/
export function column(table, column) {
return arguments.length === 1
? new Ref(null, table)
: new Ref(table, column);
if (arguments.length === 1) {
column = table;
table = null;
}
return new Ref(table, column);
}
/**
* Create a reference to all columns in a table (relation).
* @param {string} table The table name.
* @returns {Ref} The generated reference.
*/
export function all(table) {
return new Ref(table, '*');
}

31

src/to-sql.js

@@ -0,1 +1,8 @@

/**
* Convert a value to a corresponding SQL string.
* Input string values are assumed to be column references,
* otherwise the logic of literalToSQL applies.
* @param {*} value The value to convert to SQL.
* @returns {string} A SQL string.
*/
export function toSQL(value) {

@@ -7,2 +14,13 @@ return typeof value === 'string'

/**
* Convert a literal value to a corresponding SQL string.
* The values null, undefined, Infinity, NaN, and invalid
* dates are converted to SQL NULL values.
* UTC dates map to the SQL Date type, otherwise JavaScript
* date values map to the SQL Timestamp type.
* Values that are not JavaScript Date, RegExp, or primitive types
* are coerced to strings, relying on a defined toString method.
* @param {*} value The literal value.
* @returns {string} A SQL string.
*/
export function literalToSQL(value) {

@@ -14,2 +32,4 @@ switch (typeof value) {

return `'${value}'`;
case 'number':
return Number.isFinite(value) ? String(value) : 'NULL';
default:

@@ -19,7 +39,14 @@ if (value == null) {

} else if (value instanceof Date) {
// TODO: date vs. timestamp
return `MAKE_DATE(${value.getUTCFullYear()}, ${value.getUTCMonth()+1}, ${value.getUTCDate()})`;
const ts = +value;
if (Number.isNaN(ts)) return 'NULL';
const y = value.getUTCFullYear();
const m = value.getUTCMonth();
const d = value.getUTCDate();
return ts === Date.UTC(y, m, d)
? `MAKE_DATE(${y}, ${m+1}, ${d})` // utc date
: `EPOCH_MS(${ts})`; // timestamp
} else if (value instanceof RegExp) {
return `'${value.source}'`;
} else {
// otherwise rely on string coercion
return String(value);

@@ -26,0 +53,0 @@ }

src/aggregate.js
src/compare.js
src/function-call.js
src/list.js
src/sql-tag.js
SocketSocket SOC 2 Logo

Product

  • Package Alerts
  • Integrations
  • Docs
  • Pricing
  • FAQ
  • Roadmap
  • Changelog

About

Packages

npm

Go

Maven

PyPI

Rubygems

Stay in touch

Get open source security insights delivered straight into your inbox.

  • Terms
  • Privacy
  • Security

Made with ⚡️ by Socket Inc