/ Check-in [fe200eaf]
Login

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

Overview
Comment:Add user documentation for the "pragma auto_vacuum" command. (CVS 2084)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: fe200eaf373998574cc059086bfc93d6c44ec5a3
User & Date: danielk1977 2004-11-10 05:48:57
Context
2004-11-10
11:55
Ensure tables cannot be created/dropped when btree cursors are open. (CVS 2085) (check-in: 8e5c2e5d user: danielk1977 tags: trunk)
05:48
Add user documentation for the "pragma auto_vacuum" command. (CVS 2084) (check-in: fe200eaf user: danielk1977 tags: trunk)
2004-11-09
16:13
Have "DEFAULT CURRENT_TIME" & co. work even if SQLITE_OMIT_DATETIME_FUNCS is defined. (CVS 2083) (check-in: f81b9c1c user: danielk1977 tags: trunk)
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to main.mk.

   367    367   	sed \
   368    368   	  -e '/^#/d' \
   369    369   	  -e 's,\\,\\\\,g' \
   370    370   	  -e 's,",\\",g' \
   371    371   	  -e 's,^,",' \
   372    372   	  -e 's,$$,\\n",' \
   373    373   	  $(TOP)/tool/spaceanal.tcl >spaceanal_tcl.h
   374         -	$(TCCX) $(TCL_FLAGS) -DTCLSH=2 -DSQLITE_TEST=1 -static -o \
          374  +	$(TCCX) $(TCL_FLAGS) -DTCLSH=2 -DSQLITE_TEST=1 -o \
   375    375    		sqlite3_analyzer$(EXE) $(TESTSRC) $(TOP)/src/tclsqlite.c \
   376    376   		libsqlite3.a $(LIBTCL) $(THREADLIB)
   377    377   
   378    378   # Rules used to build documentation
   379    379   #
   380    380   arch.html:	$(TOP)/www/arch.tcl
   381    381   	tclsh $(TOP)/www/arch.tcl >arch.html
................................................................................
   434    434   
   435    435   index.html:	$(TOP)/www/index.tcl last_change
   436    436   	tclsh $(TOP)/www/index.tcl >index.html
   437    437   
   438    438   lang.html:	$(TOP)/www/lang.tcl
   439    439   	tclsh $(TOP)/www/lang.tcl >lang.html
   440    440   
          441  +pragma.html:	$(TOP)/www/pragma.tcl
          442  +	tclsh $(TOP)/www/pragma.tcl >pragma.html
          443  +
   441    444   lockingv3.html:	$(TOP)/www/lockingv3.tcl
   442    445   	tclsh $(TOP)/www/lockingv3.tcl >lockingv3.html
   443    446   
   444    447   oldnews.html:	$(TOP)/www/oldnews.tcl
   445    448   	tclsh $(TOP)/www/oldnews.tcl >oldnews.html
   446    449   
   447    450   omitted.html:	$(TOP)/www/omitted.tcl
................................................................................
   505    508     lang.html \
   506    509     lockingv3.html \
   507    510     mingw.html \
   508    511     nulls.html \
   509    512     oldnews.html \
   510    513     omitted.html \
   511    514     opcode.html \
          515  +  pragma.html \
   512    516     quickstart.html \
   513    517     speed.html \
   514    518     sqlite.gif \
   515    519     sqlite.html \
   516    520     support.html \
   517    521     tclsqlite.html \
   518    522     vdbe.html \

Changes to www/c_interface.tcl.

     1      1   #
     2      2   # Run this Tcl script to generate the sqlite.html file.
     3      3   #
     4         -set rcsid {$Id: c_interface.tcl,v 1.41 2004/08/30 14:58:12 drh Exp $}
            4  +set rcsid {$Id: c_interface.tcl,v 1.42 2004/11/10 05:48:57 danielk1977 Exp $}
     5      5   source common.tcl
     6      6   header {The C language interface to the SQLite library}
     7      7   puts {
     8      8   <h2>The C language interface to the SQLite library</h2>
     9      9   
    10     10   <p>The SQLite library is designed to be very easy to use from
    11     11   a C or C++ program.  This document gives an overview of the C/C++
................................................................................
   132    132   <p>But if the i-th parameter is NULL we will get:</p>
   133    133   <blockquote><pre>
   134    134   argv[i] == 0
   135    135   </pre></blockquote>
   136    136   
   137    137   <p>The names of the columns are contained in first <i>argc</i>
   138    138   entries of the fourth argument.
   139         -If the <a href="lang.html#pragma_show_datatypes">SHOW_DATATYPES</a> pragma
          139  +If the <a href="pragma.html#pragma_show_datatypes">SHOW_DATATYPES</a> pragma
   140    140   is on (it is off by default) then
   141    141   the second <i>argc</i> entries in the 4th argument are the datatypes
   142    142   for the corresponding columns.
   143    143   </p>
   144    144   
   145         -<p>If the <a href="lang.html#pragma_empty_result_callbacks">
          145  +<p>If the <a href="pragma.html#pragma_empty_result_callbacks">
   146    146   EMPTY_RESULT_CALLBACKS</a> pragma is set to ON and the result of
   147    147   a query is an empty set, then the callback is invoked once with the
   148    148   third parameter (argv) set to 0.  In other words
   149    149   <blockquote><pre>
   150    150   argv == 0
   151    151   </pre></blockquote>
   152    152   The second parameter (argc)

Changes to www/common.tcl.

    51     51   </table>}
    52     52     set date [lrange $rcsid 3 4]
    53     53     if {$date!=""} {
    54     54       puts "<small><i>This page last modified on $date</i></small>"
    55     55     }
    56     56     puts {</body></html>}
    57     57   }
           58  +
           59  +
           60  +# The following two procs, Syntax and Section, are used to ensure
           61  +# consistent formatting in the "lang.html" and "pragma.html" pages.
           62  +#
           63  +proc Syntax {args} {
           64  +  puts {<table cellpadding="10">}
           65  +  foreach {rule body} $args {
           66  +    puts "<tr><td align=\"right\" valign=\"top\">"
           67  +    puts "<i><font color=\"#ff3434\">$rule</font></i>&nbsp;::=</td>"
           68  +    regsub -all < $body {%LT} body
           69  +    regsub -all > $body {%GT} body
           70  +    regsub -all %LT $body {</font></b><i><font color="#ff3434">} body
           71  +    regsub -all %GT $body {</font></i><b><font color="#2c2cf0">} body
           72  +    regsub -all {[]|[*?]} $body {</font></b>&<b><font color="#2c2cf0">} body
           73  +    regsub -all "\n" [string trim $body] "<br>\n" body
           74  +    regsub -all "\n  *" $body "\n\\&nbsp;\\&nbsp;\\&nbsp;\\&nbsp;" body
           75  +    regsub -all {[|,.*()]} $body {<big>&</big>} body
           76  +    regsub -all { = } $body { <big>=</big> } body
           77  +    regsub -all {STAR} $body {<big>*</big>} body
           78  +    ## These metacharacters must be handled to undo being
           79  +    ## treated as SQL punctuation characters above.
           80  +    regsub -all {RPPLUS} $body {</font></b>)+<b><font color="#2c2cf0">} body
           81  +    regsub -all {LP} $body {</font></b>(<b><font color="#2c2cf0">} body
           82  +    regsub -all {RP} $body {</font></b>)<b><font color="#2c2cf0">} body
           83  +    ## Place the left-hand side of the rule in the 2nd table column.
           84  +    puts "<td><b><font color=\"#2c2cf0\">$body</font></b></td></tr>"
           85  +  }
           86  +  puts {</table>}
           87  +}
           88  +proc Section {name {label {}}} {
           89  +  puts "\n<hr />"
           90  +  if {$label!=""} {
           91  +    puts "<a name=\"$label\"></a>"
           92  +  }
           93  +  puts "<h1>$name</h1>\n"
           94  +}
           95  +

