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 _replaceCastLong(String sql) {
192 Matcher matcher = _castLongPattern.matcher(sql);
193
194 if (_vendorHypersonic) {
195 return matcher.replaceAll("CONVERT($1, SQL_BIGINT)");
196 }
197 else if (_vendorSybase) {
198 return matcher.replaceAll("CONVERT(BIGINT, $1)");
199 }
200 else {
201 return matcher.replaceAll("$1");
202 }
203 }
204
205 private String _replaceCastText(String sql) {
206 Matcher matcher = _castTextPattern.matcher(sql);
207
208 if (_vendorDB2 || _vendorDerby) {
209 return matcher.replaceAll("CAST($1 AS CHAR(254))");
210 }
211 else if (_vendorHypersonic) {
212 return matcher.replaceAll("CONVERT($1, SQL_VARCHAR)");
213 }
214 else if (_vendorMySQL) {
215 return matcher.replaceAll("CAST($1 AS CHAR)");
216 }
217 else if (_vendorOracle) {
218 return matcher.replaceAll("CAST($1 AS VARCHAR(4000))");
219 }
220 else if (_vendorPostgreSQL) {
221 return matcher.replaceAll("CAST($1 AS TEXT)");
222 }
223 else if (_vendorSQLServer) {
224 return matcher.replaceAll("CAST($1 AS NVARCHAR(MAX))");
225 }
226 else if (_vendorSybase) {
227 return matcher.replaceAll("CAST($1 AS NVARCHAR(5461))");
228 }
229 else {
230 return matcher.replaceAll("$1");
231 }
232 }
233
234 private String _replaceCrossJoin(String sql) {
235 if (_vendorSybase) {
236 return StringUtil.replace(sql, "CROSS JOIN", StringPool.COMMA);
237 }
238
239 return sql;
240 }
241
242 private String _replaceEscape(String sql) {
243 return StringUtil.replace(sql, "LIKE ?", "LIKE ? ESCAPE '\\'");
244 }
245
246 private String _replaceIntegerDivision(String sql) {
247 Matcher matcher = _integerDivisionPattern.matcher(sql);
248
249 if (_vendorMySQL) {
250 return matcher.replaceAll("$1 DIV $2");
251 }
252 else if (_vendorOracle) {
253 return matcher.replaceAll("TRUNC($1 / $2)");
254 }
255 else {
256 return matcher.replaceAll("$1 / $2");
257 }
258 }
259
260 private String _replaceLike(String sql) {
261 Matcher matcher = _likePattern.matcher(sql);
262
263 return matcher.replaceAll(
264 "LIKE COALESCE(CAST(? AS VARCHAR(32672)),'')");
265 }
266
267 private String _replaceMod(String sql) {
268 Matcher matcher = _modPattern.matcher(sql);
269
270 return matcher.replaceAll("$1 % $2");
271 }
272
273 private String _replaceNegativeComparison(String sql) {
274 Matcher matcher = _negativeComparisonPattern.matcher(sql);
275
276 return matcher.replaceAll("$1 ($2)");
277 }
278
279 private String _replaceNotEqualsBlankStringComparison(String sql) {
280 return StringUtil.replace(sql, " != ''", " IS NOT NULL");
281 }
282
283 private String _replaceReplace(String newSQL) {
284 return newSQL.replaceAll("(?i)replace\\(", "str_replace(");
285 }
286
287 private String _replaceUnion(String sql) {
288 Matcher matcher = _unionAllPattern.matcher(sql);
289
290 return matcher.replaceAll("$1 $2");
291 }
292
293 private String _transform(String sql) {
294 if (sql == null) {
295 return sql;
296 }
297
298 String newSQL = sql;
299
300 newSQL = _replaceBitwiseCheck(newSQL);
301 newSQL = _replaceBoolean(newSQL);
302 newSQL = _replaceCastLong(newSQL);
303 newSQL = _replaceCastText(newSQL);
304 newSQL = _replaceCrossJoin(newSQL);
305 newSQL = _replaceIntegerDivision(newSQL);
306
307 if (_vendorDB2) {
308 newSQL = _replaceLike(newSQL);
309 }
310 else if (_vendorDerby) {
311 newSQL = _replaceUnion(newSQL);
312 }
313 else if (_vendorMySQL) {
314 DB db = DBFactoryUtil.getDB();
315
316 if (!db.isSupportsStringCaseSensitiveQuery()) {
317 newSQL = _removeLower(newSQL);
318 }
319 }
320 else if (_vendorOracle) {
321 newSQL = _replaceEscape(newSQL);
322 newSQL = _replaceNotEqualsBlankStringComparison(newSQL);
323 }
324 else if (_vendorPostgreSQL) {
325 newSQL = _replaceNegativeComparison(newSQL);
326 }
327 else if (_vendorSQLServer) {
328 newSQL = _replaceMod(newSQL);
329 }
330 else if (_vendorSybase) {
331 newSQL = _replaceMod(newSQL);
332 newSQL = _replaceReplace(newSQL);
333 }
334
335 if (_log.isDebugEnabled()) {
336 _log.debug("Original SQL " + sql);
337 _log.debug("Modified SQL " + newSQL);
338 }
339
340 return newSQL;
341 }
342
343 private String _transformFromHqlToJpql(String sql) {
344 String newSQL = _transformedSqls.get(sql);
345
346 if (newSQL != null) {
347 return newSQL;
348 }
349
350 newSQL = _transform(sql);
351
352 newSQL = _transformPositionalParams(newSQL);
353
354 newSQL = StringUtil.replace(newSQL, _HQL_NOT_EQUALS, _JPQL_NOT_EQUALS);
355 newSQL = StringUtil.replace(
356 newSQL, _HQL_COMPOSITE_ID_MARKER, _JPQL_DOT_SEPARTOR);
357
358 _transformedSqls.put(sql, newSQL);
359
360 return newSQL;
361 }
362
363 private String _transformFromJpqlToHql(String sql) {
364 String newSQL = _transformedSqls.get(sql);
365
366 if (newSQL != null) {
367 return newSQL;
368 }
369
370 newSQL = _transform(sql);
371
372 Matcher matcher = _jpqlCountPattern.matcher(newSQL);
373
374 if (matcher.find()) {
375 String countExpression = matcher.group(1);
376 String entityAlias = matcher.group(3);
377
378 if (entityAlias.equals(countExpression)) {
379 newSQL = matcher.replaceFirst(_HQL_COUNT_SQL);
380 }
381 }
382
383 _transformedSqls.put(sql, newSQL);
384
385 return newSQL;
386 }
387
388 private String _transformPositionalParams(String queryString) {
389 if (queryString.indexOf(CharPool.QUESTION) == -1) {
390 return queryString;
391 }
392
393 StringBundler sb = new StringBundler();
394
395 int i = 1;
396 int from = 0;
397 int to = 0;
398
399 while ((to = queryString.indexOf(CharPool.QUESTION, from)) != -1) {
400 sb.append(queryString.substring(from, to));
401 sb.append(StringPool.QUESTION);
402 sb.append(i++);
403
404 from = to + 1;
405 }
406
407 sb.append(queryString.substring(from));
408
409 return sb.toString();
410 }
411
412 private static final String _HQL_COMPOSITE_ID_MARKER = "\\.id\\.";
413
414 private static final String _HQL_COUNT_SQL = "SELECT COUNT(*) FROM $2 $3";
415
416 private static final String _HQL_NOT_EQUALS = "!=";
417
418 private static final String _JPQL_DOT_SEPARTOR = ".";
419
420 private static final String _JPQL_NOT_EQUALS = "<>";
421
422 private static final String _LOWER_CLOSE = StringPool.CLOSE_PARENTHESIS;
423
424 private static final String _LOWER_OPEN = "lower(";
425
426 private static Log _log = LogFactoryUtil.getLog(SQLTransformer.class);
427
428 private static SQLTransformer _instance = new SQLTransformer();
429
430 private static Pattern _bitwiseCheckPattern = Pattern.compile(
431 "BITAND\\((.+?),(.+?)\\)");
432 private static Pattern _castLongPattern = Pattern.compile(
433 "CAST_LONG\\((.+?)\\)", Pattern.CASE_INSENSITIVE);
434 private static Pattern _castTextPattern = Pattern.compile(
435 "CAST_TEXT\\((.+?)\\)", Pattern.CASE_INSENSITIVE);
436 private static Pattern _integerDivisionPattern = Pattern.compile(
437 "INTEGER_DIV\\((.+?),(.+?)\\)", Pattern.CASE_INSENSITIVE);
438 private static Pattern _jpqlCountPattern = Pattern.compile(
439 "SELECT COUNT\\((\\S+)\\) FROM (\\S+) (\\S+)");
440 private static Pattern _likePattern = Pattern.compile(
441 "LIKE \\?", Pattern.CASE_INSENSITIVE);
442 private static Pattern _modPattern = Pattern.compile(
443 "MOD\\((.+?),(.+?)\\)", Pattern.CASE_INSENSITIVE);
444 private static Pattern _negativeComparisonPattern = Pattern.compile(
445 "(!?=)( -([0-9]+)?)", Pattern.CASE_INSENSITIVE);
446 private static Pattern _unionAllPattern = Pattern.compile(
447 "SELECT \\* FROM(.*)TEMP_TABLE(.*)", Pattern.CASE_INSENSITIVE);
448
449 private DB _db;
450 private Map<String, String> _transformedSqls;
451 private boolean _vendorDB2;
452 private boolean _vendorDerby;
453 private boolean _vendorFirebird;
454 private boolean _vendorHypersonic;
455 private boolean _vendorInformix;
456 private boolean _vendorIngres;
457 private boolean _vendorInterbase;
458 private boolean _vendorMySQL;
459 private boolean _vendorOracle;
460 private boolean _vendorPostgreSQL;
461 private boolean _vendorSQLServer;
462 private boolean _vendorSybase;
463
464 }