Documentation Source Text

<title>SQLite Changes From Version 3.5.9 To 3.6.0</title>
<tcl>
proc CODE {text} {
  hd_puts "<blockquote><pre>"
  hd_puts $text
  hd_puts "</pre></blockquote>"
}
proc PARAGRAPH {text} {
  hd_resolve <p>$text</p>\n
}
proc INDENTED {text} {
  hd_resolve <blockquote>$text</blockquote>\n
}
set level(0) 0
set level(1) 0
proc HEADING {n name {tag {}}} {
  if {$tag!=""} {
    hd_fragment $tag
  }
  global level

  }
  incr n 1
  hd_puts "<h$n>$num $name</h$n>"
}

hd_keywords 35to36
HEADING 0 {Moving From SQLite 3.5.9 to 3.6.0}

PARAGRAPH {
  SQLite version 3.6.0 contains many changes.  As is the custom with
  the SQLite project, most changes are fully backwards compatible.
  However a few of the changes in version 3.6.0 are incompatible and
  might required modifications to application code in order to upgrade.
}

PARAGRAPH {
  SQLite version 3.5.0 introduced a new OS interface layer that
  provided an abstraction of the underlying operating system.
  This was an important innovation and has proven to be helpful
  in porting and maintaining SQLite.
  However, the developers have discovered some minor flaws in the
  original "virtual file system" design introduced in version 3.5.0
  and so SQLite 3.6.0 includes some small incompatible changes
  to address these flaws.  The purpose of these document is to detail
  the incompatible changes so that system developers will know what
  alterations they need to make in order to move from SQLite version 3.5
  to version 3.6.
}

INDENTED {
  <b>Important Note:</b>  The incompatible
  changes in SQLite version 3.6.0
  only effect the rare applications that make use of the 
  [sqlite3_vfs | virtual file system] interface or that
  supply a application-defined [sqlite3_mutex_enter | mutex implementation]
  or that make use of other obscure compile-time options.  The
  changes introduced by SQLite version 3.6.0 will have zero impact on the
  vast majority of SQLite applications that use the built-in interfaces
  to unix, windows, and os/2 and that use the standard build configuration.
}

PARAGRAPH {
  This document provides a survey of both the backwards compatible and
  the incompatible changes in SQLite version 3.6.0.
}

