Documentation Source Text

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 and/or makefiles.
  This document is a briefing on the changes in SQLite 3.6.0
  with special attention to the incompatible changes.
}

HEADING 1 {Incompatible Changes}

PARAGRAPH {
  Incompatible changes are covered first since they are the most
  important to maintainers and programmers.
}

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



}

INDENTED {
  <b>Key Point:</b>  The incompatible
  changes in the SQLite operating-system interface for 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.
}

HEADING 2 {Changes In The Way The IN Operator Handles NULLs}

PARAGRAPH {
  All versions of SQLite up to and including version 3.5.9 have mishandled
  NULL values on the right-hand side of IN and NOT IN operators.
  Specifically, SQLite has previously ignored NULLs on the right-hand side
  of IN and NOT IN.
}

PARAGRAPH {
  Suppose we have a table X1 defined as follows:
}

CODE {
  CREATE TABLE x1(x INTEGER);
  INSERT INTO x1 VALUES(1);
  INSERT INTO x1 VALUES(2);
  INSERT INTO x1 VALUES(NULL);
}

PARAGRAPH {
  Given the definition of X1 above, the following expressions have
  historically evaluated to FALSE in SQLite, though the correct
  answer is actually NULL:
}

CODE {
  3 IN (1,2,NULL)
  3 IN (SELECT * FROM x1)
}

PARAGRAPH {
  Similarly, the following expressions have historically evaluated to
  TRUE when in fact NULL is also the correct answer here:
}

CODE {
  3 NOT IN (1,2,NULL)
  3 NOT IN (SELECT * FROM x1)
}

PARAGRAPH {
  The historical behavior of SQLite is incorrect according to the SQL:1999
  standard and it is inconsistent with the behavior of MySQL and
  PostgreSQL.  Version 3.6.0 changes the behavior of the IN and
  NOT IN operators to conform to the standard and to give the same
  results as other SQL database engines.
}

INDENTED {
  <b>Key Point:</b> The change to the way NULL values are handled
  by the IN and NOT IN operators is technically a bug fix, not a design
  change.  However, maintainers should check to insure that applications
  do not depend on the older, buggy behavior prior to upgrading to
  version 3.6.0.
}

HEADING 2 {Changes To Compile-Time Options}

PARAGRAPH {
  Compile-time options to SQLite are controlled by C-preprocessor 
  macros.  SQLite version 3.6.0 changes the names of some of these
  macros so that all C-preprocessor macros that are specific to
  SQLite begin with the "SQLITE_" prefix.  This is done to reduce the
  risk of name collisions with other software modules.
}

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 2 {Overview Of Incompatible Changes}

PARAGRAPH {
  The following outline gives more detail on the incompatible changes
  found in SQLite version 3.6.0, together with hyperlinks to other

  documents containing additional detail.
}
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

  <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 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:
}
PARAGRAPH {
  <ol>

  <li value="6"><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>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>
}





































































</tcl>

      <a href="http://www.sqlite.org/cvstrac/timeline">
      http://www.sqlite.org/cvstrac/timeline</a>.</p>
    }
    hd_close_aux
    hd_enable_main 1
  }
}

chng {2008 July 16 (3.6.0 beta)} {
<li>Modifications to the [sqlite3_vfs | virtual file system] interface
    to support a wider range of embedded systems.
    See [35to36 | 35to36.html] for additional information.
    <font color="red">*** Potentially incompatible change ***</font></li>
<li>All C-preprocessor macros used to control compile-time options
    now begin with the prefix "SQLITE_".  This may require changes to
    applications that compile SQLite using their own makefiles and with
    custom compile-time options, hence we mark this as a
    <font color="red">*** Potentially incompatible change ***</font></li>
<li>The SQLITE_MUTEX_APPDEF compile-time option is no longer supported.
    Alternative mutex implementations can now be added at run-time using
    the [sqlite3_config()] interface with the [SQLITE_CONFIG_MUTEX] verb.
    <font color="red">*** Potentially incompatible change ***</font></li>
<li>The handling of IN and NOT IN operators that contain a NULL on their
    right-hand side expression is brought into compliance with the SQL
    standard and with other SQL database engines.  This is a bug fix,
    but as it has the potential to break legacy applications that depend
    on the older buggy behavior, we mark that as a
    <font color="red">*** Potentially incompatible change ***</font></li>
<li>Added the [sqlite3_config()] interface for doing run-time configuration
    of the entire SQLite library.</li>
<li>Added the [sqlite3_status()] interface used for querying run-time status
    information about the overall SQLite library and its subsystems.</li>
<li>Added the [sqlite3_initialize()] and [sqlite3_shutdown()] interfaces.</li>
<li>Added the [PRAGMA page_count] command.</li>
<li>Added the [sqlite3_next_stmt()] interface.</li>
<li>Added a new R*Tree virtual table</li>
}

