CODEWITHSUNDEEP
androiddatabasesqlite
Curtor
Curtor

Reputation: 737

How to replace a table in SQLite

I'm trying to do a database migration where I remove a table and add a unique restriction on one of the columns inside of my Android application.

I created the new table, copied the values from the original values to the new table, dropped the old table, and then renamed the new table to the same name as the old table.

Problem is that now when I query the database for the values in the original table name, I'm getting results from the allegedly dropped table instead of the new table that was renamed.

I've tried doing everything except renaming the new table in order to verify everything was hooked up correctly. I see the new table with the correct columns. But querying the old table gives me valid responses instead of an error for "table does not exist".

This is happening inside of a test where I am setting up the database, calling onUpgrade, and then querying the state.

To reiterate:

  1. Create table A with columns 1, 2, 3
  2. Create table B with columns 1, 2, 4, 5
  3. Copy rows from table A to table B, dropping column 3 and having default values for columns 4 and 5.
  4. Drop table A
  5. Rename table B to table A
  6. Get entries with columns 1, 2, 3 when querying table A

Alternate:

Do I need to perform some sort of flush to get the dropped table to actually drop?

/* Create table_a */
/* Populate table_a */
/* Create table_b */
/* Copy table_a contents to table_b */
db.execSQL("DROP TABLE IF EXISTS table_a");
db.execSQL("ALTER TABLE table_b RENAME TO table_a");

Thanks.

Upvotes: 1

Views: 6253

Answers (1)

Curtor
Curtor

Reputation: 737

Worked for about 8 hours on this over the course of the last couple days, as well as with a few different engineers at work. Sorry I can't share all the details, but I'll share what I can for anyone else that might run into the same, or similar, issue.

Hypothesis: GoogleRobolectricTestRunner uses a different backing database than a real device and was causing caching issues inside of the test. Still need to test on a real device to verify.

All queries against the new table work as expected except when trying to get the column names in the new table. Originally I was doing that with the following method:

// DID NOT WORK
public static Collection<String> getTableColumnNames(
    final SQLiteDatabase db, String tableName) {
  try (Cursor cursor = db.query(tableName, null, null, null, null, null, null)) {
    return Arrays.asList(cursor.getColumnNames());
  }
}

This method always returned the column names of the old dropped table. Eventually decided to just change the way we were getting the column names and the rest of the testing was successful.

public static Collection<String> getTableColumnNames(
    final SQLiteDatabase db, String tableName) {
  Set<String> columnNames = new HashSet<>();
  try (Cursor cursor = db.rawQuery("PRAGMA table_info(" + tableName + ")", null)) {
    if (cursor.moveToFirst()) {
      do {
        columnNames.add(cursor.getString(1));
      } while (cursor.moveToNext());
    }
    return columnNames;
  }
}

Upvotes: 1

Related Questions