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.mysql;
034:
035: import com.flexive.core.DatabaseConst;
036: import com.flexive.core.search.*;
037: import com.flexive.shared.CacheAdmin;
038: import com.flexive.shared.FxArrayUtils;
039: import com.flexive.shared.FxContext;
040: import com.flexive.shared.search.SortDirection;
041: import com.flexive.shared.exceptions.FxSqlSearchException;
042: import com.flexive.shared.tree.FxTreeNode;
043: import com.flexive.sqlParser.*;
044: import org.apache.commons.logging.Log;
045: import org.apache.commons.logging.LogFactory;
046:
047: import java.sql.Connection;
048: import java.util.*;
049:
050: /**
051: * MySQL specific data selector
052: *
053: * @author Gregor Schober (gregor.schober@flexive.com), UCS - unique computing solutions gmbh (http://www.ucs.at)
054: * @version $Rev: 256 $
055: */
056: public class MySQLDataSelector extends DataSelector {
057: private static final transient Log LOG = LogFactory
058: .getLog(MySQLDataSelector.class);
059:
060: /**
061: * All field selectors supported by this implementation
062: */
063: private static final Map<String, FieldSelector> SELECTORS = new HashMap<String, FieldSelector>();
064:
065: static {
066: SELECTORS.put("MANDATOR", new MySQLGenericSelector(
067: DatabaseConst.TBL_MANDATORS, "id"));
068: SELECTORS.put("CREATED_BY", new MySQLGenericSelector(
069: DatabaseConst.TBL_ACCOUNTS, "id"));
070: SELECTORS.put("MODIFIED_BY", new MySQLGenericSelector(
071: DatabaseConst.TBL_ACCOUNTS, "id"));
072: SELECTORS.put("ACL", new MySQLACLSelector());
073: SELECTORS.put("STEP", new MySQLStepSelector());
074: }
075:
076: private static final String[] CONTENT_DIRECT_SELECT = { "ID",
077: "VERSION", "MAINLANG" };
078: private static final String[] CONTENT_DIRECT_SELECT_PROP = { "ID",
079: "VER", "MAINLANG" };
080: private static final String FILTER_ALIAS = "filter";
081: private static final String SUBSEL_ALIAS = "sub";
082:
083: private SqlSearch search;
084:
085: /**
086: * Constructor.
087: *
088: * @param search the search object
089: */
090: public MySQLDataSelector(SqlSearch search) {
091: this .search = search;
092: }
093:
094: /**
095: * {@inheritDoc}
096: */
097: @Override
098: public Map<String, FieldSelector> getSelectors() {
099: return SELECTORS;
100: }
101:
102: /**
103: * {@inheritDoc}
104: */
105: @Override
106: public String build(final Connection con)
107: throws FxSqlSearchException {
108: StringBuffer select = new StringBuffer();
109: buildColumnSelectList(select);
110: if (LOG.isDebugEnabled()) {
111: LOG.debug(search.getFxStatement().printDebug());
112: }
113: return select.toString();
114: }
115:
116: /**
117: * {@inheritDoc}
118: */
119: @Override
120: public void cleanup(Connection con) throws FxSqlSearchException {
121: // nothing to do
122: }
123:
124: /**
125: * Builds the column select.
126: * <p/>
127: * Example result: "x.oid,x.ver,(select ..xx.. from ...),..."
128: *
129: * @param select the stringbuffer to write to
130: * @throws FxSqlSearchException if the function fails
131: */
132: private void buildColumnSelectList(final StringBuffer select)
133: throws FxSqlSearchException {
134: // Build all value selectors
135: int pos = 0;
136: final SubSelectValues values[] = new SubSelectValues[search
137: .getFxStatement().getSelectedValues().size()];
138: for (SelectedValue selectedValue : search.getFxStatement()
139: .getSelectedValues()) {
140: // Prepare all values
141: final Value value = selectedValue.getValue();
142: PropertyEntry entry = null;
143: if (value instanceof Property) {
144: final PropertyResolver pr = search
145: .getPropertyResolver();
146: entry = pr.get(search.getFxStatement(),
147: (Property) value);
148: pr.addResultSetColumn(entry);
149: }
150: values[pos] = selectFromTbl(entry, value, pos);
151: pos++;
152: }
153:
154: // Build the final select statement
155:
156: select.append("SELECT \n");
157: select.append(FILTER_ALIAS).append(".rownr,").append(
158: FILTER_ALIAS).append(".id, ").append(FILTER_ALIAS)
159: .append(".ver\n");
160: for (SubSelectValues ssv : values) {
161: for (SubSelectValues.Item item : ssv.getItems()) {
162: if (ssv.isSorted() && item.isOrderBy()) {
163: select.append(",").append(FILTER_ALIAS).append(".")
164: .append(item.getAlias()).append("\n");
165: } else {
166: select.append(",").append(item.getSelect()).append(
167: " ").append(item.getAlias()).append("\n");
168: }
169: }
170: }
171: select.append("FROM (");
172: select.append(buildSourceTable(values));
173: select.append(") ").append(FILTER_ALIAS).append(" ORDER BY 1");
174: }
175:
176: /**
177: * Builds the source table.
178: *
179: * @param values the selected values
180: * @return a select statement which builds the source table
181: */
182: private String buildSourceTable(final SubSelectValues[] values) {
183: // Prepare type filter
184: final String typeFilter = search.getTypeFilter() == null ? ""
185: : " and " + FILTER_ALIAS + ".tdef="
186: + search.getTypeFilter().getId() + " ";
187:
188: final StringBuilder orderBy = new StringBuilder();
189: String orderByNumbers = "";
190: int orderByPos = 5;
191: orderBy
192: .append("select @rownr:=@rownr+1 rownr,")
193: .append(
194: ("concat(concat(concat(concat(concat(t.name,'[@pk='),"
195: + FILTER_ALIAS
196: + ".id),'.'),"
197: + FILTER_ALIAS + ".ver),']') xpathPref,"))
198: .append(FILTER_ALIAS).append(".id,").append(
199: FILTER_ALIAS).append(".ver\n");
200:
201: for (SubSelectValues ssv : values) {
202: if (ssv.isSorted()) {
203: for (SubSelectValues.Item item : ssv.getItems()) {
204: if (item.isOrderBy()) {
205: orderBy.append(",").append(item.getSelect())
206: .append(" ").append(item.getAlias())
207: .append("\n");
208: orderByNumbers += (orderByNumbers.length() == 0 ? ""
209: : ",")
210: + orderByPos
211: + " "
212: + (ssv.isSortedAscending() ? "asc"
213: : "desc");
214: } else {
215: orderBy.append(",null\n");
216: }
217: orderByPos++;
218: }
219: }
220: }
221: orderBy.append(("FROM " + search.getCacheTable() + " filter, "
222: + DatabaseConst.TBL_STRUCT_TYPES + " t "
223: + "WHERE search_id=" + search.getSearchId() + " AND "
224: + FILTER_ALIAS + ".tdef=t.id " + typeFilter + " "));
225:
226: // No order by specified = order by id and version
227: if (orderByNumbers.length() == 0) {
228: orderByNumbers = "2 asc, 3 asc";
229: }
230: orderBy.append(("ORDER BY " + orderByNumbers));
231:
232: // Evaluate the order by, then limit the result by the desired range if needed
233: if (search.getStartIndex() > 0
234: && search.getFetchRows() < Integer.MAX_VALUE) {
235: return "SELECT * FROM (" + orderBy + ") tmp LIMIT "
236: + search.getStartIndex() + ","
237: + search.getFetchRows();
238: }
239: return orderBy.toString();
240:
241: }
242:
243: /**
244: * Build the subselect query needed to get any data from the CONTENT table.
245: *
246: * @param entry the entry to select
247: * @param prop the property
248: * @param resultPos the position of the property in the select statement
249: * @return the SubSelectValues
250: * @throws FxSqlSearchException if anything goes wrong
251: */
252: private SubSelectValues selectFromTbl(PropertyEntry entry,
253: Value prop, int resultPos) throws FxSqlSearchException {
254: final SubSelectValues result = new SubSelectValues(resultPos,
255: getSortDirection(resultPos));
256: if (prop instanceof Constant || entry == null) {
257: result
258: .addItem(prop.getValue().toString(), resultPos,
259: false);
260: } else if (entry.getType() == PropertyEntry.Type.NODE_POSITION) {
261: long root = FxContext.get().getNodeId();
262: if (root == -1)
263: root = FxTreeNode.ROOT_NODE;
264: final String sel = "(select tree_nodeIndex(" + root + ","
265: + FILTER_ALIAS + ".id,false))"; // TODO: LIVE/EDIT
266: result.addItem(sel, resultPos, false);
267: } else if (entry.getType() == PropertyEntry.Type.PATH) {
268: final long propertyId = CacheAdmin.getEnvironment()
269: .getProperty("CAPTION").getId();
270: final String sel = "(select tree_FTEXT1024_Paths("
271: + FILTER_ALIAS + ".id,"
272: + search.getLanguage().getId() + "," + propertyId
273: + ",false))"; // TODO: LIVE/EDIT
274: result.addItem(sel, resultPos, false);
275: } else {
276: switch (entry.getTableType()) {
277: case T_CONTENT:
278: for (String column : entry.getReadColumns()) {
279: final int directSelect = FxArrayUtils.indexOf(
280: CONTENT_DIRECT_SELECT, column, true);
281: if (directSelect > -1) {
282: final String val = FILTER_ALIAS
283: + "."
284: + CONTENT_DIRECT_SELECT_PROP[directSelect];
285: result.addItem(val, resultPos, false);
286: } else {
287: final String val = "(SELECT " + SUBSEL_ALIAS
288: + "." + column + " FROM "
289: + DatabaseConst.TBL_CONTENT + " "
290: + SUBSEL_ALIAS + " WHERE "
291: + SUBSEL_ALIAS + ".id=" + FILTER_ALIAS
292: + ".id AND " + SUBSEL_ALIAS + ".ver="
293: + FILTER_ALIAS + ".ver)";
294: result.addItem(val, resultPos, false);
295: }
296: }
297: break;
298: case T_CONTENT_DATA:
299: for (String column : entry.getReadColumns()) {
300: final String val = _hlp_content_data(column, entry);
301: result.addItem(val, resultPos, false);
302: }
303: String xpath = "concat(filter.xpathPref,"
304: + _hlp_content_data("XPATHMULT", entry) + ")";
305: result.addItem(xpath, resultPos, true);
306: break;
307: default:
308: throw new FxSqlSearchException(LOG,
309: "ex.sqlSearch.table.typeNotSupported", entry
310: .getTableName());
311: }
312: }
313:
314: return result.prepare(this , prop, entry);
315: }
316:
317: /**
318: * Returns the {@link SortDirection} for the given column index.
319: *
320: * @param pos the position to check
321: * @return the {@link SortDirection} for the given column index.
322: */
323: private SortDirection getSortDirection(int pos) {
324: final List<OrderByValue> obvs = search.getFxStatement()
325: .getOrderByValues();
326: for (OrderByValue obv : obvs) {
327: if (obv.getColumnIndex() == pos) {
328: return obv.isAscending() ? SortDirection.ASCENDING
329: : SortDirection.DESCENDING;
330: }
331: }
332: return SortDirection.UNSORTED;
333: }
334:
335: /**
336: * Helper function.
337: *
338: * @param column the column that is being procesed
339: * @param entry the entry
340: * @return the subselect string for the value
341: */
342: private String _hlp_content_data(String column, PropertyEntry entry) {
343: // TODO: lang fallback
344: return "(SELECT "
345: + SUBSEL_ALIAS
346: + "."
347: + column
348: + " FROM "
349: + DatabaseConst.TBL_CONTENT_DATA
350: + " "
351: + SUBSEL_ALIAS
352: + " WHERE "
353: + SUBSEL_ALIAS
354: + ".id="
355: + FILTER_ALIAS
356: + ".id AND "
357: + SUBSEL_ALIAS
358: + ".ver="
359: + FILTER_ALIAS
360: + ".ver AND "
361: + (entry.isAssignment() ? "ASSIGN="
362: + entry.getAssignment().getId() : "TPROP="
363: + entry.getProperty().getId()) + " LIMIT 1 "
364: + ")";
365: }
366:
367: }
|