1/* 2** This script sets up five different tasks all writing and updating 3** the database at the same time, but each in its own table. 4*/ 5--task 1 build-t1 6 DROP TABLE IF EXISTS t1; 7 CREATE TABLE t1(a INTEGER PRIMARY KEY, b); 8 --sleep 1 9 INSERT INTO t1 VALUES(1, randomblob(2000)); 10 INSERT INTO t1 VALUES(2, randomblob(1000)); 11 --sleep 1 12 INSERT INTO t1 SELECT a+2, randomblob(1500) FROM t1; 13 INSERT INTO t1 SELECT a+4, randomblob(1500) FROM t1; 14 INSERT INTO t1 SELECT a+8, randomblob(1500) FROM t1; 15 --sleep 1 16 INSERT INTO t1 SELECT a+16, randomblob(1500) FROM t1; 17 --sleep 1 18 INSERT INTO t1 SELECT a+32, randomblob(1500) FROM t1; 19 SELECT count(*) FROM t1; 20 --match 64 21 SELECT avg(length(b)) FROM t1; 22 --match 1500.0 23 --sleep 2 24 UPDATE t1 SET b='x'||a||'y'; 25 SELECT sum(length(b)) FROM t1; 26 --match 247 27 SELECT a FROM t1 WHERE b='x17y'; 28 --match 17 29 CREATE INDEX t1b ON t1(b); 30 SELECT a FROM t1 WHERE b='x17y'; 31 --match 17 32 SELECT a FROM t1 WHERE b GLOB 'x2?y' ORDER BY b DESC LIMIT 5; 33 --match 29 28 27 26 25 34--end 35 36 37--task 2 build-t2 38 DROP TABLE IF EXISTS t2; 39 CREATE TABLE t2(a INTEGER PRIMARY KEY, b); 40 --sleep 1 41 INSERT INTO t2 VALUES(1, randomblob(2000)); 42 INSERT INTO t2 VALUES(2, randomblob(1000)); 43 --sleep 1 44 INSERT INTO t2 SELECT a+2, randomblob(1500) FROM t2; 45 INSERT INTO t2 SELECT a+4, randomblob(1500) FROM t2; 46 INSERT INTO t2 SELECT a+8, randomblob(1500) FROM t2; 47 --sleep 1 48 INSERT INTO t2 SELECT a+16, randomblob(1500) FROM t2; 49 --sleep 1 50 INSERT INTO t2 SELECT a+32, randomblob(1500) FROM t2; 51 SELECT count(*) FROM t2; 52 --match 64 53 SELECT avg(length(b)) FROM t2; 54 --match 1500.0 55 --sleep 2 56 UPDATE t2 SET b='x'||a||'y'; 57 SELECT sum(length(b)) FROM t2; 58 --match 247 59 SELECT a FROM t2 WHERE b='x17y'; 60 --match 17 61 CREATE INDEX t2b ON t2(b); 62 SELECT a FROM t2 WHERE b='x17y'; 63 --match 17 64 SELECT a FROM t2 WHERE b GLOB 'x2?y' ORDER BY b DESC LIMIT 5; 65 --match 29 28 27 26 25 66--end 67 68--task 3 build-t3 69 DROP TABLE IF EXISTS t3; 70 CREATE TABLE t3(a INTEGER PRIMARY KEY, b); 71 --sleep 1 72 INSERT INTO t3 VALUES(1, randomblob(2000)); 73 INSERT INTO t3 VALUES(2, randomblob(1000)); 74 --sleep 1 75 INSERT INTO t3 SELECT a+2, randomblob(1500) FROM t3; 76 INSERT INTO t3 SELECT a+4, randomblob(1500) FROM t3; 77 INSERT INTO t3 SELECT a+8, randomblob(1500) FROM t3; 78 --sleep 1 79 INSERT INTO t3 SELECT a+16, randomblob(1500) FROM t3; 80 --sleep 1 81 INSERT INTO t3 SELECT a+32, randomblob(1500) FROM t3; 82 SELECT count(*) FROM t3; 83 --match 64 84 SELECT avg(length(b)) FROM t3; 85 --match 1500.0 86 --sleep 2 87 UPDATE t3 SET b='x'||a||'y'; 88 SELECT sum(length(b)) FROM t3; 89 --match 247 90 SELECT a FROM t3 WHERE b='x17y'; 91 --match 17 92 CREATE INDEX t3b ON t3(b); 93 SELECT a FROM t3 WHERE b='x17y'; 94 --match 17 95 SELECT a FROM t3 WHERE b GLOB 'x2?y' ORDER BY b DESC LIMIT 5; 96 --match 29 28 27 26 25 97--end 98 99--task 4 build-t4 100 DROP TABLE IF EXISTS t4; 101 CREATE TABLE t4(a INTEGER PRIMARY KEY, b); 102 --sleep 1 103 INSERT INTO t4 VALUES(1, randomblob(2000)); 104 INSERT INTO t4 VALUES(2, randomblob(1000)); 105 --sleep 1 106 INSERT INTO t4 SELECT a+2, randomblob(1500) FROM t4; 107 INSERT INTO t4 SELECT a+4, randomblob(1500) FROM t4; 108 INSERT INTO t4 SELECT a+8, randomblob(1500) FROM t4; 109 --sleep 1 110 INSERT INTO t4 SELECT a+16, randomblob(1500) FROM t4; 111 --sleep 1 112 INSERT INTO t4 SELECT a+32, randomblob(1500) FROM t4; 113 SELECT count(*) FROM t4; 114 --match 64 115 SELECT avg(length(b)) FROM t4; 116 --match 1500.0 117 --sleep 2 118 UPDATE t4 SET b='x'||a||'y'; 119 SELECT sum(length(b)) FROM t4; 120 --match 247 121 SELECT a FROM t4 WHERE b='x17y'; 122 --match 17 123 CREATE INDEX t4b ON t4(b); 124 SELECT a FROM t4 WHERE b='x17y'; 125 --match 17 126 SELECT a FROM t4 WHERE b GLOB 'x2?y' ORDER BY b DESC LIMIT 5; 127 --match 29 28 27 26 25 128--end 129 130--task 5 build-t5 131 DROP TABLE IF EXISTS t5; 132 CREATE TABLE t5(a INTEGER PRIMARY KEY, b); 133 --sleep 1 134 INSERT INTO t5 VALUES(1, randomblob(2000)); 135 INSERT INTO t5 VALUES(2, randomblob(1000)); 136 --sleep 1 137 INSERT INTO t5 SELECT a+2, randomblob(1500) FROM t5; 138 INSERT INTO t5 SELECT a+4, randomblob(1500) FROM t5; 139 INSERT INTO t5 SELECT a+8, randomblob(1500) FROM t5; 140 --sleep 1 141 INSERT INTO t5 SELECT a+16, randomblob(1500) FROM t5; 142 --sleep 1 143 INSERT INTO t5 SELECT a+32, randomblob(1500) FROM t5; 144 SELECT count(*) FROM t5; 145 --match 64 146 SELECT avg(length(b)) FROM t5; 147 --match 1500.0 148 --sleep 2 149 UPDATE t5 SET b='x'||a||'y'; 150 SELECT sum(length(b)) FROM t5; 151 --match 247 152 SELECT a FROM t5 WHERE b='x17y'; 153 --match 17 154 CREATE INDEX t5b ON t5(b); 155 SELECT a FROM t5 WHERE b='x17y'; 156 --match 17 157 SELECT a FROM t5 WHERE b GLOB 'x2?y' ORDER BY b DESC LIMIT 5; 158 --match 29 28 27 26 25 159--end 160 161--wait all 162SELECT count(*), sum(length(b)) FROM t1; 163--match 64 247 164SELECT count(*), sum(length(b)) FROM t2; 165--match 64 247 166SELECT count(*), sum(length(b)) FROM t3; 167--match 64 247 168SELECT count(*), sum(length(b)) FROM t4; 169--match 64 247 170SELECT count(*), sum(length(b)) FROM t5; 171--match 64 247 172 173--task 1 174 SELECT t1.a FROM t1, t2 175 WHERE t2.b GLOB 'x3?y' AND t1.b=('x'||(t2.a+3)||'y') 176 ORDER BY t1.a LIMIT 4 177 --match 33 34 35 36 178 SELECT t3.a FROM t3, t4 179 WHERE t4.b GLOB 'x4?y' AND t3.b=('x'||(t4.a+5)||'y') 180 ORDER BY t3.a LIMIT 7 181 --match 45 46 47 48 49 50 51 182--end 183--task 5 184 SELECT t1.a FROM t1, t2 185 WHERE t2.b GLOB 'x3?y' AND t1.b=('x'||(t2.a+3)||'y') 186 ORDER BY t1.a LIMIT 4 187 --match 33 34 35 36 188 SELECT t3.a FROM t3, t4 189 WHERE t4.b GLOB 'x4?y' AND t3.b=('x'||(t4.a+5)||'y') 190 ORDER BY t3.a LIMIT 7 191 --match 45 46 47 48 49 50 51 192--end 193--task 3 194 SELECT t1.a FROM t1, t2 195 WHERE t2.b GLOB 'x3?y' AND t1.b=('x'||(t2.a+3)||'y') 196 ORDER BY t1.a LIMIT 4 197 --match 33 34 35 36 198 SELECT t3.a FROM t3, t4 199 WHERE t4.b GLOB 'x4?y' AND t3.b=('x'||(t4.a+5)||'y') 200 ORDER BY t3.a LIMIT 7 201 --match 45 46 47 48 49 50 51 202--end 203--task 2 204 SELECT t1.a FROM t1, t2 205 WHERE t2.b GLOB 'x3?y' AND t1.b=('x'||(t2.a+3)||'y') 206 ORDER BY t1.a LIMIT 4 207 --match 33 34 35 36 208 SELECT t3.a FROM t3, t4 209 WHERE t4.b GLOB 'x4?y' AND t3.b=('x'||(t4.a+5)||'y') 210 ORDER BY t3.a LIMIT 7 211 --match 45 46 47 48 49 50 51 212--end 213--task 4 214 SELECT t1.a FROM t1, t2 215 WHERE t2.b GLOB 'x3?y' AND t1.b=('x'||(t2.a+3)||'y') 216 ORDER BY t1.a LIMIT 4 217 --match 33 34 35 36 218 SELECT t3.a FROM t3, t4 219 WHERE t4.b GLOB 'x4?y' AND t3.b=('x'||(t4.a+5)||'y') 220 ORDER BY t3.a LIMIT 7 221 --match 45 46 47 48 49 50 51 222--end 223--wait all 224 225--task 5 226 DROP INDEX t5b; 227 --sleep 5 228 PRAGMA integrity_check(10); 229 --match ok 230 CREATE INDEX t5b ON t5(b DESC); 231--end 232--task 3 233 DROP INDEX t3b; 234 --sleep 5 235 PRAGMA integrity_check(10); 236 --match ok 237 CREATE INDEX t3b ON t3(b DESC); 238--end 239--task 1 240 DROP INDEX t1b; 241 --sleep 5 242 PRAGMA integrity_check(10); 243 --match ok 244 CREATE INDEX t1b ON t1(b DESC); 245--end 246--task 2 247 DROP INDEX t2b; 248 --sleep 5 249 PRAGMA integrity_check(10); 250 --match ok 251 CREATE INDEX t2b ON t2(b DESC); 252--end 253--task 4 254 DROP INDEX t4b; 255 --sleep 5 256 PRAGMA integrity_check(10); 257 --match ok 258 CREATE INDEX t4b ON t4(b DESC); 259--end 260--wait all 261 262--task 1 263 SELECT t1.a FROM t1, t2 264 WHERE t2.b GLOB 'x3?y' AND t1.b=('x'||(t2.a+3)||'y') 265 ORDER BY t1.a LIMIT 4 266 --match 33 34 35 36 267 SELECT t3.a FROM t3, t4 268 WHERE t4.b GLOB 'x4?y' AND t3.b=('x'||(t4.a+5)||'y') 269 ORDER BY t3.a LIMIT 7 270 --match 45 46 47 48 49 50 51 271--end 272--task 5 273 SELECT t1.a FROM t1, t2 274 WHERE t2.b GLOB 'x3?y' AND t1.b=('x'||(t2.a+3)||'y') 275 ORDER BY t1.a LIMIT 4 276 --match 33 34 35 36 277 SELECT t3.a FROM t3, t4 278 WHERE t4.b GLOB 'x4?y' AND t3.b=('x'||(t4.a+5)||'y') 279 ORDER BY t3.a LIMIT 7 280 --match 45 46 47 48 49 50 51 281--end 282--task 3 283 SELECT t1.a FROM t1, t2 284 WHERE t2.b GLOB 'x3?y' AND t1.b=('x'||(t2.a+3)||'y') 285 ORDER BY t1.a LIMIT 4 286 --match 33 34 35 36 287 SELECT t3.a FROM t3, t4 288 WHERE t4.b GLOB 'x4?y' AND t3.b=('x'||(t4.a+5)||'y') 289 ORDER BY t3.a LIMIT 7 290 --match 45 46 47 48 49 50 51 291--end 292--task 2 293 SELECT t1.a FROM t1, t2 294 WHERE t2.b GLOB 'x3?y' AND t1.b=('x'||(t2.a+3)||'y') 295 ORDER BY t1.a LIMIT 4 296 --match 33 34 35 36 297 SELECT t3.a FROM t3, t4 298 WHERE t4.b GLOB 'x4?y' AND t3.b=('x'||(t4.a+5)||'y') 299 ORDER BY t3.a LIMIT 7 300 --match 45 46 47 48 49 50 51 301--end 302--task 4 303 SELECT t1.a FROM t1, t2 304 WHERE t2.b GLOB 'x3?y' AND t1.b=('x'||(t2.a+3)||'y') 305 ORDER BY t1.a LIMIT 4 306 --match 33 34 35 36 307 SELECT t3.a FROM t3, t4 308 WHERE t4.b GLOB 'x4?y' AND t3.b=('x'||(t4.a+5)||'y') 309 ORDER BY t3.a LIMIT 7 310 --match 45 46 47 48 49 50 51 311--end 312--wait all 313 314VACUUM; 315PRAGMA integrity_check(10); 316--match ok 317 318--task 1 319 UPDATE t1 SET b=randomblob(20000); 320 --sleep 5 321 UPDATE t1 SET b='x'||a||'y'; 322 SELECT a FROM t1 WHERE b='x63y'; 323 --match 63 324--end 325--task 2 326 UPDATE t2 SET b=randomblob(20000); 327 --sleep 5 328 UPDATE t2 SET b='x'||a||'y'; 329 SELECT a FROM t2 WHERE b='x63y'; 330 --match 63 331--end 332--task 3 333 UPDATE t3 SET b=randomblob(20000); 334 --sleep 5 335 UPDATE t3 SET b='x'||a||'y'; 336 SELECT a FROM t3 WHERE b='x63y'; 337 --match 63 338--end 339--task 4 340 UPDATE t4 SET b=randomblob(20000); 341 --sleep 5 342 UPDATE t4 SET b='x'||a||'y'; 343 SELECT a FROM t4 WHERE b='x63y'; 344 --match 63 345--end 346--task 5 347 UPDATE t5 SET b=randomblob(20000); 348 --sleep 5 349 UPDATE t5 SET b='x'||a||'y'; 350 SELECT a FROM t5 WHERE b='x63y'; 351 --match 63 352--end 353--wait all 354 355--task 1 356 SELECT t1.a FROM t1, t2 357 WHERE t2.b GLOB 'x3?y' AND t1.b=('x'||(t2.a+3)||'y') 358 ORDER BY t1.a LIMIT 4 359 --match 33 34 35 36 360 SELECT t3.a FROM t3, t4 361 WHERE t4.b GLOB 'x4?y' AND t3.b=('x'||(t4.a+5)||'y') 362 ORDER BY t3.a LIMIT 7 363 --match 45 46 47 48 49 50 51 364 PRAGMA integrity_check; 365 --match ok 366--end 367--task 5 368 SELECT t1.a FROM t1, t2 369 WHERE t2.b GLOB 'x3?y' AND t1.b=('x'||(t2.a+3)||'y') 370 ORDER BY t1.a LIMIT 4 371 --match 33 34 35 36 372 SELECT t3.a FROM t3, t4 373 WHERE t4.b GLOB 'x4?y' AND t3.b=('x'||(t4.a+5)||'y') 374 ORDER BY t3.a LIMIT 7 375 --match 45 46 47 48 49 50 51 376 PRAGMA integrity_check; 377 --match ok 378--end 379--task 3 380 SELECT t1.a FROM t1, t2 381 WHERE t2.b GLOB 'x3?y' AND t1.b=('x'||(t2.a+3)||'y') 382 ORDER BY t1.a LIMIT 4 383 --match 33 34 35 36 384 SELECT t3.a FROM t3, t4 385 WHERE t4.b GLOB 'x4?y' AND t3.b=('x'||(t4.a+5)||'y') 386 ORDER BY t3.a LIMIT 7 387 --match 45 46 47 48 49 50 51 388 PRAGMA integrity_check; 389 --match ok 390--end 391--task 2 392 SELECT t1.a FROM t1, t2 393 WHERE t2.b GLOB 'x3?y' AND t1.b=('x'||(t2.a+3)||'y') 394 ORDER BY t1.a LIMIT 4 395 --match 33 34 35 36 396 SELECT t3.a FROM t3, t4 397 WHERE t4.b GLOB 'x4?y' AND t3.b=('x'||(t4.a+5)||'y') 398 ORDER BY t3.a LIMIT 7 399 --match 45 46 47 48 49 50 51 400 PRAGMA integrity_check; 401 --match ok 402--end 403--task 4 404 SELECT t1.a FROM t1, t2 405 WHERE t2.b GLOB 'x3?y' AND t1.b=('x'||(t2.a+3)||'y') 406 ORDER BY t1.a LIMIT 4 407 --match 33 34 35 36 408 SELECT t3.a FROM t3, t4 409 WHERE t4.b GLOB 'x4?y' AND t3.b=('x'||(t4.a+5)||'y') 410 ORDER BY t3.a LIMIT 7 411 --match 45 46 47 48 49 50 51 412 PRAGMA integrity_check; 413 --match ok 414--end 415--wait all 416