Documentation Source Text

Check-in [50947bf51f]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Start documentation for 3.7.10 by noting the change in DEFAULT_FILE_FORMAT.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 50947bf51fe6b32c89050f8edfa254222addab25
User & Date: drh 2011-11-07 14:45:44.704
Context
2011-11-11
13:24
Update the bubble diagram for select-core so that GROUP BY does not allow ASC or DESC keywords. This brings the documentation into compliance with the implementation. (check-in: 40ef9e8de8 user: drh tags: trunk)
2011-11-07
14:45
Start documentation for 3.7.10 by noting the change in DEFAULT_FILE_FORMAT. (check-in: 50947bf51f user: drh tags: trunk)
2011-11-01
13:14
Add SHA1 hashes to the change-log for 3.7.9. (check-in: b2eb449d5e user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to pages/changes.in.
37
38
39
40
41
42
43









44
45
46
47
48
49
50
      <a href="http://www.sqlite.org/src/timeline">
      http://www.sqlite.org/src/timeline</a>.</p>
    }
    hd_close_aux
    hd_enable_main 1
  }
}










chng {2011 November 1 (3.7.9)} {
<li>If a search token (on the right-hand side of the MATCH operator) in
    [FTS4] begins with "&#94;" then that token must be the first in its field
    of the document.  <b>** Potentially Incompatible Change **</b>
<li>Added options [SQLITE_DBSTATUS_CACHE_HIT] and [SQLITE_DBSTATUS_CACHE_MISS]
    to the [sqlite3_db_status()] interface.







>
>
>
>
>
>
>
>
>







37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
      <a href="http://www.sqlite.org/src/timeline">
      http://www.sqlite.org/src/timeline</a>.</p>
    }
    hd_close_aux
    hd_enable_main 1
  }
}

chng {2012 February 1 (3.7.10)} {
<li>The default [schema format number] is changed from 1 to 4.
    This means that, unless
    the [PRAGMA legacy_file_format | PRAGMA legacy_file_format=ON] statement is
    run, newly created database files will be unreadable by version of SQLite
    prior to 3.3.0 (2006-01-10).  It also means that the [descending indices]
    are enabled by default.
}

