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