001    /**
002     * Copyright (c) 2000-2011 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.CharPool;
022    import com.liferay.portal.kernel.util.StringBundler;
023    import com.liferay.portal.kernel.util.StringPool;
024    import com.liferay.portal.kernel.util.StringUtil;
025    
026    import java.util.Map;
027    import java.util.concurrent.ConcurrentHashMap;
028    import java.util.regex.Matcher;
029    import java.util.regex.Pattern;
030    
031    /**
032     * @author Brian Wing Shun Chan
033     * @author Shuyang Zhou
034     */
035    public class SQLTransformer {
036    
037            public static String transform(String sql) {
038                    return _instance._transform(sql);
039            }
040    
041            public static String transformFromHqlToJpql(String sql) {
042                    return _instance._transformFromHqlToJpql(sql);
043            }
044    
045            public static String transformFromJpqlToHql(String sql) {
046                    return _instance._transformFromJpqlToHql(sql);
047            }
048    
049            private SQLTransformer() {
050                    DB db = DBFactoryUtil.getDB();
051    
052                    String dbType = db.getType();
053    
054                    _db = db;
055    
056                    if (dbType.equals(DB.TYPE_DB2)) {
057                            _vendorDB2 = true;
058                    }
059                    else if (dbType.equals(DB.TYPE_DERBY)) {
060                            _vendorDerby = true;
061                    }
062                    else if (dbType.equals(DB.TYPE_FIREBIRD)) {
063                            _vendorFirebird = true;
064                    }
065                    else if (dbType.equals(DB.TYPE_HYPERSONIC)) {
066                            _vendorHypersonic = true;
067                    }
068                    else if (dbType.equals(DB.TYPE_INFORMIX)) {
069                            _vendorInformix = true;
070                    }
071                    else if (dbType.equals(DB.TYPE_INGRES)) {
072                            _vendorIngres = true;
073                    }
074                    else if (dbType.equals(DB.TYPE_INTERBASE)) {
075                            _vendorInterbase = true;
076                    }
077                    else if (dbType.equals(DB.TYPE_MYSQL)) {
078                            _vendorMySQL = true;
079                    }
080                    else if (db.getType().equals(DB.TYPE_ORACLE)) {
081                            _vendorOracle = true;
082                    }
083                    else if (dbType.equals(DB.TYPE_POSTGRESQL)) {
084                            _vendorPostgreSQL = true;
085                    }
086                    else if (dbType.equals(DB.TYPE_SQLSERVER)) {
087                            _vendorSQLServer = true;
088                    }
089                    else if (dbType.equals(DB.TYPE_SYBASE)) {
090                            _vendorSybase = true;
091                    }
092            }
093    
094            private String _removeLower(String sql) {
095                    int x = sql.indexOf(_LOWER_OPEN);
096    
097                    if (x == -1) {
098                            return sql;
099                    }
100    
101                    StringBuilder sb = new StringBuilder(sql.length());
102    
103                    int y = 0;
104    
105                    while (true) {
106                            sb.append(sql.substring(y, x));
107    
108                            y = sql.indexOf(_LOWER_CLOSE, x);
109    
110                            if (y == -1) {
111                                    sb.append(sql.substring(x));
112    
113                                    break;
114                            }
115    
116                            sb.append(sql.substring(x + _LOWER_OPEN.length(), y));
117    
118                            y++;
119    
120                            x = sql.indexOf(_LOWER_OPEN, y);
121    
122                            if (x == -1) {
123                                    sb.append(sql.substring(y));
124    
125                                    break;
126                            }
127                    }
128    
129                    sql = sb.toString();
130    
131                    return sql;
132            }
133    
134            private String _replaceBitwiseCheck(String sql) {
135                    Matcher matcher = _bitwiseCheckPattern.matcher(sql);
136    
137                    if (_vendorDB2 || _vendorHypersonic || _vendorOracle) {
138                            return matcher.replaceAll("BITAND($1, $2)");
139                    }
140                    else if (_vendorDerby) {
141                            return matcher.replaceAll("MOD($1 / $2, 2) != 0");
142                    }
143                    else if (_vendorInformix || _vendorIngres) {
144                            return matcher.replaceAll("BIT_AND($1, $2)");
145                    }
146                    else if (_vendorFirebird || _vendorInterbase) {
147                            return matcher.replaceAll("BIN_AND($1, $2)");
148                    }
149                    else {
150                            return sql;
151                    }
152            }
153    
154            private String _replaceBoolean(String newSQL) {
155                    return StringUtil.replace(
156                            newSQL,
157                            new String[] {"[$FALSE$]", "[$TRUE$]"},
158                            new String[] {_db.getTemplateFalse(), _db.getTemplateTrue()});
159            }
160    
161            private String _replaceCastText(String sql) {
162                    Matcher matcher = _castTextPattern.matcher(sql);
163    
164                    if (_vendorDB2 || _vendorDerby) {
165                            return matcher.replaceAll("CAST($1 AS CHAR(254))");
166                    }
167                    else if (_vendorPostgreSQL) {
168                            return matcher.replaceAll("CAST($1 AS TEXT)");
169                    }
170                    else if (_vendorSQLServer) {
171                            return matcher.replaceAll("CAST($1 AS NVARCHAR(MAX))");
172                    }
173                    else if (_vendorSybase) {
174                            return matcher.replaceAll("CAST($1 AS NVARCHAR)");
175                    }
176                    else {
177                            return matcher.replaceAll("$1");
178                    }
179            }
180    
181            private String _replaceIntegerDivision(String sql) {
182                    Matcher matcher = _integerDivisionPattern.matcher(sql);
183    
184                    if (_vendorMySQL) {
185                            return matcher.replaceAll("$1 DIV $2");
186                    }
187                    else if (_vendorOracle) {
188                            return matcher.replaceAll("TRUNC($1 / $2)");
189                    }
190                    else {
191                            return matcher.replaceAll("$1 / $2");
192                    }
193            }
194    
195            private String _replaceMod(String sql) {
196                    Matcher matcher = _modPattern.matcher(sql);
197    
198                    return matcher.replaceAll("$1 % $2");
199            }
200    
201            private String _replaceNegativeComparison(String sql) {
202                    Matcher matcher = _negativeComparisonPattern.matcher(sql);
203    
204                    return matcher.replaceAll("$1 ($2)");
205            }
206    
207            private String _replaceReplace(String newSQL) {
208                    return StringUtil.replace(newSQL, "replace(", "str_replace(");
209            }
210    
211            private String _replaceUnion(String sql) {
212                    Matcher matcher = _unionAllPattern.matcher(sql);
213    
214                    return matcher.replaceAll("$1 $2");
215            }
216    
217            private String _transform(String sql) {
218                    if (sql == null) {
219                            return sql;
220                    }
221    
222                    String newSQL = sql;
223    
224                    newSQL = _replaceBitwiseCheck(newSQL);
225                    newSQL = _replaceBoolean(newSQL);
226                    newSQL = _replaceCastText(newSQL);
227                    newSQL = _replaceIntegerDivision(newSQL);
228    
229                    if (_vendorDerby) {
230                            newSQL = _replaceUnion(newSQL);
231                    }
232                    else if (_vendorMySQL) {
233                            DB db = DBFactoryUtil.getDB();
234    
235                            if (!db.isSupportsStringCaseSensitiveQuery()) {
236                                    newSQL = _removeLower(newSQL);
237                            }
238                    }
239                    else if (_vendorPostgreSQL) {
240                            newSQL = _replaceNegativeComparison(newSQL);
241                    }
242                    else if (_vendorSQLServer) {
243                            newSQL = _replaceMod(newSQL);
244                    }
245                    else if (_vendorSybase) {
246                            newSQL = _replaceMod(newSQL);
247                            newSQL = _replaceReplace(newSQL);
248                    }
249    
250                    if (_log.isDebugEnabled()) {
251                            _log.debug("Original SQL " + sql);
252                            _log.debug("Modified SQL " + newSQL);
253                    }
254    
255                    return newSQL;
256            }
257    
258            private String _transformFromHqlToJpql(String sql) {
259                    String newSQL = _transformedSqls.get(sql);
260    
261                    if (newSQL != null) {
262                            return newSQL;
263                    }
264    
265                    newSQL = _transform(sql);
266    
267                    newSQL = _transformPositionalParams(newSQL);
268    
269                    newSQL = StringUtil.replace(
270                            newSQL, _HQL_NOT_EQUALS, _JPQL_NOT_EQUALS);
271                    newSQL = StringUtil.replace(
272                            newSQL, _HQL_COMPOSITE_ID_MARKER, _JPQL_DOT_SEPARTOR);
273    
274                    _transformedSqls.put(sql, newSQL);
275    
276                    return newSQL;
277            }
278    
279            private String _transformFromJpqlToHql(String sql) {
280                    String newSQL = _transformedSqls.get(sql);
281    
282                    if (newSQL != null) {
283                            return newSQL;
284                    }
285    
286                    newSQL = _transform(sql);
287    
288                    Matcher matcher = _jpqlCountPattern.matcher(newSQL);
289    
290                    if (matcher.find()) {
291                            String countExpression = matcher.group(1);
292                            String entityAlias = matcher.group(3);
293    
294                            if (entityAlias.equals(countExpression)) {
295                                    newSQL = matcher.replaceFirst(_HQL_COUNT_SQL);
296                            }
297                    }
298    
299                    _transformedSqls.put(sql, newSQL);
300    
301                    return newSQL;
302            }
303    
304            private String _transformPositionalParams(String queryString) {
305                    if (queryString.indexOf(CharPool.QUESTION) == -1) {
306                            return queryString;
307                    }
308    
309                    StringBundler sb = new StringBundler();
310    
311                    int i = 1;
312                    int from = 0;
313                    int to = 0;
314    
315                    while ((to = queryString.indexOf(CharPool.QUESTION, from)) != -1) {
316                            sb.append(queryString.substring(from, to));
317                            sb.append(StringPool.QUESTION);
318                            sb.append(i++);
319    
320                            from = to + 1;
321                    }
322    
323                    sb.append(queryString.substring(from, queryString.length()));
324    
325                    return sb.toString();
326            }
327    
328            private static final String _HQL_COMPOSITE_ID_MARKER = "\\.id\\.";
329    
330            private static final String _HQL_COUNT_SQL = "SELECT COUNT(*) FROM $2 $3";
331    
332            private static final String _HQL_NOT_EQUALS = "!=";
333    
334            private static final String _JPQL_DOT_SEPARTOR = ".";
335    
336            private static final String _JPQL_NOT_EQUALS = "<>";
337    
338            private static final String _LOWER_CLOSE = StringPool.CLOSE_PARENTHESIS;
339    
340            private static final String _LOWER_OPEN = "lower(";
341    
342            private static Log _log = LogFactoryUtil.getLog(SQLTransformer.class);
343    
344            private static SQLTransformer _instance = new SQLTransformer();
345    
346            private static Pattern _bitwiseCheckPattern = Pattern.compile(
347                    "\\(\\((.+?) & (.+?)\\)\\)");
348            private static Pattern _castTextPattern = Pattern.compile(
349                    "CAST_TEXT\\((.+?)\\)", Pattern.CASE_INSENSITIVE);
350            private static Pattern _integerDivisionPattern = Pattern.compile(
351                    "INTEGER_DIV\\((.+?),(.+?)\\)", Pattern.CASE_INSENSITIVE);
352            private static Pattern _jpqlCountPattern = Pattern.compile(
353                    "SELECT COUNT\\((\\S+)\\) FROM (\\S+) (\\S+)");
354            private static Pattern _modPattern = Pattern.compile(
355                    "MOD\\((.+?),(.+?)\\)", Pattern.CASE_INSENSITIVE);
356            private static Pattern _negativeComparisonPattern = Pattern.compile(
357                    "(!=)?( -([0-9]+)?)", Pattern.CASE_INSENSITIVE);
358            private static Map<String, String> _transformedSqls =
359                    new ConcurrentHashMap<String, String>();
360            private static Pattern _unionAllPattern = Pattern.compile(
361                    "SELECT \\* FROM(.*)TEMP_TABLE(.*)", Pattern.CASE_INSENSITIVE);
362    
363            private DB _db;
364            private boolean _vendorDB2;
365            private boolean _vendorDerby;
366            private boolean _vendorFirebird;
367            private boolean _vendorHypersonic;
368            private boolean _vendorInformix;
369            private boolean _vendorIngres;
370            private boolean _vendorInterbase;
371            private boolean _vendorMySQL;
372            private boolean _vendorOracle;
373            private boolean _vendorPostgreSQL;
374            private boolean _vendorSQLServer;
375            private boolean _vendorSybase;
376    
377    }