Attaching to Another Database
|
| Using sqlite, the .databases command lists all the databases that are open for the current session. There will always be two databases open after you invoke sqlitemain, the database specified on the command line, and temp, the database used for temporary tables. 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 clients table from demodb as follows: 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. Note 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 |
|
