Documentation Source Text

Check-in [f060763119]
Login

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

Overview
Comment:Updates to the SQL language documentation with many added hyperlink targets.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: f0607631193d8d038fcffbecb9e9b3d9748a6966
User & Date: drh 2008-04-27 17:51:42.000
Context
2008-04-29
13:10
Add details to the page_size pragma, including specification of the page size selection algorithm. (check-in: bc47f60abd user: drh tags: trunk)
2008-04-27
17:51
Updates to the SQL language documentation with many added hyperlink targets. (check-in: f060763119 user: drh tags: trunk)
15:10
Updates to the CREATE TABLE documentation. (check-in: be570dc031 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to pages/autoinc.in.
1
2

3
4
5
6
7
8
9
<title>SQLite Autoincrement</title>
<h1>SQLite Autoincrement</h1>


<p>
In SQLite, every row of every table has an integer ROWID.
The ROWID for each row is unique among all rows in the same table.
In SQLite 3.0.0 and later the ROWID is a 64-bit signed integer.
</p>



>







1
2
3
4
5
6
7
8
9
10
<title>SQLite Autoincrement</title>
<h1>SQLite Autoincrement</h1>
<tcl>hd_keywords AUTOINCREMENT</tcl>

<p>
In SQLite, every row of every table has an integer ROWID.
The ROWID for each row is unique among all rows in the same table.
In SQLite 3.0.0 and later the ROWID is a 64-bit signed integer.
</p>

Changes to pages/changes.in.
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
  global DEST
  if {[regexp {\(([0-9.]+)\)} $date all vers]} {
    set label [string map {. _} $vers]
    hd_fragment version_$label
  }
  hd_puts "<h3>$date</h3>"
  hd_resolve "<p><ul>$desc</ul></p>"
  if {[regexp {\((3\.\d+\.\d+)[ a-z]*\)} $date all vers]} {
    set tag [string map {. _} $vers]
    file mkdir $DEST/releaselog
    set filename releaselog/$tag.html
    hd_open_aux $filename
    hd_header "SQLite Release $vers On $date"
    hd_keywords "Version $vers" "version $vers"
    hd_enable_main 0







|







13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
  global DEST
  if {[regexp {\(([0-9.]+)\)} $date all vers]} {
    set label [string map {. _} $vers]
    hd_fragment version_$label
  }
  hd_puts "<h3>$date</h3>"
  hd_resolve "<p><ul>$desc</ul></p>"
  if {[regexp {\((3\.\d+\.\d+)[ a-zA-Z]*\)} $date all vers]} {
    set tag [string map {. _} $vers]
    file mkdir $DEST/releaselog
    set filename releaselog/$tag.html
    hd_open_aux $filename
    hd_header "SQLite Release $vers On $date"
    hd_keywords "Version $vers" "version $vers"
    hd_enable_main 0
Changes to pages/datatype3.in.
312
313
314
315
316
317
318
319

320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340







341
342
343
344
345
346
347

	<LI><P><B>No affinity</B> mode. In this mode no conversions between
	storage classes are ever performed. Comparisons between values of
	different storage classes (except for INTEGER and REAL) are always
	false.</P>
</UL>

<a name="collation"></a>

<h3>7. User-defined Collation Sequences</h3>

<p>
By default, when SQLite compares two text values, the result of the
comparison is determined using memcmp(), regardless of the encoding of the
string. SQLite v3 provides the ability for users to supply arbitrary
comparison functions, known as user-defined collation sequences, to be used
instead of memcmp().
</p>  
<p>
Aside from the default collation sequence BINARY, implemented using
memcmp(), SQLite features one extra built-in collation sequences 
intended for testing purposes, the NOCASE collation:
</p>  
<UL>
	<LI><b>BINARY</b> - Compares string data using memcmp(), regardless
                            of text encoding.</LI>
	<LI><b>NOCASE</b> - The same as binary, except the 26 upper case
			    characters used by the English language are
			    folded to their lower case equivalents before
                            the comparison is performed.  </UL>









<h4>7.1 Assigning Collation Sequences from SQL</h4>

<p>
Each column of each table has a default collation type. If a collation type
other than BINARY is required, a COLLATE clause is specified as part of the







|
>






|
|



|
|


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







312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355

	<LI><P><B>No affinity</B> mode. In this mode no conversions between
	storage classes are ever performed. Comparisons between values of
	different storage classes (except for INTEGER and REAL) are always
	false.</P>
</UL>

<tcl>hd_fragment collation {collating sequence} \
                 {collating function} collation BINARY NOCASE RTRIM</tcl>
<h3>7. User-defined Collation Sequences</h3>

<p>
By default, when SQLite compares two text values, the result of the
comparison is determined using memcmp(), regardless of the encoding of the
string. SQLite v3 provides the ability for users to supply arbitrary
comparison functions, known as user-defined "collation sequences" or
"collating functions", to be used instead of memcmp().
</p>  
<p>
Aside from the default collation sequence BINARY, implemented using
memcmp(), SQLite features two extra built-in collation sequences 
intended for testing purposes, the NOCASE and RTRIM collations:
</p>  
<UL>
<LI><b>BINARY</b> - Compares string data using memcmp(), regardless
                    of text encoding.</LI>
<LI><b>NOCASE</b> - The same as binary, except the 26 upper case
     characters of ASCII are
     folded to their lower case equivalents before
     the comparison is performed.  Note that only ASCII characters
     are case folded.  SQLite does not attempt to due full
     UTF case folding due to the size of the tables required.</li>

<LI><b>RTRIM</b> - The same as binary, except that trailing space
     characters are ignored.</li>
</ul>



<h4>7.1 Assigning Collation Sequences from SQL</h4>

<p>
Each column of each table has a default collation type. If a collation type
other than BINARY is required, a COLLATE clause is specified as part of the
Changes to pages/lang.in.
52
53
54
55
56
57
58



59
60
61
62
63
64
65
  {{CREATE TRIGGER} createtrigger}
  {{DROP TRIGGER} droptrigger}
  {{ATTACH DATABASE} attach}
  {{DETACH DATABASE} detach}
  {REINDEX reindex}
  {{ALTER TABLE} altertable}
  {{ANALYZE} analyze}



}] {
  foreach {s_title s_tag} $section {}
  if {$s_tag=="pragma.html"} {
    set url $s_tag
  } else {
    set url lang_$s_tag.html
  }







>
>
>







52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
  {{CREATE TRIGGER} createtrigger}
  {{DROP TRIGGER} droptrigger}
  {{ATTACH DATABASE} attach}
  {{DETACH DATABASE} detach}
  {REINDEX reindex}
  {{ALTER TABLE} altertable}
  {{ANALYZE} analyze}
  {{aggregate functions} aggfunc}
  {{core functions} corefunc}
  {{date and time functions} datefunc}
}] {
  foreach {s_title s_tag} $section {}
  if {$s_tag=="pragma.html"} {
    set url $s_tag
  } else {
    set url lang_$s_tag.html
  }
104
105
106
107
108
109
110

111
112
113
114
115
116
117

118
119
120
121
122
123
124
} {alteration} {
RENAME TO <new-table-name>
} {alteration} {
ADD [COLUMN] <column-def>
}
</tcl>


<p>SQLite's version of the ALTER TABLE command allows the user to 
rename or add a new column to an existing table. It is not possible
to remove a column from a table.
</p>

<p>The RENAME TO syntax is used to rename the table identified by 
<i>&#91;database-name.&#93;table-name</i> to <i>new-table-name</i>. This command 

cannot be used to move a table between attached databases, only to rename 
a table within the same database.</p>

<p>If the table being renamed has triggers or indices, then these remain
attached to the table after it has been renamed. However, if there are
any view definitions, or statements executed by triggers that refer to
the table being renamed, these are not automatically modified to use the new







>
|
|
|



|
>







107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
} {alteration} {
RENAME TO <new-table-name>
} {alteration} {
ADD [COLUMN] <column-def>
}
</tcl>

<p>SQLite supports a limited subset of ALTER TABLE.
The ALTER TABLE command in SQLite allows the user to rename a table
or to add a new column to an existing table. It is not possible
to rename a colum, remove a column, or add or remove constraints from a table.
</p>

<p>The RENAME TO syntax is used to rename the table identified by 
<i>&#91;database-name.&#93;table-name</i> to <i>new-table-name</i>.
This command 
cannot be used to move a table between attached databases, only to rename 
a table within the same database.</p>

<p>If the table being renamed has triggers or indices, then these remain
attached to the table after it has been renamed. However, if there are
any view definitions, or statements executed by triggers that refer to
the table being renamed, these are not automatically modified to use the new
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156

<p>The execution time of the ALTER TABLE command is independent of
the amount of data in the table.  The ALTER TABLE command runs as quickly
on a table with 10 million rows as it does on a table with 1 row.
</p>

<p>After ADD COLUMN has been run on a database, that database will not
be readable by SQLite version 3.1.3 and earlier until the database
is <a href="lang_vacuum.html">VACUUM</a>ed.</p>

<tcl>
##############################################################################
Section {ANALYZE} analyze ANALYZE

