ADDED Doc/Extra/blank.html Index: Doc/Extra/blank.html ================================================================== --- Doc/Extra/blank.html +++ Doc/Extra/blank.html @@ -0,0 +1,75 @@ + + + + [Title Goes Here] + + + + + + + + + + + + + + + + +
+
+

[H1 Header]

+

+ [Document Text] +

+

+ [Sub Section]

+

+ [Sub Section Text] +

+
+ +
+
+ + ADDED Doc/Extra/lang_altertable.html Index: Doc/Extra/lang_altertable.html ================================================================== --- Doc/Extra/lang_altertable.html +++ Doc/Extra/lang_altertable.html @@ -0,0 +1,139 @@ + + + + ALTER TABLE + + + + + + + + + + + + + + + + +
+
+

+ SQL As Understood By SQLite

+

+ ALTER TABLE

+

+ + + + + + + + + + + + + +
+ sql-statement ::= + ALTER TABLE [database-name .] table-name alteration
+ alteration ::= + RENAME TO new-table-name
+ alteration ::= + ADD [COLUMN] column-def
+

+

+ SQLite's version of the ALTER TABLE command allows the user to rename or add a new + column to an existing table. It is not possible to remove a column from a table. +

+

+ The RENAME TO syntax is used to rename the table identified by [database-name.]table-name + to new-table-name. This command cannot be used to move a table between attached + databases, only to rename a table within the same database.

+

+ If the table being renamed has triggers or indices, then these remain attached to + the table after it has been renamed. However, if there are any view definitions, + or statements executed by triggers that refer to the table being renamed, these + are not automatically modified to use the new table name. If this is required, the + triggers or view definitions must be dropped and recreated to use the new table + name by hand. +

+

+ The ADD [COLUMN] syntax is used to add a new column to an existing table. The new + column is always appended to the end of the list of existing columns. Column-def + may take any of the forms permissable in a CREATE TABLE statement, with the following + restrictions: +

+ +

+ 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. +

+

+ After ADD COLUMN has been run on a database, that database will not be readable + by SQLite version 3.1.3 and earlier until the database is + VACUUMed.

+

+  

+
+ +
+
+ + ADDED Doc/Extra/lang_analyze.html Index: Doc/Extra/lang_analyze.html ================================================================== --- Doc/Extra/lang_analyze.html +++ Doc/Extra/lang_analyze.html @@ -0,0 +1,114 @@ + + + + ANALYZE + + + + + + + + + + + + + + + + +
+
+

+ SQL As Understood By SQLite

+

+ ANALYZE

+

+ + + + + +
+ sql-statement ::= + ANALYZE
+ + + + + +
+ sql-statement ::= + ANALYZE database-name
+ + + + + +
+ sql-statement ::= + ANALYZE [database-name .] table-name
+

+

+ The ANALYZE command gathers statistics about indices and stores them in a special + tables in the database where the query optimizer can use them to help make better + index choices. If no arguments are given, all indices in all attached databases + are analyzed. If a database name is given as the argument, all indices in that one + database are analyzed. If the argument is a table name, then only indices associated + with that one table are analyzed.

+

+ The initial implementation stores all statistics in a single table named sqlite_stat1. + Future enhancements may create additional tables with the same name pattern except + with the "1" changed to a different digit. The sqlite_stat1 table cannot be DROPped, but all the content can be + DELETEd which has the same effect.

+

+


+  

+ +
+
+ + ADDED Doc/Extra/lang_attach.html Index: Doc/Extra/lang_attach.html ================================================================== --- Doc/Extra/lang_attach.html +++ Doc/Extra/lang_attach.html @@ -0,0 +1,119 @@ + + + + ATTACH DATABASE + + + + + + + + + + + + + + + + +
+
+

+ SQL As Understood By SQLite

+

+ ATTACH DATABASE

+

+ + + + + +
+ sql-statement ::= + ATTACH [DATABASE] database-filename AS database-name
+

+

+ The ATTACH DATABASE statement adds another database file to the current database + connection. If the filename contains punctuation characters it must be quoted. The + 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 DATABASE statement.

+

+ You can read from and write to an attached database and you can modify the schema + of the attached database. This is a new feature of SQLite version 3.0. In SQLite + 2.8, schema changes to attached databases were not allowed.

+

+ 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. It is also permissible to attach the same database + file multiple times.

+

+ Tables in an attached database can be referred to using the syntax database-name.table-name. + If an attached table doesn't have a duplicate table name in the main database, it + doesn't require a database name 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 table prefix. If the default + table of a given name is detached, then the last table of that name attached becomes + the new default.

+

+ 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. Atomic commit + of attached databases is a new feature of SQLite version 3.0. In SQLite version + 2.8, all commits to attached databases behaved as if the main database were ":memory:". +

+

+ There is a compile-time limit of 10 attached database files.

+

+


+  

+ +
+
+ + ADDED Doc/Extra/lang_comment.html Index: Doc/Extra/lang_comment.html ================================================================== --- Doc/Extra/lang_comment.html +++ Doc/Extra/lang_comment.html @@ -0,0 +1,114 @@ + + + + comment + + + + + + + + + + + + + + + + +
+
+

+ SQL As Understood By SQLite

+

+ comment

+

+ + + + + + + + + + + + + +
+ comment ::= + SQL-comment | C-comment
+ SQL-comment ::= + -- single-line
+ C-comment ::= + /* multiple-lines [*/]
+

+

+ Comments aren't SQL commands, but can occur in SQL queries. They are treated as + whitespace by the parser. They can begin anywhere whitespace can be found, including + inside expressions that span multiple lines. +

+

+ SQL comments only extend to the end of the current line.

+

+ C comments can span any number of lines. If there is no terminating delimiter, they + extend to the end of the input. This is not treated as an error. A new SQL statement + can begin on a line after a multiline comment ends. C comments can be embedded anywhere + whitespace can occur, including inside expressions, and in the middle of + other SQL + statements. C comments do not nest. SQL comments inside a C comment will be ignored. +

+

+


+  

+ +
+
+ + ADDED Doc/Extra/lang_conflict.html Index: Doc/Extra/lang_conflict.html ================================================================== --- Doc/Extra/lang_conflict.html +++ Doc/Extra/lang_conflict.html @@ -0,0 +1,161 @@ + + + + ON CONFLICT + + + + + + + + + + + + + + + + +
+
+

+ SQL As Understood By SQLite

+

+ ON CONFLICT clause

+

+ + + + + + + + + +
+ conflict-clause ::= + ON CONFLICT conflict-algorithm
+ conflict-algorithm ::= + ROLLBACK | + ABORT | FAIL | IGNORE | REPLACE
+

+

+ The ON CONFLICT clause is not a separate SQL command. It is a non-standard clause + that can appear in many + other SQL commands. It is given its own section in this + document because it is not part of standard SQL and therefore might not be familiar.

+

+ The syntax for the ON CONFLICT clause is as shown above for the CREATE TABLE command. + For the INSERT and UPDATE commands, the keywords "ON CONFLICT" are replaced by "OR", + to make the syntax seem more natural. For example, instead of "INSERT ON CONFLICT + IGNORE" we have "INSERT OR IGNORE". The keywords change but the meaning of the clause + is the same either way.

+

