Debian Codegraph @

www/schematab.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 Schema 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 Schema Table
122 </div>
123 <details class="fancy_toc">
124 <summary>Table Of Contents</summary>
125 <div id="toc_sub"><div class="fancy-toc1"><a href="#introduction">1. Introduction</a></div>
126 <div class="fancy-toc1"><a href="#alternative_names">2. Alternative Names</a></div>
127 <div class="fancy-toc1"><a href="#interpretation_of_the_schema_table">3. Interpretation Of The Schema Table</a></div>
128 <div class="fancy-toc1"><a href="#creation_and_modification_of_the_schema_table">4. Creation and Modification Of The Schema Table</a></div>
129 </div>
130 </details>
131 </div>
132
133
134
135
136
137 <h1 id="introduction"><span>1. </span>Introduction</h1>
138
139 <p>Every SQLite database contains a single "schema table" that stores the
140 schema for that database. The schema for a database is a description of
141 all of the other tables, indexes, triggers, and views that are
142 contained within the database. The schema table looks like this:
143
144 </p><blockquote><pre>
145 CREATE TABLE sqlite_schema(
146 type text,
147 name text,
148 tbl_name text,
149 rootpage integer,
150 sql text
151 );
152 </pre></blockquote>
153
154 <p>The sqlite_schema table contains one row for each table, index, view,
155 and trigger (collectively "objects") in the schema, except there
156 is no entry for the sqlite_schema table itself. See the
157 <a href="fileformat2.html#ffschema">schema storage</a> subsection of the <a href="fileformat2.html">file format</a> documentation for
158 additional information on how SQLite uses the sqlite_schema table
159 internally.
160
161 </p><h1 id="alternative_names"><span>2. </span>Alternative Names</h1>
162
163 <p>The schema table can always be referenced using the name "sqlite_schema",
164 especially if qualifed by the schema name like
165 "main.sqlite_schema" or "temp.sqlite_schema". But for historical
166 compatibility, some alternative names are also recognized, including:
167
168 </p><ol>
169 <li> sqlite_master
170 </li><li> sqlite_temp_schema
171 </li><li> sqlite_temp_master
172 </li></ol>
173
174 <p>
175 Alternatives (2) and (3) only work for the TEMP database associated
176 with each database connection, but alternative (1) works anywhere.
177 For historical reasons, callbacks from the <a href="c3ref/set_authorizer.html">sqlite3_set_authorizer()</a>
178 interface always refer to the schema table using names (1) or (3).
179
180 </p><h1 id="interpretation_of_the_schema_table"><span>3. </span>Interpretation Of The Schema Table</h1>
181
182 <p>The meanings of the fields of the schema table are as follows:
183
184 </p><dl>
185 <dt><b>type</b></dt>
186 <dd>
187 <p>The sqlite_schema.type column will be one
188 of the following text strings: 'table', 'index', 'view', or 'trigger'
189 according to the type of object defined. The 'table' string is used
190 for both ordinary and <a href="vtab.html">virtual tables</a>.</p>
191 </dd>
192
193 <dt><b>name</b></dt><dt>
194 </dt><dd>
195 <p>The sqlite_schema.name column will hold the name of the object.
196 <a href="lang_createtable.html#uniqueconst">UNIQUE</a> and <a href="lang_createtable.html#primkeyconst">PRIMARY KEY</a> constraints on tables cause SQLite to create
197 <a href="fileformat2.html#intschema">internal indexes</a> with names of the form "sqlite_autoindex_TABLE_N"
198 where TABLE is replaced by the name of the table that contains the
199 constraint and N is an integer beginning with 1 and increasing by one
200 with each constraint seen in the table definition.
201 In a <a href="withoutrowid.html">WITHOUT ROWID</a> table, there is no sqlite_schema entry for the
202 PRIMARY KEY, but the "sqlite_autoindex_TABLE_N" name is set aside
203 for the PRIMARY KEY as if the sqlite_schema entry did exist. This
204 will affect the numbering of subsequent UNIQUE constraints.
205 The "sqlite_autoindex_TABLE_N" name is never allocated for an
206 <a href="lang_createtable.html#rowid">INTEGER PRIMARY KEY</a>, either in rowid tables or WITHOUT ROWID tables.
207 </p>
208 </dd>
209
210 <dt><b>tbl_name</b></dt>
211 <dd>
212 <p>The sqlite_schema.tbl_name column holds the name of a table or view
213 that the object is associated with. For a table or view, the
214 tbl_name column is a copy of the name column. For an index, the tbl_name
215 is the name of the table that is indexed. For a trigger, the tbl_name
216 column stores the name of the table or view that causes the trigger
217 to fire.</p>
218 </dd>
219
220 <dt><b>rootpage</b></dt>
221 <dd>
222 <p>The sqlite_schema.rootpage column stores the page number of the root
223 b-tree page for tables and indexes. For rows that define views, triggers,
224 and virtual tables, the rootpage column is 0 or NULL.</p>
225 </dd>
226
227 <dt><b>sql</b></dt>
228 <dd>
229 <p>The sqlite_schema.sql column stores SQL text that describes the
230 object. This SQL text is a <a href="lang_createtable.html">CREATE TABLE</a>, <a href="lang_createvtab.html">CREATE VIRTUAL TABLE</a>,
231 <a href="lang_createindex.html">CREATE INDEX</a>,
232 <a href="lang_createview.html">CREATE VIEW</a>, or <a href="lang_createtrigger.html">CREATE TRIGGER</a> statement that if evaluated against
233 the database file when it is the main database of a <a href="c3ref/sqlite3.html">database connection</a>
234 would recreate the object. The text is usually a copy of the original
235 statement used to create the object but with normalizations applied so
236 that the text conforms to the following rules:
237
238 </p><ul>
239 <li>The CREATE, TABLE, VIEW, TRIGGER, and INDEX keywords at the beginning
240 of the statement are converted to all upper case letters.
241 </li><li>The TEMP or TEMPORARY keyword is removed if it occurs after the
242 initial CREATE keyword.
243 </li><li>Any database name qualifier that occurs prior to the name of the
244 object being created is removed.
245 </li><li>Leading spaces are removed.
246 </li><li>All spaces following the first two keywords are converted into a single
247 space.
248 </li></ul>
249
250 <p>The text in the sqlite_schema.sql column is a copy of the original
251 CREATE statement text that created the object, except normalized as
252 described above and as modified by subsequent <a href="lang_altertable.html">ALTER TABLE</a> statements.
253 The sqlite_schema.sql is NULL for the <a href="fileformat2.html#intschema">internal indexes</a> that are
254 automatically created by <a href="lang_createtable.html#uniqueconst">UNIQUE</a> or <a href="lang_createtable.html#primkeyconst">PRIMARY KEY</a> constraints.</p>
255 </dd>
256 </dl>
257
258 <h1 id="creation_and_modification_of_the_schema_table"><span>4. </span>Creation and Modification Of The Schema Table</h1>
259
260 <p>SQLite creates the schema table upon database creation and modifies
261 its content as SQLite users submit DDL statements for execution. There
262 is no need for users to modify it under normal circumstances, and they
263 bear the risk of <a href="howtocorrupt.html#cfgerr">database corruption</a> if they do modify it.
264 </p><p align="center"><small><i>This page was last updated on 2023-06-16 10:11:06Z </small></i></p>
265