XQuare Binding Component (petals-binding-jdbc)

1. Purpose

The XQuare BC has a double purpose :

  • JDBC data servicing
  • JDBC data emission

1.1. Data Servicing Purpose

The XQuare BC Processor builds upon the XQuare Bridge library (see web site, tutorial and reference documentation at http://xquare.objectweb.org/ ) in order to allow users to interact with databases through its three operations : insert, *query* and storedQuery.

1.2. Data Emission Purpose

The XQuare BC Listener "listens" to new data in any XQuareBC service unit's target database and builds upon the XQuery capabilities of the companion XQuareBCJBIProcessor in order to get them, format them as XML and send it as a message to the configured service. The "listening" part is done by regularly looking for "new data" as the results of a configured SQL query.

WARNING : polling is not a best practice to integrate databases. Databases are usually application-driven, and those applications (or their users) should drive the "new data" lookup process. This implementation is provided as fitting the most generic needs.

2. Data Servicing Operations detail

2.1. insert

The "insert" operation lets the user provide as the content of an incoming JBI message an XML document defining insertion or update of database records according to the configured XQuare mapping (mapping XML file and insertion XML schema file). Note that updating existing data or adding new records are both possible. Look at the XQuare Bridge reference documentation for more details. The content of the response is (if no fault because of an internal exception) the single tag "success".

2.2. query

The "query" operation lets the user provide as the content of an incoming JBI message an XQuery wrapped within the "query" top tag. This XQuery must be XQuare compliant and consistent with the configured database and mapping for the targeted service. It should also be XML encoded (even if the parser tries to be nice by formatting and including XML subnodes) - typically the results formatting should be, since they comprise XML tags. The response is an XML document whose top tag is "Results" and whose elements are specified by the XQuery's results formatting part. Note that modules (akin to XML / SQL views, defined in ".mod" files provided in the service unit's deployment directory) can be imported and used in such queries. Look at the XQuare XQuery reference documentation for more details.

There is a way to specify the top tag in the query : by writing it down in xquery and using a service configured with the queryResults.wrapInRootTag at false. Ex. 

<query><result>{for $i in collection("item")/item return $i}</result></query>
will return : 
<result><item><id>0</i><label>jean</label></item>...</result>

2.3. storedQuery

The "storedQuery" operation lets the user provide as the content of an incoming JBI message the id of the stored parameterized query to be executed and the value of its parameters. Concretely it must consist in the "storedQuery" top tag with the "id" attribute and zero or more subtags with "name" and "value" attributes. Thoses "names" must be consistent with the parameters defined within the XQuery code of the stored query (see examples), since the "parameter" mechanism of XQuare / XQuery is used by the processor. Otherwise it is similar to the "query" operation in its behaviour.

3. Data Servicing Configuration

3.1. Since petals-bc-xquare 1.3

3.1.1 Global database configuration

The target database is configured through the keyvalue extension provided by a given service unit and uses the following nodes :

  • database/url : the JDBC URL of the targeted database. Required for accessing the database through JDBC. Ex. jdbc:mysql://localhost/petals_samples_xquare_auction
  • database/user : the user required by the JDBC connection. Required for accessing the database through JDBC. Ex. petals_samples
  • database/password : the password required by the JDBC connection. Required for accessing the database through JDBC. Ex. petals_samples
  • jndidatasource/name : if provided, is used as the preferred way to access the target database. Note that the given JNDI name must exist in the Petals context and target a J2EE Datasource Ex. jdbc/myDS
Sample : 
<database>
	<url>jdbc:mysql://localhost/petals_samples_xquare_auction</url>
	<user>petals_samples</user>
	<password>petals_samples</password>
</database>
<jndidatasource>
	<name>jdbc/myDS</name>
</jndidatasource>

3.1.2. Additional properties for configuring XQuare Bridge :

  • database/mapping : the name of the XQuare mapping file to be found in the service unit deployment directory Required. Ex. auction.map
  • queryResults/wrapInRootTag : whether to always wrap the query results in a top "" tag. If set to false, single results won't be wrapped.
  • xmlconnection/baseuri : The base URI where to find modules (.mod files) to import if such are specified in user or stored queries. Optional, defaults to the service unit's deployment directory
  • (not a property per se) The schema definition of insertion XML documents must be provided in the service unit's deployment directory, with the same prefix than the corresponding mapping file (ex. auction.xsd).
Sample : 
<database>
	<mapping>auction.map</mapping>
