1   /**
2    * Copyright (c) 2000-2010 Liferay, Inc. All rights reserved.
3    *
4    * This library is free software; you can redistribute it and/or modify it under
5    * the terms of the GNU Lesser General Public License as published by the Free
6    * Software Foundation; either version 2.1 of the License, or (at your option)
7    * any later version.
8    *
9    * This library is distributed in the hope that it will be useful, but WITHOUT
10   * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
11   * FOR A PARTICULAR PURPOSE. See the GNU Lesser General Public License for more
12   * details.
13   */
14  
15  package com.liferay.util.dao.orm;
16  
17  import com.liferay.portal.kernel.dao.jdbc.DataAccess;
18  import com.liferay.portal.kernel.io.unsync.UnsyncBufferedReader;
19  import com.liferay.portal.kernel.io.unsync.UnsyncStringReader;
20  import com.liferay.portal.kernel.log.Log;
21  import com.liferay.portal.kernel.log.LogFactoryUtil;
22  import com.liferay.portal.kernel.util.GetterUtil;
23  import com.liferay.portal.kernel.util.OrderByComparator;
24  import com.liferay.portal.kernel.util.PortalClassLoaderUtil;
25  import com.liferay.portal.kernel.util.StringBundler;
26  import com.liferay.portal.kernel.util.StringPool;
27  import com.liferay.portal.kernel.util.StringUtil;
28  import com.liferay.portal.kernel.util.Validator;
29  import com.liferay.portal.kernel.xml.Document;
30  import com.liferay.portal.kernel.xml.Element;
31  import com.liferay.portal.kernel.xml.SAXReaderUtil;
32  import com.liferay.portal.util.PortalUtil;
33  
34  import java.io.IOException;
35  import java.io.InputStream;
36  
37  import java.sql.Connection;
38  import java.sql.DatabaseMetaData;
39  import java.sql.SQLException;
40  
41  import java.util.HashMap;
42  import java.util.Map;
43  import java.util.Properties;
44  import java.util.concurrent.atomic.AtomicReference;
45  
46  /**
47   * <a href="CustomSQL.java.html"><b><i>View Source</i></b></a>
48   *
49   * @author Brian Wing Shun Chan
50   * @author Bruno Farache
51   */
52  public class CustomSQL {
53  
54      public static final String DB2_FUNCTION_IS_NOT_NULL =
55          "CAST(? AS VARCHAR(32672)) IS NOT NULL";
56  
57      public static final String DB2_FUNCTION_IS_NULL =
58          "CAST(? AS VARCHAR(32672)) IS NULL";
59  
60      public static final String INFORMIX_FUNCTION_IS_NOT_NULL =
61          "NOT lportal.isnull(?)";
62  
63      public static final String INFORMIX_FUNCTION_IS_NULL = "lportal.isnull(?)";
64  
65      public static final String MYSQL_FUNCTION_IS_NOT_NULL =
66          "IFNULL(?, '1') = '0'";
67  
68      public static final String MYSQL_FUNCTION_IS_NULL = "IFNULL(?, '1') = '1'";
69  
70      public static final String SYBASE_FUNCTION_IS_NOT_NULL =
71          "ISNULL(?, '1') = '0'";
72  
73      public static final String SYBASE_FUNCTION_IS_NULL = "ISNULL(?, '1') = '1'";
74  
75      public CustomSQL() throws SQLException {
76          Connection con = DataAccess.getConnection();
77  
78          String functionIsNull = PortalUtil.getCustomSQLFunctionIsNull();
79          String functionIsNotNull = PortalUtil.getCustomSQLFunctionIsNotNull();
80  
81          try {
82              if (Validator.isNotNull(functionIsNull) &&
83                  Validator.isNotNull(functionIsNotNull)) {
84  
85                  _functionIsNull = functionIsNull;
86                  _functionIsNotNull = functionIsNotNull;
87  
88                  if (_log.isDebugEnabled()) {
89                      _log.info(
90                          "functionIsNull is manually set to " + functionIsNull);
91                      _log.info(
92                          "functionIsNotNull is manually set to " +
93                              functionIsNotNull);
94                  }
95              }
96              else if (con != null) {
97                  DatabaseMetaData metaData = con.getMetaData();
98  
99                  String dbName = GetterUtil.getString(
100                     metaData.getDatabaseProductName());
101 
102                 if (_log.isInfoEnabled()) {
103                     _log.info("Database name " + dbName);
104                 }
105 
106                 if (dbName.startsWith("DB2")) {
107                     _vendorDB2 = true;
108                     _functionIsNull = DB2_FUNCTION_IS_NULL;
109                     _functionIsNotNull = DB2_FUNCTION_IS_NOT_NULL;
110 
111                     if (_log.isInfoEnabled()) {
112                         _log.info("Detected DB2 with database name " + dbName);
113                     }
114                 }
115                 else if (dbName.startsWith("Informix")) {
116                     _vendorInformix = true;
117                     _functionIsNull = INFORMIX_FUNCTION_IS_NULL;
118                     _functionIsNotNull = INFORMIX_FUNCTION_IS_NOT_NULL;
119 
120                     if (_log.isInfoEnabled()) {
121                         _log.info(
122                             "Detected Informix with database name " + dbName);
123                     }
124                 }
125                 else if (dbName.startsWith("MySQL")) {
126                     _vendorMySQL = true;
127                     //_functionIsNull = MYSQL_FUNCTION_IS_NULL;
128                     //_functionIsNotNull = MYSQL_FUNCTION_IS_NOT_NULL;
129 
130                     if (_log.isInfoEnabled()) {
131                         _log.info(
132                             "Detected MySQL with database name " + dbName);
133                     }
134                 }
135                 else if (dbName.startsWith("Sybase") || dbName.equals("ASE")) {
136                     _vendorSybase = true;
137                     _functionIsNull = SYBASE_FUNCTION_IS_NULL;
138                     _functionIsNotNull = SYBASE_FUNCTION_IS_NOT_NULL;
139 
140                     if (_log.isInfoEnabled()) {
141                         _log.info(
142                             "Detected Sybase with database name " + dbName);
143                     }
144                 }
145                 else if (dbName.startsWith("Oracle")) {
146                     _vendorOracle = true;
147 
148                     if (_log.isInfoEnabled()) {
149                         _log.info(
150                             "Detected Oracle with database name " + dbName);
151                     }
152                 }
153                 else if (dbName.startsWith("PostgreSQL")) {
154                     _vendorPostgreSQL = true;
155 
156                     if (_log.isInfoEnabled()) {
157                         _log.info(
158                             "Detected PostgreSQL with database name " + dbName);
159                     }
160                 }
161                 else {
162                     if (_log.isDebugEnabled()) {
163                         _log.debug(
164                             "Unable to detect database with name " + dbName);
165                     }
166                 }
167             }
168         }
169         catch (Exception e) {
170             _log.error(e, e);
171         }
172         finally {
173             DataAccess.cleanUp(con);
174         }
175 
176         _sqlPool = new HashMap<String, String>();
177 
178         try {
179             ClassLoader classLoader = getClass().getClassLoader();
180 
181             String[] configs = getConfigs();
182 
183             for (int i = 0; i < configs.length; i++) {
184                 read(classLoader, configs[i]);
185             }
186         }
187         catch (Exception e) {
188             _log.error(e, e);
189         }
190     }
191 
192     public String get(String id) {
193         return _sqlPool.get(id);
194     }
195 
196     /**
197      * Returns true if Hibernate is connecting to a DB2 database.
198      *
199      * @return true if Hibernate is connecting to a DB2 database
200      */
201     public boolean isVendorDB2() {
202         return _vendorDB2;
203     }
204 
205     /**
206      * Returns true if Hibernate is connecting to an Informix database.
207      *
208      * @return true if Hibernate is connecting to an Informix database
209      */
210     public boolean isVendorInformix() {
211         return _vendorInformix;
212     }
213 
214     /**
215      * Returns true if Hibernate is connecting to a MySQL database.
216      *
217      * @return true if Hibernate is connecting to a MySQL database
218      */
219     public boolean isVendorMySQL() {
220         return _vendorMySQL;
221     }
222 
223     /**
224      * Returns true if Hibernate is connecting to an Oracle database. Oracle has
225      * a nasty bug where it treats '' as a NULL value. See
226      * http://thedailywtf.com/forums/thread/26879.aspx for more information on
227      * this nasty bug.
228      *
229      * @return true if Hibernate is connecting to an Oracle database
230      */
231     public boolean isVendorOracle() {
232         return _vendorOracle;
233     }
234 
235     /**
236      * Returns true if Hibernate is connecting to a PostgreSQL database.
237      *
238      * @return true if Hibernate is connecting to a PostgreSQL database
239      */
240     public boolean isVendorPostgreSQL() {
241         return _vendorPostgreSQL;
242     }
243 
244     /**
245      * Returns true if Hibernate is connecting to a Sybase database.
246      *
247      * @return true if Hibernate is connecting to a Sybase database
248      */
249     public boolean isVendorSybase() {
250         return _vendorSybase;
251     }
252 
253     public String[] keywords(String keywords) {
254         return keywords(keywords, true);
255     }
256 
257     public String[] keywords(String keywords, boolean lowerCase) {
258         if (lowerCase) {
259             keywords = keywords.toLowerCase();
260         }
261 
262         keywords = keywords.trim();
263 
264         String[] keywordsArray = StringUtil.split(keywords, StringPool.SPACE);
265 
266         for (int i = 0; i < keywordsArray.length; i++) {
267             String keyword = keywordsArray[i];
268 
269             keywordsArray[i] =
270                 StringPool.PERCENT + keyword + StringPool.PERCENT;
271         }
272 
273         return keywordsArray;
274     }
275 
276     public String[] keywords(String[] keywordsArray) {
277         return keywords(keywordsArray, true);
278     }
279 
280     public String[] keywords(String[] keywordsArray, boolean lowerCase) {
281         if ((keywordsArray == null) || (keywordsArray.length == 0)) {
282             keywordsArray = new String[] {null};
283         }
284 
285         if (lowerCase) {
286             for (int i = 0; i < keywordsArray.length; i++) {
287                 keywordsArray[i] = StringUtil.lowerCase(keywordsArray[i]);
288             }
289         }
290 
291         return keywordsArray;
292     }
293 
294     public String removeGroupBy(String sql) {
295         int x = sql.indexOf(_GROUP_BY_CLAUSE);
296 
297         if (x != -1) {
298             int y = sql.indexOf(_ORDER_BY_CLAUSE);
299 
300             if (y == -1) {
301                 sql = sql.substring(0, x);
302             }
303             else {
304                 sql = sql.substring(0, x) + sql.substring(y);
305             }
306         }
307 
308         return sql;
309     }
310 
311     public String removeOrderBy(String sql) {
312 
313         // See LPS-8719
314 
315         AtomicReference<String> sqlAtomicReference =
316             new AtomicReference<String>(sql);
317 
318         int pos = sqlAtomicReference.get().indexOf(_ORDER_BY_CLAUSE);
319 
320         if (pos != -1) {
321             sql = sqlAtomicReference.get().substring(0, pos);
322         }
323 
324         /*int pos = sql.indexOf(_ORDER_BY_CLAUSE);
325 
326         if (pos != -1) {
327             sql = sql.substring(0, pos);
328         }*/
329 
330         return sql;
331     }
332 
333     public String replaceAndOperator(String sql, boolean andOperator) {
334         String andOrConnector = "OR";
335         String andOrNullCheck = "AND ? IS NOT NULL";
336 
337         if (andOperator) {
338             andOrConnector = "AND";
339             andOrNullCheck = "OR ? IS NULL";
340         }
341 
342         sql = StringUtil.replace(
343             sql,
344             new String[] {
345                 "[$AND_OR_CONNECTOR$]", "[$AND_OR_NULL_CHECK$]"
346             },
347             new String[] {
348                 andOrConnector, andOrNullCheck
349             });
350 
351         if (_vendorPostgreSQL) {
352             sql = StringUtil.replace(
353                 sql,
354                 new String[] {
355                     "Date >= ? AND ? IS NOT NULL",
356                     "Date <= ? AND ? IS NOT NULL",
357                     "Date >= ? OR ? IS NULL",
358                     "Date <= ? OR ? IS NULL"
359                 },
360                 new String[] {
361                     "Date >= ? AND CAST(? AS TIMESTAMP) IS NOT NULL",
362                     "Date <= ? AND CAST(? AS TIMESTAMP) IS NOT NULL",
363                     "Date >= ? OR CAST(? AS TIMESTAMP) IS NULL",
364                     "Date <= ? OR CAST(? AS TIMESTAMP) IS NULL"
365                 });
366         }
367 
368         sql = replaceIsNull(sql);
369 
370         return sql;
371     }
372 
373     public String replaceIsNull(String sql) {
374         if (Validator.isNotNull(_functionIsNull)) {
375             sql = StringUtil.replace(
376                 sql,
377                 new String[] {
378                     "? IS NULL", "? IS NOT NULL"
379                 },
380                 new String[] {
381                     _functionIsNull,
382                     _functionIsNotNull
383                 });
384         }
385 
386         return sql;
387     }
388 
389     public String replaceKeywords(
390         String sql, String field, int[] values, boolean last) {
391 
392         StringBundler oldSql = new StringBundler(6);
393 
394         oldSql.append("(");
395         oldSql.append(field);
396         oldSql.append(" = ?)");
397 
398         if (!last) {
399             oldSql.append(" [$AND_OR_CONNECTOR$]");
400         }
401 
402         if ((values == null) || (values.length == 0)) {
403             return StringUtil.replace(sql, oldSql.toString(), StringPool.BLANK);
404         }
405 
406         StringBundler newSql = new StringBundler(values.length * 6 + 3);
407 
408         newSql.append("(");
409 
410         for (int i = 0; i < values.length; i++) {
411             if (i > 0) {
412                 newSql.append(" OR ");
413             }
414 
415             newSql.append("(");
416             newSql.append(field);
417             newSql.append(" = ?)");
418         }
419 
420         newSql.append(")");
421 
422         if (!last) {
423             newSql.append(" [$AND_OR_CONNECTOR$]");
424         }
425 
426         return StringUtil.replace(sql, oldSql.toString(), newSql.toString());
427     }
428 
429     public String replaceKeywords(
430         String sql, String field, String operator, boolean last,
431         String[] values) {
432 
433         if (values.length == 0) {
434             return sql;
435         }
436 
437         StringBundler oldSql = new StringBundler(6);
438 
439         oldSql.append("(");
440         oldSql.append(field);
441         oldSql.append(" ");
442         oldSql.append(operator);
443         oldSql.append(" ? [$AND_OR_NULL_CHECK$])");
444 
445         if (!last) {
446             oldSql.append(" [$AND_OR_CONNECTOR$]");
447         }
448 
449         StringBundler newSql = new StringBundler(values.length * 6 + 3);
450 
451         newSql.append("(");
452 
453         for (int i = 0; i < values.length; i++) {
454             if (i > 0) {
455                 newSql.append(" OR ");
456             }
457 
458             newSql.append("(");
459             newSql.append(field);
460             newSql.append(" ");
461             newSql.append(operator);
462             newSql.append(" ? [$AND_OR_NULL_CHECK$])");
463         }
464 
465         newSql.append(")");
466 
467         if (!last) {
468             newSql.append(" [$AND_OR_CONNECTOR$]");
469         }
470 
471         return StringUtil.replace(sql, oldSql.toString(), newSql.toString());
472     }
473 
474     public String replaceGroupBy(String sql, String groupBy) {
475         if (groupBy == null) {
476             return sql;
477         }
478 
479         int x = sql.indexOf(_GROUP_BY_CLAUSE);
480 
481         if (x != -1) {
482             int y = sql.indexOf(_ORDER_BY_CLAUSE);
483 
484             if (y == -1) {
485                 sql = sql.substring(0, x + _GROUP_BY_CLAUSE.length()).concat(
486                     groupBy);
487             }
488             else {
489                 sql = sql.substring(0, x + _GROUP_BY_CLAUSE.length()).concat(
490                     groupBy).concat(sql.substring(y));
491             }
492         }
493         else {
494             int y = sql.indexOf(_ORDER_BY_CLAUSE);
495 
496             if (y == -1) {
497                 sql = sql.concat(_GROUP_BY_CLAUSE).concat(groupBy);
498             }
499             else {
500                 StringBundler sb = new StringBundler();
501 
502                 sb.append(sql.substring(0, y));
503                 sb.append(_GROUP_BY_CLAUSE);
504                 sb.append(groupBy);
505                 sb.append(sql.substring(y));
506 
507                 sql = sb.toString();
508             }
509         }
510 
511         return sql;
512     }
513 
514     public String replaceOrderBy(String sql, OrderByComparator obc) {
515         if (obc == null) {
516             return sql;
517         }
518 
519         return removeOrderBy(sql).concat(_ORDER_BY_CLAUSE).concat(
520             obc.getOrderBy());
521     }
522 
523     protected String[] getConfigs() {
524         if (PortalClassLoaderUtil.getClassLoader() ==
525                 CustomSQL.class.getClassLoader()) {
526 
527             Properties propsUtil = PortalUtil.getPortalProperties();
528 
529             return StringUtil.split(
530                 propsUtil.getProperty("custom.sql.configs"));
531         }
532         else {
533             return new String[] {"custom-sql/default.xml"};
534         }
535     }
536 
537     protected void read(ClassLoader classLoader, String source)
538         throws Exception {
539 
540         InputStream is = classLoader.getResourceAsStream(source);
541 
542         if (is == null) {
543             return;
544         }
545 
546         if (_log.isDebugEnabled()) {
547             _log.debug("Loading " + source);
548         }
549 
550         Document document = SAXReaderUtil.read(is);
551 
552         Element rootElement = document.getRootElement();
553 
554         for (Element sqlElement : rootElement.elements("sql")) {
555             String file = sqlElement.attributeValue("file");
556 
557             if (Validator.isNotNull(file)) {
558                 read(classLoader, file);
559             }
560             else {
561                 String id = sqlElement.attributeValue("id");
562                 String content = transform(sqlElement.getText());
563 
564                 content = replaceIsNull(content);
565 
566                 _sqlPool.put(id, content);
567             }
568         }
569     }
570 
571     protected String transform(String sql) {
572         sql = PortalUtil.transformCustomSQL(sql);
573 
574         StringBundler sb = new StringBundler();
575 
576         try {
577             UnsyncBufferedReader unsyncBufferedReader =
578                 new UnsyncBufferedReader(new UnsyncStringReader(sql));
579 
580             String line = null;
581 
582             while ((line = unsyncBufferedReader.readLine()) != null) {
583                 sb.append(line.trim());
584                 sb.append(StringPool.SPACE);
585             }
586 
587             unsyncBufferedReader.close();
588         }
589         catch (IOException ioe) {
590             return sql;
591         }
592 
593         return sb.toString();
594     }
595 
596     private static final String _GROUP_BY_CLAUSE = " GROUP BY ";
597 
598     private static final String _ORDER_BY_CLAUSE = " ORDER BY ";
599 
600     private static Log _log = LogFactoryUtil.getLog(CustomSQL.class);
601 
602     private String _functionIsNotNull;
603     private String _functionIsNull;
604     private Map<String, String> _sqlPool;
605     private boolean _vendorDB2;
606     private boolean _vendorInformix;
607     private boolean _vendorMySQL;
608     private boolean _vendorOracle;
609     private boolean _vendorPostgreSQL;
610     private boolean _vendorSybase;
611 
612 }