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