︙ | | |
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
|
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
|
-
-
+
+
|
reading and writing format 2 was added in SQLite version 3.1.3
on 2005-02-19.</li>
<li value=3>Format 3 adds the ability of extra columns added by
[ALTER TABLE | ALTER TABLE ... ADD COLUMN] to have non-NULL default
values. This capability was added in SQLite version 3.1.4
on 2005-03-11.</li>
<li value=4>^Format 4 causes SQLite to respect the
[descending indices | DESC keyword] on
index declarations. (^The DESC keyword is ignored in indices for
[descending indexes | DESC keyword] on
index declarations. (^The DESC keyword is ignored in indexes for
formats 1, 2, and 3.)
^Format 4 also adds two new boolean record type values ([serial types]
8 and 9.) Support for format 4 was added in SQLite 3.3.0 on
2006-01-10.</li>
</ol>
<p>^New database files created by SQLite use format 4 by default.
|
︙ | | |
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
|
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
|
-
+
|
b-trees are identified by their root page number.</p>
<p>A b-tree page is either a table b-tree page or an index b-tree page.
All pages within each complete b-tree are of the same type: either table
or index. There is a one table b-trees in the database file
for each rowid table in the database schema, including system tables
such as sqlite_master. There is one index b-trees
in the database file for each index in the schema, including implied indices
in the database file for each index in the schema, including implied indexes
created by uniqueness constraints. There are no b-trees associated with
[virtual tables]. Specific virtual table implementations might make use
of [shadow tables] for storage, but those shadow tables will have separate
entries in the database schema. [WITHOUT ROWID] tables use index b-trees
rather than a table b-trees, so there is one
index b-tree in the database file for each [WITHOUT ROWID] table.
The b-tree corresponding to the sqlite_master table is always a table
|
︙ | | |
1112
1113
1114
1115
1116
1117
1118
1119
1120
1121
1122
1123
1124
1125
1126
1127
1128
1129
1130
1131
1132
1133
1134
1135
1136
1137
1138
1139
1140
1141
1142
1143
1144
1145
1146
|
1112
1113
1114
1115
1116
1117
1118
1119
1120
1121
1122
1123
1124
1125
1126
1127
1128
1129
1130
1131
1132
1133
1134
1135
1136
1137
1138
1139
1140
1141
1142
1143
1144
1145
1146
|
-
+
-
+
|
<p>^(The sqlite_master.type column will be one
of the following text strings: 'table', 'index', 'view', or 'trigger'
according to the type of object defined. The 'table' string is used
for both ordinary and [virtual tables].)^</p>
</p>^(The sqlite_master.name column will hold the name of the object.)^
^([UNIQUE] and [PRIMARY KEY] constraints on tables cause SQLite to create
[internal indices] with names of the form "sqlite_autoindex_TABLE_N"
[internal indexes] with names of the form "sqlite_autoindex_TABLE_N"
where TABLE is replaced by the name of the table that contains the
constraint and N is an integer beginning with 1 and increasing by one
with each constraint seen in the table definition.)^
^(In a [WITHOUT ROWID] table, there is no sqlite_master entry for the
PRIMARY KEY, but the "sqlite_autoindex_TABLE_N" name is set aside
for the PRIMARY KEY as if the sqlite_master entry did exist. This
will affect the numbering of subsequent UNIQUE constraints.)^
^The "sqlite_autoindex_TABLE_N" name is never allocated for an
[INTEGER PRIMARY KEY], either in rowid tables or WITHOUT ROWID tables.
</p>
<p>The sqlite_master.tbl_name column holds the name of a table or view
that the object is associated with. ^For a table or view, the
tbl_name column is a copy of the name column. ^For an index, the tbl_name
is the name of the table that is indexed. ^For a trigger, the tbl_name
column stores the name of the table or view that causes the trigger
to fire.</p>
<p>^(The sqlite_master.rootpage column stores the page number of the root
b-tree page for tables and indices.)^ ^For rows that define views, triggers,
b-tree page for tables and indexes.)^ ^For rows that define views, triggers,
and virtual tables, the rootpage column is 0 or NULL.</p>
<p>^(The sqlite_master.sql column stores SQL text that describes the
object. This SQL text is a [CREATE TABLE], [CREATE VIRTUAL TABLE],
[CREATE INDEX],
[CREATE VIEW], or [CREATE TRIGGER] statement that if evaluated against
the database file when it is the main database of a [database connection]
|
︙ | | |
1159
1160
1161
1162
1163
1164
1165
1166
1167
1168
1169
1170
1171
1172
1173
1174
1175
1176
1177
1178
1179
1180
1181
1182
|
1159
1160
1161
1162
1163
1164
1165
1166
1167
1168
1169
1170
1171
1172
1173
1174
1175
1176
1177
1178
1179
1180
1181
1182
|
-
+
-
+
-
+
|
<li>^All spaces following the first two keywords are converted into a single
space.
</ul>
<p>^(The text in the sqlite_master.sql column is a copy of the original
CREATE statement text that created the object, except normalized as
described above and as modified by subsequent [ALTER TABLE] statements.)^
^(The sqlite_master.sql is NULL for the [internal indices] that are
^(The sqlite_master.sql is NULL for the [internal indexes] that are
automatically created by [UNIQUE] or [PRIMARY KEY] constraints.)^</p>
<tcl>hd_fragment intschema {internal schema objects} \
{internal schema object} {internal index} {internal indices} \
{internal schema object} {internal index} {internal indexes} \
{internal table} {internal tables}</tcl>
<h4>2.6.1 Internal Schema Objects</h4>
<p>^In addition to the tables, indices, views, and triggers created by
<p>^In addition to the tables, indexes, views, and triggers created by
the application and/or the developer using CREATE statements SQL, the
sqlite_master table may contain zero or more entries for
<i>internal schema objects</i> that are created by SQLite for its
own internal use. ^The names of internal schema objects
always begin with "sqlite_" and any table, index, view, or trigger
whose name begins with "sqlite_" is an internal schema object.
^SQLite prohibits applications from creating objects whose names begin
|
︙ | | |
1243
1244
1245
1246
1247
1248
1249
1250
1251
1252
1253
1254
1255
1256
1257
|
1243
1244
1245
1246
1247
1248
1249
1250
1251
1252
1253
1254
1255
1256
1257
|
-
+
|
^Application code can delete all entries from the sqlite_sequence table,
but application code cannot drop the sqlite_sequence table.
<tcl>hd_fragment stat1tab {sqlite_stat1} SQLITE_STAT1 </tcl>
<h4>2.6.3 The sqlite_stat1 table</h4>
<p>^The sqlite_stat1 is an internal table created by the [ANALYZE] command
and used to hold supplemental information about tables and indices that the
and used to hold supplemental information about tables and indexes that the
query planner can use to help it find better ways of performing queries.
^Applications can update, delete from, insert into or drop the sqlite_stat1
table, but may not create or alter the sqlite_stat1 table.
^The schema of the sqlite_stat1 table is as follows:
<blockquote><pre>
CREATE TABLE sqlite_stat1(tbl,idx,stat);
|
︙ | | |
1282
1283
1284
1285
1286
1287
1288
1289
1290
1291
1292
1293
1294
1295
1296
|
1282
1283
1284
1285
1286
1287
1288
1289
1290
1291
1292
1293
1294
1295
1296
|
-
+
|
<p>^The list of integers in the stat column can also optionally be followed
by a token of the form "sz=NNN" where NNN is an unsigned integer. The
"sz=NNN" token, if present, must be separated from the last integer by a
single space. ^The "sz=NNN" token means that the average row size over all
records of the table or
index is NNN bytes per row. ^The SQLite query planner might use the
estimated row size information provided by the "sz=NNN" token
to help it choose smaller tables and indices that require less disk I/O.
to help it choose smaller tables and indexes that require less disk I/O.
<p>If the sqlite_stat1.idx column is NULL, then the sqlite_stat1.stat
column contains a single integer which is the approximate number of
rows in the table identified by sqlite_stat1.tbl.
<tcl>hd_fragment stat2tab {sqlite_stat2}</tcl>
<h4>2.6.4 The sqlite_stat2 table</h4>
|
︙ | | |
1371
1372
1373
1374
1375
1376
1377
1378
1379
1380
1381
1382
1383
1384
1385
1386
1387
1388
1389
1390
1391
1392
1393
1394
1395
1396
1397
1398
1399
1400
1401
1402
1403
1404
1405
1406
1407
1408
|
1371
1372
1373
1374
1375
1376
1377
1378
1379
1380
1381
1382
1383
1384
1385
1386
1387
1388
1389
1390
1391
1392
1393
1394
1395
1396
1397
1398
1399
1400
1401
1402
1403
1404
1405
1406
1407
1408
1409
1410
1411
1412
1413
1414
1415
1416
1417
1418
1419
1420
1421
1422
1423
1424
1425
1426
1427
1428
1429
1430
1431
1432
1433
1434
1435
1436
1437
1438
1439
1440
1441
1442
1443
1444
1445
|
-
+
+
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
|
<h4>2.6.6 The sqlite_stat4 table</h4>
<p>The sqlite_stat4 is only created and is only used if SQLite is compiled
with [SQLITE_ENABLE_STAT4] and if the SQLite version number is
3.8.1 or greater. The sqlite_stat4 table is neither read nor written by any
version of SQLite before 3.8.1.
The sqlite_stat4 table contains additional information
about the distribution of keys within an index, information that the
about the distribution of keys within an index or the distribution of
keys in the primary key of a [WITHOUT ROWID] table, information that the
query planner can use to devise better and faster query algorithms.
The schema of the sqlite_stat4 table is as follows:
<blockquote><pre>
CREATE TABLE sqlite_stat4(tbl,idx,nEq,nLt,nDLt,sample);
</pre></blockquote>
<p>There are usually multiple entries in the sqlite_stat4 table for each index.
The sqlite_stat4.sample column holds the content of an index entry in
the [record format]. The index entry stored is taken from the
index identified by sqlite_stat4.idx and sqlite_stat4.tbl.
The sqlite_stat4.nEq column holds a list of integers where the K-th integer
is the approximate number of entries in the index whose left-most K columns
exactly match the K left-most columns of the sample.
The sqlite_stat4.nLt holds a list of integers where the K-th integer is
the approximate number of entries in the
index whose K left-most columns are collectively less than the
K left-most columns of the sample.
The sqlite_stat4.nDLt column holds a list of integers where the K-th
integers is the approximate
number of entries in the index that are distinct in the first K columns and
that are whose left-most K columns are collectively less than the left-most
K columns of the sample.
<p>There are typically between 10 to 40 entries in the sqlite_stat4 table for
each index for which statistics are available, however size limits are
not hard bounds.
The meanings of the columns in the sqlite_stat4 table are as follows:
<center>
<table border="0" width="100%" cellpadding="10">
<tr><td valign="top" align="right">tbl:</td>
<td>The sqlite_stat4.tbl column holds name of the table that owns
the index that the row describes
<tr><td valign="top" align="right">idx:</td>
<td>The sqlite_stat4.idx column holds name of the index that the
row describes, or in the case of
an sqlite_stat4 entry for a [WITHOUT ROWID] table, the
name of the table itself.
<tr><td valign="top" align="right">sample:</td>
<td>The sqlite_stat4.sample column holds a BLOB
in the [record format] that encodes the indexed columns followed by
the rowid for a rowid table or by the columns of the primary key
for a WITHOUT ROWID table.
The sqlite_stat4.sample BLOB for the WITHOUT ROWID table itself
contains just the columns of the primary key.
Let the number of columns encoded by the sqlite_stat4.sample blob be N.
For indexes on an ordinary rowid table, N will be one more than the number
of columns indexed.
For indexes on WITHOUT ROWID tables, N will be the number of columns
indexed plus the number of columns in the primary key.
For a WITHOUT ROWID table, N will be the number of columns in the
primary key.
<tr><td valign="top" align="right">nEq:</td>
<td>The sqlite_stat4.nEq column holds a list of N integers where
the K-th integer is the approximate number of entries in the index
whose left-most K columns exactly match the K left-most columns
of the sample.
<tr><td valign="top" align="right">nLt:</td>
<td>The sqlite_stat4.nLt column holds a list of N integers where
the K-th integer is the approximate number of entries in the
index whose K left-most columns are collectively less than the
K left-most columns of the sample.
<tr><td valign="top" align="right">nDLt:</td>
<td>The sqlite_stat4.nDLt column holds a list of N integers where the K-th
integers is the approximate
number of entries in the index that are distinct in the first K columns and
that are whose left-most K columns are collectively less than the left-most
K columns of the sample.
</table>
</center>
<p>The sqlite_stat4 is a generalization of the sqlite_stat3 table. The
sqlite_stat3 table provides information about the left-most column of an
index whereas the sqlite_stat4 table provides information about all columns
of the index.
<p>There can be an arbitrary number of sqlite_stat4 entries per index.
|
︙ | | |