HEADING 1 {Overview Of Changes}
HEADING 2 {Incompatible Changes}
PARAGRAPH {
  An enumeration of the changes in SQLite version 3.6.0 begins with

  the changes that might require modifications to applications.
}
PARAGRAPH {
  <ol>
  <li><p>Changes to the [sqlite3_vfs] object</p>

      <ol type="a">
      <li><p>The signature of the xAccess method has been modified to

      return an [error code] and to store its output into an integer pointed
      to by a parameter, rather than returning the output directly.
      This change allows the xAccess() method to report failures.</p></li>

      <li><p>A new [error code], [SQLITE_IOERR_ACCESS], was added for cases
      where  the xAccess method of [sqlite3_vfs] fails due to an I/O
      error. </p></li>

      <li><p>The xGetTempname method has been removed from [sqlite3_vfs].
      In its place, the xOpen method is enhanced to open a temporary file
      of its own invention when the filename parameter is NULL.</p></li>

      <li><p>Added the xGetLastError() method to [sqlite3_vfs] for returning
      filesyste-specific error messages and error codes back to SQLite.</p></li>
      </ol>
  </li>

  <li><p>The signature of the xCheckReservedLock method on [sqlite3_io_methods]
      has been modifed so that it returns an [error code] and stores its
      boolean result into an integer pointed to by a parameter.</p></li>

  <li><p>When SQLite is ported to new operation systems (operating systems 
      other unix, windows, and os/2 for which ports are provided together
      with the core)
      two new functions, [sqlite3_os_init()] and [sqlite3_os_end()], must
      be provided as part of the port.</p></li>

  <li><p>Changes to compile-time options:</p>

      <ol type="a">
      <li><p>The SQLITE_MUTEX_APPDEF compile-time parameter is no longer
      recognized.  As a replacement, alternative 
      [sqlite3_mutex_enter | mutex implementations] may be created
      at runtime using [sqlite3_config()] with the [SQLITE_CONFIG_MUTEX]
      operator and the [sqlite3_mutex_methods] object.</p></li>

      <li><p>Compile-time options OS_UNIX, OS_WIN, OS_OS2, OS_OTHER, and
      TEMP_STORE have been renamed to include an "SQLITE_" prefix in order
      to help avoid namespace collisions with application software.  The
      new names of these options are respectively:
      SQLITE_OS_UNIX, SQLITE_OS_WIN, SQLITE_OS_OS2, SQLITE_OS_OTHER,
      and SQLITE_TEMP_STORE.</p></li>
      </ol>
  </li>
  </ol>
}
HEADING 2 {Fully Backwards-Compatible Enhancements}
PARAGRAPH {
  In addition to the incompatible changes listed above, SQLite
  version 3.6.0 adds the following backwards compatible changes and
  enhancements:
}
PARAGRAPH {
  <ol>

  <li value="5"><p>The new [sqlite3_config()] interface allows an application
  to customize the behavior of SQLite at run-time.  Customizations possible
  using [sqlite3_config()] include the following:</p>
  <ol type="a">
  <li><p>Specify an alternative mutex implementation using the
  [SQLITE_CONFIG_MUTEX] verb with the [sqlite3_mutex_methods] object.</p></li>
  <li><p>Specify an alternative malloc implementation using the
  [SQLITE_CONFIG_MALLOC] verb with the [sqlite3_mem_methods] object.</p></li>
  <li><p>Partually or fully disable the use of mutexes using
  [SQLITE_CONFIG_SINGLETHREAD], [SQLITE_CONFIG_MULTITHREAD] and
  [SQLITE_CONFIG_SERIALIZED].</p></li>
  </ol>
  </li>

  <li><p>The new [sqlite3_status()] interface allows an application to query
  the performance status of SQLite at runtime.
  </p></li>

  <li><p>The [sqlite3_memory_used()] and [sqlite3_memory_highwater()]
  interfaces are deprecated.  The equivalent functionality is now available
  through [sqlite3_status()].</p></li>

  <li><p>The [sqlite3_initialize()] interface can be called to explicitly
  initialize the SQLite subsystem.  The [sqlite3_initialize()] interface is
  called automatically when invoking certain interfaces so the use of
  [sqlite3_initialize()] is not required, but it is recommended.</p></li>

  <li><p>The [sqlite3_shutdown()] interface causes SQLite release any
  system resources (memory allocations, mutexes, open file handles)
  that it might have been allocated by [sqlite3_initialize()].</p></li>

  <li><p>The [sqlite3_next_stmt()] interface allows an application to discover
  all [prepared statements] associated with a [database connection].</p></li>

  <li><p>Added the [page_count] PRAGMA for returning the size of the underlying
  database file in pages.</p></li>

  <li><p>Added a new <a href="http://en.wikipedia.org/wiki/R%2a_tree">R*Tree</a>
  virtual table.</p></li>

  </ol>
}
if 0 {
HEADING 1 {Incompatible Changes}
PARAGRAPH {
  If your application defines an alternative 
  [sqlite3_vfs | virtual file system] for SQLite then some adjustments will
  need to be made in order for it to work with SQLite version 3.6.0 and
  later.  The changes are minor compared with the changes involved in
  moving from version 3.4.2 to 3.5.0, but they are still critical changes.
  Here are the changes that must be made:
}
PARAGRAPH {
  <ul>
  <li><p>
  The signature of the xAccess method of the [sqlite3_vfs] object
  must be modified so
  that it returns either [SQLITE_OK] on success or an [error code] if
  something goes wrong.  The result of the method (the determination of
  whether or not the file exists) is written into an integer which is passed
  into xAccess() by pointer.  The reason for this change is to accommodate
  systems where the xAccess() function might actually fail. 
  The xAccess() method in [sqlite3_vfs] was modeled after the access()
  system call of posix, which cannot fail as long as its parameters are
  well-formed.  But not all systems work this way.  When asked whether or
  not a file exists, some embedded systems might respond with "yes", "no",
  or "I don't know because I could not allocate the resources I need to
  find out".  The new signature of the xAccess method allows SQLite to
  accommodate the third reply.</p></li>

  <li><p>
  Similarly, the signature of the xCheckReservedLock method on the
  [sqlite3_io_methods] object is changed so that it returns either
  [SQLITE_OK] or an [error code] and reports the reserved lock status
  by writing into an integer passed into the method by pointer.</p></li>

  <li><p>
  The xGetTempname method has been removed from [sqlite3_vfs].  SQLite no
  longer needs to know the names temporary files.  When SQLite wants a
  temporary file, it will now call the xOpen method of [sqlite3_vfs] with
  a NULL pointer as the filename.  The xOpen method of the VFS must
  recognize this case and generate a unique temporary filename automatically.
  SQLite will always set the [SQLITE_OPEN_DELETEONCLOSE] when opening a
  a temporary file so that file will automatically vanish when closed.
  </p></li>

  <li><p>
  The xGetLastError() method is added to the [sqlite3_vfs] object.
  It is passed a finite-length string buffer
  by SQLite.  The method should fill in the buffer with text describing
  the most recent VFS error that occurred in the same thread in which
  the method is itself running.</p></li>

  <li><p>
  If one of the default OS interfaces for unix, win32, or os/2 is not
  used (that is to say if SQLite is compiled with -DSQLITE_OS_OTHER=1)
  then the application must supply two routines, [sqlite3_os_init()]
  and [sqlite3_os_end()].  These routines are each called once to initialize
  and to finalize the OS interface.  The [sqlite3_os_init()] routine will
  typically use [sqlite3_vfs_register()] to register the default VFS
  for the custom operating-system interface.  The [sqlite3_os_end()] routine
  should deallocate any global resources allocated by [sqlite3_os_init()].
  On many systems, [sqlite3_os_end()] is a no-op.
  </p></li>
  </ul>
}
PARAGRAPH {
  The changes above are minor and should require no more than a few minutes
  to implement for preexisting implementations.
}
}
</tcl>

