[dna-dev] Sling Applications and Relational Databases

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).