j7/db.js

const util = require("util");
const log = require("./log");
const utils = require("./utils");

class DB {

  constructor(conn) {
    this.conn = conn;
  }

  release() {
    this.conn.release();
  }

  execQuery(sql, params) {
    return new Promise((resolve) => {
      const ret = {
        err: null,
        rows: null,
      };
      this.conn.query(sql, params, (err, rows) => {
        try {
          if (err) {
            ret.err = err;
            resolve(ret);
            log.error(util.format(
              'sql:%s err:%s',
              sql, err
            ));
          } else {
            ret.err = err;
            ret.rows = rows;
            resolve(ret);
          }
        } finally {
          //this.conn.release();
        }
      });
    });
  }

  async execQueryOne(sql, params) {
    const {err, rows} = await this.execQuery(sql, params);
    return {
      'err': err,
      'row': rows && rows.length > 0 ? rows[0] : null
    };
  }

  async execScript(sql, params) {
    return await this.execQuery(sql, params);
  }

  async updateOld(tblName, whereList, fieldList) {
    const params = [];
    let sql = 'UPDATE `' + tblName + '` SET ';

    fieldList.forEach((item, index) => {
      const suffix = (index + 1 < fieldList.length ? ',': '');
      sql += ' `' + item[0] + '`=?' + suffix;
      params.push(item[1]);
    });

    sql += ' WHERE 1=1';
    whereList.forEach((item, index) => {
      sql += ' AND ' + item[0] + '=?';
      params.push(item[1]);
    });

    return await this.execScript(sql, params);
  }

  async update(tblName, whereList, fieldList) {
    const params = [];
    let sql = 'UPDATE `' + tblName + '` SET ';

    fieldList.forEach((item, index) => {
      const suffix = (index + 1 < fieldList.length ? ',': '');
      if (item[0][0] == '!') {
        if (typeof item[1] == 'function') {
          sql += ' `' + item[0].substr(1) + '`=' + item[1]() + suffix;
        } else {
          sql += ' `' + item[0].substr(1) + '`=' + item[1] + suffix;
        }
      } else {
        sql += ' `' + item[0] + '`=?' + suffix;
        params.push(item[1]);
      }
    });

    sql += ' WHERE 1=1';
    whereList.forEach((item, index) => {
      sql += ' AND ' + item[0] + '=?';
      params.push(item[1]);
    });

    return await this.execScript(sql, params);
  }

  async insert(tblName, fieldList) {
    const params = [];
    let sql = 'INSERT INTO `' + tblName + '` (';

    fieldList.forEach((item, index) => {
      const suffix = (index + 1 < fieldList.length ? ',': '');
      sql += '`' + item[0] + '`' + suffix;
    });

    sql += ') VALUES (';
    fieldList.forEach((item, index) => {
      const suffix = (index + 1 < fieldList.length ? ',': '');
      sql += '?' + suffix;
      params.push(item[1]);
    });
    sql += ')';

    return await this.execScript(sql, params);
  }

  async insertEx(tblName, fieldList) {
    const params = [];
    let sql = 'INSERT INTO `' + tblName + '` (';

    fieldList.forEach((item, index) => {
      const suffix = (index + 1 < fieldList.length ? ',': '');
      sql += '`' + item[0] + '`' + suffix;
    });

    sql += ') VALUES (';
    fieldList.forEach((item, index) => {
      const suffix = (index + 1 < fieldList.length ? ',': '');
      sql += '?' + suffix;
      params.push(item[1]);
    });
    sql += ')';

    const ret = await this.execScript(sql, params);
    ret['last_idx'] = 0;
    const {err, row} = await this.execQueryOne(
      'SELECT LAST_INSERT_ID() AS lastId',
      [
      ]
    );
    if (err) {
      ret['err'] = err;
    } else {
      ret['lastId'] = row['lastId'];
    }
    return ret;
  }

  async upsert(tblName, whereList, updateList, insertList, opts = null) {
    const params = [];
    let sql = 'SELECT * FROM `' + tblName + '` ';
    {
      sql += ' WHERE 1=1';
      whereList.forEach((item, index) => {
        sql += ' AND ' + item[0] + '=?';
        params.push(item[1]);
      });
      sql += ' LIMIT 1;';
    }

    const {err, row} = await this.execQueryOne
    (
      sql,
      params
    );
    if (err) {
      return {
        'err': err,
        'row': row
      };
    }
    if (row) {
      if (opts && utils.getVal(opts, 'onQueryOk')) {
        opts['onQueryOk'](row);
      }
      if (updateList.length > 0) {
        return await this.update(tblName, whereList, updateList);
      } else {
        return {
          'err': null,
        };
      }
    } else {
      return await this.insert(tblName, insertList);
    }
  }

  async _delete(tblName, whereList) {
    const params = [];
    let sql = 'DELETE FROM `' + tblName + '` ';

    sql += ' WHERE 1=1';
    whereList.forEach((item, index) => {
      sql += ' AND ' + item[0] + '=?';
      params.push(item[1]);
    });

    return await this.execScript(sql, params);
  }

  async ormSelect(tblName, whereList) {
    const params = [];
    let sql = 'SELECT * FROM `' + tblName + '` ';

    sql += ' WHERE 1=1';
    whereList.forEach((item, index) => {
      sql += ' AND ' + item[0] + '=?';
      params.push(item[1]);
    });

    return await this.execQuery(sql, params);
  }

  async ormSelectOne(tblName, whereList) {
    const {err, rows} = await this.ormSelect(tblName, whereList);
    return {
      'err': err,
      'row': rows && rows.length > 0 ? rows[0] : null
    };
  }

  async getMaxIdx(tblName) {
    const params = [];
    let sql = 'SELECT max(idx) AS max_idx FROM `' + tblName + '` ';

    const {err, row} = await this.execQueryOne(sql, params);
    if (err) {
      return {
        'err': err,
        'maxIdx': BigInt(0)
      };
    } else {
      return {
        'err': err,
        'maxIdx': row && row['max_idx'] != null ? BigInt(row['max_idx']) : BigInt(0)
      };
    }
  }

}

module.exports = DB;