Syntax {sql-statement} {
  ANALYZE







|
<







146
147
148
149
150
151
152
153

154
155
156
157
158
159
160

<p>The execution time of the ALTER TABLE command is independent of
the amount of data in the table.  The ALTER TABLE command runs as quickly
on a table with 10 million rows as it does on a table with 1 row.
</p>

<p>After ADD COLUMN has been run on a database, that database will not
be readable by SQLite version 3.1.3 and earlier.</p>


<tcl>
##############################################################################
Section {ANALYZE} analyze ANALYZE

Syntax {sql-statement} {
  ANALYZE
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
202
203
204
205
206
207
208
analyzed.  If a database name is given as the argument, all indices
in that one database are analyzed.  If the argument is a table name,
then only indices associated with that one table are analyzed.</p>

<p>The initial implementation stores all statistics in a single
table named <b>sqlite_stat1</b>.  Future enhancements may create
additional tables with the same name pattern except with the "1"
changed to a different digit.  The <b>sqlite_stat1</b> table cannot
be <a href="lang_droptable.html">DROP</a>ped,
but all the content can be <a href="lang_delete.html">DELETE</a>d which has the
same effect.</p>

<tcl>
Section {ATTACH DATABASE} attach ATTACH

Syntax {sql-statement} {
ATTACH [DATABASE] <database-filename> AS <database-name>
}
</tcl>

<p>The ATTACH DATABASE statement adds another database 
file to the current database connection.  If the filename contains 
punctuation characters it must be quoted.  The names 'main' and 
'temp' refer to the main database and the database used for 
temporary tables.  These cannot be detached.  Attached databases 
are removed using the <a href="lang_detach.html">DETACH DATABASE</a> 
statement.</p>

<p>You can read from and write to an attached database and you
can modify the schema of the attached database.  This is a new
feature of SQLite version 3.0.  In SQLite 2.8, schema changes
to attached databases were not allowed.</p>

<p>You cannot create a new table with the same name as a table in 
an attached database, but you can attach a database which contains
tables whose names are duplicates of tables in the main database.  It is 
also permissible to attach the same database file multiple times.</p>

<p>Tables in an attached database can be referred to using the syntax 







|
|
|
|














<
|
<
<
<
<
<







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
202
203
204
205
206
analyzed.  If a database name is given as the argument, all indices
in that one database are analyzed.  If the argument is a table name,
then only indices associated with that one table are analyzed.</p>

<p>The initial implementation stores all statistics in a single
table named <b>sqlite_stat1</b>.  Future enhancements may create
additional tables with the same name pattern except with the "1"
changed to a different digit.  The [DROP TABLE] command does
not work on the <b>sqlite_stat1</b> table,
but all the content can be removed using the [DELETE] command,
which has the same effect.</p>

<tcl>
Section {ATTACH DATABASE} attach ATTACH

Syntax {sql-statement} {
ATTACH [DATABASE] <database-filename> AS <database-name>
}
</tcl>

<p>The ATTACH DATABASE statement adds another database 
file to the current database connection.  If the filename contains 
punctuation characters it must be quoted.  The names 'main' and 
'temp' refer to the main database and the database used for 
temporary tables.  These cannot be detached.  Attached databases 

are removed using the [DETACH] statement.</p>






<p>You cannot create a new table with the same name as a table in 
an attached database, but you can attach a database which contains
tables whose names are duplicates of tables in the main database.  It is 
also permissible to attach the same database file multiple times.</p>

<p>Tables in an attached database can be referred to using the syntax 
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231

232
233
234
235
236
237
238

<p>
Transactions involving multiple attached databases are atomic,
assuming that the main database is not ":memory:".  If the main
database is ":memory:" then 
transactions continue to be atomic within each individual
database file. But if the host computer crashes in the middle
of a COMMIT where two or more database files are updated,
some of those files might get the changes where others
might not.
Atomic commit of attached databases is a new feature of SQLite version 3.0.
In SQLite version 2.8, all commits to attached databases behaved as if
the main database were ":memory:".
</p>

<p>There is a compile-time limit of 10 attached database files.</p>


<tcl>
###############################################################################
Section {BEGIN TRANSACTION} transaction {BEGIN COMMIT ROLLBACK}

Syntax {sql-statement} {
BEGIN [ DEFERRED | IMMEDIATE | EXCLUSIVE ] [TRANSACTION [<name>]]







|


<
<
<


|
>







214
215
216
217
218
219
220
221
222
223



224
225
226
227
228
229
230
231
232
233
234

<p>
Transactions involving multiple attached databases are atomic,
assuming that the main database is not ":memory:".  If the main
database is ":memory:" then 
transactions continue to be atomic within each individual
database file. But if the host computer crashes in the middle
of a [COMMIT] where two or more database files are updated,
some of those files might get the changes where others
might not.



</p>

<p>There is a compile-time limit of [SQLITE_MAX_ATTACHED]
attached database files.</p>

<tcl>
###############################################################################
Section {BEGIN TRANSACTION} transaction {BEGIN COMMIT ROLLBACK}

Syntax {sql-statement} {
BEGIN [ DEFERRED | IMMEDIATE | EXCLUSIVE ] [TRANSACTION [<name>]]
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
310
311
312
313
314
315

316
317
318
319
320

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

338
339
340
341
342
343
344
ROLLBACK [TRANSACTION [<name>]]
}
</tcl>

<p>
No changes can be made to the database except within a transaction.
Any command that changes the database (basically, any SQL command
other than SELECT) will automatically start a transaction if
one is not already in effect.  Automatically started transactions
are committed at the conclusion of the command.
</p>

<p>
Transactions can be started manually using the BEGIN
command.  Such transactions usually persist until the next
COMMIT or ROLLBACK command.  But a transaction will also 
ROLLBACK if the database is closed or if an error occurs
and the ROLLBACK conflict resolution algorithm is specified.
See the documentation on the <a href="lang_conflict.html">ON CONFLICT</a>
clause for additional information about the ROLLBACK
conflict resolution algorithm.
</p>

<p>
END TRANSACTION is an alias for COMMIT.
</p>

<p>The optional transaction name is current ignored. SQLite 
does not recognize nested transactions at this time.
However, future versions of SQLite may be enhanced to support nested
transactions and the transaction name would then become significant.
Application are advised not to use the transaction name in order
to avoid future compatibility problems.</p>

<p>
Transactions can be deferred, immediate, or exclusive.  
The default transaction behavior is deferred.
Deferred means that no locks are acquired
on the database until the database is first accessed.  Thus with a
deferred transaction, the BEGIN statement itself does nothing.  Locks
are not acquired until the first read or write operation.  The first read
operation against a database creates a SHARED lock and the first
write operation creates a RESERVED lock.   Because the acquisition of
locks is deferred until they are needed, it is possible that another
thread or process could create a separate transaction and write to
the database after the BEGIN on the current thread has executed.
If the transaction is immediate, then RESERVED locks
are acquired on all databases as soon as the BEGIN command is
executed, without waiting for the
database to be used.  After a BEGIN IMMEDIATE, you are guaranteed that
no other thread or process will be able to write to the database or
do a BEGIN IMMEDIATE or BEGIN EXCLUSIVE.  Other processes can continue
to read from the database, however.  An exclusive transaction causes
EXCLUSIVE locks to be acquired on all databases.  After a BEGIN
EXCLUSIVE, you are guaranteed that no other thread or process will
be able to read or write the database until the transaction is
complete.
</p>

<p>
A description of the meaning of SHARED, RESERVED, and EXCLUSIVE locks
is available <a href="lockingv3.html">separately</a>.
</p>

<p>
The COMMIT command does not actually perform a commit until all
pending SQL commands finish.  Thus if two or more SELECT statements
are in the middle of processing and a COMMIT is executed, the commit
will not actually occur until all SELECT statements finish.

</p>

<p>
An attempt to execute COMMIT might result in an SQLITE_BUSY return code.
This indicates that another thread or process had a read lock on the database

that prevented the database from being updated.  When COMMIT fails in this
way, the transaction remains active and the COMMIT can be retried later
after the reader has had a chance to clear.
</p>

<h3>Response To Errors Within A Transaction</h3>

<p>If certain kinds of errors occur within a transaction, the
transaction may or may not be rolled back automatically.  The
errors that cause the behavior include:</p>

<ul>
<li> SQLITE_FULL: database or disk full
<li> SQLITE_IOERR: disk I/O error
<li> SQLITE_BUSY: database in use by another process
<li> SQLITE_NOMEM: out or memory
<li> SQLITE_INTERRUPT: processing interrupted by user request

</ul>

<p>
For all of these errors, SQLite attempts to undo just the one statement
it was working on and leave changes from prior statements within the
same transaction intact and continue with the transaction.  However, 
depending on the statement being evaluated and the point at which the







|










|








|













|
|



|






|






<
<
<
<
<

|

|
>



|
|
>












|
|
|
|
|
>







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
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
ROLLBACK [TRANSACTION [<name>]]
}
</tcl>

<p>
No changes can be made to the database except within a transaction.
Any command that changes the database (basically, any SQL command
other than [SELECT]) will automatically start a transaction if
one is not already in effect.  Automatically started transactions
are committed at the conclusion of the command.
</p>

<p>
Transactions can be started manually using the BEGIN
command.  Such transactions usually persist until the next
COMMIT or ROLLBACK command.  But a transaction will also 
ROLLBACK if the database is closed or if an error occurs
and the ROLLBACK conflict resolution algorithm is specified.
See the documentation on the [ON CONFLICT]
clause for additional information about the ROLLBACK
conflict resolution algorithm.
</p>

<p>
END TRANSACTION is an alias for COMMIT.
</p>

<p>The optional transaction name is ignored. SQLite 
does not recognize nested transactions at this time.
However, future versions of SQLite may be enhanced to support nested
transactions and the transaction name would then become significant.
Application are advised not to use the transaction name in order
to avoid future compatibility problems.</p>

<p>
Transactions can be deferred, immediate, or exclusive.  
The default transaction behavior is deferred.
Deferred means that no locks are acquired
on the database until the database is first accessed.  Thus with a
deferred transaction, the BEGIN statement itself does nothing.  Locks
are not acquired until the first read or write operation.  The first read
operation against a database creates a [SHARED] lock and the first
write operation creates a [RESERVED] lock.   Because the acquisition of
locks is deferred until they are needed, it is possible that another
thread or process could create a separate transaction and write to
the database after the BEGIN on the current thread has executed.
If the transaction is immediate, then [RESERVED] locks
are acquired on all databases as soon as the BEGIN command is
executed, without waiting for the
database to be used.  After a BEGIN IMMEDIATE, you are guaranteed that
no other thread or process will be able to write to the database or
do a BEGIN IMMEDIATE or BEGIN EXCLUSIVE.  Other processes can continue
to read from the database, however.  An exclusive transaction causes
[EXCLUSIVE] locks to be acquired on all databases.  After a BEGIN
EXCLUSIVE, you are guaranteed that no other thread or process will
be able to read or write the database until the transaction is
complete.
</p>

<p>





The COMMIT command does not actually perform a commit until all
pending SQL commands finish.  Thus if one or more [SELECT] statements
are in the middle of processing and a COMMIT is executed, the commit
will not actually occur until all [SELECT] statements have been
[sqlite3_reset() | reset] or [sqlite3_finalize() | finalized].
</p>

<p>
An attempt to execute COMMIT might result in an [SQLITE_BUSY] return code.
This indicates that another thread or process had a 
[shared lock] on the database
that prevented the database from being updated.  When COMMIT fails in this
way, the transaction remains active and the COMMIT can be retried later
after the reader has had a chance to clear.
</p>

<h3>Response To Errors Within A Transaction</h3>

<p>If certain kinds of errors occur within a transaction, the
transaction may or may not be rolled back automatically.  The
errors that cause the behavior include:</p>

<ul>
<li> [SQLITE_FULL]: database or disk full
<li> [SQLITE_IOERR]: disk I/O error
<li> [SQLITE_BUSY]: database in use by another process
<li> [SQLITE_NOMEM]: out or memory
<li> [SQLITE_INTERRUPT]: processing [sqlite3_interrupt|interrupted]
     by application request
</ul>

<p>
For all of these errors, SQLite attempts to undo just the one statement
it was working on and leave changes from prior statements within the
same transaction intact and continue with the transaction.  However, 
depending on the statement being evaluated and the point at which the
396
397
398
399
400
401
402
403

404
405

406







407
408
409
410
411
412
413
414
415

416
417
418
419

420
421
422
423
424
425
426
427
428
<name> [ COLLATE <collation-name>] [ ASC | DESC ]
}
</tcl>

<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 the sort order is ignored in the current

implementation.  Sorting is always done in ascending order.</p>








<p>The COLLATE clause following each column name defines a collating
sequence used for text entires in that column.  The default collating
sequence is the collating sequence defined for that column in the
CREATE TABLE statement.  Or if no collating sequence is otherwise defined,
the built-in BINARY collating sequence is used.</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>If the UNIQUE keyword appears between CREATE and INDEX then duplicate
index entries are not allowed.  Any attempt to insert a duplicate entry
will result in an error.</p>


<p>The exact text
of each CREATE INDEX statement is stored in the <b>sqlite_master</b>
or <b>sqlite_temp_master</b> table, depending on whether the table
being indexed is temporary.  Every time 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>








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




|



|
>



|
>

|







390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
<name> [ COLLATE <collation-name>] [ ASC | DESC ]
}
</tcl>

<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.</p>

<p>Each column name can be followed by one of the "ASC" or "DESC" keywords
to indicate sort order.  The sort order may or may not be ignored depending
on the database file format.  The "legacy" file format ignores index
sort order.  The descending index file format takes index sort order
into account.  Only copies of SQLite newer than [version 3.3.0] 
(released on 2006-01-10) are able to understand the newer descending
index file format and so for compatibility with older versions of
SQLite, the legacy file format is generated by default.  Use the
[legacy_file_format] pragma to modify this behavior and generate
databases that use the newer file format.  Future versions of SQLite
may begin to generate the newer file format by default.</p>

<p>The COLLATE clause following each column name defines a collating
sequence used for text entires in that column.  The default collating
sequence is the collating sequence defined for that column in the
[CREATE TABLE] statement.  Or if no collating sequence is otherwise defined,
the built-in BINARY collating sequence is used.</p>

<p>There are no arbitrary limits on the number of indices that can be
attached to a single table.  The number of columns in an index is 
limited to [SQLITE_MAX_COLUMN].</p>

<p>If the UNIQUE keyword appears between CREATE and INDEX then duplicate
index entries are not allowed.  Any attempt to insert a duplicate entry
will result in an error.  For the purposes of unique indices, all NULL values
are considered to different from all other NULL values and are thus unique.</p>

<p>The text
of each CREATE INDEX statement is stored in the <b>sqlite_master</b>
or <b>sqlite_temp_master</b> table, depending on whether the table
being indexed is temporary.  Every time 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>

464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482


483
484
485
486
487
488

489
490
491
492
493
494
495
496
497
498

499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
} {conflict-clause} {
ON CONFLICT <conflict-algorithm>
}
</tcl>

<p>A CREATE TABLE statement is basically the keywords "CREATE TABLE"
followed by the name of a new table and a parenthesized list of column
definitions and constraints.  The table name can be either an identifier
or a string.  Tables names that begin with "<b>sqlite_</b>" are reserved
for use by the engine.</p>

<p>Each column definition is the name of the column followed by the
datatype for that column, then one or more optional column constraints.
The datatype for the column does not restrict what data may be put
in that column.
See <a href="datatype3.html">Datatypes In SQLite Version 3</a> for
additional information.
The UNIQUE constraint causes an index to be created on the specified
columns.  This index must contain unique keys.


The COLLATE clause specifies what text <a href="datatype3.html#collation">
collating function</a> to use when comparing text entries for the column.  
The built-in BINARY collating function is used by default.
<p>
The DEFAULT constraint specifies a default value to use when doing an INSERT.
The value may be NULL, a string constant or a number. Starting with version

3.1.0, the default value may also be one of the special case-independant
keywords CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP. If the value is
NULL, a string constant or number, it is literally inserted into the column
whenever an INSERT statement that does not specify a value for the column is
executed. If the value is CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP, then
the current UTC date and/or time is inserted into the columns. For
CURRENT_TIME, the format is HH:MM:SS. For CURRENT_DATE, YYYY-MM-DD. The format
for CURRENT_TIMESTAMP is "YYYY-MM-DD HH:MM:SS".
</p>


