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