+ The ON CONFLICT clause specifies an algorithm used to resolve constraint conflicts. + There are five choices: ROLLBACK, ABORT, FAIL, IGNORE, and REPLACE. The default + algorithm is ABORT. This is what they mean:

+
+
ROLLBACK
+
+

+ When a constraint violation occurs, an immediate ROLLBACK occurs, thus ending the + current transaction, and the command aborts with a return code of SQLITE_CONSTRAINT. + If no transaction is active (other than the implied transaction that is created + on every command) then this algorithm works the same as ABORT.

+
+
ABORT
+
+

+ When a constraint violation occurs, the command backs out any prior changes it might + have made and aborts with a return code of SQLITE_CONSTRAINT. But no ROLLBACK is + executed so changes from prior commands within the same transaction are preserved. + This is the default behavior.

+
+
FAIL
+
+

+ When a constraint violation occurs, the command aborts with a return code SQLITE_CONSTRAINT. + But any changes to the database that the command made prior to encountering the + constraint violation are preserved and are not backed out. For example, if an UPDATE + statement encountered a constraint violation on the 100th row that it attempts to + update, then the first 99 row changes are preserved but changes to rows 100 and + beyond never occur.

+
+
IGNORE
+
+

+ When a constraint violation occurs, the one row that contains the constraint violation + is not inserted or changed. But the command continues executing normally. Other + rows before and after the row that contained the constraint violation continue to + be inserted or updated normally. No error is returned.

+
+
REPLACE
+
+

+ When a UNIQUE constraint violation occurs, the pre-existing rows that are causing + the constraint violation are removed prior to inserting or updating the current + row. Thus the insert or update always occurs. The command continues executing normally. + No error is returned. If a NOT NULL constraint violation occurs, the NULL value + is replaced by the default value for that column. If the column has no default value, + then the ABORT algorithm is used. If a CHECK constraint violation occurs then the + IGNORE algorithm is used.

+

+ When this conflict resolution strategy deletes rows in order to satisfy a constraint, + it does not invoke delete triggers on those rows. This behavior might change in + a future release.

+
+
+

+ The algorithm specified in the OR clause of a INSERT or UPDATE overrides any algorithm + specified in a CREATE TABLE. If no algorithm is specified anywhere, the ABORT algorithm + is used.

+

+


+  

+ +
+
+ + ADDED Doc/Extra/lang_createindex.html Index: Doc/Extra/lang_createindex.html ================================================================== --- Doc/Extra/lang_createindex.html +++ Doc/Extra/lang_createindex.html @@ -0,0 +1,131 @@ + + + + CREATE INDEX + + + + + + + + + + + + + + + + +
+
+

+ SQL As Understood By SQLite

+

+ CREATE INDEX

+

+ + + + + + + + + +
+ sql-statement ::= + CREATE [UNIQUE] INDEX [IF NOT EXISTS] [database-name .] + index-name +
+ ON
table-name + ( column-name + [, + column-name]* )
+ column-name ::= + name [ COLLATE collation-name] [ ASC | DESC ]
+

+

+ The CREATE INDEX command consists of the keywords "CREATE INDEX" followed by the + name of the new index, the keyword "ON", the name of a previously created table + that is to be indexed, and a parenthesized list of names of columns in the table + that are used for the index key. Each column name can be followed by one of the + "ASC" or "DESC" keywords to indicate sort order, but the sort order is ignored in + the current implementation. Sorting is always done in ascending order.

+

+ The COLLATE clause following each column name defines a collating sequence used + for text entires in that column. 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.

+

+ There are no arbitrary limits on the number of indices that can be attached to a + single table, nor on the number of columns in an index.

+

+ 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.

+

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

+

+ If the optional IF NOT EXISTS clause is present and another index with the same name aleady exists, then this command becomes a no-op.

+

+ Indexes are removed with the DROP INDEX command.

+

+


+  

+ +
+
+ + ADDED Doc/Extra/lang_createtable.html Index: Doc/Extra/lang_createtable.html ================================================================== --- Doc/Extra/lang_createtable.html +++ Doc/Extra/lang_createtable.html @@ -0,0 +1,262 @@ + + + + CREATE TABLE + + + + + + + + + + + + + + + + +
+
+

+ SQL As Understood By SQLite

+

+ CREATE TABLE

+

+ + + + + + + + + + + + + + + + + + + + + + + + + + + + + +
+ sql-command ::= + CREATE [TEMP + | TEMPORARY] + TABLE [IF NOT EXISTS] + [database-name .] + table-name (
+    
column-def + [, + column-def]*
+    
[, + constraint]*
+ )
+ sql-command ::= + CREATE [TEMP + | TEMPORARY] + TABLE [database-name.] + 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 + ] [AUTOINCREMENT] |
+ UNIQUE
[ conflict-clause ] |
+ CHECK (
expr + ) |
+ DEFAULT
value + |
+ COLLATE
collation-name
+ constraint ::= + PRIMARY KEY ( + column-list ) [ conflict-clause ] |
+ UNIQUE (
column-list ) [ + conflict-clause + ] |
+ CHECK (
expr + )
+ conflict-clause ::= + ON CONFLICT conflict-algorithm
+

+

+ 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 "sqlite_" are reserved for use by the engine.

+

+ 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 + does not restrict what data may be put in that column. See + Datatypes In SQLite Version 3 for additional information. The UNIQUE constraint + causes an index to be created on the specified columns. This index must contain + unique keys. The COLLATE clause specifies what text + collating function to use when comparing text entries for the column. The + built-in BINARY collating function is used by default. +

+

+ The DEFAULT constraint specifies a default value to use when doing an INSERT. The + value may be NULL, a string constant or a number. Starting with version 3.1.0, the + default value may also be one of the special case-independant keywords CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP. If the value is NULL, a string constant or number, + it is literally inserted into the column whenever an INSERT statement that does + not specify a value 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". +

+

+ 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 "ROWID", "OID", or "_ROWID_". 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 + separately.

+

+ If the "TEMP" or "TEMPORARY" keyword occurs in between "CREATE" and "TABLE" then + the table that is created is only visible within that same database connection and + is automatically deleted when the database connection 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.

+

+ If a <database-name> is specified, then the table is created in the named + database. It is an error to specify both a <database-name> and the TEMP keyword, + unless the <database-name> is "temp". If no database name is specified, and + the TEMP keyword is not present, the table is created in the main database.

+

+ 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 ON CONFLICT for additional + information.

+

+ CHECK constraints are supported as of version 3.3.0. Prior to version 3.3.0, CHECK + constraints were parsed but not enforced.

+

+ 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 + in version 2.8. In version 3.0 there is no arbitrary limit on the amount of data + in a row.

+

+ 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.

+

+ The exact text of each CREATE TABLE statement is stored in the sqlite_master + table. Every time the database is opened, all CREATE TABLE statements are read from + the sqlite_master 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 sqlite_master + in place of the original command. The text of CREATE TEMPORARY TABLE statements + are stored in the sqlite_temp_master table. +

+

+ If the optional IF NOT EXISTS clause is present and another table with the same + name aleady exists, then this command becomes a no-op.

+

+ Tables are removed using the DROP TABLE statement. +

+

+


+  

+ +
+
+ + ADDED Doc/Extra/lang_createtrigger.html Index: Doc/Extra/lang_createtrigger.html ================================================================== --- Doc/Extra/lang_createtrigger.html +++ Doc/Extra/lang_createtrigger.html @@ -0,0 +1,295 @@ + + + + CREATE TABLE + + + + + + + + + + + + + + + + +
+
+

