Documentation Source Text

      <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)} {

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

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

  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

  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
                         list     Values delimited by .separator string
                         tabs     Tab-separated values
                         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

<p>
<ul>

<tcl>
foreach m [lsort {
 authorizer
 backup
 busy
 cache
 changes
 close
 collate
 collation_needed
 commit_hook
 complete
 copy
 enable_load_extension
 errorcode
 eval
 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>