EclipseLink/Development/DBWS/Overview

Overview of EclipseLink DBWS

The purpose of this document is to provide a high-level overview of the design and runtime portions of the DBWS component. In addition, an outline of relevant changes from version to version will be provided. The EclipseLink DBWS documentation can be found here: http://www.eclipse.org/eclipselink/documentation/2.5/dbws/toc.htm.

Design time Component

DBWS builder is responsible for processing input from the user (or tooling, such as JDeveloper), then scraping the database DDL based on this input. The information retrieved from the database is used to construct a meta-model, which is in turn used to generate a number of artifacts required by the DBWS runtime in order to service SOAP message requests.

The following artifacts are generated by the builder:

• JPA metadata
• JAXB metadata
• Sessions XML
• DBWS XML-Relational (XR) file (defines query operations used by the XR runtime)
• XML Schema
• WSDL (JAX-WS 2.0)
• web.xml
• DBWSProvider (Web service provider - deployed in a servlet container)
• ProviderListener (servlet listener impl.)

Typical .war file structure

   \---web-inf
   |
   |   web.xml
   |
   +---classes
   |   +---META-INF
   |   |       eclipselink-dbws.xml
   |   |       eclipselink-dbws-or.xml
   |   |       eclipselink-dbws-ox.xml
   |   |       eclipselink-dbws-sessions.xml
   |   |
   |   \---_dbws
   |           DBWSProvider.class            -- auto-generated JAX-WS 2.0 Provider
   |           ProviderListener.class        -- auto-generated Servlet listener
   |
   \---wsdl
           eclipselink-dbws-schema.xsd
           eclipselink-dbws.wsdl
           swaref.xsd                        -- optionally generated if swaRef is being utilized

Basic Flow

The basic flow of DBWS builder is as follows:

1. User input --> Scrape DB & build meta-model
2. Generate OR/OX projects based on meta-model
3. Generate XML schema based on OX project
4. Generate XR Service Model based on OR project
5. Generate WSDL based on service model
6. Generate Web app and WebService implmentation
7. Generate JAXB and JPA metadata files based on OR/OX projects
8. Package generated artifacts

Supported Input

Following are the types of input DBWS supports:

• DB Table
  • CRUD operations generated by default
  • Custom SQL statements - allows additional operations to be defined on a given table, for example, the ability to search for one or more entries based on a field other than the PK field
  • Nested procedures are supported
• Secondary SQL
  • Allows separate design/runtime SQL statements to be defined
  • Design time SQL used to build operations and runtime SQL executed at runtime
• Stored function/procedure
• PL/SQL stored function/procedure

User/Tooling Input

At a minimum, DBWS builder requires database connectivity information and one or more database targets as listed in Supported Input above. The input can be passed to the builder as an XML file or as Java objects (builder creates the Java model based on the input XML, but this model can be created and handed to the builder).

Following is sample input XML:

<?xml version="1.0" encoding="UTF-8"?>
<dbws-builder xmlns:xsd=\http://www.w3.org/2001/XMLSchema\>
  <properties>
    <property name="projectName">tabletype</property>
    <property name="logLevel">off</property>
    <property name="username">scott</property>
    <property name="password">tiger</property>
    <property name="url">jdbc:oracle:thin:@localhost:1521:ORCL</property>
    <property name="driver">oracle.jdbc.OracleDriver</property>
    <property name="platformClassname">org.eclipse.persistence.platform.database.oracle.Oracle11Platform</property>
  </properties>
  <table
     schemaPattern="SCOTT"
     tableNamePattern="EMPLOYEE"
  />
</dbws-builder>

And the equivalent Java code:

DBWSBuilder builder = new DBWSBuilder();
builder.setProjectName("tabletype");
builder.setLogLevel("off");
builder.setUsername("scott");
builder.setPassword("tiger");
builder.setUrl("jdbc:oracle:thin:@localhost:1521:ORCL");
builder.setDriver("oracle.jdbc.OracleDriver");
builder.setPlatformClassname("org.eclipse.persistence.platform.database.oracle.Oracle11Platform");
 
TableOperationModel tableOpModel = new TableOperationModel();
tableOpModel.setSchemaPattern("SCOTT");
tableOpModel.setTablePattern("EMPLOYEE");
builder.addOperation(tableOpModel);

Note: the 'projectName' property will be used as the default namespace.

Wildcards

Wildcards are supported for the schema, table, and procedure pattern values. For example, if the schemaPattern value is set to "%", all schemas visible to the current user are checked for the target artifact (table, procedure, etc.). Another example would be where all stored procedures starting with "EMP" are to be processed:

And the equivalent Java code:

procOpModel = new ProcedureOperationModel();
procOpModel.setName("EmployeeTest");
procOpModel.setCatalogPattern("TOPLEVEL");  <-- indicates the stored proc is not in a PL/SQL package
procOpModel.setProcedurePattern("EMP%");

Simple XML Format

DBWS provides the ability to wrap results in custom XML tags; this is useful where the structure of the results are not known, such as in the case of a ref cursor. If the 'isSimpleXmlFormat' flag is set to true, the results will look like the following:

