461 lines
13 KiB
JavaScript
461 lines
13 KiB
JavaScript
// Простенький "селект билдер" по мотивам MediaWiki-овского, успешно юзаю подобный в PHP уже лет 8
|
||
// (c) Виталий Филиппов, 2019
|
||
// Версия 2019-05-14
|
||
|
||
// В PHP, правда, прикольнее - там в массиве можно смешивать строковые и численные ключи,
|
||
// благодаря чему можно писать $where = [ 't1.a=t2.a', 't2.b' => [ 1, 2, 3 ] ]
|
||
// Здесь так нельзя, поэтому этот синтаксис мы заменяем на { 't1.a=t2.a': [], 't2.b': [ 1, 2, 3 ] }
|
||
|
||
const pg = require('pg');
|
||
|
||
// Сраный node-postgres конвертирует даты в Date и портит таймзону
|
||
const DATATYPE_DATE = 1082;
|
||
pg.types.setTypeParser(DATATYPE_DATE, function(val)
|
||
{
|
||
return val === null ? null : val;
|
||
});
|
||
|
||
let pg_escape;
|
||
|
||
const MS_HASH = 0;
|
||
const MS_LIST = 1;
|
||
const MS_ROW = 2;
|
||
const MS_COL = 4;
|
||
const MS_VALUE = 6;
|
||
|
||
function select_builder(tables, fields, where, options)
|
||
{
|
||
let sql = 'SELECT ', bind = [];
|
||
if (fields instanceof Array)
|
||
{
|
||
sql += fields.join(', ');
|
||
}
|
||
else if (typeof fields == 'string')
|
||
{
|
||
sql += fields;
|
||
}
|
||
else if (typeof fields == 'object')
|
||
{
|
||
sql += Object.keys(fields).map(k => fields[k]+' AS '+k).join(', ');
|
||
}
|
||
else
|
||
{
|
||
throw new Error('fields = '+fields+' is invalid');
|
||
}
|
||
sql += ' FROM ';
|
||
const t = tables_builder(tables);
|
||
sql += t.sql;
|
||
bind.push.apply(bind, t.bind);
|
||
where = where_builder(where);
|
||
sql += ' WHERE '+(where.sql || '1=1');
|
||
bind.push.apply(bind, where.bind);
|
||
if (t.moreWhere)
|
||
{
|
||
sql += ' AND '+t.moreWhere.sql;
|
||
bind.push.apply(bind, t.moreWhere.bind);
|
||
}
|
||
options = options||{};
|
||
if (options['GROUP BY'] || options.group_by)
|
||
{
|
||
let group = options['GROUP BY'] || options.group_by;
|
||
group = group instanceof Array ? group : [ group ];
|
||
sql += ' GROUP BY '+group.join(', ');
|
||
}
|
||
if (options['ORDER BY'] || options.order_by)
|
||
{
|
||
let order = options['ORDER BY'] || options.order_by;
|
||
order = order instanceof Array ? order : [ order ];
|
||
sql += ' ORDER BY '+order.join(', ');
|
||
}
|
||
if (options.LIMIT || options.limit)
|
||
{
|
||
sql += ' LIMIT '+((options.LIMIT || options.limit) | 0);
|
||
}
|
||
if (options.OFFSET || options.offset)
|
||
{
|
||
sql += ' OFFSET '+((options.OFFSET || options.offset) | 0);
|
||
}
|
||
return new Text(sql, bind);
|
||
}
|
||
|
||
function tables_builder(tables)
|
||
{
|
||
let sql = '', bind = [];
|
||
let moreWhere = null;
|
||
let first = true;
|
||
if (typeof tables == 'string')
|
||
{
|
||
sql = tables;
|
||
return { sql, bind, moreWhere };
|
||
}
|
||
for (const k in tables)
|
||
{
|
||
let jointype = 'INNER', table = tables[k], conds = null;
|
||
if (table instanceof Array)
|
||
{
|
||
[ jointype, table, conds ] = table;
|
||
}
|
||
if (!first)
|
||
{
|
||
sql += ' ' + jointype.toUpperCase() + ' JOIN ';
|
||
}
|
||
let more_on;
|
||
if (table instanceof Pg_Values)
|
||
{
|
||
sql += '(VALUES ';
|
||
let i = 0;
|
||
for (const row of table.rows)
|
||
{
|
||
sql += (i > 0 ? ', (' : '(') + table.keys.map(() => '?').join(', ')+')';
|
||
bind.push.apply(bind, table.keys.map(k => row[k]));
|
||
i++;
|
||
}
|
||
sql += ') AS '+k+'('+table.keys.join(', ')+')';
|
||
}
|
||
else if (typeof table == 'object')
|
||
{
|
||
// Nested join, `k` alias is ignored
|
||
let subjoin = tables_builder(table);
|
||
if (subjoin.moreWhere)
|
||
{
|
||
more_on = subjoin.moreWhere;
|
||
}
|
||
if (Object.keys(table).length > 1)
|
||
{
|
||
sql += "("+subjoin.sql+")";
|
||
}
|
||
else
|
||
{
|
||
sql += subjoin.sql;
|
||
}
|
||
bind.push.apply(subjoin.bind);
|
||
}
|
||
else
|
||
{
|
||
sql += table + ' ' + k;
|
||
}
|
||
conds = where_builder(conds);
|
||
if (more_on)
|
||
{
|
||
if (!conds.sql)
|
||
conds = more_on;
|
||
else
|
||
{
|
||
conds.sql += ' AND ' + more_on.sql;
|
||
conds.bind.push.apply(conds.bind, more_on.bind);
|
||
}
|
||
}
|
||
if (!first)
|
||
{
|
||
sql += ' ON ' + (conds.sql || '1=1');
|
||
bind.push.apply(bind, conds.bind);
|
||
}
|
||
else
|
||
{
|
||
// Бывает удобно указывать WHERE как условие "JOIN" первой таблицы
|
||
moreWhere = conds.sql ? conds : null;
|
||
first = false;
|
||
}
|
||
}
|
||
return { sql, bind, moreWhere };
|
||
}
|
||
|
||
// fields: one of:
|
||
// - string: 'a=b AND c=d'
|
||
// - array: [ 'a=b', [ 'a=? or b=?', 1, 2 ], [ 'a', [ 1, 2 ] ] ]
|
||
// - object: { a: 1, b: [ 1, 2 ], 'a = b': [], '(a, b)': [ [ 1, 2 ], [ 2, 3 ] ], 'c=? or d=?': [ 2, 3 ] }
|
||
// - key does not contain '?', value is a scalar or non-empty array => (key IN ...)
|
||
// - key does not contain '?', value is an empty array => just (key)
|
||
// - key contains '?', value is a scalar or non-empty array => (key) with bind params (...value)
|
||
// - key is numeric, then value is treated as in array
|
||
function where_or_set(fields, where)
|
||
{
|
||
if (typeof fields == 'string')
|
||
{
|
||
return { sql: fields, bind: [] };
|
||
}
|
||
const w = [], bind = [];
|
||
for (let k in fields)
|
||
{
|
||
let v = fields[k];
|
||
if (/^\d+$/.exec(k))
|
||
{
|
||
if (v instanceof Array)
|
||
{
|
||
k = v[0];
|
||
v = v.slice(1);
|
||
}
|
||
else
|
||
{
|
||
w.push(v);
|
||
continue;
|
||
}
|
||
}
|
||
if (k.indexOf('?') >= 0 || v instanceof Array && v.length == 0)
|
||
{
|
||
if (!(v instanceof Array))
|
||
{
|
||
v = [ v ];
|
||
}
|
||
// FIXME: check bind variable count
|
||
w.push(k);
|
||
bind.push.apply(bind, v);
|
||
continue;
|
||
}
|
||
v = v instanceof Array ? v : [ v ];
|
||
if (v.length == 1 && v[0] == null)
|
||
{
|
||
w.push(where ? k+' is null' : k+' = null');
|
||
}
|
||
else
|
||
{
|
||
if ((v.length > 1 || v[0] instanceof Array) && !where)
|
||
{
|
||
throw new Error('IN syntax can only be used inside WHERE');
|
||
}
|
||
if (v[0] instanceof Array)
|
||
{
|
||
// (a, b) in (...)
|
||
w.push(k + ' in (' + v.map(vi => '('+vi.map(() => '?').join(', ')+')') + ')');
|
||
v.map(vi => bind.push.apply(bind, vi));
|
||
}
|
||
else
|
||
{
|
||
w.push(v.length == 1
|
||
? k + ' = ?'
|
||
: k + ' in (' + v.map(() => '?').join(', ') + ')');
|
||
bind.push.apply(bind, v);
|
||
}
|
||
}
|
||
}
|
||
if (!where)
|
||
{
|
||
// SET
|
||
return { sql: w.join(', '), bind };
|
||
}
|
||
// WHERE
|
||
return { sql: w.length ? '('+w.join(') and (')+')' : '', bind };
|
||
}
|
||
|
||
function where_builder(where)
|
||
{
|
||
return where_or_set(where, true);
|
||
}
|
||
|
||
/**
|
||
* Разбивает набор таблиц на основную обновляемую + набор дополнительных
|
||
*
|
||
* Идея в том, чтобы обрабатывать хотя бы 2 простые ситуации:
|
||
* UPDATE table1 INNER JOIN table2 ...
|
||
* UPDATE table1 LEFT JOIN table2 ...
|
||
*/
|
||
function split_using(tables)
|
||
{
|
||
if (typeof tables == 'string')
|
||
{
|
||
return { what: { sql: tables, bind: [] }, using: null, moreWhere: null };
|
||
}
|
||
let first = null;
|
||
let is_next_inner = true;
|
||
let i = 0;
|
||
for (let k in tables)
|
||
{
|
||
let t = tables[k];
|
||
if (i == 0)
|
||
{
|
||
if (t instanceof Array && typeof(t[1]) != 'string')
|
||
{
|
||
throw new Error('Can only update/delete from real tables, not sub-select, sub-join or VALUES');
|
||
}
|
||
first = k;
|
||
}
|
||
else if (i == 1)
|
||
{
|
||
is_next_inner = !(t instanceof Array) || t[0].toLowerCase() == 'inner';
|
||
}
|
||
else
|
||
{
|
||
break;
|
||
}
|
||
i++;
|
||
}
|
||
let what, moreWhere;
|
||
if (is_next_inner)
|
||
{
|
||
what = tables_builder({ [first]: tables[first] });
|
||
delete tables[first];
|
||
moreWhere = what.moreWhere;
|
||
what.moreWhere = null;
|
||
}
|
||
else
|
||
{
|
||
what = tables_builder({ ["_"+first]: tables[first] });
|
||
const cond = '_'+first+'.ctid='+(/^\d+$/.exec(first) ? tables[first] : first)+'.ctid';
|
||
moreWhere = what.moreWhere
|
||
? { sql: what.moreWhere.sql+' AND '+cond, bind: what.moreWhere.bind }
|
||
: { sql: cond, bind: [] };
|
||
what.moreWhere = null;
|
||
}
|
||
return { what, using: Object.keys(tables).length > 0 ? tables : null, moreWhere };
|
||
}
|
||
|
||
function _positional(sql)
|
||
{
|
||
let i = 0;
|
||
sql = sql.replace(/'(?:[^\']*|\'\')*'|"(?:[^\"]*|\"\")*"|(\?)/g, (m, m1) => (m1 ? '$'+(++i) : m));
|
||
console.log('> '+sql);
|
||
return sql;
|
||
}
|
||
|
||
function _inline(sql, bind)
|
||
{
|
||
if (!pg_escape)
|
||
{
|
||
pg_escape = require('pg-escape');
|
||
}
|
||
let i = 0;
|
||
sql = sql.replace(/'(?:[^\']*|\'\')*'|"(?:[^\"]*|\"\")*"|(\?)/g, (m, m1) => (m1 ? '\''+pg_escape.string(bind[i++])+'\'' : m));
|
||
return sql;
|
||
}
|
||
|
||
// dbh = node-postgres.Client
|
||
async function select(dbh, tables, fields, where, options, format)
|
||
{
|
||
let { sql, bind } = select_builder(tables, fields, where, options);
|
||
let data = await dbh.query(_positional(sql), bind);
|
||
if ((format & MS_LIST) || (format & MS_COL))
|
||
data = data.rows.map(r => Object.values(r));
|
||
else
|
||
data = data.rows;
|
||
if (format & MS_ROW)
|
||
data = data[0];
|
||
if (data && (format & MS_COL))
|
||
data = data[0];
|
||
return data;
|
||
}
|
||
|
||
async function insert(dbh, table, rows, options)
|
||
{
|
||
if (!(rows instanceof Array))
|
||
{
|
||
rows = [ rows ];
|
||
}
|
||
if (!rows.length)
|
||
{
|
||
return null;
|
||
}
|
||
const keys = Object.keys(rows[0]);
|
||
let sql = 'insert into '+table+' ('+keys.join(', ')+') values ';
|
||
const bind = [];
|
||
let i = 0;
|
||
for (const row of rows)
|
||
{
|
||
sql += (i > 0 ? ', (' : '(') + keys.map(() => '$'+(++i)).join(', ')+')';
|
||
bind.push.apply(bind, keys.map(k => row[k]));
|
||
}
|
||
if (options && options.returning)
|
||
{
|
||
sql += ' returning '+options.returning;
|
||
return (await dbh.query(sql, bind)).rows;
|
||
}
|
||
return await dbh.query(sql, bind);
|
||
}
|
||
|
||
async function _delete(dbh, table, where, options)
|
||
{
|
||
where = where_builder(where);
|
||
const split = split_using(table);
|
||
if (split.using)
|
||
{
|
||
split.using = tables_builder(split.using);
|
||
}
|
||
let sql = 'delete from '+split.what.sql+
|
||
(split.using ? ' using '+split.using.sql : '')+
|
||
' where '+(where.sql || '1=1')+(split.moreWhere ? ' and '+split.moreWhere.sql : '');
|
||
let bind = [ ...split.what.bind, ...where.bind, ...(split.moreWhere ? split.moreWhere.bind : []) ];
|
||
if (options && options.returning)
|
||
{
|
||
sql += ' returning '+options.returning;
|
||
return (await dbh.query(_positional(sql), bind)).rows;
|
||
}
|
||
return await dbh.query(_positional(sql), bind);
|
||
}
|
||
|
||
async function update(dbh, table, set, where, options)
|
||
{
|
||
set = where_or_set(set, false);
|
||
where = where_builder(where);
|
||
const split = split_using(table);
|
||
if (split.using)
|
||
{
|
||
split.using = tables_builder(split.using);
|
||
}
|
||
let sql = 'update '+split.what.sql+' set '+set.sql+
|
||
(split.using ? ' from '+split.using.sql : '')+
|
||
' where '+(where.sql || '1=1')+(split.moreWhere ? ' and '+split.moreWhere.sql : '');
|
||
let bind = [
|
||
...split.what.bind,
|
||
...set.bind,
|
||
...(split.using ? split.using.bind : []),
|
||
...where.bind,
|
||
...(split.moreWhere ? split.moreWhere.bind : [])
|
||
];
|
||
if (options && options.returning)
|
||
{
|
||
sql += ' returning '+options.returning;
|
||
return (await dbh.query(_positional(sql), bind)).rows;
|
||
}
|
||
return await dbh.query(_positional(sql), bind);
|
||
}
|
||
|
||
function values(rows)
|
||
{
|
||
return new Pg_Values(Object.keys(rows[0]), rows);
|
||
}
|
||
|
||
class Pg_Values
|
||
{
|
||
constructor(keys, rows)
|
||
{
|
||
this.keys = keys;
|
||
this.rows = rows;
|
||
}
|
||
}
|
||
|
||
class Text
|
||
{
|
||
constructor(sql, bind)
|
||
{
|
||
this.sql = sql;
|
||
this.bind = bind || [];
|
||
}
|
||
|
||
toString()
|
||
{
|
||
return _inline(this.sql, this.bind);
|
||
}
|
||
|
||
concat(text)
|
||
{
|
||
return new Text(this.sql+text.sql, [ ...this.bind, ...text.bind ]);
|
||
}
|
||
}
|
||
|
||
module.exports = {
|
||
select_builder,
|
||
where_builder,
|
||
quote_into: _inline,
|
||
quote_positional: _positional,
|
||
select,
|
||
insert,
|
||
delete: _delete,
|
||
update,
|
||
values,
|
||
Text,
|
||
MS_HASH,
|
||
MS_LIST,
|
||
MS_ROW,
|
||
MS_COL,
|
||
MS_VALUE,
|
||
};
|