+ SQL As Understood By SQLite

+

+ CREATE TRIGGER

+

+ + + + + +
+ sql-statement ::= + CREATE [TEMP + | TEMPORARY] + TRIGGER trigger-name + [ BEFORE | + AFTER ]
+
database-event + ON [database-name .] + table-name
+
trigger-action
+ + + + + +
+ sql-statement ::= + CREATE [TEMP + | TEMPORARY] + TRIGGER trigger-name + INSTEAD OF
+
database-event + ON [database-name .] + view-name
+
trigger-action
+ + + + + +
+ database-event ::= + DELETE | +
+ INSERT
| +
+ UPDATE
| +
+ UPDATE OF
column-list
+ + + + + +
+ trigger-action ::= + [ FOR EACH ROW + | FOR EACH STATEMENT ] [ WHEN + expression ] +
+ BEGIN +
+    
trigger-step ; [ + trigger-step ; ]*
+ END
+ + + + + +
+ trigger-step ::= + update-statement | insert-statement + | +
+
delete-statement + | + select-statement
+

+

+ The CREATE TRIGGER statement is used to add triggers to the database schema. Triggers + are database operations (the trigger-action) that are automatically performed + when a specified database event (the database-event) occurs. +

+

+ 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.

+

+ 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 trigger-steps 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.

+

+ Both the WHEN clause and the trigger-steps may access elements of the row + being inserted, deleted or updated using references of the form "NEW.column-name" + and "OLD.column-name", where column-name 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 trigger-events for which they are relevant, as follows:

+

+ + + + + + + + + + + + + +
+ INSERT + NEW references are valid
+ UPDATE + NEW and OLD references are valid
+ DELETE + OLD references are valid
+

+

+

+

+ If a WHEN clause is supplied, the SQL statements specified as trigger-steps + 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.

+

+ The specified trigger-time determines when the trigger-steps will + be executed relative to the insertion, modification or removal of the associated + row.

+

+ An ON CONFLICT clause may be specified as part of an UPDATE or INSERT trigger-step. + 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.

+

+ Triggers are automatically dropped when the table that they are associated with + is dropped.

+

+ 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).

+

+ Example:

+

+ 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:

+
+
+CREATE TRIGGER update_customer_address UPDATE OF address ON customers 
+  BEGIN
+    UPDATE orders SET address = new.address WHERE customer_name = old.name;
+  END;
+
+
+

+ With this trigger installed, executing the statement:

+
+
+UPDATE customers SET address = '1 Main St.' WHERE name = 'Jack Jones';
+
+
+

+ causes the following to be automatically executed:

+
+
+UPDATE orders SET address = '1 Main St.' WHERE customer_name = 'Jack Jones';
+
+
+

+ 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.

+

+ A special SQL function RAISE() may be used within a trigger-program, with the following + syntax

+

+ + + + + +
+ raise-function ::= + RAISE ( ABORT, error-message ) + | +
+ RAISE ( FAIL,
error-message ) | +
+ RAISE ( ROLLBACK,
error-message ) | +
+ RAISE ( IGNORE )
+

+

+ 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.

+

+ 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. +

+

+ Triggers are removed using the DROP TRIGGER + statement.

+

+


+  

+ +
+
+ + ADDED Doc/Extra/lang_createview.html Index: Doc/Extra/lang_createview.html ================================================================== --- Doc/Extra/lang_createview.html +++ Doc/Extra/lang_createview.html @@ -0,0 +1,106 @@ + + + + CREATE VIEW + + + + + + + + + + + + + + + + +
+
+

+ SQL As Understood By SQLite

+

+ CREATE VIEW

+

+ + + + + +
+ sql-command ::= + CREATE [TEMP + | TEMPORARY] + VIEW [database-name.] + view-name AS + select-statement
+

+

+ The CREATE VIEW command assigns a name to a pre-packaged + SELECT statement. Once the view is created, it can be used in the FROM clause + of another SELECT in place of a table name. +

+

+ If the "TEMP" or "TEMPORARY" keyword occurs in between "CREATE" and "VIEW" then + the view that is created is only visible to the process that opened the database + and is automatically deleted when the database is closed.

+

+ If a <database-name> is specified, then the view is created in the named database. + It is an error to specify both a <database-name> and the TEMP keyword, unless + the <database-name> is "temp". If no database name is specified, and the TEMP + keyword is not present, the table is created in the main database.

+

+ You cannot COPY, DELETE, INSERT or UPDATE a view. Views are read-only in SQLite. + However, in many cases you can use a TRIGGER + on the view to accomplish the same thing. Views are removed with the + DROP VIEW command.

+

+


+  

+ +
+
+ + ADDED Doc/Extra/lang_createvtab.html Index: Doc/Extra/lang_createvtab.html ================================================================== --- Doc/Extra/lang_createvtab.html +++ Doc/Extra/lang_createvtab.html @@ -0,0 +1,111 @@ + + + + CREATE VIRTUAL TABLE + + + + + + + + + + + + + + + + +
+
+

+ SQL As Understood By SQLite

+

+ CREATE VIRTUAL TABLE

+

+ + + + + +
+ sql-command ::= + CREATE VIRTUAL TABLE [database-name .] table-name USING module-name [( + arguments )]
+

+

+ A virtual table is an interface to an external storage or computation engine that + appears to be a table but does not actually store information in the database file.

+

+ In general, you can do anything with a virtual table that can be done with an ordinary + table, except that you cannot create triggers on a virtual table. Some virtual table + implementations might impose additional restrictions. For example, many virtual + tables are read-only.

+

+ The <module-name> is the + name of an object that implements the virtual table. + The <module-name> must be registered with the SQLite database connection using + sqlite3_create_module prior to + issuing the CREATE VIRTUAL TABLE statement. The module takes zero or more comma-separated + arguments. The arguments can be just about any text as long as it has balanced parentheses. + The argument syntax is sufficiently general that the arguments can be made to appear + as column definitions in a traditional CREATE TABLE + statement. SQLite passes the module arguments directly to the module without any + interpretation. It is the responsibility of the module implementation to parse and + interpret its own arguments.

+

+ A virtual table is destroyed using the ordinary DROP + TABLE statement. There is no DROP VIRTUAL TABLE statement.

+

+


+  

+ +
+
+ + ADDED Doc/Extra/lang_delete.html Index: Doc/Extra/lang_delete.html ================================================================== --- Doc/Extra/lang_delete.html +++ Doc/Extra/lang_delete.html @@ -0,0 +1,95 @@ + + + + DELETE + + + + + + + + + + + + + + + + +
+
+

+ SQL As Understood By SQLite

+

+ DELETE

+

+ + + + + +
+ sql-statement ::= + DELETE FROM [database-name .] table-name [WHERE + expr]
+

+

+ 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. +

+

+ 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.

+

+


+  

+ +
+
+ + ADDED Doc/Extra/lang_detach.html Index: Doc/Extra/lang_detach.html ================================================================== --- Doc/Extra/lang_detach.html +++ Doc/Extra/lang_detach.html @@ -0,0 +1,90 @@ + + + + DETACH + + + + + + + + + + + + + + + + +
+
+

