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