<?xml version="1.0" encoding="UTF-8"?>
<simple-xml-format>
  <simple-xml>
    <ID>1</ID>
    <NAME>Joe Oracle</NAME>
  </simple-xml>
  <simple-xml>
    <ID>2</ID>
    <NAME>Jane Oracle</NAME>
  </simple-xml>
</simple-xml-format>

Note that both the simple-xml-format and simple-xml element names are customizable.

Shadow Types & Anonymous PL/SQL Generation

JDBC does not support transporting Oracle PL/SQL types. In order for DBWS to handle PL/SQL input and output arguments, two tasks are required. First, for each PL/SQL type there must be an equivalent JDBC type. DBWS will generate 'shadow' create/drop DDL for each PL/SQL type; this DDL can be executed on the DB to add and remove these required types. The second thing that is required are PL/SQL functions that covert PL/SQL types to equivalent JDBC types and vice-versa. To support this, anonymous PL/SQL is generated and executed on the database at runtime.

Shadow Type Example

If a given package SOMEPKG has a PL/SQL record ORECORD defined as follows:

CREATE OR REPLACE PACKAGE SOMEPKG AS
  TYPE ORECORD IS RECORD (
    O1 VARCHAR2(10),
    O2 DECIMAL(7,2)
  );
END PACKAGE SOMEPKG;

DBWS requires that an equivalant JDBC shadow type also exists at the TOPLEVEL (not in a package), i.e.:

CREATE OR REPLACE TYPE SOMEPKG_ORECORD AS OBJECT (
  O1 VARCHAR2(10),
  O2 DECIMAL(7,2)
)

Note that the expected naming convention for the shadow JDBC type is packagename_plsqlname.

ShadowDDLGenerator

DBWS builder generates required shadow create and drop DDL such that the user doesn't have to create the required DDL manually. Following is an example of using the builder-generated shadow DDL:

Connection conn = buildSQLConnection();
PreparedStatement pStmt;
 
// execute shadow type ddl to generate JDBC equivalents of PL/SQL types 
for (String ddl : builder.getTypeDDL()) {
    try {
        pStmt = conn.prepareStatement(ddl);
        pStmt.execute();
    } catch (SQLException e) {
        // handle exception
    }
}

Note that similar code is used to execute the drop DDL.

Anonymous PL/SQL Code Generation

Considering a PL/SQL stored procedure that returns a new ORECORD:

CREATE OR REPLACE PACKAGE SOMEPKG AS
  PROCEDURE GETNEWREC(NEWREC OUT ORECORD);
END PACKAGE SOMEPKG;

The following anonymous PL/SQL will be generated and executed against the database at runtime:

DECLARE
  NEWRECTARGET SOMEPKG.ORECORD;                      <!-- PL/SQL record declaration -->
  FUNCTION EL_PL2SQL_0(aPlsqlItem SOMEPKG.ORECORD)   <!-- Converts the new PL/SQL record to it's shadow JDBC type -->
  RETURN SOMEPKG_ORECORD IS
    aSqlItem SOMEPKG_ORECORD;
  BEGIN
    aSqlItem := SOMEPKG_ORECORD(NULL, NULL);
    aSqlItem.N1 := aPlsqlItem.N1;
    aSqlItem.N2 := aPlsqlItem.N2;
    RETURN aSqlItem;
  END EL_PL2SQL_0;
BEGIN
  SOMEPKG.GETNEWREC(NEWREC=>NEWRECTARGET);           <!-- The OUT argument 'NEWREC' from the PL/SQL procedure is passed to -->
  :1 := EL_PL2SQL_0(NEWRECTARGET);                   <!-- the anonymous function to be converted to the shadow JDBC type   -->
END;

Note that this generation occurs in the prepareInternal method of PLSQLStoredProcedureCall.

DDL Parser

To provide more complete handling of Oracle PL/SQL and 'advanced' JDBC types (Varray, Object and Object table types), the DDLParser was created. This JavaCC constructed parser is responsible for building database meta-model objects from a given string of Oracle DDL.

More information regarding the DDLParser can be found here:

• http://wiki.eclipse.org/EclipseLink/Development/DBWS/ParseDDLFS
• http://wiki.eclipse.org/EclipseLink/Development/DBWS/ParseDDLDS

Git repo:

• http://git.eclipse.org/c/eclipselink/oracleddlparser.git

JavaCC plugin for Eclipse:

• http://eclipse-javacc.sourceforge.net/

JPA Metadata Generation

• JPA now better supports PL/SQL based queries and structures via JPA
• org.eclipse.persistence.tools.dbws.XmlEntityMappingsGenerator
• Generates an XMLEntityMappings instance based on a given OR Project's Queries and Descriptors
  • Database query --> Query metadata
  • Descriptor --> Entity
  • Aggregrate descriptor --> Embeddable
    • Ids <-- direct mapping for PK field
    • Basics <-- direct mapping
    • Arrays <--
    • Structures <--
    • Embeddeds <--

• • • PLSQLCollectionType
    • PLSQLRecordType
    • ObjectTableType
    • ObjectType
    • VArrayType

