4 # The author disclaims copyright to this source code. In place of
5 # a legal notice, here is a blessing:
7 # May you do good and not evil.
8 # May you find forgiveness for yourself and forgive others.
9 # May you share freely, never taking more than you give.
11 #***********************************************************************
12 # This file implements regression tests for SQLite library. The
13 # focus of this script is btree database backend
15 # $Id: btree.test,v 1.15 2004/02/10 01:54:28 drh Exp $
18 set testdir [file dirname $argv0]
19 source $testdir/tester.tcl
21 if {[info commands btree_open]!="" && $SQLITE_PAGE_SIZE==1024
22 && $SQLITE_USABLE_SIZE==1024} {
24 # Basic functionality. Open and close a database.
27 file delete -force test1.bt
28 file delete -force test1.bt-journal
29 set rc [catch {btree_open test1.bt} ::b1]
32 # The second element of the list returned by btree_pager_stats is the
33 # number of pages currently checked out. We'll be checking this value
34 # frequently during this test script, to make sure the btree library
35 # is properly releasing the pages it checks out, and thus avoiding
39 lindex [btree_pager_stats $::b1] 1
42 set rc [catch {btree_open test1.bt} ::b2]
45 set rc [catch {btree_close $::b2} msg]
49 # Do an insert and verify that the database file grows in size.
52 set rc [catch {btree_begin_transaction $::b1} msg]
56 lindex [btree_pager_stats $::b1] 1
59 set rc [catch {btree_cursor $::b1 2 1} ::c1]
60 if {$rc} {lappend rc $::c1}
64 set rc [catch {btree_insert $::c1 one 1.00} msg]
74 set rc [catch {btree_close_cursor $::c1} msg]
78 set rc [catch {btree_commit $::b1} msg]
85 lindex [btree_pager_stats $::b1] 1
88 # Reopen the database and attempt to read the record that we wrote.
91 set rc [catch {btree_cursor $::b1 2 1} ::c1]
92 if {$rc} {lappend rc $::c1}
96 btree_move_to $::c1 abc
99 btree_move_to $::c1 xyz
102 btree_move_to $::c1 one
111 lindex [btree_pager_stats $::b1] 1
114 # Do some additional inserts
117 btree_begin_transaction $::b1
118 btree_insert $::c1 two 2.00
121 do_test btree-3.1.1 {
122 lindex [btree_pager_stats $::b1] 1
125 btree_insert $::c1 three 3.00
129 btree_insert $::c1 four 4.00
133 btree_insert $::c1 five 5.00
137 btree_insert $::c1 six 6.00
140 #btree_page_dump $::b1 2
142 set rc [btree_move_to $::c1 {}]
194 # Commit the changes, reopen and reread the data
197 set rc [catch {btree_close_cursor $::c1} msg]
200 do_test btree-3.22.1 {
201 lindex [btree_pager_stats $::b1] 1
204 set rc [catch {btree_commit $::b1} msg]
207 do_test btree-3.23.1 {
208 lindex [btree_pager_stats $::b1] 1
214 set rc [catch {btree_cursor $::b1 2 1} ::c1]
215 if {$rc} {lappend rc $::c1}
218 do_test btree-3.25.1 {
219 lindex [btree_pager_stats $::b1] 1
222 set rc [btree_move_to $::c1 {}]
274 lindex [btree_pager_stats $::b1] 1
281 btree_begin_transaction $::b1
282 btree_move_to $::c1 one
285 do_test btree-4.1.1 {
286 lindex [btree_pager_stats $::b1] 1
303 btree_move_to $::c1 {}
306 set key [btree_key $::c1]
309 lappend r [btree_data $::c1]
313 } {five 5.00 four 4.00 six 6.00 three 3.00 two 2.00}
315 # Commit and make sure the delete is still there.
319 btree_move_to $::c1 {}
322 set key [btree_key $::c1]
325 lappend r [btree_data $::c1]
329 } {five 5.00 four 4.00 six 6.00 three 3.00 two 2.00}
331 # Completely close the database and reopen it. Then check
335 lindex [btree_pager_stats $::b1] 1
338 btree_close_cursor $::c1
339 lindex [btree_pager_stats $::b1] 1
343 set ::b1 [btree_open test1.bt]
344 set ::c1 [btree_cursor $::b1 2 1]
345 lindex [btree_pager_stats $::b1] 1
351 set key [btree_key $::c1]
354 lappend r [btree_data $::c1]
358 } {five 5.00 four 4.00 six 6.00 three 3.00 two 2.00}
360 # Try to read and write meta data
364 } {0 0 0 0 0 0 0 0 0 0}
366 set rc [catch {btree_update_meta $::b1 1 2 3 4 5 6 7 8 9 10} msg]
370 btree_begin_transaction $::b1
371 set rc [catch {btree_update_meta $::b1 1 2 3 4 5 6 7 8 9 10} msg]
376 } {0 2 3 4 5 6 7 8 9 10}
378 btree_close_cursor $::c1
381 } {0 0 0 0 0 0 0 0 0 0}
383 btree_begin_transaction $::b1
384 btree_update_meta $::b1 999 10 20 30 40 50 60 70 80 90
387 } {0 10 20 30 40 50 60 70 80 90}
389 proc select_all {cursor} {
391 btree_move_to $cursor {}
393 set key [btree_key $cursor]
396 lappend r [btree_data $cursor]
401 proc select_keys {cursor} {
403 btree_move_to $cursor {}
405 set key [btree_key $cursor]
413 # Try to create a new table in the database file
416 set rc [catch {btree_create_table $::b1} msg]
420 btree_begin_transaction $::b1
421 set ::t2 [btree_create_table $::b1]
423 do_test btree-6.2.1 {
424 lindex [btree_pager_stats $::b1] 1
426 do_test btree-6.2.2 {
427 set ::c2 [btree_cursor $::b1 $::t2 1]
428 lindex [btree_pager_stats $::b1] 1
430 do_test btree-6.2.3 {
431 btree_insert $::c2 ten 10
436 set ::c1 [btree_cursor $::b1 2 1]
437 lindex [btree_pager_stats $::b1] 1
439 do_test btree-6.3.1 {
441 } {five 5.00 four 4.00 six 6.00 three 3.00 two 2.00}
442 #btree_page_dump $::b1 3
447 # Drop the new table, then create it again anew.
450 btree_begin_transaction $::b1
453 btree_close_cursor $::c2
455 do_test btree-6.6.1 {
456 lindex [btree_pager_stats $::b1] 1
459 btree_drop_table $::b1 $::t2
461 do_test btree-6.7.1 {
462 lindex [btree_get_meta $::b1] 0
465 set ::t2 [btree_create_table $::b1]
467 do_test btree-6.8.1 {
468 lindex [btree_get_meta $::b1] 0
471 set ::c2 [btree_cursor $::b1 $::t2 1]
472 lindex [btree_pager_stats $::b1] 1
475 do_test btree-6.9.1 {
476 btree_move_to $::c2 {}
480 # If we drop table 2 it just clears the table. Table 2 always exists.
483 btree_close_cursor $::c1
484 btree_drop_table $::b1 2
485 set ::c1 [btree_cursor $::b1 2 1]
486 btree_move_to $::c1 {}
497 btree_close_cursor $::c2
498 lindex [btree_pager_stats $::b1] 1
501 # Check to see that pages defragment properly. To do this test we will
503 # 1. Fill the first page table 2 with data.
504 # 2. Delete every other entry of table 2.
505 # 3. Insert a single entry that requires more contiguous
506 # space than is available.
509 btree_begin_transaction $::b1
514 for {set i 0} {$i<36} {incr i} {
515 set key [format %03d $i]
516 set data "*** $key ***"
517 btree_insert $::c1 $key $data
519 lrange [btree_cursor_dump $::c1] 4 5
522 btree_move_to $::c1 000
523 while {[btree_key $::c1]!=""} {
528 lrange [btree_cursor_dump $::c1] 4 5
530 #btree_page_dump $::b1 2
532 btree_insert $::c1 018 {*** 018 ***+++}
536 lrange [btree_cursor_dump $::c1] 4 5
538 #btree_page_dump $::b1 2
540 # Delete an entry to make a hole of a known size, then immediately recreate
541 # that entry. This tests the path into allocateSpace where the hole exactly
542 # matches the size of the desired space.
545 btree_move_to $::c1 007
547 btree_move_to $::c1 011
551 lindex [btree_cursor_dump $::c1] 5
553 #btree_page_dump $::b1 2
555 btree_insert $::c1 007 {*** 007 ***}
556 lindex [btree_cursor_dump $::c1] 5
558 #btree_page_dump $::b1 2
560 # Make sure the freeSpace() routine properly coaleses adjacent memory blocks
563 btree_move_to $::c1 013
565 lrange [btree_cursor_dump $::c1] 4 5
568 btree_move_to $::c1 009
570 lrange [btree_cursor_dump $::c1] 4 5
573 btree_move_to $::c1 018
575 lrange [btree_cursor_dump $::c1] 4 5
578 btree_move_to $::c1 033
580 lrange [btree_cursor_dump $::c1] 4 5
583 btree_move_to $::c1 035
585 lrange [btree_cursor_dump $::c1] 4 5
587 #btree_page_dump $::b1 2
589 lindex [btree_pager_stats $::b1] 1
592 # Check to see that data on overflow pages work correctly.
595 set data "*** This is a very long key "
596 while {[string length $data]<256} {append data $data}
598 btree_insert $::c1 020 $data
600 #btree_page_dump $::b1 2
601 do_test btree-8.1.1 {
602 lindex [btree_pager_stats $::b1] 1
604 #btree_pager_ref_dump $::b1
606 string length [btree_data $::c1]
607 } [string length $::data]
614 do_test btree-8.4.1 {
615 lindex [btree_get_meta $::b1] 0
616 } [expr {int(([string length $::data]-238+1019)/1020)}]
618 set data "*** This is an even longer key"
619 while {[string length $data]<2000} {append data $data}
621 btree_insert $::c1 020 $data
624 string length [btree_data $::c1]
625 } [string length $::data]
634 btree_close_cursor $::c1
636 set ::b1 [btree_open test1.bt]
637 set ::c1 [btree_cursor $::b1 2 1]
638 btree_move_to $::c1 020
642 btree_begin_transaction $::b1
646 lindex [btree_get_meta $::b1] 0
647 } [expr {int(([string length $::data]-238+1019)/1020)}]
649 # Now check out keys on overflow pages.
652 set ::keyprefix "This is a long prefix to a key "
653 while {[string length $::keyprefix]<256} {append ::keyprefix $::keyprefix}
654 btree_close_cursor $::c1
655 btree_drop_table $::b1 2
656 lindex [btree_get_meta $::b1] 0
658 do_test btree-8.12.1 {
659 set ::c1 [btree_cursor $::b1 2 1]
660 btree_insert $::c1 ${::keyprefix}1 1
667 btree_insert $::c1 ${::keyprefix}2 2
668 btree_insert $::c1 ${::keyprefix}3 3
672 btree_move_to $::c1 ${::keyprefix}2
676 btree_move_to $::c1 ${::keyprefix}1
680 btree_move_to $::c1 ${::keyprefix}3
684 lindex [btree_get_meta $::b1] 0
687 btree_move_to $::c1 ${::keyprefix}2
690 #btree_page_dump $::b1 2
696 #btree_page_dump $::b1 2
698 lindex [btree_get_meta $::b1] 0
701 lindex [btree_pager_stats $::b1] 1
704 btree_close_cursor $::c1
705 btree_drop_table $::b1 2
706 set ::c1 [btree_cursor $::b1 2 1]
707 lindex [btree_get_meta $::b1] 0
710 lindex [btree_pager_stats $::b1] 1
712 #btree_pager_ref_dump $::b1
714 # Check page splitting logic
717 for {set i 1} {$i<=19} {incr i} {
718 set key [format %03d $i]
719 set data "*** $key *** $key *** $key *** $key ***"
720 btree_insert $::c1 $key $data
723 #btree_tree_dump $::b1 2
724 #btree_pager_ref_dump $::b1
725 #set pager_refinfo_enable 1
727 btree_insert $::c1 020 {*** 020 *** 020 *** 020 *** 020 ***}
729 } {001 002 003 004 005 006 007 008 009 010 011 012 013 014 015 016 017 018 019 020}
730 #btree_page_dump $::b1 5
731 #btree_page_dump $::b1 2
732 #btree_page_dump $::b1 7
733 #btree_pager_ref_dump $::b1
734 #set pager_refinfo_enable 0
736 # The previous "select_keys" command left the cursor pointing at the root
737 # page. So there should only be two pages checked out. 2 (the root) and
739 do_test btree-9.2.1 {
740 lindex [btree_pager_stats $::b1] 1
742 for {set i 1} {$i<=20} {incr i} {
743 do_test btree-9.3.$i.1 [subst {
744 btree_move_to $::c1 [format %03d $i]
747 do_test btree-9.3.$i.2 [subst {
748 btree_move_to $::c1 [format %03d $i]
749 string range \[btree_data $::c1\] 0 10
750 }] "*** [format %03d $i] ***"
752 do_test btree-9.4.1 {
753 lindex [btree_pager_stats $::b1] 1
756 # Check the page joining logic.
758 #btree_page_dump $::b1 2
759 #btree_pager_ref_dump $::b1
760 do_test btree-9.4.2 {
761 btree_move_to $::c1 005
764 #btree_page_dump $::b1 2
765 for {set i 1} {$i<=19} {incr i} {
767 do_test btree-9.5.$i.1 [subst {
768 btree_move_to $::c1 [format %03d $i]
771 do_test btree-9.5.$i.2 [subst {
772 btree_move_to $::c1 [format %03d $i]
773 string range \[btree_data $::c1\] 0 10
774 }] "*** [format %03d $i] ***"
776 #btree_pager_ref_dump $::b1
778 btree_close_cursor $::c1
779 lindex [btree_pager_stats $::b1] 1
783 lindex [btree_pager_stats $::b1] 1
786 # Create a tree of depth two. That is, there is a single divider entry
787 # on the root pages and two leaf pages. Then delete the divider entry
791 btree_begin_transaction $::b1
792 btree_drop_table $::b1 2
793 lindex [btree_pager_stats $::b1] 1
796 set ::c1 [btree_cursor $::b1 2 1]
797 lindex [btree_pager_stats $::b1] 1
800 for {set i 1} {$i<=20} {incr i} {
801 set key [format %03d $i]
802 set data "*** $key *** $key *** $key *** $key ***"
803 btree_insert $::c1 $key $data
806 } {001 002 003 004 005 006 007 008 009 010 011 012 013 014 015 016 017 018 019 020}
807 #btree_page_dump $::b1 7
808 #btree_page_dump $::b1 2
809 #btree_page_dump $::b1 6
811 btree_move_to $::c1 011
814 } {001 002 003 004 005 006 007 008 009 010 012 013 014 015 016 017 018 019 020}
815 #btree_tree_dump $::b1 2
816 #btree_pager_ref_dump $::b1
817 for {set i 1} {$i<=20} {incr i} {
818 do_test btree-10.5.$i {
819 btree_move_to $::c1 [format %03d $i]
820 lindex [btree_pager_stats $::b1] 1
822 #btree_pager_ref_dump $::b1
823 #btree_tree_dump $::b1 2
826 # Create a tree with lots more pages
830 for {set i 21} {$i<=1000} {incr i} {
831 do_test btree-11.1.$i.1 {
832 set key [format %03d $i]
833 set ::data "*** $key *** $key *** $key *** $key ***"
834 btree_insert $::c1 $key $data
837 do_test btree-11.1.$i.2 {
840 set ::key [format %03d [expr {$i/2}]]
841 if {$::key=="011"} {set ::key 010}
842 do_test btree-11.1.$i.3 {
843 btree_move_to $::c1 $::key
850 # Make sure our reference count is still correct.
853 btree_close_cursor $::c1
854 lindex [btree_pager_stats $::b1] 1
857 set ::c1 [btree_cursor $::b1 2 1]
858 lindex [btree_pager_stats $::b1] 1
860 #btree_page_dump $::b1 2
862 # Delete the dividers on the root page
865 btree_move_to $::c1 257
870 do_test btree-11.4.1 {
871 btree_move_to $::c1 256
874 do_test btree-11.4.2 {
875 btree_move_to $::c1 258
878 do_test btree-11.4.3 {
879 btree_move_to $::c1 259
882 do_test btree-11.4.4 {
883 btree_move_to $::c1 257
884 set n [btree_key $::c1]
885 expr {$n==256||$n==258}
888 btree_move_to $::c1 513
893 do_test btree-11.5.1 {
894 btree_move_to $::c1 512
897 do_test btree-11.5.2 {
898 btree_move_to $::c1 514
901 do_test btree-11.5.3 {
902 btree_move_to $::c1 515
905 do_test btree-11.5.4 {
906 btree_move_to $::c1 513
907 set n [btree_key $::c1]
908 expr {$n==512||$n==514}
911 btree_move_to $::c1 769
916 do_test btree-11.6.1 {
917 btree_move_to $::c1 768
920 do_test btree-11.6.2 {
921 btree_move_to $::c1 771
924 do_test btree-11.6.3 {
925 btree_move_to $::c1 770
928 do_test btree-11.6.4 {
929 btree_move_to $::c1 769
930 set n [btree_key $::c1]
931 expr {$n==768||$n==770}
933 #btree_page_dump $::b1 2
934 #btree_page_dump $::b1 25
936 # Change the data on an intermediate node such that the node becomes overfull
937 # and has to split. We happen to know that intermediate nodes exist on
938 # 337, 401 and 465 by the btree_page_dumps above
941 set ::data {This is going to be a very long data segment}
942 append ::data $::data
943 append ::data $::data
945 btree_insert $::c1 337 $::data
949 btree_insert $::c1 401 $::data
953 btree_insert $::c1 465 $::data
957 btree_move_to $::c1 337
968 btree_move_to $::c1 464
979 do_test btree-12.10 {
980 btree_move_to $::c1 400
983 do_test btree-12.11 {
987 do_test btree-12.12 {
992 btree_integrity_check $::b1 2 3
997 # 1. Do some deletes from the 3-layer tree
998 # 2. Commit and reopen the database
999 # 3. Read every 15th entry and make sure it works
1000 # 4. Implement btree_sanity and put it throughout this script
1003 do_test btree-15.98 {
1004 btree_close_cursor $::c1
1005 lindex [btree_pager_stats $::b1] 1
1007 do_test btree-15.99 {
1008 btree_rollback $::b1
1009 lindex [btree_pager_stats $::b1] 1
1011 btree_pager_ref_dump $::b1
1013 do_test btree-99.1 {
1019 } ;# end if( not mem: and has pager_open command );