ts类型定义文件
type Istrings = string | string[] | undefined;
export default interface Idb {
type: string;
table: string;
field?: Istrings;
value?: Istrings;
set?: Istrings;
where?: Istrings;
order?: Istrings;
limit?: number;
}
mysql封装
import type Idb from '../type';
function clog(...arg: any) {
}
class DbMySQL {
pool: any;
config: any;
constructor(config: any) {
this.config = config;
this.pool = require('mysql').createPool(config.sql);
}
joinSql(sql: Idb, type: 'where' | 'order' | 'value' | 'set' | 'field') {
var val = sql[type];
if (Array.isArray(val)) {
if (val.length) {
if (type === 'where') {
return ' where ' + val.join(' and ');
} else if (type === 'order') {
return ' order by ' + val.join(',');
} else if (type === 'value') {
return ' values (' + val.join('),(') + ')';
} else if (type === 'set') {
return ' set ' + val.join(',');
} else if (type === 'field') {
return ' (' + val.join(',') + ')';
}
} else {
if (['value', 'set'].includes(type)) {
}
}
} else if (val) {
if (type === 'where') {
return ' where ' + val;
} else if (type === 'order') {
return ' order by ' + val;
} else if (type === 'value') {
return ' values (' + val + ')';
} else if (type === 'set') {
return ' set ' + val;
} else if (type === 'field') {
return ' (' + val + ')';
}
}
return '';
}
makeSql(sql: Idb) {
var sqls = sql.type;
if (sqls === 'insert') {
sqls += ' into';
sqls += ' ' + sql.table;
sqls += this.joinSql(sql, 'field');
sqls += this.joinSql(sql, 'value');
} else if (sqls === 'delete') {
sqls += ' from ' + sql.table;
sqls += this.joinSql(sql, 'where');
sqls += this.joinSql(sql, 'order');
sqls += sql.limit ? ' limit ' + sql.limit : '';
} else if (sqls === 'update') {
sqls += ' ' + sql.table;
sqls += this.joinSql(sql, 'set');
sqls += this.joinSql(sql, 'where');
sqls += this.joinSql(sql, 'order');
sqls += sql.limit ? ' limit ' + sql.limit : '';
} else if (sqls === 'select') {
sqls += sql.field ? ' ' + sql.field : ' *';
sqls += ' from ' + sql.table;
sqls += this.joinSql(sql, 'where');
sqls += this.joinSql(sql, 'order');
sqls += sql.limit ? ' limit ' + sql.limit : '';
}
clog(666.201, sqls);
return sqls;
}
getErr(err: any) {
var errs = err?.message || null;
return errs;
}
getResult(rows: any) {
var result = rows ?? null;
return result;
}
query(sql: Idb, callback: any) {
clog(666.101, sql, callback);
this.pool.getConnection((error: any, connection: any) => {
if (error) {
clog(666.102, error, connection);
} else {
connection.query(this.makeSql(sql), (err: any, rows: any) => {
clog(666.103, err, rows);
callback(this.getErr(err), this.getResult(rows));
connection.release();
});
}
});
}
sqlFail(val: any) {
return { code: 907, data: val };
}
sqlEmpty(val: string) {
return { code: 908, data: val };
}
sqlSuccess(val: any) {
return { code: 909, data: val };
}
sqlDb(sql: Idb) {
return new Promise((resolve, reject) => {
try {
this.query(sql, (err: any, result: any) => {
if (err) {
reject(this.sqlFail(err));
} else if (!result || (Array.isArray(result) && !result.length)) {
resolve(this.sqlEmpty(sql.type + ' is null'));
} else {
resolve(this.sqlSuccess(result));
}
});
} catch (err) {
reject(err);
}
});
}
}
export default DbMySQL;
SQLite3封装
import type Idb from '../type';
function clog(...arg: any) {
}
class DbSQLite3 {
pool: any;
config: any;
sqlite3: any;
constructor(config: any) {
this.config = config;
this.sqlite3 = require('sqlite3').verbose();
this.pool = new this.sqlite3.Database(config.sql.database, (err: any) => {
if (err) throw err;
});
}
joinSql(sql: Idb, type: 'where' | 'order' | 'value' | 'set' | 'field') {
var val = sql[type];
if (Array.isArray(val)) {
if (val.length) {
if (type === 'where') {
return ' where ' + val.join(' and ');
} else if (type === 'order') {
return ' order by ' + val.join(',');
} else if (type === 'value') {
return ' values (' + val.join('),(') + ')';
} else if (type === 'set') {
return ' set ' + val.join(',');
} else if (type === 'field') {
return ' (' + val.join(',') + ')';
}
} else {
if (['value', 'set'].includes(type)) {
}
}
} else if (val) {
if (type === 'where') {
return ' where ' + val;
} else if (type === 'order') {
return ' order by ' + val;
} else if (type === 'value') {
return ' values (' + val + ')';
} else if (type === 'set') {
return ' set ' + val;
} else if (type === 'field') {
return ' (' + val + ')';
}
}
return '';
}
makeSql(sql: Idb) {
var sqls = sql.type;
if (sqls === 'insert') {
sqls += ' into';
sqls += ' ' + sql.table;
sqls += this.joinSql(sql, 'field');
sqls += this.joinSql(sql, 'value');
} else if (sqls === 'delete') {
sqls += ' from ' + sql.table;
sqls += this.joinSql(sql, 'where');
} else if (sqls === 'update') {
sqls += ' ' + sql.table;
sqls += this.joinSql(sql, 'set');
sqls += this.joinSql(sql, 'where');
} else if (sqls === 'select') {
sqls += sql.field ? ' ' + sql.field : ' *';
sqls += ' from ' + sql.table;
sqls += this.joinSql(sql, 'where');
sqls += this.joinSql(sql, 'order');
sqls += sql.limit ? ' limit ' + sql.limit : '';
}
clog(666.111, sqls);
return sqls;
}
getErr(err: any) {
var errs = err?.message || null;
return errs;
}
getResult(rows: any) {
var result = rows ?? 'OK';
return result;
}
query(sql: Idb, callback: any) {
clog(666.101, sql, callback);
if (sql.type === 'select') {
if (sql.where && (!sql.limit || sql.limit === 1) && false) {
this.pool.each(this.makeSql(sql), (err: any, rows: any) => {
clog(666.103, err, rows);
callback(this.getErr(err), this.getResult(rows));
});
} else {
this.pool.all(this.makeSql(sql), (err: any, rows: any) => {
clog(666.104, err, rows);
callback(this.getErr(err), this.getResult(rows));
});
}
} else {
this.pool.run(this.makeSql(sql), (err: any, rows: any) => {
clog(666.105, err, rows);
callback(this.getErr(err), this.getResult(rows));
});
}
}
sqlFail(val: any) {
return { code: 907, data: val };
}
sqlEmpty(val: string) {
return { code: 908, data: val };
}
sqlSuccess(val: any) {
return { code: 909, data: val };
}
sqlDb(sql: Idb) {
return new Promise((resolve, reject) => {
try {
this.query(sql, (err: any, result: any) => {
if (err) {
reject(this.sqlFail(err));
} else if (!result || (Array.isArray(result) && !result.length)) {
resolve(this.sqlEmpty(sql.type + ' is null'));
} else {
resolve(this.sqlSuccess(result));
}
});
} catch (err) {
reject(err);
}
});
}
}
export default DbSQLite3;