| 1 |
-- Run this script through the sqlite3 command-line shell in order to generate |
| 2 |
-- a database file containing lots of data for testing purposes. |
| 3 |
-- |
| 4 |
-- This script assumes that the "bin2c" program is available on ones $PATH. |
| 5 |
-- The "bin2c" program reads a binary file and outputs C-code that creates |
| 6 |
-- an array of bytes holding the content of that file. |
| 7 |
-- |
| 8 |
-- This script is designed to create many tables and views all having |
| 9 |
-- 5 columns, "a" through "e", and with a variety of integers, short strings, |
| 10 |
-- and NULL values. |
| 11 |
-- |
| 12 |
.open -new testdb01.db |
| 13 |
PRAGMA page_size=512; |
| 14 |
BEGIN; |
| 15 |
CREATE TABLE t1(a INTEGER PRIMARY KEY, b INT, c INT, d INT, e INT); |
| 16 |
WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<50) |
| 17 |
INSERT INTO t1(a,b,c,d,e) SELECT x,abs(random()%51), |
| 18 |
abs(random()%100), abs(random()%51), abs(random()%100) FROM c; |
| 19 |
CREATE TABLE t2(a INT, b INT, c INT,d INT,e INT,PRIMARY KEY(b,a))WITHOUT ROWID; |
| 20 |
INSERT INTO t2 SELECT * FROM t1; |
| 21 |
CREATE TABLE t3(a,b,c,d,e); |
| 22 |
INSERT INTO t3 SELECT a,b,c,d,e FROM t1 ORDER BY random() LIMIT 5; |
| 23 |
INSERT INTO t3 SELECT null,b,c,d,e FROM t1 ORDER BY random() LIMIT 5; |
| 24 |
INSERT INTO t3 SELECT a,null,c,d,e FROM t1 ORDER BY random() LIMIT 5; |
| 25 |
INSERT INTO t3 SELECT a,b,null,d,e FROM t1 ORDER BY random() LIMIT 5; |
| 26 |
INSERT INTO t3 SELECT a,b,c,null,e FROM t1 ORDER BY random() LIMIT 5; |
| 27 |
INSERT INTO t3 SELECT a,b,c,d,null FROM t1 ORDER BY random() LIMIT 5; |
| 28 |
INSERT INTO t3 SELECT null,null,null,null,null FROM t1 LIMIT 5; |
| 29 |
CREATE INDEX t3x1 ON t3(a,b,c,d,e); |
| 30 |
CREATE TABLE t4(a INT UNIQUE NOT NULL, b INT UNIQUE NOT NULL,c,d,e); |
| 31 |
INSERT OR IGNORE INTO t4 SELECT a,b,c,d,e FROM t3; |
| 32 |
CREATE TABLE t5(a INTEGER PRIMARY KEY, b TEXT UNIQUE,c,d,e); |
| 33 |
INSERT INTO t5(b) VALUES |
| 34 |
('truth'), |
| 35 |
('works'), |
| 36 |
('offer'), |
| 37 |
('can'), |
| 38 |
('anger'), |
| 39 |
('wisdom'), |
| 40 |
('send'), |
| 41 |
('though'), |
| 42 |
('save'), |
| 43 |
('between'), |
| 44 |
('some'), |
| 45 |
('wine'), |
| 46 |
('ark'), |
| 47 |
('smote'), |
| 48 |
('therein'), |
| 49 |
('shew'), |
| 50 |
('morning'), |
| 51 |
('dwelt'), |
| 52 |
('begat'), |
| 53 |
('nothing'), |
| 54 |
('war'), |
| 55 |
('above'), |
| 56 |
('known'), |
| 57 |
('sacrifice'), |
| 58 |
('tell'), |
| 59 |
('departed'), |
| 60 |
('thyself'), |
| 61 |
('places'), |
| 62 |
('bear'), |
| 63 |
('part'), |
| 64 |
('while'), |
| 65 |
('gone'), |
| 66 |
('cubits'), |
| 67 |
('walk'), |
| 68 |
('long'), |
| 69 |
('near'), |
| 70 |
('serve'), |
| 71 |
('fruit'), |
| 72 |
('doth'), |
| 73 |
('poor'), |
| 74 |
('ways'), |
| 75 |
('child'), |
| 76 |
('temple'), |
| 77 |
('angel'), |
| 78 |
('inhabitants'), |
| 79 |
('oil'), |
| 80 |
('died'), |
| 81 |
('six'), |
| 82 |
('tree'), |
| 83 |
('wrath'); |
| 84 |
UPDATE t1 SET e=(SELECT b FROM t5 WHERE t5.a=(t1.e%51)); |
| 85 |
UPDATE t5 SET (c,d,e) = |
| 86 |
(SELECT c,d,e FROM t1 WHERE t1.a=abs(t5.a+random()/100)%50+1); |
| 87 |
UPDATE t2 SET e=(SELECT b FROM t5 WHERE t5.a=(t2.e%51)); |
| 88 |
UPDATE t3 SET e=(SELECT b FROM t5 WHERE t5.a=t3.e); |
| 89 |
CREATE INDEX t1e ON t1(e); |
| 90 |
CREATE INDEX t2ed ON t2(e,d); |
| 91 |
CREATE VIEW v00(a,b,c,d,e) AS SELECT 1,1,1,1,'one'; |
| 92 |
CREATE VIEW v10(a,b,c,d,e) AS SELECT a,b,c,d,e FROM t1 WHERE a<>25; |
| 93 |
CREATE VIEW v20(a,b,c,d,e) AS SELECT a,b,c,d,e FROM t2 WHERE a<>25; |
| 94 |
CREATE VIEW v30(a,b,c,d,e) AS SELECT a,b,c,d,e FROM t3 WHERE a<>25; |
| 95 |
CREATE VIEW v40(a,b,c,d,e) AS SELECT a,b,c,d,e FROM t4 WHERE a<>25; |
| 96 |
CREATE VIEW v50(a,b) AS SELECT a,b FROM t5 WHERE a<>25; |
| 97 |
CREATE VIEW v11(a,b,c,d,e) AS SELECT a,b,c,d,e FROM t1 ORDER BY b LIMIT 10; |
| 98 |
CREATE VIEW v21(a,b,c,d,e) AS SELECT a,b,c,d,e FROM t2 ORDER BY b LIMIT 10; |
| 99 |
CREATE VIEW v31(a,b,c,d,e) AS SELECT a,b,c,d,e FROM t3 ORDER BY b LIMIT 10; |
| 100 |
CREATE VIEW v41(a,b,c,d,e) AS SELECT a,b,c,d,e FROM t4 ORDER BY b LIMIT 10; |
| 101 |
CREATE VIEW v51(a,b) AS SELECT a,b FROM t5 ORDER BY b LIMIT 10; |
| 102 |
CREATE VIEW v12(a,b,c,d,e) AS |
| 103 |
SELECT sum(a), avg(b), count(*), min(d), e FROM t1 GROUP BY 5; |
| 104 |
CREATE VIEW v22(a,b,c,d,e) AS |
| 105 |
SELECT sum(a), avg(b), count(*), min(d), e FROM t2 GROUP BY 5 |
| 106 |
HAVING count(*)>1 ORDER BY 3, 1; |
| 107 |
CREATE VIEW v32(a,b,c,d,e) AS |
| 108 |
SELECT sum(a), avg(b), count(*), min(d), e FROM t3 GROUP BY 5 |
| 109 |
HAVING count(*)>1 ORDER BY 3, 1; |
| 110 |
CREATE VIEW v42(a,b,c,d,e) AS |
| 111 |
SELECT sum(a), avg(b), count(*), min(d), e FROM t4 GROUP BY 5 |
| 112 |
HAVING min(d)<30 ORDER BY 3, 1; |
| 113 |
CREATE VIEW v52(a,b,c,d,e) AS |
| 114 |
SELECT count(*), min(b), substr(b,1,1), min(a), max(a) FROM t5 |
| 115 |
GROUP BY 3 ORDER BY 1; |
| 116 |
|
| 117 |
CREATE VIEW v13(a,b,c,d,e) AS |
| 118 |
SELECT a,b,c,d,e FROM t1 |
| 119 |
UNION SELECT a,b,c,d,e FROM t2 |
| 120 |
UNION SELECT a,b,c,d,e FROM t3; |
| 121 |
CREATE VIEW v23(a,b,c,d,e) AS |
| 122 |
SELECT a,b,c,d,e FROM t1 |
| 123 |
EXCEPT SELECT a,b,c,d,e FROM t1 WHERE b<25; |
| 124 |
|
| 125 |
CREATE VIEW v60(a,b,c,d,e) AS |
| 126 |
SELECT t1.a,t2.b,t1.c,t2.d,t1.e |
| 127 |
FROM t1 LEFT JOIN t2 ON (t1.a=t2.b); |
| 128 |
CREATE VIEW v61(a,b,c,d,e) AS |
| 129 |
SELECT t2.a,t3.b,t2.c,t3.d,t2.e |
| 130 |
FROM t2 LEFT JOIN t3 ON (t2.a=t3.a); |
| 131 |
CREATE VIEW v62(a,b,c,d,e) AS |
| 132 |
SELECT t1.a,t2.b,t3.c,t4.d,t5.b |
| 133 |
FROM t1 JOIN t2 ON (t1.a=t2.b) |
| 134 |
JOIN t3 ON (t1.a=t3.a) |
| 135 |
JOIN t4 ON (t4.b=t3.b) |
| 136 |
LEFT JOIN t5 ON (t5.a=t1.c); |
| 137 |
CREATE VIEW v70(a,b,c,d,e) AS |
| 138 |
WITH RECURSIVE c0(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c0 WHERE x<9) |
| 139 |
SELECT x, b, c, d, e FROM c0 JOIN t1 ON (t1.a=50-c0.x); |
| 140 |
COMMIT; |
| 141 |
VACUUM; |
| 142 |
.shell bin2c testdb01.db |