001: /*
002:
003: This software is OSI Certified Open Source Software.
004: OSI Certified is a certification mark of the Open Source Initiative.
005:
006: The license (Mozilla version 1.0) can be read at the MMBase site.
007: See http://www.MMBase.org/license
008:
009: */
010: package org.mmbase.storage.search.implementation.database;
011:
012: import org.mmbase.storage.search.*;
013: import org.mmbase.util.logging.*;
014: import java.util.*;
015: import org.mmbase.module.corebuilders.RelDef;
016: import org.mmbase.module.corebuilders.TypeRel;
017:
018: /**
019: * The PostgreSQL query handler, implements {@link
020: * org.mmbase.storage.search.implementation.database.SqlHandler SqlHandler}
021: * for standard PostgreSql functionality.
022: * <br />
023: * Derived from {@link BasicSqlHandler BasicSqlHandler}, overrides
024: * <ul>
025: * <li>{@link #toSql toSql()}, implements {@link
026: * org.mmbase.storage.search.SearchQueryHandler#FEATURE_MAX_NUMBER
027: * FEATURE_MAX_NUMBER} and {@link
028: * org.mmbase.storage.search.SearchQueryHandler#FEATURE_OFFSET
029: * FEATURE_OFFSET}, by adding a construct like "<code>LIMIT 20</code>" or
030: * "<code>LIMIT 20 OFFSET 80</code>" after the body, when appropriate.
031: * <li>{@link #getSupportLevel(int,SearchQuery) getSupportLevel(int,SearchQuery)},
032: * returns {@link
033: * org.mmbase.storage.search.SearchQueryHandler#SUPPORT_OPTIMAL
034: * SUPPORT_OPTIMAL} for these features, delegates to the superclass for
035: * other features.
036: * </ul>
037: *
038: * @author Rob van Maris
039: * @version $Id: PostgreSqlSqlHandler.java,v 1.33 2007/06/12 10:59:41 michiel Exp $
040: * @since MMBase-1.7
041: */
042: public class PostgreSqlSqlHandler extends BasicSqlHandler implements
043: SqlHandler {
044:
045: private static final Logger log = Logging
046: .getLoggerInstance(PostgreSqlSqlHandler.class);
047:
048: private boolean localeMakesCaseInsensitive = false;
049:
050: /**
051: * Constructor.
052: */
053: public PostgreSqlSqlHandler() {
054: super ();
055: /* TODO: make this work..
056: DataSource ds = ((org.mmbase.storage.implementation.database.DatabaseStorageManagerFactory) org.mmbase.module.core.MMBase.getMMBase().getStorageManagerFactory()).getDataSource();
057: Connection con = null;
058: Statement statement = null;
059: ResultSet results = null;
060: try {
061: con = ds.getConnection();
062: statement = con.createStatement();
063: results = statement.executeQuery("select 'ab' > 'Ac'");
064: results.next();
065: localeMakesCaseInsensitive = results.getBoolean(0);
066: } catch (Exception e) {
067: log.error(e);
068: } finally {
069: if (results != null) try { results.close(); } catch (Exception e) {};
070: if (statement != null) try { statement.close(); } catch (Exception e) {};
071: if (con != null) try { con.close(); } catch (Exception e) {};
072: }
073: log.info("Postgresql database instance is case " + (localeMakesCaseInsensitive ? "INSENSITIVE" : "SENSITIVE") + " (because of Locale settings)");
074: */
075:
076: }
077:
078: // javadoc is inherited
079: @Override
080: public int getSupportLevel(int feature, SearchQuery query)
081: throws SearchQueryException {
082: int result;
083: switch (feature) {
084: case SearchQueryHandler.FEATURE_MAX_NUMBER:
085: result = SearchQueryHandler.SUPPORT_OPTIMAL;
086: break;
087:
088: case SearchQueryHandler.FEATURE_OFFSET:
089: result = SearchQueryHandler.SUPPORT_OPTIMAL;
090: break;
091: /*
092: case SearchQueryHandler.FEATURE_REGEXP:
093: result = SearchQueryHandler.SUPPORT_OPTIMAL;
094: break;
095: */
096: default:
097: result = super .getSupportLevel(feature, query);
098: }
099: return result;
100: }
101:
102: @Override
103: protected boolean useLower(FieldCompareConstraint constraint) {
104: if (constraint.getOperator() == FieldCompareConstraint.LIKE) {
105: return false;
106: } else {
107: return true;
108: }
109: }
110:
111: @Override
112: protected StringBuilder appendLikeOperator(StringBuilder sb,
113: boolean caseSensitive) {
114: if (caseSensitive) {
115: sb.append(" LIKE ");
116: } else {
117: sb.append(" ILIKE ");
118: }
119: return sb;
120: }
121:
122: /**
123: * Normally, Postgresql does not sort case senstively, so we should not sort on
124: * UPPER(fieldname). This is mainly very bad if the query is also distinct. (ERROR: for SELECT
125: * DISTINCT, ORDER BY expressions must appear in select list), may occur.
126: */
127: @Override
128: protected StringBuilder appendSortOrderField(StringBuilder sb,
129: SortOrder sortOrder, boolean multipleSteps,
130: SearchQuery query) {
131: if (localeMakesCaseInsensitive) {
132: if (sortOrder.isCaseSensitive()) {
133: log
134: .warn("Don't now how to sort case sensitively if the locale make case insensitive in Postgresql for "
135: + sortOrder + " it will be ignored.");
136: }
137: appendField(sb, sortOrder, multipleSteps);
138: return sb;
139: } else {
140: if (query.isDistinct() && !sortOrder.isCaseSensitive()) {
141: StepField sf = sortOrder.getField();
142: if (sf.getType() == org.mmbase.bridge.Field.TYPE_STRING) {
143: log
144: .warn("With a case sensitive locale, it is impossible to sort a distinct query case insensitively. Will sort it case sensitively in stead: "
145: + sortOrder);
146: }
147: appendField(sb, sortOrder, multipleSteps);
148: return sb;
149: } else {
150: return super .appendSortOrderField(sb, sortOrder,
151: multipleSteps);
152: }
153: }
154: }
155:
156: /*
157: protected StringBuilder appendRegularExpressionOperator(StringBuilder sb, boolean caseSensitive) {
158: if (caseSensitive) {
159: sb.append(" ~ ");
160: } else {
161: sb.append(" ~* ");
162: }
163: return sb;
164: }
165: */
166:
167: /**
168: * <a href="http://www.postgresql.org/docs/7.4/static/functions-datetime.html">date time
169: * functions</a>
170: *
171: * @javadoc
172: */
173: @Override
174: protected void appendDateField(StringBuilder sb, Step step,
175: String fieldName, boolean multipleSteps, int datePart) {
176: String datePartFunction = null;
177: switch (datePart) {
178: case FieldValueDateConstraint.CENTURY:
179: datePartFunction = "CENTURY";
180: break;
181: case FieldValueDateConstraint.QUARTER:
182: datePartFunction = "QUARTER";
183: break;
184: case FieldValueDateConstraint.WEEK:
185: datePartFunction = "WEEK";
186: break;
187: case FieldValueDateConstraint.DAY_OF_YEAR:
188: datePartFunction = "DOY";
189: break;
190: case FieldValueDateConstraint.DAY_OF_WEEK:
191: datePartFunction = "DOW";
192: break;
193: case FieldValueDateConstraint.MILLISECOND:
194: datePartFunction = "MILLISECONDS";
195: break;
196: default:
197: log.debug("Unknown datePart " + datePart);
198: }
199: if (datePartFunction != null) {
200: sb.append("EXTRACT(");
201: sb.append(datePartFunction);
202: sb.append(" FROM ");
203: appendField(sb, step, fieldName, multipleSteps);
204: sb.append(')');
205: } else {
206: // others are supported in super..
207: super .appendDateField(sb, step, fieldName, multipleSteps,
208: datePart);
209: }
210: }
211:
212: // javadoc is inherited
213: @Override
214: public String toSql(SearchQuery query, SqlHandler firstInChain)
215: throws SearchQueryException {
216: // XXX should table and field aliases be tested for uniqueness?
217:
218: // Test for at least 1 step and 1 field.
219: if (query.getSteps().isEmpty()) {
220: throw new IllegalStateException(
221: "Searchquery has no step (at leas 1 step is required).");
222: }
223: if (query.getFields().isEmpty()) {
224: throw new IllegalStateException(
225: "Searchquery has no field (at least 1 field is required).");
226: }
227:
228: // SELECT
229: StringBuilder sbQuery = new StringBuilder("SELECT ");
230:
231: // DISTINCT
232: if (query.isDistinct()) {
233: sbQuery.append("DISTINCT ");
234: }
235:
236: firstInChain.appendQueryBodyToSql(sbQuery, query, firstInChain);
237:
238: int offset = query.getOffset();
239: // LIMIT
240: if (query.getMaxNumber() != -1) {
241: // Maxnumber set.
242: sbQuery.append(" LIMIT ").append(query.getMaxNumber());
243: } else {
244: // could append LIMIT ALL, but why bother
245: }
246:
247: if (offset != 0) {
248: sbQuery.append(" OFFSET ").append(offset);
249: }
250:
251: String strSQL = sbQuery.toString();
252: if (log.isDebugEnabled()) {
253: log.debug("generated SQL: " + strSQL);
254: }
255: return strSQL;
256: }
257:
258: /**
259: * Optimizes postgresql queries by adding the ONLY keyword to a relation-table, provided that the
260: * role was given (and therefor the selection only applies to the given table).
261: *
262: * @see org.mmbase.storage.search.implementation.database.BasicSqlHandler#appendTableName(java.lang.StringBuilder, org.mmbase.storage.search.Step)
263: */
264: @Override
265: protected void appendTableName(StringBuilder sb, Step step) {
266: if (step instanceof RelationStep) {
267: RelationStep rs = (RelationStep) step;
268: if (rs.getRole() != null) {
269: if (log.isDebugEnabled()) {
270: log.debug("Adding ONLY keyword to tablename "
271: + step.getTableName());
272: }
273: sb.append(" ONLY ");
274: } else {
275: org.mmbase.module.core.MMBase mmbase = org.mmbase.module.core.MMBase
276: .getMMBase();
277: // no role specified, check if more than one role on sub tables are possible...
278: int sourceBuilder = mmbase.getBuilder(
279: rs.getPrevious().getTableName())
280: .getObjectType();
281: int destinationBuilder = mmbase.getBuilder(
282: rs.getNext().getTableName()).getObjectType();
283: int searchDir = rs.getDirectionality();
284: RelDef reldef = mmbase.getRelDef();
285:
286: // TODO it is not necessary to determin the full table, because the only used
287: // informations are:
288: // 1. whether it has 1 entry.
289: // 2. if it has, which it is.
290: //
291: // TODO Seems to be a bit of code-duplication from TypeRel#optimizeRelationStep,
292: // perhaps an extra helper method must be created in TypeRel.
293:
294: Set<String> tables = new HashSet<String>();
295: TypeRel typeRel = mmbase.getTypeRel();
296: for (Integer rnumber : reldef.getRoles()) {
297: log.debug(" considering role "
298: + rnumber
299: + "("
300: + reldef.getNode(rnumber).getStringValue(
301: "sname"));
302: boolean sourceToDestination = searchDir != RelationStep.DIRECTIONS_SOURCE
303: && typeRel
304: .contains(
305: sourceBuilder,
306: destinationBuilder,
307: rnumber,
308: TypeRel.INCLUDE_PARENTS_AND_DESCENDANTS);
309: boolean destinationToSource = searchDir != RelationStep.DIRECTIONS_DESTINATION
310: && typeRel
311: .contains(
312: destinationBuilder,
313: sourceBuilder,
314: rnumber,
315: TypeRel.INCLUDE_PARENTS_AND_DESCENDANTS);
316:
317: if (sourceToDestination || destinationToSource) {
318: tables.add(reldef.getBuilder(rnumber)
319: .getTableName());
320: }
321: }
322: if (tables.size() == 1) {
323: if (log.isDebugEnabled()) {
324: log
325: .debug("No role defined but only one table possible ("
326: + tables
327: + "), adding with ONLY");
328: }
329: sb.append(" ONLY ").append(mmbase.getBaseName())
330: .append('_').append(
331: tables.iterator().next());
332: appendTableAlias(sb, step);
333: return;
334: } else {
335: if (log.isDebugEnabled()) {
336: log
337: .debug("Not adding ONLY to table name because role of "
338: + step
339: + " is null, and the following tables are possible "
340: + tables);
341: }
342: // falling back to super.
343: }
344: }
345: }
346: super.appendTableName(sb, step);
347: }
348: }
|