@nodef/extra-sql@0.1.2

Built and signed on GitHub Actions

Built and signed on GitHub Actions

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585
//#region CONSTANTS
/** Set of operators in SQL. */
export const OPERATORS: Set<string> = new Set([
  // Arithmetic operators
  '+', '-', '*', '/', '%',
  // Bitwise operators
  '&', '|', '^',
  // Comparison operators
  '=', '>', '<', '>=', '<=', '<>',
  // Compound operators
  '+=', '-=', '*=', '/=', '%=', '&=', '^=', '|=',
  // Logical operators
  'ALL', 'AND', 'ANY', 'BETWEEN', 'EXISTS', 'IN', 'LIKE', 'NOT', 'OR', 'SOME'
]);


/**
 * Number of operands used with an SQL operator.
 */
export const OPERAND_COUNT: Map<string, number> = new Map([
  // Arithmetic operators
  ['+', 2], ['-', 2], ['*', 2], ['/', 2], ['%', 2],
  // Bitwise operators
  ['&', 2], ['|', 2], ['^', 2],
  // Comparison operators
  ['=', 2], ['>', 2], ['<', 2], ['>=', 2], ['<=', 2], ['<>', 2],
  // Compound operators
  ['+=', 2], ['-=', 2], ['*=', 2], ['/=', 2], ['%=', 2], ['&=', 2], ['^=', 2], ['|=', 2],
  // Logical operators
  ['ALL', 2], ['AND', 2], ['ANY', 2], ['BETWEEN', 3], ['EXISTS', 1], ['IN', 2], ['LIKE', 2], ['NOT', 1], ['OR', 2], ['SOME', 2]
]);
//#endregion




//#region TYPES
/** Types for columns in a table. */
export type ColumnTypes = {[key: string]: string};

/** Weights for columns in a table (for a tsvector). */
export type ColumnWeights = {[key: string]: string};

/** Data for a row in a table. */
export type RowData = Record<string, unknown>;


/** Options for creating a table. */
export interface CreateTableOptions {
  /** Column name for the primary key. */
  pk?: string;
}


/** Options for creating an index. */
export interface CreateIndexOptions {
  /** Indexing method (e.g., GIN, BTREE). */
  method?: string;
}


/** Options for inserting into a table. */
export interface InsertIntoOptions {
  /** Column name for the primary key. */
  pk?: string;
}


/** Options for setting up table indexes. */
export interface SetupTableIndexOptions {
  /** Column name for the primary key. */
  pk?: string;
  /** Whether to create an index. */
  index?: boolean;
  /** Columns, with their weights, for full-text search. */
  tsvector?: ColumnWeights;
}


/** Options for setting up a table. */
export interface SetupTableOptions {
  /** Column name for the primary key. */
  pk?: string;
  /** Whether to create an index. */
  index?: boolean;
  /** Columns, with their weights, for full-text search. */
  tsvector?: ColumnWeights;
}


/** Options for selecting with tsquery. */
export interface SelectTsqueryOptions {
  /** Columns to select. */
  columns?: string;
  /** Whether to order the results. */
  order?: boolean;
  /** Limit the number of results. */
  limit?: number;
  /** Normalization weight used during ranking (in ts_rank). */
  normalization?: number;
}


/** Options for matching with tsquery. */
export interface MatchTsqueryOptions {
  /** Columns to select. */
  columns?: string;
  /** Whether to order the results. */
  order?: boolean;
  /** Limit the number of results. */
  limit?: number;
  /** Normalization weight used during ranking (in ts_rank). */
  normalization?: number;
}


/** Query data with SQL and parameters. */
export interface QueryData {
  /** SQL query string. */
  query: string;
  /** Parameters for the query. */
  data: unknown[];
}
//#endregion




//#region HELPERS
/**
 * Ensure a value is an array.
 * @param x value to convert to an array
 * @returns array containing the value, or the original array
 */
