Documentation Source Text
Check-in [d29bab5808]
Not logged in

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

Overview
SHA1 Hash:d29bab58084ceb2c03a07b63bd7b5e791d5a799c
Date: 2013-03-13 17:11:37
User: drh
Comment:Documentation updates and typo fixes in preparation for the next release.
Tags And Properties
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to pages/changes.in

41
42
43
44
45
46
47


48
49
50
51
52
53
54
..
69
70
71
72
73
74
75






76
77
78
79
80
81
82
...
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
    hd_enable_main 1
  }
}

chng {2013-03-18 (3.7.16)} {
<li>Added the [PRAGMA foreign_key_check] command.
<li>Added new extended error codes for all SQLITE_CONSTRAINT errors


<li>Added SQL functions [unicode(A)] and [char(X1,...,XN)].
<li>Performance improvements for [PRAGMA incremental_vacuum], especially in
    cases where the number of free pages is greater than what will fit on a 
    single trunk page of the freelist.
<li>Improved optimization of queries containing aggregate min() or max().
<li>Enhance virtual tables so that they can potentially use an index when
    the WHERE clause contains the IN operator.
................................................................................
    shutting down the database connection.
<li>Improved error messages for invalid boolean arguments to dot-commands
    in the [command-line shell].
<li>Improved error messages for "foreign key mismatch" showing the names of
    the two tables involved.
<li>Remove all uses of umask() in the unix VFS.
<li>Added the [PRAGMA vdbe_addoptrace] and [PRAGMA vdbe_debug] commands.







<li>Bug fix: repair a long-standing problem that could cause incorrect query
    results in a 3-way or larger join that compared INTEGER fields against TEXT
    fields in two or more places.
    Ticket [http://www.sqlite.org/src/info/fc7bd6358f | fc7bd6358f]
<li>Bug fix: Issue an error message if the 16-bit reference counter on a
    view overflows due to an overly complex query.
................................................................................
    [URI filenames] are allowed to use [in-memory shared-cache | shared cache],
    so that the same
    in-memory database can be accessed from multiple database connections.
<li>Recognize and use the [coreqp | mode=memory] query parameter in
    [URI filenames].
<li>Avoid resetting the schema of [shared cache] connections when any one
    connection closes.  Instead, wait for the last connection to close before
    reseting the schema.
<li>In the [RTREE] extension, when rounding 64-bit floating point numbers
    to 32-bit for storage, always round in a direction that causes the
    bounding box to get larger.
<li>Adjust the unix driver to avoid unnecessary calls to fchown().
<li>Add interfaces sqlite3_quota_ferror() and sqlite3_quota_file_available()
    to the test_quota.c module.
<li>The [sqlite3_create_module()] and [sqlite3_create_module_v2()] interfaces







>
>







 







>
>
>
>
>
>







 







|







41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
..
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
...
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
    hd_enable_main 1
  }
}

chng {2013-03-18 (3.7.16)} {
<li>Added the [PRAGMA foreign_key_check] command.
<li>Added new extended error codes for all SQLITE_CONSTRAINT errors
<li>Added the SQLITE_READONLY_ROLLBACK extended error code for when a database
    cannot be opened because it needs rollback recovery but is read-only.
<li>Added SQL functions [unicode(A)] and [char(X1,...,XN)].
<li>Performance improvements for [PRAGMA incremental_vacuum], especially in
    cases where the number of free pages is greater than what will fit on a 
    single trunk page of the freelist.
<li>Improved optimization of queries containing aggregate min() or max().
<li>Enhance virtual tables so that they can potentially use an index when
    the WHERE clause contains the IN operator.
................................................................................
    shutting down the database connection.
<li>Improved error messages for invalid boolean arguments to dot-commands
    in the [command-line shell].
<li>Improved error messages for "foreign key mismatch" showing the names of
    the two tables involved.
<li>Remove all uses of umask() in the unix VFS.
<li>Added the [PRAGMA vdbe_addoptrace] and [PRAGMA vdbe_debug] commands.
<li>Change to use strncmp() or the equivalent instead of memcmp() when 
    comparing non-zero-terminated strings.
<li>Update cygwin interfaces to omit deprecated API calls.
<li>Enhance the [spellfix1] extension so that the edit distance cost table can
    be changed at runtime by inserting a string like 'edit_cost_table=TABLE' 
    into the "command" field.

<li>Bug fix: repair a long-standing problem that could cause incorrect query
    results in a 3-way or larger join that compared INTEGER fields against TEXT
    fields in two or more places.
    Ticket [http://www.sqlite.org/src/info/fc7bd6358f | fc7bd6358f]
<li>Bug fix: Issue an error message if the 16-bit reference counter on a
    view overflows due to an overly complex query.
................................................................................
    [URI filenames] are allowed to use [in-memory shared-cache | shared cache],
    so that the same
    in-memory database can be accessed from multiple database connections.
<li>Recognize and use the [coreqp | mode=memory] query parameter in
    [URI filenames].
<li>Avoid resetting the schema of [shared cache] connections when any one
    connection closes.  Instead, wait for the last connection to close before
    resetting the schema.
<li>In the [RTREE] extension, when rounding 64-bit floating point numbers
    to 32-bit for storage, always round in a direction that causes the
    bounding box to get larger.
<li>Adjust the unix driver to avoid unnecessary calls to fchown().
<li>Add interfaces sqlite3_quota_ferror() and sqlite3_quota_file_available()
    to the test_quota.c module.
<li>The [sqlite3_create_module()] and [sqlite3_create_module_v2()] interfaces

Changes to pages/lang.in

2053
2054
2055
2056
2057
2058
2059
2060
2061
2062
2063
2064
2065
2066
2067
  or UPDATE statement, exclusive of statements in lower-level triggers.
  ^The changes() SQL function is a wrapper around the [sqlite3_changes()]
  C/C++ function and hence follows the same rules for counting changes.
}

funcdef {char(X1,X2,...,XN)} {} {
  ^The char(X1,X2,...,XN) function returns a string composed of characters having the
   unicode code point values of integers X1 thorugh XN, respectively.
}

funcdef {coalesce(X,Y,...)} {} {
  ^The coalesce() function returns a copy of its first non-NULL argument, or
  NULL if all arguments are NULL.  ^Coalesce() must be at least 
  2 arguments.
}







|







2053
2054
2055
2056
2057
2058
2059
2060
2061
2062
2063
2064
2065
2066
2067
  or UPDATE statement, exclusive of statements in lower-level triggers.
  ^The changes() SQL function is a wrapper around the [sqlite3_changes()]
  C/C++ function and hence follows the same rules for counting changes.
}

