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