App Sandbox SQLite databases and other associated files

[Sam_Rowlands]

@347558:@Dave S

implement a “most recent used files” system, as per Sam (my understanding) requires “Security Scoped ‘Bodgemarks’” or jumping thru other even more complicated and “fragile” methods

Noooooooooo… Please don’t misunderstand me, you should never ever use Security Scoped Bodgemarks (unless you like pain). Apple have a recent items API which works much more reliably, the code is available as part of my Sandbox Kit.

@347558:@Dave S

Implement read/write on a SQLite Database (from an external source) since there is no control over where/how the journal files (especially WAL) are maintained, and this violates “sandbox” policy

This technique works https://forum.xojo.com/conversation/post/338534 also does moving the database into the application container (although hard linking is no longer viable from a Sandboxed application under High Sierra).
The final way is to duplicate the database and then disable the journal file. This way if the ■■■■ hit the fan, you can at least revert back to previous version. Just ensure that you don’t copy an already corrupted database.

Shame there’s no way to redirect the journal file to a location that your application has read/write capability.

[Norman_P]

Sqlite docs literally state http://sqlite.org/tempfiles.html
2.4. Master Journal Files
The master journal file is always located in the same directory as the main database file (the main database file is the database that is identified in the original sqlite3_open(), sqlite3_open16(), or sqlite3_open_v2() call that created the database connection) with a randomized suffix.

And the only API I can find that would allow you (or us) to dictate that temp files should be else where is
http://sqlite.org/pragma.html#pragma_temp_store_directory
which is deprecated and discouraged

Which probably accounts for apple having fixed it for their tool chain using their api’s - they supplanted whatever sqlite does for temp file with their own

[DaveS]

@347558:@Dave S

So basically, it sounds like, while using the features and functions currently supplied via Xojo, one cannot

• I do not have resources to upgrade Xojo to 2017, let alone invest in other 3rd party tools for an App that I HAD HOPED would be better embraced than I have observed… especially when I have no clue if that 3rd party tool is compatible with my goals or not.
• “moving the database” is not an option… the database could exist anywhere on the users local computer, or LAN, is NOT owned by the application in question, and might in fact be large enough that a significant amount of time and/or storage space might be involved. And if I remember from my testing, this only adversely affects a Database using WAL journalling

So again… per the initial statement in my previous post :

@347558:@Dave S

So basically, it sounds like, while using the features and functions currently supplied via Xojo, one cannot

[Oliver_Osswald]

The behavior of creating temporary files is designed by the SQLite author and sandbox limitations is something Apple is in charge with. So what exactly could Xojo do in this case to make life easier for us?

What I do when a user wants to open a sqlite seminar database file with my app (seminar.pro) then I show a dialog, tell that the db will be copied to the container with a continue or abort button.

I understand that this is not ideal for a general sqlite editor…

I wonder how Marco Bambini is managing this, he has his SQLite Manager 4 also on the App Store.

[Emile_Schwarz]

SQLite source code is available, isn’t it ?

Edit: Typo.