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