github.com/jdgcs/sqlite3@v1.12.1-0.20210908114423-bc5f96e4dd51/testdata/tcl/optfuzz-db01.txt (about)

     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