summaryrefslogtreecommitdiffstats
path: root/test/resetdb.test
diff options
context:
space:
mode:
Diffstat (limited to 'test/resetdb.test')
-rw-r--r--test/resetdb.test300
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