【Android】SQLiteのテーブルからカラムを削除する
ALTER TABLE mytable DROP COLOMN mycolomnを実行すれば完了・・・と思いきや、SQLiteはDROP COLOMNをサポートしていないらしい。
http://www.sqlite.org/faq.html#q11
そのため、以下の手順でカラムを削除した
下のコードは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; } }