001    /**
002     * Copyright (c) 2000-2010 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.portal.dao.orm.common;
016    
017    import com.liferay.portal.kernel.dao.db.DB;
018    import com.liferay.portal.kernel.dao.db.DBFactoryUtil;
019    import com.liferay.portal.kernel.log.Log;
020    import com.liferay.portal.kernel.log.LogFactoryUtil;
021    import com.liferay.portal.kernel.util.StringPool;
022    
023    import java.util.regex.Matcher;
024    import java.util.regex.Pattern;
025    
026    /**
027     * @author Brian Wing Shun Chan
028     */
029    public class SQLTransformer {
030    
031            public static String transform(String sql) {
032                    return _instance._transform(sql);
033            }
034    
035            private SQLTransformer() {
036                    DB db = DBFactoryUtil.getDB();
037    
038                    if (db.getType().equals(DB.TYPE_MYSQL)) {
039                            _vendorMySQL = true;
040                    }
041                    else if (db.getType().equals(DB.TYPE_ORACLE)) {
042                            _vendorOracle = true;
043                    }
044                    else if (db.getType().equals(DB.TYPE_POSTGRESQL)) {
045                            _vendorPostgreSQL = true;
046                    }
047                    else if (db.getType().equals(DB.TYPE_SQLSERVER)) {
048                            _vendorSQLServer = true;
049                    }
050            }
051    
052            private String _removeLower(String sql) {
053                    int x = sql.indexOf(_LOWER_OPEN);
054    
055                    if (x == -1) {
056                            return sql;
057                    }
058    
059                    StringBuilder sb = new StringBuilder(sql.length());
060    
061                    int y = 0;
062    
063                    while (true) {
064                            sb.append(sql.substring(y, x));
065    
066                            y = sql.indexOf(_LOWER_CLOSE, x);
067    
068                            if (y == -1) {
069                                    sb.append(sql.substring(x));
070    
071                                    break;
072                            }
073    
074                            sb.append(sql.substring(x + _LOWER_OPEN.length(), y));
075    
076                            y++;
077    
078                            x = sql.indexOf(_LOWER_OPEN, y);
079    
080                            if (x == -1) {
081                                    sb.append(sql.substring(y));
082    
083                                    break;
084                            }
085                    }
086    
087                    sql = sb.toString();
088    
089                    return sql;
090            }
091    
092            private String _replaceCastText(String sql) {
093                    Matcher matcher = _castTextPattern.matcher(sql);
094    
095                    if (_vendorPostgreSQL) {
096                            return matcher.replaceAll("CAST($1 AS TEXT)");
097                    }
098                    else if (_vendorSQLServer) {
099                            return matcher.replaceAll("CAST($1 AS NVARCHAR)");
100                    }
101                    else {
102                            return matcher.replaceAll("$1");
103                    }
104            }
105    
106            private String _replaceMod(String sql) {
107                    Matcher matcher = _modPattern.matcher(sql);
108    
109                    return matcher.replaceAll("$1 % $2");
110            }
111    
112            private String _transform(String sql) {
113                    if (sql == null) {
114                            return sql;
115                    }
116    
117                    String newSQL = sql;
118    
119                    if (_vendorMySQL) {
120                            DB db = DBFactoryUtil.getDB();
121    
122                            if (!db.isSupportsStringCaseSensitiveQuery()) {
123                                    newSQL = _removeLower(newSQL);
124                            }
125                    }
126                    else if (_vendorOracle) {
127                            newSQL = _replaceMod(newSQL);
128                    }
129                    else if (_vendorSQLServer) {
130                            newSQL = _replaceMod(newSQL);
131                    }
132    
133                    newSQL = _replaceCastText(newSQL);
134    
135                    if (_log.isDebugEnabled()) {
136                            _log.debug("Original SQL " + sql);
137                            _log.debug("Modified SQL " + newSQL);
138                    }
139    
140                    return newSQL;
141            }
142    
143            private static final String _LOWER_CLOSE = StringPool.CLOSE_PARENTHESIS;
144    
145            private static final String _LOWER_OPEN = "lower(";
146    
147            private static Log _log = LogFactoryUtil.getLog(SQLTransformer.class);
148    
149            private static SQLTransformer _instance = new SQLTransformer();
150    
151            private static Pattern _castTextPattern = Pattern.compile(
152                    "CAST_TEXT\\((.+?)\\)", Pattern.CASE_INSENSITIVE);
153            private static Pattern _modPattern = Pattern.compile(
154                    "MOD\\((.+?),(.+?)\\)", Pattern.CASE_INSENSITIVE);
155    
156            private boolean _vendorMySQL;
157            private boolean _vendorOracle;
158            private boolean _vendorPostgreSQL;
159            private boolean _vendorSQLServer;
160    
161    }