Documentation Source Text

##############################################################################
Section {ATTACH DATABASE} attach *ATTACH

BubbleDiagram attach-stmt 1
</tcl>

<p> ^The ATTACH DATABASE statement adds another database 
file to the current database connection.  ^If the filename contains 
punctuation characters it must be quoted.  ^The database-names 'main' and 
'temp' refer to the main database and the database used for 
temporary tables.  ^These cannot be detached.  ^Attached databases 
are removed using the [DETACH] statement.</p>

<p> ^You cannot create a new table with the same name as a table in 
an attached database, but you can attach a database which contains
tables whose names are duplicates of tables in the main database.  ^If
the system is not running in [shared cache mode], it is also permissible 
to attach the same database file multiple times.</p>

<p> ^(Tables in an attached database can be referred to using the syntax 
<i>database-name.table-name</i>.)^  ^If an attached table doesn't have 
a duplicate table name in the main database, it does not require a 
<i>database-name</i> prefix.  ^When a database is attached, all of its 
tables which don't have duplicate names become the default table
of that name.  ^Any tables of that name attached afterwards require the
database prefix. ^If the default table of a given name is detached, then
the last table of that name attached becomes the new default.</p>

<p>
^Transactions involving multiple attached databases are atomic,
assuming that the main database is not "[:memory:]".  ^If the main
database is ":memory:" then 
transactions continue to be atomic within each individual
database file. ^But if the host computer crashes in the middle
of a [COMMIT] where two or more database files are updated,
some of those files might get the changes where others
might not.
</p>

<p> ^There is a compile-time limit of [SQLITE_MAX_ATTACHED]
attached database files.</p>

<tcl>
###############################################################################
................................................................................
^The default collating
sequence is the collating sequence defined for that column in the
[CREATE TABLE] statement.  ^Or if no collating sequence is otherwise defined,
the built-in BINARY collating sequence is used.</p>

<p>There are no arbitrary limits on the number of indices that can be
attached to a single table.  ^The number of columns in an index is 
limited to [SQLITE_MAX_COLUMN].</p>


<p>^If the UNIQUE keyword appears between CREATE and INDEX then duplicate
index entries are not allowed.  ^Any attempt to insert a duplicate entry
will result in an error.  ^For the purposes of unique indices, all NULL values
are considered to different from all other NULL values and are thus unique.
This is one of the two possible interpretations of the SQL-92 standard
(the language in the standard is ambiguious) and is the interpretation
................................................................................
BubbleDiagram drop-index-stmt 1
</tcl>

<p>^The DROP INDEX statement removes an index added
with the [CREATE INDEX] statement.  The index 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 in the default mode.
^Disk space released by the DROP INDEX command is retained for use by later
[INSERT] statements.  
^To remove free space in the database, use the [VACUUM]
command.  ^If [auto_vacuum] mode is enabled for a database then space
will be freed automatically by DROP INDEX.</p>


<tcl>
##############################################################################
Section {DROP TABLE} droptable {{DROP TABLE}}

BubbleDiagram drop-table-stmt 1
</tcl>
................................................................................
<p>^The DROP TABLE statement removes a table added with the
[CREATE TABLE] statement.  The name specified is the
table name.  ^The dropped table is completely removed from the database 
schema and the disk file.  The table can not be recovered.  
^All indices and triggers
associated with the table are also deleted.</p>

<p>^The DROP TABLE statement does not reduce the size of the database 
file in the default mode.  ^Disk space released by the DROP TABLE
command is retained for reuse by subsequent [INSERT] statements.  ^To 
remove free space in the database, 
use the [VACUUM] statement.
^If [auto_vacuum] mode is enabled for a database then space
will be freed automatically by DROP TABLE.</p>

<p>^The optional IF EXISTS clause suppresses the error that would normally
result if the table does not exist.</p>

<p>^If [foreign key constraints] are enabled, a DROP TABLE command performs an
implicit [DELETE | DELETE FROM &lt;tbl&gt;] command before removing the
table from the database schema. ^Any triggers attached to the table are
dropped from the database schema before the implicit DELETE FROM &lt;tbl&gt; 
................................................................................
<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
string.  ^Any other character matches itself or it's lower/upper case
equivalent (i.e. case-insensitive matching).  (A bug: ^SQLite only
understands upper/lower case for ASCII characters.  ^The
LIKE operator is case sensitive for unicode characters that are beyond

the ASCII range.  For example, ^the expression <b>'a'&nbsp;LIKE&nbsp;'A'</b>
is TRUE but <b>'&aelig;'&nbsp;LIKE&nbsp;'&AElig;'</b> is FALSE.)</p>"</tcl>

<p>^If the optional ESCAPE clause is present, then the expression
following the ESCAPE keyword must evaluate to a string consisting of
a single character. ^This character may be used in the LIKE pattern
to include literal percent or underscore characters. ^The escape
character followed by a percent symbol, underscore or itself matches a
................................................................................
  of the last row insert from the database connection which invoked the
  function.
  ^The last_insert_rowid() SQL function is a wrapper around the
  [sqlite3_last_insert_rowid()] C/C++ interface function.
}

funcdef {length(X)} {} {
  ^The length(X) function returns the string length of X in 
  characters if X is a string, or in bytes if X is a blob.
  ^If X is NULL then length(X) is NULL.
  ^If X is numeric then length(X) returns the length of a string
  representation of X.
}

