github.com/CanonicalLtd/go-sqlite3@v1.6.0/sqlite3_test.go (about) 1 // Copyright (C) 2014 Yasuhiro Matsumoto <mattn.jp@gmail.com>. 2 // 3 // Use of this source code is governed by an MIT-style 4 // license that can be found in the LICENSE file. 5 6 package sqlite3 7 8 import ( 9 "bytes" 10 "database/sql" 11 "database/sql/driver" 12 "errors" 13 "fmt" 14 "io/ioutil" 15 "math/rand" 16 "net/url" 17 "os" 18 "reflect" 19 "regexp" 20 "strconv" 21 "strings" 22 "sync" 23 "testing" 24 "time" 25 ) 26 27 func TempFilename(t *testing.T) string { 28 f, err := ioutil.TempFile("", "go-sqlite3-test-") 29 if err != nil { 30 t.Fatal(err) 31 } 32 f.Close() 33 return f.Name() 34 } 35 36 func doTestOpen(t *testing.T, option string) (string, error) { 37 var url string 38 tempFilename := TempFilename(t) 39 defer os.Remove(tempFilename) 40 if option != "" { 41 url = tempFilename + option 42 } else { 43 url = tempFilename 44 } 45 db, err := sql.Open("sqlite3", url) 46 if err != nil { 47 return "Failed to open database:", err 48 } 49 defer os.Remove(tempFilename) 50 defer db.Close() 51 52 _, err = db.Exec("drop table foo") 53 _, err = db.Exec("create table foo (id integer)") 54 if err != nil { 55 return "Failed to create table:", err 56 } 57 58 if stat, err := os.Stat(tempFilename); err != nil || stat.IsDir() { 59 return "Failed to create ./foo.db", nil 60 } 61 62 return "", nil 63 } 64 65 func TestOpen(t *testing.T) { 66 cases := map[string]bool{ 67 "": true, 68 "?_txlock=immediate": true, 69 "?_txlock=deferred": true, 70 "?_txlock=exclusive": true, 71 "?_txlock=bogus": false, 72 } 73 for option, expectedPass := range cases { 74 result, err := doTestOpen(t, option) 75 if result == "" { 76 if !expectedPass { 77 errmsg := fmt.Sprintf("_txlock error not caught at dbOpen with option: %s", option) 78 t.Fatal(errmsg) 79 } 80 } else if expectedPass { 81 if err == nil { 82 t.Fatal(result) 83 } else { 84 t.Fatal(result, err) 85 } 86 } 87 } 88 } 89 90 func TestReadonly(t *testing.T) { 91 tempFilename := TempFilename(t) 92 defer os.Remove(tempFilename) 93 94 db1, err := sql.Open("sqlite3", "file:"+tempFilename) 95 if err != nil { 96 t.Fatal(err) 97 } 98 db1.Exec("CREATE TABLE test (x int, y float)") 99 100 db2, err := sql.Open("sqlite3", "file:"+tempFilename+"?mode=ro") 101 if err != nil { 102 t.Fatal(err) 103 } 104 _ = db2 105 _, err = db2.Exec("INSERT INTO test VALUES (1, 3.14)") 106 if err == nil { 107 t.Fatal("didn't expect INSERT into read-only database to work") 108 } 109 } 110 111 func TestForeignKeys(t *testing.T) { 112 cases := map[string]bool{ 113 "?_foreign_keys=1": true, 114 "?_foreign_keys=0": false, 115 } 116 for option, want := range cases { 117 fname := TempFilename(t) 118 uri := "file:" + fname + option 119 db, err := sql.Open("sqlite3", uri) 120 if err != nil { 121 os.Remove(fname) 122 t.Errorf("sql.Open(\"sqlite3\", %q): %v", uri, err) 123 continue 124 } 125 var enabled bool 126 err = db.QueryRow("PRAGMA foreign_keys;").Scan(&enabled) 127 db.Close() 128 os.Remove(fname) 129 if err != nil { 130 t.Errorf("query foreign_keys for %s: %v", uri, err) 131 continue 132 } 133 if enabled != want { 134 t.Errorf("\"PRAGMA foreign_keys;\" for %q = %t; want %t", uri, enabled, want) 135 continue 136 } 137 } 138 } 139 140 func TestRecursiveTriggers(t *testing.T) { 141 cases := map[string]bool{ 142 "?_recursive_triggers=1": true, 143 "?_recursive_triggers=0": false, 144 } 145 for option, want := range cases { 146 fname := TempFilename(t) 147 uri := "file:" + fname + option 148 db, err := sql.Open("sqlite3", uri) 149 if err != nil { 150 os.Remove(fname) 151 t.Errorf("sql.Open(\"sqlite3\", %q): %v", uri, err) 152 continue 153 } 154 var enabled bool 155 err = db.QueryRow("PRAGMA recursive_triggers;").Scan(&enabled) 156 db.Close() 157 os.Remove(fname) 158 if err != nil { 159 t.Errorf("query recursive_triggers for %s: %v", uri, err) 160 continue 161 } 162 if enabled != want { 163 t.Errorf("\"PRAGMA recursive_triggers;\" for %q = %t; want %t", uri, enabled, want) 164 continue 165 } 166 } 167 } 168 169 func TestClose(t *testing.T) { 170 tempFilename := TempFilename(t) 171 defer os.Remove(tempFilename) 172 db, err := sql.Open("sqlite3", tempFilename) 173 if err != nil { 174 t.Fatal("Failed to open database:", err) 175 } 176 177 _, err = db.Exec("drop table foo") 178 _, err = db.Exec("create table foo (id integer)") 179 if err != nil { 180 t.Fatal("Failed to create table:", err) 181 } 182 183 stmt, err := db.Prepare("select id from foo where id = ?") 184 if err != nil { 185 t.Fatal("Failed to select records:", err) 186 } 187 188 db.Close() 189 _, err = stmt.Exec(1) 190 if err == nil { 191 t.Fatal("Failed to operate closed statement") 192 } 193 } 194 195 func TestInsert(t *testing.T) { 196 tempFilename := TempFilename(t) 197 defer os.Remove(tempFilename) 198 db, err := sql.Open("sqlite3", tempFilename) 199 if err != nil { 200 t.Fatal("Failed to open database:", err) 201 } 202 defer db.Close() 203 204 _, err = db.Exec("drop table foo") 205 _, err = db.Exec("create table foo (id integer)") 206 if err != nil { 207 t.Fatal("Failed to create table:", err) 208 } 209 210 res, err := db.Exec("insert into foo(id) values(123)") 211 if err != nil { 212 t.Fatal("Failed to insert record:", err) 213 } 214 affected, _ := res.RowsAffected() 215 if affected != 1 { 216 t.Fatalf("Expected %d for affected rows, but %d:", 1, affected) 217 } 218 219 rows, err := db.Query("select id from foo") 220 if err != nil { 221 t.Fatal("Failed to select records:", err) 222 } 223 defer rows.Close() 224 225 rows.Next() 226 227 var result int 228 rows.Scan(&result) 229 if result != 123 { 230 t.Errorf("Expected %d for fetched result, but %d:", 123, result) 231 } 232 } 233 234 func TestUpdate(t *testing.T) { 235 tempFilename := TempFilename(t) 236 defer os.Remove(tempFilename) 237 db, err := sql.Open("sqlite3", tempFilename) 238 if err != nil { 239 t.Fatal("Failed to open database:", err) 240 } 241 defer db.Close() 242 243 _, err = db.Exec("drop table foo") 244 _, err = db.Exec("create table foo (id integer)") 245 if err != nil { 246 t.Fatal("Failed to create table:", err) 247 } 248 249 res, err := db.Exec("insert into foo(id) values(123)") 250 if err != nil { 251 t.Fatal("Failed to insert record:", err) 252 } 253 expected, err := res.LastInsertId() 254 if err != nil { 255 t.Fatal("Failed to get LastInsertId:", err) 256 } 257 affected, _ := res.RowsAffected() 258 if err != nil { 259 t.Fatal("Failed to get RowsAffected:", err) 260 } 261 if affected != 1 { 262 t.Fatalf("Expected %d for affected rows, but %d:", 1, affected) 263 } 264 265 res, err = db.Exec("update foo set id = 234") 266 if err != nil { 267 t.Fatal("Failed to update record:", err) 268 } 269 lastID, err := res.LastInsertId() 270 if err != nil { 271 t.Fatal("Failed to get LastInsertId:", err) 272 } 273 if expected != lastID { 274 t.Errorf("Expected %q for last Id, but %q:", expected, lastID) 275 } 276 affected, _ = res.RowsAffected() 277 if err != nil { 278 t.Fatal("Failed to get RowsAffected:", err) 279 } 280 if affected != 1 { 281 t.Fatalf("Expected %d for affected rows, but %d:", 1, affected) 282 } 283 284 rows, err := db.Query("select id from foo") 285 if err != nil { 286 t.Fatal("Failed to select records:", err) 287 } 288 defer rows.Close() 289 290 rows.Next() 291 292 var result int 293 rows.Scan(&result) 294 if result != 234 { 295 t.Errorf("Expected %d for fetched result, but %d:", 234, result) 296 } 297 } 298 299 func TestDelete(t *testing.T) { 300 tempFilename := TempFilename(t) 301 defer os.Remove(tempFilename) 302 db, err := sql.Open("sqlite3", tempFilename) 303 if err != nil { 304 t.Fatal("Failed to open database:", err) 305 } 306 defer db.Close() 307 308 _, err = db.Exec("drop table foo") 309 _, err = db.Exec("create table foo (id integer)") 310 if err != nil { 311 t.Fatal("Failed to create table:", err) 312 } 313 314 res, err := db.Exec("insert into foo(id) values(123)") 315 if err != nil { 316 t.Fatal("Failed to insert record:", err) 317 } 318 expected, err := res.LastInsertId() 319 if err != nil { 320 t.Fatal("Failed to get LastInsertId:", err) 321 } 322 affected, err := res.RowsAffected() 323 if err != nil { 324 t.Fatal("Failed to get RowsAffected:", err) 325 } 326 if affected != 1 { 327 t.Errorf("Expected %d for cout of affected rows, but %q:", 1, affected) 328 } 329 330 res, err = db.Exec("delete from foo where id = 123") 331 if err != nil { 332 t.Fatal("Failed to delete record:", err) 333 } 334 lastID, err := res.LastInsertId() 335 if err != nil { 336 t.Fatal("Failed to get LastInsertId:", err) 337 } 338 if expected != lastID { 339 t.Errorf("Expected %q for last Id, but %q:", expected, lastID) 340 } 341 affected, err = res.RowsAffected() 342 if err != nil { 343 t.Fatal("Failed to get RowsAffected:", err) 344 } 345 if affected != 1 { 346 t.Errorf("Expected %d for cout of affected rows, but %q:", 1, affected) 347 } 348 349 rows, err := db.Query("select id from foo") 350 if err != nil { 351 t.Fatal("Failed to select records:", err) 352 } 353 defer rows.Close() 354 355 if rows.Next() { 356 t.Error("Fetched row but expected not rows") 357 } 358 } 359 360 func TestBooleanRoundtrip(t *testing.T) { 361 tempFilename := TempFilename(t) 362 defer os.Remove(tempFilename) 363 db, err := sql.Open("sqlite3", tempFilename) 364 if err != nil { 365 t.Fatal("Failed to open database:", err) 366 } 367 defer db.Close() 368 369 _, err = db.Exec("DROP TABLE foo") 370 _, err = db.Exec("CREATE TABLE foo(id INTEGER, value BOOL)") 371 if err != nil { 372 t.Fatal("Failed to create table:", err) 373 } 374 375 _, err = db.Exec("INSERT INTO foo(id, value) VALUES(1, ?)", true) 376 if err != nil { 377 t.Fatal("Failed to insert true value:", err) 378 } 379 380 _, err = db.Exec("INSERT INTO foo(id, value) VALUES(2, ?)", false) 381 if err != nil { 382 t.Fatal("Failed to insert false value:", err) 383 } 384 385 rows, err := db.Query("SELECT id, value FROM foo") 386 if err != nil { 387 t.Fatal("Unable to query foo table:", err) 388 } 389 defer rows.Close() 390 391 for rows.Next() { 392 var id int 393 var value bool 394 395 if err := rows.Scan(&id, &value); err != nil { 396 t.Error("Unable to scan results:", err) 397 continue 398 } 399 400 if id == 1 && !value { 401 t.Error("Value for id 1 should be true, not false") 402 403 } else if id == 2 && value { 404 t.Error("Value for id 2 should be false, not true") 405 } 406 } 407 } 408 409 func timezone(t time.Time) string { return t.Format("-07:00") } 410 411 func TestTimestamp(t *testing.T) { 412 tempFilename := TempFilename(t) 413 defer os.Remove(tempFilename) 414 db, err := sql.Open("sqlite3", tempFilename) 415 if err != nil { 416 t.Fatal("Failed to open database:", err) 417 } 418 defer db.Close() 419 420 _, err = db.Exec("DROP TABLE foo") 421 _, err = db.Exec("CREATE TABLE foo(id INTEGER, ts timeSTAMP, dt DATETIME)") 422 if err != nil { 423 t.Fatal("Failed to create table:", err) 424 } 425 426 timestamp1 := time.Date(2012, time.April, 6, 22, 50, 0, 0, time.UTC) 427 timestamp2 := time.Date(2006, time.January, 2, 15, 4, 5, 123456789, time.UTC) 428 timestamp3 := time.Date(2012, time.November, 4, 0, 0, 0, 0, time.UTC) 429 tzTest := time.FixedZone("TEST", -9*3600-13*60) 430 tests := []struct { 431 value interface{} 432 expected time.Time 433 }{ 434 {"nonsense", time.Time{}}, 435 {"0000-00-00 00:00:00", time.Time{}}, 436 {time.Time{}.Unix(), time.Time{}}, 437 {timestamp1, timestamp1}, 438 {timestamp2.Unix(), timestamp2.Truncate(time.Second)}, 439 {timestamp2.UnixNano() / int64(time.Millisecond), timestamp2.Truncate(time.Millisecond)}, 440 {timestamp1.In(tzTest), timestamp1.In(tzTest)}, 441 {timestamp1.Format("2006-01-02 15:04:05.000"), timestamp1}, 442 {timestamp1.Format("2006-01-02T15:04:05.000"), timestamp1}, 443 {timestamp1.Format("2006-01-02 15:04:05"), timestamp1}, 444 {timestamp1.Format("2006-01-02T15:04:05"), timestamp1}, 445 {timestamp2, timestamp2}, 446 {"2006-01-02 15:04:05.123456789", timestamp2}, 447 {"2006-01-02T15:04:05.123456789", timestamp2}, 448 {"2006-01-02T05:51:05.123456789-09:13", timestamp2.In(tzTest)}, 449 {"2012-11-04", timestamp3}, 450 {"2012-11-04 00:00", timestamp3}, 451 {"2012-11-04 00:00:00", timestamp3}, 452 {"2012-11-04 00:00:00.000", timestamp3}, 453 {"2012-11-04T00:00", timestamp3}, 454 {"2012-11-04T00:00:00", timestamp3}, 455 {"2012-11-04T00:00:00.000", timestamp3}, 456 {"2006-01-02T15:04:05.123456789Z", timestamp2}, 457 {"2012-11-04Z", timestamp3}, 458 {"2012-11-04 00:00Z", timestamp3}, 459 {"2012-11-04 00:00:00Z", timestamp3}, 460 {"2012-11-04 00:00:00.000Z", timestamp3}, 461 {"2012-11-04T00:00Z", timestamp3}, 462 {"2012-11-04T00:00:00Z", timestamp3}, 463 {"2012-11-04T00:00:00.000Z", timestamp3}, 464 } 465 for i := range tests { 466 _, err = db.Exec("INSERT INTO foo(id, ts, dt) VALUES(?, ?, ?)", i, tests[i].value, tests[i].value) 467 if err != nil { 468 t.Fatal("Failed to insert timestamp:", err) 469 } 470 } 471 472 rows, err := db.Query("SELECT id, ts, dt FROM foo ORDER BY id ASC") 473 if err != nil { 474 t.Fatal("Unable to query foo table:", err) 475 } 476 defer rows.Close() 477 478 seen := 0 479 for rows.Next() { 480 var id int 481 var ts, dt time.Time 482 483 if err := rows.Scan(&id, &ts, &dt); err != nil { 484 t.Error("Unable to scan results:", err) 485 continue 486 } 487 if id < 0 || id >= len(tests) { 488 t.Error("Bad row id: ", id) 489 continue 490 } 491 seen++ 492 if !tests[id].expected.Equal(ts) { 493 t.Errorf("Timestamp value for id %v (%v) should be %v, not %v", id, tests[id].value, tests[id].expected, dt) 494 } 495 if !tests[id].expected.Equal(dt) { 496 t.Errorf("Datetime value for id %v (%v) should be %v, not %v", id, tests[id].value, tests[id].expected, dt) 497 } 498 if timezone(tests[id].expected) != timezone(ts) { 499 t.Errorf("Timezone for id %v (%v) should be %v, not %v", id, tests[id].value, 500 timezone(tests[id].expected), timezone(ts)) 501 } 502 if timezone(tests[id].expected) != timezone(dt) { 503 t.Errorf("Timezone for id %v (%v) should be %v, not %v", id, tests[id].value, 504 timezone(tests[id].expected), timezone(dt)) 505 } 506 } 507 508 if seen != len(tests) { 509 t.Errorf("Expected to see %d rows", len(tests)) 510 } 511 } 512 513 func TestBoolean(t *testing.T) { 514 tempFilename := TempFilename(t) 515 defer os.Remove(tempFilename) 516 db, err := sql.Open("sqlite3", tempFilename) 517 if err != nil { 518 t.Fatal("Failed to open database:", err) 519 } 520 521 defer db.Close() 522 523 _, err = db.Exec("CREATE TABLE foo(id INTEGER, fbool BOOLEAN)") 524 if err != nil { 525 t.Fatal("Failed to create table:", err) 526 } 527 528 bool1 := true 529 _, err = db.Exec("INSERT INTO foo(id, fbool) VALUES(1, ?)", bool1) 530 if err != nil { 531 t.Fatal("Failed to insert boolean:", err) 532 } 533 534 bool2 := false 535 _, err = db.Exec("INSERT INTO foo(id, fbool) VALUES(2, ?)", bool2) 536 if err != nil { 537 t.Fatal("Failed to insert boolean:", err) 538 } 539 540 bool3 := "nonsense" 541 _, err = db.Exec("INSERT INTO foo(id, fbool) VALUES(3, ?)", bool3) 542 if err != nil { 543 t.Fatal("Failed to insert nonsense:", err) 544 } 545 546 rows, err := db.Query("SELECT id, fbool FROM foo where fbool = ?", bool1) 547 if err != nil { 548 t.Fatal("Unable to query foo table:", err) 549 } 550 counter := 0 551 552 var id int 553 var fbool bool 554 555 for rows.Next() { 556 if err := rows.Scan(&id, &fbool); err != nil { 557 t.Fatal("Unable to scan results:", err) 558 } 559 counter++ 560 } 561 562 if counter != 1 { 563 t.Fatalf("Expected 1 row but %v", counter) 564 } 565 566 if id != 1 && fbool != true { 567 t.Fatalf("Value for id 1 should be %v, not %v", bool1, fbool) 568 } 569 570 rows, err = db.Query("SELECT id, fbool FROM foo where fbool = ?", bool2) 571 if err != nil { 572 t.Fatal("Unable to query foo table:", err) 573 } 574 575 counter = 0 576 577 for rows.Next() { 578 if err := rows.Scan(&id, &fbool); err != nil { 579 t.Fatal("Unable to scan results:", err) 580 } 581 counter++ 582 } 583 584 if counter != 1 { 585 t.Fatalf("Expected 1 row but %v", counter) 586 } 587 588 if id != 2 && fbool != false { 589 t.Fatalf("Value for id 2 should be %v, not %v", bool2, fbool) 590 } 591 592 // make sure "nonsense" triggered an error 593 rows, err = db.Query("SELECT id, fbool FROM foo where id=?;", 3) 594 if err != nil { 595 t.Fatal("Unable to query foo table:", err) 596 } 597 598 rows.Next() 599 err = rows.Scan(&id, &fbool) 600 if err == nil { 601 t.Error("Expected error from \"nonsense\" bool") 602 } 603 } 604 605 func TestFloat32(t *testing.T) { 606 tempFilename := TempFilename(t) 607 defer os.Remove(tempFilename) 608 db, err := sql.Open("sqlite3", tempFilename) 609 if err != nil { 610 t.Fatal("Failed to open database:", err) 611 } 612 defer db.Close() 613 614 _, err = db.Exec("CREATE TABLE foo(id INTEGER)") 615 if err != nil { 616 t.Fatal("Failed to create table:", err) 617 } 618 619 _, err = db.Exec("INSERT INTO foo(id) VALUES(null)") 620 if err != nil { 621 t.Fatal("Failed to insert null:", err) 622 } 623 624 rows, err := db.Query("SELECT id FROM foo") 625 if err != nil { 626 t.Fatal("Unable to query foo table:", err) 627 } 628 629 if !rows.Next() { 630 t.Fatal("Unable to query results:", err) 631 } 632 633 var id interface{} 634 if err := rows.Scan(&id); err != nil { 635 t.Fatal("Unable to scan results:", err) 636 } 637 if id != nil { 638 t.Error("Expected nil but not") 639 } 640 } 641 642 func TestNull(t *testing.T) { 643 tempFilename := TempFilename(t) 644 defer os.Remove(tempFilename) 645 db, err := sql.Open("sqlite3", tempFilename) 646 if err != nil { 647 t.Fatal("Failed to open database:", err) 648 } 649 defer db.Close() 650 651 rows, err := db.Query("SELECT 3.141592") 652 if err != nil { 653 t.Fatal("Unable to query foo table:", err) 654 } 655 656 if !rows.Next() { 657 t.Fatal("Unable to query results:", err) 658 } 659 660 var v interface{} 661 if err := rows.Scan(&v); err != nil { 662 t.Fatal("Unable to scan results:", err) 663 } 664 f, ok := v.(float64) 665 if !ok { 666 t.Error("Expected float but not") 667 } 668 if f != 3.141592 { 669 t.Error("Expected 3.141592 but not") 670 } 671 } 672 673 func TestTransaction(t *testing.T) { 674 tempFilename := TempFilename(t) 675 defer os.Remove(tempFilename) 676 db, err := sql.Open("sqlite3", tempFilename) 677 if err != nil { 678 t.Fatal("Failed to open database:", err) 679 } 680 defer db.Close() 681 682 _, err = db.Exec("CREATE TABLE foo(id INTEGER)") 683 if err != nil { 684 t.Fatal("Failed to create table:", err) 685 } 686 687 tx, err := db.Begin() 688 if err != nil { 689 t.Fatal("Failed to begin transaction:", err) 690 } 691 692 _, err = tx.Exec("INSERT INTO foo(id) VALUES(1)") 693 if err != nil { 694 t.Fatal("Failed to insert null:", err) 695 } 696 697 rows, err := tx.Query("SELECT id from foo") 698 if err != nil { 699 t.Fatal("Unable to query foo table:", err) 700 } 701 702 err = tx.Rollback() 703 if err != nil { 704 t.Fatal("Failed to rollback transaction:", err) 705 } 706 707 if rows.Next() { 708 t.Fatal("Unable to query results:", err) 709 } 710 711 tx, err = db.Begin() 712 if err != nil { 713 t.Fatal("Failed to begin transaction:", err) 714 } 715 716 _, err = tx.Exec("INSERT INTO foo(id) VALUES(1)") 717 if err != nil { 718 t.Fatal("Failed to insert null:", err) 719 } 720 721 err = tx.Commit() 722 if err != nil { 723 t.Fatal("Failed to commit transaction:", err) 724 } 725 726 rows, err = tx.Query("SELECT id from foo") 727 if err == nil { 728 t.Fatal("Expected failure to query") 729 } 730 } 731 732 func TestWAL(t *testing.T) { 733 tempFilename := TempFilename(t) 734 defer os.Remove(tempFilename) 735 db, err := sql.Open("sqlite3", tempFilename) 736 if err != nil { 737 t.Fatal("Failed to open database:", err) 738 } 739 defer db.Close() 740 741 if _, err = db.Exec("PRAGMA journal_mode=WAL;"); err != nil { 742 t.Fatal("Failed to Exec PRAGMA journal_mode:", err) 743 } 744 if _, err = db.Exec("PRAGMA locking_mode=EXCLUSIVE;"); err != nil { 745 t.Fatal("Failed to Exec PRAGMA locking_mode:", err) 746 } 747 if _, err = db.Exec("CREATE TABLE test (id SERIAL, user TEXT NOT NULL, name TEXT NOT NULL);"); err != nil { 748 t.Fatal("Failed to Exec CREATE TABLE:", err) 749 } 750 if _, err = db.Exec("INSERT INTO test (user, name) VALUES ('user','name');"); err != nil { 751 t.Fatal("Failed to Exec INSERT:", err) 752 } 753 754 trans, err := db.Begin() 755 if err != nil { 756 t.Fatal("Failed to Begin:", err) 757 } 758 s, err := trans.Prepare("INSERT INTO test (user, name) VALUES (?, ?);") 759 if err != nil { 760 t.Fatal("Failed to Prepare:", err) 761 } 762 763 var count int 764 if err = trans.QueryRow("SELECT count(user) FROM test;").Scan(&count); err != nil { 765 t.Fatal("Failed to QueryRow:", err) 766 } 767 if _, err = s.Exec("bbbb", "aaaa"); err != nil { 768 t.Fatal("Failed to Exec prepared statement:", err) 769 } 770 if err = s.Close(); err != nil { 771 t.Fatal("Failed to Close prepared statement:", err) 772 } 773 if err = trans.Commit(); err != nil { 774 t.Fatal("Failed to Commit:", err) 775 } 776 } 777 778 func TestTimezoneConversion(t *testing.T) { 779 zones := []string{"UTC", "US/Central", "US/Pacific", "Local"} 780 for _, tz := range zones { 781 tempFilename := TempFilename(t) 782 defer os.Remove(tempFilename) 783 db, err := sql.Open("sqlite3", tempFilename+"?_loc="+url.QueryEscape(tz)) 784 if err != nil { 785 t.Fatal("Failed to open database:", err) 786 } 787 defer db.Close() 788 789 _, err = db.Exec("DROP TABLE foo") 790 _, err = db.Exec("CREATE TABLE foo(id INTEGER, ts TIMESTAMP, dt DATETIME)") 791 if err != nil { 792 t.Fatal("Failed to create table:", err) 793 } 794 795 loc, err := time.LoadLocation(tz) 796 if err != nil { 797 t.Fatal("Failed to load location:", err) 798 } 799 800 timestamp1 := time.Date(2012, time.April, 6, 22, 50, 0, 0, time.UTC) 801 timestamp2 := time.Date(2006, time.January, 2, 15, 4, 5, 123456789, time.UTC) 802 timestamp3 := time.Date(2012, time.November, 4, 0, 0, 0, 0, time.UTC) 803 tests := []struct { 804 value interface{} 805 expected time.Time 806 }{ 807 {"nonsense", time.Time{}.In(loc)}, 808 {"0000-00-00 00:00:00", time.Time{}.In(loc)}, 809 {timestamp1, timestamp1.In(loc)}, 810 {timestamp1.Unix(), timestamp1.In(loc)}, 811 {timestamp1.In(time.FixedZone("TEST", -7*3600)), timestamp1.In(loc)}, 812 {timestamp1.Format("2006-01-02 15:04:05.000"), timestamp1.In(loc)}, 813 {timestamp1.Format("2006-01-02T15:04:05.000"), timestamp1.In(loc)}, 814 {timestamp1.Format("2006-01-02 15:04:05"), timestamp1.In(loc)}, 815 {timestamp1.Format("2006-01-02T15:04:05"), timestamp1.In(loc)}, 816 {timestamp2, timestamp2.In(loc)}, 817 {"2006-01-02 15:04:05.123456789", timestamp2.In(loc)}, 818 {"2006-01-02T15:04:05.123456789", timestamp2.In(loc)}, 819 {"2012-11-04", timestamp3.In(loc)}, 820 {"2012-11-04 00:00", timestamp3.In(loc)}, 821 {"2012-11-04 00:00:00", timestamp3.In(loc)}, 822 {"2012-11-04 00:00:00.000", timestamp3.In(loc)}, 823 {"2012-11-04T00:00", timestamp3.In(loc)}, 824 {"2012-11-04T00:00:00", timestamp3.In(loc)}, 825 {"2012-11-04T00:00:00.000", timestamp3.In(loc)}, 826 } 827 for i := range tests { 828 _, err = db.Exec("INSERT INTO foo(id, ts, dt) VALUES(?, ?, ?)", i, tests[i].value, tests[i].value) 829 if err != nil { 830 t.Fatal("Failed to insert timestamp:", err) 831 } 832 } 833 834 rows, err := db.Query("SELECT id, ts, dt FROM foo ORDER BY id ASC") 835 if err != nil { 836 t.Fatal("Unable to query foo table:", err) 837 } 838 defer rows.Close() 839 840 seen := 0 841 for rows.Next() { 842 var id int 843 var ts, dt time.Time 844 845 if err := rows.Scan(&id, &ts, &dt); err != nil { 846 t.Error("Unable to scan results:", err) 847 continue 848 } 849 if id < 0 || id >= len(tests) { 850 t.Error("Bad row id: ", id) 851 continue 852 } 853 seen++ 854 if !tests[id].expected.Equal(ts) { 855 t.Errorf("Timestamp value for id %v (%v) should be %v, not %v", id, tests[id].value, tests[id].expected, ts) 856 } 857 if !tests[id].expected.Equal(dt) { 858 t.Errorf("Datetime value for id %v (%v) should be %v, not %v", id, tests[id].value, tests[id].expected, dt) 859 } 860 if tests[id].expected.Location().String() != ts.Location().String() { 861 t.Errorf("Location for id %v (%v) should be %v, not %v", id, tests[id].value, tests[id].expected.Location().String(), ts.Location().String()) 862 } 863 if tests[id].expected.Location().String() != dt.Location().String() { 864 t.Errorf("Location for id %v (%v) should be %v, not %v", id, tests[id].value, tests[id].expected.Location().String(), dt.Location().String()) 865 } 866 } 867 868 if seen != len(tests) { 869 t.Errorf("Expected to see %d rows", len(tests)) 870 } 871 } 872 } 873 874 // TODO: Execer & Queryer currently disabled 875 // https://github.com/mattn/go-sqlite3/issues/82 876 func TestExecer(t *testing.T) { 877 tempFilename := TempFilename(t) 878 defer os.Remove(tempFilename) 879 db, err := sql.Open("sqlite3", tempFilename) 880 if err != nil { 881 t.Fatal("Failed to open database:", err) 882 } 883 defer db.Close() 884 885 _, err = db.Exec(` 886 create table foo (id integer); -- one comment 887 insert into foo(id) values(?); 888 insert into foo(id) values(?); 889 insert into foo(id) values(?); -- another comment 890 `, 1, 2, 3) 891 if err != nil { 892 t.Error("Failed to call db.Exec:", err) 893 } 894 } 895 896 func TestQueryer(t *testing.T) { 897 tempFilename := TempFilename(t) 898 defer os.Remove(tempFilename) 899 db, err := sql.Open("sqlite3", tempFilename) 900 if err != nil { 901 t.Fatal("Failed to open database:", err) 902 } 903 defer db.Close() 904 905 _, err = db.Exec(` 906 create table foo (id integer); 907 `) 908 if err != nil { 909 t.Error("Failed to call db.Query:", err) 910 } 911 912 rows, err := db.Query(` 913 insert into foo(id) values(?); 914 insert into foo(id) values(?); 915 insert into foo(id) values(?); 916 select id from foo order by id; 917 `, 3, 2, 1) 918 if err != nil { 919 t.Error("Failed to call db.Query:", err) 920 } 921 defer rows.Close() 922 n := 1 923 if rows != nil { 924 for rows.Next() { 925 var id int 926 err = rows.Scan(&id) 927 if err != nil { 928 t.Error("Failed to db.Query:", err) 929 } 930 if id != n { 931 t.Error("Failed to db.Query: not matched results") 932 } 933 } 934 } 935 } 936 937 func TestStress(t *testing.T) { 938 tempFilename := TempFilename(t) 939 defer os.Remove(tempFilename) 940 db, err := sql.Open("sqlite3", tempFilename) 941 if err != nil { 942 t.Fatal("Failed to open database:", err) 943 } 944 db.Exec("CREATE TABLE foo (id int);") 945 db.Exec("INSERT INTO foo VALUES(1);") 946 db.Exec("INSERT INTO foo VALUES(2);") 947 db.Close() 948 949 for i := 0; i < 10000; i++ { 950 db, err := sql.Open("sqlite3", tempFilename) 951 if err != nil { 952 t.Fatal("Failed to open database:", err) 953 } 954 955 for j := 0; j < 3; j++ { 956 rows, err := db.Query("select * from foo where id=1;") 957 if err != nil { 958 t.Error("Failed to call db.Query:", err) 959 } 960 for rows.Next() { 961 var i int 962 if err := rows.Scan(&i); err != nil { 963 t.Errorf("Scan failed: %v\n", err) 964 } 965 } 966 if err := rows.Err(); err != nil { 967 t.Errorf("Post-scan failed: %v\n", err) 968 } 969 rows.Close() 970 } 971 db.Close() 972 } 973 } 974 975 func TestDateTimeLocal(t *testing.T) { 976 zone := "Asia/Tokyo" 977 tempFilename := TempFilename(t) 978 defer os.Remove(tempFilename) 979 db, err := sql.Open("sqlite3", tempFilename+"?_loc="+zone) 980 if err != nil { 981 t.Fatal("Failed to open database:", err) 982 } 983 db.Exec("CREATE TABLE foo (dt datetime);") 984 db.Exec("INSERT INTO foo VALUES('2015-03-05 15:16:17');") 985 986 row := db.QueryRow("select * from foo") 987 var d time.Time 988 err = row.Scan(&d) 989 if err != nil { 990 t.Fatal("Failed to scan datetime:", err) 991 } 992 if d.Hour() == 15 || !strings.Contains(d.String(), "JST") { 993 t.Fatal("Result should have timezone", d) 994 } 995 db.Close() 996 997 db, err = sql.Open("sqlite3", tempFilename) 998 if err != nil { 999 t.Fatal("Failed to open database:", err) 1000 } 1001 1002 row = db.QueryRow("select * from foo") 1003 err = row.Scan(&d) 1004 if err != nil { 1005 t.Fatal("Failed to scan datetime:", err) 1006 } 1007 if d.UTC().Hour() != 15 || !strings.Contains(d.String(), "UTC") { 1008 t.Fatalf("Result should not have timezone %v %v", zone, d.String()) 1009 } 1010 1011 _, err = db.Exec("DELETE FROM foo") 1012 if err != nil { 1013 t.Fatal("Failed to delete table:", err) 1014 } 1015 dt, err := time.Parse("2006/1/2 15/4/5 -0700 MST", "2015/3/5 15/16/17 +0900 JST") 1016 if err != nil { 1017 t.Fatal("Failed to parse datetime:", err) 1018 } 1019 db.Exec("INSERT INTO foo VALUES(?);", dt) 1020 1021 db.Close() 1022 db, err = sql.Open("sqlite3", tempFilename+"?_loc="+zone) 1023 if err != nil { 1024 t.Fatal("Failed to open database:", err) 1025 } 1026 1027 row = db.QueryRow("select * from foo") 1028 err = row.Scan(&d) 1029 if err != nil { 1030 t.Fatal("Failed to scan datetime:", err) 1031 } 1032 if d.Hour() != 15 || !strings.Contains(d.String(), "JST") { 1033 t.Fatalf("Result should have timezone %v %v", zone, d.String()) 1034 } 1035 } 1036 1037 func TestVersion(t *testing.T) { 1038 s, n, id := Version() 1039 if s == "" || n == 0 || id == "" { 1040 t.Errorf("Version failed %q, %d, %q\n", s, n, id) 1041 } 1042 } 1043 1044 func TestStringContainingZero(t *testing.T) { 1045 tempFilename := TempFilename(t) 1046 defer os.Remove(tempFilename) 1047 db, err := sql.Open("sqlite3", tempFilename) 1048 if err != nil { 1049 t.Fatal("Failed to open database:", err) 1050 } 1051 defer db.Close() 1052 1053 _, err = db.Exec(` 1054 create table foo (id integer, name, extra text); 1055 `) 1056 if err != nil { 1057 t.Error("Failed to call db.Query:", err) 1058 } 1059 1060 const text = "foo\x00bar" 1061 1062 _, err = db.Exec(`insert into foo(id, name, extra) values($1, $2, $2)`, 1, text) 1063 if err != nil { 1064 t.Error("Failed to call db.Exec:", err) 1065 } 1066 1067 row := db.QueryRow(`select id, extra from foo where id = $1 and extra = $2`, 1, text) 1068 if row == nil { 1069 t.Error("Failed to call db.QueryRow") 1070 } 1071 1072 var id int 1073 var extra string 1074 err = row.Scan(&id, &extra) 1075 if err != nil { 1076 t.Error("Failed to db.Scan:", err) 1077 } 1078 if id != 1 || extra != text { 1079 t.Error("Failed to db.QueryRow: not matched results") 1080 } 1081 } 1082 1083 const CurrentTimeStamp = "2006-01-02 15:04:05" 1084 1085 type TimeStamp struct{ *time.Time } 1086 1087 func (t TimeStamp) Scan(value interface{}) error { 1088 var err error 1089 switch v := value.(type) { 1090 case string: 1091 *t.Time, err = time.Parse(CurrentTimeStamp, v) 1092 case []byte: 1093 *t.Time, err = time.Parse(CurrentTimeStamp, string(v)) 1094 default: 1095 err = errors.New("invalid type for current_timestamp") 1096 } 1097 return err 1098 } 1099 1100 func (t TimeStamp) Value() (driver.Value, error) { 1101 return t.Time.Format(CurrentTimeStamp), nil 1102 } 1103 1104 func TestDateTimeNow(t *testing.T) { 1105 tempFilename := TempFilename(t) 1106 defer os.Remove(tempFilename) 1107 db, err := sql.Open("sqlite3", tempFilename) 1108 if err != nil { 1109 t.Fatal("Failed to open database:", err) 1110 } 1111 defer db.Close() 1112 1113 var d time.Time 1114 err = db.QueryRow("SELECT datetime('now')").Scan(TimeStamp{&d}) 1115 if err != nil { 1116 t.Fatal("Failed to scan datetime:", err) 1117 } 1118 } 1119 1120 func TestFunctionRegistration(t *testing.T) { 1121 addi8_16_32 := func(a int8, b int16) int32 { return int32(a) + int32(b) } 1122 addi64 := func(a, b int64) int64 { return a + b } 1123 addu8_16_32 := func(a uint8, b uint16) uint32 { return uint32(a) + uint32(b) } 1124 addu64 := func(a, b uint64) uint64 { return a + b } 1125 addiu := func(a int, b uint) int64 { return int64(a) + int64(b) } 1126 addf32_64 := func(a float32, b float64) float64 { return float64(a) + b } 1127 not := func(a bool) bool { return !a } 1128 regex := func(re, s string) (bool, error) { 1129 return regexp.MatchString(re, s) 1130 } 1131 generic := func(a interface{}) int64 { 1132 switch a.(type) { 1133 case int64: 1134 return 1 1135 case float64: 1136 return 2 1137 case []byte: 1138 return 3 1139 case string: 1140 return 4 1141 default: 1142 panic("unreachable") 1143 } 1144 } 1145 variadic := func(a, b int64, c ...int64) int64 { 1146 ret := a + b 1147 for _, d := range c { 1148 ret += d 1149 } 1150 return ret 1151 } 1152 variadicGeneric := func(a ...interface{}) int64 { 1153 return int64(len(a)) 1154 } 1155 1156 sql.Register("sqlite3_FunctionRegistration", &SQLiteDriver{ 1157 ConnectHook: func(conn *SQLiteConn) error { 1158 if err := conn.RegisterFunc("addi8_16_32", addi8_16_32, true); err != nil { 1159 return err 1160 } 1161 if err := conn.RegisterFunc("addi64", addi64, true); err != nil { 1162 return err 1163 } 1164 if err := conn.RegisterFunc("addu8_16_32", addu8_16_32, true); err != nil { 1165 return err 1166 } 1167 if err := conn.RegisterFunc("addu64", addu64, true); err != nil { 1168 return err 1169 } 1170 if err := conn.RegisterFunc("addiu", addiu, true); err != nil { 1171 return err 1172 } 1173 if err := conn.RegisterFunc("addf32_64", addf32_64, true); err != nil { 1174 return err 1175 } 1176 if err := conn.RegisterFunc("not", not, true); err != nil { 1177 return err 1178 } 1179 if err := conn.RegisterFunc("regex", regex, true); err != nil { 1180 return err 1181 } 1182 if err := conn.RegisterFunc("generic", generic, true); err != nil { 1183 return err 1184 } 1185 if err := conn.RegisterFunc("variadic", variadic, true); err != nil { 1186 return err 1187 } 1188 if err := conn.RegisterFunc("variadicGeneric", variadicGeneric, true); err != nil { 1189 return err 1190 } 1191 return nil 1192 }, 1193 }) 1194 db, err := sql.Open("sqlite3_FunctionRegistration", ":memory:") 1195 if err != nil { 1196 t.Fatal("Failed to open database:", err) 1197 } 1198 defer db.Close() 1199 1200 ops := []struct { 1201 query string 1202 expected interface{} 1203 }{ 1204 {"SELECT addi8_16_32(1,2)", int32(3)}, 1205 {"SELECT addi64(1,2)", int64(3)}, 1206 {"SELECT addu8_16_32(1,2)", uint32(3)}, 1207 {"SELECT addu64(1,2)", uint64(3)}, 1208 {"SELECT addiu(1,2)", int64(3)}, 1209 {"SELECT addf32_64(1.5,1.5)", float64(3)}, 1210 {"SELECT not(1)", false}, 1211 {"SELECT not(0)", true}, 1212 {`SELECT regex("^foo.*", "foobar")`, true}, 1213 {`SELECT regex("^foo.*", "barfoobar")`, false}, 1214 {"SELECT generic(1)", int64(1)}, 1215 {"SELECT generic(1.1)", int64(2)}, 1216 {`SELECT generic(NULL)`, int64(3)}, 1217 {`SELECT generic("foo")`, int64(4)}, 1218 {"SELECT variadic(1,2)", int64(3)}, 1219 {"SELECT variadic(1,2,3,4)", int64(10)}, 1220 {"SELECT variadic(1,1,1,1,1,1,1,1,1,1)", int64(10)}, 1221 {`SELECT variadicGeneric(1,"foo",2.3, NULL)`, int64(4)}, 1222 } 1223 1224 for _, op := range ops { 1225 ret := reflect.New(reflect.TypeOf(op.expected)) 1226 err = db.QueryRow(op.query).Scan(ret.Interface()) 1227 if err != nil { 1228 t.Errorf("Query %q failed: %s", op.query, err) 1229 } else if !reflect.DeepEqual(ret.Elem().Interface(), op.expected) { 1230 t.Errorf("Query %q returned wrong value: got %v (%T), want %v (%T)", op.query, ret.Elem().Interface(), ret.Elem().Interface(), op.expected, op.expected) 1231 } 1232 } 1233 } 1234 1235 type sumAggregator int64 1236 1237 func (s *sumAggregator) Step(x int64) { 1238 *s += sumAggregator(x) 1239 } 1240 1241 func (s *sumAggregator) Done() int64 { 1242 return int64(*s) 1243 } 1244 1245 func TestAggregatorRegistration(t *testing.T) { 1246 customSum := func() *sumAggregator { 1247 var ret sumAggregator 1248 return &ret 1249 } 1250 1251 sql.Register("sqlite3_AggregatorRegistration", &SQLiteDriver{ 1252 ConnectHook: func(conn *SQLiteConn) error { 1253 if err := conn.RegisterAggregator("customSum", customSum, true); err != nil { 1254 return err 1255 } 1256 return nil 1257 }, 1258 }) 1259 db, err := sql.Open("sqlite3_AggregatorRegistration", ":memory:") 1260 if err != nil { 1261 t.Fatal("Failed to open database:", err) 1262 } 1263 defer db.Close() 1264 1265 _, err = db.Exec("create table foo (department integer, profits integer)") 1266 if err != nil { 1267 // trace feature is not implemented 1268 t.Skip("Failed to create table:", err) 1269 } 1270 1271 _, err = db.Exec("insert into foo values (1, 10), (1, 20), (2, 42)") 1272 if err != nil { 1273 t.Fatal("Failed to insert records:", err) 1274 } 1275 1276 tests := []struct { 1277 dept, sum int64 1278 }{ 1279 {1, 30}, 1280 {2, 42}, 1281 } 1282 1283 for _, test := range tests { 1284 var ret int64 1285 err = db.QueryRow("select customSum(profits) from foo where department = $1 group by department", test.dept).Scan(&ret) 1286 if err != nil { 1287 t.Fatal("Query failed:", err) 1288 } 1289 if ret != test.sum { 1290 t.Fatalf("Custom sum returned wrong value, got %d, want %d", ret, test.sum) 1291 } 1292 } 1293 } 1294 1295 func rot13(r rune) rune { 1296 switch { 1297 case r >= 'A' && r <= 'Z': 1298 return 'A' + (r-'A'+13)%26 1299 case r >= 'a' && r <= 'z': 1300 return 'a' + (r-'a'+13)%26 1301 } 1302 return r 1303 } 1304 1305 func TestCollationRegistration(t *testing.T) { 1306 collateRot13 := func(a, b string) int { 1307 ra, rb := strings.Map(rot13, a), strings.Map(rot13, b) 1308 return strings.Compare(ra, rb) 1309 } 1310 collateRot13Reverse := func(a, b string) int { 1311 return collateRot13(b, a) 1312 } 1313 1314 sql.Register("sqlite3_CollationRegistration", &SQLiteDriver{ 1315 ConnectHook: func(conn *SQLiteConn) error { 1316 if err := conn.RegisterCollation("rot13", collateRot13); err != nil { 1317 return err 1318 } 1319 if err := conn.RegisterCollation("rot13reverse", collateRot13Reverse); err != nil { 1320 return err 1321 } 1322 return nil 1323 }, 1324 }) 1325 1326 db, err := sql.Open("sqlite3_CollationRegistration", ":memory:") 1327 if err != nil { 1328 t.Fatal("Failed to open database:", err) 1329 } 1330 defer db.Close() 1331 1332 populate := []string{ 1333 `CREATE TABLE test (s TEXT)`, 1334 `INSERT INTO test VALUES ("aaaa")`, 1335 `INSERT INTO test VALUES ("ffff")`, 1336 `INSERT INTO test VALUES ("qqqq")`, 1337 `INSERT INTO test VALUES ("tttt")`, 1338 `INSERT INTO test VALUES ("zzzz")`, 1339 } 1340 for _, stmt := range populate { 1341 if _, err := db.Exec(stmt); err != nil { 1342 t.Fatal("Failed to populate test DB:", err) 1343 } 1344 } 1345 1346 ops := []struct { 1347 query string 1348 want []string 1349 }{ 1350 { 1351 "SELECT * FROM test ORDER BY s COLLATE rot13 ASC", 1352 []string{ 1353 "qqqq", 1354 "tttt", 1355 "zzzz", 1356 "aaaa", 1357 "ffff", 1358 }, 1359 }, 1360 { 1361 "SELECT * FROM test ORDER BY s COLLATE rot13 DESC", 1362 []string{ 1363 "ffff", 1364 "aaaa", 1365 "zzzz", 1366 "tttt", 1367 "qqqq", 1368 }, 1369 }, 1370 { 1371 "SELECT * FROM test ORDER BY s COLLATE rot13reverse ASC", 1372 []string{ 1373 "ffff", 1374 "aaaa", 1375 "zzzz", 1376 "tttt", 1377 "qqqq", 1378 }, 1379 }, 1380 { 1381 "SELECT * FROM test ORDER BY s COLLATE rot13reverse DESC", 1382 []string{ 1383 "qqqq", 1384 "tttt", 1385 "zzzz", 1386 "aaaa", 1387 "ffff", 1388 }, 1389 }, 1390 } 1391 1392 for _, op := range ops { 1393 rows, err := db.Query(op.query) 1394 if err != nil { 1395 t.Fatalf("Query %q failed: %s", op.query, err) 1396 } 1397 got := []string{} 1398 defer rows.Close() 1399 for rows.Next() { 1400 var s string 1401 if err = rows.Scan(&s); err != nil { 1402 t.Fatalf("Reading row for %q: %s", op.query, err) 1403 } 1404 got = append(got, s) 1405 } 1406 if err = rows.Err(); err != nil { 1407 t.Fatalf("Reading rows for %q: %s", op.query, err) 1408 } 1409 1410 if !reflect.DeepEqual(got, op.want) { 1411 t.Fatalf("Unexpected output from %q\ngot:\n%s\n\nwant:\n%s", op.query, strings.Join(got, "\n"), strings.Join(op.want, "\n")) 1412 } 1413 } 1414 } 1415 1416 func TestDeclTypes(t *testing.T) { 1417 1418 d := SQLiteDriver{} 1419 1420 conn, err := d.Open(":memory:") 1421 if err != nil { 1422 t.Fatal("Failed to begin transaction:", err) 1423 } 1424 defer conn.Close() 1425 1426 sqlite3conn := conn.(*SQLiteConn) 1427 1428 _, err = sqlite3conn.Exec("create table foo (id integer not null primary key, name text)", nil) 1429 if err != nil { 1430 t.Fatal("Failed to create table:", err) 1431 } 1432 1433 _, err = sqlite3conn.Exec("insert into foo(name) values(\"bar\")", nil) 1434 if err != nil { 1435 t.Fatal("Failed to insert:", err) 1436 } 1437 1438 rs, err := sqlite3conn.Query("select * from foo", nil) 1439 if err != nil { 1440 t.Fatal("Failed to select:", err) 1441 } 1442 defer rs.Close() 1443 1444 declTypes := rs.(*SQLiteRows).DeclTypes() 1445 1446 if !reflect.DeepEqual(declTypes, []string{"integer", "text"}) { 1447 t.Fatal("Unexpected declTypes:", declTypes) 1448 } 1449 } 1450 1451 func TestPinger(t *testing.T) { 1452 db, err := sql.Open("sqlite3", ":memory:") 1453 if err != nil { 1454 t.Fatal(err) 1455 } 1456 err = db.Ping() 1457 if err != nil { 1458 t.Fatal(err) 1459 } 1460 db.Close() 1461 err = db.Ping() 1462 if err == nil { 1463 t.Fatal("Should be closed") 1464 } 1465 } 1466 1467 func TestUpdateAndTransactionHooks(t *testing.T) { 1468 var events []string 1469 var commitHookReturn = 0 1470 1471 sql.Register("sqlite3_UpdateHook", &SQLiteDriver{ 1472 ConnectHook: func(conn *SQLiteConn) error { 1473 conn.RegisterCommitHook(func() int { 1474 events = append(events, "commit") 1475 return commitHookReturn 1476 }) 1477 conn.RegisterRollbackHook(func() { 1478 events = append(events, "rollback") 1479 }) 1480 conn.RegisterUpdateHook(func(op int, db string, table string, rowid int64) { 1481 events = append(events, fmt.Sprintf("update(op=%v db=%v table=%v rowid=%v)", op, db, table, rowid)) 1482 }) 1483 return nil 1484 }, 1485 }) 1486 db, err := sql.Open("sqlite3_UpdateHook", ":memory:") 1487 if err != nil { 1488 t.Fatal("Failed to open database:", err) 1489 } 1490 defer db.Close() 1491 1492 statements := []string{ 1493 "create table foo (id integer primary key)", 1494 "insert into foo values (9)", 1495 "update foo set id = 99 where id = 9", 1496 "delete from foo where id = 99", 1497 } 1498 for _, statement := range statements { 1499 _, err = db.Exec(statement) 1500 if err != nil { 1501 t.Fatalf("Unable to prepare test data [%v]: %v", statement, err) 1502 } 1503 } 1504 1505 commitHookReturn = 1 1506 _, err = db.Exec("insert into foo values (5)") 1507 if err == nil { 1508 t.Error("Commit hook failed to rollback transaction") 1509 } 1510 1511 var expected = []string{ 1512 "commit", 1513 fmt.Sprintf("update(op=%v db=main table=foo rowid=9)", SQLITE_INSERT), 1514 "commit", 1515 fmt.Sprintf("update(op=%v db=main table=foo rowid=99)", SQLITE_UPDATE), 1516 "commit", 1517 fmt.Sprintf("update(op=%v db=main table=foo rowid=99)", SQLITE_DELETE), 1518 "commit", 1519 fmt.Sprintf("update(op=%v db=main table=foo rowid=5)", SQLITE_INSERT), 1520 "commit", 1521 "rollback", 1522 } 1523 if !reflect.DeepEqual(events, expected) { 1524 t.Errorf("Expected notifications %v but got %v", expected, events) 1525 } 1526 } 1527 1528 func TestNilAndEmptyBytes(t *testing.T) { 1529 db, err := sql.Open("sqlite3", ":memory:") 1530 if err != nil { 1531 t.Fatal(err) 1532 } 1533 defer db.Close() 1534 actualNil := []byte("use this to use an actual nil not a reference to nil") 1535 emptyBytes := []byte{} 1536 for tsti, tst := range []struct { 1537 name string 1538 columnType string 1539 insertBytes []byte 1540 expectedBytes []byte 1541 }{ 1542 {"actual nil blob", "blob", actualNil, nil}, 1543 {"referenced nil blob", "blob", nil, nil}, 1544 {"empty blob", "blob", emptyBytes, emptyBytes}, 1545 {"actual nil text", "text", actualNil, nil}, 1546 {"referenced nil text", "text", nil, nil}, 1547 {"empty text", "text", emptyBytes, emptyBytes}, 1548 } { 1549 if _, err = db.Exec(fmt.Sprintf("create table tbl%d (txt %s)", tsti, tst.columnType)); err != nil { 1550 t.Fatal(tst.name, err) 1551 } 1552 if bytes.Equal(tst.insertBytes, actualNil) { 1553 if _, err = db.Exec(fmt.Sprintf("insert into tbl%d (txt) values (?)", tsti), nil); err != nil { 1554 t.Fatal(tst.name, err) 1555 } 1556 } else { 1557 if _, err = db.Exec(fmt.Sprintf("insert into tbl%d (txt) values (?)", tsti), &tst.insertBytes); err != nil { 1558 t.Fatal(tst.name, err) 1559 } 1560 } 1561 rows, err := db.Query(fmt.Sprintf("select txt from tbl%d", tsti)) 1562 if err != nil { 1563 t.Fatal(tst.name, err) 1564 } 1565 if !rows.Next() { 1566 t.Fatal(tst.name, "no rows") 1567 } 1568 var scanBytes []byte 1569 if err = rows.Scan(&scanBytes); err != nil { 1570 t.Fatal(tst.name, err) 1571 } 1572 if err = rows.Err(); err != nil { 1573 t.Fatal(tst.name, err) 1574 } 1575 if tst.expectedBytes == nil && scanBytes != nil { 1576 t.Errorf("%s: %#v != %#v", tst.name, scanBytes, tst.expectedBytes) 1577 } else if !bytes.Equal(scanBytes, tst.expectedBytes) { 1578 t.Errorf("%s: %#v != %#v", tst.name, scanBytes, tst.expectedBytes) 1579 } 1580 } 1581 } 1582 1583 var customFunctionOnce sync.Once 1584 1585 func BenchmarkCustomFunctions(b *testing.B) { 1586 customFunctionOnce.Do(func() { 1587 customAdd := func(a, b int64) int64 { 1588 return a + b 1589 } 1590 1591 sql.Register("sqlite3_BenchmarkCustomFunctions", &SQLiteDriver{ 1592 ConnectHook: func(conn *SQLiteConn) error { 1593 // Impure function to force sqlite to reexecute it each time. 1594 if err := conn.RegisterFunc("custom_add", customAdd, false); err != nil { 1595 return err 1596 } 1597 return nil 1598 }, 1599 }) 1600 }) 1601 1602 db, err := sql.Open("sqlite3_BenchmarkCustomFunctions", ":memory:") 1603 if err != nil { 1604 b.Fatal("Failed to open database:", err) 1605 } 1606 defer db.Close() 1607 1608 b.ResetTimer() 1609 for i := 0; i < b.N; i++ { 1610 var i int64 1611 err = db.QueryRow("SELECT custom_add(1,2)").Scan(&i) 1612 if err != nil { 1613 b.Fatal("Failed to run custom add:", err) 1614 } 1615 } 1616 } 1617 1618 func TestSuite(t *testing.T) { 1619 tempFilename := TempFilename(t) 1620 defer os.Remove(tempFilename) 1621 d, err := sql.Open("sqlite3", tempFilename+"?_busy_timeout=99999") 1622 if err != nil { 1623 t.Fatal(err) 1624 } 1625 defer d.Close() 1626 1627 db = &TestDB{t, d, SQLITE, sync.Once{}} 1628 testing.RunTests(func(string, string) (bool, error) { return true, nil }, tests) 1629 1630 if !testing.Short() { 1631 for _, b := range benchmarks { 1632 fmt.Printf("%-20s", b.Name) 1633 r := testing.Benchmark(b.F) 1634 fmt.Printf("%10d %10.0f req/s\n", r.N, float64(r.N)/r.T.Seconds()) 1635 } 1636 } 1637 db.tearDown() 1638 } 1639 1640 // Dialect is a type of dialect of databases. 1641 type Dialect int 1642 1643 // Dialects for databases. 1644 const ( 1645 SQLITE Dialect = iota // SQLITE mean SQLite3 dialect 1646 POSTGRESQL // POSTGRESQL mean PostgreSQL dialect 1647 MYSQL // MYSQL mean MySQL dialect 1648 ) 1649 1650 // DB provide context for the tests 1651 type TestDB struct { 1652 *testing.T 1653 *sql.DB 1654 dialect Dialect 1655 once sync.Once 1656 } 1657 1658 var db *TestDB 1659 1660 // the following tables will be created and dropped during the test 1661 var testTables = []string{"foo", "bar", "t", "bench"} 1662 1663 var tests = []testing.InternalTest{ 1664 {Name: "TestResult", F: testResult}, 1665 {Name: "TestBlobs", F: testBlobs}, 1666 {Name: "TestManyQueryRow", F: testManyQueryRow}, 1667 {Name: "TestTxQuery", F: testTxQuery}, 1668 {Name: "TestPreparedStmt", F: testPreparedStmt}, 1669 } 1670 1671 var benchmarks = []testing.InternalBenchmark{ 1672 {Name: "BenchmarkExec", F: benchmarkExec}, 1673 {Name: "BenchmarkQuery", F: benchmarkQuery}, 1674 {Name: "BenchmarkParams", F: benchmarkParams}, 1675 {Name: "BenchmarkStmt", F: benchmarkStmt}, 1676 {Name: "BenchmarkRows", F: benchmarkRows}, 1677 {Name: "BenchmarkStmtRows", F: benchmarkStmtRows}, 1678 } 1679 1680 func (db *TestDB) mustExec(sql string, args ...interface{}) sql.Result { 1681 res, err := db.Exec(sql, args...) 1682 if err != nil { 1683 db.Fatalf("Error running %q: %v", sql, err) 1684 } 1685 return res 1686 } 1687 1688 func (db *TestDB) tearDown() { 1689 for _, tbl := range testTables { 1690 switch db.dialect { 1691 case SQLITE: 1692 db.mustExec("drop table if exists " + tbl) 1693 case MYSQL, POSTGRESQL: 1694 db.mustExec("drop table if exists " + tbl) 1695 default: 1696 db.Fatal("unknown dialect") 1697 } 1698 } 1699 } 1700 1701 // q replaces ? parameters if needed 1702 func (db *TestDB) q(sql string) string { 1703 switch db.dialect { 1704 case POSTGRESQL: // repace with $1, $2, .. 1705 qrx := regexp.MustCompile(`\?`) 1706 n := 0 1707 return qrx.ReplaceAllStringFunc(sql, func(string) string { 1708 n++ 1709 return "$" + strconv.Itoa(n) 1710 }) 1711 } 1712 return sql 1713 } 1714 1715 func (db *TestDB) blobType(size int) string { 1716 switch db.dialect { 1717 case SQLITE: 1718 return fmt.Sprintf("blob[%d]", size) 1719 case POSTGRESQL: 1720 return "bytea" 1721 case MYSQL: 1722 return fmt.Sprintf("VARBINARY(%d)", size) 1723 } 1724 panic("unknown dialect") 1725 } 1726 1727 func (db *TestDB) serialPK() string { 1728 switch db.dialect { 1729 case SQLITE: 1730 return "integer primary key autoincrement" 1731 case POSTGRESQL: 1732 return "serial primary key" 1733 case MYSQL: 1734 return "integer primary key auto_increment" 1735 } 1736 panic("unknown dialect") 1737 } 1738 1739 func (db *TestDB) now() string { 1740 switch db.dialect { 1741 case SQLITE: 1742 return "datetime('now')" 1743 case POSTGRESQL: 1744 return "now()" 1745 case MYSQL: 1746 return "now()" 1747 } 1748 panic("unknown dialect") 1749 } 1750 1751 func makeBench() { 1752 if _, err := db.Exec("create table bench (n varchar(32), i integer, d double, s varchar(32), t datetime)"); err != nil { 1753 panic(err) 1754 } 1755 st, err := db.Prepare("insert into bench values (?, ?, ?, ?, ?)") 1756 if err != nil { 1757 panic(err) 1758 } 1759 defer st.Close() 1760 for i := 0; i < 100; i++ { 1761 if _, err = st.Exec(nil, i, float64(i), fmt.Sprintf("%d", i), time.Now()); err != nil { 1762 panic(err) 1763 } 1764 } 1765 } 1766 1767 // testResult is test for result 1768 func testResult(t *testing.T) { 1769 db.tearDown() 1770 db.mustExec("create temporary table test (id " + db.serialPK() + ", name varchar(10))") 1771 1772 for i := 1; i < 3; i++ { 1773 r := db.mustExec(db.q("insert into test (name) values (?)"), fmt.Sprintf("row %d", i)) 1774 n, err := r.RowsAffected() 1775 if err != nil { 1776 t.Fatal(err) 1777 } 1778 if n != 1 { 1779 t.Errorf("got %v, want %v", n, 1) 1780 } 1781 n, err = r.LastInsertId() 1782 if err != nil { 1783 t.Fatal(err) 1784 } 1785 if n != int64(i) { 1786 t.Errorf("got %v, want %v", n, i) 1787 } 1788 } 1789 if _, err := db.Exec("error!"); err == nil { 1790 t.Fatalf("expected error") 1791 } 1792 } 1793 1794 // testBlobs is test for blobs 1795 func testBlobs(t *testing.T) { 1796 db.tearDown() 1797 var blob = []byte{0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15} 1798 db.mustExec("create table foo (id integer primary key, bar " + db.blobType(16) + ")") 1799 db.mustExec(db.q("insert into foo (id, bar) values(?,?)"), 0, blob) 1800 1801 want := fmt.Sprintf("%x", blob) 1802 1803 b := make([]byte, 16) 1804 err := db.QueryRow(db.q("select bar from foo where id = ?"), 0).Scan(&b) 1805 got := fmt.Sprintf("%x", b) 1806 if err != nil { 1807 t.Errorf("[]byte scan: %v", err) 1808 } else if got != want { 1809 t.Errorf("for []byte, got %q; want %q", got, want) 1810 } 1811 1812 err = db.QueryRow(db.q("select bar from foo where id = ?"), 0).Scan(&got) 1813 want = string(blob) 1814 if err != nil { 1815 t.Errorf("string scan: %v", err) 1816 } else if got != want { 1817 t.Errorf("for string, got %q; want %q", got, want) 1818 } 1819 } 1820 1821 // testManyQueryRow is test for many query row 1822 func testManyQueryRow(t *testing.T) { 1823 if testing.Short() { 1824 t.Log("skipping in short mode") 1825 return 1826 } 1827 db.tearDown() 1828 db.mustExec("create table foo (id integer primary key, name varchar(50))") 1829 db.mustExec(db.q("insert into foo (id, name) values(?,?)"), 1, "bob") 1830 var name string 1831 for i := 0; i < 10000; i++ { 1832 err := db.QueryRow(db.q("select name from foo where id = ?"), 1).Scan(&name) 1833 if err != nil || name != "bob" { 1834 t.Fatalf("on query %d: err=%v, name=%q", i, err, name) 1835 } 1836 } 1837 } 1838 1839 // testTxQuery is test for transactional query 1840 func testTxQuery(t *testing.T) { 1841 db.tearDown() 1842 tx, err := db.Begin() 1843 if err != nil { 1844 t.Fatal(err) 1845 } 1846 defer tx.Rollback() 1847 1848 _, err = tx.Exec("create table foo (id integer primary key, name varchar(50))") 1849 if err != nil { 1850 t.Fatal(err) 1851 } 1852 1853 _, err = tx.Exec(db.q("insert into foo (id, name) values(?,?)"), 1, "bob") 1854 if err != nil { 1855 t.Fatal(err) 1856 } 1857 1858 r, err := tx.Query(db.q("select name from foo where id = ?"), 1) 1859 if err != nil { 1860 t.Fatal(err) 1861 } 1862 defer r.Close() 1863 1864 if !r.Next() { 1865 if r.Err() != nil { 1866 t.Fatal(err) 1867 } 1868 t.Fatal("expected one rows") 1869 } 1870 1871 var name string 1872 err = r.Scan(&name) 1873 if err != nil { 1874 t.Fatal(err) 1875 } 1876 } 1877 1878 // testPreparedStmt is test for prepared statement 1879 func testPreparedStmt(t *testing.T) { 1880 db.tearDown() 1881 db.mustExec("CREATE TABLE t (count INT)") 1882 sel, err := db.Prepare("SELECT count FROM t ORDER BY count DESC") 1883 if err != nil { 1884 t.Fatalf("prepare 1: %v", err) 1885 } 1886 ins, err := db.Prepare(db.q("INSERT INTO t (count) VALUES (?)")) 1887 if err != nil { 1888 t.Fatalf("prepare 2: %v", err) 1889 } 1890 1891 for n := 1; n <= 3; n++ { 1892 if _, err := ins.Exec(n); err != nil { 1893 t.Fatalf("insert(%d) = %v", n, err) 1894 } 1895 } 1896 1897 const nRuns = 10 1898 var wg sync.WaitGroup 1899 for i := 0; i < nRuns; i++ { 1900 wg.Add(1) 1901 go func() { 1902 defer wg.Done() 1903 for j := 0; j < 10; j++ { 1904 count := 0 1905 if err := sel.QueryRow().Scan(&count); err != nil && err != sql.ErrNoRows { 1906 t.Errorf("Query: %v", err) 1907 return 1908 } 1909 if _, err := ins.Exec(rand.Intn(100)); err != nil { 1910 t.Errorf("Insert: %v", err) 1911 return 1912 } 1913 } 1914 }() 1915 } 1916 wg.Wait() 1917 } 1918 1919 // Benchmarks need to use panic() since b.Error errors are lost when 1920 // running via testing.Benchmark() I would like to run these via go 1921 // test -bench but calling Benchmark() from a benchmark test 1922 // currently hangs go. 1923 1924 // benchmarkExec is benchmark for exec 1925 func benchmarkExec(b *testing.B) { 1926 for i := 0; i < b.N; i++ { 1927 if _, err := db.Exec("select 1"); err != nil { 1928 panic(err) 1929 } 1930 } 1931 } 1932 1933 // benchmarkQuery is benchmark for query 1934 func benchmarkQuery(b *testing.B) { 1935 for i := 0; i < b.N; i++ { 1936 var n sql.NullString 1937 var i int 1938 var f float64 1939 var s string 1940 // var t time.Time 1941 if err := db.QueryRow("select null, 1, 1.1, 'foo'").Scan(&n, &i, &f, &s); err != nil { 1942 panic(err) 1943 } 1944 } 1945 } 1946 1947 // benchmarkParams is benchmark for params 1948 func benchmarkParams(b *testing.B) { 1949 for i := 0; i < b.N; i++ { 1950 var n sql.NullString 1951 var i int 1952 var f float64 1953 var s string 1954 // var t time.Time 1955 if err := db.QueryRow("select ?, ?, ?, ?", nil, 1, 1.1, "foo").Scan(&n, &i, &f, &s); err != nil { 1956 panic(err) 1957 } 1958 } 1959 } 1960 1961 // benchmarkStmt is benchmark for statement 1962 func benchmarkStmt(b *testing.B) { 1963 st, err := db.Prepare("select ?, ?, ?, ?") 1964 if err != nil { 1965 panic(err) 1966 } 1967 defer st.Close() 1968 1969 for n := 0; n < b.N; n++ { 1970 var n sql.NullString 1971 var i int 1972 var f float64 1973 var s string 1974 // var t time.Time 1975 if err := st.QueryRow(nil, 1, 1.1, "foo").Scan(&n, &i, &f, &s); err != nil { 1976 panic(err) 1977 } 1978 } 1979 } 1980 1981 // benchmarkRows is benchmark for rows 1982 func benchmarkRows(b *testing.B) { 1983 db.once.Do(makeBench) 1984 1985 for n := 0; n < b.N; n++ { 1986 var n sql.NullString 1987 var i int 1988 var f float64 1989 var s string 1990 var t time.Time 1991 r, err := db.Query("select * from bench") 1992 if err != nil { 1993 panic(err) 1994 } 1995 for r.Next() { 1996 if err = r.Scan(&n, &i, &f, &s, &t); err != nil { 1997 panic(err) 1998 } 1999 } 2000 if err = r.Err(); err != nil { 2001 panic(err) 2002 } 2003 } 2004 } 2005 2006 // benchmarkStmtRows is benchmark for statement rows 2007 func benchmarkStmtRows(b *testing.B) { 2008 db.once.Do(makeBench) 2009 2010 st, err := db.Prepare("select * from bench") 2011 if err != nil { 2012 panic(err) 2013 } 2014 defer st.Close() 2015 2016 for n := 0; n < b.N; n++ { 2017 var n sql.NullString 2018 var i int 2019 var f float64 2020 var s string 2021 var t time.Time 2022 r, err := st.Query() 2023 if err != nil { 2024 panic(err) 2025 } 2026 for r.Next() { 2027 if err = r.Scan(&n, &i, &f, &s, &t); err != nil { 2028 panic(err) 2029 } 2030 } 2031 if err = r.Err(); err != nil { 2032 panic(err) 2033 } 2034 } 2035 }