github.com/jdgcs/sqlite3@v1.12.1-0.20210908114423-bc5f96e4dd51/all_test.go (about) 1 // Copyright 2017 The Sqlite Authors. All rights reserved. 2 // Use of this source code is governed by a BSD-style 3 // license that can be found in the LICENSE file. 4 5 package sqlite // import "modernc.org/sqlite" 6 7 import ( 8 "bytes" 9 "context" 10 "database/sql" 11 "database/sql/driver" 12 "flag" 13 "fmt" 14 "io/ioutil" 15 "math/rand" 16 "net/url" 17 "os" 18 "os/exec" 19 "path" 20 "path/filepath" 21 "reflect" 22 "runtime" 23 "runtime/debug" 24 "strconv" 25 "strings" 26 "sync" 27 "testing" 28 "time" 29 30 "modernc.org/libc" 31 "modernc.org/mathutil" 32 ) 33 34 func caller(s string, va ...interface{}) { 35 if s == "" { 36 s = strings.Repeat("%v ", len(va)) 37 } 38 _, fn, fl, _ := runtime.Caller(2) 39 fmt.Fprintf(os.Stderr, "# caller: %s:%d: ", path.Base(fn), fl) 40 fmt.Fprintf(os.Stderr, s, va...) 41 fmt.Fprintln(os.Stderr) 42 _, fn, fl, _ = runtime.Caller(1) 43 fmt.Fprintf(os.Stderr, "# \tcallee: %s:%d: ", path.Base(fn), fl) 44 fmt.Fprintln(os.Stderr) 45 os.Stderr.Sync() 46 } 47 48 func dbg(s string, va ...interface{}) { 49 if s == "" { 50 s = strings.Repeat("%v ", len(va)) 51 } 52 _, fn, fl, _ := runtime.Caller(1) 53 fmt.Fprintf(os.Stderr, "# dbg %s:%d: ", path.Base(fn), fl) 54 fmt.Fprintf(os.Stderr, s, va...) 55 fmt.Fprintln(os.Stderr) 56 os.Stderr.Sync() 57 } 58 59 func stack() string { return string(debug.Stack()) } 60 61 func use(...interface{}) {} 62 63 func init() { 64 use(caller, dbg, stack, todo, trc) //TODOOK 65 } 66 67 func origin(skip int) string { 68 pc, fn, fl, _ := runtime.Caller(skip) 69 f := runtime.FuncForPC(pc) 70 var fns string 71 if f != nil { 72 fns = f.Name() 73 if x := strings.LastIndex(fns, "."); x > 0 { 74 fns = fns[x+1:] 75 } 76 } 77 return fmt.Sprintf("%s:%d:%s", fn, fl, fns) 78 } 79 80 func todo(s string, args ...interface{}) string { //TODO- 81 switch { 82 case s == "": 83 s = fmt.Sprintf(strings.Repeat("%v ", len(args)), args...) 84 default: 85 s = fmt.Sprintf(s, args...) 86 } 87 r := fmt.Sprintf("%s: TODOTODO %s", origin(2), s) //TODOOK 88 fmt.Fprintf(os.Stdout, "%s\n", r) 89 os.Stdout.Sync() 90 return r 91 } 92 93 func trc(s string, args ...interface{}) string { //TODO- 94 switch { 95 case s == "": 96 s = fmt.Sprintf(strings.Repeat("%v ", len(args)), args...) 97 default: 98 s = fmt.Sprintf(s, args...) 99 } 100 r := fmt.Sprintf("\n%s: TRC %s", origin(2), s) 101 fmt.Fprintf(os.Stdout, "%s\n", r) 102 os.Stdout.Sync() 103 return r 104 } 105 106 // ============================================================================ 107 108 var ( 109 oRecsPerSec = flag.Bool("recs_per_sec_as_mbps", false, "Show records per second as MB/s.") 110 oXTags = flag.String("xtags", "", "passed to go build of testfixture in TestTclTest") 111 tempDir string 112 ) 113 114 func TestMain(m *testing.M) { 115 fmt.Printf("test binary compiled for %s/%s\n", runtime.GOOS, runtime.GOARCH) 116 flag.Parse() 117 libc.MemAuditStart() 118 os.Exit(testMain(m)) 119 } 120 121 func testMain(m *testing.M) int { 122 var err error 123 tempDir, err = ioutil.TempDir("", "sqlite-test-") 124 if err != nil { 125 panic(err) //TODOOK 126 } 127 128 defer os.RemoveAll(tempDir) 129 130 return m.Run() 131 } 132 133 func tempDB(t testing.TB) (string, *sql.DB) { 134 dir, err := ioutil.TempDir("", "sqlite-test-") 135 if err != nil { 136 t.Fatal(err) 137 } 138 139 db, err := sql.Open(driverName, filepath.Join(dir, "tmp.db")) 140 if err != nil { 141 os.RemoveAll(dir) 142 t.Fatal(err) 143 } 144 145 return dir, db 146 } 147 148 func TestScalar(t *testing.T) { 149 dir, db := tempDB(t) 150 151 defer func() { 152 db.Close() 153 os.RemoveAll(dir) 154 }() 155 156 t1 := time.Date(2017, 4, 20, 1, 2, 3, 56789, time.UTC) 157 t2 := time.Date(2018, 5, 21, 2, 3, 4, 98765, time.UTC) 158 r, err := db.Exec(` 159 create table t(i int, f double, b bool, s text, t time); 160 insert into t values(12, 3.14, ?, 'foo', ?), (34, 2.78, ?, 'bar', ?); 161 `, 162 true, t1, 163 false, t2, 164 ) 165 if err != nil { 166 t.Fatal(err) 167 } 168 169 n, err := r.RowsAffected() 170 if err != nil { 171 t.Fatal(err) 172 } 173 174 if g, e := n, int64(2); g != e { 175 t.Fatal(g, e) 176 } 177 178 rows, err := db.Query("select * from t") 179 if err != nil { 180 t.Fatal(err) 181 } 182 183 type rec struct { 184 i int 185 f float64 186 b bool 187 s string 188 t string 189 } 190 var a []rec 191 for rows.Next() { 192 var r rec 193 if err := rows.Scan(&r.i, &r.f, &r.b, &r.s, &r.t); err != nil { 194 t.Fatal(err) 195 } 196 197 a = append(a, r) 198 } 199 if err := rows.Err(); err != nil { 200 t.Fatal(err) 201 } 202 203 if g, e := len(a), 2; g != e { 204 t.Fatal(g, e) 205 } 206 207 if g, e := a[0], (rec{12, 3.14, true, "foo", t1.String()}); g != e { 208 t.Fatal(g, e) 209 } 210 211 if g, e := a[1], (rec{34, 2.78, false, "bar", t2.String()}); g != e { 212 t.Fatal(g, e) 213 } 214 } 215 216 func TestBlob(t *testing.T) { 217 dir, db := tempDB(t) 218 219 defer func() { 220 db.Close() 221 os.RemoveAll(dir) 222 }() 223 224 b1 := []byte(time.Now().String()) 225 b2 := []byte("\x00foo\x00bar\x00") 226 if _, err := db.Exec(` 227 create table t(b blob); 228 insert into t values(?), (?); 229 `, b1, b2, 230 ); err != nil { 231 t.Fatal(err) 232 } 233 234 rows, err := db.Query("select * from t") 235 if err != nil { 236 t.Fatal(err) 237 } 238 239 type rec struct { 240 b []byte 241 } 242 var a []rec 243 for rows.Next() { 244 var r rec 245 if err := rows.Scan(&r.b); err != nil { 246 t.Fatal(err) 247 } 248 249 a = append(a, r) 250 } 251 if err := rows.Err(); err != nil { 252 t.Fatal(err) 253 } 254 255 if g, e := len(a), 2; g != e { 256 t.Fatal(g, e) 257 } 258 259 if g, e := a[0].b, b1; !bytes.Equal(g, e) { 260 t.Fatal(g, e) 261 } 262 263 if g, e := a[1].b, b2; !bytes.Equal(g, e) { 264 t.Fatal(g, e) 265 } 266 } 267 268 func benchmarkInsertMemory(b *testing.B, n int) { 269 db, err := sql.Open(driverName, "file::memory:") 270 if err != nil { 271 b.Fatal(err) 272 } 273 274 defer func() { 275 db.Close() 276 }() 277 278 b.ReportAllocs() 279 b.ResetTimer() 280 for i := 0; i < b.N; i++ { 281 b.StopTimer() 282 if _, err := db.Exec(` 283 drop table if exists t; 284 create table t(i int); 285 begin; 286 `); err != nil { 287 b.Fatal(err) 288 } 289 290 s, err := db.Prepare("insert into t values(?)") 291 if err != nil { 292 b.Fatal(err) 293 } 294 295 b.StartTimer() 296 for i := 0; i < n; i++ { 297 if _, err := s.Exec(int64(i)); err != nil { 298 b.Fatal(err) 299 } 300 } 301 b.StopTimer() 302 if _, err := db.Exec(`commit;`); err != nil { 303 b.Fatal(err) 304 } 305 } 306 if *oRecsPerSec { 307 b.SetBytes(1e6 * int64(n)) 308 } 309 } 310 311 func BenchmarkInsertMemory(b *testing.B) { 312 for i, n := range []int{1e1, 1e2, 1e3, 1e4, 1e5, 1e6} { 313 b.Run(fmt.Sprintf("1e%d", i+1), func(b *testing.B) { benchmarkInsertMemory(b, n) }) 314 } 315 } 316 317 var staticInt int 318 319 func benchmarkNextMemory(b *testing.B, n int) { 320 db, err := sql.Open(driverName, "file::memory:") 321 if err != nil { 322 b.Fatal(err) 323 } 324 325 defer func() { 326 db.Close() 327 }() 328 329 if _, err := db.Exec(` 330 create table t(i int); 331 begin; 332 `); err != nil { 333 b.Fatal(err) 334 } 335 336 s, err := db.Prepare("insert into t values(?)") 337 if err != nil { 338 b.Fatal(err) 339 } 340 341 for i := 0; i < n; i++ { 342 if _, err := s.Exec(int64(i)); err != nil { 343 b.Fatal(err) 344 } 345 } 346 if _, err := db.Exec(`commit;`); err != nil { 347 b.Fatal(err) 348 } 349 350 b.ReportAllocs() 351 b.ResetTimer() 352 for i := 0; i < b.N; i++ { 353 b.StopTimer() 354 r, err := db.Query("select * from t") 355 if err != nil { 356 b.Fatal(err) 357 } 358 359 b.StartTimer() 360 for i := 0; i < n; i++ { 361 if !r.Next() { 362 b.Fatal(err) 363 } 364 if err := r.Scan(&staticInt); err != nil { 365 b.Fatal(err) 366 } 367 } 368 b.StopTimer() 369 if err := r.Err(); err != nil { 370 b.Fatal(err) 371 } 372 373 r.Close() 374 } 375 if *oRecsPerSec { 376 b.SetBytes(1e6 * int64(n)) 377 } 378 } 379 380 func BenchmarkNextMemory(b *testing.B) { 381 for i, n := range []int{1e1, 1e2, 1e3, 1e4, 1e5, 1e6} { 382 b.Run(fmt.Sprintf("1e%d", i+1), func(b *testing.B) { benchmarkNextMemory(b, n) }) 383 } 384 } 385 386 // https://gitlab.com/cznic/sqlite/issues/11 387 func TestIssue11(t *testing.T) { 388 const N = 6570 389 dir, db := tempDB(t) 390 391 defer func() { 392 db.Close() 393 os.RemoveAll(dir) 394 }() 395 396 if _, err := db.Exec(` 397 CREATE TABLE t1 (t INT); 398 BEGIN; 399 `, 400 ); err != nil { 401 t.Fatal(err) 402 } 403 404 for i := 0; i < N; i++ { 405 if _, err := db.Exec("INSERT INTO t1 (t) VALUES (?)", i); err != nil { 406 t.Fatalf("#%v: %v", i, err) 407 } 408 } 409 if _, err := db.Exec("COMMIT;"); err != nil { 410 t.Fatal(err) 411 } 412 } 413 414 // https://gitlab.com/cznic/sqlite/issues/12 415 func TestMemDB(t *testing.T) { 416 // Verify we can create out-of-the heap memory DB instance. 417 db, err := sql.Open(driverName, "file::memory:") 418 if err != nil { 419 t.Fatal(err) 420 } 421 422 defer func() { 423 db.Close() 424 }() 425 426 v := strings.Repeat("a", 1024) 427 if _, err := db.Exec(` 428 create table t(s string); 429 begin; 430 `); err != nil { 431 t.Fatal(err) 432 } 433 434 s, err := db.Prepare("insert into t values(?)") 435 if err != nil { 436 t.Fatal(err) 437 } 438 439 // Heap used to be fixed at 32MB. 440 for i := 0; i < (64<<20)/len(v); i++ { 441 if _, err := s.Exec(v); err != nil { 442 t.Fatalf("%v * %v= %v: %v", i, len(v), i*len(v), err) 443 } 444 } 445 if _, err := db.Exec(`commit;`); err != nil { 446 t.Fatal(err) 447 } 448 } 449 450 func TestConcurrentGoroutines(t *testing.T) { 451 const ( 452 ngoroutines = 8 453 nrows = 5000 454 ) 455 456 dir, err := ioutil.TempDir("", "sqlite-test-") 457 if err != nil { 458 t.Fatal(err) 459 } 460 461 defer func() { 462 os.RemoveAll(dir) 463 }() 464 465 db, err := sql.Open(driverName, filepath.Join(dir, "test.db")) 466 if err != nil { 467 t.Fatal(err) 468 } 469 470 defer db.Close() 471 472 tx, err := db.BeginTx(context.Background(), nil) 473 if err != nil { 474 t.Fatal(err) 475 } 476 477 if _, err := tx.Exec("create table t(i)"); err != nil { 478 t.Fatal(err) 479 } 480 481 prep, err := tx.Prepare("insert into t values(?)") 482 if err != nil { 483 t.Fatal(err) 484 } 485 486 rnd := make(chan int, 100) 487 go func() { 488 lim := ngoroutines * nrows 489 rng, err := mathutil.NewFC32(0, lim-1, false) 490 if err != nil { 491 panic(fmt.Errorf("internal error: %v", err)) 492 } 493 494 for i := 0; i < lim; i++ { 495 rnd <- rng.Next() 496 } 497 }() 498 499 start := make(chan int) 500 var wg sync.WaitGroup 501 for i := 0; i < ngoroutines; i++ { 502 wg.Add(1) 503 504 go func(id int) { 505 506 defer wg.Done() 507 508 next: 509 for i := 0; i < nrows; i++ { 510 n := <-rnd 511 var err error 512 for j := 0; j < 10; j++ { 513 if _, err := prep.Exec(n); err == nil { 514 continue next 515 } 516 } 517 518 t.Errorf("id %d, seq %d: %v", id, i, err) 519 return 520 } 521 }(i) 522 } 523 t0 := time.Now() 524 close(start) 525 wg.Wait() 526 if err := tx.Commit(); err != nil { 527 t.Fatal(err) 528 } 529 530 d := time.Since(t0) 531 rows, err := db.Query("select * from t order by i") 532 if err != nil { 533 t.Fatal(err) 534 } 535 536 var i int 537 for ; rows.Next(); i++ { 538 var j int 539 if err := rows.Scan(&j); err != nil { 540 t.Fatalf("seq %d: %v", i, err) 541 } 542 543 if g, e := j, i; g != e { 544 t.Fatalf("seq %d: got %d, exp %d", i, g, e) 545 } 546 } 547 if err := rows.Err(); err != nil { 548 t.Fatal(err) 549 } 550 551 if g, e := i, ngoroutines*nrows; g != e { 552 t.Fatalf("got %d rows, expected %d", g, e) 553 } 554 555 t.Logf("%d goroutines concurrently inserted %d rows in %v", ngoroutines, ngoroutines*nrows, d) 556 } 557 558 func TestConcurrentProcesses(t *testing.T) { 559 dir, err := ioutil.TempDir("", "sqlite-test-") 560 if err != nil { 561 t.Fatal(err) 562 } 563 564 defer func() { 565 os.RemoveAll(dir) 566 }() 567 568 m, err := filepath.Glob(filepath.FromSlash("internal/mptest/*")) 569 if err != nil { 570 t.Fatal(err) 571 } 572 573 for _, v := range m { 574 if s := filepath.Ext(v); s != ".test" && s != ".subtest" { 575 continue 576 } 577 578 b, err := ioutil.ReadFile(v) 579 if err != nil { 580 t.Fatal(err) 581 } 582 583 if runtime.GOOS == "windows" { 584 // reference tests are in *nix format -- 585 // but git on windows does line-ending xlation by default 586 // if someone has it 'off' this has no impact. 587 // '\r\n' --> '\n' 588 b = bytes.ReplaceAll(b, []byte("\r\n"), []byte("\n")) 589 } 590 591 if err := ioutil.WriteFile(filepath.Join(dir, filepath.Base(v)), b, 0666); err != nil { 592 t.Fatal(err) 593 } 594 } 595 596 bin := "./mptest" 597 if runtime.GOOS == "windows" { 598 bin += "mptest.exe" 599 } 600 args := []string{"build", "-o", filepath.Join(dir, bin)} 601 if s := *oXTags; s != "" { 602 args = append(args, "-tags", s) 603 } 604 args = append(args, "modernc.org/sqlite/internal/mptest") 605 out, err := exec.Command("go", args...).CombinedOutput() 606 if err != nil { 607 t.Fatalf("%s\n%v", out, err) 608 } 609 610 wd, err := os.Getwd() 611 if err != nil { 612 t.Fatal(err) 613 } 614 615 defer os.Chdir(wd) 616 617 if err := os.Chdir(dir); err != nil { 618 t.Fatal(err) 619 } 620 621 outer: 622 for _, script := range m { 623 script = filepath.Base(script) 624 if filepath.Ext(script) != ".test" { 625 continue 626 } 627 628 fmt.Printf("exec: %s db --trace 2 %s\n", filepath.FromSlash(bin), script) 629 out, err := exec.Command(filepath.FromSlash(bin), "db", "--trace", "2", script).CombinedOutput() 630 if err != nil { 631 t.Fatalf("%s\n%v", out, err) 632 } 633 634 // just remove it so we don't get a 635 // file busy race-condition 636 // when we spin up the next script 637 if runtime.GOOS == "windows" { 638 _ = os.Remove("db") 639 } 640 641 a := strings.Split(string(out), "\n") 642 for _, v := range a { 643 if strings.HasPrefix(v, "Summary:") { 644 b := strings.Fields(v) 645 if len(b) < 2 { 646 t.Fatalf("unexpected format of %q", v) 647 } 648 649 n, err := strconv.Atoi(b[1]) 650 if err != nil { 651 t.Fatalf("unexpected format of %q", v) 652 } 653 654 if n != 0 { 655 t.Errorf("%s", out) 656 } 657 658 t.Logf("%v: %v", script, v) 659 continue outer 660 } 661 662 } 663 t.Fatalf("%s\nerror: summary line not found", out) 664 } 665 } 666 667 // https://gitlab.com/cznic/sqlite/issues/19 668 func TestIssue19(t *testing.T) { 669 const ( 670 drop = ` 671 drop table if exists products; 672 ` 673 674 up = ` 675 CREATE TABLE IF NOT EXISTS "products" ( 676 "id" VARCHAR(255), 677 "user_id" VARCHAR(255), 678 "name" VARCHAR(255), 679 "description" VARCHAR(255), 680 "created_at" BIGINT, 681 "credits_price" BIGINT, 682 "enabled" BOOLEAN, 683 PRIMARY KEY("id") 684 ); 685 ` 686 687 productInsert = ` 688 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'); 689 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'); 690 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'); 691 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'); 692 ` 693 ) 694 695 dir, err := ioutil.TempDir("", "sqlite-test-") 696 if err != nil { 697 t.Fatal(err) 698 } 699 700 defer func() { 701 os.RemoveAll(dir) 702 }() 703 704 wd, err := os.Getwd() 705 if err != nil { 706 t.Fatal(err) 707 } 708 709 defer os.Chdir(wd) 710 711 if err := os.Chdir(dir); err != nil { 712 t.Fatal(err) 713 } 714 715 db, err := sql.Open("sqlite", "test.db") 716 if err != nil { 717 t.Fatal("failed to connect database") 718 } 719 720 defer db.Close() 721 722 db.SetMaxOpenConns(1) 723 724 if _, err = db.Exec(drop); err != nil { 725 t.Fatal(err) 726 } 727 728 if _, err = db.Exec(up); err != nil { 729 t.Fatal(err) 730 } 731 732 if _, err = db.Exec(productInsert); err != nil { 733 t.Fatal(err) 734 } 735 736 var count int64 737 if err = db.QueryRow("select count(*) from products where user_id = ?", "16935690-348b-41a6-bb20-f8bb16011015").Scan(&count); err != nil { 738 t.Fatal(err) 739 } 740 741 if count != 4 { 742 t.Fatalf("expected result for the count query %d, we received %d\n", 4, count) 743 } 744 745 rows, err := db.Query("select * from products where user_id = ?", "16935690-348b-41a6-bb20-f8bb16011015") 746 if err != nil { 747 t.Fatal(err) 748 } 749 750 count = 0 751 for rows.Next() { 752 count++ 753 } 754 if err := rows.Err(); err != nil { 755 t.Fatal(err) 756 } 757 758 if count != 4 { 759 t.Fatalf("expected result for the select query %d, we received %d\n", 4, count) 760 } 761 762 rows, err = db.Query("select * from products where enabled = ?", true) 763 if err != nil { 764 t.Fatal(err) 765 } 766 767 count = 0 768 for rows.Next() { 769 count++ 770 } 771 if err := rows.Err(); err != nil { 772 t.Fatal(err) 773 } 774 775 if count != 3 { 776 t.Fatalf("expected result for the enabled select query %d, we received %d\n", 3, count) 777 } 778 } 779 780 func mustExec(t *testing.T, db *sql.DB, sql string, args ...interface{}) sql.Result { 781 res, err := db.Exec(sql, args...) 782 if err != nil { 783 t.Fatalf("Error running %q: %v", sql, err) 784 } 785 786 return res 787 } 788 789 // https://gitlab.com/cznic/sqlite/issues/20 790 func TestIssue20(t *testing.T) { 791 const TablePrefix = "gosqltest_" 792 793 tempDir, err := ioutil.TempDir("", "") 794 if err != nil { 795 t.Fatal(err) 796 } 797 798 defer func() { 799 os.RemoveAll(tempDir) 800 }() 801 802 db, err := sql.Open("sqlite", filepath.Join(tempDir, "foo.db")) 803 if err != nil { 804 t.Fatalf("foo.db open fail: %v", err) 805 } 806 807 defer db.Close() 808 809 mustExec(t, db, "CREATE TABLE "+TablePrefix+"t (count INT)") 810 sel, err := db.PrepareContext(context.Background(), "SELECT count FROM "+TablePrefix+"t ORDER BY count DESC") 811 if err != nil { 812 t.Fatalf("prepare 1: %v", err) 813 } 814 815 ins, err := db.PrepareContext(context.Background(), "INSERT INTO "+TablePrefix+"t (count) VALUES (?)") 816 if err != nil { 817 t.Fatalf("prepare 2: %v", err) 818 } 819 820 for n := 1; n <= 3; n++ { 821 if _, err := ins.Exec(n); err != nil { 822 t.Fatalf("insert(%d) = %v", n, err) 823 } 824 } 825 826 const nRuns = 10 827 ch := make(chan bool) 828 for i := 0; i < nRuns; i++ { 829 go func() { 830 defer func() { 831 ch <- true 832 }() 833 for j := 0; j < 10; j++ { 834 count := 0 835 if err := sel.QueryRow().Scan(&count); err != nil && err != sql.ErrNoRows { 836 t.Errorf("Query: %v", err) 837 return 838 } 839 840 if _, err := ins.Exec(rand.Intn(100)); err != nil { 841 t.Errorf("Insert: %v", err) 842 return 843 } 844 } 845 }() 846 } 847 for i := 0; i < nRuns; i++ { 848 <-ch 849 } 850 } 851 852 func TestNoRows(t *testing.T) { 853 tempDir, err := ioutil.TempDir("", "") 854 if err != nil { 855 t.Fatal(err) 856 } 857 858 defer func() { 859 os.RemoveAll(tempDir) 860 }() 861 862 db, err := sql.Open("sqlite", filepath.Join(tempDir, "foo.db")) 863 if err != nil { 864 t.Fatalf("foo.db open fail: %v", err) 865 } 866 867 defer func() { 868 db.Close() 869 }() 870 871 stmt, err := db.Prepare("create table t(i);") 872 if err != nil { 873 t.Fatal(err) 874 } 875 876 defer stmt.Close() 877 878 if _, err := stmt.Query(); err != nil { 879 t.Fatal(err) 880 } 881 } 882 883 func TestColumns(t *testing.T) { 884 db, err := sql.Open("sqlite", "file::memory:") 885 if err != nil { 886 t.Fatal(err) 887 } 888 defer db.Close() 889 890 if _, err := db.Exec("create table t1(a integer, b text, c blob)"); err != nil { 891 t.Fatal(err) 892 } 893 894 if _, err := db.Exec("insert into t1 (a) values (1)"); err != nil { 895 t.Fatal(err) 896 } 897 898 rows, err := db.Query("select * from t1") 899 if err != nil { 900 t.Fatal(err) 901 } 902 defer rows.Close() 903 904 got, err := rows.Columns() 905 if err != nil { 906 t.Fatal(err) 907 } 908 909 want := []string{"a", "b", "c"} 910 if !reflect.DeepEqual(got, want) { 911 t.Errorf("got columns %v, want %v", got, want) 912 } 913 } 914 915 // https://gitlab.com/cznic/sqlite/-/issues/32 916 func TestColumnsNoRows(t *testing.T) { 917 db, err := sql.Open("sqlite", "file::memory:") 918 if err != nil { 919 t.Fatal(err) 920 } 921 defer db.Close() 922 923 if _, err := db.Exec("create table t1(a integer, b text, c blob)"); err != nil { 924 t.Fatal(err) 925 } 926 927 rows, err := db.Query("select * from t1") 928 if err != nil { 929 t.Fatal(err) 930 } 931 defer rows.Close() 932 933 got, err := rows.Columns() 934 if err != nil { 935 t.Fatal(err) 936 } 937 938 want := []string{"a", "b", "c"} 939 if !reflect.DeepEqual(got, want) { 940 t.Errorf("got columns %v, want %v", got, want) 941 } 942 } 943 944 // https://gitlab.com/cznic/sqlite/-/issues/28 945 func TestIssue28(t *testing.T) { 946 tempDir, err := ioutil.TempDir("", "") 947 if err != nil { 948 t.Fatal(err) 949 } 950 951 defer func() { 952 os.RemoveAll(tempDir) 953 }() 954 955 db, err := sql.Open("sqlite", filepath.Join(tempDir, "test.db")) 956 if err != nil { 957 t.Fatalf("test.db open fail: %v", err) 958 } 959 960 defer db.Close() 961 962 if _, err := db.Exec(`CREATE TABLE test (foo TEXT)`); err != nil { 963 t.Fatal(err) 964 } 965 966 row := db.QueryRow(`SELECT foo FROM test`) 967 var foo string 968 if err = row.Scan(&foo); err != sql.ErrNoRows { 969 t.Fatalf("got %T(%[1]v), expected %T(%[2]v)", err, sql.ErrNoRows) 970 } 971 } 972 973 // https://gitlab.com/cznic/sqlite/-/issues/30 974 func TestColumnTypes(t *testing.T) { 975 tempDir, err := ioutil.TempDir("", "") 976 if err != nil { 977 t.Fatal(err) 978 } 979 980 defer func() { 981 os.RemoveAll(tempDir) 982 }() 983 984 db, err := sql.Open("sqlite", filepath.Join(tempDir, "test.db")) 985 if err != nil { 986 t.Fatalf("test.db open fail: %v", err) 987 } 988 989 defer db.Close() 990 991 _, 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);") 992 if err != nil { 993 t.Fatal(err) 994 } 995 996 insertStatement := `INSERT INTO userinfo(username, departname, created) values("astaxie", "研发部门", "2012-12-09")` 997 _, err = db.Query(insertStatement) 998 if err != nil { 999 t.Fatal(err) 1000 } 1001 1002 rows2, err := db.Query("SELECT * FROM userinfo") 1003 if err != nil { 1004 t.Fatal(err) 1005 } 1006 defer rows2.Close() 1007 1008 columnTypes, err := rows2.ColumnTypes() 1009 if err != nil { 1010 t.Fatal(err) 1011 } 1012 1013 var b strings.Builder 1014 for index, value := range columnTypes { 1015 precision, scale, precisionOk := value.DecimalSize() 1016 length, lengthOk := value.Length() 1017 nullable, nullableOk := value.Nullable() 1018 fmt.Fprintf(&b, "Col %d: DatabaseTypeName %q, DecimalSize %v %v %v, Length %v %v, Name %q, Nullable %v %v, ScanType %q\n", 1019 index, 1020 value.DatabaseTypeName(), 1021 precision, scale, precisionOk, 1022 length, lengthOk, 1023 value.Name(), 1024 nullable, nullableOk, 1025 value.ScanType(), 1026 ) 1027 } 1028 if err := rows2.Err(); err != nil { 1029 t.Fatal(err) 1030 } 1031 1032 if g, e := b.String(), `Col 0: DatabaseTypeName "INTEGER", DecimalSize 0 0 false, Length 0 false, Name "uid", Nullable true true, ScanType "int64" 1033 Col 1: DatabaseTypeName "VARCHAR(64)", DecimalSize 0 0 false, Length 9223372036854775807 true, Name "username", Nullable true true, ScanType "string" 1034 Col 2: DatabaseTypeName "VARCHAR(64)", DecimalSize 0 0 false, Length 9223372036854775807 true, Name "departname", Nullable true true, ScanType "string" 1035 Col 3: DatabaseTypeName "DATE", DecimalSize 0 0 false, Length 9223372036854775807 true, Name "created", Nullable true true, ScanType "string" 1036 `; g != e { 1037 t.Fatalf("---- got\n%s\n----expected\n%s", g, e) 1038 } 1039 t.Log(b.String()) 1040 } 1041 1042 // https://gitlab.com/cznic/sqlite/-/issues/32 1043 func TestColumnTypesNoRows(t *testing.T) { 1044 tempDir, err := ioutil.TempDir("", "") 1045 if err != nil { 1046 t.Fatal(err) 1047 } 1048 1049 defer func() { 1050 os.RemoveAll(tempDir) 1051 }() 1052 1053 db, err := sql.Open("sqlite", filepath.Join(tempDir, "test.db")) 1054 if err != nil { 1055 t.Fatalf("test.db open fail: %v", err) 1056 } 1057 1058 defer db.Close() 1059 1060 _, 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);") 1061 if err != nil { 1062 t.Fatal(err) 1063 } 1064 1065 rows2, err := db.Query("SELECT * FROM userinfo") 1066 if err != nil { 1067 t.Fatal(err) 1068 } 1069 defer rows2.Close() 1070 1071 columnTypes, err := rows2.ColumnTypes() 1072 if err != nil { 1073 t.Fatal(err) 1074 } 1075 1076 var b strings.Builder 1077 for index, value := range columnTypes { 1078 precision, scale, precisionOk := value.DecimalSize() 1079 length, lengthOk := value.Length() 1080 nullable, nullableOk := value.Nullable() 1081 fmt.Fprintf(&b, "Col %d: DatabaseTypeName %q, DecimalSize %v %v %v, Length %v %v, Name %q, Nullable %v %v, ScanType %q\n", 1082 index, 1083 value.DatabaseTypeName(), 1084 precision, scale, precisionOk, 1085 length, lengthOk, 1086 value.Name(), 1087 nullable, nullableOk, 1088 value.ScanType(), 1089 ) 1090 } 1091 if err := rows2.Err(); err != nil { 1092 t.Fatal(err) 1093 } 1094 1095 if g, e := b.String(), `Col 0: DatabaseTypeName "INTEGER", DecimalSize 0 0 false, Length 0 false, Name "uid", Nullable true true, ScanType %!q(<nil>) 1096 Col 1: DatabaseTypeName "VARCHAR(64)", DecimalSize 0 0 false, Length 0 false, Name "username", Nullable true true, ScanType %!q(<nil>) 1097 Col 2: DatabaseTypeName "VARCHAR(64)", DecimalSize 0 0 false, Length 0 false, Name "departname", Nullable true true, ScanType %!q(<nil>) 1098 Col 3: DatabaseTypeName "DATE", DecimalSize 0 0 false, Length 0 false, Name "created", Nullable true true, ScanType %!q(<nil>) 1099 `; g != e { 1100 t.Fatalf("---- got\n%s\n----expected\n%s", g, e) 1101 } 1102 t.Log(b.String()) 1103 } 1104 1105 // https://gitlab.com/cznic/sqlite/-/issues/35 1106 func TestTime(t *testing.T) { 1107 types := []string{ 1108 "DATE", 1109 "DATETIME", 1110 "Date", 1111 "DateTime", 1112 "TIMESTAMP", 1113 "TimeStamp", 1114 "date", 1115 "datetime", 1116 "timestamp", 1117 } 1118 db, err := sql.Open(driverName, "file::memory:") 1119 if err != nil { 1120 t.Fatal(err) 1121 } 1122 1123 defer func() { 1124 db.Close() 1125 }() 1126 1127 for _, typ := range types { 1128 if _, err := db.Exec(fmt.Sprintf(` 1129 drop table if exists mg; 1130 create table mg (applied_at %s); 1131 `, typ)); err != nil { 1132 t.Fatal(err) 1133 } 1134 1135 now := time.Now() 1136 _, err = db.Exec(`INSERT INTO mg (applied_at) VALUES (?)`, &now) 1137 if err != nil { 1138 t.Fatal(err) 1139 } 1140 1141 var appliedAt time.Time 1142 err = db.QueryRow("SELECT applied_at FROM mg").Scan(&appliedAt) 1143 if err != nil { 1144 t.Fatal(err) 1145 } 1146 1147 if g, e := appliedAt, now; !g.Equal(e) { 1148 t.Fatal(g, e) 1149 } 1150 } 1151 } 1152 1153 // https://gitlab.com/cznic/sqlite/-/issues/46 1154 func TestTimeScan(t *testing.T) { 1155 ref := time.Date(2021, 1, 2, 16, 39, 17, 123456789, time.UTC) 1156 1157 cases := []struct { 1158 s string 1159 w time.Time 1160 }{ 1161 {s: "2021-01-02 12:39:17 -0400 ADT m=+00000", w: ref.Truncate(time.Second)}, 1162 {s: "2021-01-02 16:39:17 +0000 UTC m=+0.000000001", w: ref.Truncate(time.Second)}, 1163 {s: "2021-01-02 12:39:17.123456 -0400 ADT m=+00000", w: ref.Truncate(time.Microsecond)}, 1164 {s: "2021-01-02 16:39:17.123456 +0000 UTC m=+0.000000001", w: ref.Truncate(time.Microsecond)}, 1165 {s: "2021-01-02 16:39:17Z", w: ref.Truncate(time.Second)}, 1166 {s: "2021-01-02 16:39:17+00:00", w: ref.Truncate(time.Second)}, 1167 {s: "2021-01-02T16:39:17.123456+00:00", w: ref.Truncate(time.Microsecond)}, 1168 {s: "2021-01-02 16:39:17.123456+00:00", w: ref.Truncate(time.Microsecond)}, 1169 {s: "2021-01-02 12:39:17-04:00", w: ref.Truncate(time.Second)}, 1170 {s: "2021-01-02 16:39:17", w: ref.Truncate(time.Second)}, 1171 {s: "2021-01-02T16:39:17", w: ref.Truncate(time.Second)}, 1172 {s: "2021-01-02 16:39", w: ref.Truncate(time.Minute)}, 1173 {s: "2021-01-02T16:39", w: ref.Truncate(time.Minute)}, 1174 {s: "2021-01-02", w: ref.Truncate(24 * time.Hour)}, 1175 } 1176 1177 db, err := sql.Open(driverName, "file::memory:") 1178 if err != nil { 1179 t.Fatal(err) 1180 } 1181 defer db.Close() 1182 1183 for _, colType := range []string{"DATE", "DATETIME", "TIMESTAMP"} { 1184 for _, tc := range cases { 1185 if _, err := db.Exec("drop table if exists x; create table x (y " + colType + ")"); err != nil { 1186 t.Fatal(err) 1187 } 1188 if _, err := db.Exec("insert into x (y) values (?)", tc.s); err != nil { 1189 t.Fatal(err) 1190 } 1191 1192 var got time.Time 1193 1194 if err := db.QueryRow("select y from x").Scan(&got); err != nil { 1195 t.Fatal(err) 1196 } 1197 if !got.Equal(tc.w) { 1198 t.Errorf("scan(%q as %q) = %s, want %s", tc.s, colType, got, tc.w) 1199 } 1200 } 1201 } 1202 } 1203 1204 // https://gitlab.com/cznic/sqlite/-/issues/49 1205 func TestTimeLocaltime(t *testing.T) { 1206 db, err := sql.Open(driverName, "file::memory:") 1207 if err != nil { 1208 t.Fatal(err) 1209 } 1210 defer db.Close() 1211 1212 if _, err := db.Exec("select datetime('now', 'localtime')"); err != nil { 1213 t.Fatal(err) 1214 } 1215 } 1216 1217 // https://sqlite.org/lang_expr.html#varparam 1218 // https://gitlab.com/cznic/sqlite/-/issues/42 1219 func TestBinding(t *testing.T) { 1220 t.Run("DB", func(t *testing.T) { 1221 testBinding(t, func(db *sql.DB, query string, args ...interface{}) (*sql.Row, func()) { 1222 return db.QueryRow(query, args...), func() {} 1223 }) 1224 }) 1225 1226 t.Run("Prepare", func(t *testing.T) { 1227 testBinding(t, func(db *sql.DB, query string, args ...interface{}) (*sql.Row, func()) { 1228 stmt, err := db.Prepare(query) 1229 if err != nil { 1230 t.Fatal(err) 1231 } 1232 return stmt.QueryRow(args...), func() { stmt.Close() } 1233 }) 1234 }) 1235 } 1236 1237 func testBinding(t *testing.T, query func(db *sql.DB, query string, args ...interface{}) (*sql.Row, func())) { 1238 db, err := sql.Open(driverName, "file::memory:") 1239 if err != nil { 1240 t.Fatal(err) 1241 } 1242 defer db.Close() 1243 1244 for _, tc := range []struct { 1245 q string 1246 in []interface{} 1247 w []int 1248 }{ 1249 { 1250 q: "?, ?, ?", 1251 in: []interface{}{1, 2, 3}, 1252 w: []int{1, 2, 3}, 1253 }, 1254 { 1255 q: "?1, ?2, ?3", 1256 in: []interface{}{1, 2, 3}, 1257 w: []int{1, 2, 3}, 1258 }, 1259 { 1260 q: "?1, ?, ?3", 1261 in: []interface{}{1, 2, 3}, 1262 w: []int{1, 2, 3}, 1263 }, 1264 { 1265 q: "?3, ?2, ?1", 1266 in: []interface{}{1, 2, 3}, 1267 w: []int{3, 2, 1}, 1268 }, 1269 { 1270 q: "?1, ?1, ?2", 1271 in: []interface{}{1, 2}, 1272 w: []int{1, 1, 2}, 1273 }, 1274 { 1275 q: ":one, :two, :three", 1276 in: []interface{}{sql.Named("one", 1), sql.Named("two", 2), sql.Named("three", 3)}, 1277 w: []int{1, 2, 3}, 1278 }, 1279 { 1280 q: ":one, :one, :two", 1281 in: []interface{}{sql.Named("one", 1), sql.Named("two", 2)}, 1282 w: []int{1, 1, 2}, 1283 }, 1284 { 1285 q: "@one, @two, @three", 1286 in: []interface{}{sql.Named("one", 1), sql.Named("two", 2), sql.Named("three", 3)}, 1287 w: []int{1, 2, 3}, 1288 }, 1289 { 1290 q: "@one, @one, @two", 1291 in: []interface{}{sql.Named("one", 1), sql.Named("two", 2)}, 1292 w: []int{1, 1, 2}, 1293 }, 1294 { 1295 q: "$one, $two, $three", 1296 in: []interface{}{sql.Named("one", 1), sql.Named("two", 2), sql.Named("three", 3)}, 1297 w: []int{1, 2, 3}, 1298 }, 1299 { 1300 // A common usage that should technically require sql.Named but 1301 // does not. 1302 q: "$1, $2, $3", 1303 in: []interface{}{1, 2, 3}, 1304 w: []int{1, 2, 3}, 1305 }, 1306 { 1307 q: "$one, $one, $two", 1308 in: []interface{}{sql.Named("one", 1), sql.Named("two", 2)}, 1309 w: []int{1, 1, 2}, 1310 }, 1311 { 1312 q: ":one, @one, $one", 1313 in: []interface{}{sql.Named("one", 1)}, 1314 w: []int{1, 1, 1}, 1315 }, 1316 } { 1317 got := make([]int, len(tc.w)) 1318 ptrs := make([]interface{}, len(got)) 1319 for i := range got { 1320 ptrs[i] = &got[i] 1321 } 1322 1323 row, cleanup := query(db, "select "+tc.q, tc.in...) 1324 defer cleanup() 1325 1326 if err := row.Scan(ptrs...); err != nil { 1327 t.Errorf("query(%q, %+v) = %s", tc.q, tc.in, err) 1328 continue 1329 } 1330 1331 if !reflect.DeepEqual(got, tc.w) { 1332 t.Errorf("query(%q, %+v) = %#+v, want %#+v", tc.q, tc.in, got, tc.w) 1333 } 1334 } 1335 } 1336 1337 func TestBindingError(t *testing.T) { 1338 t.Run("DB", func(t *testing.T) { 1339 testBindingError(t, func(db *sql.DB, query string, args ...interface{}) (*sql.Row, func()) { 1340 return db.QueryRow(query, args...), func() {} 1341 }) 1342 }) 1343 1344 t.Run("Prepare", func(t *testing.T) { 1345 testBindingError(t, func(db *sql.DB, query string, args ...interface{}) (*sql.Row, func()) { 1346 stmt, err := db.Prepare(query) 1347 if err != nil { 1348 t.Fatal(err) 1349 } 1350 return stmt.QueryRow(args...), func() { stmt.Close() } 1351 }) 1352 }) 1353 } 1354 1355 func testBindingError(t *testing.T, query func(db *sql.DB, query string, args ...interface{}) (*sql.Row, func())) { 1356 db, err := sql.Open(driverName, "file::memory:") 1357 if err != nil { 1358 t.Fatal(err) 1359 } 1360 defer db.Close() 1361 1362 for _, tc := range []struct { 1363 q string 1364 in []interface{} 1365 }{ 1366 { 1367 q: "?", 1368 in: []interface{}{}, 1369 }, 1370 { 1371 q: "?500, ?", 1372 in: []interface{}{1, 2}, 1373 }, 1374 { 1375 q: ":one", 1376 in: []interface{}{1}, 1377 }, 1378 { 1379 q: "@one", 1380 in: []interface{}{1}, 1381 }, 1382 { 1383 q: "$one", 1384 in: []interface{}{1}, 1385 }, 1386 } { 1387 got := make([]int, 2) 1388 ptrs := make([]interface{}, len(got)) 1389 for i := range got { 1390 ptrs[i] = &got[i] 1391 } 1392 1393 row, cleanup := query(db, "select "+tc.q, tc.in...) 1394 defer cleanup() 1395 1396 err := row.Scan(ptrs...) 1397 if err == nil || (!strings.Contains(err.Error(), "missing argument with index") && !strings.Contains(err.Error(), "missing named argument")) { 1398 t.Errorf("query(%q, %+v) unexpected error %+v", tc.q, tc.in, err) 1399 } 1400 } 1401 } 1402 1403 // https://gitlab.com/cznic/sqlite/-/issues/51 1404 func TestIssue51(t *testing.T) { 1405 tempDir, err := ioutil.TempDir("", "") 1406 if err != nil { 1407 t.Fatal(err) 1408 } 1409 1410 defer func() { 1411 os.RemoveAll(tempDir) 1412 }() 1413 1414 fn := filepath.Join(tempDir, "test_issue51.db") 1415 db, err := sql.Open(driverName, fn) 1416 if err != nil { 1417 t.Fatal(err) 1418 } 1419 1420 defer func() { 1421 db.Close() 1422 }() 1423 1424 if _, err := db.Exec(` 1425 CREATE TABLE fileHash ( 1426 "hash" TEXT NOT NULL PRIMARY KEY, 1427 "filename" TEXT, 1428 "lastChecked" INTEGER 1429 );`); err != nil { 1430 t.Fatal(err) 1431 } 1432 1433 t0 := time.Now() 1434 n := 0 1435 for time.Since(t0) < time.Minute { 1436 hash := randomString() 1437 if _, err = lookupHash(fn, hash); err != nil { 1438 t.Fatal(err) 1439 } 1440 1441 if err = saveHash(fn, hash, hash+".temp"); err != nil { 1442 t.Error(err) 1443 break 1444 } 1445 n++ 1446 } 1447 t.Logf("cycles: %v", n) 1448 row := db.QueryRow("select count(*) from fileHash") 1449 if err := row.Scan(&n); err != nil { 1450 t.Fatal(err) 1451 } 1452 1453 t.Logf("DB records: %v", n) 1454 } 1455 1456 func saveHash(dbFile string, hash string, fileName string) (err error) { 1457 db, err := sql.Open("sqlite", dbFile) 1458 if err != nil { 1459 return fmt.Errorf("could not open database: %v", err) 1460 } 1461 1462 defer func() { 1463 if err2 := db.Close(); err2 != nil && err == nil { 1464 err = fmt.Errorf("could not close the database: %s", err2) 1465 } 1466 }() 1467 1468 query := `INSERT OR REPLACE INTO fileHash(hash, fileName, lastChecked) 1469 VALUES(?, ?, ?);` 1470 rows, err := executeSQL(db, query, hash, fileName, time.Now().Unix()) 1471 if err != nil { 1472 return fmt.Errorf("error saving hash to database: %v", err) 1473 } 1474 defer rows.Close() 1475 1476 return nil 1477 } 1478 1479 func executeSQL(db *sql.DB, query string, values ...interface{}) (*sql.Rows, error) { 1480 statement, err := db.Prepare(query) 1481 if err != nil { 1482 return nil, fmt.Errorf("could not prepare statement: %v", err) 1483 } 1484 defer statement.Close() 1485 1486 return statement.Query(values...) 1487 } 1488 1489 func lookupHash(dbFile string, hash string) (ok bool, err error) { 1490 db, err := sql.Open("sqlite", dbFile) 1491 if err != nil { 1492 return false, fmt.Errorf("could not open database: %n", err) 1493 } 1494 1495 defer func() { 1496 if err2 := db.Close(); err2 != nil && err == nil { 1497 err = fmt.Errorf("could not close the database: %v", err2) 1498 } 1499 }() 1500 1501 query := `SELECT hash, fileName, lastChecked 1502 FROM fileHash 1503 WHERE hash=?;` 1504 rows, err := executeSQL(db, query, hash) 1505 if err != nil { 1506 return false, fmt.Errorf("error checking database for hash: %n", err) 1507 } 1508 1509 defer func() { 1510 if err2 := rows.Close(); err2 != nil && err == nil { 1511 err = fmt.Errorf("could not close DB rows: %v", err2) 1512 } 1513 }() 1514 1515 var ( 1516 dbHash string 1517 fileName string 1518 lastChecked int64 1519 ) 1520 for rows.Next() { 1521 err = rows.Scan(&dbHash, &fileName, &lastChecked) 1522 if err != nil { 1523 return false, fmt.Errorf("could not read DB row: %v", err) 1524 } 1525 } 1526 return false, rows.Err() 1527 } 1528 1529 func randomString() string { 1530 b := make([]byte, 32) 1531 for i := range b { 1532 b[i] = charset[seededRand.Intn(len(charset))] 1533 } 1534 return string(b) 1535 } 1536 1537 var seededRand *rand.Rand = rand.New(rand.NewSource(time.Now().UnixNano())) 1538 1539 const charset = "abcdefghijklmnopqrstuvwxyz" + 1540 "ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789" 1541 1542 // https://gitlab.com/cznic/sqlite/-/issues/53 1543 func TestIssue53(t *testing.T) { 1544 tempDir, err := ioutil.TempDir("", "") 1545 if err != nil { 1546 t.Fatal(err) 1547 } 1548 1549 defer func() { 1550 os.RemoveAll(tempDir) 1551 }() 1552 1553 wd, err := os.Getwd() 1554 if err != nil { 1555 t.Fatal(err) 1556 } 1557 1558 defer os.Chdir(wd) 1559 1560 if err := os.Chdir(tempDir); err != nil { 1561 t.Fatal(err) 1562 } 1563 1564 const fn = "testissue53.sqlite" 1565 1566 db, err := sql.Open(driverName, fn) 1567 if err != nil { 1568 t.Fatal(err) 1569 } 1570 1571 defer func() { 1572 db.Close() 1573 }() 1574 1575 if _, err := db.Exec(` 1576 CREATE TABLE IF NOT EXISTS loginst ( 1577 instid INTEGER PRIMARY KEY, 1578 name VARCHAR UNIQUE 1579 ); 1580 `); err != nil { 1581 t.Fatal(err) 1582 } 1583 1584 tx, err := db.Begin() 1585 if err != nil { 1586 t.Fatal(err) 1587 } 1588 1589 for i := 0; i < 5000; i++ { 1590 x := fmt.Sprintf("foo%d", i) 1591 var id int 1592 if err := tx.QueryRow("INSERT OR IGNORE INTO loginst (name) VALUES (?); SELECT instid FROM loginst WHERE name = ?", x, x).Scan(&id); err != nil { 1593 t.Fatal(err) 1594 } 1595 } 1596 1597 } 1598 1599 // https://gitlab.com/cznic/sqlite/-/issues/37 1600 func TestPersistPragma(t *testing.T) { 1601 tempDir, err := ioutil.TempDir("", "") 1602 if err != nil { 1603 t.Fatal(err) 1604 } 1605 1606 defer func() { 1607 os.RemoveAll(tempDir) 1608 }() 1609 1610 wd, err := os.Getwd() 1611 if err != nil { 1612 t.Fatal(err) 1613 } 1614 1615 defer os.Chdir(wd) 1616 1617 if err := os.Chdir(tempDir); err != nil { 1618 t.Fatal(err) 1619 } 1620 1621 pragmas := []pragmaCfg{ 1622 {"foreign_keys", "on", int64(1)}, 1623 {"analysis_limit", "1000", int64(1000)}, 1624 {"application_id", "214", int64(214)}, 1625 {"encoding", "'UTF-16le'", "UTF-16le"}} 1626 1627 if err := testPragmas("testpersistpragma.sqlite", "testpersistpragma.sqlite", pragmas); err != nil { 1628 t.Fatal(err) 1629 } 1630 if err := testPragmas("file::memory:", "", pragmas); err != nil { 1631 t.Fatal(err) 1632 } 1633 if err := testPragmas(":memory:", "", pragmas); err != nil { 1634 t.Fatal(err) 1635 } 1636 } 1637 1638 type pragmaCfg struct { 1639 name string 1640 value string 1641 expected interface{} 1642 } 1643 1644 func testPragmas(name, diskFile string, pragmas []pragmaCfg) error { 1645 if diskFile != "" { 1646 os.Remove(diskFile) 1647 } 1648 1649 q := url.Values{} 1650 for _, pragma := range pragmas { 1651 q.Add("_pragma", pragma.name+"="+pragma.value) 1652 } 1653 1654 dsn := name + "?" + q.Encode() 1655 db, err := sql.Open(driverName, dsn) 1656 if err != nil { 1657 return err 1658 } 1659 1660 db.SetMaxOpenConns(1) 1661 1662 if err := checkPragmas(db, pragmas); err != nil { 1663 return err 1664 } 1665 1666 c, err := db.Conn(context.Background()) 1667 if err != nil { 1668 return err 1669 } 1670 1671 // Kill the connection to spawn a new one. Pragma configs should persist 1672 c.Raw(func(interface{}) error { return driver.ErrBadConn }) 1673 1674 if err := checkPragmas(db, pragmas); err != nil { 1675 return err 1676 } 1677 1678 if diskFile == "" { 1679 // Make sure in memory databases aren't being written to disk 1680 return testInMemory(db) 1681 } 1682 1683 return nil 1684 } 1685 1686 func checkPragmas(db *sql.DB, pragmas []pragmaCfg) error { 1687 for _, pragma := range pragmas { 1688 row := db.QueryRow(`PRAGMA ` + pragma.name) 1689 1690 var result interface{} 1691 if err := row.Scan(&result); err != nil { 1692 return err 1693 } 1694 if result != pragma.expected { 1695 return fmt.Errorf("expected PRAGMA %s to return %v but got %v", pragma.name, pragma.expected, result) 1696 } 1697 } 1698 return nil 1699 } 1700 1701 func TestInMemory(t *testing.T) { 1702 tempDir, err := ioutil.TempDir("", "") 1703 if err != nil { 1704 t.Fatal(err) 1705 } 1706 1707 defer func() { 1708 os.RemoveAll(tempDir) 1709 }() 1710 1711 wd, err := os.Getwd() 1712 if err != nil { 1713 t.Fatal(err) 1714 } 1715 1716 defer os.Chdir(wd) 1717 1718 if err := os.Chdir(tempDir); err != nil { 1719 t.Fatal(err) 1720 } 1721 1722 if err := testMemoryPath(":memory:"); err != nil { 1723 t.Fatal(err) 1724 } 1725 if err := testMemoryPath("file::memory:"); err != nil { 1726 t.Fatal(err) 1727 } 1728 1729 // This parameter should be ignored 1730 q := url.Values{} 1731 q.Add("mode", "readonly") 1732 if err := testMemoryPath(":memory:?" + q.Encode()); err != nil { 1733 t.Fatal(err) 1734 } 1735 } 1736 1737 func testMemoryPath(mPath string) error { 1738 db, err := sql.Open(driverName, mPath) 1739 if err != nil { 1740 return err 1741 } 1742 defer db.Close() 1743 1744 return testInMemory(db) 1745 } 1746 1747 func testInMemory(db *sql.DB) error { 1748 _, err := db.Exec(` 1749 create table in_memory_test(i int, f double); 1750 insert into in_memory_test values(12, 3.14); 1751 `) 1752 if err != nil { 1753 return err 1754 } 1755 1756 files, err := ioutil.ReadDir("./") 1757 if err != nil { 1758 return err 1759 } 1760 1761 for _, file := range files { 1762 if strings.Contains(file.Name(), "memory") { 1763 return fmt.Errorf("file was created for in memory database") 1764 } 1765 } 1766 1767 return nil 1768 } 1769 1770 func emptyDir(s string) error { 1771 m, err := filepath.Glob(filepath.FromSlash(s + "/*")) 1772 if err != nil { 1773 return err 1774 } 1775 1776 for _, v := range m { 1777 fi, err := os.Stat(v) 1778 if err != nil { 1779 return err 1780 } 1781 1782 switch { 1783 case fi.IsDir(): 1784 if err = os.RemoveAll(v); err != nil { 1785 return err 1786 } 1787 default: 1788 if err = os.Remove(v); err != nil { 1789 return err 1790 } 1791 } 1792 } 1793 return nil 1794 }