Thoth / server /db.js
mfirat007's picture
Upload 46 files
f41aded verified
Raw History Blame Contribute Delete
3.02 kB
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;
}
}