]> code.octet-stream.net Git - broadcaster/blob - server/database.go
06e5968ac1090b2e8cbaad4857338b8004ac0b0f
[broadcaster] / server / database.go
1 package main
2
3 import (
4 "database/sql"
5 "errors"
6 "log"
7 _ "modernc.org/sqlite"
8 "time"
9 )
10
11 type Database struct {
12 sqldb *sql.DB
13 }
14
15 var db Database
16
17 func InitDatabase() {
18 sqldb, err := sql.Open("sqlite", config.SqliteDB)
19 if err != nil {
20 log.Fatal(err)
21 }
22 db.sqldb = sqldb
23
24 _, err = db.sqldb.Exec("PRAGMA journal_mode = WAL")
25 if err != nil {
26 log.Fatal(err)
27 }
28
29 _, err = db.sqldb.Exec("PRAGMA foreign_keys = ON")
30 if err != nil {
31 log.Fatal(err)
32 }
33
34 _, err = db.sqldb.Exec("PRAGMA busy_timeout = 5000")
35 if err != nil {
36 log.Fatal(err)
37 }
38
39 sqlStmt := `
40 CREATE TABLE IF NOT EXISTS sessions (id INTEGER PRIMARY KEY AUTOINCREMENT, token TEXT, username TEXT, created TIMESTAMP, expiry TIMESTAMP);
41 CREATE TABLE IF NOT EXISTS playlists (id INTEGER PRIMARY KEY AUTOINCREMENT, enabled INTEGER, name TEXT, start_time TEXT);
42 CREATE TABLE IF NOT EXISTS playlist_entries (id INTEGER PRIMARY KEY AUTOINCREMENT, playlist_id INTEGER, position INTEGER, filename TEXT, delay_seconds INTEGER, is_relative INTEGER, CONSTRAINT fk_playlists FOREIGN KEY (playlist_id) REFERENCES playlists(id) ON DELETE CASCADE);
43 CREATE TABLE IF NOT EXISTS radios (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, token TEXT);
44 CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY AUTOINCREMENT, username TEXT UNIQUE, password_hash TEXT, is_admin INTEGER);
45 `
46 _, err = db.sqldb.Exec(sqlStmt)
47 if err != nil {
48 log.Printf("%q: %s\n", err, sqlStmt)
49 return
50 }
51 }
52
53 func (d *Database) CloseDatabase() {
54 d.sqldb.Close()
55 }
56
57 func (d *Database) InsertSession(user string, token string, expiry time.Time) {
58 _, err := d.sqldb.Exec("INSERT INTO sessions (token, username, created, expiry) values (?, ?, CURRENT_TIMESTAMP, ?)", token, user, expiry)
59 if err != nil {
60 log.Fatal(err)
61 }
62 }
63
64 func (d *Database) GetUserNameForSession(token string) (string, error) {
65 var username string
66 err := d.sqldb.QueryRow("SELECT username FROM sessions WHERE token = ? AND expiry > CURRENT_TIMESTAMP", token).Scan(&username)
67 if err != nil {
68 return "", errors.New("no matching token")
69 }
70 return username, nil
71 }
72
73 func (d *Database) GetUser(username string) (User, error) {
74 var user User
75 err := d.sqldb.QueryRow("SELECT id, username, password_hash, is_admin FROM users WHERE username = ?", username).Scan(&user.Id, &user.Username, &user.PasswordHash, &user.IsAdmin)
76 if err != nil {
77 return User{}, errors.New("no user with that username")
78 }
79 return user, nil
80 }
81
82 func (d *Database) GetUserById(id int) (User, error) {
83 var user User
84 err := d.sqldb.QueryRow("SELECT id, username, password_hash, is_admin FROM users WHERE id = ?", id).Scan(&user.Id, &user.Username, &user.PasswordHash, &user.IsAdmin)
85 if err != nil {
86 return User{}, errors.New("no user with that id")
87 }
88 return user, nil
89 }
90
91 func (d *Database) GetUsers() []User {
92 ret := make([]User, 0)
93 rows, err := d.sqldb.Query("SELECT id, username, password_hash, is_admin FROM users ORDER BY username ASC")
94 if err != nil {
95 return ret
96 }
97 defer rows.Close()
98 for rows.Next() {
99 var u User
100 if err := rows.Scan(&u.Id, &u.Username, &u.PasswordHash, &u.IsAdmin); err != nil {
101 return ret
102 }
103 ret = append(ret, u)
104 }
105 return ret
106 }
107
108 func (d *Database) SetUserPassword(username string, passwordHash string) {
109 d.sqldb.Exec("UPDATE users SET password_hash = ? WHERE username = ?", passwordHash, username)
110 }
111
112 func (d *Database) ClearOtherSessions(username string, token string) {
113 d.sqldb.Exec("DELETE FROM sessions WHERE username = ? AND token != ?", username, token)
114 }
115
116 func (d *Database) SetUserIsAdmin(username string, isAdmin bool) {
117 d.sqldb.Exec("UPDATE users SET is_admin = ? WHERE username = ?", isAdmin, username)
118 }
119
120 func (d *Database) CreateUser(user User) error {
121 _, err := d.sqldb.Exec("INSERT INTO users (username, password_hash, is_admin) values (?, ?, ?)", user.Username, user.PasswordHash, user.IsAdmin)
122 return err
123 }
124
125 func (d *Database) DeleteUser(username string) error {
126 _, err := d.sqldb.Exec("DELETE FROM users WHERE username = ?", username)
127 return err
128 }
129
130 func (d *Database) CreatePlaylist(playlist Playlist) int {
131 var id int
132 tx, _ := d.sqldb.Begin()
133 _, err := tx.Exec("INSERT INTO playlists (enabled, name, start_time) values (?, ?, ?)", playlist.Enabled, playlist.Name, playlist.StartTime)
134 if err != nil {
135 log.Fatal(err)
136 }
137 err = tx.QueryRow("SELECT last_insert_rowid()").Scan(&id)
138 if err != nil {
139 log.Fatal(err)
140 }
141 err = tx.Commit()
142 if err != nil {
143 log.Fatal(err)
144 }
145 return id
146 }
147
148 func (d *Database) DeletePlaylist(playlistId int) {
149 d.sqldb.Exec("DELETE FROM playlists WHERE id = ?", playlistId)
150 }
151
152 func (d *Database) GetPlaylists() []Playlist {
153 ret := make([]Playlist, 0)
154 rows, err := d.sqldb.Query("SELECT id, enabled, name, start_time FROM playlists ORDER BY id ASC")
155 if err != nil {
156 return ret
157 }
158 defer rows.Close()
159 for rows.Next() {
160 var p Playlist
161 if err := rows.Scan(&p.Id, &p.Enabled, &p.Name, &p.StartTime); err != nil {
162 return ret
163 }
164 ret = append(ret, p)
165 }
166 return ret
167 }
168
169 func (d *Database) GetPlaylist(playlistId int) (Playlist, error) {
170 var p Playlist
171 err := d.sqldb.QueryRow("SELECT id, enabled, name, start_time FROM playlists WHERE id = ?", playlistId).Scan(&p.Id, &p.Enabled, &p.Name, &p.StartTime)
172 if err != nil {
173 return p, err
174 }
175 return p, nil
176 }
177
178 func (d *Database) UpdatePlaylist(playlist Playlist) {
179 d.sqldb.Exec("UPDATE playlists SET enabled = ?, name = ?, start_time = ? WHERE id = ?", playlist.Enabled, playlist.Name, playlist.StartTime, playlist.Id)
180 }
181
182 func (d *Database) SetEntriesForPlaylist(entries []PlaylistEntry, playlistId int) {
183 tx, _ := d.sqldb.Begin()
184 _, err := tx.Exec("DELETE FROM playlist_entries WHERE playlist_id = ?", playlistId)
185 for _, e := range entries {
186 _, err = tx.Exec("INSERT INTO playlist_entries (playlist_id, position, filename, delay_seconds, is_relative) values (?, ?, ?, ?, ?)", playlistId, e.Position, e.Filename, e.DelaySeconds, e.IsRelative)
187 if err != nil {
188 log.Fatal(err)
189 }
190 }
191 tx.Commit() // ignore errors
192 }
193
194 func (d *Database) GetEntriesForPlaylist(playlistId int) []PlaylistEntry {
195 ret := make([]PlaylistEntry, 0)
196 rows, err := d.sqldb.Query("SELECT id, position, filename, delay_seconds, is_relative FROM playlist_entries WHERE playlist_id = ? ORDER by position ASC", playlistId)
197 if err != nil {
198 return ret
199 }
200 defer rows.Close()
201 for rows.Next() {
202 var entry PlaylistEntry
203 if err := rows.Scan(&entry.Id, &entry.Position, &entry.Filename, &entry.DelaySeconds, &entry.IsRelative); err != nil {
204 return ret
205 }
206 ret = append(ret, entry)
207 }
208 return ret
209 }
210
211 func (d *Database) GetRadio(radioId int) (Radio, error) {
212 var r Radio
213 err := d.sqldb.QueryRow("SELECT id, name, token FROM radios WHERE id = ?", radioId).Scan(&r.Id, &r.Name, &r.Token)
214 if err != nil {
215 return r, err
216 }
217 return r, nil
218 }
219
220 func (d *Database) GetRadioByToken(token string) (Radio, error) {
221 var r Radio
222 err := d.sqldb.QueryRow("SELECT id, name, token FROM radios WHERE token = ?", token).Scan(&r.Id, &r.Name, &r.Token)
223 if err != nil {
224 return r, err
225 }
226 return r, nil
227 }
228
229 func (d *Database) GetRadios() []Radio {
230 ret := make([]Radio, 0)
231 rows, err := d.sqldb.Query("SELECT id, name, token FROM radios ORDER BY id ASC")
232 if err != nil {
233 return ret
234 }
235 defer rows.Close()
236 for rows.Next() {
237 var r Radio
238 if err := rows.Scan(&r.Id, &r.Name, &r.Token); err != nil {
239 return ret
240 }
241 ret = append(ret, r)
242 }
243 return ret
244 }
245
246 func (d *Database) DeleteRadio(radioId int) {
247 d.sqldb.Exec("DELETE FROM radios WHERE id = ?", radioId)
248 }
249
250 func (d *Database) CreateRadio(radio Radio) {
251 d.sqldb.Exec("INSERT INTO radios (name, token) values (?, ?)", radio.Name, radio.Token)
252 }
253
254 func (d *Database) UpdateRadio(radio Radio) {
255 d.sqldb.Exec("UPDATE radios SET name = ?, token = ? WHERE id = ?", radio.Name, radio.Token, radio.Id)
256 }