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
|
package database
import (
"log"
"database/sql"
_ "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,
mail TEXT NOT NULL,
username TEXT NOT NULL,
display_name TEXT NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);`)
if err != nil {
return dbConn, err
}
log.Println("creating unique index on users table")
_, err = dbConn.Exec(`CREATE UNIQUE INDEX IF NOT EXISTS idx_users_username ON users (username);`)
if err != nil {
return dbConn, err
}
return dbConn, nil
}
func MigrateApiKeys(dbConn *sql.DB) (*sql.DB, error) {
log.Println("migrating api_keys table")
_, err := dbConn.Exec(`CREATE TABLE IF NOT EXISTS api_keys (
key TEXT PRIMARY KEY,
user_id 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
}
return dbConn, nil
}
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 INTEGER NOT NULL,
name TEXT NOT NULL,
type TEXT NOT NULL,
content TEXT NOT NULL,
ttl INTEGER NOT NULL,
internal BOOLEAN 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
}
return dbConn, nil
}
func MigrateDomainOwners(dbConn *sql.DB) (*sql.DB, error) {
log.Println("migrating domain_owners table")
_, err := dbConn.Exec(`CREATE TABLE IF NOT EXISTS domain_owners (
user_id INTEGER NOT NULL,
domain 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 UNIQUE INDEX IF NOT EXISTS idx_domain_owners_domain ON domain_owners (domain);`)
if err != nil {
return dbConn, err
}
return dbConn, nil
}
func MigrateUserSessions(dbConn *sql.DB) (*sql.DB, error) {
log.Println("migrating user_sessions table")
_, err := dbConn.Exec(`CREATE TABLE IF NOT EXISTS user_sessions (
id TEXT PRIMARY KEY,
user_id TEXT NOT NULL,
expire_at TIMESTAMP NOT NULL,
FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
);`)
if err != nil {
return dbConn, err
}
return dbConn, nil
}
func MigrateGuestBook(dbConn *sql.DB) (*sql.DB, error) {
log.Println("migrating guest_book table")
_, err := dbConn.Exec(`CREATE TABLE IF NOT EXISTS guest_book (
id TEXT PRIMARY KEY,
name TEXT NOT NULL,
message TEXT NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);`)
if err != nil {
return dbConn, err
}
_, err = dbConn.Exec(`CREATE INDEX IF NOT EXISTS idx_guest_book_created_at ON guest_book (created_at);`)
return dbConn, nil
}
func MigrateProfiles(dbConn *sql.DB) (*sql.DB, error) {
log.Println("migrating profiles columns")
userColumns := map[string]bool{}
row, err := dbConn.Query(`PRAGMA table_info(users);`)
if err != nil {
return dbConn, err
}
defer row.Close()
for row.Next() {
var columnName string
row.Scan(nil, &columnName, nil, nil, nil, nil)
userColumns[columnName] = true
}
columns := map[string]string{}
columns["pronouns"] = "unspecified"
columns["bio"] = "a computer hater"
columns["location"] = "earth"
columns["website"] = "https://hatecomputers.club"
columns["avatar"] = "/static/img/default-avatar.png"
for column, defaultValue := range columns {
if userColumns[column] {
continue
}
log.Println("migrating column", column)
_, err = dbConn.Exec(`ALTER TABLE users ADD COLUMN ` + column + ` TEXT NOT NULL DEFAULT '` + defaultValue + `';`)
}
return dbConn, nil
}
func Migrate(dbConn *sql.DB) (*sql.DB, error) {
log.Println("migrating database")
migrations := []Migrator{
MigrateUsers,
MigrateUserSessions,
MigrateApiKeys,
MigrateDomainOwners,
MigrateDNSRecords,
MigrateGuestBook,
MigrateProfiles,
}
for _, migration := range migrations {
dbConn, err := migration(dbConn)
if err != nil {
return dbConn, err
}
}
return dbConn, nil
}
|
