Documentation Source Text

Check-in [09088d9a21]
Login

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

Overview
Comment:Fix typos.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 09088d9a2178523c10d310dd07de339e2db251d9
User & Date: drh 2014-08-14 13:06:38
Context
2014-08-14
14:37
Update the 3.8.6 news item. check-in: 9af7ac771f user: drh tags: trunk
13:06
Fix typos. check-in: 09088d9a21 user: drh tags: trunk
12:49
Set the release date for 2014-08-15. Add a News entry for the release. check-in: 4de3b544f0 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to pages/appfileformat.in.

51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
...
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
...
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
...
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
...
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
<li>PPT - Microsoft PowerPoint presentations
<li>ODP - The Open Document presentation format used by OpenOffice and others
</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".  A 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
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.
................................................................................

<p>SQLite does not have any file naming requirements
and so the application can use any custom file suffix that it wants
to help identify the file as "belonging" to the application.
SQLite database files contain a 4-byte [Application ID] in
their headers that can be set to an application-defined value
and then used to identify the "type" of the document for utility
programs such as [http://linux.die.net/man/1/file | file(1)], futher
enhancing the document metaphor.


<li><p><b>High-Level Query Language.</b>
SQLite is a complete relational database engine, which means that the
application can access content using high-level queries.  Application
developers need not spend time thinking about "how" to retrieve the
................................................................................
are installed by default on Mac and Linux systems and that are 
freely available as a self-contained EXE file on Windows.
Unlike custom file formats, application-specific programs are
not required to read or write content in an SQLite database.
An SQLite database file is not an opaque blob.  It is true
that command-line tools such as text editors or "grep" or "awk" are
not useful on an SQLite database, but the SQL query language is a much
more powerful and convenient way for examining the the content, so the
inability to use "grep" and "awk" and the like is not seen as a loss.

<p>An SQLite database is a [file format | well-defined and well-documented]
file format that is in widespread use by literally hundreds of 
thousands of applications and
is backwards compatible to its inception in 2004 and which promises
to continue to be compatible in years to come.  The longevity of
................................................................................
They either happen completely
or not at all, even during system crashes or power failures.  So
there is no danger of corrupting a document just because the power happened
to go out at the same instant that a change was being written to disk.

<p>SQLite is transactional, meaning that multiple changes can be grouped
together such that either all or none of them occur, and so that the
changes can be rolled back if a problem is found prior to to commit.
This allows an application to make a change incrementally, then run
various sanity and consistency checks on the resulting data prior to
committing the changes to disk.  The
[http://www.fossil-scm.org/ | Fossil] DVCS 
[http://www.fossil-scm.org/fossil/doc/tip/www/selfcheck.wiki|uses this technique]
to verify that no repository history has been lost prior to each change.

................................................................................
is faster than SQLite database I/O, as often it is not.

<p>In either case, if performance problems do arise in an SQLite application 
those problems can often be resolved by adding one or two [CREATE INDEX]
statements to the schema or perhaps running [ANALYZE] one time
and without having to touch a single line of
application code.  But if a performance problem comes up in a custom or 
pile-of-files format, the the fix will often require extensive changes
to application code to add and maintain new indices or to extract 
information using different algorithms.

<li><p><b>Concurrent Use By Multiple Processes.</b>
SQLite automatically coordinates concurrent access to the same
document from multiple threads and/or processes.  Two or more
applications can connect and read from the same document at the







|







 







|







 







|







 







|







 







|







51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
...
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
...
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
...
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
...
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
<li>PPT - Microsoft PowerPoint presentations
<li>ODP - The Open Document presentation format used by OpenOffice and others
</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
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.
................................................................................

<p>SQLite does not have any file naming requirements
and so the application can use any custom file suffix that it wants
to help identify the file as "belonging" to the application.
SQLite database files contain a 4-byte [Application ID] in
their headers that can be set to an application-defined value
and then used to identify the "type" of the document for utility
programs such as [http://linux.die.net/man/1/file | file(1)], further
enhancing the document metaphor.


<li><p><b>High-Level Query Language.</b>
SQLite is a complete relational database engine, which means that the
application can access content using high-level queries.  Application
developers need not spend time thinking about "how" to retrieve the
................................................................................
are installed by default on Mac and Linux systems and that are 
freely available as a self-contained EXE file on Windows.
Unlike custom file formats, application-specific programs are
not required to read or write content in an SQLite database.
An SQLite database file is not an opaque blob.  It is true
that command-line tools such as text editors or "grep" or "awk" are
not useful on an SQLite database, but the SQL query language is a much
more powerful and convenient way for examining the content, so the
inability to use "grep" and "awk" and the like is not seen as a loss.

<p>An SQLite database is a [file format | well-defined and well-documented]
file format that is in widespread use by literally hundreds of 
thousands of applications and
is backwards compatible to its inception in 2004 and which promises
to continue to be compatible in years to come.  The longevity of
................................................................................
They either happen completely
or not at all, even during system crashes or power failures.  So
there is no danger of corrupting a document just because the power happened
to go out at the same instant that a change was being written to disk.

<p>SQLite is transactional, meaning that multiple changes can be grouped
together such that either all or none of them occur, and so that the
changes can be rolled back if a problem is found prior to commit.
This allows an application to make a change incrementally, then run
various sanity and consistency checks on the resulting data prior to
committing the changes to disk.  The
[http://www.fossil-scm.org/ | Fossil] DVCS 
[http://www.fossil-scm.org/fossil/doc/tip/www/selfcheck.wiki|uses this technique]
to verify that no repository history has been lost prior to each change.

................................................................................
is faster than SQLite database I/O, as often it is not.

<p>In either case, if performance problems do arise in an SQLite application 
those problems can often be resolved by adding one or two [CREATE INDEX]
statements to the schema or perhaps running [ANALYZE] one time
and without having to touch a single line of
application code.  But if a performance problem comes up in a custom or 
pile-of-files format, the fix will often require extensive changes
to application code to add and maintain new indices or to extract 
information using different algorithms.

<li><p><b>Concurrent Use By Multiple Processes.</b>
SQLite automatically coordinates concurrent access to the same
document from multiple threads and/or processes.  Two or more
applications can connect and read from the same document at the

Changes to pages/fts3.in.

1996
1997
1998
1999
2000
2001
2002
2003
2004
2005
2006
2007
2008
2009
2010
  quickly, which may speed up full-text queries and, if the workload 
  contains UPDATE or DELETE operations as well as INSERTs, reduce the space
  on disk consumed by the full-text index. However, it also increases the
  amount of data written to disk.

<p>
  For general use in cases where the workload contains few UPDATE or DELETE
  operations, a good choicd for automerge is 8. If the workload contains
  many UPDATE or DELETE commands, 
  or if query speed is a concern, it may be advantageous to reduce automerge
  to 2.

<p>
  For reasons of backwards compatibility, the "automerge=1" command sets
  the automerge parameter to 8, not 1 (a value of 1 would make no sense 







|







1996
1997
1998
1999
2000
2001
2002
2003
2004
2005
2006
2007
2008
2009
2010
  quickly, which may speed up full-text queries and, if the workload 
  contains UPDATE or DELETE operations as well as INSERTs, reduce the space
  on disk consumed by the full-text index. However, it also increases the
  amount of data written to disk.

<p>
  For general use in cases where the workload contains few UPDATE or DELETE
  operations, a good choice for automerge is 8. If the workload contains
  many UPDATE or DELETE commands, 
  or if query speed is a concern, it may be advantageous to reduce automerge
  to 2.

<p>
  For reasons of backwards compatibility, the "automerge=1" command sets
  the automerge parameter to 8, not 1 (a value of 1 would make no sense 

Changes to pages/lang.in.

233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
associated with table X.  Perhaps use the old format of the triggers and
indexes saved from step 1 above as a guide, making changes as appropriate
for the alteration.

<li><p>
If foreign key constraints were originally enabled (prior to
step 4) then run [PRAGMA foreign_key_check] to verify that the schema
change did not break any foreign key contraints, and run
[PRAGMA foreign_keys | PRAGMA foreign_keys=ON] to reenable foreign key
constraints.

<li><p>If any views refer to table X in a way that is affected by the
schema change, then drop those views using [DROP VIEW] and recreate them
with whatever changes are necessary to accomodate the schema change
using [CREATE VIEW].

</ol>

<p>The procedure above is completely general and will work even if the
schema change causes the information stored in the table to change.
So the full procedure above is appropriate for dropping a column,







|
|




|







233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
associated with table X.  Perhaps use the old format of the triggers and
indexes saved from step 1 above as a guide, making changes as appropriate
for the alteration.

<li><p>
If foreign key constraints were originally enabled (prior to
step 4) then run [PRAGMA foreign_key_check] to verify that the schema
change did not break any foreign key constraints, and run
[PRAGMA foreign_keys | PRAGMA foreign_keys=ON] to re-enable foreign key
constraints.

<li><p>If any views refer to table X in a way that is affected by the
schema change, then drop those views using [DROP VIEW] and recreate them
with whatever changes are necessary to accommodate the schema change
using [CREATE VIEW].

</ol>

<p>The procedure above is completely general and will work even if the
schema change causes the information stored in the table to change.
So the full procedure above is appropriate for dropping a column,

Changes to pages/news.in.

17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
      {<a href="http://www.sqlite.org/cvstrac/tktview?tn=\1">\0</a>} txt
  hd_resolve "<blockquote>$txt</blockquote>"
  hd_puts "<hr width=\"50%\">"
}

newsitem {2014-08-15} {Release 3.8.6} {
<p>SQLite [version 3.8.6] is a regularly scheduled maintenance release.
   Upgrading from all previous versions is recommented.

<p>This release contains the usual assortment of obscure bug fixes.
   One bug, however, deserves special attention.
   A problem appeared in the [CREATE INDEX] command beginning with
   [version 3.8.2] (2013-12-06) that allowed, under some circumstances,
   a UNIQUE index to be created on a column that was not unique.  Once
   the index is created, no new non-unique entries could be inserted, but







|







17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
      {<a href="http://www.sqlite.org/cvstrac/tktview?tn=\1">\0</a>} txt
  hd_resolve "<blockquote>$txt</blockquote>"
  hd_puts "<hr width=\"50%\">"
}

newsitem {2014-08-15} {Release 3.8.6} {
<p>SQLite [version 3.8.6] is a regularly scheduled maintenance release.
   Upgrading from all previous versions is recommended.

<p>This release contains the usual assortment of obscure bug fixes.
   One bug, however, deserves special attention.
   A problem appeared in the [CREATE INDEX] command beginning with
   [version 3.8.2] (2013-12-06) that allowed, under some circumstances,
   a UNIQUE index to be created on a column that was not unique.  Once
   the index is created, no new non-unique entries could be inserted, but

Changes to pages/optoverview.in.

798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
...
996
997
998
999
1000
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
  In the latter query, the query plan must be 
  <a href="#option2">option 2</a>.  ^Note that
  you must use the keyword "CROSS" in order to disable the table reordering
  optimization; INNER JOIN, NATURAL JOIN, JOIN, and other similar
  combinations work just like a comma join in that the optimizer is
  free to reorder tables as it sees fit. (Table reordering is also
  disabled on an outer join, but that is because outer joins are not
  associative or commutative. Reordering tables in in OUTER JOIN changes
  the result.)
}
PARAGRAPH {
  See "[The Fossil NGQP Upgrade Case Study]" for another real-world example
  of using CROSS JOIN to manually control the nesting order of a join.
  The [query planner checklist] found later in the same document provides
  further guidance on manual control of the query planner.
................................................................................
  be that SQLite can use indices to cause rows to come out in the order
  of some prefix of the terms in the ORDER BY but that later terms in
  the ORDER BY are not satisfied.  In that case, SQLite does block sorting.
  Suppose the ORDER BY clause has four terms and the natural order of the
  query results in rows appearing in order of the first two terms.  As
  each row is output by the query engine and enters the sorter, the 
  outputs in the current row corresponding to the first two terms of 
  the ORDER BY are comparied against the previous row.  If they have
  changed, the current sort is finished and output and a new sort is
  started.  This results in a slightly faster sort.  But the bigger
  advantages are that many fewer rows need to be held in memory,
  reducing memory requirements, and outputs can begin to appear before
  the core query has run to completion.
}








|







 







|







798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
...
996
997
998
999
1000
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
  In the latter query, the query plan must be 
  <a href="#option2">option 2</a>.  ^Note that
  you must use the keyword "CROSS" in order to disable the table reordering
  optimization; INNER JOIN, NATURAL JOIN, JOIN, and other similar
  combinations work just like a comma join in that the optimizer is
  free to reorder tables as it sees fit. (Table reordering is also
  disabled on an outer join, but that is because outer joins are not
  associative or commutative. Reordering tables in OUTER JOIN changes
  the result.)
}
PARAGRAPH {
  See "[The Fossil NGQP Upgrade Case Study]" for another real-world example
  of using CROSS JOIN to manually control the nesting order of a join.
  The [query planner checklist] found later in the same document provides
  further guidance on manual control of the query planner.
................................................................................
  be that SQLite can use indices to cause rows to come out in the order
  of some prefix of the terms in the ORDER BY but that later terms in
  the ORDER BY are not satisfied.  In that case, SQLite does block sorting.
  Suppose the ORDER BY clause has four terms and the natural order of the
  query results in rows appearing in order of the first two terms.  As
  each row is output by the query engine and enters the sorter, the 
  outputs in the current row corresponding to the first two terms of 
  the ORDER BY are compared against the previous row.  If they have
  changed, the current sort is finished and output and a new sort is
  started.  This results in a slightly faster sort.  But the bigger
  advantages are that many fewer rows need to be held in memory,
  reducing memory requirements, and outputs can begin to appear before
  the core query has run to completion.
}

Changes to pages/testing.in.

639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
...
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
(without the special -fprofile-arcs and -ftest-coverage options)
and the test program is rerun.
This second run is the actual test of SQLite.

<p>It is important to verify that both the gcov test run 
and the second real test run both give the same output.  Any
differences in output indicate either the use of undefined or
indeterminant behavior in the SQLite code (and hence a bug), 
or a bug in the compiler.
Note that SQLite has, over the previous decade, encountered bugs
in each of GCC, Clang, and MSVC.  Compiler bugs, while rare, do happen,
which is why it is so important to test the code in an as-delivered
configuration.

<tcl>hd_fragment thoughts1</tcl>
................................................................................
<p>The SQLite developers use an on-line checklist to coordinate testing
activity and to verify that all tests pass prior each SQLite release.
<a href="http://www.sqlite.org/checklists/index.html">Past checklists</a>
are retained for historical reference.
(The checklists are read-only for anonymous internet viewers, but
developers can log in and update checklist items in their web
browsers.)
The use of checklists for SQLite testing and other development activites
is inspired by <i>
[http://atulgawande.com/book/the-checklist-manifesto/ | The Checklist Manifesto]
</i>.</p>

<p>The latest checklists contain approximately 200 items that are
individually verified for each release.  Some checklist items only take
a few seconds to verify and mark off.  Others involve test suites







|







 







|







639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
...
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
(without the special -fprofile-arcs and -ftest-coverage options)
and the test program is rerun.
This second run is the actual test of SQLite.

<p>It is important to verify that both the gcov test run 
and the second real test run both give the same output.  Any
differences in output indicate either the use of undefined or
indeterminate behavior in the SQLite code (and hence a bug), 
or a bug in the compiler.
Note that SQLite has, over the previous decade, encountered bugs
in each of GCC, Clang, and MSVC.  Compiler bugs, while rare, do happen,
which is why it is so important to test the code in an as-delivered
configuration.

<tcl>hd_fragment thoughts1</tcl>
................................................................................
<p>The SQLite developers use an on-line checklist to coordinate testing
activity and to verify that all tests pass prior each SQLite release.
<a href="http://www.sqlite.org/checklists/index.html">Past checklists</a>
are retained for historical reference.
(The checklists are read-only for anonymous internet viewers, but
developers can log in and update checklist items in their web
browsers.)
The use of checklists for SQLite testing and other development activities
is inspired by <i>
[http://atulgawande.com/book/the-checklist-manifesto/ | The Checklist Manifesto]
</i>.</p>

<p>The latest checklists contain approximately 200 items that are
individually verified for each release.  Some checklist items only take
a few seconds to verify and mark off.  Others involve test suites