+ SQL As Understood By SQLite

+

+ DETACH

+

+ + + + + +
+ sql-command ::= + DETACH [DATABASE] database-name
+

+

+ This statement detaches an additional database connection previously attached using + the ATTACH DATABASE statement. It is possible to have the same database file attached multiple times using different names, and detaching + one connection to a file will leave the others intact.

+

+ This statement will fail if SQLite is in the middle of a transaction.

+

+


+  

+ +
+
+ + ADDED Doc/Extra/lang_dropindex.html Index: Doc/Extra/lang_dropindex.html ================================================================== --- Doc/Extra/lang_dropindex.html +++ Doc/Extra/lang_dropindex.html @@ -0,0 +1,95 @@ + + + + DROP INDEX + + + + + + + + + + + + + + + + +
+
+

+ SQL As Understood By SQLite

+

+ DROP INDEX

+

+ + + + + +
+ sql-command ::= + DROP INDEX [IF EXISTS] [database-name .] + index-name
+

+

+ The DROP INDEX statement removes an index added with the + CREATE INDEX 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.

+

+ The DROP INDEX statement does not reduce the size of the database file in the default + mode. Empty space in the database is retained for later INSERTs. To remove free + space in the database, use the + VACUUM command. If + AUTOVACUUM mode is enabled for a database then space will be freed automatically by DROP INDEX.

+

+


+  

+ +
+
+ + ADDED Doc/Extra/lang_droptable.html Index: Doc/Extra/lang_droptable.html ================================================================== --- Doc/Extra/lang_droptable.html +++ Doc/Extra/lang_droptable.html @@ -0,0 +1,98 @@ + + + + DROP TABLE + + + + + + + + + + + + + + + + +
+
+

+ SQL As Understood By SQLite

+

+ DROP TABLE

+

+ + + + + +
+ sql-command ::= + DROP TABLE [IF EXISTS] [database-name.] + table-name
+

+

+ The DROP TABLE statement removes a table added with the CREATE TABLE statement. The + name specified is the table name. It is completely removed + from the database schema and the disk file. The table can not be recovered. All + indices associated with the table are also deleted.

+

+ The DROP TABLE statement does not reduce the size of the database file in the default + mode. Empty space in the database is retained for later INSERTs. To remove free + space in the database, use the + VACUUM command. If + AUTOVACUUM mode is enabled for a database then space will be freed automatically by DROP TABLE.

+

+ The optional IF EXISTS clause suppresses the error that would normally result if the table does not exist.

+

+


+  

+ +
+
+ + ADDED Doc/Extra/lang_droptrigger.html Index: Doc/Extra/lang_droptrigger.html ================================================================== --- Doc/Extra/lang_droptrigger.html +++ Doc/Extra/lang_droptrigger.html @@ -0,0 +1,89 @@ + + + + DROP TRIGGER + + + + + + + + + + + + + + + + +
+
+

+ SQL As Understood By SQLite

+

+ DROP TRIGGER

+

+ + + + + +
+ sql-statement ::= + DROP TRIGGER [database-name .] trigger-name
+

+

+ The DROP TRIGGER statement removes a trigger created by the + CREATE TRIGGER statement. The trigger is deleted from the database schema. + Note that triggers are automatically dropped when the associated table is dropped.

+

+


+  

+ +
+
+ + ADDED Doc/Extra/lang_dropview.html Index: Doc/Extra/lang_dropview.html ================================================================== --- Doc/Extra/lang_dropview.html +++ Doc/Extra/lang_dropview.html @@ -0,0 +1,88 @@ + + + + DROP VIEW + + + + + + + + + + + + + + + + +
+
+

+ SQL As Understood By SQLite

+

+ DROP VIEW

+

+ + + + + +
+ sql-command ::= + DROP VIEW view-name
+

+

+ The DROP VIEW statement removes a view created by the CREATE VIEW statement. The + name specified is the view name. It is removed from the + database schema, but no actual data + in the underlying base tables is modified.

+

+


+  

+ +
+
+ + ADDED Doc/Extra/lang_explain.html Index: Doc/Extra/lang_explain.html ================================================================== --- Doc/Extra/lang_explain.html +++ Doc/Extra/lang_explain.html @@ -0,0 +1,94 @@ + + + + EXPLAIN + + + + + + + + + + + + + + + + +
+
+

+ SQL As Understood By SQLite

+

+ EXPLAIN

+

+ + + + + +
+ sql-statement ::= + EXPLAIN sql-statement
+

+

+ The EXPLAIN command modifier is a non-standard extension. The idea comes from a similar command found in PostgreSQL, but the operation is completely different.

+

+ If the EXPLAIN keyword appears before any + other SQLite SQL command then instead + of actually executing the command, the SQLite library will report back the sequence + of virtual machine instructions it would have used to execute the command had the + EXPLAIN keyword not been present. For additional information about virtual machine + instructions see the architecture description + or the documentation on available opcodes + for the virtual machine.

+

+


+  

+ +
+
+ + ADDED Doc/Extra/lang_expr.html Index: Doc/Extra/lang_expr.html ================================================================== --- Doc/Extra/lang_expr.html +++ Doc/Extra/lang_expr.html @@ -0,0 +1,628 @@ + + + + expression + + + + + + + + + + + + + + + + +
+
+

+ SQL As Understood By SQLite

+

+ expression

+

+ + + + + + + + + +
+ expr ::= + expr binary-op expr + |
+
expr + [NOT] + like-op + expr [ESCAPE expr] |
+
unary-op + expr + |
+ (
expr + ) |
+
column-name + |
+
table-name + . column-name + |
+
database-name + . table-name + . column-name + |
+
literal-value + |
+
parameter + |
+
function-name + ( expr-list + | * ) + |
+
expr ISNULL + |
+
expr NOTNULL + |
+
expr + [NOT] BETWEEN + expr AND + expr + |
+
expr + [NOT] IN ( + value-list + ) |
+
expr + [NOT] IN ( + select-statement + ) |
+
expr + [NOT] IN + [database-name .] + table-name + |
+
[EXISTS] + ( select-statement + ) |
+ CASE
[expr] ( + WHEN expr + THEN expr + )+ [ELSE + expr] END |
+ CAST (
expr + AS type + )
+ like-op ::= + LIKE | GLOB + | REGEXP | MATCH
+

+

+ This section is different from the others. Most other sections of this document + talks about a particular SQL command. This section does not talk about a standalone + command but about "expressions" which are subcomponents of most other commands.

+

+ SQLite understands the following binary operators, in order from highest to lowest + precedence:

+
+
+|| * / % + - << >> & | < <= >
+  >= = == != <> IN AND OR
+
+
+

+ Supported unary operators are these:

+
+
+- + ! ~ NOT
+
+
+

+ The unary operator [Operator +] is a no-op. It can be applied to strings, numbers, + or blobs and it always gives as its result the value of the operand.

+

+ Note that there are two variations of the equals and not equals operators. Equals + can be either = or + ==. The non-equals operator can be either + != or <>. The + || operator is "concatenate" - it joins together the two + strings of its operands. The operator % + outputs the remainder of its left operand modulo its right operand.

+

+ The result of any binary operator is a numeric value, except for the + || concatenation operator which gives a string result.

+ +

