1 files changed, 142 insertions, 0 deletions
diff --git a/test/optfuzz-db01.txt b/test/optfuzz-db01.txt
new file mode 100644
index 0000000..d9eef93
--- /dev/null
+++ b/test/optfuzz-db01.txt
@@ -0,0 +1,142 @@
+-- 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