[sqlite] SQLite on iOS with NSFilePresenter/NSFileCoordinator

[Arno Gramatke]

Hi all,

in an iOS app we have been storing multiple user „documents“ (hierarchical data mostly, but some larger blobs (~2MB) as well) in a single data base file, that was stored in the app’s "Application Support“ folder. That has worked without problems so far.

Since iOS 11 we would like to give the users the ability to access their documents through iOS’s new Files.app and from within other apps. Therefore we would like to move from the „one db files holds all documents“ approach to an approach where each db file only holds one document. So far so good.

These single files should be placed in the app’s „Documents“ folder, making them accessible from other apps or the Files.app when allowed by the user. With the single db file from above it was only ever our app to access this file and we wouldn’t have to worry about other processes trying to copy, move, delete the file. Now with the files stored in the „Documents“ folder this has changed and we can’t tell when another process tries to access the db files. For this Apple recommends using file coordination where each read or write is coordinated through an NSFileCoordinator.

Has anybody have experience how to approach this?

My first naive approach was to figure out, which commands will result in a read and which will result in a write to the db file. Then coordinate a read or write depending on the statement. For example, an INSERT statement will result in a write to the file (I guess). A SELECT might result in a read (I am not sure, there might some data already being cached?). It seems that processing each statement inside a coordinated read or write would lead to a lot of overhead and I guess this is not what the file coordination was made for. It rather looks like this was meant for infrequent writes to file or even file operations where the whole would be read or written in one go.

Another idea was to copy the db from the file into an in-memory database using file coordination, make all modifications in-memory and then copy the in-memory back to the file system.

Can anyone point me in the right direction? Maybe with a hint how Apple is doing this when working with Core Data and the SQLite storage?

Thanks
Arno

[Simon Slavin]

Post by Arno Gramatke
These single files should be placed in the app’s „Documents“ folder, making them accessible from other apps or the Files.app when allowed by the user. With the single db file from above it was only ever our app to access this file and we wouldn’t have to worry about other processes trying to copy, move, delete the file. Now with the files stored in the „Documents“ folder this has changed and we can’t tell when another process tries to access the db files. For this Apple recommends using file coordination where each read or write is coordinated through an NSFileCoordinator.
Has anybody have experience how to approach this?

As you’ve already figured out, there’s no easy way to encapsulate SQLite effects within NSFileCoordinator. To do it properly you’d have to write your own library or your own VFS which does both things. Neither of these things are easy and, unless you have most of your revenue from your iOS app, I don’t think either of them are work the effort of writing and debugging.

Post by Arno Gramatke
My first naive approach was to figure out, which commands will result in a read and which will result in a write to the db file. Then coordinate a read or write depending on the statement. For example, an INSERT statement will result in a write to the file (I guess). A SELECT might result in a read (I am not sure, there might some data already being cached?). It seems that processing each statement inside a coordinated read or write would lead to a lot of overhead and I guess this is not what the file coordination was made for. It rather looks like this was meant for infrequent writes to file or even file operations where the whole would be read or written in one go.

The internals of SQLite are a lot more complicated than this. Not only do you have to worry about the constant reading and writing of journal files, an INSERT can require a lot of reading (to figure out UNIQUEness requirementts) and a SELECT can require a lot of writing (if SQLite has to make up a temporary index). You would have to learn a lot about SQLite internals.

Post by Arno Gramatke
Another idea was to copy the db from the file into an in-memory database using file coordination, make all modifications in-memory and then copy the in-memory back to the file system.

This might be workable. Another alternative would be to keep the real SQLite database files in the App’s sandbox, as you have been doing all along, and to duplicate needed information into the shared areas when the App is backgrounded or quit, or when it is finished with the data for a single 'document'.

Do not forget that any user can delete anything in the shared space with a few accidental touches. If your App keeps its only copy of its data in there, users can delete the App’s data by accident, and you will then receive a support call asking why the App has lost all its data.

