package golite

import (
	"bytes"
	"context"
	"database/sql"
	"database/sql/driver"
	"errors"
	"fmt"
	"io/ioutil"
	"math"
	"math/rand"
	"net/url"
	"os"
	"path"
	"reflect"
	"strings"
	"sync"
	"testing"
	"testing/internal/testdeps"
	"time"
)



// The number of rows of test data to create in the source database.
// Can be used to control how many pages are available to be backed up.
const testRowCount = 100

// The maximum number of seconds after which the page-by-page backup is considered to have taken too long.
const usePagePerStepsTimeoutSeconds = 30

// Test the backup functionality.
func testBackup(t *testing.T, testRowCount int, usePerPageSteps bool) {
	// This function will be called multiple times.
	// It uses sql.Register(), which requires the name parameter value to be unique.
	// There does not currently appear to be a way to unregister a registered driver, however.
	// So generate a database driver name that will likely be unique.
	var driverName = fmt.Sprintf("sqlite3_testBackup_%v_%v_%v", testRowCount, usePerPageSteps, time.Now().UnixNano())

	// The driver's connection will be needed in order to perform the backup.
	driverConns := []*SQLiteConn{}
	sql.Register(driverName, &SQLiteDriver{
		ConnectHook: func(conn *SQLiteConn) error {
			driverConns = append(driverConns, conn)
			return nil
		},
	})

	// Connect to the source database.
	srcTempFilename  := "file:src?mode=memory&cache=shared"
	srcDb, err := sql.Open(driverName, srcTempFilename)
	if err != nil {
		t.Fatal("Failed to open the source database:", err)
	}
	defer srcDb.Close()
	err = srcDb.Ping()
	if err != nil {
		t.Fatal("Failed to connect to the source database:", err)
	}

	// Connect to the destination database.
	destTempFilename := "file:dst?mode=memory&cache=shared"
	destDb, err := sql.Open(driverName, destTempFilename)
	if err != nil {
		t.Fatal("Failed to open the destination database:", err)
	}
	defer destDb.Close()
	err = destDb.Ping()
	if err != nil {
		t.Fatal("Failed to connect to the destination database:", err)
	}

	// Check the driver connections.
	if len(driverConns) != 2 {
		t.Fatalf("Expected 2 driver connections, but found %v.", len(driverConns))
	}
	srcDbDriverConn := driverConns[0]
	if srcDbDriverConn == nil {
		t.Fatal("The source database driver connection is nil.")
	}
	destDbDriverConn := driverConns[1]
	if destDbDriverConn == nil {
		t.Fatal("The destination database driver connection is nil.")
	}

	// Generate some test data for the given ID.
	var generateTestData = func(id int) string {
		return fmt.Sprintf("test-%v", id)
	}

	// Populate the source database with a test table containing some test data.
	tx, err := srcDb.Begin()
	if err != nil {
		t.Fatal("Failed to begin a transaction when populating the source database:", err)
	}
	_, err = srcDb.Exec("CREATE TABLE test (id INTEGER PRIMARY KEY, value TEXT)")
	if err != nil {
		tx.Rollback()
		t.Fatal("Failed to create the source database \"test\" table:", err)
	}
	for id := 0; id < testRowCount; id++ {
		_, err = srcDb.Exec("INSERT INTO test (id, value) VALUES (?, ?)", id, generateTestData(id))
		if err != nil {
			tx.Rollback()
			t.Fatal("Failed to insert a row into the source database \"test\" table:", err)
		}
	}
	err = tx.Commit()
	if err != nil {
		t.Fatal("Failed to populate the source database:", err)
	}

	// Confirm that the destination database is initially empty.
	var destTableCount int
	err = destDb.QueryRow("SELECT COUNT(*) FROM sqlite_master WHERE type = 'table'").Scan(&destTableCount)
	if err != nil {
		t.Fatal("Failed to check the destination table count:", err)
	}
	if destTableCount != 0 {
		t.Fatalf("The destination database is not empty; %v table(s) found.", destTableCount)
	}

	// Prepare to perform the backup.
	backup, err := destDbDriverConn.Backup("main", srcDbDriverConn, "main")
	if err != nil {
		t.Fatal("Failed to initialize the backup:", err)
	}

	// Allow the initial page count and remaining values to be retrieved.
	// According to <https://www.sqlite.org/c3ref/backup_finish.html>, the page count and remaining values are "... only updated by sqlite3_backup_step()."
	isDone, err := backup.Step(0)
	if err != nil {
		t.Fatal("Unable to perform an initial 0-page backup step:", err)
	}
	if isDone {
		t.Fatal("Backup is unexpectedly done.")
	}

	// Check that the page count and remaining values are reasonable.
	initialPageCount := backup.PageCount()
	if initialPageCount <= 0 {
		t.Fatalf("Unexpected initial page count value: %v", initialPageCount)
	}
	initialRemaining := backup.Remaining()
	if initialRemaining <= 0 {
		t.Fatalf("Unexpected initial remaining value: %v", initialRemaining)
	}
	if initialRemaining != initialPageCount {
		t.Fatalf("Initial remaining value differs from the initial page count value; remaining: %v; page count: %v", initialRemaining, initialPageCount)
	}

	// Perform the backup.
	if usePerPageSteps {
		var startTime = time.Now().Unix()

		// Test backing-up using a page-by-page approach.
		var latestRemaining = initialRemaining
		for {
			// Perform the backup step.
			isDone, err = backup.Step(1)
			if err != nil {
				t.Fatal("Failed to perform a backup step:", err)
			}

			// The page count should remain unchanged from its initial value.
			currentPageCount := backup.PageCount()
			if currentPageCount != initialPageCount {
				t.Fatalf("Current page count differs from the initial page count; initial page count: %v; current page count: %v", initialPageCount, currentPageCount)
			}

			// There should now be one less page remaining.
			currentRemaining := backup.Remaining()
			expectedRemaining := latestRemaining - 1
			if currentRemaining != expectedRemaining {
				t.Fatalf("Unexpected remaining value; expected remaining value: %v; actual remaining value: %v", expectedRemaining, currentRemaining)
			}
			latestRemaining = currentRemaining

			if isDone {
				break
			}

			// Limit the runtime of the backup attempt.
			if (time.Now().Unix() - startTime) > usePagePerStepsTimeoutSeconds {
				t.Fatal("Backup is taking longer than expected.")
			}
		}
	} else {
		// Test the copying of all remaining pages.
		isDone, err = backup.Step(-1)
		if err != nil {
			t.Fatal("Failed to perform a backup step:", err)
		}
		if !isDone {
			t.Fatal("Backup is unexpectedly not done.")
		}
	}

	// Check that the page count and remaining values are reasonable.
	finalPageCount := backup.PageCount()
	if finalPageCount != initialPageCount {
		t.Fatalf("Final page count differs from the initial page count; initial page count: %v; final page count: %v", initialPageCount, finalPageCount)
	}
	finalRemaining := backup.Remaining()
	if finalRemaining != 0 {
		t.Fatalf("Unexpected remaining value: %v", finalRemaining)
	}

	// Finish the backup.
	err = backup.Finish()
	if err != nil {
		t.Fatal("Failed to finish backup:", err)
	}

	// Confirm that the "test" table now exists in the destination database.
	var doesTestTableExist bool
	err = destDb.QueryRow("SELECT EXISTS (SELECT 1 FROM sqlite_master WHERE type = 'table' AND name = 'test' LIMIT 1) AS test_table_exists").Scan(&doesTestTableExist)
	if err != nil {
		t.Fatal("Failed to check if the \"test\" table exists in the destination database:", err)
	}
	if !doesTestTableExist {
		t.Fatal("The \"test\" table could not be found in the destination database.")
	}

	// Confirm that the number of rows in the destination database's "test" table matches that of the source table.
	var actualTestTableRowCount int
	err = destDb.QueryRow("SELECT COUNT(*) FROM test").Scan(&actualTestTableRowCount)
	if err != nil {
		t.Fatal("Failed to determine the rowcount of the \"test\" table in the destination database:", err)
	}
	if testRowCount != actualTestTableRowCount {
		t.Fatalf("Unexpected destination \"test\" table row count; expected: %v; found: %v", testRowCount, actualTestTableRowCount)
	}

	// Check each of the rows in the destination database.
	for id := 0; id < testRowCount; id++ {
		var checkedValue string
		err = destDb.QueryRow("SELECT value FROM test WHERE id = ?", id).Scan(&checkedValue)
		if err != nil {
			t.Fatal("Failed to query the \"test\" table in the destination database:", err)
		}

		var expectedValue = generateTestData(id)
		if checkedValue != expectedValue {
			t.Fatalf("Unexpected value in the \"test\" table in the destination database; expected value: %v; actual value: %v", expectedValue, checkedValue)
		}
	}
}

func TestBackupStepByStep(t *testing.T) {
	testBackup(t, testRowCount, true)
}

func TestBackupAllRemainingPages(t *testing.T) {
	testBackup(t, testRowCount, false)
}

// Test the error reporting when preparing to perform a backup.
func TestBackupError(t *testing.T) {
	const driverName = "sqlite3_TestBackupError"

	// The driver's connection will be needed in order to perform the backup.
	var dbDriverConn *SQLiteConn
	sql.Register(driverName, &SQLiteDriver{
		ConnectHook: func(conn *SQLiteConn) error {
			dbDriverConn = conn
			return nil
		},
	})

	// Connect to the database.
	db, err := sql.Open(driverName, ":memory:")
	if err != nil {
		t.Fatal("Failed to open the database:", err)
	}
	defer db.Close()
	db.Ping()

	// Need the driver connection in order to perform the backup.
	if dbDriverConn == nil {
		t.Fatal("Failed to get the driver connection.")
	}

	// Prepare to perform the backup.
	// Intentionally using the same connection for both the source and destination databases, to trigger an error result.
	backup, err := dbDriverConn.Backup("main", dbDriverConn, "main")
	if err == nil {
		t.Fatal("Failed to get the expected error result.")
	}
	const expectedError = "source and destination must be distinct"
	if err.Error() != expectedError {
		t.Fatalf("Unexpected error message; expected value: \"%v\"; actual value: \"%v\"", expectedError, err.Error())
	}
	if backup != nil {
		t.Fatal("Failed to get the expected nil backup result.")
	}
}

func TestCallbackArgCast(t *testing.T) {
	intConv := callbackSyntheticForTests(reflect.ValueOf(int64(math.MaxInt64)), nil)
	floatConv := callbackSyntheticForTests(reflect.ValueOf(float64(math.MaxFloat64)), nil)
	errConv := callbackSyntheticForTests(reflect.Value{}, errors.New("test"))

	tests := []struct {
		f callbackArgConverter
		o reflect.Value
	}{
		{intConv, reflect.ValueOf(int8(-1))},
		{intConv, reflect.ValueOf(int16(-1))},
		{intConv, reflect.ValueOf(int32(-1))},
		{intConv, reflect.ValueOf(uint8(math.MaxUint8))},
		{intConv, reflect.ValueOf(uint16(math.MaxUint16))},
		{intConv, reflect.ValueOf(uint32(math.MaxUint32))},
		// Special case, int64->uint64 is only 1<<63 - 1, not 1<<64 - 1
		{intConv, reflect.ValueOf(uint64(math.MaxInt64))},
		{floatConv, reflect.ValueOf(float32(math.Inf(1)))},
	}

	for _, test := range tests {
		conv := callbackArgCast{test.f, test.o.Type()}
		val, err := conv.Run(nil)
		if err != nil {
			t.Errorf("Couldn't convert to %s: %s", test.o.Type(), err)
		} else if !reflect.DeepEqual(val.Interface(), test.o.Interface()) {
			t.Errorf("Unexpected result from converting to %s: got %v, want %v", test.o.Type(), val.Interface(), test.o.Interface())
		}
	}

	conv := callbackArgCast{errConv, reflect.TypeOf(int8(0))}
	_, err := conv.Run(nil)
	if err == nil {
		t.Errorf("Expected error during callbackArgCast, but got none")
	}
}

func TestCallbackConverters(t *testing.T) {
	tests := []struct {
		v   any
		err bool
	}{
		// Unfortunately, we can't tell which converter was returned,
		// but we can at least check which types can be converted.
		{[]byte{0}, false},
		{"text", false},
		{true, false},
		{int8(0), false},
		{int16(0), false},
		{int32(0), false},
		{int64(0), false},
		{uint8(0), false},
		{uint16(0), false},
		{uint32(0), false},
		{uint64(0), false},
		{int(0), false},
		{uint(0), false},
		{float64(0), false},
		{float32(0), false},

		{func() {}, true},
		{complex64(complex(0, 0)), true},
		{complex128(complex(0, 0)), true},
		{struct{}{}, true},
		{map[string]string{}, true},
		{[]string{}, true},
		{(*int8)(nil), true},
		{make(chan int), true},
	}

	for _, test := range tests {
		_, err := callbackArg(reflect.TypeOf(test.v))
		if test.err && err == nil {
			t.Errorf("Expected an error when converting %s, got no error", reflect.TypeOf(test.v))
		} else if !test.err && err != nil {
			t.Errorf("Expected converter when converting %s, got error: %s", reflect.TypeOf(test.v), err)
		}
	}

	for _, test := range tests {
		_, err := callbackRet(reflect.TypeOf(test.v))
		if test.err && err == nil {
			t.Errorf("Expected an error when converting %s, got no error", reflect.TypeOf(test.v))
		} else if !test.err && err != nil {
			t.Errorf("Expected converter when converting %s, got error: %s", reflect.TypeOf(test.v), err)
		}
	}
}

func TestCallbackReturnAny(t *testing.T) {
	udf := func() any {
		return 1
	}

	typ := reflect.TypeOf(udf)
	_, err := callbackRet(typ.Out(0))
	if err != nil {
		t.Errorf("Expected valid callback for any return type, got: %s", err)
	}
}

func TestSimpleError(t *testing.T) {
	e := ErrError.Error()
	if e != "SQL logic error or missing database" && e != "SQL logic error" {
		t.Error("wrong error code: " + e)
	}
}

func TestCorruptDbErrors(t *testing.T) {
	dirName, err := ioutil.TempDir("", "sqlite3")
	if err != nil {
		t.Fatal(err)
	}
	defer os.RemoveAll(dirName)

	dbFileName := path.Join(dirName, "test.db")
	f, err := os.Create(dbFileName)
	if err != nil {
		t.Error(err)
	}
	f.Write([]byte{1, 2, 3, 4, 5})
	f.Close()

	db, err := sql.Open("sqlite3", dbFileName)
	if err == nil {
		_, err = db.Exec("drop table foo")
	}

	sqliteErr := err.(Error)
	if sqliteErr.Code != ErrNotADB {
		t.Error("wrong error code for corrupted DB")
	}
	if err.Error() == "" {
		t.Error("wrong error string for corrupted DB")
	}
	db.Close()
}

func TestSqlLogicErrors(t *testing.T) {
	dirName, err := ioutil.TempDir("", "sqlite3")
	if err != nil {
		t.Fatal(err)
	}
	defer os.RemoveAll(dirName)

	db, err := sql.Open("sqlite3", ":memory:")
	if err != nil {
		t.Error(err)
	}
	defer db.Close()

	_, err = db.Exec("CREATE TABLE Foo (id INTEGER PRIMARY KEY)")
	if err != nil {
		t.Error(err)
	}

	const expectedErr = "table Foo already exists"
	_, err = db.Exec("CREATE TABLE Foo (id INTEGER PRIMARY KEY)")
	if err.Error() != expectedErr {
		t.Errorf("Unexpected error: %s, expected %s", err.Error(), expectedErr)
	}

}

