Red Hat Training

A Red Hat training course is available for Red Hat JBoss Data Virtualization

9.2. Data Definition Language (DDL) File Sequencer

The Data Definition Language (DDL) File Sequencer is capable of parsing the more important DDL statements from SQL-92, Oracle, Derby, and PostgreSQL, and constructing a graph structure containing a structured representation of these statements. The resulting graph structure is largely the same for all dialects, though some dialects have non-standard additions to their grammar, and thus require dialect-specific additions to the graph structure.
The sequencer is designed to behave as intelligently as possible with as little configuration. Thus, the sequencer automatically determines the dialect used by a given DDL stream. This can be tricky, of course, since most dialects are very similar and the distinguishing features of a dialect may only be apparent in some of the statements.
To get around this, the sequencer uses a "best fit" algorithm: run the DDL stream through the parser for each of the dialects, and determine which parser was able to successfully read the greatest number of statements and tokens.

Note

It is possible to define which DDL dialects (or grammars) should be considered during sequencing using the "grammars" property in the sequencer configuration. Set the values of this property to the names of the grammars (e.g., "oracle", "postgres", "sql92", or "derby"), specified in the order they should be used. To use a custom DDL parser (not provided by the hierarchical database) provide the fully-qualified class name of the implementation class. If this custom parser implementation is not found on the default classpath, additional classpath URLs can be specified using the "classpath" property of the sequencer.
One useful feature of this sequencer is that, although only a subset of the (more common) DDL statements are supported, the sequencer is still extremely functional since it adds all statements into the output graph (the statement text and the position in the DDL file). Thus, if a DDL file contains statements the sequencer understands and statements the sequencer does not understand, the graph will still contain all statements, where those statements understood by the sequencer will have full detail. Since the underlying parsers are able to operate upon a single statement, it is possible to go back later (after the parsers have been enhanced to support additional DDL statements) and re-parse only those incomplete statements in the graph.
At this time, the sequencer supports SQL-92 standard DDL as well as dialects from Oracle, Derby, and PostgreSQL. It supports:
  • Detailed parsing of CREATE SCHEMA, CREATE TABLE and ALTER TABLE.
  • Partial parsing of DROP statements
  • General parsing of remaining schema definition statements (i.e. CREATE VIEW, CREATE DOMAIN, etc. Note that the sequencer does not perform detailed parsing of SQL (i.e. SELECT, INSERT, UPDATE, etc....) statements.

9.2.1. DDL File Sequencer Example

Below is an example DDL schema definition statement containing table and view definition statements. 
CREATE SCHEMA hollywood
CREATE TABLE films (title varchar(255), release date, producerName varchar(255))
CREATE VIEW winners AS SELECT title, release FROM films WHERE producerName IS NOT NULL;
The resulting graph structure contains the raw statement expression, pertinent table, column and key reference information and position of the statement in the text stream (e.g., line number, column number and character index) so the statement can be tied back to the original DDL: 
<nt:unstructured jcr:name="statements"
                 jcr:mixinTypes = "mode:derived"
                 ddl:parserId="POSTGRES">
   <nt:unstructured jcr:name="hollywood" jcr:mixinTypes="ddl:createSchemaStatement"
                    ddl:startLineNumber="1"
                    ddl:startColumnNumber="1"
                    ddl:expression="CREATE SCHEMA hollywood"
                    ddl:startCharIndex="0">
   <nt:unstructured jcr:name="films" jcr:mixinTypes="ddl:createTableStatement"
                    ddl:startLineNumber="2"
                    ddl:startColumnNumber="5"
                    ddl:expression="CREATE TABLE films (title varchar(255), release date, producerName varchar(255))"
                    ddl:startCharIndex="28"/>
   <nt:unstructured jcr:name="title" jcr:mixinTypes="ddl:columnDefinition"
                    ddl:datatypeName="VARCHAR"
                    ddl:datatypeLength="255"/>
   <nt:unstructured jcr:name="release" jcr:mixinTypes="ddl:columnDefinition"
                    ddl:datatypeName="DATE"/>
   <nt:unstructured jcr:name="producerName" jcr:mixinTypes="ddl:columnDefinition"
                    ddl:datatypeName="VARCHAR"
                    ddl:datatypeLength="255"/>
   <nt:unstructured jcr:name="winners" jcr:mixinTypes="ddl:createViewStatement"
                    ddl:startLineNumber="3"
                    ddl:startColumnNumber="5"
                    ddl:expression="CREATE VIEW winners AS SELECT title, release FROM films WHERE producerName IS NOT NULL;"
                    ddl:queryExpression="SELECT title, release FROM films WHERE producerName IS NOT NULL"
                    ddl:startCharIndex="113"/>
</nt:unstructured>
Note that all nodes are of type nt:unstructured while the type of statement is identified using mixins. Also, each of the nodes representing a statement contain: a ddl:expression property with the exact statement as it appeared in the original DDL stream; a ddl:startLineNumber and ddl:startColumnNumber property defining the position in the original DDL stream of the first character in the expression; and a ddl:startCharIndex property that defines the integral index of the first character in the expression as found in the DDL stream. All of these properties make sure the statement can be traced back to its location in the original DDL.

9.2.2. Using the DDL File Sequencer

To use the DDL File Sequencer, include the modeshape-sequencer-ddl JAR in your application and configure the repository to use this sequencer using something similar to: 
{
    "name" : "DdlSequencer Test Repository",
    "sequencing" : {
        "removeDerivedContentWithOriginal" : true,
        "sequencers" : [
            {
                "description" : "Ddl sequencer test",
                "classname" : "DdlSequencer",
                "pathExpressions" : [ "default://(*.ddl)/jcr:content[@jcr:data] => default:/ddl" ]
            }
        ]
    }
}
This will use all of the built-in grammars (e.g., "sql92", "oracle", "postgres", and "derby"). To specify a different order or subset of the grammars, use the grammars parameter. Here's an example that uses the Standard grammar followed by the PostgreSQL grammar: 
{
    "name" : "DdlSequencer Test Repository",
    "sequencing" : {
        "removeDerivedContentWithOriginal" : true,
        "sequencers" : [
            {
                "description" : "Ddl sequencer test",
                "classname" : "DdlSequencer",
                "grammars" : ["sql92", "postgres"],
                "pathExpressions" : [ "default://(*.ddl)/jcr:content[@jcr:data] => default:/ddl" ]
            }
        ]
    }
}
To use a custom implementation, use the fully-qualified name of the implementation class (which must have a no-arg constructor) as the name of the grammar: 
{
    "name" : "DdlSequencer Test Repository",
    "sequencing" : {
        "removeDerivedContentWithOriginal" : true,
        "sequencers" : {
            "DDL Sequencer" : {
                "description" : "Ddl sequencer test",
                "classname" : "DdlSequencer",
                "grammars" : ["sql92", "postgres", "org.example.ddl.MyCustomDdlParser"],
                "pathExpressions" : [ "default://(*.ddl)/jcr:content[@jcr:data] => default:/ddl" ]
            }
        }
    }
}