diff options
Diffstat (limited to 'test/resetdb.test')
| -rw-r--r-- | test/resetdb.test | 300 |
1 files changed, 300 insertions, 0 deletions
diff --git a/test/resetdb.test b/test/resetdb.test new file mode 100644 index 0000000..f2d3251 --- /dev/null +++ b/test/resetdb.test @@ -0,0 +1,300 @@ +# 2018-04-28 +# +# 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. +# +#*********************************************************************** +# Test cases for SQLITE_DBCONFIG_RESET_DATABASE +# + +set testdir [file dirname $argv0] +source $testdir/tester.tcl +set testprefix resetdb + +do_not_use_codec + +ifcapable !vtab||!compound { + finish_test + return +} + +# In the "inmemory_journal" permutation, each new connection executes +# "PRAGMA journal_mode = memory". This fails with SQLITE_BUSY if attempted +# on a wal mode database with existing connections. For this and a few +# other reasons, this test is not run as part of "inmemory_journal". +# +# Permutation "journaltest" does not support wal mode. +# +if {[permutation]=="inmemory_journal" + || [permutation]=="journaltest" +} { + finish_test + return +} + +# Create a sample database +do_execsql_test 100 { + PRAGMA auto_vacuum = 0; + PRAGMA page_size=4096; + CREATE TABLE t1(a,b); + WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<20) + INSERT INTO t1(a,b) SELECT x, randomblob(300) FROM c; + CREATE INDEX t1a ON t1(a); + CREATE INDEX t1b ON t1(b); + SELECT sum(a), sum(length(b)) FROM t1; + PRAGMA integrity_check; + PRAGMA journal_mode; + PRAGMA page_count; +} {210 6000 ok delete 8} + +# Verify that the same content is seen from a separate database connection +sqlite3 db2 test.db +do_test 110 { + execsql { + SELECT sum(a), sum(length(b)) FROM t1; + PRAGMA integrity_check; + PRAGMA journal_mode; + PRAGMA page_count; + } db2 +} {210 6000 ok delete 8} + +do_test 200 { + # Thoroughly corrupt the database file by overwriting the first + # page with randomness. + sqlite3_db_config db DEFENSIVE 0 + catchsql { + UPDATE sqlite_dbpage SET data=randomblob(4096) WHERE pgno=1; + PRAGMA quick_check; + } +} {1 {file is not a database}} +do_test 201 { + catchsql { + PRAGMA quick_check; + } db2 +} {1 {file is not a database}} + +do_test 210 { + # Reset the database file using SQLITE_DBCONFIG_RESET_DATABASE + sqlite3_db_config db RESET_DB 1 + db eval VACUUM + sqlite3_db_config db RESET_DB 0 + + # If using sqlite3_prepare() instead of _v2() or _v3(), the block + # below raises an SQLITE_SCHEMA error. The following fixes this. + if {[permutation]=="prepare"} { catchsql "SELECT * FROM sqlite_master" db2 } + + # Verify that the reset took, even on the separate database connection + catchsql { + PRAGMA page_count; + PRAGMA page_size; + PRAGMA quick_check; + PRAGMA journal_mode; + } db2 +} {0 {1 4096 ok delete}} + +# Delete the old connections and database and start over again +# with a different page size and in WAL mode. +# +db close +db2 close +forcedelete test.db +sqlite3 db test.db +do_execsql_test 300 { + PRAGMA auto_vacuum = 0; + PRAGMA page_size=8192; + PRAGMA journal_mode=WAL; + CREATE TABLE t1(a,b); + WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<20) + INSERT INTO t1(a,b) SELECT x, randomblob(1300) FROM c; + CREATE INDEX t1a ON t1(a); + CREATE INDEX t1b ON t1(b); + SELECT sum(a), sum(length(b)) FROM t1; + PRAGMA integrity_check; + PRAGMA journal_mode; + PRAGMA page_size; + PRAGMA page_count; +} {wal 210 26000 ok wal 8192 12} +sqlite3 db2 test.db +do_test 310 { + execsql { + SELECT sum(a), sum(length(b)) FROM t1; + PRAGMA integrity_check; + PRAGMA journal_mode; + PRAGMA page_size; + PRAGMA page_count; + } db2 +} {210 26000 ok wal 8192 12} + +# Corrupt the database again +sqlite3_db_config db DEFENSIVE 0 +do_catchsql_test 320 { + UPDATE sqlite_dbpage SET data=randomblob(8192) WHERE pgno=1; + PRAGMA quick_check +} {1 {file is not a database}} + +do_test 330 { + catchsql { + PRAGMA quick_check + } db2 +} {1 {file is not a database}} + +db2 cache flush ;# Required by permutation "prepare". + +# Reset the database yet again. Verify that the page size and +# journal mode are preserved. +# +do_test 400 { + sqlite3_db_config db RESET_DB 1 + db eval VACUUM + sqlite3_db_config db RESET_DB 0 + catchsql { + PRAGMA page_count; + PRAGMA page_size; + PRAGMA journal_mode; + PRAGMA quick_check; + } db2 +} {0 {1 8192 wal ok}} +db2 close + +# Reset the database yet again. This time immediately after it is closed +# and reopened. So that the VACUUM is the first statement run. +# +db close +sqlite3 db test.db +do_test 500 { + sqlite3_finalize [ + sqlite3_prepare db "SELECT 1 FROM sqlite_master LIMIT 1" -1 tail + ] + sqlite3_db_config db RESET_DB 1 + db eval VACUUM + sqlite3_db_config db RESET_DB 0 + sqlite3 db2 test.db + catchsql { + PRAGMA page_count; + PRAGMA page_size; + PRAGMA journal_mode; + PRAGMA quick_check; + } db2 +} {0 {1 8192 wal ok}} +db2 close + +#------------------------------------------------------------------------- +reset_db +sqlite3 db2 test.db +do_execsql_test 600 { + PRAGMA journal_mode = wal; + CREATE TABLE t1(a); + INSERT INTO t1 VALUES(1), (2), (3), (4); +} {wal} + +do_execsql_test -db db2 610 { + SELECT * FROM t1 +} {1 2 3 4} + +do_test 620 { + set res [list] + db2 eval {SELECT a FROM t1} { + lappend res $a + if {$a==3} { + sqlite3_db_config db RESET_DB 1 + db eval VACUUM + sqlite3_db_config db RESET_DB 0 + } + } + + set res +} {1 2 3 4} + +do_execsql_test -db db2 630 { + SELECT * FROM sqlite_master +} {} + +#------------------------------------------------------------------------- +db2 close +reset_db + +do_execsql_test 700 { + PRAGMA page_size=512; + PRAGMA auto_vacuum = 0; + CREATE TABLE t1(a,b,c); + CREATE INDEX t1a ON t1(a); + CREATE INDEX t1bc ON t1(b,c); + WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<10) + INSERT INTO t1(a,b,c) SELECT x, randomblob(100),randomblob(100) FROM c; + PRAGMA page_count; + PRAGMA integrity_check; +} {19 ok} + +if {[nonzero_reserved_bytes]} { + finish_test + return +} + +sqlite3_db_config db DEFENSIVE 0 +do_execsql_test 710 { + UPDATE sqlite_dbpage SET data= + X'53514C69746520666F726D61742033000200030100402020000000000000001300000000000000000000000300000004000000000000000000000001000000000000000000000000000000000000000000000000000000000000000000000000000000000D00000003017C0001D801AC017C00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002E03061715110145696E6465787431626374310443524541544520494E4445582074316263204F4E20743128622C63292A0206171311013F696E64657874316174310343524541544520494E44455820743161204F4E20743128612926010617111101397461626C657431743102435245415445205441424C4520743128612C622C6329' WHERE pgno=1; +} + +do_execsql_test 720 { + PRAGMA integrity_check; +} {ok} + +do_test 730 { + sqlite3_db_config db RESET_DB 1 + db eval VACUUM + sqlite3_db_config db RESET_DB 0 +} {0} + +do_execsql_test 740 { + PRAGMA page_count; + PRAGMA integrity_check; +} {1 ok} + +#------------------------------------------------------------------------- +ifcapable utf16 { + reset_db + do_execsql_test 800 { + PRAGMA encoding = 'utf8'; + CREATE TABLE t1(a, b); + PRAGMA encoding; + } {UTF-8} + + db close + sqlite3 db test.db + + sqlite3 db2 test.db + do_execsql_test -db db2 810 { + CREATE TEMP TABLE t2(x); + INSERT INTO t2 VALUES('hello world'); + SELECT name FROM sqlite_schema; + } {t1} + do_test 820 { + db eval "PRAGMA encoding = 'utf16'" + sqlite3_db_config db RESET_DB 1 + } {1} + do_test 830 { + db eval VACUUM + sqlite3_db_config db RESET_DB 0 + } {0} + do_test 840 { + string range [db eval { + CREATE TABLE t1(a, b); + INSERT INTO t1 VALUES('one', 'two'); + PRAGMA encoding; + }] 0 5 + } {UTF-16} + + do_test 850 { + catchsql { SELECT * FROM t1; } db2 + } {1 {attached databases must use the same text encoding as main database}} + do_test 860 { + catchsql { SELECT * FROM t2; } db2 + } {1 {attached databases must use the same text encoding as main database}} +} + +finish_test |