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