SQLite

Check-in [9c0400aa7a]
Login

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

Overview
Comment:More documentation updates. (CVS 718)
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 9c0400aa7a0244605828fa6450ea1cc723240b16
User & Date: drh 2002-08-15 13:45:17.000
Context
2002-08-15
13:50
Fix for ticket #135: Remove unused variables from three files. (CVS 719) (check-in: 8cf17f2a24 user: drh tags: trunk)
13:45
More documentation updates. (CVS 718) (check-in: 9c0400aa7a user: drh tags: trunk)
11:48
Additional test cases and documentation updates. (CVS 717) (check-in: 048b16c111 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to main.mk.
︙︙
298
299
300
301
302
303
304



305
306
307
308
309
310
311


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

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





# Files to be published on the website.
#
DOC = \
  index.html \
  sqlite.html \
  changes.html \








>
>
>








298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314


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

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

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


# Files to be published on the website.
#
DOC = \
  index.html \
  sqlite.html \
  changes.html \
︙︙
320
321
322
323
324
325
326
327

328
329
330
331
332
333
334

  tclsqlite.html \
  download.html \
  speed.html \
  faq.html \
  formatchng.html \
  conflict.html \
  omitted.html \
  datatypes.html


doc:	$(DOC)
	mkdir -p doc
	mv $(DOC) doc

install:	sqlite libsqlite.a sqlite.h
	mv sqlite /usr/bin








|
>








323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338

  tclsqlite.html \
  download.html \
  speed.html \
  faq.html \
  formatchng.html \
  conflict.html \
  omitted.html \
  datatypes.html \
  quickstart.html

doc:	$(DOC)
	mkdir -p doc
	mv $(DOC) doc

install:	sqlite libsqlite.a sqlite.h
	mv sqlite /usr/bin
︙︙
Changes to www/datatypes.tcl. 
1
2
3
4
5
6
7
8
9
10
11

#
# Run this script to generated a datatypes.html output file
#
set rcsid {$Id: datatypes.tcl,v 1.3 2002/08/15 11:48:14 drh Exp $}

puts {<html>
<head>
  <title>Datatypes In SQLite</title>
</head>
<body bgcolor="white">
<h1 align="center">




|








1
2
3
4
5
6
7
8
9
10
11

#
# Run this script to generated a datatypes.html output file
#
set rcsid {$Id: datatypes.tcl,v 1.4 2002/08/15 13:45:17 drh Exp $}

puts {<html>
<head>
  <title>Datatypes In SQLite</title>
</head>
<body bgcolor="white">
<h1 align="center">
︙︙
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80

  e FLOAT,
  f BOOLEAN,
  g CLOB,
  h BLOB,
  i TIMESTAMP,
  j NUMERIC(10,5)
  k VARYING CHARACTER (24),
  l NATIVE VARYING CHAR(16)
);
</pre></blockquote>

<p>
And so forth.  Basically any sequence of names optionally followed by 
one or two signed integers in parentheses will do.
</p>








|








66
67
68
69
70
71
72
73
74
75
76
77
78
79
80

  e FLOAT,
  f BOOLEAN,
  g CLOB,
  h BLOB,
  i TIMESTAMP,
  j NUMERIC(10,5)
  k VARYING CHARACTER (24),
  l NATIONAL VARYING CHARACTER(16)
);
</pre></blockquote>

<p>
And so forth.  Basically any sequence of names optionally followed by 
one or two signed integers in parentheses will do.
</p>
︙︙
Changes to www/download.tcl. 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

25
26
27
28
29

30


31


32
33
34
35
36
37
38

#
# Run this TCL script to generate HTML for the download.html file.
#
set rcsid {$Id: download.tcl,v 1.5 2002/03/30 14:15:52 drh Exp $}

puts {<html>
<head><title>SQLite Download Page</title></head>
<body bgcolor=white>
<h1 align=center>SQLite Download Page</h1>}
#<p align=center>}
#puts "This page was last modified on [lrange $rcsid 3 4] UTC<br>"
#set vers [lindex $argv 0]
#puts "The latest SQLite version is <b>$vers</b>"
#puts " created on [exec cat last_change] UTC"
#puts {</p>}

puts {<h2>Precompiled Binaries For Linux</h2>}

proc Product {file desc} {
  if {![file exists $file]} return
  set size [file size $file]
  puts [subst {
<table cellpadding="15">
<tr>

<td width="200" align="right" valign="top">
<a href="$file">$file</a><br>($size bytes)
</td>
<td valign="top">[string trim $desc]</td>
</tr>

</table>}]


}



Product sqlite.bin.gz {
  A command-line program for accessing and modifing SQLite databases.
  See <a href="sqlite.html">the documentation</a> for additional information.
}

Product tclsqlite.so.gz {




|




|
|
<
<
<
<
<
|
|





<

>
|
|
|

|
>
|
>
>

>
>








1
2
3
4
5
6
7
8
9
10





11
12
13
14
15
16
17

18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

#
# Run this TCL script to generate HTML for the download.html file.
#
set rcsid {$Id: download.tcl,v 1.6 2002/08/15 13:45:17 drh Exp $}

puts {<html>
<head><title>SQLite Download Page</title></head>
<body bgcolor=white>
<h1 align=center>SQLite Download Page</h1>
<table width="100%" cellpadding="5">





}


proc Product {file desc} {
  if {![file exists $file]} return
  set size [file size $file]
  puts [subst {

<tr>
<td width="10"></td>
<td align="right" valign="top">
<a href="$file">$file</a><br>($size bytes)</td>
<td width="5"></td>
<td valign="top">[string trim $desc]</td>
</tr>}]
}

proc Heading {title} {
  puts "<tr><td colspan=4><big><b>$title</b></big></td></tr>"
}

Heading {Precompiled Binaries for Linux}

Product sqlite.bin.gz {
  A command-line program for accessing and modifing SQLite databases.
  See <a href="sqlite.html">the documentation</a> for additional information.
}

Product tclsqlite.so.gz {
︙︙
55
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

    Product $name {
      RPM containing shared libraries and the <b>sqlite</b> command-line
      program.
    }
  }
}

puts {<h2>Precompiled Binaries For Windows</h2>}

Product sqlite.zip {
  A command-line program for accessing and modifing SQLite databases.
  See <a href="sqlite.html">the documentation</a> for additional information.
}
Product tclsqlite.zip {
  Bindings for TCL.  You can import this shared library into either
  tclsh or wish to get SQLite database access from Tcl/Tk.
  See <a href="tclsqlite.html">the documentation</a> for details.
}
Product sqlitedll.zip {
  This is a DLL of the SQLite library without the TCL bindings.
  The only external dependency is MSVCRT.DLL.
}

puts {<h2>Source Code</h2>}

Product {sqlite_source.zip} {
  This ZIP archive contains pure C source code for the SQLite library.
  Unlike the tarballs below, all of the preprocessing has already been
  done on these C source code, so you can just hand the files directly to
  your favorite C compiler.  This file is provided as a service to
  MS-Windows users who lack the build support infrastructure of Unix.








|















|








55
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

    Product $name {
      RPM containing shared libraries and the <b>sqlite</b> command-line
      program.
    }
  }
}

