package main import ( "log" "os" "time" "github.com/jmoiron/sqlx" _ "github.com/mattn/go-sqlite3" ) const schema = ` CREATE TABLE IF NOT EXISTS entries ( id INTEGER PRIMARY KEY, location TEXT NOT NULL, referrer TEXT, started_at DATETIME NOT NULL, time_spent INTEGER NOT NULL, scrolled_to REAL NOT NULL );` type Entry struct { Id int `json:"-" db:"id"` Location string `json:"location" db:"location"` Referrer string `json:"referrer,omitempty" db:"referrer"` StartedAt time.Time `json:"startedAt" db:"started_at"` TimeSpent int `json:"timeSpent" db:"time_spent"` ScrolledTo float32 `json:"scrolledTo" db:"scrolled_to"` } var db *sqlx.DB func InitEntries() { file := os.Getenv("STATSDB") if file == "" { log.Println("Defaulting to STATSDB=./stats.db") file = "./stats.db" } var err error db, err = sqlx.Connect("sqlite3", file) if err != nil { log.Fatalln(err) } _, err = db.Exec(schema) if err != nil { log.Fatalln("Could not initialize schema") log.Fatalln(err) } } func CloseEntries() { db.Close() } func InsertEntry(entry *Entry) error { const query = ` INSERT INTO entries (location, referrer, started_at, time_spent, scrolled_to) VALUES (:location, :referrer, :started_at, :time_spent, :scrolled_to);` _, err := db.NamedExec(query, entry) log.Println("Entry recorded:", entry.Location) return err } func CountEntries() (count uint, err error) { err = db.Get(&count, "SELECT COUNT(*) FROM entries;") return } func AllEntries() (entries []Entry, err error) { err = db.Select(&entries, "SELECT * FROM entries;") return } func ListEntries(from, count uint) (entries []Entry, err error) { err = db.Select(&entries, "SELECT * FROM entries LIMIT ? OFFSET ?;", count, from) return }