【Android】SQLiteのテーブルからカラムを削除する

ALTER TABLE mytable DROP COLOMN mycolomnを実行すれば完了・・・と思いきや、SQLiteはDROP COLOMNをサポートしていないらしい。

http://www.sqlite.org/faq.html#q11

そのため、以下の手順でカラムを削除した

1. テーブルのデータを全て取得
2. DROP TABLEでテーブル削除
3. カラムを消したSQL文を使ってCREATE TABLEでテーブル作成
4. テーブルのデータを復元

下のコードはfilterテーブルからfeedIdカラムを削除したコードになる。

public class DatabaseHelper extends SQLiteOpenHelper{
  
    public static final String DATABASE_NAME = "rss_manage";
    private static final int DATABASE_VERSION = 3;
    private static final int DATABASE_VERSION_ADD_FILTER_FEED_REGISTRATION = 3;

    private String createFiltersTableSQL =
            "create table " + Filter.TABLE_NAME + "(" +
                    Filter.ID + " integer primary key autoincrement,"+
                    Filter.KEYWORD + " text,"+
                    Filter.URL + " text," +
                    Filter.TITLE + " text,"+
                    Filter.ENABLED + " integer)";

    public DatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    //onCreate() is called when database is created
    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(createFiltersTableSQL);
    }
      
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        if (newVersion < oldVersion) return;
        if ((oldVersion < DATABASE_VERSION_ADD_FILTER_FEED_REGISTRATION)) {
            // Drop feed ID column in filter table, but Androd does not support drop column.
            // Copy and drop table and insert.
            ArrayList<Filter> filters = getAllFilters(db);
            String sql = "DROP TABLE " + Filter.TABLE_NAME;
            db.execSQL(sql);
            db.execSQL(createFiltersTableSQL);

            // Insert all of the filters
            insertFilters(db, filters);
        }
    }

    private void insertFilters(@NonNull SQLiteDatabase db, @NonNull ArrayList<Filter> filters) {
        try {
            db.beginTransaction();
            boolean result = true;
            for (Filter filter : filters) {
                ContentValues filterVal = new ContentValues();
                filterVal.put(Filter.TITLE, filter.getTitle());
                filterVal.put(Filter.KEYWORD, filter.getKeyword());
                filterVal.put(Filter.URL, filter.getUrl());
                filterVal.put(Filter.ENABLED, filter.isEnabled());
                long newFilterId = db.insert(Filter.TABLE_NAME, null, filterVal);
                if (newFilterId == -1) {
                    result = false;
                    break;
                }
            }
            if (result) db.setTransactionSuccessful();
        } catch (SQLiteException e) {
            e.printStackTrace();
        } finally {
            db.endTransaction();
        }
    }

    private ArrayList<Filter> getAllFilters(SQLiteDatabase db) {
        Cursor cursor = null;
        ArrayList<Filter> filters = new ArrayList<>();
        try {
            db.beginTransaction();
            String[] columns = {
                    Filter.ID,
                    Filter.TITLE,
                    Filter.KEYWORD,
                    Filter.URL,
                    Filter.FEED_ID,
                    Filter.ENABLED
            };
            cursor = db.query(Filter.TABLE_NAME, columns, "", null, null, null, null);
            if (cursor != null && cursor.getCount() > 0) {
                while (cursor.moveToNext()) {
                    int filterId = cursor.getInt(0);
                    String title = cursor.getString(1);
                    String keyword = cursor.getString(2);
                    String url = cursor.getString(3);
                    int feedId = cursor.getInt(4);
                    int enabled = cursor.getInt(5);
                    Filter filter = new Filter(filterId, title, keyword, url, feedId, enabled);
                    filters.add(filter);
                }
            }
            db.setTransactionSuccessful();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (cursor != null) {
                cursor.close();
            }
            db.endTransaction();
        }
        return filters;
    }
}