<p>Specifying a PRIMARY KEY normally just creates a UNIQUE index
on the corresponding columns.  However, if primary key is on a single column
that has datatype INTEGER, then that column is used internally
as the actual key of the B-Tree for the table.  This means that the column
may only hold unique integer values.  (Except for this one case,
SQLite ignores the datatype specification of columns and allows
any kind of data to be put in a column regardless of its declared
datatype.)  If a table does not have an INTEGER PRIMARY KEY column,
then the B-Tree key will be a automatically generated integer.
<a name="rowid"></a> The
B-Tree key for a row can always be accessed using one of the
special names "<b>ROWID</b>", "<b>OID</b>", or "<b>_ROWID_</b>".
This is true regardless of whether or not there is an INTEGER
PRIMARY KEY.  An INTEGER PRIMARY KEY column can also include the
keyword AUTOINCREMENT.  The AUTOINCREMENT keyword modified the way
that B-Tree keys are automatically generated.  Additional detail
on automatic B-Tree key generation is available
<a href="autoinc.html">separately</a>.</p>

<p>According to the SQL standard, PRIMARY KEY should imply NOT NULL.
Unfortunately, due to a long-standing coding oversight, this is not 
the case in SQLite.  SQLite allows NULL values







|
|








|
|
>
>
|
|
|

|
|
>
|









>









<
|



|







469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516

517
518
519
520
521
522
523
524
525
526
527
528
} {conflict-clause} {
ON CONFLICT <conflict-algorithm>
}
</tcl>

<p>A CREATE TABLE statement is basically the keywords "CREATE TABLE"
followed by the name of a new table and a parenthesized list of column
definitions and constraints.  
Tables names that begin with "<b>sqlite_</b>" are reserved
for use by the engine.</p>

<p>Each column definition is the name of the column followed by the
datatype for that column, then one or more optional column constraints.
The datatype for the column does not restrict what data may be put
in that column.
See <a href="datatype3.html">Datatypes In SQLite Version 3</a> for
additional information.
The UNIQUE constraint causes an unique index to be created on the specified
columns.  All NULL values are considered different from each other and from
all other values for the purpose of determining uniqueness, hence a UNIQUE
column may contain multiple entries with the value of NULL.
The COLLATE clause specifies what text 
[collating function] to use when comparing text entries for the column.  
The built-in [BINARY] collating function is used by default.
<p>
The DEFAULT constraint specifies a default value to use when doing an [INSERT].
The value may be NULL, a string constant or a number. Starting with
[version 3.1.0],
the default value may also be one of the special case-independant
keywords CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP. If the value is
NULL, a string constant or number, it is literally inserted into the column
whenever an INSERT statement that does not specify a value for the column is
executed. If the value is CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP, then
the current UTC date and/or time is inserted into the columns. For
CURRENT_TIME, the format is HH:MM:SS. For CURRENT_DATE, YYYY-MM-DD. The format
for CURRENT_TIMESTAMP is "YYYY-MM-DD HH:MM:SS".
</p>

<tcl>hd_fragment rowid {INTEGER PRIMARY KEY} ROWID</tcl>
<p>Specifying a PRIMARY KEY normally just creates a UNIQUE index
on the corresponding columns.  However, if primary key is on a single column
that has datatype INTEGER, then that column is used internally
as the actual key of the B-Tree for the table.  This means that the column
may only hold unique integer values.  (Except for this one case,
SQLite ignores the datatype specification of columns and allows
any kind of data to be put in a column regardless of its declared
datatype.)  If a table does not have an INTEGER PRIMARY KEY column,
then the B-Tree key will be a automatically generated integer.

The B-Tree key for a row can always be accessed using one of the
special names "<b>ROWID</b>", "<b>OID</b>", or "<b>_ROWID_</b>".
This is true regardless of whether or not there is an INTEGER
PRIMARY KEY.  An INTEGER PRIMARY KEY column can also include the
keyword [AUTOINCREMENT].  The [AUTOINCREMENT] keyword modified the way
that B-Tree keys are automatically generated.  Additional detail
on automatic B-Tree key generation is available
<a href="autoinc.html">separately</a>.</p>

<p>According to the SQL standard, PRIMARY KEY should imply NOT NULL.
Unfortunately, due to a long-standing coding oversight, this is not 
the case in SQLite.  SQLite allows NULL values
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562

<p> If a &lt;database-name&gt; is specified, then the table is created in 
the named database. It is an error to specify both a &lt;database-name&gt;
and the TEMP keyword, unless the &lt;database-name&gt; is "temp". If no
database name is specified, and the TEMP keyword is not present,
the table is created in the main database.</p>

<p>The optional conflict-clause following each constraint
allows the specification of an alternative default
constraint conflict resolution algorithm for that constraint.
The default is abort ABORT.  Different constraints within the same
table may have different default conflict resolution algorithms.
If an COPY, INSERT, or UPDATE command specifies a different conflict
resolution algorithm, then that algorithm is used in place of the
default algorithm specified in the CREATE TABLE statement.
See the section titled
<a href="lang_conflict.html">ON CONFLICT</a> for additional information.</p>

<p>CHECK constraints are supported as of version 3.3.0.  Prior
to version 3.3.0, CHECK constraints were parsed but not enforced.</p>

<p>The number of columns in a table is limited by the
[SQLITE_MAX_COLUMN] compile-time parameter.
A single row of a table cannot store more than
[SQLITE_MAX_LENGTH] bytes of data.
Both of these limits can be lowered at runtime using the







|




|



|

|







545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570

<p> If a &lt;database-name&gt; is specified, then the table is created in 
the named database. It is an error to specify both a &lt;database-name&gt;
and the TEMP keyword, unless the &lt;database-name&gt; is "temp". If no
database name is specified, and the TEMP keyword is not present,
the table is created in the main database.</p>

<p>The optional [conflict clause] following each constraint
allows the specification of an alternative default
constraint conflict resolution algorithm for that constraint.
The default is abort ABORT.  Different constraints within the same
table may have different default conflict resolution algorithms.
If an [INSERT] or [UPDATE] statement specifies a different conflict
resolution algorithm, then that algorithm is used in place of the
default algorithm specified in the CREATE TABLE statement.
See the section titled
[ON CONFLICT] for additional information.</p>

<p>CHECK constraints are supported as of [version 3.3.0].  Prior
to version 3.3.0, CHECK constraints were parsed but not enforced.</p>

<p>The number of columns in a table is limited by the
[SQLITE_MAX_COLUMN] compile-time parameter.
A single row of a table cannot store more than
[SQLITE_MAX_LENGTH] bytes of data.
Both of these limits can be lowered at runtime using the
622
623
624
625
626
627
628
629

630
631
632
633
634
635
636
637
</tcl>

<p>The CREATE TRIGGER statement is used to add triggers to the 
database schema. Triggers are database operations (the <i>trigger-action</i>) 
that are automatically performed when a specified database event (the
<i>database-event</i>) occurs.  </p>

<p>A trigger may be specified to fire whenever a DELETE, INSERT or UPDATE of a

particular database table occurs, or whenever an UPDATE of one or more
specified columns of a table are updated.</p>

<p>At this time SQLite supports only FOR EACH ROW triggers, not FOR EACH
STATEMENT triggers. Hence explicitly specifying FOR EACH ROW is optional.  FOR
EACH ROW implies that the SQL statements specified as <i>trigger-steps</i> 
may be executed (depending on the WHEN clause) for each database row being
inserted, updated or deleted by the statement causing the trigger to fire.</p>







|
>
|







630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
</tcl>

<p>The CREATE TRIGGER statement is used to add triggers to the 
database schema. Triggers are database operations (the <i>trigger-action</i>) 
that are automatically performed when a specified database event (the
<i>database-event</i>) occurs.  </p>

<p>A trigger may be specified to fire whenever a [DELETE], [INSERT],
or [UPDATE] of a
particular database table occurs, or whenever an [UPDATE] of one or more
specified columns of a table are updated.</p>

<p>At this time SQLite supports only FOR EACH ROW triggers, not FOR EACH
STATEMENT triggers. Hence explicitly specifying FOR EACH ROW is optional.  FOR
EACH ROW implies that the SQL statements specified as <i>trigger-steps</i> 
may be executed (depending on the WHEN clause) for each database row being
inserted, updated or deleted by the statement causing the trigger to fire.</p>
655
656
657
658
659
660
661
662



663
664
665
666
667
668
669
670
671
672
673
674
675

676
677

678
679

680
681
682
683
684
685
686
687
688
689
<tr>
<td valign="top" align="right" width=120><i>DELETE</i></td>
<td valign="top">OLD references are valid</td>
</tr>
</table>
</p>

<p>If a WHEN clause is supplied, the SQL statements specified as <i>trigger-steps</i> are only executed for rows for which the WHEN clause is true. If no WHEN clause is supplied, the SQL statements are executed for all rows.</p>




<p>The specified <i>trigger-time</i> determines when the <i>trigger-steps</i>
will be executed relative to the insertion, modification or removal of the
associated row.</p>

<p>An ON CONFLICT clause may be specified as part of an UPDATE or INSERT
<i>trigger-step</i>. However if an ON CONFLICT clause is specified as part of 
the statement causing the trigger to fire, then this conflict handling
policy is used instead.</p>

<p>Triggers are automatically dropped when the table that they are 
associated with is dropped.</p>


<p>Triggers may be created on views, as well as ordinary tables, by specifying
INSTEAD OF in the CREATE TRIGGER statement. If one or more ON INSERT, ON DELETE

or ON UPDATE triggers are defined on a view, then it is not an error to execute
an INSERT, DELETE or UPDATE statement on the view, respectively. Thereafter,

executing an INSERT, DELETE or UPDATE on the view causes the associated
  triggers to fire. The real tables underlying the view are not modified
  (except possibly explicitly, by a trigger program).</p>

<p><b>Example:</b></p>

<p>Assuming that customer records are stored in the "customers" table, and
that order records are stored in the "orders" table, the following trigger
ensures that all associated orders are redirected when a customer changes
his or her address:</p>







|
>
>
>





|
|






>
|
|
>
|
|
>

|
|







664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
<tr>
<td valign="top" align="right" width=120><i>DELETE</i></td>
<td valign="top">OLD references are valid</td>
</tr>
</table>
</p>

<p>If a WHEN clause is supplied, the SQL statements specified as 
<i>trigger-steps</i> are only executed for rows for which the WHEN
clause is true. If no WHEN clause is supplied, the SQL statements
are executed for all rows.</p>

<p>The specified <i>trigger-time</i> determines when the <i>trigger-steps</i>
will be executed relative to the insertion, modification or removal of the
associated row.</p>

<p>An [ON CONFLICT] clause may be specified as part of an [UPDATE] or [INSERT]
<i>trigger-step</i>. However if an [ON CONFLICT] clause is specified as part of 
the statement causing the trigger to fire, then this conflict handling
policy is used instead.</p>

<p>Triggers are automatically dropped when the table that they are 
associated with is dropped.</p>

<tcl>hd_fragment instead_of_trigger {INSTEAD OF} {INSTEAD OF trigger}</tcl>
<p>Triggers may be created on [views], as well as ordinary tables, by
specifying INSTEAD OF in the CREATE TRIGGER statement. 
If one or more ON INSERT, ON DELETE
or ON UPDATE triggers are defined on a view, then it is not an
error to execute an INSERT, DELETE or UPDATE statement on the view, 
respectively. Thereafter,
executing an INSERT, DELETE or UPDATE on the view causes the associated
triggers to fire. The real tables underlying the view are not modified
(except possibly explicitly, by a trigger program).</p>

<p><b>Example:</b></p>

<p>Assuming that customer records are stored in the "customers" table, and
that order records are stored in the "orders" table, the following trigger
ensures that all associated orders are redirected when a customer changes
his or her address:</p>
704
705
706
707
708
709
710
711
712
713
714
715
716
717

718
719
720
721
722
723
724
725
726
727
728
729



730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
<p>causes the following to be automatically executed:</p>

<tcl>Example {
UPDATE orders SET address = '1 Main St.' WHERE customer_name = 'Jack Jones';
}</tcl>

<p>Note that currently, triggers may behave oddly when created on tables
  with INTEGER PRIMARY KEY fields. If a BEFORE trigger program modifies the 
  INTEGER PRIMARY KEY field of a row that will be subsequently updated by the
  statement that causes the trigger to fire, then the update may not occur. 
  The workaround is to declare the table with a PRIMARY KEY column instead
  of an INTEGER PRIMARY KEY column.</p>

<p>A special SQL function RAISE() may be used within a trigger-program, with the following syntax</p> 


