SQLite

Check-in [3252269e90]
Login

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

Overview
Comment::-) (CVS 88)
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 3252269e9005fe3f31f285506430e33d1031da88
User & Date: drh 2000-06-09 03:47:19.000
Context
2000-06-09
03:51
:-) (CVS 89) (check-in: 8de283ded1 user: drh tags: trunk)
03:47
:-) (CVS 88) (check-in: 3252269e90 user: drh tags: trunk)
01:58
:-) (CVS 87) (check-in: 3661b5ff93 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
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.15 2000/06/08 19:38:36 drh Exp $}

puts {<html>
<head><title>SQLite: An SQL Database Engine Built Atop GDBM</title></head>
<body bgcolor=white>
<h1 align=center>SQLite: An SQL Database Engine Built Atop
<a href="http://www.gnu.org/software/gdbm/gdbm.html">GDBM</a></h1>
<p align=center>}




|








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.16 2000/06/09 03:47:19 drh Exp $}

puts {<html>
<head><title>SQLite: An SQL Database Engine Built Atop GDBM</title></head>
<body bgcolor=white>
<h1 align=center>SQLite: An SQL Database Engine Built Atop
<a href="http://www.gnu.org/software/gdbm/gdbm.html">GDBM</a></h1>
<p align=center>}
︙︙
90
91
92
93
94
95
96




97
98
99
100
101
102
103

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

<p><ul>
<li>Information on the <a href="sqlite.html">sqlite</a>
    command-line utility.</li>
<li>The <a href="c_interface.html">C/C++ Interface</a>.</li>
<li>The <a href="fileformat.html">file format</a> used by SQLite databases.</li>




</ul>
</p>

<p>The SQLite source code is 35% comment.  These comments are
another important source of information. </p>
}









>
>
>
>








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

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

<p><ul>
<li>Information on the <a href="sqlite.html">sqlite</a>
    command-line utility.</li>
<li>The <a href="c_interface.html">C/C++ Interface</a>.</li>
<li>The <a href="fileformat.html">file format</a> used by SQLite databases.</li>
<li>The <a href="arch.html">Architecture of the SQLite Library</a> describes
    how the library is put together.  <b>(preliminary)</b></li>
<li>The <a href="lang.html">SQL Language</a> subset understood by SQLite.
    <b>(under development)</b></li>
</ul>
</p>

<p>The SQLite source code is 35% comment.  These comments are
another important source of information. </p>
}
︙︙
Changes to www/lang.tcl. 
1
2
3
4
5
6
7
8
9
10
11

#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: lang.tcl,v 1.2 2000/06/09 01:58:37 drh Exp $}

puts {<html>
<head>
  <title>Query Language Understood By SQLite</title>
</head>
<body bgcolor=white>
<h1 align=center>




|








1
2
3
4
5
6
7
8
9
10
11

#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: lang.tcl,v 1.3 2000/06/09 03:47:19 drh Exp $}

puts {<html>
<head>
  <title>Query Language Understood By SQLite</title>
</head>
<body bgcolor=white>
<h1 align=center>
︙︙
42
43
44
45
46
47
48


49
50
51
52
53
54
55

  {{DROP TABLE} droptable}
  {{DROP INDEX} dropindex}
  {INSERT insert}
  {DELETE delete}
  {UPDATE update}
  {SELECT select}
  {COPY copy}


}] {
  puts "<li><a href=\"#[lindex $section 1]\">[lindex $section 0]</a></li>"
}
puts {</ul></p>

<p>Details on the implementation of each command are provided in
the sequel.</p>








>
>








42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57

  {{DROP TABLE} droptable}
  {{DROP INDEX} dropindex}
  {INSERT insert}
  {DELETE delete}
  {UPDATE update}
  {SELECT select}
  {COPY copy}
  {EXPLAIN explain}
  {expressions expr}
}] {
  puts "<li><a href=\"#[lindex $section 1]\">[lindex $section 0]</a></li>"
}
puts {</ul></p>

<p>Details on the implementation of each command are provided in
the sequel.</p>
︙︙
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

    regsub -all < $body {%LT} body
    regsub -all > $body {%GT} body
    regsub -all %LT $body {</font></b><i><font color="#ff3434">} body
    regsub -all %GT $body {</font></i><b><font color="#2c2cf0">} body
    regsub -all {[]|[*?]} $body {</font></b>&<b><font color="#2c2cf0">} body
    regsub -all "\n" [string trim $body] "<br>\n" body
    regsub -all "\n  *" $body "\n\\&nbsp;\\&nbsp;\\&nbsp;\\&nbsp;" body
    regsub -all {[|,*()]} $body {<big>&</big>} body


    puts "<td><b><font color=\"#2c2cf0\">$body</font></b></td></tr>"
  }
  puts {</table>}
}

proc Section {name {label {}}} {
  puts "\n<hr />"
  if {$label!=""} {
    puts "<a name=\"$label\">"
  }
  puts "<h1>$name</h1>\n"
}

proc Example {text} {
  puts "<blockquote><pre>$text</pre></blockquote>"
}




































