Getting Started With SQLite Database Model Part 2 – Creating the Database Model

Creating the Database Model

To use SQLite Database Model, we need to create our specific DatabaseModel by simply extending the abstract za.co.neilson.sqlite.orm.DatabaseModel class and implementing all the required methods.

Our DatabaseModel will be responsible for things like:

  • Specifying a name for our database
  • Keeping track of the database version number
  • Specifying which Objects to map to database columns
  • Performing any special actions when the database is first created or upgraded from one version to another
  • Seeding the databases tables with default records when it is first created.
public class CarDatabaseModel extends DatabaseModel<ResultSet, HashMap<String,Object>> {
   
    public CarDatabaseModel() throws SQLException,
            ClassNotFoundException, NoSuchFieldException {
        super((Object[])null);
    }

    ...

The DatabaseModel constructor takes a Object... parameter that is passed through the constructor and on to the onInitializeDatabaseDriverInterface() method for extensibility. When using the JDBC version of SQLite Database Model we don’t need to pass anything. The Android version requires you to pass your Context in to the DatabaseModel constructor.

The first method that we need to implement is onInitializeDatabaseDriverInterface(). When using the JDBC version of SQLite Database Model we simply need to instantiate the JdbcSqliteDatabaseDriverInterface and return it. For android we would return an instance of AndroidSqliteDatabaseDriverInterface.

    ...

    @Override
    protected DatabaseDriverInterface<ResultSet,HashMap<String,Object>> onInitializeDatabaseDriverInterface(Object... args) {      
        return new JdbcSqliteDatabaseDriverInterface(this);
    }

    ...

Registering Your Objects With SQLite Database Model

Creating Generic & Custom Object Models

Every object that the DatabaseModel maps to a database table is mapped through an ObjectModel. For the default behaviour, nothing has to be done except to create a generic ObjectModel for each Object and to put it into the objectModels collection supplied to the method as a parameter.

ObjectModels are the primary extensibility point for creating custom methods for mapping object properties to columns/values in the database.

By overriding methods within an Object’s ObjectModel you can also specify custom column names, create primary & foreign keys, constraints and other manage other column attributes without annotating your Object’s fields.

You can create each individual ObjectModel in a separate class file, nested within your DatabaseModel class or in-line as follows.

    ...

    @Override
    protected void onRegisterObjectModels(HashMap<Type, ObjectModel<?,ResultSet,HashMap<String,Object>>> objectModels) throws ClassNotFoundException, NoSuchFieldException {       
        /*
         * Tables Managed By This Model
         */

        // Register the ObjectModel for the Car class with the DatabaseModel
        objectModels.put(Car.class, new JdbcObjectModel<Car>(this) {});

        // Engine has a foreign key reference to Car and must therefore be added
        // after Car.
        objectModels.put(Engine.class, new JdbcObjectModel<Engine>(this) {});

        // Wheel has a foreign key reference to Car and must therefore be added
        // after Car.
        objectModels.put(Wheel.class, new JdbcObjectModel<Wheel>(this) {});            
       
        /*
         * WheelNut has a foreign key reference to Wheel and must therefore be
         * added after Wheel.
         * The WheelNutModel class contains custom code for mapping the WheelNut's
         * thingaMaJigger complex type property to a column in the WheelNut
         * table
         */

        objectModels.put(WheelNut.class, new WheelNutModel(this));
    }
    ...

Next up, the DatabaseModel requires us to supply it with an ObjectModel for the DatabaseInfo class. For JDBC we return a JdbcObjectModel, for android we use AndroidObjectModel.

    ...

    @Override
    public ObjectModel<DatabaseInfo, ResultSet, HashMap<String, Object>> onCreateDatabaseInfoModel() throws ClassNotFoundException, NoSuchFieldException {     
        return new JdbcObjectModel<DatabaseInfo>(this) {};
    }

    ...

Custom Object Models

To map the WheelNut’s thingaMaJigger complex type property to a column in the WheelNut table we’ll need to create a custom ObjectModel. This is done by extending the appropriate base ObjectModel type, JdbcObjectModel<T> or AndroidObjectModel<T>.

public class WheelNutModel extends JdbcObjectModel<WheelNut> {

    public WheelNutModel(
            DatabaseModel<ResultSet, HashMap<String, Object>> databaseModel)
            throws ClassNotFoundException, NoSuchFieldException {
        super(databaseModel);
    }

