I ran experiments about transfering data from a readonly (RO) src database to an attached readwrite (RW) dst database.

With the CLI

• if src is open with ?mode=ro, attaching dst and trying to transfer data will work.
• if -readonly is used when opening src, attaching dst and trying to transfer data will not work

With the C API

error = sqlite3_open_v2("file:src.db", &db, SQLITE_OPEN_CREATE | SQLITE_OPEN_READWRITE | SQLITE_OPEN_URI, nullptr); ro = sqlite3_db_readonly(db , sqlite3_db_name(db, 0)); error = sqlite3_exec(db, "CREATE TABLE IF NOT EXISTS src (id INTEGER PRIMARY KEY)", nullptr, nullptr, &errmsg); error = sqlite3_exec(db, "INSERT OR REPLACE INTO src VALUES(1)", nullptr, nullptr, &errmsg); error = sqlite3_close(db);

error = sqlite3_open_v2("file:dst.db", &db, SQLITE_OPEN_CREATE | SQLITE_OPEN_READWRITE | SQLITE_OPEN_URI, nullptr); error = sqlite3_exec(db, "CREATE TABLE IF NOT EXISTS dst (id INTEGER PRIMARY KEY)", nullptr, nullptr, &errmsg); error = sqlite3_exec(db, "DELETE FROM dst;", nullptr, nullptr, &errmsg); error = sqlite3_close(db);

error = sqlite3_open_v2("file:src.db", &db, SQLITE_OPEN_READWRITE | SQLITE_OPEN_URI, nullptr); ro = sqlite3_db_readonly(db , sqlite3_db_name(db, 0));//readonly is 0 error = sqlite3_exec(db, "ATTACH DATABASE \"file:dst.db\" AS dst", nullptr, nullptr, &errmsg); error = sqlite3_exec(db, "DELETE FROM dst;", nullptr, nullptr, &errmsg);//OK error = sqlite3_exec(db, "INSERT OR REPLACE INTO dst SELECT * FROM src", nullptr, nullptr, &errmsg);//OK error = sqlite3_close(db);

error = sqlite3_open_v2("file:src.db", &db, SQLITE_OPEN_READONLY | SQLITE_OPEN_URI, nullptr); ro = sqlite3_db_readonly(db , sqlite3_db_name(db, 0));//readonly is 1 error = sqlite3_exec(db, "ATTACH DATABASE \"file:dst.db\" AS dst", nullptr, nullptr, &errmsg); error = sqlite3_exec(db, "DELETE FROM dst;", nullptr, nullptr, &errmsg);//error SQLITE_READONLY error = sqlite3_exec(db, "INSERT OR REPLACE INTO dst SELECT * FROM src", nullptr, nullptr, &errmsg);//error SQLITE_READONLY error = sqlite3_close(db);

error = sqlite3_open_v2("file:src.db", &db, SQLITE_OPEN_READONLY | SQLITE_OPEN_URI, nullptr); ro = sqlite3_db_readonly(db , sqlite3_db_name(db, 0));//readonly is 1 error = sqlite3_exec(db, "ATTACH DATABASE \"file:dst.db?mode=rw\" AS dst", nullptr, nullptr, &errmsg);//error SQLITE_ERROR error = sqlite3_exec(db, "DELETE FROM dst;", nullptr, nullptr, &errmsg);//error SQLITE_ERROR error = sqlite3_exec(db, "INSERT OR REPLACE INTO dst SELECT * FROM src", nullptr, nullptr, &errmsg);//error SQLITE_ERROR error = sqlite3_close(db);

error = sqlite3_open_v2("file:src.db?mode=ro", &db, SQLITE_OPEN_READWRITE | SQLITE_OPEN_URI, nullptr); ro = sqlite3_db_readonly(db , sqlite3_db_name(db, 0));//readonly is 1 error = sqlite3_exec(db, "ATTACH DATABASE \"file:dst.db\" AS dst", nullptr, nullptr, &errmsg); error = sqlite3_exec(db, "DELETE FROM dst;", nullptr, nullptr, &errmsg);//OK error = sqlite3_exec(db, "INSERT OR REPLACE INTO dst SELECT * FROM src", nullptr, nullptr, &errmsg);//OK error = sqlite3_close(db);

So it seems that SQLITE_OPEN_READONLY is equivalent to using -readonly on the CLI, while opening src with ?mode=ro overriding SQLITE_OPEN_READWRITE is the solution to succesfully transfer from src to dst.

Do you confirm that it is the expected behaviour and design ? I find it surprising that SQLITE_OPEN_READONLY is so restrictive (and not enough documented regarding such a case)

Have you considered that the flags apply to the connection and not to the file?

Having the connection be readonly is an entirely different thing from having a file (one of possibly many associated with a connection) being readonly.

I have no idea if this is explicitly documented (nor have I examined the code particularly to verify this) however all the descriptions of the connection flags would indicate that they apply to the connection and only indirectly to underlying files; and that flags applied to the file (via URL mode flags) appear to apply to that one specific file and not the entire connection.

