lang.tcl

sqlite数据库源码

<p>The exact text
of each CREATE INDEX statement is stored in the <b>sqlite_master</b>
or <b>sqlite_temp_master</b> table, depending on whether the table
being indexed is temporary.  Everytime the database is opened,
all CREATE INDEX statements
are read from the <b>sqlite_master</b> table and used to regenerate
SQLite's internal representation of the index layout.</p>

<p>Indexes cannot be added on tables in attached databases.
Indexes are removed with the <a href="#dropindex">DROP INDEX</a> 
command.</p>
}


Section {CREATE TABLE} {createtable}

Syntax {sql-command} {
CREATE [TEMP | TEMPORARY] TABLE <table-name> (
  <column-def> [, <column-def>]*
  [, <constraint>]*
)
} {sql-command} {
CREATE [TEMP | TEMPORARY] TABLE <table-name> AS <select-statement>
} {column-def} {
<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> ] |
UNIQUE [ <conflict-clause> ] |
CHECK ( <expr> ) [ <conflict-clause> ] |
DEFAULT <value>
} {constraint} {
PRIMARY KEY ( <column-list> ) [ <conflict-clause> ] |
UNIQUE ( <column-list> ) [ <conflict-clause> ] |
CHECK ( <expr> ) [ <conflict-clause> ]
} {conflict-clause} {
ON CONFLICT <conflict-algorithm>
}

