BlueGit

timfa filemanager

Files

filemanager / server / db.js

db.js

Persist last visited folder
timfa Tim Falken · pushed by timfa · 5 months ago
60304be1 History
db.js 2.8 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 uploaded_at TEXT
39 );
40
41 CREATE TABLE IF NOT EXISTS auth_sessions (
42 session_id TEXT PRIMARY KEY,
43 username TEXT NOT NULL,
44 ip TEXT NOT NULL,
45 region TEXT NOT NULL,
46 fp_hash TEXT NOT NULL,
47 created_at INTEGER NOT NULL,
48 last_seen_at INTEGER NOT NULL,
49 revoked_at INTEGER
50 );
51
52 CREATE INDEX IF NOT EXISTS idx_auth_sessions_username ON auth_sessions(username);
53 CREATE INDEX IF NOT EXISTS idx_auth_sessions_last_seen ON auth_sessions(last_seen_at);
54
55 CREATE TABLE IF NOT EXISTS user_last_navigation (
56 username TEXT PRIMARY KEY,
57 last_dir TEXT,
58 updated_at INTEGER NOT NULL
59 );
60
61 CREATE INDEX IF NOT EXISTS idx_user_last_navigation_updated_at ON user_last_navigation(updated_at);
62
63 CREATE TABLE IF NOT EXISTS share_tokens (
64 token TEXT PRIMARY KEY,
65 file_path TEXT NOT NULL,
66 is_dir INTEGER NOT NULL DEFAULT 0,
67 created_by TEXT NOT NULL,
68 created_at INTEGER NOT NULL,
69 expires_at INTEGER NOT NULL
70 );
71
72 CREATE INDEX IF NOT EXISTS idx_share_tokens_expires ON share_tokens(expires_at);
73 `);
74
75 const fileMetaCols = d.prepare('PRAGMA table_info(file_meta)').all();
76 const colNames = fileMetaCols.map((col) => String(col.name || ''));
77
78 if (!colNames.includes('watched_by'))
79 {
80 d.exec("ALTER TABLE file_meta ADD COLUMN watched_by TEXT NOT NULL DEFAULT '{}'");
81 }
82
83 if (!colNames.includes('sha256'))
84 {
85 d.exec('ALTER TABLE file_meta ADD COLUMN sha256 TEXT');
86 d.exec('CREATE INDEX IF NOT EXISTS idx_file_meta_sha256 ON file_meta(sha256)');
87 } else
88 {
89 d.exec('CREATE INDEX IF NOT EXISTS idx_file_meta_sha256 ON file_meta(sha256)');
90 }
91
92 if (!colNames.includes('uploaded_at'))
93 {
94 d.exec('ALTER TABLE file_meta ADD COLUMN uploaded_at TEXT');
95 }
96}
97
98function closeDb ()
99{
100 if (db)
101 {
102 db.close();
103 db = null;
104 }
105}
106
107module.exports = { getDb, initDb, closeDb };