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