sqlite3 routine

sqlite3 race

package main

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

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

func main() {
	os.Remove("/tmp/testlock.db")
	os.Remove("/tmp/testlock-shm.db")
	os.Remove("/tmp/testlock-wal.db")

	var db *sql.DB
	if len(os.Args) != 2 { //no param
		printUsage()
		return
	}
	switch os.Args[1] {
	case "1":
		db = openDB()
		initDB(db)
	case "0":
		dbLoc := openDB()
		initDB(dbLoc)
		dbLoc.Close()
	default:
		printUsage()
		return
	}

	ch := make(chan bool)
	go writer(db)
	go reader(db)
	<-ch
}

func printUsage() {
	fmt.Printf("usage: dbtest mode\n 0:multiple connection; 1:single connection\n")
}

func initDB(db *sql.DB) {
	sSql := `
		CREATE TABLE counters (
		    id      INTEGER,
		    intro         VARCHAR (255)
		)`
	db.Exec(sSql)
}

func openDB() *sql.DB {
	db, err := sql.Open("sqlite3", "file:/tmp/testlock.db?cache=shared&mode=rwc&_busy_timeout=9999999")
	if err != nil {
		log.Printf("open error %s", err)
		return nil
	}
	db.Exec("PRAGMA journal_mode=WAL")
	return db
}

func writer(db *sql.DB) {
	var dbLoc *sql.DB
	if db == nil {
		dbLoc = openDB()
	} else {
		dbLoc = db
	}
	sSql := `
		INSERT INTO counters (
			id,intro
		) VALUES(
			?,?
		)`
	dbLoc.Exec(sSql, 1, "")
	i := 0
	sSql = `
	update counters set id = ?
	`
	for {
		_, err := dbLoc.Exec(sSql, i)
		i += 1
		if err != nil {
			log.Printf("db error in writer %s", err)
			os.Exit(1)
		}
	}
}

func reader(db *sql.DB) {
	var dbLoc *sql.DB
	if db == nil {
		dbLoc = openDB()
	} else {
		dbLoc = db
	}
	sSql := `
	select * from counters
	`
	for {
		_, err := dbLoc.Exec(sSql)
		if err != nil {
			log.Printf("db error in reader %s", err)
			os.Exit(1)
		}
	}
}