Documentation Source Text

Check-in [4fb953fec0]
Login

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

Overview
Comment:Updates to the ANALYZE and FTS4 documentation to describe the latest changes. Updates to the changes.html file as we move closer to the 3.7.6 release. Added amalgamation32k to the download.html page.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 4fb953fec047f7c2c492e8ec86d5d9a3fb7f6dcb
User & Date: drh 2011-04-05 14:31:35.649
Context
2011-04-05
22:22
Change the target release date for 3.7.6 to April 12. (check-in: 76d31b952b user: drh tags: trunk)
14:31
Updates to the ANALYZE and FTS4 documentation to describe the latest changes. Updates to the changes.html file as we move closer to the 3.7.6 release. Added amalgamation32k to the download.html page. (check-in: 4fb953fec0 user: drh tags: trunk)
2011-03-30
19:11
Fix an broken link in the compile.in document. (check-in: 21e4585531 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to art/syntax/analyze-stmt.gif.

cannot compute difference between binary files

Changes to art/syntax/bubble-generator-data.tcl.
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
       {line ALTER TABLE {optx /database-name .} /table-name}
       {tailbranch
          {line RENAME TO /new-table-name}
          {line ADD {optx COLUMN} column-def}
       }
  }
  analyze-stmt {
     line ANALYZE {or nil /database-name /table-name
                    {line /database-name . /table-name}}
  }
  attach-stmt {
     line ATTACH {or DATABASE nil} expr AS /database-name
  }
  begin-stmt {
     line BEGIN {or nil DEFERRED IMMEDIATE EXCLUSIVE}
          {optx TRANSACTION}







|
|







51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
       {line ALTER TABLE {optx /database-name .} /table-name}
       {tailbranch
          {line RENAME TO /new-table-name}
          {line ADD {optx COLUMN} column-def}
       }
  }
  analyze-stmt {
     line ANALYZE {or nil /database-name /table-or-index-name
                    {line /database-name . /table-or-index-name}}
  }
  attach-stmt {
     line ATTACH {or DATABASE nil} expr AS /database-name
  }
  begin-stmt {
     line BEGIN {or nil DEFERRED IMMEDIATE EXCLUSIVE}
          {optx TRANSACTION}
Changes to pages/changes.in.
55
56
57
58
59
60
61
62







63
64
65
66
67
68
69
<li> Integer arithmetic operations what would have resulted in overflow
     are now performed using floating-point instead.
<li> Increased the version number on the [sqlite3_vfs | VFS object] to
     3 and added new methods xSetSysCall, xGetSysCall, and xNextSysCall
     used for doing full-coverage testing.
<li> Increase the maximum value of [SQLITE_MAX_ATTACHED] from 30 to 62
     (though the default value remains at 10).
<li> Enhancements to FTS4:  <i>Description TBD...</i>







}

chng {2011 February 01 (3.7.5)} {
<li> Added the [sqlite3_vsnprintf()] interface.
<li> Added the [SQLITE_DBSTATUS_LOOKASIDE_HIT],
     [SQLITE_DBSTATUS_LOOKASIDE_MISS_SIZE], and
     [SQLITE_DBSTATUS_LOOKASIDE_MISS_FULL] options for the







|
>
>
>
>
>
>
>







55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
<li> Integer arithmetic operations what would have resulted in overflow
     are now performed using floating-point instead.
<li> Increased the version number on the [sqlite3_vfs | VFS object] to
     3 and added new methods xSetSysCall, xGetSysCall, and xNextSysCall
     used for doing full-coverage testing.
<li> Increase the maximum value of [SQLITE_MAX_ATTACHED] from 30 to 62
     (though the default value remains at 10).
<li> Enhancements to FTS4:
     <ol type="a">
     <li> Added the [fts4aux] table
     <li> Added support for [compressed FTS4 content]
     </ol>
<li> Enhance the [ANALYZE] command to support the name of an index
     as its argument, in order to analyze just that one index.
<li> Added the "unix-excl" built-in VFS on unix and unix-like platforms.
}

