Support for NSSQLitePragmasOption generally, WAL specifically

[patrickhartling]

Has anyone worked on adding support for NSSQLitePragmasOption, particularly so that write-ahead logging (WAL) can be enabled? I have tried but am having only limited success. It's not so hard to extend ECD to execute the PRAGMA SQLite statements from the dictionary associated with NSSQLitePragmasOption in the persistent store options. Migrating between managed object model versions is proving terribly difficult, however.

The one (more or less) related Stack Overflow post I ran across has a down-voted answer that indirectly refers to an Apple Technical Q&A. That Q&A post doesn't strike me as being truly relevant, particularly because the approach of disabling WAL before migrating does not make any difference for me. As far as I can tell, Apple's Core Data code is fully WAL-aware as long as the right options are supplied consistently. The challenge lies in ensuring that ECD handles WAL correctly.

It seems very much like the EncryptedStore object has to be deallocated to prevent possible corruption of the -wal file and/or the encrypted store by the EncryptedStore object allocated for the newer version of the managed object model. That seems sensible to me, but there is an unexpected wrinkle. When I do ensure that the EncryptedStore object is deallocated, the -wal and -shm files are removed. Is that a normal side effect for sqlite3_close()? I'm still working my way through the sqlcipher source to understand what to expect.

If I execute the same code using NSSQLiteStoreType instead of EncryptedStoreType, the -wal and -shm files remain on disk after I execute steps to remove the persistent store from its coordinator. Perhaps the NSIncrementalStore subclass for NSSQLiteStoreType cleans up after itself differently than EncryptedStore does?

[DanielBroad]

Hi Patrick, whats the result of this? Did you manage to get it working by just applying the pragmas?

[patrickhartling]

@DanielBroad I completely missed your reply. I was not able to get WAL working with ECD. I have changes on a branch, but I had to put it on hold due to time constraints.

[jeffdgr8]

Has anyone made any more progress on this? WAL journal mode seems to be a requirement for utilizing multiple persistent store coordinators, as described in this WWDC session (~27:50).

I created a sample app that illustrates the issue, where parallel operations on multiple persistent store coordinators causes the error: Error Domain=NSSQLiteErrorDomain Code=5 "(null)" UserInfo={EncryptedStoreErrorMessage=database is locked}, The operation couldn’t be completed. (NSSQLiteErrorDomain error 5.)

[jeffdgr8]

@patrickhartling are the changes you were working on pushed to an accessible branch?

[patrickhartling]

@jeffdgr8 Not at the moment. I do still have the branch, but it is at least two years out of date. I'm under the gun with work stuff right now, and it may be a few days before I can get it current again.

[jeffdgr8]

@patrickhartling ok, thank you. I would like to continue looking into this. Any code you have made progress on, even if outdated, would be helpful.

[jeffdgr8]

I added code to handle NSSQLitePragmasOption configuration options and tested with ["journal_mode" : "WAL"]. It seems to be working as expected, creating the -wal and -shm files alongside the .sqlite database file. It resolves the database locking errors I was experiencing using multiple persistent store coordinators.

@patrickhartling I tested the code with new model versions and found it working with the changes I tested, which included adding entities, adding attributes, and removing entities from the schema. What were the issues you were experiencing migrating between managed object model versions? Maybe the errors you were experiencing have since been resolved with updates to the library?

I can create a pull request for my changes if they look good to merge or make changes if you have any feedback.

[jeffdgr8]

There do seem to be issues still in my production app, using multiple persistent store coordinators. While the database locking errors are alleviated by the usage of the WAL journaling mode (I wasn't able to reproduce them in my sample app), they aren't completely eliminated. I'm still receiving the error: Error Domain=NSSQLiteErrorDomain Code=5 "(null)" UserInfo={EncryptedStoreErrorMessage=database is locked}, The operation couldn’t be completed. (NSSQLiteErrorDomain error 5.) in my production app. Using the default Core Data SQLite persistent store type, I don't get these errors.

[jeffdgr8]

I was able to resolve the locked database error completely by specifying a ["busy_timeout" : "60000"] in the NSSQLitePragmasOption as well as the WAL journal mode. I made a pull request with my changes: #300

[patrickhartling]

@jeffdgr8 That looks very similar to what I had added. If you were able to get it working reliably, then you got farther than I did!

pragmas.patch.zip

[jeffdgr8]

@patrickhartling yes, very similar! Glad to know we were on the same page. If you're still able to make use of the change, you might try it again and see if it's working now for you.

[patrickhartling]

@jeffdgr8 I will carve out some time for that. I've been wanting to put WAL to use for a long time, and I' be thrilled if your changes work for our use case.

[AKoulabukhov]

@patrickhartling hi, is there a chances to have SQLitePragmas support merged in the near future?

[patrickhartling]

@AKoulabukhov Maybe. I didn't end up having time to try out the change myself back in May, but I can try again next week. I'll make a reminder for myself.