[jdbc] JDBC update

[Jukka Zitting <ju...@gmail.com>]

Hi,

I just put some extra effort into the JDBC driver for JCR that I've
been working on in sandbox/jackrabbit-jdbc2jcr. You can build it with
"mvn package" which gives you a
jackrabbit-jdbc2jcr-SNAPSHOT-jar-with-dependencies.jar file in the
target directory. Just add this to the classpath of a JDBC client, and
you're ready to go.

The JDBC driver class name is org.apache.jackrabbit.jdbc.JCRDriver,
and you can use the following kinds of JDBC URLs to access a content
repository:

    jdbc:jcr:http://localhost:8080/rmi    (RMI, http lookup)
    jdbc:jcr:rmi://localhost/jackrabbit.repository    (RMI, with rmiregistry)
    jdbc:jcr:jndi://javax/jcr/Repository    (JNDI, default directory)

You can use the parameters "workspace", "username" and "password" in
the JDBC URL to specify login information. The JDBC URL parameters are
also passed to the JNDI initial context, so you can use that to access
other JNDI directories than the default one.

The JDBC driver will create a set of SQL views for accessing content
in the repository. No copies of content are kept, so any changes you
make in the repository are immediately reflected in the SQL views. You
can use these views in joins, subqueries, aggregates and other sorts
of complex queries that are not supported by the native JCR query
features. Note that the views are strictly view-only, you can not
modify repository content through this JDBC driver.

By default the driver will create views for all node types in your
repository. The name of such a view is the name of the node type with
the ":" character replaced with "_".  The view maps all non-residual
properties of the node type to corresponding SQL columns (again with
":" replaced with "_"). Also, all views contain the special columns
jcr_path, jcr_name, jcr_primaryType and jcr_mixinTypes. A node type
view will contain information from all nodes that match the XPath
query //element(*,<name of type>).

Additionally you can specify custom views by adding special jdbc:view
nodes in the workspace that you access. See the node type definitions
at the end of this message for how to do that. Custom views allow you
to use a JCR query to determine what content will be included in the
view. This is much more efficient than using a WHERE clause to select
specific content from a more generic view.

Note that this JDBC driver will most likely not perform very well on
complex queries, as it needs to always load the full contents of all
accessed views even if just a few rows of that view are needed. Using
the RMI layer to access a repository will also notably degrade
performance.

BR,

Jukka Zitting



<jdbc = 'http://jackrabbit.apache.org/ns/2008/jdbc'>

// Column definition node
// The name of the node is used as the column name in the generated SQL view
[jdbc:column]
    // By default the column maps to a property with the same name as the
    // column definition node, but you can specify any relative property path
    // in the "path" property to make that property mapped to the column.
    - path (path)

    // By default the column type is a string, but you can override that
    // by specifying any JCR value type (as seen in the PropertyType.TYPENAME
    // constants) in the "type" property
    - type (string)

    // Columns are by default single-valued (showing just the first value
    // of a multi-valued property), but you can make a column multi-valued
    // by setting the "multiple" property to "true"
    - multiple (boolean)

// View definition node
// The name of the node is used as the name of the generated SQL view
[jdbc:view] orderable
    // Alternative name for the generated SQL view. Set this property to
    // override the default of using the node name as the view name
    - name (string)

    // The view query. All nodes that match the given JCR query are included
    // in the generated SQL view. Default is the XPath query "//*".
    - query (string)

    // The query language. Set to "sql" if the view query is expressed in SQL.
    // Default value is "xpath".
    - language (string)

    // Column definitions for this view
    * (jdbc:column) = jdbc:column

Re: [jdbc] JDBC update

[Jukka Zitting <ju...@gmail.com>]

Hi,

On Mon, Jan 19, 2009 at 2:13 PM, Jukka Zitting <ju...@gmail.com> wrote:
> I just put some extra effort into the JDBC driver for JCR that I've
> been working on in sandbox/jackrabbit-jdbc2jcr.

Michael Marth blogged more about this, see
http://dev.day.com/microsling/content/blogs/main/jdbc2jcr.html

BR,

Jukka Zitting