From c3a3cf9d7aed9b3c48acbe31fd48f2c27549a570 Mon Sep 17 00:00:00 2001 From: EuAndreh Date: Mon, 7 Oct 2024 16:40:02 -0300 Subject: Rename golite -> acudego --- tests/acudego.go | 3674 ++++++++++++++++++++++++++++++++++++++++++ tests/benchmarks/exec.go | 4 +- tests/benchmarks/query.go | 4 +- tests/functional/json.go | 4 +- tests/functional/libbuild.go | 4 +- tests/functional/limit.go | 12 +- tests/golite.go | 3674 ------------------------------------------ tests/main.go | 4 +- 8 files changed, 3690 insertions(+), 3690 deletions(-) create mode 100644 tests/acudego.go delete mode 100644 tests/golite.go (limited to 'tests') diff --git a/tests/acudego.go b/tests/acudego.go new file mode 100644 index 0000000..cb9e672 --- /dev/null +++ b/tests/acudego.go @@ -0,0 +1,3674 @@ +package acudego + +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 , 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("", "acudego") + 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("acudego", 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("", "acudego") + if err != nil { + t.Fatal(err) + } + defer os.RemoveAll(dirName) + + db, err := sql.Open("acudego", ":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("acudego", ":memory:") + if err != nil { + t.Error(err) + } + defer db.Close() + + _, 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("acudego", ":memory:") + if err != nil { + t.Error(err) + } + defer db.Close() + + _, 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("acudego", ":memory:") + if err != nil { + t.Error(err) + } + defer db.Close() + + _, 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("acudego", "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("acudego", ":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("acudego", ":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("acudego", ":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("acudego", ":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("acudego", "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("acudego", "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("acudego", "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("acudego", ":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("acudego", 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(), 5*time.Second) + err = db.PingContext(ctx) + cancel() + if err != nil { + return "ping error:", err + } + + ctx, cancel = context.WithTimeout(context.Background(), 5*time.Second) + _, err = db.ExecContext(ctx, "drop table foo") + cancel() + ctx, cancel = context.WithTimeout(context.Background(), 5*time.Second) + _, err = db.ExecContext(ctx, "create table foo (id integer)") + cancel() + if err != nil { + return "Failed to create table:", err + } + + return "", nil +} + +func TestFileCopyTruncate(t *testing.T) { + var err error + tempFilename := TempFilename(t) + defer os.Remove(tempFilename) + + db, err := sql.Open("acudego", tempFilename) + if err != nil { + t.Fatal("open error:", err) + } + defer db.Close() + + if true { + _, err = db.Exec("PRAGMA journal_mode = delete;") + if err != nil { + t.Fatal("journal_mode delete:", err) + } + } + + ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second) + err = db.PingContext(ctx) + cancel() + if err != nil { + t.Fatal("ping error:", err) + } + + ctx, cancel = context.WithTimeout(context.Background(), 5*time.Second) + _, err = db.ExecContext(ctx, "drop table foo") + cancel() + ctx, cancel = context.WithTimeout(context.Background(), 5*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 + copyFilename := tempFilename + "-db-copy" + f, err = os.Create(copyFilename) + if err != nil { + t.Fatal("create file error:", err) + } + defer os.Remove(copyFilename) + + _, 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(), 5*time.Second) + err = db.PingContext(ctx) + cancel() + if err != nil { + t.Fatal("ping error:", err) + } + + ctx, cancel = context.WithTimeout(context.Background(), 5*time.Millisecond) + _, err = db.ExecContext(ctx, "drop table foo") + cancel() + if err == nil { + t.Fatal("drop table no error") + } + + ctx, cancel = context.WithTimeout(context.Background(), 5*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("acudego", copyFilename) + if err != nil { + t.Fatal("open error:", err) + } + defer db.Close() + + ctx, cancel = context.WithTimeout(context.Background(), 5*time.Second) + err = db.PingContext(ctx) + cancel() + if err != nil { + t.Fatal("ping error:", err) + } + + ctx, cancel = context.WithTimeout(context.Background(), 5*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(), 5*time.Second) + _, err = db.ExecContext(ctx, "create table foo (id integer)") + cancel() + if err != nil { + t.Fatal("create table error:", err) + } +} + +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 TestFTS5(t *testing.T) { + db, err := sql.Open("acudego", ":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 fts5(id, value)") + 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") + } + + _, err = db.Exec("DROP TABLE foo") + _, err = db.Exec("CREATE VIRTUAL TABLE foo USING fts5(tokenize=unicode61, id, value)") + 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() + + 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("acudego", ":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 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", tempFilename) + db, err := sql.Open("acudego", 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", tempFilename) + db, err := sql.Open("acudego", 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", tempFilename) + db, err := sql.Open("acudego", 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("acudego", 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 TestOpenWithVFS(t *testing.T) { + { + uri := fmt.Sprintf("file:%s?mode=memory&vfs=hello", t.Name()) + db, err := sql.Open("acudego", 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("acudego", 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("acudego", 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("acudego", 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("acudego", "file:"+tempFilename) + if err != nil { + t.Fatal(err) + } + defer db1.Close() + db1.Exec("CREATE TABLE test (x int, y float)") + + db2, err := sql.Open("acudego", "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("acudego", 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 TestClose(t *testing.T) { + db, err := sql.Open("acudego", ":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("acudego", ":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("acudego", ":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("acudego", ":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("acudego", ":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("acudego", ":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("acudego", ":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("acudego", ":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("acudego", ":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("acudego", ":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("acudego", ":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("acudego", ":memory:") + if err != nil { + t.Fatal("Failed to open database:", err) + } + defer db.Close() + + 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("acudego", "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("acudego", ":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("acudego", ":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("acudego", ":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("acudego", 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("acudego", 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("acudego", 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("acudego", ":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("acudego", ":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("acudego", ":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() + + 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("acudego", ":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("acudego", ":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("acudego", ":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("acudego", ":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("acudego", tempFilename+"?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 }, + { "TestFileCopyTruncate", TestFileCopyTruncate }, + { "TestColumnTableName", TestColumnTableName }, + { "TestFTS5", TestFTS5 }, + { "TestMathFunctions", TestMathFunctions }, + { "TestSerializeDeserialize", TestSerializeDeserialize }, + { "TestOpenWithVFS", TestOpenWithVFS }, + { "TestOpenNoCreate", TestOpenNoCreate }, + { "TestReadonly", TestReadonly }, + { "TestDeferredForeignKey", TestDeferredForeignKey }, + { "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 + } + + deps := testdeps.TestDeps{} + benchmarks := []testing.InternalBenchmark {} + fuzzTargets := []testing.InternalFuzzTarget{} + examples := []testing.InternalExample {} + m := testing.MainStart(deps, tests, benchmarks, fuzzTargets, examples) + os.Exit(m.Run()) +} diff --git a/tests/benchmarks/exec.go b/tests/benchmarks/exec.go index 60c9435..0a85b1f 100644 --- a/tests/benchmarks/exec.go +++ b/tests/benchmarks/exec.go @@ -4,7 +4,7 @@ import ( "database/sql" "flag" - _ "golite" + _ "acudego" ) @@ -15,7 +15,7 @@ func main() { flag.Parse() n := *nFlag - db, err := sql.Open("golite3", "file:bench.db?mode=memory&cache=shared") + db, err := sql.Open("acudego3", "file:bench.db?mode=memory&cache=shared") if err != nil { panic(err) } diff --git a/tests/benchmarks/query.go b/tests/benchmarks/query.go index 2bc8e00..e9c7b24 100644 --- a/tests/benchmarks/query.go +++ b/tests/benchmarks/query.go @@ -4,7 +4,7 @@ import ( "database/sql" "flag" - _ "golite" + _ "acudego" ) @@ -15,7 +15,7 @@ func main() { flag.Parse() n := *nFlag - db, err := sql.Open("golite", "file:benchdb?mode=memory&cache=shared") + db, err := sql.Open("acudego", "file:benchdb?mode=memory&cache=shared") if err != nil { panic(err) } diff --git a/tests/functional/json.go b/tests/functional/json.go index 6a1ae81..352a72c 100644 --- a/tests/functional/json.go +++ b/tests/functional/json.go @@ -8,7 +8,7 @@ import ( "log" "os" - _ "golite" + _ "acudego" ) type Tag struct { @@ -29,7 +29,7 @@ func main() { os.Remove("json.db") defer os.Remove("json.db") - db, err := sql.Open("golite", "json.db") + db, err := sql.Open("acudego", "json.db") if err != nil { log.Fatal(err) } diff --git a/tests/functional/libbuild.go b/tests/functional/libbuild.go index 1b7694e..860aff9 100644 --- a/tests/functional/libbuild.go +++ b/tests/functional/libbuild.go @@ -3,11 +3,11 @@ package main import ( "database/sql" - _ "golite" + _ "acudego" ) func main() { - db, err := sql.Open("golite", ":memory:") + db, err := sql.Open("acudego", ":memory:") if err != nil { panic(err) } diff --git a/tests/functional/limit.go b/tests/functional/limit.go index 8c65a25..ab726f6 100644 --- a/tests/functional/limit.go +++ b/tests/functional/limit.go @@ -7,7 +7,7 @@ import ( "os" "strings" - "golite" + "acudego" ) func createBulkInsertQuery(n int, start int) (string, []any) { @@ -49,9 +49,9 @@ func main() { delete from mylimittable; ` - var conn *golite.SQLiteConn - sql.Register("sqlite3_with_limit", &golite.SQLiteDriver{ - ConnectHook: func(c *golite.SQLiteConn) error { + var conn *acudego.SQLiteConn + sql.Register("sqlite3_with_limit", &acudego.SQLiteDriver{ + ConnectHook: func(c *acudego.SQLiteConn) error { conn = c return nil }, @@ -82,7 +82,7 @@ func main() { } } - conn.SetLimit(golite.SQLITE_LIMIT_VARIABLE_NUMBER, smallLimit) + conn.SetLimit(acudego.SQLITE_LIMIT_VARIABLE_NUMBER, smallLimit) { query, args := createBulkInsertQuery(num, num) err := bulkInsert(db, query, args) @@ -91,7 +91,7 @@ func main() { } } - conn.SetLimit(golite.SQLITE_LIMIT_VARIABLE_NUMBER, bigLimit) + conn.SetLimit(acudego.SQLITE_LIMIT_VARIABLE_NUMBER, bigLimit) { query, args := createBulkInsertQuery(500, num+num) err := bulkInsert(db, query, args) diff --git a/tests/golite.go b/tests/golite.go deleted file mode 100644 index d9693ad..0000000 --- a/tests/golite.go +++ /dev/null @@ -1,3674 +0,0 @@ -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 , 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("", "golite") - 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("golite", 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("", "golite") - if err != nil { - t.Fatal(err) - } - defer os.RemoveAll(dirName) - - db, err := sql.Open("golite", ":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("golite", ":memory:") - if err != nil { - t.Error(err) - } - defer db.Close() - - _, 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("golite", ":memory:") - if err != nil { - t.Error(err) - } - defer db.Close() - - _, 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("golite", ":memory:") - if err != nil { - t.Error(err) - } - defer db.Close() - - _, 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("golite", "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("golite", ":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("golite", ":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("golite", ":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("golite", ":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("golite", "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("golite", "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("golite", "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("golite", ":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("golite", 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(), 5*time.Second) - err = db.PingContext(ctx) - cancel() - if err != nil { - return "ping error:", err - } - - ctx, cancel = context.WithTimeout(context.Background(), 5*time.Second) - _, err = db.ExecContext(ctx, "drop table foo") - cancel() - ctx, cancel = context.WithTimeout(context.Background(), 5*time.Second) - _, err = db.ExecContext(ctx, "create table foo (id integer)") - cancel() - if err != nil { - return "Failed to create table:", err - } - - return "", nil -} - -func TestFileCopyTruncate(t *testing.T) { - var err error - tempFilename := TempFilename(t) - defer os.Remove(tempFilename) - - db, err := sql.Open("golite", tempFilename) - if err != nil { - t.Fatal("open error:", err) - } - defer db.Close() - - if true { - _, err = db.Exec("PRAGMA journal_mode = delete;") - if err != nil { - t.Fatal("journal_mode delete:", err) - } - } - - ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second) - err = db.PingContext(ctx) - cancel() - if err != nil { - t.Fatal("ping error:", err) - } - - ctx, cancel = context.WithTimeout(context.Background(), 5*time.Second) - _, err = db.ExecContext(ctx, "drop table foo") - cancel() - ctx, cancel = context.WithTimeout(context.Background(), 5*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 - copyFilename := tempFilename + "-db-copy" - f, err = os.Create(copyFilename) - if err != nil { - t.Fatal("create file error:", err) - } - defer os.Remove(copyFilename) - - _, 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(), 5*time.Second) - err = db.PingContext(ctx) - cancel() - if err != nil { - t.Fatal("ping error:", err) - } - - ctx, cancel = context.WithTimeout(context.Background(), 5*time.Millisecond) - _, err = db.ExecContext(ctx, "drop table foo") - cancel() - if err == nil { - t.Fatal("drop table no error") - } - - ctx, cancel = context.WithTimeout(context.Background(), 5*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("golite", copyFilename) - if err != nil { - t.Fatal("open error:", err) - } - defer db.Close() - - ctx, cancel = context.WithTimeout(context.Background(), 5*time.Second) - err = db.PingContext(ctx) - cancel() - if err != nil { - t.Fatal("ping error:", err) - } - - ctx, cancel = context.WithTimeout(context.Background(), 5*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(), 5*time.Second) - _, err = db.ExecContext(ctx, "create table foo (id integer)") - cancel() - if err != nil { - t.Fatal("create table error:", err) - } -} - -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 TestFTS5(t *testing.T) { - db, err := sql.Open("golite", ":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 fts5(id, value)") - 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") - } - - _, err = db.Exec("DROP TABLE foo") - _, err = db.Exec("CREATE VIRTUAL TABLE foo USING fts5(tokenize=unicode61, id, value)") - 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() - - 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("golite", ":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 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", tempFilename) - db, err := sql.Open("golite", 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", tempFilename) - db, err := sql.Open("golite", 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", tempFilename) - db, err := sql.Open("golite", 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("golite", 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 TestOpenWithVFS(t *testing.T) { - { - uri := fmt.Sprintf("file:%s?mode=memory&vfs=hello", t.Name()) - db, err := sql.Open("golite", 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("golite", 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("golite", 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("golite", 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("golite", "file:"+tempFilename) - if err != nil { - t.Fatal(err) - } - defer db1.Close() - db1.Exec("CREATE TABLE test (x int, y float)") - - db2, err := sql.Open("golite", "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("golite", 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 TestClose(t *testing.T) { - db, err := sql.Open("golite", ":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("golite", ":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("golite", ":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("golite", ":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("golite", ":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("golite", ":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("golite", ":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("golite", ":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("golite", ":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("golite", ":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("golite", ":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("golite", ":memory:") - if err != nil { - t.Fatal("Failed to open database:", err) - } - defer db.Close() - - 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("golite", "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("golite", ":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("golite", ":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("golite", ":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("golite", 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("golite", 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("golite", 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("golite", ":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("golite", ":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("golite", ":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() - - 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("golite", ":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("golite", ":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("golite", ":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("golite", ":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("golite", tempFilename+"?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 }, - { "TestFileCopyTruncate", TestFileCopyTruncate }, - { "TestColumnTableName", TestColumnTableName }, - { "TestFTS5", TestFTS5 }, - { "TestMathFunctions", TestMathFunctions }, - { "TestSerializeDeserialize", TestSerializeDeserialize }, - { "TestOpenWithVFS", TestOpenWithVFS }, - { "TestOpenNoCreate", TestOpenNoCreate }, - { "TestReadonly", TestReadonly }, - { "TestDeferredForeignKey", TestDeferredForeignKey }, - { "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 - } - - deps := testdeps.TestDeps{} - benchmarks := []testing.InternalBenchmark {} - fuzzTargets := []testing.InternalFuzzTarget{} - examples := []testing.InternalExample {} - m := testing.MainStart(deps, tests, benchmarks, fuzzTargets, examples) - os.Exit(m.Run()) -} diff --git a/tests/main.go b/tests/main.go index f8a5b6f..7e0b774 100644 --- a/tests/main.go +++ b/tests/main.go @@ -1,7 +1,7 @@ package main -import "golite" +import "acudego" func main() { - golite.MainTest() + acudego.MainTest() } -- cgit v1.2.3