AryaWu/sqlite

1# 2018-04-282#3# The author disclaims copyright to this source code.  In place of4# 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# Test cases for SQLITE_DBCONFIG_RESET_DATABASE12#13 14set testdir [file dirname $argv0]15source $testdir/tester.tcl16set testprefix resetdb17 18do_not_use_codec19 20ifcapable !vtab||!compound {21  finish_test22  return23}24 25# In the "inmemory_journal" permutation, each new connection executes 26# "PRAGMA journal_mode = memory". This fails with SQLITE_BUSY if attempted27# on a wal mode database with existing connections. For this and a few28# other reasons, this test is not run as part of "inmemory_journal".29#30# Permutation "journaltest" does not support wal mode.31#32if {[permutation]=="inmemory_journal"33 || [permutation]=="journaltest"34} {35  finish_test36  return37}38 39# Create a sample database40do_execsql_test 100 {41  PRAGMA auto_vacuum = 0;42  PRAGMA page_size=4096;43  CREATE TABLE t1(a,b);44  WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<20)45    INSERT INTO t1(a,b) SELECT x, randomblob(300) FROM c;46  CREATE INDEX t1a ON t1(a);47  CREATE INDEX t1b ON t1(b);48  SELECT sum(a), sum(length(b)) FROM t1;49  PRAGMA integrity_check;50  PRAGMA journal_mode;51  PRAGMA page_count;52} {210 6000 ok delete 8}53 54# Verify that the same content is seen from a separate database connection55sqlite3 db2 test.db56do_test 110 {57  execsql {58    SELECT sum(a), sum(length(b)) FROM t1;59    PRAGMA integrity_check;60    PRAGMA journal_mode;61    PRAGMA page_count;62  } db263} {210 6000 ok delete 8}64 65do_test 200 {66  # Thoroughly corrupt the database file by overwriting the first67  # page with randomness.68  sqlite3_db_config db DEFENSIVE 069  catchsql {70    UPDATE sqlite_dbpage SET data=randomblob(4096) WHERE pgno=1;71    PRAGMA quick_check;72  }73} {1 {file is not a database}}74do_test 201 {75  catchsql {76    PRAGMA quick_check;77  } db278} {1 {file is not a database}}79 80do_test 210 {81  # Reset the database file using SQLITE_DBCONFIG_RESET_DATABASE82  sqlite3_db_config db RESET_DB 183  db eval VACUUM84  sqlite3_db_config db RESET_DB 085 86  # If using sqlite3_prepare() instead of _v2() or _v3(), the block 87  # below raises an SQLITE_SCHEMA error. The following fixes this.88  if {[permutation]=="prepare"} { catchsql "SELECT * FROM sqlite_master" db2 }89 90  # Verify that the reset took, even on the separate database connection91  catchsql {92     PRAGMA page_count;93     PRAGMA page_size;94     PRAGMA quick_check;95     PRAGMA journal_mode;96  } db297} {0 {1 4096 ok delete}}98 99# Delete the old connections and database and start over again100# with a different page size and in WAL mode.101#102db close103db2 close104forcedelete test.db105sqlite3 db test.db106do_execsql_test 300 {107  PRAGMA auto_vacuum = 0;108  PRAGMA page_size=8192;109  PRAGMA journal_mode=WAL;110  CREATE TABLE t1(a,b);111  WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<20)112    INSERT INTO t1(a,b) SELECT x, randomblob(1300) FROM c;113  CREATE INDEX t1a ON t1(a);114  CREATE INDEX t1b ON t1(b);115  SELECT sum(a), sum(length(b)) FROM t1;116  PRAGMA integrity_check;117  PRAGMA journal_mode;118  PRAGMA page_size;119  PRAGMA page_count;120} {wal 210 26000 ok wal 8192 12}121sqlite3 db2 test.db122do_test 310 {123  execsql {124    SELECT sum(a), sum(length(b)) FROM t1;125    PRAGMA integrity_check;126    PRAGMA journal_mode;127    PRAGMA page_size;128    PRAGMA page_count;129  } db2130} {210 26000 ok wal 8192 12}131 132# Corrupt the database again133sqlite3_db_config db DEFENSIVE 0134do_catchsql_test 320 {135  UPDATE sqlite_dbpage SET data=randomblob(8192) WHERE pgno=1;136  PRAGMA quick_check137} {1 {file is not a database}}138 139do_test 330 {140  catchsql {141    PRAGMA quick_check142  } db2143} {1 {file is not a database}}144 145db2 cache flush         ;# Required by permutation "prepare".146 147# Reset the database yet again.  Verify that the page size and148# journal mode are preserved.149#150do_test 400 {151  sqlite3_db_config db RESET_DB 1152  db eval VACUUM153  sqlite3_db_config db RESET_DB 0154  catchsql {155     PRAGMA page_count;156     PRAGMA page_size;157     PRAGMA journal_mode;158     PRAGMA quick_check;159  } db2160} {0 {1 8192 wal ok}}161db2 close162 163# Reset the database yet again. This time immediately after it is closed164# and reopened. So that the VACUUM is the first statement run.165#166db close167sqlite3 db test.db168do_test 500 {169  sqlite3_finalize [170    sqlite3_prepare db "SELECT 1 FROM sqlite_master LIMIT 1" -1 tail171  ]172  sqlite3_db_config db RESET_DB 1173  db eval VACUUM174  sqlite3_db_config db RESET_DB 0175  sqlite3 db2 test.db176  catchsql {177     PRAGMA page_count;178     PRAGMA page_size;179     PRAGMA journal_mode;180     PRAGMA quick_check;181  } db2182} {0 {1 8192 wal ok}}183db2 close184 185#-------------------------------------------------------------------------186reset_db187sqlite3 db2 test.db188do_execsql_test 600 {189  PRAGMA journal_mode = wal;190  CREATE TABLE t1(a);191  INSERT INTO t1 VALUES(1), (2), (3), (4);192} {wal}193 194do_execsql_test -db db2 610 {195  SELECT * FROM t1196} {1 2 3 4}197 198do_test 620 {199  set res [list]200  db2 eval {SELECT a FROM t1} {201    lappend res $a202    if {$a==3} {203      sqlite3_db_config db RESET_DB 1204      db eval VACUUM205      sqlite3_db_config db RESET_DB 0206    }207  }208 209  set res210} {1 2 3 4}211 212do_execsql_test -db db2 630 {213  SELECT * FROM sqlite_master214} {}215 216#-------------------------------------------------------------------------217db2 close218reset_db219 220do_execsql_test 700 {221  PRAGMA page_size=512;222  PRAGMA auto_vacuum = 0;223  CREATE TABLE t1(a,b,c);224  CREATE INDEX t1a ON t1(a);225  CREATE INDEX t1bc ON t1(b,c);226  WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<10)227    INSERT INTO t1(a,b,c) SELECT x, randomblob(100),randomblob(100) FROM c;228  PRAGMA page_count;229  PRAGMA integrity_check;230} {19 ok}231 232if {[nonzero_reserved_bytes]} {233  finish_test234  return235}236 237sqlite3_db_config db DEFENSIVE 0238do_execsql_test 710 {239  UPDATE sqlite_dbpage SET data=240    X'53514C69746520666F726D61742033000200030100402020000000000000001300000000000000000000000300000004000000000000000000000001000000000000000000000000000000000000000000000000000000000000000000000000000000000D00000003017C0001D801AC017C00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002E03061715110145696E6465787431626374310443524541544520494E4445582074316263204F4E20743128622C63292A0206171311013F696E64657874316174310343524541544520494E44455820743161204F4E20743128612926010617111101397461626C657431743102435245415445205441424C4520743128612C622C6329' WHERE pgno=1;241}242 243do_execsql_test 720 {244  PRAGMA integrity_check;245} {ok}246 247do_test 730 {248  sqlite3_db_config db RESET_DB 1249  db eval VACUUM250  sqlite3_db_config db RESET_DB 0251} {0}252 253do_execsql_test 740 {254  PRAGMA page_count;255  PRAGMA integrity_check;256} {1 ok}257 258#-------------------------------------------------------------------------259ifcapable utf16 {260  reset_db261  do_execsql_test 800 {262    PRAGMA encoding = 'utf8';263    CREATE TABLE t1(a, b);264    PRAGMA encoding;265  } {UTF-8}266  267  db close268  sqlite3 db test.db269 270  sqlite3 db2 test.db271  do_execsql_test -db db2 810 {272    CREATE TEMP TABLE t2(x);273    INSERT INTO t2 VALUES('hello world');274    SELECT name FROM sqlite_schema;275  } {t1}276  do_test 820 {277    db eval "PRAGMA encoding = 'utf16'"278    sqlite3_db_config db RESET_DB 1279  } {1}280  do_test 830 {281    db eval VACUUM282    sqlite3_db_config db RESET_DB 0283  } {0}284  do_test 840 {285    string range [db eval {286      CREATE TABLE t1(a, b);287      INSERT INTO t1 VALUES('one', 'two');288      PRAGMA encoding;289    }] 0 5290  } {UTF-16}291 292  do_test 850 {293    catchsql { SELECT * FROM t1; } db2 294  } {1 {attached databases must use the same text encoding as main database}}295  do_test 860 {296    catchsql { SELECT * FROM t2; } db2 297  } {1 {attached databases must use the same text encoding as main database}}298}299 300finish_test301