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