Documentation Source Text

<title>SQLite System Requirements</title>
<tcl>
unset -nocomplain sysreq_list
proc sysreq {id derivedfrom explaination text} {
  global sysreq_list
  lappend sysreq_list $id $derivedfrom $text
  hd_fragment $id $id
  if {[string length $explaination]} {
    hd_resolve "<p>$explaination</p>"
  }
  hd_puts "<blockquote><b>$id:</b>"
  hd_resolve $text
  hd_puts {</b></blockquote}
}
</tcl>

<h1>System Requirements For SQLite</h1>

<p>This document outlines the high-level objectives of the SQLite
library.
All of the features and capabilities of SQLite can be traced back
to one of the requirements specified here.</p>

<p>SQLite is not a complete system and so the requirements
provided by this document are not system requirements in the
strict sense.  Perhaps "sub-system requirements" would be a better
term, since SQLite is always a sub-component of a larger system.</p>

<p>This document is targeted primarily at developers who are
working within a waterfall development model that uses detailed
requirements using the modal auxiliary verb "shall".  
However, this document is also useful as a general-purpose high-level 
description of SQLite for developers who are not working under
different development paradigms.</p>

<h2>1.0 SQLite is a translator from SQL into low-level disk I/O</h2>

<tcl>
sysreq S10000 {} {
  SQLite is an SQL database engine.  And the fundamental task of
  every SQL database engine it to translate the abstract SQL statements
  readily understood by humans, into sequences of I/O operations readily
  understood by computer hardware.
} {
  The SQLite library shall translate high-level SQL statements into
  low-level I/O calls to persistent storage.
}

sysreq S10100 S10000 {
  SQL is one of the worlds most widely known programming languages,
  but it is also one of the most ill-defined.  There are various SQL
  standards documents available.  But the SQL standards documents are 
  obtuse to the point of being incomprehensible.  And the standards 
  allow for so much "implementation defined" behavior that there exist
  two SQL database engines understand exactly the same language.</p>
  
  <p>SQLite does not attempt to obtain strict compliance with any
  one of the various SQL standards.
  Instead, SQLite tries to be as compatible as possible with other SQL
  database engines.  SQLite attempts to operate on the principle of
  least surprise.  That is to say, experienced SQL programmers should
  find SQLite's dialect intuitive and natural.</p>
  
  <p>SQLite may omit some obscure features of SQLite.  And the SQL
  dialect that SQLite understands might contain some enhancements not
  found in some standards documents.  Nevertheless, applications
  written for other SQL database engines should be portable to SQLite
  with little to no change.  And programmers writing code for SQLite
  should not encounter anything unexpected.
} {
  The SQLite library shall accepts a well-defined dialect of SQL
  that conforms to published SQL standards.
}

sysreq S10110 S10100 {
  SQLite is able to hold content in various datatypes and sizes.
} {
  The SQLite library shall support BLOB, CLOB, integer, and floating-point
  datatypes.
}

sysreq S10120 S10100 {
  SQLite handles NULLs in accordance with SQL
  standards and in a manner that is compatible with SQL standards.
} {
  The SQLite library shall implement the standard SQL interpretation
  of NULL values.
}


sysreq S10200 S10000 {
  Most other database
  engines implement a client/server model in which a small client library
  is linked with the application and the client communicates with a separate
  server process using interprocess communication (IPC).  SQLite avoids
  the complication of having a separate server process by doing direct
  I/O directly to the underlying filesystem.
} {  
  The SQLite library shall communicate directly with database files
  in persistent storage.
}


sysreq S10300 S10000 {
  In the database world, "ACID" is an acronym for Atomic, Consistent,
  Isolated, and Durable.  Atomic means that a change to the database
  happens either entirely or not at all.  Consistent means that if the
  database file is well-formed before the start of a transaction then
  it is guaranteed to be well-formed after the transaction commits.  
  Isolated means that when two or more threads are
  processes are working with the same database, uncommitted changes 
  made by one are not visible to the other.  Durable means that once
  a transaction commits, it stays committed even if there is a subsequent
  software crash or power failure.
} {
  The SQLite library shall implement ACID transactions.
}


