001    /**
002     * Copyright (c) 2000-2013 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.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 void reloadSQLTransformer() {
038                    _instance._reloadSQLTransformer();
039            }
040    
041            public static String transform(String sql) {
042                    return _instance._transform(sql);
043            }
044    
045            public static String transformFromHqlToJpql(String sql) {
046                    return _instance._transformFromHqlToJpql(sql);
047            }
048    
049            public static String transformFromJpqlToHql(String sql) {
050                    return _instance._transformFromJpqlToHql(sql);
051            }
052    
053            private SQLTransformer() {
054                    _reloadSQLTransformer();
055            }
056    
057            private void _reloadSQLTransformer() {
058                    if (_transformedSqls == null) {
059                            _transformedSqls = new ConcurrentHashMap<String, String>();
060                    }
061                    else {
062                            _transformedSqls.clear();
063                    }
064    
065                    _vendorDB2 = false;
066                    _vendorDerby = false;
067                    _vendorFirebird = false;
068                    _vendorHypersonic = false;
069                    _vendorInformix = false;
070                    _vendorIngres = false;
071                    _vendorInterbase = false;
072                    _vendorMySQL = false;
073                    _vendorOracle = false;
074                    _vendorPostgreSQL = false;
075                    _vendorSQLServer = false;
076                    _vendorSybase = false;
077    
078                    DB db = DBFactoryUtil.getDB();
079    
080                    String dbType = db.getType();
081    
082                    _db = db;
083    
084                    if (dbType.equals(DB.TYPE_DB2)) {
085                            _vendorDB2 = true;
086                    }
087                    else if (dbType.equals(DB.TYPE_DERBY)) {
088                            _vendorDerby = true;
089                    }
090                    else if (dbType.equals(DB.TYPE_FIREBIRD)) {
091                            _vendorFirebird = true;
092                    }
093                    else if (dbType.equals(DB.TYPE_HYPERSONIC)) {
094                            _vendorHypersonic = true;
095                    }
096                    else if (dbType.equals(DB.TYPE_INFORMIX)) {
097                            _vendorInformix = true;
098                    }
099                    else if (dbType.equals(DB.TYPE_INGRES)) {
100                            _vendorIngres = true;
101                    }
102                    else if (dbType.equals(DB.TYPE_INTERBASE)) {
103                            _vendorInterbase = true;
104                    }
105                    else if (dbType.equals(DB.TYPE_MYSQL)) {
106                            _vendorMySQL = true;
107                    }
108                    else if (db.getType().equals(DB.TYPE_ORACLE)) {
109                            _vendorOracle = true;
110                    }
111                    else if (dbType.equals(DB.TYPE_POSTGRESQL)) {
112                            _vendorPostgreSQL = true;
113                    }
114                    else if (dbType.equals(DB.TYPE_SQLSERVER)) {
115                            _vendorSQLServer = true;
116                    }
117                    else if (dbType.equals(DB.TYPE_SYBASE)) {
118                            _vendorSybase = true;
119                    }
120            }
121    
122            private String _removeLower(String sql) {
123                    int x = sql.indexOf(_LOWER_OPEN);
124    
125                    if (x == -1) {
126                            return sql;
127                    }
128    
129                    StringBuilder sb = new StringBuilder(sql.length());
130    
131                    int y = 0;
132    
133                    while (true) {
134                            sb.append(sql.substring(y, x));
135    
136                            y = sql.indexOf(_LOWER_CLOSE, x);
137    
138                            if (y == -1) {
139                                    sb.append(sql.substring(x));
140    
141                                    break;
142                            }
143    
144                            sb.append(sql.substring(x + _LOWER_OPEN.length(), y));
145    
146                            y++;
147    
148                            x = sql.indexOf(_LOWER_OPEN, y);
149    
150                            if (x == -1) {
151                                    sb.append(sql.substring(y));
152    
153                                    break;
154                            }
155                    }
156    
157                    sql = sb.toString();
158    
159                    return sql;
160            }
161    
162            private String _replaceBitwiseCheck(String sql) {
163                    Matcher matcher = _bitwiseCheckPattern.matcher(sql);
164    
165                    if (_vendorDerby) {
166                            return matcher.replaceAll("MOD($1 / $2, 2) != 0");
167                    }
168                    else if (_vendorInformix || _vendorIngres) {
169                            return matcher.replaceAll("BIT_AND($1, $2)");
170                    }
171                    else if (_vendorFirebird || _vendorInterbase) {
172                            return matcher.replaceAll("BIN_AND($1, $2)");
173                    }
174                    else if (_vendorMySQL || _vendorPostgreSQL || _vendorSQLServer ||
175                                     _vendorSybase) {
176    
177                            return matcher.replaceAll("($1 & $2)");
178                    }
179                    else {
180                            return sql;
181                    }
182            }
183    
184            private String _replaceBoolean(String newSQL) {
185                    return StringUtil.replace(
186                            newSQL, new String[] {"[$FALSE$]", "[$TRUE$]"},
187                            new String[] {_db.getTemplateFalse(), _db.getTemplateTrue()});
188            }
189    
190            private String _replaceCastLong(String sql) {
191                    Matcher matcher = _castLongPattern.matcher(sql);
192    
193                    if (_vendorHypersonic) {
194                            return matcher.replaceAll("CONVERT($1, SQL_BIGINT)");
195                    }
196                    else if (_vendorSybase) {
197                            return matcher.replaceAll("CONVERT(BIGINT, $1)");
198                    }
199                    else {
200                            return matcher.replaceAll("$1");
201                    }
202            }
203    
204            private String _replaceCastText(String sql) {
205                    Matcher matcher = _castTextPattern.matcher(sql);
206    
207                    if (_vendorDB2 || _vendorDerby) {
208                            return matcher.replaceAll("CAST($1 AS CHAR(254))");
209                    }
210                    else if (_vendorHypersonic) {
211                            return matcher.replaceAll("CONVERT($1, SQL_VARCHAR)");
212                    }
213                    else if (_vendorOracle) {
214                            return matcher.replaceAll("CAST($1 AS VARCHAR(4000))");
215                    }
216                    else if (_vendorPostgreSQL) {
217                            return matcher.replaceAll("CAST($1 AS TEXT)");
218                    }
219                    else if (_vendorSQLServer) {
220                            return matcher.replaceAll("CAST($1 AS NVARCHAR(MAX))");
221                    }
222                    else if (_vendorSybase) {
223                            return matcher.replaceAll("CAST($1 AS NVARCHAR(5461))");
224                    }
225                    else {
226                            return matcher.replaceAll("$1");
227                    }
228            }
229    
230            private String _replaceCrossJoin(String sql) {
231                    if (_vendorSybase) {
232                            return StringUtil.replace(sql, "CROSS JOIN", StringPool.COMMA);
233                    }
234    
235                    return sql;
236            }
237    
238            private String _replaceEscape(String sql) {
239                    return StringUtil.replace(sql, "LIKE ?", "LIKE ? ESCAPE '\\'");
240            }
241    
242            private String _replaceIntegerDivision(String sql) {
243                    Matcher matcher = _integerDivisionPattern.matcher(sql);
244    
245                    if (_vendorMySQL) {
246                            return matcher.replaceAll("$1 DIV $2");
247                    }
248                    else if (_vendorOracle) {
249                            return matcher.replaceAll("TRUNC($1 / $2)");
250                    }
251                    else {
252                            return matcher.replaceAll("$1 / $2");
253                    }
254            }
255    
256            private String _replaceLike(String sql) {
257                    Matcher matcher = _likePattern.matcher(sql);
258    
259                    return matcher.replaceAll(
260                            "LIKE COALESCE(CAST(? AS VARCHAR(32672)),'')");
261            }
262    
263            private String _replaceMod(String sql) {
264                    Matcher matcher = _modPattern.matcher(sql);
265    
266                    return matcher.replaceAll("$1 % $2");
267            }
268    
269            private String _replaceNegativeComparison(String sql) {
270                    Matcher matcher = _negativeComparisonPattern.matcher(sql);
271    
272                    return matcher.replaceAll("$1 ($2)");
273            }
274    
275            private String _replaceNotEqualsBlankStringComparison(String sql) {
276                    return StringUtil.replace(sql, " != ''", " IS NOT NULL");
277            }
278    
279            private String _replaceReplace(String newSQL) {
280                    return newSQL.replaceAll("(?i)replace\\(", "str_replace(");
281            }
282    
283            private String _replaceUnion(String sql) {
284                    Matcher matcher = _unionAllPattern.matcher(sql);
285    
286                    return matcher.replaceAll("$1 $2");
287            }
288    
289            private String _transform(String sql) {
290                    if (sql == null) {
291                            return sql;
292                    }
293    
294                    String newSQL = sql;
295    
296                    newSQL = _replaceBitwiseCheck(newSQL);
297                    newSQL = _replaceBoolean(newSQL);
298                    newSQL = _replaceCastLong(newSQL);
299                    newSQL = _replaceCastText(newSQL);
300                    newSQL = _replaceCrossJoin(newSQL);
301                    newSQL = _replaceIntegerDivision(newSQL);
302    
303                    if (_vendorDB2) {
304                            newSQL = _replaceLike(newSQL);
305                    }
306                    else if (_vendorDerby) {
307                            newSQL = _replaceUnion(newSQL);
308                    }
309                    else if (_vendorMySQL) {
310                            DB db = DBFactoryUtil.getDB();
311    
312                            if (!db.isSupportsStringCaseSensitiveQuery()) {
313                                    newSQL = _removeLower(newSQL);
314                            }
315                    }
316                    else if (_vendorOracle) {
317                            newSQL = _replaceEscape(newSQL);
318                            newSQL = _replaceNotEqualsBlankStringComparison(newSQL);
319                    }
320                    else if (_vendorPostgreSQL) {
321                            newSQL = _replaceNegativeComparison(newSQL);
322                    }
323                    else if (_vendorSQLServer) {
324                            newSQL = _replaceMod(newSQL);
325                    }
326                    else if (_vendorSybase) {
327                            newSQL = _replaceMod(newSQL);
328                            newSQL = _replaceReplace(newSQL);
329                    }
330    
331                    if (_log.isDebugEnabled()) {
332                            _log.debug("Original SQL " + sql);
333                            _log.debug("Modified SQL " + newSQL);
334                    }
335    
336                    return newSQL;
337            }
338    
339            private String _transformFromHqlToJpql(String sql) {
340                    String newSQL = _transformedSqls.get(sql);
341    
342                    if (newSQL != null) {
343                            return newSQL;
344                    }
345    
346                    newSQL = _transform(sql);
347    
348                    newSQL = _transformPositionalParams(newSQL);
349    
350                    newSQL = StringUtil.replace(newSQL, _HQL_NOT_EQUALS, _JPQL_NOT_EQUALS);
351                    newSQL = StringUtil.replace(
352                            newSQL, _HQL_COMPOSITE_ID_MARKER, _JPQL_DOT_SEPARTOR);
353    
354                    _transformedSqls.put(sql, newSQL);
355    
356                    return newSQL;
357            }
358    
359            private String _transformFromJpqlToHql(String sql) {
360                    String newSQL = _transformedSqls.get(sql);
361    
362                    if (newSQL != null) {
363                            return newSQL;
364                    }
365    
366                    newSQL = _transform(sql);
367    
368                    Matcher matcher = _jpqlCountPattern.matcher(newSQL);
369    
370                    if (matcher.find()) {
371                            String countExpression = matcher.group(1);
372                            String entityAlias = matcher.group(3);
373    
374                            if (entityAlias.equals(countExpression)) {
375                                    newSQL = matcher.replaceFirst(_HQL_COUNT_SQL);
376                            }
377                    }
378    
379                    _transformedSqls.put(sql, newSQL);
380    
381                    return newSQL;
382            }
383    
384            private String _transformPositionalParams(String queryString) {
385                    if (queryString.indexOf(CharPool.QUESTION) == -1) {
386                            return queryString;
387                    }
388    
389                    StringBundler sb = new StringBundler();
390    
391                    int i = 1;
392                    int from = 0;
393                    int to = 0;
394    
395                    while ((to = queryString.indexOf(CharPool.QUESTION, from)) != -1) {
396                            sb.append(queryString.substring(from, to));
397                            sb.append(StringPool.QUESTION);
398                            sb.append(i++);
399    
400                            from = to + 1;
401                    }
402    
403                    sb.append(queryString.substring(from));
404    
405                    return sb.toString();
406            }
407    
408            private static final String _HQL_COMPOSITE_ID_MARKER = "\\.id\\.";
409    
410            private static final String _HQL_COUNT_SQL = "SELECT COUNT(*) FROM $2 $3";
411    
412            private static final String _HQL_NOT_EQUALS = "!=";
413    
414            private static final String _JPQL_DOT_SEPARTOR = ".";
415    
416            private static final String _JPQL_NOT_EQUALS = "<>";
417    
418            private static final String _LOWER_CLOSE = StringPool.CLOSE_PARENTHESIS;
419    
420            private static final String _LOWER_OPEN = "lower(";
421    
422            private static Log _log = LogFactoryUtil.getLog(SQLTransformer.class);
423    
424            private static SQLTransformer _instance = new SQLTransformer();
425    
426            private static Pattern _bitwiseCheckPattern = Pattern.compile(
427                    "BITAND\\((.+?),(.+?)\\)");
428            private static Pattern _castLongPattern = Pattern.compile(
429                    "CAST_LONG\\((.+?)\\)", Pattern.CASE_INSENSITIVE);
430            private static Pattern _castTextPattern = Pattern.compile(
431                    "CAST_TEXT\\((.+?)\\)", Pattern.CASE_INSENSITIVE);
432            private static Pattern _integerDivisionPattern = Pattern.compile(
433                    "INTEGER_DIV\\((.+?),(.+?)\\)", Pattern.CASE_INSENSITIVE);
434            private static Pattern _jpqlCountPattern = Pattern.compile(
435                    "SELECT COUNT\\((\\S+)\\) FROM (\\S+) (\\S+)");
436            private static Pattern _likePattern = Pattern.compile(
437                    "LIKE \\?", Pattern.CASE_INSENSITIVE);
438            private static Pattern _modPattern = Pattern.compile(
439                    "MOD\\((.+?),(.+?)\\)", Pattern.CASE_INSENSITIVE);
440            private static Pattern _negativeComparisonPattern = Pattern.compile(
441                    "(!?=)( -([0-9]+)?)", Pattern.CASE_INSENSITIVE);
442            private static Pattern _unionAllPattern = Pattern.compile(
443                    "SELECT \\* FROM(.*)TEMP_TABLE(.*)", Pattern.CASE_INSENSITIVE);
444    
445            private DB _db;
446            private Map<String, String> _transformedSqls;
447            private boolean _vendorDB2;
448            private boolean _vendorDerby;
449            private boolean _vendorFirebird;
450            private boolean _vendorHypersonic;
451            private boolean _vendorInformix;
452            private boolean _vendorIngres;
453            private boolean _vendorInterbase;
454            private boolean _vendorMySQL;
455            private boolean _vendorOracle;
456            private boolean _vendorPostgreSQL;
457            private boolean _vendorSQLServer;
458            private boolean _vendorSybase;
459    
460    }