File size: 10,439 Bytes
1a25764
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
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
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
'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 migrate() {
  const d = getDb();
  d.exec(`
    CREATE TABLE IF NOT EXISTS users (
      id      INTEGER PRIMARY KEY,
      name    TEXT,
      username TEXT UNIQUE,
      email   TEXT,
      phone   TEXT,
      website TEXT,
      address TEXT,
      company TEXT
    );

    CREATE TABLE IF NOT EXISTS posts (
      id      INTEGER PRIMARY KEY,
      userId  INTEGER,
      title   TEXT,
      body    TEXT,
      FOREIGN KEY(userId) REFERENCES users(id)
    );

    CREATE TABLE IF NOT EXISTS comments (
      id      INTEGER PRIMARY KEY,
      postId  INTEGER,
      name    TEXT,
      email   TEXT,
      body    TEXT,
      FOREIGN KEY(postId) REFERENCES posts(id)
    );

    CREATE TABLE IF NOT EXISTS albums (
      id     INTEGER PRIMARY KEY,
      userId INTEGER,
      title  TEXT,
      FOREIGN KEY(userId) REFERENCES users(id)
    );

    CREATE TABLE IF NOT EXISTS photos (
      id           INTEGER PRIMARY KEY,
      albumId      INTEGER,
      title        TEXT,
      url          TEXT,
      thumbnailUrl TEXT,
      FOREIGN KEY(albumId) REFERENCES albums(id)
    );

    CREATE TABLE IF NOT EXISTS todos (
      id        INTEGER PRIMARY KEY,
      userId    INTEGER,
      title     TEXT,
      completed INTEGER DEFAULT 0,
      FOREIGN KEY(userId) REFERENCES users(id)
    );
  `);
}

