3 # The author disclaims copyright to this source code. In place of
4 # a legal notice, here is a blessing:
6 # May you do good and not evil.
7 # May you find forgiveness for yourself and forgive others.
8 # May you share freely, never taking more than you give.
10 #***********************************************************************
12 # This file implements tests to show that certain CREATE TABLE statements
13 # generate identical database files. For example, changes in identifier
14 # names, white-space, and formatting of the CREATE TABLE statement should
15 # produce identical table content.
18 set testdir [file dirname $argv0]
19 source $testdir/tester.tcl
20 set ::testprefix schema6
22 # Command: check_same_database_content TESTNAME SQL1 SQL2 SQL3 ...
24 # This command creates fresh databases using SQL1 and subsequent arguments
25 # and checks to make sure the content of all database files is byte-for-byte
26 # identical. Page 1 of the database files is allowed to be different, since
27 # page 1 contains the sqlite_master table which is expected to vary.
29 proc check_same_database_content {basename args} {
37 set pgsz [db one {PRAGMA page_size}]
39 set sz [file size test.db]
40 set thishash [md5file test.db $pgsz [expr {$sz-$pgsz}]]
44 do_test $basename-$i "set x $thishash" $hash
50 # Command: check_different_database_content TESTNAME SQL1 SQL2 SQL3 ...
52 # This command creates fresh databases using SQL1 and subsequent arguments
53 # and checks to make sure the content of all database files is different
54 # in ways other than on page 1.
56 proc check_different_database_content {basename args} {
63 set pgsz [db one {PRAGMA page_size}]
65 set sz [file size test.db]
66 set thishash [md5file test.db $pgsz [expr {$sz-$pgsz}]]
67 set j [lsearch $hashes $thishash]
69 do_test $basename-$i "set x {$i is the same as $j}" "All are different"
71 do_test $basename-$i "set x {All are different}" "All are different"
73 lappend hashes $thishash
78 check_same_database_content 100 {
79 CREATE TABLE t1(a INTEGER PRIMARY KEY, b UNIQUE);
80 INSERT INTO t1(a,b) VALUES(123,'Four score and seven years ago...');
82 CREATE TABLE t1(xyz INTEGER, abc, PRIMARY KEY(xyz), UNIQUE(abc));
83 INSERT INTO t1(xyz,abc) VALUES(123,'Four score and seven years ago...');
85 CREATE TABLE t1(xyz INTEGER, abc, UNIQUE(abc), PRIMARY KEY(xyz));
86 INSERT INTO t1(xyz,abc) VALUES(123,'Four score and seven years ago...');
88 CREATE TABLE t1(a INTEGER PRIMARY KEY ASC, b UNIQUE);
89 INSERT INTO t1(a,b) VALUES(123,'Four score and seven years ago...');
91 CREATE TABLE t1(a INTEGER PRIMARY KEY, b);
92 CREATE UNIQUE INDEX t1b ON t1(b);
93 INSERT INTO t1(a,b) VALUES(123,'Four score and seven years ago...');
95 CREATE TABLE t1(a INTEGER PRIMARY KEY, b);
96 INSERT INTO t1(a,b) VALUES(123,'Four score and seven years ago...');
97 CREATE UNIQUE INDEX t1b ON t1(b);
100 check_same_database_content 110 {
101 CREATE TABLE t1(a INTEGER PRIMARY KEY UNIQUE, b UNIQUE);
102 INSERT INTO t1(a,b) VALUES(123,'Four score and seven years ago...');
104 CREATE TABLE t1(a INTEGER UNIQUE PRIMARY KEY, b UNIQUE);
105 INSERT INTO t1(a,b) VALUES(123,'Four score and seven years ago...');
107 CREATE TABLE t1(a INTEGER UNIQUE PRIMARY KEY, b UNIQUE, UNIQUE(a));
108 INSERT INTO t1(a,b) VALUES(123,'Four score and seven years ago...');
110 CREATE TABLE t1(a INTEGER UNIQUE PRIMARY KEY, b);
111 CREATE UNIQUE INDEX t1b ON t1(b);
112 INSERT INTO t1(a,b) VALUES(123,'Four score and seven years ago...');
114 CREATE TABLE t1(a INTEGER UNIQUE PRIMARY KEY, b);
115 INSERT INTO t1(a,b) VALUES(123,'Four score and seven years ago...');
116 CREATE UNIQUE INDEX t1b ON t1(b);
119 check_same_database_content 120 {
120 CREATE TABLE t1(a INTEGER PRIMARY KEY, b UNIQUE) WITHOUT ROWID;
121 INSERT INTO t1(a,b) VALUES(123,'Four score and seven years ago...');
123 CREATE TABLE t1(xyz INTEGER, abc, PRIMARY KEY(xyz), UNIQUE(abc))WITHOUT ROWID;
124 INSERT INTO t1(xyz,abc) VALUES(123,'Four score and seven years ago...');
126 CREATE TABLE t1(xyz INTEGER, abc, UNIQUE(abc), PRIMARY KEY(xyz))WITHOUT ROWID;
127 INSERT INTO t1(xyz,abc) VALUES(123,'Four score and seven years ago...');
129 CREATE TABLE t1(a INTEGER PRIMARY KEY ASC, b UNIQUE) WITHOUT ROWID;
130 INSERT INTO t1(a,b) VALUES(123,'Four score and seven years ago...');
132 CREATE TABLE t1(a INTEGER PRIMARY KEY UNIQUE, b UNIQUE) WITHOUT ROWID;
133 INSERT INTO t1(a,b) VALUES(123,'Four score and seven years ago...');
135 CREATE TABLE t1(a INTEGER UNIQUE PRIMARY KEY, b UNIQUE) WITHOUT ROWID;
136 INSERT INTO t1(a,b) VALUES(123,'Four score and seven years ago...');
138 CREATE TABLE t1(a INTEGER UNIQUE PRIMARY KEY, b UNIQUE, UNIQUE(a))
140 INSERT INTO t1(a,b) VALUES(123,'Four score and seven years ago...');
142 CREATE TABLE t1(a INTEGER PRIMARY KEY, b) WITHOUT ROWID;
143 CREATE UNIQUE INDEX t1b ON t1(b);
144 INSERT INTO t1(a,b) VALUES(123,'Four score and seven years ago...');
146 CREATE TABLE t1(a INTEGER PRIMARY KEY, b) WITHOUT ROWID;
147 INSERT INTO t1(a,b) VALUES(123,'Four score and seven years ago...');
148 CREATE UNIQUE INDEX t1b ON t1(b);
151 check_different_database_content 130 {
152 CREATE TABLE t1(a INTEGER PRIMARY KEY, b UNIQUE);
153 INSERT INTO t1(a,b) VALUES(123,'Four score and seven years ago...');
155 CREATE TABLE t1(a INTEGER PRIMARY KEY UNIQUE, b UNIQUE);
156 INSERT INTO t1(a,b) VALUES(123,'Four score and seven years ago...');
158 CREATE TABLE t1(a INTEGER PRIMARY KEY, b UNIQUE) WITHOUT ROWID;
159 INSERT INTO t1(a,b) VALUES(123,'Four score and seven years ago...');