Android Question Migration SQLite during app Updating

ALBRECHT

Active Member
Licensed User
Hello,

Ok, i know that no Sqlite data will be lost during an upgrade, only when we uninstall the app.

But if i need to change the data format between 2 versions, (in this case, add a field)

i would have to implement some migration code.

what is the just method for doing this ?

Thanks Michel
 

ALBRECHT

Active Member
Licensed User
Thank you for the links, thats what i looked for.
 

mw71

Active Member
Licensed User
Check if Column Exist
B4X:
Sub ExistColumnInTable (SQL_AT As SQL, TableName As String, CToSearch As String) As Boolean
      Dim cursor1 As Cursor
    Private Row As Int
    Private Query As String
 
    Query = "PRAGMA table_info ('" & TableName & "')"
    cursor1 = SQL_AT.ExecQuery(Query)
    Dim Found As Boolean = False
    For Row = 0 To cursor1.RowCount - 1
        cursor1.Position = Row
        If cursor1.getstring2(1) = CToSearch Then
            Found=True
            Exit
        End If
    Next
    
    Return Found
End Sub
Add Column (do nothing if exist)
B4X:
Sub AddColumnToTable (SQL_AT As SQL, TableName As String, CToAdd As String, CFormat As String)
      Dim cursor1 As Cursor
    Private Row As Int
    Private Query As String
 
    Query = "PRAGMA table_info ('" & TableName & "')"
    cursor1 = SQL_AT.ExecQuery(Query)
    Dim Found As Boolean = False
    For Row = 0 To cursor1.RowCount - 1
        cursor1.Position = Row
        If cursor1.getstring2(1) = CToAdd Then
            Found=True
            Exit
        End If
    Next
    If Found=False Then
        'File.Copy(DBDir,DBName,DBDir,DBName & DateTime.Now & "_backup") 'backup before altering table
        Query = "ALTER TABLE " & TableName & " ADD COLUMN " & CToAdd & " " & CFormat
        SQL_AT.ExecNonQuery(Query)
    End If
 
    Query = "PRAGMA table_info(" & TableName &")" 'Get table info -> shows column names and format
    cursor1 = SQL_AT.ExecQuery(Query)
    For Row = 0 To cursor1.RowCount - 1
        cursor1.Position = Row
    Next
 
End Sub
add more than 1 Column
Map -> Column Name : Type
B4X:
Sub AddColumnsToTable (SQL_AT As SQL, TableName As String, CAdd As Map)
      Dim cursor1 As Cursor
    Private Row As Int
    Private Query As String
      Dim Cols As Map
    Cols.Initialize
 
    Query = "PRAGMA table_info ('" & TableName & "')"
    cursor1 = SQL_AT.ExecQuery(Query)
    For Row = 0 To cursor1.RowCount - 1
        cursor1.Position = Row
        Cols.put(cursor1.GetString2(1),"-")
    Next

    For i=0 To CAdd.Size-1
        If Cols.ContainsKey(CAdd.GetKeyAt(i))=False Then
            Query = "ALTER TABLE " & TableName & " ADD COLUMN " & CAdd.GetKeyAt(i) & " " & CAdd.GetValueAt(i)
            SQL_AT.ExecNonQuery(Query)           
        End If
    Next

 
End Sub
 
Top