Право на увид у код. Отворен протокол омогућава независне оператере и интероперабилност. Манифест
Извор

4d7883a894b3
   1 // SQLite (node:sqlite, WAL, FTS5). Times are integer milliseconds since epoch.
   2 import { DatabaseSync } from 'node:sqlite';
   3 import fs from 'node:fs';
   4 import path from 'node:path';
   5 
   6 export const SCHEMA = `
   7 CREATE TABLE IF NOT EXISTS meta (key TEXT PRIMARY KEY, value TEXT NOT NULL);
   8 CREATE TABLE IF NOT EXISTS users (
   9   id INTEGER PRIMARY KEY,
  10   handle TEXT NOT NULL UNIQUE COLLATE NOCASE,
  11   display TEXT NOT NULL,
  12   pass_hash TEXT NOT NULL,
  13   role TEXT NOT NULL DEFAULT 'member',
  14   level TEXT NOT NULL DEFAULT 'member',
  15   city TEXT NOT NULL DEFAULT '',
  16   bio TEXT NOT NULL DEFAULT '',
  17   avatar TEXT,
  18   hue INTEGER NOT NULL DEFAULT 200,
  19   member_no INTEGER NOT NULL UNIQUE,
  20   dinar INTEGER NOT NULL DEFAULT 0,
  21   created_at INTEGER NOT NULL,
  22   last_visit_at INTEGER NOT NULL DEFAULT 0,
  23   prev_visit_at INTEGER NOT NULL DEFAULT 0,
  24   banned INTEGER NOT NULL DEFAULT 0,
  25   deleted INTEGER NOT NULL DEFAULT 0,
  26   is_seed INTEGER NOT NULL DEFAULT 0,
  27   is_test INTEGER NOT NULL DEFAULT 0
  28 );
  29 CREATE TABLE IF NOT EXISTS sessions (
  30   id_hash TEXT PRIMARY KEY,
  31   user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  32   csrf TEXT NOT NULL,
  33   created_at INTEGER NOT NULL,
  34   expires_at INTEGER NOT NULL
  35 );
  36 CREATE INDEX IF NOT EXISTS sessions_user ON sessions(user_id);
  37 CREATE TABLE IF NOT EXISTS categories (
  38   id INTEGER PRIMARY KEY,
  39   slug TEXT NOT NULL UNIQUE,
  40   name TEXT NOT NULL,
  41   description TEXT NOT NULL,
  42   icon TEXT NOT NULL,
  43   position INTEGER NOT NULL,
  44   admin_only INTEGER NOT NULL DEFAULT 0
  45 );
  46 CREATE TABLE IF NOT EXISTS threads (
  47   id INTEGER PRIMARY KEY,
  48   category_id INTEGER NOT NULL REFERENCES categories(id),
  49   user_id INTEGER NOT NULL REFERENCES users(id),
  50   title TEXT NOT NULL,
  51   created_at INTEGER NOT NULL,
  52   last_post_at INTEGER NOT NULL,
  53   last_post_user INTEGER,
  54   reply_count INTEGER NOT NULL DEFAULT 0,
  55   views INTEGER NOT NULL DEFAULT 0,
  56   pinned INTEGER NOT NULL DEFAULT 0,
  57   locked INTEGER NOT NULL DEFAULT 0,
  58   deleted INTEGER NOT NULL DEFAULT 0
  59 );
  60 CREATE INDEX IF NOT EXISTS threads_cat ON threads(category_id, deleted, pinned DESC, last_post_at DESC);
  61 CREATE INDEX IF NOT EXISTS threads_last ON threads(deleted, last_post_at DESC);
  62 CREATE TABLE IF NOT EXISTS posts (
  63   id INTEGER PRIMARY KEY,
  64   thread_id INTEGER NOT NULL REFERENCES threads(id),
  65   user_id INTEGER NOT NULL REFERENCES users(id),
  66   body TEXT NOT NULL,
  67   created_at INTEGER NOT NULL,
  68   edited_at INTEGER,
  69   deleted INTEGER NOT NULL DEFAULT 0
  70 );
  71 CREATE INDEX IF NOT EXISTS posts_thread ON posts(thread_id, created_at);
  72 CREATE INDEX IF NOT EXISTS posts_user ON posts(user_id, created_at DESC);
  73 CREATE VIRTUAL TABLE IF NOT EXISTS search USING fts5(title, body, post_id UNINDEXED, thread_id UNINDEXED, tokenize = 'unicode61 remove_diacritics 2');
  74 CREATE TABLE IF NOT EXISTS reactions (
  75   post_id INTEGER NOT NULL REFERENCES posts(id),
  76   user_id INTEGER NOT NULL REFERENCES users(id),
  77   kind TEXT NOT NULL,
  78   created_at INTEGER NOT NULL,
  79   PRIMARY KEY (post_id, user_id, kind)
  80 );
  81 CREATE TABLE IF NOT EXISTS reports (
  82   id INTEGER PRIMARY KEY,
  83   post_id INTEGER NOT NULL REFERENCES posts(id),
  84   user_id INTEGER NOT NULL REFERENCES users(id),
  85   reason TEXT NOT NULL,
  86   created_at INTEGER NOT NULL,
  87   resolved_by INTEGER,
  88   resolved_at INTEGER,
  89   outcome TEXT
  90 );
  91 CREATE TABLE IF NOT EXISTS notifications (
  92   id INTEGER PRIMARY KEY,
  93   user_id INTEGER NOT NULL REFERENCES users(id),
  94   kind TEXT NOT NULL,
  95   actor_id INTEGER,
  96   thread_id INTEGER,
  97   post_id INTEGER,
  98   created_at INTEGER NOT NULL,
  99   read INTEGER NOT NULL DEFAULT 0
 100 );
 101 CREATE INDEX IF NOT EXISTS notif_user ON notifications(user_id, read, created_at DESC);
 102 CREATE TABLE IF NOT EXISTS thread_reads (
 103   user_id INTEGER NOT NULL,
 104   thread_id INTEGER NOT NULL,
 105   read_at INTEGER NOT NULL,
 106   PRIMARY KEY (user_id, thread_id)
 107 );
 108 CREATE TABLE IF NOT EXISTS ledger (
 109   id INTEGER PRIMARY KEY,
 110   user_id INTEGER NOT NULL REFERENCES users(id),
 111   delta INTEGER NOT NULL,
 112   reason TEXT NOT NULL,
 113   ref TEXT,
 114   created_at INTEGER NOT NULL
 115 );
 116 CREATE INDEX IF NOT EXISTS ledger_user ON ledger(user_id, created_at DESC);
 117 CREATE TABLE IF NOT EXISTS user_cards (
 118   id INTEGER PRIMARY KEY,
 119   user_id INTEGER NOT NULL REFERENCES users(id),
 120   edition TEXT NOT NULL,
 121   serial INTEGER NOT NULL,
 122   price INTEGER NOT NULL,
 123   created_at INTEGER NOT NULL,
 124   UNIQUE (user_id, edition),
 125   UNIQUE (edition, serial)
 126 );
 127 CREATE TABLE IF NOT EXISTS mod_log (
 128   id INTEGER PRIMARY KEY,
 129   actor_id INTEGER NOT NULL,
 130   action TEXT NOT NULL,
 131   target TEXT NOT NULL,
 132   note TEXT,
 133   created_at INTEGER NOT NULL
 134 );
 135 `;
 136 
 137 export function openDb(dataDir) {
 138   fs.mkdirSync(dataDir, { recursive: true });
 139   const db = new DatabaseSync(path.join(dataDir, 'forum.db'));
 140   db.exec('PRAGMA journal_mode = WAL; PRAGMA synchronous = NORMAL; PRAGMA foreign_keys = ON; PRAGMA busy_timeout = 5000;');
 141   db.exec(SCHEMA);
 142   const cache = new Map();
 143   const q = (sql) => {
 144     let st = cache.get(sql);
 145     if (!st) { st = db.prepare(sql); cache.set(sql, st); }
 146     return st;
 147   };
 148   let depth = 0;
 149   const api = {
 150     raw: db, dataDir, dataDir,
 151     get: (sql, ...a) => q(sql).get(...a),
 152     all: (sql, ...a) => q(sql).all(...a),
 153     run: (sql, ...a) => q(sql).run(...a),
 154     tx(fn) {
 155       const nested = depth > 0, savepoint = 'yu_' + depth;
 156       db.exec(nested ? 'SAVEPOINT ' + savepoint : 'BEGIN IMMEDIATE');
 157       depth++;
 158       try {
 159         const result = fn();
 160         db.exec(nested ? 'RELEASE ' + savepoint : 'COMMIT');
 161         return result;
 162       } catch (error) {
 163         db.exec(nested ? 'ROLLBACK TO ' + savepoint : 'ROLLBACK');
 164         if (nested) db.exec('RELEASE ' + savepoint);
 165         throw error;
 166       } finally { depth--; }
 167     },
 168     meta(key, value) {
 169       if (value === undefined) return api.get('SELECT value FROM meta WHERE key = ?', key)?.value;
 170       api.run('INSERT INTO meta(key, value) VALUES(?, ?) ON CONFLICT(key) DO UPDATE SET value = excluded.value', key, String(value));
 171     },
 172     close: () => db.close(),
 173   };
 174   return api;
 175 }
 176 
 177 // Change DINAR balance and write the ledger row in one step (caller holds a transaction when needed).
 178 export function dinar(db, userId, delta, reason, ref = null, at = Date.now()) {
 179   db.run('UPDATE users SET dinar = dinar + ? WHERE id = ?', delta, userId);
 180   db.run('INSERT INTO ledger(user_id, delta, reason, ref, created_at) VALUES(?, ?, ?, ?, ?)', userId, delta, reason, ref, at);
 181 }
 182 
 183 export function indexPost(db, postId, threadId, title, body) {
 184   db.run('INSERT INTO search(title, body, post_id, thread_id) VALUES(?, ?, ?, ?)', title, body, postId, threadId);
 185 }
 186 
 187 export function unindexPost(db, postId) {
 188   db.run('DELETE FROM search WHERE post_id = ?', postId);
 189 }
 190 
 191 export function backup(db, dataDir, now = new Date()) {
 192   const dir = path.join(dataDir, 'backups');
 193   fs.mkdirSync(dir, { recursive: true });
 194   const name = `forum-${now.toISOString().slice(0, 10)}.db`;
 195   const file = path.join(dir, name);
 196   if (fs.existsSync(file)) return null;
 197   db.raw.exec(`VACUUM INTO '${file.replace(/'/g, "''")}'`);
 198   const all = fs.readdirSync(dir).filter((f) => /^forum-\d{4}-\d{2}-\d{2}\.db$/.test(f)).sort();
 199   for (const old of all.slice(0, Math.max(0, all.length - 7))) fs.rmSync(path.join(dir, old));
 200   return name;
 201 }
 202