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; }
Ok, the application is changed to take care for the new class. What about our databases?
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); } }
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;");
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); }
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.