Heading {Precompiled Binaries For Windows}

Product sqlite.zip {
  A command-line program for accessing and modifing SQLite databases.
  See <a href="sqlite.html">the documentation</a> for additional information.
}
Product tclsqlite.zip {
  Bindings for TCL.  You can import this shared library into either
  tclsh or wish to get SQLite database access from Tcl/Tk.
  See <a href="tclsqlite.html">the documentation</a> for details.
}
Product sqlitedll.zip {
  This is a DLL of the SQLite library without the TCL bindings.
  The only external dependency is MSVCRT.DLL.
}

Heading {Source Code}

Product {sqlite_source.zip} {
  This ZIP archive contains pure C source code for the SQLite library.
  Unlike the tarballs below, all of the preprocessing has already been
  done on these C source code, so you can just hand the files directly to
  your favorite C compiler.  This file is provided as a service to
  MS-Windows users who lack the build support infrastructure of Unix.
︙︙
93
94
95
96
97
98
99

100
101
102
103
104
105
106

  regexp {sqlite-(.*)\.tar\.gz} $name match vers
  Product $name "
      Version $vers of the source tree including all documentation.
  "
}

puts {

<p><hr /></p>
<p>
<a href="index.html"><img src="/goback.jpg" border=0 />
Back to the SQLite home page</a>
</p>

</body></html>}








