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  
36  import java.sql.Connection;
37  import java.sql.DatabaseMetaData;
38  import java.sql.SQLException;
39  
40  import java.util.HashMap;
41  import java.util.Iterator;
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_NULL =
54          "CAST(? AS VARCHAR(32672)) IS NULL";
55  
56      public static final String DB2_FUNCTION_IS_NOT_NULL =
57          "CAST(? AS VARCHAR(32672)) IS NOT NULL";
58  
59      public static final String INFORMIX_FUNCTION_IS_NULL = "lportal.isnull(?)";
60  
61      public static final String INFORMIX_FUNCTION_IS_NOT_NULL =
62          "NOT " + INFORMIX_FUNCTION_IS_NULL;
63  
64      public static final String MYSQL_FUNCTION_IS_NULL = "IFNULL(?, '1') = '1'";
65  
66      public static final String MYSQL_FUNCTION_IS_NOT_NULL =
67          "IFNULL(?, '1') = '0'";
68  
69      public static final String SYBASE_FUNCTION_IS_NULL = "ISNULL(?, '1') = '1'";
70  
71      public static final String SYBASE_FUNCTION_IS_NOT_NULL =
72          "ISNULL(?, '1') = '0'";
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 replaceAndOperator(String sql, boolean andOperator) {
294         String andOrConnector = "OR";
295         String andOrNullCheck = "AND ? IS NOT NULL";
296 
297         if (andOperator) {
298             andOrConnector = "AND";
299             andOrNullCheck = "OR ? IS NULL";
300         }
301 
302         sql = StringUtil.replace(
303             sql,
304             new String[] {
305                 "[$AND_OR_CONNECTOR$]", "[$AND_OR_NULL_CHECK$]"
306             },
307             new String[] {
308                 andOrConnector, andOrNullCheck
309             });
310 
311         if (_vendorPostgreSQL) {
312             sql = StringUtil.replace(
313                 sql,
314                 new String[] {
315                     "Date >= ? AND ? IS NOT NULL",
316                     "Date <= ? AND ? IS NOT NULL",
317                     "Date >= ? OR ? IS NULL",
318                     "Date <= ? OR ? IS NULL"
319                 },
320                 new String[] {
321                     "Date >= ? AND CAST(? AS TIMESTAMP) IS NOT NULL",
322                     "Date <= ? AND CAST(? AS TIMESTAMP) IS NOT NULL",
323                     "Date >= ? OR CAST(? AS TIMESTAMP) IS NULL",
324                     "Date <= ? OR CAST(? AS TIMESTAMP) IS NULL"
325                 });
326         }
327 
328         sql = replaceIsNull(sql);
329 
330         return sql;
331     }
332 
333     public String replaceIsNull(String sql) {
334         if (Validator.isNotNull(_functionIsNull)) {
335             sql = StringUtil.replace(
336                 sql,
337                 new String[] {
338                     "? IS NULL", "? IS NOT NULL"
339                 },
340                 new String[] {
341                     _functionIsNull,
342                     _functionIsNotNull
343                 });
344         }
345 
346         return sql;
347     }
348 
349     public String replaceKeywords(
350         String sql, String field, String operator, boolean last,
351         String[] values) {
352 
353         if (values.length == 0) {
354             return sql;
355         }
356 
357         StringBundler oldSql = new StringBundler(6);
358 
359         oldSql.append("(");
360         oldSql.append(field);
361         oldSql.append(" ");
362         oldSql.append(operator);
363         oldSql.append(" ? [$AND_OR_NULL_CHECK$])");
364 
365         if (!last) {
366             oldSql.append(" [$AND_OR_CONNECTOR$]");
367         }
368 
369         StringBundler newSql = new StringBundler(values.length * 6 + 3);
370 
371         newSql.append("(");
372 
373         for (int i = 0; i < values.length; i++) {
374             if (i > 0) {
375                 newSql.append(" OR ");
376             }
377 
378             newSql.append("(");
379             newSql.append(field);
380             newSql.append(" ");
381             newSql.append(operator);
382             newSql.append(" ? [$AND_OR_NULL_CHECK$])");
383         }
384 
385         newSql.append(")");
386 
387         if (!last) {
388             newSql.append(" [$AND_OR_CONNECTOR$]");
389         }
390 
391         return StringUtil.replace(sql, oldSql.toString(), newSql.toString());
392     }
393 
394     public String removeOrderBy(String sql) {
395         int pos = sql.indexOf(" ORDER BY ");
396 
397         if (pos != -1) {
398             sql = sql.substring(0, pos);
399         }
400 
401         return sql;
402     }
403 
404     public String replaceOrderBy(String sql, OrderByComparator obc) {
405         if (obc == null) {
406             return sql;
407         }
408 
409         return removeOrderBy(sql).concat(" ORDER BY ").concat(obc.getOrderBy());
410     }
411 
412     protected String[] getConfigs() {
413         if (PortalClassLoaderUtil.getClassLoader() ==
414                 CustomSQL.class.getClassLoader()) {
415 
416             Properties propsUtil = PortalUtil.getPortalProperties();
417 
418             return StringUtil.split(
419                 propsUtil.getProperty("custom.sql.configs"));
420         }
421         else {
422             return new String[] {"custom-sql/default.xml"};
423         }
424     }
425 
426     protected void read(ClassLoader classLoader, String source)
427         throws Exception {
428 
429         String xml = null;
430 
431         try {
432             xml = StringUtil.read(classLoader, source);
433         }
434         catch (Exception e) {
435             _log.warn("Cannot load " + source);
436         }
437 
438         if (xml == null) {
439             return;
440         }
441 
442         if (_log.isDebugEnabled()) {
443             _log.debug("Loading " + source);
444         }
445 
446         Document doc = SAXReaderUtil.read(new UnsyncStringReader(xml));
447 
448         Element root = doc.getRootElement();
449 
450         Iterator<Element> itr = root.elements("sql").iterator();
451 
452         while (itr.hasNext()) {
453             Element sql = itr.next();
454 
455             String file = sql.attributeValue("file");
456 
457             if (Validator.isNotNull(file)) {
458                 read(classLoader, file);
459             }
460             else {
461                 String id = sql.attributeValue("id");
462                 String content = transform(sql.getText());
463 
464                 content = replaceIsNull(content);
465 
466                 _sqlPool.put(id, content);
467             }
468         }
469     }
470 
471     protected String transform(String sql) {
472         sql = PortalUtil.transformCustomSQL(sql);
473 
474         StringBundler sb = new StringBundler();
475 
476         try {
477             UnsyncBufferedReader unsyncBufferedReader =
478                 new UnsyncBufferedReader(new UnsyncStringReader(sql));
479 
480             String line = null;
481 
482             while ((line = unsyncBufferedReader.readLine()) != null) {
483                 sb.append(line.trim());
484                 sb.append(StringPool.SPACE);
485             }
486 
487             unsyncBufferedReader.close();
488         }
489         catch (IOException ioe) {
490             return sql;
491         }
492 
493         return sb.toString();
494     }
495 
496     private static Log _log = LogFactoryUtil.getLog(CustomSQL.class);
497 
498     private boolean _vendorDB2;
499     private boolean _vendorInformix;
500     private boolean _vendorMySQL;
501     private boolean _vendorOracle;
502     private boolean _vendorPostgreSQL;
503     private boolean _vendorSybase;
504     private String _functionIsNull;
505     private String _functionIsNotNull;
506     private Map<String, String> _sqlPool;
507 
508 }