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 17set testdir [file dirname $argv0] 18source $testdir/tester.tcl 19 20# A really basic table with manifest typing and a row of each type. 21db close 22sqlite3 db test.db 23db 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. 42do_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. 47do_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 60do_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 73do_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 86do_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 99do_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 112do_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 125do_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 138do_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 151do_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 164do_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 177do_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 190do_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 203do_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. 216do_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. 229do_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. 242do_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. 255do_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. 269do_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. 286do_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# 340ifcapable {!utf16} { 341 finish_test 342 return 343} 344 345# Baseline UTF-8. 346file delete -force test.db 347sqlite3 db test.db; 348db eval { 349 PRAGMA encoding = 'UTF-8'; 350} 351 352do_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. 374file delete -force test.db 375sqlite3 db test.db; 376db eval { 377 PRAGMA encoding = 'UTF-16LE'; 378} 379 380do_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. 402file delete -force test.db 403sqlite3 db test.db; 404db eval { 405 PRAGMA encoding = 'UTF-16BE'; 406} 407 408do_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 429finish_test 430