function asArray<T>(x: unknown): T[] {
  if (Array.isArray(x)) return x;
  if (x==null || typeof x === 'string' || typeof (x as Iterable<T>)[Symbol.iterator] !== 'function') return [x as T];
  return Array.from(x as Iterable<T>);
}


/**
 * Format an object into a string based on a format pattern.
 * @param obj object to format
 * @param fmt format string with placeholders %k, %v, %i
 * @param sep separator between formatted items
 * @param i starting index for %i placeholder [0]
 * @param val array to collect values (optional)
 * @returns formatted string
 */
function formatData(obj: RowData, fmt: string, sep: string, i: number=0, val?: unknown[]): string {
  const a: string[] = [];
  const ve = Array.isArray(val);
  i = i || (ve? val.length : 0);
  for (const k in obj) {
    const v = obj[k];
    a.push(fmt.replace(/%k/g, k).replace(/%v/g, String(v)).replace(/%i/g, String(i++)));
    if (ve) val.push(v);
  }
  return a.join(sep);
}


/**
 * Helper function to add a row to an INSERT INTO SQL command.
 * @param row row object with key-value pairs
 * @param acc string to accumulate to (internal use)
 * @param i current index [0]
 * @returns updated SQL string with the new row added
 */
function addRow(row: RowData, acc: string='', i: number=0): string {
  if (i===0) {
    for (const k in row)
      acc += `"${k}", `;
    acc  = acc.endsWith(', ')? acc.substring(0, acc.length - 2) : acc;
    acc += ') VALUES\n(';
  }
  for (const k in row)
    acc += row[k] == null? 'NULL, ' : `$$${row[k]}$$, `;
  acc  = acc.endsWith(', ')? acc.substring(0, acc.length - 2) : acc;
  acc += '),\n(';
  return acc;
}
//#endregion




/**
 * Generate SQL command for CREATE TABLE.
 * @param name table name
 * @param cols columns `{name: type}`
 * @param opt options `{pk}`
 * @param acc string to accumulate to (internal use)
 * @returns SQL command for creating the table
 * @example
 * ```ts
 * xsql.createTable("plant", {name: "TEXT", type: "TEXT", age: "INT"});
 * // → CREATE TABLE IF NOT EXISTS "plant" ("name" TEXT, "type" TEXT, "age" INT);
 *
 * xsql.createTable("animal", {name: "TEXT", type: "TEXT", age: "INT"}, {pk: "name"});
 * // → CREATE TABLE IF NOT EXISTS "animal" ("name" TEXT, "type" TEXT, "age" INT, PRIMARY KEY("name"));
 * ```
 */
export function createTable(name: string, cols: ColumnTypes, opt: CreateTableOptions={}, acc: string=''): string {
  acc += `CREATE TABLE IF NOT EXISTS "${name}" (`;
  for (const k in cols)
    acc += `"${k}" ${cols[k]}, `;
  if (opt.pk) acc += `PRIMARY KEY("${opt.pk}"), `;
  return acc.replace(/, $/, '') + `);\n`;
}


/**
 * Generate SQL command for CREATE INDEX.
 * @param name index name
 * @param table table name
 * @param expr index expression
 * @param opt options `{method}`
 * @param acc string to accumulate to (internal use)
 * @returns SQL command for creating the index
 * @example
 * ```ts
 * xsql.createIndex("food_code_idx", "food", `"code"`);
 * // → CREATE INDEX IF NOT EXISTS "food_code_idx" ON "food" ("code");
 *
 * xsql.createIndex("food_type_idx", "food", `"type"`, {method: "GIN"});
 * // → CREATE INDEX IF NOT EXISTS "food_type_idx" ON "food" USING GIN ("type");
 * ```
 */
export function createIndex(name: string, table: string, expr: string, opt: CreateIndexOptions={}, acc: string=''): string {
  acc += `CREATE INDEX IF NOT EXISTS "${name}" ON "${table}" `;
  if (opt.method) acc += `USING ${opt.method} `;
  return acc + `(${expr});\n`;
}


