Documentation Source Text

Check-in [b9fd0996f1]
Login

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

Overview
Comment:Documentation changes in preparation for the 3.6.11 release.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: b9fd0996f18ac3ae6725943ada610beaf01b4497
User & Date: drh 2009-02-04 23:29:41
Context
2009-02-05
02:33
Enhancements to the SQL language documentation. check-in: c8dc1e13d7 user: drh tags: trunk
2009-02-04
23:29
Documentation changes in preparation for the 3.6.11 release. check-in: b9fd0996f1 user: drh tags: trunk
2009-02-02
13:04
Fix a typo on the homepage. check-in: b8436f59f5 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to pages/changes.in.

37
38
39
40
41
42
43








44
45
46
47
48
49
50
      <a href="http://www.sqlite.org/cvstrac/timeline">
      http://www.sqlite.org/cvstrac/timeline</a>.</p>
    }
    hd_close_aux
    hd_enable_main 1
  }
}









chng {2009 Jan 15 (3.6.10)} {
<li>Fix a cache coherency problem that could lead to database corruption.
    [Ticket #3584].
}

chng {2009 Jan 14 (3.6.9)} {







>
>
>
>
>
>
>
>







37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
      <a href="http://www.sqlite.org/cvstrac/timeline">
      http://www.sqlite.org/cvstrac/timeline</a>.</p>
    }
    hd_close_aux
    hd_enable_main 1
  }
}

chng {2009 Feb 18 (3.6.11)} {
<li>Added the [sqlite3_backup_init | hot-backup interface].
<li>Added new commands ".backup" and ".restore" to the [CLI].
<li>Added new methods <a href="tclsqlite.html#backup">backup</a>
    <a href="tclsqlite.html#restore">restore</a> to the TCL interface.
<li>Various minor bug fixes
}