    ...

Manually Mapping an Object’s Properties to Columns in it’s Database Table

Here we’re overriding onInitializeObjectModelColumns() to demonstrate how
we can manually create a Model’s ObjectModelColumns to have finer control
over the columns that are created / or mapped to in the database.

The default behaviour of onInitializeObjectModelColumns() is to create ObjectModelColumns for each of the classes supported properties and to create relationships and constraints by inspecting the properties for the presence of any SQLite Database Model annotations.

Overriding onInitializeObjectModelColumns() is appropriate if

  • Your model’s field names do not match your databases column names
  • You need to manually map a complex type to a logical value in a
    custom column
  • You would like to create custom relationships

Generally, where onInitializeObjectModelColumns() has been overridden,
setColumnValue() and getColumnValue() will need to be overridden as well
to ensure that your object’s fields are correctly mapped to your custom
columns.

    ...

    @Override
    protected ObjectModelColumn[] onInitializeObjectModelColumns()
            throws ClassNotFoundException, NoSuchFieldException {
        try {
            objectModelColumns = new ObjectModelColumn[] {
                    new ObjectModelColumn("wheelNutId", int.class, false, true, true),
                    new ObjectModelColumn("wheelId", int.class, false, false, false, "Wheel", "wheelId"),

                    // Refers to a complex type in WheelNut.class to demonstrate
                    // how custom objects can be
                    // mapped to database table columns by overriding
                    // onInitializeObjectModelColumns() in the ObjectModel
                    new ObjectModelColumn("thingaMaJigger", int.class, true) };

    ...

As you can see above, I’ve mapped the WheelNut.thingaMaJigger property to an integer type column the value of which we will use to inflate a thingaMaJigger with the correct state when the WheelNut is retrieved from the database.

Manually Creating Relationships

This relationship would normally be created automatically by onInitializeObjectModelColumns() provided that the @ForeignKey(table = "Wheel", column = "wheelId") attribute is present on the wheelId field.

Another option would be to call super.onInitializeObjectModelColumns() before adding the thingaMaJigger ObjectModelColumn to the ObjectModel.objectModelColumns[] array. I’ve done it manually here to demonstrate how relationships can be added to an ObjectModelColumn.

    ...

            Relationship relationship = new Relationship();
            relationship.setChildType(WheelNut.class);
            relationship.setChildKeyField(WheelNut.class
                    .getDeclaredField("wheelId"));

            relationship.setParentType(Wheel.class);
            relationship.setParentKeyField(Wheel.class
                    .getDeclaredField("wheelId"));

            relationship.setRelationshipType(Relationship.Type.ONE_TO_MANY);

            objectModelColumns[1].getRelationships().add(relationship);

        } catch (UnsupportedTypeException e) {
            e.printStackTrace();
        }
        return objectModelColumns;
    }
    ...

Manually Mapping an Object’s Properties to Database Table Values

Now that we have added the WheelNut.thingaMaJigger property to the Object model’s ObjectModelColumns[] collection we need to define how the ObjectModel should store the state of the property in the column and how it should inflate the thingaMaJigger when a WheelNut record is retrieved from the database.

Here we’re overriding ObjectModel.setColumnValue() to provide the logic for mapping our WheelNut.thingaMaJigger complex type to an integer value in the database table.

If our WheelNut.thingaMaJigger is charged we’ll put a 2 in the thingaMaJigger column in the WheelNut table, if the thingaMaJigger is depleted we’ll assign the column a value of 1. We’re not using 0 as when a nullable integer column is queried, 0 is returned for a null value.

We’ll delegate the column value mapping for the other fields to the superclass

    ...
   
    @Override
    protected Object setColumnValue(Object t,
            ObjectModelColumn objectModelColumn) throws NoSuchFieldException,
            SecurityException, IllegalArgumentException, IllegalAccessException {

        // if the ObjectModelColumn is the thingaMaJigger column, we'll need
        // some custom logic to insert the value of the WheelNut's
        // thingaMaJigger
        // field into the thingaMaJigger int column that we created in our
        // override
        // of ObjectModel.onInitializeObjectModelColumns().
        if (objectModelColumn.getName().equals("thingaMaJigger")) {
            WheelNut.ThingaMaJigger thingaMaJigger = ((WheelNut) t)
                    .getThingaMaJigger();
            if (thingaMaJigger == null) {
                return null;
            } else if (thingaMaJigger.getStatus() == Status.CHARGED) {
                return 2;
            } else {
                return 1;
            }

        } else { // Other ObjectModelColumn values can be mapped using the
                    // default logic
            return super.setColumnValue(t, objectModelColumn);
        }
    }
    ...

Manually Inflating an Object’s Properties from Database Table Values

Here we’re overriding ObjectModel.getColumnValue() to provide the logic for mapping an integer value in the database table the appropriate value for the WheelNut.thingaMaJigger complex type property.

If the WheelNut table record’s thingaMaJigger column value is 1, the WheelNut should be inflated with a thingaMaJigger with a charged status otherwise the WheelNut’s thingaMaJigger’s status should be depleted.

We’ll delegate the column value mapping for the other fields to the superclass