puts {
<p>A CREATE TABLE statement is basically the keywords "CREATE TABLE"
followed by the name of a new table and a parenthesized list of column
definitions and constraints.  The table name can be either an identifier
or a string.  Tables names that begin with "<b>sqlite_</b>" are reserved
for use by the engine.</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.
SQLite is <a href="datatypes.html">typeless</a>.
The datatype for the column does not restrict what data may be put
in that column.
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 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.</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>

<p>The optional conflict-clause following each constraint
allows the specification of an alternative default
constraint conflict resolution algorithm for that constraint.
The default is abort ABORT.  Different constraints within the same
table may have different default conflict resolution algorithms.
If an COPY, INSERT, or UPDATE command specifies a different conflict
resolution algorithm, then that algorithm is used in place of the
default algorithm specified in the CREATE TABLE statement.
See the section titled
<a href="#conflict">ON CONFLICT</a> for additional information.</p>

<p>CHECK constraints are ignored in the current implementation.
Support for CHECK constraints may be added in the future.  As of
version 2.3.0, NOT NULL, PRIMARY KEY, and UNIQUE constraints all
work.</p>

<p>There are no arbitrary limits on the number
of columns or on the number of constraints in a table.
The total amount of data in a single row is limited to about
1 megabytes.  (This limit can be increased to 16MB by changing
a single #define in the source code and recompiling.)</p>

<p>The CREATE TABLE AS form defines the table to be
the result set of a query.  The names of the table columns are
the names of the columns in the result.</p>

<p>The exact text
of each CREATE TABLE statement is stored in the <b>sqlite_master</b>
table.  Everytime the database is opened, all CREATE TABLE statements
are read from the <b>sqlite_master</b> table and used to regenerate
SQLite's internal representation of the table layout.
If the original command was a CREATE TABLE AS then then an equivalent
CREATE TABLE statement is synthesized and store in <b>sqlite_master</b>
in place of the original command.
The text of CREATE TEMPORARY TABLE statements are stored in the
<b>sqlite_temp_master</b> table.
</p>

<p>Tables are removed using the <a href="#droptable">DROP TABLE</a> 
statement.  Non-temporary tables in an attached database cannot be 
dropped.</p>
}


Section {CREATE TRIGGER} createtrigger

Syntax {sql-statement} {
CREATE [TEMP | TEMPORARY] TRIGGER <trigger-name> [ BEFORE | AFTER ]
<database-event> ON [<database-name> .] <table-name>
<trigger-action>
}

Syntax {sql-statement} {
CREATE [TEMP | TEMPORARY] TRIGGER <trigger-name> INSTEAD OF
<database-event> ON [<database-name> .] <view-name>
<trigger-action>
}

Syntax {database-event} {
DELETE | 
INSERT | 
UPDATE | 
UPDATE OF <column-list>
}

Syntax {trigger-action} {
[ FOR EACH ROW | FOR EACH STATEMENT ] [ WHEN <expression> ] 
BEGIN 
  <trigger-step> ; [ <trigger-step> ; ]*
END
}

Syntax {trigger-step} {
<update-statement> | <insert-statement> | 
<delete-statement> | <select-statement> 
}

puts {
<p>The CREATE TRIGGER statement is used to add triggers to the 
database schema. Triggers are database operations (the <i>trigger-action</i>) 
that are automatically performed when a specified database event (the
<i>database-event</i>) occurs.  </p>

<p>A trigger may be specified to fire whenever a DELETE, INSERT or UPDATE of a
particular database table occurs, or whenever an UPDATE of one or more
specified columns of a table are updated.</p>

<p>At this time SQLite supports only FOR EACH ROW triggers, not FOR EACH
STATEMENT triggers. Hence explicitly specifying FOR EACH ROW is optional.  FOR
EACH ROW implies that the SQL statements specified as <i>trigger-steps</i> 
may be executed (depending on the WHEN clause) for each database row being
inserted, updated or deleted by the statement causing the trigger to fire.</p>

<p>Both the WHEN clause and the <i>trigger-steps</i> may access elements of 
the row being inserted, deleted or updated using references of the form 
"NEW.<i>column-name</i>" and "OLD.<i>column-name</i>", where
<i>column-name</i> is the name of a column from the table that the trigger
is associated with. OLD and NEW references may only be used in triggers on
<i>trigger-event</i>s for which they are relevant, as follows:</p>

<table border=0 cellpadding=10>
<tr>
<td valign="top" align="right" width=120><i>INSERT</i></td>
<td valign="top">NEW references are valid</td>
</tr>
<tr>
<td valign="top" align="right" width=120><i>UPDATE</i></td>
<td valign="top">NEW and OLD references are valid</td>
</tr>
<tr>
<td valign="top" align="right" width=120><i>DELETE</i></td>
<td valign="top">OLD references are valid</td>
</tr>
</table>
</p>

<p>If a WHEN clause is supplied, the SQL statements specified as <i>trigger-steps</i> are only executed for rows for which the WHEN clause is true. If no WHEN clause is supplied, the SQL statements are executed for all rows.</p>

<p>The specified <i>trigger-time</i> determines when the <i>trigger-steps</i>
will be executed relative to the insertion, modification or removal of the
associated row.</p>

<p>An ON CONFLICT clause may be specified as part of an UPDATE or INSERT
<i>trigger-step</i>. However if an ON CONFLICT clause is specified as part of 
the statement causing the trigger to fire, then this conflict handling
policy is used instead.</p>

<p>Triggers are automatically dropped when the table that they are 
associated with is dropped.</p>

<p>Triggers may be created on views, as well as ordinary tables, by specifying
INSTEAD OF in the CREATE TRIGGER statement. If one or more ON INSERT, ON DELETE
or ON UPDATE triggers are defined on a view, then it is not an error to execute
an INSERT, DELETE or UPDATE statement on the view, respectively. Thereafter,
executing an INSERT, DELETE or UPDATE on the view causes the associated
  triggers to fire. The real tables underlying the view are not modified
  (except possibly explicitly, by a trigger program).</p>

<p><b>Example:</b></p>

<p>Assuming that customer records are stored in the "customers" table, and
that order records are stored in the "orders" table, the following trigger
ensures that all associated orders are redirected when a customer changes
his or her address:</p>
}
Example {
CREATE TRIGGER update_customer_address UPDATE OF address ON customers 
  BEGIN
    UPDATE orders SET address = new.address WHERE customer_name = old.name;
  END;
}
puts {
<p>With this trigger installed, executing the statement:</p>
}

Example {
UPDATE customers SET address = '1 Main St.' WHERE name = 'Jack Jones';
}
puts {
<p>causes the following to be automatically executed:</p>
}
Example {
UPDATE orders SET address = '1 Main St.' WHERE customer_name = 'Jack Jones';
}

puts {
<p>Note that currently, triggers may behave oddly when created on tables
  with INTEGER PRIMARY KEY fields. If a BEFORE trigger program modifies the 
  INTEGER PRIMARY KEY field of a row that will be subsequently updated by the
  statement that causes the trigger to fire, then the update may not occur. 
  The workaround is to declare the table with a PRIMARY KEY column instead
  of an INTEGER PRIMARY KEY column.</p>
}

puts {
<p>A special SQL function RAISE() may be used within a trigger-program, with the following syntax</p> 
}
Syntax {raise-function} {
RAISE ( ABORT, <error-message> ) | 
RAISE ( FAIL, <error-message> ) | 
RAISE ( ROLLBACK, <error-message> ) | 
RAISE ( IGNORE )
}
puts {
<p>When one of the first three forms is called during trigger-program execution, the specified ON CONFLICT processing is performed (either ABORT, FAIL or 
 ROLLBACK) and the current query terminates. An error code of SQLITE_CONSTRAINT is returned to the user, along with the specified error message.</p>

<p>When RAISE(IGNORE) is called, the remainder of the current trigger program,
the statement that caused the trigger program to execute and any subsequent
    trigger programs that would of been executed are abandoned. No database
    changes are rolled back.  If the statement that caused the trigger program
    to execute is itself part of a trigger program, then that trigger program
    resumes execution at the beginning of the next step.
</p>

<p>Triggers are removed using the <a href="#droptrigger">DROP TRIGGER</a>
statement.  Non-temporary triggers cannot be added on a table in an 
attached database.</p>
}


Section {CREATE VIEW} {createview}

Syntax {sql-command} {
CREATE [TEMP | TEMPORARY] VIEW <view-name> AS <select-statement>
}

puts {
<p>The CREATE VIEW command assigns a name to a pre-packaged 
<a href="#select">SELECT</a>
statement.  Once the view is created, it can be used in the FROM clause
of another SELECT in place of a table name.
</p>

<p>You cannot COPY, DELETE, INSERT or UPDATE a view.  Views are read-only 
in SQLite.  However, in many cases you can use a <a href="#trigger">
TRIGGER</a> on the view to accomplish the same thing.  Views are removed 
with the <a href="#dropview">DROP VIEW</a> 
command.  Non-temporary views cannot be created on tables in an attached 
database.</p>
}


Section DELETE delete

Syntax {sql-statement} {
DELETE FROM [<database-name> .] <table-name> [WHERE <expr>]
}

puts {
<p>The DELETE command is used to remove records from a table.
The command consists of the "DELETE FROM" keywords followed by
the name of the table from which records are to be removed.
</p>

<p>Without a WHERE clause, all rows of the table are removed.
If a WHERE clause is supplied, then only those rows that match
the expression are removed.</p>
}


Section {DETACH DATABASE} detach

Syntax {sql-command} {
DETACH [DATABASE] <database-name>
}