${run.doctype} DBMT

DBMT - User guide.

Home -- versión en castellano del manual

1.- Configuring the XML file.

A migration is described in a XML file containing all steps that should be followed. Although this XML file is quite easy, it can be annoying to write, specially if the migration has many steps, so contact us if you want to write a GUI to perform this task :-).
Anyway, a XML file should be something like this:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE migration SYSTEM "http://dbmt.sourceforge.net/dtds/migration_1_0.dtd">
<migration>

  <databank>
    <source
      url="jdbc:mysql://127.0.0.1/DATABASE_SRC?user=USERNAME&password=PASSWORD"
      driver="org.gjt.mm.mysql.Driver"
    />
    <target
      url="jdbc:postgresql://destiny/DATABASE_TGT?user=USERNAME&password=PASSWORD"
      driver="org.postgresql.Driver"
    />
  </databank>

  <functions>
      <function name="countrows"
                sql="select count(*) from source_table where id > 0" />
      <function name="count_greater_rows"
                sql="select count(*) from source_table where id > ${current.id}" />
  </functions>

  <steps>
    <step name="test_migration"
          source_table="source_table"
          target_table="target_table"
          clear_target="true"
          output="file"
          sliced="true"
          slice_size="1"
          slice_key="Id"           
     >
      <field from="id" to="item" type="int" value=""/>
      <field from="datum" to="post-date" type="date" value=""/>
      <field from="now" to="then" type="timestamp" value=""/>
      <field from="somethingbig" to="athing" type="blob" value=""/>      
      <field from="text" to="description" type="string" value=""/>
      <field from="" to="user" type="new-string" value="anonymous"/>
      <field from="" to="today" type="new-date" value="2002-11-25"/>
      <field from="" to="intvalue" type="new-int" value="0"/>
      <field from="" to="count" type="new-int" value="${countrows}"/>     
    </step>
  </steps>

</migration>

You can download a documented version of this example here

1.1.- The `databank` tag.

In this tag we are defining the databases used as source and target for this migration, giving the URL for the connection and the name of the JDBC driver to be used. Pay attention that JDBC drivers are not included in DBMT's distribution, so you should add them to your classpath.
Of course this tag is mandatory.

  <databank>
    <source
      url="jdbc:mysql://127.0.0.1/DATABASE_SRC?user=USERNAME&password=PASSWORD"
      driver="org.gjt.mm.mysql.Driver"
    />
    <target
      url="jdbc:postgresql://destiny/DATABASE_TGT?user=USERNAME&password=PASSWORD"
      driver="org.postgresql.Driver"
    />
  </databank>

As we will see, DBMT can write the whole insert statements to a text file instead of directly executing them in the target database, which is very useful if both machines are not accesible at the same time or place. Even in this case you should provide DBMT with a source and a target connection, althoug since no SQL insert sentences will be executes, you can simply repeat the information of the source database.

1.2.- The `functions` tag.

The next part of a migration are the functions. Functions are SQL statements which result can be used to set new files in the target table.
This tag is optional. Most of the times you won't need functions.

  <functions>
      <function name="countrows"
                sql="select count(*) from source_table where id > 0" />
  </functions>

Sometimes we'll need to execute these functions based on the values of the current row in the migration. This can be done using ${current.FIELDNAME} instead of the field name.

      <function name="count_greater_rows"
                sql="select count(*) from source_table where id > ${current.id}" />

In this example, the select clause will vary for every row being migrated, taken the current value of the field id for every row.

1.3.- The `steps` tag.

Steps are the most important part of a migration and in every step it is defined how the fields of a table should be changed to fit in the new schema.
We can write so many step tags as tables we want to migrate.

  <steps>
    <step name="test_migration"
          source_table="source_table"
          target_table="target_table"
          clear_target="true"
          output="file"
          sliced="true"
          slice_size="1"
          slice_key="Id"                     
     >
       FIELDS
    </step>
  </steps>

First we have setted some configuration data: name, source_table, target_table, clear_target and output. The first three are pretty clear, the fourth indicates if data from the target table should be removed at the beginning of the migration, and the fifth says what kind of output to produce. This output attribute can take three values:

db file both

With db data will be inserted in the target database, while with file a text file with the name STEPNAME.sql will be produced with all insert statements using the new schema. Obviously both will produce both kinds of output.

If our table is big enough to avoid a complete select of the data and our driver support JDBC 2.0, we can split table's data into slices to do the migration in smallers steps setting the value of sliced to true. Then we can use slice_size and slice_id to configure this slices.

Next we have to write how the fields will be processed. We can map a field of a given type from the old schema to another in the new one, leaving the attribute value empty and entering the correct type. Till now only string, date, int, timestamp and blob are allowed, mainly because till now we didn't need more, it should be pretty simple to add new ones.

      <field from="id" to="item" type="int" value=""/>
      <field from="datum" to="post-date" type="date" value=""/>
      <field from="text" to="description" type="string" value=""/>
      <field from="now" to="then" type="timestamp" value=""/>
      <field from="somethingbig" to="athing" type="blob" value=""/>

If the new schema has some new fields, we can set them with fixed leaving the from empty and adding the text new- at the beginning of the type, setting the value in the value attribute.

      <field from="" to="user" type="new-string" value="anonymous"/>
      <field from="" to="today" type="new-date" value="2002-11-25"/>
      <field from="" to="intvalue" type="new-int" value="0"/>
      <field from="" to="then" type="new-timestamp" value=""/>

For new values we can also use the already defined functions, giving function's name in the attribute value as follows:

      <field from="" to="count" type="new-int" value="${FUNCTIONNAME}"/>

2.- Running.

Well... pretty simple, run the class org.javahispano.dbmt.MigrationRunner giving your XML file as parameter.
As with every java program, you will have to add all needed classes to the classpath, for example the JDBC drivers of your DBMS and those of the XML parser (crimson and JAXP are included in directory "lib").

        *nix:
          java -cp dbmt.jar:MORE_JARS org.javahispano.dbmt.MigrationRunner migration.xml
        Windows:
          java -cp dbmt.jar;MORE_JARS org.javahispano.dbmt.MigrationRunner migration.xml

3.- License.

DBMT is distributed under the GNU General Public License (GPL)