Spaces:
Sleeping
Sleeping
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 };
|