Can this code delete a user's existing database?

Hi gurus

I have an app that uses a database. One of my users claims that all his data is lost. When he sends me a copy of his database, it is just the schema with no data in it. User is sure he is using the correct computer, correct Mac User Account and has not reformatted/deleted/partitioned his drive etc

This app has been used since 2015, and nobody else has made this claim, but I am worried. My method for creating the database is largely copied from the Learning Xojo guide that I read when I was new. Pretty standard stuff, I would have thought.

My app looks for a folder within the Application Support folder:

dim f as FolderItem = SpecialFolder.ApplicationData f = f.child("MyAppFolder") if f.Exists = false then // does the folder exist ? f.CreateAsFolder() // then create it end if

It then looks for the database file within that folder:

MyDBFile = SpecialFolder.ApplicationData.Child("MyAppFolder").Child("MyAppDb.dbs") If MyDBFile.Exists Then //found it? MyDatabase = New SQLiteDatabase MyDatabase.DatabaseFile = MyDBFile If MyDatabase.Connect() Then Return True End If Else Return CreateDatabase //no existing database - so create it End If

The CreateDatabase method looks like this

[code]MyDBFile = SpecialFolder.ApplicationData.Child(“MyAppFolder”).Child(“MyAppDb.dbs”) //get the database from stored database
If MyDBFile <> Nil Then //if the database exists on the drive…
MyDatabase = New SQLiteDatabase //instantiate MyDatabase as a new sqlite database
MyDatabase.DatabaseFile = MyDBFile //load MyDatabase with the existing database

If MyDatabase.CreateDatabaseFile() Then //if we can create (or connect if it exists) to the database

If MyDatabase.Connect() Then  //connect
  CreateSchema // goes on to write tables etc
  Return True
Else
  Return False
End If

Else
Return False
End If
End If[/code]

I admit I am a little sketchy when it comes to the difference between

If folderItem.exists

and

If folderitem <> nil 

Is there any way this code can overwrite an existing database and thereby delete somebody’s existing data?

A folderitem is nil if you have no reference to it.

dim f as folderitem f.delete -> NilObjectException

If you have a folderitem then you can check if it is a real file or not:

dim f as folderitem = specialfolder.desktop.child("blabla") if f.exists then 'do something else 'we will end up here end if

The user is ALWAYS sure he/she/it hasn’t screwed up. Does he have a TimeMachine backup?

I’ve got him checking his TM backups now.

Logged in with a new account?
Specialfolder.sharedapplicationdata if multi-user

And I am hearing of oddness where Dropbox is installed

He claims he is using the same user account. I will get him to check the shared applicationdata directory as well.

Does this have a deeper location for codesigned/sandboxed mac apps, or is it just /Library/Application Support?

Not at my Mac currently, but i remember it beeing another/deeper Location under the Container Folder. Something like Container/MyAppFolder/…

But you can read all the details about this Folder here…

Has he ever had /is he now using a non-sandboxed version?

ApplicationData is a different place for sandboxed and non-sandboxed… he may have sent you a file created by a non-sandboxed version as that sll he is likely to be able to see.

Yep, I asked him that. He only ever used the Mac App Store version. I specifically directed him to the sandboxed location and it was a blank file.

Yep I know the deeper location for the sandboxed version for an Admin and Standard Mac user account. But I’m wondering about the location of a Shared user account?

What is the File Change Counter value of the empty db he sent you ?

I didn’t know there was such a thing. How do I access it?

Sorry, I cannot access to http://www.sqlite.com/. I will try the Xojo LR to see if I can find it.

Basically, when you use a sqlite db file you do not access that information; it is not stored in TABLES; it is SQLite who deals with that information.

I am searching…

Sorry, wrong URL. I found it:

https://sqlite.org/fileformat.html

search the entry: File change counter in the Database Header Format table.

Can I get some confirmation that my code can’t replace or overwrite an existing database? It will only create a new database if one does not already exist?

No, it does not do that.

I put your code in a Button (of an existing project) and a Breakpoint in Dim f As Folder… that allows me to follow the code in the debugger. The first run creates the db file, the second does not.

Thanks Emile. It has worked fine for 4 years, but one user claimed all his data was deleted. I suspect it was user-error, not an app bug.

Adda button and put code that follows the logic below:

OpenDialog the .sqlite file,

TF_File_Change_Counter.Text = Str(ReadBS.ReadUInt32),
Close.

Then, you will know the number of times that SQLite db file was changed.

The problem lies in the interpretation of the value.

I recently had a report telling the DB disappears. I asked questions and determined that that Windows 7 tower clock year was 2011. I had the good (or bad ?) idea to put the current year in the data base name (so at year’s end some process have to be done). So, the software, based on the clock value was searching a non existent db file (the software was created and started to be in use in 2013) with 2011 in its name. After a minute, I had the reason of the bug (a creative bug I could avoid using a special TABLE where I store the date of the created application, the .sqlite creation).

I never thought of this case, however possible: the proof is here !