Documentation Source Text

</p>


<tcl>HEADING 1 {Binding Parameters and Reusing Prepared Statements}</tcl>

<p>
  In prior discussion, it was assumed that each SQL statement is prepared
  onces, evaluated, then destroyed.  However, the SQLite allows the same
  [prepared statement] to evaluated multiple times.  These is accomplished
  using the following routines:
</p>

<p><ul>
  <li> [sqlite3_reset()] </li>
  <li> [sqlite3_bind_int | sqlite3_bind()] </li>

</p>


<tcl>HEADING 1 {Binding Parameters and Reusing Prepared Statements}</tcl>

<p>
  In prior discussion, it was assumed that each SQL statement is prepared
  once, evaluated, then destroyed.  However, the SQLite allows the same
  [prepared statement] to evaluated multiple times.  These is accomplished
  using the following routines:
</p>

<p><ul>
  <li> [sqlite3_reset()] </li>
  <li> [sqlite3_bind_int | sqlite3_bind()] </li>

          number of fields.
        <p>
          When comparing two database records, the first field of one
          record is compared to the first field of the other. If they
          are not equal, the next pair of fields are compared, and so
          on. If all the fields in the database records are equal, then
          the two records are considered equal. Otherwise, the result
          of the comparison is determined by the first pair of inequal 
          fields.
        <p>
          Two database record fields (SQL values) are compared using the
          following rules:
        <ol>
          <li>If both values are NULL, then they are considered equal.
          <li>If one value is a NULL and the other is not, it is considered

          number of fields.
        <p>
          When comparing two database records, the first field of one
          record is compared to the first field of the other. If they
          are not equal, the next pair of fields are compared, and so
          on. If all the fields in the database records are equal, then
          the two records are considered equal. Otherwise, the result
          of the comparison is determined by the first pair of unequal 
          fields.
        <p>
          Two database record fields (SQL values) are compared using the
          following rules:
        <ol>
          <li>If both values are NULL, then they are considered equal.
          <li>If one value is a NULL and the other is not, it is considered

refers to the high-level SQL formatting rather than the low-level b-tree
formatting.  Four schema format numbers are currently defined:</p>

<ol>
<li value=1>Format 1 is understood by all versions of SQLite back to
version 3.0.0.</li>
<li value=2>Format 2 adds the ability of rows within the same table
to have a varing number of columns, in order to support the
[ALTER TABLE | ALTER TABLE ... ADD COLUMN] functionality.  Support for
reading and writing format 2 was added in SQLite version 3.1.3 
on 2005-02-19.</li>
<li value=3>Format 3 adds the ability of extra columns added by
[ALTER TABLE | ALTER TABLE ... ADD COLUMN] to have non-NULL default
values.  This capability was added in SQLite version 3.1.4 
on 2005-03-11.</li>
................................................................................
with a pointer on both ends. (The previous sentence is to be understood
conceptually - the actual layout of the keys and
pointers within the page is more complicated and will be described in
the sequel.)  All keys within the same page are unique and are logically
organized in ascending order from left to right.  (Again, this ordering
is logical, not physical.  The actual location of keys within the page
is arbitrary.) ^For any key X, pointers to the left
of a X refer to b-tree pages on which alls keys are less than or equal to X.
^Pointers to the right of X refer to pages where all keys are 
greater than X.</p>

<p>Within an interior b-tree page, each key and the pointer to its
immediate left are combined into a structure called a "cell".  The
right-most pointer is held separately.  A leaf b-tree page has no
pointers, but it still uses the cell structure to hold keys for

refers to the high-level SQL formatting rather than the low-level b-tree
formatting.  Four schema format numbers are currently defined:</p>

<ol>
<li value=1>Format 1 is understood by all versions of SQLite back to
version 3.0.0.</li>
<li value=2>Format 2 adds the ability of rows within the same table
to have a varying number of columns, in order to support the
[ALTER TABLE | ALTER TABLE ... ADD COLUMN] functionality.  Support for
reading and writing format 2 was added in SQLite version 3.1.3 
on 2005-02-19.</li>
<li value=3>Format 3 adds the ability of extra columns added by
[ALTER TABLE | ALTER TABLE ... ADD COLUMN] to have non-NULL default
values.  This capability was added in SQLite version 3.1.4 
on 2005-03-11.</li>
................................................................................
with a pointer on both ends. (The previous sentence is to be understood
conceptually - the actual layout of the keys and
pointers within the page is more complicated and will be described in
the sequel.)  All keys within the same page are unique and are logically
organized in ascending order from left to right.  (Again, this ordering
is logical, not physical.  The actual location of keys within the page
is arbitrary.) ^For any key X, pointers to the left
of a X refer to b-tree pages on which all keys are less than or equal to X.
^Pointers to the right of X refer to pages where all keys are 
greater than X.</p>

<p>Within an interior b-tree page, each key and the pointer to its
immediate left are combined into a structure called a "cell".  The
right-most pointer is held separately.  A leaf b-tree page has no
pointers, but it still uses the cell structure to hold keys for

