Documentation Source Text

Check-in [74e8dbd8bf]
Login

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

Overview
Comment:Merge changes from the 3.12.0 branch. Add the "rowidtable.html" page. Add documentation for SQLITE_ENABLE_PREUPDATE_HOOK.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 74e8dbd8bf31a47cba0d750141a39ebe53f307e0
User & Date: drh 2016-04-01 14:57:30
Context
2016-04-01
16:08
Create a place-holder for a tutorial on the session extension. check-in: 24e5940ece user: drh tags: trunk
14:57
Merge changes from the 3.12.0 branch. Add the "rowidtable.html" page. Add documentation for SQLITE_ENABLE_PREUPDATE_HOOK. check-in: 74e8dbd8bf user: drh tags: trunk
01:05
Add a change log entry for the query planner enhancement backport to 3.9.3. check-in: cc1675f94a user: drh tags: branch-3.12
2016-03-30
14:26
Start a change log for version 3.13.0 check-in: 6633347c5b user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to pages/changes.in.

19
20
21
22
23
24
25








26
27
28
29
30
31
32
  set aChng($nChng) [list $date $desc $options]
  incr nChng
}

chng {2016-05-00 (3.13.0)} {
<li>Added the [session] extension to trunk
}









chng {2016-03-29 (3.12.0)} {
<p><b>Potentially Disruptive Change:</b>
<li>The [SQLITE_DEFAULT_PAGE_SIZE] is increased from 1024 to 4096.  
    The [SQLITE_DEFAULT_CACHE_SIZE] is changed from 2000 to -2000 so 
    the same amount of cache memory is used by default.
    See the application note on the







>
>
>
>
>
>
>
>







19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
  set aChng($nChng) [list $date $desc $options]
  incr nChng
}

chng {2016-05-00 (3.13.0)} {
<li>Added the [session] extension to trunk
}

