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
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
|
package database
import (
"database/sql"
"log"
"strings"
_ "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,
primary_mac TEXT,
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
}
// MigrateUserPrimaryMac adds primary_mac to pre-existing users tables. It's
// idempotent: SQLite has no "ADD COLUMN IF NOT EXISTS", so the duplicate-column
// error (column already present, e.g. on a fresh DB) is swallowed.
func MigrateUserPrimaryMac(dbConn *sql.DB) (*sql.DB, error) {
log.Println("migrating users.primary_mac column")
_, err := dbConn.Exec(`ALTER TABLE users ADD COLUMN primary_mac TEXT;`)
if err != nil && !strings.Contains(err.Error(), "duplicate column name") {
return dbConn, err
}
return dbConn, nil
}
// MigrateDNSRecords holds the user-editable DNS records served for the LAN zone.
func MigrateDNSRecords(dbConn *sql.DB) (*sql.DB, error) {
log.Println("migrating dns_records table")
_, err := dbConn.Exec(`CREATE TABLE IF NOT EXISTS dns_records (
id TEXT PRIMARY KEY,
user_id TEXT NOT NULL,
name TEXT NOT NULL,
type TEXT NOT NULL,
content TEXT NOT NULL,
ttl INTEGER 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 UNIQUE INDEX IF NOT EXISTS idx_dns_records_name_content_type ON dns_records (name, type, content);`)
if err != nil {
return dbConn, err
}
_, err = dbConn.Exec(`CREATE INDEX IF NOT EXISTS idx_dns_records_name_type ON dns_records (name, type);`)
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,
MigrateUserPrimaryMac,
MigrateMacs,
MigrateSessions,
MigrateChat,
MigrateConnections,
MigrateMinecraft,
MigrateCanvas,
MigrateDNSRecords,
}
for _, migration := range migrations {
var err error
if dbConn, err = migration(dbConn); err != nil {
return dbConn, err
}
}
return dbConn, nil
}
|