>








93
94
95
96
97
98
99
100
101
102
103
104
105
106
107

  regexp {sqlite-(.*)\.tar\.gz} $name match vers
  Product $name "
      Version $vers of the source tree including all documentation.
  "
}

puts {
</table>
<p><hr /></p>
<p>
<a href="index.html"><img src="/goback.jpg" border=0 />
Back to the SQLite home page</a>
</p>

</body></html>}
Changes to www/index.tcl. 
1
2
3
4
5
6
7
8
9
10
11

#
# Run this TCL script to generate HTML for the index.html file.
#
set rcsid {$Id: index.tcl,v 1.65 2002/08/14 00:08:13 drh Exp $}

puts {<html>
<head><title>SQLite: An SQL Database Engine In A C Library</title></head>
<body bgcolor=white>
<h1 align=center>SQLite: An SQL Database Engine In A C Library</h1>
<p align=center>}
puts "This page was last modified on [lrange $rcsid 3 4] UTC<br>"




|








1
2
3
4
5
6
7
8
9
10
11

#
# Run this TCL script to generate HTML for the index.html file.
#
set rcsid {$Id: index.tcl,v 1.66 2002/08/15 13:45:17 drh Exp $}

puts {<html>
<head><title>SQLite: An SQL Database Engine In A C Library</title></head>
<body bgcolor=white>
<h1 align=center>SQLite: An SQL Database Engine In A C Library</h1>
<p align=center>}
puts "This page was last modified on [lrange $rcsid 3 4] UTC<br>"
︙︙
34
35
36
37
38
39
40

41
42
43
44
45
46
47

<tr><td align="right" bgcolor="#cacae4">
<table border="2" width="100%" cellspacing=0 cellpadding=5><tr><td align="left">
Quick Links:
<ul>
<li><a href="download.html">Download</a></li>
<li><a href="http://cvs.hwaci.com:2080/sqlite/timeline">Change Log</a></li>
<li><a href="http://cvs.hwaci.com:2080/sqlite/tktnew">Report a bug</a></li>

</ul>
</td></tr></table>
</td></tr>
</table>
}

puts {<h2>Features</h2>








>








34
35
36
37
38
39
40
41
42
43
44
45
46
47
48

<tr><td align="right" bgcolor="#cacae4">
<table border="2" width="100%" cellspacing=0 cellpadding=5><tr><td align="left">
Quick Links:
<ul>
<li><a href="download.html">Download</a></li>
<li><a href="http://cvs.hwaci.com:2080/sqlite/timeline">Change Log</a></li>
<li><a href="http://cvs.hwaci.com:2080/sqlite/tktnew">Report a bug</a></li>
<li><a href="quickstart.html">Quick start</a></li>
</ul>
</td></tr></table>
</td></tr>
</table>
}

puts {<h2>Features</h2>
︙︙
125
126
127
128
129
130
131


132
133
134
135
136
137
138

}

puts {<h2>Documentation</h2>

<p>The following documentation is currently available:</p>

<p><ul>


<li><a href="faq.html">Frequently Asked Questions</a> are available online.</li>
<li>Information on the <a href="sqlite.html">sqlite</a>
    command-line utility.</li>
<li>SQLite is <a href="datatypes.html">typeless</a>.
<li>The <a href="lang.html">SQL Language</a> subset understood by SQLite.</li>
<li>The <a href="c_interface.html">C/C++ Interface</a>.</li>
<li>The <a href="tclsqlite.html">Tcl Binding</a> to SQLite.</li>








>
>








126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141

}

