code.octet-stream.net Git - broadcaster/blob - 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         DELETE FROM sessions WHERE expiry < CURRENT_TIMESTAMP;
  47         `
  48         _, err = db.sqldb.Exec(sqlStmt)
  49         if err != nil {
  50                 log.Printf("%q: %s\n", err, sqlStmt)
  51                 return
  52         }
  53 }
  54
  55 func (d *Database) CloseDatabase() {
  56         d.sqldb.Close()
  57 }
  58
  59 func (d *Database) InsertSession(user string, token string, expiry time.Time) {
  60         _, err := d.sqldb.Exec("INSERT INTO sessions (token, username, created, expiry) values (?, ?, CURRENT_TIMESTAMP, ?)", token, user, expiry)
  61         if err != nil {
  62                 log.Fatal(err)
  63         }
  64 }
  65
  66 func (d *Database) GetUserNameForSession(token string) (string, error) {
  67         var username string
  68         err := d.sqldb.QueryRow("SELECT username FROM sessions WHERE token = ? AND expiry > CURRENT_TIMESTAMP", token).Scan(&username)
  69         if err != nil {
  70                 return "", errors.New("no matching token")
  71         }
  72         return username, nil
  73 }
  74
  75 func (d *Database) GetUser(username string) (User, error) {
  76         var user User
  77         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)
  78         if err != nil {
  79                 return User{}, errors.New("no user with that username")
  80         }
  81         return user, nil
  82 }
  83
  84 func (d *Database) GetUserById(id int) (User, error) {
  85         var user User
  86         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)
  87         if err != nil {
  88                 return User{}, errors.New("no user with that id")
  89         }
  90         return user, nil
  91 }
  92
  93 func (d *Database) GetUsers() []User {
  94         ret := make([]User, 0)
  95         rows, err := d.sqldb.Query("SELECT id, username, password_hash, is_admin FROM users ORDER BY username ASC")
  96         if err != nil {
  97                 return ret
  98         }
  99         defer rows.Close()
 100         for rows.Next() {
 101                 var u User
 102                 if err := rows.Scan(&u.Id, &u.Username, &u.PasswordHash, &u.IsAdmin); err != nil {
 103                         return ret
 104                 }
 105                 ret = append(ret, u)
 106         }
 107         return ret
 108 }
 109
 110 func (d *Database) SetUserPassword(username string, passwordHash string) {
 111         d.sqldb.Exec("UPDATE users SET password_hash = ? WHERE username = ?", passwordHash, username)
 112 }
 113
 114 func (d *Database) ClearOtherSessions(username string, token string) {
 115         d.sqldb.Exec("DELETE FROM sessions WHERE username = ? AND token != ?", username, token)
 116 }
 117
 118 func (d *Database) ClearSession(username string, token string) {
 119         d.sqldb.Exec("DELETE FROM sessions WHERE username = ? AND token = ?", username, token)
 120 }
 121
 122 func (d *Database) SetUserIsAdmin(username string, isAdmin bool) {
 123         d.sqldb.Exec("UPDATE users SET is_admin = ? WHERE username = ?", isAdmin, username)
 124 }
 125
 126 func (d *Database) CreateUser(user User) error {
 127         _, err := d.sqldb.Exec("INSERT INTO users (username, password_hash, is_admin) values (?, ?, ?)", user.Username, user.PasswordHash, user.IsAdmin)
 128         return err
 129 }
 130
 131 func (d *Database) DeleteUser(username string) error {
 132         _, err := d.sqldb.Exec("DELETE FROM users WHERE username = ?", username)
 133         return err
 134 }
 135
 136 func (d *Database) CreatePlaylist(playlist Playlist) int {
 137         var id int
 138         tx, _ := d.sqldb.Begin()
 139         _, err := tx.Exec("INSERT INTO playlists (enabled, name, start_time) values (?, ?, ?)", playlist.Enabled, playlist.Name, playlist.StartTime)
 140         if err != nil {
 141                 log.Fatal(err)
 142         }
 143         err = tx.QueryRow("SELECT last_insert_rowid()").Scan(&id)
 144         if err != nil {
 145                 log.Fatal(err)
 146         }
 147         err = tx.Commit()
 148         if err != nil {
 149                 log.Fatal(err)
 150         }
 151         return id
 152 }
 153
 154 func (d *Database) DeletePlaylist(playlistId int) {
 155         d.sqldb.Exec("DELETE FROM playlists WHERE id = ?", playlistId)
 156 }
 157
 158 func (d *Database) GetPlaylists() []Playlist {
 159         ret := make([]Playlist, 0)
 160         rows, err := d.sqldb.Query("SELECT id, enabled, name, start_time FROM playlists ORDER BY start_time DESC")
 161         if err != nil {
 162                 return ret
 163         }
 164         defer rows.Close()
 165         for rows.Next() {
 166                 var p Playlist
 167                 if err := rows.Scan(&p.Id, &p.Enabled, &p.Name, &p.StartTime); err != nil {
 168                         return ret
 169                 }
 170                 ret = append(ret, p)
 171         }
 172         return ret
 173 }
 174
 175 func (d *Database) GetPlaylist(playlistId int) (Playlist, error) {
 176         var p Playlist
 177         err := d.sqldb.QueryRow("SELECT id, enabled, name, start_time FROM playlists WHERE id = ?", playlistId).Scan(&p.Id, &p.Enabled, &p.Name, &p.StartTime)
 178         if err != nil {
 179                 return p, err
 180         }
 181         return p, nil
 182 }
 183
 184 func (d *Database) UpdatePlaylist(playlist Playlist) {
 185         d.sqldb.Exec("UPDATE playlists SET enabled = ?, name = ?, start_time = ? WHERE id = ?", playlist.Enabled, playlist.Name, playlist.StartTime, playlist.Id)
 186 }
 187
 188 func (d *Database) SetEntriesForPlaylist(entries []PlaylistEntry, playlistId int) {
 189         tx, _ := d.sqldb.Begin()
 190         _, err := tx.Exec("DELETE FROM playlist_entries WHERE playlist_id = ?", playlistId)
 191         for _, e := range entries {
 192                 _, 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)
 193                 if err != nil {
 194                         log.Fatal(err)
 195                 }
 196         }
 197         tx.Commit() // ignore errors
 198 }
 199
 200 func (d *Database) GetEntriesForPlaylist(playlistId int) []PlaylistEntry {
 201         ret := make([]PlaylistEntry, 0)
 202         rows, err := d.sqldb.Query("SELECT id, position, filename, delay_seconds, is_relative FROM playlist_entries WHERE playlist_id = ? ORDER by position ASC", playlistId)
 203         if err != nil {
 204                 return ret
 205         }
 206         defer rows.Close()
 207         for rows.Next() {
 208                 var entry PlaylistEntry
 209                 if err := rows.Scan(&entry.Id, &entry.Position, &entry.Filename, &entry.DelaySeconds, &entry.IsRelative); err != nil {
 210                         return ret
 211                 }
 212                 ret = append(ret, entry)
 213         }
 214         return ret
 215 }
 216
 217 func (d *Database) GetRadio(radioId int) (Radio, error) {
 218         var r Radio
 219         err := d.sqldb.QueryRow("SELECT id, name, token FROM radios WHERE id = ?", radioId).Scan(&r.Id, &r.Name, &r.Token)
 220         if err != nil {
 221                 return r, err
 222         }
 223         return r, nil
 224 }
 225
 226 func (d *Database) GetRadioByToken(token string) (Radio, error) {
 227         var r Radio
 228         err := d.sqldb.QueryRow("SELECT id, name, token FROM radios WHERE token = ?", token).Scan(&r.Id, &r.Name, &r.Token)
 229         if err != nil {
 230                 return r, err
 231         }
 232         return r, nil
 233 }
 234
 235 func (d *Database) GetRadios() []Radio {
 236         ret := make([]Radio, 0)
 237         rows, err := d.sqldb.Query("SELECT id, name, token FROM radios ORDER BY id ASC")
 238         if err != nil {
 239                 return ret
 240         }
 241         defer rows.Close()
 242         for rows.Next() {
 243                 var r Radio
 244                 if err := rows.Scan(&r.Id, &r.Name, &r.Token); err != nil {
 245                         return ret
 246                 }
 247                 ret = append(ret, r)
 248         }
 249         return ret
 250 }
 251
 252 func (d *Database) DeleteRadio(radioId int) {
 253         d.sqldb.Exec("DELETE FROM radios WHERE id = ?", radioId)
 254 }
 255
 256 func (d *Database) CreateRadio(radio Radio) {
 257         d.sqldb.Exec("INSERT INTO radios (name, token) values (?, ?)", radio.Name, radio.Token)
 258 }
 259
 260 func (d *Database) UpdateRadio(radio Radio) {
 261         d.sqldb.Exec("UPDATE radios SET name = ?, token = ? WHERE id = ?", radio.Name, radio.Token, radio.Id)
 262 }