Android Question Migration SQLite during app Updating

Discussion in 'Android Questions' started by ALBRECHT, Aug 3, 2019.

  1. ALBRECHT

    ALBRECHT 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
     
  2. Jorge M A

    Jorge M A Active Member Licensed User

  3. ALBRECHT

    ALBRECHT Member Licensed User

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

    mw71 Active Member Licensed User

    Check if Column Exist
    Code:
    Sub ExistColumnInTable (SQL_AT As SQL, TableName As String, CToSearch As StringAs 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)
    Code:
    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
    Code:
    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
     
    AnandGupta likes this.
Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice