Design Considerations of QuerySpecification Objects with Logical and Comparison Operators and QueryLanguage Elements

Objective

The purpose of this specification is to try to satisfy vast majority of query requirements, without using sql statements

Terms

Operator QuerySpecification: Query specification objects which has 0 or many Logical and/or Comparison operator child elements.  See Usage Example 1), 2), and 3) 

QueryLanguage QuerySpecification: Query specification object which has exactly 1 QueryLanguage child element, and no other child elements.  See Usage Example 4), 5), and 6)

Operators and QueryLanguage Supported (all lowercase)

Logical Operator:  

Comparison Operator: 

QueryLanguage Type:

Usage Examples:

1) Default is "and" and "eq" (this is the QuerySpecification when the number of both Comparison and Logical operators are 0, which also makes the new definition backward-compatible)

<NutrientDefinitionQuerySpecification>
    <NutrNo>203</NutrNo>
    <NumDec>2</NumDec>
</NutrientDefinitionQuerySpecification> 

Equivalent Sql: NutrNo=203 and NumDec=2

2) "or" logical applied to All (*) fields

<NutrientDefinitionQuerySpecification>
    <Logical operator="or" fields="*"/>
    <Comparison operator="like" fields="Tagname,NutrDesc"/>
    <Comparison operator="in" fields="NutrNo"/>
    <Comparison operator="gt" fields="NumDec"/>
    <NutrNo>203,204</NutrNo>
    <Tagname>PROC%</Tagname>
    <NutrDesc>Prote%</NutrDesc>
    <NumDec>2</NumDec>
</NutrientDefinitionQuerySpecification>

Equivalent Sql: NutrNo in (203,204) or Tagname like "PROC%" or NutrDesc like "Prote%" or NumDec > 2

3) "or" logical applied to only specified fields

<NutrientDefinitionQuerySpecification>
    <Logical operator="or" fields="Tagname,NutrDesc"/>
    <Comparison operator="like" fields="Tagname,NutrDesc"/>
    <Comparison operator="in" fields="NutrNo"/>
    <NutrNo>203,204</NutrNo>
    <Tagname>PROC%</Tagname>
    <NutrDesc>Prote%</NutrDesc>
    <NumDec>2</NumDec>
</NutrientDefinitionQuerySpecification>

Equivalent Sql:: NutrNo in (203,204) and (Tagname like "PROC%" or NutrDesc like "Prote%") and NumDec > 2

4) Using hql to generated complex query or reports (do we have security risk here?)

<MyReportNutrientDefinitionQuerySpecification>
       <QueryLanguage type="hql" value="from MyReportNutrientDefinition where NutrNo='203'"/>
</MyReportNutrientDefinitionQuerySpecification>

5) Using named query to generated complex query or reports (hql)

     Mapping file and named query:

<!-- MyReportNutrientDefinition.hbm.xml -->
<hibernate-mapping  package="gov.usda.ars.moa.jmsobjects.nationalnutrientdatabase.v1_0">
    <class name="MyReportNutrientDefinition" table="NUTR_DEF">
        <id name="NutrNo" column="NUTR_NO"/>
        ...
    </class>

   <query name="findNutrientDefinitionByNutrNo">
        <![CDATA[from MyReportNutrientDefinition m where m.NutrNo = :NutrNo]]>
   </query>
</hibernate-mapping>

     Usage example:

<MyReportNutrientDefinitionQuerySpecification>   
   <QueryLanguage type="named" value="findNutrientDefinitionByNutrNo">
       <Parameter name="NutrNo" value="203"/>
   </QueryLanguage>
</MyReportNutrientDefinitionQuerySpecification>

6) Using named query to generated complex query or reports (native sql)

      Mapping file and named query:

<!-- MyReportNutrientDefinition.hbm.xml -->
<hibernate-mapping  package="gov.usda.ars.moa.jmsobjects.nationalnutrientdatabase.v1_0">
    <class name="MyReportNutrientDefinition" table="NUTR_DEF">
        <id name="NutrNo" column="NUTR_NO"/>
        ...
    </class>
 
    <sql-query name="findNutrientDefinitionNativeByNutrNo">
        <return alias="MyReportNutrientDefinition" class="MyReportNutrientDefinition"/>
        <![CDATA[select * from NUTR_DEF n where n.NUTR_NO = :NutrNo]]>
    </sql-query>
