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: |
06bd5070966b59730a91bc4d384df4f8 |
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
Changes to www/lang.tcl.
1 2 3 | # # Run this Tcl script to generate the sqlite.html file. # | | | 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 | 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 | | | | 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 | 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 | | | | | | | | 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 | by a line that contains only a baskslash and a dot:} puts "\"[Operator \\.]\".</p>" Section {CREATE INDEX} createindex 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 [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 | 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> | | | | 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 | } {column-constraint} { NOT NULL [ <conflict-clause> ] | PRIMARY KEY [<sort-order>] [ <conflict-clause> ] | UNIQUE [ <conflict-clause> ] | CHECK ( <expr> ) [ <conflict-clause> ] | DEFAULT <value> } {constraint} { | | | | 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 | <unary-op> <expr> | ( <expr> ) | <column-name> | <table-name> . <column-name> | <database-name> . <table-name> . <column-name> | <literal-value> | <function-name> ( <expr-list> | STAR ) | | < > | 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 | 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> | < < < < < < < < < | 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 | 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> | | | | | | < | > > > > > > > > > > > > > > | > > > > > > | 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 | 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. | | > > > > > > > > > > | | | < | | 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> |
︙ | ︙ |