SQLite: Check-in [14392258]

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

Overview

Comment:	Update documentation for the 2.2.0 release. (CVS 335)
Downloads:	Tarball \| ZIP archive \| SQL archive
Timelines:	family \| ancestors \| descendants \| both \| trunk
Files:	files \| file ages \| folders
SHA1:	14392258c5b6385091be8d684e3ea6841941b483
User & Date:	drh 2001-12-22 19:27:40

Context

2001-12-22
21:48		Bug fix in the out-of-order INSERT. (CVS 336) check-in: a26d0880 user: drh tags: trunk
19:27		Update documentation for the 2.2.0 release. (CVS 335) check-in: 14392258 user: drh tags: trunk
14:49		Bug fixing in the new integer primary key code. (CVS 334) check-in: 29cab124 user: drh tags: trunk

Changes

Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to Makefile.template.


speed.html:	$(TOP)/www/speed.tcl
	tclsh $(TOP)/www/speed.tcl >speed.html

faq.html:	$(TOP)/www/faq.tcl
	tclsh $(TOP)/www/faq.tcl >faq.html




download.html:	$(TOP)/www/download.tcl
	tclsh $(TOP)/www/download.tcl >download.html


# Files to be published on the website.
#
DOC = \
................................................................................
  vdbe.html \
  c_interface.html \
  crosscompile.html \
  mingw.html \
  tclsqlite.html \
  download.html \
  speed.html \
  faq.html


doc:	$(DOC)
	mkdir -p doc
	mv $(DOC) doc

install:	sqlite libsqlite.a sqlite.h
	mv sqlite /usr/bin


speed.html:	$(TOP)/www/speed.tcl
	tclsh $(TOP)/www/speed.tcl >speed.html

faq.html:	$(TOP)/www/faq.tcl
	tclsh $(TOP)/www/faq.tcl >faq.html

formatchng.html:	$(TOP)/www/formatchng.tcl
	tclsh $(TOP)/www/formatchng.tcl >formatchng.html

download.html:	$(TOP)/www/download.tcl
	tclsh $(TOP)/www/download.tcl >download.html


# Files to be published on the website.
#
DOC = \
................................................................................
  vdbe.html \
  c_interface.html \
  crosscompile.html \
  mingw.html \
  tclsqlite.html \
  download.html \
  speed.html \
  faq.html \
  formatchng.html

doc:	$(DOC)
	mkdir -p doc
	mv $(DOC) doc

install:	sqlite libsqlite.a sqlite.h
	mv sqlite /usr/bin

Changes to test/intpkey.test.

#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for the special processing associated
# with INTEGER PRIMARY KEY columns.
#
# $Id: intpkey.test,v 1.2 2001/12/22 14:49:26 drh Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

# Create a table with a primary key and a datatype other than
# integer
#
................................................................................
} {0 zero entry 0}
do_test intpkey=5.2 {
  execsql {
    SELECT rowid, a FROM t1
  }
} {-4 -4 0 0 5 5 6 6 11 11}










































finish_test








|







 







>
>
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for the special processing associated
# with INTEGER PRIMARY KEY columns.
#
# $Id: intpkey.test,v 1.3 2001/12/22 19:27:41 drh Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

# Create a table with a primary key and a datatype other than
# integer
#
................................................................................
} {0 zero entry 0}
do_test intpkey=5.2 {
  execsql {
    SELECT rowid, a FROM t1
  }
} {-4 -4 0 0 5 5 6 6 11 11}

# Test the ability of the COPY command to put data into a
# table that contains an integer primary key.
#
do_test intpkey-6.1 {
  set f [open ./data1.txt w]
  puts $f "20\tb-20\tc-20"
  puts $f "21\tb-21\tc-21"
  puts $f "22\tb-22\tc-22"
  close $f
  execsql {
    COPY t1 FROM 'data1.txt';
    SELECT * FROM t1 WHERE a>=20;
  }
} {20 b-20 c-20 21 b-21 c-21 22 b-22 c-22}
do_test intpkey-6.2 {
  execsql {
    SELECT * FROM t1 WHERE b=='hello'
  }
} {5 hello world 11 hello world}
do_test intpkey-6.3 {
  execsql {
    DELETE FROM t1 WHERE b='b-21';
    SELECT * FROM t1 WHERE b=='b-21';
  }
} {}
do_test intpkey-6.4 {
  execsql {
    SELECT * FROM t1 WHERE a>=20
  }
} {20 b-20 c-20 22 b-22 c-22}

