0

I have an app on playstore which contains a database.
Now in the new release, database is changed, a new table is entered.
Currently i am using sqlite studio and put the database file in the assets which is later copied as an app database. See this example.

I have to delete the previous database to copy new, or if i copy new(overwrite), in both cases user data is lost.

Maybe i can attach a new table to current database, but what if i have to add a column in table in previous database???
How should i handle this???

Edit: If i have to copy the data from database and re-insert, how can i know the tables and columns which currently exist in my database.

Please guide me...

Community
  • 1
  • 1
Salmaan
  • 3,543
  • 8
  • 33
  • 59
  • Create a backup **first**, delete the old database, create a new database and copy the backup data from the old database to the new database (provided you know where each content needs to be placed to which table). – Buhake Sindi Jan 07 '15 at 11:07
  • @BuhakeSindi If i have to copy the data from database and re-insert, how can i know the tables and columns which currently exist in my database. – Salmaan Jan 07 '15 at 11:40
  • Backup each table into a file, that way you know which backup file maps to which table. – Buhake Sindi Jan 07 '15 at 12:02
  • You can use the most recent SQLiteStudio and do all the changes you usually do, then open "DDL history" window - you will see all queries needed to be execued in order to change your database, just like SQLiteStudio does it. You can then use those queries to update your database on user's side, from your application level. – Googie Jan 09 '15 at 08:36

2 Answers2

0

Get All the data from Database into ArrayList or anything else (I will suggest Arraylist) and Delete old database and then Create New database and Insert all The data into New database.

Its not a Proper way but a trick to save your time.

Cheers

-Aman

AmniX
  • 653
  • 5
  • 12
  • Better create ArrayList of Objects which included all columns from db. – Mark Zucchini Jan 07 '15 at 11:11
  • @MarkZucchini How can i know the tables and columns which currently exist in my app database? – Salmaan Jan 07 '15 at 11:36
  • @Salmaan Table names: http://stackoverflow.com/questions/17185269/how-to-list-the-table-name-from-sqlite-db-android Columns names: http://stackoverflow.com/questions/2382528/how-to-get-a-tables-columns-arraylist-on-android – Mark Zucchini Jan 07 '15 at 13:00
  • @Salmaan Probably you need implement a whole class in oder to do the thing you want. – Mark Zucchini Jan 07 '15 at 13:04
  • @Salmaan Actually android includes mechanisme which may help to upgrade db from one version to another. This page (especially 4 chapters) describes how to work with sql: http://vogella.com/tutorials/AndroidSQLite/article.html#databasetutorial_database . And besides you may just copy one table to another (temporary) by command `INSERT INTO Destination SELECT * FROM Source;` This will copy all data from `Source` table to `Destination`. – Mark Zucchini Jan 07 '15 at 21:38
0

For a new Table, you just have to add the create table in the update method

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion){
    if(oldVersion < LastVersionWithoutTheTable && newVersion >= LastVersionWithoutTheTable){
        db.execSQL(CREATE_THE_TABLE);
    }
}

For a new column, copy all your data in an array, recreate your table and insert your data again.

It's maybe possible to used SQL Alter Table but I never try it on a SQlite database.

AxelH
  • 14,325
  • 2
  • 25
  • 55
  • If i have to copy the data from database and re-insert, how can i know the tables and columns which currently exist in my database. – Salmaan Jan 07 '15 at 11:39
  • Suppose you've added a column in the v3 off the database, in onUpgrade, you check the value of oldVersion, if it's before v3, you know it's the table without all this new column. (Same things for the tables, keep a record of your modification of your database somewhere, to know that your've created this table in this version...) I use an array of string to store my column name (and order), you could use this to store the old structure (one array per version of table, to know how was your table. – AxelH Jan 09 '15 at 15:09