227 lines
6.3 KiB
Go
227 lines
6.3 KiB
Go
package repository
|
|
|
|
import (
|
|
"butterfliu/internal/model"
|
|
"database/sql"
|
|
"time"
|
|
)
|
|
|
|
type ExistingMediaFile struct {
|
|
Path string
|
|
MediaFileID int
|
|
HasSong bool
|
|
FileSize int64
|
|
FileMtimeNs int64
|
|
LastSeenAt time.Time
|
|
}
|
|
|
|
type MediaRepository struct {
|
|
db *sql.DB
|
|
}
|
|
|
|
func NewMediaRepository(db *sql.DB) *MediaRepository {
|
|
return &MediaRepository{db: db}
|
|
}
|
|
|
|
func (r *MediaRepository) Get(id int) (model.MediaFile, error) {
|
|
var m model.MediaFile
|
|
err := r.db.QueryRow(`
|
|
SELECT id, path, library_id,
|
|
COALESCE(file_size, 0), COALESCE(file_mtime_ns, 0), COALESCE(format, ''),
|
|
COALESCE(bit_rate, 0), COALESCE(sample_rate, 0),
|
|
COALESCE(last_seen_at, '1970-01-01T00:00:00Z'),
|
|
COALESCE(created_at, '1970-01-01T00:00:00Z'),
|
|
COALESCE(updated_at, '1970-01-01T00:00:00Z')
|
|
FROM media_files WHERE id = ?
|
|
`, id).Scan(
|
|
&m.ID, &m.Path, &m.LibraryID,
|
|
&m.FileSize, &m.FileMtimeNs, &m.Format,
|
|
&m.BitRate, &m.SampleRate, &m.LastSeenAt,
|
|
&m.CreatedAt, &m.UpdatedAt,
|
|
)
|
|
if err != nil {
|
|
return model.MediaFile{}, err
|
|
}
|
|
return m, nil
|
|
}
|
|
|
|
func (r *MediaRepository) GetByPath(path string) (model.MediaFile, error) {
|
|
row := r.db.QueryRow(`
|
|
SELECT id, path, library_id,
|
|
COALESCE(file_size, 0), COALESCE(file_mtime_ns, 0), COALESCE(format, ''),
|
|
COALESCE(bit_rate, 0), COALESCE(sample_rate, 0),
|
|
COALESCE(last_seen_at, '1970-01-01T00:00:00Z'),
|
|
COALESCE(created_at, '1970-01-01T00:00:00Z'),
|
|
COALESCE(updated_at, '1970-01-01T00:00:00Z')
|
|
FROM media_files WHERE path = ?
|
|
`)
|
|
var mediaFile model.MediaFile
|
|
if err := row.Scan(
|
|
&mediaFile.ID, &mediaFile.Path, &mediaFile.LibraryID,
|
|
&mediaFile.FileSize, &mediaFile.FileMtimeNs, &mediaFile.Format,
|
|
&mediaFile.BitRate, &mediaFile.SampleRate, &mediaFile.LastSeenAt,
|
|
&mediaFile.CreatedAt, &mediaFile.UpdatedAt,
|
|
); err != nil {
|
|
return model.MediaFile{}, err
|
|
}
|
|
return mediaFile, nil
|
|
}
|
|
|
|
func (r *MediaRepository) Create(mediaFile model.MediaFile) (model.MediaFile, error) {
|
|
result, err := r.db.Exec(
|
|
`INSERT INTO media_files (path, library_id, file_size, file_mtime_ns, format, bit_rate, sample_rate, last_seen_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,
|
|
mediaFile.Path,
|
|
mediaFile.LibraryID,
|
|
mediaFile.FileSize,
|
|
mediaFile.FileMtimeNs,
|
|
mediaFile.Format,
|
|
mediaFile.BitRate,
|
|
mediaFile.SampleRate,
|
|
mediaFile.LastSeenAt,
|
|
)
|
|
if err != nil {
|
|
return model.MediaFile{}, err
|
|
}
|
|
|
|
id, err := result.LastInsertId()
|
|
if err != nil {
|
|
return model.MediaFile{}, err
|
|
}
|
|
|
|
mediaFile.ID = int(id)
|
|
return mediaFile, nil
|
|
}
|
|
|
|
func (r *MediaRepository) ListExistingByLibrary(libraryID int) ([]ExistingMediaFile, error) {
|
|
rows, err := r.db.Query(`
|
|
SELECT mf.path, mf.id, CASE WHEN s.id IS NULL THEN 0 ELSE 1 END AS has_song,
|
|
COALESCE(mf.file_size, 0), COALESCE(mf.file_mtime_ns, 0),
|
|
COALESCE(mf.last_seen_at, '1970-01-01T00:00:00Z')
|
|
FROM media_files mf
|
|
LEFT JOIN songs s ON s.media_file_id = mf.id
|
|
WHERE mf.library_id = ?
|
|
`, libraryID)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
defer rows.Close()
|
|
|
|
existing := []ExistingMediaFile{}
|
|
for rows.Next() {
|
|
var item ExistingMediaFile
|
|
var hasSong int
|
|
if err := rows.Scan(&item.Path, &item.MediaFileID, &hasSong, &item.FileSize, &item.FileMtimeNs, &item.LastSeenAt); err != nil {
|
|
return nil, err
|
|
}
|
|
item.HasSong = hasSong == 1
|
|
existing = append(existing, item)
|
|
}
|
|
return existing, nil
|
|
}
|
|
|
|
func (r *MediaRepository) UpdateMetadata(mediaFile model.MediaFile) error {
|
|
_, err := r.db.Exec(
|
|
`UPDATE media_files SET file_size = ?, file_mtime_ns = ?, format = ?, bit_rate = ?, sample_rate = ?, last_seen_at = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?`,
|
|
mediaFile.FileSize,
|
|
mediaFile.FileMtimeNs,
|
|
mediaFile.Format,
|
|
mediaFile.BitRate,
|
|
mediaFile.SampleRate,
|
|
mediaFile.LastSeenAt,
|
|
mediaFile.ID,
|
|
)
|
|
return err
|
|
}
|
|
|
|
func (r *MediaRepository) TouchLastSeen(mediaFileID int, seenAt time.Time) error {
|
|
_, err := r.db.Exec(
|
|
`UPDATE media_files SET last_seen_at = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?`,
|
|
seenAt,
|
|
mediaFileID,
|
|
)
|
|
return err
|
|
}
|
|
|
|
func (r *MediaRepository) Delete(id int) error {
|
|
_, err := r.db.Exec("DELETE FROM media_files WHERE id = ?", id)
|
|
return err
|
|
}
|
|
|
|
func (r *MediaRepository) GetSongsByLibraryWithDetails(libraryID int) ([]model.SongDetail, error) {
|
|
rows, err := r.db.Query(`
|
|
SELECT s.id, s.title, a.name as artist_name, al.title as album_title, s.duration, mf.path
|
|
FROM songs s
|
|
INNER JOIN media_files mf ON s.media_file_id = mf.id
|
|
INNER JOIN artists a ON s.artist_id = a.id
|
|
INNER JOIN albums al ON s.album_id = al.id
|
|
WHERE mf.library_id = ?
|
|
ORDER BY s.title
|
|
`, libraryID)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
defer rows.Close()
|
|
|
|
songs := []model.SongDetail{}
|
|
for rows.Next() {
|
|
var song model.SongDetail
|
|
if err := rows.Scan(&song.ID, &song.Title, &song.Artist, &song.Album, &song.Duration, &song.Path); err != nil {
|
|
return nil, err
|
|
}
|
|
songs = append(songs, song)
|
|
}
|
|
return songs, nil
|
|
}
|
|
|
|
func (r *MediaRepository) GetSongsByArtistWithDetails(artistID int) ([]model.SongDetail, error) {
|
|
rows, err := r.db.Query(`
|
|
SELECT s.id, s.title, a.name as artist_name, al.title as album_title, s.duration, mf.path
|
|
FROM songs s
|
|
INNER JOIN media_files mf ON s.media_file_id = mf.id
|
|
INNER JOIN artists a ON s.artist_id = a.id
|
|
INNER JOIN albums al ON s.album_id = al.id
|
|
WHERE s.artist_id = ?
|
|
ORDER BY s.title
|
|
`, artistID)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
defer rows.Close()
|
|
|
|
songs := []model.SongDetail{}
|
|
for rows.Next() {
|
|
var song model.SongDetail
|
|
if err := rows.Scan(&song.ID, &song.Title, &song.Artist, &song.Album, &song.Duration, &song.Path); err != nil {
|
|
return nil, err
|
|
}
|
|
songs = append(songs, song)
|
|
}
|
|
return songs, nil
|
|
}
|
|
|
|
func (r *MediaRepository) GetSongsByAlbumWithDetails(albumID int) ([]model.SongDetail, error) {
|
|
rows, err := r.db.Query(`
|
|
SELECT s.id, s.title, a.name as artist_name, al.title as album_title, s.duration, mf.path
|
|
FROM songs s
|
|
INNER JOIN media_files mf ON s.media_file_id = mf.id
|
|
INNER JOIN artists a ON s.artist_id = a.id
|
|
INNER JOIN albums al ON s.album_id = al.id
|
|
WHERE s.album_id = ?
|
|
ORDER BY s.title
|
|
`, albumID)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
defer rows.Close()
|
|
|
|
songs := []model.SongDetail{}
|
|
for rows.Next() {
|
|
var song model.SongDetail
|
|
if err := rows.Scan(&song.ID, &song.Title, &song.Artist, &song.Album, &song.Duration, &song.Path); err != nil {
|
|
return nil, err
|
|
}
|
|
songs = append(songs, song)
|
|
}
|
|
return songs, nil
|
|
}
|