XML and relational databases

A problem I seem to keep to getting asked for help with is how to persist XML data to a relational database. (I’ve now been asked to help on at least four different projects where this has become something of an issue/blocker although strangely I’ve never actually needed to do this myself)

The context of this is usually services – I’ve seen this in REST and SOAP based services – not a debate I’m going to enter into here.
Note that this post is concerned with traditional relational databases and does not cover using the XRX (XForms, REST, Xquery) model or XML databases such as MarkLogic or eXist.
The first thing I recommend doing is to ensure that you have an XML schema. I think that it’s good practice and makes you think about the design of your XML structures. If you’re more familiar with designing databases or programmatic data structures then this will help you think about your XML in the same way and save you problems later on. It’s all too easy to end up with the same element name used for different things or common data structures with slightly different names if you just use well-formed XML.

What’s the best way to do this? Well as usual it depends…

Java driven approach

Here the starting point is your java beans.
You can use annotations for both JPA, to define the object relational mapping, and JAXB to define the object XML mapping.
http://www.oracle.com/technetwork/articles/marx-jse6-090753.html

XML driven approach

Here the XML is the primary component. Information about the structure of the data is maintained in the schema together with the JPA annotations.
Using the schema annotations it is possible to customize the way that the java beans are generated.
This is a nice approach because all the information about the structure of the data is held in the same place and everything can be easily regenerated and you don’t have to worry about losing any changes made to the generated classes.
You will, of course, need to ensure that your database schema stays in sync with the JPA definitions.
Option #1 – Hyperjaxb3

https://github.com/highsource/hyperjaxb3

Option #2 – Use Dali to map your POJOs to Database (JPA)

The Eclipse Dali tool provides tooling to easily map your POJOs to a relational database using JPA:

http://www.eclipse.org/webtools/dali/

Option #3 – Use EclipseLink

EclipseLink provides both JPA and JAXB implementations. The JAXB implementation (MOXy) contains extensions specifically for handling JPA entities:

http://wiki.eclipse.org/EclipseLink/Examples/MOXy/JPA
Other links
http://www.slideshare.net/shaunmsmith/restful-services-with-jaxb-and-jpa
http://blog.hma-info.de/2008/05/15/hyperjaxb-3-the-fastest-way-from-xml-to-db/

http://blogs.bytecode.com.au/glen/2010/07/29/from-wsdl-to-jaxb-to-jpa-with-a-single-schema–adventures-in-hyperjaxb3.html

XML driven approach – a variation

An alternative approach to annotating the XML schema is to use aspect orientated programming and place the JPA annotations in the AspectJ files (a bit like how Spring Roo works)

Database driven approach

In this approach the database is the most important component in the system – this is a good approach if you’ve got an existing, stable database and want to be able to quickly add XML capabilities e.g. to provide a service based interface

Create JPA beans

There are a number of ways to do this – you can of course write the beans by hand but it’s easier to generate them. You can do this using Spring Roo (see earlier blog post) or Eclipse Dali

Mapping JPA to XML

The obvious way to map between XML and Java beans is to use JAXB.
One approach is to generate the JAXB beans and write a custom mapping to the JPA beans however there is a better way.
If you use the MOXy JAXB implementation then there are some extensions which you can use :
• XPath Based Mapping
• JPA Entities to XML – Bidirectional Relationships
These extensions allow you to use annotations on your JPA beans to describe your JAXB mappings.
N.B. If you regenerate your JPA beans then these annotations will be lost – a major disadvantage to this approach

A detailed example of how to do this is at:
http://bdoughan.blogspot.com/2010/08/creating-restful-web-service-part-15.html

The common approach

I’ve called in this because, in my experience, this is what most java coders do. In fact this covers several different approaches but at heart they are very similar.

Parse the XML into java beans

Lots of ways to do this – JAXB, using DOM or SAX by hand, if you’ve a well written WSDL file for a SOAP service then this will be part of the generated code.

Persist the java beans

Again lots of ways to do this – hand written JDBC calls, Hibernate…

The simple approach