</database>
<queryResults>
	<wrapInRootTag>false</wrapInRootTag>
</queryResults>
<xmlconnection>
	<baseuri></baseuri>
</xmlconnection>

3.1.3. Configuring stored queries :

Stored queries must be defined in a tag <storedQuery id="x">. The query id is a String and is its XQuery definition. Note that parameters are implicitly defined using the corresponding XQuare / XQuery concept.

Sample : 

<storedQueries>
	<storedQuery id="0"><![CDATA[
		declare variable $var as xs:string external
		<result> {
		   for $i in collection("items")/items
		   where contains($i/description, $var)
		   return $i
		} </result>]]>
	</storedQuery>
</storedQueries>

3.2. Before petals-bc-xquare 1.3

3.2.1. Global database configuration

The target database is configured through the .properties file provided by a given service unit and uses the following properties :

  • database.url : the JDBC URL of the targeted database. Required for accessing the database through JDBC. Ex. jdbc:mysql://localhost/petals_samples_xquare_auction
  • database.user : the user required by the JDBC connection. Required for accessing the database through JDBC. Ex. petals_samples
  • database.password : the password required by the JDBC connection. Required for accessing the database through JDBC. Ex. petals_samples
  • jndidatasource.name : if provided, is used as the preferred way to access the target database. Note that the given JNDI name must exist in the Petals context and target a J2EE Datasource Ex. jdbc/myDS

3.2.2. Additional properties for configuring XQuare Bridge :

  • database.mapping : the name of the XQuare mapping file to be found in the service unit deployment directory Required. Ex. auction.map
  • queryResults.wrapInRootTag : whether to always wrap the query results in a top "" tag. If set to false, single results won't be wrapped.
  • xmlconnection.baseuri : The base URI where to find modules (.mod files) to import if such are specified in user or stored queries. Optional, defaults to the service unit's deployment directory
  • (not a property per se) The schema definition of insertion XML documents must be provided in the service unit's deployment directory, with the same prefix than the corresponding mapping file (ex. auction.xsd).

3.2.3. Configuring stored queries :

Stored queries must be defined as a property whose name is "storedQuery_" followed by the query id (must be a String), and whose value is its XQuery definition. Note that parameters are implicitly defined using the corresponding XQuare / XQuery concept. See examples. Ex. "storedQuery_0"

4. Data Emission Features

4.1. What it does and how it works

This default implementation identifies "new data" by a given field that must be incremental - that is, be different and always greated in newly inserted records. The usual case for it is being a record identifier or primary key, be it works well with other kind of fields as well, like timestamps.

Concretely, it executes the configured "oldDataDelimiterSelect" to get the old "new data delimiting value", then it executes the configured "newDataDelimiterSelect" to get the new "new data delimiting value" as available in the actual data. If they are different, the configured XQuery is then executed through the XQuareBCJBIProcessor and its results formatted as an XML document and sent as a JBI message to the configured JBI service. Finally the "old data delimiting value" is updated to the new one.

4.2. Notes

This requires a new table to be added to the target database, that will contain a single record with a single field being the last seen "maximum data delimiting value". This table may start empty, in which case the "initDataDelimiter" will be used the first time.

Before executing the XQuery, in its code the $$OLD_DATA_DELIMITER$$ and $$NEW_DATA_DELIMITER$$ expressions are replaced by the values found in the preceding executions of "oldDataDelimiterSelect" and "newDataDelimiterSelect". WARNING xwiki can't display them, but those tokens use actually sharps instead of dollar characters.

Many such listeners may be deployed in different service units to listen to different kinds of "new data" in the same database. Each one must have its own additional table.

Since it uses the XQuareBCJBIProcessor's "query" capabilities, look at its documentation for more details on how it behaves.

4.3. Configuration

The datasource to the target database and the XQuareBCJBIProcessor's XQuery capabilities are configured through the XQuareBCJBIProcessor (look at its javadoc for details).

4.3.1 Since petals-bc-xquare 1.3