func TestExtendedErrorCodes_ForeignKey(t *testing.T) {
	dirName, err := ioutil.TempDir("", "sqlite3-err")
	if err != nil {
		t.Fatal(err)
	}
	defer os.RemoveAll(dirName)

	db, err := sql.Open("sqlite3", ":memory:")
	if err != nil {
		t.Error(err)
	}
	defer db.Close()

	_, err = db.Exec("PRAGMA foreign_keys=ON;")
	if err != nil {
		t.Errorf("PRAGMA foreign_keys=ON: %v", err)
	}

	_, err = db.Exec(`CREATE TABLE Foo (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		value INTEGER NOT NULL,
		ref INTEGER NULL REFERENCES Foo (id),
		UNIQUE(value)
	);`)
	if err != nil {
		t.Error(err)
	}

	_, err = db.Exec("INSERT INTO Foo (ref, value) VALUES (100, 100);")
	if err == nil {
		t.Error("No error!")
	} else {
		sqliteErr := err.(Error)
		if sqliteErr.Code != ErrConstraint {
			t.Errorf("Wrong basic error code: %d != %d",
				sqliteErr.Code, ErrConstraint)
		}
		if sqliteErr.ExtendedCode != ErrConstraintForeignKey {
			t.Errorf("Wrong extended error code: %d != %d",
				sqliteErr.ExtendedCode, ErrConstraintForeignKey)
		}
	}

}

func TestExtendedErrorCodes_NotNull(t *testing.T) {
	dirName, err := ioutil.TempDir("", "sqlite3-err")
	if err != nil {
		t.Fatal(err)
	}
	defer os.RemoveAll(dirName)

	db, err := sql.Open("sqlite3", ":memory:")
	if err != nil {
		t.Error(err)
	}
	defer db.Close()

	_, err = db.Exec("PRAGMA foreign_keys=ON;")
	if err != nil {
		t.Errorf("PRAGMA foreign_keys=ON: %v", err)
	}

	_, err = db.Exec(`CREATE TABLE Foo (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		value INTEGER NOT NULL,
		ref INTEGER NULL REFERENCES Foo (id),
		UNIQUE(value)
	);`)
	if err != nil {
		t.Error(err)
	}

	res, err := db.Exec("INSERT INTO Foo (value) VALUES (100);")
	if err != nil {
		t.Fatalf("Creating first row: %v", err)
	}

	id, err := res.LastInsertId()
	if err != nil {
		t.Fatalf("Retrieving last insert id: %v", err)
	}

	_, err = db.Exec("INSERT INTO Foo (ref) VALUES (?);", id)
	if err == nil {
		t.Error("No error!")
	} else {
		sqliteErr := err.(Error)
		if sqliteErr.Code != ErrConstraint {
			t.Errorf("Wrong basic error code: %d != %d",
				sqliteErr.Code, ErrConstraint)
		}
		if sqliteErr.ExtendedCode != ErrConstraintNotNull {
			t.Errorf("Wrong extended error code: %d != %d",
				sqliteErr.ExtendedCode, ErrConstraintNotNull)
		}
	}

}

func TestExtendedErrorCodes_Unique(t *testing.T) {
	dirName, err := ioutil.TempDir("", "sqlite3-err")
	if err != nil {
		t.Fatal(err)
	}
	defer os.RemoveAll(dirName)

	db, err := sql.Open("sqlite3", ":memory:")
	if err != nil {
		t.Error(err)
	}
	defer db.Close()

	_, err = db.Exec("PRAGMA foreign_keys=ON;")
	if err != nil {
		t.Errorf("PRAGMA foreign_keys=ON: %v", err)
	}

	_, err = db.Exec(`CREATE TABLE Foo (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		value INTEGER NOT NULL,
		ref INTEGER NULL REFERENCES Foo (id),
		UNIQUE(value)
	);`)
	if err != nil {
		t.Error(err)
	}

	res, err := db.Exec("INSERT INTO Foo (value) VALUES (100);")
	if err != nil {
		t.Fatalf("Creating first row: %v", err)
	}

	id, err := res.LastInsertId()
	if err != nil {
		t.Fatalf("Retrieving last insert id: %v", err)
	}

	_, err = db.Exec("INSERT INTO Foo (ref, value) VALUES (?, 100);", id)
	if err == nil {
		t.Error("No error!")
	} else {
		sqliteErr := err.(Error)
		if sqliteErr.Code != ErrConstraint {
			t.Errorf("Wrong basic error code: %d != %d",
				sqliteErr.Code, ErrConstraint)
		}
		if sqliteErr.ExtendedCode != ErrConstraintUnique {
			t.Errorf("Wrong extended error code: %d != %d",
				sqliteErr.ExtendedCode, ErrConstraintUnique)
		}
		extended := sqliteErr.Code.Extend(3).Error()
		expected := "constraint failed"
		if extended != expected {
			t.Errorf("Wrong basic error code: %q != %q",
				extended, expected)
		}
	}
}

func TestError_SystemErrno(t *testing.T) {
	_, n, _ := LibVersion()
	if n < 3012000 {
		t.Skip("sqlite3_system_errno requires sqlite3 >= 3.12.0")
	}

	// open a non-existent database in read-only mode so we get an IO error.
	db, err := sql.Open("sqlite3", "file:nonexistent.db?mode=ro")
	if err != nil {
		t.Fatal(err)
	}
	defer db.Close()

	err = db.Ping()
	if err == nil {
		t.Fatal("expected error pinging read-only non-existent database, but got nil")
	}

	serr, ok := err.(Error)
	if !ok {
		t.Fatalf("expected error to be of type Error, but got %[1]T %[1]v", err)
	}

	if serr.SystemErrno == 0 {
		t.Fatal("expected SystemErrno to be set")
	}

	if !os.IsNotExist(serr.SystemErrno) {
		t.Errorf("expected SystemErrno to be a not exists error, but got %v", serr.SystemErrno)
	}
}

func TestBeginTxCancel(t *testing.T) {
	db, err := sql.Open("sqlite3", ":memory:")
	if err != nil {
		t.Fatal(err)
	}

	db.SetMaxOpenConns(10)
	db.SetMaxIdleConns(5)

	defer db.Close()
	initDatabase(t, db, 100)

	// create several go-routines to expose racy issue
	for i := 0; i < 1000; i++ {
		func() {
			ctx, cancel := context.WithCancel(context.Background())
			conn, err := db.Conn(ctx)
			if err != nil {
				t.Fatal(err)
			}
			defer func() {
				if err := conn.Close(); err != nil {
					t.Error(err)
				}
			}()

			err = conn.Raw(func(driverConn any) error {
				d, ok := driverConn.(driver.ConnBeginTx)
				if !ok {
					t.Fatal("unexpected: wrong type")
				}
				// checks that conn.Raw can be used to get *SQLiteConn
				if _, ok = driverConn.(*SQLiteConn); !ok {
					t.Fatalf("conn.Raw() driverConn type=%T, expected *SQLiteConn", driverConn)
				}

				go cancel() // make it cancel concurrently with exec("BEGIN");
				tx, err := d.BeginTx(ctx, driver.TxOptions{})
				switch err {
				case nil:
					switch err := tx.Rollback(); err {
					case nil, sql.ErrTxDone:
					default:
						return err
					}
				case context.Canceled:
				default:
					// must not fail with "cannot start a transaction within a transaction"
					return err
				}
				return nil
			})
			if err != nil {
				t.Fatal(err)
			}
		}()
	}
}

func TestStmtReadonly(t *testing.T) {
	db, err := sql.Open("sqlite3", ":memory:")
	if err != nil {
		t.Fatal(err)
	}
	defer db.Close()

	_, err = db.Exec("CREATE TABLE t (count INT)")
	if err != nil {
		t.Fatal(err)
	}

	isRO := func(query string) bool {
		c, err := db.Conn(context.Background())
		if err != nil {
			return false
		}

		var ro bool
		c.Raw(func(dc any) error {
			stmt, err := dc.(*SQLiteConn).Prepare(query)
			if err != nil {
				return err
			}
			if stmt == nil {
				return errors.New("stmt is nil")
			}
			ro = stmt.(*SQLiteStmt).Readonly()
			return nil
		})
		return ro // On errors ro will remain false.
	}

	if !isRO(`select * from t`) {
		t.Error("select not seen as read-only")
	}
	if isRO(`insert into t values (1), (2)`) {
		t.Error("insert seen as read-only")
	}
}

func TestNamedParams(t *testing.T) {
	db, err := sql.Open("sqlite3", ":memory:")
	if err != nil {
		t.Fatal("Failed to open database:", err)
	}
	defer db.Close()

	_, err = db.Exec(`
	create table foo (id integer, name text, extra text);
	`)
	if err != nil {
		t.Error("Failed to call db.Query:", err)
	}

	_, err = db.Exec(`insert into foo(id, name, extra) values(:id, :name, :name)`, sql.Named("name", "foo"), sql.Named("id", 1))
	if err != nil {
		t.Error("Failed to call db.Exec:", err)
	}

	row := db.QueryRow(`select id, extra from foo where id = :id and extra = :extra`, sql.Named("id", 1), sql.Named("extra", "foo"))
	if row == nil {
		t.Error("Failed to call db.QueryRow")
	}
	var id int
	var extra string
	err = row.Scan(&id, &extra)
	if err != nil {
		t.Error("Failed to db.Scan:", err)
	}
	if id != 1 || extra != "foo" {
		t.Error("Failed to db.QueryRow: not matched results")
	}
}

var (
	testTableStatements = []string{
		`DROP TABLE IF EXISTS test_table`,
		`
CREATE TABLE IF NOT EXISTS test_table (
	key1      VARCHAR(64) PRIMARY KEY,
	key_id    VARCHAR(64) NOT NULL,
	key2      VARCHAR(64) NOT NULL,
	key3      VARCHAR(64) NOT NULL,
	key4      VARCHAR(64) NOT NULL,
	key5      VARCHAR(64) NOT NULL,
	key6      VARCHAR(64) NOT NULL,
	data      BLOB        NOT NULL
);`,
	}
	letterBytes = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ"
)

func randStringBytes(n int) string {
	b := make([]byte, n)
	for i := range b {
		b[i] = letterBytes[rand.Intn(len(letterBytes))]
	}
	return string(b)
}

func initDatabase(t *testing.T, db *sql.DB, rowCount int64) {
	for _, query := range testTableStatements {
		_, err := db.Exec(query)
		if err != nil {
			t.Fatal(err)
		}
	}
	for i := int64(0); i < rowCount; i++ {
		query := `INSERT INTO test_table
			(key1, key_id, key2, key3, key4, key5, key6, data)
			VALUES
			(?, ?, ?, ?, ?, ?, ?, ?);`
		args := []interface{}{
			randStringBytes(50),
			fmt.Sprint(i),
			randStringBytes(50),
			randStringBytes(50),
			randStringBytes(50),
			randStringBytes(50),
			randStringBytes(50),
			randStringBytes(50),
			randStringBytes(2048),
		}
		_, err := db.Exec(query, args...)
		if err != nil {
			t.Fatal(err)
		}
	}
}

func TestShortTimeout(t *testing.T) {
	db, err := sql.Open("sqlite3", ":memory:")
	if err != nil {
		t.Fatal(err)
	}
	defer db.Close()
	initDatabase(t, db, 100)

	ctx, cancel := context.WithTimeout(context.Background(), 1*time.Microsecond)
	defer cancel()
	query := `SELECT key1, key_id, key2, key3, key4, key5, key6, data
		FROM test_table
		ORDER BY key2 ASC`
	_, err = db.QueryContext(ctx, query)
	if err != nil && err != context.DeadlineExceeded {
		t.Fatal(err)
	}
	if ctx.Err() != nil && ctx.Err() != context.DeadlineExceeded {
		t.Fatal(ctx.Err())
	}
}

func TestExecContextCancel(t *testing.T) {
	db, err := sql.Open("sqlite3", "file:exec?mode=memory&cache=shared")
	if err != nil {
		t.Fatal(err)
	}
	defer db.Close()

	ts := time.Now()
	initDatabase(t, db, 1000)
	spent := time.Since(ts)
	const minTestTime = 100 * time.Millisecond
	if spent < minTestTime && false {
		t.Skipf("test will be too racy (spent=%s < min=%s) as ExecContext below will be too fast.",
			spent.String(), minTestTime.String(),
		)
	}

	// expected to be extremely slow query
	q := `
INSERT INTO test_table (key1, key_id, key2, key3, key4, key5, key6, data)
SELECT t1.key1 || t2.key1, t1.key_id || t2.key_id, t1.key2 || t2.key2, t1.key3 || t2.key3, t1.key4 || t2.key4, t1.key5 || t2.key5, t1.key6 || t2.key6, t1.data || t2.data
FROM test_table t1 LEFT OUTER JOIN test_table t2`
	// expect query above take ~ same time as setup above
	// This is racy: the context must be valid so sql/db.ExecContext calls the sqlite3 driver.
	// It starts the query, the context expires, then calls sqlite3_interrupt
	ctx, cancel := context.WithTimeout(context.Background(), minTestTime/2)
	defer cancel()
	ts = time.Now()
	r, err := db.ExecContext(ctx, q)
	// racy check
	if r != nil {
		n, err := r.RowsAffected()
		t.Logf("query should not have succeeded: rows=%d; err=%v; duration=%s",
			n, err, time.Since(ts).String())
	}
	if err != context.DeadlineExceeded {
		t.Fatal(err, ctx.Err())
	}
}

func TestQueryRowContextCancel(t *testing.T) {
	// FIXME: too slow
	db, err := sql.Open("sqlite3", "file:query?mode=memory&cache=shared")
	if err != nil {
		t.Fatal(err)
	}
	defer db.Close()
	initDatabase(t, db, 100)

	const query = `SELECT key_id FROM test_table ORDER BY key2 ASC`
	var keyID string
	unexpectedErrors := make(map[string]int)
	for i := 0; i < 10000; i++ {
		ctx, cancel := context.WithCancel(context.Background())
		row := db.QueryRowContext(ctx, query)

		cancel()
		// it is fine to get "nil" as context cancellation can be handled with delay
		if err := row.Scan(&keyID); err != nil && err != context.Canceled {
			if err.Error() == "sql: Rows are closed" {
				// see https://github.com/golang/go/issues/24431
				// fixed in 1.11.1 to properly return context error
				continue
			}
			unexpectedErrors[err.Error()]++
		}
	}
	for errText, count := range unexpectedErrors {
		t.Error(errText, count)
	}
}

func TestQueryRowContextCancelParallel(t *testing.T) {
	// FIXME: too slow
	db, err := sql.Open("sqlite3", "file:parallel?mode=memory&cache=shared")
	if err != nil {
		t.Fatal(err)
	}
	db.SetMaxOpenConns(10)
	db.SetMaxIdleConns(5)

	defer db.Close()
	initDatabase(t, db, 100)

	const query = `SELECT key_id FROM test_table ORDER BY key2 ASC`
	wg := sync.WaitGroup{}
	defer wg.Wait()

	testCtx, cancel := context.WithCancel(context.Background())
	defer cancel()

	for i := 0; i < 10; i++ {
		wg.Add(1)
		go func() {
			defer wg.Done()

			var keyID string
			for {
				select {
				case <-testCtx.Done():
					return
				default:
				}
				ctx, cancel := context.WithCancel(context.Background())
				row := db.QueryRowContext(ctx, query)

				cancel()
				_ = row.Scan(&keyID) // see TestQueryRowContextCancel
			}
		}()
	}

	var keyID string
	for i := 0; i < 10000; i++ {
		// note that testCtx is not cancelled during query execution
		row := db.QueryRowContext(testCtx, query)

		if err := row.Scan(&keyID); err != nil {
			t.Fatal(i, err)
		}
	}
}

