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
18set testdir [file dirname $argv0]
19source $testdir/tester.tcl
20set ::testprefix schema6
21do_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#
30proc 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#
57proc 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
79check_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
101check_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
120check_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
152check_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
164finish_test
165