Documentation Source Text

Check-in [c922c8ef50]
Login

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

Overview
Comment:Change 3.6.21 release date. Fix typos reported by internet reviewers.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: c922c8ef50d5d1111b9467fae90968e0e765657e
User & Date: drh 2009-12-03 18:14:59
Context
2009-12-04
07:50
Clarify that the values stored in the rowid of an FTS3 table do not change when the database is VACUUMed. check-in: 39d5f660c5 user: dan tags: trunk
2009-12-03
18:14
Change 3.6.21 release date. Fix typos reported by internet reviewers. check-in: c922c8ef50 user: drh tags: trunk
2009-12-02
08:41
Fix typo in howtocompile.in markup. check-in: a47d474c56 user: dan tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to pages/changes.in.

38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
      http://www.sqlite.org/src/timeline</a>.</p>
    }
    hd_close_aux
    hd_enable_main 1
  }
}

chng {2009 Dec 05 (3.6.21)} {
<li>The SQL output resulting from [sqlite3_trace()] is now modified to include
the values of [bound parameters].
<li>Performance optimizations targetting a specific use case from
a single high-profile user of SQLite.  A 12% reduction in the number of
CPU operations is achieved (as measured by valgrind).  Actual performance
improvements in practice may vary depending on workload.  Changes
include:
<ul>
<li>The [ifnull()] and [coalesce()] SQL functions are now implemented
using in-line VDBE code rather than calling external functions, so that
unused arguments need never be evaluated.
<li>The [substr()] SQL function does not bother to measure the length
if its entire input string if it is only computing a prefix
<li>Unnecessary OP_IsNull, OP_Affinity, and OP_MustBeInt VDBE opcodes
are suppressed
<li>Various code refactorizations for performance
</ul>
<li>The FTS3 extension has undergone a major rework and cleanup.
New [fts3 | FTS3 documentation] is now available.
<li>The [SQLITE_SECURE_DELETE] compile-time option fixed to make sure that







|












|







38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
      http://www.sqlite.org/src/timeline</a>.</p>
    }
    hd_close_aux
    hd_enable_main 1
  }
}

