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