Documentation Source Text

Check-in [d7a2085524]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:More typo and spelling fixes.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: d7a208552486ddddff9e37bceed18328968149f2
User & Date: shaneh 2010-08-30 15:10:45.000
Context
2010-08-31
11:01
Add documentation for custom r-tree geometry callbacks to rtree.in (check-in: 7e421e2c51 user: dan tags: trunk)
2010-08-30
15:10
More typo and spelling fixes. (check-in: d7a2085524 user: shaneh tags: trunk)
15:09
More updates to custom word list. (check-in: 2bbdfeeb37 user: shaneh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to pages/cintro.in.
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
</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>







|







277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
</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>
Changes to pages/fileformat.in.
1303
1304
1305
1306
1307
1308
1309
1310
1311
1312
1313
1314
1315
1316
1317
          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







|







1303
1304
1305
1306
1307
1308
1309
1310
1311
1312
1313
1314
1315
1316
1317
          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
Changes to pages/fileformat2.in.
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
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>







|







282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
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>
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
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







|







437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
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
Changes to pages/fts3.in.
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223

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








|







209
210
211
212
213
214
215
216
217
218
219
220
221
222
223

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

311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
  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].








|







311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
  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].

Changes to pages/pragma.in.
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
    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







|







128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
    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
Changes to pages/unlock_notify.in.
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
       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







|







153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
       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