1   /**
2    * Copyright (c) 2000-2008 Liferay, Inc. All rights reserved.
3    *
4    * Permission is hereby granted, free of charge, to any person obtaining a copy
5    * of this software and associated documentation files (the "Software"), to deal
6    * in the Software without restriction, including without limitation the rights
7    * to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
8    * copies of the Software, and to permit persons to whom the Software is
9    * furnished to do so, subject to the following conditions:
10   *
11   * The above copyright notice and this permission notice shall be included in
12   * all copies or substantial portions of the Software.
13   *
14   * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
15   * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
16   * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
17   * AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
18   * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
19   * OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
20   * SOFTWARE.
21   */
22  
23  package com.liferay.util.dao.orm;
24  
25  import com.liferay.portal.kernel.dao.jdbc.DataAccess;
26  import com.liferay.portal.kernel.util.GetterUtil;
27  import com.liferay.portal.kernel.util.OrderByComparator;
28  import com.liferay.portal.kernel.util.PortalClassLoaderUtil;
29  import com.liferay.portal.kernel.util.StringPool;
30  import com.liferay.portal.kernel.util.StringUtil;
31  import com.liferay.portal.kernel.util.Validator;
32  import com.liferay.portal.kernel.xml.Document;
33  import com.liferay.portal.kernel.xml.Element;
34  import com.liferay.portal.kernel.xml.SAXReaderUtil;
35  import com.liferay.portal.util.PortalUtil;
36  
37  import java.io.BufferedReader;
38  import java.io.IOException;
39  import java.io.StringReader;
40  
41  import java.sql.Connection;
42  import java.sql.DatabaseMetaData;
43  import java.sql.SQLException;
44  
45  import java.util.HashMap;
46  import java.util.Iterator;
47  import java.util.Map;
48  import java.util.Properties;
49  
50  import org.apache.commons.logging.Log;
51  import org.apache.commons.logging.LogFactory;
52  
53  /**
54   * <a href="CustomSQL.java.html"><b><i>View Source</i></b></a>
55   *
56   * @author Brian Wing Shun Chan
57   * @author Bruno Farache
58   *
59   */
60  public class CustomSQL {
61  
62      public static final String DB2_FUNCTION_IS_NULL =
63          "CAST(? AS VARCHAR(32672)) IS NULL";
64  
65      public static final String DB2_FUNCTION_IS_NOT_NULL =
66          "CAST(? AS VARCHAR(32672)) IS NOT NULL";
67  
68      public static final String INFORMIX_FUNCTION_IS_NULL = "lportal.isnull(?)";
69  
70      public static final String INFORMIX_FUNCTION_IS_NOT_NULL =
71          "NOT " + INFORMIX_FUNCTION_IS_NULL;
72  
73      public static final String MYSQL_FUNCTION_IS_NULL = "IFNULL(?, '1') = '1'";
74  
75      public static final String MYSQL_FUNCTION_IS_NOT_NULL =
76          "IFNULL(?, '1') = '0'";
77  
78      public static final String SYBASE_FUNCTION_IS_NULL = "ISNULL(?, '1') = '1'";
79  
80      public static final String SYBASE_FUNCTION_IS_NOT_NULL =
81          "ISNULL(?, '1') = '0'";
82  
83      public CustomSQL() throws SQLException {
84          Connection con = DataAccess.getConnection();
85  
86          String functionIsNull = PortalUtil.getCustomSQLFunctionIsNull();
87          String functionIsNotNull = PortalUtil.getCustomSQLFunctionIsNotNull();
88  
89          try {
90              if (Validator.isNotNull(functionIsNull) &&
91                  Validator.isNotNull(functionIsNotNull)) {
92  
93                  _functionIsNull = functionIsNull;
94                  _functionIsNotNull = functionIsNotNull;
95  
96                  if (_log.isDebugEnabled()) {
97                      _log.info(
98                          "functionIsNull is manually set to " + functionIsNull);
99                      _log.info(
100                         "functionIsNotNull is manually set to " +
101                             functionIsNotNull);
102                 }
103             }
104             else if (con != null) {
105                 DatabaseMetaData metaData = con.getMetaData();
106 
107                 String dbName = GetterUtil.getString(
108                     metaData.getDatabaseProductName());
109 
110                 if (_log.isInfoEnabled()) {
111                     _log.info("Database name " + dbName);
112                 }
113 
114                 if (dbName.startsWith("DB2")) {
115                     _vendorDB2 = true;
116                     _functionIsNull = DB2_FUNCTION_IS_NULL;
117                     _functionIsNotNull = DB2_FUNCTION_IS_NOT_NULL;
118 
119                     if (_log.isInfoEnabled()) {
120                         _log.info("Detected DB2 with database name " + dbName);
121                     }
122                 }
123                 else if (dbName.startsWith("Informix")) {
124                     _vendorInformix = true;
125                     _functionIsNull = INFORMIX_FUNCTION_IS_NULL;
126                     _functionIsNotNull = INFORMIX_FUNCTION_IS_NOT_NULL;
127 
128                     if (_log.isInfoEnabled()) {
129                         _log.info(
130                             "Detected Informix with database name " + dbName);
131                     }
132                 }
133                 else if (dbName.startsWith("MySQL")) {
134                     _vendorMySQL = true;
135                     //_functionIsNull = MYSQL_FUNCTION_IS_NULL;
136                     //_functionIsNotNull = MYSQL_FUNCTION_IS_NOT_NULL;
137 
138                     if (_log.isInfoEnabled()) {
139                         _log.info(
140                             "Detected MySQL with database name " + dbName);
141                     }
142                 }
143                 else if (dbName.startsWith("Sybase") || dbName.equals("ASE")) {
144                     _vendorSybase = true;
145                     _functionIsNull = SYBASE_FUNCTION_IS_NULL;
146                     _functionIsNotNull = SYBASE_FUNCTION_IS_NOT_NULL;
147 
148                     if (_log.isInfoEnabled()) {
149                         _log.info(
150                             "Detected Sybase with database name " + dbName);
151                     }
152                 }
153                 else if (dbName.startsWith("Oracle")) {
154                     _vendorOracle = true;
155 
156                     if (_log.isInfoEnabled()) {
157                         _log.info(
158                             "Detected Oracle with database name " + dbName);
159                     }
160                 }
161                 else if (dbName.startsWith("PostgreSQL")) {
162                     _vendorPostgreSQL = true;
163 
164                     if (_log.isInfoEnabled()) {
165                         _log.info(
166                             "Detected PostgreSQL with database name " + dbName);
167                     }
168                 }
169                 else {
170                     if (_log.isDebugEnabled()) {
171                         _log.debug(
172                             "Unable to detect database with name " + dbName);
173                     }
174                 }
175             }
176         }
177         catch (Exception e) {
178             _log.error(e, e);
179         }
180         finally {
181             DataAccess.cleanUp(con);
182         }
183 
184         _sqlPool = new HashMap<String, String>();
185 
186         try {
187             ClassLoader classLoader = getClass().getClassLoader();
188 
189             String[] configs = getConfigs();
190 
191             for (int i = 0; i < configs.length; i++) {
192                 read(classLoader, configs[i]);
193             }
194         }
195         catch (Exception e) {
196             _log.error(e, e);
197         }
198     }
199 
200     public String get(String id) {
201         return _sqlPool.get(id);
202     }
203 
204     /**
205      * Returns true if Hibernate is connecting to a DB2 database.
206      *
207      * @return      true if Hibernate is connecting to a DB2 database
208      */
209     public boolean isVendorDB2() {
210         return _vendorDB2;
211     }
212 
213     /**
214      * Returns true if Hibernate is connecting to an Informix database.
215      *
216      * @return      true if Hibernate is connecting to an Informix database
217      */
218     public boolean isVendorInformix() {
219         return _vendorInformix;
220     }
221 
222     /**
223      * Returns true if Hibernate is connecting to a MySQL database.
224      *
225      * @return      true if Hibernate is connecting to a MySQL database
226      */
227     public boolean isVendorMySQL() {
228         return _vendorMySQL;
229     }
230 
231     /**
232      * Returns true if Hibernate is connecting to an Oracle database.
233      *
234      * Oracle has a nasty bug where it treats '' as a NULL value. See
235      * http://thedailywtf.com/forums/thread/26879.aspx for more information
236      * on this nasty bug.
237      *
238      * @return      true if Hibernate is connecting to an Oracle database
239      */
240     public boolean isVendorOracle() {
241         return _vendorOracle;
242     }
243 
244     /**
245      * Returns true if Hibernate is connecting to a PostgreSQL database.
246      *
247      * @return      true if Hibernate is connecting to a PostgreSQL database
248      */
249     public boolean isVendorPostgreSQL() {
250         return _vendorPostgreSQL;
251     }
252 
253     /**
254      * Returns true if Hibernate is connecting to a Sybase database.
255      *
256      * @return      true if Hibernate is connecting to a Sybase database
257      */
258     public boolean isVendorSybase() {
259         return _vendorSybase;
260     }
261 
262     public String[] keywords(String keywords) {
263         return keywords(keywords, true);
264     }
265 
266     public String[] keywords(String keywords, boolean lowerCase) {
267         if (lowerCase) {
268             keywords = keywords.toLowerCase();
269         }
270 
271         keywords = keywords.trim();
272 
273         String[] keywordsArray = StringUtil.split(keywords, StringPool.SPACE);
274 
275         for (int i = 0; i < keywordsArray.length; i++) {
276             String keyword = keywordsArray[i];
277 
278             keywordsArray[i] =
279                 StringPool.PERCENT + keyword + StringPool.PERCENT;
280         }
281 
282         return keywordsArray;
283     }
284 
285     public String[] keywords(String[] keywordsArray) {
286         return keywords(keywordsArray, true);
287     }
288 
289     public String[] keywords(String[] keywordsArray, boolean lowerCase) {
290         if ((keywordsArray == null) || (keywordsArray.length == 0)) {
291             keywordsArray = new String[] {null};
292         }
293 
294         if (lowerCase) {
295             for (int i = 0; i < keywordsArray.length; i++) {
296                 keywordsArray[i] = StringUtil.lowerCase(keywordsArray[i]);
297             }
298         }
299 
300         return keywordsArray;
301     }
302 
303     public String replaceAndOperator(String sql, boolean andOperator) {
304         String andOrConnector = "OR";
305         String andOrNullCheck = "AND ? IS NOT NULL";
306 
307         if (andOperator) {
308             andOrConnector = "AND";
309             andOrNullCheck = "OR ? IS NULL";
310         }
311 
312         sql = StringUtil.replace(
313             sql,
314             new String[] {
315                 "[$AND_OR_CONNECTOR$]", "[$AND_OR_NULL_CHECK$]"
316             },
317             new String[] {
318                 andOrConnector, andOrNullCheck
319             });
320 
321         if (_vendorPostgreSQL) {
322             sql = StringUtil.replace(
323                 sql,
324                 new String[] {
325                     "Date >= ? AND ? IS NOT NULL",
326                     "Date <= ? AND ? IS NOT NULL",
327                     "Date >= ? OR ? IS NULL",
328                     "Date <= ? OR ? IS NULL"
329                 },
330                 new String[] {
331                     "Date >= ? AND CAST(? AS TIMESTAMP) IS NOT NULL",
332                     "Date <= ? AND CAST(? AS TIMESTAMP) IS NOT NULL",
333                     "Date >= ? OR CAST(? AS TIMESTAMP) IS NULL",
334                     "Date <= ? OR CAST(? AS TIMESTAMP) IS NULL"
335                 });
336         }
337 
338         sql = replaceIsNull(sql);
339 
340         return sql;
341     }
342 
343     public String replaceIsNull(String sql) {
344         if (Validator.isNotNull(_functionIsNull)) {
345             sql = StringUtil.replace(
346                 sql,
347                 new String[] {
348                     "? IS NULL", "? IS NOT NULL"
349                 },
350                 new String[] {
351                     _functionIsNull,
352                     _functionIsNotNull
353                 });
354         }
355 
356         return sql;
357     }
358 
359     public String replaceKeywords(
360         String sql, String field, String operator, boolean last,
361         String[] values) {
362 
363         if (values.length == 0) {
364             return sql;
365         }
366 
367         StringBuilder oldSql = new StringBuilder();
368 
369         oldSql.append("(");
370         oldSql.append(field);
371         oldSql.append(" ");
372         oldSql.append(operator);
373         oldSql.append(" ? [$AND_OR_NULL_CHECK$])");
374 
375         if (!last) {
376             oldSql.append(" [$AND_OR_CONNECTOR$]");
377         }
378 
379         StringBuilder newSql = new StringBuilder();
380 
381         newSql.append("(");
382 
383         for (int i = 0; i < values.length; i++) {
384             if (i > 0) {
385                 newSql.append(" OR ");
386             }
387 
388             newSql.append("(");
389             newSql.append(field);
390             newSql.append(" ");
391             newSql.append(operator);
392             newSql.append(" ? [$AND_OR_NULL_CHECK$])");
393         }
394 
395         newSql.append(")");
396 
397         if (!last) {
398             newSql.append(" [$AND_OR_CONNECTOR$]");
399         }
400 
401         return StringUtil.replace(sql, oldSql.toString(), newSql.toString());
402     }
403 
404     public String removeOrderBy(String sql) {
405         int pos = sql.indexOf(" ORDER BY ");
406 
407         if (pos != -1) {
408             sql = sql.substring(0, pos);
409         }
410 
411         return sql;
412     }
413 
414     public String replaceOrderBy(String sql, OrderByComparator obc) {
415         if (obc == null) {
416             return sql;
417         }
418 
419         StringBuilder sb = new StringBuilder();
420 
421         sb.append(removeOrderBy(sql));
422         sb.append(" ORDER BY ");
423         sb.append(obc.getOrderBy());
424 
425         return sb.toString();
426     }
427 
428     protected String[] getConfigs() {
429         if (PortalClassLoaderUtil.getClassLoader() ==
430                 CustomSQL.class.getClassLoader()) {
431 
432             Properties propsUtil = PortalUtil.getPortalProperties();
433 
434             return StringUtil.split(
435                 propsUtil.getProperty("custom.sql.configs"));
436         }
437         else {
438             return new String[] {"custom-sql/default.xml"};
439         }
440     }
441 
442     protected void read(ClassLoader classLoader, String source)
443         throws Exception {
444 
445         String xml = null;
446 
447         try {
448             xml = StringUtil.read(classLoader, source);
449         }
450         catch (Exception e) {
451             _log.warn("Cannot load " + source);
452         }
453 
454         if (xml == null) {
455             return;
456         }
457 
458         if (_log.isDebugEnabled()) {
459             _log.debug("Loading " + source);
460         }
461 
462         Document doc = SAXReaderUtil.read(new StringReader(xml));
463 
464         Element root = doc.getRootElement();
465 
466         Iterator<Element> itr = root.elements("sql").iterator();
467 
468         while (itr.hasNext()) {
469             Element sql = itr.next();
470 
471             String file = sql.attributeValue("file");
472 
473             if (Validator.isNotNull(file)) {
474                 read(classLoader, file);
475             }
476             else {
477                 String id = sql.attributeValue("id");
478                 String content = transform(sql.getText());
479 
480                 content = replaceIsNull(content);
481 
482                 _sqlPool.put(id, content);
483             }
484         }
485     }
486 
487     protected String transform(String sql) {
488         sql = PortalUtil.transformCustomSQL(sql);
489 
490         StringBuilder sb = new StringBuilder();
491 
492         try {
493             BufferedReader br = new BufferedReader(new StringReader(sql));
494 
495             String line = null;
496 
497             while ((line = br.readLine()) != null) {
498                 sb.append(line.trim());
499                 sb.append(StringPool.SPACE);
500             }
501 
502             br.close();
503         }
504         catch (IOException ioe) {
505             return sql;
506         }
507 
508         return sb.toString();
509     }
510 
511     private static Log _log = LogFactory.getLog(CustomSQL.class);
512 
513     private boolean _vendorDB2;
514     private boolean _vendorInformix;
515     private boolean _vendorMySQL;
516     private boolean _vendorOracle;
517     private boolean _vendorPostgreSQL;
518     private boolean _vendorSybase;
519     private String _functionIsNull;
520     private String _functionIsNotNull;
521     private Map<String, String> _sqlPool;
522 
523 }