OLD | NEW |
(Empty) | |
| 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 te
xt} 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 te
xt} 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 te
xt} 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 te
xt} 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 {Th
is 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 {Th
is 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 1
2 -1234567 13 8388607 14 -8388608 15 1234567890 16 -1234567890 17 2147483647 18
-2147483648 19 123456789012345 20 -123456789012345 21 140737488355327 22 -140737
488355328 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('Mjölnir'); |
| 358 INSERT INTO e VALUES('Mjǫlnir'); |
| 359 INSERT INTO e VALUES('Mjölner'); |
| 360 INSERT INTO e VALUES('Mjølner'); |
| 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 Mjölnir 3 Mjǫlnir 4 Mjölner 5 Mjølner 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('Mjölnir'); |
| 386 INSERT INTO e VALUES('Mjǫlnir'); |
| 387 INSERT INTO e VALUES('Mjölner'); |
| 388 INSERT INTO e VALUES('Mjølner'); |
| 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 Mjölnir 3 Mjǫlnir 4 Mjölner 5 Mjølner 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('Mjölnir'); |
| 414 INSERT INTO e VALUES('Mjǫlnir'); |
| 415 INSERT INTO e VALUES('Mjölner'); |
| 416 INSERT INTO e VALUES('Mjølner'); |
| 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 Mjölnir 3 Mjǫlnir 4 Mjölner 5 Mjølner 6 ハンマー} |
| 428 |
| 429 finish_test |
OLD | NEW |