arimelody-web/repository/music/release_postgres.go

package music

import (
	"arimelody-web/model"
	"time"
)

func (repo *MusicRepositoryPostgres) GetAllReleases(onlyVisible bool, limit int) ([]*model.Release, error) {
    var releases = []*model.Release{}

    query := "SELECT * FROM musicrelease"
    if onlyVisible {
        query += " WHERE visible=true"
    }
    query += " ORDER BY release_date DESC"
    var err error
    if limit > 0 {
        err = repo.db.Select(&releases, query + " LIMIT $1", limit)
    } else {
        err = repo.db.Select(&releases, query)
    }
    if err != nil {
        return nil, err
    }

    return releases, nil
}
func (repo *MusicRepositoryPostgres) GetReleaseCount(onlyVisible bool) (int, error) {
    query := "SELECT count(*) FROM musicrelease"
    if onlyVisible {
        query += " WHERE visible=true"
    }

    var count int
    err := repo.db.Get(&count, query)

    return count, err
}

func (repo *MusicRepositoryPostgres) GetReleaseByID(id string) (*model.Release, error) {
    var release = model.Release{}
    err := repo.db.Get(&release, "SELECT * FROM musicrelease WHERE id=$1", id)
    if err != nil { return nil, err }
    return &release, nil
}

func (repo *MusicRepositoryPostgres) GetReleaseTracks(releaseID string) ([]*model.Track, error) {
    var tracks = []*model.Track{}

    err := repo.db.Select(&tracks,
        "SELECT musictrack.* FROM musictrack "+
        "JOIN musicreleasetrack ON track=id "+
        "WHERE release=$1 "+
        "ORDER BY number ASC",
        releaseID,
    )
    if err != nil {
        return nil, err
    }

    return tracks, nil
}

func (repo *MusicRepositoryPostgres) GetReleaseCredits(releaseID string) ([]*model.Credit, error) {
    rows, err := repo.db.Query(
        "SELECT artist.id,artist.name,artist.website,artist.avatar,role,is_primary "+
        "FROM musiccredit "+
        "JOIN artist ON artist=artist.id "+
        "JOIN musicrelease ON release=musicrelease.id "+
        "WHERE musicrelease.id=$1 "+
        "ORDER BY is_primary DESC",
        releaseID,
    )
    if err != nil {
        return nil, err
    }

    var credits []*model.Credit
    for rows.Next() {
        credit := &model.Credit{
            Artist: &model.Artist{},
        }
        rows.Scan(
            &credit.Artist.ID,
            &credit.Artist.Name,
            &credit.Artist.Website,
            &credit.Artist.Avatar,
            &credit.Role,
            &credit.Primary)
        credits = append(credits, credit)
    }

    return credits, nil
}

func (repo *MusicRepositoryPostgres) GetReleaseLinks(releaseID string) ([]*model.Link, error) {
    var links = []*model.Link{}

    err := repo.db.Select(&links, "SELECT name,url FROM musiclink WHERE release=$1", releaseID)
    if err != nil {
        return nil, err
    }

    return links, nil
}

func (repo *MusicRepositoryPostgres) CreateRelease(
    id string,
    title string,
    releaseType model.ReleaseType,
    releaseDate time.Time,
    artworkURL string,
) error {
    _, err := repo.db.Exec(
        "INSERT INTO musicrelease "+
        "(id, title, type, release_date, artwork) "+
        "VALUES ($1, $2, $3, $4, $5)",
        id,
        title,
        releaseType,
        releaseDate.Format("2006-01-02 15:04:05"),
        artworkURL,
    )
    if err != nil {
        return err
    }

    return nil
}

func (repo *MusicRepositoryPostgres) UpdateRelease(release *model.Release) error {
    _, err := repo.db.Exec(
        "UPDATE musicrelease SET "+
        "visible=$2, title=$3, description=$4, type=$5, release_date=$6, artwork=$7, buyname=$8, buylink=$9, copyright=$10, copyrighturl=$11 "+
        "WHERE id=$1",
        release.ID,
        release.Visible,
        release.Title,
        release.Description,
        release.ReleaseType,
        release.ReleaseDate.Format("2006-01-02 15:04:05"),
        release.Artwork,
        release.Buyname,
        release.Buylink,
        release.Copyright,
        release.CopyrightURL,
    )
    if err != nil {
        return err
    }

    return nil
}

func (repo *MusicRepositoryPostgres) UpdateReleaseID(oldID string, newID string) error {
    _, err := repo.db.Exec("UPDATE musicrelease SET id=$2 WHERE id=$1", oldID, newID)
    return err
}

