xref: /sqlite-3.40.0/test/dbpage.test (revision 6a51e70c) 
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
24sqlite3_db_config db DEFENSIVE 0
25do_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, x3
65   WHERE sqlite_dbpage.schema=x3.y AND sqlite_dbpage.pgno=x3.z
66   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 close
106sqlite3 db :memory:
107do_execsql_test 300 {
108  SELECT * FROM sqlite_temp_schema, sqlite_dbpage;
109} {}
110
111finish_test
112