github.com/afumu/sqlite@v0.0.5/all_test.go (about) 1 // Copyright 2017 The Sqlite Authors. All rights reserved. 2 // Use of this source code is governed by a BSD-style 3 // license that can be found in the LICENSE file. 4 5 package sqlite // import "github.com/afumu/sqlite" 6 7 import ( 8 "bytes" 9 "context" 10 "database/sql" 11 "database/sql/driver" 12 "embed" 13 "errors" 14 "flag" 15 "fmt" 16 "io" 17 "math/rand" 18 "net/netip" 19 "net/url" 20 "os" 21 "os/exec" 22 "path" 23 "path/filepath" 24 "reflect" 25 "regexp" 26 "runtime" 27 "runtime/debug" 28 "runtime/pprof" 29 "strconv" 30 "strings" 31 "sync" 32 "sync/atomic" 33 "testing" 34 "time" 35 "unsafe" 36 37 "github.com/afumu/libc" 38 sqlite3 "github.com/afumu/sqlite/lib" 39 "github.com/afumu/sqlite/vfs" 40 "github.com/google/pprof/profile" 41 "modernc.org/mathutil" 42 ) 43 44 func caller(s string, va ...interface{}) { 45 if s == "" { 46 s = strings.Repeat("%v ", len(va)) 47 } 48 _, fn, fl, _ := runtime.Caller(2) 49 fmt.Fprintf(os.Stderr, "# caller: %s:%d: ", path.Base(fn), fl) 50 fmt.Fprintf(os.Stderr, s, va...) 51 fmt.Fprintln(os.Stderr) 52 _, fn, fl, _ = runtime.Caller(1) 53 fmt.Fprintf(os.Stderr, "# \tcallee: %s:%d: ", path.Base(fn), fl) 54 fmt.Fprintln(os.Stderr) 55 os.Stderr.Sync() 56 } 57 58 func dbg(s string, va ...interface{}) { 59 if s == "" { 60 s = strings.Repeat("%v ", len(va)) 61 } 62 _, fn, fl, _ := runtime.Caller(1) 63 fmt.Fprintf(os.Stderr, "# dbg %s:%d: ", path.Base(fn), fl) 64 fmt.Fprintf(os.Stderr, s, va...) 65 fmt.Fprintln(os.Stderr) 66 os.Stderr.Sync() 67 } 68 69 func stack() string { return string(debug.Stack()) } 70 71 func use(...interface{}) {} 72 73 func init() { 74 use(caller, dbg, stack, todo, trc) //TODOOK 75 } 76 77 func todo(s string, args ...interface{}) string { //TODO- 78 switch { 79 case s == "": 80 s = fmt.Sprintf(strings.Repeat("%v ", len(args)), args...) 81 default: 82 s = fmt.Sprintf(s, args...) 83 } 84 r := fmt.Sprintf("%s: TODOTODO %s", origin(2), s) //TODOOK 85 fmt.Fprintf(os.Stdout, "%s\n", r) 86 os.Stdout.Sync() 87 return r 88 } 89 90 func trc(s string, args ...interface{}) string { //TODO- 91 switch { 92 case s == "": 93 s = fmt.Sprintf(strings.Repeat("%v ", len(args)), args...) 94 default: 95 s = fmt.Sprintf(s, args...) 96 } 97 r := fmt.Sprintf("\n%s: TRC %s", origin(2), s) 98 fmt.Fprintf(os.Stdout, "%s\n", r) 99 os.Stdout.Sync() 100 return r 101 } 102 103 // ============================================================================ 104 105 var ( 106 oRecsPerSec = flag.Bool("recs_per_sec_as_mbps", false, "Show records per second as MB/s.") 107 oXTags = flag.String("xtags", "", "passed to go build of testfixture in TestTclTest") 108 tempDir string 109 ) 110 111 func TestMain(m *testing.M) { 112 fmt.Printf("test binary compiled for %s/%s\n", runtime.GOOS, runtime.GOARCH) 113 flag.Parse() 114 libc.MemAuditStart() 115 os.Exit(testMain(m)) 116 } 117 118 func testMain(m *testing.M) int { 119 var err error 120 tempDir, err = os.MkdirTemp("", "sqlite-test-") 121 if err != nil { 122 panic(err) //TODOOK 123 } 124 125 defer os.RemoveAll(tempDir) 126 127 return m.Run() 128 } 129 130 func tempDB(t testing.TB) (string, *sql.DB) { 131 dir, err := os.MkdirTemp("", "sqlite-test-") 132 if err != nil { 133 t.Fatal(err) 134 } 135 136 db, err := sql.Open(driverName, filepath.Join(dir, "tmp.db")) 137 if err != nil { 138 os.RemoveAll(dir) 139 t.Fatal(err) 140 } 141 142 return dir, db 143 } 144 145 // https://gitlab.com/cznic/sqlite/issues/118 146 func TestIssue118(t *testing.T) { 147 // Many iterations generate enough objects to ensure pprof 148 // profile captures the samples that we are seeking below 149 for i := 0; i < 10000; i++ { 150 func() { 151 db, err := sql.Open("sqlite", ":memory:") 152 if err != nil { 153 t.Fatal(err) 154 } 155 defer db.Close() 156 if _, err := db.Exec(`CREATE TABLE t1(v TEXT)`); err != nil { 157 t.Fatal(err) 158 } 159 var val []byte 160 if _, err := db.Exec(`INSERT INTO t1(v) VALUES(?)`, val); err != nil { 161 t.Fatal(err) 162 } 163 var count int 164 err = db.QueryRow("SELECT MAX(_ROWID_) FROM t1").Scan(&count) 165 if err != nil || count <= 0 { 166 t.Fatalf("Query failure: %d, %s", count, err) 167 } 168 }() 169 } 170 171 // Dump & read heap sample 172 var buf bytes.Buffer 173 if err := pprof.Lookup("heap").WriteTo(&buf, 0); err != nil { 174 t.Fatalf("Error dumping heap profile: %s", err) 175 } 176 heapProfile, err := profile.Parse(&buf) 177 if err != nil { 178 t.Fatalf("Error parsing heap profile: %s", err) 179 } 180 181 // Profile.SampleType indexes map into Sample.Values below. We are 182 // looking for "inuse_*" values, and skip the "alloc_*" ones 183 inUseIndexes := make([]int, 0, 2) 184 for i, t := range heapProfile.SampleType { 185 if strings.HasPrefix(t.Type, "inuse_") { 186 inUseIndexes = append(inUseIndexes, i) 187 } 188 } 189 190 // Look for samples from "libc.NewTLS" and insure that they have nothing in-use 191 for _, sample := range heapProfile.Sample { 192 isInUse := false 193 for _, idx := range inUseIndexes { 194 isInUse = isInUse || sample.Value[idx] > 0 195 } 196 if !isInUse { 197 continue 198 } 199 200 isNewTLS := false 201 sampleStack := []string{} 202 for _, location := range sample.Location { 203 for _, line := range location.Line { 204 sampleStack = append(sampleStack, fmt.Sprintf("%s (%s:%d)", line.Function.Name, line.Function.Filename, line.Line)) 205 isNewTLS = isNewTLS || strings.Contains(line.Function.Name, "libc.NewTLS") 206 } 207 } 208 if isNewTLS { 209 t.Errorf("Memory leak via libc.NewTLS:\n%s\n", strings.Join(sampleStack, "\n")) 210 } 211 } 212 } 213 214 // https://gitlab.com/cznic/sqlite/issues/100 215 func TestIssue100(t *testing.T) { 216 db, err := sql.Open("sqlite", ":memory:") 217 if err != nil { 218 t.Fatal(err) 219 } 220 defer db.Close() 221 if _, err := db.Exec(`CREATE TABLE t1(v TEXT)`); err != nil { 222 t.Fatal(err) 223 } 224 var val []byte 225 if _, err := db.Exec(`INSERT INTO t1(v) VALUES(?)`, val); err != nil { 226 t.Fatal(err) 227 } 228 var res sql.NullByte 229 if err = db.QueryRow(`SELECT v FROM t1 LIMIT 1`).Scan(&res); err != nil { 230 t.Fatal(err) 231 } 232 if res.Valid { 233 t.Fatalf("got non-NULL result: %+v", res) 234 } 235 236 if _, err := db.Exec(`CREATE TABLE t2( 237 v TEXT check(v is NULL OR(json_valid(v) AND json_type(v)='array')) 238 )`); err != nil { 239 t.Fatal(err) 240 } 241 for _, val := range [...][]byte{nil, []byte(`["a"]`)} { 242 if _, err := db.Exec(`INSERT INTO t2(v) VALUES(?)`, val); err != nil { 243 t.Fatalf("inserting value %v (%[1]q): %v", val, err) 244 } 245 } 246 } 247 248 // https://gitlab.com/cznic/sqlite/issues/98 249 func TestIssue98(t *testing.T) { 250 dir, db := tempDB(t) 251 252 defer func() { 253 db.Close() 254 os.RemoveAll(dir) 255 }() 256 257 if _, err := db.Exec("create table t(b mediumblob not null)"); err != nil { 258 t.Fatal(err) 259 } 260 if _, err := db.Exec("insert into t values (?)", []byte{}); err != nil { 261 t.Fatal(err) 262 } 263 if _, err := db.Exec("insert into t values (?)", nil); err == nil { 264 t.Fatal("expected statement to fail") 265 } 266 } 267 268 // https://gitlab.com/cznic/sqlite/issues/97 269 func TestIssue97(t *testing.T) { 270 name := filepath.Join(t.TempDir(), "tmp.db") 271 272 db, err := sql.Open(driverName, fmt.Sprintf("file:%s", name)) 273 if err != nil { 274 t.Fatal(err) 275 } 276 defer db.Close() 277 278 if _, err := db.Exec("create table t(b int)"); err != nil { 279 t.Fatal(err) 280 } 281 282 rodb, err := sql.Open(driverName, fmt.Sprintf("file:%s?mode=ro", name)) 283 if err != nil { 284 t.Fatal(err) 285 } 286 defer rodb.Close() 287 288 _, err = rodb.Exec("drop table t") 289 if err == nil { 290 t.Fatal("expected drop table statement to fail on a read only database") 291 } else if err.Error() != "attempt to write a readonly database (8)" { 292 t.Fatal("expected drop table statement to fail because its a readonly database") 293 } 294 } 295 296 func TestScalar(t *testing.T) { 297 dir, db := tempDB(t) 298 299 defer func() { 300 db.Close() 301 os.RemoveAll(dir) 302 }() 303 304 t1 := time.Date(2017, 4, 20, 1, 2, 3, 56789, time.UTC) 305 t2 := time.Date(2018, 5, 21, 2, 3, 4, 98765, time.UTC) 306 r, err := db.Exec(` 307 create table t(i int, f double, b bool, s text, t time); 308 insert into t values(12, 3.14, ?, 'foo', ?), (34, 2.78, ?, 'bar', ?); 309 `, 310 true, t1, 311 false, t2, 312 ) 313 if err != nil { 314 t.Fatal(err) 315 } 316 317 n, err := r.RowsAffected() 318 if err != nil { 319 t.Fatal(err) 320 } 321 322 if g, e := n, int64(2); g != e { 323 t.Fatal(g, e) 324 } 325 326 rows, err := db.Query("select * from t") 327 if err != nil { 328 t.Fatal(err) 329 } 330 331 type rec struct { 332 i int 333 f float64 334 b bool 335 s string 336 t string 337 } 338 var a []rec 339 for rows.Next() { 340 var r rec 341 if err := rows.Scan(&r.i, &r.f, &r.b, &r.s, &r.t); err != nil { 342 t.Fatal(err) 343 } 344 345 a = append(a, r) 346 } 347 if err := rows.Err(); err != nil { 348 t.Fatal(err) 349 } 350 351 if g, e := len(a), 2; g != e { 352 t.Fatal(g, e) 353 } 354 355 if g, e := a[0], (rec{12, 3.14, true, "foo", t1.String()}); g != e { 356 t.Fatal(g, e) 357 } 358 359 if g, e := a[1], (rec{34, 2.78, false, "bar", t2.String()}); g != e { 360 t.Fatal(g, e) 361 } 362 } 363 364 func TestRedefineUserDefinedFunction(t *testing.T) { 365 dir, db := tempDB(t) 366 ctx := context.Background() 367 368 defer func() { 369 db.Close() 370 os.RemoveAll(dir) 371 }() 372 373 connection, err := db.Conn(context.Background()) 374 if err != nil { 375 t.Fatal(err) 376 } 377 378 var r int 379 funName := "test" 380 381 if err = connection.Raw(func(driverConn interface{}) error { 382 c := driverConn.(*conn) 383 384 name, err := libc.CString(funName) 385 if err != nil { 386 return err 387 } 388 389 return c.createFunctionInternal(&userDefinedFunction{ 390 zFuncName: name, 391 nArg: 0, 392 eTextRep: sqlite3.SQLITE_UTF8 | sqlite3.SQLITE_DETERMINISTIC, 393 xFunc: func(tls *libc.TLS, ctx uintptr, argc int32, argv uintptr) { 394 sqlite3.Xsqlite3_result_int(tls, ctx, 1) 395 }, 396 }) 397 }); err != nil { 398 t.Fatal(err) 399 } 400 row := connection.QueryRowContext(ctx, "select test()") 401 402 if err := row.Scan(&r); err != nil { 403 t.Fatal(err) 404 } 405 406 if g, e := r, 1; g != e { 407 t.Fatal(g, e) 408 } 409 410 if err = connection.Raw(func(driverConn interface{}) error { 411 c := driverConn.(*conn) 412 413 name, err := libc.CString(funName) 414 if err != nil { 415 return err 416 } 417 418 return c.createFunctionInternal(&userDefinedFunction{ 419 zFuncName: name, 420 nArg: 0, 421 eTextRep: sqlite3.SQLITE_UTF8 | sqlite3.SQLITE_DETERMINISTIC, 422 xFunc: func(tls *libc.TLS, ctx uintptr, argc int32, argv uintptr) { 423 sqlite3.Xsqlite3_result_int(tls, ctx, 2) 424 }, 425 }) 426 }); err != nil { 427 t.Fatal(err) 428 } 429 row = connection.QueryRowContext(ctx, "select test()") 430 431 if err := row.Scan(&r); err != nil { 432 t.Fatal(err) 433 } 434 435 if g, e := r, 2; g != e { 436 t.Fatal(g, e) 437 } 438 } 439 440 func TestRegexpUserDefinedFunction(t *testing.T) { 441 dir, db := tempDB(t) 442 ctx := context.Background() 443 444 defer func() { 445 db.Close() 446 os.RemoveAll(dir) 447 }() 448 449 connection, err := db.Conn(context.Background()) 450 if err != nil { 451 t.Fatal(err) 452 } 453 454 if err = connection.Raw(func(driverConn interface{}) error { 455 c := driverConn.(*conn) 456 457 name, err := libc.CString("regexp") 458 if err != nil { 459 return err 460 } 461 462 return c.createFunctionInternal(&userDefinedFunction{ 463 zFuncName: name, 464 nArg: 2, 465 eTextRep: sqlite3.SQLITE_UTF8 | sqlite3.SQLITE_DETERMINISTIC, 466 xFunc: func(tls *libc.TLS, ctx uintptr, argc int32, argv uintptr) { 467 const sqliteValPtrSize = unsafe.Sizeof(&sqlite3.Sqlite3_value{}) 468 469 argvv := make([]uintptr, argc) 470 for i := int32(0); i < argc; i++ { 471 argvv[i] = *(*uintptr)(unsafe.Pointer(argv + uintptr(i)*sqliteValPtrSize)) 472 } 473 474 setErrorResult := func(res error) { 475 errmsg, cerr := libc.CString(res.Error()) 476 if cerr != nil { 477 panic(cerr) 478 } 479 defer libc.Xfree(tls, errmsg) 480 sqlite3.Xsqlite3_result_error(tls, ctx, errmsg, -1) 481 sqlite3.Xsqlite3_result_error_code(tls, ctx, sqlite3.SQLITE_ERROR) 482 } 483 484 var s1 string 485 switch sqlite3.Xsqlite3_value_type(tls, argvv[0]) { 486 case sqlite3.SQLITE_TEXT: 487 s1 = libc.GoString(sqlite3.Xsqlite3_value_text(tls, argvv[0])) 488 default: 489 setErrorResult(errors.New("expected argv[0] to be text")) 490 return 491 } 492 493 var s2 string 494 switch sqlite3.Xsqlite3_value_type(tls, argvv[1]) { 495 case sqlite3.SQLITE_TEXT: 496 s2 = libc.GoString(sqlite3.Xsqlite3_value_text(tls, argvv[1])) 497 default: 498 setErrorResult(errors.New("expected argv[1] to be text")) 499 return 500 } 501 502 matched, err := regexp.MatchString(s1, s2) 503 if err != nil { 504 setErrorResult(fmt.Errorf("bad regular expression: %q", err)) 505 return 506 } 507 sqlite3.Xsqlite3_result_int(tls, ctx, libc.Bool32(matched)) 508 }, 509 }) 510 }); err != nil { 511 t.Fatal(err) 512 } 513 514 t.Run("regexp filter", func(tt *testing.T) { 515 t1 := "seafood" 516 t2 := "fruit" 517 518 connection.ExecContext(ctx, ` 519 create table t(b text); 520 insert into t values(?), (?); 521 `, t1, t2) 522 523 rows, err := connection.QueryContext(ctx, "select * from t where b regexp 'foo.*'") 524 if err != nil { 525 tt.Fatal(err) 526 } 527 528 type rec struct { 529 b string 530 } 531 var a []rec 532 for rows.Next() { 533 var r rec 534 if err := rows.Scan(&r.b); err != nil { 535 tt.Fatal(err) 536 } 537 538 a = append(a, r) 539 } 540 if err := rows.Err(); err != nil { 541 tt.Fatal(err) 542 } 543 544 if g, e := len(a), 1; g != e { 545 tt.Fatal(g, e) 546 } 547 548 if g, e := a[0].b, t1; g != e { 549 tt.Fatal(g, e) 550 } 551 }) 552 553 t.Run("regexp matches", func(tt *testing.T) { 554 row := connection.QueryRowContext(ctx, "select 'seafood' regexp 'foo.*'") 555 556 var r int 557 if err := row.Scan(&r); err != nil { 558 tt.Fatal(err) 559 } 560 561 if g, e := r, 1; g != e { 562 tt.Fatal(g, e) 563 } 564 }) 565 566 t.Run("regexp does not match", func(tt *testing.T) { 567 row := connection.QueryRowContext(ctx, "select 'fruit' regexp 'foo.*'") 568 569 var r int 570 if err := row.Scan(&r); err != nil { 571 tt.Fatal(err) 572 } 573 574 if g, e := r, 0; g != e { 575 tt.Fatal(g, e) 576 } 577 }) 578 579 t.Run("errors on bad regexp", func(tt *testing.T) { 580 _, err := connection.QueryContext(ctx, "select 'seafood' regexp 'a(b'") 581 if err == nil { 582 tt.Fatal(errors.New("expected error, got none")) 583 } 584 }) 585 586 t.Run("errors on bad first argument", func(tt *testing.T) { 587 _, err := connection.QueryContext(ctx, "SELECT 1 REGEXP 'a(b'") 588 if err == nil { 589 tt.Fatal(errors.New("expected error, got none")) 590 } 591 }) 592 593 t.Run("errors on bad second argument", func(tt *testing.T) { 594 _, err := connection.QueryContext(ctx, "SELECT 'seafood' REGEXP 1") 595 if err == nil { 596 tt.Fatal(errors.New("expected error, got none")) 597 } 598 }) 599 } 600 601 func TestBlob(t *testing.T) { 602 dir, db := tempDB(t) 603 604 defer func() { 605 db.Close() 606 os.RemoveAll(dir) 607 }() 608 609 b1 := []byte(time.Now().String()) 610 b2 := []byte("\x00foo\x00bar\x00") 611 if _, err := db.Exec(` 612 create table t(b blob); 613 insert into t values(?), (?); 614 `, b1, b2, 615 ); err != nil { 616 t.Fatal(err) 617 } 618 619 rows, err := db.Query("select * from t") 620 if err != nil { 621 t.Fatal(err) 622 } 623 624 type rec struct { 625 b []byte 626 } 627 var a []rec 628 for rows.Next() { 629 var r rec 630 if err := rows.Scan(&r.b); err != nil { 631 t.Fatal(err) 632 } 633 634 a = append(a, r) 635 } 636 if err := rows.Err(); err != nil { 637 t.Fatal(err) 638 } 639 640 if g, e := len(a), 2; g != e { 641 t.Fatal(g, e) 642 } 643 644 if g, e := a[0].b, b1; !bytes.Equal(g, e) { 645 t.Fatal(g, e) 646 } 647 648 if g, e := a[1].b, b2; !bytes.Equal(g, e) { 649 t.Fatal(g, e) 650 } 651 } 652 653 func benchmarkInsertMemory(b *testing.B, n int) { 654 db, err := sql.Open(driverName, "file::memory:") 655 if err != nil { 656 b.Fatal(err) 657 } 658 659 defer func() { 660 db.Close() 661 }() 662 663 b.ReportAllocs() 664 b.ResetTimer() 665 for i := 0; i < b.N; i++ { 666 b.StopTimer() 667 if _, err := db.Exec(` 668 drop table if exists t; 669 create table t(i int); 670 begin; 671 `); err != nil { 672 b.Fatal(err) 673 } 674 675 s, err := db.Prepare("insert into t values(?)") 676 if err != nil { 677 b.Fatal(err) 678 } 679 680 b.StartTimer() 681 for i := 0; i < n; i++ { 682 if _, err := s.Exec(int64(i)); err != nil { 683 b.Fatal(err) 684 } 685 } 686 b.StopTimer() 687 if _, err := db.Exec(`commit;`); err != nil { 688 b.Fatal(err) 689 } 690 } 691 if *oRecsPerSec { 692 b.SetBytes(1e6 * int64(n)) 693 } 694 } 695 696 func BenchmarkInsertMemory(b *testing.B) { 697 for i, n := range []int{1e1, 1e2, 1e3, 1e4, 1e5, 1e6} { 698 b.Run(fmt.Sprintf("1e%d", i+1), func(b *testing.B) { benchmarkInsertMemory(b, n) }) 699 } 700 } 701 702 var staticInt int 703 704 func benchmarkNextMemory(b *testing.B, n int) { 705 db, err := sql.Open(driverName, "file::memory:") 706 if err != nil { 707 b.Fatal(err) 708 } 709 710 defer func() { 711 db.Close() 712 }() 713 714 if _, err := db.Exec(` 715 create table t(i int); 716 begin; 717 `); err != nil { 718 b.Fatal(err) 719 } 720 721 s, err := db.Prepare("insert into t values(?)") 722 if err != nil { 723 b.Fatal(err) 724 } 725 726 for i := 0; i < n; i++ { 727 if _, err := s.Exec(int64(i)); err != nil { 728 b.Fatal(err) 729 } 730 } 731 if _, err := db.Exec(`commit;`); err != nil { 732 b.Fatal(err) 733 } 734 735 b.ReportAllocs() 736 b.ResetTimer() 737 for i := 0; i < b.N; i++ { 738 b.StopTimer() 739 r, err := db.Query("select * from t") 740 if err != nil { 741 b.Fatal(err) 742 } 743 744 b.StartTimer() 745 for i := 0; i < n; i++ { 746 if !r.Next() { 747 b.Fatal(err) 748 } 749 if err := r.Scan(&staticInt); err != nil { 750 b.Fatal(err) 751 } 752 } 753 b.StopTimer() 754 if err := r.Err(); err != nil { 755 b.Fatal(err) 756 } 757 758 r.Close() 759 } 760 if *oRecsPerSec { 761 b.SetBytes(1e6 * int64(n)) 762 } 763 } 764 765 func BenchmarkNextMemory(b *testing.B) { 766 for i, n := range []int{1e1, 1e2, 1e3, 1e4, 1e5, 1e6} { 767 b.Run(fmt.Sprintf("1e%d", i+1), func(b *testing.B) { benchmarkNextMemory(b, n) }) 768 } 769 } 770 771 // https://gitlab.com/cznic/sqlite/issues/11 772 func TestIssue11(t *testing.T) { 773 const N = 6570 774 dir, db := tempDB(t) 775 776 defer func() { 777 db.Close() 778 os.RemoveAll(dir) 779 }() 780 781 if _, err := db.Exec(` 782 CREATE TABLE t1 (t INT); 783 BEGIN; 784 `, 785 ); err != nil { 786 t.Fatal(err) 787 } 788 789 for i := 0; i < N; i++ { 790 if _, err := db.Exec("INSERT INTO t1 (t) VALUES (?)", i); err != nil { 791 t.Fatalf("#%v: %v", i, err) 792 } 793 } 794 if _, err := db.Exec("COMMIT;"); err != nil { 795 t.Fatal(err) 796 } 797 } 798 799 // https://gitlab.com/cznic/sqlite/issues/12 800 func TestMemDB(t *testing.T) { 801 // Verify we can create out-of-the heap memory DB instance. 802 db, err := sql.Open(driverName, "file::memory:") 803 if err != nil { 804 t.Fatal(err) 805 } 806 807 defer func() { 808 db.Close() 809 }() 810 811 v := strings.Repeat("a", 1024) 812 if _, err := db.Exec(` 813 create table t(s string); 814 begin; 815 `); err != nil { 816 t.Fatal(err) 817 } 818 819 s, err := db.Prepare("insert into t values(?)") 820 if err != nil { 821 t.Fatal(err) 822 } 823 824 // Heap used to be fixed at 32MB. 825 for i := 0; i < (64<<20)/len(v); i++ { 826 if _, err := s.Exec(v); err != nil { 827 t.Fatalf("%v * %v= %v: %v", i, len(v), i*len(v), err) 828 } 829 } 830 if _, err := db.Exec(`commit;`); err != nil { 831 t.Fatal(err) 832 } 833 } 834 835 func TestConcurrentGoroutines(t *testing.T) { 836 const ( 837 ngoroutines = 8 838 nrows = 5000 839 ) 840 841 dir, err := os.MkdirTemp("", "sqlite-test-") 842 if err != nil { 843 t.Fatal(err) 844 } 845 846 defer func() { 847 os.RemoveAll(dir) 848 }() 849 850 db, err := sql.Open(driverName, filepath.Join(dir, "test.db")) 851 if err != nil { 852 t.Fatal(err) 853 } 854 855 defer db.Close() 856 857 tx, err := db.BeginTx(context.Background(), nil) 858 if err != nil { 859 t.Fatal(err) 860 } 861 862 if _, err := tx.Exec("create table t(i)"); err != nil { 863 t.Fatal(err) 864 } 865 866 prep, err := tx.Prepare("insert into t values(?)") 867 if err != nil { 868 t.Fatal(err) 869 } 870 871 rnd := make(chan int, 100) 872 go func() { 873 lim := ngoroutines * nrows 874 rng, err := mathutil.NewFC32(0, lim-1, false) 875 if err != nil { 876 panic(fmt.Errorf("internal error: %v", err)) 877 } 878 879 for i := 0; i < lim; i++ { 880 rnd <- rng.Next() 881 } 882 }() 883 884 start := make(chan int) 885 var wg sync.WaitGroup 886 for i := 0; i < ngoroutines; i++ { 887 wg.Add(1) 888 889 go func(id int) { 890 891 defer wg.Done() 892 893 next: 894 for i := 0; i < nrows; i++ { 895 n := <-rnd 896 var err error 897 for j := 0; j < 10; j++ { 898 if _, err := prep.Exec(n); err == nil { 899 continue next 900 } 901 } 902 903 t.Errorf("id %d, seq %d: %v", id, i, err) 904 return 905 } 906 }(i) 907 } 908 t0 := time.Now() 909 close(start) 910 wg.Wait() 911 if err := tx.Commit(); err != nil { 912 t.Fatal(err) 913 } 914 915 d := time.Since(t0) 916 rows, err := db.Query("select * from t order by i") 917 if err != nil { 918 t.Fatal(err) 919 } 920 921 var i int 922 for ; rows.Next(); i++ { 923 var j int 924 if err := rows.Scan(&j); err != nil { 925 t.Fatalf("seq %d: %v", i, err) 926 } 927 928 if g, e := j, i; g != e { 929 t.Fatalf("seq %d: got %d, exp %d", i, g, e) 930 } 931 } 932 if err := rows.Err(); err != nil { 933 t.Fatal(err) 934 } 935 936 if g, e := i, ngoroutines*nrows; g != e { 937 t.Fatalf("got %d rows, expected %d", g, e) 938 } 939 940 t.Logf("%d goroutines concurrently inserted %d rows in %v", ngoroutines, ngoroutines*nrows, d) 941 } 942 943 func TestConcurrentProcesses(t *testing.T) { 944 if testing.Short() { 945 t.Skip("skipping test in short mode") 946 } 947 948 dir, err := os.MkdirTemp("", "sqlite-test-") 949 if err != nil { 950 t.Fatal(err) 951 } 952 953 defer func() { 954 os.RemoveAll(dir) 955 }() 956 957 m, err := filepath.Glob(filepath.FromSlash("internal/mptest/*")) 958 if err != nil { 959 t.Fatal(err) 960 } 961 962 for _, v := range m { 963 if s := filepath.Ext(v); s != ".test" && s != ".subtest" { 964 continue 965 } 966 967 b, err := os.ReadFile(v) 968 if err != nil { 969 t.Fatal(err) 970 } 971 972 if runtime.GOOS == "windows" { 973 // reference tests are in *nix format -- 974 // but git on windows does line-ending xlation by default 975 // if someone has it 'off' this has no impact. 976 // '\r\n' --> '\n' 977 b = bytes.ReplaceAll(b, []byte("\r\n"), []byte("\n")) 978 } 979 980 if err := os.WriteFile(filepath.Join(dir, filepath.Base(v)), b, 0666); err != nil { 981 t.Fatal(err) 982 } 983 } 984 985 bin := "./mptest" 986 if runtime.GOOS == "windows" { 987 bin += "mptest.exe" 988 } 989 args := []string{"build", "-o", filepath.Join(dir, bin)} 990 if s := *oXTags; s != "" { 991 args = append(args, "-tags", s) 992 } 993 args = append(args, "github.com/afumu/sqlite/internal/mptest") 994 out, err := exec.Command("go", args...).CombinedOutput() 995 if err != nil { 996 t.Fatalf("%s\n%v", out, err) 997 } 998 999 wd, err := os.Getwd() 1000 if err != nil { 1001 t.Fatal(err) 1002 } 1003 1004 defer os.Chdir(wd) 1005 1006 if err := os.Chdir(dir); err != nil { 1007 t.Fatal(err) 1008 } 1009 1010 outer: 1011 for _, script := range m { 1012 script = filepath.Base(script) 1013 if filepath.Ext(script) != ".test" { 1014 continue 1015 } 1016 1017 fmt.Printf("exec: %s db %s\n", filepath.FromSlash(bin), script) 1018 out, err := exec.Command(filepath.FromSlash(bin), "db", "--timeout", "6000000", script).CombinedOutput() 1019 if err != nil { 1020 t.Fatalf("%s\n%v", out, err) 1021 } 1022 1023 // just remove it so we don't get a 1024 // file busy race-condition 1025 // when we spin up the next script 1026 if runtime.GOOS == "windows" { 1027 _ = os.Remove("db") 1028 } 1029 1030 a := strings.Split(string(out), "\n") 1031 for _, v := range a { 1032 if strings.HasPrefix(v, "Summary:") { 1033 b := strings.Fields(v) 1034 if len(b) < 2 { 1035 t.Fatalf("unexpected format of %q", v) 1036 } 1037 1038 n, err := strconv.Atoi(b[1]) 1039 if err != nil { 1040 t.Fatalf("unexpected format of %q", v) 1041 } 1042 1043 if n != 0 { 1044 t.Errorf("%s", out) 1045 } 1046 1047 t.Logf("%v: %v", script, v) 1048 continue outer 1049 } 1050 1051 } 1052 t.Fatalf("%s\nerror: summary line not found", out) 1053 } 1054 } 1055 1056 // https://gitlab.com/cznic/sqlite/issues/19 1057 func TestIssue19(t *testing.T) { 1058 const ( 1059 drop = ` 1060 drop table if exists products; 1061 ` 1062 1063 up = ` 1064 CREATE TABLE IF NOT EXISTS "products" ( 1065 "id" VARCHAR(255), 1066 "user_id" VARCHAR(255), 1067 "name" VARCHAR(255), 1068 "description" VARCHAR(255), 1069 "created_at" BIGINT, 1070 "credits_price" BIGINT, 1071 "enabled" BOOLEAN, 1072 PRIMARY KEY("id") 1073 ); 1074 ` 1075 1076 productInsert = ` 1077 INSERT INTO "products" ("id", "user_id", "name", "description", "created_at", "credits_price", "enabled") VALUES ('9be4398c-d527-4efb-93a4-fc532cbaf804', '16935690-348b-41a6-bb20-f8bb16011015', 'dqdwqdwqdwqwqdwqd', 'qwdwqwqdwqdwqdwqd', '1577448686', '1', '0'); 1078 INSERT INTO "products" ("id", "user_id", "name", "description", "created_at", "credits_price", "enabled") VALUES ('759f10bd-9e1d-4ec7-b764-0868758d7b85', '16935690-348b-41a6-bb20-f8bb16011015', 'qdqwqwdwqdwqdwqwqd', 'wqdwqdwqdwqdwqdwq', '1577448692', '1', '1'); 1079 INSERT INTO "products" ("id", "user_id", "name", "description", "created_at", "credits_price", "enabled") VALUES ('512956e7-224d-4b2a-9153-b83a52c4aa38', '16935690-348b-41a6-bb20-f8bb16011015', 'qwdwqwdqwdqdwqwqd', 'wqdwdqwqdwqdwqdwqdwqdqw', '1577448699', '2', '1'); 1080 INSERT INTO "products" ("id", "user_id", "name", "description", "created_at", "credits_price", "enabled") VALUES ('02cd138f-6fa6-4909-9db7-a9d0eca4a7b7', '16935690-348b-41a6-bb20-f8bb16011015', 'qdwqdwqdwqwqdwdq', 'wqddwqwqdwqdwdqwdqwq', '1577448706', '3', '1'); 1081 ` 1082 ) 1083 1084 dir, err := os.MkdirTemp("", "sqlite-test-") 1085 if err != nil { 1086 t.Fatal(err) 1087 } 1088 1089 defer func() { 1090 os.RemoveAll(dir) 1091 }() 1092 1093 wd, err := os.Getwd() 1094 if err != nil { 1095 t.Fatal(err) 1096 } 1097 1098 defer os.Chdir(wd) 1099 1100 if err := os.Chdir(dir); err != nil { 1101 t.Fatal(err) 1102 } 1103 1104 db, err := sql.Open("sqlite", "test.db") 1105 if err != nil { 1106 t.Fatal("failed to connect database") 1107 } 1108 1109 defer db.Close() 1110 1111 db.SetMaxOpenConns(1) 1112 1113 if _, err = db.Exec(drop); err != nil { 1114 t.Fatal(err) 1115 } 1116 1117 if _, err = db.Exec(up); err != nil { 1118 t.Fatal(err) 1119 } 1120 1121 if _, err = db.Exec(productInsert); err != nil { 1122 t.Fatal(err) 1123 } 1124 1125 var count int64 1126 if err = db.QueryRow("select count(*) from products where user_id = ?", "16935690-348b-41a6-bb20-f8bb16011015").Scan(&count); err != nil { 1127 t.Fatal(err) 1128 } 1129 1130 if count != 4 { 1131 t.Fatalf("expected result for the count query %d, we received %d\n", 4, count) 1132 } 1133 1134 rows, err := db.Query("select * from products where user_id = ?", "16935690-348b-41a6-bb20-f8bb16011015") 1135 if err != nil { 1136 t.Fatal(err) 1137 } 1138 1139 count = 0 1140 for rows.Next() { 1141 count++ 1142 } 1143 if err := rows.Err(); err != nil { 1144 t.Fatal(err) 1145 } 1146 1147 if count != 4 { 1148 t.Fatalf("expected result for the select query %d, we received %d\n", 4, count) 1149 } 1150 1151 rows, err = db.Query("select * from products where enabled = ?", true) 1152 if err != nil { 1153 t.Fatal(err) 1154 } 1155 1156 count = 0 1157 for rows.Next() { 1158 count++ 1159 } 1160 if err := rows.Err(); err != nil { 1161 t.Fatal(err) 1162 } 1163 1164 if count != 3 { 1165 t.Fatalf("expected result for the enabled select query %d, we received %d\n", 3, count) 1166 } 1167 } 1168 1169 func mustExec(t *testing.T, db *sql.DB, sql string, args ...interface{}) sql.Result { 1170 res, err := db.Exec(sql, args...) 1171 if err != nil { 1172 t.Fatalf("Error running %q: %v", sql, err) 1173 } 1174 1175 return res 1176 } 1177 1178 // https://gitlab.com/cznic/sqlite/issues/20 1179 func TestIssue20(t *testing.T) { 1180 const TablePrefix = "gosqltest_" 1181 1182 tempDir, err := os.MkdirTemp("", "") 1183 if err != nil { 1184 t.Fatal(err) 1185 } 1186 1187 defer func() { 1188 os.RemoveAll(tempDir) 1189 }() 1190 1191 // go1.20rc1, linux/ppc64le VM 1192 // 10000 FAIL 1193 // 20000 FAIL 1194 // 40000 PASS 1195 // 30000 PASS 1196 // 25000 PASS 1197 db, err := sql.Open("sqlite", filepath.Join(tempDir, "foo.db")+"?_pragma=busy_timeout%3d50000") 1198 if err != nil { 1199 t.Fatalf("foo.db open fail: %v", err) 1200 } 1201 1202 defer db.Close() 1203 1204 mustExec(t, db, "CREATE TABLE "+TablePrefix+"t (count INT)") 1205 sel, err := db.PrepareContext(context.Background(), "SELECT count FROM "+TablePrefix+"t ORDER BY count DESC") 1206 if err != nil { 1207 t.Fatalf("prepare 1: %v", err) 1208 } 1209 1210 ins, err := db.PrepareContext(context.Background(), "INSERT INTO "+TablePrefix+"t (count) VALUES (?)") 1211 if err != nil { 1212 t.Fatalf("prepare 2: %v", err) 1213 } 1214 1215 for n := 1; n <= 3; n++ { 1216 if _, err := ins.Exec(n); err != nil { 1217 t.Fatalf("insert(%d) = %v", n, err) 1218 } 1219 } 1220 1221 const nRuns = 10 1222 ch := make(chan bool) 1223 for i := 0; i < nRuns; i++ { 1224 go func() { 1225 defer func() { 1226 ch <- true 1227 }() 1228 for j := 0; j < 10; j++ { 1229 count := 0 1230 if err := sel.QueryRow().Scan(&count); err != nil && err != sql.ErrNoRows { 1231 t.Errorf("Query: %v", err) 1232 return 1233 } 1234 1235 if _, err := ins.Exec(rand.Intn(100)); err != nil { 1236 t.Errorf("Insert: %v", err) 1237 return 1238 } 1239 } 1240 }() 1241 } 1242 for i := 0; i < nRuns; i++ { 1243 <-ch 1244 } 1245 } 1246 1247 func TestNoRows(t *testing.T) { 1248 tempDir, err := os.MkdirTemp("", "") 1249 if err != nil { 1250 t.Fatal(err) 1251 } 1252 1253 defer func() { 1254 os.RemoveAll(tempDir) 1255 }() 1256 1257 db, err := sql.Open("sqlite", filepath.Join(tempDir, "foo.db")) 1258 if err != nil { 1259 t.Fatalf("foo.db open fail: %v", err) 1260 } 1261 1262 defer func() { 1263 db.Close() 1264 }() 1265 1266 stmt, err := db.Prepare("create table t(i);") 1267 if err != nil { 1268 t.Fatal(err) 1269 } 1270 1271 defer stmt.Close() 1272 1273 if _, err := stmt.Query(); err != nil { 1274 t.Fatal(err) 1275 } 1276 } 1277 1278 func TestColumns(t *testing.T) { 1279 db, err := sql.Open("sqlite", "file::memory:") 1280 if err != nil { 1281 t.Fatal(err) 1282 } 1283 defer db.Close() 1284 1285 if _, err := db.Exec("create table t1(a integer, b text, c blob)"); err != nil { 1286 t.Fatal(err) 1287 } 1288 1289 if _, err := db.Exec("insert into t1 (a) values (1)"); err != nil { 1290 t.Fatal(err) 1291 } 1292 1293 rows, err := db.Query("select * from t1") 1294 if err != nil { 1295 t.Fatal(err) 1296 } 1297 defer rows.Close() 1298 1299 got, err := rows.Columns() 1300 if err != nil { 1301 t.Fatal(err) 1302 } 1303 1304 want := []string{"a", "b", "c"} 1305 if !reflect.DeepEqual(got, want) { 1306 t.Errorf("got columns %v, want %v", got, want) 1307 } 1308 } 1309 1310 // https://gitlab.com/cznic/sqlite/-/issues/32 1311 func TestColumnsNoRows(t *testing.T) { 1312 db, err := sql.Open("sqlite", "file::memory:") 1313 if err != nil { 1314 t.Fatal(err) 1315 } 1316 defer db.Close() 1317 1318 if _, err := db.Exec("create table t1(a integer, b text, c blob)"); err != nil { 1319 t.Fatal(err) 1320 } 1321 1322 rows, err := db.Query("select * from t1") 1323 if err != nil { 1324 t.Fatal(err) 1325 } 1326 defer rows.Close() 1327 1328 got, err := rows.Columns() 1329 if err != nil { 1330 t.Fatal(err) 1331 } 1332 1333 want := []string{"a", "b", "c"} 1334 if !reflect.DeepEqual(got, want) { 1335 t.Errorf("got columns %v, want %v", got, want) 1336 } 1337 } 1338 1339 // https://gitlab.com/cznic/sqlite/-/issues/28 1340 func TestIssue28(t *testing.T) { 1341 tempDir, err := os.MkdirTemp("", "") 1342 if err != nil { 1343 t.Fatal(err) 1344 } 1345 1346 defer func() { 1347 os.RemoveAll(tempDir) 1348 }() 1349 1350 db, err := sql.Open("sqlite", filepath.Join(tempDir, "test.db")) 1351 if err != nil { 1352 t.Fatalf("test.db open fail: %v", err) 1353 } 1354 1355 defer db.Close() 1356 1357 if _, err := db.Exec(`CREATE TABLE test (foo TEXT)`); err != nil { 1358 t.Fatal(err) 1359 } 1360 1361 row := db.QueryRow(`SELECT foo FROM test`) 1362 var foo string 1363 if err = row.Scan(&foo); err != sql.ErrNoRows { 1364 t.Fatalf("got %T(%[1]v), expected %T(%[2]v)", err, sql.ErrNoRows) 1365 } 1366 } 1367 1368 // https://gitlab.com/cznic/sqlite/-/issues/30 1369 func TestColumnTypes(t *testing.T) { 1370 tempDir, err := os.MkdirTemp("", "") 1371 if err != nil { 1372 t.Fatal(err) 1373 } 1374 1375 defer func() { 1376 os.RemoveAll(tempDir) 1377 }() 1378 1379 db, err := sql.Open("sqlite", filepath.Join(tempDir, "test.db")) 1380 if err != nil { 1381 t.Fatalf("test.db open fail: %v", err) 1382 } 1383 1384 defer db.Close() 1385 1386 _, err = db.Exec("CREATE TABLE IF NOT EXISTS `userinfo` (`uid` INTEGER PRIMARY KEY AUTOINCREMENT,`username` VARCHAR(64) NULL, `departname` VARCHAR(64) NULL, `created` DATE NULL);") 1387 if err != nil { 1388 t.Fatal(err) 1389 } 1390 1391 insertStatement := `INSERT INTO userinfo(username, departname, created) values("astaxie", "研发部门", "2012-12-09")` 1392 _, err = db.Query(insertStatement) 1393 if err != nil { 1394 t.Fatal(err) 1395 } 1396 1397 rows2, err := db.Query("SELECT * FROM userinfo") 1398 if err != nil { 1399 t.Fatal(err) 1400 } 1401 defer rows2.Close() 1402 1403 columnTypes, err := rows2.ColumnTypes() 1404 if err != nil { 1405 t.Fatal(err) 1406 } 1407 1408 var b strings.Builder 1409 for index, value := range columnTypes { 1410 precision, scale, precisionOk := value.DecimalSize() 1411 length, lengthOk := value.Length() 1412 nullable, nullableOk := value.Nullable() 1413 fmt.Fprintf(&b, "Col %d: DatabaseTypeName %q, DecimalSize %v %v %v, Length %v %v, Name %q, Nullable %v %v, ScanType %q\n", 1414 index, 1415 value.DatabaseTypeName(), 1416 precision, scale, precisionOk, 1417 length, lengthOk, 1418 value.Name(), 1419 nullable, nullableOk, 1420 value.ScanType(), 1421 ) 1422 } 1423 if err := rows2.Err(); err != nil { 1424 t.Fatal(err) 1425 } 1426 1427 if g, e := b.String(), `Col 0: DatabaseTypeName "INTEGER", DecimalSize 0 0 false, Length 0 false, Name "uid", Nullable true true, ScanType "int64" 1428 Col 1: DatabaseTypeName "VARCHAR(64)", DecimalSize 0 0 false, Length 9223372036854775807 true, Name "username", Nullable true true, ScanType "string" 1429 Col 2: DatabaseTypeName "VARCHAR(64)", DecimalSize 0 0 false, Length 9223372036854775807 true, Name "departname", Nullable true true, ScanType "string" 1430 Col 3: DatabaseTypeName "DATE", DecimalSize 0 0 false, Length 9223372036854775807 true, Name "created", Nullable true true, ScanType "string" 1431 `; g != e { 1432 t.Fatalf("---- got\n%s\n----expected\n%s", g, e) 1433 } 1434 t.Log(b.String()) 1435 } 1436 1437 // https://gitlab.com/cznic/sqlite/-/issues/32 1438 func TestColumnTypesNoRows(t *testing.T) { 1439 tempDir, err := os.MkdirTemp("", "") 1440 if err != nil { 1441 t.Fatal(err) 1442 } 1443 1444 defer func() { 1445 os.RemoveAll(tempDir) 1446 }() 1447 1448 db, err := sql.Open("sqlite", filepath.Join(tempDir, "test.db")) 1449 if err != nil { 1450 t.Fatalf("test.db open fail: %v", err) 1451 } 1452 1453 defer db.Close() 1454 1455 _, err = db.Exec("CREATE TABLE IF NOT EXISTS `userinfo` (`uid` INTEGER PRIMARY KEY AUTOINCREMENT,`username` VARCHAR(64) NULL, `departname` VARCHAR(64) NULL, `created` DATE NULL);") 1456 if err != nil { 1457 t.Fatal(err) 1458 } 1459 1460 rows2, err := db.Query("SELECT * FROM userinfo") 1461 if err != nil { 1462 t.Fatal(err) 1463 } 1464 defer rows2.Close() 1465 1466 columnTypes, err := rows2.ColumnTypes() 1467 if err != nil { 1468 t.Fatal(err) 1469 } 1470 1471 var b strings.Builder 1472 for index, value := range columnTypes { 1473 precision, scale, precisionOk := value.DecimalSize() 1474 length, lengthOk := value.Length() 1475 nullable, nullableOk := value.Nullable() 1476 fmt.Fprintf(&b, "Col %d: DatabaseTypeName %q, DecimalSize %v %v %v, Length %v %v, Name %q, Nullable %v %v, ScanType %q\n", 1477 index, 1478 value.DatabaseTypeName(), 1479 precision, scale, precisionOk, 1480 length, lengthOk, 1481 value.Name(), 1482 nullable, nullableOk, 1483 value.ScanType(), 1484 ) 1485 } 1486 if err := rows2.Err(); err != nil { 1487 t.Fatal(err) 1488 } 1489 1490 if g, e := b.String(), `Col 0: DatabaseTypeName "INTEGER", DecimalSize 0 0 false, Length 0 false, Name "uid", Nullable true true, ScanType %!q(<nil>) 1491 Col 1: DatabaseTypeName "VARCHAR(64)", DecimalSize 0 0 false, Length 0 false, Name "username", Nullable true true, ScanType %!q(<nil>) 1492 Col 2: DatabaseTypeName "VARCHAR(64)", DecimalSize 0 0 false, Length 0 false, Name "departname", Nullable true true, ScanType %!q(<nil>) 1493 Col 3: DatabaseTypeName "DATE", DecimalSize 0 0 false, Length 0 false, Name "created", Nullable true true, ScanType %!q(<nil>) 1494 `; g != e { 1495 t.Fatalf("---- got\n%s\n----expected\n%s", g, e) 1496 } 1497 t.Log(b.String()) 1498 } 1499 1500 // https://gitlab.com/cznic/sqlite/-/issues/35 1501 func TestTime(t *testing.T) { 1502 types := []string{ 1503 "DATE", 1504 "DATETIME", 1505 "Date", 1506 "DateTime", 1507 "TIMESTAMP", 1508 "TimeStamp", 1509 "date", 1510 "datetime", 1511 "timestamp", 1512 } 1513 db, err := sql.Open(driverName, "file::memory:") 1514 if err != nil { 1515 t.Fatal(err) 1516 } 1517 1518 defer func() { 1519 db.Close() 1520 }() 1521 1522 for _, typ := range types { 1523 if _, err := db.Exec(fmt.Sprintf(` 1524 drop table if exists mg; 1525 create table mg (applied_at %s); 1526 `, typ)); err != nil { 1527 t.Fatal(err) 1528 } 1529 1530 now := time.Now() 1531 _, err = db.Exec(`INSERT INTO mg (applied_at) VALUES (?)`, &now) 1532 if err != nil { 1533 t.Fatal(err) 1534 } 1535 1536 var appliedAt time.Time 1537 err = db.QueryRow("SELECT applied_at FROM mg").Scan(&appliedAt) 1538 if err != nil { 1539 t.Fatal(err) 1540 } 1541 1542 if g, e := appliedAt, now; !g.Equal(e) { 1543 t.Fatal(g, e) 1544 } 1545 } 1546 } 1547 1548 // https://gitlab.com/cznic/sqlite/-/issues/46 1549 func TestTimeScan(t *testing.T) { 1550 ref := time.Date(2021, 1, 2, 16, 39, 17, 123456789, time.UTC) 1551 1552 cases := []struct { 1553 s string 1554 w time.Time 1555 }{ 1556 {s: "2021-01-02 12:39:17 -0400 ADT m=+00000", w: ref.Truncate(time.Second)}, 1557 {s: "2021-01-02 16:39:17 +0000 UTC m=+0.000000001", w: ref.Truncate(time.Second)}, 1558 {s: "2021-01-02 12:39:17.123456 -0400 ADT m=+00000", w: ref.Truncate(time.Microsecond)}, 1559 {s: "2021-01-02 16:39:17.123456 +0000 UTC m=+0.000000001", w: ref.Truncate(time.Microsecond)}, 1560 {s: "2021-01-02 16:39:17Z", w: ref.Truncate(time.Second)}, 1561 {s: "2021-01-02 16:39:17+00:00", w: ref.Truncate(time.Second)}, 1562 {s: "2021-01-02T16:39:17.123456+00:00", w: ref.Truncate(time.Microsecond)}, 1563 {s: "2021-01-02 16:39:17.123456+00:00", w: ref.Truncate(time.Microsecond)}, 1564 {s: "2021-01-02 16:39:17.123456Z", w: ref.Truncate(time.Microsecond)}, 1565 {s: "2021-01-02 12:39:17-04:00", w: ref.Truncate(time.Second)}, 1566 {s: "2021-01-02 16:39:17", w: ref.Truncate(time.Second)}, 1567 {s: "2021-01-02T16:39:17", w: ref.Truncate(time.Second)}, 1568 {s: "2021-01-02 16:39", w: ref.Truncate(time.Minute)}, 1569 {s: "2021-01-02T16:39", w: ref.Truncate(time.Minute)}, 1570 {s: "2021-01-02", w: ref.Truncate(24 * time.Hour)}, 1571 } 1572 1573 db, err := sql.Open(driverName, "file::memory:") 1574 if err != nil { 1575 t.Fatal(err) 1576 } 1577 defer db.Close() 1578 1579 for _, colType := range []string{"DATE", "DATETIME", "TIMESTAMP"} { 1580 for _, tc := range cases { 1581 if _, err := db.Exec("drop table if exists x; create table x (y " + colType + ")"); err != nil { 1582 t.Fatal(err) 1583 } 1584 if _, err := db.Exec("insert into x (y) values (?)", tc.s); err != nil { 1585 t.Fatal(err) 1586 } 1587 1588 var got time.Time 1589 if err := db.QueryRow("select y from x").Scan(&got); err != nil { 1590 t.Fatal(err) 1591 } 1592 if !got.Equal(tc.w) { 1593 t.Errorf("scan(%q as %q) = %s, want %s", tc.s, colType, got, tc.w) 1594 } 1595 } 1596 } 1597 } 1598 1599 // https://gitlab.com/cznic/sqlite/-/issues/49 1600 func TestTimeLocaltime(t *testing.T) { 1601 db, err := sql.Open(driverName, "file::memory:") 1602 if err != nil { 1603 t.Fatal(err) 1604 } 1605 defer db.Close() 1606 1607 if _, err := db.Exec("select datetime('now', 'localtime')"); err != nil { 1608 t.Fatal(err) 1609 } 1610 } 1611 1612 func TestTimeFormat(t *testing.T) { 1613 ref := time.Date(2021, 1, 2, 16, 39, 17, 123456789, time.UTC) 1614 1615 cases := []struct { 1616 f string 1617 w string 1618 }{ 1619 {f: "", w: "2021-01-02 16:39:17.123456789 +0000 UTC"}, 1620 {f: "sqlite", w: "2021-01-02 16:39:17.123456789+00:00"}, 1621 } 1622 for _, c := range cases { 1623 t.Run("", func(t *testing.T) { 1624 dsn := "file::memory:" 1625 if c.f != "" { 1626 q := make(url.Values) 1627 q.Set("_time_format", c.f) 1628 dsn += "?" + q.Encode() 1629 } 1630 db, err := sql.Open(driverName, dsn) 1631 if err != nil { 1632 t.Fatal(err) 1633 } 1634 defer db.Close() 1635 1636 if _, err := db.Exec("drop table if exists x; create table x (y text)"); err != nil { 1637 t.Fatal(err) 1638 } 1639 1640 if _, err := db.Exec(`insert into x values (?)`, ref); err != nil { 1641 t.Fatal(err) 1642 } 1643 1644 var got string 1645 if err := db.QueryRow(`select y from x`).Scan(&got); err != nil { 1646 t.Fatal(err) 1647 } 1648 1649 if got != c.w { 1650 t.Fatal(got, c.w) 1651 } 1652 }) 1653 } 1654 } 1655 1656 func TestTimeFormatBad(t *testing.T) { 1657 db, err := sql.Open(driverName, "file::memory:?_time_format=bogus") 1658 if err != nil { 1659 t.Fatal(err) 1660 } 1661 defer db.Close() 1662 1663 // Error doesn't appear until a connection is opened. 1664 _, err = db.Exec("select 1") 1665 if err == nil { 1666 t.Fatal("wanted error") 1667 } 1668 1669 want := `unknown _time_format "bogus"` 1670 if got := err.Error(); got != want { 1671 t.Fatalf("got error %q, want %q", got, want) 1672 } 1673 } 1674 1675 // https://sqlite.org/lang_expr.html#varparam 1676 // https://gitlab.com/cznic/sqlite/-/issues/42 1677 func TestBinding(t *testing.T) { 1678 t.Run("DB", func(t *testing.T) { 1679 testBinding(t, func(db *sql.DB, query string, args ...interface{}) (*sql.Row, func()) { 1680 return db.QueryRow(query, args...), func() {} 1681 }) 1682 }) 1683 1684 t.Run("Prepare", func(t *testing.T) { 1685 testBinding(t, func(db *sql.DB, query string, args ...interface{}) (*sql.Row, func()) { 1686 stmt, err := db.Prepare(query) 1687 if err != nil { 1688 t.Fatal(err) 1689 } 1690 return stmt.QueryRow(args...), func() { stmt.Close() } 1691 }) 1692 }) 1693 } 1694 1695 func testBinding(t *testing.T, query func(db *sql.DB, query string, args ...interface{}) (*sql.Row, func())) { 1696 db, err := sql.Open(driverName, "file::memory:") 1697 if err != nil { 1698 t.Fatal(err) 1699 } 1700 defer db.Close() 1701 1702 for _, tc := range []struct { 1703 q string 1704 in []interface{} 1705 w []int 1706 }{ 1707 { 1708 q: "?, ?, ?", 1709 in: []interface{}{1, 2, 3}, 1710 w: []int{1, 2, 3}, 1711 }, 1712 { 1713 q: "?1, ?2, ?3", 1714 in: []interface{}{1, 2, 3}, 1715 w: []int{1, 2, 3}, 1716 }, 1717 { 1718 q: "?1, ?, ?3", 1719 in: []interface{}{1, 2, 3}, 1720 w: []int{1, 2, 3}, 1721 }, 1722 { 1723 q: "?3, ?2, ?1", 1724 in: []interface{}{1, 2, 3}, 1725 w: []int{3, 2, 1}, 1726 }, 1727 { 1728 q: "?1, ?1, ?2", 1729 in: []interface{}{1, 2}, 1730 w: []int{1, 1, 2}, 1731 }, 1732 { 1733 q: ":one, :two, :three", 1734 in: []interface{}{sql.Named("one", 1), sql.Named("two", 2), sql.Named("three", 3)}, 1735 w: []int{1, 2, 3}, 1736 }, 1737 { 1738 q: ":one, :one, :two", 1739 in: []interface{}{sql.Named("one", 1), sql.Named("two", 2)}, 1740 w: []int{1, 1, 2}, 1741 }, 1742 { 1743 q: "@one, @two, @three", 1744 in: []interface{}{sql.Named("one", 1), sql.Named("two", 2), sql.Named("three", 3)}, 1745 w: []int{1, 2, 3}, 1746 }, 1747 { 1748 q: "@one, @one, @two", 1749 in: []interface{}{sql.Named("one", 1), sql.Named("two", 2)}, 1750 w: []int{1, 1, 2}, 1751 }, 1752 { 1753 q: "$one, $two, $three", 1754 in: []interface{}{sql.Named("one", 1), sql.Named("two", 2), sql.Named("three", 3)}, 1755 w: []int{1, 2, 3}, 1756 }, 1757 { 1758 // A common usage that should technically require sql.Named but 1759 // does not. 1760 q: "$1, $2, $3", 1761 in: []interface{}{1, 2, 3}, 1762 w: []int{1, 2, 3}, 1763 }, 1764 { 1765 q: "$one, $one, $two", 1766 in: []interface{}{sql.Named("one", 1), sql.Named("two", 2)}, 1767 w: []int{1, 1, 2}, 1768 }, 1769 { 1770 q: ":one, @one, $one", 1771 in: []interface{}{sql.Named("one", 1)}, 1772 w: []int{1, 1, 1}, 1773 }, 1774 } { 1775 got := make([]int, len(tc.w)) 1776 ptrs := make([]interface{}, len(got)) 1777 for i := range got { 1778 ptrs[i] = &got[i] 1779 } 1780 1781 row, cleanup := query(db, "select "+tc.q, tc.in...) 1782 defer cleanup() 1783 1784 if err := row.Scan(ptrs...); err != nil { 1785 t.Errorf("query(%q, %+v) = %s", tc.q, tc.in, err) 1786 continue 1787 } 1788 1789 if !reflect.DeepEqual(got, tc.w) { 1790 t.Errorf("query(%q, %+v) = %#+v, want %#+v", tc.q, tc.in, got, tc.w) 1791 } 1792 } 1793 } 1794 1795 func TestBindingError(t *testing.T) { 1796 t.Run("DB", func(t *testing.T) { 1797 testBindingError(t, func(db *sql.DB, query string, args ...interface{}) (*sql.Row, func()) { 1798 return db.QueryRow(query, args...), func() {} 1799 }) 1800 }) 1801 1802 t.Run("Prepare", func(t *testing.T) { 1803 testBindingError(t, func(db *sql.DB, query string, args ...interface{}) (*sql.Row, func()) { 1804 stmt, err := db.Prepare(query) 1805 if err != nil { 1806 t.Fatal(err) 1807 } 1808 return stmt.QueryRow(args...), func() { stmt.Close() } 1809 }) 1810 }) 1811 } 1812 1813 func testBindingError(t *testing.T, query func(db *sql.DB, query string, args ...interface{}) (*sql.Row, func())) { 1814 db, err := sql.Open(driverName, "file::memory:") 1815 if err != nil { 1816 t.Fatal(err) 1817 } 1818 defer db.Close() 1819 1820 for _, tc := range []struct { 1821 q string 1822 in []interface{} 1823 }{ 1824 { 1825 q: "?", 1826 in: []interface{}{}, 1827 }, 1828 { 1829 q: "?500, ?", 1830 in: []interface{}{1, 2}, 1831 }, 1832 { 1833 q: ":one", 1834 in: []interface{}{1}, 1835 }, 1836 { 1837 q: "@one", 1838 in: []interface{}{1}, 1839 }, 1840 { 1841 q: "$one", 1842 in: []interface{}{1}, 1843 }, 1844 } { 1845 got := make([]int, 2) 1846 ptrs := make([]interface{}, len(got)) 1847 for i := range got { 1848 ptrs[i] = &got[i] 1849 } 1850 1851 row, cleanup := query(db, "select "+tc.q, tc.in...) 1852 defer cleanup() 1853 1854 err := row.Scan(ptrs...) 1855 if err == nil || (!strings.Contains(err.Error(), "missing argument with index") && !strings.Contains(err.Error(), "missing named argument")) { 1856 t.Errorf("query(%q, %+v) unexpected error %+v", tc.q, tc.in, err) 1857 } 1858 } 1859 } 1860 1861 // https://gitlab.com/cznic/sqlite/-/issues/51 1862 func TestIssue51(t *testing.T) { 1863 if testing.Short() { 1864 t.Skip("skipping test in short mode") 1865 } 1866 1867 tempDir, err := os.MkdirTemp("", "") 1868 if err != nil { 1869 t.Fatal(err) 1870 } 1871 1872 defer func() { 1873 os.RemoveAll(tempDir) 1874 }() 1875 1876 fn := filepath.Join(tempDir, "test_issue51.db") 1877 db, err := sql.Open(driverName, fn) 1878 if err != nil { 1879 t.Fatal(err) 1880 } 1881 1882 defer func() { 1883 db.Close() 1884 }() 1885 1886 if _, err := db.Exec(` 1887 CREATE TABLE fileHash ( 1888 "hash" TEXT NOT NULL PRIMARY KEY, 1889 "filename" TEXT, 1890 "lastChecked" INTEGER 1891 );`); err != nil { 1892 t.Fatal(err) 1893 } 1894 1895 t0 := time.Now() 1896 n := 0 1897 for time.Since(t0) < time.Minute { 1898 hash := randomString() 1899 if _, err = lookupHash(fn, hash); err != nil { 1900 t.Fatal(err) 1901 } 1902 1903 if err = saveHash(fn, hash, hash+".temp"); err != nil { 1904 t.Error(err) 1905 break 1906 } 1907 n++ 1908 } 1909 t.Logf("cycles: %v", n) 1910 row := db.QueryRow("select count(*) from fileHash") 1911 if err := row.Scan(&n); err != nil { 1912 t.Fatal(err) 1913 } 1914 1915 t.Logf("DB records: %v", n) 1916 } 1917 1918 func saveHash(dbFile string, hash string, fileName string) (err error) { 1919 db, err := sql.Open("sqlite", dbFile) 1920 if err != nil { 1921 return fmt.Errorf("could not open database: %v", err) 1922 } 1923 1924 defer func() { 1925 if err2 := db.Close(); err2 != nil && err == nil { 1926 err = fmt.Errorf("could not close the database: %s", err2) 1927 } 1928 }() 1929 1930 query := `INSERT OR REPLACE INTO fileHash(hash, fileName, lastChecked) 1931 VALUES(?, ?, ?);` 1932 rows, err := executeSQL(db, query, hash, fileName, time.Now().Unix()) 1933 if err != nil { 1934 return fmt.Errorf("error saving hash to database: %v", err) 1935 } 1936 defer rows.Close() 1937 1938 return nil 1939 } 1940 1941 func executeSQL(db *sql.DB, query string, values ...interface{}) (*sql.Rows, error) { 1942 statement, err := db.Prepare(query) 1943 if err != nil { 1944 return nil, fmt.Errorf("could not prepare statement: %v", err) 1945 } 1946 defer statement.Close() 1947 1948 return statement.Query(values...) 1949 } 1950 1951 func lookupHash(dbFile string, hash string) (ok bool, err error) { 1952 db, err := sql.Open("sqlite", dbFile) 1953 if err != nil { 1954 return false, fmt.Errorf("could not open database: %n", err) 1955 } 1956 1957 defer func() { 1958 if err2 := db.Close(); err2 != nil && err == nil { 1959 err = fmt.Errorf("could not close the database: %v", err2) 1960 } 1961 }() 1962 1963 query := `SELECT hash, fileName, lastChecked 1964 FROM fileHash 1965 WHERE hash=?;` 1966 rows, err := executeSQL(db, query, hash) 1967 if err != nil { 1968 return false, fmt.Errorf("error checking database for hash: %n", err) 1969 } 1970 1971 defer func() { 1972 if err2 := rows.Close(); err2 != nil && err == nil { 1973 err = fmt.Errorf("could not close DB rows: %v", err2) 1974 } 1975 }() 1976 1977 var ( 1978 dbHash string 1979 fileName string 1980 lastChecked int64 1981 ) 1982 for rows.Next() { 1983 err = rows.Scan(&dbHash, &fileName, &lastChecked) 1984 if err != nil { 1985 return false, fmt.Errorf("could not read DB row: %v", err) 1986 } 1987 } 1988 return false, rows.Err() 1989 } 1990 1991 func randomString() string { 1992 b := make([]byte, 32) 1993 for i := range b { 1994 b[i] = charset[seededRand.Intn(len(charset))] 1995 } 1996 return string(b) 1997 } 1998 1999 var seededRand *rand.Rand = rand.New(rand.NewSource(time.Now().UnixNano())) 2000 2001 const charset = "abcdefghijklmnopqrstuvwxyz" + 2002 "ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789" 2003 2004 // https://gitlab.com/cznic/sqlite/-/issues/53 2005 func TestIssue53(t *testing.T) { 2006 tempDir, err := os.MkdirTemp("", "") 2007 if err != nil { 2008 t.Fatal(err) 2009 } 2010 2011 defer func() { 2012 os.RemoveAll(tempDir) 2013 }() 2014 2015 wd, err := os.Getwd() 2016 if err != nil { 2017 t.Fatal(err) 2018 } 2019 2020 defer os.Chdir(wd) 2021 2022 if err := os.Chdir(tempDir); err != nil { 2023 t.Fatal(err) 2024 } 2025 2026 const fn = "testissue53.sqlite" 2027 2028 db, err := sql.Open(driverName, fn) 2029 if err != nil { 2030 t.Fatal(err) 2031 } 2032 2033 defer func() { 2034 db.Close() 2035 }() 2036 2037 if _, err := db.Exec(` 2038 CREATE TABLE IF NOT EXISTS loginst ( 2039 instid INTEGER PRIMARY KEY, 2040 name VARCHAR UNIQUE 2041 ); 2042 `); err != nil { 2043 t.Fatal(err) 2044 } 2045 2046 tx, err := db.Begin() 2047 if err != nil { 2048 t.Fatal(err) 2049 } 2050 2051 for i := 0; i < 5000; i++ { 2052 x := fmt.Sprintf("foo%d", i) 2053 var id int 2054 if err := tx.QueryRow("INSERT OR IGNORE INTO loginst (name) VALUES (?); SELECT instid FROM loginst WHERE name = ?", x, x).Scan(&id); err != nil { 2055 t.Fatal(err) 2056 } 2057 } 2058 2059 } 2060 2061 // https://gitlab.com/cznic/sqlite/-/issues/37 2062 func TestPersistPragma(t *testing.T) { 2063 tempDir, err := os.MkdirTemp("", "") 2064 if err != nil { 2065 t.Fatal(err) 2066 } 2067 2068 defer func() { 2069 os.RemoveAll(tempDir) 2070 }() 2071 2072 wd, err := os.Getwd() 2073 if err != nil { 2074 t.Fatal(err) 2075 } 2076 2077 defer os.Chdir(wd) 2078 2079 if err := os.Chdir(tempDir); err != nil { 2080 t.Fatal(err) 2081 } 2082 2083 pragmas := []pragmaCfg{ 2084 {"foreign_keys", "on", int64(1)}, 2085 {"analysis_limit", "1000", int64(1000)}, 2086 {"application_id", "214", int64(214)}, 2087 {"encoding", "'UTF-16le'", "UTF-16le"}} 2088 2089 if err := testPragmas("testpersistpragma.sqlite", "testpersistpragma.sqlite", pragmas); err != nil { 2090 t.Fatal(err) 2091 } 2092 if err := testPragmas("file::memory:", "", pragmas); err != nil { 2093 t.Fatal(err) 2094 } 2095 if err := testPragmas(":memory:", "", pragmas); err != nil { 2096 t.Fatal(err) 2097 } 2098 } 2099 2100 type pragmaCfg struct { 2101 name string 2102 value string 2103 expected interface{} 2104 } 2105 2106 func testPragmas(name, diskFile string, pragmas []pragmaCfg) error { 2107 if diskFile != "" { 2108 os.Remove(diskFile) 2109 } 2110 2111 q := url.Values{} 2112 for _, pragma := range pragmas { 2113 q.Add("_pragma", pragma.name+"="+pragma.value) 2114 } 2115 2116 dsn := name + "?" + q.Encode() 2117 db, err := sql.Open(driverName, dsn) 2118 if err != nil { 2119 return err 2120 } 2121 2122 db.SetMaxOpenConns(1) 2123 2124 if err := checkPragmas(db, pragmas); err != nil { 2125 return err 2126 } 2127 2128 c, err := db.Conn(context.Background()) 2129 if err != nil { 2130 return err 2131 } 2132 2133 // Kill the connection to spawn a new one. Pragma configs should persist 2134 c.Raw(func(interface{}) error { return driver.ErrBadConn }) 2135 2136 if err := checkPragmas(db, pragmas); err != nil { 2137 return err 2138 } 2139 2140 if diskFile == "" { 2141 // Make sure in memory databases aren't being written to disk 2142 return testInMemory(db) 2143 } 2144 2145 return nil 2146 } 2147 2148 func checkPragmas(db *sql.DB, pragmas []pragmaCfg) error { 2149 for _, pragma := range pragmas { 2150 row := db.QueryRow(`PRAGMA ` + pragma.name) 2151 2152 var result interface{} 2153 if err := row.Scan(&result); err != nil { 2154 return err 2155 } 2156 if result != pragma.expected { 2157 return fmt.Errorf("expected PRAGMA %s to return %v but got %v", pragma.name, pragma.expected, result) 2158 } 2159 } 2160 return nil 2161 } 2162 2163 func TestInMemory(t *testing.T) { 2164 tempDir, err := os.MkdirTemp("", "") 2165 if err != nil { 2166 t.Fatal(err) 2167 } 2168 2169 defer func() { 2170 os.RemoveAll(tempDir) 2171 }() 2172 2173 wd, err := os.Getwd() 2174 if err != nil { 2175 t.Fatal(err) 2176 } 2177 2178 defer os.Chdir(wd) 2179 2180 if err := os.Chdir(tempDir); err != nil { 2181 t.Fatal(err) 2182 } 2183 2184 if err := testMemoryPath(":memory:"); err != nil { 2185 t.Fatal(err) 2186 } 2187 if err := testMemoryPath("file::memory:"); err != nil { 2188 t.Fatal(err) 2189 } 2190 2191 // This parameter should be ignored 2192 q := url.Values{} 2193 q.Add("mode", "readonly") 2194 if err := testMemoryPath(":memory:?" + q.Encode()); err != nil { 2195 t.Fatal(err) 2196 } 2197 } 2198 2199 func testMemoryPath(mPath string) error { 2200 db, err := sql.Open(driverName, mPath) 2201 if err != nil { 2202 return err 2203 } 2204 defer db.Close() 2205 2206 return testInMemory(db) 2207 } 2208 2209 func testInMemory(db *sql.DB) error { 2210 _, err := db.Exec(` 2211 create table in_memory_test(i int, f double); 2212 insert into in_memory_test values(12, 3.14); 2213 `) 2214 if err != nil { 2215 return err 2216 } 2217 2218 dirEntries, err := os.ReadDir("./") 2219 if err != nil { 2220 return err 2221 } 2222 2223 for _, dirEntry := range dirEntries { 2224 if strings.Contains(dirEntry.Name(), "memory") { 2225 return fmt.Errorf("file was created for in memory database") 2226 } 2227 } 2228 2229 return nil 2230 } 2231 2232 func emptyDir(s string) error { 2233 m, err := filepath.Glob(filepath.FromSlash(s + "/*")) 2234 if err != nil { 2235 return err 2236 } 2237 2238 for _, v := range m { 2239 fi, err := os.Stat(v) 2240 if err != nil { 2241 return err 2242 } 2243 2244 switch { 2245 case fi.IsDir(): 2246 if err = os.RemoveAll(v); err != nil { 2247 return err 2248 } 2249 default: 2250 if err = os.Remove(v); err != nil { 2251 return err 2252 } 2253 } 2254 } 2255 return nil 2256 } 2257 2258 // https://gitlab.com/cznic/sqlite/-/issues/70 2259 func TestIssue70(t *testing.T) { 2260 db, err := sql.Open(driverName, "file::memory:") 2261 if _, err = db.Exec(`create table t (foo)`); err != nil { 2262 t.Fatalf("create: %v", err) 2263 } 2264 2265 defer func() { 2266 if err := db.Close(); err != nil { 2267 t.Errorf("conn close: %v", err) 2268 } 2269 }() 2270 2271 r, err := db.Query("select * from t") 2272 if err != nil { 2273 t.Errorf("select a: %v", err) 2274 return 2275 } 2276 2277 if err := r.Close(); err != nil { 2278 t.Errorf("rows close: %v", err) 2279 return 2280 } 2281 2282 if _, err := db.Query("select * from t"); err != nil { 2283 t.Errorf("select b: %v", err) 2284 } 2285 } 2286 2287 // https://gitlab.com/cznic/sqlite/-/issues/66 2288 func TestIssue66(t *testing.T) { 2289 tempDir, err := os.MkdirTemp("", "") 2290 if err != nil { 2291 t.Fatal(err) 2292 } 2293 2294 defer func() { 2295 os.RemoveAll(tempDir) 2296 }() 2297 2298 fn := filepath.Join(tempDir, "testissue66.db") 2299 db, err := sql.Open(driverName, fn) 2300 2301 defer func() { 2302 if err := db.Close(); err != nil { 2303 t.Errorf("conn close: %v", err) 2304 } 2305 }() 2306 2307 if _, err = db.Exec(`CREATE TABLE IF NOT EXISTS verdictcache (sha1 text);`); err != nil { 2308 t.Fatalf("create: %v", err) 2309 } 2310 2311 // ab 2312 // 00 ok 2313 // 01 ok 2314 // 10 ok 2315 // 11 hangs with old implementation of conn.step(). 2316 2317 // a 2318 if _, err = db.Exec("INSERT OR REPLACE INTO verdictcache (sha1) VALUES ($1)", "a"); err != nil { 2319 t.Fatalf("insert: %v", err) 2320 } 2321 2322 // b 2323 if _, err := db.Query("SELECT * FROM verdictcache WHERE sha1=$1", "a"); err != nil { 2324 t.Fatalf("select: %v", err) 2325 } 2326 2327 // c 2328 if _, err = db.Exec("INSERT OR REPLACE INTO verdictcache (sha1) VALUES ($1)", "b"); err != nil { 2329 2330 // https://www.sqlite.org/rescode.html#busy 2331 // ---------------------------------------------------------------------------- 2332 // The SQLITE_BUSY result code indicates that the database file could not be 2333 // written (or in some cases read) because of concurrent activity by some other 2334 // database connection, usually a database connection in a separate process. 2335 // ---------------------------------------------------------------------------- 2336 // 2337 // The SQLITE_BUSY error is _expected_. 2338 // 2339 // According to the above, performing c after b's result was not yet 2340 // consumed/closed is not possible. Mattn's driver seems to resort to 2341 // autoclosing the driver.Rows returned by b in this situation, but I don't 2342 // think that's correct (jnml). 2343 2344 t.Logf("insert 2: %v", err) 2345 if !strings.Contains(err.Error(), "database is locked (5) (SQLITE_BUSY)") { 2346 t.Fatalf("insert 2: %v", err) 2347 } 2348 } 2349 } 2350 2351 // https://gitlab.com/cznic/sqlite/-/issues/65 2352 func TestIssue65(t *testing.T) { 2353 tempDir, err := os.MkdirTemp("", "") 2354 if err != nil { 2355 t.Fatal(err) 2356 } 2357 2358 defer func() { 2359 os.RemoveAll(tempDir) 2360 }() 2361 2362 db, err := sql.Open("sqlite", filepath.Join(tempDir, "testissue65.sqlite")) 2363 if err != nil { 2364 t.Fatalf("Failed to open database: %v", err) 2365 } 2366 2367 testIssue65(t, db, true) 2368 2369 // go1.20rc1, linux/ppc64le VM 2370 // 10000 FAIL 2371 // 20000 PASS, FAIL 2372 // 40000 FAIL 2373 // 80000 PASS, PASS 2374 if db, err = sql.Open("sqlite", filepath.Join(tempDir, "testissue65b.sqlite")+"?_pragma=busy_timeout%3d80000"); err != nil { 2375 t.Fatalf("Failed to open database: %v", err) 2376 } 2377 2378 testIssue65(t, db, false) 2379 } 2380 2381 func testIssue65(t *testing.T, db *sql.DB, canFail bool) { 2382 defer db.Close() 2383 2384 ctx := context.Background() 2385 2386 if _, err := db.Exec("CREATE TABLE foo (department INTEGER, profits INTEGER)"); err != nil { 2387 t.Fatal("Failed to create table:", err) 2388 } 2389 2390 if _, err := db.Exec("INSERT INTO foo VALUES (1, 10), (1, 20), (1, 45), (2, 42), (2, 115)"); err != nil { 2391 t.Fatal("Failed to insert records:", err) 2392 } 2393 2394 readFunc := func(ctx context.Context) error { 2395 tx, err := db.BeginTx(ctx, nil) 2396 if err != nil { 2397 return fmt.Errorf("read error: %v", err) 2398 } 2399 2400 defer tx.Rollback() 2401 2402 var dept, count int64 2403 if err := tx.QueryRowContext(ctx, "SELECT department, COUNT(*) FROM foo GROUP BY department").Scan( 2404 &dept, 2405 &count, 2406 ); err != nil { 2407 return fmt.Errorf("read error: %v", err) 2408 } 2409 2410 return nil 2411 } 2412 2413 writeFunc := func(ctx context.Context) error { 2414 tx, err := db.BeginTx(ctx, nil) 2415 if err != nil { 2416 return fmt.Errorf("write error: %v", err) 2417 } 2418 2419 defer tx.Rollback() 2420 2421 if _, err := tx.ExecContext( 2422 ctx, 2423 "INSERT INTO foo(department, profits) VALUES (@department, @profits)", 2424 sql.Named("department", rand.Int()), 2425 sql.Named("profits", rand.Int()), 2426 ); err != nil { 2427 return fmt.Errorf("write error: %v", err) 2428 } 2429 2430 return tx.Commit() 2431 } 2432 2433 var wg sync.WaitGroup 2434 wg.Add(2) 2435 2436 const cycles = 100 2437 2438 errCh := make(chan error, 2) 2439 2440 go func() { 2441 defer wg.Done() 2442 2443 for i := 0; i < cycles; i++ { 2444 if err := readFunc(ctx); err != nil { 2445 err = fmt.Errorf("readFunc(%v): %v", canFail, err) 2446 t.Log(err) 2447 if !canFail { 2448 errCh <- err 2449 } 2450 return 2451 } 2452 } 2453 }() 2454 2455 go func() { 2456 defer wg.Done() 2457 2458 for i := 0; i < cycles; i++ { 2459 if err := writeFunc(ctx); err != nil { 2460 err = fmt.Errorf("writeFunc(%v): %v", canFail, err) 2461 t.Log(err) 2462 if !canFail { 2463 errCh <- err 2464 } 2465 return 2466 } 2467 } 2468 }() 2469 2470 wg.Wait() 2471 for { 2472 select { 2473 case err := <-errCh: 2474 t.Error(err) 2475 default: 2476 return 2477 } 2478 } 2479 } 2480 2481 // https://gitlab.com/cznic/sqlite/-/issues/73 2482 func TestConstraintPrimaryKeyError(t *testing.T) { 2483 db, err := sql.Open(driverName, "file::memory:") 2484 if err != nil { 2485 t.Fatal(err) 2486 } 2487 defer db.Close() 2488 2489 _, err = db.Exec(`CREATE TABLE IF NOT EXISTS hash (hashval TEXT PRIMARY KEY NOT NULL)`) 2490 if err != nil { 2491 t.Fatal(err) 2492 } 2493 2494 _, err = db.Exec("INSERT INTO hash (hashval) VALUES (?)", "somehashval") 2495 if err != nil { 2496 t.Fatal(err) 2497 } 2498 2499 _, err = db.Exec("INSERT INTO hash (hashval) VALUES (?)", "somehashval") 2500 if err == nil { 2501 t.Fatal("wanted error") 2502 } 2503 2504 if errs, want := err.Error(), "constraint failed: UNIQUE constraint failed: hash.hashval (1555)"; errs != want { 2505 t.Fatalf("got error string %q, want %q", errs, want) 2506 } 2507 } 2508 2509 func TestConstraintUniqueError(t *testing.T) { 2510 db, err := sql.Open(driverName, "file::memory:") 2511 if err != nil { 2512 t.Fatal(err) 2513 } 2514 defer db.Close() 2515 2516 _, err = db.Exec(`CREATE TABLE IF NOT EXISTS hash (hashval TEXT UNIQUE)`) 2517 if err != nil { 2518 t.Fatal(err) 2519 } 2520 2521 _, err = db.Exec("INSERT INTO hash (hashval) VALUES (?)", "somehashval") 2522 if err != nil { 2523 t.Fatal(err) 2524 } 2525 2526 _, err = db.Exec("INSERT INTO hash (hashval) VALUES (?)", "somehashval") 2527 if err == nil { 2528 t.Fatal("wanted error") 2529 } 2530 2531 if errs, want := err.Error(), "constraint failed: UNIQUE constraint failed: hash.hashval (2067)"; errs != want { 2532 t.Fatalf("got error string %q, want %q", errs, want) 2533 } 2534 } 2535 2536 // https://gitlab.com/cznic/sqlite/-/issues/92 2537 func TestBeginMode(t *testing.T) { 2538 tempDir, err := os.MkdirTemp("", "") 2539 if err != nil { 2540 t.Fatal(err) 2541 } 2542 2543 defer func() { 2544 os.RemoveAll(tempDir) 2545 }() 2546 2547 tests := []struct { 2548 mode string 2549 want int32 2550 }{ 2551 {"deferred", sqlite3.SQLITE_TXN_NONE}, 2552 {"immediate", sqlite3.SQLITE_TXN_WRITE}, 2553 // TODO: how to verify "exclusive" is working differently from immediate, 2554 // short of concurrently trying to open the database again? This is only 2555 // different in non-WAL journal modes. 2556 {"exclusive", sqlite3.SQLITE_TXN_WRITE}, 2557 } 2558 2559 for _, tt := range tests { 2560 tt := tt 2561 for _, jm := range []string{"delete", "wal"} { 2562 jm := jm 2563 t.Run(jm+"/"+tt.mode, func(t *testing.T) { 2564 // t.Parallel() 2565 2566 qs := fmt.Sprintf("?_txlock=%s&_pragma=journal_mode(%s)", tt.mode, jm) 2567 db, err := sql.Open("sqlite", filepath.Join(tempDir, fmt.Sprintf("testbeginmode-%s.sqlite", tt.mode))+qs) 2568 if err != nil { 2569 t.Fatalf("Failed to open database: %v", err) 2570 } 2571 defer db.Close() 2572 connection, err := db.Conn(context.Background()) 2573 if err != nil { 2574 t.Fatalf("Failed to open connection: %v", err) 2575 } 2576 2577 tx, err := connection.BeginTx(context.Background(), nil) 2578 if err != nil { 2579 t.Fatalf("Failed to begin transaction: %v", err) 2580 } 2581 defer tx.Rollback() 2582 if err := connection.Raw(func(driverConn interface{}) error { 2583 p, err := libc.CString("main") 2584 if err != nil { 2585 return err 2586 } 2587 c := driverConn.(*conn) 2588 defer c.free(p) 2589 got := sqlite3.Xsqlite3_txn_state(c.tls, c.db, p) 2590 if got != tt.want { 2591 return fmt.Errorf("in mode %s, got txn state %d, want %d", tt.mode, got, tt.want) 2592 } 2593 return nil 2594 }); err != nil { 2595 t.Fatalf("Failed to check txn state: %v", err) 2596 } 2597 }) 2598 } 2599 } 2600 } 2601 2602 // https://gitlab.com/cznic/sqlite/-/issues/94 2603 func TestCancelRace(t *testing.T) { 2604 tempDir, err := os.MkdirTemp("", "") 2605 if err != nil { 2606 t.Fatal(err) 2607 } 2608 2609 defer func() { 2610 os.RemoveAll(tempDir) 2611 }() 2612 2613 db, err := sql.Open("sqlite", filepath.Join(tempDir, "testcancelrace.sqlite")) 2614 if err != nil { 2615 t.Fatalf("Failed to open database: %v", err) 2616 } 2617 defer db.Close() 2618 2619 tests := []struct { 2620 name string 2621 f func(context.Context, *sql.DB) error 2622 }{ 2623 { 2624 "db.ExecContext", 2625 func(ctx context.Context, d *sql.DB) error { 2626 _, err := db.ExecContext(ctx, "select 1") 2627 return err 2628 }, 2629 }, 2630 { 2631 "db.QueryContext", 2632 func(ctx context.Context, d *sql.DB) error { 2633 _, err := db.QueryContext(ctx, "select 1") 2634 return err 2635 }, 2636 }, 2637 { 2638 "tx.ExecContext", 2639 func(ctx context.Context, d *sql.DB) error { 2640 tx, err := db.BeginTx(ctx, &sql.TxOptions{}) 2641 if err != nil { 2642 return err 2643 } 2644 defer tx.Rollback() 2645 if _, err := tx.ExecContext(ctx, "select 1"); err != nil { 2646 return err 2647 } 2648 return tx.Rollback() 2649 }, 2650 }, 2651 { 2652 "tx.QueryContext", 2653 func(ctx context.Context, d *sql.DB) error { 2654 tx, err := db.BeginTx(ctx, &sql.TxOptions{}) 2655 if err != nil { 2656 return err 2657 } 2658 defer tx.Rollback() 2659 if _, err := tx.QueryContext(ctx, "select 1"); err != nil { 2660 return err 2661 } 2662 return tx.Rollback() 2663 }, 2664 }, 2665 } 2666 2667 for _, tt := range tests { 2668 t.Run(tt.name, func(t *testing.T) { 2669 // this is a race condition, so it's not guaranteed to fail on any given run, 2670 // but with a moderate number of iterations it will eventually catch it 2671 iterations := 100 2672 for i := 0; i < iterations; i++ { 2673 // none of these iterations should ever fail, because we never cancel their 2674 // context until after they complete 2675 ctx, cancel := context.WithCancel(context.Background()) 2676 if err := tt.f(ctx, db); err != nil { 2677 t.Fatalf("Failed to run test query on iteration %d: %v", i, err) 2678 } 2679 cancel() 2680 } 2681 }) 2682 } 2683 } 2684 2685 //go:embed embed.db 2686 var fs embed.FS 2687 2688 //go:embed embed2.db 2689 var fs2 embed.FS 2690 2691 func TestVFS(t *testing.T) { 2692 fn, f, err := vfs.New(fs) 2693 if err != nil { 2694 t.Fatal(err) 2695 } 2696 2697 defer func() { 2698 if err := f.Close(); err != nil { 2699 t.Error(err) 2700 } 2701 }() 2702 2703 f2n, f2, err := vfs.New(fs2) 2704 if err != nil { 2705 t.Fatal(err) 2706 } 2707 2708 defer func() { 2709 if err := f2.Close(); err != nil { 2710 t.Error(err) 2711 } 2712 }() 2713 2714 db, err := sql.Open("sqlite", "file:embed.db?vfs="+fn) 2715 if err != nil { 2716 t.Fatal(err) 2717 } 2718 2719 defer db.Close() 2720 2721 db2, err := sql.Open("sqlite", "file:embed2.db?vfs="+f2n) 2722 if err != nil { 2723 t.Fatal(err) 2724 } 2725 2726 defer db2.Close() 2727 2728 rows, err := db.Query("select * from t order by i;") 2729 if err != nil { 2730 t.Fatal(err) 2731 } 2732 2733 var a []int 2734 for rows.Next() { 2735 var i, j, k int 2736 if err := rows.Scan(&i, &j, &k); err != nil { 2737 t.Fatal(err) 2738 } 2739 2740 a = append(a, i, j, k) 2741 } 2742 if err := rows.Err(); err != nil { 2743 t.Fatal(err) 2744 } 2745 2746 t.Log(a) 2747 if g, e := fmt.Sprint(a), "[1 2 3 40 50 60]"; g != e { 2748 t.Fatalf("got %q, expected %q", g, e) 2749 } 2750 2751 if rows, err = db2.Query("select * from u order by s;"); err != nil { 2752 t.Fatal(err) 2753 } 2754 2755 var b []string 2756 for rows.Next() { 2757 var x, y string 2758 if err := rows.Scan(&x, &y); err != nil { 2759 t.Fatal(err) 2760 } 2761 2762 b = append(b, x, y) 2763 } 2764 if err := rows.Err(); err != nil { 2765 t.Fatal(err) 2766 } 2767 2768 t.Log(b) 2769 if g, e := fmt.Sprint(b), "[123 xyz abc def]"; g != e { 2770 t.Fatalf("got %q, expected %q", g, e) 2771 } 2772 } 2773 2774 // y = 2^n, except for n < 0 y = 0. 2775 func exp(n int) int { 2776 if n < 0 { 2777 return 0 2778 } 2779 2780 return 1 << n 2781 } 2782 2783 func BenchmarkConcurrent(b *testing.B) { 2784 benchmarkConcurrent(b, "sqlite", []string{"sql", "drv"}) 2785 } 2786 2787 func benchmarkConcurrent(b *testing.B, drv string, modes []string) { 2788 for _, mode := range modes { 2789 for _, measurement := range []string{"reads", "writes"} { 2790 for _, writers := range []int{0, 1, 10, 100} { 2791 for _, readers := range []int{0, 1, 10, 100} { 2792 if measurement == "reads" && readers == 0 || measurement == "writes" && writers == 0 { 2793 continue 2794 } 2795 2796 tag := fmt.Sprintf("%s %s readers %d writers %d %s", mode, measurement, readers, writers, drv) 2797 b.Run(tag, func(b *testing.B) { c := &concurrentBenchmark{}; c.run(b, readers, writers, drv, measurement, mode) }) 2798 } 2799 } 2800 } 2801 } 2802 } 2803 2804 // The code for concurrentBenchmark is derived from/heavily inspired by 2805 // original code available at 2806 // 2807 // https://github.com/kalafut/go-sqlite-bench 2808 // 2809 // # MIT License 2810 // 2811 // # Copyright (c) 2022 Jim Kalafut 2812 // 2813 // Permission is hereby granted, free of charge, to any person obtaining a copy 2814 // of this software and associated documentation files (the "Software"), to deal 2815 // in the Software without restriction, including without limitation the rights 2816 // to use, copy, modify, merge, publish, distribute, sublicense, and/or sell 2817 // copies of the Software, and to permit persons to whom the Software is 2818 // furnished to do so, subject to the following conditions: 2819 // 2820 // The above copyright notice and this permission notice shall be included in all 2821 // copies or substantial portions of the Software. 2822 // 2823 // THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR 2824 // IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, 2825 // FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE 2826 // AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER 2827 // LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, 2828 // OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE 2829 // SOFTWARE. 2830 type concurrentBenchmark struct { 2831 b *testing.B 2832 drv string 2833 fn string 2834 start chan struct{} 2835 stop chan struct{} 2836 wg sync.WaitGroup 2837 2838 reads int32 2839 records int32 2840 writes int32 2841 } 2842 2843 func (c *concurrentBenchmark) run(b *testing.B, readers, writers int, drv, measurement, mode string) { 2844 c.b = b 2845 c.drv = drv 2846 b.ReportAllocs() 2847 dir := b.TempDir() 2848 fn := filepath.Join(dir, "test.db") 2849 sqlite3.MutexCounters.Disable() 2850 sqlite3.MutexEnterCallers.Disable() 2851 c.makeDB(fn) 2852 b.ResetTimer() 2853 for i := 0; i < b.N; i++ { 2854 b.StopTimer() 2855 c.start = make(chan struct{}) 2856 c.stop = make(chan struct{}) 2857 sqlite3.MutexCounters.Disable() 2858 sqlite3.MutexEnterCallers.Disable() 2859 c.makeReaders(readers, mode) 2860 c.makeWriters(writers, mode) 2861 sqlite3.MutexCounters.Clear() 2862 sqlite3.MutexCounters.Enable() 2863 sqlite3.MutexEnterCallers.Clear() 2864 //sqlite3.MutexEnterCallers.Enable() 2865 time.AfterFunc(time.Second, func() { close(c.stop) }) 2866 b.StartTimer() 2867 close(c.start) 2868 c.wg.Wait() 2869 } 2870 switch measurement { 2871 case "reads": 2872 b.ReportMetric(float64(c.reads), "reads/s") 2873 case "writes": 2874 b.ReportMetric(float64(c.writes), "writes/s") 2875 } 2876 // b.Log(sqlite3.MutexCounters) 2877 // b.Log(sqlite3.MutexEnterCallers) 2878 } 2879 2880 func (c *concurrentBenchmark) randString(n int) string { 2881 b := make([]byte, n) 2882 for i := range b { 2883 b[i] = byte(65 + rand.Intn(26)) 2884 } 2885 return string(b) 2886 } 2887 2888 func (c *concurrentBenchmark) mustExecSQL(db *sql.DB, sql string) { 2889 var err error 2890 for i := 0; i < 100; i++ { 2891 if _, err = db.Exec(sql); err != nil { 2892 if c.retry(err) { 2893 continue 2894 } 2895 2896 c.b.Fatalf("%s: %v", sql, err) 2897 } 2898 2899 return 2900 } 2901 c.b.Fatalf("%s: %v", sql, err) 2902 } 2903 2904 func (c *concurrentBenchmark) mustExecDrv(db driver.Conn, sql string) { 2905 var err error 2906 for i := 0; i < 100; i++ { 2907 if _, err = db.(driver.Execer).Exec(sql, nil); err != nil { 2908 if c.retry(err) { 2909 continue 2910 } 2911 2912 c.b.Fatalf("%s: %v", sql, err) 2913 } 2914 2915 return 2916 } 2917 c.b.Fatalf("%s: %v", sql, err) 2918 } 2919 2920 func (c *concurrentBenchmark) makeDB(fn string) { 2921 const quota = 1e6 2922 c.fn = fn 2923 db := c.makeSQLConn() 2924 2925 defer db.Close() 2926 2927 c.mustExecSQL(db, "CREATE TABLE foo (id INTEGER NOT NULL PRIMARY KEY, name TEXT)") 2928 tx, err := db.Begin() 2929 if err != nil { 2930 c.b.Fatal(err) 2931 } 2932 2933 stmt, err := tx.Prepare("INSERT INTO FOO(name) VALUES($1)") 2934 if err != nil { 2935 c.b.Fatal(err) 2936 } 2937 2938 for i := int32(0); i < quota; i++ { 2939 if _, err = stmt.Exec(c.randString(30)); err != nil { 2940 c.b.Fatal(err) 2941 } 2942 } 2943 2944 if err := tx.Commit(); err != nil { 2945 c.b.Fatal(err) 2946 } 2947 2948 c.records = quota 2949 2950 // Warm the cache. 2951 rows, err := db.Query("SELECT * FROM foo") 2952 if err != nil { 2953 c.b.Fatal(err) 2954 } 2955 2956 for rows.Next() { 2957 var id int 2958 var name string 2959 err = rows.Scan(&id, &name) 2960 if err != nil { 2961 c.b.Fatal(err) 2962 } 2963 } 2964 } 2965 2966 func (c *concurrentBenchmark) makeSQLConn() *sql.DB { 2967 db, err := sql.Open(c.drv, c.fn) 2968 if err != nil { 2969 c.b.Fatal(err) 2970 } 2971 2972 db.SetMaxOpenConns(0) 2973 c.mustExecSQL(db, "PRAGMA busy_timeout=10000") 2974 c.mustExecSQL(db, "PRAGMA synchronous=NORMAL") 2975 c.mustExecSQL(db, "PRAGMA journal_mode=WAL") 2976 return db 2977 } 2978 2979 func (c *concurrentBenchmark) makeDrvConn() driver.Conn { 2980 db, err := sql.Open(c.drv, c.fn) 2981 if err != nil { 2982 c.b.Fatal(err) 2983 } 2984 2985 drv := db.Driver() 2986 if err := db.Close(); err != nil { 2987 c.b.Fatal(err) 2988 } 2989 2990 conn, err := drv.Open(c.fn) 2991 if err != nil { 2992 c.b.Fatal(err) 2993 } 2994 2995 c.mustExecDrv(conn, "PRAGMA busy_timeout=10000") 2996 c.mustExecDrv(conn, "PRAGMA synchronous=NORMAL") 2997 c.mustExecDrv(conn, "PRAGMA journal_mode=WAL") 2998 return conn 2999 } 3000 3001 func (c *concurrentBenchmark) retry(err error) bool { 3002 s := strings.ToLower(err.Error()) 3003 return strings.Contains(s, "lock") || strings.Contains(s, "busy") 3004 } 3005 3006 func (c *concurrentBenchmark) makeReaders(n int, mode string) { 3007 var wait sync.WaitGroup 3008 wait.Add(n) 3009 c.wg.Add(n) 3010 for i := 0; i < n; i++ { 3011 switch mode { 3012 case "sql": 3013 go func() { 3014 db := c.makeSQLConn() 3015 3016 defer func() { 3017 db.Close() 3018 c.wg.Done() 3019 }() 3020 3021 wait.Done() 3022 <-c.start 3023 3024 for i := 1; ; i++ { 3025 select { 3026 case <-c.stop: 3027 return 3028 default: 3029 } 3030 3031 recs := atomic.LoadInt32(&c.records) 3032 id := recs * int32(i) % recs 3033 rows, err := db.Query("SELECT * FROM foo WHERE id=$1", id) 3034 if err != nil { 3035 if c.retry(err) { 3036 continue 3037 } 3038 3039 c.b.Fatal(err) 3040 } 3041 3042 for rows.Next() { 3043 var id int 3044 var name string 3045 err = rows.Scan(&id, &name) 3046 if err != nil { 3047 c.b.Fatal(err) 3048 } 3049 } 3050 if err := rows.Close(); err != nil { 3051 c.b.Fatal(err) 3052 } 3053 3054 atomic.AddInt32(&c.reads, 1) 3055 } 3056 3057 }() 3058 case "drv": 3059 go func() { 3060 conn := c.makeDrvConn() 3061 3062 defer func() { 3063 conn.Close() 3064 c.wg.Done() 3065 }() 3066 3067 q := conn.(driver.Queryer) 3068 wait.Done() 3069 <-c.start 3070 3071 for i := 1; ; i++ { 3072 select { 3073 case <-c.stop: 3074 return 3075 default: 3076 } 3077 3078 recs := atomic.LoadInt32(&c.records) 3079 id := recs * int32(i) % recs 3080 rows, err := q.Query("SELECT * FROM foo WHERE id=$1", []driver.Value{int64(id)}) 3081 if err != nil { 3082 if c.retry(err) { 3083 continue 3084 } 3085 3086 c.b.Fatal(err) 3087 } 3088 3089 var dest [2]driver.Value 3090 for { 3091 if err := rows.Next(dest[:]); err != nil { 3092 if err != io.EOF { 3093 c.b.Fatal(err) 3094 } 3095 break 3096 } 3097 } 3098 3099 if err := rows.Close(); err != nil { 3100 c.b.Fatal(err) 3101 } 3102 3103 atomic.AddInt32(&c.reads, 1) 3104 } 3105 3106 }() 3107 default: 3108 panic(todo("")) 3109 } 3110 } 3111 wait.Wait() 3112 } 3113 3114 func (c *concurrentBenchmark) makeWriters(n int, mode string) { 3115 var wait sync.WaitGroup 3116 wait.Add(n) 3117 c.wg.Add(n) 3118 for i := 0; i < n; i++ { 3119 switch mode { 3120 case "sql": 3121 go func() { 3122 db := c.makeSQLConn() 3123 3124 defer func() { 3125 db.Close() 3126 c.wg.Done() 3127 }() 3128 3129 wait.Done() 3130 <-c.start 3131 3132 for { 3133 select { 3134 case <-c.stop: 3135 return 3136 default: 3137 } 3138 3139 if _, err := db.Exec("INSERT INTO FOO(name) VALUES($1)", c.randString(30)); err != nil { 3140 if c.retry(err) { 3141 continue 3142 } 3143 3144 c.b.Fatal(err) 3145 } 3146 3147 atomic.AddInt32(&c.records, 1) 3148 atomic.AddInt32(&c.writes, 1) 3149 } 3150 3151 }() 3152 case "drv": 3153 go func() { 3154 conn := c.makeDrvConn() 3155 3156 defer func() { 3157 conn.Close() 3158 c.wg.Done() 3159 }() 3160 3161 e := conn.(driver.Execer) 3162 wait.Done() 3163 <-c.start 3164 3165 for { 3166 select { 3167 case <-c.stop: 3168 return 3169 default: 3170 } 3171 3172 if _, err := e.Exec("INSERT INTO FOO(name) VALUES($1)", []driver.Value{c.randString(30)}); err != nil { 3173 if c.retry(err) { 3174 continue 3175 } 3176 3177 c.b.Fatal(err) 3178 } 3179 3180 atomic.AddInt32(&c.records, 1) 3181 atomic.AddInt32(&c.writes, 1) 3182 } 3183 3184 }() 3185 default: 3186 panic(todo("")) 3187 } 3188 } 3189 wait.Wait() 3190 } 3191 3192 // https://gitlab.com/cznic/sqlite/-/issues/142 3193 // https://github.com/capnspacehook/sqlite-repro/tree/83906dafb74817ed4fca5443aeff52affbc0e72d 3194 3195 var ( 3196 issue142Cont1ID = "container_one_ID" 3197 issue142Cont1Name = "container1" 3198 issue142Cont1Addr = netip.MustParseAddr("172.0.1.2") 3199 ) 3200 3201 type issue142DBTX interface { 3202 ExecContext(context.Context, string, ...interface{}) (sql.Result, error) 3203 PrepareContext(context.Context, string) (*sql.Stmt, error) 3204 QueryContext(context.Context, string, ...interface{}) (*sql.Rows, error) 3205 QueryRowContext(context.Context, string, ...interface{}) *sql.Row 3206 } 3207 3208 type issue142Queries struct { 3209 db issue142DBTX 3210 tx *sql.Tx 3211 addContainerStmt *sql.Stmt 3212 addContainerAddrStmt *sql.Stmt 3213 addContainerAliasStmt *sql.Stmt 3214 deleteContainerStmt *sql.Stmt 3215 getContainersStmt *sql.Stmt 3216 } 3217 3218 func issue142Prepare(ctx context.Context, db *sql.DB) (*issue142Queries, error) { 3219 q := issue142Queries{db: db} 3220 var err error 3221 if q.addContainerStmt, err = db.PrepareContext(ctx, issue142AddContainer); err != nil { 3222 return nil, fmt.Errorf("error preparing query AddContainer: %w", err) 3223 } 3224 if q.addContainerAddrStmt, err = db.PrepareContext(ctx, issue142AddContainerAddr); err != nil { 3225 return nil, fmt.Errorf("error preparing query AddContainerAddr: %w", err) 3226 } 3227 if q.addContainerAliasStmt, err = db.PrepareContext(ctx, issue142AddContainerAlias); err != nil { 3228 return nil, fmt.Errorf("error preparing query AddContainerAlias: %w", err) 3229 } 3230 if q.deleteContainerStmt, err = db.PrepareContext(ctx, issue142DeleteContainer); err != nil { 3231 return nil, fmt.Errorf("error preparing query DeleteContainer: %w", err) 3232 } 3233 if q.getContainersStmt, err = db.PrepareContext(ctx, issue142GetContainers); err != nil { 3234 return nil, fmt.Errorf("error preparing query GetContainers: %w", err) 3235 } 3236 return &q, nil 3237 } 3238 3239 func (q *issue142Queries) Close() error { 3240 var err error 3241 if q.addContainerStmt != nil { 3242 if cerr := q.addContainerStmt.Close(); cerr != nil { 3243 err = fmt.Errorf("error closing addContainerStmt: %w", cerr) 3244 } 3245 } 3246 if q.addContainerAddrStmt != nil { 3247 if cerr := q.addContainerAddrStmt.Close(); cerr != nil { 3248 err = fmt.Errorf("error closing addContainerAddrStmt: %w", cerr) 3249 } 3250 } 3251 if q.addContainerAliasStmt != nil { 3252 if cerr := q.addContainerAliasStmt.Close(); cerr != nil { 3253 err = fmt.Errorf("error closing addContainerAliasStmt: %w", cerr) 3254 } 3255 } 3256 if q.deleteContainerStmt != nil { 3257 if cerr := q.deleteContainerStmt.Close(); cerr != nil { 3258 err = fmt.Errorf("error closing deleteContainerStmt: %w", cerr) 3259 } 3260 } 3261 if q.getContainersStmt != nil { 3262 if cerr := q.getContainersStmt.Close(); cerr != nil { 3263 err = fmt.Errorf("error closing getContainersStmt: %w", cerr) 3264 } 3265 } 3266 return err 3267 } 3268 3269 func (q *issue142Queries) exec(ctx context.Context, stmt *sql.Stmt, query string, args ...interface{}) (sql.Result, error) { 3270 switch { 3271 case stmt != nil && q.tx != nil: 3272 return q.tx.StmtContext(ctx, stmt).ExecContext(ctx, args...) 3273 case stmt != nil: 3274 return stmt.ExecContext(ctx, args...) 3275 default: 3276 return nil, errors.New("no prepared statement provided") 3277 } 3278 } 3279 3280 var ( 3281 issue142Retries int 3282 issue142MaxRetries int32 3283 ) 3284 3285 func (q *issue142Queries) query(ctx context.Context, stmt *sql.Stmt, query string, args ...interface{}) (*sql.Rows, error) { 3286 switch { 3287 case stmt != nil && q.tx != nil: 3288 return q.tx.StmtContext(ctx, stmt).QueryContext(ctx, args...) 3289 case stmt != nil: 3290 var r *sql.Rows 3291 var err error 3292 for i := 0; i < 10; i++ { 3293 if r, err = stmt.QueryContext(ctx, args...); err == nil { 3294 return r, nil 3295 } 3296 3297 issue142Retries++ 3298 issue142MaxRetries = mathutil.MaxInt32(issue142MaxRetries, int32(i+1)) 3299 time.Sleep(100 * time.Millisecond) 3300 } 3301 return r, err 3302 default: 3303 return nil, errors.New("no prepared statement provided") 3304 } 3305 } 3306 3307 func (q *issue142Queries) WithTx(tx *sql.Tx) *issue142Queries { 3308 return &issue142Queries{ 3309 db: tx, 3310 tx: tx, 3311 addContainerStmt: q.addContainerStmt, 3312 addContainerAddrStmt: q.addContainerAddrStmt, 3313 addContainerAliasStmt: q.addContainerAliasStmt, 3314 deleteContainerStmt: q.deleteContainerStmt, 3315 getContainersStmt: q.getContainersStmt, 3316 } 3317 } 3318 3319 const issue142AddContainer = "INSERT INTO containers(id, name) VALUES (?, ?)" 3320 3321 func (q *issue142Queries) AddContainer(ctx context.Context, iD string, name string) error { 3322 _, err := q.exec(ctx, q.addContainerStmt, issue142AddContainer, iD, name) 3323 return err 3324 } 3325 3326 const issue142AddContainerAddr = "INSERT INTO addrs(addr, container_id) VALUES (?, ?)" 3327 3328 func (q *issue142Queries) AddContainerAddr(ctx context.Context, addr []byte, containerID string) error { 3329 _, err := q.exec(ctx, q.addContainerAddrStmt, issue142AddContainerAddr, addr, containerID) 3330 return err 3331 } 3332 3333 const issue142AddContainerAlias = "INSERT INTO container_aliases(container_id, container_alias) VALUES (?, ?)" 3334 3335 func (q *issue142Queries) AddContainerAlias(ctx context.Context, containerID string, containerAlias string) error { 3336 _, err := q.exec(ctx, q.addContainerAliasStmt, issue142AddContainerAlias, containerID, containerAlias) 3337 return err 3338 } 3339 3340 const issue142DeleteContainer = "DELETE FROM containers WHERE id = ?" 3341 3342 func (q *issue142Queries) DeleteContainer(ctx context.Context, id string) error { 3343 _, err := q.exec(ctx, q.deleteContainerStmt, issue142DeleteContainer, id) 3344 return err 3345 } 3346 3347 const issue142GetContainers = "SELECT id, name FROM containers" 3348 3349 func (q *issue142Queries) GetContainers(ctx context.Context) error { 3350 rows, err := q.query(ctx, q.getContainersStmt, issue142GetContainers) 3351 if err != nil { 3352 return err 3353 } 3354 return rows.Close() 3355 } 3356 3357 type issue142DB struct { 3358 *issue142Queries 3359 db *sql.DB 3360 } 3361 3362 func NewIssue142DB(ctx context.Context, database *sql.DB) (*issue142DB, error) { 3363 q, err := issue142Prepare(ctx, database) 3364 if err != nil { 3365 return nil, err 3366 } 3367 3368 return &issue142DB{ 3369 issue142Queries: q, 3370 db: database, 3371 }, nil 3372 } 3373 3374 type issue142TX interface { 3375 *issue142Queries 3376 Rollback() bool 3377 Commit() error 3378 } 3379 3380 type issue142Tx struct { 3381 *issue142Queries 3382 ctx context.Context 3383 } 3384 3385 func (d *issue142DB) Begin(ctx context.Context) (*issue142Tx, error) { 3386 transaction, err := d.db.BeginTx(ctx, nil) 3387 if err != nil { 3388 return nil, fmt.Errorf("error beginning database transaction: %w", err) 3389 } 3390 3391 return &issue142Tx{ 3392 issue142Queries: d.WithTx(transaction), 3393 ctx: ctx, 3394 }, nil 3395 } 3396 3397 func (t *issue142Tx) Rollback(tt *testing.T) bool { 3398 if err := t.tx.Rollback(); err != nil { 3399 if errors.Is(err, sql.ErrTxDone) { 3400 return false 3401 } 3402 tt.Logf("error rolling back database transaction: %v", err) 3403 return false 3404 } 3405 3406 return true 3407 } 3408 3409 func (t *issue142Tx) Commit() error { 3410 if err := t.tx.Commit(); err != nil { 3411 if errors.Is(err, sql.ErrTxDone) && t.ctx.Err() != nil { 3412 err = t.ctx.Err() 3413 } 3414 return fmt.Errorf("error committing database transaction: %w", err) 3415 } 3416 3417 return nil 3418 } 3419 3420 func TestIssue142(t *testing.T) { 3421 t0 := time.Now() 3422 n := 0 3423 for i := 0; i < 100; i++ { 3424 t.Log(i) 3425 testIssue142(t) 3426 n++ 3427 if i&15 == 0 && time.Since(t0) > time.Hour { 3428 break 3429 } 3430 } 3431 t.Logf("retries %d/%d, max retries %d", issue142Retries, n, issue142MaxRetries) 3432 } 3433 3434 func testIssue142(t *testing.T) { 3435 const ( 3436 pragmas = ` 3437 PRAGMA foreign_keys = true; 3438 PRAGMA busy_timeout = 1000; 3439 PRAGMA journal_mode = WAL; 3440 ` 3441 schema = ` 3442 CREATE TABLE containers ( 3443 id TEXT PRIMARY KEY, 3444 name TEXT UNIQUE NOT NULL 3445 ); 3446 3447 CREATE TABLE addrs ( 3448 addr BLOB PRIMARY KEY, 3449 container_id TEXT NOT NULL, 3450 3451 FOREIGN KEY(container_id) REFERENCES containers(id) 3452 ); 3453 3454 CREATE TABLE container_aliases ( 3455 container_id TEXT NOT NULL, 3456 container_alias TEXT NOT NULL, 3457 3458 PRIMARY KEY(container_id, container_alias), 3459 FOREIGN KEY(container_id) REFERENCES containers(id) 3460 ); 3461 ` 3462 ) 3463 tempDir := t.TempDir() 3464 dbFile := filepath.Join(tempDir, "sqlite.db") 3465 dbFile += "?_txlock=immediate" 3466 dbFile += "&_pragma=busy_timeout%3d5000" 3467 sqlDB, err := sql.Open("sqlite", dbFile) 3468 if err != nil { 3469 t.Fatal(err) 3470 } 3471 3472 defer sqlDB.Close() 3473 3474 _, err = sqlDB.Exec(schema) 3475 if err != nil { 3476 t.Fatal(err) 3477 } 3478 3479 _, err = sqlDB.Exec(pragmas) 3480 if err != nil { 3481 t.Fatal(err) 3482 } 3483 3484 db, err := NewIssue142DB(context.Background(), sqlDB) 3485 if err != nil { 3486 t.Fatal(err) 3487 } 3488 3489 defer db.Close() 3490 3491 tx, err := db.Begin(context.Background()) 3492 if err != nil { 3493 t.Fatal(err) 3494 } 3495 3496 err = tx.AddContainer(context.Background(), "dummyid", "dummyname") 3497 if err != nil { 3498 t.Fatal(err) 3499 } 3500 3501 err = tx.DeleteContainer(context.Background(), "dummyid") 3502 if err != nil { 3503 t.Fatal(err) 3504 } 3505 3506 if err := tx.Commit(); err != nil { 3507 t.Fatal(err) 3508 } 3509 3510 tx.Rollback(t) 3511 3512 ctx, cancel := context.WithCancel(context.Background()) 3513 tx, err = db.Begin(ctx) 3514 if err != nil { 3515 t.Fatal(err) 3516 } 3517 3518 err = tx.AddContainer(ctx, issue142Cont1ID, issue142Cont1Name) 3519 if err != nil { 3520 t.Fatal(err) 3521 } 3522 3523 err = tx.AddContainerAddr(ctx, issue142Cont1Addr.AsSlice(), issue142Cont1ID) 3524 if err != nil { 3525 t.Fatal(err) 3526 } 3527 3528 err = tx.AddContainerAlias(ctx, issue142Cont1ID, "/"+issue142Cont1Name) 3529 if err != nil { 3530 t.Fatal(err) 3531 } 3532 3533 // cancel the context so the committing the transaction will fail and rollback 3534 cancel() 3535 err = tx.Commit() 3536 if !errors.Is(err, context.Canceled) { 3537 if err != nil { 3538 t.Fatal(err) 3539 } 3540 } 3541 tx.Rollback(t) 3542 3543 err = db.GetContainers(context.Background()) 3544 if err != nil { 3545 t.Fatal(err) 3546 } 3547 }