001
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
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 }