001: //** Copyright Statement ***************************************************
002: //The Salmon Open Framework for Internet Applications (SOFIA)
003: // Copyright (C) 1999 - 2002, Salmon LLC
004: //
005: // This program is free software; you can redistribute it and/or
006: // modify it under the terms of the GNU General Public License version 2
007: // as published by the Free Software Foundation;
008: //
009: // This program is distributed in the hope that it will be useful,
010: // but WITHOUT ANY WARRANTY; without even the implied warranty of
011: // MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
012: // GNU General Public License for more details.
013: //
014: // You should have received a copy of the GNU General Public License
015: // along with this program; if not, write to the Free Software
016: // Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA.
017: //
018: // For more information please visit http://www.salmonllc.com
019: //** End Copyright Statement ***************************************************
020: package com.salmonllc.sql;
021:
022: import java.io.Serializable;
023: import java.sql.SQLException;
024: import java.util.ArrayList;
025: import java.util.HashMap;
026: import java.util.StringTokenizer;
027: import java.util.Vector;
028:
029: /**
030: * This class is used to to build Query By Example filters for datastore retrieves. Selection criteria buckets are added to the datastore using the add criteria method. These buckets can be bound to GUI components which allow the user to enter selection criteria into the builder. Then the builder combines the entered criteria with bucket definition to generate a SQL filter or DataStoreExpression filter to filter the data.
031: */
032: public class QBEBuilder extends DataStoreBuffer {
033: public static final int CRITERIA_TYPE_EQUALS_IGNORE_CASE = 101;
034: public static final int CRITERIA_TYPE_STARTS_WITH = 102;
035: public static final int CRITERIA_TYPE_STARTS_WITH_IGNORE_CASE = 103;
036: public static final int CRITERIA_TYPE_CONTAINS = 104;
037: public static final int CRITERIA_TYPE_CONTAINS_IGNORE_CASE = 105;
038:
039: static final int CRITERIA_STRING_ONLY = 100;
040:
041: public static final int CRITERIA_TYPE_LTE = 1;
042: public static final int CRITERIA_TYPE_LT = 2;
043: public static final int CRITERIA_TYPE_GTE = 3;
044: public static final int CRITERIA_TYPE_GT = 4;
045: public static final int CRITERIA_TYPE_EQUALS = 5;
046: public static final int CRITERIA_TYPE_NOT_EQUALS = 6;
047: public static final int CRITERIA_TYPE_IN = 7;
048: public static final int CRITERIA_TYPE_CUSTOM = 9;
049: public static final int CRITERIA_TYPE_COMPLEX = 99;
050: public String _stopWords[] = null;
051:
052: public class CriteriaElement implements Serializable {
053: public String bucketName;
054: public int type;
055: public String columnList;
056: }
057:
058: private ArrayList _criteriaElements = new ArrayList();
059: private boolean _useOr = false;
060: private Vector _qbeListeners;
061: HashMap _tables = null;
062: DataStoreQBEInterface _lastDSI;
063:
064: /**
065: * Adds a selection criteria bucket to the builder
066: * @param bucketName The name of the bucket that will hold the user entered selection criteria
067: * @param criteriaType The type of criteria to use with this bucket. Valid values are one of the CRITERIA_TYPE constants described in this class.
068: * @param columnList A comma separated list of database column names (for SQL filters) or datatable internal names (for datastore filters). Each column in the list will be compared with the filter value in the generated filter. Wild cards are allowed including: *: all columns in all tables in the DataStore or for datastore filters, all columns in the datastore and tablename.* : all columns in the specified table
069: */
070: public void addCriteria(String bucketName, int criteriaType,
071: String columnList) {
072: CriteriaElement ele = new CriteriaElement();
073: ele.bucketName = bucketName;
074: ele.type = criteriaType;
075: ele.columnList = columnList;
076: _criteriaElements.add(ele);
077: addBucket(bucketName, DATATYPE_STRING);
078: _tables = null;
079: reset();
080: }
081:
082: /**
083: * Blanks out all criteria in the builder
084: */
085: public void reset() {
086: super .reset();
087: super .insertRow();
088: }
089:
090: /**
091: * @returns true if criteria elements should be combined with or instead of and
092: */
093: public boolean getUseOr() {
094: return _useOr;
095: }
096:
097: /**
098: * @set to true if criteria elements should be combined with or instead of and
099: */
100: public void setUseOr(boolean b) {
101: _useOr = b;
102: }
103:
104: /**
105: * Builds a datastore filter expression by combining the selection criteria rules with the values in the DataStore and then runs the filter on the DataStore.
106: * @param dsb The DataStore to run the filter against.
107: */
108: public void filter(DataStoreBuffer dsb) throws DataStoreException {
109: String st = generateDataStoreFilter(dsb);
110: dsb.filter(st);
111: }
112:
113: /**
114: * Builds a datastore SQL where clause by combining the selection criteria rules with the values in the DataStore and then uses that to retreive the data into the passed DataStore
115: * @param dsb The DataStore to load the data for.(Note: this method takes a DataStoreBuffer so subclasses can override it for different types of DataStoreBuffer. This implementation will only work with a DataStore or ProxyDataStore).
116: */
117: public void retrieve(DataStoreBuffer dsb) throws SQLException,
118: DataStoreException {
119: if (!(dsb instanceof DataStoreQBEInterface))
120: return;
121: DataStoreQBEInterface dsi = (DataStoreQBEInterface) dsb;
122: String st = generateSQLFilter(dsb);
123: if (st != null && st.length() == 0)
124: st = null;
125: dsi.retrieve(st);
126: }
127:
128: /**
129: * Estimates the number of rows to retrieve for the passed datastore using the criteria and rules in the component
130: * @param dsb The DataStore to estimate the retrieve for.(Note: this method takes a DataStoreBuffer so subclasses can override it for different types of DataStoreBuffer. This implementation will only work with a DataStore or ProxyDataStore).
131: */
132: public int estimateRowsRetrieved(DataStoreBuffer dsb)
133: throws Exception {
134: if (!(dsb instanceof DataStoreQBEInterface))
135: return 0;
136: DataStoreQBEInterface dsi = (DataStoreQBEInterface) dsb;
137: String st = generateSQLFilter(dsb);
138: return dsi.estimateRowsRetrieved(st);
139: }
140:
141: /**
142: * Builds a datastore filter expression string by combining the selection criteria rules with the values in the DataStore
143: * @param dsb The DataStore to run the filter against.
144: */
145: public String generateDataStoreFilter(DataStoreBuffer dsb) {
146: StringBuffer filter = new StringBuffer(255);
147: String eleFilter = null;
148: for (int i = 0; i < _criteriaElements.size(); i++) {
149: String criteriaEntered = null;
150: CriteriaElement ele = (CriteriaElement) _criteriaElements
151: .get(i);
152: try {
153: criteriaEntered = getString(ele.bucketName);
154: String cols[] = getDataStoreColumnNamesFromList(dsb,
155: ele.columnList);
156: eleFilter = QBECriteriaBuilder.buildFilter(dsb,
157: criteriaEntered, ele.type, cols, _stopWords);
158:
159: } catch (DataStoreException e) {
160: }
161: if (eleFilter != null) {
162: if (filter.length() > 0)
163: filter.append(_useOr ? " || " : " && ");
164: filter.append("(");
165: filter.append(eleFilter);
166: filter.append(")");
167: }
168: }
169: String ret = null;
170: if (filter.length() == 0 || filter.toString().equals("()"))
171: ret = null;
172: else
173: ret = filter.toString();
174:
175: QBEEvent evt = new QBEEvent(this , QBEEvent.TYPE_FILTER_PREVIEW,
176: ret);
177: notifyListeners(evt);
178: return evt.getFilter();
179: }
180:
181: /**
182: * Builds a SQL where clause string by combining the selection criteria rules with the values in the DataStoreBuffer.
183: * @param dsb The DataStoreBuffer to run the filter against (Note: this method takes a DataStoreBuffer so subclasses can override it for different types of DataStore. This implementation will only work with a DataStore or ProxyDataStore).
184: */
185: public String generateSQLFilter(DataStoreBuffer dsb) {
186: if (!(dsb instanceof DataStoreQBEInterface))
187: return null;
188: DataStoreQBEInterface dsi = (DataStoreQBEInterface) dsb;
189:
190: StringBuffer filter = new StringBuffer(255);
191: String eleFilter = null;
192: for (int i = 0; i < _criteriaElements.size(); i++) {
193: String criteriaEntered = null;
194: CriteriaElement ele = (CriteriaElement) _criteriaElements
195: .get(i);
196: try {
197: criteriaEntered = getString(ele.bucketName);
198: ColumnDefinition cols[] = getTableColumnNamesFromList(
199: dsi, ele.columnList);
200: eleFilter = QBECriteriaBuilder.buildSQL(dsi,
201: criteriaEntered, ele.type, cols, _stopWords);
202:
203: } catch (DataStoreException e) {
204: }
205: if (eleFilter != null && eleFilter.length() > 0) {
206: if (filter.length() > 0)
207: filter.append(_useOr ? " or " : " and ");
208: filter.append("(");
209: filter.append(eleFilter);
210: filter.append(")");
211: }
212: }
213:
214: String ret = null;
215: if (filter.length() == 0 || filter.toString().equals("()"))
216: ret = null;
217: else
218: ret = filter.toString();
219:
220: QBEEvent evt = new QBEEvent(this , QBEEvent.TYPE_SQL_PREVIEW,
221: ret);
222: notifyListeners(evt);
223: return evt.getFilter();
224: }
225:
226: private ColumnDefinition[] getTableColumnNamesFromList(
227: DataStoreQBEInterface dsi, String colList)
228: throws DataStoreException {
229: HashMap columns = new HashMap();
230: if (_tables == null)
231: _tables = new HashMap();
232: else {
233: if (_lastDSI != null) {
234: if (_lastDSI != dsi)
235: _tables.clear();
236: else if (dsi.getAliasCount() != _lastDSI
237: .getAliasCount())
238: _tables.clear();
239: }
240: }
241: _lastDSI = dsi;
242: StringTokenizer st = new StringTokenizer(colList, ",");
243: while (st.hasMoreTokens()) {
244: String tok = st.nextToken().trim();
245: int dotPos = tok.lastIndexOf(".");
246: if (dotPos == -1) {
247: if (tok.equals("*")) {
248: int count = dsi.getColumnCount();
249: for (int i = 0; i < count; i++) {
250: String tableName = dsi.getColumnTableName(i);
251: if (tableName == null)
252: continue;
253:
254: if (!_tables.containsKey(tableName)) {
255: ColumnDefinition[] c = getColumnsForTable(
256: dsi, tableName);
257: _tables.put(tableName, c);
258: }
259:
260: ColumnDefinition[] c = (ColumnDefinition[]) _tables
261: .get(tableName);
262: for (int j = 0; j < c.length; j++) {
263: String key = c[j].getTableName() + "."
264: + c[j].getColumnName();
265: columns.put(key, c[j]);
266: }
267: }
268: } else {
269: int ndx = dsi.getColumnIndex(tok);
270: if (ndx != -1) {
271: String tableName = dsi.getColumnTableName(ndx);
272: String databaseName = dsi
273: .getColumnDatabaseName(ndx);
274: String columnName = dsi
275: .getColumnDatabaseName(ndx);
276: if (tableName != null && databaseName != null) {
277: if (!_tables.containsKey(tableName))
278: _tables.put(tableName,
279: getColumnsForTable(dsi,
280: tableName));
281: ColumnDefinition[] c = (ColumnDefinition[]) _tables
282: .get(tableName);
283: for (int i = 0; i < c.length; i++) {
284: if (c[i].getColumnName().equals(
285: columnName))
286: columns.put(databaseName, c[i]);
287: }
288: }
289: }
290: }
291: } else {
292: String tableName = tok.substring(0, dotPos);
293: String columnName = tok.substring(dotPos + 1);
294:
295: if (!_tables.containsKey(tableName))
296: _tables.put(tableName, getColumnsForTable(dsi,
297: tableName));
298:
299: ColumnDefinition[] c = (ColumnDefinition[]) _tables
300: .get(tableName);
301: if (columnName.equals("*")) {
302: for (int i = 0; i < c.length; i++) {
303: String key = c[i].getTableName() + "."
304: + c[i].getColumnName();
305: columns.put(key, c[i]);
306: }
307: } else {
308: for (int i = 0; i < c.length; i++) {
309: if (c[i].getColumnName().equalsIgnoreCase(
310: columnName))
311: columns.put(c[i].getTableName() + "."
312: + c[i].getColumnName(), c[i]);
313: }
314: }
315: }
316: }
317:
318: ColumnDefinition ret[] = new ColumnDefinition[columns.size()];
319: columns.values().toArray(ret);
320: return ret;
321: }
322:
323: private ColumnDefinition[] getColumnsForTable(
324: DataStoreQBEInterface dsi, String table)
325: throws DataStoreException {
326: String realTable = getTableFromAlias(dsi, table);
327: ColumnDefinition ret[] = dsi.getColumnsForTable(realTable);
328: if (ret != null && !realTable.equals(table)) {
329: for (int i = 0; i < ret.length; i++)
330: ret[i].setTableName(table);
331: }
332: return ret;
333: }
334:
335: private String getTableFromAlias(DataStoreQBEInterface dsi,
336: String alias) throws DataStoreException {
337: for (int i = 0; i < dsi.getAliasCount(); i++) {
338: if (dsi.getAlias(i) != null
339: && dsi.getAlias(i).equals(alias))
340: return dsi.getTable(i);
341: }
342: return alias;
343: }
344:
345: private String[] getDataStoreColumnNamesFromList(
346: DataStoreBuffer dsb, String colList) {
347: ArrayList l = new ArrayList();
348: StringTokenizer tok = new StringTokenizer(colList, ",");
349: while (tok.hasMoreTokens()) {
350: String ele = tok.nextToken().trim();
351: if (ele.equals("*"))
352: return dsb.getColumnList();
353: else if (dsb.getColumnIndex(ele) != -1)
354: l.add(ele);
355: else {
356: int pos = ele.indexOf(".*");
357: if (pos > -1) {
358: String table = ele.substring(0, pos);
359: DSDataStoreDescriptor desc = getDescriptor();
360: for (int i = 0; i < desc.getColumnCount(); i++) {
361: DSColumnDescriptor col = desc.getColumn(i);
362: if (col.getTable() != null
363: && col.getTable().equals(table))
364: try {
365: l.add(getColumnName(i));
366: } catch (DataStoreException e) {
367: }
368: }
369: }
370: }
371: }
372:
373: String ret[] = new String[l.size()];
374: l.toArray(ret);
375: return ret;
376:
377: }
378:
379: /**
380: * Returns an array of all the criteria elements in the QBEBuilder
381: */
382: public CriteriaElement[] getCriteriaElements() {
383: CriteriaElement el[] = new CriteriaElement[_criteriaElements
384: .size()];
385: _criteriaElements.toArray(el);
386: return el;
387: }
388:
389: /**
390: * Adds a new listerner to this QBEBuilder that will be notified whenever a SQL or DataStore filter is generated
391: */
392: public void addQBEListener(QBEListener l) {
393: if (_qbeListeners == null)
394: _qbeListeners = new Vector();
395:
396: for (int i = 0; i < _qbeListeners.size(); i++) {
397: if (((ModelChangedListener) _qbeListeners.elementAt(i)) == l)
398: return;
399: }
400:
401: _qbeListeners.addElement(l);
402: }
403:
404: /**
405: * This method removes a listener from the list of listeners that will be notified when a model changed event is fired.
406: */
407: public void removeQBEListener(QBEListener l) {
408: if (_qbeListeners == null)
409: return;
410:
411: for (int i = 0; i < _qbeListeners.size(); i++) {
412: if (((QBEListener) _qbeListeners.elementAt(i)) == l) {
413: _qbeListeners.removeElementAt(i);
414: return;
415: }
416: }
417: }
418:
419: /**
420: * Notifies all listeners that a model changed event occurred
421: */
422: public void notifyListeners(QBEEvent e) {
423: if (_qbeListeners == null)
424: return;
425:
426: for (int i = 0; i < _qbeListeners.size(); i++) {
427: ((QBEListener) _qbeListeners.elementAt(i)).QBEPreview(e);
428: }
429: }
430:
431: /**
432: * @return The list of stop words used for building complex criteria. Stop words are words that are ignored when buildinng the criteria. Set to null to use the default list or an empty array to not use stop words.
433: */
434: public String[] getStopWords() {
435: return _stopWords;
436: }
437:
438: /**
439: * Sets the list of stop words used for building complex criteria. Stop words are words that are ignored when building the criteria. Set to null to use the default list or an empty array to not use stop words.
440: */
441: public void setStopWords(String[] stopWords) {
442: _stopWords = stopWords;
443: }
444:
445: }
|