Socket
Socket
Sign inDemoInstall

sqljson-query

Package Overview
Dependencies
Maintainers
1
Versions
98
Alerts
File Explorer

Advanced tools

License
Socket logo

Install Socket

Detect and block malicious and high-risk dependencies

Install

sqljson-query - npm Package Compare versions

Comparing version 1.9.1 to 1.9.2

11

lib/query-specs.d.ts

@@ -16,3 +16,3 @@ import { Nullable } from './util/mod';

resultRepresentations?: Nullable<ResultRepr[]>;
additionalObjectPropertyColumns?: Nullable<AdditionalObjectPropertyColumn[]>;
additionalOutputColumns?: Nullable<AdditionalOutputColumn[]>;
generateResultTypes?: Nullable<boolean>;

@@ -24,2 +24,3 @@ generateSource?: Nullable<boolean>;

}
export type AdditionalOutputColumn = string | TableFieldExpr;
export type QueryTypesFileHeader = string | {

@@ -69,3 +70,3 @@ [l in SourceLanguage]: string;

referenceName: undefined;
subqueryAlias?: Nullable<string>;
fromEntryAlias?: Nullable<string>;
}

@@ -96,6 +97,2 @@ export interface ChildSpec extends TableJsonSpec {

export type ResultRepr = "MULTI_COLUMN_ROWS" | "JSON_OBJECT_ROWS" | "JSON_ARRAY_ROW";
export type AdditionalObjectPropertyColumn = string | {
property: string;
as: string;
};
export declare function getInlineParentSpecs(tableSpec: TableJsonSpec): InlineParentSpec[];

@@ -119,3 +116,3 @@ export declare function getReferencedParentSpecs(tableSpec: TableJsonSpec): ReferencedParentSpec[];

defaultSchema: Nullable<string>, dbmd: DatabaseMetadata, specLoc: SpecLocation): RelId;
export declare function verifyTableFieldExpressionsValid(tableSpec: TableJsonSpec, defaultSchema: Nullable<string>, dbmd: DatabaseMetadata, specLoc: SpecLocation): void;
export declare function verifyTableFieldExpressionsValid(fieldExpressions: Nullable<(string | TableFieldExpr)[]>, table: string, defaultSchema: Nullable<string>, dbmd: DatabaseMetadata, specLoc: SpecLocation): void;
export declare function validateCustomMatchCondition(customMatchCond: CustomMatchCondition, childRelId: RelId, parentRelId: RelId, dbmd: DatabaseMetadata, specLoc: SpecLocation): void;

11

lib/query-specs.js

@@ -50,7 +50,7 @@ "use strict";

