You are here: Platform Specific Issues > Android > Comparison With SQLite > Schema Evolution

Schema Evolution

When a new application development is considered it is important to think about its evolution. What happens if your initial model does not suffice and you need changes or additions? Let's look how db4o and SQLite applications can handle it.

To keep the example simple, let's add a registration record to our car:

private RegistrationRecord registration;
   
public RegistrationRecord getRegistration() {
	return registration;
}

public void setRegistration(RegistrationRecord registration) {
	this.registration = registration;
}
Car.java: Add a new field to the car

Ok, the application is changed to take care for the new class. What about our databases?

Schema Evolution in db4o

db4o supports such schema change on the fly: we can select values and update the new field too:

ObjectContainer container = database();
if (container != null){
	ObjectSet<Car>  result = container.query(new Predicate<Car>(){
		@Override
		public boolean match(Car car) {
			return car.getPilot().getPoints()==15;
		}
		
	});
	if (!result.hasNext()){
		logToConsole(0, "Car not found, refill the database to continue.", false);
	} else {
		Car car = result.next();
		logToConsole(startTime, "Selected Car (" + car + "): ", false);
		startTime = System.currentTimeMillis();
		car.setRegistration(new RegistrationRecord("A1", new Date()));
		logToConsole(startTime, "Updated Car (" + car + "): ", true);
	}			
}
Db4oExample.java: update a car with db4o

Schema Evolution in SQLite

For SQLite database model should be synchronized with the object model:

db.execSQL("CREATE TABLE IF NOT EXISTS REG_RECORDS ("
		+ "id TEXT PRIMARY KEY, year DATE);");
db.execSQL("CREATE INDEX  IF NOT EXISTS  IDX_REG_RECORDS ON REG_RECORDS (id);");
db.execSQL("ALTER TABLE " + DB_TABLE_CAR + " ADD reg_record TEXT;");
SqlExample.java: upgrade schema in SQLite

Now we can try to retrieve and update records:

SQLiteDatabase db = database();
db.execSQL("INSERT INTO REG_RECORDS (id,year) VALUES ('A1', DATETIME('NOW'))");

ContentValues updateValues = new ContentValues();
updateValues.put("reg_record", "A1");
int count = db.update(DB_TABLE_CAR, updateValues,
		"pilot IN (SELECT id FROM " + DB_TABLE_PILOT
				+ " WHERE points = 15)", null);
if (count == 0) {
	logToConsole(0, "Car not found, refill the database to continue.",
			false);
} else {
	Cursor c = db.rawQuery("SELECT c.model, r.id, r.year from car c, "
			+ "REG_RECORDS r, pilot p where c.reg_record = r.id "
			+ "AND c.pilot = p.id AND p.points = 15;", null);
	if (c.getCount() == 0) {
		logToConsole(0,
				"Car not found, refill the database to continue.",
				false);
		return;
	}
	c.moveToFirst();
	String date = c.getString(2);
	
	Date dt = parseDate(date);
	RegistrationRecord record = new RegistrationRecord(c.getString(1),dt);
	
	Car car = new Car();
	car.setModel(c.getString(0));
	car.setRegistration(record);
	logToConsole(startTime, "Updated Car (" + car + "): ", true);
}
SqlExample.java: update a car with SQLite

Conclusion

You can see that schema evolution is much easier with db4o. But the main difficulty that is not visible from the example is that schema evolution with SQLite database can potentially introduce a lot of bugs that will be difficult to spot. For more information see Refactoring and Schema Evolution.