AryaWu/sqlite
0
1# 2017-07-302#3# The author disclaims copyright to this source code. In place of4# 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 statements13# generate identical database files. For example, changes in identifier14# names, white-space, and formatting of the CREATE TABLE statement should15# produce identical table content.16#17 18set testdir [file dirname $argv0]19source $testdir/tester.tcl20set ::testprefix schema621do_not_use_codec22 23# Command: check_same_database_content TESTNAME SQL1 SQL2 SQL3 ...24#25# This command creates fresh databases using SQL1 and subsequent arguments26# and checks to make sure the content of all database files is byte-for-byte27# identical. Page 1 of the database files is allowed to be different, since28# page 1 contains the sqlite_master table which is expected to vary.29#30proc check_same_database_content {basename args} {31 set i 032 set hash {}33 foreach sql $args {34 catch {db close}35 forcedelete test.db36 sqlite3 db test.db37 db eval $sql38 set pgsz [db one {PRAGMA page_size}]39 db close40 set sz [file size test.db]41 set thishash [md5file test.db $pgsz [expr {$sz-$pgsz}]]42 if {$i==0} {43 set hash $thishash44 } else {45 do_test $basename-$i "set x $thishash" $hash46 }47 incr i48 }49}50 51# Command: check_different_database_content TESTNAME SQL1 SQL2 SQL3 ...52#53# This command creates fresh databases using SQL1 and subsequent arguments54# and checks to make sure the content of all database files is different55# in ways other than on page 1.56#57proc check_different_database_content {basename args} {58 set i 059 set hashes {}60 foreach sql $args {61 forcedelete test.db62 sqlite3 db test.db63 db eval $sql64 set pgsz [db one {PRAGMA page_size}]65 db close66 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 $thishash75 incr i76 }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_test165