View Javadoc

1   /**
2    * This file Copyright (c) 2003-2010 Magnolia International
3    * Ltd.  (http://www.magnolia-cms.com). All rights reserved.
4    *
5    *
6    * This file is dual-licensed under both the Magnolia
7    * Network Agreement and the GNU General Public License.
8    * You may elect to use one or the other of these licenses.
9    *
10   * This file is distributed in the hope that it will be
11   * useful, but AS-IS and WITHOUT ANY WARRANTY; without even the
12   * implied warranty of MERCHANTABILITY or FITNESS FOR A
13   * PARTICULAR PURPOSE, TITLE, or NONINFRINGEMENT.
14   * Redistribution, except as permitted by whichever of the GPL
15   * or MNA you select, is prohibited.
16   *
17   * 1. For the GPL license (GPL), you can redistribute and/or
18   * modify this file under the terms of the GNU General
19   * Public License, Version 3, as published by the Free Software
20   * Foundation.  You should have received a copy of the GNU
21   * General Public License, Version 3 along with this program;
22   * if not, write to the Free Software Foundation, Inc., 51
23   * Franklin St, Fifth Floor, Boston, MA 02110-1301 USA.
24   *
25   * 2. For the Magnolia Network Agreement (MNA), this file
26   * and the accompanying materials are made available under the
27   * terms of the MNA which accompanies this distribution, and
28   * is available at http://www.magnolia-cms.com/mna.html
29   *
30   * Any modifications to this file must keep this entire header
31   * intact.
32   *
33   */
34  package info.magnolia.cms.gui.controlx.search;
35  
36  import info.magnolia.cms.gui.query.DateSearchQueryParameter;
37  import info.magnolia.cms.gui.query.SearchQueryExpression;
38  import info.magnolia.cms.gui.query.SearchQueryOperator;
39  import info.magnolia.cms.gui.query.StringSearchQueryParameter;
40  
41  import java.text.DecimalFormat;
42  import java.util.Date;
43  import java.util.TimeZone;
44  
45  import org.apache.commons.lang.StringUtils;
46  import org.apache.commons.lang.time.DateFormatUtils;
47  
48  
49  /**
50   * @author Sameer Charles $Id: QueryBuilder.java 32667 2010-03-13 00:37:06Z gjoseph $ package private helper class
51   * Implement this class if you need any further operations in future <b>NOTE</b> : its a very simple in-order binary
52   * traversal, order of operation is not preserved
53   */
54  public class QueryBuilder {
55  
56      /**
57       * search model using which query will be created
58       */
59      private RepositorySearchListModel model;
60  
61      /**
62       * Use JCR order by clause to sort the items. Default is false.
63       */
64      private boolean useJCROrderBy = false;
65  
66      /**
67       * package private
68       * @param model
69       */
70      public QueryBuilder(RepositorySearchListModel model) {
71          this.model = model;
72      }
73  
74      /**
75       * get SQL statement based on SearchQuery
76       * @return SQL statement
77       */
78      public String getSQLStatement() {
79          StringBuffer statement = new StringBuffer("select * from ");
80          statement.append(this.model.getNodeType());
81  
82          String where = buildWhereClause();
83          if (where.length() > 0) {
84              statement.append(" where ").append(where);
85          }
86          String orderBy = buildOrderByClause();
87          if (orderBy.length() > 0) {
88              statement.append(" order by ").append(orderBy);
89          }
90  
91          return statement.toString();
92      }
93  
94      /**
95       * Build the complete where clause
96       */
97      protected String buildWhereClause() {
98          StringBuffer where = new StringBuffer();
99          if (StringUtils.isNotEmpty(this.model.getSearchPath())) {
100             where.append(" jcr:path like '");
101             where.append(this.model.getSearchPath());
102             where.append("%'");
103         }
104         if(this.model.getQuery() !=null){
105             where.append(buildWhereClause(this.model.getQuery().getRootExpression()));
106         }
107         return where.toString();
108     }
109 
110     /**
111      * Order clause
112      */
113     protected String buildOrderByClause() {
114         StringBuffer orderBy = new StringBuffer();
115         if(useJCROrderBy){
116             if (StringUtils.isNotEmpty(this.model.getGroupBy()) && StringUtils.isNotEmpty(this.model.getSortBy())) {
117                 orderBy.append(this.model.getGroupBy());
118                 orderBy.append(" ");
119                 orderBy.append(this.model.getGroupByOrder());
120                 orderBy.append(", ");
121                 orderBy.append(this.model.getSortBy());
122                 orderBy.append(" ");
123                 orderBy.append(this.model.getSortByOrder());
124             }
125             else if (StringUtils.isNotEmpty(this.model.getGroupBy()) && StringUtils.isEmpty(this.model.getSortBy())) {
126                 orderBy.append(this.model.getGroupBy());
127                 orderBy.append(" ");
128                 orderBy.append(this.model.getGroupByOrder());
129             }
130             else if (StringUtils.isEmpty(this.model.getGroupBy()) && StringUtils.isNotEmpty(this.model.getSortBy())) {
131                 orderBy.append(this.model.getSortBy());
132                 orderBy.append(" ");
133                 orderBy.append(this.model.getSortByOrder());
134             }
135         }
136         return orderBy.toString();
137     }
138 
139     /**
140      * NOTE : its a very simple in-order binary traversal, order of operation is not preserved
141      * @param expression
142      */
143     protected String buildWhereClause(SearchQueryExpression expression) {
144         StringBuffer where = new StringBuffer();
145         if (expression != null) {
146             where.append(buildWhereClause(expression.getLeft()));
147             where.append(" ");
148             where.append(toJCRExpression(expression));
149             where.append(buildWhereClause(expression.getRight()));
150         }
151         return where.toString();
152     }
153 
154     /**
155      * Make a jcr expression out of the expression
156      * @param expression
157      * @return the expression as string
158      */
159     protected String toJCRExpression(SearchQueryExpression expression) {
160         if (expression instanceof SearchQueryOperator) {
161             // operator is 1:1 usable in jcr
162             return StringUtils.defaultString(((SearchQueryOperator) expression).getOperator());
163         }
164         else if (expression instanceof StringSearchQueryParameter) {
165             return toStringJCRExpression((StringSearchQueryParameter) expression);
166         }
167         else if (expression instanceof DateSearchQueryParameter) {
168             return toDateJCRExpression((DateSearchQueryParameter) expression);
169         }
170         return StringUtils.EMPTY;
171     }
172 
173     /**
174      * Make a jcr expression out of the expression
175      * @param param
176      * @return the expression as a string
177      */
178     protected String toDateJCRExpression(DateSearchQueryParameter param) {
179         Date date = param.getValue();
180         if (param.getConstraint().equalsIgnoreCase(DateSearchQueryParameter.TODAY)) {
181             date = new Date();
182         }
183 
184         StringBuffer buffer = new StringBuffer();
185         buffer.append(param.getName());
186         if (param.getConstraint().equalsIgnoreCase(DateSearchQueryParameter.BEFORE)) {
187             buffer.append(" <= ");
188         }
189         else if (param.getConstraint().equalsIgnoreCase(DateSearchQueryParameter.AFTER)) {
190             buffer.append(" >= ");
191         }
192         else if (param.getConstraint().equalsIgnoreCase(DateSearchQueryParameter.IS)) {
193             buffer.append(" = ");
194         }
195 
196         buffer.append("TIMESTAMP '");
197         buffer.append(DateFormatUtils.format(date, "yyyy-MM-dd"));
198         buffer.append("T00:00:00.000");
199 
200         TimeZone timezone = TimeZone.getDefault();
201         int milis = Math.abs(timezone.getRawOffset());
202         if (milis == 0) {
203             buffer.append("Z");
204         }
205         else {
206             if (timezone.getRawOffset() > 0) {
207                 buffer.append("+");
208             }
209             else {
210                 buffer.append("-");
211             }
212 
213             int hours = milis / (1000 * 60 * 60);
214             int minutes = (milis - hours * 1000 * 60 * 60) / (1000 * 60);
215             DecimalFormat format = new DecimalFormat("00");
216             buffer.append(format.format(hours)).append(":").append(format.format(minutes));
217         }
218         buffer.append("'");
219         return buffer.toString();
220     }
221 
222     /**
223      * @param param
224      * @return jcr search expression
225      */
226     protected String toStringJCRExpression(StringSearchQueryParameter param) {
227         if (param.getConstraint().equals(StringSearchQueryParameter.CONTAINS)) {
228             return "contains(" + param.getName() + ",'" + param.getValue() + "*')";
229         }
230 
231         else if (param.getConstraint().equals(StringSearchQueryParameter.CONTAINS_NOT)) {
232             return "not contains(" + param.getName() + ",'*" + param.getValue() + "*')";
233         }
234 
235         else if (param.getConstraint().equals(StringSearchQueryParameter.ENDS)) {
236             return "contains(" + param.getName() + ",'*" + param.getValue() + "')";
237         }
238 
239         else if (param.getConstraint().equals(StringSearchQueryParameter.STARTS)) {
240             return "contains(" + param.getName() + ",'" + param.getValue() + "*')";
241         }
242 
243         else if (param.getConstraint().equals(StringSearchQueryParameter.IS)) {
244             return param.getName() + " = '" + param.getValue() + "'";
245         }
246 
247         else if (param.getConstraint().equals(StringSearchQueryParameter.IS_NOT)) {
248             return param.getName() + " <> '" + param.getValue() + "'";
249         }
250         else {
251             return param.getName() + " " + param.getConstraint() + " '" + param.getValue() + "'";
252         }
253     }
254 
255 
256     public boolean isUseJCROrderBy() {
257         return this.useJCROrderBy;
258     }
259 
260 
261     public void setUseJCROrderBy(boolean useJCROrderBy) {
262         this.useJCROrderBy = useJCROrderBy;
263     }
264 
265 }