1 # 2010 February 16 2 # 3 # The author disclaims copyright to this source code. In place of 4 # a legal notice, here is a blessing: 5 # 6 # May you do good and not evil. 7 # May you find forgiveness for yourself and forgive others. 8 # May you share freely, never taking more than you give. 9 # 10 #*********************************************************************** 11 # 12 # 13 14 if {![info exists testdir]} { 15 set testdir [file join [file dirname [info script]] .. .. test] 16 } 17 source $testdir/tester.tcl 18 ifcapable !rtree { finish_test ; return } 19 20 #------------------------------------------------------------------------- 21 # The following block of tests - rtree8-1.* - feature reading and writing 22 # an r-tree table while there exist open cursors on it. 23 # 24 proc populate_t1 {n} { 25 execsql { DELETE FROM t1 } 26 for {set i 1} {$i <= $n} {incr i} { 27 execsql { INSERT INTO t1 VALUES($i, $i, $i+2) } 28 } 29 } 30 31 # A DELETE while a cursor is reading the table. 32 # 33 do_test rtree8-1.1.1 { 34 execsql { PRAGMA page_size = 512 } 35 execsql { CREATE VIRTUAL TABLE t1 USING rtree_i32(id, x1, x2) } 36 populate_t1 5 37 } {} 38 do_test rtree8-1.1.2 { 39 set res [list] 40 db eval { SELECT * FROM t1 } { 41 lappend res $x1 $x2 42 if {$id==3} { db eval { DELETE FROM t1 WHERE id>3 } } 43 } 44 set res 45 } {1 3 2 4 3 5} 46 do_test rtree8-1.1.3 { 47 execsql { SELECT * FROM t1 } 48 } {1 1 3 2 2 4 3 3 5} 49 50 # Many SELECTs on the same small table. 51 # 52 proc nested_select {n} { 53 set ::max $n 54 db eval { SELECT * FROM t1 } { 55 if {$id == $n} { nested_select [expr $n+1] } 56 } 57 return $::max 58 } 59 do_test rtree8-1.2.1 { populate_t1 50 } {} 60 do_test rtree8-1.2.2 { nested_select 1 } {51} 61 62 # This test runs many SELECT queries simultaneously against a large 63 # table, causing a collision in the hash-table used to store r-tree 64 # nodes internally. 65 # 66 populate_t1 1500 67 do_execsql_test rtree8-1.3.1 { SELECT max(nodeno) FROM t1_node } {164} 68 do_test rtree8-1.3.2 { 69 set rowids [execsql {SELECT min(rowid) FROM t1_rowid GROUP BY nodeno}] 70 set stmt_list [list] 71 foreach row $rowids { 72 set stmt [sqlite3_prepare db "SELECT * FROM t1 WHERE id = $row" -1 tail] 73 sqlite3_step $stmt 74 lappend res_list [sqlite3_column_int $stmt 0] 75 lappend stmt_list $stmt 76 } 77 } {} 78 do_test rtree8-1.3.3 { set res_list } $rowids 79 do_execsql_test rtree8-1.3.4 { SELECT count(*) FROM t1 } {1500} 80 do_test rtree8-1.3.5 { 81 foreach stmt $stmt_list { sqlite3_finalize $stmt } 82 } {} 83 84 85 #------------------------------------------------------------------------- 86 # The following block of tests - rtree8-2.* - test a couple of database 87 # corruption cases. In this case things are not corrupted at the b-tree 88 # level, but the contents of the various tables used internally by an 89 # r-tree table are inconsistent. 90 # 91 populate_t1 50 92 do_execsql_test rtree8-2.1.1 { SELECT max(nodeno) FROM t1_node } {5} 93 do_execsql_test rtree8-2.1.2 { DELETE FROM t1_node } {} 94 for {set i 1} {$i <= 50} {incr i} { 95 do_catchsql_test rtree8-2.1.3.$i { 96 SELECT * FROM t1 WHERE id = $i 97 } {1 {database disk image is malformed}} 98 } 99 do_catchsql_test rtree8-2.1.4 { 100 SELECT * FROM t1 101 } {1 {database disk image is malformed}} 102 do_catchsql_test rtree8-2.1.5 { 103 DELETE FROM t1 104 } {1 {database disk image is malformed}} 105 106 do_execsql_test rtree8-2.1.6 { 107 DROP TABLE t1; 108 CREATE VIRTUAL TABLE t1 USING rtree_i32(id, x1, x2); 109 } {} 110 111 112 populate_t1 50 113 do_execsql_test rtree8-2.2.1 { 114 DELETE FROM t1_parent 115 } {} 116 do_catchsql_test rtree8-2.2.2 { 117 DELETE FROM t1 WHERE id=25 118 } {1 {database disk image is malformed}} 119 do_execsql_test rtree8-2.2.3 { 120 DROP TABLE t1; 121 CREATE VIRTUAL TABLE t1 USING rtree_i32(id, x1, x2); 122 } {} 123 124 125 #------------------------------------------------------------------------- 126 # Test that trying to use the MATCH operator with the r-tree module does 127 # not confuse it. 128 # 129 populate_t1 10 130 do_catchsql_test rtree8-3.1 { 131 SELECT * FROM t1 WHERE x1 MATCH '1234' 132 } {1 {SQL logic error or missing database}} 133 134 #------------------------------------------------------------------------- 135 # Test a couple of invalid arguments to rtreedepth(). 136 # 137 do_catchsql_test rtree8-4.1 { 138 SELECT rtreedepth('hello world') 139 } {1 {Invalid argument to rtreedepth()}} 140 do_catchsql_test rtree8-4.2 { 141 SELECT rtreedepth(X'00') 142 } {1 {Invalid argument to rtreedepth()}} 143 144 145 #------------------------------------------------------------------------- 146 # Delete half of a lopsided tree. 147 # 148 do_execsql_test rtree8-5.1 { 149 CREATE VIRTUAL TABLE t2 USING rtree_i32(id, x1, x2) 150 } {} 151 do_test rtree8-5.2 { 152 execsql BEGIN 153 for {set i 0} {$i < 100} {incr i} { 154 execsql { INSERT INTO t2 VALUES($i, 100, 101) } 155 } 156 for {set i 100} {$i < 200} {incr i} { 157 execsql { INSERT INTO t2 VALUES($i, 1000, 1001) } 158 } 159 execsql COMMIT 160 } {} 161 do_test rtree8-5.3 { 162 execsql BEGIN 163 for {set i 0} {$i < 200} {incr i} { 164 execsql { DELETE FROM t2 WHERE id = $i } 165 } 166 execsql COMMIT 167 } {} 168 169 170 finish_test 171 172
