xonecas.io
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}