Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Update documentation for the 2.2.0 release. (CVS 335) |
|---|---|
| Downloads: | Tarball | ZIP archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA1: |
14392258c5b6385091be8d684e3ea684 |
| User & Date: | drh 2001-12-22 19:27:40.000 |
Context
|
2001-12-22
| ||
| 21:48 | Bug fix in the out-of-order INSERT. (CVS 336) (check-in: a26d0880b2 user: drh tags: trunk) | |
| 19:27 | Update documentation for the 2.2.0 release. (CVS 335) (check-in: 14392258c5 user: drh tags: trunk) | |
| 14:49 | Bug fixing in the new integer primary key code. (CVS 334) (check-in: 29cab124b4 user: drh tags: trunk) | |
Changes
Changes to Makefile.template.
| ︙ | ︙ | |||
328 329 330 331 332 333 334 335 336 337 338 339 340 341 | speed.html: $(TOP)/www/speed.tcl tclsh $(TOP)/www/speed.tcl >speed.html faq.html: $(TOP)/www/faq.tcl tclsh $(TOP)/www/faq.tcl >faq.html download.html: $(TOP)/www/download.tcl tclsh $(TOP)/www/download.tcl >download.html # Files to be published on the website. # DOC = \ | > > > | 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 | speed.html: $(TOP)/www/speed.tcl tclsh $(TOP)/www/speed.tcl >speed.html faq.html: $(TOP)/www/faq.tcl tclsh $(TOP)/www/faq.tcl >faq.html formatchng.html: $(TOP)/www/formatchng.tcl tclsh $(TOP)/www/formatchng.tcl >formatchng.html download.html: $(TOP)/www/download.tcl tclsh $(TOP)/www/download.tcl >download.html # Files to be published on the website. # DOC = \ |
| ︙ | ︙ | |||
349 350 351 352 353 354 355 | vdbe.html \ c_interface.html \ crosscompile.html \ mingw.html \ tclsqlite.html \ download.html \ speed.html \ | | > | 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 | vdbe.html \ c_interface.html \ crosscompile.html \ mingw.html \ tclsqlite.html \ download.html \ speed.html \ faq.html \ formatchng.html doc: $(DOC) mkdir -p doc mv $(DOC) doc install: sqlite libsqlite.a sqlite.h mv sqlite /usr/bin |
| ︙ | ︙ |
Changes to test/intpkey.test.
| ︙ | ︙ | |||
9 10 11 12 13 14 15 | # #*********************************************************************** # This file implements regression tests for SQLite library. # # This file implements tests for the special processing associated # with INTEGER PRIMARY KEY columns. # | | | 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | # #*********************************************************************** # This file implements regression tests for SQLite library. # # This file implements tests for the special processing associated # with INTEGER PRIMARY KEY columns. # # $Id: intpkey.test,v 1.3 2001/12/22 19:27:41 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl # Create a table with a primary key and a datatype other than # integer # |
| ︙ | ︙ | |||
362 363 364 365 366 367 368 |
} {0 zero entry 0}
do_test intpkey=5.2 {
execsql {
SELECT rowid, a FROM t1
}
} {-4 -4 0 0 5 5 6 6 11 11}
| > > | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 |
} {0 zero entry 0}
do_test intpkey=5.2 {
execsql {
SELECT rowid, a FROM t1
}
} {-4 -4 0 0 5 5 6 6 11 11}
# Test the ability of the COPY command to put data into a
# table that contains an integer primary key.
#
do_test intpkey-6.1 {
set f [open ./data1.txt w]
puts $f "20\tb-20\tc-20"
puts $f "21\tb-21\tc-21"
puts $f "22\tb-22\tc-22"
close $f
execsql {
COPY t1 FROM 'data1.txt';
SELECT * FROM t1 WHERE a>=20;
}
} {20 b-20 c-20 21 b-21 c-21 22 b-22 c-22}
do_test intpkey-6.2 {
execsql {
SELECT * FROM t1 WHERE b=='hello'
}
} {5 hello world 11 hello world}
do_test intpkey-6.3 {
execsql {
DELETE FROM t1 WHERE b='b-21';
SELECT * FROM t1 WHERE b=='b-21';
}
} {}
do_test intpkey-6.4 {
execsql {
SELECT * FROM t1 WHERE a>=20
}
} {20 b-20 c-20 22 b-22 c-22}
# Do an insert of values with the columns specified out of order.
#
execsql {pragma vdbe_trace=on;}
do_test intpkey-7.1 {
execsql {
INSERT INTO t1(c,b,a) VALUES('row','new',30);
SELECT * FROM t1 WHERE rowid>=30;
}
} {30 new row}
finish_test
|
Changes to www/c_interface.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: c_interface.tcl,v 1.22 2001/12/22 19:27:41 drh Exp $}
puts {<html>
<head>
<title>The C language interface to the SQLite library</title>
</head>
<body bgcolor=white>
<h1 align=center>
|
| ︙ | ︙ | |||
181 182 183 184 185 186 187 188 189 190 191 192 193 194 | #define SQLITE_FULL 13 /* Insertion failed because database is full */ #define SQLITE_CANTOPEN 14 /* Unable to open the database file */ #define SQLITE_PROTOCOL 15 /* Database lock protocol error */ #define SQLITE_EMPTY 16 /* (Internal Only) Database table is empty */ #define SQLITE_SCHEMA 17 /* The database schema changed */ #define SQLITE_TOOBIG 18 /* Too much data for one row of a table */ #define SQLITE_CONSTRAINT 19 /* Abort due to contraint violation */ </pre></blockquote> <p> The meanings of these various return values are as follows: </p> <blockquote> | > | 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 | #define SQLITE_FULL 13 /* Insertion failed because database is full */ #define SQLITE_CANTOPEN 14 /* Unable to open the database file */ #define SQLITE_PROTOCOL 15 /* Database lock protocol error */ #define SQLITE_EMPTY 16 /* (Internal Only) Database table is empty */ #define SQLITE_SCHEMA 17 /* The database schema changed */ #define SQLITE_TOOBIG 18 /* Too much data for one row of a table */ #define SQLITE_CONSTRAINT 19 /* Abort due to contraint violation */ #define SQLITE_MISMATCH 20 /* Data type mismatch */ </pre></blockquote> <p> The meanings of these various return values are as follows: </p> <blockquote> |
| ︙ | ︙ | |||
283 284 285 286 287 288 289 290 291 292 293 294 295 296 | row of a single table. If you attempt to store more than 1 megabyte in a single row, this is the return code you get. </p></dd> <dt>SQLITE_CONSTRAINT</dt> <dd><p>This constant is returned if the SQL statement would have violated a database constraint. </p></dd> </dl> </blockquote> <h2>The Extended API</h2> <p>Only the three core routines shown above are required to use SQLite. But there are many other functions that provide | > > > > > > | 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 | row of a single table. If you attempt to store more than 1 megabyte in a single row, this is the return code you get. </p></dd> <dt>SQLITE_CONSTRAINT</dt> <dd><p>This constant is returned if the SQL statement would have violated a database constraint. </p></dd> <dt>SQLITE_MISMATCH</dt> <dd><p>This error occurs when there is an attempt to insert non-integer data into a column labeled INTEGER PRIMARY KEY. For most columns, SQLite ignores the data type and allows any kind of data to be stored. But an INTEGER PRIMARY KEY column is only allowed to store integer data. </p></dd> </dl> </blockquote> <h2>The Extended API</h2> <p>Only the three core routines shown above are required to use SQLite. But there are many other functions that provide |
| ︙ | ︙ |
Changes to www/changes.tcl.
| ︙ | ︙ | |||
13 14 15 16 17 18 19 |
proc chng {date desc} {
puts "<DT><B>$date</B></DT>"
puts "<DD><P><UL>$desc</UL></P></DD>"
}
| | > > > > | 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
proc chng {date desc} {
puts "<DT><B>$date</B></DT>"
puts "<DD><P><UL>$desc</UL></P></DD>"
}
chng {2001 Dec 22 (2.2.0)} {
<li>Columns of type INTEGER PRIMARY KEY are actually used as the primary
key in underlying B-Tree representation of the table.</li>
<li>Several obscure, unrelated bugs were found and fixed while
implemented the integer primary key change of the previous bullet.</li>
<li>Added the ability to specify "*" as part of a larger column list in
the result section of a SELECT statement. For example:
<nobr>"<b>SELECT rowid, * FROM table1;</b>"</nobr>.</li>
<li>Updates to comments and documentation.</li>
}
chng {2001 Dec 14 (2.1.7)} {
|
| ︙ | ︙ |
Changes to www/faq.tcl.
1 2 3 | # # Run this script to generated a faq.html output file # | | | 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.5 2001/12/22 19:27:41 drh Exp $}
puts {<html>
<head>
<title>SQLite Frequently Asked Questions</title>
</head>
<body bgcolor="white">
<h1 align="center">Frequently Asked Questions</h1>
|
| ︙ | ︙ | |||
40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 |
BEGIN TRANSACTION;
SELECT cnt FROM counter;
UPDATE counter SET cnt=cnt+1;
COMMIT;
</pre></blockquote>
There are other ways of simulating the effect of AUTOINCREMENT but
this approach seems to be the easiest and most efficient.
}
faq {
SQLite lets me insert a string into a database column of type integer!
} {
<p>This is a feature, not a bug. SQLite is typeless. Any data can be
inserted into any column. You can put arbitrary length strings into
integer columns, floating point numbers in boolean columns, or dates
in character columns. The datatype you assign to a column in the
CREATE TABLE command is (mostly) ignored. Every column is able to hold
| > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | > > > | 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 |
BEGIN TRANSACTION;
SELECT cnt FROM counter;
UPDATE counter SET cnt=cnt+1;
COMMIT;
</pre></blockquote>
There are other ways of simulating the effect of AUTOINCREMENT but
this approach seems to be the easiest and most efficient.
<p><i>New in SQLite version 2.2.0:</i>
If one of the columns in a table has type INTEGER PRIMARY KEY and
you do an INSERT on that table that does not specify a value for
the primary key, then a unique random number is inserted automatically
in that column. This automatically generated key is random, not
sequential, but you can still use it as a unique identifier.</p>
<p>Here is an example of how the INTEGER PRIMARY KEY feature can be
used:</p>
<blockquote><pre>
CREATE TABLE ex2(
cnum INTEGER PRIMARY KEY,
name TEXT,
email TEXT
);
INSERT INTO ex2(name,email) VALUES('drh','drh@hwaci.com');
INSERT INTO ex2(name,email) VALUES('alle','alle@hwaci.com');
SELECT * FROM ex1;
</pre></blockquote>
<p>Notice that the primary key column <b>cnum</b> is not specified on
the INSERT statements. The output of the SELECT on the last line will
be something like this:</p>
<blockquote>
1597027670|drh|drh@hwaci.com<br>
1597027853|alle|alle@hwaci.com
</blockquote>
<p>The randomly generated keys in this case are 1597027670 and
1597027853. You will probably get different keys every time you
try this. The keys will often be ascending, but this is not always
the case and you cannot count on that behavior. The keys will never
be sequential. If you need sequential keys, use the counter implemention
described first.</p>
}
faq {
SQLite lets me insert a string into a database column of type integer!
} {
<p>This is a feature, not a bug. SQLite is typeless. Any data can be
inserted into any column. You can put arbitrary length strings into
integer columns, floating point numbers in boolean columns, or dates
in character columns. The datatype you assign to a column in the
CREATE TABLE command is (mostly) ignored. Every column is able to hold
an arbitrary length string. (There is one exception: Columns of
type INTEGER PRIMARY KEY may only hold an integer. An error will result
if you try to put anything other than an integer into an
INTEGER PRIMARY KEY column.)</p>
<p>Because SQLite ignores data types, you can omit the data type definition
from columns in CREATE TABLE statements. For example, instead of saying
<blockquote><pre>
CREATE TABLE t1(
f1 int,
f2 varchar(10),
|
| ︙ | ︙ | |||
172 173 174 175 176 177 178 179 180 181 182 183 184 185 |
} {
<p>Almost. In the source file named "<b>os.c</b>" there are two functions
named <b>sqliteOsEnterMutex()</b> and <b>sqliteOsLeaveMutex()</b>. In
the default distribution these functions are stubs. They do not do anything.
If you change them so that they actually implement a mutex, then SQLite
will be threadsafe. But because these routines are stubs, the default
SQLite distribution is not threadsafe.</p>
}
faq {
How do I list all tables/indices contained in an SQLite database
} {
<p>If you are running the <b>sqlite</b> command-line access program
you can type "<b>.tables</b>" to get a list of all tables. Or you
| > > > > > > | 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 |
} {
<p>Almost. In the source file named "<b>os.c</b>" there are two functions
named <b>sqliteOsEnterMutex()</b> and <b>sqliteOsLeaveMutex()</b>. In
the default distribution these functions are stubs. They do not do anything.
If you change them so that they actually implement a mutex, then SQLite
will be threadsafe. But because these routines are stubs, the default
SQLite distribution is not threadsafe.</p>
<p>"Threadsafe" in the previous paragraph means that two or more threads
can run SQLite at the same time on different "<b>sqlite</b>" structures
returned from separate calls to <b>sqlite_open()</b>. It is never safe
to use the same <b>sqlite</b> structure pointer simultaneously in two
or more threads.</p>
}
faq {
How do I list all tables/indices contained in an SQLite database
} {
<p>If you are running the <b>sqlite</b> command-line access program
you can type "<b>.tables</b>" to get a list of all tables. Or you
|
| ︙ | ︙ |
Added www/formatchng.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 |
#
# Run this Tcl script to generate the formatchng.html file.
#
set rcsid {$Id: formatchng.tcl,v 1.1 2001/12/22 19:27:41 drh Exp $ }
puts {<html>
<head>
<title>File Format Changes in SQLite</title>
</head>
<body bgcolor=white>
<h1 align=center>
File Format Changes in SQLite
</h1>}
puts "<p align=center>
(This page was last modified on [lrange $rcsid 3 4] UTC)
</p>"
puts {
<p>
From time to time, enhancements or bug fixes require a change to
the underlying file format for SQLite. When this happens and you
want to upgrade your library, you must convert the contents of your
databases into a portable ASCII representation using the old version
of the library then reload the data using the new version of the
library.
</p>
<p>
You can tell if you should reload your databases by comparing the
version numbers of the old and new libraries. If either of the
first two digits in the version number change, then a reload is
either required or recommended. For example, upgrading from
version 1.0.32 to 2.0.0 requires a reload. So does going from
version 2.0.8 to 2.1.0.
</p>
<p>
The following table summarizes the SQLite file format changes that have
occurred since version 1.0.0:
</p>
<blockquote>
<table border=2 cellpadding=5>
<tr>
<th>Version Change</th>
<th>Approx. Date</th>
<th>Description Of File Format Change</th>
</tr>
<tr>
<td valign="top">1.0.32 to 2.0.0</td>
<td valign="top">2001-Sep-20</td>
<td>Version 1.0.X of SQLite used the GDBM library as its backend
interface to the disk. Beginning in version 2.0.0, GDBM was replaced
by a custom B-Tree library written especially for SQLite. The new
B-Tree backend is twice as fast as GDBM, supports atomic commits and
rollback, and stores an entire database in a single disk file instead
using a separate file for each table as GDBM does. The two
file formats are not even remotely similar.</td>
</tr>
<tr>
<td valign="top">2.0.8 to 2.1.0</td>
<td valign="top">2001-Nov-12</td>
<td>The same basic B-Tree format is used but the details of the
index keys were changed in order to provide better query
optimization opportunities. Some of the headers were also changed in order
to increase the maximum size of a row from 64KB to 24MB.</td>
</tr>
<tr>
<td valign="top">2.1.7 to 2.2.0</td>
<td valign="top">2001-Dec-21</td>
<td>Beginning with version 2.2.0, SQLite no longer builds an index for
an INTEGER PRIMARY KEY column. Instead, it uses that column as the actual
B-Tree key for the main table.<p>Version 2.2.0 and later of the library
will automatically detect when it is reading a 2.1.x database and will
disable the new INTEGER PRIMARY KEY feature. In other words, version
2.2.x is backwards compatible to version 2.1.x. But version 2.1.x is not
forward compatible with version 2.2.x. If you try to open
a 2.2.x database with an older 2.1.x library and that database contains
an INTEGER PRIMARY KEY, you will likely get a coredump. If the database
schema does not contain any INTEGER PRIMARY KEYs, then the version 2.1.x
and version 2.2.x database files will be identical and completely
interchangeable.</p>
</tr>
</table>
</blockquote>
<p>
To perform a database reload, have ready versions of the
<b>sqlite</b> command-line utility for both the old and new
version of SQLite. Call these two executables "<b>sqlite-old</b>"
and "<b>sqlite-new</b>". Suppose the name of your old database
is "<b>old.db</b>" and you want to create a new database with
the same information named "<b>new.db</b>". The command to do
this is as follows:
</p>
<blockquote>
echo .dump | sqlite-old old.db | sqlite-new new.db
</blockquote>
}
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>}
|
Changes to www/index.tcl.
1 2 3 | # # Run this TCL script to generate HTML for the index.html file. # | | | 1 2 3 4 5 6 7 8 9 10 11 |
#
# Run this TCL script to generate HTML for the index.html file.
#
set rcsid {$Id: index.tcl,v 1.50 2001/12/22 19:27:41 drh Exp $}
puts {<html>
<head><title>SQLite: An SQL Database Engine In A C Library</title></head>
<body bgcolor=white>
<h1 align=center>SQLite: An SQL Database Engine In A C Library</h1>
<p align=center>}
puts "This page was last modified on [lrange $rcsid 3 4] UTC<br>"
|
| ︙ | ︙ | |||
62 63 64 65 66 67 68 |
puts {<h2>Current Status</h2>
<p>A <a href="changes.html">change history</a> is available online.
The latest source code is
<a href="download.html">available for download</a>.
There are currently no known memory leaks or bugs
in the library.
| | | | < < < < < < < < < | < < < | < < < < < | < < < < < < | 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 |
puts {<h2>Current Status</h2>
<p>A <a href="changes.html">change history</a> is available online.
The latest source code is
<a href="download.html">available for download</a>.
There are currently no known memory leaks or bugs
in the library.
SQLite 2.1.7 is currently being used in several mission-critical
applications. SQLite 2.2.0 is in beta-test.
</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. See <a href="formatchng.html">formatchng.html</a>
for additional information.
</p>
<h2>Documentation</h2>
<p>The following documentation is currently available:</p>
<p><ul>
|
| ︙ | ︙ |
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.18 2001/12/22 19:27:41 drh Exp $}
puts {<html>
<head>
<title>Query Language Understood By SQLite</title>
</head>
<body bgcolor=white>
<h1 align=center>
|
| ︙ | ︙ | |||
235 236 237 238 239 240 241 | 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 UNIQUE constraint causes an index to be created on the specified | | < > > > > > > > > > > > > > > | 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 | 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 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. </p> <p>Specifying a PRIMARY KEY normally just creates a UNIQUE index on the primary key. However, if primary key is on a single column that has datatype INTEGER, then that column is used internally as the actual key of the B-Tree for the table. This means that the column may only hold unique integer values. (Except for this one case, SQLite ignores the datatype specification of columns and allows any kind of data to be put in a column regardless of its declared datatype.) If a table does not have an INTEGER PRIMARY KEY column, then the B-Tree key will be a randomly generated integer. The B-Tree key for a row can always be accessed using one of the special names "<b>ROWID</b>", "<b>OID</b>", or "<b>_ROWID_</b>". This is true regardless of whether or not there is an INTEGER PRIMARY KEY.</p> <p>If the "TEMP" or "TEMPORARY" keyword occurs in between "CREATE" and "TABLE" then the table that is created is only visible to the process that opened the database and is automatically deleted when the database is closed. Any indices created on a temporary table are also temporary. Temporary tables and indices are stored in a separate file distinct from the main database file.</p> |
| ︙ | ︙ |