Spaces:
Sleeping
Sleeping
File size: 3,533 Bytes
1a25764 96a886b | 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 | 'use strict';
const { DatabaseSync } = require('node:sqlite');
const { dbPath } = require('./config');
let db;
function getDb() {
if (!db) {
db = new DatabaseSync(dbPath);
db.exec('PRAGMA journal_mode=WAL');
db.exec('PRAGMA foreign_keys=ON');
}
return db;
}
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'];
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'],
};
function buildWhere(table, query) {
const cols = FILTER_COLS[table] || [];
const clauses = [];
const values = [];
for (const [k, v] of Object.entries(query)) {
if (cols.includes(k)) {
clauses.push(`${k} = ?`);
values.push(k === 'completed' ? (v === 'true' ? 1 : 0) : v);
}
}
return {
where: clauses.length ? ' WHERE ' + clauses.join(' AND ') : '',
values,
};
}
function listAll(table, query = {}) {
const d = getDb();
const { where, values } = buildWhere(table, query);
const rows = d.prepare(`SELECT * FROM ${table}${where}`).all(...values);
return rows.map((r) => parseRow(table, r));
}
function getOne(table, id) {
const d = getDb();
const row = d.prepare(`SELECT * FROM ${table} WHERE id = ?`).get(id);
return parseRow(table, row);
}
function insertOne(table, data) {
const d = getDb();
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 = getDb();
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);
}
function deleteOne(table, id) {
const d = getDb();
const existing = getOne(table, id);
if (!existing) return null;
d.prepare(`DELETE FROM ${table} WHERE id = ?`).run(id);
return existing;
}
function nextId(table) {
const d = getDb();
const row = d.prepare(`SELECT MAX(id) as m FROM ${table}`).get();
return (row.m || 0) + 1;
}
module.exports = { getDb, listAll, getOne, insertOne, updateOne, deleteOne, nextId, TABLES };
|