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