001: /***************************************************************
002: * This file is part of the [fleXive](R) project.
003: *
004: * Copyright (c) 1999-2008
005: * UCS - unique computing solutions gmbh (http://www.ucs.at)
006: * All rights reserved
007: *
008: * The [fleXive](R) project is free software; you can redistribute
009: * it and/or modify it under the terms of the GNU General Public
010: * License as published by the Free Software Foundation;
011: * either version 2 of the License, or (at your option) any
012: * later version.
013: *
014: * The GNU General Public License can be found at
015: * http://www.gnu.org/copyleft/gpl.html.
016: * A copy is found in the textfile GPL.txt and important notices to the
017: * license from the author are found in LICENSE.txt distributed with
018: * these libraries.
019: *
020: * This library is distributed in the hope that it will be useful,
021: * but WITHOUT ANY WARRANTY; without even the implied warranty of
022: * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
023: * GNU General Public License for more details.
024: *
025: * For further information about UCS - unique computing solutions gmbh,
026: * please see the company website: http://www.ucs.at
027: *
028: * For further information about [fleXive](R), please see the
029: * project website: http://www.flexive.org
030: *
031: *
032: * This copyright notice MUST APPEAR in all copies of the file!
033: ***************************************************************/package com.flexive.shared.search.query;
034:
035: import com.flexive.shared.CacheAdmin;
036: import com.flexive.shared.EJBLookup;
037: import com.flexive.shared.FxSharedUtils;
038: import com.flexive.shared.FxFormatUtils;
039: import com.flexive.shared.exceptions.FxApplicationException;
040: import com.flexive.shared.exceptions.FxInvalidParameterException;
041: import com.flexive.shared.exceptions.FxInvalidStateException;
042: import com.flexive.shared.search.*;
043: import com.flexive.shared.search.query.QueryOperatorNode.Operator;
044: import com.flexive.shared.structure.FxAssignment;
045: import com.flexive.shared.structure.FxProperty;
046: import com.flexive.shared.value.FxValue;
047: import org.apache.commons.lang.ArrayUtils;
048: import org.apache.commons.lang.StringUtils;
049:
050: import java.io.Serializable;
051: import java.util.*;
052:
053: /**
054: * Query Builder for flexive SQL queries. Supports incremental adding
055: * of conditions, filter and order by columns.
056: *
057: * @author Daniel Lichtenberger (daniel.lichtenberger@flexive.com), UCS - unique computing solutions gmbh (http://www.ucs.at)
058: * @version $Rev: 181 $
059: */
060: public class SqlQueryBuilder implements Serializable {
061: static final long serialVersionUID = -4805627533111750389L;
062:
063: private static final String[] BASIC_SELECTS = new String[] { "@pk",
064: "mandator", "created_by", "acl", "step", "*" };
065: /**
066: * Start column for user defined properties
067: */
068: public static final int COL_USERPROPS = ArrayUtils.indexOf(
069: BASIC_SELECTS, "*");
070:
071: private final ResultLocation location;
072: private ResultViewType viewType;
073:
074: private final List<String> selectColumns;
075: private final List<String> filters;
076: private final StringBuilder whereConditions;
077: private final List<String> orderBy;
078: private final Set<Table> tables;
079: private final Stack<Operator> operatorStack;
080: private final Stack<Integer> expressionCounter;
081:
082: private int startRow;
083: private int maxRows = -1;
084:
085: private boolean includeBasicSelects;
086: private boolean frozen; // when the query is frozen, no more conditions may be added. Getting the query or the search conditions freezes the query builder.
087:
088: /**
089: * Instantiates an empty query builder.
090: */
091: public SqlQueryBuilder() {
092: this (AdminResultLocations.DEFAULT, ResultViewType.LIST);
093: }
094:
095: /**
096: * Instantiates an empty query builder for the specified result location and view type.
097: *
098: * @param location the result location
099: * @param viewType the view type
100: */
101: public SqlQueryBuilder(ResultLocation location,
102: ResultViewType viewType) {
103: this (new ArrayList<String>(), new ArrayList<String>(),
104: new StringBuilder(), new ArrayList<String>(),
105: new HashSet<Table>(), new Stack<Operator>(),
106: new Stack<Integer>(), true, false, location, viewType);
107: }
108:
109: /**
110: * Copy constructor. Creates an independent query builder based on the query builder <code>other</code>
111: *
112: * @param other the query builder to be copied
113: */
114: public SqlQueryBuilder(SqlQueryBuilder other) {
115: //noinspection unchecked
116: this (new ArrayList<String>(other.selectColumns),
117: new ArrayList<String>(other.filters),
118: new StringBuilder(other.whereConditions),
119: new ArrayList<String>(other.orderBy),
120: new HashSet<Table>(other.tables),
121: (Stack<Operator>) other.operatorStack.clone(),
122: (Stack<Integer>) other.expressionCounter.clone(),
123: other.includeBasicSelects, other.frozen,
124: other.location, other.viewType);
125: }
126:
127: private SqlQueryBuilder(List<String> selectColumns,
128: List<String> filters, StringBuilder whereConditions,
129: List<String> orderBy, Set<Table> tables,
130: Stack<Operator> operatorStack,
131: Stack<Integer> expressionCounter,
132: boolean includeBasicSelects, boolean frozen,
133: ResultLocation location, ResultViewType viewType) {
134: this .selectColumns = selectColumns;
135: this .filters = filters;
136: this .orderBy = orderBy;
137: this .whereConditions = whereConditions;
138: this .tables = tables;
139: this .operatorStack = operatorStack;
140: this .expressionCounter = expressionCounter;
141: this .includeBasicSelects = includeBasicSelects;
142: this .frozen = frozen;
143: this .location = location;
144: this .viewType = viewType;
145: }
146:
147: /**
148: * Returns the complete query generated by the builder.
149: *
150: * @return the SQL search query
151: */
152: public String getQuery() {
153: final String conditions = getConditions();
154: if (tables.size() == 0 && StringUtils.isNotBlank(conditions)
155: && !"()".equals(conditions)) {
156: throw new FxInvalidStateException(
157: "ex.sqlQueryBuilder.tables.empty")
158: .asRuntimeException();
159: } else if (tables.size() == 0
160: && StringUtils.isBlank(conditions)) {
161: // default query: select all content
162: tables.add(Table.CONTENT);
163: }
164: StringBuilder query = new StringBuilder();
165: // build select
166: query.append("SELECT ").append(Table.CONTENT.getAlias())
167: .append(".");
168: query.append(StringUtils.join(getColumnNames().iterator(), ", "
169: + Table.CONTENT.getAlias() + "."));
170: // build from
171: query.append("\nFROM ");
172: for (Table table : tables) {
173: query.append(table.getName()).append(' ').append(
174: table.getAlias());
175: query.append(',');
176: }
177: query.setCharAt(query.length() - 1, ' '); // replace last comma
178: // build filters
179: if (!filters.isEmpty()) {
180: query.append("\nFILTER ").append(getFilters());
181: }
182: if (conditions.length() > 0) {
183: // build conditions
184: query.append("\nWHERE ").append(conditions);
185: }
186: if (!orderBy.isEmpty()) {
187: query.append("\nORDER BY ").append(getOrderBy());
188: }
189: return query.toString();
190: }
191:
192: /**
193: * Return the filters defined for this query (if any).
194: *
195: * @return the filters defined for this query
196: */
197: public String getFilters() {
198: return StringUtils.join(filters.iterator(), ", ");
199: }
200:
201: /**
202: * Return the order by columns defined for this query.
203: *
204: * @return the order by columns defined for this query.
205: */
206: public String getOrderBy() {
207: return orderBy.isEmpty() ? "" : Table.CONTENT.getAlias()
208: + "."
209: + StringUtils.join(orderBy.iterator(), ", "
210: + Table.CONTENT.getAlias());
211: }
212:
213: /**
214: * Render a condition for a common FxAssignment query.
215: *
216: * @param assignment the assignment
217: * @param comparator the comparator to be used
218: * @param value the value to be compared against
219: * @return this
220: */
221: public SqlQueryBuilder condition(FxAssignment assignment,
222: PropertyValueComparator comparator, FxValue<?, ?> value) {
223: renderCondition(comparator.getSql(assignment, value));
224: tables.add(Table.CONTENT);
225: return this ;
226: }
227:
228: /**
229: * Render a condition for a common FxProperty query.
230: *
231: * @param property the property
232: * @param comparator the comparator to be used
233: * @param value the value to be compared against
234: * @return this
235: */
236: public SqlQueryBuilder condition(FxProperty property,
237: PropertyValueComparator comparator, FxValue<?, ?> value) {
238: renderCondition(comparator.getSql(property, value));
239: tables.add(Table.CONTENT);
240: return this ;
241: }
242:
243: /**
244: * Render a condition for a FxProperty query.
245: *
246: * @param propertyName the property name
247: * @param comparator the comparator to be used
248: * @param value the value to be compared against
249: * @return this
250: */
251: public SqlQueryBuilder condition(String propertyName,
252: PropertyValueComparator comparator, FxValue<?, ?> value) {
253: renderCondition(comparator.getSql(Table.CONTENT
254: .getColumnName(propertyName), value));
255: tables.add(Table.CONTENT);
256: return this ;
257: }
258:
259: /**
260: * Render a condition for a FxProperty query.
261: *
262: * @param propertyName the property name
263: * @param comparator the comparator to be used
264: * @param value the value to be compared against
265: * @return this
266: */
267: public SqlQueryBuilder condition(String propertyName,
268: PropertyValueComparator comparator, Object value) {
269: renderCondition(comparator.getSql(Table.CONTENT
270: .getColumnName(propertyName), value));
271: tables.add(Table.CONTENT);
272: return this ;
273: }
274:
275: /**
276: * Performs a fulltext query over all indexed properties against the given text.
277: *
278: * @param value the fulltext query
279: * @return this
280: */
281: public SqlQueryBuilder fulltext(String value) {
282: return condition("*", PropertyValueComparator.EQ, value);
283: }
284:
285: /**
286: * Adds a condition that selects only objects of the given type. Note that this is different
287: * to {@link #filterType(String)}, since filtering is applied on the result that also may contain
288: * other types, but this is a standard search condition that restrains the search result.
289: *
290: * @param typeName the type name to be selected
291: * @return this
292: */
293: public SqlQueryBuilder type(String typeName) {
294: condition("typedef", PropertyValueComparator.EQ, CacheAdmin
295: .getEnvironment().getType(typeName).getId());
296: return this ;
297: }
298:
299: /**
300: * Adds a condition that selects only objects of the given type. Note that this is different
301: * to {@link #filterType(long)}, since filtering is applied on the result that also may contain
302: * other types, but this is a standard search condition that restrains the search result.
303: *
304: * @param typeId the type id to be selected
305: * @return this
306: */
307: public SqlQueryBuilder type(long typeId) {
308: condition("typedef", PropertyValueComparator.EQ, typeId);
309: return this ;
310: }
311:
312: /**
313: * Limits the (sub-)query to children of the given node.
314: *
315: * @param nodeId the root node for the (sub-)query
316: * @return this
317: */
318: public SqlQueryBuilder isChild(long nodeId) {
319: renderCondition("IS CHILD OF " + nodeId);
320: tables.add(Table.CONTENT);
321: return this ;
322: }
323:
324: /**
325: * Limits the (sub-)query to children of the given node.
326: *
327: * @param path the tree path of the node
328: * @return this
329: */
330: public SqlQueryBuilder isChild(String path) {
331: renderCondition("IS CHILD OF "
332: + FxFormatUtils.escapeForSql(path));
333: tables.add(Table.CONTENT);
334: return this ;
335: }
336:
337: /**
338: * Limits the (sub-)query to the direct children of the given node.
339: *
340: * @param nodeId the root node for the (sub-)query
341: * @return this
342: */
343: public SqlQueryBuilder isDirectChild(long nodeId) {
344: renderCondition("IS DIRECT CHILD OF " + nodeId);
345: tables.add(Table.CONTENT);
346: return this ;
347: }
348:
349: /**
350: * Limits the (sub-)query to the direct children of the given node.
351: *
352: * @param path the tree path of the node
353: * @return this
354: */
355: public SqlQueryBuilder isDirectChild(String path) {
356: renderCondition("IS DIRECT CHILD OF "
357: + FxFormatUtils.escapeForSql(path));
358: tables.add(Table.CONTENT);
359: return this ;
360: }
361:
362: /**
363: * Return the conditional ("WHERE") statement(s) contained in this query. If the initial
364: * subquery scope is still open, it will be closed.
365: *
366: * @return the conditional statement(s)
367: */
368: public String getConditions() {
369: if (operatorStack.size() == 1) {
370: // close outmost scope automatically
371: closeSub();
372: }
373: assertValidQuery();
374: frozen = true;
375: return whereConditions.toString();
376: }
377:
378: /**
379: * Return the selected column names.
380: *
381: * @return the selected column names.
382: */
383: public List<String> getColumnNames() {
384: final List<String> names = new ArrayList<String>();
385: if (includeBasicSelects) {
386: names.addAll(Arrays.asList(BASIC_SELECTS));
387: }
388: names.addAll(selectColumns);
389: return names;
390:
391: }
392:
393: /**
394: * Select one or more columns.
395: *
396: * @param columns columns to be selected
397: * @return this
398: */
399: public SqlQueryBuilder select(String... columns) {
400: selectColumns.addAll(Arrays.asList(columns));
401: if (columns.length > 0) {
402: includeBasicSelects = false;
403: }
404: return this ;
405: }
406:
407: /**
408: * Return only objects of the given briefcase.
409: *
410: * @param briefcaseId the briefcase ID
411: * @return this
412: */
413: public SqlQueryBuilder filterBriefcase(long briefcaseId) {
414: return uniqueFilter("briefcase", briefcaseId);
415: }
416:
417: /**
418: * Uses a content type filter for the given content type name.
419: *
420: * @param name the content type name
421: * @return this
422: */
423: public SqlQueryBuilder filterType(String name) {
424: return setTypeFilter(name);
425: }
426:
427: /**
428: * Uses a content type filter for the given content type.
429: *
430: * @param typeId the content type ID, or -1 to disable the content type filter
431: * @return this
432: */
433: public SqlQueryBuilder filterType(long typeId) {
434: return setTypeFilter(typeId != -1 ? typeId : null);
435: }
436:
437: /**
438: * Filter the result set using the given version filter mode (live, max, auto or all).
439: *
440: * @param filter the version filter to be applied
441: * @return this
442: */
443: public SqlQueryBuilder filterVersion(VersionFilter filter) {
444: return uniqueFilter("co.version", filter.name());
445: }
446:
447: private SqlQueryBuilder setTypeFilter(Object value) {
448: final String filter = Table.CONTENT.getAlias() + ".TYPE";
449: removeFilter(filter);
450: if (value != null) {
451: uniqueFilter(filter, value);
452: }
453: return this ;
454: }
455:
456: private SqlQueryBuilder uniqueFilter(String base, Object value) {
457: assertNoFilterStartsWith(base);
458: filters.add(base + "=" + value);
459: return this ;
460: }
461:
462: private void assertNoFilterStartsWith(String substring) {
463: for (String filter : filters) {
464: if (filter.startsWith(substring)) {
465: throw new FxInvalidParameterException("FILTER",
466: "ex.sqlQueryBuilder.filter.unique", filter,
467: substring).asRuntimeException();
468: }
469: }
470: }
471:
472: private SqlQueryBuilder removeFilter(String name) {
473: for (Iterator<String> iterator = filters.iterator(); iterator
474: .hasNext();) {
475: if (iterator.next().startsWith(name + "=")) {
476: iterator.remove();
477: return this ;
478: }
479: }
480: return this ;
481: }
482:
483: /**
484: * Order the results by the given column. Any previously set order by columns
485: * are removed.
486: *
487: * @param column the column to be sorted
488: * @param direction the sort direction
489: * @return this
490: */
491: public SqlQueryBuilder orderBy(String column,
492: SortDirection direction) {
493: orderBy.clear();
494: final int columnIndex = FxSharedUtils.getColumnIndex(
495: getColumnNames(), column);
496: if (columnIndex == -1) {
497: throw new FxInvalidParameterException("column",
498: "ex.sqlQueryBuilder.column.invalid", column)
499: .asRuntimeException();
500: }
501: orderBy.add(getColumnNames().get(columnIndex - 1)
502: + direction.getSqlSuffix());
503: return this ;
504: }
505:
506: /**
507: * Order the results by the given column (1-based). Any previously set order by columns
508: * are removed.
509: *
510: * @param columnIndex the 1-based index of the column to be sorted
511: * @param direction the sort direction
512: * @return this
513: */
514: public SqlQueryBuilder orderBy(int columnIndex,
515: SortDirection direction) {
516: orderBy.clear();
517: orderBy(getColumnNames().get(columnIndex - 1), direction);
518: return this ;
519: }
520:
521: /**
522: * Returns true if the predefined columns are selected by the condition.
523: *
524: * @return true if the predefined columns are selected by the condition.
525: */
526: public boolean isIncludeBasicSelects() {
527: return includeBasicSelects;
528: }
529:
530: /**
531: * Enables or disables the default select columns. By default they are included
532: * only if no select clause was specified by {@link #select(String[])}.
533: *
534: * @param includeBasicSelects true if the pre-defined selects should be included
535: * @return this
536: */
537: public SqlQueryBuilder setIncludeBasicSelects(
538: boolean includeBasicSelects) {
539: this .includeBasicSelects = includeBasicSelects;
540: return this ;
541: }
542:
543: /**
544: * Create a new subquery scope with the given operator. Subsequent conditions will
545: * be added to that scope. Not that all subscopes (except the first one) must be closed
546: * before the query can be generated.
547: *
548: * @param operator the operator to be used inside the new scope
549: * @return this
550: */
551: public SqlQueryBuilder enterSub(Operator operator) {
552: assertNotFrozen();
553: if (expressionCounter.size() > 0
554: && expressionCounter.peek() > 0) {
555: whereConditions.append(" ").append(operatorStack.peek())
556: .append(" ");
557: }
558: whereConditions.append("(");
559: operatorStack.add(operator);
560: expressionCounter.add(0);
561: return this ;
562: }
563:
564: /**
565: * Shorthand for {@link #enterSub(Operator)} with {@link Operator#AND} as the operator.
566: * @return this
567: */
568: public SqlQueryBuilder andSub() {
569: return enterSub(Operator.AND);
570: }
571:
572: /**
573: * Shorthand for {@link #enterSub(Operator)} with {@link Operator#OR} as the operator.
574: * @return this
575: */
576: public SqlQueryBuilder orSub() {
577: return enterSub(Operator.OR);
578: }
579:
580: /**
581: * Close a scope.
582: *
583: * @return this
584: */
585: public SqlQueryBuilder closeSub() {
586: assertStackNotEmpty();
587: assertNotFrozen();
588: operatorStack.pop();
589: expressionCounter.pop();
590: if (expressionCounter.size() > 0) {
591: expressionCounter.push(expressionCounter.pop() + 1);
592: }
593: whereConditions.append(')');
594: if (operatorStack.isEmpty()) {
595: // no more conditions can be rendered after closing the last scope
596: frozen = true;
597: }
598: return this ;
599: }
600:
601: /**
602: * Return true if this query builder is frozen and cannot be further modified.
603: * A query builder freezes when the query or the query conditions are queried
604: * with the {@link #getQuery()} and {@link #getConditions()} methods.
605: * A frozen query builder does not guarantee, however, that the query itself is valid.
606: *
607: * @return true if this query builder is frozen and cannot be further modified.
608: */
609: public boolean isFrozen() {
610: return frozen;
611: }
612:
613: /**
614: * Return the result location used in this query builder.
615: *
616: * @return the result location used in this query builder.
617: */
618: public ResultLocation getLocation() {
619: return location;
620: }
621:
622: /**
623: * Return the result view type used in this query builder.
624: *
625: * @return the result view type used in this query builder.
626: */
627: public ResultViewType getViewType() {
628: return viewType;
629: }
630:
631: public SqlQueryBuilder viewType(ResultViewType viewType) {
632: this .viewType = viewType;
633: return this ;
634: }
635:
636: public int getStartRow() {
637: return startRow;
638: }
639:
640: public SqlQueryBuilder startRow(int startRow) {
641: this .startRow = startRow;
642: return this ;
643: }
644:
645: public int getMaxRows() {
646: return maxRows;
647: }
648:
649: public SqlQueryBuilder maxRows(int maxRows) {
650: this .maxRows = maxRows;
651: return this ;
652: }
653:
654: /**
655: * Convenience method for executing a search query using this query builder.
656: *
657: * @return the search result for the current query
658: * @throws FxApplicationException if the search failed
659: */
660: public FxResultSet getResult() throws FxApplicationException {
661: return EJBLookup.getSearchEngine().search(getQuery(), startRow,
662: maxRows != -1 ? maxRows : null, null, location,
663: viewType);
664: }
665:
666: /**
667: * Render the given condition in the current scope.
668: *
669: * @param condition the condition to be rendered.
670: */
671: private void renderCondition(String condition) {
672: if (operatorStack.isEmpty() && whereConditions.length() == 0) {
673: // implicitly open first scope
674: andSub();
675: }
676: assertStackNotEmpty();
677: assertNotFrozen();
678: if (expressionCounter.peek() == 0) {
679: // first condition, open scope
680: /* int level = expressionCounter.size() - 1;
681: while (level >= 0 && expressionCounter.get(level) == 0) {
682: // nested condition, add operator
683: if (level > 0 && expressionCounter.get(level - 1) > 0) {
684: whereConditions.append(' ')
685: .append(operatorStack.get(level - 1).getSqlRepresentation())
686: .append(' ');
687: }
688: whereConditions.append('(');
689: level--;
690: }*/
691: } else {
692: // render operator between nodes
693: whereConditions.append(' ').append(
694: operatorStack.peek().getSqlRepresentation())
695: .append(' ');
696: }
697: whereConditions.append(condition);
698: expressionCounter.add(expressionCounter.pop() + 1);
699: }
700:
701: private void assertStackNotEmpty() {
702: if (operatorStack.isEmpty()) {
703: throw new FxInvalidStateException(
704: "ex.sqlQueryBuilder.operatorStack.empty")
705: .asRuntimeException();
706: }
707: if (expressionCounter.isEmpty()) {
708: throw new FxInvalidStateException(
709: "ex.sqlQueryBuilder.counterStack.empty")
710: .asRuntimeException();
711: }
712: }
713:
714: private void assertValidQuery() {
715: if (!operatorStack.isEmpty() || !expressionCounter.isEmpty()) {
716: throw new FxInvalidStateException(
717: "ex.sqlQueryBuilder.query.incomplete",
718: operatorStack.size()).asRuntimeException();
719: }
720: }
721:
722: private void assertNotFrozen() {
723: if (frozen) {
724: throw new FxInvalidStateException(
725: "ex.sqlQueryBuilder.query.frozen")
726: .asRuntimeException();
727: }
728: }
729: }
|