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: |
fe200eaf373998574cc059086bfc93d6 |
| 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
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> ::=</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\\ \\ \\ \\ " 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> ::=</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\\ \\ \\ \\ " 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 vdbe_trace=ON;</tt>" to turn tracing on and 311 311 "<tt>PRAGMA vdbe_trace=OFF</tt>" to turn tracing back off. 312 312 Like this:</p> 313 313 }