chng {2016-04-01 (3.9.3)} {
<li>Backport a
    [https://www.sqlite.org/src/info/c648539b52ca28c0|simple query planner optimization]
    that allows the IS operator
    to drive an index on a LEFT OUTER JOIN.  No other changes from the
    [version 3.9.2] baseline.
}

chng {2016-03-29 (3.12.0)} {
<p><b>Potentially Disruptive Change:</b>
<li>The [SQLITE_DEFAULT_PAGE_SIZE] is increased from 1024 to 4096.  
    The [SQLITE_DEFAULT_CACHE_SIZE] is changed from 2000 to -2000 so 
    the same amount of cache memory is used by default.
    See the application note on the

Changes to pages/compile.in.

790
791
792
793
794
795
796














797
798
799
800
801
802
803
  supply a large chunk of memory from which all memory allocations are
  taken.
  The MEMSYS5 module rounds all allocations up to the next power
  of two and uses a first-fit, buddy-allocator algorithm
  that provides strong guarantees against fragmentation and breakdown
  subject to certain operating constraints.
}















COMPILE_OPTION {SQLITE_ENABLE_RBU} {
  Enable the code the implements the [RBU extension].
}

COMPILE_OPTION {SQLITE_ENABLE_RTREE} {
  This option causes SQLite to include support for the







>
>
>
>
>
>
>
>
>
>
>
>
>
>







790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
  supply a large chunk of memory from which all memory allocations are
  taken.
  The MEMSYS5 module rounds all allocations up to the next power
  of two and uses a first-fit, buddy-allocator algorithm
  that provides strong guarantees against fragmentation and breakdown
  subject to certain operating constraints.
}

COMPILE_OPTION {SQLITE_ENABLE_PREUPDATE_HOOK} {
  This option enables 
  [sqlite3_preupdate_hook|several new APIs] that provide callbacks
  prior to any change to a [rowid table].  The callbacks can be used
  to record the state of the row before the change occurs.
  <p>The action of the preupdate hook is similar to the
  [sqlite3_update_hook|update hook] except that the callback is
  invoked before the change, not afterwards, and the preupdate
  hook interfaces are omitted unless this compile-time option is
  used.
  <p>The preupdate hook interfaces were originally added to
  support the [session] extension.
}

COMPILE_OPTION {SQLITE_ENABLE_RBU} {
  Enable the code the implements the [RBU extension].
}

COMPILE_OPTION {SQLITE_ENABLE_RTREE} {
  This option causes SQLite to include support for the

Changes to pages/fileformat2.in.

437
438
439
440
441
442
443
444
445
446
447
448
449
450
451

<p>^The number of freelist pages is stored as a 4-byte big-endian integer
in the database header at an offset of 36 from the beginning of the file.
^The database header also stores the page number of the first freelist trunk
page as a 4-byte big-endian integer at an offset of 32 from the beginning
of the file.</p>

<tcl>hd_fragment btree {B*-Trees}</tcl>
<h3>1.5 B-tree Pages</h3>

<p>The b-tree algorithm provides key/data storage with unique and
ordered keys on page-oriented storage devices.
For background information on b-trees, see
Knuth, <u>The Art Of Computer Programming</u>, Volume 3 "Sorting
and Searching", pages 471-479.  Two kinds of b-trees are used by







|







437
438
439
440
441
442
443
444
445
446
447
448
449
450
451

<p>^The number of freelist pages is stored as a 4-byte big-endian integer
in the database header at an offset of 36 from the beginning of the file.
^The database header also stores the page number of the first freelist trunk
page as a 4-byte big-endian integer at an offset of 32 from the beginning
of the file.</p>

<tcl>hd_fragment btree {B*-Trees} {B-tree}</tcl>
<h3>1.5 B-tree Pages</h3>

<p>The b-tree algorithm provides key/data storage with unique and
ordered keys on page-oriented storage devices.
For background information on b-trees, see
Knuth, <u>The Art Of Computer Programming</u>, Volume 3 "Sorting
and Searching", pages 471-479.  Two kinds of b-trees are used by

Added pages/rowidtable.in.























































































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
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
<title>Rowid Tables</title>
<tcl>
hd_keywords {rowid table} {rowid tables}
</tcl>
<h1 align="center">Rowid Tables</h1>

<h2>1.0 Definition</h2>

<p>A "rowid table" is any table in an SQLite schema that
<ul>
<li>is <em>not</em> a [virtual table], and
<li>is <em>not</em> a [WITHOUT ROWID] table.
</ul>
Most tables in a typical SQLite database schema are rowid tables.

<p>Rowid tables are distingished by the fact that they all have
a unique, non-NULL, signed 64-bit integer [rowid] that is used as
the access key for the data in the underlying [B-tree] storage engine.

<h2>2.0 Quirks</h2>

<ul>
<li><p>
The [PRIMARY KEY] of a rowid table (if there is one) is usually not the
true primary key for the table, in the sense that it is not the unique
key used by the underlying [B-tree] storage engine.  The exception to
this rule is when the rowid table declares an [INTEGER PRIMARY KEY].
In the exception, the INTEGER PRIMARY KEY becomes an alias for the 
[rowid].

<li><p>
The true primary key for a rowid table (the value that is used as the
key to look up rows in the underlying [B-tree] storage engine)
is the [rowid].

<li><p>
The PRIMARY KEY constraint for a rowid table (as long as it is not
the true primary key or INTEGER PRIMARY KEY) is really the same thing
as a [UNIQUE constraint].  Because it is not a true primary key,
columns of the PRIMARY KEY are allowed to be NULL, in violation of
all SQL standards.

<li><p>
The [rowid] of a rowid table can be accessed (or changed) by reading or
writing to any of the "rowid" or "oid" or "_rowid_" columns.  Except,
if there is a declared columns in the table that use those
special names, then those names refer to the declared columns, not to
the underlying [rowid].

<li><p>
Access to records via [rowid] is highly optimized and very fast.

<li><p>
If the [rowid] is not alised by [INTEGER PRIMARY KEY] then it is not
persistent and might change.  In particular the [VACUUM] command will
change rowids for tables that do not declare an INTEGER PRIMARY KEY.
Therefore, applications should not normally access the rowid directly,
but instead use an INTEGER PRIMARY KEY.

<li><p>
In the underlying [file format], each rowid is stored as a
[variable-length integer].  That means that small non-negative
rowid values take up less disk space than large or negative
rowid values.

<li><p>
All of the complications above (and others not mentioned here)
arise from the need to preserve backwards
compatibility to the tens of billions of SQLite database files in
circulation.  In a perfect world, there would be no such thing as a "rowid"
and all tables would following the standard semantics implemented as
[WITHOUT ROWID] tables, only without the extra "WITHOUT ROWID" keywords.
Unfortunately, life is messy.  The designer of SQLite offers his
sincere apology for the current mess.
</ul>