SQLite

Check-in [fc4f1f13d0]
Login

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

Overview
Comment:Documentation updates prior to 3.1.0. (CVS 2259)
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: fc4f1f13d053f861b6be0ceba29bd9e62554252f
User & Date: drh 2005-01-21 18:19:28.000
Context
2005-01-21
18:28
Version 3.1.0 (alpha) (CVS 2260) (check-in: 45094abe38 user: drh tags: trunk)
18:19
Documentation updates prior to 3.1.0. (CVS 2259) (check-in: fc4f1f13d0 user: drh tags: trunk)
17:53
Initialize the random number generator from /dev/urandom, if available. (CVS 2258) (check-in: aab8e7f8d8 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to main.mk.
484
485
486
487
488
489
490



491
492
493
494
495
496
497

vdbe.html:	$(TOP)/www/vdbe.tcl
	tclsh $(TOP)/www/vdbe.tcl >vdbe.html

version3.html:	$(TOP)/www/version3.tcl
	tclsh $(TOP)/www/version3.tcl >version3.html





# Files to be published on the website.
#
DOC = \
  arch.html \
  arch.png \
  autoinc.html \







>
>
>







484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500

vdbe.html:	$(TOP)/www/vdbe.tcl
	tclsh $(TOP)/www/vdbe.tcl >vdbe.html

version3.html:	$(TOP)/www/version3.tcl
	tclsh $(TOP)/www/version3.tcl >version3.html

whentouse.html:	$(TOP)/www/whentouse.tcl
	tclsh $(TOP)/www/whentouse.tcl >whentouse.html


# Files to be published on the website.
#
DOC = \
  arch.html \
  arch.png \
  autoinc.html \
523
524
525
526
527
528
529
530

531
532
533
534
535
536
537
  quickstart.html \
  speed.html \
  sqlite.gif \
  sqlite.html \
  support.html \
  tclsqlite.html \
  vdbe.html \
  version3.html 


doc:	common.tcl $(DOC)
	mkdir -p doc
	mv $(DOC) doc

# Standard install and cleanup targets
#







|
>







526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
  quickstart.html \
  speed.html \
  sqlite.gif \
  sqlite.html \
  support.html \
  tclsqlite.html \
  vdbe.html \
  version3.html \
  whentouse.html

doc:	common.tcl $(DOC)
	mkdir -p doc
	mv $(DOC) doc

# Standard install and cleanup targets
#
Changes to www/index.tcl.
56
57
58
59
60
61
62












63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86

proc newsitem {date title text} {
  puts "<h3>$date - $title</h3>"
  regsub -all "\n( *\n)+" $text "</p>\n\n<p>" txt
  puts "<p>$txt</p>"
  puts "<hr width=\"50%\">"
}













newsitem {2004-Nov-09} {SQLite at the 2004 International PHP Conference} {
  There was a talk on the architecture of SQLite and how to optimize
  SQLite queries at the 2004 International PHP Conference in Frankfurt,
  Germany.
  <a href="http://www.sqlite.org/php2004/page-001.html">
  Slides</a> from that talk are available.
}


newsitem {2004-Oct-11} {Version 3.0.8} {
  Version 3.0.8 of SQLite contains several code optimizations and minor
  bug fixes and adds support for DEFERRED, IMMEDIATE, and EXCLUSIVE
  transactions.  This is an incremental release.  There is no reason
  to upgrade from version 3.0.7 if that version is working for you.
}

newsitem {2004-Oct-10} {SQLite at the 11<sup><small>th</small></sup>
Annual Tcl/Tk Conference} {
  There will be a talk on the use of SQLite in Tcl/Tk at the
  11<sup><small>th</small></sup> Tcl/Tk Conference this week in
  New Orleans.  Visit <a href="http://www.tcl.tk/community/tcl2004/">
  http://www.tcl.tk/</a> for details.
  <a href="http://www.sqlite.org/tclconf2004/page-001.html">







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










<
<
<
<
<
<
<







56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84







85
86
87
88
89
90
91

proc newsitem {date title text} {
  puts "<h3>$date - $title</h3>"
  regsub -all "\n( *\n)+" $text "</p>\n\n<p>" txt
  puts "<p>$txt</p>"
  puts "<hr width=\"50%\">"
}

newsitem {2005-Jan-21} {Version 3.1.0 (alpha) Released} {
  Version 3.1.0 (alpha) is now available on the
  website.  Verison 3.1.0 is fully backwards compatible with the 3.0 series
  and features many new features including Autovacuum and correlated
  subqueries.  See the
  <a href="http://www.sqlite.org/releasenotes310.html">release notes</a>
  for details.

  This is an alpha release.  A beta release is expected in about a week
  with the first stable release to follow after two more weeks.
}

newsitem {2004-Nov-09} {SQLite at the 2004 International PHP Conference} {
  There was a talk on the architecture of SQLite and how to optimize
  SQLite queries at the 2004 International PHP Conference in Frankfurt,
  Germany.
  <a href="http://www.sqlite.org/php2004/page-001.html">
  Slides</a> from that talk are available.
}









newsitem {2004-Oct-10} {SQLite at the 11<sup><small>th</small></sup>
Annual Tcl/Tk Conference} {
  There will be a talk on the use of SQLite in Tcl/Tk at the
  11<sup><small>th</small></sup> Tcl/Tk Conference this week in
  New Orleans.  Visit <a href="http://www.tcl.tk/community/tcl2004/">
  http://www.tcl.tk/</a> for details.
  <a href="http://www.sqlite.org/tclconf2004/page-001.html">
96
97
98
99
100
101
102
103
}
  

puts {
<p align="right"><a href="oldnews.html">Old news...</a></p>
</td></tr></table>
}
footer {$Id: index.tcl,v 1.103 2004/11/11 19:32:40 drh Exp $}







|
101
102
103
104
105
106
107
108
}
  

puts {
<p align="right"><a href="oldnews.html">Old news...</a></p>
</td></tr></table>
}
footer {$Id: index.tcl,v 1.104 2005/01/21 18:19:28 drh Exp $}
Changes to www/oldnews.tcl.
1
2
3
4
5
6
7
8
9
10







11
12
13
14
15
16
17
#!/usr/bin/tclsh
source common.tcl
header {SQLite Older News}

proc newsitem {date title text} {
  puts "<h3>$date - $title</h3>"
  regsub -all "\n( *\n)+" $text "</p>\n\n<p>" txt
  puts "<p>$txt</p>"
  puts "<hr width=\"50%\">"
}








newsitem {2004-Sep-18} {Version 3.0.7} {
  Version 3.0 has now been in use by multiple projects for several
  months with no major difficulties.   We consider it stable and
  ready for production use. 
}











>
>
>
>
>
>
>







1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
#!/usr/bin/tclsh
source common.tcl
header {SQLite Older News}

proc newsitem {date title text} {
  puts "<h3>$date - $title</h3>"
  regsub -all "\n( *\n)+" $text "</p>\n\n<p>" txt
  puts "<p>$txt</p>"
  puts "<hr width=\"50%\">"
}

newsitem {2004-Oct-11} {Version 3.0.8} {
  Version 3.0.8 of SQLite contains several code optimizations and minor
  bug fixes and adds support for DEFERRED, IMMEDIATE, and EXCLUSIVE
  transactions.  This is an incremental release.  There is no reason
  to upgrade from version 3.0.7 if that version is working for you.
}

newsitem {2004-Sep-18} {Version 3.0.7} {
  Version 3.0 has now been in use by multiple projects for several
  months with no major difficulties.   We consider it stable and
  ready for production use. 
}

118
119
120
121
122
123
124
125
  changes to both the C-language API and the underlying file format
  that will enable SQLite to better support internationalization.
  The first beta is schedule for release on 2004-July-01.

  Plans are to continue to support SQLite version 2.8 with
  bug fixes.  But all new development will occur in version 3.0.
}
footer {$Id: oldnews.tcl,v 1.7 2004/10/11 22:26:00 drh Exp $}







