Sandboxing issue : sandbox and SQLite Database

Sbastien

Check out Thom’s answers above. They should solve your problem.

I’m going to but heads with Thom here - but avoid SSBs if you can! They have caused myself and others so much pain due to incomplete implementation. There are other ways around this which I’ve posted to this very forum.

That’s cool, Sam.

Can you provide the link to the topic?

No, it is a SQLite behavior. There are journal files written to disk next to the database whenever you write to it. Your app has permission to write to the database location, but not the journal locations. You have a few options though.

  1. When opening the document, copy it into your app support folder. You can write there all you like. When the user saves (or whenever you feel appropriate) copy the file back to the user’s location. This gives you the advantage of supporting a revert option, since all work is done on a copy of the database.

  2. Switch your document to a bundle and store your database inside the bundle. When the user selects a file, you’ll actually have permission to the entire folder and you can write whatever you need anywhere inside the bundle.

  3. Don’t use a database. Probably not an acceptable option.

See, I’ve had nothing but perfect luck with them.

[quote=32873:@Simon Berridge]That’s cool, Sam.

Can you provide the link to the topic?[/quote]
https://forum.xojo.com/3428-sandboxing-done-right-recent-items

The easiest way is to use NSDocumentController to manage the recent items menu as any file added to this menu automagically gets it’s file access saved and restored.

[quote=32877:@Sam Rowlands]https://forum.xojo.com/3428-sandboxing-done-right-recent-items

The easiest way is to use NSDocumentController to manage the recent items menu as any file added to this menu automagically gets it’s file access saved and restored.[/quote]
I think the reason I’m having luck is that the app I’m working on as no use for the Recent Items menu.

[quote=32875:@Thom McGrath]No, it is a SQLite behavior. There are journal files written to disk next to the database whenever you write to it. Your app has permission to write to the database location, but not the journal locations. You have a few options though.

  1. When opening the document, copy it into your app support folder. You can write there all you like. When the user saves (or whenever you feel appropriate) copy the file back to the user’s location. This gives you the advantage of supporting a revert option, since all work is done on a copy of the database.

  2. Switch your document to a bundle and store your database inside the bundle. When the user selects a file, you’ll actually have permission to the entire folder and you can write whatever you need anywhere inside the bundle.

  3. Don’t use a database. Probably not an acceptable option.[/quote]
    You know what… scratch all this. The app I’m developing uses SQLite databases with SSBs without any issue at all. So my advice doesn’t really apply.

Indeed using SSBs as recent items also has further issues.

By using the NSDocumentController, you don’t have to use any SSB. NSDocumentController manages access for all files in the Recent Items menu. Simply store the URL to your file in NSDocController and forget about it.

I must not let my emotion run away with me, but SSBs, have left a nasty scar and I will do anything to avoid using them in my own apps.

Anyway back on topic, I seem to recall that there is a way to disable journalling of a DB, but I don’t recall what it is.

Ok so, I changed my code like this :

[code]function createDatabase(f As FolderItem)
{
db = new SQLiteDatabase
if(db.connect) then
db.databaseFile = f
db.DatabaseFile.ExtensionVisible = true
db.EncryptionKey = encryptionKey

if( db.CreateDatabaseFile and db.Connect ) then
  db.SQLExecute("MY SQL REQUEST")
  db.Close
  return true

else
  Return false
end if

}
else return
}[/code]

And in this situation I have no error on the line

db.databaseFile = f 

But I get the error 1 : no such table:REAL__SQL__TEMP__TABLE on this line :

if( dtb.CreateDatabaseFile ) then

I tried to create my file in the ApplicationData folder but got the same error…

Your “orders of operation” are wrong. Given your code above, your database is pretty much sitting in “undefined” mode since your 4th line will always result in FALSE (unless you have all your db parameters set somewhere else outside of this function).

function createDatabase(f As FolderItem)
{
    db = new SQLiteDatabase
  
    db.databaseFile = f 
    db.DatabaseFile.ExtensionVisible = true
    db.EncryptionKey = encryptionKey

    if db.databasefile.exists = false then
       db.CreateDatabaseFile
    end if

    if db.connect = true then
       db.SQLExecute("MY SQL REQUEST")
       db.Close
       return true
    else
       return false
    end if
}

