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