SQLite: Check-in [e372a60be8]

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview

Comment:	Documentation updates. (CVS 720)
Downloads:	Tarball \| ZIP archive
Timelines:	family \| ancestors \| descendants \| both \| trunk
Files:	files \| file ages \| folders
SHA1:	e372a60be8de3332ab6e45d82f21227b8b9acf82
User & Date:	drh 2002-08-18 19:09:23.000

Context

2002-08-18
20:28		Fix for ticket #110: return an error if trying to start a transaction within a transaction or when attempting to commit or rollback outside of a transaction. (CVS 721) (check-in: df51cb166b user: drh tags: trunk)
19:09		Documentation updates. (CVS 720) (check-in: e372a60be8 user: drh tags: trunk)
2002-08-15
13:50		Fix for ticket #135: Remove unused variables from three files. (CVS 719) (check-in: 8cf17f2a24 user: drh tags: trunk)

Changes

Changes to main.mk.

Changes to www/changes.tcl.

Changes to www/datatypes.tcl.

Changes to www/faq.tcl.

Added www/fileformat.tcl.

Changes to www/index.tcl.

Changes to www/lang.tcl.

Changes to www/quickstart.tcl.

︙
301 302 303 304 305 306 307 308 309 310 311 312 313 314	301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317	+ + +	datatypes.html: $(TOP)/www/datatypes.tcl tclsh $(TOP)/www/datatypes.tcl >datatypes.html quickstart.html: $(TOP)/www/quickstart.tcl tclsh $(TOP)/www/quickstart.tcl >quickstart.html fileformat.html: $(TOP)/www/fileformat.tcl tclsh $(TOP)/www/fileformat.tcl >fileformat.html # Files to be published on the website. # DOC = \ index.html \ sqlite.html \ changes.html \
︙
324 325 326 327 328 329 330 ~~331~~ 332 333 334 335 336 337 338	327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342	- + +	download.html \ speed.html \ faq.html \ formatchng.html \ conflict.html \ omitted.html \ datatypes.html \ ~~quickstart.html~~ quickstart.html \ fileformat.html doc: $(DOC) mkdir -p doc mv $(DOC) doc install: sqlite libsqlite.a sqlite.h mv sqlite /usr/bin
︙

1 2 3 4 5 6 7 8 9 10 11	1 2 3 4 5 6 7 8 9 10 11	- +	# # Run this script to generated a datatypes.html output file # ~~set rcsid {$Id: datatypes.tcl,v 1.4 2002/08/15 13:~~45:17~~ drh Exp $}~~ set rcsid {$Id: datatypes.tcl,v 1.5 2002/08/18 19:09:24 drh Exp $} puts {<html> <head> <title>Datatypes In SQLite</title> </head> <body bgcolor="white"> <h1 align="center">
︙
113 114 115 116 117 118 119 ~~120~~ 121 122 123 124 ~~125 126~~ 127 128 129 130 131 132 133	113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133	- + - - + +	</p> <p> If data is of type <b>text</b> then the comparison is determined by the standard C data comparison functions <b>memcmp()</b> or <b>strcmp()</b>. The comparison looks at bytes from two inputs one by one and returns the first non-zero difference. ~~String are '\000' terminated so shorter~~ Strings are '\000' terminated so shorter strings sort before longer strings, as you would expect. </p> <p> ~~For numeric data, this situation is more complex. If both ~~string~~s ~~being compared~~ look like well-formed numbers, then they are converted~~ For numeric data, this situation is more complex. If both inputs look like well-formed numbers, then they are converted into floating point values using <b>atof()</b> and compared numerically. If one input is not a well-formed number but the other is, then the number is considered to be less than the non-number. If neither inputs is a well-formed number, then <b>strcmp()</b> is used to do the comparison. </p>
︙
210 211 212 213 214 215 216 ~~217~~ 218 219 220 221 222 223 224	210 211 212 213 214 215 216 217 218 219 220 221 222 223 224	- +	inserted into a numeric column but 0==0.0 which violates the uniqueness constraint. However, the second insert in the right-hand sequence works. In this case, the constants 0 and 0.0 are treated a strings which means that they are distinct.</p> <p>SQLite always converts numbers into double-precision (64-bit) floats for comparison purposes. This means that a long sequence of digits that ~~differ only in digits ~~of far to the right~~ will compare equal if they~~ differ only in insignificant digits will compare equal if they are in a numeric column but will compare unequal if they are in a text column. We have:</p> <blockquote><pre> INSERT INTO t1 INSERT INTO t2 VALUES('12345678901234567890'); VALUES(12345678901234567890); INSERT INTO t1 INSERT INTO t2
︙

