8058
Ir
irvc / files
irvc / db.go
3.9 KB
1package main 2 3import ( 4 "database/sql" 5 "log" 6 "time" 7 8 _ "modernc.org/sqlite" 9) 10 11type User struct { 12 ID int64 `json:"id"` 13 Username string `json:"username"` 14 PassHash string `json:"-"` 15 CreatedAt int64 `json:"createdAt"` 16} 17 18type Channel struct { 19 ID int64 `json:"id"` 20 Type string `json:"type"` 21 Name string `json:"name"` 22 Position int `json:"position"` 23 Permanent bool `json:"permanent"` 24 Unread int `json:"unread"` 25} 26 27type Message struct { 28 ID int64 `json:"id"` 29 ChannelID int64 `json:"channelId"` 30 UserID int64 `json:"userId"` 31 Username string `json:"username"` 32 Body string `json:"body"` 33 CreatedAt int64 `json:"createdAt"` 34 EditedAt int64 `json:"editedAt,omitempty"` 35 Reactions map[string][]string `json:"reactions,omitempty"` 36 Preview *Preview `json:"preview,omitempty"` 37} 38 39func OpenDB(path string) *sql.DB { 40 dsn := path + "?_pragma=busy_timeout(5000)&_pragma=journal_mode(WAL)&_pragma=foreign_keys(1)" 41 db, err := sql.Open("sqlite", dsn) 42 if err != nil { 43 log.Fatalf("open db: %v", err) 44 } 45 if err := db.Ping(); err != nil { 46 log.Fatalf("ping db: %v", err) 47 } 48 migrate(db) 49 return db 50} 51 52const schema = ` 53CREATE TABLE IF NOT EXISTS users ( 54 id INTEGER PRIMARY KEY AUTOINCREMENT, 55 username TEXT NOT NULL UNIQUE, 56 pass_hash TEXT NOT NULL, 57 created_at INTEGER NOT NULL 58); 59CREATE TABLE IF NOT EXISTS invites ( 60 token TEXT PRIMARY KEY, 61 created_by INTEGER, 62 expires_at INTEGER NOT NULL, 63 used_by INTEGER, 64 used_at INTEGER 65); 66CREATE TABLE IF NOT EXISTS sessions ( 67 id TEXT PRIMARY KEY, 68 user_id INTEGER NOT NULL, 69 expires_at INTEGER NOT NULL 70); 71CREATE TABLE IF NOT EXISTS channels ( 72 id INTEGER PRIMARY KEY AUTOINCREMENT, 73 type TEXT NOT NULL, 74 name TEXT NOT NULL, 75 position INTEGER NOT NULL, 76 created_at INTEGER NOT NULL, 77 permanent INTEGER NOT NULL DEFAULT 0 78); 79CREATE TABLE IF NOT EXISTS messages ( 80 id INTEGER PRIMARY KEY AUTOINCREMENT, 81 channel_id INTEGER NOT NULL, 82 user_id INTEGER NOT NULL, 83 body TEXT NOT NULL, 84 created_at INTEGER NOT NULL, 85 edited_at INTEGER 86); 87CREATE TABLE IF NOT EXISTS reactions ( 88 message_id INTEGER NOT NULL REFERENCES messages(id) ON DELETE CASCADE, 89 user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE, 90 reaction TEXT NOT NULL, 91 PRIMARY KEY (message_id, user_id, reaction) 92); 93CREATE TABLE IF NOT EXISTS read_state ( 94 user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE, 95 channel_id INTEGER NOT NULL REFERENCES channels(id) ON DELETE CASCADE, 96 last_read_message_id INTEGER NOT NULL DEFAULT 0, 97 updated_at INTEGER NOT NULL, 98 PRIMARY KEY (user_id, channel_id) 99); 100CREATE INDEX IF NOT EXISTS idx_messages_channel ON messages(channel_id, id); 101CREATE UNIQUE INDEX IF NOT EXISTS idx_channels_dm ON channels(name) WHERE type = 'dm'; 102CREATE UNIQUE INDEX IF NOT EXISTS idx_channels_name ON channels(type, name) WHERE type != 'dm'; 103` 104 105func migrate(db *sql.DB) { 106 if _, err := db.Exec(schema); err != nil { 107 log.Fatalf("migrate: %v", err) 108 } 109 db.Exec(`ALTER TABLE messages ADD COLUMN edited_at INTEGER`) 110 db.Exec(`ALTER TABLE messages ADD COLUMN preview TEXT`) 111 db.Exec(`ALTER TABLE channels ADD COLUMN permanent INTEGER NOT NULL DEFAULT 0`) 112 db.Exec(`UPDATE channels SET permanent = 1 WHERE name = 'general' AND type IN ('text', 'voice')`) 113 var n int 114 if err := db.QueryRow(`SELECT COUNT(*) FROM channels WHERE type != 'dm'`).Scan(&n); err != nil { 115 log.Fatalf("count channels: %v", err) 116 } 117 if n > 0 { 118 return 119 } 120 now := time.Now().Unix() 121 seed := []struct { 122 kind, name string 123 pos int 124 }{ 125 {"text", "general", 0}, 126 {"voice", "general", 1}, 127 } 128 for _, c := range seed { 129 if _, err := db.Exec( 130 `INSERT INTO channels (type, name, position, created_at, permanent) VALUES (?, ?, ?, ?, 1)`, 131 c.kind, c.name, c.pos, now, 132 ); err != nil { 133 log.Fatalf("seed channels: %v", err) 134 } 135 } 136}