Section {CREATE TABLE} {createtable}

Syntax {sql-command} {
CREATE TABLE <table-name> (
  <column-def> [, <column-def>]*
  [, <constraint>]*








|
>
>
















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








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
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132

    regsub -all < $body {%LT} body
    regsub -all > $body {%GT} body
    regsub -all %LT $body {</font></b><i><font color="#ff3434">} body
    regsub -all %GT $body {</font></i><b><font color="#2c2cf0">} body
    regsub -all {[]|[*?]} $body {</font></b>&<b><font color="#2c2cf0">} body
    regsub -all "\n" [string trim $body] "<br>\n" body
    regsub -all "\n  *" $body "\n\\&nbsp;\\&nbsp;\\&nbsp;\\&nbsp;" body
    regsub -all {[|,.*()]} $body {<big>&</big>} body
    regsub -all { = } $body { <big>=</big> } body
    regsub -all {STAR} $body {<big>*</big>} body
    puts "<td><b><font color=\"#2c2cf0\">$body</font></b></td></tr>"
  }
  puts {</table>}
}

proc Section {name {label {}}} {
  puts "\n<hr />"
  if {$label!=""} {
    puts "<a name=\"$label\">"
  }
  puts "<h1>$name</h1>\n"
}

proc Example {text} {
  puts "<blockquote><pre>$text</pre></blockquote>"
}

Section COPY copy

Syntax {sql-statement} {
COPY <table-name> FROM <string>
}

Section {CREATE INDEX} createindex

Syntax {sql-statement} {
CREATE INDEX <index-name> 
ON <table-name> ( <column-name> [, <column-name>]* )
} {column-name} {
<name> [ ASC | DESC ]
}

puts {
<p>The CREATE INDEX command consists of the keywords "CREATE INDEX" followed
by the name of the new index, the keyword "ON" the name of a previously
created table that is to be indexed, and a parenthesized list of names of
columns in the table that are used for the index key.
Each column name can be followed by one of the "ASC" or "DESC" keywords
to indicate sort order, but since GDBM does not implement ordered keys,
these keywords are ignored.</p>

<p>There are no arbitrary limits on the number of indices that can be
attached to a single table, nor on the number of columns in an index.</p>

<p>The exact text
of each CREATE INDEX statement is stored in the <b>sqlite_master</b>
table.  Everytime the database is opened, all CREATE INDEX statements
are read from the <b>sqlite_master</b> table and used to regenerate
SQLite's internal representation of the index layout.</p>
}


Section {CREATE TABLE} {createtable}

Syntax {sql-command} {
CREATE TABLE <table-name> (
  <column-def> [, <column-def>]*
  [, <constraint>]*
︙︙
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


163
164
165
166
167
168
169





170
171
172
173
174
175
176
177
178
179
180
181
182
183

184


185
























186



187
















































188
189
190
191
192
193
194
195
196
197
198
199
200
201

<p>The exact text
of each CREATE TABLE statement is stored in the <b>sqlite_master</b>
table.  Everytime the database is opened, all CREATE TABLE statements
are read from the <b>sqlite_master</b> table and used to regenerate
SQLite's internal representation of the table layout.</p>
}

Section {CREATE INDEX} createindex

Syntax {sql-statement} {
CREATE INDEX <index-name> 
ON <table-name> ( <column-name> [, <column-name>]* )
} {column-name} {
<name> [ ASC | DESC ]
}

puts {
<p>The CREATE INDEX command consists of the keywords "CREATE INDEX" followed
by the name of the new index, the keyword "ON" the name of a previously
created table that is to be indexed, and a parenthesized list of names of
columns in the table that are used for the index key.
Each column name can be followed by one of the "ASC" or "DESC" keywords
to indicate sort order, but since GDBM does not implement ordered keys,
these keywords are ignored.</p>

<p>There are no arbitrary limits on the number of indices that can be

attached to a single table, nor on the number of columns in an index.</p>



<p>The exact text
of each CREATE INDEX statement is stored in the <b>sqlite_master</b>
table.  Everytime the database is opened, all CREATE INDEX statements
are read from the <b>sqlite_master</b> table and used to regenerate
SQLite's internal representation of the index layout.</p>
}







Section {DROP TABLE} droptable

Syntax {sql-command} {
DROP TABLE <table-name>
}

puts {
<p>The DROP TABLE statement consists of the keywords "DROP TABLE" followed
by the name of the table.  The table named is completely removed from
the disk.  The table can not be recovered.  All indices associated with
the table are also reversibly deleted.</p>}


Section {DROP INDEX} dropindex



























Syntax {sql-command} {



DROP INDEX <index-name>
















































}

puts {
<p>The DROP INDEX statement consists of the keywords "DROP INDEX" followed
by the name of the index.  The index named is completely removed from
the disk.  The only way to recover the index is to reenter the
appropriate CREATE INDEX command.</p>
}

Section VACUUM vacuum

Syntax {sql-statement} {
VACUUM [<index-or-table-name>]
}








|


<
|
<
<



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













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



|
<
<
<








175
176
177
178
179
180
181
182
183
184

185


186
187
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
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302



303
304
305
306
307
308
309

<p>The exact text
of each CREATE TABLE statement is stored in the <b>sqlite_master</b>
table.  Everytime the database is opened, all CREATE TABLE statements
are read from the <b>sqlite_master</b> table and used to regenerate
SQLite's internal representation of the table layout.</p>
}

