space-ace-go/db.go

package main

import (
	"database/sql"
	"fmt"
	"log"
	"os"
	"strconv"
	"strings"
	"time"

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

//
// Tables:
//
// settings(username, setting, value)
// scores(username, when, date, hand, won, run, score)
// monthly(month, username, days, hands_won, bestrun, score)
//

// Make sure the go-sqlite3 is here.  Keep the db related things together.

type DBData struct {
	DB        *sql.DB
	User      string
	LockValue string
}

func (db *DBData) Open(filename string) {
	var err error

	db.DB, err = sql.Open("sqlite3", filename)
	if err != nil {
		log.Panicln("Error Opening Sqlite3:", err)
	}

	// Create the tables (if needed)
	db.Create()
}

func (db *DBData) Close() {
	db.DB.Close()
}

const LOCK_USERNAME = ""
const LOCK_SETTING = "LOCK"
const LOCK_CLEAR = "0"

func (db *DBData) Create() {
	var SQLLines []string = []string{
		"CREATE TABLE IF NOT EXISTS settings( " +
			"username TEXT, setting TEXT, value TEXT, " +
			"PRIMARY KEY(username, setting));",
		"CREATE TABLE IF NOT EXISTS scores( " +
			"username TEXT, `when` INTEGER, date INTEGER, " +
			"hand INTEGER, won INTEGER, run INTEGER, " +
			" score INTEGER, " +
			"PRIMARY KEY(username, date, hand));",
		"CREATE TABLE IF NOT EXISTS monthly( " +
			"month INTEGER, username TEXT, days INTEGER, " +
			"hands_won INTEGER, bestrun INTEGER, " +
			"score INTEGER, " +
			"PRIMARY KEY(month, username) );",
	}
	for _, sql := range SQLLines {
		_, err := db.DB.Exec(sql)
		if err != nil {
			log.Panicln("DBData.Create:", err)
		}
	}

	// Make sure our settings LOCK value exists
	db.DB.Exec(
		"INSERT INTO settings(username, setting, value) "+
			"VALUES(?,?,?);",
		LOCK_USERNAME, LOCK_SETTING, LOCK_CLEAR)
	// An error here is OK.  It means the setting already exists.
}

// Lock the database (for Monthly maint)
func (db *DBData) Lock(timeout int) bool {
	var now time.Time = time.Now()
	var value string = fmt.Sprintf("%d,%d", os.Getpid(), now.Unix())

RetryLock:
	_, err := db.DB.Exec("UPDATE settings SET value=? WHERE username=? AND setting=? AND value=?;",
		value, LOCK_USERNAME, LOCK_SETTING, LOCK_CLEAR)
	if err != nil {
		log.Printf("Lock(%s) : %s\n", value, err)
	}
	row := db.DB.QueryRow("SELECT value FROM settings WHERE username=? AND setting=?;",
		LOCK_USERNAME, LOCK_SETTING)
	var result string
	err = row.Scan(&result)
	if err != nil {
		log.Println("Lock Scan", err)
	}
	// I need to do further checking here -- to make sure the lock isn't stale.

	if result == value {
		// Success! We've got the lock!
		log.Printf("Lock [%s]\n", value)
		db.LockValue = value
		return true
	}

	// We failed.  There's already a lock present.  Check if it is stale.
	var lockSet string
	var pos int
	var howOld time.Duration
	pos = strings.Index(result, ",")
	if pos != -1 {
		lockSet = result[pos+1:]
		var unixtime int64
		unixtime, err = strconv.ParseInt(lockSet, 10, 64)
		if err == nil {
			var lockTime time.Time = time.Unix(unixtime, 0)
			howOld = now.Sub(lockTime)
			log.Printf("Lock %s [%0.2f seconds old]\n", result, howOld.Seconds())
			if howOld.Seconds() > 60 {
				// Lock expired
				log.Printf("Lock %s [%0.2f seconds old] -- Expiring\n", result, howOld.Seconds())
				db.DB.Exec("UPDATE settings SET value=? WHERE username=? AND setting=? AND value=?;",
					LOCK_CLEAR, LOCK_USERNAME, LOCK_SETTING, result)
				goto RetryLock
			}
		}
	} else {
		log.Printf("Can't find , in [%s]\n", result)
	}

	time.Sleep(time.Duration(50) * time.Millisecond)
	howOld = time.Until(now)
	if int(-howOld.Seconds()) > timeout {
		return false
	}
	goto RetryLock
}

func (db *DBData) Unlock() bool {
	if db.LockValue == "" {
		// Nothing to unlock -- we didn't get the lock.
		return true
	}
	_, err := db.DB.Exec("UPDATE settings SET value=? WHERE username=? AND setting=? AND value=?;",
		LOCK_CLEAR, LOCK_USERNAME, LOCK_SETTING, db.LockValue)
	if err != nil {
		log.Printf("Unlock(%s) : %s\n", db.LockValue, err)
	}
	row := db.DB.QueryRow("SELECT value FROM settings WHERE username=? AND setting=?;",
		LOCK_USERNAME, LOCK_SETTING)
	var result string
	err = row.Scan(&result)
	if err != nil {
		log.Println("Unlock Scan", err)
	}
	if result != LOCK_CLEAR {
		log.Printf("Unlock Failed (LockValue %s) Result %s (should be %s)\n", db.LockValue, result, LOCK_CLEAR)
	} else {
		log.Println("Unlocked.")
	}
	return result == LOCK_CLEAR
}

func (db *DBData) GetSetting(setting string, ifMissing string) string {
	row := db.DB.QueryRow("SELECT value FROM settings WHERE username=? AND setting=?;",
		db.User, setting)
	var value string
	// log.Printf("row: %#v\n", row)
	err := row.Scan(&value)
	if err != nil {
		return ifMissing
	}
	return value
}

func (db *DBData) SetSetting(setting string, value string) {
	_, err := db.DB.Exec("REPLACE INTO settings(username, setting, value) VALUES(?,?,?);",
		db.User, setting, value)
	if err != nil {
		log.Panicln("Query SetSetting", err)
	}
	// log.Printf("SetSetting %s %s = %s\n", db.User, setting, value)
}

func (db *DBData) SaveScore(when YMD, date YMD, hand int, won int, run int, score int) {
	_, err := db.DB.Exec("INSERT INTO scores(username, `when`, date, hand, won, run, score) VALUES(?,?,?,?,?,?,?);",
		db.User, int(when), int(date), hand, won, run, score)
	if err != nil {
		log.Println("SaveScore", err)
		// When we play a hand we've already played:
		// panic: SaveScore UNIQUE constraint failed: scores.username, scores.date, scores.hand
	}
}

func (db *DBData) HandsPlayedOnDay(day YMD) int {
	row := db.DB.QueryRow("SELECT COUNT(*) FROM scores WHERE username=? AND date=?;",
		db.User, int(day))
	var value int
	err := row.Scan(&value)
	if err != nil {
		return 0
	}
	return value
}

/*
WhenPlayed = GetPlayed  (as far as I can tell...)
*/
func (db *DBData) WhenPlayed() map[YMD]int {
	var result map[YMD]int = make(map[YMD]int)

	rows, err := db.DB.Query("SELECT date, COUNT(hand) FROM scores WHERE username=? GROUP BY date;",
		db.User)
	if err != nil {
		log.Println("GetPlayed", err)
		return result
	}
	defer rows.Close()

	for rows.Next() {
		var date YMD
		var hands int

		if err := rows.Scan(&date, &hands); err != nil {
			log.Println("GetPlayed Scan", err)
			return result
		}

		result[date] = hands
	}

	if err = rows.Err(); err != nil {
		log.Println("GetPlayed rows.Err", err)
	}

	return result
}

type MonthUser struct {
	Date     YM
	Username string
}

type MonthStats struct {
	Days      int
	Hands_Won int
	Run       int
	Score     int
}

func (db *DBData) ExpireScores(month_first YMD) {
	// step 1: acquire lock

	l := db.Lock(5)

	if !l {
		log.Println("Lock failed")
		return
	}

	defer db.Unlock()

	var Monthly map[MonthUser]MonthStats = make(map[MonthUser]MonthStats)
	rows, err := db.DB.Query("SELECT date, username, SUM(score), SUM(won), MAX(run) "+
		"FROM scores WHERE date < ? "+
		"GROUP BY date, username ORDER BY date, SUM(score) DESC;",
		month_first)
	if err != nil {
		log.Println("ExpireScores", err)
		return
	}
	defer rows.Close()

	for rows.Next() {
		var mu MonthUser
		var score int
		var won int
		var run int
		var scoreDate YMD
		if err := rows.Scan(&scoreDate, &mu.Username, &score, &won, &run); err != nil {
			log.Println("ExpireScores Scan", err)
			return
		}

		mu.Date = scoreDate.YM()
		// FirstOfMonthDateUnix(&mu.Date)

		if mumap, found := Monthly[mu]; found {
			// entry found
			mumap.Days++
			mumap.Score += score
			mumap.Hands_Won += won
			if run > mumap.Run {
				mumap.Run = run
			}
			Monthly[mu] = mumap
		} else {
			// new entry
			var ms MonthStats = MonthStats{Days: 1,
				Score:     score,
				Hands_Won: won,
				Run:       run,
			}
			Monthly[mu] = ms
		}
	}

	rows.Close()

	if len(Monthly) == 0 {
		// Nothing to do
		return
	}

	// Begin transaction
	tx, err := db.DB.Begin()
	if err != nil {
		log.Println("ExpireScores Begin", err)
		return
	}

	tx.Exec(
		"DELETE FROM scores WHERE date < ?;",
		month_first)

	for mu, ms := range Monthly {
		_, err := tx.Exec("INSERT INTO monthly(month, username, days, hands_won, bestrun, score) "+
			"VALUES(?,?,?,?,?,?);",
			mu.Date, mu.Username, ms.Days, ms.Hands_Won, ms.Run, ms.Score)
		if err != nil {
			log.Println("ExpireScores Insert", err)
			tx.Rollback()
			return
		}
	}

	// End transaction / Commit
	err = tx.Commit()
	if err != nil {
		log.Println("ExpireScores Commit", err)
	}

}

type ScoresDetails struct {
	User  string
	Date  int64
	Hand  int
	Won   int
	Score int
}

func (db *DBData) GetScoresOnDay(date int64) []ScoresDetails {
	var result []ScoresDetails
	rows, err := db.DB.Query("SELECT username, date, hand, won, score FROM SCORES "+
		"WHERE date=? ORDER BY username, hand;",
		date)
	if err != nil {
		log.Println("GetScoresOnDay", date, err)
		return result
	}
	defer rows.Close()

	for rows.Next() {
		var sd ScoresDetails

		if err := rows.Scan(&sd.User, &sd.Date, &sd.Hand, &sd.Won, &sd.Score); err != nil {
			log.Println("GetScoresOnDay Scan", err)
			return result
		}
		result = append(result, sd)
	}

	if err = rows.Err(); err != nil {
		log.Println("GetScoresOnDay rows.Err", err)
	}
	return result
}

type ScoresData struct {
	Date  int64
	User  string
	Score int
	Won   int
}

func (db *DBData) GetScores(limit int) []ScoresData {
	var result []ScoresData

	rows, err := db.DB.Query("SELECT date, username, SUM(score), SUM(won) "+
		"FROM SCORES GROUP BY date, username ORDER BY SUM(score) DESC LIMIT ?;",
		limit)
	if err != nil {
		log.Println("GetScores", err)
		return result
	}
	defer rows.Close()

	for rows.Next() {
		var sd ScoresData

		if err := rows.Scan(&sd.Date, &sd.User, &sd.Score, &sd.Won); err != nil {
			log.Println("GetScores Scan", err)
			return result
		}
		result = append(result, sd)
	}

	if err = rows.Err(); err != nil {
		log.Println("GetScores rows.Err", err)
	}
	return result
}

type MonthlyData struct {
	Date      int64
	User      string
	Days      int
	Hands_Won int
	Best_Run  int
	Score     int
}

func (db *DBData) GetMonthlyScores(limit int) []MonthlyData {
	var result []MonthlyData

	rows, err := db.DB.Query("SELECT month, username, days, hands_won, bestrun, score "+
		"FROM monthly ORDER BY score DESC LIMIT ?;",
		limit)
	if err != nil {
		log.Println("GetMonthlyScores", err)
		return result
	}
	defer rows.Close()

	for rows.Next() {
		var md MonthlyData

		if err := rows.Scan(&md.Date, &md.User, &md.Days, &md.Hands_Won, &md.Best_Run, &md.Score); err != nil {
			log.Println("GetMonthlyScores Scan", err)
			return result
		}
		result = append(result, md)
	}

	if err = rows.Err(); err != nil {
		log.Println("GetMonthlyScores rows.Err", err)
	}
	return result
}