Android Question Changing sqlite tables between upgrades

Alberto Michelis

Well-Known Member
Licensed User
Longtime 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
 
There are different possibilities to do that, one is for example to rename the table:
Code: Copy
ALTERTABLE{tableName} RENAME TO TempOldTable;
Then create a new table with the missing column:
Code: Copy
CREATETABLE{tableName}(name TEXT, COLNew {type}DEFAULT{defaultValue}, qty INTEGER, rate REAL);
then fill it with the old data:
Code: Copy
INSERTINTO{tableName}(name, qty, rate)SELECT name, qty, rate FROM TempOldTable;
At last, delete the tempOldTable
Code: Copy
DROPTABLE TempOldTable;
 
Upvote 0
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)
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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: Copy
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
 
Upvote 0
Back
Top