SQLite

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

arch2.gif:	$(TOP)/www/arch2.gif
	cp $(TOP)/www/arch2.gif .

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

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

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

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

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

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

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

copyright-release.html:	$(TOP)/www/copyright-release.html
	cp $(TOP)/www/copyright-release.html .

copyright-release.pdf:	$(TOP)/www/copyright-release.pdf

	tclsh $(TOP)/www/version3.tcl >version3.html


# Files to be published on the website.
#
DOC = \
  arch.html \
  arch.png \
  autoinc.html \
  c_interface.html \
  capi3.html \
  capi3ref.html \
  changes.html \
  compile.html \
  copyright.html \
  copyright-release.html \
  copyright-release.pdf \
  conflict.html \
  datatypes.html \
  datatype3.html \
  docs.html \
  download.html \
  faq.html \
  fileformat.html \
  formatchng.html \
  index.html \
  lang.html \
  lockingv3.html \
  mingw.html \
  nulls.html \
  oldnews.html \
  omitted.html \
  opcode.html \
  pragma.html \
  quickstart.html \
  speed.html \
  sqlite.gif \
  sqlite.html \
  support.html \
  tclsqlite.html \
  vdbe.html \
  version3.html 

doc:	common.tcl $(DOC)
	mkdir -p doc
	mv $(DOC) doc

install:	sqlite3 libsqlite3.la sqlite3.h
	$(INSTALL) -d $(DESTDIR)$(libdir)

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

arch.png:	$(TOP)/www/arch.png
	cp $(TOP)/www/arch.png .

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

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

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

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

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

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

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

copyright-release.html:	$(TOP)/www/copyright-release.html
	cp $(TOP)/www/copyright-release.html .

copyright-release.pdf:	$(TOP)/www/copyright-release.pdf

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

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





# Files to be published on the website.
#
DOC = \
  arch.html \
  arch.png \
  autoinc.html \
  c_interface.html \
  capi3.html \
  capi3ref.html \
  changes.html \
  compile.html \
  copyright.html \
  copyright-release.html \
  copyright-release.pdf \
  conflict.html \
  datatypes.html \
  datatype3.html \
  docs.html \

  quickstart.html \
  speed.html \
  sqlite.gif \
  sqlite.html \
  support.html \
  tclsqlite.html \
  vdbe.html \
  version3.html 


doc:	common.tcl $(DOC)
	mkdir -p doc
	mv $(DOC) doc

# Standard install and cleanup targets
#

#
# Run this Tcl script to generate the autoinc.html file.
#
set rcsid {$Id: }
source common.tcl

if {[llength $argv]>0} {
  set outputdir [lindex $argv 0]
} else {
  set outputdir ""
}

