Documentation Source Text

}

hd_keywords {*cintro}
HEADING 0 {An Introduction To The SQLite C/C++ Interface}
</tcl>

<p>
  This article provides an overview and roadmap to the C/C++ interface
  to SQLite.
</p>

<p>
  Early versions of SQLite were very easy to learn since they only
  supported 5 C/C++ interfaces.  But as SQLite has grown in capability,
  new C/C++ interfaces have been added so that now there
  are over 185 distinct APIs.  This can be overwhelming to a new programmer.
  Fortunately, most of the C/C++ interfaces in SQLite are very specialized
  and never need to be used.  Despite having so many
  entry points, the core API is still relatively simple and easy to code to.
  This article aims to provide all of the background information needed to
  easily understand how SQLite works.
</p>

<p>
  A separate document, [capi3ref | The SQLite C/C++ Interface],
................................................................................
<p>
  The six C/C++ interface routines and two objects listed above form the core
  functionality of SQLite.  The developer who understands them
  will have a good foundation for using SQLite.
</p>

<p>
  Note that the list of routines is conceptual rather than actual.
  Many of these routines come in multiple versions.
  For example, the list above shows a single routine
  named [sqlite3_open()] when in fact there are three separate routines
  that accomplish the same thing in slightly different ways:
  [sqlite3_open()], [sqlite3_open16()] and [sqlite3_open_v2()].
  The list mentions [sqlite3_column_int | sqlite3_column()]
  when in fact no such routine exists.
................................................................................
<td valign="top">
  This routine
  converts SQL text into a [prepared statement] object and returns a pointer
  to that object.  This interface requires a [database connection] pointer
  created by a prior call to [sqlite3_open()] and a text string containing
  the SQL statement to be prepared.  This API does not actually evaluate
  the SQL statement.  It merely prepares the SQL statement for evaluation.






  <p>Note that the use of [sqlite3_prepare()] is not recommended for new
  applications.  The alternative routine [sqlite3_prepare_v2()] should
  be used instead.</p>
</td>

<tr><td valign="top" align="right">[sqlite3_step()]</td>
<td valign="top">
  This routine is used to evaluate a [prepared statement] that has been
  previously created by the [sqlite3_prepare()] interface.  The statement
................................................................................
<td valign="top">
  This routine returns a single column from the current row of a result
  set for a [prepared statement] that is being evaluated by [sqlite3_step()].
  Each time [sqlite3_step()] stops with a new result set row, this routine
  can be called multiple times to find the values of all columns in that row.
  As noted above, there really is no such thing as a "sqlite3_column()"
  function in the SQLite API.  Instead, what we here call "sqlite3_column()"
  is really a place-holder for an entire family of functions that return
  a value from the result set in various data types.  There are also routines
  in this family that return the size of the result (if it is a string or
  BLOB) and the number of columns in the result set.  

  <p><ul>
    <li> [sqlite3_column_blob()] </li>
    <li> [sqlite3_column_bytes()] </li>
................................................................................
<p>
  An application is allowed to prepare multiple SQL statements in advance
  and evaluate them as needed.
  There is no arbitrary limit to the number of outstanding
  [prepared statements].
</p>



























<tcl>HEADING 1 {Extending SQLite}</tcl>

<p>
  SQLite includes interfaces that can be used to extend its functionality.
  Such routines include:
</p>

<p><ul>
  <li> [sqlite3_create_collation()] </li>
  <li> [sqlite3_create_function()] </li>
  <li> [sqlite3_create_module()] </li>

</ul></p>

<p>
  The [sqlite3_create_collation()] interface is used to create new
  collating sequences for sorting text.
  The [sqlite3_create_module()] interface is used to register new
  virtual table implementations.

</p>

<p>
  The [sqlite3_create_function()] interface creates new SQL functions - 
  either scalar or aggregate.  The new function implementation typically
  makes use of the following additional interfaces:
</p>
................................................................................
  <li> [sqlite3_user_data()] </li>
  <li> [sqlite3_value_int | sqlite3_value()] </li>
