Documentation Source Text

HEADING 1 {Incompatible Changes}

PARAGRAPH {
  Incompatible changes are covered first since they are the most
  important to maintainers and programmers.
}
HEADING 2 {Overview Of Incompatible Changes}

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.
      In association with this signature change, a new 
      extended error code [SQLITE_IOERR_ACCESS] has been added.
      </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.  In
      association with this change, a new extended error code
      [SQLITE_IOERR_CHECKRESERVEDLOCK] has been added.</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>The way in which the IN and NOT IN operators handle NULL values
      in their right-hand expressions has been brought into compliance with
      the SQL standard and with other SQL database engines.</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 {Changes To The VFS Layer}

PARAGRAPH {
  SQLite [version 3.5.0] introduced a [34to35 | new OS interface layer] that
  provided an abstraction of the underlying operating system.
  This was an important innovation and has proven to be helpful

INDENTED {
  <b>Key Point:</b>  Changes to compile-time options have the potential
  to effect makefiles in projects that do customized builds of SQLite.
  Theses changes should have zero impact on application code and for
  most projects which use a standard, default build of SQLite.
}

































































HEADING 1 {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:

<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>
  regsub -all "\n\\s*\n" $text "</p>\n\n<p>" text
  hd_resolve <blockquote><p>$text</p></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_FILE_FORMAT=<i>&lt;1 or 4&gt;</i>} {
  The default schema-level file format used by SQLite when creating
  new database files is set by this macro.  The file formats are all
  very similar.  The difference between formats 1 and 4 is that format
  4 understands descending indices and has a tighter encoding for
  boolean values.

  SQLite (as of [version 3.6.0]) can read and write any file format
  between 1 and 4.  But older versions of SQLite might not be able to
  read formats greater than 1.  So that older versions of SQLite will
  be able to read and write database files created by newer versions
  of SQLite, the default file format is set to 1 for maximum
  compatability.

  The file format for a new database can be set at runtime using
  the [PRAGMA legacy_file_format] command.
}

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_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.

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

  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.
}

COMPILE_OPTION {SQLITE_TEMP_STORE=<i>&lt;0 through 3&gt;</i>} {
  This option controls whether temporary files are stored on disk or
  in memory.  The meanings for various settings of this compile-time
  option are as follows:

  <table cellpadding="2" border="1">
  <tr><th>SQLITE_TEMP_STORE</th><th>Meaning</th></tr>
  <tr><td align="center">0</td><td>Always use temporary files</td></tr>
  <tr><td align="center">1</td><td>Use files by default but allow the
  [PRAGMA temp_store] command to override</td></tr>
  <tr><td align="center">2</td><td>Use memory by default but allow the
  [PRAGMA temp_store] command to override</td></tr>
  <tr><td align="center">3</td><td>Always use memory</td></tr>
  </table>

  The default setting is 1.  
  Additional information can be found in [tempstore | tempfiles.html].
}
</tcl>

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

<a name="omitfeatures"></a>

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.


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

}

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

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.


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

}
</tcl>

  }
  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" "$x pragma"
  }
}
</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

    <p>When the pragma is issued with no argument, it returns the
    setting of the flag.  This pragma does <u>not</u> tell which
    file format the current database is using.  It tells what format
    will be used by any newly created databases.</p>

    <p>This flag only affects newly created databases.  It has no
    effect on databases that already exist.</p>

    <p>The default file format is set by the
    [SQLITE_DEFAULT_FILE_FORMAT] compile-time option.</p>
</li>

<tcl>Subsection locking_mode</tcl>
<li><p><b>PRAGMA locking_mode;
       <br>PRAGMA locking_mode = <i>NORMAL | EXCLUSIVE</i></b></p>
    <p>This pragma sets or queries the database connection locking-mode. 
    The locking-mode is either NORMAL or EXCLUSIVE.

