snow-editor

small markdown and org-mode editor
Log | Files | Refs | README

db.js (2418B)

      1 import fs from 'fs';
      2 import path from 'path';
      3 import { DatabaseSync } from 'node:sqlite';
      4 
      5 let db;
      6 
      7 export function getDb() {
      8   if (!db) {
      9     throw new Error('Database not initialized');
     10   }
     11   return db;
     12 }
     13 
     14 export function initDb(databasePath) {
     15   const dir = path.dirname(databasePath);
     16   fs.mkdirSync(dir, { recursive: true });
     17 
     18   db = new DatabaseSync(databasePath);
     19   db.exec('PRAGMA journal_mode = WAL');
     20   db.exec('PRAGMA foreign_keys = ON');
     21   initSchema(db);
     22   return db;
     23 }
     24 
     25 function initSchema(database) {
     26   database.exec(`
     27     CREATE TABLE IF NOT EXISTS documents (
     28       id TEXT PRIMARY KEY,
     29       title TEXT NOT NULL DEFAULT 'Untitled document',
     30       mode TEXT NOT NULL CHECK (mode IN ('markdown', 'org')),
     31       content TEXT NOT NULL DEFAULT '',
     32       view_token TEXT UNIQUE NOT NULL,
     33       edit_token TEXT UNIQUE NOT NULL,
     34       expires_at TEXT,
     35       created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
     36       updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
     37     );
     38 
     39     CREATE TABLE IF NOT EXISTS edit_locks (
     40       id TEXT PRIMARY KEY,
     41       document_id TEXT NOT NULL,
     42       lock_token TEXT UNIQUE NOT NULL,
     43       client_id TEXT NOT NULL,
     44       expires_at TEXT NOT NULL,
     45       created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
     46       updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
     47       FOREIGN KEY (document_id) REFERENCES documents(id) ON DELETE CASCADE
     48     );
     49 
     50     CREATE TABLE IF NOT EXISTS document_versions (
     51       id TEXT PRIMARY KEY,
     52       document_id TEXT NOT NULL,
     53       title TEXT NOT NULL,
     54       mode TEXT NOT NULL CHECK (mode IN ('markdown', 'org')),
     55       content TEXT NOT NULL,
     56       created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
     57       FOREIGN KEY (document_id) REFERENCES documents(id) ON DELETE CASCADE
     58     );
     59 
     60     CREATE INDEX IF NOT EXISTS idx_documents_view_token ON documents(view_token);
     61     CREATE INDEX IF NOT EXISTS idx_documents_edit_token ON documents(edit_token);
     62     CREATE INDEX IF NOT EXISTS idx_edit_locks_document_id ON edit_locks(document_id);
     63     CREATE INDEX IF NOT EXISTS idx_edit_locks_lock_token ON edit_locks(lock_token);
     64   `);
     65 }
     66 
     67 export function purgeExpiredLocks(database) {
     68   const now = new Date().toISOString();
     69   database.prepare('DELETE FROM edit_locks WHERE expires_at <= ?').run(now);
     70 }
     71 
     72 export function checkDbHealth(database) {
     73   try {
     74     database.prepare('SELECT 1 AS ok').get();
     75     return true;
     76   } catch {
     77     return false;
     78   }
     79 }