</ul></p>

<p>
  All of the built-in SQL functions of SQLite are created using exactly
  these same interfaces.  Refer to the SQLite source code, and in particular
  the <b>date.c</b> and <b>func.c</b> source files for examples.



</p>




<tcl>HEADING 1 {Other Interfaces}</tcl>

<p>
  This article only mentions the foundational SQLite interfaces.
  The SQLite library includes many other APIs implementing useful
  features that are not described here.  
  A [capi3ref_funclist | complete list of functions] that form the SQLite
  application programming interface is found at the
  [capi3ref | C/C++ Interface Specification].
  Refer to that document for complete and authoritative information about
  all SQLite interfaces.
</p>

}

hd_keywords {*cintro}
HEADING 0 {An Introduction To The SQLite C/C++ Interface}
</tcl>

<p>
  This article provides an overview to the C/C++ interface
  to SQLite.
</p>

<p>
  Early versions of SQLite were very easy to learn since they only
  supported 5 C/C++ interfaces.  But as SQLite has grown in capability,
  new C/C++ interfaces have been added so that now there
  are over 200 distinct APIs.  This can be overwhelming to a new programmer.
  Fortunately, most of the C/C++ interfaces in SQLite are very specialized
  and never need to be considered.  Despite having so many
  entry points, the core API is still relatively simple and easy to code to.
  This article aims to provide all of the background information needed to
  easily understand how SQLite works.
</p>

<p>
  A separate document, [capi3ref | The SQLite C/C++ Interface],
................................................................................
<p>
  The six C/C++ interface routines and two objects listed above form the core
  functionality of SQLite.  The developer who understands them
  will have a good foundation for using SQLite.
</p>

<p>
  Note that the list of routines above is conceptual rather than actual.
  Many of these routines come in multiple versions.
  For example, the list above shows a single routine
  named [sqlite3_open()] when in fact there are three separate routines
  that accomplish the same thing in slightly different ways:
  [sqlite3_open()], [sqlite3_open16()] and [sqlite3_open_v2()].
  The list mentions [sqlite3_column_int | sqlite3_column()]
  when in fact no such routine exists.
................................................................................
<td valign="top">
  This routine
  converts SQL text into a [prepared statement] object and returns a pointer
  to that object.  This interface requires a [database connection] pointer
  created by a prior call to [sqlite3_open()] and a text string containing
  the SQL statement to be prepared.  This API does not actually evaluate
  the SQL statement.  It merely prepares the SQL statement for evaluation.

  <p>Think of each SQL statement as a small computer program.  The purpose
  of [sqlite3_prepare()] is to compile that program into object code.
  The [prepared statement] is the object code.  The [sqlite3_step()] interface
  then runs the object code to get a result.

  <p>Note that the use of [sqlite3_prepare()] is not recommended for new
  applications.  A newer alternative routine [sqlite3_prepare_v2()] should
  be used instead.</p>
</td>

<tr><td valign="top" align="right">[sqlite3_step()]</td>
<td valign="top">
  This routine is used to evaluate a [prepared statement] that has been
  previously created by the [sqlite3_prepare()] interface.  The statement
................................................................................
<td valign="top">
  This routine returns a single column from the current row of a result
  set for a [prepared statement] that is being evaluated by [sqlite3_step()].
  Each time [sqlite3_step()] stops with a new result set row, this routine
  can be called multiple times to find the values of all columns in that row.
  As noted above, there really is no such thing as a "sqlite3_column()"
  function in the SQLite API.  Instead, what we here call "sqlite3_column()"
  is a place-holder for an entire family of functions that return
  a value from the result set in various data types.  There are also routines
  in this family that return the size of the result (if it is a string or
  BLOB) and the number of columns in the result set.  

  <p><ul>
    <li> [sqlite3_column_blob()] </li>
    <li> [sqlite3_column_bytes()] </li>
................................................................................
<p>
  An application is allowed to prepare multiple SQL statements in advance
  and evaluate them as needed.
  There is no arbitrary limit to the number of outstanding
  [prepared statements].
