AryaWu/sqlite

1# 2017-10-112#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# This file implements regression tests for SQLite library.  The12# focus of this file is testing the sqlite_dbpage virtual table.13#14 15set testdir [file dirname $argv0]16source $testdir/tester.tcl17set testprefix dbpage18 19ifcapable !vtab||!compound {20  finish_test21  return22}23 24sqlite3_db_config db DEFENSIVE 025do_test 100 {26  execsql {27    PRAGMA auto_vacuum=0;28    PRAGMA page_size=4096;29    PRAGMA journal_mode=WAL;30  }31  execsql { 32    CREATE TABLE t1(a,b);33    WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<100)34      INSERT INTO t1(a,b) SELECT x, printf('%d-x%.*c',x,x,'x') FROM c;35    PRAGMA integrity_check;36  }37} {ok}38do_execsql_test 110 {39  SELECT pgno, quote(substr(data,1,5)) FROM sqlite_dbpage('main') ORDER BY pgno;40} {1 X'53514C6974' 2 X'0500000001' 3 X'0D0000004E' 4 X'0D00000016'}41do_execsql_test 120 {42  SELECT pgno, quote(substr(data,1,5)) FROM sqlite_dbpage WHERE pgno=2;43} {2 X'0500000001'}44do_execsql_test 130 {45  SELECT pgno, quote(substr(data,1,5)) FROM sqlite_dbpage WHERE pgno=4;46} {4 X'0D00000016'}47do_execsql_test 140 {48  SELECT pgno, quote(substr(data,1,5)) FROM sqlite_dbpage WHERE pgno=5;49} {}50do_execsql_test 150 {51  SELECT pgno, quote(substr(data,1,5)) FROM sqlite_dbpage WHERE pgno=0;52} {}53do_execsql_test 160 {54  ATTACH ':memory:' AS aux1;55  PRAGMA aux1.page_size=4096;56  CREATE TABLE aux1.t2(a,b,c);57  INSERT INTO t2 VALUES(11,12,13);58  SELECT pgno, quote(substr(data,1,5)) FROM sqlite_dbpage('aux1');59} {1 X'53514C6974' 2 X'0D00000001'}60do_execsql_test 170 {61  CREATE TABLE aux1.x3(x,y,z);62  INSERT INTO x3(x,y,z) VALUES(1,'main',1),(2,'aux1',1);63  SELECT pgno, schema, substr(data,1,6)64    FROM sqlite_dbpage, x365   WHERE sqlite_dbpage.schema=x3.y AND sqlite_dbpage.pgno=x3.z66   ORDER BY x3.x;67} {1 main SQLite 1 aux1 SQLite}68 69do_execsql_test 200 {70  CREATE TEMP TABLE saved_content(x);71  INSERT INTO saved_content(x) SELECT data FROM sqlite_dbpage WHERE pgno=4;72  UPDATE sqlite_dbpage SET data=zeroblob(4096) WHERE pgno=4;73} {}74do_catchsql_test 210 {75  PRAGMA integrity_check;76} {1 {database disk image is malformed}}77do_execsql_test 220 {78  SELECT pgno, quote(substr(data,1,5)) FROM sqlite_dbpage('main') ORDER BY pgno;79} {1 X'53514C6974' 2 X'0500000001' 3 X'0D0000004E' 4 X'0000000000'}80do_execsql_test 230 {81  UPDATE sqlite_dbpage SET data=(SELECT x FROM saved_content) WHERE pgno=4;82} {}83do_catchsql_test 230 {84  PRAGMA integrity_check;85} {0 ok}86do_execsql_test 240 {87  DELETE FROM saved_content;88  INSERT INTO saved_content(x) 89     SELECT data FROM sqlite_dbpage WHERE schema='aux1' AND pgno=2;90} {}91do_execsql_test 241 {92  UPDATE sqlite_dbpage SET data=zeroblob(4096) WHERE pgno=2 AND schema='aux1';93} {}94do_catchsql_test 250 {95  PRAGMA aux1.integrity_check;96} {1 {database disk image is malformed}}97do_execsql_test 260 {98  UPDATE sqlite_dbpage SET data=(SELECT x FROM saved_content)99   WHERE pgno=2 AND schema='aux1';100} {}101do_catchsql_test 270 {102  PRAGMA aux1.integrity_check;103} {0 ok}104 105db close106sqlite3 db :memory:107do_execsql_test 300 {108  SELECT * FROM sqlite_temp_schema, sqlite_dbpage;109} {}110 111#-------------------------------------------------------------------------112reset_db113do_execsql_test 400 {114  ATTACH ':memory:' AS aux1;115  BEGIN;116    CREATE VIRTUAL TABLE aux1.t1 USING sqlite_dbpage;117    INSERT INTO t1 VALUES(17, NULL);118  COMMIT;119}120 121#-------------------------------------------------------------------------122reset_db123forcedelete test.db2124sqlite3 db2 test.db2125db2 eval {126  PRAGMA auto_vacuum=NONE;127  CREATE TABLE t1(x, y);128}129 130do_execsql_test 500 {131  PRAGMA auto_vacuum=NONE;132  CREATE TABLE x1(a);133  INSERT INTO x1 VALUES( hex(randomblob(2000)) );134  INSERT INTO x1 VALUES( hex(randomblob(2000)) );135  INSERT INTO x1 VALUES( hex(randomblob(2000)) );136  INSERT INTO x1 VALUES( hex(randomblob(2000)) );137  PRAGMA page_count;138} {18}139 140do_test 510 {141  db eval  BEGIN 142  db2 eval { PRAGMA page_count } {143    db eval {144      INSERT INTO sqlite_dbpage values($page_count, NULL);145    }146  }147  db2 eval { SELECT pgno, data FROM sqlite_dbpage } {148    db eval {149      INSERT INTO sqlite_dbpage values($pgno, $data);150    }151  }152 153  db eval COMMIT154} {}155 156db close157sqlite3 db test.db158 159do_execsql_test 520 {160  PRAGMA page_count;161  SELECT * FROM t1;162} {2}163 164db2 close165 166#-------------------------------------------------------------------------167reset_db168forcedelete test.db2169do_execsql_test 610 {170  ATTACH 'test.db2' AS aux;171  CREATE TABLE t1(x);172  CREATE TABLE t2(y);173  INSERT INTO t1 VALUES(1234);174  CREATE TABLE aux.x1(z);175}176 177set pgno [db one {SELECT max(rootpage) FROM sqlite_schema}]178sqlite3 db2 test.db2179db2 eval {180  BEGIN;181    SELECT * FROM x1;182}183 184do_catchsql_test 620 {185  UPDATE sqlite_dbpage SET data = (186    SELECT data FROM sqlite_dbpage WHERE pgno=$pgno-1187  ) WHERE pgno = $pgno;188} {1 {database is locked}}189 190db2 eval {191  COMMIT;192}193 194do_catchsql_test 630 {195  UPDATE sqlite_dbpage SET data = (196    SELECT data FROM sqlite_dbpage WHERE pgno=$pgno-1197  ) WHERE pgno = $pgno;198} {0 {}}199 200db close201sqlite3 db test.db202 203do_execsql_test 640 {204  SELECT * FROM t2;205} {1234}206 207db2 close208 209#-------------------------------------------------------------------------210reset_db211do_execsql_test 700 {212  CREATE TABLE t1(x);213  INSERT INTO t1 VALUES( hex(randomblob(1000)) );214  INSERT INTO t1 VALUES( hex(randomblob(1000)) );215  INSERT INTO t1 VALUES( hex(randomblob(1000)) );216}217 218forcedelete test.db2219sqlite3 db2 test.db2220db2 eval {221  CREATE TABLE y1(y);222  INSERT INTO y1 VALUES( hex(randomblob(1000)) );223}224 225set max [db2 one {PRAGMA page_count}]226 227do_test 710 {228  execsql {229    BEGIN;230  }231 232  for {set ii 1} {$ii <= $max} {incr ii} {233    set data [db2 one {SELECT data FROM sqlite_dbpage WHERE pgno=$ii}]234    execsql {235      UPDATE sqlite_dbpage SET data=$data WHERE pgno=$ii236    }237  }238 239  execsql {240      SAVEPOINT abc;241        INSERT INTO sqlite_dbpage VALUES(2, NULL);242      ROLLBACK TO abc;243    COMMIT;244  }245} {}246 247db close248sqlite3 db test.db249 250do_execsql_test 720 {251  PRAGMA integrity_check252} {ok}253 254 255finish_test256