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