<title>The Design Of SQLite4</title>
<nowiki>
<h1>1.0 Executive Summary</h1>
<ol>
<li> SQLite4 is a compact, self-contained, zero-adminstration,
ACID database engine in a
library, just like SQLite3, but with an improved interface and file
format.
<li> The run-time environment is encapsulated in an object.
<li> A greatly simplified Key/Value storage engine is used:
<ol type="a">
<li> A single large key space - not separate key spaces for each table
and index as in SQLite3.
<li> Keys sort in lexicographical order.
<li> Multiple storage engines, interchangeable at run-time.
<li> Default on-disk storage engine uses a log-structured merge database.
</ol>
<li> The PRIMARY KEY of a table really is used as the key to the storage
engine.
<li> Decimal arithmetic is used.
<li> Foreign key constraints and recursive triggers are on by default.
<li> Covering indices can be declared explicitly.
</ol>
<h1>2.0 Overview</h1>
<p>SQLite4 is an alternative, not a replacement, for SQLite3.
SQLite3 is not going away.
SQLite3 and SQLite4 will be supported in parallel.
The SQLite3 legacy will not be abandoned.
SQLite3 will continue to be maintained and improved.
But designers of new systems will now have the option to select
SQLite4 instead of SQLite3 if desired.
</p>
<p>SQLite4 strives to keep the best features of SQLite3 while addressing
issues with SQLite3 that can not be fixed without breaking compatibility.
Among the features that will remain in common between SQLite3 that SQLite4
are:
<ol>
<li><p>SQLite4 is a complete, relational, transactional, ACID, SQL database
database engine contained in a compact library that links into a larger
application. There is no server. I/O is direct to disk.</p></li>
<li><p>The source code for SQLite4 is available to anyone for any
purpose. There are no restrictions on copying, distribution, or
publication of sources or compiled binaries. There are no viral licenses
to worry over.</p></li>
<li><p>Dynamic typing is used, rather than the rigid static typing of
most other SQL database engines.</p></li>
<li><p>The (default) on-disk database image is a single disk file with a
well-documented and stable file format, making the SQLite4 library suitable
for use as an application file format.</p></li>
<li><p>SQLite4 will be fast and reliable and require no administrator
attention. It just works.</p></li>
<li><p>The implementation of SQLite4 has minimal external dependencies
so that it is easy to incorporate into embedded systems or other
unusual runtime environments.</p></li>
</ol>
<p>The implementation is still in C, which we regard as the universal
assembly language. However, SQLite4 will use more C99 features than
did SQLite3, while still striving to maintain compatibility with
popular compilers. SQLite4 makes use of standard data types
such as <tt>size_t</tt>, <tt>int64_t</tt>, <tt>uint64_t</tt>, and others.
</p>
<p>The programming interface for SQLite4 is similar to that of SQLite3,
though with all name prefixes changed from "<tt>sqlite3_</tt>"
to "<tt>sqlite4_</tt>". Legacy and deprecated interfaces in SQLite3 have
been removed from SQLite4. There are additional parameters to some routines,
and on occasion the parameters are altered slightly or reordered. Some of
the interface names have been modified to be more rational. But on the
whole, the programming interface for SQLite4 is remarkably similar to
SQLite3, and porting an application from SQLite3 to SQLite4 will usually
involve only an hour or two search-and-replace.</p>
<p>SQLite3 and SQLite4 share no symbols in common, so it is possible to
link both SQLite3 and SQLite4 into the same process and use them both at
the same time.</p>
<h1>3.0 Key Changes In SQLite4</h1>
<h2>3.1 The Run-Time Environment Object</h2>
<p>Some of the interfaces in SQLite4 take a new (added) first parameter
which is a pointer to an <tt>sqlite4_env</tt>
object that defines the run-time environment.
Examples of routines that require the sqlite4_env pointer include:
<ul>
<li> <tt>sqlite4_open()</tt>
<li> <tt>sqlite4_malloc()</tt>, <tt>sqlite4_realloc()</tt>, and
<tt>sqlite4_free()</tt>
<li> <tt>sqlite4_mprintf()</tt>
<li> <tt>sqlite4_random()</tt>
<li> <tt>sqlite4_config()</tt>
</ul>
<p>An instance of an sqlite4_env object defines how SQLite4 interacts with
the rest of the system. An sqlite4_env object includes methods to:
<ul>
<li> allocate, enter, leave, and deallocate mutexes,
<li> allocate, resize, and free heap memory,
<li> access and control the underlying key/value storage engines,
<li> initialize the built-in PRNG with a high-quality random seed,
<li> acquire the current time and date and the local timezone,
<li> record error log message.
</ul>
<p>Builds of SQLite4 for standard platforms (windows and unix) contain a global
sqlite4_env object that is normally appropriate for use on that platform.
Any interface routine that requires a pointer to an sqlite4_env object
will use the global default sqlite4_env object if a NULL pointer is passed
on its sqlite4_env parameter. Most applications never need to pass anything
other than this NULL pointer. However, some applications may want to have two
or more instances of SQLite4 running in the same address space where each
instance uses different mutex primitives, a different heap, different
date/time functions, etc. SQLite4 accommodates this need by allowing each
database instance to be created using a different sqlite4_env object.
The sqlite4_env object also eliminates all use of global and static variables
in SQLite4, making SQLite4 easier to port to some embedded systems
with limited support for static or global data.
<h2>3.2 Simplified Key/Value Storage Engine</h2>
<p>SQLite4 uses a key/value storage engine which has a greatly simplified
interface relative to SQLite3. The storage engine is pluggable; it can
be changed out at runtime by making appropriate alterations to the
sqlite4_env object prior to opening a new database connection.
<p>SQLite4 needs a storage engine that implements ordered key/value pairs
where the key and value are arbitrary-length binary data. Keys must be
unique and must be ordered lexicographically. In other words, the
keys should be ordered according to a comparison function like the following:
<blockquote><pre>
int key_compare(const void *key1, int n1, const void *key2, int n2){
int c = memcmp(key1, key2, n1<n2 ? n1 : n2);
if( c==0 ) c = n1 - n2;
return c;
}
</pre></blockquote>
<p>SQLite4 needs to be able to seek to the nearest key for a given probe
key, then step through keys in lexicographical order in either the
ascending or the descending direction. Inserts with a preexisting key
overwrite the old data. Transactions, including atomic commit and
rollback, are the responsibility of the storage engine.
<p>SQLite4 stores all content, from all tables and all indices, in a
single keyspace. This contrasts with SQLite3 that required a separate
keyspace for each table and each index. The SQLite4 storage also differs
from SQLite3 in that it requires the storage engine to sort keys is
lexicographical order, whereas SQLite3 uses a very complex comparison
function to determine the record storage order.
<p>SQLite4 communicates with the storage engine through a well-defined
and simple interface. New storage engines can be substituted at runtime
simply by replacing some pointers to functions inside the sqlite4_env
object prior to allocating a database connection.
<p>If an alternative storage engine does not support rollback, that just
means that ROLLBACK will not work in SQLite4. If the alternative storage
engine does not support nested transactions, that means that nested
transactions will not work on SQLite4. So less-capable storage engines
can be plugged into SQLite4, though with a corresponding reduction in
the capability of the overall system.
<p>The default built-in storage engine is a log-structured merge database.
It is very fast, faster than LevelDB, supports nested transactions, and
stores all content in a single disk file. Future versions of SQLite4 might
also include a built-in B-Tree storage engine.
<h2>3.3 The PRIMARY KEY Is The Real Primary Key</h2>
<p>SQLite3 allows one to declare any column or columns of a table to be
the primary key. But internally, SQLite3 simply treats that PRIMARY KEY
as a UNIQUE constraint. The actual key used for storage in SQLite is the
rowid associated with each row.
<p>SQLite4, on the other hand, actually uses the declared PRIMARY KEY
of a table (or, more precisely, an encoding of the PRIMARY KEY value)
as the key into the storage engine. SQLite4 tables do not normally have
a rowid (unless the table has no PRIMARY KEY in which case a rowid is
created to be the implicit primary key.) That means that content is stored
on disk in PRIMARY KEY order. It also means that records can be located
in the main table using just a single search on the PRIMARY KEY fields.
In SQLite3, a search on the primary key means doing a search on an
automatic index created for that primary key to find the rowid,
then doing a second search on the main table using the rowid.
<p>SQLite4 requires all elements of the PRIMARY KEY to be non-null.
This is an SQL standard. Due to an oversight in early versions,
SQLite3 does not enforce the NOT NULL constraint on PRIMARY KEY columns
since by the time the omission was discovered SQLite3 was in such
widespread use, activation of NOT NULL enforcement would have broken
too many programs.
<h2>3.4 Decimal Math</h2>
<p>SQLite4 does all numeric computations using decimal arithmetic.
SQLite4 never uses C datatypes <tt>double</tt> or <tt>float</tt>
(except in interface routines when converting between double and
the internal decimal representation). Instead, all numeric values
are represented internally as an 18-digit decimal number with a
3-digit base-10 exponent. Features of this represention include:
<ul>
<li> It works reliably and consistently even on platforms that lack
support for IEEE 754 binary64 floating point numbers.
<li> Currency computations can normally be done exactly and without rounding.
<li> Any signed or unsigned 64-bit integer can be represented exactly.
<li> The floating point range and accuracy exceed that of IEEE 754 binary64
floating point numbers.
<li> Positive and negative infinity and NaN (Not-a-Number) have well-defined
representations.
</ul>
<p>SQLite4 makes no distinction between integer and floating point numbers.
It does, however, distinguish between exact and approximate numbers. In
C/C++, integers are exact and floating point numbers are approximate.
But this is not necessarily true of SQLite4. Floating point numbers can
be exact in SQLite4. Integers that can be represented in 64-bits are
always exact in SQLite4 but larger integers might be approximate.
<p>The SQLite4 numeric format is for internal use. Numbers can be translated
between integers or doubles for input and output. The on-disk storage
space required for SQLite4 numbers varies from between 1 and 12 bytes,
depending on the magnitude and the number of significant digits in the
value.
<h2>3.5 Foreign Key Constraints And Recursive Triggers Are On By Default</h2>
<p>Foreign key constraints were not available in early versions of SQLite3
and so they default off for backwards compatibility. But foreign key
constraints are always available and default on in SQLite4. All foreign
key constraints are deferred by default, though they can be declared to
be immediate when created. There is no mechanism available to toggle
foreign key constraints between deferred and immediate.
<p>SQLite3 supports recursive triggers, but only if a run-time option is
available. In SQLite4, all triggers are recursive all the time.</p>
<h2>3.6 Explicit Index Cover</h2>
<p>SQLite4 adds an optional clause to CREATE TABLE statements that
defines extra columns of information that are duplicated in the index.
This allows the application developer to explicitly create covering indices
in SQLite4 without resorting to trickery and subterfuge. For example:
<blockquote><pre>
CREATE INDEX cover1 ON table1(a,b) COVERING(c,d);
</pre></blockquote>
<p>The cover1 index above allows queries of the form
"SELECT c,d FROM table1 WHERE a=?1 AND b=?2" to be executed
with only a single lookup into the storage engine. Without the extra
COVERING clause on the index, SQLite4 would have to do two lookups in the
storage engine; one to find the primary key based on the values of a and b
and a second to find the values of c and d based on the primary key. The
COVERING clause makes the values of c and d available in the index, which
means that they can be extracted directly from the index without a second
lookup.
<p>A variation on the COVERING clause is:
<blockquote><pre>
CREATE INDEX cover2 ON table(x,y) COVERING ALL;
</pre></blockquote>
<p>The COVERING ALL clause means that all columns of the table are duplicated
in the index which guarantees that the original table will never have to
be consulted to complete a query that uses the index. The downside of this,
of course, is that information is duplicated and so the database file is
larger. But by providing an explicit COVERING close on indices, SQLite4
allows application developers to make space versus time tradeoffs that
are appropriate for their application.