Android Code Snippet SQlite: Backup database and alter table

Here's a little sub to backup and alter a table (assuming you have all the SQlite parts in the starter service).

The sub checks if the column is already defined. Of course you can leave the loop at once with a return. For debug reasons I've used a Boolean here.

Sub AddColumnToTable (TableName As String, CToAdd As String, CFormat As String)
  
    Private Row As Int
    Private Query As String
  
'    Dim TableName As String = "items" 'tablename
'    Dim CToAdd As String = "iid" 'column to add
'    Dim CFormat As String = "INTEGER" 'format of the column
    Dim DBDir As String =Starter.SQLDataBasePath
    Dim DBName As String =Starter.SQLDateBaseName
   
    Query = "SELECT * FROM " & TableName 'get all columns
    Cursor1 = Starter.SQL1.ExecQuery(Query)
    Dim Found As Boolean = False
    For l=0 To Cursor1.ColumnCount-1
        Log(Cursor1.GetColumnName(l))
        If Cursor1.GetColumnName(l) = CToAdd Then
            Found=True
            Exit
        End If
    Next
    If Found=False Then 'backup before alter ;-)
        File.Copy(DBDir,DBName,DBDir,DBName & DateTime.Now & "_backup") 'backup before altering table
        Query = "ALTER TABLE " & TableName & " ADD COLUMN " & CToAdd & " " & CFormat
        Starter.SQL1.ExecNonQuery(Query)      
    End If
  
    Query = "PRAGMA table_info(" & TableName &")" 'Get table info -> shows column names and format
    Cursor1 = Starter.SQL1.ExecQuery(Query)
    For Row = 0 To Cursor1.RowCount - 1
        Cursor1.Position = Row
        Log(Cursor1.GetString2(1) & ":" & Cursor1.GetString2(2))
    Next
  

End Sub

 

[Star-Dust]

I also add a new table with its Fields

Sub AddTable(Path as String, FileName as String, NameTable As String,Fields As String) As Boolean
    ' Files. es
    ' "ID INTEGER  PRIMARY KEY AUTOINCREMENT NOT NULL, Name TEXT, Position NUMERIC, Quantity INTEGER"
    Dim DB As SQL
    Dim S As String
    Dim B As Boolean = False
   
    DB.Initialize(Path,FileName,False)
    S=DB.ExecQuerySingleResult("SELECT name FROM sqlite_master WHERE type='table' AND name='" & NameTable &"';")
    If S=Null Then
        DB.ExecNonQuery("CREATE TABLE " & NameTable &" (" & Fields & ")" )
        B=True
        End If
    Return b
End Sub

 

[Johan Hormaza]

Good job!!!

 

[ivan.tellez]

Here's a little sub to backup and alter a table (assuming you have all the SQlite parts in the starter service).

The sub checks if the column is already defined. Of course you can leave the loop at once with a return. For debug reasons I've used a Boolean here.

    Query = "SELECT * FROM " & TableName 'get all columns

Do you really want to load ALL the table data to memory? What if has 2 million records?

Yo should add an LIMIT 1 to the query