<tcl>
###############################################################################
Syntax {raise-function} {
RAISE ( ABORT, <error-message> ) | 
RAISE ( FAIL, <error-message> ) | 
RAISE ( ROLLBACK, <error-message> ) | 
RAISE ( IGNORE )
}
</tcl>

<p>When one of the first three forms is called during trigger-program execution, the specified ON CONFLICT processing is performed (either ABORT, FAIL or 



 ROLLBACK) and the current query terminates. An error code of SQLITE_CONSTRAINT is returned to the user, along with the specified error message.</p>

<p>When RAISE(IGNORE) is called, the remainder of the current trigger program,
the statement that caused the trigger program to execute and any subsequent
    trigger programs that would of been executed are abandoned. No database
    changes are rolled back.  If the statement that caused the trigger program
    to execute is itself part of a trigger program, then that trigger program
    resumes execution at the beginning of the next step.
</p>

<p>Triggers are removed using the 
<a href="lang_droptrigger.html">DROP TRIGGER</a> statement.</p>


<tcl>
###############################################################################
Section {CREATE VIEW} {createview} {{CREATE VIEW}}

Syntax {sql-command} {
CREATE [TEMP | TEMPORARY] VIEW [IF NOT EXISTS] [<database-name>.] <view-name> AS <select-statement>
}
</tcl>

<p>The CREATE VIEW command assigns a name to a pre-packaged 
<a href="lang_select.html">SELECT</a>
statement.  Once the view is created, it can be used in the FROM clause
of another SELECT in place of a table name.
</p>

<p>If the "TEMP" or "TEMPORARY" keyword occurs in between "CREATE"
and "VIEW" then the view that is created is only visible to the
process that opened the database and is automatically deleted when
the database is closed.</p>

<p> If a &lt;database-name&gt; is specified, then the view is created in 
the named database. It is an error to specify both a &lt;database-name&gt;
and the TEMP keyword, unless the &lt;database-name&gt; is "temp". If no
database name is specified, and the TEMP keyword is not present,
the table is created in the main database.</p>

<p>You cannot COPY, DELETE, INSERT or UPDATE a view.  Views are read-only 
in SQLite.  However, in many cases you can use a 
<a href="lang_createtrigger.html">TRIGGER</a> on the view to accomplish 
the same thing.  Views are removed 
with the <a href="lang_dropview.html">DROP VIEW</a> 
command.</p>

<tcl>
##############################################################################
Section {CREATE VIRTUAL TABLE} {createvtab} {{CREATE VIRTUAL_TABLE}}

