AryaWu/sqlite

1-- Run this script through the sqlite3 command-line shell in order to generate2-- 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 creates6-- an array of bytes holding the content of that file.7--8-- This script is designed to create many tables and views all having9-- 5 columns, "a" through "e", and with a variety of integers, short strings,10-- and NULL values.11--12.open -new testdb01.db13PRAGMA page_size=512;14BEGIN;15CREATE TABLE t1(a INTEGER PRIMARY KEY, b INT, c INT, d INT, e INT);16WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<50)17INSERT INTO t1(a,b,c,d,e) SELECT x,abs(random()%51),18   abs(random()%100), abs(random()%51), abs(random()%100) FROM c;19CREATE TABLE t2(a INT, b INT, c INT,d INT,e INT,PRIMARY KEY(b,a))WITHOUT ROWID;20INSERT INTO t2 SELECT * FROM t1;21CREATE TABLE t3(a,b,c,d,e);22INSERT INTO t3 SELECT a,b,c,d,e FROM t1 ORDER BY random() LIMIT 5;23INSERT INTO t3 SELECT null,b,c,d,e FROM t1 ORDER BY random() LIMIT 5;24INSERT INTO t3 SELECT a,null,c,d,e FROM t1 ORDER BY random() LIMIT 5;25INSERT INTO t3 SELECT a,b,null,d,e FROM t1 ORDER BY random() LIMIT 5;26INSERT INTO t3 SELECT a,b,c,null,e FROM t1 ORDER BY random() LIMIT 5;27INSERT INTO t3 SELECT a,b,c,d,null FROM t1 ORDER BY random() LIMIT 5;28INSERT INTO t3 SELECT null,null,null,null,null FROM t1 LIMIT 5;29CREATE INDEX t3x1 ON t3(a,b,c,d,e);30CREATE TABLE t4(a INT UNIQUE NOT NULL, b INT UNIQUE NOT NULL,c,d,e);31INSERT OR IGNORE INTO t4 SELECT a,b,c,d,e FROM t3;32CREATE TABLE t5(a INTEGER PRIMARY KEY, b TEXT UNIQUE,c,d,e);33INSERT INTO t5(b) VALUES34   ('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');84UPDATE t1 SET e=(SELECT b FROM t5 WHERE t5.a=(t1.e%51));85UPDATE t5 SET (c,d,e) = 86   (SELECT c,d,e FROM t1 WHERE t1.a=abs(t5.a+random()/100)%50+1);87UPDATE t2 SET e=(SELECT b FROM t5 WHERE t5.a=(t2.e%51));88UPDATE t3 SET e=(SELECT b FROM t5 WHERE t5.a=t3.e);89CREATE INDEX t1e ON t1(e);90CREATE INDEX t2ed ON t2(e,d);91CREATE VIEW v00(a,b,c,d,e) AS SELECT 1,1,1,1,'one';92CREATE VIEW v10(a,b,c,d,e) AS SELECT a,b,c,d,e FROM t1 WHERE a<>25;93CREATE VIEW v20(a,b,c,d,e) AS SELECT a,b,c,d,e FROM t2 WHERE a<>25;94CREATE VIEW v30(a,b,c,d,e) AS SELECT a,b,c,d,e FROM t3 WHERE a<>25;95CREATE VIEW v40(a,b,c,d,e) AS SELECT a,b,c,d,e FROM t4 WHERE a<>25;96CREATE VIEW v50(a,b) AS SELECT a,b FROM t5 WHERE a<>25;97CREATE VIEW v11(a,b,c,d,e) AS SELECT a,b,c,d,e FROM t1 ORDER BY b LIMIT 10;98CREATE VIEW v21(a,b,c,d,e) AS SELECT a,b,c,d,e FROM t2 ORDER BY b LIMIT 10;99CREATE VIEW v31(a,b,c,d,e) AS SELECT a,b,c,d,e FROM t3 ORDER BY b LIMIT 10;100CREATE VIEW v41(a,b,c,d,e) AS SELECT a,b,c,d,e FROM t4 ORDER BY b LIMIT 10;101CREATE VIEW v51(a,b) AS SELECT a,b FROM t5 ORDER BY b LIMIT 10;102CREATE VIEW v12(a,b,c,d,e) AS103  SELECT sum(a), avg(b), count(*), min(d), e FROM t1 GROUP BY 5;104CREATE VIEW v22(a,b,c,d,e) AS105  SELECT sum(a), avg(b), count(*), min(d), e FROM t2 GROUP BY 5106    HAVING count(*)>1 ORDER BY 3, 1;107CREATE VIEW v32(a,b,c,d,e) AS108  SELECT sum(a), avg(b), count(*), min(d), e FROM t3 GROUP BY 5109    HAVING count(*)>1 ORDER BY 3, 1;110CREATE VIEW v42(a,b,c,d,e) AS111  SELECT sum(a), avg(b), count(*), min(d), e FROM t4 GROUP BY 5112    HAVING min(d)<30 ORDER BY 3, 1;113CREATE VIEW v52(a,b,c,d,e) AS114  SELECT count(*), min(b), substr(b,1,1), min(a), max(a) FROM t5115   GROUP BY 3 ORDER BY 1;116 117CREATE VIEW v13(a,b,c,d,e) AS118  SELECT a,b,c,d,e FROM t1119  UNION SELECT a,b,c,d,e FROM t2120  UNION SELECT a,b,c,d,e FROM t3;121CREATE VIEW v23(a,b,c,d,e) AS122  SELECT a,b,c,d,e FROM t1123  EXCEPT SELECT a,b,c,d,e FROM t1 WHERE b<25;124 125CREATE VIEW v60(a,b,c,d,e) AS126  SELECT t1.a,t2.b,t1.c,t2.d,t1.e127    FROM t1 LEFT JOIN t2 ON (t1.a=t2.b);128CREATE VIEW v61(a,b,c,d,e) AS129  SELECT t2.a,t3.b,t2.c,t3.d,t2.e130    FROM t2 LEFT JOIN t3 ON (t2.a=t3.a);131CREATE VIEW v62(a,b,c,d,e) AS132  SELECT t1.a,t2.b,t3.c,t4.d,t5.b133    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);137CREATE VIEW v70(a,b,c,d,e) AS138  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);140COMMIT;141VACUUM;142.shell bin2c testdb01.db143