001: /*
002: * tinySQLWhere - Class to handle all Where clause processing.
003: *
004: * $Author: davis $
005: * $Date: 2004/12/18 21:24:13 $
006: * $Revision: 1.1 $
007: *
008: * This library is free software; you can redistribute it and/or
009: * modify it under the terms of the GNU Lesser General Public
010: * License as published by the Free Software Foundation; either
011: * version 2.1 of the License, or (at your option) any later version.
012: *
013: * This library is distributed in the hope that it will be useful,
014: * but WITHOUT ANY WARRANTY; without even the implied warranty of
015: * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
016: * Lesser General Public License for more details.
017: *
018: * You should have received a copy of the GNU Lesser General Public
019: * License along with this library; if not, write to the Free Software
020: * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
021: *
022: * Revision History:
023: *
024: * Written by Davis Swan in May, 2004.
025: */
026:
027: package com.sqlmagic.tinysql;
028:
029: import java.util.*;
030: import java.lang.*;
031: import java.io.*;
032: import java.sql.SQLException;
033: import java.sql.Types;
034:
035: public class tinySQLWhere {
036: Vector whereClauseList;
037:
038: /*
039: * The constructor builds a Where clause object from the input string.
040: */
041: public tinySQLWhere(String whereString, Hashtable tableDefs)
042: throws tinySQLException {
043: FieldTokenizer ft;
044: Vector whereConditions;
045: tsColumn leftColumn, rightColumn;
046: Object whereObj;
047: StringBuffer fieldBuffer;
048: String nextField, upperField, wherePhrase, comp, left, right, andOr, lastWord;
049: Vector whereCondition;
050: String[] comparisons = { "<=", "=<", ">=", "=>", "=", "<>",
051: "!=", ">", "<", "LIKE", "NOT LIKE", "IS" };
052: String[] fields, keepFields;
053: boolean inBrackets = false, foundFunction = false;
054: int i, j, foundKeyWord, foundComp, startAt, foundAnd, foundOr, keepCount;
055: /*
056: * The whereClauseList is a Vector containing pointers to whereCondition
057: * Vectors or tinySQLWhere objects.
058: */
059: whereConditions = new Vector();
060: whereClauseList = new Vector();
061: /*
062: * Identify any phrases that are contained within brackets. Note that
063: * the FieldTokenizer will catch function definitions as well as
064: * subPhrases so there has to be additional logic to reconstruct
065: * the functions.
066: */
067: ft = new FieldTokenizer(whereString, '(', true);
068: fields = ft.getFields();
069: keepFields = new String[fields.length];
070: lastWord = "NULL";
071: fieldBuffer = new StringBuffer();
072: foundFunction = false;
073: keepCount = 0;
074: for (i = 0; i < fields.length; i++) {
075: keepFields[i] = "";
076: if (fields[i].equals("(")) {
077: /*
078: * If this is a known function reconstruct the function definition
079: * and save the entire string.
080: */
081: foundFunction = Utils.isFunctionName(lastWord);
082: if (foundFunction) {
083: fieldBuffer.append("(");
084: } else {
085: if (fieldBuffer.length() > 0) {
086:
087: keepFields[keepCount] = fieldBuffer.toString();
088: keepCount++;
089: fieldBuffer.delete(0, fieldBuffer.length());
090: }
091: keepFields[keepCount] = "(";
092: keepCount++;
093: }
094: } else if (fields[i].equals(")")) {
095: if (foundFunction) {
096: fieldBuffer.append(") ");
097: foundFunction = false;
098: } else {
099: if (fieldBuffer.length() > 0) {
100: keepFields[keepCount] = fieldBuffer.toString();
101: keepCount++;
102: fieldBuffer.delete(0, fieldBuffer.length());
103: }
104: keepFields[keepCount] = ")";
105: keepCount++;
106: }
107: } else {
108: fieldBuffer.append(fields[i]);
109: }
110: lastWord = fields[i]
111: .substring(fields[i].lastIndexOf(" ") + 1);
112: }
113: /*
114: * Keep last subPhrase
115: */
116: if (fieldBuffer.length() > 0) {
117: keepFields[keepCount] = fieldBuffer.toString();
118: keepCount++;
119: }
120: for (i = 0; i < keepCount; i++) {
121: if (tinySQLGlobals.WHERE_DEBUG)
122: System.out.println("keepFields[" + i + "]="
123: + keepFields[i]);
124: nextField = keepFields[i];
125: upperField = nextField.toUpperCase();
126: if (nextField.equals("(")) {
127: whereObj = (Object) null;
128: inBrackets = true;
129: } else if (nextField.equals(")")) {
130: inBrackets = false;
131: whereObj = (Object) null;
132: } else if (inBrackets) {
133: whereObj = new tinySQLWhere(nextField, tableDefs);
134: whereConditions.addElement(whereObj);
135: } else {
136: /*
137: * Look for AND/OR keywords - if none are found process the
138: * entire string.
139: */
140: andOr = "AND";
141: startAt = 0;
142: while (startAt < upperField.length()) {
143: if (upperField.startsWith("AND ")) {
144: foundAnd = 0;
145: } else {
146: foundAnd = upperField.indexOf(" AND", startAt);
147: /*
148: * Make sure this is not just part of a longer string.
149: */
150: if (foundAnd > -1
151: & foundAnd < upperField.length() - 4)
152: if (upperField.charAt(foundAnd + 4) != ' ')
153: foundAnd = -1;
154: }
155: if (upperField.startsWith("OR ")) {
156: foundOr = 0;
157: } else {
158: foundOr = upperField.indexOf(" OR", startAt);
159: if (foundOr > -1
160: & foundOr < upperField.length() - 3)
161: if (upperField.charAt(foundOr + 3) != ' ')
162: foundOr = -1;
163: }
164: foundKeyWord = upperField.length();
165: if (foundAnd > -1)
166: foundKeyWord = foundAnd;
167: if (foundOr > -1 & foundOr < foundKeyWord) {
168: foundKeyWord = foundOr;
169: andOr = "OR";
170: }
171: if (foundKeyWord == 0) {
172: startAt = andOr.length() + 1;
173: foundKeyWord = upperField.length();
174: }
175: wherePhrase = nextField.substring(startAt,
176: foundKeyWord);
177: if (tinySQLGlobals.WHERE_DEBUG)
178: System.out.println("Where phrase is "
179: + wherePhrase);
180: if (foundKeyWord < upperField.length() - 4)
181: andOr = upperField.substring(foundKeyWord + 1,
182: foundKeyWord + 3);
183: /*
184: * Build a whereCondition Vector. The elements are
185: * as follows:
186: * 0 - left column object
187: * 1 - comparison
188: * 2 - right column object
189: * 3 - status
190: *
191: * The status values indicate which parts of the where
192: * condition have been set.
193: */
194: whereCondition = new Vector();
195: for (j = 0; j < comparisons.length; j++) {
196: comp = comparisons[j];
197: foundComp = wherePhrase.toUpperCase().indexOf(
198: comp);
199: if (foundComp > -1) {
200: left = wherePhrase.substring(0, foundComp)
201: .trim();
202: leftColumn = new tsColumn(left, tableDefs,
203: "WHERE");
204: whereCondition.addElement(leftColumn);
205: whereCondition.addElement(comp);
206: right = wherePhrase.substring(
207: foundComp + comp.length()).trim();
208: if (comp.equals("IS"))
209: right = "'" + right.toUpperCase() + "'";
210: rightColumn = new tsColumn(right,
211: tableDefs, "WHERE");
212: whereCondition.addElement(rightColumn);
213: if (leftColumn.isConstant
214: & rightColumn.isConstant)
215: whereCondition.addElement("BOTH");
216: else if (leftColumn.isConstant)
217: whereCondition.addElement("LEFT");
218: else if (rightColumn.isConstant)
219: whereCondition.addElement("RIGHT");
220: else
221: whereCondition.addElement("UNKNOWN");
222: break;
223: }
224: }
225: whereConditions.addElement(whereCondition);
226: /*
227: * If this condition and the previous one are joined by an
228: * AND keyword, add the condition to the existing Vector.
229: * For an OR keyword, create a new entry in the whereClauseList.
230: */
231: if (andOr.equals("OR")) {
232: whereClauseList.addElement(whereConditions);
233: whereConditions = new Vector();
234: }
235: startAt = foundKeyWord + andOr.length() + 2;
236: }
237: }
238: }
239: /*
240: * Add the last where condition to the list.
241: */
242: if (whereConditions.size() > 0)
243: whereClauseList.addElement(whereConditions);
244: if (tinySQLGlobals.WHERE_DEBUG)
245: System.out.println("Where clause is \n" + toString());
246: }
247:
248: /*
249: * This method returns the column to build an index on. This is very
250: * primitive and only works on a single column that is compared to
251: * to a constant.
252: */
253: public Vector getIndexCondition(String inputTableName) {
254: int i, j;
255: Vector whereConditions;
256: tsColumn leftColumn, rightColumn;
257: Object whereObj;
258: String objectType, columnName, tableName, comparison;
259: Vector whereCondition;
260: StringBuffer outputBuffer = new StringBuffer();
261: for (i = 0; i < whereClauseList.size(); i++) {
262: whereConditions = (Vector) whereClauseList.elementAt(i);
263: for (j = 0; j < whereConditions.size(); j++) {
264: /*
265: * Where conditions can be tinySQLWhere objects or String arrays.
266: */
267: whereObj = whereConditions.elementAt(j);
268: objectType = whereObj.getClass().getName();
269: if (objectType.endsWith("java.util.Vector")) {
270: whereCondition = (Vector) whereObj;
271: leftColumn = (tsColumn) whereCondition.elementAt(0);
272: comparison = (String) whereCondition.elementAt(1);
273: rightColumn = (tsColumn) whereCondition
274: .elementAt(2);
275: if (leftColumn.tableName.equals(inputTableName)
276: & rightColumn.isConstant
277: & comparison.equals("=")) {
278: return whereCondition;
279: } else if (leftColumn.tableName
280: .equals(inputTableName)
281: & rightColumn.isConstant
282: & comparison.equals("=")) {
283: return whereCondition;
284: }
285: }
286: }
287: }
288: return (Vector) null;
289: }
290:
291: /*
292: * Clear all the non-constant values in all where conditions
293: */
294: public void clearValues(String inputTableName) {
295: int i, j;
296: Vector whereConditions;
297: tsColumn leftColumn, rightColumn;
298: Object whereObj;
299: String objectType, columnName, tableName, status;
300: Vector whereCondition;
301: StringBuffer outputBuffer = new StringBuffer();
302: for (i = 0; i < whereClauseList.size(); i++) {
303: whereConditions = (Vector) whereClauseList.elementAt(i);
304: for (j = 0; j < whereConditions.size(); j++) {
305: /*
306: * Where conditions can be tinySQLWhere objects or String arrays.
307: */
308: whereObj = whereConditions.elementAt(j);
309: objectType = whereObj.getClass().getName();
310: if (objectType.endsWith("tinySQLWhere")) {
311: ((tinySQLWhere) whereObj)
312: .clearValues(inputTableName);
313: } else if (objectType.endsWith("java.util.Vector")) {
314: whereCondition = (Vector) whereObj;
315: status = (String) whereCondition.elementAt(3);
316: if (status.equals("UNKNOWN"))
317: continue;
318: /*
319: * Check left side of condition
320: */
321: leftColumn = (tsColumn) whereCondition.elementAt(0);
322: if (leftColumn.clear(inputTableName)) {
323: if (status.equals("LEFT"))
324: whereCondition.setElementAt("UNKNOWN", 3);
325: else
326: whereCondition.setElementAt("RIGHT", 3);
327: }
328: /*
329: * Check right side of condition
330: */
331: rightColumn = (tsColumn) whereCondition
332: .elementAt(2);
333: if (rightColumn.clear(inputTableName)) {
334: if (status.equals("RIGHT"))
335: whereCondition.setElementAt("UNKNOWN", 3);
336: else
337: whereCondition.setElementAt("LEFT", 3);
338: }
339: if (tinySQLGlobals.WHERE_DEBUG)
340: System.out.println("Where condition " + j
341: + " after clearing table "
342: + inputTableName + " is\n"
343: + conditionToString(whereCondition));
344: }
345: }
346: }
347: }
348:
349: public String toString() {
350: int i, j;
351: Vector whereConditions;
352: Object whereObj;
353: String objectType;
354: Vector whereCondition;
355: StringBuffer outputBuffer = new StringBuffer();
356: for (i = 0; i < whereClauseList.size(); i++) {
357: if (i > 0)
358: outputBuffer.append("OR\n");
359: whereConditions = (Vector) whereClauseList.elementAt(i);
360: for (j = 0; j < whereConditions.size(); j++) {
361: if (j > 0)
362: outputBuffer.append("AND\n");
363: /*
364: * Where conditions can be tinySQLWhere objects or String arrays.
365: */
366: whereObj = whereConditions.elementAt(j);
367: objectType = whereObj.getClass().getName();
368: if (objectType.endsWith("tinySQLWhere")) {
369: outputBuffer.append(((tinySQLWhere) whereObj)
370: .toString());
371: }
372: if (objectType.endsWith("java.util.Vector")) {
373: whereCondition = (Vector) whereObj;
374: outputBuffer
375: .append(conditionToString(whereCondition)
376: + "\n");
377: }
378: }
379: }
380: return outputBuffer.toString();
381: }
382:
383: /*
384: * Format a where condition for display.
385: */
386: private String conditionToString(Vector inputWhereCondition) {
387: int i;
388: String comparison, conditionStatus;
389: tsColumn leftColumn, rightColumn;
390: if (inputWhereCondition.size() < 4)
391: return "";
392: StringBuffer outputBuffer = new StringBuffer("WHERE ");
393: leftColumn = (tsColumn) inputWhereCondition.elementAt(0);
394: comparison = (String) inputWhereCondition.elementAt(1);
395: rightColumn = (tsColumn) inputWhereCondition.elementAt(2);
396: conditionStatus = (String) inputWhereCondition.elementAt(3);
397: outputBuffer
398: .append(leftColumn.getString() + " " + comparison + " "
399: + rightColumn.getString() + " "
400: + conditionStatus);
401: return outputBuffer.toString();
402: }
403:
404: /*
405: * Given a column name, and a Hashtable containing tables, determine
406: * which table "owns" a given column.
407: */
408: private tinySQLTable getTableForColumn(Hashtable tables,
409: String inputColumn) {
410: tinySQLTable tbl;
411: Vector tableNames;
412: Hashtable columnInfo;
413: String findColumn, tableAndAlias = (String) null, tableAlias;
414: int i, dotAt;
415: findColumn = inputColumn.toUpperCase();
416: dotAt = findColumn.indexOf(".");
417: tableNames = (Vector) tables.get("TABLE_SELECT_ORDER");
418: if (dotAt > -1) {
419: tableAlias = findColumn.substring(0, dotAt);
420: try {
421: tableAndAlias = UtilString.findTableAlias(tableAlias,
422: tableNames);
423: } catch (Exception ex) {
424: }
425: if (tableAndAlias != (String) null) {
426: tbl = (tinySQLTable) tables.get(tableAndAlias);
427: if (tbl != (tinySQLTable) null)
428: return tbl;
429: }
430: } else {
431: for (i = 0; i < tableNames.size(); i++) {
432: tbl = (tinySQLTable) tables.get((String) tableNames
433: .elementAt(i));
434: /*
435: * Get the Hashtable containing column information, and see if it
436: * contains the column we're looking for.
437: */
438: columnInfo = tbl.column_info;
439: if (columnInfo != (Hashtable) null)
440: if (columnInfo.containsKey(findColumn))
441: return tbl;
442: }
443: }
444: return (tinySQLTable) null;
445: }
446:
447: /*
448: * This method updates the where conditions that contain the input column and
449: * returns the status of the entire where clause.
450: */
451: public String evaluate(String inputColumnName,
452: String inputColumnValue) throws tinySQLException {
453: int i, j, result;
454: FieldTokenizer ft;
455: tsColumn leftColumn, rightColumn;
456: Vector whereConditions, whereCondition;
457: Object whereObj;
458: String objectType, comparison, conditionStatus;
459: double leftValue, rightValue;
460: if (tinySQLGlobals.WHERE_DEBUG)
461: System.out.println("Evaluate where with " + inputColumnName
462: + " = " + inputColumnValue);
463: for (i = 0; i < whereClauseList.size(); i++) {
464: whereConditions = (Vector) whereClauseList.elementAt(i);
465: for (j = 0; j < whereConditions.size(); j++) {
466: /*
467: * Where conditions can be tinySQLWhere objects or String arrays.
468: */
469: conditionStatus = "TRUE";
470: whereObj = whereConditions.elementAt(j);
471: objectType = whereObj.getClass().getName();
472: if (objectType.endsWith("tinySQLWhere")) {
473: conditionStatus = ((tinySQLWhere) whereObj)
474: .evaluate(inputColumnName, inputColumnValue);
475: } else if (objectType.endsWith("java.util.Vector")) {
476: whereCondition = (Vector) whereObj;
477: /*
478: * Check for updates on this column. Update the status to
479: * reflect which parts of the where condition have been set.
480: */
481: leftColumn = (tsColumn) whereCondition.elementAt(0);
482: conditionStatus = (String) whereCondition
483: .elementAt(3);
484: leftColumn.update(inputColumnName, inputColumnValue
485: .trim());
486: leftColumn.updateFunctions();
487: if (leftColumn.isValueSet()) {
488: if (conditionStatus.equals("UNKNOWN"))
489: whereCondition.setElementAt("LEFT", 3);
490: else if (conditionStatus.equals("RIGHT"))
491: whereCondition.setElementAt("BOTH", 3);
492: }
493: rightColumn = (tsColumn) whereCondition
494: .elementAt(2);
495: rightColumn.update(inputColumnName,
496: inputColumnValue.trim());
497: rightColumn.updateFunctions();
498: if (rightColumn.isValueSet()) {
499: if (conditionStatus.equals("UNKNOWN"))
500: whereCondition.setElementAt("RIGHT", 3);
501: else if (conditionStatus.equals("LEFT"))
502: whereCondition.setElementAt("BOTH", 3);
503: }
504: if (tinySQLGlobals.WHERE_DEBUG)
505: System.out
506: .println(conditionToString(whereCondition));
507: /*
508: * A where condition cannot be evaluated until both left and
509: * right values have been assigned.
510: */
511: conditionStatus = (String) whereCondition
512: .elementAt(3);
513: if (conditionStatus.equals("UNKNOWN")
514: | conditionStatus.equals("LEFT")
515: | conditionStatus.equals("RIGHT"))
516: continue;
517: /*
518: * Evaluate this where condition.
519: */
520: conditionStatus = "TRUE";
521: /*
522: * Any condition except IS NULL that involves a null
523: * column must evaluate to FALSE.
524: */
525: comparison = (String) whereCondition.elementAt(1);
526: if (leftColumn.isNull() | rightColumn.isNull()) {
527: if (comparison.equals("IS")) {
528: if (rightColumn.getString().equals("NULL")) {
529: whereCondition.setElementAt(
530: conditionStatus, 3);
531: continue;
532: }
533: }
534: whereCondition.setElementAt("FALSE", 3);
535: continue;
536: } else {
537: if (comparison.equals("IS")) {
538: if (rightColumn.getString().equals(
539: "NOT NULL")) {
540: whereCondition.setElementAt("TRUE", 3);
541: continue;
542: } else if (rightColumn.getString().equals(
543: "NULL")) {
544: whereCondition.setElementAt("FALSE", 3);
545: continue;
546: } else {
547: throw new tinySQLException(
548: "Invalid WHERE condition "
549: + rightColumn
550: .getString());
551: }
552: }
553: }
554: /*
555: * Evaluate all conditions other than NULL and NOT NULL
556: */
557: result = leftColumn.compareTo(rightColumn);
558: if (Utils.isCharColumn(leftColumn.type)
559: | Utils.isDateColumn(leftColumn.type)) {
560: /*
561: * Character string comparisons.
562: */
563: if (comparison.equals("=") & result != 0) {
564: conditionStatus = "FALSE";
565: } else if (comparison.equals("<>")
566: & result == 0) {
567: conditionStatus = "FALSE";
568: } else if (comparison.equals("!=")
569: & result == 0) {
570: conditionStatus = "FALSE";
571: } else if (comparison.equals(">") & result <= 0) {
572: conditionStatus = "FALSE";
573: } else if (comparison.equals(">=")
574: | comparison.equals("=>")) {
575: if (result < 0)
576: conditionStatus = "FALSE";
577: } else if (comparison.equals("<") & result >= 0) {
578: conditionStatus = "FALSE";
579: } else if (comparison.equals("<=")
580: | comparison.equals("=<")) {
581: if (result > 0)
582: conditionStatus = "FALSE";
583: } else if (comparison.equalsIgnoreCase("LIKE")) {
584: if (!leftColumn.like(rightColumn))
585: conditionStatus = "FALSE";
586: }
587: } else if (Utils.isNumberColumn(leftColumn.type)) {
588: /*
589: * Numeric comparisons.
590: */
591: if (comparison.equals("=") & result != 0)
592: conditionStatus = "FALSE";
593: else if (comparison.equals("<>") & result == 0)
594: conditionStatus = "FALSE";
595: else if (comparison.equals(">") & result <= 0)
596: conditionStatus = "FALSE";
597: else if (comparison.equals("<") & result >= 0)
598: conditionStatus = "FALSE";
599: else if (comparison.equals("<=") & result > 0)
600: conditionStatus = "FALSE";
601: else if (comparison.equals("=<") & result > 0)
602: conditionStatus = "FALSE";
603: else if (comparison.equals(">=") & result < 0)
604: conditionStatus = "FALSE";
605: else if (comparison.equals("=>") & result < 0)
606: conditionStatus = "FALSE";
607: }
608: whereCondition.setElementAt(conditionStatus, 3);
609: if (tinySQLGlobals.WHERE_DEBUG)
610: System.out.println("Where condition " + j
611: + " evaluation:\n"
612: + conditionToString(whereCondition));
613: }
614: }
615: }
616: return getStatus();
617: }
618:
619: /*
620: * This method evaluates the status of the entire where clause.
621: */
622: public String getStatus() {
623: int i, j;
624: Vector whereConditions;
625: Object whereObj;
626: String objectType, andStatus, orStatus, conditionStatus;
627: Vector whereCondition;
628: orStatus = "FALSE";
629: for (i = 0; i < whereClauseList.size(); i++) {
630: /*
631: * The AND operator is applied to the whereConditions
632: */
633: whereConditions = (Vector) whereClauseList.elementAt(i);
634: andStatus = "TRUE";
635: for (j = 0; j < whereConditions.size(); j++) {
636: /*
637: * Where conditions can be tinySQLWhere objects or String arrays.
638: */
639: whereObj = whereConditions.elementAt(j);
640: objectType = whereObj.getClass().getName();
641: if (objectType.endsWith("tinySQLWhere")) {
642: andStatus = ((tinySQLWhere) whereObj).getStatus();
643: if (andStatus.equals("FALSE"))
644: break;
645: } else if (objectType.endsWith("java.util.Vector")) {
646: whereCondition = (Vector) whereObj;
647: /*
648: * If any AND condition is FALSE, the entire where condition
649: * is FALSE.
650: */
651: conditionStatus = (String) whereCondition
652: .elementAt(3);
653: if (conditionStatus.equals("FALSE")) {
654: andStatus = "FALSE";
655: break;
656: /*
657: * If any AND condition is UNKNOWN, LEFT, or RIGHT, the entire
658: * where condition is UNKNOWN.
659: */
660: } else if (conditionStatus.equals("UNKNOWN")
661: | conditionStatus.equals("LEFT")
662: | conditionStatus.equals("RIGHT")) {
663: andStatus = "UNKNOWN";
664: }
665: }
666: }
667: /*
668: * If any OR condition is true, the entire where condition
669: * is true
670: */
671: if (andStatus.equals("TRUE")) {
672: orStatus = "TRUE";
673: break;
674: } else if (andStatus.equals("UNKNOWN")) {
675: orStatus = "UNKNOWN";
676: }
677: }
678: if (tinySQLGlobals.WHERE_DEBUG)
679: System.out.println("Return status " + orStatus);
680: return orStatus;
681: }
682: }
|