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 | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA1: | 06bd5070966b59730a91bc4d384df4f8 |
| User & Date: | jplyon 2004-01-19 05:09:24 |
Context
|
2004-01-20
| ||
| 11:54 | Make ATTACH and DETACH work with the non-callback API. Ticket #574. (CVS 1194) check-in: c32a80fe 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: 06bd5070 user: jplyon tags: trunk | |
| 04:57 | comment typos (CVS 1192) check-in: 7972ee32 user: jplyon tags: trunk | |
Changes
Changes to www/lang.tcl.
1 2 3 4 5 6 7 8 9 10 11 ... 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 ... 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 ... 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 ... 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 ... 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 ... 677 678 679 680 681 682 683 684 685 686 687 688 689 690 ... 693 694 695 696 697 698 699 700 701 702 703 704 705 706 ... 757 758 759 760 761 762 763 764 765 766 767 768 769 770 771 772 773 774 775 776 ... 851 852 853 854 855 856 857 858 859 860 861 862 863 864 865 866 867 868 869 870 871 872 873 .... 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 .... 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 |
#
# 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>
................................................................................
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>
................................................................................
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} {
................................................................................
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 {
................................................................................
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} {
................................................................................
} {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"
................................................................................
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>
}
................................................................................
<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 {
................................................................................
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
................................................................................
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>
................................................................................
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>
|
| | | | | | | | | | | | | | > > > > > > > > > > < > < < < < < < < < < | | | | | < | > > > > > > > > > > > > > > | > > > > > > | > > > > > > > > > > | | | < | |
1 2 3 4 5 6 7 8 9 10 11 ... 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 ... 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 ... 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 ... 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 ... 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 ... 677 678 679 680 681 682 683 684 685 686 687 688 689 690 691 692 693 694 695 ... 698 699 700 701 702 703 704 705 706 707 708 709 710 711 712 713 714 715 716 ... 767 768 769 770 771 772 773 774 775 776 777 778 779 780 781 782 783 784 785 786 ... 861 862 863 864 865 866 867 868 869 870 871 872 873 874 .... 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 .... 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 |
#
# 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>
................................................................................
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>
................................................................................
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} {
................................................................................
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 {
................................................................................
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} {
................................................................................
} {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"
................................................................................
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>
}
................................................................................
<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 {
................................................................................
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
................................................................................
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>
................................................................................
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>
|