001: package org.obe.server.util;
002:
003: import org.apache.commons.logging.Log;
004: import org.apache.commons.logging.LogFactory;
005: import org.obe.OBERuntimeException;
006: import org.obe.client.api.repository.RepositoryException;
007: import org.obe.engine.WorkflowEngineUtilities;
008: import org.obe.sql.*;
009: import org.obe.xpdl.model.data.DataType;
010: import org.obe.xpdl.model.data.DataTypes;
011: import org.obe.xpdl.model.data.Type;
012: import org.obe.xpdl.model.workflow.WorkflowProcess;
013: import org.wfmc.wapi.WMInvalidAttributeException;
014:
015: import java.beans.FeatureDescriptor;
016: import java.beans.PropertyDescriptor;
017: import java.io.IOException;
018: import java.io.StringReader;
019: import java.io.StringWriter;
020: import java.sql.*;
021: import java.util.*;
022: import java.util.Date;
023:
024: /**
025: * Builds an SQL query by AND-ing multiple query terms together. The query
026: * terms can be simple 'attribute-comparison-value' terms, or SQL predicates of
027: * arbitrary complexity. All literal values (including those in SQL-style
028: * predicates) are replaced by JDBC parameter markers. References to system
029: * attributes are translated directly into COLUMN references, and references to
030: * custom attributes are replaced by references to a uniquely joined row from
031: * the OBEATTRIBUTEINSTANCE table.
032: *
033: * @author Adrian Price
034: */
035: public class QueryBuilder {
036: private static final Log _logger = LogFactory
037: .getLog(QueryBuilder.class);
038: private static final Integer BOOLEAN_TYPE = new Integer(
039: Type.BOOLEAN_TYPE);
040: private static final Integer DATETIME_TYPE = new Integer(
041: Type.DATETIME_TYPE);
042: private static final Integer FLOAT_TYPE = new Integer(
043: Type.FLOAT_TYPE);
044: private static final Integer INTEGER_TYPE = new Integer(
045: Type.INTEGER_TYPE);
046: private static final Integer SCHEMA_TYPE = new Integer(
047: Type.SCHEMA_TYPE);
048: private static final Integer STRING_TYPE = new Integer(
049: Type.STRING_TYPE);
050: private static final int INTEGER_CACHE_SIZE = 15;
051: private static final Integer[] _integers = new Integer[INTEGER_CACHE_SIZE];
052: private static final Map _wapiToJdbc = new HashMap();
053: private static final String BOOLVALUE = "BOOLVALUE";
054: private static final String DATEVALUE = "DATEVALUE";
055: private static final String DBLVALUE = "DBLVALUE";
056: private static final String INTVALUE = "INTVALUE";
057: private static final String STRVALUE = "STRVALUE";
058: private static final String OBJVALUE = "OBJVALUE";
059: private static final String[] _columnNames = { STRVALUE, // STRING_TYPE
060: DBLVALUE, // FLOAT_TYPE
061: INTVALUE, // INTEGER_TYPE
062: OBJVALUE, // REFERENCE_TYPE
063: DATEVALUE, // DATETIME_TYPE
064: BOOLVALUE, // BOOLEAN_TYPE
065: STRVALUE, // PERFORMER_TYPE
066: null, // DECLARED_TYPE (always mapped to another type)
067: OBJVALUE, // SCHEMA_TYPE
068: OBJVALUE, // EXTERNAL_REFERENCE_TYPE
069: OBJVALUE, // RECORD_TYPE
070: OBJVALUE, // UNION_TYPE
071: OBJVALUE, // ENUMERATION_TYPE
072: OBJVALUE, // ARRAY_TYPE
073: OBJVALUE // LIST_TYPE
074: };
075: private static final Comparator propertyDescriptorNameComparator;
076:
077: private final StringBuffer _from = new StringBuffer();
078: private final StringWriter _out = new StringWriter();
079: private final StringBuffer _where = _out.getBuffer();
080: private final String _pkField;
081: private final int _ownerType;
082: private final PropertyDescriptor[] _sysAttrs;
083: private final WorkflowProcess _workflow;
084: private Map _attrRefs;
085: private List _parms;
086: private List _parmTypes;
087: private boolean _appendingFrom;
088: private boolean _appendingWhere;
089: private boolean _appendingOrderBy;
090: private StringBuffer _orderBy;
091: private int _maxRows;
092:
093: static {
094: propertyDescriptorNameComparator = new Comparator() {
095: public int compare(Object o1, Object o2) {
096: String lhs = o1 instanceof PropertyDescriptor ? ((FeatureDescriptor) o1)
097: .getName()
098: : (String) o1;
099: String rhs = o2 instanceof PropertyDescriptor ? ((FeatureDescriptor) o2)
100: .getName()
101: : (String) o2;
102: return lhs.compareTo(rhs);
103: }
104: };
105: _wapiToJdbc.put(BOOLEAN_TYPE, new Integer(Types.BIT));
106: _wapiToJdbc.put(DATETIME_TYPE, new Integer(Types.TIMESTAMP));
107: _wapiToJdbc.put(FLOAT_TYPE, new Integer(Types.DOUBLE));
108: _wapiToJdbc.put(INTEGER_TYPE, new Integer(Types.INTEGER));
109: _wapiToJdbc.put(SCHEMA_TYPE, new Integer(Types.BLOB));
110: _wapiToJdbc.put(STRING_TYPE, new Integer(Types.VARCHAR));
111: for (int i = 0; i < _integers.length; i++)
112: _integers[i] = new Integer(i);
113: }
114:
115: public QueryBuilder(String sql, String pkField, int ownerType,
116: PropertyDescriptor[] sysAttrs, WorkflowProcess workflow) {
117:
118: _from.append(sql);
119: _pkField = pkField;
120: _ownerType = ownerType;
121: _sysAttrs = sysAttrs;
122: _workflow = workflow;
123: }
124:
125: // Adds another table to the FROM clause.
126: public void addTable(String table, String alias) {
127: if (_appendingFrom)
128: _from.append(", ");
129: _from.append(table).append(' ').append(alias);
130: _appendingFrom = true;
131: }
132:
133: // Adds a simple query term involving a system or custom attribute;
134: // returns the attribute (not parameter) index.
135: public void addQueryTerm(String attrName, int attrType,
136: String comparison, Object value) {
137:
138: if (!_appendingFrom) {
139: _from.append(" T");
140: _appendingFrom = true;
141: }
142: _where.append(_appendingWhere ? " AND " : " WHERE ");
143: if (isSystemAttribute(attrName)) {
144: _where.append("T.").append(attrName.toUpperCase()).append(
145: comparison).append('?');
146: } else {
147: String attrPrefix = addCustomAttributeRef(attrName);
148: _where.append(attrPrefix).append('.').append(
149: _columnNames[attrType]).append(comparison).append(
150: '?');
151: }
152: addParameter(value, attrType);
153: _appendingWhere = true;
154: }
155:
156: public void addQueryTerm(String sql) throws RepositoryException {
157: if (!_appendingFrom) {
158: _from.append(" T");
159: _appendingFrom = true;
160: }
161: _where.append(_appendingWhere ? " AND (" : " WHERE (");
162:
163: // Parse the filter string.
164: SQLParser parser = new SQLParser(new StringReader(sql));
165: SQLOrExpr root;
166: try {
167: root = parser.SQLOrExpr();
168: } catch (ParseException e) {
169: throw new RepositoryException(e);
170: }
171: if (_logger.isDebugEnabled()) {
172: StringWriter out = new StringWriter();
173: try {
174: root.write(out);
175: } catch (IOException e) {
176: throw new RepositoryException(e);
177: }
178: _logger.debug("filter parsed to: " + out.toString());
179: }
180:
181: // Convert all literal values into SQL parameter narkers.
182: root.jjtAccept(new SQLParserVisitor() {
183: public Object visit(SimpleNode node, Object data) {
184: switch (node.getId()) {
185: case SQLParserTreeConstants.JJTSQLLVALUETERM:
186: SQLLvalueTerm term = (SQLLvalueTerm) node;
187: List path = term.getPath();
188: String attrName = (String) path.get(0);
189: if (isSystemAttribute(attrName)) {
190: // Use the appropriate alias for system attributes.
191: path.add(0, "T");
192: path.set(1, attrName.toUpperCase());
193: } else {
194: // Replace a reference to a custom attribute with an
195: // external reference to the appropriate column in the
196: // corresponding joined table. We'll create one join
197: // clause per unique custom attribute reference.
198: String prefix = addCustomAttributeRef(attrName);
199:
200: // Determine the data type of this attribute
201: // (defaulting to string).
202: DataType dataType;
203: try {
204: dataType = _workflow == null ? null
205: : WorkflowEngineUtilities
206: .findDataType(_workflow,
207: attrName, false);
208: } catch (WMInvalidAttributeException e) {
209: throw new OBERuntimeException(e);
210: }
211: int attrType = dataType == null ? Type.STRING_TYPE
212: : dataType.getType().value();
213:
214: // Change this node to refer to the appropriate
215: // column in the joined table.
216: path.clear();
217: path.add(prefix);
218: path.add(_columnNames[attrType]);
219: }
220: break;
221: case SQLParserTreeConstants.JJTSQLLITERAL:
222: // Replace a literal value with a parameter marker.
223: Object value = node.execute(null);
224: addParameter(value, DataTypes.typeForClass(value
225: .getClass()));
226: node.setParameter();
227: break;
228: }
229: return node.childrenAccept(this , data);
230: }
231: }, null);
232:
233: // Regenerate the SQL expression, which now contains parameter markers.
234: try {
235: root.write(_out);
236: } catch (IOException e) {
237: throw new RepositoryException(e);
238: }
239: _where.append(')');
240: _appendingWhere = true;
241: }
242:
243: public void addOrderBy(String attrName) {
244: if (_orderBy == null)
245: _orderBy = new StringBuffer(" ORDER BY ");
246: if (_appendingOrderBy)
247: _orderBy.append(", ");
248: _orderBy.append(attrName);
249: _appendingOrderBy = true;
250: }
251:
252: public void setMaxRows(int maxRows) {
253: _maxRows = maxRows;
254: }
255:
256: public PreparedStatement prepareStatement(Connection con)
257: throws SQLException {
258:
259: _from.append(_where.toString());
260: if (_orderBy != null)
261: _from.append(_orderBy.toString());
262: String sql = _from.toString();
263:
264: if (_logger.isDebugEnabled()) {
265: _logger.debug("sql: " + sql);
266: _logger.debug("parameters: " + _parms);
267: }
268:
269: PreparedStatement stmt = con.prepareStatement(sql);
270: if (_parms != null) {
271: for (int i = 0, pnum = 1; i < _parms.size(); i++, pnum++) {
272: Object parm = _parms.get(i);
273: Integer parmType = (Integer) _parmTypes.get(i);
274: if (parm == null) {
275: stmt.setNull(pnum, ((Integer) _wapiToJdbc
276: .get(parmType)).intValue());
277: } else {
278: switch (parmType.intValue()) {
279: case Type.BOOLEAN_TYPE:
280: stmt.setBoolean(pnum, ((Boolean) parm)
281: .booleanValue());
282: break;
283: case Type.DATETIME_TYPE:
284: Date date = (Date) parm;
285: if (!(parm instanceof Timestamp))
286: date = new Timestamp(date.getTime());
287: stmt.setTimestamp(pnum, (Timestamp) date);
288: break;
289: case Type.FLOAT_TYPE:
290: stmt.setDouble(pnum, ((Number) parm)
291: .doubleValue());
292: break;
293: case Type.INTEGER_TYPE:
294: stmt.setInt(pnum, ((Number) parm).intValue());
295: break;
296: case Type.SCHEMA_TYPE:
297: stmt.setObject(pnum, parm);
298: break;
299: case Type.STRING_TYPE:
300: stmt.setString(pnum, (String) parm);
301: break;
302: }
303: }
304: }
305: }
306: if (_maxRows > 0)
307: stmt.setMaxRows(_maxRows);
308:
309: return stmt;
310: }
311:
312: private boolean isSystemAttribute(String attrName) {
313: return _sysAttrs == null
314: || Arrays.binarySearch(_sysAttrs, attrName,
315: propertyDescriptorNameComparator) > -1;
316: }
317:
318: // Adds a custom attribute reference and returns the attribute alias.
319: private String addCustomAttributeRef(String attrName) {
320: if (_attrRefs == null)
321: _attrRefs = new HashMap();
322: String alias = (String) _attrRefs.get(attrName);
323: if (alias == null) {
324: int index = _attrRefs.size();
325: alias = "A" + index;
326: _attrRefs.put(attrName, alias);
327: addTable("OBEATTRIBUTEINSTANCE", alias);
328: _where.append("T.PROCESSINSTANCEID=").append(alias).append(
329: ".PROCESSINSTANCEID AND T.").append(_pkField)
330: .append('=').append(alias).append(".OWNERID AND ")
331: .append(alias).append(".OWNERTYPE=?").append(
332: " AND ").append(alias).append(
333: ".NAME=? AND ");
334: addParameter(_integers[_ownerType], Type.INTEGER_TYPE);
335: addParameter(attrName, Type.STRING_TYPE);
336: _appendingWhere = true;
337: }
338: return alias;
339: }
340:
341: // Adds a parameter, returns the JDBC parameter index.
342: private void addParameter(Object value, int parmType) {
343: if (_parms == null) {
344: _parms = new ArrayList();
345: _parmTypes = new ArrayList();
346: }
347: _parms.add(value);
348: _parmTypes.add(_integers[parmType]);
349: }
350: }
|