</p>

<tcl>HEADING 1 {Configuring SQLite}</tcl>

<p>
  The default configuration for SQLite works great for most applications.
  But sometimes developers want to tweak the setup to try to squeeze out
  a little more performance, or take advantage of some obscure feature.
<p>
  The [sqlite3_config()] interface is used to make global, process-wide
  configuration changes for SQLite.  The [sqlite3_config()] interface must
  be called before any [database connections] are created.  The
  [sqlite3_config()] interface allows the programmer to do things like:
<ul>
<li>Adjust how SQLite does [memory allocation], including setting up
    alternative memory allocators appropriate for safety-critical
    real-time embedded systems and application-defind memory allocators.
<li>Set up a process-wide [error log].
<li>Specify an application-defined page cache.
<li>Adjust the use of mutexes so that they are appropriate for various
    [threading mode | threading models], or substitute an 
    application-defined mutex system.
</ul> 
<p>
  After process-wide configuration is complete and [database connections]
  have been created, individual database connections can be configured using
  calls to [sqlite3_limit()] and [sqlite3_db_config()].

<tcl>HEADING 1 {Extending SQLite}</tcl>

<p>
  SQLite includes interfaces that can be used to extend its functionality.
  Such routines include:
</p>

<p><ul>
  <li> [sqlite3_create_collation()] </li>
  <li> [sqlite3_create_function()] </li>
  <li> [sqlite3_create_module()] </li>
  <li> [sqlite3_vfs_register()] </li>
</ul></p>

<p>
  The [sqlite3_create_collation()] interface is used to create new
  [collating sequences] for sorting text.
  The [sqlite3_create_module()] interface is used to register new
  [virtual table] implementations.
  The [sqlite3_vfs_register()] interface creates new [VFSes].
</p>

<p>
  The [sqlite3_create_function()] interface creates new SQL functions - 
  either scalar or aggregate.  The new function implementation typically
  makes use of the following additional interfaces:
</p>
................................................................................
  <li> [sqlite3_user_data()] </li>
  <li> [sqlite3_value_int | sqlite3_value()] </li>
</ul></p>