/**
 * Generate SQL command for CREATE VIEW.
 * @param name view name
 * @param query view query
 * @param opt options (currently unused)
 * @param acc string to accumulate to (internal use)
 * @returns SQL command for creating the view
 * @example
 * ```ts
 * xsql.createView("food_code", "SELECT \"code\" FROM \"food\"");
 * // → CREATE OR REPLACE VIEW "food_code" AS SELECT "code" FROM "food";
 * ```
 */
export function createView(name: string, query: string, _opt: object | null=null, acc: string=''): string {
  acc += `CREATE OR REPLACE VIEW "${name}" AS ${query};\n`;
  return acc;
}


/**
 * Generates SQL command for INSERT INTO using an array of values.
 * @param table table name
 * @param rows row objects `{column: value}`
 * @param opt options `{pk}`
 * @param acc string to accumulate to (internal use)
 * @returns SQL command to insert into the table
 * @example
 * ```ts
 * xsql.insertInto("food", [{code: "F1", name: "Mango"}]);
 * // → INSERT INTO "food" ("code", "name") VALUES\n($$F1$$, $$Mango$$);
 *
 * xsql.insertInto("food", [{code: "F1", name: "Mango"}, {code: "F2", name: "Lychee"}], {pk: "code"});
 * // → INSERT INTO "food" ("code", "name") VALUES
 * // → ($$F1$$, $$Mango$$),
 * // → ($$F2$$, $$Lychee$$)
 * // → ON CONFLICT ("code") DO NOTHING;
 * ```
 */
export function insertInto(table: string, rows: Iterable<RowData>, opt: InsertIntoOptions={}, acc: string=''): string {
  let i = -1;
  acc += `INSERT INTO "${table}" (`;
  for (const val of rows)
    acc = addRow(val, acc, ++i);
  acc = acc.replace(/\),\n\($/, '') + ')';
  if (opt.pk) acc += `\nON CONFLICT ("${opt.pk}") DO NOTHING`;
  return acc + ';\n';
}


/**
 * Generate a tsvector expression for full-text search.
 * @param cols columns with their weights `{name: weight}`
 * @returns tsvector expression
 */
function tsvector(cols: ColumnWeights): string {
  let acc = '';
  for (const k in cols) {
    if (cols[k]) acc += `setweight(to_tsvector('english', "${k}"), '${cols[k]}')||`;
  }
  return acc.replace(/\|\|$/, '');
}


/**
 * Generate SQL commands for setting up table indexes and views.
 * @param table table name
 * @param cols columns with their types `{name: type}`
 * @param opt options `{pk, index, tsvector}`
 * @param acc Accumulator for the SQL string (internal use).
 * @returns SQL commands for setting up the table indexes and views
 */
export function setupTableIndex(table: string, cols: ColumnTypes, opt: SetupTableIndexOptions={}, acc: string=''): string {
  if (opt.tsvector) {
    const tv = tsvector(opt.tsvector);
    acc += createView(table + '_tsvector', `SELECT *, ${tv} AS "tsvector" FROM "${table}"`);
    if (opt.index) acc += createIndex(table + '_tsvector_idx', table, `(${tv})`, { method: 'GIN' });
  }
  if (opt.index) {
    for (const k in cols) {
      if (cols[k] == null || k === opt.pk) continue;
      const knam = k.replace(/\W+/g, '_').toLowerCase();
      acc += createIndex(`${table}_${knam}_idx`, table, `"${k}"`);
    }
  }
  return acc;
}


