001    /**
002     * Copyright (c) 2000-2011 Liferay, Inc. All rights reserved.
003     *
004     * This library is free software; you can redistribute it and/or modify it under
005     * the terms of the GNU Lesser General Public License as published by the Free
006     * Software Foundation; either version 2.1 of the License, or (at your option)
007     * any later version.
008     *
009     * This library is distributed in the hope that it will be useful, but WITHOUT
010     * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
011     * FOR A PARTICULAR PURPOSE. See the GNU Lesser General Public License for more
012     * details.
013     */
014    
015    package com.liferay.util.dao.orm;
016    
017    import com.liferay.portal.kernel.dao.jdbc.DataAccess;
018    import com.liferay.portal.kernel.io.unsync.UnsyncBufferedReader;
019    import com.liferay.portal.kernel.io.unsync.UnsyncStringReader;
020    import com.liferay.portal.kernel.log.Log;
021    import com.liferay.portal.kernel.log.LogFactoryUtil;
022    import com.liferay.portal.kernel.util.GetterUtil;
023    import com.liferay.portal.kernel.util.OrderByComparator;
024    import com.liferay.portal.kernel.util.PortalClassLoaderUtil;
025    import com.liferay.portal.kernel.util.StringBundler;
026    import com.liferay.portal.kernel.util.StringPool;
027    import com.liferay.portal.kernel.util.StringUtil;
028    import com.liferay.portal.kernel.util.Validator;
029    import com.liferay.portal.kernel.xml.Document;
030    import com.liferay.portal.kernel.xml.Element;
031    import com.liferay.portal.kernel.xml.SAXReaderUtil;
032    import com.liferay.portal.util.PortalUtil;
033    
034    import java.io.IOException;
035    import java.io.InputStream;
036    
037    import java.sql.Connection;
038    import java.sql.DatabaseMetaData;
039    import java.sql.SQLException;
040    
041    import java.util.HashMap;
042    import java.util.Map;
043    import java.util.Properties;
044    
045    /**
046     * @author Brian Wing Shun Chan
047     * @author Bruno Farache
048     * @author Raymond Augé
049     */
050    public class CustomSQL {
051    
052            public static final String DB2_FUNCTION_IS_NOT_NULL =
053                    "CAST(? AS VARCHAR(32672)) IS NOT NULL";
054    
055            public static final String DB2_FUNCTION_IS_NULL =
056                    "CAST(? AS VARCHAR(32672)) IS NULL";
057    
058            public static final String INFORMIX_FUNCTION_IS_NOT_NULL =
059                    "NOT lportal.isnull(?)";
060    
061            public static final String INFORMIX_FUNCTION_IS_NULL = "lportal.isnull(?)";
062    
063            public static final String MYSQL_FUNCTION_IS_NOT_NULL =
064                    "IFNULL(?, '1') = '0'";
065    
066            public static final String MYSQL_FUNCTION_IS_NULL = "IFNULL(?, '1') = '1'";
067    
068            public static final String SYBASE_FUNCTION_IS_NOT_NULL =
069                    "CONVERT(VARCHAR,?) IS NOT NULL";
070    
071            public static final String SYBASE_FUNCTION_IS_NULL =
072                    "CONVERT(VARCHAR,?) IS NULL";
073    
074            public CustomSQL() throws SQLException {
075                    reloadCustomSQL();
076            }
077    
078            public String appendCriteria(String sql, String criteria) {
079                    if (Validator.isNull(criteria)) {
080                            return sql;
081                    }
082    
083                    if (!criteria.startsWith(StringPool.SPACE)) {
084                            criteria = StringPool.SPACE.concat(criteria);
085                    }
086    
087                    if (!criteria.endsWith(StringPool.SPACE)) {
088                            criteria = criteria.concat(StringPool.SPACE);
089                    }
090    
091                    int pos = sql.indexOf(_GROUP_BY_CLAUSE);
092    
093                    if (pos != -1) {
094                            return sql.substring(0, pos + 1).concat(criteria).concat(
095                                    sql.substring(pos + 1));
096                    }
097    
098                    pos = sql.indexOf(_ORDER_BY_CLAUSE);
099    
100                    if (pos != -1) {
101                            return sql.substring(0, pos + 1).concat(criteria).concat(
102                                    sql.substring(pos + 1));
103                    }
104    
105                    return sql.concat(criteria);
106            }
107    
108            public String get(String id) {
109                    return _sqlPool.get(id);
110            }
111    
112            /**
113             * Returns <code>true</code> if Hibernate is connecting to a DB2 database.
114             *
115             * @return <code>true</code> if Hibernate is connecting to a DB2 database
116             */
117            public boolean isVendorDB2() {
118                    return _vendorDB2;
119            }
120    
121            /**
122             * Returns <code>true</code> if Hibernate is connecting to an Informix
123             * database.
124             *
125             * @return <code>true</code> if Hibernate is connecting to an Informix
126             *         database
127             */
128            public boolean isVendorInformix() {
129                    return _vendorInformix;
130            }
131    
132            /**
133             * Returns <code>true</code> if Hibernate is connecting to a MySQL database.
134             *
135             * @return <code>true</code> if Hibernate is connecting to a MySQL database
136             */
137            public boolean isVendorMySQL() {
138                    return _vendorMySQL;
139            }
140    
141            /**
142             * Returns <code>true</code> if Hibernate is connecting to an Oracle
143             * database. Oracle has a nasty bug where it treats '' as a
144             * <code>NULL</code> value. See
145             * http://thedailywtf.com/forums/thread/26879.aspx for more information on
146             * this nasty bug.
147             *
148             * @return <code>true</code> if Hibernate is connecting to an Oracle
149             *         database
150             */
151            public boolean isVendorOracle() {
152                    return _vendorOracle;
153            }
154    
155            /**
156             * Returns <code>true</code> if Hibernate is connecting to a PostgreSQL
157             * database.
158             *
159             * @return <code>true</code> if Hibernate is connecting to a PostgreSQL
160             *         database
161             */
162            public boolean isVendorPostgreSQL() {
163                    return _vendorPostgreSQL;
164            }
165    
166            /**
167             * Returns <code>true</code> if Hibernate is connecting to a Sybase
168             * database.
169             *
170             * @return <code>true</code> if Hibernate is connecting to a Sybase database
171             */
172            public boolean isVendorSybase() {
173                    return _vendorSybase;
174            }
175    
176            public String[] keywords(String keywords) {
177                    return keywords(keywords, true);
178            }
179    
180            public String[] keywords(String keywords, boolean lowerCase) {
181                    if (Validator.isNull(keywords)) {
182                            return new String[] {null};
183                    }
184    
185                    if (lowerCase) {
186                            keywords = keywords.toLowerCase();
187                    }
188    
189                    keywords = keywords.trim();
190    
191                    String[] keywordsArray = keywords.split("\\s+");
192    
193                    for (int i = 0; i < keywordsArray.length; i++) {
194                            String keyword = keywordsArray[i];
195    
196                            keywordsArray[i] =
197                                    StringPool.PERCENT + keyword + StringPool.PERCENT;
198                    }
199    
200                    return keywordsArray;
201            }
202    
203            public String[] keywords(String[] keywordsArray) {
204                    return keywords(keywordsArray, true);
205            }
206    
207            public String[] keywords(String[] keywordsArray, boolean lowerCase) {
208                    if ((keywordsArray == null) || (keywordsArray.length == 0)) {
209                            return new String[] {null};
210                    }
211    
212                    if (lowerCase) {
213                            for (int i = 0; i < keywordsArray.length; i++) {
214                                    keywordsArray[i] = StringUtil.lowerCase(keywordsArray[i]);
215                            }
216                    }
217    
218                    return keywordsArray;
219            }
220    
221            public void reloadCustomSQL() throws SQLException {
222                    PortalUtil.initCustomSQL();
223    
224                    Connection con = DataAccess.getConnection();
225    
226                    String functionIsNull = PortalUtil.getCustomSQLFunctionIsNull();
227                    String functionIsNotNull = PortalUtil.getCustomSQLFunctionIsNotNull();
228    
229                    try {
230                            if (Validator.isNotNull(functionIsNull) &&
231                                    Validator.isNotNull(functionIsNotNull)) {
232    
233                                    _functionIsNull = functionIsNull;
234                                    _functionIsNotNull = functionIsNotNull;
235    
236                                    if (_log.isDebugEnabled()) {
237                                            _log.info(
238                                                    "functionIsNull is manually set to " + functionIsNull);
239                                            _log.info(
240                                                    "functionIsNotNull is manually set to " +
241                                                            functionIsNotNull);
242                                    }
243                            }
244                            else if (con != null) {
245                                    DatabaseMetaData metaData = con.getMetaData();
246    
247                                    String dbName = GetterUtil.getString(
248                                            metaData.getDatabaseProductName());
249    
250                                    if (_log.isInfoEnabled()) {
251                                            _log.info("Database name " + dbName);
252                                    }
253    
254                                    if (dbName.startsWith("DB2")) {
255                                            _vendorDB2 = true;
256                                            _functionIsNull = DB2_FUNCTION_IS_NULL;
257                                            _functionIsNotNull = DB2_FUNCTION_IS_NOT_NULL;
258    
259                                            if (_log.isInfoEnabled()) {
260                                                    _log.info("Detected DB2 with database name " + dbName);
261                                            }
262                                    }
263                                    else if (dbName.startsWith("Informix")) {
264                                            _vendorInformix = true;
265                                            _functionIsNull = INFORMIX_FUNCTION_IS_NULL;
266                                            _functionIsNotNull = INFORMIX_FUNCTION_IS_NOT_NULL;
267    
268                                            if (_log.isInfoEnabled()) {
269                                                    _log.info(
270                                                            "Detected Informix with database name " + dbName);
271                                            }
272                                    }
273                                    else if (dbName.startsWith("MySQL")) {
274                                            _vendorMySQL = true;
275                                            //_functionIsNull = MYSQL_FUNCTION_IS_NULL;
276                                            //_functionIsNotNull = MYSQL_FUNCTION_IS_NOT_NULL;
277    
278                                            if (_log.isInfoEnabled()) {
279                                                    _log.info(
280                                                            "Detected MySQL with database name " + dbName);
281                                            }
282                                    }
283                                    else if (dbName.startsWith("Sybase") || dbName.equals("ASE")) {
284                                            _vendorSybase = true;
285                                            _functionIsNull = SYBASE_FUNCTION_IS_NULL;
286                                            _functionIsNotNull = SYBASE_FUNCTION_IS_NOT_NULL;
287    
288                                            if (_log.isInfoEnabled()) {
289                                                    _log.info(
290                                                            "Detected Sybase with database name " + dbName);
291                                            }
292                                    }
293                                    else if (dbName.startsWith("Oracle")) {
294                                            _vendorOracle = true;
295    
296                                            if (_log.isInfoEnabled()) {
297                                                    _log.info(
298                                                            "Detected Oracle with database name " + dbName);
299                                            }
300                                    }
301                                    else if (dbName.startsWith("PostgreSQL")) {
302                                            _vendorPostgreSQL = true;
303    
304                                            if (_log.isInfoEnabled()) {
305                                                    _log.info(
306                                                            "Detected PostgreSQL with database name " + dbName);
307                                            }
308                                    }
309                                    else {
310                                            if (_log.isDebugEnabled()) {
311                                                    _log.debug(
312                                                            "Unable to detect database with name " + dbName);
313                                            }
314                                    }
315                            }
316                    }
317                    catch (Exception e) {
318                            _log.error(e, e);
319                    }
320                    finally {
321                            DataAccess.cleanUp(con);
322                    }
323    
324                    if (_sqlPool == null) {
325                            _sqlPool = new HashMap<String, String>();
326                    }
327                    else {
328                             _sqlPool.clear();
329                    }
330    
331                    try {
332                            Class<?> clazz = getClass();
333    
334                            ClassLoader classLoader = clazz.getClassLoader();
335    
336                            String[] configs = getConfigs();
337    
338                            for (String _config : configs) {
339                                    read(classLoader, _config);
340                            }
341                    }
342                    catch (Exception e) {
343                            _log.error(e, e);
344                    }
345            }
346    
347            public String removeGroupBy(String sql) {
348                    int x = sql.indexOf(_GROUP_BY_CLAUSE);
349    
350                    if (x != -1) {
351                            int y = sql.indexOf(_ORDER_BY_CLAUSE);
352    
353                            if (y == -1) {
354                                    sql = sql.substring(0, x);
355                            }
356                            else {
357                                    sql = sql.substring(0, x) + sql.substring(y);
358                            }
359                    }
360    
361                    return sql;
362            }
363    
364            public String removeOrderBy(String sql) {
365                    int pos = sql.indexOf(_ORDER_BY_CLAUSE);
366    
367                    if (pos != -1) {
368                            sql = sql.substring(0, pos);
369                    }
370    
371                    return sql;
372            }
373    
374            public String replaceAndOperator(String sql, boolean andOperator) {
375                    String andOrConnector = "OR";
376                    String andOrNullCheck = "AND ? IS NOT NULL";
377    
378                    if (andOperator) {
379                            andOrConnector = "AND";
380                            andOrNullCheck = "OR ? IS NULL";
381                    }
382    
383                    sql = StringUtil.replace(
384                            sql,
385                            new String[] {
386                                    "[$AND_OR_CONNECTOR$]", "[$AND_OR_NULL_CHECK$]"
387                            },
388                            new String[] {
389                                    andOrConnector, andOrNullCheck
390                            });
391    
392                    if (_vendorPostgreSQL) {
393                            sql = StringUtil.replace(
394                                    sql,
395                                    new String[] {
396                                            "Date >= ? AND ? IS NOT NULL",
397                                            "Date <= ? AND ? IS NOT NULL",
398                                            "Date >= ? OR ? IS NULL",
399                                            "Date <= ? OR ? IS NULL"
400                                    },
401                                    new String[] {
402                                            "Date >= ? AND CAST(? AS TIMESTAMP) IS NOT NULL",
403                                            "Date <= ? AND CAST(? AS TIMESTAMP) IS NOT NULL",
404                                            "Date >= ? OR CAST(? AS TIMESTAMP) IS NULL",
405                                            "Date <= ? OR CAST(? AS TIMESTAMP) IS NULL"
406                                    });
407                    }
408    
409                    sql = replaceIsNull(sql);
410    
411                    return sql;
412            }
413    
414            public String replaceIsNull(String sql) {
415                    if (Validator.isNotNull(_functionIsNull)) {
416                            sql = StringUtil.replace(
417                                    sql,
418                                    new String[] {
419                                            "? IS NULL", "? IS NOT NULL"
420                                    },
421                                    new String[] {
422                                            _functionIsNull,
423                                            _functionIsNotNull
424                                    });
425                    }
426    
427                    return sql;
428            }
429    
430            public String replaceKeywords(
431                    String sql, String field, boolean last, int[] values) {
432    
433                    if ((values != null) && (values.length == 1)) {
434                            return sql;
435                    }
436    
437                    StringBundler oldSql = new StringBundler(4);
438    
439                    oldSql.append("(");
440                    oldSql.append(field);
441                    oldSql.append(" = ?)");
442    
443                    if (!last) {
444                            oldSql.append(" [$AND_OR_CONNECTOR$]");
445                    }
446    
447                    if ((values == null) || (values.length == 0)) {
448                            return StringUtil.replace(sql, oldSql.toString(), StringPool.BLANK);
449                    }
450    
451                    StringBundler newSql = new StringBundler(values.length * 4 + 3);
452    
453                    newSql.append("(");
454    
455                    for (int i = 0; i < values.length; i++) {
456                            if (i > 0) {
457                                    newSql.append(" OR ");
458                            }
459    
460                            newSql.append("(");
461                            newSql.append(field);
462                            newSql.append(" = ?)");
463                    }
464    
465                    newSql.append(")");
466    
467                    if (!last) {
468                            newSql.append(" [$AND_OR_CONNECTOR$]");
469                    }
470    
471                    return StringUtil.replace(sql, oldSql.toString(), newSql.toString());
472            }
473    
474            public String replaceKeywords(
475                    String sql, String field, boolean last, long[] values) {
476    
477                    if ((values != null) && (values.length == 1)) {
478                            return sql;
479                    }
480    
481                    StringBundler oldSql = new StringBundler(4);
482    
483                    oldSql.append("(");
484                    oldSql.append(field);
485                    oldSql.append(" = ?)");
486    
487                    if (!last) {
488                            oldSql.append(" [$AND_OR_CONNECTOR$]");
489                    }
490    
491                    if ((values == null) || (values.length == 0)) {
492                            return StringUtil.replace(sql, oldSql.toString(), StringPool.BLANK);
493                    }
494    
495                    StringBundler newSql = new StringBundler(values.length * 4 + 3);
496    
497                    newSql.append("(");
498    
499                    for (int i = 0; i < values.length; i++) {
500                            if (i > 0) {
501                                    newSql.append(" OR ");
502                            }
503    
504                            newSql.append("(");
505                            newSql.append(field);
506                            newSql.append(" = ?)");
507                    }
508    
509                    newSql.append(")");
510    
511                    if (!last) {
512                            newSql.append(" [$AND_OR_CONNECTOR$]");
513                    }
514    
515                    return StringUtil.replace(sql, oldSql.toString(), newSql.toString());
516            }
517    
518            public String replaceKeywords(
519                    String sql, String field, String operator, boolean last,
520                    String[] values) {
521    
522                    if ((values != null) && (values.length <= 1)) {
523                            return sql;
524                    }
525    
526                    StringBundler oldSql = new StringBundler(6);
527    
528                    oldSql.append("(");
529                    oldSql.append(field);
530                    oldSql.append(" ");
531                    oldSql.append(operator);
532                    oldSql.append(" ? [$AND_OR_NULL_CHECK$])");
533    
534                    if (!last) {
535                            oldSql.append(" [$AND_OR_CONNECTOR$]");
536                    }
537    
538                    StringBundler newSql = new StringBundler(values.length * 6 + 3);
539    
540                    newSql.append("(");
541    
542                    for (int i = 0; i < values.length; i++) {
543                            if (i > 0) {
544                                    newSql.append(" OR ");
545                            }
546    
547                            newSql.append("(");
548                            newSql.append(field);
549                            newSql.append(" ");
550                            newSql.append(operator);
551                            newSql.append(" ? [$AND_OR_NULL_CHECK$])");
552                    }
553    
554                    newSql.append(")");
555    
556                    if (!last) {
557                            newSql.append(" [$AND_OR_CONNECTOR$]");
558                    }
559    
560                    return StringUtil.replace(sql, oldSql.toString(), newSql.toString());
561            }
562    
563            public String replaceGroupBy(String sql, String groupBy) {
564                    if (groupBy == null) {
565                            return sql;
566                    }
567    
568                    int x = sql.indexOf(_GROUP_BY_CLAUSE);
569    
570                    if (x != -1) {
571                            int y = sql.indexOf(_ORDER_BY_CLAUSE);
572    
573                            if (y == -1) {
574                                    sql = sql.substring(0, x + _GROUP_BY_CLAUSE.length()).concat(
575                                            groupBy);
576                            }
577                            else {
578                                    sql = sql.substring(0, x + _GROUP_BY_CLAUSE.length()).concat(
579                                            groupBy).concat(sql.substring(y));
580                            }
581                    }
582                    else {
583                            int y = sql.indexOf(_ORDER_BY_CLAUSE);
584    
585                            if (y == -1) {
586                                    sql = sql.concat(_GROUP_BY_CLAUSE).concat(groupBy);
587                            }
588                            else {
589                                    StringBundler sb = new StringBundler();
590    
591                                    sb.append(sql.substring(0, y));
592                                    sb.append(_GROUP_BY_CLAUSE);
593                                    sb.append(groupBy);
594                                    sb.append(sql.substring(y));
595    
596                                    sql = sb.toString();
597                            }
598                    }
599    
600                    return sql;
601            }
602    
603            public String replaceOrderBy(String sql, OrderByComparator obc) {
604                    if (obc == null) {
605                            return sql;
606                    }
607    
608                    String orderBy = obc.getOrderBy();
609    
610                    int pos = sql.indexOf(_ORDER_BY_CLAUSE);
611    
612                    if ((pos != -1) && (pos < sql.length())) {
613                            sql = sql.substring(0, pos + _ORDER_BY_CLAUSE.length()).concat(
614                                    orderBy);
615                    }
616                    else {
617                            sql = sql.concat(_ORDER_BY_CLAUSE).concat(orderBy);
618                    }
619    
620                    return sql;
621            }
622    
623            protected String[] getConfigs() {
624                    if (PortalClassLoaderUtil.getClassLoader() ==
625                                    CustomSQL.class.getClassLoader()) {
626    
627                            Properties propsUtil = PortalUtil.getPortalProperties();
628    
629                            return StringUtil.split(
630                                    propsUtil.getProperty("custom.sql.configs"));
631                    }
632                    else {
633                            return new String[] {"custom-sql/default.xml"};
634                    }
635            }
636    
637            protected void read(ClassLoader classLoader, String source)
638                    throws Exception {
639    
640                    InputStream is = classLoader.getResourceAsStream(source);
641    
642                    if (is == null) {
643                            return;
644                    }
645    
646                    if (_log.isDebugEnabled()) {
647                            _log.debug("Loading " + source);
648                    }
649    
650                    Document document = SAXReaderUtil.read(is);
651    
652                    Element rootElement = document.getRootElement();
653    
654                    for (Element sqlElement : rootElement.elements("sql")) {
655                            String file = sqlElement.attributeValue("file");
656    
657                            if (Validator.isNotNull(file)) {
658                                    read(classLoader, file);
659                            }
660                            else {
661                                    String id = sqlElement.attributeValue("id");
662                                    String content = transform(sqlElement.getText());
663    
664                                    content = replaceIsNull(content);
665    
666                                    _sqlPool.put(id, content);
667                            }
668                    }
669            }
670    
671            protected String transform(String sql) {
672                    sql = PortalUtil.transformCustomSQL(sql);
673    
674                    StringBundler sb = new StringBundler();
675    
676                    try {
677                            UnsyncBufferedReader unsyncBufferedReader =
678                                    new UnsyncBufferedReader(new UnsyncStringReader(sql));
679    
680                            String line = null;
681    
682                            while ((line = unsyncBufferedReader.readLine()) != null) {
683                                    sb.append(line.trim());
684                                    sb.append(StringPool.SPACE);
685                            }
686    
687                            unsyncBufferedReader.close();
688                    }
689                    catch (IOException ioe) {
690                            return sql;
691                    }
692    
693                    return sb.toString();
694            }
695    
696            private static final String _GROUP_BY_CLAUSE = " GROUP BY ";
697    
698            private static final String _ORDER_BY_CLAUSE = " ORDER BY ";
699    
700            private static Log _log = LogFactoryUtil.getLog(CustomSQL.class);
701    
702            private String _functionIsNotNull;
703            private String _functionIsNull;
704            private Map<String, String> _sqlPool;
705            private boolean _vendorDB2;
706            private boolean _vendorInformix;
707            private boolean _vendorMySQL;
708            private boolean _vendorOracle;
709            private boolean _vendorPostgreSQL;
710            private boolean _vendorSybase;
711    
712    }