xref: /sqlite-3.40.0/test/dbpage.test (revision 4e8ad3bc) 
1# 2017-10-11
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.  The
12# focus of this file is testing the sqlite_dbpage virtual table.
13#
14
15set testdir [file dirname $argv0]
16source $testdir/tester.tcl
17set testprefix dbpage
18
19ifcapable !vtab||!compound {
20  finish_test
21  return
22}
23
24do_test 100 {
25  execsql {
26    PRAGMA auto_vacuum=0;
27    PRAGMA page_size=4096;
28    PRAGMA journal_mode=WAL;
29  }
30  execsql {
31    CREATE TABLE t1(a,b);
32    WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<100)
33      INSERT INTO t1(a,b) SELECT x, printf('%d-x%.*c',x,x,'x') FROM c;
34    PRAGMA integrity_check;
35  }
36} {ok}
37do_execsql_test 110 {
38  SELECT pgno, quote(substr(data,1,5)) FROM sqlite_dbpage('main') ORDER BY pgno;
39} {1 X'53514C6974' 2 X'0500000001' 3 X'0D0000004E' 4 X'0D00000016'}
40do_execsql_test 120 {
41  SELECT pgno, quote(substr(data,1,5)) FROM sqlite_dbpage WHERE pgno=2;
42} {2 X'0500000001'}
43do_execsql_test 130 {
44  SELECT pgno, quote(substr(data,1,5)) FROM sqlite_dbpage WHERE pgno=4;
45} {4 X'0D00000016'}
46do_execsql_test 140 {
47  SELECT pgno, quote(substr(data,1,5)) FROM sqlite_dbpage WHERE pgno=5;
48} {}
49do_execsql_test 150 {
50  SELECT pgno, quote(substr(data,1,5)) FROM sqlite_dbpage WHERE pgno=0;
51} {}
52do_execsql_test 160 {
53  ATTACH ':memory:' AS aux1;
54  PRAGMA aux1.page_size=4096;
55  CREATE TABLE aux1.t2(a,b,c);
56  INSERT INTO t2 VALUES(11,12,13);
57  SELECT pgno, quote(substr(data,1,5)) FROM sqlite_dbpage('aux1');
58} {1 X'53514C6974' 2 X'0D00000001'}
59do_execsql_test 170 {
60  CREATE TABLE aux1.x3(x,y,z);
61  INSERT INTO x3(x,y,z) VALUES(1,'main',1),(2,'aux1',1);
62  SELECT pgno, schema, substr(data,1,6)
63    FROM sqlite_dbpage, x3
64   WHERE sqlite_dbpage.schema=x3.y AND sqlite_dbpage.pgno=x3.z
65   ORDER BY x3.x;
66} {1 main SQLite 1 aux1 SQLite}
67
68do_execsql_test 200 {
69  CREATE TEMP TABLE saved_content(x);
70  INSERT INTO saved_content(x) SELECT data FROM sqlite_dbpage WHERE pgno=4;
71  UPDATE sqlite_dbpage SET data=zeroblob(4096) WHERE pgno=4;
72} {}
73do_catchsql_test 210 {
74  PRAGMA integrity_check;
75} {1 {database disk image is malformed}}
76do_execsql_test 220 {
77  SELECT pgno, quote(substr(data,1,5)) FROM sqlite_dbpage('main') ORDER BY pgno;
78} {1 X'53514C6974' 2 X'0500000001' 3 X'0D0000004E' 4 X'0000000000'}
79do_execsql_test 230 {
80  UPDATE sqlite_dbpage SET data=(SELECT x FROM saved_content) WHERE pgno=4;
81} {}
82do_catchsql_test 230 {
83  PRAGMA integrity_check;
84} {0 ok}
85do_execsql_test 240 {
86  DELETE FROM saved_content;
87  INSERT INTO saved_content(x)
88     SELECT data FROM sqlite_dbpage WHERE schema='aux1' AND pgno=2;
89} {}
90do_execsql_test 241 {
91  UPDATE sqlite_dbpage SET data=zeroblob(4096) WHERE pgno=2 AND schema='aux1';
92} {}
93do_catchsql_test 250 {
94  PRAGMA aux1.integrity_check;
95} {1 {database disk image is malformed}}
96do_execsql_test 260 {
97  UPDATE sqlite_dbpage SET data=(SELECT x FROM saved_content)
98   WHERE pgno=2 AND schema='aux1';
99} {}
100do_catchsql_test 270 {
101  PRAGMA aux1.integrity_check;
102} {0 ok}
103
104finish_test
105