Can this code delete a user's existing database?

  1. 4 months ago
    Edited 4 months ago

    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

    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

    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?

  2. Beatrix W

    Jul 11 Pre-Release Testers Europe (Germany)
    Edited 4 months ago

    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?

  3. I've got him checking his TM backups now.

  4. Jeff T

    Jul 11 Midlands of England, Europe

    One of my users claims that all his data is lost.

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

    And I am hearing of oddness where Dropbox is installed

  5. 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?

  6. Sascha S

    Jul 11 Pre-Release Testers, Xojo Pro Germany/W'haven
    Edited 4 months ago

    @Rob L /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...

  7. Jeff T

    Jul 11 Midlands of England, Europe

    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.

  8. 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.

  9. Edited 4 months ago

    @SaschaSchneppmueller but i remember it beeing another/deeper Location under the Container Folder. Something like Container/MyAppFolder/....

    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?

  10. Emile S

    Jul 11 Europe (France, Strasbourg)
    Edited 4 months ago

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

  11. Edited 4 months ago

    @Emile S 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?

  12. Emile S

    Jul 11 Europe (France, Strasbourg)

    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…

  13. Emile S

    Jul 11 Europe (France, Strasbourg)

    Sorry, wrong URL. I found it:

    https://sqlite.org/fileformat.html

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

  14. 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?

  15. Emile S

    Jul 11 Europe (France, Strasbourg)

    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.

  16. 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.

  17. Emile S

    Jul 12 Europe (France, Strasbourg)

    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 !

or Sign Up to reply!