funcdef {char(X1,X2,...,XN)} {} {
  ^The char(X1,X2,...,XN) function returns a string composed of characters having the
   unicode code point values of integers X1 through XN, respectively.
}

funcdef {coalesce(X,Y,...)} {} {
  ^The coalesce() function returns a copy of its first non-NULL argument, or
  NULL if all arguments are NULL.  ^Coalesce() must be at least 
  2 arguments.
}

Changes to pages/news.in

14
15
16
17
18
19
20
















21
22
23
24
25
26
27
...
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
  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 "<blockquote>$txt</blockquote>"
  hd_puts "<hr width=\"50%\">"
}

















newsitem {2013-Jan-09} {Release 3.7.15.2} {
  SQLite [version 3.7.15.2] is a patch release that fixes a single bug
  that was introduced in version [version 3.7.15].  The fix is a 4-character
  edit to a single line of code.  Other than this 4-character change and
  the update of the version number, nothing has changed from
  [version 3.7.15.1].
................................................................................

newsitem {2012-May-14} {Version 3.7.12} {
  SQLite [version 3.7.12] is a regularly scheduled maintenance release.
  This release contains several new optimizations and bug fixes and upgrading
  is recommended.  See the [version 3.7.12 | change summary] for details.
}

newsitem {2012-March-20} {Version 3.7.11} {
  SQLite [version 3.7.11] is a regularly scheduled maintenance release
  which was rushed out early due to a 
  [http://www.sqlite.org/src/info/b7c8682cc1 | bug in the query optimizer]
  introduced in the previous release.  The bug is obscure - it changes
  a LEFT JOIN into an INNER JOIN in some cases when there is a 3-way join
  and OR terms in the WHERE clause.  But it was considered serious enough to
  rush out a fix.  Apart from this one problem, SQLite [version 3.7.10] has
  not given any trouble.  Upgrading to [version 3.7.11] from versions
  3.7.6.3, 3.7.7, 3.7.7.1, 3.7.8, or 3.7.9 is
  optional.  Upgrading from other releases, including the previous release
  3.7.10, is recommended.

  Other enhancements found in this release are enumerated in the
  [version 3.7.11 | change log].
}

newsitem {2012-January-16} {Version 3.7.10} {
  SQLite [version 3.7.10] is a regularly scheduled maintenance release.
  Upgrading from version 3.7.6.3, 3.7.7, 3.7.7.1, 3.7.8, or 3.7.9 is
  optional.  Upgrading from other releases is recommended.

  The [SQLITE_CONFIG_PCACHE] mechanism has been replaced with
  [SQLITE_CONFIG_PCACHE2].  If you do not know what this mechanism
  is (it is an extreme corner-case and is seldom used) then this 
  change will not effect you in the least.

  The default [schema format number] for new database files has changed
  from 1 to 4.  SQLite has been able to generate and read database files
  using schema format 4 for six years.  But up unto now, the default
  schema format has been 1 so that older versions of SQLite could read
  and write databases generated by newer versions of SQLite.  But those
  older versions of SQLite have become so scarce now that it seems 
  reasonable to make the new format the default.

  SQLite is changing some of the assumptions it makes above the behavior
  of disk drives and flash memory devices during a sudden power loss.
  This change is completely transparent to applications.
  Read about the [powersafe overwrite] property for additional information.

  Lots of new interfaces have been added in this release:
  <ul>
  <li> [sqlite3_db_release_memory()]
  <li> [PRAGMA shrink_memory]
  <li> [sqlite3_db_filename()]
  <li> [sqlite3_stmt_busy()]
  <li> [sqlite3_uri_boolean()]
  <li> [sqlite3_uri_int64()]
  </ul>

  The [PRAGMA cache_size] statement has been enhanced.  Formerly, you would
  use this statement to tell SQLite how many pages of the database files it
  should hold in its cache at once.  The total memory requirement would
  depend on the database page size. Now, if you give [PRAGMA cache_size]
  a negative value -N, it will allocate roughly N 
  [http://en.wikipedia.org/wiki/Kibibyte | kibibytes] of memory to cache,
  divided up according to page size.  This enhancement allows programs to
  more easily control their memory usage.

  There have been several obscure bug fixes.  One noteworthy bug,
  ticket [http://www.sqlite.org/src/info/ff5be73dee | ff5be73dee],
  could in theory result in a corrupt database file if a power loss
  occurred at just the wrong moment on an unusually cantankerous disk
  drive.  But that is mostly a theoretical concern and is very unlikely
  to happen in practice.  The bug was found during laboratory testing
  and has never been observed to occur in the wild.
}

newsitem {2011-November-01} {Version 3.7.9} {
  SQLite [version 3.7.9] is a regularly scheduled maintenance release.
  Upgrading from version 3.7.6.3, 3.7.7, 3.7.7.1, and 3.7.8 is optional.
  Upgrading from other versions is recommended.

  The [SQLITE_ENABLE_STAT2] compile-time option is now a no-op.  The enhanced
  query-planner functionality formerly available using SQLITE_ENABLE_STAT2
  is now available through [SQLITE_ENABLE_STAT3].  The enhanced query planning
  is still disabled by default.  However, future releases of SQLite might
  convert STAT3 from an enable-option to a disable-option so that it is 
  available by default and is only omitted upon request.

  The [FTS4] full-text search engine has been enhanced such that tokens in
  the search string that begin with "&#94" must be the first token in their
  respective columns in order to match.   Formerly, "&#94;" characters in the
  search string were simply ignored.  Hence, if a legacy application was
  including "&#94;" characters in FTS4 search strings, thinking that they would
  always be ignored, then those legacy applications might break with this
  update.  The fix is simply remove the "&#94;" characters from the search 
  string.

  See the [version 3.7.9 | change summary] for additional changes associated
  with this release.  
}

newsitem {2011-September-19} {Version 3.7.8} {
  SQLite [version 3.7.8] is a quarterly maintenance release.  Upgrading from
  versions 3.7.6.3, 3.7.7, or 3.7.7.1 is optional.  Upgrading from other
  versions is recommended.

  This release features a new "external merge sort" algorithm used to
  implement ORDER BY and GROUP BY and also to presort the content of an
  index for CREATE INDEX.  The new algorithm does approximately the same
  number of comparisons and I/Os as before, but the I/Os are much more
  sequential and so runtimes are greatly reduced when the size of the
  set being sorted is larger than the filesystem cache.  The performance 
  improvement can be dramatic - orders of magnitude faster 
  for large CREATE INDEX commands.  On the other hand, 
  the code is slightly slower (1% or 2%)
  for a small CREATE INDEX.  Since CREATE INDEX is not an
  operation that commonly occurs on a speed-critical path, we feel that 
  this tradeoff is a good one.  The slight slowdown for small CREATE INDEX
  statements might be recovered in a future release.  ORDER BY and GROUP BY
  operations should now be faster for all cases, large and small.

  The query planner has been enhanced to do a better job of handling
  the DISTINCT keyword on SELECT statements.

  There has been a lot of work on the default [VFSes].  The unix VFS has
  been enhanced to include more overrideable system calls - a feature requested
  by Chromium to make it easier to build SQLite into a sandbox.  The
  windows VFS has been enhanced to be more resistant to interference from
  anti-virus software.

  Every version of SQLite is better tested than the previous, and 3.7.8
  is no exception to this rule.  Version 3.7.8 has been used internally by
  the SQLite team for mission critical functions and has performed flawlessly.
  And, of course, it passes our rigorous [testing] procedures with no
  problems detected.  Version 3.7.8 is recommended for all new development.
}


</tcl>

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







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







 







<
<
<
<
<
<
<
<
<
<
<
<

<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<



14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
...
173
174
175
176
177
178
179












180





















































































































181
182
183
  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 "<blockquote>$txt</blockquote>"
  hd_puts "<hr width=\"50%\">"
}

newsitem {2013-Mar-18} {Release 3.7.16} {
  SQLite [version 3.7.16] is a regularly scheduled release of SQLite.
  This release contains several langauge enhancements and improvements
  to the query optimizer.  A list of the major enhancements and optimizations
  can be see on the [version 3.7.16 | change log].

  There was one important bug fix
  (see [http://www.sqlite.org/src/info/fc7bd6358f | Ticket fc7bd6358f])
  that addresses an incorrect query result that could have occurred in
  a three-way join where the join constraints compared INTEGER columns
  to TEXT columns.  This issue had been in the code for time out of mind
  and had never before been reported, so we surmise that it is very obscure.
  Nevertheless, all users are advised to upgrade to avoid any future problems
  associated with this issue.
}

newsitem {2013-Jan-09} {Release 3.7.15.2} {
  SQLite [version 3.7.15.2] is a patch release that fixes a single bug
  that was introduced in version [version 3.7.15].  The fix is a 4-character
  edit to a single line of code.  Other than this 4-character change and
  the update of the version number, nothing has changed from
  [version 3.7.15.1].
................................................................................

newsitem {2012-May-14} {Version 3.7.12} {
  SQLite [version 3.7.12] is a regularly scheduled maintenance release.
  This release contains several new optimizations and bug fixes and upgrading
  is recommended.  See the [version 3.7.12 | change summary] for details.
}



































































































































</tcl>

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

Changes to pages/oldnews.in

6
7
8
9
10
11
12

































































































































13
14
15
16
17
18
19
  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 "<blockquote>$txt</blockquote>"
  hd_puts "<hr width=\"50%\">"
}


































































































































newsitem {2011-June-28} {Version 3.7.7.1} {
  SQLite [version 3.7.7.1] adds a one-line bug fix to 3.7.7 to fix
  [http://www.sqlite.org/src/info/25ee812710 | a problem]
  causing [PRAGMA case_sensitive_like] statements compiled using the legacy
  [sqlite3_prepare()] interface to fail with an [SQLITE_SCHEMA] error. Because
  [sqlite3_exec()] uses sqlite3_prepare() internally, the problem also affects







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
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
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
  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 "<blockquote>$txt</blockquote>"
  hd_puts "<hr width=\"50%\">"
}

newsitem {2012-March-20} {Version 3.7.11} {
  SQLite [version 3.7.11] is a regularly scheduled maintenance release
  which was rushed out early due to a 
  [http://www.sqlite.org/src/info/b7c8682cc1 | bug in the query optimizer]
  introduced in the previous release.  The bug is obscure - it changes
  a LEFT JOIN into an INNER JOIN in some cases when there is a 3-way join
  and OR terms in the WHERE clause.  But it was considered serious enough to
  rush out a fix.  Apart from this one problem, SQLite [version 3.7.10] has
  not given any trouble.  Upgrading to [version 3.7.11] from versions
  3.7.6.3, 3.7.7, 3.7.7.1, 3.7.8, or 3.7.9 is
  optional.  Upgrading from other releases, including the previous release
  3.7.10, is recommended.

  Other enhancements found in this release are enumerated in the
  [version 3.7.11 | change log].
}

newsitem {2012-January-16} {Version 3.7.10} {
  SQLite [version 3.7.10] is a regularly scheduled maintenance release.
  Upgrading from version 3.7.6.3, 3.7.7, 3.7.7.1, 3.7.8, or 3.7.9 is
  optional.  Upgrading from other releases is recommended.

  The [SQLITE_CONFIG_PCACHE] mechanism has been replaced with
  [SQLITE_CONFIG_PCACHE2].  If you do not know what this mechanism
  is (it is an extreme corner-case and is seldom used) then this 
  change will not effect you in the least.

  The default [schema format number] for new database files has changed
  from 1 to 4.  SQLite has been able to generate and read database files
  using schema format 4 for six years.  But up unto now, the default
  schema format has been 1 so that older versions of SQLite could read
  and write databases generated by newer versions of SQLite.  But those
  older versions of SQLite have become so scarce now that it seems 
  reasonable to make the new format the default.

  SQLite is changing some of the assumptions it makes above the behavior
  of disk drives and flash memory devices during a sudden power loss.
  This change is completely transparent to applications.
  Read about the [powersafe overwrite] property for additional information.

  Lots of new interfaces have been added in this release:
  <ul>
  <li> [sqlite3_db_release_memory()]
  <li> [PRAGMA shrink_memory]
  <li> [sqlite3_db_filename()]
  <li> [sqlite3_stmt_busy()]
  <li> [sqlite3_uri_boolean()]
  <li> [sqlite3_uri_int64()]
  </ul>

  The [PRAGMA cache_size] statement has been enhanced.  Formerly, you would
  use this statement to tell SQLite how many pages of the database files it
  should hold in its cache at once.  The total memory requirement would
  depend on the database page size. Now, if you give [PRAGMA cache_size]
  a negative value -N, it will allocate roughly N 
  [http://en.wikipedia.org/wiki/Kibibyte | kibibytes] of memory to cache,
  divided up according to page size.  This enhancement allows programs to
  more easily control their memory usage.

  There have been several obscure bug fixes.  One noteworthy bug,
  ticket [http://www.sqlite.org/src/info/ff5be73dee | ff5be73dee],
  could in theory result in a corrupt database file if a power loss
  occurred at just the wrong moment on an unusually cantankerous disk
  drive.  But that is mostly a theoretical concern and is very unlikely
  to happen in practice.  The bug was found during laboratory testing
  and has never been observed to occur in the wild.
}

newsitem {2011-November-01} {Version 3.7.9} {
  SQLite [version 3.7.9] is a regularly scheduled maintenance release.
  Upgrading from version 3.7.6.3, 3.7.7, 3.7.7.1, and 3.7.8 is optional.
  Upgrading from other versions is recommended.

  The [SQLITE_ENABLE_STAT2] compile-time option is now a no-op.  The enhanced
  query-planner functionality formerly available using SQLITE_ENABLE_STAT2
  is now available through [SQLITE_ENABLE_STAT3].  The enhanced query planning
  is still disabled by default.  However, future releases of SQLite might
  convert STAT3 from an enable-option to a disable-option so that it is 
  available by default and is only omitted upon request.

  The [FTS4] full-text search engine has been enhanced such that tokens in
  the search string that begin with "&#94" must be the first token in their
  respective columns in order to match.   Formerly, "&#94;" characters in the
  search string were simply ignored.  Hence, if a legacy application was
  including "&#94;" characters in FTS4 search strings, thinking that they would
  always be ignored, then those legacy applications might break with this
  update.  The fix is simply remove the "&#94;" characters from the search 
  string.

  See the [version 3.7.9 | change summary] for additional changes associated
  with this release.  
}

newsitem {2011-September-19} {Version 3.7.8} {
  SQLite [version 3.7.8] is a quarterly maintenance release.  Upgrading from
  versions 3.7.6.3, 3.7.7, or 3.7.7.1 is optional.  Upgrading from other
  versions is recommended.

  This release features a new "external merge sort" algorithm used to
  implement ORDER BY and GROUP BY and also to presort the content of an
  index for CREATE INDEX.  The new algorithm does approximately the same
  number of comparisons and I/Os as before, but the I/Os are much more
  sequential and so runtimes are greatly reduced when the size of the
  set being sorted is larger than the filesystem cache.  The performance 
  improvement can be dramatic - orders of magnitude faster 
  for large CREATE INDEX commands.  On the other hand, 
  the code is slightly slower (1% or 2%)
  for a small CREATE INDEX.  Since CREATE INDEX is not an
  operation that commonly occurs on a speed-critical path, we feel that 
  this tradeoff is a good one.  The slight slowdown for small CREATE INDEX
  statements might be recovered in a future release.  ORDER BY and GROUP BY
  operations should now be faster for all cases, large and small.

  The query planner has been enhanced to do a better job of handling
  the DISTINCT keyword on SELECT statements.

  There has been a lot of work on the default [VFSes].  The unix VFS has
  been enhanced to include more overrideable system calls - a feature requested
  by Chromium to make it easier to build SQLite into a sandbox.  The
  windows VFS has been enhanced to be more resistant to interference from
  anti-virus software.

  Every version of SQLite is better tested than the previous, and 3.7.8
  is no exception to this rule.  Version 3.7.8 has been used internally by
  the SQLite team for mission critical functions and has performed flawlessly.
  And, of course, it passes our rigorous [testing] procedures with no
  problems detected.  Version 3.7.8 is recommended for all new development.
}

newsitem {2011-June-28} {Version 3.7.7.1} {
  SQLite [version 3.7.7.1] adds a one-line bug fix to 3.7.7 to fix
  [http://www.sqlite.org/src/info/25ee812710 | a problem]
  causing [PRAGMA case_sensitive_like] statements compiled using the legacy
  [sqlite3_prepare()] interface to fail with an [SQLITE_SCHEMA] error. Because
  [sqlite3_exec()] uses sqlite3_prepare() internally, the problem also affects

Changes to pages/pragma.in

970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
    <p>^(<b>PRAGMA foreign_key_list(</b><i>table-name</i><b>);</b></p>

    <p>This pragma returns one row for each foreign key that references
    a column in the argument table.)^
}

Pragma foreign_key_check {
    <p>^(<b>PRAGMA foreign_key_check;)^</b></p>

    <p>The foreign_key_check pragma checks the database for 
    [foreign key constraints] that are violated and returns one row of
    output for each violation.  There are four columns in each result row.
    The first column is the name of the table that contains the REFERENCES
    clause.  The second column is the [rowid] of the row the that that
    contains the invalid REFERENCES clause.  The third column is the name
    of the table that is referred to. The fourth column is the index of
    the specific foreign key constraint that failed.  The fourth column
    in the output of the foreign_key_check pragma is the same integer as
    the first column in the output of the foreign_key_list pragma.</p>
}

Pragma freelist_count {
    <p>^(<b>PRAGMA freelist_count;</b></p>
    <p>Return the number of unused pages in the database file.)^</p>
}








|





|




|







970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
    <p>^(<b>PRAGMA foreign_key_list(</b><i>table-name</i><b>);</b></p>

    <p>This pragma returns one row for each foreign key that references
    a column in the argument table.)^
}

Pragma foreign_key_check {
    <p><b>^(PRAGMA foreign_key_check;)^</b></p>

    <p>The foreign_key_check pragma checks the database for 
    [foreign key constraints] that are violated and returns one row of
    output for each violation.  There are four columns in each result row.
    The first column is the name of the table that contains the REFERENCES
    clause.  The second column is the [rowid] of the row that
    contains the invalid REFERENCES clause.  The third column is the name
    of the table that is referred to. The fourth column is the index of
    the specific foreign key constraint that failed.  The fourth column
    in the output of the foreign_key_check pragma is the same integer as
    the first column in the output of the [foreign_key_list pragma].</p>
}

Pragma freelist_count {
    <p>^(<b>PRAGMA freelist_count;</b></p>
    <p>Return the number of unused pages in the database file.)^</p>
}

Changes to pages/spellfix1.in

196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
...
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
...
334
335
336
337
338
339
340









341
342
343
344

345
346
347
348
349
350
351
...
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
...
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
...
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
the form "top=N" in the WHERE clause of the query.

<dt><p><b>scope</b><dd>
(HIDDEN)  For any query, this value is the same on all
rows.  The scope is a measure of how widely the virtual
table looks for matching words.  Smaller values of
scope cause a broader search.  The scope is normally
choosen automatically and is capped at 4.  Applications
can change the scope by including a term of the form
"scope=N" in the WHERE clause of the query.  Increasing
the scope will make the query run faster, but will reduce
the possible corrections.

<dt><p><b>srchcnt</b><dd>
(HIDDEN)  For any query, this value is the same on all
................................................................................
is exposed as spellfix1_editdist(X,Y).  The edit distance function
returns the "cost" of converting X into Y.  Some transformations
cost more than others.  Changing one vowel into a different vowel,
for example is relatively cheap, as is doubling a constant, or
omitting the second character of a double-constant.  Other transformations
or more expensive.  The idea is that the edit distance function returns
a low cost of words that are similar and a higher cost for words
that are futher apart.  In this implementation, the maximum cost
of any single-character edit (delete, insert, or substitute) is 100,
with lower costs for some edits (such as transforming vowels).

<p>The "score" for a comparison is the edit distance between the pattern
and the word, adjusted down by the base-2 logorithm of the word rank.
For example, a match with distance 100 but rank 1000 would have a
score of 122 (= 100 - log2(1000) + 32) where as a match with distance
100 with a rank of 1 would have a score of 131 (100 - log2(1) + 32).
(NB:  The constant 32 is added to each score to keep it from going
negative in case the edit distance is zero.)  In this way, frequently
used words get a slightly lower cost which tends to move them toward
the top of the list of alternative spellings.
................................................................................
when the virtual table is created.
For example:

<blockquote><pre>
CREATE VIRTUAL TABLE demo2 USING spellfix1(edit_cost_table=APPCOST);
</pre></blockquote>










<p>In the example above, the APPCOST table would be interrogated to find
the edit distance coefficients.  It is the presence of the "edit_cost_table="
parameter to the spellfix1 module name that causes editdist3() to be used
in place of the built-in edit distance function.


<p>The edit distance coefficients are normally read from the APPCOST table
once and there after stored in memory.  Hence, run-time changes to the
APPCOST table will not normally effect the edit distance results.
However, inserting the special string 'reset' into the "command" column of the
virtual table causes the edit distance coefficients to be reread the
APPCOST table.  Hence, applications should run a SQL statement similar
................................................................................
<li>Silent "d" in words beginning with "dj":  djinn, Djikarta
<li>Silent "k" in words beginning with "kn":  knight, Knuthson
<li>Silent "g" in words beginning with "gn":  gnarly, gnome, gnat
<li>"Mac" versus "Mc" beginning Scottish surnames
<li>"Tch" sounds in Slavic words:  Tchaikovsky vs. Chaykovsky
<li>The letter "j" pronounced like "h" in Spanish:  LaJolla
<li>Words beginning with "wr" versus "r":  write vs. rite
<li>Miscellanous problem words such as "debt", "tsetse",
      "Nguyen", "Van Nuyes".
</ul>

<h2>Auxiliary Functions</h2>

<p>The source code module that implements the spellfix1 virtual table also
implements several SQL functions that might be useful to applications
................................................................................
<dt><p><b>spellfix1_phonehash(X)</b><dd>
This routine constructs a phonetic hash of the pure ascii input word X
and returns that hash.  This routine is used internally by spellfix1 in
order to transform the K1 column of the shadow table into the K2
column.

<dt><p><b>spellfix1_scriptcode(X)</b><dd>
Given an input string X, this routine attempts to determin the dominant
script of that input and returns the ISO-15924 numeric code for that
script.  The current implementation understands the following scripts:
<ul>
<li> 215 - Latin
<li> 220 - Cyrillic
<li> 200 - Greek
</ul>
................................................................................
The only requirement is that the
table must contain the four columns show above, with exactly the names shown.

<p>The iLang column is a non-negative integer that identifies a set of costs
appropriate for a particular language.  The editdist3 function will only use
a single iLang value for any given edit-distance computation.  The default
value is 0.  It is recommended that applications that only need to use a
single langauge always use iLang==0 for all entries.

<p>The iCost column is the numeric cost of transforming cFrom into cTo.  This
value should be a non-negative integer, and should probably be less than 100.
The default single-character insertion and deletion costs are 100 and the
default single-character to single-character substitution cost is 150.  A
cost of 10000 or more is considered "infinite" and causes the rule to be
ignored.







|







 







|




|







 







>
>
>
>
>
>
>
>
>
|


|
>







 







|







 







|







 







|







196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
...
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
...
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
...
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
...
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
...
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
the form "top=N" in the WHERE clause of the query.

<dt><p><b>scope</b><dd>
(HIDDEN)  For any query, this value is the same on all
rows.  The scope is a measure of how widely the virtual
table looks for matching words.  Smaller values of
scope cause a broader search.  The scope is normally
chosen automatically and is capped at 4.  Applications
can change the scope by including a term of the form
"scope=N" in the WHERE clause of the query.  Increasing
the scope will make the query run faster, but will reduce
the possible corrections.

<dt><p><b>srchcnt</b><dd>
(HIDDEN)  For any query, this value is the same on all
................................................................................
is exposed as spellfix1_editdist(X,Y).  The edit distance function
returns the "cost" of converting X into Y.  Some transformations
cost more than others.  Changing one vowel into a different vowel,
for example is relatively cheap, as is doubling a constant, or
omitting the second character of a double-constant.  Other transformations
or more expensive.  The idea is that the edit distance function returns
a low cost of words that are similar and a higher cost for words
that are further apart.  In this implementation, the maximum cost
of any single-character edit (delete, insert, or substitute) is 100,
with lower costs for some edits (such as transforming vowels).

<p>The "score" for a comparison is the edit distance between the pattern
and the word, adjusted down by the base-2 logarithm of the word rank.
For example, a match with distance 100 but rank 1000 would have a
score of 122 (= 100 - log2(1000) + 32) where as a match with distance
100 with a rank of 1 would have a score of 131 (100 - log2(1) + 32).
(NB:  The constant 32 is added to each score to keep it from going
negative in case the edit distance is zero.)  In this way, frequently
used words get a slightly lower cost which tends to move them toward
the top of the list of alternative spellings.
................................................................................
when the virtual table is created.
For example:

<blockquote><pre>
CREATE VIRTUAL TABLE demo2 USING spellfix1(edit_cost_table=APPCOST);
</pre></blockquote>

<p>The [editdist3()] edit-distance function can also be selected or 
deselected at run-time by inserting an appropriate string into the
"command" column of the virtual table:</p>

<blockquote><pre>
INSERT INTO demo2(command) VALUES('edit_cost_table=APPCOST');
</pre></blockquote>


<p>In the examples above, the APPCOST table would be interrogated to find
the edit distance coefficients.  It is the presence of the "edit_cost_table="
parameter to the spellfix1 module name that causes editdist3() to be used
in place of the built-in edit distance function.  If APPCOST is an empty
string, then the built-in Wagner edit-distance function is used.

<p>The edit distance coefficients are normally read from the APPCOST table
once and there after stored in memory.  Hence, run-time changes to the
APPCOST table will not normally effect the edit distance results.
However, inserting the special string 'reset' into the "command" column of the
virtual table causes the edit distance coefficients to be reread the
APPCOST table.  Hence, applications should run a SQL statement similar
................................................................................
<li>Silent "d" in words beginning with "dj":  djinn, Djikarta
<li>Silent "k" in words beginning with "kn":  knight, Knuthson
<li>Silent "g" in words beginning with "gn":  gnarly, gnome, gnat
<li>"Mac" versus "Mc" beginning Scottish surnames
<li>"Tch" sounds in Slavic words:  Tchaikovsky vs. Chaykovsky
<li>The letter "j" pronounced like "h" in Spanish:  LaJolla
<li>Words beginning with "wr" versus "r":  write vs. rite
<li>Miscellaneous problem words such as "debt", "tsetse",
      "Nguyen", "Van Nuyes".
</ul>

<h2>Auxiliary Functions</h2>

<p>The source code module that implements the spellfix1 virtual table also
implements several SQL functions that might be useful to applications
................................................................................
<dt><p><b>spellfix1_phonehash(X)</b><dd>
This routine constructs a phonetic hash of the pure ascii input word X
and returns that hash.  This routine is used internally by spellfix1 in
order to transform the K1 column of the shadow table into the K2
column.

<dt><p><b>spellfix1_scriptcode(X)</b><dd>
Given an input string X, this routine attempts to determine the dominant
script of that input and returns the ISO-15924 numeric code for that
script.  The current implementation understands the following scripts:
<ul>
<li> 215 - Latin
<li> 220 - Cyrillic
<li> 200 - Greek
</ul>
................................................................................
The only requirement is that the
table must contain the four columns show above, with exactly the names shown.

<p>The iLang column is a non-negative integer that identifies a set of costs
appropriate for a particular language.  The editdist3 function will only use
a single iLang value for any given edit-distance computation.  The default
value is 0.  It is recommended that applications that only need to use a
single language always use iLang==0 for all entries.

<p>The iCost column is the numeric cost of transforming cFrom into cTo.  This
value should be a non-negative integer, and should probably be less than 100.
The default single-character insertion and deletion costs are 100 and the
default single-character to single-character substitution cost is 150.  A
cost of 10000 or more is considered "infinite" and causes the rule to be
ignored.

Changes to pages/tempfiles.in

178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
Shared memory files only exist while running in WAL mode.
</p>

<p>
The shared-memory file contains no persistent content.  The only purpose
of the shared-memory file is to provide a block of shared memory for use
by multiple processes all accessing the same database in WAL mode.
If the [VFS] is able to providean  alternative method for accessing shared
memory, then that alternative method might be used rather than the
shared-memory file.  For example, if [PRAGMA locking_mode] is set to
EXCLUSIVE (meaning that only one process is able to access the database
file) then the shared memory will be allocated from heap rather than out
of the shared-memory file, and the shared-memory file will never be
created.
</p>







|







178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
Shared memory files only exist while running in WAL mode.
</p>

<p>
The shared-memory file contains no persistent content.  The only purpose
of the shared-memory file is to provide a block of shared memory for use
by multiple processes all accessing the same database in WAL mode.
If the [VFS] is able to provide an alternative method for accessing shared
memory, then that alternative method might be used rather than the
shared-memory file.  For example, if [PRAGMA locking_mode] is set to
EXCLUSIVE (meaning that only one process is able to access the database
file) then the shared memory will be allocated from heap rather than out
of the shared-memory file, and the shared-memory file will never be
created.
</p>

Changes to pages/vfs.in

175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
The following are other VFS implementations available in the public
SQLite source tree:
</p>

<ul>
<li><p>
[http://www.sqlite.org/src/doc/trunk/src/test_demovfs.c | test_demovfs.c] - 
This file implements a very simple VFS named "demo" that uss POSIX 
functions such as
open(), read(), write(), fsync(), close(), fsync(), sleep(), time(),
and so forth.  This VFS only works on unix systems.  But it is not
intended as a replacement for the standard "unix" VFS used by default
on unix platforms.  The "demo" VFS is deliberately kept very simple
so that it can be used as a learning aid or as template for building
other VFSes or for porting SQLite to new operating systems.







|







175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
The following are other VFS implementations available in the public
SQLite source tree:
</p>

<ul>
<li><p>
[http://www.sqlite.org/src/doc/trunk/src/test_demovfs.c | test_demovfs.c] - 
This file implements a very simple VFS named "demo" that uses POSIX 
functions such as
open(), read(), write(), fsync(), close(), fsync(), sleep(), time(),
and so forth.  This VFS only works on unix systems.  But it is not
intended as a replacement for the standard "unix" VFS used by default
on unix platforms.  The "demo" VFS is deliberately kept very simple
so that it can be used as a learning aid or as template for building
other VFSes or for porting SQLite to new operating systems.