SQLite

Check-in [06bd507096]
Login

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

Overview
Comment:documented pragma foreign_key_list(). updated docs for removed behavior: Oracle join syntax and temp indexes. (CVS 1193)
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 06bd5070966b59730a91bc4d384df4f8abaadd37
User & Date: jplyon 2004-01-19 05:09:24.000
Context
2004-01-20
11:54
Make ATTACH and DETACH work with the non-callback API. Ticket #574. (CVS 1194) (check-in: c32a80fe3c user: drh tags: trunk)
2004-01-19
05:09
documented pragma foreign_key_list(). updated docs for removed behavior: Oracle join syntax and temp indexes. (CVS 1193) (check-in: 06bd507096 user: jplyon tags: trunk)
04:57
comment typos (CVS 1192) (check-in: 7972ee3219 user: jplyon tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to www/lang.tcl. 
1
2
3
4
5
6
7
8
9
10
11

#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: lang.tcl,v 1.65 2003/07/20 01:16:48 drh Exp $}

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




|








1
2
3
4
5
6
7
8
9
10
11

#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: lang.tcl,v 1.66 2004/01/19 05:09:24 jplyon Exp $}

puts {<html>
<head>
  <title>Query Language Understood By SQLite</title>
</head>
<body bgcolor=white>
<h1 align=center>
︙︙
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216

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 documention on the <a href="#conflict">ON CONFLICT</a>
clause for additional information about the ROLLBACK
conflict resolution algorithm.
</p>









|
|








201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216

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 documention on the <a href="#conflict">ON CONFLICT</a>
clause for additional information about the ROLLBACK
conflict resolution algorithm.
</p>
︙︙
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257

Syntax {comment} {<SQL-comment> | <C-comment>
} {SQL-comment} {-- <single-line>
} {C-comment} {/STAR <multiple-lines> [STAR/]
}

puts {
<p> Comments aren't SQL commands, but can occur in SQL queries. They are 
treated as whitespace by the parser. They can begin anywhere whitespace 
can be found, including inside expressions that span multiple lines.
</p>

<p> SQL comments only extend to the end of the current line.</p>

<p> C comments can span any number of lines. If there is no terminating
delimiter, they extend to the end of the input. This is not treated as 
an error. A new SQL statement can begin on a line after a multiline 
comment ends. C comments can be embedded anywhere whitespace can occur, 
including inside expressions, and in the middle of other SQL statements.
C comments do not nest. SQL comments inside a C comment will be ignored.
</p>
}


Section COPY copy

Syntax {sql-statement} {








|





|
|
|
|

|








232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257

Syntax {comment} {<SQL-comment> | <C-comment>
} {SQL-comment} {-- <single-line>
} {C-comment} {/STAR <multiple-lines> [STAR/]
}

puts {
<p> Comments aren't SQL commands, but can occur in SQL queries. They are 
treated as whitespace by the parser.  They can begin anywhere whitespace 
can be found, including inside expressions that span multiple lines.
</p>

<p> SQL comments only extend to the end of the current line.</p>

<p> C comments can span any number of lines.  If there is no terminating
delimiter, they extend to the end of the input.  This is not treated as
an error.  A new SQL statement can begin on a line after a multiline
comment ends.  C comments can be embedded anywhere whitespace can occur,
including inside expressions, and in the middle of other SQL statements.
C comments do not nest.  SQL comments inside a C comment will be ignored.
</p>
}


Section COPY copy

Syntax {sql-statement} {
︙︙
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305

by a line that contains only a baskslash and a dot:}
puts "\"[Operator \\.]\".</p>"


Section {CREATE INDEX} createindex

Syntax {sql-statement} {
CREATE [TEMP | TEMPORARY] [UNIQUE] INDEX <index-name> 
ON [<database-name> .] <table-name> ( <column-name> [, <column-name>]* )
[ ON CONFLICT <conflict-algorithm> ]
} {column-name} {
<name> [ ASC | DESC ]
}

puts {








|








291
292
293
294
295
296
297
298
299
300
301
302
303
304
305

by a line that contains only a baskslash and a dot:}
puts "\"[Operator \\.]\".</p>"


Section {CREATE INDEX} createindex

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

puts {
︙︙
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347

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.  Everytime the database is opened,
all CREATE INDEX statements
are read from the <b>sqlite_master</b> table and used to regenerate
SQLite's internal representation of the index layout.</p>

<p>Non-temporary indexes cannot be added on tables in attached 
databases.  They are removed with the <a href="#dropindex">DROP INDEX</a> 
command.</p>
}


Section {CREATE TABLE} {createtable}

Syntax {sql-command} {








|
|








332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347

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.  Everytime the database is opened,
all CREATE INDEX statements
are read from the <b>sqlite_master</b> table and used to regenerate
SQLite's internal representation of the index layout.</p>

<p>Indexes cannot be added on tables in attached databases.
Indexes are removed with the <a href="#dropindex">DROP INDEX</a> 
command.</p>
}


Section {CREATE TABLE} {createtable}

Syntax {sql-command} {
︙︙
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375

} {column-constraint} {
NOT NULL [ <conflict-clause> ] |
PRIMARY KEY [<sort-order>] [ <conflict-clause> ] |
UNIQUE [ <conflict-clause> ] |
CHECK ( <expr> ) [ <conflict-clause> ] |
DEFAULT <value>
} {constraint} {
PRIMARY KEY ( <name> [, <name>]* ) [ <conflict-clause> ]|
UNIQUE ( <name> [, <name>]* ) [ <conflict-clause> ] |
CHECK ( <expr> ) [ <conflict-clause> ]
} {conflict-clause} {
ON CONFLICT <conflict-algorithm>
}

puts {
<p>A CREATE TABLE statement is basically the keywords "CREATE TABLE"








|
|








360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375

} {column-constraint} {
NOT NULL [ <conflict-clause> ] |
PRIMARY KEY [<sort-order>] [ <conflict-clause> ] |
UNIQUE [ <conflict-clause> ] |
CHECK ( <expr> ) [ <conflict-clause> ] |
DEFAULT <value>
} {constraint} {
PRIMARY KEY ( <column-list> ) [ <conflict-clause> ] |
UNIQUE ( <column-list> ) [ <conflict-clause> ] |
CHECK ( <expr> ) [ <conflict-clause> ]
} {conflict-clause} {
ON CONFLICT <conflict-algorithm>
}

puts {
<p>A CREATE TABLE statement is basically the keywords "CREATE TABLE"
︙︙
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
705
706


puts {
<p>The DROP INDEX statement removes an index added with the <a href="#createindex">
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.  Non-temporary indexes on tables in 
an attached database cannot be dropped.</p>





}


Section {DROP TABLE} droptable

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

puts {
<p>The DROP TABLE statement removes a table added with the <a href=
"#createtable">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.  Non-temporary tables in an attached 
database cannot be dropped.</p>





}


Section {DROP TRIGGER} droptrigger
Syntax {sql-statement} {
DROP TRIGGER [<database-name> .] <trigger-name>
}








>
>
>
>
>
















>
>
>
>
>








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
705
706
707
708
709
710
711
712
713
714
715
716


puts {
<p>The DROP INDEX statement removes an index added with the <a href="#createindex">
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.  Non-temporary indexes on tables in 
an attached database cannot be dropped.</p>

<p>The DROP INDEX statement does not reduce the size of the database 
file.  Empty space in the database is retained for later INSERTs.  To 
remove free space in the database, use the <a href="#vacuum">VACUUM</a> 
command.</p>
}


Section {DROP TABLE} droptable

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

puts {
<p>The DROP TABLE statement removes a table added with the <a href=
"#createtable">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.  Non-temporary tables in an attached 
database cannot be dropped.</p>

<p>The DROP TABLE statement does not reduce the size of the database 
file.  Empty space in the database is retained for later INSERTs.  To 
remove free space in the database, use the <a href="#vacuum">VACUUM</a> 
command.</p>
}


Section {DROP TRIGGER} droptrigger
Syntax {sql-statement} {
DROP TRIGGER [<database-name> .] <trigger-name>
}
︙︙
757
758
759
760
761
762
763
764
765
766
767
768
769

770
771
772
773
774
775
776

<unary-op> <expr> |
( <expr> ) |
<column-name> |
<table-name> . <column-name> |
<database-name> . <table-name> . <column-name> |
<literal-value> |
<function-name> ( <expr-list> | STAR ) |
<expr> (+) |
<expr> ISNULL |
<expr> NOTNULL |
<expr> [NOT] BETWEEN <expr> AND <expr> |
<expr> [NOT] IN ( <value-list> ) |
<expr> [NOT] IN ( <select-statement> ) |

( <select-statement> ) |
CASE [<expr>] LP WHEN <expr> THEN <expr> RPPLUS [ELSE <expr>] END
} {like-op} {
LIKE | GLOB | NOT LIKE | NOT GLOB
}

puts {








<





>








767
768
769
770
771
772
773

774
775
776
777
778
779
780
781
782
783
784
785
786

<unary-op> <expr> |
( <expr> ) |
<column-name> |
<table-name> . <column-name> |
<database-name> . <table-name> . <column-name> |
<literal-value> |
<function-name> ( <expr-list> | STAR ) |

<expr> ISNULL |
<expr> NOTNULL |
<expr> [NOT] BETWEEN <expr> AND <expr> |
<expr> [NOT] IN ( <value-list> ) |
<expr> [NOT] IN ( <select-statement> ) |
<expr> [NOT] IN [<database-name> .] <table-name> |
( <select-statement> ) |
CASE [<expr>] LP WHEN <expr> THEN <expr> RPPLUS [ELSE <expr>] END
} {like-op} {
LIKE | GLOB | NOT LIKE | NOT GLOB
}

puts {
︙︙
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873

The special identifiers only refer to the row key if the CREATE TABLE
statement does not define a real column with the same name.  Row keys
act like read-only columns.  A row key can be used anywhere a regular
column can be used, except that you cannot change the value
of a row key in an UPDATE or INSERT statement.
"SELECT * ..." does not return the row key.</p>

<p>SQLite supports a minimal Oracle8 outer join behavior. A column 
expression of the form "column" or "table.column" can be followed by 
the special "<b>(+)</b>" operator.  If the table of the column expression 
is the second or subsequent table in a join, then that table becomes 
the left table in a LEFT OUTER JOIN.  The expression that uses that 
table becomes part of the ON clause for the join.
The exact Oracle8 behavior is not implemented, but it is possible to 
construct queries that will work correctly for both SQLite and Oracle8.</p>

<p>SELECT statements can appear in expressions as either the
right-hand operand of the IN operator or as a scalar quantity.
In both cases, the SELECT should have only a single column in its
result.  Compound SELECTs (connected with keywords like UNION or
EXCEPT) are allowed.
A SELECT in an expression is evaluated once before any other processing
is performed, so none of the expressions within the select itself can








<
<
<
<
<
<
<
<
<








861
862
863
864
865
866
867









868
869
870
871
872
873
874

The special identifiers only refer to the row key if the CREATE TABLE
statement does not define a real column with the same name.  Row keys
act like read-only columns.  A row key can be used anywhere a regular
column can be used, except that you cannot change the value
of a row key in an UPDATE or INSERT statement.
"SELECT * ..." does not return the row key.</p>










<p>SELECT statements can appear in expressions as either the
right-hand operand of the IN operator or as a scalar quantity.
In both cases, the SELECT should have only a single column in its
result.  Compound SELECTs (connected with keywords like UNION or
EXCEPT) are allowed.
A SELECT in an expression is evaluated once before any other processing
is performed, so none of the expressions within the select itself can
︙︙
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

    thing but only applies the setting to the current session.</p></li>

<a name="pragma_default_temp_store"></a>
<li><p><b>PRAGMA default_temp_store;
       <br>PRAGMA default_temp_store = DEFAULT; </b>(0)<b>
       <br>PRAGMA default_temp_store = MEMORY; </b>(2)<b>
       <br>PRAGMA default_temp_store = FILE;</b> (1)</p>
    <p>Query or change the setting of the "temp_store" flag stored in
    the database.  When temp_store is DEFAULT (0), the compile-time default 
    is used for the temporary database.  When temp_store is MEMORY (2), an 
    in-memory database is used.  When temp_store is FILE (1), a temporary 
    database file on disk will be used.  Note that it is possible for 
    the library compile-time options to override this setting.  Once 
    the temporary database is in use, its location cannot be changed.</p>















    <p>This pragma changes the temp_store mode persistently.  Once changed,
    the mode stays as set even if the database is closed and reopened.  The
    <a href="#pragma_temp_store"><b>temp_store</b></a> pragma does the same 
    thing but only applies the setting to the current session.</p></li>

<a name="pragma_empty_result_callbacks"></a>
<li><p><b>PRAGMA empty_result_callbacks = ON; </b>(1)<b>
       <br>PRAGMA empty_result_callbacks = OFF;</b> (0)</p>
    <p>When on, the EMPTY_RESULT_CALLBACKS pragma causes the callback
    function to be invoked once for each query that has an empty result
    set.  The third "<b>argv</b>" parameter to the callback is set to NULL
    because there is no data to report.  But the second "<b>argc</b>" and
    fourth "<b>columnNames</b>" parameters are valid and can be used to
    determine the number and names of the columns that would have been in
    the result set had the set not been empty.</p></li>







<li><p><b>PRAGMA full_column_names = ON; </b>(1)<b>
       <br>PRAGMA full_column_names = OFF;</b> (0)</p>
    <p>The column names reported in an SQLite callback are normally just
    the name of the column itself, except for joins when "TABLE.COLUMN"
    is used.  But when full_column_names is turned on, column names are
    always reported as "TABLE.COLUMN" even for simple queries.</p></li>








|
|
|
|
|
<
|
>
>

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

|













>
>
>
>
>
>








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

    thing but only applies the setting to the current session.</p></li>

<a name="pragma_default_temp_store"></a>
<li><p><b>PRAGMA default_temp_store;
       <br>PRAGMA default_temp_store = DEFAULT; </b>(0)<b>
       <br>PRAGMA default_temp_store = MEMORY; </b>(2)<b>
       <br>PRAGMA default_temp_store = FILE;</b> (1)</p>
    <p>Query or change the setting of the "<b>temp_store</b>" flag stored in
    the database.  When temp_store is DEFAULT (0), the compile-time value
    of the symbol TEMP_STORE is used for the temporary database.  
    When temp_store is MEMORY (2), an in-memory database is used.  
    When temp_store is FILE (1), a temporary database file on disk will be used.  

    Once the temporary database is in use, its location cannot be changed.  
    It is possible for the library compile-time symbol TEMP_STORE to override 
    this setting.  The following table summarizes this:</p>

<table cellpadding="2">
<tr><th>TEMP_STORE</th><th>temp_store</th><th>temp database location</th></tr>
<tr><td align="center">0</td><td align="center"><em>any</em></td><td align="center">file</td></tr>
<tr><td align="center">1</td><td align="center">0</td><td align="center">file</td></tr>
<tr><td align="center">1</td><td align="center">1</td><td align="center">file</td></tr>
<tr><td align="center">1</td><td align="center">2</td><td align="center">memory</td></tr>
<tr><td align="center">2</td><td align="center">0</td><td align="center">memory</td></tr>
<tr><td align="center">2</td><td align="center">1</td><td align="center">file</td></tr>
<tr><td align="center">2</td><td align="center">2</td><td align="center">memory</td></tr>
<tr><td align="center">3</td><td align="center"><em>any</em></td><td align="center">memory</td></tr>
</table>

    <p>This pragma changes the temp_store mode persistently.  Once changed,
    the mode stays set even if the database is closed and reopened.  The
    <a href="#pragma_temp_store"><b>temp_store</b></a> pragma does the same 
    thing but only applies the setting to the current session.</p></li>

<a name="pragma_empty_result_callbacks"></a>
<li><p><b>PRAGMA empty_result_callbacks = ON; </b>(1)<b>
       <br>PRAGMA empty_result_callbacks = OFF;</b> (0)</p>
    <p>When on, the EMPTY_RESULT_CALLBACKS pragma causes the callback
    function to be invoked once for each query that has an empty result
    set.  The third "<b>argv</b>" parameter to the callback is set to NULL
    because there is no data to report.  But the second "<b>argc</b>" and
    fourth "<b>columnNames</b>" parameters are valid and can be used to
    determine the number and names of the columns that would have been in
    the result set had the set not been empty.</p></li>

<li><p><b>PRAGMA foreign_key_list(</b><i>table-name</i><b>);</b></p>
    <p>For each foreign key that references a column in the argument
    table, invoke the callback function with information about that
    foreign key. The callback function will be invoked once for each
    column in each foreign key.</p></li>

<li><p><b>PRAGMA full_column_names = ON; </b>(1)<b>
       <br>PRAGMA full_column_names = OFF;</b> (0)</p>
    <p>The column names reported in an SQLite callback are normally just
    the name of the column itself, except for joins when "TABLE.COLUMN"
    is used.  But when full_column_names is turned on, column names are
    always reported as "TABLE.COLUMN" even for simple queries.</p></li>
︙︙
1611
1612
1613
1614
1615
1616
1617
1618










1619
1620
1621
1622
1623
1624
1625
1626
1627
1628
1629
1630
1631
1632
1633
1634
1635
1636
1637
1638
1639
1640

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 reimplimented in version 2.8.1.  It now cleans










the database by copying its contents to a temporary database file and 
reloading the original database file from the copy.  This will eliminate 
free pages,  align table data to be contiguous, and otherwise clean up 
the database file structure.  The index or table name argument is now
ignored.</p>

<p>This command will fail if there is an active transaction.  This 
command has no effect on an in-memory database.</p>
}


Section {SQLite keywords} keywords

puts {
<p>The following keywords are used by SQLite. Most are either reserved 
words in SQL-92 or were listed as potential reserved words.  Those which 
aren't are shown in italics.  Not all of these words are actually used
by SQLite.  Keywords are not reserved in SQLite.  Any keyword can be used 
as an identifier for SQLite objects (columns, databases, indexes, tables, 
triggers, views, ...) but must generally be enclosed by brackets or 
quotes to avoid confusing the parser.  Keyword matching in SQLite is 
case-insensitive.</p>








|
>
>
>
>
>
>
>
>
>
>

|
|
|
<









|








1631
1632
1633
1634
1635
1636
1637
1638
1639
1640
1641
1642
1643
1644
1645
1646
1647
1648
1649
1650
1651
1652

1653
1654
1655
1656
1657
1658
1659
1660
1661
1662
1663
1664
1665
1666
1667
1668
1669

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 reimplimented in version 2.8.1.
The index or table name argument is now ignored.
</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 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>This command will fail if there is an active transaction.  This 
command has no effect on an in-memory database.</p>
}


Section {SQLite keywords} keywords

puts {
<p>The following keywords are used by SQLite.  Most are either reserved 
words in SQL-92 or were listed as potential reserved words.  Those which 
aren't are shown in italics.  Not all of these words are actually used
by SQLite.  Keywords are not reserved in SQLite.  Any keyword can be used 
as an identifier for SQLite objects (columns, databases, indexes, tables, 
triggers, views, ...) but must generally be enclosed by brackets or 
quotes to avoid confusing the parser.  Keyword matching in SQLite is 
case-insensitive.</p>
︙︙