sysreq S10500 S10000 {
  An operating system crash or an unexpected power loss can
  sometimes damage
  the underlying persistent storage in ways that no software can defend
  against.  (For example, the content of a disk drive might be completely
  erased and become unrecoverable.)
  Nevertheless, software can take steps to defend against the kinds
  of damage that typically occurs following operating system crashes and 
  power failures.  The usual damage is that some writes are missing
  or incomplete and that writes have occurred out of order.  We say
  that software is "robust" if it defines against the common kinds of
  damage seen following an operating system crash or power loss.</p>
} {
  The SQLite library shall implement transactions that are robust
  across application crashes, operating-system crashes, and power
  failures.
}


sysreq S10600 S10000 {
  Many applications benefit from being about to access multiple
  database file using the same database connection, so that 
  information can be transfered from from one database to another 
  atomically, or so that queries can join data across multiple
  databases.
} {  
  The SQLite library shall support simultaneous access to multiple
  database files on the same database connection.
}

</tcl>
<h2>2.0 SQLite is designed to be extensible by the application</h2>
<tcl>

sysreq S20000 {} {
  SQLite is intended to be an embedded database that functions well
  in resource-limited systems.  For that reason we desire to keep the
  size of the library small.  That choices argues against a large
  default function set.  Instead of having many built-in features, SQLite is
  designed to be extensible at compile-time and run-time with new
  application-defined functions and behaviors.
} {
  The SQLite library shall be extensible and configurable.
}

sysreq S20100 S20000 {
  SQLite works on common workstations and in embedded systems.
  Sometimes these devices, particularly embedded systems,
  have odd and unusual operating systems.  In order to support
  this level of portability, SQLite allows the interface to the operating
  system to be defined at run-time.
} {
  The SQLite library shall provide interfaces that permit the application
  to override operating-system interfaces.
}
sysreq S20110 S20100 {} {
  The SQLite library shall provide interfaces that permit the application
  to override the interfaces used to read and write persistent storage.
}
sysreq S20120 S20100 {} {
  The SQLite library shall provide interfaces that permit the application
  to override the interfaces used for memory allocation.
}
sysreq S20130 S20100 {} {
  The SQLite library shall provide interfaces that permit the application
  to override the interfaces used for controlling mutexes.
}

sysreq S20200 S20000 {
  Most SQL database engines support a rich set of SQL functions.
  SQLite, in contrast, supports only a select few SQL functions.
  But SQLite makes up for its dearth of built-in SQL functions by 
  allowing the application to create new SQL function easily.
} {
  The SQLite library shall provide interfaces that permit the application
  to create new SQL functions.
}


sysreq S20300 S20000 {
  By default, SQLite only understands ASCII text.  The tables needed
  to do proper comparisons and case folding
  of full unicode text are huge - much larger
  than the SQLite library itself.  And, any application that is dealing
  with unicode already probably already has those tables built in.  For
  SQLite to include unicode comparison tables would be redundant and wasteful.
  As a compromise, SQLite allows the application to specify alternative
  collating sequences for things such as unicode text,
  so that for applications that need such comparison sequences can have
  them easily while other applications that are content with ASCII are
  not burdened with unnecessary tables.
} {
  The SQLite library shall provide interfaces that permit the application
  to create new text collating sequences.
}

sysreq S20400 S20000 {
  A virtual table is an SQL object that appears to be an ordinary
  SQL table for the purposes of INSERT, UPDATE, DELETE, and SELECT statements.
  But instead of being backed by persistent storage, the virtual table is
  an object that responds programmatically to INSERT, UPDATE, DELETE, and
  SELECT requests.  Virtual tables have been used to implement full-text
  search and R-Tree indices, among other things.
} {
  The SQLite library shall provide interfaces that permit the application
  to create new classes of virtual SQL tables.
}

