Reporting against the repository data

[bwallis42]

Using a JCR repository in our produt means we will be storing a large amount of our clients data in that repository.

The next thing the customer is going to want is to run reports across that data and they are going to want to use a tool (like jasper or crystal reports) that queries data for the reporting using jdbc or odbc.

Can I do this against a modeshape repository (either 2.8 or 3.0)?

We have had a look at the solution that jackrabbit uses and it is not very nice. As I understnad it (it was explained to me, I haven't looked myself) you have to write java code to extract the data for each report query into an in memory relational db that is then used via the JDBC interface for the reproting tool. It creates and dumps the in-memory representation each time you do this.

thanks,

[bwallis42]

To answer my own question, yes, there is the jdbc driver available in 2.8 that enables the use JCR-SQL2 queries over the JDBC interface. I don't believe that this is working yet in 3.0?

Can someone give me some idea about how well this works? There is nothing nasty in the woodwork about it making in memory copies of the data or results that limit its capabilities?

thanks

[rhauch]

ModeShape's JDBC driver is a lightweight implementation that forwards the submitted query expression (which must be a string containing any valid JCR-SQL2 or ModeShape-extended JCR-SQL2 query) directly to the repository, and then wraps the actual JCR query result with an implementation of JDBC's ResultSet. Because the JDBC ResultSet methods are extracting values from the columns for the current row, there's no "copying of data" in the JDBC layer.

There are actually two driver implementations available as separate Maven artifacts (e.g., JARs):

1. A local JDBC driver that works in the same process as the JCR repository and that supports queries and JDBC metadata. This is what we use when we deploy ModeShape within JBoss AS and create a JDBC DataSource for deployed repositories.
2. A JDBC driver that extends the local-only one and that adds support for remote JDBC metadata (but at this time no remote queries; see MODE-872).

These are available for 2.x, and will very soon be available for 3.0. In fact, I'm working on releasing 3.0.0.Alpha4 which will include the local driver and the JBoss AS7 kit (which will install the driver but does not yet create a JDBC data source yet; see MODE-1481).

Best regards

[bwallis42]

Thanks for that.