xref: /sqlite-3.40.0/mptest/multiwrite01.test (revision 3f5bc380) 
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
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 total(length(b)) FROM t1;
26  --match 247
27--end
28
29
30--task 2
31  DROP TABLE IF EXISTS t2;
32  CREATE TABLE t2(a INTEGER PRIMARY KEY, b);
33  --sleep 1
34  INSERT INTO t2 VALUES(1, randomblob(2000));
35  INSERT INTO t2 VALUES(2, randomblob(1000));
36  --sleep 1
37  INSERT INTO t2 SELECT a+2, randomblob(1500) FROM t2;
38  INSERT INTO t2 SELECT a+4, randomblob(1500) FROM t2;
39  INSERT INTO t2 SELECT a+8, randomblob(1500) FROM t2;
40  --sleep 1
41  INSERT INTO t2 SELECT a+16, randomblob(1500) FROM t2;
42  --sleep 1
43  INSERT INTO t2 SELECT a+32, randomblob(1500) FROM t2;
44  SELECT count(*) FROM t2;
45  --match 64
46  SELECT avg(length(b)) FROM t2;
47  --match 1500.0
48  --sleep 2
49  UPDATE t2 SET b='x'||a||'y';
50  SELECT total(length(b)) FROM t2;
51  --match 247
52--end
53
54--task 3
55  DROP TABLE IF EXISTS t3;
56  CREATE TABLE t3(a INTEGER PRIMARY KEY, b);
57  --sleep 1
58  INSERT INTO t3 VALUES(1, randomblob(2000));
59  INSERT INTO t3 VALUES(2, randomblob(1000));
60  --sleep 1
61  INSERT INTO t3 SELECT a+2, randomblob(1500) FROM t3;
62  INSERT INTO t3 SELECT a+4, randomblob(1500) FROM t3;
63  INSERT INTO t3 SELECT a+8, randomblob(1500) FROM t3;
64  --sleep 1
65  INSERT INTO t3 SELECT a+16, randomblob(1500) FROM t3;
66  --sleep 1
67  INSERT INTO t3 SELECT a+32, randomblob(1500) FROM t3;
68  SELECT count(*) FROM t3;
69  --match 64
70  SELECT avg(length(b)) FROM t3;
71  --match 1500.0
72  --sleep 2
73  UPDATE t3 SET b='x'||a||'y';
74  SELECT total(length(b)) FROM t3;
75  --match 247
76--end
77
78--task 4
79  DROP TABLE IF EXISTS t4;
80  CREATE TABLE t4(a INTEGER PRIMARY KEY, b);
81  --sleep 1
82  INSERT INTO t4 VALUES(1, randomblob(2000));
83  INSERT INTO t4 VALUES(2, randomblob(1000));
84  --sleep 1
85  INSERT INTO t4 SELECT a+2, randomblob(1500) FROM t4;
86  INSERT INTO t4 SELECT a+4, randomblob(1500) FROM t4;
87  INSERT INTO t4 SELECT a+8, randomblob(1500) FROM t4;
88  --sleep 1
89  INSERT INTO t4 SELECT a+16, randomblob(1500) FROM t4;
90  --sleep 1
91  INSERT INTO t4 SELECT a+32, randomblob(1500) FROM t4;
92  SELECT count(*) FROM t4;
93  --match 64
94  SELECT avg(length(b)) FROM t4;
95  --match 1500.0
96  --sleep 2
97  UPDATE t4 SET b='x'||a||'y';
98  SELECT total(length(b)) FROM t4;
99  --match 247
100--end
101
102--task 5
103  DROP TABLE IF EXISTS t5;
104  CREATE TABLE t5(a INTEGER PRIMARY KEY, b);
105  --sleep 1
106  INSERT INTO t5 VALUES(1, randomblob(2000));
107  INSERT INTO t5 VALUES(2, randomblob(1000));
108  --sleep 1
109  INSERT INTO t5 SELECT a+2, randomblob(1500) FROM t5;
110  INSERT INTO t5 SELECT a+4, randomblob(1500) FROM t5;
111  INSERT INTO t5 SELECT a+8, randomblob(1500) FROM t5;
112  --sleep 1
113  INSERT INTO t5 SELECT a+16, randomblob(1500) FROM t5;
114  --sleep 1
115  INSERT INTO t5 SELECT a+32, randomblob(1500) FROM t5;
116  SELECT count(*) FROM t5;
117  --match 64
118  SELECT avg(length(b)) FROM t5;
119  --match 1500.0
120  --sleep 2
121  UPDATE t5 SET b='x'||a||'y';
122  SELECT total(length(b)) FROM t5;
123  --match 247
124--end
125
126--wait all
127SELECT count(*), total(length(b)) FROM t1;
128--match 64 247
129SELECT count(*), total(length(b)) FROM t2;
130--match 64 247
131SELECT count(*), total(length(b)) FROM t3;
132--match 64 247
133SELECT count(*), total(length(b)) FROM t4;
134--match 64 247
135SELECT count(*), total(length(b)) FROM t5;
136--match 64 247
137DROP TABLE t1;
138DROP TABLE t2;
139DROP TABLE t3;
140DROP TABLE t4;
141DROP TABLE t5;
142