sysreq S20500 S20000 {
  Some applications choose to package extensions in separate
  shared library files and load those extensions at run-time on
  an as-needed basis.  Depending on the nature of the application,
  this can be an aid to configuration management, since it allows
  the extension to be updated without having to replace the core
  application.
} {
  The SQLite library shall provide interfaces that permit the application
  to load extensions at run-time using shared libraries.
}

sysreq S20600 S20000 {
  SQLite has finite limits.  For example, there is a maximum size BLOB
  or CLOB that SQLite will store, a maximum size to a database file,
  a maximum number of columns in a table or query, and a maximum depth
  of an expression parse tree.  All of these have default values that
  are sufficiently large that a typical application is very unlikely to 
  ever reach the limits.  But some applications (for example, applications
  that process content from untrusted and possibly hostile sources)
  might want to define much lower limits on some database connections for
  the purpose of preventing denial-of-service attacks.  Or, an application
  might want to select much lower limits in order to prevent over-utilization
  of limited resources on an embedded device.  Whatever the rationale, SQLite
  permits limits to be queried and set at run-time.
} {
  The SQLite library shall provide interfaces that permit the application
  to dynamically query and modify size limits.
}

</tcl>
<h2>3.0 SQLite is lightweight and leak-free</h2>
</tcl>

sysreq S30000 {} {
  SQLite is designed to work well within embedded devices with 
  very limited resources.  To this end, it expects to confront situations
  where memory is unavailable and where I/O operations fail and it is designed
  to handle such situations with ease and grace.  SQLite also avoids aggravating
  low-resource situations by correctly freeing rather than leaking 
  resources it uses itself.
} {
  The SQLite library shall be safe for use in long-running,
  low-resource, high-reliability applications.
}

sysreq S30100 S30000 {
  A "Proper shutdown" means that all resources that the application
  has allocated from SQLite have been released by the application.
  The leak-free operation guarantee of SQLite applies even if there
  have been memory allocation errors or I/O errors during operation.
} {
  The SQLite library shall release all system resources it holds
  when it is properly shutdown.
}

sysreq S30200 S30000 {
  Safety-critical systems typically disallow the use of malloc() and
  free() because one never knows when they might fail due to memory
  fragmentation.  However, SQLite makes extensive use of dynamic objects
  and so it must be able to allocate and deallocate memory
  to hold those objects.</p>
  
  <p>In order to be acceptable for use in safety critical systems,
  SQLite can be configured to use its own internal memory allocator
  which, subject to proper usage by the application, guarantees that
  memory allocation will never fail either due to memory fragmentation
  or any other cause.  The proof of correctness is due to J. M. Robson:
  "Bounds for Some Functions Concerning Dynamic Storage Allocations",
  Journal of the ACM, Volume 21, Number 3, July 1974.</p>
  
  <p>The internal memory allocator is seeded with a large contiguous
  block of memory at application start.  SQLite makes all of its
  internal memory allocations from this initial seed.  
  The Robson proof depends on SQLite being coupled to a well-behaved
  application.  The application must not try to use more than a
  precomputed fraction of the available memory - that fraction depending
  on the size ratio between the largest and smallest memory allocations.
  Additional details are provided elsewhere.
  For the purposes of this document, it is sufficient
  to state:
} {
  The SQLite library shall be configurable so that it is guaranteed
  to never fail a memory allocation as long as the application does
  not request resources in excess of reasonable and published limits.
}

