Android Code Snippet Sqlite dataBase UpsizeTable

#Region  Project Attributes
    #ApplicationLabel: B4A Example
    #VersionCode: 1
    #VersionName:
    'SupportedOrientations possible values: unspecified, landscape or portrait.
    #SupportedOrientations: unspecified
    #CanInstallToExternalStorage: False
#End Region

#Region  Activity Attributes
    #FullScreen: False
    #IncludeTitle: True
#End Region

Sub Process_Globals
    'These global variables will be declared once when the application starts.
    'These variables can be accessed from all modules.
    Private xui As XUI
    Dim RP1 As RuntimePermissions
 
    Dim Sql1         As SQL
    Dim SqlMaster    As SQL
    Dim DBFileDir As String
End Sub

Sub Globals
    'These global variables will be redeclared each time the activity is created.
    Dim DBFileName As String = "database.db"
End Sub

Sub Activity_Create(FirstTime As Boolean)
    Activity.LoadLayout("Layout")

    DBFileDir = RP1.GetSafeDirDefaultExternal("")
 
    If File.Exists(DBFileDir, DBFileName) = False Then
        File.Copy(File.DirAssets,DBFileName, DBFileDir, DBFileName )
    End If
 
End Sub

Sub Activity_Resume

End Sub

Sub Activity_Pause (UserClosed As Boolean)

End Sub

Sub Button1_Click
    Dim DB_Master As String ="_Master_" & DBFileName
 
    If File.Exists(DBFileDir,DB_Master) = True Then
        File.Delete(DBFileDir,DB_Master)
    End If
 
    File.Copy(File.DirAssets,DBFileName, DBFileDir,DB_Master)
 
    Sql1.Initialize(DBFileDir,DBFileName,False)
    SqlMaster.Initialize(DBFileDir, DB_Master,False)
    
    Sql1.ExecNonQuery("PRAGMA foreign_keys = 0;")
    UpsizeTable("myTable","myTable")
 
    Sql1.ExecNonQuery("PRAGMA foreign_keys = 1;")
 
    MsgboxAsync("Update Terminated","Ok")
End Sub

Sub UpsizeTable(MasterTable As String, OriginalTable As String)
    Dim tableExists As Boolean = False
    Dim s As String
    s = Sql1.ExecQuerySingleResult("SELECT name FROM sqlite_master WHERE type='table' AND name='" & OriginalTable & "';")
    If s <> Null Then tableExists = True
 
    Sql1.BeginTransaction
 
    Try
       If tableExists Then
            ' --- Find ONLY the columns that are common between old and new table ---
            Dim oldCols As List, newCols As List
            oldCols.Initialize
            newCols.Initialize

            Dim c1 As ResultSet = Sql1.ExecQuery("PRAGMA table_info(temp_" & OriginalTable & ");")
            Do While c1.NextRow
                oldCols.Add(c1.GetString("name"))
            Loop
            c1.Close

            Dim c2 As ResultSet = Sql1.ExecQuery("PRAGMA table_info('" & OriginalTable & "');")
            Do While c2.NextRow
                Dim nm As String = c2.GetString("name")
                If oldCols.IndexOf(nm) <> -1 Then newCols.Add(nm)
            Loop
            c2.Close

            Dim colNames As String = JoinList(newCols, ", ")

            ' Copy only the common fields: new fields will use their default values
            Dim insertSQL As String = "INSERT INTO " & OriginalTable & " (" & colNames & ") " & _
                                      "SELECT " & colNames & " FROM temp_" & OriginalTable & ";"
            Sql1.ExecNonQuery(insertSQL)
            Log("[UPSZ] Data migrated (common fields): " & colNames)

            ' Remove the temporary table
            Sql1.ExecNonQuery("DROP TABLE IF EXISTS temp_" & OriginalTable & ";")
        End If
        
        createSQL = createSQL.Replace(MasterTable, OriginalTable)
        Sql1.ExecNonQuery(createSQL)
        Log("[UPSZ] CREATE TABLE executed: " & createSQL)
    
        If tableExists Then
            ' --- Transfer data from the temporary table -----
            Dim cur As ResultSet = SqlMaster.ExecQuery("PRAGMA table_info('" & OriginalTable & "');")
            Dim colNames As String = ""
            Do While cur.NextRow
                If colNames <> "" Then colNames = colNames & ", "
                colNames = colNames & cur.GetString("name")
            Loop
            cur.Close
        
            Dim insertSQL As String
            insertSQL = "INSERT INTO " & OriginalTable & " (" & colNames & ") " & _
                        "SELECT " & colNames & " FROM temp_" & OriginalTable & ";"
            Sql1.ExecNonQuery(insertSQL)
        
            ' --- Delete temporary table -----------------
            Sql1.ExecNonQuery("DROP TABLE temp_" & OriginalTable & ";")
        End If
    
        ' --- Automatically recreate indexes ----------
        Dim idxCur As ResultSet = SqlMaster.ExecQuery( _
            "SELECT name, sql FROM sqlite_master WHERE type='index' AND tbl_name='" & MasterTable & "' AND sql NOT NULL;")
        Do While idxCur.NextRow
            Dim idxName As String = idxCur.GetString("name")
            Dim idxSQL As String = idxCur.GetString("sql")
        
            idxSQL = idxSQL.Replace(MasterTable, OriginalTable)
        
            If idxName = "" Or idxName = Null Then
                idxName = """" ' OriginalTable & "idx" & DateTime.Now
                ' Ricostruisce correttamente la query senza "" vuote
                Dim pos As Int = idxSQL.IndexOf("CREATE INDEX")
                If pos >= 0 Then
                    idxSQL = "CREATE INDEX  " & """" & " " & idxName & idxSQL.SubString(idxSQL.IndexOf(" ON "))
                End If
            
            End If
            Sql1.ExecNonQuery(idxSQL)
        
            Log("[UPSZ] Index recreated: " & idxSQL)
        Loop
        idxCur.Close
    
        ' Delete the Backup Table
    
        ' --- Drop original table --------------------
        Sql1.ExecNonQuery("DROP TABLE IF EXISTS " & OriginalTable & "_BACKUP;")
    
        Sql1.TransactionSuccessful
          
        Log("[UPSZ] Table " & OriginalTable & " successfully recreated.")
    
    Catch
        Log("[UPSZ][ERROR] Upsize failed! Check physical backup and " & OriginalTable & "_BACKUP.")
    End Try
 
    Sql1.EndTransaction
End Sub

Private Sub JoinList(lst As List, sep As String) As String
    Dim sb As StringBuilder
    sb.Initialize
    For i = 0 To lst.Size - 1
        If i > 0 Then sb.Append(sep)
        sb.Append(lst.Get(i))
    Next
    Return sb.ToString
End Sub