KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > org > mmbase > storage > search > implementation > database > PostgreSqlSqlHandler


1 /*
2
3 This software is OSI Certified Open Source Software.
4 OSI Certified is a certification mark of the Open Source Initiative.
5
6 The license (Mozilla version 1.0) can be read at the MMBase site.
7 See http://www.MMBase.org/license
8
9 */

10 package org.mmbase.storage.search.implementation.database;
11
12 import org.mmbase.storage.search.*;
13 import org.mmbase.util.logging.*;
14 import java.util.*;
15 import org.mmbase.module.corebuilders.RelDef;
16 import org.mmbase.module.core.MMObjectNode;
17
18 /**
19  * The PostgreSQL query handler, implements {@link
20  * org.mmbase.storage.search.implementation.database.SqlHandler SqlHandler}
21  * for standard PostgreSql functionality.
22  * <br />
23  * Derived from {@link BasicSqlHandler BasicSqlHandler}, overrides
24  * <ul>
25  * <li>{@link #toSql toSql()}, implements {@link
26  * org.mmbase.storage.search.SearchQueryHandler#FEATURE_MAX_NUMBER
27  * FEATURE_MAX_NUMBER} and {@link
28  * org.mmbase.storage.search.SearchQueryHandler#FEATURE_OFFSET
29  * FEATURE_OFFSET}, by adding a construct like "<code>LIMIT 20</code>" or
30  * "<code>LIMIT 20 OFFSET 80</code>" after the body, when appropriate.
31  * <li>{@link #getSupportLevel(int,SearchQuery) getSupportLevel(int,SearchQuery)},
32  * returns {@link
33  * org.mmbase.storage.search.SearchQueryHandler#SUPPORT_OPTIMAL
34  * SUPPORT_OPTIMAL} for these features, delegates to the superclass for
35  * other features.
36  * </ul>
37  *
38  * @author Rob van Maris
39  * @version $Id: PostgreSqlSqlHandler.java,v 1.24.2.1 2006/10/02 11:33:37 michiel Exp $
40  * @since MMBase-1.7
41  */

