Documentation Source Text

Check-in [e2ad1111f4]
Login

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.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: e2ad1111f4fc208a6a4bb8513cef819db39f6461
User & Date: drh 2016-01-02 04:16:21
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
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to pages/changes.in.

28
29
30
31
32
33
34










35
36
37





38

39
40
41
42
43
44
45
46
47
48
49
<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>

<li>Added the --enable-editline option 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







>
>
>
>
>
>
>
>
>
>



>
>
>
>
>

>
|
<
|
<







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

123
124
125
126
127
128
129
130
...
137
138
139
140
141
142
143

144
145
146
147
148
149
150
...
194
195
196
197
198
199
200

201
202
203
204
205
206
207
}</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.


<h3>Special commands to sqlite3</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
................................................................................
</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

.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
................................................................................
.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>







>
|







 







>







 







>







116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
...
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
...
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
}</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
................................................................................
</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
................................................................................
.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
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 and 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>







|







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
7
8
9
10
11
12
13
14
15
16
17
..
89
90
91
92
93
94
95
















96
97
98
99
100
101
102
...
508
509
510
511
512
513
514















515
516
517
518
519
520
521
522
523
524
525
...
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
<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 eleven [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 SQL functions implemented by json1:

<blockquote>
<center><table border=0 cellpadding=5>
<tcl>
set tabcnt 0
proc tabentry {fx desc lnk} {
  global tabcnt
................................................................................
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>
................................................................................

<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>
<h3>3.9 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.
................................................................................

<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.10.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






|


|







 







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







 







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



|







 







|







1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
..
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
...
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
...
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
<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
................................................................................
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>
................................................................................

<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.
................................................................................

<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
1955
1956
1957
1958
1959
1960
1961
1962
....
2917
2918
2919
2920
2921
2922
2923
2924
2925
2926
2927
2928

2929
2930
2931
2932
2933
2934
2935
2936
<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, and REGEXP 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
................................................................................
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}</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" 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>







|







 







|




>
|







1948
1949
1950
1951
1952
1953
1954
1955
1956
1957
1958
1959
1960
1961
1962
....
2917
2918
2919
2920
2921
2922
2923
2924
2925
2926
2927
2928
2929
2930
2931
2932
2933
2934
2935
2936
2937
<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
................................................................................
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
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 tend sto 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>







|







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>