Documentation Source Text: Check-in [02ab9d6bd3]

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

Overview

Comment:	Merge documentation fixes from the 3.28 release.
Downloads:	Tarball \| ZIP archive \| SQL archive
Timelines:	family \| ancestors \| descendants \| both \| trunk
Files:	files \| file ages \| folders
SHA3-256:	02ab9d6bd3b6357a2269c217010ebdfc2fe2fdd76e63fbe30749928e00ec3e0c
User & Date:	drh 2019-07-09 09:50:21

Context

2019-07-09
13:13		Minor tweaks to the change log and to the documentation for SQLITE_DQS. (check-in: d3b763b6a7 user: drh tags: trunk)
09:50		Merge documentation fixes from the 3.28 release. (check-in: 02ab9d6bd3 user: drh tags: trunk)
09:48		Fix documentation typo. (Leaf check-in: ee5e3f5b43 user: drh tags: branch-3.28)
2019-07-02
13:25		Fix to the 12-step procedure for generalized schema modifications. Additional text trying to describe why schema modifications are hard in SQLite. (check-in: df84d97d7b user: drh tags: trunk)

Changes

Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to document_header.tcl.



proc document_header {title path {search {}}} {
  set ret [subst -nocommands {
  <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
  <html><head>
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
  <meta http-equiv="content-type" content="text/html; charset=UTF-8">
  <link href="${path}sqlite.css" rel="stylesheet">
  <title>$title</title>
  <!-- path=$path -->
  </head>



proc document_header {title path {search {}}} {
  set ret [subst -nocommands {
  <!DOCTYPE html>
  <html><head>
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
  <meta http-equiv="content-type" content="text/html; charset=UTF-8">
  <link href="${path}sqlite.css" rel="stylesheet">
  <title>$title</title>
  <!-- path=$path -->
  </head>

Changes to pages/assert.in.

actually deal with the problem when the programmers reasoning
turns out to be wrong.  Since the code that follows ALWAYS(X) or NEVER(X)
is untested, it should be something very simple, like a "return" statement,
that is easily verified by inspection.

<p>
Because assert() can be and is commonly misused, some programming language
theorists and designers look with disfavor on the whole idea of assert().
For example, the [https://golang.org|Go programming language] omits assert().
The Go developers
[https://golang.org/doc/faq#assertions|recognize this is contentious].

The SQLite developers agree with those that think the decision to 
omit assert() from Go is a mistake.
It is true that assert() can be misused.  Antibiotics can be misused
too, but that does not mean we should ban all antibiotics.


<h2>Different Behaviors According To Build Type</h2>

<p>Three separate builds are used to validate the SQLite software.
<ol>
<li> A functionality testing build is used to validate the source code.
<li> A coverage testing build is used to validate the test suite, to confirm








|
|
<
|
>
|
|
|
|
>

actually deal with the problem when the programmers reasoning
turns out to be wrong.  Since the code that follows ALWAYS(X) or NEVER(X)
is untested, it should be something very simple, like a "return" statement,
that is easily verified by inspection.

<p>
Because assert() can be and is commonly misused, some programming language
theorists and designers look upon it with disfavor.
For example, the designers of the [https://golang.org|Go programming language] 

intentionally [https://golang.org/doc/faq#assertions|omit a built-in assert()].
They feel that the harm caused by misuse of assert()
outweighs the benefits of including it as a language built-in.
The SQLite developers disagree.  In fact, the original purpose of this
article is to push back against the common notion that assert() is harmful.
In our experience, SQLite would be much more difficult to develop, test,
and maintain without assert().

<h2>Different Behaviors According To Build Type</h2>

<p>Three separate builds are used to validate the SQLite software.
<ol>
<li> A functionality testing build is used to validate the source code.
<li> A coverage testing build is used to validate the test suite, to confirm

Changes to pages/lang.in.

In most cases [PRAGMA optimize] will not run ANALYZE, but it will occasionally
do so either for tables that have never before been analyzed, or for tables
that have grown significantly since they were last analyzed.</p>

<p>Since the actions of [PRAGMA optimize] are determined to some extent by
prior queries that have been evaluated on the same database connection, it
is recommended that [PRAGMA optimize] be deferred until the database connection
is closing and has thus had an opportunity to accumulate as must usage information
as possible.  It is also reasonable to set a timer to run [PRAGMA optimize]
every few hours, or every few days, for database connections that stay open
for a long time.</p>

<p>Applications that desire more control can run [PRAGMA optimize(0x03)] to 
obtain a list of ANALYZE commands that SQLite thinks are appropriate to run,
but without actually running those commands.  If the returned set is 
................................................................................

<p>An UPSERT is an ordinary [INSERT] statement that is followed by
the special ON CONFLICT clause shown above.

<p>The syntax that occurs in between the "ON CONFLICT" and "DO" keywords
is called the "conflict target".  The conflict target specifies a specific
uniqueness constraint that will trigger the upsert.  The conflict target
is required for DO UPDATE upserts, but is optional for DO NOTHING.
A DO NOTHING upsert without a conflict target works the same as an
[ON CONFLICT|INSERT OR IGNORE].

<p>If the insert operation would cause the uniqueness constraint identified
by the conflict-target clause to fail, then the insert is omitted and
either the DO NOTHING or DO UPDATE operation is performed instead.
In the case of a multi-row insert, this decision is made separately
for each row of the insert.








<p>Column names in the expressions of a DO UPDATE refer to the original
unchanged value of the column, before the attempted INSERT.  To use the
value that would have been inserted had the constraint not failed,
add the special "excluded." table qualifier to the column name.

<p>Some examples will help illustrate the difference:








|







 







|
|
|






>
>
>
>
>
>
>

In most cases [PRAGMA optimize] will not run ANALYZE, but it will occasionally
do so either for tables that have never before been analyzed, or for tables
that have grown significantly since they were last analyzed.</p>

<p>Since the actions of [PRAGMA optimize] are determined to some extent by
prior queries that have been evaluated on the same database connection, it
is recommended that [PRAGMA optimize] be deferred until the database connection
is closing and has thus had an opportunity to accumulate as much usage information
as possible.  It is also reasonable to set a timer to run [PRAGMA optimize]
every few hours, or every few days, for database connections that stay open
for a long time.</p>

<p>Applications that desire more control can run [PRAGMA optimize(0x03)] to 
obtain a list of ANALYZE commands that SQLite thinks are appropriate to run,
but without actually running those commands.  If the returned set is 
................................................................................

<p>An UPSERT is an ordinary [INSERT] statement that is followed by
the special ON CONFLICT clause shown above.

<p>The syntax that occurs in between the "ON CONFLICT" and "DO" keywords
is called the "conflict target".  The conflict target specifies a specific
uniqueness constraint that will trigger the upsert.  The conflict target
is required for DO UPDATE upserts, but is optional for DO NOTHING.  When
the conflict target is omitted, the upsert behavior is triggered by a
violation of any uniqueness constraint on the table of the INSERT.

<p>If the insert operation would cause the uniqueness constraint identified
by the conflict-target clause to fail, then the insert is omitted and
either the DO NOTHING or DO UPDATE operation is performed instead.
In the case of a multi-row insert, this decision is made separately
for each row of the insert.

<p>The special UPSERT processing happens only for uniqueness constraint on
the table that is receiving the INSERT. A "uniqueness constraint"
is an explicit UNIQUE or PRIMARY KEY constraint within
the CREATE TABLE statement, or a [unique index].
UPSERT does not intervene for failed NOT NULL or foreign key constraints
or for constraints that are implemented using triggers.

<p>Column names in the expressions of a DO UPDATE refer to the original
unchanged value of the column, before the attempted INSERT.  To use the
value that would have been inserted had the constraint not failed,
add the special "excluded." table qualifier to the column name.

<p>Some examples will help illustrate the difference:

Changes to pages/quirks.in.


<p>
SQLite is very flexible with regard to datatypes.

<p>
Some commentators say that SQLite is "weakly typed" and that other
SQL databases are "strongly typed".  We consider these terms to be
inaccurate and purgative.  We prefer to say that SQLite is "flexibly typed"
and that other SQL databases are "rigidly typed".

<p>
See the [datatype|Datatypes in SQLite Version 3] document for a detailed
discussion of the type system in SQLite.

<p>


<p>
SQLite is very flexible with regard to datatypes.

<p>
Some commentators say that SQLite is "weakly typed" and that other
SQL databases are "strongly typed".  We consider these terms to be
inaccurate and perjorative.  We prefer to say that SQLite is "flexibly typed"
and that other SQL databases are "rigidly typed".

<p>
See the [datatype|Datatypes in SQLite Version 3] document for a detailed
discussion of the type system in SQLite.

<p>

Changes to pages/whynotgit.in.

[https://git-scm.org|Git] version control system.
SQLite uses
[https://fossil-scm.org/|Fossil] instead, which is a
version control system that was specifically designed
and written to support SQLite.

<p>
People sometimes ask why SQLite does not use the
[https://git-scm.org|Git] version control system like everybody
else.
This article attempts to answer that question.  Also,
in <a href="#getthecode">section 3</a>, 
this article provides hints to Git users
about how they can easily access the SQLite source code.

<p>
This article is <u>not</u> a comparison between Fossil
and Git.  See
[https://fossil-scm.org/fossil/doc/trunk/www/fossil-v-git.wiki]
for a comparison of the two systems.












<h2>Edits</h2>

<p>
This article has been revised multiple times in an attempt
to improve clarity, address concerns and misgivings,
and to fix errors identified on
[https://news.ycombinator.com/item?id=16806114|Hacker News],
[https://www.reddit.com/r/programming/comments/8c2niw/why_sqlite_does_not_use_git/|Reddit]
and
[https://lobste.rs/s/slcntl/why_sqlite_does_not_use_git|Lobsters].
The complete edit history can be seen at
[https://sqlite.org/docsrc/finfo/pages/whynotgit.in].


<h1>A Few Reasons Why SQLite Does Not Use Git</h1>



<p>
One could summarize the reason why SQLite does not use Git in
a single sentence:  The lead SQLite developer finds Git to be
unpalatable.  If you like Git and want to use it, that's great.
I do not like Git and would rather use something that I think
is better.





<p>
The following are a few of the reasons why I do not like Git:

























<h2>Git makes it difficult to find successors (descendents)
of a check-in</h2>

<p>
Git allows you to go backwards in time easily.  Given the latest
check-in on a branch, Git lets you see all the ancestors of that
................................................................................
<li> The working directory
<li> The "index" or staging area
<li> The local head
<li> The local copy of the remote head
<li> The actual remote head
</ol>
<p>
Git contains commands (or options on commands) for moving and
comparing content between all of these locations. 

<p>In contrast,
Fossil users only need to think about their working directory and
the check-in they are working on.  That is 60% less distraction.
Every developer has a finite number of brain-cycles.  Fossil
requires fewer brain-cycles to operate, thus freeing up 
................................................................................
Git keeps the complete DAG of the check-in sequence.  But branch
tags are local information that is not synced and not retained
once a branch closes.
This makes review of historical
branches tedious.

<p>
As an example, consider display of a single historical
branch of SQLite as rendered by GitHub and by Fossil:




<ul>
<li><b>GitHub:</b> [https://github.com/mackyle/sqlite/commits/prefer-coroutine-sort-subquery]
<li><b>Fossil:</b> [https://sqlite.org/src/timeline?r=prefer-coroutine-sort-subquery]
</ul>

<p>
The Fossil view clearly shows that the branch was eventually merged back into
trunk.  It shows where the branch started, and it shows two occasions where changes
on trunk were merged into the branch.  GitHub shows none of this.  In fact, the
................................................................................

<h2>Git requires more administrative support</h2>

<p>
Git is complex software.
One needs an installer of some kind to put Git on a developer
workstation, or to upgrade to a newer version of Git.
Setting up a Git server is non-trivial.  One could use GitHub,
but that introduces another third-party dependency and a
centralized service, which mitigate the key advantage of Git
which is that it is "distributed".  There are various free
alternatives to GitHub, such as GitLab, but those too have
a lot of dependencies and require a lot of server setup.

<p>
In contrast, Fossil is a single standalone binary which is
installed by putting it on $PATH.  That one binary contains all
the functionality of core Git and also GitHub and/or GitLab.  It
manages a community server with wiki, bug tracking, and forums, 
provides packaged downloads for consumers, login managements, 
................................................................................
parody site that generates
[https://git-man-page-generator.lokaltog.net/|fake git man pages].

<p>Designing software is hard.  It takes a lot of focus.
A good version control system should provide the developer with
assistance, not frustration.  Git has gotten better in this
regard over the past decade, but it still has a long way to go.
And so, the developers of SQLite plan to continue using a
different version control system for now.

<a name="getthecode"></a>
<h1>A Git-User's Guide To Accessing SQLite Source Code</h1>

<p>
If you are a devoted Git user, you can still easily access SQLite.  
This section gives some hints on how to do so.

<h2>GitHub Mirrors</h2>

<p>
There is a mirror of the SQLite source tree on GitHub at
[https://github.com/mackyle/sqlite].  This mirror is maintained
by user "mackyle" who is unaffiliated with, and unknown to,
the official SQLite development team.  We do not know mackyle,

but we observe that he does a terrific job of keeping his mirror
current, and so if you want to access the SQLite source code on
GitHub, his mirror is the recommended source.














<h2>Web Access</h2>

<p>
The [https://sqlite.org/src/timeline|SQLite Fossil Repository] contains links
for downloading  a Tarball, ZIP Archive, or [SQLite Archive] for any
historical version of SQLite.  The URLs for these downloads are
................................................................................
Additional documentation on Fossil can be found at
[https://fossil-scm.org/fossil/doc/trunk/www/permutedindex.html]

<p>
Do not be afraid to explore and experiment.
Without a log-in you won't be able to
push back any changes you make, so you cannot damage the project.















<h1>See Also</h1>

<p>Other pages that talk about Fossil and Git include:
<ul>
<li><p>[https://fossil-scm.org/fossil/doc/trunk/www/fossil-v-git.wiki|Fossil vs. Git]
<li><p>[https://www.fossil-scm.org/fossil/doc/trunk/www/quotes.wiki|What others say about Fossil and Git]
</ul>








|











|
>
>
>
>
>
>
>
>
>
>
>













|



>
>

<
<
|
|
|
>
>
>
>


<
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







 







|







 







|
|
>
>
>


|







 







|
|
|
|
<
<







 







<
<








|


|
|
|
<
>
|
|
|
>
>
>
>
>
>
>
>
>
>
>
>
>







 







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

[https://git-scm.org|Git] version control system.
SQLite uses
[https://fossil-scm.org/|Fossil] instead, which is a
version control system that was specifically designed
and written to support SQLite.

<p>
People often wonder why SQLite does not use the
[https://git-scm.org|Git] version control system like everybody
else.
This article attempts to answer that question.  Also,
in <a href="#getthecode">section 3</a>, 
this article provides hints to Git users
about how they can easily access the SQLite source code.

<p>
This article is <u>not</u> a comparison between Fossil
and Git.  See
[https://fossil-scm.org/fossil/doc/trunk/www/fossil-v-git.wiki]
for one comparison of the two systems.  There are others as
well.

<p>
This article is <u>not</u> advocating that you switch your projects
away from Git.  You can use whatever version control system you want.
If you are perfectly happy with Git, then by all means keep using
Git.  But, if you are wondering if there isn't something better,
then maybe try to understand the perspectives presented below.
Use the insights thus obtained to find or write a different and
better version control system, or to just make
improvements to Git itself.

<h2>Edits</h2>

<p>
This article has been revised multiple times in an attempt
to improve clarity, address concerns and misgivings,
and to fix errors identified on
[https://news.ycombinator.com/item?id=16806114|Hacker News],
[https://www.reddit.com/r/programming/comments/8c2niw/why_sqlite_does_not_use_git/|Reddit]
and
[https://lobste.rs/s/slcntl/why_sqlite_does_not_use_git|Lobsters].
The complete edit history can be seen at
[https://sqlite.org/docsrc/finfo/pages/whynotgit.in].
(Usage hint: Click on any two nodes of the graph for a diff.)

<h1>A Few Reasons Why SQLite Does Not Use Git</h1>

<h2>Git does not provide good situational awareness</h2>

<p>


When I want to see what has been happening on SQLite (or any of
about a dozen other projects that I work on) I visit the
[https://sqlite.org/src/timeline|timeline] and in a single
screen I can see a quick summary of all the latest changes,
on all branches.
In a few clicks, I can drill down to see as much detail as I
want.  I can even do this from a phone.

<p>

GitHub and GitLab offer nothing comparible.  The closest I have
found is the [https://github.com/sqlite/sqlite/network|network],
which is slow to render (unless it is already cached), does not 
offer nearly as much details, and scarcely works on mobile.
The [https://github.com/sqlite/sqlite/commits/master|commits] view
of GitHub provides more detail, renders quickly,
and works on mobile, but only shows a single branch at a time,
so I cannot easily know if I've seen all of the recent changes.
And even if GitHub/GitLab did offer better interfaces, both are
third-party services.  They are not a core part of Git.  Hence,
using them introduces yet another dependency into the project.

<p>
I am told that Git users commonly install third-party graphical
viewers for Git, many of which do a better job of showing recent 
activity on the project.  That is great, but these are still
more third-party applications that must be installed and
managed separately.  Many are platform-specific.  (One of the
better ones, [https://gitup.co/|GitUp], only works on Mac, for
example.)  All require that you first sync your local repository
then bring up their graphical interface on your desktop.  And
even with all that, I still cannot see what I typically want to 
see without multiple clicks.  Checking on project status from
a phone while away from the office is not an option.

<h2>Git makes it difficult to find successors (descendents)
of a check-in</h2>

<p>
Git allows you to go backwards in time easily.  Given the latest
check-in on a branch, Git lets you see all the ancestors of that
................................................................................
<li> The working directory
<li> The "index" or staging area
<li> The local head
<li> The local copy of the remote head
<li> The actual remote head
</ol>
<p>
Git commands (and/or options on commands) for moving and
comparing content between all of these locations. 

<p>In contrast,
Fossil users only need to think about their working directory and
the check-in they are working on.  That is 60% less distraction.
Every developer has a finite number of brain-cycles.  Fossil
requires fewer brain-cycles to operate, thus freeing up 
................................................................................
Git keeps the complete DAG of the check-in sequence.  But branch
tags are local information that is not synced and not retained
once a branch closes.
This makes review of historical
branches tedious.

<p>
As an example, suppose someone (perhaps a customer) asks you:
"What ever became of that 'prefer-coroutine-sort-subquery' branch
from two years ago?"
You might try to answer the query by consulting the history in
your version control system, thusly:

<ul>
<li><b>GitHub:</b> [https://github.com/sqlite/sqlite/commits/prefer-coroutine-sort-subquery]
<li><b>Fossil:</b> [https://sqlite.org/src/timeline?r=prefer-coroutine-sort-subquery]
</ul>

<p>
The Fossil view clearly shows that the branch was eventually merged back into
trunk.  It shows where the branch started, and it shows two occasions where changes
on trunk were merged into the branch.  GitHub shows none of this.  In fact, the
................................................................................

<h2>Git requires more administrative support</h2>

<p>
Git is complex software.
One needs an installer of some kind to put Git on a developer
workstation, or to upgrade to a newer version of Git.
Setting up a Git server is non-trivial, and so most users
have to use a third-party service such as GitHub or GitLab,
and thus introduce additional (unnecessary) dependencies
into the project.



<p>
In contrast, Fossil is a single standalone binary which is
installed by putting it on $PATH.  That one binary contains all
the functionality of core Git and also GitHub and/or GitLab.  It
manages a community server with wiki, bug tracking, and forums, 
provides packaged downloads for consumers, login managements, 
................................................................................
parody site that generates
[https://git-man-page-generator.lokaltog.net/|fake git man pages].

<p>Designing software is hard.  It takes a lot of focus.
A good version control system should provide the developer with
assistance, not frustration.  Git has gotten better in this
regard over the past decade, but it still has a long way to go.



<a name="getthecode"></a>
<h1>A Git-User's Guide To Accessing SQLite Source Code</h1>

<p>
If you are a devoted Git user, you can still easily access SQLite.  
This section gives some hints on how to do so.

<h2>The Official GitHub Mirror</h2>

<p>
As of 2019-03-20, there is now an 
[https://github.com/sqlite/sqlite|official Git mirror] of the
SQLite sources on GitHub.


<p>The mirror is an incremental export of the 
[https://sqlite.org/src/timeline|canonical Fossil repository] for
SQLite.  A cron-job updates the GitHub repository once an hour.
This is a one-way, read-only code mirror.  No pull requests or 
changes are accepted via GitHub.  The GitHub repository merely copies
the content from the Fossil repository.  All changes are input via
Fossil.

<p>
The hashes that identify check-ins and files on the Git mirror are
different from the hashes in Fossil.  There are many reasons for
this, chief among them that Fossil uses a SHA3-256 hash whereas
Git uses a SHA1 hash.  During export, the original Fossil hash for
each check-in is added as a footer to check-in comments.  To avoid
confusion, always use the original Fossil hash, not the Git hash,
when referring to SQLite check-ins.

<h2>Web Access</h2>

<p>
The [https://sqlite.org/src/timeline|SQLite Fossil Repository] contains links
for downloading  a Tarball, ZIP Archive, or [SQLite Archive] for any
historical version of SQLite.  The URLs for these downloads are
................................................................................
Additional documentation on Fossil can be found at
[https://fossil-scm.org/fossil/doc/trunk/www/permutedindex.html]

<p>
Do not be afraid to explore and experiment.
Without a log-in you won't be able to
push back any changes you make, so you cannot damage the project.

<h2>Verifying Source Code Integrity</h2>

<p>
If you need to verify that the SQLite source code that you have is
authentic and has not been modified in any way (perhaps by an adversary)
that can be done using a few simple command-line tools.  At the root 
of the SQLite source tree is a file named "manifest".  The manifest 
file contains the name of every other file in the source tree together 
with either a SHA1 or SHA3-256 hash for that file.  (SHA1 is used for
older files and SHA3-256 for newer files.)  You can write a
script to extract these hashes and verify them against the source code 
files.  The hash name for the check-in is just the SHA3-256 hash of the
"manifest" file itself.

<h1>See Also</h1>

<p>Other pages that talk about Fossil and Git include:
<ul>
<li><p>[https://fossil-scm.org/fossil/doc/trunk/www/fossil-v-git.wiki|Fossil vs. Git]
<li><p>[https://www.fossil-scm.org/fossil/doc/trunk/www/quotes.wiki|What others say about Fossil and Git]
</ul>