lyrics

release.go (10673B)

---

 package db
 
 import (
 	"fmt"
 	"lyrics/internal/domain"
 	"strings"
 	"time"
 )
 
 type releaseArtistRow struct {
 	artistID  domain.ArtistID
 	role      domain.ArtistRole
 	sortOrder int
 }
 
 type releaseSongRow struct {
 	songID      domain.SongID
 	trackNumber int
 }
 
 func Release(e Execer, id domain.ReleaseID) (domain.Release, error) {
 	var release domain.Release
 	var releaseDateStr string
 
 	err := e.QueryRow(`
 		select id, musicbrainz_release_id, title, latin_title, release_date, type
 		from releases
 		where id = ?
 	`, id).Scan(&release.ID, &release.MusicBrainzReleaseID, &release.Title, &release.LatinTitle, &releaseDateStr, &release.Type)
 	if err != nil {
 		return domain.Release{}, fmt.Errorf("failed to get release: %w", err)
 	}
 
 	release.ReleaseDate, err = time.Parse(time.RFC3339, releaseDateStr)
 	if err != nil {
 		return domain.Release{}, fmt.Errorf("failed to parse release release_date: %w", err)
 	}
 
 	// fetch release_artists rows
 	var artistRows []releaseArtistRow
 
 	{
 		rows, err := e.Query(`
 			select artist_id, role, sort_order
 			from release_artists
 			where release_id = ?
 			order by sort_order
 		`, id)
 		if err != nil {
 			return domain.Release{}, fmt.Errorf("failed to query release artists: %w", err)
 		}
 
 		for rows.Next() {
 			var r releaseArtistRow
 			if err := rows.Scan(&r.artistID, &r.role, &r.sortOrder); err != nil {
 				_ = rows.Close()
 				return domain.Release{}, fmt.Errorf("failed to scan release artist: %w", err)
 			}
 			artistRows = append(artistRows, r)
 		}
 		if err := rows.Err(); err != nil {
 			_ = rows.Close()
 			return domain.Release{}, fmt.Errorf("failed to iterate release artists: %w", err)
 		}
 		_ = rows.Close()
 	}
 
 	// fetch full artist records in one query
 	if len(artistRows) > 0 {
 		artistIDs := make([]any, len(artistRows))
 		for i, r := range artistRows {
 			artistIDs[i] = r.artistID
 		}
 		placeholders := strings.Repeat("?,", len(artistIDs))
 		placeholders = placeholders[:len(placeholders)-1]
 
 		artistMap := make(map[domain.ArtistID]domain.Artist, len(artistIDs))
 		{
 			rows, err := e.Query(`
 				select id, musicbrainz_artist_id, name, latin_name, disambiguation, bio
 				from artists
 				where id in (`+placeholders+`)
 			`, artistIDs...)
 			if err != nil {
 				return domain.Release{}, fmt.Errorf("failed to query artists: %w", err)
 			}
 
 			for rows.Next() {
 				var a domain.Artist
 				if err := rows.Scan(&a.ID, &a.MusicBrainzArtistID, &a.Name, &a.LatinName, &a.Disambiguation, &a.Bio); err != nil {
 					_ = rows.Close()
 					return domain.Release{}, fmt.Errorf("failed to scan artist: %w", err)
 				}
 				artistMap[a.ID] = a
 			}
 			if err := rows.Err(); err != nil {
 				_ = rows.Close()
 				return domain.Release{}, fmt.Errorf("failed to iterate artists: %w", err)
 			}
 			_ = rows.Close()
 		}
 
 		release.Artists = make([]domain.ReleaseArtist, len(artistRows))
 		for i, r := range artistRows {
 			release.Artists[i] = domain.ReleaseArtist{
 				Artist:    artistMap[r.artistID],
 				Role:      r.role,
 				SortOrder: r.sortOrder,
 			}
 		}
 	}
 
 	// fetch songs on this release ordered by track_number
 	var songRows []releaseSongRow
 	{
 		rows, err := e.Query(`
 			select song_id, track_number
 			from song_releases
 			where release_id = ?
 			order by track_number
 		`, id)
 		if err != nil {
 			return domain.Release{}, fmt.Errorf("failed to query release songs: %w", err)
 		}
 
 		for rows.Next() {
 			var r releaseSongRow
 			if err := rows.Scan(&r.songID, &r.trackNumber); err != nil {
 				_ = rows.Close()
 				return domain.Release{}, fmt.Errorf("failed to scan release song: %w", err)
 			}
 			songRows = append(songRows, r)
 		}
 		if err := rows.Err(); err != nil {
 			_ = rows.Close()
 			return domain.Release{}, fmt.Errorf("failed to iterate release songs: %w", err)
 		}
 		_ = rows.Close()
 	}
 
 	release.Songs = make([]domain.ReleaseSong, 0, len(songRows))
 	for _, r := range songRows {
 		song, err := Song(e, r.songID)
 		if err != nil {
 			return domain.Release{}, fmt.Errorf("failed to get song %d: %w", r.songID, err)
 		}
 		release.Songs = append(release.Songs, domain.ReleaseSong{
 			Song:        song,
 			TrackNumber: r.trackNumber,
 		})
 	}
 
 	return release, nil
 }
 
 func UpdateRelease(e Execer, release domain.EditRelease) error {
 	result, err := e.Exec(`
 		update releases 
 		set
 			type = ?,
 			musicbrainz_release_id = ?,
 			title = ?,
 			latin_title = ?,
 			release_date = ?
 		where id = ?
 	`, release.Type, release.MusicBrainzReleaseID, release.Title, release.LatinTitle, release.ReleaseDate.Format(time.RFC3339), release.ID)
 	if err != nil {
 		return fmt.Errorf("failed to update release: %w", err)
 	}
 
 	rowsAffected, err := result.RowsAffected()
 	if err != nil {
 		return fmt.Errorf("failed to check updated release rows: %w", err)
 	}
 	if rowsAffected == 0 {
 		return fmt.Errorf("release %d does not exist", release.ID)
 	}
 
 	_, err = e.Exec(`
 		delete from release_artists
 		where release_id = ?
 	`, release.ID)
 	if err != nil {
 		return fmt.Errorf("failed to delete release primary artists: %w", err)
 	}
 
 	for _, artist := range release.Artists {
 		_, err = e.Exec(`
 			insert into release_artists (
 				release_id,
 				artist_id,
 				role,
 				sort_order
 			)
 			values (?, ?, ?, ?)
 		`, release.ID, artist.ArtistID, artist.Role, artist.SortOrder)
 		if err != nil {
 			return fmt.Errorf("failed to insert release artist: %w", err)
 		}
 	}
 
 	_, err = e.Exec(`
 		delete from song_releases
 		where release_id = ?
 	`, release.ID)
 	if err != nil {
 		return fmt.Errorf("failed to delete release songs: %w", err)
 	}
 
 	for _, song := range release.Songs {
 		_, err = e.Exec(`
 			insert into song_releases (
 				song_id,
 				release_id,
 				track_number
 			)
 			values (?, ?, ?)
 		`, song.SongID, release.ID, song.TrackNumber)
 		if err != nil {
 			return fmt.Errorf("failed to update release song: %w", err)
 		}
 	}
 
 	return nil
 }
 
 func InsertRelease(e Execer, release domain.NewRelease) (domain.ReleaseID, error) {
 	result, err := e.Exec(`
 		insert into releases (
 			type,
 			musicbrainz_release_id,
 			title,
 			latin_title,
 			release_date,
 			created_at
 		)
 		values (?, ?, ?, ?, ?, ?)
 	`, release.Type, release.MusicBrainzReleaseID, release.Title, release.LatinTitle, release.ReleaseDate.Format(time.RFC3339), time.Now().Format(time.RFC3339))
 	if err != nil {
 		return 0, fmt.Errorf("failed to insert release: %w", err)
 	}
 
 	lastID, err := result.LastInsertId()
 	if err != nil {
 		return 0, fmt.Errorf("failed to retrieve last inserted id: %w", err)
 	}
 
 	for _, artist := range release.Artists {
 		_, err = e.Exec(`
 			insert into release_artists (
 				release_id,
 				artist_id,
 				role,
 				sort_order
 			)
 			values (?, ?, ?, ?)
 			on conflict(release_id, artist_id, role) do update set
 				sort_order = excluded.sort_order
 		`, lastID, artist.ArtistID, artist.Role, artist.SortOrder)
 		if err != nil {
 			return 0, fmt.Errorf("failed to insert release artist: %w", err)
 		}
 	}
 
 	return domain.ReleaseID(lastID), nil
 }
 
 func SearchRelease(e Execer, releaseName string, limit, offset int64) (int, []domain.Release, error) {
 	releaseName = strings.TrimSpace(releaseName)
 	if releaseName == "" {
 		return 0, nil, nil
 	}
 
 	wildcard := "%" + releaseName + "%"
 
 	var totalRows int
 	err := e.QueryRow(`
 		select count(*)
 		from releases
 		where title like ? collate nocase
 		   or latin_title like ? collate nocase
 		order by title
 	`, wildcard, wildcard).Scan(&totalRows)
 	if err != nil {
 		return 0, nil, fmt.Errorf("failed to count total releases: %w", err)
 	}
 
 	rows, err := e.Query(`
 		select id, musicbrainz_release_id, title, latin_title, release_date, type
 		from releases
 		where title like ? collate nocase
 		   or latin_title like ? collate nocase
 		order by title
 		limit ? offset ?
 	`, wildcard, wildcard, limit, offset)
 	if err != nil {
 		return 0, nil, fmt.Errorf("failed to query releases: %w", err)
 	}
 	defer func() {
 		_ = rows.Close()
 	}()
 
 	var results []domain.Release
 	for rows.Next() {
 		var release domain.Release
 		var releaseDateStr string
 
 		if err := rows.Scan(&release.ID, &release.MusicBrainzReleaseID, &release.Title, &release.LatinTitle, &releaseDateStr, &release.Type); err != nil {
 			return 0, nil, fmt.Errorf("failed to scan release row: %w", err)
 		}
 
 		releaseDate, err := time.Parse(time.RFC3339, releaseDateStr)
 		if err != nil {
 			return 0, nil, fmt.Errorf("failed to parse release date string '%s': %w", releaseDateStr, err)
 		}
 
 		release.ReleaseDate = releaseDate
 
 		// fetch release_artists rows
 		var artistRows []releaseArtistRow
 
 		{
 			rows, err := e.Query(`
 				select artist_id, role, sort_order
 				from release_artists
 				where release_id = ?
 				order by sort_order
 			`, release.ID)
 			if err != nil {
 				return 0, []domain.Release{}, fmt.Errorf("failed to query release artists: %w", err)
 			}
 
 			for rows.Next() {
 				var r releaseArtistRow
 				if err := rows.Scan(&r.artistID, &r.role, &r.sortOrder); err != nil {
 					_ = rows.Close()
 					return 0, []domain.Release{}, fmt.Errorf("failed to scan release artist: %w", err)
 				}
 				artistRows = append(artistRows, r)
 			}
 			if err := rows.Err(); err != nil {
 				_ = rows.Close()
 				return 0, []domain.Release{}, fmt.Errorf("failed to iterate release artists: %w", err)
 			}
 			_ = rows.Close()
 		}
 
 		// fetch full artist records in one query
 		if len(artistRows) > 0 {
 			artistIDs := make([]any, len(artistRows))
 			for i, r := range artistRows {
 				artistIDs[i] = r.artistID
 			}
 			placeholders := strings.Repeat("?,", len(artistIDs))
 			placeholders = placeholders[:len(placeholders)-1]
 
 			artistMap := make(map[domain.ArtistID]domain.Artist, len(artistIDs))
 			{
 				rows, err := e.Query(`
 					select id, musicbrainz_artist_id, name, latin_name, disambiguation, bio
 					from artists
 					where id in (`+placeholders+`)
 				`, artistIDs...)
 				if err != nil {
 					return 0, []domain.Release{}, fmt.Errorf("failed to query artists: %w", err)
 				}
 
 				for rows.Next() {
 					var a domain.Artist
 					if err := rows.Scan(&a.ID, &a.MusicBrainzArtistID, &a.Name, &a.LatinName, &a.Disambiguation, &a.Bio); err != nil {
 						_ = rows.Close()
 						return 0, []domain.Release{}, fmt.Errorf("failed to scan artist: %w", err)
 					}
 					artistMap[a.ID] = a
 				}
 				if err := rows.Err(); err != nil {
 					_ = rows.Close()
 					return 0, []domain.Release{}, fmt.Errorf("failed to iterate artists: %w", err)
 				}
 				_ = rows.Close()
 			}
 
 			release.Artists = make([]domain.ReleaseArtist, len(artistRows))
 			for i, r := range artistRows {
 				release.Artists[i] = domain.ReleaseArtist{
 					Artist:    artistMap[r.artistID],
 					Role:      r.role,
 					SortOrder: r.sortOrder,
 				}
 			}
 		}
 
 		results = append(results, release)
 	}
 
 	return totalRows, results, rows.Err()
 }