sysreq S30210 S30200 {
  To help insure that an
  application never fails a memory allocation call, SQLite provides
  interfaces that can inform the application if its memory usage
  is growing close to or has exceeded the critical Robson limits.  
  In practice, the memory used by an application can exceed the 
  limits of the Robson proof by a wide margin with no harmful effect.  
  There is plenty of safety margin.  But the Robson proof does break 
  down once the limits are exceeded  
  and the guarantee that no memory allocation will fail is lost.  Hence
  it is important to be able to track how close an application has come
  to reaching critical limits.
} {
  The SQLite library shall be provide instrumentation that can alert
  the application when its resource usages nears or exceeds the limits
  of the memory breakdown guarantee.
}

sysreq S30220 S30200 {
  When SQLite comes under memory pressure, it can be configured to
  recycle memory from one use to another, thus helping to reduce the
  pressure.  "Memory pressure" means that memory available for 
  allocation is becoming less plentiful.  In a safety-critical application,
  memory pressure might mean that the amount of allocated memory is
  getting close to the point where the Robson proof
  breaks down.  On a workstation, memory pressure might mean that
  available virtual memory is running low.
} {
  The SQLite library shall be provide facilities to automatically
  recycle memory when usage nears preset limits.
}

sysreq S30230 S30200 {
  SQLite provides the ability to read and write megabyte
  or gigabyte blobs and text strings without having to allocate
  enough memory to hold the entire blob and string in memory all
  at once.  This enables SQLite to read and write BLOBs that
  are actually larger than the available memory on the device.
  It also helps reduce the size of the maximum memory allocation
  which helps keep memory usage below Robson limits and thus helps
  to guarantee failure-free memory allocation.
} {
  The SQLite library shall be permit BLOB and CLOB objects to be
  read and written incrementally using small memory buffers.
}

sysreq S30300 S30000 {
  Memory allocation problems do not cause SQLite to fail
  catastrophically.
  SQLite recognizes all memory allocation failures and either works 
  around them, or
  cleanly aborts what it is doing and returns to the application
  with an error that indicates insufficient memory was available.
  Assuming new memory becomes available, SQLite is able to continue 
  operating normally after a memory allocation failure.
} {
  When a memory allocation fails, SQLite shall either silently make
  due without the requested memory or else it shall report the error
  back to the application.
}

sysreq S30400 S30000 {
  SQLite responses sanely to disk I/O errors.  If it is unable
  to work around the problem, SQLite might have to report the error
  back up to the application.  In either case, SQLite is able to
  continue functioning, assuming of course that the I/O error was
  transient.
} {
  When a I/O operation fails, SQLite shall either silently 
  recover or else it shall report the error
  back to the application.
}

sysreq S30500 S30000 {
  SQLite is able to cleanly abort an operation in progress and
  afterwards continue functioning normally without any memory or
  other resource leaks.  An example of where this functionality is
  used occurs in the command-line interface (CLI) program for SQLite.
  If the user enters a query that has millions of result rows, those
  rows begin pouring out onto the screen.  The operator can then
  hit the interrupt key sequence (which varies from one operating
  system to another but it often Control-C) which causes the query
  to be aborted.
} {
  SQLite shall provide the capability to monitor
  the progress and interrupt the evaluation of a long-running query.
}

sysreq S30600 S30000 {
  When information is deleted from an SQLite database, the default
  action is for SQLite to mark the space as unused and then to reuse
  the space at the next opportune INSERT.  On devices where persistent 
  storage is scarce, however, it is sometime desirable to return the
  unused space back to the operating system.  SQLite supports this.
} {
  All unused portions of a well-formed SQLite database file shall
  be available for reuse.
}
sysreq S30700 S30000 {} {
  SQLite shall provide the capability to incrementally decrease the
  size of the persistent storage file as information is removed from
  the database.
}

sysreq S30800 S30000 {
  In consumer-grade software, it is often acceptable to run tests
  on an instrumented version of the code.  But for high-reliability
  systems, it is better to test the code exactly as it is deployed.
  The saying at NASA is "test what you fly and fly what you test."
  In support of this goal, SQLite includes interfaces whose only purpose
  is to observe internal state and to place SQLite into internal states
  for the testing.
} {
  SQLite shall provide the interfaces that support testing and
  validation of the library code in an as-delivered configuration.
}

