Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Documentation updates. (CVS 1946) |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
799f5383c0c72a74c7b29f9f40fc949f |
User & Date: | drh 2004-09-08 13:06:21.000 |
Context
2004-09-08
| ||
13:07 | Add new APIs to sqlite3.def: sqlite3_bind_parameter_name and sqlite3_bind_parameter_index. (CVS 1947) (check-in: ff256fb528 user: drh tags: trunk) | |
13:06 | Documentation updates. (CVS 1946) (check-in: 799f5383c0 user: drh tags: trunk) | |
2004-09-07
| ||
16:19 | Wildcards with the same name map into the same variable number. New api sqlite3_bind_parameter_index() added to map wildcard names into wildcard index numbers. Support for "?nnn" wildcards. (CVS 1945) (check-in: 435b3f301f user: drh tags: trunk) | |
Changes
Changes to www/arch.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: arch.tcl,v 1.15 2004/09/08 13:06:21 drh Exp $} source common.tcl header {Architecture of SQLite} puts { <h2>The Architecture Of SQLite</h2> <h3>Introduction</h3> |
︙ | ︙ | |||
20 21 22 23 24 25 26 | <p> A block diagram showing the main components of SQLite and how they interrelate is shown at the right. The text that follows will provide a quick overview of each of these components. </p> | < < < < < < < < < < < < < < | < < | < < < < | > > | < | > | | | < < < < | < | | 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 | <p> A block diagram showing the main components of SQLite and how they interrelate is shown at the right. The text that follows will provide a quick overview of each of these components. </p> <p> This document describes SQLite version 3.0. Version 2.8 and earlier are similar but the details differ. </p> <h3>Interface</h3> <p>Much of the public interface to the SQLite library is implemented by functions found in the <b>main.c</b>, <b>legacy.c</b>, and <b>vdbeapi.c</b> source files though some routines are scattered about in other files where they can have access to data structures with file scope. The <b>sqlite3_get_table()</b> routine is implemented in <b>table.c</b>. <b>sqlite3_mprintf()</b> is found in <b>printf.c</b>. <b>sqlite3_complete()</b> is in <b>tokenize.c</b>. The Tcl interface is implemented by <b>tclsqlite.c</b>. More information on the C interface to SQLite is <a href="capi3ref.html">available separately</a>.<p> <p>To avoid name collisions with other software, all external symbols in the SQLite library begin with the prefix <b>sqlite3</b>. Those symbols that are intended for external use (in other words, those symbols which form the API for SQLite) begin with <b>sqlite3_</b>.</p> <h3>Tokenizer</h3> <p>When a string containing SQL statements is to be executed, the interface passes that string to the tokenizer. The job of the tokenizer is to break the original string up into tokens and pass those tokens one by one to the parser. The tokenizer is hand-coded in C in the file <b>tokenize.c</b>. <p>Note that in this design, the tokenizer calls the parser. People who are familiar with YACC and BISON may be used to doing things the other way around -- having the parser call the tokenizer. The author of SQLite has done it both ways and finds things generally work out nicer for the tokenizer to call the parser. YACC has it backwards.</p> |
︙ | ︙ | |||
109 110 111 112 113 114 115 | </p> <h3>Code Generator</h3> <p>After the parser assembles tokens into complete SQL statements, it calls the code generator to produce virtual machine code that will do the work that the SQL statements request. There are many | | > > > | > > | > > > | | > > | > > > > > > > > > > > > > | > | > > | | > > > > > > | > | > > > > > > > > > > | > | > > > > > > | > | | > | 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 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 | </p> <h3>Code Generator</h3> <p>After the parser assembles tokens into complete SQL statements, it calls the code generator to produce virtual machine code that will do the work that the SQL statements request. There are many files in the code generator: <b>attach.c</b>, <b>auth.c</b>, <b>build.c</b>, <b>delete.c</b>, <b>expr.c</b>, <b>insert.c</b>, <b>pragma.c</b>, <b>select.c</b>, <b>trigger.c</b>, <b>update.c</b>, <b>vacuum.c</b> and <b>where.c</b>. In these files is where most of the serious magic happens. <b>expr.c</b> handles code generation for expressions. <b>where.c</b> handles code generation for WHERE clauses on SELECT, UPDATE and DELETE statements. The files <b>attach.c</b>, <b>delete.c</b>, <b>insert.c</b>, <b>select.c</b>, <b>trigger.c</b> <b>update.c</b>, and <b>vacuum.c</b> handle the code generation for SQL statements with the same names. (Each of these files calls routines in <b>expr.c</b> and <b>where.c</b> as necessary.) All other SQL statements are coded out of <b>build.c</b>. The <b>auth.c</b> file implements the functionality of <b>sqlite3_set_authorizer()</b>.</p> <h3>Virtual Machine</h3> <p>The program generated by the code generator is executed by the virtual machine. Additional information about the virtual machine is <a href="opcode.html">available separately</a>. To summarize, the virtual machine implements an abstract computing engine specifically designed to manipulate database files. The machine has a stack which is used for intermediate storage. Each instruction contains an opcode and up to three additional operands.</p> <p>The virtual machine itself is entirely contained in a single source file <b>vdbe.c</b>. The virtual machine also has its own header files: <b>vdbe.h</b> that defines an interface between the virtual machine and the rest of the SQLite library and <b>vdbeInt.h</b> which defines structure private the virtual machine. The <b>vdbeaux.c</b> file contains utilities used by the virtual machine and interface modules used by the rest of the library to construct VM programs. The <b>vdbeapi.c</b> file contains external interfaces to the virtual machine such as the <b>sqlite3_bind_...</b> family of functions. Individual values (strings, integer, floating point numbers, and BLOBs) are stored in an internal object named "Mem" which is implemented by <b>vdbemem.c</b>.</p> <p> SQLite implements SQL functions using callbacks to C-language routines. Even the built-in SQL functions are implemented this way. Most of the built-in SQL functions (ex: <b>coalesce()</b>, <b>count()</b>, <b>substr()</b>, and so forth) can be found in <b>func.c</b>. Date and time conversion functions are found in <b>date.c</b>. </p> <h3>B-Tree</h3> <p>An SQLite database is maintained on disk using a B-tree implementation found in the <b>btree.c</b> source file. A separate B-tree is used for each table and index in the database. All B-trees are stored in the same disk file. Details of the file format are recorded in a large comment at the beginning of <b>btree.c</b>.</p> <p>The interface to the B-tree subsystem is defined by the header file <b>btree.h</b>. </p> <h3>Page Cache</h3> <p>The B-tree module requests information from the disk in fixed-size chunks. The default chunk size is 1024 bytes but can vary between 512 and 65536 bytes. The page cache is reponsible for reading, writing, and caching these chunks. The page cache also provides the rollback and atomic commit abstraction and takes care of locking of the database file. The B-tree driver requests particular pages from the page cache and notifies the page cache when it wants to modify pages or commit or rollback changes and the page cache handles all the messy details of making sure the requests are handled quickly, safely, and efficiently.</p> <p>The code to implement the page cache is contained in the single C source file <b>pager.c</b>. The interface to the page cache subsystem is defined by the header file <b>pager.h</b>. </p> <h3>OS Interface</h3> <p> In order to provide portability between POSIX and Win32 operating systems, SQLite uses an abstraction layer to interface with the operating system. The interface to the OS abstraction layer is defined in <b>os.h</b>. Each supported operating system has its own implementation: <b>os_unix.c</b> for Unix, <b>os_win.c</b> for windows, and so forth. Each of these operating-specific implements typically has its own header file: <b>os_unix.h</b>, <b>os_win.h</b>, etc. </p> <h3>Utilities</h3> <p> Memory allocation and caseless string comparison routines are located in <b>util.c</b>. Symbol tables used by the parser are maintained by hash tables found in <b>hash.c</b>. The <b>utf.c</b> source file contains Unicode conversion subroutines. SQLite has its own private implementation of <b>printf()</b> (with some extensions) in <b>printf.c</b> and its own random number generator in <b>random.c</b>. </p> <h3>Test Code</h3> <p> If you count regression test scripts, more than half the total code base of SQLite is devoted to testing. There are many <b>assert()</b> statements in the main code files. In additional, the source files <b>test1.c</b> through <b>test5.c</b> together with <b>md5.c</b> implement extensions used for testing purposes only. The <b>os_test.c</b> backend interface is used to simulate power failures to verify the crash-recovery mechanism in the pager. </p> } footer $rcsid |
Changes to www/arch2.gif.
cannot compute difference between binary files
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.72 2004/09/08 13:06:21 drh Exp $} source common.tcl header {Query Language Understood by SQLite} puts { <h2>SQL As Understood By SQLite</h2> <p>The SQLite library understands most of the standard SQL language. But it does <a href="omitted.html">omit some features</a> |
︙ | ︙ | |||
179 180 181 182 183 184 185 | } Syntax {sql-statement} { ROLLBACK [TRANSACTION [<name>]] } puts { <p>Beginning in version 2.0, SQLite supports transactions with | | < | 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 | } Syntax {sql-statement} { ROLLBACK [TRANSACTION [<name>]] } puts { <p>Beginning in version 2.0, SQLite supports transactions with rollback and atomic commit.</p> <p>The optional transaction name is ignored. SQLite currently does not allow nested transactions.</p> <p> No changes can be made to the database except within a transaction. Any command that changes the database (basically, any SQL command |
︙ | ︙ | |||
257 258 259 260 261 262 263 264 265 266 267 268 269 270 | [ USING DELIMITERS <delim> ] } puts { <p>The COPY command is available in SQLite version 2.8 and earlier. The COPY command has been removed from SQLite version 3.0 due to complications in trying to support it in a mixed UTF-8/16 environment. </p> <p>The COPY command is an extension used to load large amounts of data into a table. It is modeled after a similar command found in PostgreSQL. In fact, the SQLite COPY command is specifically designed to be able to read the output of the PostgreSQL dump utility <b>pg_dump</b> so that data can be easily transferred from | > > > | 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 | [ USING DELIMITERS <delim> ] } puts { <p>The COPY command is available in SQLite version 2.8 and earlier. The COPY command has been removed from SQLite version 3.0 due to complications in trying to support it in a mixed UTF-8/16 environment. In version 3.0, the <a href="sqlite.html">command-line shell</a> contains a new command <b>.import</b> that can be used as a substitute for COPY. </p> <p>The COPY command is an extension used to load large amounts of data into a table. It is modeled after a similar command found in PostgreSQL. In fact, the SQLite COPY command is specifically designed to be able to read the output of the PostgreSQL dump utility <b>pg_dump</b> so that data can be easily transferred from |
︙ | ︙ | |||
385 386 387 388 389 390 391 | followed by the name of a new table and a parenthesized list of column definitions and constraints. The table name can be either an identifier or a string. Tables names that begin with "<b>sqlite_</b>" are reserved for use by the engine.</p> <p>Each column definition is the name of the column followed by the datatype for that column, then one or more optional column constraints. | < > | | 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 | followed by the name of a new table and a parenthesized list of column definitions and constraints. The table name can be either an identifier or a string. Tables names that begin with "<b>sqlite_</b>" are reserved for use by the engine.</p> <p>Each column definition is the name of the column followed by the datatype for that column, then one or more optional column constraints. The datatype for the column does not restrict what data may be put in that column. See <a href="datatype3.html">Datatypes In SQLite Version 3</a> for additional information. The UNIQUE constraint causes an index to be created on the specified columns. This index must contain unique keys. The DEFAULT constraint specifies a default value to use when doing an INSERT. The COLLATE clause specifies what text collating function to use when comparing text entries for the column. The built-in BINARY collating function is used by default. |
︙ | ︙ | |||
444 445 446 447 448 449 450 | Support for CHECK constraints may be added in the future. As of version 2.3.0, NOT NULL, PRIMARY KEY, and UNIQUE constraints all work.</p> <p>There are no arbitrary limits on the number of columns or on the number of constraints in a table. The total amount of data in a single row is limited to about | | > | | 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 | Support for CHECK constraints may be added in the future. As of version 2.3.0, NOT NULL, PRIMARY KEY, and UNIQUE constraints all work.</p> <p>There are no arbitrary limits on the number of columns or on the number of constraints in a table. The total amount of data in a single row is limited to about 1 megabytes in version 2.8. In version 3.0 there is no arbitrary limit on the amount of data in a row.</p> <p>The CREATE TABLE AS form defines the table to be the result set of a query. The names of the table columns are the names of the columns in the result.</p> <p>The exact text of each CREATE TABLE statement is stored in the <b>sqlite_master</b> |
︙ | ︙ | |||
700 701 702 703 704 705 706 | Section {DROP INDEX} dropindex Syntax {sql-command} { DROP INDEX [<database-name> .] <index-name> } puts { | | > | 703 704 705 706 707 708 709 710 711 712 713 714 715 716 717 718 | Section {DROP INDEX} dropindex Syntax {sql-command} { DROP INDEX [<database-name> .] <index-name> } 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 |
︙ | ︙ | |||
917 918 919 920 921 922 923 | function can be used in any expression. Simple functions return a result immediately based on their inputs. Aggregate functions may only be used in a SELECT statement. Aggregate functions compute their result across all rows of the result set.</p> <p>The functions shown below are available by default. Additional functions may be written in C and added to the database engine using | | | | | 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 956 | function can be used in any expression. Simple functions return a result immediately based on their inputs. Aggregate functions may only be used in a SELECT statement. Aggregate functions compute their result across all rows of the result set.</p> <p>The functions shown below are available by default. Additional functions may be written in C and added to the database engine using the <a href="capi3ref.html#cfunc">sqlite3_create_function()</a> API.</p> <table border=0 cellpadding=10> <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> <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>X GLOB Y</b>" syntax of SQLite. The <a href="capi3ref.html#sqlite3_create_function">sqlite3_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">ifnull(<i>X</i>,<i>Y</i>)</td> |
︙ | ︙ | |||
969 970 971 972 973 974 975 | 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 | | | | 973 974 975 976 977 978 979 980 981 982 983 984 985 986 987 988 | 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>X LIKE Y</b>" syntax of SQL. The <a href="capi3ref.html#sqlite3_create_function">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> |
︙ | ︙ | |||
1008 1009 1010 1011 1012 1013 1014 1015 1016 1017 1018 1019 1020 1021 | <tr> <td valign="top" align="right">nullif(<i>X</i>,<i>Y</i>)</td> <td valign="top">Return the first argument if the arguments are different, otherwise return NULL.</td> </tr> <tr> <td valign="top" align="right">random(*)</td> <td valign="top">Return a random integer between -2147483648 and +2147483647.</td> </tr> <tr> | > > > > > > > > > > > | 1012 1013 1014 1015 1016 1017 1018 1019 1020 1021 1022 1023 1024 1025 1026 1027 1028 1029 1030 1031 1032 1033 1034 1035 1036 | <tr> <td valign="top" align="right">nullif(<i>X</i>,<i>Y</i>)</td> <td valign="top">Return the first argument if the arguments are different, otherwise return NULL.</td> </tr> <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 random integer between -2147483648 and +2147483647.</td> </tr> <tr> |
︙ | ︙ | |||
1049 1050 1051 1052 1053 1054 1055 | right rather than the left. If SQLite is configured to support UTF-8, then characters indices refer to actual UTF-8 characters, not bytes.</td> </tr> <tr> <td valign="top" align="right">typeof(<i>X</i>)</td> <td valign="top">Return the type of the expression <i>X</i>. The only | > | | > | | 1064 1065 1066 1067 1068 1069 1070 1071 1072 1073 1074 1075 1076 1077 1078 1079 1080 1081 1082 1083 1084 1085 1086 1087 1088 1089 1090 1091 1092 1093 1094 1095 1096 | right rather than the left. If SQLite is configured to support UTF-8, then characters indices refer to actual UTF-8 characters, not bytes.</td> </tr> <tr> <td valign="top" align="right">typeof(<i>X</i>)</td> <td valign="top">Return the type of the expression <i>X</i>. The only return values are "null", "integer", "real", "text", and "blob". SQLite's type handling is explained in <a href="datatype3.html">Datatypes in SQLite Version 3</a>.</td> </tr> <tr> <td valign="top" align="right">upper(<i>X</i>)</td> <td valign="top">Return a copy of input string <i>X</i> converted to all upper-case letters. The implementation of this function uses the C library routine <b>toupper()</b> which means it may not work correctly on UTF-8 strings.</td> </tr> </table> <p> The following aggregate functions are available by default. Additional aggregate functions written in C may be added using the <a href="capi3ref.html#sqlite3_create_function">sqlite3_create_function()</a> API.</p> <table border=0 cellpadding=10> <tr> <td valign="top" align="right" width=120>avg(<i>X</i>)</td> <td valign="top">Return the average value of all <i>X</i> within a group.</td> </tr> |
︙ | ︙ | |||
1266 1267 1268 1269 1270 1271 1272 | the main database. The second row will be for the database used to store temporary tables.</p></li> <a name="pragma_default_cache_size"></a> <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 | | > > | | 1283 1284 1285 1286 1287 1288 1289 1290 1291 1292 1293 1294 1295 1296 1297 1298 1299 1300 | the main database. The second row will be for the database used to store temporary tables.</p></li> <a name="pragma_default_cache_size"></a> <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 <a href="#pragma_cache_size"><b>cache_size</b></a> 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 everytime you reopen the database.</p></li> <a name="pragma_default_synchronous"></a> <li><p><b>PRAGMA default_synchronous; |
︙ | ︙ | |||
1304 1305 1306 1307 1308 1309 1310 | crashes or the computer loses power before that data has been written to the disk surface. On the other hand, some operations are as much as 50 or more times faster with synchronous OFF. </p> <p>This pragma changes the synchronous mode persistently. Once changed, the mode stays as set even if the database is closed and reopened. The <a href="#pragma_synchronous"><b>synchronous</b></a> pragma does the same | | > > | 1323 1324 1325 1326 1327 1328 1329 1330 1331 1332 1333 1334 1335 1336 1337 1338 1339 | crashes or the computer loses power before that data has been written to the disk surface. On the other hand, some operations are as much as 50 or more times faster with synchronous OFF. </p> <p>This pragma changes the synchronous mode persistently. Once changed, the mode stays as set even if the database is closed and reopened. The <a href="#pragma_synchronous"><b>synchronous</b></a> pragma does the same 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 |
︙ | ︙ |