header {SQLite Autoincrement}
puts {
<h1>SQLite Autoincrement</h1>

<p>
In SQLite, every row of every table has an integer ROWID.
The ROWID for each row is unique among all rows in the same table.
In SQLite version 2.8 the ROWID is a 32-bit signed integer.
Version 3.0 of SQLite expanded the ROWID to be a 64-bit signed integer.
</p>

<p>
You can access the ROWID of an SQLite table using one the special column
names ROWID, _ROWID_, or OID.
Except if you declare an ordinary table column to use one of those special
names, then the use of that name will refer to the declared column not
to the internal ROWID.
</p>

<p>
If a table contains a column of type INTEGER PRIMARY KEY, then that
column becomes an alias for the ROWID.  You can then access the ROWID
using any of four different names, the original three names described above
or the name given to the INTEGER PRIMARY KEY column.  All these names are
aliases for one another and work equally well in any context.
</p>

<p>
When a new row is inserted into an SQLite table, the ROWID can either
be specified as part of the INSERT statement or it can be assigned
automatically by the database engine.  To specify a ROWID manually,
just include it in the list of values to be inserted.  For example:
</p>

<blockquote><pre>
CREATE TABLE test1(a INT, b TEXT);
INSERT INTO test1(rowid, a, b) VALUES(123, 5, 'hello');
</pre></blockquote>

<p>
If no ROWID is specified on the insert, an appropriate ROWID is created
automatically.  The usual algorithm is to give the newly created row
a ROWID that is one larger than the largest ROWID in the table prior
to the insert.  If the table is initially empty, then a ROWID of 1 is
used.  If the largest ROWID is equal to the largest possible integer
(9223372036854775807 in SQLite version 3.0 and later) then the database
engine starts picking candidate ROWIDs at random until it finds one
that is not previously used.
</p>

<p>
The normal ROWID selection algorithm described above
will generate monotonically increasing
unique ROWIDs as long as you never use the maximum ROWID value and you never
delete the entry in the table with the largest ROWID. 
If you ever delete rows or if you ever create a row with the maximum possible
ROWID, then ROWIDs from previously deleted rows might be reused when creating
new rows and newly created ROWIDs might not be in strictly accending order.
</p>


<h2>The AUTOINCREMENT Keyword</h2>

<p>
If a column has the type INTEGER PRIMARY KEY AUTOINCREMENT then a slightly
different ROWID selection algorithm is used.  
The ROWID chosen for the new row is one larger than the largest ROWID
that has ever before existed in that same table.  If the table has never
before contained any data, then a ROWID of 1 is used.  If the table
has previously held a row with the largest possible ROWID, then new INSERTs
are not allowed and any attempt to insert a new row will fail with an
SQLITE_FULL error.
</p>

<p>
SQLite keeps track of the largest ROWID that a table has ever held using
the special SQLITE_SEQUENCE table.  The SQLITE_SEQUENCE table is created
and initialized automatically whenever a normal table that contains an
AUTOINCREMENT column is created.  The content of the SQLITE_SEQUENCE table
can be modified using ordinary UPDATE, INSERT, and DELETE statements.
But making modifications to this table will likely perturb the AUTOINCREMENT
key generation algorithm.  Make sure you know what you are doing before
you undertake such changes.
</p>

<p>
The behavior implemented by the AUTOINCREMENT keyword is subtly different
from the default behavior.  With AUTOINCREMENT, rows with automatically
selected ROWIDs are guaranteed to have ROWIDs that have never been used
before by the same table in the same database.  And the automatically generated
ROWIDs are guaranteed to be monotonically increasing.  These are important
properties in certain applications.  But if your application does not
need these properties, you should probably stay with the default behavior
since the use of AUTOINCREMENT requires additional work to be done
as each row is inserted and thus causes INSERTs to run a little slower.
}
footer $rcsid

#
# Run this Tcl script to generate the lang-*.html files.
#
set rcsid {$Id: lang.tcl,v 1.80 2004/11/21 01:02:01 drh Exp $}
source common.tcl

if {[llength $argv]>0} {
  set outputdir [lindex $argv 0]
} else {
  set outputdir ""
}

