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 String transform(String sql) {
038 return _instance._transform(sql);
039 }
040
041 public static String transformFromHqlToJpql(String sql) {
042 return _instance._transformFromHqlToJpql(sql);
043 }
044
045 public static String transformFromJpqlToHql(String sql) {
046 return _instance._transformFromJpqlToHql(sql);
047 }
048
049 private SQLTransformer() {
050 DB db = DBFactoryUtil.getDB();
051
052 String dbType = db.getType();
053
054 _db = db;
055
056 if (dbType.equals(DB.TYPE_DB2)) {
057 _vendorDB2 = true;
058 }
059 else if (dbType.equals(DB.TYPE_DERBY)) {
060 _vendorDerby = true;
061 }
062 else if (dbType.equals(DB.TYPE_FIREBIRD)) {
063 _vendorFirebird = true;
064 }
065 else if (dbType.equals(DB.TYPE_HYPERSONIC)) {
066 _vendorHypersonic = true;
067 }
068 else if (dbType.equals(DB.TYPE_INFORMIX)) {
069 _vendorInformix = true;
070 }
071 else if (dbType.equals(DB.TYPE_INGRES)) {
072 _vendorIngres = true;
073 }
074 else if (dbType.equals(DB.TYPE_INTERBASE)) {
075 _vendorInterbase = true;
076 }
077 else if (dbType.equals(DB.TYPE_MYSQL)) {
078 _vendorMySQL = true;
079 }
080 else if (db.getType().equals(DB.TYPE_ORACLE)) {
081 _vendorOracle = true;
082 }
083 else if (dbType.equals(DB.TYPE_POSTGRESQL)) {
084 _vendorPostgreSQL = true;
085 }
086 else if (dbType.equals(DB.TYPE_SQLSERVER)) {
087 _vendorSQLServer = true;
088 }
089 else if (dbType.equals(DB.TYPE_SYBASE)) {
090 _vendorSybase = true;
091 }
092 }
093
094 private String _removeLower(String sql) {
095 int x = sql.indexOf(_LOWER_OPEN);
096
097 if (x == -1) {
098 return sql;
099 }
100
101 StringBuilder sb = new StringBuilder(sql.length());
102
103 int y = 0;
104
105 while (true) {
106 sb.append(sql.substring(y, x));
107
108 y = sql.indexOf(_LOWER_CLOSE, x);
109
110 if (y == -1) {
111 sb.append(sql.substring(x));
112
113 break;
114 }
115
116 sb.append(sql.substring(x + _LOWER_OPEN.length(), y));
117
118 y++;
119
120 x = sql.indexOf(_LOWER_OPEN, y);
121
122 if (x == -1) {
123 sb.append(sql.substring(y));
124
125 break;
126 }
127 }
128
129 sql = sb.toString();
130
131 return sql;
132 }
133
134 private String _replaceBitwiseCheck(String sql) {
135 Matcher matcher = _bitwiseCheckPattern.matcher(sql);
136
137 if (_vendorDB2 || _vendorHypersonic || _vendorOracle) {
138 return matcher.replaceAll("BITAND($1, $2)");
139 }
140 else if (_vendorDerby) {
141 return matcher.replaceAll("MOD($1 / $2, 2) != 0");
142 }
143 else if (_vendorInformix || _vendorIngres) {
144 return matcher.replaceAll("BIT_AND($1, $2)");
145 }
146 else if (_vendorFirebird || _vendorInterbase) {
147 return matcher.replaceAll("BIN_AND($1, $2)");
148 }
149 else {
150 return sql;
151 }
152 }
153
154 private String _replaceBoolean(String newSQL) {
155 return StringUtil.replace(
156 newSQL,
157 new String[] {"[$FALSE$]", "[$TRUE$]"},
158 new String[] {_db.getTemplateFalse(), _db.getTemplateTrue()});
159 }
160
161 private String _replaceCastText(String sql) {
162 Matcher matcher = _castTextPattern.matcher(sql);
163
164 if (_vendorDB2 || _vendorDerby) {
165 return matcher.replaceAll("CAST($1 AS CHAR(254))");
166 }
167 else if (_vendorPostgreSQL) {
168 return matcher.replaceAll("CAST($1 AS TEXT)");
169 }
170 else if (_vendorSQLServer) {
171 return matcher.replaceAll("CAST($1 AS NVARCHAR(MAX))");
172 }
173 else if (_vendorSybase) {
174 return matcher.replaceAll("CAST($1 AS NVARCHAR)");
175 }
176 else {
177 return matcher.replaceAll("$1");
178 }
179 }
180
181 private String _replaceIntegerDivision(String sql) {
182 Matcher matcher = _integerDivisionPattern.matcher(sql);
183
184 if (_vendorMySQL) {
185 return matcher.replaceAll("$1 DIV $2");
186 }
187 else if (_vendorOracle) {
188 return matcher.replaceAll("TRUNC($1 / $2)");
189 }
190 else {
191 return matcher.replaceAll("$1 / $2");
192 }
193 }
194
195 private String _replaceMod(String sql) {
196 Matcher matcher = _modPattern.matcher(sql);
197
198 return matcher.replaceAll("$1 % $2");
199 }
200
201 private String _replaceNegativeComparison(String sql) {
202 Matcher matcher = _negativeComparisonPattern.matcher(sql);
203
204 return matcher.replaceAll("$1 ($2)");
205 }
206
207 private String _replaceReplace(String newSQL) {
208 return StringUtil.replace(newSQL, "replace(", "str_replace(");
209 }
210
211 private String _replaceUnion(String sql) {
212 Matcher matcher = _unionAllPattern.matcher(sql);
213
214 return matcher.replaceAll("$1 $2");
215 }
216
217 private String _transform(String sql) {
218 if (sql == null) {
219 return sql;
220 }
221
222 String newSQL = sql;
223
224 newSQL = _replaceBitwiseCheck(newSQL);
225 newSQL = _replaceBoolean(newSQL);
226 newSQL = _replaceCastText(newSQL);
227 newSQL = _replaceIntegerDivision(newSQL);
228
229 if (_vendorDerby) {
230 newSQL = _replaceUnion(newSQL);
231 }
232 else if (_vendorMySQL) {
233 DB db = DBFactoryUtil.getDB();
234
235 if (!db.isSupportsStringCaseSensitiveQuery()) {
236 newSQL = _removeLower(newSQL);
237 }
238 }
239 else if (_vendorPostgreSQL) {
240 newSQL = _replaceNegativeComparison(newSQL);
241 }
242 else if (_vendorSQLServer) {
243 newSQL = _replaceMod(newSQL);
244 }
245 else if (_vendorSybase) {
246 newSQL = _replaceMod(newSQL);
247 newSQL = _replaceReplace(newSQL);
248 }
249
250 if (_log.isDebugEnabled()) {
251 _log.debug("Original SQL " + sql);
252 _log.debug("Modified SQL " + newSQL);
253 }
254
255 return newSQL;
256 }
257
258 private String _transformFromHqlToJpql(String sql) {
259 String newSQL = _transformedSqls.get(sql);
260
261 if (newSQL != null) {
262 return newSQL;
263 }
264
265 newSQL = _transform(sql);
266
267 newSQL = _transformPositionalParams(newSQL);
268
269 newSQL = StringUtil.replace(
270 newSQL, _HQL_NOT_EQUALS, _JPQL_NOT_EQUALS);
271 newSQL = StringUtil.replace(
272 newSQL, _HQL_COMPOSITE_ID_MARKER, _JPQL_DOT_SEPARTOR);
273
274 _transformedSqls.put(sql, newSQL);
275
276 return newSQL;
277 }
278
279 private String _transformFromJpqlToHql(String sql) {
280 String newSQL = _transformedSqls.get(sql);
281
282 if (newSQL != null) {
283 return newSQL;
284 }
285
286 newSQL = _transform(sql);
287
288 Matcher matcher = _jpqlCountPattern.matcher(newSQL);
289
290 if (matcher.find()) {
291 String countExpression = matcher.group(1);
292 String entityAlias = matcher.group(3);
293
294 if (entityAlias.equals(countExpression)) {
295 newSQL = matcher.replaceFirst(_HQL_COUNT_SQL);
296 }
297 }
298
299 _transformedSqls.put(sql, newSQL);
300
301 return newSQL;
302 }
303
304 private String _transformPositionalParams(String queryString) {
305 if (queryString.indexOf(CharPool.QUESTION) == -1) {
306 return queryString;
307 }
308
309 StringBundler sb = new StringBundler();
310
311 int i = 1;
312 int from = 0;
313 int to = 0;
314
315 while ((to = queryString.indexOf(CharPool.QUESTION, from)) != -1) {
316 sb.append(queryString.substring(from, to));
317 sb.append(StringPool.QUESTION);
318 sb.append(i++);
319
320 from = to + 1;
321 }
322
323 sb.append(queryString.substring(from, queryString.length()));
324
325 return sb.toString();
326 }
327
328 private static final String _HQL_COMPOSITE_ID_MARKER = "\\.id\\.";
329
330 private static final String _HQL_COUNT_SQL = "SELECT COUNT(*) FROM $2 $3";
331
332 private static final String _HQL_NOT_EQUALS = "!=";
333
334 private static final String _JPQL_DOT_SEPARTOR = ".";
335
336 private static final String _JPQL_NOT_EQUALS = "<>";
337
338 private static final String _LOWER_CLOSE = StringPool.CLOSE_PARENTHESIS;
339
340 private static final String _LOWER_OPEN = "lower(";
341
342 private static Log _log = LogFactoryUtil.getLog(SQLTransformer.class);
343
344 private static SQLTransformer _instance = new SQLTransformer();
345
346 private static Pattern _bitwiseCheckPattern = Pattern.compile(
347 "\\(\\((.+?) & (.+?)\\)\\)");
348 private static Pattern _castTextPattern = Pattern.compile(
349 "CAST_TEXT\\((.+?)\\)", Pattern.CASE_INSENSITIVE);
350 private static Pattern _integerDivisionPattern = Pattern.compile(
351 "INTEGER_DIV\\((.+?),(.+?)\\)", Pattern.CASE_INSENSITIVE);
352 private static Pattern _jpqlCountPattern = Pattern.compile(
353 "SELECT COUNT\\((\\S+)\\) FROM (\\S+) (\\S+)");
354 private static Pattern _modPattern = Pattern.compile(
355 "MOD\\((.+?),(.+?)\\)", Pattern.CASE_INSENSITIVE);
356 private static Pattern _negativeComparisonPattern = Pattern.compile(
357 "(!=)?( -([0-9]+)?)", Pattern.CASE_INSENSITIVE);
358 private static Map<String, String> _transformedSqls =
359 new ConcurrentHashMap<String, String>();
360 private static Pattern _unionAllPattern = Pattern.compile(
361 "SELECT \\* FROM(.*)TEMP_TABLE(.*)", Pattern.CASE_INSENSITIVE);
362
363 private DB _db;
364 private boolean _vendorDB2;
365 private boolean _vendorDerby;
366 private boolean _vendorFirebird;
367 private boolean _vendorHypersonic;
368 private boolean _vendorInformix;
369 private boolean _vendorIngres;
370 private boolean _vendorInterbase;
371 private boolean _vendorMySQL;
372 private boolean _vendorOracle;
373 private boolean _vendorPostgreSQL;
374 private boolean _vendorSQLServer;
375 private boolean _vendorSybase;
376
377 }