/ Check-in [e372a60b]
Login

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

Overview
Comment:Documentation updates. (CVS 720)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:e372a60be8de3332ab6e45d82f21227b8b9acf82
User & Date: drh 2002-08-18 19:09:23
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: df51cb16 user: drh tags: trunk
19:09
Documentation updates. (CVS 720) check-in: e372a60b user: drh tags: trunk
2002-08-15
13:50
Fix for ticket #135: Remove unused variables from three files. (CVS 719) check-in: 8cf17f2a user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to main.mk.

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

Changes to www/changes.tcl.

    25     25     puts "<DD><P><UL>$desc</UL></P></DD>"
    26     26   }
    27     27   
    28     28   chng {2002 Aug ?? (2.7.0)} {
    29     29   <li>Make a distinction between numeric and text values when sorting.
    30     30       Text values sort according to memcmp().  Numeric values sort in
    31     31       numeric order.</li>
           32  +<li>Allow multiple simulataneous readers under windows by simulating
           33  +    the reader/writers locks that are missing from Win95/98/ME.</li>
    32     34   }
    33     35   
    34     36   chng {2002 Aug 12 (2.6.3)} {
    35     37   <li>Add the ability to read both little-endian and big-endian databases.
    36     38       So database created under SunOS or MacOSX can be read and written
    37     39       under Linux or Windows and vice versa.</li>
    38     40   <li>Convert to the new website: http://www.sqlite.org/</li>

Changes to www/datatypes.tcl.

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

Changes to www/faq.tcl.

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

Added www/fileformat.tcl.

            1  +#
            2  +# Run this script to generated a fileformat.html output file
            3  +#
            4  +set rcsid {$Id: fileformat.tcl,v 1.6 2002/08/18 19:09:24 drh Exp $}
            5  +
            6  +puts {<html>
            7  +<head>
            8  +  <title>SQLite Database File Format</title>
            9  +</head>
           10  +<body bgcolor="white">
           11  +<h1 align="center">
           12  +SQLite Database File Format
           13  +</h1>
           14  +}
           15  +puts "<p align=center>
           16  +(This page was last modified on [lrange $rcsid 3 4] UTC)
           17  +</p>"
           18  +
           19  +puts {
           20  +<h2>1.0 &nbsp; Layers</h2>
           21  +
           22  +<p>
           23  +SQLite is implemented in layers.
           24  +(See the <a href="arch.html">architecture description</a>.)
           25  +The format of database files is determined by three different
           26  +layers in the architecture.
           27  +</p>
           28  +
           29  +<ul>
           30  +<li>The <b>schema</b> layer implemented by the VDBE.</li>
           31  +<li>The <b>b-tree</b> layer implemented by btree.c</li>
           32  +<li>The <b>pager</b> layer implemented by pager.c</li>
           33  +</ul>
           34  +
           35  +<p>
           36  +We wil describe each layer beginning with the bottom (pager)
           37  +layer and working upwards.
           38  +</p>
           39  +
           40  +<h2>2.0 &nbsp; The Pager Layer</h2>
           41  +
           42  +<p>
           43  +An SQLite database consists of
           44  +"pages" of data.  Each page is 1024 bytes in size.
           45  +Pages are numbered beginning with 1.
           46  +A page number of 0 is used to indicate "no such page" in the
           47  +B-Tree and Schema layers.
           48  +</p>
           49  +
           50  +<p>
           51  +The pager layer is responsible for implementing transactions
           52  +with atomic commit and rollback.  It does this using a separate
           53  +journal file.  Whenever a new transaction is started, a journal
           54  +file is created that records the original state of the database.
           55  +If the program terminates before completing the transaction, the next
           56  +process to open the database can use the journal file to restore
           57  +the database to its original state.
           58  +</p>
           59  +
           60  +<p>
           61  +The journal file is located in the same directory as the database
           62  +file and has the same name as the database file but with the
           63  +characters "<tt>-journal</tt>" appended.
           64  +</p>
           65  +
           66  +<p>
           67  +The pager layer does not impose any content restrictions on the
           68  +main database file.  As far as the pager is concerned, each page
           69  +contains 1024 bytes of arbitrary data.  But there is structure to
           70  +the journal file.
           71  +</p>
           72  +
           73  +<p>
           74  +A journal file begins with 8 bytes as follows:
           75  +0xd9, 0xd5, 0x05, 0xf9, 0x20, 0xa1, 0x63, and 0xd5.
           76  +Processes that are attempting to rollback a journal use these 8 bytes
           77  +as a sanity check to make sure the file they think is a journal really
           78  +is a valid journal.  There is no significance to the choice of
           79  +bytes here - the values were obtained from /dev/random. 
           80  +</p>
           81  +
           82  +<p>
           83  +Following the 8 byte prefix is a single 4-byte integer that is the
           84  +original size of the main database file before the transaction was
           85  +started.  The main database file is truncated back to this size
           86  +as part of the rollback process.
           87  +The size is expressed in pages (1024 bytes per page) and is
           88  +a big-endian number.  That means that the most significant byte
           89  +occurs first.  All multi-byte integers in the journal file are
           90  +written as big-endian numbers.  That way, a journal file that is
           91  +originally created on one machine can be rolled back by another
           92  +machine that uses a different byte order.  So, for example, a
           93  +transaction that failed to complete on your big-endian SparcStation
           94  +can still be rolled back on your little-endian Linux box.
           95  +</p>
           96  +
           97  +<p>
           98  +After the 8-byte prefix and the 4-byte initial database size, the
           99  +journal file consists of zero or more page records.  Each page
          100  +record is a 4-byte (big-endian) page number followed by 1024 bytes
          101  +of data.  The data is the original content of the database page
          102  +before the transaction was started.  So to roll back the transaction,
          103  +the data is simply written into the corresponding page of the
          104  +main database file.  Pages can appear in the journal in any order,
          105  +but they are guaranteed to appear only once. All page numbers will be
          106  +between 1 and the maximum specified by the page size integer that
          107  +appeared at the beginning of the journal.
          108  +</p>
          109  +
          110  +<p>
          111  +Here is a summary of the journal file format:
          112  +</p>
          113  +
          114  +<ul>
          115  +<li>8 byte prefix: 0xd9, 0xd5, 0x05, 0xf9, 0x20, 0xa1, 0x63, x0d5</li>
          116  +<li>4 byte initial database page count, big-endian.</li>
          117  +<li>Zero or more instances of the following:
          118  +   <ul>
          119  +   <li>4 byte page number - big-endian</li>
          120  +   <li>1024 bytes of original data for the page</li>
          121  +   </ul>
          122  +</li>
          123  +</ul>
          124  +
          125  +<h2>3.0 &nbsp; The B-Tree Layer</h2>
          126  +
          127  +<p>
          128  +The B-Tree layer builds on top of the pager layer to implement
          129  +one or more separate b-trees all in the same disk file.  The
          130  +algorithms used are taken from Knuth's <i>The Art Of Computer
          131  +Programming.</i></p>
          132  +
          133  +<p>
          134  +Page 1 of a database contains a header string used for sanity
          135  +checking, a few 32-bit words of configuration data, and a pointer
          136  +to the beginning of a list of unused pages in the database.
          137  +All other pages in the
          138  +database are either pages of a b-tree, overflow pages, or unused
          139  +pages on the freelist.
          140  +</p>
          141  +
          142  +<p>
          143  +Each b-tree page contains zero or more database entries.
          144  +Each entry has an unique key of one or more bytes and data of
          145  +zero or more bytes.
          146  +Both the key and data are arbitrary byte sequences.  The combination
          147  +of key and data are collectively known as "payload".  The current
          148  +implementation limits the amount of payload in a single entry to
          149  +1048576 bytes.  This limit can be raised to 16777216 by adjusting
          150  +a single #define in the source code and recompiling.  But most entries
          151  +contain less than a hundred bytes of payload so a megabyte limit seems
          152  +more than enough.
          153  +</p>
          154  +
          155  +<p>
          156  +Up to 238 bytes of payload for an entry can be held directly on
          157  +a b-tree page.  Any additional payload is contained on a linked list
          158  +of overflow pages.  This limit on the amount of payload held directly
          159  +on b-tree pages guarantees that each b-tree page can hold at least
          160  +4 entries.  In practice, most entries are smaller than 238 bytes and
          161  +thus most pages can hold more than 4 entries.
          162  +</p>
          163  +
          164  +<p>
          165  +A single database file can hold any number of separate, independent b-trees.
          166  +Each b-tree is identified by its root page, which never changes.
          167  +Child pages of the b-tree may change as entries are added and removed
          168  +and pages split and combine.  But the root page always stays the same.
          169  +The b-tree itself does not record which pages are root pages and which
          170  +are not.  That information is handled entirely at the schema layer.
          171  +</p>
          172  +
          173  +<h3>3.1 &nbsp; B-Tree Page 1 Details</h3>
          174  +
          175  +<p>
          176  +Page 1 begins with the following 48-byte string:
          177  +</p>
          178  +
          179  +<blockquote><pre>
          180  +** This file contains an SQLite 2.1 database **
          181  +</pre></blockquote>
          182  +
          183  +<p>
          184  +If you count the number of characters in the string above, you will
          185  +see that there are only 47.  A '\000' terminator byte is added to
          186  +bring the total to 48.
          187  +</p>
          188  +
          189  +<p>
          190  +A frequent question is why the string says version 2.1 when (as
          191  +of this writing) we are up to version 2.7.0 of SQLite and any
          192  +change to the second digit of the version is suppose to represent
          193  +a database format change.  The answer to this is that the B-tree
          194  +layer has not changed any since version 2.1.  There have been
          195  +database format changes since version 2.1 but those changes have
          196  +all been in the schema layer.  Because the format of the b-tree
          197  +layer is unchanged since version 2.1.0, the header string still
          198  +says version 2.1.
          199  +</p>
          200  +
          201  +<p>
          202  +After the format string is a 4-byte integer used to determine the
          203  +byte-order of the database.  The integer has a value of
          204  +0xdae37528.  If this number is expressed as 0xda, 0xe3, 0x75, 0x28, then
          205  +the database is in a big-endian format and all 16 and 32-bit integers
          206  +elsewhere in the b-tree layer are also big-endian.  If the number is
          207  +expressed as 0x28, 0x75, 0xe3, and 0xda, then the database is in a
          208  +little-endian format and all other multi-byte numbers in the b-tree 
          209  +layer are also little-endian.  
          210  +Prior to version 2.6.3, the SQLite engine was only able to read databases
          211  +that used the same byte order as the processor they were running on.
          212  +But beginning with 2.6.3, SQLite can read or write databases in any
          213  +byte order.
          214  +</p>
          215  +
          216  +<p>
          217  +After the byte-order code are six 4-byte integers.  Each integer is in the
          218  +byte order determined by the byte-order code.  The first integer is the
          219  +page number for the first page of the freelist.  If there are no unused
          220  +pages in the database, then this integer is 0.  The second integer is
          221  +the number of unused pages in the database.  The last 4 integers are
          222  +not used by the b-tree layer.  These are the so-called "meta" values that
          223  +are passed up to the schema layer
          224  +and used there for configuration and format version information.
          225  +All bytes of page 1 past beyond the meta-value integers are unused 
          226  +and are initialized to zero.
          227  +</p>
          228  +
          229  +<p>
          230  +Here is a summary of the information contained on page 1 in the b-tree layer:
          231  +</p>
          232  +
          233  +<ul>
          234  +<li>48 byte header string</li>
          235  +<li>4 byte integer used to determine the byte-order</li>
          236  +<li>4 byte integer which is the first page of the freelist</li>
          237  +<li>4 byte integer which is the number of pages on the freelist</li>
          238  +<li>16 bytes of meta-data arranged as four 4-byte integers</li>
          239  +<li>948 bytes of unused space</li>
          240  +</ul>
          241  +
          242  +<h3>3.2 &nbsp; Structure Of A Single B-Tree Page</h3>
          243  +
          244  +<p>
          245  +Conceptually, a b-tree page contains N database entries and N+1 pointers
          246  +to other b-tree pages.
          247  +</p>
          248  +
          249  +<blockquote>
          250  +<table border=1 cellspacing=0 cellpadding=5>
          251  +<tr>
          252  +<td align="center">Ptr<br>0</td>
          253  +<td align="center">Entry<br>0</td>
          254  +<td align="center">Ptr<br>1</td>
          255  +<td align="center">Entry<br>1</td>
          256  +<td align="center"><b>...</b></td>
          257  +<td align="center">Ptr<br>N-1</td>
          258  +<td align="center">Entry<br>N-1</td>
          259  +<td align="center">Ptr<br>N</td>
          260  +</tr>
          261  +</table>
          262  +</blockquote>
          263  +
          264  +<p>
          265  +The entries are arranged in increasing order.  That is, the key to
          266  +Entry 0 is less than the key to Entry 1, and the key to Entry 1 is
          267  +less than the key of Entry 2, and so forth.  The pointers point to
          268  +pages containing additional entries that have keys in between the
          269  +entries on either side.  So Ptr 0 points to another b-tree page that
          270  +contains entries that all have keys less than Key 0, and Ptr 1
          271  +points to a b-tree pages where all entries have keys greater than Key 0
          272  +but less than Key 1, and so forth.
          273  +</p>
          274  +
          275  +<p>
          276  +Each b-tree page in SQLite consists of a header, zero or more "cells"
          277  +each holding a single entry and pointer, and zero or more "free blocks"
          278  +that represent unused space on the page.
          279  +</p>
          280  +
          281  +<p>
          282  +The header on a b-tree page is the first 8 bytes of the page.
          283  +The header contains the value
          284  +of the right-most pointer (Ptr N) and the byte offset into the page
          285  +of the first cell and the first free block.  The pointer is a 32-bit
          286  +value and the offsets are each 16-bit values.  We have:
          287  +</p>
          288  +
          289  +<blockquote>
          290  +<table border=1 cellspacing=0 cellpadding=5>
          291  +<tr>
          292  +<td align="center" width=30>0</td>
          293  +<td align="center" width=30>1</td>
          294  +<td align="center" width=30>2</td>
          295  +<td align="center" width=30>3</td>
          296  +<td align="center" width=30>4</td>
          297  +<td align="center" width=30>5</td>
          298  +<td align="center" width=30>6</td>
          299  +<td align="center" width=30>7</td>
          300  +</tr>
          301  +<tr>
          302  +<td align="center" colspan=4>Ptr N</td>
          303  +<td align="center" colspan=2>Cell 0</td>
          304  +<td align="center" colspan=2>Freeblock 0</td>
          305  +</tr>
          306  +</table>
          307  +</blockquote>
          308  +
          309  +<p>
          310  +The 1016 bytes of a b-tree page that come after the header contain
          311  +cells and freeblocks.  All 1016 bytes are covered by either a cell
          312  +or a freeblock.
          313  +</p>
          314  +
          315  +<p>
          316  +The cells are connected in a linked list.  Cell 0 contains Ptr 0 and
          317  +Entry 0.  Bytes 4 and 5 of the header point to Cell 0.  Cell 0 then
          318  +points to Cell 1 which contains Ptr 1 and Entry 1.  And so forth.
          319  +Cells vary in size.  Every cell has a 12-byte header and at least 4
          320  +bytes of payload space.  Space is allocated to payload in increments
          321  +of 4 bytes.  Thus the minimum size of a cell is 16 bytes and up to
          322  +63 cells can fit on a single page.  The size of a cell is always a multiple
          323  +of 4 bytes.
          324  +A cell can have up to 238 byte of payload space.  If
          325  +the payload is more than 238 bytes, then an addition 4 point page
          326  +number is appended to the cell which is the page number of the first
          327  +overflow page containing the additional payload.  The maximum size
          328  +of a cell is thus 254 bytes, meaning that a least 4 cells can fit into
          329  +the 1016 bytes of space available on a b-tree page.
          330  +An average cell is usually around 52 to 100 bytes in size with about
          331  +10 or 20 cells to a page.
          332  +</p>
          333  +
          334  +<p>
          335  +The data layout of a cell looks like this:
          336  +</p>
          337  +
          338  +<blockquote>
          339  +<table border=1 cellspacing=0 cellpadding=5>
          340  +<tr>
          341  +<td align="center" width=20>0</td>
          342  +<td align="center" width=20>1</td>
          343  +<td align="center" width=20>2</td>
          344  +<td align="center" width=20>3</td>
          345  +<td align="center" width=20>4</td>
          346  +<td align="center" width=20>5</td>
          347  +<td align="center" width=20>6</td>
          348  +<td align="center" width=20>7</td>
          349  +<td align="center" width=20>8</td>
          350  +<td align="center" width=20>9</td>
          351  +<td align="center" width=20>10</td>
          352  +<td align="center" width=20>11</td>
          353  +<td align="center" width=100>12 ... 249</td>
          354  +<td align="center" width=20>250</td>
          355  +<td align="center" width=20>251</td>
          356  +<td align="center" width=20>252</td>
          357  +<td align="center" width=20>253</td>
          358  +</tr>
          359  +<tr>
          360  +<td align="center" colspan=4>Ptr</td>
          361  +<td align="center" colspan=2>Keysize<br>(low)</td>
          362  +<td align="center" colspan=2>Next</td>
          363  +<td align="center" colspan=1>Ksz<br>(hi)</td>
          364  +<td align="center" colspan=1>Dsz<br>(hi)</td>
          365  +<td align="center" colspan=2>Datasize<br>(low)</td>
          366  +<td align="center" colspan=1>Payload</td>
          367  +<td align="center" colspan=4>Overflow<br>Pointer</td>
          368  +</tr>
          369  +</table>
          370  +</blockquote>
          371  +
          372  +<p>
          373  +The first four bytes are the pointer.  The size of the key is a 24-bit
          374  +where the upper 8 bits are taken from by 8 and the lower 16 bits are
          375  +token from bytes 4 and 5 (or bytes 5 and 4 on little-endian machines.)
          376  +The size of the data is another 24-bit value where the upper 8 bits
          377  +are taken from byte 9 and the lower 16 bits are taken from bytes 10 and
          378  +11 or 11 and 10, depending on the byte order.  Bytes 6 and 7 are the
          379  +offset to the next cell in the linked list of all cells on the current
          380  +page.  This offset is 0 for the last cell on the page.
          381  +</p>
          382  +
          383  +<p>
          384  +The payload itself can be any number of bytes between 1 and 1048576.
          385  +But space to hold the payload is allocated in 4-byte chunks up to
          386  +238 bytes.  If the entry contains more than 238 bytes of payload, then
          387  +additional payload data is stored on a linked list of overflow pages.
          388  +A 4 byte page number is appended to the cell that contains the first
          389  +page of this linked list.
          390  +</p>
          391  +
          392  +<p>
          393  +Each overflow page begins with a 4-byte value which is the
          394  +page number of the next overflow page in the list.   This value is
          395  +0 for the last page in the list.  The remaining
          396  +1020 bytes of the overflow page are available for storing payload.
          397  +Note that a full page is allocated regardless of the number of overflow
          398  +bytes stored.  Thus, if the total payload for an entry is 239 bytes,
          399  +the first 238 are stored in the cell and the overflow page stores just
          400  +one byte.
          401  +</p>
          402  +
          403  +<p>
          404  +The structure of an overflow page looks like this:
          405  +</p>
          406  +
          407  +<blockquote>
          408  +<table border=1 cellspacing=0 cellpadding=5>
          409  +<tr>
          410  +<td align="center" width=20>0</td>
          411  +<td align="center" width=20>1</td>
          412  +<td align="center" width=20>2</td>
          413  +<td align="center" width=20>3</td>
          414  +<td align="center" width=200>4 ... 1023</td>
          415  +</tr>
          416  +<tr>
          417  +<td align="center" colspan=4>Next Page</td>
          418  +<td align="center" colspan=1>Overflow Data</td>
          419  +</tr>
          420  +</table>
          421  +</blockquote>
          422  +
          423  +<p>
          424  +All space on a b-tree page which is not used by the header or by cells
          425  +is filled by freeblocks.  Freeblocks, like cells, are variable in size.
          426  +The size of a freeblock is at least 4 bytes and is always a multiple of
          427  +4 bytes.
          428  +The first 4 bytes contain a header and the remaining bytes
          429  +are unused.  The structure of the freeblock is as follows:
          430  +</p>
          431  +
          432  +<blockquote>
          433  +<table border=1 cellspacing=0 cellpadding=5>
          434  +<tr>
          435  +<td align="center" width=20>0</td>
          436  +<td align="center" width=20>1</td>
          437  +<td align="center" width=20>2</td>
          438  +<td align="center" width=20>3</td>
          439  +<td align="center" width=200>4 ... 1015</td>
          440  +</tr>
          441  +<tr>
          442  +<td align="center" colspan=2>Size</td>
          443  +<td align="center" colspan=2>Next</td>
          444  +<td align="center" colspan=1>Unused</td>
          445  +</tr>
          446  +</table>
          447  +</blockquote>
          448  +
          449  +<p>
          450  +Freeblocks are stored in a linked list in increasing order.  That is
          451  +to say, the first freeblock occurs at a lower index into the page than
          452  +the second free block, and so forth.  The first 2 bytes of the header
          453  +are an integer which is the total number of bytes in the freeblock.
          454  +The second 2 bytes are the index into the page of the next freeblock
          455  +in the list.  The last freeblock has a Next value of 0.
          456  +</p>
          457  +
          458  +<p>
          459  +When a new b-tree is created in a database, the root page of the b-tree
          460  +consist of a header and a single 1016 byte freeblock.  As entries are
          461  +added, space is carved off of that freeblock and used to make cells.
          462  +When b-tree entries are deleted, the space used by their cells is converted
          463  +into freeblocks.  Adjacent freeblocks are merged, but the page can still
          464  +become fragmented.  The b-tree code will occasionally try to defragment
          465  +the page by moving all cells to the beginning and constructing a single
          466  +freeblock at the end to take up all remaining space.
          467  +</p>
          468  +
          469  +<h3>3.3 &nbsp; The B-Tree Free Page List</h3>
          470  +
          471  +<p>
          472  +When information is removed from an SQLite database such that one or
          473  +more pages are no longer needed, those pages are added to a list of
          474  +free pages so that they can be reused later when new information is
          475  +added.  This subsection describes the structure of this freelist.
          476  +</p>
          477  +
          478  +<p>
          479  +The 32-bit integer beginning at byte-offset 52 in page 1 of the database
          480  +contains the address of the first page in a linked list of free pages.
          481  +If there are no free pages available, this integer has a value of 0.
          482  +The 32-bit integer at byte-offset 56 in page 1 contains the number of
          483  +free pages on the freelist.
          484  +</p>
          485  +
          486  +<p>
          487  +The freelist contains a trunk and many branches.  The trunk of
          488  +the freelist is composed of overflow pages.  That is to say, each page
          489  +contains a single 32-bit integer at byte offset 0 which
          490  +is the page number of the next page on the freelist trunk.
          491  +The payload area
          492  +of each trunk page is used to record pointers to branch pages. 
          493  +The first 32-bit integer in the payload area of a trunk page
          494  +is the number of branch pages to follow (between 0 and 254)
          495  +and each subsequent 32-bit integer is a page number for a branch page.
          496  +The following diagram shows the structure of a trunk freelist page:
          497  +</p>
          498  +
          499  +<blockquote>
          500  +<table border=1 cellspacing=0 cellpadding=5>
          501  +<tr>
          502  +<td align="center" width=20>0</td>
          503  +<td align="center" width=20>1</td>
          504  +<td align="center" width=20>2</td>
          505  +<td align="center" width=20>3</td>
          506  +<td align="center" width=20>4</td>
          507  +<td align="center" width=20>5</td>
          508  +<td align="center" width=20>6</td>
          509  +<td align="center" width=20>7</td>
          510  +<td align="center" width=200>8 ... 1023</td>
          511  +</tr>
          512  +<tr>
          513  +<td align="center" colspan=4>Next trunk page</td>
          514  +<td align="center" colspan=4># of branch pages</td>
          515  +<td align="center" colspan=1>Page numbers for branch pages</td>
          516  +</tr>
          517  +</table>
          518  +</blockquote>
          519  +
          520  +<p>
          521  +It is important to note that only the pages on the trunk of the freelist
          522  +contain pointers to other pages.  The branch pages contain no
          523  +data whatsoever.  The fact that the branch pages are completely
          524  +blank allows for an important optimization in the paging layer.  When
          525  +a branch page is removed from the freelist to be reused, it is not
          526  +necessary to write the original content of that page into the rollback
          527  +journal.  The branch page contained no data to begin with, so there is
          528  +no need to restore the page in the event of a rollback.  Similarly,
          529  +when a page is not longer needed and is added to the freelist as a branch
          530  +page, it is not necessary to write the content of that page
          531  +into the database file.
          532  +Again, the page contains no real data so it is not necessary to record the
          533  +content of that page.  By reducing the amount of disk I/O required,
          534  +these two optimizations allow some database operations
          535  +to go four to six times faster than they would otherwise.
          536  +</p>
          537  +
          538  +<h2>4.0 &nbsp; The Schema Layer</h2>
          539  +
          540  +<p>
          541  +The schema layer implements an SQL database on top of one or more
          542  +b-trees and keeps track of the root page numbers for all b-trees.
          543  +Where the b-tree layer provides only unformatted data storage with
          544  +a unique key, the schema layer allows each entry to contain multiple
          545  +columns.  The schema layer also allows indices and non-unique key values.
          546  +</p>
          547  +
          548  +<p>
          549  +The schema layer implements two separate data storage abstractions:
          550  +tables and indices.  Each table and each index uses its own b-tree
          551  +but they use the b-tree capabilities in different ways.  For a table,
          552  +the b-tree key is a unique 4-byte integer and the b-tree data is the
          553  +content of the table row, encoded so that columns can be separately
          554  +extracted.  For indices, the b-tree key varies in size depending on the
          555  +size of the fields being indexed and the b-tree data is empty.
          556  +</p>
          557  +
          558  +<h3>4.1 &nbsp; SQL Table Implementation Details</h3>
          559  +
          560  +<p>Each row of an SQL table is stored in a single b-tree entry.
          561  +The b-tree key is a 4-byte big-endian integer that is the ROWID
          562  +or INTEGER PRIMARY KEY for that table row.
          563  +The key is stored in a big-endian format so
          564  +that keys will sort in numerical order using memcmp() function.</p>
          565  +
          566  +<p>The content of a table row is stored in the data portion of
          567  +the corresponding b-tree table.  The content is encoded to allow
          568  +individual columns of the row to be extracted as necessary.  Assuming
          569  +that the table has N columns, the content is encoded as N+1 offsets
          570  +followed by N column values, as follows:
          571  +</p>
          572  +
          573  +<blockquote>
          574  +<table border=1 cellspacing=0 cellpadding=5>
          575  +<tr>
          576  +<td>offset 0</td>
          577  +<td>offset 1</td>
          578  +<td><b>...</b></td>
          579  +<td>offset N-1</td>
          580  +<td>offset N</td>
          581  +<td>value 0</td>
          582  +<td>value 1</td>
          583  +<td><b>...</b></td>
          584  +<td>value N-1</td>
          585  +</tr>
          586  +</table>
          587  +</blockquote>
          588  +
          589  +<p>
          590  +The offsets can be either 8-bit, 16-bit, or 24-bit integers depending
          591  +on how much data is to be stored.  If the total size of the content
          592  +is less than 256 bytes then 8-bit offsets are used.  If the total size
          593  +of the b-tree data is less than 65536 then 16-bit offsets are used.
          594  +24-bit offsets are used otherwise.  Offsets are always little-endian,
          595  +which means that the least significant byte occurs first.
          596  +</p>
          597  +
          598  +<p>
          599  +Data is stored as a nul-terminated string.  Any empty string consists
          600  +of just the nul terminator.  A NULL value is an empty string with no
          601  +nul-terminator.  Thus a NULL value occupies zero bytes and an empty string
          602  +occupies 1 byte.
          603  +</p>
          604  +
          605  +<p>
          606  +Column values are stored in the order that they appear in the CREATE TABLE
          607  +statement.  The offsets at the beginning of the record contain the
          608  +byte index of the corresponding column value.  Thus, Offset 0 contains
          609  +the byte index for Value 0, Offset 1 contains the byte offset
          610  +of Value 1, and so forth.  The number of bytes in a column value can
          611  +always be found by subtracting offsets.  This allows NULLs to be
          612  +recovered from the record unabiguously.
          613  +</p>
          614  +
          615  +<p>
          616  +Most columns are stored in the b-tree data as described above.
          617  +The one exception is column that has type INTEGER PRIMARY KEY.
          618  +INTEGER PRIMARY KEY columns correspond to the 4-byte b-tree key.
          619  +When an SQL statement attempts to read the INTEGER PRIMARY KEY,
          620  +the 4-byte b-tree key is read rather than information out of the
          621  +b-tree data.  But there is still an Offset associated with the
          622  +INTEGER PRIMARY KEY, just like any other column.  But the Value
          623  +associated with that offset is always NULL.
          624  +</p>
          625  +
          626  +<h3>4.2 &nbsp; SQL Index Implementation Details</h3>
          627  +
          628  +<p>
          629  +SQL indices are implement using a b-tree in which the key is used
          630  +but the data is always empty.  The purpose of an index is to map
          631  +one or more column values into the ROWID for the table entry that
          632  +contains those column values.
          633  +</p>
          634  +
          635  +<p>
          636  +Each b-tree in an index consists of one or more column values followed
          637  +by a 4-byte ROWID.  Each column value is nul-terminated (even NULL values)
          638  +and begins with a single character that indicates the datatype for that
          639  +column value.  Only three datatypes are supported: NULL, Number, and
          640  +Text.  NULL values are encoded as the character 'a' followed by the
          641  +nul terminator.  Numbers are encoded as the character 'b' followed by
          642  +a string that has been crafted so that sorting the string using memcmp()
          643  +will sort the corresponding numbers in numerical order.  (See the
          644  +sqliteRealToSortable() function in util.c of the SQLite sources for
          645  +additional information on this encoding.)  Numbers are also nul-terminated.
          646  +Text values consists of the character 'c' followed by a copy of the
          647  +text string and a nul-terminator.  These encoding rules result in
          648  +NULLs being sorted first, followed by numerical values in numerical
          649  +order, followed by text values in lexigraphical order.
          650  +</p>
          651  +
          652  +<h3>4.4 &nbsp; SQL Schema Storage And Root B-Tree Page Numbers</h3>
          653  +
          654  +<p>
          655  +The database schema is stored in the database in a special tabled named
          656  +"sqlite_master" and which always has a root b-tree page number of 2.
          657  +This table contains the original CREATE TABLE,
          658  +CREATE INDEX, CREATE VIEW, and CREATE TRIGGER statements used to define
          659  +the database to begin with.  Whenever an SQLite database is opened,
          660  +the sqlite_master table is scanned from beginning to end and 
          661  +all the original CREATE statements are played back through the parser
          662  +in order to reconstruct an in-memory representation of the database
          663  +schema for use in subsequent command parsing.  For each CREATE TABLE
          664  +and CREATE INDEX statement, the root page number for the corresponding
          665  +b-tree is also recorded in the sqlite_master table so that SQLite will
          666  +know where to look for the appropriate b-tree.
          667  +</p>
          668  +
          669  +<p>
          670  +SQLite users can query the sqlite_master table just like any other table
          671  +in the database.  But the sqlite_master table cannot be directly written.
          672  +The sqlite_master table is automatically updated in response to CREATE
          673  +and DROP statements but it cannot be changed using INSERT, UPDATE, or
          674  +DELETE statements as that would risk corrupting the database.
          675  +</p>
          676  +
          677  +<p>
          678  +SQLite stores temporary tables and indices in a separate
          679  +file from the main database file.  The temporary table database file
          680  +is the same structure as the main database file.  The schema table
          681  +for the temporary tables is stored on page 2 just as in the main
          682  +database.  But the schema table for the temporary database named
          683  +"sqlite_temp_master" instead of "sqlite_master".  Other than the
          684  +name change, it works exactly the same.
          685  +</p>
          686  +
          687  +<h3>4.4 &nbsp; Schema Version Numbering And Other Meta-Information</h3>
          688  +
          689  +<p>
          690  +The four 32-bit integers that are stored beginning at byte offset
          691  +60 of Page 1 in the b-tree layer are passed up into the schema layer
          692  +and used for versioning and configuration information.  The meaning
          693  +of these four integers is as follows:
          694  +</p>
          695  +
          696  +<ol>
          697  +<li>The schema version number</li>
          698  +<li>The format version number</li>
          699  +<li>The recommended pager cache size</li>
          700  +<li>Unused</li>
          701  +</ol>
          702  +
          703  +<p>
          704  +The first meta-value, the schema version number, is used to detect when
          705  +the schema of the database is changed by a CREATE or DROP statement.
          706  +Recall that when a database is first opened the sqlite_master table is
          707  +scanned and an internal representation of the tables, indices, views,
          708  +and triggers for the database is built in memory.  This internal
          709  +representation is used for all subsequent SQL command parsing and
          710  +execution.  But what if another process were to change the schema
          711  +by adding or removing a table, index, view, or trigger?  If the original
          712  +process were to continue using the old schema, it could potentially
          713  +corrupt the database by writing to a table that no longer exists.
          714  +To avoid this problem, the schema version number is changed whenever
          715  +a CREATE or DROP statement is executed.  Before each command is
          716  +executed, the current schema version number for the database file
          717  +is compared against the schema version number from when the sqlite_master
          718  +table was last read.  If those numbers are different, the internal
          719  +schema representation is erased and the sqlite_master table is reread
          720  +to reconstruct the internal schema representation.
          721  +(Calls to sqlite_exec() generally return SQLITE_SCHEMA when this happens.)
          722  +</p>
          723  +
          724  +<p>
          725  +The second meta-value is the schema format version number.  This
          726  +number tells what version of the schema layer should be used to
          727  +interpret the file.  There have been changes to the schema layer
          728  +over time and this number is used to detect when an older database
          729  +file is being processed by a newer version of the library.
          730  +As of this writing (SQLite version 2.7.0) the current format version
          731  +is "4".
          732  +</p>
          733  +
          734  +<p>
          735  +The third meta-value is the recommended pager cache size as set 
          736  +by the DEFAULT_CACHE_SIZE pragma.  If the value is positive it
          737  +means that synchronous behavior is enable (via the DEFAULT_SYNCHRONOUS
          738  +pragma) and if negative it means that synchronous behavior is
          739  +disabled.
          740  +</p>
          741  +
          742  +<p>
          743  +The fourth meta-value is currently unused.
          744  +</p>
          745  +
          746  +}
          747  +
          748  +puts {
          749  +<p><hr /></p>
          750  +<p><a href="index.html"><img src="/goback.jpg" border=0 />
          751  +Back to the SQLite Home Page</a>
          752  +</p>
          753  +
          754  +</body></html>}

Changes to www/index.tcl.

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

Changes to www/lang.tcl.

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

Changes to www/quickstart.tcl.

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