diff options
Diffstat (limited to 'test/schema6.test')
| -rw-r--r-- | test/schema6.test | 164 |
1 files changed, 164 insertions, 0 deletions
diff --git a/test/schema6.test b/test/schema6.test new file mode 100644 index 0000000..0eb8a1c --- /dev/null +++ b/test/schema6.test @@ -0,0 +1,164 @@ +# 2017-07-30 +# +# The author disclaims copyright to this source code. In place of +# a legal notice, here is a blessing: +# +# May you do good and not evil. +# May you find forgiveness for yourself and forgive others. +# May you share freely, never taking more than you give. +# +#*********************************************************************** +# +# This file implements tests to show that certain CREATE TABLE statements +# generate identical database files. For example, changes in identifier +# names, white-space, and formatting of the CREATE TABLE statement should +# produce identical table content. +# + +set testdir [file dirname $argv0] +source $testdir/tester.tcl +set ::testprefix schema6 +do_not_use_codec + +# Command: check_same_database_content TESTNAME SQL1 SQL2 SQL3 ... +# +# This command creates fresh databases using SQL1 and subsequent arguments +# and checks to make sure the content of all database files is byte-for-byte +# identical. Page 1 of the database files is allowed to be different, since +# page 1 contains the sqlite_master table which is expected to vary. +# +proc check_same_database_content {basename args} { + set i 0 + set hash {} + foreach sql $args { + catch {db close} + forcedelete test.db + sqlite3 db test.db + db eval $sql + set pgsz [db one {PRAGMA page_size}] + db close + set sz [file size test.db] + set thishash [md5file test.db $pgsz [expr {$sz-$pgsz}]] + if {$i==0} { + set hash $thishash + } else { + do_test $basename-$i "set x $thishash" $hash + } + incr i + } +} + +# Command: check_different_database_content TESTNAME SQL1 SQL2 SQL3 ... +# +# This command creates fresh databases using SQL1 and subsequent arguments +# and checks to make sure the content of all database files is different +# in ways other than on page 1. +# +proc check_different_database_content {basename args} { + set i 0 + set hashes {} + foreach sql $args { + forcedelete test.db + sqlite3 db test.db + db eval $sql + set pgsz [db one {PRAGMA page_size}] + db close + set sz [file size test.db] + set thishash [md5file test.db $pgsz [expr {$sz-$pgsz}]] + set j [lsearch $hashes $thishash] + if {$j>=0} { + do_test $basename-$i "set x {$i is the same as $j}" "All are different" + } else { + do_test $basename-$i "set x {All are different}" "All are different" + } + lappend hashes $thishash + incr i + } +} + +check_same_database_content 100 { + CREATE TABLE t1(a INTEGER PRIMARY KEY, b UNIQUE); + INSERT INTO t1(a,b) VALUES(123,'Four score and seven years ago...'); +} { + CREATE TABLE t1(xyz INTEGER, abc, PRIMARY KEY(xyz), UNIQUE(abc)); + INSERT INTO t1(xyz,abc) VALUES(123,'Four score and seven years ago...'); +} { + CREATE TABLE t1(xyz INTEGER, abc, UNIQUE(abc), PRIMARY KEY(xyz)); + INSERT INTO t1(xyz,abc) VALUES(123,'Four score and seven years ago...'); +} { + CREATE TABLE t1(a INTEGER PRIMARY KEY ASC, b UNIQUE); + INSERT INTO t1(a,b) VALUES(123,'Four score and seven years ago...'); +} { + CREATE TABLE t1(a INTEGER PRIMARY KEY, b); + CREATE UNIQUE INDEX t1b ON t1(b); + INSERT INTO t1(a,b) VALUES(123,'Four score and seven years ago...'); +} { + CREATE TABLE t1(a INTEGER PRIMARY KEY, b); + INSERT INTO t1(a,b) VALUES(123,'Four score and seven years ago...'); + CREATE UNIQUE INDEX t1b ON t1(b); +} + +check_same_database_content 110 { + CREATE TABLE t1(a INTEGER PRIMARY KEY UNIQUE, b UNIQUE); + INSERT INTO t1(a,b) VALUES(123,'Four score and seven years ago...'); +} { + CREATE TABLE t1(a INTEGER UNIQUE PRIMARY KEY, b UNIQUE); + INSERT INTO t1(a,b) VALUES(123,'Four score and seven years ago...'); +} { + CREATE TABLE t1(a INTEGER UNIQUE PRIMARY KEY, b UNIQUE, UNIQUE(a)); + INSERT INTO t1(a,b) VALUES(123,'Four score and seven years ago...'); +} { + CREATE TABLE t1(a INTEGER UNIQUE PRIMARY KEY, b); + CREATE UNIQUE INDEX t1b ON t1(b); + INSERT INTO t1(a,b) VALUES(123,'Four score and seven years ago...'); +} { + CREATE TABLE t1(a INTEGER UNIQUE PRIMARY KEY, b); + INSERT INTO t1(a,b) VALUES(123,'Four score and seven years ago...'); + CREATE UNIQUE INDEX t1b ON t1(b); +} + +check_same_database_content 120 { + CREATE TABLE t1(a INTEGER PRIMARY KEY, b UNIQUE) WITHOUT ROWID; + INSERT INTO t1(a,b) VALUES(123,'Four score and seven years ago...'); +} { + CREATE TABLE t1(xyz INTEGER, abc, PRIMARY KEY(xyz), UNIQUE(abc))WITHOUT ROWID; + INSERT INTO t1(xyz,abc) VALUES(123,'Four score and seven years ago...'); +} { + CREATE TABLE t1(xyz INTEGER, abc, UNIQUE(abc), PRIMARY KEY(xyz))WITHOUT ROWID; + INSERT INTO t1(xyz,abc) VALUES(123,'Four score and seven years ago...'); +} { + CREATE TABLE t1(a INTEGER PRIMARY KEY ASC, b UNIQUE) WITHOUT ROWID; + INSERT INTO t1(a,b) VALUES(123,'Four score and seven years ago...'); +} { + CREATE TABLE t1(a INTEGER PRIMARY KEY UNIQUE, b UNIQUE) WITHOUT ROWID; + INSERT INTO t1(a,b) VALUES(123,'Four score and seven years ago...'); +} { + CREATE TABLE t1(a INTEGER UNIQUE PRIMARY KEY, b UNIQUE) WITHOUT ROWID; + INSERT INTO t1(a,b) VALUES(123,'Four score and seven years ago...'); +} { + CREATE TABLE t1(a INTEGER UNIQUE PRIMARY KEY, b UNIQUE, UNIQUE(a)) + WITHOUT ROWID; + INSERT INTO t1(a,b) VALUES(123,'Four score and seven years ago...'); +} { + CREATE TABLE t1(a INTEGER PRIMARY KEY, b) WITHOUT ROWID; + CREATE UNIQUE INDEX t1b ON t1(b); + INSERT INTO t1(a,b) VALUES(123,'Four score and seven years ago...'); +} { + CREATE TABLE t1(a INTEGER PRIMARY KEY, b) WITHOUT ROWID; + INSERT INTO t1(a,b) VALUES(123,'Four score and seven years ago...'); + CREATE UNIQUE INDEX t1b ON t1(b); +} + +check_different_database_content 130 { + CREATE TABLE t1(a INTEGER PRIMARY KEY, b UNIQUE); + INSERT INTO t1(a,b) VALUES(123,'Four score and seven years ago...'); +} { + CREATE TABLE t1(a INTEGER PRIMARY KEY UNIQUE, b UNIQUE); + INSERT INTO t1(a,b) VALUES(123,'Four score and seven years ago...'); +} { + CREATE TABLE t1(a INTEGER PRIMARY KEY, b UNIQUE) WITHOUT ROWID; + INSERT INTO t1(a,b) VALUES(123,'Four score and seven years ago...'); +} + + +finish_test |