115 lines
3.8 KiB
Go
115 lines
3.8 KiB
Go
package main
|
||
|
||
import (
|
||
"database/sql"
|
||
"strings"
|
||
|
||
_ "modernc.org/sqlite"
|
||
)
|
||
|
||
var db *sql.DB
|
||
|
||
const schema = `
|
||
CREATE TABLE IF NOT EXISTS user (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
uid INTEGER UNIQUE,
|
||
username TEXT UNIQUE,
|
||
password BLOB,
|
||
created_at INTEGER,
|
||
last_login INTEGER,
|
||
avatar TEXT NOT NULL DEFAULT ''
|
||
);
|
||
|
||
CREATE TABLE IF NOT EXISTS session (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
uid INTEGER,
|
||
value TEXT UNIQUE,
|
||
created_at INTEGER,
|
||
expires INTEGER,
|
||
description TEXT
|
||
);
|
||
|
||
CREATE TABLE IF NOT EXISTS entry (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
pid INTEGER UNIQUE NOT NULL,
|
||
uid INTEGER NOT NULL,
|
||
created_at INTEGER NOT NULL,
|
||
content TEXT NOT NULL DEFAULT '',
|
||
filepath TEXT NOT NULL DEFAULT '',
|
||
reply_to INTEGER NOT NULL DEFAULT 0,
|
||
reply_count INTEGER NOT NULL DEFAULT 0,
|
||
last_activity INTEGER NOT NULL DEFAULT 0,
|
||
deleted INTEGER NOT NULL DEFAULT 0,
|
||
bump_count INTEGER NOT NULL DEFAULT 0,
|
||
last_bump INTEGER NOT NULL DEFAULT 0
|
||
);
|
||
|
||
CREATE TABLE IF NOT EXISTS vote (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
uid INTEGER,
|
||
pid INTEGER,
|
||
mode TEXT,
|
||
UNIQUE(uid, pid)
|
||
);
|
||
|
||
-- Seitenaufrufe, aggregiert pro (Tag, anonymisiertes Netz, Pfad). Es wird nie
|
||
-- eine volle IP gespeichert: anon_ip ist auf /16 (IPv4) bzw. /32 (IPv6) gekürzt
|
||
-- (siehe maskIP). asn ist das autonome System (Netzbetreiber), aufgelöst aus dem
|
||
-- bereits anonymisierten Netz (siehe lookupASN) -- nicht im PK, da pro Netz
|
||
-- deterministisch. Aggregation statt Eventlog -> die Tabelle wächst nur mit
|
||
-- Tag × Netz × Pfad, nicht pro Request.
|
||
CREATE TABLE IF NOT EXISTS impression (
|
||
day TEXT NOT NULL,
|
||
anon_ip TEXT NOT NULL,
|
||
path TEXT NOT NULL DEFAULT '',
|
||
asn TEXT NOT NULL DEFAULT '',
|
||
hits INTEGER NOT NULL DEFAULT 0,
|
||
PRIMARY KEY (day, anon_ip, path)
|
||
);
|
||
|
||
-- Indizes für die häufigen Zugriffspfade; ohne sie werden Feed (ORDER BY
|
||
-- last_activity), Thread (WHERE reply_to) und Vote-Zähler (WHERE pid) mit
|
||
-- wachsender Tabelle zu Full-Table-Scans. username/session.value/vote(uid,pid)
|
||
-- sind über UNIQUE bereits indiziert.
|
||
CREATE INDEX IF NOT EXISTS idx_entry_last_activity ON entry(last_activity);
|
||
CREATE INDEX IF NOT EXISTS idx_entry_reply_to ON entry(reply_to);
|
||
CREATE INDEX IF NOT EXISTS idx_entry_uid ON entry(uid);
|
||
CREATE INDEX IF NOT EXISTS idx_vote_pid ON vote(pid);
|
||
CREATE INDEX IF NOT EXISTS idx_session_uid ON session(uid);
|
||
`
|
||
|
||
func initDB(dsn string) error {
|
||
var err error
|
||
// SQLite hat genau einen Schreiber: busy_timeout wartet bei Sperren, statt
|
||
// sofort mit SQLITE_BUSY zu scheitern; WAL erlaubt parallele Leser.
|
||
db, err = sql.Open("sqlite", dsn+"?_pragma=busy_timeout(5000)&_pragma=journal_mode(WAL)&_pragma=foreign_keys(on)")
|
||
if err != nil {
|
||
return err
|
||
}
|
||
if err = db.Ping(); err != nil {
|
||
return err
|
||
}
|
||
if _, err = db.Exec(schema); err != nil {
|
||
return err
|
||
}
|
||
return migrate()
|
||
}
|
||
|
||
// migrate ergänzt Spalten, die nach dem ersten Deploy dazukamen. CREATE TABLE IF
|
||
// NOT EXISTS fasst eine bestehende Tabelle nicht an -> neue Spalten brauchen ein
|
||
// ALTER. SQLite kann ADD COLUMN nicht "IF NOT EXISTS", deshalb schlucken wir den
|
||
// "duplicate column"-Fehler: bei einer frischen DB sind die Spalten schon aus
|
||
// dem Schema da, bei einer alten werden sie hier nachgezogen.
|
||
func migrate() error {
|
||
alters := []string{
|
||
`ALTER TABLE entry ADD COLUMN bump_count INTEGER NOT NULL DEFAULT 0`,
|
||
`ALTER TABLE entry ADD COLUMN last_bump INTEGER NOT NULL DEFAULT 0`,
|
||
}
|
||
for _, stmt := range alters {
|
||
if _, err := db.Exec(stmt); err != nil && !strings.Contains(err.Error(), "duplicate column") {
|
||
return err
|
||
}
|
||
}
|
||
return nil
|
||
}
|