1<?php
2if (!defined('NEXUS')) exit('Forbidden');
3
4/**
5 * Database abstraction — supports SQLite3, MySQL, and MariaDB.
6 * Driver is selected from DB_DRIVER constant (set by installer or config.php).
7 *
8 * SQLite: DB_DRIVER='sqlite' (default, no credentials needed)
9 * MySQL: DB_DRIVER='mysql' + DB_HOST, DB_NAME, DB_USER, DB_PASS, DB_PORT
10 * MariaDB: DB_DRIVER='mysql' (same driver as MySQL via PDO)
11 */
12class DB {
13 private static ?PDO $pdo = null;
14
15
16 public static function connect(): PDO {
17 if (self::$pdo !== null) return self::$pdo;
18
19 $driver = defined('DB_DRIVER') ? DB_DRIVER : 'sqlite';
20
21 if ($driver === 'mysql') {
22 $host = defined('DB_HOST') ? DB_HOST : '127.0.0.1';
23 $port = defined('DB_PORT') ? DB_PORT : '3306';
24 $name = defined('DB_NAME') ? DB_NAME : 'nexus';
25 $user = defined('DB_USER') ? DB_USER : 'root';
26 $pass = defined('DB_PASS') ? DB_PASS : '';
27 $charset = defined('DB_CHARSET') ? DB_CHARSET : 'utf8mb4';
28
29 $dsn = "mysql:host={$host};port={$port};dbname={$name};charset={$charset}";
30 self::$pdo = new PDO($dsn, $user, $pass, [
31 PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
32 PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
33 PDO::ATTR_EMULATE_PREPARES => false,
34 PDO::MYSQL_ATTR_FOUND_ROWS => true,
35 ]);
36 self::$pdo->exec("SET SESSION sql_mode='STRICT_TRANS_TABLES,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO'");
37 } else {
38
39 $path = DATA . '/forum.db';
40 if (!is_dir(DATA)) mkdir(DATA, 0750, true);
41 self::$pdo = new PDO('sqlite:' . $path, null, null, [
42 PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
43 PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
44 ]);
45 self::$pdo->exec('PRAGMA foreign_keys = ON');
46 self::$pdo->exec('PRAGMA journal_mode = WAL');
47 self::$pdo->exec('PRAGMA synchronous = NORMAL');
48 self::$pdo->exec('PRAGMA temp_store = MEMORY');
49 self::$pdo->exec('PRAGMA mmap_size = 268435456');
50 }
51
52 return self::$pdo;
53 }
54
55 public static function driver(): string {
56 return defined('DB_DRIVER') ? DB_DRIVER : 'sqlite';
57 }
58
59 public static function isMysql(): bool {
60 return self::driver() === 'mysql';
61 }
62
63
64 public static function run(string $sql, array $p = []): PDOStatement {
65 $s = self::connect()->prepare($sql);
66 $s->execute($p);
67 return $s;
68 }
69
70 public static function row(string $sql, array $p = []): ?array {
71 $r = self::run($sql, $p)->fetch();
72 return $r ?: null;
73 }
74
75 public static function rows(string $sql, array $p = []): array {
76 return self::run($sql, $p)->fetchAll();
77 }
78
79 public static function insert(string $sql, array $p = []): int {
80 self::run($sql, $p);
81 return (int) self::connect()->lastInsertId();
82 }
83
84 public static function val(string $sql, array $p = []): mixed {
85 $row = self::run($sql, $p)->fetch(PDO::FETCH_NUM);
86 return $row ? $row[0] : null;
87 }
88
89 /**
90 * Cross-driver INSERT IGNORE.
91 * Silently skips if the row already exists (duplicate key).
92 */
93 public static function insertIgnore(string $table, array $cols, array $vals): void {
94 $placeholders = implode(',', array_fill(0, count($vals), '?'));
95 $colList = implode(',', array_map(fn($c) => "`$c`", $cols));
96 if (self::isMysql()) {
97 self::run("INSERT IGNORE INTO `{$table}` ({$colList}) VALUES ({$placeholders})", $vals);
98 } else {
99 self::run("INSERT OR IGNORE INTO {$table} (" . implode(',', $cols) . ") VALUES ({$placeholders})", $vals);
100 }
101 }
102
103 /**
104 * Cross-driver upsert (INSERT ... ON DUPLICATE KEY UPDATE for MySQL,
105 * INSERT OR REPLACE for SQLite).
106 * Only works for simple single-column key tables like settings(key,value).
107 */
108 public static function upsert(string $table, string $keyCol, string $valCol, string $key, string $val): void {
109 if (self::isMysql()) {
110 self::run(
111 "INSERT INTO `{$table}` (`{$keyCol}`,`{$valCol}`) VALUES (?,?) ON DUPLICATE KEY UPDATE `{$valCol}`=VALUES(`{$valCol}`)",
112 [$key, $val]
113 );
114 } else {
115 self::run(
116 "INSERT OR REPLACE INTO {$table} ({$keyCol},{$valCol}) VALUES (?,?)",
117 [$key, $val]
118 );
119 }
120 }
121
122
123 public static function init(): void {
124 $mysql = self::isMysql();
125
126
127 $now = $mysql ? "DEFAULT CURRENT_TIMESTAMP" : "DEFAULT (datetime('now'))";
128 $auto = $mysql ? "INT AUTO_INCREMENT" : "INTEGER PRIMARY KEY AUTOINCREMENT";
129 $pk = $mysql ? "PRIMARY KEY" : "";
130 $eng = $mysql ? "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci" : "";
131
132 $tables = [];
133
134
135 $tables[] = "CREATE TABLE IF NOT EXISTS settings (
136 `key` VARCHAR(100) PRIMARY KEY,
137 `value` TEXT NOT NULL
138 ) $eng";
139
140
141 if ($mysql) {
142 $tables[] = "CREATE TABLE IF NOT EXISTS users (
143 id INT AUTO_INCREMENT PRIMARY KEY,
144 username VARCHAR(30) NOT NULL,
145 email VARCHAR(254) NOT NULL,
146 password VARCHAR(255) NOT NULL,
147 avatar TEXT,
148 bio TEXT,
149 role VARCHAR(20) NOT NULL DEFAULT 'member',
150 permissions TEXT NOT NULL DEFAULT '{}',
151 post_count INT NOT NULL DEFAULT 0,
152 topic_count INT NOT NULL DEFAULT 0,
153 karma INT NOT NULL DEFAULT 0,
154 suspended TINYINT NOT NULL DEFAULT 0,
155 silenced TINYINT NOT NULL DEFAULT 0,
156 location VARCHAR(100) NOT NULL DEFAULT '',
157 friends_hidden TINYINT NOT NULL DEFAULT 0,
158 joined_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
159 last_seen DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
160 UNIQUE KEY uq_username (username),
161 UNIQUE KEY uq_email (email)
162 ) $eng";
163 } else {
164 $tables[] = "CREATE TABLE IF NOT EXISTS users (
165 id INTEGER PRIMARY KEY AUTOINCREMENT,
166 username TEXT NOT NULL UNIQUE,
167 email TEXT NOT NULL UNIQUE,
168 password TEXT NOT NULL,
169 avatar TEXT,
170 bio TEXT,
171 role TEXT NOT NULL DEFAULT 'member',
172 permissions TEXT NOT NULL DEFAULT '{}',
173 post_count INTEGER NOT NULL DEFAULT 0,
174 topic_count INTEGER NOT NULL DEFAULT 0,
175 karma INTEGER NOT NULL DEFAULT 0,
176 suspended INTEGER NOT NULL DEFAULT 0,
177 silenced INTEGER NOT NULL DEFAULT 0,
178 location TEXT NOT NULL DEFAULT '',
179 friends_hidden INTEGER NOT NULL DEFAULT 0,
180 joined_at TEXT NOT NULL DEFAULT (datetime('now')),
181 last_seen TEXT NOT NULL DEFAULT (datetime('now'))
182 )";
183 }
184
185
186 if ($mysql) {
187 $tables[] = "CREATE TABLE IF NOT EXISTS categories (
188 id INT AUTO_INCREMENT PRIMARY KEY,
189 name VARCHAR(100) NOT NULL,
190 slug VARCHAR(100) NOT NULL,
191 description TEXT NOT NULL DEFAULT '',
192 color VARCHAR(20) NOT NULL DEFAULT '#3b82f6',
193 icon VARCHAR(10) NOT NULL DEFAULT '💬',
194 position INT NOT NULL DEFAULT 0,
195 parent_id INT,
196 topic_count INT NOT NULL DEFAULT 0,
197 post_count INT NOT NULL DEFAULT 0,
198 read_role VARCHAR(20) NOT NULL DEFAULT 'guest',
199 post_role VARCHAR(20) NOT NULL DEFAULT 'member',
200 reply_role VARCHAR(20) NOT NULL DEFAULT 'member',
201 UNIQUE KEY uq_slug (slug)
202 ) $eng";
203 } else {
204 $tables[] = "CREATE TABLE IF NOT EXISTS categories (
205 id INTEGER PRIMARY KEY AUTOINCREMENT,
206 name TEXT NOT NULL,
207 slug TEXT NOT NULL UNIQUE,
208 description TEXT NOT NULL DEFAULT '',
209 color TEXT NOT NULL DEFAULT '#3b82f6',
210 icon TEXT NOT NULL DEFAULT '💬',
211 position INTEGER NOT NULL DEFAULT 0,
212 parent_id INTEGER,
213 topic_count INTEGER NOT NULL DEFAULT 0,
214 post_count INTEGER NOT NULL DEFAULT 0,
215 read_role TEXT NOT NULL DEFAULT 'guest',
216 post_role TEXT NOT NULL DEFAULT 'member',
217 reply_role TEXT NOT NULL DEFAULT 'member'
218 )";
219 }
220
221
222 if ($mysql) {
223 $tables[] = "CREATE TABLE IF NOT EXISTS topics (
224 id INT AUTO_INCREMENT PRIMARY KEY,
225 title VARCHAR(200) NOT NULL,
226 slug VARCHAR(220) NOT NULL,
227 category_id INT NOT NULL,
228 user_id INT NOT NULL,
229 views INT NOT NULL DEFAULT 0,
230 reply_count INT NOT NULL DEFAULT 0,
231 pinned TINYINT NOT NULL DEFAULT 0,
232 closed TINYINT NOT NULL DEFAULT 0,
233 archived TINYINT NOT NULL DEFAULT 0,
234 last_post_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
235 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
236 UNIQUE KEY uq_slug (slug),
237 KEY ix_cat (category_id),
238 KEY ix_user (user_id)
239 ) $eng";
240 } else {
241 $tables[] = "CREATE TABLE IF NOT EXISTS topics (
242 id INTEGER PRIMARY KEY AUTOINCREMENT,
243 title TEXT NOT NULL,
244 slug TEXT NOT NULL UNIQUE,
245 category_id INTEGER NOT NULL,
246 user_id INTEGER NOT NULL,
247 views INTEGER NOT NULL DEFAULT 0,
248 reply_count INTEGER NOT NULL DEFAULT 0,
249 pinned INTEGER NOT NULL DEFAULT 0,
250 closed INTEGER NOT NULL DEFAULT 0,
251 archived INTEGER NOT NULL DEFAULT 0,
252 last_post_at TEXT NOT NULL DEFAULT (datetime('now')),
253 created_at TEXT NOT NULL DEFAULT (datetime('now'))
254 )";
255 }
256
257
258 if ($mysql) {
259 $tables[] = "CREATE TABLE IF NOT EXISTS posts (
260 id INT AUTO_INCREMENT PRIMARY KEY,
261 topic_id INT NOT NULL,
262 user_id INT NOT NULL,
263 content TEXT NOT NULL,
264 post_num INT NOT NULL,
265 reply_to INT,
266 likes INT NOT NULL DEFAULT 0,
267 edited TINYINT NOT NULL DEFAULT 0,
268 edit_reason VARCHAR(200),
269 deleted TINYINT NOT NULL DEFAULT 0,
270 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
271 updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
272 KEY ix_topic (topic_id),
273 KEY ix_user (user_id)
274 ) $eng";
275 } else {
276 $tables[] = "CREATE TABLE IF NOT EXISTS posts (
277 id INTEGER PRIMARY KEY AUTOINCREMENT,
278 topic_id INTEGER NOT NULL,
279 user_id INTEGER NOT NULL,
280 content TEXT NOT NULL,
281 post_num INTEGER NOT NULL,
282 reply_to INTEGER,
283 likes INTEGER NOT NULL DEFAULT 0,
284 edited INTEGER NOT NULL DEFAULT 0,
285 edit_reason TEXT,
286 deleted INTEGER NOT NULL DEFAULT 0,
287 created_at TEXT NOT NULL DEFAULT (datetime('now')),
288 updated_at TEXT NOT NULL DEFAULT (datetime('now'))
289 )";
290 }
291
292
293 if ($mysql) {
294 $tables[] = "CREATE TABLE IF NOT EXISTS likes (
295 post_id INT NOT NULL,
296 user_id INT NOT NULL,
297 PRIMARY KEY (post_id, user_id)
298 ) $eng";
299 } else {
300 $tables[] = "CREATE TABLE IF NOT EXISTS likes (
301 post_id INTEGER NOT NULL,
302 user_id INTEGER NOT NULL,
303 PRIMARY KEY (post_id, user_id)
304 )";
305 }
306
307
308 if ($mysql) {
309 $tables[] = "CREATE TABLE IF NOT EXISTS tags (
310 id INT AUTO_INCREMENT PRIMARY KEY,
311 name VARCHAR(50) NOT NULL,
312 topic_count INT NOT NULL DEFAULT 0,
313 UNIQUE KEY uq_name (name)
314 ) $eng";
315 $tables[] = "CREATE TABLE IF NOT EXISTS topic_tags (
316 topic_id INT NOT NULL,
317 tag_id INT NOT NULL,
318 PRIMARY KEY (topic_id, tag_id)
319 ) $eng";
320 } else {
321 $tables[] = "CREATE TABLE IF NOT EXISTS tags (
322 id INTEGER PRIMARY KEY AUTOINCREMENT,
323 name TEXT NOT NULL UNIQUE,
324 topic_count INTEGER NOT NULL DEFAULT 0
325 )";
326 $tables[] = "CREATE TABLE IF NOT EXISTS topic_tags (
327 topic_id INTEGER NOT NULL,
328 tag_id INTEGER NOT NULL,
329 PRIMARY KEY (topic_id, tag_id)
330 )";
331 }
332
333
334 if ($mysql) {
335 $tables[] = "CREATE TABLE IF NOT EXISTS notifications (
336 id INT AUTO_INCREMENT PRIMARY KEY,
337 user_id INT NOT NULL,
338 type VARCHAR(50) NOT NULL,
339 payload TEXT NOT NULL,
340 `read` TINYINT NOT NULL DEFAULT 0,
341 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
342 KEY ix_user (user_id)
343 ) $eng";
344 } else {
345 $tables[] = "CREATE TABLE IF NOT EXISTS notifications (
346 id INTEGER PRIMARY KEY AUTOINCREMENT,
347 user_id INTEGER NOT NULL,
348 type TEXT NOT NULL,
349 payload TEXT NOT NULL DEFAULT '{}',
350 read INTEGER NOT NULL DEFAULT 0,
351 created_at TEXT NOT NULL DEFAULT (datetime('now'))
352 )";
353 }
354
355
356 if ($mysql) {
357 $tables[] = "CREATE TABLE IF NOT EXISTS friends (
358 id INT AUTO_INCREMENT PRIMARY KEY,
359 user_id INT NOT NULL,
360 friend_id INT NOT NULL,
361 status VARCHAR(20) NOT NULL DEFAULT 'pending',
362 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
363 UNIQUE KEY uq_pair (user_id, friend_id),
364 KEY ix_uid (user_id),
365 KEY ix_fid (friend_id)
366 ) $eng";
367 } else {
368 $tables[] = "CREATE TABLE IF NOT EXISTS friends (
369 id INTEGER PRIMARY KEY AUTOINCREMENT,
370 user_id INTEGER NOT NULL,
371 friend_id INTEGER NOT NULL,
372 status TEXT NOT NULL DEFAULT 'pending',
373 created_at TEXT NOT NULL DEFAULT (datetime('now')),
374 UNIQUE(user_id, friend_id)
375 )";
376 }
377
378
379 if ($mysql) {
380 $tables[] = "CREATE TABLE IF NOT EXISTS messages (
381 id INT AUTO_INCREMENT PRIMARY KEY,
382 sender_id INT NOT NULL,
383 receiver_id INT NOT NULL,
384 subject VARCHAR(150) NOT NULL DEFAULT '',
385 body TEXT NOT NULL,
386 is_read TINYINT NOT NULL DEFAULT 0,
387 deleted_by_sender TINYINT NOT NULL DEFAULT 0,
388 deleted_by_receiver TINYINT NOT NULL DEFAULT 0,
389 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
390 KEY ix_recv (receiver_id),
391 KEY ix_send (sender_id)
392 ) $eng";
393 } else {
394 $tables[] = "CREATE TABLE IF NOT EXISTS messages (
395 id INTEGER PRIMARY KEY AUTOINCREMENT,
396 sender_id INTEGER NOT NULL,
397 receiver_id INTEGER NOT NULL,
398 subject TEXT NOT NULL DEFAULT '',
399 body TEXT NOT NULL,
400 is_read INTEGER NOT NULL DEFAULT 0,
401 deleted_by_sender INTEGER NOT NULL DEFAULT 0,
402 deleted_by_receiver INTEGER NOT NULL DEFAULT 0,
403 created_at TEXT NOT NULL DEFAULT (datetime('now'))
404 )";
405 }
406
407
408 if ($mysql) {
409 $tables[] = "CREATE TABLE IF NOT EXISTS rate_events (
410 id INT AUTO_INCREMENT PRIMARY KEY,
411 user_id INT NOT NULL,
412 event_type VARCHAR(20) NOT NULL DEFAULT 'post',
413 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
414 KEY ix_rate (user_id, event_type, created_at)
415 ) $eng";
416 } else {
417 $tables[] = "CREATE TABLE IF NOT EXISTS rate_events (
418 id INTEGER PRIMARY KEY AUTOINCREMENT,
419 user_id INTEGER NOT NULL,
420 event_type TEXT NOT NULL DEFAULT 'post',
421 created_at TEXT NOT NULL DEFAULT (datetime('now'))
422 )";
423 }
424
425
426 if ($mysql) {
427 $tables[] = "CREATE TABLE IF NOT EXISTS themes (
428 id INT AUTO_INCREMENT PRIMARY KEY,
429 name VARCHAR(100) NOT NULL,
430 slug VARCHAR(100) NOT NULL,
431 css LONGTEXT NOT NULL DEFAULT '',
432 is_active TINYINT NOT NULL DEFAULT 0,
433 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
434 UNIQUE KEY uq_slug (slug)
435 ) $eng";
436 } else {
437 $tables[] = "CREATE TABLE IF NOT EXISTS themes (
438 id INTEGER PRIMARY KEY AUTOINCREMENT,
439 name TEXT NOT NULL,
440 slug TEXT NOT NULL UNIQUE,
441 css TEXT NOT NULL DEFAULT '',
442 is_active INTEGER NOT NULL DEFAULT 0,
443 created_at TEXT NOT NULL DEFAULT (datetime('now'))
444 )";
445 }
446
447 $pdo = self::connect();
448 foreach ($tables as $sql) {
449 $pdo->exec($sql);
450 }
451
452
453 if (!$mysql) {
454 $pdo->exec("CREATE INDEX IF NOT EXISTS ix_topics_cat ON topics(category_id)");
455 $pdo->exec("CREATE INDEX IF NOT EXISTS ix_posts_topic ON posts(topic_id)");
456 $pdo->exec("CREATE INDEX IF NOT EXISTS ix_notif_user ON notifications(user_id)");
457 $pdo->exec("CREATE INDEX IF NOT EXISTS ix_rate_user ON rate_events(user_id, event_type, created_at)");
458 $pdo->exec("CREATE INDEX IF NOT EXISTS ix_friends_u ON friends(user_id)");
459 $pdo->exec("CREATE INDEX IF NOT EXISTS ix_msg_recv ON messages(receiver_id)");
460 }
461
462
463
464
465 if ($mysql) {
466
467 $dbName = defined('DB_NAME') ? DB_NAME : '';
468 $existingCols = array_column(self::rows(
469 "SELECT COLUMN_NAME FROM information_schema.COLUMNS
470 WHERE TABLE_SCHEMA = ? AND TABLE_NAME = 'users'",
471 [$dbName]
472 ), 'COLUMN_NAME');
473 $mysqlAdd = [
474 'karma' => "ALTER TABLE users ADD COLUMN karma INT NOT NULL DEFAULT 0",
475 'permissions' => "ALTER TABLE users ADD COLUMN permissions TEXT NOT NULL DEFAULT '{}'",
476 'friends_hidden' => "ALTER TABLE users ADD COLUMN friends_hidden TINYINT NOT NULL DEFAULT 0",
477 'suspended' => "ALTER TABLE users ADD COLUMN suspended TINYINT NOT NULL DEFAULT 0",
478 'silenced' => "ALTER TABLE users ADD COLUMN silenced TINYINT NOT NULL DEFAULT 0",
479 'topic_count' => "ALTER TABLE users ADD COLUMN topic_count INT NOT NULL DEFAULT 0",
480 ];
481 foreach ($mysqlAdd as $col => $sql) {
482 if (!in_array($col, $existingCols)) {
483 try { self::run($sql); } catch (\Throwable $e) { }
484 }
485 }
486 } else {
487
488 $cols = array_column(self::rows("PRAGMA table_info(users)"), 'name');
489 $sqliteAdd = [
490 'karma' => "ALTER TABLE users ADD COLUMN karma INTEGER NOT NULL DEFAULT 0",
491 'permissions' => "ALTER TABLE users ADD COLUMN permissions TEXT NOT NULL DEFAULT '{}'",
492 'friends_hidden' => "ALTER TABLE users ADD COLUMN friends_hidden INTEGER NOT NULL DEFAULT 0",
493 'suspended' => "ALTER TABLE users ADD COLUMN suspended INTEGER NOT NULL DEFAULT 0",
494 'silenced' => "ALTER TABLE users ADD COLUMN silenced INTEGER NOT NULL DEFAULT 0",
495 'topic_count' => "ALTER TABLE users ADD COLUMN topic_count INTEGER NOT NULL DEFAULT 0",
496 ];
497 foreach ($sqliteAdd as $col => $sql) {
498 if (!in_array($col, $cols)) {
499 try { self::run($sql); } catch (\Throwable $e) { }
500 }
501 }
502 }
503
504
505 if ($mysql) {
506 $dbName = defined('DB_NAME') ? DB_NAME : '';
507 $catCols = array_column(self::rows(
508 "SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=? AND TABLE_NAME='categories'",
509 [$dbName]
510 ), 'COLUMN_NAME');
511 foreach (['read_role'=>"VARCHAR(20) NOT NULL DEFAULT 'guest'",'post_role'=>"VARCHAR(20) NOT NULL DEFAULT 'member'",'reply_role'=>"VARCHAR(20) NOT NULL DEFAULT 'member'"] as $col=>$def) {
512 if (!in_array($col,$catCols)) { try{self::run("ALTER TABLE categories ADD COLUMN $col $def");}catch(\Throwable $e){} }
513 }
514 } else {
515 $catCols = array_column(self::rows("PRAGMA table_info(categories)"),'name');
516 foreach (['read_role'=>"TEXT NOT NULL DEFAULT 'guest'",'post_role'=>"TEXT NOT NULL DEFAULT 'member'",'reply_role'=>"TEXT NOT NULL DEFAULT 'member'"] as $col=>$def) {
517 if (!in_array($col,$catCols)) { try{self::run("ALTER TABLE categories ADD COLUMN $col $def");}catch(\Throwable $e){} }
518 }
519 }
520
521
522 if ($mysql) {
523 $dbName = defined('DB_NAME') ? DB_NAME : '';
524 $uCols = array_column(self::rows(
525 "SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=? AND TABLE_NAME='users'",
526 [$dbName]
527 ), 'COLUMN_NAME');
528 if (!in_array('location', $uCols)) {
529 try { self::run("ALTER TABLE users ADD COLUMN location VARCHAR(100) NOT NULL DEFAULT ''"); }
530 catch (\Throwable $e) {}
531 }
532 } else {
533 $uCols2 = array_column(self::rows("PRAGMA table_info(users)"), 'name');
534 if (!in_array('location', $uCols2)) {
535 try { self::run("ALTER TABLE users ADD COLUMN location TEXT NOT NULL DEFAULT ''"); }
536 catch (\Throwable $e) {}
537 }
538 }
539
540
541
542
543 if ($mysql) {
544 $dbName = defined('DB_NAME') ? DB_NAME : '';
545 $msgCols = array_column(self::rows(
546 "SELECT COLUMN_NAME FROM information_schema.COLUMNS
547 WHERE TABLE_SCHEMA = ? AND TABLE_NAME = 'messages'", [$dbName]
548 ), 'COLUMN_NAME');
549 if (!in_array('conversation_id', $msgCols)) {
550 try {
551 self::run("ALTER TABLE messages ADD COLUMN conversation_id VARCHAR(30) NOT NULL DEFAULT ''");
552 self::run("ALTER TABLE messages ADD INDEX ix_conv (conversation_id)");
553
554 self::run("UPDATE messages SET conversation_id = CONCAT(LEAST(sender_id,receiver_id),'-',GREATEST(sender_id,receiver_id))");
555 } catch (\Throwable $e) { }
556 }
557 } else {
558 $msgCols = array_column(self::rows("PRAGMA table_info(messages)"), 'name');
559 if (!in_array('conversation_id', $msgCols)) {
560 try {
561 self::run("ALTER TABLE messages ADD COLUMN conversation_id TEXT NOT NULL DEFAULT ''");
562 self::run("UPDATE messages SET conversation_id = MIN(sender_id,receiver_id)||'-'||MAX(sender_id,receiver_id)");
563 } catch (\Throwable $e) { }
564 }
565 }
566 }
567
568
569
570
571 public static function now(): string {
572 return self::isMysql() ? 'NOW()' : "datetime('now')";
573 }
574
575
576 public static function sinceSeconds(int $seconds): string {
577 if (self::isMysql()) {
578 return "DATE_SUB(NOW(), INTERVAL {$seconds} SECOND)";
579 }
580 return "datetime('now','-{$seconds} seconds')";
581 }
582}