function seed() {
  const d = getDb();
  const count = d.prepare('SELECT COUNT(*) as c FROM users').get();
  if (count.c > 0) return;

  console.log('[DB] Seeding initial data…');

  const users = [
    { id: 1, name: 'Leanne Graham', username: 'Bret', email: 'Sincere@april.biz', phone: '1-770-736-8031 x56442', website: 'hildegard.org', address: JSON.stringify({ street: 'Kulas Light', suite: 'Apt. 556', city: 'Gwenborough', zipcode: '92998-3874', geo: { lat: '-37.3159', lng: '81.1496' } }), company: JSON.stringify({ name: 'Romaguera-Crona', catchPhrase: 'Multi-layered client-server neural-net', bs: 'harness real-time e-markets' }) },
    { id: 2, name: 'Ervin Howell', username: 'Antonette', email: 'Shanna@melissa.tv', phone: '010-692-6593 x09125', website: 'anastasia.net', address: JSON.stringify({ street: 'Victor Plains', suite: 'Suite 879', city: 'Wisokyburgh', zipcode: '90566-7771', geo: { lat: '-43.9509', lng: '-34.7618' } }), company: JSON.stringify({ name: 'Deckow-Crist', catchPhrase: 'Proactive didactic contingency', bs: 'synergize scalable supply-chains' }) },
    { id: 3, name: 'Clementine Bauch', username: 'Samantha', email: 'Nathan@yesenia.net', phone: '1-463-123-4447', website: 'ramiro.info', address: JSON.stringify({ street: 'Douglas Extension', suite: 'Suite 847', city: 'McKenziehaven', zipcode: '59590-4157', geo: { lat: '-68.6102', lng: '-47.0653' } }), company: JSON.stringify({ name: 'Romaguera-Jacobson', catchPhrase: 'Face to face bifurcated interface', bs: 'e-enable strategic applications' }) },
    { id: 4, name: 'Patricia Lebsack', username: 'Karianne', email: 'Julianne.OConner@kory.org', phone: '493-170-9623 x156', website: 'kale.biz', address: JSON.stringify({ street: 'Hoeger Mall', suite: 'Apt. 692', city: 'South Elvis', zipcode: '53919-4257', geo: { lat: '29.4572', lng: '-164.2990' } }), company: JSON.stringify({ name: 'Robel-Corkery', catchPhrase: 'Multi-tiered zero tolerance productivity', bs: 'transition cutting-edge web services' }) },
    { id: 5, name: 'Chelsey Dietrich', username: 'Kamren', email: 'Lucio_Hettinger@annie.ca', phone: '(254)954-1289', website: 'demarco.info', address: JSON.stringify({ street: 'Skiles Walks', suite: 'Suite 351', city: 'Roscoeview', zipcode: '33263', geo: { lat: '-31.8129', lng: '62.5342' } }), company: JSON.stringify({ name: 'Keebler LLC', catchPhrase: 'User-centric fault-tolerant solution', bs: 'revolutionize end-to-end systems' }) },
  ];

  const insertUser = d.prepare(`INSERT INTO users (id,name,username,email,phone,website,address,company) VALUES (?,?,?,?,?,?,?,?)`);
  for (const u of users) {
    insertUser.run(u.id, u.name, u.username, u.email, u.phone, u.website, u.address, u.company);
  }

  const insertPost = d.prepare(`INSERT INTO posts (id,userId,title,body) VALUES (?,?,?,?)`);
  const titles = ['sunt aut facere repellat provident', 'qui est esse', 'ea molestias quasi exercitationem', 'eum et est occaecati', 'nesciunt quas odio', 'dolorem eum magni eos aperiam', 'magnam facilis autem', 'dolorem dolore est ipsam', 'nesciunt iure omnis dolorem', 'optio molestias id quia eum'];
  const bodies = ['quia et suscipit\nsuscipit recusandae consequuntur', 'est rerum tempore vitae\nsequi sint nihil reprehenderit dolor', 'et iusto sed quo iure\nvoluptatem occaecati omnis eligendi', 'ullam et saepe reiciendis voluptatem adipisci\nsit amet autem assumenda', 'repudiandae veniam quaerat sunt sed\nalias aut fugiat sit autem', 'ut aspernatur corporis harum nihil quis', 'eveniet quod temporibus', 'in ut explicabo rerum consectetur aut', 'eum accusamus quia doloribus', 'voluptas blanditiis repellat itaque'];
  let postId = 1;
  for (let u = 1; u <= 5; u++) {
    for (let i = 0; i < 10; i++, postId++) {
      insertPost.run(postId, u, titles[i], bodies[i]);
    }
  }

  const insertComment = d.prepare(`INSERT INTO comments (id,postId,name,email,body) VALUES (?,?,?,?,?)`);
  let commentId = 1;
  for (let p = 1; p <= 50; p++) {
    for (let i = 0; i < 5; i++, commentId++) {
      insertComment.run(commentId, p, `Comment ${i + 1} on post ${p}`, `commenter${commentId}@example.com`, `Great post! Comment body ${commentId}.`);
    }
  }

  const insertAlbum = d.prepare(`INSERT INTO albums (id,userId,title) VALUES (?,?,?)`);
  const albumTitles = ['quidem molestiae enim', 'sunt qui excepturi placeat culpa', 'omnis laborum odio', 'non esse culpa molestiae omnis sed optio', 'eaque aut omnis a', 'natus impedit quibusdam illo est', 'quibusdam autem aliquid et et quia', 'cum voluptatibus rerum architecto harum nihil', 'quisquam choro et aut officiis'];
  let albumId = 1;
  for (let u = 1; u <= 5; u++) {
    for (let i = 0; i < 2; i++, albumId++) {
      insertAlbum.run(albumId, u, albumTitles[(albumId - 1) % albumTitles.length]);
    }
  }

  const insertPhoto = d.prepare(`INSERT INTO photos (id,albumId,title,url,thumbnailUrl) VALUES (?,?,?,?,?)`);
  let photoId = 1;
  for (let a = 1; a <= 10; a++) {
    for (let i = 0; i < 5; i++, photoId++) {
      insertPhoto.run(photoId, a, `Photo ${photoId}`, `https://via.placeholder.com/600/${photoId}`, `https://via.placeholder.com/150/${photoId}`);
    }
  }

  const insertTodo = d.prepare(`INSERT INTO todos (id,userId,title,completed) VALUES (?,?,?,?)`);
  const todoTitles = ['delectus aut autem', 'quis ut nam facilis et officia qui', 'fugiat veniam minus', 'et porro tempora'];
  let todoId = 1;
  for (let u = 1; u <= 5; u++) {
    for (let i = 0; i < 4; i++, todoId++) {
      insertTodo.run(todoId, u, todoTitles[i], todoId % 2 === 0 ? 1 : 0);
    }
  }

  console.log('[DB] Seeding done.');
}

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 = { migrate, seed, listAll, getOne, insertOne, updateOne, deleteOne, nextId, TABLES };