gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/swarmvtab.test (about) 1 # 2017-07-15 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 # This file implements regression tests for SQLite library. The 12 # focus of this file is the "swarmvtab" extension 13 # 14 15 set testdir [file dirname $argv0] 16 source $testdir/tester.tcl 17 set testprefix swarmvtab 18 do_not_use_codec 19 20 ifcapable !vtab { 21 finish_test 22 return 23 } 24 25 load_static_extension db unionvtab 26 27 set nFile $sqlite_open_file_count 28 29 do_execsql_test 1.0 { 30 CREATE TABLE t0(a INTEGER PRIMARY KEY, b TEXT); 31 WITH s(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<400) 32 INSERT INTO t0 SELECT i, hex(randomblob(50)) FROM s; 33 34 CREATE TABLE dir(f, t, imin, imax); 35 } 36 37 do_test 1.1 { 38 for {set i 0} {$i < 40} {incr i} { 39 set iMin [expr $i*10 + 1] 40 set iMax [expr $iMin+9] 41 42 forcedelete "test.db$i" 43 execsql [subst { 44 ATTACH 'test.db$i' AS aux; 45 CREATE TABLE aux.t$i (a INTEGER PRIMARY KEY, b TEXT); 46 INSERT INTO aux.t$i SELECT * FROM t0 WHERE a BETWEEN $iMin AND $iMax; 47 DETACH aux; 48 INSERT INTO dir VALUES('test.db$i', 't$i', $iMin, $iMax); 49 }] 50 } 51 52 execsql { 53 CREATE VIRTUAL TABLE temp.s1 USING swarmvtab('SELECT * FROM dir'); 54 } 55 } {} 56 57 do_execsql_test 1.2 { 58 DROP TABLE s1; 59 } {} 60 61 do_execsql_test 1.3 { 62 CREATE VIRTUAL TABLE temp.s1 USING swarmvtab('SELECT * FROM dir'); 63 SELECT count(*) FROM s1 WHERE rowid<50; 64 } {49} 65 66 proc do_compare_test {tn where} { 67 set sql [subst { 68 SELECT (SELECT group_concat(a || ',' || b, ',') FROM t0 WHERE $where) 69 IS 70 (SELECT group_concat(a || ',' || b, ',') FROM s1 WHERE $where) 71 }] 72 73 uplevel [list do_execsql_test $tn $sql 1] 74 } 75 76 do_compare_test 1.4.1 "rowid = 700" 77 do_compare_test 1.4.2 "rowid = -1" 78 do_compare_test 1.4.3 "rowid = 0" 79 do_compare_test 1.4.4 "rowid = 55" 80 do_compare_test 1.4.5 "rowid BETWEEN 20 AND 100" 81 do_compare_test 1.4.6 "rowid > 350" 82 do_compare_test 1.4.7 "rowid >= 350" 83 do_compare_test 1.4.8 "rowid >= 200" 84 do_compare_test 1.4.9 "1" 85 86 # Multiple simultaneous cursors. 87 # 88 do_execsql_test 1.5.1.(5-seconds-or-so) { 89 SELECT count(*) FROM s1 a, s1 b WHERE b.rowid<=200; 90 } {80000} 91 do_execsql_test 1.5.2 { 92 SELECT count(*) FROM s1 a, s1 b, s1 c 93 WHERE a.rowid=b.rowid AND b.rowid=c.rowid; 94 } {400} 95 96 # Empty source tables. 97 # 98 do_test 1.6.0 { 99 for {set i 0} {$i < 20} {incr i} { 100 sqlite3 db2 test.db$i 101 db2 eval " DELETE FROM t$i " 102 db2 close 103 } 104 db eval { DELETE FROM t0 WHERE rowid<=200 } 105 } {} 106 107 do_compare_test 1.6.1 "rowid = 700" 108 do_compare_test 1.6.2 "rowid = -1" 109 do_compare_test 1.6.3 "rowid = 0" 110 do_compare_test 1.6.4 "rowid = 55" 111 do_compare_test 1.6.5 "rowid BETWEEN 20 AND 100" 112 do_compare_test 1.6.6 "rowid > 350" 113 do_compare_test 1.6.7 "rowid >= 350" 114 do_compare_test 1.6.8 "rowid >= 200" 115 do_compare_test 1.6.9 "1" 116 do_compare_test 1.6.10 "rowid >= 5" 117 118 do_test 1.x { 119 set sqlite_open_file_count 120 } [expr $nFile+9] 121 122 do_test 1.y { db close } {} 123 124 # Delete all the database files created above. 125 # 126 for {set i 0} {$i < 40} {incr i} { forcedelete "test.db$i" } 127 128 #------------------------------------------------------------------------- 129 # Test some error conditions: 130 # 131 # 2.1: Database file does not exist. 132 # 2.2: Table does not exist. 133 # 2.3: Table schema does not match. 134 # 2.4: Syntax error in SELECT statement. 135 # 136 reset_db 137 load_static_extension db unionvtab 138 do_test 2.0.1 { 139 db eval { 140 CREATE TABLE t0(a INTEGER PRIMARY KEY, b TEXT); 141 WITH s(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<400) 142 INSERT INTO t0 SELECT i, hex(randomblob(50)) FROM s; 143 CREATE TABLE dir(f, t, imin, imax); 144 } 145 146 for {set i 0} {$i < 40} {incr i} { 147 set iMin [expr $i*10 + 1] 148 set iMax [expr $iMin+9] 149 150 forcedelete "test.db$i" 151 db eval [subst { 152 ATTACH 'test.db$i' AS aux; 153 CREATE TABLE aux.t$i (a INTEGER PRIMARY KEY, b TEXT); 154 INSERT INTO aux.t$i SELECT * FROM t0 WHERE a BETWEEN $iMin AND $iMax; 155 DETACH aux; 156 INSERT INTO dir VALUES('test.db$i', 't$i', $iMin, $iMax); 157 }] 158 } 159 execsql { 160 CREATE VIRTUAL TABLE temp.s1 USING swarmvtab('SELECT * FROM dir'); 161 } 162 } {} 163 164 do_test 2.0.2 { 165 forcedelete test.db5 166 167 sqlite3 db2 test.db15 168 db2 eval { DROP TABLE t15 } 169 db2 close 170 171 sqlite3 db2 test.db25 172 db2 eval { 173 DROP TABLE t25; 174 CREATE TABLE t25(x, y, z PRIMARY KEY); 175 } 176 db2 close 177 } {} 178 179 do_catchsql_test 2.1 { 180 SELECT * FROM s1 WHERE rowid BETWEEN 1 AND 100; 181 } {1 {unable to open database file}} 182 do_catchsql_test 2.2 { 183 SELECT * FROM s1 WHERE rowid BETWEEN 101 AND 200; 184 } {1 {no such rowid table: t15}} 185 do_catchsql_test 2.3 { 186 SELECT * FROM s1 WHERE rowid BETWEEN 201 AND 300; 187 } {1 {source table schema mismatch}} 188 189 do_catchsql_test 2.4 { 190 CREATE VIRTUAL TABLE temp.x1 USING swarmvtab('SELECT * FROMdir'); 191 } {1 {sql error: near "FROMdir": syntax error}} 192 do_catchsql_test 2.5 { 193 CREATE VIRTUAL TABLE temp.x1 USING swarmvtab('SELECT * FROMdir', 'fetchdb'); 194 } {1 {sql error: near "FROMdir": syntax error}} 195 196 for {set i 0} {$i < 40} {incr i} { 197 forcedelete "test.db$i" 198 } 199 200 #------------------------------------------------------------------------- 201 # Test the outcome of the fetch function throwing an exception. 202 # 203 proc fetch_db {file} { 204 error "fetch_db error!" 205 } 206 207 db func fetch_db fetch_db 208 209 do_catchsql_test 3.1 { 210 CREATE VIRTUAL TABLE temp.xyz USING swarmvtab( 211 'VALUES 212 (''test.db1'', ''t1'', 1, 10), 213 (''test.db2'', ''t1'', 11, 20) 214 ', 'fetch_db_no_such_function' 215 ); 216 } {1 {sql error: no such function: fetch_db_no_such_function}} 217 218 do_catchsql_test 3.2 { 219 CREATE VIRTUAL TABLE temp.xyz USING swarmvtab( 220 'VALUES 221 (''test.db1'', ''t1'', 1, 10), 222 (''test.db2'', ''t1'', 11, 20) 223 ', 'fetch_db' 224 ); 225 } {1 {fetch_db error!}} 226 227 do_execsql_test 3.3.1 { 228 ATTACH 'test.db1' AS aux; 229 CREATE TABLE aux.t1(a INTEGER PRIMARY KEY, b); 230 INSERT INTO aux.t1 VALUES(1, NULL); 231 INSERT INTO aux.t1 VALUES(2, NULL); 232 INSERT INTO aux.t1 VALUES(9, NULL); 233 DETACH aux; 234 CREATE VIRTUAL TABLE temp.xyz USING swarmvtab( 235 'VALUES 236 (''test.db1'', ''t1'', 1, 10), 237 (''test.db2'', ''t1'', 11, 20) 238 ', 'fetch_db' 239 ); 240 } {} 241 242 do_catchsql_test 3.3.2 { SELECT * FROM xyz } {1 {fetch_db error!}} 243 244 245 246 finish_test