3

For example I have a table named myTable in my database file (Android, p.s. it doesn't support column renaming, deleting and so on).

This table has idx, name columns

I want to delete and recreate this table but with new columns

Here how I do it:

db.transaction {
    var cursor = query("myTable", null, null, null, null, null, null)
    // save info to a list and insert it later to a recreated table with new columns
    cursor.close()

    // delete table "myTable"
    execSQL("DROP TABLE IF EXISTS myTable")

    // create table "myTable" but with new columns
    execSQL("CREATE TABLE myTable(id, title)")

    // get columns
    cursor = query("myTable", null, null, null, null, null, null)
    val columnNames = cursor.columnNames
    cursor.close()
    Log.i(TAG, "columns of new table ${Arrays.toString(columnNames)}")
}

But cursor.columnNames doesn't return new columns (id, title)

It returns the old columns (e.g., idx, name)

Though when I close the app and open again everything is ok (old columns are gone and new ones are available)

So annoying!

So new columns are created ok in main.db/myTable during all this process but seems the app still keeps old columns in memory

I can't even use cursor.getString(cursor.getColumnIndex("title")) (would return -1) because it still thinks that this column doesn't exist yet until I restart this app

BUT! Recreating a table with new columns works fine if I don't use cursor to backup data (info) before I delete the old table

db.transaction {
    // !! I COMMENTED THESE LINES
    // var cursor = query("myTable", null, null, null, null, null, null)
    // save info to a list and insert it later to a recreated table with new columns
    // cursor.close()

    // delete table "myTable"
    execSQL("DROP TABLE IF EXISTS myTable")

    // create table "myTable" but with new columns
    execSQL("CREATE TABLE myTable(id, title)")

    // get columns
    cursor = query("myTable", null, null, null, null, null, null)
    val columnNames = cursor.columnNames
    cursor.close()
    Log.i(TAG, "columns of new table ${Arrays.toString(columnNames)}")
}

In this case cursor.columnNames returns new columns id, title

This is crazy!

So it seems query table1, drop table1, create table1, query table1 sequence would lead to an unexpected behavior

And we can only do drop table1, create table1, query table1 sequence

user25
  • 2,873
  • 2
  • 30
  • 66
  • `I want to delete and recreate this table but with new columns` ... which **won't work** until you change the **database version**. Why don't you use`ALTER TABLE`, instead? – Phantômaxx Feb 24 '19 at 14:14
  • @Fantômas well... I do it in `onUpgrade` so of course it's a new version... What are you trying to say? `ALTER TABLE` - what you mean? It's limited in SQLite and there is no support for column renaming. Better try it yourself to understand the issue. It's really a bug. Here's the same issue https://stackoverflow.com/questions/5743045/android-database-strangeness-listing-columns?noredirect=1&lq=1 – user25 Feb 24 '19 at 14:57
  • @Fantômas also I could just use `INSERT INTO` to copy data (in this case I don't need cursor) but I need to process this data before I send it to a recreated table so I need to cursor anyway. But it should not matter what I need because it seems there is a bug and we have to do something about it – user25 Feb 24 '19 at 15:11
  • Don't you want to rename and/or add columns? It seems that `ALTER TABLE` is you best friend, then. https://www.sqlite.org/lang_altertable.html – Phantômaxx Feb 24 '19 at 15:43
  • 1
    @Fantômas don't you want to read this? https://stackoverflow.com/a/52346199/4548520 I need to support Android 14-28, not 33 (which isn't even here yet) – user25 Feb 24 '19 at 15:45
  • I see. Then it's Android not implementing the latest SQLite incarnation. But don't ll the answers in that post help even a bit? – Phantômaxx Feb 24 '19 at 15:50
  • 1
    @Fantômas of course some answers helped but I had to use a new name for table. So I recreated a table with new name (it's not a big deal). But this question is still should be seen by Android team – user25 Feb 24 '19 at 15:52
  • Well, yes,it actually sould. But there's nothing WE, as a mutual help community, can do. Other than finding some workarounds (as you did), until the issue is fixed by the Android development team. – Phantômaxx Feb 24 '19 at 16:18

0 Answers0