Open database read-only

Hi,

I'm trying to open a database read-only (as it's 'locked' from the Finder).
Judging from the documentation, if I set the readonly config option on a DatabasePool it will open it in readonly non-wal mode.
Unfortunately, for both DatabasePool and DatabaseQueue, I get the following errors:

**[logging-persist] os_unix.c:43353: (0) open(/Users/remco/Desktop/Untitled V2.sqlite-wal) - Undefined error: 0**
**[logging-persist] unable to open database file in "SELECT * FROM sqlite_master LIMIT 1"**

I use the following code to open:

    var config = Configuration()
    config.readonly = true

    let db=try DatabaseQueue(path: url.path, configuration: config)

How can I make sure that a database is opened in read-only mode?

Thanks in advance.

Kind regards,

Remco Poelstra

I should add that the error in os_unix.c is probably caused by the sandbox, which blocks my app from creating files, as the Desktop is not in the sandbox, only the URL to open.
This of course increases the need to open the file in real readonly mode.

Hello @remcopoelstra,

Judging from the documentation, if I set the readonly config option on a DatabasePool it will open it in readonly non-wal mode.

This is true: DatabasePool won't activate the WAL mode if set to readonly.

However, the WAL mode is a persistent attribute of the database itself. And SQLite will not open a readonly connection on a WAL database unless the companion -wal and -shm files are present. This is really unfortunate, but clearly documented there.

If you control the applications that create and write into the database file, then you can make sure the -wal and -shm files are always present, so that other processes can open readonly connections. This is done by setting the SQLITE_FCNTL_PERSIST_WAL flag (see https://github.com/groue/GRDB.swift/blob/master/Documentation/SharingADatabase.md#use-a-database-pool). This will lift restrictions at the SQLite level, but maybe macOS will create further problems: I don't know yet.

On the other hand, if you do not control all processes that create and write into the database file, then your application must be ready to be given WAL databases without -wal and -shm companions. In this case, there is no way out: readonly accesses will fail, and an error should be reported to your users.

@remcopoelstra, will you please share your conclusions, after you have closed this topic of your application? I quite hope we can gather and make public as much experience as possible about the SQLite WAL mode on Apple platforms. It's a complex topic, not easily handled, with obscure failure modes, and generally speaking a real time sink, even for expert users.

Thanks for your attention. I’m looking forward to your reply.

Hi,

Thanks for your elaborate answer.
In principle the database if fully under my app's control, but I sometimes also use DB Browser for SQLite to inspect the file contents and I might expect my users will one day do the same :slight_smile:
I create the database using

let databaseQueue=try DatabaseQueue(path: url.path)
try database.backup(to: databaseQueue)

Which always writes to an empty database. The main database is a WAL mode database (in a temporary location), but I want the file that the user sees to be single, so I always copy it. I was under the assumption that, as I create the database using DatabaseQueue, that the resulting file would not have WAL mode. But even if I open the file readonly in the DB Browser I see WAL mode is still enabled.

Is there a way to make sure the backup database will have WAL mode off?

Indeed, DatabaseQueue does never activate the WAL mode. But...

The main database is a WAL mode database (in a temporary location)

... maybe the SQLite backup copies the WAL flag? I don't know, so let's make sure:

Can you please run this code after the backup has completed, and report the value of journalMode?

let databaseQueue = try DatabaseQueue(path: url.path)
try database.backup(to: databaseQueue)
let journalMode = try databaseQueue.read { db in
    try String.fetchOne(db, sql: "PRAGMA journal_mode")
}

Fwiw, the docs on WAL mode, under overview -> disadvantages -> point 5, suggest that recent versions of SQLite can open a wal database in read-only mode without the companion files by setting the immutable flag when opening the connection.

That returns a nice 'wal'.
But, as I'm at that point the only connection to the database, I assume it's doable to turn it off before the other files are created.
I'll investigate this tomorrow.

Thanks for pointing this out, @ahti, as I glossed over it until now. The immutable flag can indeed help when:

the underlying database file is held on read-only media and cannot be modified, even by another process with elevated privileges.

Since @remcopoelstra wants to open a database that is "locked" in the Finder, this should apply!

Hi,

If I add the following code after writing the database:

try databaseQueue.inDatabase { db in
            try db.execute(sql: "PRAGMA journal_mode = DELETE")
}

Then I can indeed open the resulting file even if it's locked.

I also tried setting the immutable flag (as a fall back option), but could find no other way than using the URI syntax. This doesn't seem to work though.

My bad.
I passed the URL using the 'path' property, which skips the query parameters. If I use the 'absoluteString' property then it works just fine!

Good to know! Thanks for your continued feedback, @remcopoelstra :+1:

Yes, SQLite interprets paths as URIs if they start with "file:". I haven't bothered exposing this in Swift...

Well, basically you did, didn't you? I can make good use of it now.
Maybe a hint in the documentation that this is possible might help others?

It's delicate. There is no purpose documenting all SQLite uses cases from GRDB. There are just too many. And they are already documented on the SQLite web sire.

My job is to enable them, so that no user ever feels "trapped" in GRDB.

When I (subjectively) think a use case is common during the development of GUI apps, then it exactly fits the focus of GRDB, and is granted with doc and sometimes tailored Swift apis. Experts use case are not, but discussions like those are an invaluable resource for future readers :-)