Simon.

[Jens Alfke]

Post by Arno Gramatke
My first naive approach was to figure out, which commands will result in a read and which will result in a write to the db file.

I think you’ll need to assume that any SQLite access can both read and write the file. So at a high level, you should use the NSFileCoordinator to request access before an activity that can cause queries or database writes.

If you want to avoid requesting write access too often, you could open the database read-only; then you’d know SQLite would only be reading. When you need to make a change you could open another handle with write access just for that transaction.

Post by Arno Gramatke
Another idea was to copy the db from the file into an in-memory database using file coordination, make all modifications in-memory and then copy the in-memory back to the file system.

Why not copy it on-disk? iOS 10.3 or later has the APFS filesystem, which is copy-on-write at the block level, making it extremely cheap to copy files. So the initial copy operation will be nearly instant and consume nearly zero disk space, and subsequent changes will use space only for the disk blocks that SQLite changes or adds. Likewise, copying the file back should be instant too.

—Jens

[Arno Gramatke]

Jens, Simon,

thanks for your feedback and suggestions. I will take a closer look at keeping a "shadow“ copy in the current location and copy the file to the Documents folder when needed. That seems to be a feasible approach, especially with what Jens wrote about APFS and its copy-on-write support. I’ve completely forgotten that. :-)

Thanks
Arno

Post by Jens Alfke

Post by Arno Gramatke
My first naive approach was to figure out, which commands will result in a read and which will result in a write to the db file.

I think you’ll need to assume that any SQLite access can both read and write the file. So at a high level, you should use the NSFileCoordinator to request access before an activity that can cause queries or database writes.
If you want to avoid requesting write access too often, you could open the database read-only; then you’d know SQLite would only be reading. When you need to make a change you could open another handle with write access just for that transaction.

Post by Arno Gramatke
Another idea was to copy the db from the file into an in-memory database using file coordination, make all modifications in-memory and then copy the in-memory back to the file system.

Why not copy it on-disk? iOS 10.3 or later has the APFS filesystem, which is copy-on-write at the block level, making it extremely cheap to copy files. So the initial copy operation will be nearly instant and consume nearly zero disk space, and subsequent changes will use space only for the disk blocks that SQLite changes or adds. Likewise, copying the file back should be instant too.
—Jens
_______________________________________________
sqlite-users mailing list
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[Peter Da Silva]

Post by Arno Gramatke
Jens, Simon,
thanks for your feedback and suggestions. I will take a closer look at keeping a "shadow“ copy in the current location and copy the file to the Documents folder when needed. That seems to be a feasible approach, especially with what Jens wrote about APFS and its copy-on-write support. I’ve completely forgotten that. :-)

Another question I have is... is an SQLITE database the best format for exposing your data to other applications?

[Richard Hipp]

Post by Peter Da Silva
Another question I have is... is an SQLITE database the best format for
exposing your data to other applications?

Yes, it's the best format. Peter, were you not paying attention
during my talk at the Tcl conference last week? :-)

[Peter Da Silva]

Post by Peter Da Silva
Another question I have is... is an SQLITE database the best format for
exposing your data to other applications?

Yes, it's the best format. Peter, were you not paying attention during my talk at the Tcl conference last week? :-)

I tried to open your message as an SQLITE database and got “Error: file is not a database”. Please advise.

[Jens Alfke]

Post by Peter Da Silva
Another question I have is... is an SQLITE database the best format for exposing your data to other applications?

Yes, it’s a common document format. Whether it’s “the best” is clearly dependent on the type of data and how it’s used. My opinion is that when the data set becomes large enough that it’s impractical to load it all into memory at once (and write it out in its entirety on every save), a database is the best option.

(Whether it’s the best data interchange format is another question. But IIRC in this case the developer isn’t exposing the data to other applications, rather it’s exposed to allow the user to copy the documents between devices.)

—Jens