sysreq S30900 S30000 {
  On resource-constrained devices, it is desirable to get double-duty
  out of resources where possible.
} {
  SQLite shall provide the ability for separate database connections
  within the same process to share resources.
}

</tcl>
<h2>4.0 SQLite is safe to use in multi-thread and multi-process
applications.</h2>
<tcl>

sysreq S40000 {} {
  In nearly all modern digital systems, there are many things happening
  at once.  And many of those things involve SQLite.
} {
  The SQLite library shall be safe for use in applications that
  make concurrent access to the underlying database from different
  threads and/or processes.
}

sysreq S40100 S40000 {
  The developers of SQLite believe that "thread-safe" is a
  self contradiction.  No application that includes multiple threads
  of control within the same address space is every truly "safe".
  And yet it is recognized that many developers want to
  create multithreaded applications and to use SQLite in those
  applications.  Therefore, SQLite is engineered to be "thread-safe".
} {
  The SQLite library shall be configurable to operate correctly in
  a multi-threaded application.
}

sysreq S40200 S40000 {
  Multiple database connections can be created and operated
  independently within the same thread or process.
} {
  The SQLite library shall support multiple independent database
  connections per thread and per process.
}

sysreq S40300 S40000 {
  SQLite uses both internal mutexes and external file locking to 
  ensure that two or more threads or processes working
  on the same database file play nicely with one another.
} {
  The SQLite library shall automatically control access to common
  databases from different connections in different threads or processes.
}
sysreq S40400 S40000 {} {
  The SQLite library shall notify the application if an operation can
  not be completed due to concurrent access constraints.
}

</tcl>
<h2>5.0 SQLite is cross-platform</h2>
<tcl>

sysreq S50000 {} {
  Cross-platform in this context means that the SQLite 
  can be used on a wide variety of operating systems and processors,
  ranging from small, special-purpose embedded systems, to workstations,
  to servers.  Platforms can be 32- or 64-bit, big-endian or little-endian.
  Cross-platform refers to the source code.  Obviously the SQLite would
  need to be recompiled in order to run on processors with different
  instruction sets.
} {
  The SQLite library shall be cross-platform.
}

sysreq S50100 S50000 {
  C has been called the "universal assembly language".
  Nearly all computer systems accept code written in C.
  Thus, to help make SQLite cross-platform:
} {
  The SQLite library shall be implemented in ANSI-C.
}

sysreq S50200 S50000 {
  SQLite stores text data as unicode.  Three separate unicode 
  representations are allowed:
} {
  The SQLite library shall support text encoded as UTF-8,
  UTF-16le, or UTF-16be.
}

sysreq S50300 S50000 {
  An SQLite database file can be freely moved between machine
  with different operating systems, different processors,
  different size integers, and different byte orders.  The same
  database file should work on any machine.
} {
  SQLite database files shall by processor and byte-order independent.
}

</tcl>
<h2>6.0 Other Features</h2>
<tcl>

sysreq S60000 {} {
  Some applications need to be able to discover characteristics of
  their environment at run-time and to make appropriate adjustments to
  their processing to accommodate the environment they find themselves in.
  SQLite attempts to support this need.
} {
  The SQLite library shall provide introspection capabilities to the
  application.
}

sysreq S60100 S60000 {
  Some applications are designed to work with different versions
  of SQLite which may or may not enable selected features.  For example,
  SQLite can be compiled to be threadsafe or not.  The threadsafe version
  works in multi-threaded applications.  The non-threadsafe build runs
  faster.  When an application is using an unknown version of SQLite
  it is important that it be able to determine the characteristics of
  the particular SQLite build it is using.
} {
  The SQLite library shall provide interfaces that an application can
  use to discover fixed, compile-time characteristics of the 
  SQLite library.
}