<p>
  All of the built-in SQL functions of SQLite are created using exactly
  these same interfaces.  Refer to the SQLite source code, and in particular
  the 
  [http://www.sqlite.org/src/doc/trunk/src/date.c | date.c] and
  [http://www.sqlite.org/src/doc/trunk/src/func.c | func.c] source files
  for examples.
</p>

<p>
  Shared libraries or DLLs can be used as [loadable extensions] to SQLite.

<tcl>HEADING 1 {Other Interfaces}</tcl>

<p>
  This article only mentions the foundational SQLite interfaces.
  The SQLite library includes many other APIs implementing useful
  features that are not described here.  
  A [capi3ref_funclist | complete list of functions] that form the SQLite
  application programming interface is found at the
  [capi3ref | C/C++ Interface Specification].
  Refer to that document for complete and authoritative information about
  all SQLite interfaces.
</p>

their result across all rows of the result set.</p>

<tcl>
##############################################################################
Section {Core Functions} corefunc {*corefunc}
proc funcdef {syntax keywords desc} {
  hd_puts {<tr>}
  regsub -all {\s+} [string trim $syntax] {<br></br>} syntax
  regsub -all {\(([^*)]+)\)} $syntax {(<i>\1</i>)} syntax
  regsub -all {,} $syntax {</i>,<i>} syntax
  regsub -all {<i>\.\.\.</i>} $syntax {...} syntax
  hd_puts "<td valign=\"top\" align=\"right\" width=\"120\">"
  if {[llength $keywords]==0} {
    regexp {[a-z_]+} $syntax name
    hd_fragment $name *$name "${name}() SQL function"
................................................................................
  [LIKE] operator depending on whether or not an ESCAPE clause was 
  specified.
}

funcdef {load_extension(X) load_extension(X,Y)} {} {
  ^The load_extension(X,Y) function loads [SQLite extensions] out of the shared
  library file named X using the entry point Y.  ^The result of load_extension()
  is always a NULL.  ^If Y is omitted then the default entry point
  of <b>sqlite3_extension_init</b> is used.  ^The load_extension() function
  raises an exception if the extension fails to load or initialize correctly.

  <p>^The load_extension() function will fail if the extension attempts to 
  modify or delete an SQL function or collating sequence.  ^The
  extension can add new functions or collating sequences, but cannot
  modify or delete existing functions or collating sequences because
  those functions and/or collating sequences might be used elsewhere
  in the currently running SQL statement.  To load an extension that
  changes or deletes functions or collating sequences, use the
  [sqlite3_load_extension()] C-language API.</p>



}

funcdef {lower(X)} {} {
  ^The lower(X) function returns a copy of string X with all ASCII characters
  converted to lower case.  ^The default built-in lower() function works
  for ASCII characters only.  To do case conversions on non-ASCII
  characters, load the ICU extension.

their result across all rows of the result set.</p>

<tcl>
##############################################################################
Section {Core Functions} corefunc {*corefunc}
proc funcdef {syntax keywords desc} {
  hd_puts {<tr>}
  regsub -all {\s+} [string trim $syntax] {<br />} syntax
  regsub -all {\(([^*)]+)\)} $syntax {(<i>\1</i>)} syntax
  regsub -all {,} $syntax {</i>,<i>} syntax
  regsub -all {<i>\.\.\.</i>} $syntax {...} syntax
  hd_puts "<td valign=\"top\" align=\"right\" width=\"120\">"
  if {[llength $keywords]==0} {
    regexp {[a-z_]+} $syntax name
    hd_fragment $name *$name "${name}() SQL function"
................................................................................
  [LIKE] operator depending on whether or not an ESCAPE clause was 
  specified.
}

funcdef {load_extension(X) load_extension(X,Y)} {} {
  ^The load_extension(X,Y) function loads [SQLite extensions] out of the shared
  library file named X using the entry point Y.  ^The result of load_extension()
  is always a NULL.  ^If Y is omitted then the default entry point name is used.
  ^The load_extension() function raises an exception if the extension fails to
  load or initialize correctly.

  <p>^The load_extension() function will fail if the extension attempts to 
  modify or delete an SQL function or collating sequence.  ^The
  extension can add new functions or collating sequences, but cannot
  modify or delete existing functions or collating sequences because
  those functions and/or collating sequences might be used elsewhere
  in the currently running SQL statement.  To load an extension that
  changes or deletes functions or collating sequences, use the
  [sqlite3_load_extension()] C-language API.</p>

  <p>For security reasons, extension loaded is turned off by default and must
  be enabled by a prior call to [sqlite3_enable_load_extension()].</p>
}

funcdef {lower(X)} {} {
  ^The lower(X) function returns a copy of string X with all ASCII characters
  converted to lower case.  ^The default built-in lower() function works
  for ASCII characters only.  To do case conversions on non-ASCII
  characters, load the ICU extension.

<p>
The VFS specified by a URI has the highest priority.  After that comes
a VFS specified as the fourth argument to [sqlite3_open_v2()].  The
default VFS is used if no VFS is specified otherwise.
</p>


<h2>2.2 VFS Shims</h2>

<p>
From the point of view of the uppers layers of the SQLite stack, each
open database file uses exactly one VFS.
But in practice, a particular VFS might
just be a thin wrapper around another VFS that does the real work.

<p>
The VFS specified by a URI has the highest priority.  After that comes
a VFS specified as the fourth argument to [sqlite3_open_v2()].  The
default VFS is used if no VFS is specified otherwise.
</p>

<tcl>hd_fragment shim {VFS shims} {shims}</tcl>
<h2>2.2 VFS Shims</h2>

<p>
From the point of view of the uppers layers of the SQLite stack, each
open database file uses exactly one VFS.
But in practice, a particular VFS might
just be a thin wrapper around another VFS that does the real work.