Debian Codegraph @

test/schema6.test

raw
1 # 2017-07-30
2 #
3 # The author disclaims copyright to this source code. In place of
4 # a legal notice, here is a blessing:
5 #
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.
9 #
10 #***********************************************************************
11 #
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.
16 #
17
18 set testdir [file dirname $argv0]
19 source $testdir/tester.tcl
20 set ::testprefix schema6
21 do_not_use_codec
22
23 # Command: check_same_database_content TESTNAME SQL1 SQL2 SQL3 ...
24 #
25 # This command creates fresh databases using SQL1 and subsequent arguments
26 # and checks to make sure the content of all database files is byte-for-byte
27 # identical. Page 1 of the database files is allowed to be different, since
28 # page 1 contains the sqlite_master table which is expected to vary.
29 #
30 proc check_same_database_content {basename args} {
31 set i 0
32 set hash {}
33 foreach sql $args {
34 catch {db close}
35 forcedelete test.db
36 sqlite3 db test.db
37 db eval $sql
38 set pgsz [db one {PRAGMA page_size}]
39 db close
40 set sz [file size test.db]
41 set thishash [md5file test.db $pgsz [expr {$sz-$pgsz}]]
42 if {$i==0} {
43 set hash $thishash
44 } else {
45 do_test $basename-$i "set x $thishash" $hash
46 }
47 incr i
48 }
49 }
50
51 # Command: check_different_database_content TESTNAME SQL1 SQL2 SQL3 ...
52 #
53 # This command creates fresh databases using SQL1 and subsequent arguments
54 # and checks to make sure the content of all database files is different
55 # in ways other than on page 1.
56 #
57 proc check_different_database_content {basename args} {
58 set i 0
59 set hashes {}
60 foreach sql $args {
61 forcedelete test.db
62 sqlite3 db test.db
63 db eval $sql
64 set pgsz [db one {PRAGMA page_size}]
65 db close
66 set sz [file size test.db]
67 set thishash [md5file test.db $pgsz [expr {$sz-$pgsz}]]
68 set j [lsearch $hashes $thishash]
69 if {$j>=0} {
70 do_test $basename-$i "set x {$i is the same as $j}" "All are different"
71 } else {
72 do_test $basename-$i "set x {All are different}" "All are different"
73 }
74 lappend hashes $thishash
75 incr i
76 }
77 }
78
79 check_same_database_content 100 {
80 CREATE TABLE t1(a INTEGER PRIMARY KEY, b UNIQUE);
81 INSERT INTO t1(a,b) VALUES(123,'Four score and seven years ago...');
82 } {
83 CREATE TABLE t1(xyz INTEGER, abc, PRIMARY KEY(xyz), UNIQUE(abc));
84 INSERT INTO t1(xyz,abc) VALUES(123,'Four score and seven years ago...');
85 } {
86 CREATE TABLE t1(xyz INTEGER, abc, UNIQUE(abc), PRIMARY KEY(xyz));
87 INSERT INTO t1(xyz,abc) VALUES(123,'Four score and seven years ago...');
88 } {
89 CREATE TABLE t1(a INTEGER PRIMARY KEY ASC, b UNIQUE);
90 INSERT INTO t1(a,b) VALUES(123,'Four score and seven years ago...');
91 } {
92 CREATE TABLE t1(a INTEGER PRIMARY KEY, b);
93 CREATE UNIQUE INDEX t1b ON t1(b);
94 INSERT INTO t1(a,b) VALUES(123,'Four score and seven years ago...');
95 } {
96 CREATE TABLE t1(a INTEGER PRIMARY KEY, b);
97 INSERT INTO t1(a,b) VALUES(123,'Four score and seven years ago...');
98 CREATE UNIQUE INDEX t1b ON t1(b);
99 }
100
101 check_same_database_content 110 {
102 CREATE TABLE t1(a INTEGER PRIMARY KEY UNIQUE, b UNIQUE);
103 INSERT INTO t1(a,b) VALUES(123,'Four score and seven years ago...');
104 } {
105 CREATE TABLE t1(a INTEGER UNIQUE PRIMARY KEY, b UNIQUE);
106 INSERT INTO t1(a,b) VALUES(123,'Four score and seven years ago...');
107 } {
108 CREATE TABLE t1(a INTEGER UNIQUE PRIMARY KEY, b UNIQUE, UNIQUE(a));
109 INSERT INTO t1(a,b) VALUES(123,'Four score and seven years ago...');
110 } {
111 CREATE TABLE t1(a INTEGER UNIQUE PRIMARY KEY, b);
112 CREATE UNIQUE INDEX t1b ON t1(b);
113 INSERT INTO t1(a,b) VALUES(123,'Four score and seven years ago...');
114 } {
115 CREATE TABLE t1(a INTEGER UNIQUE PRIMARY KEY, b);
116 INSERT INTO t1(a,b) VALUES(123,'Four score and seven years ago...');
117 CREATE UNIQUE INDEX t1b ON t1(b);
118 }
119
120 check_same_database_content 120 {
121 CREATE TABLE t1(a INTEGER PRIMARY KEY, b UNIQUE) WITHOUT ROWID;
122 INSERT INTO t1(a,b) VALUES(123,'Four score and seven years ago...');
123 } {
124 CREATE TABLE t1(xyz INTEGER, abc, PRIMARY KEY(xyz), UNIQUE(abc))WITHOUT ROWID;
125 INSERT INTO t1(xyz,abc) VALUES(123,'Four score and seven years ago...');
126 } {
127 CREATE TABLE t1(xyz INTEGER, abc, UNIQUE(abc), PRIMARY KEY(xyz))WITHOUT ROWID;
128 INSERT INTO t1(xyz,abc) VALUES(123,'Four score and seven years ago...');
129 } {
130 CREATE TABLE t1(a INTEGER PRIMARY KEY ASC, b UNIQUE) WITHOUT ROWID;
131 INSERT INTO t1(a,b) VALUES(123,'Four score and seven years ago...');
132 } {
133 CREATE TABLE t1(a INTEGER PRIMARY KEY UNIQUE, b UNIQUE) WITHOUT ROWID;
134 INSERT INTO t1(a,b) VALUES(123,'Four score and seven years ago...');
135 } {
136 CREATE TABLE t1(a INTEGER UNIQUE PRIMARY KEY, b UNIQUE) WITHOUT ROWID;
137 INSERT INTO t1(a,b) VALUES(123,'Four score and seven years ago...');
138 } {
139 CREATE TABLE t1(a INTEGER UNIQUE PRIMARY KEY, b UNIQUE, UNIQUE(a))
140 WITHOUT ROWID;
141 INSERT INTO t1(a,b) VALUES(123,'Four score and seven years ago...');
142 } {
143 CREATE TABLE t1(a INTEGER PRIMARY KEY, b) WITHOUT ROWID;
144 CREATE UNIQUE INDEX t1b ON t1(b);
145 INSERT INTO t1(a,b) VALUES(123,'Four score and seven years ago...');
146 } {
147 CREATE TABLE t1(a INTEGER PRIMARY KEY, b) WITHOUT ROWID;
148 INSERT INTO t1(a,b) VALUES(123,'Four score and seven years ago...');
149 CREATE UNIQUE INDEX t1b ON t1(b);
150 }
151
152 check_different_database_content 130 {
153 CREATE TABLE t1(a INTEGER PRIMARY KEY, b UNIQUE);
154 INSERT INTO t1(a,b) VALUES(123,'Four score and seven years ago...');
155 } {
156 CREATE TABLE t1(a INTEGER PRIMARY KEY UNIQUE, b UNIQUE);
157 INSERT INTO t1(a,b) VALUES(123,'Four score and seven years ago...');
158 } {
159 CREATE TABLE t1(a INTEGER PRIMARY KEY, b UNIQUE) WITHOUT ROWID;
160 INSERT INTO t1(a,b) VALUES(123,'Four score and seven years ago...');
161 }
162
163
164 finish_test