func TestExecCancel(t *testing.T) {
	db, err := sql.Open("sqlite3", ":memory:")
	if err != nil {
		t.Fatal(err)
	}
	defer db.Close()

	if _, err = db.Exec("create table foo (id integer primary key)"); err != nil {
		t.Fatal(err)
	}

	for n := 0; n < 100; n++ {
		ctx, cancel := context.WithCancel(context.Background())
		_, err = db.ExecContext(ctx, "insert into foo (id) values (?)", n)
		cancel()
		if err != nil {
			t.Fatal(err)
		}
	}
}

func doTestOpenContext(t *testing.T, url string) (string, error) {
	db, err := sql.Open("sqlite3", url)
	if err != nil {
		return "Failed to open database:", err
	}

	defer func() {
		err = db.Close()
		if err != nil {
			t.Error("db close error:", err)
		}
	}()

	ctx, cancel := context.WithTimeout(context.Background(), 55*time.Second)
	err = db.PingContext(ctx)
	cancel()
	if err != nil {
		return "ping error:", err
	}

	ctx, cancel = context.WithTimeout(context.Background(), 55*time.Second)
	_, err = db.ExecContext(ctx, "drop table foo")
	cancel()
	ctx, cancel = context.WithTimeout(context.Background(), 55*time.Second)
	_, err = db.ExecContext(ctx, "create table foo (id integer)")
	cancel()
	if err != nil {
		return "Failed to create table:", err
	}

	return "", nil
}

func TestOpenContext(t *testing.T) {
	cases := map[string]bool{
		"file:openctx1?mode=memory&cache=shared":                   true,
		"file:openctx2?mode=memory&cache=shared&_txlock=immediate": true,
		"file:openctx3?mode=memory&cache=shared&_txlock=deferred":  true,
		"file:openctx4?mode=memory&cache=shared&_txlock=exclusive": true,
		"file:openctx5?mode=memory&cache=shared&_txlock=bogus":     false,
	}
	for option, expectedPass := range cases {
		result, err := doTestOpenContext(t, option)
		if result == "" {
			if !expectedPass {
				errmsg := fmt.Sprintf("_txlock error not caught at dbOpen with option: %s", option)
				t.Fatal(errmsg)
			}
		} else if expectedPass {
			if err == nil {
				t.Fatal(result)
			} else {
				t.Fatal(result, err)
			}
		}
	}
}

func TestFileCopyTruncate(t *testing.T) {
	var err error
	tempFilename := TempFilename(t)

	defer func() {
		err = os.Remove(tempFilename)
		if err != nil {
			t.Error("temp file remove error:", err)
		}
	}()

	db, err := sql.Open("sqlite3", tempFilename)
	if err != nil {
		t.Fatal("open error:", err)
	}

	defer func() {
		err = db.Close()
		if err != nil {
			t.Error("db close error:", err)
		}
	}()

	_, err = db.Exec("PRAGMA journal_mode = delete;")
	if err != nil {
		t.Fatal("journal_mode delete:", err)
	}

	ctx, cancel := context.WithTimeout(context.Background(), 55*time.Second)
	err = db.PingContext(ctx)
	cancel()
	if err != nil {
		t.Fatal("ping error:", err)
	}

	ctx, cancel = context.WithTimeout(context.Background(), 55*time.Second)
	_, err = db.ExecContext(ctx, "drop table foo")
	cancel()
	ctx, cancel = context.WithTimeout(context.Background(), 55*time.Second)
	_, err = db.ExecContext(ctx, "create table foo (id integer)")
	cancel()
	if err != nil {
		t.Fatal("create table error:", err)
	}

	// copy db to new file
	var data []byte
	data, err = ioutil.ReadFile(tempFilename)
	if err != nil {
		t.Fatal("read file error:", err)
	}

	var f *os.File
	f, err = os.Create(tempFilename + "-db-copy")
	if err != nil {
		t.Fatal("create file error:", err)
	}

	defer func() {
		err = os.Remove(tempFilename + "-db-copy")
		if err != nil {
			t.Error("temp file moved remove error:", err)
		}
	}()

	_, err = f.Write(data)
	if err != nil {
		f.Close()
		t.Fatal("write file error:", err)
	}
	err = f.Close()
	if err != nil {
		t.Fatal("close file error:", err)
	}

	// truncate current db file
	f, err = os.OpenFile(tempFilename, os.O_WRONLY|os.O_TRUNC, 0666)
	if err != nil {
		t.Fatal("open file error:", err)
	}
	err = f.Close()
	if err != nil {
		t.Fatal("close file error:", err)
	}

	// test db after file truncate
	ctx, cancel = context.WithTimeout(context.Background(), 55*time.Second)
	err = db.PingContext(ctx)
	cancel()
	if err != nil {
		t.Fatal("ping error:", err)
	}

	ctx, cancel = context.WithTimeout(context.Background(), 55*time.Second)
	_, err = db.ExecContext(ctx, "drop table foo")
	cancel()
	if err == nil {
		t.Fatal("drop table no error")
	}

	ctx, cancel = context.WithTimeout(context.Background(), 55*time.Second)
	_, err = db.ExecContext(ctx, "create table foo (id integer)")
	cancel()
	if err != nil {
		t.Fatal("create table error:", err)
	}

	err = db.Close()
	if err != nil {
		t.Error("db close error:", err)
	}

	// test copied file
	db, err = sql.Open("sqlite3", tempFilename+"-db-copy")
	if err != nil {
		t.Fatal("open error:", err)
	}

	defer func() {
		err = db.Close()
		if err != nil {
			t.Error("db close error:", err)
		}
	}()

	ctx, cancel = context.WithTimeout(context.Background(), 55*time.Second)
	err = db.PingContext(ctx)
	cancel()
	if err != nil {
		t.Fatal("ping error:", err)
	}

	ctx, cancel = context.WithTimeout(context.Background(), 55*time.Second)
	_, err = db.ExecContext(ctx, "drop table foo")
	cancel()
	if err != nil {
		t.Fatal("drop table error:", err)
	}

	ctx, cancel = context.WithTimeout(context.Background(), 55*time.Second)
	_, err = db.ExecContext(ctx, "create table foo (id integer)")
	cancel()
	if err != nil {
		t.Fatal("create table error:", err)
	}
}

func TestExtensionsError(t *testing.T) {
	sql.Register("sqlite3_TestExtensionsError",
		&SQLiteDriver{
			Extensions: []string{
				"foobar",
			},
		},
	)

	db, err := sql.Open("sqlite3_TestExtensionsError", ":memory:")
	if err != nil {
		t.Fatal(err)
	}
	defer db.Close()

	err = db.Ping()
	if err == nil {
		t.Fatal("expected error loading non-existent extension")
	}

	if err.Error() == "not an error" {
		t.Fatal("expected error from sqlite3_enable_load_extension to be returned")
	}
}

func TestLoadExtensionError(t *testing.T) {
	sql.Register("sqlite3_TestLoadExtensionError",
		&SQLiteDriver{
			ConnectHook: func(c *SQLiteConn) error {
				return c.LoadExtension("foobar", "")
			},
		},
	)

	db, err := sql.Open("sqlite3_TestLoadExtensionError", ":memory:")
	if err != nil {
		t.Fatal(err)
	}
	defer db.Close()

	err = db.Ping()
	if err == nil {
		t.Fatal("expected error loading non-existent extension")
	}

	if err.Error() == "not an error" {
		t.Fatal("expected error from sqlite3_enable_load_extension to be returned")
	}
}

func TestColumnTableName(t *testing.T) {
	d := SQLiteDriver{}
	conn, err := d.Open(":memory:")
	if err != nil {
		t.Fatal("failed to get database connection:", err)
	}
	defer conn.Close()
	sqlite3conn := conn.(*SQLiteConn)

	_, err = sqlite3conn.Exec(`CREATE TABLE foo (name string)`, nil)
	if err != nil {
		t.Fatal("Failed to create table:", err)
	}
	_, err = sqlite3conn.Exec(`CREATE TABLE bar (name string)`, nil)
	if err != nil {
		t.Fatal("Failed to create table:", err)
	}

	stmt, err := sqlite3conn.Prepare(`SELECT * FROM foo JOIN bar ON foo.name = bar.name`)
	if err != nil {
		t.Fatal(err)
	}

	if exp, got := "foo", stmt.(*SQLiteStmt).ColumnTableName(0); exp != got {
		t.Fatalf("Incorrect table name returned expected: %s, got: %s", exp, got)
	}
	if exp, got := "bar", stmt.(*SQLiteStmt).ColumnTableName(1); exp != got {
		t.Fatalf("Incorrect table name returned expected: %s, got: %s", exp, got)
	}
	if exp, got := "", stmt.(*SQLiteStmt).ColumnTableName(2); exp != got {
		t.Fatalf("Incorrect table name returned expected: %s, got: %s", exp, got)
	}
}

func TestFTS3(t *testing.T) {
	db, err := sql.Open("sqlite3", ":memory:")
	if err != nil {
		t.Fatal("Failed to open database:", err)
	}
	defer db.Close()

	_, err = db.Exec("DROP TABLE foo")
	_, err = db.Exec("CREATE VIRTUAL TABLE foo USING fts3(id INTEGER PRIMARY KEY, value TEXT)")
	if err != nil {
		t.Fatal("Failed to create table:", err)
	}

	_, err = db.Exec("INSERT INTO foo(id, value) VALUES(?, ?)", 1, `今日の 晩御飯は 天麩羅よ`)
	if err != nil {
		t.Fatal("Failed to insert value:", err)
	}

	_, err = db.Exec("INSERT INTO foo(id, value) VALUES(?, ?)", 2, `今日は いい 天気だ`)
	if err != nil {
		t.Fatal("Failed to insert value:", err)
	}

	rows, err := db.Query("SELECT id, value FROM foo WHERE value MATCH '今日* 天*'")
	if err != nil {
		t.Fatal("Unable to query foo table:", err)
	}
	defer rows.Close()

	for rows.Next() {
		var id int
		var value string

		if err := rows.Scan(&id, &value); err != nil {
			t.Error("Unable to scan results:", err)
			continue
		}

		if id == 1 && value != `今日の 晩御飯は 天麩羅よ` {
			t.Error("Value for id 1 should be `今日の 晩御飯は 天麩羅よ`, but:", value)
		} else if id == 2 && value != `今日は いい 天気だ` {
			t.Error("Value for id 2 should be `今日は いい 天気だ`, but:", value)
		}
	}

	rows, err = db.Query("SELECT value FROM foo WHERE value MATCH '今日* 天麩羅*'")
	if err != nil {
		t.Fatal("Unable to query foo table:", err)
	}
	defer rows.Close()

	var value string
	if !rows.Next() {
		t.Fatal("Result should be only one")
	}

	if err := rows.Scan(&value); err != nil {
		t.Fatal("Unable to scan results:", err)
	}

	if value != `今日の 晩御飯は 天麩羅よ` {
		t.Fatal("Value should be `今日の 晩御飯は 天麩羅よ`, but:", value)
	}

	if rows.Next() {
		t.Fatal("Result should be only one")
	}
}

func TestFTS4(t *testing.T) {
	db, err := sql.Open("sqlite3", ":memory:")
	if err != nil {
		t.Fatal("Failed to open database:", err)
	}
	defer db.Close()

	_, err = db.Exec("DROP TABLE foo")
	_, err = db.Exec("CREATE VIRTUAL TABLE foo USING fts4(tokenize=unicode61, id INTEGER PRIMARY KEY, value TEXT)")
	switch {
	case err != nil && err.Error() == "unknown tokenizer: unicode61":
		t.Skip("FTS4 not supported")
	case err != nil:
		t.Fatal("Failed to create table:", err)
	}

	_, err = db.Exec("INSERT INTO foo(id, value) VALUES(?, ?)", 1, `février`)
	if err != nil {
		t.Fatal("Failed to insert value:", err)
	}

	rows, err := db.Query("SELECT value FROM foo WHERE value MATCH 'fevrier'")
	if err != nil {
		t.Fatal("Unable to query foo table:", err)
	}
	defer rows.Close()

	var value string
	if !rows.Next() {
		t.Fatal("Result should be only one")
	}

	if err := rows.Scan(&value); err != nil {
		t.Fatal("Unable to scan results:", err)
	}

	if value != `février` {
		t.Fatal("Value should be `février`, but:", value)
	}

	if rows.Next() {
		t.Fatal("Result should be only one")
	}
}

func TestMathFunctions(t *testing.T) {
	db, err := sql.Open("sqlite3", ":memory:")
	if err != nil {
		t.Fatal("Failed to open database:", err)
	}
	defer db.Close()

	queries := []string{
		`SELECT acos(1)`,
		`SELECT log(10, 100)`,
		`SELECT power(2, 2)`,
	}

	for _, query := range queries {
		var result float64
		if err := db.QueryRow(query).Scan(&result); err != nil {
			t.Errorf("invoking math function query %q: %v", query, err)
		}
	}
}

type preUpdateHookDataForTest struct {
	databaseName string
	tableName    string
	count        int
	op           int
	oldRow       []any
	newRow       []any
}

/*
func TestPreUpdateHook(t *testing.T) {
	var events []preUpdateHookDataForTest

	sql.Register("sqlite3_PreUpdateHook", &SQLiteDriver{
		ConnectHook: func(conn *SQLiteConn) error {
			conn.RegisterPreUpdateHook(func(data SQLitePreUpdateData) {
				eval := -1
				oldRow := []any{eval}
				if data.Op != SQLITE_INSERT {
					err := data.Old(oldRow...)
					if err != nil {
						t.Fatalf("Unexpected error calling SQLitePreUpdateData.Old: %v", err)
					}
				}

				eval2 := -1
				newRow := []any{eval2}
				if data.Op != SQLITE_DELETE {
					err := data.New(newRow...)
					if err != nil {
						t.Fatalf("Unexpected error calling SQLitePreUpdateData.New: %v", err)
					}
				}

				// tests dest bound checks in loop
				var tooSmallRow []any
				if data.Op != SQLITE_INSERT {
					err := data.Old(tooSmallRow...)
					if err != nil {
						t.Fatalf("Unexpected error calling SQLitePreUpdateData.Old: %v", err)
					}
					if len(tooSmallRow) != 0 {
						t.Errorf("Expected tooSmallRow to be empty, got: %v", tooSmallRow)
					}
				}

				events = append(events, preUpdateHookDataForTest{
					databaseName: data.DatabaseName,
					tableName:    data.TableName,
					count:        data.Count(),
					op:           data.Op,
					oldRow:       oldRow,
					newRow:       newRow,
				})
			})
			return nil
		},
	})

	db, err := sql.Open("sqlite3_PreUpdateHook", ":memory:")
	if err != nil {
		t.Fatal("Failed to open database:", err)
	}
	defer db.Close()

	statements := []string{
		"create table foo (id integer primary key)",
		"insert into foo values (9)",
		"update foo set id = 99 where id = 9",
		"delete from foo where id = 99",
	}
	for _, statement := range statements {
		_, err = db.Exec(statement)
		if err != nil {
			t.Fatalf("Unable to prepare test data [%v]: %v", statement, err)
		}
	}

	if len(events) != 3 {
		t.Errorf("Events should be 3 entries, got: %d", len(events))
	}

	if events[0].op != SQLITE_INSERT {
		t.Errorf("Op isn't as expected: %v", events[0].op)
	}

	if events[1].op != SQLITE_UPDATE {
		t.Errorf("Op isn't as expected: %v", events[1].op)
	}

	if events[1].count != 1 {
		t.Errorf("Expected event row 1 to have 1 column, had: %v", events[1].count)
	}

	newRow_0_0 := events[0].newRow[0].(int64)
	if newRow_0_0 != 9 {
		t.Errorf("Expected event row 0 new column 0 to be == 9, got: %v", newRow_0_0)
	}

	oldRow_1_0 := events[1].oldRow[0].(int64)
	if oldRow_1_0 != 9 {
		t.Errorf("Expected event row 1 old column 0 to be == 9, got: %v", oldRow_1_0)
	}

	newRow_1_0 := events[1].newRow[0].(int64)
	if newRow_1_0 != 99 {
		t.Errorf("Expected event row 1 new column 0 to be == 99, got: %v", newRow_1_0)
	}

	oldRow_2_0 := events[2].oldRow[0].(int64)
	if oldRow_2_0 != 99 {
		t.Errorf("Expected event row 1 new column 0 to be == 99, got: %v", oldRow_2_0)
	}
}
*/

