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) 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 }