1 2 3 4 5 6 7 8 9 10 11	1 2 3 4 5 6 7 8 9 10 11	- +	# # Run this script to generated a faq.html output file # ~~set rcsid {$Id: faq.tcl,v 1.17 2002/08/15 11:~~48:1~~4 drh Exp $}~~ set rcsid {$Id: faq.tcl,v 1.18 2002/08/18 19:09:24 drh Exp $} puts {<html> <head> <title>SQLite Frequently Asked Questions</title> </head> <body bgcolor="white"> <h1 align="center">Frequently Asked Questions</h1>
︙
350 351 352 353 354 355 356 ~~357 358~~ 359 360 361 362 363 364 365	350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365	- - + +	} { <p>You can declare a table column to be of type "BLOB" but it will still only store null-terminated strings. This is because the only way to insert information into an SQLite database is using an INSERT SQL statement, and you can not include binary data in the middle of the ASCII text string of an INSERT statement.</p> ~~<p>SQLite is 8-bit clean with regard to the data is stores as long as the data does not contain any ~~NUL~~ characters. If you want to store binary~~ <p>SQLite is 8-bit clean with regard to the data it stores as long as the data does not contain any '\000' characters. If you want to store binary data, consider encoding your data in such a way that it contains no NUL characters and inserting it that way. You might use URL-style encoding: encode NUL as "%00" and "%" as "%25". Or, you might consider encoding your binary data using base-64. There is a source file named "<b>src/encode.c</b>" in the SQLite distribution that contains implementations of functions named "<b>sqlite_encode_binary()</b> and <b>sqlite_decode_binary()</b> that can be used for converting
︙

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16	1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16	- + - + - +	# # Run this TCL script to generate HTML for the index.html file. # ~~set rcsid {$Id: index.tcl,v 1.66 2002/08/15 13:~~45:17~~ drh Exp $}~~ set rcsid {$Id: index.tcl,v 1.67 2002/08/18 19:09:24 drh Exp $} puts {<html> ~~<head><title>SQLite: An SQL Database Engine ~~In A C Library~~</title></head>~~ <head><title>SQLite: An Embeddable SQL Database Engine</title></head> <body bgcolor=white> ~~<h1 align=center>SQLite~~: An~~ SQL Database Engine ~~In A C Library~~</h1>~~ <h1 align=center>SQLite<br>An Embeddable SQL Database Engine</h1> <p align=center>} puts "This page was last modified on [lrange $rcsid 3 4] UTC<br>" set vers [lindex $argv 0] puts "The latest SQLite version is <b>$vers</b>" puts " created on [exec cat last_change] UTC" puts {</p>}
︙
100 101 102 103 104 105 106 ~~107~~ 108 109 110 111 ~~112~~ 113 114 115 116 117 118 119	100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120	+ - + - +	So if you have trouble accessing the CVS server, it could be because the IP address has recently changed. Try again in a few days. </p> <p> Whenever either of the first two digits in the version number for SQLite change, it means that the underlying file format has changed. Usually these changes are backwards compatible. ~~~~has changed.~~ See <a href="formatchng.html">formatchng.html</a>~~ See <a href="formatchng.html">formatchng.html</a> for additional information. </p> } ~~puts {<h2>Database File Format Change - Version 2.6.0 - 2002 July 17</h2>~~ puts {<h2>Major Database File Format Change - Version 2.6.0 - 2002 July 17</h2> <p>Beginning with version 2.6.0, the SQLite database file format changed in an incompatible way. If you open a database file from version 2.5.6 or earlier with version 2.6.0 or later of the library, then the file format will be converted automatically. This is an irreversible operation. Once the conversion occurs, you will no longer be able to access the database file from older versions of the library. If the database is large, the
︙
139 140 141 142 143 144 145 146 147 148 149 150 ~~151~~ 152 153 154 155 156 157 158	140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161	+ + - +	<li>The <a href="lang.html">SQL Language</a> subset understood by SQLite.</li> <li>The <a href="c_interface.html">C/C++ Interface</a>.</li> <li>The <a href="tclsqlite.html">Tcl Binding</a> to SQLite.</li> <li>The <a href="arch.html">Architecture of the SQLite Library</a> describes how the library is put together.</li> <li>A description of the <a href="opcode.html">virtual machine</a> that SQLite uses to access the database.</li> <li>A description of the <a href="fileformat.html">database file format</a> used by SQLite. <li>A <a href="speed.html">speed comparison</a> between SQLite, PostgreSQL, and MySQL.</li> </ul> </p> ~~<p>The SQLite source code is 35% comment. These comments are~~ <p>The SQLite source code is 30% comment. These comments are another important source of information. </p> } puts { <table align="right"> <tr><td align="center">
︙
195 196 197 198 199 200 201 202 203 204 205 206 207 208	198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218	+ + + + + + +	$ tar xzf sqlite.tar.gz <i> Unpacks into directory named "sqlite" </i> $ mkdir bld <i> Create a separate build directory </i> $ cd bld $ ../sqlite/configure $ make <i> Builds "sqlite" and "libsqlite.a" </i> $ make test <i> Optional: run regression tests </i> </pre></blockquote> <p>If you prefer, you can also build by making whatever modifications you desire to the file "Makefile.linux-gcc" and then executing that makefile. Tha latter method is used for all official development and testing of SQLite and for building the precompiled binaries found on this website. Windows binaries are generated by cross-compiling from Linux using <a href="www.mingw.org">MinGW</a></p> } puts {<h2>Related Sites</h2> <p> For information bindings of SQLite to other programming languages (Perl, Python, Ruby, PHP, etc.) and for a list of programs currently
︙

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	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	- + + - +	# # Run this Tcl script to generate the sqlite.html file. # ~~set rcsid {$Id: lang.tcl,v 1.43 2002/08/15 11:~~48:1~~4 drh Exp $}~~ set rcsid {$Id: lang.tcl,v 1.44 2002/08/18 19:09:24 drh Exp $} puts {<html> <head> <title>Query Language Understood By SQLite</title> </head> <body bgcolor=white> <h1 align=center> SQL As Understood By SQLite </h1>} puts "<p align=center> (This page was last modified on [lrange $rcsid 3 4] UTC) </p>" puts { <p>The SQLite library understands most of the standard SQL language. But it does <a href="omitted.html">omit some features</a> ~~~~language. But it does omit some features~~ while at the same time~~ while at the same time adding a few features of its own. This document attempts to describe percisely what parts of the SQL language SQLite does and does not support.</p> <p>In all of the syntax diagrams that follow, literal text is shown in bold blue. Non-terminal symbols are shown in italic red. Operators that are part of the syntactic markup itself are shown in black roman.</p>
︙
126 127 128 129 130 131 132 ~~133~~ 134 135 136 137 138 139 140	127 128 129 130 131 132 133 134 135 136 137 138 139 140 141	- +	<p>Beginning in version 2.0, SQLite supports transactions with rollback and atomic commit.</p> <p> No changes can be made to the database except within a transaction. Any command that changes the database (basically, any SQL command other than SELECT) will automatically starts a transaction if ~~one is not already in effect. Automatically stared transactions~~ one is not already in effect. Automatically started transactions are committed at the conclusion of the command. </p> <p> Transactions can be started manually using the BEGIN command. Such transactions usually persist until the next COMMIT or ROLLBACK command. But a transaction will also
︙
218 219 220 221 222 223 224 ~~225~~ 226 ~~227~~ 228 229 230 231 232 233 234	219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235	- + - +	implementation.</p> <p>There are no arbitrary limits on the number of indices that can be attached to a single table, nor on the number of columns in an index.</p> <p>If the UNIQUE keyword appears between CREATE and INDEX then duplicate index entries are not allowed. Any attempt to insert a duplicate entry ~~will result in a ~~rollback and a~~n error ~~message~~.</p>~~ will result in an error.</p> ~~<p>The optional conflict-clause allows the specification of al alternative~~ <p>The optional conflict-clause allows the specification of an alternative default constraint conflict resolution algorithm for this index. This only makes sense if the UNIQUE keyword is used since otherwise there are not constraints on the index. The default algorithm is ABORT. If a COPY, INSERT, or UPDATE statement specifies a particular conflict resolution algorithm, that algorithm is used in place of the default algorithm specified here. See the section titled
︙
649 650 651 652 653 654 655 ~~656~~ ~~657~~ 658 659 660 661 662 663 664	650 651 652 653 654 655 656 657 658 659 660 661 662 663 664	- + -	<font color="#2c2cf0"><big>- + ! ~</big></font> </pre></blockquote> <p>Any SQLite value can be used as part of an expression. For arithmetic operations, integers are treated as integers. Strings are first converted to real numbers using <b>atof()</b>. For comparison operators, numbers compare as numbers and strings ~~compare ~~as strings. For str~~ing comp~~aris~~on~~s, case is significant~~~~ compare using the <b>strcmp()</b> function. ~~but is only used to break a tie.~~ Note that there are two variations of the equals and not equals operators. Equals can be either} puts "[Operator =] or [Operator ==]. The non-equals operator can be either [Operator !=] or [Operator {<>}]. The [Operator \|\|] operator is \"concatenate\" - it joins together the two strings of its operands.</p>"
︙
698 699 700 701 702 703 704 ~~705~~ 706 707 708 709 710 711 712	698 699 700 701 702 703 704 705 706 707 708 709 710 711 712	- +	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 or as a scalar quantity. In both cases, the SELECT should have only a single column in its result. Compound SELECTs (connected with keywords like UNION or ~~EXCEPT) are allowed. ~~Any ORDER BY clause on the select is ignored.~~~~ EXCEPT) are allowed. A SELECT in an expression is evaluated once before any other processing is performed, so none of the expressions within the select itself can refer to quantities in the containing expression.</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
︙
985 986 987 988 989 990 991 ~~992~~ 993 994 995 996 997 998 999	985 986 987 988 989 990 991 992 993 994 995 996 997 998 999	- +	Syntax {sql-statement} { PRAGMA <name> = <value> \| PRAGMA <function>(<arg>) } puts { <p>The PRAGMA command is used to modify the operation of the SQLite library. ~~The pragma command is experimental and specific pragma statements may~~ The pragma command is experimental and specific pragma statements may be removed or added in future releases of SQLite. Use this command with caution.</p> <p>The current implementation supports the following pragmas:</p> <ul> <li><p><b>PRAGMA cache_size;
︙
1170 1171 1172 1173 1174 1175 1176 ~~1177~~ 1178 1179 1180 1181 1182 1183 1184 1185 1186 1187 ~~1188~~ ~~1189 1190 1191~~ 1192 1193 1194 1195 1196 1197 1198	1170 1171 1172 1173 1174 1175 1176 1177 1178 1179 1180 1181 1182 1183 1184 1185 1186 1187 1188 1189 1190 1191 1192 1193 1194 1195	- + - + - - -	has a fixed number of columns. The number of columns in the result is specified by the expression list in between the SELECT and FROM keywords. Any arbitrary expression can be used as a result. If a result expression is } puts "[Operator *] then all columns of all tables are substituted" puts {for that one expression.</p> ~~<p>The query is executed again one or more tables specified after~~ <p>The query is executed against one or more tables specified after the FROM keyword. If multiple tables names are separated by commas, then the query is against the cross join of the various tables. The full SQL-92 join syntax can also be used to specify joins. A sub-query in parentheses may be substituted for any table name in the FROM clause. The entire FROM clause may be omitted, in which case the result is a single row consisting of the values of the expression list. </p> <p>The WHERE clause can be used to limit the number of rows over ~~which the query operates. ~~In the current implementation,~~~~ which the query operates.</p> ~~indices will only be used to~~ ~~optimize the query if WHERE expression contains equality comparisons~~ ~~connected by the AND operator.</p>~~ <p>The GROUP BY clauses causes one or more rows of the result to be combined into a single row of output. This is especially useful when the result contains aggregate functions. The expressions in the GROUP BY clause do <em>not</em> have to be expressions that appear in the result. The HAVING clause is similar to WHERE except that HAVING applies after grouping has occurred. The HAVING expression
︙

1 2 3 4 5 6 7 8 9 10 11	1 2 3 4 5 6 7 8 9 10 11	- +	# # Run this TCL script to generate HTML for the quickstart.html file. # ~~set rcsid {$Id: quickstart.tcl,v 1.1 2002/08/15 13:~~45:17~~ drh Exp $}~~ set rcsid {$Id: quickstart.tcl,v 1.2 2002/08/18 19:09:24 drh Exp $} puts {<html> <head><title>SQLite In 5 Minutes Or Less</title></head> <body bgcolor=white> <h1 align=center>SQLite In 5 Minutes Or Less</h1>} puts {
︙
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	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	- + + + + + + - - + + - + - + + + + +	<h2>Write Programs That Use SQLite</h2> <ul> <li><p>Below is a simple TCL program that demonstrates how to use the TCL interface to SQLite. The program executes the SQL statements given as the second argument on the database defined by the first ~~argument.~~</p>~~~~ argument. The commands to watch for are the <b>sqlite</b> command on line 7 which opens an SQLite database and creates a new TCL command named "<b>db</b>" to access that database, the invocation of the <b>db</b> command on line 8 to execute SQL commands against the database, and the closing of the database connection on the last line of the script.</p> <blockquote><pre> #!/usr/bin/tclsh if {$argc!=2} { puts stderr "Usage: %s DATABASE SQL-STATEMENT" exit 1 } load /usr/lib/tclsqlite.so Sqlite ~~sqlite db [lindex $argv 0] db eval [lindex $argv 1] x {~~ <b>sqlite</b> db [lindex $argv 0] <b>db</b> eval [lindex $argv 1] x { foreach v $x() { puts "$v = $x($v)" } puts "" } ~~db close~~ <b>db</b> close </pre></blockquote> </li> <li><p>Below is a simple C program that demonstrates how to use the C/C++ interface to SQLite. The name of a database is given by the first argument and the second argument is one or more SQL statements ~~to execute against the database.~~</p>~~~~ to execute against the database. The function calls to pay attention to here are the call to <b>sqlite_open()</b> on line 22 which opens the database, <b>sqlite_exec()</b> on line 27 that executes SQL command against the database, and <b>sqlite_close()</b> on line 31 that closes the database connection.</p> <blockquote><pre> #include <stdio.h> #include <sqlite.h> static int callback(void NotUsed, int argc, char argv, char azColName){ int i;
︙
79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102	88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111	- + - + - +	char *zErrMsg = 0; int rc; if( argc!=3 ){ fprintf(stderr, "Usage: %s DATABASE SQL-STATEMENT\n", argv[0]); exit(1); } ~~db = sqlite_open(argv[1], 0, &zErrMsg);~~ db = <b>sqlite_open</b>(argv[1], 0, &zErrMsg); if( db==0 ){ fprintf(stderr, "Can't open database: %s\n", &zErrMsg); exit(1); } ~~rc = sqlite_exec(db, argv[2], callback, 0, &zErrMsg);~~ rc = <b>sqlite_exec</b>(db, argv[2], callback, 0, &zErrMsg); if( rc!=SQLITE_OK ){ fprintf(stderr, "SQL error: %s\n", zErrMsg); } ~~sqlite_close(db);~~ <b>sqlite_close</b>(db); return 0; } </pre></blockquote> </li> </ul> }
︙