221 lines
4.8 KiB
Go
221 lines
4.8 KiB
Go
package database
|
|
|
|
import (
|
|
"database/sql"
|
|
"encoding/json"
|
|
)
|
|
|
|
type Song struct {
|
|
Type
|
|
id int64
|
|
resource int64
|
|
cover int64
|
|
album int
|
|
metadata []byte
|
|
}
|
|
|
|
func InsertSongToDatabase(db *Database, resourceId int64, names map[string]string) *Song {
|
|
|
|
//TODO: do it in single tx
|
|
|
|
rows, err := db.Query("INSERT INTO songs (resource) VALUES ($1) RETURNING id;", resourceId)
|
|
if err != nil {
|
|
return nil
|
|
}
|
|
defer rows.Close()
|
|
rows.Next()
|
|
var id int64
|
|
err = rows.Scan(&id)
|
|
if err != nil {
|
|
return nil
|
|
}
|
|
song := GetSongFromDatabase(db, id)
|
|
if song == nil {
|
|
return nil
|
|
}
|
|
|
|
for k, n := range names {
|
|
song.AddName(k, n)
|
|
}
|
|
|
|
return song
|
|
}
|
|
|
|
func GetSongsFromDatabaseByName(db *Database, name string) (songs []*Song) {
|
|
rows, err := db.Query("SELECT id, resource, cover, album, metadata FROM songs WHERE id IN(SELECT song FROM songs_names WHERE name ILIKE $1);", name)
|
|
if err != nil {
|
|
return nil
|
|
}
|
|
defer rows.Close()
|
|
for rows.Next() {
|
|
song, err := GetSongFromRow(db, rows)
|
|
if err != nil {
|
|
break
|
|
}
|
|
songs = append(songs, song)
|
|
}
|
|
return
|
|
}
|
|
|
|
func GetSongFromDatabase(db *Database, id int64) *Song {
|
|
rows, err := db.Query("SELECT id, resource, cover, album, metadata FROM songs WHERE id = $1;", id)
|
|
if err != nil {
|
|
return nil
|
|
}
|
|
defer rows.Close()
|
|
rows.Next()
|
|
r, err := GetSongFromRow(db, rows)
|
|
if err != nil {
|
|
return nil
|
|
}
|
|
return r
|
|
}
|
|
|
|
func GetSongFromRow(db *Database, row *sql.Rows) (*Song, error) {
|
|
r := &Song{
|
|
Type: Type{
|
|
db: db,
|
|
},
|
|
}
|
|
var cover sql.NullInt64
|
|
err := row.Scan(&r.id, &r.resource, &cover, &r.album, &r.metadata)
|
|
|
|
if cover.Valid {
|
|
r.cover = cover.Int64
|
|
}
|
|
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
return r, nil
|
|
}
|
|
|
|
func (r *Song) GetId() int64 {
|
|
return r.id
|
|
}
|
|
|
|
func (r *Song) GetResource() *Resource {
|
|
return GetResourceFromDatabase(r.db, r.resource)
|
|
}
|
|
|
|
func (r *Song) GetCover() *Resource {
|
|
if r.cover == 0 {
|
|
return nil
|
|
}
|
|
|
|
return GetResourceFromDatabase(r.db, r.cover)
|
|
}
|
|
|
|
func (r *Song) SetCover(resource *Resource) {
|
|
if resource == nil {
|
|
r.db.Exec("UPDATE songs SET cover = NULL WHERE id = $1;", r.id)
|
|
return
|
|
}
|
|
|
|
r.db.Exec("UPDATE songs SET cover = $2 WHERE id = $1;", r.id, resource.id)
|
|
}
|
|
|
|
func (r *Song) GetAlbum() *Album {
|
|
if r.album == 0 {
|
|
return nil
|
|
}
|
|
return GetAlbumFromDatabase(r.db, r.album)
|
|
}
|
|
|
|
func (r *Song) SetAlbum(album *Album) {
|
|
if album == nil {
|
|
r.db.Exec("UPDATE songs SET album = NULL WHERE id = $1;", r.id)
|
|
return
|
|
}
|
|
|
|
r.db.Exec("UPDATE songs SET album = $2 WHERE id = $1;", r.id, album.id)
|
|
}
|
|
|
|
func (r *Song) GetNames() map[string]string {
|
|
|
|
result := make(map[string]string)
|
|
|
|
rows, err := r.db.Query("SELECT kind, name FROM song_names WHERE id $1;", r.id)
|
|
if err != nil {
|
|
return nil
|
|
}
|
|
defer rows.Close()
|
|
for rows.Next() {
|
|
var kind string
|
|
var name string
|
|
err = rows.Scan(&kind, &name)
|
|
if err != nil {
|
|
break
|
|
}
|
|
result[kind] = name
|
|
}
|
|
|
|
return result
|
|
}
|
|
|
|
func (r *Song) RemoveName(kind string) {
|
|
r.db.Exec("DELETE FROM song_names WHERE id = $1 AND kind = $2;", r.id, kind)
|
|
}
|
|
|
|
func (r *Song) AddName(kind, name string) {
|
|
r.db.Exec("INSERT INTO song_names (song, kind, name) VALUES ($1, $2, $3) ON CONFLICT (song, kind) DO UPDATE SET name = $3;", r.id, kind, name)
|
|
}
|
|
|
|
func (r *Song) GetMetadata() (result map[string]interface{}) {
|
|
json.Unmarshal(r.metadata, &result)
|
|
return result
|
|
}
|
|
|
|
func (r *Song) SetMetadata(value map[string]interface{}) {
|
|
r.metadata, _ = json.Marshal(value)
|
|
r.db.Exec("UPDATE songs SET metadata = $2::jsonb WHERE id = $1;", r.id, r.metadata)
|
|
}
|
|
|
|
func (r *Song) GetTags() (tags []*Tag) {
|
|
rows, err := r.db.Query("SELECT id, name FROM tags WHERE id IN(SELECT tag FROM song_taggings WHERE song = $1);", r.id)
|
|
if err != nil {
|
|
return nil
|
|
}
|
|
defer rows.Close()
|
|
for rows.Next() {
|
|
tag, err := GetTagFromRow(r.db, rows)
|
|
if err != nil {
|
|
break
|
|
}
|
|
tags = append(tags, tag)
|
|
}
|
|
return
|
|
}
|
|
|
|
func (r *Song) RemoveTag(tag *Tag) {
|
|
r.db.Exec("DELETE FROM song_taggings WHERE song = $1 AND tag = $2;", r.id, tag.id)
|
|
}
|
|
|
|
func (r *Song) AddTag(tag *Tag) {
|
|
r.db.Exec("INSERT INTO song_taggings (tag, song) VALUES ($2, $1) ON CONFLICT (tag, song) DO NOTHING;", r.id, tag.id)
|
|
}
|
|
|
|
func (r *Song) GetArtists() (artists []*SongArtist) {
|
|
rows, err := r.db.Query("SELECT artist as id, (SELECT artists.metadata FROM artists WHERE artists.id = song_artists.artist) as metadata, kind FROM song_artists WHERE song_artists.song = $1;", r.id)
|
|
if err != nil {
|
|
return nil
|
|
}
|
|
defer rows.Close()
|
|
for rows.Next() {
|
|
artist, err := GetSongArtistFromRow(r.db, rows)
|
|
if err != nil {
|
|
break
|
|
}
|
|
artists = append(artists, artist)
|
|
}
|
|
return
|
|
}
|
|
|
|
func (r *Song) RemoveArtist(artist *SongArtist) {
|
|
r.db.Exec("DELETE FROM song_artists WHERE song = $1 AND kind = $3 AND tag = $2;", r.id, artist.id, artist.kind)
|
|
}
|
|
|
|
func (r *Song) AddArtist(artist *SongArtist) {
|
|
r.db.Exec("INSERT INTO song_artists (song, artist, kind) VALUES ($1, $2, $3) ON CONFLICT (song, artist, kind) DO NOTHING;", r.id, artist.id, artist.kind)
|
|
}
|