github.com/markdessain/sqlitego@v1.21.1/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/markdessain/sqlitego" 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 "github.com/markdessain/sqlitego/lib" 40 "github.com/markdessain/sqlitego/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.NullByte 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.String()}); g != e { 368 t.Fatal(g, e) 369 } 370 371 if g, e := a[1], (rec{34, 2.78, false, "bar", t2.String()}); 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.QueryContext(ctx, "select 'seafood' regexp 'a(b'") 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.QueryContext(ctx, "SELECT 1 REGEXP 'a(b'") 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.QueryContext(ctx, "SELECT 'seafood' REGEXP 1") 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, "github.com/markdessain/sqlitego/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.Query(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 defer rows2.Close() 1414 1415 columnTypes, err := rows2.ColumnTypes() 1416 if err != nil { 1417 t.Fatal(err) 1418 } 1419 1420 var b strings.Builder 1421 for index, value := range columnTypes { 1422 precision, scale, precisionOk := value.DecimalSize() 1423 length, lengthOk := value.Length() 1424 nullable, nullableOk := value.Nullable() 1425 fmt.Fprintf(&b, "Col %d: DatabaseTypeName %q, DecimalSize %v %v %v, Length %v %v, Name %q, Nullable %v %v, ScanType %q\n", 1426 index, 1427 value.DatabaseTypeName(), 1428 precision, scale, precisionOk, 1429 length, lengthOk, 1430 value.Name(), 1431 nullable, nullableOk, 1432 value.ScanType(), 1433 ) 1434 } 1435 if err := rows2.Err(); err != nil { 1436 t.Fatal(err) 1437 } 1438 1439 if g, e := b.String(), `Col 0: DatabaseTypeName "INTEGER", DecimalSize 0 0 false, Length 0 false, Name "uid", Nullable true true, ScanType "int64" 1440 Col 1: DatabaseTypeName "VARCHAR(64)", DecimalSize 0 0 false, Length 9223372036854775807 true, Name "username", Nullable true true, ScanType "string" 1441 Col 2: DatabaseTypeName "VARCHAR(64)", DecimalSize 0 0 false, Length 9223372036854775807 true, Name "departname", Nullable true true, ScanType "string" 1442 Col 3: DatabaseTypeName "DATE", DecimalSize 0 0 false, Length 9223372036854775807 true, Name "created", Nullable true true, ScanType "string" 1443 `; g != e { 1444 t.Fatalf("---- got\n%s\n----expected\n%s", g, e) 1445 } 1446 t.Log(b.String()) 1447 } 1448 1449 // https://gitlab.com/cznic/sqlite/-/issues/32 1450 func TestColumnTypesNoRows(t *testing.T) { 1451 tempDir, err := os.MkdirTemp("", "") 1452 if err != nil { 1453 t.Fatal(err) 1454 } 1455 1456 defer func() { 1457 os.RemoveAll(tempDir) 1458 }() 1459 1460 db, err := sql.Open("sqlite", filepath.Join(tempDir, "test.db")) 1461 if err != nil { 1462 t.Fatalf("test.db open fail: %v", err) 1463 } 1464 1465 defer db.Close() 1466 1467 _, 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);") 1468 if err != nil { 1469 t.Fatal(err) 1470 } 1471 1472 rows2, err := db.Query("SELECT * FROM userinfo") 1473 if err != nil { 1474 t.Fatal(err) 1475 } 1476 defer rows2.Close() 1477 1478 columnTypes, err := rows2.ColumnTypes() 1479 if err != nil { 1480 t.Fatal(err) 1481 } 1482 1483 var b strings.Builder 1484 for index, value := range columnTypes { 1485 precision, scale, precisionOk := value.DecimalSize() 1486 length, lengthOk := value.Length() 1487 nullable, nullableOk := value.Nullable() 1488 fmt.Fprintf(&b, "Col %d: DatabaseTypeName %q, DecimalSize %v %v %v, Length %v %v, Name %q, Nullable %v %v, ScanType %q\n", 1489 index, 1490 value.DatabaseTypeName(), 1491 precision, scale, precisionOk, 1492 length, lengthOk, 1493 value.Name(), 1494 nullable, nullableOk, 1495 value.ScanType(), 1496 ) 1497 } 1498 if err := rows2.Err(); err != nil { 1499 t.Fatal(err) 1500 } 1501 1502 if g, e := b.String(), `Col 0: DatabaseTypeName "INTEGER", DecimalSize 0 0 false, Length 0 false, Name "uid", Nullable true true, ScanType %!q(<nil>) 1503 Col 1: DatabaseTypeName "VARCHAR(64)", DecimalSize 0 0 false, Length 0 false, Name "username", Nullable true true, ScanType %!q(<nil>) 1504 Col 2: DatabaseTypeName "VARCHAR(64)", DecimalSize 0 0 false, Length 0 false, Name "departname", Nullable true true, ScanType %!q(<nil>) 1505 Col 3: DatabaseTypeName "DATE", DecimalSize 0 0 false, Length 0 false, Name "created", Nullable true true, ScanType %!q(<nil>) 1506 `; g != e { 1507 t.Fatalf("---- got\n%s\n----expected\n%s", g, e) 1508 } 1509 t.Log(b.String()) 1510 } 1511 1512 // https://gitlab.com/cznic/sqlite/-/issues/35 1513 func TestTime(t *testing.T) { 1514 types := []string{ 1515 "DATE", 1516 "DATETIME", 1517 "Date", 1518 "DateTime", 1519 "TIMESTAMP", 1520 "TimeStamp", 1521 "date", 1522 "datetime", 1523 "timestamp", 1524 } 1525 db, err := sql.Open(driverName, "file::memory:") 1526 if err != nil { 1527 t.Fatal(err) 1528 } 1529 1530 defer func() { 1531 db.Close() 1532 }() 1533 1534 for _, typ := range types { 1535 if _, err := db.Exec(fmt.Sprintf(` 1536 drop table if exists mg; 1537 create table mg (applied_at %s); 1538 `, typ)); err != nil { 1539 t.Fatal(err) 1540 } 1541 1542 now := time.Now() 1543 _, err = db.Exec(`INSERT INTO mg (applied_at) VALUES (?)`, &now) 1544 if err != nil { 1545 t.Fatal(err) 1546 } 1547 1548 var appliedAt time.Time 1549 err = db.QueryRow("SELECT applied_at FROM mg").Scan(&appliedAt) 1550 if err != nil { 1551 t.Fatal(err) 1552 } 1553 1554 if g, e := appliedAt, now; !g.Equal(e) { 1555 t.Fatal(g, e) 1556 } 1557 } 1558 } 1559 1560 // https://gitlab.com/cznic/sqlite/-/issues/46 1561 func TestTimeScan(t *testing.T) { 1562 ref := time.Date(2021, 1, 2, 16, 39, 17, 123456789, time.UTC) 1563 1564 cases := []struct { 1565 s string 1566 w time.Time 1567 }{ 1568 {s: "2021-01-02 12:39:17 -0400 ADT m=+00000", w: ref.Truncate(time.Second)}, 1569 {s: "2021-01-02 16:39:17 +0000 UTC m=+0.000000001", w: ref.Truncate(time.Second)}, 1570 {s: "2021-01-02 12:39:17.123456 -0400 ADT m=+00000", w: ref.Truncate(time.Microsecond)}, 1571 {s: "2021-01-02 16:39:17.123456 +0000 UTC m=+0.000000001", w: ref.Truncate(time.Microsecond)}, 1572 {s: "2021-01-02 16:39:17Z", w: ref.Truncate(time.Second)}, 1573 {s: "2021-01-02 16:39:17+00:00", w: ref.Truncate(time.Second)}, 1574 {s: "2021-01-02T16:39:17.123456+00:00", w: ref.Truncate(time.Microsecond)}, 1575 {s: "2021-01-02 16:39:17.123456+00:00", w: ref.Truncate(time.Microsecond)}, 1576 {s: "2021-01-02 16:39:17.123456Z", w: ref.Truncate(time.Microsecond)}, 1577 {s: "2021-01-02 12:39:17-04:00", w: ref.Truncate(time.Second)}, 1578 {s: "2021-01-02 16:39:17", w: ref.Truncate(time.Second)}, 1579 {s: "2021-01-02T16:39:17", w: ref.Truncate(time.Second)}, 1580 {s: "2021-01-02 16:39", w: ref.Truncate(time.Minute)}, 1581 {s: "2021-01-02T16:39", w: ref.Truncate(time.Minute)}, 1582 {s: "2021-01-02", w: ref.Truncate(24 * time.Hour)}, 1583 } 1584 1585 db, err := sql.Open(driverName, "file::memory:") 1586 if err != nil { 1587 t.Fatal(err) 1588 } 1589 defer db.Close() 1590 1591 for _, colType := range []string{"DATE", "DATETIME", "TIMESTAMP"} { 1592 for _, tc := range cases { 1593 if _, err := db.Exec("drop table if exists x; create table x (y " + colType + ")"); err != nil { 1594 t.Fatal(err) 1595 } 1596 if _, err := db.Exec("insert into x (y) values (?)", tc.s); err != nil { 1597 t.Fatal(err) 1598 } 1599 1600 var got time.Time 1601 if err := db.QueryRow("select y from x").Scan(&got); err != nil { 1602 t.Fatal(err) 1603 } 1604 if !got.Equal(tc.w) { 1605 t.Errorf("scan(%q as %q) = %s, want %s", tc.s, colType, got, tc.w) 1606 } 1607 } 1608 } 1609 } 1610 1611 // https://gitlab.com/cznic/sqlite/-/issues/49 1612 func TestTimeLocaltime(t *testing.T) { 1613 db, err := sql.Open(driverName, "file::memory:") 1614 if err != nil { 1615 t.Fatal(err) 1616 } 1617 defer db.Close() 1618 1619 if _, err := db.Exec("select datetime('now', 'localtime')"); err != nil { 1620 t.Fatal(err) 1621 } 1622 } 1623 1624 func TestTimeFormat(t *testing.T) { 1625 ref := time.Date(2021, 1, 2, 16, 39, 17, 123456789, time.UTC) 1626 1627 cases := []struct { 1628 f string 1629 w string 1630 }{ 1631 {f: "", w: "2021-01-02 16:39:17.123456789 +0000 UTC"}, 1632 {f: "sqlite", w: "2021-01-02 16:39:17.123456789+00:00"}, 1633 } 1634 for _, c := range cases { 1635 t.Run("", func(t *testing.T) { 1636 dsn := "file::memory:" 1637 if c.f != "" { 1638 q := make(url.Values) 1639 q.Set("_time_format", c.f) 1640 dsn += "?" + q.Encode() 1641 } 1642 db, err := sql.Open(driverName, dsn) 1643 if err != nil { 1644 t.Fatal(err) 1645 } 1646 defer db.Close() 1647 1648 if _, err := db.Exec("drop table if exists x; create table x (y text)"); err != nil { 1649 t.Fatal(err) 1650 } 1651 1652 if _, err := db.Exec(`insert into x values (?)`, ref); err != nil { 1653 t.Fatal(err) 1654 } 1655 1656 var got string 1657 if err := db.QueryRow(`select y from x`).Scan(&got); err != nil { 1658 t.Fatal(err) 1659 } 1660 1661 if got != c.w { 1662 t.Fatal(got, c.w) 1663 } 1664 }) 1665 } 1666 } 1667 1668 func TestTimeFormatBad(t *testing.T) { 1669 db, err := sql.Open(driverName, "file::memory:?_time_format=bogus") 1670 if err != nil { 1671 t.Fatal(err) 1672 } 1673 defer db.Close() 1674 1675 // Error doesn't appear until a connection is opened. 1676 _, err = db.Exec("select 1") 1677 if err == nil { 1678 t.Fatal("wanted error") 1679 } 1680 1681 want := `unknown _time_format "bogus"` 1682 if got := err.Error(); got != want { 1683 t.Fatalf("got error %q, want %q", got, want) 1684 } 1685 } 1686 1687 // https://sqlite.org/lang_expr.html#varparam 1688 // https://gitlab.com/cznic/sqlite/-/issues/42 1689 func TestBinding(t *testing.T) { 1690 t.Run("DB", func(t *testing.T) { 1691 testBinding(t, func(db *sql.DB, query string, args ...interface{}) (*sql.Row, func()) { 1692 return db.QueryRow(query, args...), func() {} 1693 }) 1694 }) 1695 1696 t.Run("Prepare", func(t *testing.T) { 1697 testBinding(t, func(db *sql.DB, query string, args ...interface{}) (*sql.Row, func()) { 1698 stmt, err := db.Prepare(query) 1699 if err != nil { 1700 t.Fatal(err) 1701 } 1702 return stmt.QueryRow(args...), func() { stmt.Close() } 1703 }) 1704 }) 1705 } 1706 1707 func testBinding(t *testing.T, query func(db *sql.DB, query string, args ...interface{}) (*sql.Row, func())) { 1708 db, err := sql.Open(driverName, "file::memory:") 1709 if err != nil { 1710 t.Fatal(err) 1711 } 1712 defer db.Close() 1713 1714 for _, tc := range []struct { 1715 q string 1716 in []interface{} 1717 w []int 1718 }{ 1719 { 1720 q: "?, ?, ?", 1721 in: []interface{}{1, 2, 3}, 1722 w: []int{1, 2, 3}, 1723 }, 1724 { 1725 q: "?1, ?2, ?3", 1726 in: []interface{}{1, 2, 3}, 1727 w: []int{1, 2, 3}, 1728 }, 1729 { 1730 q: "?1, ?, ?3", 1731 in: []interface{}{1, 2, 3}, 1732 w: []int{1, 2, 3}, 1733 }, 1734 { 1735 q: "?3, ?2, ?1", 1736 in: []interface{}{1, 2, 3}, 1737 w: []int{3, 2, 1}, 1738 }, 1739 { 1740 q: "?1, ?1, ?2", 1741 in: []interface{}{1, 2}, 1742 w: []int{1, 1, 2}, 1743 }, 1744 { 1745 q: ":one, :two, :three", 1746 in: []interface{}{sql.Named("one", 1), sql.Named("two", 2), sql.Named("three", 3)}, 1747 w: []int{1, 2, 3}, 1748 }, 1749 { 1750 q: ":one, :one, :two", 1751 in: []interface{}{sql.Named("one", 1), sql.Named("two", 2)}, 1752 w: []int{1, 1, 2}, 1753 }, 1754 { 1755 q: "@one, @two, @three", 1756 in: []interface{}{sql.Named("one", 1), sql.Named("two", 2), sql.Named("three", 3)}, 1757 w: []int{1, 2, 3}, 1758 }, 1759 { 1760 q: "@one, @one, @two", 1761 in: []interface{}{sql.Named("one", 1), sql.Named("two", 2)}, 1762 w: []int{1, 1, 2}, 1763 }, 1764 { 1765 q: "$one, $two, $three", 1766 in: []interface{}{sql.Named("one", 1), sql.Named("two", 2), sql.Named("three", 3)}, 1767 w: []int{1, 2, 3}, 1768 }, 1769 { 1770 // A common usage that should technically require sql.Named but 1771 // does not. 1772 q: "$1, $2, $3", 1773 in: []interface{}{1, 2, 3}, 1774 w: []int{1, 2, 3}, 1775 }, 1776 { 1777 q: "$one, $one, $two", 1778 in: []interface{}{sql.Named("one", 1), sql.Named("two", 2)}, 1779 w: []int{1, 1, 2}, 1780 }, 1781 { 1782 q: ":one, @one, $one", 1783 in: []interface{}{sql.Named("one", 1)}, 1784 w: []int{1, 1, 1}, 1785 }, 1786 } { 1787 got := make([]int, len(tc.w)) 1788 ptrs := make([]interface{}, len(got)) 1789 for i := range got { 1790 ptrs[i] = &got[i] 1791 } 1792 1793 row, cleanup := query(db, "select "+tc.q, tc.in...) 1794 defer cleanup() 1795 1796 if err := row.Scan(ptrs...); err != nil { 1797 t.Errorf("query(%q, %+v) = %s", tc.q, tc.in, err) 1798 continue 1799 } 1800 1801 if !reflect.DeepEqual(got, tc.w) { 1802 t.Errorf("query(%q, %+v) = %#+v, want %#+v", tc.q, tc.in, got, tc.w) 1803 } 1804 } 1805 } 1806 1807 func TestBindingError(t *testing.T) { 1808 t.Run("DB", func(t *testing.T) { 1809 testBindingError(t, func(db *sql.DB, query string, args ...interface{}) (*sql.Row, func()) { 1810 return db.QueryRow(query, args...), func() {} 1811 }) 1812 }) 1813 1814 t.Run("Prepare", func(t *testing.T) { 1815 testBindingError(t, func(db *sql.DB, query string, args ...interface{}) (*sql.Row, func()) { 1816 stmt, err := db.Prepare(query) 1817 if err != nil { 1818 t.Fatal(err) 1819 } 1820 return stmt.QueryRow(args...), func() { stmt.Close() } 1821 }) 1822 }) 1823 } 1824 1825 func testBindingError(t *testing.T, query func(db *sql.DB, query string, args ...interface{}) (*sql.Row, func())) { 1826 db, err := sql.Open(driverName, "file::memory:") 1827 if err != nil { 1828 t.Fatal(err) 1829 } 1830 defer db.Close() 1831 1832 for _, tc := range []struct { 1833 q string 1834 in []interface{} 1835 }{ 1836 { 1837 q: "?", 1838 in: []interface{}{}, 1839 }, 1840 { 1841 q: "?500, ?", 1842 in: []interface{}{1, 2}, 1843 }, 1844 { 1845 q: ":one", 1846 in: []interface{}{1}, 1847 }, 1848 { 1849 q: "@one", 1850 in: []interface{}{1}, 1851 }, 1852 { 1853 q: "$one", 1854 in: []interface{}{1}, 1855 }, 1856 } { 1857 got := make([]int, 2) 1858 ptrs := make([]interface{}, len(got)) 1859 for i := range got { 1860 ptrs[i] = &got[i] 1861 } 1862 1863 row, cleanup := query(db, "select "+tc.q, tc.in...) 1864 defer cleanup() 1865 1866 err := row.Scan(ptrs...) 1867 if err == nil || (!strings.Contains(err.Error(), "missing argument with index") && !strings.Contains(err.Error(), "missing named argument")) { 1868 t.Errorf("query(%q, %+v) unexpected error %+v", tc.q, tc.in, err) 1869 } 1870 } 1871 } 1872 1873 // https://gitlab.com/cznic/sqlite/-/issues/51 1874 func TestIssue51(t *testing.T) { 1875 if testing.Short() { 1876 t.Skip("skipping test in short mode") 1877 } 1878 1879 tempDir, err := os.MkdirTemp("", "") 1880 if err != nil { 1881 t.Fatal(err) 1882 } 1883 1884 defer func() { 1885 os.RemoveAll(tempDir) 1886 }() 1887 1888 fn := filepath.Join(tempDir, "test_issue51.db") 1889 db, err := sql.Open(driverName, fn) 1890 if err != nil { 1891 t.Fatal(err) 1892 } 1893 1894 defer func() { 1895 db.Close() 1896 }() 1897 1898 if _, err := db.Exec(` 1899 CREATE TABLE fileHash ( 1900 "hash" TEXT NOT NULL PRIMARY KEY, 1901 "filename" TEXT, 1902 "lastChecked" INTEGER 1903 );`); err != nil { 1904 t.Fatal(err) 1905 } 1906 1907 t0 := time.Now() 1908 n := 0 1909 for time.Since(t0) < time.Minute { 1910 hash := randomString() 1911 if _, err = lookupHash(fn, hash); err != nil { 1912 t.Fatal(err) 1913 } 1914 1915 if err = saveHash(fn, hash, hash+".temp"); err != nil { 1916 t.Error(err) 1917 break 1918 } 1919 n++ 1920 } 1921 t.Logf("cycles: %v", n) 1922 row := db.QueryRow("select count(*) from fileHash") 1923 if err := row.Scan(&n); err != nil { 1924 t.Fatal(err) 1925 } 1926 1927 t.Logf("DB records: %v", n) 1928 } 1929 1930 func saveHash(dbFile string, hash string, fileName string) (err error) { 1931 db, err := sql.Open("sqlite", dbFile) 1932 if err != nil { 1933 return fmt.Errorf("could not open database: %v", err) 1934 } 1935 1936 defer func() { 1937 if err2 := db.Close(); err2 != nil && err == nil { 1938 err = fmt.Errorf("could not close the database: %s", err2) 1939 } 1940 }() 1941 1942 query := `INSERT OR REPLACE INTO fileHash(hash, fileName, lastChecked) 1943 VALUES(?, ?, ?);` 1944 rows, err := executeSQL(db, query, hash, fileName, time.Now().Unix()) 1945 if err != nil { 1946 return fmt.Errorf("error saving hash to database: %v", err) 1947 } 1948 defer rows.Close() 1949 1950 return nil 1951 } 1952 1953 func executeSQL(db *sql.DB, query string, values ...interface{}) (*sql.Rows, error) { 1954 statement, err := db.Prepare(query) 1955 if err != nil { 1956 return nil, fmt.Errorf("could not prepare statement: %v", err) 1957 } 1958 defer statement.Close() 1959 1960 return statement.Query(values...) 1961 } 1962 1963 func lookupHash(dbFile string, hash string) (ok bool, err error) { 1964 db, err := sql.Open("sqlite", dbFile) 1965 if err != nil { 1966 return false, fmt.Errorf("could not open database: %n", err) 1967 } 1968 1969 defer func() { 1970 if err2 := db.Close(); err2 != nil && err == nil { 1971 err = fmt.Errorf("could not close the database: %v", err2) 1972 } 1973 }() 1974 1975 query := `SELECT hash, fileName, lastChecked 1976 FROM fileHash 1977 WHERE hash=?;` 1978 rows, err := executeSQL(db, query, hash) 1979 if err != nil { 1980 return false, fmt.Errorf("error checking database for hash: %n", err) 1981 } 1982 1983 defer func() { 1984 if err2 := rows.Close(); err2 != nil && err == nil { 1985 err = fmt.Errorf("could not close DB rows: %v", err2) 1986 } 1987 }() 1988 1989 var ( 1990 dbHash string 1991 fileName string 1992 lastChecked int64 1993 ) 1994 for rows.Next() { 1995 err = rows.Scan(&dbHash, &fileName, &lastChecked) 1996 if err != nil { 1997 return false, fmt.Errorf("could not read DB row: %v", err) 1998 } 1999 } 2000 return false, rows.Err() 2001 } 2002 2003 func randomString() string { 2004 b := make([]byte, 32) 2005 for i := range b { 2006 b[i] = charset[seededRand.Intn(len(charset))] 2007 } 2008 return string(b) 2009 } 2010 2011 var seededRand *rand.Rand = rand.New(rand.NewSource(time.Now().UnixNano())) 2012 2013 const charset = "abcdefghijklmnopqrstuvwxyz" + 2014 "ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789" 2015 2016 // https://gitlab.com/cznic/sqlite/-/issues/53 2017 func TestIssue53(t *testing.T) { 2018 tempDir, err := os.MkdirTemp("", "") 2019 if err != nil { 2020 t.Fatal(err) 2021 } 2022 2023 defer func() { 2024 os.RemoveAll(tempDir) 2025 }() 2026 2027 wd, err := os.Getwd() 2028 if err != nil { 2029 t.Fatal(err) 2030 } 2031 2032 defer os.Chdir(wd) 2033 2034 if err := os.Chdir(tempDir); err != nil { 2035 t.Fatal(err) 2036 } 2037 2038 const fn = "testissue53.sqlite" 2039 2040 db, err := sql.Open(driverName, fn) 2041 if err != nil { 2042 t.Fatal(err) 2043 } 2044 2045 defer func() { 2046 db.Close() 2047 }() 2048 2049 if _, err := db.Exec(` 2050 CREATE TABLE IF NOT EXISTS loginst ( 2051 instid INTEGER PRIMARY KEY, 2052 name VARCHAR UNIQUE 2053 ); 2054 `); err != nil { 2055 t.Fatal(err) 2056 } 2057 2058 tx, err := db.Begin() 2059 if err != nil { 2060 t.Fatal(err) 2061 } 2062 2063 for i := 0; i < 5000; i++ { 2064 x := fmt.Sprintf("foo%d", i) 2065 var id int 2066 if err := tx.QueryRow("INSERT OR IGNORE INTO loginst (name) VALUES (?); SELECT instid FROM loginst WHERE name = ?", x, x).Scan(&id); err != nil { 2067 t.Fatal(err) 2068 } 2069 } 2070 2071 } 2072 2073 // https://gitlab.com/cznic/sqlite/-/issues/37 2074 func TestPersistPragma(t *testing.T) { 2075 tempDir, err := os.MkdirTemp("", "") 2076 if err != nil { 2077 t.Fatal(err) 2078 } 2079 2080 defer func() { 2081 os.RemoveAll(tempDir) 2082 }() 2083 2084 wd, err := os.Getwd() 2085 if err != nil { 2086 t.Fatal(err) 2087 } 2088 2089 defer os.Chdir(wd) 2090 2091 if err := os.Chdir(tempDir); err != nil { 2092 t.Fatal(err) 2093 } 2094 2095 pragmas := []pragmaCfg{ 2096 {"foreign_keys", "on", int64(1)}, 2097 {"analysis_limit", "1000", int64(1000)}, 2098 {"application_id", "214", int64(214)}, 2099 {"encoding", "'UTF-16le'", "UTF-16le"}} 2100 2101 if err := testPragmas("testpersistpragma.sqlite", "testpersistpragma.sqlite", pragmas); err != nil { 2102 t.Fatal(err) 2103 } 2104 if err := testPragmas("file::memory:", "", pragmas); err != nil { 2105 t.Fatal(err) 2106 } 2107 if err := testPragmas(":memory:", "", pragmas); err != nil { 2108 t.Fatal(err) 2109 } 2110 } 2111 2112 type pragmaCfg struct { 2113 name string 2114 value string 2115 expected interface{} 2116 } 2117 2118 func testPragmas(name, diskFile string, pragmas []pragmaCfg) error { 2119 if diskFile != "" { 2120 os.Remove(diskFile) 2121 } 2122 2123 q := url.Values{} 2124 for _, pragma := range pragmas { 2125 q.Add("_pragma", pragma.name+"="+pragma.value) 2126 } 2127 2128 dsn := name + "?" + q.Encode() 2129 db, err := sql.Open(driverName, dsn) 2130 if err != nil { 2131 return err 2132 } 2133 2134 db.SetMaxOpenConns(1) 2135 2136 if err := checkPragmas(db, pragmas); err != nil { 2137 return err 2138 } 2139 2140 c, err := db.Conn(context.Background()) 2141 if err != nil { 2142 return err 2143 } 2144 2145 // Kill the connection to spawn a new one. Pragma configs should persist 2146 c.Raw(func(interface{}) error { return driver.ErrBadConn }) 2147 2148 if err := checkPragmas(db, pragmas); err != nil { 2149 return err 2150 } 2151 2152 if diskFile == "" { 2153 // Make sure in memory databases aren't being written to disk 2154 return testInMemory(db) 2155 } 2156 2157 return nil 2158 } 2159 2160 func checkPragmas(db *sql.DB, pragmas []pragmaCfg) error { 2161 for _, pragma := range pragmas { 2162 row := db.QueryRow(`PRAGMA ` + pragma.name) 2163 2164 var result interface{} 2165 if err := row.Scan(&result); err != nil { 2166 return err 2167 } 2168 if result != pragma.expected { 2169 return fmt.Errorf("expected PRAGMA %s to return %v but got %v", pragma.name, pragma.expected, result) 2170 } 2171 } 2172 return nil 2173 } 2174 2175 func TestInMemory(t *testing.T) { 2176 tempDir, err := os.MkdirTemp("", "") 2177 if err != nil { 2178 t.Fatal(err) 2179 } 2180 2181 defer func() { 2182 os.RemoveAll(tempDir) 2183 }() 2184 2185 wd, err := os.Getwd() 2186 if err != nil { 2187 t.Fatal(err) 2188 } 2189 2190 defer os.Chdir(wd) 2191 2192 if err := os.Chdir(tempDir); err != nil { 2193 t.Fatal(err) 2194 } 2195 2196 if err := testMemoryPath(":memory:"); err != nil { 2197 t.Fatal(err) 2198 } 2199 if err := testMemoryPath("file::memory:"); err != nil { 2200 t.Fatal(err) 2201 } 2202 2203 // This parameter should be ignored 2204 q := url.Values{} 2205 q.Add("mode", "readonly") 2206 if err := testMemoryPath(":memory:?" + q.Encode()); err != nil { 2207 t.Fatal(err) 2208 } 2209 } 2210 2211 func testMemoryPath(mPath string) error { 2212 db, err := sql.Open(driverName, mPath) 2213 if err != nil { 2214 return err 2215 } 2216 defer db.Close() 2217 2218 return testInMemory(db) 2219 } 2220 2221 func testInMemory(db *sql.DB) error { 2222 _, err := db.Exec(` 2223 create table in_memory_test(i int, f double); 2224 insert into in_memory_test values(12, 3.14); 2225 `) 2226 if err != nil { 2227 return err 2228 } 2229 2230 dirEntries, err := os.ReadDir("./") 2231 if err != nil { 2232 return err 2233 } 2234 2235 for _, dirEntry := range dirEntries { 2236 if strings.Contains(dirEntry.Name(), "memory") { 2237 return fmt.Errorf("file was created for in memory database") 2238 } 2239 } 2240 2241 return nil 2242 } 2243 2244 func emptyDir(s string) error { 2245 m, err := filepath.Glob(filepath.FromSlash(s + "/*")) 2246 if err != nil { 2247 return err 2248 } 2249 2250 for _, v := range m { 2251 fi, err := os.Stat(v) 2252 if err != nil { 2253 return err 2254 } 2255 2256 switch { 2257 case fi.IsDir(): 2258 if err = os.RemoveAll(v); err != nil { 2259 return err 2260 } 2261 default: 2262 if err = os.Remove(v); err != nil { 2263 return err 2264 } 2265 } 2266 } 2267 return nil 2268 } 2269 2270 // https://gitlab.com/cznic/sqlite/-/issues/70 2271 func TestIssue70(t *testing.T) { 2272 db, err := sql.Open(driverName, "file::memory:") 2273 if _, err = db.Exec(`create table t (foo)`); err != nil { 2274 t.Fatalf("create: %v", err) 2275 } 2276 2277 defer func() { 2278 if err := db.Close(); err != nil { 2279 t.Errorf("conn close: %v", err) 2280 } 2281 }() 2282 2283 r, err := db.Query("select * from t") 2284 if err != nil { 2285 t.Errorf("select a: %v", err) 2286 return 2287 } 2288 2289 if err := r.Close(); err != nil { 2290 t.Errorf("rows close: %v", err) 2291 return 2292 } 2293 2294 if _, err := db.Query("select * from t"); err != nil { 2295 t.Errorf("select b: %v", err) 2296 } 2297 } 2298 2299 // https://gitlab.com/cznic/sqlite/-/issues/66 2300 func TestIssue66(t *testing.T) { 2301 tempDir, err := os.MkdirTemp("", "") 2302 if err != nil { 2303 t.Fatal(err) 2304 } 2305 2306 defer func() { 2307 os.RemoveAll(tempDir) 2308 }() 2309 2310 fn := filepath.Join(tempDir, "testissue66.db") 2311 db, err := sql.Open(driverName, fn) 2312 2313 defer func() { 2314 if err := db.Close(); err != nil { 2315 t.Errorf("conn close: %v", err) 2316 } 2317 }() 2318 2319 if _, err = db.Exec(`CREATE TABLE IF NOT EXISTS verdictcache (sha1 text);`); err != nil { 2320 t.Fatalf("create: %v", err) 2321 } 2322 2323 // ab 2324 // 00 ok 2325 // 01 ok 2326 // 10 ok 2327 // 11 hangs with old implementation of conn.step(). 2328 2329 // a 2330 if _, err = db.Exec("INSERT OR REPLACE INTO verdictcache (sha1) VALUES ($1)", "a"); err != nil { 2331 t.Fatalf("insert: %v", err) 2332 } 2333 2334 // b 2335 if _, err := db.Query("SELECT * FROM verdictcache WHERE sha1=$1", "a"); err != nil { 2336 t.Fatalf("select: %v", err) 2337 } 2338 2339 // c 2340 if _, err = db.Exec("INSERT OR REPLACE INTO verdictcache (sha1) VALUES ($1)", "b"); err != nil { 2341 2342 // https://www.sqlite.org/rescode.html#busy 2343 // ---------------------------------------------------------------------------- 2344 // The SQLITE_BUSY result code indicates that the database file could not be 2345 // written (or in some cases read) because of concurrent activity by some other 2346 // database connection, usually a database connection in a separate process. 2347 // ---------------------------------------------------------------------------- 2348 // 2349 // The SQLITE_BUSY error is _expected_. 2350 // 2351 // According to the above, performing c after b's result was not yet 2352 // consumed/closed is not possible. Mattn's driver seems to resort to 2353 // autoclosing the driver.Rows returned by b in this situation, but I don't 2354 // think that's correct (jnml). 2355 2356 t.Logf("insert 2: %v", err) 2357 if !strings.Contains(err.Error(), "database is locked (5) (SQLITE_BUSY)") { 2358 t.Fatalf("insert 2: %v", err) 2359 } 2360 } 2361 } 2362 2363 // https://gitlab.com/cznic/sqlite/-/issues/65 2364 func TestIssue65(t *testing.T) { 2365 tempDir, err := os.MkdirTemp("", "") 2366 if err != nil { 2367 t.Fatal(err) 2368 } 2369 2370 defer func() { 2371 os.RemoveAll(tempDir) 2372 }() 2373 2374 db, err := sql.Open("sqlite", filepath.Join(tempDir, "testissue65.sqlite")) 2375 if err != nil { 2376 t.Fatalf("Failed to open database: %v", err) 2377 } 2378 2379 testIssue65(t, db, true) 2380 2381 // go1.20rc1, linux/ppc64le VM 2382 // 10000 FAIL 2383 // 20000 PASS, FAIL 2384 // 40000 FAIL 2385 // 80000 PASS, PASS 2386 if db, err = sql.Open("sqlite", filepath.Join(tempDir, "testissue65b.sqlite")+"?_pragma=busy_timeout%3d80000"); err != nil { 2387 t.Fatalf("Failed to open database: %v", err) 2388 } 2389 2390 testIssue65(t, db, false) 2391 } 2392 2393 func testIssue65(t *testing.T, db *sql.DB, canFail bool) { 2394 defer db.Close() 2395 2396 ctx := context.Background() 2397 2398 if _, err := db.Exec("CREATE TABLE foo (department INTEGER, profits INTEGER)"); err != nil { 2399 t.Fatal("Failed to create table:", err) 2400 } 2401 2402 if _, err := db.Exec("INSERT INTO foo VALUES (1, 10), (1, 20), (1, 45), (2, 42), (2, 115)"); err != nil { 2403 t.Fatal("Failed to insert records:", err) 2404 } 2405 2406 readFunc := func(ctx context.Context) error { 2407 tx, err := db.BeginTx(ctx, nil) 2408 if err != nil { 2409 return fmt.Errorf("read error: %v", err) 2410 } 2411 2412 defer tx.Rollback() 2413 2414 var dept, count int64 2415 if err := tx.QueryRowContext(ctx, "SELECT department, COUNT(*) FROM foo GROUP BY department").Scan( 2416 &dept, 2417 &count, 2418 ); err != nil { 2419 return fmt.Errorf("read error: %v", err) 2420 } 2421 2422 return nil 2423 } 2424 2425 writeFunc := func(ctx context.Context) error { 2426 tx, err := db.BeginTx(ctx, nil) 2427 if err != nil { 2428 return fmt.Errorf("write error: %v", err) 2429 } 2430 2431 defer tx.Rollback() 2432 2433 if _, err := tx.ExecContext( 2434 ctx, 2435 "INSERT INTO foo(department, profits) VALUES (@department, @profits)", 2436 sql.Named("department", rand.Int()), 2437 sql.Named("profits", rand.Int()), 2438 ); err != nil { 2439 return fmt.Errorf("write error: %v", err) 2440 } 2441 2442 return tx.Commit() 2443 } 2444 2445 var wg sync.WaitGroup 2446 wg.Add(2) 2447 2448 const cycles = 100 2449 2450 errCh := make(chan error, 2) 2451 2452 go func() { 2453 defer wg.Done() 2454 2455 for i := 0; i < cycles; i++ { 2456 if err := readFunc(ctx); err != nil { 2457 err = fmt.Errorf("readFunc(%v): %v", canFail, err) 2458 t.Log(err) 2459 if !canFail { 2460 errCh <- err 2461 } 2462 return 2463 } 2464 } 2465 }() 2466 2467 go func() { 2468 defer wg.Done() 2469 2470 for i := 0; i < cycles; i++ { 2471 if err := writeFunc(ctx); err != nil { 2472 err = fmt.Errorf("writeFunc(%v): %v", canFail, err) 2473 t.Log(err) 2474 if !canFail { 2475 errCh <- err 2476 } 2477 return 2478 } 2479 } 2480 }() 2481 2482 wg.Wait() 2483 for { 2484 select { 2485 case err := <-errCh: 2486 t.Error(err) 2487 default: 2488 return 2489 } 2490 } 2491 } 2492 2493 // https://gitlab.com/cznic/sqlite/-/issues/73 2494 func TestConstraintPrimaryKeyError(t *testing.T) { 2495 db, err := sql.Open(driverName, "file::memory:") 2496 if err != nil { 2497 t.Fatal(err) 2498 } 2499 defer db.Close() 2500 2501 _, err = db.Exec(`CREATE TABLE IF NOT EXISTS hash (hashval TEXT PRIMARY KEY NOT NULL)`) 2502 if err != nil { 2503 t.Fatal(err) 2504 } 2505 2506 _, err = db.Exec("INSERT INTO hash (hashval) VALUES (?)", "somehashval") 2507 if err != nil { 2508 t.Fatal(err) 2509 } 2510 2511 _, err = db.Exec("INSERT INTO hash (hashval) VALUES (?)", "somehashval") 2512 if err == nil { 2513 t.Fatal("wanted error") 2514 } 2515 2516 if errs, want := err.Error(), "constraint failed: UNIQUE constraint failed: hash.hashval (1555)"; errs != want { 2517 t.Fatalf("got error string %q, want %q", errs, want) 2518 } 2519 } 2520 2521 func TestConstraintUniqueError(t *testing.T) { 2522 db, err := sql.Open(driverName, "file::memory:") 2523 if err != nil { 2524 t.Fatal(err) 2525 } 2526 defer db.Close() 2527 2528 _, err = db.Exec(`CREATE TABLE IF NOT EXISTS hash (hashval TEXT UNIQUE)`) 2529 if err != nil { 2530 t.Fatal(err) 2531 } 2532 2533 _, err = db.Exec("INSERT INTO hash (hashval) VALUES (?)", "somehashval") 2534 if err != nil { 2535 t.Fatal(err) 2536 } 2537 2538 _, err = db.Exec("INSERT INTO hash (hashval) VALUES (?)", "somehashval") 2539 if err == nil { 2540 t.Fatal("wanted error") 2541 } 2542 2543 if errs, want := err.Error(), "constraint failed: UNIQUE constraint failed: hash.hashval (2067)"; errs != want { 2544 t.Fatalf("got error string %q, want %q", errs, want) 2545 } 2546 } 2547 2548 // https://gitlab.com/cznic/sqlite/-/issues/92 2549 func TestBeginMode(t *testing.T) { 2550 tempDir, err := os.MkdirTemp("", "") 2551 if err != nil { 2552 t.Fatal(err) 2553 } 2554 2555 defer func() { 2556 os.RemoveAll(tempDir) 2557 }() 2558 2559 tests := []struct { 2560 mode string 2561 want int32 2562 }{ 2563 {"deferred", sqlite3.SQLITE_TXN_NONE}, 2564 {"immediate", sqlite3.SQLITE_TXN_WRITE}, 2565 // TODO: how to verify "exclusive" is working differently from immediate, 2566 // short of concurrently trying to open the database again? This is only 2567 // different in non-WAL journal modes. 2568 {"exclusive", sqlite3.SQLITE_TXN_WRITE}, 2569 } 2570 2571 for _, tt := range tests { 2572 tt := tt 2573 for _, jm := range []string{"delete", "wal"} { 2574 jm := jm 2575 t.Run(jm+"/"+tt.mode, func(t *testing.T) { 2576 // t.Parallel() 2577 2578 qs := fmt.Sprintf("?_txlock=%s&_pragma=journal_mode(%s)", tt.mode, jm) 2579 db, err := sql.Open("sqlite", filepath.Join(tempDir, fmt.Sprintf("testbeginmode-%s.sqlite", tt.mode))+qs) 2580 if err != nil { 2581 t.Fatalf("Failed to open database: %v", err) 2582 } 2583 defer db.Close() 2584 connection, err := db.Conn(context.Background()) 2585 if err != nil { 2586 t.Fatalf("Failed to open connection: %v", err) 2587 } 2588 2589 tx, err := connection.BeginTx(context.Background(), nil) 2590 if err != nil { 2591 t.Fatalf("Failed to begin transaction: %v", err) 2592 } 2593 defer tx.Rollback() 2594 if err := connection.Raw(func(driverConn interface{}) error { 2595 p, err := libc.CString("main") 2596 if err != nil { 2597 return err 2598 } 2599 c := driverConn.(*conn) 2600 defer c.free(p) 2601 got := sqlite3.Xsqlite3_txn_state(c.tls, c.db, p) 2602 if got != tt.want { 2603 return fmt.Errorf("in mode %s, got txn state %d, want %d", tt.mode, got, tt.want) 2604 } 2605 return nil 2606 }); err != nil { 2607 t.Fatalf("Failed to check txn state: %v", err) 2608 } 2609 }) 2610 } 2611 } 2612 } 2613 2614 // https://gitlab.com/cznic/sqlite/-/issues/94 2615 func TestCancelRace(t *testing.T) { 2616 tempDir, err := os.MkdirTemp("", "") 2617 if err != nil { 2618 t.Fatal(err) 2619 } 2620 2621 defer func() { 2622 os.RemoveAll(tempDir) 2623 }() 2624 2625 db, err := sql.Open("sqlite", filepath.Join(tempDir, "testcancelrace.sqlite")) 2626 if err != nil { 2627 t.Fatalf("Failed to open database: %v", err) 2628 } 2629 defer db.Close() 2630 2631 tests := []struct { 2632 name string 2633 f func(context.Context, *sql.DB) error 2634 }{ 2635 { 2636 "db.ExecContext", 2637 func(ctx context.Context, d *sql.DB) error { 2638 _, err := db.ExecContext(ctx, "select 1") 2639 return err 2640 }, 2641 }, 2642 { 2643 "db.QueryContext", 2644 func(ctx context.Context, d *sql.DB) error { 2645 _, err := db.QueryContext(ctx, "select 1") 2646 return err 2647 }, 2648 }, 2649 { 2650 "tx.ExecContext", 2651 func(ctx context.Context, d *sql.DB) error { 2652 tx, err := db.BeginTx(ctx, &sql.TxOptions{}) 2653 if err != nil { 2654 return err 2655 } 2656 defer tx.Rollback() 2657 if _, err := tx.ExecContext(ctx, "select 1"); err != nil { 2658 return err 2659 } 2660 return tx.Rollback() 2661 }, 2662 }, 2663 { 2664 "tx.QueryContext", 2665 func(ctx context.Context, d *sql.DB) error { 2666 tx, err := db.BeginTx(ctx, &sql.TxOptions{}) 2667 if err != nil { 2668 return err 2669 } 2670 defer tx.Rollback() 2671 if _, err := tx.QueryContext(ctx, "select 1"); err != nil { 2672 return err 2673 } 2674 return tx.Rollback() 2675 }, 2676 }, 2677 } 2678 2679 for _, tt := range tests { 2680 t.Run(tt.name, func(t *testing.T) { 2681 // this is a race condition, so it's not guaranteed to fail on any given run, 2682 // but with a moderate number of iterations it will eventually catch it 2683 iterations := 100 2684 for i := 0; i < iterations; i++ { 2685 // none of these iterations should ever fail, because we never cancel their 2686 // context until after they complete 2687 ctx, cancel := context.WithCancel(context.Background()) 2688 if err := tt.f(ctx, db); err != nil { 2689 t.Fatalf("Failed to run test query on iteration %d: %v", i, err) 2690 } 2691 cancel() 2692 } 2693 }) 2694 } 2695 } 2696 2697 //go:embed embed.db 2698 var fs embed.FS 2699 2700 //go:embed embed2.db 2701 var fs2 embed.FS 2702 2703 func TestVFS(t *testing.T) { 2704 fn, f, err := vfs.New(fs) 2705 if err != nil { 2706 t.Fatal(err) 2707 } 2708 2709 defer func() { 2710 if err := f.Close(); err != nil { 2711 t.Error(err) 2712 } 2713 }() 2714 2715 f2n, f2, err := vfs.New(fs2) 2716 if err != nil { 2717 t.Fatal(err) 2718 } 2719 2720 defer func() { 2721 if err := f2.Close(); err != nil { 2722 t.Error(err) 2723 } 2724 }() 2725 2726 db, err := sql.Open("sqlite", "file:embed.db?vfs="+fn) 2727 if err != nil { 2728 t.Fatal(err) 2729 } 2730 2731 defer db.Close() 2732 2733 db2, err := sql.Open("sqlite", "file:embed2.db?vfs="+f2n) 2734 if err != nil { 2735 t.Fatal(err) 2736 } 2737 2738 defer db2.Close() 2739 2740 rows, err := db.Query("select * from t order by i;") 2741 if err != nil { 2742 t.Fatal(err) 2743 } 2744 2745 var a []int 2746 for rows.Next() { 2747 var i, j, k int 2748 if err := rows.Scan(&i, &j, &k); err != nil { 2749 t.Fatal(err) 2750 } 2751 2752 a = append(a, i, j, k) 2753 } 2754 if err := rows.Err(); err != nil { 2755 t.Fatal(err) 2756 } 2757 2758 t.Log(a) 2759 if g, e := fmt.Sprint(a), "[1 2 3 40 50 60]"; g != e { 2760 t.Fatalf("got %q, expected %q", g, e) 2761 } 2762 2763 if rows, err = db2.Query("select * from u order by s;"); err != nil { 2764 t.Fatal(err) 2765 } 2766 2767 var b []string 2768 for rows.Next() { 2769 var x, y string 2770 if err := rows.Scan(&x, &y); err != nil { 2771 t.Fatal(err) 2772 } 2773 2774 b = append(b, x, y) 2775 } 2776 if err := rows.Err(); err != nil { 2777 t.Fatal(err) 2778 } 2779 2780 t.Log(b) 2781 if g, e := fmt.Sprint(b), "[123 xyz abc def]"; g != e { 2782 t.Fatalf("got %q, expected %q", g, e) 2783 } 2784 } 2785 2786 // y = 2^n, except for n < 0 y = 0. 2787 func exp(n int) int { 2788 if n < 0 { 2789 return 0 2790 } 2791 2792 return 1 << n 2793 } 2794 2795 func BenchmarkConcurrent(b *testing.B) { 2796 benchmarkConcurrent(b, "sqlite", []string{"sql", "drv"}) 2797 } 2798 2799 func benchmarkConcurrent(b *testing.B, drv string, modes []string) { 2800 for _, mode := range modes { 2801 for _, measurement := range []string{"reads", "writes"} { 2802 for _, writers := range []int{0, 1, 10, 100, 100} { 2803 for _, readers := range []int{0, 1, 10, 100, 100} { 2804 if measurement == "reads" && readers == 0 || measurement == "writes" && writers == 0 { 2805 continue 2806 } 2807 2808 tag := fmt.Sprintf("%s %s readers %d writers %d %s", mode, measurement, readers, writers, drv) 2809 b.Run(tag, func(b *testing.B) { c := &concurrentBenchmark{}; c.run(b, readers, writers, drv, measurement, mode) }) 2810 } 2811 } 2812 } 2813 } 2814 } 2815 2816 // The code for concurrentBenchmark is derived from/heavily inspired by 2817 // original code available at 2818 // 2819 // https://github.com/kalafut/go-sqlite-bench 2820 // 2821 // MIT License 2822 // 2823 // Copyright (c) 2022 Jim Kalafut 2824 // 2825 // Permission is hereby granted, free of charge, to any person obtaining a copy 2826 // of this software and associated documentation files (the "Software"), to deal 2827 // in the Software without restriction, including without limitation the rights 2828 // to use, copy, modify, merge, publish, distribute, sublicense, and/or sell 2829 // copies of the Software, and to permit persons to whom the Software is 2830 // furnished to do so, subject to the following conditions: 2831 // 2832 // The above copyright notice and this permission notice shall be included in all 2833 // copies or substantial portions of the Software. 2834 // 2835 // THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR 2836 // IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, 2837 // FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE 2838 // AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER 2839 // LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, 2840 // OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE 2841 // SOFTWARE. 2842 type concurrentBenchmark struct { 2843 b *testing.B 2844 drv string 2845 fn string 2846 start chan struct{} 2847 stop chan struct{} 2848 wg sync.WaitGroup 2849 2850 reads int32 2851 records int32 2852 writes int32 2853 } 2854 2855 func (c *concurrentBenchmark) run(b *testing.B, readers, writers int, drv, measurement, mode string) { 2856 c.b = b 2857 c.drv = drv 2858 b.ReportAllocs() 2859 dir := b.TempDir() 2860 fn := filepath.Join(dir, "test.db") 2861 sqlite3.MutexCounters.Disable() 2862 sqlite3.MutexEnterCallers.Disable() 2863 c.makeDB(fn) 2864 b.ResetTimer() 2865 for i := 0; i < b.N; i++ { 2866 b.StopTimer() 2867 c.start = make(chan struct{}) 2868 c.stop = make(chan struct{}) 2869 sqlite3.MutexCounters.Disable() 2870 sqlite3.MutexEnterCallers.Disable() 2871 c.makeReaders(readers, mode) 2872 c.makeWriters(writers, mode) 2873 sqlite3.MutexCounters.Clear() 2874 sqlite3.MutexCounters.Enable() 2875 sqlite3.MutexEnterCallers.Clear() 2876 //sqlite3.MutexEnterCallers.Enable() 2877 time.AfterFunc(time.Second, func() { close(c.stop) }) 2878 b.StartTimer() 2879 close(c.start) 2880 c.wg.Wait() 2881 } 2882 switch measurement { 2883 case "reads": 2884 b.ReportMetric(float64(c.reads), "reads/s") 2885 case "writes": 2886 b.ReportMetric(float64(c.writes), "writes/s") 2887 } 2888 // b.Log(sqlite3.MutexCounters) 2889 // b.Log(sqlite3.MutexEnterCallers) 2890 } 2891 2892 func (c *concurrentBenchmark) randString(n int) string { 2893 b := make([]byte, n) 2894 for i := range b { 2895 b[i] = byte(65 + rand.Intn(26)) 2896 } 2897 return string(b) 2898 } 2899 2900 func (c *concurrentBenchmark) mustExecSQL(db *sql.DB, sql string) { 2901 var err error 2902 for i := 0; i < 100; i++ { 2903 if _, err = db.Exec(sql); err != nil { 2904 if c.retry(err) { 2905 continue 2906 } 2907 2908 c.b.Fatalf("%s: %v", sql, err) 2909 } 2910 2911 return 2912 } 2913 c.b.Fatalf("%s: %v", sql, err) 2914 } 2915 2916 func (c *concurrentBenchmark) mustExecDrv(db driver.Conn, sql string) { 2917 var err error 2918 for i := 0; i < 100; i++ { 2919 if _, err = db.(driver.Execer).Exec(sql, nil); err != nil { 2920 if c.retry(err) { 2921 continue 2922 } 2923 2924 c.b.Fatalf("%s: %v", sql, err) 2925 } 2926 2927 return 2928 } 2929 c.b.Fatalf("%s: %v", sql, err) 2930 } 2931 2932 func (c *concurrentBenchmark) makeDB(fn string) { 2933 const quota = 1e6 2934 c.fn = fn 2935 db := c.makeSQLConn() 2936 2937 defer db.Close() 2938 2939 c.mustExecSQL(db, "CREATE TABLE foo (id INTEGER NOT NULL PRIMARY KEY, name TEXT)") 2940 tx, err := db.Begin() 2941 if err != nil { 2942 c.b.Fatal(err) 2943 } 2944 2945 stmt, err := tx.Prepare("INSERT INTO FOO(name) VALUES($1)") 2946 if err != nil { 2947 c.b.Fatal(err) 2948 } 2949 2950 for i := int32(0); i < quota; i++ { 2951 if _, err = stmt.Exec(c.randString(30)); err != nil { 2952 c.b.Fatal(err) 2953 } 2954 } 2955 2956 if err := tx.Commit(); err != nil { 2957 c.b.Fatal(err) 2958 } 2959 2960 c.records = quota 2961 2962 // Warm the cache. 2963 rows, err := db.Query("SELECT * FROM foo") 2964 if err != nil { 2965 c.b.Fatal(err) 2966 } 2967 2968 for rows.Next() { 2969 var id int 2970 var name string 2971 err = rows.Scan(&id, &name) 2972 if err != nil { 2973 c.b.Fatal(err) 2974 } 2975 } 2976 } 2977 2978 func (c *concurrentBenchmark) makeSQLConn() *sql.DB { 2979 db, err := sql.Open(c.drv, c.fn) 2980 if err != nil { 2981 c.b.Fatal(err) 2982 } 2983 2984 db.SetMaxOpenConns(0) 2985 c.mustExecSQL(db, "PRAGMA busy_timeout=10000") 2986 c.mustExecSQL(db, "PRAGMA synchronous=NORMAL") 2987 c.mustExecSQL(db, "PRAGMA journal_mode=WAL") 2988 return db 2989 } 2990 2991 func (c *concurrentBenchmark) makeDrvConn() driver.Conn { 2992 db, err := sql.Open(c.drv, c.fn) 2993 if err != nil { 2994 c.b.Fatal(err) 2995 } 2996 2997 drv := db.Driver() 2998 if err := db.Close(); err != nil { 2999 c.b.Fatal(err) 3000 } 3001 3002 conn, err := drv.Open(c.fn) 3003 if err != nil { 3004 c.b.Fatal(err) 3005 } 3006 3007 c.mustExecDrv(conn, "PRAGMA busy_timeout=10000") 3008 c.mustExecDrv(conn, "PRAGMA synchronous=NORMAL") 3009 c.mustExecDrv(conn, "PRAGMA journal_mode=WAL") 3010 return conn 3011 } 3012 3013 func (c *concurrentBenchmark) retry(err error) bool { 3014 s := strings.ToLower(err.Error()) 3015 return strings.Contains(s, "lock") || strings.Contains(s, "busy") 3016 } 3017 3018 func (c *concurrentBenchmark) makeReaders(n int, mode string) { 3019 var wait sync.WaitGroup 3020 wait.Add(n) 3021 c.wg.Add(n) 3022 for i := 0; i < n; i++ { 3023 switch mode { 3024 case "sql": 3025 go func() { 3026 db := c.makeSQLConn() 3027 3028 defer func() { 3029 db.Close() 3030 c.wg.Done() 3031 }() 3032 3033 wait.Done() 3034 <-c.start 3035 3036 for i := 1; ; i++ { 3037 select { 3038 case <-c.stop: 3039 return 3040 default: 3041 } 3042 3043 recs := atomic.LoadInt32(&c.records) 3044 id := recs * int32(i) % recs 3045 rows, err := db.Query("SELECT * FROM foo WHERE id=$1", id) 3046 if err != nil { 3047 if c.retry(err) { 3048 continue 3049 } 3050 3051 c.b.Fatal(err) 3052 } 3053 3054 for rows.Next() { 3055 var id int 3056 var name string 3057 err = rows.Scan(&id, &name) 3058 if err != nil { 3059 c.b.Fatal(err) 3060 } 3061 } 3062 if err := rows.Close(); err != nil { 3063 c.b.Fatal(err) 3064 } 3065 3066 atomic.AddInt32(&c.reads, 1) 3067 } 3068 3069 }() 3070 case "drv": 3071 go func() { 3072 conn := c.makeDrvConn() 3073 3074 defer func() { 3075 conn.Close() 3076 c.wg.Done() 3077 }() 3078 3079 q := conn.(driver.Queryer) 3080 wait.Done() 3081 <-c.start 3082 3083 for i := 1; ; i++ { 3084 select { 3085 case <-c.stop: 3086 return 3087 default: 3088 } 3089 3090 recs := atomic.LoadInt32(&c.records) 3091 id := recs * int32(i) % recs 3092 rows, err := q.Query("SELECT * FROM foo WHERE id=$1", []driver.Value{int64(id)}) 3093 if err != nil { 3094 if c.retry(err) { 3095 continue 3096 } 3097 3098 c.b.Fatal(err) 3099 } 3100 3101 var dest [2]driver.Value 3102 for { 3103 if err := rows.Next(dest[:]); err != nil { 3104 if err != io.EOF { 3105 c.b.Fatal(err) 3106 } 3107 break 3108 } 3109 } 3110 3111 if err := rows.Close(); err != nil { 3112 c.b.Fatal(err) 3113 } 3114 3115 atomic.AddInt32(&c.reads, 1) 3116 } 3117 3118 }() 3119 default: 3120 panic(todo("")) 3121 } 3122 } 3123 wait.Wait() 3124 } 3125 3126 func (c *concurrentBenchmark) makeWriters(n int, mode string) { 3127 var wait sync.WaitGroup 3128 wait.Add(n) 3129 c.wg.Add(n) 3130 for i := 0; i < n; i++ { 3131 switch mode { 3132 case "sql": 3133 go func() { 3134 db := c.makeSQLConn() 3135 3136 defer func() { 3137 db.Close() 3138 c.wg.Done() 3139 }() 3140 3141 wait.Done() 3142 <-c.start 3143 3144 for { 3145 select { 3146 case <-c.stop: 3147 return 3148 default: 3149 } 3150 3151 if _, err := db.Exec("INSERT INTO FOO(name) VALUES($1)", c.randString(30)); err != nil { 3152 if c.retry(err) { 3153 continue 3154 } 3155 3156 c.b.Fatal(err) 3157 } 3158 3159 atomic.AddInt32(&c.records, 1) 3160 atomic.AddInt32(&c.writes, 1) 3161 } 3162 3163 }() 3164 case "drv": 3165 go func() { 3166 conn := c.makeDrvConn() 3167 3168 defer func() { 3169 conn.Close() 3170 c.wg.Done() 3171 }() 3172 3173 e := conn.(driver.Execer) 3174 wait.Done() 3175 <-c.start 3176 3177 for { 3178 select { 3179 case <-c.stop: 3180 return 3181 default: 3182 } 3183 3184 if _, err := e.Exec("INSERT INTO FOO(name) VALUES($1)", []driver.Value{c.randString(30)}); err != nil { 3185 if c.retry(err) { 3186 continue 3187 } 3188 3189 c.b.Fatal(err) 3190 } 3191 3192 atomic.AddInt32(&c.records, 1) 3193 atomic.AddInt32(&c.writes, 1) 3194 } 3195 3196 }() 3197 default: 3198 panic(todo("")) 3199 } 3200 } 3201 wait.Wait() 3202 }