header {Query Language Understood by SQLite}
puts {
<h1>SQL As Understood By SQLite</h1>

<p>The SQLite library understands most of the standard SQL
language.  But it does <a href="omitted.html">omit some features</a>
while at the same time
adding a few features of its own.  This document attempts to
describe precisely what parts of the SQL language SQLite does
and does not support.  A list of <a href="lang_keywords.html">keywords</a> is 
also provided.</p>

<p>In all of the syntax diagrams that follow, literal text is shown in
bold blue.  Non-terminal symbols are shown in italic red.  Operators
that are part of the syntactic markup itself are shown in black roman.</p>

<p>This document is just an overview of the SQL syntax implemented
by SQLite.  Many low-level productions are omitted.  For detailed information

        footer_standard $id
        rename footer ""
        rename puts ""
        rename puts_standard puts
        rename footer_standard footer
      } 
      set ::section_file [open [file join $outputdir lang_$label.html] w]
      header "Query Language Understood by SQLite: $name"
      puts "<h1>SQL As Understood By SQLite</h1>"
      puts "<a href=\"lang.html\">\[Contents\]</a>"
      puts "<h2>$name</h2>"
      return 
    }
  }
  puts "\n<hr />"
  if {$label!=""} {
    puts "<a name=\"$label\"></a>"

<name> [<type>] [[CONSTRAINT <name>] <column-constraint>]*
} {type} {
<typename> |
<typename> ( <number> ) |
<typename> ( <number> , <number> )
} {column-constraint} {
NOT NULL [ <conflict-clause> ] |
PRIMARY KEY [<sort-order>] [ <conflict-clause> ] [AUTOINCREMENT] |
UNIQUE [ <conflict-clause> ] |
CHECK ( <expr> ) [ <conflict-clause> ] |
DEFAULT <value> |
COLLATE <collation-name>
} {constraint} {
PRIMARY KEY ( <column-list> ) [ <conflict-clause> ] |
UNIQUE ( <column-list> ) [ <conflict-clause> ] |

for the column is executed. If the value is CURRENT_TIME, CURRENT_DATE or
CURRENT_TIMESTAMP, then the current UTC date and/or time is inserted into
the columns. For CURRENT_TIME, the format is HH:MM:SS. For CURRENT_DATE, 
YYYY-MM-DD. The format for CURRENT_TIMESTAMP is "YYYY-MM-DD HH:MM:SS".
</p>

<p>Specifying a PRIMARY KEY normally just creates a UNIQUE index
on the corresponding columns.  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 automatically 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.  An INTEGER PRIMARY KEY column man also include the
keyword AUTOINCREMENT.  The AUTOINCREMENT keyword modified the way
that B-Tree keys are automatically generated.  Additional detail
on automatic B-Tree key generation is available
<a href="autoinc.html">separately</a>.</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>

}

puts {
<p>The DROP INDEX statement removes an index added
with the <a href="#createindex">
CREATE INDEX</a> statement.  The index named is completely removed from
the disk.  The only way to recover the index is to reenter the
appropriate CREATE INDEX command.</p>


<p>The DROP INDEX statement does not reduce the size of the database 
file.  Empty space in the database is retained for later INSERTs.  To 
remove free space in the database, use the <a href="#vacuum">VACUUM</a> 
command.</p>
}

command has no effect on an in-memory database.</p>

<p>As of SQLite version 3.1, an alternative to using the VACUUM command
is auto-vacuum mode, enabled using the 
<a href="pragma.html#pragma_auto_vacuum">auto_vacuum pragma</a>.</p>
}

# A list of keywords.  A asterisk occurs after the keyword if it is on
# the fallback list.
#
set keyword_list [lsort {
   ABORT*
   AFTER*
   ALL
   ALTER
   AND
   AS
   ASC*
   ATTACH*
   AUTOINCREMENT
   BEFORE*
   BEGIN*
   BETWEEN
   BY
   CASCADE*
   CASE
   CHECK
   COLLATE
   COMMIT
   CONFLICT*
   CONSTRAINT
   CREATE
   CROSS
   CURRENT_DATE*
   CURRENT_TIME*
   CURRENT_TIMESTAMP*
   DATABASE*
   DEFAULT
   DEFERRED*
   DEFERRABLE
   DELETE
   DESC*
   DETACH*
   DISTINCT
   DROP
   END*
   EACH*
   ELSE
   ESCAPE
   EXCEPT
   EXCLUSIVE*
   EXPLAIN*
   FAIL*
   FOR*
   FOREIGN
   FROM
   FULL
   GLOB*
   GROUP
   HAVING
   IGNORE*
   IMMEDIATE*
   IN
   INDEX
   INITIALLY*
   INNER
   INSERT
   INSTEAD*
   INTERSECT
   INTO
   IS
   ISNULL
   JOIN
   KEY*
   LEFT
   LIKE*
   LIMIT
   MATCH*
   NATURAL
   NOT
   NOTNULL
   NULL
   OF*
   OFFSET*
   ON
   OR
   ORDER
   OUTER
   PRAGMA*
   PRIMARY
   RAISE*
   REFERENCES
   REINDEX*
   RENAME*
   REPLACE*
   RESTRICT*
   RIGHT
   ROLLBACK
   ROW*
   SELECT
   SET
   STATEMENT*
   TABLE
   TEMP*
   TEMPORARY*
   THEN
   TO
   TRANSACTION
   TRIGGER*
   UNION
   UNIQUE
   UPDATE
   USING
   VACUUM*
   VALUES
   VIEW*
   WHEN
   WHERE
}]



