Android Question Changing sqlite tables between upgrades

Discussion in 'Android Questions' started by Alberto Michelis, Jun 13, 2015.

  1. Alberto Michelis

    Alberto Michelis Active Member Licensed User

    Hi,
    How do you manage the table changes (ner fields, indexes, etc) between upgrades if you can not delete and recreate the table bacause you need to keep the user data.
    Thanks
     
  2. eurojam

    eurojam Well-Known Member Licensed User

    There are different possibilities to do that, one is for example to rename the table:
    Code:
    ALTERTABLE{tableName} RENAME TO TempOldTable;
    Then create a new table with the missing column:
    Code:
    CREATETABLE{tableName}(name TEXT, COLNew {type}DEFAULT{defaultValue}, qty INTEGER, rate REAL);
    then fill it with the old data:
    Code:
    INSERTINTO{tableName}(name, qty, rate)SELECT name, qty, rate FROM TempOldTable;
    At last, delete the tempOldTable
    Code:
    DROPTABLE TempOldTable;
     
  3. Peter Simpson

    Peter Simpson Well-Known Member Licensed User

    To add a new column at the end of your current table, you could just do the following.

    ALTER TABLE table_name ADD COLUMN new_column columntype
    so for example
    ALTER TABLE user ADD email CHAR(30)
     
  4. Alberto Michelis

    Alberto Michelis Active Member Licensed User

    Thank you very much, I know about the sqlite commands, but how do you identify the version an act in consecuence.
    The user, may be has V2 and v3 has changes and v4 has chenges and the actual version is v5... and the user did not upgrade to 3 and 4 and need to upgrade from 2 to 5.
    How do you manage it?
    How do you identify the version?
     
  5. Peter Simpson

    Peter Simpson Well-Known Member Licensed User

    What exactly do you mean, can you explain yourself a bit better...
     
    Last edited: Jun 13, 2015
  6. Alberto Michelis

    Alberto Michelis Active Member Licensed User

    Yes, suppouse you release V2
    The user upgrades and the app made the table changes between V1 and V2
    Then you release V3 and the user does not upgrade
    Then you release V4 with changes between v3 not v2 and the user upgrades, th user miss the v2 to v3 upgrades...
    Execept you maintein all the changes between version inside the app which may be a problem making the app grous and grous every table change.
    And if you decide to do this you need to know which version the user is running, etc, etc
    Then I dont know how to do it and try to find some experienced programmers advise.
    Hope I'm beeing more clear.
     
  7. Eumel

    Eumel Active Member Licensed User

    i give my table a versionnummer too, and check this nummer at startup.
    in a do ... loop i update the table structure and increase the version nummer.

    Code:
    Sub Check_DBV
        
    Dim DBVersion, CurrentDBVersion As Int
        DBVersion = DBUtils.GetDBVersion(SQL1)
       
        CurrentDBVersion = 
    3
        
    Do While DBVersion < CurrentDBVersion
            
    Select DBVersion
            
    Case 1
                UpdateDB1_2(SQL1)
            
    Case 2
                UpdateDB2_3(SQL1)
            
    Case 3
                UpdateDB3_4(SQL1)
            
    End Select
            DBVersion = DBUtils.GetDBVersion(SQL1)
        
    Loop
    End Sub
    In the Subs are the changes relevant to the version nummer. So it doesn´t matter if the user always update or not

    -----------------
    Eumel
     
Loading...