+ A literal value is an integer number or a floating point number. Scientific notation + is supported. The "." character is always used as the decimal point even if the + locale setting specifies "," for this role - the use of "," for the decimal point + would result in syntactic ambiguity. A string constant is formed by enclosing the + string in single quotes ('). A single quote within the string can be encoded by + putting two single quotes in a row - as in Pascal. C-style escapes using the backslash + character are not supported because they are not standard SQL. BLOB literals are + string literals containing hexadecimal data and preceded by a single "x" or "X" + character. For example:

+
+
+X'53514697465'
+
+
+

+ A literal value can also be the token "NULL". +

+

+ A parameter specifies a placeholder in the expression for a literal value that is + filled in at runtime using the sqlite3_bind + API. Parameters can take several forms: +

+

+ + + + + + + + + + + + + + + + + + + + + + + + + + +
+ ?NNN + + A question mark followed by a number NNN holds a spot for the NNN-th parameter. + NNN must be between 1 and 999.
+ ? + + A question mark that is not followed by a number holds a spot for the next unused + parameter.
+ :AAAA + + A colon followed by an identifier name holds a spot for a named parameter with the + name AAAA. Named parameters are also numbered. The number assigned is the next unused + number. To avoid confusion, it is best to avoid mixing named and numbered parameters.
+ @AAAA + + An "at" sign works exactly like a colon.
+ $AAAA + + A dollar-sign followed by an identifier name also holds a spot for a named parameter + with the name AAAA. The identifier name in this case can include one or more occurances + of "::" and a suffix enclosed in "(...)" containing any text at all. This syntax + is the form of a variable name in the Tcl programming language.
+

+
+
+

+ Parameters that are not assigned values using + sqlite3_bind are treated as NULL.

+ +

+ The LIKE operator does a pattern matching comparison. The operand to the right contains + the pattern, the left hand operand contains the string to match against the pattern. + A percent symbol % in the pattern matches + any sequence of zero or more characters in the string. An underscore + _ in the 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 7-bit Latin characters. + Hence the LIKE operator is case sensitive for 8-bit iso8859 characters or UTF-8 + characters. For example, the expression 'a' LIKE 'A' is TRUE but '' LIKE + '' is FALSE.).

+

+ 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 literal percent symbol, underscore or escape character in the string, respectively. + The infix LIKE operator is implemented by calling the user function + like(X,Y).

+

+ The LIKE operator is not case sensitive and will match upper case characters on + one side against lower case characters on the other. (A bug: SQLite only understands + upper/lower case for 7-bit Latin characters. Hence the LIKE operator is case sensitive + for 8-bit iso8859 characters or UTF-8 characters. For example, the expression 'a' + LIKE 'A' is TRUE but '' LIKE '' is FALSE.). +

+

+

+

+ The infix LIKE operator is implemented by calling the user function + like(X,Y). If an ESCAPE clause is present, it adds a third parameter + to the function call. If the functionality of LIKE can be overridden by defining + an alternative implementation of the like() SQL function.

+

+

+ +

+ The GLOB operator is similar to LIKE but uses the Unix file globbing syntax for + its wildcards. Also, GLOB is case sensitive, unlike LIKE. Both GLOB and LIKE may + be preceded by the NOT keyword to invert the sense of the test. The infix GLOB operator + is implemented by calling the user function glob(X,Y) + and can be modified by overriding that function.

+ +

+ The REGEXP operator is a special syntax for the regexp() user function. No regexp() + user function is defined by default and so use of the REGEXP operator will normally + result in an error message. If a user-defined function named "regexp" is added at + run-time, that function will be called in order to implement the REGEXP operator.

+ +

+ The MATCH operator is a special syntax for the match() user function. The default + match() function implementation raises and exception and is not really useful for + anything. But extensions can override the match() function with more helpful logic.

+

+ A column name can be any of the names defined in the CREATE TABLE statement or one + of the following special identifiers: "ROWID", "OID", or "_ROWID_". + These special identifiers all describe the unique random integer key (the "row key") + associated with every row of every table. The special identifiers only refer to + the row key if the CREATE TABLE statement does not define a real column with the + same name. Row keys act like read-only columns. A row key can be used anywhere a + regular column can be used, except that you cannot change the value of a row key + in an UPDATE or INSERT statement. "SELECT * ..." does not return the row key.

+

+ SELECT statements can appear in expressions as either the right-hand operand of + the IN operator, as a scalar quantity, or as the operand of an EXISTS operator. + As a scalar quantity or the operand of an IN operator, the SELECT should have only + a single column in its result. Compound SELECTs (connected with keywords like UNION + or EXCEPT) are allowed. With the EXISTS operator, the columns in the result set + of the SELECT are ignored and the expression returns TRUE if one or more rows exist + and FALSE if the result set is empty. If no terms in the SELECT expression refer + to value in the containing query, then the expression is evaluated once prior to + any other processing and the result is reused as necessary. If the SELECT expression + does contain variables from the outer query, then the SELECT is reevaluated every + time it is needed.

+

+ When a SELECT is the right operand of the IN operator, the IN operator returns TRUE + if the result of the left operand is any of the values generated by the select. + The IN operator may be preceded by the NOT keyword to invert the sense of the test.

+

+ When a SELECT appears within an expression but is not the right operand of an IN + operator, then the first row of the result of the SELECT becomes the value used + in the expression. If the SELECT yields more than one result row, all rows after the first are ignored. If the SELECT yields no rows, then the value of the SELECT + is NULL.

+

+ A CAST expression changes the datatype of the + + + into the type specified by <type>. <type> can be any non-empty type + name that is valid for the type in a column definition of a CREATE TABLE statement.

+

+ Both simple and aggregate functions are supported. A simple function can be used + in any expression. Simple functions return a result immediately based on their inputs. + Aggregate functions may only be used in a SELECT statement. Aggregate functions + compute their result across all rows of the result set.

+

+ Core Functions +

+

+ The core functions shown below are available by default. Additional functions may + be written in C and added to the database engine using the + sqlite3_create_function() API.

+

+ + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + +
+ abs(X) + Return the absolute value of argument X.
+ coalesce(X,Y,...) + Return a copy of the first non-NULL argument. If all arguments are NULL then NULL + is returned. There must be at least 2 arguments.
+ glob(X,Y) + This function is used to implement the "X GLOB Y" syntax of SQLite. The sqlite3_create_function() interface + can be used to override this function and thereby change the operation of the GLOB operator.
+ ifnull(X,Y) + Return a copy of the first non-NULL argument. If both arguments are NULL then NULL + is returned. This behaves the same as coalesce() above.
+ last_insert_rowid() + Return the ROWID of the last row insert from this connection to the database. This + is the same value that would be returned from the sqlite_last_insert_rowid() + API function.
+ length(X) + Return the string length of X in characters. If SQLite is configured to support + UTF-8, then the number of UTF-8 characters is returned, not the number of bytes.
+ like(X,Y [,Z]) + This function is used to implement the "X LIKE Y [ESCAPE Z]" syntax of SQL. + If the optional ESCAPE clause is present, then the user-function is invoked with + three arguments. Otherwise, it is invoked with two arguments only. The + sqlite_create_function() interface can be used to override this function and + thereby change the operation of the LIKE operator. When doing + this, it may be important to override both the two and three argument versions of + the like() function. Otherwise, different code may be called to implement the LIKE + operator depending on whether or not an ESCAPE clause was specified.
+ load_extension(X)
+ load_extension(X,Y)
+ Load SQLite extensions out of the shared library file named X using the entry + point Y. The result is a NULL. If Y is omitted then the default entry + point of sqlite3_extension_init is used. This function raises an exception + if the extension fails to load or initialize correctly. +
+ lower(X) + Return a copy of string X will all characters converted to lower case. The + C library tolower() routine is used for the conversion, which means that + this function might not work correctly on UTF-8 characters.
+ max(X,Y,...) + Return the argument with the maximum value. Arguments may be strings in addition + to numbers. The maximum value is determined by the usual sort order. Note that + max() is a simple function when it has 2 or more arguments but converts to + an aggregate function if given only a single argument.
+ min(X,Y,...) + Return the argument with the minimum value. Arguments may be strings in addition + to numbers. The minimum value is determined by the usual sort order. Note that + min() is a simple function when it has 2 or more arguments but converts to + an aggregate function if given only a single argument.
+ nullif(X,Y) + Return the first argument if the arguments are different, otherwise return NULL.
+ quote(X) + This routine returns a string which is the value of its argument suitable for inclusion + into another SQL statement. Strings are surrounded by single-quotes with escapes + on interior quotes as needed. BLOBs are encoded as hexadecimal literals. The current + implementation of + VACUUM uses this function. The function is also useful when writing + triggers to implement undo/redo functionality. +
+ random(*) + Return a pseudo-random integer between -9223372036854775808 and +9223372036854775807.
+ round(X)
+ round(X,Y)
+ Round off the number X to Y digits to the right of the decimal point. + If the Y argument is omitted, 0 is assumed.
+ soundex(X) + Compute the soundex encoding of the string X. The string "?000" is returned + if the argument is NULL. This function is omitted from SQLite by default. It is + only available the -DSQLITE_SOUNDEX=1 compiler option is used when SQLite is built.
+ sqlite_version(*) + Return the version string for the SQLite library that is running. Example: "2.8.0"
+ substr(X,Y,Z) + Return a substring of input string X that begins with the Y-th character + and which is Z characters long. The left-most character of X is number + 1. If Y is negative the the first character of the substring is found by + counting from the right rather than the left. If SQLite is configured to support + UTF-8, then characters indices refer to actual UTF-8 characters, not bytes.
+ typeof(X) + Return the type of the expression X. The only return values are "null", "integer", + "real", "text", and "blob". + SQLite's type handling is explained in + Datatypes in SQLite Version 3.
+ upper(X) + Return a copy of input string X converted to all upper-case letters. The + implementation of this function uses the C library routine toupper() which + means it may not work correctly on UTF-8 strings.
+ Date And Time Functions +

+

+ Date and time functions are documented in the + SQLite Wiki.

+

+ Aggregate Functions +

+

+ The aggregate functions shown below are available by default. Additional aggregate + functions written in C may be added using the + sqlite3_create_function() API.

+

+ In any aggregate function that takes a single argument, that argument can be preceeded + by the keyword DISTINCT. In such cases, duplicate elements are filtered before being + passed into the aggregate function. For example, the function "count(distinct X)" + will return the number of distinct values of column X instead of the total number + of non-null values in column X. +

+

+ + + + + + + + + + + + + + + + + + + + + +
+ avg(X) + Return the average value of all non-NULL X 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 even if all inputs are integers. +

+

+
+ count(X)
+ count(*)
+ The first form return a count of the number of times that X is not NULL in + a group. The second form (with no argument) returns the total number of rows in + the group.
+ max(X) + Return the maximum value of all values in the group. The usual sort order is used + to determine the maximum.
+ min(X) + Return the minimum non-NULL value of all values in the group. The usual sort order + is used to determine the minimum. NULL is only returned if all values in the group + are NULL.
+ sum(X)
+ total(X)
+ 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 but the SQL standard requires it and most + other SQL database engines implement sum() that way so SQLite does it in the same + way in order to be compatible. The non-standard total() function is provided as + a convenient way to work around this design problem in the SQL language. +

+

+

+ The result of total() is always a floating point value. The result of sum() is an + integer value if all non-NULL inputs are integers. 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.

+

+ 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 an exception.

+
+

+

+


+  

+ +
+
+ + ADDED Doc/Extra/lang_insert.html Index: Doc/Extra/lang_insert.html ================================================================== --- Doc/Extra/lang_insert.html +++ Doc/Extra/lang_insert.html @@ -0,0 +1,120 @@ + + + + INSERT + + + + + + + + + + + + + + + + +
+
+

+ SQL As Understood By SQLite

+

+ INSERT

+

+ + + + + +
+ sql-statement ::= + INSERT [OR + conflict-algorithm] INTO [database-name .] + table-name + [(column-list)] VALUES(value-list) + |
+ INSERT
[OR + conflict-algorithm] + INTO [database-name .] + table-name + [(column-list)] select-statement
+

+

+ The INSERT statement comes in two basic forms. The first form (with the "VALUES" + keyword) creates a single new row in an existing table. If no column-list is specified + then the number of values must be the same as the number of columns in the table. + If a column-list is specified, then the number of values must match the number of + specified columns. Columns of the table that do not appear in the column list are + filled with the default value, or with NULL if not default value is specified. +

+

+ The second form of the INSERT statement takes it data + from a SELECT statement. The + number of columns in the result of the SELECT must exactly match the number of columns + in the table if no column list is specified, or it must match the number of columns + name in the column list. A new entry is made in the table for every row of the SELECT + result. The SELECT may be simple or compound. If the SELECT statement has an ORDER + BY clause, the ORDER BY is ignored.

+

+ The optional conflict-clause allows the specification of an alternative constraint conflict resolution algorithm to use during this one command. See the section titled + ON CONFLICT for additional information. For compatibility + with MySQL, the parser allows the use of the single keyword + REPLACE as an alias for "INSERT OR REPLACE". +

+

+


+  

+ +
+
+ + ADDED Doc/Extra/lang_reindex.html Index: Doc/Extra/lang_reindex.html ================================================================== --- Doc/Extra/lang_reindex.html +++ Doc/Extra/lang_reindex.html @@ -0,0 +1,111 @@ + + + + REINDEX + + + + + + + + + + + + + + + + +
+
+

+ SQL As Understood By SQLite

+

+ REINDEX

+

+ + + + + +
+ sql-statement ::= + REINDEX collation + name
+ + + + + +
+ sql-statement ::= + REINDEX [database-name .] table/index-name
+

+

+ The REINDEX command is used to delete and recreate indices from scratch. This is + useful when the definition of a collation sequence has changed. +

+

+ In the first form, all indices in all attached databases that use the named collation + sequence are recreated. In the second form, if [database-name.]table/index-name + identifies a table, then all indices associated with the table are rebuilt. If an + index is identified, then only this specific index is deleted and recreated. +

+

+ If no database-name is specified and there exists both a table or index and + a collation sequence of the specified name, then indices associated with the collation + sequence only are reconstructed. This ambiguity may be dispelled by always specifying + a database-name when reindexing a specific table or index. +

+

+


+  

+ +
+
+ + ADDED Doc/Extra/lang_replace.html Index: Doc/Extra/lang_replace.html ================================================================== --- Doc/Extra/lang_replace.html +++ Doc/Extra/lang_replace.html @@ -0,0 +1,98 @@ + + + + REPLACE + + + + + + + + + + + + + + + + +
+
+

