Android Code Snippet SQlite: Backup database and alter table

Discussion in 'Code Snippets' started by KMatle, Oct 30, 2017.

  1. KMatle

    KMatle Expert Licensed User

    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.

    Code:
    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
     
    mendiburen, jimmyF, Volga and 5 others like this.
  2. Star-Dust

    Star-Dust Expert Licensed User

    I also add a new table with its Fields :p

    Code:
    Sub AddTable(Path as String, FileName as String, NameTable As String,Fields As StringAs 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
     
  3. Johan Hormaza

    Johan Hormaza Active Member Licensed User

    Good job!!!
     
  4. ivan.tellez

    ivan.tellez Active Member Licensed User

    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
     
    mendiburen 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