# 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