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