func TestSerializeDeserialize(t *testing.T) {
	// Connect to the source database.
	srcDb, err := sql.Open(driverName, "file:src?mode=memory&cache=shared")
	if err != nil {
		t.Fatal("Failed to open the source database:", err)
	}
	defer srcDb.Close()
	err = srcDb.Ping()
	if err != nil {
		t.Fatal("Failed to connect to the source database:", err)
	}

	// Connect to the destination database.
	destDb, err := sql.Open(driverName, "file:dst?mode=memory&cache=shared")
	if err != nil {
		t.Fatal("Failed to open the destination database:", err)
	}
	defer destDb.Close()
	err = destDb.Ping()
	if err != nil {
		t.Fatal("Failed to connect to the destination database:", err)
	}

	// Write data to source database.
	_, err = srcDb.Exec(`CREATE TABLE foo (name string)`)
	if err != nil {
		t.Fatal("Failed to create table in source database:", err)
	}
	_, err = srcDb.Exec(`INSERT INTO foo(name) VALUES("alice")`)
	if err != nil {
		t.Fatal("Failed to insert data into source database", err)
	}

	// Serialize the source database
	srcConn, err := srcDb.Conn(context.Background())
	if err != nil {
		t.Fatal("Failed to get connection to source database:", err)
	}
	defer srcConn.Close()

	var serialized []byte
	if err := srcConn.Raw(func(raw any) error {
		var err error
		serialized, err = raw.(*SQLiteConn).Serialize("")
		return err
	}); err != nil {
		t.Fatal("Failed to serialize source database:", err)
	}
	srcConn.Close()

	// Confirm that the destination database is initially empty.
	var destTableCount int
	err = destDb.QueryRow("SELECT COUNT(*) FROM sqlite_master WHERE type = 'table'").Scan(&destTableCount)
	if err != nil {
		t.Fatal("Failed to check the destination table count:", err)
	}
	if destTableCount != 0 {
		t.Fatalf("The destination database is not empty; %v table(s) found.", destTableCount)
	}

	// Deserialize to destination database
	destConn, err := destDb.Conn(context.Background())
	if err != nil {
		t.Fatal("Failed to get connection to destination database:", err)
	}
	defer destConn.Close()

	if err := destConn.Raw(func(raw any) error {
		return raw.(*SQLiteConn).Deserialize(serialized, "")
	}); err != nil {
		t.Fatal("Failed to deserialize source database:", err)
	}
	destConn.Close()

	// Confirm that destination database has been loaded correctly.
	var destRowCount int
	err = destDb.QueryRow(`SELECT COUNT(*) FROM foo`).Scan(&destRowCount)
	if err != nil {
		t.Fatal("Failed to count rows in destination database table", err)
	}
	if destRowCount != 1 {
		t.Fatalf("Destination table does not have the expected records")
	}
}

func TestUnlockNotify(t *testing.T) {
	tempFilename := TempFilename(t)
	defer os.Remove(tempFilename)
	dsn := fmt.Sprintf("file:%s?cache=shared&mode=memory&_busy_timeout=%d", tempFilename, 500)
	db, err := sql.Open("sqlite3", dsn)
	if err != nil {
		t.Fatal("Failed to open database:", err)
	}
	defer db.Close()

	_, err = db.Exec("CREATE TABLE foo(id INTEGER, status INTEGER)")
	if err != nil {
		t.Fatal("Failed to create table:", err)
	}

	tx, err := db.Begin()
	if err != nil {
		t.Fatal("Failed to begin transaction:", err)
	}

	_, err = tx.Exec("INSERT INTO foo(id, status) VALUES(1, 100)")
	if err != nil {
		t.Fatal("Failed to insert null:", err)
	}

	_, err = tx.Exec("UPDATE foo SET status = 200 WHERE id = 1")
	if err != nil {
		t.Fatal("Failed to update table:", err)
	}

	wg := sync.WaitGroup{}
	wg.Add(1)
	timer := time.NewTimer(500 * time.Millisecond)
	go func() {
		<-timer.C
		err := tx.Commit()
		if err != nil {
			t.Fatal("Failed to commit transaction:", err)
		}
		wg.Done()
	}()

	rows, err := db.Query("SELECT count(*) from foo")
	if err != nil {
		t.Fatal("Unable to query foo table:", err)
	}

	if rows.Next() {
		var count int
		if err := rows.Scan(&count); err != nil {
			t.Fatal("Failed to Scan rows", err)
		}
	}
	if err := rows.Err(); err != nil {
		t.Fatal("Failed at the call to Next:", err)
	}
	wg.Wait()

}

func TestUnlockNotifyMany(t *testing.T) {
	tempFilename := TempFilename(t)
	defer os.Remove(tempFilename)
	dsn := fmt.Sprintf("file:%s?cache=shared&mode=memory&_busy_timeout=%d", tempFilename, 500)
	db, err := sql.Open("sqlite3", dsn)
	if err != nil {
		t.Fatal("Failed to open database:", err)
	}
	defer db.Close()

	_, err = db.Exec("CREATE TABLE foo(id INTEGER, status INTEGER)")
	if err != nil {
		t.Fatal("Failed to create table:", err)
	}

	tx, err := db.Begin()
	if err != nil {
		t.Fatal("Failed to begin transaction:", err)
	}

	_, err = tx.Exec("INSERT INTO foo(id, status) VALUES(1, 100)")
	if err != nil {
		t.Fatal("Failed to insert null:", err)
	}

	_, err = tx.Exec("UPDATE foo SET status = 200 WHERE id = 1")
	if err != nil {
		t.Fatal("Failed to update table:", err)
	}

	wg := sync.WaitGroup{}
	wg.Add(1)
	timer := time.NewTimer(500 * time.Millisecond)
	go func() {
		<-timer.C
		err := tx.Commit()
		if err != nil {
			t.Fatal("Failed to commit transaction:", err)
		}
		wg.Done()
	}()

	const concurrentQueries = 1000
	wg.Add(concurrentQueries)
	for i := 0; i < concurrentQueries; i++ {
		go func() {
			rows, err := db.Query("SELECT count(*) from foo")
			if err != nil {
				t.Fatal("Unable to query foo table:", err)
			}

			if rows.Next() {
				var count int
				if err := rows.Scan(&count); err != nil {
					t.Fatal("Failed to Scan rows", err)
				}
			}
			if err := rows.Err(); err != nil {
				t.Fatal("Failed at the call to Next:", err)
			}
			wg.Done()
		}()
	}
	wg.Wait()
}

func TestUnlockNotifyDeadlock(t *testing.T) {
	tempFilename := TempFilename(t)
	defer os.Remove(tempFilename)
	dsn := fmt.Sprintf("file:%s?cache=shared&mode=memory&_busy_timeout=%d", tempFilename, 500)
	db, err := sql.Open("sqlite3", dsn)
	if err != nil {
		t.Fatal("Failed to open database:", err)
	}
	defer db.Close()

	_, err = db.Exec("CREATE TABLE foo(id INTEGER, status INTEGER)")
	if err != nil {
		t.Fatal("Failed to create table:", err)
	}

	tx, err := db.Begin()
	if err != nil {
		t.Fatal("Failed to begin transaction:", err)
	}

	_, err = tx.Exec("INSERT INTO foo(id, status) VALUES(1, 100)")
	if err != nil {
		t.Fatal("Failed to insert null:", err)
	}

	_, err = tx.Exec("UPDATE foo SET status = 200 WHERE id = 1")
	if err != nil {
		t.Fatal("Failed to update table:", err)
	}

	wg := sync.WaitGroup{}
	wg.Add(1)
	timer := time.NewTimer(500 * time.Millisecond)
	go func() {
		<-timer.C
		err := tx.Commit()
		if err != nil {
			t.Fatal("Failed to commit transaction:", err)
		}
		wg.Done()
	}()

	wg.Add(1)
	go func() {
		tx2, err := db.Begin()
		if err != nil {
			t.Fatal("Failed to begin transaction:", err)
		}
		defer tx2.Rollback()

		_, err = tx2.Exec("DELETE FROM foo")
		if err != nil {
			t.Fatal("Failed to delete table:", err)
		}
		err = tx2.Commit()
		if err != nil {
			t.Fatal("Failed to commit transaction:", err)
		}
		wg.Done()
	}()

	rows, err := tx.Query("SELECT count(*) from foo")
	if err != nil {
		t.Fatal("Unable to query foo table:", err)
	}

	if rows.Next() {
		var count int
		if err := rows.Scan(&count); err != nil {
			t.Fatal("Failed to Scan rows", err)
		}
	}
	if err := rows.Err(); err != nil {
		t.Fatal("Failed at the call to Next:", err)
	}

	wg.Wait()
}

func getRowCount(rows *sql.Rows) (int, error) {
	var i int
	for rows.Next() {
		i++
	}
	return i, nil
}

func TempFilename(t testing.TB) string {
	f, err := ioutil.TempFile("", "go-sqlite3-test-")
	if err != nil {
		t.Fatal(err)
	}
	f.Close()
	return f.Name()
}

func doTestOpen(t *testing.T, url string) (string, error) {
	db, err := sql.Open("sqlite3", url)
	if err != nil {
		return "Failed to open database:", err
	}

	defer func() {
		err = db.Close()
		if err != nil {
			t.Error("db close error:", err)
		}
	}()

	err = db.Ping()
	if err != nil {
		return "ping error:", err
	}

	_, err = db.Exec("drop table foo")
	_, err = db.Exec("create table foo (id integer)")
	if err != nil {
		return "Failed to create table:", err
	}

	return "", nil
}

func TestOpen(t *testing.T) {
	cases := map[string]bool{
		"file:open1?mode=memory&cache=shared":                   true,
		"file:open1?mode=memory&cache=shared&_txlock=immediate": true,
		"file:open1?mode=memory&cache=shared&_txlock=deferred":  true,
		"file:open1?mode=memory&cache=shared&_txlock=exclusive": true,
		"file:open1?mode=memory&cache=shared&_txlock=bogus":     false,
	}
	for option, expectedPass := range cases {
		result, err := doTestOpen(t, option)
		if result == "" {
			if !expectedPass {
				errmsg := fmt.Sprintf("_txlock error not caught at dbOpen with option: %s", option)
				t.Fatal(errmsg)
			}
		} else if expectedPass {
			if err == nil {
				t.Fatal(result)
			} else {
				t.Fatal(result, err)
			}
		}
	}
}

func TestOpenWithVFS(t *testing.T) {
	{
	uri := fmt.Sprintf("file:%s?mode=memory&vfs=hello", t.Name())
	db, err := sql.Open("sqlite3", uri)
	if err != nil {
		t.Fatal("Failed to open", err)
	}
	err = db.Ping()
	if err == nil {
		t.Fatal("Failed to open", err)
	}
	db.Close()
	}

	{
	uri := fmt.Sprintf("file:%s?mode=memory&vfs=unix-none", t.Name())
	db, err := sql.Open("sqlite3", uri)
	if err != nil {
		t.Fatal("Failed to open", err)
	}
	err = db.Ping()
	if err != nil {
		t.Fatal("Failed to ping", err)
	}
	db.Close()
	}
}

func TestOpenNoCreate(t *testing.T) {
	filename := t.Name() + ".sqlite"

	if err := os.Remove(filename); err != nil && !os.IsNotExist(err) {
		t.Fatal(err)
	}
	defer os.Remove(filename)

	// https://golang.org/pkg/database/sql/#Open
	// "Open may just validate its arguments without creating a connection
	// to the database. To verify that the data source name is valid, call Ping."
	db, err := sql.Open("sqlite3", fmt.Sprintf("file:%s?mode=rw", filename))
	if err == nil {
		defer db.Close()

		err = db.Ping()
		if err == nil {
			t.Fatal("expected error from Open or Ping")
		}
	}

	sqlErr, ok := err.(Error)
	if !ok {
		t.Fatalf("expected sqlite3.Error, but got %T", err)
	}

	if sqlErr.Code != ErrCantOpen {
		t.Fatalf("expected SQLITE_CANTOPEN, but got %v", sqlErr)
	}

	// make sure database file truly was not created
	if _, err := os.Stat(filename); !os.IsNotExist(err) {
		if err != nil {
			t.Fatal(err)
		}
		t.Fatal("expected database file to not exist")
	}

	// verify that it works if the mode is "rwc" instead
	db, err = sql.Open("sqlite3", fmt.Sprintf("file:%s?mode=rwc", filename))
	if err != nil {
		t.Fatal(err)
	}
	defer db.Close()

	if err := db.Ping(); err != nil {
		t.Fatal(err)
	}

	// make sure database file truly was created
	if _, err := os.Stat(filename); err != nil {
		if !os.IsNotExist(err) {
			t.Fatal(err)
		}
		t.Fatal("expected database file to exist")
	}
}

func TestReadonly(t *testing.T) {
	tempFilename := TempFilename(t)
	defer os.Remove(tempFilename)

	db1, err := sql.Open("sqlite3", "file:"+tempFilename)
	if err != nil {
		t.Fatal(err)
	}
	defer db1.Close()
	db1.Exec("CREATE TABLE test (x int, y float)")

	db2, err := sql.Open("sqlite3", "file:"+tempFilename+"?mode=ro")
	if err != nil {
		t.Fatal(err)
	}
	defer db2.Close()
	_ = db2
	_, err = db2.Exec("INSERT INTO test VALUES (1, 3.14)")
	if err == nil {
		t.Fatal("didn't expect INSERT into read-only database to work")
	}
}

func TestDeferredForeignKey(t *testing.T) {
	fname := TempFilename(t)
	uri := "file:" + fname + "?_foreign_keys=1&mode=memory"
	db, err := sql.Open("sqlite3", uri)
	if err != nil {
		os.Remove(fname)
		t.Errorf("sql.Open(\"sqlite3\", %q): %v", uri, err)
	}
	_, err = db.Exec("CREATE TABLE bar (id INTEGER PRIMARY KEY)")
	if err != nil {
		t.Errorf("failed creating tables: %v", err)
	}
	_, err = db.Exec("CREATE TABLE foo (bar_id INTEGER, FOREIGN KEY(bar_id) REFERENCES bar(id) DEFERRABLE INITIALLY DEFERRED)")
	if err != nil {
		t.Errorf("failed creating tables: %v", err)
	}
	tx, err := db.Begin()
	if err != nil {
		t.Errorf("Failed to begin transaction: %v", err)
	}
	_, err = tx.Exec("INSERT INTO foo (bar_id) VALUES (123)")
	if err != nil {
		t.Errorf("Failed to insert row: %v", err)
	}
	err = tx.Commit()
	if err == nil {
		t.Errorf("Expected an error: %v", err)
	}
	_, err = db.Begin()
	if err != nil {
		t.Errorf("Failed to begin transaction: %v", err)
	}

	db.Close()
	os.Remove(fname)
}

