1 # 2012 January 4 {} 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. 12 # 13 # Use tables to test leaf-node reading, and also type checking. 14 # 15 # $Id$ 16 17 set testdir [file dirname $argv0] 18 source $testdir/tester.tcl 19 20 # A really basic table with manifest typing and a row of each type. 21 db close 22 sqlite3 db test.db 23 db eval { 24 DROP TABLE IF EXISTS types; 25 CREATE TABLE types (rowtype TEXT, value); 26 INSERT INTO types VALUES ("NULL", NULL); 27 INSERT INTO types VALUES ("INTEGER", 17); 28 INSERT INTO types VALUES ("FLOAT", 3.1415927); 29 INSERT INTO types VALUES ("TEXT", "This is text"); 30 INSERT INTO types VALUES ("BLOB", CAST("This is a blob" AS BLOB)); 31 32 -- Same contents, with an alias for rowid. Testing separately 33 -- because it changes the structure of the data (the alias column is 34 -- serialized as NULL). 35 DROP TABLE IF EXISTS types2; 36 CREATE TABLE types2 (id INTEGER PRIMARY KEY, rowtype TEXT, value); 37 INSERT INTO types2 (id, rowtype, value) 38 SELECT rowid, rowtype, value FROM types; 39 } 40 41 # Baseline results. 42 do_test recover-types-0.0 { 43 execsql {SELECT rowid, rowtype, value, TYPEOF(value) FROM types} 44 } {1 NULL {} null 2 INTEGER 17 integer 3 FLOAT 3.1415927 real 4 TEXT {This is text} text 5 BLOB {This is a blob} blob} 45 46 # With no restrictions, recover table shows identical results. 47 do_test recover-types-0.1 { 48 db eval { 49 DROP TABLE IF EXISTS temp.types_recover; 50 CREATE VIRTUAL TABLE temp.types_recover USING recover( 51 types, 52 rowtype TEXT, 53 value 54 ); 55 } 56 execsql {SELECT rowid, rowtype, value, TYPEOF(value) FROM types_recover} 57 } {1 NULL {} null 2 INTEGER 17 integer 3 FLOAT 3.1415927 real 4 TEXT {This is text} text 5 BLOB {This is a blob} blob} 58 59 # Restrict by INTEGER 60 do_test recover-types-1.0 { 61 db eval { 62 DROP TABLE IF EXISTS temp.types_recover; 63 CREATE VIRTUAL TABLE temp.types_recover USING recover( 64 types, 65 rowtype TEXT, 66 value INTEGER 67 ); 68 } 69 execsql {SELECT rowid, rowtype, value, TYPEOF(value) FROM types_recover} 70 } {1 NULL {} null 2 INTEGER 17 integer} 71 72 # Restrict by INTEGER NOT NULL 73 do_test recover-types-1.1 { 74 db eval { 75 DROP TABLE IF EXISTS temp.types_recover; 76 CREATE VIRTUAL TABLE temp.types_recover USING recover( 77 types, 78 rowtype TEXT, 79 value INTEGER NOT NULL 80 ); 81 } 82 execsql {SELECT rowid, rowtype, value, TYPEOF(value) FROM types_recover} 83 } {2 INTEGER 17 integer} 84 85 # Restrict by FLOAT 86 do_test recover-types-2.0 { 87 db eval { 88 DROP TABLE IF EXISTS temp.types_recover; 89 CREATE VIRTUAL TABLE temp.types_recover USING recover( 90 types, 91 rowtype TEXT, 92 value FLOAT 93 ); 94 } 95 execsql {SELECT rowid, rowtype, value, TYPEOF(value) FROM types_recover} 96 } {1 NULL {} null 2 INTEGER 17.0 real 3 FLOAT 3.1415927 real} 97 98 # Restrict by FLOAT NOT NULL 99 do_test recover-types-2.1 { 100 db eval { 101 DROP TABLE IF EXISTS temp.types_recover; 102 CREATE VIRTUAL TABLE temp.types_recover USING recover( 103 types, 104 rowtype TEXT, 105 value FLOAT NOT NULL 106 ); 107 } 108 execsql {SELECT rowid, rowtype, value, TYPEOF(value) FROM types_recover} 109 } {2 INTEGER 17.0 real 3 FLOAT 3.1415927 real} 110 111 # Restrict by FLOAT STRICT 112 do_test recover-types-2.2 { 113 db eval { 114 DROP TABLE IF EXISTS temp.types_recover; 115 CREATE VIRTUAL TABLE temp.types_recover USING recover( 116 types, 117 rowtype TEXT, 118 value FLOAT STRICT 119 ); 120 } 121 execsql {SELECT rowid, rowtype, value, TYPEOF(value) FROM types_recover} 122 } {1 NULL {} null 3 FLOAT 3.1415927 real} 123 124 # Restrict by FLOAT STRICT NOT NULL 125 do_test recover-types-2.3 { 126 db eval { 127 DROP TABLE IF EXISTS temp.types_recover; 128 CREATE VIRTUAL TABLE temp.types_recover USING recover( 129 types, 130 rowtype TEXT, 131 value FLOAT STRICT NOT NULL 132 ); 133 } 134 execsql {SELECT rowid, rowtype, value, TYPEOF(value) FROM types_recover} 135 } {3 FLOAT 3.1415927 real} 136 137 # Restrict by TEXT 138 do_test recover-types-3.0 { 139 db eval { 140 DROP TABLE IF EXISTS temp.types_recover; 141 CREATE VIRTUAL TABLE temp.types_recover USING recover( 142 types, 143 rowtype TEXT, 144 value TEXT 145 ); 146 } 147 execsql {SELECT rowid, rowtype, value, TYPEOF(value) FROM types_recover} 148 } {1 NULL {} null 4 TEXT {This is text} text 5 BLOB {This is a blob} blob} 149 150 # Restrict by TEXT NOT NULL 151 do_test recover-types-3.1 { 152 db eval { 153 DROP TABLE IF EXISTS temp.types_recover; 154 CREATE VIRTUAL TABLE temp.types_recover USING recover( 155 types, 156 rowtype TEXT, 157 value TEXT NOT NULL 158 ); 159 } 160 execsql {SELECT rowid, rowtype, value, TYPEOF(value) FROM types_recover} 161 } {4 TEXT {This is text} text 5 BLOB {This is a blob} blob} 162 163 # Restrict by TEXT STRICT 164 do_test recover-types-3.2 { 165 db eval { 166 DROP TABLE IF EXISTS temp.types_recover; 167 CREATE VIRTUAL TABLE temp.types_recover USING recover( 168 types, 169 rowtype TEXT, 170 value TEXT STRICT 171 ); 172 } 173 execsql {SELECT rowid, rowtype, value, TYPEOF(value) FROM types_recover} 174 } {1 NULL {} null 4 TEXT {This is text} text} 175 176 # Restrict by TEXT STRICT NOT NULL 177 do_test recover-types-3.3 { 178 db eval { 179 DROP TABLE IF EXISTS temp.types_recover; 180 CREATE VIRTUAL TABLE temp.types_recover USING recover( 181 types, 182 rowtype TEXT, 183 value TEXT STRICT NOT NULL 184 ); 185 } 186 execsql {SELECT rowid, rowtype, value, TYPEOF(value) FROM types_recover} 187 } {4 TEXT {This is text} text} 188 189 # Restrict by BLOB 190 do_test recover-types-4.0 { 191 db eval { 192 DROP TABLE IF EXISTS temp.types_recover; 193 CREATE VIRTUAL TABLE temp.types_recover USING recover( 194 types, 195 rowtype TEXT, 196 value BLOB 197 ); 198 } 199 execsql {SELECT rowid, rowtype, value, TYPEOF(value) FROM types_recover} 200 } {1 NULL {} null 5 BLOB {This is a blob} blob} 201 202 # Restrict by BLOB NOT NULL 203 do_test recover-types-4.1 { 204 db eval { 205 DROP TABLE IF EXISTS temp.types_recover; 206 CREATE VIRTUAL TABLE temp.types_recover USING recover( 207 types, 208 rowtype TEXT, 209 value BLOB NOT NULL 210 ); 211 } 212 execsql {SELECT rowid, rowtype, value, TYPEOF(value) FROM types_recover} 213 } {5 BLOB {This is a blob} blob} 214 215 # Manifest typing. 216 do_test recover-types-5.0 { 217 db eval { 218 DROP TABLE IF EXISTS temp.types_recover; 219 CREATE VIRTUAL TABLE temp.types_recover USING recover( 220 types, 221 rowtype TEXT, 222 value 223 ); 224 } 225 execsql {SELECT rowid, rowtype, value, TYPEOF(value) FROM types_recover} 226 } {1 NULL {} null 2 INTEGER 17 integer 3 FLOAT 3.1415927 real 4 TEXT {This is text} text 5 BLOB {This is a blob} blob} 227 228 # Should get same results specifying manifest typing explicitly. 229 do_test recover-types-5.1 { 230 db eval { 231 DROP TABLE IF EXISTS temp.types_recover; 232 CREATE VIRTUAL TABLE temp.types_recover USING recover( 233 types, 234 rowtype TEXT, 235 value ANY 236 ); 237 } 238 execsql {SELECT rowid, rowtype, value, TYPEOF(value) FROM types_recover} 239 } {1 NULL {} null 2 INTEGER 17 integer 3 FLOAT 3.1415927 real 4 TEXT {This is text} text 5 BLOB {This is a blob} blob} 240 241 # Same results, skipping the NULL row. 242 do_test recover-types-5.2 { 243 db eval { 244 DROP TABLE IF EXISTS temp.types_recover; 245 CREATE VIRTUAL TABLE temp.types_recover USING recover( 246 types, 247 rowtype TEXT, 248 value ANY NOT NULL 249 ); 250 } 251 execsql {SELECT rowid, rowtype, value, TYPEOF(value) FROM types_recover} 252 } {2 INTEGER 17 integer 3 FLOAT 3.1415927 real 4 TEXT {This is text} text 5 BLOB {This is a blob} blob} 253 254 # Test ROWID values. 255 do_test recover-types-6.0 { 256 db eval { 257 DROP TABLE IF EXISTS temp.types2_recover; 258 CREATE VIRTUAL TABLE temp.types2_recover USING recover( 259 types2, 260 id ROWID, 261 rowtype TEXT, 262 value 263 ); 264 } 265 execsql {SELECT rowid, id, rowtype, value, TYPEOF(value) FROM types2_recover} 266 } {1 1 NULL {} null 2 2 INTEGER 17 integer 3 3 FLOAT 3.1415927 real 4 4 TEXT {This is text} text 5 5 BLOB {This is a blob} blob} 267 268 # ROWID NOT NULL is identical. 269 do_test recover-types-6.1 { 270 db eval { 271 DROP TABLE IF EXISTS temp.types2_recover; 272 CREATE VIRTUAL TABLE temp.types2_recover USING recover( 273 types2, 274 id ROWID NOT NULL, 275 rowtype TEXT, 276 value 277 ); 278 } 279 execsql {SELECT rowid, id, rowtype, value, TYPEOF(value) FROM types2_recover} 280 } {1 1 NULL {} null 2 2 INTEGER 17 integer 3 3 FLOAT 3.1415927 real 4 4 TEXT {This is text} text 5 5 BLOB {This is a blob} blob} 281 282 # Check that each of the possible integer sizes is being decoded. 283 # TODO(shess): It would be neat to ACTUALLY test these things. As-is, 284 # this should exercise the code paths, but one needs logging or a 285 # debugger to verify that things are stored as expected. 286 do_test recover-types-7.0 { 287 db eval { 288 DROP TABLE IF EXISTS integers; 289 CREATE TABLE integers (value); 290 291 -- encoded directly in type info. 292 INSERT INTO integers VALUES (0); 293 INSERT INTO integers VALUES (1); 294 295 -- 8-bit signed. 296 INSERT INTO integers VALUES (2); 297 INSERT INTO integers VALUES (-2); 298 INSERT INTO integers VALUES (127); 299 INSERT INTO integers VALUES (-128); 300 301 -- 16-bit signed. 302 INSERT INTO integers VALUES (12345); 303 INSERT INTO integers VALUES (-12345); 304 INSERT INTO integers VALUES (32767); 305 INSERT INTO integers VALUES (-32768); 306 307 -- 24-bit signed. 308 INSERT INTO integers VALUES (1234567); 309 INSERT INTO integers VALUES (-1234567); 310 INSERT INTO integers VALUES (8388607); 311 INSERT INTO integers VALUES (-8388608); 312 313 -- 32-bit signed. 314 INSERT INTO integers VALUES (1234567890); 315 INSERT INTO integers VALUES (-1234567890); 316 INSERT INTO integers VALUES (2147483647); 317 INSERT INTO integers VALUES (-2147483648); 318 319 -- 48-bit signed. 320 INSERT INTO integers VALUES (123456789012345); 321 INSERT INTO integers VALUES (-123456789012345); 322 INSERT INTO integers VALUES (140737488355327); 323 INSERT INTO integers VALUES (-140737488355328); 324 325 -- 64-bit signed. 326 INSERT INTO integers VALUES (9223372036854775807); 327 INSERT INTO integers VALUES (-9223372036854775808); 328 329 DROP TABLE IF EXISTS integers_recover; 330 CREATE VIRTUAL TABLE temp.integers_recover USING recover( 331 integers, 332 value INTEGER 333 ); 334 } 335 execsql {SELECT rowid, value FROM integers_recover} 336 } {1 0 2 1 3 2 4 -2 5 127 6 -128 7 12345 8 -12345 9 32767 10 -32768 11 1234567 12 -1234567 13 8388607 14 -8388608 15 1234567890 16 -1234567890 17 2147483647 18 -2147483648 19 123456789012345 20 -123456789012345 21 140737488355327 22 -140737488355328 23 9223372036854775807 24 -9223372036854775808} 337 338 # If UTF16 support is disabled, ignore the rest of the tests. 339 # 340 ifcapable {!utf16} { 341 finish_test 342 return 343 } 344 345 # Baseline UTF-8. 346 file delete -force test.db 347 sqlite3 db test.db; 348 db eval { 349 PRAGMA encoding = 'UTF-8'; 350 } 351 352 do_test recover-encoding-1.0 { 353 execsql { 354 DROP TABLE IF EXISTS e; 355 CREATE TABLE e (v TEXT); 356 INSERT INTO e VALUES('Mjollnir'); 357 INSERT INTO e VALUES('Mjlnir'); 358 INSERT INTO e VALUES('Mjlnir'); 359 INSERT INTO e VALUES('Mjlner'); 360 INSERT INTO e VALUES('Mjlner'); 361 INSERT INTO e VALUES(''); 362 PRAGMA encoding; 363 364 DROP TABLE IF EXISTS e_recover; 365 CREATE VIRTUAL TABLE temp.e_recover USING recover( 366 e, 367 v TEXT 368 ); 369 SELECT rowid, v FROM e_recover ORDER BY rowid; 370 } 371 } {UTF-8 1 Mjollnir 2 Mjlnir 3 Mjlnir 4 Mjlner 5 Mjlner 6 } 372 373 # Reset the database to UTF-16LE. 374 file delete -force test.db 375 sqlite3 db test.db; 376 db eval { 377 PRAGMA encoding = 'UTF-16LE'; 378 } 379 380 do_test recover-encoding-2.0 { 381 execsql { 382 DROP TABLE IF EXISTS e; 383 CREATE TABLE e (v TEXT); 384 INSERT INTO e VALUES('Mjollnir'); 385 INSERT INTO e VALUES('Mjlnir'); 386 INSERT INTO e VALUES('Mjlnir'); 387 INSERT INTO e VALUES('Mjlner'); 388 INSERT INTO e VALUES('Mjlner'); 389 INSERT INTO e VALUES(''); 390 PRAGMA encoding; 391 392 DROP TABLE IF EXISTS e_recover; 393 CREATE VIRTUAL TABLE temp.e_recover USING recover( 394 e, 395 v TEXT 396 ); 397 SELECT rowid, v FROM e_recover ORDER BY rowid; 398 } 399 } {UTF-16le 1 Mjollnir 2 Mjlnir 3 Mjlnir 4 Mjlner 5 Mjlner 6 } 400 401 # Reset the database to UTF-16BE. 402 file delete -force test.db 403 sqlite3 db test.db; 404 db eval { 405 PRAGMA encoding = 'UTF-16BE'; 406 } 407 408 do_test recover-encoding-3.0 { 409 execsql { 410 DROP TABLE IF EXISTS e; 411 CREATE TABLE e (v TEXT); 412 INSERT INTO e VALUES('Mjollnir'); 413 INSERT INTO e VALUES('Mjlnir'); 414 INSERT INTO e VALUES('Mjlnir'); 415 INSERT INTO e VALUES('Mjlner'); 416 INSERT INTO e VALUES('Mjlner'); 417 INSERT INTO e VALUES(''); 418 PRAGMA encoding; 419 420 DROP TABLE IF EXISTS e_recover; 421 CREATE VIRTUAL TABLE temp.e_recover USING recover( 422 e, 423 v TEXT 424 ); 425 SELECT rowid, v FROM e_recover ORDER BY rowid; 426 } 427 } {UTF-16be 1 Mjollnir 2 Mjlnir 3 Mjlnir 4 Mjlner 5 Mjlner 6 } 428 429 finish_test 430
