wMwV1CdK

1const Database = require('better-sqlite3');
2
3
4
5const SQLTYPE = {
6  'boolean': 'INTEGER',
7  'number': 'REAL',
8  'string': 'TEXT',
9};
10const RKEYWORD = /^offset|limit|order|group|where/i;
11const db = new Database('main.db');
12
13
14
15
16
17function type(val) {
18  return SQLTYPE[typeof val]||null;
19}
20
21function objectStringify(object, separator) {
22  var o = object, s = separator, out = '';
23  for(var k in o)
24    out += k+'='+o[k]+s;
25  return out.substring(0, out.length-s.length);
26}
27
28function valueMap(value) {
29  var v = value;
30  if(Array.isArray(v)) return v.join(';');
31  if(v && typeof v==='object') return objectStringify(v, ';');
32  return v;
33}
34
35function rowMap(row) {
36  var out = {};
37  for(var k in row)
38    out[k] = valueMap(row[k]);
39  return out;
40};
41
42function queryMap(query) {
43  var q = query||'';
44  return RKEYWORD.test(q)? q:'WHERE '+q;
45}
46
47function dropTable(table) {
48  var sql = `DROP TABLE IF EXISTS "${table}"`;
49  return db.prepare(sql).run();
50}
51
52function createTable(table) {
53  var sql = `CREATE TABLE IF NOT EXISTS "${table}" ("id" TEXT PRIMARY KEY)`;
54  return db.prepare(sql).run();
55}
56
57function deleteRows(table, query) {
58  var expr = queryMap(query);
59  var sql = `DELETE FROM "${table}" ${expr}`;
60  return db.prepare(sql).run();
61}
62
63function replaceRow(table, row) {
64  var keys = Object.keys(row);
65  var fields = keys.map(k => `"${k}"`).join(', ');
66  var values = keys.map(k => `@${k}`).join(', ');
67  var sql = `REPLACE INTO "${table}" (${fields}) VALUES (${values})`;
68  return db.prepare(sql).run(row);
69}
70
71function replaceRowAuto(table, row) {
72  var pragma = db.pragma(`table_info("${table}")`);
73  var columns = pragma.map(r => r.name.toLowerCase());
74  var row = rowMap(row);
75  for(var k in row) {
76    if(columns.includes(k.toLowerCase())) continue;
77    db.prepare(`ALTER TABLE "${table}" ADD "${k}" ${type(row[k])}`).run();
78  }
79  var old = db.prepare(`SELECT * FROM "${table}" WHERE "id"=@id`).get(row)||{};
80  return replaceRow(table, Object.assign(old, row));
81}
82
83function selectRows(table, query) {
84  var expr = queryMap(query);
85  var sql = `SELECT * FROM "${table}" ${expr}`;
86  return db.prepare(sql).all();
87}
88exports.drop = dropTable;
89exports.create = createTable;
90exports.delete = deleteRows;
91exports.replace = replaceRowAuto;
92exports.select = selectRows;