Where to put SQLite db on Desktop App

Databases
1

Hi,

For some reason I always have problems with folder items. I have a SQLite db I want to store with the application. However I can’t seem to figure out haw to get the Apps Path.

So how do I open a SQLite DB in the same folder as the application?

Thanks

2

GetFolderItem("") ?

But first, where do you place your application ?

If in the Application folder, you will be in troubles: no permission granted to write in the Application folder.

Edit
Added link:
http://documentation.xojo.com/index.php/GetFolderItem

3

What about a subfolder in the apps folder, like: ~/Applications/MyAppFolder/Data/SQLite file?

4

Drag the db into your project
Lets say your app is called ‘Banana’
When the app starts, if there is no folder called Banana in specialfolder.applicationdata, create one
If there is no db inside that, copy the one you have in resources to the banana folder

[code]dim f as folderitem
dim db as folderitem
f = specialfolder.applicationdata.child(“Banana”)
if not(f.exists) then
f.createasfolder
end if

db= f.child(“Mydb.sqllite3”)
if not (db.exists) then
//copy or create the db file here
end if[/code]

If you try to keep the writable file in Applications, OSX wont like it
If you try to keep a writeable file in Program Files on Windows, Windows wont like it

5

Change your mindset like GetFolderItem doesn’t exist.
I just described why merely an hour ago here: https://forum.xojo.com/49851-text-file-folderitem-problem

If you want to access files copied into your app via a CopyFiles step you should choose the route that works best for you. If you’re using the namespaced framework the FolderItem there has GetResource. If you’re using the classic framework, use TPSF.Resources: https://github.com/devtimi/tpsf

Note that connecting to SQLite with Xojo creates temporary files, so you cannot connect to a database that is in your app’s resources on Mac, you need to copy it somewhere first. If you’re only reading, I would recommend copying to SpecialFolder.Temporary, if you need the data later copy it to TPSF.AppSupport (which does as Jeff describes on both platforms magically for you).

6

I just remembered, I have an app that writes to a configuration file in the apps folder. So it can’t be read only.

7

You are right. Take the other way: try to move the file oiut of the Applications folder (on MacOS): what happened ?

8

Doh ! The file was not moved: an alias file was created elsewhere !

More seriously: read the used OS to know what they said about that.

9

It may be working for now, but don’t count on it. Configuration belongs in SpecialFolder.ApplicationData. User data belongs either there or in Documents, depending on whether you want the user to be able to access the file outside of your app.

10

Good advice. A note to add though:
Don’t just save to Documents. If your user needs to access the data outside the app, use a SaveAsDialog. If the app doesn’t need to allow the user access the data (like a shoebox app), store it in SpecialFolder.ApplicationData.

Treat the user’s system like you’re walking into the home of someone with OCD.
Just because you don’t care if things are a mess doesn’t mean they share that attitude.

11

Very good advice.

12

Hi,

I’m trying to use TPSF as @Tim Parnell recommended and getting an exception, here is the code:

Dim dbFile As FolderItem
dbFile = SpecialFolder.GetResource("ASPEdata.sqlite")

If Not TPSF.AppSupport.Child("ASPEdata.sqlite").Exists Then  ' <<<< Getting NilObject Exception
  // Only copy the file if it is not already there
  Dim dest As FolderItem = TPSF.AppSupport
  dbFile.CopyFileTo(dest)
End If

I fave the build step copy files putting the DB in the resource folder. What am I doing wrong?

Thanks

13

What’s the exception? The TPSF module will give you the folder items, but it is not responsible for checking the validity of if they exist (the same way SpecialFolder works). If this is the first time you’re using the AppSupport folder, check that it exists and create it if not.

The TPSF.AppSupport folder gets you a FolderItem for the child of SpecialFolder.ApplicationData that is yours, but doesn’t create it. I suppose that could be documented a little better in the readme.

In App.Open you could:

[code]if TPSF.AppSupport.Exists = false then
TPSF.AppSupport.CreateAsDirectory

end
[/code]
and that’s all you’ll need to set it up :slight_smile:

14

There’s an “Application Support” Folder.

15

Under ~/Library

16

I may have edited my post to make it more detailed while you were posting. I think I covered why the exception is happening, and the example code should get you up and running :slight_smile:

The TPSF.AppSupport FolderItem is the child of SpecialFolder.ApplicationData that is yours. It’s a small convenience so that you don’t have to keep doing .Child(“MyApp”)

17

Yea because I put this line, to read the value, AppSupDB = TPSF.AppSupport.

Its looking for Macintosh HD:Users:rich:Library:Application Support:org.aspe.tablesdt

That should fix it.

Thanks

18

@Tim Parnell

It’s TPSF.AppSupport.CreateAsFolder, is this platform specific?

19

No, CreateAsFolder is a FolderItem method available to all platforms.