/**
 * Generate SQL commands to set up a table (create, insert, index).
 * @param name table name
 * @param cols columns with their types `{name: type}`
 * @param rows rows to insert (optional)
 * @param opt options `{pk, index, tsvector}`
 * @param acc string to accumulate to (internal use)
 * @returns SQL commands for setting up the table
 * @example
 * ```ts
 * xsql.setupTable("food", {code: "TEXT", name: "TEXT"});
 * // → CREATE TABLE IF NOT EXISTS "food" ("code" TEXT, "name" TEXT);
 *
 * xsql.setupTable("food", {code: "TEXT", name: "TEXT"},
 *   [{code: "F1", name: "Mango"}, {code: "F2", name: "Lychee"}]);
 * // → CREATE TABLE IF NOT EXISTS "food" ("code" TEXT, "name" TEXT);
 * // → INSERT INTO "food" ("code", "name") VALUES
 * // → ('F1', 'Mango'),
 * // → ('F2', 'Lychee');
 *
 * xsql.setupTable("food", {code: "TEXT", name: "TEXT"},
 *   [{code: "F1", name: "Mango"}, {code: "F2", name: "Lychee"}],
 *   {index: true});
 * // → CREATE TABLE IF NOT EXISTS "food" ("code" TEXT, "name" TEXT);
 * // → INSERT INTO "food" ("code", "name") VALUES
 * // → ('F1', 'Mango'),
 * // → ('F2', 'Lychee');
 * // → CREATE INDEX IF NOT EXISTS food_code_idx ON "food" ("code");
 * // → CREATE INDEX IF NOT EXISTS food_name_idx ON "food" ("name");
 *
 * xsql.setupTable("food", {code: "TEXT", name: "TEXT"},
 *   [{code: "F1", name: "Mango"}, {code: "F2", name: "Lychee"}],
 *   {pk: "code", index: true, tsvector: {code: "A", name: "B"}});
 * // → CREATE TABLE IF NOT EXISTS "food" ("code" TEXT, "name" TEXT, PRIMARY KEY("code"));
 * // → INSERT INTO "food" ("code", "name") VALUES
 * // → ('F1', 'Mango'),
 * // → ('F2', 'Lychee');
 * // → ON CONFLICT ("code") DO NOTHING;
 * // → CREATE OR REPLACE VIEW "food_tsvector" AS SELECT *, setweight(to_tsvector('english', "code"), 'A')||setweight(to_tsvector('english', "name"), 'B') AS "tsvector" FROM "food";
 * // → CREATE INDEX IF NOT EXISTS "food_tsvector_idx" ON "food" USING GIN ((setweight(to_tsvector('english', "code"), 'A')||setweight(to_tsvector('english', "name"), 'B')));
 * // → CREATE INDEX IF NOT EXISTS "food_name_idx" ON "food" ("name");
 * ```
 */
export function setupTable(name: string, cols: ColumnTypes, rows: Iterable<RowData> | null=null, opt: SetupTableOptions={}, acc: string=''): string {
  acc = createTable(name, cols, opt, acc);
  if (rows) acc = insertInto(name, rows, opt, acc);
  return setupTableIndex(name, cols, opt, acc);
}


/**
 * Generate SQL command to check if a table exists.
 * @param name table name
 * @returns SQL command to check if the table exists
 * @example
 * ```ts
 * xsql.tableExists("food");
 * // → SELECT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name='food');
 * ```
 */
export function tableExists(name: string): string {
  return `SELECT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name='${name}');\n`;
}


/**
 * Generate SQL command for SELECT with tsquery.
 * @param table table name
 * @param query plain query words
 * @param tsv tsvector column name ["tsvector"]
 * @param opt options `{columns, order, limit, normalization}`
 * @returns SQL command for selecting with tsquery
 * @example
 * ```ts
 * xsql.selectTsquery("columns", "total fat");
 * // → SELECT * FROM "columns" WHERE "tsvector" @@ plainto_tsquery('total fat');
 *
 * xsql.selectTsquery("columns", "total fat", '"tsvector"', {columns: '"code"'});
 * // → SELECT "code" FROM "columns" WHERE "tsvector" @@ plainto_tsquery('total fat');
 *
 * xsql.selectTsquery("columns", "total fat", '"tsvector"', {order: true, limit: 1, normalization: 2});
 * // → SELECT * FROM "columns" WHERE "tsvector" @@ plainto_tsquery('total fat') ORDER BY ts_rank("tsvector", plainto_tsquery('total fat'), 2) DESC LIMIT 1;
 * ```
 */
