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 };