puts {<h2>Documentation</h2>

<p>The following documentation is currently available:</p>

<p><ul>
<li>A <a href="quickstart.html">Quick Start</a> guide to using SQLite in
    5 minutes or less.</li>
<li><a href="faq.html">Frequently Asked Questions</a> are available online.</li>
<li>Information on the <a href="sqlite.html">sqlite</a>
    command-line utility.</li>
<li>SQLite is <a href="datatypes.html">typeless</a>.
<li>The <a href="lang.html">SQL Language</a> subset understood by SQLite.</li>
<li>The <a href="c_interface.html">C/C++ Interface</a>.</li>
<li>The <a href="tclsqlite.html">Tcl Binding</a> to SQLite.</li>
︙︙
Changes to www/omitted.tcl. 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

23





24
25
26
27
28
29
30

31
32
33
34
35
36
37
38
39
40
41
42
43
44
45

46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73



74






75







76
77
78
79
80
81
82

#
# Run this script to generated a omitted.html output file
#
set rcsid {$Id: omitted.tcl,v 1.1 2002/08/14 00:08:14 drh Exp $}

puts {<html>
<head>
  <title>SQL Features That SQLite Does Not Implement</title>
</head>
<body bgcolor="white">
<h1 align="center">
SQL Features That SQLite Does Not Implement
</h1>
}
puts "<p align=center>
(This page was last modified on [lrange $rcsid 3 4] UTC)
</p>"

puts {
<p>
Rather than try to list all the features of SQL92 that SQLite does
support, it is much easier to list those that it does not.

The following are features of of SQL92 that SQLite does not implement.





</p>

<table cellpadding="10">
}

proc feature {name desc} {
  puts "<tr><td valign=\"top\"><b><nobr>$name</nobr></b></td>"

  puts "<td valign=\"top\">$desc</td></tr>"
}

feature {RIGHT and FULL OUTER JOIN} {
  LEFT OUTER JOIN is implemented, but not RIGHT OUTER JOIN or
  FULL OUTER JOIN.
}

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 ignored.

}

feature {GRANT and REVOKE} {
  Since SQLite reads and writes an ordinary disk file, the
  only access permissions that can be applied are the normal
  file access permissions of the underlying operating system.
  The GRANT and REVOKE commands commonly found on client/server
  RDBMSes are not implemented because they would be meaningless
  for an embedded database engine.
}

feature {DELETE, INSERT, and UPDATE on VIEWs} {
  VIEWs in SQLite are read-only.  But you can create a trigger
  that fires on an attempt to DELETE, INSERT, or UPDATE a view and do
  what you need in the body of the trigger.
}

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 {The COUNT(DISTINCT X) function} {
  You can accomplish the same thing using a subquery, like this:<br />
  &nbsp;&nbsp;SELECT count(x) FROM (SELECT DISTINCT x FROM tbl);
}

feature {Variable subqueries} {



  Subqueries must be static.  They are evaluated only once.  They must not,






  therefore, refer to variables in the containing query.







}

puts {
</table>

<p>
If you find other SQL92 features that SQLite does not support, please




|


















>
|
>
>
>
>
>







>



<
<
<
<
<





|
|
>


|
<
<
<
<
<
<
<
|
<
<
<
<












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








1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40





41
42
43
44
45
46
47
48
49
50
51







52




53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90

#
# Run this script to generated a omitted.html output file
#
set rcsid {$Id: omitted.tcl,v 1.2 2002/08/15 13:45:17 drh Exp $}

puts {<html>
<head>
  <title>SQL Features That SQLite Does Not Implement</title>
</head>
<body bgcolor="white">
<h1 align="center">
SQL Features That SQLite Does Not Implement
</h1>
}
puts "<p align=center>
(This page was last modified on [lrange $rcsid 3 4] UTC)
</p>"

puts {
<p>
Rather than try to list all the features of SQL92 that SQLite does
support, it is much easier to list those that it does not.
Unsupported features of SQL92 are shown below.</p>

<p>
The order of this list gives some hint as to when a feature might
be added to SQLite.  Those features near the top of the list are
likely to be added in the near future.  There are no immediate
plans to add features near the bottom of the list.
</p>

<table cellpadding="10">
}

proc feature {name desc} {
  puts "<tr><td valign=\"top\"><b><nobr>$name</nobr></b></td>"
  puts "<td width=\"10\">&nbsp;</th>"
  puts "<td valign=\"top\">$desc</td></tr>"
}






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 containing query.
}

