nodejs-mw-select-builder/select-builder-oracle.js

// Простенький "селект билдер" по мотивам MediaWiki-овского, успешно юзаю подобный в PHP уже лет 8
// (c) Виталий Филиппов, 2018

// В PHP, правда, прикольнее - там в массиве можно смешивать строковые и численные ключи,
// благодаря чему можно писать $where = [ 't1.a=t2.a', 't2.b' => [ 1, 2, 3 ] ]

const MS_HASH = 0;
const MS_LIST = 1;
const MS_ROW = 2;
const MS_COL = 4;
const MS_VALUE = 6;

function limitOffset(sql, limit, offset)
{
    if (!limit && !offset)
        return sql;
    const w = [];
    if (limit)
        w.push('rownum <= '+((limit|0)+(offset|0)));
    if (offset)
        w.push('rownum >= '+(1+(offset|0)));
    return 'select * from ('+sql+') t where '+w.join(' and ');
}

function hashResult(res)
{
    const keys = res.metaData.map(m => m.name.toLowerCase());
    const hashes = [];
    for (const row of res.rows)
    {
        const h = {};
        keys.map((k, i) => h[k] = row[i]);
        hashes.push(h);
    }
    return hashes;
}

function selectBuilder(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 ';
    let first = true;
    let moreWhere = null;
    tables = typeof tables == 'string' ? { t: tables } : tables;
    for (const k in tables)
    {
        if (first)
        {
            if (typeof tables[k] != 'string')
            {
                // Бывает удобно указывать WHERE как условие "JOIN" первой таблицы
                sql += tables[k][1] + ' ' + k;
                moreWhere = tables[k][2];
            }
            else
            {
                sql += tables[k] + ' ' + k;
            }
            first = false;
        }
        else if (typeof tables[k] == 'string')
        {
            sql += ' INNER JOIN '+tables[k]+' '+k+' ON 1=1';
        }
        else
        {
            const on = whereBuilder(tables[k][2]);
            sql += ' ' + tables[k][0].toUpperCase() + ' JOIN ' + tables[k][1] + ' ' + k + ' ON ' + (on[0] || '1=1');
            bind.push.apply(bind, on[1]);
        }
    }
    const w = whereBuilder(where);
    sql += ' WHERE '+(w[0] || '1=1');
    bind.push.apply(bind, w[1]);
    if (moreWhere)
    {
        moreWhere = whereBuilder(moreWhere);
        if (moreWhere[0])
        {
            sql += ' AND '+moreWhere[0];
            bind.push.apply(bind, moreWhere[1]);
        }
    }
    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.OFFSET || options.limit || options.offset)
    {
        sql = limitOffset(sql, options.LIMIT || options.limit, options.OFFSET || options.offset);
    }
    return [ sql, bind ];
}

function whereOrSetBuilder(fields, where)
{
    if (typeof fields == 'string')
        return [ fields, [] ];
    const w = [], bind = [];
    for (const k in fields)
    {
        let v = fields[k];
        if (k.indexOf('?') >= 0)
        {
            if (!(v instanceof Array))
                v = [ v ];
            w.push(k);
            bind.push.apply(bind, v);
        }
        else if (/^\d+$/.exec(k))
        {
            if (v instanceof Array)
            {
                w.push(v[0]);
                bind.push.apply(bind, v.slice(1));
            }
            else
            {
                w.push(v);
            }
        }
        else if (v != null || v instanceof Array && v.length)
        {
            v = v instanceof Array ? v : [ v ];
            w.push(v.length == 1 ? k + ' = ?' : k + ' in (' + v.map(() => '?').join(', ') + ')');
            bind.push.apply(bind, v);
        }
    }
    if (!where)
        return [ w.join(', '), bind ];
    return [ w.length ? '('+w.join(') and (')+')' : '', bind ];
}

function whereBuilder(where)
{
    return whereOrSetBuilder(where, true);
}

function _positional(sql)
{
    let i = 0;
    sql = sql.replace(/\?/g, () => ':'+(++i));
    return sql;
}

async function select(dbh, tables, fields, where, options, format)
{
    let [ sql, bind ] = selectBuilder(tables, fields, where, options);
    let data = await dbh.execute(_positional(sql), bind);
    if ((format & MS_LIST) || (format & MS_COL))
        data = data.rows;
    else
        data = hashResult(data);
    if (format & MS_ROW)
        data = data[0];
    if (data && (format & MS_COL))
        data = data[0];
    return data;
}

async function insert(dbh, table, rows)
{
    if (!(rows instanceof Array))
    {
        rows = [ rows ];
    }
    if (!rows.length)
    {
        return null;
    }
    const keys = Object.keys(rows[0]);
    let sql = 'insert into '+table+' ('+keys.join(', ')+') ';
    const bind = [];
    let i = 0;
    for (const row of rows)
    {
        sql += (i > 0 ? ' union all ' : '')+' select'+keys.map(() => ':'+(++i)).join(', ')+' from dual';
        bind.push.apply(bind, keys.map(k => row[k]));
    }
    return await dbh.execute(sql, bind);
}

async function _delete(dbh, table, where)
{
    const w = whereBuilder(where);
    const sql = 'DELETE FROM '+table+' WHERE '+(w[0] || '1=1');
    return await dbh.execute(_positional(sql), w[1]);
}

async function update(dbh, table, set, where)
{
    set = whereOrSetBuilder(set, false);
    where = whereOrSetBuilder(where, true)
    const sql = 'UPDATE '+table+' SET '+set[0]+' WHERE '+(where[0] || '1=1');
    const bind = [ ...set[1], ...where[1] ];
    return await dbh.execute(_positional(sql), bind);
}

module.exports = {
    select,
    insert,
    delete: _delete,
    update,
    MS_HASH,
    MS_LIST,
    MS_ROW,
    MS_COL,
    MS_VALUE,
    hashResult,
};