Cacher is the code snippet organizer for pro developers

package main

import (
	"fmt"
	"github.com/cznic/ql"
	"log"
	"math/rand"
	"os"
	"sort"
)

var (
	stmCreateTables = ql.MustCompile(`
BEGIN TRANSACTION;
CREATE TABLE products (
	n int32,
	s string
);
COMMIT;`)
	stmInsertIntStr = ql.MustCompile(`INSERT INTO products VALUES($1, $2);`)
	stmCount        = ql.MustCompile(`SELECT count() FROM products;`)
	stmSelect       = ql.MustCompile(`SELECT n, s FROM products ORDER BY n;`)
)

func genRandomIntStr() (int32, string) {
	n := rand.Int31()
	return int32(n), fmt.Sprintf("s_%d", n)
}

func PathExists(path string) bool {
	_, err := os.Stat(path)
	if err == nil {
		return true
	}
	if os.IsNotExist(err) {
		return false
	}
	return false
}

type IntStr struct {
	n int32
	s string
}

type IntStrByInt []IntStr

func (s IntStrByInt) Len() int {
	return len(s)
}
func (s IntStrByInt) Swap(i, j int) {
	s[i], s[j] = s[j], s[i]
}
func (s IntStrByInt) Less(i, j int) bool {
	return s[i].n < s[j].n
}

var (
	insertedData []IntStr
)

func getCountFromRecordSet(rs []ql.Recordset) int {
	var count int64
	err := rs[0].Do(false, func(rec []interface{}) (bool, error) {
		//fmt.Printf("rec: %#v\n", rec)
		count = rec[0].(int64)
		return true, nil
	})
	if err != nil {
		log.Fatalf("rs[0].Do() failed with '%s'\n", err.Error())
	}
	fmt.Printf("count: %d\n", count)
	return int(count)
}

func verifyCount(db *ql.DB) {
	rs, _, err := db.Execute(ql.NewRWCtx(), stmCount)
	if err != nil {
		log.Fatalf("db.Execute('%s') failed with '%s'\n", stmCount, err.Error())
	}

	//fmt.Printf("len(rs) for stmCount: %d\n", len(rs))
	nRecords := getCountFromRecordSet(rs)
	if nRecords != 1024 {
		log.Fatalf("nRecords (%d) != 1024\n", nRecords)
	}
}

func readDatabase(db *ql.DB) []IntStr {
	verifyCount(db)
	rs, _, err := db.Execute(ql.NewRWCtx(), stmSelect)
	if err != nil {
		log.Fatalf("db.Execute('%s') failed with '%s'\n", stmSelect, err.Error())
	}
	var res []IntStr
	for _, rec := range rs {
		err := rec.Do(false, func(fields []interface{}) (bool, error) {
			n := fields[0].(int32)
			s := fields[1].(string)
			res = append(res, IntStr{n: n, s: s})
			return true, nil
		})
		if err != nil {
			log.Fatalf("rec.Do() failed with '%s'\n", err.Error())
		}
	}
	fmt.Printf("len(res) = %d\n", len(res))
	return res
}

func populateDatabase(db *ql.DB) {
	_, _, err := db.Execute(ql.NewRWCtx(), stmCreateTables)
	if err != nil {
		log.Fatalf("db.Execute('%s') failed with '%s'\n", stmCreateTables, err.Error())
	}
	rand.Seed(666) // ensure consistent numbers
	ctx := ql.NewRWCtx()
	_, _, err = db.Run(ctx, "BEGIN TRANSACTION;")
	if err != nil {
		log.Fatalf("db.Run('%s') failed with '%s'\n", "BEGIN TRANSACTION;", err.Error())
	}
	for i := 0; i < 1024; i++ {
		n, s := genRandomIntStr()
		_, _, err = db.Execute(ctx, stmInsertIntStr, n, s)
		if err != nil {
			log.Fatalf("db.Execute('%s') failed with '%s'\n", stmInsertIntStr, err.Error())
		}
		el := IntStr{n: n, s: s}
		insertedData = append(insertedData, el)
	}

	_, _, err = db.Run(ctx, "COMMIT;")
	if err != nil {
		log.Fatalf("db.Run('%s') failed with '%s'\n", "COMMIT;", err.Error())
	}
}

func generateInsertedData() {
	rand.Seed(666) // ensure consistent numbers
	for i := 0; i < 1024; i++ {
		n, s := genRandomIntStr()
		el := IntStr{n: n, s: s}
		insertedData = append(insertedData, el)
	}
}

func verifySelectResults(res []IntStr) {
	for i, el := range res {
		//fmt.Printf("i=%d\n", i)
		expected := insertedData[i]
		if el.n != expected.n {
			log.Fatalf("i=%d, el.n != expected.n (%d != %d)\n", i, el.n, expected.n)
		}
		if el.s != expected.s {
			log.Fatalf("i=%d, el.s != expected.s (%s != %s)\n", i, el.s, expected.s)
		}
	}
	fmt.Printf("select results verified!\n")
}

func dumpFirst(n int, arr []IntStr) {
	if n > len(arr) {
		n = len(arr)
	}
	for i := 0; i < n; i++ {
		el := arr[i]
		fmt.Printf("n=%d, s=%s\n", el.n, el.s)
	}
}

func main() {
	fmt.Printf("Testing ql database.\n")
	dbFileName := "test.db"
	exists := PathExists(dbFileName)
	opt := &ql.Options{CanCreate: true}
	db, err := ql.OpenFile(dbFileName, opt)
	if err != nil {
		log.Fatalf("ql.OpenFile() failed with '%s'\n", err.Error())
	}
	defer db.Close()
	if !exists {
		populateDatabase(db)
	} else {
		generateInsertedData()
	}

	res := readDatabase(db)

	//dumpFirst(10, res)
	//fmt.Printf("\n")

	//dumpFirst(10, insertedData)
	//fmt.Printf("\n")

	sort.Sort(IntStrByInt(insertedData))
	//dumpFirst(10, insertedData)
	//fmt.Printf("\n")

	verifySelectResults(res)
}