func TestRecursiveTriggers(t *testing.T) {
	cases := map[string]bool{
		"?_recursive_triggers=1": true,
		"?_recursive_triggers=0": false,
	}
	for option, want := range cases {
		fname := TempFilename(t)
		uri := "file:" + fname + option + "&mode=memory"
		db, err := sql.Open("sqlite3", uri)
		if err != nil {
			os.Remove(fname)
			t.Errorf("sql.Open(\"sqlite3\", %q): %v", uri, err)
			continue
		}
		var enabled bool
		err = db.QueryRow("PRAGMA recursive_triggers;").Scan(&enabled)
		db.Close()
		os.Remove(fname)
		if err != nil {
			t.Errorf("query recursive_triggers for %s: %v", uri, err)
			continue
		}
		if enabled != want {
			t.Errorf("\"PRAGMA recursive_triggers;\" for %q = %t; want %t", uri, enabled, want)
			continue
		}
	}
}

func TestClose(t *testing.T) {
	db, err := sql.Open("sqlite3", ":memory:")
	if err != nil {
		t.Fatal("Failed to open database:", err)
	}

	_, err = db.Exec("drop table foo")
	_, err = db.Exec("create table foo (id integer)")
	if err != nil {
		t.Fatal("Failed to create table:", err)
	}

	stmt, err := db.Prepare("select id from foo where id = ?")
	if err != nil {
		t.Fatal("Failed to select records:", err)
	}

	db.Close()
	_, err = stmt.Exec(1)
	if err == nil {
		t.Fatal("Failed to operate closed statement")
	}
}

func TestInsert(t *testing.T) {
	db, err := sql.Open("sqlite3", ":memory:")
	if err != nil {
		t.Fatal("Failed to open database:", err)
	}
	defer db.Close()

	_, err = db.Exec("drop table foo")
	_, err = db.Exec("create table foo (id integer)")
	if err != nil {
		t.Fatal("Failed to create table:", err)
	}

	res, err := db.Exec("insert into foo(id) values(123)")
	if err != nil {
		t.Fatal("Failed to insert record:", err)
	}
	affected, _ := res.RowsAffected()
	if affected != 1 {
		t.Fatalf("Expected %d for affected rows, but %d:", 1, affected)
	}

	rows, err := db.Query("select id from foo")
	if err != nil {
		t.Fatal("Failed to select records:", err)
	}
	defer rows.Close()

	rows.Next()

	var result int
	rows.Scan(&result)
	if result != 123 {
		t.Errorf("Expected %d for fetched result, but %d:", 123, result)
	}
}

func TestUpsert(t *testing.T) {
	_, n, _ := LibVersion()
	if n < 3024000 {
		t.Skip("UPSERT requires sqlite3 >= 3.24.0")
	}
	db, err := sql.Open("sqlite3", ":memory:")
	if err != nil {
		t.Fatal("Failed to open database:", err)
	}
	defer db.Close()

	_, err = db.Exec("drop table foo")
	_, err = db.Exec("create table foo (name string primary key, counter integer)")
	if err != nil {
		t.Fatal("Failed to create table:", err)
	}

	for i := 0; i < 10; i++ {
		res, err := db.Exec("insert into foo(name, counter) values('key', 1) on conflict (name) do update set counter=counter+1")
		if err != nil {
			t.Fatal("Failed to upsert record:", err)
		}
		affected, _ := res.RowsAffected()
		if affected != 1 {
			t.Fatalf("Expected %d for affected rows, but %d:", 1, affected)
		}
	}
	rows, err := db.Query("select name, counter from foo")
	if err != nil {
		t.Fatal("Failed to select records:", err)
	}
	defer rows.Close()

	rows.Next()

	var resultName string
	var resultCounter int
	rows.Scan(&resultName, &resultCounter)
	if resultName != "key" {
		t.Errorf("Expected %s for fetched result, but %s:", "key", resultName)
	}
	if resultCounter != 10 {
		t.Errorf("Expected %d for fetched result, but %d:", 10, resultCounter)
	}

}

func TestUpdate(t *testing.T) {
	db, err := sql.Open("sqlite3", ":memory:")
	if err != nil {
		t.Fatal("Failed to open database:", err)
	}
	defer db.Close()

	_, err = db.Exec("drop table foo")
	_, err = db.Exec("create table foo (id integer)")
	if err != nil {
		t.Fatal("Failed to create table:", err)
	}

	res, err := db.Exec("insert into foo(id) values(123)")
	if err != nil {
		t.Fatal("Failed to insert record:", err)
	}
	expected, err := res.LastInsertId()
	if err != nil {
		t.Fatal("Failed to get LastInsertId:", err)
	}
	affected, _ := res.RowsAffected()
	if err != nil {
		t.Fatal("Failed to get RowsAffected:", err)
	}
	if affected != 1 {
		t.Fatalf("Expected %d for affected rows, but %d:", 1, affected)
	}

	res, err = db.Exec("update foo set id = 234")
	if err != nil {
		t.Fatal("Failed to update record:", err)
	}
	lastID, err := res.LastInsertId()
	if err != nil {
		t.Fatal("Failed to get LastInsertId:", err)
	}
	if expected != lastID {
		t.Errorf("Expected %q for last Id, but %q:", expected, lastID)
	}
	affected, _ = res.RowsAffected()
	if err != nil {
		t.Fatal("Failed to get RowsAffected:", err)
	}
	if affected != 1 {
		t.Fatalf("Expected %d for affected rows, but %d:", 1, affected)
	}

	rows, err := db.Query("select id from foo")
	if err != nil {
		t.Fatal("Failed to select records:", err)
	}
	defer rows.Close()

	rows.Next()

	var result int
	rows.Scan(&result)
	if result != 234 {
		t.Errorf("Expected %d for fetched result, but %d:", 234, result)
	}
}

func TestDelete(t *testing.T) {
	db, err := sql.Open("sqlite3", ":memory:")
	if err != nil {
		t.Fatal("Failed to open database:", err)
	}
	defer db.Close()

	_, err = db.Exec("drop table foo")
	_, err = db.Exec("create table foo (id integer)")
	if err != nil {
		t.Fatal("Failed to create table:", err)
	}

	res, err := db.Exec("insert into foo(id) values(123)")
	if err != nil {
		t.Fatal("Failed to insert record:", err)
	}
	expected, err := res.LastInsertId()
	if err != nil {
		t.Fatal("Failed to get LastInsertId:", err)
	}
	affected, err := res.RowsAffected()
	if err != nil {
		t.Fatal("Failed to get RowsAffected:", err)
	}
	if affected != 1 {
		t.Errorf("Expected %d for cout of affected rows, but %q:", 1, affected)
	}

	res, err = db.Exec("delete from foo where id = 123")
	if err != nil {
		t.Fatal("Failed to delete record:", err)
	}
	lastID, err := res.LastInsertId()
	if err != nil {
		t.Fatal("Failed to get LastInsertId:", err)
	}
	if expected != lastID {
		t.Errorf("Expected %q for last Id, but %q:", expected, lastID)
	}
	affected, err = res.RowsAffected()
	if err != nil {
		t.Fatal("Failed to get RowsAffected:", err)
	}
	if affected != 1 {
		t.Errorf("Expected %d for cout of affected rows, but %q:", 1, affected)
	}

	rows, err := db.Query("select id from foo")
	if err != nil {
		t.Fatal("Failed to select records:", err)
	}
	defer rows.Close()

	if rows.Next() {
		t.Error("Fetched row but expected not rows")
	}
}

func TestBooleanRoundtrip(t *testing.T) {
	db, err := sql.Open("sqlite3", ":memory:")
	if err != nil {
		t.Fatal("Failed to open database:", err)
	}
	defer db.Close()

	_, err = db.Exec("DROP TABLE foo")
	_, err = db.Exec("CREATE TABLE foo(id INTEGER, value BOOL)")
	if err != nil {
		t.Fatal("Failed to create table:", err)
	}

	_, err = db.Exec("INSERT INTO foo(id, value) VALUES(1, ?)", true)
	if err != nil {
		t.Fatal("Failed to insert true value:", err)
	}

	_, err = db.Exec("INSERT INTO foo(id, value) VALUES(2, ?)", false)
	if err != nil {
		t.Fatal("Failed to insert false value:", err)
	}

	rows, err := db.Query("SELECT id, value FROM foo")
	if err != nil {
		t.Fatal("Unable to query foo table:", err)
	}
	defer rows.Close()

	for rows.Next() {
		var id int
		var value bool

		if err := rows.Scan(&id, &value); err != nil {
			t.Error("Unable to scan results:", err)
			continue
		}

		if id == 1 && !value {
			t.Error("Value for id 1 should be true, not false")

		} else if id == 2 && value {
			t.Error("Value for id 2 should be false, not true")
		}
	}
}

func timezone(t time.Time) string { return t.Format("-07:00") }

func TestTimestamp(t *testing.T) {
	db, err := sql.Open("sqlite3", ":memory:")
	if err != nil {
		t.Fatal("Failed to open database:", err)
	}
	defer db.Close()

	_, err = db.Exec("DROP TABLE foo")
	_, err = db.Exec("CREATE TABLE foo(id INTEGER, ts timeSTAMP, dt DATETIME)")
	if err != nil {
		t.Fatal("Failed to create table:", err)
	}

	timestamp1 := time.Date(2012, time.April, 6, 22, 50, 0, 0, time.UTC)
	timestamp2 := time.Date(2006, time.January, 2, 15, 4, 5, 123456789, time.UTC)
	timestamp3 := time.Date(2012, time.November, 4, 0, 0, 0, 0, time.UTC)
	tzTest := time.FixedZone("TEST", -9*3600-13*60)
	tests := []struct {
		value    any
		expected time.Time
	}{
		{"nonsense", time.Time{}},
		{"0000-00-00 00:00:00", time.Time{}},
		{time.Time{}.Unix(), time.Time{}},
		{timestamp1, timestamp1},
		{timestamp2.Unix(), timestamp2.Truncate(time.Second)},
		{timestamp2.UnixNano() / int64(time.Millisecond), timestamp2.Truncate(time.Millisecond)},
		{timestamp1.In(tzTest), timestamp1.In(tzTest)},
		{timestamp1.Format("2006-01-02 15:04:05.000"), timestamp1},
		{timestamp1.Format("2006-01-02T15:04:05.000"), timestamp1},
		{timestamp1.Format("2006-01-02 15:04:05"), timestamp1},
		{timestamp1.Format("2006-01-02T15:04:05"), timestamp1},
		{timestamp2, timestamp2},
		{"2006-01-02 15:04:05.123456789", timestamp2},
		{"2006-01-02T15:04:05.123456789", timestamp2},
		{"2006-01-02T05:51:05.123456789-09:13", timestamp2.In(tzTest)},
		{"2012-11-04", timestamp3},
		{"2012-11-04 00:00", timestamp3},
		{"2012-11-04 00:00:00", timestamp3},
		{"2012-11-04 00:00:00.000", timestamp3},
		{"2012-11-04T00:00", timestamp3},
		{"2012-11-04T00:00:00", timestamp3},
		{"2012-11-04T00:00:00.000", timestamp3},
		{"2006-01-02T15:04:05.123456789Z", timestamp2},
		{"2012-11-04Z", timestamp3},
		{"2012-11-04 00:00Z", timestamp3},
		{"2012-11-04 00:00:00Z", timestamp3},
		{"2012-11-04 00:00:00.000Z", timestamp3},
		{"2012-11-04T00:00Z", timestamp3},
		{"2012-11-04T00:00:00Z", timestamp3},
		{"2012-11-04T00:00:00.000Z", timestamp3},
	}
	for i := range tests {
		_, err = db.Exec("INSERT INTO foo(id, ts, dt) VALUES(?, ?, ?)", i, tests[i].value, tests[i].value)
		if err != nil {
			t.Fatal("Failed to insert timestamp:", err)
		}
	}

	rows, err := db.Query("SELECT id, ts, dt FROM foo ORDER BY id ASC")
	if err != nil {
		t.Fatal("Unable to query foo table:", err)
	}
	defer rows.Close()

	seen := 0
	for rows.Next() {
		var id int
		var ts, dt time.Time

		if err := rows.Scan(&id, &ts, &dt); err != nil {
			t.Error("Unable to scan results:", err)
			continue
		}
		if id < 0 || id >= len(tests) {
			t.Error("Bad row id: ", id)
			continue
		}
		seen++
		if !tests[id].expected.Equal(ts) {
			t.Errorf("Timestamp value for id %v (%v) should be %v, not %v", id, tests[id].value, tests[id].expected, dt)
		}
		if !tests[id].expected.Equal(dt) {
			t.Errorf("Datetime value for id %v (%v) should be %v, not %v", id, tests[id].value, tests[id].expected, dt)
		}
		if timezone(tests[id].expected) != timezone(ts) {
			t.Errorf("Timezone for id %v (%v) should be %v, not %v", id, tests[id].value,
				timezone(tests[id].expected), timezone(ts))
		}
		if timezone(tests[id].expected) != timezone(dt) {
			t.Errorf("Timezone for id %v (%v) should be %v, not %v", id, tests[id].value,
				timezone(tests[id].expected), timezone(dt))
		}
	}

	if seen != len(tests) {
		t.Errorf("Expected to see %d rows", len(tests))
	}
}

func TestBoolean(t *testing.T) {
	db, err := sql.Open("sqlite3", ":memory:")
	if err != nil {
		t.Fatal("Failed to open database:", err)
	}

	defer db.Close()

	_, err = db.Exec("CREATE TABLE foo(id INTEGER, fbool BOOLEAN)")
	if err != nil {
		t.Fatal("Failed to create table:", err)
	}

	bool1 := true
	_, err = db.Exec("INSERT INTO foo(id, fbool) VALUES(1, ?)", bool1)
	if err != nil {
		t.Fatal("Failed to insert boolean:", err)
	}

	bool2 := false
	_, err = db.Exec("INSERT INTO foo(id, fbool) VALUES(2, ?)", bool2)
	if err != nil {
		t.Fatal("Failed to insert boolean:", err)
	}

	bool3 := "nonsense"
	_, err = db.Exec("INSERT INTO foo(id, fbool) VALUES(3, ?)", bool3)
	if err != nil {
		t.Fatal("Failed to insert nonsense:", err)
	}

	rows, err := db.Query("SELECT id, fbool FROM foo where fbool = ?", bool1)
	if err != nil {
		t.Fatal("Unable to query foo table:", err)
	}
	counter := 0

	var id int
	var fbool bool

	for rows.Next() {
		if err := rows.Scan(&id, &fbool); err != nil {
			t.Fatal("Unable to scan results:", err)
		}
		counter++
	}

	if counter != 1 {
		t.Fatalf("Expected 1 row but %v", counter)
	}

	if id != 1 && !fbool {
		t.Fatalf("Value for id 1 should be %v, not %v", bool1, fbool)
	}

	rows, err = db.Query("SELECT id, fbool FROM foo where fbool = ?", bool2)
	if err != nil {
		t.Fatal("Unable to query foo table:", err)
	}

	counter = 0

	for rows.Next() {
		if err := rows.Scan(&id, &fbool); err != nil {
			t.Fatal("Unable to scan results:", err)
		}
		counter++
	}

	if counter != 1 {
		t.Fatalf("Expected 1 row but %v", counter)
	}

	if id != 2 && fbool {
		t.Fatalf("Value for id 2 should be %v, not %v", bool2, fbool)
	}

	// make sure "nonsense" triggered an error
	rows, err = db.Query("SELECT id, fbool FROM foo where id=?;", 3)
	if err != nil {
		t.Fatal("Unable to query foo table:", err)
	}

	rows.Next()
	err = rows.Scan(&id, &fbool)
	if err == nil {
		t.Error("Expected error from \"nonsense\" bool")
	}
}

