How add new columns to the existing database on asset folder?

Question

I have an application that use existing sqlite database on that and put my database on asset folder of android application. Now I need to add new columns to one of the table of database. I googled and find some questions and answers but none of them had same problem, Infact on table which I need to add new column on it there is useful data that I couldn't lost them. I attached my code as follow:

public class ExternalDbOpenHelper extends SQLiteOpenHelper {

    public static final int DATABASE_NEW_VERSION = 1;
public static final int DATABASE_OLD_VERSION = 1;

public static String DB_PATH;

private static String TABALE_NAME = "font";
private static String FONT_ID = "_id";
private static String FONT_TYPE = "font_type";
private static String FONT_SIZE = "font_size";

public static String DB_NAME;
public SQLiteDatabase database;
public final Context context;

public SQLiteDatabase getDb() {
    return database;
}

public ExternalDbOpenHelper(Context context, String databaseName) {
    super(context, databaseName, null, DATABASE_NEW_VERSION);
    this.context = context;

    String packageName = context.getPackageName();

    DB_PATH = String.format("//data//data//%s//databases//", packageName);
    DB_NAME = databaseName;
    openDataBase();
}


public void createDataBase() {
    boolean dbExist = checkDataBase();
    if (!dbExist) {
        this.getReadableDatabase();
        try {
            copyDataBase();
        } catch (IOException e) {
            Log.e(this.getClass().toString(), "Copying error");
            throw new Error("Error copying database!");
        }
    } else {
        Log.i(this.getClass().toString(), "Database already exists");
    }
}

private boolean checkDataBase() {
    SQLiteDatabase checkDb = null;
    try {
        String path = DB_PATH + DB_NAME;
        checkDb = SQLiteDatabase.openDatabase(path, null,
                SQLiteDatabase.OPEN_READONLY);
    } catch (SQLException e) {
        Log.e(this.getClass().toString(), "Error while checking db");
    }
    if (checkDb != null) {
        checkDb.close();
    }
    return checkDb != null;
}

private void copyDataBase() throws IOException {

    InputStream externalDbStream = context.getAssets().open(DB_NAME);

    String outFileName = DB_PATH + DB_NAME;

    OutputStream localDbStream = new FileOutputStream(outFileName);

    byte[] buffer = new byte[1024];
    int bytesRead;
    while ((bytesRead = externalDbStream.read(buffer)) > 0) {
        localDbStream.write(buffer, 0, bytesRead);
    }
    localDbStream.close();
    externalDbStream.close();

}

public SQLiteDatabase openDataBase() throws SQLException {
    String path = DB_PATH + DB_NAME;
    if (database == null) {
        createDataBase();
        database = SQLiteDatabase.openDatabase(path, null,
                SQLiteDatabase.OPEN_READWRITE);
    }
    return database;
}

@Override
public synchronized void close() {
    if (database != null) {
        database.close();
    }
    super.close();
}

@Override
public void onCreate(SQLiteDatabase db) {


}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

}


public int updateFont(int i, String fontName, int p) {
    SQLiteDatabase db = this.getWritableDatabase();

    ContentValues font = new ContentValues();
    font.put(FONT_TYPE, fontName);
    font.put(FONT_SIZE, p);

    String whereClause = FONT_ID + " LIKE ?";
    String[] whereArgs = new String[] { String.valueOf(i) };

    db.update(TABALE_NAME, font, whereClause, whereArgs);
    return db.update(TABALE_NAME, font, whereClause, whereArgs);
    }
}

Answer 1

There are a few ways, but they all start with increasing your DATABASE_NEW_VERSION.

Then your onUpgrade() method will be called on devices that have a version less than the one specified by DATABASE_NEW_VERSION. If you find yourself making multiple changes over time you can check if the device is multiple versions behind by looking at the oldVersion passed to this method.

The generic solution (if you start renaming tables, columns and generally remapping data) is just read the "old data" from database/table and store it in an ArrayList etc. Then DROP the old table(s), CREATE new table(s), and then INSERT the data.

However, if you're only adding new columns, it can be done simply with:

ALTER TABLE {tableName} ADD COLUMN {columnName} {type};

ALTER TABLE syntax here.