0

I have got 2 tables. Headers with names and details with texts:

create table Headers (_id integer primary key autoincrement, name string);
create table Details (_id integer primary key autoincrement, id_headers integet, text string);

id_headers is the link to table Headers row (one-to-many). I want to write a method to upgrade these tables. The first and the least case I know is to create a temp table copy of 1st and 2nd tables, create new structure and insert data into new structure.

But in this case all "id_headers to _id" relations will be lost. How can I keep them in new structure, and the same time I want them to keep as "autoincrement".

tshepang
  • 12,111
  • 21
  • 91
  • 136
Foenix
  • 991
  • 3
  • 10
  • 23

1 Answers1

0

SQLiteDatabase.insert returns the new _id. Insert the Headers table data first, creating a mapping of new _id's against _id's in temp data structure.

Now when you populate the Details table consult your map for the old id_headers value to get the new id_headers value. 

private void migrate(SQLiteDatabase db){
    ArrayList<Header> oldHeaders = new ArrayList<Header>();
    ArrayList<Detail> oldDetails = new ArrayList<Detail>)();
    HashMap<Long,Long> idMap = new HashMap<Long,Long>();

    Cursor oldHeadersCurs = db.query("Headers", null, null, null, null, null, null);
    oldHeadersCurs.moveToFirst();

    //store the old header records
    while (!oldHeadersCurs.isAfterLast()){
        long oldId = oldHeadersCurs.getLong(oldHeadersCurs.getColumnIndex("_id"));
        String name = oldHeadersCurs.getString(oldHeadersCurs.getColumnIndex("name"));
        oldHeaders.put(new Header(oldId,name));

        oldHeadersCurs.moveToNext();
    }

    //delete the headers table
    db.execSQL("DROP TABLE Headers");
    //create the new headers table
    db.execSQL(CREATE_NEW_HEADERS_TABLE_STMT);

    //insert the header records capturing the new id
    for (Header header : oldHeaders){
        ContentValues cv = new ContentValues();
        cv.put("name", header.getName());
        long newId = db.insert("Headers", null, cv);
        idMap.put(header.getId(), newId); //mapping the old _id to the new 
    }

    //store the old detail records
    Cursor oldDetailsCurs = db.query("Details", null, null, null, null, null, null);
    oldDetailsCurs.moveToFirst();
    while (!oldDetailsCurs.isAfterLast()){
        //btw text is a data type in sqlite, you need to rename this column
        String text = oldDetailsCurs.getString(oldDetailsCurs.getColumnIndex("text"));
        long oldHeaderId = oldDetailsCurs.getLong(oldDetailsCurs.getColumnIndex("id_headers"));
        oldDetails.put(new Detail(text,oldHeaderId));
        oldDetails.moveToNext();
    }

    //recreate details table
    db.execSQL("DROP TABLE Details");
    db.execSQL("CREATE_NEW_DETAILS_TABLE_STMT");

    //insert the new detail records using the id map
    for (Detail detail : oldDetails){
        ContentValues cv = new ContentValues();
        cv.put("text",detail.getText());
        cv.put("id", idMap.get(detail.getHeaderId())); //retrieving the new _id based on the old
        db.insert("Details", null, cv);
    }
}
kbeal
  • 118
  • 6