Go Sqlite3 Backup

Go Sqlite3 Backup

package main
 
import (
	"database/sql"
	"fmt"
	"github.com/mattn/go-sqlite3"
	"log"
	"os"
	"regexp"
	"time"
)
 
func main() {
	sqlite3conn := []*sqlite3.SQLiteConn{}
	sql.Register("sqlite3_with_hook_example",
		&sqlite3.SQLiteDriver{
			ConnectHook: func(conn *sqlite3.SQLiteConn) error {
				sqlite3conn = append(sqlite3conn, conn)
				return nil
			},
		})
	os.Remove("./foo.db")
	os.Remove("./bar.db")
 
	destDb, err := sql.Open("sqlite3_with_hook_example", "./foo.db")
	if err != nil {
		log.Fatal(err)
	}
	defer destDb.Close()
	destDb.Ping()
 
	_, err = destDb.Exec("create table foo(id int, company_id int, value1 text, value2 text)")
	if err != nil {
		log.Fatal(err)
	}
 
	now := time.Now()
	tx, err := destDb.Begin()
	if err != nil {
		log.Fatal(err)
	}
	for i := 0; i < 1000000; i++ {
		_, err = tx.Exec(fmt.Sprintf(
			"insert into foo values "+
				"("+
				"%d, "+
				"100, "+
				"'This is a longer string of data --------------------- just deal with it peon %d', "+
				"'SOMESTRINGHERE%d' "+
				")",
			i, i, i),
		)
		if err != nil {
			log.Fatal(err)
		}
		if i%10000 == 0 {
			fmt.Println("at", i, time.Now().Sub(now))
			now = time.Now()
		}
	}
	tx.Commit()
	fmt.Println("create took", time.Now().Sub(now))
 
	now = time.Now()
	srcDb, err := sql.Open("sqlite3_with_hook_example", "./bar.db")
	if err != nil {
		log.Fatal(err)
	}
	defer srcDb.Close()
	srcDb.Ping()
 
	bk, err := sqlite3conn[1].Backup("main", sqlite3conn[0], "main")
	if err != nil {
		log.Fatal(err)
	}
 
	// single step backup
	bk.Step(-1)
	if err != nil {
		log.Fatal(err)
	}
	_, err = destDb.Query("select * from foo")
	if err != nil {
		log.Fatal(err)
	}
	bk.Finish()
	fmt.Println("backup took", time.Now().Sub(now))
 
	// integrity check
	now = time.Now()
	res, err := srcDb.Query("PRAGMA integrity_check;")
	if err != nil {
		log.Fatal(err)
	}
	defer res.Close()
 
	count := 0
	badBackup := false
	for res.Next() {
		var result string
		res.Scan(&result)
		fmt.Printf("%+v\n", result)
		match, err := regexp.MatchString("ok", result)
		if err != nil {
			log.Fatal(err)
		}
		if count > 0 || !match {
			badBackup = true
		}
		count++
	}
	fmt.Println("integrity check took", time.Now().Sub(now), "is ng", badBackup)
}