Attaching to Another Database
command lists all the databases that are open for the current session. There will always be two databases
sqlite> .databases 0 main /home/chris/sqlite/demodb 1 temp /var/tmp/sqlite VGazbfyWvuUr29P
It is possible to attach more databases to your current session with the ATTACH DATABASE statement. This adds a connection to another database without replacing your currently selected database.
The syntax is
ATTACH [DATABASE] database-filename AS database-name
The keyword DATABASE is optional and is used only for readability, but you must provide a unique database-name parameter that will be used to qualify table references, essential in case more than one database could have the same table name.
Suppose you are working on a new database called newdb and want to access some of the databases from our demo database from Chapter 2. The following example shows demodb being attached to the current sqlite session:
$ sqlite newdb SQLite version 2.8.12 Enter ".help" for instructions sqlite> ATTACH DATABASE demodb AS demodb; sqlite> . databases 0 main /home/chris/sqlite/newdb 1 temp /var/tmp/sqlite_VGazbfyWvuUr29P 2 demodb /home/chris/sqlite/demodb
Accessing tables from an attached database is straightforwardjust prefix any table name with the database name (the name given after the keyword AS , not the filename, if they are different) and a period.
We can perform a query on the
sqlite> SELECT company_name FROM demodb.clients; company_name -------------------- Acme Products ABC Enterprises Premier Things Ltd
Tables in the main database can be accessed using their table name alone, or qualified as main.tablename . If a table name is unique across all databases attached in a particular session, it does not need to be prefixed with its database name even if it is not in the main database. However, it is still good practice to qualify all tables when you are working with multiple databases to avoid confusion.
The SQL commands INSERT , UPDATE , SELECT , and DELETE can all be performed on an attached database by using the database name prefix. However, CREATE TABLE and DROP TABLE can only take place on the main databaseyou must exit sqlite and begin a new session if you want to manipulate tables from a different database.
Note the situation with multi-database transactions here. If a machine or software failure occurs, a transaction is only atomic within one database. If more than one database were written to within a single transaction, one database might be committed and the other rolled back in the event of a failure.
There is a compile-time limit of 10 attached database files by default. This can be increased to up to 255 concurrent databases by modifying the following line in src/sqliteInt.h :
#define MAX_ATTACHED 10
To detach an attached database, the syntax is simply
DETACH [DATABASE] database-name
Next we'll look at how records can be added to a database and
Any change to a SQLite database must take place within a
a block of one or more statements that alter the database in some way. Transactions are the way in which a robust database system ensures that either all or none of the
command is issued, SQLite will begin a new transaction unless one has already been started. An implicit transaction lasts only for the duration of the one statement but ensures that, for instance, an
A transaction can be started from SQL if you want to make a series of changes to the database as one atomic unit. This is the syntax of the BEGIN TRANSACTION statement:
BEGIN [TRANSACTION [name]] [ON CONFLICT conflict-algorithm]
The transaction name is optional and, currently, is ignored by SQLite. The facility to provide a transaction name is included for future use if the ability to nest transactions is added. Currently only one transaction can be
An ON CONFLICT clause can be specified to override the default conflict resolution algorithm specified at the table level, but can be superseded itself by the OR clause of an INSERT , UPDATE , or DELETE statement.
To end a transaction and save changes to the database, use COMMIT TRANSACTION . The optional transaction name may be specified. To abort a transaction without any of the changes being stored, use ROLLBACK TRANSACTION .
There are two versions of the syntax for the INSERT statement, depending on where the data to be inserted is coming from.
The first syntax is the one we have already used in Chapter 2, to insert a single row from values provided in the statement itself. The second version is used to insert a dataset returned as the result of a SELECT statement.
INSERT Using VALUES
The syntax for a single-row insert using the
keyword and a list of values provided as part of the statement is as
INSERT [OR conflict-algorithm] INTO [database-name .] table-name [(column-list)] VALUES (value-list)
Although all our examples so far have included a column-list , it is actually optional. Where no column-list is provided, the value-list is assumed to contain one value for each column in the table, in the order they appear in the schema.
This can be a useful shortcut when you are adding data; for instance because we know the
is a name and then a
sqlite> INSERT INTO vegetables VALUES ('mushroom', 'white');
However if the schema of the table is not what you are expecting, the
will fail with an error. SQLite would not make any assumption as to which
Because SQLite does not have an ALTER TABLE command, it is much harder to change a schema after a table has been created than it is with other database engines that include this command. We'll see a workaround for ALTER TABLE in the following example, and if for any reason the vegetables table had been expanded to include three columns, the same INSERT statement would produce this error:
sqlite> INSERT INTO vegetables VALUES ('mushroom', 'white'); SQL error: table vegetables has 3 columns but 2 values were supplied
Therefore it is good practice to always include the column-list in an INSERT statementalso known as performing a full insert .
The OR keyword is used to specify a conflict resolution algorithm in the same way we saw for the CREATE TABLE statement. The list of algorithms and their behavior is identical, but the keyword OR is used instead of ON CONFLICT to give a more natural-sounding syntax.
The conflict algorithm in the OR clause of an INSERT statement has the highest precedence possible, and will override any other setting present at the table or transaction level.
INSERT Using SELECT
The syntax to insert the result of a SELECT query into another table is as follows:
INSERT [OR conflict-algorithm] INTO [database-name .] table-name [(column-list)] select-statement
The select-statement should return a dataset with the same number and order as the columns specified in the column-list (or every column in the destination table if no column-list is supplied). The full syntax of the SELECT statement is available, and any number of rows can be returned.
As with INSERT ... VALUES , the column-list is optional but including it is highly advisable.
The syntax of the UPDATE statement in SQLite is as follows:
UPDATE [OR conflict-algorithms] [database-name .] table-name SET assignment [, assignment]* [WHERE expr]
One or more assignments can be performed within the same statement upon the same subset of data, defined by the optional WHERE clause. An assignment is defined as
column-name = expr
clause is not required, it is usually desirable. The following example would assign the value of
for every row in the table, when in fact we probably only
sqlite> UPDATE vegetables ...> SET color = 'green';
The WHERE clause can be as simple or complex as necessary, and all the conditional elements that can be used in the WHERE clause of a SELECT statement can be used here.
It is not logical to join two or more tables when performing an UPDATE ; however, subselects can be used in the WHERE clause, as in the following example:
sqlite> UPDATE mytable ...> SET myfield = 'somevalue' ...> WHERE mykey IN ( ...> SELECT keyfield ...> FROM anothertable ...> );
The OR keyword is used in an UPDATE statement to specify a conflict resolution algorithm with the highest precedence possible, in the same way as with an INSERT .
The DELETE statement is used to remove rows from a database. Its syntax is
DELETE FROM [database-name .] table-name [WHERE expr]
As with the UPDATE statement, the WHERE clause is optional but is usually desiredperforming a DELETE on a table with no WHERE clause will empty the table. No column-list is required for a DELETE because the operation affects the entire row.
The WHERE clause can use the AND and OR operators to combine conditions and can use subselects to perform a DELETE operation conditional on the results of another query.
The following example modifies a query from Chapter 2 to remove records from the
table where the
field does not
sqlite> DELETE FROM timesheets ...> WHERE project_code NOT IN ( ...> SELECT code ...> FROM projects ...> );
Altering a Table Schema
There is no ALTER TABLE statement in SQLite; instead a table must be dropped and re-created with a new field added, with any data that you want to preserve extracted before the table is dropped and reloaded into the new structure.
A temporary table is the ideal place to hold such data, and the CREATE TABLE ... AS syntax gives us a very easy way to create a copy of an existing table. The syntax is simply
CREATE [TEMP TEMPORARY TABLE] table-name AS select-statement
Let's suppose we want to add a new descriptive column to our vegetables table but without losing the data we have already created. The first step is to take a copy of the existing vegetables table to a new temporary table.
sqlite> CREATE TEMPORARY TABLE veg_temp ...> AS SELECT * FROM vegetables;
However, only the field specification has been
sqlite> .schema veg_temp CREATE TEMP TABLE veg_temp(name,color);
Compare this to the schema of the original vegetables table, which we'll need for re-creating the table with the new field:
sqlite> .schema vegetables CREATE TABLE vegetables ( name CHAR NOT NULL, color CHAR NOT NULL DEFAULT 'green' );
So now we can safely drop the old vegetables table and re-create it with our new field:
sqlite> DROP TABLE vegetables; sqlite> CREATE TABLE vegetables ( ...> name CHAR NOT NULL, ...> color CHAR NOT NULL DEFAULT 'green', ...> description CHAR ...> );
Finally, reinstate the copied data from the temporary table using the INSERT ... SELECT syntax:
sqlite> INSERT INTO vegetables (name, color) ...> SELECT name, color FROM veg_temp;
Loading Data from a File
The COPY command in SQLite was based on a similar command found in PostgreSQL and as a result is designed to read the output of the pg_dump command to facilitate data transfer between the two systems.
However, COPY can also be used to load data from most delimited text file formats into SQLite. It has the following syntax:
COPY [OR conflict-algorithm] [database-name .] table-name FROM filename [USING DELIMITERS delim]
The destination table
must existthough it need not be emptybefore the
operation is attempted, and either
must be in the current directory or a full
Each line in the input file will become a record in the table, with each column separated by a tab character, unless a different
If a tabor the specified delimiterappears within a data column, it must be escaped with a backslash character. The backslash itself can appear in the data if it is escaped itself; in other words it will appear as two consecutive backslash
The special character sequence \N in the data file can be used to represent a NULL value.
Tab is used to separate columns in the output of pg_dump , so it is the default delimiter for the COPY command. Another popular format is comma-separated values (CSV). Listing 3.1 shows a comma-separated data file that can be loaded into the three-column vegetables table.
Listing 3.1. vegetables.csv
cucumber,green,Long green salad vegetable pumpkin,orange,Great for Halloween avocado,green,Can't make guacamole without it
The COPY command to load this data file into SQLite is
sqlite> COPY vegetables FROM 'vegetables.csv' ...> USING DELIMITERS ',';
You can instruct
to read data from the standard input stream instead of a file by using the keyword
instead of a filename. A blank line, or a backslash followed by a period, is used to