Modelpublic
AryaWu/sqlite

sourceHugging Faceupdated 10mo agoView on Hugging Face
0likes
multiwrite01.test416 linesDownload Raw Back to mptest
1/*2** This script sets up five different tasks all writing and updating3** the database at the same time, but each in its own table.4*/5--task 1 build-t16  DROP TABLE IF EXISTS t1;7  CREATE TABLE t1(a INTEGER PRIMARY KEY, b);8  --sleep 19  INSERT INTO t1 VALUES(1, randomblob(2000));10  INSERT INTO t1 VALUES(2, randomblob(1000));11  --sleep 112  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 116  INSERT INTO t1 SELECT a+16, randomblob(1500) FROM t1;17  --sleep 118  INSERT INTO t1 SELECT a+32, randomblob(1500) FROM t1;19  SELECT count(*) FROM t1;20  --match 6421  SELECT avg(length(b)) FROM t1;22  --match 1500.023  --sleep 224  UPDATE t1 SET b='x'||a||'y';25  SELECT sum(length(b)) FROM t1;26  --match 24727  SELECT a FROM t1 WHERE b='x17y';28  --match 1729  CREATE INDEX t1b ON t1(b);30  SELECT a FROM t1 WHERE b='x17y';31  --match 1732  SELECT a FROM t1 WHERE b GLOB 'x2?y' ORDER BY b DESC LIMIT 5;33  --match 29 28 27 26 2534--end35 36 37--task 2 build-t238  DROP TABLE IF EXISTS t2;39  CREATE TABLE t2(a INTEGER PRIMARY KEY, b);40  --sleep 141  INSERT INTO t2 VALUES(1, randomblob(2000));42  INSERT INTO t2 VALUES(2, randomblob(1000));43  --sleep 144  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 148  INSERT INTO t2 SELECT a+16, randomblob(1500) FROM t2;49  --sleep 150  INSERT INTO t2 SELECT a+32, randomblob(1500) FROM t2;51  SELECT count(*) FROM t2;52  --match 6453  SELECT avg(length(b)) FROM t2;54  --match 1500.055  --sleep 256  UPDATE t2 SET b='x'||a||'y';57  SELECT sum(length(b)) FROM t2;58  --match 24759  SELECT a FROM t2 WHERE b='x17y';60  --match 1761  CREATE INDEX t2b ON t2(b);62  SELECT a FROM t2 WHERE b='x17y';63  --match 1764  SELECT a FROM t2 WHERE b GLOB 'x2?y' ORDER BY b DESC LIMIT 5;65  --match 29 28 27 26 2566--end67 68--task 3 build-t369  DROP TABLE IF EXISTS t3;70  CREATE TABLE t3(a INTEGER PRIMARY KEY, b);71  --sleep 172  INSERT INTO t3 VALUES(1, randomblob(2000));73  INSERT INTO t3 VALUES(2, randomblob(1000));74  --sleep 175  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 179  INSERT INTO t3 SELECT a+16, randomblob(1500) FROM t3;80  --sleep 181  INSERT INTO t3 SELECT a+32, randomblob(1500) FROM t3;82  SELECT count(*) FROM t3;83  --match 6484  SELECT avg(length(b)) FROM t3;85  --match 1500.086  --sleep 287  UPDATE t3 SET b='x'||a||'y';88  SELECT sum(length(b)) FROM t3;89  --match 24790  SELECT a FROM t3 WHERE b='x17y';91  --match 1792  CREATE INDEX t3b ON t3(b);93  SELECT a FROM t3 WHERE b='x17y';94  --match 1795  SELECT a FROM t3 WHERE b GLOB 'x2?y' ORDER BY b DESC LIMIT 5;96  --match 29 28 27 26 2597--end98 99--task 4 build-t4100  DROP TABLE IF EXISTS t4;101  CREATE TABLE t4(a INTEGER PRIMARY KEY, b);102  --sleep 1103  INSERT INTO t4 VALUES(1, randomblob(2000));104  INSERT INTO t4 VALUES(2, randomblob(1000));105  --sleep 1106  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 1110  INSERT INTO t4 SELECT a+16, randomblob(1500) FROM t4;111  --sleep 1112  INSERT INTO t4 SELECT a+32, randomblob(1500) FROM t4;113  SELECT count(*) FROM t4;114  --match 64115  SELECT avg(length(b)) FROM t4;116  --match 1500.0117  --sleep 2118  UPDATE t4 SET b='x'||a||'y';119  SELECT sum(length(b)) FROM t4;120  --match 247121  SELECT a FROM t4 WHERE b='x17y';122  --match 17123  CREATE INDEX t4b ON t4(b);124  SELECT a FROM t4 WHERE b='x17y';125  --match 17126  SELECT a FROM t4 WHERE b GLOB 'x2?y' ORDER BY b DESC LIMIT 5;127  --match 29 28 27 26 25128--end129 130--task 5 build-t5131  DROP TABLE IF EXISTS t5;132  CREATE TABLE t5(a INTEGER PRIMARY KEY, b);133  --sleep 1134  INSERT INTO t5 VALUES(1, randomblob(2000));135  INSERT INTO t5 VALUES(2, randomblob(1000));136  --sleep 1137  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 1141  INSERT INTO t5 SELECT a+16, randomblob(1500) FROM t5;142  --sleep 1143  INSERT INTO t5 SELECT a+32, randomblob(1500) FROM t5;144  SELECT count(*) FROM t5;145  --match 64146  SELECT avg(length(b)) FROM t5;147  --match 1500.0148  --sleep 2149  UPDATE t5 SET b='x'||a||'y';150  SELECT sum(length(b)) FROM t5;151  --match 247152  SELECT a FROM t5 WHERE b='x17y';153  --match 17154  CREATE INDEX t5b ON t5(b);155  SELECT a FROM t5 WHERE b='x17y';156  --match 17157  SELECT a FROM t5 WHERE b GLOB 'x2?y' ORDER BY b DESC LIMIT 5;158  --match 29 28 27 26 25159--end160 161--wait all162SELECT count(*), sum(length(b)) FROM t1;163--match 64 247164SELECT count(*), sum(length(b)) FROM t2;165--match 64 247166SELECT count(*), sum(length(b)) FROM t3;167--match 64 247168SELECT count(*), sum(length(b)) FROM t4;169--match 64 247170SELECT count(*), sum(length(b)) FROM t5;171--match 64 247172 173--task 1174  SELECT t1.a FROM t1, t2175   WHERE t2.b GLOB 'x3?y' AND t1.b=('x'||(t2.a+3)||'y')176   ORDER BY t1.a LIMIT 4177  --match 33 34 35 36178  SELECT t3.a FROM t3, t4179   WHERE t4.b GLOB 'x4?y' AND t3.b=('x'||(t4.a+5)||'y')180   ORDER BY t3.a LIMIT 7181  --match 45 46 47 48 49 50 51182--end183--task 5184  SELECT t1.a FROM t1, t2185   WHERE t2.b GLOB 'x3?y' AND t1.b=('x'||(t2.a+3)||'y')186   ORDER BY t1.a LIMIT 4187  --match 33 34 35 36188  SELECT t3.a FROM t3, t4189   WHERE t4.b GLOB 'x4?y' AND t3.b=('x'||(t4.a+5)||'y')190   ORDER BY t3.a LIMIT 7191  --match 45 46 47 48 49 50 51192--end193--task 3194  SELECT t1.a FROM t1, t2195   WHERE t2.b GLOB 'x3?y' AND t1.b=('x'||(t2.a+3)||'y')196   ORDER BY t1.a LIMIT 4197  --match 33 34 35 36198  SELECT t3.a FROM t3, t4199   WHERE t4.b GLOB 'x4?y' AND t3.b=('x'||(t4.a+5)||'y')200   ORDER BY t3.a LIMIT 7201  --match 45 46 47 48 49 50 51202--end203--task 2204  SELECT t1.a FROM t1, t2205   WHERE t2.b GLOB 'x3?y' AND t1.b=('x'||(t2.a+3)||'y')206   ORDER BY t1.a LIMIT 4207  --match 33 34 35 36208  SELECT t3.a FROM t3, t4209   WHERE t4.b GLOB 'x4?y' AND t3.b=('x'||(t4.a+5)||'y')210   ORDER BY t3.a LIMIT 7211  --match 45 46 47 48 49 50 51212--end213--task 4214  SELECT t1.a FROM t1, t2215   WHERE t2.b GLOB 'x3?y' AND t1.b=('x'||(t2.a+3)||'y')216   ORDER BY t1.a LIMIT 4217  --match 33 34 35 36218  SELECT t3.a FROM t3, t4219   WHERE t4.b GLOB 'x4?y' AND t3.b=('x'||(t4.a+5)||'y')220   ORDER BY t3.a LIMIT 7221  --match 45 46 47 48 49 50 51222--end223--wait all224 225--task 5226  DROP INDEX t5b;227  --sleep 5228  PRAGMA integrity_check(10);229  --match ok230  CREATE INDEX t5b ON t5(b DESC);231--end232--task 3233  DROP INDEX t3b;234  --sleep 5235  PRAGMA integrity_check(10);236  --match ok237  CREATE INDEX t3b ON t3(b DESC);238--end239--task 1240  DROP INDEX t1b;241  --sleep 5242  PRAGMA integrity_check(10);243  --match ok244  CREATE INDEX t1b ON t1(b DESC);245--end246--task 2247  DROP INDEX t2b;248  --sleep 5249  PRAGMA integrity_check(10);250  --match ok251  CREATE INDEX t2b ON t2(b DESC);252--end253--task 4254  DROP INDEX t4b;255  --sleep 5256  PRAGMA integrity_check(10);257  --match ok258  CREATE INDEX t4b ON t4(b DESC);259--end260--wait all261 262--task 1263  SELECT t1.a FROM t1, t2264   WHERE t2.b GLOB 'x3?y' AND t1.b=('x'||(t2.a+3)||'y')265   ORDER BY t1.a LIMIT 4266  --match 33 34 35 36267  SELECT t3.a FROM t3, t4268   WHERE t4.b GLOB 'x4?y' AND t3.b=('x'||(t4.a+5)||'y')269   ORDER BY t3.a LIMIT 7270  --match 45 46 47 48 49 50 51271--end272--task 5273  SELECT t1.a FROM t1, t2274   WHERE t2.b GLOB 'x3?y' AND t1.b=('x'||(t2.a+3)||'y')275   ORDER BY t1.a LIMIT 4276  --match 33 34 35 36277  SELECT t3.a FROM t3, t4278   WHERE t4.b GLOB 'x4?y' AND t3.b=('x'||(t4.a+5)||'y')279   ORDER BY t3.a LIMIT 7280  --match 45 46 47 48 49 50 51281--end282--task 3283  SELECT t1.a FROM t1, t2284   WHERE t2.b GLOB 'x3?y' AND t1.b=('x'||(t2.a+3)||'y')285   ORDER BY t1.a LIMIT 4286  --match 33 34 35 36287  SELECT t3.a FROM t3, t4288   WHERE t4.b GLOB 'x4?y' AND t3.b=('x'||(t4.a+5)||'y')289   ORDER BY t3.a LIMIT 7290  --match 45 46 47 48 49 50 51291--end292--task 2293  SELECT t1.a FROM t1, t2294   WHERE t2.b GLOB 'x3?y' AND t1.b=('x'||(t2.a+3)||'y')295   ORDER BY t1.a LIMIT 4296  --match 33 34 35 36297  SELECT t3.a FROM t3, t4298   WHERE t4.b GLOB 'x4?y' AND t3.b=('x'||(t4.a+5)||'y')299   ORDER BY t3.a LIMIT 7300  --match 45 46 47 48 49 50 51301--end302--task 4303  SELECT t1.a FROM t1, t2304   WHERE t2.b GLOB 'x3?y' AND t1.b=('x'||(t2.a+3)||'y')305   ORDER BY t1.a LIMIT 4306  --match 33 34 35 36307  SELECT t3.a FROM t3, t4308   WHERE t4.b GLOB 'x4?y' AND t3.b=('x'||(t4.a+5)||'y')309   ORDER BY t3.a LIMIT 7310  --match 45 46 47 48 49 50 51311--end312--wait all313 314VACUUM;315PRAGMA integrity_check(10);316--match ok317 318--task 1319  UPDATE t1 SET b=randomblob(20000);320  --sleep 5321  UPDATE t1 SET b='x'||a||'y';322  SELECT a FROM t1 WHERE b='x63y';323  --match 63324--end325--task 2326  UPDATE t2 SET b=randomblob(20000);327  --sleep 5328  UPDATE t2 SET b='x'||a||'y';329  SELECT a FROM t2 WHERE b='x63y';330  --match 63331--end332--task 3333  UPDATE t3 SET b=randomblob(20000);334  --sleep 5335  UPDATE t3 SET b='x'||a||'y';336  SELECT a FROM t3 WHERE b='x63y';337  --match 63338--end339--task 4340  UPDATE t4 SET b=randomblob(20000);341  --sleep 5342  UPDATE t4 SET b='x'||a||'y';343  SELECT a FROM t4 WHERE b='x63y';344  --match 63345--end346--task 5347  UPDATE t5 SET b=randomblob(20000);348  --sleep 5349  UPDATE t5 SET b='x'||a||'y';350  SELECT a FROM t5 WHERE b='x63y';351  --match 63352--end353--wait all354 355--task 1356  SELECT t1.a FROM t1, t2357   WHERE t2.b GLOB 'x3?y' AND t1.b=('x'||(t2.a+3)||'y')358   ORDER BY t1.a LIMIT 4359  --match 33 34 35 36360  SELECT t3.a FROM t3, t4361   WHERE t4.b GLOB 'x4?y' AND t3.b=('x'||(t4.a+5)||'y')362   ORDER BY t3.a LIMIT 7363  --match 45 46 47 48 49 50 51364  PRAGMA integrity_check;365  --match ok366--end367--task 5368  SELECT t1.a FROM t1, t2369   WHERE t2.b GLOB 'x3?y' AND t1.b=('x'||(t2.a+3)||'y')370   ORDER BY t1.a LIMIT 4371  --match 33 34 35 36372  SELECT t3.a FROM t3, t4373   WHERE t4.b GLOB 'x4?y' AND t3.b=('x'||(t4.a+5)||'y')374   ORDER BY t3.a LIMIT 7375  --match 45 46 47 48 49 50 51376  PRAGMA integrity_check;377  --match ok378--end379--task 3380  SELECT t1.a FROM t1, t2381   WHERE t2.b GLOB 'x3?y' AND t1.b=('x'||(t2.a+3)||'y')382   ORDER BY t1.a LIMIT 4383  --match 33 34 35 36384  SELECT t3.a FROM t3, t4385   WHERE t4.b GLOB 'x4?y' AND t3.b=('x'||(t4.a+5)||'y')386   ORDER BY t3.a LIMIT 7387  --match 45 46 47 48 49 50 51388  PRAGMA integrity_check;389  --match ok390--end391--task 2392  SELECT t1.a FROM t1, t2393   WHERE t2.b GLOB 'x3?y' AND t1.b=('x'||(t2.a+3)||'y')394   ORDER BY t1.a LIMIT 4395  --match 33 34 35 36396  SELECT t3.a FROM t3, t4397   WHERE t4.b GLOB 'x4?y' AND t3.b=('x'||(t4.a+5)||'y')398   ORDER BY t3.a LIMIT 7399  --match 45 46 47 48 49 50 51400  PRAGMA integrity_check;401  --match ok402--end403--task 4404  SELECT t1.a FROM t1, t2405   WHERE t2.b GLOB 'x3?y' AND t1.b=('x'||(t2.a+3)||'y')406   ORDER BY t1.a LIMIT 4407  --match 33 34 35 36408  SELECT t3.a FROM t3, t4409   WHERE t4.b GLOB 'x4?y' AND t3.b=('x'||(t4.a+5)||'y')410   ORDER BY t3.a LIMIT 7411  --match 45 46 47 48 49 50 51412  PRAGMA integrity_check;413  --match ok414--end415--wait all416