<codeblock>
  <i>-- Optimize the internal structure of FTS3 table "docs".</i>
  INSERT INTO docs(docs) VALUES('optimize');
</codeblock>

  <p>
    The statement above may appear syntacticly incorrect to some. Refer to
    the section describing the [simple fts3 queries] for an explanation.

  <p>
    There is another, deprecated, method for invoking the optimize 
    operation using a SELECT statement. New code should use statements
    similar to the INSERT above to optimize FTS3 structures.

................................................................................
  SELECT * FROM mail WHERE body    MATCH 'feedback';    <i>-- Selects row 2</i>
  SELECT * FROM mail WHERE mail    MATCH 'software';    <i>-- Selects rows 1, 2 and 3</i>
  SELECT * FROM mail WHERE mail    MATCH 'slow';        <i>-- Selects rows 1 and 3</i>
</codeblock>
  
<p>
  At first glance, the final two full-text queries in the example above seem
  to be syntacticly incorrect, as there is a table name ("mail") used as
  an SQL expression. The reason this is acceptable is that each FTS3 table
  actually has a [sqlite3_declare_vtab|HIDDEN] column with the same name
  as the table itself (in this case, "mail"). The value stored in this
  column is not meaningful to the application, but can be used as the 
  left-hand operand to a MATCH operator. This special column may also be
  passed as an argument to the [snippet()|FTS3 auxiliary functions].

<codeblock>
  <i>-- Optimize the internal structure of FTS3 table "docs".</i>
  INSERT INTO docs(docs) VALUES('optimize');
</codeblock>

  <p>
    The statement above may appear syntactically incorrect to some. Refer to
    the section describing the [simple fts3 queries] for an explanation.

  <p>
    There is another, deprecated, method for invoking the optimize 
    operation using a SELECT statement. New code should use statements
    similar to the INSERT above to optimize FTS3 structures.

................................................................................
  SELECT * FROM mail WHERE body    MATCH 'feedback';    <i>-- Selects row 2</i>
  SELECT * FROM mail WHERE mail    MATCH 'software';    <i>-- Selects rows 1, 2 and 3</i>
  SELECT * FROM mail WHERE mail    MATCH 'slow';        <i>-- Selects rows 1 and 3</i>
</codeblock>
  
<p>
  At first glance, the final two full-text queries in the example above seem
  to be syntactically incorrect, as there is a table name ("mail") used as
  an SQL expression. The reason this is acceptable is that each FTS3 table
  actually has a [sqlite3_declare_vtab|HIDDEN] column with the same name
  as the table itself (in this case, "mail"). The value stored in this
  column is not meaningful to the application, but can be used as the 
  left-hand operand to a MATCH operator. This special column may also be
  passed as an argument to the [snippet()|FTS3 auxiliary functions].

    repack individual database pages the way that the
    [VACUUM] command does.)^  In fact, because
    it moves pages around within the file, auto-vacuum can actually
    make fragmentation worse.</p>

    <p>Auto-vacuuming is only possible if the database stores some
    additional information that allows each database page to be
    traced backwards to its referer.  ^Therefore, auto-vacuuming must
    be turned on before any tables are created.  It is not possible
    to enable or disable auto-vacuum after a table has been created.</p>

    <p>^When the value of auto-vacuum is 2 or "incremental" then the additional
    information needed to do auto-vacuuming is stored in the database file
    but auto-vacuuming does not occur automatically at each commit as it
    does with auto_vacuum=full.  ^In incremental mode, the separate

    repack individual database pages the way that the
    [VACUUM] command does.)^  In fact, because
    it moves pages around within the file, auto-vacuum can actually
    make fragmentation worse.</p>

    <p>Auto-vacuuming is only possible if the database stores some
    additional information that allows each database page to be
    traced backwards to its referrer.  ^Therefore, auto-vacuuming must
    be turned on before any tables are created.  It is not possible
    to enable or disable auto-vacuum after a table has been created.</p>

    <p>^When the value of auto-vacuum is 2 or "incremental" then the additional
    information needed to do auto-vacuuming is stored in the database file
    but auto-vacuuming does not occur automatically at each commit as it
    does with auto_vacuum=full.  ^In incremental mode, the separate

       SQLITE_MISUSE.

  <li><p> Return to step 1.
</ol>

<p>
  The algorithm used by the sqlite3_blocking_prepare_v2() function is similar,
  except that step 4 (reseting the statement handle) is omitted.


<p><b>Writer Starvation</b>

<p>
  Multiple connections may hold a read-lock simultaneously.  If many threads
  are acquiring overlapping read-locks, it might be the case that at least

       SQLITE_MISUSE.

  <li><p> Return to step 1.
</ol>

<p>
  The algorithm used by the sqlite3_blocking_prepare_v2() function is similar,
  except that step 4 (resetting the statement handle) is omitted.


<p><b>Writer Starvation</b>

<p>
  Multiple connections may hold a read-lock simultaneously.  If many threads
  are acquiring overlapping read-locks, it might be the case that at least