4

I'm an experienced programmer, but this is my first MacOS app (on 10.15.2), which needs to read an sqlite db of the user's choice (potentially anywhere on the machine)

At first it wouldn't open the DB 'name.sqlite' file itself; but when I used NSOpenPanel to let the user select it, that worked.

But then the query failed because sqlite3 (via SQLite.swift) was trying to open 'name.sqlite-wal'

os_unix.c:43353: (0) open(/path/to/dbname.lrcat-wal) - Undefined error: 0

If I open the db using the command-line client, and pragma journal-mode=off then the app works fine, but I can't restrict the app based on that - most of the dbs have WAL journalling

I tried turning com.apple.security.app-sandbox false in app.entitlements, but that didn't help.

I tried moving the db to the Pictures folder and turning com.apple.security.assets.pictures.read-write true, but that didn't help.

The unix permissions for db and the containing folder (/tmp in my test) are both good.

Because allowing the user to select the db via the NSOpenPanel allowed me to open the db for reading, I assume there's a similar restriction on the creation of the '-wal' file for writing.

How can I get permission (ideally without bothering the user with details of what a wal file is) to create the file next to the db?

Edit: Following TheNextMan's suggestion about sidecar files, I searched again. The WWDC presentation he (guessing the pronoun) linked shows how to use CFBundleDocumentTypes to relate extra extensions with NSIsRelatedItemTypefor NSFilePresenter, but (as far as I know) I'm not going through that route. I've tried it but it hasn't helped

I also, armed with the "sidecar" search term, found Access sidecar files in a Mac sandboxed app, which looked good but didn't help.

Even better I found the four-year-old unanswered duplicate of my question (if I had enough rep I'd mark this as a duplicate), SQLite and Sandboxed OSX apps, which includes a quote from Apple documentation App Sandbox Design Guide, saying

Note: In the case of a SQLite journal file, beginning in 10.8.2, journal files, write-ahead logging files, and shared memory files are automatically added to the related items list if you open a SQLite database, so this step is unnecessary.

So apparently it should Just Work™. But it doesn't.

aa599
  • 51
  • 3
  • Catalina has additional privacy protections on some user data folders, maybe you are running foul of that. The `NSOpenPanel` bypasses this protection; but I would expect a prompt in the UI when attempting to create the .wal file. Is your app listed under System Preferences > Security & Privacy > Privacy > Files and Folders? There is a good [WWDC talk](https://developer.apple.com/videos/play/wwdc2019/701) about this; it discusses how you can specify "sidecar" files alongside the user specified ones while avoiding privacy issues. – TheNextman Dec 18 '19 at 16:49
  • Thanks. I'm just running the app from xcode at the moment - doesn't seem to be much point installing it until it works! - so I can't see it under System Preferences. – aa599 Dec 21 '19 at 15:46
  • I have only ever got this working by using a package for the document file. Like you I had to switch off journaling to get this to work under sandbox. – Warren Burton Dec 21 '19 at 16:14
  • We can only mark a question as duplicate if it has an answer, I'm afraid – TheNextman Dec 21 '19 at 20:45

1 Answers1

0

Thank you for this thorough question. I encountered today the same issue as you, fighting the macOS sandbox restrictions. Similar to you, I've already copied the user selected (NSOpenPanel) database to my apps temporary folder (FileManager.default.temporaryDirectory).
But still I was unable to query the database file: sqlite3_prepare_v2 always returned the os_unix.c:45340: (0) open(/var/folders/.../myDB.db-wal) - Undefined error: 0 message.

As the database already is located in the temporary folder, which definitely is writable by our app (as we copied the database file!), we can simply create the myDB.db-wal file ourselves:

let tmpLocation = /// URL of the SQLite *.db file in a writable directory
let walLocation = tmpLocation.deletingPathExtension().appendingPathExtension("db-wal")
try Data().write(to: walLocation)

Et voilà: opening the DB is working.

pd95
  • 1,999
  • 1
  • 20
  • 33