func (repo *MusicRepositoryPostgres) UpdateReleaseVisibility(id string, visible bool) error {
    _, err := repo.db.Exec("UPDATE musicrelease SET visible=$2 WHERE id=$1", id, visible)
    return err
}
func (repo *MusicRepositoryPostgres) UpdateReleaseTitle(id string, title string) error {
    _, err := repo.db.Exec("UPDATE musicrelease SET title=$2 WHERE id=$1", id, title)
    return err
}
func (repo *MusicRepositoryPostgres) UpdateReleaseDescription(id string, description string) error {
    _, err := repo.db.Exec("UPDATE musicrelease SET description=$2 WHERE id=$1", id, description)
    return err
}
func (repo *MusicRepositoryPostgres) UpdateReleaseType(id string, releaseType model.ReleaseType) error {
    _, err := repo.db.Exec("UPDATE musicrelease SET type=$2 WHERE id=$1", id, releaseType)
    return err
}
func (repo *MusicRepositoryPostgres) UpdateReleaseDate(id string, releaseDate time.Time) error {
    _, err := repo.db.Exec(
        "UPDATE musicrelease SET release_date=$2 WHERE id=$1",
        id, 
        releaseDate.Format("2006-01-02 15:04:05"),
    )
    return err
}
func (repo *MusicRepositoryPostgres) UpdateReleaseArtwork(id string, artwork string) error {
    _, err := repo.db.Exec("UPDATE musicrelease SET artwork=$2 WHERE id=$1", id, artwork)
    return err
}
func (repo *MusicRepositoryPostgres) UpdateReleaseBuyInfo(id string, buyName string, buyLink string) error {
    _, err := repo.db.Exec(
        "UPDATE musicrelease SET buyname=$2,buylink=$3 WHERE id=$1",
        id, buyName, buyLink,
    )
    return err
}
func (repo *MusicRepositoryPostgres) UpdateReleaseCopyright(id string, copyright string, url string) error {
    _, err := repo.db.Exec(
        "UPDATE musicrelease SET copyright=$2,copyrighturl=$3 WHERE id=$1",
        id, copyright, url,
    )
    return err
}

func (repo *MusicRepositoryPostgres) UpdateReleaseTracks(releaseID string, newTrackIDs []string) error {
    tx, err := repo.db.Begin()
    if err != nil {
        return err
    }

    _, err = tx.Exec("DELETE FROM musicreleasetrack WHERE release=$1", releaseID)
    if err != nil {
        return err
    }
    for i, trackID := range newTrackIDs {
        _, err = tx.Exec(
            "INSERT INTO musicreleasetrack "+
            "(release, track, number) "+
            "VALUES ($1, $2, $3)",
            releaseID,
            trackID,
            i)
        if err != nil {
            return err
        }
    }

    err = tx.Commit()
    if err != nil {
        return err
    }

    return nil
}

func (repo *MusicRepositoryPostgres) UpdateReleaseCredits(releaseID string, newCredits []*model.Credit) error {
    tx, err := repo.db.Begin()
    if err != nil {
        return err
    }

    _, err = tx.Exec("DELETE FROM musiccredit WHERE release=$1", releaseID)
    if err != nil {
        return err
    }
    for _, credit := range newCredits {
        _, err = tx.Exec(
            "INSERT INTO musiccredit "+
            "(release, artist, role, is_primary) "+
            "VALUES ($1, $2, $3, $4)",
            releaseID,
            credit.Artist.ID,
            credit.Role,
            credit.Primary,
        )
        if err != nil {
            return err
        }
    }

    err = tx.Commit()
    if err != nil {
        return err
    }

    return nil
}

func (repo *MusicRepositoryPostgres) UpdateReleaseLinks(releaseID string, newLinks []*model.Link) error {
    tx, err := repo.db.Begin()
    if err != nil {
        return err
    }

    _, err = tx.Exec("DELETE FROM musiclink WHERE release=$1", releaseID)
    if err != nil {
        return err
    }
    for _, link := range newLinks {
        _, err := tx.Exec(
            "INSERT INTO musiclink "+
            "(release, name, url) "+
            "VALUES ($1, $2, $3)",
            releaseID,
            link.Name,
            link.URL,
        )
        if err != nil {
            return err
        }
    }

    err = tx.Commit()
    if err != nil {
        return err
    }

    return nil
}

func (repo *MusicRepositoryPostgres) DeleteRelease(id string) (string, error) {
    var deletedID string
    err := repo.db.Get(&deletedID, "DELETE FROM musicrelease WHERE id=$1", id)
    return deletedID, err
}