# Do an insert of values with the columns specified out of order.
#
execsql {pragma vdbe_trace=on;}
do_test intpkey-7.1 {
  execsql {
    INSERT INTO t1(c,b,a) VALUES('row','new',30);
    SELECT * FROM t1 WHERE rowid>=30;
  }
} {30 new row}

finish_test

Changes to www/c_interface.tcl.

#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: c_interface.tcl,v 1.21 2001/11/24 13:36:30 drh Exp $}

puts {<html>
<head>
  <title>The C language interface to the SQLite library</title>
</head>
<body bgcolor=white>
<h1 align=center>
................................................................................
#define SQLITE_FULL        13   /* Insertion failed because database is full */
#define SQLITE_CANTOPEN    14   /* Unable to open the database file */
#define SQLITE_PROTOCOL    15   /* Database lock protocol error */
#define SQLITE_EMPTY       16   /* (Internal Only) Database table is empty */
#define SQLITE_SCHEMA      17   /* The database schema changed */
#define SQLITE_TOOBIG      18   /* Too much data for one row of a table */
#define SQLITE_CONSTRAINT  19   /* Abort due to contraint violation */

</pre></blockquote>

<p>
The meanings of these various return values are as follows:
</p>

<blockquote>
................................................................................
row of a single table.  If you attempt to store more than 1 megabyte
in a single row, this is the return code you get.
</p></dd>
<dt>SQLITE_CONSTRAINT</dt>
<dd><p>This constant is returned if the SQL statement would have violated
a database constraint.
</p></dd>






</dl>
</blockquote>

<h2>The Extended API</h2>

<p>Only the three core routines shown above are required to use
SQLite.  But there are many other functions that provide

#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: c_interface.tcl,v 1.22 2001/12/22 19:27:41 drh Exp $}

puts {<html>
<head>
  <title>The C language interface to the SQLite library</title>
</head>
<body bgcolor=white>
<h1 align=center>
................................................................................
#define SQLITE_FULL        13   /* Insertion failed because database is full */
#define SQLITE_CANTOPEN    14   /* Unable to open the database file */
#define SQLITE_PROTOCOL    15   /* Database lock protocol error */
#define SQLITE_EMPTY       16   /* (Internal Only) Database table is empty */
#define SQLITE_SCHEMA      17   /* The database schema changed */
#define SQLITE_TOOBIG      18   /* Too much data for one row of a table */
#define SQLITE_CONSTRAINT  19   /* Abort due to contraint violation */
#define SQLITE_MISMATCH    20   /* Data type mismatch */
</pre></blockquote>

<p>
The meanings of these various return values are as follows:
</p>

<blockquote>
................................................................................
row of a single table.  If you attempt to store more than 1 megabyte
in a single row, this is the return code you get.
</p></dd>
<dt>SQLITE_CONSTRAINT</dt>
<dd><p>This constant is returned if the SQL statement would have violated
a database constraint.
</p></dd>
<dt>SQLITE_MISMATCH</dt>
<dd><p>This error occurs when there is an attempt to insert non-integer
data into a column labeled INTEGER PRIMARY KEY.  For most columns, SQLite
ignores the data type and allows any kind of data to be stored.  But
an INTEGER PRIMARY KEY column is only allowed to store integer data.
</p></dd>
</dl>
</blockquote>

<h2>The Extended API</h2>

<p>Only the three core routines shown above are required to use
SQLite.  But there are many other functions that provide

Changes to www/changes.tcl.



proc chng {date desc} {
  puts "<DT><B>$date</B></DT>"
  puts "<DD><P><UL>$desc</UL></P></DD>"
}

chng {2001 Dec 16 (2.1.8)} {




<li>Added the ability to specify "*" as part of a larger column list in
    the result section of a SELECT statement.  For example:
    <nobr>"<b>SELECT rowid, * FROM table1;</b>"</nobr>.</li>
<li>Updates to comments and documentation.</li>
}

chng {2001 Dec 14 (2.1.7)} {








|
>
>
>
>



proc chng {date desc} {
  puts "<DT><B>$date</B></DT>"
  puts "<DD><P><UL>$desc</UL></P></DD>"
}

chng {2001 Dec 22 (2.2.0)} {
<li>Columns of type INTEGER PRIMARY KEY are actually used as the primary
    key in underlying B-Tree representation of the table.</li>
<li>Several obscure, unrelated bugs were found and fixed while 
    implemented the integer primary key change of the previous bullet.</li>
<li>Added the ability to specify "*" as part of a larger column list in
    the result section of a SELECT statement.  For example:
    <nobr>"<b>SELECT rowid, * FROM table1;</b>"</nobr>.</li>
<li>Updates to comments and documentation.</li>
}

chng {2001 Dec 14 (2.1.7)} {

Changes to www/faq.tcl.

#
# Run this script to generated a faq.html output file
#
set rcsid {$Id: faq.tcl,v 1.4 2001/12/15 14:22:19 drh Exp $}

puts {<html>
<head>
  <title>SQLite Frequently Asked Questions</title>
</head>
<body bgcolor="white">
<h1 align="center">Frequently Asked Questions</h1>
................................................................................
BEGIN TRANSACTION;
SELECT cnt FROM counter;
UPDATE counter SET cnt=cnt+1;
COMMIT;
</pre></blockquote>
  There are other ways of simulating the effect of AUTOINCREMENT but
  this approach seems to be the easiest and most efficient.





































}

faq {
  SQLite lets me insert a string into a database column of type integer!
} {
  <p>This is a feature, not a bug.  SQLite is typeless.  Any data can be
  inserted into any column.  You can put arbitrary length strings into
  integer columns, floating point numbers in boolean columns, or dates
  in character columns.  The datatype you assign to a column in the
  CREATE TABLE command is (mostly) ignored.  Every column is able to hold
  an arbitrary length string.</p>




  <p>Because SQLite ignores data types, you can omit the data type definition
  from columns in CREATE TABLE statements.  For example, instead of saying
<blockquote><pre>
CREATE TABLE t1(
  f1 int,
  f2 varchar(10),
................................................................................
} {
  <p>Almost.  In the source file named "<b>os.c</b>" there are two functions
  named <b>sqliteOsEnterMutex()</b> and <b>sqliteOsLeaveMutex()</b>.  In
  the default distribution these functions are stubs.  They do not do anything.
  If you change them so that they actually implement a mutex, then SQLite
  will be threadsafe.  But because these routines are stubs, the default
  SQLite distribution is not threadsafe.</p>






}

faq {
  How do I list all tables/indices contained in an SQLite database
} {
  <p>If you are running the <b>sqlite</b> command-line access program
  you can type "<b>.tables</b>" to get a list of all tables.  Or you




|







 







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










|
>
>
>







 







>
>
>
>
>
>

#
# Run this script to generated a faq.html output file
#
set rcsid {$Id: faq.tcl,v 1.5 2001/12/22 19:27:41 drh Exp $}

puts {<html>
<head>
  <title>SQLite Frequently Asked Questions</title>
</head>
<body bgcolor="white">
<h1 align="center">Frequently Asked Questions</h1>
................................................................................
BEGIN TRANSACTION;
SELECT cnt FROM counter;
UPDATE counter SET cnt=cnt+1;
COMMIT;
</pre></blockquote>
  There are other ways of simulating the effect of AUTOINCREMENT but
  this approach seems to be the easiest and most efficient.

  <p><i>New in SQLite version 2.2.0:</i>
  If one of the columns in a table has type INTEGER PRIMARY KEY and
  you do an INSERT on that table that does not specify a value for
  the primary key, then a unique random number is inserted automatically
  in that column.  This automatically generated key is random, not 
  sequential, but you can still use it as a unique identifier.</p>

  <p>Here is an example of how the INTEGER PRIMARY KEY feature can be
  used:</p>

  <blockquote><pre>
CREATE TABLE ex2(
  cnum INTEGER PRIMARY KEY,
  name TEXT,
  email TEXT
);
INSERT INTO ex2(name,email) VALUES('drh','drh@hwaci.com');
INSERT INTO ex2(name,email) VALUES('alle','alle@hwaci.com');
SELECT * FROM ex1;
</pre></blockquote>

  <p>Notice that the primary key column <b>cnum</b> is not specified on
  the INSERT statements.  The output of the SELECT on the last line will
  be something like this:</p>

  <blockquote>
     1597027670|drh|drh@hwaci.com<br>
     1597027853|alle|alle@hwaci.com
  </blockquote>

  <p>The randomly generated keys in this case are 1597027670 and
  1597027853.  You will probably get different keys every time you
  try this.  The keys will often be ascending, but this is not always
  the case and you cannot count on that behavior.  The keys will never
  be sequential.  If you need sequential keys, use the counter implemention
  described first.</p>
}

faq {
  SQLite lets me insert a string into a database column of type integer!
} {
  <p>This is a feature, not a bug.  SQLite is typeless.  Any data can be
  inserted into any column.  You can put arbitrary length strings into
  integer columns, floating point numbers in boolean columns, or dates
  in character columns.  The datatype you assign to a column in the
  CREATE TABLE command is (mostly) ignored.  Every column is able to hold
  an arbitrary length string.  (There is one exception: Columns of
  type INTEGER PRIMARY KEY may only hold an integer.  An error will result
  if you try to put anything other than an integer into an
  INTEGER PRIMARY KEY column.)</p>

  <p>Because SQLite ignores data types, you can omit the data type definition
  from columns in CREATE TABLE statements.  For example, instead of saying
<blockquote><pre>
CREATE TABLE t1(
  f1 int,
  f2 varchar(10),
................................................................................
} {
  <p>Almost.  In the source file named "<b>os.c</b>" there are two functions
  named <b>sqliteOsEnterMutex()</b> and <b>sqliteOsLeaveMutex()</b>.  In
  the default distribution these functions are stubs.  They do not do anything.
  If you change them so that they actually implement a mutex, then SQLite
  will be threadsafe.  But because these routines are stubs, the default
  SQLite distribution is not threadsafe.</p>

  <p>"Threadsafe" in the previous paragraph means that two or more threads
  can run SQLite at the same time on different "<b>sqlite</b>" structures
  returned from separate calls to <b>sqlite_open()</b>.  It is never safe
  to use the same <b>sqlite</b> structure pointer simultaneously in two
  or more threads.</p>
}

faq {
  How do I list all tables/indices contained in an SQLite database
} {
  <p>If you are running the <b>sqlite</b> command-line access program
  you can type "<b>.tables</b>" to get a list of all tables.  Or you

Added www/formatchng.tcl.

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

#
# Run this Tcl script to generate the formatchng.html file.
#
set rcsid {$Id: formatchng.tcl,v 1.1 2001/12/22 19:27:41 drh Exp $ }

puts {<html>
<head>
  <title>File Format Changes in SQLite</title>
</head>
<body bgcolor=white>
<h1 align=center>
File Format Changes in SQLite
</h1>}
puts "<p align=center>
(This page was last modified on [lrange $rcsid 3 4] UTC)
</p>"

puts {
<p>
From time to time, enhancements or bug fixes require a change to
the underlying file format for SQLite.  When this happens and you
want to upgrade your library, you must convert the contents of your
databases into a portable ASCII representation using the old version
of the library then reload the data using the new version of the
library.
</p>

<p>
You can tell if you should reload your databases by comparing the
version numbers of the old and new libraries.  If either of the
first two digits in the version number change, then a reload is
either required or recommended.  For example, upgrading from
version 1.0.32 to 2.0.0 requires a reload.  So does going from
version 2.0.8 to 2.1.0.
</p>

<p>
The following table summarizes the SQLite file format changes that have
occurred since version 1.0.0:
</p>

<blockquote>
<table border=2 cellpadding=5>
<tr>
  <th>Version Change</th>
  <th>Approx. Date</th>
  <th>Description Of File Format Change</th>
</tr>
<tr>
  <td valign="top">1.0.32 to 2.0.0</td>
  <td valign="top">2001-Sep-20</td>
  <td>Version 1.0.X of SQLite used the GDBM library as its backend
  interface to the disk.  Beginning in version 2.0.0, GDBM was replaced
  by a custom B-Tree library written especially for SQLite.  The new
  B-Tree backend is twice as fast as GDBM, supports atomic commits and
  rollback, and stores an entire database in a single disk file instead
  using a separate file for each table as GDBM does.  The two
  file formats are not even remotely similar.</td>
</tr>
<tr>
  <td valign="top">2.0.8 to 2.1.0</td>
  <td valign="top">2001-Nov-12</td>
  <td>The same basic B-Tree format is used but the details of the 
  index keys were changed in order to provide better query 
  optimization opportunities.  Some of the headers were also changed in order
  to increase the maximum size of a row from 64KB to 24MB.</td>
</tr>
<tr>
  <td valign="top">2.1.7 to 2.2.0</td>
  <td valign="top">2001-Dec-21</td>
  <td>Beginning with version 2.2.0, SQLite no longer builds an index for
  an INTEGER PRIMARY KEY column.  Instead, it uses that column as the actual
  B-Tree key for the main table.<p>Version 2.2.0 and later of the library
  will automatically detect when it is reading a 2.1.x database and will
  disable the new INTEGER PRIMARY KEY feature.   In other words, version
  2.2.x is backwards compatible to version 2.1.x.  But version 2.1.x is not
  forward compatible with version 2.2.x. If you try to open
  a 2.2.x database with an older 2.1.x library and that database contains
  an INTEGER PRIMARY KEY, you will likely get a coredump.  If the database
  schema does not contain any INTEGER PRIMARY KEYs, then the version 2.1.x
  and version 2.2.x database files will be identical and completely
  interchangeable.</p>
</tr>
</table>
</blockquote>

<p>
To perform a database reload, have ready versions of the
<b>sqlite</b> command-line utility for both the old and new
version of SQLite.  Call these two executables "<b>sqlite-old</b>"
and "<b>sqlite-new</b>".  Suppose the name of your old database
is "<b>old.db</b>" and you want to create a new database with
the same information named "<b>new.db</b>".  The command to do
this is as follows:
</p>

<blockquote>
  echo .dump | sqlite-old old.db | sqlite-new new.db
</blockquote>
}

puts {
<p><hr /></p>
<p><a href="index.html"><img src="/goback.jpg" border=0 />
Back to the SQLite Home Page</a>
</p>

</body></html>}

Changes to www/index.tcl.

#
# Run this TCL script to generate HTML for the index.html file.
#
set rcsid {$Id: index.tcl,v 1.49 2001/11/24 13:23:05 drh Exp $}

puts {<html>
<head><title>SQLite: An SQL Database Engine In A C Library</title></head>
<body bgcolor=white>
<h1 align=center>SQLite: An SQL Database Engine In A C Library</h1>
<p align=center>}
puts "This page was last modified on [lrange $rcsid 3 4] UTC<br>"
................................................................................
puts {<h2>Current Status</h2>

<p>A <a href="changes.html">change history</a> is available online.
The latest source code is
<a href="download.html">available for download</a>.
There are currently no known memory leaks or bugs
in the library.
SQLite 2.1.0 is currently being used in several mission-critical
applications.
</p>

<p>
The SQLite file format changed beginning with version 2.1.0.  The
same basic B-Tree structure from version 2.0.0 is used but the
details of indices where altered to permit better query optimization
and the B-Tree table entry headers where changed slightly to expand the
maximum amount of data on a row from 64KB to 16MB.  
The file format changes
between 2.0.8 and 2.1.0 are small but they still require that you
dump and restore your old databases.  The following command should
suffice:
</p>

<blockquote><pre>
echo .dump | sqlite2.0 old.db | sqlite2.1 new.db
</pre></blockquote>

<p>
The above command assumes that <b>sqlite2.0</b> is any of the
2.0 series of sqlite command-line tools and <b>sqlite2.1</b> is the
new version 2.1 sqlite command-line tool.
</p>

<p>
Version 1.0.X of SQLite used GDBM as its backend and so its
file format is complete incompatable with all version 2.0 and
version 2.1 SQLite releases.  Legacy databases must be dumped to
ASCII and reloaded, as shown above, before they can be used with
newer versions of SQLite.
</p>

<h2>Documentation</h2>

<p>The following documentation is currently available:</p>

<p><ul>




|







 







|
|



|
|
|
|
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<

#
# Run this TCL script to generate HTML for the index.html file.
#
set rcsid {$Id: index.tcl,v 1.50 2001/12/22 19:27:41 drh Exp $}

puts {<html>
<head><title>SQLite: An SQL Database Engine In A C Library</title></head>
<body bgcolor=white>
<h1 align=center>SQLite: An SQL Database Engine In A C Library</h1>
<p align=center>}
puts "This page was last modified on [lrange $rcsid 3 4] UTC<br>"
................................................................................
puts {<h2>Current Status</h2>

<p>A <a href="changes.html">change history</a> is available online.
The latest source code is
<a href="download.html">available for download</a>.
There are currently no known memory leaks or bugs
in the library.
SQLite 2.1.7 is currently being used in several mission-critical
applications.  SQLite 2.2.0 is in beta-test.
</p>

<p>
Whenever either of the first two digits in the version number
for SQLite change, it means that the underlying file format
has changed.  See <a href="formatchng.html">formatchng.html</a>
for additional information.























</p>

<h2>Documentation</h2>

<p>The following documentation is currently available:</p>

<p><ul>

Changes to www/lang.tcl.

#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: lang.tcl,v 1.17 2001/11/24 13:50:53 drh Exp $}

puts {<html>
<head>
  <title>Query Language Understood By SQLite</title>
</head>
<body bgcolor=white>
<h1 align=center>
................................................................................
is the name of the table that records the database schema.</p>

<p>Each column definition is the name of the column followed by the
datatype for that column, then one or more optional column constraints.
The datatype for the column is ignored.  All information
is stored as null-terminated strings.
The UNIQUE constraint causes an index to be created on the specified
columns.  This index must contain unique keys.  The PRIMARY KEY constraint
is an alias for UNIQUE.
The DEFAULT constraint
specifies a default value to use when doing an INSERT.
</p>















<p>If the "TEMP" or "TEMPORARY" keyword occurs in between "CREATE"
and "TABLE" then the table that is created is only visible to the
process that opened the database and is automatically deleted when
the database is closed.  Any indices created on a temporary table
are also temporary.  Temporary tables and indices are stored in a
separate file distinct from the main database file.</p>




|







 







|
<



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

#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: lang.tcl,v 1.18 2001/12/22 19:27:41 drh Exp $}

puts {<html>
<head>
  <title>Query Language Understood By SQLite</title>
</head>
<body bgcolor=white>
<h1 align=center>
................................................................................
is the name of the table that records the database schema.</p>

<p>Each column definition is the name of the column followed by the
datatype for that column, then one or more optional column constraints.
The datatype for the column is ignored.  All information
is stored as null-terminated strings.
The UNIQUE constraint causes an index to be created on the specified
columns.  This index must contain unique keys.

The DEFAULT constraint
specifies a default value to use when doing an INSERT.
</p>

<p>Specifying a PRIMARY KEY normally just creates a UNIQUE index
on the primary key.  However, if primary key is on a single column
that has datatype INTEGER, then that column is used internally
as the actual key of the B-Tree for the table.  This means that the column
may only hold unique integer values.  (Except for this one case,
SQLite ignores the datatype specification of columns and allows
any kind of data to be put in a column regardless of its declared
datatype.)  If a table does not have an INTEGER PRIMARY KEY column,
then the B-Tree key will be a randomly generated integer.  The
B-Tree key for a row can always be accessed using one of the
special names "<b>ROWID</b>", "<b>OID</b>", or "<b>_ROWID_</b>".
This is true regardless of whether or not there is an INTEGER
PRIMARY KEY.</p>

<p>If the "TEMP" or "TEMPORARY" keyword occurs in between "CREATE"
and "TABLE" then the table that is created is only visible to the
process that opened the database and is automatically deleted when
the database is closed.  Any indices created on a temporary table
are also temporary.  Temporary tables and indices are stored in a
separate file distinct from the main database file.</p>