func TestFloat32(t *testing.T) {
	db, err := sql.Open("sqlite3", ":memory:")
	if err != nil {
		t.Fatal("Failed to open database:", err)
	}
	defer db.Close()

	_, err = db.Exec("CREATE TABLE foo(id INTEGER)")
	if err != nil {
		t.Fatal("Failed to create table:", err)
	}

	_, err = db.Exec("INSERT INTO foo(id) VALUES(null)")
	if err != nil {
		t.Fatal("Failed to insert null:", err)
	}

	rows, err := db.Query("SELECT id FROM foo")
	if err != nil {
		t.Fatal("Unable to query foo table:", err)
	}

	if !rows.Next() {
		t.Fatal("Unable to query results:", err)
	}

	var id any
	if err := rows.Scan(&id); err != nil {
		t.Fatal("Unable to scan results:", err)
	}
	if id != nil {
		t.Error("Expected nil but not")
	}
}

func TestNull(t *testing.T) {
	db, err := sql.Open("sqlite3", ":memory:")
	if err != nil {
		t.Fatal("Failed to open database:", err)
	}
	defer db.Close()

	rows, err := db.Query("SELECT 3.141592")
	if err != nil {
		t.Fatal("Unable to query foo table:", err)
	}

	if !rows.Next() {
		t.Fatal("Unable to query results:", err)
	}

	var v any
	if err := rows.Scan(&v); err != nil {
		t.Fatal("Unable to scan results:", err)
	}
	f, ok := v.(float64)
	if !ok {
		t.Error("Expected float but not")
	}
	if f != 3.141592 {
		t.Error("Expected 3.141592 but not")
	}
}

func TestTransaction(t *testing.T) {
	db, err := sql.Open("sqlite3", ":memory:")
	if err != nil {
		t.Fatal("Failed to open database:", err)
	}
	defer db.Close()

	_, err = db.Exec("CREATE TABLE foo(id INTEGER)")
	if err != nil {
		t.Fatal("Failed to create table:", err)
	}

	tx, err := db.Begin()
	if err != nil {
		t.Fatal("Failed to begin transaction:", err)
	}

	_, err = tx.Exec("INSERT INTO foo(id) VALUES(1)")
	if err != nil {
		t.Fatal("Failed to insert null:", err)
	}

	rows, err := tx.Query("SELECT id from foo")
	if err != nil {
		t.Fatal("Unable to query foo table:", err)
	}

	err = tx.Rollback()
	if err != nil {
		t.Fatal("Failed to rollback transaction:", err)
	}

	if rows.Next() {
		t.Fatal("Unable to query results:", err)
	}

	tx, err = db.Begin()
	if err != nil {
		t.Fatal("Failed to begin transaction:", err)
	}

	_, err = tx.Exec("INSERT INTO foo(id) VALUES(1)")
	if err != nil {
		t.Fatal("Failed to insert null:", err)
	}

	err = tx.Commit()
	if err != nil {
		t.Fatal("Failed to commit transaction:", err)
	}

	rows, err = tx.Query("SELECT id from foo")
	if err == nil {
		t.Fatal("Expected failure to query")
	}
}

func TestWAL(t *testing.T) {
	db, err := sql.Open("sqlite3", ":memory:")
	if err != nil {
		t.Fatal("Failed to open database:", err)
	}
	defer db.Close()

	if _, err = db.Exec("PRAGMA journal_mode=WAL;"); err != nil {
		t.Fatal("Failed to Exec PRAGMA journal_mode:", err)
	}
	if _, err = db.Exec("PRAGMA locking_mode=EXCLUSIVE;"); err != nil {
		t.Fatal("Failed to Exec PRAGMA locking_mode:", err)
	}
	if _, err = db.Exec("CREATE TABLE test (id SERIAL, user TEXT NOT NULL, name TEXT NOT NULL);"); err != nil {
		t.Fatal("Failed to Exec CREATE TABLE:", err)
	}
	if _, err = db.Exec("INSERT INTO test (user, name) VALUES ('user','name');"); err != nil {
		t.Fatal("Failed to Exec INSERT:", err)
	}

	trans, err := db.Begin()
	if err != nil {
		t.Fatal("Failed to Begin:", err)
	}
	s, err := trans.Prepare("INSERT INTO test (user, name) VALUES (?, ?);")
	if err != nil {
		t.Fatal("Failed to Prepare:", err)
	}

	var count int
	if err = trans.QueryRow("SELECT count(user) FROM test;").Scan(&count); err != nil {
		t.Fatal("Failed to QueryRow:", err)
	}
	if _, err = s.Exec("bbbb", "aaaa"); err != nil {
		t.Fatal("Failed to Exec prepared statement:", err)
	}
	if err = s.Close(); err != nil {
		t.Fatal("Failed to Close prepared statement:", err)
	}
	if err = trans.Commit(); err != nil {
		t.Fatal("Failed to Commit:", err)
	}
}

func TestTimezoneConversion(t *testing.T) {
	zones := []string{"UTC", "US/Central", "US/Pacific", "Local"}
	for _, tz := range zones {
		db, err := sql.Open("sqlite3", "file:tz?mode=memory&_loc="+url.QueryEscape(tz))
		if err != nil {
			t.Fatal("Failed to open database:", err)
		}
		defer db.Close()

		_, err = db.Exec("DROP TABLE foo")
		_, err = db.Exec("CREATE TABLE foo(id INTEGER, ts TIMESTAMP, dt DATETIME)")
		if err != nil {
			t.Fatal("Failed to create table:", err)
		}

		loc, err := time.LoadLocation(tz)
		if err != nil {
			t.Fatal("Failed to load location:", err)
		}

		timestamp1 := time.Date(2012, time.April, 6, 22, 50, 0, 0, time.UTC)
		timestamp2 := time.Date(2006, time.January, 2, 15, 4, 5, 123456789, time.UTC)
		timestamp3 := time.Date(2012, time.November, 4, 0, 0, 0, 0, time.UTC)
		tests := []struct {
			value    any
			expected time.Time
		}{
			{"nonsense", time.Time{}.In(loc)},
			{"0000-00-00 00:00:00", time.Time{}.In(loc)},
			{timestamp1, timestamp1.In(loc)},
			{timestamp1.Unix(), timestamp1.In(loc)},
			{timestamp1.In(time.FixedZone("TEST", -7*3600)), timestamp1.In(loc)},
			{timestamp1.Format("2006-01-02 15:04:05.000"), timestamp1.In(loc)},
			{timestamp1.Format("2006-01-02T15:04:05.000"), timestamp1.In(loc)},
			{timestamp1.Format("2006-01-02 15:04:05"), timestamp1.In(loc)},
			{timestamp1.Format("2006-01-02T15:04:05"), timestamp1.In(loc)},
			{timestamp2, timestamp2.In(loc)},
			{"2006-01-02 15:04:05.123456789", timestamp2.In(loc)},
			{"2006-01-02T15:04:05.123456789", timestamp2.In(loc)},
			{"2012-11-04", timestamp3.In(loc)},
			{"2012-11-04 00:00", timestamp3.In(loc)},
			{"2012-11-04 00:00:00", timestamp3.In(loc)},
			{"2012-11-04 00:00:00.000", timestamp3.In(loc)},
			{"2012-11-04T00:00", timestamp3.In(loc)},
			{"2012-11-04T00:00:00", timestamp3.In(loc)},
			{"2012-11-04T00:00:00.000", timestamp3.In(loc)},
		}
		for i := range tests {
			_, err = db.Exec("INSERT INTO foo(id, ts, dt) VALUES(?, ?, ?)", i, tests[i].value, tests[i].value)
			if err != nil {
				t.Fatal("Failed to insert timestamp:", err)
			}
		}

		rows, err := db.Query("SELECT id, ts, dt FROM foo ORDER BY id ASC")
		if err != nil {
			t.Fatal("Unable to query foo table:", err)
		}
		defer rows.Close()

		seen := 0
		for rows.Next() {
			var id int
			var ts, dt time.Time

			if err := rows.Scan(&id, &ts, &dt); err != nil {
				t.Error("Unable to scan results:", err)
				continue
			}
			if id < 0 || id >= len(tests) {
				t.Error("Bad row id: ", id)
				continue
			}
			seen++
			if !tests[id].expected.Equal(ts) {
				t.Errorf("Timestamp value for id %v (%v) should be %v, not %v", id, tests[id].value, tests[id].expected, ts)
			}
			if !tests[id].expected.Equal(dt) {
				t.Errorf("Datetime value for id %v (%v) should be %v, not %v", id, tests[id].value, tests[id].expected, dt)
			}
			if tests[id].expected.Location().String() != ts.Location().String() {
				t.Errorf("Location for id %v (%v) should be %v, not %v", id, tests[id].value, tests[id].expected.Location().String(), ts.Location().String())
			}
			if tests[id].expected.Location().String() != dt.Location().String() {
				t.Errorf("Location for id %v (%v) should be %v, not %v", id, tests[id].value, tests[id].expected.Location().String(), dt.Location().String())
			}
		}

		if seen != len(tests) {
			t.Errorf("Expected to see %d rows", len(tests))
		}
	}
}

// TODO: Execer & Queryer currently disabled
// https://github.com/mattn/go-sqlite3/issues/82
func TestExecer(t *testing.T) {
	db, err := sql.Open("sqlite3", ":memory:")
	if err != nil {
		t.Fatal("Failed to open database:", err)
	}
	defer db.Close()

	_, err = db.Exec(`
       create table foo (id integer); -- one comment
       insert into foo(id) values(?);
       insert into foo(id) values(?);
       insert into foo(id) values(?); -- another comment
       `, 1, 2, 3)
	if err != nil {
		t.Error("Failed to call db.Exec:", err)
	}
}

func TestQueryer(t *testing.T) {
	db, err := sql.Open("sqlite3", ":memory:")
	if err != nil {
		t.Fatal("Failed to open database:", err)
	}
	defer db.Close()

	_, err = db.Exec(`
		create table foo (id integer);
	`)
	if err != nil {
		t.Error("Failed to call db.Query:", err)
	}

	_, err = db.Exec(`
		insert into foo(id) values(?);
		insert into foo(id) values(?);
		insert into foo(id) values(?);
	`, 3, 2, 1)
	if err != nil {
		t.Error("Failed to call db.Exec:", err)
	}
	rows, err := db.Query(`
		select id from foo order by id;
	`)
	if err != nil {
		t.Error("Failed to call db.Query:", err)
	}
	defer rows.Close()
	n := 0
	for rows.Next() {
		var id int
		err = rows.Scan(&id)
		if err != nil {
			t.Error("Failed to db.Query:", err)
		}
		if id != n+1 {
			t.Error("Failed to db.Query: not matched results")
		}
		n = n + 1
	}
	if err := rows.Err(); err != nil {
		t.Errorf("Post-scan failed: %v\n", err)
	}
	if n != 3 {
		t.Errorf("Expected 3 rows but retrieved %v", n)
	}
}

func TestStress(t *testing.T) {
	db, err := sql.Open("sqlite3", ":memory:")
	if err != nil {
		t.Fatal("Failed to open database:", err)
	}
	defer db.Close()
	db.Exec("CREATE TABLE foo (id int);")
	db.Exec("INSERT INTO foo VALUES(1);")
	db.Exec("INSERT INTO foo VALUES(2);")

	for i := 0; i < 10000; i++ {
		for j := 0; j < 3; j++ {
			rows, err := db.Query("select * from foo where id=1;")
			if err != nil {
				t.Error("Failed to call db.Query:", err)
			}
			for rows.Next() {
				var i int
				if err := rows.Scan(&i); err != nil {
					t.Errorf("Scan failed: %v\n", err)
				}
			}
			if err := rows.Err(); err != nil {
				t.Errorf("Post-scan failed: %v\n", err)
			}
			rows.Close()
		}
	}
}

func TestDateTimeLocal(t *testing.T) {
	const zone = "Asia/Tokyo"
	tempFilename := TempFilename(t)
	defer os.Remove(tempFilename)
	filename1 := tempFilename + "?mode=memory&cache=shared"
	filename2 := filename1 + "&_loc=" + zone
	db1, err := sql.Open("sqlite3", filename2)
	if err != nil {
		t.Fatal("Failed to open database:", err)
	}
	defer db1.Close()
	db1.Exec("CREATE TABLE foo (dt datetime);")
	db1.Exec("INSERT INTO foo VALUES('2015-03-05 15:16:17');")

	row := db1.QueryRow("select * from foo")
	var d time.Time
	err = row.Scan(&d)
	if err != nil {
		t.Fatal("Failed to scan datetime:", err)
	}
	if d.Hour() == 15 || !strings.Contains(d.String(), "JST") {
		t.Fatal("Result should have timezone", d)
	}

	db2, err := sql.Open("sqlite3", filename1)
	if err != nil {
		t.Fatal("Failed to open database:", err)
	}
	defer db2.Close()

	row = db2.QueryRow("select * from foo")
	err = row.Scan(&d)
	if err != nil {
		t.Fatal("Failed to scan datetime:", err)
	}
	if d.UTC().Hour() != 15 || !strings.Contains(d.String(), "UTC") {
		t.Fatalf("Result should not have timezone %v %v", zone, d.String())
	}

	_, err = db2.Exec("DELETE FROM foo")
	if err != nil {
		t.Fatal("Failed to delete table:", err)
	}
	dt, err := time.Parse("2006/1/2 15/4/5 -0700 MST", "2015/3/5 15/16/17 +0900 JST")
	if err != nil {
		t.Fatal("Failed to parse datetime:", err)
	}
	db2.Exec("INSERT INTO foo VALUES(?);", dt)

	db3, err := sql.Open("sqlite3", filename2)
	if err != nil {
		t.Fatal("Failed to open database:", err)
	}
	defer db3.Close()

	row = db3.QueryRow("select * from foo")
	err = row.Scan(&d)
	if err != nil {
		t.Fatal("Failed to scan datetime:", err)
	}
	if d.Hour() != 15 || !strings.Contains(d.String(), "JST") {
		t.Fatalf("Result should have timezone %v %v", zone, d.String())
	}
}

func TestVersion(t *testing.T) {
	s, n, id := LibVersion()
	if s == "" || n == 0 || id == "" {
		t.Errorf("Version failed %q, %d, %q\n", s, n, id)
	}
}

func TestStringContainingZero(t *testing.T) {
	db, err := sql.Open("sqlite3", ":memory:")
	if err != nil {
		t.Fatal("Failed to open database:", err)
	}
	defer db.Close()

	_, err = db.Exec(`
	create table foo (id integer, name, extra text);
	`)
	if err != nil {
		t.Error("Failed to call db.Query:", err)
	}

	const text = "foo\x00bar"

	_, err = db.Exec(`insert into foo(id, name, extra) values($1, $2, $2)`, 1, text)
	if err != nil {
		t.Error("Failed to call db.Exec:", err)
	}

	row := db.QueryRow(`select id, extra from foo where id = $1 and extra = $2`, 1, text)
	if row == nil {
		t.Error("Failed to call db.QueryRow")
	}

	var id int
	var extra string
	err = row.Scan(&id, &extra)
	if err != nil {
		t.Error("Failed to db.Scan:", err)
	}
	if id != 1 || extra != text {
		t.Error("Failed to db.QueryRow: not matched results")
	}
}