Section DELETE delete

Syntax {sql-statement} {

DELETE FROM <table-name> [WHERE <expression>]


}

puts {






<p></p>
}

Section {DROP INDEX} dropindex

Syntax {sql-command} {
DROP INDEX <index-name>
}






puts {
<p>The DROP INDEX statement consists of the keywords "DROP INDEX" followed
by the name of the index.  The index named is completely removed from
the disk.  The only way to recover the index is to reenter the
appropriate CREATE INDEX command.</p>
}

Section {DROP TABLE} droptable

Syntax {sql-command} {
DROP TABLE <table-name>
}

puts {
<p>The DROP TABLE statement consists of the keywords "DROP TABLE" followed
by the name of the table.  The table named is completely removed from
the disk.  The table can not be recovered.  All indices associated with
the table are also reversibly deleted.</p>}

Section EXPLAIN explain

Syntax {sql-statement} {
EXPLAIN <sql-statement>
}

Section expression expr

Syntax {expression} {
<expression> <binary-op> <expression> |
<expression> <like-op> <expression> |
<unary-op> <expression> |
( <expression> ) |
<column-name> |
<table-name> . <column-name> |
<literal-value> |
<function-name> ( <expr-list> | STAR ) |
<expression> ISNULL |
<expression> NOTNULL |
<expression> BETWEEN <expression> AND <expression> |
<expression> IN ( <value-list> ) |
<expression> IN ( <select> ) |
( <select> )
} {like-op} {
LIKE | GLOB | NOT LIKE | NOT GLOB
}

Section INSERT insert

Syntax {sql-statement} {
INSERT INTO <table-name> [( <column-list> )] VALUES ( <value-list> ) |
INSERT INTO <table-name> [( <column-list> )] <select-statement>
}

puts {
<p>The INSERT statement comes in two basic forms.  The first form
(with the "VALUES" keyword) creates a single new row in an existing table.
If no column-list is specified then the number of values must
be the same as the number of columns in the table.  If a column-list
is specified, then the number of values must match the number of
specified columns.  Columns of the table that do not appear in the
column list are fill with the default value, or with NULL if not
default value is specified.
</p>

<p>The second form of the INSERT statement takes it data from a
SELECT statement.  The number of columns in the result of the
SELECT must exactly match the number of columns in the table if
no column list is specified, or it must match the number of columns
name in the column list.  A new entry is made in the table
for every row of the SELECT result.  The SELECT may be simple
or compound.  If the SELECT statement has an ORDER BY clause,
the ORDER BY is ignored.</p>
}

Section SELECT select

Syntax {sql-statement} {
SELECT <result> FROM <table-list> 
[WHERE <expression>]
[GROUP BY <expr-list>]
[HAVING <expression>]
[<compound-op> <select>]*
[ORDER BY <sort-expr-list>]
} {result} {
STAR | <expresssion> [, <expression>]*
} {table-list} {
<table-name> [, <table-name>]*
} {sort-expr-list} {
<expr> [<sort-order>] [, <expr> [<sort-order>]]*
} {sort-order} {
ASC | DESC
} {compound_op} {
UNION | UNION ALL | INTERSECT | EXCEPT
}

Section UPDATE update

Syntax {sql-statement} {
UPDATE <table-name> SET <assignment> [, <assignment>] [WHERE <expression>]
} {assignment} {
<column-name> = <expression>
}

puts {
<p>



}

Section VACUUM vacuum

Syntax {sql-statement} {
VACUUM [<index-or-table-name>]
}
︙︙
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
236
237
238
239
240

<p>It is a good idea to run VACUUM after creating large indices,
especially indices where a single index value refers to many
entries in the data table.  Reorganizing these indices will make
the underlying GDBM file much smaller and will help queries to
run much faster.</p>
}

Section INSERT insert

Syntax {sql-statement} {
INSERT INTO <table-name> [( <column-list> )] VALUES ( <value-list> ) |
INSERT INTO <table-name> [( <column-list> )] <select-statement>
}

puts {
<p>The INSERT statement comes in two basic forms.  The first form
(with the "VALUES" keyword) creates a single new row in an existing table.
If no column-list is specified then the number of values must
be the same as the number of columns in the table.  If a column-list
is specified, then the number of values must match the number of
specified columns
</p>
}

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>}








<
<
<
<
<
<
<

<
<
<
<
<
<
|










318
319
320
321
322
323
324







325






326
327
328
329
330
331
332
333
334
335

<p>It is a good idea to run VACUUM after creating large indices,
especially indices where a single index value refers to many
entries in the data table.  Reorganizing these indices will make
the underlying GDBM file much smaller and will help queries to
run much faster.</p>
}








puts {






<p></p>
}

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>}