BlueGit

timfa filemanager

Files

filemanager / server / db.js

db.js

WIP: Duplicate detection
timfa Tim Falken · pushed by timfa · 6 months ago
3fcdc0ca History
db.js 2.4 KiB Download Raw
1'use strict';
2
3const path = require('path');
4const { Database } = require('node-sqlite3-wasm');
5
6const DB_PATH = path.join(__dirname, '../filemanager.db');
7let db;
8
9function getDb ()
10{
11 if (!db)
12 {
13 db = new Database(DB_PATH);
14 db.exec('PRAGMA journal_mode = WAL');
15 db.exec('PRAGMA foreign_keys = ON');
16 }
17 return db;
18}
19
20function initDb ()
21{
22 const d = getDb();
23
24 d.exec(`
25 CREATE TABLE IF NOT EXISTS users (
26 id INTEGER PRIMARY KEY AUTOINCREMENT,
27 username TEXT UNIQUE NOT NULL,
28 password_hash TEXT NOT NULL,
29 is_admin INTEGER NOT NULL DEFAULT 0,
30 permissions TEXT NOT NULL DEFAULT '{}'
31 );
32
33 CREATE TABLE IF NOT EXISTS file_meta (
34 file_path TEXT PRIMARY KEY,
35 uploader TEXT NOT NULL DEFAULT '',
36 downloaders TEXT NOT NULL DEFAULT '[]',
37 watched_by TEXT NOT NULL DEFAULT '{}'
38 );
39
40 CREATE TABLE IF NOT EXISTS auth_sessions (
41 session_id TEXT PRIMARY KEY,
42 username TEXT NOT NULL,
43 ip TEXT NOT NULL,
44 region TEXT NOT NULL,
45 fp_hash TEXT NOT NULL,
46 created_at INTEGER NOT NULL,
47 last_seen_at INTEGER NOT NULL,
48 revoked_at INTEGER
49 );
50
51 CREATE INDEX IF NOT EXISTS idx_auth_sessions_username ON auth_sessions(username);
52 CREATE INDEX IF NOT EXISTS idx_auth_sessions_last_seen ON auth_sessions(last_seen_at);
53
54 CREATE TABLE IF NOT EXISTS share_tokens (
55 token TEXT PRIMARY KEY,
56 file_path TEXT NOT NULL,
57 is_dir INTEGER NOT NULL DEFAULT 0,
58 created_by TEXT NOT NULL,
59 created_at INTEGER NOT NULL,
60 expires_at INTEGER NOT NULL
61 );
62
63 CREATE INDEX IF NOT EXISTS idx_share_tokens_expires ON share_tokens(expires_at);
64 `);
65
66 const fileMetaCols = d.prepare('PRAGMA table_info(file_meta)').all();
67 const colNames = fileMetaCols.map((col) => String(col.name || ''));
68
69 if (!colNames.includes('watched_by'))
70 {
71 d.exec("ALTER TABLE file_meta ADD COLUMN watched_by TEXT NOT NULL DEFAULT '{}'");
72 }
73
74 if (!colNames.includes('sha256'))
75 {
76 d.exec('ALTER TABLE file_meta ADD COLUMN sha256 TEXT');
77 d.exec('CREATE INDEX IF NOT EXISTS idx_file_meta_sha256 ON file_meta(sha256)');
78 } else
79 {
80 d.exec('CREATE INDEX IF NOT EXISTS idx_file_meta_sha256 ON file_meta(sha256)');
81 }
82}
83
84function closeDb ()
85{
86 if (db)
87 {
88 db.close();
89 db = null;
90 }
91}
92
93module.exports = { getDb, initDb, closeDb };