Android Question Migration SQLite during app Updating

[ALBRECHT]

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

 

[Jorge M A]

What dou you mean with "change data format"? change strings to numbers, or something similar?

Here are a couple of ideas:

https://www.b4x.com/android/forum/threads/changing-sqlite-tables-between-upgrades.54967/#post-345494
https://www.b4x.com/android/forum/t...th-changed-sqlite-database.36049/#post-211620

 

[ALBRECHT]

Thank you for the links, thats what i looked for.

 

[mw71]

Check if Column Exist

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)

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

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