[dna-dev] Sling Applications and Relational Databases

[Randall Hauch]

Very interesting blog that is a use case around federattion with JCR.

Sling Applications and Relational Databases


One topic that periodically comes up is how to integrate data that  
resides relational databases with a Sling application. The use case  
might be that there is legacy data that cannot be migrated but needs  
to displayed in a new (Sling-based) web application. In this post I  
would like to discuss some aspects and possible strategies to  
accomplish this goal.



One essential question for determining a fitting integration strategy  
is if the db rows shall be exposed as resources (in the REST sense).  
Let us start with the case that they shall not be exposed as  
resources. In such a scenario a db row does not have its own URL. A  
typical Sling application in such a scenario would expose the JCR- 
based content as resources, but might add data from the db to the  
presentation of the resources.



On a technical level this could be implemented by creating an OSGi  
bundle that uses JDBC to connect to the database. The bundle would  
then be called by the rendering script through an OSGi service  
interface (as described here).



However, this approach cannot be used if the db rows shall be exposed  
as resources and have their own URL.



For data that changes on low frequencies one can create a JCR node for  
each db row. This requires the ability to keep the db and the JCR in  
sync. The JCR nodes could contain the primary key of the db data.  
Synching can be achieved by any programm capable of JCR and JDBC.



One seemingly obvious way to avoid having to synch is to create a  
dummy node that represents all of the db data. However, the resulting  
URLs would not be resource-oriented at all since you need to pass the  
resource identifier as a request parameter.



The full monty and best solution for making your db URLs RESTful is to  
create a Sling ResourceProvider. This would be an OSGi bundle that  
claims a part of the URL space in Sling and maps the URLs to resources  
of whatever kind. In the Sling samples there is an example  
implementation for file systems. In this example a tree of the local  
file system is mapped onto the URL space and all files are given Sling  
resource types. As such, the normal mechanisms for rendering requests  
are used. The same can be achieved with db rows, of course.



It should be noted that there is a generic problem when the relational  
data needs to be mapped onto the hierarchical URL space. You could go  
the brute force way of using /content/tablename/id. However, if your  
data is somehow categorized using many-to-one relationships consider  
"denormalizing" the data (mentally - you do not need to actually  
change the tables). This would yield nicer URLs like /content/ 
category1/category2/data (e.g. like /content/wagons/mercedes/e-class).


-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.jboss.org/pipermail/dna-dev/attachments/20080730/89d60a34/attachment.html