001    /**
002     * Copyright (c) 2000-2011 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                    String dbType = db.getType();
039    
040                    if (dbType.equals(DB.TYPE_DB2)) {
041                            _vendorDB2 = true;
042                    }
043                    else if (dbType.equals(DB.TYPE_DERBY)) {
044                            _vendorDerby = true;
045                    }
046                    else if (dbType.equals(DB.TYPE_MYSQL)) {
047                            _vendorMySQL = true;
048                    }
049                    else if (db.getType().equals(DB.TYPE_ORACLE)) {
050                            _vendorOracle = true;
051                    }
052                    else if (dbType.equals(DB.TYPE_POSTGRESQL)) {
053                            _vendorPostgreSQL = true;
054                    }
055                    else if (dbType.equals(DB.TYPE_SQLSERVER)) {
056                            _vendorSQLServer = true;
057                    }
058                    else if (dbType.equals(DB.TYPE_SYBASE)) {
059                            _vendorSybase = true;
060                    }
061            }
062    
063            private String _removeLower(String sql) {
064                    int x = sql.indexOf(_LOWER_OPEN);
065    
066                    if (x == -1) {
067                            return sql;
068                    }
069    
070                    StringBuilder sb = new StringBuilder(sql.length());
071    
072                    int y = 0;
073    
074                    while (true) {
075                            sb.append(sql.substring(y, x));
076    
077                            y = sql.indexOf(_LOWER_CLOSE, x);
078    
079                            if (y == -1) {
080                                    sb.append(sql.substring(x));
081    
082                                    break;
083                            }
084    
085                            sb.append(sql.substring(x + _LOWER_OPEN.length(), y));
086    
087                            y++;
088    
089                            x = sql.indexOf(_LOWER_OPEN, y);
090    
091                            if (x == -1) {
092                                    sb.append(sql.substring(y));
093    
094                                    break;
095                            }
096                    }
097    
098                    sql = sb.toString();
099    
100                    return sql;
101            }
102    
103            private String _replaceCastText(String sql) {
104                    Matcher matcher = _castTextPattern.matcher(sql);
105    
106                    if (_vendorDB2) {
107                            return matcher.replaceAll("CAST($1 AS VARCHAR(500))");
108                    }
109                    else if (_vendorDerby) {
110                            return matcher.replaceAll("CAST($1 AS CHAR(254))");
111                    }
112                    else if (_vendorPostgreSQL) {
113                            return matcher.replaceAll("CAST($1 AS TEXT)");
114                    }
115                    else if (_vendorSQLServer || _vendorSybase) {
116                            return matcher.replaceAll("CAST($1 AS NVARCHAR)");
117                    }
118                    else {
119                            return matcher.replaceAll("$1");
120                    }
121            }
122    
123            private String _replaceIntegerDivision(String sql) {
124                    Matcher matcher = _integerDivisionPattern.matcher(sql);
125    
126                    if (_vendorMySQL) {
127                            return matcher.replaceAll("$1 DIV $2");
128                    }
129                    else if (_vendorOracle) {
130                            return matcher.replaceAll("TRUNC($1 / $2)");
131                    }
132                    else {
133                            return matcher.replaceAll("$1 / $2");
134                    }
135            }
136    
137            private String _replaceMod(String sql) {
138                    Matcher matcher = _modPattern.matcher(sql);
139    
140                    return matcher.replaceAll("$1 % $2");
141            }
142    
143            private String _replaceNegativeComparison(String sql) {
144                    Matcher matcher = _negativeComparisonPattern.matcher(sql);
145    
146                    return matcher.replaceAll("$1 ($2)");
147            }
148    
149            private String _replaceUnion(String sql) {
150                    Matcher matcher = _unionAllPattern.matcher(sql);
151    
152                    return matcher.replaceAll("$1 $2");
153            }
154    
155            private String _transform(String sql) {
156                    if (sql == null) {
157                            return sql;
158                    }
159    
160                    String newSQL = sql;
161    
162                    newSQL = _replaceCastText(newSQL);
163                    newSQL = _replaceIntegerDivision(newSQL);
164    
165                    if (_vendorDerby) {
166                            newSQL = _replaceUnion(newSQL);
167                    }
168                    else if (_vendorMySQL) {
169                            DB db = DBFactoryUtil.getDB();
170    
171                            if (!db.isSupportsStringCaseSensitiveQuery()) {
172                                    newSQL = _removeLower(newSQL);
173                            }
174                    }
175                    else if (_vendorPostgreSQL) {
176                            newSQL = _replaceNegativeComparison(newSQL);
177                    }
178                    else if (_vendorSQLServer || _vendorSybase) {
179                            newSQL = _replaceMod(newSQL);
180                    }
181                    if (_log.isDebugEnabled()) {
182                            _log.debug("Original SQL " + sql);
183                            _log.debug("Modified SQL " + newSQL);
184                    }
185    
186                    return newSQL;
187            }
188    
189            private static final String _LOWER_CLOSE = StringPool.CLOSE_PARENTHESIS;
190    
191            private static final String _LOWER_OPEN = "lower(";
192    
193            private static Log _log = LogFactoryUtil.getLog(SQLTransformer.class);
194    
195            private static SQLTransformer _instance = new SQLTransformer();
196    
197            private static Pattern _castTextPattern = Pattern.compile(
198                    "CAST_TEXT\\((.+?)\\)", Pattern.CASE_INSENSITIVE);
199            private static Pattern _integerDivisionPattern = Pattern.compile(
200                    "INTEGER_DIV\\((.+?),(.+?)\\)", Pattern.CASE_INSENSITIVE);
201            private static Pattern _modPattern = Pattern.compile(
202                    "MOD\\((.+?),(.+?)\\)", Pattern.CASE_INSENSITIVE);
203            private static Pattern _negativeComparisonPattern = Pattern.compile(
204                    "(!= )?(-([0-9]+)?)", Pattern.CASE_INSENSITIVE);
205            private static Pattern _unionAllPattern = Pattern.compile(
206                    "SELECT \\* FROM(.*)TEMP_TABLE(.*)", Pattern.CASE_INSENSITIVE);
207    
208            private boolean _vendorDB2;
209            private boolean _vendorDerby;
210            private boolean _vendorMySQL;
211            private boolean _vendorOracle;
212            private boolean _vendorPostgreSQL;
213            private boolean _vendorSQLServer;
214            private boolean _vendorSybase;
215    
216    }