sysreq S60200 S60000 {
  In addition to the compile-time characteristics, SQLite allows
  the run-time settings of the library and of the underlying
  database file to be interrogated.
} {
  The SQLite library shall provide interfaces that an application can
  use to find run-time performance characteristics of the
  SQLite library.
}
sysreq S60300 S60000 {} {
  The SQLite library shall provide interfaces that permit an application
  to query the schema of a database.
}
sysreq S60400 S60000 {} {
  The SQLite library shall provide interfaces that allow an application
  to monitor sequence of queries and progress of submitted to SQLite.
}
sysreq S60500 S60000 {} {
  The SQLite library shall provide interfaces that allow an application
  to discover the algorithms that SQLite has chosen to implement specific
  SQL statements.
}

sysreq S70000 {} {
  Many applications need to be able to safely process data or
  even SQL statements that are received from untrusted sources.
  An "SQL Injection Attack" occurs when an adversary intentionally
  introduces data that is designed to have undesirable side effects
  on the database files.  For example, suppose an application generates
  an INSERT statement as follows:</p>
  
  <blockquote><pre>
  snprintf(z, n, "INSERT INTO table1 VALUES('%s')", zUserData);
  </pre></blockquote>
  
  <p>If a hostile user supplies data that reads:</p>
  
  <blockquote><pre>
  beginning'); DELETE FROM table1; INSERT INTO table1 VALUES('
  </pre></blockquote>
  
  <p>Then the constructed INSERT statement would be transformed into
  three statements, the second of which is an undesired deletion of
  all prior content from the table.  SQLite contains interfaces that
  are designed to help applications avoid SQL injection attacks and
  similar problems.
} {
  The SQLite library shall provide interfaces that promote the safe
  construction and processing of SQL statements and data from
  untrusted sources.
}

sysreq S70100 S70000 {
  Some applications (for example
  <a href="http://www.cvstrac.org/">CVSTrac</a> and
  <a href="http://www.fossil-scm.org/">Fossil</a>) will run SELECT
  statements entered by anonymous users on the internet.  Such 
  applications want to be able to guarantee that a hostile users does
  not access restricted tables (such as the password column of the user
  table) or modify the database in any way.  SQLite supports the ability
  to analyze an arbitrary SQL statement to insure that it does not
  perform undesired operations.
} {
  The SQLite library shall provide the application means by which the
  application can test and enforce compliance with database access
  policies for any particular SQL statement.
}

sysreq S70200 S70000 {
  <p>Applications such as the command-line interface (CLI) for SQLite
  will prompt the user to enter SQL statements and will evaluate those
  statements as they are entered.  But sometimes an SQL statement spans
  multiple lines.  The CLI needs to know to issue a continuation prompt
  and await additional input if the input received so far is incomplete.
  SQLite supports interfaces that allow the CLI and similar applications
  to know if the input it has gathered so far is complete or if it needs
  to await additional input before processing the SQL.
} {
  The SQLite library shall provide interfaces that test to see if an
  SQL statement being received incrementally is complete.
}

</tcl>
<h2>7.0 Summary Of System Requirements</h2>

<p>The foregoing contained both system requirements and explanatory
text interspersed.  The following is a reproduction of the system
requirements without the explanatory text, as a convenient reference.
The top-level system requirements are shown first, followed by lower-level
system requirements that provide additional detail.</p>

<h3>7.1 Top-level System Requirements</h3>

<tcl>
foreach {id derivedfrom text} $sysreq_list {
  if {[llength $derivedfrom]>0} continue
  hd_puts "<blockquote><b>$id:</b>"
  hd_resolve $text
  hd_puts </blockquote>
}
</tcl>

<h3>7.2 Derived System Requirements</h3>

<tcl>
foreach {id derivedfrom text} $sysreq_list {
  if {[llength $derivedfrom]==0} continue
  hd_puts "<blockquote><b>$id:</b>"
  hd_resolve $text
  hd_puts </blockquote>
}
</tcl>