Documentation Source Text

Check-in [f7a0591178]
Login

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

Overview
Comment:Initial documentation changes for 3.18.0. Add documentation for PRAGMA optimize and update documentation for PRAGMA integrity_check and quick_check.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: f7a0591178386a0c4433190bcc46d852765488f1
User & Date: drh 2017-03-06 20:49:15
Context
2017-03-06
20:54
Fix a typo in the 3.18 change log. check-in: 82f55fbab0 user: drh tags: trunk
20:49
Initial documentation changes for 3.18.0. Add documentation for PRAGMA optimize and update documentation for PRAGMA integrity_check and quick_check. check-in: f7a0591178 user: drh tags: trunk
19:56
Merge fixes from the 3.17 branch. check-in: 02cadace0c user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to pages/changes.in.

16
17
18
19
20
21
22




















23
24
25
26
27
28
29
set nChng 0
proc chng {date desc {options {}}} {
  global nChng aChng xrefChng
  set aChng($nChng) [list $date $desc $options]
  set xrefChng($date) $nChng
  incr nChng
}





















chng {2017-02-13 (3.17.0)} {
<li>Approximately 25% better performance from the [R-Tree extension].
    <ul>
    <li> Uses compiler built-ins (ex: __builtin_bswap32() or _byteswap_ulong())
         for byteswapping when available.
    <li> Uses the [sqlite3_blob] key/value access object instead of SQL







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







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
set nChng 0
proc chng {date desc {options {}}} {
  global nChng aChng xrefChng
  set aChng($nChng) [list $date $desc $options]
  set xrefChng($date) $nChng
  incr nChng
}

chng {2017-04-00 (3.18.0)} {
<li>Added the [PRAGMA optimize] command
<li>Enhance the [LIKE optimization] so that it works for arbitrary expressionsn on 
    the left-hand side as long as the LIKE pattern on the right-hand side does not
    begin with a digit or minus sign.
<li>Added the [sqlite3_set_last_insert_rowid()] interface and use that new interface in 
    the [FTS3], [FTS4], and [FTS5] extensions to ensure that the [sqlite3_last_insert_rowid()]
    interface always returns reasonable values.
<li>Enhance [PRAGMA integrity_check] and [PRAGMA quick_check] so that they verify
    [CHECK constraints].
<li>Enhance the query plans for joins to detect empty tables early
    halt without doing unnecessary work.
<li>Various performance improvements.
<p><b>Bug Fixes:</b>
<li>Ensure that indexed expressions with collating sequences are handled correctly.
    Fix for ticket [https://www.sqlite.org/src/info/eb703ba7b50c1a5|eb703ba7b50c1a5].
<li>Fix a bug in the 'start of ...' modifiers for the [date and time functions].
    Ticket [https://www.sqlite.org/src/info/6097cb92745327a1|6097cb92745327a1]
}

chng {2017-02-13 (3.17.0)} {
<li>Approximately 25% better performance from the [R-Tree extension].
    <ul>
    <li> Uses compiler built-ins (ex: __builtin_bswap32() or _byteswap_ulong())
         for byteswapping when available.
    <li> Uses the [sqlite3_blob] key/value access object instead of SQL

Changes to pages/chronology.in.

24
25
26
27
28
29
30

31
32
33
34
35
36
37
#    ORDER BY mtime DESC;
#
# A small amount of manual editing and de-duplication followed.
#
# Manually edit the list for each subsequent release.
#      
foreach line [split {

ada05cfa86|2017-02-13|version 3.17.0
a65a62893c|2017-01-06|version 3.16.2
979f043928|2017-01-03|Version 3.16.1
04ac0b75b1|2017-01-02|Version 3.16.0
bbd85d235f|2016-11-28|Version 3.15.2
1136863c76|2016-11-04|Version 3.15.1
707875582f|2016-10-14|Version 3.15.0







>







24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
#    ORDER BY mtime DESC;
#
# A small amount of manual editing and de-duplication followed.
#
# Manually edit the list for each subsequent release.
#      
foreach line [split {
xxxxxxxxxx|pending)|version 3.18.0
ada05cfa86|2017-02-13|version 3.17.0
a65a62893c|2017-01-06|version 3.16.2
979f043928|2017-01-03|Version 3.16.1
04ac0b75b1|2017-01-02|Version 3.16.0
bbd85d235f|2016-11-28|Version 3.15.2
1136863c76|2016-11-04|Version 3.15.1
707875582f|2016-10-14|Version 3.15.0

Changes to pages/index.in.

10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
[full-featured SQL|full-featured], [public-domain],
SQL database engine.
SQLite is the [most used] database engine in the world.
<a class="button" href="about.html">More Info</a></p>

<hr class="xhr">
<span class="hdrfont">Latest Release:&nbsp;&nbsp;</span>
<a href="releaselog/3_17_0.html">Version 3.17.0</a> ([dateof:3.17.0]).
<a class="button" href="download.html">Download</a>
<a class="button" href="chronology.html">Prior Releases</a>

<div class="mobileonly">
<hr class="xhr">
<h3>Common Links</h3>
<tcl>common_links</tcl>







|







10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
[full-featured SQL|full-featured], [public-domain],
SQL database engine.
SQLite is the [most used] database engine in the world.
<a class="button" href="about.html">More Info</a></p>

<hr class="xhr">
<span class="hdrfont">Latest Release:&nbsp;&nbsp;</span>
<a href="releaselog/3_18_0.html">Version 3.18.0</a> ([dateof:3.18.0]).
<a class="button" href="download.html">Download</a>
<a class="button" href="chronology.html">Prior Releases</a>

<div class="mobileonly">
<hr class="xhr">
<h3>Common Links</h3>
<tcl>common_links</tcl>

Changes to pages/pragma.in.

914
915
916
917
918
919
920






































































921
922
923
924
925
926
927
....
1448
1449
1450
1451
1452
1453
1454
1455
1456
1457
1458
1459
1460
1461
1462
....
1469
1470
1471
1472
1473
1474
1475
1476
1477
1478
1479
1480
1481
1482
1483
1484
        <br>PRAGMA DB.max_page_count = </b><i>N</i><b>;</b></p>
    <p>Query or set the maximum number of pages in the database file.)^
    ^Both forms of the pragma return the maximum page count.  ^The second
    form attempts to modify the maximum page count.  ^The maximum page
    count cannot be reduced below the current database size.
    </p>
}







































































Pragma query_only {
    <p><b>PRAGMA query_only;
      <br>PRAGMA query_only = </b><i>boolean</i><b>;</b></p>

    <p>The query_only pragma prevents all changes to database files when
    enabled.</p>
................................................................................

Pragma integrity_check {
    <p><b>PRAGMA DB.integrity_check;
      <br>PRAGMA DB.integrity_check(</b><i>N</i><b>)</b></p>
    <p>^This pragma does an integrity check of the entire database.  ^The
    integrity_check pragma
    looks for out-of-order records, missing pages, malformed records, missing
    index entries, and UNIQUE and NOT NULL constraint errors.
    ^If the integrity_check pragma finds problems, strings are returned
    (as multiple rows with a single column per row) which describe
    the problems.  ^Pragma integrity_check will return at most <i>N</i>
    errors before the analysis quits, with N defaulting
    to 100.  ^If pragma integrity_check finds no errors, a
    single row with the value 'ok' is returned.</p>

................................................................................
    checking of PRAGMA integrity_check but runs much faster.</p>
}

Pragma quick_check {
    <p><b>PRAGMA DB.quick_check;
      <br>PRAGMA DB.quick_check(</b><i>N</i><b>)</b></p>
    <p>^The pragma is like [integrity_check] except that it does not verify
    UNIQUE and NOT NULL constraints and does not verify
    that index content matches table content.  By skipping UNIQUE and NOT NULL
    and index consistency checks, quick_check is able to run much faster than
    integrity_check.  ^Otherwise the two pragmas are the same.
    </p>
}

DebugPragma parser_trace {
    <p><b>PRAGMA parser_trace = </b><i>boolean</i><b>; </b></p>







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







 







|







 







|
|







914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
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
996
997
....
1518
1519
1520
1521
1522
1523
1524
1525
1526
1527
1528
1529
1530
1531
1532
....
1539
1540
1541
1542
1543
1544
1545
1546
1547
1548
1549
1550
1551
1552
1553
1554
        <br>PRAGMA DB.max_page_count = </b><i>N</i><b>;</b></p>
    <p>Query or set the maximum number of pages in the database file.)^
    ^Both forms of the pragma return the maximum page count.  ^The second
    form attempts to modify the maximum page count.  ^The maximum page
    count cannot be reduced below the current database size.
    </p>
}

Pragma optimize {
  <p>^(<b>PRAGMA optimize;
       <br>PRAGMA optimize(</b><i>MASK</i><b>);
       <br>PRAGMA </b><i>schema</i><b>.optimize;
       <br>PRAGMA </b><i>schema</i><b>.optimize(</b><i>MASK</i><b>);</b></p>
  <p>Attempt to optimize the database.)^  ^All schemas are optimized in the 
  first two forms, and only the specified schema is optimized in the latter
  two.</p>
  <p>For achieve the best long-term query performance without the need to
  do a detailed engineering analysis of the application schema and SQL,
  it is recommended that applications run "PRAGMA optimize" (with no arguments)
  just before closing each [database connection].  Long-running applications
  might also benefit from setting a timer to run "PRAGMA optimize" every
  few hours.
  </p>
  <p>This pragma is usually a no-op or nearly so and is very fast.
  However if SQLite feels
  that performing database optimizations (such as running [ANALYZE]
  or creating new indexes) will improve the performance of future queries, then
  some database I/O may be done.  Applications that want to limit the amount
  of work performed can set a timer that will invoke
  [sqlite3_interrupt()] if the pragma goes on for too long.
  </p>
  <p>The details of optimizations performed by this pragma are expected
  to change and improve over time.  Applications should anticipate that
  this pragma will perform new optimizations in future releases.</p>

  <p>The optional MASK argument is a bitmask of optimizations to perform:
  <ol>
  <li value='1'><p>
         Debugging mode.  Do not actually perform any optimizations
         but instead return one line of text for each optimization
         that would have been done.  Off by default.
  <li value='2'><p>
         Run [ANALYZE] on tables that might benefit.  On by default.
         See below for additional information.
  <li value='4'><p>
         <em>(Not yet implemented)</em>
         Record usage and performance 
         information from the current session in the
         database file so that it will be available to "optimize"
         pragmas run by future database connections.
  <li value='8'><p>
         <em>(Not yet implemented)</em>
         Create indexes that might have been helpful to recent queries.
  </ol>
  <p>The default MASK is 14 (0x0e), which means perform all of the optimizations
  listed above except do not set Debug Mode.  New optimizations may be
  added in future releases but they will be turned off by default.  The
  default MASK will always be 0x0e.
  
  <p><b>Determination Of When To Run Analyze</b></p>
  <p> In the current implementation, a table is analyzed if only if all of
      the following are true:
  <ul>
  <li><p>
    MASK bit 0x02 is set.
  <li><p>
    The query planner used [sqlite_stat1]-style statistics for one or
    more indexes of the table at some point during the lifetime of
    the current connection.
  <li><p>
    One or more indexes of the table are currently unanalyzed <em>or</em>
    the number of rows in the table has increased by 25 times or more
    since the last time ANALYZE was run.
  </ul>
  <p> The rules for when tables are analyzed are likely to change in
      future releases.
}

Pragma query_only {
    <p><b>PRAGMA query_only;
      <br>PRAGMA query_only = </b><i>boolean</i><b>;</b></p>

    <p>The query_only pragma prevents all changes to database files when
    enabled.</p>
................................................................................

Pragma integrity_check {
    <p><b>PRAGMA DB.integrity_check;
      <br>PRAGMA DB.integrity_check(</b><i>N</i><b>)</b></p>
    <p>^This pragma does an integrity check of the entire database.  ^The
    integrity_check pragma
    looks for out-of-order records, missing pages, malformed records, missing
    index entries, and UNIQUE, CHECK, and NOT NULL constraint errors.
    ^If the integrity_check pragma finds problems, strings are returned
    (as multiple rows with a single column per row) which describe
    the problems.  ^Pragma integrity_check will return at most <i>N</i>
    errors before the analysis quits, with N defaulting
    to 100.  ^If pragma integrity_check finds no errors, a
    single row with the value 'ok' is returned.</p>

................................................................................
    checking of PRAGMA integrity_check but runs much faster.</p>
}

Pragma quick_check {
    <p><b>PRAGMA DB.quick_check;
      <br>PRAGMA DB.quick_check(</b><i>N</i><b>)</b></p>
    <p>^The pragma is like [integrity_check] except that it does not verify
    UNIQUE constraints and does not verify
    that index content matches table content.  By skipping UNIQUE
    and index consistency checks, quick_check is able to run much faster than
    integrity_check.  ^Otherwise the two pragmas are the same.
    </p>
}

DebugPragma parser_trace {
    <p><b>PRAGMA parser_trace = </b><i>boolean</i><b>; </b></p>