1

In my app, I have a sqlite database for collectibles. The DB is pre-populated, but the user adds data to indicate he owns that particular item. The collectibles are structured in categories, each having its own table.

So in the first version of the app, I have the 2011 baseball players, identified by a number and a name. I have all the 2009 to 2011 cards in a database (with a table for each year) which is situated in the assets folder and imported via InputStream. So far everything works great, and the user can add a flag saying he owns a card.

My problem is now I want to add the 2012 cards, so I added those as an additional table in my assets database for version 2 of the app. What I can't get to work is adding the new (pre-populated) table to the existing database on upgrade, without losing the flags the user set.

Obviously I don't want to lose the data users have already entered, so no DROP table.

I tried to get the new table onUpgrade by attaching the updated database and copying the missing table. Unfortunately, it seems I can't ATTACH a table during a transaction, as showed by the error I get:

Failure 1 (cannot ATTACH database within transaction) on 0x2a1900 when executing 'ATTACH DATABASE '/data/data/my.app/databases/updateddb' as myNewDb'

Am I missing something, is there a workaround? Or am I going about this the wrong way?

CL.
  • 173,858
  • 17
  • 217
  • 259
InterRaptor
  • 301
  • 1
  • 2
  • 4

2 Answers2

1

when you update your app use onUpgrade method to add a table to the existing database..

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    db.execSQL("create table "
        + LIBRARY + "( " + COLUMN_ID
        + " integer primary key autoincrement, " + COLUMN_NAME
        + " text not null, "+COLUMN_URL
        + " text not null);");
         //and populate the table 
}

this will just add a table to the existing database..

5hssba
  • 8,079
  • 2
  • 33
  • 35
  • Thanks for your answer, but as you say, this only adds the table. So how do I populate that table now, since I can't attach the original? – InterRaptor Mar 28 '12 at 13:30
  • I am not getting you.. you have a database with only one table.. and on updating your app you want to add a new column to that table? Or you want to add a table to the existing database? – 5hssba Mar 29 '12 at 03:58
  • I want to add a table to the existing database. But that table needs to be pre-filled with data, or in some way the data has to be filled into the table. – InterRaptor Mar 30 '12 at 07:40
  • I added some info in the question to (hopefully) make the problem more clear. And thanks for looking into it. – InterRaptor Mar 30 '12 at 07:52
  • If you are using sqlitehelper class.. then you will have its oncreate method... there you can add any data into the table after creating it...@Override public void onCreate(SQLiteDatabase database) { database.execSQL(DATABASE_CREATE);<-- after this line fill the table – 5hssba Mar 30 '12 at 07:56
  • like this..put this in oncreate of your sqlite helper class after creating the table... ContentValues initialValues = new ContentValues(); initialValues.put("COLUMN_NAME", "xxxxx"); initialValues.put("COLUMN_URL", "xxxxxxxxxx"); – 5hssba Mar 30 '12 at 07:59
  • That would certainly work, but it seems impractical for anything but a small amount of records. In my case it can go up to 200 records. – InterRaptor Mar 30 '12 at 08:11
0

The onUpgrade() method is called within a transaction, so you cannot simply attach another database file.

The only remaining way to modify the database is by executing SQL commands. So you have to extract the table data into an SQL text file, ship that with the new version of the app, and in the onUpgrade(), execute all commands in the file.

Community
  • 1
  • 1
CL.
  • 173,858
  • 17
  • 217
  • 259