GoRacerr/database.go

package main

import (
	"context"
	"database/sql"
	"log"
	"time"

	sq "github.com/Masterminds/squirrel"
	_ "github.com/mattn/go-sqlite3"
)

type Database interface {
	CreatePreRace(*Race) error
	DeletePreRace(int) error
	UpdatePreRace(int) error
	GetPreRaceByID(int) (*Race, error)
	GetPreRaces() ([]*Race, error)
}

type SQLiteDatabase struct {
	Db *sql.DB
}

func NewSQLiteDatabase(cfg Config) (*SQLiteDatabase, error) {
	db, err := sql.Open("sqlite3", cfg.DBName)
	if err != nil {
		return nil, err
	}
	log.Println("Opening SQLite database")

	_, err = db.ExecContext(
		context.Background(),
		`CREATE TABLE IF NOT EXISTS "races" (
		"id"	INTEGER NOT NULL UNIQUE,
		"torrent_name"	TEXT NOT NULL,
		"category"	TEXT,
		"indexer"	TEXT,
		"type"	TEXT,
		"title"	TEXT,
		"season"	TEXT,
		"episode"	TEXT,
		"year"	TEXT,
		"resolution"	TEXT,
		"source"	TEXT,
		"torrent_url"	TEXT,
		"torrent_file"	TEXT,
		"nfo_file"	TEXT,
		"original_path"	TEXT,
		"won"	INTEGER,
		"prerace"	INTEGER,
		"prerace_added_at"	INTEGER,
		"race_started_at"	INTEGER,
		"race_ended_at"	INTEGER,
		PRIMARY KEY("ID" AUTOINCREMENT)
		);
		CREATE TABLE IF NOT EXISTS"releases" (
		"id"	INTEGER NOT NULL UNIQUE,
		"torrent_name"	TEXT NOT NULL,
		"title"	TEXT NOT NULL UNIQUE,
		"original_title"	TEXT,
		"tmdb_id"	INTEGER,
		"overview"	TEXT,
		"release_date"	TEXT,
		"original_language"	TEXT,
		"year"	TEXT,
		"season"	TEXT,
		"episode"	TEXT,
		"type"	TEXT,
		PRIMARY KEY("id")
		)`,
	)

	if err != nil {
		log.Panicln("Cannot create table", err)
	}

	return &SQLiteDatabase{
		Db: db,
	}, nil
}

// Prerace

func (s *SQLiteDatabase) CreateRace(r *Race) error {

	// Search if release has been raced already

	if r.Type == "movie" || r.Type == "episode" {
		queryBuilder := sq.
			Insert("races").
			Columns("torrent_name", "category", "indexer", "type", "title", "season", "episode", "year", "resolution", "source",
				"torrent_url", "torrent_file", "nfo_file", "original_path", "won", "prerace", "prerace_added_at", "race_started_at, race_ended_at").
			Values(r.TorrentName, r.Category, r.Indexer, r.Type, r.Title, r.Season, r.Episode, r.Year, r.Resolution, r.Source, r.TorrentURL, "", "", "", r.Won, r.PreRace, time.Time.Unix(time.Now()), 0, 0).
			Suffix("RETURNING id").RunWith(s.Db)

		err := queryBuilder.QueryRow().Scan(&r.ID)
		if err != nil {
			log.Println("Error while searching for races: ", err)
		}
	}

	return nil
}

func (s *SQLiteDatabase) DeletePreRace(*Race) error {
	return nil
}

func (s *SQLiteDatabase) UpdatePreRace(*Race) error {
	return nil
}

func (s *SQLiteDatabase) GetPreRaceByID(*Race) error {
	return nil
}

func (s *SQLiteDatabase) GetPreRaces(*Race) error {
	return nil
}