chng {2009 Jan 15 (3.6.10)} {
<li>Fix a cache coherency problem that could lead to database corruption.
    [Ticket #3584].
}

chng {2009 Jan 14 (3.6.9)} {

Changes to pages/index.in.

76
77
78
79
80
81
82
83
84

85
86
87
88
89
90
91
92

</td>
<td width="20"></td><td bgcolor="#80a796" width="1"></td><td width="20"></td>
<td valign="top">
<h3>Current Status</h3>

<p><ul>
<li><a href="releaselog/3_6_10.html">Version 3.6.10</a>
of SQLite is recommended for all new development.  Upgrading from

all prior versions is  recommended.</li>
</ul></p>

<h3>Common Links</h3>

<p><ul>
<li> <a href="features.html">Features</a> </li>
<li> <a href="faq.html">Frequently Asked Questions</a> </li>







|

>
|







76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93

</td>
<td width="20"></td><td bgcolor="#80a796" width="1"></td><td width="20"></td>
<td valign="top">
<h3>Current Status</h3>

<p><ul>
<li><a href="releaselog/3_6_11.html">Version 3.6.11</a>
of SQLite is recommended for all new development.  Upgrading from
version 3.6.10 is optional.  Upgrading from versions prior to 3.6.10
is recommended.</li>
</ul></p>

<h3>Common Links</h3>

<p><ul>
<li> <a href="features.html">Features</a> </li>
<li> <a href="faq.html">Frequently Asked Questions</a> </li>

Changes to pages/lang.in.

154
155
156
157
158
159
160






161
162
163
164
165
166
167
....
1878
1879
1880
1881
1882
1883
1884
1885

1886

1887
1888
1889
1890
1891
1892
1893
1894
1895
1896
1897
1898
1899

1900
1901
1902
1903
1904


1905
1906
1907
1908
1909

1910
1911

1912
1913
1914
1915
1916
1917
1918
....
1927
1928
1929
1930
1931
1932
1933
1934
1935
1936
1937
1938
1939
1940
1941
<ul>
<li>The column may not have a PRIMARY KEY or UNIQUE constraint.</li>
<li>The column may not have a default value of CURRENT_TIME, CURRENT_DATE 
    or CURRENT_TIMESTAMP.</li>
<li>If a NOT NULL constraint is specified, then the column must have a
    default value other than NULL.
</ul>







<p>The execution time of the ALTER TABLE command is independent of
the amount of data in the table.  The ALTER TABLE command runs as quickly
on a table with 10 million rows as it does on a table with 1 row.
</p>

<p>After ADD COLUMN has been run on a database, that database will not
................................................................................

<table border=0 cellpadding=10>
<tcl>
funcdef {avg(X)} {} {
  Return the average value of all non-NULL <i>X</i> within a
  group.  String and BLOB values that do not look like numbers are
  interpreted as 0.
  The result of avg() is always a floating point value even if all

  inputs are integers.

}

funcdef {count(X) count(*)} {} {
  The first form return a count of the number of times
  that <i>X</i> is not NULL in a group.  The second form (with no argument)
  returns the total number of rows in the group.
}

funcdef {group_concat(X) group_concat(X,Y)} {} {
  The result is a string which is the concatenation of
  all non-NULL values of <i>X</i>.  If parameter <i>Y</i> is the separator
  between instances of <i>X</i>.  A comma (",") is used as the separator
  if <i>Y</i> is omitted.

}

funcdef {max(X)} {maxAggFunc agg_max} {
  Return the maximum value of all values in the group.
  The usual sort order is used to determine the maximum.


}

funcdef {min(X)} {minAggFunc agg_min} {
  Return the minimum non-NULL value of all values in the group.
  The usual sort order is used to determine the minimum.  

  NULL is only returned
  if all values in the group are NULL.

}

funcdef {sum(X) total(X)} {sumFunc sum total} {
  Return the numeric sum of all non-NULL values in the group.
  If there are no non-NULL input rows then sum() returns
  NULL but total() returns 0.0.
  NULL is not normally a helpful result for the sum of no rows
................................................................................
  If any input to sum() is neither an integer or a NULL
  then sum() returns a floating point value
  which might be an approximation to the true sum.</p>

  <p>Sum() will throw an "integer overflow" exception if all inputs
  are integers or NULL
  and an integer overflow occurs at any point during the computation.
  Total() never throws an exception.
}
</tcl>
</table>

<tcl>
##############################################################################
Section INSERT insert {INSERT INSERTs}







>
>
>
>
>
>







 







|
>
|
>












|
>




|
>
>




|
>
|
<
>







 







|







154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
....
1884
1885
1886
1887
1888
1889
1890
1891
1892
1893
1894
1895
1896
1897
1898
1899
1900
1901
1902
1903
1904
1905
1906
1907
1908
1909
1910
1911
1912
1913
1914
1915
1916
1917
1918
1919
1920
1921
1922

1923
1924
1925
1926
1927
1928
1929
1930
....
1939
1940
1941
1942
1943
1944
1945
1946
1947
1948
1949
1950
1951
1952
1953
<ul>
<li>The column may not have a PRIMARY KEY or UNIQUE constraint.</li>
<li>The column may not have a default value of CURRENT_TIME, CURRENT_DATE 
    or CURRENT_TIMESTAMP.</li>
<li>If a NOT NULL constraint is specified, then the column must have a
    default value other than NULL.
</ul>

<p>Note also that when adding a CHECK constraint, the CHECK constraint
is not tested against preexisting rows of the table.
This can result in a table that contains data that
is in violation of the CHECK constraint.  Future versions of SQLite might
change to validate CHECK constraints as they are added.</p>

<p>The execution time of the ALTER TABLE command is independent of
the amount of data in the table.  The ALTER TABLE command runs as quickly
on a table with 10 million rows as it does on a table with 1 row.
</p>

<p>After ADD COLUMN has been run on a database, that database will not
................................................................................

<table border=0 cellpadding=10>
<tcl>
funcdef {avg(X)} {} {
  Return the average value of all non-NULL <i>X</i> within a
  group.  String and BLOB values that do not look like numbers are
  interpreted as 0.
  The result of avg() is always a floating point value as long as
  at there is at least one non-NULL input even if all
  inputs are integers.  The result of avg() NULL if and only if
  there are no non-NULL inputs.  
}

funcdef {count(X) count(*)} {} {
  The first form return a count of the number of times
  that <i>X</i> is not NULL in a group.  The second form (with no argument)
  returns the total number of rows in the group.
}

funcdef {group_concat(X) group_concat(X,Y)} {} {
  The result is a string which is the concatenation of
  all non-NULL values of <i>X</i>.  If parameter <i>Y</i> is the separator
  between instances of <i>X</i>.  A comma (",") is used as the separator
  if <i>Y</i> is omitted.  The order of the concatenated elements is
  arbitrary.
}

funcdef {max(X)} {maxAggFunc agg_max} {
  Return the maximum value of all values in the group.
  The maximum value is the value that would be returned last in an
  ORDER BY on the same column.  NULL is returned if and only if there are
  no non-NULL values in the group.
}

funcdef {min(X)} {minAggFunc agg_min} {
  Return the minimum non-NULL value of all values in the group.
  The minimum value is the first non-NULL value that would appear
  in an ORDER BY of the column.
  NULL is only returned if and only if there are no non-NULL values in the 

  group.
}

funcdef {sum(X) total(X)} {sumFunc sum total} {
  Return the numeric sum of all non-NULL values in the group.
  If there are no non-NULL input rows then sum() returns
  NULL but total() returns 0.0.
  NULL is not normally a helpful result for the sum of no rows
................................................................................
  If any input to sum() is neither an integer or a NULL
  then sum() returns a floating point value
  which might be an approximation to the true sum.</p>

  <p>Sum() will throw an "integer overflow" exception if all inputs
  are integers or NULL
  and an integer overflow occurs at any point during the computation.
  Total() never throws and integer overflow.
}
</tcl>
</table>

<tcl>
##############################################################################
Section INSERT insert {INSERT INSERTs}

Changes to pages/news.in.

14
15
16
17
18
19
20









21
22
23
24
25
26
27
  hd_puts "<h3>$date - $title</h3>"
  regsub -all "\n( *\n)+" $text "</p>\n\n<p>" txt
  regsub -all {[Tt]icket #(\d+)} $txt \
      {<a href="http://www.sqlite.org/cvstrac/tktview?tn=\1">\0</a>} txt
  hd_resolve "<p>$txt</p>"
  hd_puts "<hr width=\"50%\">"
}










newsitem {2009-Jan-15} {Version 3.6.10} {
  SQLite [version 3.6.10] fixes a cache coherency bug (Ticket #3584)
  introduced by check-in
  <a href="http://www.sqlite.org/cvstrac/chngview?cn=5864">[5864]</a>
  which was part of [version 3.6.5].  This bug might lead to database
  corruption, hence we felt it was important to get it out as quickly







>
>
>
>
>
>
>
>
>







14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
  hd_puts "<h3>$date - $title</h3>"
  regsub -all "\n( *\n)+" $text "</p>\n\n<p>" txt
  regsub -all {[Tt]icket #(\d+)} $txt \
      {<a href="http://www.sqlite.org/cvstrac/tktview?tn=\1">\0</a>} txt
  hd_resolve "<p>$txt</p>"
  hd_puts "<hr width=\"50%\">"
}

newsitem {2009-Feb-18} {Version 3.6.11} {
  SQLite [version 3.6.11] adds support for the
  [sqlite3_backup_init | hot-backup] interface.  This interface can be
  used to create a backup copy of an SQLite database while it is in use.
  The same interface can be used to initialize an in-memory database from
  a persistent disk image or to save an in-memory database into a
  persistent disk image.
}

newsitem {2009-Jan-15} {Version 3.6.10} {
  SQLite [version 3.6.10] fixes a cache coherency bug (Ticket #3584)
  introduced by check-in
  <a href="http://www.sqlite.org/cvstrac/chngview?cn=5864">[5864]</a>
  which was part of [version 3.6.5].  This bug might lead to database
  corruption, hence we felt it was important to get it out as quickly

Changes to pages/sqlite.in.

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
..
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
...
120
121
122
123
124
125
126

127
128
129
130
131
132
133
134
135
136

137
138
139
140
141
142
143
...
146
147
148
149
150
151
152

153
154
155
156
157

158
159
160
161
162
163
164
  regsub -all \n $body <br>\n body
  hd_puts $body
  hd_puts {</tt></blockquote>}
}

Code {
$ (((sqlite3 ex1)))
SQLite version 3.3.10
Enter ".help" for instructions

sqlite> (((create table tbl1(one varchar(10), two smallint);)))
sqlite> (((insert into tbl1 values('hello!',10);)))
sqlite> (((insert into tbl1 values('goodbye', 20);)))
sqlite> (((select * from tbl1;)))
hello!|10
goodbye|20
sqlite>
}
</tcl>

<p>You can terminate the sqlite3 program by typing your systems
End-Of-File character (usually a Control-D) or the interrupt
character (usually a Control-C).</p>

<p>Make sure you type a semicolon at the end of each SQL command!
The sqlite3 program looks for a semicolon to know when your SQL command is
complete.  If you omit the semicolon, sqlite3 will give you a
continuation prompt and wait for you to enter more text to be
added to the current SQL command.  This feature allows you to
enter SQL commands that span multiple lines.  For example:</p>
................................................................................
a special table named "sqlite_master".
You can execute "SELECT" statements against the
special sqlite_master table just like any other table
in an SQLite database.  For example:</p>

<tcl>Code {
$ (((sqlite3 ex1)))
SQlite vresion 3.3.10
Enter ".help" for instructions
sqlite> (((select * from sqlite_master;)))
    type = table
    name = tbl1
tbl_name = tbl1
rootpage = 3
     sql = create table tbl1(one varchar(10), two smallint)
................................................................................
<p>
For a listing of the available dot commands, you can enter ".help"
at any time.  For example:
</p>

<tcl>Code {
sqlite> (((.help)))

.bail ON|OFF           Stop after hitting an error.  Default OFF
.databases             List names and files of attached databases
.dump ?TABLE? ...      Dump the database in an SQL text format
.echo ON|OFF           Turn command echo on or off
.exit                  Exit this program
.explain ON|OFF        Turn output mode suitable for EXPLAIN on or off.
.header(s) ON|OFF      Turn display of headers on or off
.help                  Show this message
.import FILE TABLE     Import data from FILE into TABLE
.indices TABLE         Show names of all indices on TABLE

.load FILE ?ENTRY?     Load an extension library
.mode MODE ?TABLE?     Set output mode where MODE is one of:
                         csv      Comma-separated values
                         column   Left-aligned columns.  (See .width)
                         html     HTML <table> code
                         insert   SQL insert statements for TABLE
                         line     One value per line
................................................................................
                         tcl      TCL list elements
.nullvalue STRING      Print STRING in place of NULL values
.output FILENAME       Send output to FILENAME
.output stdout         Send output to the screen
.prompt MAIN CONTINUE  Replace the standard prompts
.quit                  Exit this program
.read FILENAME         Execute SQL in FILENAME

.schema ?TABLE?        Show the CREATE statements
.separator STRING      Change separator used by output mode and .import
.show                  Show the current values for various settings
.tables ?PATTERN?      List names of tables matching a LIKE pattern
.timeout MS            Try opening locked tables for MS milliseconds

.width NUM NUM ...     Set column widths for "column" mode
sqlite> 
}</tcl>

<h3>Changing Output Formats</h3>

<p>The sqlite3 program is able to show the results of a query







|

>











|
|







 







|







 







>










>







 







>





>







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
..
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
...
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
...
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
  regsub -all \n $body <br>\n body
  hd_puts $body
  hd_puts {</tt></blockquote>}
}

Code {
$ (((sqlite3 ex1)))
SQLite version 3.6.11
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> (((create table tbl1(one varchar(10), two smallint);)))
sqlite> (((insert into tbl1 values('hello!',10);)))
sqlite> (((insert into tbl1 values('goodbye', 20);)))
sqlite> (((select * from tbl1;)))
hello!|10
goodbye|20
sqlite>
}
</tcl>

<p>You can terminate the sqlite3 program by typing your systems
End-Of-File character (usually a Control-D).  Use the interrupt
character (usually a Control-C) to stop a long-running SQL statement.</p>

<p>Make sure you type a semicolon at the end of each SQL command!
The sqlite3 program looks for a semicolon to know when your SQL command is
complete.  If you omit the semicolon, sqlite3 will give you a
continuation prompt and wait for you to enter more text to be
added to the current SQL command.  This feature allows you to
enter SQL commands that span multiple lines.  For example:</p>
................................................................................
a special table named "sqlite_master".
You can execute "SELECT" statements against the
special sqlite_master table just like any other table
in an SQLite database.  For example:</p>

<tcl>Code {
$ (((sqlite3 ex1)))
SQLite vresion 3.6.11
Enter ".help" for instructions
sqlite> (((select * from sqlite_master;)))
    type = table
    name = tbl1
tbl_name = tbl1
rootpage = 3
     sql = create table tbl1(one varchar(10), two smallint)
................................................................................
<p>
For a listing of the available dot commands, you can enter ".help"
at any time.  For example:
</p>

<tcl>Code {
sqlite> (((.help)))
.backup ?DB? FILE      Backup DB (default "main") to FILE
.bail ON|OFF           Stop after hitting an error.  Default OFF
.databases             List names and files of attached databases
.dump ?TABLE? ...      Dump the database in an SQL text format
.echo ON|OFF           Turn command echo on or off
.exit                  Exit this program
.explain ON|OFF        Turn output mode suitable for EXPLAIN on or off.
.header(s) ON|OFF      Turn display of headers on or off
.help                  Show this message
.import FILE TABLE     Import data from FILE into TABLE
.indices TABLE         Show names of all indices on TABLE
.iotrace FILE          Enable I/O diagnostic logging to FILE
.load FILE ?ENTRY?     Load an extension library
.mode MODE ?TABLE?     Set output mode where MODE is one of:
                         csv      Comma-separated values
                         column   Left-aligned columns.  (See .width)
                         html     HTML <table> code
                         insert   SQL insert statements for TABLE
                         line     One value per line
................................................................................
                         tcl      TCL list elements
.nullvalue STRING      Print STRING in place of NULL values
.output FILENAME       Send output to FILENAME
.output stdout         Send output to the screen
.prompt MAIN CONTINUE  Replace the standard prompts
.quit                  Exit this program
.read FILENAME         Execute SQL in FILENAME
.restore ?DB? FILE     Restore content of DB (default "main") from FILE
.schema ?TABLE?        Show the CREATE statements
.separator STRING      Change separator used by output mode and .import
.show                  Show the current values for various settings
.tables ?PATTERN?      List names of tables matching a LIKE pattern
.timeout MS            Try opening locked tables for MS milliseconds
.timer ON|OFF          Turn the CPU timer measurement on or off
.width NUM NUM ...     Set column widths for "column" mode
sqlite> 
}</tcl>

<h3>Changing Output Formats</h3>

<p>The sqlite3 program is able to show the results of a query

Changes to pages/tclsqlite.in.

50
51
52
53
54
55
56

57
58
59
60
61
62
63
..
69
70
71
72
73
74
75

76
77
78
79
80
81
82
...
686
687
688
689
690
691
692













































693

<p>
<ul>

<tcl>
foreach m [lsort {
 authorizer

 busy
 cache
 changes
 close
 collate
 collation_needed
 commit_hook
................................................................................
 exists
 function
 last_insert_rowid
 nullvalue
 onecolumn
 profile
 progress

 rollback_hook
 status
 timeout
 total_changes
 trace
 transaction
 update_hook
................................................................................

<p>The "trace" method registers a callback that is invoked as each SQL
statement is compiled.  The text of the SQL is appended as a single string
to the command before it is invoked.  This can be used (for example) to
keep a log of all SQL operations that an application performs.
</p>
}













































</tcl>







>







 







>







 







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

50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
..
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
...
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740

<p>
<ul>

<tcl>
foreach m [lsort {
 authorizer
 backup
 busy
 cache
 changes
 close
 collate
 collation_needed
 commit_hook
................................................................................
 exists
 function
 last_insert_rowid
 nullvalue
 onecolumn
 profile
 progress
 restore
 rollback_hook
 status
 timeout
 total_changes
 trace
 transaction
 update_hook
................................................................................

<p>The "trace" method registers a callback that is invoked as each SQL
statement is compiled.  The text of the SQL is appended as a single string
to the command before it is invoked.  This can be used (for example) to
keep a log of all SQL operations that an application performs.
</p>
}

##############################################################################
METHOD backup {

<p>The "backup" method makes a backup copy of a live database.  The
command syntax is like this:</p>

<blockquote>
<i>dbcmd</i>&nbsp;&nbsp;<b>backup</b>&nbsp;&nbsp;?<i>source-database</i>?&nbsp;&nbsp;<i>backup-filename</i>
</blockquote>

<p>The optional <i>source-database</i> argument tells which database in
the current connection should be backed up.  The default value is <b>main</b>
(or, in other words, the primary database file).  To back up TEMP tables
use <b>temp</b>.  To backup an auxilary database added to the connection
using the [ATTACH] command, use the name of that database as it was assigned
in the [ATTACH] command.</p>

<p>The <i>backup-filename</i> is the name of a file into which the backup is
written.  <i>Backup-filename</i> does not have to exist ahead of time, but if
it does, it must be a well-formed SQLite database.</p>
}

##############################################################################
METHOD restore {

<p>The "restore" method copies the content a separate database file 
into the current database connection, overwriting any preexisting content.
The command syntax is like this:</p>

<blockquote>
<i>dbcmd</i>&nbsp;&nbsp;<b>restore</b>&nbsp;&nbsp;?<i>target-database</i>?&nbsp;&nbsp;<i>source-filename</i>
</blockquote>

<p>The optional <i>target-database</i> argument tells which database in
the current connection should be overwritten with new content.  
The default value is <b>main</b> 
(or, in other words, the primary database file).  To repopulate the TEMP tables
use <b>temp</b>.  To overwrite an auxilary database added to the connection
using the [ATTACH] command, use the name of that database as it was assigned
in the [ATTACH] command.</p>

<p>The <i>source-filename</i> is the name of a existing well-formed SQLite
database file from which the content is extracted.</p>
}
</tcl>