1# 2007 April 6 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# This file implements regression tests for SQLite library. 12# 13# This file implements tests to verify database file format. 14# 15# $Id: filefmt.test,v 1.3 2009/06/18 11:34:43 drh Exp $ 16 17set testdir [file dirname $argv0] 18source $testdir/tester.tcl 19 20# Do not use a codec for tests in this file, as the database file is 21# manipulated directly using tcl scripts (using the [hexio_write] command). 22# 23do_not_use_codec 24 25db close 26forcedelete test.db test.db-journal 27 28# Database begins with valid 16-byte header string. 29# 30do_test filefmt-1.1 { 31 sqlite3 db test.db 32 db eval {CREATE TABLE t1(x)} 33 db close 34 hexio_read test.db 0 16 35} {53514C69746520666F726D6174203300} 36 37# If the 16-byte header is changed, the file will not open 38# 39do_test filefmt-1.2 { 40 hexio_write test.db 0 54 41 set x [catch {sqlite3 db test.db} err] 42 lappend x $err 43} {0 {}} 44do_test filefmt-1.3 { 45 catchsql { 46 SELECT count(*) FROM sqlite_master 47 } 48} {1 {file is not a database}} 49do_test filefmt-1.4 { 50 db close 51 hexio_write test.db 0 53 52 sqlite3 db test.db 53 catchsql { 54 SELECT count(*) FROM sqlite_master 55 } 56} {0 1} 57 58# The page-size is stored at offset 16 59# 60ifcapable pager_pragmas { 61 foreach pagesize {512 1024 2048 4096 8192 16384 32768} { 62 if {[info exists SQLITE_MAX_PAGE_SIZE] 63 && $pagesize>$SQLITE_MAX_PAGE_SIZE} continue 64 do_test filefmt-1.5.$pagesize.1 { 65 db close 66 forcedelete test.db 67 sqlite3 db test.db 68 db eval "PRAGMA auto_vacuum=OFF" 69 db eval "PRAGMA page_size=$pagesize" 70 db eval {CREATE TABLE t1(x)} 71 file size test.db 72 } [expr $pagesize*2] 73 do_test filefmt-1.5.$pagesize.2 { 74 hexio_get_int [hexio_read test.db 16 2] 75 } $pagesize 76 } 77} 78 79# The page-size must be a power of 2 80# 81do_test filefmt-1.6 { 82 db close 83 hexio_write test.db 16 [hexio_render_int16 1025] 84 sqlite3 db test.db 85 catchsql { 86 SELECT count(*) FROM sqlite_master 87 } 88} {1 {file is not a database}} 89 90 91# The page-size must be at least 512 bytes 92# 93do_test filefmt-1.7 { 94 db close 95 hexio_write test.db 16 [hexio_render_int16 256] 96 sqlite3 db test.db 97 catchsql { 98 SELECT count(*) FROM sqlite_master 99 } 100} {1 {file is not a database}} 101 102# Usable space per page (page-size minus unused space per page) 103# must be at least 480 bytes 104# 105ifcapable pager_pragmas { 106 do_test filefmt-1.8 { 107 db close 108 forcedelete test.db 109 sqlite3 db test.db 110 db eval {PRAGMA page_size=512; CREATE TABLE t1(x)} 111 db close 112 hexio_write test.db 20 21 113 sqlite3 db test.db 114 catchsql { 115 SELECT count(*) FROM sqlite_master 116 } 117 } {1 {file is not a database}} 118} 119 120#------------------------------------------------------------------------- 121# The following block of tests - filefmt-2.* - test that versions 3.7.0 122# and later can read and write databases that have been modified or created 123# by 3.6.23.1 and earlier. The difference difference is that 3.7.0 stores 124# the size of the database in the database file header, whereas 3.6.23.1 125# always derives this from the size of the file. 126# 127db close 128forcedelete test.db 129 130set a_string_counter 1 131proc a_string {n} { 132 incr ::a_string_counter 133 string range [string repeat "${::a_string_counter}." $n] 1 $n 134} 135sqlite3 db test.db 136db func a_string a_string 137 138do_execsql_test filefmt-2.1.1 { 139 PRAGMA page_size = 1024; 140 PRAGMA auto_vacuum = 0; 141 CREATE TABLE t1(a); 142 CREATE INDEX i1 ON t1(a); 143 INSERT INTO t1 VALUES(a_string(3000)); 144 CREATE TABLE t2(a); 145 INSERT INTO t2 VALUES(1); 146} {} 147if {![nonzero_reserved_bytes]} { 148 do_test filefmt-2.1.2 { 149 hexio_read test.db 28 4 150 } {00000009} 151} 152 153do_test filefmt-2.1.3 { 154 sql36231 { INSERT INTO t1 VALUES(a_string(3000)) } 155} {} 156 157do_execsql_test filefmt-2.1.4 { INSERT INTO t2 VALUES(2) } {} 158integrity_check filefmt-2.1.5 159do_test filefmt-2.1.6 { hexio_read test.db 28 4 } {00000010} 160 161db close 162forcedelete test.db 163sqlite3 db test.db 164db func a_string a_string 165 166do_execsql_test filefmt-2.2.1 { 167 PRAGMA page_size = 1024; 168 PRAGMA auto_vacuum = 0; 169 CREATE TABLE t1(a); 170 CREATE INDEX i1 ON t1(a); 171 INSERT INTO t1 VALUES(a_string(3000)); 172 CREATE TABLE t2(a); 173 INSERT INTO t2 VALUES(1); 174} {} 175if {![nonzero_reserved_bytes]} { 176 do_test filefmt-2.2.2 { 177 hexio_read test.db 28 4 178 } {00000009} 179} 180 181do_test filefmt-2.2.3 { 182 sql36231 { INSERT INTO t1 VALUES(a_string(3000)) } 183} {} 184 185do_execsql_test filefmt-2.2.4 { 186 PRAGMA integrity_check; 187 BEGIN; 188 INSERT INTO t2 VALUES(2); 189 SAVEPOINT a; 190 INSERT INTO t2 VALUES(3); 191 ROLLBACK TO a; 192} {ok} 193 194integrity_check filefmt-2.2.5 195do_execsql_test filefmt-2.2.6 { COMMIT } {} 196db close 197sqlite3 db test.db 198integrity_check filefmt-2.2.7 199 200#-------------------------------------------------------------------------- 201# Check that ticket 89b8c9ac54 is fixed. Before the fix, the SELECT 202# statement would return SQLITE_CORRUPT. The database file was not actually 203# corrupted, but SQLite was reporting that it was. 204# 205db close 206forcedelete test.db 207sqlite3 db test.db 208do_execsql_test filefmt-3.1 { 209 PRAGMA auto_vacuum = 1; 210 CREATE TABLE t1(a, b); 211} {} 212do_test filefmt-3.2 { 213 sql36231 { DROP TABLE t1 } 214} {} 215do_execsql_test filefmt-3.3 { 216 SELECT * FROM sqlite_master; 217 PRAGMA integrity_check; 218} {ok} 219 220reset_db 221do_execsql_test filefmt-4.1 { 222 PRAGMA auto_vacuum = 1; 223 CREATE TABLE t1(x, y); 224 CREATE TABLE t2(x, y); 225 226 INSERT INTO t1 VALUES(randomblob(100), randomblob(100)); 227 INSERT INTO t1 VALUES(randomblob(100), randomblob(100)); 228 INSERT INTO t1 VALUES(randomblob(100), randomblob(100)); 229 INSERT INTO t1 VALUES(randomblob(100), randomblob(100)); 230 INSERT INTO t1 VALUES(randomblob(100), randomblob(100)); 231 INSERT INTO t1 VALUES(randomblob(100), randomblob(100)); 232 233 INSERT INTO t2 SELECT randomblob(100), randomblob(100) FROM t1; 234 INSERT INTO t2 SELECT randomblob(100), randomblob(100) FROM t1; 235 INSERT INTO t2 SELECT randomblob(100), randomblob(100) FROM t1; 236 INSERT INTO t2 SELECT randomblob(100), randomblob(100) FROM t1; 237} 238 239do_test filefmt-4.2 { 240 sql36231 { INSERT INTO t2 SELECT * FROM t1 } 241} {} 242 243do_test filefmt-4.3 { 244 forcedelete bak.db 245 db backup bak.db 246} {} 247 248do_test filefmt-4.4 { 249 sqlite3 db2 bak.db 250 db2 eval { PRAGMA integrity_check } 251} {ok} 252db2 close 253 254finish_test 255