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