chng {2009 Dec 04 (3.6.21)} {
<li>The SQL output resulting from [sqlite3_trace()] is now modified to include
the values of [bound parameters].
<li>Performance optimizations targetting a specific use case from
a single high-profile user of SQLite.  A 12% reduction in the number of
CPU operations is achieved (as measured by valgrind).  Actual performance
improvements in practice may vary depending on workload.  Changes
include:
<ul>
<li>The [ifnull()] and [coalesce()] SQL functions are now implemented
using in-line VDBE code rather than calling external functions, so that
unused arguments need never be evaluated.
<li>The [substr()] SQL function does not bother to measure the length
its entire input string if it is only computing a prefix
<li>Unnecessary OP_IsNull, OP_Affinity, and OP_MustBeInt VDBE opcodes
are suppressed
<li>Various code refactorizations for performance
</ul>
<li>The FTS3 extension has undergone a major rework and cleanup.
New [fts3 | FTS3 documentation] is now available.
<li>The [SQLITE_SECURE_DELETE] compile-time option fixed to make sure that

Changes to pages/docs.in.

113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
doc {Asynchronous IO Mode} {asyncvfs.html} {
  This page describes the asynchronous IO extension developed alongside
  SQLite. Using asynchronous IO can cause SQLite to appear more responsive
  by delegating database writes to a background thread.
}
doc {Foreign Key Support} {foreignkeys.html} {
  This document describes the support for foreign key constraints introduced
  in version 3.7.0.
}
doc {Shared Cache Mode} {sharedcache.html} {
  Version 3.3.0 and later supports the ability for two or more
  database connections to share the same page and schema cache.
  This feature is useful for certain specialized applications.
}
doc {Unlock Notify} {unlock_notify.html} {







|







113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
doc {Asynchronous IO Mode} {asyncvfs.html} {
  This page describes the asynchronous IO extension developed alongside
  SQLite. Using asynchronous IO can cause SQLite to appear more responsive
  by delegating database writes to a background thread.
}
doc {Foreign Key Support} {foreignkeys.html} {
  This document describes the support for foreign key constraints introduced
  in version 3.6.19.
}
doc {Shared Cache Mode} {sharedcache.html} {
  Version 3.3.0 and later supports the ability for two or more
  database connections to share the same page and schema cache.
  This feature is useful for certain specialized applications.
}
doc {Unlock Notify} {unlock_notify.html} {

Changes to pages/fts3.in.

54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
...
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
...
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
...
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
...
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
...
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
}]

<p>
  Of course, the two queries above are not entirely equivalent. For example
  the LIKE query matches rows that contain terms such as "linuxophobe"
  or "EnterpriseLinux" (as it happens, the Enron E-Mail Dataset does not
  actually contain any such terms), whereas the MATCH query on the FTS3 table
  selects only those rows that contain "linux" as a discreet token. Both 
  searches are case-insensitive. The FTS3 table consumes around 2006 MB on
  disk compared to just 1453 MB for the ordinary table. Using the same
  hardware configuration used to perform the SELECT queries above, the FTS3
  table took just under 31 minutes to populate, versus 25 for the ordinary
  table.

[h2 "Creating and Destroying FTS3 Tables"]
................................................................................
    already exists in the table is an error, just as it would be with an 
    ordinary SQLite table.

  <p>
    There is one other subtle difference between "docid" and the normal SQLite
    aliases for the rowid column. Normally, if an INSERT or UPDATE statement 
    assigns discreet values to two or more aliases of the rowid column, SQLite 
    writes the rightmost of of such values specified in the INSERT or UPDATE
    statement to the database. However, assigning a non-NULL value to both
    the "docid" and one or more of the SQLite rowid aliases when inserting or
    updating an FTS3 table is considered an error. See below for an example.

[Code {
  <i>-- Create an FTS3 table</i>
  CREATE VIRTUAL TABLE pages USING fts3(title, body);
................................................................................
  of full-text query possible. However more complicated queries are possible,
  including phrase searches, term-prefix searches and searches for documents 
  containing combinations of terms occuring within a defined proximity of each
  other. The various ways in which the full-text index may be queried are
  \[FTS3 MATCH|described below\].

<p>
  Normally, full-text queries are case-insensitive. However, this (and other
  details) are depend on the specific \[tokenizer\] used by the FTS3 table
  being queried. Refer to the section on \[tokenizer|tokenizers\] for details.

<p>
  The paragraph above notes that a MATCH operator with a simple term as the
  right-hand operand evaluates to true for all documents that contain the
  specified term. In this context, the "document" may refer to either the 
  data stored in a single column of a row of an FTS3 table, or to the contents
................................................................................
  performed by specifying a clause of the form 
  "&lt;column&gt; MATCH &lt;full-text query expression&gt;" to the WHERE 
  clause of a SELECT statement that reads data from an FTS3 table. 
  \[simple fts3 queries|Simple FTS3 queries\] that return all documents that 
  contain a given term are described above. In that discussion the right-hand
  operand of the MATCH operator was assumed to be a string consisting of a
  single term. This section describes the more complex query types supported 
  by FTS3 tables, and how they may be utilized by specifying more a more
  complex query expression as the right-hand operand of a MATCH operator.

<p>
  FTS3 tables support three basic query types:

<ul>
  <li><p><b>Token or token prefix queries</b>. 
................................................................................
  <i>-- ("driver" may also appear in the title, but this alone will not satisfy the</i>.
  <i>-- query criteria).</i>
  SELECT * FROM docs WHERE body MATCH 'title:linux driver';
}]

<ul>
  <li><p><b>Phrase queries</b>.
    A phrase query is a query that retreives all documents that contain a
    nominated set of terms or term prefixes in a specified order with no
    intervening tokens. Phrase queries are specified by enclosing a space
    separated sequence of terms or term prefixes in double quotes (").
    For example:
</ul>

[Code {
................................................................................
  Stemming algorithm</a>. This tokenizer uses the same rules to separate
  the input document into terms, but as well as folding all terms to lower
  case it uses the Porter Stemming algorithm to reduce related English language
  words to a common root. For example, using the same input document as in the
  paragraph above, the porter tokenizer extracts the following tokens:
  "right now thei veri frustrat". Even though some of these terms are not even
  English words, in some cases using them to build the full-text index is more
  useful than the more intelligble output produced by the simple tokenizer.
  Using the porter tokenizer, the document not only matches full-text queries
  such as "MATCH 'Frustrated'", but also queries such as "MATCH 'Frustration'",
  as the term "Frustration" is reduced by the Porter stemmer algorithm to 
  "frustrat" - just as "Frustrated" is. So, when using the porter tokenizer,
  FTS3 is able to find not just exact matches for queried terms, but matches
  against similar English language terms. For more information on the 
  Porter Stemmer algorithm, please refer to the page linked above.







|







 







|







 







|
|







 







|







 







|







 







|







54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
...
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
...
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
...
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
...
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
...
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
}]

<p>
  Of course, the two queries above are not entirely equivalent. For example
  the LIKE query matches rows that contain terms such as "linuxophobe"
  or "EnterpriseLinux" (as it happens, the Enron E-Mail Dataset does not
  actually contain any such terms), whereas the MATCH query on the FTS3 table
  selects only those rows that contain "linux" as a discrete token. Both 
  searches are case-insensitive. The FTS3 table consumes around 2006 MB on
  disk compared to just 1453 MB for the ordinary table. Using the same
  hardware configuration used to perform the SELECT queries above, the FTS3
  table took just under 31 minutes to populate, versus 25 for the ordinary
  table.

[h2 "Creating and Destroying FTS3 Tables"]
................................................................................
    already exists in the table is an error, just as it would be with an 
    ordinary SQLite table.

  <p>
    There is one other subtle difference between "docid" and the normal SQLite
    aliases for the rowid column. Normally, if an INSERT or UPDATE statement 
    assigns discreet values to two or more aliases of the rowid column, SQLite 
    writes the rightmost of such values specified in the INSERT or UPDATE
    statement to the database. However, assigning a non-NULL value to both
    the "docid" and one or more of the SQLite rowid aliases when inserting or
    updating an FTS3 table is considered an error. See below for an example.

[Code {
  <i>-- Create an FTS3 table</i>
  CREATE VIRTUAL TABLE pages USING fts3(title, body);
................................................................................
  of full-text query possible. However more complicated queries are possible,
  including phrase searches, term-prefix searches and searches for documents 
  containing combinations of terms occuring within a defined proximity of each
  other. The various ways in which the full-text index may be queried are
  \[FTS3 MATCH|described below\].

<p>
  Normally, full-text queries are case-insensitive. However, this is
  is dependent on the specific \[tokenizer\] used by the FTS3 table
  being queried. Refer to the section on \[tokenizer|tokenizers\] for details.

<p>
  The paragraph above notes that a MATCH operator with a simple term as the
  right-hand operand evaluates to true for all documents that contain the
  specified term. In this context, the "document" may refer to either the 
  data stored in a single column of a row of an FTS3 table, or to the contents
................................................................................
  performed by specifying a clause of the form 
  "&lt;column&gt; MATCH &lt;full-text query expression&gt;" to the WHERE 
  clause of a SELECT statement that reads data from an FTS3 table. 
  \[simple fts3 queries|Simple FTS3 queries\] that return all documents that 
  contain a given term are described above. In that discussion the right-hand
  operand of the MATCH operator was assumed to be a string consisting of a
  single term. This section describes the more complex query types supported 
  by FTS3 tables, and how they may be utilized by specifying a more
  complex query expression as the right-hand operand of a MATCH operator.

<p>
  FTS3 tables support three basic query types:

<ul>
  <li><p><b>Token or token prefix queries</b>. 
................................................................................
  <i>-- ("driver" may also appear in the title, but this alone will not satisfy the</i>.
  <i>-- query criteria).</i>
  SELECT * FROM docs WHERE body MATCH 'title:linux driver';
}]

<ul>
  <li><p><b>Phrase queries</b>.
    A phrase query is a query that retrieves all documents that contain a
    nominated set of terms or term prefixes in a specified order with no
    intervening tokens. Phrase queries are specified by enclosing a space
    separated sequence of terms or term prefixes in double quotes (").
    For example:
</ul>

[Code {
................................................................................
  Stemming algorithm</a>. This tokenizer uses the same rules to separate
  the input document into terms, but as well as folding all terms to lower
  case it uses the Porter Stemming algorithm to reduce related English language
  words to a common root. For example, using the same input document as in the
  paragraph above, the porter tokenizer extracts the following tokens:
  "right now thei veri frustrat". Even though some of these terms are not even
  English words, in some cases using them to build the full-text index is more
  useful than the more intelligible output produced by the simple tokenizer.
  Using the porter tokenizer, the document not only matches full-text queries
  such as "MATCH 'Frustrated'", but also queries such as "MATCH 'Frustration'",
  as the term "Frustration" is reduced by the Porter stemmer algorithm to 
  "frustrat" - just as "Frustrated" is. So, when using the porter tokenizer,
  FTS3 is able to find not just exact matches for queried terms, but matches
  against similar English language terms. For more information on the 
  Porter Stemmer algorithm, please refer to the page linked above.

Changes to pages/news.in.

15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
  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 {2009-Dec-05} {Version 3.6.21} {
  SQLite [version 3.6.21] focuses on performance optimization.  For
  a certain set of traces, this version uses 12% fewer CPU instructions
  than the previous release (as measured by valgrind).  In addition, the
  [FTS3] extension has been through an extensive cleanup and rework and
  the [sqlite3_trace()] interface has been modified to insert 
  [bound parameter] values into its output.
}







|







15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
  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 {2009-Dec-04} {Version 3.6.21} {
  SQLite [version 3.6.21] focuses on performance optimization.  For
  a certain set of traces, this version uses 12% fewer CPU instructions
  than the previous release (as measured by valgrind).  In addition, the
  [FTS3] extension has been through an extensive cleanup and rework and
  the [sqlite3_trace()] interface has been modified to insert 
  [bound parameter] values into its output.
}