File size: 6,192 Bytes
1a25764
 
 
eab7289
bb750d6
1a25764
 
bb750d6
1a25764
 
 
bb750d6
 
 
 
1a25764
 
 
 
bb750d6
 
 
 
 
 
1a25764
 
 
 
 
 
 
 
 
 
 
 
 
 
 
7f0d072
1a25764
 
 
 
 
 
 
 
7f0d072
1a25764
 
eab7289
 
 
 
 
 
 
7f0d072
eab7289
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
1a25764
 
 
 
eab7289
 
 
1a25764
 
 
 
eab7289
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
1a25764
 
 
bb750d6
eab7289
 
1a25764
 
 
 
bb750d6
1a25764
 
 
 
 
bb750d6
1a25764
 
 
 
 
 
 
 
 
 
 
 
 
 
bb750d6
1a25764
 
 
 
 
 
 
 
 
 
 
 
 
 
5533459
 
 
 
 
 
1a25764
bb750d6
1a25764
 
5533459
 
 
 
 
 
 
 
1a25764
 
 
 
 
bb750d6
1a25764
eab7289
 
1a25764
 
eab7289
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
'use strict';

const { DatabaseSync } = require('node:sqlite');
const { dbPath, dbDebugSql, defaultPageSize } = require('../config');
const { wrapDb } = require('./sql-logger');

let db;
let wrappedDb;

function getDb() {
  if (!db) {
    const raw = new DatabaseSync(dbPath);
    raw.exec('PRAGMA journal_mode=WAL');
    raw.exec('PRAGMA foreign_keys=ON');
    db = raw;
  }
  return db;
}

function getWrappedDb() {
  if (!dbDebugSql) return getDb();
  if (!wrappedDb) wrappedDb = wrapDb(getDb());
  return wrappedDb;
}

function parseRow(table, row) {
  if (!row) return null;
  const r = { ...row };
  if ('address' in r && typeof r.address === 'string') {
    try { r.address = JSON.parse(r.address); } catch (_) { }
  }
  if ('company' in r && typeof r.company === 'string') {
    try { r.company = JSON.parse(r.company); } catch (_) { }
  }
  if ('completed' in r) {
    r.completed = Boolean(r.completed);
  }
  return r;
}

const TABLES = ['users', 'posts', 'comments', 'albums', 'photos', 'todos', 'settings'];

const FILTER_COLS = {
  users: ['id', 'name', 'username', 'email', 'phone', 'website'],
  posts: ['id', 'userId', 'title'],
  comments: ['id', 'postId', 'name', 'email'],
  albums: ['id', 'userId', 'title'],
  photos: ['id', 'albumId', 'title', 'url', 'thumbnailUrl'],
  todos: ['id', 'userId', 'title', 'completed'],
  settings: ['id', 'key', 'value', 'description'],
};

const SEARCH_COLS = {
  users: ['name', 'username', 'email'],
  posts: ['title', 'body'],
  comments: ['name', 'email', 'body'],
  albums: ['title'],
  photos: ['title'],
  todos: ['title'],
  settings: ['key', 'value', 'description'],
};

