Sqlite3: core read-only and ATTACH database

Question

I want to understand the implications of using ATTACH for databases with different read and write permissions.

I have a scenario where I need to access a large database (about 512 MB), which is located in a read-only file system. There is also a small read and write database with the same schema as found in the read / write file system. A read-only database provides the basic data used in my script, with infrequent updates to the data stored in the database for reading and writing.

I am currently opening these two databases in separate connections, and the code supporting the connections is responsible for presenting a single view of the data to my clients. For example, this means that the code should combine the query results from read-only, read-write, etc. databases. I understand that this setting is non-elementary (and probably suboptimal), and tried to use the ATTACH command to create a unified representation of the data in SQL, not C ++.

I am wondering if there are any special problems associated with adding read-only and read-write databases that I should be aware of. I am considering one of the following ATTACH scripts:

• Open the read-only database as the primary, and ATTACH the read and write database. This is my preferred solution.
• Open the read and write database as the primary, and ATTACH the read-only database.
• Third option?

Several Google queries point to messages suggesting problems in the script (1). Since I did not find a definitive answer, and because my own testing using SQLite 3.6.13 did not reveal any problems, I am posting this question.

Thanks for any ideas.

Answer 1

the documentation does not seem to mention any warnings about attaching read-only and read-only databases.

So my assumption is that what you should expect is opening databases, which should open separately when you open one and attach the other.

I put your script 1 on a test and it works fine. Here is what I tried:

[ someone@somewhere tmp]$ echo .dump | sqlite3 big_readonly_db PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; CREATE TABLE foo (a INT); INSERT INTO "foo" VALUES(1); INSERT INTO "foo" VALUES(2); INSERT INTO "foo" VALUES(3); INSERT INTO "foo" VALUES(4); INSERT INTO "foo" VALUES(5); COMMIT; [ someone@somewhere tmp]$ echo .dump | sqlite3 small_readwrite_db PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; CREATE TABLE bar (a INT); COMMIT; [ someone@somewhere tmp]$ chmod -w big_readonly_db [ someone@somewhere tmp]$ ls -l big_readonly_db -r--r--r-- 1 someone someone 2048 Apr 12 21:41 big_readonly_db [ someone@somewhere tmp]$ sqlite3 big_readonly_db SQLite version 3.7.7.1 2011-06-28 17:39:05 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> attach database small_readwrite_db as rw; sqlite> insert into bar select * from foo; sqlite> select * from bar; 1 2 3 4 5