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