feature {FOREIGN KEY constraints} {







  FOREIGN KEY constraints are parsed but are not enforced.




}

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 {The COUNT(DISTINCT X) function} {
  You can accomplish the same thing using a subquery, like this:<br />
  &nbsp;&nbsp;SELECT count(x) FROM (SELECT DISTINCT x FROM tbl);
}

feature {RIGHT and FULL OUTER JOIN} {
  LEFT OUTER JOIN is implemented, but not RIGHT OUTER JOIN or
  FULL OUTER JOIN.
}

feature {Writing to VIEWs} {
  VIEWs in SQLite are read-only.  You may not execute a DELETE, INSERT, or
  UPDATE statement on a view. But you can create a trigger
  that fires on an attempt to DELETE, INSERT, or UPDATE a view and do
  what you need in the body of the trigger.
}

feature {GRANT and REVOKE} {
  Since SQLite reads and writes an ordinary disk file, the
  only access permissions that can be applied are the normal
  file access permissions of the underlying operating system.
  The GRANT and REVOKE commands commonly found on client/server
  RDBMSes are not implemented because they would be meaningless
  for an embedded database engine.
}

puts {
</table>

<p>
If you find other SQL92 features that SQLite does not support, please
︙︙
Added www/quickstart.tcl. 






























































































































































































































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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110

#
# Run this TCL script to generate HTML for the quickstart.html file.
#
set rcsid {$Id: quickstart.tcl,v 1.1 2002/08/15 13:45:17 drh Exp $}

puts {<html>
<head><title>SQLite In 5 Minutes Or Less</title></head>
<body bgcolor=white>
<h1 align=center>SQLite In 5 Minutes Or Less</h1>}

puts {
<p>Here is what you do to start experimenting with SQLite without having
to do a lot of tedious reading and configuration:</p>

<h2>Download The Code</h2>

<ul>
<li><p>Get a copy of the prebuild binaries for your machine, or get a copy
of the sources and compile them yourself.  Visit
the <a href="download.html">download</a> page for more information.</p></li>
</ul>

<h2>Create A New Database</h2>

<ul>
<li><p>At a shell or DOS prompt, enter: "<b>sqlite 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>
</ul>

<h2>Write Programs That Use SQLite</h2>

<ul>
<li><p>Below is a simple TCL program that demonstrates how to use
the TCL interface to SQLite.  The program executes the SQL statements
given as the second argument on the database defined by the first
argument.</p>

<blockquote><pre>
#!/usr/bin/tclsh
if {$argc!=2} {
  puts stderr "Usage: %s DATABASE SQL-STATEMENT"
  exit 1
}
load /usr/lib/tclsqlite.so Sqlite
sqlite db [lindex $argv 0]
db eval [lindex $argv 1] x {
  foreach v $x(*) {
    puts "$v = $x($v)"
  }
  puts ""
}
db close
</pre></blockquote>
</li>

<li><p>Below is a simple C program that demonstrates how to use
the C/C++ interface to SQLite.  The name of a database is given by
the first argument and the second argument is one or more SQL statements
to execute against the database.</p>

<blockquote><pre>
#include &lt;stdio.h&gt;
#include &lt;sqlite.h&gt;

static int callback(void *NotUsed, int argc, char **argv, char **azColName){
  int i;
  for(i=0; i&lt;argc; i++){
    printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
  }
  printf("\n");
  return 0;
}

int main(int argc, char **argv){
  sqlite *db;
  char *zErrMsg = 0;
  int rc;

  if( argc!=3 ){
    fprintf(stderr, "Usage: %s DATABASE SQL-STATEMENT\n", argv[0]);
    exit(1);
  }
  db = sqlite_open(argv[1], 0, &zErrMsg);
  if( db==0 ){
    fprintf(stderr, "Can't open database: %s\n", &zErrMsg);
    exit(1);
  }
  rc = sqlite_exec(db, argv[2], callback, 0, &zErrMsg);
  if( rc!=SQLITE_OK ){
    fprintf(stderr, "SQL error: %s\n", zErrMsg);
  }
  sqlite_close(db);
  return 0;
}
</pre></blockquote>
</li>
</ul>
}

puts {
<p><hr /></p>
<p>
<a href="index.html"><img src="/goback.jpg" border=0 />
Back to the SQLite home page</a>
</p>

</body></html>}