Section {SQLite keywords} keywords

puts {








<p>The SQL standard specifies a huge number of keywords which may not

be used as the names of tables, indices, columns, or databases.  The











list is so long that few people can remember them all.  For most SQL

code, your safest bet is to never use any English language word as the

name of a user-defined object.</p>

<p>If you want to use a keyword as a name, you need to quote it.  There
are three ways of quoting keywords in SQLite:</p>














<p>
<blockquote>
<table>
<tr>	<td valign="top"><b>'keyword'</b></td><td width="20"></td>
	<td>A keyword in single quotes is interpreted as a literal string
        if it occurs in a context where a string literal is allowed, otherwise
	it is understood as an identifier.</td></tr>
<tr>	<td valign="top"><b>"keyword"</b></td><td></td>
	<td>A keyword in double-quotes is interpreted as an identifier if
        it matches a known identifier.  Otherwise it is interpreted as a
        string literal.</td></tr>
<tr>	<td valign="top"><b>[keyword]</b></td><td></td>
	<td>A keyword enclosed in square brackets is always understood as
        an identifier.  This is not standard SQL.  This quoting mechanism
        is used by MS Access and SQL Server and is included in SQLite for
        compatibility.</td></tr>
</table>
</blockquote>
</p>

<p>Quoted keywords are unaesthetic.
To help you avoid them, SQLite allows many keywords to be used unquoted


































as the names of databases, tables, indices, triggers, views, and/or columns.
In the list of keywords that follows, those that can be used as identifiers
are shown in an italic font.  Keywords that must be quoted in order to be
used as identifiers are shown in bold.</p>














<p>
SQLite adds new keywords from time to time when it take on new features.
So to prevent you code from being broken by future enhancements, you should
normally quote any indentifier that is an English language word, even if
you do not have to.
</p>

<p>
The following are the keywords currently recognized by SQLite:

</p>

<blockquote>
<table width="100%">
<tr>
<td align="left" valign="top" width="20%">
}



















set n [llength $keyword_list]
set nCol 5

set nRow [expr {($n+$nCol-1)/$nCol}]

















set i 0
foreach word $keyword_list {
  if {[string index $word end]=="*"} {
    set word [string range $word 0 end-1]
    set font i
  } else {
    set font b
  }
  if {$i==$nRow} {
















    puts "</td><td valign=\"top\" align=\"left\" width=\"20%\">"
    set i 1
  } else {
    incr i
  }
  puts "<$font>$word</$font><br>"
}

puts {
</td></tr></table></blockquote>

<h2>Special names</h2>

<p>The following are not keywords in SQLite, but are used as names of 
system objects.  They can be used as an identifier for a different 
type of object.</p>

<blockquote><b>

  _ROWID_<br>
  MAIN<br>
  OID<br>
  ROWID<br>
  SQLITE_MASTER<br>
  SQLITE_SEQUENCE<br>
  SQLITE_TEMP_MASTER<br>
  TEMP<br>
</b></blockquote>
}

footer $rcsid
if {[string length $outputdir]} {
  footer $rcsid
}