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