# 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