135 lines
3.3 KiB
Go
135 lines
3.3 KiB
Go
package lib
|
|
|
|
import (
|
|
"database/sql"
|
|
"log"
|
|
"os"
|
|
"path/filepath"
|
|
|
|
_ "turso.tech/database/tursogo"
|
|
)
|
|
|
|
var DB *sql.DB
|
|
|
|
// init sqlite db — creates app.db at project root (run from root) or ROOT_DIR if set
|
|
func InitDB() {
|
|
var err error
|
|
|
|
dbPath := "app.db"
|
|
if rootDir := os.Getenv("ROOT_DIR"); rootDir != "" {
|
|
dbPath = filepath.Join(rootDir, "app.db")
|
|
}
|
|
|
|
DB, err = sql.Open("turso", dbPath)
|
|
if err != nil {
|
|
log.Fatal(err)
|
|
}
|
|
|
|
if _, err = DB.Exec("PRAGMA foreign_keys = ON;"); err != nil {
|
|
log.Fatal(err)
|
|
}
|
|
|
|
// make users table
|
|
userTable := `
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
username TEXT UNIQUE NOT NULL,
|
|
password_hash TEXT NOT NULL
|
|
);`
|
|
_, err = DB.Exec(userTable)
|
|
if err != nil {
|
|
log.Fatal(err)
|
|
}
|
|
|
|
// make sessions table
|
|
sessionTable := `
|
|
CREATE TABLE IF NOT EXISTS sessions (
|
|
id TEXT PRIMARY KEY,
|
|
user_id INTEGER NOT NULL,
|
|
expires_at DATETIME NOT NULL,
|
|
FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
|
|
);`
|
|
_, err = DB.Exec(sessionTable)
|
|
if err != nil {
|
|
log.Fatal(err)
|
|
}
|
|
|
|
// make rooms table
|
|
roomTable := `
|
|
CREATE TABLE IF NOT EXISTS rooms (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
name TEXT NOT NULL,
|
|
code TEXT UNIQUE NOT NULL,
|
|
scale TEXT NOT NULL DEFAULT 'fibonacci',
|
|
owner_id INTEGER NOT NULL,
|
|
created_at DATETIME NOT NULL,
|
|
active_story_id INTEGER,
|
|
FOREIGN KEY(owner_id) REFERENCES users(id) ON DELETE CASCADE
|
|
);`
|
|
_, err = DB.Exec(roomTable)
|
|
if err != nil {
|
|
log.Fatal(err)
|
|
}
|
|
|
|
// make room_members table
|
|
memberTable := `
|
|
CREATE TABLE IF NOT EXISTS room_members (
|
|
room_id INTEGER NOT NULL,
|
|
user_id INTEGER NOT NULL,
|
|
joined_at DATETIME NOT NULL,
|
|
PRIMARY KEY (room_id, user_id),
|
|
FOREIGN KEY(room_id) REFERENCES rooms(id) ON DELETE CASCADE,
|
|
FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
|
|
);`
|
|
_, err = DB.Exec(memberTable)
|
|
if err != nil {
|
|
log.Fatal(err)
|
|
}
|
|
|
|
// make stories table
|
|
storyTable := `
|
|
CREATE TABLE IF NOT EXISTS stories (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
room_id INTEGER NOT NULL,
|
|
title TEXT NOT NULL,
|
|
points INTEGER,
|
|
voted INTEGER NOT NULL DEFAULT 0,
|
|
FOREIGN KEY(room_id) REFERENCES rooms(id) ON DELETE CASCADE
|
|
);`
|
|
_, err = DB.Exec(storyTable)
|
|
if err != nil {
|
|
log.Fatal(err)
|
|
}
|
|
|
|
// make votes table
|
|
voteTable := `
|
|
CREATE TABLE IF NOT EXISTS votes (
|
|
story_id INTEGER NOT NULL,
|
|
user_id INTEGER NOT NULL,
|
|
value TEXT NOT NULL,
|
|
PRIMARY KEY (story_id, user_id),
|
|
FOREIGN KEY(story_id) REFERENCES stories(id) ON DELETE CASCADE,
|
|
FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
|
|
);`
|
|
_, err = DB.Exec(voteTable)
|
|
if err != nil {
|
|
log.Fatal(err)
|
|
}
|
|
|
|
indexes := []string{
|
|
"CREATE INDEX IF NOT EXISTS idx_sessions_user_expires ON sessions(user_id, expires_at);",
|
|
"CREATE INDEX IF NOT EXISTS idx_sessions_expires ON sessions(expires_at);",
|
|
"CREATE INDEX IF NOT EXISTS idx_rooms_owner ON rooms(owner_id);",
|
|
"CREATE INDEX IF NOT EXISTS idx_room_members_room ON room_members(room_id);",
|
|
"CREATE INDEX IF NOT EXISTS idx_room_members_user ON room_members(user_id);",
|
|
"CREATE INDEX IF NOT EXISTS idx_stories_room ON stories(room_id);",
|
|
"CREATE INDEX IF NOT EXISTS idx_votes_story ON votes(story_id);",
|
|
"CREATE INDEX IF NOT EXISTS idx_votes_user ON votes(user_id);",
|
|
}
|
|
for _, stmt := range indexes {
|
|
if _, err = DB.Exec(stmt); err != nil {
|
|
log.Fatal(err)
|
|
}
|
|
}
|
|
}
|