Documentation Source Text

Check-in [ca4b03a1f2]
Login

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

Overview
Comment:Add news for the 3.8.1 release. Other final documentation tweaks prior to release.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: ca4b03a1f2548d08995b1536b3af195419cffde0
User & Date: drh 2013-10-17 12:54:29
Context
2013-10-17
13:52
Add the SQLITE_SOURCE_ID and sha1sum of sqlite3.c for 3.8.1 to changes.in. check-in: 7532f00f57 user: dan tags: trunk
12:54
Add news for the 3.8.1 release. Other final documentation tweaks prior to release. check-in: ca4b03a1f2 user: drh tags: trunk
2013-10-16
23:58
Fix typos in documentation. check-in: df95e4e1ec user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to pages/changes.in.

    14     14   set nChng 0
    15     15   proc chng {date desc {options {}}} {
    16     16     global nChng aChng
    17     17     set aChng($nChng) [list $date $desc $options]
    18     18     incr nChng
    19     19   }
    20     20   
    21         -chng {2013-10-18 (3.8.1)} {
           21  +chng {2013-10-17 (3.8.1)} {
    22     22   <li>Added the [unlikely()] and [likelihood()] SQL functions to be used
    23     23       as hints to the query planner.
    24     24   <li>Enhancements to the query planner:
    25     25   <ul>
    26     26   <li>Take into account the fact WHERE clause terms that cannot be used with indices
    27     27       still probably reduce the number of output rows.
    28     28   <li>Estimate the sizes of table and index rows and use the smallest applicable B-Tree
    29     29       for full scans and "count(*)" operations.
    30     30   </ul>
    31     31   <li>Added the [soft_heap_limit pragma].
    32     32   <li>Added support for [SQLITE_ENABLE_STAT4]
    33         -<li>Added support for "sz=NNN" parameters at the end of sqlite_stat1.stat fields
           33  +<li>Added support for "sz=NNN" parameters at the end of 
           34  +    [sqlite_stat1 | sqlite_stat1.stat] fields
    34     35       used to specify the average length in bytes for table and index rows.
    35     36   <li>Avoid running foreign-key constraint checks on an UPDATE if none of the
    36     37       modified columns are associated with foreign keys.
    37     38   <li>Added the [SQLITE_MINIMUM_FILE_DESCRIPTOR] compile-time option
    38     39   <li>Added the win32-longpath VFS on windows, permitting filenames up to 32K
    39     40       characters in length.
    40     41   <li>The [Date And Time Functions] are enhanced so that the current time
................................................................................
    49     50   <li>The sqlite3_analyzer utility program is updated to provide better descriptions
    50     51       and to compute a more accurate estimate for "Non-sequential pages"
    51     52   <li>Refactor the implementation of PRAGMA statements to improve parsing performance.
    52     53   <li>The directory used to hold temporary files on unix can now be set using
    53     54       the SQLITE_TMPDIR environment variable,  which takes precedence over the
    54     55       TMPDIR environment variable.  The [sqlite3_temp_directory] global variable
    55     56       still has higher precedence than both environment variables, however.
           57  +<li>Added the [PRAGMA stats] statement.
    56     58   <li><b>Bug fix:</b> Return the correct answer for "SELECT count(*) FROM table" even if
    57     59       there is a [partial index] on the table. Ticket
    58     60       [http://www.sqlite.org/src/info/a5c8ed66ca|a5c8ed66ca].
    59     61   }
    60     62   
    61     63   chng {2013-09-03 (3.8.0.2)} {
    62     64   <li>Fix a bug in the optimization that attempts to omit unused LEFT JOINs

Changes to pages/compile.in.

   603    603   
   604    604   COMPILE_OPTION {SQLITE_ENABLE_STAT3} {
   605    605     This option adds additional logic to the [ANALYZE] command and to
   606    606     the [query planner] that can help SQLite to chose a better query plan
   607    607     under certain situations.  The [ANALYZE] command is enhanced to collect
   608    608     histogram data from the left-most column of each index and store that data
   609    609     in the [sqlite_stat3] table.  The query planner will then use the
   610         -  histogram data to help it make better index choices.
          610  +  histogram data to help it make better index choices.  Note, however,
          611  +  that the use of histogram data in query planner violates the
          612  +  [query planner stability guarantee] which is important to some applications.
   611    613     <p>
   612    614   }
   613    615   
   614    616   COMPILE_OPTION {SQLITE_ENABLE_STAT4} {
   615    617     This option adds additional logic to the [ANALYZE] command and to
   616    618     the [query planner] that can help SQLite to chose a better query plan
   617    619     under certain situations.  The [ANALYZE] command is enhanced to collect
   618    620     histogram data from all columns of every index and store that data
   619    621     in the [sqlite_stat4] table.  The query planner will then use the
   620         -  histogram data to help it make better index choices.
          622  +  histogram data to help it make better index choices.  The downside of
          623  +  this compile-time option is that it violates the
          624  +  [query planner stability guarantee] making it more difficult to ensure
          625  +  consistent performance is mass-produced applications.
   621    626     <p>
   622    627     SQLITE_ENABLE_STAT4 is an enhancement of [SQLITE_ENABLE_STAT3].  STAT3
   623    628     only recorded histogram data for the left-most column of each index
   624    629     whereas the STAT4 enhancement records histograph data from all columns
   625    630     of each index.
   626    631     The [SQLITE_ENABLE_STAT3] compile-time option is a no-op and is ignored
   627    632     if the SQLITE_ENABLE_STAT4 compile-time option is used.

Changes to pages/fileformat2.in.

  1183   1183   integer is the approximate average number of rows in the index that have 
  1184   1184   the same value for the first two columns.  The N-th integer (for N>1) 
  1185   1185   is the approximate average number of rows in 
  1186   1186   the index which have the same value for the first N-1 columns.  For
  1187   1187   a K-column index, there will be K+1 integers in the stat column.  If
  1188   1188   the index is unique, then the last integer will be 1.
  1189   1189   
  1190         -<p>The list of integers in the stat column can optionally be followed
  1191         -by the keyword "unordered".  The "unordered" keyword, if it is present,
  1192         -must be separated from the last integer by a single space.  If the
         1190  +<p>^The list of integers in the stat column can optionally be followed
         1191  +by the keyword "unordered".  ^The "unordered" keyword, if it is present,
         1192  +must be separated from the last integer by a single space.  ^If the
  1193   1193   "unordered" keyword is present, then the query planner assumes that
  1194   1194   the index is unordered and will not use the index for a range query
  1195   1195   or for sorting.
         1196  +
         1197  +<p>^The list of integers in the stat column can also optionally be followed
         1198  +by a token of the form "sz=NNN" where NNN is an unsigned integer.  The
         1199  +"sz=NNN" token, if present, must be separated from the last integer by a
         1200  +single space.  ^The "sz=NNN" token means that the average row size over all 
         1201  +records of the table or
         1202  +index is NNN bytes per row.  ^The SQLite query planner might use the
         1203  +estimated row size information provided by the "sz=NNN" token
         1204  +to help it choose smaller tables and indices that require less disk I/O.
  1196   1205   
  1197   1206   <p>If the sqlite_stat1.idx column is NULL, then the sqlite_stat1.stat
  1198   1207   column contains a single integer which is the approximate number of
  1199   1208   rows in the table identified by sqlite_stat1.tbl.
  1200   1209   
  1201   1210   <tcl>hd_fragment stat2tab {sqlite_stat2}</tcl>
  1202   1211   <h4>2.5.4 The sqlite_stat2 table</h4>

Changes to pages/news.in.

    15     15     regsub -all "\n( *\n)+" $text "</p>\n\n<p>" txt
    16     16     regsub -all {[Tt]icket #(\d+)} $txt \
    17     17         {<a href="http://www.sqlite.org/cvstrac/tktview?tn=\1">\0</a>} txt
    18     18     hd_resolve "<blockquote>$txt</blockquote>"
    19     19     hd_puts "<hr width=\"50%\">"
    20     20   }
    21     21   
    22         -newsitem {2013-03-03} {Release 3.8.0.2} {
           22  +newsitem {2013-10-17} {Release 3.8.1} {
           23  +  <p>SQLite [version 3.8.1] is a regularly scheduled maintenance release.
           24  +  Upgrading from the previous release is optional, though you should upgrade
           25  +  if you are using [partial indices] as there was a 
           26  +  [http://www.sqlite.org/src/info/a5c8ed66ca|bug] related to partial
           27  +  indices in the previous release that could result in an incorrect answer
           28  +  for count(*) queries.
           29  +
           30  +  <p>The [next generation query planner] that was premiered in the previous
           31  +  release continues to work well.  
           32  +  The new query planner has been tweaked slightly
           33  +  in the current release to help it make better decisions in some
           34  +  cases, but is largely unchanged.  Two new SQL functions, [likelihood()] and
           35  +  [unlikely()], have been added to allow developers to give hints to the
           36  +  query planner without forcing the query planner into a particular decision.
           37  +
           38  +  <p>Version 3.8.1 is the first SQLite release to take into account the
           39  +  estimated size of table and index rows when choosing a query plan.
           40  +  Row size estimates are based on the declared datatypes of columns.
           41  +  For example, a column of type VARCHAR(1000) is assumed
           42  +  to use much more space than a column of type INT.  The datatype-based
           43  +  row size estimate can be
           44  +  overridden by appending a term of the form "sz=NNN" (where NNN is the
           45  +  average row size in bytes) to the end of the [sqlite_stat1|sqlite_stat1.stat]
           46  +  record for a table or index.  Currently, row sizes are only used to help the
           47  +  query planner choose between a table or one of its indices when doing a
           48  +  table scan or a count(*) operation, though future releases are likely
           49  +  to use the estimated row size in other contexts as well.  The new
           50  +  [PRAGMA stats] statement can be used to view row size estimates.
           51  +
           52  +  <p>Version 3.8.1 adds the [SQLITE_ENABLE_STAT4] compile-time option.
           53  +  STAT4 is very similar to STAT3 in that it uses samples from indices to 
           54  +  try to guess how many rows of the index will be satisfy by WHERE clause
           55  +  constraints.  The difference is that STAT4 samples all columns of the
           56  +  index whereas the older STAT3 only sampled the left-most column.  Users
           57  +  of STAT3 are encouraged to upgrade to STAT4.  Application developers should
           58  +  use STAT3 and STAT4 with caution since both options, by design, violate 
           59  +  the [query planner stability guarantee], making it more difficult to ensure
           60  +  uniform performance is widely-deployed and mass-produced embedded
           61  +  applications.
           62  +}
           63  +
           64  +newsitem {2013-09-03} {Release 3.8.0.2} {
    23     65     <p>SQLite [version 3.8.0.2] contains a one-line fix to a bug in the
    24     66     new optimization that tries to omit unused LEFT JOINs from a query.
    25     67   }
    26     68   
    27     69   newsitem {2013-08-29} {Release 3.8.0.1} {
    28     70     <p>SQLite [version 3.8.0.1] fixes some obscure bugs that were uncovered by
    29     71     users in the 3.8.0 release.  Changes from 3.8.0 are minimal.

Changes to pages/pragma.in.

  1127   1127   }
  1128   1128   
  1129   1129   Pragma stats {
  1130   1130       <p>^(<b>PRAGMA stats;</b>)^ </p>
  1131   1131       <p>This pragma returns auxiliary information about tables and
  1132   1132       indices.  The returned information is used during testing to help
  1133   1133       verify that the query planner is operating correctly.  The format
  1134         -    and meaning of this pragma will likely change from one point release
  1135         -    to the next. Because of its volatility, applications should avoid
  1136         -    using this pragma.</p>
         1134  +    and meaning of this pragma will likely change from release
         1135  +    to the next. Because of its volatility, the behavior and output
         1136  +    format of this pragma are deliberately undocumented.  This pragma is
         1137  +    intended for interactive, debugging, and testing use only.
         1138  +    Applications should avoid using this pragma.</p>
  1137   1139   }
  1138   1140   
  1139   1141   Pragma page_count {
  1140   1142       <p>^(<b>PRAGMA page_count;</b></p>
  1141   1143       <p>Return the total number of pages in the database file.</p>)^
  1142   1144   }
  1143   1145