42 public class PostgreSqlSqlHandler extends BasicSqlHandler implements SqlHandler {
43
44     private static final Logger log = Logging.getLoggerInstance(PostgreSqlSqlHandler.class);
45
46     /**
47      * Constructor.
48      */

49     public PostgreSqlSqlHandler() {
50         super();
51     }
52
53     // javadoc is inherited
54
public int getSupportLevel(int feature, SearchQuery query) throws SearchQueryException {
55         int result;
56         switch (feature) {
57         case SearchQueryHandler.FEATURE_MAX_NUMBER:
58             result = SearchQueryHandler.SUPPORT_OPTIMAL;
59             break;
60
61         case SearchQueryHandler.FEATURE_OFFSET:
62             result = SearchQueryHandler.SUPPORT_OPTIMAL;
63             break;
64             /*
65               case SearchQueryHandler.FEATURE_REGEXP:
66               result = SearchQueryHandler.SUPPORT_OPTIMAL;
67               break;
68             */

69         default:
70             result = super.getSupportLevel(feature, query);
71         }
72         return result;
73     }
74
75
76     protected boolean useLower(FieldCompareConstraint constraint) {
77         if (constraint.getOperator() == FieldCompareConstraint.LIKE) {
78             return false;
79         } else {
80             return true;
81         }
82     }
83
84
85     protected StringBuffer JavaDoc appendLikeOperator(StringBuffer JavaDoc sb, boolean caseSensitive) {
86         if (caseSensitive) {
87             sb.append(" LIKE ");
88         } else {
89             sb.append(" ILIKE ");
90         }
91         return sb;
92     }
93
94
95     /// TODO: Needs to determine the value of this (select 'a' > 'A' or so?)
96
private final boolean localeMakesCaseInsensitive = false;
97
98     /**
99      * Normally, Postgresql does not sort case senstively, so we should not sort on
100      * UPPER(fieldname). This is mainly very bad if the query is also distinct. (ERROR: for SELECT
101      * DISTINCT, ORDER BY expressions must appear in select list), may occur.
102      */

103     protected StringBuffer JavaDoc appendSortOrderField(StringBuffer JavaDoc sb, SortOrder sortOrder, boolean multipleSteps, SearchQuery query) {
104         if (localeMakesCaseInsensitive) {
105             if (sortOrder.isCaseSensitive()) {
106                 log.warn("Don't now how to sort case sensitively if the locale make case insensitive in Postgresql for " + sortOrder + " it will be ignored.");
107             }
108             StepField sf = sortOrder.getField();
109             appendField(sb, sf.getStep(), sf.getFieldName(), multipleSteps);
110             return sb;
111         } else {
112             if (query.isDistinct() && ! sortOrder.isCaseSensitive()) {
113                 StepField sf = sortOrder.getField();
114                 if (sf.getType() == org.mmbase.bridge.Field.TYPE_STRING ) {
115                     log.warn("With a case sensitive locale, it is impossible to sort a distinct query case insensitively. Will sort it case sensitively in stead: " + sortOrder);
116                 }
117                 appendField(sb, sf.getStep(), sf.getFieldName(), multipleSteps);
118                 return sb;
119             } else {
120                 return super.appendSortOrderField(sb, sortOrder, multipleSteps);
121             }
122         }
123     }
124     /*
125     protected StringBuffer appendRegularExpressionOperator(StringBuffer sb, boolean caseSensitive) {
126         if (caseSensitive) {
127             sb.append(" ~ ");
128         } else {
129             sb.append(" ~* ");
130         }
131         return sb;
132     }
133     */

134
135     /**
136      * <a HREF="http://www.postgresql.org/docs/7.4/static/functions-datetime.html">date time
137      * functions</a>
138      *
139      * @javadoc
140      */

141     protected void appendDateField(StringBuffer JavaDoc sb, Step step, String JavaDoc fieldName, boolean multipleSteps, int datePart) {
142         String JavaDoc datePartFunction = null;
143         switch (datePart) {
144         case FieldValueDateConstraint.CENTURY:
145             datePartFunction = "CENTURY";
146             break;
147         case FieldValueDateConstraint.QUARTER:
148             datePartFunction = "QUARTER";
149             break;
150         case FieldValueDateConstraint.WEEK:
151             datePartFunction = "WEEK";
152             break;
153         case FieldValueDateConstraint.DAY_OF_YEAR:
154             datePartFunction = "DOY";
155             break;
156         case FieldValueDateConstraint.DAY_OF_WEEK:
157             datePartFunction = "DOW";
158             break;
159         case FieldValueDateConstraint.MILLISECOND:
160             datePartFunction = "MILLISECONDS";
161             break;
162         default:
163             log.debug("Unknown datePart " + datePart);
164         }
165         if (datePartFunction != null) {
166             sb.append("EXTRACT(");
167             sb.append(datePartFunction);
168             sb.append(" FROM ");
169             appendField(sb, step, fieldName, multipleSteps);
170             sb.append(')');
171         } else {
172             // others are supported in super..
173
super.appendDateField(sb, step, fieldName, multipleSteps, datePart);
174         }
175     }
176
177
178     // javadoc is inherited
179
public String JavaDoc toSql(SearchQuery query, SqlHandler firstInChain) throws SearchQueryException {
180         // XXX should table and field aliases be tested for uniqueness?
181

182         // Test for at least 1 step and 1 field.
183
if (query.getSteps().isEmpty()) {
184             throw new IllegalStateException JavaDoc(
185             "Searchquery has no step (at leas 1 step is required).");
186         }
187         if (query.getFields().isEmpty()) {
188             throw new IllegalStateException JavaDoc(
189             "Searchquery has no field (at least 1 field is required).");
190         }
191
192         // SELECT
193
StringBuffer JavaDoc sbQuery = new StringBuffer JavaDoc("SELECT ");
194
195         // DISTINCT
196
if (query.isDistinct()) {
197             sbQuery.append("DISTINCT ");
198         }
199
200         firstInChain.appendQueryBodyToSql(sbQuery, query, firstInChain);
201
202
203         int offset = query.getOffset();
204         // LIMIT
205
if (query.getMaxNumber() != -1) {
206             // Maxnumber set.
207
sbQuery.append(" LIMIT ").append(query.getMaxNumber());
208         } else {
209             // could append LIMIT ALL, but why bother
210
}
211
212         if (offset != 0) {
213             sbQuery.append(" OFFSET ").append(offset);
214         }
215
216         String JavaDoc strSQL = sbQuery.toString();
217         if (log.isDebugEnabled()) {
218             log.debug("generated SQL: " + strSQL);
219         }
220         return strSQL;
221     }
222
223
224     /**
225      * Optimizes postgresql queries by adding the ONLY keyword to a relation-table, provided that the
226      * role was given (and therefor the selection only applies to the given table).
227      *
228      * @see org.mmbase.storage.search.implementation.database.BasicSqlHandler#appendTableName(java.lang.StringBuffer, org.mmbase.storage.search.Step)
229      */

230     protected void appendTableName(StringBuffer JavaDoc sb, Step step) {
231         if(step instanceof RelationStep) {
232             RelationStep rs = (RelationStep) step;
233             if (rs.getRole() != null) {
234                 if (log.isDebugEnabled()) {
235                     log.debug("Adding ONLY keyword to tablename " + step.getTableName());
236                 }
237                 sb.append(" ONLY ");
238             } else {
239                 org.mmbase.module.core.MMBase mmbase = org.mmbase.module.core.MMBase.getMMBase();
240                 // no role specified, check if more than one role on sub tables are possible...
241
int sourceBuilder = mmbase.getBuilder(rs.getPrevious().getTableName()).getObjectType();
242                 int destinationBuilder = mmbase.getBuilder(rs.getNext().getTableName()).getObjectType();
243                 int directionality = rs.getDirectionality();
244                 RelDef reldef = mmbase.getRelDef();
245                 Set tables = new HashSet();
246                 Iterator allowed = mmbase.getTypeRel().getAllowedRelations(sourceBuilder, destinationBuilder, 0, directionality).iterator();
247                 while(allowed.hasNext()) {
248                     MMObjectNode typeRel = (MMObjectNode) allowed.next();
249                     int rnumber = typeRel.getIntValue("rnumber");
250                     tables.add(reldef.getBuilder(rnumber).getTableName());
251                 }
252                 if (tables.size() == 1) {
253                     if (log.isDebugEnabled()) {
254                         log.debug("No role defined but only one table possible (" + tables + "), adding with ONLY");
255                     }
256                     sb.append(" ONLY ").
257                         append(mmbase.getBaseName()).
258                         append('_').
259                         append((String JavaDoc) tables.iterator().next());
260                     appendTableAlias(sb, step);
261                     return;
262                 } else {
263                     if (log.isDebugEnabled()) {
264                         log.debug("Not adding ONLY to table name because role of " + step + " is null, and the following tables are possible " + tables);
265                     }
266                 }
267             }
268         }
269         super.appendTableName(sb, step);
270     }
271 }
272
Popular Tags