Android Question SQLite DB corruption

[Harris]

I use 4 DB files, each with many tables to keep data confined to specific types (operator inputs, location tables, configurations, automatic driving records, etc.). Today, at client site, I was reviewing why one device was causing my app to crash, whereas it had been working flawlessly for months.

Turns out, the db called qinspect contained only the tables found in the log.db! None of the proper tables existed - which allows the app to operate correctly. It is like the log.db had been renamed to qinspect.db - which I certainly DONT do. I backup the db's after every user logout - and the backups were corrupt as well

As for the actual logs.db - it could not be opened at all using SQLite Expert. It reports Status for [main] database: Access Error.

The only way to recover is to un-install and re-install the app since the db files exist and doesn't pass as damaged by my recovery code shown next.

Sub Restoredb(db As String) As Boolean
Dim result As Boolean
result = False
    Try
      If File.Exists(File.DirDefaultExternal,db&".bak") Then
         File.Copy( File.DirDefaultExternal, db&".bak",File.DirDefaultExternal,db)
         ToastMessageShow(" Restore File Succeeded: "&db,True)
         result = True
      End If
    Catch
     ToastMessageShow(" Error - Restore Failed: "&db,True)
    End Try
    Return result   
   
End Sub

Sub LoadDB
     If File.Exists(File.DirDefaultExternal, "messages.db") = False Then
        File.Copy(File.DirAssets, "messages.db",File.DirDefaultExternal,"messages.db")
        Log(" Copying messages.db to SD card")
    End If
   
    If SQL4.IsInitialized = False Then
     Try
        SQL4.Initialize(File.DirDefaultExternal, "messages.db", False)
        Log(" ***** --- Initing messages.db if not already inited **********")
     Catch
        Log(" Messages - Malformed datafile file: "&LastException.Message)
     End Try
   End If
  
    If File.Exists(File.DirDefaultExternal, "qinspect.db") = False Then
        File.Copy(File.DirAssets, "qinspect.db",File.DirDefaultExternal,"qinspect.db")
        Log(" Copying QInspect.db to SD card")
    End If

    If SQL1.IsInitialized = False Then
     Try
        SQL1.Initialize(File.DirDefaultExternal, "qinspect.db", False)
        Log(" ***** --- Initing QInspect.db if not already inited **********")
     Catch
        Dim s As String
        s = LastException.Message
        If s.Contains("disk image is malformed") Then
            Log(" error - Malformed log file: "&LastException)
            If Restoredb("qinspect.db") = False Then
               File.Copy(File.DirAssets, "qinspect.db",File.DirDefaultExternal,"qinspect.db")
               SQL1.Initialize(File.DirDefaultExternal, "qinspect.db", False)
              
               Log(" Copying qinspect.db to SD card")
            End If
        Else
            Log(" some other error opening qinspect file: "&LastException)
            If Restoredb("qinspect.db") = False Then
               File.Copy(File.DirAssets, "qinspect.db",File.DirDefaultExternal,"qinspect.db")
               Log(" Copying qinspect.db to SD card")
               SQL1.Initialize(File.DirDefaultExternal, "qinspect.db", False)
              
            End If
        End If
     End Try
    End If



   If File.Exists(File.DirDefaultExternal, "log.db") = False Then
        File.Copy(File.DirAssets, "log.db",File.DirDefaultExternal,"log.db")
        Log(" Copying log.db to SD card")
   End If
  
   If SQL3.IsInitialized = False Then
     Try
        SQL3.Initialize(File.DirDefaultExternal, "log.db", False)
     Catch
        Dim s As String
        s = LastException.Message
       
        If s.Contains("disk image is malformed") Then
            Log(" error - Malformed log file: "&LastException)
            If Restoredb("log.db") = False Then
               File.Copy(File.DirAssets, "log.db",File.DirDefaultExternal,"log.db")
               Log(" Copying log.db to SD card")
                  SQL3.Initialize(File.DirDefaultExternal, "log.db", False)

            End If
        Else
            Log(" some other error opening log file: "&LastException)
            If Restoredb("log.db") = False Then
               File.Copy(File.DirAssets, "log.db",File.DirDefaultExternal,"log.db")
               Log(" Copying log.db to SD card")
                  SQL3.Initialize(File.DirDefaultExternal, "log.db", False)

            End If
           
        End If
           
     End Try
        Log(" ***** --- Initing log.db if not already inited **********")
   End If

    'CheckforUpdates
    rS = GetDeviceLayoutValues
End Sub