<tcl>Subsection temp_store</tcl>
<li><p><b>PRAGMA temp_store;
       <br>PRAGMA temp_store = DEFAULT;</b> (0)<b>
       <br>PRAGMA temp_store = FILE;</b> (1)<b>
       <br>PRAGMA temp_store = MEMORY;</b> (2)</p>
    <p>Query or change the setting of the "<b>temp_store</b>" parameter.
    When temp_store is DEFAULT (0), the compile-time C preprocessor macro
    [SQLITE_TEMP_STORE] is used to determine where temporary tables and indices
    are stored.  When
    temp_store is MEMORY (2) temporary tables and indices are kept in memory.
    When temp_store is FILE (1) temporary tables and indices are stored
    in a file.  The <a href="#pragma_temp_store_directory">
    temp_store_directory</a> pragma can be used to specify the directory
    containing this file.
    <b>FILE</b> is specified. When the temp_store setting is changed,
    all existing temporary tables, indices, triggers, and views are
    immediately deleted.</p>

    <p>It is possible for the library compile-time C preprocessor symbol
    [SQLITE_TEMP_STORE] to override this pragma setting.
    The following table summarizes
    the interaction of the [SQLITE_TEMP_STORE] preprocessor macro and the
    temp_store pragma:</p>

    <blockquote>
    <table cellpadding="2" border="1">
    <tr><th valign="bottom">[SQLITE_TEMP_STORE]</th>
        <th valign="bottom">PRAGMA<br>temp_store</th>
        <th>Storage used for<br>TEMP tables and indices</th></tr>
    <tr><td align="center">0</td>
        <td align="center"><em>any</em></td>
        <td align="center">file</td></tr>
    <tr><td align="center">1</td>
        <td align="center">0</td>

<li><p><b>PRAGMA index_list(</b><i>table-name</i><b>);</b></p>
    <p>For each index on the named table, invoke the callback function
    once with information about that index.  Arguments include the
    index name and a flag to indicate whether or not the index must be
    unique.</p></li>

<tcl>Subsection page_count</tcl>
<li><p><b>PRAGMA &#91;database&#93;.page_count;</b></p>
    <p>Return the total number of pages in the database file.</p></li>

<tcl>Subsection table_info</tcl>
<li><p><b>PRAGMA table_info(</b><i>table-name</i><b>);</b></p>
    <p>For each column in the named table, invoke the callback function
    once with information about that column, including the column name,
    data type, whether or not the column can be NULL, and the default

journal to restore the database to its state prior to the start
of the incomplete transaction.  This is how SQLite implements
atomic commit.
</p>

<p>
If an application puts SQLite in 
[PRAGMA locking_mode | exclusive locking mode] using
the pragma:
</p>

<blockquote><pre>
PRAGMA locking_mode=EXCLUSIVE;
</pre></blockquote>

<p>
SQLite creates a new rollback journal at the start of the first
transaction within an exclusive locking mode session.  But at the
conclusion of the transaction, it does not delete the rollback
journal.  The rollback journal might be truncated, or its header
might be zeroed (depending on what version of SQLite you are using)
but the rollback journal is not deleted.  The rollback journal is
not deleted until exclusive access mode is exited.</p>

<p>
Rollback journal creation and deletion is also changed by the
[journal_mode pragma].
The default journaling mode is DELETE, which is the default behavior
of deleting the rollback journal file at the end of each transaction,
as described above.  The PERSIST journal mode foregoes the deletion of
the journal file and instead overwrites the rollback journal header
with zeros, which prevents other processes from rolling back the
journal and thus has the same effect as deleting the journal file, though
without the expense of actually removing the file from disk.  In other

constraint or a RAISE exception within a trigger and thus need to
undo partial results.
If the UPDATE or INSERT is not contained within BEGIN...COMMIT and if
there are no other active statements on the same database connection then
no statement journal is created since the ordinary
rollback journal can be used instead.
The statement journal is also omitted if an alternative
[ON CONFLICT | conflict resolution algorithm] is
used.  For example:
</p>

<blockquote><pre>
UPDATE OR FAIL ...
UPDATE OR IGNORE ...
UPDATE OR REPLACE ...

