import { DatabaseSync } from 'node:sqlite'; import path from 'node:path'; import { DATA_DIR } from './config.js'; export const db = new DatabaseSync(path.join(DATA_DIR, 'thoth.db')); db.exec(` PRAGMA journal_mode = WAL; PRAGMA foreign_keys = ON; CREATE TABLE IF NOT EXISTS users ( id INTEGER PRIMARY KEY, email TEXT NOT NULL UNIQUE COLLATE NOCASE, name TEXT NOT NULL, password_hash TEXT NOT NULL, created_at TEXT NOT NULL DEFAULT (datetime('now')) ); CREATE TABLE IF NOT EXISTS sessions ( token_hash TEXT PRIMARY KEY, user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE, expires_at TEXT NOT NULL ); CREATE TABLE IF NOT EXISTS projects ( id TEXT PRIMARY KEY, owner_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE, title TEXT NOT NULL, main_file TEXT NOT NULL DEFAULT 'main.tex', rev_counter INTEGER NOT NULL DEFAULT 0, created_at TEXT NOT NULL DEFAULT (datetime('now')), updated_at TEXT NOT NULL DEFAULT (datetime('now')) ); CREATE TABLE IF NOT EXISTS project_members ( project_id TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE, user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE, role TEXT NOT NULL CHECK (role IN ('editor', 'viewer')), PRIMARY KEY (project_id, user_id) ); -- content: metin dosyalarında TEXT, ikili dosyalarda (görsel, PDF) BLOB olarak saklanır. CREATE TABLE IF NOT EXISTS files ( project_id TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE, path TEXT NOT NULL, content BLOB NOT NULL, is_binary INTEGER NOT NULL DEFAULT 0, rev INTEGER NOT NULL, PRIMARY KEY (project_id, path) ); CREATE TABLE IF NOT EXISTS versions ( id INTEGER PRIMARY KEY, project_id TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE, user_id INTEGER REFERENCES users(id) ON DELETE SET NULL, label TEXT NOT NULL, is_auto INTEGER NOT NULL DEFAULT 0, created_at TEXT NOT NULL DEFAULT (datetime('now')) ); CREATE TABLE IF NOT EXISTS version_files ( version_id INTEGER NOT NULL REFERENCES versions(id) ON DELETE CASCADE, path TEXT NOT NULL, content BLOB NOT NULL, is_binary INTEGER NOT NULL DEFAULT 0, PRIMARY KEY (version_id, path) ); CREATE TABLE IF NOT EXISTS comments ( id INTEGER PRIMARY KEY, project_id TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE, parent_id INTEGER REFERENCES comments(id) ON DELETE CASCADE, user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE, file_path TEXT, line INTEGER, quote TEXT, body TEXT NOT NULL, resolved INTEGER NOT NULL DEFAULT 0, created_at TEXT NOT NULL DEFAULT (datetime('now')) ); CREATE INDEX IF NOT EXISTS idx_versions_project ON versions(project_id, id); CREATE INDEX IF NOT EXISTS idx_comments_project ON comments(project_id, id); `); /** fn'i tek bir veritabanı işlemi (transaction) içinde çalıştırır. */ export function tx(fn) { db.exec('BEGIN IMMEDIATE'); try { const result = fn(); db.exec('COMMIT'); return result; } catch (err) { db.exec('ROLLBACK'); throw err; } }