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