Exposing DB Data

[Andreas Kollegger <an...@kollegger.name>]

Hi,

I'd like to expose data stored in an external database as resource  
nodes in Sling. Access to the database is already wrapped in an OSGi  
service (MyService.java), and there is a Java model for the data  
(MyModel.java). From what I can understand from reading the examples,  
searching the mailing list and looking at documentation, the steps to  
integrate with Sling could be:

1. MyResourceProvider implements ResourceProvider
	- bridge to MyService for getting data rows
	- provider.roots = /my/resources/
	- sub-path of root used to select row in table (/my/resources/ 
3245.html retrieves row index 3245 as Resource)
	- listChildren() of root would list all database rows (probably add  
query params to limit number of rows returned)
	- getResource() returns a MyResourceWrapper

2. MyResourceWrapper implements Resource
	- wraps MyModel
	- resourceType = my/Model
	- adaptable to javax.jcr.Node (exposing columns as node properties)

That would take care of read access to the data. I can then easily add  
scripts to render the data as appropriate.

Write access is a little less clear to me. I'd like to have a servlet  
which handles json, xml or form POSTs for anything submitted to the  
path of my ResourceProvider. I think I need to do this:

1. MyResourceTypeProvider implements JcrResourceTypeProvider
	- provider.roots = /my/resources
	- any node below the root is returned as resourceType my/Model,  
otherwise return null

2. MyPostServlet extends SlingAllMethodsServlet
	- registered to handle nodes of type my/Model
	- override doPost() to handle data based on content-type
	- creates new database entries using MyService