Changes to www/docs.tcl.

     1      1   # This script generates the "docs.html" page that describes various
     2      2   # sources of documentation available for SQLite.
     3      3   #
     4         -set rcsid {$Id: docs.tcl,v 1.6 2004/09/18 18:00:24 drh Exp $}
            4  +set rcsid {$Id: docs.tcl,v 1.7 2004/11/10 05:48:57 danielk1977 Exp $}
     5      5   source common.tcl
     6      6   header {SQLite Documentation}
     7      7   puts {
     8      8   <h2>Available Documentation</h2>
     9      9   <table width="100%" cellpadding="5">
    10     10   }
    11     11   
................................................................................
    23     23     A very quick introduction to programming with SQLite.
    24     24   }
    25     25   
    26     26   doc {SQL Syntax} {lang.html} {
    27     27     This document describes the SQL language that is understood by
    28     28     SQLite.  
    29     29   }
           30  +
           31  +doc {Pragma commands} {pragma.html} {
           32  +  This document describes SQLite performance tuning options and other 
           33  +  special purpose database commands.
           34  +}
    30     35   
    31     36   doc {Version 2 C/C++ API} {c_interface.html} {
    32     37     A description of the C/C++ interface bindings for SQLite through version 
    33     38     2.8
    34     39   }
    35     40   doc {SQLite Version 3} {version3.html} {
    36     41     A summary of of the changes between SQLite version 2.8 and SQLite version 3.0.

Changes to www/faq.tcl.

     1      1   #
     2      2   # Run this script to generated a faq.html output file
     3      3   #
     4         -set rcsid {$Id: faq.tcl,v 1.26 2004/10/10 17:24:55 drh Exp $}
            4  +set rcsid {$Id: faq.tcl,v 1.27 2004/11/10 05:48:57 danielk1977 Exp $}
     5      5   source common.tcl
     6      6   header {SQLite Frequently Asked Questions</title>}
     7      7   
     8      8   set cnt 1
     9      9   proc faq {question answer} {
    10     10     set ::faq($::cnt) [list [string trim $question] [string trim $answer]]
    11     11     incr ::cnt
................................................................................
   395    395     run the VACUUM command (version 2.8.1 and later).  VACUUM will reconstruct
   396    396     the database from scratch.  This will leave the database with an empty
   397    397     free-list and a file that is minimal in size.  Note, however, that the
   398    398     VACUUM can take some time to run (around a half second per megabyte
   399    399     on the Linux box where SQLite is developed) and it can use up to twice
   400    400     as much temporary disk space as the original file while it is running.
   401    401     </p>
          402  +
          403  +  <p>As of SQLite version 3.1, an alternative to using the VACUUM command
          404  +  is auto-vacuum mode, enabled using the 
          405  +  <a href="pragma.html#pragma_auto_vacuum">auto_vacuum pragma</a>.</p>
   402    406   }
   403    407   
   404    408   faq {
   405    409     Can I use SQLite in my commercial product without paying royalties?
   406    410   } {
   407    411     <p>Yes.  SQLite is in the public domain.  No claim of ownership is made
   408    412     to any part of the code.  You can do anything you want with it.</p>

Changes to www/lang.tcl.

     1      1   #
     2      2   # Run this Tcl script to generate the sqlite.html file.
     3      3   #
     4         -set rcsid {$Id: lang.tcl,v 1.74 2004/10/10 17:24:55 drh Exp $}
            4  +set rcsid {$Id: lang.tcl,v 1.75 2004/11/10 05:48:57 danielk1977 Exp $}
     5      5   source common.tcl
     6      6   header {Query Language Understood by SQLite}
     7      7   puts {
     8      8   <h2>SQL As Understood By SQLite</h2>
     9      9   
    10     10   <p>The SQLite library understands most of the standard SQL
    11     11   language.  But it does <a href="omitted.html">omit some features</a>
................................................................................
    26     26   
    27     27   
    28     28   <p>SQLite implements the follow syntax:</p>
    29     29   <p><ul>
    30     30   }
    31     31   
    32     32   foreach {section} [lsort -index 0 -dictionary {
    33         -  {{CREATE TABLE} createtable}
    34         -  {{CREATE INDEX} createindex}
    35         -  {VACUUM vacuum}
    36         -  {{DROP TABLE} droptable}
    37         -  {{DROP INDEX} dropindex}
    38         -  {INSERT insert}
    39         -  {REPLACE replace}
    40         -  {DELETE delete}
    41         -  {UPDATE update}
    42         -  {SELECT select}
    43         -  {comment comment}
    44         -  {COPY copy}
    45         -  {EXPLAIN explain}
    46         -  {expression expr}
    47         -  {{BEGIN TRANSACTION} transaction}
    48         -  {{COMMIT TRANSACTION} transaction}
    49         -  {{END TRANSACTION} transaction}
    50         -  {{ROLLBACK TRANSACTION} transaction}
    51         -  {PRAGMA pragma}
    52         -  {{ON CONFLICT clause} conflict}
    53         -  {{CREATE VIEW} createview}
    54         -  {{DROP VIEW} dropview}
    55         -  {{CREATE TRIGGER} createtrigger}
    56         -  {{DROP TRIGGER} droptrigger}
    57         -  {{ATTACH DATABASE} attach}
    58         -  {{DETACH DATABASE} detach}
           33  +  {{CREATE TABLE} #createtable}
           34  +  {{CREATE INDEX} #createindex}
           35  +  {VACUUM #vacuum}
           36  +  {{DROP TABLE} #droptable}
           37  +  {{DROP INDEX} #dropindex}
           38  +  {INSERT #insert}
           39  +  {REPLACE #replace}
           40  +  {DELETE #delete}
           41  +  {UPDATE #update}
           42  +  {SELECT #select}
           43  +  {comment #comment}
           44  +  {COPY #copy}
           45  +  {EXPLAIN #explain}
           46  +  {expression #expr}
           47  +  {{BEGIN TRANSACTION} #transaction}
           48  +  {{COMMIT TRANSACTION} #transaction}
           49  +  {{END TRANSACTION} #transaction}
           50  +  {{ROLLBACK TRANSACTION} #transaction}
           51  +  {PRAGMA pragma.html}
           52  +  {{ON CONFLICT clause} #conflict}
           53  +  {{CREATE VIEW} #createview}
           54  +  {{DROP VIEW} #dropview}
           55  +  {{CREATE TRIGGER} #createtrigger}
           56  +  {{DROP TRIGGER} #droptrigger}
           57  +  {{ATTACH DATABASE} #attach}
           58  +  {{DETACH DATABASE} #detach}
    59     59   }] {
    60         -  puts "<li><a href=\"#[lindex $section 1]\">[lindex $section 0]</a></li>"
           60  +  foreach {s_title s_tag} $section {}
           61  +  puts "<li><a href=\"$s_tag\">$s_title</a></li>"
    61     62   }
    62     63   puts {</ul></p>
    63     64   
    64     65   <p>Details on the implementation of each command are provided in
    65     66   the sequel.</p>
    66     67   }
    67     68   
    68         -proc Syntax {args} {
    69         -  puts {<table cellpadding="10">}
    70         -  foreach {rule body} $args {
    71         -    puts "<tr><td align=\"right\" valign=\"top\">"
    72         -    puts "<i><font color=\"#ff3434\">$rule</font></i>&nbsp;::=</td>"
    73         -    regsub -all < $body {%LT} body
    74         -    regsub -all > $body {%GT} body
    75         -    regsub -all %LT $body {</font></b><i><font color="#ff3434">} body
    76         -    regsub -all %GT $body {</font></i><b><font color="#2c2cf0">} body
    77         -    regsub -all {[]|[*?]} $body {</font></b>&<b><font color="#2c2cf0">} body
    78         -    regsub -all "\n" [string trim $body] "<br>\n" body
    79         -    regsub -all "\n  *" $body "\n\\&nbsp;\\&nbsp;\\&nbsp;\\&nbsp;" body
    80         -    regsub -all {[|,.*()]} $body {<big>&</big>} body
    81         -    regsub -all { = } $body { <big>=</big> } body
    82         -    regsub -all {STAR} $body {<big>*</big>} body
    83         -    ## These metacharacters must be handled to undo being
    84         -    ## treated as SQL punctuation characters above.
    85         -    regsub -all {RPPLUS} $body {</font></b>)+<b><font color="#2c2cf0">} body
    86         -    regsub -all {LP} $body {</font></b>(<b><font color="#2c2cf0">} body
    87         -    regsub -all {RP} $body {</font></b>)<b><font color="#2c2cf0">} body
    88         -    ## Place the left-hand side of the rule in the 2nd table column.
    89         -    puts "<td><b><font color=\"#2c2cf0\">$body</font></b></td></tr>"
    90         -  }
    91         -  puts {</table>}
    92         -}
    93     69   proc Operator {name} {
    94     70     return "<font color=\"#2c2cf0\"><big>$name</big></font>"
    95     71   }
    96     72   proc Nonterminal {name} {
    97     73     return "<i><font color=\"#ff3434\">$name</font></i>"
    98     74   }
    99     75   proc Keyword {name} {
   100     76     return "<font color=\"#2c2cf0\">$name</font>"
   101     77   }
   102         - 
   103         -
   104         -proc Section {name {label {}}} {
   105         -  puts "\n<hr />"
   106         -  if {$label!=""} {
   107         -    puts "<a name=\"$label\"></a>"
   108         -  }
   109         -  puts "<h1>$name</h1>\n"
   110         -}
   111         -
   112     78   proc Example {text} {
   113     79     puts "<blockquote><pre>$text</pre></blockquote>"
   114     80   }
   115     81   
   116     82   
   117     83   Section {ATTACH DATABASE} attach
   118     84   
................................................................................
  1268   1234   overrides any algorithm specified in a CREATE TABLE or CREATE INDEX.
  1269   1235   If no algorithm is specified anywhere, the ABORT algorithm is used.</p>
  1270   1236   
  1271   1237   }
  1272   1238   # <p>For additional information, see 
  1273   1239   # <a href="conflict.html">conflict.html</a>.</p>
  1274   1240   
  1275         -
  1276         -Section PRAGMA pragma
  1277         -
  1278         -Syntax {sql-statement} {
  1279         -PRAGMA <name> [= <value>] |
  1280         -PRAGMA <function>(<arg>)
  1281         -}
  1282         -
  1283         -puts {
  1284         -<p>The PRAGMA command is used to modify the operation of the SQLite library.
  1285         -The pragma command is experimental and specific pragma statements may be
  1286         -removed or added in future releases of SQLite.  Use this command
  1287         -with caution.</p>
  1288         -
  1289         -<p>The pragmas that take an integer <b><i>value</i></b> also accept 
  1290         -symbolic names.  The strings "<b>on</b>", "<b>true</b>", and "<b>yes</b>" 
  1291         -are equivalent to <b>1</b>.  The strings "<b>off</b>", "<b>false</b>", 
  1292         -and "<b>no</b>" are equivalent to <b>0</b>.  These strings are case-
  1293         -insensitive, and do not require quotes.  An unrecognized string will be 
  1294         -treated as <b>1</b>, and will not generate an error.  When the <i>value</i> 
  1295         -is returned it is as an integer.</p>
  1296         -
  1297         -<p>The current implementation supports the following pragmas:</p>
  1298         -
  1299         -<ul>
  1300         -<a name="pragma_cache_size"></a>
  1301         -<li><p><b>PRAGMA cache_size;
  1302         -       <br>PRAGMA cache_size = </b><i>Number-of-pages</i><b>;</b></p>
  1303         -    <p>Query or change the maximum number of database disk pages that SQLite
  1304         -    will hold in memory at once.  Each page uses about 1.5K of memory.
  1305         -    The default cache size is 2000.  If you are doing UPDATEs or DELETEs
  1306         -    that change many rows of a database and you do not mind if SQLite
  1307         -    uses more memory, you can increase the cache size for a possible speed
  1308         -    improvement.</p>
  1309         -    <p>When you change the cache size using the cache_size pragma, the
  1310         -    change only endures for the current session.  The cache size reverts
  1311         -    to the default value when the database is closed and reopened.  Use
  1312         -    the <a href="#pragma_default_cache_size"><b>default_cache_size</b></a> 
  1313         -    pragma to check the cache size permanently.</p></li>
  1314         -
  1315         -<li><p><b>PRAGMA database_list;</b></p>
  1316         -    <p>For each open database, invoke the callback function once with
  1317         -    information about that database.  Arguments include the index and 
  1318         -    the name the database was attached with.  The first row will be for 
  1319         -    the main database.  The second row will be for the database used to 
  1320         -    store temporary tables.</p></li>
  1321         -
  1322         -<a name="pragma_default_cache_size"></a>
  1323         -<li><p><b>PRAGMA default_cache_size;
  1324         -       <br>PRAGMA default_cache_size = </b><i>Number-of-pages</i><b>;</b></p>
  1325         -    <p>Query or change the maximum number of database disk pages that SQLite
  1326         -    will hold in memory at once.  Each page uses 1K on disk and about
  1327         -    1.5K in memory.
  1328         -    This pragma works like the
  1329         -    <a href="#pragma_cache_size"><b>cache_size</b></a> 
  1330         -    pragma with the additional
  1331         -    feature that it changes the cache size persistently.  With this pragma,
  1332         -    you can set the cache size once and that setting is retained and reused
  1333         -    every time you reopen the database.</p></li>
  1334         -
  1335         -<a name="pragma_default_synchronous"></a>
  1336         -<li><p><b>PRAGMA default_synchronous;
  1337         -       <br>PRAGMA default_synchronous = FULL; </b>(2)<b>
  1338         -       <br>PRAGMA default_synchronous = NORMAL; </b>(1)<b>
  1339         -       <br>PRAGMA default_synchronous = OFF; </b>(0)</p>
  1340         -    <p>Query or change the setting of the "synchronous" flag in
  1341         -    the database.  The first (query) form will return the setting as an 
  1342         -    integer.  When synchronous is FULL (2), the SQLite database engine will
  1343         -    pause at critical moments to make sure that data has actually been 
  1344         -    written to the disk surface before continuing.  This ensures that if
  1345         -    the operating system crashes or if there is a power failure, the database
  1346         -    will be uncorrupted after rebooting.  FULL synchronous is very 
  1347         -    safe, but it is also slow.  
  1348         -    When synchronous is NORMAL (1, the default), the SQLite database
  1349         -    engine will still pause at the most critical moments, but less often
  1350         -    than in FULL mode.  There is a very small (though non-zero) chance that
  1351         -    a power failure at just the wrong time could corrupt the database in
  1352         -    NORMAL mode.  But in practice, you are more likely to suffer
  1353         -    a catastrophic disk failure or some other unrecoverable hardware
  1354         -    fault.  So NORMAL is the default mode.
  1355         -    With synchronous OFF (0), SQLite continues without pausing
  1356         -    as soon as it has handed data off to the operating system.
  1357         -    If the application running SQLite crashes, the data will be safe, but
  1358         -    the database might become corrupted if the operating system
  1359         -    crashes or the computer loses power before that data has been written
  1360         -    to the disk surface.  On the other hand, some
  1361         -    operations are as much as 50 or more times faster with synchronous OFF.
  1362         -    </p>
  1363         -    <p>This pragma changes the synchronous mode persistently.  Once changed,
  1364         -    the mode stays as set even if the database is closed and reopened.  The
  1365         -    <a href="#pragma_synchronous"><b>synchronous</b></a> pragma does the same 
  1366         -    thing but only applies the setting to the current session.
  1367         -    
  1368         -    </p></li>
  1369         -
  1370         -<a name="pragma_default_temp_store"></a>
  1371         -<li><p><b>PRAGMA default_temp_store;
  1372         -       <br>PRAGMA default_temp_store = DEFAULT; </b>(0)<b>
  1373         -       <br>PRAGMA default_temp_store = MEMORY; </b>(2)<b>
  1374         -       <br>PRAGMA default_temp_store = FILE;</b> (1)</p>
  1375         -    <p>Query or change the setting of the "<b>temp_store</b>" flag stored in
  1376         -    the database.  When temp_store is DEFAULT (0), the compile-time value
  1377         -    of the symbol TEMP_STORE is used for the temporary database.  
  1378         -    When temp_store is MEMORY (2), an in-memory database is used.  
  1379         -    When temp_store is FILE (1), a temporary database file on disk will be used.
  1380         -    It is possible for the library compile-time symbol TEMP_STORE to override 
  1381         -    this setting.  The following table summarizes this:</p>
  1382         -
  1383         -<table cellpadding="2">
  1384         -<tr><th>TEMP_STORE</th><th>temp_store</th><th>temp database location</th></tr>
  1385         -<tr><td align="center">0</td><td align="center"><em>any</em></td><td align="center">file</td></tr>
  1386         -<tr><td align="center">1</td><td align="center">0</td><td align="center">file</td></tr>
  1387         -<tr><td align="center">1</td><td align="center">1</td><td align="center">file</td></tr>
  1388         -<tr><td align="center">1</td><td align="center">2</td><td align="center">memory</td></tr>
  1389         -<tr><td align="center">2</td><td align="center">0</td><td align="center">memory</td></tr>
  1390         -<tr><td align="center">2</td><td align="center">1</td><td align="center">file</td></tr>
  1391         -<tr><td align="center">2</td><td align="center">2</td><td align="center">memory</td></tr>
  1392         -<tr><td align="center">3</td><td align="center"><em>any</em></td><td align="center">memory</td></tr>
  1393         -</table>
  1394         -
  1395         -    <p>This pragma changes the temp_store mode for whenever the database
  1396         -    is opened in the future.  The temp_store mode for the current session
  1397         -    is unchanged.  Use the 
  1398         -    <a href="#pragma_temp_store"><b>temp_store</b></a> pragma to change the
  1399         -    temp_store mode for the current session.</p></li>
  1400         -
  1401         -<li><p><b>PRAGMA foreign_key_list(</b><i>table-name</i><b>);</b></p>
  1402         -    <p>For each foreign key that references a column in the argument
  1403         -    table, invoke the callback function with information about that
  1404         -    foreign key. The callback function will be invoked once for each
  1405         -    column in each foreign key.</p></li>
  1406         -
  1407         -<li><p><b>PRAGMA index_info(</b><i>index-name</i><b>);</b></p>
  1408         -    <p>For each column that the named index references, invoke the 
  1409         -    callback function
  1410         -    once with information about that column, including the column name,
  1411         -    and the column number.</p></li>
  1412         -
  1413         -<li><p><b>PRAGMA index_list(</b><i>table-name</i><b>);</b></p>
  1414         -    <p>For each index on the named table, invoke the callback function
  1415         -    once with information about that index.  Arguments include the
  1416         -    index name and a flag to indicate whether or not the index must be
  1417         -    unique.</p></li>
  1418         -
  1419         -<li><p><b>PRAGMA integrity_check;</b></p>
  1420         -    <p>The command does an integrity check of the entire database.  It
  1421         -    looks for out-of-order records, missing pages, malformed records, and
  1422         -    corrupt indices.
  1423         -    If any problems are found, then a single string is returned which is
  1424         -    a description of all problems.  If everything is in order, "ok" is
  1425         -    returned.</p></li>
  1426         -
  1427         -<li><p><b>PRAGMA parser_trace = ON; </b>(1)<b>
  1428         -    <br>PRAGMA parser_trace = OFF;</b> (0)</p>
  1429         -    <p>Turn tracing of the SQL parser inside of the
  1430         -    SQLite library on and off.  This is used for debugging.
  1431         -    This only works if the library is compiled without the NDEBUG macro.
  1432         -    </p></li>
  1433         -
  1434         -<a name="pragma_synchronous"></a>
  1435         -<li><p><b>PRAGMA synchronous;
  1436         -       <br>PRAGMA synchronous = FULL; </b>(2)<b>
  1437         -       <br>PRAGMA synchronous = NORMAL; </b>(1)<b>
  1438         -       <br>PRAGMA synchronous = OFF;</b> (0)</p>
  1439         -    <p>Query or change the setting of the "synchronous" flag affecting
  1440         -    the database for the duration of the current database connection.
  1441         -    The synchronous flag reverts to its default value when the database
  1442         -    is closed and reopened.  For additional information on the synchronous
  1443         -    flag, see the description of the <a href="#pragma_default_synchronous">
  1444         -    <b>default_synchronous</b></a> pragma.</p>
  1445         -    </li>
  1446         -
  1447         -<li><p><b>PRAGMA table_info(</b><i>table-name</i><b>);</b></p>
  1448         -    <p>For each column in the named table, invoke the callback function
  1449         -    once with information about that column, including the column name,
  1450         -    data type, whether or not the column can be NULL, and the default
  1451         -    value for the column.</p></li>
  1452         -
  1453         -<a name="pragma_temp_store"></a>
  1454         -<li><p><b>PRAGMA temp_store;
  1455         -       <br>PRAGMA temp_store = DEFAULT; </b>(0)<b>
  1456         -       <br>PRAGMA temp_store = MEMORY; </b>(2)<b>
  1457         -       <br>PRAGMA temp_store = FILE;</b> (1)</p>
  1458         -    <p>Query or change the setting of the "temp_store" flag affecting
  1459         -    the database for the duration of the current database connection.
  1460         -    The temp_store flag reverts to its default value when the database
  1461         -    is closed and reopened.  For additional information on the temp_store
  1462         -    flag, see the description of the <a href="#pragma_default_temp_store">
  1463         -    <b>default_temp_store</b></a> pragma.  Note that it is possible for 
  1464         -    the library compile-time options to override this setting. </p>
  1465         -
  1466         -    <p>When the temp_store setting is changed, all existing temporary
  1467         -    tables, indices, triggers, and viewers are immediately deleted.
  1468         -    </p>
  1469         -    </li>
  1470         -
  1471         -<a name="pragma_vdbe_trace"></a>
  1472         -<li><p><b>PRAGMA vdbe_trace = ON; </b>(1)<b>
  1473         -    <br>PRAGMA vdbe_trace = OFF;</b> (0)</p>
  1474         -    <p>Turn tracing of the virtual database engine inside of the
  1475         -    SQLite library on and off.  This is used for debugging.  See the 
  1476         -    <a href="vdbe.html#trace">VDBE documentation</a> for more 
  1477         -    information.</p></li>
  1478         -</ul>
  1479         -
  1480         -<p>No error message is generated if an unknown pragma is issued.
  1481         -Unknown pragmas are ignored.</p>
  1482         -}
  1483         -
  1484         -
  1485   1241   Section REPLACE replace
  1486   1242   
  1487   1243   Syntax {sql-statement} {
  1488   1244   REPLACE INTO [<database-name> .] <table-name> [( <column-list> )] VALUES ( <value-list> ) |
  1489   1245   REPLACE INTO [<database-name> .] <table-name> [( <column-list> )] <select-statement>
  1490   1246   }
  1491   1247   
................................................................................
  1657   1413   reloading the original database file from the copy.  This eliminates 
  1658   1414   free pages,  aligns table data to be contiguous, and otherwise cleans 
  1659   1415   up the database file structure. It is not possible to perform the same
  1660   1416   process on an attached database file.</p>
  1661   1417   
  1662   1418   <p>This command will fail if there is an active transaction.  This 
  1663   1419   command has no effect on an in-memory database.</p>
         1420  +
         1421  +<p>As of SQLite version 3.1, an alternative to using the VACUUM command
         1422  +is auto-vacuum mode, enabled using the 
         1423  +<a href="pragma.html#pragma_auto_vacuum">auto_vacuum pragma</a>.</p>
  1664   1424   }
  1665   1425   
  1666   1426   
  1667   1427   Section {SQLite keywords} keywords
  1668   1428   
  1669   1429   puts {
  1670   1430   <p>The following keywords are used by SQLite.  Most are either reserved 

Added www/pragma.tcl.

            1  +#
            2  +# Run this Tcl script to generate the pragma.html file.
            3  +#
            4  +set rcsid {$Id: pragma.tcl,v 1.1 2004/11/10 05:48:57 danielk1977 Exp $}
            5  +source common.tcl
            6  +header {Pragma statements supported by SQLite}
            7  +
            8  +puts {
            9  +<p>The <a href="#syntax">PRAGMA command</a> is a special command used to 
           10  +modify the operation of the SQLite library or to query the library for 
           11  +internal (non-table) data. The PRAGMA command is issued using the same
           12  +interface as other SQLite commands (e.g. SELECT, INSERT) but is different
           13  +different in the following important respects:
           14  +</p>
           15  +<ul>
           16  +<li>Specific pragma statements may be removed and others added in future
           17  +    releases of SQLite. Use with caution!
           18  +<li>No error messages are generated if an unknown pragma is issued.
           19  +    Unknown pragmas are simply ignored. This means if there is a typo in 
           20  +    a pragma statement the library does not inform the user of the fact.
           21  +<li>Some pragmas take effect during the SQL compilation stage, not the
           22  +    execution stage. This means if using the C-language sqlite3_compile(), 
           23  +    sqlite3_step(), sqlite3_finalize() API (or similar in a wrapper 
           24  +    interface), the pragma may be applied to the library during the 
           25  +    sqlite3_compile() call.
           26  +<li>The pragma command is unlikely to be compatible with any other SQL
           27  +    engine.
           28  +</ul>
           29  +
           30  +<p>The available pragma's fall into three basic categories:</p>
           31  +<ul>
           32  +<li>Pragmas used to <a href="#schema">query the schema</a> of the current 
           33  +    database.
           34  +<li>Pragmas used to <a href="#modify">modify the operation</a> of the 
           35  +    SQLite library in some manner, or to query for the current mode of 
           36  +    operation.
           37  +<li>Pragmas used to <a href="#debug">debug the library</a> and verify that
           38  +    database files are not corrupted.
           39  +</ul>
           40  +}
           41  +
           42  +Section {PRAGMA command syntax} syntax
           43  +
           44  +Syntax {sql-statement} {
           45  +PRAGMA <name> [= <value>] |
           46  +PRAGMA <function>(<arg>)
           47  +}
           48  +
           49  +puts {
           50  +<p>The pragmas that take an integer <b><i>value</i></b> also accept 
           51  +symbolic names.  The strings "<b>on</b>", "<b>true</b>", and "<b>yes</b>" 
           52  +are equivalent to <b>1</b>.  The strings "<b>off</b>", "<b>false</b>", 
           53  +and "<b>no</b>" are equivalent to <b>0</b>.  These strings are case-
           54  +insensitive, and do not require quotes.  An unrecognized string will be 
           55  +treated as <b>1</b>, and will not generate an error.  When the <i>value</i> 
           56  +is returned it is as an integer.</p>
           57  +}
           58  +
           59  +Section {Pragmas used to modify library operation} modify
           60  +
           61  +puts {
           62  +<ul>
           63  +<a name="pragma_auto_vacuum"></a>
           64  +<li><p><b>PRAGMA auto_vacuum;
           65  +       <br>PRAGMA auto_vacuum = </b><i>0 | 1</i><b>;</b></p>
           66  +    <p> Query or set the auto-vacuum flag in the database.</p>
           67  +
           68  +    <p>Normally, when a transaction that deletes data from a database is
           69  +    committed, the database file remains the same size. Unused database file 
           70  +    pages are marked as such and reused later on, when data is inserted into 
           71  +    the database. In this mode the <a href="lang.html#vacuum">VACUUM</a>
           72  +    command is used to reclaim unused space.</p>
           73  +
           74  +    <p>When the auto-vacuum flag is set, the database file shrinks when a
           75  +    transaction that deletes data is committed (The VACUUM command is not
           76  +    useful in a database with the auto-vacuum flag set). To support this
           77  +    functionality the database stores extra information internally, resulting
           78  +    in slightly larger database files than would otherwise be possible.</p>
           79  +
           80  +    <p>It is only possible to modify the value of the auto-vacuum flag before
           81  +    any tables have been created in the database. No error message is 
           82  +    returned if an attempt to modify the auto-vacuum flag is made after
           83  +    one or more tables have been created.
           84  +    </p></li>
           85  +
           86  +<a name="pragma_cache_size"></a>
           87  +<li><p><b>PRAGMA cache_size;
           88  +       <br>PRAGMA cache_size = </b><i>Number-of-pages</i><b>;</b></p>
           89  +    <p>Query or change the maximum number of database disk pages that SQLite
           90  +    will hold in memory at once.  Each page uses about 1.5K of memory.
           91  +    The default cache size is 2000.  If you are doing UPDATEs or DELETEs
           92  +    that change many rows of a database and you do not mind if SQLite
           93  +    uses more memory, you can increase the cache size for a possible speed
           94  +    improvement.</p>
           95  +    <p>When you change the cache size using the cache_size pragma, the
           96  +    change only endures for the current session.  The cache size reverts
           97  +    to the default value when the database is closed and reopened.  Use
           98  +    the <a href="#pragma_default_cache_size"><b>default_cache_size</b></a> 
           99  +    pragma to check the cache size permanently.</p></li>
          100  +
          101  +<a name="pragma_default_cache_size"></a>
          102  +<li><p><b>PRAGMA default_cache_size;
          103  +       <br>PRAGMA default_cache_size = </b><i>Number-of-pages</i><b>;</b></p>
          104  +    <p>Query or change the maximum number of database disk pages that SQLite
          105  +    will hold in memory at once.  Each page uses 1K on disk and about
          106  +    1.5K in memory.
          107  +    This pragma works like the
          108  +    <a href="#pragma_cache_size"><b>cache_size</b></a> 
          109  +    pragma with the additional
          110  +    feature that it changes the cache size persistently.  With this pragma,
          111  +    you can set the cache size once and that setting is retained and reused
          112  +    every time you reopen the database.</p></li>
          113  +
          114  +<a name="pragma_default_synchronous"></a>
          115  +<li><p><b>PRAGMA default_synchronous;
          116  +       <br>PRAGMA default_synchronous = FULL; </b>(2)<b>
          117  +       <br>PRAGMA default_synchronous = NORMAL; </b>(1)<b>
          118  +       <br>PRAGMA default_synchronous = OFF; </b>(0)</p>
          119  +    <p>Query or change the setting of the "synchronous" flag in
          120  +    the database.  The first (query) form will return the setting as an 
          121  +    integer.  When synchronous is FULL (2), the SQLite database engine will
          122  +    pause at critical moments to make sure that data has actually been 
          123  +    written to the disk surface before continuing.  This ensures that if
          124  +    the operating system crashes or if there is a power failure, the database
          125  +    will be uncorrupted after rebooting.  FULL synchronous is very 
          126  +    safe, but it is also slow.  
          127  +    When synchronous is NORMAL (1, the default), the SQLite database
          128  +    engine will still pause at the most critical moments, but less often
          129  +    than in FULL mode.  There is a very small (though non-zero) chance that
          130  +    a power failure at just the wrong time could corrupt the database in
          131  +    NORMAL mode.  But in practice, you are more likely to suffer
          132  +    a catastrophic disk failure or some other unrecoverable hardware
          133  +    fault.  So NORMAL is the default mode.
          134  +    With synchronous OFF (0), SQLite continues without pausing
          135  +    as soon as it has handed data off to the operating system.
          136  +    If the application running SQLite crashes, the data will be safe, but
          137  +    the database might become corrupted if the operating system
          138  +    crashes or the computer loses power before that data has been written
          139  +    to the disk surface.  On the other hand, some
          140  +    operations are as much as 50 or more times faster with synchronous OFF.
          141  +    </p>
          142  +    <p>This pragma changes the synchronous mode persistently.  Once changed,
          143  +    the mode stays as set even if the database is closed and reopened.  The
          144  +    <a href="#pragma_synchronous"><b>synchronous</b></a> pragma does the same 
          145  +    thing but only applies the setting to the current session.
          146  +    
          147  +    </p></li>
          148  +
          149  +<a name="pragma_default_temp_store"></a>
          150  +<li><p><b>PRAGMA default_temp_store;
          151  +       <br>PRAGMA default_temp_store = DEFAULT; </b>(0)<b>
          152  +       <br>PRAGMA default_temp_store = MEMORY; </b>(2)<b>
          153  +       <br>PRAGMA default_temp_store = FILE;</b> (1)</p>
          154  +    <p>Query or change the setting of the "<b>temp_store</b>" flag stored in
          155  +    the database.  When temp_store is DEFAULT (0), the compile-time value
          156  +    of the symbol TEMP_STORE is used for the temporary database.  
          157  +    When temp_store is MEMORY (2), an in-memory database is used.  
          158  +    When temp_store is FILE (1), a temporary database file on disk will be used.
          159  +    It is possible for the library compile-time symbol TEMP_STORE to override 
          160  +    this setting.  The following table summarizes this:</p>
          161  +
          162  +<table cellpadding="2">
          163  +<tr><th>TEMP_STORE</th><th>temp_store</th><th>temp database location</th></tr>
          164  +<tr><td align="center">0</td><td align="center"><em>any</em></td><td align="center">file</td></tr>
          165  +<tr><td align="center">1</td><td align="center">0</td><td align="center">file</td></tr>
          166  +<tr><td align="center">1</td><td align="center">1</td><td align="center">file</td></tr>
          167  +<tr><td align="center">1</td><td align="center">2</td><td align="center">memory</td></tr>
          168  +<tr><td align="center">2</td><td align="center">0</td><td align="center">memory</td></tr>
          169  +<tr><td align="center">2</td><td align="center">1</td><td align="center">file</td></tr>
          170  +<tr><td align="center">2</td><td align="center">2</td><td align="center">memory</td></tr>
          171  +<tr><td align="center">3</td><td align="center"><em>any</em></td><td align="center">memory</td></tr>
          172  +</table>
          173  +
          174  +    <p>This pragma changes the temp_store mode for whenever the database
          175  +    is opened in the future.  The temp_store mode for the current session
          176  +    is unchanged.  Use the 
          177  +    <a href="#pragma_temp_store"><b>temp_store</b></a> pragma to change the
          178  +    temp_store mode for the current session.</p></li>
          179  +
          180  +<a name="pragma_synchronous"></a>
          181  +<li><p><b>PRAGMA synchronous;
          182  +       <br>PRAGMA synchronous = FULL; </b>(2)<b>
          183  +       <br>PRAGMA synchronous = NORMAL; </b>(1)<b>
          184  +       <br>PRAGMA synchronous = OFF;</b> (0)</p>
          185  +    <p>Query or change the setting of the "synchronous" flag affecting
          186  +    the database for the duration of the current database connection.
          187  +    The synchronous flag reverts to its default value when the database
          188  +    is closed and reopened.  For additional information on the synchronous
          189  +    flag, see the description of the <a href="#pragma_default_synchronous">
          190  +    <b>default_synchronous</b></a> pragma.</p>
          191  +    </li>
          192  +
          193  +<a name="pragma_temp_store"></a>
          194  +<li><p><b>PRAGMA temp_store;
          195  +       <br>PRAGMA temp_store = DEFAULT; </b>(0)<b>
          196  +       <br>PRAGMA temp_store = MEMORY; </b>(2)<b>
          197  +       <br>PRAGMA temp_store = FILE;</b> (1)</p>
          198  +    <p>Query or change the setting of the "temp_store" flag affecting
          199  +    the database for the duration of the current database connection.
          200  +    The temp_store flag reverts to its default value when the database
          201  +    is closed and reopened.  For additional information on the temp_store
          202  +    flag, see the description of the <a href="#pragma_default_temp_store">
          203  +    <b>default_temp_store</b></a> pragma.  Note that it is possible for 
          204  +    the library compile-time options to override this setting. </p>
          205  +
          206  +    <p>When the temp_store setting is changed, all existing temporary
          207  +    tables, indices, triggers, and viewers are immediately deleted.
          208  +    </p>
          209  +    </li>
          210  +</ul>
          211  +}
          212  +
          213  +Section {Pragma's used to query the database schema} schema
          214  +
          215  +puts {
          216  +<ul>
          217  +<li><p><b>PRAGMA database_list;</b></p>
          218  +    <p>For each open database, invoke the callback function once with
          219  +    information about that database.  Arguments include the index and 
          220  +    the name the database was attached with.  The first row will be for 
          221  +    the main database.  The second row will be for the database used to 
          222  +    store temporary tables.</p></li>
          223  +
          224  +<li><p><b>PRAGMA foreign_key_list(</b><i>table-name</i><b>);</b></p>
          225  +    <p>For each foreign key that references a column in the argument
          226  +    table, invoke the callback function with information about that
          227  +    foreign key. The callback function will be invoked once for each
          228  +    column in each foreign key.</p></li>
          229  +
          230  +<li><p><b>PRAGMA index_info(</b><i>index-name</i><b>);</b></p>
          231  +    <p>For each column that the named index references, invoke the 
          232  +    callback function
          233  +    once with information about that column, including the column name,
          234  +    and the column number.</p></li>
          235  +
          236  +<li><p><b>PRAGMA index_list(</b><i>table-name</i><b>);</b></p>
          237  +    <p>For each index on the named table, invoke the callback function
          238  +    once with information about that index.  Arguments include the
          239  +    index name and a flag to indicate whether or not the index must be
          240  +    unique.</p></li>
          241  +
          242  +<li><p><b>PRAGMA table_info(</b><i>table-name</i><b>);</b></p>
          243  +    <p>For each column in the named table, invoke the callback function
          244  +    once with information about that column, including the column name,
          245  +    data type, whether or not the column can be NULL, and the default
          246  +    value for the column.</p></li>
          247  +</ul>
          248  +}
          249  +
          250  +Section {Pragma's used to debug the library} debug
          251  +
          252  +puts {
          253  +<ul>
          254  +<li><p><b>PRAGMA integrity_check;</b></p>
          255  +    <p>The command does an integrity check of the entire database.  It
          256  +    looks for out-of-order records, missing pages, malformed records, and
          257  +    corrupt indices.
          258  +    If any problems are found, then a single string is returned which is
          259  +    a description of all problems.  If everything is in order, "ok" is
          260  +    returned.</p></li>
          261  +
          262  +<li><p><b>PRAGMA parser_trace = ON; </b>(1)<b>
          263  +    <br>PRAGMA parser_trace = OFF;</b> (0)</p>
          264  +    <p>Turn tracing of the SQL parser inside of the
          265  +    SQLite library on and off.  This is used for debugging.
          266  +    This only works if the library is compiled without the NDEBUG macro.
          267  +    </p></li>
          268  +
          269  +<a name="pragma_vdbe_trace"></a>
          270  +<li><p><b>PRAGMA vdbe_trace = ON; </b>(1)<b>
          271  +    <br>PRAGMA vdbe_trace = OFF;</b> (0)</p>
          272  +    <p>Turn tracing of the virtual database engine inside of the
          273  +    SQLite library on and off.  This is used for debugging.  See the 
          274  +    <a href="vdbe.html#trace">VDBE documentation</a> for more 
          275  +    information.</p></li>
          276  +</ul>
          277  +
          278  +}
          279  +

Changes to www/vdbe.tcl.

     1      1   #
     2      2   # Run this Tcl script to generate the vdbe.html file.
     3      3   #
     4         -set rcsid {$Id: vdbe.tcl,v 1.12 2004/05/31 15:06:30 drh Exp $}
            4  +set rcsid {$Id: vdbe.tcl,v 1.13 2004/11/10 05:48:57 danielk1977 Exp $}
     5      5   source common.tcl
     6      6   header {The Virtual Database Engine of SQLite}
     7      7   puts {
     8      8   <h2>The Virtual Database Engine of SQLite</h2>
     9      9   
    10     10   <blockquote><b>
    11     11   This document describes the virtual machine used in SQLite version 2.8.0.  
................................................................................
   299    299   program, which the VDBE appends when it prepares a program to run.</p>
   300    300   
   301    301   
   302    302   <a name="trace">
   303    303   <h2>Tracing VDBE Program Execution</h2>
   304    304   
   305    305   <p>If the SQLite library is compiled without the NDEBUG preprocessor 
   306         -macro, then the PRAGMA <a href="lang.html#pragma_vdbe_trace">vdbe_trace
          306  +macro, then the PRAGMA <a href="pragma.html#pragma_vdbe_trace">vdbe_trace
   307    307   </a> causes the VDBE to trace the execution of programs.  Though this 
   308    308   feature was originally intended for testing and debugging, it can also 
   309    309   be useful in learning about how the VDBE operates.  
   310    310   Use "<tt>PRAGMA&nbsp;vdbe_trace=ON;</tt>" to turn tracing on and 
   311    311   "<tt>PRAGMA&nbsp;vdbe_trace=OFF</tt>" to turn tracing back off.  
   312    312   Like this:</p>
   313    313   }