export function selectTsquery(table: string, query: string, tsv: string='"tsvector"', opt: SelectTsqueryOptions={}): string {
  const col = opt.columns || '*';
  const nrm = opt.normalization || 0;
  let acc = `SELECT ${col} FROM "${table}" WHERE ${tsv} @@ plainto_tsquery('${query}')`;
  if (opt.order) acc += ` ORDER BY ts_rank(${tsv}, plainto_tsquery('${query}'), ${nrm}) DESC`;
  if (opt.limit) acc += ` LIMIT ${opt.limit}`;
  acc += `;\n`;
  return acc;
}


/**
 * Generate SQL query for matching words with tsquery.
 * @param table table name
 * @param words match words
 * @param tsv tsvector column name ["tsvector"]
 * @param opt options `{columns, order, limit, normalization}`
 * @returns SQL query for matching words with tsquery
 * @example
 * ```ts
 * xsql.matchTsquery("columns", ["total", "fat"]);
 * // → SELECT *, '2'::INT AS "matchTsquery" FROM "columns" WHERE "tsvector" @@ plainto_tsquery('total fat') UNION ALL
 * // → SELECT *, '1'::INT AS "matchTsquery" FROM "columns" WHERE "tsvector" @@ plainto_tsquery('total');
 *
 * xsql.matchTsquery("columns", ["total", "fat"], '"tsvector"', {columns: '"code"'});
 * // → SELECT "code", '2'::INT AS "matchTsquery" FROM "columns" WHERE "tsvector" @@ plainto_tsquery('total fat') UNION ALL
 * // → SELECT "code", '1'::INT AS "matchTsquery" FROM "columns" WHERE "tsvector" @@ plainto_tsquery('total');
 *
 * xsql.matchTsquery("columns", ["total", "fat"], '"tsvector"', {order: true, limit: 1, normalization: 2});
 * // → SELECT *, '2'::INT AS "matchTsquery" FROM "columns" WHERE "tsvector" @@ plainto_tsquery('total fat') ORDER BY ts_rank("tsvector", plainto_tsquery('total fat'), 2) DESC UNION ALL
 * // → SELECT *, '1'::INT AS "matchTsquery" FROM "columns" WHERE "tsvector" @@ plainto_tsquery('total') ORDER BY ts_rank("tsvector", plainto_tsquery('total'), 2) DESC LIMIT 1;
 * ```
 */
