lyrics

db.go (4781B)

---

 package db
 
 import (
 	"context"
 	"database/sql"
 	"fmt"
 	"log/slog"
 	"lyrics/internal/log"
 	"strings"
 
 	_ "github.com/mattn/go-sqlite3"
 )
 
 type DB struct {
 	*sql.DB
 	logger *slog.Logger
 }
 
 type Execer interface {
 	Query(query string, args ...any) (*sql.Rows, error)
 	QueryContext(ctx context.Context, query string, args ...any) (*sql.Rows, error)
 	QueryRow(query string, args ...any) *sql.Row
 	QueryRowContext(ctx context.Context, query string, args ...any) *sql.Row
 	Exec(query string, args ...any) (sql.Result, error)
 	ExecContext(ctx context.Context, query string, args ...any) (sql.Result, error)
 	Prepare(query string) (*sql.Stmt, error)
 	PrepareContext(ctx context.Context, query string) (*sql.Stmt, error)
 }
 
 func New(ctx context.Context, dbPath string) (*DB, error) {
 	// https://github.com/mattn/go-sqlite3#connection-string
 	opts := []string{
 		"_foreign_keys=1",
 		"_journal_mode=WAL",
 		"_synchronous=NORMAL",
 		"_auto_vacuum=incremental",
 	}
 
 	logger := log.FromContext(ctx)
 	logger = log.SubLogger(logger, "db")
 
 	db, err := sql.Open("sqlite3", dbPath+"?"+strings.Join(opts, "&"))
 	if err != nil {
 		return nil, fmt.Errorf("failed to open db: %w", err)
 	}
 
 	conn, err := db.Conn(ctx)
 	if err != nil {
 		return nil, err
 	}
 	defer conn.Close()
 
 	logger.Debug("creating tables")
 
 	tx, err := conn.BeginTx(ctx, nil)
 	if err != nil {
 		return nil, err
 	}
 	defer tx.Rollback()
 
 	_, err = tx.ExecContext(ctx, `
 		create table if not exists songs (
 			id integer primary key autoincrement,
 			musicbrainz_recording_id text,
 			title text not null,
 			latin_title text,
 			lyrics text not null,
 			release_date text not null,
 			created_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now'))
 		);
 	`)
 	if err != nil {
 		return nil, fmt.Errorf("failed to create songs table: %w", err)
 	}
 
 	_, err = tx.ExecContext(ctx, `
 		create table if not exists artists (
 			id integer primary key autoincrement,
 			musicbrainz_artist_id text,
 			name text not null,
 			latin_name text,
 			disambiguation text,
 			bio text,
 			created_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now'))
 		);
 	`)
 	if err != nil {
 		return nil, fmt.Errorf("failed to create artists table: %w", err)
 	}
 
 	_, err = tx.ExecContext(ctx, `
 		create table if not exists releases (
 			id integer primary key autoincrement,
 			musicbrainz_release_id text,
 			title text not null,
 			latin_title text,
 			release_date text not null,
 			-- album, single, ep, other
 			type text not null,
 			created_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
 
 			unique(title)
 		);
 	`)
 	if err != nil {
 		return nil, fmt.Errorf("failed to create releases table: %w", err)
 	}
 
 	_, err = tx.ExecContext(ctx, `
 		create table if not exists release_artists (
 			release_id integer not null,
 			artist_id integer not null,
 			role text not null,
 			sort_order integer not null,
 
 			unique(release_id, sort_order),
 			foreign key (release_id) references releases(id) on delete cascade,
 			foreign key (artist_id) references artists(id) on delete cascade,
 			primary key (release_id, artist_id, role)
 		);
 	`)
 	if err != nil {
 		return nil, fmt.Errorf("failed to create release_artists table: %w", err)
 	}
 
 	_, err = tx.ExecContext(ctx, `
 		create table if not exists song_artists (
 			song_id integer not null,
 			artist_id integer not null,
 			role text not null,
 			sort_order integer not null,
 
 			unique(song_id, sort_order),
 			unique(song_id, role),
 			foreign key (song_id) references songs(id) on delete cascade,
 			foreign key (artist_id) references artists(id) on delete cascade,
 			primary key (song_id, artist_id, role)
 		);
 	`)
 	if err != nil {
 		return nil, fmt.Errorf("failed to create song_artists table: %w", err)
 	}
 
 	_, err = tx.ExecContext(ctx, `
 		create table if not exists song_genres (
 			song_id integer not null,
 			genre text not null,
 
 			foreign key (song_id) references songs(id) on delete cascade,
 			primary key (song_id, genre)
 		);
 	`)
 	if err != nil {
 		return nil, fmt.Errorf("failed to create song_genres table: %w", err)
 	}
 
 	_, err = tx.ExecContext(ctx, `
 		create table if not exists song_releases (
 			song_id integer not null,
 			release_id integer not null,
 			track_number integer not null,
 
 			unique(release_id, track_number),
 			foreign key (song_id) references songs(id) on delete cascade,
 			foreign key (release_id) references releases(id) on delete cascade,
 			primary key (song_id, release_id)
 		);
 	`)
 	if err != nil {
 		return nil, fmt.Errorf("failed to create song_releases table: %w", err)
 	}
 
 	if err = tx.Commit(); err != nil {
 		return nil, fmt.Errorf("failed to commit db create operations: %w", err)
 	}
 
 	logger.Debug("successfully created tables")
 
 	return &DB{db, logger}, nil
 }
 
 func (d *DB) Close() error {
 	return d.DB.Close()
 }