funcdef {like(X,Y) like(X,Y,Z)} {} {
................................................................................
<tcl>
##############################################################################
Section VACUUM vacuum VACUUM

BubbleDiagram vacuum-stmt 1
</tcl>

<p>^When an object (table, index, or trigger) is dropped from the 
database, it leaves behind empty space. 
^This empty space will be reused the next time new information is
added to the database.  But in the meantime, the database file might
be larger than strictly necessary.  Also, frequent inserts, updates,
and deletes can cause the information in the database to become
fragmented - scrattered out all across the database file rather
than clustered together in one place.</p>

##############################################################################
Section {ATTACH DATABASE} attach *ATTACH

BubbleDiagram attach-stmt 1
</tcl>

<p> ^The ATTACH DATABASE statement adds another database 
file to the current [database connection]. 
^The database-names 'main' and 
'temp' refer to the main database and the database used for 
temporary tables.  ^The main and temp databases cannot be attached or
detached.</p>

<p> ^You cannot create a new table with the same name as a table in 
an attached database, but you can attach a database which contains
tables whose names are duplicates of tables in the main database.  ^If
the system is not running in [shared cache mode], it is also permissible 
to attach the same database file multiple times.</p>

<p> ^(Tables in an attached database can be referred to using the syntax 
<i>database-name.table-name</i>.)^  ^If the name of the table is unique
across all attached databases and the main and temp databases, then the
<i>database-name</i> prefix is not required.  ^When a database is attached, 
all tables which don't have duplicate names become the default table
of that name.  ^Any tables of that name attached afterwards require the
database prefix. ^If the default table of a given name is detached, then
the last table of that name attached becomes the new default.</p>

<p>
^Transactions involving multiple attached databases are atomic,
assuming that the main database is not "[:memory:]".  ^(If the main
database is ":memory:" then 
transactions continue to be atomic within each individual
database file. But if the host computer crashes in the middle
of a [COMMIT] where two or more database files are updated,
some of those files might get the changes where others
might not.)^
</p>

<p> ^There is a compile-time limit of [SQLITE_MAX_ATTACHED]
attached database files.</p>

<tcl>
###############################################################################
................................................................................
^The default collating
sequence is the collating sequence defined for that column in the
[CREATE TABLE] statement.  ^Or if no collating sequence is otherwise defined,
the built-in BINARY collating sequence is used.</p>

<p>There are no arbitrary limits on the number of indices that can be
attached to a single table.  ^The number of columns in an index is 
limited to the value set by
[sqlite3_limit]([SQLITE_LIMIT_COLUMN],...).</p>

<p>^If the UNIQUE keyword appears between CREATE and INDEX then duplicate
index entries are not allowed.  ^Any attempt to insert a duplicate entry
will result in an error.  ^For the purposes of unique indices, all NULL values
are considered to different from all other NULL values and are thus unique.
This is one of the two possible interpretations of the SQL-92 standard
(the language in the standard is ambiguious) and is the interpretation
................................................................................
BubbleDiagram drop-index-stmt 1
</tcl>

<p>^The DROP INDEX statement removes an index added
with the [CREATE INDEX] statement.  The index is completely removed from
the disk.  The only way to recover the index is to reenter the
appropriate [CREATE INDEX] command.</p>










<tcl>
##############################################################################
Section {DROP TABLE} droptable {{DROP TABLE}}

BubbleDiagram drop-table-stmt 1
</tcl>
................................................................................
<p>^The DROP TABLE statement removes a table added with the
[CREATE TABLE] statement.  The name specified is the
table name.  ^The dropped table is completely removed from the database 
schema and the disk file.  The table can not be recovered.  
^All indices and triggers
associated with the table are also deleted.</p>









<p>^The optional IF EXISTS clause suppresses the error that would normally
result if the table does not exist.</p>

<p>^If [foreign key constraints] are enabled, a DROP TABLE command performs an
implicit [DELETE | DELETE FROM &lt;tbl&gt;] command before removing the
table from the database schema. ^Any triggers attached to the table are
dropped from the database schema before the implicit DELETE FROM &lt;tbl&gt; 
................................................................................
<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
string.  ^(Any other character matches itself or it's lower/upper case
equivalent (i.e. case-insensitive matching).)^  (A bug: ^SQLite only
understands upper/lower case for ASCII characters by default.  ^The
LIKE operator is by default case sensitive for unicode characters that are
beyond the ASCII range.  For example,
^the expression <b>'a'&nbsp;LIKE&nbsp;'A'</b>
is TRUE but <b>'&aelig;'&nbsp;LIKE&nbsp;'&AElig;'</b> is FALSE.)</p>"</tcl>

<p>^If the optional ESCAPE clause is present, then the expression
following the ESCAPE keyword must evaluate to a string consisting of
a single character. ^This character may be used in the LIKE pattern
to include literal percent or underscore characters. ^The escape
character followed by a percent symbol, underscore or itself matches a
................................................................................
  of the last row insert from the database connection which invoked the
  function.
  ^The last_insert_rowid() SQL function is a wrapper around the
  [sqlite3_last_insert_rowid()] C/C++ interface function.
}

funcdef {length(X)} {} {
  ^The length(X) function returns the length of X in 
  characters if X is a string, or in bytes if X is a blob.
  ^If X is NULL then length(X) is NULL.
  ^If X is numeric then length(X) returns the length of a string
  representation of X.
}

funcdef {like(X,Y) like(X,Y,Z)} {} {
................................................................................
<tcl>
##############################################################################
Section VACUUM vacuum VACUUM

BubbleDiagram vacuum-stmt 1
</tcl>

<p>^When an object (table, index, trigger, or view) is dropped from the 
database, it leaves behind empty space. 
^This empty space will be reused the next time new information is
added to the database.  But in the meantime, the database file might
be larger than strictly necessary.  Also, frequent inserts, updates,
and deletes can cause the information in the database to become
fragmented - scrattered out all across the database file rather
than clustered together in one place.</p>