chng {2011 February 01 (3.7.5)} {
<li> Added the [sqlite3_vsnprintf()] interface.
<li> Added the [SQLITE_DBSTATUS_LOOKASIDE_HIT],
     [SQLITE_DBSTATUS_LOOKASIDE_MISS_SIZE], and
     [SQLITE_DBSTATUS_LOOKASIDE_MISS_FULL] options for the
Changes to pages/download.in.
68
69
70
71
72
73
74






75
76
77
78
79
80
81

Product {sqlite-amalgamation-DATE.zip} {
  This is a snapshot (as of VERSION) of the current SQLite code under 
  development.  Use this snapshot for testing only.  This is not a release.
  This ZIP archive contains all preprocessed C code combined into a
  single source file (the <a href="amalgamation.html">amalgamation</a>).
}






Product {sqlite-tea-DATE.zip} {
  This is a snapshot (as of VERSION) of the current SQLite code under 
  development, packaged and ready to build using the
  <a href="http://www.tcl.tk/doc/tea/">Tcl Extension Architecture (TEA)</a>.
  Use this snapshot for testing only.  This is not a release.
}
Product {sqlite-shell-win32-x86-DATE.zip} {







>
>
>
>
>
>







68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87

Product {sqlite-amalgamation-DATE.zip} {
  This is a snapshot (as of VERSION) of the current SQLite code under 
  development.  Use this snapshot for testing only.  This is not a release.
  This ZIP archive contains all preprocessed C code combined into a
  single source file (the <a href="amalgamation.html">amalgamation</a>).
}
Product {sqlite-amalgamation32k-DATE.zip} {
  This is a snapshot (as of VERSION) of the current SQLite code under 
  development.  The code is split into a small number of source files,
  such that no source files is longer than 32767 lines of code.
  Use this snapshot for testing only.  This is not a release.
}
Product {sqlite-tea-DATE.zip} {
  This is a snapshot (as of VERSION) of the current SQLite code under 
  development, packaged and ready to build using the
  <a href="http://www.tcl.tk/doc/tea/">Tcl Extension Architecture (TEA)</a>.
  Use this snapshot for testing only.  This is not a release.
}
Product {sqlite-shell-win32-x86-DATE.zip} {
93
94
95
96
97
98
99






100
101
102
103
104
105
106
Heading {Source Code}

Product {sqlite-amalgamation-VVV.zip} {
  This ZIP archive contains all C source code for SQLite VERSION
  combined into a
  single source file (the <a href="amalgamation.html">amalgamation</a>).
}







Product {sqlite-autoconf-VVV.tar.gz} {
  A tarball containing the <a href="amalgamation.html">amalgamation</a>
  for SQLite VERSION together with an configure script and makefile
  for building it.
} {amalgtarball {amalgamation tarball}}








>
>
>
>
>
>







99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
Heading {Source Code}

Product {sqlite-amalgamation-VVV.zip} {
  This ZIP archive contains all C source code for SQLite VERSION
  combined into a
  single source file (the <a href="amalgamation.html">amalgamation</a>).
}

Product {sqlite-amalgamation32k-VVV.zip} {
  This ZIP archive contains all C source code for SQLite VERSION
  combined into a small number of source files, where no source file
  is longer than 32767 lines of code.
}

Product {sqlite-autoconf-VVV.tar.gz} {
  A tarball containing the <a href="amalgamation.html">amalgamation</a>
  for SQLite VERSION together with an configure script and makefile
  for building it.
} {amalgtarball {amalgamation tarball}}

Changes to pages/fts3.in.
223
224
225
226
227
228
229

230
231
232
233
234
235
236
    
  <tr><td>uncompress<td>
    This option is used to specify the uncompress function. It is an error to
    specify an uncompress function without also specifying a compress
    function.
</table>


<p>
  The compress and uncompress options allow FTS4 content to be stored in
  the database in a compressed form. Both options should be set to the name
  of an SQL scalar function registered using [sqlite3_create_function()]
  that accepts a single argument. 

<p>







>







223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
    
  <tr><td>uncompress<td>
    This option is used to specify the uncompress function. It is an error to
    specify an uncompress function without also specifying a compress
    function.
</table>

<tcl>hd_fragment *fts4compression {compressed FTS4 content}</tcl>
<p>
  The compress and uncompress options allow FTS4 content to be stored in
  the database in a compressed form. Both options should be set to the name
  of an SQL scalar function registered using [sqlite3_create_function()]
  that accepts a single argument. 

<p>
Changes to pages/lang.in.
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
<tcl>
##############################################################################
Section {ANALYZE} analyze ANALYZE

BubbleDiagram analyze-stmt 1
</tcl>

<p> ^The ANALYZE command gathers statistics about indices and stores them

in a special tables in the database where the query optimizer can use
them to help make better index choices.
^If no arguments are given, all indices in all attached databases are
analyzed.  ^If a database name is given as the argument, all indices
in that one database are analyzed.  ^If the argument is a table name,

then only indices associated with that one table are analyzed.</p>


<p> ^The default implementation stores all statistics in a single
table named <b>sqlite_stat1</b>.  ^If SQLite is compiled with the
[SQLITE_ENABLE_STAT2] option, then additional histogram data is
collected and stored in <b>sqlite_stat2</b>.
Future enhancements may create
additional tables with the same name pattern except with the "1"
or "2" changed to a different digit.</p>

<p> ^The [DROP TABLE] command does
not work on the <b>sqlite_stat1</b> or <b>sqlite_stat2</b> tables,
but all the content of those tables can be queried using [SELECT]
and can be deleted, augmented, or modified using the [DELETE],
[INSERT], and [UPDATE] commands.
Appropriate care should be used when changing the content of the statistics
tables as invalid content can cause SQLite to select inefficient
query plans.  Generally speaking, one should not modify the content of
the statistics tables by any mechanism other than invoking the
ANALYZE command.</p>

<p> ^Statistics gathered by ANALYZE are <u>not</u> automatically updated as
the content of the database changes.  If the content of the database
changes significantly, or if the database schema changes, then one should
consider rerunning the ANALYZE command in order to update the statistics.</p>






<tcl>
##############################################################################
Section {ATTACH DATABASE} attach *ATTACH

BubbleDiagram attach-stmt 1
</tcl>







|
>

|
|
|
|
>
|
>









|














>
>
>
>
>







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
<tcl>
##############################################################################
Section {ANALYZE} analyze ANALYZE

BubbleDiagram analyze-stmt 1
</tcl>

<p> ^The ANALYZE command gathers statistics about tables and
indices and stores the collected information
in a special tables in the database where the query optimizer can use
them to help make better query planning choices.
^If no arguments are given, all attached databases are
analyzed.  ^If a database name is given as the argument, then all tables
and indices in that one database are analyzed.  
^If the argument is a table name, then only that table and the
indices associated with that table are analyzed.  ^If the argument
is an index name, then only that one index is analyzed.</p>

<p> ^The default implementation stores all statistics in a single
table named <b>sqlite_stat1</b>.  ^If SQLite is compiled with the
[SQLITE_ENABLE_STAT2] option, then additional histogram data is
collected and stored in <b>sqlite_stat2</b>.
Future enhancements may create
additional tables with the same name pattern except with the "1"
or "2" changed to a different digit.</p>

<p> ^The [DROP TABLE] and [ALTER TABLE] commands do
not work on the <b>sqlite_stat1</b> or <b>sqlite_stat2</b> tables,
but all the content of those tables can be queried using [SELECT]
and can be deleted, augmented, or modified using the [DELETE],
[INSERT], and [UPDATE] commands.
Appropriate care should be used when changing the content of the statistics
tables as invalid content can cause SQLite to select inefficient
query plans.  Generally speaking, one should not modify the content of
the statistics tables by any mechanism other than invoking the
ANALYZE command.</p>

<p> ^Statistics gathered by ANALYZE are <u>not</u> automatically updated as
the content of the database changes.  If the content of the database
changes significantly, or if the database schema changes, then one should
consider rerunning the ANALYZE command in order to update the statistics.</p>

<p> The query planner might not notice manual changes to the
<b>sqlite_stat1</b> and/or <b>sqlite3_stat2</b> tables.  ^An application
can force the query planner to reread the statistics tables by running
<b>ANALYZE sqlite_master</b>. </p>

<tcl>
##############################################################################
Section {ATTACH DATABASE} attach *ATTACH

BubbleDiagram attach-stmt 1
</tcl>