SQLite onUpgrade migrate data?

Problems with WiFi, SQLite ,Bluetooth, WiMax, Proxies, etc...

SQLite onUpgrade migrate data?

Postby gcstang » Thu Jan 07, 2010 8:55 pm

Is it possible to migrate the data from the old database to the new one when onUpgrade is called?

Any tips in the right direction if this is possible?


Thank you,
gcstang
Freshman
Freshman
 
Posts: 8
Joined: Sun Jan 03, 2010 12:37 am

Top

Postby Draffodx » Fri Jan 08, 2010 1:26 pm

not sure what you mean?

something like this?

Syntax: [ Download ] [ Hide ]
Using java Syntax Highlighting
  1. public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
  2.  
  3.                         Log.w(TAG, "Upgrading database from version " + oldVersion + " to " +
  4.  
  5.                                          newVersion + ", which will destroy all old data");
  6.  
  7.                        
  8.  
  9.                         db.execSQL("DROP TABLE IF EXISTS " + SETTINGS_TABLE);
  10.  
  11.                         onCreate(db);
  12.  
  13.                        
  14.  
  15.                 }
Parsed in 0.032 seconds, using GeSHi 1.0.8.4
Draffodx
Master Developer
Master Developer
 
Posts: 205
Joined: Wed Nov 12, 2008 2:31 pm

Mine does that by default....I want the data to be migrated.

Postby gcstang » Fri Jan 08, 2010 1:49 pm

Draffodx wrote:not sure what you mean?

something like this?

Syntax: [ Download ] [ Hide ]
Using java Syntax Highlighting
  1. public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
  2.                         Log.w(TAG, "Upgrading database from version " + oldVersion + " to " +
  3.                                          newVersion + ", which will destroy all old data");
  4.                        
  5.                         db.execSQL("DROP TABLE IF EXISTS " + SETTINGS_TABLE);
  6.                         onCreate(db);
  7.                        
  8.                 }
Parsed in 0.032 seconds, using GeSHi 1.0.8.4


My program currently does this but this loses the data, is there a way to migrate the data to the new version of the database?

Thanks for your assist.
gcstang
Freshman
Freshman
 
Posts: 8
Joined: Sun Jan 03, 2010 12:37 am

Postby grzegorz.zajac » Fri Jan 08, 2010 2:31 pm

Hi,

This is the exact purpose of the onUpgrade() method. Implement the operations in the following order:

1) create temporary table (with the same structure as original) and copy data from the original table into this new one
2) drop the original table
3) create the new original table (i.e. with more columns, with other column names, etc.)
4) copy data back from the temporary table to this new original one

BTW - if you only need to add one or more columns to the existing table, there is no need to do what I have written above. You can simply use ALTER sql command on the original table without data loss.

Regards,
Grzesiek
grzegorz.zajac
Developer
Developer
 
Posts: 39
Joined: Wed Feb 18, 2009 8:37 pm
Location: Krakow/Poland

Awesome thank you!

Postby gcstang » Fri Jan 08, 2010 3:26 pm

grzegorz.zajac wrote:Hi,

This is the exact purpose of the onUpgrade() method. Implement the operations in the following order:

1) create temporary table (with the same structure as original) and copy data from the original table into this new one
2) drop the original table
3) create the new original table (i.e. with more columns, with other column names, etc.)
4) copy data back from the temporary table to this new original one

BTW - if you only need to add one or more columns to the existing table, there is no need to do what I have written above. You can simply use ALTER sql command on the original table without data loss.

Regards,
Grzesiek


Thank you very much, wasn't sure I could use alter on this database.

To Ensure I understand correctly:
In your steps above I would have a second DBHelper to a temp table that I would copy all my existing data into and then pull it back into my new one and voila.

Thanks again.
gcstang
Freshman
Freshman
 
Posts: 8
Joined: Sun Jan 03, 2010 12:37 am

Re: Awesome thank you!

Postby grzegorz.zajac » Fri Jan 08, 2010 3:44 pm

Hi again,

gcstang wrote:
Thank you very much, wasn't sure I could use alter on this database.

To Ensure I understand correctly:
In your steps above I would have a second DBHelper to a temp table that I would copy all my existing data into and then pull it back into my new one and voila.

Thanks again.


More precisely you can do ALTER on the table not the database itself.
In the onUpgrade() you are not deleting the database but only TABLES.
In my practice I only use execSQL() methods to create new table and drop old ones.

BTW - there is one more step I forgot about
5) drop the temporary table

Regards,
Grzesiek
grzegorz.zajac
Developer
Developer
 
Posts: 39
Joined: Wed Feb 18, 2009 8:37 pm
Location: Krakow/Poland

Top

Return to Networking & Database Problems

Who is online

Users browsing this forum: No registered users and 6 guests