You can use separate SQLite database on different machines as a single virtual database by using the attach command in SQLite.
I read about the technique here: http://souptonuts.sourceforge.net/readme_sqlite_tutorial.html
For this example let's use the simplest possible database. A contact database containing nothing but email addresses.
1. Create SQL script for a simple contacts database
File createcontacts.sql:
CREATE TABLE contacts ( id INTEGER PRIMARY KEY, email VARCHAR(64)); |
2. Create two databases
Create the databases:
$ sqlite3 contacts1.db < createcontacts.sql $ sqlite3 contacts2.db < createcontacts.sql
Notice: I'm using SQLite on Mac OS X, which comes preinstalled with /usr/bin/sqlite3.
Insert one contact in each database:
$ sqlite3 contacts1.db "insert into contacts (email) values ('john@example.com');"
$ sqlite3 contacts2.db "insert into contacts (email) values ('jane@example.com');"
The are now two databases, each with a row of data:
$ ls contacts1.db contacts1.sql contacts2.db contacts2.sql $ sqlite3 contacts1.db 'select * from contacts;' 1|john@example.com $ sqlite3 contacts2.db 'select * from contacts;' 1|jane@example.com
Next, we'll combine the databases using the SQLite command attach.
3. Create a single virtual database
First, enter the sqlite3 console:
$ sqlite3 SQLite version 3.6.12 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite>
Then, attach the two databases:
sqlite> attach database 'contacts1.db' as c1; sqlite> attach database 'contacts2.db' as c2;
Check that it went well with the .databases command:
sqlite> .databases seq name file --- --------------- ---------------------------------------------------------- 0 main 2 c1 /Users/kostas/Documents/Dev/SqliteExp/attach/contacts1.db 3 c2 /Users/kostas/Documents/Dev/SqliteExp/attach/contacts2.db
Finally, select email addresses simultaneously from contacts1.db and contacts2.db:
sqlite> select 'c1',* from c1.contacts union select 'c2',* from c2.contacts; c1|1|john@example.com c1|2|janet@example.com c1|3|joe@example.com c2|1|jane@example.com
This demonstrates how one can select rows from multiple databases at once, using the attach command in SQLite. Imagine that the databases are actually on separate machines and accessed via e.g. NFS mounts.
Next, I'll look at spatial data in SQLite.