Debian Codegraph @

www/imposter.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>Imposter Tables</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 Imposter Tables
122 </div>
123 </div>
124
125
126
127
128 <h1 id="introduction"><span>1. </span>Introduction</h1>
129
130 <p>
131 An imposter table is a table that is attached to the same <a href="fileformat2.html#btree">b-tree</a>
132 as an index.
133 An imposter table allows the content of an index to be queried
134 as if the index were an ordinary table.
135 </p>
136
137 <p>
138 Imposter tables are intended for analysis and debugging only.
139 This is not a feature that most application developers should understand
140 or even know about.
141 </p>
142
143 <p>
144 Imposter table are read-only by default. And as long as they are
145 read-only, their are harmless. But when <a href="pragma.html#pragma_writable_schema">PRAGMA writable_schema=ON</a>
146 is engaged, imposter table can be written. This can lead to
147 index corruption, though
148 any corruption created this way can be fixed by running <a href="lang_reindex.html">REINDEX</a>.
149 </p>
150
151 <h1 id="details"><span>2. </span>Details</h1>
152
153 <p>
154 Each table and each index in SQLite is stored in a separate b-tree
155 in the database file. Each b-tree is identified by its root page
156 number. The root page number for any index or table can be found
157 by querying the "rootpage" column of the <a href="schematab.html">sqlite_schema table</a>.
158 See the <a href="queryplanner.html">indexing tutorial</a> and the <a href="fileformat2.html">file format</a> documentation
159 for further background on this design.
160 </p>
161
162 <p>
163 Usually the b-trees for tables and indexes are slightly different.
164 A table b-tree contains a 64-bit integer key and arbitrary data.
165 The 64-bit integer key is the <a href="lang_createtable.html#rowid">ROWID</a>. Index b-trees contain
166 an arbitrary binary key and no data. So table b-trees and index
167 b-trees are not directly compatible.
168 </p>
169
170 <p>
171 However, the b-tree for a <a href="withoutrowid.html">WITHOUT ROWID</a> table is in the same format
172 as an index b-tree. Thus, an index b-tree can be accessed as if it
173 were a WITHOUT ROWID table.
174 </p>
175
176 <h2 id="manually_created_imposter_tables"><span>2.1. </span>Manually Created Imposter Tables</h2>
177
178 <p>
179 One way to create an imposter table is to directly edit the sqlite_schema
180 table to insert a new row that describes the table.
181 For example, suppose the schema is like this:
182 </p>
183
184 <div class="codeblock"><pre>CREATE TABLE t1(a INTEGER PRIMARY KEY,b TEXT,c INT, d INT);
185 CREATE INDEX t1bc ON t1(b,c);
186 </pre></div>
187
188 <p>
189 The WITHOUT ROWID table that has the same structure as the t1bc index
190 would look like this:
191 </p>
192
193 <div class="codeblock"><pre>CREATE TABLE t2(b TEXT,c INT,a INT, PRIMARY KEY(b,c,a)) WITHOUT ROWID;
194 </pre></div>
195
196 <p>
197 To create a permanent imposter table "t2" against index "t1bc" one
198 should first enable editing of the sqlite_schema table by running
199 "<a href="pragma.html#pragma_writable_schema">PRAGMA writable_schema=ON</a>".
200 (Be careful to observe the warnings that accompany this PRAGMA.
201 A mistake can cause severe database corruption.)
202 Then insert a new entry into the sqlite_schema table like this:
203 </p>
204
205 <div class="codeblock"><pre>INSERT INTO sqlite_schema(type,name,tbl_name,rootpage,sql)
206 SELECT 'table','t2','t2',rootpage,
207 'CREATE TABLE t2(b,c,a,PRIMARY KEY(b,c,a))WITHOUT ROWID'
208 FROM sqlite_schema
209 WHERE name='t1bc';
210 </pre></div>
211
212 <p>
213 The INSERT statement above adds a new row to the sqlite_schema table that
214 defines a table "t2" having the same on-disk format as index "t1bc" and
215 pointing to the same b-tree.
216 After adding this sqlite_schema table entry, it is necessary to close and
217 reopen the database in order to get SQLite to reread the schema.
218 Then the "t2" table can be queried to see the content of the "t1bc" index.
219 </p>
220
221 <h3 id="corrupted_database"><span>2.1.1. </span>Corrupted Database</h3>
222
223 <p>
224 A serious problem with the manual imposter table approach described above
225 is that after adding the new "t2" entry to the "sqlite_schema" table, the
226 database file will technically be corrupt. Both the "t1bc" index and the
227 "t2" table will point to the same b-tree. This will not cause
228 any immediate problems, though one should avoid running <a href="lang_vacuum.html">VACUUM</a>.
229 </p>
230
231 <p>
232 If <a href="pragma.html#pragma_writable_schema">PRAGMA writable_schema</a> is turned on, then
233 it is possible to write into the "t2" table, thus changing the content
234 of the index.
235 Doing so will get the "t1bc" index out of synchronization with its
236 parent table "t1". An out-of-sync index can result in incorrect query
237 results.
238 </p>
239
240 <p>
241 Since the "t2" imposter table is a form of database corruption, the
242 manual approach to creating imposter tables is not recommended.
243 Actually, any use of imposter tables is discouraged for all but
244 expert developers, but manually created imposter tables are
245 especially discouraged because they are permanent.
246 </p>
247
248 <h2 id="transient_imposter_tables"><span>2.2. </span>Transient Imposter Tables</h2>
249
250 <p>
251 Another (safer) approach to creating an imposter table is to add an
252 entry for the imposter table to SQLite's internal symbol table without
253 updating the "sqlite_schema" table on disk.
254 That way, the imposter table exists in only a single database connection
255 and is automatically removed whenever the schema is reloaded.
256 </p>
257
258 <p>
259 Creation of a transient imposter table involves a special
260 <a href="c3ref/test_control.html">sqlite3_test_control()</a> call. Unlike all other SQLite APIs,
261 <a href="c3ref/test_control.html">sqlite3_test_control()</a> interface is subject to incompatible changes
262 from one release to the next, and so the mechanism described below
263 is not guaranteed to work in future releases of SQLite. The
264 SQLite developers do not consider this a problem because imposter
265 tables should not be used in applications. Imposter tables are for
266 analysis and testing use only.
267 </p>
268
269 <p>
270 To create a transient imposter table, first call sqlite3_test_control()
271 as follows:
272 </p>
273
274 <div class="codeblock"><pre>sqlite3_test_control(SQLITE_TESTCTRL_IMPOSTER, db, "main", 1, tnum);
275 </pre></div>
276
277 <p>
278 The "db" parameter is a pointer to the <a href="c3ref/sqlite3.html">database connection</a>.
279 The "main" argument is the name of the schema in which the imposter
280 table is to be created. The "1" argument enables the imposter table
281 mechanism. If the argument is "2" instead of "1", then a read-only
282 imposter table is created. "tnum" is the root page of the index that
283 the imposter table should mirror.
284 </p>
285
286 <p>
287 After the sqlite3_test_control() call above, then run a <a href="lang_createtable.html">CREATE TABLE</a>
288 statement that defines the imposter table.
289 With the imposter mechanism enabled, this CREATE TABLE statement does
290 not create a real table but instead merely adds an entry in SQLite's
291 internal symbol table. Note that the CREATE TABLE statement must
292 be in the correct format for the index. If the imposter table has the
293 wrong number of columns or is not a <a href="withoutrowid.html">WITHOUT ROWID</a> table or is otherwise
294 incompatible with the index b-tree, then <a href="rescode.html#corrupt">SQLITE_CORRUPT</a> errors will result
295 when the imposter table is used.
296 </p>
297
298 <p>
299 After running the CREATE TABLE statement, disable the imposter mechanism
300 as follows:
301 </p>
302
303 <div class="codeblock"><pre>sqlite3_test_control(SQLITE_TESTCTRL_IMPOSTER, db, "main", 0, 0);
304 </pre></div>
305
306 <p>
307 In other words, make the same sqlite3_test_control() call except change
308 the last two parameters to zero.
309 </p>
310
311 <p>
312 After the imposter table is loaded into SQLite's internal schema as
313 described above, the imposter table can be used as any other table.
314 But the imposter table will only be visible to the one database
315 connection that created it. No changes are made to the database file
316 on disk. And the imposter table will disappear the next time the schema
317 is loaded.
318 </p>
319
320 <a name="dotimposter"></a>
321
322 <h2 id="the_imposter_shell_command"><span>2.3. </span>The .imposter Shell Command</h2>
323
324 <p>
325 The <a href="cli.html">command-line shell</a> contains
326 a dot-command ".imposter" that does all of the work of setting up a
327 transient imposter table.
328 Instead of making multiple calls to sqlite3_test_control() and figuring
329 out and invoking a compatible CREATE TABLE statement, a transient
330 imposter table can be constructed as follows:
331 </p>
332
333 <div class="codeblock"><pre>.imposter t1bc t2
334 </pre></div>
335
336 <p>
337 Of course, substitute the desired index and imposter table names in
338 place of the "t1bc" and "t2" shown in the example.
339 The ".imposter" command reads the schema of the "t1bc" index, uses
340 that information to construct a compatible CREATE TABLE statement for
341 the imposter table, then makes all the necessary calls to create the
342 transient imposter table automatically.
343 </p>
344
345 <p>
346 The ".imposter" command works by default in SQLite version 3.51.0
347 (2025-11-04) or later, except that the created imposter table
348 is read-only. Use the <a href="cli.html#testing_mode">--unsafe-testing command-line option</a> to active
349 the ".imposter" command in earlier versions of the CLI, or to create
350 a read-write imposter table.
351
352 </p><h1 id="summary_and_final_warning"><span>3. </span>Summary And Final Warning</h1>
353
354 <p>
355 The imposter table mechanism is a powerful analysis and debugging tool
356 for SQLite. But as with all sharp tools, it can also be dangerous and
357 can result in corrupt database files if misused. Do not attempt to
358 use imposter tables in an application. Imposter tables are intended
359 for use in the laboratory by experts.
360 </p>
361 <p align="center"><small><i>This page was last updated on 2025-11-13 07:12:58Z </small></i></p>
362