<title>Compilation Options For SQLite</title>

<h1>1.0 Compilation Options For SQLite</h1>

<p>
For most purposes, SQLite can be built just fine using the default
compilation options. However, if required, the compile-time options
documented below can be used to 
<a href="#omitfeatures">omit SQLite features</a> (resulting in
a smaller compiled library size) or to change the
<a href="#defaults">default values</a> of some parameters.
</p>

<p>
Every effort has been made to ensure that the various combinations
of compilation options work harmoniously and produce a working library.
Nevertheless, it is strongly recommended that the SQLite test-suite
be executed to check for errors before using an SQLite library built
with non-standard compilation options.
</p>
<a name="defaults"></a>
<h2>1.1 Options To Set Default Parameter Values</h2>

<tcl>
proc COMPILE_OPTION {name text} {
  if {[regexp {SQLITE_([A-Z0-9_]+)} $name all label]} {
    hd_fragment [string tolower $label]
    hd_keywords $all
  }
  hd_puts <p><b>$name</b></p>
  hd_resolve <blockquote>$text</blockquote>
}

COMPILE_OPTION {SQLITE_DEFAULT_AUTOVACUUM=<i>&lt;1 or 0&gt;</i>} {
  This macro determines if SQLite creates databases with the 

  [auto_vacuum] flag set by default. The default value is 0 
  (do not create auto-vacuum databases). In any case the
  compile-time default may be overridden by the 
  [PRAGMA auto_vacuum] command.

}

COMPILE_OPTION {SQLITE_DEFAULT_CACHE_SIZE=<i>&lt;pages&gt;</i>} {
  This macro sets the default size of the page-cache for each attached
  database, in pages. This can be overridden by the 
  [PRAGMA cache_size] comamnd. The default value is 2000.
}

COMPILE_OPTION {SQLITE_DEFAULT_PAGE_SIZE=<i>&lt;bytes&gt;</i>} {
  This macro is used to set the default page-size used when a
  database is created. The value assigned must be a power of 2. The
  default value is 1024. The compile-time default may be overridden at 
  runtime by the [PRAGMA page_size] command.
}