Note that since you close the db right after your sqlexecute statement, your database will be closed. Also, make sure you commit your changes to the database before closing. I often keep my database open while my app is active and have it closed when closing the last window/app. That way, I don’t have to keep track of things like: “hmmm, did I forget to open/close” my database connection?

With this order I always get the error 1 : Operation cannot be completed because the database is closed on the line

dtb.databaseFile = f

And the error 14 : unable to open database file when I try to create a table…

:frowning:

Could this be related to the temporary files created by SQLite itself: http://www.sqlite.org/tempfiles.html

And maybe this has some additional info: http://sqlite.1065341.n5.nabble.com/sqlite-and-Mac-App-Store-sandbox-td67752.html

Next thing for you to try is to test outside of Sandbox (if you haven’t already) and see if you are still getting the error. That should narrow things down quite a bit. I have apps in the Mac App Store that uses the sqlite database and had no issues. Of course, all my apps are old so they still use “RealSQLDatabase” instead of the new “SQLiteDatabase”. In any case, there should be no issues writing to the data directory as you had already shown that you can write to there. The temp files everyone is talking about should also be created there too – and therefore, should have no problems writing there as well.

On a related note, all since all of my data connections are active while my apps are running, they all create a temporary file that sits next to the actual database file (in a sandbox environment) and goes away when I commit and close my database at the end of the run. Try switching SQLiteDatabase to REALSQLDatabase – just for sh*ts & giggles. I’m now curious…

Your Error 1:no such table is because the db.connect statement is before you tell the db that it has a file associated. This means that the db.connect has created the table in memory, not on disk.

Also, you are creating different instances if the db with the ‘new SQLiteDatabase’ statement in each function. You should really create a global property for the database and instantiate it when the app opens or when the user asks to create a new one. Don’t close the db until the end of the app.

To disable the on-disk journal, use PRAGMA journal_mode = MEMORY; once connected to store the journal in RAM. The downside to this is that if the app crashes mid-transaction, the database is very likely to become corrupt. There is no way to relocate on-disk journals, so when sandboxed, MEMORY and OFF are the only acceptable values. But since OFF disables the ability to rollback transactions, I’d consider that a showstopper.

So my previous solutions do apply. I’ll continue to see if there are any other tricks.

Looks like NSFileCoordinator was designed for this sort of thing. Sam, any experience with this class?

https://developer.apple.com/library/mac/documentation/Foundation/Reference/NSFileCoordinator_class/Reference/Reference.html#//apple_ref/doc/uid/TP40010585

[quote=32888:@SbastienAngot]Ok so, I changed my code like this :

[code]function createDatabase(f As FolderItem)
{
db = new SQLiteDatabase
if(db.connect) then
db.databaseFile = f
db.DatabaseFile.ExtensionVisible = true
db.EncryptionKey = encryptionKey

if( db.CreateDatabaseFile and db.Connect ) then
  db.SQLExecute("MY SQL REQUEST")
  db.Close
  return true

else
  Return false
end if

}
else return
}[/code]

And in this situation I have no error on the line

db.databaseFile = f 

But I get the error 1 : no such table:REAL__SQL__TEMP__TABLE on this line :

if( dtb.CreateDatabaseFile ) then

I tried to create my file in the ApplicationData folder but got the same error…[/quote]
Try this:

[code]function createDatabase(f As FolderItem)

db = new SQLiteDatabase
db.databaseFile = f
db.DatabaseFile.ExtensionVisible = true
db.EncryptionKey = encryptionKey
if db.CreateDatabaseFile then
db.SQLExecute(“MY SQL REQUEST”)
db.Close
return true
else
Return false
end if
[/code]

Thanks Frederick, I already tried that! Thanks Thom, Paul and everybody for your answers!

Finally, I think the issue is due to the creation of temporary files by SQLiteDatabase (or RealSQLDatabase)… This library tries to create a temporary database but cannot succeed because of sandboxing…

Now, I have to find a way to let SQLiteDatabase create its temporary files it needs :slight_smile:

I’ve found some good ways to explore in the doc you give me Paul, thank you!