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 forcedelete test.db 34 sqlite3 db test.db 35 db eval $sql 36 set pgsz [db one {PRAGMA page_size}] 37 db close 38 set sz [file size test.db] 39 set thishash [md5file test.db $pgsz [expr {$sz-$pgsz}]] 40 if {$i==0} { 41 set hash $thishash 42 } else { 43 do_test $basename-$i "set x $thishash" $hash 44 } 45 incr i 46 } 47} 48 49# Command: check_different_database_content TESTNAME SQL1 SQL2 SQL3 ... 50# 51# This command creates fresh databases using SQL1 and subsequent arguments 52# and checks to make sure the content of all database files is different 53# in ways other than on page 1. 54# 55proc check_different_database_content {basename args} { 56 set i 0 57 set hashes {} 58 foreach sql $args { 59 forcedelete test.db 60 sqlite3 db test.db 61 db eval $sql 62 set pgsz [db one {PRAGMA page_size}] 63 db close 64 set sz [file size test.db] 65 set thishash [md5file test.db $pgsz [expr {$sz-$pgsz}]] 66 set j [lsearch $hashes $thishash] 67 if {$j>=0} { 68 do_test $basename-$i "set x {$i is the same as $j}" "All are different" 69 } else { 70 do_test $basename-$i "set x {All are different}" "All are different" 71 } 72 lappend hashes $thishash 73 incr i 74 } 75} 76 77check_same_database_content 100 { 78 CREATE TABLE t1(a INTEGER PRIMARY KEY, b UNIQUE); 79 INSERT INTO t1(a,b) VALUES(123,'Four score and seven years ago...'); 80} { 81 CREATE TABLE t1(xyz INTEGER, abc, PRIMARY KEY(xyz), UNIQUE(abc)); 82 INSERT INTO t1(xyz,abc) VALUES(123,'Four score and seven years ago...'); 83} { 84 CREATE TABLE t1(xyz INTEGER, abc, UNIQUE(abc), PRIMARY KEY(xyz)); 85 INSERT INTO t1(xyz,abc) VALUES(123,'Four score and seven years ago...'); 86} { 87 CREATE TABLE t1(a INTEGER PRIMARY KEY ASC, b UNIQUE); 88 INSERT INTO t1(a,b) VALUES(123,'Four score and seven years ago...'); 89} { 90 CREATE TABLE t1(a INTEGER PRIMARY KEY, b); 91 CREATE UNIQUE INDEX t1b ON t1(b); 92 INSERT INTO t1(a,b) VALUES(123,'Four score and seven years ago...'); 93} { 94 CREATE TABLE t1(a INTEGER PRIMARY KEY, b); 95 INSERT INTO t1(a,b) VALUES(123,'Four score and seven years ago...'); 96 CREATE UNIQUE INDEX t1b ON t1(b); 97} 98 99check_same_database_content 110 { 100 CREATE TABLE t1(a INTEGER PRIMARY KEY UNIQUE, b UNIQUE); 101 INSERT INTO t1(a,b) VALUES(123,'Four score and seven years ago...'); 102} { 103 CREATE TABLE t1(a INTEGER UNIQUE PRIMARY KEY, b UNIQUE); 104 INSERT INTO t1(a,b) VALUES(123,'Four score and seven years ago...'); 105} { 106 CREATE TABLE t1(a INTEGER UNIQUE PRIMARY KEY, b UNIQUE, UNIQUE(a)); 107 INSERT INTO t1(a,b) VALUES(123,'Four score and seven years ago...'); 108} { 109 CREATE TABLE t1(a INTEGER UNIQUE PRIMARY KEY, b); 110 CREATE UNIQUE INDEX t1b ON t1(b); 111 INSERT INTO t1(a,b) VALUES(123,'Four score and seven years ago...'); 112} { 113 CREATE TABLE t1(a INTEGER UNIQUE PRIMARY KEY, b); 114 INSERT INTO t1(a,b) VALUES(123,'Four score and seven years ago...'); 115 CREATE UNIQUE INDEX t1b ON t1(b); 116} 117 118check_same_database_content 120 { 119 CREATE TABLE t1(a INTEGER PRIMARY KEY, b UNIQUE) WITHOUT ROWID; 120 INSERT INTO t1(a,b) VALUES(123,'Four score and seven years ago...'); 121} { 122 CREATE TABLE t1(xyz INTEGER, abc, PRIMARY KEY(xyz), UNIQUE(abc))WITHOUT ROWID; 123 INSERT INTO t1(xyz,abc) VALUES(123,'Four score and seven years ago...'); 124} { 125 CREATE TABLE t1(xyz INTEGER, abc, UNIQUE(abc), PRIMARY KEY(xyz))WITHOUT ROWID; 126 INSERT INTO t1(xyz,abc) VALUES(123,'Four score and seven years ago...'); 127} { 128 CREATE TABLE t1(a INTEGER PRIMARY KEY ASC, b UNIQUE) WITHOUT ROWID; 129 INSERT INTO t1(a,b) VALUES(123,'Four score and seven years ago...'); 130} { 131 CREATE TABLE t1(a INTEGER PRIMARY KEY UNIQUE, b UNIQUE) WITHOUT ROWID; 132 INSERT INTO t1(a,b) VALUES(123,'Four score and seven years ago...'); 133} { 134 CREATE TABLE t1(a INTEGER UNIQUE PRIMARY KEY, b UNIQUE) WITHOUT ROWID; 135 INSERT INTO t1(a,b) VALUES(123,'Four score and seven years ago...'); 136} { 137 CREATE TABLE t1(a INTEGER UNIQUE PRIMARY KEY, b UNIQUE, UNIQUE(a)) 138 WITHOUT ROWID; 139 INSERT INTO t1(a,b) VALUES(123,'Four score and seven years ago...'); 140} { 141 CREATE TABLE t1(a INTEGER PRIMARY KEY, b) WITHOUT ROWID; 142 CREATE UNIQUE INDEX t1b ON t1(b); 143 INSERT INTO t1(a,b) VALUES(123,'Four score and seven years ago...'); 144} { 145 CREATE TABLE t1(a INTEGER PRIMARY KEY, b) WITHOUT ROWID; 146 INSERT INTO t1(a,b) VALUES(123,'Four score and seven years ago...'); 147 CREATE UNIQUE INDEX t1b ON t1(b); 148} 149 150check_different_database_content 130 { 151 CREATE TABLE t1(a INTEGER PRIMARY KEY, b UNIQUE); 152 INSERT INTO t1(a,b) VALUES(123,'Four score and seven years ago...'); 153} { 154 CREATE TABLE t1(a INTEGER PRIMARY KEY UNIQUE, b UNIQUE); 155 INSERT INTO t1(a,b) VALUES(123,'Four score and seven years ago...'); 156} { 157 CREATE TABLE t1(a INTEGER PRIMARY KEY, b UNIQUE) WITHOUT ROWID; 158 INSERT INTO t1(a,b) VALUES(123,'Four score and seven years ago...'); 159} 160 161 162finish_test 163