EclipseLink/Development/DBWS/ParseDDLSprints

DDL Parsing Sprints

Sprint #1: build CRUD Webservice from sample Table DDL (done)

• The goal of this sprint is to produce a functional end-to-end use case: a DBWS Web service that exposes a database table's CRUD (Create/Read(findByPK,findAll)/Update/Delete) lifecycle operations. The refactored DBWSBuilder utility takes a standard DBWS configuration file as input:

<?xml version="1.0" encoding="UTF-8"?>
<dbws-builder xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <properties>
    <property name="projectName">emp</property>
    ... database properties
  </properties>
  <table
    catalogPattern="%"
    tableNamePattern="EMP"
  />
</dbws-builder>

prompt > dbwsbuilder.cmd -builderFile dbws-builder.xml -stageDir output_directory -packageAs wls emp.war

and produces a .war file suitable for deployment on WLS 10.3.5

• to reduce 'scope' of sprint: Table columns only use scalar types (i.e. no VARRAY, Nested tables, Object types)

Workitem	Assigned to	Description	Estimate
DatabaseTypeBuilder	Mike Norman	pattern matching for schema and table names	~2 days
grammar for Table	Mike Norman	ensure JSON grammar productions can describe a Table	~3 days
Model objects for Table	Mike Norman	ensure model objects fully represent a Table as an in-memory object-graph	~3 days
Visitor for Table	Dave McCann	ensure Table object-graph can be 'walked' from root to leaves	~4 days
Generate ORM map from Table visitor	Dave McCann	build OR project/descriptors/mappings to represent Table entity from database POV	~3 days
Generate OXM map from Table visitor	Dave McCann	build OX project/descriptors/mappings to represent Table entity from SOAP msg POV	~3 days
Generate Schema for Table	Dave McCann	build XML schema to represent Table entity (to be used in WSDL)	~3 days
Generate WSDL for Webservice	Dave McCann	build WSDL schema to represent CRUD operations	TBD (small # of days: can leverage existing DBWSBuilder code)

Sprint #2: build Webservice from simple Stored Procedure/Function (done)

• The goal of this sprint is to produce a functional end-to-end use case: a DBWS Web service that exposes a StoredProcedure (or StoredFunction). To manage the scope and complexity of this sprint, only scalar argument types will be supported.
  • backlog from Sprint #1
    1. custom SQL
    2. SQL nested under TableOperations
  • make sure that ProcedureOperations nested under TableOperations work
    • for both non-Oracle and Oracle platforms
  • test for IN, OUT, INOUT and RETURN arguments

Sprint #3: build Webservice from simple Stored Procedure/Function in a PL/SQL package (done)

• Extend the work from Sprint #2: StoredProcedures with only scalar arguments, but these procedures are not located at TOPLEVEL, they are inside of PL/SQL packages.
  • backlog from Sprint #2:
    1. Handle resultSet from Stored Procedure via SYS_REFCURSOR OUT parameter (bug# 356617)
    2. Handle OUT parameter(s) for Stored Functions (bug# 356637)

Sprint #4: build Webservice from Stored Procedure/Function in a PL/SQL package (done)

• Extend the work from Sprint #3: StoredProcedures with PL/SQL Record types as arguments (completed)
  • backlog from Sprint #3 (tbd)
  • test for IN, OUT, INOUT and RETURN arguments

Sprint #5: build Webservice from Stored Procedure/Function in a PL/SQL package (done)

• Extend the work from Sprint #4: StoredProcedures with PL/SQL Collection types as arguments (complete)
  • backlog from Sprint #4
    • create & drop DDL for 'shadow' types
  • include support for PL/SQL Record types that include PL/SQL Collection types and vice-versa

Sprint #6: support Advanced Oracle types (ongoing)

• Add support for Advanced Oracle types: OBJECT's, VARRAY's and Object-type tables (CREATE TYPE PERSONTYPE_TABLE AS TABLE OF PERSONTYPE)
  • backlog from Sprint #5 (tbd)
  • have to re-visit existing Table and StoredProcedure support (TOPLEVEL and inside PL/SQL packages)

Sprint #7: Resolve types left unresolved after parsing (ongoing)

After parsing a top-level artifact (StoredProcedure/Function, Package, Object type, etc.) some nested database types may be unresolved as they may refer to other top-level artifacts. The following is a list of use cases:

1. in PL/SQL package: types that use %ROWTYPE and %TYPE macros: point to columns of tables (in this or granted schemas)
2. in top-level StoredProcedure/Function: argument uses top-level types
3. in top-level Object type: fields point to other Object types(s)
   • also top-level Varray types
   • also top-level Object tables
   • and nested combinations of all three

11/11/01

---

The remaining work items (including the last sprint (#8) and sprint-backlogs):

• get non-Oracle tests working after git2svn merge
• 362585 DBWS Oracle DDL Parser should resolve types from other schemas
• 325124 DBWS should support weakly-typed package REF CURSOR
• 362002 DBWSBuilder should generate CREATE and DROP DDL for 'shadow' JDBC types compatible with PL/SQL record + collection types (sprint backlog)
• 360235 Additional testing of INOUT parms required (sprint backlog)
• 359587 Oracle 'DATE' columns should map to java.sql.Timestamp (sprint backlog)
• 359130 Custom SQL with build SQL - JDBC metadata for 'DATE' wrong for Oracle (sprint backlog)
• 356637 handle OUT parameter(s) for Stored Functions (sprint backlog)
• 353417 DBWS should support non-associative PLSQL collections
• 339909 DBWS should support optional argument queries
• 339721 DBWS should support strongly-typed package REF CURSOR
• 329435 DBWS should handle %ROWTYPE mix of PL/SQL types and JDBC Advanced types