Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Add the <fancy_format> markup which works like <table_of_contents> but omits the actual table of contents. Begin writing documentation for the carray() function. Fix typos and adjust wording in the CLI document. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
e89a552dc3e7cf5f045da7f90682c342 |
User & Date: | drh 2016-07-12 23:52:19.308 |
Context
2016-07-13
| ||
00:55 | Improvements to the carray() function documentation. (check-in: 3f0e846a97 user: drh tags: trunk) | |
2016-07-12
| ||
23:52 | Add the <fancy_format> markup which works like <table_of_contents> but omits the actual table of contents. Begin writing documentation for the carray() function. Fix typos and adjust wording in the CLI document. (check-in: e89a552dc3 user: drh tags: trunk) | |
20:54 | Updates to reflect changes in 3.14.0. The "expr" syntax diagram is changed to show the ability to put table-valued functions on the RHS of an IN operator. Related text changes. Update the change log. (check-in: 24cd5438cc user: drh tags: trunk) | |
Changes
Added pages/carray.in.
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 | <title>The Carray() Table-Valued Function</title> <tcl>hd_keywords carray {carray() table-valued function}</tcl> <fancy_format> <h1>Overview</h1> <p>Carray($PTR,$N) is a [table-valued function] with a single column (named "value") and zero or more rows. The "value" of each row in the carray() is taken from a C-language array that is $N elements long and begins at address $PTR. In this way, the carray() function provides a convenient mechanism to bind C-language arrays to SQL queries. <h1>Availability</h1> <p>The carray() function is not compiled into SQLite by default. It is available as a [loadable extension] in the [https://www.sqlite.org/src/artifact?ci=trunk&filename=ext/misc/carray.c|ext/misc/carray.c] source file. <p>The carray() function is a little dangerous. The first parameter is a 64-bit integer which gets cast into a pointer to an array. In an application that runs user-generated or untrusted SQL, the carray() function could be used to crash the appliation or to leak sensitive information that the user is not suppose to have access to. For that reason, the carray() function will never be a standard part of SQLite. In that way, carray() will only be available in applications that deliberately request it, and which therefore presumably have protections in place to prevent misuse. <h1>Details</h1> <p>The carray() function takes two or three arguments. The first argument is a 64-bit integer that will be cast into a pointer to the C-language array that is to be returned by the function. The second argument is the number of elements in the array. The optional third argument is a string that determines the datatype of the elements in the C-language array. Allowed values for the third argument are: <ol> <li> 'int32' <li> 'int64' <li> 'double' <li> 'char*' </ol> <p>The default datatype is 'int32'. <h1>Example Usage</h1> <i>TBD...</i> |
Changes to pages/changes.in.
︙ | ︙ | |||
25 26 27 28 29 30 31 32 33 34 35 36 37 38 | <li>Added support for [WITHOUT ROWID virtual tables]. <li>Improved the query planner so that the [OR optimization] can be used on [virtual tables] even if one or more of the disjuncts use the [LIKE], [GLOB], [REGEXP], [MATCH] operators. <li>Added the [CSV virtual table] for reading [https://www.ietf.org/rfc/rfc4180.txt|RFC 4180] formatted comma-separated value files. <li>Enabled [persistent loadable extensions] using the new [SQLITE_OK_LOAD_PERMANENTLY] return code from the extension entry point. <li>Added the [SQLITE_DBSTATUS_CACHE_USED_SHARED] option to [sqlite3_db_status()]. <li>Add the vfsstat.c loadable extension - a VFS shim that measures I/O together with an [eponymous virtual table] that provides access to the measurements. <li>Improved algorithm for running queries with both an ORDER BY and a LIMIT where | > | 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 | <li>Added support for [WITHOUT ROWID virtual tables]. <li>Improved the query planner so that the [OR optimization] can be used on [virtual tables] even if one or more of the disjuncts use the [LIKE], [GLOB], [REGEXP], [MATCH] operators. <li>Added the [CSV virtual table] for reading [https://www.ietf.org/rfc/rfc4180.txt|RFC 4180] formatted comma-separated value files. <li>Added the [carray() table-valued function] extension. <li>Enabled [persistent loadable extensions] using the new [SQLITE_OK_LOAD_PERMANENTLY] return code from the extension entry point. <li>Added the [SQLITE_DBSTATUS_CACHE_USED_SHARED] option to [sqlite3_db_status()]. <li>Add the vfsstat.c loadable extension - a VFS shim that measures I/O together with an [eponymous virtual table] that provides access to the measurements. <li>Improved algorithm for running queries with both an ORDER BY and a LIMIT where |
︙ | ︙ |
Changes to pages/cli.in.
1 2 3 4 | <title>Command Line Shell For SQLite</title> <tcl>hd_keywords {CLI} {Command Line Interface} {command-line shell} \ {command-line interface} </tcl> | > | > | < < < | > | > | | | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 | <title>Command Line Shell For SQLite</title> <tcl>hd_keywords {CLI} {Command Line Interface} {command-line shell} \ {command-line interface} </tcl> <table_of_contents> <tcl>hd_fragment intro</tcl> <h1>Getting Started</h1> <p>The SQLite project provides a simple command-line utility named <b>sqlite3</b> (or <b>sqlite3.exe</b> on Windows) that allows the user to manually enter and execute SQL statements against an SQLite database. This document provides a brief introduction on how to use the <b>sqlite3</b> program. <p>To start the <b>sqlite3</b> program, simply type "sqlite3" at the command prompt. The "sqlite3" command may be optionally followed by the name the file that holds the SQLite database. If the file does not exist, a new database file with the given name will be created automatically. If no database file is specified on the command-line, a temporary database is created, then deleted when the "sqlite3" program exits. <p>On startup, the <b>sqlite3</b> program will show a brief banner message then prompt you to enter SQL. Type in SQL statements (terminated by a semicolon), press "Enter" and the SQL will be executed.</p> <p>For example, to create a new SQLite database named "ex1" with a single table named "tbl1", you might do this:</p> <tcl> |
︙ | ︙ | |||
79 80 81 82 83 84 85 | sqlite> }</tcl> <tcl>hd_fragment dblclick</tcl> <h1>Double-click Startup On Windows</h1> <p>Windows users can double-click on the <b>sqlite3.exe</b> icon to cause | | > | | | | 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 | sqlite> }</tcl> <tcl>hd_fragment dblclick</tcl> <h1>Double-click Startup On Windows</h1> <p>Windows users can double-click on the <b>sqlite3.exe</b> icon to cause the command-line shell to pop-up a terminal window running SQLite. However, because double-clicking starts the sqlite3.exe without command-line arguments, no database file will have been specified, so SQLite will use a temporary database that is deleted when the session exits. To use a persistent disk file as the database, enter the ".open" command immediately after the terminal window starts up: <tcl>DisplayCode { SQLite version 3.8.5 2014-05-29 12:36:14 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> (((.open ex1.db))) sqlite> }</tcl> <p>The example above causes the database file named "ex1.db" to be opened and used, and created if it does not previously exist. You might want to use a full pathname to ensure that the file is in the directory that you think it is in. Use forward-slashes as the directory separator character. In other words use "c:/work/ex1.db", not "c:\work\ex1.db".</p> <p>Alternatively, you can create a new database using the default temporary storage, then save that database into a disk file using the ".save" command: <tcl>DisplayCode { SQLite version 3.8.5 2014-05-29 12:36:14 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. |
︙ | ︙ | |||
124 125 126 127 128 129 130 | <tcl>hd_fragment dotcmd {dot-commands}</tcl> <h1>Special commands to sqlite3 (dot-commands)</h1> <p> Most of the time, sqlite3 just reads lines of input and passes them on to the SQLite library for execution. | | | | 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 | <tcl>hd_fragment dotcmd {dot-commands}</tcl> <h1>Special commands to sqlite3 (dot-commands)</h1> <p> Most of the time, sqlite3 just reads lines of input and passes them on to the SQLite library for execution. But input lines that begin with a dot (".") are intercepted and interpreted by the sqlite3 program itself. These "dot commands" are typically used to change the output format of queries, or to execute certain prepackaged query statements. </p> <p> For a listing of the available dot commands, you can enter ".help" at any time. For example: |
︙ | ︙ | |||
382 383 384 385 386 387 388 | <h1>Writing results to a file</h1> <p>By default, sqlite3 sends query results to standard output. You can change this using the ".output" and ".once" commands. Just put the name of an output file as an argument to .output and all subsequent query results will be written to that file. Or use the .once command instead of .output and output will only be redirected for the single next | | | 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 | <h1>Writing results to a file</h1> <p>By default, sqlite3 sends query results to standard output. You can change this using the ".output" and ".once" commands. Just put the name of an output file as an argument to .output and all subsequent query results will be written to that file. Or use the .once command instead of .output and output will only be redirected for the single next command before reverting to the console. Use .output with no arguments to begin writing to standard output again. For example:</p> <tcl>DisplayCode { sqlite> (((.mode list))) sqlite> (((.separator |))) sqlite> (((.output test_file_1.txt))) sqlite> (((select * from tbl1;))) |
︙ | ︙ | |||
414 415 416 417 418 419 420 | sqlite3> (((SELECT * FROM bigTable;))) }</tcl> <tcl>hd_fragment fileio {file I/O functions}</tcl> <h2>File I/O Functions</h2> <p>The command-line shell adds two [application-defined SQL functions] that | | | 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 | sqlite3> (((SELECT * FROM bigTable;))) }</tcl> <tcl>hd_fragment fileio {file I/O functions}</tcl> <h2>File I/O Functions</h2> <p>The command-line shell adds two [application-defined SQL functions] that facilitate reading content from a file into an table column, and writing the content of a column into a file, respectively. <p>The readfile(X) SQL function reads the entire content of the file named X and returns that content as a BLOB. This can be used to load content into a table. For example: <tcl>DisplayCode { |
︙ | ︙ |
Changes to pages/dbstat.in.
1 2 | <title>The DBSTAT Virtual Table</title> <tcl>hd_keywords dbstat {dbstat virtual table}</tcl> | | < > | | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | <title>The DBSTAT Virtual Table</title> <tcl>hd_keywords dbstat {dbstat virtual table}</tcl> <fancy_format> <p> The DBSTAT virtual tables is a read-only [eponymous virtual table] that returns information about which pages of the database files are used by which tables and indexes in the schema. The the DBSTAT virtual table is used to implement [sqlite3_analyzer.exe] utility program, and to help compute the [https://www.sqlite.org/src/repo-tabsize|table size pie-chart] in the [https://www.fossil-scm.org/|Fossil-implemented] version control system for SQLite. </p> <h1>Overview</h1> <p> ^The <b>dbstat</b> virtual table is available on all [database connections] when SQLite is built using the [SQLITE_ENABLE_DBSTAT_VTAB] compile-time option. ^The dbstat virtual table provides low-level information about btree and overflow pages in a database file. |
︙ | ︙ | |||
78 79 80 81 82 83 84 | <p> There is a single row of the dbstat table for each page in the database file. Freelist pages, the lock page, and pointer-map pages of the database file do not appear in the dbstat virtual table. | | | 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 | <p> There is a single row of the dbstat table for each page in the database file. Freelist pages, the lock page, and pointer-map pages of the database file do not appear in the dbstat virtual table. <h1>The "path" column of the dbstat virtual table</h1> <p> The "path" column describes the path taken from the root node of the btree structure to each page. The "path" of the root node itself is '/'. The "path" for the left-most child page of the root of |
︙ | ︙ | |||
114 115 116 117 118 119 120 | the overflow pages associated with a cell will appear earlier in the sort-order than its child page: <blockquote><pre> '/1c2/000/' // Left-most child of 451st child of root </pre></blockquote> | | | 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 | the overflow pages associated with a cell will appear earlier in the sort-order than its child page: <blockquote><pre> '/1c2/000/' // Left-most child of 451st child of root </pre></blockquote> <h1>Example uses of the dbstat virtual table</h1> <p> To find the total number of pages used to store table "xyz" in schema "aux1", use: <blockquote><pre> SELECT count(*) FROM dbstat('aux1') WHERE name='xyz'; |
︙ | ︙ |
Changes to pages/fancyformat.tcl.
︙ | ︙ | |||
410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 | # The following three are set by the [parsehtml] callback. The title, # table-of-contents and text of the pre-processed document. # set ::Addtoc(title) "" set ::Addtoc(toc) "" set ::Addtoc(doc) "" parsehtml $zDoc addtoc_cb # Variable $toc is set to the HTML text for the table of contents. The # text "<table_of_contents>" in the input file will be replaced by # this text. The "<div class=startsearch>" tag tells the script that # builds the site-search database not to index any text that occurs # before it. This stops the table of contents from being used for # snippets on search results pages. # set toc [subst { <div class=fancy> <div style="font-size:2em;text-align:center;color:#044a64"> $::Addtoc(title) </div> <div style="font-size:1.5em;margin:1em;color:#044a64"> Table Of Contents</div> <div id=toc> $::Addtoc(toc) </div> <div class=startsearch></div> }] | > > > > > > > > | < | 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 | # The following three are set by the [parsehtml] callback. The title, # table-of-contents and text of the pre-processed document. # set ::Addtoc(title) "" set ::Addtoc(toc) "" set ::Addtoc(doc) "" set ::Addtoc(fancy) "" parsehtml $zDoc addtoc_cb # Variable $toc is set to the HTML text for the table of contents. The # text "<table_of_contents>" in the input file will be replaced by # this text. The "<div class=startsearch>" tag tells the script that # builds the site-search database not to index any text that occurs # before it. This stops the table of contents from being used for # snippets on search results pages. # set toc [subst { <div class=fancy> <div style="font-size:2em;text-align:center;color:#044a64"> $::Addtoc(title) </div> <div style="font-size:1.5em;margin:1em;color:#044a64"> Table Of Contents</div> <div id=toc> $::Addtoc(toc) </div> <div class=startsearch></div> }] set fancy [subst { <div class=fancy> <div style="font-size:2em;text-align:center;color:#044a64"> $::Addtoc(title) </div> <div class=startsearch></div> }] string map [list <table_of_contents> $toc <fancy_format> $fancy] $::Addtoc(doc) } |
Changes to wrap.tcl.
︙ | ︙ | |||
751 752 753 754 755 756 757 | # foreach infile [lrange $argv 3 end] { cd $HOMEDIR puts "Processing $infile" set fd [open $infile r] set in [read $fd] close $fd | | | 751 752 753 754 755 756 757 758 759 760 761 762 763 764 765 | # foreach infile [lrange $argv 3 end] { cd $HOMEDIR puts "Processing $infile" set fd [open $infile r] set in [read $fd] close $fd if {[regexp {<(fancy_format|table_of_contents)>} $in]} { set in [addtoc $in] } set title {No Title} regexp {<title>([^\n]*)</title>} $in all title regsub {<title>[^\n]*</title>} $in {} in set outfile [file root [file tail $infile]].html hd_open_main $outfile db eval { INSERT INTO page(filename,pagetitle) |
︙ | ︙ | |||
783 784 785 786 787 788 789 | proc hd_requirement {args} {} foreach infile [lrange $argv 3 end] { cd $HOMEDIR puts "Processing $infile" set fd [open $infile r] set in [read $fd] close $fd | | | 783 784 785 786 787 788 789 790 791 792 793 794 795 796 797 | proc hd_requirement {args} {} foreach infile [lrange $argv 3 end] { cd $HOMEDIR puts "Processing $infile" set fd [open $infile r] set in [read $fd] close $fd if {[regexp {<(fancy_format|table_of_contents)>} $in]} { set in [addtoc $in] } set title {No Title} regexp {<title>([^\n]*)</title>} $in all title regsub {<title>[^\n]*</title>} $in {} in set outfile [file root [file tail $infile]].html hd_open_main $outfile hd_header $title $infile regsub -all {<tcl>} $in "\175; eval \173" in |
︙ | ︙ |