001    /**
002     * Copyright (c) 2000-2010 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.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_DERBY)) {
039                            _vendorDerby = true;
040                    }
041                    else if (db.getType().equals(DB.TYPE_MYSQL)) {
042                            _vendorMySQL = 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                    else if (db.getType().equals(DB.TYPE_SYBASE)) {
051                            _vendorSybase = true;
052                    }
053            }
054    
055            private String _removeLower(String sql) {
056                    int x = sql.indexOf(_LOWER_OPEN);
057    
058                    if (x == -1) {
059                            return sql;
060                    }
061    
062                    StringBuilder sb = new StringBuilder(sql.length());
063    
064                    int y = 0;
065    
066                    while (true) {
067                            sb.append(sql.substring(y, x));
068    
069                            y = sql.indexOf(_LOWER_CLOSE, x);
070    
071                            if (y == -1) {
072                                    sb.append(sql.substring(x));
073    
074                                    break;
075                            }
076    
077                            sb.append(sql.substring(x + _LOWER_OPEN.length(), y));
078    
079                            y++;
080    
081                            x = sql.indexOf(_LOWER_OPEN, y);
082    
083                            if (x == -1) {
084                                    sb.append(sql.substring(y));
085    
086                                    break;
087                            }
088                    }
089    
090                    sql = sb.toString();
091    
092                    return sql;
093            }
094    
095            private String _replaceCastText(String sql) {
096                    Matcher matcher = _castTextPattern.matcher(sql);
097    
098                    if (_vendorDerby) {
099                            return matcher.replaceAll("CAST($1 AS CHAR(254))");
100                    }
101                    else if (_vendorPostgreSQL) {
102                            return matcher.replaceAll("CAST($1 AS TEXT)");
103                    }
104                    else if (_vendorSQLServer || _vendorSybase) {
105                            return matcher.replaceAll("CAST($1 AS NVARCHAR)");
106                    }
107                    else {
108                            return matcher.replaceAll("$1");
109                    }
110            }
111    
112            private String _replaceMod(String sql) {
113                    Matcher matcher = _modPattern.matcher(sql);
114    
115                    return matcher.replaceAll("$1 % $2");
116            }
117    
118            private String _replaceUnion(String sql) {
119                    Matcher matcher = _unionAllPattern.matcher(sql);
120    
121                    return matcher.replaceAll("$1 $2");
122            }
123    
124            private String _transform(String sql) {
125                    if (sql == null) {
126                            return sql;
127                    }
128    
129                    String newSQL = sql;
130    
131                    newSQL = _replaceCastText(newSQL);
132    
133                    if (_vendorDerby) {
134                            newSQL = _replaceUnion(newSQL);
135                    }
136                    else if (_vendorMySQL) {
137                            DB db = DBFactoryUtil.getDB();
138    
139                            if (!db.isSupportsStringCaseSensitiveQuery()) {
140                                    newSQL = _removeLower(newSQL);
141                            }
142                    }
143                    else if (_vendorSQLServer || _vendorSybase) {
144                            newSQL = _replaceMod(newSQL);
145                    }
146    
147                    if (_log.isDebugEnabled()) {
148                            _log.debug("Original SQL " + sql);
149                            _log.debug("Modified SQL " + newSQL);
150                    }
151    
152                    return newSQL;
153            }
154    
155            private static final String _LOWER_CLOSE = StringPool.CLOSE_PARENTHESIS;
156    
157            private static final String _LOWER_OPEN = "lower(";
158    
159            private static Log _log = LogFactoryUtil.getLog(SQLTransformer.class);
160    
161            private static SQLTransformer _instance = new SQLTransformer();
162    
163            private static Pattern _castTextPattern = Pattern.compile(
164                    "CAST_TEXT\\((.+?)\\)", Pattern.CASE_INSENSITIVE);
165            private static Pattern _modPattern = Pattern.compile(
166                    "MOD\\((.+?),(.+?)\\)", Pattern.CASE_INSENSITIVE);
167            private static Pattern _unionAllPattern = Pattern.compile(
168                    "SELECT \\* FROM(.*)TEMP_TABLE(.*)", Pattern.CASE_INSENSITIVE);
169    
170            private boolean _vendorDerby;
171            private boolean _vendorMySQL;
172            private boolean _vendorPostgreSQL;
173            private boolean _vendorSQLServer;
174            private boolean _vendorSybase;
175    
176    }