Is there a logical reason for why file:dst.db?mode=rwc does not work in the last section? I.e why can't you create a database when attaching it when the original database is mode=ro but you can attach an existing one in read-write mode?

No, not only is there no logical reason. In fact, it does work.

Note, that it works correctly with the tip of trunk. No clue which version you are claiming behaves differently.

I did the observation in an application using sqlite-amalgamation-3410100.zip. Then I thought I got the observation confirmed by line 333 in the file test/e_uri.test.

After further analysis I found that I have to pass SQLITE_OPEN_CREATE to sqlite3_open_v2 on the first database in order to allow mode=rwc later on when attaching a (non-existing) database.

So my updated question is: Why do I have to pass SQLITE_OPEN_CREATE to sqlite3_open_v2 with mode=ro if I later on want to attach a non-existing database with mode=rwc?

Sorry, I believe I may be mistaken here.

I believe I patched my copy of SQLite3 so that the "open flags" are the "default" flags for attachments (including the main file) on the connection, but that they may be overridden by "finer grained flags specified on the file itself" and not as a limitation of those flags.

For example, sqlite3_open("file:test.db?mode=rwc", flags=SQLITE_OPEN_READONLY) and then attach 'file:dribble.db?mode=rwc' as x, however, if you did not specify particular flags for the file via the mode= URI parameter, then the file would be opened/attached using the flags thus resulting in opening a readonly file that must already exist.

This pattern with the src database in read-only mode and the destination in read-write mode is powerfull.

Do I understand correctly that it doesn't play well if you want to use write transaction on the destination?

From the documentation I could not figure out what to expect if I start a transaction before attaching the dst database. The transaction is started on a read-only database so it shouldn't make sense that this transaction is upgraded to a read-write transaction, right?

Does anyone know how this is supposed to work?

FWIW, I'm using the databases with journal_mode=WAL.

This pattern with the src database in read-only mode and the destination in read-write mode is powerfull.

Yes it is. Using the standard code where the flags are upper permission limits means that you would have to open the connection with "normal" flags and specify the mode you want to apply to each file.

sqlite3_open('file:dizzy.db?mode=ro', flags=SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE)
attach 'file:writable.db?mode=rw' as writetomeplease;

I have no idea the effect of attaching/detaching crap while autocommit is turned off. I doubt that the autocommit setting has any effect whatsoever. Also note that "transactions" are per database file, and not per connection.

You may have difficulty (ie, pop an error) if you attempt to detach a file while it has a transaction active -- I would expect that you should get a (6) SQLITE_LOCKED error. However, if txn_state of the file is 0 then I expect that it would just be detached with no muss and no fuss no matter what the autocommit setting.

What I'm trying to accomplish is to allow reading from a specific point in time in the src database. Starting a read-transaction creates such a "snapshot".

I attach the dst database and starts writing things read from src. The database gets locked and blocks another connection to src writing data (that should not be vissible in my read transaction on src).

So, this usage patterns seems to be less useful as it does not seem to be possible to read from src at a specific point in time. The only solution I can think of is to use separate database connections for src and dst and take the overhead of transferring data in/out using sqlite's API.

I'm very interested if anyone has any other suggestions I could try.

The database gets locked

Insufficient specificity. WHICH database is locked? You have a connection which is attached (most of the time) to more than one database, the "main" database (that was opened with the connection), the "temp" database, plus any other databases that are attached.

blocks another connection to src writing data

Again, you have provided insufficient specificity. SQLite permits multiple simultaneous readers OR one writer. Unless the database file is in WAL mode -- however, since you cannot write on a separate connection, that would mean that you are not in WAL journal mode.

Sorry, I was unclear. The src database gets locked once I start writing to the attached dst database. The dst database is attached to a connection to the src database opened with mode=ro (as described in the first post of this thread).

Good point about journal_mode in dst. It was indeed in delete mode as it was created as part of the attach statement (mode=rwc). Unfortunately, it does not seem possible to create a database in wal mode using this method. Using PRAGMA journal_mode=WAL is, if I understand the documentation correctly, not possible to run on a specific attached database. And it is not possible to run the pragma once a transaction is started.

I presume by "src" you mean "main" -- ie, the databae that is opened with the connection? This is the database that need to be in WAL logging mode so that you can "write" to it from a connection while another connection is "reading" it. If it is not, then you will get an error when you try to commit any changes to the database on "the other, write, connection". The "dst" database does not need to be in WAL journal mode because it is being accessed by only one connection period.

Yes, correct. src is the main database opened with the connection. It is in WAL mode.

Other connection (only one!) to src (but opened with mode=rw) is blocked once writing to dst attached to the mode=ro opened src database.

I.e:

I have two connections to src, one with src.db?mode=rw and one with src.db?mode=ro.

I attache dst to the latter connection as dst.db?mode=rw.

I start the "intention" of a transaction with BEGIN TRANSACTION on the mode=ro src-connection. I read data from src and insert into dstusing the very same connection.

