gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/schema6.test (about) 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 18 set testdir [file dirname $argv0] 19 source $testdir/tester.tcl 20 set ::testprefix schema6 21 do_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 # 30 proc 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 # 57 proc 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 79 check_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 101 check_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 120 check_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 152 check_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 164 finish_test