| -- Run this script through the sqlite3 command-line shell in order to generate |
| -- a database file containing lots of data for testing purposes. |
| -- |
| -- This script assumes that the "bin2c" program is available on ones $PATH. |
| -- The "bin2c" program reads a binary file and outputs C-code that creates |
| -- an array of bytes holding the content of that file. |
| -- |
| -- This script is designed to create many tables and views all having |
| -- 5 columns, "a" through "e", and with a variety of integers, short strings, |
| -- and NULL values. |
| -- |
| .open -new testdb01.db |
| PRAGMA page_size=512; |
| BEGIN; |
| CREATE TABLE t1(a INTEGER PRIMARY KEY, b INT, c INT, d INT, e INT); |
| WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<50) |
| INSERT INTO t1(a,b,c,d,e) SELECT x,abs(random()%51), |
| abs(random()%100), abs(random()%51), abs(random()%100) FROM c; |
| CREATE TABLE t2(a INT, b INT, c INT,d INT,e INT,PRIMARY KEY(b,a))WITHOUT ROWID; |
| INSERT INTO t2 SELECT * FROM t1; |
| CREATE TABLE t3(a,b,c,d,e); |
| INSERT INTO t3 SELECT a,b,c,d,e FROM t1 ORDER BY random() LIMIT 5; |
| INSERT INTO t3 SELECT null,b,c,d,e FROM t1 ORDER BY random() LIMIT 5; |
| INSERT INTO t3 SELECT a,null,c,d,e FROM t1 ORDER BY random() LIMIT 5; |
| INSERT INTO t3 SELECT a,b,null,d,e FROM t1 ORDER BY random() LIMIT 5; |
| INSERT INTO t3 SELECT a,b,c,null,e FROM t1 ORDER BY random() LIMIT 5; |
| INSERT INTO t3 SELECT a,b,c,d,null FROM t1 ORDER BY random() LIMIT 5; |
| INSERT INTO t3 SELECT null,null,null,null,null FROM t1 LIMIT 5; |
| CREATE INDEX t3x1 ON t3(a,b,c,d,e); |
| CREATE TABLE t4(a INT UNIQUE NOT NULL, b INT UNIQUE NOT NULL,c,d,e); |
| INSERT OR IGNORE INTO t4 SELECT a,b,c,d,e FROM t3; |
| CREATE TABLE t5(a INTEGER PRIMARY KEY, b TEXT UNIQUE,c,d,e); |
| INSERT INTO t5(b) VALUES |
| ('truth'), |
| ('works'), |
| ('offer'), |
| ('can'), |
| ('anger'), |
| ('wisdom'), |
| ('send'), |
| ('though'), |
| ('save'), |
| ('between'), |
| ('some'), |
| ('wine'), |
| ('ark'), |
| ('smote'), |
| ('therein'), |
| ('shew'), |
| ('morning'), |
| ('dwelt'), |
| ('begat'), |
| ('nothing'), |
| ('war'), |
| ('above'), |
| ('known'), |
| ('sacrifice'), |
| ('tell'), |
| ('departed'), |
| ('thyself'), |
| ('places'), |
| ('bear'), |
| ('part'), |
| ('while'), |
| ('gone'), |
| ('cubits'), |
| ('walk'), |
| ('long'), |
| ('near'), |
| ('serve'), |
| ('fruit'), |
| ('doth'), |
| ('poor'), |
| ('ways'), |
| ('child'), |
| ('temple'), |
| ('angel'), |
| ('inhabitants'), |
| ('oil'), |
| ('died'), |
| ('six'), |
| ('tree'), |
| ('wrath'); |
| UPDATE t1 SET e=(SELECT b FROM t5 WHERE t5.a=(t1.e%51)); |
| UPDATE t5 SET (c,d,e) = |
| (SELECT c,d,e FROM t1 WHERE t1.a=abs(t5.a+random()/100)%50+1); |
| UPDATE t2 SET e=(SELECT b FROM t5 WHERE t5.a=(t2.e%51)); |
| UPDATE t3 SET e=(SELECT b FROM t5 WHERE t5.a=t3.e); |
| CREATE INDEX t1e ON t1(e); |
| CREATE INDEX t2ed ON t2(e,d); |
| CREATE VIEW v00(a,b,c,d,e) AS SELECT 1,1,1,1,'one'; |
| CREATE VIEW v10(a,b,c,d,e) AS SELECT a,b,c,d,e FROM t1 WHERE a<>25; |
| CREATE VIEW v20(a,b,c,d,e) AS SELECT a,b,c,d,e FROM t2 WHERE a<>25; |
| CREATE VIEW v30(a,b,c,d,e) AS SELECT a,b,c,d,e FROM t3 WHERE a<>25; |
| CREATE VIEW v40(a,b,c,d,e) AS SELECT a,b,c,d,e FROM t4 WHERE a<>25; |
| CREATE VIEW v50(a,b) AS SELECT a,b FROM t5 WHERE a<>25; |
| CREATE VIEW v11(a,b,c,d,e) AS SELECT a,b,c,d,e FROM t1 ORDER BY b LIMIT 10; |
| CREATE VIEW v21(a,b,c,d,e) AS SELECT a,b,c,d,e FROM t2 ORDER BY b LIMIT 10; |
| CREATE VIEW v31(a,b,c,d,e) AS SELECT a,b,c,d,e FROM t3 ORDER BY b LIMIT 10; |
| CREATE VIEW v41(a,b,c,d,e) AS SELECT a,b,c,d,e FROM t4 ORDER BY b LIMIT 10; |
| CREATE VIEW v51(a,b) AS SELECT a,b FROM t5 ORDER BY b LIMIT 10; |
| CREATE VIEW v12(a,b,c,d,e) AS |
| SELECT sum(a), avg(b), count(*), min(d), e FROM t1 GROUP BY 5; |
| CREATE VIEW v22(a,b,c,d,e) AS |
| SELECT sum(a), avg(b), count(*), min(d), e FROM t2 GROUP BY 5 |
| HAVING count(*)>1 ORDER BY 3, 1; |
| CREATE VIEW v32(a,b,c,d,e) AS |
| SELECT sum(a), avg(b), count(*), min(d), e FROM t3 GROUP BY 5 |
| HAVING count(*)>1 ORDER BY 3, 1; |
| CREATE VIEW v42(a,b,c,d,e) AS |
| SELECT sum(a), avg(b), count(*), min(d), e FROM t4 GROUP BY 5 |
| HAVING min(d)<30 ORDER BY 3, 1; |
| CREATE VIEW v52(a,b,c,d,e) AS |
| SELECT count(*), min(b), substr(b,1,1), min(a), max(a) FROM t5 |
| GROUP BY 3 ORDER BY 1; |
| |
| CREATE VIEW v13(a,b,c,d,e) AS |
| SELECT a,b,c,d,e FROM t1 |
| UNION SELECT a,b,c,d,e FROM t2 |
| UNION SELECT a,b,c,d,e FROM t3; |
| CREATE VIEW v23(a,b,c,d,e) AS |
| SELECT a,b,c,d,e FROM t1 |
| EXCEPT SELECT a,b,c,d,e FROM t1 WHERE b<25; |
| |
| CREATE VIEW v60(a,b,c,d,e) AS |
| SELECT t1.a,t2.b,t1.c,t2.d,t1.e |
| FROM t1 LEFT JOIN t2 ON (t1.a=t2.b); |
| CREATE VIEW v61(a,b,c,d,e) AS |
| SELECT t2.a,t3.b,t2.c,t3.d,t2.e |
| FROM t2 LEFT JOIN t3 ON (t2.a=t3.a); |
| CREATE VIEW v62(a,b,c,d,e) AS |
| SELECT t1.a,t2.b,t3.c,t4.d,t5.b |
| FROM t1 JOIN t2 ON (t1.a=t2.b) |
| JOIN t3 ON (t1.a=t3.a) |
| JOIN t4 ON (t4.b=t3.b) |
| LEFT JOIN t5 ON (t5.a=t1.c); |
| CREATE VIEW v70(a,b,c,d,e) AS |
| WITH RECURSIVE c0(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c0 WHERE x<9) |
| SELECT x, b, c, d, e FROM c0 JOIN t1 ON (t1.a=50-c0.x); |
| COMMIT; |
| VACUUM; |
| .shell bin2c testdb01.db |