COMPILE_OPTION {SQLITE_DEFAULT_TEMP_CACHE_SIZE=<i>&lt;pages&gt;</i>} {
  This macro sets the default size of the page-cache for temporary files
  created by SQLite to store intermediate results, in pages. It does
  not affect the page-cache for the temp database, where tables created
  using [CREATE TABLE | CREATE TEMP TABLE] are stored. The default value
  is 500.
}
</tcl>

<h2>1.2 Options To Set Size Limits</h2>

<p>There are compile-time options that will set upper bounds
on the sizes of various structures in SQLite.  The compile-time
options normally set a hard upper bound which can be changed
at run-time on individual [database connections] using the
[sqlite3_limit()] interface.</p>




<p>The compile-time options for setting upper bounds are
[limits | documented separately].  The following is a list of
the available settings:</p>

<ul>
<li> [SQLITE_MAX_ATTACHED]  </li>
<li> [SQLITE_MAX_COLUMN]  </li>
<li> [SQLITE_MAX_COMPOUND_SELECT]  </li>
<li> [SQLITE_MAX_EXPR_DEPTH]  </li>
<li> [SQLITE_MAX_FUNCTION_ARG]  </li>
<li> [SQLITE_MAX_LENGTH]  </li>
<li> [SQLITE_MAX_LIKE_PATTERN_LENGTH]  </li>
<li> [SQLITE_MAX_PAGE_COUNT]  </li>
<li> [SQLITE_MAX_PAGE_SIZE]  </li>
<li> [SQLITE_MAX_SQL_LENGTH]  </li>
<li> [SQLITE_MAX_VARIABLE_NUMBER]  </li>
</ul>

<a name="controlfeatures"></a>
<h2>1.3 Options To Control Operating Characteristics</h2>

<tcl>
COMPILE_OPTION {SQLITE_THREADSAFE=<i>&lt;0 or 1&gt;</i>} {
  This option controls whether or not code is included in SQLite to
  enable it to operate safely in a multithreaded environment.  The
  default is SQLITE_THREADSAFE=1 which is safe for use in a multithreaded
  environment.  When compiled with SQLITE_THREADSAFE=0 all mutexing code
  is omitted and it is unsafe to use SQLite in a multithreaded program.

  <p>The value of SQLITE_THREADSAFE can be determined at run-time
  using the [sqlite3_threadsafe()] interface.</p>

  <p>When SQLite has been compiled with SQLITE_THREADSAFE=1 then mutexing
  can be disabled at run-time using the [sqlite3_config()] interface together
  with the [SQLITE_CONFIG_SINGLETHREAD], [SQLITE_CONFIG_MULTITHREAD], and
  [SQLITE_CONFIG_SERIALIZED] verbs.</p>
}
</tcl>




<a name="enablefeatures"></a>
<h2>1.4 Options To Enable Features Normally Turned Off</h2>

<a name="omitfeatures"></a>
<h2>1.5 Options To Omit Features</h2>

<p>The following options are used to reduce the size of the compiled
library by omiting optional features. This is probably only useful
in embedded systems where space is especially tight, as even with all
features included the SQLite library is relatively small. Don't forget
to tell your compiler to optimize for binary size! (the -Os option if
using GCC).</p>

<p>If any of these options are defined, then the same set of SQLITE_OMIT_XXX
options must also be defined when using the 'lemon' tool to generate a parse.c
file. Because of this, these options may only used when the library is built
from source, not from the collection of pre-packaged C files provided for
non-UNIX like platforms on the website.
</p>

<tcl>
COMPILE_OPTION {SQLITE_OMIT_ALTERTABLE} {
  When this option is defined, the 
  [ALTER TABLE] command is not included in the 
  library. Executing an [ALTER TABLE] statement causes a parse error.

}

COMPILE_OPTION {SQLITE_OMIT_AUTHORIZATION} {
  Defining this option omits the authorization callback feature from the
  library. The [sqlite3_set_authorizer()] API function is not present
  in the library.

}

COMPILE_OPTION {SQLITE_OMIT_AUTOVACUUM} {
  If this option is defined, the library cannot create or write to 
  databases that support [auto_vacuum].
  Executing a [PRAGMA auto_vacuum] statement is not an error
  (since unknown PRAGMAs are silently ignored), but does not return a value
  or modify the auto-vacuum flag in the database file. If a database that
  supports auto-vacuum is opened by a library compiled with this option, it
  is automatically opened in read-only mode.

}

