Notice: This Wiki is now read only and edits are no longer possible. Please see: https://gitlab.eclipse.org/eclipsefdn/helpdesk/-/wikis/Wiki-shutdown-plan for the plan.
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:
- 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.
- columnType - integer code corresponding to
java.sql.Types
(NB - some JDBC drivers and/or database use non-standard code numbers) - columnTypeName - the symbolic name for the integer columnType
- precision, scale - for numeric data, precision is the total number of digits, scale the number of digits to the right of the decimal point.
- 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