Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Cherrypick changes off of the bubble-generator-tkpath branch that were intended for trunk. Also merge in changes to the 3.9.x branch. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
e2ad1111f4fc208a6a4bb8513cef819d |
User & Date: | drh 2016-01-02 04:16:21.598 |
Context
2016-01-04
| ||
00:44 | Add the SQLITE_LIKE_DOESNT_MATCH_BLOBS compile-time option to the changes log. (check-in: df761d0907 user: drh tags: trunk) | |
2016-01-02
| ||
04:16 | Cherrypick changes off of the bubble-generator-tkpath branch that were intended for trunk. Also merge in changes to the 3.9.x branch. (check-in: e2ad1111f4 user: drh tags: trunk) | |
04:14 | Further tweaks to the website. (Leaf check-in: 5619b986d5 user: drh tags: bubble-generator-tkpath) | |
2015-12-31
| ||
14:06 | Update the change log with the latest enhancements. (check-in: 5077172640 user: drh tags: bubble-generator-tkpath) | |
2015-12-30
| ||
15:10 | Fix a problem in an SQL example in fts3.html. (check-in: 4648330f75 user: dan tags: trunk) | |
2015-12-28
| ||
16:20 | Add links to the built-in SQL function documentation to the docs.html page. (Leaf check-in: 51cd2291e6 user: drh tags: branch-3.9) | |
Changes
Changes to pages/changes.in.
︙ | ︙ | |||
28 29 30 31 32 33 34 35 36 37 38 | <li>Enhance the [PRAGMA cache_spill] statement to accept a 32-bit integer parameter which is the threshold below which cache spilling is prohibited. <li>On unix, if a symlink to a database file is opened, then the corresponding journal files are based on the actual filename, not the symlink name. <li>Added the "--transaction" option to [sqldiff]. <li>Added the [sqlite3_db_cacheflush()] interface. <li>Added the [sqlite3_strlike()] interface. <li>Many small performance optimizations. <p><b>Portability enhancements:</b> <li>Work around a sign-exension bug in the optimizer of the HP C compiler on HP/UX. <p><b>Enhancements to makefiles:</b> | > > > > > > > > > > > > > > > | | | < | 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 | <li>Enhance the [PRAGMA cache_spill] statement to accept a 32-bit integer parameter which is the threshold below which cache spilling is prohibited. <li>On unix, if a symlink to a database file is opened, then the corresponding journal files are based on the actual filename, not the symlink name. <li>Added the "--transaction" option to [sqldiff]. <li>Added the [sqlite3_db_cacheflush()] interface. <li>Added the [sqlite3_strlike()] interface. <li>When using [memory-mapped I/O] map the database file read-only so that stray pointers and/or array overruns in the application cannot accidently modify the database file. <li>Added the <em>experimental</em> [sqlite3_snapshot_get()], [sqlite3_snapshot_open()], and [sqlite3_snapshot_free()] interfaces. These are subject to change or removal in a subsequent release. <li>Enhance the ['utc' modifier] in the [date and time functions] so that it is a no-op if the date/time is known to already be in UTC. (This is not a compatibility break since the behavior has long been documented as "undefined" in that case.) <li>Added the [json_group_array()] and [json_group_object()] SQL functions in the [json] extension. <li>Many small performance optimizations. <p><b>Portability enhancements:</b> <li>Work around a sign-exension bug in the optimizer of the HP C compiler on HP/UX. [https://www.sqlite.org/src/fdiff?sbs=1&v1=869c95b0fc73026d&v2=232c242a0ccb3d67|(details)] <p><b>Enhancements to the [command-line shell]:</b> <li>Added the ".changes ON|OFF" and ".vfsinfo" [dot-commands]. <li>Translate between MBCS and UTF8 when running in [https://en.wikipedia.org/wiki/Cmd.exe|cmd.exe] on Windows. <p><b>Enhancements to makefiles:</b> <li>Added the --enable-editline and --enable-static-shell options to the various autoconf-generated configure scripts. <li>Omit all use of "awk" in the makefiles, to make building easier for MSVC users. <p><b>Important fixes:</b> <li>Fix inconsistent integer to floating-point comparison operations that could result in a corrupt index if the index is created on a table column that contains both large integers and floating point values of similar magnitude. Ticket [https://www.sqlite.org/src/tktview?name=38a97a87a6|38a97a87a6]. <li>Fix an infinite-loop in the query planner that could occur on |
︙ | ︙ |
Changes to pages/cli.in.
︙ | ︙ | |||
116 117 118 119 120 121 122 | }</tcl> <p>Be careful when using the ".save" command as it will overwrite any preexisting database files having the same name without prompting for confirmation. As with the ".open" command, you might want to use a full pathname with forward-slash directory separators to avoid ambiguity. | > | > | 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 | }</tcl> <p>Be careful when using the ".save" command as it will overwrite any preexisting database files having the same name without prompting for confirmation. As with the ".open" command, you might want to use a full pathname with forward-slash directory separators to avoid ambiguity. <tcl>hd_fragment dotcmd {dot-commands}</tcl> <h3>Special commands to sqlite3 (dot-commands)</h3> <p> Most of the time, sqlite3 just reads lines of input and passes them on to the SQLite library for execution. But if an input line begins with a dot ("."), then that line is 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: </p> <tcl>DisplayCode { sqlite> (((.help))) .backup ?DB? FILE Backup DB (default "main") to FILE .bail on|off Stop after hitting an error. Default OFF .binary on|off Turn binary output on or off. Default OFF .changes on|off Show number of rows changed by SQL .clone NEWDB Clone data into NEWDB from the existing database .databases List names and files of attached databases .dbinfo ?DB? Show status information about the database .dump ?TABLE? ... Dump the database in an SQL text format If TABLE specified, only dump tables matching LIKE pattern TABLE. .echo on|off Turn command echo on or off |
︙ | ︙ | |||
194 195 196 197 198 199 200 201 202 203 204 205 206 207 | .system CMD ARGS... Run CMD ARGS... in a system shell .tables ?TABLE? List names of tables If TABLE specified, only list tables matching LIKE pattern TABLE. .timeout MS Try opening locked tables for MS milliseconds .timer on|off Turn SQL timer on or off .trace FILE|off Output each SQL statement as it is run .vfsname ?AUX? Print the name of the VFS stack .width NUM1 NUM2 ... Set column widths for "column" mode Negative values right-justify sqlite> }</tcl> <h3>Rules for "dot-commands"</h3> | > | 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 | .system CMD ARGS... Run CMD ARGS... in a system shell .tables ?TABLE? List names of tables If TABLE specified, only list tables matching LIKE pattern TABLE. .timeout MS Try opening locked tables for MS milliseconds .timer on|off Turn SQL timer on or off .trace FILE|off Output each SQL statement as it is run .vfsinfo ?AUX? Information about the top-level VFS .vfsname ?AUX? Print the name of the VFS stack .width NUM1 NUM2 ... Set column widths for "column" mode Negative values right-justify sqlite> }</tcl> <h3>Rules for "dot-commands"</h3> |
︙ | ︙ |
Changes to pages/docs.in.
︙ | ︙ | |||
98 99 100 101 102 103 104 105 106 107 108 109 110 111 | This document describes the SQL language that is understood by SQLite. } doc {Pragma commands} {pragma.html} { This document describes SQLite performance tuning options and other special purpose database commands. } doc {DataTypes} {datatype3.html} { SQLite version 3 introduces the concept of manifest typing, where the type of a value is associated with the value itself, not the column that it is stored in. This page describes data typing for SQLite version 3 in further detail. } | > > > > > > > > > > > > | 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 | This document describes the SQL language that is understood by SQLite. } doc {Pragma commands} {pragma.html} { This document describes SQLite performance tuning options and other special purpose database commands. } doc {Core SQL Functions} {lang_corefunc.html} { General-purpose built-in scalar SQL functions. } doc {Aggregate SQL Functions} {lang_aggfunc.html} { General-purpose built-in aggregate SQL functions. } doc {Date and Time SQL Functions} {lang_datefunc.html} { SQL functions for manipulating dates and times. } doc {JSON SQL Functions} {json1.html} { SQL functions for creating, parsing, and querying JSON content. } doc {DataTypes} {datatype3.html} { SQLite version 3 introduces the concept of manifest typing, where the type of a value is associated with the value itself, not the column that it is stored in. This page describes data typing for SQLite version 3 in further detail. } |
︙ | ︙ |
Changes to pages/fileformat2.in.
︙ | ︙ | |||
647 648 649 650 651 652 653 | values. A varint is between 1 and 9 bytes in length. The varint consists of either zero or more byte which have the high-order bit set followed by a single byte with the high-order bit clear, or nine bytes, whichever is shorter. The lower seven bits of each of the first eight bytes and all 8 bits of the ninth byte are used to reconstruct the 64-bit twos-complement integer. Varints are big-endian: bits taken from the earlier byte of the varint | | | 647 648 649 650 651 652 653 654 655 656 657 658 659 660 661 | values. A varint is between 1 and 9 bytes in length. The varint consists of either zero or more byte which have the high-order bit set followed by a single byte with the high-order bit clear, or nine bytes, whichever is shorter. The lower seven bits of each of the first eight bytes and all 8 bits of the ninth byte are used to reconstruct the 64-bit twos-complement integer. Varints are big-endian: bits taken from the earlier byte of the varint are the more significant than bits taken from the later bytes. </p> <p>The format of a cell depends on which kind of b-tree page the cell appears on. The following table shows the elements of a cell, in order of appearance, for the various b-tree page types.</p> <blockquote><dl> <dt><p>Table B-Tree Leaf Cell (header 0x0d):</p></dt> |
︙ | ︙ |
Changes to pages/json1.in.
1 2 3 4 5 6 | <title>The JSON1 Extension</title> <tcl>hd_keywords json1 {the json1 extension} {JSON SQL functions}</tcl> <h2>The JSON1 Extension</h2> <p> The <b>json1</b> extension is a [loadable extension] that | | | | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | <title>The JSON1 Extension</title> <tcl>hd_keywords json1 {the json1 extension} {JSON SQL functions}</tcl> <h2>The JSON1 Extension</h2> <p> The <b>json1</b> extension is a [loadable extension] that implements thirteen [application-defined SQL functions] and two [table-valued functions] that are useful for managing [http://json.org/ | JSON] content stored in an SQLite database. These are the scalar SQL functions implemented by json1: <blockquote> <center><table border=0 cellpadding=5> <tcl> set tabcnt 0 proc tabentry {fx desc lnk} { global tabcnt |
︙ | ︙ | |||
89 90 91 92 93 94 95 96 97 98 99 100 101 102 | tabentry {json_type(json)<br>json_type(json,path)} { Return the type of a JSON string or subcomponent. } jtype tabentry {json_valid(json)} { Return true (1) if the input text is a valid JSON string } jvalid </tcl> </table></center></blockquote> <p>The [table-valued functions] implemented by this routine are: <blockquote><center><table border=0 cellpadding=5> <tcl> | > > > > > > > > > > > > > > > > | 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 117 118 | tabentry {json_type(json)<br>json_type(json,path)} { Return the type of a JSON string or subcomponent. } jtype tabentry {json_valid(json)} { Return true (1) if the input text is a valid JSON string } jvalid </tcl> </table></center></blockquote> <p>There are two aggregate SQL functions: <blockquote><center><table border=0 cellpadding=5> <tcl> tabentry {json_group_array(value)} { Return a JSON array composed of all <i>value</i> elements in the aggregation. } jgrouparray tabentry {json_group_object(name,value)} { Return a JSON object composed of all <i>name</i> and <i>value</i> pairs in the aggregation. } jgroupobject </tcl> </table></center></blockquote> <p>The [table-valued functions] implemented by this routine are: <blockquote><center><table border=0 cellpadding=5> <tcl> |
︙ | ︙ | |||
508 509 510 511 512 513 514 515 516 517 | <tcl> jexample \ {json_valid('{"x":35}')} 1 \ "json_valid('\173\"x\":35')" 0 </tcl> <tcl>hd_fragment jeach {json_each table-valued function} {json_each}</tcl> <tcl>hd_fragment jtree {json_tree table-valued function} {json_tree}</tcl> | > > > > > > > > > > > > > > > | | 524 525 526 527 528 529 530 531 532 533 534 535 536 537 538 539 540 541 542 543 544 545 546 547 548 549 550 551 552 553 554 555 556 | <tcl> jexample \ {json_valid('{"x":35}')} 1 \ "json_valid('\173\"x\":35')" 0 </tcl> <tcl> hd_fragment jgrouparray {json_group_array SQL function} \ {json_group_array} hd_fragment jgroupobject {json_group_object SQL function} \ {json_group_object} </tcl> <h3>3.10 The json_group_array() and json_group_object() aggregate SQL functions</h3> <p>The json_group_array(X) function is an [Aggregate Functions|aggregate SQL function] that returns a JSON array comprised of all X values in the aggregation. Similarly, the json_group_object(NAME,VALUE) function returns a JSON object comprised of all NAME/VALUE pairs in the aggregation. <tcl>hd_fragment jeach {json_each table-valued function} {json_each}</tcl> <tcl>hd_fragment jtree {json_tree table-valued function} {json_tree}</tcl> <h3>3.11 The json_each() and json_tree() table-valued functions</h3> <p>The json_each(X) and json_tree(X) [table-valued functions] walk the JSON value provided as their first argument and return one row for each element. The json_each(X) function only walks the immediate children of the top-level array or object or or just the top-level element itself if the top-level element is a primitive value. |
︙ | ︙ | |||
586 587 588 589 590 591 592 | <p> The "path" column is the path to the array or object container the holds the current row, or the path to the current row in the case where the iteration starts on a primitive type and thus only provides a single row of output. | | | 617 618 619 620 621 622 623 624 625 626 627 628 629 630 631 | <p> The "path" column is the path to the array or object container the holds the current row, or the path to the current row in the case where the iteration starts on a primitive type and thus only provides a single row of output. <h4>3.11.1 Examples using json_each() and json_tree()</h4> <p>Suppose the table "CREATE TABLE user(name,phone)" stores zero or more phone numbers as a JSON array object in the user.phone field. To find all users who have any phone number with a 704 area code: <blockquote><pre> SELECT DISTINCT user.name |
︙ | ︙ |
Changes to pages/lang.in.
︙ | ︙ | |||
1948 1949 1950 1951 1952 1953 1954 | <p>^The maximum parameter number is set at compile-time by the [SQLITE_MAX_VARIABLE_NUMBER] macro. ^(An individual [database connection] D can reduce its maximum parameter number below the compile-time maximum using the [sqlite3_limit](D, [SQLITE_LIMIT_VARIABLE_NUMBER],...) interface.)^ </p> <tcl>hd_fragment like LIKE ESCAPE</tcl> | | | 1948 1949 1950 1951 1952 1953 1954 1955 1956 1957 1958 1959 1960 1961 1962 | <p>^The maximum parameter number is set at compile-time by the [SQLITE_MAX_VARIABLE_NUMBER] macro. ^(An individual [database connection] D can reduce its maximum parameter number below the compile-time maximum using the [sqlite3_limit](D, [SQLITE_LIMIT_VARIABLE_NUMBER],...) interface.)^ </p> <tcl>hd_fragment like LIKE ESCAPE</tcl> <h3>The LIKE, GLOB, REGEXP, and MATCH operators</h3> <p>^The LIKE operator does a pattern matching comparison. ^The operand to the right of the LIKE operator contains the pattern and the left hand operand contains the string to match against the pattern. <tcl>hd_puts "^A percent symbol (\"%\") in the LIKE pattern matches any sequence of zero or more characters in the string. ^An underscore (\"_\") in the LIKE pattern matches any single character in the |
︙ | ︙ | |||
2917 2918 2919 2920 2921 2922 2923 | separate the "unixepoch" modifier from prior DDDDDDDDDD then the behavior is undefined. Due to precision limitations imposed by the implementations use of 64-bit integers, the "unixepoch" modifier only works for dates between 0000-01-01 00:00:00 and 5352-11-01 10:52:47 (unix times of -62167219200 through 10675199167).</p> | | > | | 2917 2918 2919 2920 2921 2922 2923 2924 2925 2926 2927 2928 2929 2930 2931 2932 2933 2934 2935 2936 2937 | separate the "unixepoch" modifier from prior DDDDDDDDDD then the behavior is undefined. Due to precision limitations imposed by the implementations use of 64-bit integers, the "unixepoch" modifier only works for dates between 0000-01-01 00:00:00 and 5352-11-01 10:52:47 (unix times of -62167219200 through 10675199167).</p> <tcl>hd_fragment localtime {localtime modifier} {'utc' modifier}</tcl> <p>^The "localtime" modifier (12) assumes the time string to its left is in Universal Coordinated Time (UTC) and adjusts the time string so that it displays localtime. If "localtime" follows a time that is not UTC, then the behavior is undefined. ^(The "utc" modifier is the opposite of "localtime". "utc" assumes that the string to its left is in the local timezone and adjusts that string to be in UTC.)^ If the prior string is not in localtime, then the result of "utc" is undefined.</p> <h3>Examples</h3> ^(<p>Compute the current date.<p> |
︙ | ︙ |
Changes to pages/mostdeployed.in.
︙ | ︙ | |||
51 52 53 54 55 56 57 | <p> Precise numbers are difficult to obtain and so exact rankings are impossible. But our best guess is that SQLite is the second mostly widely deployed software library, after libz. Some commentators observe that SQLite tends to be statically linked and thus have multiple instances on | | | 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 | <p> Precise numbers are difficult to obtain and so exact rankings are impossible. But our best guess is that SQLite is the second mostly widely deployed software library, after libz. Some commentators observe that SQLite tends to be statically linked and thus have multiple instances on each machine, whereas libz tends to have just a single instance per machine in the form of a shared library or DLL. So even though the number of devices containing libz may be greater than the number of devices that contain SQLite, the total number of instances per device <em>might</em> be higher for SQLite and so SQLite <em>might</em> be the single most widely deployed and used software component. </p> |