chng {2011 November 1 (3.7.9)} {
<li>If a search token (on the right-hand side of the MATCH operator) in
    [FTS4] begins with "&#94;" then that token must be the first in its field
    of the document.  <b>** Potentially Incompatible Change **</b>
<li>Added options [SQLITE_DBSTATUS_CACHE_HIT] and [SQLITE_DBSTATUS_CACHE_MISS]
    to the [sqlite3_db_status()] interface.
Changes to pages/compile.in.
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
COMPILE_OPTION {SQLITE_DEFAULT_CACHE_SIZE=<i>&lt;pages&gt;</i>} {
  This macro sets the default size of the page-cache for each attached
  database, in pages. This can be overridden by the 
  [PRAGMA cache_size] command. The default value is 2000.
}

COMPILE_OPTION {SQLITE_DEFAULT_FILE_FORMAT=<i>&lt;1 or 4&gt;</i>} {
  The default schema-level file format used by SQLite when creating
  new database files is set by this macro.  The file formats are all
  very similar.  The difference between formats 1 and 4 is that format
  4 understands descending indices and has a tighter encoding for
  boolean values.


  SQLite (as of [version 3.6.0]) can read and write any file format
  between 1 and 4.  But older versions of SQLite might not be able to
  read formats greater than 1.  So that older versions of SQLite will
  be able to read and write database files created by newer versions
  of SQLite, the default file format is set to 1 for maximum

  compatibility.

  The file format for a new database can be set at runtime using
  the [PRAGMA legacy_file_format] command.
}

COMPILE_OPTION {SQLITE_DEFAULT_FILE_PERMISSIONS=<i>N</i>} {
  The default numeric file permissions for newly created database files
  under unix.  If not specified, the default is 0644 which means that
  the files is globally readable but only writable by the creator.







|
|

|


>
|



|
>
|

|







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
COMPILE_OPTION {SQLITE_DEFAULT_CACHE_SIZE=<i>&lt;pages&gt;</i>} {
  This macro sets the default size of the page-cache for each attached
  database, in pages. This can be overridden by the 
  [PRAGMA cache_size] command. The default value is 2000.
}

COMPILE_OPTION {SQLITE_DEFAULT_FILE_FORMAT=<i>&lt;1 or 4&gt;</i>} {
  The default [schema format number] used by SQLite when creating
  new database files is set by this macro.  The schema formats are all
  very similar.  The difference between formats 1 and 4 is that format
  4 understands [descending indices] and has a tighter encoding for
  boolean values.

  All versions of SQLite since 3.3.0 (2006-01-10)
  can read and write any schema format
  between 1 and 4.  But older versions of SQLite might not be able to
  read formats greater than 1.  So that older versions of SQLite will
  be able to read and write database files created by newer versions
  of SQLite, the default schema format was set to 1 for SQLite versions
  through 3.7.9 (2011-11-01).  Beginning with version 3.7.10, the default
  schema format is 4.

  The schema format number for a new database can be set at runtime using
  the [PRAGMA legacy_file_format] command.
}

COMPILE_OPTION {SQLITE_DEFAULT_FILE_PERMISSIONS=<i>N</i>} {
  The default numeric file permissions for newly created database files
  under unix.  If not specified, the default is 0644 which means that
  the files is globally readable but only writable by the creator.
Changes to pages/fileformat2.in.
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
journal or write-ahead log contains critical state information needed 
to restore the main database file to a consistent state.  When a rollback 
journal or write-ahead log contain information necessary for recovering 
the state of the database, they are called a "hot journal" or "hot WAL file".
Hot journals and WAL files are only a factor during error recovery
scenarios and so are uncommon, but they are part of the state of an SQLite
database and so cannot be ignored.  This document defines the format
of a rollback journal and the write-ahead log file, but main focus is
on the main database file.</p>

<h3>1.1 Pages</h3>

<p>The main database file consists of one or more pages.  ^The size of a
page is a power of two between 512 and 65536 inclusive.  All pages within
the same database are the same size.  ^The page size for a database file







|







21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
journal or write-ahead log contains critical state information needed 
to restore the main database file to a consistent state.  When a rollback 
journal or write-ahead log contain information necessary for recovering 
the state of the database, they are called a "hot journal" or "hot WAL file".
Hot journals and WAL files are only a factor during error recovery
scenarios and so are uncommon, but they are part of the state of an SQLite
database and so cannot be ignored.  This document defines the format
of a rollback journal and the write-ahead log file, but the focus is
on the main database file.</p>

<h3>1.1 Pages</h3>

<p>The main database file consists of one or more pages.  ^The size of a
page is a power of two between 512 and 65536 inclusive.  All pages within
the same database are the same size.  ^The page size for a database file
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
prepared statement is compiled against a specific version of the
database schema.  ^Whenever the database schema changes, the statement
must be reprepared.  ^Whenever a prepared statement runs, it first checks
the schema cookie to make sure the value is the same as when the statement
was prepared and if the schema cookie has changed, the statement aborts
in order to force the statement to be reprepared.</p>


<h4>1.2.10 Schema format number</h4>

<p>The schema format number is a 4-byte big-endian integer at offset 44.
The schema format number is similar to the file format read and write
version numbers at offsets 18 and 19 except that the schema format number
refers to the high-level SQL formatting rather than the low-level b-tree
formatting.  Four schema format numbers are currently defined:</p>

<ol>
<li value=1>Format 1 is understood by all versions of SQLite back to
version 3.0.0.</li>
<li value=2>Format 2 adds the ability of rows within the same table
to have a varying number of columns, in order to support the
[ALTER TABLE | ALTER TABLE ... ADD COLUMN] functionality.  Support for
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 DESC keyword on

index declarations.  (^The DESC keyword is ignored in indices 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 1 by default, so
that database files created by newer versions of SQLite can still
be read by older versions of SQLite.
^The [legacy_file_format pragma] can be used to cause SQLite
to create new database files using format 4.  Future versions of 
SQLite may begin to create files using format 4 by default.
The format version number can be made to default to 4 instead of 1 by
setting [SQLITE_DEFAULT_FILE_FORMAT]=4 at compile-time.
</p>

<h4>1.2.11 Suggested cache size</h4>

<p>The 4-byte big-endian signed integer at offset 48 is the suggested
cache size in pages for the database file.  The value is a suggestion
only and SQLite is under no obligation to honor it.  The absolute value
of the integer is used as the suggested size.  The suggested cache size
can be set using the [default_cache_size pragma].</p>

<h4>1.2.12 Incremental vacuum settings</h4>

<p>The two 4-byte big-endian integers at offsets 52 and 64 are used
to manage the [auto_vacuum] and [incremental_vacuum] modes.  ^If
the integer at offset 52 is zero then pointer-map (ptrmap) pages are
omitted from the database file and neither auto_vacuum nor
incremental_vacuum are supported.  ^If the integer at offset 52 is
non-zero then it is the page number of the largest root page in the
database file, the database file contain ptrmap pages, and the
mode must be either auto_vacuum or incremental_vacuum.  ^In this latter
case, the integer at offset 64 is true for incremental_vacuum and
false for auto_vacuum.  ^If the integer at offset 52 is zero then
the integer at offset 64 must also be zero.</p>

<h4>1.2.13 Text encoding</h4>








>




















|
>







|
<
<

|
<
|
|


















|







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
prepared statement is compiled against a specific version of the
database schema.  ^Whenever the database schema changes, the statement
must be reprepared.  ^Whenever a prepared statement runs, it first checks
the schema cookie to make sure the value is the same as when the statement
was prepared and if the schema cookie has changed, the statement aborts
in order to force the statement to be reprepared.</p>

<tcl>hd_fragment {schemaformat} {schema format number}</tcl>
<h4>1.2.10 Schema format number</h4>

<p>The schema format number is a 4-byte big-endian integer at offset 44.
The schema format number is similar to the file format read and write
version numbers at offsets 18 and 19 except that the schema format number
refers to the high-level SQL formatting rather than the low-level b-tree
formatting.  Four schema format numbers are currently defined:</p>

<ol>
<li value=1>Format 1 is understood by all versions of SQLite back to
version 3.0.0.</li>
<li value=2>Format 2 adds the ability of rows within the same table
to have a varying number of columns, in order to support the
[ALTER TABLE | ALTER TABLE ... ADD COLUMN] functionality.  Support for
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 
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.


^The [legacy_file_format pragma] can be used to cause SQLite
to create new database files using format 1.

The format version number can be made to default to 1 instead of 4 by
setting [SQLITE_DEFAULT_FILE_FORMAT]=1 at compile-time.
</p>

<h4>1.2.11 Suggested cache size</h4>

<p>The 4-byte big-endian signed integer at offset 48 is the suggested
cache size in pages for the database file.  The value is a suggestion
only and SQLite is under no obligation to honor it.  The absolute value
of the integer is used as the suggested size.  The suggested cache size
can be set using the [default_cache_size pragma].</p>

<h4>1.2.12 Incremental vacuum settings</h4>

<p>The two 4-byte big-endian integers at offsets 52 and 64 are used
to manage the [auto_vacuum] and [incremental_vacuum] modes.  ^If
the integer at offset 52 is zero then pointer-map (ptrmap) pages are
omitted from the database file and neither auto_vacuum nor
incremental_vacuum are supported.  ^If the integer at offset 52 is
non-zero then it is the page number of the largest root page in the
database file, the database file will contain ptrmap pages, and the
mode must be either auto_vacuum or incremental_vacuum.  ^In this latter
case, the integer at offset 64 is true for incremental_vacuum and
false for auto_vacuum.  ^If the integer at offset 52 is zero then
the integer at offset 64 must also be zero.</p>

<h4>1.2.13 Text encoding</h4>

366
367
368
369
370
371
372

373
374
375
376
377
378
379
380
in size contains no lock-byte page.  A database file larger than
1073741824 contains exactly one lock-byte page.
</p>

<p>The lock-byte page is set aside for use by the operating-system specific
[VFS] implementation in implementing the database file locking primitives.
^SQLite does not use the lock-byte page.  ^The SQLite core 

will never read or write, though operating-system specific [VFS] 
implementations may choose to read or write bytes on the lock-byte 
page according to the 
needs and proclivities of the underlying system.  The unix and win32
[VFS] implementations that come built into SQLite do not write to the
lock-byte page, but third-party VFS implementations for
other operating systems might.</p>








>
|







365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
in size contains no lock-byte page.  A database file larger than
1073741824 contains exactly one lock-byte page.
</p>

<p>The lock-byte page is set aside for use by the operating-system specific
[VFS] implementation in implementing the database file locking primitives.
^SQLite does not use the lock-byte page.  ^The SQLite core 
will never read or write the lock-byte page,
though operating-system specific [VFS] 
implementations may choose to read or write bytes on the lock-byte 
page according to the 
needs and proclivities of the underlying system.  The unix and win32
[VFS] implementations that come built into SQLite do not write to the
lock-byte page, but third-party VFS implementations for
other operating systems might.</p>

Changes to pages/index.in.
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99

</td>
<td width="20"></td><td bgcolor="#044a64" width="1"></td><td width="20"></td>
<td valign="top">
<h3>Current Status</h3>

<p><ul>
<li><a href="releaselog/3_7_9.html">Version 3.7.9</a>
of SQLite is recommended for all new development.
Upgrading from version 3.7.6.3, 3.7.7, 3.7.7.1, or 3.7.8 is optional.
Upgrading from all other SQLite versions is recommended.</li>
</ul></p>

<h3>Common Links</h3>

<p><ul>
<li> <a href="features.html">Features</a> </li>







|

|







83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99

</td>
<td width="20"></td><td bgcolor="#044a64" width="1"></td><td width="20"></td>
<td valign="top">
<h3>Current Status</h3>

<p><ul>
<li><a href="releaselog/3_7_10.html">Version 3.7.10</a>
of SQLite is recommended for all new development.
Upgrading from version 3.7.6.3, 3.7.7, 3.7.7.1, 3.7.8, or 3.7.9 is optional.
Upgrading from all other SQLite versions is recommended.</li>
</ul></p>

<h3>Common Links</h3>

<p><ul>
<li> <a href="features.html">Features</a> </li>
Changes to pages/lang.in.
577
578
579
580
581
582
583

584
585
586

587
588
589
590

591
592
593
594
595
596
597
598
599
600
601
</tcl>

<p>^The CREATE INDEX command consists of the keywords "CREATE INDEX" followed
by the name of the new index, the keyword "ON", the name of a previously
created table that is to be indexed, and a parenthesized list of names of
columns in the table that are used for the index key.</p>


<p>^Each column name can be followed by one of the "ASC" or "DESC" keywords
to indicate sort order.  ^The sort order may or may not be ignored depending
on the database file format.  ^The "legacy" file format ignores index

sort order.  ^The descending index file format takes index sort order
into account.  ^(Only copies of SQLite newer than [version 3.3.0] 
(released on 2006-01-10) are able to understand the newer descending
index file format and so for compatibility with older versions of

SQLite, the legacy file format is generated by default.)^  ^Use the
[legacy_file_format] pragma to modify this behavior and generate
databases that use the newer file format.  Future versions of SQLite
may begin to generate the newer file format by default.</p>

<p>^The COLLATE clause optionally following each column name defines a
collating sequence used for text entries in that column.
^The default collating
sequence is the collating sequence defined for that column in the
[CREATE TABLE] statement.  ^Or if no collating sequence is otherwise defined,
the built-in BINARY collating sequence is used.</p>







>


|
>
|


|
>
|
|
|
<







577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596

597
598
599
600
601
602
603
</tcl>

<p>^The CREATE INDEX command consists of the keywords "CREATE INDEX" followed
by the name of the new index, the keyword "ON", the name of a previously
created table that is to be indexed, and a parenthesized list of names of
columns in the table that are used for the index key.</p>

<tcl>hd_fragment {descidx} {descending indices} {descending index}</tcl>
<p>^Each column name can be followed by one of the "ASC" or "DESC" keywords
to indicate sort order.  ^The sort order may or may not be ignored depending
on the database file format, and in particular the [schema format number].
^The "legacy" schema format (1) ignores index
sort order.  ^The descending index schema format (4) takes index sort order
into account.  ^(Only copies of SQLite newer than [version 3.3.0] 
(released on 2006-01-10) are able to understand the newer descending
index file format.)^  For compatibility, version of SQLite between 3.3.0
and 3.7.9 use the legacy schema format by default.  The newer schema format is
used by default in version 3.7.10 and later.
^The [legacy_file_format pragma] can be used to change set the specific
behavior for any version of SQLite.</p>


<p>^The COLLATE clause optionally following each column name defines a
collating sequence used for text entries in that column.
^The default collating
sequence is the collating sequence defined for that column in the
[CREATE TABLE] statement.  ^Or if no collating sequence is otherwise defined,
the built-in BINARY collating sequence is used.</p>