Debian Codegraph @

www/dbpage.html

raw
1 <!DOCTYPE html>
2 <html><head>
3 <meta name="viewport" content="width=device-width, initial-scale=1.0">
4 <meta http-equiv="content-type" content="text/html; charset=UTF-8">
5 <link href="sqlite.css" rel="stylesheet">
6 <title>The SQLITE_DBPAGE Virtual Table</title>
7 <!-- path= -->
8 </head>
9 <body>
10 <div class=nosearch>
11 <a href="index.html">
12 <img class="logo" src="images/sqlite370_banner.svg" alt="SQLite" border="0">
13 </a>
14 <div><!-- IE hack to prevent disappearing logo --></div>
15 <div class="tagline desktoponly">
16 Small. Fast. Reliable.<br>Choose any three.
17 </div>
18 <div class="menu mainmenu">
19 <ul>
20 <li><a href="index.html">Home</a>
21 <li class='mobileonly'><a href="javascript:void(0)" onclick='toggle_div("submenu")'>Menu</a>
22 <li class='wideonly'><a href='about.html'>About</a>
23 <li class='desktoponly'><a href="docs.html">Documentation</a>
24 <li class='desktoponly'><a href="download.html">Download</a>
25 <li class='wideonly'><a href='copyright.html'>License</a>
26 <li class='desktoponly'><a href="support.html">Support</a>
27 <li class='desktoponly'><a href="prosupport.html">Purchase</a>
28 <li class='search' id='search_menubutton'>
29 <a href="javascript:void(0)" onclick='toggle_search()'>Search</a>
30 </ul>
31 </div>
32 <div class="menu submenu" id="submenu">
33 <ul>
34 <li><a href='about.html'>About</a>
35 <li><a href='docs.html'>Documentation</a>
36 <li><a href='download.html'>Download</a>
37 <li><a href='support.html'>Support</a>
38 <li><a href='prosupport.html'>Purchase</a>
39 </ul>
40 </div>
41 <div class="searchmenu" id="searchmenu">
42 <form method="GET" action="search">
43 <select name="s" id="searchtype">
44 <option value="d">Search Documentation</option>
45 <option value="c">Search Changelog</option>
46 </select>
47 <input type="text" name="q" id="searchbox" value="">
48 <input type="submit" value="Go">
49 </form>
50 </div>
51 </div>
52 <script>
53 function toggle_div(nm) {
54 var w = document.getElementById(nm);
55 if( w.style.display=="block" ){
56 w.style.display = "none";
57 }else{
58 w.style.display = "block";
59 }
60 }
61 function toggle_search() {
62 var w = document.getElementById("searchmenu");
63 if( w.style.display=="block" ){
64 w.style.display = "none";
65 } else {
66 w.style.display = "block";
67 setTimeout(function(){
68 document.getElementById("searchbox").focus()
69 }, 30);
70 }
71 }
72 function div_off(nm){document.getElementById(nm).style.display="none";}
73 window.onbeforeunload = function(e){div_off("submenu");}
74 /* Disable the Search feature if we are not operating from CGI, since */
75 /* Search is accomplished using CGI and will not work without it. */
76 if( !location.origin || !location.origin.match || !location.origin.match(/http/) ){
77 document.getElementById("search_menubutton").style.display = "none";
78 }
79 /* Used by the Hide/Show button beside syntax diagrams, to toggle the */
80 function hideorshow(btn,obj){
81 var x = document.getElementById(obj);
82 var b = document.getElementById(btn);
83 if( x.style.display!='none' ){
84 x.style.display = 'none';
85 b.innerHTML='show';
86 }else{
87 x.style.display = '';
88 b.innerHTML='hide';
89 }
90 return false;
91 }
92 var antiRobot = 0;
93 function antiRobotGo(){
94 if( antiRobot!=3 ) return;
95 antiRobot = 7;
96 var j = document.getElementById("mtimelink");
97 if(j && j.hasAttribute("data-href")) j.href=j.getAttribute("data-href");
98 }
99 function antiRobotDefense(){
100 document.body.onmousedown=function(){
101 antiRobot |= 2;
102 antiRobotGo();
103 document.body.onmousedown=null;
104 }
105 document.body.onmousemove=function(){
106 antiRobot |= 2;
107 antiRobotGo();
108 document.body.onmousemove=null;
109 }
110 setTimeout(function(){
111 antiRobot |= 1;
112 antiRobotGo();
113 }, 100)
114 antiRobotGo();
115 }
116 antiRobotDefense();
117 </script>
118 <div class=fancy>
119 <div class=nosearch>
120 <div class="fancy_title">
121 The SQLITE_DBPAGE Virtual Table
122 </div>
123 </div>
124
125
126
127
128 <h1 id="overview"><span>1. </span>Overview</h1>
129
130 <p>
131 The SQLITE_DBPAGE extension implements an <a href="vtab.html#epoonlyvtab">eponymous-only virtual table</a> that
132 provides direct access to the underlying database file by interacting
133 with the pager. SQLITE_DBPAGE is capable of both reading and writing any
134 page of the database. Because interaction is through the pager layer, all
135 changes are transactional.
136 </p>
137
138 <p>
139 <b>Warning:</b> writing to the SQLITE_DBPAGE virtual table can very easily
140 cause unrecoverable database corruption. Do not allow untrusted components
141 to access the SQLITE_DBPAGE table. Use appropriate care while using the
142 SQLITE_DBPAGE table. Back up important data prior to experimenting with the
143 SQLITE_DBPAGE table. Writes to the SQLITE_DBPAGE virtual table are
144 disabled when the <a href="c3ref/c_dbconfig_defensive.html#sqlitedbconfigdefensive">SQLITE_DBCONFIG_DEFENSIVE</a> flag is set.
145
146 </p><p>
147 The SQLITE_DBPAGE extension is included in the <a href="amalgamation.html">amalgamation</a> though
148 it is disabled
149 by default. Use the <a href="compile.html#enable_dbpage_vtab">SQLITE_ENABLE_DBPAGE_VTAB</a> compile-time option to enable
150 the SQLITE_DBPAGE extension. The SQLITE_DBPAGE extension makes use of
151 unpublished internal interfaces and is not run-time loadable. The only way
152 to add SQLITE_DBPAGE to an application is to compile it in using the
153 <a href="compile.html#enable_dbpage_vtab">SQLITE_ENABLE_DBPAGE_VTAB</a> compile-time option.
154 </p>
155
156 <p>
157 The SQLITE_DBPAGE extension is enabled in default builds
158 of the <a href="cli.html">command-line shell</a>, but is read-only by default. Use
159 the --unsafe-testing command-line option to the CLI to enable writing
160 to the sqlite_dbpage virtual table.
161
162 </p><h1 id="usage"><span>2. </span>Usage</h1>
163
164 <p>
165 The SQLITE_DBPAGE virtual table read/write table that provides direct
166 access to the underlying disk file on a page-by-page basis. The
167 virtual table appears to have a schema like this:
168
169 </p><div class="codeblock"><pre>CREATE TABLE sqlite_dbpage(
170 pgno INTEGER PRIMARY KEY,
171 data BLOB
172 );
173 </pre></div>
174
175 <p>
176 An SQLite database file is divided into pages.
177 The first page is 1, the second page is 2, and so forth.
178 There is no page 0.
179 Every page is the same size.
180 The size of every page is a power of 2 between 512 and 65536.
181 See the <a href="fileformat2.html">file format</a> documentation for further details.
182
183 </p><p>
184 The SQLITE_DBPAGE table allows an application to view or replace the
185 raw binary content of each page of the database file.
186 No attempt is made to interpret the content of the page.
187 Content is returned byte-for-byte as it appears on disk.
188
189 </p><p>
190 The SQLITE_DBPAGE table has one row for each page in the database file.
191 SQLITE_DBPAGE allows pages to be read using a <a href="lang_select.html">SELECT</a> statement or
192 to be overwritten using an <a href="lang_update.html">UPDATE</a> or <a href="lang_insert.html">INSERT</a> statement. When using
193 an INSERT against sqlite_dbpage, it acts like <a href="lang_replace.html">REPLACE</a>. In other words,
194 INSERT into a page that already exists overwrites that page. To increase
195 the size of a database, INSERT into a page beyond the end of the database.
196
197 </p><h2 id="using_sqlite_dbpage_on_attach_ed_databases"><span>2.1. </span>Using SQLITE_DBPAGE On ATTACH-ed Databases</h2>
198
199 <p>
200 The SQLITE_DBPAGE table schema shown above is incomplete. There is
201 a third <a href="vtab.html#hiddencol">hidden column</a> named "schema" that determines which
202 <a href="lang_attach.html">ATTACH-ed database</a> should be read or written. Because
203 the "schema" column is hidden, it can be used as a parameter when
204 SQLITE_DBPAGE is invoked as a <a href="vtab.html#tabfunc2">table-valued function</a>.
205
206 </p><p>
207 For example, suppose an additional database is attached to the
208 database connection using a statement like this:
209
210 </p><div class="codeblock"><pre>ATTACH 'auxdata1.db' AS aux1;
211 </pre></div>
212
213 <p>
214 Then to read the first page of that database file, one merely runs:
215
216 </p><div class="codeblock"><pre>SELECT data FROM sqlite_dbpage('aux1') WHERE pgno=1;
217 </pre></div>
218
219 <p>
220 If the "schema" is omitted, it defaults to the primary database
221 (usually called 'main', unless renamed using <a href="c3ref/c_dbconfig_defensive.html#sqlitedbconfigmaindbname">SQLITE_DBCONFIG_MAINDBNAME</a>).
222 Hence, the following two queries are normally equivalent:
223
224 </p><div class="codeblock"><pre>SELECT data FROM sqlite_dbpage('main') WHERE pgno=1;
225 SELECT data FROM sqlite_dbpage WHERE pgno=1;
226 </pre></div>
227
228 <p>
229 The SQLITE_DBPAGE table can participate in a join just like any other
230 table. Hence, to see the content of the first page to all connected
231 database files, one might run a statement like this:
232
233 </p><div class="codeblock"><pre>SELECT dbpage.data, dblist.name
234 FROM pragma_database_list AS dblist
235 JOIN sqlite_dbpage(dblist.name) AS dbpage
236 WHERE dbpage.pgno=1;
237 </pre></div>
238
239 <h2 id="truncating_a_database"><span>2.2. </span>Truncating A Database</h2>
240
241 <p>
242 Normally the data for UPDATE and INSERT must be a blob which is the exact
243 same size as a database page. However, as a special case, doing an INSERT
244 of a NULL into page 2 or greater as the last operation prior to COMMIT
245 causes the page being inserted into and all subsequent pages to be deleted,
246 truncating the database. For example, a command like
247 "<tt>INSERT INTO sqlite_dbpage(pgno,data) VALUES(52,NULL)</tt>" causes
248 the database to be truncated to 51 pages in length. Pages are numbered
249 beginning with 1, so the total number of pages remaining is the same as
250 the last page number or 51 in this case.
251
252 </p><p>
253 Call an INSERT into sqlite_dbpage with a NULL page content
254 a "null-INSERT". The null-INSERT behavior is a special case and does
255 not necessarily follow the usual rules. The following are some specific
256 limitations of null-INSERT:
257
258 </p><ol>
259 <li><p>
260 The sqlite_dbpage table does not allow a null-INSERT into page 1.
261 Hence, it is not possible to truncate a database to zero bytes or
262 zero pages using sqlite_dbpage, as doing so would omit the database
263 header and the file would cease to be an SQLite database.
264
265 </p></li><li><p>
266 A null-INSERT only truncates the database if it is the last write
267 operation to occur before the transaction commit. If the null-INSERT is
268 within a multi-statement transaction and is followed by one or more UPDATE
269 or INSERT operations within that same transaction, then the null-INSERT
270 becomes a no-op and the database is not truncated.
271
272 </p></li><li><p>
273 Only a single database file can be truncated per transaction.
274 The sqlite_dbpage virtual table is able to write to multiple ATTACH-ed
275 databases during the same transaction, but because a null-INSERT must
276 be the last write statement to occur before COMMIT, sqlite_dbpage can
277 only truncate one of the ATTACH-ed databases. If multiple ATTACH-ed
278 databases need to be truncated, each must be truncated in a separate
279 transaction.
280
281 </p></li><li><p>
282 Any <a href="lang_savepoint.html">ROLLBACK TO</a> will cancel the null-INSERT,
283 even a ROLLBACK TO that does not involve the null-INSERT itself. The
284 special behavior of null-INSERT is only guaranteed if it occurs
285 as its own transaction or immediately before COMMIT.
286 </p></li></ol>
287
288 <h1 id="compatibility"><span>3. </span>Compatibility</h1>
289
290 <p>
291 The ability to do an INSERT into sqlite_dbpage, and to increase
292 or decrease the size of the database using INSERT was added in
293 SQLite version 3.47.0 (2024-10-21). Older versions do not allow
294 INSERT against sqlite_dbpage.
295 </p>
296