const CurrentTimeStamp = "2006-01-02 15:04:05"

type TimeStamp struct{ *time.Time }

func (t TimeStamp) Scan(value any) error {
	var err error
	switch v := value.(type) {
	case string:
		*t.Time, err = time.Parse(CurrentTimeStamp, v)
	case []byte:
		*t.Time, err = time.Parse(CurrentTimeStamp, string(v))
	default:
		err = errors.New("invalid type for current_timestamp")
	}
	return err
}

func (t TimeStamp) Value() (driver.Value, error) {
	return t.Time.Format(CurrentTimeStamp), nil
}

func TestDateTimeNow(t *testing.T) {
	db, err := sql.Open("sqlite3", ":memory:")
	if err != nil {
		t.Fatal("Failed to open database:", err)
	}
	defer db.Close()

	var d time.Time
	err = db.QueryRow("SELECT datetime('now')").Scan(TimeStamp{&d})
	if err != nil {
		t.Fatal("Failed to scan datetime:", err)
	}
}

type sumAggregator int64

func (s *sumAggregator) Step(x int64) {
	*s += sumAggregator(x)
}

func (s *sumAggregator) Done() int64 {
	return int64(*s)
}

func TestAggregatorRegistration(t *testing.T) {
	customSum := func() *sumAggregator {
		var ret sumAggregator
		return &ret
	}

	sql.Register("sqlite3_AggregatorRegistration", &SQLiteDriver{
		ConnectHook: func(conn *SQLiteConn) error {
			return conn.RegisterAggregator("customSum", customSum, true)
		},
	})
	db, err := sql.Open("sqlite3_AggregatorRegistration", ":memory:")
	if err != nil {
		t.Fatal("Failed to open database:", err)
	}
	defer db.Close()

	_, err = db.Exec("create table foo (department integer, profits integer)")
	if err != nil {
		// trace feature is not implemented
		t.Skip("Failed to create table:", err)
	}

	_, err = db.Exec("insert into foo values (1, 10), (1, 20), (2, 42)")
	if err != nil {
		t.Fatal("Failed to insert records:", err)
	}

	tests := []struct {
		dept, sum int64
	}{
		{1, 30},
		{2, 42},
	}

	for _, test := range tests {
		var ret int64
		err = db.QueryRow("select customSum(profits) from foo where department = $1 group by department", test.dept).Scan(&ret)
		if err != nil {
			t.Fatal("Query failed:", err)
		}
		if ret != test.sum {
			t.Fatalf("Custom sum returned wrong value, got %d, want %d", ret, test.sum)
		}
	}
}

type mode struct {
	counts   map[any]int
	top      any
	topCount int
}

func newMode() *mode {
	return &mode{
		counts: map[any]int{},
	}
}

func (m *mode) Step(x any) {
	m.counts[x]++
	c := m.counts[x]
	if c > m.topCount {
		m.top = x
		m.topCount = c
	}
}

func (m *mode) Done() any {
	return m.top
}

func TestAggregatorRegistration_GenericReturn(t *testing.T) {
	sql.Register("sqlite3_AggregatorRegistration_GenericReturn", &SQLiteDriver{
		ConnectHook: func(conn *SQLiteConn) error {
			return conn.RegisterAggregator("mode", newMode, true)
		},
	})
	db, err := sql.Open("sqlite3_AggregatorRegistration_GenericReturn", ":memory:")
	if err != nil {
		t.Fatal("Failed to open database:", err)
	}
	defer db.Close()

	_, err = db.Exec("create table foo (department integer, profits integer)")
	if err != nil {
		t.Fatal("Failed to create table:", err)
	}
	_, err = db.Exec("insert into foo values (1, 10), (1, 20), (1, 45), (2, 42), (2, 115), (2, 20)")
	if err != nil {
		t.Fatal("Failed to insert records:", err)
	}

	var mode int
	err = db.QueryRow("select mode(profits) from foo").Scan(&mode)
	if err != nil {
		t.Fatal("MODE query error:", err)
	}

	if mode != 20 {
		t.Fatal("Got incorrect mode. Wanted 20, got: ", mode)
	}
}

func rot13(r rune) rune {
	switch {
	case r >= 'A' && r <= 'Z':
		return 'A' + (r-'A'+13)%26
	case r >= 'a' && r <= 'z':
		return 'a' + (r-'a'+13)%26
	}
	return r
}

func TestCollationRegistration(t *testing.T) {
	collateRot13 := func(a, b string) int {
		ra, rb := strings.Map(rot13, a), strings.Map(rot13, b)
		return strings.Compare(ra, rb)
	}
	collateRot13Reverse := func(a, b string) int {
		return collateRot13(b, a)
	}

	sql.Register("sqlite3_CollationRegistration", &SQLiteDriver{
		ConnectHook: func(conn *SQLiteConn) error {
			if err := conn.RegisterCollation("rot13", collateRot13); err != nil {
				return err
			}
			if err := conn.RegisterCollation("rot13reverse", collateRot13Reverse); err != nil {
				return err
			}
			return nil
		},
	})

	db, err := sql.Open("sqlite3_CollationRegistration", ":memory:")
	if err != nil {
		t.Fatal("Failed to open database:", err)
	}
	defer db.Close()

	populate := []string{
		`CREATE TABLE test (s TEXT)`,
		`INSERT INTO test VALUES ('aaaa')`,
		`INSERT INTO test VALUES ('ffff')`,
		`INSERT INTO test VALUES ('qqqq')`,
		`INSERT INTO test VALUES ('tttt')`,
		`INSERT INTO test VALUES ('zzzz')`,
	}
	for _, stmt := range populate {
		if _, err := db.Exec(stmt); err != nil {
			t.Fatal("Failed to populate test DB:", err)
		}
	}

	ops := []struct {
		query string
		want  []string
	}{
		{
			"SELECT * FROM test ORDER BY s COLLATE rot13 ASC",
			[]string{
				"qqqq",
				"tttt",
				"zzzz",
				"aaaa",
				"ffff",
			},
		},
		{
			"SELECT * FROM test ORDER BY s COLLATE rot13 DESC",
			[]string{
				"ffff",
				"aaaa",
				"zzzz",
				"tttt",
				"qqqq",
			},
		},
		{
			"SELECT * FROM test ORDER BY s COLLATE rot13reverse ASC",
			[]string{
				"ffff",
				"aaaa",
				"zzzz",
				"tttt",
				"qqqq",
			},
		},
		{
			"SELECT * FROM test ORDER BY s COLLATE rot13reverse DESC",
			[]string{
				"qqqq",
				"tttt",
				"zzzz",
				"aaaa",
				"ffff",
			},
		},
	}

	for _, op := range ops {
		rows, err := db.Query(op.query)
		if err != nil {
			t.Fatalf("Query %q failed: %s", op.query, err)
		}
		got := []string{}
		defer rows.Close()
		for rows.Next() {
			var s string
			if err = rows.Scan(&s); err != nil {
				t.Fatalf("Reading row for %q: %s", op.query, err)
			}
			got = append(got, s)
		}
		if err = rows.Err(); err != nil {
			t.Fatalf("Reading rows for %q: %s", op.query, err)
		}

		if !reflect.DeepEqual(got, op.want) {
			t.Fatalf("Unexpected output from %q\ngot:\n%s\n\nwant:\n%s", op.query, strings.Join(got, "\n"), strings.Join(op.want, "\n"))
		}
	}
}

func TestDeclTypes(t *testing.T) {

	d := SQLiteDriver{}

	conn, err := d.Open(":memory:")
	if err != nil {
		t.Fatal("Failed to begin transaction:", err)
	}
	defer conn.Close()

	sqlite3conn := conn.(*SQLiteConn)

	_, err = sqlite3conn.Exec("create table foo (id integer not null primary key, name text)", nil)
	if err != nil {
		t.Fatal("Failed to create table:", err)
	}

	_, err = sqlite3conn.Exec("insert into foo(name) values('bar')", nil)
	if err != nil {
		t.Fatal("Failed to insert:", err)
	}

	rs, err := sqlite3conn.Query("select * from foo", nil)
	if err != nil {
		t.Fatal("Failed to select:", err)
	}
	defer rs.Close()

	declTypes := rs.(*SQLiteRows).DeclTypes()

	if !reflect.DeepEqual(declTypes, []string{"integer", "text"}) {
		t.Fatal("Unexpected declTypes:", declTypes)
	}
}

func TestPinger(t *testing.T) {
	db, err := sql.Open("sqlite3", ":memory:")
	if err != nil {
		t.Fatal(err)
	}
	err = db.Ping()
	if err != nil {
		t.Fatal(err)
	}
	db.Close()
	err = db.Ping()
	if err == nil {
		t.Fatal("Should be closed")
	}
}

func TestUpdateAndTransactionHooks(t *testing.T) {
	var events []string
	var commitHookReturn = 0

	sql.Register("sqlite3_UpdateHook", &SQLiteDriver{
		ConnectHook: func(conn *SQLiteConn) error {
			conn.RegisterCommitHook(func() int {
				events = append(events, "commit")
				return commitHookReturn
			})
			conn.RegisterRollbackHook(func() {
				events = append(events, "rollback")
			})
			conn.RegisterUpdateHook(func(op int, db string, table string, rowid int64) {
				events = append(events, fmt.Sprintf("update(op=%v db=%v table=%v rowid=%v)", op, db, table, rowid))
			})
			return nil
		},
	})
	db, err := sql.Open("sqlite3_UpdateHook", ":memory:")
	if err != nil {
		t.Fatal("Failed to open database:", err)
	}
	defer db.Close()

	statements := []string{
		"create table foo (id integer primary key)",
		"insert into foo values (9)",
		"update foo set id = 99 where id = 9",
		"delete from foo where id = 99",
	}
	for _, statement := range statements {
		_, err = db.Exec(statement)
		if err != nil {
			t.Fatalf("Unable to prepare test data [%v]: %v", statement, err)
		}
	}

	commitHookReturn = 1
	_, err = db.Exec("insert into foo values (5)")
	if err == nil {
		t.Error("Commit hook failed to rollback transaction")
	}

	var expected = []string{
		"commit",
		fmt.Sprintf("update(op=%v db=main table=foo rowid=9)", SQLITE_INSERT),
		"commit",
		fmt.Sprintf("update(op=%v db=main table=foo rowid=99)", SQLITE_UPDATE),
		"commit",
		fmt.Sprintf("update(op=%v db=main table=foo rowid=99)", SQLITE_DELETE),
		"commit",
		fmt.Sprintf("update(op=%v db=main table=foo rowid=5)", SQLITE_INSERT),
		"commit",
		"rollback",
	}
	if !reflect.DeepEqual(events, expected) {
		t.Errorf("Expected notifications: %#v\nbut got:                            %#v\n", expected, events)
	}
}

func TestSetFileControlInt(t *testing.T) {
	t.Run("PERSIST_WAL", func(t *testing.T) {
		tempFilename := TempFilename(t)
		defer os.Remove(tempFilename)

		sql.Register("sqlite3_FCNTL_PERSIST_WAL", &SQLiteDriver{
			ConnectHook: func(conn *SQLiteConn) error {
				if err := conn.SetFileControlInt("", SQLITE_FCNTL_PERSIST_WAL, 1); err != nil {
					return fmt.Errorf("Unexpected error from SetFileControlInt(): %w", err)
				}
				return nil
			},
		})

		db, err := sql.Open("sqlite3_FCNTL_PERSIST_WAL", tempFilename)
		if err != nil {
			t.Fatal("Failed to open database:", err)
		}
		defer db.Close()

		// Set to WAL mode & write a page.
		if _, err := db.Exec(`PRAGMA journal_mode = wal`); err != nil {
			t.Fatal("Failed to set journal mode:", err)
		} else if _, err := db.Exec(`CREATE TABLE t (x)`); err != nil {
			t.Fatal("Failed to create table:", err)
		}
		if err := db.Close(); err != nil {
			t.Fatal("Failed to close database", err)
		}

		// Ensure WAL file persists after close.
		if _, err := os.Stat(tempFilename + "-wal"); err != nil {
			t.Fatal("Expected WAL file to be persisted after close", err)
		}
	})
}

func TestNonColumnString(t *testing.T) {
	db, err := sql.Open("sqlite3", ":memory:")
	if err != nil {
		t.Fatal(err)
	}
	defer db.Close()

	var x any
	if err := db.QueryRow("SELECT 'hello'").Scan(&x); err != nil {
		t.Fatal(err)
	}
	s, ok := x.(string)
	if !ok {
		t.Fatalf("non-column string must return string but got %T", x)
	}
	if s != "hello" {
		t.Fatalf("non-column string must return %q but got %q", "hello", s)
	}
}

func TestNilAndEmptyBytes(t *testing.T) {
	db, err := sql.Open("sqlite3", ":memory:")
	if err != nil {
		t.Fatal(err)
	}
	defer db.Close()
	actualNil := []byte("use this to use an actual nil not a reference to nil")
	emptyBytes := []byte{}
	for tsti, tst := range []struct {
		name          string
		columnType    string
		insertBytes   []byte
		expectedBytes []byte
	}{
		{"actual nil blob", "blob", actualNil, nil},
		{"referenced nil blob", "blob", nil, nil},
		{"empty blob", "blob", emptyBytes, emptyBytes},
		{"actual nil text", "text", actualNil, nil},
		{"referenced nil text", "text", nil, nil},
		{"empty text", "text", emptyBytes, emptyBytes},
	} {
		if _, err = db.Exec(fmt.Sprintf("create table tbl%d (txt %s)", tsti, tst.columnType)); err != nil {
			t.Fatal(tst.name, err)
		}
		if bytes.Equal(tst.insertBytes, actualNil) {
			if _, err = db.Exec(fmt.Sprintf("insert into tbl%d (txt) values (?)", tsti), nil); err != nil {
				t.Fatal(tst.name, err)
			}
		} else {
			if _, err = db.Exec(fmt.Sprintf("insert into tbl%d (txt) values (?)", tsti), &tst.insertBytes); err != nil {
				t.Fatal(tst.name, err)
			}
		}
		rows, err := db.Query(fmt.Sprintf("select txt from tbl%d", tsti))
		if err != nil {
			t.Fatal(tst.name, err)
		}
		if !rows.Next() {
			t.Fatal(tst.name, "no rows")
		}
		var scanBytes []byte
		if err = rows.Scan(&scanBytes); err != nil {
			t.Fatal(tst.name, err)
		}
		if err = rows.Err(); err != nil {
			t.Fatal(tst.name, err)
		}
		if tst.expectedBytes == nil && scanBytes != nil {
			t.Errorf("%s: %#v != %#v", tst.name, scanBytes, tst.expectedBytes)
		} else if !bytes.Equal(scanBytes, tst.expectedBytes) {
			t.Errorf("%s: %#v != %#v", tst.name, scanBytes, tst.expectedBytes)
		}
	}
}

func TestInsertNilByteSlice(t *testing.T) {
	db, err := sql.Open("sqlite3", ":memory:")
	if err != nil {
		t.Fatal(err)
	}
	defer db.Close()
	if _, err := db.Exec("create table blob_not_null (b blob not null)"); err != nil {
		t.Fatal(err)
	}
	var nilSlice []byte
	if _, err := db.Exec("insert into blob_not_null (b) values (?)", nilSlice); err == nil {
		t.Fatal("didn't expect INSERT to 'not null' column with a nil []byte slice to work")
	}
	zeroLenSlice := []byte{}
	if _, err := db.Exec("insert into blob_not_null (b) values (?)", zeroLenSlice); err != nil {
		t.Fatal("failed to insert zero-length slice")
	}
}

