1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
|
package database
import (
"database/sql"
"log"
_ "github.com/mattn/go-sqlite3"
)
type Migrator func(*sql.DB) (*sql.DB, error)
func MigrateUsers(dbConn *sql.DB) (*sql.DB, error) {
log.Println("migrating users table")
_, err := dbConn.Exec(`CREATE TABLE IF NOT EXISTS users (
id TEXT PRIMARY KEY,
username TEXT NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
last_seen TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);`)
if err != nil {
return dbConn, err
}
_, err = dbConn.Exec(`CREATE UNIQUE INDEX IF NOT EXISTS idx_users_username ON users (username);`)
return dbConn, err
}
// MigrateMacs maps hardware addresses to users. One user can own many MACs —
// that's how a privacy-rotated device "logs back in" to an existing account.
func MigrateMacs(dbConn *sql.DB) (*sql.DB, error) {
log.Println("migrating macs table")
_, err := dbConn.Exec(`CREATE TABLE IF NOT EXISTS macs (
mac TEXT PRIMARY KEY,
user_id TEXT NOT NULL,
first_seen TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
last_seen TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
);`)
return dbConn, err
}
func MigrateSessions(dbConn *sql.DB) (*sql.DB, error) {
log.Println("migrating sessions table")
_, err := dbConn.Exec(`CREATE TABLE IF NOT EXISTS sessions (
id TEXT PRIMARY KEY,
user_id TEXT NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
expire_at TIMESTAMP NOT NULL,
FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
);`)
return dbConn, err
}
func MigrateChat(dbConn *sql.DB) (*sql.DB, error) {
log.Println("migrating chat table")
_, err := dbConn.Exec(`CREATE TABLE IF NOT EXISTS chat (
id TEXT PRIMARY KEY,
user_id TEXT NOT NULL,
body TEXT NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
);`)
if err != nil {
return dbConn, err
}
_, err = dbConn.Exec(`CREATE INDEX IF NOT EXISTS idx_chat_created_at ON chat (created_at);`)
return dbConn, err
}
func MigrateConnections(dbConn *sql.DB) (*sql.DB, error) {
log.Println("migrating connections table")
_, err := dbConn.Exec(`CREATE TABLE IF NOT EXISTS connections (
ip TEXT PRIMARY KEY,
mac TEXT NOT NULL,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);`)
return dbConn, err
}
func MigrateMinecraft(dbConn *sql.DB) (*sql.DB, error) {
log.Println("migrating minecraft_status table")
_, err := dbConn.Exec(`CREATE TABLE IF NOT EXISTS minecraft_status (
id INTEGER PRIMARY KEY CHECK (id = 1),
status TEXT NOT NULL,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);`)
return dbConn, err
}
func MigrateCanvas(dbConn *sql.DB) (*sql.DB, error) {
log.Println("migrating canvas tables")
_, err := dbConn.Exec(`CREATE TABLE IF NOT EXISTS canvas_pixels (
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id TEXT,
x INTEGER NOT NULL,
y INTEGER NOT NULL,
color INTEGER NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE SET NULL
);`)
if err != nil {
return dbConn, err
}
_, err = dbConn.Exec(`CREATE TABLE IF NOT EXISTS canvas_snapshots (
id INTEGER PRIMARY KEY AUTOINCREMENT,
data BLOB NOT NULL,
through_id INTEGER NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);`)
return dbConn, err
}
func Migrate(dbConn *sql.DB) (*sql.DB, error) {
log.Println("migrating database")
migrations := []Migrator{
MigrateUsers,
MigrateMacs,
MigrateSessions,
MigrateChat,
MigrateConnections,
MigrateMinecraft,
MigrateCanvas,
}
for _, migration := range migrations {
var err error
if dbConn, err = migration(dbConn); err != nil {
return dbConn, err
}
}
return dbConn, nil
}
|
