Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Fixed metacharacter coloring with LP, RP, PLUS. Added more links. (CVS 962) |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
2bbb08049508829419dd9a3729241b97 |
User & Date: | jplyon 2003-05-10 02:54:02.000 |
Context
2003-05-10
| ||
02:54 | Documented the "GO" and "\" command terminators. (CVS 963) (check-in: 6a76bd9590 user: jplyon tags: trunk) | |
02:54 | Fixed metacharacter coloring with LP, RP, PLUS. Added more links. (CVS 962) (check-in: 2bbb080495 user: jplyon tags: trunk) | |
2003-05-07
| ||
13:37 | Format keyword lists in lang.html using a TCL proc. (CVS 961) (check-in: ca75f1ca12 user: drh 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 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 | # # Run this Tcl script to generate the sqlite.html file. # set rcsid {$Id: lang.tcl,v 1.57 2003/05/10 02:54:02 jplyon Exp $} puts {<html> <head> <title>Query Language Understood By SQLite</title> </head> <body bgcolor=white> <h1 align=center> SQL As Understood By SQLite </h1>} puts "<p align=center> (This page was last modified on [lrange $rcsid 3 4] UTC) </p>" puts { <p>The SQLite library understands most of the standard SQL language. But it does <a href="omitted.html">omit some features</a> while at the same time adding a few features of its own. This document attempts to describe percisely what parts of the SQL language SQLite does and does not support. A list of <a href="#keywords">keywords<a/> is given at the end.</p> <p>In all of the syntax diagrams that follow, literal text is shown in bold blue. Non-terminal symbols are shown in italic red. Operators that are part of the syntactic markup itself are shown in black roman.</p> <p>This document is just an overview of the SQL syntax implemented by SQLite. Many low-level productions are omitted. For detailed information |
︙ | ︙ | |||
84 85 86 87 88 89 90 91 92 93 94 95 96 97 | regsub -all %GT $body {</font></i><b><font color="#2c2cf0">} body regsub -all {[]|[*?]} $body {</font></b>&<b><font color="#2c2cf0">} body regsub -all "\n" [string trim $body] "<br>\n" body regsub -all "\n *" $body "\n\\ \\ \\ \\ " body regsub -all {[|,.*()]} $body {<big>&</big>} body regsub -all { = } $body { <big>=</big> } body regsub -all {STAR} $body {<big>*</big>} body puts "<td><b><font color=\"#2c2cf0\">$body</font></b></td></tr>" } puts {</table>} } proc Operator {name} { return "<font color=\"#2c2cf0\"><big>$name</big></font>" } | > > > > > > | 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 | regsub -all %GT $body {</font></i><b><font color="#2c2cf0">} body regsub -all {[]|[*?]} $body {</font></b>&<b><font color="#2c2cf0">} body regsub -all "\n" [string trim $body] "<br>\n" body regsub -all "\n *" $body "\n\\ \\ \\ \\ " body regsub -all {[|,.*()]} $body {<big>&</big>} body regsub -all { = } $body { <big>=</big> } body regsub -all {STAR} $body {<big>*</big>} body ## These metacharacters must be handled to undo being ## treated as SQL punctuation characters above. regsub -all {RPPLUS} $body {</font></b>)+<b><font color="#2c2cf0">} body regsub -all {LP} $body {</font></b>(<b><font color="#2c2cf0">} body regsub -all {RP} $body {</font></b>)<b><font color="#2c2cf0">} body ## Place the left-hand side of the rule in the 2nd table column. puts "<td><b><font color=\"#2c2cf0\">$body</font></b></td></tr>" } puts {</table>} } proc Operator {name} { return "<font color=\"#2c2cf0\"><big>$name</big></font>" } |
︙ | ︙ | |||
750 751 752 753 754 755 756 | <expr> (+) | <expr> ISNULL | <expr> NOTNULL | <expr> [NOT] BETWEEN <expr> AND <expr> | <expr> [NOT] IN ( <value-list> ) | <expr> [NOT] IN ( <select-statement> ) | ( <select-statement> ) | | | | 757 758 759 760 761 762 763 764 765 766 767 768 769 770 771 | <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 { <p>This section is different from the others. Most other sections of this document talks about a particular SQL command. This section does |
︙ | ︙ | |||
797 798 799 800 801 802 803 804 805 806 807 808 809 810 811 812 813 814 815 816 | [Operator !=] or [Operator {<>}]. The [Operator ||] operator is \"concatenate\" - it joins together the two strings of its operands. The operator [Operator %] outputs the remainder of its left operand modulo its right operand.</p>" puts { <p>The LIKE operator does a wildcard comparision. The operand to the right contains the wildcards.} puts "A percent symbol [Operator %] in the right operand matches any sequence of zero or more characters on the left. An underscore [Operator _] on the right matches any single character on the left." puts {The LIKE operator is not case sensitive and will match upper case characters on one side against lower case characters on the other. (A bug: SQLite only understands upper/lower case for 7-bit Latin characters. Hence the LIKE operator is case sensitive for 8-bit iso8859 characters or UTF-8 characters. For example, the expression <b>'a' LIKE 'A'</b> is TRUE but | > | > > > | > > | 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 | [Operator !=] or [Operator {<>}]. The [Operator ||] operator is \"concatenate\" - it joins together the two strings of its operands. The operator [Operator %] outputs the remainder of its left operand modulo its right operand.</p>" puts { <a name="like"></a> <p>The LIKE operator does a wildcard comparision. The operand to the right contains the wildcards.} puts "A percent symbol [Operator %] in the right operand matches any sequence of zero or more characters on the left. An underscore [Operator _] on the right matches any single character on the left." puts {The LIKE operator is not case sensitive and will match upper case characters on one side against lower case characters on the other. (A bug: SQLite only understands upper/lower case for 7-bit Latin characters. Hence the LIKE operator is case sensitive for 8-bit iso8859 characters or UTF-8 characters. For example, the expression <b>'a' LIKE 'A'</b> is TRUE but <b>'æ' LIKE 'Æ'</b> is FALSE.). The infix LIKE operator is identical the user function <a href="#likeFunc"> like(<i>X</i>,<i>Y</i>)</a>. </p> <a name="glob"></a> <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 identical the user function <a href="#globFunc"> glob(<i>X</i>,<i>Y</i>)</a>.</p> <p>A column name can be any of the names defined in the CREATE TABLE statement or one of the following special identifiers: "<b>ROWID</b>", "<b>OID</b>", or "<b>_ROWID_</b>". These special identifiers all describe the unique random integer key (the "row key") associated with every row of every table. |
︙ | ︙ | |||
884 885 886 887 888 889 890 891 892 893 894 895 896 | <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.</td> </tr> <tr> <td valign="top" align="right">glob(<i>X</i>,<i>Y</i>)</td> <td valign="top">This function is used to implement the "<b>Y GLOB X</b>" syntax of SQLite. The <a href="c_interface.html#cfunc">sqlite_create_function()</a> interface can be used to override this function and thereby change the operation | > | > | | 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 | <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.</td> </tr> <tr> <a name="globFunc"></a> <td valign="top" align="right">glob(<i>X</i>,<i>Y</i>)</td> <td valign="top">This function is used to implement the "<b>Y GLOB X</b>" syntax of SQLite. The <a href="c_interface.html#cfunc">sqlite_create_function()</a> interface can be used to override this function and thereby change the operation of the <a href="#glob">GLOB</a> operator.</td> </tr> <tr> <td valign="top" align="right">last_insert_rowid()</td> <td valign="top">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 <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> <a name="likeFunc"></a> <td valign="top" align="right">like(<i>X</i>,<i>Y</i>)</td> <td valign="top">This function is used to implement the "<b>Y LIKE X</b>" syntax of SQL. The <a href="c_interface.html#cfunc">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.</td> </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 |
︙ | ︙ | |||
1042 1043 1044 1045 1046 1047 1048 | puts { <p>The INSERT statement comes in two basic forms. The first form (with the "VALUES" keyword) creates a single new row in an existing table. If no column-list is specified then the number of values must be the same as the number of columns in the table. If a column-list is specified, then the number of values must match the number of specified columns. Columns of the table that do not appear in the | | | 1057 1058 1059 1060 1061 1062 1063 1064 1065 1066 1067 1068 1069 1070 1071 | puts { <p>The INSERT statement comes in two basic forms. The first form (with the "VALUES" keyword) creates a single new row in an existing table. If no column-list is specified then the number of values must be the same as the number of columns in the table. If a column-list is specified, then the number of values must match the number of specified columns. Columns of the table that do not appear in the column list are filled with the default value, or with NULL if not default value is specified. </p> <p>The second form of the INSERT statement takes it data from a SELECT statement. The number of columns in the result of the SELECT must exactly match the number of columns in the table if no column list is specified, or it must match the number of columns |
︙ | ︙ | |||
1371 1372 1373 1374 1375 1376 1377 | <p>For each column in the named table, invoke the callback function once with information about that column, including the column name, data type, whether or not the column can be NULL, and the default value for the column.</p></li> <a name="pragma_temp_store"></a> <li><p><b>PRAGMA temp_store; | | | | | 1386 1387 1388 1389 1390 1391 1392 1393 1394 1395 1396 1397 1398 1399 1400 1401 1402 | <p>For each column in the named table, invoke the callback function once with information about that column, including the column name, data type, whether or not the column can be NULL, and the default value for the column.</p></li> <a name="pragma_temp_store"></a> <li><p><b>PRAGMA temp_store; <br>PRAGMA temp_store = DEFAULT; <br>PRAGMA temp_store = MEMORY; <br>PRAGMA temp_store = FILE;</b></p> <p>Query or change the setting of the "temp_store" flag affecting the database for the duration of the current database connection. The temp_store flag reverts to its default value when the database is closed and reopened. For additional information on the temp_store flag, see the description of the <a href="#pragma_default_temp_store"> <b>default_temp_store</b></a> pragma. Note that it is possible for the library compile-time options to override this setting. </p> |
︙ | ︙ | |||
1418 1419 1420 1421 1422 1423 1424 | Syntax {sql-statement} { SELECT [ALL | DISTINCT] <result> [FROM <table-list>] [WHERE <expr>] [GROUP BY <expr-list>] [HAVING <expr>] [<compound-op> <select>]* [ORDER BY <sort-expr-list>] | | | 1433 1434 1435 1436 1437 1438 1439 1440 1441 1442 1443 1444 1445 1446 1447 | Syntax {sql-statement} { SELECT [ALL | DISTINCT] <result> [FROM <table-list>] [WHERE <expr>] [GROUP BY <expr-list>] [HAVING <expr>] [<compound-op> <select>]* [ORDER BY <sort-expr-list>] [LIMIT <integer> [LP OFFSET | , RP <integer>]] } {result} { <result-column> [, <result-column>]* } {result-column} { STAR | <table-name> . STAR | <expr> [ [AS] <string> ] } {table-list} { <table> [<join-op> <table> <join-args>]* } {table} { |
︙ | ︙ | |||
1563 1564 1565 1566 1567 1568 1569 | 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 | | | 1578 1579 1580 1581 1582 1583 1584 1585 1586 1587 1588 1589 1590 1591 1592 | 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> <p>Keywords can be used as identifiers in three ways:</p> |
︙ | ︙ |