/*
* Copyright (c) 2006-2013 Massachusetts General Hospital
* All rights reserved. This program and the accompanying materials
* are made available under the terms of the i2b2 Software License v1.0
* which accompanies this distribution.
*
* Contributors:
* Christopher Herrick
*/
package edu.harvard.i2b2.crc.dao.setfinder.querybuilder.temporal;
import java.math.BigInteger;
import java.util.ArrayList;
import java.util.EnumSet;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;
import javax.xml.bind.JAXBElement;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import edu.harvard.i2b2.common.exception.I2B2DAOException;
import edu.harvard.i2b2.common.exception.I2B2Exception;
import edu.harvard.i2b2.common.util.jaxb.JAXBUtilException;
import edu.harvard.i2b2.crc.dao.DAOFactoryHelper;
import edu.harvard.i2b2.crc.dao.setfinder.querybuilder.DxTableHandler;
import edu.harvard.i2b2.crc.dao.setfinder.querybuilder.QueryDefinitionUnWrapUtil;
import edu.harvard.i2b2.crc.dao.setfinder.querybuilder.QueryTimingHandler;
import edu.harvard.i2b2.crc.dao.setfinder.querybuilder.TempTableNameMap;
import edu.harvard.i2b2.crc.dao.setfinder.querybuilder.temporal.TemporalQueryOptions.InvertedConstraintStrategy;
import edu.harvard.i2b2.crc.dao.setfinder.querybuilder.temporal.TemporalQueryOptions.QueryConstraintStrategy;
import edu.harvard.i2b2.crc.datavo.CRCJAXBUtil;
import edu.harvard.i2b2.crc.datavo.db.DataSourceLookup;
import edu.harvard.i2b2.crc.datavo.i2b2message.RequestMessageType;
import edu.harvard.i2b2.crc.datavo.i2b2message.SecurityType;
import edu.harvard.i2b2.crc.datavo.setfinder.query.QueryConstraintType;
import edu.harvard.i2b2.crc.datavo.setfinder.query.QueryDefinitionType;
import edu.harvard.i2b2.crc.util.PMServiceAccountUtil;
/**
* Temporal Query Object
*
* <P> General query object that reads in CRC query requests and converts it into sql that can be run against the database
* This class and the subsequent classes it depends on duplicate much of functionality already found in querybuilder package, but reorganize the
* code in a way that makes it easier to identify sql syntax issues and find the corresponding code snippet that generates it. In addition,
* temporal comparison functionality was added to this set of classes to enable temporal based queries to run in the CRC.
*
* @author Christopher Herrick
*
*/
public class TemporalQuery {
protected final Log log = LogFactory.getLog(getClass());
private static QueryTimingHandler timingHandler = null;
private TempTableNameMap tempTableNameMap = null;
private String queryTiming = "ANY";
private DataSourceLookup dsLookup = null;
private List<TemporalSubQuery> subQueryList = null;
private HashMap<String, Integer> subQueryMap = null;
private QueryDefinitionType queryDef = null;
private String queryId = null;
private String projectId = null;
private SecurityType securityType = null;
private SecurityType userSecurityType = null;
private Map projectParamMap = null;
private boolean allowLargeTextValueConstrainFlag = false;
private TemporalQueryConstraintMapping constraintMapping = null;
private int maxPanelIndex = 0;
private int processingLevel = 1;
private StringBuffer ignoredItemMessageBuffer = new StringBuffer();
private TemporalQueryOptions options = null;
private String lastSubQueryId = null;
private List<String> preProcessingSql = null;
private List<String> postProcessingSql = null;
/**
* Constructor
*
* @param dataSourceLookup data source lookup object that contains information about the database and connection used to query against
* @param projectParameterMap map object that contains project parameters used to configure CRC and query logic
* @param queryXml string that contains the query definition xml object received from the query request
* @param allowLargeTextValueConstrainFlag boolean flag that, when set to true, allows text searching against text fields in the database
* @throws JAXBUtilException exception thrown when errors arise from converting string to xml and vice versa
* @throws I2B2Exception exception thrown when i2b2 specific error arises
*/
public TemporalQuery(DataSourceLookup dataSourceLookup, Map projectParameterMap, String queryXml, boolean allowLargeTextValueConstrainFlag) throws JAXBUtilException, I2B2Exception{
this(dataSourceLookup, projectParameterMap, queryXml, allowLargeTextValueConstrainFlag, 1);
}
/**
* Constructor
*
* @param dataSourceLookup data source lookup object that contains information about the database and connection used to query against
* @param projectParameterMap map object that contains project parameters used to configure CRC and query logic
* @param queryXml string that contains the query definition xml object received from the query request
* @param allowLargeTextValueConstrainFlag boolean flag that, when set to true, allows text searching against text fields in the database
* @throws JAXBUtilException exception thrown when errors arise from converting string to xml and vice versa
* @throws I2B2Exception exception thrown when i2b2 specific error arises
*/
public TemporalQuery(DataSourceLookup dataSourceLookup, Map projectParameterMap, String queryXml, boolean allowLargeTextValueConstrainFlag, int processingLevel) throws JAXBUtilException, I2B2Exception{
dsLookup = dataSourceLookup;
projectParamMap = projectParameterMap;
this.allowLargeTextValueConstrainFlag = allowLargeTextValueConstrainFlag;
this.processingLevel = processingLevel;
options = new TemporalQueryOptions();
if (getServerType().equalsIgnoreCase(DAOFactoryHelper.SQLSERVER) ||
getServerType().equalsIgnoreCase(DAOFactoryHelper.POSTGRESQL)){
options.setQueryConstraintLogic(QueryConstraintStrategy.WITH_STATEMENT);
}
else if (getServerType().equalsIgnoreCase(DAOFactoryHelper.ORACLE)){
options.setInvertedConstraintLogic(InvertedConstraintStrategy.MINUS_CLAUSE);
}
this.preProcessingSql = new ArrayList<String>();
this.postProcessingSql = new ArrayList<String>();
parseQueryDefinitionXml(queryXml);
}
public TemporalQuery(DataSourceLookup dataSourceLookup, Map projectParameterMap, QueryDefinitionType queryDefinition, boolean allowLargeTextValueConstrainFlag,
String queryProjectId, SecurityType userSecurityType, SecurityType querySecurityType) throws JAXBUtilException, I2B2Exception{
this(dataSourceLookup, projectParameterMap, queryDefinition, allowLargeTextValueConstrainFlag, 1, queryProjectId, userSecurityType, querySecurityType);
}
public TemporalQuery(DataSourceLookup dataSourceLookup, Map projectParameterMap, QueryDefinitionType queryDefinition, boolean allowLargeTextValueConstrainFlag, int processingLevel,
String queryProjectId, SecurityType userSecurityType, SecurityType querySecurityType) throws JAXBUtilException, I2B2Exception{
dsLookup = dataSourceLookup;
projectParamMap = projectParameterMap;
this.allowLargeTextValueConstrainFlag = allowLargeTextValueConstrainFlag;
this.processingLevel = processingLevel;
options = new TemporalQueryOptions();
if (getServerType().equalsIgnoreCase(DAOFactoryHelper.SQLSERVER) ||
getServerType().equalsIgnoreCase(DAOFactoryHelper.POSTGRESQL)){
//no default options at this time
options.setQueryConstraintLogic(QueryConstraintStrategy.WITH_STATEMENT);
}
else if (getServerType().equalsIgnoreCase(DAOFactoryHelper.ORACLE)){
options.setInvertedConstraintLogic(InvertedConstraintStrategy.MINUS_CLAUSE);
}
this.queryDef = queryDefinition;
this.projectId = queryProjectId;
this.userSecurityType = userSecurityType;
this.securityType = querySecurityType;
this.preProcessingSql = new ArrayList<String>();
this.postProcessingSql = new ArrayList<String>();
parseQuery();
}
public void parseQueryDefinitionXml(String queryXml) throws JAXBUtilException, I2B2Exception{
if (queryXml==null)
return;
//start by converting string object to query definition object
QueryDefinitionUnWrapUtil queryDefUnWrapHelper = new QueryDefinitionUnWrapUtil();
queryDef = queryDefUnWrapHelper
.getQueryDefinitionType(queryXml);
//get query timing out - this will be used as the default timing for all groups
//and panels. Panel timing values will override this value
queryTiming = queryDef.getQueryTiming();
//extract out project and security information. this will be reused when validating
//panel items with ontology cell
JAXBElement responseJaxb = CRCJAXBUtil.getJAXBUtil()
.unMashallFromString(queryXml);
RequestMessageType request = (RequestMessageType) responseJaxb
.getValue();
projectId = request.getMessageHeader().getProjectId();
userSecurityType = request.getMessageHeader()
.getSecurity();
securityType = PMServiceAccountUtil
.getServiceSecurityType(userSecurityType.getDomain());
parseQuery();
}
/**
* Parse Query
*
* <P> Parses out string representation of query xml object into building blocks that will be used to generate sql statement.
* In particular, this method establishes the processing order of groups, panels, and items when building the query
*
* @param queryXml string that contains the query definition xml object received from the query request
* @throws JAXBUtilException exception thrown when errors arise from converting string to xml and vice versa
* @throws I2B2Exception exception thrown when i2b2 specific error arises
*/
public void parseQuery() throws I2B2Exception {
//get query timing out - this will be used as the default timing for all groups
//and panels. Panel timing values will override this value
queryTiming = queryDef.getQueryTiming();
//first step, get the constraints parsed out by groups they reference
//also parse through the columns that need to be returned by each panel group
constraintMapping = new TemporalQueryConstraintMapping(queryDef);
HashMap<String, TemporalSubQuery> subQuerySet = new HashMap<String, TemporalSubQuery>();
//second step, treat panel object on the query level as panel group with no temporal component
if (queryDef.getPanel()!=null&&queryDef.getPanel().size()>0){
TemporalSubQuery mainQuery = new TemporalSubQuery(this, queryDef.getPanel());
//check to see if the main query is an "everyone" query
boolean getEveryoneQuery = false;
if (mainQuery!=null&&mainQuery.getPanelCount()==1){
TemporalPanel mainPanel = mainQuery.getPanelList().get(0);
if (mainPanel!=null &&
mainPanel.isPanelInverted() &&
(mainPanel.getItemList()==null||mainPanel.getItemList().size()==0)){
getEveryoneQuery = true;
}
}
//if there are no subqueries or the main query is not an "everyone" query, include
//the main query as a special subquery
if (queryDef.getSubquery()==null ||
queryDef.getSubquery().size()==0 ||
!getEveryoneQuery){
subQuerySet.put("**default**", mainQuery);
}
}
//third step, check for query objects and create corresponding temporal query panel groups
if (queryDef.getSubquery()!=null&&queryDef.getSubquery().size()>0){
for (QueryDefinitionType query : queryDef.getSubquery()){
HashMap<String, List<QueryConstraintType>> constraints = constraintMapping.getConstraintsForQuery(query.getQueryId());
EnumSet<TemporalSubQuery.TemporalQueryReturnColumns> returnColumns = constraintMapping.getReturnColumnsForQuery(query.getQueryId());
TemporalSubQuery subQuery = new TemporalSubQuery(this, query, constraints, returnColumns);
subQuerySet.put(query.getQueryId(), subQuery);
}
}
//last step, group set object established the order of the groups. now copy objects
//into straight list for easier processing
subQueryList = new ArrayList<TemporalSubQuery>();
subQueryMap = new HashMap<String, Integer>();
int index = 0;
for(String eventId : constraintMapping.getOrderedQueryList()){
TemporalSubQuery subQuery = subQuerySet.get(eventId);
if (subQuery!=null){
subQueryList.add(subQuery);
subQueryMap.put(eventId, index);
index++;
subQuerySet.remove(eventId);
}
}
if (subQuerySet.size()>0){
for (TemporalSubQuery grp : subQuerySet.values()){
subQueryList.add(grp);
subQueryMap.put(grp.getQueryId(), index);
index++;
}
}
}
protected QueryDefinitionType searchForSubQuery(String subQueryId){
if (subQueryId==null||subQueryId.trim().length()==0)
return null;
String strippedId = "";
if (subQueryId.startsWith("masterid:")){
strippedId = subQueryId.replace("masterid:", "");
}
if (this.queryDef!=null&&this.queryDef.getSubquery()!=null){
for (QueryDefinitionType query : queryDef.getSubquery()){
if (query.getQueryId()!=null&&query.getQueryId().trim().length()>0&&query.getQueryId().equalsIgnoreCase(subQueryId)){
return query;
}
else if (strippedId!=null&&strippedId.trim().length()>0&&strippedId.equalsIgnoreCase(subQueryId))
return query;
}
}
return null;
}
/**
* Build Sql
*
* <P> Main call that converts the query object into a sql statement that can be run against the database
*
* @return String sql statement generated from query object
* @throws I2B2DAOException exception generated when encountering an i2b2 specific database error
*/
public String buildSql() throws I2B2DAOException {
StringBuffer querySqlBuffer = new StringBuffer();
// iterator through the ordered panel groups, generating a sql
// statement for each
for (TemporalSubQuery subQuery : subQueryList){
if (subQuery!=null){
querySqlBuffer.append(subQuery.buildSql());
lastSubQueryId = subQuery.getSubQueryId();
maxPanelIndex = subQuery.getEndPanelIndex();
}
}
// finally, add in insert into dx table that will get passed back to
// calling class
String dxTempTableSql = "";
if (subQueryList.size()>1){
dxTempTableSql = buildDxInsertSqlFromMaster(returnEncounterNum(), returnInstanceNum(), lastSubQueryId, 1);
}
else {
dxTempTableSql = buildDxInsertSqlFromTemp(returnEncounterNum(), returnInstanceNum(), maxPanelIndex);
}
querySqlBuffer.append(dxTempTableSql);
StringBuffer sqlBuffer = new StringBuffer();
if (this.preProcessingSql!=null&&this.preProcessingSql.size()>0){
for(String sql : this.preProcessingSql){
sqlBuffer.append(sql);
sqlBuffer.append(getSqlDelimiter());
}
}
sqlBuffer.append(querySqlBuffer.toString());
if (this.postProcessingSql!=null&&this.postProcessingSql.size()>0){
for(String sql : this.postProcessingSql){
sqlBuffer.append(getSqlDelimiter());
sqlBuffer.append(sql);
}
}
return sqlBuffer.toString();
}
public String buildDxInsertSqlFromTemp(boolean encounterNumFlag, boolean instanceNumFlag, int maxPanelNum) {
StringBuilder dxInsertSql = new StringBuilder();
String selectEncounterNum = " ", selectPatientNum = " patient_num ";
if (encounterNumFlag) {
selectEncounterNum = " , encounter_num ";
}
dxInsertSql.append(" insert into " + this.getDxTempTableName() + " ( " + selectPatientNum
+ selectEncounterNum + " ) select * from ("
+ " select distinct " + selectPatientNum
+ selectEncounterNum + "from " + this.getTempTableName()
+ " where panel_count = " + maxPanelNum
+ " ) q");
return dxInsertSql.toString();
}
public String buildDxInsertSqlFromMaster(boolean encounterNumFlag, boolean instanceNumFlag, String subQueryId, int levelNo) {
StringBuilder dxInsertSql = new StringBuilder();
String selectEncounterNum = " ", selectPatientNum = " patient_num ";
if (encounterNumFlag) {
selectEncounterNum = " , encounter_num ";
}
if (useSqlServerTempTables()){
dxInsertSql.append(" insert into " + this.getDxTempTableName() + " ( " + selectPatientNum
+ selectEncounterNum + " ) select * from ("
+ " select distinct " + selectPatientNum
+ selectEncounterNum + "from #m" + subQueryId
+ " where level_no = " + String.valueOf(levelNo));
}
else {
dxInsertSql.append(" insert into " + this.getDxTempTableName() + " ( " + selectPatientNum
+ selectEncounterNum + " ) select * from ("
+ " select distinct " + selectPatientNum
+ selectEncounterNum + "from " + this.getMasterTempTableName()
+ " where level_no = " + String.valueOf(levelNo)
+ " and master_id = '" + subQueryId + "'");
}
dxInsertSql.append(" ) q");
return dxInsertSql.toString();
}
/**
* Get Project Id
*
* @return string that represents the project id for the query that was passed in
*/
protected String getProjectId() {
return projectId;
}
/**
* Get Security Type
*
* @return security type for the query that was passed in
*/
protected SecurityType getSecurityType() {
return securityType;
}
/**
* Get Security Type
*
* @return security type for the query that was passed in
*/
protected SecurityType getRequestorSecurityType() {
return userSecurityType;
}
/**
* Get Panel Group Index
*
* @param grp reference to temporal panel group to test
* @return int that corresponds to the index of this panel group occurs the group list
*/
public int getSubQueryIndex(TemporalSubQuery grp){
return subQueryList.indexOf(grp);
}
public int getSubQueryIndex(String subQueryId){
return subQueryMap.get(subQueryId);
}
/**
* Is First Group
*
* Test to check if passed in temporal panel group is the first panel group for this query
*
* @param subQuery reference to a temporal panel group to test
* @return true or false depending on if passed in panel group is the first group for this query
*/
public boolean isFirstSubQuery(TemporalSubQuery subQuery){
return (getSubQueryIndex(subQuery)==0);
}
/**
* Is Last Group
*
* Test to check if passed in temporal panel group is the last panel group for this query
*
* @param subQuery reference to a temporal panel group to test
* @return true or false depending on if passed in panel group is the last group for this query
*/
public boolean isLastSubQuery(TemporalSubQuery subQuery){
return (getSubQueryIndex(subQuery)==(subQueryList.size()-1));
}
/**
* Get Query Timing
*
* Get the default timing passed in by the query object
*
* @return string containing the query timing constant
*/
public String getQueryTiming() {
return queryTiming;
}
/**
* Get Data Source Lookup
*
* Returns the data source lookup object passed in in the constructor
*
* @return data source lookup object passed in the constructor
*/
protected DataSourceLookup getDataSourceLookup() {
return dsLookup;
}
/**
* Get Database Schema
*
* Returns the name of the schema used to reference the correct table names in the sql syntax
*
* @return string that contains the name of the schema this query should run under
*/
public String getDatabaseSchema() {
String dbSchemaName = dsLookup.getFullSchema();
if (dbSchemaName != null && dbSchemaName.endsWith(".")) {
return dbSchemaName.trim();
}
else if (dbSchemaName != null) {
return dbSchemaName.trim() + ".";
}
return dbSchemaName;
}
/**
* Get Server Type
*
* Returns the type of database server CRC is running against
*
* @return string containing the type of database CRC is running against (Sql Server or Oracle)
*/
protected String getServerType(){
return dsLookup.getServerType();
}
/**
* Get Project Parameter Map
*
* Returns the map that was passed in in the constructor that contains the parameters used for this project
*
* @return map object that contains the project parameters
*/
protected Map getProjectParameterMap(){
return this.projectParamMap;
}
/**
* Get Timing Handler
*
* @return query timing handler object that tests timing text from query for various states
*/
protected QueryTimingHandler getTimingHandler(){
if (timingHandler==null)
timingHandler = new QueryTimingHandler();
return timingHandler;
}
/**
* Get Temp Table Name
*
* Return the default name of the temproary table in the database. The temporary table is used to store the results of individual panel items and the joins
* of panel to panel items
*
* @return string containing the name of the default temporary table name in the database
*/
protected String getTempTableName(){
if (this.tempTableNameMap==null)
this.tempTableNameMap = new TempTableNameMap(this.getServerType());
String tableName = "";
if (getServerType().equalsIgnoreCase(DAOFactoryHelper.ORACLE)){
tableName = getDatabaseSchema();
}
tableName += tempTableNameMap.getTempTableName();
return tableName;
}
/**
* Get DX Temp Table Name
*
* Return the default name of the dx temporary table in the database. The dx table is used to return the final results of the query from the database to the
* CRC cell
*
* @return string containing the default name of the dx return table in database
*/
protected String getDxTempTableName(){
if (this.tempTableNameMap==null)
this.tempTableNameMap = new TempTableNameMap(this.getServerType());
String tableName = "";
if (getServerType().equalsIgnoreCase(DAOFactoryHelper.ORACLE)){
tableName = getDatabaseSchema();
}
tableName += tempTableNameMap.getTempDxTableName();
return tableName;
}
/**
* Get Master Temp Table Name
*
* Return default name of the master table in the database. The master table is used to temporarily store results from embedded query items
* and panel group constraints
*
* @return string containing the default name used for the "master" table in the database
*/
protected String getMasterTempTableName(){
if (this.tempTableNameMap==null)
this.tempTableNameMap = new TempTableNameMap(this.getServerType());
String tableName = "";
if (getServerType().equalsIgnoreCase(DAOFactoryHelper.ORACLE)){
tableName = getDatabaseSchema();
}
tableName += tempTableNameMap.getTempMasterTable();
return tableName;
}
/**
* Allow Large Text Value Constrian Flag
*
* @return true if text querying against large text fields is allowed, else false
*/
protected boolean allowLargeTextValueConstrainFlag(){
return this.allowLargeTextValueConstrainFlag;
}
/**
* Return Encounter Num
*
* @return true if query needs to return encounter information in final results, else false
*/
protected boolean returnEncounterNum(){
if (getTimingHandler().isSameVisit(queryTiming))
return true;
else
return false;
}
/**
* Return Instance Num
*
* @return true if query needs to return instance information is the final results, else false
*/
protected boolean returnInstanceNum(){
if (getTimingHandler().isSameInstanceNum(queryTiming))
return true;
else
return false;
}
/**
* Get Panel Group Count
*
* @return int - the number of panel groups contained in this query
*/
protected int getSubQueryCount(){
return subQueryList.size();
}
/**
* Generate Unique Id
*
* Returns a string with a guaranteed unique value. This method is used specifically to uniquely identify queries and
* panel groups within the context of the query
*
* @return string with a guaranteed unique value
*/
protected String generateUniqueId(){
UUID uniqueKey = UUID.randomUUID();
String hexNum = uniqueKey.toString().replace("-", "");
BigInteger big = new BigInteger(hexNum, 16);
return big.toString(36);
}
/**
* Get Query Id
*
* @return string containing the unique id of the query that is being run
*/
protected String getQueryId(){
if (queryId==null)
queryId = generateUniqueId();
return queryId;
}
public int getMaxPanelIndex(){
return this.maxPanelIndex;
}
/**
* @return the processingLevel
*/
public int getProcessingLevel() {
return processingLevel;
}
public TemporalQueryOptions getQueryOptions(){
return options;
}
/**
* @return the ignoredItemMessageBuffer
*/
public StringBuffer getIgnoredItemMessageBuffer() {
return ignoredItemMessageBuffer;
}
public void addIgnoredMessage(String errorMessage) {
this.ignoredItemMessageBuffer.append(errorMessage + "\n");
}
/**
* @return the lastSubQueryId
*/
protected String getLastProcessedSubQueryId() {
return lastSubQueryId;
}
protected void addPreProcessingSql(String sql){
if (sql!=null&&sql.trim().length()>0)
this.preProcessingSql.add(sql);
}
protected void addPostProcessingSql(String sql){
if (sql!=null&&sql.trim().length()>0)
this.postProcessingSql.add(sql);
}
public String getSqlDelimiter() {
return "\n<*>\n";
}
/**
* Build Temp Table Check Drop
*
* Create a Sql Server specific statement to check for the existence of a temporary table and,
* if the table exists, drop it
*
* @param tempTableName String name of the temporary table
* @return String sql statement that checks for temporary table and drops it
*/
protected String buildTempTableCheckDrop(String tempTableName) {
if (tempTableName == null)
return "";
else if (!tempTableName.startsWith("#"))
tempTableName = "#" + tempTableName;
return "if (object_id('tempdb.." + tempTableName + "') is not null) \n"
+ "begin \n" + "drop table " + tempTableName + " \n" + "end";
}
protected boolean useSqlServerTempTables(){
return ((this.getQueryOptions().getQueryConstraintLogic()==QueryConstraintStrategy.TEMP_TABLES) &&
(this.getServerType().equalsIgnoreCase(DAOFactoryHelper.SQLSERVER)));
}
}