gopkg.in/cznic/ql.v1@v1.1.0/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 mentionedColumns(w.expr) 2806 } 2807 case *updateStmt: 2808 for _, v := range x.list { 2809 mentionedColumns(v.expr) 2810 } 2811 if e := x.where; e != nil { 2812 mentionedColumns(e) 2813 } 2814 default: 2815 panic("internal error 056") 2816 } 2817 return nil 2818 } 2819 2820 const ( 2821 issue99RowsToInsert = 100 2822 issue99Cycles = 100 2823 ) 2824 2825 var ( 2826 fieldsIssue99 = []string{ 2827 "Datacenter", 2828 "Name", 2829 "Address", 2830 "Health", 2831 "C0", 2832 "C1", 2833 "C2", 2834 "C3", 2835 "C4", 2836 "C5", 2837 "C6", 2838 "C7", 2839 "C8", 2840 "C9", 2841 "C10", 2842 "C11", 2843 "C12", 2844 "C13", 2845 "C14", 2846 "C15", 2847 "C16", 2848 "C17", 2849 "C18", 2850 "C19", 2851 "C20", 2852 "C21", 2853 "C22", 2854 "C23", 2855 "C24", 2856 "C25", 2857 "C26", 2858 "C27", 2859 "C28", 2860 "C29", 2861 "C30", 2862 "C31", 2863 "C32", 2864 "C33", 2865 "C34", 2866 "C35", 2867 "C36", 2868 "C37", 2869 "C38", 2870 "C39", 2871 "C40", 2872 "C41", 2873 "C42", 2874 "C43", 2875 "C44", 2876 "C45", 2877 "C46", 2878 "C47", 2879 "C48", 2880 "C49", 2881 "C50", 2882 "C51", 2883 "C52", 2884 "C53", 2885 "C54", 2886 "C55", 2887 "C56", 2888 "C57", 2889 "C58", 2890 "C59", 2891 "C60", 2892 "C61", 2893 "C62", 2894 "C63", 2895 "C64", 2896 "C65", 2897 "C66", 2898 "C67", 2899 "C68", 2900 "C69", 2901 "C70", 2902 "C71", 2903 "C72", 2904 "C73", 2905 "C74", 2906 "C75", 2907 "C76", 2908 "C77", 2909 "C78", 2910 "C79", 2911 "C80", 2912 "C81", 2913 "C82", 2914 "C83", 2915 "C84", 2916 "C85", 2917 "C86", 2918 "C87", 2919 "C88", 2920 "C89", 2921 "C90", 2922 "C91", 2923 "C92", 2924 "C93", 2925 "C94", 2926 "C95", 2927 "C96", 2928 "C97", 2929 "C98", 2930 "C99", 2931 } 2932 2933 valuesIssue99 = make([]interface{}, len(fieldsIssue99)) 2934 ) 2935 2936 func init() { 2937 for i := range valuesIssue99 { 2938 s := "" 2939 for _, v := range rand.Perm(32) { 2940 s += string('0' + v) 2941 } 2942 valuesIssue99[i] = s 2943 } 2944 valuesIssue99[3] = true 2945 } 2946 2947 func createTablesIssue99(db *sql.DB) error { 2948 tx, err := db.Begin() 2949 if err != nil { 2950 return err 2951 } 2952 2953 if _, err = tx.Exec(` 2954 DROP TABLE IF EXISTS Node; 2955 CREATE TABLE Node ( 2956 Datacenter string, 2957 Name string, 2958 Address string, 2959 Health bool, 2960 C0 string DEFAULT "", 2961 C1 string DEFAULT "", 2962 C2 string DEFAULT "", 2963 C3 string DEFAULT "", 2964 C4 string DEFAULT "", 2965 C5 string DEFAULT "", 2966 C6 string DEFAULT "", 2967 C7 string DEFAULT "", 2968 C8 string DEFAULT "", 2969 C9 string DEFAULT "", 2970 C10 string DEFAULT "", 2971 C11 string DEFAULT "", 2972 C12 string DEFAULT "", 2973 C13 string DEFAULT "", 2974 C14 string DEFAULT "", 2975 C15 string DEFAULT "", 2976 C16 string DEFAULT "", 2977 C17 string DEFAULT "", 2978 C18 string DEFAULT "", 2979 C19 string DEFAULT "", 2980 C20 string DEFAULT "", 2981 C21 string DEFAULT "", 2982 C22 string DEFAULT "", 2983 C23 string DEFAULT "", 2984 C24 string DEFAULT "", 2985 C25 string DEFAULT "", 2986 C26 string DEFAULT "", 2987 C27 string DEFAULT "", 2988 C28 string DEFAULT "", 2989 C29 string DEFAULT "", 2990 C30 string DEFAULT "", 2991 C31 string DEFAULT "", 2992 C32 string DEFAULT "", 2993 C33 string DEFAULT "", 2994 C34 string DEFAULT "", 2995 C35 string DEFAULT "", 2996 C36 string DEFAULT "", 2997 C37 string DEFAULT "", 2998 C38 string DEFAULT "", 2999 C39 string DEFAULT "", 3000 C40 string DEFAULT "", 3001 C41 string DEFAULT "", 3002 C42 string DEFAULT "", 3003 C43 string DEFAULT "", 3004 C44 string DEFAULT "", 3005 C45 string DEFAULT "", 3006 C46 string DEFAULT "", 3007 C47 string DEFAULT "", 3008 C48 string DEFAULT "", 3009 C49 string DEFAULT "", 3010 C50 string DEFAULT "", 3011 C51 string DEFAULT "", 3012 C52 string DEFAULT "", 3013 C53 string DEFAULT "", 3014 C54 string DEFAULT "", 3015 C55 string DEFAULT "", 3016 C56 string DEFAULT "", 3017 C57 string DEFAULT "", 3018 C58 string DEFAULT "", 3019 C59 string DEFAULT "", 3020 C60 string DEFAULT "", 3021 C61 string DEFAULT "", 3022 C62 string DEFAULT "", 3023 C63 string DEFAULT "", 3024 C64 string DEFAULT "", 3025 C65 string DEFAULT "", 3026 C66 string DEFAULT "", 3027 C67 string DEFAULT "", 3028 C68 string DEFAULT "", 3029 C69 string DEFAULT "", 3030 C70 string DEFAULT "", 3031 C71 string DEFAULT "", 3032 C72 string DEFAULT "", 3033 C73 string DEFAULT "", 3034 C74 string DEFAULT "", 3035 C75 string DEFAULT "", 3036 C76 string DEFAULT "", 3037 C77 string DEFAULT "", 3038 C78 string DEFAULT "", 3039 C79 string DEFAULT "", 3040 C80 string DEFAULT "", 3041 C81 string DEFAULT "", 3042 C82 string DEFAULT "", 3043 C83 string DEFAULT "", 3044 C84 string DEFAULT "", 3045 C85 string DEFAULT "", 3046 C86 string DEFAULT "", 3047 C87 string DEFAULT "", 3048 C88 string DEFAULT "", 3049 C89 string DEFAULT "", 3050 C90 string DEFAULT "", 3051 C91 string DEFAULT "", 3052 C92 string DEFAULT "", 3053 C93 string DEFAULT "", 3054 C94 string DEFAULT "", 3055 C95 string DEFAULT "", 3056 C96 string DEFAULT "", 3057 C97 string DEFAULT "", 3058 C98 string DEFAULT "", 3059 C99 string DEFAULT "", 3060 );`); err != nil { 3061 return err 3062 } 3063 3064 return tx.Commit() 3065 } 3066 3067 func issue99Fill(db *sql.DB) (int, error) { 3068 tx, err := db.Begin() 3069 if err != nil { 3070 return -1, err 3071 } 3072 3073 sql := "INSERT INTO Node (" + strings.Join(fieldsIssue99, ",") + ") VALUES ($1, $2, $3, $4" 3074 for i := range valuesIssue99 { 3075 if i > 3 { 3076 sql += ", $" + strconv.Itoa(i+1) 3077 } 3078 } 3079 sql += ")" 3080 3081 stmt, err := tx.Prepare(sql) 3082 if err != nil { 3083 return 0, err 3084 } 3085 3086 for i := 0; i < issue99RowsToInsert; i++ { 3087 if _, err = stmt.Exec(valuesIssue99...); err != nil { 3088 return 0, err 3089 } 3090 } 3091 3092 return issue99RowsToInsert, tx.Commit() 3093 } 3094 3095 func testIssue99(tb testing.TB, db *sql.DB) int { 3096 sum := 0 3097 for i := 0; i < issue99Cycles; i++ { 3098 if err := createTablesIssue99(db); err != nil { 3099 tb.Fatal(err) 3100 } 3101 3102 n2, err := issue99Fill(db) 3103 if err != nil { 3104 tb.Fatal(err) 3105 } 3106 3107 sum += n2 3108 } 3109 return sum 3110 } 3111 3112 var benchmarkIssue99 sync.Once 3113 3114 func BenchmarkIssue99(b *testing.B) { 3115 if testing.Verbose() { 3116 benchProlog(b) 3117 benchmarkIssue99.Do(func() { 3118 b.Logf(`1 op == (Re)create a 100+ column table, fill it with %d records. Repeat %d times. 3119 3120 `, issue99RowsToInsert, issue99Cycles) 3121 }) 3122 } 3123 RegisterMemDriver() 3124 db, err := sql.Open("ql-mem", "issue99") 3125 if err != nil { 3126 b.Fatal(err) 3127 } 3128 3129 b.ResetTimer() 3130 recs := 0 3131 for i := 0; i < b.N; i++ { 3132 recs = testIssue99(b, db) 3133 } 3134 b.SetBytes(int64(recs) * benchScale) 3135 } 3136 3137 func TestIssue108(t *testing.T) { 3138 db, err := OpenMem() 3139 if err != nil { 3140 t.Fatal(err) 3141 } 3142 3143 setup := `BEGIN TRANSACTION; 3144 CREATE TABLE people (name string NOT NULL); 3145 INSERT INTO people VALUES ("alice"), ("bob"); 3146 ` 3147 ctx := NewRWCtx() 3148 _, _, err = db.Run(ctx, setup) 3149 if err != nil { 3150 t.Fatal(err) 3151 } 3152 3153 rs, _, err := db.Run(ctx, "SELECT count() FROM people;") 3154 if err != nil { 3155 t.Fatal(err) 3156 } 3157 3158 row, err := rs[0].FirstRow() // Used to block forever. 3159 if err != nil { 3160 t.Fatal(err) 3161 } 3162 3163 n, ok := row[0].(int64) 3164 if !ok || n != 2 { 3165 t.Fatal(n, 2) 3166 } 3167 3168 _, _, err = db.Run(ctx, "COMMIT;") 3169 if err != nil { 3170 t.Fatal(err) 3171 } 3172 } 3173 3174 type issue109 struct { 3175 *testing.T 3176 db *DB 3177 } 3178 3179 func (t issue109) test(doIndex bool) { 3180 t.Logf("Use index: %v", doIndex) 3181 var err error 3182 if t.db, err = OpenMem(); err != nil { 3183 t.Fatal(err) 3184 } 3185 3186 if _, _, err := t.db.Run(NewRWCtx(), ` 3187 BEGIN TRANSACTION; 3188 CREATE TABLE people (name string NOT NULL); 3189 CREATE TABLE awards (name string NOT NULL); 3190 CREATE TABLE people_awards (person_id int NOT NULL, award_id int NOT NULL); 3191 COMMIT; 3192 `); err != nil { 3193 t.Fatal(err) 3194 } 3195 3196 if doIndex { 3197 t.createBuggyIndex() 3198 } 3199 3200 pid1 := t.createPerson("alice") 3201 3202 aid1 := t.createAward("awesome") 3203 aid2 := t.createAward("best") 3204 3205 zeroFullJoinCount := t.countFullJoin(pid1) 3206 if zeroFullJoinCount != 0 { 3207 t.Fatal(zeroFullJoinCount, "Incorrect full join count before creating records") 3208 } 3209 3210 t.insertPersonAward(pid1, aid1) 3211 t.insertPersonAward(pid1, aid2) 3212 3213 initialFullJoinCount := t.countFullJoin(pid1) 3214 if initialFullJoinCount != 2 { 3215 t.Fatal(initialFullJoinCount, "Incorrect full join count before deleting records") 3216 } 3217 3218 initialNumJoinRecords := t.countJoinRecords() 3219 if initialNumJoinRecords != 2 { 3220 t.Fatal(initialNumJoinRecords, "Incorrect number of join records before deleting records") 3221 } 3222 3223 t.deletePersonAwards(pid1) 3224 3225 afterNumJoinRecords := t.countJoinRecords() 3226 if afterNumJoinRecords != 0 { 3227 t.Fatal(afterNumJoinRecords, "Incorrect number of join records after deleting records") 3228 } 3229 3230 afterFullJoinCount := t.countFullJoin(pid1) 3231 if afterFullJoinCount != 0 { 3232 t.Fatal(afterFullJoinCount, "Incorrect full join count after deleting records") 3233 } 3234 3235 t.Logf("OK") 3236 } 3237 3238 func (t issue109) createBuggyIndex() { 3239 if _, _, err := t.db.Run(NewRWCtx(), ` 3240 BEGIN TRANSACTION; 3241 CREATE INDEX people_awards_person_id ON people_awards (person_id); 3242 COMMIT; 3243 `); err != nil { 3244 t.Fatal(err) 3245 } 3246 t.Log("CREATE INDEX people_awards_person_id ON people_awards (person_id);") 3247 } 3248 3249 func (t issue109) createPerson(name string) int64 { 3250 ctx := NewRWCtx() 3251 _, _, err := t.db.Run(ctx, ` 3252 BEGIN TRANSACTION; 3253 INSERT INTO people(name) VALUES ($1); 3254 COMMIT;`, 3255 name, 3256 ) 3257 if err != nil { 3258 t.Fatal(err) 3259 } 3260 3261 t.Logf("INSERT INTO people(name) VALUES (%q); -> ID %v", name, ctx.LastInsertID) 3262 return ctx.LastInsertID 3263 } 3264 3265 func (t issue109) createAward(name string) int64 { 3266 ctx := NewRWCtx() 3267 _, _, err := t.db.Run(ctx, ` 3268 BEGIN TRANSACTION; 3269 INSERT INTO awards(name) VALUES ($1); 3270 COMMIT`, 3271 name, 3272 ) 3273 if err != nil { 3274 t.Fatal(err) 3275 } 3276 3277 t.Logf("INSERT INTO awards(name) VALUES (%q); -> ID %v", name, ctx.LastInsertID) 3278 return ctx.LastInsertID 3279 } 3280 3281 func (t issue109) countFullJoin(personID int64) int { 3282 stmt := ` 3283 SELECT 3284 * 3285 FROM 3286 awards 3287 FULL JOIN 3288 people_awards 3289 ON 3290 id(awards) == people_awards.award_id 3291 WHERE 3292 people_awards.person_id == $1 3293 ` 3294 rs, _, err := t.db.Run(nil, "explain "+stmt, personID) 3295 if err != nil { 3296 t.Fatal(err) 3297 } 3298 3299 rows, err := rs[0].Rows(-1, 0) 3300 if err != nil { 3301 t.Fatal(err) 3302 } 3303 3304 t.Log("----") 3305 for _, v := range rows { 3306 t.Log(v) 3307 } 3308 3309 if rs, _, err = t.db.Run(nil, stmt, personID); err != nil { 3310 t.Fatal(err) 3311 } 3312 3313 if rows, err = rs[0].Rows(-1, 0); err != nil { 3314 t.Fatal(err) 3315 } 3316 3317 for i, v := range rows { 3318 t.Logf("%v/%v: %v", i, len(rows), v) 3319 } 3320 t.Log("----") 3321 return len(rows) 3322 } 3323 3324 func (t issue109) insertPersonAward(personID, awardID int64) { 3325 ctx := NewRWCtx() 3326 _, _, err := t.db.Run(ctx, ` 3327 BEGIN TRANSACTION; 3328 INSERT INTO people_awards(person_id, award_id) VALUES ($1, $2); 3329 COMMIT;`, 3330 personID, awardID, 3331 ) 3332 if err != nil { 3333 t.Fatal(err) 3334 } 3335 t.Logf("INSERT INTO people_awards(person_id, award_id) VALUES (%v, %v);", personID, awardID) 3336 } 3337 3338 func (t issue109) countJoinRecords() int64 { 3339 rs, _, err := t.db.Run(nil, ` 3340 SELECT 3341 count() 3342 FROM 3343 people_awards; 3344 `) 3345 if err != nil { 3346 t.Fatal(err) 3347 } 3348 3349 row, err := rs[0].FirstRow() 3350 if err != nil { 3351 t.Fatal(err) 3352 } 3353 3354 return row[0].(int64) 3355 } 3356 3357 func (t issue109) deletePersonAwards(personID int64) { 3358 ctx := NewRWCtx() 3359 _, _, err := t.db.Run(ctx, ` 3360 BEGIN TRANSACTION; 3361 DELETE FROM people_awards WHERE person_id == $1; 3362 COMMIT`, 3363 personID, 3364 ) 3365 if err != nil { 3366 t.Fatal(err) 3367 } 3368 3369 if ctx.RowsAffected != 2 { 3370 t.Fatal("Did not delete rows as expected") 3371 } 3372 t.Logf("DELETE FROM people_awards WHERE person_id == %v;", personID) 3373 } 3374 3375 func TestIssue109(t *testing.T) { 3376 (issue109{T: t}).test(false) 3377 (issue109{T: t}).test(true) 3378 } 3379 3380 // https://github.com/cznic/ql/issues/142 3381 func TestIssue142(t *testing.T) { 3382 cwd, err := os.Getwd() 3383 if err != nil { 3384 t.Fatal(err) 3385 } 3386 3387 defer os.Chdir(cwd) 3388 3389 wd, err := ioutil.TempDir("", "ql-test-issue-142") 3390 if err != nil { 3391 t.Fatal(err) 3392 } 3393 3394 defer os.RemoveAll(wd) 3395 3396 if err := os.Chdir(wd); err != nil { 3397 t.Fatal(err) 3398 } 3399 3400 RegisterDriver() 3401 for _, nm := range []string{"test.db", "./test.db", "another.db"} { 3402 t.Log(nm) 3403 db, err := sql.Open("ql", nm) 3404 if err != nil { 3405 t.Fatal(err) 3406 } 3407 3408 tx, err := db.Begin() 3409 if err != nil { 3410 t.Fatal(err) 3411 } 3412 3413 if _, err := tx.Exec("drop table if exists t; create table t (c int)"); err != nil { 3414 t.Fatal(err) 3415 } 3416 3417 if err := tx.Commit(); err != nil { 3418 t.Fatal(err) 3419 } 3420 3421 if err := db.Close(); err != nil { 3422 t.Fatal(err) 3423 } 3424 3425 fn := filepath.Join(wd, nm) 3426 fi, err := os.Stat(fn) 3427 if err != nil { 3428 t.Fatal(err) 3429 } 3430 3431 t.Log(fn, fi.Size()) 3432 if fi.Size() == 0 { 3433 t.Fatal("DB is empty") 3434 } 3435 } 3436 }