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