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