Android Code Snippet Backup & Restore SQLite using SaveAs

This code snippet is based on tutorial [B4X] TextEditor - Save and load external files here. This is my answer to this question.
Instead of writing a text file, we can make use of the same idea to backup and restore our SQLite database.
Check post #4 for restore database.

Sub Class_Globals
    Private Root As B4XView
    Private xui As XUI
    Private SQL1 As SQL
    Private FileHandler1 As FileHandler
    Private LblMessage As B4XView
End Sub

Public Sub Initialize
'    B4XPages.GetManager.LogEvents = True
End Sub

Private Sub B4XPage_Created (Root1 As B4XView)
    Root = Root1
    Root.LoadLayout("1")
    FileHandler1.Initialize
    If File.Exists(File.DirInternal, "data.db") = False Then
        File.Copy(File.DirAssets, "sample.db", File.DirInternal, "data.db")
    End If
    SQL1.Initialize(File.DirInternal, "data.db", False)
End Sub

Private Sub BtnBackup_Click
    'DateTime.DateFormat = "yyyy-MM-dd HH:mm:ss"
    'SQL1.ExecNonQuery2("UPDATE info SET modified = ?", Array As String(DateTime.Date(DateTime.Now)))
 
    If File.Exists(File.DirInternal, "backup.db") Then File.Delete(File.DirInternal, "backup.db")
    Dim BackupFilePath As String = File.Combine(File.DirInternal, "backup.db")
    SQL1.ExecNonQuery2("VACUUM INTO ?", Array As String(BackupFilePath))
 
    DateTime.DateFormat = "yyyyMMddHHmmss"
    Dim timestamps As String = DateTime.Date(DateTime.Now)
    Dim sf As Object = FileHandler1.SaveAs(File.OpenInput(File.DirInternal, "backup.db"), "application/vnd.sqlite3", timestamps & ".db")
    Wait For (sf) Complete (Success As Boolean)
    If Success Then LblMessage.Text = "File saved successfully" Else LblMessage.Text = "File failed to save"
End Sub

Don't miss the manifest editor.

'FileProvider
AddApplicationText(
  <provider
  android:name="android.support.v4.content.FileProvider"
  android:authorities="$PACKAGE$.provider"
  android:exported="false"
  android:grantUriPermissions="true">
  <meta-data
  android:name="android.support.FILE_PROVIDER_PATHS"
  android:resource="@xml/provider_paths"/>
  </provider>
)
CreateResource(xml, provider_paths,
   <files-path name="name" path="shared" />
)

 

[hatzisn]

Great example. Can you post an example on how to restore the DB using the Load Funtion of FileHandler.bas ?

 

[hatzisn]

I forgot to mention that I tried by getting the results of LoadResult and using a File.Copy but It was not enough. Obviously.

 

[aeric]

I don't think anything special need to do.
Maybe change the MIME type to allow loading the backed up db.

Public Sub Load As ResumableSub
    Dim cc As ContentChooser
    cc.Initialize("cc")
    cc.Show("application/*", "Choose sqlite file")
    Wait For CC_Result (Success As Boolean, Dir As String, FileName As String)
    Dim res As LoadResult = CreateLoadResult(Success, Dir, FileName)
    If res.Success Then ExtractInformationFromURI(res.FileName, res)
    Return res
End Sub

Private Sub BtnRestore_Click
    Wait For (FileHandler1.Load) Complete (Result As LoadResult)
    HandleLoadResult(Result)
End Sub

Private Sub HandleLoadResult (Result As LoadResult)
    If Result.Success Then
        Try
            SQL1.Close
            If File.Exists(File.DirInternal, "data.db") Then
                File.Delete(File.DirInternal, "data.db")
            End If
            File.Copy(Result.Dir, Result.FileName, File.DirInternal, "data.db")
            LblMessage.Text = "File restored successfully"
            SQL1.Initialize(File.DirInternal, "data.db", False)
            ReadTextFromDB
        Catch
            LblMessage.Text = "File failed to restore"
            Log(LastException)
        End Try
    End If
End Sub

 

[hatzisn]

I don't think anything special need to do.
Maybe change the MIME type to allow loading the backed up db.

Public Sub Load As ResumableSub
    Dim cc As ContentChooser
    cc.Initialize("cc")
    cc.Show("application/*", "Choose sqlite file")
    Wait For CC_Result (Success As Boolean, Dir As String, FileName As String)
    Dim res As LoadResult = CreateLoadResult(Success, Dir, FileName)
    If res.Success Then ExtractInformationFromURI(res.FileName, res)
    Return res
End Sub

Private Sub BtnRestore_Click
    Wait For (FileHandler1.Load) Complete (Result As LoadResult)
    HandleLoadResult(Result)
End Sub

Private Sub HandleLoadResult (Result As LoadResult)
    If Result.Success Then
        Try
            SQL1.Close
            If File.Exists(File.DirInternal, "data.db") Then
                File.Delete(File.DirInternal, "data.db")
            End If
            File.Copy(Result.Dir, Result.FileName, File.DirInternal, "data.db")
            LblMessage.Text = "File restored successfully"
            SQL1.Initialize(File.DirInternal, "data.db", False)
            ReadTextFromDB
        Catch
            LblMessage.Text = "File failed to restore"
            Log(LastException)
        End Try
    End If
End Sub

View attachment 168000

I have done the same in the morning and I have got "File does not exist". I will try it also with your example.

 

[hatzisn]

Your code works perfectly. I will try tomorrow to see where I was wrong.

 

[aeric]

I have done the same in the morning and I have got "File does not exist". I will try it also with your example.

Which file are you checking?
If you are trying to check the file loaded from contentresolver using File.Exist, it is not going to work. I tried that.

 

[hatzisn]

I was trying to create a b4xlib taking advantage of your example. The mime type was different, but I believe (make that I am sure), this is because I selected a pdf file from Google Drive to load/copy... I have not still checked this out but I will this evening.

 

[aeric]

I was trying to create a b4xlib taking advantage of your example. The mime type was different, but I believe (make that I am sure), this is because I selected a pdf file from Google Drive to load/copy... I have not still checked this out but I will this evening.

I am not sure what you are trying to do.
This Code Snippet is about SQLite database, not PDF file.
If you have further question, please start a new thread so other members can help you.

 

[Mark Stuart]

Hi aeric,
Just found this code snippet. Just remembered about using VACUUM on the database. Good idea.
Also, I didn't see in the backup code where you should close the database before copying it in the SaveAs code.
Mark

 

[aeric]

I didn't see in the backup code where you should close the database before copying it in the SaveAs code.

My logic is the "data.db" database doesn't need to be close when executing the vacuum. (If closed, you can't execute query right?)
The "backup.db" is the one going to be copied, which is not opened.

I only close the "data.db" during the restore operation in post #4 before deleting it.
Correct me if I am wrong.

 

[aeric]

DateTime.DateFormat = "yyyy-MM-dd HH:mm:ss"
SQL1.ExecNonQuery2("UPDATE info SET modified = ?", Array As String(DateTime.Date(DateTime.Now)))

Note: this code is just a test and not required in the backup process. I just want to show a simple modified value.
I have commented the code in post #1.