function buildWhere(table, query = {}) {
  const filters = {};
  let page, limit, start, end, sort, order, search;

  for (const [k, v] of Object.entries(query)) {
    switch (k) {
      case '_page': page = parseInt(v, 10); break;
      case '_limit': limit = parseInt(v, 10); break;
      case '_start': start = parseInt(v, 10); break;
      case '_end': end = parseInt(v, 10); break;
      case '_sort': sort = v; break;
      case '_order': order = v; break;
      case 'q': search = v; break;
      default: filters[k] = v; break;
    }
  }

  const cols = FILTER_COLS[table] || [];
  const clauses = [];
  const values = [];

  for (const [k, v] of Object.entries(filters)) {
    const isKnownCol = cols.includes(k);
    if (isKnownCol) {
      clauses.push(`${k} = ?`);
      values.push(k === 'completed' ? (v === 'true' ? 1 : 0) : v);
    }
  }

  const searchCols = SEARCH_COLS[table] || [];
  if (search && searchCols.length > 0) {
    const escaped = search.replace(/[%_\\]/g, '\\$&');
    const searchClauses = searchCols.map((c) => `${c} LIKE ? ESCAPE '\\'`);
    const likeVal = `%${escaped}%`;
    clauses.push(`(${searchClauses.join(' OR ')})`);
    for (const _ of searchCols) values.push(likeVal);
  }

  const hasClauses = clauses.length > 0;
  let sql = `SELECT * FROM ${table}`;
  if (hasClauses) {
    sql += ' WHERE ' + clauses.join(' AND ');
  }

  const allCols = [...new Set([...cols, ...searchCols])];
  const safeSort = sort && allCols.includes(sort) ? sort : null;
  if (safeSort) {
    const dir = order && ['asc', 'desc'].includes(order.toLowerCase()) ? order.toUpperCase() : 'ASC';
    sql += ` ORDER BY "${safeSort}" ${dir}`;
  }
  const hasPage = page !== undefined;
  const hasLimit = limit !== undefined;
  if (start !== undefined) {
    const lim = end !== undefined ? end - start : -1;
    sql += ` LIMIT ${lim} OFFSET ${start}`;
  } else if (hasPage || hasLimit) {
    const p = hasPage ? page : 1;
    const lim = hasLimit ? limit : defaultPageSize;
    sql += ` LIMIT ${lim} OFFSET ${(p - 1) * lim}`;
  }

  return { sql, values };
}

function listAll(table, query = {}) {
  const d = getWrappedDb();
  const { sql, values } = buildWhere(table, query);
  const rows = d.prepare(sql).all(...values);
  return rows.map((r) => parseRow(table, r));
}

function getOne(table, id) {
  const d = getWrappedDb();
  const row = d.prepare(`SELECT * FROM ${table} WHERE id = ?`).get(id);
  return parseRow(table, row);
}

function insertOne(table, data) {
  const d = getWrappedDb();
  const cols = Object.keys(data);
  const placeholders = cols.map(() => '?').join(',');
  const vals = cols.map((c) => {
    if (typeof data[c] === 'object' && data[c] !== null) return JSON.stringify(data[c]);
    if (typeof data[c] === 'boolean') return data[c] ? 1 : 0;
    return data[c];
  });
  const result = d.prepare(
    `INSERT INTO ${table} (${cols.join(',')}) VALUES (${placeholders})`
  ).run(...vals);
  return getOne(table, result.lastInsertRowid);
}

function updateOne(table, id, data, replace = false) {
  const d = getWrappedDb();
  const existing = getOne(table, id);
  if (!existing) return null;
  const merged = replace ? { ...data, id } : { ...existing, ...data, id };
  const cols = Object.keys(merged).filter((c) => c !== 'id');
  const setClauses = cols.map((c) => `${c} = ?`).join(', ');
  const vals = cols.map((c) => {
    if (typeof merged[c] === 'object' && merged[c] !== null) return JSON.stringify(merged[c]);
    if (typeof merged[c] === 'boolean') return merged[c] ? 1 : 0;
    return merged[c] ?? null;
  });
  d.prepare(`UPDATE ${table} SET ${setClauses} WHERE id = ?`).run(...vals, id);
  return getOne(table, id);
}

const CASCADE_MAP = {
  users:   { key: 'userId',   children: ['posts', 'albums', 'todos'] },
  posts:   { key: 'postId',   children: ['comments'] },
  albums:  { key: 'albumId',  children: ['photos'] },
};

function deleteOne(table, id) {
  const d = getWrappedDb();
  const existing = getOne(table, id);
  if (!existing) return null;

  const cascade = CASCADE_MAP[table];
  if (cascade) {
    for (const child of cascade.children) {
      d.prepare(`DELETE FROM ${child} WHERE ${cascade.key} = ?`).run(id);
    }
  }

  d.prepare(`DELETE FROM ${table} WHERE id = ?`).run(id);
  return existing;
}

function nextId(table) {
  const d = getWrappedDb();
  const row = d.prepare(`SELECT MAX(id) as m FROM ${table}`).get();
  const m = row.m ?? 0;
  return m + 1;
}

module.exports = { getDb, getWrappedDb, buildWhere, listAll, getOne, insertOne, updateOne, deleteOne, nextId, TABLES };