Android Question How to replace SQLite database after installation?

[rlocke]

Hi all,

When my app is initially installed, it comes with a SQLite database. I'm working on code that allows end users to replace this SQLite database after the app has been installed - I would have them download and save the new database to the device's "Downloads" folder. During the development phase, I'm able to replace the database in the Files tab of the IDE, and this functionality would be for end users.

So far, my code is able to browse the Downloads folder, and then this code runs:

Private Sub cmdImport_Click
   
    Dim ImportDBName As String = "data2.db"    'the new database must be named "data2.db"
   
    If File.Exists(File.DirRootExternal  & "/Download/", ImportDBName) = True Then
        Log("Located " & ImportDBName & " in Download folder - begin importing process")
       
       
        Try
            Dim OutStr As OutputStream = File.OpenOutput(File.DirInternal, "data2.db",False)                        'the name of the imported file
            Dim InStr As InputStream = File.OpenInput(File.DirRootExternal & "/Download/", ImportDBName)        'the source file
            File.Copy2(InStr,OutStr)
            OutStr.Close
           
            'MsgboxAsync("The database has been successfully imported.", "Import complete")
           
            If File.Exists(File.DirInternal, "data.db") = True Then
                File.Delete(File.DirInternal, "data.db")
                Log("Previous version of data.db was deleted from internal storage.")
               
                Log("Found data.db in internal storage (just copied)")
                MsgboxAsync("The database has been successfully imported. Please quit and restart the app to load the new database.", "Success")
                'db.Initialize(File.DirInternal, "data.db", False)
            Else
                Log("data.db not found in internal storage!")
            End If
           
        Catch
            MsgboxAsync(LastException.Message, "Import DB Error")
            Log(LastException)
        End Try
           
        'File.Copy(Storage.SelectDir(False), "new.db", File.dirinternal, sourceDB)
        'MsgboxAsync("DB copied","Ok")
    Else
        MsgboxAsync(ImportDBName & " not found in Download folder", "Source database not found")
    End If
   
End Sub

The original database is named "data.db", and for testing purposes the new database has to be named "data2.db" and in the "Downloads" folder.

The code above seems to delete the original "data.db", but when I quit and restart the app, "data2.db" isn't copied to DirInternal - the original "data.db" is always copied from the Directory Assets location.

I'd prefer not to run a SQL script to update/insert rows from "data2.db" to "data.db", because as the database grows, there'll be a lot more tables and it'd be easier to completely replace the database.

My two questions:

1. Is it possible to completely replace the SQLite database? And if so, what am I doing wrong? If not, what are some other suggestions to accomplish updating the database?
2. I put my database initialization code in the Starter class - is that where it goes? Or should it be in the Main class (Activity_Create)?

Thank you!

 

[rlocke]

I think I got it working, in the Starter class, I updated the SQL initialization code to:

If File.Exists(File.DirInternal, "data2.db") = True Then
        Log("data2.db exists!")
       
        File.Delete(File.DirInternal, "data.db")
        File.Copy(File.DirInternal, "data2.db", File.DirInternal, "data.db")
        db.Initialize(File.DirInternal, "data.db", True)
       
        File.Delete(File.DirInternal, "data2.db")
    Else
        'check if the database already exists
        If File.Exists(File.DirInternal, "data.db") = False Then
            'copy the default DB
            File.Copy(File.DirAssets, "data.db", File.DirInternal, "data.db")
       
            'if not, initialize it
            db.Initialize(File.DirInternal, "data.db", True)
       
            Log("DB does not exist, copying it to DirInternal")
        Else
            'if yes, initialize it
            db.Initialize(File.DirInternal, "data.db", False)
            Log("DB already exists - initialized")
        End If
    End If

Just wanted to double check that the Starter class (Service Create) is where the database initialization code goes? I just want to make sure that after data.db is copied to DirInternal, that it won't be deleted and replaced with the original database (data.db) from DirAssets (otherwise users would lose any data saved to the database).

 

[Erel]

Just wanted to double check that the Starter class (Service Create) is where the database initialization code goes?

Yes, if you chose to waste your time with activities. New projects should use B4XPages and in such case the initialization goes into B4XPage_Created of the main page.

I just want to make sure that after data.db is copied to DirInternal, that it won't be deleted and replaced with the original database (data.db) from DirAssets

Your code is correct but can be simplified:

If File.Exists(File.DirInternal, "data2.db") = True Then
        Log("data2.db exists!")
        File.Delete(File.DirInternal, "data.db")
        File.Copy(File.DirInternal, "data2.db", File.DirInternal, "data.db")
        File.Delete(File.DirInternal, "data2.db")
    Else
        'check if the database already exists
        If File.Exists(File.DirInternal, "data.db") = False Then
            'copy the default DB
            File.Copy(File.DirAssets, "data.db", File.DirInternal, "data.db")      
            Log("DB does not exist, copying it to DirInternal")
        Else
            Log("DB already exists - initialized")
        End If
    End If
   db.Initialize(File.DirInternal, "data.db", False)

 

[rlocke]

Thank you @Erel !