001: //** Copyright Statement ***************************************************
002: //The Salmon Open Framework for Internet Applications (SOFIA)
003: //Copyright (C) 1999 - 2003, 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:
021: package com.salmonllc.sql;
022:
023: import java.sql.Timestamp;
024: import java.text.ParseException;
025: import java.text.SimpleDateFormat;
026: import java.util.Date;
027: import java.util.StringTokenizer;
028: import java.util.Vector;
029:
030: import com.salmonllc.util.SalmonDateFormat;
031:
032: /**
033: * This class builds selection criteria strings for the QBEBuilder component
034: */
035: public class QBECriteriaBuilder {
036: public static final int OR_OPERATOR = 0;
037: public static final int AND_OPERATOR = 1;
038: public static final int NOT_OPERATOR = 2;
039:
040: private static final int FILTERTYPE_STRING = 0;
041: private static final int FILTERTYPE_NUMBER = 1;
042: private static final int FILTERTYPE_NONE = 2;
043: private static final int FILTERTYPE_TEXT = 3;
044:
045: public static final String DEFAULT_STOP_WORDS[] = { "the", "of",
046: "to", "and", "in", "that", "for", "by", "as", "be", "or",
047: "this", "which", "with", "at", "an", "from", "under",
048: "such", "there", "other", "if", "but", "upon", "where",
049: "these", "when", "whether", "also", "than", "after",
050: "within", "before", "because", "without", "however",
051: "between", "those", "since", "into", "out", "a", "is",
052: "it", "i" };
053:
054: private static SalmonDateFormat _dateFormat = new SalmonDateFormat();
055:
056: private static class Tokenizer {
057: private Vector _tokens = new Vector();
058: private Vector _operators = new Vector();
059: private int _nextElement = -1;
060: public static final String DELIMITERS = ",.?\"!()[]{}:;\\/ +";
061:
062: public Tokenizer(String parseIt) {
063: super ();
064:
065: if (parseIt == null)
066: return;
067:
068: int size = parseIt.length();
069: StringBuffer work = new StringBuffer(size);
070:
071: char lastOp = ' ';
072: char lastChar = ' ';
073: char c = ' ';
074: boolean quoteMode = false;
075: boolean delimitMode = false;
076:
077: for (int i = 0; i < size; i++) {
078: lastChar = c;
079: c = parseIt.charAt(i);
080: if (c == '+') {
081: if (quoteMode)
082: work.append(c);
083: else {
084: lastOp = c;
085: if (work.length() > 0) {
086: if (addString(work, lastOp))
087: ;
088: lastOp = ' ';
089: }
090: delimitMode = true;
091: }
092: } else if (c == '-') {
093: if (quoteMode || DELIMITERS.indexOf(lastChar) < 0)
094: work.append(c);
095: else
096: lastOp = c;
097: } else if (c == '"') {
098: if (addString(work, lastOp))
099: lastOp = ' ';
100: quoteMode = !quoteMode;
101: } else {
102: if (quoteMode)
103: work.append(c);
104: else if ((DELIMITERS.indexOf(c) != -1)) {
105: if (!delimitMode) {
106: if (work.length() > 0) {
107: if (addString(work, lastOp))
108: ;
109: lastOp = ' ';
110: }
111: delimitMode = true;
112: }
113: } else {
114: delimitMode = false;
115: work.append(c);
116: }
117: }
118: }
119:
120: addString(work, lastOp);
121: }
122:
123: private boolean addString(StringBuffer s, char op) {
124: if (s.length() == 0)
125: return false;
126:
127: int operator = OR_OPERATOR;
128: String token = s.toString();
129:
130: if (op == '+')
131: operator = AND_OPERATOR;
132: else if (op == '-')
133: operator = NOT_OPERATOR;
134:
135: _operators.addElement(new Integer(operator));
136: _tokens.addElement(token);
137: s.setLength(0);
138:
139: return true;
140: }
141:
142: public int currentOperator() {
143: if (_nextElement == -1 || _nextElement >= _operators.size())
144: return -1;
145: else
146: return ((Integer) _operators.elementAt(_nextElement))
147: .intValue();
148: }
149:
150: public boolean hasMoreTokens() {
151:
152: int size = _operators.size();
153:
154: if (size > (_nextElement + 1))
155: return true;
156:
157: return false;
158: }
159:
160: public String nextToken() {
161: if (!hasMoreTokens())
162: return null;
163:
164: _nextElement++;
165:
166: return (String) _tokens.elementAt(_nextElement);
167: }
168: }
169:
170: public static String buildSQL(DataStoreQBEInterface ds,
171: String filterString, int filterType,
172: ColumnDefinition[] colList, String stopWords[])
173: throws DataStoreException {
174: String val = filterString;
175: if (val == null)
176: return null;
177: if (colList == null || colList.length == 0)
178: return null;
179:
180: String dbms = ds.getDBMS();
181:
182: StringBuffer filterBuffer = new StringBuffer(255);
183:
184: if (filterType == QBEBuilder.CRITERIA_TYPE_COMPLEX) {
185: String complexFilter = buildComplexSQL(val, colList, dbms,
186: stopWords);
187: if (complexFilter != null)
188: filterBuffer.append(complexFilter);
189: } else {
190: for (int i = 0; i < colList.length; i++) {
191: if (filterType >= QBEBuilder.CRITERIA_STRING_ONLY
192: && colList[i].getDSDataType() != QBEBuilder.DATATYPE_STRING)
193: continue;
194: if (filterType == QBEBuilder.CRITERIA_TYPE_CUSTOM)
195: continue;
196: val = DataStoreBuffer.fixQuote(val.trim(), colList[i]
197: .getDSDataType(), dbms);
198: String column = colList[i].getTableName() + "."
199: + colList[i].getColumnName();
200: if (filterType == QBEBuilder.CRITERIA_TYPE_CONTAINS) {
201: addOr(filterBuffer);
202: filterBuffer.append(column);
203: filterBuffer.append(" LIKE '%");
204: filterBuffer.append(val);
205: filterBuffer.append("%'");
206: } else if (filterType == QBEBuilder.CRITERIA_TYPE_CONTAINS_IGNORE_CASE) {
207: addOr(filterBuffer);
208: filterBuffer.append("upper(");
209: filterBuffer.append(column);
210: filterBuffer.append(") LIKE '%");
211: filterBuffer.append(val.toUpperCase());
212: filterBuffer.append("%'");
213: } else if (filterType == QBEBuilder.CRITERIA_TYPE_EQUALS_IGNORE_CASE) {
214: addOr(filterBuffer);
215: filterBuffer.append("upper(");
216: filterBuffer.append(column);
217: filterBuffer.append(") = '");
218: filterBuffer.append(val.toUpperCase());
219: filterBuffer.append("'");
220: } else if (filterType == QBEBuilder.CRITERIA_TYPE_STARTS_WITH) {
221: addOr(filterBuffer);
222: filterBuffer.append(column);
223: filterBuffer.append(" LIKE '");
224: filterBuffer.append(val);
225: filterBuffer.append("%'");
226: } else if (filterType == QBEBuilder.CRITERIA_TYPE_STARTS_WITH_IGNORE_CASE) {
227: addOr(filterBuffer);
228: filterBuffer.append("upper(");
229: filterBuffer.append(column);
230: filterBuffer.append(") LIKE '");
231: filterBuffer.append(val.toUpperCase());
232: filterBuffer.append("%'");
233: } else if (filterType == QBEBuilder.CRITERIA_TYPE_IN) {
234: String in = buildInSQL(val, colList[i]);
235: if (in != null) {
236: addOr(filterBuffer);
237: filterBuffer.append(column);
238: filterBuffer.append(" IN (");
239: filterBuffer.append(in);
240: filterBuffer.append(")");
241: }
242: } else {
243: int datatype = colList[i].getDSDataType();
244: String lit = getColumnLiteralValue(val, datatype,
245: true);
246: String op = null;
247: if (filterType == QBEBuilder.CRITERIA_TYPE_LTE)
248: op = " <= ";
249: else if (filterType == QBEBuilder.CRITERIA_TYPE_LT)
250: op = " < ";
251: else if (filterType == QBEBuilder.CRITERIA_TYPE_GTE)
252: op = " >= ";
253: else if (filterType == QBEBuilder.CRITERIA_TYPE_GT)
254: op = " >= ";
255: else if (filterType == QBEBuilder.CRITERIA_TYPE_EQUALS)
256: op = " = ";
257: else if (filterType == QBEBuilder.CRITERIA_TYPE_NOT_EQUALS) {
258: lit += " or " + column + " IS NULL ";
259: op = " <> ";
260: }
261: if (lit != null && op != null) {
262: addOr(filterBuffer);
263: filterBuffer.append(column);
264: filterBuffer.append(op);
265: filterBuffer.append(lit);
266: }
267: }
268: }
269: }
270: return filterBuffer.toString();
271:
272: }
273:
274: private static void addOr(StringBuffer sb) {
275: if (sb.length() != 0)
276: sb.append(" or ");
277: }
278:
279: private static void addDoubleBar(StringBuffer sb) {
280: if (sb.length() != 0)
281: sb.append(" || ");
282: }
283:
284: /**
285: * Builds a datastore filter expression for the specified datastore and filter string comparing to all columns in the datastore
286: * @param dsb The DataStore to build the filter for
287: * @param filterString the filter to value to compare to elements in the datastore
288: * @param filterTye The type of comparison to use in the filter. Valid values are one of the QBEBuilder CRITERIA_TYPE constants
289: **/
290: public static String buildFilter(DataStoreBuffer dsb,
291: String filterString, int filterType)
292: throws DataStoreException {
293: String colList[] = dsb.getColumnList();
294: return buildFilter(dsb, filterString, filterType, colList, null);
295: }
296:
297: /**
298: * Builds a datastore filter expression for the specified datastore and filter string comparing to all columns in the specified column list
299: * @param dsb The DataStore to build the filter for
300: * @param filterString the filter to value to compare to elements in the datastore
301: * @param filterType The type of comparison to use in the filter. Valid values are one of the QBEBuilder CRITERIA_TYPE constants
302: * @param colList The list of columns in the datastore to compare to the filter string using the filter type
303: * @param stopWords A list of words to exclude from complex criteria. Set to null to use the default list.
304: **/
305: public static String buildFilter(DataStoreBuffer dsb,
306: String filterString, int filterType, String colList[],
307: String stopWords[]) throws DataStoreException {
308: String val = filterString;
309: if (val == null)
310: return null;
311: if (colList == null || colList.length == 0)
312: return null;
313:
314: StringBuffer filterBuffer = new StringBuffer(255);
315:
316: if (filterType == QBEBuilder.CRITERIA_TYPE_COMPLEX)
317: filterBuffer.append(buildComplexFilter(dsb, val, colList,
318: stopWords));
319: else {
320: for (int i = 0; i < colList.length; i++) {
321: if (filterType >= QBEBuilder.CRITERIA_STRING_ONLY
322: && dsb.getColumnDataType(colList[i]) != QBEBuilder.DATATYPE_STRING)
323: continue;
324: if (filterType == QBEBuilder.CRITERIA_TYPE_CUSTOM)
325: continue;
326: val = fixQuoteForDsExp(val.trim());
327: if (filterType == QBEBuilder.CRITERIA_TYPE_CONTAINS) {
328: addDoubleBar(filterBuffer);
329: filterBuffer.append(colList[i]);
330: filterBuffer.append(".indexOf('");
331: filterBuffer.append(val);
332: filterBuffer.append("') > -1");
333: } else if (filterType == QBEBuilder.CRITERIA_TYPE_CONTAINS_IGNORE_CASE) {
334: addDoubleBar(filterBuffer);
335: filterBuffer.append(colList[i]);
336: filterBuffer.append(".toLowerCase().indexOf('");
337: filterBuffer.append(val.toLowerCase());
338: filterBuffer.append("') > -1");
339: } else if (filterType == QBEBuilder.CRITERIA_TYPE_EQUALS_IGNORE_CASE) {
340: addDoubleBar(filterBuffer);
341: filterBuffer.append(colList[i]);
342: filterBuffer.append(".toLowerCase() == '");
343: filterBuffer.append(val.toLowerCase());
344: filterBuffer.append("'");
345: } else if (filterType == QBEBuilder.CRITERIA_TYPE_STARTS_WITH) {
346: addDoubleBar(filterBuffer);
347: filterBuffer.append(colList[i]);
348: filterBuffer.append(".indexOf('");
349: filterBuffer.append(val);
350: filterBuffer.append("') == 0");
351: } else if (filterType == QBEBuilder.CRITERIA_TYPE_STARTS_WITH_IGNORE_CASE) {
352: addDoubleBar(filterBuffer);
353: filterBuffer.append(colList[i]);
354: filterBuffer.append(".toLowerCase().indexOf('");
355: filterBuffer.append(val.toLowerCase());
356: filterBuffer.append("') == 0");
357: } else if (filterType == QBEBuilder.CRITERIA_TYPE_IN) {
358: String in = buildInFilter(val, colList[i], dsb
359: .getColumnDataType(colList[i]));
360: if (in != null) {
361: addDoubleBar(filterBuffer);
362: filterBuffer.append(in);
363: }
364: } else {
365: int datatype = dsb.getColumnDataType(colList[i]);
366: String lit = getColumnLiteralValue(val, datatype,
367: false);
368: String op = null;
369: if (filterType == QBEBuilder.CRITERIA_TYPE_LTE)
370: op = " <= ";
371: else if (filterType == QBEBuilder.CRITERIA_TYPE_LT)
372: op = " < ";
373: else if (filterType == QBEBuilder.CRITERIA_TYPE_GTE)
374: op = " >= ";
375: else if (filterType == QBEBuilder.CRITERIA_TYPE_GT)
376: op = " >= ";
377: else if (filterType == QBEBuilder.CRITERIA_TYPE_EQUALS)
378: op = " == ";
379: else if (filterType == QBEBuilder.CRITERIA_TYPE_NOT_EQUALS)
380: op = " != ";
381: if (lit != null && op != null) {
382: addDoubleBar(filterBuffer);
383: filterBuffer.append(colList[i]);
384: filterBuffer.append(op);
385: filterBuffer.append(lit);
386: }
387: }
388: }
389: }
390: return filterBuffer.toString();
391: }
392:
393: /**
394: * Builds a datastore filter expression for the specified datastore and filter string
395: * @param ds The DataStore to build the filter for
396: * @param filterString the filter to parse and convert into a datastore filter
397: * @param the column list to compare the filter to
398: */
399: private static String buildComplexFilter(DataStoreBuffer ds,
400: String filterString, String[] colList, String[] stopWords) {
401: try {
402: if (filterString == null)
403: return null;
404: if (filterString.length() == 0)
405: return null;
406:
407: //figure out if we use the default stop word list or the one passed in
408: String sw[] = DEFAULT_STOP_WORDS;
409: if (stopWords != null)
410: sw = stopWords;
411:
412: //figure out from the datastore what colums and datatypes we can use to build a filter
413: int colTypes[] = new int[colList.length];
414: int colCount = 0;
415: for (int i = 0; i < colList.length; i++) {
416: String colName = colList[i];
417: int type = ds.getColumnDataType(colName);
418: if (type == DataStoreBuffer.DATATYPE_STRING) {
419: type = FILTERTYPE_STRING;
420: colCount++;
421: } else if (type == DataStoreBuffer.DATATYPE_DOUBLE
422: || type == DataStoreBuffer.DATATYPE_FLOAT
423: || type == DataStoreBuffer.DATATYPE_INT
424: || type == DataStoreBuffer.DATATYPE_LONG
425: || type == DataStoreBuffer.DATATYPE_SHORT) {
426: type = FILTERTYPE_NUMBER;
427: colCount++;
428: } else
429: type = FILTERTYPE_NONE;
430:
431: colTypes[i] = type;
432: }
433:
434: if (colCount == 0)
435: return null;
436:
437: //parse the criteria into terms, types and operators
438: String work = filterString.trim();
439:
440: Tokenizer t = new Tokenizer(work);
441: Vector fTerms = new Vector();
442: Vector fTypes = new Vector();
443: Vector fOperators = new Vector();
444: while (t.hasMoreTokens()) {
445: String token = t.nextToken();
446: boolean addToken = true;
447: for (int i = 0; i < sw.length; i++) {
448: String temp = token.toLowerCase();
449: if (temp.equals(sw[i])) {
450: addToken = false;
451: break;
452: }
453: }
454: if (token.length() == 1)
455: addToken = false;
456:
457: if (addToken) {
458: fTerms.addElement(fixQuoteForDsExp(token));
459: try {
460: Integer.parseInt(token);
461: fTypes
462: .addElement(new Integer(
463: FILTERTYPE_NUMBER));
464: } catch (Exception e) {
465: fTypes
466: .addElement(new Integer(
467: FILTERTYPE_STRING));
468: }
469: fOperators.addElement(new Integer(t
470: .currentOperator()));
471: }
472: }
473:
474: //build a filter string
475: String ret = "";
476: String ors = "";
477: String ands = "";
478: work = "";
479: int termCount = fTerms.size();
480: for (int i = 0; i < termCount; i++) {
481: String or = "";
482: work = "";
483: String fTerm = (String) fTerms.elementAt(i);
484: int fType = ((Integer) fTypes.elementAt(i)).intValue();
485: int fOperator = ((Integer) fOperators.elementAt(i))
486: .intValue();
487:
488: for (int j = 0; j < colList.length; j++) {
489:
490: String col = colList[j];
491:
492: if (colTypes[j] == FILTERTYPE_NONE)
493: continue;
494: else if (colTypes[j] == FILTERTYPE_STRING) {
495: work += or;
496: String token = (String) fTerms.elementAt(i);
497: work += "(" + col + ".toUpperCase().indexOf('"
498: + token.toUpperCase() + "')";
499: if (fOperator == NOT_OPERATOR) {
500: work += " == -1 || " + col + " == null)";
501: or = " && ";
502: } else {
503: work += " > -1)";
504: or = " || ";
505: }
506: } else if (fType == FILTERTYPE_NUMBER) {
507: work += or;
508: work += "(" + col;
509: if (fOperator == NOT_OPERATOR) {
510: work += " != ";
511: or = " && ";
512: } else {
513: work += " == ";
514: or = " || ";
515: }
516:
517: work += fTerm + ")";
518:
519: }
520: }
521: String op = "\n";
522:
523: if (fOperator == OR_OPERATOR) {
524: if (ors.length() > 0)
525: ors += " || ";
526: ors += "(" + work + ")";
527: } else {
528: if (ands.length() > 0)
529: ands += " && ";
530: ands += "(" + work + ")";
531: }
532: }
533:
534: if (ors.length() > 0)
535: ret += " (" + ors + ")";
536:
537: if (ands.length() > 0) {
538: if (ors.length() > 0)
539: ret += " && ";
540: ret += ands;
541: }
542: return ret;
543: } catch (Exception ex) {
544: return null;
545: }
546: }
547:
548: private static String buildInSQL(String filterString,
549: ColumnDefinition col) {
550: String ret = null;
551: if (filterString == null)
552: return ret;
553: if (filterString.length() == 0)
554: return ret;
555: StringTokenizer st = new StringTokenizer(filterString, ",");
556: while (st.hasMoreElements()) {
557: String tok = st.nextToken();
558: tok = getColumnLiteralValue(tok, col.getDSDataType(), true);
559: if (tok != null) {
560: if (ret == null)
561: ret = tok;
562: else
563: ret += "," + tok;
564: }
565: }
566: return ret;
567: }
568:
569: private static String buildInFilter(String filterString,
570: String colName, int colType) {
571: String ret = null;
572: if (filterString == null)
573: return ret;
574: if (filterString.length() == 0)
575: return ret;
576: StringTokenizer st = new StringTokenizer(filterString, ",");
577: while (st.hasMoreElements()) {
578: String tok = st.nextToken();
579: tok = getColumnLiteralValue(tok, colType, false);
580: if (tok != null) {
581: if (ret != null)
582: ret += " || ";
583: else
584: ret = "";
585: ret += colName + " == " + tok;
586: }
587: }
588: return ret;
589: }
590:
591: /**
592: * Builds a datastore filter expression for the specified datastore and filter string
593: * @param ds The DataStore to build the filter for
594: * @param filterString the filter to parse and convert into a datastore filter
595: * @param the column list to compare the filter to
596: */
597: private static String buildComplexSQL(String filterString,
598: ColumnDefinition[] colList, String DBMS, String[] stopWords) {
599: try {
600: if (filterString == null)
601: return null;
602: if (filterString.length() == 0)
603: return null;
604:
605: //figure out if we use the default stop word list or the one passed in
606: String sw[] = DEFAULT_STOP_WORDS;
607: if (stopWords != null)
608: sw = stopWords;
609:
610: //figure out from the datastore what colums and datatypes we can use to build a filter
611: int colTypes[] = new int[colList.length];
612: int colCount = 0;
613: for (int i = 0; i < colList.length; i++) {
614: int type = colList[i].getDSDataType();
615: if (type == DataStoreBuffer.DATATYPE_STRING) {
616: type = FILTERTYPE_STRING;
617: if (DBMS.equals(DBConnection.SYBASE_CONNECTION)
618: || DBMS
619: .equals(DBConnection.MSSQLSEVER_CONNECTION))
620: if (colList[i].getDBDataType() != null
621: && colList[i].getDBDataType()
622: .equalsIgnoreCase("text"))
623: type = FILTERTYPE_TEXT;
624: colCount++;
625: } else if (type == DataStoreBuffer.DATATYPE_DOUBLE
626: || type == DataStoreBuffer.DATATYPE_FLOAT
627: || type == DataStoreBuffer.DATATYPE_INT
628: || type == DataStoreBuffer.DATATYPE_LONG
629: || type == DataStoreBuffer.DATATYPE_SHORT) {
630: type = FILTERTYPE_NUMBER;
631: colCount++;
632: } else
633: type = FILTERTYPE_NONE;
634:
635: colTypes[i] = type;
636: }
637:
638: if (colCount == 0)
639: return null;
640:
641: //parse the criteria into terms, types and operators
642: String work = filterString.trim();
643:
644: Tokenizer t = new Tokenizer(work);
645: Vector fTerms = new Vector();
646: Vector fTypes = new Vector();
647: Vector fOperators = new Vector();
648: while (t.hasMoreTokens()) {
649: String token = t.nextToken();
650: boolean addToken = true;
651: for (int i = 0; i < sw.length; i++) {
652: String temp = token.toLowerCase();
653: if (temp.equals(sw[i])) {
654: addToken = false;
655: break;
656: }
657: }
658:
659: if (addToken) {
660: fTerms.addElement(DataStoreBuffer.fixQuote(token,
661: DataStoreBuffer.DATATYPE_STRING, DBMS));
662: try {
663: Integer.parseInt(token);
664: fTypes
665: .addElement(new Integer(
666: FILTERTYPE_NUMBER));
667: } catch (Exception e) {
668: fTypes
669: .addElement(new Integer(
670: FILTERTYPE_STRING));
671: }
672: fOperators.addElement(new Integer(t
673: .currentOperator()));
674: }
675: }
676:
677: //build a filter string
678: String ret = "";
679: String ors = "";
680: String ands = "";
681: work = "";
682: int termCount = fTerms.size();
683: for (int i = 0; i < termCount; i++) {
684: String or = "";
685: work = "";
686: String fTerm = (String) fTerms.elementAt(i);
687: int fType = ((Integer) fTypes.elementAt(i)).intValue();
688: int fOperator = ((Integer) fOperators.elementAt(i))
689: .intValue();
690:
691: for (int j = 0; j < colList.length; j++) {
692:
693: String col = colList[j].getTableName() + "."
694: + colList[j].getColumnName();
695:
696: if (colTypes[j] == FILTERTYPE_NONE)
697: continue;
698: else if (colTypes[j] == FILTERTYPE_STRING
699: || colTypes[j] == FILTERTYPE_TEXT) {
700: work += or;
701: String token = (String) fTerms.elementAt(i);
702: String temp = col;
703: if (colTypes[j] == FILTERTYPE_TEXT)
704: // temp = "convert(char(8000), " + col + ")";
705: temp = "convert(char(255), " + col + ")";
706: if (fOperator == NOT_OPERATOR) {
707: work += "( upper(" + temp + ") NOT LIKE '%"
708: + token.toUpperCase() + "%'";
709: work += " or " + col + " IS NULL) ";
710: or = " and ";
711: } else {
712: work += "( upper(" + temp + ") LIKE '%"
713: + token.toUpperCase() + "%')";
714: or = " or ";
715: }
716: } else if (fType == FILTERTYPE_NUMBER) {
717: work += or;
718: work += "(" + col;
719: if (fOperator == NOT_OPERATOR) {
720: work += " <> ";
721: or = " and ";
722: } else {
723: work += " = ";
724: or = " or ";
725: }
726:
727: work += fTerm + ")";
728:
729: }
730: }
731: String op = "\n";
732:
733: if (fOperator == OR_OPERATOR) {
734: if (ors.length() > 0)
735: ors += " or ";
736: ors += "(" + work + ")";
737: } else {
738: if (ands.length() > 0)
739: ands += " and ";
740: ands += "(" + work + ")";
741: }
742: }
743:
744: if (ors.length() > 0)
745: ret += " (" + ors + ")";
746:
747: if (ands.length() > 0) {
748: if (ors.length() > 0)
749: ret += " and ";
750: ret += ands;
751: }
752: if (ret.length() == 0)
753: return null;
754: else
755: return ret;
756: } catch (Exception ex) {
757: return null;
758: }
759: }
760:
761: private static String fixQuoteForDsExp(String token) {
762: StringBuffer sb = new StringBuffer(token.length());
763: for (int i = 0; i < token.length(); i++) {
764: char c = token.charAt(i);
765: if (c == '\'')
766: sb.append("~'");
767: else
768: sb.append(c);
769: }
770: return sb.toString();
771: }
772:
773: private static String getColumnLiteralValue(String token,
774: int dataType, boolean SQL) {
775: if (dataType == QBEBuilder.DATATYPE_ANY)
776: return token;
777: else if (dataType == QBEBuilder.DATATYPE_STRING)
778: return "'" + token + "'";
779: else if (dataType == QBEBuilder.DATATYPE_SHORT
780: || dataType == QBEBuilder.DATATYPE_INT
781: || dataType == QBEBuilder.DATATYPE_LONG) {
782: try {
783: double d = Double.parseDouble(token);
784: long l = (long) d;
785: return new Long(l).toString();
786: } catch (Exception ex) {
787: return null;
788: }
789: } else if (dataType == QBEBuilder.DATATYPE_DOUBLE
790: || dataType == QBEBuilder.DATATYPE_FLOAT) {
791: try {
792: return new Double(Double.parseDouble(token)).toString();
793: } catch (Exception ex) {
794: return null;
795: }
796: } else if (dataType == QBEBuilder.DATATYPE_DATETIME
797: || dataType == QBEBuilder.DATATYPE_DATE
798: || dataType == QBEBuilder.DATATYPE_TIME) {
799: try {
800: if (SQL) {
801: Date d = _dateFormat.parse(token);
802: String val = "";
803: if (dataType == QBEBuilder.DATATYPE_DATETIME)
804: val = "{ts ' "
805: + new Timestamp(d.getTime()).toString()
806: + "'}";
807: else if (dataType == QBEBuilder.DATATYPE_DATE)
808: val = "{d ' "
809: + new java.sql.Date(d.getTime())
810: .toString() + "'}";
811: else if (dataType == QBEBuilder.DATATYPE_TIME)
812: val = "{t '"
813: + new java.sql.Time(d.getTime())
814: .toString() + "'}";
815:
816: return val;
817: } else {
818: Date d = _dateFormat.parse(token);
819: String val = "";
820: if (dataType == QBEBuilder.DATATYPE_DATETIME)
821: val = new Timestamp(d.getTime()).toString();
822: else if (dataType == QBEBuilder.DATATYPE_DATE)
823: val = new java.sql.Date(d.getTime()).toString();
824: else if (dataType == QBEBuilder.DATATYPE_TIME)
825: val = new java.sql.Time(d.getTime()).toString();
826: return "'" + val + "'";
827: }
828: } catch (ParseException e) {
829: return null;
830: }
831: } else
832: return null;
833: }
834:
835: private static String formatDateTime(Timestamp t,
836: SimpleDateFormat f, String DBMS) {
837: String ret = f.format(t);
838:
839: if (t.getNanos() == 0)
840: return ret;
841:
842: String nanosString;
843: String zeros = "000000000";
844:
845: nanosString = Integer.toString(t.getNanos());
846:
847: // Add leading zeros
848: nanosString = zeros.substring(0, (9 - nanosString.length()))
849: + nanosString;
850:
851: // Truncate trailing zeros
852: char[] nanosChar = new char[nanosString.length()];
853: nanosString.getChars(0, nanosString.length(), nanosChar, 0);
854: int truncIndex = 8;
855: while (nanosChar[truncIndex] == '0') {
856: truncIndex--;
857: }
858: nanosString = new String(nanosChar, 0, truncIndex + 1);
859:
860: if (DBMS == DBConnection.SYBASE_CONNECTION
861: && nanosString.length() > 3)
862: nanosString = nanosString.substring(0, 3);
863:
864: return ret + "." + nanosString;
865: }
866: }
|