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         `
  45         _, err = db.sqldb.Exec(sqlStmt)
  46         if err != nil {
  47                 log.Printf("%q: %s\n", err, sqlStmt)
  48                 return
  49         }
  50 }
  51
  52 func (d *Database) CloseDatabase() {
  53         d.sqldb.Close()
  54 }
  55
  56 func (d *Database) InsertSession(user string, token string, expiry time.Time) {
  57         _, err := d.sqldb.Exec("INSERT INTO sessions (token, username, created, expiry) values (?, ?, CURRENT_TIMESTAMP, ?)", token, user, expiry)
  58         if err != nil {
  59                 log.Fatal(err)
  60         }
  61 }
  62
  63 func (d *Database) GetUserForSession(token string) (string, error) {
  64         var username string
  65         err := d.sqldb.QueryRow("SELECT username FROM sessions WHERE token = ? AND expiry > CURRENT_TIMESTAMP", token).Scan(&username)
  66         if err != nil {
  67                 return "", errors.New("no matching token")
  68         }
  69         return username, nil
  70 }
  71
  72 func (d *Database) CreatePlaylist(playlist Playlist) int {
  73         var id int
  74         tx, _ := d.sqldb.Begin()
  75         _, err := tx.Exec("INSERT INTO playlists (enabled, name, start_time) values (?, ?, ?)", playlist.Enabled, playlist.Name, playlist.StartTime)
  76         if err != nil {
  77                 log.Fatal(err)
  78         }
  79         err = tx.QueryRow("SELECT last_insert_rowid()").Scan(&id)
  80         if err != nil {
  81                 log.Fatal(err)
  82         }
  83         err = tx.Commit()
  84         if err != nil {
  85                 log.Fatal(err)
  86         }
  87         return id
  88 }
  89
  90 func (d *Database) DeletePlaylist(playlistId int) {
  91         d.sqldb.Exec("DELETE FROM playlists WHERE id = ?", playlistId)
  92 }
  93
  94 func (d *Database) GetPlaylists() []Playlist {
  95         ret := make([]Playlist, 0)
  96         rows, err := d.sqldb.Query("SELECT id, enabled, name, start_time FROM playlists ORDER BY id ASC")
  97         if err != nil {
  98                 return ret
  99         }
 100         defer rows.Close()
 101         for rows.Next() {
 102                 var p Playlist
 103                 if err := rows.Scan(&p.Id, &p.Enabled, &p.Name, &p.StartTime); err != nil {
 104                         return ret
 105                 }
 106                 ret = append(ret, p)
 107         }
 108         return ret
 109 }
 110
 111 func (d *Database) GetPlaylist(playlistId int) (Playlist, error) {
 112         var p Playlist
 113         err := d.sqldb.QueryRow("SELECT id, enabled, name, start_time FROM playlists WHERE id = ?", playlistId).Scan(&p.Id, &p.Enabled, &p.Name, &p.StartTime)
 114         if err != nil {
 115                 return p, err
 116         }
 117         return p, nil
 118 }
 119
 120 func (d *Database) UpdatePlaylist(playlist Playlist) {
 121         d.sqldb.Exec("UPDATE playlists SET enabled = ?, name = ?, start_time = ? WHERE id = ?", playlist.Enabled, playlist.Name, playlist.StartTime, playlist.Id)
 122 }
 123
 124 func (d *Database) SetEntriesForPlaylist(entries []PlaylistEntry, playlistId int) {
 125         tx, _ := d.sqldb.Begin()
 126         _, err := tx.Exec("DELETE FROM playlist_entries WHERE playlist_id = ?", playlistId)
 127         for _, e := range entries {
 128                 _, 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)
 129                 if err != nil {
 130                         log.Fatal(err)
 131                 }
 132         }
 133         tx.Commit() // ignore errors
 134 }
 135
 136 func (d *Database) GetEntriesForPlaylist(playlistId int) []PlaylistEntry {
 137         ret := make([]PlaylistEntry, 0)
 138         rows, err := d.sqldb.Query("SELECT id, position, filename, delay_seconds, is_relative FROM playlist_entries WHERE playlist_id = ? ORDER by position ASC", playlistId)
 139         if err != nil {
 140                 return ret
 141         }
 142         defer rows.Close()
 143         for rows.Next() {
 144                 var entry PlaylistEntry
 145                 if err := rows.Scan(&entry.Id, &entry.Position, &entry.Filename, &entry.DelaySeconds, &entry.IsRelative); err != nil {
 146                         return ret
 147                 }
 148                 ret = append(ret, entry)
 149         }
 150         return ret
 151 }
 152
 153 func (d *Database) GetRadio(radioId int) (Radio, error) {
 154         var r Radio
 155         err := d.sqldb.QueryRow("SELECT id, name, token FROM radios WHERE id = ?", radioId).Scan(&r.Id, &r.Name, &r.Token)
 156         if err != nil {
 157                 return r, err
 158         }
 159         return r, nil
 160 }
 161
 162 func (d *Database) GetRadioByToken(token string) (Radio, error) {
 163         var r Radio
 164         err := d.sqldb.QueryRow("SELECT id, name, token FROM radios WHERE token = ?", token).Scan(&r.Id, &r.Name, &r.Token)
 165         if err != nil {
 166                 return r, err
 167         }
 168         return r, nil
 169 }
 170
 171 func (d *Database) GetRadios() []Radio {
 172         ret := make([]Radio, 0)
 173         rows, err := d.sqldb.Query("SELECT id, name, token FROM radios ORDER BY id ASC")
 174         if err != nil {
 175                 return ret
 176         }
 177         defer rows.Close()
 178         for rows.Next() {
 179                 var r Radio
 180                 if err := rows.Scan(&r.Id, &r.Name, &r.Token); err != nil {
 181                         return ret
 182                 }
 183                 ret = append(ret, r)
 184         }
 185         return ret
 186 }
 187
 188 func (d *Database) DeleteRadio(radioId int) {
 189         d.sqldb.Exec("DELETE FROM radios WHERE id = ?", radioId)
 190 }
 191
 192 func (d *Database) CreateRadio(radio Radio) {
 193         d.sqldb.Exec("INSERT INTO radios (name, token) values (?, ?)", radio.Name, radio.Token)
 194 }
 195
 196 func (d *Database) UpdateRadio(radio Radio) {
 197         d.sqldb.Exec("UPDATE radios SET name = ?, token = ? WHERE id = ?", radio.Name, radio.Token, radio.Id)
 198 }