DbUtils.java

import java.util.Arrays;
import java.util.LinkedList;
import java.util.List;

import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.text.TextUtils;

/**
 * Utilities to work with SQLite database.
 * 
 * @author johnkil
 * 
 */
public class DbUtils {

    /**
     * Drop specify column(s) from database table.
     * 
     * @param db {@link SQLiteDatabase} instance
     * @param createTableSql The SQL statement to create table without specify columns
     * @param tableName The name of the table from which the columns will be removed
     * @param columnsToRemove The columns to be removed from the table
     * @throws SQLException
     */
    public static void dropColumns(SQLiteDatabase db, String createTableSql, String tableName, String[] columnsToRemove) throws SQLException {
        List<String> updatedTableColumns = getTableColumns(db, tableName);
        updatedTableColumns.removeAll(Arrays.asList(columnsToRemove));
        String columnsSeperated = TextUtils.join(",", updatedTableColumns);

        // Rename the current table
        db.execSQL("ALTER TABLE " + tableName + " RENAME TO " + tableName + "_old;");

        // Creating the table on its new format (no redundant columns)
        db.execSQL(createTableSql);

        // Populating the table with the data
        db.execSQL("INSERT INTO " + tableName + "(" + columnsSeperated + ") SELECT " + columnsSeperated + " FROM " + tableName + "_old;");
        db.execSQL("DROP TABLE " + tableName + "_old;");
    }

    /**
     * Return names of all table columns.
     * 
     * @param db {@link SQLiteDatabase} instance
     * @param tableName The name of table
     * @return names of all table columns
     */
    public static List<String> getTableColumns(SQLiteDatabase db, String tableName) {
        List<String> columns = new LinkedList<String>();
        String sql = "PRAGMA table_info(" + tableName + ");";
        Cursor cursor = db.rawQuery(sql, null);
        if (cursor.moveToFirst()) {
            int nameColumnIndex = cursor.getColumnIndex("name");
            do {
                columns.add(cursor.getString(nameColumnIndex));
            } while (cursor.moveToNext());
        }
        cursor.close();
        return columns;
    }

}