Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | :-) (CVS 87) |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
3661b5ff93b01da7fea9f85370ecdda1 |
User & Date: | drh 2000-06-09 01:58:36.000 |
Context
2000-06-09
| ||
03:47 | :-) (CVS 88) (check-in: 3252269e90 user: drh tags: trunk) | |
01:58 | :-) (CVS 87) (check-in: 3661b5ff93 user: drh tags: trunk) | |
2000-06-08
| ||
21:53 | :-) (CVS 86) (check-in: 049abcb37d user: drh tags: trunk) | |
Changes
Changes to Makefile.in.
︙ | ︙ | |||
183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 | tclsh $(TOP)/www/changes.tcl >changes.html fileformat.html: $(TOP)/www/fileformat.tcl tclsh $(TOP)/www/fileformat.tcl >fileformat.html lang.html: $(TOP)/www/lang.tcl tclsh $(TOP)/www/lang.tcl >lang.html # Files to be published on the website. # PUBLISH = \ sqlite.tar.gz \ index.html \ sqlite.html \ changes.html \ fileformat.html \ lang.html \ c_interface.html website: $(PUBLISH) publish: $(PUBLISH) scp $(PUBLISH) hwaci@oak.he.net:public_html/sw/sqlite | > > > > > > > > > > > > > | 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 | tclsh $(TOP)/www/changes.tcl >changes.html fileformat.html: $(TOP)/www/fileformat.tcl tclsh $(TOP)/www/fileformat.tcl >fileformat.html lang.html: $(TOP)/www/lang.tcl tclsh $(TOP)/www/lang.tcl >lang.html arch.html: $(TOP)/www/arch.tcl tclsh $(TOP)/www/arch.tcl >arch.html arch.png: $(TOP)/www/arch.png cp $(TOP)/www/arch.png . opcode.html: $(TOP)/www/opcode.tcl tclsh $(TOP)/www/opcode.tcl $(TOP)/src/vdbe.c >opcode.html # Files to be published on the website. # PUBLISH = \ sqlite.tar.gz \ index.html \ sqlite.html \ changes.html \ fileformat.html \ lang.html \ opcode.html \ arch.html \ arch.png \ c_interface.html website: $(PUBLISH) publish: $(PUBLISH) scp $(PUBLISH) hwaci@oak.he.net:public_html/sw/sqlite |
︙ | ︙ |
Changes to src/vdbe.c.
︙ | ︙ | |||
37 38 39 40 41 42 43 | ** inplicit conversion from one type to the other occurs as necessary. ** ** Most of the code in this file is taken up by the sqliteVdbeExec() ** function which does the work of interpreting a VDBE program. ** But other routines are also provided to help in building up ** a program instruction by instruction. ** | | | 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 | ** inplicit conversion from one type to the other occurs as necessary. ** ** Most of the code in this file is taken up by the sqliteVdbeExec() ** function which does the work of interpreting a VDBE program. ** But other routines are also provided to help in building up ** a program instruction by instruction. ** ** $Id: vdbe.c,v 1.31 2000/06/09 01:58:37 drh Exp $ */ #include "sqliteInt.h" #include <unistd.h> /* ** SQL is translated into a sequence of instructions to be ** executed by a virtual machine. Each instruction is an instance |
︙ | ︙ | |||
942 943 944 945 946 947 948 | if( z==0 ) z = ""; p->zStack[i] = z; p->aStack[i].n = strlen(z) + 1; p->aStack[i].flags = STK_Str; break; } | | | 942 943 944 945 946 947 948 949 950 951 952 953 954 955 956 | if( z==0 ) z = ""; p->zStack[i] = z; p->aStack[i].n = strlen(z) + 1; p->aStack[i].flags = STK_Str; break; } /* Opcode: Null * * * ** ** Push a NULL value onto the stack. */ case OP_Null: { int i = ++p->tos; if( NeedStack(p, p->tos) ) goto no_mem; p->zStack[i] = 0; |
︙ | ︙ | |||
1889 1890 1891 1892 1893 1894 1895 | } /* Opcode: KeyAsData P1 P2 * ** ** Turn the key-as-data mode for cursor P1 either on (if P2==1) or ** off (if P2==0). In key-as-data mode, the OP_Fetch opcode pulls ** data off of the key rather than the data. This is useful for | | | | > > > > | 1889 1890 1891 1892 1893 1894 1895 1896 1897 1898 1899 1900 1901 1902 1903 1904 1905 1906 1907 1908 1909 1910 1911 1912 1913 1914 1915 1916 1917 1918 1919 1920 1921 1922 1923 1924 1925 | } /* Opcode: KeyAsData P1 P2 * ** ** Turn the key-as-data mode for cursor P1 either on (if P2==1) or ** off (if P2==0). In key-as-data mode, the OP_Fetch opcode pulls ** data off of the key rather than the data. This is useful for ** processing compound selects. */ case OP_KeyAsData: { int i = pOp->p1; if( i>=0 && i<p->nTable && p->aTab[i].pTable!=0 ){ p->aTab[i].keyAsData = pOp->p2; } break; } /* Opcode: Field P1 P2 * ** ** Push onto the stack the value of the P2-th field from the ** most recent Fetch from table P1. ** ** The value pushed is just a pointer to the data in the cursor. ** The value will go away the next time a record is fetched from P1, ** or when P1 is closed. Make a copy of the string (using ** "Concat 1 0 0" if it needs to persist longer than that. ** ** If the KeyAsData opcode has previously executed on this cursor, ** then the field might be extracted from the key rather than the ** data. */ case OP_Field: { int *pAddr; int amt; int i = pOp->p1; int p2 = pOp->p2; int tos = ++p->tos; |
︙ | ︙ | |||
2752 2753 2754 2755 2756 2757 2758 | ** ** Pop the top of the stack and use that as an aggregator key. If ** an aggregator with that same key already exists, then make the ** aggregator the current aggregator and jump to P2. If no aggregator ** with the given key exists, create one and make it current but ** do not jump. ** | > | > > | | 2756 2757 2758 2759 2760 2761 2762 2763 2764 2765 2766 2767 2768 2769 2770 2771 2772 2773 2774 | ** ** Pop the top of the stack and use that as an aggregator key. If ** an aggregator with that same key already exists, then make the ** aggregator the current aggregator and jump to P2. If no aggregator ** with the given key exists, create one and make it current but ** do not jump. ** ** The order of aggregator opcodes is important. The order is: ** AggReset AggFocus AggNext. In other words, you must execute ** AggReset first, then zero or more AggFocus operations, then ** zero or more AggNext operations. You must not execute an AggFocus ** in between an AggNext and an AggReset. */ case OP_AggFocus: { int tos = p->tos; AggElem *pElem; char *zKey; int nKey; |
︙ | ︙ | |||
2848 2849 2850 2851 2852 2853 2854 | PopStack(p, 1); break; } /* Opcode: AggGet * P2 * ** ** Push a new entry onto the stack which is a copy of the P2-th field | | | | 2855 2856 2857 2858 2859 2860 2861 2862 2863 2864 2865 2866 2867 2868 2869 2870 | PopStack(p, 1); break; } /* Opcode: AggGet * P2 * ** ** Push a new entry onto the stack which is a copy of the P2-th field ** of the current aggregate. Strings are not duplicated so ** string values will be ephemeral. */ case OP_AggGet: { AggElem *pFocus = AggInFocus(p->agg); int i = pOp->p2; int tos = ++p->tos; if( NeedStack(p, tos) ) goto no_mem; if( pFocus==0 ) goto no_mem; |
︙ | ︙ | |||
2872 2873 2874 2875 2876 2877 2878 | /* Opcode: AggNext * P2 * ** ** Make the next aggregate value the current aggregate. The prior ** aggregate is deleted. If all aggregate values have been consumed, ** jump to P2. ** | > > > | | | 2879 2880 2881 2882 2883 2884 2885 2886 2887 2888 2889 2890 2891 2892 2893 2894 2895 2896 2897 | /* Opcode: AggNext * P2 * ** ** Make the next aggregate value the current aggregate. The prior ** aggregate is deleted. If all aggregate values have been consumed, ** jump to P2. ** ** The order of aggregator opcodes is important. The order is: ** AggReset AggFocus AggNext. In other words, you must execute ** AggReset first, then zero or more AggFocus operations, then ** zero or more AggNext operations. You must not execute an AggFocus ** in between an AggNext and an AggReset. */ case OP_AggNext: { if( p->agg.nHash ){ p->agg.nHash = 0; sqliteFree(p->agg.apHash); p->agg.apHash = 0; p->agg.pCurrent = p->agg.pFirst; |
︙ | ︙ | |||
2901 2902 2903 2904 2905 2906 2907 | pc = pOp->p2-1; } break; } /* Opcode: SetClear P1 * * ** | | | | 2911 2912 2913 2914 2915 2916 2917 2918 2919 2920 2921 2922 2923 2924 2925 2926 2927 2928 2929 2930 2931 2932 2933 2934 2935 2936 2937 | pc = pOp->p2-1; } break; } /* Opcode: SetClear P1 * * ** ** Remove all elements from the P1-th Set. */ case OP_SetClear: { int i = pOp->p1; if( i>=0 && i<p->nSet ){ SetClear(&p->aSet[i]); } break; } /* Opcode: SetInsert P1 * P3 ** ** If Set P1 does not exist then create it. Then insert value ** P3 into that set. If P3 is NULL, then insert the top of the ** stack into the set. */ case OP_SetInsert: { int i = pOp->p1; if( p->nSet<=i ){ p->aSet = sqliteRealloc(p->aSet, (i+1)*sizeof(p->aSet[0]) ); |
︙ | ︙ |
Added www/arch.fig.
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 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 | #FIG 3.2 Landscape Center Inches Letter 100.00 Single -2 1200 2 2 2 0 1 0 7 100 0 -1 0.000 0 0 -1 0 0 5 2550 2250 4875 2250 4875 3525 2550 3525 2550 2250 2 2 0 1 0 7 100 0 -1 0.000 0 0 -1 0 0 5 2550 4050 4875 4050 4875 5325 2550 5325 2550 4050 2 2 0 1 0 7 100 0 -1 0.000 0 0 -1 0 0 5 2550 5850 4875 5850 4875 7125 2550 7125 2550 5850 2 2 0 1 0 7 100 0 -1 0.000 0 0 -1 0 0 5 2550 7650 4875 7650 4875 8925 2550 8925 2550 7650 2 2 0 1 0 7 100 0 -1 0.000 0 0 -1 0 0 5 2550 450 4875 450 4875 1725 2550 1725 2550 450 2 2 0 1 0 7 100 0 -1 0.000 0 0 -1 0 0 5 2550 9450 4875 9450 4875 10725 2550 10725 2550 9450 2 1 0 3 0 7 100 0 -1 0.000 0 0 -1 1 0 2 1 1 3.00 75.00 135.00 3675 1725 3675 2250 2 1 0 3 0 7 100 0 -1 0.000 0 0 -1 1 0 2 1 1 3.00 75.00 135.00 3675 3525 3675 4050 2 1 0 3 0 7 100 0 -1 0.000 0 0 -1 1 0 2 1 1 3.00 75.00 135.00 3675 5325 3675 5850 2 1 0 3 0 7 100 0 -1 0.000 0 0 -1 1 0 2 1 1 3.00 75.00 135.00 3675 7125 3675 7650 2 1 0 3 0 7 100 0 -1 0.000 0 0 -1 1 0 2 1 1 3.00 75.00 135.00 3675 8925 3675 9450 4 1 0 100 0 0 20 0.0000 4 195 1920 3675 8025 Virtual Machine\001 4 1 0 100 0 0 20 0.0000 4 195 1830 3675 6225 Code Generator\001 4 1 0 100 0 0 20 0.0000 4 195 735 3675 4350 Parser\001 4 1 0 100 0 0 20 0.0000 4 195 1140 3675 2550 Tokenizer\001 4 1 0 100 0 0 20 0.0000 4 195 1020 3675 750 Interface\001 4 1 0 100 0 0 20 0.0000 4 195 990 3675 9825 Backend\001 4 1 0 100 0 0 14 0.0000 4 150 570 3675 10650 dbbe.c\001 4 1 0 100 0 0 14 0.0000 4 150 570 3675 8850 vdbe.c\001 4 1 0 100 0 0 14 0.0000 4 195 2190 3675 7050 select.c update.c where.c\001 4 1 0 100 0 0 14 0.0000 4 195 1860 3675 6825 build.c delete.c expr.c\001 4 1 0 100 0 0 14 0.0000 4 150 630 3675 5250 parse.y\001 4 1 0 100 0 0 14 0.0000 4 150 870 3675 3450 tokenize.c\001 4 1 0 100 0 0 14 0.0000 4 150 570 3675 1575 main.c\001 |
Added www/arch.png.
cannot compute difference between binary files
Added www/arch.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 | # # Run this Tcl script to generate the sqlite.html file. # set rcsid {$Id: arch.tcl,v 1.1 2000/06/09 01:58:51 drh Exp $} puts {<html> <head> <title>Architecture of SQLite</title> </head> <body bgcolor=white> <h1 align=center> The Architecture Of SQLite </h1>} puts "<p align=center> (This page was last modified on [lrange $rcsid 3 4] GMT) </p>" puts { <h2>Introduction</h2> <table align="right" border="1" cellpadding="15" cellspacing="1"> <tr><th>Block Diagram Of SQLite</th></tr> <tr><td><img src="arch.png"></td></tr> </table> <p>This file describes the architecture of the SQLite library. A block diagram showing the main components of SQLite and how that interrelate is shown at the right. The text that follows will provide a quick overview of each of these components. </p> <h2>Interface</h2> <p>The public interface to the SQLite library is implemented by four functions found in the <b>main.c</b> source file. Additional information on the C interface to SQLite is <a href="c_interface.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>sqlite</b>. Those symbols that are intended for external use (as oppose to those which are for internal use only but which have to be exported do to limitations of the C linker's scoping mechanism) begin with <b>sqlite_</b>.</p> <h2>Tokenizer</h2> <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. (There is no "lex" code here.) All of the code for the tokenizer is contained in the <b>tokenize.c</b> source file.</p> <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. This author as done it both ways, and finds things generally work out nicer for the tokenizer to call the parser. YACC has it backwards.</p> <h2>Parser</h2> <p>The parser is the piece that assigns meaning to tokens based on their context. The parser for SQLite is generated using the <a href="http://www.hwaci.com/sw/lemon/">Lemon</a> LALR(1) parser generator. Lemon does the same job as YACC/BISON, but is uses a different input syntax which is less error-prone than the clumsy YACC/BISON syntax. Lemon also generates a parser which is reentrant and thread-safe. And lemon defines the concept of a non-terminal destructor so that it does not leak memory when syntax errors are encountered. The source file that drives Lemon is found in <b>parse.y</b>.</p> <p>Because lemon is a program not normally found on development machines, the complete source code to lemon (just one C file) is included in the SQLite distribution in the "tool" subdirectory. Documentation on lemon is found in the "doc" subdirectory of the distribution. </p> <h2>Code Generator</h2> <p>After the parser assemblies 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 six files in the code generator: <b>build.c</b>, <b>delete.c</b>, <b>expr.c</b>, <b>select.c</b>, <b>update.c</b>, and <b>where.c</b>. In these files is where most of the serious magic happens.</p> <h2>Virtual Machine</h2> <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 as a stack. Each instruction contains an opcode and up to three additional operands.</p> <p>The virtual machine is entirely contained in a single source file <b>vdbe.c</b>. The virtual machine also has its own header file <b>vdbe.h</b> that defines an interface between the virtual machine and the rest of the SQLite library.</p> <h2>Backend</h2> <p>The last layer in the design of SQLite is the backend. The backend implements an interface between the virtual machine and the underlying data file library -- GDBM in this case. The interface is designed to make it easy to substitute a different database library, such as the Berkeley DB. The backend abstracts many of the low-level details to help reduce the complexity of the virtual machine.</p> <p>The backend is contained in the single source file <b>dbbe.c</b>. The backend also has a header file <b>dbbe.h</b> that defines the interface between the backend and the rest of the SQLite library.</p> } puts { <br clear="both" /> <p><hr /></p> <p><a href="index.html"><img src="/goback.jpg" border=0 /> Back to the SQLite Home Page</a> </p> </body></html>} |
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.2 2000/06/09 01:58:37 drh Exp $} puts {<html> <head> <title>Query Language Understood By SQLite</title> </head> <body bgcolor=white> <h1 align=center> |
︙ | ︙ | |||
26 27 28 29 30 31 32 | bold blue. Non-terminal symbols are shown in italic red. Operators that are part of the syntactic markup itself are shown in black roman.</p> <p>This document is just an overview of the SQL syntax implemented by SQLite. Many low-level productions are omitted. For detailed information on the language that SQLite understands, refer to the source code.</p> | > > > > > > | > > > > > > > > > > > | > | > > > > > > > > > > > > > > > > | > | > > | < | 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 | bold blue. Non-terminal symbols are shown in italic red. Operators that are part of the syntactic markup itself are shown in black roman.</p> <p>This document is just an overview of the SQL syntax implemented by SQLite. Many low-level productions are omitted. For detailed information on the language that SQLite understands, refer to the source code.</p> <p>SQLite implements the follow SQL commands:</p> <p><ul> } foreach {section} [lsort -index 0 -dictionary { {{CREATE TABLE} createtable} {{CREATE INDEX} createindex} {VACUUM vacuum} {{DROP TABLE} droptable} {{DROP INDEX} dropindex} {INSERT insert} {DELETE delete} {UPDATE update} {SELECT select} {COPY copy} }] { puts "<li><a href=\"#[lindex $section 1]\">[lindex $section 0]</a></li>" } puts {</ul></p> <p>Details on the implementation of each command are provided in the sequel.</p> } proc Syntax {args} { puts {<table cellpadding="15">} foreach {rule body} $args { puts "<tr><td align=\"right\" valign=\"top\">" puts "<i><font color=\"#ff3434\">$rule</font></i> ::=</td>" regsub -all < $body {%LT} body regsub -all > $body {%GT} body regsub -all %LT $body {</font></b><i><font color="#ff3434">} body regsub -all %GT $body {</font></i><b><font color="#2c2cf0">} body regsub -all {[]|[*?]} $body {</font></b>&<b><font color="#2c2cf0">} body regsub -all "\n" [string trim $body] "<br>\n" body regsub -all "\n *" $body "\n\\ \\ \\ \\ " body regsub -all {[|,*()]} $body {<big>&</big>} body puts "<td><b><font color=\"#2c2cf0\">$body</font></b></td></tr>" } puts {</table>} } proc Section {name {label {}}} { puts "\n<hr />" if {$label!=""} { puts "<a name=\"$label\">" } puts "<h1>$name</h1>\n" } proc Example {text} { puts "<blockquote><pre>$text</pre></blockquote>" } Section {CREATE TABLE} {createtable} Syntax {sql-command} { CREATE TABLE <table-name> ( <column-def> [, <column-def>]* [, <constraint>]* ) } {column-def} { <name> <type> [<column-constraint>]* } {type} { <typename> | <typename> ( <number> ) | <typename> ( <number> , <number> ) } {column-constraint} { NOT NULL | PRIMARY KEY [<sort-order>] | UNIQUE | CHECK ( <expr> ) | DEFAULT <value> } {constraint} { PRIMARY KEY ( <name> [, <name>]* ) | UNIQUE ( <name> [, <name>]* ) | CHECK ( <expr> ) } puts { <p>A CREATE TABLE statement is basically the keywords "CREATE TABLE" 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. The only reserved table name is "<b>sqlite_master</b>" which is the name of the table that records the database schema.</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 is ignored. All information is stored as null-terminated strings. The constraints are also ignored, except that the PRIMARY KEY constraint will cause an index to be automatically created that implements the primary key and the DEFAULT constraint which specifies a default value to use when doing an INSERT. The name of the primary key index will be the table name with "<b>__primary_key</b>" appended. The index used for a primary key does not show up in the <b>sqlite_master</b> table, but a GDBM file is created for that index.</p> <p>There are no arbitrary limits on the size of columns, on the number of columns, or on the number of constraints in a table.</p> <p>The exact text of each CREATE TABLE statement is stored in the <b>sqlite_master</b> table. Everytime the database is opened, all CREATE TABLE statements are read from the <b>sqlite_master</b> table and used to regenerate SQLite's internal representation of the table layout.</p> } Section {CREATE INDEX} createindex Syntax {sql-statement} { CREATE INDEX <index-name> ON <table-name> ( <column-name> [, <column-name>]* ) } {column-name} { <name> [ ASC | DESC ] } |
︙ | ︙ | |||
126 127 128 129 130 131 132 | attached to a single table, nor on the number of columns in an index.</p> <p>The exact text of each CREATE INDEX statement is stored in the <b>sqlite_master</b> table. Everytime the database is opened, all CREATE INDEX statements are read from the <b>sqlite_master</b> table and used to regenerate SQLite's internal representation of the index layout.</p> | | | | > | | < | < | > | > > > > > > > > > > > > > > > > | 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 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 | attached to a single table, nor on the number of columns in an index.</p> <p>The exact text of each CREATE INDEX statement is stored in the <b>sqlite_master</b> table. Everytime the database is opened, all CREATE INDEX statements are read from the <b>sqlite_master</b> table and used to regenerate SQLite's internal representation of the index layout.</p> } Section {DROP TABLE} droptable Syntax {sql-command} { DROP TABLE <table-name> } puts { <p>The DROP TABLE statement consists of the keywords "DROP TABLE" followed by the name of the table. The table named is completely removed from the disk. The table can not be recovered. All indices associated with the table are also reversibly deleted.</p>} Section {DROP INDEX} dropindex Syntax {sql-command} { DROP INDEX <index-name> } puts { <p>The DROP INDEX statement consists of the keywords "DROP INDEX" followed by the name of the index. The index named is completely removed from the disk. The only way to recover the index is to reenter the appropriate CREATE INDEX command.</p> } Section VACUUM vacuum Syntax {sql-statement} { VACUUM [<index-or-table-name>] } puts { <p>The VACUUM command is an SQLite extension modelled after a similar command found in PostgreSQL. If VACUUM is invoked with the name of a table or index, then the <b>gdbm_reorganize()</b> function is called on the corresponding GDBM file. If VACUUM is invoked with no arguments, then <b>gdbm_reorganize()</b> is call on every GDBM file in the database.</p> <p>It is a good idea to run VACUUM after creating large indices, especially indices where a single index value refers to many entries in the data table. Reorganizing these indices will make the underlying GDBM file much smaller and will help queries to run much faster.</p> } Section INSERT insert Syntax {sql-statement} { INSERT INTO <table-name> [( <column-list> )] VALUES ( <value-list> ) | INSERT INTO <table-name> [( <column-list> )] <select-statement> } puts { <p>The INSERT statement comes in two basic forms. The first form (with the "VALUES" keyword) creates a single new row in an existing table. If no column-list is specified then the number of values must be the same as the number of columns in the table. If a column-list is specified, then the number of values must match the number of specified columns </p> } puts { <p><hr /></p> <p><a href="index.html"><img src="/goback.jpg" border=0 /> Back to the SQLite Home Page</a> </p> </body></html>} |
Added www/opcode.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 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 222 223 224 225 | # # Run this Tcl script to generate the sqlite.html file. # set rcsid {$Id: opcode.tcl,v 1.1 2000/06/09 01:58:37 drh Exp $} puts {<html> <head> <title>SQLite Virtual Machine Opcodes</title> </head> <body bgcolor=white> <h1 align=center> SQLite Virtual Machine Opcodes </h1>} puts "<p align=center> (This page was last modified on [lrange $rcsid 3 4] GMT) </p>" set fd [open [lindex $argv 0] r] set file [read $fd [file size [lindex $argv 0]]] close $fd set current_op {} foreach line [split $file \n] { set line [string trim $line] if {[string index $line 1]!="*"} { set current_op {} continue } if {[regexp {^/\* Opcode: } $line]} { set current_op [lindex $line 2] set Opcode($current_op:args) [lrange $line 3 end] lappend OpcodeList $current_op continue } if {$current_op==""} continue if {[regexp {^\*/} $line]} { set current_op {} continue } set line [string trim [string range $line 3 end]] if {$line==""} { append Opcode($current_op:text) \n<p> } else { append Opcode($current_op:text) \n$line } } unset file puts { <h2>Introduction</h2> <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. The document describes the operation of that virtual machine.</p> <p>The source code to the virtual machine is in the <b>vdbe.c</b> 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 in this document was generated by scanning the <b>vdbe.c</b> source file and extracting the necessary information from comments. So the source code comments are really the canonical source of information about the virtual macchine. When in doubt, refer to the source code.</p> <p>Each instruction in the virtual machine consists of an opcode and up to three operands named P1, P2 and P3. P1 may be an arbitrary integer. P2 must be a non-negative integer. P2 is always the jump destination in any operation that might cause a jump. P3 is a null-terminated string or NULL. Some operators use all three 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 (1) a Halt instruction is seen, or (2) the program counter becomes one greater than the address of last instruction, or (3) there is an execution error. When the virtual machine halts, all memory that it allocated is released and all database files it may have had open are closed.</p> <p>The virtual machine also contains an operand stack of unlimited depth. Many of the opcodes use operands from the stack. The details are described in the descriptions of each opcode.</p> <p>The virtual machine can have zero or more cursors. Each cursor is a pointer into a single GDBM file. There can be multiple cursors pointing at the same file. All cursors operate independenly. The only way for the virtual machine to interact with a GDBM file is through a cursor. Instructions in the virtual machine can create a new cursor (Open), read data from a cursor (Field), advance the cursor to the next entry in the GDBM file (Next), and many other operations. All cursors are automatically closed when the virtual machine terminates.</p> <p>The virtual machine contains an arbitrary number of fixed memory locations with addresses beginning at zero and growing upward. Each memory location can hold an arbitrary string. The memory cells are typically used to hold the result of a scalar SELECT that is part of a larger expression.</p> <p>The virtual machine contains an arbitrary number of sorters. Each sorter is able to accumulate records, sort those records, then play the records back in sorted order. Sorters are used to implement the ORDER BY clause of a SELECT statement. The fact that the virtual machine allows multiple sorters is an historical accident. In practice no more than one sorter (sorter number 0) ever gets used.</p> <p>The virtual machine may contain an arbitrary number of "Lists". Each list stores a list of integers. Lists are used to hold the GDBM keys for records of a GDBM file that needs to be modified. The WHERE clause of an UPDATE or DELETE statement scans through the table and writes the GDBM key of every record to be modified into a list. Then the list is played back and the table is modified in a separate step. It is necessary to do this in two steps since making a change to a GDBM file can alter the scan order.</p> <p>The virtual machine can contain an arbitrary number of "Sets". Each set holds an arbitrary number of strings. Sets are used to implement the IN operator with a constant right-hand side.</p> <p>The virtual machine can open a single external file for reading. This external read file is used to implement the COPY command.</p> <p>Finally, the virtual machine can have a single set of aggregators. An aggregator is a device used to implement the GROUP BY clause of a SELECT. An aggregator has one or more slots that can hold values being extracted by the select. The number of slots is the same for all aggregators and is defined by the AggReset operation. At any point in time a single aggregator is current or "has focus". There are operations to read or write to memory slots of the aggregator in focus. There are also operations to change the focus aggregator and to scan through all aggregators.</p> <h2>Viewing Programs Generated By SQLite</h2> <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.</p> <p>You can use the <b>sqlite</b> command-line tool to see the instructions generated by an SQL statement. The following is an example:</p>} proc Code {body} { puts {<blockquote><pre>} regsub -all {&} [string trim $body] {\&} body regsub -all {>} $body {\>} body regsub -all {<} $body {\<} body regsub -all {\(\(\(} $body {<font color="#00671f"><i>} body regsub -all {\)\)\)} $body {</i></font>} body puts $body puts {</pre></blockquote>} } Code { $ (((sqlite ex1))) sqlite> (((.explain))) sqlite> (((explain delete from tbl1 where two<20;))) addr opcode p1 p2 p3 ---- ------------ ----- ----- ------------------------------------- 0 ListOpen 0 0 1 Open 0 1 tbl1 2 Next 0 9 3 Field 0 1 4 Integer 20 0 5 Ge 0 2 6 Key 0 0 7 ListWrite 0 0 8 Goto 0 2 9 Noop 0 0 10 ListRewind 0 0 11 ListRead 0 14 12 Delete 0 0 13 Goto 0 11 14 ListClose 0 0 } puts { <p>All you have to do is add the "EXPLAIN" keyword to the front of the SQL statement. But if you use the ".explain" command to <b>sqlite</b> first, it will set up the output mode to make the program more easily viewable.</p> <p>If <b>sqlite</b> has been compiled without the "-DNDEBUG=1" option (that is, with the NDEBUG preprocessor macro not defined) then you can put the SQLite virtual machine in a mode where it will trace its execution by writing messages to standard output. There are special comments to turn tracing on and off. Use the <b>--vdbe-trace-on--</b> comment to turn tracing on and the <b>--vdbe-trace-off--</b> comment to turn tracing back off.</p> <h2>The Opcodes</h2> } puts "<p>There are currently [llength $OpcodeList] opcodes defined by the virtual machine." puts {All currently defined opcodes are described in the table below. This table was generated automatically by scanning the source code from the file <b>vdbe.c</b>.</p>} puts { <p><table cellspacing="1" border="1" cellpadding="10"> <tr><th>Opcode Name</th><th>Description</th></tr>} foreach op [lsort -dictionary $OpcodeList] { puts {<tr><td valign="top" align="center">} puts "$op" puts "<td>[string trim $Opcode($op:text)]</td></tr>" } puts {</table></p>} puts { <p><hr /></p> <p><a href="index.html"><img src="/goback.jpg" border=0 /> Back to the SQLite Home Page</a> </p> </body></html>} |