Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Changes In Branch branch-3.12
Excluding Merge-Ins
This is equivalent to a diff from
f343e93d94
to 7c96132d65
2016-05-05
| | |
13:33 |
|
(check-in: d545903147 user: drh tags: trunk)
|
13:28 |
|
(Leaf
check-in: 7c96132d65 user: drh tags: branch-3.12)
|
2016-04-25
| | |
21:58 |
|
(check-in: 44656180a0 user: drh tags: branch-3.12)
|
2016-04-01
| | |
01:05 |
|
(check-in: cc1675f94a user: drh tags: branch-3.12)
|
2016-03-30
| | |
14:26 |
|
(check-in: 6633347c5b user: drh tags: trunk)
|
2016-03-29
| | |
22:30 |
|
(check-in: f343e93d94 user: drh tags: trunk)
|
22:27 |
|
(check-in: 454218b20d user: drh tags: trunk)
|
| | |
Changes to pages/34to35.in.
︙ | | |
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
|
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
|
-
+
|
be required to upgrade to newer versions.
}
HEADING 1 {Overview Of Changes}
PARAGRAPH {
A quick enumeration of the changes in SQLite version 3.5.0
is provide here. Subsequent sections will describe these
is provided here. Subsequent sections will describe these
changes in more detail.
}
PARAGRAPH {
<ol>
<li>The OS interface layer has been completely reworked:
<ol type="a">
<li>The undocumented <b>sqlite3_os_switch()</b> interface has
|
︙ | | |
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
|
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
|
-
+
|
PARAGRAPH {
The new OS interface for SQLite is built around an object named
[sqlite3_vfs]. The "vfs" stands for "Virtual File System".
The sqlite3_vfs object is basically a structure containing pointers
to functions that implement the primitive disk I/O operations that
SQLite needs to perform in order to read and write databases.
In this article, we will often refer a sqlite3_vfs objects as a "VFS".
In this article, we will often refer to an sqlite3_vfs objects as a "VFS".
}
PARAGRAPH {
SQLite is able to use multiple VFSes at the same time. Each
individual database connection is associated with just one VFS.
But if you have multiple database connections, each connection
can be associated with a different VFS.
|
︙ | | |
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
|
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
|
-
+
-
+
|
}
CODE {
int sqlite3_vfs_register(sqlite3_vfs*, int makeDflt);
}
PARAGRAPH {
Applications can call sqlite3_vfs_register at any time, though of course
Applications can call sqlite3_vfs_register() at any time, though of course
a VFS needs to be registered before it can be used. The first argument
is a pointer to a customized VFS object that the application has prepared.
The second argument is true to make the new VFS the default VFS so that
it will be used by the legacy [sqlite3_open()] and [sqlite3_open16()] APIs.
If the new VFS is not the default, then you will probably have to use
the new [sqlite3_open_v2()] API to use it. Note, however, that if
a new VFS is the only VFS known to SQLite (if SQLite was compiled without
its usual default VFS or if the precompiled default VFS was removed
using [sqlite3_vfs_unregister()]) then the new VFS automatic becomes the
using [sqlite3_vfs_unregister()]) then the new VFS automatically becomes the
default VFS regardless of the makeDflt argument to [sqlite3_vfs_register()].
}
PARAGRAPH {
Standard builds include the default "unix" or "win32" VFSes.
But if you use the -DOS_OTHER=1 compile-time option, then SQLite is
built without a default VFS. In that case, the application must
|
︙ | | |
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
|
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
|
-
+
|
PARAGRAPH {
Once a VFS has been registered, it should never be modified. If
a change in behavior is required, a new VFS should be registered.
The application could, perhaps, use [sqlite3_vfs_find()] to locate
the old VFS, make a copy of the old VFS into a new [sqlite3_vfs]
object, make the desired modifications to the new VFS, unregister
the old VFS, the register the new VFS in its place. Existing
the old VFS, then register the new VFS in its place. Existing
database connections would continue to use the old VFS even after
it is unregistered, but new database connections would use the
new VFS.
}
HEADING 3 {The VFS Object}
|
︙ | | |
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
|
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
|
-
+
-
+
|
PARAGRAPH {
The pAppData pointer is unused by the SQLite core. The pointer is
available to store auxiliary information that a VFS information might
want to carry around.
}
PARAGRAPH {
The remaining fields of the [sqlite3_vfs] object all store pointer
The remaining fields of the [sqlite3_vfs] object all store pointers
to functions that implement primitive operations. We call these
"methods". The first methods, xOpen, is used to open files on
"methods". The first method, xOpen, is used to open files on
the underlying storage media. The result is an [sqlite3_file]
object. There are additional methods, defined by the [sqlite3_file]
object itself that are used to read and write and close the file.
The additional methods are detailed below. The filename is in UTF-8.
SQLite will guarantee that the zFilename string passed to
xOpen() is a full pathname as generated by xFullPathname() and
that the string will be valid and unchanged until xClose() is
|
︙ | | |
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
|
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
|
-
+
|
ORDER BY or GROUP BY clause. Both TEMP_DB and TRANSIENT_DB databases
are private and are deleted automatically. TEMP_DB databases last
for the duration of the database connection. TRANSIENT_DB databases
last only for the duration of a single SQL statement.
}
PARAGRAPH {
The xDelete method is used delete a file. The name of the file is
The xDelete method is used to delete a file. The name of the file is
given in the second parameter. The filename will be in UTF-8.
The VFS must convert the filename into whatever character representation
the underlying operating system expects. If the syncDir parameter is
true, then the xDelete method should not return until the change
to the directory contents for the directory containing the
deleted file have been synced to disk in order to ensure that the
file does not "reappear" if a power failure occurs soon after.
|
︙ | | |
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
|
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
|
-
+
-
+
|
the xRandomness method on the default VFS is used. The xRandomness
methods on other VFSes are never accessed by SQLite.
The xRandomness routine requests that nByte bytes of randomness
be written into zOut. The routine returns the actual number of
bytes of randomness obtained. The quality of the randomness so obtained
will determine the quality of the randomness generated by built-in
SQLite functions such as random() and randomblob(). SQLite also
uses its PRNG to generate temporary file names.. On some platforms
uses its PRNG to generate temporary file names. On some platforms
(ex: Windows) SQLite assumes that temporary file names are unique
without actually testing for collisions, so it is important to have
good-quality randomness even if the random() and randomblob()
functions are never used.
}
PARAGRAPH {
The xSleep method is used to suspend the calling thread for at
least the number of microseconds given. This method is used to
implement the [sqlite3_sleep()] and [sqlite3_busy_timeout()] APIs.
In the case of [sqlite3_sleep()] the xSleep method of the default
VFS is always used. If the underlying system does not have a
microsecond resolution sleep capability, then the sleep time should
be rounded up. xSleep returns this rounded-up value.
}
PARAGRAPH {
The xCurrentTime method finds the current time and date and writes
the result as double-precision floating point value into pointer
the result as a double-precision floating point value into pointer
provided by the second parameter. The time and date is in
coordinated universal time (UTC) and is a fractional Julian day number.
}
HEADING 3 {The Open File Object}
PARAGRAPH {
|
︙ | | |
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
|
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
|
-
+
-
+
-
+
-
+
-
+
|
The locking level is specified as the second argument to both xLock
and xUnlock. The xLock method increases the locking level to the
specified locking level or higher. The xUnlock method decreases the
locking level to no lower than the level specified.
[SQLITE_LOCK_NONE] means that the file is unlocked. [SQLITE_LOCK_SHARED]
gives permission to read the file. Multiple database connections can
hold [SQLITE_LOCK_SHARED] at the same time.
[SQLITE_LOCK_RESERVED] is like [SQLITE_LOCK_SHARED] in that its is permission
[SQLITE_LOCK_RESERVED] is like [SQLITE_LOCK_SHARED] in that it is permission
to read the file. But only a single connection can hold a reserved lock
at any point in time. The [SQLITE_LOCK_PENDING] is also permission to
read the file. Other connections can continue to read the file as well,
but no other connection is allowed to escalate a lock from none to shared.
[SQLITE_LOCK_EXCLUSIVE] is permission to write on the file. Only a single
connection can hold an exclusive lock and no other connection can hold
any lock (other than "none") while one connection is hold an exclusive
any lock (other than "none") while one connection holds an exclusive
lock. The xLock returns [SQLITE_OK] on success, [SQLITE_BUSY] if it
is unable to obtain the lock, or [SQLITE_IOERR_RDLOCK] if something else
goes wrong. The xUnlock method returns [SQLITE_OK] on success and
[SQLITE_IOERR_UNLOCK] for problems.
}
PARAGRAPH {
The xCheckReservedLock method checks to see if another connection or
The xCheckReservedLock() method checks to see if another connection or
another process is currently holding a reserved, pending, or exclusive
lock on the file. It returns true or false.
}
PARAGRAPH {
The xFileControl() method is a generic interface that allows custom
VFS implementations to directly control an open file using the
(new and experimental)
[sqlite3_file_control()] interface. The second "op" argument
is an integer opcode. The third
is an integer opcode. The third
argument is a generic pointer which is intended to be a pointer
to a structure that may contain arguments or space in which to
write return values. Potential uses for xFileControl() might be
functions to enable blocking locks with timeouts, to change the
locking strategy (for example to use dot-file locks), to inquire
about the status of a lock, or to break stale locks. The SQLite
core reserves opcodes less than 100 for its own use.
A [SQLITE_FCNTL_LOCKSTATE | list of opcodes] less than 100 is available.
Applications that define a custom xFileControl method should use opcodes
greater than 100 to avoid conflicts.
}
PARAGRAPH {
The xSectorSize returns the "sector size" of the underlying
non-volatile media. A "sector" is defined as the smallest unit of
storage that can be written without disturbing adjacent storage.
On a disk drive the "sector size" has until recently been 512 bytes,
though there is a push to increase this value to 4KiB. SQLite needs
to know the sector size so that it can write a full sector at a
time, and thus avoid corrupting adjacent storage space if a power
lose occurs in the middle of a write.
loss occurs in the middle of a write.
}
PARAGRAPH {
The xDeviceCharacteristics method returns an integer bit vector that
defines any special properties that the underlying storage medium might
have that SQLite can use to increase performance. The allowed return
is the bit-wise OR of the following values:
|
︙ | | |
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
|
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
|
-
+
|
sqlite3_int64 iThreshold
);
}
PARAGRAPH {
The [sqlite3_memory_alarm] routine is used to register
a callback on memory allocation events.
This routine registers or clears a callbacks that fires when
This routine registers or clears a callback that fires when
the amount of memory allocated exceeds iThreshold. Only
a single callback can be registered at a time. Each call
to [sqlite3_memory_alarm()] overwrites the previous callback.
The callback is disabled by setting xCallback to a NULL
pointer.
}
|
︙ | | |
Changes to pages/35to36.in.
︙ | | |
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
|
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
|
-
+
|
<li><p>The signature of the xCheckReservedLock method on [sqlite3_io_methods]
has been modified so that it returns an [error code] and stores its
boolean result into an integer pointed to by a parameter. In
association with this change, a new extended error code
[SQLITE_IOERR_CHECKRESERVEDLOCK] has been added.</p></li>
<li><p>When SQLite is ported to new operation systems (operating systems
<li><p>When SQLite is ported to new operating systems (operating systems
other than Unix, Windows, and OS/2 for which ports are provided together
with the core)
two new functions, [sqlite3_os_init()] and [sqlite3_os_end()], must
be provided as part of the port.</p></li>
<li><p>The way in which the IN and NOT IN operators handle NULL values
in their right-hand expressions has been brought into compliance with
|
︙ | | |
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
|
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
|
-
+
-
+
|
through [sqlite3_status()].</p></li>
<li><p>The [sqlite3_initialize()] interface can be called to explicitly
initialize the SQLite subsystem. The [sqlite3_initialize()] interface is
called automatically when invoking certain interfaces so the use of
[sqlite3_initialize()] is not required, but it is recommended.</p></li>
<li><p>The [sqlite3_shutdown()] interface causes SQLite release any
<li><p>The [sqlite3_shutdown()] interface causes SQLite to release any
system resources (memory allocations, mutexes, open file handles)
that it might have been allocated by [sqlite3_initialize()].</p></li>
that might have been allocated by [sqlite3_initialize()].</p></li>
<li><p>The [sqlite3_next_stmt()] interface allows an application to discover
all [prepared statements] associated with a [database connection].</p></li>
<li><p>Added the [page_count] PRAGMA for returning the size of the underlying
database file in pages.</p></li>
<li><p>Added a new [rtree | R*Tree index extension].</p></li>
</ol>
}
</tcl>
|
Changes to pages/appfileformat.in.
︙ | | |
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
|
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
|
-
+
|
</ul>
<p>We make a distinction between a "file format" and an "application format".
A file format is used to store a single object. So, for example, a GIF or
JPEG file stores a single image, and an XHTML file stores text,
so those are "file formats" and not "application formats". An EPUB file,
in contrast, stores both text and images (as contained XHTML and GIF/JPEG
files) and so it is considered a "application format". This article is
files) and so it is considered an "application format". This article is
about "application formats".
<p>The boundary between a file format and an application format is fuzzy.
This article calls JPEG a file format, but for an image editor, JPEG
might be considered the application format. Much depends on context.
For this article, let us say that a file format stores a single object
and an application format stores many different objects and their relationships
|
︙ | | |
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
|
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
|
-
+
|
as an equivalent ZIP archive, and it has the advantage
of being able to update individual "files" without rewriting
the entire document.
<p>
But an SQLite database is not limited to a simple key/value structure
like a pile-of-files database. An SQLite database can have dozens
or hundreds or thousands of different of tables, with dozens or
or hundreds or thousands of different tables, with dozens or
hundreds or thousands of fields per table, each with different datatypes
and constraints and particular meanings, all cross-referencing each other,
appropriately and automatically indexed for rapid retrieval,
and all stored efficiently and compactly in a single disk file.
And all of this structure is succinctly documented for humans
by the SQL schema.
|
︙ | | |
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
|
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
|
-
+
|
and avoid time spent "heads down" fiddling with low-level file
formatting details.
<p>A pile-of-files format can be viewed as a key/value database.
A key/value database is better than no database at all.
But without transactions or indices or a high-level query language or
a proper schema,
it much harder and more error prone to use a key/value database than
it is much harder and more error prone to use a key/value database than
a relational database.
<li><p><b>Accessible Content.</b>
Information held in an SQLite database file is accessible using
commonly available open-source command-line tools - tools that
are installed by default on Mac and Linux systems and that are
freely available as a self-contained EXE file on Windows.
|
︙ | | |
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
|
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
|
-
+
|
(a single page).
<p>SQLite also supports continuous update.
Instead of collecting changes in memory and then writing
them to disk only on a File/Save action, changes can be written back to
the disk as they occur. This avoids loss of work on a system crash or
power failure. An [automated undo/redo stack], managed using triggers,
can be kept in the on-disk database, meaning that undo/redo can occur
can be kept in the on-disk database, meaning that undo/redo can occur
across session boundaries.
<li><p><b>Easily Extensible.</b>
As an application grows, new features can be added to an
SQLite application file format simply by adding new tables to the schema
or by adding new columns to existing tables. Adding columns or tables
does not change the meaning of prior queries, so with a
|
︙ | | |
Changes to pages/atomiccommit.in.
︙ | | |
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
|
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
|
-
+
|
in the gaps.</p>
<tcl>hd_fragment completesectors</tcl>
<h3>6.1 Always Journal Complete Sectors</h3>
<p>When the original content of a database page is written into
the rollback journal (as shown in <a href="#section_3_5">section 3.5</a>),
SQLite always writes a complete sectors worth of data, even if the
SQLite always writes a complete sector of data, even if the
page size of the database is smaller than the sector size.
Historically, the sector size in SQLite has been hard coded to 512
bytes and since the minimum page size is also 512 bytes, this has never
been an issue. But beginning with SQLite version 3.3.14, it is possible
for SQLite to use mass storage devices with a sector size larger than 512
bytes. So, beginning with version 3.3.14, whenever any page within a
sector is written into the journal file, all pages in that same sector
|
︙ | | |
1107
1108
1109
1110
1111
1112
1113
1114
1115
1116
1117
1118
1119
1120
1121
|
1107
1108
1109
1110
1111
1112
1113
1114
1115
1116
1117
1118
1119
1120
1121
|
-
+
|
<a href="pragma.html#pragma_journal_mode">journal_mode</a> PRAGMA.
For example:</p>
<blockquote><pre>
PRAGMA journal_mode=PERSIST;
</per></blockquote>
<p>The use of persistent journal mode provide a noticeable performance
<p>The use of persistent journal mode provides a noticeable performance
improvement on many systems. Of course, the drawback is that the
journal files remain on the disk, using disk space and cluttering
directories, long after the transaction commits. The only safe way
to delete a persistent journal file is to commit a transaction
with journaling mode set to DELETE:</p>
<blockquote><pre>
|
︙ | | |
1236
1237
1238
1239
1240
1241
1242
1243
1244
1245
1246
1247
1248
1249
1250
|
1236
1237
1238
1239
1240
1241
1242
1243
1244
1245
1246
1247
1248
1249
1250
|
-
+
|
dot-file locks or vice versa.</p>
<tcl>hd_fragment fsync</tcl>
<h3>9.2 Incomplete Disk Flushes</h3>
<p>SQLite uses the fsync() system call on Unix and the FlushFileBuffers()
system call on w32 in order to sync the file system buffers onto disk
oxide as shown in <a href="#section_3_7">step 3.7</a> and
oxide as shown in <a href="#section_3_7">step 3.7</a> and
<a href="#section_3_10">step 3.10</a>. Unfortunately, we have received
reports that neither of these interfaces works as advertised on many
systems. We hear that FlushFileBuffers() can be completely disabled
using registry settings on some Windows versions. Some historical
versions of Linux contain versions of fsync() which are no-ops on
some filesystems, we are told. Even on systems where
FlushFileBuffers() and fsync() are said to be working, often
|
︙ | | |
Changes to pages/autoinc.in.
︙ | | |
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
|
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
|
-
+
|
^If a column has the type INTEGER PRIMARY KEY AUTOINCREMENT then a slightly
different ROWID selection algorithm is used.
^The ROWID chosen for the new row is at least one larger than the largest ROWID
that has ever before existed in that same table. ^If the table has never
before contained any data, then a ROWID of 1 is used. ^If the table
has previously held a row with the largest possible ROWID, then new INSERTs
are not allowed and any attempt to insert a new row will fail with an
SQLITE_FULL error. ^(Only ROWID values from previously transactions that
SQLITE_FULL error. ^(Only ROWID values from previous transactions that
were committed are considered. ROWID values that were rolled back
are ignored and can be reused.)^
</p>
<p>
^SQLite keeps track of the largest ROWID that a table has ever held using
an [internal table] named "[sqlite_sequence]".
|
︙ | | |
Changes to pages/backup.in.
︙ | | |
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
|
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
|
-
+
-
+
-
+
|
** pTo accordingly. */
pFrom = (isSave ? pInMemory : pFile);
pTo = (isSave ? pFile : pInMemory);
/* Set up the backup procedure to copy from the "main" database of
** connection pFile to the main database of connection pInMemory.
** If something goes wrong, pBackup will be set to NULL and an error
** code and message left in connection pTo.
** code and message left in connection pTo.
**
** If the backup object is successfully created, call backup_step()
** to copy data from pFile to pInMemory. Then call backup_finish()
** to release resources associated with the pBackup object. If an
** error occurred, then an error code and message will be left in
** error occurred, then an error code and message will be left in
** connection pTo. If no error occurred, then the error code belonging
** to pTo is set to SQLITE_OK.
*/
pBackup = sqlite3_backup_init(pTo, "main", pFrom, "main");
if( pBackup ){
(void)sqlite3_backup_step(pBackup, -1);
(void)sqlite3_backup_finish(pBackup);
}
rc = sqlite3_errcode(pTo);
}
/* Close the database connection opened on database file zFilename
** and return the result of this function. */
(void)sqlite3_close(pFile);
return rc;
}
}
</tcl>
<p>
The C function to the right demonstrates of one of the simplest,
The C function to the right demonstrates one of the simplest,
and most common, uses of the backup API: loading and saving the contents
of an in-memory database to a file on disk. The backup API is used as
follows in this example:
<ol>
<li>Function [sqlite3_backup_init()] is called to create an [sqlite3_backup]
object to copy data between the two databases (either from a file and
|
︙ | | |
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
|
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
|
-
+
|
function is sleeping, then SQLite detects this and usually restarts the
backup process when sqlite3_backup_step() is next called. There is one
exception to this rule: If the source database is not an in-memory database,
and the write is performed from within the same process as the backup
operation and uses the same database handle (pDb), then the destination
database (the one opened using connection pFile) is automatically updated
along with the source. The backup process may then be continued after the
xSleep() call returns as if nothing had happened.
sqlite3_sleep() call returns as if nothing had happened.
<p>
Whether or not the backup process is restarted as a result of writes to
the source database mid-backup, the user can be sure that when the backup
operation is completed the backup database contains a consistent and
up-to-date snapshot of the original. However:
|
︙ | | |
Changes to pages/btreemodule.in.
︙ | | |
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
|
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
|
-
+
|
[Tr] <td> L****** <td> Requirement statements specifying some details of the internal workings of the B-Tree module.
</table>
[h2 "Glossary"]
<table id=glossary>
[Glossary "Balance-Siblings Algorithm" {
The balance-siblings algorithm is one of four algorithms that may be used
The balance-siblings algorithm is one of four algorithms that may be
used to redistribute data within a b-tree structure after an insert or
delete operation that causes a b-tree node to become overfull or underfull.
See section <cite>balance_siblings</cite> for details.
}]
[Glossary "B-Tree Cursor" {
<span class=todo>Define this.
}]
|
︙ | | |
Changes to pages/changes.in.
︙ | | |
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
|
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
|
-
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
|
http://www.sqlite.org/src/timeline?t=release</a>.
See the [chronology] a succinct listing of releases.
</p>
<tcl>
set nChng 0
proc chng {date desc {options {}}} {
global nChng aChng
global nChng aChng xrefChng
set aChng($nChng) [list $date $desc $options]
set xrefChng($date) $nChng
incr nChng
}
chng {2016-04-18 (3.12.2)} {
<li>Fix a backwards compatibility problem in version 3.12.0 and 3.12.1:
Columns declared as <tt>"INTEGER" PRIMARY KEY</tt> (with quotes around
the datatype keyword) where not being recognized as an
[INTEGER PRIMARY KEY], which resulted in an incompatible database file.
Ticket [https://www.sqlite.org/src/info/7d7525cb01b68|7d7525cb01b68]
<li>Fix a bug (present since [version 3.9.0]) that can cause the [DELETE]
operation to miss rows if [PRAGMA reverse_unordered_selects] is turned on.
Ticket [https://www.sqlite.org/src/info/a306e56ff68b8fa5|a306e56ff68b8fa5]
<li>Fix a bug in the code generator that can causes incorrect results if
two or more [virtual tables] are joined and the virtual table used in
outer loop of the join has an [IN operator] constraint.
<li>Correctly interpret negative "PRAGMA cache_size" values when determining
the cache size used for sorting large amounts of data.
<p><b>Hashes:</b>
<li>SQLITE_SOURCE_ID: "2016-04-18 17:30:31 92dc59fd5ad66f646666042eb04195e3a61a9e8e"
<li>SHA1 for sqlite3.c: de5a5898ebd3a3477d4652db143746d008b24c83
} {patchagainst 1 patchagainst 3}
chng {2016-04-08 (3.12.1)} {
<li>Fix a boundary condition error introduced by version 3.12.0
that can result in a crash during heavy [SAVEPOINT] usage.
Ticket [https://www.sqlite.org/src/info/7f7f8026eda38|7f7f8026eda38].
<li>Fix [views] so that they inherit column datatypes from the
table that they are defined against, when possible.
<li>Fix the query planner so that IS and IS NULL operators are able
to drive an index on a LEFT OUTER JOIN.
<p><b>Hashes:</b>
<li>SQLITE_SOURCE_ID: "2016-04-08 15:09:49 fe7d3b75fe1bde41511b323925af8ae1b910bc4d"
<li>SHA1 for sqlite3.c: ebb18593350779850e3e1a930eb84a70fca8c1d1
} {patchagainst 2}
chng {2016-04-01 (3.9.3)} {
<li>Backport a 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/cli.in.
︙ | | |
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
|
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
|
-
+
|
sqlite> (((select * from tbl1;)))
hello!|10
goodbye|20
sqlite>
}
</tcl>
<p>You can terminate the sqlite3 program by typing your systems
<p>You can terminate the sqlite3 program by typing your system
End-Of-File character (usually a Control-D). Use the interrupt
character (usually a Control-C) to stop a long-running SQL statement.</p>
<p>Make sure you type a semicolon at the end of each SQL command!
The sqlite3 program looks for a semicolon to know when your SQL command is
complete. If you omit the semicolon, sqlite3 will give you a
continuation prompt and wait for you to enter more text to be
|
︙ | | |
Changes to pages/crew.in.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
-
+
|
<title>SQLite Developers</title>
<h2>The SQLite Development Team</h2>
<img src="images/drh1.jpg" align="left" hspace="25" vspace="0">
<p>
<b>D. Richard Hipp
</b> began the SQLite project in on 2000-May-29
</b> began the SQLite project on 2000-05-29
and continues to serve as the project architect. Richard was born,
lives, and works in
[http://en.wikipedia.org/wiki/Charlotte,_North_Carolina | Charlotte, North Carolina.] He holds degrees from
[http://www.gatech.edu/ | Georgia Tech] (MSEE, 1984) and
[http://www.duke.edu/ | Duke University] (PhD, 1992) and is
the founder of the consulting firm
[http://www.hwaci.com/ | Hwaci].</p>
|
︙ | | |
Changes to pages/dev.in.
1
2
3
4
5
6
7
8
9
|
1
2
3
4
5
6
7
8
9
|
-
+
|
<title>SQLite Developer Links</title>
<h1 align="center">Developer Resources</h1>
<ul>
<li> <a href="http://www.sqlite.org/src/wiki/name=Bug+Reports">Report A Bug</a>
<li> <a href="http://www.sqlite.org/src/wiki?name=Bug+Reports">Report A Bug</a>
<li> <a href="http://www.sqlite.org/src/timeline">Timeline</a>
<li> <a href="http://www.sqlite.org/src/dir">Browse Historical Source Files</a>
<li> <a href="http://www.sqlite.org/docsrc">Documentation Source Text</a>
</ul>
|
Changes to pages/howtocorrupt.in.
︙ | | |
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
|
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
|
-
+
-
-
+
+
+
+
+
+
+
-
+
-
+
+
+
+
+
|
then the two processes will be talking to different database files with
the same name. (Note that this is only possible on Posix and Posix-like
systems that permit a file to be unlinked while it is still open for
reading and writing. Windows does not allow this to occur.)
Since rollback journals and WAL files are based on the name of the database
file, the two different database files will share the same rollback
journal or WAL file. A rollback or recovery for one of the databases
might use content from the other database, resulting in corruption.</p>
might use content from the other database, resulting in corruption.
<p>A similar problem occurs if a database file is renamed while it is
A similar problem occurs if a database file is renamed while it is
opened and a new file is created with the old name.</p>
<p>In other words, unlinking or renaming an open database file
results in behavior that is undefined and probably undesirable.</p>
<p>Beginning with SQLite [version 3.7.17], the unix OS interface will
send SQLITE_WARNING messages to the [error log] if a database file is unlinked
while it is still in use.</p>
<tcl>hd_fragment alias {database filename aliasing}</tcl>
<h3>2.5 Multiple links to the same file</h3>
<p>If a single database file has multiple links (either hard or soft links)
then that is just another way of saying that the file has multiple names.
If two or more processes open the database using different names, then
they will use different rollback journals and WAL files. That means that
if one process crashes, the other process will be unable to recover the
transaction in progress because it will be looking in the wrong place
for the appropriate journal.</p>
<p>In other words, opening and using a database file that has two or
more names results in behavior that is undefined and probably undesirable.</p>
<p>Beginning with SQLite [version 3.7.17], the unix OS interface will
send SQLITE_WARNING messages to the [error log] if a database file has
multiple hard links. As of this writing, SQLite still does not yet detect
multiple hard links.</p>
or warn about the use of database files through soft links.</p>
<p>Beginning with SQLite [version 3.10.0], the unix OS interface will
attempt to resolve symbolic links and open the database file by its
canonical name. Prior to version 3.10.0, opening a database file
through a symbolic link was similar to opening a database file
that had multiple hard links and resulted in undefined behavior.</p>
<h2>3.0 Failure to sync</h2>
<p>In order to guarantee that database files are always consistent, SQLite
will occasionally ask the operating system to flush all pending writes to
persistent storage then wait for that flush to complete. This is
accomplished using the <tt>fsync()</tt> system call under unix and
|
︙ | | |
Changes to pages/index.in.
︙ | | |
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
|
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
|
-
+
|
</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_12_0.html">Version 3.12.0</a>
<li><a href="releaselog/3_12_2.html">Version 3.12.2</a>
of SQLite is recommended for all new development.
</li>
</ul></p>
<h3>Common Links</h3>
<p><ul>
|
︙ | | |
Changes to pages/news.in.
︙ | | |
14
15
16
17
18
19
20
21
22
23
24
25
26
27
|
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
|
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
|
hd_puts "<h3>$date - $title</h3>"
regsub -all "\n( *\n)+" $text "</p>\n\n<p>" txt
regsub -all {[Tt]icket #(\d+)} $txt \
{<a href="http://www.sqlite.org/cvstrac/tktview?tn=\1">\0</a>} txt
hd_resolve "<blockquote>$txt</blockquote>"
hd_puts "<hr width=\"50%\">"
}
newsitem {2016-04-18} {Release 3.12.2} {
<p>Yikes! The 3.12.0 and 3.12.1 releases contain a backwards compatibility bug!
Tables that declare a column with type <tt>"INTEGER" PRIMARY KEY</tt>
(where the datatype name INTEGER is quoted) generate an incompatible
database file. The mistake came about because the developers have never
thought to put a typename in quotes before, and so there was no documentation
of that capability nor any tests. (There are tests now, though, of course.)
Instances of quoting the datatype name are probably infrequent in the wild,
so we do not expect the impact of this bug to be too severe.
Upgrading is still strongly recommended.
<p>Fixes for three other minor issues were included in this patch release.
The other issues would have normally been deferred until the next scheduled
release, but since a patch release is being issued anyhow, they might as
well be included.
}
newsitem {2016-04-08} {Release 3.12.1} {
<p>SQLite [version 3.12.1] is an emergency patch release to address a
[https://www.sqlite.org/src/info/7f7f8026eda38|crash bug] that snuck
into [version 3.12.0]. Upgrading from version 3.12.0 is highly
recommended.
<p>Another minor problem involving datatypes on [view] columns, and
a query planner deficiency are fixed at the same time. These two
issues did not justify a new release on their own, but since a release
is being issued to deal with the crash bug, we included these other
fixes for good measure.
}
newsitem {2016-03-29} {Release 3.12.0} {
<p>SQLite [version 3.12.0] is a regularly scheduled maintenance release.
A notable change in this release is an
[increase in the default page size] for newly created database files.
There are also various performance improvements.
See the [version 3.12.0|change log] for details.
|
︙ | | |
Changes to pages/not-found.in.
1
2
3
4
5
6
7
8
9
|
1
2
3
4
5
6
7
8
9
|
-
+
|
<title>Page Not Found</title>
<h1 align='center'>Page Not found</h1>
<h1 align='center'>Page Not Found</h1>
<p>The document you seek is not available.
Please consider one of the links below
or use the Search feature on the right-hand side of the
menu bar above.
<p><ul>
|
︙ | | |
Changes to pages/onefile.in.
︙ | | |
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
|
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
|
-
+
|
[http://en.wikipedia.org/wiki/Endianness | big-endian] and
[http://en.wikipedia.org/wiki/Endianness | little-endian]
architectures.
</p>
<p>
The SQLite database file format is also stable.
All releases of of SQLite version 3 can read and write database
All releases of SQLite version 3 can read and write database
files created by the very first SQLite 3 release (version 3.0.0)
going back to 2004-06-18. This is "backwards compatibility".
The developers promise to maintain backwards compatibility of
the database file format for all future releases of SQLite 3.
"Forwards compatibility" means that older releases
of SQLite can also read and write databases created by newer
releases. SQLite is usually, but not completely forwards
|
︙ | | |
Changes to pages/partialindex.in.
︙ | | |
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
|
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
|
-
+
|
are created by UNIQUE or PRIMARY KEY constraints inside of CREATE TABLE
statements) are ordinary full indexes.
</p>
<p>
^The expression following the WHERE clause may contain operators,
literal values, and names of columns in the table being indexed.
^The WHERE clause may <em>not</em> contains subqueries, references to other
^The WHERE clause may <em>not</em> contain subqueries, references to other
tables, functions, or [bound parameters]. The LIKE, GLOB, MATCH,
and REGEXP operators in SQLite are implemented as functions by the same name.
^Since functions are prohibited in the
WHERE clause of a CREATE INDEX statement, so too are the LIKE, GLOB,
MATCH, and REGEXP operators.</p>
<p>
|
︙ | | |
Changes to pages/pgszchng2016.in.
︙ | | |
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
|
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
|
-
+
|
a 4096 byte page is a faster and better choice.
So, beginning with SQLite [version 3.12.0] (circa 2016) the default
page size for new database files has been increased to 4096 bytes.
</p>
<p>
The upper bound on the database [cache_size|cache size] has
traditionally defaulted to 2000 page. SQLite [version 3.12.0] also
traditionally defaulted to 2000 pages. SQLite [version 3.12.0] also
changes this default setting to be "-2000" which means 2000*1024
bytes, regardless of page size. So, the upper bound on the amount
of memory used for the page cache is unchanged.
</p>
<h2>2.0 <u>Not</u> a Compatibility Break</h2>
|
︙ | | |
Changes to pages/pragma.in.
︙ | | |
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
|
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
|
-
-
+
+
+
|
<p>^When a new database is created, SQLite assigned a page size to
the database based on platform and filesystem. For many years,
the default page size was almost always 1024 bytes, but beginning
with SQLite [version 3.12.0] in 2016, the default page size increased
to 4096.
<p>^The page_size pragma will only cause an immediate change in the
page size if it is issued while the database is still empty, prior
to the first CREATE TABLE statement. ^(If the page_size pragma is
page size if it is issued while the database is still empty (prior
to the first CREATE statement) and if the database is not in
[WAL mode]. ^(If the page_size pragma is
used to specify a new page size just prior to
running the [VACUUM] command and if the database is not in
[WAL | WAL journal mode] then [VACUUM] will change the page
size to the new value.)^</p>
<p>^The [SQLITE_DEFAULT_PAGE_SIZE] compile-time option can be used
to change the default page size assigned to new databases.
|
︙ | | |
Changes to pages/quickstart.in.
︙ | | |
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
|
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
|
-
+
|
<ul>
<li><p>At a shell or DOS prompt, enter: "<b>sqlite3 test.db</b>". This will
create a new database named "test.db". (You can use a different name if
you like.)</p></li>
<li><p>Enter SQL commands at the prompt to create and populate the
new database.</p></li>
<li><p>Additional documentation is available [CLI | here]</li>
<li><p>Additional documentation is available [CLI | here].</li>
</ul>
<h2>Write Programs That Use SQLite</h2>
<ul>
<li><p>Below is a simple
[http://www.tcl-lang.org | TCL program] that demonstrates how to use
|
︙ | | |
Changes to pages/serverless.in.
︙ | | |
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
|
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
|
-
+
|
<p>
On the other hand, a database engine that uses a server can
provide better protection from bugs in the client
application - stray pointers in a client cannot corrupt memory
on the server.
And because a server is a single persistent process,
it is able to control database access with more precision,
allowing for finer grain locking and better concurrency.
allowing for finer-grained locking and better concurrency.
</p>
<p>
Most SQL database engines are client/server based.
Of those that are serverless, SQLite is the only one
known to this author that allows multiple applications
to access the same database at the same time.
|
︙ | | |
Changes to pages/threadsafe.in.
1
2
3
4
5
6
7
8
9
10
11
12
13
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
-
+
|
<title>Using SQLite In Multi-Threaded Applications</title>
<tcl>hd_keywords {threading mode}</tcl>
<h2>SQLite And Multiple Threads</h2>
<p>SQLite support three different threading modes:</p>
<p>SQLite supports three different threading modes:</p>
<ol>
<li><p><b>Single-thread</b>.
In this mode, all mutexes are disabled and SQLite is unsafe to use in
more than a single thread at once.</p></li>
<li><p><b>Multi-thread</b>.
|
︙ | | |