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.core.search;
034:
035: import com.flexive.core.Database;
036: import com.flexive.core.DatabaseConst;
037: import com.flexive.core.search.mysql.MySQLDataFilter;
038: import com.flexive.core.search.mysql.MySQLDataSelector;
039: import com.flexive.shared.CacheAdmin;
040: import com.flexive.shared.FxContext;
041: import com.flexive.shared.FxLanguage;
042: import com.flexive.shared.FxSharedUtils;
043: import com.flexive.shared.configuration.DBVendor;
044: import com.flexive.shared.exceptions.FxApplicationException;
045: import com.flexive.shared.exceptions.FxSqlSearchException;
046: import com.flexive.shared.interfaces.BriefcaseEngine;
047: import com.flexive.shared.interfaces.ResultPreferencesEngine;
048: import com.flexive.shared.interfaces.SequencerEngine;
049: import com.flexive.shared.interfaces.TreeEngine;
050: import com.flexive.shared.search.*;
051: import com.flexive.shared.structure.FxEnvironment;
052: import com.flexive.shared.structure.FxType;
053: import com.flexive.sqlParser.*;
054:
055: import java.sql.Connection;
056: import java.sql.ResultSet;
057: import java.sql.SQLException;
058: import java.sql.Statement;
059: import java.util.ArrayList;
060:
061: import org.apache.commons.logging.Log;
062: import org.apache.commons.logging.LogFactory;
063: import org.apache.commons.lang.StringUtils;
064:
065: /**
066: * The main search engine class
067: *
068: * @author Gregor Schober (gregor.schober@flexive.com), UCS - unique computing solutions gmbh (http://www.ucs.at)
069: * @version $Rev: 256 $
070: */
071: public class SqlSearch {
072: private static final Log LOG = LogFactory.getLog(SqlSearch.class);
073:
074: private final int startIndex;
075: private final int fetchRows;
076: private final String query;
077: private final ResultLocation location;
078: private final ResultViewType viewType;
079: private final SequencerEngine seq;
080: private final BriefcaseEngine briefcase;
081: private final TreeEngine treeEngine;
082:
083: private FxStatement statement;
084: private FxType typeFilter;
085: private PropertyResolver pr;
086: private int parserExecutionTime = -1;
087: private long searchId = -1;
088: private String cacheTbl;
089: private final FxSQLSearchParams params;
090: private boolean hasWildcard = false;
091: private FxEnvironment environment;
092: private final ResultPreferencesEngine conf;
093: private FxLanguage language;
094:
095: /**
096: * Ctor
097: *
098: * @param seq reference to the sequencer
099: * @param briefcase reference to the briefcase engine
100: * @param treeEngine reference to the tree engine
101: * @param query the query to execute
102: * @param startIndex the start index (0 based)
103: * @param maxFetchRows the number of rows to return with the resultset, or null to fetch all rows
104: * @param params all aditional search parameters
105: * @param conf the result set configuration
106: * @param location the location that started the search
107: * @param viewType the view type @throws com.flexive.shared.exceptions.FxSqlSearchException
108: * if the search failed
109: * @throws com.flexive.shared.exceptions.FxSqlSearchException if the search engine could not be initialized
110: */
111: public SqlSearch(SequencerEngine seq, BriefcaseEngine briefcase,
112: TreeEngine treeEngine, String query, int startIndex,
113: Integer maxFetchRows, FxSQLSearchParams params,
114: ResultPreferencesEngine conf, ResultLocation location,
115: ResultViewType viewType) throws FxSqlSearchException {
116: FxSharedUtils.checkParameterEmpty(query, "query");
117: // Init
118: this .seq = seq;
119: this .briefcase = briefcase;
120: this .treeEngine = treeEngine;
121: this .conf = conf;
122: this .environment = CacheAdmin.getEnvironment();
123: this .params = params;
124: this .startIndex = startIndex;
125: this .fetchRows = maxFetchRows == null ? Integer.MAX_VALUE
126: : maxFetchRows;
127: this .query = query;
128: this .language = FxContext.get().getTicket().getLanguage();
129: this .location = location;
130: this .viewType = viewType;
131:
132: // Parameter checks
133: if (this .startIndex < 0) {
134: throw new FxSqlSearchException(LOG,
135: "ex.sqlSearch.parameter.invalidStartIndex",
136: startIndex);
137: }
138:
139: if (maxFetchRows != null) {
140: if (maxFetchRows < 1 && maxFetchRows != -1) {
141: throw new FxSqlSearchException(LOG,
142: "ex.sqlSearch.parameter.fetchRows",
143: maxFetchRows);
144: }
145: }
146: }
147:
148: /**
149: * Returns the content type filter, or null if the filter is not set.
150: *
151: * @return the content type filter, or null
152: */
153: public FxType getTypeFilter() {
154: return typeFilter;
155: }
156:
157: /**
158: * Returns the language of this search.
159: *
160: * @return the language of this search
161: */
162: public FxLanguage getLanguage() {
163: return language;
164: }
165:
166: /**
167: * Executes the search.
168: *
169: * @return the resultset
170: * @throws FxSqlSearchException if the search failed
171: */
172: public FxResultSet executeQuery() throws FxSqlSearchException {
173: parseQuery();
174:
175: // Check if the statement will produce any resultset at all
176: if (statement.getType() == FxStatement.Type.EMPTY) {
177: return new FxResultSetImpl(statement,
178: this .parserExecutionTime, 0, startIndex, fetchRows,
179: location, viewType, null, -1, -1);
180: }
181:
182: // Execute select
183: Statement stmt = null;
184: Connection con = null;
185: FxResultSetImpl fx_result = null;
186: final long startTime = java.lang.System.currentTimeMillis();
187: DataSelector ds = null;
188: DataFilter df = null;
189: String selectSql = null;
190: try {
191:
192: // Init
193: switch (params.getCacheMode()) {
194: case ON:
195: cacheTbl = DatabaseConst.TBL_SEARCHCACHE_PERM;
196: searchId = seq
197: .getId(SequencerEngine.System.SEARCHCACHE_PERM);
198: break;
199: case OFF:
200: case READ_ONLY:
201: cacheTbl = DatabaseConst.TBL_SEARCHCACHE_MEMORY;
202: searchId = seq
203: .getId(SequencerEngine.System.SEARCHCACHE_MEMORY);
204: break;
205: default:
206: // Can never happen
207: cacheTbl = null;
208: }
209:
210: pr = new PropertyResolver();
211: con = Database.getDbConnection();
212:
213: // Find all matching objects
214: df = getDataFilter(con);
215: df.build();
216:
217: // Wildcard handling depending on the found entries
218: if (this .hasWildcard) {
219: replaceWildcard(df);
220: }
221: if (statement.getOrderByValues().isEmpty()) {
222: // add user-defined order by
223: final ResultPreferences resultPreferences = getResultPreferences(df);
224: for (ResultOrderByInfo column : resultPreferences
225: .getOrderByColumns()) {
226: try {
227: statement.addOrderByValue(new OrderByValue(
228: column.getColumnName(),
229: column.getDirection().equals(
230: SortDirection.ASCENDING)));
231: } catch (SqlParserException e) {
232: if (LOG.isDebugEnabled()) {
233: LOG
234: .debug("Ignoring user preferences column "
235: + column
236: + " since it was not selected.");
237: }
238: }
239: }
240:
241: }
242:
243: // If specified create a briefcase with the found data
244: long createdBriefcaseId = -1;
245: if (params.getWillCreateBriefcase()) {
246: createdBriefcaseId = copyToBriefcase(con);
247: }
248:
249: // Select all desired rows for the resultset
250: ds = getDataSelector();
251: selectSql = ds.build(con);
252:
253: stmt = con.createStatement();
254: stmt.executeUpdate("set @rownr=1;");
255: stmt.close();
256:
257: stmt = con.createStatement();
258: stmt.setQueryTimeout(params.getQueryTimeout());
259:
260: // Fetch the result
261: ResultSet rs = stmt.executeQuery(selectSql);
262: int dbSearchTime = (int) (java.lang.System
263: .currentTimeMillis() - startTime);
264: fx_result = new FxResultSetImpl(statement,
265: this .parserExecutionTime, dbSearchTime, startIndex,
266: fetchRows, location, viewType,
267: df.getContentTypes(),
268: getTypeFilter() != null ? getTypeFilter().getId()
269: : -1, createdBriefcaseId);
270: fx_result.setTotalRowCount(df.getFoundEntries());
271: fx_result.setTruncated(df.isTruncated());
272:
273: final long fetchStart = java.lang.System
274: .currentTimeMillis();
275: while (rs.next()) {
276: Object[] row = new Object[pr.getResultSetColumns()
277: .size()];
278: int i = 0;
279: for (PropertyEntry entry : pr.getResultSetColumns()) {
280: //Object val =getValue(rs,entry);
281: Object val = entry.getResultValue(rs, language);
282: row[i] = val;
283: i++;
284: }
285: fx_result.addRow(row);
286: if (fx_result.getRowCount() == fetchRows) {
287: // Maximum fetch size reached, stop
288: break;
289: }
290: }
291: int timeSpent = (int) (java.lang.System.currentTimeMillis() - fetchStart);
292: fx_result.setFetchTime(timeSpent);
293: return fx_result;
294: } catch (FxSqlSearchException exc) {
295: throw exc;
296: } catch (SQLException exc) {
297: throw new FxSqlSearchException(LOG, exc,
298: "ex.sqlSearch.failed", exc.getMessage(), query,
299: selectSql);
300: } catch (Exception e) {
301: throw new FxSqlSearchException(LOG, e,
302: "ex.sqlSearch.failed", e.getMessage(), query,
303: selectSql);
304: } finally {
305: try {
306: if (ds != null)
307: ds.cleanup(con);
308: } catch (Throwable t) {/*ignore*/
309: }
310: try {
311: if (df != null)
312: df.cleanup();
313: } catch (Throwable t) {/*ignore*/
314: }
315: Database.closeObjects(SqlSearch.class, con, stmt);
316: if (fx_result != null) {
317: int timeSpent = (int) (java.lang.System
318: .currentTimeMillis() - startTime);
319: fx_result.setTotalTime(timeSpent);
320: }
321: }
322: }
323:
324: private void parseQuery() throws FxSqlSearchException {
325: // Parse the statement
326: try {
327: final long start = java.lang.System.currentTimeMillis();
328: statement = FxStatement.parseSql(query);
329: this .hasWildcard = this .hasWildcard();
330: this .parserExecutionTime = (int) (java.lang.System
331: .currentTimeMillis() - start);
332: } catch (SqlParserException pe) {
333: // Catch the parse exception and convert it to an localized one
334: throw new FxSqlSearchException(LOG, pe);
335: } catch (Throwable t) {
336: throw new FxSqlSearchException(LOG, t,
337: "ex.sqlSearch.parser.error", t.getMessage(), query);
338: }
339:
340: // Process content type filter
341: if (statement.hasContentTypeFilter()) {
342: String type = statement.getContentTypeFilter();
343: try {
344: typeFilter = StringUtils.isNumeric(type) ? environment
345: .getType(Long.parseLong(type)) : environment
346: .getType(type);
347: } catch (Throwable t) {
348: throw new FxSqlSearchException(
349: LOG,
350: t,
351: "ex.sqlSearch.filter.invalidContentTypeFilterValue",
352: type);
353: }
354: } else {
355: typeFilter = null;
356: }
357: }
358:
359: private long copyToBriefcase(Connection con)
360: throws FxSqlSearchException {
361: FxSQLSearchParams.BriefcaseCreationData bcd = params
362: .getBriefcaseCreationData();
363: Statement stmt = null;
364: try {
365: // Create the briefcase
366: long bid = briefcase.create(bcd.getName(), bcd
367: .getDescription(), bcd.getAclId());
368: stmt = con.createStatement();
369: stmt.addBatch("SET @pos=0;");
370: String sSql = "insert into "
371: + DatabaseConst.TBL_BRIEFCASE_DATA
372: + "(BRIEFCASE_ID,POS,ID,AMOUNT) " + "(select "
373: + bid + ",@pos:=@pos+1 pos,data.id,1 from "
374: + "(SELECT DISTINCT data2.id FROM "
375: + getCacheTable() + " data2 WHERE data2.search_id="
376: + getSearchId() + ") data)";
377: stmt.addBatch(sSql);
378: stmt.executeBatch();
379: return bid;
380: } catch (Throwable t) {
381: throw new FxSqlSearchException(LOG, t,
382: "ex.sqlSearch.err.failedToBuildBriefcase", bcd
383: .getName());
384: } finally {
385: Database.closeObjects(MySQLDataSelector.class, null, stmt);
386: }
387: }
388:
389: /**
390: * Get the DataSelector for the sql searchengine based on the used DB
391: *
392: * @return DataSelector the data selecttor implementation
393: * @throws FxSqlSearchException if the function fails
394: */
395: public DataSelector getDataSelector() throws FxSqlSearchException {
396: DBVendor vendor;
397: try {
398: vendor = Database.getDivisionData().getDbVendor();
399: switch (vendor) {
400: case MySQL:
401: return new MySQLDataSelector(this );
402: default:
403: throw new FxSqlSearchException(LOG,
404: "ex.db.selector.undefined", vendor);
405: }
406: } catch (SQLException e) {
407: throw new FxSqlSearchException(LOG,
408: "ex.db.vendor.notFound", FxContext.get()
409: .getDivisionId());
410: }
411: }
412:
413: /**
414: * Get the DataSelector for the sql searchengine based on the used DB
415: *
416: * @param con the connection to use
417: * @return DataSelector the data selecttor implementation
418: * @throws FxSqlSearchException if the function fails
419: */
420: public DataFilter getDataFilter(Connection con)
421: throws FxSqlSearchException {
422: DBVendor vendor;
423: try {
424: vendor = Database.getDivisionData().getDbVendor();
425: switch (vendor) {
426: case MySQL:
427: return new MySQLDataFilter(con, this );
428: default:
429: throw new FxSqlSearchException(LOG,
430: "ex.db.filter.undefined", vendor);
431: }
432: } catch (SQLException e) {
433: throw new FxSqlSearchException(LOG,
434: "ex.db.vendor.notFound", FxContext.get()
435: .getDivisionId());
436: }
437: }
438:
439: public int getStartIndex() {
440: return startIndex;
441: }
442:
443: public int getFetchRows() {
444: return fetchRows;
445: }
446:
447: public FxStatement getFxStatement() {
448: return statement;
449: }
450:
451: public PropertyResolver getPropertyResolver() {
452: return pr;
453: }
454:
455: public FxSQLSearchParams getParams() {
456: return this .params;
457: }
458:
459: public String getCacheTable() {
460: return cacheTbl;
461: }
462:
463: /**
464: * Returns the unique id of this search.
465: *
466: * @return the unique id of this search
467: */
468: public long getSearchId() {
469: return searchId;
470: }
471:
472: private boolean hasWildcard() throws FxSqlSearchException {
473: // Find out if we have to deal with a wildcard
474: boolean hasWildcard = false;
475: for (SelectedValue value : statement.getSelectedValues()) {
476: if (value.getValue() instanceof Property) {
477: Property prop = ((Property) value.getValue());
478: if (prop.isWildcard()) {
479: if (hasWildcard) {
480: // Only one wildcard may be used per statement
481: throw new FxSqlSearchException(LOG,
482: "ex.sqlSearch.onlyOneWildcardPermitted");
483: }
484: hasWildcard = true;
485: }
486: }
487: }
488: return hasWildcard;
489: }
490:
491: /**
492: * Replaces the wildcard in the fx_statement by the defined properties.
493: *
494: * @param df the datafilter
495: * @throws FxSqlSearchException if the function fails
496: */
497: private void replaceWildcard(DataFilter df)
498: throws FxSqlSearchException {
499:
500: try {
501: ResultPreferences prefs = getResultPreferences(df);
502: ArrayList<SelectedValue> selValues = new ArrayList<SelectedValue>(
503: (statement.getSelectedValues().size() - 1)
504: + prefs.getSelectedColumns().size());
505: for (SelectedValue _value : statement.getSelectedValues()) {
506: if (_value.getValue() instanceof Property
507: && ((Property) _value.getValue()).isWildcard()) {
508: Property wildcard = (Property) _value.getValue();
509: // Wildcard, replace it with the correct values
510: for (ResultColumnInfo nfo : prefs
511: .getSelectedColumns()) {
512: Property newProp = new Property(wildcard
513: .getTableAlias(),
514: nfo.getPropertyName(), nfo.getSuffix());
515: SelectedValue newSel = new SelectedValue(
516: newProp, nfo.getLabel(this .environment));
517: selValues.add(newSel);
518: }
519: } else {
520: // Normal property, use it as is
521: selValues.add(_value);
522: }
523: }
524: statement.setSelectedValues(selValues);
525: } catch (Throwable t) {
526: throw new FxSqlSearchException(LOG, t,
527: "ex.sqlSearch.wildcardProcessingFailed");
528: }
529: }
530:
531: private ResultPreferences getResultPreferences(DataFilter df)
532: throws FxApplicationException {
533: long cType = getTypeFilter() != null ?
534: // Type filter: only one type is contained in the search, use it
535: getTypeFilter().getId()
536: :
537: // No Type filter: see if we got only one type in the result, or use the default for all types
538: df.getContentTypes().size() == 1 ? df.getContentTypes()
539: .get(0).getContentTypeId() : -1;
540:
541: //ArrayList<SelectedValue> selValues = new ArrayList<SelectedValue>(statement.getSelectedValues().size()+25);
542: return conf.load(cType, viewType, location);
543: }
544:
545: public String getQuery() {
546: return query;
547: }
548:
549: public TreeEngine getTreeEngine() {
550: return treeEngine;
551: }
552: }
|