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