export function matchTsquery(table: string, words: string[], tsv: string='"tsvector"', opt: MatchTsqueryOptions={}): string {
  const col = opt.columns || '*';
  const nrm = opt.normalization || 0;
  let acc = '';
  for (let i=words.length; i>0; i--) {
    const qry = words.slice(0, i).join(' ').replace(/([\'\"])/g, '$1$1');
    acc += `SELECT ${col}, '${i}'::INT AS "matchTsquery" FROM "${table}"`;
    acc += ` WHERE ${tsv} @@ plainto_tsquery('${qry}')`;
    if (opt.order) acc += ` ORDER BY ts_rank(${tsv}, plainto_tsquery('${qry}'), ${nrm}) DESC`;
    acc += ' UNION ALL\n';
  }
  acc = acc.substring(0, acc.length - 11);
  if (opt.limit) acc += ` LIMIT ${opt.limit}`;
  acc += ';\n';
  return acc;
}


/**
 * Generate SQL command for creating a table with data.
 * @param table table name
 * @param cols columns with their types `{name: type}`
 * @param pkeys primary key(s)
 * @returns query data for creating the table `{query, data}`
 */
export function createTableData(table: string, cols: ColumnTypes, pkeys?: string | Iterable<string>): QueryData {
  return {
    query: `CREATE TABLE IF NOT EXISTS "${table}" (` +
      `${formatData(cols, '"%k" %v', ', ')}` +
      (pkeys? `, PRIMARY KEY(${formatData(asArray(pkeys) as unknown as RowData, '"%v"', ', ')})` : ``) + `);`,
    data: []
  };
}


/**
 * Generate SQL command for updating data.
 * @param table table name
 * @param set columns to set `{column: value}`
 * @param where where conditions `{column: value}`
 * @param op operator for conditions ['=']
 * @param sep separator for conditions ['AND']
 * @returns query data for updating the data `{query, data}`
 */
export function updateData(table: string, set: RowData, where: RowData, op: string='=', sep: string='AND'): QueryData {
  const par: unknown[] = [];
  const setStr = formatData(set   || {}, '"%k" = $%i', ', ', 1, par);
  const exp    = formatData(where || {}, `"%k" ${op} $%i`, ` ${sep} `, par.length + 1, par);
  return {
    query: `UPDATE "${table}" SET ${setStr}${exp ? ' WHERE ' + exp : ''};`,
    data: par
  };
}


/**
 * Generate SQL command for selecting data.
 * @param tab table name
 * @param whr where conditions `{column: value}`
 * @param op operator for conditions ['=']
 * @param sep separator for conditions ['AND']
 * @returns query data for selecting the data `{query, data}`
 */
export function selectData(tab: string, whr: RowData, op: string='=', sep: string='AND'): QueryData {
  const par: unknown[] = [];
  const exp = formatData(whr || {}, `"%k" ${op} $%i`, ` ${sep} `, 1, par);
  return {
    query: `SELECT * FROM "${tab}"${exp ? ' WHERE ' + exp : ''};`,
    data: par
  };
}


/**
 * Generate SQL command for inserting data.
 * @param table table name
 * @param rows rows to insert
 * @returns query data for inserting the data `{query, data}`
 */
export function insertIntoData(table: string, rows: RowData[]): QueryData {
  const par: unknown[] = [];
  const into    = formatData(rows[0] || {}, '"%k"', ', ', 1, par);
  const rowsStr = formatData(par as unknown as RowData, '$%i', ', ', 1);
  return {
    query: `INSERT INTO "${table}" (${into}) VALUES (${rowsStr});`,
    data: par
  };
}


/**
 * Generate SQL command for deleting data.
 * @param table table name
 * @param where where conditions `{column: value}`
 * @param op operator for conditions ['=']
 * @param sep separator for conditions ['AND']
 * @returns query data for deleting the data `{query, data}`
 */
export function deleteData(table: string, where: RowData, op: string='=', sep: string='AND'): QueryData {
  const par: unknown[] = [];
  const exp = formatData(where || {}, `"%k" ${op} $%i`, ` ${sep} `, 1, par);
  return {
    query: `DELETE FROM "${table}"${exp ? ' WHERE ' + exp : ''};`,
    data: par
  };
}




// /**
//  * Convert Linux wildcard patterns to SQL LIKE patterns.
//  * @param txt pattern to convert
//  * @returns converted pattern, or null if input is null
//  */
// function fromLinuxWildcard(txt: string | null): string | null {
//   return txt ? txt.replace(/\*/g, '%').replace(/\?/g, '_') : txt;
// }


// /**
//  * Generate SQL command for INSERT INTO using a stream of values.
//  * @param table table name
//  * @param stream readable stream of row objects `{column: value}`
//  * @param opt options `{pk}`
//  * @param acc string to accumulate to (internal use)
//  * @returns SQL command to insert into the table (promise)
//  */
// export function insertIntoStream(table: string, stream: Readable, opt: InsertIntoOptions={}, acc: string=''): Promise<string> {
//   let i = -1;
//   acc += `INSERT INTO "${table}" (`;
//   return new Promise((resolve, reject) => {
//     stream.on('error', reject);
//     stream.on('data', (row: RowData) => {
//       acc = addRow(row, acc, ++i);
//     });
//     stream.on('end', () => {
//       acc = acc.replace(/\),\n\($/, '') + ')';
//       if (opt.pk) acc += `\nON CONFLICT ("${opt.pk}") DO NOTHING`;
//       resolve(acc + ';\n');
//     });
//   });
// }