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.StringPool;
022
023 import java.util.regex.Matcher;
024 import java.util.regex.Pattern;
025
026
029 public class SQLTransformer {
030
031 public static String transform(String sql) {
032 return _instance._transform(sql);
033 }
034
035 private SQLTransformer() {
036 DB db = DBFactoryUtil.getDB();
037
038 if (db.getType().equals(DB.TYPE_MYSQL)) {
039 _vendorMySQL = true;
040 }
041 else if (db.getType().equals(DB.TYPE_ORACLE)) {
042 _vendorOracle = true;
043 }
044 else if (db.getType().equals(DB.TYPE_POSTGRESQL)) {
045 _vendorPostgreSQL = true;
046 }
047 else if (db.getType().equals(DB.TYPE_SQLSERVER)) {
048 _vendorSQLServer = true;
049 }
050 }
051
052 private String _removeLower(String sql) {
053 int x = sql.indexOf(_LOWER_OPEN);
054
055 if (x == -1) {
056 return sql;
057 }
058
059 StringBuilder sb = new StringBuilder(sql.length());
060
061 int y = 0;
062
063 while (true) {
064 sb.append(sql.substring(y, x));
065
066 y = sql.indexOf(_LOWER_CLOSE, x);
067
068 if (y == -1) {
069 sb.append(sql.substring(x));
070
071 break;
072 }
073
074 sb.append(sql.substring(x + _LOWER_OPEN.length(), y));
075
076 y++;
077
078 x = sql.indexOf(_LOWER_OPEN, y);
079
080 if (x == -1) {
081 sb.append(sql.substring(y));
082
083 break;
084 }
085 }
086
087 sql = sb.toString();
088
089 return sql;
090 }
091
092 private String _replaceCastText(String sql) {
093 Matcher matcher = _castTextPattern.matcher(sql);
094
095 if (_vendorPostgreSQL) {
096 return matcher.replaceAll("CAST($1 AS TEXT)");
097 }
098 else if (_vendorSQLServer) {
099 return matcher.replaceAll("CAST($1 AS NVARCHAR)");
100 }
101 else {
102 return matcher.replaceAll("$1");
103 }
104 }
105
106 private String _replaceMod(String sql) {
107 Matcher matcher = _modPattern.matcher(sql);
108
109 return matcher.replaceAll("$1 % $2");
110 }
111
112 private String _transform(String sql) {
113 if (sql == null) {
114 return sql;
115 }
116
117 String newSQL = sql;
118
119 if (_vendorMySQL) {
120 DB db = DBFactoryUtil.getDB();
121
122 if (!db.isSupportsStringCaseSensitiveQuery()) {
123 newSQL = _removeLower(newSQL);
124 }
125 }
126 else if (_vendorOracle) {
127 newSQL = _replaceMod(newSQL);
128 }
129 else if (_vendorSQLServer) {
130 newSQL = _replaceMod(newSQL);
131 }
132
133 newSQL = _replaceCastText(newSQL);
134
135 if (_log.isDebugEnabled()) {
136 _log.debug("Original SQL " + sql);
137 _log.debug("Modified SQL " + newSQL);
138 }
139
140 return newSQL;
141 }
142
143 private static final String _LOWER_CLOSE = StringPool.CLOSE_PARENTHESIS;
144
145 private static final String _LOWER_OPEN = "lower(";
146
147 private static Log _log = LogFactoryUtil.getLog(SQLTransformer.class);
148
149 private static SQLTransformer _instance = new SQLTransformer();
150
151 private static Pattern _castTextPattern = Pattern.compile(
152 "CAST_TEXT\\((.+?)\\)", Pattern.CASE_INSENSITIVE);
153 private static Pattern _modPattern = Pattern.compile(
154 "MOD\\((.+?),(.+?)\\)", Pattern.CASE_INSENSITIVE);
155
156 private boolean _vendorMySQL;
157 private boolean _vendorOracle;
158 private boolean _vendorPostgreSQL;
159 private boolean _vendorSQLServer;
160
161 }