Android Question Changing sqlite tables between upgrades

Alberto Michelis

Well-Known 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
 

eurojam

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

Alberto Michelis

Well-Known 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?
 

Alberto Michelis

Well-Known 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.
 

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.

B4X:
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
 
Top