Proper location of the DB file to be able to update it and the different locations on Iphone

Hi all,
I’m not really clear on few points which are linked to each others regarding the DB file I want to use for my app ( DB = SQlite )

First , If I look at Ios DB example, at “app Opening”, the following is run

// Copy the database from the app bundle to the Documents folder
// so that it can be modified.
Var fileName As String = "myDB.sqlite"
Var dbFile As FolderItem = SpecialFolder.Resource(fileName)
Var destFile As FolderItem = SpecialFolder.Documents.Child(fileName)

Q1 : This means that if I don’t copy it to Documents , it can’t be modifed at all ? ( read only in ressources folder ? )
Q2 : what is the “documents folder” on an Iphone ? is it the default one opened when you run the “files” app ?
Q3 : If Q2 true, then where should I put my DB just to avoid users being able to delete the file …

From another hand , on the build settings , a “copyDB” action is set :
image to copy the DB file to the ressources Folder.

I guess I’m missing something on the process itself as,
I have my DB file on my mac stored on my desktop . I edit it to add entries as ex.
When I run again the build, old version of the DB is used ( the one without the updates )

When App is run, A - file copied from Mac Desktop to Iphone Ressource folder, B : by code, file is copied from ressource to Document folder . C - DB is opened from the Document folder.
Q4 : Assuming all these folders are on the iphone according my understanding , what are the differences between

  • App Parent Folder
  • Resources folder
  • Framework Folder
  • Bundle Parent Folder
  • Content Folder

Which leads to one my previous question : where should I put the DB file ?
Thanks for clarifications

First of all, CopyTo fails if the file exists, so you’ll need to delete the previous one first. If you need to transfer data, you’ll need to do that in your app.

Yes, I handled this potential issue
exact code I’m using is this

// Copy the database from the app bundle to the Documents folder
// so that it can be modified.

Var fileName As String = "dc.sqlite"
Var dbFile As FolderItem = SpecialFolder.Resource(fileName)
Var destFile As FolderItem = SpecialFolder.Documents.Child(fileName)


Hence the fact I don’t understand how the “old” DB is still there …

I usually copy all app related files and databases into the SpecialFolder.ApplicationSupport folder.

Files in both ApplicationSupport and in Documents will be deleted when the user uninstalls the app.
The difference between the two is that you can configure the Documents folder in a way that the user can access it when connecting the device to a MacBook via USB. That’s not possible with ApplicationSupport.

Thanks @Christoph_Emrich , so I will use the Application support folder. I don’t want users to have access to the DB.
Which Folder correspond to Application Folder in Xojo ?
“Bundle parent” ?

No. You need to use the logic you presented in App.Opening to copy your original database from the resources folder to SpecialFolder.ApplicationSupport.

1 Like

As Thom said. Use the build step to copy the database to the Resources folder.
I believe, this is where your problem still exists:

I have the same use case: a db file called localization.db which I regularly update and then I want the app to use only the latest version without further modification in the app. The way I’m doing it is with an in-memory database. So I use the copy files build step as you do:
Bildschirm­foto 2023-03-30 um 09.24.27

And then in the app.opening event I use this code to create the in-memory database every time the app starts to make sure the app always uses the latest version I have on my Mac (languageDB is a global property):

//create the languageDB in-memory
var dbFile as folderitem = SpecialFolder.Resource("localization.db")
var tempDB as new SQLiteDatabase
tempDB.DatabaseFile = dbFile

languageDB = new SQLiteDatabase
tempDB.BackUp(languageDB, nil, -1)

Please note that right after that, since there is a bug in the Apple Simulator which doesn’t have the ApplicationSupport folder, I do this:

//check if the folder Application Support exists. If not create it.
dbFile = SpecialFolder.ApplicationSupport
if not dbFile.Exists then dbFile.CreateFolder

Hope this helps?

OMG. do not use in-memory databases in iOS. That’s an excellent way to ■■■■ off the user or worse, get your app killed for using too much memory or CPU use.

Remember, the newest iOS phones out there only have 6GB of RAM and a 6-core CPU. That means that the oldest supported ones out there have far less.

My biggest in-memory database I use is 623 KB. I don’t consider it a problem at this point.

Thanks @Thom_McGrath , I’m using your “SpecialFolder.ApplicationSupport.” method.
@Greg_O understood regarding memory DB; I was planning anyway to use a file as I will have many pictures in it ( to anticipate some questions, some will be stored as jpg files and I want to store some others in DB to avoid users to access them directly )

From another hand, please note that “SqlliteManagerTool” do not update the DB unless you save and overwrite the dbfile. This was my issue regarding the changes I was making without seeing them ( commit