+ SQL As Understood By SQLite

+

+ REPLACE

+

+ + + + + +
+ sql-statement ::= + REPLACE INTO [database-name .] table-name [( + column-list )] VALUES ( value-list ) |
+ REPLACE INTO
[database-name .] + table-name + [( column-list )] + select-statement
+

+

+ The REPLACE command is an alias for the "INSERT OR REPLACE" variant of the + INSERT command. This alias is provided for compatibility with MySQL. See the + INSERT command documentation for additional information.

+

+


+  

+ +
+
+ + ADDED Doc/Extra/lang_select.html Index: Doc/Extra/lang_select.html ================================================================== --- Doc/Extra/lang_select.html +++ Doc/Extra/lang_select.html @@ -0,0 +1,256 @@ + + + + SELECT + + + + + + + + + + + + + + + + +
+
+

+ SQL As Understood By SQLite

+

+ SELECT

+

+ + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + +
+ sql-statement ::= + SELECT [ALL + | DISTINCT] + result + [FROM table-list]
+
[WHERE expr]
+
[GROUP BY + expr-list]
+
[HAVING expr]
+
[compound-op select]*
+
[ORDER BY + sort-expr-list]
+
[LIMIT integer [( + OFFSET | , + ) integer]]
+ result ::= + result-column [, + result-column]*
+ result-column ::= + * | + table-name + . * | + expr + [ [AS] string + ]
+ table-list ::= + table [join-op table + join-args]*
+ table ::= + table-name [AS + alias] + |
+ (
select + ) [AS + alias]
+ join-op ::= + , | + [NATURAL] + [LEFT | + RIGHT | FULL] + [OUTER | + INNER | CROSS] JOIN
+ join-args ::= + [ON expr] + [USING ( + id-list )]
+ sort-expr-list ::= + expr [sort-order] [, + expr + [sort-order]]*
+ sort-order ::= + [ COLLATE + collation-name + ] [ ASC + | DESC ]
+ compound_op ::= + UNION | UNION + ALL | INTERSECT | EXCEPT
+

+

+ The SELECT statement is used to query the database. The result of a SELECT is zero + or more rows of data where each row has a fixed number of columns. The number of + columns in the result is specified by the expression list in between the SELECT + and FROM keywords. Any arbitrary expression can be used as a result. If a result + expression is * then all columns of all + tables are substituted for that one expression. If the expression is the name of + a table followed by .* then the result is + all columns in that one table.

+

+ The DISTINCT keyword causes a subset of result rows to be returned, in which each + result row is different. NULL values are not treated as distinct from each other. + The default behavior is that all result rows be returned, which can be made explicit + with the keyword ALL.

+

+ The query is executed against one or more tables specified after the FROM keyword. + If multiple tables names are separated by commas, then the query is against the + cross join of the various tables. The full SQL-92 join syntax can also be used to + specify joins. A sub-query in parentheses may be substituted for any table name + in the FROM clause. The entire FROM clause may be omitted, in which case the result + is a single row consisting of the values of the expression list. +

+

+ The WHERE clause can be used to limit the number of rows over which the query operates.

+

+ The GROUP BY clauses causes one or more rows of the result to be combined into a + single row of output. This is especially useful when the result contains aggregate + functions. The expressions in the GROUP BY clause do not have to be expressions + that appear in the result. The HAVING clause is similar to WHERE except that HAVING + applies after grouping has occurred. The HAVING expression may refer to values, + even aggregate functions, that are not in the result.

+

+ The ORDER BY clause causes the output rows to be sorted. The argument to ORDER BY + is a list of expressions that are used as the key for the sort. The expressions + do not have to be part of the result for a simple SELECT, but in a compound SELECT + each sort expression must exactly match one of the result columns. Each sort expression + may be optionally followed by a COLLATE keyword and the name of a collating function + used for ordering text and/or keywords ASC or DESC to specify the sort order.

+

+ The LIMIT clause places an upper bound on the number of rows returned in the result. + A negative LIMIT indicates no upper bound. The optional OFFSET following LIMIT specifies + how many rows to skip at the beginning of the result set. In a compound query, the + LIMIT clause may only appear on the final SELECT statement. The limit is applied + to the entire query not to the individual SELECT statement to which it is attached. + Note that if the OFFSET keyword is used in the LIMIT clause, then the limit is the + first number and the offset is the second number. If a comma is used instead of + the OFFSET keyword, then the offset is the first number and the limit is the second + number. This seeming contradition is intentional - it maximizes compatibility with + legacy SQL database systems. +

+

+ A compound SELECT is formed from two or more simple SELECTs connected by one of + the operators UNION, UNION ALL, INTERSECT, or EXCEPT. In a compound SELECT, all + the constituent SELECTs must specify the same number of result columns. There may + be only a single ORDER BY clause at the end of the compound SELECT. The UNION and + UNION ALL operators combine the results of the SELECTs to the right and left into + a single big table. The difference is that in UNION all result rows are distinct + where in UNION ALL there may be duplicates. The INTERSECT operator takes the intersection + of the results of the left and right SELECTs. EXCEPT takes the result of left SELECT + after removing the results of the right SELECT. When three or more SELECTs are connected + into a compound, they group from left to right.

+

+


+  

+ +
+
+ + ADDED Doc/Extra/lang_transaction.html Index: Doc/Extra/lang_transaction.html ================================================================== --- Doc/Extra/lang_transaction.html +++ Doc/Extra/lang_transaction.html @@ -0,0 +1,177 @@ + + + + TRANSACTIONS + + + + + + + + + + + + + + + + +
+
+

+ SQL As Understood By SQLite

+

+ BEGIN TRANSACTION

+

+ + + + + +
+ sql-statement ::= + BEGIN [ DEFERRED + | IMMEDIATE | EXCLUSIVE ] [TRANSACTION [name]]
+ + + + + +
+ sql-statement ::= + END [TRANSACTION + [name]]
+ + + + + +
+ sql-statement ::= + COMMIT [TRANSACTION + [name]]
+ + + + + +
+ sql-statement ::= + ROLLBACK [TRANSACTION + [name]]
+

+

+ Beginning in version 2.0, SQLite supports transactions with rollback and atomic + commit.

+

+ The optional transaction name is ignored. SQLite currently does not allow nested + transactions.

+

+ No changes can be made to the database except within a transaction. Any command + that changes the database (basically, any SQL command + other than SELECT) will automatically start a transaction if one is not already in effect. Automatically started transactions + are committed at the conclusion of the command. +

+

+ Transactions can be started manually using the BEGIN command. Such transactions + usually persist until the next COMMIT or ROLLBACK command. But a transaction will + also ROLLBACK if the database is closed or if an error occurs and the ROLLBACK conflict + resolution algorithm is specified. See the documentation on the + ON CONFLICT clause for additional information about the ROLLBACK conflict + resolution algorithm. +

+