• • • NamedPLSQLStoredProcedureQueryMetadata
    • NamedPLSQLStoredFunctionQueryMetadata
    • NamedStoredProcedureQueryMetadata
    • NamedStoredFunctionQueryMetadata
    • NamedNativeQueryMetadata

• • • PLSQLRecords
    • PLSQLTables
    • OracleObjectTypes
    • OracleArrayTypes

• • • STRUCT
    • ARRAY

JAXB Metadata Generation

• org.eclipse.persistence.tools.dbws.XmlBindingsGenerator
• Generates one or more EclipseLink XmlBindings objects based on a given list of XMLDescriptors

Legacy Deployment XML Support

To support archives that were generated prior to EclipseLink 2.5, we need to handle loading OR/OX deployment XML at runtime. The basic algorithm is to first attempt to load JAXB/JPA metadata, and if this fails try to load the legacy deployment XML project.

Runtime Component

The DBWS runtime component is responsible for fulfilling SOAP message requests. DBWS makes use of EclipseLink JAXB (static and dynamic), JPA and XR to accomplish this.

Basic Flow

The basic flow of the DBWS runtime is as follows:

1. TBD: metadata/legacy xml loaded ...
2. SOAP message received and passed to the DBWS runtime
3. SOAP body is unmarshaled into an XR Invocation object
4. Query Operation is retrieved from the service model based on the invocation name
5. Query operation is invoked
6. Query results are used to generate a SOAP response message
7. SOAP response is returned to the caller

Tests

There are tests in two different projects; dbws and utils. The tests in the dbws project focus on XR runtime testing, whereas the tests in the utils project are end-to-end tests. Additionally, there are server tests in the utils project that allow the builder to be executed, then the generated .war can be deployed to an app server and the service tested (via SOAP).

XR (dbws project)

• eclipselink.dbws.test
• eclipselink.dbws.test.oracle

DBWS builder (utils project)

• eclipselink.dbws.builder.test
• eclipselink.dbws.builder.test.oracle
• eclipselink.dbws.builder.test.oracle.server

Test Configuration

The following properties need to be set for the tests to run successfully (typically via test.properties in user_home):

1. db.user
2. db.pwd
3. db.driver
4. db.url
5. db.platform
6. jdbc.driver.jar
7. logging.level (optional)
8. support.test (should set this to true)

For server tests, the following additional properties need to be set:

1. server.platform
2. server.host
3. server.port
4. server.datasource

Following is a sample test.properties file

# Testing environment properties
 
#
# Database properties
#
db.user=SCOTT
db.pwd=toger
db.driver=oracle.jdbc.OracleDriver
db.url=jdbc:oracle:thin:@ottvm000.ca.oracle.com:1521:ORCL
db.platform=org.eclipse.persistence.platform.database.oracle.Oracle11Platform
jdbc.driver.jar=D:/extension.oracle.lib.external/ojdbc6.jar
 
#
# WLS properties
#
server.platform=wls
server.host=localhost
server.port=7001
server.datasource=jdbc/DBWStestDS
 
#
# TopLink properties
#
# Logging option for debugging - 'info' for light logging, 'fine' for SQL stmts, 'finest' for everything
logging.level=off
 
support.test=true

ANT

The XR/Builder tests can be run by executing:

• ant -f antbuild.xml run-tests

For the server tests, the following two targets can be executed:

• ant -f antbuild.xml test-builder
• ant -f antbuild.xml test-service <-- deploy generated .war files first

Evolution of DBWS

EclipseLink 2.4

EclipseLink 2.4 was the first release containing the Oracle DDL parser. This JavaCC based parser was designed to better handle parsing Oracle-specific DDL, providing the ability to process PL/SQL and advanced Oracle JDBC types, such as Varray, Object and Object table types. Another major change was abandoning the visitor pattern that was used to create projects and descriptors based on the types discovered in the DDL. As of 2.4, a database type meta-model is constructed based on results of the DDLParser parse or JDBC driver metadata, and this meta-model is used to build the various required artifacts.

Information about the DDLParser's design and functionality can be found here: http://wiki.eclipse.org/index.php?title=EclipseLink/Development/DBWS/ParseDDLDS . In particular, the "Parsing Technology" section is worth reviewing.

EclipseLink 2.5

The major change in this release was removal of the legacy EclipseLink deployment XML project files in favor of JPA/JAXB metadata. In addition, much of the code was reworked to accommodate generation of the metadata vs. deployment XML artifacts.

It is important to note that 2.5 and 2.6 DBWS code bases are virtually identical.

Metadata Generation

For 2.6 we were planning on generating a JPA meta-model directly from the DDLParser meta-model objects, as opposed to generating OR/OX projects to use to build the required artifacts, then the JPA meta-model at the end of the process. JAXB would have followed, however, this work was just under way when DBWS was shelved. The code is in the utils project in the org.eclipse.persistence.tools.metadata.generation package.

Note that there are tests for metadata generation in the org.eclipse.persistence.tools.metadata.generation.test package in the utils project; these tests were intended to test JPA metadata generation based on the DDLParser meta-model, and unless that work is continued/completed they aren't relevant.