This is a simple approach which might be sufficient if performance is not too much of an issue.
This could be a good approach if you’ve got some relatively complex XML, which you want to store, but are not interested in much of the actual content of the XML.
It is easy enough to store XML documents as BLOB and all the signification databases now support XPATH querying of the data (Check your database for specifics of how to do this e.g. MySQL at http://dev.mysql.com/tech-resources/articles/xml-in-mysql5.1-6.0.html#xml-5.1-xpath)
If you are using this approach then it’s probably a good idea to use stored procedures so that if you need to migrate to a more structured data model then this can be accomplished with minor application changes.
This approach can be enhanced by extracting specific elements into structured columns so that if there is a particular element that you want to query you can do it in the traditional manner.

ETL Tools

If you’ve got the tools and the experience in using them then ETL tools can provide a graphically driven interface to produce the mapping between the XML and the database schema. Talend, Oracle Data Integrator, IBM Data Stage, Informatica etc all provide this and can expose service endpoints for interactions. If you’ve got the tools and expertise in using them then it’s worth considering this approach.

I think this post is probably long enough for now…

Orbeon upgrade

Notes on upgrading from Orbeon Forms 3.8 to 3.9

Need to change theme.xsl as follows:

From:
<!– Copy body attributes –>
<xsl:apply-templates select=”/xhtml:html/xhtml:body/@*”/>

<xsl:apply-templates select=”/xhtml:html/xhtml:body/node()”/>

<xsl:for-each select=”/xhtml:html/xhtml:head/xhtml:script”>
<xsl:element name=”xhtml:{local-name()}” namespace=”{namespace-uri()}”>
<xsl:apply-templates select=”@*|node()”/>
</xsl:element>
</xsl:for-each>

To:
<!– Body –>
<xsl:apply-templates select=”/xhtml:html/xhtml:body”/>
<!– Handle post-body scripts if present. They can be placed here by oxf:resources-aggregator –>
<xsl:apply-templates select=”/xhtml:html/xhtml:script”/>

Use of xxforms:get-remote-user (xforms:setvalue)

From:
<xforms:setvalue ref=”instance(‘ins-current-user’)/name” value=”xxforms:get-remote-user()”/>

To:
<xforms:setvalue ref=”instance(‘ins-current-user’)/name/text()” value=”xxforms:get-remote-user()”/>

Ongoing is the use of XBL – the map component in particular
(Fixed in the final release)

Ubuntu virtual box unable to log in

Rather carelessly I filled up my root partition and was therefore unable to login as I kept getting thrown back to the login screen

The solution was to boot from the original ubuntu iso image via the virtual box settings and ‘Try Ubuntu’

From there it’s possible to mount /dev/sda1 and delete some files to make some space

After that it’s possible to login again and create a new disk/partition in the usual fdisk, mkfs way

Maven settings for deployment using scp

Create a file settings.xml in ~/.m2 that’s C:Documents and Settingsiwright.m2 in my case using cygwin on XP

Create a server entry with the necessary info e.g. if using ssh
<servers>
<server>
<id>cggh</id>
<username>iwright</username>
<privateKey>C:Documents and Settingsiwright.sshid_rsa</privateKey>
<passphrase>mypassphrase</passphrase>
<directoryPermissions>775</directoryPermissions>
<filePermissions>664</filePermissions>
</server>
</servers>

Note that if you are using cygwin then you will probably need to copy your .ssh directory from /cygwin/home/username to Documents and Settingusername

Run a deploy command
mvn verify install:install deploy:deploy

If the deployment repository isn’t set in the pom then you can also define it using:
-DaltDeploymentRepository=cggh::default::scp://cloud1.cggh.org/var/www/maven

(Note the id cggh must match the id in settings.xml otherwise the permissions won’t be resolved from the settings.xml)

Using Roo on an existing database

Creating a web application to update an existing database with Roo is extremely simple…

project –topLevelPackage org.cggh.quac –projectName quac –java 6
persistence setup –provider HIBERNATE –database MYSQL –databaseName drupal_users –userName drupal6 –password drupal6
database introspect –schema drupal_users
database reverse engineer –package ~.model –schema drupal_users
controller all –package ~.web

Some notes:
Make sure that the database has foreign keys set up
If you want security then run:
security setup

For password encoding:
(See https://jira.springsource.org/browse/ROO-1133)
Add the following to src/main/resources/META-INF/spring/applicationContext.xml
<bean id=”passwordEncoder” class=”org.springframework.security.authentication.encoding.MessageDigestPasswordEncoder”>
<constructor-arg value=”MD5″ />
</bean>

Add the following to the Users class:
@Autowired
@Transient
transient private MessageDigestPasswordEncoder passwordEncoder;

private String pass;

public void setPass(String password) {
if(password==null||password.equals(“”)) {
return; //don’t update password if blank is sent
}
String encodedPassword = passwordEncoder.encodePassword(password, null);
this.pass = encodedPassword;
}

Tomcat JNDI configuration for auto redeploy

With thanks to http://www.mail-archive.com/users@tomcat.apache.org/msg74521.html

Although it’s deprecated putting the context in server.xml seems to work quite nicely
however if you want to auto deploy e.g. on a CI server then you need to get a bit cleverer….

Don’t keep the application in the appBase – locate it outside of
Tomcat’s directory structure, and use the docBase attribute of the
element to tell Tomcat where to find it. Replace the .war file at your
leisure, and then do a touch of the .xml file to trigger an application redeployment

1) I have created a folder with absolute path:
##war directory##
2) In this folder I have placed my WAR file, ##Application##.war
3) In apache-tomcat-6.0.20/conf/Catalina/localhost
I have placed a file called ##Application.xml## (contents of file below).



<?xml version="1.0" encoding="UTF-8"?>

<Context docBase="##war directory##/##Application.war##">
        <Resource name="jdbc/postgres" auth="Container"
                type="javax.sql.DataSource"
driverClassName="org.postgresql.Driver"
                url="jdbc:postgresql://localhost:5432/XXXXXX"
                username="YYYYYY" password="ZZZZZZ" maxActive="20"
maxIdle="10"
                maxWait="-1"/>
</Context>

Fun with classloaders

Class loaders are one of those things which work most of the time and you don’t have to worry about but when they go wrong they can be a bit of a nuisance to sort out.

When you are running a java application in a application server there are several class loaders in play e.g. for tomcat see http://tomcat.apache.org/tomcat-6.0-doc/class-loader-howto.html
Most of the time this is what you want and any other behaviour would cause problems – think Singletons – however sometimes you are trying to be clever and using a method like Class.forName or ClassLoader.getResource in which case you need a better grasp on what is happening.

In this case you can use Thread.currentThread().getContextClassLoader() or Thread.currentThread().getClass().getClassLoader() as well as Class.getClassLoader to access the different class loaders and hopefully the class/resource which you are after.