exports.identifyTable = identifyTable;
function verifyTableFieldExpressionsValid(tableSpec, defaultSchema, dbmd, specLoc) {
if (!tableSpec.fieldExpressions)
function verifyTableFieldExpressionsValid(fieldExpressions, table, defaultSchema, dbmd, specLoc) {
if (!fieldExpressions)
return;
const simpleSelectFields = [];
for (const [ix, fieldExpr] of tableSpec.fieldExpressions.entries()) {
for (const [ix, fieldExpr] of fieldExpressions.entries()) {
if (typeof fieldExpr === 'string')

@@ -69,3 +69,3 @@ simpleSelectFields.push(fieldExpr);

}
const relMd = getRelMetadata(tableSpec.table, defaultSchema, dbmd, specLoc);
const relMd = getRelMetadata(table, defaultSchema, dbmd, specLoc);
verifyFieldsExistInRelMd(simpleSelectFields, relMd, dbmd, specLoc);

@@ -93,5 +93,2 @@ }

}
function verifyPropertyColumnsExist(propertyNames, specLoc) {
// TODO
}
function getRelMetadata(table, // as from input, possibly qualified

@@ -98,0 +95,0 @@ defaultSchema, dbmd, specLoc) {

6

lib/sql-generation/sql-dialects/hsql.d.ts

@@ -9,7 +9,7 @@ import { Nullable } from '../../util/nullable';

constructor(indentSpaces: number);
getRowObjectExpression(selectEntries: SelectEntry[], srcAlias: string): string;
getAggregatedRowObjectsExpression(selectEntries: SelectEntry[], orderBy: Nullable<string>, srcAlias: string): string;
getAggregatedColumnValuesExpression(selectEntry: SelectEntry, orderBy: Nullable<string>, srcAlias: string): string;
getRowObjectExpression(selectEntries: SelectEntry[], selectEntryValueSqlFn: (selectEntry: SelectEntry) => string): string;
getAggregatedRowObjectsExpression(selectEntries: SelectEntry[], selectEntryValueSqlFn: (selectEntry: SelectEntry) => string, orderBy: Nullable<string>): string;
getAggregatedColumnValuesExpression(valueExpression: string, orderBy: Nullable<string>): string;
quoteObjectNameIfNeeded(name: string): string;
quoteColumnNameIfNeeded(nameExact: string): string;
}

115

lib/sql-generation/sql-dialects/hsql.js

@@ -5,6 +5,3 @@ "use strict";

const strings_1 = require("../../util/strings");
const simpleIdentifierRegex = new RegExp(/^[A-Za-z][A-Za-z0-9_]+$/);
const sqlKeywordsLowercase = new Set([
'select', 'from', 'where', 'user', 'order', 'group', 'by', 'over', 'is'
]);
const sql_specs_1 = require("../sql-specs");
class HSQLDialect {

@@ -16,6 +13,6 @@ constructor(indentSpaces) {

}
getRowObjectExpression(selectEntries, srcAlias) {
getRowObjectExpression(selectEntries, selectEntryValueSqlFn) {
const objectFieldDecls = selectEntries
.map(se => `'${se.projectedName}' value ${srcAlias}.${this.quoteColumnNameIfNeeded(se.projectedName)}` +
(needsFormatJsonQualifier(se) ? " format json" : ""))
.map(se => `'${se.projectedName}' value ${selectEntryValueSqlFn(se)}` +
(needsFormatJson(se) ? " format json" : ""))
.join(',\n');

@@ -26,13 +23,12 @@ return ('json_object(\n' +

}
getAggregatedRowObjectsExpression(selectEntries, orderBy, srcAlias) {
getAggregatedRowObjectsExpression(selectEntries, selectEntryValueSqlFn, orderBy) {
return ('coalesce(json_arrayagg(' +
this.getRowObjectExpression(selectEntries, srcAlias) +
(orderBy != null ? ' order by ' + orderBy.replace(/\$\$/g, srcAlias) : '') +
this.getRowObjectExpression(selectEntries, selectEntryValueSqlFn) +
(orderBy != null ? ` order by ${orderBy}` : '') +
`), '[]')`);
}
getAggregatedColumnValuesExpression(selectEntry, orderBy, srcAlias) {
const columnName = selectEntry.projectedName;
return (`coalesce(json_arrayagg(${srcAlias}.${this.quoteColumnNameIfNeeded(columnName)}` +
(orderBy != null ? ` order by ${orderBy.replace(/\$\$/g, srcAlias)}` : '') +
`), '[]')`);
getAggregatedColumnValuesExpression(valueExpression, orderBy) {
return (`coalesce(jsonb_agg(${valueExpression}` +
(orderBy != null ? ' order by ' + orderBy : '') +
'))');
}

@@ -44,3 +40,3 @@ quoteObjectNameIfNeeded(name) {

!this.uppercaseNameRegex.test(name) ||
sqlKeywordsLowercase.has(name.toLowerCase()))
sql_specs_1.generalSqlKeywordsLowercase.has(name.toLowerCase()))
return `"${name}"`;

@@ -52,3 +48,5 @@ return name;

return nameExact;
if (!simpleIdentifierRegex.test(nameExact) || !this.uppercaseNameRegex.test(nameExact))
if (!simpleIdentifierRegex.test(nameExact) ||
!this.uppercaseNameRegex.test(nameExact) ||
avoidColumnNamesLowercase.has(nameExact.toLowerCase()))
return `"${nameExact}"`;

@@ -59,3 +57,3 @@ return nameExact;

exports.HSQLDialect = HSQLDialect;
function needsFormatJsonQualifier(selectEntry) {
function needsFormatJson(selectEntry) {
switch (selectEntry.entryType) {

@@ -70,5 +68,84 @@ case "se-parent-ref":

case "se-inline-parent-prop":
return needsFormatJsonQualifier(selectEntry.parentSelectEntry);
return needsFormatJson(selectEntry.parentSelectEntry);
}
}
const simpleIdentifierRegex = new RegExp(/^[A-Za-z][A-Za-z0-9_]+$/);
const avoidColumnNamesLowercase = new Set([
'all',
'and',
'and',
'any',
'as',
'at',
'between',
'both',
'by',
'call',
'case',
'cast',
'coalesce',
'collation',
'convert',
'corresponding',
'create',
'cross',
'cube',
'current',
'current_path',
'default',
'distinct',
'do',
'drop',
'else',
'every',
'except',
'exists',
'fetch',
'for',
'from',
'full',
'grant',
'group',
'grouping',
'having',
'in',
'inner',
'intersect',
'into',
'is',
'join',
'leading',
'left',
'like',
'natural',
'normalize',
'not',
'nullif',
'on',
'or',
'order',
'outer',
'primary',
'references',
'right',
'rollup',
'row',
'select',
'set',
'some',
'sum',
'table',
'then',
'to',
'trailing',
'trigger',
'union',
'unique',
'user',
'using',
'values',
'when',
'where',
'with',
]);
//# sourceMappingURL=hsql.js.map

6

lib/sql-generation/sql-dialects/mysql.d.ts

@@ -9,7 +9,7 @@ import { Nullable } from '../../util/nullable';

constructor(indentSpaces: number);
getRowObjectExpression(selectEntries: SelectEntry[], srcAlias: string): string;
getAggregatedRowObjectsExpression(selectEntries: SelectEntry[], orderBy: Nullable<string>, srcAlias: string): string;
getAggregatedColumnValuesExpression(selectEntry: SelectEntry, orderBy: Nullable<string>, srcAlias: string): string;
getRowObjectExpression(selectEntries: SelectEntry[], selectEntryValueSqlFn: (selectEntry: SelectEntry) => string): string;
getAggregatedRowObjectsExpression(selectEntries: SelectEntry[], selectEntryValueSqlFn: (selectEntry: SelectEntry) => string, orderBy: Nullable<string>): string;
getAggregatedColumnValuesExpression(valueExpression: string, orderBy: Nullable<string>): string;
quoteObjectNameIfNeeded(name: string): string;
quoteColumnNameIfNeeded(name: string): string;
}

275

lib/sql-generation/sql-dialects/mysql.js

@@ -5,6 +5,3 @@ "use strict";

const strings_1 = require("../../util/strings");
const simpleIdentifierRegex = new RegExp(/^[A-Za-z][A-Za-z0-9_]+$/);
const sqlKeywordsLowercase = new Set([
'select', 'from', 'where', 'user', 'order', 'group', 'by', 'over', 'is'
]);
const sql_specs_1 = require("../sql-specs");
class MySQLDialect {

@@ -16,6 +13,5 @@ constructor(indentSpaces) {

}
getRowObjectExpression(selectEntries, srcAlias) {
const columnNames = selectEntries.map(e => e.projectedName);
const objectFieldDecls = columnNames
.map(colName => `'${colName}', ${srcAlias}.${this.quoteColumnNameIfNeeded(colName)}`)
getRowObjectExpression(selectEntries, selectEntryValueSqlFn) {
const objectFieldDecls = selectEntries
.map(se => `'${se.projectedName}', ${selectEntryValueSqlFn(se)}`)
.join(',\n');

@@ -26,14 +22,13 @@ return ('json_object(\n' +

}
getAggregatedRowObjectsExpression(selectEntries, orderBy, srcAlias) {
getAggregatedRowObjectsExpression(selectEntries, selectEntryValueSqlFn, orderBy) {
if (orderBy != null)
throw new Error(`MySQL dialect does not support ordering in aggregate functions currently.`);
return ('cast(coalesce(json_arrayagg(' +
this.getRowObjectExpression(selectEntries, srcAlias) +
this.getRowObjectExpression(selectEntries, selectEntryValueSqlFn) +
`), json_type('[]')) as json)`);
}
getAggregatedColumnValuesExpression(selectEntry, orderBy, srcAlias) {
const columnName = selectEntry.projectedName;
getAggregatedColumnValuesExpression(valueExpression, orderBy) {
if (orderBy != null)
throw new Error(`Error for column ${columnName}: MySQL dialect does not support ordering in aggregate functions currently.`);
return (`cast(coalesce(json_arrayagg(${srcAlias}.${this.quoteColumnNameIfNeeded(columnName)}), json_type('[]')) as json)`);
throw new Error(`MySQL dialect does not support ordering in aggregate functions currently.`);
return (`cast(coalesce(json_arrayagg(${valueExpression}), json_type('[]')) as json)`);
}

@@ -45,3 +40,4 @@ quoteObjectNameIfNeeded(name) {

return `\`${(0, strings_1.unDoubleQuote)(name)}\``;
if (!simpleIdentifierRegex.test(name) || sqlKeywordsLowercase.has(name.toLowerCase()))
if (!simpleIdentifierRegex.test(name) ||
sql_specs_1.generalSqlKeywordsLowercase.has(name.toLowerCase()))
return `\`${name}\``;

@@ -55,3 +51,4 @@ return name;

return `\`${(0, strings_1.unDoubleQuote)(name)}\``;
if (!simpleIdentifierRegex.test(name))
if (!simpleIdentifierRegex.test(name) ||
avoidColumnNamesLowercase.has(name.toLowerCase()))
return `\`${name}\``;

@@ -62,2 +59,248 @@ return name;

exports.MySQLDialect = MySQLDialect;
const simpleIdentifierRegex = new RegExp(/^[A-Za-z][A-Za-z0-9_]+$/);
const avoidColumnNamesLowercase = new Set([
'add',
'all',
'alter',
'analyze',
'and',
'as',
'asc',
'asensitive',
'before',
'between',
'bigint',
'binary',
'blob',
'both',
'by',
'call',
'cascade',
'case',
'change',
'char',
'character',
'check',
'collate',
'column',
'condition',
'constraint',
'continue',
'convert',
'create',
'cross',
'cube',
'cume_dist',
'current_date',
'current_time',
'current_timestamp',
'current_user',
'cursor',
'database',
'databases',
'day_hour',
'day_microsecond',
'day_minute',
'day_second',
'dec',
'decimal',
'declare',
'default',
'delayed',
'delete',
'dense_rank',
'desc',
'describe',
'deterministic',
'distinct',
'distinctrow',
'div',
'double',
'drop',
'dual',
'each',
'else',
'elseif',
'enclosed',
'escaped',
'except',
'exists',
'exit',
'explain',
'false',
'fetch',
'float',
'float4',
'float8',
'for',
'force',
'foreign',
'from',
'fulltext',
'function',
'generated',
'get',
'grant',
'group',
'grouping',
'having',
'high_priority',
'hour_microsecond',
'hour_minute',
'hour_second',
'if',
'ignore',
'in',
'index',
'infile',
'inner',
'inout',
'insensitive',
'insert',
'int',
'int1',
'int2',
'int3',
'int4',
'int8',
'integer',
'interval',
'into',
'is',
'iterate',
'join',
'key',
'keys',
'kill',
'lateral',
'lead',
'leading',
'leave',
'left',
'like',
'limit',
'lines',
'load',
'localtime',
'localtimestamp',
'lock',
'long',
'longblob',
'longtext',
'loop',
'low_priority',
'match',
'mediumblob',
'mediumint',
'mediumtext',
'middleint',
'minute_microsecond',
'minute_second',
'mod',
'modifies',
'natural',
'no_write_to_binlog',
'not',
'null',
'numeric',
'of',
'on',
'optimize',
'option',
'optionally',
'or',
'order',
'out',
'outer',
'outfile',
'over',
'partition',
'percent_rank',
'precision',
'primary',
'procedure',
'purge',
'range',
'rank',
'read',
'reads',
'real',
'recursive',
'references',
'regexp',
'release',
'rename',
'repeat',
'replace',
'require',
'require',
'resignal',
'restrict',
'return',
'revoke',
'right',
'rlike',
'row',
'row_number',
'rows',
'schema',
'schemas',
'second_microsecond',
'select',
'sensitive',
'separator',
'set',
'signal',
'show',
'smallint',
'spatial',
'specific',
'sql',
'sql_big_result',
'sql_calc_found_rows',
'sql_small_result',
'sqlexception',
'sqlstate',
'sqlwarning',
'ssl',
'starting',
'straight_join',
'system',
'table',
'terminated',
'then',
'tinyblob',
'tinyint',
'tinytext',
'to',
'trailing',
'trigger',
'true',
'undo',
'union',
'unique',
'unlock',
'unsigned',
'update',
'usage',
'use',
'using',
'utc_date',
'utc_time',
'utc_timestamp',
'values',
'varbinary',
'varchar',
'varcharacter',
'varying',
'when',
'where',
'while',
'window',
'with',
'write',
'xor',
'year_month',
'zerofill'
]);
//# sourceMappingURL=mysql.js.map

8

lib/sql-generation/sql-dialects/ora.d.ts

@@ -9,7 +9,7 @@ import { Nullable } from '../../util/nullable';

constructor(indentSpaces: number);
getRowObjectExpression(selectEntries: SelectEntry[], srcAlias: string): string;
getAggregatedRowObjectsExpression(selectEntries: SelectEntry[], orderBy: Nullable<string>, srcAlias: string): string;
getAggregatedColumnValuesExpression(selectEntry: SelectEntry, orderBy: Nullable<string>, srcAlias: string): string;
getRowObjectExpression(selectEntries: SelectEntry[], selectEntryValueSqlFn: (selectEntry: SelectEntry) => string): string;
getAggregatedRowObjectsExpression(selectEntries: SelectEntry[], selectEntryValueSqlFn: (selectEntry: SelectEntry) => string, orderBy: Nullable<string>): string;
getAggregatedColumnValuesExpression(valueExpression: string, orderBy: Nullable<string>): string;
quoteObjectNameIfNeeded(name: string): string;
quoteColumnNameIfNeeded(nameExact: string): string;
quoteColumnNameIfNeeded(name: string): string;
}

152

lib/sql-generation/sql-dialects/ora.js

@@ -5,6 +5,3 @@ "use strict";

const strings_1 = require("../../util/strings");
const simpleIdentifierRegex = new RegExp(/^[A-Za-z][A-Za-z0-9_]+$/);
const sqlKeywordsLowercase = new Set([
'select', 'from', 'where', 'user', 'order', 'group', 'by', 'over', 'is'
]);
const sql_specs_1 = require("../sql-specs");
class OracleDialect {

@@ -16,6 +13,5 @@ constructor(indentSpaces) {

}
getRowObjectExpression(selectEntries, srcAlias) {
const columnNames = selectEntries.map(e => e.projectedName);
const objectFieldDecls = columnNames
.map(colName => `'${colName}' value ${srcAlias}.${this.quoteColumnNameIfNeeded(colName)}`)
getRowObjectExpression(selectEntries, selectEntryValueSqlFn) {
const objectFieldDecls = selectEntries
.map(se => `'${se.projectedName}' value ${selectEntryValueSqlFn(se)}`)
.join(',\n');

@@ -26,12 +22,11 @@ return ('json_object(\n' +

}
getAggregatedRowObjectsExpression(selectEntries, orderBy, srcAlias) {
getAggregatedRowObjectsExpression(selectEntries, selectEntryValueSqlFn, orderBy) {
return ('treat(coalesce(json_arrayagg(' +
this.getRowObjectExpression(selectEntries, srcAlias) +
(orderBy != null ? ' order by ' + orderBy.replace(/\$\$/g, srcAlias) : '') +
this.getRowObjectExpression(selectEntries, selectEntryValueSqlFn) +
(orderBy != null ? ` order by ${orderBy}` : '') +
` returning clob), to_clob('[]')) as json)`);
}
getAggregatedColumnValuesExpression(selectEntry, orderBy, srcAlias) {
const columnName = selectEntry.projectedName;
return (`treat(coalesce(json_arrayagg(${srcAlias}.${this.quoteColumnNameIfNeeded(columnName)}` +
(orderBy != null ? ` order by ${orderBy.replace(/\$\$/g, srcAlias)}` : '') +
getAggregatedColumnValuesExpression(valueExpression, orderBy) {
return (`treat(coalesce(json_arrayagg(${valueExpression}` +
(orderBy != null ? ` order by ${orderBy}` : '') +
` returning clob), to_clob('[]')) as json)`);

@@ -44,15 +39,128 @@ }

!this.uppercaseNameRegex.test(name) ||
sqlKeywordsLowercase.has(name.toLowerCase()))
sql_specs_1.generalSqlKeywordsLowercase.has(name.toLowerCase()))
return `"${name}"`;
return name;
}
quoteColumnNameIfNeeded(nameExact) {
if (this.quotedStringRegex.test(nameExact))
return nameExact;
if (!simpleIdentifierRegex.test(nameExact) || !this.uppercaseNameRegex.test(nameExact))
return `"${nameExact}"`;
return nameExact;
quoteColumnNameIfNeeded(name) {
if (this.quotedStringRegex.test(name))
return name;
if (!simpleIdentifierRegex.test(name) ||
!this.uppercaseNameRegex.test(name) ||
avoidColumnNamesLowercase.has(name.toLowerCase()))
return `"${name}"`;
return name;
}
}
exports.OracleDialect = OracleDialect;
const simpleIdentifierRegex = new RegExp(/^[A-Za-z][A-Za-z0-9_]+$/);
const avoidColumnNamesLowercase = new Set([
'access',
'add',
'all',
'alter',
'and',
'any',
'as',
'asc',
'audit',
'between',
'by',
'char',
'check',
'cluster',
'column',
'comment',
'compress',
'connect',
'create',
'current',
'date',
'decimal',
'default',
'delete',
'desc',
'distinct',
'drop',
'else',
'exclusive',
'exists',
'file',
'float',
'for',
'from',
'grant',
'group',
'having',
'identified',
'immediate',
'in',
'increment',
'index',
'initial',
'insert',
'integer',
'intersect',
'into',
'is',
'level',
'like',
'lock',
'long',
'maxextents',
'minus',
'mlslabel',
'mode',
'modify',
'noaudit',
'nocompress',
'not',
'nowait',
'null',
'number',
'of',
'offline',
'on',
'online',
'option',
'or',
'order',
'pctfree',
'prior',
'public',
'raw',
'rename',
'resource',
'revoke',
'row',
'rowid',
'rownum',
'rows',
'select',
'session',
'set',
'share',
'size',
'smallint',
'start',
'successful',
'synonym',
'sysdate',
'table',
'then',
'to',
'trigger',
'uid',
'union',
'unique',
'update',
'user',
'validate',
'values',
'varchar',
'varchar2',
'view',
'whenever',
'where',
'with',
]);
//# sourceMappingURL=ora.js.map

6

lib/sql-generation/sql-dialects/pg.d.ts

@@ -9,7 +9,7 @@ import { Nullable } from '../../util/nullable';

constructor(indentSpaces: number);
getRowObjectExpression(selectEntries: SelectEntry[], srcAlias: string): string;
getAggregatedRowObjectsExpression(selectEntries: SelectEntry[], orderBy: Nullable<string>, srcAlias: string): string;
getAggregatedColumnValuesExpression(selectEntry: SelectEntry, orderBy: Nullable<string>, srcAlias: string): string;
getRowObjectExpression(selectEntries: SelectEntry[], selectEntryValueSqlFn: (selectEntry: SelectEntry) => string): string;
getAggregatedRowObjectsExpression(selectEntries: SelectEntry[], selectEntryValueSqlFn: (selectEntry: SelectEntry) => string, orderBy: Nullable<string>): string;
getAggregatedColumnValuesExpression(valueExpression: string, orderBy: Nullable<string>): string;
quoteObjectNameIfNeeded(name: string): string;
quoteColumnNameIfNeeded(name: string): string;
}

127

lib/sql-generation/sql-dialects/pg.js

@@ -5,6 +5,3 @@ "use strict";

const strings_1 = require("../../util/strings");
const simpleIdentifierRegex = new RegExp(/^[A-Za-z][A-Za-z0-9_]+$/);
const sqlKeywordsLowercase = new Set([
'select', 'from', 'where', 'user', 'order', 'group', 'by', 'over', 'is'
]);
const sql_specs_1 = require("../sql-specs");
class PostgresDialect {

@@ -16,6 +13,5 @@ constructor(indentSpaces) {

}
getRowObjectExpression(selectEntries, srcAlias) {
const columnNames = selectEntries.map(e => e.projectedName);
const objectFieldDecls = columnNames
.map(colName => `'${colName}', ${srcAlias}.${this.quoteColumnNameIfNeeded(colName)}`)
getRowObjectExpression(selectEntries, selectEntryValueSqlFn) {
const objectFieldDecls = selectEntries
.map(se => `'${se.projectedName}', ${selectEntryValueSqlFn(se)}`)
.join(',\n');

@@ -26,12 +22,11 @@ return ('jsonb_build_object(\n' +

}
getAggregatedRowObjectsExpression(selectEntries, orderBy, srcAlias) {
getAggregatedRowObjectsExpression(selectEntries, selectEntryValueSqlFn, orderBy) {
return ('coalesce(jsonb_agg(' +
this.getRowObjectExpression(selectEntries, srcAlias) +
(orderBy != null ? ` order by ${orderBy.replace(/\$\$/g, srcAlias)}` : '') +
this.getRowObjectExpression(selectEntries, selectEntryValueSqlFn) +
(orderBy != null ? ` order by ${orderBy}` : '') +
`),'[]'::jsonb)`);
}
getAggregatedColumnValuesExpression(selectEntry, orderBy, srcAlias) {
const columnName = selectEntry.projectedName;
return (`coalesce(jsonb_agg(${srcAlias}.${this.quoteColumnNameIfNeeded(columnName)}` +
(orderBy != null ? ' order by ' + orderBy.replace(/\$\$/g, srcAlias) : '') +
getAggregatedColumnValuesExpression(valueExpression, orderBy) {
return (`coalesce(jsonb_agg(${valueExpression}` +
(orderBy != null ? ' order by ' + orderBy : '') +
'))');

@@ -44,3 +39,3 @@ }

!this.lowercaseNameRegex.test(name) ||
sqlKeywordsLowercase.has(name))
sql_specs_1.generalSqlKeywordsLowercase.has(name.toLowerCase()))
return `"${name}"`;

@@ -52,3 +47,5 @@ return name;

return name;
if (!simpleIdentifierRegex.test(name) || !this.lowercaseNameRegex.test(name))
if (!simpleIdentifierRegex.test(name) ||
!this.lowercaseNameRegex.test(name) ||
avoidColumnNamesLowercase.has(name.toLowerCase()))
return `"${name}"`;

@@ -59,2 +56,98 @@ return name;

exports.PostgresDialect = PostgresDialect;
const simpleIdentifierRegex = new RegExp(/^[A-Za-z][A-Za-z0-9_]+$/);
const avoidColumnNamesLowercase = new Set([
'all',
'analyze',
'and',
'any',
'array',
'as',
'asymmetric',
'authorization',
'binary',
'both',
'case',
'cast',
'check',
'collate',
'collation',
'column',
'constraint',
'create',
'cross',
'current_date',
'current_role',
'current_time',
'current_timestamp',
'current_user',
'default',
'deferrable',
'desc',
'distinct',
'do',
'else',
'end',
'except',
'false',
'fetch',
'for',
'foreign',
'freeze',
'from',
'full',
'grant',
'group',
'having',
'ilike',
'in',
'initially',
'inner',
'intersect',
'interval',
'into',
'is',
'isnull',
'join',
'lateral',
'leading',
'left',
'like',
'limit',
'localtime',
'localtimestamp',
'natural',
'not',
'notnull',
'null',
'offset',
'on',
'only',
'or',
'order',
'outer',
'overlaps',
'placing',
'primary',
'references',
'right',
'select',
'session_user',
'similar',
'some',
'symmetric',
'table',
'tablesample',
'then',
'to',
'trailing',
'true',
'union',
'unique',
'user',
'using',
'verbose',
'when',
'where',
'with'
]);
//# sourceMappingURL=pg.js.map

6

lib/sql-generation/sql-dialects/sql-dialect.d.ts
import { Nullable } from '../../util/mod';
import { SelectEntry } from "../sql-specs";
export interface SqlDialect {
getRowObjectExpression(selectEntries: SelectEntry[], srcAlias: string): string;
getAggregatedRowObjectsExpression(selectEntries: SelectEntry[], orderBy: Nullable<string>, srcAlias: string): string;
getAggregatedColumnValuesExpression(selectEntry: SelectEntry, orderBy: Nullable<string>, srcAlias: string): string;
getRowObjectExpression(selectEntries: SelectEntry[], selectEntryValueSqlFn: (selectEntry: SelectEntry) => string): string;
getAggregatedRowObjectsExpression(selectEntries: SelectEntry[], selectEntryValueSqlFn: (selectEntry: SelectEntry) => string, orderBy: Nullable<string>): string;
getAggregatedColumnValuesExpression(valueExpression: string, orderBy: Nullable<string>): string;
quoteObjectNameIfNeeded(name: string): string;

@@ -8,0 +8,0 @@ quoteColumnNameIfNeeded(name: string): string;

7

lib/sql-generation/sql-source-generator.d.ts

@@ -11,7 +11,3 @@ import { CaseSensitivity } from "../dbmd";

makeSql(spec: SqlSpec): string;
private baseSql;
private makeSelectEntriesSql;
private jsonRowObjectsSql;
private additionalPropertyColumnSql;
private aggregateSql;
private selectEntriesSql;
private selectEntrySql;

@@ -23,3 +19,2 @@ private fromEntrySql;

private indent;
private maybeQuoteColumn;
}

160

lib/sql-generation/sql-source-generator.js

@@ -16,36 +16,28 @@ "use strict";

makeSql(spec) {
var _a, _b;
const baseSql = this.baseSql(spec);
const wrapPropsInObj = (_a = spec.objectWrapProperties) !== null && _a !== void 0 ? _a : false;
if (!spec.aggregateToArray && !wrapPropsInObj) // no aggregation nor object wrapping
{
return baseSql;
}
else // at least one of aggregation and object wrapping of properties will be done
{
const propSelectEntries = (0, sql_specs_1.getPropertySelectEntries)(spec);
const baseTable = baseTableDescn(spec); // only used for comments
if (!spec.aggregateToArray) // no aggregation but do object wrapping
{
const additionalCols = (_b = spec.additionalObjectPropertyColumns) !== null && _b !== void 0 ? _b : [];
return this.jsonRowObjectsSql(baseSql, propSelectEntries, additionalCols, spec.orderBy, baseTable);
}
else // aggregation and maybe object wrapping
{
return this.aggregateSql(baseSql, propSelectEntries, wrapPropsInObj, spec.orderBy, baseTable);
}
}
}
baseSql(spec) {
var _a, _b, _c;
const propSelEntries = (0, sql_specs_1.getPropertySelectEntries)(spec);
const objWrap = (_a = spec.objectWrapProperties) !== null && _a !== void 0 ? _a : false;
const arrayAgg = (_b = spec.aggregateToArray) !== null && _b !== void 0 ? _b : false;
const addSelEntries = (_c = spec.additionalOutputSelectEntries) !== null && _c !== void 0 ? _c : [];
const selectComment = this.genComments && spec.selectEntriesLeadingComment ?
`-- ${spec.selectEntriesLeadingComment}\n` : '';
const selectEntries = this.makeSelectEntriesSql(spec.selectEntries) + '\n';
const selEntryValFn = (se) => this.selectEntrySql(se, true);
const selectEntries = arrayAgg ?
(objWrap
? this.sqlDialect.getAggregatedRowObjectsExpression(propSelEntries, selEntryValFn, spec.aggregateOrderBy)
: this.sqlDialect.getAggregatedColumnValuesExpression(this.selectEntrySql(propSelEntries[0], true), spec.aggregateOrderBy)) + ' json\n'
: objWrap ?
this.sqlDialect.getRowObjectExpression(propSelEntries, selEntryValFn) + ' json' +
((0, collections_1.nonEmpty)(addSelEntries)
? `,\n${addSelEntries.map(c => this.selectEntrySql(c)).join(',\n')}`
: '') + "\n"
: this.selectEntriesSql(spec.selectEntries) + "\n";
const fromComment = this.genComments && spec.fromEntriesLeadingComment ?
`-- ${spec.fromEntriesLeadingComment}\n` : '';
const fromEntries = spec.fromEntries.map(e => this.fromEntrySql(e)).join('\n') + '\n';
const whereClause = (0, collections_1.isNonEmpty)(spec.whereEntries)
? `where (\n${this.indent(spec.whereEntries.map(e => this.whereEntrySql(e)).join(' and\n'))}\n)\n`
const whereClause = (0, collections_1.nonEmpty)(spec.whereEntries) ?
`where (\n${this.indent(spec.whereEntries.map(e => this.whereEntrySql(e)).join(' and\n'))}\n)\n`
: '';
const orderByClause = spec.orderBy
? 'order by ' + spec.orderBy.orderBy.replace(/\$\$/g, spec.orderBy.tableAlias) + '\n'
const orderByClause = spec.orderBy ?
'order by ' + spec.orderBy.orderBy.replace(/\$\$/g, spec.orderBy.tableAlias) + '\n'
: '';

@@ -57,3 +49,3 @@ return ('select\n' + this.indent(selectComment + selectEntries) +

}
makeSelectEntriesSql(specSelectEntries) {
selectEntriesSql(specSelectEntries) {
// Assign any missing displayOrders based on select entry position.

@@ -68,46 +60,29 @@ const selectEntries = specSelectEntries.map((entry, ix) => {

const sortedSelectEntries = (0, collections_1.sorted)(selectEntries, (e1, e2) => { var _a, _b; return ((_a = e1.displayOrder) !== null && _a !== void 0 ? _a : 0) - ((_b = e2.displayOrder) !== null && _b !== void 0 ? _b : 0); });
return sortedSelectEntries.map(e => this.selectEntrySql(e)).join(',\n');
return sortedSelectEntries.map(se => (this.genComments && se.comment ? `-- ${se.comment}\n` : '') +
this.selectEntrySql(se)).join(',\n');
}
jsonRowObjectsSql(baseSql, selectEntries, additionalColumns, orderBy, baseTableDesc) {
return ('select\n' +
this.indent((baseTableDesc ? `-- row object for table '${baseTableDesc}'\n` : '') +
this.sqlDialect.getRowObjectExpression(selectEntries, 'q') + ' json' +
((0, collections_1.isNonEmpty)(additionalColumns)
? `,\n${additionalColumns.map(c => this.additionalPropertyColumnSql(c)).join(',\n')}`
: '')) + '\n' +
'from (\n' +
nlterm(this.indent((baseTableDesc ? `-- base query for table '${baseTableDesc}'\n` : '') +
baseSql)) +
') q' +
(orderBy != null ? '\norder by ' + orderBy.orderBy.replace(/\$\$/g, 'q') : ''));
}
additionalPropertyColumnSql(c) {
if (typeof (c) === 'string')
return this.sqlDialect.quoteColumnNameIfNeeded(c);
const propNameExpr = this.sqlDialect.quoteColumnNameIfNeeded(c.property);
const alias = this.sqlDialect.quoteColumnNameIfNeeded(c.as);
return `${propNameExpr} as ${alias}`;
}
aggregateSql(sql, selectEntries, wrapProps, orderBy, baseTableDesc) {
const ordby = orderBy === null || orderBy === void 0 ? void 0 : orderBy.orderBy;
return ('select\n' +
this.indent((this.genComments ? `-- aggregated ${wrapProps ? 'rows' : 'values'} from table '${baseTableDesc}'\n` : '') +
(wrapProps
? this.sqlDialect.getAggregatedRowObjectsExpression(selectEntries, ordby, 'q')
: this.sqlDialect.getAggregatedColumnValuesExpression(selectEntries[0], ordby, 'q')) + ' json\n') +
'from (\n' +
nlterm(this.indent((this.genComments ? `-- base query for table '${baseTableDesc}'\n` : '') +
sql)) +
') q');
}
selectEntrySql(selectEntry) {
const projectedName = this.maybeQuoteColumn(selectEntry.projectedName);
const isProjectedNameQuoted = projectedName !== selectEntry.projectedName;
selectEntrySql(selectEntry, valueOnly = false) {
switch (selectEntry.entryType) {
case 'se-field':
{
const fieldName = this.maybeQuoteColumn(selectEntry.field.name);
const fieldName = this.sqlDialect.quoteColumnNameIfNeeded(selectEntry.field.name);
const valueSql = `${selectEntry.tableAlias}.${fieldName}`;
if (valueOnly)
return valueSql;
const projectedName = this.sqlDialect.quoteColumnNameIfNeeded(selectEntry.projectedName);
const isProjectedNameQuoted = projectedName !== selectEntry.projectedName;
const sep = isProjectedNameQuoted ? ' ' : ' as ';
return `${selectEntry.tableAlias}.${fieldName}${sep}${projectedName}`;
return `${valueSql}${sep}${projectedName}`;
}
case 'se-hidden-pkf':
{
const fieldName = this.sqlDialect.quoteColumnNameIfNeeded(selectEntry.pkFieldName);
const valueSql = `${selectEntry.tableAlias}.${fieldName}`;
if (valueOnly)
return valueSql;
const isFieldNameQuoted = fieldName !== selectEntry.pkFieldName;
const sep = isFieldNameQuoted ? ' ' : ' as ';
const exportedName = this.sqlDialect.quoteColumnNameIfNeeded(selectEntry.projectedName);
return `${valueSql}${sep}${exportedName}`;
}
case 'se-expr':

@@ -117,2 +92,6 @@ {

const expr = (0, strings_1.replaceAll)(selectEntry.expression, tableAliasPlaceholder, selectEntry.tableAlias);
if (valueOnly)
return expr;
const projectedName = this.sqlDialect.quoteColumnNameIfNeeded(selectEntry.projectedName);
const isProjectedNameQuoted = projectedName !== selectEntry.projectedName;
const sep = isProjectedNameQuoted ? ' ' : ' as ';

@@ -123,4 +102,4 @@ return `${expr}${sep}${projectedName}`;

{
return (this.genComments && selectEntry.comment ? `-- ${selectEntry.comment}\n` : '') +
`${selectEntry.parentAlias}.${projectedName}`;
const projectedName = this.sqlDialect.quoteColumnNameIfNeeded(selectEntry.projectedName);
return `${selectEntry.parentAlias}.${projectedName}`;
}

@@ -130,6 +109,7 @@ case 'se-parent-ref':

const parentRowObjSql = this.makeSql(selectEntry.parentRowObjectSql);
return (this.genComments && selectEntry.comment ? `-- ${selectEntry.comment}\n` : '') +
'(\n' +
this.indent(parentRowObjSql) + '\n' +
`) ${projectedName}`;
const valueSql = '(\n' + this.indent(parentRowObjSql) + ')';
if (valueOnly)
return valueSql;
const projectedName = this.sqlDialect.quoteColumnNameIfNeeded(selectEntry.projectedName);
return `${valueSql} ${projectedName}`;
}

@@ -139,15 +119,8 @@ case 'se-child-coll':

const collectionSql = this.makeSql(selectEntry.collectionSql);
return (this.genComments && selectEntry.comment ? `-- ${selectEntry.comment}\n` : '') +
'(\n' +
this.indent(collectionSql) + '\n' +
`) ${projectedName}`;
const valueSql = '(\n' + this.indent(collectionSql) + ')';
if (valueOnly)
return valueSql;
const projectedName = this.sqlDialect.quoteColumnNameIfNeeded(selectEntry.projectedName);
return `${valueSql} ${projectedName}`;
}
case 'se-hidden-pkf':
{
const fieldName = this.maybeQuoteColumn(selectEntry.pkFieldName);
const isFieldNameQuoted = fieldName !== selectEntry.pkFieldName;
const sep = isFieldNameQuoted ? ' ' : ' as ';
const exportedName = this.maybeQuoteColumn(selectEntry.projectedName);
return `${selectEntry.tableAlias}.${fieldName}${sep}${exportedName}`;
}
}

@@ -190,5 +163,5 @@ }

: [pcCond.parentAlias, pcCond.fromAlias];
return pcCond.matchedFields.map(mf => `${childAlias}.${this.maybeQuoteColumn(mf.foreignKeyFieldName)}` +
return pcCond.matchedFields.map(mf => `${childAlias}.${this.sqlDialect.quoteColumnNameIfNeeded(mf.foreignKeyFieldName)}` +
' = ' +
`${parentAlias}.${this.maybeQuoteColumn(mf.primaryKeyFieldName)}`).join(' and ');
`${parentAlias}.${this.sqlDialect.quoteColumnNameIfNeeded(mf.primaryKeyFieldName)}`).join(' and ');
}

@@ -204,18 +177,5 @@ minimalRelIdentifier(relId) {

}
maybeQuoteColumn(colName) {
return this.sqlDialect.quoteColumnNameIfNeeded(colName);
}
}
exports.SqlSourceGenerator = SqlSourceGenerator;
function baseTableDescn(sqlSpec) {
const firstFromEntry = sqlSpec.fromEntries[0];
if (firstFromEntry.entryType === 'table')
return firstFromEntry.table.name;
return null;
}
// Newline terminate the given string if it doesn't end with a newline.
function nlterm(s) {
return !s.endsWith('\n') ? s + '\n' : s;
}
const DEFAULT_ALIAS_PLACEHOLDER = '$$';
//# sourceMappingURL=sql-source-generator.js.map

64

lib/sql-generation/sql-spec-generator.js

@@ -32,4 +32,4 @@ "use strict";

case 'JSON_OBJECT_ROWS':
const additionalCols = (_a = query.additionalObjectPropertyColumns) !== null && _a !== void 0 ? _a : [];
return this.jsonObjectRowsSql(query.tableJson, additionalCols, null, query.orderBy, specLoc);
const addCols = (_a = query.additionalOutputColumns) !== null && _a !== void 0 ? _a : [];
return this.jsonObjectRowsSql(query.tableJson, addCols, null, query.orderBy, specLoc);
case 'JSON_ARRAY_ROW':

@@ -53,3 +53,3 @@ return this.jsonArrayRowSql(query.tableJson, null, false, query.orderBy, specLoc);

sqlb.addSelectEntries(this.hiddenPrimaryKeySelectEntries(relId, alias));
sqlb.addSelectEntries(this.tableFieldExpressionSelectEntries(tjs, alias, specLoc));
sqlb.addSelectEntries(this.tableFieldExpressionSelectEntries(tjs.fieldExpressions, tjs.table, alias, specLoc));
sqlb.addParts(this.inlineParentsSqlParts(tjs, relId, alias, sqlb.getAliases(), specLoc));

@@ -77,17 +77,10 @@ sqlb.addParts(this.referencedParentsSqlParts(tjs, relId, alias, specLoc));

/// Make query SQL having JSON object result values at the top level of the
/// result set. The query returns a JSON value in a single column and with
/// any number of result rows.
jsonObjectRowsSql(tjs, additionalObjectPropertyColumns, pkCond, orderBy, specLoc) {
/// result set. The query returns a JSON value in a single column for each
/// of any number of result rows.
jsonObjectRowsSql(tjs, addOutputColumns, pkCond, orderBy, specLoc) {
const baseSql = this.baseSql(tjs, pkCond, orderBy, specLoc);
// Additional property column names must exist as projected column names in the base sql.
const propCols = additionalObjectPropertyColumns.map(c => typeof (c) == 'string' ? c : c.property);
const projectedPropNames = new Set((0, sql_specs_1.getPropertySelectEntries)(baseSql).map(prop => prop.projectedName));
const invalidPropCols = propCols.filter(pc => !projectedPropNames.has(pc));
if (invalidPropCols.length > 0)
throw new query_specs_1.SpecError(specLoc, `Extracted property column(s) not found: ${invalidPropCols.join(', ')}.`);
return {
...baseSql,
objectWrapProperties: true,
additionalObjectPropertyColumns
};
(0, query_specs_1.verifyTableFieldExpressionsValid)(addOutputColumns, tjs.table, this.defaultSchema, this.dbmd, specLoc);
const alias = baseSql.fromEntries[0].alias;
const additionalOutputSelectEntries = this.tableFieldExpressionSelectEntries(addOutputColumns, tjs.table, alias, specLoc);
return { ...baseSql, objectWrapProperties: true, additionalOutputSelectEntries };
}

@@ -98,3 +91,3 @@ /// Make query SQL having a single row and column result, with the result value

jsonArrayRowSql(tjs, childFkCond, unwrap, orderBy, specLoc) {
const baseSql = this.baseSql(tjs, childFkCond, orderBy, specLoc);
const baseSql = this.baseSql(tjs, childFkCond, null, specLoc);
if (unwrap && (0, sql_specs_1.getPropertySelectEntries)(baseSql).length != 1)

@@ -105,4 +98,4 @@ throw new query_specs_1.SpecError(specLoc, 'Unwrapped collections cannot have multiple properties.');

aggregateToArray: true,
objectWrapProperties: !unwrap,
fromEntriesLeadingComment: `base query for table '${tjs.table}'`,
aggregateOrderBy: orderBy,
objectWrapProperties: !unwrap
};

@@ -118,9 +111,9 @@ }

}
tableFieldExpressionSelectEntries(tjs, tableAlias, specLoc) {
(0, query_specs_1.verifyTableFieldExpressionsValid)(tjs, this.defaultSchema, this.dbmd, specLoc);
if (!tjs.fieldExpressions)
tableFieldExpressionSelectEntries(fieldExpressions, table, tableAlias, specLoc) {
(0, query_specs_1.verifyTableFieldExpressionsValid)(fieldExpressions, table, this.defaultSchema, this.dbmd, specLoc);
if (!fieldExpressions)
return [];
const dbFieldsByName = this.getTableFieldsByName(tjs.table, specLoc);
return tjs.fieldExpressions.map((tfe, ix) => {
const feLoc = (0, query_specs_1.addLocPart)(specLoc, `fieldExpressions entry #${ix + 1} of table ${tjs.table}`);
const dbFieldsByName = this.getTableFieldsByName(table, specLoc);
return fieldExpressions.map((tfe, ix) => {
const feLoc = (0, query_specs_1.addLocPart)(specLoc, `fieldExpressions entry #${ix + 1} of table ${table}`);
const projectedName = this.jsonPropertyName(tfe, feLoc);

@@ -131,3 +124,3 @@ if (typeof tfe === 'string' || tfe.field != null) {

if (dbField == undefined)
throw new query_specs_1.SpecError(specLoc, `No metadata found for field ${tjs.table}.${fieldName}.`);
throw new query_specs_1.SpecError(specLoc, `No metadata found for field ${table}.${fieldName}.`);
return {

@@ -160,14 +153,13 @@ entryType: 'se-field',

const sqlParts = new sql_specs_1.SqlParts(aliasesInScope);
for (const parent of (0, query_specs_1.getInlineParentSpecs)(tjs)) {
const ipLoc = (0, query_specs_1.addLocPart)(specLoc, `parent table '${parent.table}'`);
sqlParts.addParts(this.inlineParentSqlParts(parent, relId, alias, sqlParts.getAliases(), ipLoc));
for (const parSpec of (0, query_specs_1.getInlineParentSpecs)(tjs)) {
const ipLoc = (0, query_specs_1.addLocPart)(specLoc, `parent table '${parSpec.table}'`);
sqlParts.addParts(this.inlineParentSqlParts(parSpec, relId, alias, sqlParts.getAliases(), ipLoc));
}
return sqlParts;
}
// Return sql parts for the *including* (child) table's SQL query which are contributed by the inline parent table.
inlineParentSqlParts(parentSpec, childRelId, childAlias, avoidAliases, specLoc) {
var _a, _b;
const sqlParts = new sql_specs_1.SqlParts();
const subqueryAlias = parentSpec.subqueryAlias || (0, mod_1.makeNameNotInSet)('q', avoidAliases);
sqlParts.addAlias(subqueryAlias);
const fromEntryAlias = parentSpec.fromEntryAlias || (0, sql_specs_1.createTableAlias)(parentSpec.table, avoidAliases);
sqlParts.addAlias(fromEntryAlias);
const parentPropsSql = this.baseSql(parentSpec, null, null, specLoc, 'export-pk-fields-hidden');

@@ -183,3 +175,3 @@ const matchedFields = this.getParentPrimaryKeyCondition(parentSpec, childRelId, childAlias, specLoc)

query: parentPropsSql,
alias: subqueryAlias,
alias: fromEntryAlias,
join: {

@@ -190,3 +182,3 @@ joinType: 'LEFT',

fromAlias: childAlias,
parentAlias: subqueryAlias,
parentAlias: fromEntryAlias,
matchedFields,

@@ -203,3 +195,3 @@ matchMustExist

projectedName: parentSelectEntry.projectedName,
parentAlias: subqueryAlias,
parentAlias: fromEntryAlias,
parentTable: parentRelId,

@@ -206,0 +198,0 @@ parentSelectEntry: parentSelectEntry,

12

lib/sql-generation/sql-specs.d.ts
import { Field, ForeignKeyComponent, RelId } from "../dbmd";
import { AdditionalObjectPropertyColumn } from "../query-specs";
import { Nullable } from "../util/mod";

@@ -11,4 +10,5 @@ export interface SqlSpec {

objectWrapProperties?: Nullable<boolean>;
additionalObjectPropertyColumns?: Nullable<AdditionalObjectPropertyColumn[]>;
additionalOutputSelectEntries?: Nullable<AdditionalOutputSelectEntry[]>;
aggregateToArray?: Nullable<boolean>;
aggregateOrderBy?: Nullable<string>;
selectEntriesLeadingComment?: Nullable<string>;

@@ -28,2 +28,3 @@ fromEntriesLeadingComment?: Nullable<string>;

}>;
readonly comment?: Nullable<string>;
}

@@ -40,2 +41,3 @@ export interface ExpressionSelectEntry {

};
readonly comment?: Nullable<string>;
}

@@ -71,3 +73,5 @@ export interface InlineParentSelectEntry {

readonly displayOrder?: undefined;
readonly comment?: Nullable<string>;
}
export type AdditionalOutputSelectEntry = FieldSelectEntry | ExpressionSelectEntry;
export type FromEntry = TableFromEntry | QueryFromEntry;

@@ -79,3 +83,3 @@ export interface TableFromEntry {

readonly join?: Nullable<Join>;
readonly comment?: undefined;
readonly comment?: Nullable<string>;
}

@@ -143,1 +147,3 @@ export interface QueryFromEntry {

export declare function getBaseTable(sql: SqlSpec): RelId;
export declare const generalSqlKeywordsLowercase: Set<string>;
export declare function createTableAlias(relName: string, avoid: Set<string>): string;

839

lib/sql-generation/sql-specs.js
"use strict";
Object.defineProperty(exports, "__esModule", { value: true });
exports.getBaseTable = exports.getPropertySelectEntries = exports.SqlParts = void 0;
exports.createTableAlias = exports.generalSqlKeywordsLowercase = exports.getBaseTable = exports.getPropertySelectEntries = exports.SqlParts = void 0;
const collections_1 = require("../util/collections");

@@ -52,3 +52,3 @@ const strings_1 = require("../util/strings");

createTableAlias(relName) {
const alias = (0, strings_1.makeNameNotInSet)((0, strings_1.lowerCaseInitials)(relName, '_') + '_', this.aliases);
const alias = createTableAlias(relName, this.aliases);
this.aliases.add(alias);

@@ -90,2 +90,837 @@ return alias;

exports.getBaseTable = getBaseTable;
exports.generalSqlKeywordsLowercase = new Set([
'a',
'abort',
'abs',
'absolute',
'access',
'action',
'ada',
'add',
'admin',
'after',
'aggregate',
'alias',
'all',
'allocate',
'also',
'alter',
'always',
'analyse',
'analyze',
'and',
'any',
'are',
'array',
'as',
'asc',
'asensitive',
'assertion',
'assignment',
'asymmetric',
'at',
'atomic',
'attribute',
'attributes',
'audit',
'authorization',
'auto_increment',
'avg',
'avg_row_length',
'backup',
'backward',
'before',
'begin',
'bernoulli',
'between',
'bigint',
'binary',
'bit',
'bit_length',
'bitvar',
'blob',
'bool',
'boolean',
'both',
'breadth',
'break',
'browse',
'bulk',
'by',
'c',
'cache',
'call',
'called',
'cardinality',
'cascade',
'cascaded',
'case',
'cast',
'catalog',
'catalog_name',
'ceil',
'ceiling',
'chain',
'change',
'char',
'char_length',
'character',
'character_length',
'character_set_catalog',
'character_set_name',
'character_set_schema',
'characteristics',
'characters',
'check',
'checked',
'checkpoint',
'checksum',
'class',
'class_origin',
'clob',
'close',
'cluster',
'clustered',
'coalesce',
'cobol',
'collate',
'collation',
'collation_catalog',
'collation_name',
'collation_schema',
'collect',
'column',
'column_name',
'columns',
'command_function',
'command_function_code',
'comment',
'commit',
'committed',
'completion',
'compress',
'compute',
'condition',
'condition_number',
'connect',
'connection',
'connection_name',
'constraint',
'constraint_catalog',
'constraint_name',
'constraint_schema',
'constraints',
'constructor',
'contains',
'containstable',
'continue',
'conversion',
'convert',
'copy',
'corr',
'corresponding',
'count',
'covar_pop',
'covar_samp',
'create',
'createdb',
'createrole',
'createuser',
'cross',
'csv',
'cube',
'cume_dist',
'current',
'current_date',
'current_default_transform_group',
'current_path',
'current_role',
'current_time',
'current_timestamp',
'current_transform_group_for_type',
'current_user',
'cursor',
'cursor_name',
'cycle',
'data',
'database',
'databases',
'date',
'datetime',
'datetime_interval_code',
'datetime_interval_precision',
'day',
'day_hour',
'day_microsecond',
'day_minute',
'day_second',
'dayofmonth',
'dayofweek',
'dayofyear',
'dbcc',
'deallocate',
'dec',
'decimal',
'declare',
'default',
'defaults',
'deferrable',
'deferred',
'defined',
'definer',
'degree',
'delay_key_write',
'delayed',
'delete',
'delimiter',
'delimiters',
'dense_rank',
'deny',
'depth',
'deref',
'derived',
'desc',
'describe',
'descriptor',
'destroy',
'destructor',
'deterministic',
'diagnostics',
'dictionary',
'disable',
'disconnect',
'disk',
'dispatch',
'distinct',
'distinctrow',
'distributed',
'div',
'do',
'domain',
'double',
'drop',
'dual',
'dummy',
'dump',
'dynamic',
'dynamic_function',
'dynamic_function_code',
'each',
'element',
'else',
'elseif',
'enable',
'enclosed',
'encoding',
'encrypted',
'end',
'end-exec',
'enum',
'equals',
'errlvl',
'escape',
'escaped',
'every',
'except',
'exception',
'exclude',
'excluding',
'exclusive',
'exec',
'execute',
'existing',
'exists',
'exit',
'exp',
'explain',
'external',
'extract',
'false',
'fetch',
'fields',
'file',
'fillfactor',
'filter',
'final',
'first',
'float',
'float4',
'float8',
'floor',
'flush',
'following',
'for',
'force',
'foreign',
'fortran',
'forward',
'found',
'free',
'freetext',
'freetexttable',
'freeze',
'from',
'full',
'fulltext',
'function',
'fusion',
'g',
'general',
'generated',
'get',
'global',
'go',
'goto',
'grant',
'granted',
'grants',
'greatest',
'group',
'grouping',
'handler',
'having',
'header',
'heap',
'hierarchy',
'high_priority',
'hold',
'holdlock',
'host',
'hosts',
'hour',
'hour_microsecond',
'hour_minute',
'hour_second',
'identified',
'identity',
'identity_insert',
'identitycol',
'if',
'ignore',
'ilike',
'immediate',
'immutable',
'implementation',
'implicit',
'in',
'include',
'including',
'increment',
'index',
'indicator',
'infile',
'infix',
'inherit',
'inherits',
'initial',
'initialize',
'initially',
'inner',
'inout',
'input',
'insensitive',
'insert',
'insert_id',
'instance',
'instantiable',
'instead',
'int',
'int1',
'int2',
'int3',
'int4',
'int8',
'integer',
'intersect',
'intersection',
'interval',
'into',
'invoker',
'is',
'isam',
'isnull',
'isolation',
'iterate',
'join',
'k',
'key',
'key_member',
'key_type',
'keys',
'kill',
'lancompiler',
'language',
'large',
'last',
'last_insert_id',
'lateral',
'lead',
'leading',
'least',
'leave',
'left',
'length',
'less',
'level',
'like',
'limit',
'lineno',
'lines',
'listen',
'ln',
'load',
'local',
'localtime',
'localtimestamp',
'location',
'locator',
'lock',
'login',
'logs',
'long',
'longblob',
'longtext',
'loop',
'low_priority',
'lower',
'm',
'map',
'match',
'matched',
'max',
'max_rows',
'maxextents',
'maxvalue',
'mediumblob',
'mediumint',
'mediumtext',
'member',
'merge',
'message_length',
'message_octet_length',
'message_text',
'method',
'middleint',
'min',
'min_rows',
'minus',
'minute',
'minute_microsecond',
'minute_second',
'minvalue',
'mlslabel',
'mod',
'mode',
'modifies',
'modify',
'module',
'month',
'monthname',
'more',
'move',
'multiset',
'mumps',
'myisam',
'name',
'names',
'national',
'natural',
'nchar',
'nclob',
'nesting',
'new',
'next',
'no',
'no_write_to_binlog',
'noaudit',
'nocheck',
'nocompress',
'nocreatedb',
'nocreaterole',
'nocreateuser',
'noinherit',
'nologin',
'nonclustered',
'none',
'normalize',
'normalized',
'nosuperuser',
'not',
'nothing',
'notify',
'notnull',
'nowait',
'null',
'nullable',
'nullif',
'nulls',
'number',
'numeric',
'object',
'octet_length',
'octets',
'of',
'off',
'offline',
'offset',
'offsets',
'oids',
'old',
'on',
'online',
'only',
'open',
'opendatasource',
'openquery',
'openrowset',
'openxml',
'operation',
'operator',
'optimize',
'option',
'optionally',
'options',
'or',
'order',
'ordering',
'ordinality',
'others',
'out',
'outer',
'outfile',
'output',
'over',
'overlaps',
'overlay',
'overriding',
'owner',
'pack_keys',
'pad',
'parameter',
'parameter_mode',
'parameter_name',
'parameter_ordinal_position',
'parameter_specific_catalog',
'parameter_specific_name',
'parameter_specific_schema',
'parameters',
'partial',
'partition',
'pascal',
'password',
'path',
'pctfree',
'percent',
'percent_rank',
'percentile_cont',
'percentile_disc',
'placing',
'plan',
'pli',
'position',
'postfix',
'power',
'preceding',
'precision',
'prefix',
'preorder',
'prepare',
'prepared',
'preserve',
'primary',
'print',
'prior',
'privileges',
'proc',
'procedural',
'procedure',
'process',
'processlist',
'public',
'purge',
'quote',
'raid0',
'raiserror',
'range',
'rank',
'raw',
'read',
'reads',
'readtext',
'real',
'recheck',
'reconfigure',
'recursive',
'ref',
'references',
'referencing',
'regexp',
'regr_avgx',
'regr_avgy',
'regr_count',
'regr_intercept',
'regr_r2',
'regr_slope',
'regr_sxx',
'regr_sxy',
'regr_syy',
'reindex',
'relative',
'release',
'reload',
'rename',
'repeat',
'repeatable',
'replace',
'replication',
'require',
'reset',
'resignal',
'resource',
'restart',
'restore',
'restrict',
'result',
'return',
'returned_cardinality',
'returned_length',
'returned_octet_length',
'returned_sqlstate',
'returns',
'revoke',
'right',
'rlike',
'role',
'rollback',
'rollup',
'routine',
'routine_catalog',
'routine_name',
'routine_schema',
'row',
'row_count',
'row_number',
'rowcount',
'rowguidcol',
'rowid',
'rownum',
'rows',
'rule',
'save',
'savepoint',
'scale',
'schema',
'schema_name',
'schemas',
'scope',
'scope_catalog',
'scope_name',
'scope_schema',
'scroll',
'search',
'second',
'second_microsecond',
'section',
'security',
'select',
'self',
'sensitive',
'separator',
'sequence',
'serializable',
'server_name',
'session',
'session_user',
'set',
'setof',
'sets',
'setuser',
'share',
'show',
'shutdown',
'signal',
'similar',
'simple',
'size',
'smallint',
'some',
'soname',
'source',
'space',
'spatial',
'specific',
'specific_name',
'specifictype',
'sql',
'sql_big_result',
'sql_big_selects',
'sql_big_tables',
'sql_calc_found_rows',
'sql_log_off',
'sql_log_update',
'sql_low_priority_updates',
'sql_select_limit',
'sql_small_result',
'sql_warnings',
'sqlca',
'sqlcode',
'sqlerror',
'sqlexception',
'sqlstate',
'sqlwarning',
'sqrt',
'ssl',
'stable',
'start',
'starting',
'state',
'statement',
'static',
'statistics',
'status',
'stddev_pop',
'stddev_samp',
'stdin',
'stdout',
'storage',
'straight_join',
'strict',
'string',
'structure',
'style',
'subclass_origin',
'sublist',
'submultiset',
'substring',
'successful',
'sum',
'superuser',
'symmetric',
'synonym',
'sysdate',
'sysid',
'system',
'system_user',
'table',
'table_name',
'tables',
'tablesample',
'tablespace',
'temp',
'template',
'temporary',
'terminate',
'terminated',
'text',
'textsize',
'than',
'then',
'ties',
'time',
'timestamp',
'timezone_hour',
'timezone_minute',
'tinyblob',
'tinyint',
'tinytext',
'to',
'toast',
'top',
'top_level_count',
'trailing',
'tran',
'transaction',
'transaction_active',
'transactions_committed',
'transactions_rolled_back',
'transform',
'transforms',
'translate',
'translation',
'treat',
'trigger',
'trigger_catalog',
'trigger_name',
'trigger_schema',
'trim',
'true',
'truncate',
'trusted',
'tsequal',
'type',
'uescape',
'uid',
'unbounded',
'uncommitted',
'under',
'undo',
'unencrypted',
'union',
'unique',
'unknown',
'unlisten',
'unlock',
'unnamed',
'unnest',
'unsigned',
'until',
'update',
'updatetext',
'upper',
'usage',
'use',
'user',
'user_defined_type_catalog',
'user_defined_type_code',
'user_defined_type_name',
'user_defined_type_schema',
'using',
'utc_date',
'utc_time',
'utc_timestamp',
'vacuum',
'valid',
'validate',
'validator',
'value',
'values',
'var_pop',
'var_samp',
'varbinary',
'varchar',
'varchar2',
'varcharacter',
'variable',
'variables',
'varying',
'verbose',
'view',
'volatile',
'waitfor',
'when',
'whenever',
'where',
'while',
'width_bucket',
'window',
'with',
'within',
'without',
'work',
'write',
'writetext',
'x509',
'xor',
'year',
'year_month',
'zerofill',
'zone',
]);
function createTableAlias(relName, avoid) {
let alias = (0, strings_1.makeNameNotInSet)((0, strings_1.lowerCaseInitials)(relName, '_'), avoid);
if (alias.length > 1 && exports.generalSqlKeywordsLowercase.has(alias.toLowerCase()))
alias = alias + "_";
return alias;
}
exports.createTableAlias = createTableAlias;
//# sourceMappingURL=sql-specs.js.map

2

lib/util/collections.d.ts

@@ -12,2 +12,2 @@ export declare function computeIfAbsent<K, V>(m: Map<K, V>, k: K, valueMaker: (k: K) => V): V;

export declare function firstValue<K, V>(m: Map<K, V>): V;
export declare function isNonEmpty<T>(ts: T[] | null | undefined): ts is NonNullable<T[]>;
export declare function nonEmpty<T>(ts: T[] | null | undefined): ts is NonNullable<T[]>;

6

lib/util/collections.js
"use strict";
Object.defineProperty(exports, "__esModule", { value: true });
exports.isNonEmpty = exports.firstValue = exports.dedupe = exports.sorted = exports.partitionByEquality = exports.makeArrayValuesMap = exports.mapEntries = exports.mapValues = exports.makeMap = exports.mapSet = exports.setsEqual = exports.computeIfAbsent = void 0;
exports.nonEmpty = exports.firstValue = exports.dedupe = exports.sorted = exports.partitionByEquality = exports.makeArrayValuesMap = exports.mapEntries = exports.mapValues = exports.makeMap = exports.mapSet = exports.setsEqual = exports.computeIfAbsent = void 0;
function computeIfAbsent(m, k, valueMaker) {

@@ -129,6 +129,6 @@ const maybeVal = m.get(k);

}
function isNonEmpty(ts) {
function nonEmpty(ts) {
return (ts != null && ts.length > 0);
}
exports.isNonEmpty = isNonEmpty;
exports.nonEmpty = nonEmpty;
//# sourceMappingURL=collections.js.map

1

lib/util/strings.d.ts

@@ -8,4 +8,5 @@ export declare function upperCamelCase(name: string): string;

export declare function makeNameNotInSet(baseName: string, existingNames: Set<string>, suffixSep?: string): string;
export declare function makeNameNotInSets(baseName: string, avoidSets: Set<string>[], suffixSep?: string): string;
export declare function indentLines(linesStr: string, spacesCount: number, indentFirstLine?: boolean): string;
export declare function unDoubleQuote(s: string): string;
export declare function hashString(s: string): number;

18

lib/util/strings.js
"use strict";
Object.defineProperty(exports, "__esModule", { value: true });
exports.hashString = exports.unDoubleQuote = exports.indentLines = exports.makeNameNotInSet = exports.snakeCase = exports.replaceAll = exports.escapeRegExp = exports.lowerCaseInitials = exports.lowerCamelCase = exports.upperCamelCase = void 0;
exports.hashString = exports.unDoubleQuote = exports.indentLines = exports.makeNameNotInSets = exports.makeNameNotInSet = exports.snakeCase = exports.replaceAll = exports.escapeRegExp = exports.lowerCaseInitials = exports.lowerCamelCase = exports.upperCamelCase = void 0;
function upperCamelCase(name) {

@@ -13,4 +13,6 @@ return camelCase(name, true);

function lowerCaseInitials(name, sep) {
const cleanedName = name.replaceAll(/[._ ]/g, '_')
.replaceAll(/[^a-zA-Z0-9_]/g, "");
const parts = [];
for (const word of name.split(sep)) {
for (const word of cleanedName.split(sep)) {
if (word.length > 0)

@@ -65,2 +67,14 @@ parts.push(word.charAt(0).toLowerCase());

exports.makeNameNotInSet = makeNameNotInSet;
function makeNameNotInSets(baseName, avoidSets, suffixSep = "") {
const nameOk = (name) => !avoidSets.some(set => set.has(name));
if (nameOk(baseName))
return baseName;
else {
let i = 1;
while (!nameOk(baseName + suffixSep + i))
++i;
return baseName + suffixSep + i;
}
}
exports.makeNameNotInSets = makeNameNotInSets;
function indentLines(linesStr, spacesCount, indentFirstLine = true) {

@@ -67,0 +81,0 @@ const sb = [];

12

package.json
{
"name": "sqljson-query",
"version": "1.9.1",
"version": "1.9.2",
"description": "Command line tool to generate SQL/JSON SQL queries and result types for Typescript or Java.",

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

"build-pg": "docker build -t drugs-pg src/__tests__/db/pg",
"start-pg": "npm run build-pg && docker run -d --name drugs-pg --rm -p 127.0.0.1:5432:5432 --shm-size=256MB drugs-pg",
"start-pg": "npm run build-pg && docker run -d --name drugs-pg --rm -p 5432:5432 --shm-size=256MB drugs-pg",
"pg-cli": "docker exec -it drugs-pg psql -U drugs",

@@ -41,9 +41,9 @@ "build-mysql": "docker build -t drugs-mysql src/__tests__/db/mysql",

"ajv": "^8.11.2",
"dotenv": "^16.0.3",
"lodash": "^4.17.21",
"minimist": "^1.2.7",
"mysql2": "^2.3.3",
"path": "^0.12.7",
"pg": "^8.8.0",
"zod": "^3.19.1",
"dotenv": "^16.0.3",
"path": "^0.12.7"
"zod": "^3.19.1"
},

@@ -54,5 +54,5 @@ "devDependencies": {

"@types/lodash": "^4.14.191",
"@types/minimist": "^1.2.2",
"@types/node": "^18.11.10",
"@types/pg": "^8.6.5",
"@types/minimist": "^1.2.2",
"copyfiles": "^2.4.1",

@@ -59,0 +59,0 @@ "jest": "^29.3.1",

19

src/mod.ts
import * as path from 'path';
import { promises as fs } from 'fs';
import { requireFileExists, writeTextFile, readTextFile } from './util/files';
import { parseArgs, parseBoolOption } from './util/args';
import { Nullable, replaceAll} from './util/mod';
import { QueryGroupSpec, ResultRepr, SpecError } from './query-specs';
import { SourceGenerationOptions, SourceLanguage } from './source-generation-options';
import { QueryPropertiesMetadata } from './query-properties-metadata-generation';
import {promises as fs} from 'fs';
import {readTextFile, requireFileExists, writeTextFile} from './util/files';
import {parseArgs, parseBoolOption} from './util/args';
import {Nullable, replaceAll} from './util/mod';
import {QueryGroupSpec, ResultRepr, SpecError} from './query-specs';
import {SourceGenerationOptions, SourceLanguage} from './source-generation-options';
import {QueryPropertiesMetadata} from './query-properties-metadata-generation';
import {
DatabaseMetadata, GeneratedResultTypes, GeneratedSql, generateQueryGroupSources,
DatabaseMetadata,
GeneratedResultTypes,
GeneratedSql,
generateQueryGroupSources,
parseStoredDatabaseMetadata

@@ -12,0 +15,0 @@ } from './lib';

8

src/query-properties-metadata-generation/query-properties-metadata-generator.ts
import {
getBaseTable, getPropertySelectEntries, QueryFromEntry, SelectEntry, SqlSpec
getBaseTable,
getPropertySelectEntries,
QueryFromEntry,
SelectEntry,
SqlSpec
} from "../sql-generation/sql-specs";
import { ParentStep, QueryPropertyMetadata, QueryPropertiesMetadata } from "./query-properties-metadata";
import {ParentStep, QueryPropertiesMetadata, QueryPropertyMetadata} from "./query-properties-metadata";

@@ -6,0 +10,0 @@ export function makeQueryPropertiesMetadata

38

src/query-specs.ts

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

import { caseNormalizeName, Nullable, relIdDescn } from './util/mod';
import { DatabaseMetadata, makeRelId, RelId, RelMetadata } from './dbmd';
import { SourceLanguage } from './source-generation-options';
import {caseNormalizeName, Nullable, relIdDescn} from './util/mod';
import {DatabaseMetadata, makeRelId, RelId, RelMetadata} from './dbmd';
import {SourceLanguage} from './source-generation-options';

@@ -20,3 +20,3 @@ export interface QueryGroupSpec

resultRepresentations?: Nullable<ResultRepr[]>;
additionalObjectPropertyColumns?: Nullable<AdditionalObjectPropertyColumn[]>;
additionalOutputColumns?: Nullable<AdditionalOutputColumn[]>; // for JSON_OBJECT_ROWS only
generateResultTypes?: Nullable<boolean>;

@@ -29,2 +29,5 @@ generateSource?: Nullable<boolean>;

// Additional output columns for top-level JSON_OBJECT_ROWS queries (added after the "json" column).
export type AdditionalOutputColumn = string | TableFieldExpr;
export type QueryTypesFileHeader = string | { [l in SourceLanguage]: string };

@@ -81,3 +84,3 @@

referenceName: undefined;
subqueryAlias?: Nullable<string>; // alias for the FROM clause subquery, for use in record conditions
fromEntryAlias?: Nullable<string>; // alias for the FROM entry in the child's query for the parent, for record conditions
}

@@ -99,3 +102,3 @@

equatedFields: FieldPair[];
// By default a custom match condition for a parent will cause a nullable parent reference or nullable inline
// By default, a custom match condition for a parent will cause a nullable parent reference or nullable inline
// fields from the parent. This option allows asserting that a matching parent record always exists for this

@@ -124,5 +127,2 @@ // join condition, so the parent reference or inline fields can be non-nullable if other factors don't

export type AdditionalObjectPropertyColumn = string | { property: string; as: string };
export function getInlineParentSpecs(tableSpec: TableJsonSpec): InlineParentSpec[]

@@ -201,3 +201,4 @@ {

(
tableSpec: TableJsonSpec,
fieldExpressions: Nullable<(string | TableFieldExpr)[]>,
table: string,
defaultSchema: Nullable<string>,

@@ -208,7 +209,7 @@ dbmd: DatabaseMetadata,

{
if (!tableSpec.fieldExpressions)
if (!fieldExpressions)
return;
const simpleSelectFields : string[] = [];
for ( const [ix, fieldExpr] of tableSpec.fieldExpressions.entries() )
for ( const [ix, fieldExpr] of fieldExpressions.entries() )
{

@@ -230,3 +231,3 @@ if (typeof fieldExpr === 'string')

const relMd = getRelMetadata(tableSpec.table, defaultSchema, dbmd, specLoc);
const relMd = getRelMetadata(table, defaultSchema, dbmd, specLoc);

@@ -278,13 +279,2 @@ verifyFieldsExistInRelMd(simpleSelectFields, relMd, dbmd, specLoc);

function verifyPropertyColumnsExist
(
propertyNames: string[],
specLoc: SpecLocation
)
{
// TODO
}
function getRelMetadata

@@ -291,0 +281,0 @@ (

19

src/result-type-generation/result-type-spec-generator.ts

@@ -1,9 +0,16 @@

import { RelId } from "../dbmd";
import {RelId} from "../dbmd";
import {
ChildCollectionSelectEntry, ExpressionSelectEntry, FieldSelectEntry, getBaseTable,
getPropertySelectEntries, InlineParentSelectEntry, ParentReferenceSelectEntry, QueryFromEntry,
SelectEntry, SqlSpec
ChildCollectionSelectEntry,
ExpressionSelectEntry,
FieldSelectEntry,
getBaseTable,
getPropertySelectEntries,
InlineParentSelectEntry,
ParentReferenceSelectEntry,
QueryFromEntry,
SelectEntry,
SqlSpec
} from "../sql-generation/sql-specs";
import { dedupedWithAssignedNames } from "./result-type-names-assignment";
import { NamedResultTypeSpec, ResultTypeProperty, ResultTypeSpec } from "./result-type-specs";
import {dedupedWithAssignedNames} from "./result-type-names-assignment";
import {NamedResultTypeSpec, ResultTypeProperty, ResultTypeSpec} from "./result-type-specs";

@@ -10,0 +17,0 @@ export function makeNamedResultTypeSpecs

129

src/sql-generation/sql-dialects/hsql.ts
import {Nullable} from '../../util/nullable';
import {indentLines} from '../../util/strings';
import {SqlDialect} from './sql-dialect';
import {SelectEntry} from "../sql-specs";
import {generalSqlKeywordsLowercase, SelectEntry} from "../sql-specs";
const simpleIdentifierRegex = new RegExp(/^[A-Za-z][A-Za-z0-9_]+$/);
const sqlKeywordsLowercase = new Set([
'select', 'from', 'where', 'user', 'order', 'group', 'by', 'over', 'is'
]);
export class HSQLDialect implements SqlDialect

@@ -22,3 +16,3 @@ {

selectEntries: SelectEntry[],
srcAlias: string
selectEntryValueSqlFn: (selectEntry: SelectEntry) => string
)

@@ -29,5 +23,4 @@ : string

selectEntries
.map(se=>
`'${se.projectedName}' value ${srcAlias}.${this.quoteColumnNameIfNeeded(se.projectedName)}` +
(needsFormatJsonQualifier(se) ? " format json" : "")
.map(se=> `'${se.projectedName}' value ${selectEntryValueSqlFn(se)}` +
(needsFormatJson(se) ? " format json" : "")
)

@@ -46,4 +39,4 @@ .join(',\n');

selectEntries: SelectEntry[],
orderBy: Nullable<string>,
srcAlias: string
selectEntryValueSqlFn: (selectEntry: SelectEntry) => string,
orderBy: Nullable<string>
)

@@ -54,5 +47,5 @@ : string

'coalesce(json_arrayagg(' +
this.getRowObjectExpression(selectEntries, srcAlias) +
(orderBy != null ? ' order by ' + orderBy.replace(/\$\$/g, srcAlias) : '') +
`), '[]')`
this.getRowObjectExpression(selectEntries, selectEntryValueSqlFn) +
(orderBy != null ? ` order by ${orderBy}` : '') +
`), '[]')`
);

@@ -63,14 +56,11 @@ }

(
selectEntry: SelectEntry,
orderBy: Nullable<string>,
srcAlias: string
valueExpression: string,
orderBy: Nullable<string>
)
: string
{
const columnName = selectEntry.projectedName;
return (
`coalesce(json_arrayagg(${srcAlias}.${this.quoteColumnNameIfNeeded(columnName)}` +
(orderBy != null ? ` order by ${orderBy.replace(/\$\$/g, srcAlias)}` : '') +
`), '[]')`
`coalesce(jsonb_agg(${valueExpression}` +
(orderBy != null ? ' order by ' + orderBy : '') +
'))'
);

@@ -85,3 +75,3 @@ }

!this.uppercaseNameRegex.test(name) ||
sqlKeywordsLowercase.has(name.toLowerCase()) )
generalSqlKeywordsLowercase.has(name.toLowerCase()) )
return `"${name}"`;

@@ -95,3 +85,5 @@ return name;

return nameExact;
if ( !simpleIdentifierRegex.test(nameExact) || !this.uppercaseNameRegex.test(nameExact) )
if ( !simpleIdentifierRegex.test(nameExact) ||
!this.uppercaseNameRegex.test(nameExact) ||
avoidColumnNamesLowercase.has(nameExact.toLowerCase()) )
return `"${nameExact}"`;

@@ -102,3 +94,3 @@ return nameExact;

function needsFormatJsonQualifier(selectEntry: SelectEntry): boolean
function needsFormatJson(selectEntry: SelectEntry): boolean
{

@@ -115,5 +107,86 @@ switch (selectEntry.entryType)

case "se-inline-parent-prop":
return needsFormatJsonQualifier(selectEntry.parentSelectEntry);
return needsFormatJson(selectEntry.parentSelectEntry);
}
}
const simpleIdentifierRegex = new RegExp(/^[A-Za-z][A-Za-z0-9_]+$/);
const avoidColumnNamesLowercase = new Set([
'all',
'and',
'and',
'any',
'as',
'at',
'between',
'both',
'by',
'call',
'case',
'cast',
'coalesce',
'collation',
'convert',
'corresponding',
'create',
'cross',
'cube',
'current',
'current_path',
'default',
'distinct',
'do',
'drop',
'else',
'every',
'except',
'exists',
'fetch',
'for',
'from',
'full',
'grant',
'group',
'grouping',
'having',
'in',
'inner',
'intersect',
'into',
'is',
'join',
'leading',
'left',
'like',
'natural',
'normalize',
'not',
'nullif',
'on',
'or',
'order',
'outer',
'primary',
'references',
'right',
'rollup',
'row',
'select',
'set',
'some',
'sum',
'table',
'then',
'to',
'trailing',
'trigger',
'union',
'unique',
'user',
'using',
'values',
'when',
'where',
'with',
]);

289

src/sql-generation/sql-dialects/mysql.ts
import {Nullable} from '../../util/nullable';
import {indentLines, unDoubleQuote} from '../../util/strings';
import {SqlDialect} from './sql-dialect';
import {SelectEntry} from "../sql-specs";
import {generalSqlKeywordsLowercase, SelectEntry} from "../sql-specs";
const simpleIdentifierRegex = new RegExp(/^[A-Za-z][A-Za-z0-9_]+$/);
const sqlKeywordsLowercase = new Set([
'select', 'from', 'where', 'user', 'order', 'group', 'by', 'over', 'is'
]);
export class MySQLDialect implements SqlDialect

@@ -22,11 +16,9 @@ {

selectEntries: SelectEntry[],
srcAlias: string
selectEntryValueSqlFn: (selectEntry: SelectEntry) => string
)
: string
{
const columnNames = selectEntries.map(e => e.projectedName);
const objectFieldDecls =
columnNames
.map(colName => `'${colName}', ${srcAlias}.${this.quoteColumnNameIfNeeded(colName)}`)
selectEntries
.map(se => `'${se.projectedName}', ${selectEntryValueSqlFn(se)}`)
.join(',\n');

@@ -44,4 +36,4 @@

selectEntries: SelectEntry[],
orderBy: Nullable<string>,
srcAlias: string
selectEntryValueSqlFn: (selectEntry: SelectEntry) => string,
orderBy: Nullable<string>
)

@@ -55,3 +47,3 @@ : string

'cast(coalesce(json_arrayagg(' +
this.getRowObjectExpression(selectEntries, srcAlias) +
this.getRowObjectExpression(selectEntries, selectEntryValueSqlFn) +
`), json_type('[]')) as json)`

@@ -63,16 +55,11 @@ );

(
selectEntry: SelectEntry,
orderBy: Nullable<string>,
srcAlias: string
valueExpression: string,
orderBy: Nullable<string>
)
: string
{
const columnName = selectEntry.projectedName;
if (orderBy != null )
throw new Error(`Error for column ${columnName}: MySQL dialect does not support ordering in aggregate functions currently.`);
throw new Error(`MySQL dialect does not support ordering in aggregate functions currently.`);
return (
`cast(coalesce(json_arrayagg(${srcAlias}.${this.quoteColumnNameIfNeeded(columnName)}), json_type('[]')) as json)`
);
return (`cast(coalesce(json_arrayagg(${valueExpression}), json_type('[]')) as json)`);
}

@@ -86,3 +73,4 @@

return `\`${unDoubleQuote(name)}\``;
if ( !simpleIdentifierRegex.test(name) || sqlKeywordsLowercase.has(name.toLowerCase()) )
if ( !simpleIdentifierRegex.test(name) ||
generalSqlKeywordsLowercase.has(name.toLowerCase()) )
return `\`${name}\``;

@@ -98,3 +86,4 @@ return name;

return `\`${unDoubleQuote(name)}\``;
if ( !simpleIdentifierRegex.test(name) )
if ( !simpleIdentifierRegex.test(name) ||
avoidColumnNamesLowercase.has(name.toLowerCase()) )
return `\`${name}\``;

@@ -104,1 +93,249 @@ return name;

}
const simpleIdentifierRegex = new RegExp(/^[A-Za-z][A-Za-z0-9_]+$/);
const avoidColumnNamesLowercase = new Set([
'add',
'all',
'alter',
'analyze',
'and',
'as',
'asc',
'asensitive',
'before',
'between',
'bigint',
'binary',
'blob',
'both',
'by',
'call',
'cascade',
'case',
'change',
'char',
'character',
'check',
'collate',
'column',
'condition',
'constraint',
'continue',
'convert',
'create',
'cross',
'cube',
'cume_dist',
'current_date',
'current_time',
'current_timestamp',
'current_user',
'cursor',
'database',
'databases',
'day_hour',
'day_microsecond',
'day_minute',
'day_second',
'dec',
'decimal',
'declare',
'default',
'delayed',
'delete',
'dense_rank',
'desc',
'describe',
'deterministic',
'distinct',
'distinctrow',
'div',
'double',
'drop',
'dual',
'each',
'else',
'elseif',
'enclosed',
'escaped',
'except',
'exists',
'exit',
'explain',
'false',
'fetch',
'float',
'float4',
'float8',
'for',
'force',
'foreign',
'from',
'fulltext',
'function',
'generated',
'get',
'grant',
'group',
'grouping',
'having',
'high_priority',
'hour_microsecond',
'hour_minute',
'hour_second',
'if',
'ignore',
'in',
'index',
'infile',
'inner',
'inout',
'insensitive',
'insert',
'int',
'int1',
'int2',
'int3',
'int4',
'int8',
'integer',
'interval',
'into',
'is',
'iterate',
'join',
'key',
'keys',
'kill',
'lateral',
'lead',
'leading',
'leave',
'left',
'like',
'limit',
'lines',
'load',
'localtime',
'localtimestamp',
'lock',
'long',
'longblob',
'longtext',
'loop',
'low_priority',
'match',
'mediumblob',
'mediumint',
'mediumtext',
'middleint',
'minute_microsecond',
'minute_second',
'mod',
'modifies',
'natural',
'no_write_to_binlog',
'not',
'null',
'numeric',
'of',
'on',
'optimize',
'option',
'optionally',
'or',
'order',
'out',
'outer',
'outfile',
'over',
'partition',
'percent_rank',
'precision',
'primary',
'procedure',
'purge',
'range',
'rank',
'read',
'reads',
'real',
'recursive',
'references',
'regexp',
'release',
'rename',
'repeat',
'replace',
'require',
'require',
'resignal',
'restrict',
'return',
'revoke',
'right',
'rlike',
'row',
'row_number',
'rows',
'schema',
'schemas',
'second_microsecond',
'select',
'sensitive',
'separator',
'set',
'signal',
'show',
'smallint',
'spatial',
'specific',
'sql',
'sql_big_result',
'sql_calc_found_rows',
'sql_small_result',
'sqlexception',
'sqlstate',
'sqlwarning',
'ssl',
'starting',
'straight_join',
'system',
'table',
'terminated',
'then',
'tinyblob',
'tinyint',
'tinytext',
'to',
'trailing',
'trigger',
'true',
'undo',
'union',
'unique',
'unlock',
'unsigned',
'update',
'usage',
'use',
'using',
'utc_date',
'utc_time',
'utc_timestamp',
'values',
'varbinary',
'varchar',
'varcharacter',
'varying',
'when',
'where',
'while',
'window',
'with',
'write',
'xor',
'year_month',
'zerofill'
]);

164

src/sql-generation/sql-dialects/ora.ts
import {Nullable} from '../../util/nullable';
import {indentLines} from '../../util/strings';
import {SqlDialect} from './sql-dialect';
import {SelectEntry} from "../sql-specs";
import {generalSqlKeywordsLowercase, SelectEntry} from "../sql-specs";
const simpleIdentifierRegex = new RegExp(/^[A-Za-z][A-Za-z0-9_]+$/);
const sqlKeywordsLowercase = new Set([
'select', 'from', 'where', 'user', 'order', 'group', 'by', 'over', 'is'
]);
export class OracleDialect implements SqlDialect

@@ -22,11 +16,9 @@ {

selectEntries: SelectEntry[],
srcAlias: string
selectEntryValueSqlFn: (selectEntry: SelectEntry) => string
)
: string
{
const columnNames = selectEntries.map(e => e.projectedName);
const objectFieldDecls =
columnNames
.map(colName => `'${colName}' value ${srcAlias}.${this.quoteColumnNameIfNeeded(colName)}`)
selectEntries
.map(se => `'${se.projectedName}' value ${selectEntryValueSqlFn(se)}`)
.join(',\n');

@@ -44,4 +36,4 @@

selectEntries: SelectEntry[],
orderBy: Nullable<string>,
srcAlias: string
selectEntryValueSqlFn: (selectEntry: SelectEntry) => string,
orderBy: Nullable<string>
)

@@ -52,4 +44,4 @@ : string

'treat(coalesce(json_arrayagg(' +
this.getRowObjectExpression(selectEntries, srcAlias) +
(orderBy != null ? ' order by ' + orderBy.replace(/\$\$/g, srcAlias) : '') +
this.getRowObjectExpression(selectEntries, selectEntryValueSqlFn) +
(orderBy != null ? ` order by ${orderBy}` : '') +
` returning clob), to_clob('[]')) as json)`

@@ -61,13 +53,10 @@ );

(
selectEntry: SelectEntry,
orderBy: Nullable<string>,
srcAlias: string
valueExpression: string,
orderBy: Nullable<string>
)
: string
{
const columnName = selectEntry.projectedName;
return (
`treat(coalesce(json_arrayagg(${srcAlias}.${this.quoteColumnNameIfNeeded(columnName)}` +
(orderBy != null ? ` order by ${orderBy.replace(/\$\$/g, srcAlias)}` : '') +
`treat(coalesce(json_arrayagg(${valueExpression}` +
(orderBy != null ? ` order by ${orderBy}` : '') +
` returning clob), to_clob('[]')) as json)`

@@ -83,3 +72,3 @@ );

!this.uppercaseNameRegex.test(name) ||
sqlKeywordsLowercase.has(name.toLowerCase()) )
generalSqlKeywordsLowercase.has(name.toLowerCase()) )
return `"${name}"`;

@@ -89,10 +78,125 @@ return name;

quoteColumnNameIfNeeded(nameExact: string): string
quoteColumnNameIfNeeded(name: string): string
{
if ( this.quotedStringRegex.test(nameExact) )
return nameExact;
if ( !simpleIdentifierRegex.test(nameExact) || !this.uppercaseNameRegex.test(nameExact) )
return `"${nameExact}"`;
return nameExact;
if ( this.quotedStringRegex.test(name) )
return name;
if ( !simpleIdentifierRegex.test(name) ||
!this.uppercaseNameRegex.test(name) ||
avoidColumnNamesLowercase.has(name.toLowerCase()) )
return `"${name}"`;
return name;
}
}
const simpleIdentifierRegex = new RegExp(/^[A-Za-z][A-Za-z0-9_]+$/);
const avoidColumnNamesLowercase = new Set([
'access',
'add',
'all',
'alter',
'and',
'any',
'as',
'asc',
'audit',
'between',
'by',
'char',
'check',
'cluster',
'column',
'comment',
'compress',
'connect',
'create',
'current',
'date',
'decimal',
'default',
'delete',
'desc',
'distinct',
'drop',
'else',
'exclusive',
'exists',
'file',
'float',
'for',
'from',
'grant',
'group',
'having',
'identified',
'immediate',
'in',
'increment',
'index',
'initial',
'insert',
'integer',
'intersect',
'into',
'is',
'level',
'like',
'lock',
'long',
'maxextents',
'minus',
'mlslabel',
'mode',
'modify',
'noaudit',
'nocompress',
'not',
'nowait',
'null',
'number',
'of',
'offline',
'on',
'online',
'option',
'or',
'order',
'pctfree',
'prior',
'public',
'raw',
'rename',
'resource',
'revoke',
'row',
'rowid',
'rownum',
'rows',
'select',
'session',
'set',
'share',
'size',
'smallint',
'start',
'successful',
'synonym',
'sysdate',
'table',
'then',
'to',
'trigger',
'uid',
'union',
'unique',
'update',
'user',
'validate',
'values',
'varchar',
'varchar2',
'view',
'whenever',
'where',
'with',
]);

141

src/sql-generation/sql-dialects/pg.ts
import {Nullable} from '../../util/nullable';
import {indentLines} from '../../util/strings';
import {SqlDialect} from './sql-dialect';
import {SelectEntry} from "../sql-specs";
import {generalSqlKeywordsLowercase, SelectEntry} from "../sql-specs";
const simpleIdentifierRegex = new RegExp(/^[A-Za-z][A-Za-z0-9_]+$/);
const sqlKeywordsLowercase = new Set([
'select', 'from', 'where', 'user', 'order', 'group', 'by', 'over', 'is'
]);
export class PostgresDialect implements SqlDialect

@@ -22,11 +16,9 @@ {

selectEntries: SelectEntry[],
srcAlias: string
selectEntryValueSqlFn: (selectEntry: SelectEntry) => string
)
: string
{
const columnNames = selectEntries.map(e => e.projectedName);
const objectFieldDecls =
columnNames
.map(colName => `'${colName}', ${srcAlias}.${this.quoteColumnNameIfNeeded(colName)}`)
selectEntries
.map(se => `'${se.projectedName}', ${selectEntryValueSqlFn(se)}`)
.join(',\n');

@@ -44,4 +36,4 @@

selectEntries: SelectEntry[],
orderBy: Nullable<string>,
srcAlias: string
selectEntryValueSqlFn: (selectEntry: SelectEntry) => string,
orderBy: Nullable<string>
)

@@ -52,4 +44,4 @@ : string

'coalesce(jsonb_agg(' +
this.getRowObjectExpression(selectEntries, srcAlias) +
(orderBy != null ? ` order by ${orderBy.replace(/\$\$/g, srcAlias)}` : '') +
this.getRowObjectExpression(selectEntries, selectEntryValueSqlFn) +
(orderBy != null ? ` order by ${orderBy}` : '') +
`),'[]'::jsonb)`

@@ -61,13 +53,10 @@ );

(
selectEntry: SelectEntry,
orderBy: Nullable<string>,
srcAlias: string
valueExpression: string,
orderBy: Nullable<string>
)
: string
{
const columnName = selectEntry.projectedName;
return (
`coalesce(jsonb_agg(${srcAlias}.${this.quoteColumnNameIfNeeded(columnName)}` +
(orderBy != null ? ' order by ' + orderBy.replace(/\$\$/g, srcAlias) : '') +
`coalesce(jsonb_agg(${valueExpression}` +
(orderBy != null ? ' order by ' + orderBy : '') +
'))'

@@ -83,3 +72,3 @@ );

!this.lowercaseNameRegex.test(name) ||
sqlKeywordsLowercase.has(name) )
generalSqlKeywordsLowercase.has(name.toLowerCase()) )
return `"${name}"`;

@@ -93,6 +82,106 @@ return name;

return name;
if ( !simpleIdentifierRegex.test(name) || !this.lowercaseNameRegex.test(name) )
if ( !simpleIdentifierRegex.test(name) ||
!this.lowercaseNameRegex.test(name) ||
avoidColumnNamesLowercase.has(name.toLowerCase()) )
return `"${name}"`;
return name;
}
}
}
const simpleIdentifierRegex = new RegExp(/^[A-Za-z][A-Za-z0-9_]+$/);
const avoidColumnNamesLowercase = new Set([
'all',
'analyze',
'and',
'any',
'array',
'as',
'asymmetric',
'authorization',
'binary',
'both',
'case',
'cast',
'check',
'collate',
'collation',
'column',
'constraint',
'create',
'cross',
'current_date',
'current_role',
'current_time',
'current_timestamp',
'current_user',
'default',
'deferrable',
'desc',
'distinct',
'do',
'else',
'end',
'except',
'false',
'fetch',
'for',
'foreign',
'freeze',
'from',
'full',
'grant',
'group',
'having',
'ilike',
'in',
'initially',
'inner',
'intersect',
'interval',
'into',
'is',
'isnull',
'join',
'lateral',
'leading',
'left',
'like',
'limit',
'localtime',
'localtimestamp',
'natural',
'not',
'notnull',
'null',
'offset',
'on',
'only',
'or',
'order',
'outer',
'overlaps',
'placing',
'primary',
'references',
'right',
'select',
'session_user',
'similar',
'some',
'symmetric',
'table',
'tablesample',
'then',
'to',
'trailing',
'true',
'union',
'unique',
'user',
'using',
'verbose',
'when',
'where',
'with'
]);

22

src/sql-generation/sql-dialects/sql-dialect.ts

@@ -6,7 +6,23 @@ import {Nullable} from '../../util/mod';

{
getRowObjectExpression(selectEntries: SelectEntry[], srcAlias: string): string;
getRowObjectExpression
(
selectEntries: SelectEntry[],
selectEntryValueSqlFn: (selectEntry: SelectEntry) => string
)
: string;
getAggregatedRowObjectsExpression(selectEntries: SelectEntry[], orderBy: Nullable<string>, srcAlias: string): string;
getAggregatedRowObjectsExpression
(
selectEntries: SelectEntry[],
selectEntryValueSqlFn: (selectEntry: SelectEntry) => string,
orderBy: Nullable<string>
)
: string;
getAggregatedColumnValuesExpression(selectEntry: SelectEntry, orderBy: Nullable<string>, srcAlias: string): string;
getAggregatedColumnValuesExpression
(
valueExpression: string,
orderBy: Nullable<string>
)
: string;

@@ -13,0 +29,0 @@ quoteObjectNameIfNeeded(name: string): string;

239

src/sql-generation/sql-source-generator.ts
import {indentLines, replaceAll} from "../util/strings";
import {isNonEmpty, mapSet, sorted} from "../util/collections";
import {mapSet, nonEmpty, sorted} from "../util/collections";
import {exactUnquotedName} from "../util/database-names";
import {CaseSensitivity, RelId} from "../dbmd";
import {
FromEntry,
getPropertySelectEntries,
OrderBy,
ParentChildCondition,
SelectEntry,
SqlSpec,
WhereEntry
} from "./sql-specs";
import {FromEntry, getPropertySelectEntries, ParentChildCondition, SelectEntry, SqlSpec, WhereEntry} from "./sql-specs";
import {SqlDialect} from "./sql-dialects";
import {AdditionalObjectPropertyColumn} from "../query-specs";
import {Nullable} from "../util/mod";

@@ -35,40 +25,35 @@ export class SqlSourceGenerator

{
const baseSql = this.baseSql(spec);
const propSelEntries = getPropertySelectEntries(spec);
const objWrap = spec.objectWrapProperties ?? false;
const arrayAgg = spec.aggregateToArray ?? false;
const addSelEntries = spec.additionalOutputSelectEntries ?? [];
const selectComment = this.genComments && spec.selectEntriesLeadingComment ?
`-- ${spec.selectEntriesLeadingComment}\n` : '';
const wrapPropsInObj = spec.objectWrapProperties ?? false;
const selEntryValFn = (se: SelectEntry) => this.selectEntrySql(se, true);
if (!spec.aggregateToArray && !wrapPropsInObj) // no aggregation nor object wrapping
{
return baseSql;
}
else // at least one of aggregation and object wrapping of properties will be done
{
const propSelectEntries = getPropertySelectEntries(spec);
const baseTable = baseTableDescn(spec); // only used for comments
const selectEntries =
arrayAgg ?
(objWrap
? this.sqlDialect.getAggregatedRowObjectsExpression(propSelEntries, selEntryValFn, spec.aggregateOrderBy)
: this.sqlDialect.getAggregatedColumnValuesExpression(this.selectEntrySql(propSelEntries[0], true), spec.aggregateOrderBy))+' json\n'
: objWrap ?
this.sqlDialect.getRowObjectExpression(propSelEntries, selEntryValFn)+' json' +
(nonEmpty(addSelEntries)
? `,\n${addSelEntries.map(c => this.selectEntrySql(c)).join(',\n')}`
: ''
) + "\n"
: this.selectEntriesSql(spec.selectEntries) + "\n";
if (!spec.aggregateToArray) // no aggregation but do object wrapping
{
const additionalCols = spec.additionalObjectPropertyColumns ?? [];
return this.jsonRowObjectsSql(baseSql, propSelectEntries, additionalCols, spec.orderBy, baseTable);
}
else // aggregation and maybe object wrapping
{
return this.aggregateSql(baseSql, propSelectEntries, wrapPropsInObj, spec.orderBy, baseTable);
}
}
}
private baseSql(spec: SqlSpec)
{
const selectComment = this.genComments && spec.selectEntriesLeadingComment ?
`-- ${spec.selectEntriesLeadingComment}\n` : '';
const selectEntries = this.makeSelectEntriesSql(spec.selectEntries) + '\n';
const fromComment = this.genComments && spec.fromEntriesLeadingComment ?
`-- ${spec.fromEntriesLeadingComment}\n` : '';
const fromEntries = spec.fromEntries.map(e => this.fromEntrySql(e)).join('\n') + '\n';
const whereClause = isNonEmpty(spec.whereEntries)
? `where (\n${this.indent(spec.whereEntries.map(e => this.whereEntrySql(e)).join(' and\n'))}\n)\n`
const whereClause = nonEmpty(spec.whereEntries) ?
`where (\n${this.indent(spec.whereEntries.map(e => this.whereEntrySql(e)).join(' and\n'))}\n)\n`
: '';
const orderByClause = spec.orderBy
? 'order by ' + spec.orderBy.orderBy.replace(/\$\$/g, spec.orderBy.tableAlias) + '\n'
const orderByClause = spec.orderBy ?
'order by ' + spec.orderBy.orderBy.replace(/\$\$/g, spec.orderBy.tableAlias) + '\n'
: '';

@@ -84,3 +69,3 @@

private makeSelectEntriesSql(specSelectEntries: SelectEntry[]): string
private selectEntriesSql(specSelectEntries: SelectEntry[]): string
{

@@ -94,79 +79,16 @@ // Assign any missing displayOrders based on select entry position.

const sortedSelectEntries = sorted(selectEntries, (e1, e2) => (e1.displayOrder ?? 0) - (e2.displayOrder ?? 0));
const sortedSelectEntries =
sorted(
selectEntries,
(e1, e2) => (e1.displayOrder ?? 0) - (e2.displayOrder ?? 0)
);
return sortedSelectEntries.map(e => this.selectEntrySql(e)).join(',\n');
}
private jsonRowObjectsSql
(
baseSql: string,
selectEntries: SelectEntry[],
additionalColumns: AdditionalObjectPropertyColumn[],
orderBy: Nullable<OrderBy>,
baseTableDesc: Nullable<string>, // for comments
)
: string
{
return (
'select\n' +
this.indent(
(baseTableDesc ? `-- row object for table '${baseTableDesc}'\n` : '') +
this.sqlDialect.getRowObjectExpression(selectEntries, 'q') + ' json' +
(isNonEmpty(additionalColumns)
? `,\n${additionalColumns.map(c => this.additionalPropertyColumnSql(c)).join(',\n')}`
: '')
) + '\n' +
'from (\n' +
nlterm(this.indent(
(baseTableDesc ? `-- base query for table '${baseTableDesc}'\n` : '') +
baseSql
)) +
') q' +
(orderBy != null ? '\norder by ' + orderBy.orderBy.replace(/\$\$/g, 'q') : '')
return sortedSelectEntries.map(se =>
(this.genComments && se.comment ? `-- ${se.comment}\n` : '') +
this.selectEntrySql(se)).join(',\n'
);
}
private additionalPropertyColumnSql(c: AdditionalObjectPropertyColumn): string
private selectEntrySql(selectEntry: SelectEntry, valueOnly = false): string
{
if (typeof(c) === 'string')
return this.sqlDialect.quoteColumnNameIfNeeded(c);
const propNameExpr = this.sqlDialect.quoteColumnNameIfNeeded(c.property);
const alias = this.sqlDialect.quoteColumnNameIfNeeded(c.as);
return `${propNameExpr} as ${alias}`;
}
private aggregateSql
(
sql: string,
selectEntries: SelectEntry[],
wrapProps: boolean,
orderBy: Nullable<OrderBy>,
baseTableDesc: Nullable<string>, // for comments
)
: string
{
const ordby = orderBy?.orderBy;
return (
'select\n' +
this.indent(
(this.genComments ? `-- aggregated ${wrapProps? 'rows' : 'values'} from table '${baseTableDesc}'\n`: '') +
(wrapProps
? this.sqlDialect.getAggregatedRowObjectsExpression(selectEntries, ordby, 'q')
: this.sqlDialect.getAggregatedColumnValuesExpression(selectEntries[0], ordby, 'q')) + ' json\n'
) +
'from (\n' +
nlterm(this.indent(
(this.genComments ? `-- base query for table '${baseTableDesc}'\n` : '') +
sql
)) +
') q'
);
}
private selectEntrySql(selectEntry: SelectEntry): string
{
const projectedName = this.maybeQuoteColumn(selectEntry.projectedName);
const isProjectedNameQuoted = projectedName !== selectEntry.projectedName;
switch (selectEntry.entryType)

@@ -176,7 +98,27 @@ {

{
const fieldName = this.maybeQuoteColumn(selectEntry.field.name);
const fieldName = this.sqlDialect.quoteColumnNameIfNeeded(selectEntry.field.name);
const valueSql = `${selectEntry.tableAlias}.${fieldName}`;
if (valueOnly)
return valueSql;
const projectedName = this.sqlDialect.quoteColumnNameIfNeeded(selectEntry.projectedName);
const isProjectedNameQuoted = projectedName !== selectEntry.projectedName;
const sep = isProjectedNameQuoted ? ' ' : ' as ';
return `${selectEntry.tableAlias}.${fieldName}${sep}${projectedName}`;
return `${valueSql}${sep}${projectedName}`;
}
case 'se-hidden-pkf':
{
const fieldName = this.sqlDialect.quoteColumnNameIfNeeded(selectEntry.pkFieldName);
const valueSql = `${selectEntry.tableAlias}.${fieldName}`;
if (valueOnly)
return valueSql;
const isFieldNameQuoted = fieldName !== selectEntry.pkFieldName;
const sep = isFieldNameQuoted ? ' ' : ' as ';
const exportedName = this.sqlDialect.quoteColumnNameIfNeeded(selectEntry.projectedName);
return `${valueSql}${sep}${exportedName}`;
}
case 'se-expr':

@@ -186,2 +128,7 @@ {

const expr = replaceAll(selectEntry.expression, tableAliasPlaceholder, selectEntry.tableAlias);
if (valueOnly)
return expr;
const projectedName = this.sqlDialect.quoteColumnNameIfNeeded(selectEntry.projectedName);
const isProjectedNameQuoted = projectedName !== selectEntry.projectedName;
const sep = isProjectedNameQuoted ? ' ' : ' as ';

@@ -193,4 +140,4 @@

{
return (this.genComments && selectEntry.comment ? `-- ${selectEntry.comment}\n` : '') +
`${selectEntry.parentAlias}.${projectedName}`;
const projectedName = this.sqlDialect.quoteColumnNameIfNeeded(selectEntry.projectedName);
return `${selectEntry.parentAlias}.${projectedName}`;
}

@@ -201,6 +148,9 @@ case 'se-parent-ref':

return (this.genComments && selectEntry.comment ? `-- ${selectEntry.comment}\n` : '') +
'(\n' +
this.indent(parentRowObjSql) + '\n' +
`) ${projectedName}`;
const valueSql = '(\n'+this.indent(parentRowObjSql)+')';
if (valueOnly)
return valueSql;
const projectedName = this.sqlDialect.quoteColumnNameIfNeeded(selectEntry.projectedName);
return `${valueSql} ${projectedName}`;
}

@@ -211,15 +161,10 @@ case 'se-child-coll':

return (this.genComments && selectEntry.comment ? `-- ${selectEntry.comment}\n` : '') +
'(\n' +
this.indent(collectionSql) + '\n' +
`) ${projectedName}`;
const valueSql = '(\n'+this.indent(collectionSql)+')';
if (valueOnly)
return valueSql;
const projectedName = this.sqlDialect.quoteColumnNameIfNeeded(selectEntry.projectedName);
return `${valueSql} ${projectedName}`;
}
case 'se-hidden-pkf':
{
const fieldName = this.maybeQuoteColumn(selectEntry.pkFieldName);
const isFieldNameQuoted = fieldName !== selectEntry.pkFieldName;
const sep = isFieldNameQuoted ? ' ' : ' as ';
const exportedName = this.maybeQuoteColumn(selectEntry.projectedName);
return `${selectEntry.tableAlias}.${fieldName}${sep}${exportedName}`;
}
}

@@ -277,5 +222,5 @@ }

return pcCond.matchedFields.map(mf =>
`${childAlias}.${this.maybeQuoteColumn(mf.foreignKeyFieldName)}` +
`${childAlias}.${this.sqlDialect.quoteColumnNameIfNeeded(mf.foreignKeyFieldName)}` +
' = ' +
`${parentAlias}.${this.maybeQuoteColumn(mf.primaryKeyFieldName)}`
`${parentAlias}.${this.sqlDialect.quoteColumnNameIfNeeded(mf.primaryKeyFieldName)}`
).join(' and ');

@@ -296,22 +241,4 @@ }

}
private maybeQuoteColumn(colName: string)
{
return this.sqlDialect.quoteColumnNameIfNeeded(colName);
}
}
function baseTableDescn(sqlSpec: SqlSpec): string | null
{
const firstFromEntry = sqlSpec.fromEntries[0];
if (firstFromEntry.entryType === 'table') return firstFromEntry.table.name;
return null;
}
// Newline terminate the given string if it doesn't end with a newline.
function nlterm(s: string): string
{
return !s.endsWith('\n') ? s + '\n' : s;
}
const DEFAULT_ALIAS_PLACEHOLDER = '$$';

80

src/sql-generation/sql-spec-generator.ts

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

import {caseNormalizeName, exactUnquotedName, makeMap, makeNameNotInSet, Nullable, relIdDescn} from '../util/mod';
import {caseNormalizeName, exactUnquotedName, makeMap, Nullable, relIdDescn} from '../util/mod';
import {DatabaseMetadata, Field, ForeignKey, foreignKeyFieldNames, RelId} from '../dbmd';
import {
AdditionalObjectPropertyColumn,
AdditionalOutputColumn,
addLocPart,

@@ -25,6 +25,8 @@ ChildSpec,

ChildForeignKeyCondition,
createTableAlias,
ExpressionSelectEntry,
FieldSelectEntry,
getPropertySelectEntries,
HiddenPrimaryKeySelectEntry,
ParentPrimaryKeyCondition,
SelectEntry,
SqlParts,

@@ -72,4 +74,4 @@ SqlSpec,

case 'JSON_OBJECT_ROWS':
const additionalCols = query.additionalObjectPropertyColumns ?? [];
return this.jsonObjectRowsSql(query.tableJson, additionalCols, null, query.orderBy, specLoc);
const addCols = query.additionalOutputColumns ?? [];
return this.jsonObjectRowsSql(query.tableJson, addCols, null, query.orderBy, specLoc);
case 'JSON_ARRAY_ROW':

@@ -88,3 +90,3 @@ return this.jsonArrayRowSql(query.tableJson, null, false, query.orderBy, specLoc);

specLoc: SpecLocation,
exportPkFieldsHidden?: 'export-pk-fields-hidden',
exportPkFieldsHidden?: 'export-pk-fields-hidden'
)

@@ -111,3 +113,3 @@ : SqlSpec

sqlb.addSelectEntries(this.tableFieldExpressionSelectEntries(tjs, alias, specLoc));
sqlb.addSelectEntries(this.tableFieldExpressionSelectEntries(tjs.fieldExpressions, tjs.table, alias, specLoc));

@@ -146,8 +148,8 @@ sqlb.addParts(this.inlineParentsSqlParts(tjs, relId, alias, sqlb.getAliases(), specLoc));

/// Make query SQL having JSON object result values at the top level of the
/// result set. The query returns a JSON value in a single column and with
/// any number of result rows.
/// result set. The query returns a JSON value in a single column for each
/// of any number of result rows.
private jsonObjectRowsSql
(
tjs: TableJsonSpec,
additionalObjectPropertyColumns: AdditionalObjectPropertyColumn[],
addOutputColumns: AdditionalOutputColumn[],
pkCond: Nullable<ParentPrimaryKeyCondition>,

@@ -161,14 +163,9 @@ orderBy: Nullable<string>,

// Additional property column names must exist as projected column names in the base sql.
const propCols = additionalObjectPropertyColumns.map(c => typeof(c) == 'string' ? c : c.property);
const projectedPropNames = new Set(getPropertySelectEntries(baseSql).map(prop => prop.projectedName));
const invalidPropCols = propCols.filter(pc => !projectedPropNames.has(pc));
if (invalidPropCols.length > 0)
throw new SpecError(specLoc, `Extracted property column(s) not found: ${invalidPropCols.join(', ')}.`);
verifyTableFieldExpressionsValid(addOutputColumns, tjs.table, this.defaultSchema, this.dbmd, specLoc);
return {
...baseSql,
objectWrapProperties: true,
additionalObjectPropertyColumns
};
const alias = baseSql.fromEntries[0].alias;
const additionalOutputSelectEntries =
this.tableFieldExpressionSelectEntries(addOutputColumns, tjs.table, alias, specLoc);
return { ...baseSql, objectWrapProperties: true, additionalOutputSelectEntries };
}

@@ -189,3 +186,3 @@

{
const baseSql = this.baseSql(tjs, childFkCond, orderBy, specLoc);
const baseSql = this.baseSql(tjs, childFkCond, null, specLoc);

@@ -198,4 +195,4 @@ if (unwrap && getPropertySelectEntries(baseSql).length != 1)

aggregateToArray: true,
objectWrapProperties: !unwrap,
fromEntriesLeadingComment: `base query for table '${tjs.table}'`,
aggregateOrderBy: orderBy,
objectWrapProperties: !unwrap
};

@@ -221,17 +218,18 @@ }

(
tjs: TableJsonSpec,
fieldExpressions: Nullable<(string | TableFieldExpr)[]>,
table: string,
tableAlias: string,
specLoc: SpecLocation
)
: SelectEntry[]
: (FieldSelectEntry | ExpressionSelectEntry)[]
{
verifyTableFieldExpressionsValid(tjs, this.defaultSchema, this.dbmd, specLoc);
verifyTableFieldExpressionsValid(fieldExpressions, table, this.defaultSchema, this.dbmd, specLoc);
if (!tjs.fieldExpressions)
if (!fieldExpressions)
return [];
const dbFieldsByName: Map<string,Field> = this.getTableFieldsByName(tjs.table, specLoc);
const dbFieldsByName: Map<string,Field> = this.getTableFieldsByName(table, specLoc);
return tjs.fieldExpressions.map((tfe, ix) => {
const feLoc = addLocPart(specLoc, `fieldExpressions entry #${ix+1} of table ${tjs.table}`);
return fieldExpressions.map((tfe, ix) => {
const feLoc = addLocPart(specLoc, `fieldExpressions entry #${ix+1} of table ${table}`);
const projectedName = this.jsonPropertyName(tfe, feLoc);

@@ -244,3 +242,3 @@

if (dbField == undefined)
throw new SpecError(specLoc, `No metadata found for field ${tjs.table}.${fieldName}.`);
throw new SpecError(specLoc, `No metadata found for field ${table}.${fieldName}.`);

@@ -286,7 +284,7 @@ return {

for (const parent of getInlineParentSpecs(tjs))
for (const parSpec of getInlineParentSpecs(tjs))
{
const ipLoc = addLocPart(specLoc, `parent table '${parent.table}'`);
const ipLoc = addLocPart(specLoc, `parent table '${parSpec.table}'`);
sqlParts.addParts(this.inlineParentSqlParts(parent, relId, alias, sqlParts.getAliases(), ipLoc));
sqlParts.addParts(this.inlineParentSqlParts(parSpec, relId, alias, sqlParts.getAliases(), ipLoc));
}

@@ -297,3 +295,2 @@

// Return sql parts for the *including* (child) table's SQL query which are contributed by the inline parent table.
private inlineParentSqlParts

@@ -311,4 +308,4 @@ (

const subqueryAlias = parentSpec.subqueryAlias || makeNameNotInSet('q', avoidAliases);
sqlParts.addAlias(subqueryAlias);
const fromEntryAlias = parentSpec.fromEntryAlias || createTableAlias(parentSpec.table, avoidAliases);
sqlParts.addAlias(fromEntryAlias);

@@ -332,3 +329,3 @@ const parentPropsSql = this.baseSql(parentSpec, null, null, specLoc, 'export-pk-fields-hidden');

query: parentPropsSql,
alias: subqueryAlias,
alias: fromEntryAlias,
join: {

@@ -339,3 +336,3 @@ joinType: 'LEFT',

fromAlias: childAlias,
parentAlias: subqueryAlias,
parentAlias: fromEntryAlias,
matchedFields,

@@ -355,3 +352,3 @@ matchMustExist

projectedName: parentSelectEntry.projectedName,
parentAlias: subqueryAlias,
parentAlias: fromEntryAlias,
parentTable: parentRelId,

@@ -615,2 +612,3 @@ parentSelectEntry: parentSelectEntry,

}
} // QuerySQLSpecGenerator

@@ -617,0 +615,0 @@

853

src/sql-generation/sql-specs.ts
import {Field, ForeignKeyComponent, RelId} from "../dbmd";
import {AdditionalObjectPropertyColumn} from "../query-specs";
import {sorted} from "../util/collections";

@@ -15,4 +14,5 @@ import {Nullable} from "../util/mod";

objectWrapProperties?: Nullable<boolean>;
additionalObjectPropertyColumns?: Nullable<AdditionalObjectPropertyColumn[]>;
additionalOutputSelectEntries?: Nullable<AdditionalOutputSelectEntry[]>;
aggregateToArray?: Nullable<boolean>;
aggregateOrderBy?: Nullable<string>;
selectEntriesLeadingComment?: Nullable<string>;

@@ -39,2 +39,3 @@ fromEntriesLeadingComment?: Nullable<string>;

readonly sourceCodeFieldType: Nullable<string | { [srcLang: string]: string }>;
readonly comment?: Nullable<string>;
}

@@ -51,2 +52,3 @@

readonly sourceCodeFieldType: string | { [srcLang: string]: string };
readonly comment?: Nullable<string>;
}

@@ -90,4 +92,7 @@

readonly displayOrder?: undefined;
readonly comment?: Nullable<string>;
}
export type AdditionalOutputSelectEntry = FieldSelectEntry | ExpressionSelectEntry;
export type FromEntry =

@@ -103,3 +108,3 @@ TableFromEntry |

readonly join?: Nullable<Join>;
readonly comment?: undefined;
readonly comment?: Nullable<string>;
}

@@ -125,3 +130,4 @@

export type ParentChildCondition =
(ParentPrimaryKeyCondition | ChildForeignKeyCondition ) & { readonly fromAlias: string };
(ParentPrimaryKeyCondition | ChildForeignKeyCondition) &
{ readonly fromAlias: string }; // The FROM clause entry alias that the condition is applied on.

@@ -228,3 +234,3 @@ export interface ParentPrimaryKeyCondition

{
const alias = makeNameNotInSet(lowerCaseInitials(relName, '_') + '_', this.aliases);
const alias = createTableAlias(relName, this.aliases);
this.aliases.add(alias);

@@ -272,2 +278,839 @@ return alias;

else throw new Error(`No base table for sql: ${sql}`);
}
export const generalSqlKeywordsLowercase = new Set([
'a',
'abort',
'abs',
'absolute',
'access',
'action',
'ada',
'add',
'admin',
'after',
'aggregate',
'alias',
'all',
'allocate',
'also',
'alter',
'always',
'analyse',
'analyze',
'and',
'any',
'are',
'array',
'as',
'asc',
'asensitive',
'assertion',
'assignment',
'asymmetric',
'at',
'atomic',
'attribute',
'attributes',
'audit',
'authorization',
'auto_increment',
'avg',
'avg_row_length',
'backup',
'backward',
'before',
'begin',
'bernoulli',
'between',
'bigint',
'binary',
'bit',
'bit_length',
'bitvar',
'blob',
'bool',
'boolean',
'both',
'breadth',
'break',
'browse',
'bulk',
'by',
'c',
'cache',
'call',
'called',
'cardinality',
'cascade',
'cascaded',
'case',
'cast',
'catalog',
'catalog_name',
'ceil',
'ceiling',
'chain',
'change',
'char',
'char_length',
'character',
'character_length',
'character_set_catalog',
'character_set_name',
'character_set_schema',
'characteristics',
'characters',
'check',
'checked',
'checkpoint',
'checksum',
'class',
'class_origin',
'clob',
'close',
'cluster',
'clustered',
'coalesce',
'cobol',
'collate',
'collation',
'collation_catalog',
'collation_name',
'collation_schema',
'collect',
'column',
'column_name',
'columns',
'command_function',
'command_function_code',
'comment',
'commit',
'committed',
'completion',
'compress',
'compute',
'condition',
'condition_number',
'connect',
'connection',
'connection_name',
'constraint',
'constraint_catalog',
'constraint_name',
'constraint_schema',
'constraints',
'constructor',
'contains',
'containstable',
'continue',
'conversion',
'convert',
'copy',
'corr',
'corresponding',
'count',
'covar_pop',
'covar_samp',
'create',
'createdb',
'createrole',
'createuser',
'cross',
'csv',
'cube',
'cume_dist',
'current',
'current_date',
'current_default_transform_group',
'current_path',
'current_role',
'current_time',
'current_timestamp',
'current_transform_group_for_type',
'current_user',
'cursor',
'cursor_name',
'cycle',
'data',
'database',
'databases',
'date',
'datetime',
'datetime_interval_code',
'datetime_interval_precision',
'day',
'day_hour',
'day_microsecond',
'day_minute',
'day_second',
'dayofmonth',
'dayofweek',
'dayofyear',
'dbcc',
'deallocate',
'dec',
'decimal',
'declare',
'default',
'defaults',
'deferrable',
'deferred',
'defined',
'definer',
'degree',
'delay_key_write',
'delayed',
'delete',
'delimiter',
'delimiters',
'dense_rank',
'deny',
'depth',
'deref',
'derived',
'desc',
'describe',
'descriptor',
'destroy',
'destructor',
'deterministic',
'diagnostics',
'dictionary',
'disable',
'disconnect',
'disk',
'dispatch',
'distinct',
'distinctrow',
'distributed',
'div',
'do',
'domain',
'double',
'drop',
'dual',
'dummy',
'dump',
'dynamic',
'dynamic_function',
'dynamic_function_code',
'each',
'element',
'else',
'elseif',
'enable',
'enclosed',
'encoding',
'encrypted',
'end',
'end-exec',
'enum',
'equals',
'errlvl',
'escape',
'escaped',
'every',
'except',
'exception',
'exclude',
'excluding',
'exclusive',
'exec',
'execute',
'existing',
'exists',
'exit',
'exp',
'explain',
'external',
'extract',
'false',
'fetch',
'fields',
'file',
'fillfactor',
'filter',
'final',
'first',
'float',
'float4',
'float8',
'floor',
'flush',
'following',
'for',
'force',
'foreign',
'fortran',
'forward',
'found',
'free',
'freetext',
'freetexttable',
'freeze',
'from',
'full',
'fulltext',
'function',
'fusion',
'g',
'general',
'generated',
'get',
'global',
'go',
'goto',
'grant',
'granted',
'grants',
'greatest',
'group',
'grouping',
'handler',
'having',
'header',
'heap',
'hierarchy',
'high_priority',
'hold',
'holdlock',
'host',
'hosts',
'hour',
'hour_microsecond',
'hour_minute',
'hour_second',
'identified',
'identity',
'identity_insert',
'identitycol',
'if',
'ignore',
'ilike',
'immediate',
'immutable',
'implementation',
'implicit',
'in',
'include',
'including',
'increment',
'index',
'indicator',
'infile',
'infix',
'inherit',
'inherits',
'initial',
'initialize',
'initially',
'inner',
'inout',
'input',
'insensitive',
'insert',
'insert_id',
'instance',
'instantiable',
'instead',
'int',
'int1',
'int2',
'int3',
'int4',
'int8',
'integer',
'intersect',
'intersection',
'interval',
'into',
'invoker',
'is',
'isam',
'isnull',
'isolation',
'iterate',
'join',
'k',
'key',
'key_member',
'key_type',
'keys',
'kill',
'lancompiler',
'language',
'large',
'last',
'last_insert_id',
'lateral',
'lead',
'leading',
'least',
'leave',
'left',
'length',
'less',
'level',
'like',
'limit',
'lineno',
'lines',
'listen',
'ln',
'load',
'local',
'localtime',
'localtimestamp',
'location',
'locator',
'lock',
'login',
'logs',
'long',
'longblob',
'longtext',
'loop',
'low_priority',
'lower',
'm',
'map',
'match',
'matched',
'max',
'max_rows',
'maxextents',
'maxvalue',
'mediumblob',
'mediumint',
'mediumtext',
'member',
'merge',
'message_length',
'message_octet_length',
'message_text',
'method',
'middleint',
'min',
'min_rows',
'minus',
'minute',
'minute_microsecond',
'minute_second',
'minvalue',
'mlslabel',
'mod',
'mode',
'modifies',
'modify',
'module',
'month',
'monthname',
'more',
'move',
'multiset',
'mumps',
'myisam',
'name',
'names',
'national',
'natural',
'nchar',
'nclob',
'nesting',
'new',
'next',
'no',
'no_write_to_binlog',
'noaudit',
'nocheck',
'nocompress',
'nocreatedb',
'nocreaterole',
'nocreateuser',
'noinherit',
'nologin',
'nonclustered',
'none',
'normalize',
'normalized',
'nosuperuser',
'not',
'nothing',
'notify',
'notnull',
'nowait',
'null',
'nullable',
'nullif',
'nulls',
'number',
'numeric',
'object',
'octet_length',
'octets',
'of',
'off',
'offline',
'offset',
'offsets',
'oids',
'old',
'on',
'online',
'only',
'open',
'opendatasource',
'openquery',
'openrowset',
'openxml',
'operation',
'operator',
'optimize',
'option',
'optionally',
'options',
'or',
'order',
'ordering',
'ordinality',
'others',
'out',
'outer',
'outfile',
'output',
'over',
'overlaps',
'overlay',
'overriding',
'owner',
'pack_keys',
'pad',
'parameter',
'parameter_mode',
'parameter_name',
'parameter_ordinal_position',
'parameter_specific_catalog',
'parameter_specific_name',
'parameter_specific_schema',
'parameters',
'partial',
'partition',
'pascal',
'password',
'path',
'pctfree',
'percent',
'percent_rank',
'percentile_cont',
'percentile_disc',
'placing',
'plan',
'pli',
'position',
'postfix',
'power',
'preceding',
'precision',
'prefix',
'preorder',
'prepare',
'prepared',
'preserve',
'primary',
'print',
'prior',
'privileges',
'proc',
'procedural',
'procedure',
'process',
'processlist',
'public',
'purge',
'quote',
'raid0',
'raiserror',
'range',
'rank',
'raw',
'read',
'reads',
'readtext',
'real',
'recheck',
'reconfigure',
'recursive',
'ref',
'references',
'referencing',
'regexp',
'regr_avgx',
'regr_avgy',
'regr_count',
'regr_intercept',
'regr_r2',
'regr_slope',
'regr_sxx',
'regr_sxy',
'regr_syy',
'reindex',
'relative',
'release',
'reload',
'rename',
'repeat',
'repeatable',
'replace',
'replication',
'require',
'reset',
'resignal',
'resource',
'restart',
'restore',
'restrict',
'result',
'return',
'returned_cardinality',
'returned_length',
'returned_octet_length',
'returned_sqlstate',
'returns',
'revoke',
'right',
'rlike',
'role',
'rollback',
'rollup',
'routine',
'routine_catalog',
'routine_name',
'routine_schema',
'row',
'row_count',
'row_number',
'rowcount',
'rowguidcol',
'rowid',
'rownum',
'rows',
'rule',
'save',
'savepoint',
'scale',
'schema',
'schema_name',
'schemas',
'scope',
'scope_catalog',
'scope_name',
'scope_schema',
'scroll',
'search',
'second',
'second_microsecond',
'section',
'security',
'select',
'self',
'sensitive',
'separator',
'sequence',
'serializable',
'server_name',
'session',
'session_user',
'set',
'setof',
'sets',
'setuser',
'share',
'show',
'shutdown',
'signal',
'similar',
'simple',
'size',
'smallint',
'some',
'soname',
'source',
'space',
'spatial',
'specific',
'specific_name',
'specifictype',
'sql',
'sql_big_result',
'sql_big_selects',
'sql_big_tables',
'sql_calc_found_rows',
'sql_log_off',
'sql_log_update',
'sql_low_priority_updates',
'sql_select_limit',
'sql_small_result',
'sql_warnings',
'sqlca',
'sqlcode',
'sqlerror',
'sqlexception',
'sqlstate',
'sqlwarning',
'sqrt',
'ssl',
'stable',
'start',
'starting',
'state',
'statement',
'static',
'statistics',
'status',
'stddev_pop',
'stddev_samp',
'stdin',
'stdout',
'storage',
'straight_join',
'strict',
'string',
'structure',
'style',
'subclass_origin',
'sublist',
'submultiset',
'substring',
'successful',
'sum',
'superuser',
'symmetric',
'synonym',
'sysdate',
'sysid',
'system',
'system_user',
'table',
'table_name',
'tables',
'tablesample',
'tablespace',
'temp',
'template',
'temporary',
'terminate',
'terminated',
'text',
'textsize',
'than',
'then',
'ties',
'time',
'timestamp',
'timezone_hour',
'timezone_minute',
'tinyblob',
'tinyint',
'tinytext',
'to',
'toast',
'top',
'top_level_count',
'trailing',
'tran',
'transaction',
'transaction_active',
'transactions_committed',
'transactions_rolled_back',
'transform',
'transforms',
'translate',
'translation',
'treat',
'trigger',
'trigger_catalog',
'trigger_name',
'trigger_schema',
'trim',
'true',
'truncate',
'trusted',
'tsequal',
'type',
'uescape',
'uid',
'unbounded',
'uncommitted',
'under',
'undo',
'unencrypted',
'union',
'unique',
'unknown',
'unlisten',
'unlock',
'unnamed',
'unnest',
'unsigned',
'until',
'update',
'updatetext',
'upper',
'usage',
'use',
'user',
'user_defined_type_catalog',
'user_defined_type_code',
'user_defined_type_name',
'user_defined_type_schema',
'using',
'utc_date',
'utc_time',
'utc_timestamp',
'vacuum',
'valid',
'validate',
'validator',
'value',
'values',
'var_pop',
'var_samp',
'varbinary',
'varchar',
'varchar2',
'varcharacter',
'variable',
'variables',
'varying',
'verbose',
'view',
'volatile',
'waitfor',
'when',
'whenever',
'where',
'while',
'width_bucket',
'window',
'with',
'within',
'without',
'work',
'write',
'writetext',
'x509',
'xor',
'year',
'year_month',
'zerofill',
'zone',
]);
export function createTableAlias(relName: string, avoid: Set<string>): string
{
let alias = makeNameNotInSet(lowerCaseInitials(relName, '_'), avoid);
if (alias.length > 1 && generalSqlKeywordsLowercase.has(alias.toLowerCase()))
alias = alias + "_";
return alias;
}

2

src/util/collections.ts

@@ -210,5 +210,5 @@ export function computeIfAbsent<K,V>

export function isNonEmpty<T>(ts: T[] | null | undefined): ts is NonNullable<T[]>
export function nonEmpty<T>(ts: T[] | null | undefined): ts is NonNullable<T[]>
{
return (ts != null && ts.length > 0);
}

6

src/util/property-names.ts

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

import { CaseSensitivity } from '../dbmd/database-metadata';
import { PropertyNameDefault } from '../query-specs';
import { lowerCamelCase, snakeCase, upperCamelCase } from './strings';
import {CaseSensitivity} from '../dbmd/database-metadata';
import {PropertyNameDefault} from '../query-specs';
import {lowerCamelCase, snakeCase, upperCamelCase} from './strings';

@@ -5,0 +5,0 @@ export function propertyNameDefaultFunction

25

src/util/strings.ts

@@ -12,5 +12,9 @@ export function upperCamelCase(name: string): string

{
const cleanedName =
name.replaceAll(/[._ ]/g,'_')
.replaceAll(/[^a-zA-Z0-9_]/g, "");
const parts: string[] = [];
for ( const word of name.split(sep) )
for ( const word of cleanedName.split(sep) )
{

@@ -91,2 +95,21 @@ if (word.length > 0)

export function makeNameNotInSets
(
baseName: string,
avoidSets: Set<string>[],
suffixSep: string = ""
)
: string
{
const nameOk = (name: string) => !avoidSets.some(set => set.has(name));
if (nameOk(baseName))
return baseName;
else
{
let i = 1;
while (!nameOk(baseName + suffixSep + i)) ++i;
return baseName + suffixSep + i;
}
}
export function indentLines

@@ -93,0 +116,0 @@ (

2

tsconfig.json

@@ -7,3 +7,3 @@ {

"target": "ES2018",
"lib": ["ES2020"],
"lib": ["ES2021"],
"module": "commonjs",

@@ -10,0 +10,0 @@ "esModuleInterop": true,

lib/mod.js.map

Sorry, the diff of this file is not supported yet

lib/query-properties-metadata-generation/query-properties-metadata-generator.js.map

Sorry, the diff of this file is not supported yet

lib/query-specs.js.map

Sorry, the diff of this file is not supported yet

lib/result-type-generation/result-type-spec-generator.js.map

Sorry, the diff of this file is not supported yet

lib/sql-generation/sql-dialects/hsql.js.map

Sorry, the diff of this file is not supported yet

lib/sql-generation/sql-dialects/mysql.js.map

Sorry, the diff of this file is not supported yet

lib/sql-generation/sql-dialects/ora.js.map

Sorry, the diff of this file is not supported yet

lib/sql-generation/sql-dialects/pg.js.map

Sorry, the diff of this file is not supported yet

lib/sql-generation/sql-source-generator.js.map

Sorry, the diff of this file is not supported yet

lib/sql-generation/sql-spec-generator.js.map

Sorry, the diff of this file is not supported yet

lib/sql-generation/sql-specs.js.map

Sorry, the diff of this file is not supported yet

lib/util/collections.js.map

Sorry, the diff of this file is not supported yet

lib/util/property-names.js.map

Sorry, the diff of this file is not supported yet

lib/util/strings.js.map

Sorry, the diff of this file is not supported yet

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