3

We currently have the following task ahead, and I'm struggling to find what I believe should be out there somewhere.

A datamodel is stored in MySQL via Hibernate. Everything works fine and the product is shipped.

For a planned future version though changes to the datamodel are required. We do not yet know what changes and how drastically, but we do know that customer databases have to be updated accordingly.

This will most likely happen again on future versions of the future version, which brings me to part one of the question:

  • Is it reasonable to solve this problem by converting existing database contents via exporting them to an XML file, based on the Hibernate version they have been created with and then update the contents (f.ex. with XSLT), before rewriting the database contents with the updated Hibernate version?

I can see the following advantages of this approach:

  • We have to write one XSLT once for a new version.
  • We can easily apply this to all customers - even automated.
  • We can easily transform database contents from version X to version Y, even if there are several versions in-between, by chaining the corresponding XSLTs.

The actual problem, however, assuming this approach will be followed, is that we do not want to write additional code for generating the corresponding XML files. Optimally, I'd like to use a database that stores its contents in a XML file and provides a JDBC interface. Does anyone here know of how to achieve this with the minimal effort?

The workflow I have in mind would be the following:

  • Use Hibernate datamodel from version X. Create two database connections, one to MySQL DB, one to XML DB. Read out everything from the first, dump it into the later (this requires a little custom code probably)
  • Write the XSLT, execute it on the XML DB's XML file.
  • Use Hibernate datamodel from version Y. Create two database connections, one to MySQL DB, one to XML DB. Read out everything from XML and dump it back into MySQL.

Feasible? Is there a totally better way to solve the problem? And where can I find a JDBC-supporting XML database?

Improve this question

3 Answers 3

Reset to default
1
+100

An easy solution could be to to a mysqldump --xml ( http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_xml ), which generates data like:

<mysqldump xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<database name="world">
  <table_data name="City">
    <row>
      <field name="ID">1</field>
      <field name="Name">Kabul</field>
      <field name="CountryCode">AFG</field>
      <field name="District">Kabol</field>
      <field name="Population">1780000</field>
    </row>

You can then transform this data and load it again table per table using 'LOAD XML INFILE' http://dev.mysql.com/doc/refman/5.5/en/load-xml.html.

As an alternative, I heard some colleagues having nice results with Liquibase: a database refactoring tool. This allows you to script your database migrations in a descriptive way using refactoring commands as listed in http://www.liquibase.org/manual/refactoring_commands.

For example:

  <databaseChangeLog>
  <changeSet id="1" author="greyfairer>  
  <addLookupTable
        existingTableName="address" existingColumnName="state"
        newTableName="state" newColumnName="abbreviation"
        constraintName="fk_address_state"
    />

Liquibase also keeps track of all changesets that were ever applied in a dedicated changeset table. So if you had changesets 2a and 2b applied in version 2 of your product, and 3a and 3b for version 3, Liquibase can detect that e.g. they all 4 need to be applied if upgrading from version 1, and only 3a and 3b for version 2, because 2a and 2b are already in the changeset table.

Granted, these solutions do not reflect your domain model, but sometimes it's easier working in database terms rather than in domain terms when doing such migrations.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thanks. That's pretty much what I was looking for. Not yet exactly, but certainly worth persuing further.
1

I do not think it would make any sense for XML databases (like eXist) to implement JDBC, since JDBC is directly based on dealing with SQL and relational model. Further, I am not sure I see what the planned benefit of XML is at all; XSLT is ok transformation language for XML content. But in this case what you have at most is XML wrapping of relational data, not tree-formed deeply nested textual data. So you might be better off just writing Java code to do changes, row by row. This is a fairly common practice.

You can of course use XML or JSON as the intermediate storage/buffering format, i.e. handle import and export. But processing in-between does not necessarily benefit from XSLT or temporary database.

Improve this answer

2 Comments

I am specifically looking for an approach that scales well. How do you deal with this problem, when it happens with several versions after each other? It is easy to chain XSLTs, but different java programs with different versions of the same libraries.. I do not see how that would work well?
Usually new transformations are just additional classes for cases I have seen -- or even just a huge file, with various switch statements. Not elegant, but depending on amount of changes, doable. I can see why set of XSLTs would seem like a good choice tho, from that perspective.
0

The better way to accompolish this is not to use Hibernate IMO.

We had a solution using nHibernate and SqlServer so your solution will be different but I'm sure relevant tools could be applied to achieve the same goal.

To upgrade from R1 to R1.1 we'd generate the new schema into an empty 'build' db using the nHibernate mapping files (say db1.1). We'd then take a copy of the current release (db1.0) and used a db schema tool (we used DbGhost) to then upgrade db1.0 to db1.1.

DbGhost (and other similar tools) will automatically add for non-null additions and new tables and provide injection points for custom sql for each set of wholesale changes.

It worked a dream and worked in our multi-developer, mutli-environment just fine. A real bonus was that for final release the DbGhost call could be changed to produce a single upgrade scipt summarizing all the chnages.

Hope it helps.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.