diff options
Diffstat (limited to 'tests/golite.go')
| -rw-r--r-- | tests/golite.go | 3652 |
1 files changed, 3652 insertions, 0 deletions
diff --git a/tests/golite.go b/tests/golite.go new file mode 100644 index 0000000..2459993 --- /dev/null +++ b/tests/golite.go @@ -0,0 +1,3652 @@ +package golite + +import ( + "bytes" + "context" + "database/sql" + "database/sql/driver" + "errors" + "fmt" + "io/ioutil" + "math" + "math/rand" + "net/url" + "os" + "path" + "reflect" + "strings" + "sync" + "testing" + "testing/internal/testdeps" + "time" +) + + + +// The number of rows of test data to create in the source database. +// Can be used to control how many pages are available to be backed up. +const testRowCount = 100 + +// The maximum number of seconds after which the page-by-page backup is considered to have taken too long. +const usePagePerStepsTimeoutSeconds = 30 + +// Test the backup functionality. +func testBackup(t *testing.T, testRowCount int, usePerPageSteps bool) { + // This function will be called multiple times. + // It uses sql.Register(), which requires the name parameter value to be unique. + // There does not currently appear to be a way to unregister a registered driver, however. + // So generate a database driver name that will likely be unique. + var driverName = fmt.Sprintf("sqlite3_testBackup_%v_%v_%v", testRowCount, usePerPageSteps, time.Now().UnixNano()) + + // The driver's connection will be needed in order to perform the backup. + driverConns := []*SQLiteConn{} + sql.Register(driverName, &SQLiteDriver{ + ConnectHook: func(conn *SQLiteConn) error { + driverConns = append(driverConns, conn) + return nil + }, + }) + + // Connect to the source database. + srcTempFilename := "file:src?mode=memory&cache=shared" + srcDb, err := sql.Open(driverName, srcTempFilename) + if err != nil { + t.Fatal("Failed to open the source database:", err) + } + defer srcDb.Close() + err = srcDb.Ping() + if err != nil { + t.Fatal("Failed to connect to the source database:", err) + } + + // Connect to the destination database. + destTempFilename := "file:dst?mode=memory&cache=shared" + destDb, err := sql.Open(driverName, destTempFilename) + if err != nil { + t.Fatal("Failed to open the destination database:", err) + } + defer destDb.Close() + err = destDb.Ping() + if err != nil { + t.Fatal("Failed to connect to the destination database:", err) + } + + // Check the driver connections. + if len(driverConns) != 2 { + t.Fatalf("Expected 2 driver connections, but found %v.", len(driverConns)) + } + srcDbDriverConn := driverConns[0] + if srcDbDriverConn == nil { + t.Fatal("The source database driver connection is nil.") + } + destDbDriverConn := driverConns[1] + if destDbDriverConn == nil { + t.Fatal("The destination database driver connection is nil.") + } + + // Generate some test data for the given ID. + var generateTestData = func(id int) string { + return fmt.Sprintf("test-%v", id) + } + + // Populate the source database with a test table containing some test data. + tx, err := srcDb.Begin() + if err != nil { + t.Fatal("Failed to begin a transaction when populating the source database:", err) + } + _, err = srcDb.Exec("CREATE TABLE test (id INTEGER PRIMARY KEY, value TEXT)") + if err != nil { + tx.Rollback() + t.Fatal("Failed to create the source database \"test\" table:", err) + } + for id := 0; id < testRowCount; id++ { + _, err = srcDb.Exec("INSERT INTO test (id, value) VALUES (?, ?)", id, generateTestData(id)) + if err != nil { + tx.Rollback() + t.Fatal("Failed to insert a row into the source database \"test\" table:", err) + } + } + err = tx.Commit() + if err != nil { + t.Fatal("Failed to populate the source database:", err) + } + + // Confirm that the destination database is initially empty. + var destTableCount int + err = destDb.QueryRow("SELECT COUNT(*) FROM sqlite_master WHERE type = 'table'").Scan(&destTableCount) + if err != nil { + t.Fatal("Failed to check the destination table count:", err) + } + if destTableCount != 0 { + t.Fatalf("The destination database is not empty; %v table(s) found.", destTableCount) + } + + // Prepare to perform the backup. + backup, err := destDbDriverConn.Backup("main", srcDbDriverConn, "main") + if err != nil { + t.Fatal("Failed to initialize the backup:", err) + } + + // Allow the initial page count and remaining values to be retrieved. + // According to <https://www.sqlite.org/c3ref/backup_finish.html>, the page count and remaining values are "... only updated by sqlite3_backup_step()." + isDone, err := backup.Step(0) + if err != nil { + t.Fatal("Unable to perform an initial 0-page backup step:", err) + } + if isDone { + t.Fatal("Backup is unexpectedly done.") + } + + // Check that the page count and remaining values are reasonable. + initialPageCount := backup.PageCount() + if initialPageCount <= 0 { + t.Fatalf("Unexpected initial page count value: %v", initialPageCount) + } + initialRemaining := backup.Remaining() + if initialRemaining <= 0 { + t.Fatalf("Unexpected initial remaining value: %v", initialRemaining) + } + if initialRemaining != initialPageCount { + t.Fatalf("Initial remaining value differs from the initial page count value; remaining: %v; page count: %v", initialRemaining, initialPageCount) + } + + // Perform the backup. + if usePerPageSteps { + var startTime = time.Now().Unix() + + // Test backing-up using a page-by-page approach. + var latestRemaining = initialRemaining + for { + // Perform the backup step. + isDone, err = backup.Step(1) + if err != nil { + t.Fatal("Failed to perform a backup step:", err) + } + + // The page count should remain unchanged from its initial value. + currentPageCount := backup.PageCount() + if currentPageCount != initialPageCount { + t.Fatalf("Current page count differs from the initial page count; initial page count: %v; current page count: %v", initialPageCount, currentPageCount) + } + + // There should now be one less page remaining. + currentRemaining := backup.Remaining() + expectedRemaining := latestRemaining - 1 + if currentRemaining != expectedRemaining { + t.Fatalf("Unexpected remaining value; expected remaining value: %v; actual remaining value: %v", expectedRemaining, currentRemaining) + } + latestRemaining = currentRemaining + + if isDone { + break + } + + // Limit the runtime of the backup attempt. + if (time.Now().Unix() - startTime) > usePagePerStepsTimeoutSeconds { + t.Fatal("Backup is taking longer than expected.") + } + } + } else { + // Test the copying of all remaining pages. + isDone, err = backup.Step(-1) + if err != nil { + t.Fatal("Failed to perform a backup step:", err) + } + if !isDone { + t.Fatal("Backup is unexpectedly not done.") + } + } + + // Check that the page count and remaining values are reasonable. + finalPageCount := backup.PageCount() + if finalPageCount != initialPageCount { + t.Fatalf("Final page count differs from the initial page count; initial page count: %v; final page count: %v", initialPageCount, finalPageCount) + } + finalRemaining := backup.Remaining() + if finalRemaining != 0 { + t.Fatalf("Unexpected remaining value: %v", finalRemaining) + } + + // Finish the backup. + err = backup.Finish() + if err != nil { + t.Fatal("Failed to finish backup:", err) + } + + // Confirm that the "test" table now exists in the destination database. + var doesTestTableExist bool + err = destDb.QueryRow("SELECT EXISTS (SELECT 1 FROM sqlite_master WHERE type = 'table' AND name = 'test' LIMIT 1) AS test_table_exists").Scan(&doesTestTableExist) + if err != nil { + t.Fatal("Failed to check if the \"test\" table exists in the destination database:", err) + } + if !doesTestTableExist { + t.Fatal("The \"test\" table could not be found in the destination database.") + } + + // Confirm that the number of rows in the destination database's "test" table matches that of the source table. + var actualTestTableRowCount int + err = destDb.QueryRow("SELECT COUNT(*) FROM test").Scan(&actualTestTableRowCount) + if err != nil { + t.Fatal("Failed to determine the rowcount of the \"test\" table in the destination database:", err) + } + if testRowCount != actualTestTableRowCount { + t.Fatalf("Unexpected destination \"test\" table row count; expected: %v; found: %v", testRowCount, actualTestTableRowCount) + } + + // Check each of the rows in the destination database. + for id := 0; id < testRowCount; id++ { + var checkedValue string + err = destDb.QueryRow("SELECT value FROM test WHERE id = ?", id).Scan(&checkedValue) + if err != nil { + t.Fatal("Failed to query the \"test\" table in the destination database:", err) + } + + var expectedValue = generateTestData(id) + if checkedValue != expectedValue { + t.Fatalf("Unexpected value in the \"test\" table in the destination database; expected value: %v; actual value: %v", expectedValue, checkedValue) + } + } +} + +func TestBackupStepByStep(t *testing.T) { + testBackup(t, testRowCount, true) +} + +func TestBackupAllRemainingPages(t *testing.T) { + testBackup(t, testRowCount, false) +} + +// Test the error reporting when preparing to perform a backup. +func TestBackupError(t *testing.T) { + const driverName = "sqlite3_TestBackupError" + + // The driver's connection will be needed in order to perform the backup. + var dbDriverConn *SQLiteConn + sql.Register(driverName, &SQLiteDriver{ + ConnectHook: func(conn *SQLiteConn) error { + dbDriverConn = conn + return nil + }, + }) + + // Connect to the database. + db, err := sql.Open(driverName, ":memory:") + if err != nil { + t.Fatal("Failed to open the database:", err) + } + defer db.Close() + db.Ping() + + // Need the driver connection in order to perform the backup. + if dbDriverConn == nil { + t.Fatal("Failed to get the driver connection.") + } + + // Prepare to perform the backup. + // Intentionally using the same connection for both the source and destination databases, to trigger an error result. + backup, err := dbDriverConn.Backup("main", dbDriverConn, "main") + if err == nil { + t.Fatal("Failed to get the expected error result.") + } + const expectedError = "source and destination must be distinct" + if err.Error() != expectedError { + t.Fatalf("Unexpected error message; expected value: \"%v\"; actual value: \"%v\"", expectedError, err.Error()) + } + if backup != nil { + t.Fatal("Failed to get the expected nil backup result.") + } +} + +func TestCallbackArgCast(t *testing.T) { + intConv := callbackSyntheticForTests(reflect.ValueOf(int64(math.MaxInt64)), nil) + floatConv := callbackSyntheticForTests(reflect.ValueOf(float64(math.MaxFloat64)), nil) + errConv := callbackSyntheticForTests(reflect.Value{}, errors.New("test")) + + tests := []struct { + f callbackArgConverter + o reflect.Value + }{ + {intConv, reflect.ValueOf(int8(-1))}, + {intConv, reflect.ValueOf(int16(-1))}, + {intConv, reflect.ValueOf(int32(-1))}, + {intConv, reflect.ValueOf(uint8(math.MaxUint8))}, + {intConv, reflect.ValueOf(uint16(math.MaxUint16))}, + {intConv, reflect.ValueOf(uint32(math.MaxUint32))}, + // Special case, int64->uint64 is only 1<<63 - 1, not 1<<64 - 1 + {intConv, reflect.ValueOf(uint64(math.MaxInt64))}, + {floatConv, reflect.ValueOf(float32(math.Inf(1)))}, + } + + for _, test := range tests { + conv := callbackArgCast{test.f, test.o.Type()} + val, err := conv.Run(nil) + if err != nil { + t.Errorf("Couldn't convert to %s: %s", test.o.Type(), err) + } else if !reflect.DeepEqual(val.Interface(), test.o.Interface()) { + t.Errorf("Unexpected result from converting to %s: got %v, want %v", test.o.Type(), val.Interface(), test.o.Interface()) + } + } + + conv := callbackArgCast{errConv, reflect.TypeOf(int8(0))} + _, err := conv.Run(nil) + if err == nil { + t.Errorf("Expected error during callbackArgCast, but got none") + } +} + +func TestCallbackConverters(t *testing.T) { + tests := []struct { + v any + err bool + }{ + // Unfortunately, we can't tell which converter was returned, + // but we can at least check which types can be converted. + {[]byte{0}, false}, + {"text", false}, + {true, false}, + {int8(0), false}, + {int16(0), false}, + {int32(0), false}, + {int64(0), false}, + {uint8(0), false}, + {uint16(0), false}, + {uint32(0), false}, + {uint64(0), false}, + {int(0), false}, + {uint(0), false}, + {float64(0), false}, + {float32(0), false}, + + {func() {}, true}, + {complex64(complex(0, 0)), true}, + {complex128(complex(0, 0)), true}, + {struct{}{}, true}, + {map[string]string{}, true}, + {[]string{}, true}, + {(*int8)(nil), true}, + {make(chan int), true}, + } + + for _, test := range tests { + _, err := callbackArg(reflect.TypeOf(test.v)) + if test.err && err == nil { + t.Errorf("Expected an error when converting %s, got no error", reflect.TypeOf(test.v)) + } else if !test.err && err != nil { + t.Errorf("Expected converter when converting %s, got error: %s", reflect.TypeOf(test.v), err) + } + } + + for _, test := range tests { + _, err := callbackRet(reflect.TypeOf(test.v)) + if test.err && err == nil { + t.Errorf("Expected an error when converting %s, got no error", reflect.TypeOf(test.v)) + } else if !test.err && err != nil { + t.Errorf("Expected converter when converting %s, got error: %s", reflect.TypeOf(test.v), err) + } + } +} + +func TestCallbackReturnAny(t *testing.T) { + udf := func() any { + return 1 + } + + typ := reflect.TypeOf(udf) + _, err := callbackRet(typ.Out(0)) + if err != nil { + t.Errorf("Expected valid callback for any return type, got: %s", err) + } +} + +func TestSimpleError(t *testing.T) { + e := ErrError.Error() + if e != "SQL logic error or missing database" && e != "SQL logic error" { + t.Error("wrong error code: " + e) + } +} + +func TestCorruptDbErrors(t *testing.T) { + dirName, err := ioutil.TempDir("", "FIXME") + 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(DriverName, 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("", "FIXME") + if err != nil { + t.Fatal(err) + } + defer os.RemoveAll(dirName) + + db, err := sql.Open(DriverName, ":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(DriverName, ":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(DriverName, ":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(DriverName, ":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(DriverName, "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(DriverName, ":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(DriverName, ":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(DriverName, ":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(DriverName, ":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(DriverName, "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(DriverName, "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(DriverName, "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(DriverName, ":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(DriverName, 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(DriverName, 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(DriverName, 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(DriverName, ":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") + } +} + +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(DriverName, 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(DriverName, 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(DriverName, 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(DriverName, 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(DriverName, 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(DriverName, 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(DriverName, 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(DriverName, 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(DriverName, "file:"+tempFilename) + if err != nil { + t.Fatal(err) + } + defer db1.Close() + db1.Exec("CREATE TABLE test (x int, y float)") + + db2, err := sql.Open(DriverName, "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(DriverName, 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(DriverName, ":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(DriverName, ":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(DriverName, ":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(DriverName, ":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(DriverName, ":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(DriverName, ":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(DriverName, ":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(DriverName, ":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(DriverName, ":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(DriverName, ":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(DriverName, ":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(DriverName, ":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(DriverName, "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(DriverName, ":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(DriverName, ":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(DriverName, ":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(DriverName, 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(DriverName, 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(DriverName, 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(DriverName, ":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(DriverName, ":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(DriverName, ":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(DriverName, ":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(DriverName, ":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(DriverName, ":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(DriverName, ":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(DriverName, 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 }, + { "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()) +} |