    ...
   
    @Override
    protected Object getColumnValue(ResultSet resultSet,
            ObjectModelColumn objectModelColumn,
            int resultSetObjectModelColumnIndex) throws NoSuchFieldException,
            SecurityException, SQLException {

        // if the ObjectModelColumn is the thingaMaJigger column, we'll need
        // some custom logic to initialize the WheelNut's thingaMaJigger field.
        if (objectModelColumn.getName().equals("thingaMaJigger")) {

            // retrieve the value from the ResultSet for this ObjectModelColumn
            int value = resultSet.getInt(resultSetObjectModelColumnIndex);

            switch (value) {
            case 1: // If the value is 1, we know the WheelNut has a DEPLETED
                    // ThingaMaJigger
                return new WheelNut.ThingaMaJigger(Status.DEPLETED);
            case 2: // If the value is 2, we know the WheelNut has a CHARGED
                    // ThingaMaJigger
                return new WheelNut.ThingaMaJigger(Status.CHARGED);
            default: // The only other value we should get here is 0, indicating
                        // a null value in the table.
                return null;
            }

        } else { // Other ObjectModelColumn values can be mapped using the
                    // default logic
            return super.getColumnValue(resultSet, objectModelColumn,
                    resultSetObjectModelColumnIndex);
        }
    }

}

Upgrading Your Database to a New Version

Other methods that the DatabaseModel must implement are getDatabaseName() to specify a file name for the database generated / existing database and getDatabaseVersion().

    ...
    /* (non-Javadoc)
     * @see za.co.neilson.sqlite.orm.DatabaseModel#getDatabaseName()
     */

    @Override
    public String getDatabaseName() {
        return "Cars.db";
    }

    /* (non-Javadoc)
     * @see za.co.neilson.sqlite.orm.DatabaseModel#getDatabaseVersion()
     */

    @Override
    public int getDatabaseVersion() {
        return 1;
    }

    /* (non-Javadoc)
     * @see za.co.neilson.sqlite.orm.DatabaseModel#onUpgrade(int)
     */

    @Override
    protected void onUpgrade(int previousVersion) throws SQLException {
        super.onUpgrade(previousVersion);
    };
    ...

When SQLite Database Model detects that the database version number in an existing database is lower than the number provided here, onUpgrade() will be called by the DatabaseModel constructor.

The default behaviour of the onUpgrade() method is to drop the existing tables and to re-create them using the ObjectModels registered in the onRegisterObjectModels() method. If you don’t want this to happen do not call super.onUpgrade(previousVersion); in your onUpgrade() override.

Seeding Your Database With Default Records

To seed your database with default values, override DatabaseModel.onInsertDefaultValues(), a method called after onCreate() and after the default implementation of onUpgrade() which drops and re-creates the databases tables.

When onInsertDefaultValues() is called, the DatabaseModel is fully initialized and ready to work, so any SQLite Database Model functionality that you might normally use to manipulate the data in the database can be used here.

For the sake of this demonstration let’s seed the Car database with a few default car, engine & wheel records.

    ...
    /* (non-Javadoc)
     * @see za.co.neilson.sqlite.orm.DatabaseModel#onInsertDefaultValues()
     */