func TestNamedParam(t *testing.T) {
	tempFilename := TempFilename(t)
	defer os.Remove(tempFilename)
	db, err := sql.Open("sqlite3", ":memory:")
	if err != nil {
		t.Fatal("Failed to open database:", err)
	}
	defer db.Close()

	_, err = db.Exec("drop table foo")
	_, err = db.Exec("create table foo (id integer, name text, amount integer)")
	if err != nil {
		t.Fatal("Failed to create table:", err)
	}

	_, err = db.Exec("insert into foo(id, name, amount) values(:id, @name, $amount)",
		sql.Named("bar", 42), sql.Named("baz", "quux"),
		sql.Named("amount", 123), sql.Named("corge", "waldo"),
		sql.Named("id", 2), sql.Named("name", "grault"))
	if err != nil {
		t.Fatal("Failed to insert record with named parameters:", err)
	}

	rows, err := db.Query("select id, name, amount from foo")
	if err != nil {
		t.Fatal("Failed to select records:", err)
	}
	defer rows.Close()

	rows.Next()

	var id, amount int
	var name string
	rows.Scan(&id, &name, &amount)
	if id != 2 || name != "grault" || amount != 123 {
		t.Errorf("Expected %d, %q, %d for fetched result, but got %d, %q, %d:", 2, "grault", 123, id, name, amount)
	}
}

var customFunctionOnce sync.Once

func TestSuite(t *testing.T) {
	initializeTestDB(t)
	defer freeTestDB()

	for _, test := range tests {
		t.Run(test.Name, test.F)
	}
}

// DB provide context for the tests
type TestDB struct {
	testing.TB
	*sql.DB
	once         sync.Once
	tempFilename string
}

var tdb *TestDB

func initializeTestDB(t testing.TB) {
	tempFilename := TempFilename(t)
	d, err := sql.Open("sqlite3", tempFilename+"?_busy_timeout=99999&mode=memory&cache=shared")
	if err != nil {
		os.Remove(tempFilename)
		t.Fatal(err)
	}

	tdb = &TestDB{t, d, sync.Once{}, tempFilename}
}

func freeTestDB() {
	err := tdb.DB.Close()
	if err != nil {
		panic(err)
	}
	err = os.Remove(tdb.tempFilename)
	if err != nil {
		panic(err)
	}
}

// the following tables will be created and dropped during the test
var testTables = []string{"foo"}

var tests = []testing.InternalTest{
	{Name: "TestResult", F: testResult},
	{Name: "TestBlobs", F: testBlobs},
	{Name: "TestMultiBlobs", F: testMultiBlobs},
	{Name: "TestNullZeroLengthBlobs", F: testNullZeroLengthBlobs},
	{Name: "TestManyQueryRow", F: testManyQueryRow},
	{Name: "TestTxQuery", F: testTxQuery},
	{Name: "TestPreparedStmt", F: testPreparedStmt},
	{Name: "TestExecEmptyQuery", F: testExecEmptyQuery},
}

func (db *TestDB) mustExec(sql string, args ...any) sql.Result {
	res, err := db.Exec(sql, args...)
	if err != nil {
		db.Fatalf("Error running %q: %v", sql, err)
	}
	return res
}

func (db *TestDB) tearDown() {
	for _, tbl := range testTables {
		db.mustExec("drop table if exists " + tbl)
	}
}

// testResult is test for result
func testResult(t *testing.T) {
	tdb.tearDown()
	tdb.mustExec("create temporary table test (id integer primary key autoincrement, name varchar(10))")

	for i := 1; i < 3; i++ {
		r := tdb.mustExec("insert into test (name) values (?)", fmt.Sprintf("row %d", i))
		n, err := r.RowsAffected()
		if err != nil {
			t.Fatal(err)
		}
		if n != 1 {
			t.Errorf("got %v, want %v", n, 1)
		}
		n, err = r.LastInsertId()
		if err != nil {
			t.Fatal(err)
		}
		if n != int64(i) {
			t.Errorf("got %v, want %v", n, i)
		}
	}
	if _, err := tdb.Exec("error!"); err == nil {
		t.Fatalf("expected error")
	}
}

// testBlobs is test for blobs
func testBlobs(t *testing.T) {
	tdb.tearDown()
	var blob = []byte{0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15}
	tdb.mustExec("create table foo (id integer primary key, bar blob[16])")
	tdb.mustExec("insert into foo (id, bar) values(?,?)", 0, blob)

	want := fmt.Sprintf("%x", blob)

	b := make([]byte, 16)
	err := tdb.QueryRow("select bar from foo where id = ?", 0).Scan(&b)
	got := fmt.Sprintf("%x", b)
	if err != nil {
		t.Errorf("[]byte scan: %v", err)
	} else if got != want {
		t.Errorf("for []byte, got %q; want %q", got, want)
	}

	err = tdb.QueryRow("select bar from foo where id = ?", 0).Scan(&got)
	want = string(blob)
	if err != nil {
		t.Errorf("string scan: %v", err)
	} else if got != want {
		t.Errorf("for string, got %q; want %q", got, want)
	}
}

func testMultiBlobs(t *testing.T) {
	tdb.tearDown()
	tdb.mustExec("create table foo (id integer primary key, bar blob[16])")
	var blob0 = []byte{0, 1, 2, 3, 4, 5, 6, 7}
	tdb.mustExec("insert into foo (id, bar) values(?,?)", 0, blob0)
	var blob1 = []byte{0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15}
	tdb.mustExec("insert into foo (id, bar) values(?,?)", 1, blob1)

	r, err := tdb.Query("select bar from foo order by id")
	if err != nil {
		t.Fatal(err)
	}
	defer r.Close()
	if !r.Next() {
		if r.Err() != nil {
			t.Fatal(err)
		}
		t.Fatal("expected one rows")
	}

	want0 := fmt.Sprintf("%x", blob0)
	b0 := make([]byte, 8)
	err = r.Scan(&b0)
	if err != nil {
		t.Fatal(err)
	}
	got0 := fmt.Sprintf("%x", b0)

	if !r.Next() {
		if r.Err() != nil {
			t.Fatal(err)
		}
		t.Fatal("expected one rows")
	}

	want1 := fmt.Sprintf("%x", blob1)
	b1 := make([]byte, 16)
	err = r.Scan(&b1)
	if err != nil {
		t.Fatal(err)
	}
	got1 := fmt.Sprintf("%x", b1)
	if got0 != want0 {
		t.Errorf("for []byte, got %q; want %q", got0, want0)
	}
	if got1 != want1 {
		t.Errorf("for []byte, got %q; want %q", got1, want1)
	}
}

// testBlobs tests that we distinguish between null and zero-length blobs
func testNullZeroLengthBlobs(t *testing.T) {
	tdb.tearDown()
	tdb.mustExec("create table foo (id integer primary key, bar blob[16])")
	tdb.mustExec("insert into foo (id, bar) values(?,?)", 0, nil)
	tdb.mustExec("insert into foo (id, bar) values(?,?)", 1, []byte{})

	r0 := tdb.QueryRow("select bar from foo where id=0")
	var b0 []byte
	err := r0.Scan(&b0)
	if err != nil {
		t.Fatal(err)
	}
	if b0 != nil {
		t.Errorf("for id=0, got %x; want nil", b0)
	}

	r1 := tdb.QueryRow("select bar from foo where id=1")
	var b1 []byte
	err = r1.Scan(&b1)
	if err != nil {
		t.Fatal(err)
	}
	if b1 == nil {
		t.Error("for id=1, got nil; want zero-length slice")
	} else if len(b1) > 0 {
		t.Errorf("for id=1, got %x; want zero-length slice", b1)
	}
}

func testManyQueryRow(t *testing.T) {
	// FIXME: too slow
	tdb.tearDown()
	tdb.mustExec("create table foo (id integer primary key, name varchar(50))")
	tdb.mustExec("insert into foo (id, name) values(?,?)", 1, "bob")
	var name string
	for i := 0; i < 10000; i++ {
		err := tdb.QueryRow("select name from foo where id = ?", 1).Scan(&name)
		if err != nil || name != "bob" {
			t.Fatalf("on query %d: err=%v, name=%q", i, err, name)
		}
	}
}

func testTxQuery(t *testing.T) {
	tdb.tearDown()
	tx, err := tdb.Begin()
	if err != nil {
		t.Fatal(err)
	}
	defer tx.Rollback()

	_, err = tx.Exec("create table foo (id integer primary key, name varchar(50))")
	if err != nil {
		t.Fatal(err)
	}

	_, err = tx.Exec("insert into foo (id, name) values(?,?)", 1, "bob")
	if err != nil {
		t.Fatal(err)
	}

	r, err := tx.Query("select name from foo where id = ?", 1)
	if err != nil {
		t.Fatal(err)
	}
	defer r.Close()

	if !r.Next() {
		if r.Err() != nil {
			t.Fatal(err)
		}
		t.Fatal("expected one rows")
	}

	var name string
	err = r.Scan(&name)
	if err != nil {
		t.Fatal(err)
	}
}

func testPreparedStmt(t *testing.T) {
	tdb.tearDown()
	tdb.mustExec("CREATE TABLE t (count INT)")
	sel, err := tdb.Prepare("SELECT count FROM t ORDER BY count DESC")
	if err != nil {
		t.Fatalf("prepare 1: %v", err)
	}
	ins, err := tdb.Prepare("INSERT INTO t (count) VALUES (?)")
	if err != nil {
		t.Fatalf("prepare 2: %v", err)
	}

	for n := 1; n <= 3; n++ {
		if _, err := ins.Exec(n); err != nil {
			t.Fatalf("insert(%d) = %v", n, err)
		}
	}

	const nRuns = 10
	var wg sync.WaitGroup
	for i := 0; i < nRuns; i++ {
		wg.Add(1)
		go func() {
			defer wg.Done()
			for j := 0; j < 10; j++ {
				count := 0
				if err := sel.QueryRow().Scan(&count); err != nil && err != sql.ErrNoRows {
					t.Errorf("Query: %v", err)
					return
				}
				if _, err := ins.Exec(rand.Intn(100)); err != nil {
					t.Errorf("Insert: %v", err)
					return
				}
			}
		}()
	}
	wg.Wait()
}

// testEmptyQuery is test for validating the API in case of empty query
func testExecEmptyQuery(t *testing.T) {
	tdb.tearDown()
	res, err := tdb.Exec(" -- this is just a comment ")
	if err != nil {
		t.Fatalf("empty query err: %v", err)
	}

	_, err = res.LastInsertId()
	if err != nil {
		t.Fatalf("LastInsertId returned an error: %v", err)
	}

	_, err = res.RowsAffected()
	if err != nil {
		t.Fatalf("RowsAffected returned an error: %v", err)
	}
}



func MainTest() {
	tests := []testing.InternalTest {
		{ "TestBackupStepByStep", TestBackupStepByStep },
		{ "TestBackupAllRemainingPages", TestBackupAllRemainingPages },
		{ "TestBackupError", TestBackupError },
		{ "TestCallbackArgCast", TestCallbackArgCast },
		{ "TestCallbackConverters", TestCallbackConverters },
		{ "TestCallbackReturnAny", TestCallbackReturnAny },
		{ "TestSimpleError", TestSimpleError },
		{ "TestCorruptDbErrors", TestCorruptDbErrors },
		{ "TestSqlLogicErrors", TestSqlLogicErrors },
		{ "TestExtendedErrorCodes_ForeignKey", TestExtendedErrorCodes_ForeignKey },
		{ "TestExtendedErrorCodes_NotNull", TestExtendedErrorCodes_NotNull },
		{ "TestExtendedErrorCodes_Unique", TestExtendedErrorCodes_Unique },
		{ "TestError_SystemErrno", TestError_SystemErrno },
		{ "TestBeginTxCancel", TestBeginTxCancel },
		{ "TestStmtReadonly", TestStmtReadonly },
		{ "TestNamedParams", TestNamedParams },
		{ "TestShortTimeout", TestShortTimeout },
		{ "TestExecContextCancel", TestExecContextCancel },
		{ "TestQueryRowContextCancel", TestQueryRowContextCancel },
		{ "TestQueryRowContextCancelParallel", TestQueryRowContextCancelParallel },
		{ "TestExecCancel", TestExecCancel },
		{ "TestOpenContext", TestOpenContext },
		{ "TestFileCopyTruncate", TestFileCopyTruncate },
		{ "TestExtensionsError", TestExtensionsError },
		{ "TestLoadExtensionError", TestLoadExtensionError },
		{ "TestColumnTableName", TestColumnTableName },
		{ "TestFTS3", TestFTS3 },
		{ "TestFTS4", TestFTS4 },
		{ "TestMathFunctions", TestMathFunctions },
		{ "TestSerializeDeserialize", TestSerializeDeserialize },
		{ "TestOpen", TestOpen },
		{ "TestOpenWithVFS", TestOpenWithVFS },
		{ "TestOpenNoCreate", TestOpenNoCreate },
		{ "TestReadonly", TestReadonly },
		{ "TestDeferredForeignKey", TestDeferredForeignKey },
		{ "TestRecursiveTriggers", TestRecursiveTriggers },
		{ "TestClose", TestClose },
		{ "TestInsert", TestInsert },
		{ "TestUpsert", TestUpsert },
		{ "TestUpdate", TestUpdate },
		{ "TestDelete", TestDelete },
		{ "TestBooleanRoundtrip", TestBooleanRoundtrip },
		{ "TestTimestamp", TestTimestamp },
		{ "TestBoolean", TestBoolean },
		{ "TestFloat32", TestFloat32 },
		{ "TestNull", TestNull },
		{ "TestTransaction", TestTransaction },
		{ "TestWAL", TestWAL },
		{ "TestTimezoneConversion", TestTimezoneConversion },
		{ "TestExecer", TestExecer },
		{ "TestQueryer", TestQueryer },
		{ "TestStress", TestStress },
		{ "TestDateTimeLocal", TestDateTimeLocal },
		{ "TestVersion", TestVersion },
		{ "TestStringContainingZero", TestStringContainingZero },
		{ "TestDateTimeNow", TestDateTimeNow },
		{ "TestAggregatorRegistration", TestAggregatorRegistration },
		{ "TestAggregatorRegistration_GenericReturn", TestAggregatorRegistration_GenericReturn },
		{ "TestCollationRegistration", TestCollationRegistration },
		{ "TestDeclTypes", TestDeclTypes },
		{ "TestPinger", TestPinger },
		{ "TestUpdateAndTransactionHooks", TestUpdateAndTransactionHooks },
		{ "TestSetFileControlInt", TestSetFileControlInt },
		{ "TestNonColumnString", TestNonColumnString },
		{ "TestNilAndEmptyBytes", TestNilAndEmptyBytes },
		{ "TestInsertNilByteSlice", TestInsertNilByteSlice },
		{ "TestNamedParam", TestNamedParam },
		{ "TestSuite", TestSuite }, // FIXME: too slow
	}

	benchmarks  := []testing.InternalBenchmark {}
	fuzzTargets := []testing.InternalFuzzTarget{}
	examples    := []testing.InternalExample   {}
	m := testing.MainStart(
		testdeps.TestDeps{},
		tests,
		benchmarks,
		fuzzTargets,
		examples,
	)
	os.Exit(m.Run())
}