|
|
This chapter explains how to extend the database support of AquaLogic Data Services Platform (ALDSP). Extensions let you provide immediate, dynamic support for unsupported databases and new versions of supported databases. This chapter explains how to extend database support using a feature called the Configurable Relational Provider.
| Tip: | A sample Configurable Relational Provider file is provided in this chapter. You can copy the sample and use it as a starting point for creating your own customized provider. See Sample Configurable Relational Provider File for the complete listing. |
This chapter assumes that you are familiar with XQuery and SQL, especially for more advanced use cases. For suggested background on these subjects with respect to ALDSP, see Related Reading.
This chapter includes these topics:
The Configurable Relational Provider lets you extend the database support and functionality of ALDSP. The Configurable Relational Provider lets you add or modify database support by configuring an XML file, called a “provider.” You can configure the XML provider to extend database support for all but a few advanced cases. See Using the Configurable Relational Provider for details.
This section describes the overall framework for extending ALDSP database support, defines general terms, and lists several use cases for the extension framework.
This section includes these topics:
This section explains cases where you might consider extending database support using the Configurable Relational Provider.
If you are using ALDSP with base platform database support (see Relational Providers Included With ALDSP), it is possible that the database itself can handle more complex constructs, such as expressions and clauses, than are generated by the base platform provider. In this case, users might experience reduced performance. To solve this problem, you can configure an Configurable Relational Provider.
If a new version of a core database is released, ALDSP by default treats it the same as the previously supported version. Obviously, with a new release, there may be features that you want to use, such as improved SQL pushdown. In this case, you can update the database support by extending the relational provider for the core database using the Configurable Relational Provider to add the new pushdown features.
| Note: | This use case is uncommon. |
It is possible that you require access to a database that is not supported by ALDSP core database set (see Table 10-1) and that cannot consume SQL generated by the base platform provider. In this case, you can use the Configurable Relational Provider and either disable unsupported features or add new features as desired.
The Relational Wrapper Extension Framework lets you add or modify relational database support for ALDSP. This framework supports the Configurable Relational Provider, which lets you extend database support by editing a configuration file. Figure 10-1 illustrates the architecture of the Relational Wrapper Extension Framework.
This framework includes a component called a Relational Wrapper that exposes XQuery views of relational sources and executes queries against them. The Relational Wrapper includes the Relational Database Provider Registry, which manages chains of components called relational providers.
| Tip: | The Configurable Relational Provider, which is discussed in detail in this chapter, is an example of a relational provider that you can easily configure and deploy by editing a file. The Configurable Relational Provider is the primary means by which you can extend database support. |
Inside the provider registry, relational providers are organized into chains. These chains delegate to one another and allow method invocations to be intercepted and processed along the way. Each provider either answers a request or delegates the request to its parent provider. A provider’s parent is specified by the <parent> element of the provider’s deployment descriptor (see Configurable Relational Provider Format Description and Reference).
As shown in Figure 10-1, the first chain is assembled from three providers: provider_1, provider_2 and provider_3. When the relational wrapper calls this chain, provider_1 first receives the call and has a choice of either answering it or delegating to its parent provider (provider_2). If provider_1 delegates to provider_2 then it is the responsibility of provider_2 to handle the request. In turn, provider_2 can decide to delegate processing to provider_3. This chain architecture increases system flexibility by supporting modular provider definitions and facilitating easy assembly.
| Tip: | Typically, when you create a provider using the Configurable Relational Provider, you specify a parent provider. The parent provides some features that the child provider can either accept by default or override. The child provider inherits the features of the parent; however, you can also add features to the child provider that are not implemented in the parent. Usually, one of the abstract providers serves as the parent of the first provider in a chain. See Abstract SQL Providers. |
By default, the Relational Wrapper Extension Framework supports a core set of databases. See Relational Providers Included With ALDSP for a complete list. Extensibility allows for full support of databases that are not in the core set and allows for support of new versions of the core databases. For example, a new version of a core database might provide new pushdown capabilities that are not currently recognized by ALDSP. You can use the extension framework to add the required database support immediately by editing and deploying a Configurable Relational Provider.
Table 10-1 lists the set of standard relational providers that are included with ALDSP. Standard providers are implemented using the Relational Wrapper Extension Framework and are registered by default. You can use these providers as a basis for configuring the Configurable Relational Provider.
|
Microsoft Access support is implemented using the Configurable Relational Provider described in Using the Configurable Relational Provider.
|
||
These abstract providers provide base functionality to the Configurable Relational Provider. See Configurable Relational Provider Format Description and Reference for details. See also Abstract SQL Providers.
|
| Tip: | The Base Database Version is calculated by the framework. This value specifies the minimum version of a database that a provider can handle. Matching rules are used to determine the value when you pick a provider that best matches your database. For more information on this calculation, see Database Matching. |
The Configurable Relational Provider supports the following features found in the core relational providers:
Some features defined by the Relational Wrapper Extension Framework are not supported by the Configurable Relational Provider. In such cases, the Configurable Relational Provider delegates the request to its parent provider, which answers it.
The unsupported features include:
You can import metadata on the data sources needed by your application using the AquaLogic Data Services Platform Metadata Import wizard. This wizard introspects available data sources and identifies data objects that can be rendered as data services and functions. The relational provider registry returns a list of providers that best match the database. You can then pick one of these providers (typically, the best match or one close to the best match) from a drop down menu. The best match appears at the top of the drop down menu. Once created, physical data services become the building-blocks for queries and logical data services. For detailed information on using the Metadata Import wizard, see Creating and Updating Physical Data Services in the Data Services Developer’s Guide. For information on how matching is performed, see Rules for Database Matching.
Refer to the following ALDSP documentation for more information on ALDSP database, XQuery, and SQL support:
Listing 10-1 shows a sample Configurable Relational Provider file. This sample demonstrates a possible way to configure a custom Microsoft Access provider. You can also find the sample Microsoft Access provider in your ALDSP installation here:
ALDSP_HOME\samples\RelationalAdapter\MS-Access
| Tip: | Copy this sample provider to use as a starting point for creating your own customized provider. Reference information in this chapter explains all of the configurable elements of this XML file. To get started, see Using the Configurable Relational Provider. |
<?xml version="1.0"?>
<aldsp-rdb-extension xmlns="http://www.bea.com/ns/aldsp/rdb/extension">
<name>MS Access XML Provider</name>
<vendor>BEA</vendor>
<implementation-version>1.0</implementation-version>
<description> MS Access Relational Wrapper Extension </description>
<rdb-provider>
<id>MS-Access-2003</id>
<description>XMLProvider MS Access 2003</description>
<parent>AbstractSQL</parent>
<factory
class="com.bea.dsp.wrappers.rdb.providers.custom.XMLCustomizableProviderFactory>
<custom-rdb-provider
xmlns="http://www.bea.com/ns/aldsp/rdb/extension/custom"
xmlns:fn="http://www.w3.org/2004/07/xpath-functions"
xmlns:fn-bea="http://www.bea.com/xquery/xquery-functions"
xmlns:op-bea="http://www.bea.com/xquery/xquery-operators"
xmlns:op="http://www.w3.org/2004/07/xpath-operators"
xmlns:xdt="http://www.w3.org/2004/07/xpath-datatypes"
xmlns:xs="http://www.w3.org/2001/XMLSchema">
<database-kind>
<match-database>
<![CDATA[
(jdbc:getDatabaseProductName() eq "ACCESS") and
(jdbc:getDatabaseMajorVersion() ge 4)
]]>
</match-database>
<base-version>4</base-version>
</database-kind>
<database-objects>
<catalog quote=""" separator="." />
<schema quote=""" separator="." />
<table quote=""" qualified-name-parts="catalog schema table" />
</database-objects>
<joins inner-join="true" outer-join="true">
<sql92 right-trees="true">
<inner-join-syntax>
{0} INNER JOIN {1} ON {2}
</inner-join-syntax>
</sql92>
</joins>
<orderby column="true" expression="true" aggregate="true" null-order="low"/>
<groupby column="true" expression="true" constant="true"/>
<subqueries in-from="true" in-where="true" />
<case supported="false" />
<functions>
<!-- String Functions -->
<function name="fn:concat" supported="true" infix="true" >&</function>
<function name="fn:string-length" arity="1">LEN({0})</function>
<function name="fn:lower-case" arity="1">IIF(ISNULL(LCASE({0})),'',LCASE({0}))</function>
<function name="fn:upper-case" supported="true" > IIF(ISNULL(UCASE({0})),'',UCASE({0}))</function>
<function name="fn:substring" arity="2" > IIF(ISNULL(MID({0},{1})),'',MID({0},{1}))</function>
<function name="fn:substring" arity="3" > IIF(ISNULL(MID({0},{1},{2})),'',MID({0},{1},{2}))</function>
<function name="fn-bea:left" >LEFT({0},{1})</function>
<function name="fn-bea:right" >RIGHT({0},{1})</function>
<function name="fn-bea:repeat" supported="false" />
<function name="fn-bea:trim" arity="1" >TRIM({0})</function>
<function name="fn-bea:trim-left" arity="1" >LTRIM({0})</function>
<function name="fn-bea:trim-right" >RTRIM({0})</function>
<function name="fn-bea:sql-like" arity="2" >({0} LIKE {1})</function>
<function name="fn-bea:sql-like" arity="3" supported="false" />
<function name="fn:starts-with" supported="false" />
<function name="fn:ends-with" supported="false" />
<function name="fn:contains" supported="false" />
<function name="op-bea:string-not-equal" arity="2" >({0} <> {1})</function>
<!-- Numeric Functions -->
<function name="fn:abs" supported="true" arity="1" >ABS({0})</function>
<function name="fn:ceiling" supported="false" />
<function name="fn:floor" supported="false" />
<function name="fn:round" >ROUND ({0})</function>
<!-- Aggregate Functions -->
<function name="fn:count" supported="true" arity="1" >COUNT({0})</function>
<function name="fn:avg" >AVG({0})</function>
<function name="fn:min" arity="1" >MIN({0})</function>
<function name="fn:max" supported="true" arity="1" >MAX({0})</function>
<function name="fn:sum" arity="1" > IIF(ISNULL(SUM({0})),0,SUM({0}))</function>
<!-- DateTime Functions -->
<function name="fn:day-from-date" arity="1" >DAY({0})</function>
<function name="fn:month-from-date" >MONTH({0})</function>
<function name="fn:year-from-date" >YEAR({0})</function>
<function name="fn:day-from-dateTime" arity="1" >DAY({0})</function>
<function name="fn:month-from-dateTime" >MONTH({0})</function>
<function name="fn:year-from-dateTime" >YEAR({0})</function>
<function name="fn:hours-from-dateTime" >HOUR({0})</function>
<function name="fn:minutes-from-dateTime" arity="1" >MINUTE({0})</function>
<function name="fn:seconds-from-dateTime" >SECOND({0})</function>
<function name="fn:current-date" supported="false"/>
<function name="fn:current-time" supported="false"/>
<function name="fn:current-dateTime" supported="false"/>
</functions>
<casts>
<cast from="xs:string" from-subtypes="true" to="xs:int">
CINT({0})
</cast>
<cast from="xs:double" from-subtypes="true" to="xs:int">
CINT({0})
</cast>
<cast from="xs:float" from-subtypes="true" to="xs:int">
CINT({0})
</cast>
<cast from="xs:decimal" from-subtypes="true" to="xs:int">
CINT({0})
</cast>
<cast from="xs:string" from-subtypes="true" to="xs:double">
CDBL({0})
</cast>
<cast from="xs:decimal" from-subtypes="true" to="xs:double">
CDBL({0})
</cast>
<cast from="xs:string" from-subtypes="true" to="xs:float">
CDBL({0})
</cast>
<cast from="xs:decimal" from-subtypes="true" to="xs:float">
CDBL({0})
</cast>
<cast from="xs:string" from-subtypes="true" to="xs:dateTime">
CDATE({0})
</cast>
<cast from="xs:float" from-subtypes="true" to="xs:string" >
CSTR({0})
</cast>
<cast from="xs:double" from-subtypes="true" to="xs:string" >
CSTR({0})
</cast>
<cast from="xs:decimal" from-subtypes="true" to="xs:string" >
CSTR({0})
</cast>
<cast from="xs:boolean" from-subtypes="true" to="xs:string" >
CSTR({0})
</cast>
<cast from="xs:dateTime" from-subtypes="false" to="xs:string" >
CSTR({0})
</cast>
</casts>
<limit>
<select-top />
</limit>
<insert>
<auto-column-generator kind="sql-post" >
select @@identity
</auto-column-generator>
</insert>
<properties
supports-multiple-active-queries-per-connection="false"
supports-cancel-query="false"
supports-query-timeout="false" />
</custom-rdb-provider>
</factory>
</rdb-provider>
</aldsp-rdb-extension>
This section explains how to use the Configurable Relational Provider. The Configurable Relational Provider lets you configure a new relational provider by editing an XML configuration file.
| Tip: | Be sure to review the section Introduction before continuing. |
This section includes these topics:
This section lists the basic steps required to develop and deploy an Configurable Relational Provider. The basic process of creating a new provider is also shown in Figure 10-2.
A command-line deployment tool, described in this section, is provided with ALDSP. Use this tool to add and remove relational providers. To use this deployment tool, your provider’s deployment descriptor must be packaged in a JAR file. See Deploying the Relational Provider.
| Note: | When ALDSP loads an extension, the deployment descriptor is read and validated. If a provider section of the description is determined to be invalid, it is ignored. |
The command syntax for adding a provider is:
<ALDSP_HOME>/bin/update-providers.[cmd/sh] -add <provider.jar>
The fully-qualified path to the provider relational wrapper extension JAR file is required. When a new provider is added, it is copied into the <ALDSP_HOME>/providers directory.
| Note: | Adding or removing a provider requires that you restart the IDE or the server. |
The command syntax for removing a provider is:
<ALDSP_HOME>/bin/update-providers.[cmd/sh] -remove <provider.jar>
Specify the filename of the provider JAR file located in the <ALDSP_HOME>/providers directory. When an existing provider is removed, it is deleted from the <ALDSP_HOME>/providers directory.
| Note: | Adding or removing a provider requires that you restart the IDE or the server. |
This section describes the format, elements, and configurable properties of an Configurable Relational Provider.
| Note: | A complete provider example is listed in Sample Configurable Relational Provider File. |
This section describes each of the primary elements in an Configurable Relational Provider file. This file is a deployment descriptor that is used to specify the properties of the relational provider extension.
| Tip: | The file must be packaged and deployed in a JAR file. The JAR must only contain one deployment descriptor; however, the descriptor can define and configure one or more providers. See Deploying the Relational Provider. |
The following list describes the primary elements of a relational provider deployment descriptor.
| Note: | You must name the deployment descriptor file aldsp-rdb-extension.xml. |
<name> – The name of the provider. <vendor> – (Optional) The name of the vendor of the provider. <implementation-version> – (Optional) A version number for the provider. <description> – (Optional) A brief description of the extension.<id> – The provider ID. This ID is used to register the provider in the provider registry. <description> – (Optional) A brief description of the provider.<parent> – (Optional) The <id> element of a parent provider. | Tip: | In the sample file in Sample Configurable Relational Provider File, the class specified by the <parent> element is AbstractSQL. See Abstract SQL Providers for detailed information on this abstract provider parent class. |
<modifier> – (Optional) Either abstract or final. If set to abstract, the provider cannot be referred to by any data service; however, an abstract provider can be extended (be the parent of another provider). If set to final, the provider cannot be extended by any other providers.<factory> – (Optional) This element specifies a factory class that instantiates the provider. The Configurable Relational Provider uses the default factory class, ,XMLCustomizableProviderFactory. | Tip: | In the sample file in Sample Configurable Relational Provider File, the <factory> element explicitly specifies the default factory class, XMLCustomizableProviderFactory. |
<custom-rdb-provider> – A sub-element that specifies the namespace of the Configurable Relational Provider and its full configuration. The default namespace is: http://www.bea.com/ns/aldsp/rdb/extension/custom. | Tip: | For details on configuring the <custom-rdb-provider> element, see Sample Configurable Relational Provider File and Configurable Relational Provider Format Description and Reference. |
| Note: | When ALDSP loads an extension, the deployment descriptor is read and validated. If a provider section of the description is determined to be invalid, it is ignored. |
Listing 10-2 shows the basic configuration of the <custom-rdb-provider> element in an Configurable Relational Provider. This configuration is based on a schema file that is provided with ALDSP.
Each of the properties are described in greater detail in Configurable Relational Provider Reference. For a complete example, see Sample Configurable Relational Provider File.
<custom-rdb-provider xmlns="http://www.bea.com/ns/aldsp/rdb/extension/custom">
<database-kind>
<match-database>
XQuery expression that uses a predefined external function to
Access JDBC metadata. Result type: boolean
</match-database>
<base-version>
Base database version supported by this provider (decimal)
</base-version>
<matched-version>
XQuery expression returning matched version. Result type: decimal
</matched-version>
</database-kind>
<database-objects>
<catalog quote?="string" separator?="string"/>
<schema quote?="string" separator?="string" />
<table quote?="string" separator?="string"
qualified-name-parts="string*"/>
<column quote?="string" />
<procedure quote?="string" qualified-name-parts="string*"/>
</database-objects>
<joins inner-join="boolean" outer-join="boolean">
<sql92 right-trees="boolean(:=true)" /> or
<sql89 outer-join-kind?="columnModifier|tableModifier"
outer-join-modifier?="string" />
</joins>
<orderby column?="boolean" expression?="boolean" aggregate?="boolean"
null-order?="low|high|first|last|undefined"
style?="ordering-expression|ordering-expression-with-projection|
position-in-project-list" />
<groupby column?="boolean" constant?="boolean" expression?="boolean" />
<subqueries in-from?="boolean" in-where?="boolean" />
<case supported?="boolean(:=true)" />
<functions default-syntax-for-empty-input="lax|strict|strict-coalesce">
<function name="QName" arity?="integer" supported?="boolean(:=true)"
infix?="boolean(:=false)">
SQL expression which uses {0},{1},…{n} for input expressions
(string)
</function>
</functions>
<casts>
<cast from="QName" from-subtypes?="boolean(:=false)" to="QName"
supported?="boolean(:=true)">
SQL expression which uses {0} for input expression
</cast>
</casts>
<limit supported?="boolean(:=true)">
<top parameter="true|false" composable="true|false"/> or
<rownum kind="project_first|filter_first">
ROWNUM
</rownum>
</limit>
<insert>
<key-gen kind?="jdbc|sql-pre|sql-post">
SQL statement
</key-gen>
</insert>
<properties
supports-query-timeout = "boolean"
supports-cancel-query = "boolean"
supports-multiple-active-queries-per-connection = "boolean"
/>
</custom-rdb-provider>
Table 10-2 describes each of the sub-elements and properties of the <custom-rdb-provider> element of an XML Customization Provider configuration file.
For a summary of the file format, see Overview of the <custom-rdb-provider> Element. For a complete example, see Sample Configurable Relational Provider File.
| Tip: | Most of the settings listed in Table 10-2 are optional. Any settings that are specified in the configuration file override default settings provided by the parent provider. The parent provider is specified with the <parent> element of the descriptor. If no setting is provided for an attribute, then the request is delegated to the parent provider. See Overview of the Extension Framework Architecture for a description of the way in which providers delegate to parent providers in a “chains.” |
These elements contain XQuery expressions that can access JDBC database metadata through predefined external functions. See Database Matching.
|
||||
|
The general rule is: if the number of characters in the specified quote string is even – then it is assumed that open and close quotes are different. The first half of the specified string is the open quote; the second half is the close quote. If the number of characters in the specified string is odd then it is assumed that the open and close quotes are the same and equal to the whole string.
|
||||
The qualified-name-parts attribute specifies a list of object kinds that specify how a fully qualified name is constructed for this database object.
|
||||
|
||||
|
||||
An enumeration of strings that define which default syntax to use in the presence of NULL input. NULL (an empty sequence in XQuery) input is usually handled differently by SQL and XQuery functions. In SQL, NULL is usually propagate to the output of a function. For example: f(NULL)=NULL). In XQuery, however, NULL is usually replaced with a value. For string functions, such as
f( () ) = ””, sum( () )=0, and so on. This setting specifies how to deal with such situations when choosing default SQL syntax for a function.
|
||||
|
The contents of this sub-element is a SQL expression that must be generated for the named function. Parameters are specified as {0}, {1}, … {n}.
A variable number of parameters is supported. See Specifying SQL Syntax for Functions for more information on the format.
The contents of this element can be empty. In this case, the default syntax for this function is used for SQL generation. A list of default syntaxes is provided in Default SQL Syntax for Functions.
For examples, see Sample Configurable Relational Provider File.
|
||||
(Required) Specifies the QName of a function. See Function and Type Name Resolution Process.
|
||||
|
The content of this element is the SQL expression that must be generated for this cast operation. The parameter is specified as {0}.
For examples, see Sample Configurable Relational Provider File.
|
||||
These attributes specify the QNames of input and target XQuery types. If only a local name is specified, ALDSP searches for the type in well-known namespaces.
For examples, see Sample Configurable Relational Provider File. See also Standard and ALDSP Namespaces for Functions and Types.
|
||||
(Boolean) Specifies whether the matching input type must also match its subtypes (according to XQuery type hierarchy).
For examples, see Sample Configurable Relational Provider File.
|
||||
Specifies that fn:subsequence() must be pushed down using the TOP modifier of the SELECT clause. For example: SELECT TOP n FROM ...
|
||||
Specifies that fn:subsequence() should be translated into SQL as a LIMIT-like clause added at the end of a SQL query.
|
||||
|
||||
A string constant (enumeration) that defines the key generation strategy. This attribute must specify one of the following values:
|
||||
This section describes how ALDSP determines the best database match for a given provider. Database matching logic is specified as an XQuery expression that can access JDBC database metadata through predefined XQuery external functions.
Matching expressions are specified in the Configurable Relational Provider elements ( Table 10-2, Configuration Elements and Attributes Description, on page 10-20) and are evaluated by the ALDSP XQuery engine. Expressions can use standard XQuery functions supported by the ALDSP XQuery engine as well as additional functions defined by the Configurable Relational Provider. Database matching XQuery expressions return an xs:boolean value.
Another use of matching XQuery expressions is to compute the matched database version (in this case the result must be xs:decimal).
This section includes these topics:
The framework employs matching rules to determine if a given provider is compatible with a database. During the metadata import process (see Importing Relational Source Metadata) the relational provider registry determines which providers support the database being imported. For successful matches, the base version offset is also obtained. The base version offset is calculated as:
Base version offset (decimal) = (matched db version – base db version returned by the provider)
Base version decimals for the standard providers are listed in Table 10-1 in the section Relational Providers Included With ALDSP.
The Datasource Import Wizard uses the base version offset to display providers when there are multiple matches. The wizard’s drop down menu contains providers with the minimum base version offset (that is, the closest version to the database). The best match appears at the top of the drop down menu. For information on the Datasource Import Wizard, see Creating and Updating Physical Data Services in the Data Services Developer’s Guide.
For example, consider the standard DB2 relational provider. This provider matches all DB2 versions starting from 8. Its base version is 8. Assume that a new DB2 provider is created with the Configurable Relational Provider that matches DB2 9 with base version 9. During metadata import of a table from the DB2 9 instance, both providers will match the database. However, for the first provider, the base version offset is 1, but the second one is be 0. Therefore, the second provider will be preferred over the first one.
This section describes the mapping of a java.sql.DatabaseMetaData instance to a set of XQuery functions that can be used by a database matching expression.
Mapped interface: java.sql.DatabaseMetaData
Requirements for mapped methods:
Table 10-3 lists the java.sql.DatabaseMetaData methods that satisfy these requirements and their corresponding JDBC methods and XQuery functions.
This section describes additional functions that are available in the database matching expression, but are not directly mapped from the jdbc.sql.DatabaseMetaData interface.
Table 10-4 lists and describes the function signatures.
This section discusses the SQL syntax for functions specified in the Configurable Relational Provider deployment descriptor. See also Configurable Relational Provider Reference and the example descriptor in Sample Configurable Relational Provider File.
This section includes these topics:
Function SQL syntax is specified as a string with placeholders for each parameter. The syntax defines a SQL fragment to be generated by the relational wrapper when translating the corresponding XQuery function into SQL. It is specified as the content of the <function> element.
<function name=”fn:lower”>LOWER({0})</functions> Parameter placeholders start with 0. There can be more than one placeholder with the same index which means that the argument must be replicated in the generated SQL.
<function name=”fn:substring” arity=”2”>SUBSTR({0}, {1}, LENGTH({0})-{1}+1) </function> Functions with a variable number of arguments can be specified in two different ways:
These methods are described in the next two sections.
The infix attribute of the function element is set as follows:
<function name=”fn:concat” infix=”true”>||</function>
The generated SQL for this example is:
arg1 || arg2 || arg3 || ... || argN
During SQL generation the variable length placeholder {...} is replaced with the remaining arguments separated by commas.
<function name=”fn:concat”>CONCAT({...})</function>CONCAT(arg1,arg2,arg3,….,argN)
If another delimiter is required, it must be specified inside the variable length placeholder as follows:
{...DELIMITER} <function name=”fn:concat”>COALESCE({... || }, ””)</function>COALESCE(arg1 || arg2 || arg3 || … || argN, ””)
| Note: | In this case the delimiter is “|| “. |
The default syntax for a function is used when the function is specified in the <functions> section of the Configurable Relational Provider configuration file (Table 10-2), but its syntax is not provided by the user (the <function> element content is empty). For some functions in this case, the relational provider chooses default syntax based on the default-syntax-for-empty-input attribute. See Configurable Relational Provider Reference for information on the default-syntax-for-empty-input attribute.
This section lists the default syntaxes used for the three possible values of the default-syntax-for-empty-input attribute.
Functions for which the default SQL syntax depends on the default-syntax-for-empty-input attribute are denoted with an asterisk (*) in Table 10-5, Table 10-6, and Table 10-7.
If default syntax is not defined for a function, then you must specify the syntax of the function when you use it. Otherwise, it is an error.
The XQuery Functions and Operators specification defines built-in operators into which arithmetic and comparison operations are translated. For some operations ALDSP defines additional operators that it uses for evaluation. These additional operators can also be used for specifying XQuery to SQL translation.
| Tip: | For references to the XQuery specifications, see Supported XQuery Specifications in the XQuery and XQSE Developer’s Guide. |
For each of the following arithmetic operations, ALDSP defines more specific operations for the following types: integer, decimal, double, float. These specific operations can be used to specify a better type match when defining a SQL generation rule.
| Tip: | The function operation prefixes used in the expanded operations (such as op-bea) are discussed in Standard and ALDSP Namespaces for Functions and Types. |
For example, the following four operations are defined for op:numeric-add:
Comparison operations in the XQuery are defined by three operators:
ALDSP adds three more operations for each type:
For numeric types, each operator op-bea:numeric-<comparison_op> is further expanded into four numeric types:
op-bea:integer-<comparison_op>, op-bea:decimal-<comparison_op>, op-bea:double-<comparison_op>, op-bea:float-<comparison_op>.
Additional numeric comparisons added by ALDSP follow the same pattern. For example
op-bea:numeric-not-equals is expanded into:
All six string comparison operators are defined as ALDSP specific operators:
Table 10-8 lists the standard and ALDSP namespaces for functions and types. Table 10-9 lists and describes each of the type namespaces.
The Relational Wrapper Extension Framework looks up functions, operators, and types by name as follows:
For example, suppose the following function definition exists:
<function name=”round”>ROUND({0})</function>
First, that name is resolved to a QName in the default element namespace and looked up. Suppose then that the XQuery function with this name is not found (for example, if there was no default namespace used in the XML document). Then the system will try start searching for the following functions (in this order): fn:round, op:round, fn-bea:round, op-bea:round. The system will find fn:round and register it with the specified SQL syntax.
A similar lookup process is applied for types when reading cast operation definitions. For types, the system automatically searches in xs, xdt and dt-bea namespaces.
Note that the arity attribute is also optional and only required to disambiguate between functions with the same name, for example, a substring with 2 and 3 arguments.
ALDSP provides a group of three abstract base classes that provide functionality to the Configurable Relational Provider. The AbstractSQLProvider class is the default parent class of the Configurable Relational Provider. You can specify an abstract provider class in the Configurable Relational Provider’s deployment descriptor with the parent element. See Using the Configurable Relational Provider.
This section discusses the abstract relational provider classes, and contains these sections:
AbstractSQLProvider is an abstract base class. All other abstract and concrete relational provider classes extend this class. This class is used as a parent provider when the parent is not specified in the deployment descriptor of a provider; therefore, this class is not explicitly registered in the provider registry.
Table 10-10 summarizes the level of SQL support provided by AbstractSQLProvider:
Table 10-11 lists the supported functions and operators for AbstractSQLProvider.
AbstractSQL89Provider extends AbstractSQLProvider (see AbstractSQLProvider). This class adds support for additional clauses, functions, and updates. The AbstractSQL89Provider class includes these features:
select … from A,B where A.<x> = B.<x>). Table 10-12 lists the supported functions and operators for AbstractSQL89Provider. These functions and operators are in addition to the ones provided by the parent class, AbstractSQLProvider.
AbstractSQL92Provider extends AbstractSQL89Provider (see AbstractSQL89Provider). This class adds support for SQL92-style joins (inner and outer), subqueries, and other features. The AbstractSQL92Provider class supports:
Table 10-13 lists the supported functions and operators for AbstractSQL92Provider. These functions and operators are in addition to the ones provided by the parent class, AbtractSQL89Provider.
Table 10-14 lists the cast operations that are pushed down by AbstractSQL92Provider.
|