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         _, 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) GetUsers() []User {
  83         ret := make([]User, 0)
  84         rows, err := d.sqldb.Query("SELECT id, username, password_hash, is_admin FROM users ORDER BY username ASC")
  85         if err != nil {
  86                 return ret
  87         }
  88         defer rows.Close()
  89         for rows.Next() {
  90                 var u User
  91                 if err := rows.Scan(&u.Id, &u.Username, &u.PasswordHash, &u.IsAdmin); err != nil {
  92                         return ret
  93                 }
  94                 ret = append(ret, u)
  95         }
  96         return ret
  97 }
  98
  99 func (d *Database) SetUserPassword(username string, passwordHash string) {
 100         d.sqldb.Exec("UPDATE users SET password_hash = ? WHERE username = ?", passwordHash, username)
 101 }
 102
 103 func (d *Database) ClearOtherSessions(username string, token string) {
 104         d.sqldb.Exec("DELETE FROM sessions WHERE username = ? AND token != ?", username, token)
 105 }
 106
 107 func (d *Database) SetUserIsAdmin(username string, isAdmin bool) {
 108         d.sqldb.Exec("UPDATE users SET is_admin = ? WHERE username = ?", isAdmin, username)
 109 }
 110
 111 func (d *Database) CreateUser(user User) error {
 112         _, err := d.sqldb.Exec("INSERT INTO users (username, password_hash, is_admin) values (?, ?, ?)", user.Username, user.PasswordHash, user.IsAdmin)
 113         return err
 114 }
 115
 116 func (d *Database) DeleteUser(username string) error {
 117         _, err := d.sqldb.Exec("DELETE FROM users WHERE username = ?", username)
 118         return err
 119 }
 120
 121 func (d *Database) CreatePlaylist(playlist Playlist) int {
 122         var id int
 123         tx, _ := d.sqldb.Begin()
 124         _, err := tx.Exec("INSERT INTO playlists (enabled, name, start_time) values (?, ?, ?)", playlist.Enabled, playlist.Name, playlist.StartTime)
 125         if err != nil {
 126                 log.Fatal(err)
 127         }
 128         err = tx.QueryRow("SELECT last_insert_rowid()").Scan(&id)
 129         if err != nil {
 130                 log.Fatal(err)
 131         }
 132         err = tx.Commit()
 133         if err != nil {
 134                 log.Fatal(err)
 135         }
 136         return id
 137 }
 138
 139 func (d *Database) DeletePlaylist(playlistId int) {
 140         d.sqldb.Exec("DELETE FROM playlists WHERE id = ?", playlistId)
 141 }
 142
 143 func (d *Database) GetPlaylists() []Playlist {
 144         ret := make([]Playlist, 0)
 145         rows, err := d.sqldb.Query("SELECT id, enabled, name, start_time FROM playlists ORDER BY id ASC")
 146         if err != nil {
 147                 return ret
 148         }
 149         defer rows.Close()
 150         for rows.Next() {
 151                 var p Playlist
 152                 if err := rows.Scan(&p.Id, &p.Enabled, &p.Name, &p.StartTime); err != nil {
 153                         return ret
 154                 }
 155                 ret = append(ret, p)
 156         }
 157         return ret
 158 }
 159
 160 func (d *Database) GetPlaylist(playlistId int) (Playlist, error) {
 161         var p Playlist
 162         err := d.sqldb.QueryRow("SELECT id, enabled, name, start_time FROM playlists WHERE id = ?", playlistId).Scan(&p.Id, &p.Enabled, &p.Name, &p.StartTime)
 163         if err != nil {
 164                 return p, err
 165         }
 166         return p, nil
 167 }
 168
 169 func (d *Database) UpdatePlaylist(playlist Playlist) {
 170         d.sqldb.Exec("UPDATE playlists SET enabled = ?, name = ?, start_time = ? WHERE id = ?", playlist.Enabled, playlist.Name, playlist.StartTime, playlist.Id)
 171 }
 172
 173 func (d *Database) SetEntriesForPlaylist(entries []PlaylistEntry, playlistId int) {
 174         tx, _ := d.sqldb.Begin()
 175         _, err := tx.Exec("DELETE FROM playlist_entries WHERE playlist_id = ?", playlistId)
 176         for _, e := range entries {
 177                 _, 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)
 178                 if err != nil {
 179                         log.Fatal(err)
 180                 }
 181         }
 182         tx.Commit() // ignore errors
 183 }
 184
 185 func (d *Database) GetEntriesForPlaylist(playlistId int) []PlaylistEntry {
 186         ret := make([]PlaylistEntry, 0)
 187         rows, err := d.sqldb.Query("SELECT id, position, filename, delay_seconds, is_relative FROM playlist_entries WHERE playlist_id = ? ORDER by position ASC", playlistId)
 188         if err != nil {
 189                 return ret
 190         }
 191         defer rows.Close()
 192         for rows.Next() {
 193                 var entry PlaylistEntry
 194                 if err := rows.Scan(&entry.Id, &entry.Position, &entry.Filename, &entry.DelaySeconds, &entry.IsRelative); err != nil {
 195                         return ret
 196                 }
 197                 ret = append(ret, entry)
 198         }
 199         return ret
 200 }
 201
 202 func (d *Database) GetRadio(radioId int) (Radio, error) {
 203         var r Radio
 204         err := d.sqldb.QueryRow("SELECT id, name, token FROM radios WHERE id = ?", radioId).Scan(&r.Id, &r.Name, &r.Token)
 205         if err != nil {
 206                 return r, err
 207         }
 208         return r, nil
 209 }
 210
 211 func (d *Database) GetRadioByToken(token string) (Radio, error) {
 212         var r Radio
 213         err := d.sqldb.QueryRow("SELECT id, name, token FROM radios WHERE token = ?", token).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) GetRadios() []Radio {
 221         ret := make([]Radio, 0)
 222         rows, err := d.sqldb.Query("SELECT id, name, token FROM radios ORDER BY id ASC")
 223         if err != nil {
 224                 return ret
 225         }
 226         defer rows.Close()
 227         for rows.Next() {
 228                 var r Radio
 229                 if err := rows.Scan(&r.Id, &r.Name, &r.Token); err != nil {
 230                         return ret
 231                 }
 232                 ret = append(ret, r)
 233         }
 234         return ret
 235 }
 236
 237 func (d *Database) DeleteRadio(radioId int) {
 238         d.sqldb.Exec("DELETE FROM radios WHERE id = ?", radioId)
 239 }
 240
 241 func (d *Database) CreateRadio(radio Radio) {
 242         d.sqldb.Exec("INSERT INTO radios (name, token) values (?, ?)", radio.Name, radio.Token)
 243 }
 244
 245 func (d *Database) UpdateRadio(radio Radio) {
 246         d.sqldb.Exec("UPDATE radios SET name = ?, token = ? WHERE id = ?", radio.Name, radio.Token, radio.Id)
 247 }