summaryrefslogtreecommitdiffstats
path: root/www/dbpage.html
blob: fb9625a463287b846ee6c72e3cb4669048c153b5 (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
<!DOCTYPE html>
<html><head>
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
<link href="sqlite.css" rel="stylesheet">
<title>The SQLITE_DBPAGE Virtual Table</title>
<!-- path= -->
</head>
<body>
<div class=nosearch>
<a href="index.html">
<img class="logo" src="images/sqlite370_banner.gif" alt="SQLite" border="0">
</a>
<div><!-- IE hack to prevent disappearing logo --></div>
<div class="tagline desktoponly">
Small. Fast. Reliable.<br>Choose any three.
</div>
<div class="menu mainmenu">
<ul>
<li><a href="index.html">Home</a>
<li class='mobileonly'><a href="javascript:void(0)" onclick='toggle_div("submenu")'>Menu</a>
<li class='wideonly'><a href='about.html'>About</a>
<li class='desktoponly'><a href="docs.html">Documentation</a>
<li class='desktoponly'><a href="download.html">Download</a>
<li class='wideonly'><a href='copyright.html'>License</a>
<li class='desktoponly'><a href="support.html">Support</a>
<li class='desktoponly'><a href="prosupport.html">Purchase</a>
<li class='search' id='search_menubutton'>
<a href="javascript:void(0)" onclick='toggle_search()'>Search</a>
</ul>
</div>
<div class="menu submenu" id="submenu">
<ul>
<li><a href='about.html'>About</a>
<li><a href='docs.html'>Documentation</a>
<li><a href='download.html'>Download</a>
<li><a href='support.html'>Support</a>
<li><a href='prosupport.html'>Purchase</a>
</ul>
</div>
<div class="searchmenu" id="searchmenu">
<form method="GET" action="search">
<select name="s" id="searchtype">
<option value="d">Search Documentation</option>
<option value="c">Search Changelog</option>
</select>
<input type="text" name="q" id="searchbox" value="">
<input type="submit" value="Go">
</form>
</div>
</div>
<script>
function toggle_div(nm) {
var w = document.getElementById(nm);
if( w.style.display=="block" ){
w.style.display = "none";
}else{
w.style.display = "block";
}
}
function toggle_search() {
var w = document.getElementById("searchmenu");
if( w.style.display=="block" ){
w.style.display = "none";
} else {
w.style.display = "block";
setTimeout(function(){
document.getElementById("searchbox").focus()
}, 30);
}
}
function div_off(nm){document.getElementById(nm).style.display="none";}
window.onbeforeunload = function(e){div_off("submenu");}
/* Disable the Search feature if we are not operating from CGI, since */
/* Search is accomplished using CGI and will not work without it. */
if( !location.origin || !location.origin.match || !location.origin.match(/http/) ){
document.getElementById("search_menubutton").style.display = "none";
}
/* Used by the Hide/Show button beside syntax diagrams, to toggle the */
function hideorshow(btn,obj){
var x = document.getElementById(obj);
var b = document.getElementById(btn);
if( x.style.display!='none' ){
x.style.display = 'none';
b.innerHTML='show';
}else{
x.style.display = '';
b.innerHTML='hide';
}
return false;
}
var antiRobot = 0;
function antiRobotGo(){
if( antiRobot!=3 ) return;
antiRobot = 7;
var j = document.getElementById("mtimelink");
if(j && j.hasAttribute("data-href")) j.href=j.getAttribute("data-href");
}
function antiRobotDefense(){
document.body.onmousedown=function(){
antiRobot |= 2;
antiRobotGo();
document.body.onmousedown=null;
}
document.body.onmousemove=function(){
antiRobot |= 2;
antiRobotGo();
document.body.onmousemove=null;
}
setTimeout(function(){
antiRobot |= 1;
antiRobotGo();
}, 100)
antiRobotGo();
}
antiRobotDefense();
</script>
<div class=fancy>
<div class=nosearch>
<div class="fancy_title">
The SQLITE_DBPAGE Virtual Table
</div>
</div>




<h1 id="overview"><span>1. </span>Overview</h1>

<p>
The SQLITE_DBPAGE extension implements an <a href="vtab.html#epoonlyvtab">eponymous-only virtual table</a> that
provides direct access to the underlying database file by interacting
with the pager.  SQLITE_DBPAGE is capable of both reading and writing any
page of the database.  Because interaction is through the pager layer, all
changes are transactional.
</p>

<p>
<b>Warning:</b> writing to the SQLITE_DBPAGE virtual table can very easily
cause unrecoverably database corruption.  Do not allow untrusted components
to access the SQLITE_DBPAGE table.  Use appropriate care while using the
SQLITE_DBPAGE table.  Back up important data prior to experimenting with the
SQLITE_DBPAGE table.  Writes to the SQLITE_DBPAGE virtual table are
disabled when the <a href="c3ref/c_dbconfig_defensive.html#sqlitedbconfigdefensive">SQLITE_DBCONFIG_DEFENSIVE</a> flag is set.

</p><p>
The SQLITE_DBPAGE extension is included in the <a href="amalgamation.html">amalgamation</a> though 
it is disabled
by default.  Use the <a href="compile.html#enable_dbpage_vtab">SQLITE_ENABLE_DBPAGE_VTAB</a> compile-time option to enable
the SQLITE_DBPAGE extension.  The SQLITE_DBPAGE extension makes use of
unpublished internal interfaces and is not run-time loadable.  The only way
to add SQLITE_DBPAGE to an application is to compile it in using the
<a href="compile.html#enable_dbpage_vtab">SQLITE_ENABLE_DBPAGE_VTAB</a> compile-time option.
</p>

<p>
The SQLITE_DBPAGE extension is enabled in default builds
of the <a href="cli.html">command-line shell</a>.

</p><h1 id="usage"><span>2. </span>Usage</h1>

<p>
The SQLITE_DBPAGE virtual table read/write table that provides direct
access to the underlying disk file on a page-by-page basis.  The
virtual table appears to have a schema like this:

</p><div class="codeblock"><pre>CREATE TABLE sqlite_dbpage(
  pgno INTEGER PRIMARY KEY,
  data BLOB
);
</pre></div>

<p>
An SQLite database file is divided into pages.
The first page is 1, the second page is 2, and so forth.
There is no page 0.
Every page is the same size.
The size of every page is a power of 2 between 512 and 65536.
See the <a href="fileformat2.html">file format</a> documentation for further details.

</p><p>
The SQLITE_DBPAGE table allows an application to view or replace the
raw binary content of each page of the database file.
No attempt is made to interpret the content of the page.
Content is returned byte-for-byte as it appears on disk.

</p><p>
The SQLITE_DBPAGE table has one row for each page in the database file.
SQLITE_DBPAGE allows pages to be read or to be overwritten.
However the size of the database file cannot be changed.  It is not
possible to change the number of rows in the SQLITE_DBPAGE table by
running DELETE or INSERT operations against that table.

</p><h2 id="using_sqlite_dbpage_on_attach_ed_databases"><span>2.1. </span>Using SQLITE_DBPAGE On ATTACH-ed Databases</h2>

<p>
The SQLITE_DBPAGE table schema shown above is incomplete.  There is
a third <a href="vtab.html#hiddencol">hidden column</a> named "schema" that determines which
<a href="lang_attach.html">ATTACH-ed database</a> should be read or written.  Because
the "schema" column is hidden, it can be used as a parameter when
SQLITE_DBPAGE is invoked as a <a href="vtab.html#tabfunc2">table-valued function</a>.

</p><p>
For example, suppose an additional database is attached to the 
database connection using a statement like this:

</p><div class="codeblock"><pre>ATTACH 'auxdata1.db' AS aux1;
</pre></div>

<p>
Then to read the first page of that database file, one merely runs:

</p><div class="codeblock"><pre>SELECT data FROM sqlite_dbpage('aux1') WHERE pgno=1;
</pre></div>

<p>
If the "schema" is omitted, it defaults to the primary database
(usually called 'main', unless renamed using <a href="c3ref/c_dbconfig_defensive.html#sqlitedbconfigmaindbname">SQLITE_DBCONFIG_MAINDBNAME</a>).
Hence, the following two queries are normally equivalent:

</p><div class="codeblock"><pre>SELECT data FROM sqlite_dbpage('main') WHERE pgno=1;
SELECT data FROM sqlite_dbpage WHERE pgno=1;
</pre></div>

<p>
The SQLITE_DBPAGE table can participate in a join just like any other
table.  Hence, to see the content of the first page to all connected
database files, one might run a statement like this:

</p><div class="codeblock"><pre>SELECT dbpage.data, dblist.name
  FROM pragma_database_list AS dblist
  JOIN sqlite_dbpage(dblist.name) AS dbpage
 WHERE dbpage.pgno=1;
</pre></div>
<p align="center"><small><i>This page last modified on  <a href="https://sqlite.org/docsrc/honeypot" id="mtimelink"  data-href="https://sqlite.org/docsrc/finfo/pages/dbpage.in?m=21798cdb09def756b">2018-11-12 15:24:40</a> UTC </small></i></p>