The configuration specific to this listener is configured in the jbi.xml provided by a given service unit, and uses the following additional nodes :

  • newdatalistener/service : The fully qualified name of the service to which the new data messages will be sent. Ex.
{http://petals.objectweb.org/}HelloworldService
  • newdatalistener/operation : This service's operation. Ex. sayHello
  • newdatalistener/delimiter/oldDataDelimiterSelect : An SQL query that must return the single value of the saved, old data delimiter. Ex.
select max(bid_date) from bids_xqbc
  • newdatalistener/delimiter/newDataDelimiterSelect : An SQL query that must return the single value of the current "new data" delimiter. Ex.
select max(bid_date) from bids
  • newdatalistener/delimiter/initDataDelimiter : The new data delimiter initial value if none in its own table and field. Ex. 1970-01-01 or merely 0
  • newdatalistener/delimiter/newDataDelimiterUpdates : An SQL insert that must update the value of the "new data" delimiter from "old" to "new". Note that the $$NEW_DATA_DELIMITER$$ expression is replaced within the SQL code before its execution. Ex.
delete from bids_xqbc;
insert into bids_xqbc (bid_date) values ("$$NEW_DATA_DELIMITER$$")
  • newdatalistener/delimiter/newDataXquery : An XQuery that must return and format the current new data. Note that the $$OLD_DATA_DELIMITER$$ and $$NEW_DATA_DELIMITER$$ expressions are replaced within the SQL code before its execution. Ex.
for $i in collection("bids")/bids 
where $i/bid_date > xs:date("$$OLD_DATA_DELIMITER$$") and $i/bid_date <= xs:date("$$NEW_DATA_DELIMITER$$") return
<bid>{ $i/bid_date }</bid>

WARNING Note that the SQL or XQuery in the XQuareBC's jbi.xml configuration files must be in a CDATA section.

Sample : 

<newdatalistener>
	<service>{http://petals.objectweb.org/}HelloworldService</service>
	<operation>sayHello</operation>
	<delimiter>
		<oldDataDelimiterSelect><![CDATA[select max(bid_date) from bids_xqbc]]></oldDataDelimiterSelect>
		<newDataDelimiterSelect><![CDATA[select max(bid_date) from bids]]></newDataDelimiterSelect>
		<initDataDelimiter>1970-01-01</initDataDelimiter>
		<newDataDelimiterUpdates><![CDATA[
			delete from bids_xqbc;
			insert into bids_xqbc (bid_date) values ("$$NEW_DATA_DELIMITER$$")]]>
		</newDataDelimiterUpdates>
		<newDataXquery><![CDATA[
			for $i in collection("bids")/bids 
			where $i/bid_date > xs:date("$$OLD_DATA_DELIMITER$$") and $i/bid_date <= xs:date("$$NEW_DATA_DELIMITER$$")
			return <bid>{ $i/bid_date }</bid>]]>
		</newDataXquery>
	</delimiter>
</newdatalistener>

4.3.2 Before petals-bc-xquare 1.3

The configuration specific to this listener is configured through the same .properties file provided by a given service unit, and uses the following additional properties :

  • *newdatalistener.service : The fully qualified name of the service to which the new data messages will be sent. Ex.
{http://petals.objectweb.org/}HelloworldService
  • newdatalistener.operation : This service's operation. Ex. sayHello
  • newdatalistener.delimiter.oldDataDelimiterSelect : An SQL query that must return the single value of the saved, old data delimiter. Ex.
select max(bid_date) from bids_xqbc
  • newdatalistener.delimiter.newDataDelimiterSelect : An SQL query that must return the single value of the current "new data" delimiter. Ex.
select max(bid_date) from bids
  • newdatalistener.delimiter.initDataDelimiter : The new data delimiter initial value if none in its own table and field. Ex. 1970-01-01 or merely 0
  • newdatalistener.delimiter.newDataDelimiterUpdates : An SQL insert that must update the value of the "new data" delimiter from "old" to "new". Note that the $$NEW_DATA_DELIMITER$$ expression is replaced within the SQL code before its execution. Ex.
delete from bids_xqbc;
insert into bids_xqbc (bid_date) values ("$$NEW_DATA_DELIMITER$$")
  • newdatalistener.delimiter.newDataXquery : An XQuery that must return and format the current new data. Note that the $$OLD_DATA_DELIMITER$$ and $$NEW_DATA_DELIMITER$$ expressions are replaced within the SQL code before its execution. Ex.
for $i in collection("bids")/bids 
where $i/bid_date > xs:date("$$OLD_DATA_DELIMITER$$") and $i/bid_date <= xs:date("$$NEW_DATA_DELIMITER$$") return
<bid>{ $i/bid_date }</bid>

WARNING Note that when specifying SQL or XQuery in the XQuareBC's .properties configuration files, new lines within a property value must be escaped using a line-ending backslash.

5. Deploying an XQuareBC service unit onto Petals

5.1. Writing its jbi.xml

5.1.1 Since petals-bc-xquare 1.3

Here is a sample jbi.xml :

<jbi version="1.0"
		xmlns="http://java.sun.com/xml/ns/jbi"
		xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
		xmlns:petals="http://petals.objectweb.org/"
		xmlns:extensions="http://petals.objectweb.org/extensions/"
		xmlns:xquare="http://petals.objectweb.org/extensions/xquare/">
	<services binding-component="true">
		<provides
				interface-name="mySampleXquare"
				service-name="petals:SampleXquareService"
				endpoint-name="mySampleXquareEndpoint">
			<extensions:extensions>
				<extension xmlns="http://petals.objectweb.org/extensions/xquare/">
					<jndidatasource>
						<name>jdbc/myDS</name>
					</jndidatasource>
					<database>
						<url>jdbc:mysql://localhost/petals_samples_xquare_auction</url>
						<user>petals_samples</user>
						<password>petals_samples</password>
						<mapping>auction.map</mapping>
					</database>
					<queryResults>
						<wrapInRootTag>false</wrapInRootTag>
					</queryResults>
					<xmlconnection>
						<baseuri></baseuri>
					</xmlconnection>
					<storedQueries>
						<storedQuery id="0"><![CDATA[
							declare variable $var as xs:string external
							<result> {
							   for $i in collection("items")/items
							   where contains($i/description, $var)
							   return $i
							} </result>]]>
						</storedQuery>
					</storedQueries>
					<newdatalistener>
						<service>{http://petals.objectweb.org/}HelloworldService</service>
						<operation>sayHello</operation>
						<delimiter>
							<oldDataDelimiterSelect><![CDATA[select max(bid_date) from bids_xqbc]]></oldDataDelimiterSelect>
							<newDataDelimiterSelect><![CDATA[select max(bid_date) from bids]]></newDataDelimiterSelect>
							<initDataDelimiter>1970-01-01</initDataDelimiter>
							<newDataDelimiterUpdates><![CDATA[
								delete from bids_xqbc;
								insert into bids_xqbc (bid_date) values ("$$NEW_DATA_DELIMITER$$")]]>
							</newDataDelimiterUpdates>
							<newDataXquery><![CDATA[
								for $i in collection("bids")/bids 
								where $i/bid_date > xs:date("$$OLD_DATA_DELIMITER$$") and $i/bid_date <= xs:date("$$NEW_DATA_DELIMITER$$")
								return <bid>{ $i/bid_date }</bid>]]>
							</newDataXquery>
						</delimiter>
					</newdatalistener>
				</extension>
			</extensions:extensions>
		</provides>
	</services>
</jbi>

5.1.2 Before petals-bc-xquare 1.3

The jbi.xml must mention the petals-extensions "type" specific to XQuareBC : "xquare_service" .

Here is a sample jbi.xml :

<jbi version="1.0" xmlns='http://java.sun.com/xml/ns/jbi'
                xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance'>
        <services binding-component="true">
                <consumes interface-name="foo" service-name="{namespace}fooService" endpoint-name="fooServiceEndpoint" />
                <provides interface-name="mySampleXquare" service-name="{http://petals.objectweb.org/}SampleXquareService" endpoint-name="mySampleXquareEndpoint" />
                <type>xquare_service</type>
        </services>
</jbi>

5.2. What must be provided withing the Service Unit

  • as for any JBI component, META-INF/jbi.xml
  • the .wsdl file describing the provided JBI service
  • before petals-bc-xquare 1.3 : the .properties file configuring the XQuareBC SU (see reference above)
  • the .map file configuring XQuare's XML to SQL mapping (this file must be specified in the .properties file)
  • and its .xsd XML schema file
  • any .mod file whose XQuery functions (describing "views" on available data) have to be provided to XQuare for this Service Unit

5.3. Sample

See attached file "myxquare.zip".

It binds with the XQuare sample "auction" database, which must be provided by a MySQL server. The data base schema is provided in the XQuareBC tests source code. It emits new bids according to their date and target them to the HelloworldService (which must be deployed).

sample SA sample sql sample xqbc sql

5.4 Using another database engine than MySQL

The MySQL driver is packaged within the standard release of the XQuareBC component. In order to support other database engines (among those supported by XQuare), their drivers must be included in the component .zip package and added to the component classpath within its jbi.xml .