Using Repositories with JDBC in Wildfly

ModeShape provides a JDBC-compliant API which allows clients to connect & query a repository via JDBC. The ModeShape kit for Wildfly comes prepackaged with a module named org.modeshape.jdbc which contains a java.sql.Driver implementation
that allows JDBC clients to connect to existing repositories.

Configuration

The easiest way to access a ModeShape repository via JDBC is to configure a ModeShape datasource and a driver inside of Wildfly.

The following example shows a configuration snippet from a Wildfly standalone.xml file, which exposes via JDBC, the workspace "extra" from a repository named "artifacts"

<datasource jndi-name="java:/datasources/ModeShapeDS" enabled="true" use-java-context="true" pool-name="ModeShapeDS">
  <connection-url>jdbc:jcr:jndi:jcr?repositoryName=artifacts</connection-url>
  <driver>modeshape</driver>
  <connection-property name="workspace">extra</connection-property>
  <security>
   <user-name>admin</user-name>
   <password>admin</password>
  </security>
</datasource>

<drivers>
  <driver name="modeshape" module="org.modeshape.jdbc">
     <driver-class>org.modeshape.jdbc.LocalJcrDriver</driver-class>
  </driver>
</drivers>

JDBC Driver

As you can see from the above snippet, configuring the ModeShape JDBC driver requires the following attributes:

`name`	a symbolic name for the JDBC driver, which will be used by the datasource
`module`	the Wildfly module name which contains ModeShape's jdbc driver implemenation
`driver-class`	the fully qualified class name of the `java.sql.Driver` implementation

DataSource

For each repository you want to access, you will need to configure a DataSource in the Wildfly configuration file. In the example above, the following attributes are defined:

`jndi-name`	The name under which the datasource should be registered in JNDI by Wildfly. Please note that, at the moment Wildfly only allows datasources to be registered under a name beginning either with `java:/` or `java:jboss/`
`connection-url`	A JNDI url, which points ModeShape to an existing repository. This format of this url is: `jdbc:jcr:jndi:jcr:?repositoryName`=
`driver`	The name of the JDBC driver, as described by the above section
`security`	The username & password which will be passed to the connection, when attempting to access a repository. Inside of Wildfly, those are normally taken from the `modeshape-security` domain.
`connection-property`	Any additional properties which can be passed to the connection. For example, to access a specific workspace of a repository, the `workspace` property can be defined.

Usage

Once a datasource has been configured and the application server has started up, the datasource can be accessed from JNDI and queries executed against the configured repository.

For example:

@Resource( mappedName = "datasources/ModeShapeDS" )
private DataSource modeshapeDS;

....

Connection connection = modeshapeDS.getConnection();
Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery("SELECT [jcr:primaryType], [jcr:mixinTypes], [jcr:path], [jcr:name] FROM [nt:unstructured] ORDER BY [jcr:path]");

Queries

The query language used should be JCR-SQL2.

However, since JCR Nodes cannot be exposed directly via JDBC, the only way to return the path-related and score information is through additional columns in the result. While such columns could "magically" appear in the result set, doing so is not compatible with JDBC applications that dynamically build queries based upon database metadata. Such applications require the columns to be properly described in database metadata, and the columns need to be used within queries.

ModeShape attempts to solve these issues by directly supporting a number of "pseudo-columns" within JCR-SQL2 queries, wherever columns can be used. These "pseudo-columns" include:

jcr:score is a column of type DOUBLE that represents the full-text search score of the node, which is a measure of the node's relevance to the full-text search expression. ModeShape does compute the scores for all queries, though the score for rows in queries that do not include a full-text search criteria may not be reliable.
jcr:path is a column of type PATH that represents the normalized path of a node, including same-name siblings. This is the same as what would be returned by the getPath() method of Node. Examples of paths include "/jcr:system" and "/foo/bar[3]".
jcr:name is a column of type NAME that represents the node name in its namespace-qualified form using namespace prefixes and excluding same-name-sibling indexes. Examples of node names include "jcr:system", "jcr:content", "ex:UserData", and "bar".
mode:localName is a column of type STRING that represents the local name of the node, which excludes the namespace prefix and same-name-sibling index. As an example, the local name of the "jcr:system" node is "system", while the local name of the "ex:UserData[3]" node is "UserData".
mode:depth is a column of type LONG that represents the depth of a node, which corresponds exactly to the number of path segments within the path. For example, the depth of the root node is 0, whereas the depth of the "/jcr:system/jcr:nodeTypes" node is 2.

All of these are exposed in the database metadata, allowing potential clients to detect & use them.