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