COMPILE_OPTION {SQLITE_OMIT_AUTOINCREMENT} {
  This option is used to omit the 
  [AUTOINCREMENT] functionality. When this 
  is macro is defined, columns declared as 
  "[INTEGER PRIMARY KEY] AUTOINCREMENT"
  behave in the same way as columns declared as "[INTEGER PRIMARY KEY]" when a 
  NULL is inserted. The sqlite_sequence system table is neither created, nor
  respected if it already exists.

}

COMPILE_OPTION {SQLITE_OMIT_BLOB_LITERAL} {
  When this option is defined, it is not possible to specify a blob in
  an SQL statement using the X'ABCD' syntax.
}

COMPILE_OPTION {SQLITE_OMIT_COMPLETE} {
  This option causes the [sqlite3_complete()] and [sqlite3_complete16()]
  interfaces to be omitted.

}

COMPILE_OPTION {SQLITE_OMIT_COMPOUND_SELECT} {
  This option is used to omit the compound [SELECT] functionality. 




  [SELECT] statements that use the 
  UNION, UNION ALL, INTERSECT or EXCEPT compound SELECT operators will 
  cause a parse error.
}

COMPILE_OPTION {SQLITE_OMIT_CONFLICT_CLAUSE} {
  In the future, this option will be used to omit the 
  [ON CONFLICT] clause from the library.

}

COMPILE_OPTION {SQLITE_OMIT_DATETIME_FUNCS} {
  If this option is defined, SQLite's built-in date and time manipulation
  functions are omitted. Specifically, the SQL functions julianday(), date(),
  time(), datetime() and strftime() are not available. The default column
  values CURRENT_TIME, CURRENT_DATE and CURRENT_DATETIME are still available.

}

COMPILE_OPTION {SQLITE_OMIT_EXPLAIN} {
  Defining this option causes the [EXPLAIN] command to be omitted from the
  library. Attempting to execute an [EXPLAIN] statement will cause a parse
  error.

}

COMPILE_OPTION {SQLITE_OMIT_FLOATING_POINT} {
  This option is used to omit floating-point number support from the SQLite
  library. When specified, specifying a floating point number as a literal 
  (i.e. "1.01") results in a parse error.

  <p>In the future, this option may also disable other floating point 
  functionality, for example the [sqlite3_result_double()], 
  [sqlite3_bind_double()], [sqlite3_value_double()] and
  [sqlite3_column_double()] API functions.
  </p>
}

COMPILE_OPTION {SQLITE_OMIT_FOREIGN_KEY} {
  If this option is defined, FOREIGN KEY clauses in column declarations are
  ignored.

}

COMPILE_OPTION {SQLITE_OMIT_INTEGRITY_CHECK} {
  This option may be used to omit the 
  [integrity_check] PRAGMA.


}

COMPILE_OPTION {SQLITE_OMIT_MEMORYDB} {
  When this is defined, the library does not respect the special database
  name ":memory:" (normally used to create an in-memory database). If 
  ":memory:" is passed to [sqlite3_open()], [sqlite3_open16()], or
  [sqlite3_open_v2()], a file with this name will be 
  opened or created.

}

COMPILE_OPTION {SQLITE_OMIT_PAGER_PRAGMAS} {
  Defining this option omits pragmas related to the pager subsystem from 
  the build.



}

COMPILE_OPTION {SQLITE_OMIT_PRAGMA} {
  This option is used to omit the [PRAGMA] command
  from the library. Note that it is useful to define the macros that omit
  specific pragmas in addition to this, as they may also remove supporting code
  in other sub-systems. This macro removes the [PRAGMA] command only.

}

COMPILE_OPTION {SQLITE_OMIT_PROGRESS_CALLBACK} {
  This option may be defined to omit the capability to issue "progress" 
  callbacks during long-running SQL statements. The 
  [sqlite3_progress_handler()]
  API function is not present in the library.
}

COMPILE_OPTION {SQLITE_OMIT_REINDEX} {
  When this option is defined, the [REINDEX]
  command is not included in the library.
  Executing a [REINDEX] statement causes 
  a parse error.

}