Syntax {sql-command} {
CREATE VIRTUAL TABLE [<database-name> .] <table-name> USING <module-name> [( <arguments> )]







|
|


|

|
>











|
>
>
>
|



|
|
|
|


|
<




|









|













|
|
|

<
|







719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759

760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791

792
793
794
795
796
797
798
799
<p>causes the following to be automatically executed:</p>

<tcl>Example {
UPDATE orders SET address = '1 Main St.' WHERE customer_name = 'Jack Jones';
}</tcl>

<p>Note that currently, triggers may behave oddly when created on tables
  with [INTEGER PRIMARY KEY] fields. If a BEFORE trigger program modifies the 
  [INTEGER PRIMARY KEY] field of a row that will be subsequently updated by the
  statement that causes the trigger to fire, then the update may not occur. 
  The workaround is to declare the table with a PRIMARY KEY column instead
  of an [INTEGER PRIMARY KEY] column.</p>

<p>A special SQL function RAISE() may be used within a trigger-program,
with the following syntax</p> 

<tcl>
###############################################################################
Syntax {raise-function} {
RAISE ( ABORT, <error-message> ) | 
RAISE ( FAIL, <error-message> ) | 
RAISE ( ROLLBACK, <error-message> ) | 
RAISE ( IGNORE )
}
</tcl>

<p>When one of the first three forms is called during trigger-program
execution, the specified [ON CONFLICT] processing is performed
(either ABORT, FAIL or ROLLBACK) and the current query terminates.
An error code of [SQLITE_CONSTRAINT] is returned to the application,
along with the specified error message.</p>

<p>When RAISE(IGNORE) is called, the remainder of the current trigger program,
the statement that caused the trigger program to execute and any subsequent
trigger programs that would of been executed are abandoned. No database
changes are rolled back.  If the statement that caused the trigger program
to execute is itself part of a trigger program, then that trigger program
resumes execution at the beginning of the next step.
</p>

<p>Triggers are removed using the [DROP TRIGGER] statement.</p>



<tcl>
###############################################################################
Section {CREATE VIEW} {createview} {{CREATE VIEW} view views}

Syntax {sql-command} {
CREATE [TEMP | TEMPORARY] VIEW [IF NOT EXISTS] [<database-name>.] <view-name> AS <select-statement>
}
</tcl>

<p>The CREATE VIEW command assigns a name to a pre-packaged 
<a href="lang_select.html">SELECT</a>
statement.  Once the view is created, it can be used in the FROM clause
of another [SELECT] in place of a table name.
</p>

<p>If the "TEMP" or "TEMPORARY" keyword occurs in between "CREATE"
and "VIEW" then the view that is created is only visible to the
process that opened the database and is automatically deleted when
the database is closed.</p>

<p> If a &lt;database-name&gt; is specified, then the view is created in 
the named database. It is an error to specify both a &lt;database-name&gt;
and the TEMP keyword, unless the &lt;database-name&gt; is "temp". If no
database name is specified, and the TEMP keyword is not present,
the table is created in the main database.</p>

<p>You cannot [DELETE], [INSERT], or [UPDATE] a view.  Views are read-only 
in SQLite.  However, in many cases you can use an
[INSTEAD OF trigger] on the view to accomplish 
the same thing.  Views are removed 

with the [DROP VIEW] command.</p>

<tcl>
##############################################################################
Section {CREATE VIRTUAL TABLE} {createvtab} {{CREATE VIRTUAL_TABLE}}

Syntax {sql-command} {
CREATE VIRTUAL TABLE [<database-name> .] <table-name> USING <module-name> [( <arguments> )]
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830















831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
the SQLite database connection using
sqlite3_create_module()
prior to issuing the CREATE VIRTUAL TABLE statement.
The module takes zero or more comma-separated arguments.
The arguments can be just about any text as long as it has balanced
parentheses.  The argument syntax is sufficiently general that the
arguments can be made to appear as column definitions in a traditional
<a href="lang_createtable.html">CREATE TABLE</a> statement.  
SQLite passes the module arguments directly
to the module without any interpretation.  It is the responsibility
of the module implementation to parse and interpret its own arguments.</p>

<p>A virtual table is destroyed using the ordinary
<a href="lang_droptable.html">DROP TABLE</a> statement.  There is no
DROP VIRTUAL TABLE statement.</p>

<tcl>
##############################################################################
Section DELETE delete {DELETE DELETEs}

Syntax {sql-statement} {
DELETE FROM [<database-name> .] <table-name> [WHERE <expr>]
}
</tcl>

<p>The DELETE command is used to remove records from a table.
The command consists of the "DELETE FROM" keywords followed by
the name of the table from which records are to be removed.
</p>

<p>Without a WHERE clause, all rows of the table are removed.
If a WHERE clause is supplied, then only those rows that match
the expression are removed.</p>
















<tcl>
###############################################################################
Section {DETACH DATABASE} detach DETACH

Syntax {sql-command} {
DETACH [DATABASE] <database-name>
}
</tcl>

<p>This statement detaches an additional database connection previously 
attached using the <a href="lang_attach.html">ATTACH DATABASE</a> statement.  
It is possible to have the same database file attached multiple times using 
different names, and detaching one connection to a file will leave the 
others intact.</p>

<p>This statement will fail if SQLite is in the middle of a transaction.</p>


<tcl>
##############################################################################
Section {DROP INDEX} dropindex {{DROP INDEX}}

Syntax {sql-command} {
DROP INDEX [IF EXISTS] [<database-name> .] <index-name>
}
</tcl>

<p>The DROP INDEX statement removes an index added
with the <a href="lang_createindex.html">
CREATE INDEX</a> statement.  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>

<p>The DROP INDEX statement does not reduce the size of the database 
file in the default mode.
Empty space in the database is retained for later INSERTs.  To 
remove free space in the database, 
use the <a href="lang_vacuum.html">VACUUM</a> 
command.  If AUTOVACUUM mode is enabled for a database then space
will be freed automatically by DROP INDEX.</p>


<tcl>
##############################################################################
Section {DROP TABLE} droptable {{DROP TABLE}}

Syntax {sql-command} {
DROP TABLE [IF EXISTS] [<database-name>.] <table-name>
}
</tcl>

<p>The DROP TABLE statement removes a table added with the <a href=
"lang_createtable.html">CREATE TABLE</a> statement.  The name specified is the
table name.  It is completely removed from the database schema and the 
disk file.  The table can not be recovered.  All indices associated 
with the table are also deleted.</p>

<p>The DROP TABLE statement does not reduce the size of the database 
file in the default mode.  Empty space in the database is retained for
later INSERTs.  To 
remove free space in the database, 
use the <a href="lang_vacuum.html">VACUUM</a> 
command.  If AUTOVACUUM mode is enabled for a database then space
will be freed automatically by DROP TABLE.</p>

<p>The optional IF EXISTS clause suppresses the error that would normally
result if the table does not exist.</p>

<tcl>
##############################################################################
Section {DROP TRIGGER} droptrigger {{DROP TRIGGER}}
Syntax {sql-statement} {
DROP TRIGGER [IF EXISTS] [<database-name> .] <trigger-name>
}
</tcl>

<p>The DROP TRIGGER statement removes a trigger created by the 
<a href="lang_createtrigger.html">CREATE TRIGGER</a> statement.  The trigger is 
deleted from the database schema. Note that triggers are automatically 
dropped when the associated table is dropped.</p>

<tcl>
##############################################################################
Section {DROP VIEW} dropview {{DROP VIEW}}

Syntax {sql-command} {
DROP VIEW [IF EXISTS] <view-name>
}
</tcl>

<p>The DROP VIEW statement removes a view created by the <a href=
"lang_createview.html">CREATE VIEW</a> statement.  The name specified is the 
view name.  It is removed from the database schema, but no actual data 
in the underlying base tables is modified.</p>

<tcl>
##############################################################################
Section EXPLAIN explain EXPLAIN








|





|




















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










|

















<
|

|



|
|
<
|












|
|






|

|
|














|












|
|







814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890

891
892
893
894
895
896
897
898

899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
the SQLite database connection using
sqlite3_create_module()
prior to issuing the CREATE VIRTUAL TABLE statement.
The module takes zero or more comma-separated arguments.
The arguments can be just about any text as long as it has balanced
parentheses.  The argument syntax is sufficiently general that the
arguments can be made to appear as column definitions in a traditional
[CREATE TABLE] statement.  
SQLite passes the module arguments directly
to the module without any interpretation.  It is the responsibility
of the module implementation to parse and interpret its own arguments.</p>

<p>A virtual table is destroyed using the ordinary
[DROP TABLE] statement.  There is no
DROP VIRTUAL TABLE statement.</p>

<tcl>
##############################################################################
Section DELETE delete {DELETE DELETEs}

Syntax {sql-statement} {
DELETE FROM [<database-name> .] <table-name> [WHERE <expr>]
}
</tcl>

<p>The DELETE command is used to remove records from a table.
The command consists of the "DELETE FROM" keywords followed by
the name of the table from which records are to be removed.
</p>

<p>Without a WHERE clause, all rows of the table are removed.
If a WHERE clause is supplied, then only those rows that match
the expression are removed.</p>

<p>When the WHERE clause is omitted from a DELETE statement,
SQLite uses an optimization to erase the entire table content
without having to visit each row of the table individual.
This "truncate" optimization makes the delete run much faster.
However, it also means that the [sqlite3_changes()] interface
and the [count_changes] PRAGMA
will not actually return the number of deleted rows.  If you
need to delete every row of a table and still have
[sqlite3_changes()] and [count_changes] work correctly, then
use WHERE clause that is always true:</p>

<blockquote><pre>
DELETE FROM sometable WHERE 1;
</pre></blockquote>

<tcl>
###############################################################################
Section {DETACH DATABASE} detach DETACH

Syntax {sql-command} {
DETACH [DATABASE] <database-name>
}
</tcl>

<p>This statement detaches an additional database connection previously 
attached using the [ATTACH] statement.  
It is possible to have the same database file attached multiple times using 
different names, and detaching one connection to a file will leave the 
others intact.</p>

<p>This statement will fail if SQLite is in the middle of a transaction.</p>


<tcl>
##############################################################################
Section {DROP INDEX} dropindex {{DROP INDEX}}

Syntax {sql-command} {
DROP INDEX [IF EXISTS] [<database-name> .] <index-name>
}
</tcl>

<p>The DROP INDEX statement removes an index added

with the [CREATE INDEX] statement.  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>

<p>The DROP INDEX statement does not reduce the size of the database 
file in the default mode.
Empty space in the database is retained for later [INSERT] statements.  To 
remove free space in the database, use the [VACUUM]

command.  If [auto_vacuum] mode is enabled for a database then space
will be freed automatically by DROP INDEX.</p>


<tcl>
##############################################################################
Section {DROP TABLE} droptable {{DROP TABLE}}

Syntax {sql-command} {
DROP TABLE [IF EXISTS] [<database-name>.] <table-name>
}
</tcl>

<p>The DROP TABLE statement removes a table added with the
[CREATE TABLE] statement.  The name specified is the
table name.  It is completely removed from the database schema and the 
disk file.  The table can not be recovered.  All indices associated 
with the table are also deleted.</p>

<p>The DROP TABLE statement does not reduce the size of the database 
file in the default mode.  Empty space in the database is retained for
later [INSERT] statements.  To 
remove free space in the database, 
use the [VACUUM] statement.
If [auto_vacuum] mode is enabled for a database then space
will be freed automatically by DROP TABLE.</p>

<p>The optional IF EXISTS clause suppresses the error that would normally
result if the table does not exist.</p>

<tcl>
##############################################################################
Section {DROP TRIGGER} droptrigger {{DROP TRIGGER}}
Syntax {sql-statement} {
DROP TRIGGER [IF EXISTS] [<database-name> .] <trigger-name>
}
</tcl>

<p>The DROP TRIGGER statement removes a trigger created by the 
[CREATE TRIGGER] statement.  The trigger is 
deleted from the database schema. Note that triggers are automatically 
dropped when the associated table is dropped.</p>

<tcl>
##############################################################################
Section {DROP VIEW} dropview {{DROP VIEW}}

Syntax {sql-command} {
DROP VIEW [IF EXISTS] <view-name>
}
</tcl>

<p>The DROP VIEW statement removes a view created by the
[CREATE VIEW] statement.  The name specified is the 
view name.  It is removed from the database schema, but no actual data 
in the underlying base tables is modified.</p>

<tcl>
##############################################################################
Section EXPLAIN explain EXPLAIN

1051
1052
1053
1054
1055
1056
1057
1058
1059
1060
1061
1062
1063
1064
1065
1066
1067
1068
1069
A parameter specifies a placeholder in the expression for a literal
value that is filled in at runtime using the
[sqlite3_bind_blob() | sqlite3_bind()] APIs.
Parameters can take several forms:
</p

<blockquote>
<table class="pdf_functions">
<tr>
<td align="right" valign="top"><b>?</b><i>NNN</i></td><td width="20"></td>
<td>A question mark followed by a number <i>NNN</i> holds a spot for the
NNN-th parameter.  NNN must be between 1 and 999.</td>
</tr>
<tr>
<td align="right" valign="top"><b>?</b></td><td width="20"></td>
<td>A question mark that is not followed by a number holds a spot for
the next unused parameter.</td>
</tr>
<tr>







|



|







1081
1082
1083
1084
1085
1086
1087
1088
1089
1090
1091
1092
1093
1094
1095
1096
1097
1098
1099
A parameter specifies a placeholder in the expression for a literal
value that is filled in at runtime using the
[sqlite3_bind_blob() | sqlite3_bind()] APIs.
Parameters can take several forms:
</p

<blockquote>
<table>
<tr>
<td align="right" valign="top"><b>?</b><i>NNN</i></td><td width="20"></td>
<td>A question mark followed by a number <i>NNN</i> holds a spot for the
NNN-th parameter.  NNN must be between 1 and [SQLITE_MAX_VARIABLE_NUMBER].</td>
</tr>
<tr>
<td align="right" valign="top"><b>?</b></td><td width="20"></td>
<td>A question mark that is not followed by a number holds a spot for
the next unused parameter.</td>
</tr>
<tr>
1133
1134
1135
1136
1137
1138
1139
1140
1141
1142
1143
1144
1145
1146
1147
1148
</p>

<tcl>hd_fragment glob GLOB</tcl>
<p>The GLOB operator is similar to LIKE but uses the Unix
file globbing syntax for its wildcards.  Also, GLOB is case
sensitive, unlike LIKE.  Both GLOB and LIKE may be preceded by
the NOT keyword to invert the sense of the test.  The infix GLOB 
operator is implemented by calling the user function <a href="#globFunc">
glob(<i>X</i>,<i>Y</i>)</a> and can be modified by overriding
that function.</p>

<tcl>hd_fragment regexp REGEXP</tcl>
<p>The REGEXP operator is a special syntax for the regexp()
user function.  No regexp() user function is defined by default
and so use of the REGEXP operator will normally result in an
error message.  If a user-defined function named "regexp"







|
|







1163
1164
1165
1166
1167
1168
1169
1170
1171
1172
1173
1174
1175
1176
1177
1178
</p>

<tcl>hd_fragment glob GLOB</tcl>
<p>The GLOB operator is similar to LIKE but uses the Unix
file globbing syntax for its wildcards.  Also, GLOB is case
sensitive, unlike LIKE.  Both GLOB and LIKE may be preceded by
the NOT keyword to invert the sense of the test.  The infix GLOB 
operator is implemented by calling the function
[GLOB | glob(<i>X</i>,<i>Y</i>)] and can be modified by overriding
that function.</p>

<tcl>hd_fragment regexp REGEXP</tcl>
<p>The REGEXP operator is a special syntax for the regexp()
user function.  No regexp() user function is defined by default
and so use of the REGEXP operator will normally result in an
error message.  If a user-defined function named "regexp"
1195
1196
1197
1198
1199
1200
1201
1202
1203
1204
1205
1206
1207
1208
1209
1210

1211


















1212
1213
1214
1215
1216
1217
1218
1219
1220

1221
1222
1223
1224
1225
1226
1227
1228
1229
1230
1231
1232
1233
1234
1235

1236
1237
1238
1239
1240
1241
1242
1243
1244
1245
1246
1247
1248
1249
1250
1251
1252
1253
1254
1255
1256
1257
1258
1259
1260

1261
1262
1263
1264
1265
1266
1267
1268
1269
1270
1271
1272
1273
1274
1275
1276
1277
1278
1279
1280
1281
1282
1283
1284
1285
1286
1287
1288
1289
1290
1291
1292
1293
1294
1295
1296
1297
1298
1299
1300
1301
1302
1303
1304
1305
1306
1307
1308
1309
1310
1311
1312
1313
1314
1315
1316
1317
1318
1319
1320
1321
1322
1323
1324
1325
1326
1327
1328
1329

1330
1331
1332
1333
1334
1335

1336
1337
1338
1339
1340
1341
1342
1343
1344
1345
1346
1347
1348
1349
1350
1351
1352
1353
1354
1355
1356
1357
1358
1359
1360
1361
1362
1363
1364
1365
1366
1367
1368
1369
1370
1371
1372
1373
1374
1375
1376
1377
1378
1379
1380
1381
1382
1383
1384



1385
1386
1387
1388
1389


1390

1391




1392
1393


1394


1395
1396
1397
1398
1399
1400
1401
1402
1403
1404
1405
1406
1407
1408
1409
1410
1411
1412
1413
1414
1415
1416
1417
1418
1419
1420
1421
1422
1423
1424
1425
1426
1427
1428
1429
1430
1431
1432
1433
1434
1435
1436
1437
1438
1439
1440
1441
1442
1443
1444
1445
1446
1447
1448
1449
1450
1451
1452
1453
1454
1455
1456
1457
1458
1459
1460
1461
1462
1463
1464
1465
1466

1467
1468


1469

1470
1471
1472
1473
1474
1475

1476

1477
1478
1479
1480
1481
1482
1483
1484
1485
1486
1487
1488
1489
1490
1491
1492
1493
1494
1495
1496
1497
1498
1499
1500
1501
1502
1503
1504
1505
1506
1507
1508
1509
1510
1511
1512
1513
1514
1515
1516
1517
1518
1519
1520

1521
1522
1523
1524
1525
1526

1527
1528

1529
1530
1531
1532
1533
1534
1535
1536
1537
1538
1539
1540
1541
1542
1543
1544
1545
1546
1547
1548
1549
1550
1551
1552
1553

1554
1555
1556
1557
1558
1559
1560
1561
SELECT becomes the value used in the expression.  If the SELECT yields
more than one result row, all rows after the first are ignored.  If
the SELECT yields no rows, then the value of the SELECT is NULL.</p>

<p>A CAST expression changes the datatype of the <expr> into the
type specified by &lt;type&gt;. 
&lt;type&gt; can be any non-empty type name that is valid
for the type in a column definition of a CREATE TABLE statement.</p>

<p>Both simple and aggregate functions are supported.  A simple
function can be used in any expression.  Simple functions return
a result immediately based on their inputs.  Aggregate functions
may only be used in a SELECT statement.  Aggregate functions compute
their result across all rows of the result set.</p>

<a name="corefunctions"></a>

<b>Core Functions</b>



















<p>The core functions shown below are available by default.  Additional
functions may be written in C and added to the database engine using
the [sqlite3_create_function()] API.</p>

<table border=0 cellpadding=10 class="pdf_functions">
<tr>
<td valign="top" align="right" width=120>abs(<i>X</i>)</td>
<td valign="top">Return the absolute value of argument <i>X</i>.</td>

</tr>

<tr>
<td valign="top" align="right">coalesce(<i>X</i>,<i>Y</i>,...)</td>
<td valign="top">Return a copy of the first non-NULL argument.  If
all arguments are NULL then NULL is returned.  There must be at least 
2 arguments.</td>
</tr>

<tr>
<td valign="top" align="right">
<a name="globFunc"></a>
glob(<i>X</i>,<i>Y</i>)</td>
<td valign="top">This function is used to implement the
"<b>X GLOB Y</b>" syntax of SQLite.  The [sqlite3_create_function()]

interface can
be used to override this function and thereby change the operation
of the [GLOB] operator.</td>
</tr>

<tr>
<td valign="top" align="right">ifnull(<i>X</i>,<i>Y</i>)</td>
<td valign="top">Return a copy of the first non-NULL argument.  If
both arguments are NULL then NULL is returned. This behaves the same as 
<b>coalesce()</b> above.</td>
</tr>

<tr>
<td valign="top" align="right">
<a name="hexFunc"></a>
hex(<i>X</i>)</td>
<td valign="top">The argument is interpreted as a BLOB.  The result
is a hexadecimal rendering of the content of that blob.</td>
</tr>

<tr>
<td valign="top" align="right">last_insert_rowid()</td>
<td valign="top">Return the <a href="lang_createtable.html#rowid">ROWID</a>
of the last row insert from this
connection to the database.  This is the same value that would be returned

from the <b>sqlite_last_insert_rowid()</b> API function.</td>
</tr>

<tr>
<td valign="top" align="right">length(<i>X</i>)</td>
<td valign="top">Return the string length of <i>X</i> in characters.
If SQLite is configured to support UTF-8, then the number of UTF-8
characters is returned, not the number of bytes.</td>
</tr>

<tr>
<td valign="top" align="right">
<a name="likeFunc"></a>
like(<i>X</i>,<i>Y</i>)<br>
like(<i>X</i>,<i>Y</i>,<i>Z</i>)</td>
<td valign="top">
This function is used to implement the "<b>X LIKE Y &#91;ESCAPE Z&#93;</b>"
syntax of SQL. If the optional ESCAPE clause is present, then the
user-function is invoked with three arguments. Otherwise, it is
invoked with two arguments only. The 
<a href="c3ref/create_function.html">
sqlite_create_function()</a> interface can be used to override this
function and thereby change the operation of the <a
href= "#like">LIKE</a> operator. When doing this, it may be important
to override both the two and three argument versions of the like() 
function. Otherwise, different code may be called to implement the
LIKE operator depending on whether or not an ESCAPE clause was 
specified.</td>
</tr>

<tr>
<td valign="top" align="right">load_extension(<i>X</i>)<br>
load_extension(<i>X</i>,<i>Y</i>)</td>
<td valign="top">Load SQLite extensions out of the shared library
file named <i>X</i> using the entry point <i>Y</i>.  The result
is a NULL.  If <i>Y</i> is omitted then the default entry point
of <b>sqlite3_extension_init</b> is used.  This function raises
an exception if the extension fails to load or initialize correctly.

<p>This function will fail if the extension attempts to modify
or delete a SQL function or collating sequence.  The
extension can add new functions or collating sequences, but cannot
modify or delete existing functions or collating sequences because
those functions and/or collating sequences might be used elsewhere
in the currently running SQL statement.  To load an extension that
changes or deletes functions or collating sequences, use the
[sqlite3_load_extension()] C-language API.</p>
</tr>

<tr>
<td valign="top" align="right">lower(<i>X</i>)</td>
<td valign="top">Return a copy of string <i>X</i> will all characters
converted to lower case.  The C library <b>tolower()</b> routine is used
for the conversion, which means that this function might not
work correctly on UTF-8 characters.</td>
</tr>

<tr>
<td valign="top" align="right">
<a name="ltrimFunc"></a>
ltrim(<i>X</i>)<br>ltrim(<i>X</i>,<i>Y</i>)</td>
<td valign="top">Return a string formed by removing any and all
characters that appear in <i>Y</i> from the left side of <i>X</i>.
If the <i>Y</i> argument is omitted, spaces are removed.</td>
</tr>


<tr>
<td valign="top" align="right">max(<i>X</i>,<i>Y</i>,...)</td>

<td valign="top">Return the argument with the maximum value.  Arguments
may be strings in addition to numbers.  The maximum value is determined
by the usual sort order.  Note that <b>max()</b> is a simple function when
it has 2 or more arguments but converts to an aggregate function if given
only a single argument.</td>
</tr>


<tr>
<td valign="top" align="right">min(<i>X</i>,<i>Y</i>,...)</td>
<td valign="top">Return the argument with the minimum value.  Arguments
may be strings in addition to numbers.  The minimum value is determined
by the usual sort order.  Note that <b>min()</b> is a simple function when
it has 2 or more arguments but converts to an aggregate function if given
only a single argument.</td>
</tr>

<tr>
<td valign="top" align="right">nullif(<i>X</i>,<i>Y</i>)</td>
<td valign="top">Return the first argument if the arguments are different, 
otherwise return NULL.</td>
</tr>

<tr>
<td valign="top" align="right">quote(<i>X</i>)</td>
<td valign="top">This routine returns a string which is the value of
its argument suitable for inclusion into another SQL statement.
Strings are surrounded by single-quotes with escapes on interior quotes
as needed.  BLOBs are encoded as hexadecimal literals.
The current implementation of [VACUUM] uses this function.  The function
is also useful when writing triggers to implement undo/redo functionality.
</td>
</tr>

<tr>
<td valign="top" align="right">random(*)</td>
<td valign="top">Return a pseudo-random integer
between -9223372036854775808 and +9223372036854775807.</td>
</tr>

<tr>
<td valign="top" align="right">
<a name="replaceFunc"></a>
replace(<i>X</i>,<i>Y</i>,<i>Z</i>)</td>
<td valign="top">Return a string formed by substituting string <i>Z</i> for
every occurrance of string <i>Y</i> in string <i>X</i>.  The BINARY
collating sequence is used for comparisons.</td>
</tr>

<tr>
<td valign="top" align="right">
<a name="randomblobFunc"></a>
randomblob(<i>N</i>)</td>
<td valign="top">Return a <i>N</i>-byte blob containing pseudo-random bytes.
<i>N</i> should be a postive integer.</td>
</tr>




<tr>
<td valign="top" align="right">round(<i>X</i>)<br>round(<i>X</i>,<i>Y</i>)</td>
<td valign="top">Round off the number <i>X</i> to <i>Y</i> digits to the
right of the decimal point.  If the <i>Y</i> argument is omitted, 0 is 


assumed.</td>

</tr>





<tr>


<td valign="top" align="right">


<a name="rtrimFunc"></a>
rtrim(<i>X</i>)<br>rtrim(<i>X</i>,<i>Y</i>)</td>
<td valign="top">Return a string formed by removing any and all
characters that appear in <i>Y</i> from the right side of <i>X</i>.
If the <i>Y</i> argument is omitted, spaces are removed.</td>
</tr>

<tr>
<td valign="top" align="right">soundex(<i>X</i>)</td>
<td valign="top">Compute the soundex encoding of the string <i>X</i>.
The string "?000" is returned if the argument is NULL.
This function is omitted from SQLite by default.
It is only available the -DSQLITE_SOUNDEX=1 compiler option
is used when SQLite is built.</td>
</tr>

<tr>
<td valign="top" align="right">sqlite_version(*)</td>
<td valign="top">Return the version string for the SQLite library
that is running.  Example:  "2.8.0"</td>
</tr>

<tr>
<td valign="top" align="right">
  substr(<i>X</i>,<i>Y</i>,<i>Z</i>)<br>
  substr(<i>X</i>,<i>Y</i>)</td>
<td valign="top">Return a substring of input string <i>X</i> that begins
with the <i>Y</i>-th character and which is <i>Z</i> characters long.
If <i>Z</i> is omitted then all character through the end of the string
are returned.
The left-most character of <i>X</i> is number 1.  If <i>Y</i> is negative
the the first character of the substring is found by counting from the
right rather than the left.  If <i>X</i> is string
then characters indices refer to actual UTF-8 characters.  If
<i>X</i> is a BLOB then the indices refer to bytes.</td>
</tr>

<tr>
<td valign="top" align="right">
<a name="trimFunc"></a>
trim(<i>X</i>)<br>trim(<i>X</i>,<i>Y</i>)</td>
<td valign="top">Return a string formed by removing any and all
characters that appear in <i>Y</i> from both ends of <i>X</i>.
If the <i>Y</i> argument is omitted, spaces are removed.</td>
</tr>


<tr>
<td valign="top" align="right">typeof(<i>X</i>)</td>
<td valign="top">Return the type of the expression <i>X</i>.  The only 
return values are "null", "integer", "real", "text", and "blob".
SQLite's type handling is 
explained in <a href="datatype3.html">Datatypes in SQLite Version 3</a>.</td>
</tr>

<tr>
<td valign="top" align="right">upper(<i>X</i>)</td>
<td valign="top">Return a copy of input string <i>X</i> converted to all
upper-case letters.  The implementation of this function uses the C library
routine <b>toupper()</b> which means it may not work correctly on 
UTF-8 strings.</td>
</tr>

<tr>
<td valign="top" align="right">zeroblob(<i>N</i>)</td>
<td valign="top"><a name="zeroblob"></a>
Return a BLOB consisting of N bytes of 0x00.  SQLite
manages these zeroblobs very efficiently.  Zeroblobs can be used to
reserve space for a BLOB that is later written using 
[sqlite3_blob_open() | incremental BLOB I/O].</td>
</tr>


</table>



<b>Date And Time Functions</b>


<p>Date and time functions are documented in the 
<a href="http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions">
SQLite Wiki</a>.</p>

<a name="aggregatefunctions"></a>

<b>Aggregate Functions</b>


<p>
The aggregate functions shown below are available by default.  Additional
aggregate functions written in C may be added using the 
[sqlite3_create_function()]</a>
API.</p>

<p>
In any aggregate function that takes a single argument, that argument
can be preceeded by the keyword DISTINCT.  In such cases, duplicate
elements are filtered before being passed into the aggregate function.
For example, the function "count(distinct X)" will return the number
of distinct values of column X instead of the total number of non-null
values in column X.
</p>

<table border=0 cellpadding=10 class="pdf_functions">
<tr>
<td valign="top" align="right" width=120>avg(<i>X</i>)</td>
<td valign="top">Return the average value of all non-NULL <i>X</i> within a
group.  String and BLOB values that do not look like numbers are
interpreted as 0.
The result of avg() is always a floating point value even if all
inputs are integers. </p></td>
</tr>

<tr>
<td valign="top" align="right">count(<i>X</i>)<br>count(*)</td>
<td valign="top">The first form return a count of the number of times
that <i>X</i> is not NULL in a group.  The second form (with no argument)
returns the total number of rows in the group.</td>
</tr>

<tr>
<td valign="top" align="right">group_concat(<i>X</i>)<br>
group_concat(<i>X</i>,<i>Y</i>)</td>
<td valign="top">The result is a string which is the concatenation of
all non-NULL values of <i>X</i>.  If parameter <i>Y</i> is the separator
between instances of <i>X</i>.  A comma (",") is used as the separator
if <i>Y</i> is omitted.</td>
</tr>

<tr>
<td valign="top" align="right">max(<i>X</i>)</td>

<td valign="top">Return the maximum value of all values in the group.
The usual sort order is used to determine the maximum.</td>
</tr>

<tr>
<td valign="top" align="right">min(<i>X</i>)</td>

<td valign="top">Return the minimum non-NULL value of all values in the group.
The usual sort order is used to determine the minimum.  NULL is only returned

if all values in the group are NULL.</td>
</tr>

<tr>
<td valign="top" align="right">sum(<i>X</i>)<br>total(<i>X</i>)</td>
<td valign="top">Return the numeric sum of all non-NULL values in the group.
   If there are no non-NULL input rows then sum() returns
   NULL but total() returns 0.0.
   NULL is not normally a helpful result for the sum of no rows
   but the SQL standard requires it and most other
   SQL database engines implement sum() that way so SQLite does it in the
   same way in order to be compatible.   The non-standard total() function
   is provided as a convenient way to work around this design problem
   in the SQL language.</p>

   <p>The result of total() is always a floating point value.
   The result of sum() is an integer value if all non-NULL inputs are integers.
   If any input to sum() is neither an integer or a NULL
   then sum() returns a floating point value
   which might be an approximation to the true sum.</p>

   <p>Sum() will throw an "integer overflow" exception if all inputs
   are integers or NULL
   and an integer overflow occurs at any point during the computation.
   Total() never throws an exception.</p>

</tr>
</table>

<tcl>
##############################################################################
Section INSERT insert {INSERT INSERTs}

Syntax {sql-statement} {







|







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





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

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

<
<
>
|
|
|
|
|
<
>

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

<
<
<
|
|
|
|
>
>
>

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

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


>
>
|
>





|
>
|
>
















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

|
|
|
|
|

|
|
|
|
>
|







1225
1226
1227
1228
1229
1230
1231
1232
1233
1234
1235
1236
1237
1238
1239
1240
1241
1242
1243
1244
1245
1246
1247
1248
1249
1250
1251
1252
1253
1254
1255
1256
1257
1258
1259
1260
1261
1262
1263
1264
1265
1266
1267
1268
1269
1270
1271
1272
1273
1274
1275
1276
1277

1278
1279


1280
1281
1282
1283
1284
1285
1286

1287
1288
1289
1290
1291
1292

1293
1294


1295
1296
1297

1298
1299
1300
1301
1302
1303
1304
1305

1306
1307
1308
1309
1310
1311

1312
1313


1314


1315
1316
1317
1318

1319
1320
1321
1322
1323
1324
1325

1326
1327

1328
1329
1330
1331
1332
1333
1334
1335
1336
1337
1338
1339
1340
1341
1342

1343
1344
1345
1346
1347
1348
1349

1350
1351

1352

1353
1354
1355

1356
1357


1358
1359
1360
1361
1362
1363

1364
1365
1366

1367
1368
1369
1370
1371

1372
1373
1374
1375
1376

1377
1378
1379
1380
1381
1382
1383
1384
1385


1386
1387
1388
1389
1390

1391








1392



1393
1394
1395
1396
1397
1398
1399
1400
1401



1402
1403
1404
1405
1406
1407
1408
1409
1410
1411
1412
1413
1414
1415
1416
1417
1418
1419
1420
1421
1422

1423
1424
1425
1426
1427
1428
1429
1430

1431
1432
1433
1434
1435

1436
1437


1438
1439
1440
1441
1442
1443
1444
1445
1446
1447

1448
1449

1450

1451
1452
1453

1454
1455

1456
1457
1458
1459
1460

1461
1462
1463
1464
1465
1466
1467

1468
1469

1470
1471
1472
1473
1474

1475
1476
1477
1478
1479
1480
1481
1482
1483
1484
1485
1486
1487
1488
1489
1490
1491
1492
1493
1494
1495
1496
1497
1498
1499
1500
1501
1502
1503
1504
1505
1506
1507
1508
1509
1510
1511
1512
1513
1514
1515

1516
1517
1518
1519
1520
1521

1522
1523

1524
1525
1526
1527
1528

1529
1530

1531
1532
1533

1534
1535

1536
1537
1538
1539
1540

1541
1542
1543
1544
1545
1546
1547
1548
1549
1550
1551
1552
1553
1554
1555
1556
1557
1558
1559
1560
1561
1562
1563
1564
1565
1566
1567
1568
1569
1570
1571
1572
SELECT becomes the value used in the expression.  If the SELECT yields
more than one result row, all rows after the first are ignored.  If
the SELECT yields no rows, then the value of the SELECT is NULL.</p>

<p>A CAST expression changes the datatype of the <expr> into the
type specified by &lt;type&gt;. 
&lt;type&gt; can be any non-empty type name that is valid
for the type in a column definition of a [CREATE TABLE] statement.</p>

<p>Both simple and aggregate functions are supported.  A simple
function can be used in any expression.  Simple functions return
a result immediately based on their inputs.  Aggregate functions
may only be used in a SELECT statement.  Aggregate functions compute
their result across all rows of the result set.</p>

<tcl>
##############################################################################
Section {Core Functions} corefunc {}
proc funcdef {syntax keywords desc} {
  hd_puts {<tr>}
  regsub -all {\s+} [string trim $syntax] {<br></br>} syntax
  regsub -all {\(([^*)]+)\)} $syntax {(<i>\1</i>)} syntax
  regsub -all {,} $syntax {</i>,<i>} syntax
  regsub -all {<i>\.\.\.</i>} $syntax {...} syntax
  hd_puts "<td valign=\"top\" align=\"right\" width=\"120\">$syntax</td>"
  hd_puts {<td valign="top">}
  if {[llength $keywords]==0} {
    regexp {[a-z_]+} $syntax name
    hd_fragment $name $name
  } else {
    eval hd_fragment $keywords
  }
  hd_resolve $desc
  hd_puts {</td></tr>}
}
</tcl>

<p>The core functions shown below are available by default.  Additional
functions may be written in C and added to the database engine using
the [sqlite3_create_function()] API.</p>

<table border=0 cellpadding=10>
<tcl>
funcdef {abs(X)} {} {
  Return the absolute value of the numeric
  argument <i>X</i>.  Return NULL if <i>X</i> is NULL.  Return 0.0 if
  <i>X</i> is not a numeric value.
}

funcdef {coalesce(X,Y,...)} {} {
  Return a copy of the first non-NULL argument.  If
  all arguments are NULL then NULL is returned.  There must be at least 
  2 arguments.

}



funcdef {glob(X,Y)} {} {
  This function is used to implement the
  "<b>X GLOB Y</b>" syntax of SQLite.
  The [sqlite3_create_function()]
  interface can
  be used to override this function and thereby change the operation
  of the [GLOB] operator.</td>

}

funcdef {ifnull(X,Y)} {} {
  Return a copy of the first non-NULL argument.  If
  both arguments are NULL then NULL is returned. This behaves the same as 
  [coalesce()].</td>

}



funcdef {hex(X)} {} {
  The argument is interpreted as a BLOB.  The result
  is a hexadecimal rendering of the content of that blob.

}

funcdef {last_insert_rowid()} {} {
  Return the [ROWID]
  of the last row insert from this
  connection to the database. 
  This is the same value that would be returned
  from the [sqlite3_last_insert_rowid()] API function.

}

funcdef {length(X)} {} {
  Return the string length of <i>X</i> in characters.
  If SQLite is configured to support UTF-8, then the number of UTF-8
  characters is returned, not the number of bytes.

}



funcdef {like(X,Y) like(X,Y,Z)} {} {


  This function is used to implement the "<b>X LIKE Y &#91;ESCAPE Z&#93;</b>"
  syntax of SQL. If the optional ESCAPE clause is present, then the
  user-function is invoked with three arguments. Otherwise, it is
  invoked with two arguments only. The 

  [sqlite3_create_function()] interface can be used to override this
  function and thereby change the operation of the
  [LIKE] operator. When doing this, it may be important
  to override both the two and three argument versions of the like() 
  function. Otherwise, different code may be called to implement the
  [LIKE] operator depending on whether or not an ESCAPE clause was 
  specified.

}


funcdef {load_extension(X) load_extension(X,Y)} {load_extension} {
  Load SQLite extensions out of the shared library
  file named <i>X</i> using the entry point <i>Y</i>.  The result
  is a NULL.  If <i>Y</i> is omitted then the default entry point
  of <b>sqlite3_extension_init</b> is used.  This function raises
  an exception if the extension fails to load or initialize correctly.

  <p>This function will fail if the extension attempts to modify
  or delete a SQL function or collating sequence.  The
  extension can add new functions or collating sequences, but cannot
  modify or delete existing functions or collating sequences because
  those functions and/or collating sequences might be used elsewhere
  in the currently running SQL statement.  To load an extension that
  changes or deletes functions or collating sequences, use the
  [sqlite3_load_extension()] C-language API.</p>

}

funcdef {lower(X)} {} {
  Return a copy of string <i>X</i> will all ASCII characters
  converted to lower case.  The C library <b>tolower()</b> routine is used
  for the conversion, which means that this function might not
  work correctly on non-ASCII UTF-8 characters.

}


funcdef {ltrim(X) ltrim(X,Y)} {} {

  Return a string formed by removing any and all
  characters that appear in <i>Y</i> from the left side of <i>X</i>.
  If the <i>Y</i> argument is omitted, spaces are removed.

}



funcdef {max(X,Y,...)} {maxCoreFunc max} {
  Return the argument with the maximum value.  Arguments
  may be strings in addition to numbers.  The maximum value is determined
  by the usual sort order.  Note that <b>max()</b> is a simple function when
  it has 2 or more arguments but converts to an aggregate function if given
  only a single argument.

}

funcdef {min(X,Y,...)} {minCoreFunc min} {

  Return the argument with the minimum value.  Arguments
  may be strings in addition to numbers.  The minimum value is determined
  by the usual sort order.  Note that <b>min()</b> is a simple function when
  it has 2 or more arguments but converts to an aggregate function if given
  only a single argument.

}

funcdef {nullif(X,Y)} {} {
  Return the first argument if the arguments are different, 
  otherwise return NULL.

}

funcdef {quote(X)} {} {
  This routine returns a string which is the value of
  its argument suitable for inclusion into another SQL statement.
  Strings are surrounded by single-quotes with escapes on interior quotes
  as needed.  BLOBs are encoded as hexadecimal literals.
  The implementation of [VACUUM] uses this function. The function
  is also useful when writing triggers to implement undo/redo functionality.


}

funcdef {random()} {} {
  Return a pseudo-random integer
  between -9223372036854775808 and +9223372036854775807.

}












funcdef {randomblob(N)} {} {
  Return an <i>N</i>-byte blob containing pseudo-random bytes.
  <i>N</i> should be a postive integer.

  <p>Hint:  applications can generate globally unique identifiers
  using this function together with [hex()] and/or
  [lower()] like this:</p>

  <blockquote>



  hex(randomblob(16))<br></br>
  lower(hex(randomblob(16)))
  </blockquote>
}

funcdef {replace(X,Y,Z)} {} {
  Return a string formed by substituting string <i>Z</i> for
  every occurrance of string <i>Y</i> in string <i>X</i>.  The [BINARY]
  collating sequence is used for comparisons.
}

funcdef {round(X) round(X,Y)} {} {
  Round off the number <i>X</i> to <i>Y</i> digits to the
  right of the decimal point.  If the <i>Y</i> argument is omitted, 0 is 
  assumed.
}

funcdef {rtrim(X) rtrim(X,Y)} {} {
  Return a string formed by removing any and all
  characters that appear in <i>Y</i> from the right side of <i>X</i>.
  If the <i>Y</i> argument is omitted, spaces are removed.

}

funcdef {soundex(X)} {} {
  Compute the soundex encoding of the string <i>X</i>.
  The string "?000" is returned if the argument is NULL.
  This function is omitted from SQLite by default.
  It is only available the -DSQLITE_SOUNDEX=1 compiler option
  is used when SQLite is built.

}

funcdef {sqlite_version(X)} {sqlite_version} {
  Return the version string for the SQLite library
  that is running.  Example:  "3.5.9"

}



funcdef {substr(X,Y,Z) substr(X,Y)} {} {
  Return a substring of input string <i>X</i> that begins
  with the <i>Y</i>-th character and which is <i>Z</i> characters long.
  If <i>Z</i> is omitted then all character through the end of the string
  are returned.
  The left-most character of <i>X</i> is number 1.  If <i>Y</i> is negative
  the the first character of the substring is found by counting from the
  right rather than the left.  If <i>X</i> is string
  then characters indices refer to actual UTF-8 characters.  If
  <i>X</i> is a BLOB then the indices refer to bytes.

}


funcdef {trim(X) trim(X,Y)} {} {

  Return a string formed by removing any and all
  characters that appear in <i>Y</i> from both ends of <i>X</i>.
  If the <i>Y</i> argument is omitted, spaces are removed.

}


funcdef {typeof(X)} {} {
  Return the type of the expression <i>X</i>.  The only 
  return values are "null", "integer", "real", "text", and "blob".
  SQLite's type handling is 
  explained in <a href="datatype3.html">Datatypes in SQLite Version 3</a>.

}

funcdef {upper(X)} {} {
  Return a copy of input string <i>X</i> converted to all
  upper-case letters.  The implementation of this function uses the C library
  routine <b>toupper()</b> which means it may not work correctly on 
  non-ASCII UTF-8 strings.

}


funcdef {zeroblob(N)} {} {
  Return a BLOB consisting of N bytes of 0x00.  SQLite
  manages these zeroblobs very efficiently.  Zeroblobs can be used to
  reserve space for a BLOB that is later written using 
  [sqlite3_blob_open() | incremental BLOB I/O].

}
</tcl>
</table>

<tcl>
##############################################################################
Section {Date And Time Functions} datefunc {}
</tcl>

<p>Date and time functions are documented in the 
<a href="http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions">
SQLite Wiki</a>.</p>

<tcl>
##############################################################################
Section {Aggregate Functions} aggfunc {}
</tcl>

<p>
The aggregate functions shown below are available by default.  Additional
aggregate functions written in C may be added using the 
[sqlite3_create_function()]</a>
API.</p>

<p>
In any aggregate function that takes a single argument, that argument
can be preceeded by the keyword DISTINCT.  In such cases, duplicate
elements are filtered before being passed into the aggregate function.
For example, the function "count(distinct X)" will return the number
of distinct values of column X instead of the total number of non-null
values in column X.
</p>

<table border=0 cellpadding=10>
<tcl>
funcdef {avg(X)} {} {
  Return the average value of all non-NULL <i>X</i> within a
  group.  String and BLOB values that do not look like numbers are
  interpreted as 0.
  The result of avg() is always a floating point value even if all
  inputs are integers.

}

funcdef {count(X) count(*)} {} {
  The first form return a count of the number of times
  that <i>X</i> is not NULL in a group.  The second form (with no argument)
  returns the total number of rows in the group.

}


funcdef {group_concat(X) group_concat(X,Y)} {} {
  The result is a string which is the concatenation of
  all non-NULL values of <i>X</i>.  If parameter <i>Y</i> is the separator
  between instances of <i>X</i>.  A comma (",") is used as the separator
  if <i>Y</i> is omitted.

}


funcdef {max(X)} {maxAggFunc agg_max} {
  Return the maximum value of all values in the group.
  The usual sort order is used to determine the maximum.

}


funcdef {min(X)} {minAggFunc agg_min} {
  Return the minimum non-NULL value of all values in the group.
  The usual sort order is used to determine the minimum.  
  NULL is only returned
  if all values in the group are NULL.

}

funcdef {sum(X) total(X)} {sumFunc sum total} {
  Return the numeric sum of all non-NULL values in the group.
  If there are no non-NULL input rows then sum() returns
  NULL but total() returns 0.0.
  NULL is not normally a helpful result for the sum of no rows
  but the SQL standard requires it and most other
  SQL database engines implement sum() that way so SQLite does it in the
  same way in order to be compatible.   The non-standard total() function
  is provided as a convenient way to work around this design problem
  in the SQL language.</p>

  <p>The result of total() is always a floating point value.
  The result of sum() is an integer value if all non-NULL inputs are integers.
  If any input to sum() is neither an integer or a NULL
  then sum() returns a floating point value
  which might be an approximation to the true sum.</p>

  <p>Sum() will throw an "integer overflow" exception if all inputs
  are integers or NULL
  and an integer overflow occurs at any point during the computation.
  Total() never throws an exception.
}
</tcl>
</table>

<tcl>
##############################################################################
Section INSERT insert {INSERT INSERTs}

Syntax {sql-statement} {
1581
1582
1583
1584
1585
1586
1587
1588
1589
1590
1591
1592
1593
1594
1595
1596
1597
1598
1599
1600
1601
1602
1603
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.</p>

<p>The optional conflict-clause allows the specification of an alternative
constraint conflict resolution algorithm to use during this one command.
See the section titled
<a href="lang_conflict.html">ON CONFLICT</a> for additional information.
For compatibility with MySQL, the parser allows the use of the
single keyword <a href="lang_replace.html">REPLACE</a> as an 
alias for "INSERT OR REPLACE".
</p>

<tcl>
##############################################################################
Section {ON CONFLICT clause} conflict {{conflict clause}}

Syntax {conflict-clause} {
ON CONFLICT <conflict-algorithm>
} {conflict-algorithm} {
ROLLBACK | ABORT | FAIL | IGNORE | REPLACE
}
</tcl>







|







|







1592
1593
1594
1595
1596
1597
1598
1599
1600
1601
1602
1603
1604
1605
1606
1607
1608
1609
1610
1611
1612
1613
1614
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.</p>

<p>The optional conflict-clause allows the specification of an alternative
constraint conflict resolution algorithm to use during this one command.
See the section titled
[ON CONFLICT] for additional information.
For compatibility with MySQL, the parser allows the use of the
single keyword <a href="lang_replace.html">REPLACE</a> as an 
alias for "INSERT OR REPLACE".
</p>

<tcl>
##############################################################################
Section {ON CONFLICT clause} conflict {{conflict clause} {ON CONFLICT}}

Syntax {conflict-clause} {
ON CONFLICT <conflict-algorithm>
} {conflict-algorithm} {
ROLLBACK | ABORT | FAIL | IGNORE | REPLACE
}
</tcl>
1866
1867
1868
1869
1870
1871
1872
1873
1874
1875
1876
1877
1878
1879
1880
1881
1882
1883
1884
1885
1886
1887
1888
1889
1890
1891
1892
1893
1894
1895
1896
1897
1898
1899
1900
1901
1902
1903
1904
1905
1906
1907
1908
1909
1910
1911
1912
1913
1914
1915
1916
1917
1918
1919
1920
1921
1922
1923
1924
1925
1926
1927
1928
1929
1930
1931
to the right.  The expressions may use the values of other columns.
All expressions are evaluated before any assignments are made.
A WHERE clause can be used to restrict which rows are updated.</p>

<p>The optional conflict-clause allows the specification of an alternative
constraint conflict resolution algorithm to use during this one command.
See the section titled
<a href="lang_conflict.html">ON CONFLICT</a> for additional information.</p>


<tcl>
##############################################################################
Section VACUUM vacuum VACUUM

Syntax {sql-statement} {
VACUUM
}
</tcl>

<p>The VACUUM command is an SQLite extension modeled after a similar
command found in PostgreSQL.  If VACUUM is invoked with the name of a
table or index then it is suppose to clean up the named table or index.
In version 1.0 of SQLite, the VACUUM command would invoke 
<b>gdbm_reorganize()</b> to clean up the backend database file.</p>

<p>
VACUUM became a no-op when the GDBM backend was removed from
SQLITE in version 2.0.0.
VACUUM was reimplemented in version 2.8.1.
</p>

<p>When an object (table, index, or trigger) is dropped from the 
database, it leaves behind empty space.  This makes the database 
file larger than it needs to be, but can speed up inserts.  In time 
inserts and deletes can leave the database file structure fragmented, 
which slows down disk access to the database contents.

The VACUUM command cleans
the main database by copying its contents to a temporary database file and 
reloading the original database file from the copy.  This eliminates 
free pages,  aligns table data to be contiguous, and otherwise cleans 
up the database file structure.</p>

<p>The VACUUM command may change the 
<a href="lang_createtable.html#rowid">ROWID</a> of entires in tables that do
not have an explicit INTEGER PRIMARY KEY.</p>

<p>VACUUM only works on the main database.
It is not possible to VACUUM an attached database file.</p>

<p>The VACUUM command will fail if there is an active transaction.
The VACUUM command is a no-op for in-memory databases.</p>

<p>As of SQLite version 3.1, an alternative to using the VACUUM command
is auto-vacuum mode, enabled using the 
<a href="pragma.html#pragma_auto_vacuum">auto_vacuum pragma</a>.
When auto-vacuum is enabled for a database, large deletes cause
the size of the database file to shrink.  However, auto-vacuum
also causes excess fragmentation of the database file.  And auto-vacuum
does not compact partially filled pages of the database as VACUUM
does.
</p>

<tcl>
#############################################################################
# A list of keywords.  A asterisk occurs after the keyword if it is on







|











<
<
<
<
<
<
<
<
<
<
<
<




|

|

















|
|
|
|







1877
1878
1879
1880
1881
1882
1883
1884
1885
1886
1887
1888
1889
1890
1891
1892
1893
1894
1895












1896
1897
1898
1899
1900
1901
1902
1903
1904
1905
1906
1907
1908
1909
1910
1911
1912
1913
1914
1915
1916
1917
1918
1919
1920
1921
1922
1923
1924
1925
1926
1927
1928
1929
1930
to the right.  The expressions may use the values of other columns.
All expressions are evaluated before any assignments are made.
A WHERE clause can be used to restrict which rows are updated.</p>

<p>The optional conflict-clause allows the specification of an alternative
constraint conflict resolution algorithm to use during this one command.
See the section titled
[ON CONFLICT] for additional information.</p>


<tcl>
##############################################################################
Section VACUUM vacuum VACUUM

Syntax {sql-statement} {
VACUUM
}
</tcl>













<p>When an object (table, index, or trigger) is dropped from the 
database, it leaves behind empty space.  This makes the database 
file larger than it needs to be, but can speed up inserts.  In time 
inserts and deletes can leave the database file structure fragmented, 
which slows down disk access to the database contents.</p>

<p>The VACUUM command cleans
the main database by copying its contents to a temporary database file and 
reloading the original database file from the copy.  This eliminates 
free pages,  aligns table data to be contiguous, and otherwise cleans 
up the database file structure.</p>

<p>The VACUUM command may change the 
<a href="lang_createtable.html#rowid">ROWID</a> of entires in tables that do
not have an explicit INTEGER PRIMARY KEY.</p>

<p>VACUUM only works on the main database.
It is not possible to VACUUM an attached database file.</p>

<p>The VACUUM command will fail if there is an active transaction.
The VACUUM command is a no-op for in-memory databases.</p>

<p>As of SQLite version 3.1, an alternative to using the VACUUM command
is auto-vacuum mode, enabled using the 
[auto_vacuum] pragma.  When [auto_vacuum] is enabled for a database, 
large deletes cause
the size of the database file to shrink.  However, [auto_vacuum]
also causes excess fragmentation of the database file.  And [auto_vacuum]
does not compact partially filled pages of the database as VACUUM
does.
</p>

<tcl>
#############################################################################
# A list of keywords.  A asterisk occurs after the keyword if it is on
2063
2064
2065
2066
2067
2068
2069
2070
2071
2072
2073
2074
2075
2076
2077
word as the name of a user-defined object.</p>

<p>If you want to use a keyword as a name, you need to quote it.  There
are three ways of quoting keywords in SQLite:</p>

<p>
<blockquote>
<table class="pdf_functions">
<tr>	<td valign="top"><b>'keyword'</b></td><td width="20"></td>
	<td>A keyword in single quotes is interpreted as a literal string
        if it occurs in a context where a string literal is allowed, otherwise
	it is understood as an identifier.</td></tr>
<tr>	<td valign="top"><b>"keyword"</b></td><td></td>
	<td>A keyword in double-quotes is interpreted as an identifier if
        it matches a known identifier.  Otherwise it is interpreted as a







|







2062
2063
2064
2065
2066
2067
2068
2069
2070
2071
2072
2073
2074
2075
2076
word as the name of a user-defined object.</p>

<p>If you want to use a keyword as a name, you need to quote it.  There
are three ways of quoting keywords in SQLite:</p>

<p>
<blockquote>
<table>
<tr>	<td valign="top"><b>'keyword'</b></td><td width="20"></td>
	<td>A keyword in single quotes is interpreted as a literal string
        if it occurs in a context where a string literal is allowed, otherwise
	it is understood as an identifier.</td></tr>
<tr>	<td valign="top"><b>"keyword"</b></td><td></td>
	<td>A keyword in double-quotes is interpreted as an identifier if
        it matches a known identifier.  Otherwise it is interpreted as a
Changes to pages/lockingv3.in.
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
133
134
135
136
137
138
verification against the database file before being used.  Other 
processes can read or write the database as their own locking states
permit.  This is the default state.
</td></tr>

<tr><td valign="top">SHARED</td>
<td valign="top">

The database may be read but not written.  Any number of 
processes can hold SHARED locks at the same time, hence there can be
many simultaneous readers.  But no other thread or process is allowed
to write to the database file while one or more SHARED locks are active.
</td></tr>

<tr><td valign="top">RESERVED</td>
<td valign="top">

A RESERVED lock means that the process is planning on writing to the
database file at some point in the future but that it is currently just
reading from the file.  Only a single RESERVED lock may be active at one
time, though multiple SHARED locks can coexist with a single RESERVED lock.
RESERVED differs from PENDING in that new SHARED locks can be acquired
while there is a RESERVED lock.
</td></tr>

<tr><td valign="top">PENDING</td>
<td valign="top">

A PENDING lock means that the process holding the lock wants to write
to the database as soon as possible and is just waiting on all current
SHARED locks to clear so that it can get an EXCLUSIVE lock.  No new 
SHARED locks are permitted against the database if
a PENDING lock is active, though existing SHARED locks are allowed to
continue.
</td></tr>

<tr><td valign="top">EXCLUSIVE</td>
<td valign="top">

An EXCLUSIVE lock is needed in order to write to the database file.
Only one EXCLUSIVE lock is allowed on the file and no other locks of
any kind are allowed to coexist with an EXCLUSIVE lock.  In order to
maximize concurrency, SQLite works to minimize the amount of time that
EXCLUSIVE locks are held.
</td></tr>
</table>







>








>










>










>







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
133
134
135
136
137
138
139
140
141
142
verification against the database file before being used.  Other 
processes can read or write the database as their own locking states
permit.  This is the default state.
</td></tr>

<tr><td valign="top">SHARED</td>
<td valign="top">
<tcl>hd_fragment shared_lock {shared lock} SHARED</tcl>
The database may be read but not written.  Any number of 
processes can hold SHARED locks at the same time, hence there can be
many simultaneous readers.  But no other thread or process is allowed
to write to the database file while one or more SHARED locks are active.
</td></tr>

<tr><td valign="top">RESERVED</td>
<td valign="top">
<tcl>hd_fragment reserved_lock {reserved lock} RESERVED</tcl>
A RESERVED lock means that the process is planning on writing to the
database file at some point in the future but that it is currently just
reading from the file.  Only a single RESERVED lock may be active at one
time, though multiple SHARED locks can coexist with a single RESERVED lock.
RESERVED differs from PENDING in that new SHARED locks can be acquired
while there is a RESERVED lock.
</td></tr>

<tr><td valign="top">PENDING</td>
<td valign="top">
<tcl>hd_fragment pending_lock {pending lock} PENDING</tcl>
A PENDING lock means that the process holding the lock wants to write
to the database as soon as possible and is just waiting on all current
SHARED locks to clear so that it can get an EXCLUSIVE lock.  No new 
SHARED locks are permitted against the database if
a PENDING lock is active, though existing SHARED locks are allowed to
continue.
</td></tr>

<tr><td valign="top">EXCLUSIVE</td>
<td valign="top">
<tcl>hd_fragment exclusive_lock {exclusive lock} EXCLUSIVE</tcl>
An EXCLUSIVE lock is needed in order to write to the database file.
Only one EXCLUSIVE lock is allowed on the file and no other locks of
any kind are allowed to coexist with an EXCLUSIVE lock.  In order to
maximize concurrency, SQLite works to minimize the amount of time that
EXCLUSIVE locks are held.
</td></tr>
</table>
Changes to wrap.tcl.
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466

# Generate a document showing the hyperlink keywords and their
# targets.
#
hd_open_main doc_keyword_crossref.html
hd_header {Hyperlink Crossreference} $DOC/wrap.tcl
hd_puts "<ul>"
foreach x [lsort [array names glink]] {
  set y $glink($x)
  hd_puts "<li>$x - <a href=\"$y\">$y</a></li>"
  lappend revglink($y) $x
}
hd_puts "</ul><hr><ul>"
foreach y [lsort [array names revglink]] {
  hd_puts "<li><a href=\"$y\">$y</a> - [lsort $revglink($y)]</li>"







|







452
453
454
455
456
457
458
459
460
461
462
463
464
465
466

# Generate a document showing the hyperlink keywords and their
# targets.
#
hd_open_main doc_keyword_crossref.html
hd_header {Hyperlink Crossreference} $DOC/wrap.tcl
hd_puts "<ul>"
foreach x [lsort -dict [array names glink]] {
  set y $glink($x)
  hd_puts "<li>$x - <a href=\"$y\">$y</a></li>"
  lappend revglink($y) $x
}
hd_puts "</ul><hr><ul>"
foreach y [lsort [array names revglink]] {
  hd_puts "<li><a href=\"$y\">$y</a> - [lsort $revglink($y)]</li>"