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