The mode=rw opened src-connection get blocked until I COMMIT TRANSACTION (or ROLLBACK TRANSACTION in my case as I'm mostly interested in a snapshot on the srcdatabase) on the mode=ro-opened src-database.

If this is the definition of "works perfectly" I guess I've to rethink my solution and give up this thing of attaching a mode=rw database to a mode=ro opened connection as it doesn't seem to play out well in the end.

If I get this working it would be nice if it was possible to have the transaction upgraded to a read transaction on src and read-write on dst for efficiency reasons when writing (still using the mode=ro-opened src database with the dstdatabased attached with mode=rw as already mentioned.

Unable to reproduce. I can only reproduce if the main database is in a journal_mode that is not WAL. I would suggest that you check that the main database is indeed in WAL journal-mode (and show your work).

Example:

>sqlite src.db
SQLite version 3.43.0 2023-06-12 15:44:01
Enter ".help" for usage hints.
sqlite> pragma main.journal_mode;
┌──────────────┐
│ journal_mode │
├──────────────┤
│ 'wal'        │
└──────────────┘
VM-steps: 6
Run Time: real 0.007 user 0.000000 sys 0.000000
sqlite>

I really appreciate all the feedback so far from you!

I will try to create a standalone test program replicating my issues. I'll be back! :)

If I get this working it would be nice if it was possible to have the transaction upgraded to a read transaction on src and read-write on dst for efficiency reasons when writing (still using the mode=ro-opened src database with the dstdatabased attached with mode=rw as already mentioned.

There is no such thing as "upgrading" to a read transaction. You may "commence" a read transaction, however, by "reading" from a database.

Also, you can "commence" a write transaction by writing. If you already have a read transaction in process, you may "upgrade it" by starting a write statement.

Note that transactions apply to DATABASE FILES and not the connection. Although BEGIN IMMEDIATE and BEGIN EXCLUSIVE will affect every database on the connection. You can, of course, attach your dst database AND THEN do a BEGIN IMMEDIATE. This will do nothing to immutable database files, explicity start a read-txn on read-only files with immediate effect, and start a write-txn on writable files.

If you were to issue the "begin immediate" before attaching the database, then the attached file will act as if "BEGIN DEFERRED" had been issued.

BEGIN (without IMMEDIATE or EXCLUSIVE) merely turns off the autocommit machinery. THe actual transaction level and affected databases are dependent on the read/write commands issued. Turning off autocommit merely stops the transaction from being committed or aborted when the statement ends. You must then re-enable autocommit using either COMMIT or ROLLBACK. The autocommit machinery will then be turned back on and when that statement (COMMIT or ROLLBACK) ends then autocommit machinery will either commit or rollback the changes in accordance with the command given.

Using PRAGMA journal_mode=WAL is, if I understand the documentation correctly, not possible to run on a specific attached database.

The documentation specifies no such thing. What it does say is that if you do not specify the database you want changed, then all attached databases will change -- and if one of them cannot be changed, then the command will fail.

If you specify which database you want to be affected, then only that database is affected.

https://sqlite.org/pragma.html#pragma_journal_mode

Yes, but unfortunately it doesn't make any difference if I put the dst in WAL mode after creating it.

Do I understand correctly that it doesn't play well if you want to use write transaction on the destination?

No, it works perfectly.

From the documentation I could not figure out what to expect if I start a transaction before attaching the dst database.

Why would you expect anything at all?

You have turned autocommit off, and then attached the file. There is no transaction in progress on that database file and none will be started until you "access" the database file, in which case the transaction mode will be in accordance with your "access".

If you read from the database file, a read transaction will be started. If you write to it a write transaction will be started. If you do not access it then no transaction will be started.

In simple terms, the attached database will operate as if you had turned off autocommit by starting a deferred transaction. Other types (immediate/exlcusive) only apply to database files which are attached at the time the command is issued. Commands given last month do not have any effect of database files attached this month unless you re-issue the command.

I'm a bit new to the API of sqlite so I'm pretty sure I miss a lot of the details even after reading the documentation multiple times.

IIRC, if initiate a read before attach the dst database, will the transaction then be started as a read transaction?

I'm not really following the second part of your last paragraph.

The issue I experience is that the src (mode=ro) database is locked once I start writing to the dst (mode=rw) database so another connection on src (mode=rw) is blocked.

From your wording it seems that it should work if I get the details right so I will re-check my test code.

The particular database which you are reading will be in a read transaction until the statement processing ends, at which time the autocommit machinery will terminate the transaction. Other databases attached to the same connection will not be in any transaction state, and any databases attached during the processing of the command that induced the read transaction will not have a transaction state.

To clarify things even further:

I execute a statement similar to INSERT INTO dst.table SELECT ... FROM src.table; (on the connection that opened src with mode=ro and then attached dst as mode=rw) with the intention that src should continue to be writable from the other connection (that is the only one that has opened src with mode=rw.