    @Override
    protected void onInsertDefaultValues() {

        Car polo = new Car();
        polo.setMake("Volkswagen");
        polo.setModel("Polo");
        polo.setMileage(60149);
        polo.setManufacturedDate(Date.valueOf("2010-06-05"));
        polo.setRegistration("BM52ZVGP");
        polo.setRegistered(false);

        polo.setWheels(new ArrayList<Wheel>(4));
        for (int i = 0; i < 4; i++) {
            Wheel wheel = new Wheel();
            wheel.setSize(18);
            /*
             * Since we have specified Car's "wheels" property as the
             * parentReference in Wheel's foreign key relationship to car, all
             * we need to do to add the wheels to the database is to add them to
             * the car's wheels collection and insert the car. We don't even
             * need to set the wheel's carRegistration property
             */

            polo.getWheels().add(wheel);
        }

        try {
            getObjectModel(Car.class).insert(polo);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        ...

To add a car to the database all we have to do is construct the Car, set it’s properties and pass it as a parameter to the insert(T) method of the Car ObjectModel.

Since we have specified Car’s “wheels” property as the parentReference in Wheel’s foreign key relationship to car, all we need to do to add the wheels to the database is to add them to the Polo’s wheels collection and insert the Polo. We don’t even need to set the wheel’s carRegistration property to the same registration as the polo, this is done for us internally.

Next, lets insert a Mazda 3, and this time lets give it an an engine:

        ...
        Car mazda = new Car();
        mazda.setMake("Mazda");
        mazda.setModel("3");
        mazda.setMileage(48526);
        mazda.setManufacturedDate(Date.valueOf("2012-03-17"));
        mazda.setRegistration("FS13TWGP");
        mazda.setRegistered(true);

        Engine mazdaEngine = new Engine();
        mazdaEngine.setFuel(Fuel.PETROL);
        mazdaEngine.setPower(77);
        mazdaEngine.setTorque(145);
        mazdaEngine.setEngineCapacity(1.6F);
        mazda.setEngine(mazdaEngine);

        mazda.setWheels(new ArrayList<Wheel>(4));
        for (int i = 0; i < 4; i++) {
            Wheel wheel = new Wheel();
            wheel.setSize(18);
            mazda.getWheels().add(wheel);
        }

        try {
            getObjectModel(Car.class).insert(mazda);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        ...

Since we have specified Car’s “engine” property as the parentReference in Engine’s one-to-one foreign key relationship to car, all we need to do is set the Mazda’s engine property to a reference to the new mazdaEngine and SQLite Database Model will set the engine’s carRegistration to the same registration as the Mazda and insert it for us.

Lastly, let’s insert a Ford Ranger. This time with an engine, wheels and wheel nuts:

        ...
        Car ford = new Car();
        ford.setMake("Ford");
        ford.setModel("Ranger");
        ford.setMileage(162276);
        ford.setManufacturedDate(Date.valueOf("2011-07-26"));
        ford.setRegistration("PZ56XTGP");
        ford.setRegistered(true);

        Engine fordEngine = new Engine();
        fordEngine.setFuel(Fuel.PETROL);
        fordEngine.setPower(147);
        fordEngine.setTorque(470);
        fordEngine.setEngineCapacity(3.2F);
        ford.setEngine(fordEngine);

        ford.setWheels(new ArrayList<Wheel>(4));
        for (int i = 0; i < 4; i++) {
            Wheel wheel = new Wheel();
            wheel.setSize(18);
            ford.getWheels().add(wheel);
        }

        try {
            getObjectModel(Car.class).insert(ford);
        } catch (SQLException e) {
            e.printStackTrace();
        }

        try {
            ObjectModel<Wheel,?,?> wheelModel = getObjectModel(Wheel.class);
            ObjectModel<WheelNut,?,?> wheelNutModel = getObjectModel(WheelNut.class);
            for (Wheel wheel : wheelModel
                    .getAll("carRegistration = ?","PZ56XTGP")) {
                for (int i = 0; i < 5; i++) {
                    WheelNut wheelNut = new WheelNut();
                    wheelNut.setWheelId(wheel.getWheelId());
                    wheelNut.setThingaMaJigger(new ThingaMaJigger(
                            i % 2 == 0 ? Status.CHARGED : Status.DEPLETED));
                    wheelNutModel.insert(wheelNut);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

As previously stated, we created the WheelNut class without a reference to it’s wheel, and the Wheel class without a Collection property for it’s WheelNuts. If we had done so, and decorated the WheelNut’s wheelId property with the annontation @ForeignKey(table = "Wheel", column = "wheelId", parentReference="wheelNuts", childReference="wheel") we could simply add the WheelNuts to the Wheel’s Collection<WheelNut> wheelNuts collection and then inserting the Ford would automatically insert it’s engine, wheels and all the wheels’ wheel nuts.

However, in order to demonstrate how this can be done manually. I’ve used the Wheel’s ObjectModel’s getAll() method to retrieve a List of the Ford’s wheels. I’ve then looped through the list of wheels and manually inserted 5 WheelNuts for each Wheel, making sure to set the each WheelNut’s wheelId property appropriately.


Getting Started With SQLite Database Model Part 1 – Defining Your Object Models

Getting Started With SQLite Database Model Part 2 – Creating the Database Model

Getting Started With SQLite Database Model Part 3 – Using SQLite Database Model to Store, Query and Manipulate Your Data