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.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
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 _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 final Log _log = LogFactoryUtil.getLog(SQLTransformer.class);
423
424 private static final SQLTransformer _instance = new SQLTransformer();
425
426 private static final Pattern _bitwiseCheckPattern = Pattern.compile(
427 "BITAND\\((.+?),(.+?)\\)");
428 private static final Pattern _castLongPattern = Pattern.compile(
429 "CAST_LONG\\((.+?)\\)", Pattern.CASE_INSENSITIVE);
430 private static final Pattern _castTextPattern = Pattern.compile(
431 "CAST_TEXT\\((.+?)\\)", Pattern.CASE_INSENSITIVE);
432 private static final Pattern _integerDivisionPattern = Pattern.compile(
433 "INTEGER_DIV\\((.+?),(.+?)\\)", Pattern.CASE_INSENSITIVE);
434 private static final Pattern _jpqlCountPattern = Pattern.compile(
435 "SELECT COUNT\\((\\S+)\\) FROM (\\S+) (\\S+)");
436 private static final Pattern _likePattern = Pattern.compile(
437 "LIKE \\?", Pattern.CASE_INSENSITIVE);
438 private static final Pattern _modPattern = Pattern.compile(
439 "MOD\\((.+?),(.+?)\\)", Pattern.CASE_INSENSITIVE);
440 private static final Pattern _negativeComparisonPattern = Pattern.compile(
441 "(!?=)( -([0-9]+)?)", Pattern.CASE_INSENSITIVE);
442 private static final 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 }