diff options
Diffstat (limited to 'www/imposter.html')
| -rw-r--r-- | www/imposter.html | 351 |
1 files changed, 351 insertions, 0 deletions
diff --git a/www/imposter.html b/www/imposter.html new file mode 100644 index 0000000..0a20b75 --- /dev/null +++ b/www/imposter.html @@ -0,0 +1,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> + |