Alberto Michelis Well-Known Member Licensed User Longtime User Jun 13, 2015 #1 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
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 Longtime User Jun 13, 2015 #2 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
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;
Peter Simpson Expert Licensed User Longtime User Jun 13, 2015 #3 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
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)
Alberto Michelis Well-Known Member Licensed User Longtime User Jun 13, 2015 #4 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
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?
Peter Simpson Expert Licensed User Longtime User Jun 13, 2015 #5 What exactly do you mean, can you explain yourself a bit better... Last edited: Jun 13, 2015 Upvote 0
Alberto Michelis Well-Known Member Licensed User Longtime User Jun 13, 2015 #6 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
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.
E Eumel Active Member Licensed User Longtime User Jun 14, 2015 #7 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
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