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.Iterator;
43  import java.util.Map;
44  import java.util.Properties;
45  import java.util.concurrent.atomic.AtomicReference;
46  
47  /**
48   * <a href="CustomSQL.java.html"><b><i>View Source</i></b></a>
49   *
50   * @author Brian Wing Shun Chan
51   * @author Bruno Farache
52   */
53  public class CustomSQL {
54  
55      public static final String DB2_FUNCTION_IS_NULL =
56          "CAST(? AS VARCHAR(32672)) IS NULL";
57  
58      public static final String DB2_FUNCTION_IS_NOT_NULL =
59          "CAST(? AS VARCHAR(32672)) IS NOT NULL";
60  
61      public static final String INFORMIX_FUNCTION_IS_NULL = "lportal.isnull(?)";
62  
63      public static final String INFORMIX_FUNCTION_IS_NOT_NULL =
64          "NOT " + INFORMIX_FUNCTION_IS_NULL;
65  
66      public static final String MYSQL_FUNCTION_IS_NULL = "IFNULL(?, '1') = '1'";
67  
68      public static final String MYSQL_FUNCTION_IS_NOT_NULL =
69          "IFNULL(?, '1') = '0'";
70  
71      public static final String SYBASE_FUNCTION_IS_NULL = "ISNULL(?, '1') = '1'";
72  
73      public static final String SYBASE_FUNCTION_IS_NOT_NULL =
74          "ISNULL(?, '1') = '0'";
75  
76      public CustomSQL() throws SQLException {
77          Connection con = DataAccess.getConnection();
78  
79          String functionIsNull = PortalUtil.getCustomSQLFunctionIsNull();
80          String functionIsNotNull = PortalUtil.getCustomSQLFunctionIsNotNull();
81  
82          try {
83              if (Validator.isNotNull(functionIsNull) &&
84                  Validator.isNotNull(functionIsNotNull)) {
85  
86                  _functionIsNull = functionIsNull;
87                  _functionIsNotNull = functionIsNotNull;
88  
89                  if (_log.isDebugEnabled()) {
90                      _log.info(
91                          "functionIsNull is manually set to " + functionIsNull);
92                      _log.info(
93                          "functionIsNotNull is manually set to " +
94                              functionIsNotNull);
95                  }
96              }
97              else if (con != null) {
98                  DatabaseMetaData metaData = con.getMetaData();
99  
100                 String dbName = GetterUtil.getString(
101                     metaData.getDatabaseProductName());
102 
103                 if (_log.isInfoEnabled()) {
104                     _log.info("Database name " + dbName);
105                 }
106 
107                 if (dbName.startsWith("DB2")) {
108                     _vendorDB2 = true;
109                     _functionIsNull = DB2_FUNCTION_IS_NULL;
110                     _functionIsNotNull = DB2_FUNCTION_IS_NOT_NULL;
111 
112                     if (_log.isInfoEnabled()) {
113                         _log.info("Detected DB2 with database name " + dbName);
114                     }
115                 }
116                 else if (dbName.startsWith("Informix")) {
117                     _vendorInformix = true;
118                     _functionIsNull = INFORMIX_FUNCTION_IS_NULL;
119                     _functionIsNotNull = INFORMIX_FUNCTION_IS_NOT_NULL;
120 
121                     if (_log.isInfoEnabled()) {
122                         _log.info(
123                             "Detected Informix with database name " + dbName);
124                     }
125                 }
126                 else if (dbName.startsWith("MySQL")) {
127                     _vendorMySQL = true;
128                     //_functionIsNull = MYSQL_FUNCTION_IS_NULL;
129                     //_functionIsNotNull = MYSQL_FUNCTION_IS_NOT_NULL;
130 
131                     if (_log.isInfoEnabled()) {
132                         _log.info(
133                             "Detected MySQL with database name " + dbName);
134                     }
135                 }
136                 else if (dbName.startsWith("Sybase") || dbName.equals("ASE")) {
137                     _vendorSybase = true;
138                     _functionIsNull = SYBASE_FUNCTION_IS_NULL;
139                     _functionIsNotNull = SYBASE_FUNCTION_IS_NOT_NULL;
140 
141                     if (_log.isInfoEnabled()) {
142                         _log.info(
143                             "Detected Sybase with database name " + dbName);
144                     }
145                 }
146                 else if (dbName.startsWith("Oracle")) {
147                     _vendorOracle = true;
148 
149                     if (_log.isInfoEnabled()) {
150                         _log.info(
151                             "Detected Oracle with database name " + dbName);
152                     }
153                 }
154                 else if (dbName.startsWith("PostgreSQL")) {
155                     _vendorPostgreSQL = true;
156 
157                     if (_log.isInfoEnabled()) {
158                         _log.info(
159                             "Detected PostgreSQL with database name " + dbName);
160                     }
161                 }
162                 else {
163                     if (_log.isDebugEnabled()) {
164                         _log.debug(
165                             "Unable to detect database with name " + dbName);
166                     }
167                 }
168             }
169         }
170         catch (Exception e) {
171             _log.error(e, e);
172         }
173         finally {
174             DataAccess.cleanUp(con);
175         }
176 
177         _sqlPool = new HashMap<String, String>();
178 
179         try {
180             ClassLoader classLoader = getClass().getClassLoader();
181 
182             String[] configs = getConfigs();
183 
184             for (int i = 0; i < configs.length; i++) {
185                 read(classLoader, configs[i]);
186             }
187         }
188         catch (Exception e) {
189             _log.error(e, e);
190         }
191     }
192 
193     public String get(String id) {
194         return _sqlPool.get(id);
195     }
196 
197     /**
198      * Returns true if Hibernate is connecting to a DB2 database.
199      *
200      * @return true if Hibernate is connecting to a DB2 database
201      */
202     public boolean isVendorDB2() {
203         return _vendorDB2;
204     }
205 
206     /**
207      * Returns true if Hibernate is connecting to an Informix database.
208      *
209      * @return true if Hibernate is connecting to an Informix database
210      */
211     public boolean isVendorInformix() {
212         return _vendorInformix;
213     }
214 
215     /**
216      * Returns true if Hibernate is connecting to a MySQL database.
217      *
218      * @return true if Hibernate is connecting to a MySQL database
219      */
220     public boolean isVendorMySQL() {
221         return _vendorMySQL;
222     }
223 
224     /**
225      * Returns true if Hibernate is connecting to an Oracle database. Oracle has
226      * a nasty bug where it treats '' as a NULL value. See
227      * http://thedailywtf.com/forums/thread/26879.aspx for more information on
228      * this nasty bug.
229      *
230      * @return true if Hibernate is connecting to an Oracle database
231      */
232     public boolean isVendorOracle() {
233         return _vendorOracle;
234     }
235 
236     /**
237      * Returns true if Hibernate is connecting to a PostgreSQL database.
238      *
239      * @return true if Hibernate is connecting to a PostgreSQL database
240      */
241     public boolean isVendorPostgreSQL() {
242         return _vendorPostgreSQL;
243     }
244 
245     /**
246      * Returns true if Hibernate is connecting to a Sybase database.
247      *
248      * @return true if Hibernate is connecting to a Sybase database
249      */
250     public boolean isVendorSybase() {
251         return _vendorSybase;
252     }
253 
254     public String[] keywords(String keywords) {
255         return keywords(keywords, true);
256     }
257 
258     public String[] keywords(String keywords, boolean lowerCase) {
259         if (lowerCase) {
260             keywords = keywords.toLowerCase();
261         }
262 
263         keywords = keywords.trim();
264 
265         String[] keywordsArray = StringUtil.split(keywords, StringPool.SPACE);
266 
267         for (int i = 0; i < keywordsArray.length; i++) {
268             String keyword = keywordsArray[i];
269 
270             keywordsArray[i] =
271                 StringPool.PERCENT + keyword + StringPool.PERCENT;
272         }
273 
274         return keywordsArray;
275     }
276 
277     public String[] keywords(String[] keywordsArray) {
278         return keywords(keywordsArray, true);
279     }
280 
281     public String[] keywords(String[] keywordsArray, boolean lowerCase) {
282         if ((keywordsArray == null) || (keywordsArray.length == 0)) {
283             keywordsArray = new String[] {null};
284         }
285 
286         if (lowerCase) {
287             for (int i = 0; i < keywordsArray.length; i++) {
288                 keywordsArray[i] = StringUtil.lowerCase(keywordsArray[i]);
289             }
290         }
291 
292         return keywordsArray;
293     }
294 
295     public String replaceAndOperator(String sql, boolean andOperator) {
296         String andOrConnector = "OR";
297         String andOrNullCheck = "AND ? IS NOT NULL";
298 
299         if (andOperator) {
300             andOrConnector = "AND";
301             andOrNullCheck = "OR ? IS NULL";
302         }
303 
304         sql = StringUtil.replace(
305             sql,
306             new String[] {
307                 "[$AND_OR_CONNECTOR$]", "[$AND_OR_NULL_CHECK$]"
308             },
309             new String[] {
310                 andOrConnector, andOrNullCheck
311             });
312 
313         if (_vendorPostgreSQL) {
314             sql = StringUtil.replace(
315                 sql,
316                 new String[] {
317                     "Date >= ? AND ? IS NOT NULL",
318                     "Date <= ? AND ? IS NOT NULL",
319                     "Date >= ? OR ? IS NULL",
320                     "Date <= ? OR ? IS NULL"
321                 },
322                 new String[] {
323                     "Date >= ? AND CAST(? AS TIMESTAMP) IS NOT NULL",
324                     "Date <= ? AND CAST(? AS TIMESTAMP) IS NOT NULL",
325                     "Date >= ? OR CAST(? AS TIMESTAMP) IS NULL",
326                     "Date <= ? OR CAST(? AS TIMESTAMP) IS NULL"
327                 });
328         }
329 
330         sql = replaceIsNull(sql);
331 
332         return sql;
333     }
334 
335     public String replaceIsNull(String sql) {
336         if (Validator.isNotNull(_functionIsNull)) {
337             sql = StringUtil.replace(
338                 sql,
339                 new String[] {
340                     "? IS NULL", "? IS NOT NULL"
341                 },
342                 new String[] {
343                     _functionIsNull,
344                     _functionIsNotNull
345                 });
346         }
347 
348         return sql;
349     }
350 
351     public String replaceKeywords(
352         String sql, String field, String operator, boolean last,
353         String[] values) {
354 
355         if (values.length == 0) {
356             return sql;
357         }
358 
359         StringBundler oldSql = new StringBundler(6);
360 
361         oldSql.append("(");
362         oldSql.append(field);
363         oldSql.append(" ");
364         oldSql.append(operator);
365         oldSql.append(" ? [$AND_OR_NULL_CHECK$])");
366 
367         if (!last) {
368             oldSql.append(" [$AND_OR_CONNECTOR$]");
369         }
370 
371         StringBundler newSql = new StringBundler(values.length * 6 + 3);
372 
373         newSql.append("(");
374 
375         for (int i = 0; i < values.length; i++) {
376             if (i > 0) {
377                 newSql.append(" OR ");
378             }
379 
380             newSql.append("(");
381             newSql.append(field);
382             newSql.append(" ");
383             newSql.append(operator);
384             newSql.append(" ? [$AND_OR_NULL_CHECK$])");
385         }
386 
387         newSql.append(")");
388 
389         if (!last) {
390             newSql.append(" [$AND_OR_CONNECTOR$]");
391         }
392 
393         return StringUtil.replace(sql, oldSql.toString(), newSql.toString());
394     }
395 
396     public String removeOrderBy(String sql) {
397 
398         // See LPS-8719
399 
400         AtomicReference<String> sqlAtomicReference =
401             new AtomicReference<String>(sql);
402 
403         int pos = sqlAtomicReference.get().indexOf(" ORDER BY ");
404 
405         if (pos != -1) {
406             sql = sqlAtomicReference.get().substring(0, pos);
407         }
408 
409         /*int pos = sql.indexOf(" ORDER BY ");
410 
411         if (pos != -1) {
412             sql = sql.substring(0, pos);
413         }*/
414 
415         return sql;
416     }
417 
418     public String replaceOrderBy(String sql, OrderByComparator obc) {
419         if (obc == null) {
420             return sql;
421         }
422 
423         return removeOrderBy(sql).concat(" ORDER BY ").concat(obc.getOrderBy());
424     }
425 
426     protected String[] getConfigs() {
427         if (PortalClassLoaderUtil.getClassLoader() ==
428                 CustomSQL.class.getClassLoader()) {
429 
430             Properties propsUtil = PortalUtil.getPortalProperties();
431 
432             return StringUtil.split(
433                 propsUtil.getProperty("custom.sql.configs"));
434         }
435         else {
436             return new String[] {"custom-sql/default.xml"};
437         }
438     }
439 
440     protected void read(ClassLoader classLoader, String source)
441         throws Exception {
442 
443         InputStream is = classLoader.getResourceAsStream(source);
444 
445         if (is == null) {
446             return;
447         }
448 
449         if (_log.isDebugEnabled()) {
450             _log.debug("Loading " + source);
451         }
452 
453         Document doc = SAXReaderUtil.read(is);
454 
455         Element root = doc.getRootElement();
456 
457         Iterator<Element> itr = root.elements("sql").iterator();
458 
459         while (itr.hasNext()) {
460             Element sql = itr.next();
461 
462             String file = sql.attributeValue("file");
463 
464             if (Validator.isNotNull(file)) {
465                 read(classLoader, file);
466             }
467             else {
468                 String id = sql.attributeValue("id");
469                 String content = transform(sql.getText());
470 
471                 content = replaceIsNull(content);
472 
473                 _sqlPool.put(id, content);
474             }
475         }
476     }
477 
478     protected String transform(String sql) {
479         sql = PortalUtil.transformCustomSQL(sql);
480 
481         StringBundler sb = new StringBundler();
482 
483         try {
484             UnsyncBufferedReader unsyncBufferedReader =
485                 new UnsyncBufferedReader(new UnsyncStringReader(sql));
486 
487             String line = null;
488 
489             while ((line = unsyncBufferedReader.readLine()) != null) {
490                 sb.append(line.trim());
491                 sb.append(StringPool.SPACE);
492             }
493 
494             unsyncBufferedReader.close();
495         }
496         catch (IOException ioe) {
497             return sql;
498         }
499 
500         return sb.toString();
501     }
502 
503     private static Log _log = LogFactoryUtil.getLog(CustomSQL.class);
504 
505     private boolean _vendorDB2;
506     private boolean _vendorInformix;
507     private boolean _vendorMySQL;
508     private boolean _vendorOracle;
509     private boolean _vendorPostgreSQL;
510     private boolean _vendorSybase;
511     private String _functionIsNull;
512     private String _functionIsNotNull;
513     private Map<String, String> _sqlPool;
514 
515 }