Here is an overview of the steps performed in order to convert an xpath string to an sql statement:
The org.apache.xpath.compiler.XPathParser class of the Xalan-Java API is used to tokenize and parse the xpath expression. For more information on the specification behind this process: http://www.w3.org/TR/xpath
The starting point of the parsing step is in the com.ibm.rpm.XPathToStack class. This class is also used to initialized custom functions such as isNull() and isNotNull() that are defined in the com.ibm.rpm.xpathparser.function package.
This section of the XPathToStack.parse(String xpath) will convert the xpath expression into a list of operator and a list of tokens:
XPathParser parser = new XPathParser(null, null);
Compiler compiler = getCompiler();
parser.initXPath(compiler, xpath, null);
The org.apache.xpath.compiler.OpCodes contains the list of the operation codes supported by xpath. We had to wrapped this class in the com.ibm.rpm.xpathparser.ReflectOpCodes to be compatible with multiple versions of the JDK which don't use the same constants. Same applies for org.apache.xpath.compiler.FunctionTables and com.ibm.rpm.xpathparser.ReflectFunctionTable
Classes of the com.ibm.rpm.xpathparser.steps are used to convert the lists created in the preceding step to an object model.
The base class of all the classes in this package is XPathStep. Here is description of the methods of this class:
public int build(int position) throws XPathException,
RPMException {
setLength(opMap.elementAt(++position) - 2);
notifyPreBuild(this);
position = super.build(++position);
notifyPostBuild(this);
return position;
}
The lists created in the preceding step will be passed as parameters of the StartXPath constructor. The build(int position) method starts the conversion of the operation codes list to the object model.
StartXPath step = new StartXPath(compiler.getOpMap(),
compiler.getTokenQueue());
step.build(0);
The returned object model will be a java.util.Stack of com.ibm.rpm.xpathparser.Table.
com.ibm.rpm.xpathparser.Table: represents a container.
/Object
will produce a Stack with one Table./Object1/Object2
will produce a Stack with 2 Tables.com.ibm.rpm.xpathparser.Predicate: represents the conditions as a structure. At the end of this step, the predicate is convert to an ArrayList and set to the Table.conditions field.
com.ibm.rpm.xpathparser.Condition: represents a condition that can be
preceded by a group operator (and, or)
com.ibm.rpm.xpathparser.FieldCondition: represents a condition on a
field that can have comparison operator (=, <)
or a
function (isnull, contains)
/Object[field = 'a']
will produce a Stack with one
Table. The conditions ArrayList will have one FieldCondition./Object1[Object2]
will produce a Stack with one
Table.The conditions ArrayList will have one ContainerCondition. The
ContainerCondition will have a Stack with one Table.For example, the Object Model for this xpath expression: /Task[name =
'sample task' or @containingProject[name = 'a project']] will be:
Step by step
creation of the Object Model
The class com.ibm.rpm.xpathparser.test.TestXPathSyntax is used to validate that xpath parser returns the expected object model. This test needs to be run after each modifications of classes from the com.ibm.rpm.xpathparser.steps package. These tests cannot be executed outside the rpm-data project.
The class com.ibm.rpm.client.junit.xpath.testcases.validation.TestXPathParserValidation is used to validate that xpath expression with invalid syntax return the right exception.
The validation and completion step is performed by the com.ibm.rpm.xpathparser.XPathUtil class.
For an xpath expression like /Project[name = 'sample
project']/task[name = 'a task'], the object model will be:
note: operation code are converted to string operators by the
FieldCondition.getStringOperator() method.
The role of the XpathUtil class is to validate these points:
XpathUtil is also used to fill up the object model with the missing values for parent (only for subObjects), fieldName and fieldType. If the objectName received is a field name instead of an object it will find the type of the field and put it in the Objectname field.
After calling the XpathUtil.validateAndPrepareTableStack() method with the preceding stack. The returned Stack will looks like this:
Here is another example:
/Task[name = 'sample task' or @containingProject[name = 'a project']]
Initial Object Model:
After calling XpathUtil.validateAndPrepareTableStack():
The class com.ibm.rpm.client.junit.xpath.testcases.TestXPathUtil tests that the validation and completion of the object model works properly.
The class com.ibm.rpm.client.junit.xpath.testcases.validation.TestXPathUtilValidation is used to validate that xpath expressions with invalid container names, field names, relationship or unsupported functions return the right exception.
The following section illustrates which method will be called to generate the Join Conditions depending on the xpath string and what will be the resulting SQL.
Note that these join conditions can instead be specified in the metadata for the appropriate container and field: Specifying Join Conditions in the Metadata.
a) The following xpath uses the getJoinCondition method only.
/Container2[field2 = 'value2']/Container1[field1 = 'value1']
Note: Container1 is handle by Manager1 that maps to Table1...
Condition | Manager, method and parameter | Join Condition |
---|---|---|
Manager1.getJoinCondition() parameter: manager = null (1) |
joinCondition1.tableName joinCondition1.condition (2) |
|
FieldCondition Operator null ,Property field1,Operation = ,Value 'value1' |
AbstractRPMObjectManager.buildFieldCondition() | joinCondition2.condition |
ContainerCondition Operator null |
Manager2.getJoinCondition() parameter: manager = Manager1 |
joinCondition3.condition (condition includes join condition between Table1 and Table2) |
FieldCondition Operator null ,Property field1,Operation = ,Value 'value1' |
AbstractRPMObjectManager.buildFieldCondition() | joinCondition4.condition |
The array of resulting Join Condition will be passed as a paramater to the AbstractRPMObjectmanager.buildQuery() method. The code in red is added automatically by the AbstractRPMObjectmanager.buildQuery() method.
Generated SQL: SELECT
Manager1.getAllFields() FROM
joinCondition1.tableName WHERE
joinCondition1.condition AND (
joinCondition2.condition AND
joinCondition3.condition AND (
joinCondition4.condition))
b) The following xpath uses the getJoinCondition method and the getSubTableJoinCondition method.
/Container1[field1='value1' or Container2[field2 = 'value2'] ]
Condition | Manager, method and parameter | Join Condition |
---|---|---|
Manager1.getJoinCondition() parameter: manager = null (1) |
joinCondition1.tableName joinCondition1.condition (2) |
|
FieldCondition Operator null ,Property field1,Operation = ,Value 'value1' |
AbstractRPMObjectManager.buildFieldCondition() | joinCondition2.condition |
ContainerCondition Operator OR |
Manager2.getSubTableJoinCondition() parameter: manager = Manager1 |
joinCondition3.condition (condition includes join condition between Table1 and Table2) |
FieldCondition Operator null ,Property field2,Operation = ,Value 'value2' |
AbstractRPMObjectManager.buildFieldCondition() | joinCondition4.condition |
SELECT Table1.getAllFields() FROM joinCondition1.tableName WHERE
joinCondition1.condition AND (
joinCondition2.condition OR
joinCondition3.condition AND (
joinCondition4.condition))
/GenericProject[name = 'Project A' or Task[name='Task B']]
Condition | manager/method | Join Condition |
WbsManager.getJoinCondition() parameter: manager = null |
joinCondition1.tableName = TMT_WBS joinCondition1.condition = TMT_WBS.ELEMENT_ID IS NOT NULL AND TMT_WBS.REC_STATUS != 'D' AND TMT_WBS.TYPE_ID IN(239,36,58,231,154,241,3,240,39,230,236,259,59,237,2,225,260,60,1,238,288,37) AND TMT_WBS.TYPE_ID IN(154,1,288) |
|
FieldCondition Operator null ,Property name ,Operation = ,Value 'Project A' |
AbstractRPMObjectManager.buildFieldCondition() | joinCondition2.condition = (TMT_WBS.ELEMENT_NAME = 'Project A') |
ContainerCondition Operator OR |
WbsManager.getSubTableJoinCondition() parameter: manager = WbsManager |
joinCondition3.condition = TMT_WBS.ELEMENT_ID IN (SELECT
TMT_WBS.PARENT_ID FROM TMT_WBS WHERE TMT_WBS.ELEMENT_ID IS NOT NULL
AND TMT_WBS.REC_STATUS != 'D' AND TMT_WBS.TYPE_ID
IN(239,36,58,231,154,241,3,240,39,230,236,259,59,237,2,225,260,60,1,238,288,37)
AND TMT_WBS.TYPE_ID IN(237,239,260,238,241,3,240,236) |
FieldCondition Operator null ,Property name ,Operation = ,Value 'Task B' |
AbstractRPMObjectManager.buildFieldCondition() | joinCondition4.condition =(TMT_WBS.ELEMENT_NAME = 'Task B') |
Generated SQL: SELECT ... FROM TMT_WBS WHERE TMT_WBS.ELEMENT_ID IS NOT NULL AND TMT_WBS.REC_STATUS != 'D' AND TMT_WBS.TYPE_ID IN(239,36,58,231,154,241,3,240,39,230,236,259,59,237,2,225,260,60,1,238,288,37) AND TMT_WBS.TYPE_ID IN(154,1,288) AND ( (TMT_WBS.ELEMENT_NAME = 'Project A') OR TMT_WBS.ELEMENT_ID IN (SELECT TMT_WBS.PARENT_ID FROM TMT_WBS WHERE TMT_WBS.ELEMENT_ID IS NOT NULL AND TMT_WBS.REC_STATUS != 'D' AND TMT_WBS.TYPE_ID IN(239,36,58,231,154,241,3,240,39,230,236,259,59,237,2,225,260,60,1,238,288,37) AND TMT_WBS.TYPE_ID IN(237,239,260,238,241,3,240,236) AND ( (TMT_WBS.ELEMENT_NAME = 'Task B')))) WITH UR
(1) The manager parameter is always null the first time the getJoinCondition method gets called. For the other calls, the prededing manager is passed as a parameter.
(2) Conditions generated by the managers from ContainerCondition are basic conditions like the output of the getFilter() method or a list of TYPE_ID that corresponds to the container type that is specified in the xpath expression. When generating the sql, a parenthesis is always opened after the AND following a condition generated from a manager to make sure that this condition is always applied when executing the sql else it could be excluded if the xpath contains a "OR" condition.
To help you implement or debug implementations of getJoinCondition
and getSubTableJoinCondition methods, you can look at the logs that display which method of which manager generates which part of the
SQL for which Condition and the final SQL.
DEBUG - ***** Generating sql statement for context: WBS *****
DEBUG - Calling WbsManager.getJoinCondition()(manager = null)
DEBUG - SQL: TMT_WBS.ELEMENT_ID IS NOT NULL AND TMT_WBS.REC_STATUS !=
'D' AND TMT_WBS.TYPE_ID
IN(239,36,58,231,154,241,3,240,39,230,236,259,59,237,2,225,260,60,1,238,288,37)
AND TMT_WBS.TYPE_ID IN(237,239,260,238,241,3,240,236)
DEBUG - XPath: FieldCondition: Operator null ,Property name ,Operation =
,Value 'sample task'
DEBUG - SQL: (TMT_WBS.ELEMENT_NAME = 'sample task')
DEBUG - XPath: ContainerCondition: Operator OR
DEBUG - Calling WbsManager.getSubTableJoinCondition()(manager =
WbsManager)
DEBUG - SQL: TMT_WBS.PROJECT_ID IN (SELECT TMT_WBS.ELEMENT_ID FROM
TMT_WBS WHERE TMT_WBS.ELEMENT_ID IS NOT NULL AND TMT_WBS.REC_STATUS !=
'D' AND TMT_WBS.TYPE_ID
IN(239,36,58,231,154,241,3,240,39,230,236,259,59,237,2,225,260,60,1,238,288,37)
AND TMT_WBS.TYPE_ID IN(154,1,288)
DEBUG - XPath: FieldCondition: Operator null ,Property name ,Operation =
,Value 'a project'
DEBUG - SQL: (TMT_WBS.ELEMENT_NAME = 'a project') DEBUG - ******
Executing generated sql statement ******
DEBUG - SELECT
TMT_WBS.RANK,TMT_WBS.WBS_CODE,...
The conversion of field names to column names is done in the manager classes. In this sample, the field named parentId will be mapped to the column named PARENT_ID of the table TMT_POOLS.
public static final String NAME_PARENT_ID =
"TMT_POOLS.PARENT_ID";
public static final String PROPERTY_PARENT_ID = "PARENTID";
static
{
FIELDPROPERTYMAP.put(PROPERTY_PARENT_ID, NAME_PARENT_ID);
}
The boolean values 'true' and 'false' are converted to database values in the managers. In this sample, if the xpath specifies a conditions where Resource.active='true', the generated sql will look for column ACTIVE of the RESOURCES table where the value is 1.
public static final String NAME_ACTIVE = "RESOURCES.ACTIVE";
public static final String PROPERTY_ACTIVE = "ACTIVE";
static{
FIELDPROPERTYMAP.put(PROPERTY_ACTIVE,SqlUtil.getBooleanValue(NAME_ACTIVE,
"1"));
}
The com.ibm.rpm.framework.util.EnumeratedTypeUtil class is used to keep the mapping of the Enumerated Type values to the database values. The mapping is initialized in the com.ibm.rpm.init.EnumeratedTypeInitializer.
Example:
addEnumeratedType(InputType.class, InputType.None, new Integer(0), true, null);
addEnumeratedType(InputType.class, InputType.Text, new Integer(1), true, null);
...
In class com.ibm.rpm.framework.util.EnumeratedTypeUtil
If the data type of the database column is Types.CHAR, the mapping of
the field will be done as in the basic mapping of
fields section.
public static final int TYPE_PUBLISHED = Types.CHAR;
public static final int TYPE_INPUT_TYPE = Types.SMALLINT;
public static final String NAME_INPUT_TYPE = "INPUT_TYPE";
public static final String PROPERTY_INPUT_TYPE = "INPUTTYPE";
static
{
FIELDPROPERTYMAP.put(PROPERTY_INPUT_TYPE,
SqlUtil.castAsChar(NAME_INPUT_TYPE,1));
}
Note: When using SqlUtil.castAsChar(NAME_INPUT_TYPE,1)), the second
parameter, numberOfChars, must be set to the maximum possible length of
the database value. Put 2 if the database value can be between 1 and
99.
The class com.ibm.rpm.client.junit.xpath.testcases.TestXPathWithEnumeratedTypes is used to test the mapping of enumerated types to database values. These test only asserts that a valid sql is generated. Tests on specific values must be performed in the regular unit-test classes.
Some values needs to be converted before we can compare them to the database values.For example, son fields that represents hours in the container are saved as minutes in the database.
public static final String NAME_MINUTES_PER_DAY =
"TMT_CALENDARS.MINUTES_PER_DAY";
public static final String PROPERTY_MINUTES_PER_DAY = "HOURSPERDAY";
static{
FIELDPROPERTYMAP.put(PROPERTY_MINUTES_PER_DAY,NAME_MINUTES_PER_DAY+"/60.0");
}
Some Date values are saved as Timestamp in the database. For this particular case, we needed to request a new custom database function from the Database Team to assure the compatibility with DB2 and Oracle. The custom dataabse functione gets called in SqlUtil.convertTimestampToDate method.
public static final String NAME_CONSTRAINT_DT =
"TMT_WBS.CONSTRAINT_DT";
public static final String PROPERTY_CONSTRAINT_DT = "CONSTRAINTDATE";
static
{
FIELDPROPERTYMAP.put(PROPERTY_CONSTRAINT_DT,
SqlUtil.convertTimestampToDate(NAME_CONSTRAINT_DT));
}
Only apostrophes cannot be passed as is in an xpath expression. They
needs to be replaced the html entity '
.
List of the special characters supported in xpath expressions:
~ | ` | ! | # | $ | % | ^ | & | * | ( | ) | _ | - | + | = | { | } | [ | ] | | | \ | : | ; | " | > | < | , | . | ? | / |