space-ace-go/db.go

package main

import (
	"database/sql"
	"log"
	"os"
	"strconv"

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

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

type DBData struct {
	DB   *sql.DB
	User 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() {
	_, err := db.DB.Exec(
		"CREATE TABLE IF NOT EXISTS settings(username TEXT, setting TEXT, value TEXT, PRIMARY KEY(username, setting));")
	if err != nil {
		log.Panicln(err)
	}
	_, err = db.DB.Exec(
		"CREATE TABLE IF NOT EXISTS scores ( username TEXT, `when` INTEGER, date INTEGER, hand INTEGER, won INTEGER, score INTEGER, PRIMARY KEY(username, date, hand));")
	if err != nil {
		log.Panicln(err)
	}
	_, err = db.DB.Exec(
		"CREATE TABLE IF NOT EXISTS monthly ( month	INTEGER, username TEXT, days INTEGER, hands_won INTEGER, score INTEGER, PRIMARY KEY(month, username) );")
	if err != nil {
		log.Panicln(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.
	/*
		if err != nil {
			log.Printf("LOCK %#v\n", err)
		}
	*/
}

func (db *DBData) Lock(value string) string {
	_, 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)
	}
	log.Printf("Lock(%s) = %s\n", value, result)
	return result
}

func (db *DBData) Unlock(value string) bool {
	_, err := db.DB.Exec("UPDATE settings SET value=? WHERE username=? AND setting=? AND value=?;",
		LOCK_CLEAR, LOCK_USERNAME, LOCK_SETTING, value)
	if err != nil {
		log.Printf("Unlock(%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("Unlock Scan", err)
	}
	log.Printf("Unlock(%s) = %s\n", value, result)
	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 int64, date int64, hand int, won int, score int) {
	_, err := db.DB.Exec("INSERT INTO scores(username, `when`, date, hand, won, score) VALUES(?,?,?,?,?,?);",
		db.User, when, date, hand, won, score)
	if err != nil {
		log.Panicln("SaveScore", err)
	}
}

func (db *DBData) HandsPlayedOnDay(day int64) int {
	row := db.DB.QueryRow("SELECT COUNT(*) FROM scores WHERE username=? AND date=?;")
	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[int64]int {
	var result map[int64]int = make(map[int64]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 int64
		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     int64
	Username string
}

type MonthStats struct {
	Days      int
	Hands_Won int
	Score     int
}

func (db *DBData) ExpireScores(month_first_t int64) {
	// step 1: acquire lock
	// ... step 0: find working lock strategy in go.  :(
	// lockfile := lockedfile.Create("db.lock")
	// defer lockfile.Close()
	var pid string = strconv.Itoa(os.Getpid())

	l := db.Lock(pid)

	if l == pid {
		log.Printf("Lock worked!\n")
		defer db.Unlock(pid)
	} else {
		log.Printf("Lock failed [%s]\n", l)
	}

	// var Monthly map[MonthUser]MonthStats = make(map[MonthUser]MonthStats)

}

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
	Score     int
}

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

	rows, err := db.DB.Query("SELECT month, username, days, hands_won, 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.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
}