Spaces:
Sleeping
Sleeping
Download src/db/index.js from dangkhoa2016/Nodejs-JSON-API-Server: direct link, hf CLI and curl.
- Browser
- Download file 6.19 kB
-
https://huggingface.co/spaces/dangkhoa2016/Nodejs-JSON-API-Server/resolve/main/src/db/index.js
- Command line
-
hf download hf://spaces/dangkhoa2016/Nodejs-JSON-API-Server/src/db/index.js
-
curl -L -o index.js https://huggingface.co/spaces/dangkhoa2016/Nodejs-JSON-API-Server/resolve/main/src/db/index.js
6.19 kB
| ; | |
| 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 }; | |