COMPILE_OPTION {SQLITE_OMIT_SCHEMA_PRAGMAS} {
  Defining this option omits pragmas for querying the database schema from 
  the build.






}

COMPILE_OPTION {SQLITE_OMIT_SCHEMA_VERSION_PRAGMAS} {
  Defining this option omits pragmas for querying and modifying the 
  database schema version and user version from the build. Specifically, the 
  [schema_version] and [user_version] PRAGMAs are omitted.
}

COMPILE_OPTION {SQLITE_OMIT_SUBQUERY} {
  If defined, support for sub-selects and the IN() operator are omitted.
}






COMPILE_OPTION {SQLITE_OMIT_TCL_VARIABLE} {
  If this macro is defined, then the special "$<variable-name>" syntax
  used to automatically bind SQL variables to TCL variables is omitted.

}

COMPILE_OPTION {SQLITE_OMIT_TRIGGER} {
  Defining this option omits support for VIEW objects. Neither the 
  [CREATE TRIGGER] or [DROP TRIGGER]

  commands are available in this case, and attempting to execute
  either will result in a parse error.

  <p>
  WARNING: If this macro is defined, it will not be possible to open a database
  for which the schema contains TRIGGER objects. 
  </p>
}

COMPILE_OPTION {SQLITE_OMIT_UTF16} {
  This macro is used to omit support for UTF16 text encoding. When this is
  defined all API functions that return or accept UTF16 encoded text are
  unavailable. These functions can be identified by the fact that they end
  with '16', for example [sqlite3_prepare16()], [sqlite3_column_text16()] and
  [sqlite3_bind_text16()].

}

COMPILE_OPTION {SQLITE_OMIT_VACUUM} {
  When this option is defined, the [VACUUM]
  command is not included in the library.
  Executing a [VACUUM] statement causes 
  a parse error.

}

COMPILE_OPTION {SQLITE_OMIT_VIEW} {
  Defining this option omits support for VIEW objects. Neither the 
  [CREATE VIEW] nor the [DROP VIEW]

  commands are available in this case, and
  attempting to execute either will result in a parse error.

  <p>
  WARNING: If this macro is defined, it will not be possible to open a database
  for which the schema contains VIEW objects. 
  </p>
}
</tcl>

<title>Implementation Limits For SQLite</title>
<tcl>hd_keywords limits</tcl>

<h2>Limits In SQLite</h2>

<p>
"Limits" in the context of this article means sizes or
quantities that can not be exceeded.  We are concerned
with things like the maximum number of bytes in a

    }
  }
  hd_puts "<h1>$name</h1>\n"
}
proc Subsection {args} {
  set f [lindex $args 0]
  hd_fragment pragma_$f
  foreach x $args {
    hd_keywords $x "PRAGMA $x"
  }
}
</tcl>

<p>The [PRAGMA] statement is a special SQL statement used to 
modify the operation of the SQLite library or to query the library for 
internal (non-table) data. The [PRAGMA] statement is issued using the same
interface as other SQLite commands (e.g. [SELECT], [INSERT]) but is

    data type, whether or not the column can be NULL, and the default
    value for the column.</p></li>
</ul>

<tcl>Section {Pragmas to query/modify version values} version</tcl>

<ul>
<tcl>Subsection schema_version user_version</tcl>
<li><p><b>PRAGMA &#91;database.&#93;schema_version; 
       <br>PRAGMA &#91;database.&#93;schema_version = </b><i>integer </i><b>;
       <br>PRAGMA &#91;database.&#93;user_version;
       <br>PRAGMA &#91;database.&#93;user_version = </b><i>integer </i><b>;</b>

  
<p>    The pragmas schema_version and user_version are used to set or get

    return
  }
  set x2 [split $x |]
  set kw [string trim [lindex $x2 0]]
  if {[llength $x2]==1} {
    set content $kw
    regsub {\([^)]*\)} $content {} kw
    regsub {=.*} $kw {} kw
    regsub -all {[^a-zA-Z0-9_.# -]} $kw {} kw
  } else {
    regsub -all {[^a-zA-Z0-9_.# -]} $kw {} kw
    set content [string trim [lindex $x2 1]]
  }
  global hd llink glink
  if {$hd(enable-main)} {