<p>
The TEMP database file is very similar to auxiliary database
files added using the [ATTACH] statement, though with a few
special properties.
The TEMP database is always automatically deleted when the
[database connection] is closed.
The TEMP database always uses the

[synchronous=OFF] and [journal_mode=PERSIST]
PRAGMA settings.
And, the TEMP database cannot be used with [DETACH] nor can
another process [ATTACH] the TEMP database.
</p>

<p>
The temporary files associated with the TEMP database and its
rollback journal are only created if the application makes use

<p>
The temporary file created by the [VACUUM] command exists only
for the duration of the command itself.  The size of the temporary
file will be no larger than the original database.
</p>

<tcl>hd_fragment tempstore; hd_keywords tempstore</tcl>
<h2>3.0 The SQLITE_TEMP_STORE Compile-Time Parameter and Pragma</h2>

<p>
The rollback journal, master journal,
and statement journal files are always written
to disk.
But the other kinds of temporary files might be stored in memory
only and never written to disk.
Whether or not temporary files other than the rollback,
master, and statement journals are written to disk or stored only in memory
depends on the [SQLITE_TEMP_STORE] compile-time parameter, the
[temp_store pragma],
and on the size of the temporary file.
</p>

<p>
The [SQLITE_TEMP_STORE] compile-time parameter is a #define whose value is
an integer between 0 and 3, inclusive.  The meaning of the
[SQLITE_TEMP_STORE] compile-time parameter is as follows:
</p>

<ol type="1">
<li value="0">
Temporary files are always stored on disk regardless of the setting
of the [temp_store pragma].
</li>
<li value="1">
Temporary files are stored on disk by default but this can be
overridden by the [temp_store pragma].
</li>
<li value="2">
Temporary files are stored in memory by default but this can be
overridden by the [temp_store pragma].
</li>
<li value="3">
Temporary files are always stored in memory regardless of the setting
of the [temp_store pragma].
</li>
</ol>

<p>
The default value of the [SQLITE_TEMP_STORE] compile-time parameter is 1,
which means to store temporary files on disk but provide the option
of overriding the behavior using the [temp_store pragma].

</p>

<p>
The [temp_store pragma] has
an integer value which also influences the decision of where to store
temporary files.  The values of the the temp_store pragma have the
following meanings:
</p>

<ol type="1">
<li value="0">
Use either disk or memory storage for temporary files as determined
by the [SQLITE_TEMP_STORE] compile-time parameter.
</li>
<li value="1">
If the [SQLITE_TEMP_STORE] compile-time parameter specifies memory storage for
temporary files, then override that decision and use disk storage instead.
Otherwise follow the recommendation of the [SQLITE_TEMP_STORE] compile-time
parameter.
</li>
<li value="2">
If the [SQLITE_TEMP_STORE] compile-time parameter specifies disk storage for
temporary files, then override that decision and use memory storage instead.
Otherwise follow the recommendation of the [SQLITE_TEMP_STORE] compile-time
parameter.
</li>
</ol>

<p>
The default setting for the [temp_store pragma] is 0,

which means to following the recommendation of [SQLITE_TEMP_STORE] compile-time
parameter.
</p>

<p>
To reiterate, the [SQLITE_TEMP_STORE] compile-time parameter an the 
[temp_store pragma] only
influence the temporary files other than the rollback journal
and the master journal.  The rollback journal and the master
journal are always written to disk regardless of the settings of
the [SQLITE_TEMP_STORE] compile-time parameter and the
[temp_store pragma].
</p>

<h2>4.0 Other Temporary File Optimizations</h2>

<p>
SQLite uses a page cache of recently read and written database
pages.  This page cache is used not just for the main database
file but also for transient indices and tables stored in temporary
files.  If SQLite needs to use a temporary index or table and
the [SQLITE_TEMP_STORE] compile-time parameter and the
[temp_store pragma] are
set to store temporary tables and index on disk, the information
is still initially stored in memory in the page cache.  The 
temporary file is not opened and the information is not truly
written to disk until the page cache is full.
</p>

<p>