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 21 22# Command: check_same_database_content TESTNAME SQL1 SQL2 SQL3 ... 23# 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. 28# 29proc check_same_database_content {basename args} { 30 set i 0 31 set hash {} 32 foreach sql $args { 33 catch {db close} 34 forcedelete test.db 35 sqlite3 db test.db 36 db eval $sql 37 set pgsz [db one {PRAGMA page_size}] 38 db close 39 set sz [file size test.db] 40 set thishash [md5file test.db $pgsz [expr {$sz-$pgsz}]] 41 if {$i==0} { 42 set hash $thishash 43 } else { 44 do_test $basename-$i "set x $thishash" $hash 45 } 46 incr i 47 } 48} 49 50# Command: check_different_database_content TESTNAME SQL1 SQL2 SQL3 ... 51# 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. 55# 56proc check_different_database_content {basename args} { 57 set i 0 58 set hashes {} 59 foreach sql $args { 60 forcedelete test.db 61 sqlite3 db test.db 62 db eval $sql 63 set pgsz [db one {PRAGMA page_size}] 64 db close 65 set sz [file size test.db] 66 set thishash [md5file test.db $pgsz [expr {$sz-$pgsz}]] 67 set j [lsearch $hashes $thishash] 68 if {$j>=0} { 69 do_test $basename-$i "set x {$i is the same as $j}" "All are different" 70 } else { 71 do_test $basename-$i "set x {All are different}" "All are different" 72 } 73 lappend hashes $thishash 74 incr i 75 } 76} 77 78check_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...'); 81} { 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...'); 84} { 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...'); 87} { 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...'); 90} { 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...'); 94} { 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); 98} 99 100check_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...'); 103} { 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...'); 106} { 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...'); 109} { 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...'); 113} { 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); 117} 118 119check_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...'); 122} { 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...'); 125} { 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...'); 128} { 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...'); 131} { 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...'); 134} { 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...'); 137} { 138 CREATE TABLE t1(a INTEGER UNIQUE PRIMARY KEY, b UNIQUE, UNIQUE(a)) 139 WITHOUT ROWID; 140 INSERT INTO t1(a,b) VALUES(123,'Four score and seven years ago...'); 141} { 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...'); 145} { 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); 149} 150 151check_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...'); 154} { 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...'); 157} { 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...'); 160} 161 162 163finish_test 164