Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Add documentation about the readfile() and writefile() extension functions in the command-line shell. Additional command-line shell documentation updates. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
d4097ee3bf36d2267e32468e2b3b77fc |
User & Date: | drh 2014-07-24 13:50:15.221 |
Context
2014-07-24
| ||
14:27 | Add documentation on the hexadecimal integer notation. (check-in: 4656303de6 user: drh tags: trunk) | |
13:50 | Add documentation about the readfile() and writefile() extension functions in the command-line shell. Additional command-line shell documentation updates. (check-in: d4097ee3bf user: drh tags: trunk) | |
2014-07-22
| ||
12:24 | Enhance the file format description to allow unrecognized text tokens at the end of the stat column in sqlite_stat1. (check-in: 3e1aad19c5 user: drh tags: trunk) | |
Changes
Changes to pages/cli.in.
︙ | ︙ | |||
146 147 148 149 150 151 152 153 154 155 156 157 158 159 | If TABLE specified, only dump tables matching LIKE pattern TABLE. .echo on|off Turn command echo on or off .eqp on|off Enable or disable automatic EXPLAIN QUERY PLAN .exit Exit this program .explain ?on|off? Turn output mode suitable for EXPLAIN on or off. With no args, it turns EXPLAIN on. .headers on|off Turn display of headers on or off .help Show this message .import FILE TABLE Import data from FILE into TABLE .indices ?TABLE? Show names of all indices If TABLE specified, only show indices for tables matching LIKE pattern TABLE. .load FILE ?ENTRY? Load an extension library | > | 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 | If TABLE specified, only dump tables matching LIKE pattern TABLE. .echo on|off Turn command echo on or off .eqp on|off Enable or disable automatic EXPLAIN QUERY PLAN .exit Exit this program .explain ?on|off? Turn output mode suitable for EXPLAIN on or off. With no args, it turns EXPLAIN on. .fullschema Show schema and the content of sqlite_stat tables .headers on|off Turn display of headers on or off .help Show this message .import FILE TABLE Import data from FILE into TABLE .indices ?TABLE? Show names of all indices If TABLE specified, only show indices for tables matching LIKE pattern TABLE. .load FILE ?ENTRY? Load an extension library |
︙ | ︙ | |||
176 177 178 179 180 181 182 | .quit Exit this program .read FILENAME Execute SQL in FILENAME .restore ?DB? FILE Restore content of DB (default "main") from FILE .save FILE Write in-memory database into FILE .schema ?TABLE? Show the CREATE statements If TABLE specified, only show tables matching LIKE pattern TABLE. | | > | 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 | .quit Exit this program .read FILENAME Execute SQL in FILENAME .restore ?DB? FILE Restore content of DB (default "main") from FILE .save FILE Write in-memory database into FILE .schema ?TABLE? Show the CREATE statements If TABLE specified, only show tables matching LIKE pattern TABLE. .separator STRING ?NL? Change separator used by output mode and .import NL is the end-of-line mark for CSV .shell CMD ARGS... Run CMD ARGS... in a system shell .show Show the current values for various settings .stats on|off Turn stats on or off .system CMD ARGS... Run CMD ARGS... in a system shell .tables ?TABLE? List names of tables If TABLE specified, only list tables matching LIKE pattern TABLE. |
︙ | ︙ | |||
280 281 282 283 284 285 286 | one two ---------- ---------- hello 10 goodbye 20 sqlite> }</tcl> | | > | 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 | one two ---------- ---------- hello 10 goodbye 20 sqlite> }</tcl> <p>By default, each column is between 1 and 10 characters wide, depending on the column header name and the width of the first column of data. Data that is too wide to fit in a column is truncated. You can adjust the column widths using the ".width" command. Like this:</p> <tcl>DisplayCode { sqlite> (((.width 12 6))) sqlite> (((select * from tbl1;))) one two |
︙ | ︙ | |||
334 335 336 337 338 339 340 | <p>When specifying insert mode, you have to give an extra argument which is the name of the table to be inserted into. For example:</p> <tcl>DisplayCode { sqlite> (((.mode insert new_table))) sqlite> (((select * from tbl1;))) | | | > | | 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 | <p>When specifying insert mode, you have to give an extra argument which is the name of the table to be inserted into. For example:</p> <tcl>DisplayCode { sqlite> (((.mode insert new_table))) sqlite> (((select * from tbl1;))) INSERT INTO "new_table" VALUES('hello',10); INSERT INTO "new_table" VALUES('goodbye',20); sqlite> }</tcl> <p>The last output mode is "html". In this mode, sqlite3 writes the results of the query as an XHTML table. The beginning <TABLE> and the ending </TABLE> are not written, but all of the intervening <TR>s, <TH>s, and <TD>s are. The html output mode is envisioned as being useful for CGI.</p> <tcl>hd_fragment explain {explain dot-command}</tcl> <p>The ".explain" dot command can be used to set the output mode to "column" and to set the column widths to values that are reasonable for looking at the output of an [EXPLAIN] command. The EXPLAIN command is an SQLite-specific SQL extension that is useful for debugging. If any regular SQL is prefaced by EXPLAIN, then the SQL command is parsed and analyzed but is not executed. Instead, the sequence of virtual machine instructions that would have been used to execute the SQL command are returned like a query result. For example:</p> <tcl>DisplayCode { |
︙ | ︙ | |||
387 388 389 390 391 392 393 | 19 VerifyCookie 0 1 0 00 20 TableLock 0 2 1 tbl1 00 iDb=0 root=2 write=1 21 Integer 20 3 0 00 r[3]=20 22 Goto 0 2 0 00 }</tcl> <p>Notice how the shell changes the indentation of some opcodes to | | | 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 | 19 VerifyCookie 0 1 0 00 20 TableLock 0 2 1 tbl1 00 iDb=0 root=2 write=1 21 Integer 20 3 0 00 r[3]=20 22 Goto 0 2 0 00 }</tcl> <p>Notice how the shell changes the indentation of some opcodes to help show the loop structure of the [VDBE] program. <h3>Writing results to a file</h3> <p>By default, sqlite3 sends query results to standard output. You can change this using the ".output" and ".once" commands. Just put the name of an output file as an argument to .output and all subsequent query results will be written to that file. Or use the .once command |
︙ | ︙ | |||
423 424 425 426 427 428 429 430 431 432 433 434 435 436 | it reads from standard input. So to see the results of a query in a text editor, one could type:</p> <tcl>DisplayCode { sqlite3> (((.once '|open -f'))) sqlite3> (((SELECT * FROM bigTable;))) }</tcl> <h3>Querying the database schema</h3> <p>The sqlite3 program provides several convenience commands that are useful for looking at the schema of the database. There is nothing that these commands do that cannot be done by some other means. These commands are provided purely as a shortcut.</p> | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 427 428 429 430 431 432 433 434 435 436 437 438 439 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 465 466 467 468 469 470 471 472 | it reads from standard input. So to see the results of a query in a text editor, one could type:</p> <tcl>DisplayCode { sqlite3> (((.once '|open -f'))) sqlite3> (((SELECT * FROM bigTable;))) }</tcl> <tcl>hd_fragment fileio {file I/O functions}</tcl> <h4>File I/O Functions</h4> <p>The command-line shell adds two [application-defined SQL functions] that facilitate read content from a file into an table column, and writing the content of a column into a file, respectively. <p>The readfile(X) SQL function reads the entire content of the file named X and returns that content as a BLOB. This can be used to load content into a table. For example: <tcl>DisplayCode { sqlite> (((CREATE TABLE images(name TEXT, type TEXT, img BLOB);))) sqlite> (((INSERT INTO images(name,type,img)))) ...> ((( VALUES('icon','jpeg',readfile('icon.jpg'));))) }</tcl> <p>The writefile(X,Y) SQL function write the blob Y into the file named X and returns the number of bytes written. Use this function to extract the content of a single table column into a file. For example: <tcl>DisplayCode { sqlite> (((SELECT writefile('icon.jpg',img) FROM images WHERE name='icon';))) }</tcl> <p>Note that the readfile(X) and writefile(X,Y) functions are extension functions and are not built into the core SQLite library. These routines are available as a [loadable extension] in the [http://www.sqlite.org/src/artifact?ci=trunk&filename=ext/misc/fileio.c|ext/misc/fileio.c] source file in the [SQLite source code repositories]. <h3>Querying the database schema</h3> <p>The sqlite3 program provides several convenience commands that are useful for looking at the schema of the database. There is nothing that these commands do that cannot be done by some other means. These commands are provided purely as a shortcut.</p> |
︙ | ︙ |
Changes to pages/download.in.
︙ | ︙ | |||
373 374 375 376 377 378 379 380 381 382 383 384 385 386 | filename encoding is 3XXYY00. For branch version 3.X.Y.Z, the encoding is 3XXYYZZ. <p>The <i>date</i> in template (4) is of the form: YYYYMMDDHHMM <a name="cvs"></a> <a name="fossil"></a> <h3>Source Code Repositories</h3> <p> The SQLite source code is maintained in three geographically-dispersed self-synchronizing [http://www.fossil-scm.org/ | Fossil] repositories that are available for anonymous read-only access. Anyone can | > | 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 | filename encoding is 3XXYY00. For branch version 3.X.Y.Z, the encoding is 3XXYYZZ. <p>The <i>date</i> in template (4) is of the form: YYYYMMDDHHMM <a name="cvs"></a> <a name="fossil"></a> <tcl>hd_fragment srctree {SQLite source code repositories} {code repositories}</tcl> <h3>Source Code Repositories</h3> <p> The SQLite source code is maintained in three geographically-dispersed self-synchronizing [http://www.fossil-scm.org/ | Fossil] repositories that are available for anonymous read-only access. Anyone can |
︙ | ︙ |
Changes to pages/lang.in.
︙ | ︙ | |||
740 741 742 743 744 745 746 | <p>^The CREATE INDEX command consists of the keywords "CREATE INDEX" followed by the name of the new index, the keyword "ON", the name of a previously created table that is to be indexed, and a parenthesized list of names of columns in the table that are used for the index key. If the optional WHERE clause is included, then the index is a "[partial index]". </p> | | | 740 741 742 743 744 745 746 747 748 749 750 751 752 753 754 | <p>^The CREATE INDEX command consists of the keywords "CREATE INDEX" followed by the name of the new index, the keyword "ON", the name of a previously created table that is to be indexed, and a parenthesized list of names of columns in the table that are used for the index key. If the optional WHERE clause is included, then the index is a "[partial index]". </p> <tcl>hd_fragment {descidx} {descending indices} {descending index} {descending indexes}</tcl> <p>^Each column name can be followed by one of the "ASC" or "DESC" keywords to indicate sort order. ^The sort order may or may not be ignored depending on the database file format, and in particular the [schema format number]. ^The "legacy" schema format (1) ignores index sort order. ^The descending index schema format (4) takes index sort order into account. Only versions of SQLite 3.3.0 and later are able to understand the descending index format. For compatibility, version of SQLite between 3.3.0 |
︙ | ︙ |
Changes to pages/opcode.in.
1 | <title>SQLite Virtual Machine Opcodes</title> | | > | | 1 2 3 4 5 6 7 8 9 10 11 12 | <title>SQLite Virtual Machine Opcodes</title> <tcl>hd_keywords {virtual machine instructions} {VDBE} {virtual machine} \ {opcodes}</tcl> <h2>The SQLite Virtual Machine</h2> <tcl> set uuid {} catch { exec fossil sha1sum $::SRC/src/vdbe.c } uuid set uuid [lindex $uuid 0] |
︙ | ︙ | |||
76 77 78 79 80 81 82 | </tcl> <h3>Introduction</h3> <p>In order to execute an SQL statement, the SQLite library first parses the SQL, analyzes the statement, then generates a short program to execute the statement. The program is generated for a "virtual machine" implemented | > > | < | > | 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 | </tcl> <h3>Introduction</h3> <p>In order to execute an SQL statement, the SQLite library first parses the SQL, analyzes the statement, then generates a short program to execute the statement. The program is generated for a "virtual machine" implemented by the SQLite library. That virtual machine is sometimes called the "VDBE" or "Virtual DataBase Engine". This document describes the operation of the VDBE.</p> <p>This document is intended as a reference, not a tutorial. A separate <a href="vdbe.html">Virtual Machine Tutorial</a> is available. If you are looking for a narrative description of how the virtual machine works, you should read the tutorial and not this document. Once you have a basic idea of what the virtual machine does, you can refer back to this document for the details on a particular opcode. Unfortunately, the virtual machine tutorial was written for SQLite version 1.0. There are substantial changes in the virtual machine for version 2.0 and again for version 3.0.0 and again for version 3.5.5 and the tutorial document has not been updated. But the basic concepts behind the virtual machine still apply. </p> <p>The source code to the virtual machine is in the [http://www.sqlite.org/src/finfo?name=src/vdbe.c | vdbe.c] source file. All of the opcode definitions further down in this document are contained in comments in the source file. In fact, the opcode table |
︙ | ︙ | |||
119 120 121 122 123 124 125 | pointer to the implementation of an application-defined SQL function, or various other things. P5 is an unsigned character normally used as a flag. Some operators use all five operands. Some use one or two. Some operators use none of the operands.<p> <p>The virtual machine begins execution on instruction number 0. | | | 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 | pointer to the implementation of an application-defined SQL function, or various other things. P5 is an unsigned character normally used as a flag. Some operators use all five operands. Some use one or two. Some operators use none of the operands.<p> <p>The virtual machine begins execution on instruction number 0. Execution continues until a Halt instruction is seen, or until the program counter becomes one greater than the address of last instruction, or there is an execution error. When the virtual machine halts, all memory that it allocated is released and all database cursors it may have had open are closed. If the execution stopped due to an error, any pending transactions are terminated and changes made to the database are rolled back.</p> |
︙ | ︙ | |||
144 145 146 147 148 149 150 | read data from a cursor (Column), advance the cursor to the next entry in the table (Next) or index (NextIdx), and many other operations. All cursors are automatically closed when the virtual machine terminates.</p> <p>The virtual machine contains an arbitrary number of registers | | | > > | | < < < < | | 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 | read data from a cursor (Column), advance the cursor to the next entry in the table (Next) or index (NextIdx), and many other operations. All cursors are automatically closed when the virtual machine terminates.</p> <p>The virtual machine contains an arbitrary number of registers with addresses beginning at one and growing upward. Each register can a single SQL value (a string, a BLOB, a signed 64-bit integer, a 64-bit floating point number, or a NULL). A register might also hold objects used internally by SQLite, such as a RowSet or Frame. </p> <h3>Viewing Programs Generated By SQLite</h3> <p>Every SQL statement that SQLite interprets results in a program for the virtual machine. But if you precede the SQL statement with the keyword [EXPLAIN] the virtual machine will not execute the program. Instead, the instructions of the program will be returned like a query result. This feature is useful for debugging and for learning how the virtual machine operates. For example: </p> <tcl> proc Code {body} { hd_puts {<blockquote><pre>} regsub -all {&} [string trim $body] {\&} body regsub -all {>} $body {\>} body regsub -all {<} $body {\<} body |
︙ | ︙ | |||
213 214 215 216 217 218 219 | 25 TableLock 0 2 1 tbl1 00 iDb=0 root=2 write=1 26 Integer 20 3 0 00 r[3]=20 27 Goto 0 2 0 00 } </tcl> <p>All you have to do is add the [EXPLAIN] keyword to the front of the | | > | | | < > > > | < | | | < > > > | < | | < < | 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 | 25 TableLock 0 2 1 tbl1 00 iDb=0 root=2 write=1 26 Integer 20 3 0 00 r[3]=20 27 Goto 0 2 0 00 } </tcl> <p>All you have to do is add the [EXPLAIN] keyword to the front of the SQL statement. But if you use the "[explain dot-command|.explain]" command in the [CLI], it will set up the output mode to make the VDBE code easier for humans to read.</p> <p>When SQLite is compiled with the [SQLITE_DEBUG] compile-time option, extra [PRAGMA] commands are available that are useful for debugging and for exploring the operation of the VDBE. For example the [vdbe_trace] pragma can be enabled to cause a disassembly of each VDBE opcode to be printed on standard output as the opcode is executed. These debugging pragmas include: <ul> <li> [PRAGMA parser_trace] <li> [PRAGMA vdbe_addoptrace] <li> [PRAGMA vdbe_debug] <li> [PRAGMA vdbe_listing] <li> [PRAGMA vdbe_trace] </ul> </p> <h3>The Opcodes</h3> <p>There are currently <tcl>hd_puts [llength $OpcodeList]</tcl> opcodes defined by the virtual machine. All currently defined opcodes are described in the table below. This table was generated automatically by scanning the source code |
︙ | ︙ |
Changes to pages/vdbe.in.
1 | <title>The Virtual Database Engine of SQLite</title> | < < | > | | < > | > | > | | | | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | <title>The Virtual Database Engine of SQLite</title> <h2>The Virtual Database Engine of SQLite</h2> <blockquote><font color="red"> <b>Obsolete Documentation Warning:</b> This document describes the virtual machine used in SQLite version 2.8.0. The virtual machine in SQLite version 3.0 and 3.1 is similar in concept but is now register-based instead of stack-based, has five operands per opcode instead of three, and has a different set of opcodes from those shown below. See the [virtual machine instructions] document for the current set of VDBE opcodes and a brief overview of how the VDBE operates. This document is retained as an historical reference. </font></blockquote> <tcl> hd_puts { <p>If you want to know how the SQLite library works internally, you need to begin with a solid understanding of the Virtual Database Engine or VDBE. The VDBE occurs right in the middle of the processing stream (see the <a href="arch.html">architecture diagram</a>) and so it seems to touch most parts of the library. Even parts of the code that do not directly interact with the VDBE |
︙ | ︙ |