0001: /*
0002: * Copyright (c) 2002-2003 by OpenSymphony
0003: * All rights reserved.
0004: */
0005: package com.opensymphony.workflow.spi.jdbc;
0006:
0007: import com.opensymphony.module.propertyset.PropertySet;
0008: import com.opensymphony.module.propertyset.PropertySetManager;
0009:
0010: import com.opensymphony.workflow.StoreException;
0011: import com.opensymphony.workflow.query.Expression;
0012: import com.opensymphony.workflow.query.FieldExpression;
0013: import com.opensymphony.workflow.query.NestedExpression;
0014: import com.opensymphony.workflow.query.WorkflowExpressionQuery;
0015: import com.opensymphony.workflow.query.WorkflowQuery;
0016: import com.opensymphony.workflow.spi.SimpleStep;
0017: import com.opensymphony.workflow.spi.SimpleWorkflowEntry;
0018: import com.opensymphony.workflow.spi.Step;
0019: import com.opensymphony.workflow.spi.WorkflowEntry;
0020: import com.opensymphony.workflow.spi.WorkflowStore;
0021:
0022: import org.apache.commons.logging.Log;
0023: import org.apache.commons.logging.LogFactory;
0024:
0025: import java.sql.*;
0026:
0027: import java.util.*;
0028: import java.util.Date;
0029:
0030: import javax.naming.InitialContext;
0031: import javax.naming.NamingException;
0032:
0033: import javax.sql.DataSource;
0034:
0035: /**
0036: * JDBC implementation.
0037: * <p>
0038: *
0039: * The following properties are all <b>required</b>:
0040: * <ul>
0041: * <li><b>datasource</b> - the JNDI location for the DataSource that is to be used.</li>
0042: * <li><b>entry.sequence</b> - SQL query that returns the next ID for a workflow entry</li>
0043: * <li><b>entry.table</b> - table name for workflow entry</li>
0044: * <li><b>entry.id</b> - column name for workflow entry ID field</li>
0045: * <li><b>entry.name</b> - column name for workflow entry name field</li>
0046: * <li><b>entry.state</b> - column name for workflow entry state field</li>
0047: * <li><b>step.sequence</b> - SQL query that returns the next ID for a workflow step</li>
0048: * <li><b>history.table</b> - table name for steps in history</li>
0049: * <li><b>current.table</b> - table name for current steps</li>
0050: * <li><b>step.id</b> - column name for step ID field</li>
0051: * <li><b>step.entryId</b> - column name for workflow entry ID field (foreign key relationship to [entry.table].[entry.id])</li>
0052: * <li><b>step.stepId</b> - column name for step workflow definition step field</li>
0053: * <li><b>step.actionId</b> - column name for step action field</li>
0054: * <li><b>step.owner</b> - column name for step owner field</li>
0055: * <li><b>step.caller</b> - column name for step caller field</li>
0056: * <li><b>step.startDate</b> - column name for step start date field</li>
0057: * <li><b>step.dueDate</b> - column name for optional step due date field</li>
0058: * <li><b>step.finishDate</b> - column name for step finish date field</li>
0059: * <li><b>step.status</b> - column name for step status field</li>
0060: * <li><b>currentPrev.table</b> - table name for the previous IDs for current steps</li>
0061: * <li><b>historyPrev.table</b> - table name for the previous IDs for history steps</li>
0062: * <li><b>step.previousId</b> - column name for step ID field (foreign key relation to [history.table].[step.id] or [current.table].[step.id])</li>
0063: * </ul>
0064: *
0065: * @author <a href="mailto:plightbo@hotmail.com">Pat Lightbody</a>
0066: */
0067: public class JDBCWorkflowStore implements WorkflowStore {
0068: //~ Static fields/initializers /////////////////////////////////////////////
0069:
0070: private static final Log log = LogFactory
0071: .getLog(JDBCWorkflowStore.class);
0072:
0073: //~ Instance fields ////////////////////////////////////////////////////////
0074:
0075: protected DataSource ds;
0076: protected String currentPrevTable;
0077: protected String currentTable;
0078: protected String entryId;
0079: protected String entryName;
0080: protected String entrySequence;
0081: protected String entryState;
0082: protected String entryTable;
0083: protected String historyPrevTable;
0084: protected String historyTable;
0085: protected String stepActionId;
0086: protected String stepCaller;
0087: protected String stepDueDate;
0088: protected String stepEntryId;
0089: protected String stepFinishDate;
0090: protected String stepId;
0091: protected String stepOwner;
0092: protected String stepPreviousId;
0093: protected String stepSequence;
0094: protected String stepStartDate;
0095: protected String stepStatus;
0096: protected String stepStepId;
0097: protected boolean closeConnWhenDone = false;
0098:
0099: //~ Methods ////////////////////////////////////////////////////////////////
0100:
0101: public void setEntryState(long id, int state) throws StoreException {
0102: Connection conn = null;
0103: PreparedStatement ps = null;
0104:
0105: try {
0106: conn = getConnection();
0107:
0108: String sql = "UPDATE " + entryTable + " SET " + entryState
0109: + " = ? WHERE " + entryId + " = ?";
0110: ps = conn.prepareStatement(sql);
0111: ps.setInt(1, state);
0112: ps.setLong(2, id);
0113: ps.executeUpdate();
0114: } catch (SQLException e) {
0115: throw new StoreException(
0116: "Unable to update state for workflow instance #"
0117: + id + " to " + state, e);
0118: } finally {
0119: cleanup(conn, ps, null);
0120: }
0121: }
0122:
0123: public PropertySet getPropertySet(long entryId) {
0124: HashMap args = new HashMap(1);
0125: args.put("globalKey", "osff_" + entryId);
0126:
0127: return PropertySetManager.getInstance("jdbc", args);
0128: }
0129:
0130: ////////////METHOD #2 OF 3 //////////////////
0131: ////////// ...gur; ////////////////////
0132: //kiz
0133: public boolean checkIfORExists(NestedExpression nestedExpression) {
0134: //GURKAN;
0135: //This method checks if OR exists in any nested query
0136: //This method is used by doNestedNaturalJoin() to make sure
0137: //OR does not exist within query
0138: int numberOfExp = nestedExpression.getExpressionCount();
0139:
0140: if (nestedExpression.getExpressionOperator() == NestedExpression.OR) {
0141: return true;
0142: }
0143:
0144: for (int i = 0; i < numberOfExp; i++) {
0145: Expression expression = nestedExpression.getExpression(i);
0146:
0147: if (expression.isNested()) {
0148: NestedExpression nestedExp = (NestedExpression) expression;
0149:
0150: return checkIfORExists(nestedExp);
0151: }
0152: }
0153:
0154: //System.out.println("!!!...........false is returned ..!!!");
0155: return false;
0156: }
0157:
0158: public Step createCurrentStep(long entryId, int wfStepId,
0159: String owner, Date startDate, Date dueDate, String status,
0160: long[] previousIds) throws StoreException {
0161: Connection conn = null;
0162:
0163: try {
0164: conn = getConnection();
0165:
0166: long id = createCurrentStep(conn, entryId, wfStepId, owner,
0167: startDate, dueDate, status);
0168: addPreviousSteps(conn, id, previousIds);
0169:
0170: return new SimpleStep(id, entryId, wfStepId, 0, owner,
0171: startDate, dueDate, null, status, previousIds, null);
0172: } catch (SQLException e) {
0173: throw new StoreException(
0174: "Unable to create current step for workflow instance #"
0175: + entryId, e);
0176: } finally {
0177: cleanup(conn, null, null);
0178: }
0179: }
0180:
0181: public WorkflowEntry createEntry(String workflowName)
0182: throws StoreException {
0183: Connection conn = null;
0184: PreparedStatement stmt = null;
0185:
0186: try {
0187: conn = getConnection();
0188:
0189: String sql = "INSERT INTO " + entryTable + " (" + entryId
0190: + ", " + entryName + ", " + entryState
0191: + ") VALUES (?,?,?)";
0192:
0193: if (log.isDebugEnabled()) {
0194: log.debug("Executing SQL statement: " + sql);
0195: }
0196:
0197: stmt = conn.prepareStatement(sql);
0198:
0199: long id = getNextEntrySequence(conn);
0200: stmt.setLong(1, id);
0201: stmt.setString(2, workflowName);
0202: stmt.setInt(3, WorkflowEntry.CREATED);
0203: stmt.executeUpdate();
0204:
0205: return new SimpleWorkflowEntry(id, workflowName,
0206: WorkflowEntry.CREATED);
0207: } catch (SQLException e) {
0208: throw new StoreException(
0209: "Error creating new workflow instance", e);
0210: } finally {
0211: cleanup(conn, stmt, null);
0212: }
0213: }
0214:
0215: public List findCurrentSteps(long entryId) throws StoreException {
0216: Connection conn = null;
0217: PreparedStatement stmt = null;
0218: ResultSet rset = null;
0219: PreparedStatement stmt2 = null;
0220:
0221: try {
0222: conn = getConnection();
0223:
0224: String sql = "SELECT " + stepId + ", " + stepStepId + ", "
0225: + stepActionId + ", " + stepOwner + ", "
0226: + stepStartDate + ", " + stepDueDate + ", "
0227: + stepFinishDate + ", " + stepStatus + ", "
0228: + stepCaller + " FROM " + currentTable + " WHERE "
0229: + stepEntryId + " = ?";
0230: String sql2 = "SELECT " + stepPreviousId + " FROM "
0231: + currentPrevTable + " WHERE " + stepId + " = ?";
0232:
0233: if (log.isDebugEnabled()) {
0234: log.debug("Executing SQL statement: " + sql);
0235: }
0236:
0237: stmt = conn.prepareStatement(sql);
0238:
0239: if (log.isDebugEnabled()) {
0240: log.debug("Executing SQL statement: " + sql2);
0241: }
0242:
0243: stmt2 = conn.prepareStatement(sql2);
0244: stmt.setLong(1, entryId);
0245:
0246: rset = stmt.executeQuery();
0247:
0248: ArrayList currentSteps = new ArrayList();
0249:
0250: while (rset.next()) {
0251: long id = rset.getLong(1);
0252: int stepId = rset.getInt(2);
0253: int actionId = rset.getInt(3);
0254: String owner = rset.getString(4);
0255: Date startDate = rset.getTimestamp(5);
0256: Date dueDate = rset.getTimestamp(6);
0257: Date finishDate = rset.getTimestamp(7);
0258: String status = rset.getString(8);
0259: String caller = rset.getString(9);
0260:
0261: ArrayList prevIdsList = new ArrayList();
0262: stmt2.setLong(1, id);
0263:
0264: ResultSet rs = stmt2.executeQuery();
0265:
0266: while (rs.next()) {
0267: long prevId = rs.getLong(1);
0268: prevIdsList.add(new Long(prevId));
0269: }
0270:
0271: long[] prevIds = new long[prevIdsList.size()];
0272: int i = 0;
0273:
0274: for (Iterator iterator = prevIdsList.iterator(); iterator
0275: .hasNext();) {
0276: Long aLong = (Long) iterator.next();
0277: prevIds[i] = aLong.longValue();
0278: i++;
0279: }
0280:
0281: SimpleStep step = new SimpleStep(id, entryId, stepId,
0282: actionId, owner, startDate, dueDate,
0283: finishDate, status, prevIds, caller);
0284: currentSteps.add(step);
0285: }
0286:
0287: return currentSteps;
0288: } catch (SQLException e) {
0289: throw new StoreException(
0290: "Unable to locate current steps for workflow instance #"
0291: + entryId, e);
0292: } finally {
0293: cleanup(null, stmt2, null);
0294: cleanup(conn, stmt, rset);
0295: }
0296: }
0297:
0298: public WorkflowEntry findEntry(long theEntryId)
0299: throws StoreException {
0300: Connection conn = null;
0301: PreparedStatement stmt = null;
0302: ResultSet rset = null;
0303:
0304: try {
0305: conn = getConnection();
0306:
0307: String sql = "SELECT " + entryName + ", " + entryState
0308: + " FROM " + entryTable + " WHERE " + entryId
0309: + " = ?";
0310:
0311: if (log.isDebugEnabled()) {
0312: log.debug("Executing SQL statement: " + sql);
0313: }
0314:
0315: stmt = conn.prepareStatement(sql);
0316: stmt.setLong(1, theEntryId);
0317:
0318: rset = stmt.executeQuery();
0319: rset.next();
0320:
0321: String workflowName = rset.getString(1);
0322: int state = rset.getInt(2);
0323:
0324: return new SimpleWorkflowEntry(theEntryId, workflowName,
0325: state);
0326: } catch (SQLException e) {
0327: throw new StoreException(
0328: "Error finding workflow instance #" + entryId);
0329: } finally {
0330: cleanup(conn, stmt, rset);
0331: }
0332: }
0333:
0334: public List findHistorySteps(long entryId) throws StoreException {
0335: Connection conn = null;
0336: PreparedStatement stmt = null;
0337: PreparedStatement stmt2 = null;
0338: ResultSet rset = null;
0339:
0340: try {
0341: conn = getConnection();
0342:
0343: String sql = "SELECT " + stepId + ", " + stepStepId + ", "
0344: + stepActionId + ", " + stepOwner + ", "
0345: + stepStartDate + ", " + stepDueDate + ", "
0346: + stepFinishDate + ", " + stepStatus + ", "
0347: + stepCaller + " FROM " + historyTable + " WHERE "
0348: + stepEntryId + " = ? ORDER BY " + stepId + " DESC";
0349: String sql2 = "SELECT " + stepPreviousId + " FROM "
0350: + historyPrevTable + " WHERE " + stepId + " = ?";
0351:
0352: if (log.isDebugEnabled()) {
0353: log.debug("Executing SQL statement: " + sql);
0354: }
0355:
0356: stmt = conn.prepareStatement(sql);
0357:
0358: if (log.isDebugEnabled()) {
0359: log.debug("Executing SQL statement: " + sql2);
0360: }
0361:
0362: stmt2 = conn.prepareStatement(sql2);
0363: stmt.setLong(1, entryId);
0364:
0365: rset = stmt.executeQuery();
0366:
0367: ArrayList currentSteps = new ArrayList();
0368:
0369: while (rset.next()) {
0370: long id = rset.getLong(1);
0371: int stepId = rset.getInt(2);
0372: int actionId = rset.getInt(3);
0373: String owner = rset.getString(4);
0374: Date startDate = rset.getTimestamp(5);
0375: Date dueDate = rset.getTimestamp(6);
0376: Date finishDate = rset.getTimestamp(7);
0377: String status = rset.getString(8);
0378: String caller = rset.getString(9);
0379:
0380: ArrayList prevIdsList = new ArrayList();
0381: stmt2.setLong(1, id);
0382:
0383: ResultSet rs = stmt2.executeQuery();
0384:
0385: while (rs.next()) {
0386: long prevId = rs.getLong(1);
0387: prevIdsList.add(new Long(prevId));
0388: }
0389:
0390: long[] prevIds = new long[prevIdsList.size()];
0391: int i = 0;
0392:
0393: for (Iterator iterator = prevIdsList.iterator(); iterator
0394: .hasNext();) {
0395: Long aLong = (Long) iterator.next();
0396: prevIds[i] = aLong.longValue();
0397: i++;
0398: }
0399:
0400: SimpleStep step = new SimpleStep(id, entryId, stepId,
0401: actionId, owner, startDate, dueDate,
0402: finishDate, status, prevIds, caller);
0403: currentSteps.add(step);
0404: }
0405:
0406: return currentSteps;
0407: } catch (SQLException e) {
0408: throw new StoreException(
0409: "Unable to locate history steps for workflow instance #"
0410: + entryId, e);
0411: } finally {
0412: cleanup(null, stmt2, null);
0413: cleanup(conn, stmt, rset);
0414: }
0415: }
0416:
0417: public void init(Map props) throws StoreException {
0418: entrySequence = getInitProperty(props, "entry.sequence",
0419: "SELECT nextVal('seq_os_wfentry')");
0420: stepSequence = getInitProperty(props, "step.sequence",
0421: "SELECT nextVal('seq_os_currentsteps')");
0422: entryTable = getInitProperty(props, "entry.table", "OS_WFENTRY");
0423: entryId = getInitProperty(props, "entry.id", "ID");
0424: entryName = getInitProperty(props, "entry.name", "NAME");
0425: entryState = getInitProperty(props, "entry.state", "STATE");
0426: historyTable = getInitProperty(props, "history.table",
0427: "OS_HISTORYSTEP");
0428: currentTable = getInitProperty(props, "current.table",
0429: "OS_CURRENTSTEP");
0430: currentPrevTable = getInitProperty(props, "currentPrev.table",
0431: "OS_CURRENTSTEP_PREV");
0432: historyPrevTable = getInitProperty(props, "historyPrev.table",
0433: "OS_HISTORYSTEP_PREV");
0434: stepId = getInitProperty(props, "step.id", "ID");
0435: stepEntryId = getInitProperty(props, "step.entryId", "ENTRY_ID");
0436: stepStepId = getInitProperty(props, "step.stepId", "STEP_ID");
0437: stepActionId = getInitProperty(props, "step.actionId",
0438: "ACTION_ID");
0439: stepOwner = getInitProperty(props, "step.owner", "OWNER");
0440: stepCaller = getInitProperty(props, "step.caller", "CALLER");
0441: stepStartDate = getInitProperty(props, "step.startDate",
0442: "START_DATE");
0443: stepFinishDate = getInitProperty(props, "step.finishDate",
0444: "FINISH_DATE");
0445: stepDueDate = getInitProperty(props, "step.dueDate", "DUE_DATE");
0446: stepStatus = getInitProperty(props, "step.status", "STATUS");
0447: stepPreviousId = getInitProperty(props, "step.previousId",
0448: "PREVIOUS_ID");
0449:
0450: String jndi = (String) props.get("datasource");
0451:
0452: if (jndi != null) {
0453: try {
0454: ds = (DataSource) lookup(jndi);
0455:
0456: if (ds == null) {
0457: ds = (DataSource) new javax.naming.InitialContext()
0458: .lookup(jndi);
0459: }
0460: } catch (Exception e) {
0461: throw new StoreException(
0462: "Error looking up DataSource at " + jndi, e);
0463: }
0464: }
0465: }
0466:
0467: public Step markFinished(Step step, int actionId, Date finishDate,
0468: String status, String caller) throws StoreException {
0469: Connection conn = null;
0470: PreparedStatement stmt = null;
0471:
0472: try {
0473: conn = getConnection();
0474:
0475: String sql = "UPDATE " + currentTable + " SET "
0476: + stepStatus + " = ?, " + stepActionId + " = ?, "
0477: + stepFinishDate + " = ?, " + stepCaller
0478: + " = ? WHERE " + stepId + " = ?";
0479:
0480: if (log.isDebugEnabled()) {
0481: log.debug("Executing SQL statement: " + sql);
0482: }
0483:
0484: stmt = conn.prepareStatement(sql);
0485: stmt.setString(1, status);
0486: stmt.setInt(2, actionId);
0487: stmt.setTimestamp(3, new Timestamp(finishDate.getTime()));
0488: stmt.setString(4, caller);
0489: stmt.setLong(5, step.getId());
0490: stmt.executeUpdate();
0491:
0492: SimpleStep theStep = (SimpleStep) step;
0493: theStep.setActionId(actionId);
0494: theStep.setFinishDate(finishDate);
0495: theStep.setStatus(status);
0496: theStep.setCaller(caller);
0497:
0498: return theStep;
0499: } catch (SQLException e) {
0500: throw new StoreException(
0501: "Unable to mark step finished for #"
0502: + step.getEntryId(), e);
0503: } finally {
0504: cleanup(conn, stmt, null);
0505: }
0506: }
0507:
0508: public void moveToHistory(Step step) throws StoreException {
0509: Connection conn = null;
0510: PreparedStatement stmt = null;
0511:
0512: try {
0513: conn = getConnection();
0514:
0515: String sql = "INSERT INTO " + historyTable + " (" + stepId
0516: + ',' + stepEntryId + ", " + stepStepId + ", "
0517: + stepActionId + ", " + stepOwner + ", "
0518: + stepStartDate + ", " + stepFinishDate + ", "
0519: + stepStatus + ", " + stepCaller
0520: + ") VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
0521:
0522: if (log.isDebugEnabled()) {
0523: log.debug("Executing SQL statement: " + sql);
0524: }
0525:
0526: stmt = conn.prepareStatement(sql);
0527: stmt.setLong(1, step.getId());
0528: stmt.setLong(2, step.getEntryId());
0529: stmt.setInt(3, step.getStepId());
0530: stmt.setInt(4, step.getActionId());
0531: stmt.setString(5, step.getOwner());
0532: stmt.setTimestamp(6, new Timestamp(step.getStartDate()
0533: .getTime()));
0534:
0535: if (step.getFinishDate() != null) {
0536: stmt.setTimestamp(7, new Timestamp(step.getFinishDate()
0537: .getTime()));
0538: } else {
0539: stmt.setNull(7, Types.TIMESTAMP);
0540: }
0541:
0542: stmt.setString(8, step.getStatus());
0543: stmt.setString(9, step.getCaller());
0544: stmt.executeUpdate();
0545:
0546: long[] previousIds = step.getPreviousStepIds();
0547:
0548: if ((previousIds != null) && (previousIds.length > 0)) {
0549: sql = "INSERT INTO " + historyPrevTable + " (" + stepId
0550: + ", " + stepPreviousId + ") VALUES (?, ?)";
0551: log.debug("Executing SQL statement: " + sql);
0552: cleanup(null, stmt, null);
0553: stmt = conn.prepareStatement(sql);
0554:
0555: for (int i = 0; i < previousIds.length; i++) {
0556: long previousId = previousIds[i];
0557: stmt.setLong(1, step.getId());
0558: stmt.setLong(2, previousId);
0559: stmt.executeUpdate();
0560: }
0561: }
0562:
0563: sql = "DELETE FROM " + currentPrevTable + " WHERE "
0564: + stepId + " = ?";
0565:
0566: if (log.isDebugEnabled()) {
0567: log.debug("Executing SQL statement: " + sql);
0568: }
0569:
0570: cleanup(null, stmt, null);
0571: stmt = conn.prepareStatement(sql);
0572: stmt.setLong(1, step.getId());
0573: stmt.executeUpdate();
0574:
0575: sql = "DELETE FROM " + currentTable + " WHERE " + stepId
0576: + " = ?";
0577:
0578: if (log.isDebugEnabled()) {
0579: log.debug("Executing SQL statement: " + sql);
0580: }
0581:
0582: cleanup(null, stmt, null);
0583: stmt = conn.prepareStatement(sql);
0584: stmt.setLong(1, step.getId());
0585: stmt.executeUpdate();
0586: } catch (SQLException e) {
0587: throw new StoreException(
0588: "Unable to move current step to history step for #"
0589: + step.getEntryId(), e);
0590: } finally {
0591: cleanup(conn, stmt, null);
0592: }
0593: }
0594:
0595: public List query(WorkflowExpressionQuery e) throws StoreException {
0596: //GURKAN;
0597: // If it is simple, call buildSimple()
0598: // SELECT DISTINCT(ENTRY_ID) FROM OS_HISTORYSTEP WHERE FINISH_DATE < ?
0599: //
0600: // If it is nested, call doNestedNaturalJoin() if and only if the query is
0601: // ANDed including nested-nestd queries
0602: // If OR exists in any query call buildNested()
0603: //
0604: //doNestedNaturalJoin()
0605: // This doNestedNaturalJoin() method improves performance of the queries if and only if
0606: // the queries including nested queries are ANDed
0607: //
0608: // SELECT DISTINCT (a1.ENTRY_ID) AS retrieved
0609: // FROM OS_CURRENTSTEP AS a1 , OS_CURRENTSTEP AS a2 , OS_CURRENTSTEP AS a3 , OS_CURRENTSTEP AS a4
0610: // WHERE ((a1.ENTRY_ID = a1.ENTRY_ID AND a1.ENTRY_ID = a2.ENTRY_ID) AND
0611: // (a2.ENTRY_ID = a3.ENTRY_ID AND a3.ENTRY_ID = a4.ENTRY_ID))
0612: // AND ( a1.OWNER = ? AND a2.STATUS != ? AND a3.OWNER = ? AND a4.STATUS != ? )
0613: //
0614: //doNestedLeftJoin() //not used
0615: // For this method to work, order of queries is matter
0616: // This doNestedLeftJoin() method will generate the queries but it works if and only if
0617: // the query is in correct order -- it is your luck
0618: // SELECT DISTINCT (a0.ENTRY_ID) AS retrieved FROM OS_CURRENTSTEP AS a0
0619: // LEFT JOIN OS_CURRENTSTEP a1 ON a0.ENTRY_ID = a1.ENTRY_ID
0620: //
0621: // LEFT JOIN OS_CURRENTSTEP a2 ON a1.ENTRY_ID = a2.ENTRY_ID
0622: // LEFT JOIN OS_CURRENTSTEP a3 ON a2.ENTRY_ID = a3.ENTRY_ID
0623: // WHERE a1.OWNER = ? AND (a2.STATUS = ? OR a3.OWNER = ?)
0624: //
0625: if (log.isDebugEnabled()) {
0626: log.debug("Starting Query");
0627: }
0628:
0629: Expression expression = e.getExpression();
0630:
0631: if (log.isDebugEnabled()) {
0632: log.debug("Have all variables");
0633: }
0634:
0635: if (expression.isNested()) {
0636: NestedExpression nestedExp = (NestedExpression) expression;
0637:
0638: StringBuffer sel = new StringBuffer();
0639: StringBuffer columns = new StringBuffer();
0640: StringBuffer leftJoin = new StringBuffer();
0641: StringBuffer where = new StringBuffer();
0642: StringBuffer whereComp = new StringBuffer();
0643: StringBuffer orderBy = new StringBuffer();
0644: List values = new LinkedList();
0645: List queries = new LinkedList();
0646:
0647: String columnName;
0648: String selectString;
0649:
0650: //Expression is nested and see if the expresion has OR
0651: if (checkIfORExists(nestedExp)) {
0652: //For doNestedLeftJoin() uncomment these -- again order is matter
0653: //and comment out last two lines where buildNested() is called
0654: //
0655: //columns.append("SELECT DISTINCT (");
0656: //columns.append("a0" + "." + stepEntryId);
0657: //columnName = "retrieved";
0658: //columns.append(") AS " + columnName);
0659: //columns.append(" FROM ");
0660: //columns.append(currentTable + " AS " + "a0");
0661: //where.append("WHERE ");
0662: //doNestedLeftJoin(e, nestedExp, leftJoin, where, values, queries, orderBy);
0663: //selectString = columns.toString() + " " + leftJoin.toString() + " " + where.toString() + " " + orderBy.toString();
0664: //System.out.println("LEFT JOIN ...");
0665: //
0666: //
0667: columnName = buildNested(nestedExp, sel, values);
0668: selectString = sel.toString();
0669: } else {
0670: columns.append("SELECT DISTINCT (");
0671: columns.append("a1" + '.' + stepEntryId);
0672: columnName = "retrieved";
0673: columns.append(") AS " + columnName);
0674: columns.append(" FROM ");
0675: where.append("WHERE ");
0676:
0677: doNestedNaturalJoin(e, nestedExp, columns, where,
0678: whereComp, values, queries, orderBy);
0679: selectString = columns.toString() + ' '
0680: + leftJoin.toString() + ' ' + where.toString()
0681: + " AND ( " + whereComp.toString() + " ) "
0682: + ' ' + orderBy.toString();
0683:
0684: // System.out.println("NATURAL JOIN ...");
0685: }
0686:
0687: //System.out.println("number of queries is : " + queries.size());
0688: //System.out.println("values.toString() : " + values.toString());
0689: //System.out.println("columnName : " + columnName);
0690: //System.out.println("where : " + where);
0691: //System.out.println("whereComp : " + whereComp);
0692: //System.out.println("columns : " + columns);
0693: // System.out.println("Query is : " + selectString + "\n");
0694: return doExpressionQuery(selectString, columnName, values);
0695: } else {
0696: // query is not empty ... it's a SIMPLE query
0697: // do what the old query did
0698: StringBuffer qry;
0699: List values = new LinkedList();
0700:
0701: qry = new StringBuffer();
0702:
0703: String columnName = buildSimple(
0704: (FieldExpression) expression, qry, values);
0705:
0706: if (e.getSortOrder() != WorkflowExpressionQuery.SORT_NONE) {
0707: qry.append(" ORDER BY ");
0708:
0709: if (e.getOrderBy() != 0) {
0710: String fName = fieldName(e.getOrderBy());
0711:
0712: qry.append(fName);
0713:
0714: // To help w/ MySQL and Informix, you have to include the column in the query
0715: String current = qry.toString();
0716: String entry = current.substring(0, current
0717: .indexOf(columnName))
0718: + columnName + "), " + fName + ' ';
0719: entry += current.substring(current
0720: .indexOf(columnName)
0721: + columnName.length() + 1);
0722:
0723: qry = new StringBuffer(entry);
0724:
0725: if (e.getSortOrder() == WorkflowExpressionQuery.SORT_DESC) {
0726: qry.append(" DESC");
0727: } else {
0728: qry.append(" ASC");
0729: }
0730: } else {
0731: qry.append(columnName);
0732: }
0733: }
0734:
0735: //System.out.println("Query is: " + qry.toString());
0736: return doExpressionQuery(qry.toString(), columnName, values);
0737: }
0738: }
0739:
0740: public List query(WorkflowQuery query) throws StoreException {
0741: List results = new ArrayList();
0742:
0743: // going to try to do all the comparisons in one query
0744: String sel;
0745: String table;
0746:
0747: int qtype = query.getType();
0748:
0749: if (qtype == 0) { // then not set, so look in sub queries
0750: // todo: not sure if you would have a query that would look in both old and new, if so, i'll have to change this - TR
0751: // but then again, why are there redundant tables in the first place? the data model should probably change
0752:
0753: if (query.getLeft() != null) {
0754: qtype = query.getLeft().getType();
0755: }
0756: }
0757:
0758: if (qtype == WorkflowQuery.CURRENT) {
0759: table = currentTable;
0760: } else {
0761: table = historyTable;
0762: }
0763:
0764: sel = "SELECT DISTINCT(" + stepEntryId + ") FROM " + table
0765: + " WHERE ";
0766: sel += queryWhere(query);
0767:
0768: if (log.isDebugEnabled()) {
0769: log.debug(sel);
0770: }
0771:
0772: Connection conn = null;
0773: Statement stmt = null;
0774: ResultSet rs = null;
0775:
0776: try {
0777: conn = getConnection();
0778: stmt = conn.createStatement();
0779: rs = stmt.executeQuery(sel);
0780:
0781: while (rs.next()) {
0782: // get entryIds and add to results list
0783: Long id = new Long(rs.getLong(stepEntryId));
0784: results.add(id);
0785: }
0786: } catch (SQLException ex) {
0787: throw new StoreException("SQL Exception in query: "
0788: + ex.getMessage());
0789: } finally {
0790: cleanup(conn, stmt, rs);
0791: }
0792:
0793: return results;
0794: }
0795:
0796: protected Connection getConnection() throws SQLException {
0797: closeConnWhenDone = true;
0798:
0799: return ds.getConnection();
0800: }
0801:
0802: protected long getNextEntrySequence(Connection c)
0803: throws SQLException {
0804: if (log.isDebugEnabled()) {
0805: log.debug("Executing SQL statement: " + entrySequence);
0806: }
0807:
0808: PreparedStatement stmt = null;
0809: ResultSet rset = null;
0810:
0811: try {
0812: stmt = c.prepareStatement(entrySequence);
0813: rset = stmt.executeQuery();
0814: rset.next();
0815:
0816: long id = rset.getLong(1);
0817:
0818: return id;
0819: } finally {
0820: cleanup(null, stmt, rset);
0821: }
0822: }
0823:
0824: protected long getNextStepSequence(Connection c)
0825: throws SQLException {
0826: if (log.isDebugEnabled()) {
0827: log.debug("Executing SQL statement: " + stepSequence);
0828: }
0829:
0830: PreparedStatement stmt = null;
0831: ResultSet rset = null;
0832:
0833: try {
0834: stmt = c.prepareStatement(stepSequence);
0835: rset = stmt.executeQuery();
0836: rset.next();
0837:
0838: long id = rset.getLong(1);
0839:
0840: return id;
0841: } finally {
0842: cleanup(null, stmt, rset);
0843: }
0844: }
0845:
0846: protected void addPreviousSteps(Connection conn, long id,
0847: long[] previousIds) throws SQLException {
0848: if ((previousIds != null) && (previousIds.length > 0)) {
0849: if (!((previousIds.length == 1) && (previousIds[0] == 0))) {
0850: String sql = "INSERT INTO " + currentPrevTable + " ("
0851: + stepId + ", " + stepPreviousId
0852: + ") VALUES (?, ?)";
0853: log.debug("Executing SQL statement: " + sql);
0854:
0855: PreparedStatement stmt = conn.prepareStatement(sql);
0856:
0857: for (int i = 0; i < previousIds.length; i++) {
0858: long previousId = previousIds[i];
0859: stmt.setLong(1, id);
0860: stmt.setLong(2, previousId);
0861: stmt.executeUpdate();
0862: }
0863:
0864: cleanup(null, stmt, null);
0865: }
0866: }
0867: }
0868:
0869: protected void cleanup(Connection connection, Statement statement,
0870: ResultSet result) {
0871: if (result != null) {
0872: try {
0873: result.close();
0874: } catch (SQLException ex) {
0875: log.error("Error closing resultset", ex);
0876: }
0877: }
0878:
0879: if (statement != null) {
0880: try {
0881: statement.close();
0882: } catch (SQLException ex) {
0883: log.error("Error closing statement", ex);
0884: }
0885: }
0886:
0887: if ((connection != null) && closeConnWhenDone) {
0888: try {
0889: connection.close();
0890: } catch (SQLException ex) {
0891: log.error("Error closing connection", ex);
0892: }
0893: }
0894: }
0895:
0896: protected long createCurrentStep(Connection conn, long entryId,
0897: int wfStepId, String owner, Date startDate, Date dueDate,
0898: String status) throws SQLException {
0899: String sql = "INSERT INTO " + currentTable + " (" + stepId
0900: + ',' + stepEntryId + ", " + stepStepId + ", "
0901: + stepActionId + ", " + stepOwner + ", "
0902: + stepStartDate + ", " + stepDueDate + ", "
0903: + stepFinishDate + ", " + stepStatus + ", "
0904: + stepCaller
0905: + " ) VALUES (?, ?, ?, null, ?, ?, ?, null, ?, null)";
0906:
0907: if (log.isDebugEnabled()) {
0908: log.debug("Executing SQL statement: " + sql);
0909: }
0910:
0911: PreparedStatement stmt = conn.prepareStatement(sql);
0912:
0913: long id = getNextStepSequence(conn);
0914: stmt.setLong(1, id);
0915: stmt.setLong(2, entryId);
0916: stmt.setInt(3, wfStepId);
0917: stmt.setString(4, owner);
0918: stmt.setTimestamp(5, new Timestamp(startDate.getTime()));
0919:
0920: if (dueDate != null) {
0921: stmt.setTimestamp(6, new Timestamp(dueDate.getTime()));
0922: } else {
0923: stmt.setNull(6, Types.TIMESTAMP);
0924: }
0925:
0926: stmt.setString(7, status);
0927: stmt.executeUpdate();
0928: cleanup(null, stmt, null);
0929:
0930: return id;
0931: }
0932:
0933: ////////////METHOD #3 OF 3 //////////////////
0934: ////////// ...gur; ////////////////////
0935: //kardes
0936: void doNestedNaturalJoin(WorkflowExpressionQuery e,
0937: NestedExpression nestedExpression, StringBuffer columns,
0938: StringBuffer where, StringBuffer whereComp, List values,
0939: List queries, StringBuffer orderBy) { // throws StoreException {
0940:
0941: Object value;
0942: int currentExpField;
0943:
0944: int numberOfExp = nestedExpression.getExpressionCount();
0945:
0946: for (int i = 0; i < numberOfExp; i++) { //ori
0947:
0948: //for (i = numberOfExp; i > 0; i--) { //reverse 1 of 3
0949: Expression expression = nestedExpression.getExpression(i); //ori
0950:
0951: //Expression expression = nestedExpression.getExpression(i - 1); //reverse 2 of 3
0952: if (!(expression.isNested())) {
0953: FieldExpression fieldExp = (FieldExpression) expression;
0954:
0955: FieldExpression fieldExpBeforeCurrent;
0956: queries.add(expression);
0957:
0958: int queryId = queries.size();
0959:
0960: if (queryId > 1) {
0961: columns.append(" , ");
0962: }
0963:
0964: //do; OS_CURRENTSTEP AS a1 ....
0965: if (fieldExp.getContext() == FieldExpression.CURRENT_STEPS) {
0966: columns.append(currentTable + " AS " + 'a'
0967: + queryId);
0968: } else if (fieldExp.getContext() == FieldExpression.HISTORY_STEPS) {
0969: columns.append(historyTable + " AS " + 'a'
0970: + queryId);
0971: } else {
0972: columns.append(entryTable + " AS " + 'a' + queryId);
0973: }
0974:
0975: ///////// beginning of WHERE JOINS/s : //////////////////////////////////////////
0976: //do for first query; a1.ENTRY_ID = a1.ENTRY_ID
0977: if (queryId == 1) {
0978: where.append("a1" + '.' + stepEntryId);
0979: where.append(" = ");
0980:
0981: if (fieldExp.getContext() == FieldExpression.CURRENT_STEPS) {
0982: where.append("a" + queryId + '.' + stepEntryId);
0983: } else if (fieldExp.getContext() == FieldExpression.HISTORY_STEPS) {
0984: where.append("a" + queryId + '.' + stepEntryId);
0985: } else {
0986: where.append("a" + queryId + '.' + entryId);
0987: }
0988: }
0989:
0990: //do; a1.ENTRY_ID = a2.ENTRY_ID
0991: if (queryId > 1) {
0992: fieldExpBeforeCurrent = (FieldExpression) queries
0993: .get(queryId - 2);
0994:
0995: if (fieldExpBeforeCurrent.getContext() == FieldExpression.CURRENT_STEPS) {
0996: where.append("a" + (queryId - 1) + '.'
0997: + stepEntryId);
0998: } else if (fieldExpBeforeCurrent.getContext() == FieldExpression.HISTORY_STEPS) {
0999: where.append("a" + (queryId - 1) + '.'
1000: + stepEntryId);
1001: } else {
1002: where.append("a" + (queryId - 1) + '.'
1003: + entryId);
1004: }
1005:
1006: where.append(" = ");
1007:
1008: if (fieldExp.getContext() == FieldExpression.CURRENT_STEPS) {
1009: where.append("a" + queryId + '.' + stepEntryId);
1010: } else if (fieldExp.getContext() == FieldExpression.HISTORY_STEPS) {
1011: where.append("a" + queryId + '.' + stepEntryId);
1012: } else {
1013: where.append("a" + queryId + '.' + entryId);
1014: }
1015: }
1016:
1017: ///////// end of LEFT JOIN : "LEFT JOIN OS_CURRENTSTEP a1 ON a0.ENTRY_ID = a1.ENTRY_ID
1018: //
1019: //////// BEGINNING OF WHERE clause //////////////////////////////////////////////////
1020: value = fieldExp.getValue();
1021: currentExpField = fieldExp.getField();
1022:
1023: //if the Expression is negated and FieldExpression is "EQUALS", we need to negate that FieldExpression
1024: if (expression.isNegate()) {
1025: //do ; a2.STATUS !=
1026: whereComp.append("a" + queryId + '.'
1027: + fieldName(fieldExp.getField()));
1028:
1029: switch (fieldExp.getOperator()) { //WHERE a1.STATUS !=
1030: case FieldExpression.EQUALS:
1031:
1032: if (value == null) {
1033: whereComp.append(" IS NOT ");
1034: } else {
1035: whereComp.append(" != ");
1036: }
1037:
1038: break;
1039:
1040: case FieldExpression.NOT_EQUALS:
1041:
1042: if (value == null) {
1043: whereComp.append(" IS ");
1044: } else {
1045: whereComp.append(" = ");
1046: }
1047:
1048: break;
1049:
1050: case FieldExpression.GT:
1051: whereComp.append(" < ");
1052:
1053: break;
1054:
1055: case FieldExpression.LT:
1056: whereComp.append(" > ");
1057:
1058: break;
1059:
1060: default:
1061: whereComp.append(" != ");
1062:
1063: break;
1064: }
1065:
1066: switch (currentExpField) {
1067: case FieldExpression.START_DATE:
1068: case FieldExpression.FINISH_DATE:
1069: values.add(new Timestamp(
1070: ((java.util.Date) value).getTime()));
1071:
1072: break;
1073:
1074: default:
1075:
1076: if (value == null) {
1077: values.add(null);
1078: } else {
1079: values.add(value);
1080: }
1081:
1082: break;
1083: }
1084: } else {
1085: //do; a1.OWNER =
1086: whereComp.append("a" + queryId + '.'
1087: + fieldName(fieldExp.getField()));
1088:
1089: switch (fieldExp.getOperator()) { //WHERE a2.FINISH_DATE <
1090: case FieldExpression.EQUALS:
1091:
1092: if (value == null) {
1093: whereComp.append(" IS ");
1094: } else {
1095: whereComp.append(" = ");
1096: }
1097:
1098: break;
1099:
1100: case FieldExpression.NOT_EQUALS:
1101:
1102: if (value == null) {
1103: whereComp.append(" IS NOT ");
1104: } else {
1105: whereComp.append(" <> ");
1106: }
1107:
1108: break;
1109:
1110: case FieldExpression.GT:
1111: whereComp.append(" > ");
1112:
1113: break;
1114:
1115: case FieldExpression.LT:
1116: whereComp.append(" < ");
1117:
1118: break;
1119:
1120: default:
1121: whereComp.append(" = ");
1122:
1123: break;
1124: }
1125:
1126: switch (currentExpField) {
1127: case FieldExpression.START_DATE:
1128: case FieldExpression.FINISH_DATE:
1129: values.add(new Timestamp(
1130: ((java.util.Date) value).getTime()));
1131:
1132: break;
1133:
1134: default:
1135:
1136: if (value == null) {
1137: values.add(null);
1138: } else {
1139: values.add(value);
1140: }
1141:
1142: break;
1143: }
1144: }
1145:
1146: //do; a1.OWNER = ? ... a2.STATUS != ?
1147: whereComp.append(" ? ");
1148:
1149: //////// END OF WHERE clause////////////////////////////////////////////////////////////
1150: if ((e.getSortOrder() != WorkflowExpressionQuery.SORT_NONE)
1151: && (e.getOrderBy() != 0)) {
1152: System.out.println("ORDER BY ; queries.size() : "
1153: + queries.size());
1154: orderBy.append(" ORDER BY ");
1155: orderBy.append("a1" + '.'
1156: + fieldName(e.getOrderBy()));
1157:
1158: if (e.getSortOrder() == WorkflowExpressionQuery.SORT_ASC) {
1159: orderBy.append(" ASC");
1160: } else if (e.getSortOrder() == WorkflowExpressionQuery.SORT_DESC) {
1161: orderBy.append(" DESC");
1162: }
1163: }
1164: } else {
1165: NestedExpression nestedExp = (NestedExpression) expression;
1166:
1167: where.append('(');
1168:
1169: doNestedNaturalJoin(e, nestedExp, columns, where,
1170: whereComp, values, queries, orderBy);
1171:
1172: where.append(')');
1173: }
1174:
1175: //add AND or OR clause between the queries
1176: if (i < (numberOfExp - 1)) { //ori
1177:
1178: //if (i > 1) { //reverse 3 of 3
1179: if (nestedExpression.getExpressionOperator() == NestedExpression.AND) {
1180: where.append(" AND ");
1181: whereComp.append(" AND ");
1182: } else {
1183: where.append(" OR ");
1184: whereComp.append(" OR ");
1185: }
1186: }
1187: }
1188: }
1189:
1190: private String getInitProperty(Map props, String strName,
1191: String strDefault) {
1192: Object o = props.get(strName);
1193:
1194: if (o == null) {
1195: return strDefault;
1196: }
1197:
1198: return (String) o;
1199: }
1200:
1201: private String buildNested(NestedExpression nestedExpression,
1202: StringBuffer sel, List values) {
1203: sel.append("SELECT DISTINCT(");
1204:
1205: // Changed by Anthony on 2 June 2004, to query from OS_CURRENTSTEP instead
1206: //sel.append(entryId);
1207: sel.append(stepEntryId);
1208: sel.append(") FROM ");
1209:
1210: // Changed by Anthony on 2 June 2004, to query from OS_CURRENTSTEP instead
1211: // sel.append(entryTable);
1212: sel.append(currentTable);
1213:
1214: if (log.isDebugEnabled()) {
1215: log.debug("Thus far, query is: " + sel.toString());
1216: }
1217:
1218: for (int i = 0; i < nestedExpression.getExpressionCount(); i++) {
1219: Expression expression = nestedExpression.getExpression(i);
1220:
1221: if (i == 0) {
1222: sel.append(" WHERE ");
1223: } else {
1224: if (nestedExpression.getExpressionOperator() == NestedExpression.AND) {
1225: sel.append(" AND ");
1226: } else {
1227: sel.append(" OR ");
1228: }
1229: }
1230:
1231: if (expression.isNegate()) {
1232: sel.append(" NOT ");
1233: }
1234:
1235: // Changed by Anthony on 2 June 2004, to query from OS_CURRENTSTEP instead
1236: // sel.append(entryId);
1237: sel.append(stepEntryId);
1238: sel.append(" IN (");
1239:
1240: if (expression.isNested()) {
1241: this .buildNested((NestedExpression) nestedExpression
1242: .getExpression(i), sel, values);
1243: } else {
1244: FieldExpression sub = (FieldExpression) nestedExpression
1245: .getExpression(i);
1246: this .buildSimple(sub, sel, values);
1247: }
1248:
1249: sel.append(')');
1250: }
1251:
1252: // Changed by Anthony on 2 June 2004, to query from OS_CURRENTSTEP instead
1253: // return (entryId);
1254: return (stepEntryId);
1255: }
1256:
1257: private String buildSimple(FieldExpression fieldExpression,
1258: StringBuffer sel, List values) {
1259: String table;
1260: String columnName;
1261:
1262: if (fieldExpression.getContext() == FieldExpression.CURRENT_STEPS) {
1263: table = currentTable;
1264: columnName = stepEntryId;
1265: } else if (fieldExpression.getContext() == FieldExpression.HISTORY_STEPS) {
1266: table = historyTable;
1267: columnName = stepEntryId;
1268: } else {
1269: table = entryTable;
1270: columnName = entryId;
1271: }
1272:
1273: sel.append("SELECT DISTINCT(");
1274: sel.append(columnName);
1275: sel.append(") FROM ");
1276: sel.append(table);
1277: sel.append(" WHERE ");
1278: queryComparison(fieldExpression, sel, values);
1279:
1280: return columnName;
1281: }
1282:
1283: private List doExpressionQuery(String sel, String columnName,
1284: List values) throws StoreException {
1285: if (log.isDebugEnabled()) {
1286: log.debug(sel);
1287: }
1288:
1289: Connection conn = null;
1290: PreparedStatement stmt = null;
1291: ResultSet rs = null;
1292: List results = new ArrayList();
1293:
1294: try {
1295: conn = getConnection();
1296: stmt = conn.prepareStatement(sel);
1297:
1298: if (!values.isEmpty()) {
1299: for (int i = 1; i <= values.size(); i++) {
1300: stmt.setObject(i, values.get(i - 1));
1301: }
1302: }
1303:
1304: rs = stmt.executeQuery();
1305:
1306: while (rs.next()) {
1307: // get entryIds and add to results list
1308: Long id = new Long(rs.getLong(columnName));
1309: results.add(id);
1310: }
1311:
1312: return results;
1313: } catch (SQLException ex) {
1314: throw new StoreException("SQL Exception in query: "
1315: + ex.getMessage());
1316: } finally {
1317: cleanup(conn, stmt, rs);
1318: }
1319: }
1320:
1321: private static String escape(String s) {
1322: StringBuffer sb = new StringBuffer(s);
1323:
1324: char c;
1325: char[] chars = s.toCharArray();
1326:
1327: for (int i = 0; i < chars.length; i++) {
1328: c = chars[i];
1329:
1330: switch (c) {
1331: case '\'':
1332: sb.insert(i, '\'');
1333: i++;
1334:
1335: break;
1336:
1337: case '\\':
1338: sb.insert(i, '\\');
1339: i++;
1340: }
1341: }
1342:
1343: return sb.toString();
1344: }
1345:
1346: private String fieldName(int field) {
1347: switch (field) {
1348: case FieldExpression.ACTION: // actionId
1349: return stepActionId;
1350:
1351: case FieldExpression.CALLER:
1352: return stepCaller;
1353:
1354: case FieldExpression.FINISH_DATE:
1355: return stepFinishDate;
1356:
1357: case FieldExpression.OWNER:
1358: return stepOwner;
1359:
1360: case FieldExpression.START_DATE:
1361: return stepStartDate;
1362:
1363: case FieldExpression.STEP: // stepId
1364: return stepStepId;
1365:
1366: case FieldExpression.STATUS:
1367: return stepStatus;
1368:
1369: case FieldExpression.STATE:
1370: return entryState;
1371:
1372: case FieldExpression.NAME:
1373: return entryName;
1374:
1375: case FieldExpression.DUE_DATE:
1376: return stepDueDate;
1377:
1378: default:
1379: return "1";
1380: }
1381: }
1382:
1383: private Object lookup(String location) throws NamingException {
1384: try {
1385: InitialContext context = new InitialContext();
1386:
1387: try {
1388: return context.lookup(location);
1389: } catch (NamingException e) {
1390: //ok, couldn't find it, look in env
1391: return context.lookup("java:comp/env/" + location);
1392: }
1393: } catch (NamingException e) {
1394: throw e;
1395: }
1396: }
1397:
1398: private String queryComparison(WorkflowQuery query) {
1399: Object value = query.getValue();
1400: int operator = query.getOperator();
1401: int field = query.getField();
1402:
1403: //int type = query.getType();
1404: String oper;
1405:
1406: switch (operator) {
1407: case WorkflowQuery.EQUALS:
1408: oper = " = ";
1409:
1410: break;
1411:
1412: case WorkflowQuery.NOT_EQUALS:
1413: oper = " <> ";
1414:
1415: break;
1416:
1417: case WorkflowQuery.GT:
1418: oper = " > ";
1419:
1420: break;
1421:
1422: case WorkflowQuery.LT:
1423: oper = " < ";
1424:
1425: break;
1426:
1427: default:
1428: oper = " = ";
1429: }
1430:
1431: String left = fieldName(field);
1432: String right;
1433:
1434: if (value != null) {
1435: right = '\'' + escape(value.toString()) + '\'';
1436: } else {
1437: right = "null";
1438: }
1439:
1440: return left + oper + right;
1441: }
1442:
1443: /**
1444: * Method queryComparison
1445: *
1446: * @param expression a FieldExpression
1447: * @param sel a StringBuffer
1448: *
1449: */
1450: private void queryComparison(FieldExpression expression,
1451: StringBuffer sel, List values) {
1452: Object value = expression.getValue();
1453: int operator = expression.getOperator();
1454: int field = expression.getField();
1455:
1456: String oper;
1457:
1458: switch (operator) {
1459: case FieldExpression.EQUALS:
1460:
1461: if (value == null) {
1462: oper = " IS ";
1463: } else {
1464: oper = " = ";
1465: }
1466:
1467: break;
1468:
1469: case FieldExpression.NOT_EQUALS:
1470:
1471: if (value == null) {
1472: oper = " IS NOT ";
1473: } else {
1474: oper = " <> ";
1475: }
1476:
1477: break;
1478:
1479: case FieldExpression.GT:
1480: oper = " > ";
1481:
1482: break;
1483:
1484: case FieldExpression.LT:
1485: oper = " < ";
1486:
1487: break;
1488:
1489: default:
1490: oper = " = ";
1491: }
1492:
1493: String left = fieldName(field);
1494: String right = "?";
1495:
1496: switch (field) {
1497: case FieldExpression.FINISH_DATE:
1498: values.add(new Timestamp(((Date) value).getTime()));
1499:
1500: break;
1501:
1502: case FieldExpression.START_DATE:
1503: values.add(new Timestamp(((Date) value).getTime()));
1504:
1505: break;
1506:
1507: case FieldExpression.DUE_DATE:
1508: values.add(new Timestamp(((Date) value).getTime()));
1509:
1510: break;
1511:
1512: default:
1513:
1514: if (value == null) {
1515: right = "null";
1516: } else {
1517: values.add(value);
1518: }
1519: }
1520:
1521: sel.append(left);
1522: sel.append(oper);
1523: sel.append(right);
1524: }
1525:
1526: private String queryWhere(WorkflowQuery query) {
1527: if (query.getLeft() == null) {
1528: // leaf node
1529: return queryComparison(query);
1530: } else {
1531: int operator = query.getOperator();
1532: WorkflowQuery left = query.getLeft();
1533: WorkflowQuery right = query.getRight();
1534:
1535: switch (operator) {
1536: case WorkflowQuery.AND:
1537: return '(' + queryWhere(left) + " AND "
1538: + queryWhere(right) + ')';
1539:
1540: case WorkflowQuery.OR:
1541: return '(' + queryWhere(left) + " OR "
1542: + queryWhere(right) + ')';
1543:
1544: case WorkflowQuery.XOR:
1545: return '(' + queryWhere(left) + " XOR "
1546: + queryWhere(right) + ')';
1547: }
1548: }
1549:
1550: return ""; // not sure if we should throw an exception or how this should be handled
1551: }
1552: }
|