</hibernate-mapping>

     Usage example:

<MyReportNutrientDefinitionQuerySpecification>
   <QueryLanguage type="named" value="findNutrientDefinitionNativeByNutrNo">
       <Parameter name="NutrNo" value="203"/>
   </QueryLanguage>
</MyReportNutrientDefinitionQuerySpecification>

7) Using querylanguages.xml configuration to hide QueryLanguage from QuerySpecification (which is exposed to ESB service user)

A) "named" type (must be of hibernate managed MOA)

      Mapping file and named query:

<!-- PatientProfile.hbm.xml -->
<hibernate-mapping package="edu.emory.sph.moa.jmsobjects.webease.v1_0">
 <class name="PatientProfile" table="PATIENT_PROFILE">
   <id type="OpenEaiLong" name="PatientId" column="PATIENT_ID">
     <generator class="assigned"/>
   </id>
...
 </class>
 <query name="findPatientProfileByPrincipal">
    <![CDATA[from PatientProfile p inner join fetch p.UserAuthorization u   where u.Principal = :Principal]]>
 </query>
</hibernate-mapping

 querylanguages.xml (in Jars/hibernate directory):

<querylanguages>
   <querylanguage>
     <PatientProfileQuerySpecification>
 	<QueryLanguage type="named" value="findPatientProfileByPrincipal">
            <Parameter name="Principal"/> 
 	</QueryLanguage>
     </PatientProfileQuerySpecification>
   </querylanguage>
...
</querylanguages>

 Usage example (same as regular QuerySpecification):

<PatientProfileQuerySpecification>
	<Principal>swheat</Principal>
</PatientProfileQuerySpecification>

B) "scalars" type (can be non hibernate managed MOA)

 querylanguages.xml (in Jars/hibernate directory):

<querylanguages>
   <querylanguage>
   <HelloHibernateGwCountQuerySpecification>
     <QueryLanguage type="scalars" name="findHelloCountById">
      <SqlString value="select count(*) as value from hello_hibernate_gw where id = :Id">
       <Return name="Value" type="string"/>
      </SqlString>
      <Parameter name="Id"/>
     </QueryLanguage>
   </querylanguage>
...
</querylanguages>

 Usage example (same as regular QuerySpecification):

<HelloHibernateGwCountQuerySpecification>
	<Id>1</Id>
</HelloHibernateGwCountQuerySpecification>

8) Special case - data attribute of Comparison element.  

All non-operator elements should be field names, except when data attribute exists in Comparison element.

A) When fields type is String, data attribute is mostly not needed

The above can be written as the following

B) When fields type is Date or Datetime, data attribute is needed

Because Date or Datetime* (in dtd) is not supported, the following is illegal

Here comes the data attribute:

StartDate and EndDate are two values for the LastModifiedDate field

 Logical Operator Rules

There are (at most) two level Logicals - 1st level and second level.  These two level has to be opposite to each other -  If the 1st leve is or, then 2nd level must be and.  If the 1st level is and, the 2nd level must be or.

1) 1st level Logical is determined by *

-if there is or *, top level is or

-else (there is no * or there is and *) top level is and

2) second leve is determined by (or, and) "fields group"

3) duplicate Logical operator not allowed, but duplicate fields are ok:

illegal:

legal:

4) empty queryElement is ignored  (should we implement isNull or isNotNull???)

Implementations

The implementations of above functionality involvles using the following openeai packages

• org.openeai.implementations.moagen (openeai_moagen.jar) for moa generation
• org.openeai.moa.objects.resources.v1_0 (part of openeai.jar) for moa and object definition
• org.openii.openeai.toolkit.rdbms.persistence.hibernate (openii_toolkit.jar) for hibernate O/R mapping

Known Issues or Limitations

-It needs the moa generated by the latest openeai_moagen.jar, where it will have the signature setXXXDatetime(org.openeai.moa.objects.resources.Datetime).

TO DO

-Empty query element treated as empty string.  Add isNull and isNotNull comparison operator.

-More comparison operators? such as Restrictions.eqProperth(propertyA,propertyB) and other PropertyExpressions (geProperty, gtProperty, leProperty, ltProperty, neProperty)

-sql fragment (sqlRestriction)