That would allow the creation of a new database row by posting an  
appropriate json or xml file to /my/resource/*.html . For the form  
data, I'd conform to the SlingPostServlet interface to remain  
compatible with generic node editing.

Am I thinking about this correctly, or is their a more appropriate  
Sling-Way?

Thanks for your thoughts,
Andreas
  

Re: Exposing DB Data

[Andreas Kollegger <ak...@tembopublic.org>]

>> Write access is a little less clear to me. I'd like to have a servlet
>> which handles json, xml or form POSTs for anything submitted to the  
>> path
>> of my ResourceProvider. I think I need to do this:
>>
>> 1. MyResourceTypeProvider implements JcrResourceTypeProvider
>>    - provider.roots = /my/resources
>>    - any node below the root is returned as resourceType my/Model,
>> otherwise return null
>>
>> 2. MyPostServlet extends SlingAllMethodsServlet
>>    - registered to handle nodes of type my/Model
>>    - override doPost() to handle data based on content-type
>>    - creates new database entries using MyService
>>
>> That would allow the creation of a new database row by posting an
>> appropriate json or xml file to /my/resource/*.html . For the form  
>> data,
>> I'd conform to the SlingPostServlet interface to remain compatible  
>> with
>> generic node editing.
>>
>> Am I thinking about this correctly, or is their a more appropriate
>> Sling-Way?
> Hmm not sure :) I guess your idea should work. Now, for updating db  
> data
> it should already work if your resource is adaptable to  
> PersistableValueMap.
>
> For adding/deleting resources something like the above might work. We
> are thinking of extending the resource provider interface to get
> better support for these use cases. This is one of the topics for the
> near future, so maybe we should start thinking about this (again)  
> now :)


The PersistableValueMap seems like a good way to go. If I extend  
MyResourceProvider to allow creation of MyResources from a node, I  
should then consider adding a SlingPostProcessor instead, which would  
use MyResourceProvider.createResourceForNode(n) for any nodes created  
under /my/resource. I guess I'd have to undo any nodes actually  
created in the jcr since they'd be masked by MyResourceProvider. But,  
that would then get me round-trip node semantics.

For xml or json posts, I've noticed the OptingServlet interface, which  
is probably a better approach than trying to use the  
JcrResourceTypeProvider to assert a default resource type in an  
attempt to resolve the request to a POST servlet mapped to that type.  
I could then register my POST servlets as default resource type  
handlers, which only opt-in for post requests to the appropriate path  
and http content-type. Better, right?

Thanks for your advice,
Andreas

Re: Exposing DB Data

[Carsten Ziegeler <cz...@apache.org>]

Hi Andreas,

Andreas Kollegger wrote:
> Hi,
> 
> I'd like to expose data stored in an external database as resource nodes
> in Sling. Access to the database is already wrapped in an OSGi service
> (MyService.java), and there is a Java model for the data (MyModel.java).
> From what I can understand from reading the examples, searching the
> mailing list and looking at documentation, the steps to integrate with
> Sling could be:
> 
> 1. MyResourceProvider implements ResourceProvider
>     - bridge to MyService for getting data rows
>     - provider.roots = /my/resources/
>     - sub-path of root used to select row in table
> (/my/resources/3245.html retrieves row index 3245 as Resource)
>     - listChildren() of root would list all database rows (probably add
> query params to limit number of rows returned)
>     - getResource() returns a MyResourceWrapper
> 
> 2. MyResourceWrapper implements Resource
>     - wraps MyModel
>     - resourceType = my/Model
>     - adaptable to javax.jcr.Node (exposing columns as node properties)
it depends on your application (code) but I think making your resource
adaptable to ValueMap (jcr resources are adaptable to value map as well)
is more generic. It allows you to access different kind of resources
(backed by jcr, backed by your db provider) in a unique way. And the
ValueMap interface is easier to use than jcr Node :)

> That would take care of read access to the data. I can then easily add
> scripts to render the data as appropriate.
Sounds good to me :)

> 
> Write access is a little less clear to me. I'd like to have a servlet
> which handles json, xml or form POSTs for anything submitted to the path
> of my ResourceProvider. I think I need to do this:
> 
> 1. MyResourceTypeProvider implements JcrResourceTypeProvider
>     - provider.roots = /my/resources
>     - any node below the root is returned as resourceType my/Model,
> otherwise return null
> 
> 2. MyPostServlet extends SlingAllMethodsServlet
>     - registered to handle nodes of type my/Model
>     - override doPost() to handle data based on content-type
>     - creates new database entries using MyService
> 
> That would allow the creation of a new database row by posting an
> appropriate json or xml file to /my/resource/*.html . For the form data,
> I'd conform to the SlingPostServlet interface to remain compatible with
> generic node editing.
> 
> Am I thinking about this correctly, or is their a more appropriate
> Sling-Way?
Hmm not sure :) I guess your idea should work. Now, for updating db data
it should already work if your resource is adaptable to PersistableValueMap.

For adding/deleting resources something like the above might work. We
are thinking of extending the resource provider interface to get
better support for these use cases. This is one of the topics for the
near future, so maybe we should start thinking about this (again) now :)

Regards
Carsten
-- 
Carsten Ziegeler
cziegeler@apache.org

Re: Exposing DB Data

[Felix Meschberger <fm...@gmail.com>]

Hi Andreas,

Andreas Kollegger schrieb:
> Hi,
> 
> I'd like to expose data stored in an external database as resource nodes
> in Sling. Access to the database is already wrapped in an OSGi service
> (MyService.java), and there is a Java model for the data (MyModel.java).
> From what I can understand from reading the examples, searching the
> mailing list and looking at documentation, the steps to integrate with
> Sling could be:
> 
> 1. MyResourceProvider implements ResourceProvider
>     - bridge to MyService for getting data rows
>     - provider.roots = /my/resources/
>     - sub-path of root used to select row in table
> (/my/resources/3245.html retrieves row index 3245 as Resource)
>     - listChildren() of root would list all database rows (probably add
> query params to limit number of rows returned)
>     - getResource() returns a MyResourceWrapper

Makes perfect sense.

> 2. MyResourceWrapper implements Resource
>     - wraps MyModel
>     - resourceType = my/Model
>     - adaptable to javax.jcr.Node (exposing columns as node properties)

I would not adapt it to a Node. Rather I would adapt it to the MyModel
class and propbably to ValueMap (or even PersistableValueMap) to get at
the properties.

> 
> That would take care of read access to the data. I can then easily add
> scripts to render the data as appropriate.
> 
> Write access is a little less clear to me. I'd like to have a servlet
> which handles json, xml or form POSTs for anything submitted to the path
> of my ResourceProvider. I think I need to do this:
> 
> 1. MyResourceTypeProvider implements JcrResourceTypeProvider
>     - provider.roots = /my/resources
>     - any node below the root is returned as resourceType my/Model,
> otherwise return null

The problem exists mainly for non-existing resources (rows). Existing
rows already map to the correct implementation (see above) and thus have
the correct resource type.

For non-existing resources, Ian Boston is preparing a patch providing a
PathResourceTypeProvider which may be used to assign a special resource
type to non-existing nodes much like the JcrResourceTypeProvider does
for plain JCR Item based resources.

So in your use case Ian's PathResourceTypeProvider would probably be
appropriate.

> 
> 2. MyPostServlet extends SlingAllMethodsServlet
>     - registered to handle nodes of type my/Model
>     - override doPost() to handle data based on content-type
>     - creates new database entries using MyService

Makes perfect sense to me. We might want to inspect how much of the
existing SlingPostServlet could be abstracted to support general
resource creation, modification, removal, not just JCR Item based ones.

> 
> That would allow the creation of a new database row by posting an
> appropriate json or xml file to /my/resource/*.html . For the form data,
> I'd conform to the SlingPostServlet interface to remain compatible with
> generic node editing.
> 
> Am I thinking about this correctly, or is their a more appropriate
> Sling-Way?

I think you are approach looks perfectly like the Sling way.

Another approach could of course be to create a Jackrabbit
PersistenceManager for your database and access your database through
Jackrabbit. But I suspect that this would be more complicated.

Regards
Felix