github.com/fanyang01/ql@v1.1.1-0.20170406094456-9cac60d33e9d/all_test.go (about) 1 // Copyright 2014 The ql 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 ql 6 7 import ( 8 "bytes" 9 "crypto/md5" 10 "database/sql" 11 "fmt" 12 "io" 13 "io/ioutil" 14 "log" 15 "math/big" 16 "math/rand" 17 "os" 18 "path" 19 "path/filepath" 20 "runtime" 21 "runtime/debug" 22 "strconv" 23 "strings" 24 "sync" 25 "testing" 26 "time" 27 28 "github.com/cznic/strutil" 29 ) 30 31 // Note: All benchmarks report MB/s equal to record/s. 32 const benchScale = 1e6 33 34 func init() { 35 log.SetFlags(log.Flags() | log.Lshortfile) 36 isTesting = true 37 use(dieHard, caller, dumpTables2, dumpTables3, dumpFields, dumpFlds, dumpCols, typeof, stypeof) 38 } 39 40 func dieHard(exitValue int) { 41 debug.PrintStack() 42 os.Exit(exitValue) 43 } 44 45 func dbg(s string, va ...interface{}) { 46 if s == "" { 47 s = strings.Repeat("%v ", len(va)) 48 } 49 _, fn, fl, _ := runtime.Caller(1) 50 fmt.Printf("dbg %s:%d: ", path.Base(fn), fl) 51 fmt.Printf(s, va...) 52 fmt.Println() 53 } 54 55 func caller(s string, va ...interface{}) { 56 if s == "" { 57 s = strings.Repeat("%v ", len(va)) 58 } 59 _, fn, fl, _ := runtime.Caller(2) 60 fmt.Printf("caller: %s:%d: ", path.Base(fn), fl) 61 fmt.Printf(s, va...) 62 fmt.Println() 63 _, fn, fl, _ = runtime.Caller(1) 64 fmt.Printf("\tcallee: %s:%d: ", path.Base(fn), fl) 65 fmt.Println() 66 } 67 68 func use(...interface{}) {} 69 70 func dumpTables3(r *root) { 71 fmt.Printf("---- r.head %d, r.thead %p\n", r.head, r.thead) 72 for k, v := range r.tables { 73 fmt.Printf("%p: %s->%+v\n", v, k, v) 74 } 75 fmt.Println("<exit>") 76 } 77 78 func dumpTables2(s storage) { 79 fmt.Println("****") 80 h := int64(1) 81 for h != 0 { 82 d, err := s.Read(nil, h) 83 if err != nil { 84 log.Fatal(err) 85 } 86 87 fmt.Printf("%d: %v\n", h, d) 88 h = d[0].(int64) 89 } 90 fmt.Println("<exit>") 91 } 92 93 func (db *DB) dumpTables() string { 94 var buf bytes.Buffer 95 for k, v := range db.root.tables { 96 buf.WriteString(fmt.Sprintf("%s->%v, %v\n", k, v.h, v.next)) 97 } 98 return buf.String() 99 } 100 101 func fldsString(f []*fld) string { 102 a := []string{} 103 for _, v := range f { 104 a = append(a, v.name) 105 } 106 return strings.Join(a, " ") 107 } 108 109 type testDB interface { 110 setup() (db *DB, err error) 111 mark() (err error) 112 teardown(ctx *TCtx) (err error) 113 } 114 115 var ( 116 _ testDB = (*fileTestDB)(nil) 117 _ testDB = (*memTestDB)(nil) 118 ) 119 120 type memTestDB struct { 121 db *DB 122 m0 int64 123 } 124 125 func (m *memTestDB) setup() (db *DB, err error) { 126 if m.db, err = OpenMem(); err != nil { 127 return 128 } 129 130 return m.db, nil 131 } 132 133 func (m *memTestDB) mark() (err error) { 134 m.m0, err = m.db.store.Verify() 135 if err != nil { 136 m.m0 = -1 137 } 138 return 139 } 140 141 func (m *memTestDB) teardown(ctx *TCtx) (err error) { 142 if m.m0 < 0 { 143 return 144 } 145 146 n, err := m.db.store.Verify() 147 if err != nil { 148 return 149 } 150 151 if g, e := n, m.m0; g != e { 152 return fmt.Errorf("STORAGE LEAK: allocs: got %d, exp %d", g, e) 153 } 154 155 if ctx == nil { 156 return nil 157 } 158 159 _, _, err = m.db.Execute(ctx, txCommit) 160 return err 161 } 162 163 type fileTestDB struct { 164 db *DB 165 gmp0 int 166 m0 int64 167 } 168 169 func (m *fileTestDB) setup() (db *DB, err error) { 170 m.gmp0 = runtime.GOMAXPROCS(0) 171 f, err := ioutil.TempFile("", "ql-test-") 172 if err != nil { 173 return 174 } 175 176 if m.db, err = OpenFile(f.Name(), &Options{}); err != nil { 177 return 178 } 179 180 return m.db, nil 181 } 182 183 func (m *fileTestDB) mark() (err error) { 184 m.m0, err = m.db.store.Verify() 185 if err != nil { 186 m.m0 = -1 187 } 188 return 189 } 190 191 func (m *fileTestDB) teardown(ctx *TCtx) (err error) { 192 runtime.GOMAXPROCS(m.gmp0) 193 defer func() { 194 f := m.db.store.(*file) 195 errSet(&err, m.db.Close()) 196 os.Remove(f.f0.Name()) 197 if f.wal != nil { 198 os.Remove(f.wal.Name()) 199 } 200 }() 201 202 if m.m0 < 0 { 203 return 204 } 205 206 n, err := m.db.store.Verify() 207 if err != nil { 208 return 209 } 210 211 if g, e := n, m.m0; g != e { 212 return fmt.Errorf("STORAGE LEAK: allocs: got %d, exp %d", g, e) 213 } 214 215 if ctx == nil { 216 return nil 217 } 218 219 _, _, err = m.db.Execute(ctx, txCommit) 220 return err 221 } 222 223 type osFileTestDB struct { 224 db *DB 225 gmp0 int 226 m0 int64 227 } 228 229 func (m *osFileTestDB) setup() (db *DB, err error) { 230 m.gmp0 = runtime.GOMAXPROCS(0) 231 f, err := ioutil.TempFile("", "ql-test-osfile") 232 if err != nil { 233 return 234 } 235 236 if m.db, err = OpenFile("", &Options{OSFile: f}); err != nil { 237 return 238 } 239 240 return m.db, nil 241 } 242 243 func (m *osFileTestDB) mark() (err error) { 244 m.m0, err = m.db.store.Verify() 245 if err != nil { 246 m.m0 = -1 247 } 248 return 249 } 250 251 func (m *osFileTestDB) teardown(ctx *TCtx) (err error) { 252 runtime.GOMAXPROCS(m.gmp0) 253 defer func() { 254 f := m.db.store.(*file) 255 errSet(&err, m.db.Close()) 256 os.Remove(f.f0.Name()) 257 if f.wal != nil { 258 os.Remove(f.wal.Name()) 259 } 260 }() 261 262 if m.m0 < 0 { 263 return 264 } 265 266 n, err := m.db.store.Verify() 267 if err != nil { 268 return 269 } 270 271 if g, e := n, m.m0; g != e { 272 return fmt.Errorf("STORAGE LEAK: allocs: got %d, exp %d", g, e) 273 } 274 275 if ctx == nil { 276 return nil 277 } 278 279 _, _, err = m.db.Execute(ctx, txCommit) 280 return err 281 } 282 283 func TestMemStorage(t *testing.T) { 284 test(t, &memTestDB{}) 285 } 286 287 func TestFileStorage(t *testing.T) { 288 if testing.Short() { 289 t.Skip("skipping test in short mode.") 290 } 291 292 test(t, &fileTestDB{}) 293 } 294 295 func TestOSFileStorage(t *testing.T) { 296 if testing.Short() { 297 t.Skip("skipping test in short mode.") 298 } 299 300 test(t, &osFileTestDB{}) 301 } 302 303 var ( 304 compiledCommit = MustCompile("COMMIT;") 305 compiledCreate = MustCompile("BEGIN TRANSACTION; CREATE TABLE t (i16 int16, s16 string, s string);") 306 compiledCreate2 = MustCompile("BEGIN TRANSACTION; CREATE TABLE t (i16 int16, s16 string, s string); COMMIT;") 307 compiledIns = MustCompile("INSERT INTO t VALUES($1, $2, $3);") 308 compiledSelect = MustCompile("SELECT * FROM t;") 309 compiledSelectOrderBy = MustCompile("SELECT * FROM t ORDER BY i16, s16;") 310 compiledTrunc = MustCompile("BEGIN TRANSACTION; TRUNCATE TABLE t; COMMIT;") 311 ) 312 313 func rnds16(rng *rand.Rand, n int) string { 314 a := make([]string, n) 315 for i := range a { 316 a[i] = fmt.Sprintf("%016x", rng.Int63()) 317 } 318 return strings.Join(a, "") 319 } 320 321 var ( 322 benchmarkScaleOnce sync.Once 323 benchmarkSelectOnce = map[string]bool{} 324 ) 325 326 func benchProlog(b *testing.B) { 327 benchmarkScaleOnce.Do(func() { 328 b.Logf(` 329 ============================================================= 330 NOTE: All benchmarks report records/s as %d bytes/s. 331 =============================================================`, int64(benchScale)) 332 }) 333 } 334 335 func benchmarkSelect(b *testing.B, n int, sel List, ts testDB) { 336 if testing.Verbose() { 337 benchProlog(b) 338 id := fmt.Sprintf("%T|%d", ts, n) 339 if !benchmarkSelectOnce[id] { 340 b.Logf(`Having a table of %d records, each of size 1kB, measure the performance of 341 %s 342 `, n, sel) 343 } 344 benchmarkSelectOnce[id] = true 345 } 346 347 db, err := ts.setup() 348 if err != nil { 349 b.Error(err) 350 return 351 } 352 353 defer ts.teardown(nil) 354 355 ctx := NewRWCtx() 356 if _, i, err := db.Execute(ctx, compiledCreate); err != nil { 357 b.Error(i, err) 358 return 359 } 360 361 rng := rand.New(rand.NewSource(42)) 362 for i := 0; i < n; i++ { 363 if _, _, err := db.Execute(ctx, compiledIns, int16(rng.Int()), rnds16(rng, 1), rnds16(rng, 63)); err != nil { 364 b.Error(err) 365 return 366 } 367 } 368 369 if _, i, err := db.Execute(ctx, compiledCommit); err != nil { 370 b.Error(i, err) 371 return 372 } 373 374 b.SetBytes(int64(n) * benchScale) 375 runtime.GC() 376 b.ResetTimer() 377 for i := 0; i < b.N; i++ { 378 rs, index, err := db.Execute(nil, sel) 379 if err != nil { 380 b.Error(index, err) 381 return 382 } 383 384 if err = rs[0].Do(false, func(record []interface{}) (bool, error) { return true, nil }); err != nil { 385 b.Errorf("%v %T(%#v)", err, err, err) 386 return 387 } 388 } 389 b.StopTimer() 390 391 } 392 393 func BenchmarkSelectMem1kBx1e2(b *testing.B) { 394 benchmarkSelect(b, 1e2, compiledSelect, &memTestDB{}) 395 } 396 397 func BenchmarkSelectMem1kBx1e3(b *testing.B) { 398 benchmarkSelect(b, 1e3, compiledSelect, &memTestDB{}) 399 } 400 401 func BenchmarkSelectMem1kBx1e4(b *testing.B) { 402 benchmarkSelect(b, 1e4, compiledSelect, &memTestDB{}) 403 } 404 405 func BenchmarkSelectMem1kBx1e5(b *testing.B) { 406 benchmarkSelect(b, 1e5, compiledSelect, &memTestDB{}) 407 } 408 409 func BenchmarkSelectFile1kBx1e2(b *testing.B) { 410 benchmarkSelect(b, 1e2, compiledSelect, &fileTestDB{}) 411 } 412 413 func BenchmarkSelectFile1kBx1e3(b *testing.B) { 414 benchmarkSelect(b, 1e3, compiledSelect, &fileTestDB{}) 415 } 416 417 func BenchmarkSelectFile1kBx1e4(b *testing.B) { 418 benchmarkSelect(b, 1e4, compiledSelect, &fileTestDB{}) 419 } 420 421 func BenchmarkSelectFile1kBx1e5(b *testing.B) { 422 benchmarkSelect(b, 1e5, compiledSelect, &fileTestDB{}) 423 } 424 425 func BenchmarkSelectOrderedMem1kBx1e2(b *testing.B) { 426 benchmarkSelect(b, 1e2, compiledSelectOrderBy, &memTestDB{}) 427 } 428 429 func BenchmarkSelectOrderedMem1kBx1e3(b *testing.B) { 430 benchmarkSelect(b, 1e3, compiledSelectOrderBy, &memTestDB{}) 431 } 432 433 func BenchmarkSelectOrderedMem1kBx1e4(b *testing.B) { 434 benchmarkSelect(b, 1e4, compiledSelectOrderBy, &memTestDB{}) 435 } 436 437 func BenchmarkSelectOrderedFile1kBx1e2(b *testing.B) { 438 benchmarkSelect(b, 1e2, compiledSelectOrderBy, &fileTestDB{}) 439 } 440 441 func BenchmarkSelectOrderedFile1kBx1e3(b *testing.B) { 442 benchmarkSelect(b, 1e3, compiledSelectOrderBy, &fileTestDB{}) 443 } 444 445 func BenchmarkSelectOrderedFile1kBx1e4(b *testing.B) { 446 benchmarkSelect(b, 1e4, compiledSelectOrderBy, &fileTestDB{}) 447 } 448 449 func TestString(t *testing.T) { 450 for _, v := range testdata { 451 a := strings.Split(v, "\n|") 452 v = a[0] 453 v = strings.Replace(v, "∨", "|", -1) 454 v = strings.Replace(v, "⩖", "||", -1) 455 l, err := Compile(v) 456 if err != nil { 457 continue 458 } 459 460 if s := l.String(); len(s) == 0 { 461 t.Fatal("List.String() returned empty string") 462 } 463 } 464 } 465 466 var benchmarkInsertOnce = map[string]bool{} 467 468 func benchmarkInsert(b *testing.B, batch, total int, ts testDB) { 469 if testing.Verbose() { 470 benchProlog(b) 471 id := fmt.Sprintf("%T|%d|%d", ts, batch, total) 472 if !benchmarkInsertOnce[id] { 473 b.Logf(`In batches of %d record(s), insert a total of %d records, each of size 1kB, into a table. 474 475 `, batch, total) 476 } 477 benchmarkInsertOnce[id] = true 478 } 479 480 if total%batch != 0 { 481 b.Fatal("internal error 001") 482 } 483 484 db, err := ts.setup() 485 if err != nil { 486 b.Error(err) 487 return 488 } 489 490 defer ts.teardown(nil) 491 492 ctx := NewRWCtx() 493 if _, i, err := db.Execute(ctx, compiledCreate2); err != nil { 494 b.Error(i, err) 495 return 496 } 497 498 s := fmt.Sprintf(`(0, "0123456789abcdef", "%s"),`, rnds16(rand.New(rand.NewSource(42)), 63)) 499 var buf bytes.Buffer 500 buf.WriteString("BEGIN TRANSACTION; INSERT INTO t VALUES\n") 501 for i := 0; i < batch; i++ { 502 buf.WriteString(s) 503 } 504 buf.WriteString("; COMMIT;") 505 ins, err := Compile(buf.String()) 506 if err != nil { 507 b.Error(err) 508 return 509 } 510 511 b.SetBytes(int64(total) * benchScale) 512 runtime.GC() 513 b.ResetTimer() 514 for i := 0; i < b.N; i++ { 515 for n := 0; n != total; n += batch { 516 if _, _, err = db.Execute(ctx, ins); err != nil { 517 b.Error(err) 518 return 519 } 520 } 521 b.StopTimer() 522 if _, _, err = db.Execute(ctx, compiledTrunc); err != nil { 523 b.Error(err) 524 } 525 b.StartTimer() 526 } 527 b.StopTimer() 528 } 529 530 func BenchmarkInsertMem1kBn1e0t1e2(b *testing.B) { 531 benchmarkInsert(b, 1e0, 1e2, &memTestDB{}) 532 } 533 534 func BenchmarkInsertFile1kBn1e0t1e2(b *testing.B) { 535 benchmarkInsert(b, 1e0, 1e2, &fileTestDB{}) 536 } 537 538 //============================================================================= 539 540 func BenchmarkInsertMem1kBn1e1t1e2(b *testing.B) { 541 benchmarkInsert(b, 1e1, 1e2, &memTestDB{}) 542 } 543 544 func BenchmarkInsertFile1kBn1e1t1e2(b *testing.B) { 545 benchmarkInsert(b, 1e1, 1e2, &fileTestDB{}) 546 } 547 548 func BenchmarkInsertMem1kBn1e1t1e3(b *testing.B) { 549 benchmarkInsert(b, 1e1, 1e3, &memTestDB{}) 550 } 551 552 func BenchmarkInsertFile1kBn1e1t1e3(b *testing.B) { 553 benchmarkInsert(b, 1e1, 1e3, &fileTestDB{}) 554 } 555 556 //============================================================================= 557 558 func BenchmarkInsertMem1kBn1e2t1e2(b *testing.B) { 559 benchmarkInsert(b, 1e2, 1e2, &memTestDB{}) 560 } 561 562 func BenchmarkInsertFile1kBn1e2t1e2(b *testing.B) { 563 benchmarkInsert(b, 1e2, 1e2, &fileTestDB{}) 564 } 565 566 func BenchmarkInsertMem1kBn1e2t1e3(b *testing.B) { 567 benchmarkInsert(b, 1e2, 1e3, &memTestDB{}) 568 } 569 570 func BenchmarkInsertFile1kBn1e2t1e3(b *testing.B) { 571 benchmarkInsert(b, 1e2, 1e3, &fileTestDB{}) 572 } 573 574 func BenchmarkInsertMem1kBn1e2t1e4(b *testing.B) { 575 benchmarkInsert(b, 1e2, 1e4, &memTestDB{}) 576 } 577 578 func BenchmarkInsertFile1kBn1e2t1e4(b *testing.B) { 579 benchmarkInsert(b, 1e2, 1e4, &fileTestDB{}) 580 } 581 582 //============================================================================= 583 584 func BenchmarkInsertMem1kBn1e3t1e3(b *testing.B) { 585 benchmarkInsert(b, 1e3, 1e3, &memTestDB{}) 586 } 587 588 func BenchmarkInsertFile1kBn1e3t1e3(b *testing.B) { 589 benchmarkInsert(b, 1e3, 1e3, &fileTestDB{}) 590 } 591 592 func BenchmarkInsertMem1kBn1e3t1e4(b *testing.B) { 593 benchmarkInsert(b, 1e3, 1e4, &memTestDB{}) 594 } 595 596 func BenchmarkInsertFile1kBn1e3t1e4(b *testing.B) { 597 benchmarkInsert(b, 1e3, 1e4, &fileTestDB{}) 598 } 599 600 func BenchmarkInsertMem1kBn1e3t1e5(b *testing.B) { 601 benchmarkInsert(b, 1e3, 1e5, &memTestDB{}) 602 } 603 604 func BenchmarkInsertFile1kBn1e3t1e5(b *testing.B) { 605 benchmarkInsert(b, 1e3, 1e5, &fileTestDB{}) 606 } 607 608 func TestReopen(t *testing.T) { 609 if testing.Short() { 610 t.Skip("skipping test in short mode.") 611 } 612 613 f, err := ioutil.TempFile("", "ql-test-") 614 if err != nil { 615 t.Fatal(err) 616 } 617 618 nm := f.Name() 619 if err = f.Close(); err != nil { 620 t.Fatal(err) 621 } 622 623 defer func() { 624 if nm != "" { 625 os.Remove(nm) 626 } 627 }() 628 629 db, err := OpenFile(nm, &Options{}) 630 if err != nil { 631 t.Error(err) 632 return 633 } 634 635 for _, tn := range "abc" { 636 ql := fmt.Sprintf(` 637 BEGIN TRANSACTION; 638 CREATE TABLE %c (i int, s string); 639 INSERT INTO %c VALUES (%d, "<%c>"); 640 COMMIT; 641 `, tn, tn, tn-'a'+42, tn) 642 _, i, err := db.Run(NewRWCtx(), ql) 643 if err != nil { 644 db.Close() 645 t.Error(i, err) 646 return 647 } 648 } 649 650 if err = db.Close(); err != nil { 651 t.Error(err) 652 return 653 } 654 655 db, err = OpenFile(nm, &Options{}) 656 if err != nil { 657 t.Error(err) 658 return 659 } 660 661 defer func() { 662 if err = db.Close(); err != nil { 663 t.Error(err) 664 } 665 }() 666 667 if _, _, err = db.Run(NewRWCtx(), "BEGIN TRANSACTION; DROP TABLE b; COMMIT;"); err != nil { 668 t.Error(err) 669 return 670 } 671 672 for _, tn := range "ac" { 673 ql := fmt.Sprintf("SELECT * FROM %c;", tn) 674 rs, i, err := db.Run(NewRWCtx(), ql) 675 if err != nil { 676 t.Error(i, err) 677 return 678 } 679 680 if rs == nil { 681 t.Error(rs) 682 return 683 } 684 685 rid := 0 686 if err = rs[0].Do(false, func(r []interface{}) (bool, error) { 687 rid++ 688 if rid != 1 { 689 return false, fmt.Errorf("rid %d", rid) 690 } 691 692 if g, e := recStr(r), fmt.Sprintf(`%d, "<%c>"`, tn-'a'+42, tn); g != e { 693 return false, fmt.Errorf("g `%s`, e `%s`", g, e) 694 } 695 696 return true, nil 697 }); err != nil { 698 t.Error(err) 699 return 700 } 701 } 702 } 703 704 func recStr(data []interface{}) string { 705 a := make([]string, len(data)) 706 for i, v := range data { 707 switch x := v.(type) { 708 case string: 709 a[i] = fmt.Sprintf("%q", x) 710 default: 711 a[i] = fmt.Sprint(x) 712 } 713 } 714 return strings.Join(a, ", ") 715 } 716 717 //TODO +test long blob types with multiple inner chunks. 718 719 func TestLastInsertID(t *testing.T) { 720 table := []struct { 721 ql string 722 id int 723 }{ 724 // 0 725 {`BEGIN TRANSACTION; CREATE TABLE t (c int); COMMIT`, 0}, 726 {`BEGIN TRANSACTION; INSERT INTO t VALUES (41); COMMIT`, 1}, 727 {`BEGIN TRANSACTION; INSERT INTO t VALUES (42);`, 2}, 728 {`INSERT INTO t VALUES (43)`, 3}, 729 {`ROLLBACK; BEGIN TRANSACTION; INSERT INTO t VALUES (44); COMMIT`, 4}, 730 731 //5 732 {`BEGIN TRANSACTION; INSERT INTO t VALUES (45); COMMIT`, 5}, 733 {` 734 BEGIN TRANSACTION; 735 INSERT INTO t VALUES (46); // 6 736 BEGIN TRANSACTION; 737 INSERT INTO t VALUES (47); // 7 738 INSERT INTO t VALUES (48); // 8 739 ROLLBACK; 740 INSERT INTO t VALUES (49); // 9 741 COMMIT`, 9}, 742 {` 743 BEGIN TRANSACTION; 744 INSERT INTO t VALUES (50); // 10 745 BEGIN TRANSACTION; 746 INSERT INTO t VALUES (51); // 11 747 INSERT INTO t VALUES (52); // 12 748 ROLLBACK; 749 COMMIT;`, 10}, 750 {`BEGIN TRANSACTION; INSERT INTO t VALUES (53); ROLLBACK`, 10}, 751 {`BEGIN TRANSACTION; INSERT INTO t VALUES (54); COMMIT`, 14}, 752 // 10 753 {`BEGIN TRANSACTION; CREATE TABLE u (c int); COMMIT`, 14}, 754 {` 755 BEGIN TRANSACTION; 756 INSERT INTO t SELECT * FROM u; 757 COMMIT;`, 14}, 758 {`BEGIN TRANSACTION; INSERT INTO u VALUES (150); COMMIT`, 15}, 759 {` 760 BEGIN TRANSACTION; 761 INSERT INTO t SELECT * FROM u; 762 COMMIT;`, 16}, 763 } 764 765 db, err := OpenMem() 766 if err != nil { 767 t.Fatal(err) 768 } 769 770 ctx := NewRWCtx() 771 for i, test := range table { 772 l, err := Compile(test.ql) 773 if err != nil { 774 t.Fatal(i, err) 775 } 776 777 if _, _, err = db.Execute(ctx, l); err != nil { 778 t.Fatal(i, err) 779 } 780 781 if g, e := ctx.LastInsertID, int64(test.id); g != e { 782 t.Fatal(i, g, e) 783 } 784 } 785 } 786 787 func ExampleTCtx_lastInsertID() { 788 ins := MustCompile("BEGIN TRANSACTION; INSERT INTO t VALUES ($1); COMMIT;") 789 790 db, err := OpenMem() 791 if err != nil { 792 panic(err) 793 } 794 795 ctx := NewRWCtx() 796 if _, _, err = db.Run(ctx, ` 797 BEGIN TRANSACTION; 798 CREATE TABLE t (c int); 799 INSERT INTO t VALUES (1), (2), (3); 800 COMMIT; 801 `); err != nil { 802 panic(err) 803 } 804 805 if _, _, err = db.Execute(ctx, ins, int64(42)); err != nil { 806 panic(err) 807 } 808 809 id := ctx.LastInsertID 810 rs, _, err := db.Run(ctx, `SELECT * FROM t WHERE id() == $1`, id) 811 if err != nil { 812 panic(err) 813 } 814 815 if err = rs[0].Do(false, func(data []interface{}) (more bool, err error) { 816 fmt.Println(data) 817 return true, nil 818 }); err != nil { 819 panic(err) 820 } 821 // Output: 822 // [42] 823 } 824 825 func Example_recordsetFields() { 826 // See RecordSet.Fields documentation 827 828 db, err := OpenMem() 829 if err != nil { 830 panic(err) 831 } 832 833 rs, _, err := db.Run(NewRWCtx(), ` 834 BEGIN TRANSACTION; 835 CREATE TABLE t (s string, i int); 836 CREATE TABLE u (s string, i int); 837 INSERT INTO t VALUES 838 ("a", 1), 839 ("a", 2), 840 ("b", 3), 841 ("b", 4), 842 ; 843 INSERT INTO u VALUES 844 ("A", 10), 845 ("A", 20), 846 ("B", 30), 847 ("B", 40), 848 ; 849 COMMIT; 850 851 SELECT t.s+u.s as a, t.i+u.i as b, "noName", "name" as Named FROM t, u; 852 853 SELECT DISTINCT s as S, sum(i) as I FROM ( 854 SELECT t.s+u.s as s, t.i+u.i, 3 as i FROM t, u; 855 ) 856 GROUP BY s 857 ORDER BY I; 858 859 SELECT DISTINCT * FROM ( 860 SELECT t.s+u.s as S, t.i+u.i, 3 as I FROM t, u; 861 ) 862 WHERE I < $1 863 ORDER BY S; 864 `, 42) 865 if err != nil { 866 panic(err) 867 } 868 869 for i, v := range rs { 870 fields, err := v.Fields() 871 switch { 872 case err != nil: 873 fmt.Printf("Fields[%d]: error: %s\n", i, err) 874 default: 875 fmt.Printf("Fields[%d]: %#v\n", i, fields) 876 } 877 } 878 // Output: 879 // Fields[0]: []string{"a", "b", "", "Named"} 880 // Fields[1]: []string{"S", "I"} 881 // Fields[2]: []string{"S", "", "I"} 882 } 883 884 func TestRowsAffected(t *testing.T) { 885 db, err := OpenMem() 886 if err != nil { 887 t.Fatal(err) 888 } 889 890 ctx := NewRWCtx() 891 ctx.LastInsertID, ctx.RowsAffected = -1, -1 892 if _, _, err = db.Run(ctx, ` 893 BEGIN TRANSACTION; 894 CREATE TABLE t (i int); 895 COMMIT; 896 `); err != nil { 897 t.Fatal(err) 898 } 899 900 if g, e := ctx.LastInsertID, int64(0); g != e { 901 t.Fatal(g, e) 902 } 903 904 if g, e := ctx.RowsAffected, int64(0); g != e { 905 t.Fatal(g, e) 906 } 907 908 if _, _, err = db.Run(ctx, ` 909 BEGIN TRANSACTION; 910 INSERT INTO t VALUES (1), (2), (3); 911 COMMIT; 912 `); err != nil { 913 t.Fatal(err) 914 } 915 916 if g, e := ctx.LastInsertID, int64(3); g != e { 917 t.Fatal(g, e) 918 } 919 920 if g, e := ctx.RowsAffected, int64(3); g != e { 921 t.Fatal(g, e) 922 } 923 924 if _, _, err = db.Run(ctx, ` 925 BEGIN TRANSACTION; 926 INSERT INTO t 927 SELECT * FROM t WHERE i == 2; 928 COMMIT; 929 `); err != nil { 930 t.Fatal(err) 931 } 932 933 if g, e := ctx.LastInsertID, int64(4); g != e { 934 t.Fatal(g, e) 935 } 936 937 if g, e := ctx.RowsAffected, int64(1); g != e { 938 t.Fatal(g, e) 939 } 940 941 if _, _, err = db.Run(ctx, ` 942 BEGIN TRANSACTION; 943 DELETE FROM t WHERE i == 2; 944 COMMIT; 945 `); err != nil { 946 t.Fatal(err) 947 } 948 949 if g, e := ctx.LastInsertID, int64(4); g != e { 950 t.Fatal(g, e) 951 } 952 953 if g, e := ctx.RowsAffected, int64(2); g != e { 954 t.Fatal(g, e) 955 } 956 957 if _, _, err = db.Run(ctx, ` 958 BEGIN TRANSACTION; 959 UPDATE t i = 42 WHERE i == 3; 960 COMMIT; 961 `); err != nil { 962 t.Fatal(err) 963 } 964 965 if g, e := ctx.LastInsertID, int64(4); g != e { 966 t.Fatal(g, e) 967 } 968 969 if g, e := ctx.RowsAffected, int64(1); g != e { 970 t.Fatal(g, e) 971 } 972 } 973 974 func dumpDB(db *DB, tag string) (string, error) { 975 var buf bytes.Buffer 976 f := strutil.IndentFormatter(&buf, "\t") 977 f.Format("---- %s%i\n", tag) 978 for nm, tab := range db.root.tables { 979 hh := tab.hhead 980 h := tab.head 981 f.Format("%u%q: hhead %d, head %d, scols0 %q, scols %q%i\n", nm, hh, h, cols2meta(tab.cols0), cols2meta(tab.cols)) 982 for h != 0 { 983 rec, err := db.store.Read(nil, h, tab.cols...) 984 if err != nil { 985 return "", err 986 } 987 988 f.Format("record @%d: %v\n", h, rec) 989 h = rec[0].(int64) 990 } 991 f.Format("%u") 992 for i, v := range tab.indices { 993 if v == nil { 994 continue 995 } 996 997 xname := v.name 998 cname := "id()" 999 if i != 0 { 1000 cname = tab.cols0[i-1].name 1001 } 1002 f.Format("index %s on %s%i\n", xname, cname) 1003 it, _, err := v.x.Seek([]interface{}{nil}) 1004 if err != nil { 1005 return "", err 1006 } 1007 1008 for { 1009 k, v, err := it.Next() 1010 if err != nil { 1011 if err == io.EOF { 1012 break 1013 } 1014 1015 return "", err 1016 } 1017 1018 f.Format("%v: %v\n", k, v) 1019 } 1020 f.Format("%u") 1021 } 1022 } 1023 1024 return buf.String(), nil 1025 } 1026 1027 func testIndices(db *DB, t *testing.T) { 1028 ctx := NewRWCtx() 1029 var err error 1030 e := func(q string) { 1031 s0, err := dumpDB(db, "pre\n\t"+q) 1032 if err != nil { 1033 t.Log(s0) 1034 t.Fatal(err) 1035 } 1036 1037 if _, _, err = db.Run(ctx, q); err != nil { 1038 t.Log(q) 1039 t.Fatal(err) 1040 } 1041 1042 s, err := dumpDB(db, "post\n\t"+q) 1043 if err != nil { 1044 t.Log(s0) 1045 t.Log(s) 1046 t.Fatal(err) 1047 } 1048 1049 if db.isMem { 1050 return 1051 } 1052 1053 nm := db.Name() 1054 1055 if err = db.Close(); err != nil { 1056 t.Log(s0) 1057 t.Log(s) 1058 t.Fatal(err) 1059 } 1060 1061 if db, err = OpenFile(nm, &Options{}); err != nil { 1062 t.Log(s0) 1063 t.Log(s) 1064 t.Fatal(err) 1065 } 1066 1067 if s, err = dumpDB(db, "reopened"); err != nil { 1068 t.Log(s0) 1069 t.Log(s) 1070 t.Fatal(err) 1071 } 1072 1073 } 1074 1075 e(` BEGIN TRANSACTION; 1076 CREATE TABLE t (i int); 1077 COMMIT;`) 1078 e(` BEGIN TRANSACTION; 1079 CREATE TABLE IF NOT EXISTS Index2 (TableName string); 1080 CREATE INDEX x ON t (id()); 1081 COMMIT;`) 1082 e(` BEGIN TRANSACTION; 1083 INSERT INTO t VALUES(42); 1084 COMMIT;`) 1085 e(` BEGIN TRANSACTION; 1086 INSERT INTO t VALUES(24); 1087 COMMIT;`) 1088 e(` BEGIN TRANSACTION; 1089 CREATE INDEX xi ON t (i); 1090 COMMIT;`) 1091 e(` BEGIN TRANSACTION; 1092 INSERT INTO t VALUES(1); 1093 COMMIT;`) 1094 e(` BEGIN TRANSACTION; 1095 INSERT INTO t VALUES(999); 1096 COMMIT;`) 1097 e(` BEGIN TRANSACTION; 1098 UPDATE t i = 240 WHERE i == 24; 1099 COMMIT;`) 1100 e(` BEGIN TRANSACTION; 1101 DELETE FROM t WHERE i == 240; 1102 COMMIT;`) 1103 e(` BEGIN TRANSACTION; 1104 TRUNCATE TABLE t; 1105 COMMIT;`) 1106 e(` BEGIN TRANSACTION; 1107 DROP TABLE IF EXISTS t; 1108 CREATE TABLE t (i int, s string); 1109 CREATE INDEX xi ON t (i); 1110 INSERT INTO t VALUES (42, "foo"); 1111 COMMIT;`) 1112 e(` BEGIN TRANSACTION; 1113 ALTER TABLE t DROP COLUMN i; 1114 COMMIT;`) 1115 1116 e(` BEGIN TRANSACTION; 1117 DROP TABLE IF EXISTS t; 1118 CREATE TABLE t (i int); 1119 CREATE INDEX x ON t (i); 1120 INSERT INTO t VALUES (42); 1121 INSERT INTO t SELECT 10*i FROM t; 1122 COMMIT;`) 1123 e(` BEGIN TRANSACTION; 1124 DROP TABLE IF EXISTS t; 1125 CREATE TABLE t (i int); 1126 CREATE INDEX x ON t (i); 1127 INSERT INTO t VALUES (42); 1128 COMMIT; 1129 BEGIN TRANSACTION; 1130 INSERT INTO t SELECT 10*i FROM t; 1131 COMMIT;`) 1132 e(` BEGIN TRANSACTION; 1133 DROP TABLE IF EXISTS t; 1134 CREATE TABLE t (i int); 1135 CREATE INDEX x ON t (i); 1136 INSERT INTO t VALUES (42); 1137 DROP INDEX x; 1138 COMMIT;`) 1139 e(` BEGIN TRANSACTION; 1140 DROP TABLE IF EXISTS t; 1141 CREATE TABLE t (i int); 1142 CREATE INDEX x ON t (i); 1143 INSERT INTO t VALUES (42); 1144 COMMIT; 1145 BEGIN TRANSACTION; 1146 DROP INDEX x; 1147 COMMIT;`) 1148 e(` BEGIN TRANSACTION; 1149 DROP TABLE IF EXISTS t; 1150 CREATE TABLE t (i int); 1151 CREATE INDEX x ON t (i); 1152 INSERT INTO t VALUES (42); 1153 COMMIT;`) 1154 e(` 1155 BEGIN TRANSACTION; 1156 DROP INDEX x; 1157 COMMIT;`) 1158 e(` BEGIN TRANSACTION; 1159 DROP TABLE IF EXISTS t; 1160 CREATE TABLE t (i int); 1161 CREATE INDEX x ON t (i); 1162 ALTER TABLE t ADD s string; 1163 COMMIT;`) 1164 e(` BEGIN TRANSACTION; 1165 DROP TABLE IF EXISTS t; 1166 CREATE TABLE t (i int); 1167 CREATE INDEX x ON t (i); 1168 COMMIT;`) 1169 e(` BEGIN TRANSACTION; 1170 ALTER TABLE t ADD s string; 1171 COMMIT;`) 1172 e(` BEGIN TRANSACTION; 1173 DROP TABLE IF EXISTS t; 1174 CREATE TABLE t (i bigint); 1175 CREATE INDEX x ON t (i); 1176 INSERT INTO t VALUES(42); 1177 COMMIT;`) 1178 e(` BEGIN TRANSACTION; 1179 DROP TABLE IF EXISTS t; 1180 CREATE TABLE t (i int); 1181 CREATE INDEX x ON t (i+1, 2*i); // Non simple index. 1182 COMMIT;`) 1183 e(` BEGIN TRANSACTION; 1184 DROP INDEX x; 1185 COMMIT;`) 1186 1187 if err = db.Close(); err != nil { 1188 t.Fatal(err) 1189 } 1190 } 1191 1192 func TestIndices(t *testing.T) { 1193 db, err := OpenMem() 1194 if err != nil { 1195 t.Fatal(err) 1196 } 1197 1198 testIndices(db, t) 1199 if testing.Short() { 1200 t.Skip("skipping test in short mode.") 1201 } 1202 1203 dir, err := ioutil.TempDir("", "ql-test") 1204 1205 if err != nil { 1206 t.Fatal(err) 1207 } 1208 1209 defer func() { 1210 os.RemoveAll(dir) 1211 1212 }() 1213 1214 nm := filepath.Join(dir, "ql.db") 1215 db, err = OpenFile(nm, &Options{CanCreate: true}) 1216 if err != nil { 1217 t.Fatal(err) 1218 } 1219 1220 testIndices(db, t) 1221 } 1222 1223 var benchmarkInsertBoolOnce = map[string]bool{} 1224 1225 func benchmarkInsertBool(b *testing.B, db *DB, size int, selectivity float64, index bool, teardown func()) { 1226 if testing.Verbose() { 1227 benchProlog(b) 1228 id := fmt.Sprintf("%t|%d|%g|%t", db.isMem, size, selectivity, index) 1229 if !benchmarkInsertBoolOnce[id] { 1230 s := "INDEXED" 1231 if !index { 1232 s = "NON " + s 1233 } 1234 b.Logf(`Insert %d records into a table having a single bool %s column. Batch size: 1 record. 1235 1236 `, size, s) 1237 } 1238 benchmarkInsertBoolOnce[id] = true 1239 } 1240 1241 if teardown != nil { 1242 defer teardown() 1243 } 1244 1245 ctx := NewRWCtx() 1246 if _, _, err := db.Run(ctx, ` 1247 BEGIN TRANSACTION; 1248 CREATE TABLE t (b bool); 1249 `); err != nil { 1250 b.Fatal(err) 1251 } 1252 1253 if index { 1254 if _, _, err := db.Run(ctx, ` 1255 CREATE INDEX x ON t (b); 1256 `); err != nil { 1257 b.Fatal(err) 1258 } 1259 } 1260 1261 ins, err := Compile("INSERT INTO t VALUES($1);") 1262 if err != nil { 1263 b.Fatal(err) 1264 } 1265 1266 trunc, err := Compile("TRUNCATE TABLE t;") 1267 if err != nil { 1268 b.Fatal(err) 1269 } 1270 1271 begin, err := Compile("BEGIN TRANSACTION;") 1272 if err != nil { 1273 b.Fatal(err) 1274 } 1275 1276 commit, err := Compile("COMMIT;") 1277 if err != nil { 1278 b.Fatal(err) 1279 } 1280 1281 rng := rand.New(rand.NewSource(42)) 1282 debug.FreeOSMemory() 1283 b.ResetTimer() 1284 for i := 0; i < b.N; i++ { 1285 b.StopTimer() 1286 if i != 0 { 1287 if _, _, err = db.Execute(ctx, begin); err != nil { 1288 b.Fatal(err) 1289 } 1290 } 1291 1292 if _, _, err = db.Execute(ctx, trunc); err != nil { 1293 b.Fatal(err) 1294 } 1295 1296 b.StartTimer() 1297 for j := 0; j < size; j++ { 1298 if _, _, err = db.Execute(ctx, ins, rng.Float64() < selectivity); err != nil { 1299 b.Fatal(err) 1300 } 1301 } 1302 if _, _, err = db.Execute(ctx, commit); err != nil { 1303 b.Fatal(err) 1304 } 1305 } 1306 b.StopTimer() 1307 b.SetBytes(int64(size) * benchScale) 1308 } 1309 1310 func benchmarkInsertBoolMem(b *testing.B, size int, sel float64, index bool) { 1311 db, err := OpenMem() 1312 if err != nil { 1313 b.Fatal(err) 1314 } 1315 1316 benchmarkInsertBool(b, db, size, sel, index, nil) 1317 } 1318 1319 func BenchmarkInsertBoolMemNoX1e1(b *testing.B) { 1320 benchmarkInsertBoolMem(b, 1e1, 0.5, false) 1321 } 1322 1323 func BenchmarkInsertBoolMemX1e1(b *testing.B) { 1324 benchmarkInsertBoolMem(b, 1e1, 0.5, true) 1325 } 1326 1327 func BenchmarkInsertBoolMemNoX1e2(b *testing.B) { 1328 benchmarkInsertBoolMem(b, 1e2, 0.5, false) 1329 } 1330 1331 func BenchmarkInsertBoolMemX1e2(b *testing.B) { 1332 benchmarkInsertBoolMem(b, 1e2, 0.5, true) 1333 } 1334 1335 func BenchmarkInsertBoolMemNoX1e3(b *testing.B) { 1336 benchmarkInsertBoolMem(b, 1e3, 0.5, false) 1337 } 1338 1339 func BenchmarkInsertBoolMemX1e3(b *testing.B) { 1340 benchmarkInsertBoolMem(b, 1e3, 0.5, true) 1341 } 1342 1343 func BenchmarkInsertBoolMemNoX1e4(b *testing.B) { 1344 benchmarkInsertBoolMem(b, 1e4, 0.5, false) 1345 } 1346 1347 func BenchmarkInsertBoolMemX1e4(b *testing.B) { 1348 benchmarkInsertBoolMem(b, 1e4, 0.5, true) 1349 } 1350 1351 func BenchmarkInsertBoolMemNoX1e5(b *testing.B) { 1352 benchmarkInsertBoolMem(b, 1e5, 0.5, false) 1353 } 1354 1355 func BenchmarkInsertBoolMemX1e5(b *testing.B) { 1356 benchmarkInsertBoolMem(b, 1e5, 0.5, true) 1357 } 1358 1359 func benchmarkInsertBoolFile(b *testing.B, size int, sel float64, index bool) { 1360 dir, err := ioutil.TempDir("", "ql-bench-") 1361 if err != nil { 1362 b.Fatal(err) 1363 } 1364 1365 n := runtime.GOMAXPROCS(0) 1366 db, err := OpenFile(filepath.Join(dir, "ql.db"), &Options{CanCreate: true}) 1367 if err != nil { 1368 b.Fatal(err) 1369 } 1370 1371 benchmarkInsertBool(b, db, size, sel, index, func() { 1372 runtime.GOMAXPROCS(n) 1373 db.Close() 1374 os.RemoveAll(dir) 1375 }) 1376 } 1377 1378 func BenchmarkInsertBoolFileNoX1e1(b *testing.B) { 1379 benchmarkInsertBoolFile(b, 1e1, 0.5, false) 1380 } 1381 1382 func BenchmarkInsertBoolFileX1e1(b *testing.B) { 1383 benchmarkInsertBoolFile(b, 1e1, 0.5, true) 1384 } 1385 1386 func BenchmarkInsertBoolFileNoX1e2(b *testing.B) { 1387 benchmarkInsertBoolFile(b, 1e2, 0.5, false) 1388 } 1389 1390 func BenchmarkInsertBoolFileX1e2(b *testing.B) { 1391 benchmarkInsertBoolFile(b, 1e2, 0.5, true) 1392 } 1393 1394 func BenchmarkInsertBoolFileNoX1e3(b *testing.B) { 1395 benchmarkInsertBoolFile(b, 1e3, 0.5, false) 1396 } 1397 1398 func BenchmarkInsertBoolFileX1e3(b *testing.B) { 1399 benchmarkInsertBoolFile(b, 1e3, 0.5, true) 1400 } 1401 1402 var benchmarkSelectBoolOnce = map[string]bool{} 1403 1404 func benchmarkSelectBool(b *testing.B, db *DB, size int, selectivity float64, index bool, teardown func()) { 1405 sel, err := Compile("SELECT * FROM t WHERE b;") 1406 if err != nil { 1407 b.Fatal(err) 1408 } 1409 1410 if testing.Verbose() { 1411 benchProlog(b) 1412 id := fmt.Sprintf("%t|%d|%g|%t", db.isMem, size, selectivity, index) 1413 if !benchmarkSelectBoolOnce[id] { 1414 s := "INDEXED" 1415 if !index { 1416 s = "NON " + s 1417 } 1418 b.Logf(`A table has a single %s bool column b. Insert %d records with a random bool value, 1419 %.0f%% of them are true. Measure the performance of 1420 %s 1421 `, s, size, 100*selectivity, sel) 1422 } 1423 benchmarkSelectBoolOnce[id] = true 1424 } 1425 1426 if teardown != nil { 1427 defer teardown() 1428 } 1429 1430 ctx := NewRWCtx() 1431 if _, _, err := db.Run(ctx, ` 1432 BEGIN TRANSACTION; 1433 CREATE TABLE t (b bool); 1434 `); err != nil { 1435 b.Fatal(err) 1436 } 1437 1438 if index { 1439 if _, _, err := db.Run(ctx, ` 1440 CREATE INDEX x ON t (b); 1441 `); err != nil { 1442 b.Fatal(err) 1443 } 1444 } 1445 1446 ins, err := Compile("INSERT INTO t VALUES($1);") 1447 if err != nil { 1448 b.Fatal(err) 1449 } 1450 1451 var n int64 1452 rng := rand.New(rand.NewSource(42)) 1453 for j := 0; j < size; j++ { 1454 v := rng.Float64() < selectivity 1455 if v { 1456 n++ 1457 } 1458 if _, _, err = db.Execute(ctx, ins, v); err != nil { 1459 b.Fatal(err) 1460 } 1461 } 1462 1463 if _, _, err := db.Run(ctx, "COMMIT;"); err != nil { 1464 b.Fatal(err) 1465 } 1466 1467 debug.FreeOSMemory() 1468 b.ResetTimer() 1469 for i := 0; i < b.N; i++ { 1470 var m int64 1471 rss, _, err := db.Execute(nil, sel) 1472 if err != nil { 1473 b.Fatal(err) 1474 } 1475 1476 if err = rss[0].Do(false, func([]interface{}) (bool, error) { 1477 m++ 1478 return true, nil 1479 }); err != nil { 1480 b.Fatal(err) 1481 } 1482 if g, e := n, m; g != e { 1483 b.Fatal(g, e) 1484 } 1485 } 1486 b.StopTimer() 1487 b.SetBytes(n * benchScale) 1488 } 1489 1490 func benchmarkSelectBoolMem(b *testing.B, size int, sel float64, index bool) { 1491 db, err := OpenMem() 1492 if err != nil { 1493 b.Fatal(err) 1494 } 1495 1496 benchmarkSelectBool(b, db, size, sel, index, nil) 1497 } 1498 1499 // ---- 1500 1501 func BenchmarkSelectBoolMemNoX1e1Perc50(b *testing.B) { 1502 benchmarkSelectBoolMem(b, 1e1, 0.5, false) 1503 } 1504 1505 func BenchmarkSelectBoolMemX1e1Perc50(b *testing.B) { 1506 benchmarkSelectBoolMem(b, 1e1, 0.5, true) 1507 } 1508 1509 func BenchmarkSelectBoolMemNoX1e2Perc50(b *testing.B) { 1510 benchmarkSelectBoolMem(b, 1e2, 0.5, false) 1511 } 1512 1513 func BenchmarkSelectBoolMemX1e2Perc50(b *testing.B) { 1514 benchmarkSelectBoolMem(b, 1e2, 0.5, true) 1515 } 1516 1517 func BenchmarkSelectBoolMemNoX1e3Perc50(b *testing.B) { 1518 benchmarkSelectBoolMem(b, 1e3, 0.5, false) 1519 } 1520 1521 func BenchmarkSelectBoolMemX1e3Perc50(b *testing.B) { 1522 benchmarkSelectBoolMem(b, 1e3, 0.5, true) 1523 } 1524 1525 func BenchmarkSelectBoolMemNoX1e4Perc50(b *testing.B) { 1526 benchmarkSelectBoolMem(b, 1e4, 0.5, false) 1527 } 1528 1529 func BenchmarkSelectBoolMemX1e4Perc50(b *testing.B) { 1530 benchmarkSelectBoolMem(b, 1e4, 0.5, true) 1531 } 1532 1533 func BenchmarkSelectBoolMemNoX1e5Perc50(b *testing.B) { 1534 benchmarkSelectBoolMem(b, 1e5, 0.5, false) 1535 } 1536 1537 func BenchmarkSelectBoolMemX1e5Perc50(b *testing.B) { 1538 benchmarkSelectBoolMem(b, 1e5, 0.5, true) 1539 } 1540 1541 // ---- 1542 1543 func BenchmarkSelectBoolMemNoX1e1Perc5(b *testing.B) { 1544 benchmarkSelectBoolMem(b, 1e1, 0.05, false) 1545 } 1546 1547 func BenchmarkSelectBoolMemX1e1Perc5(b *testing.B) { 1548 benchmarkSelectBoolMem(b, 1e1, 0.05, true) 1549 } 1550 1551 func BenchmarkSelectBoolMemNoX1e2Perc5(b *testing.B) { 1552 benchmarkSelectBoolMem(b, 1e2, 0.05, false) 1553 } 1554 1555 func BenchmarkSelectBoolMemX1e2Perc5(b *testing.B) { 1556 benchmarkSelectBoolMem(b, 1e2, 0.05, true) 1557 } 1558 1559 func BenchmarkSelectBoolMemNoX1e3Perc5(b *testing.B) { 1560 benchmarkSelectBoolMem(b, 1e3, 0.05, false) 1561 } 1562 1563 func BenchmarkSelectBoolMemX1e3Perc5(b *testing.B) { 1564 benchmarkSelectBoolMem(b, 1e3, 0.05, true) 1565 } 1566 1567 func BenchmarkSelectBoolMemNoX1e4Perc5(b *testing.B) { 1568 benchmarkSelectBoolMem(b, 1e4, 0.05, false) 1569 } 1570 1571 func BenchmarkSelectBoolMemX1e4Perc5(b *testing.B) { 1572 benchmarkSelectBoolMem(b, 1e4, 0.05, true) 1573 } 1574 1575 func BenchmarkSelectBoolMemNoX1e5Perc5(b *testing.B) { 1576 benchmarkSelectBoolMem(b, 1e5, 0.05, false) 1577 } 1578 1579 func BenchmarkSelectBoolMemX1e5Perc5(b *testing.B) { 1580 benchmarkSelectBoolMem(b, 1e5, 0.05, true) 1581 } 1582 1583 func benchmarkSelectBoolFile(b *testing.B, size int, sel float64, index bool) { 1584 dir, err := ioutil.TempDir("", "ql-bench-") 1585 if err != nil { 1586 b.Fatal(err) 1587 } 1588 1589 n := runtime.GOMAXPROCS(0) 1590 db, err := OpenFile(filepath.Join(dir, "ql.db"), &Options{CanCreate: true}) 1591 if err != nil { 1592 b.Fatal(err) 1593 } 1594 1595 benchmarkSelectBool(b, db, size, sel, index, func() { 1596 runtime.GOMAXPROCS(n) 1597 db.Close() 1598 os.RemoveAll(dir) 1599 }) 1600 } 1601 1602 // ---- 1603 1604 func BenchmarkSelectBoolFileNoX1e1Perc50(b *testing.B) { 1605 benchmarkSelectBoolFile(b, 1e1, 0.5, false) 1606 } 1607 1608 func BenchmarkSelectBoolFileX1e1Perc50(b *testing.B) { 1609 benchmarkSelectBoolFile(b, 1e1, 0.5, true) 1610 } 1611 1612 func BenchmarkSelectBoolFileNoX1e2Perc50(b *testing.B) { 1613 benchmarkSelectBoolFile(b, 1e2, 0.5, false) 1614 } 1615 1616 func BenchmarkSelectBoolFileX1e2Perc50(b *testing.B) { 1617 benchmarkSelectBoolFile(b, 1e2, 0.5, true) 1618 } 1619 1620 func BenchmarkSelectBoolFileNoX1e3Perc50(b *testing.B) { 1621 benchmarkSelectBoolFile(b, 1e3, 0.5, false) 1622 } 1623 1624 func BenchmarkSelectBoolFileX1e3Perc50(b *testing.B) { 1625 benchmarkSelectBoolFile(b, 1e3, 0.5, true) 1626 } 1627 1628 func BenchmarkSelectBoolFileNoX1e4Perc50(b *testing.B) { 1629 benchmarkSelectBoolFile(b, 1e4, 0.5, false) 1630 } 1631 1632 func BenchmarkSelectBoolFileX1e4Perc50(b *testing.B) { 1633 benchmarkSelectBoolFile(b, 1e4, 0.5, true) 1634 } 1635 1636 // ---- 1637 1638 func BenchmarkSelectBoolFileNoX1e1Perc5(b *testing.B) { 1639 benchmarkSelectBoolFile(b, 1e1, 0.05, false) 1640 } 1641 1642 func BenchmarkSelectBoolFileX1e1Perc5(b *testing.B) { 1643 benchmarkSelectBoolFile(b, 1e1, 0.05, true) 1644 } 1645 1646 func BenchmarkSelectBoolFileNoX1e2Perc5(b *testing.B) { 1647 benchmarkSelectBoolFile(b, 1e2, 0.05, false) 1648 } 1649 1650 func BenchmarkSelectBoolFileX1e2Perc5(b *testing.B) { 1651 benchmarkSelectBoolFile(b, 1e2, 0.05, true) 1652 } 1653 1654 func BenchmarkSelectBoolFileNoX1e3Perc5(b *testing.B) { 1655 benchmarkSelectBoolFile(b, 1e3, 0.05, false) 1656 } 1657 1658 func BenchmarkSelectBoolFileX1e3Perc5(b *testing.B) { 1659 benchmarkSelectBoolFile(b, 1e3, 0.05, true) 1660 } 1661 1662 func BenchmarkSelectBoolFileNoX1e4Perc5(b *testing.B) { 1663 benchmarkSelectBoolFile(b, 1e4, 0.05, false) 1664 } 1665 1666 func BenchmarkSelectBoolFileX1e4Perc5(b *testing.B) { 1667 benchmarkSelectBoolFile(b, 1e4, 0.05, true) 1668 } 1669 1670 func TestIndex(t *testing.T) { 1671 db, err := OpenMem() 1672 if err != nil { 1673 t.Fatal(err) 1674 } 1675 1676 ctx := NewRWCtx() 1677 if _, _, err := db.Run(ctx, ` 1678 BEGIN TRANSACTION; 1679 CREATE TABLE t (b bool); 1680 `); err != nil { 1681 t.Fatal(err) 1682 } 1683 1684 if _, _, err := db.Run(ctx, ` 1685 CREATE INDEX x ON t (b); 1686 `); err != nil { 1687 t.Fatal(err) 1688 } 1689 1690 ins, err := Compile("INSERT INTO t VALUES($1);") 1691 if err != nil { 1692 t.Fatal(err) 1693 } 1694 1695 size, selectivity := int(1e1), 0.5 1696 rng := rand.New(rand.NewSource(42)) 1697 var n int64 1698 for j := 0; j < size; j++ { 1699 v := rng.Float64() < selectivity 1700 if v { 1701 n++ 1702 t.Logf("id %d <- true", j+1) 1703 } 1704 if _, _, err = db.Execute(ctx, ins, v); err != nil { 1705 t.Fatal(err) 1706 } 1707 } 1708 1709 if _, _, err := db.Run(ctx, "COMMIT;"); err != nil { 1710 t.Fatal(err) 1711 } 1712 1713 s, err := dumpDB(db, "") 1714 if err != nil { 1715 t.Fatal(err) 1716 } 1717 1718 t.Logf("n: %d\n%s", n, s) 1719 sel, err := Compile("SELECT id(), b FROM t WHERE b;") 1720 if err != nil { 1721 t.Fatal(err) 1722 } 1723 1724 var m int64 1725 rss, _, err := db.Execute(nil, sel) 1726 if err != nil { 1727 t.Fatal(err) 1728 } 1729 1730 if err = rss[0].Do(false, func(rec []interface{}) (bool, error) { 1731 t.Logf("%v", rec) 1732 m++ 1733 return true, nil 1734 }); err != nil { 1735 t.Fatal(err) 1736 } 1737 1738 if g, e := n, m; g != e { 1739 t.Fatal(g, e) 1740 } 1741 } 1742 1743 var benchmarkCrossJoinOnce = map[string]bool{} 1744 1745 func benchmarkCrossJoin(b *testing.B, db *DB, create, sel List, size1, size2 int, index bool, teardown func()) { 1746 if testing.Verbose() { 1747 benchProlog(b) 1748 id := fmt.Sprintf("%t|%d|%d|%t", db.isMem, size1, size2, index) 1749 if !benchmarkCrossJoinOnce[id] { 1750 s := "INDEXED " 1751 if !index { 1752 s = "NON " + s 1753 } 1754 b.Logf(`Fill two %stables with %d and %d records of random numbers [0, 1). Measure the performance of 1755 %s 1756 `, s, size1, size2, sel) 1757 } 1758 benchmarkCrossJoinOnce[id] = true 1759 } 1760 1761 if teardown != nil { 1762 defer teardown() 1763 } 1764 1765 ctx := NewRWCtx() 1766 if _, _, err := db.Execute(ctx, create); err != nil { 1767 b.Fatal(err) 1768 } 1769 1770 if index { 1771 if _, _, err := db.Execute(ctx, xjoinX); err != nil { 1772 b.Fatal(err) 1773 } 1774 } 1775 1776 rng := rand.New(rand.NewSource(42)) 1777 for i := 0; i < size1; i++ { 1778 if _, _, err := db.Execute(ctx, xjoinT, rng.Float64()); err != nil { 1779 b.Fatal(err) 1780 } 1781 } 1782 for i := 0; i < size2; i++ { 1783 if _, _, err := db.Execute(ctx, xjoinU, rng.Float64()); err != nil { 1784 b.Fatal(err) 1785 } 1786 } 1787 1788 if _, _, err := db.Run(ctx, "COMMIT"); err != nil { 1789 b.Fatal(err) 1790 } 1791 1792 rs, _, err := db.Execute(nil, sel) 1793 if err != nil { 1794 b.Fatal(err) 1795 } 1796 1797 var n int 1798 debug.FreeOSMemory() 1799 b.ResetTimer() 1800 for i := 0; i < b.N; i++ { 1801 n = 0 1802 if err := rs[0].Do(false, func(rec []interface{}) (bool, error) { 1803 n++ 1804 return true, nil 1805 }); err != nil { 1806 b.Fatal(err) 1807 } 1808 } 1809 b.StopTimer() 1810 b.SetBytes(int64(n) * benchScale) 1811 } 1812 1813 var ( 1814 xjoinCreate = MustCompile(`BEGIN TRANSACTION; 1815 CREATE TABLE t (f float); 1816 CREATE TABLE u (f float);`) 1817 xjoinSel = MustCompile(`SELECT * FROM (SELECT f FROM t WHERE f < 0.1), (SELECT f FROM u where f < 0.1);`) 1818 xjoinT = MustCompile("INSERT INTO t VALUES($1);") 1819 xjoinU = MustCompile("INSERT INTO u VALUES($1);") 1820 xjoinX = MustCompile(`CREATE INDEX x ON t (f); CREATE INDEX y ON u (f);`) 1821 ) 1822 1823 func benchmarkCrossJoinMem(b *testing.B, size1, size2 int, index bool) { 1824 db, err := OpenMem() 1825 if err != nil { 1826 b.Fatal(err) 1827 } 1828 1829 benchmarkCrossJoin(b, db, xjoinCreate, xjoinSel, size1, size2, index, nil) 1830 } 1831 1832 func benchmarkCrossJoinFile(b *testing.B, size1, size2 int, index bool) { 1833 dir, err := ioutil.TempDir("", "ql-bench-") 1834 if err != nil { 1835 b.Fatal(err) 1836 } 1837 1838 n := runtime.GOMAXPROCS(0) 1839 db, err := OpenFile(filepath.Join(dir, "ql.db"), &Options{CanCreate: true}) 1840 if err != nil { 1841 b.Fatal(err) 1842 } 1843 1844 benchmarkCrossJoin(b, db, xjoinCreate, xjoinSel, size1, size2, index, func() { 1845 runtime.GOMAXPROCS(n) 1846 db.Close() 1847 os.RemoveAll(dir) 1848 }) 1849 } 1850 1851 func BenchmarkCrossJoinMem1e1NoX1e2(b *testing.B) { 1852 benchmarkCrossJoinMem(b, 1e1, 1e2, false) 1853 } 1854 1855 func BenchmarkCrossJoinMem1e1X1e2(b *testing.B) { 1856 benchmarkCrossJoinMem(b, 1e1, 1e2, true) 1857 } 1858 1859 func BenchmarkCrossJoinMem1e2NoX1e3(b *testing.B) { 1860 benchmarkCrossJoinMem(b, 1e2, 1e3, false) 1861 } 1862 1863 func BenchmarkCrossJoinMem1e2X1e3(b *testing.B) { 1864 benchmarkCrossJoinMem(b, 1e2, 1e3, true) 1865 } 1866 1867 func BenchmarkCrossJoinMem1e3NoX1e4(b *testing.B) { 1868 benchmarkCrossJoinMem(b, 1e3, 1e4, false) 1869 } 1870 1871 func BenchmarkCrossJoinMem1e3X1e4(b *testing.B) { 1872 benchmarkCrossJoinMem(b, 1e3, 1e4, true) 1873 } 1874 1875 func BenchmarkCrossJoinMem1e2NoX1e1(b *testing.B) { 1876 benchmarkCrossJoinMem(b, 1e2, 1e1, false) 1877 } 1878 1879 func BenchmarkCrossJoinMem1e2X1e1(b *testing.B) { 1880 benchmarkCrossJoinMem(b, 1e2, 1e1, true) 1881 } 1882 1883 func BenchmarkCrossJoinMem1e3NoX1e2(b *testing.B) { 1884 benchmarkCrossJoinMem(b, 1e3, 1e2, false) 1885 } 1886 1887 func BenchmarkCrossJoinMem1e3X1e2(b *testing.B) { 1888 benchmarkCrossJoinMem(b, 1e3, 1e2, true) 1889 } 1890 1891 func BenchmarkCrossJoinMem1e4NoX1e3(b *testing.B) { 1892 benchmarkCrossJoinMem(b, 1e4, 1e3, false) 1893 } 1894 1895 func BenchmarkCrossJoinMem1e4X1e3(b *testing.B) { 1896 benchmarkCrossJoinMem(b, 1e4, 1e3, true) 1897 } 1898 1899 // ---- 1900 1901 func BenchmarkCrossJoinFile1e1NoX1e2(b *testing.B) { 1902 benchmarkCrossJoinFile(b, 1e1, 1e2, false) 1903 } 1904 1905 func BenchmarkCrossJoinFile1e1X1e2(b *testing.B) { 1906 benchmarkCrossJoinFile(b, 1e1, 1e2, true) 1907 } 1908 1909 func BenchmarkCrossJoinFile1e2NoX1e3(b *testing.B) { 1910 benchmarkCrossJoinFile(b, 1e2, 1e3, false) 1911 } 1912 1913 func BenchmarkCrossJoinFile1e2X1e3(b *testing.B) { 1914 benchmarkCrossJoinFile(b, 1e2, 1e3, true) 1915 } 1916 1917 func BenchmarkCrossJoinFile1e3NoX1e4(b *testing.B) { 1918 benchmarkCrossJoinFile(b, 1e3, 1e4, false) 1919 } 1920 1921 func BenchmarkCrossJoinFile1e3X1e4(b *testing.B) { 1922 benchmarkCrossJoinFile(b, 1e3, 1e4, true) 1923 } 1924 1925 func BenchmarkCrossJoinFile1e2NoX1e1(b *testing.B) { 1926 benchmarkCrossJoinFile(b, 1e2, 1e1, false) 1927 } 1928 1929 func BenchmarkCrossJoinFile1e2X1e1(b *testing.B) { 1930 benchmarkCrossJoinFile(b, 1e2, 1e1, true) 1931 } 1932 1933 func BenchmarkCrossJoinFile1e3NoX1e2(b *testing.B) { 1934 benchmarkCrossJoinFile(b, 1e3, 1e2, false) 1935 } 1936 1937 func BenchmarkCrossJoinFile1e3X1e2(b *testing.B) { 1938 benchmarkCrossJoinFile(b, 1e3, 1e2, true) 1939 } 1940 1941 func BenchmarkCrossJoinFile1e4NoX1e3(b *testing.B) { 1942 benchmarkCrossJoinFile(b, 1e4, 1e3, false) 1943 } 1944 1945 func BenchmarkCrossJoinFile1e4X1e3(b *testing.B) { 1946 benchmarkCrossJoinFile(b, 1e4, 1e3, true) 1947 } 1948 1949 func TestIssue35(t *testing.T) { 1950 var bigInt big.Int 1951 var bigRat big.Rat 1952 bigInt.SetInt64(42) 1953 bigRat.SetInt64(24) 1954 db, err := OpenMem() 1955 if err != nil { 1956 t.Fatal(err) 1957 } 1958 1959 ctx := NewRWCtx() 1960 _, _, err = db.Run(ctx, ` 1961 BEGIN TRANSACTION; 1962 CREATE TABLE t (i bigint, r bigrat); 1963 INSERT INTO t VALUES ($1, $2); 1964 COMMIT; 1965 `, bigInt, bigRat) 1966 if err != nil { 1967 t.Fatal(err) 1968 } 1969 1970 bigInt.SetInt64(420) 1971 bigRat.SetInt64(240) 1972 1973 rs, _, err := db.Run(nil, "SELECT * FROM t;") 1974 if err != nil { 1975 t.Fatal(err) 1976 } 1977 1978 n := 0 1979 if err := rs[0].Do(false, func(rec []interface{}) (bool, error) { 1980 switch n { 1981 case 0: 1982 n++ 1983 if g, e := fmt.Sprint(rec), "[42 24/1]"; g != e { 1984 t.Fatal(g, e) 1985 } 1986 1987 return true, nil 1988 default: 1989 t.Fatal(n) 1990 panic("unreachable") 1991 } 1992 }); err != nil { 1993 t.Fatal(err) 1994 } 1995 } 1996 1997 func TestIssue28(t *testing.T) { 1998 if testing.Short() { 1999 t.Skip("skipping test in short mode.") 2000 } 2001 2002 RegisterDriver() 2003 dir, err := ioutil.TempDir("", "ql-test-") 2004 if err != nil { 2005 t.Fatal(err) 2006 } 2007 2008 defer os.RemoveAll(dir) 2009 pth := filepath.Join(dir, "ql.db") 2010 sdb, err := sql.Open("ql", "file://"+pth) 2011 if err != nil { 2012 t.Fatal(err) 2013 } 2014 2015 defer sdb.Close() 2016 tx, err := sdb.Begin() 2017 if err != nil { 2018 t.Fatal(err) 2019 } 2020 2021 if _, err = tx.Exec("CREATE TABLE t (i int);"); err != nil { 2022 t.Fatal(err) 2023 } 2024 2025 if err = tx.Commit(); err != nil { 2026 t.Fatal(err) 2027 } 2028 2029 if _, err = os.Stat(pth); err != nil { 2030 t.Fatal(err) 2031 } 2032 2033 pth = filepath.Join(dir, "mem.db") 2034 mdb, err := sql.Open("ql", "memory://"+pth) 2035 if err != nil { 2036 t.Fatal(err) 2037 } 2038 2039 defer mdb.Close() 2040 if tx, err = mdb.Begin(); err != nil { 2041 t.Fatal(err) 2042 } 2043 2044 if _, err = tx.Exec("CREATE TABLE t (i int);"); err != nil { 2045 t.Fatal(err) 2046 } 2047 2048 if err = tx.Commit(); err != nil { 2049 t.Fatal(err) 2050 } 2051 2052 if _, err = os.Stat(pth); err == nil { 2053 t.Fatal(err) 2054 } 2055 } 2056 2057 func dumpFields(f []*fld) string { 2058 a := []string{} 2059 for _, v := range f { 2060 a = append(a, fmt.Sprintf("%p: %q", v, v.name)) 2061 } 2062 return strings.Join(a, ", ") 2063 } 2064 2065 func TestIssue50(t *testing.T) { // https://github.com/cznic/ql/issues/50 2066 if testing.Short() { 2067 t.Skip("skipping test in short mode.") 2068 } 2069 2070 const dbFileName = "scans.qldb" 2071 2072 type Scan struct { 2073 ID int 2074 Jobname string 2075 Timestamp time.Time 2076 Data []byte 2077 2078 X, Y, Z float64 2079 Alpha, Beta, Gamma float64 2080 } 2081 2082 // querys 2083 const dbCreateTables = ` 2084 CREATE TABLE IF NOT EXISTS Scans ( 2085 X float, 2086 Y float, 2087 Z float, 2088 Alpha float, 2089 Beta float, 2090 Gamma float, 2091 Timestamp time, 2092 Jobname string, 2093 Data blob 2094 ); 2095 CREATE INDEX IF NOT EXISTS ScansId on Scans (id()); 2096 ` 2097 2098 const dbInsertScan = ` 2099 INSERT INTO Scans (Timestamp,Jobname,X,Y,Z,Alpha,Beta,Gamma,Data) VALUES( 2100 $1, 2101 $2, 2102 $3,$4,$5, 2103 $6,$7,$8, 2104 $9 2105 ); 2106 ` 2107 2108 const dbSelectOverview = `SELECT id() as ID, Jobname, Timestamp, Data, Y,Z, Gamma From Scans;` 2109 2110 dir, err := ioutil.TempDir("", "ql-test-") 2111 if err != nil { 2112 t.Fatal(err) 2113 } 2114 defer os.RemoveAll(dir) 2115 2116 // create db 2117 t.Log("Opening db.") 2118 RegisterDriver() 2119 db, err := sql.Open("ql", filepath.Join(dir, dbFileName)) 2120 if err != nil { 2121 t.Fatal(err) 2122 } 2123 defer db.Close() 2124 2125 tx, err := db.Begin() 2126 if err != nil { 2127 return 2128 } 2129 _, err = tx.Exec(dbCreateTables) 2130 if err != nil { 2131 t.Fatal("could not create Table.", err) 2132 } 2133 2134 err = tx.Commit() 2135 if err != nil { 2136 t.Fatal("could not commit transaction.", err) 2137 } 2138 2139 // insert some data 2140 tx, err = db.Begin() 2141 if err != nil { 2142 t.Fatalf("db.Begin() Error - %v", err) 2143 } 2144 2145 stmt, err := tx.Prepare(dbInsertScan) 2146 if err != nil { 2147 t.Fatalf("tx.Prepare(dbInsertScan) Error - %v", err) 2148 } 2149 defer stmt.Close() 2150 2151 scanFnames := []string{"1.xyz", "2.xyz", "3.xyz"} 2152 for _, fname := range scanFnames { 2153 scanData, err := ioutil.ReadFile(filepath.Join("_testdata", fname)) 2154 if err != nil { 2155 t.Fatalf("ioutil.ReadFile(%s) Error - %v", fname, err) 2156 } 2157 2158 // hash before insert 2159 h := md5.New() 2160 h.Write(scanData) 2161 2162 t.Logf("md5 of %s: %x", fname, h.Sum(nil)) 2163 2164 _, err = stmt.Exec(time.Now(), "Job-0815", 1.0, 2.0, 3.0, 0.1, 0.2, 0.3, scanData) 2165 if err != nil { 2166 t.Fatalf("stmt.Exec() Error - %v", err) 2167 return 2168 } 2169 } 2170 2171 err = tx.Commit() 2172 if err != nil { 2173 t.Fatalf("tx.Commit() Error - %v", err) 2174 } 2175 2176 // select the inserted data 2177 rows, err := db.Query(dbSelectOverview) 2178 if err != nil { 2179 t.Fatalf("db.Query(dbSelectOverview) Error - %v", err) 2180 } 2181 defer rows.Close() 2182 2183 var scans []Scan 2184 for rows.Next() { 2185 var s Scan 2186 var data []byte 2187 2188 err = rows.Scan(&s.ID, &s.Jobname, &s.Timestamp, &data, &s.Y, &s.Z, &s.Gamma) 2189 if err != nil { 2190 t.Fatalf("rows.Scan(&s..) Error - %v", err) 2191 } 2192 scans = append(scans, s) 2193 2194 // hash after select 2195 h := md5.New() 2196 h.Write(data) 2197 2198 t.Logf("md5 of %d: %x", s.ID, h.Sum(nil)) 2199 } 2200 2201 err = rows.Err() 2202 if err != nil { 2203 t.Fatalf("rows.Err() Error - %v", err) 2204 } 2205 2206 t.Log("Done:", scans) 2207 } 2208 2209 func TestIssue56(t *testing.T) { 2210 if testing.Short() { 2211 t.Skip("skipping test in short mode.") 2212 } 2213 2214 var schema = ` 2215 CREATE TABLE IF NOT EXISTS Test ( 2216 A string, 2217 B string, 2218 Suppressed bool, 2219 ); 2220 CREATE INDEX IF NOT EXISTS aIdx ON Test (A); 2221 CREATE INDEX IF NOT EXISTS bIdx ON Test (B); 2222 ` 2223 2224 RegisterDriver() 2225 dir, err := ioutil.TempDir("", "ql-test-") 2226 if err != nil { 2227 t.Fatal(err) 2228 } 2229 2230 defer os.RemoveAll(dir) 2231 pth := filepath.Join(dir, "test.db") 2232 db, err := sql.Open("ql", "file://"+pth) 2233 if err != nil { 2234 t.Fatal(err) 2235 } 2236 2237 defer db.Close() 2238 2239 tx, err := db.Begin() 2240 if err != nil { 2241 t.Fatal(err) 2242 } 2243 2244 _, err = tx.Exec(schema) 2245 if err != nil { 2246 t.Fatal(err) 2247 } 2248 2249 err = tx.Commit() 2250 if err != nil { 2251 t.Fatal(err) 2252 } 2253 2254 // Open a new transaction and do a SELECT 2255 2256 tx, err = db.Begin() 2257 if err != nil { 2258 t.Fatal(err) 2259 } 2260 2261 var id int64 2262 row := tx.QueryRow("SELECT * FROM Test") 2263 err = row.Scan(&id) // <-- Blocks here 2264 2265 switch err { 2266 case sql.ErrNoRows: 2267 break 2268 case nil: 2269 break 2270 default: 2271 t.Fatal(err) 2272 } 2273 2274 tx.Rollback() 2275 return 2276 } 2277 2278 func TestRecordSetRows(t *testing.T) { 2279 db, err := OpenMem() 2280 if err != nil { 2281 t.Fatal(err) 2282 } 2283 2284 rss, _, err := db.Run(NewRWCtx(), ` 2285 BEGIN TRANSACTION; 2286 CREATE TABLE t (i int); 2287 INSERT INTO t VALUES (1), (2), (3), (4), (5); 2288 COMMIT; 2289 SELECT * FROM t ORDER BY i; 2290 `) 2291 if err != nil { 2292 t.Fatal(err) 2293 } 2294 2295 tab := []struct { 2296 limit, offset int 2297 result []int64 2298 }{ 2299 // 0 2300 {-1, 0, []int64{1, 2, 3, 4, 5}}, 2301 {0, 0, nil}, 2302 {1, 0, []int64{1}}, 2303 {2, 0, []int64{1, 2}}, 2304 {3, 0, []int64{1, 2, 3}}, 2305 // 5 2306 {4, 0, []int64{1, 2, 3, 4}}, 2307 {5, 0, []int64{1, 2, 3, 4, 5}}, 2308 {6, 0, []int64{1, 2, 3, 4, 5}}, 2309 {-1, 0, []int64{1, 2, 3, 4, 5}}, 2310 {-1, 1, []int64{2, 3, 4, 5}}, 2311 // 10 2312 {-1, 2, []int64{3, 4, 5}}, 2313 {-1, 3, []int64{4, 5}}, 2314 {-1, 4, []int64{5}}, 2315 {-1, 5, nil}, 2316 {3, 0, []int64{1, 2, 3}}, 2317 // 15 2318 {3, 1, []int64{2, 3, 4}}, 2319 {3, 2, []int64{3, 4, 5}}, 2320 {3, 3, []int64{4, 5}}, 2321 {3, 4, []int64{5}}, 2322 {3, 5, nil}, 2323 // 20 2324 {-1, 2, []int64{3, 4, 5}}, 2325 {0, 2, nil}, 2326 {1, 2, []int64{3}}, 2327 {2, 2, []int64{3, 4}}, 2328 {3, 2, []int64{3, 4, 5}}, 2329 // 25 2330 {4, 2, []int64{3, 4, 5}}, 2331 } 2332 2333 rs := rss[0] 2334 for iTest, test := range tab { 2335 t.Log(iTest) 2336 rows, err := rs.Rows(test.limit, test.offset) 2337 if err != nil { 2338 t.Fatal(err) 2339 } 2340 2341 if g, e := len(rows), len(test.result); g != e { 2342 t.Log(rows, test.result) 2343 t.Fatal(g, e) 2344 } 2345 2346 for i, row := range rows { 2347 if g, e := len(row), 1; g != e { 2348 t.Fatal(i, g, i) 2349 } 2350 2351 if g, e := row[0], test.result[i]; g != e { 2352 t.Fatal(i, g, e) 2353 } 2354 } 2355 } 2356 } 2357 2358 func TestRecordFirst(t *testing.T) { 2359 q := MustCompile("SELECT * FROM t WHERE i > $1 ORDER BY i;") 2360 db, err := OpenMem() 2361 if err != nil { 2362 t.Fatal(err) 2363 } 2364 2365 if _, _, err = db.Run(NewRWCtx(), ` 2366 BEGIN TRANSACTION; 2367 CREATE TABLE t (i int); 2368 INSERT INTO t VALUES (1), (2), (3), (4), (5); 2369 COMMIT; 2370 `); err != nil { 2371 t.Fatal(err) 2372 } 2373 2374 tab := []struct { 2375 par int64 2376 result int64 2377 }{ 2378 {-1, 1}, 2379 {0, 1}, 2380 {1, 2}, 2381 {2, 3}, 2382 {3, 4}, 2383 {4, 5}, 2384 {5, -1}, 2385 } 2386 2387 for iTest, test := range tab { 2388 t.Log(iTest) 2389 rss, _, err := db.Execute(nil, q, test.par) 2390 if err != nil { 2391 t.Fatal(err) 2392 } 2393 2394 row, err := rss[0].FirstRow() 2395 if err != nil { 2396 t.Fatal(err) 2397 } 2398 2399 switch { 2400 case test.result < 0: 2401 if row != nil { 2402 t.Fatal(row) 2403 } 2404 default: 2405 if row == nil { 2406 t.Fatal(row) 2407 } 2408 2409 if g, e := len(row), 1; g != e { 2410 t.Fatal(g, e) 2411 } 2412 2413 if g, e := row[0], test.result; g != e { 2414 t.Fatal(g, e) 2415 } 2416 } 2417 } 2418 } 2419 2420 var issue63 = MustCompile(` 2421 BEGIN TRANSACTION; 2422 CREATE TABLE Forecast (WeatherProvider string, Timestamp time, MinTemp int32, MaxTemp int32); 2423 INSERT INTO Forecast VALUES ("dwd.de", now(), 20, 22); 2424 COMMIT; 2425 SELECT * FROM Forecast WHERE Timestamp > 0;`) 2426 2427 func TestIssue63(t *testing.T) { 2428 db, err := OpenMem() 2429 if err != nil { 2430 t.Fatal(err) 2431 } 2432 2433 rs, _, err := db.Execute(NewRWCtx(), issue63) 2434 if err != nil { 2435 t.Fatal(err) 2436 } 2437 2438 if _, err = rs[0].Rows(-1, 0); err == nil { 2439 t.Fatal(err) 2440 } 2441 2442 t.Log(err) 2443 if g, e := strings.Contains(err.Error(), "invalid operation"), true; g != e { 2444 t.Fatal(g, e) 2445 } 2446 2447 if g, e := strings.Contains(err.Error(), "mismatched types time.Time and int64"), true; g != e { 2448 t.Fatal(g, e) 2449 } 2450 } 2451 2452 const issue66Src = ` 2453 BEGIN TRANSACTION; 2454 CREATE TABLE t (i int); 2455 CREATE UNIQUE INDEX x ON t (i); 2456 INSERT INTO t VALUES (1), (1); 2457 COMMIT;` 2458 2459 var issue66 = MustCompile(issue66Src) 2460 2461 func TestIssue66Mem(t *testing.T) { 2462 db, err := OpenMem() 2463 if err != nil { 2464 t.Fatal(err) 2465 } 2466 2467 _, _, err = db.Execute(NewRWCtx(), issue66) 2468 if err == nil { 2469 t.Fatal(err) 2470 } 2471 2472 t.Log(err) 2473 } 2474 2475 func TestIssue66File(t *testing.T) { 2476 dir, err := ioutil.TempDir("", "ql-test-") 2477 if err != nil { 2478 t.Fatal(err) 2479 } 2480 2481 defer os.RemoveAll(dir) 2482 2483 db, err := OpenFile(filepath.Join(dir, "test.db"), &Options{CanCreate: true}) 2484 if err != nil { 2485 t.Fatal(err) 2486 } 2487 2488 defer db.Close() 2489 2490 _, _, err = db.Execute(NewRWCtx(), issue66) 2491 if err == nil { 2492 t.Fatal(err) 2493 } 2494 2495 t.Log(err) 2496 } 2497 2498 func TestIssue66MemDriver(t *testing.T) { 2499 RegisterMemDriver() 2500 db, err := sql.Open("ql-mem", "TestIssue66MemDriver-"+fmt.Sprintf("%d", time.Now().UnixNano())) 2501 if err != nil { 2502 t.Fatal(err) 2503 } 2504 2505 defer db.Close() 2506 2507 tx, err := db.Begin() 2508 if err != nil { 2509 t.Fatal(err) 2510 } 2511 2512 if _, err = tx.Exec(issue66Src); err == nil { 2513 t.Fatal(err) 2514 } 2515 2516 t.Log(err) 2517 } 2518 2519 func TestIssue66FileDriver(t *testing.T) { 2520 RegisterDriver() 2521 dir, err := ioutil.TempDir("", "ql-test-") 2522 if err != nil { 2523 t.Fatal(err) 2524 } 2525 2526 defer os.RemoveAll(dir) 2527 2528 db, err := sql.Open("ql", filepath.Join(dir, "TestIssue66MemDriver")) 2529 if err != nil { 2530 t.Fatal(err) 2531 } 2532 2533 defer db.Close() 2534 2535 tx, err := db.Begin() 2536 if err != nil { 2537 t.Fatal(err) 2538 } 2539 2540 if _, err = tx.Exec(issue66Src); err == nil { 2541 t.Fatal(err) 2542 } 2543 2544 t.Log(err) 2545 } 2546 2547 func Example_lIKE() { 2548 db, err := OpenMem() 2549 if err != nil { 2550 panic(err) 2551 } 2552 2553 rss, _, err := db.Run(NewRWCtx(), ` 2554 BEGIN TRANSACTION; 2555 CREATE TABLE t (i int, s string); 2556 INSERT INTO t VALUES 2557 (1, "seafood"), 2558 (2, "A fool on the hill"), 2559 (3, NULL), 2560 (4, "barbaz"), 2561 (5, "foobar"), 2562 ; 2563 COMMIT; 2564 2565 SELECT * FROM t WHERE s LIKE "foo" ORDER BY i; 2566 SELECT * FROM t WHERE s LIKE "^bar" ORDER BY i; 2567 SELECT * FROM t WHERE s LIKE "bar$" ORDER BY i; 2568 SELECT * FROM t WHERE !(s LIKE "foo") ORDER BY i;`, 2569 ) 2570 if err != nil { 2571 panic(err) 2572 } 2573 2574 for _, rs := range rss { 2575 if err := rs.Do(false, func(data []interface{}) (bool, error) { 2576 fmt.Println(data) 2577 return true, nil 2578 }); err != nil { 2579 panic(err) 2580 } 2581 fmt.Println("----") 2582 } 2583 // Output: 2584 // [1 seafood] 2585 // [2 A fool on the hill] 2586 // [5 foobar] 2587 // ---- 2588 // [4 barbaz] 2589 // ---- 2590 // [5 foobar] 2591 // ---- 2592 // [4 barbaz] 2593 // ---- 2594 } 2595 2596 func TestIssue73(t *testing.T) { 2597 if testing.Short() { 2598 t.Skip("skipping test in short mode.") 2599 } 2600 2601 RegisterDriver() 2602 dir, err := ioutil.TempDir("", "ql-test-") 2603 if err != nil { 2604 t.Fatal(err) 2605 } 2606 2607 defer os.RemoveAll(dir) 2608 pth := filepath.Join(dir, "test.db") 2609 2610 for i := 0; i < 10; i++ { 2611 var db *sql.DB 2612 var tx *sql.Tx 2613 var err error 2614 var row *sql.Row 2615 var name string 2616 2617 if db, err = sql.Open("ql", pth); err != nil { 2618 t.Fatal("sql.Open: ", err) 2619 } 2620 2621 t.Log("Call to db.Begin()") 2622 if tx, err = db.Begin(); err != nil { 2623 t.Fatal("db.Begin: ", err) 2624 } 2625 2626 t.Log("Call to tx.QueryRow()") 2627 row = tx.QueryRow(`SELECT Name FROM __Table`) 2628 t.Log("Call to tx.Commit()") 2629 if err = tx.Commit(); err != nil { 2630 t.Fatal("tx.Commit: ", err) 2631 } 2632 2633 row.Scan(&name) 2634 t.Log("name: ", name) 2635 } 2636 } 2637 2638 func Example_id() { 2639 db, err := OpenMem() 2640 if err != nil { 2641 panic(err) 2642 } 2643 2644 rss, _, err := db.Run(NewRWCtx(), ` 2645 BEGIN TRANSACTION; 2646 CREATE TABLE foo (i int); 2647 INSERT INTO foo VALUES (10), (20); 2648 CREATE TABLE bar (fooID int, s string); 2649 INSERT INTO bar SELECT id(), "ten" FROM foo WHERE i == 10; 2650 INSERT INTO bar SELECT id(), "twenty" FROM foo WHERE i == 20; 2651 COMMIT; 2652 SELECT * 2653 FROM foo, bar 2654 WHERE bar.fooID == id(foo) 2655 ORDER BY id(foo);`, 2656 ) 2657 if err != nil { 2658 panic(err) 2659 } 2660 2661 for _, rs := range rss { 2662 if err := rs.Do(false, func(data []interface{}) (bool, error) { 2663 fmt.Println(data) 2664 return true, nil 2665 }); err != nil { 2666 panic(err) 2667 } 2668 fmt.Println("----") 2669 } 2670 // Output: 2671 // [10 1 ten] 2672 // [20 2 twenty] 2673 // ---- 2674 } 2675 2676 func eqRows(a, b [][]interface{}) bool { 2677 if len(a) != len(b) { 2678 return false 2679 } 2680 2681 for i, rowa := range a { 2682 rowb := b[i] 2683 if len(rowa) != len(rowb) { 2684 return false 2685 } 2686 2687 for j, va := range rowa { 2688 if va != rowb[j] { 2689 return false 2690 } 2691 } 2692 } 2693 return true 2694 } 2695 2696 func TestInPredicateBug(t *testing.T) { 2697 db, err := OpenMem() 2698 if err != nil { 2699 t.Fatal(err) 2700 } 2701 2702 if _, _, err := db.Run(NewRWCtx(), ` 2703 BEGIN TRANSACTION; 2704 CREATE TABLE all (i int); 2705 INSERT INTO all VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9); 2706 CREATE TABLE even (i int); 2707 INSERT INTO even VALUES (0), (2), (4), (6), (8); 2708 COMMIT; 2709 `); err != nil { 2710 t.Fatal(err) 2711 } 2712 2713 q := MustCompile(`SELECT * FROM all WHERE i IN (SELECT * FROM even) ORDER BY i`) 2714 rs, _, err := db.Execute(nil, q) 2715 if err != nil { 2716 t.Fatal(err) 2717 } 2718 2719 rows, err := rs[0].Rows(-1, 0) 2720 if err != nil { 2721 t.Fatal(err) 2722 } 2723 2724 if g, e := rows, [][]interface{}{{int64(0)}, {int64(2)}, {int64(4)}, {int64(6)}, {int64(8)}}; !eqRows(g, e) { 2725 t.Fatalf("\n%v\n%v", g, e) 2726 } 2727 2728 if _, _, err := db.Run(NewRWCtx(), ` 2729 BEGIN TRANSACTION; 2730 TRUNCATE TABLE even; 2731 INSERT INTO even VALUES (1), (3), (5); 2732 COMMIT; 2733 `); err != nil { 2734 t.Fatal(err) 2735 } 2736 2737 if rs, _, err = db.Execute(nil, q); err != nil { 2738 t.Fatal(err) 2739 } 2740 2741 if rows, err = rs[0].Rows(-1, 0); err != nil { 2742 t.Fatal(err) 2743 } 2744 2745 if g, e := rows, [][]interface{}{{int64(1)}, {int64(3)}, {int64(5)}}; !eqRows(g, e) { 2746 t.Fatalf("\n%v\n%v", g, e) 2747 } 2748 } 2749 2750 func testMentionedColumns(s stmt) (err error) { 2751 defer func() { 2752 if e := recover(); e != nil { 2753 switch x := e.(type) { 2754 case error: 2755 err = x 2756 default: 2757 err = fmt.Errorf("error: %v", e) 2758 } 2759 } 2760 }() 2761 2762 switch x := s.(type) { 2763 case 2764 *alterTableAddStmt, 2765 *alterTableDropColumnStmt, 2766 beginTransactionStmt, 2767 *createTableStmt, 2768 commitStmt, 2769 *dropIndexStmt, 2770 *dropTableStmt, 2771 *explainStmt, 2772 rollbackStmt, 2773 *truncateTableStmt: 2774 // nop 2775 case *createIndexStmt: 2776 for _, e := range x.exprList { 2777 mentionedColumns(e) 2778 } 2779 case *deleteStmt: 2780 if e := x.where; e != nil { 2781 mentionedColumns(e) 2782 } 2783 case *insertIntoStmt: 2784 for _, ll := range x.lists { 2785 for _, e := range ll { 2786 mentionedColumns(e) 2787 } 2788 } 2789 case *selectStmt: 2790 for _, f := range x.flds { 2791 mentionedColumns(f.expr) 2792 } 2793 if l := x.limit; l != nil { 2794 mentionedColumns(l.expr) 2795 } 2796 if o := x.offset; o != nil { 2797 mentionedColumns(o.expr) 2798 } 2799 if o := x.order; o != nil { 2800 for _, e := range o.by { 2801 mentionedColumns(e) 2802 } 2803 } 2804 if w := x.where; w != nil { 2805 if e := w.expr; e != nil { 2806 mentionedColumns(w.expr) 2807 } 2808 if s := w.sel; s != nil { 2809 if err := testMentionedColumns(s); err != nil { 2810 return err 2811 } 2812 } 2813 } 2814 case *updateStmt: 2815 for _, v := range x.list { 2816 mentionedColumns(v.expr) 2817 } 2818 if e := x.where; e != nil { 2819 mentionedColumns(e) 2820 } 2821 default: 2822 panic("internal error 056") 2823 } 2824 return nil 2825 } 2826 2827 const ( 2828 issue99RowsToInsert = 100 2829 issue99Cycles = 100 2830 ) 2831 2832 var ( 2833 fieldsIssue99 = []string{ 2834 "Datacenter", 2835 "Name", 2836 "Address", 2837 "Health", 2838 "C0", 2839 "C1", 2840 "C2", 2841 "C3", 2842 "C4", 2843 "C5", 2844 "C6", 2845 "C7", 2846 "C8", 2847 "C9", 2848 "C10", 2849 "C11", 2850 "C12", 2851 "C13", 2852 "C14", 2853 "C15", 2854 "C16", 2855 "C17", 2856 "C18", 2857 "C19", 2858 "C20", 2859 "C21", 2860 "C22", 2861 "C23", 2862 "C24", 2863 "C25", 2864 "C26", 2865 "C27", 2866 "C28", 2867 "C29", 2868 "C30", 2869 "C31", 2870 "C32", 2871 "C33", 2872 "C34", 2873 "C35", 2874 "C36", 2875 "C37", 2876 "C38", 2877 "C39", 2878 "C40", 2879 "C41", 2880 "C42", 2881 "C43", 2882 "C44", 2883 "C45", 2884 "C46", 2885 "C47", 2886 "C48", 2887 "C49", 2888 "C50", 2889 "C51", 2890 "C52", 2891 "C53", 2892 "C54", 2893 "C55", 2894 "C56", 2895 "C57", 2896 "C58", 2897 "C59", 2898 "C60", 2899 "C61", 2900 "C62", 2901 "C63", 2902 "C64", 2903 "C65", 2904 "C66", 2905 "C67", 2906 "C68", 2907 "C69", 2908 "C70", 2909 "C71", 2910 "C72", 2911 "C73", 2912 "C74", 2913 "C75", 2914 "C76", 2915 "C77", 2916 "C78", 2917 "C79", 2918 "C80", 2919 "C81", 2920 "C82", 2921 "C83", 2922 "C84", 2923 "C85", 2924 "C86", 2925 "C87", 2926 "C88", 2927 "C89", 2928 "C90", 2929 "C91", 2930 "C92", 2931 "C93", 2932 "C94", 2933 "C95", 2934 "C96", 2935 "C97", 2936 "C98", 2937 "C99", 2938 } 2939 2940 valuesIssue99 = make([]interface{}, len(fieldsIssue99)) 2941 ) 2942 2943 func init() { 2944 for i := range valuesIssue99 { 2945 s := "" 2946 for _, v := range rand.Perm(32) { 2947 s += string('0' + v) 2948 } 2949 valuesIssue99[i] = s 2950 } 2951 valuesIssue99[3] = true 2952 } 2953 2954 func createTablesIssue99(db *sql.DB) error { 2955 tx, err := db.Begin() 2956 if err != nil { 2957 return err 2958 } 2959 2960 if _, err = tx.Exec(` 2961 DROP TABLE IF EXISTS Node; 2962 CREATE TABLE Node ( 2963 Datacenter string, 2964 Name string, 2965 Address string, 2966 Health bool, 2967 C0 string DEFAULT "", 2968 C1 string DEFAULT "", 2969 C2 string DEFAULT "", 2970 C3 string DEFAULT "", 2971 C4 string DEFAULT "", 2972 C5 string DEFAULT "", 2973 C6 string DEFAULT "", 2974 C7 string DEFAULT "", 2975 C8 string DEFAULT "", 2976 C9 string DEFAULT "", 2977 C10 string DEFAULT "", 2978 C11 string DEFAULT "", 2979 C12 string DEFAULT "", 2980 C13 string DEFAULT "", 2981 C14 string DEFAULT "", 2982 C15 string DEFAULT "", 2983 C16 string DEFAULT "", 2984 C17 string DEFAULT "", 2985 C18 string DEFAULT "", 2986 C19 string DEFAULT "", 2987 C20 string DEFAULT "", 2988 C21 string DEFAULT "", 2989 C22 string DEFAULT "", 2990 C23 string DEFAULT "", 2991 C24 string DEFAULT "", 2992 C25 string DEFAULT "", 2993 C26 string DEFAULT "", 2994 C27 string DEFAULT "", 2995 C28 string DEFAULT "", 2996 C29 string DEFAULT "", 2997 C30 string DEFAULT "", 2998 C31 string DEFAULT "", 2999 C32 string DEFAULT "", 3000 C33 string DEFAULT "", 3001 C34 string DEFAULT "", 3002 C35 string DEFAULT "", 3003 C36 string DEFAULT "", 3004 C37 string DEFAULT "", 3005 C38 string DEFAULT "", 3006 C39 string DEFAULT "", 3007 C40 string DEFAULT "", 3008 C41 string DEFAULT "", 3009 C42 string DEFAULT "", 3010 C43 string DEFAULT "", 3011 C44 string DEFAULT "", 3012 C45 string DEFAULT "", 3013 C46 string DEFAULT "", 3014 C47 string DEFAULT "", 3015 C48 string DEFAULT "", 3016 C49 string DEFAULT "", 3017 C50 string DEFAULT "", 3018 C51 string DEFAULT "", 3019 C52 string DEFAULT "", 3020 C53 string DEFAULT "", 3021 C54 string DEFAULT "", 3022 C55 string DEFAULT "", 3023 C56 string DEFAULT "", 3024 C57 string DEFAULT "", 3025 C58 string DEFAULT "", 3026 C59 string DEFAULT "", 3027 C60 string DEFAULT "", 3028 C61 string DEFAULT "", 3029 C62 string DEFAULT "", 3030 C63 string DEFAULT "", 3031 C64 string DEFAULT "", 3032 C65 string DEFAULT "", 3033 C66 string DEFAULT "", 3034 C67 string DEFAULT "", 3035 C68 string DEFAULT "", 3036 C69 string DEFAULT "", 3037 C70 string DEFAULT "", 3038 C71 string DEFAULT "", 3039 C72 string DEFAULT "", 3040 C73 string DEFAULT "", 3041 C74 string DEFAULT "", 3042 C75 string DEFAULT "", 3043 C76 string DEFAULT "", 3044 C77 string DEFAULT "", 3045 C78 string DEFAULT "", 3046 C79 string DEFAULT "", 3047 C80 string DEFAULT "", 3048 C81 string DEFAULT "", 3049 C82 string DEFAULT "", 3050 C83 string DEFAULT "", 3051 C84 string DEFAULT "", 3052 C85 string DEFAULT "", 3053 C86 string DEFAULT "", 3054 C87 string DEFAULT "", 3055 C88 string DEFAULT "", 3056 C89 string DEFAULT "", 3057 C90 string DEFAULT "", 3058 C91 string DEFAULT "", 3059 C92 string DEFAULT "", 3060 C93 string DEFAULT "", 3061 C94 string DEFAULT "", 3062 C95 string DEFAULT "", 3063 C96 string DEFAULT "", 3064 C97 string DEFAULT "", 3065 C98 string DEFAULT "", 3066 C99 string DEFAULT "", 3067 );`); err != nil { 3068 return err 3069 } 3070 3071 return tx.Commit() 3072 } 3073 3074 func issue99Fill(db *sql.DB) (int, error) { 3075 tx, err := db.Begin() 3076 if err != nil { 3077 return -1, err 3078 } 3079 3080 sql := "INSERT INTO Node (" + strings.Join(fieldsIssue99, ",") + ") VALUES ($1, $2, $3, $4" 3081 for i := range valuesIssue99 { 3082 if i > 3 { 3083 sql += ", $" + strconv.Itoa(i+1) 3084 } 3085 } 3086 sql += ")" 3087 3088 stmt, err := tx.Prepare(sql) 3089 if err != nil { 3090 return 0, err 3091 } 3092 3093 for i := 0; i < issue99RowsToInsert; i++ { 3094 if _, err = stmt.Exec(valuesIssue99...); err != nil { 3095 return 0, err 3096 } 3097 } 3098 3099 return issue99RowsToInsert, tx.Commit() 3100 } 3101 3102 func testIssue99(tb testing.TB, db *sql.DB) int { 3103 sum := 0 3104 for i := 0; i < issue99Cycles; i++ { 3105 if err := createTablesIssue99(db); err != nil { 3106 tb.Fatal(err) 3107 } 3108 3109 n2, err := issue99Fill(db) 3110 if err != nil { 3111 tb.Fatal(err) 3112 } 3113 3114 sum += n2 3115 } 3116 return sum 3117 } 3118 3119 var benchmarkIssue99 sync.Once 3120 3121 func BenchmarkIssue99(b *testing.B) { 3122 if testing.Verbose() { 3123 benchProlog(b) 3124 benchmarkIssue99.Do(func() { 3125 b.Logf(`1 op == (Re)create a 100+ column table, fill it with %d records. Repeat %d times. 3126 3127 `, issue99RowsToInsert, issue99Cycles) 3128 }) 3129 } 3130 RegisterMemDriver() 3131 db, err := sql.Open("ql-mem", "issue99") 3132 if err != nil { 3133 b.Fatal(err) 3134 } 3135 3136 b.ResetTimer() 3137 recs := 0 3138 for i := 0; i < b.N; i++ { 3139 recs = testIssue99(b, db) 3140 } 3141 b.SetBytes(int64(recs) * benchScale) 3142 } 3143 3144 func TestIssue108(t *testing.T) { 3145 db, err := OpenMem() 3146 if err != nil { 3147 t.Fatal(err) 3148 } 3149 3150 setup := `BEGIN TRANSACTION; 3151 CREATE TABLE people (name string NOT NULL); 3152 INSERT INTO people VALUES ("alice"), ("bob"); 3153 ` 3154 ctx := NewRWCtx() 3155 _, _, err = db.Run(ctx, setup) 3156 if err != nil { 3157 t.Fatal(err) 3158 } 3159 3160 rs, _, err := db.Run(ctx, "SELECT count() FROM people;") 3161 if err != nil { 3162 t.Fatal(err) 3163 } 3164 3165 row, err := rs[0].FirstRow() // Used to block forever. 3166 if err != nil { 3167 t.Fatal(err) 3168 } 3169 3170 n, ok := row[0].(int64) 3171 if !ok || n != 2 { 3172 t.Fatal(n, 2) 3173 } 3174 3175 _, _, err = db.Run(ctx, "COMMIT;") 3176 if err != nil { 3177 t.Fatal(err) 3178 } 3179 } 3180 3181 type issue109 struct { 3182 *testing.T 3183 db *DB 3184 } 3185 3186 func (t issue109) test(doIndex bool) { 3187 t.Logf("Use index: %v", doIndex) 3188 var err error 3189 if t.db, err = OpenMem(); err != nil { 3190 t.Fatal(err) 3191 } 3192 3193 if _, _, err := t.db.Run(NewRWCtx(), ` 3194 BEGIN TRANSACTION; 3195 CREATE TABLE people (name string NOT NULL); 3196 CREATE TABLE awards (name string NOT NULL); 3197 CREATE TABLE people_awards (person_id int NOT NULL, award_id int NOT NULL); 3198 COMMIT; 3199 `); err != nil { 3200 t.Fatal(err) 3201 } 3202 3203 if doIndex { 3204 t.createBuggyIndex() 3205 } 3206 3207 pid1 := t.createPerson("alice") 3208 3209 aid1 := t.createAward("awesome") 3210 aid2 := t.createAward("best") 3211 3212 zeroFullJoinCount := t.countFullJoin(pid1) 3213 if zeroFullJoinCount != 0 { 3214 t.Fatal(zeroFullJoinCount, "Incorrect full join count before creating records") 3215 } 3216 3217 t.insertPersonAward(pid1, aid1) 3218 t.insertPersonAward(pid1, aid2) 3219 3220 initialFullJoinCount := t.countFullJoin(pid1) 3221 if initialFullJoinCount != 2 { 3222 t.Fatal(initialFullJoinCount, "Incorrect full join count before deleting records") 3223 } 3224 3225 initialNumJoinRecords := t.countJoinRecords() 3226 if initialNumJoinRecords != 2 { 3227 t.Fatal(initialNumJoinRecords, "Incorrect number of join records before deleting records") 3228 } 3229 3230 t.deletePersonAwards(pid1) 3231 3232 afterNumJoinRecords := t.countJoinRecords() 3233 if afterNumJoinRecords != 0 { 3234 t.Fatal(afterNumJoinRecords, "Incorrect number of join records after deleting records") 3235 } 3236 3237 afterFullJoinCount := t.countFullJoin(pid1) 3238 if afterFullJoinCount != 0 { 3239 t.Fatal(afterFullJoinCount, "Incorrect full join count after deleting records") 3240 } 3241 3242 t.Logf("OK") 3243 } 3244 3245 func (t issue109) createBuggyIndex() { 3246 if _, _, err := t.db.Run(NewRWCtx(), ` 3247 BEGIN TRANSACTION; 3248 CREATE INDEX people_awards_person_id ON people_awards (person_id); 3249 COMMIT; 3250 `); err != nil { 3251 t.Fatal(err) 3252 } 3253 t.Log("CREATE INDEX people_awards_person_id ON people_awards (person_id);") 3254 } 3255 3256 func (t issue109) createPerson(name string) int64 { 3257 ctx := NewRWCtx() 3258 _, _, err := t.db.Run(ctx, ` 3259 BEGIN TRANSACTION; 3260 INSERT INTO people(name) VALUES ($1); 3261 COMMIT;`, 3262 name, 3263 ) 3264 if err != nil { 3265 t.Fatal(err) 3266 } 3267 3268 t.Logf("INSERT INTO people(name) VALUES (%q); -> ID %v", name, ctx.LastInsertID) 3269 return ctx.LastInsertID 3270 } 3271 3272 func (t issue109) createAward(name string) int64 { 3273 ctx := NewRWCtx() 3274 _, _, err := t.db.Run(ctx, ` 3275 BEGIN TRANSACTION; 3276 INSERT INTO awards(name) VALUES ($1); 3277 COMMIT`, 3278 name, 3279 ) 3280 if err != nil { 3281 t.Fatal(err) 3282 } 3283 3284 t.Logf("INSERT INTO awards(name) VALUES (%q); -> ID %v", name, ctx.LastInsertID) 3285 return ctx.LastInsertID 3286 } 3287 3288 func (t issue109) countFullJoin(personID int64) int { 3289 stmt := ` 3290 SELECT 3291 * 3292 FROM 3293 awards 3294 FULL JOIN 3295 people_awards 3296 ON 3297 id(awards) == people_awards.award_id 3298 WHERE 3299 people_awards.person_id == $1 3300 ` 3301 rs, _, err := t.db.Run(nil, "explain "+stmt, personID) 3302 if err != nil { 3303 t.Fatal(err) 3304 } 3305 3306 rows, err := rs[0].Rows(-1, 0) 3307 if err != nil { 3308 t.Fatal(err) 3309 } 3310 3311 t.Log("----") 3312 for _, v := range rows { 3313 t.Log(v) 3314 } 3315 3316 if rs, _, err = t.db.Run(nil, stmt, personID); err != nil { 3317 t.Fatal(err) 3318 } 3319 3320 if rows, err = rs[0].Rows(-1, 0); err != nil { 3321 t.Fatal(err) 3322 } 3323 3324 for i, v := range rows { 3325 t.Logf("%v/%v: %v", i, len(rows), v) 3326 } 3327 t.Log("----") 3328 return len(rows) 3329 } 3330 3331 func (t issue109) insertPersonAward(personID, awardID int64) { 3332 ctx := NewRWCtx() 3333 _, _, err := t.db.Run(ctx, ` 3334 BEGIN TRANSACTION; 3335 INSERT INTO people_awards(person_id, award_id) VALUES ($1, $2); 3336 COMMIT;`, 3337 personID, awardID, 3338 ) 3339 if err != nil { 3340 t.Fatal(err) 3341 } 3342 t.Logf("INSERT INTO people_awards(person_id, award_id) VALUES (%v, %v);", personID, awardID) 3343 } 3344 3345 func (t issue109) countJoinRecords() int64 { 3346 rs, _, err := t.db.Run(nil, ` 3347 SELECT 3348 count() 3349 FROM 3350 people_awards; 3351 `) 3352 if err != nil { 3353 t.Fatal(err) 3354 } 3355 3356 row, err := rs[0].FirstRow() 3357 if err != nil { 3358 t.Fatal(err) 3359 } 3360 3361 return row[0].(int64) 3362 } 3363 3364 func (t issue109) deletePersonAwards(personID int64) { 3365 ctx := NewRWCtx() 3366 _, _, err := t.db.Run(ctx, ` 3367 BEGIN TRANSACTION; 3368 DELETE FROM people_awards WHERE person_id == $1; 3369 COMMIT`, 3370 personID, 3371 ) 3372 if err != nil { 3373 t.Fatal(err) 3374 } 3375 3376 if ctx.RowsAffected != 2 { 3377 t.Fatal("Did not delete rows as expected") 3378 } 3379 t.Logf("DELETE FROM people_awards WHERE person_id == %v;", personID) 3380 } 3381 3382 func TestIssue109(t *testing.T) { 3383 (issue109{T: t}).test(false) 3384 (issue109{T: t}).test(true) 3385 } 3386 3387 // https://github.com/cznic/ql/issues/142 3388 func TestIssue142(t *testing.T) { 3389 cwd, err := os.Getwd() 3390 if err != nil { 3391 t.Fatal(err) 3392 } 3393 3394 defer os.Chdir(cwd) 3395 3396 wd, err := ioutil.TempDir("", "ql-test-issue-142") 3397 if err != nil { 3398 t.Fatal(err) 3399 } 3400 3401 defer os.RemoveAll(wd) 3402 3403 if err := os.Chdir(wd); err != nil { 3404 t.Fatal(err) 3405 } 3406 3407 RegisterDriver() 3408 for _, nm := range []string{"test.db", "./test.db", "another.db"} { 3409 t.Log(nm) 3410 db, err := sql.Open("ql", nm) 3411 if err != nil { 3412 t.Fatal(err) 3413 } 3414 3415 tx, err := db.Begin() 3416 if err != nil { 3417 t.Fatal(err) 3418 } 3419 3420 if _, err := tx.Exec("drop table if exists t; create table t (c int)"); err != nil { 3421 t.Fatal(err) 3422 } 3423 3424 if err := tx.Commit(); err != nil { 3425 t.Fatal(err) 3426 } 3427 3428 if err := db.Close(); err != nil { 3429 t.Fatal(err) 3430 } 3431 3432 fn := filepath.Join(wd, nm) 3433 fi, err := os.Stat(fn) 3434 if err != nil { 3435 t.Fatal(err) 3436 } 3437 3438 t.Log(fn, fi.Size()) 3439 if fi.Size() == 0 { 3440 t.Fatal("DB is empty") 3441 } 3442 } 3443 }