song.go (11717B)
1 package db 2 3 import ( 4 "fmt" 5 "lyrics/internal/domain" 6 "strings" 7 "time" 8 ) 9 10 func Song(e Execer, id domain.SongID) (domain.Song, error) { 11 var song domain.Song 12 var releaseDateStr string 13 14 err := e.QueryRow(` 15 select id, musicbrainz_recording_id, title, latin_title, lyrics, release_date 16 from songs 17 where id = ? 18 `, id).Scan(&song.ID, &song.MusicBrainzRecordingID, &song.Title, &song.LatinTitle, &song.Lyrics, &releaseDateStr) 19 if err != nil { 20 return domain.Song{}, fmt.Errorf("failed to get song: %w", err) 21 } 22 23 song.ReleaseDate, err = time.Parse(time.RFC3339, releaseDateStr) 24 if err != nil { 25 return domain.Song{}, fmt.Errorf("failed to parse release_date: %w", err) 26 } 27 28 // fetch artists 29 { 30 rows, err := e.Query(` 31 select 32 a.id, 33 a.musicbrainz_artist_id, 34 a.name, 35 a.latin_name, 36 a.disambiguation, 37 a.bio, 38 sa.role, 39 sa.sort_order 40 from song_artists sa 41 join artists a on a.id = sa.artist_id 42 where sa.song_id = ? 43 order by sa.sort_order 44 `, id) 45 if err != nil { 46 return domain.Song{}, fmt.Errorf("failed to query song artists: %w", err) 47 } 48 49 for rows.Next() { 50 var artist domain.SongArtist 51 if err := rows.Scan( 52 &artist.Artist.ID, 53 &artist.Artist.MusicBrainzArtistID, 54 &artist.Artist.Name, 55 &artist.Artist.LatinName, 56 &artist.Artist.Disambiguation, 57 &artist.Artist.Bio, 58 &artist.Role, 59 &artist.SortOrder, 60 ); err != nil { 61 _ = rows.Close() 62 return domain.Song{}, fmt.Errorf("failed to scan song artist: %w", err) 63 } 64 song.Artists = append(song.Artists, artist) 65 } 66 if err := rows.Err(); err != nil { 67 _ = rows.Close() 68 return domain.Song{}, fmt.Errorf("failed to iterate song artists: %w", err) 69 } 70 _ = rows.Close() 71 } 72 73 // fetch releases 74 { 75 rows, err := e.Query(` 76 select 77 a.id, 78 a.musicbrainz_release_id, 79 a.title, 80 a.latin_title, 81 a.release_date, 82 sa.track_number 83 from song_releases sa 84 join releases a on a.id = sa.release_id 85 where sa.song_id = ? 86 order by sa.track_number 87 `, id) 88 if err != nil { 89 return domain.Song{}, fmt.Errorf("failed to query song releases: %w", err) 90 } 91 92 for rows.Next() { 93 var release domain.SongRelease 94 var releaseReleaseDateStr string 95 if err := rows.Scan( 96 &release.Release.ID, 97 &release.Release.MusicBrainzReleaseID, 98 &release.Release.Title, 99 &release.Release.LatinTitle, 100 &releaseReleaseDateStr, 101 &release.TrackNumber, 102 ); err != nil { 103 _ = rows.Close() 104 return domain.Song{}, fmt.Errorf("failed to scan song release: %w", err) 105 } 106 107 release.Release.ReleaseDate, err = time.Parse(time.RFC3339, releaseReleaseDateStr) 108 if err != nil { 109 _ = rows.Close() 110 return domain.Song{}, fmt.Errorf("failed to parse release release_date: %w", err) 111 } 112 113 song.Releases = append(song.Releases, release) 114 } 115 if err := rows.Err(); err != nil { 116 _ = rows.Close() 117 return domain.Song{}, fmt.Errorf("failed to iterate song releases: %w", err) 118 } 119 _ = rows.Close() 120 } 121 122 // fetch genres 123 { 124 rows, err := e.Query(` 125 select genre 126 from song_genres 127 where song_id = ? 128 `, id) 129 if err != nil { 130 return domain.Song{}, fmt.Errorf("failed to query song genres: %w", err) 131 } 132 133 for rows.Next() { 134 var g domain.Genre 135 if err := rows.Scan(&g); err != nil { 136 _ = rows.Close() 137 return domain.Song{}, fmt.Errorf("failed to scan song genre: %w", err) 138 } 139 song.Genres = append(song.Genres, g) 140 } 141 if err := rows.Err(); err != nil { 142 _ = rows.Close() 143 return domain.Song{}, fmt.Errorf("failed to iterate song genres: %w", err) 144 } 145 _ = rows.Close() 146 } 147 148 return song, nil 149 } 150 151 func RecentSongs(e Execer, limit int) ([]domain.Song, error) { 152 rows, err := e.Query(` 153 select id, musicbrainz_recording_id, title, latin_title, lyrics, release_date 154 from songs 155 order by created_at desc 156 limit ? 157 `, limit) 158 if err != nil { 159 return nil, fmt.Errorf("failed to query recent songs: %w", err) 160 } 161 162 var songs []domain.Song 163 for rows.Next() { 164 var song domain.Song 165 var releaseDateStr string 166 167 if err := rows.Scan(&song.ID, &song.MusicBrainzRecordingID, &song.Title, &song.LatinTitle, &song.Lyrics, &releaseDateStr); err != nil { 168 _ = rows.Close() 169 return nil, fmt.Errorf("failed to scan recent song: %w", err) 170 } 171 172 song.ReleaseDate, err = time.Parse(time.RFC3339, releaseDateStr) 173 if err != nil { 174 _ = rows.Close() 175 return nil, fmt.Errorf("failed to parse recent song release_date: %w", err) 176 } 177 178 songs = append(songs, song) 179 } 180 if err := rows.Err(); err != nil { 181 _ = rows.Close() 182 return nil, fmt.Errorf("failed to iterate recent songs: %w", err) 183 } 184 _ = rows.Close() 185 186 if len(songs) == 0 { 187 return nil, nil 188 } 189 190 songIDs := make([]any, len(songs)) 191 songByID := make(map[domain.SongID]*domain.Song, len(songs)) 192 for i := range songs { 193 songIDs[i] = songs[i].ID 194 songByID[songs[i].ID] = &songs[i] 195 } 196 placeholders := strings.Repeat("?,", len(songIDs)) 197 placeholders = placeholders[:len(placeholders)-1] 198 199 { 200 rows, err := e.Query(` 201 select 202 sa.song_id, 203 a.id, 204 a.musicbrainz_artist_id, 205 a.name, 206 a.latin_name, 207 a.disambiguation, 208 a.bio, 209 sa.role, 210 sa.sort_order 211 from song_artists sa 212 join artists a on a.id = sa.artist_id 213 where sa.song_id in (`+placeholders+`) 214 order by sa.song_id, sa.sort_order 215 `, songIDs...) 216 if err != nil { 217 return nil, fmt.Errorf("failed to query recent song artists: %w", err) 218 } 219 220 for rows.Next() { 221 var songID domain.SongID 222 var artist domain.SongArtist 223 if err := rows.Scan( 224 &songID, 225 &artist.Artist.ID, 226 &artist.Artist.MusicBrainzArtistID, 227 &artist.Artist.Name, 228 &artist.Artist.LatinName, 229 &artist.Artist.Disambiguation, 230 &artist.Artist.Bio, 231 &artist.Role, 232 &artist.SortOrder, 233 ); err != nil { 234 _ = rows.Close() 235 return nil, fmt.Errorf("failed to scan recent song artist: %w", err) 236 } 237 238 if song := songByID[songID]; song != nil { 239 song.Artists = append(song.Artists, artist) 240 } 241 } 242 if err := rows.Err(); err != nil { 243 _ = rows.Close() 244 return nil, fmt.Errorf("failed to iterate recent song artists: %w", err) 245 } 246 _ = rows.Close() 247 } 248 249 { 250 rows, err := e.Query(` 251 select 252 sa.song_id, 253 a.id, 254 a.musicbrainz_release_id, 255 a.title, 256 a.latin_title, 257 a.release_date, 258 sa.track_number 259 from song_releases sa 260 join releases a on a.id = sa.release_id 261 where sa.song_id in (`+placeholders+`) 262 order by sa.song_id, sa.track_number 263 `, songIDs...) 264 if err != nil { 265 return nil, fmt.Errorf("failed to query recent song releases: %w", err) 266 } 267 268 for rows.Next() { 269 var songID domain.SongID 270 var release domain.SongRelease 271 var releaseReleaseDateStr string 272 if err := rows.Scan( 273 &songID, 274 &release.Release.ID, 275 &release.Release.MusicBrainzReleaseID, 276 &release.Release.Title, 277 &release.Release.LatinTitle, 278 &releaseReleaseDateStr, 279 &release.TrackNumber, 280 ); err != nil { 281 _ = rows.Close() 282 return nil, fmt.Errorf("failed to scan recent song release: %w", err) 283 } 284 285 release.Release.ReleaseDate, err = time.Parse(time.RFC3339, releaseReleaseDateStr) 286 if err != nil { 287 _ = rows.Close() 288 return nil, fmt.Errorf("failed to parse recent release release_date: %w", err) 289 } 290 291 if song := songByID[songID]; song != nil { 292 song.Releases = append(song.Releases, release) 293 } 294 } 295 if err := rows.Err(); err != nil { 296 _ = rows.Close() 297 return nil, fmt.Errorf("failed to iterate recent song releases: %w", err) 298 } 299 _ = rows.Close() 300 } 301 302 return songs, nil 303 } 304 305 func InsertSong(e Execer, song domain.NewSong) error { 306 result, err := e.Exec(` 307 insert into songs ( 308 musicbrainz_recording_id, 309 title, 310 latin_title, 311 lyrics, 312 release_date, 313 created_at 314 ) 315 values (?, ?, ?, ?, ?, ?) 316 `, song.MusicBrainzRecordingID, song.Title, song.LatinTitle, song.Lyrics, song.ReleaseDate.Format(time.RFC3339), time.Now().Format(time.RFC3339)) 317 if err != nil { 318 return fmt.Errorf("failed to insert song: %w", err) 319 } 320 321 lastID, err := result.LastInsertId() 322 if err != nil { 323 return fmt.Errorf("failed to retrieve last inserted id: %w", err) 324 } 325 326 for _, artist := range song.Artists { 327 _, err = e.Exec(` 328 insert into song_artists ( 329 song_id, 330 artist_id, 331 role, 332 sort_order 333 ) 334 values (?, ?, ?, ?) 335 on conflict(song_id, artist_id, role) do update set 336 sort_order = excluded.sort_order 337 `, lastID, artist.ArtistID, artist.Role, artist.SortOrder) 338 if err != nil { 339 return fmt.Errorf("failed to insert song artist: %w", err) 340 } 341 } 342 343 for _, release := range song.Releases { 344 _, err = e.Exec(` 345 insert into song_releases ( 346 song_id, 347 release_id, 348 track_number 349 ) 350 values ( 351 ?, 352 ?, 353 coalesce(( 354 select max(track_number) 355 from song_releases 356 where release_id = ? 357 ), 0) + 1 358 ) 359 `, lastID, release.ReleaseID, release.ReleaseID) 360 if err != nil { 361 return fmt.Errorf("failed to insert song release: %w", err) 362 } 363 } 364 365 for _, genre := range song.Genres { 366 _, err = e.Exec(` 367 insert into song_genres ( 368 song_id, 369 genre 370 ) 371 values (?, ?) 372 `, lastID, genre) 373 if err != nil { 374 return fmt.Errorf("failed to insert song genre: %w", err) 375 } 376 } 377 378 return nil 379 } 380 381 func SearchSong(e Execer, songTitle string, limit, offset int64) (int, []domain.Song, error) { 382 songTitle = strings.TrimSpace(songTitle) 383 if songTitle == "" { 384 return 0, []domain.Song{}, nil 385 } 386 387 wildcard := "%" + songTitle + "%" 388 389 var totalRows int 390 err := e.QueryRow(` 391 select count(*) 392 from songs 393 where title like ? collate nocase 394 or latin_title like ? collate nocase 395 `, wildcard, wildcard).Scan(&totalRows) 396 if err != nil { 397 return 0, nil, fmt.Errorf("failed to count total songs: %w", err) 398 } 399 400 rows, err := e.Query(` 401 select id, musicbrainz_recording_id, title, latin_title, lyrics, release_date 402 from songs 403 where title like ? collate nocase 404 or latin_title like ? collate nocase 405 order by title 406 limit ? offset ? 407 `, wildcard, wildcard, limit, offset) 408 if err != nil { 409 return 0, nil, fmt.Errorf("failed to query songs: %w", err) 410 } 411 defer func() { 412 _ = rows.Close() 413 }() 414 415 var results []domain.Song 416 for rows.Next() { 417 var song domain.Song 418 var releaseDateStr string 419 420 if err := rows.Scan(&song.ID, &song.MusicBrainzRecordingID, &song.Title, &song.LatinTitle, &song.Lyrics, &releaseDateStr); err != nil { 421 return 0, []domain.Song{}, fmt.Errorf("failed to get song: %w", err) 422 } 423 424 song.ReleaseDate, err = time.Parse(time.RFC3339, releaseDateStr) 425 if err != nil { 426 return 0, []domain.Song{}, fmt.Errorf("failed to parse release_date: %w", err) 427 } 428 429 // fetch artists 430 { 431 rows, err := e.Query(` 432 select 433 a.id, 434 a.musicbrainz_artist_id, 435 a.name, 436 a.latin_name, 437 a.disambiguation, 438 a.bio, 439 sa.role, 440 sa.sort_order 441 from song_artists sa 442 join artists a on a.id = sa.artist_id 443 where sa.song_id = ? 444 order by sa.sort_order 445 `, song.ID) 446 if err != nil { 447 return 0, []domain.Song{}, fmt.Errorf("failed to query song artists: %w", err) 448 } 449 450 for rows.Next() { 451 var artist domain.SongArtist 452 if err := rows.Scan( 453 &artist.Artist.ID, 454 &artist.Artist.MusicBrainzArtistID, 455 &artist.Artist.Name, 456 &artist.Artist.LatinName, 457 &artist.Artist.Disambiguation, 458 &artist.Artist.Bio, 459 &artist.Role, 460 &artist.SortOrder, 461 ); err != nil { 462 _ = rows.Close() 463 return 0, []domain.Song{}, fmt.Errorf("failed to scan song artist: %w", err) 464 } 465 song.Artists = append(song.Artists, artist) 466 } 467 if err := rows.Err(); err != nil { 468 _ = rows.Close() 469 return 0, []domain.Song{}, fmt.Errorf("failed to iterate song artists: %w", err) 470 } 471 _ = rows.Close() 472 473 results = append(results, song) 474 } 475 } 476 477 return totalRows, results, nil 478 }