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