|
125
126
127
128
129
130
131
132
  changes to both the C-language API and the underlying file format
  that will enable SQLite to better support internationalization.
  The first beta is schedule for release on 2004-July-01.

  Plans are to continue to support SQLite version 2.8 with
  bug fixes.  But all new development will occur in version 3.0.
}
footer {$Id: oldnews.tcl,v 1.8 2005/01/21 18:19:29 drh Exp $}
Changes to www/omitted.tcl.
1
2
3
4
5
6
7
8
9
10
11
#
# Run this script to generated a omitted.html output file
#
set rcsid {$Id: omitted.tcl,v 1.6 2004/05/31 15:06:30 drh Exp $}
source common.tcl
header {SQL Features That SQLite Does Not Implement}
puts {
<h2>SQL Features That SQLite Does Not Implement</h2>

<p>
Rather than try to list all the features of SQL92 that SQLite does



|







1
2
3
4
5
6
7
8
9
10
11
#
# Run this script to generated a omitted.html output file
#
set rcsid {$Id: omitted.tcl,v 1.7 2005/01/21 18:19:29 drh Exp $}
source common.tcl
header {SQL Features That SQLite Does Not Implement}
puts {
<h2>SQL Features That SQLite Does Not Implement</h2>

<p>
Rather than try to list all the features of SQL92 that SQLite does
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
}

feature {CHECK constraints} {
  CHECK constraints are parsed but they are not enforced.
  NOT NULL and UNIQUE constraints are enforced, however.
}

feature {Variable subqueries} {
  Subqueries must be static.  They are evaluated only once.  They may not,
  therefore, refer to variables in the main query.
}

feature {FOREIGN KEY constraints} {
  FOREIGN KEY constraints are parsed but are not enforced.
}

feature {Complete trigger support} {
  There is some support for triggers but it is not complete.  Missing
  subfeatures include FOR EACH STATEMENT triggers (currently all triggers
  must be FOR EACH ROW), INSTEAD OF triggers on tables (currently 
  INSTEAD OF triggers are only allowed on views), and recursive
  triggers - triggers that trigger themselves.
}

feature {ALTER TABLE} {
  To change a table you have to delete it (saving its contents to a temporary
  table) and recreate it from scratch.

}

feature {Nested transactions} {
  The current implementation only allows a single active transaction.
}

feature {The COUNT(DISTINCT X) function} {







<
<
<
<
<















>







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
}

feature {CHECK constraints} {
  CHECK constraints are parsed but they are not enforced.
  NOT NULL and UNIQUE constraints are enforced, however.
}






feature {FOREIGN KEY constraints} {
  FOREIGN KEY constraints are parsed but are not enforced.
}

feature {Complete trigger support} {
  There is some support for triggers but it is not complete.  Missing
  subfeatures include FOR EACH STATEMENT triggers (currently all triggers
  must be FOR EACH ROW), INSTEAD OF triggers on tables (currently 
  INSTEAD OF triggers are only allowed on views), and recursive
  triggers - triggers that trigger themselves.
}

feature {ALTER TABLE} {
  To change a table you have to delete it (saving its contents to a temporary
  table) and recreate it from scratch.
  ("ALTER TABLE ... RENAME TABLE ..." was added to version 3.1.0.)
}

feature {Nested transactions} {
  The current implementation only allows a single active transaction.
}

feature {The COUNT(DISTINCT X) function} {
Changes to www/speed.tcl.
1
2
3
4
5
6
7
8
9











10
11
12
13
14
15
16
#
# Run this Tcl script to generate the speed.html file.
#
set rcsid {$Id: speed.tcl,v 1.15 2004/10/10 17:24:55 drh Exp $ }
source common.tcl
header {SQLite Database Speed Comparison}

puts {
<h2>Database Speed Comparison</h2>












<h3>Executive Summary</h3>

<p>A series of tests were run to measure the relative performance of
SQLite 2.7.6, PostgreSQL 7.1.3, and MySQL 3.23.41.
The following are general
conclusions drawn from these experiments:



|





>
>
>
>
>
>
>
>
>
>
>







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
#
# Run this Tcl script to generate the speed.html file.
#
set rcsid {$Id: speed.tcl,v 1.16 2005/01/21 18:19:29 drh Exp $ }
source common.tcl
header {SQLite Database Speed Comparison}

puts {
<h2>Database Speed Comparison</h2>

<font color="red"><b>
Note:  This document is old.  It describes a speed comparison between
an older version of SQLite against archic versions of MySQL and PostgreSQL.
Readers are invited to contribute more up-to-date speed comparisons
on the <a href="http://www.sqlite.org/cvstrac/wiki">SQLite Wiki</a>.
<p>
The numbers here are old enough to be nearly meaningless.  Until it is
updated, use this document only as proof that SQLite is not a
sluggard.
</b></font>

<h3>Executive Summary</h3>

<p>A series of tests were run to measure the relative performance of
SQLite 2.7.6, PostgreSQL 7.1.3, and MySQL 3.23.41.
The following are general
conclusions drawn from these experiments: