Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Additional work toward denser hyperlinking in the documentation. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
aa8a072b1bdad84815f0f37279663e45 |
User & Date: | drh 2007-12-20 23:01:02.000 |
Context
2007-12-21
| ||
22:13 | Additional error diagnostics when the webserver fails. (check-in: 310ef51837 user: drh tags: trunk) | |
2007-12-20
| ||
23:01 | Additional work toward denser hyperlinking in the documentation. (check-in: aa8a072b1b user: drh tags: trunk) | |
03:29 | An attempt to get automatic hyperlinking working on all web pages. The website builds, but there are still many problems. (check-in: 16b1f03012 user: drh tags: trunk) | |
Changes
Changes to pages/changes.in.
︙ | ︙ | |||
15 16 17 18 19 20 21 | if {[regexp {\(([0-9.]+)\)} $date all vers]} { set label [string map {. _} $vers] hd_fragment version_$label } hd_puts "<dt><b>$date</b></dt>" hd_resolve "<dd><p><ul>$desc</ul></p>" hd_puts "</dd>" | | | 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 | if {[regexp {\(([0-9.]+)\)} $date all vers]} { set label [string map {. _} $vers] hd_fragment version_$label } hd_puts "<dt><b>$date</b></dt>" hd_resolve "<dd><p><ul>$desc</ul></p>" hd_puts "</dd>" 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 |
︙ | ︙ |
Changes to pages/faq.in.
︙ | ︙ | |||
388 389 390 391 392 393 394 | API should be prepared to handle SQLITE_SCHEMA errors. An example of one approach to this follows:</p> <blockquote><pre> int rc; sqlite3_stmt *pStmt; | | | 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 | API should be prepared to handle SQLITE_SCHEMA errors. An example of one approach to this follows:</p> <blockquote><pre> int rc; sqlite3_stmt *pStmt; char zSql[] = "SELECT ....."; do { /* Compile the statement from SQL. Assume success. */ sqlite3_prepare(pDb, zSql, -1, &pStmt, 0); while( SQLITE_ROW==sqlite3_step(pStmt) ){ /* Do something with the row of available data */ |
︙ | ︙ |
Changes to pages/lang.in.
︙ | ︙ | |||
82 83 84 85 86 87 88 | proc Keyword {name} { return "<font color=\"#2c2cf0\">$name</font>" } proc Example {text} { hd_puts "<blockquote><pre>$text</pre></blockquote>" } | | > | | > | | | | 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 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 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 | proc Keyword {name} { return "<font color=\"#2c2cf0\">$name</font>" } proc Example {text} { hd_puts "<blockquote><pre>$text</pre></blockquote>" } proc Section {name label keywords} { global DOC hd_close_main hd_open_main lang_$label.html hd_header "SQLite Query Language: $name" $DOC/pages/lang.in eval hd_keywords $keywords hd_puts {<a href="lang.html"><h2>SQL As Understood By SQLite</h2></a>} hd_puts "<h3>$name</h3>" } ############################################################################### Section {ALTER TABLE} altertable {{ALTER TABLE} {ALTER}} Syntax {sql-statement} { ALTER TABLE [<database-name> .] <table-name> <alteration> } {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>[database-name.]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 table name. If this is required, the triggers or view definitions must be dropped and recreated to use the new table name by hand. </p> <p>The ADD [COLUMN] syntax is used to add a new column to an existing table. The new column is always appended to the end of the list of existing columns. <i>Column-def</i> may take any of the forms permissable in a [CREATE TABLE] statement, with the following restrictions: <ul> <li>The column may not have a PRIMARY KEY or UNIQUE constraint.</li> <li>The column may not have a default value of CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP.</li> <li>If a NOT NULL constraint is specified, then the column must have a default value other than NULL. </ul> <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 } Syntax {sql-statement} { ANALYZE <database-name> } |
︙ | ︙ | |||
174 175 176 177 178 179 180 | 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> | | | 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 | 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 |
︙ | ︙ | |||
226 227 228 229 230 231 232 | the main database were ":memory:". </p> <p>There is a compile-time limit of 10 attached database files.</p> <tcl> ############################################################################### | | | 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 | 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>]] } Syntax {sql-statement} { END [TRANSACTION [<name>]] } |
︙ | ︙ | |||
339 340 341 342 343 344 345 | 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 error occurs, it might be necessary for SQLite to rollback and cancel the transaction. An application can tell which course of action SQLite took by using the | < | | | 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 | 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 error occurs, it might be necessary for SQLite to rollback and cancel the transaction. An application can tell which course of action SQLite took by using the [sqlite3_get_autocommit()] C-language interface.</p> <p>It is recommended that applications respond to the errors listed above by explicitly issuing a ROLLBACK command. If the transaction has already been rolled back automatically by the error response, then the ROLLBACK command will fail with an error, but no harm is caused by this.</p> <p>Future versions of SQLite may extend the list of errors which might cause automatic transaction rollback. Future versions of SQLite might change the error response. In particular, we may choose to simplify the interface in future versions of SQLite by causing the errors above to force an unconditional rollback.</p> <tcl> ############################################################################### Section comment comment {comment comments} Syntax {comment} {<SQL-comment> | <C-comment> } {SQL-comment} {-- <single-line> } {C-comment} {/STAR <multiple-lines> [STAR/] } </tcl> |
︙ | ︙ | |||
382 383 384 385 386 387 388 | 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> <tcl> ############################################################################## | | | 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 | 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> <tcl> ############################################################################## Section {CREATE INDEX} createindex {{CREATE INDEX}} Syntax {sql-statement} { CREATE [UNIQUE] INDEX [IF NOT EXISTS] [<database-name> .] <index-name> ON <table-name> ( <column-name> [, <column-name>]* ) } {column-name} { <name> [ COLLATE <collation-name>] [ ASC | DESC ] } |
︙ | ︙ | |||
430 431 432 433 434 435 436 | <p>Indexes are removed with the <a href="lang_dropindex.html">DROP INDEX</a> command.</p> <tcl> ############################################################################## | | | 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 | <p>Indexes are removed with the <a href="lang_dropindex.html">DROP INDEX</a> command.</p> <tcl> ############################################################################## Section {CREATE TABLE} {createtable} {{CREATE TABLE}} Syntax {sql-command} { CREATE [TEMP | TEMPORARY] TABLE [IF NOT EXISTS] [<database-name> .] <table-name> ( <column-def> [, <column-def>]* [, <constraint>]* ) } {sql-command} { |
︙ | ︙ | |||
581 582 583 584 585 586 587 | with the same name aleady exists, then this command becomes a no-op.</p> <p>Tables are removed using the <a href="lang_droptable.html">DROP TABLE</a> statement. </p> <tcl> ############################################################################## | | | 582 583 584 585 586 587 588 589 590 591 592 593 594 595 596 | with the same name aleady exists, then this command becomes a no-op.</p> <p>Tables are removed using the <a href="lang_droptable.html">DROP TABLE</a> statement. </p> <tcl> ############################################################################## Section {CREATE TRIGGER} createtrigger {{CREATE TRIGGER}} Syntax {sql-statement} { CREATE [TEMP | TEMPORARY] TRIGGER [IF NOT EXISTS] <trigger-name> [ BEFORE | AFTER ] <database-event> ON [<database-name> .] <table-name> <trigger-action> } |
︙ | ︙ | |||
737 738 739 740 741 742 743 | <p>Triggers are removed using the <a href="lang_droptrigger.html">DROP TRIGGER</a> statement.</p> <tcl> ############################################################################### | | | 738 739 740 741 742 743 744 745 746 747 748 749 750 751 752 | <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 |
︙ | ︙ | |||
770 771 772 773 774 775 776 | <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> ############################################################################## | | | 771 772 773 774 775 776 777 778 779 780 781 782 783 784 785 | <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> )] } </tcl> <p>A virtual table is an interface to an external storage or computation |
︙ | ︙ | |||
806 807 808 809 810 811 812 | <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> ############################################################################## | | | | | 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 | <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 |
︙ | ︙ | |||
866 867 868 869 870 871 872 | 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> ############################################################################## | | | 867 868 869 870 871 872 873 874 875 876 877 878 879 880 881 | 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= |
︙ | ︙ | |||
892 893 894 895 896 897 898 | 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> ############################################################################## | | | | | | 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 | 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 Syntax {sql-statement} { EXPLAIN <sql-statement> } </tcl> <p>The EXPLAIN command modifier is a non-standard extension. The idea comes from a similar command found in PostgreSQL, but the operation is completely different.</p> <p>If the EXPLAIN keyword appears before any other SQLite SQL command then instead of actually executing the command, the SQLite library will report back the sequence of virtual machine instructions it would have used to execute the command had the EXPLAIN keyword not been present. For additional information about virtual machine instructions see the <a href="arch.html">architecture description</a> or the documentation on <a href="opcode.html">available opcodes</a> for the virtual machine.</p> <tcl> ############################################################################## Section expression expr {expression {expression syntax}} Syntax {expr} { <expr> <binary-op> <expr> | <expr> [NOT] <like-op> <expr> [ESCAPE <expr>] | <unary-op> <expr> | ( <expr> ) | <column-name> | |
︙ | ︙ | |||
998 999 1000 1001 1002 1003 1004 1005 1006 | </pre></blockquote> <p>The COLLATE operator can be thought of as a unary postfix operator. The COLLATE operator has the highest precedence. It always binds more tightly than any prefix unary operator or any binary operator.</p> <p>The unary operator [Operator +] is a no-op. It can be applied to strings, numbers, or blobs and it always gives as its result the | > | | 999 1000 1001 1002 1003 1004 1005 1006 1007 1008 1009 1010 1011 1012 1013 1014 1015 1016 | </pre></blockquote> <p>The COLLATE operator can be thought of as a unary postfix operator. The COLLATE operator has the highest precedence. It always binds more tightly than any prefix unary operator or any binary operator.</p> <tcl>hd_puts " <p>The unary operator [Operator +] is a no-op. It can be applied to strings, numbers, or blobs and it always gives as its result the value of the operand.</p>"</tcl> <p>Note that there are two variations of the equals and not equals operators. Equals can be either <tcl> hd_puts "[Operator =] or [Operator ==]. The non-equals operator can be either |
︙ | ︙ | |||
1043 1044 1045 1046 1047 1048 1049 | <p> A literal value can also be the token "NULL". </p> <p> A parameter specifies a placeholder in the expression for a literal value that is filled in at runtime using the | | | 1045 1046 1047 1048 1049 1050 1051 1052 1053 1054 1055 1056 1057 1058 1059 | <p> A literal value can also be the token "NULL". </p> <p> 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> |
︙ | ︙ | |||
1082 1083 1084 1085 1086 1087 1088 | any text at all. This syntax is the form of a variable name in the Tcl programming language.</td> </tr> </table> </blockquote> <p>Parameters that are not assigned values using | | | | 1084 1085 1086 1087 1088 1089 1090 1091 1092 1093 1094 1095 1096 1097 1098 1099 1100 1101 | any text at all. This syntax is the form of a variable name in the Tcl programming language.</td> </tr> </table> </blockquote> <p>Parameters that are not assigned values using [sqlite3_bind_blob() | sqlite3_bind()] are treated as NULL.</p> <tcl>hd_fragment like LIKE ESCAPE</tcl> <p>The LIKE operator does a pattern matching comparison. The operand to the right contains the pattern, the left hand operand contains the string to match against the pattern. <tcl>hd_puts "A percent symbol [Operator %] in the pattern matches any sequence of zero or more characters in the string. An underscore [Operator _] in the pattern matches any single character in the |
︙ | ︙ | |||
1125 1126 1127 1128 1129 1130 1131 | operator is implemented by calling the user function <a href="#likeFunc"> like(<i>X</i>,<i>Y</i>)</a>. If an ESCAPE clause is present, it adds a third parameter to the function call. If the functionality of LIKE can be overridden by defining an alternative implementation of the like() SQL function.</p> </p> | | | | | | | | | | | | | 1127 1128 1129 1130 1131 1132 1133 1134 1135 1136 1137 1138 1139 1140 1141 1142 1143 1144 1145 1146 1147 1148 1149 1150 1151 1152 1153 1154 1155 1156 1157 1158 1159 1160 1161 1162 1163 1164 1165 1166 1167 1168 1169 1170 1171 1172 1173 1174 1175 1176 1177 1178 1179 1180 1181 1182 1183 1184 1185 1186 1187 1188 1189 1190 1191 | operator is implemented by calling the user function <a href="#likeFunc"> like(<i>X</i>,<i>Y</i>)</a>. If an ESCAPE clause is present, it adds a third parameter to the function call. If the functionality of LIKE can be overridden by defining an alternative implementation of the like() SQL function.</p> </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" is added at run-time, that function will be called in order to implement the REGEXP operator.</p> <tcl>hd_fragment match MATCH</tcl> <p>The MATCH operator is a special syntax for the match() user function. The default match() function implementation raises and exception and is not really useful for anything. But extensions can override the match() function with more helpful logic.</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 integer key (the "row key") associated with every row of every table. 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, as a scalar quantity, or as the operand of an EXISTS operator. As a scalar quantity or the operand of an IN operator, the SELECT should have only a single column in its result. Compound SELECTs (connected with keywords like UNION or EXCEPT) are allowed. With the EXISTS operator, the columns in the result set of the [SELECT] are ignored and the expression returns TRUE if one or more rows exist and FALSE if the result set is empty. If no terms in the [SELECT] expression refer to value in the containing query, then the expression is evaluated once prior to any other processing and the result is reused as necessary. If the [SELECT] expression does contain variables from the outer query, then the [SELECT] is reevaluated every time it is needed.</p> <p>When a SELECT is the right operand of the IN operator, the IN operator returns TRUE if the result of the left operand is any of the values generated by the select. The IN operator may be preceded by the NOT keyword to invert the sense of the test.</p> |
︙ | ︙ | |||
1205 1206 1207 1208 1209 1210 1211 | 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 | | < | < | | 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 | 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> |
︙ | ︙ | |||
1269 1270 1271 1272 1273 1274 1275 | <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"> | | | 1269 1270 1271 1272 1273 1274 1275 1276 1277 1278 1279 1280 1281 1282 1283 | <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 [ESCAPE Z]</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 |
︙ | ︙ | |||
1299 1300 1301 1302 1303 1304 1305 | <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 | < | | 1299 1300 1301 1302 1303 1304 1305 1306 1307 1308 1309 1310 1311 1312 1313 | <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 |
︙ | ︙ | |||
1351 1352 1353 1354 1355 1356 1357 | <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. | | | 1350 1351 1352 1353 1354 1355 1356 1357 1358 1359 1360 1361 1362 1363 1364 | <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 |
︙ | ︙ | |||
1457 1458 1459 1460 1461 1462 1463 | <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 | | | | 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 | <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 |
︙ | ︙ | |||
1552 1553 1554 1555 1556 1557 1558 | and an integer overflow occurs at any point during the computation. Total() never throws an exception.</p> </tr> </table> <tcl> ############################################################################## | | | 1551 1552 1553 1554 1555 1556 1557 1558 1559 1560 1561 1562 1563 1564 1565 | 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} { INSERT [OR <conflict-algorithm>] INTO [<database-name> .] <table-name> [(<column-list>)] VALUES(<value-list>) | INSERT [OR <conflict-algorithm>] INTO [<database-name> .] <table-name> [(<column-list>)] <select-statement> } </tcl> |
︙ | ︙ | |||
1589 1590 1591 1592 1593 1594 1595 | 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> ############################################################################## | | | 1588 1589 1590 1591 1592 1593 1594 1595 1596 1597 1598 1599 1600 1601 1602 | 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> |
︙ | ︙ | |||
1668 1669 1670 1671 1672 1673 1674 | <p>The algorithm specified in the OR clause of a INSERT or UPDATE overrides any algorithm specified in a CREATE TABLE. If no algorithm is specified anywhere, the ABORT algorithm is used.</p> <tcl> ############################################################################## | | | > | | | 1667 1668 1669 1670 1671 1672 1673 1674 1675 1676 1677 1678 1679 1680 1681 1682 1683 1684 1685 1686 1687 1688 1689 1690 1691 1692 1693 1694 1695 1696 1697 1698 1699 1700 1701 1702 1703 1704 1705 1706 1707 1708 1709 1710 1711 1712 1713 1714 1715 1716 1717 1718 1719 1720 1721 1722 1723 1724 1725 1726 1727 1728 | <p>The algorithm specified in the OR clause of a INSERT or UPDATE overrides any algorithm specified in a CREATE TABLE. If no algorithm is specified anywhere, the ABORT algorithm is used.</p> <tcl> ############################################################################## Section REINDEX reindex REINDEX Syntax {sql-statement} { REINDEX <collation name> } Syntax {sql-statement} { REINDEX [<database-name> .] <table/index-name> } </tcl> <p>The REINDEX command is used to delete and recreate indices from scratch. This is useful when the definition of a collation sequence has changed. </p> <p>In the first form, all indices in all attached databases that use the named collation sequence are recreated. In the second form, if <i>[database-name.]table/index-name</i> identifies a table, then all indices associated with the table are rebuilt. If an index is identified, then only this specific index is deleted and recreated. </p> <p>If no <i>database-name</i> is specified and there exists both a table or index and a collation sequence of the specified name, then indices associated with the collation sequence only are reconstructed. This ambiguity may be dispelled by always specifying a <i>database-name</i> when reindexing a specific table or index. <tcl> ############################################################################### Section REPLACE replace REPLACE Syntax {sql-statement} { REPLACE INTO [<database-name> .] <table-name> [( <column-list> )] VALUES ( <value-list> ) | REPLACE INTO [<database-name> .] <table-name> [( <column-list> )] <select-statement> } </tcl> <p>The REPLACE command is an alias for the "INSERT OR REPLACE" variant of the <a href="lang_insert.html">INSERT</a> command. This alias is provided for compatibility with MySQL. See the <a href="lang_insert.html">INSERT</a> command documentation for additional information.</p> <tcl> ############################################################################### Section SELECT select {SELECT query} Syntax {sql-statement} { SELECT [ALL | DISTINCT] <result> [FROM <table-list>] [WHERE <expr>] [GROUP BY <expr-list>] [HAVING <expr>] [<compound-op> <select>]* |
︙ | ︙ | |||
1844 1845 1846 1847 1848 1849 1850 | left and right SELECTs. EXCEPT takes the result of left SELECT after removing the results of the right SELECT. When three or more SELECTs are connected into a compound, they group from left to right.</p> <tcl> ############################################################################## | | | 1844 1845 1846 1847 1848 1849 1850 1851 1852 1853 1854 1855 1856 1857 1858 | left and right SELECTs. EXCEPT takes the result of left SELECT after removing the results of the right SELECT. When three or more SELECTs are connected into a compound, they group from left to right.</p> <tcl> ############################################################################## Section UPDATE update {UPDATE UPDATEs} Syntax {sql-statement} { UPDATE [ OR <conflict-algorithm> ] [<database-name> .] <table-name> SET <assignment> [, <assignment>]* [WHERE <expr>] } {assignment} { <column-name> = <expr> |
︙ | ︙ | |||
1870 1871 1872 1873 1874 1875 1876 | 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> ############################################################################## | | | 1870 1871 1872 1873 1874 1875 1876 1877 1878 1879 1880 1881 1882 1883 1884 | 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 |
︙ | ︙ | |||
2043 2044 2045 2046 2047 2048 2049 | VIEW* VIRTUAL* WHEN WHERE }] hd_puts {<DIV class="pdf_section">} | | | 2043 2044 2045 2046 2047 2048 2049 2050 2051 2052 2053 2054 2055 2056 2057 | VIEW* VIRTUAL* WHEN WHERE }] hd_puts {<DIV class="pdf_section">} Section {SQLite Keywords} keywords {{SQL keyword} {SQL keywords}} hd_puts {</DIV>} </tcl> <p>The SQL standard specifies a huge number of keywords which may not be used as the names of tables, indices, columns, databases, user-defined functions, collations, virtual table modules, or any other named object. The list of keywords is so long that few people can remember them all. |
︙ | ︙ | |||
2068 2069 2070 2071 2072 2073 2074 | <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 string literal.</td></tr> | | | 2068 2069 2070 2071 2072 2073 2074 2075 2076 2077 2078 2079 2080 2081 2082 | <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 string literal.</td></tr> <tr> <td valign="top"><b>[keyword]</b></td><td></td> <td>A keyword enclosed in square brackets is always understood as an identifier. This is not standard SQL. This quoting mechanism is used by MS Access and SQL Server and is included in SQLite for compatibility.</td></tr> </table> </blockquote> </p> |
︙ | ︙ |
Changes to pages/mostdeployed.in.
︙ | ︙ | |||
18 19 20 21 22 23 24 | multiple copies of SQLite. SQLite is used on servers, but it is also used on desktop PC, and in cellphones, and PDAs, and MP3-players, and set-top boxes.</p> <h3>Estimates</h3> <p>At the end of 2006, there were 100 million websites on the internet. | | | | | | | 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 | multiple copies of SQLite. SQLite is used on servers, but it is also used on desktop PC, and in cellphones, and PDAs, and MP3-players, and set-top boxes.</p> <h3>Estimates</h3> <p>At the end of 2006, there were 100 million websites on the internet. <a href="http://news.netcraft.com/archives/2006/11/01/november_2006_web_server_survey.html">[1]</a> Let us use that number as a proxy for the number of deployed SQL database engines other than SQLite. Not every website runs an SQL database engine. Larger websites run multiple database engines. But the vast majority of smaller websites (the long tail) share a database engine with several other websites, if they use database engine at all Hopefully these two factors cancel each other out and give 100 million as a reasonable estimate for the number of non-SQLite SQL database engines currently deployed.</p> <p>Now let's consider where SQLite is used:</p> <ul> <li>125 million copies of Mozilla Firefox <a href="http://arstechnica.com/news.ars/post/20071202-mozilla-coo-over-125-million-people-use-firefox.html">[2]</a></li> <li>20 million Mac computers, each of which contains multiple copies of SQLite</li> <li>20 million websites run PHP which has SQLite built in. <a href="http://www.php.net/usage.php">[3]</a> We have no way of estimating what fraction of those sites actively use SQLite, but we think it is a significant fraction.<li> <li>300 million downloads of the <a href="http://www.skype.com/">Skype</a> client software and 100 million registered users <a href="http://skypejournal.com/blog/archives/2006/04/how_many_users_does_skype_have_today.php">[4]</a>. All recent versions of the Skype client use SQLite internally.</li> <li>20 million Symbian smartphones shipped in Q3 2007 <a href="http://www.symbian.com/news/pr/2007/pr20079552.html">[5]</a> Newer versions of the SymbianOS have SQLite built in. It is unclear exactly how many Symbian phones actually contain SQLite, so we will use a single quarter's sales as a lower bound.</li> <li>Millions and millions of copies of <a href="http://www.mcafee.com/">McAfee</a> anti-virus software all use SQLite internally.</li> <li>Millions of iPhones use SQLite</li> |
︙ | ︙ |
Changes to pages/pragma.in.
1 2 3 | <title>Pragma statements supported by SQLite</title> <tcl> | | | > > > > > > > > | | | | | | | > | | | | | | | | | | | | | < < < < < < < < | | | | | | 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 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 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 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 | <title>Pragma statements supported by SQLite</title> <tcl> proc Section {name {label {}} {keywords {}}} { hd_puts "\n<hr />" if {$label!=""} { hd_fragment $label if {$keywords!=""} { eval hd_keywords $keywords } } hd_puts "<h1>$name</h1>\n" } proc Subsection {args} { set f [lindex $args 0] hd_fragment pragma_$f eval hd_keywords $args } </tcl> <p>The [PRAGMA] statement is a special SQL statement used to modify the operation of the SQLite library or to query the library for internal (non-table) data. The [PRAGMA] statement is issued using the same interface as other SQLite commands (e.g. [SELECT], [INSERT]) but is different in the following important respects: </p> <ul> <li>Specific pragma statements may be removed and others added in future releases of SQLite. Use with caution! <li>No error messages are generated if an unknown pragma is issued. Unknown pragmas are simply ignored. This means if there is a typo in a pragma statement the library does not inform the user of the fact. <li>Some pragmas take effect during the SQL compilation stage, not the execution stage. This means if using the C-language [sqlite3_prepare()], [sqlite3_step()], [sqlite3_finalize()] API (or similar in a wrapper interface), the pragma may be applied to the library during the [sqlite3_prepare()] call. <li>The pragma command is unlikely to be compatible with any other SQL engine. </ul> <p>The available pragmas fall into four basic categories:</p> <ul> <li>Pragmas used to <a href="#modify">modify the operation</a> of the SQLite library in some manner, or to query for the current mode of operation. <li>Pragmas used to <a href="#schema">query the schema</a> of the current database. <li>Pragmas used to <a href="#version">query or modify the databases two version values</a>, the schema-version and the user-version. <li>Pragmas used to <a href="#debug">debug the library</a> and verify that database files are not corrupted. </ul> <tcl> Section {PRAGMA command syntax} syntax {PRAGMA} Syntax {sql-statement} { PRAGMA <name> [= <value>] | PRAGMA <function>(<arg>) } </tcl> <p>The pragmas that take an integer <b><i>value</i></b> also accept symbolic names. The strings "<b>on</b>", "<b>true</b>", and "<b>yes</b>" are equivalent to <b>1</b>. The strings "<b>off</b>", "<b>false</b>", and "<b>no</b>" are equivalent to <b>0</b>. These strings are case- insensitive, and do not require quotes. An unrecognized string will be treated as <b>1</b>, and will not generate an error. When the <i>value</i> is returned it is as an integer.</p> <tcl>Section {Pragmas to modify library operation} modify</tcl> </tcl> <ul> <tcl>Subsection auto_vacuum</tcl> <li><p><b>PRAGMA auto_vacuum;<br> PRAGMA auto_vacuum = </b> <i>0 | none | 1 | full | 2 | incremental</i><b>;</b></p> <p>Query or set the auto-vacuum flag in the database.</p> <p>Normally, (that is to say when auto_vacuum is 0 or "none") when a transaction that deletes data from a database is committed, the database file remains the same size. Unused database file pages are added to a "freelist" are reused for subsequent inserts. The database file does not shrink. In this mode the [VACUUM] command can be used to reclaim unused space.</p> <p>When the auto-vacuum flag is 1 (full), the freelist pages are moved to the end of the file and the file is truncated to remove the freelist pages at every commit. Note, however, that auto-vacuum only truncates the freelist pages from the file. Auto-vacuum does not defragment the database nor repack individual database pages the way that the [VACUUM] command does. In fact, because it moves pages around within the file, auto-vacuum can actually make fragmentation worse.</p> <p>Auto-vacuuming is only possible if the database stores some additional information that allows each database page to be traced backwards to its referer. Therefore, auto-vacuuming must be turned on before any tables are created. It is not possible to enable or disable auto-vacuum after a table has been created.</p> <p>When the value of auto-vacuum is 2 (incremental) then the additional information needed to do autovacuuming is stored in the database file but autovacuuming does not occur automatically at each commit as it does with auto_vacuum==full. In incremental mode, the separate [incremental_vacuum] pragma must be invoked to cause the vacuum to occur.</p> <p>The database connection can be changed between full and incremental autovacuum mode at will. However, the connection cannot be changed in and out of the "none" mode after any table has been created in the database. </p></li> <tcl>Subsection cache_size</tcl> <li><p><b>PRAGMA cache_size; <br>PRAGMA cache_size = </b><i>Number-of-pages</i><b>;</b></p> <p>Query or change the maximum number of database disk pages that SQLite will hold in memory at once. Each page uses about 1.5K of memory. The default cache size is 2000. If you are doing [UPDATEs] or [DELETEs] that change many rows of a database and you do not mind if SQLite uses more memory, you can increase the cache size for a possible speed improvement.</p> <p>When you change the cache size using the cache_size pragma, the change only endures for the current session. The cache size reverts to the default value when the database is closed and reopened. Use the [default_cache_size] pragma to check the cache size permanently.</p></li> <tcl>Subsection case_sensitive_like</tcl> <li><p><b>PRAGMA case_sensitive_like; <br>PRAGMA case_sensitive_like = </b><i>0 | 1</i><b>;</b></p> <p>The default behavior of the [LIKE] operator is to ignore case for latin1 characters. Hence, by default <b>'a' LIKE 'A'</b> is true. The case_sensitive_like pragma can be turned on to change this behavior. When case_sensitive_like is enabled, <b>'a' LIKE 'A'</b> is false but <b>'a' LIKE 'a'</b> is still true.</p> </li> <tcl>Subsection count_changes</tcl> <li><p><b>PRAGMA count_changes; <br>PRAGMA count_changes = </b><i>0 | 1</i><b>;</b></p> <p>Query or change the count-changes flag. Normally, when the count-changes flag is not set, [INSERT], [UPDATE] and [DELETE] statements return no data. When count-changes is set, each of these commands returns a single row of data consisting of one integer value - the number of rows inserted, modified or deleted by the command. The returned change count does not include any insertions, modifications or deletions performed by triggers.</p> <tcl>Subsection default_cache_size</tcl> <li><p><b>PRAGMA default_cache_size; <br>PRAGMA default_cache_size = </b><i>Number-of-pages</i><b>;</b></p> <p>Query or change the maximum number of database disk pages that SQLite will hold in memory at once. Each page uses 1K on disk and about 1.5K in memory. This pragma works like the [cache_size] pragma with the additional feature that it changes the cache size persistently. With this pragma, you can set the cache size once and that setting is retained and reused every time you reopen the database.</p></li> <tcl>Subsection empty_result_callbacks</tcl> <li><p><b>PRAGMA empty_result_callbacks; <br>PRAGMA empty_result_callbacks = </b><i>0 | 1</i><b>;</b></p> <p>Query or change the empty-result-callbacks flag.</p> <p>The empty-result-callbacks flag affects the [sqlite3_exec()] API only. Normally, when the empty-result-callbacks flag is cleared, the callback function supplied to the [sqlite3_exec()] call is not invoked for commands that return zero rows of data. When empty-result-callbacks is set in this situation, the callback function is invoked exactly once, with the third parameter set to 0 (NULL). This is to enable programs that use the [sqlite3_exec()] API to retrieve column-names even when a query returns no data. </p> <tcl>Subsection encoding</tcl> <li><p><b>PRAGMA encoding; <br>PRAGMA encoding = "UTF-8"; <br>PRAGMA encoding = "UTF-16"; <br>PRAGMA encoding = "UTF-16le"; <br>PRAGMA encoding = "UTF-16be";</b></p> <p>In first form, if the main database has already been created, then this pragma returns the text encoding used by the |
︙ | ︙ | |||
197 198 199 200 201 202 203 | it is created by this session. The string "UTF-16" is interpreted as "UTF-16 encoding using native machine byte-ordering". If the second and subsequent forms are used after the database file has already been created, they have no effect and are silently ignored.</p> <p>Once an encoding has been set for a database, it cannot be changed.</p> | | | | | | | | | | | | | | 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 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 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 | it is created by this session. The string "UTF-16" is interpreted as "UTF-16 encoding using native machine byte-ordering". If the second and subsequent forms are used after the database file has already been created, they have no effect and are silently ignored.</p> <p>Once an encoding has been set for a database, it cannot be changed.</p> <p>Databases created by the [ATTACH] command always use the same encoding as the main database.</p> </li> <tcl>Subsection full_column_names</tcl> <li><p><b>PRAGMA full_column_names; <br>PRAGMA full_column_names = </b><i>0 | 1</i><b>;</b></p> <p>Query or change the full-column-names flag. This flag affects the way SQLite names columns of data returned by [SELECT] statements when the expression for the column is a table-column name or the wildcard "*". Normally, such result columns are named <table-name/alias><column-name> if the [SELECT] statement joins two or more tables together, or simply <column-name> if the [SELECT] statement queries a single table. When the full-column-names flag is set, such columns are always named <table-name/alias> <column-name> regardless of whether or not a join is performed. </p> <p>If both the short-column-names and full-column-names are set, then the behaviour associated with the full-column-names flag is exhibited. </p> </li> <tcl>Subsection fullfsync</tcl> <li><p><b>PRAGMA fullfsync <br>PRAGMA fullfsync = </b><i>0 | 1</i><b>;</b></p> <p>Query or change the fullfsync flag. This flag affects determines whether or not the F_FULLFSYNC syncing method is used on systems that support it. The default value is off. As of this writing (2006-02-10) only Mac OS X supports F_FULLFSYNC. </p> </li> <tcl>Subsection incremental_vacuum</tcl> <li><p><b>PRAGMA incremental_vacuum</b><i>(N)</i><b>;</b></p> <p>The incremental_vacuum pragma causes up to <i>N</i> pages to be removed from the freelist. The database file is truncated by the same amount. The incremental_vacuum pragma has no effect if the database is not in <a href="#pragma_auto_vacuum">auto_vacuum==incremental</a> mode or if there are no pages on the freelist. If there are fewer than <i>N</i> pages on the freelist, then the entire freelist is cleared.</p> <p>As of [version 3.4.0] (the first version that supports incremental_vacuum) this feature is still experimental. Possible future changes include enhancing incremental vacuum to do defragmentation and node repacking just as the full-blown [VACUUM] command does. And incremental vacuum may be promoted from a pragma to a separate SQL command, or perhaps some variation on the [VACUUM] command. Programmers are cautioned to not become enamored with the current syntax or functionality as it is likely to change.</p> </li> <tcl>Subsection legacy_file_format</tcl> <li><p><b>PRAGMA legacy_file_format; <br>PRAGMA legacy_file_format = <i>ON | OFF</i></b></p> <p>This pragma sets or queries the value of the legacy_file_format flag. When this flag is on, new SQLite databases are created in a file format that is readable and writable by all versions of SQLite going back to 3.0.0. When the flag is off, new databases are created using the latest file format which might not be readable or writable by older versions of SQLite.</p> <p>When the pragma is issued with no argument, it returns the setting of the flag. This pragma does <u>not</u> tell which file format the current database is using. It tells what format will be used by any newly created databases.</p> <p>This flag only affects newly created databases. It has no effect on databases that already exist.</p> </li> <tcl>Subsection locking_mode</tcl> <li><p><b>PRAGMA locking_mode; <br>PRAGMA locking_mode = <i>NORMAL | EXCLUSIVE</i></b></p> <p>This pragma sets or queries the database connection locking-mode. The locking-mode is either NORMAL or EXCLUSIVE. <p>In NORMAL locking-mode (the default), a database connection unlocks the database file at the conclusion of each read or |
︙ | ︙ | |||
309 310 311 312 313 314 315 | <blockquote> PRAGMA <b>main.</b>locking_mode=EXCLUSIVE; </blockquote> <p>Then the locking mode applies only to the named database. If no database name qualifier preceeds the "locking_mode" keyword then the locking mode is applied to all databases, including any new | | < | | | < | | | | | | | 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 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 | <blockquote> PRAGMA <b>main.</b>locking_mode=EXCLUSIVE; </blockquote> <p>Then the locking mode applies only to the named database. If no database name qualifier preceeds the "locking_mode" keyword then the locking mode is applied to all databases, including any new databases added by subsequent [ATTACH] commands.</p> <p>The "temp" database (in which TEMP tables and indices are stored) always uses exclusive locking mode. The locking mode of temp cannot be changed. All other databases use the normal locking mode by default and are affected by this pragma.</p> </li> <tcl>Subsection page_size</tcl> <li><p><b>PRAGMA page_size; <br>PRAGMA page_size = </b><i>bytes</i><b>;</b></p> <p>Query or set the page-size of the database. The page-size may only be set if the database has not yet been created. The page size must be a power of two greater than or equal to 512 and less than or equal to 8192. The upper limit may be modified by setting the value of macro SQLITE_MAX_PAGE_SIZE during compilation. The maximum upper bound is 32768. </p> </li> <tcl>Subsection max_page_count</tcl> <li><p><b>PRAGMA max_page_count; <br>PRAGMA max_page_count = </b><i>N</i><b>;</b></p> <p>Query or set the maximum number of pages in the database file. Both forms of the pragma return the maximum page count. The second form attempts to modify the maximum page count. The maximum page count cannot be reduced below the current database size. </p> </li> <tcl>Subsection read_uncommitted</tcl> <li><p><b>PRAGMA read_uncommitted; <br>PRAGMA read_uncommitted = </b><i>0 | 1</i><b>;</b></p> <p>Query, set, or clear READ UNCOMMITTED isolation. The default isolation level for SQLite is SERIALIZABLE. Any process or thread can select READ UNCOMMITTED isolation, but SERIALIZABLE will still be used except between connections that share a common page and schema cache. Cache sharing is enabled using the [sqlite3_enable_shared_cache()] API and is only available between connections running the same thread. Cache sharing is off by default. </p> </li> <tcl>Subsection short_column_names</tcl> <li><p><b>PRAGMA short_column_names; <br>PRAGMA short_column_names = </b><i>0 | 1</i><b>;</b></p> <p>Query or change the short-column-names flag. This flag affects the way SQLite names columns of data returned by [SELECT] statements when the expression for the column is a table-column name or the wildcard "*". Normally, such result columns are named <table-name/alias>lt;column-name> if the [SELECT] statement joins two or more tables together, or simply <column-name> if the [SELECT] statement queries a single table. When the short-column-names flag is set, such columns are always named <column-name> regardless of whether or not a join is performed. </p> <p>If both the short-column-names and full-column-names are set, then the behaviour associated with the full-column-names flag is exhibited. </p> </li> <tcl>Subsection synchronous</tcl> <li><p><b>PRAGMA synchronous; <br>PRAGMA synchronous = FULL; </b>(2)<b> <br>PRAGMA synchronous = NORMAL; </b>(1)<b> <br>PRAGMA synchronous = OFF; </b>(0)</p> <p>Query or change the setting of the "synchronous" flag. The first (query) form will return the setting as an integer. When synchronous is FULL (2), the SQLite database engine will |
︙ | ︙ | |||
407 408 409 410 411 412 413 | </p> <p>In SQLite version 2, the default value is NORMAL. For version 3, the default was changed to FULL. </p> </li> | | | 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 | </p> <p>In SQLite version 2, the default value is NORMAL. For version 3, the default was changed to FULL. </p> </li> <tcl>Subsection temp_store</tcl> <li><p><b>PRAGMA temp_store; <br>PRAGMA temp_store = DEFAULT;</b> (0)<b> <br>PRAGMA temp_store = FILE;</b> (1)<b> <br>PRAGMA temp_store = MEMORY;</b> (2)</p> <p>Query or change the setting of the "<b>temp_store</b>" parameter. When temp_store is DEFAULT (0), the compile-time C preprocessor macro TEMP_STORE is used to determine where temporary tables and indices |
︙ | ︙ | |||
464 465 466 467 468 469 470 | <td align="center"><em>any</em></td> <td align="center">memory</td></tr> </table> </blockquote> </li> <br> | | | 463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 | <td align="center"><em>any</em></td> <td align="center">memory</td></tr> </table> </blockquote> </li> <br> <tcl>Subsection temp_store_directory</tcl> <li><p><b>PRAGMA temp_store_directory; <br>PRAGMA temp_store_directory = 'directory-name';</b></p> <p>Query or change the setting of the "temp_store_directory" - the directory where files used for storing temporary tables and indices are kept. This setting lasts for the duration of the current connection only and resets to its default value for each new connection opened. |
︙ | ︙ | |||
500 501 502 503 504 505 506 | </li> </ul> <tcl>Section {Pragmas to query the database schema} schema</tcl> <ul> | | | | | | | | | < | | | | | 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 529 530 531 532 533 534 535 536 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 563 564 | </li> </ul> <tcl>Section {Pragmas to query the database schema} schema</tcl> <ul> <tcl>Subsection database_list</tcl> <li><p><b>PRAGMA database_list;</b></p> <p>For each open database, invoke the callback function once with information about that database. Arguments include the index and the name the database was attached with. The first row will be for the main database. The second row will be for the database used to store temporary tables.</p></li> <tcl>Subsection foreign_key_list</tcl> <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> <tcl>Subsection freelist_count</tcl> <li><p><b>PRAGMA [database].freelist_count;</b></p> <p>Return the number of unused pages in the database file. Running a <a href="#pragma_incremental_vacuum">"PRAGMA incremental_vaccum(N);"</a> command with a large value of N will shrink the database file by this number of pages. </p></li> <tcl>Subsection index_info</tcl> <li><p><b>PRAGMA index_info(</b><i>index-name</i><b>);</b></p> <p>For each column that the named index references, invoke the callback function once with information about that column, including the column name, and the column number.</p></li> <tcl>Subsection index_list</tcl> <li><p><b>PRAGMA index_list(</b><i>table-name</i><b>);</b></p> <p>For each index on the named table, invoke the callback function once with information about that index. Arguments include the index name and a flag to indicate whether or not the index must be unique.</p></li> <tcl>Subsection table_info</tcl> <li><p><b>PRAGMA table_info(</b><i>table-name</i><b>);</b></p> <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> </ul> <tcl>Section {Pragmas to query/modify version values} version</tcl> <ul> <tcl>Subsection schema_version; Subsection user_version</tcl> <li><p><b>PRAGMA [database.]schema_version; <br>PRAGMA [database.]schema_version = </b><i>integer </i><b>; <br>PRAGMA [database.]user_version; <br>PRAGMA [database.]user_version = </b><i>integer </i><b>;</b> <p> The pragmas schema_version and user_version are used to set or get the value of the schema-version and user-version, respectively. Both the schema-version and the user-version are 32-bit signed integers stored in the database header.</p> |
︙ | ︙ | |||
578 579 580 581 582 583 584 | applications for any purpose.</p> </li> </ul> <tcl>Section {Pragmas to debug the library} debug</tcl> <ul> | | | | | | | 576 577 578 579 580 581 582 583 584 585 586 587 588 589 590 591 592 593 594 595 596 597 598 599 600 601 602 603 604 605 606 607 608 609 610 611 612 613 614 615 616 617 618 619 620 621 622 623 | applications for any purpose.</p> </li> </ul> <tcl>Section {Pragmas to debug the library} debug</tcl> <ul> <tcl>Subsection integrity_check</tcl> <li><p><b>PRAGMA integrity_check; <br>PRAGMA integrity_check(</b><i>integer</i><b>)</b></p> <p>The command does an integrity check of the entire database. It looks for out-of-order records, missing pages, malformed records, and corrupt indices. If any problems are found, then strings are returned (as multiple rows with a single column per row) which describe the problems. At most <i>integer</i> errors will be reported before the analysis quits. The default value for <i>integer</i> is 100. If no errors are found, a single row with the value "ok" is returned.</p></li> <tcl>Subsection parser_trace</tcl> <li><p><b>PRAGMA parser_trace = ON; </b>(1)<b> <br>PRAGMA parser_trace = OFF;</b> (0)</p> <p>Turn tracing of the SQL parser inside of the SQLite library on and off. This is used for debugging. This only works if the library is compiled without the NDEBUG macro. </p></li> <tcl>Subsection vdbe_trace</tcl> <li><p><b>PRAGMA vdbe_trace = ON; </b>(1)<b> <br>PRAGMA vdbe_trace = OFF;</b> (0)</p> <p>Turn tracing of the virtual database engine inside of the SQLite library on and off. This is used for debugging. See the <a href="vdbe.html#trace">VDBE documentation</a> for more information.</p></li> <tcl>Subsection vdbe_listing</tcl> <li><p><b>PRAGMA vdbe_listing = ON; </b>(1)<b> <br>PRAGMA vdbe_listing = OFF;</b> (0)</p> <p>Turn listings of virtual machine programs on and off. With listing is on, the entire content of a program is printed just prior to beginning execution. This is like automatically executing an [EXPLAIN] prior to each statement. The statement executes normally after the listing is printed. This is used for debugging. See the <a href="vdbe.html#trace">VDBE documentation</a> for more information.</p></li> </ul> |
Changes to wrap.tcl.
︙ | ︙ | |||
54 55 56 57 58 59 60 61 62 63 64 65 66 67 | proc hd_resolve_one {x} { set x2 [split $x |] set kw [string trim [lindex $x2 0]] if {[llength $x2]==1} { set content $kw regsub -all {[^a-zA-Z0-9_.# -]} $content {} kw } else { set content [string trim [lindex $x2 1]] } global hd llink glink if {$hd(enable-main)} { set fn $hd(fn-main) if {[regexp {^[Tt]icket #(\d+)$} $kw all tktid]} { set url http://www.sqlite.org/cvstrac/tktview?tn=$tktid | > | 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 | proc hd_resolve_one {x} { set x2 [split $x |] set kw [string trim [lindex $x2 0]] if {[llength $x2]==1} { set content $kw regsub -all {[^a-zA-Z0-9_.# -]} $content {} kw } else { regsub -all {[^a-zA-Z0-9_.# -]} $kw {} kw set content [string trim [lindex $x2 1]] } global hd llink glink if {$hd(enable-main)} { set fn $hd(fn-main) if {[regexp {^[Tt]icket #(\d+)$} $kw all tktid]} { set url http://www.sqlite.org/cvstrac/tktview?tn=$tktid |
︙ | ︙ | |||
115 116 117 118 119 120 121 122 123 124 125 126 127 128 | set lurl "#$hd(fragment)" } set fn $hd(fn-main) if {[info exists hd(aux)]} { set gurl $hd(fn-aux) } else { set gurl {} } foreach a $args { if {[info exists glink($a)]} { puts stderr "WARNING: duplicate keyword \"$a\"" } if {$gurl==""} { set glink($a) $lurl | > > > | 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 | set lurl "#$hd(fragment)" } set fn $hd(fn-main) if {[info exists hd(aux)]} { set gurl $hd(fn-aux) } else { set gurl {} if {$hd(fragment)!=""} { set lurl $hd(fn-main)#$hd(fragment) } } foreach a $args { if {[info exists glink($a)]} { puts stderr "WARNING: duplicate keyword \"$a\"" } if {$gurl==""} { set glink($a) $lurl |
︙ | ︙ | |||
136 137 138 139 140 141 142 | # Start a new fragment in the main file. Give the new fragment the # indicated name. Any keywords defined after this point will refer # to the fragment, not to the beginning of the file. # # Only the main file may have fragments. Auxiliary files are assumed # to be small enough that fragments are not helpful. # | | > | 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 | # Start a new fragment in the main file. Give the new fragment the # indicated name. Any keywords defined after this point will refer # to the fragment, not to the beginning of the file. # # Only the main file may have fragments. Auxiliary files are assumed # to be small enough that fragments are not helpful. # proc hd_fragment {name args} { global hd set hd(fragment) $name puts $hd(main) "<a name=\"$name\"></a>" eval hd_keywords $args } # Write raw output to both the main file and the auxiliary. Only write # to files that are enabled. # proc hd_puts {text} { global hd |
︙ | ︙ | |||
435 436 437 438 439 440 441 | hd_header $title $infile regsub -all {<tcl>} $in "\175; eval \173" in regsub -all {</tcl>} $in "\175; hd_resolve \173" in eval "hd_resolve \173$in\175" cd $::HOMEDIR hd_close_main } | > > > > > > > > > > > > > > > > > > | 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 | hd_header $title $infile regsub -all {<tcl>} $in "\175; eval \173" in regsub -all {</tcl>} $in "\175; hd_resolve \173" in eval "hd_resolve \173$in\175" cd $::HOMEDIR hd_close_main } # 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>" } hd_puts "</ul>" hd_close_main |