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
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
|
<!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>Imposter Tables</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">
Imposter Tables
</div>
</div>
<h1 id="introduction"><span>1. </span>Introduction</h1>
<p>
An imposter table is a table that is attached to the same <a href="fileformat2.html#btree">b-tree</a>
as an index.
An imposter table allows the content of an index to be queried or
modified as if the index were an ordinary table.
</p>
<p>
Imposter tables are intended for analysis and debugging only.
This is not a feature that most application developers should understand
or even know about.
Imposter tables are for experts only.
</p>
<p>
Improper use of imposter tables can cause index corruption, though
any corruption created this way can be fixed by running <a href="lang_reindex.html">REINDEX</a>.
</p>
<h1 id="details"><span>2. </span>Details</h1>
<p>
Each table and each index in SQLite is stored in a separate b-tree
in the database file. Each b-tree is identified by its root page
number. The root page number for any index or table can be found
by querying the "rootpage" column of the <a href="schematab.html">sqlite_schema table</a>.
See the <a href="queryplanner.html">indexing tutorial</a> and the <a href="fileformat2.html">file format</a> documentation
for further background on this design.
</p>
<p>
Usually the b-trees for tables and indexes are slightly different.
A table b-tree contains a 64-bit integer key and arbitrary data.
The 64-bit integer key is the <a href="lang_createtable.html#rowid">ROWID</a>. Index b-trees contain
an arbitrary binary key and no data. So table b-trees and index
b-trees are not directly compatible.
</p>
<p>
However, the b-tree for a <a href="withoutrowid.html">WITHOUT ROWID</a> table is in the same format
as an index b-tree. Thus, an index b-tree can be accessed as if it
were a WITHOUT ROWID table.
</p>
<h2 id="manually_created_imposter_tables"><span>2.1. </span>Manually Created Imposter Tables</h2>
<p>
One way to create an imposter table is to directly edit the sqlite_schema
table to insert a new row that describes the table.
For example, suppose the schema is like this:
</p>
<div class="codeblock"><pre>CREATE TABLE t1(a INTEGER PRIMARY KEY,b TEXT,c INT, d INT);
CREATE INDEX t1bc ON t1(b,c);
</pre></div>
<p>
The WITHOUT ROWID table that has the same structure as the t1bc index
would look like this:
</p>
<div class="codeblock"><pre>CREATE TABLE t2(b TEXT,c INT,a INT, PRIMARY KEY(b,c,a)) WITHOUT ROWID;
</pre></div>
<p>
To create a permanent imposter table "t2" against index "t1bc" one
should first enable editing of the sqlite_schema table by running
"<a href="pragma.html#pragma_writable_schema">PRAGMA writable_schema=ON</a>".
(Be careful to observe the warnings that accompany this PRAGMA.
A mistake can cause severe database corruption.)
Then insert a new entry into the sqlite_schema table like this:
</p>
<div class="codeblock"><pre>INSERT INTO sqlite_schema(type,name,tbl_name,rootpage,sql)
SELECT 'table','t2','t2',rootpage,
'CREATE TABLE t2(b,c,a,PRIMARY KEY(b,c,a))WITHOUT ROWID'
FROM sqlite_schema
WHERE name='t1bc';
</pre></div>
<p>
The INSERT statement above adds a new row to the sqlite_schema table that
defines a table "t2" having the same on-disk format as index "t1bc" and
pointing to the same b-tree.
After adding this sqlite_schema table entry, it is necessary to close and
reopen the database in order to get SQLite to reread the schema.
Then the "t2" table can be queried to see the content of the "t1bc" index.
</p>
<h3 id="corrupted_database"><span>2.1.1. </span>Corrupted Database</h3>
<p>
A serious problem with the manual imposter table approach described above
is that after adding the new "t2" entry to the "sqlite_schema" table, the
database file will technically be corrupt. Both the "t1bc" index and the
"t2" table will point to the same b-tree. This will not cause
any immediate problems, though one should avoid running <a href="lang_vacuum.html">VACUUM</a>.
</p>
<p>
It is possible to write into the "t2" table, thus changing the content
of the index.
But doing so will get the "t1bc" index out of synchronization with its
parent table "t1". An out-of-sync index can result in incorrect query
results.
</p>
<p>
Since the "t2" imposter table is a form of database corruption, the
manual approach to creating imposter tables is not recommended.
Actually, any use of imposter tables is discouraged for all but
expert developers, but manually created imposter tables are
especially discouraged because they are permanent.
</p>
<h2 id="transient_imposter_tables"><span>2.2. </span>Transient Imposter Tables</h2>
<p>
Another (safer) approach to creating an imposter table is to add an
entry for the imposter table to SQLite's internal symbol table without
updating the "sqlite_schema" table on disk.
That way, the imposter table exists in only a single database connection
and is automatically removed whenever the schema is reloaded.
</p>
<p>
Creation of a transient imposter table involves a special
<a href="c3ref/test_control.html">sqlite3_test_control()</a> call. Unlike all other SQLite APIs,
<a href="c3ref/test_control.html">sqlite3_test_control()</a> interface is subject to incompatible changes
from one release to the next, and so the mechanism described below
is not guaranteed to work in future releases of SQLite. The
SQLite developers do not consider this a problem because imposter
tables should not be used in applications. Imposter tables are for
analysis and testing use only.
</p>
<p>
To create a transient imposter table, first call sqlite3_test_control()
as follows:
</p>
<div class="codeblock"><pre>sqlite3_test_control(SQLITE_TESTCTRL_IMPOSTER, db, "main", 1, tnum);
</pre></div>
<p>
The "db" parameter is a pointer to the <a href="c3ref/sqlite3.html">database connection</a>.
The "main" argument is the name of the schema in which the imposter
table is to be created. The "1" argument enables the imposter table
mechanism. "tnum" is the root page of the index that the imposter
table should mirror.
</p>
<p>
After the sqlite3_test_control() call above, then run a <a href="lang_createtable.html">CREATE TABLE</a>
statement the defines the imposter table.
With the imposter mechanism enabled, this CREATE TABLE statement does
not create a real table but instead merely adds an entry in SQLite's
internal symbol table. Note that the CREATE TABLE statement must
be in the correct format for the index. If the imposter table has the
wrong number of columns or is not a <a href="withoutrowid.html">WITHOUT ROWID</a> table or is otherwise
incompatible with the index b-tree, then <a href="rescode.html#corrupt">SQLITE_CORRUPT</a> errors will result
when the imposter table is used.
</p>
<p>
After running the CREATE TABLE statement, disable the imposter mechanism
as follows:
</p>
<div class="codeblock"><pre>sqlite3_test_control(SQLITE_TESTCTRL_IMPOSTER, db, "main", 0, 0);
</pre></div>
<p>
In other words, make the same sqlite3_test_control() call except change
the last two parameters to zero.
</p>
<p>
After the imposter table is loaded into SQLite's internal schema as
described above, the imposter table can be used as any other table.
But the imposter table will only be visible to the one database
connection that created it. No changes are made to the database file
on disk. And the imposter table will disappear the next time the schema
is loaded.
</p>
<a name="dotimposter"></a>
<h2 id="the_imposter_shell_command"><span>2.3. </span>The .imposter Shell Command</h2>
<p>
As of SQLite 3.16.0 (2017-01-02), the <a href="cli.html">command-line shell</a> contains
a dot-command ".imposter" that does all of the work of setting up a
transient imposter table.
Instead of making multiple calls to sqlite3_test_control() and figuring
out and invoking a compatible CREATE TABLE statement, a transient
imposter table can be constructed as follows:
</p>
<div class="codeblock"><pre>.imposter t1bc t2
</pre></div>
<p>
Of course, substitute the desired index and imposter table names in
place of the "t1bc" and "t2" shown in the example.
The ".imposter" command reads the schema of the "t1bc" index, uses
that information to construct a compatible CREATE TABLE statement for
the imposter table, then makes all the necessary calls to create the
transient imposter table automatically.
</p>
<h1 id="summary_and_final_warning"><span>3. </span>Summary And Final Warning</h1>
<p>
The imposter table mechanism is a power analysis and debugging tool
for SQLite. But as with all sharp tools, it can also be dangerous and
can result in corrupt database files if misused. Do not attempt to
use imposter tables in an application. Imposter tables are intended
for use in the laboratory by experts.
</p>
<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/imposter.in?m=219fd65ee2">2022-01-08 05:02:57</a> UTC </small></i></p>
|
