EclipseLink/Development/DBWS/CustomSQLDesignTimeSchema

{NB - this capability is available starting in version 2.2 of DBWS}

Custom SQL Design-time Schema

Overview

The use-case is the creation of a Web service that exposes the results of executing some custom SQL SELECT statement, without exposing the actual SQL. Currently, DBWS determines the 'shape' of the returned result at the time the SELECT statement is executed (i.e. at runtime, not design-time). For example, the following DBWSBuilder file produces a SXF (Simplified XML Format) document where the element tag names are direct copies of column names:

<?xml version="1.0" encoding="UTF-8"?>
<dbws-builder xmlns:xsd="http://www.w3.org/2001/XMLSchema"
    <properties>
        <property name="projectName">Semployees</property>
        ...
    </properties>
    <sql
        name="Semployees"
        isCollection="false"
        >
        <text><![CDATA[select * from EMP where ENAME like 'S%']]></text>
    </sql>
</dbws-builder>

<?xml version = '1.0' encoding = 'UTF-8'?>
<simple-xml-format>
  <simple-xml>
    <EMPNO>7788</EMPNO>
    <ENAME>SCOTT</ENAME>
    <JOB>ANALYST</JOB>
    <MGR>7566</MGR>
    <HIREDATE>1987-04-19</HIREDATE>
    <SAL>3000</SAL>
    <DEPTNO>20</DEPTNO>
  </simple-xml>
  <simple-xml>
    <EMPNO>7369</EMPNO>
    <ENAME>SMITH</ENAME>
    <JOB>CLERK</JOB>
    <MGR>7902</MGR>
    <HIREDATE>1980-12-17</HIREDATE>
    <SAL>800</SAL>
    <DEPTNO>20</DEPTNO>
  </simple-xml>
</simple-xml-format>

New Capability: generate schema at design-time

The custom SQL SELECT statement is executed at design-time and the returned java.sql.ResultSet examined via the java.sql.ResultSetMetaData APIs - getColumnCount(), getColumnLabel(), getColumnType(int column), etc. From this information, an eclipselink-dbws-schema.xsd file is created along with all the other required DBWS meta files (eclipselink-dbws.xml, eclipselink-dbws.wsdl, eclipselink-dbws-ox.xml, etc.)

The resulting schema for the above example would be:

<?xml version="1.0" encoding="UTF-8"?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" targetNamespace="urn:emp" xmlns="urn:emp" elementFormDefault="qualified">
   <xsd:complexType name="empType">
      <xsd:sequence>
         <xsd:element name="empno" type="xsd:decimal"/>
         <xsd:element name="ename" type="xsd:string" minOccurs="0" nillable="true"/>
         <xsd:element name="job" type="xsd:string" minOccurs="0" nillable="true"/>
         <xsd:element name="mgr" type="xsd:decimal" minOccurs="0" nillable="true"/>
         <xsd:element name="hiredate" type="xsd:date" minOccurs="0" nillable="true"/>
         <xsd:element name="sal" type="xsd:decimal" minOccurs="0" nillable="true"/>
         <xsd:element name="comm" type="xsd:decimal" minOccurs="0" nillable="true"/>
         <xsd:element name="deptno" type="xsd:decimal" minOccurs="0" nillable="true"/>
      </xsd:sequence>
   </xsd:complexType>
   <xsd:element name="empType" type="empType"/>
</xsd:schema>

Strategy

The key to this feature is the execution of the SELECT statement at design-time. The user must provide a primary and secondary SELECT statement. The secondary statement is optimized with a 'nonsense' WHERE clause (WHERE 0=1) (or optimized using platform-specific hints i.e.g SELECT TOP 1) to reduce the cost of preparing and executing the query. Even if no rows are returned, the ResultSet metadata will still have information about the number, name and type of columns involved in the query.

Work items

Changes to DBWS builder file

A new field build-statement is added to the sql Query operation:

<?xml version="1.0" encoding="UTF-8"?>
<dbws-builder xmlns:xsd="http://www.w3.org/2001/XMLSchema"
    <properties>
        <property name="projectName">emp</property>
        ...
    </properties>
    <sql
        name="Semployees"
        isCollection="false"
        returnType="empType"
        >
        <statement><![CDATA[select * from EMP where ENAME like 'S%']]></statement>
        <build-statement><![CDATA[select * from EMP where 0=1]]></build-statement>
    </sql>
</dbws-builder>

The build-statement is executed and metadata from the resultSet analyzed to create an XSD schema. The top-level name for the schema type is specified by the returnType element. If the build-statement element is missing, then a SXF document will be built at runtime.

Done

Changes to JDBCHelper

Once the build-statement has been executed, the ResultSetMetaData is examined to extract:

1. columnLabel - name of the column as specified in SQL statement; or, if a column alias used (e.g. SELECT COUNT(*) as 'employeeCount' from emp), the column alias.
2. columnType - integer code corresponding to java.sql.Types (NB - some JDBC drivers and/or database use non-standard code numbers)
3. columnTypeName - the symbolic name for the integer columnType
4. precision, scale - for numeric data, precision is the total number of digits, scale the number of digits to the right of the decimal point.
5. isNullable - can the column contain null values

Done

Changes to DBWSBuilder processing

Once the set of DbColumn's is built, OR and OX projects are created (very similar to the tables use-case). In addition to generating the eclipselink-dbws-or.xml and eclipselink-dbws-ox.xml, the eclipselink-dbws-schema.xsd file is auto-generated from the OX project (re-using SchemaModelGenerator)

Done

Limitation 1 - repeated labels: above it says 'set' - this points out a limitation with this feature. It is perfectly valid to have a SQL statement with multiple identical columns:

SELECT ENAME, ENAME FROM EMP WHERE 0=1

ENAME	ENAME
SMITH	SMITH
...	...
SCOTT	SCOTT

The above is admittedly nonsensical, but a SELECT statement that uses UNION could return a set of column labels where a label is repeated.
DBWSBuilder will keep track of 'already processed columns' and throw an exception when it detects a duplicate.

Limitation 2 - compatible column label sets: the primary and secondary SQL statements must return compatible column label sets. At this time, no pre-processing is done to ensure that the column sets are the same; the error will be detected at runtime when the service is invoked.

Changes to WSDLGenerator

Integrate newly-generated eclipselink-dbws-schema.xsd (previously, Custom SQL produced Simplified XML Format docs whose schema was 'faked' to be <xsd:sequence> <xsd:any minOccurs="0"/> </xsd:sequence>)

Done

End-to-end SOAPMessage testing

Built dbws.testing.secondarysql.SecondarySQLTestSuite

Done