+ In SQLite version 3.0.8 and later, transactions can be deferred, immediate, or exclusive. + Deferred means that no locks are acquired on the database until the database is + first accessed. Thus with a deferred transaction, the BEGIN statement itself does + nothing. Locks are not acquired until the first read or write operation. The first + read operation against a database creates a SHARED lock and the first write operation + creates a RESERVED lock. Because the acquisition of locks is deferred until they + are needed, it is possible that another thread or process could create a separate + transaction and write to the database after the BEGIN on the current thread has + executed. If the transaction is immediate, then RESERVED locks are acquired on all + databases as soon as the BEGIN command is executed, without waiting for the database + to be used. After a BEGIN IMMEDIATE, you are guaranteed that no other thread or + process will be able to write to the database or do a BEGIN IMMEDIATE or BEGIN EXCLUSIVE. + Other processes can continue to read from the database, however. An exclusive transaction + causes EXCLUSIVE locks to be acquired on all databases. After a BEGIN EXCLUSIVE, + you are guaranteed that no other thread or process will be able to read or write + the database until the transaction is complete. +

+

+ A description of the meaning of SHARED, RESERVED, and EXCLUSIVE locks is available + separately. +

+

+ The default behavior for SQLite version 3.0.8 is a deferred transaction. For SQLite + version 3.0.0 through 3.0.7, deferred is the only kind of transaction available. + For SQLite version 2.8 and earlier, all transactions are exclusive. +

+

+ The COMMIT command does not actually perform a commit until all pending SQL commands + finish. Thus if two or more SELECT statements are in the middle of processing and + a COMMIT is executed, the commit will not actually occur until all SELECT statements + finish. +

+

+ An attempt to execute COMMIT might result in an SQLITE_BUSY return code. This indicates + that another thread or process had a read lock on the database that prevented the + database from being updated. When COMMIT fails in this way, the transaction remains + active and the COMMIT can be retried later after the reader has had a chance to + clear. +

+

+


+  

+ +
+
+ + ADDED Doc/Extra/lang_update.html Index: Doc/Extra/lang_update.html ================================================================== --- Doc/Extra/lang_update.html +++ Doc/Extra/lang_update.html @@ -0,0 +1,112 @@ + + + + UPDATE + + + + + + + + + + + + + + + + +
+
+

+ SQL As Understood By SQLite

+

+ UPDATE

+

+ + + + + + + + + +
+ sql-statement ::= + UPDATE [ OR + conflict-algorithm + ] [database-name .] table-name
+ SET
assignment + [, + assignment]*
+
[WHERE expr]
+ assignment ::= + column-name = expr
+

+

+ The UPDATE statement is used to change the value of columns in selected rows of + a table. Each assignment in an UPDATE specifies a column + name to the left of the + equals sign and an arbitrary expression to the right. The expressions may use the + values of + other columns. All expressions are evaluated before any assignments are + made. A WHERE clause can be used to restrict which rows are updated.

+

+ The optional conflict-clause allows the specification of an alternative constraint conflict resolution algorithm to use during this one command. See the section titled + ON CONFLICT for additional information.

+

+


+  

+ +
+
+ + ADDED Doc/Extra/lang_vacuum.html Index: Doc/Extra/lang_vacuum.html ================================================================== --- Doc/Extra/lang_vacuum.html +++ Doc/Extra/lang_vacuum.html @@ -0,0 +1,109 @@ + + + + VACUUM + + + + + + + + + + + + + + + + +
+
+

+ SQL As Understood By SQLite

+

+ VACUUM

+

+ + + + + +
+ sql-statement ::= + VACUUM [index-or-table-name]
+

+

+ The VACUUM command is an SQLite extension modeled after a similar command found + in PostgreSQL. If VACUUM is invoked with the name of a table or index then it is + suppose to clean up the named table or index. In version 1.0 of SQLite, the VACUUM + command would invoke gdbm_reorganize() to clean up the backend database file.

+

+ VACUUM became a no-op when the GDBM backend was removed from SQLITE in version 2.0.0. + VACUUM was reimplemented in version 2.8.1. The index or table name argument is now + ignored. +

+

+ When an object (table, index, or trigger) is dropped from the database, it leaves + behind empty space. This makes the database file larger than it needs to be, but + can speed up inserts. In time inserts and deletes can leave the database file structure + fragmented, which slows down disk access to the database contents. The VACUUM command + cleans the main database by copying its contents to a temporary database file and + reloading the original database file from the copy. This eliminates free pages, + aligns table data to be contiguous, and otherwise cleans up the database file structure. + It is not possible to perform the same process on an attached database file.

+

+ This command will fail if there is an active transaction. This command has no effect + on an in-memory database.

+

+ As of SQLite version 3.1, an alternative to using the VACUUM command is auto-vacuum + mode, enabled using the auto_vacuum pragma.

+

+


+  

+ +
+
+ + ADDED Doc/Extra/pragma.html Index: Doc/Extra/pragma.html ================================================================== --- Doc/Extra/pragma.html +++ Doc/Extra/pragma.html @@ -0,0 +1,656 @@ + + + + PRAGMA + + + + + + + + + + + + + + + + +
+
+

+ SQL As Understood By SQLite

+

+ PRAGMA

+

+ The PRAGMA command is a special command used to modify the + operation of the SQLite library or to query the library for internal (non-table) + data. The PRAGMA command is issued using the same interface as other SQLite commands + (e.g. SELECT, INSERT) but is different in the following important respects: +

+ +

+ The available pragmas fall into four basic categories:

+ +
+ +

+ PRAGMA command syntax

+

+ + + + + +
+ sql-statement ::= + PRAGMA name [= + value] + |
+ PRAGMA
function(arg)
+

+

+ The pragmas that take an integer value also accept symbolic names. + The strings "on", "true", and "yes" are equivalent to 1. + The strings "off", "false", and "no" are equivalent to 0. + These strings are case- insensitive, and do not require quotes. An unrecognized + string will be treated as 1, and will not generate an error. When the value + is returned it is as an integer.

+
+ +

+ Pragmas to modify library operation

+ +
+ +

+ Pragmas to query the database schema

+ +
+ +

+ Pragmas to query/modify version values

+ +
+ +

+ Pragmas to debug the library

+ +

+


+  

+ +
+
+ + ADDED Doc/Extra/syntax.html Index: Doc/Extra/syntax.html ================================================================== --- Doc/Extra/syntax.html +++ Doc/Extra/syntax.html @@ -0,0 +1,221 @@ + + + + SQLite Query Syntax + + + + + + + + + + + + + + + + +
+
+

+ SQL As Understood By SQLite

+

+ The SQLite library understands most of the standard SQL language. But it does omit + some features 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 keywords is also provided. 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. This document is just an overview of the SQL syntax implemented + by SQLite. +

+

+ SQLite implements the follow syntax:

+

+ + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + +
+ + ALTER TABLE
+ + ANALYZE
+ + ATTACH DATABASE
+ + BEGIN TRANSACTION
+ + comment
+ + COMMIT TRANSACTION
+ CREATE INDEX
+ CREATE TABLE
+ CREATE TRIGGER
+ CREATE VIEW
+ + CREATE VIRTUAL TABLE
+ + DELETE
+ + DETACH DATABASE
+ DROP INDEX
+ DROP TABLE
+ DROP TRIGGER
+ DROP VIEW
+ + END TRANSACTION
+ + EXPLAIN
+ + expression
+ + INSERT
+ + ON CONFLICT clause
+ + PRAGMA
+ + REINDEX
+ + REPLACE
+ + ROLLBACK TRANSACTION
+ + SELECT
+ + UPDATE
+ + VACUUM
+

+
+ +
+
+ +