ONJava.com -- The Independent Source for Enterprise Java
oreilly.comSafari Books Online.Conferences.


AddThis Social Bookmark Button

Agile Object to Relational Database Replication with db4o
Pages: 1, 2, 3, 4, 5, 6, 7, 8

Configuring for Replication to MySQL

As described above, dRS uses Hibernate to map objects to tables, so we need some configuration files to tell it what classes to map and how to map them. The main configuration file, hibernate.cfg.xml, specifies the database driver and URL (on localhost in this example), database-specific dialect, and the classes mapping file names. The database, drsexample, on the MySQL server is specified in the URL.

<!-- hibernate.cfg.xml -->
<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
                "-//Hibernate/Hibernate Configuration DTD 3.0//EN"

        <!-- Database connection settings -->
        <property name="hibernate.connection.driver_class">
        <property name="hibernate.connection.url">jdbc:mysql://
        <property name="hibernate.connection.username"></property>
        <property name="hibernate.connection.password"></property>

        <!-- JDBC connection pool (use the built-in) -->
        <property name="hibernate.connection.pool_size">1</property>

        <!-- SQL dialect -->
        <property name="hibernate.dialect">

        <!-- Echo all executed SQL to stdout -->
        <property name="hibernate.show_sql">true</property>

        <!-- Update the database schema if out of date -->
        <property name="hibernate.hbm2ddl.auto">update</property>

        <!-- Specify all your data entity classes here -->
        <mapping resource="com/onjava/db4o/drs/Address.hbm.xml"/>  
        <mapping resource="com/onjava/db4o/drs/Customer.hbm.xml"/>  

The two class mapping files, Customer.hbm.xml and Address.hbm.xml, are shown below. These map each class to a table: the tables are called CUSTOMERS and ADDRESSES. Note that Customer.hbm.xml specifies the address field as a participant in a one-to-one relationship.

<!-- Customer.hbm.xml -->
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
                "-//Hibernate/Hibernate Mapping DTD 3.0//EN"

<hibernate-mapping default-access="field" default-lazy="false"
    <class name="com.onjava.db4o.drs.Customer" table="CUSTOMERS">
        <id column="id" type="java.lang.Long">
            <generator class="foreign">
                <param name="property">address</param>
        <property name="title" type="java.lang.String"/>
        <property name="firstName" type="java.lang.String"/>
        <property name="lastName" type="java.lang.String"/>
        <property name="email" type="java.lang.String"/>
        <property name="phoneNumber" type="java.lang.String"/>
        <property name="faxNumber" type="java.lang.String"/>
        <property name="creditLine" type="java.lang.Integer" length="5"/>
        <property name="contactCode" type="java.lang.String"/>
        <one-to-one name="address"/>   

<!-- Address.hbm.xml -->
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
                "-//Hibernate/Hibernate Mapping DTD 3.0//EN"

<hibernate-mapping default-access="field" default-lazy="false"
    <class name="com.onjava.db4o.drs.Address" table="ADDRESSES">
        <id column="id" type="java.lang.Long">
            <generator class="native"/>
        <property name="street1" type="java.lang.String"/>
        <property name="street2" type="java.lang.String"/>    
        <property name="city" type="java.lang.String"/>
        <property name="zip" type="java.lang.String"/>
        <property name="country" type="java.lang.String"/>

Running the Replication

To replicate from the db4o database file to a running MySQL server with an empty database named drsexample, we need to add all of the dRS distribution libraries to a project; import the required Hibernate, db4o, and dRS classes; and run the following code:

ObjectContainer objectContainer = Db4o.openFile("c:/mobileone.yap"); 
Configuration config = new Configuration().configure("hibernate.cfg.xml"); 
ReplicationSession replication = Replication.begin(container, config);
ObjectSet changed = replication.providerA().objectsChangedSinceLastReplication();
while (changed.hasNext()) 

After running this, we can open a MySQL command-line client on the drsexample database, and take a look at what's in the database. There are a number of tables, the most obviously relevant being CUSTOMERS and ADDRESSES . We can query to find out what's in them (for brevity, not all fields are shown here):

