#!/usr/bin/tclshsource common.tclheader {SQLite Version 3 Overview}puts {<h2>SQLite Version 3 Overview</h2><p>SQLite version 3.0 introduces important changes to the library, including:</p><ul><li>A more compact format for database files.</li><li>Manifest typing and BLOB support.</li><li>Support for both UTF-8 and UTF-16 text.</li><li>User-defined text collating sequences.</li><li>64-bit ROWIDs.</li><li>Improved Concurrency.</li></ul><p>This document is a quick introduction to the changes for SQLite 3.0for users who are already familiar with SQLite version 2.8.</p><h3>Naming Changes</h3><p>SQLite version 2.8 will continue to be supported with bug fixesfor the foreseeable future.  In order to allow SQLite version 2.8and SQLite version 3.0 to peacefully coexist, the names of key filesand APIs in SQLite version 3.0 have been changed to include thecharacter "3".  For example, the include file used by C programshas been changed from "sqlite.h" to "sqlite3.h".  And the name ofthe shell program used to interact with databases has been changedfrom "sqlite.exe" to "sqlite3.exe".  With these changes, it is possibleto have both SQLite 2.8 and SQLite 3.0 installed on the same system atthe same time.  And it is possible for the same C program to linkagainst both SQLite 2.8 and SQLite 3.0 at the same time and to useboth libraries at the same time.</p><h3>New File Format</h3><p>The format used by SQLite database files has been completely revised.The old version 2.1 format and the new 3.0 format are incompatible withone another.  Version 2.8 of SQLite will not read a version 3.0 databasefiles and version 3.0 of SQLite will not read a version 2.8 database file.</p><p>To convert an SQLite 2.8 database into an SQLite 3.0 database, haveready the command-line shells for both version 2.8 and 3.0.  Thenenter a command like the following:</p><blockquote><pre>sqlite OLD.DB .dump | sqlite3 NEW.DB</pre></blockquote><p>The new database file format uses B+trees for tables.  In a B+tree, alldata is stored in the leaves of the tree instead of in both the leaves andthe intermediate branch nodes.  The use of B+trees for tables allows forbetter scalability and the storage of larger data fields without the use ofoverflow pages.  Traditional B-trees are still used for indices.</p><p>The new file format also supports variable pages sizes between 512 and32768 bytes.  The size of a page is stored in the file header so thesame library can read databases with different pages sizes, in theory,though this feature has not yet been implemented in practice.</p><p>The new file format omits unused fields from its disk images.  For example,indices use only the key part of a B-tree record and not the data.  Sofor indices, the field that records the length of the data is omitted.Integer values such as the length of key and data are stored usinga variable-length encoding so that only one or two bytes are required tostore the most common cases but up to 64-bits of information can be encodedif needed. Integer and floating point data is stored on the disk in binary ratherthan being converted into ASCII as in SQLite version 2.8.These changes taken together result in database files that are typically25% to 35% smaller than the equivalent files in SQLite version 2.8.</p><p>Details of the low-level B-tree format used in SQLite version 3.0 canbe found in header comments to the <a href="http://www.sqlite.org/cvstrac/getfile/sqlite/src/btree.c">btree.c</a>source file.</p><h3>Manifest Typing and BLOB Support</h3><p>SQLite version 2.8 will deal with data in various formats internally,but when writing to the disk or interacting through its API, SQLite 2.8always converts data into ASCII text.  SQLite 3.0, in contrast, exposes its internal data representations to the user and stores binary representationsto disk when appropriate.  The exposing of non-ASCII representations wasadded in order to support BLOBs.</p><p>SQLite version 2.8 had the feature that any type of data could be storedin any table column regardless of the declared type of that column.  Thisfeature is retained in version 3.0, though in a slightly modified form.Each table column will store any type of data, though columns have anaffinity for the format of data defined by their declared datatype.When data is inserted into a column, that column will make at attemptto convert the data format into the columns declared type.   All SQLdatabase engines do this.  The difference is that SQLite 3.0 will still store the data even if a format conversion is not possible.</p><p>For example, if you have a table column declared to be of type "INTEGER"and you try to insert a string, the column will look at the text stringand see if it looks like a number.  If the string does look like a numberit is converted into a number and into an integer if the number does nothave a fractional part, and stored that way.  But if the string is nota well-formed number it is still stored as a string.  A column with atype of "TEXT" tries to convert numbers into an ASCII-Text representationbefore storing them.  But BLOBs are stored in TEXT columns as BLOBs becauseyou cannot in general convert a BLOB into text.</p><p>In most other SQL database engines the datatype is associated withthe table column that holds the data - with the data container.In SQLite 3.0, the datatype is associated with the data itself, notwith its container.<a href="http://www.paulgraham.com/">Paul Graham</a> in his book <a href="http://www.paulgraham.com/acl.html"><i>ANSI Common Lisp</i></a>calls this property "Manifest Typing".Other writers have other definitions for the term "manifest typing",so beware of confusion.  But by whatever name, that is the datatypemodel supported by SQLite 3.0.</p><p>Additional information about datatypes in SQLite version 3.0 isavailable<a href="datatype3.html">separately</a>.</p><h3>Support for UTF-8 and UTF-16</h3><p>The new API for SQLite 3.0 contains routines that accept text asboth UTF-8 and UTF-16 in the native byte order of the host machine.Each database file manages text as either UTF-8, UTF-16BE (big-endian),or UTF-16LE (little-endian).  Internally and in the disk file, thesame text representation is used everywhere.  If the text representationspecified by the database file (in the file header) does not matchthe text representation required by the interface routines, then textis converted on-the-fly.Constantly converting text from one representation to another can becomputationally expensive, so it is suggested that programmers choose asingle representation and stick with it throughout their application.</p><p>In the current implementation of SQLite, the SQL parser only workswith UTF-8 text.  So if you supply UTF-16 text it will be converted.This is just an implementation issue and there is nothing to preventfuture versions of SQLite from parsing UTF-16 encoded SQL natively.</p><p>When creating new user-defined SQL functions and collating sequences,each function or collating sequence can specify it if works withUTF-8, UTF-16be, or UTF-16le.  Separate implementations can be registeredfor each encoding.   If an SQL function or collating sequences is requiredbut a version for the current text encoding is not available, then the text is automatically converted.  As before, this conversion takescomputation time, so programmers are advised to pick a singleencoding and stick with it in order to minimize the amount of unnecessaryformat juggling.</p><p>SQLite is not particular about the text it receives and is more thanhappy to process text strings that are not normalized or evenwell-formed UTF-8 or UTF-16.  Thus, programmers who want to storeIS08859 data can do so using the UTF-8 interfaces.  As long as noattempts are made to use a UTF-16 collating sequence or SQL function,the byte sequence of the text will not be modified in any way.</p><h3>User-defined Collating Sequences</h3><p>A collating sequence is just a defined order for text.  When SQLite 3.0sorts (or uses a comparison operator like "<" or ">=") the sort orderis first determined by the data type.</p><ul><li>NULLs sort first</li><li>Numeric values sort next in numerical order</li><li>Text values come after numerics</li><li>BLOBs sort last</li></ul><p>Collating sequences are used for comparing two text strings.The collating sequence does not change the ordering of NULLs, numbers,or BLOBs, only text.</p><p>A collating sequence is implemented as a function that takes thetwo strings being compared as inputs and returns negative, zero, orpositive if the first string is less than, equal to, or greater thanthe second.SQLite 3.0 comes with a single built-in collating sequence named "BINARY"which is implemented using the memcmp() routine from the standard C library.The BINARY collating sequence works well for English text.  For otherlanguages or locales, alternative collating sequences may be preferred.</p><p>The decision of which collating sequence to use is controlled by theCOLLATE clause in SQL.  A COLLATE clause can occur on a table definition,to define a default collating sequence to a table column, or on fieldof an index, or in the ORDER BY clause of a SELECT statement.Planned enhancements to SQLite are to include standard CAST() syntaxto allow the collating sequence of an expression to be defined.</p><h3>64-bit ROWIDs</h3><p>Every row of a table has a unique rowid.If the table defines a column with the type "INTEGER PRIMARY KEY" then thatcolumn becomes an alias for the rowid.  But with or without an INTEGER PRIMARYKEY column, every row still has a rowid.</p><p>In SQLite version 3.0, the rowid is a 64-bit signed integer.This is an expansion of SQLite version 2.8 which only permittedrowids of 32-bits.</p><p>To minimize storage space, the 64-bit rowid is stored as a variable lengthinteger.  Rowids between 0 and 127 use only a single byte.  Rowids between 0 and 16383 use just 2 bytes.  Up to 2097152 uses threebytes.  And so forth.  Negative rowids are allowed but they always usenine bytes of storage and so their use is discouraged.  When rowidsare generated automatically by SQLite, they will always be non-negative.</p><h3>Improved Concurrency</h3><p>SQLite version 2.8 allowed multiple simultaneous readers or a singlewriter but not both.  SQLite version 3.0 allows one process to beginwriting the database while other processes continue to read.  Thewriter must still obtain an exclusive lock on the database for a briefinterval in order to commit its changes, but the exclusive lock is nolonger required for the entire write operation.A <a href="lockingv3.html">more detailed report</a> on the lockingbehavior of SQLite version 3.0 is available separately.</p><p>A limited form of table-level locking is now also available in SQLite.If each table is stored in a separate database file, those separatefiles can be attached to the main database (using the ATTACH command)and the combined databases will function as one.  But locks will onlybe acquired on individual files as needed.  So if you redefine "database"to mean two or more database files, then it is entirely possible fortwo processes to be writing to the same database at the same time.To further support this capability, commits of transactions involvingtwo or more ATTACHed database are now atomic.</p><h3>Credits</h3><p>SQLite version 3.0 is made possible in part by AOL developerssupporting and embracing great Open-Source Software.</p>}footer {$Id: version3.tcl,v 1.6 2006/03/03 21:39:54 drh Exp $}