func (s *SQLiteDatabase) FindRaces() ([]Race, error) {
	var races []Race
	rows, err := s.Db.Query("SELECT id, torrent_name, category, indexer, type, title, season, episode, year, resolution, source, torrent_url, torrent_file,	nfo_file, original_path, won, prerace, prerace_added_at, race_started_at, race_ended_at FROM races")
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	for rows.Next() {
		var race Race
		err := rows.Scan(&race.ID, &race.TorrentName, &race.Category, &race.Indexer, &race.Type, &race.Title, &race.Season,
			&race.Episode, &race.Year, &race.Resolution, &race.Source, &race.TorrentURL, &race.TorrentFile,
			&race.NFOFile, &race.OriginalPath, &race.Won, &race.PreRace, &race.PreRaceStarted, &race.RaceStarted, &race.RaceEnded)
		if err != nil {
			return nil, err
		}
		races = append(races, race)
	}

	return races, err

}

func (s *SQLiteDatabase) FindRace(releaseName string, indexer string) Race {
	var race Race
	s.Db.QueryRow("SELECT * FROM races WHERE torrent_name=? AND indexer=?", releaseName, indexer).
		Scan(&race.ID, &race.TorrentName, &race.Category, &race.Indexer, &race.Type, &race.Title, &race.Season,
			&race.Episode, &race.Year, &race.Resolution, &race.Source, &race.TorrentURL, &race.TorrentFile,
			&race.NFOFile, &race.OriginalPath, &race.Won, &race.PreRace, &race.PreRaceStarted, &race.RaceStarted, &race.RaceEnded)
	return race
}

func (s *SQLiteDatabase) FindRaceByID(id string) Race {
	var race Race
	s.Db.QueryRow("SELECT * FROM races WHERE id=?", id).
		Scan(&race.ID, &race.TorrentName, &race.Category, &race.Indexer, &race.Type, &race.Title, &race.Season,
			&race.Episode, &race.Year, &race.Resolution, &race.Source, &race.TorrentURL, &race.TorrentFile,
			&race.NFOFile, &race.OriginalPath, &race.Won, &race.PreRace, &race.PreRaceStarted, &race.RaceStarted, &race.RaceEnded)
	return race
}

// Release

func (s *SQLiteDatabase) CreateRelease(rls Release) error {
	if rls.TMDbID != 0 {
		queryBuilder := sq.Insert("releases").
			Columns("torrent_name", "title", "original_title", "tmdb_id", "overview", "release_date", "original_language", "year", "season", "episode", "type").
			Values(rls.TorrentName, rls.Title, rls.OriginalTitle, rls.TMDbID, rls.Overview, rls.ReleaseDate, rls.OriginalLanguage, rls.Year, rls.Season, rls.Episode, rls.Type).
			Suffix("RETURNING id").RunWith(s.Db)

		err := queryBuilder.QueryRow().Scan(&rls.ID)
		if err != nil {
			return err
		}
	}
	return nil
}

func (s *SQLiteDatabase) FindRelease(rls Release) Release {
	var release Release
	s.Db.QueryRow("SELECT ID, torrent_name, title, original_title, tmdb_id, release_date, original_language, year, season, episode, type FROM releases WHERE title=?",
		rls.Title).Scan(&release.ID, &release.TorrentName, &release.Title, &release.OriginalTitle, &release.TMDbID,
		&release.ReleaseDate, &release.OriginalLanguage, &release.Year, &release.Season, &release.Episode, &release.Type)

	return release
}

func (s *SQLiteDatabase) FindReleases() ([]Release, error) {
	var releases []Release
	rows, err := s.Db.Query("SELECT id, torrent_name, title, original_title, tmdb_id, overview, release_date, original_language, year, season, episode, type FROM releases")
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	for rows.Next() {
		var release Release
		err := rows.Scan(&release.ID, &release.TorrentName, &release.Title, &release.OriginalTitle, &release.TMDbID, &release.Overview, &release.ReleaseDate,
			&release.OriginalLanguage, &release.Year, &release.Season, &release.Episode, &release.Type)
		if err != nil {
			return nil, err
		}
		releases = append(releases, release)
	}

	return releases, err

}