Manage database schema is always a pain in the arse. I’d like to share with you the way I use to do automate schema evolution and make it a bit easier.
The concept is pretty easy – separate SQL from code and create a tool to automatically run all necessary scripts on every database upgrade.
Let’s start from scripts. I keep all my scripts in the /assets sub-folders as follows:
-
Initial CREATE TABLE scripts in the /create folder. There goes all scripts which are needed to create initial database structure,
-
All CREATE VIEW scripts in /view folder,
-
All incremental scripts are going to /alter folder.
Now to the code. The logic is simple:
-
if there is no database yet, then run all initial scrips from /create folder, then run all incremental scripts from /alter folder, then run all scripts from /view folder to create database views,
-
on every database upgrade, run incremental scripts from /alter folder which haven’t been run before and then run all scripts from /view folder to recreate all database views.
To keep the track of which scripts have already been run, I keep the list in ALTERLOG table:
create table alterlog (script text not null, datetime long not null);
Now ot the code. There is a helper class in the Android SDK which helps with database creation and versioning – SQLiteOpenHelper. I’ve created a subclass:
public class DatabaseSchemaEvolution extends SQLiteOpenHelper {
// some constants and constructor ...
@Override
public void onCreate(SQLiteDatabase db) {
try {
// Creating ALTERLOG table
db.execSQL(ALTERLOG_CREATE);
db.execSQL(ALTERLOG_CREATE_INDEX );
// Running create scripts
runAllScripts(db, CREATE_PATH, false);
// Running alter scripts
runAllScripts(db, ALTER_PATH, true);
// Running create view scripts...
runAllScripts(db, VIEW_PATH, false);
} catch (Exception ex) {
throw new RuntimeException("Failed to create database", ex);
}
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
try {
// Running alter scripts
runAllScripts(db, ALTER_PATH, true);
// Running create view scripts
runAllScripts(db, VIEW_PATH, false);
} catch (Exception ex) {
throw new RuntimeException("Failed to upgrade database", ex);
}
}
private void runAllScripts(SQLiteDatabase db, String path,
boolean checkAlterlog) throws IOException {
// ...
}
}
}
The code does what the algorithm says overriding onCreate and onUpdate methods from the base class. Here is the runAllScripts method:
private void runAllScripts(SQLiteDatabase db, String path,
boolean checkAlterlog) throws IOException {
String[] scripts = sortScripts(assetManager.list(path));
for (String scriptFile : scripts) {
String script = path + ";" + scriptFile;
if (checkAlterlog) {
if (alreadyRun(db, script)) {
// Skip script
continue;
}
}
// Running script
String[] content = readFile(script).split(";");
for (String s : content) {
db.execSQL(sql);
}
if (checkAlterlog) {
saveScriptToAlterlog(db, script);
}
}
}
The method gets all files from the according assets path (we expect to have only sqls there), then sort them and then runs them one by one checking if necessary if a scripts hasn’t been run before.
The sorting is important because it specifies in what order the scripts should run. The order can differ in the subdirectories, but I use natural (alphabetical) sort order and special pattern for the files names: YYYYMMDD_HHMM_*.sql. For example:
20091027_1900_alter_my_table.sql
This way it’s easy to add new scripts to /alter and always know in which order they will run.
So, with your database schema evolve you just add all necessary scripts into /alter (also add/change scripts in /view if necessary) and don’t care more 
Please check the full code for the stuff I missed – like alreadyRun method, etc.
