The Long Weekend Website

How to attach multiple SQLite databases together

By Mark on May 29th, 2010 in Technobabble
Tags: databases, development, Press Releases, sqlite

Our iPhone application Japanese Flash had a large (60MB+) table of read-only data, and a small table to which we wanted to store new data. Copying the entire database takes about 30 seconds on an iPhone 3G, so we decided to find a way to split the tables into 2 databases. The large database stays in the application bundle (NSBundle), and the lightweight user database is copied quickly.

This entry describes how to connect multiple SQLite databases to the same active database connection using the ATTACH DATABASE statement. The tutorial assumes that you know how to write SQL statements, and how to type commands into the Terminal. It also assumes you’re running either Mac OS X or some Linux variant that has sqlite3 installed.

If you already have two database files ready to go and you’re comfortable, skip to step 2. If you want the full tutorial from scratch, start at step 1.

Step 1: Create two databases using sqlite3 in the OS X Terminal

Let’s create some data from scratch. First, open a Terminal window and open sqlite3.

SQLite comes prepackaged with OS X, so I highly recommend setting everything up and testing your logic on the command line, not in your program. It’s much easier to spot problems in your SQL query using the interactive session provided on the command line.

Marks-Apple:~ Mark$ sqlite3 readOnlyDatabase.sqlite.db

This will launch sqlite3, creating a new database file called readOnlyDatabase.sqlite.db. Inside sqlite3, we are prompted to do something with our currently-empty database.

Note that I am merely calling the file readOnly as an indication of how I intend to use the data, this does not mean that the database is actually read-only.

I’m tired of these boring tutorials with “students” and “exams” and so forth – let’s have some immature fun, people. I’m going to make a database to keep track of my ongoing contacts with ex-girlfriends.

sqlite>CREATE TABLE exgirlfriends (name TEXT, birthday DATE);

Are these real? You’ll never know (they will, though).

sqlite>INSERT INTO exgirlfriends VALUES ('Danielle','1982-07-25');
INSERT INTO exgirlfriends VALUES ('Yukiko','1981-12-17');
INSERT INTO exgirlfriends VALUES ('Heather','1980-02-26');

OK, so now we have a test table in a database with some data (some of my ex’s and their birthdays, or at least to the best of my memory). Time to save this database and create another one.

sqlite> .exit

Worth a check to make sure your database file saved properly. Then we’ll open sqlite3 again with a new database name.

Marks-Apple:~ Mark$ ls -al readOnlyDatabase.sqlite.db
-rw-r--r--  1 Mark  staff  4096 May 29 12:09 readOnlyDatabase.sqlite.db
Marks-Apple:~ Mark$ sqlite3 writableDatabase.sqlite.db
SQLite version 3.6.12
sqlite>

This time, we’re going to have data that changes/is writable – my ex’s can’t change their names or birthdays, but maybe we want to keep track of whether they are OK to call, or if they have new boyfriends.

sqlite>CREATE TABLE current_ex_status (name TEXT, ok_to_call INTEGER, has_bf INTEGER);
sqlite>
INSERT INTO current_ex_status VALUES ('Danielle',1,1);
INSERT INTO current_ex_status VALUES ('Yukiko',1,0);
INSERT INTO current_ex_status VALUES ('Heather',0,1);
sqlite>.exit

Marks-Apple:~ Mark$ ls -al *.sqlite.db
-rw-r--r--  1 phooze  staff  4096 May 29 12:09 readOnlyDatabase.sqlite.db
-rw-r--r--  1 phooze  staff  4096 May 29 12:31 writableDatabase.sqlite.db

Step 2: Attach the databases

All done. Now let’s attach these databases together using the href="http://www.sqlite.org/lang_attach.html" target="_blank">ATTACH DATABASE command. The syntax is a snap:

ATTACH DATABASE filename AS database-name;

The DATABASE keyword is optional, but I like the clarity of it. filename should be the path to the database file you wish to attach, and the database-name is a unique name. Both main and temp are reserved. main refers to whatever database you specified on the command line when launching sqlite3.

Let’s attach our secondary database. I’ve chosen the name ex_data. I also put the filename in double quotes because you never know when our filename could contain spaces or other unsavory characters.

sqlite>ATTACH DATABASE "readOnlyDatabase.sqlite.db" AS ex_data;

To confirm the successful attachment, issue the .databases command.

sqlite>.databases
seq  name             file
---  ---------------  ----------------------------------------------------------
0    main             /Users/Mark/writableDatabase.sqlite.db
2    ex_data          /Users/Mark/readOnlyDatabase.sqlite.db

However, there’s a catch here. Tables from the attached database will not show up with the .tables command, nor will they show up in the SQLite sqlite_master table (if you’re not familiar with sqlite_master , it’s simply a table that exists in every SQLite database to keep track of information about the other tables). sqlite_master is database-specific. When you type .tables or SELECT name FROM sqlite_master WHERE type='table';, you’re really querying main.sqlite_master.

To confirm the attached database’s tables, specify the database name before the table name:

sqlite>SELECT name FROM ex_data.sqlite_master WHERE type='table';
exgirlfriends

And just like that, ex-girlfriends come out of the woodwork when you didn’t expect it. How apropos.

Anyway, let’s query some data. I’m going to phone up one of my ex’s, and I want to make sure I remember her birthday – otherwise, I look like the jerk that I am if it just passed or is coming up and I don’t say anything. I join the two tables, even though they are on different databases. SQLite doesn’t mind at all.

sqlite>SELECT st.name, ex.birthday FROM exgirlfriends ex, current_ex_status st WHERE st.name = ex.name AND st.ok_to_call = 1;
Danielle|1982-07-25
Yukiko|1981-12-17

Our query works and our database attaches smoothly. You’re ready to go.

A few key words of warning: if both databases contain the same table name, that’s fine – but like different tables with the same column name, you have to specify the database name in dot notation before using these tables. For example: database-name.table-name.

Next, I’ll blog about how to use this cleverly with iPhone applications to make versioning easier.