mysql> select id,firstname,lastname, email, creditLine from customers;
| id | firstname | lastname       | email             | creditLine |
| 1  | Matt      | Hasselbeck     | matt@seahawks.com |      16000 |
| 2  | Ben       | Roethlisberger | ben@steelers.com  |      25000 |
2 rows in set (0.00 sec)

mysql>  select id,street1,street2, city, zip, country from addresses;
| id | street1         | street2  | city       | zip      | country |
| 1  | 2 Second Street | Uptown   | Seattle    | WA 98200 | USA     |
| 2  | 1 First Street  | Downtown | Pittsburgh | PA 15121 | USA     |
2 rows in set (0.00 sec)

With the few lines of code above, dRS has extracted all the objects from the db4o database, and with the help of Hibernate, has created the appropriate tables in the MySQL database and inserted the field values in the MySQL tables. All of the objects are copied, as this is the first replication of the database. Note the id field values--these did not come from the objects, and are not related to the db4o OIDs. The values have been generated by MySQL as directed by the Hibernate configuration files, and are needed so that the right ADDRESS row can be associated with each CUSTOMER row (remember this was taken care of in db4o by storing object references directly). For example, to get the ADDRESS for Ben Roethlisberger, we could write SQL like this:

select addresses.id, street1,street2, city, zip, country 
    from customers, addresses 
    where customer.id = address.id;

There is no need for a foreign key, as the simple one-to-one relationship means we can just match the ids in each table. For objects with one-to-many relationships, the configuration files can be written so that the required foreign keys are generated.

This is only part of the story, however. Replication requires that the database keeps track of changes to objects so that it only copies those that have been changed. We also need to be able to associate each table row with the correct object in a db4o database. dRS creates some additional fields to help with these requirements. These are the same for every class. The output from the MySQL describe command below shows all of the fields in the Customer table, while the following queries show some of the values stored:

mysql> describe customers;
| Field              | Type         | Null | Key | Default | Extra |
| id                 | bigint(20)   | NO   | PRI |         |       |
| title              | varchar(255) | YES  |     | NULL    |       |
| firstName          | varchar(255) | YES  |     | NULL    |       |
| lastName           | varchar(255) | YES  |     | NULL    |       |
| email              | varchar(255) | YES  |     | NULL    |       |
| phoneNumber        | varchar(255) | YES  |     | NULL    |       |
| faxNumber          | varchar(255) | YES  |     | NULL    |       |
| creditLine         | int(11)      | YES  |     | NULL    |       |
| contactCode        | varchar(255) | YES  |     | NULL    |       |
| drs_uuid_long_part | bigint(20)   | YES  |     | NULL    |       |
| drs_version        | bigint(20)   | YES  |     | NULL    |       |
| drs_provider_id    | bigint(20)   | YES  | MUL | NULL    |       |
12 rows in set (0.01 sec)

mysql>  select lastname, drs_version, drs_provider_id from customers;
| lastname       | drs_version | drs_provider_id |
| Hasselbeck     |           1 |               2 |
| Roethlisberger |           1 |               2 |
2 rows in set (0.00 sec)

mysql> select street1, drs_version, drs_provider_id from addresses;
| street1         | drs_version | drs_provider_id |
| 2 Second Street |         100 |               2 |
| 1 First Street  |         100 |               2 |
2 rows in set (0.00 sec)

drs_version is the version number, which is compared to the db4o object version number when replicating to determine which objects have changed. drs_provider_id identifies the provider, which means the db4o database that provided this object. Both rows here have the drs_provider_id, as they all came from the same source. There is also a field, drs_uuid_long_part (not shown here), which is used by dRS to match a table row to a specific object in the database based on its UUID.

In addition to creating additional fields, dRS creates a set of tables to assist the replication process. These include the following:

  • REPLICATIONPROVIDERSIGNATURE: Contains the actual database identifier part of the UUID for each provider db4o database. The drs_provider_id field in a data table is actually a foreign key that relates a data row to an entry in this table. The MySQL database itself is regarded as a provider too, and has an entry in this table.
  • REPLICATIONRECORD: Contains the current version number associated with each db4o provider database.

Pages: 1, 2, 3, 4, 5, 6, 7, 8

Next Pagearrow