space-ace-go/db.go

package main

import (
	"database/sql"
	"log"

	_ "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()
}

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)
	}

}

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)
}