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:

RegistrationRecord.java
01package com.db4odoc.android.compare.refactored; 02 03import java.util.Date; 04 05public class RegistrationRecord { 06 private String number; 07 private Date year; 08 09 public RegistrationRecord(String number, Date year){ 10 this.number = number; 11 this.year = year; 12 } 13 14 public String getNumber() { 15 return number; 16 } 17 18 public void setNumber(String number) { 19 this.number = number; 20 } 21 22 public Date getYear() { 23 return year; 24 } 25 26 public void setYear(Date year) { 27 this.year = year; 28 } 29 30 31 32}

Now we will need to modify Car class to attach the record:

Car.java
01/* Copyright (C) 2004 - 2007 db4objects Inc. http://www.db4o.com */ 02package com.db4odoc.android.compare.refactored; 03 04import java.text.DateFormat; 05import java.text.SimpleDateFormat; 06import java.util.Calendar; 07import java.util.GregorianCalendar; 08 09 10public class Car { 11 private String model; 12 private Pilot pilot; 13 private RegistrationRecord registration; 14 15 public RegistrationRecord getRegistration() { 16 return registration; 17 } 18 19 public void setRegistration(RegistrationRecord registration) { 20 this.registration = registration; 21 } 22 23 public Car(){ 24 25 } 26 27 public Car(String model) { 28 this.model=model; 29 this.pilot=null; 30 } 31 32 public Pilot getPilot() { 33 return pilot; 34 } 35 36 public void setPilot(Pilot pilot) { 37 this.pilot = pilot; 38 } 39 40 public String getModel() { 41 return model; 42 } 43 44 public String toString() { 45 if (registration == null){ 46 return model + "["+pilot+"]"; 47 } else { 48 DateFormat df = new SimpleDateFormat("d/M/yyyy"); 49 return model + ": " + df.format(registration.getYear()); 50 } 51 } 52 53 public void setModel(String model) { 54 this.model = model; 55 } 56 57}

Ok, the application is changed to cater for 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:

Db4oExample.java: selectCarAndUpdate
01public static void selectCarAndUpdate() { 02 ObjectContainer container = database(); 03 if (container != null){ 04 Query query = container.query(); 05 query.constrain(Car.class); 06 query.descend("pilot").descend("points").constrain(new Integer(15)); 07 08 long startTime = System.currentTimeMillis(); 09 ObjectSet result = query.execute(); 10 result.reset(); 11 if (!result.hasNext()){ 12 logToConsole(0, "Car not found, refill the database to continue.", false); 13 } else { 14 Car car = (Car)result.next(); 15 logToConsole(startTime, "Selected Car (" + car + "): ", false); 16 startTime = System.currentTimeMillis(); 17 car.setRegistration(new RegistrationRecord("A1", new Date(System.currentTimeMillis()))); 18 logToConsole(startTime, "Updated Car (" + car + "): ", true); 19 } 20 21 } 22 }

Schema Evolution in SQLite

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

SqlExample.java: upgradeDatabase
1public static void upgradeDatabase(SQLiteDatabase db) { 2 db.execSQL("create table REG_RECORDS (" 3 + "id text primary key," + "year date);"); 4 db.execSQL("CREATE INDEX IDX_REG_RECORDS ON REG_RECORDS (id);"); 5 db.execSQL("alter table " + DB_TABLE_CAR + " add reg_record text;"); 6 7 }

Now we can try to retrieve and update records:

SqlExample.java: selectCarAndUpdate
01public static void selectCarAndUpdate() { 02 SQLiteDatabase db = database(); 03 if (db != null) { 04 long startTime = System.currentTimeMillis(); 05 06 db.execSQL("insert into REG_RECORDS (id,year) values ('A1', DATETIME('NOW'))"); 07 ContentValues updateValues = new ContentValues(); 08 09 // update car 10 updateValues.put("reg_record", "A1"); 11 int count = db.update(DB_TABLE_CAR, updateValues, "pilot in (select id from pilot where points = 15)", null); 12 if (count == 0){ 13 logToConsole(0, "Car not found, refill the database to continue.", false); 14 } else { 15 Cursor c = 16 db.query("select c.model, r.id, r.year from car c, " + 17 "REG_RECORDS r, pilot p where c.reg_record = r.id " + 18 "and c.pilot = p.id and p.points = 15;", null); 19 if (c.count() == 0) { 20 logToConsole(0, "Car not found, refill the database to continue.", false); 21 return; 22 } 23 c.first(); 24 String date = c.getString(2); 25 SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd H:mm:ss"); 26 try { 27 Date dt = sf.parse(date); 28 RegistrationRecord record = new RegistrationRecord(c.getString(1), dt); 29 30 Car car = new Car(); 31 car.setModel(c.getString(0)); 32 car.setRegistration(record); 33 logToConsole(startTime, "Updated Car (" + car + "): ", true); 34 } catch (ParseException e){ 35 Log.e(Db4oExample.class.getName(), e.toString()); 36 } 37 38 } 39 } 40 }

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.