chng {2008 May 14 (3.5.9)} {
<li>Added <em>experimental</em>
    support for the [journal_mode] PRAGMA and persistent journal.</li>
<li>[journal_mode | Journal mode PERSIST] is the default behavior in
    [locking_mode | exclusive locking mode].</li>
<li>Fix a performance regression on LEFT JOIN (see [ticket #3015])

  <td valign="top">2007-Sep-3</td>
  <td><p>The design of the OS interface layer was changed for
  release 3.5.0.  Applications that implemented a custom OS
  interface will need to be modified in order to upgrade.
  There are also some subtly different semantics a few obscure
  APIs.  An <a href="34to35.html">article</a> is avilable which
  describing the changes in detail.</p>

  <p>The on-disk file format is unchanged.</p>
  </td>
</tr>
<tr>
  <td valign="top">3.5.9 to 3.6.0</td>
  <td valign="top">2008-July-16</td>
  <td><p>There are minor tweaks to the new OS interface layer that
  was added in version 3.5.0.
  Applications that implemented a custom OS
  interface will need to be adjusted.
  An <a href="35to36.html">article</a> is avilable which
  describing the changes in detail.</p>

  <p>The on-disk file format is unchanged.</p>
  </td>
</tr>
</table>
</blockquote>

</td>
<td width="20"></td><td bgcolor="#80a796" width="1"></td><td width="20"></td>
<td valign="top">
<h3>Current Status</h3>

<p><ul>
<li><a href="releaselog/3_6_0.html">Version 3.6.0</a>
of SQLite is recommended for all new development.</li>
</ul></p>

<h3>Common Links</h3>

<p><ul>
<li> <a href="features.html">Features</a> </li>
<li> <a href="faq.html">Frequently Asked Questions</a> </li>

  hd_puts "<h3>$date - $title</h3>"
  regsub -all "\n( *\n)+" $text "</p>\n\n<p>" txt
  regsub -all {[Tt]icket #(\d+)} $txt \
      {<a href="http://www.sqlite.org/cvstrac/tktview?tn=\1">\0</a>} txt
  hd_resolve "<p>$txt</p>"
  hd_puts "<hr width=\"50%\">"
}

newsitem {2008-July-16} {Version 3.6.0 beta} {
  Version 3.6.0 makes changes to the [sqlite3_vfs | VFS] object in order
  to make SQLite more easily portable to a wider variety of platforms.
  There are potential incompatibilities with some legacy applications.
  See the [35to36 | 35to36.html] document for details.

  Many new interfaces are introduced in version 3.6.0.  The code is
  very well tested and is appropriate for use in stable systems.  We
  have attached the "beta" designation only so that we can make tweaks to
  the new interfaces in the next release without having to declare an
  incompatibility.
}

newsitem {2008-May-12} {Version 3.5.9} {
  Version 3.5.9 adds a new experimental [PRAGMA]: [journal_mode].
  Setting the journal mode to PERSIST can provide performance improvement
  on systems where deleting a file is expective.  The PERSIST journal
  mode is still considered experimental and should be used with caution
  pending further testing.

  [sqlite3_context_db_handle()] is a convenience routine that allows
  an application-defined SQL function implementation to retrieve
  its [database connection] handle.

  This release of SQLite is considered stable and ready for production use.
}

























































</tcl>

<a href="oldnews.html">Old news...</a>

<title>SQLite Older News</title>

<tcl>
proc newsitem {date title text} {
  regsub -all {[^a-z0-9]} $date _ tag
  hd_puts "<a name=\"$tag\"></a>"
  hd_puts "<h3>$date - $title</h3>"
  regsub -all "\n( *\n)+" $text "</p>\n\n<p>" txt
  hd_resolve "<p>$txt</p>"
  hd_puts "<hr width=\"50%\">"
}

newsitem {2008-Mar-17} {Version 3.5.7} {
  Version 3.5.7 fixes several minor and obscure bugs, especially 
  in the autoconf-generated makefile.  Upgrading is optional.
  This release of SQLite is considered stable and ready for production use.
}

newsitem {2008-Feb-6} {Version 3.5.6} {
  Version 3.5.6 fixes a minor regression in 3.5.5 - a regression that
  had nothing to do with the massive change ove the virtual machine
  to a register-based design.
  No problems have been reported with the new virtual machine.  This
  release of SQLite is considered stable and ready for production use.
}

newsitem {2008-Jan-31} {Version 3.5.5} {
  Version 3.5.5 changes over 8% of the core source code of SQLite in order
  to convert the internal virtual machine from a stack-based design into
  a register-based design.  This change will allow future optimizations
  and will avoid an entire class of stack overflow bugs that have caused
  problems in the past.  Even though this change is large, extensive testing
  has found zero errors in the new virtual machine and so we believe this
  to be a very stable release.
}

newsitem {2007-Dec-14} {Version 3.5.4} {
  Version 3.5.4 fixes an long-standing but obscure bug in UPDATE and
  DELETE which might cause database corruption.  (See ticket #2832.)
  Upgrading is recommended for all users.

  This release also brings the processing of ORDER BY statements into
  compliance with standard SQL.  This could, in theory, cause problems
  for existing applications that depend on the older, buggy behavior.
  See ticket #2822 for additional information.
}

newsitem {2007-Dec-12} {SQLite Consortium Announced} {
  The <a href="consortium.html">SQLite Consortium</a> was launched
  today with <a href="http://www.mozilla.org/">Mozilla</a> and
  <a href="http://www.symbian.com/">Symbian</a> as charter members.
  As noted in the <a href="pressrelease-20071212.html">press release</a>,
  the Consortium's goal is to promote the continuing vitality and
  independence of SQLite.  
}
newsitem {2007-Nov-27} {Version 3.5.3} {
  This is an incremental release that fixes several minor problems.
  Upgrading is optional.  If Version 3.5.2 or 3.5.1 is working fine
  for you, then there is no pressing need to change to 3.5.3.

  The prebuilt binaries and the amalgamation found on the 
  <a href="download.html">download</a> page include the FTS3 fulltext
  search extension module.  We are doing this on an experimental
  basis and are not promising to provide prebuilt binaries with
  FTS3 in the future.
}

newsitem {2007-Nov-05} {Version 3.5.2} {
  This is an incremental release that fixes several minor problems,
  adds some obscure features, and provides some performance tweaks.  
  Upgrading is optional.

  The experimental compile-time option

    <div><!-- container div to satisfy validator -->
    
    <a href="${path}index.html">
    <img class="logo" src="${path}images/SQLite.gif" alt="SQLite Logo"
     border="0"></a>
    <div><!-- IE hack to prevent disappearing logo--></div>
    <div class="tagline">Small. Fast. Reliable.<br>Choose any three.</div>

    <table width=100% style="clear:both"><tr><td>
      <div class="se"><div class="sw"><div class="ne"><div class="nw">
      <div class="toolbar">
        <a href="${path}about.html">About</a>
        <a href="${path}sitemap.html">Sitemap</a>
        <a href="${path}docs.html">Documentation</a>
        <a href="${path}download.html">Download</a>
        <a href="${path}copyright.html">License</a>
        <a href="${path}news.html">News</a>
        <a href="http://www.sqlite.org/cvstrac/index">Developers</a>
        <a href="${path}support.html">Support</a>
      </div></div></div></div></div>
    </td></tr></table>
  }
  if {[file exists DRAFT]} {
    putsin4 $fd {
      <p align="center"><font size="6" color="red">*** DRAFT ***</font></p>
    }
  }
  if {$srcfile!=""} {
    if {[file exists DRAFT]} {
      set hd(footer) {
        <p align="center"><font size="6" color="red">*** DRAFT ***</font></p>
      }
    } else {
      set hd(footer) {}
    }
    append hd(footer) "<hr><small><i>\n"
    set mtime [file mtime $srcfile]
    set date [clock format $mtime -format {%Y/%m/%d %H:%M:%S UTC} -gmt 1]
    append hd(footer) "This page last modified $date\n"
    append hd(footer) "</i></small></div></body></html>"
  } else {
    set hd(enable-main) $saved_enable
  }
}

















# The following proc is used to ensure consistent formatting in the 
# HTML generated by lang.tcl and pragma.tcl.
#
proc Syntax {args} {
  hd_puts {<table cellpadding="10">}
  foreach {rule body} $args {
    hd_puts "<tr><td align=\"right\" valign=\"top\">"