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