Refresh sqlite database in my application
So, I already have an app on a slot machine ....
Now I want to add a column to the database in my application. For this I have to update my database, which can be done by changing the version of the database.
Users already have something in the database and when I download an updated version of my application (with the changed version of the database) it will create a new database and the user will lose everything they have in his / her database.
What is the solution to this problem? How do I back up / restore the contents of the old database to the new database? (I know how to back up the database, just copy it to the program folder in the repository).
source to share
You can use the onUpgrade () method for this.
Something like that:
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
if (oldVersion == 1 && newVersion == 2) {
db.execSQL("create temporary table people_tmp ("
+ "id integer, name text, position text, posid integer);");
db.execSQL("insert into people_tmp select id, name, position, posid from people;");
db.execSQL("drop table people;");
db.execSQL("create table people ("
+ "id integer primary key autoincrement,"
+ "name text, posid integer);");
db.execSQL("insert into people select id, name, posid from people_tmp;");
db.execSQL("drop table people_tmp;");
}
}
So. You create a temporary table and store all the information you need inside this table. Then you drop the table, create a new one, and insert values from your temporary table into it. You can add additional fields and feel free to put whatever you want there.
UPDATE: After a little googling, I found an easier solution:
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// If you need to add a column
if (newVersion == 2) {
db.execSQL("ALTER TABLE foo ADD COLUMN new_column INTEGER DEFAULT 0");
}
}
The Alter table method will change the structure of your database without losing data.
source to share
If you are just adding a new column, you can modify the existing table instead of creating a new table. Example:
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
if(oldVersion<2){
db.execSQL("ALTER TABLE "+this.getTableName()+" ADD COLUMN "+COLUMNS.NAME+ " integer default 0;", null);
db.execSQL("UPDATE "+this.getTableName()+ " SET "+COLUMNS.NAME+ "="+COLUMNS.NAMEVALUE+";", null);
}
};
Here is Android documentation for an example of using ALTER TABLE in onUpgrade () . Therefore, in this case, unless you rename or delete the existing table, you do not need to archive the old table.
If you add new columns, you can use ALTER TABLE to insert them into the live table.
See also: fooobar.com/questions/100666 / ...
source to share