dwelling-radio/internal/statistics/db/sqlite/db.go

package sqlite

import (
	"database/sql"
	"dwelling-radio/internal/statistics"
	_ "embed"
	"fmt"

	_ "github.com/mattn/go-sqlite3"
	"github.com/pkg/errors"
)

const dbDateFormat = "2006-01-02 15:04:05.999"

var (
	//go:embed queries/schema.sql
	querySchema string

	//go:embed queries/song_add.sql
	querySongAdd string

	//go:embed queries/history_add.sql
	queryHistoryAdd string

	//go:embed queries/last_n_songs.sql
	queryLastNSongs string
	//go:embed queries/most_popular_songs.sql
	queryMostPopularSongs string
	//go:embed queries/most_simultaneous_listeners.sql
	queryMostSimultaneousListeners string
)

type SQLiteStatistics struct {
	statistics.BaseStatistics
}

func New(path string) (statistics.Statistics, error) {
	db, err := sql.Open("sqlite3", fmt.Sprintf("file:%s?_journal=WAL&_mutex=full", path))
	if err != nil {
		return nil, err
	}

	stats := &SQLiteStatistics{
		BaseStatistics: statistics.BaseStatistics{
			Db: db, DbDateFormat: dbDateFormat}}

	db.Exec("PRAGMA foreign_keys = ON;")

	_, err = db.Exec(querySchema)
	if err != nil {
		return nil, errors.Wrap(err,
			statistics.ErrPrepareStmt{Name: "initial schema"}.Error())
	}

	stats.BaseStatistics.StmtSongAdd, err = db.Prepare(querySongAdd)
	if err != nil {
		return nil, errors.Wrap(err,
			statistics.ErrPrepareStmt{Name: "song_add"}.Error())
	}

	stats.BaseStatistics.StmtHistoryAdd, err = db.Prepare(queryHistoryAdd)
	if err != nil {
		return nil, errors.Wrap(err,
			statistics.ErrPrepareStmt{Name: "history_add"}.Error())
	}

	stats.BaseStatistics.StmtLastNSongs, err = db.Prepare(queryLastNSongs)
	if err != nil {
		return nil, errors.Wrap(err,
			statistics.ErrPrepareStmt{Name: "last N songs"}.Error())
	}

	stats.BaseStatistics.StmtMostPopularSongs, err = db.Prepare(queryMostPopularSongs)
	if err != nil {
		return nil, errors.Wrap(err,
			statistics.ErrPrepareStmt{Name: "most popular song"}.Error())
	}

	stats.BaseStatistics.StmtMostSimultaneousListeners, err = db.Prepare(queryMostSimultaneousListeners)
	if err != nil {
		return nil, errors.Wrap(err,
			statistics.ErrPrepareStmt{Name: "most simultaneous listeners"}.Error())
	}

	return stats, nil
}

func (s *SQLiteStatistics) Close() error {
	return s.BaseStatistics.Close()
}