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 String dbType = db.getType();
039
040 if (dbType.equals(DB.TYPE_DB2)) {
041 _vendorDB2 = true;
042 }
043 else if (dbType.equals(DB.TYPE_DERBY)) {
044 _vendorDerby = true;
045 }
046 else if (dbType.equals(DB.TYPE_MYSQL)) {
047 _vendorMySQL = true;
048 }
049 else if (db.getType().equals(DB.TYPE_ORACLE)) {
050 _vendorOracle = true;
051 }
052 else if (dbType.equals(DB.TYPE_POSTGRESQL)) {
053 _vendorPostgreSQL = true;
054 }
055 else if (dbType.equals(DB.TYPE_SQLSERVER)) {
056 _vendorSQLServer = true;
057 }
058 else if (dbType.equals(DB.TYPE_SYBASE)) {
059 _vendorSybase = true;
060 }
061 }
062
063 private String _removeLower(String sql) {
064 int x = sql.indexOf(_LOWER_OPEN);
065
066 if (x == -1) {
067 return sql;
068 }
069
070 StringBuilder sb = new StringBuilder(sql.length());
071
072 int y = 0;
073
074 while (true) {
075 sb.append(sql.substring(y, x));
076
077 y = sql.indexOf(_LOWER_CLOSE, x);
078
079 if (y == -1) {
080 sb.append(sql.substring(x));
081
082 break;
083 }
084
085 sb.append(sql.substring(x + _LOWER_OPEN.length(), y));
086
087 y++;
088
089 x = sql.indexOf(_LOWER_OPEN, y);
090
091 if (x == -1) {
092 sb.append(sql.substring(y));
093
094 break;
095 }
096 }
097
098 sql = sb.toString();
099
100 return sql;
101 }
102
103 private String _replaceCastText(String sql) {
104 Matcher matcher = _castTextPattern.matcher(sql);
105
106 if (_vendorDB2) {
107 return matcher.replaceAll("CAST($1 AS VARCHAR(500))");
108 }
109 else if (_vendorDerby) {
110 return matcher.replaceAll("CAST($1 AS CHAR(254))");
111 }
112 else if (_vendorPostgreSQL) {
113 return matcher.replaceAll("CAST($1 AS TEXT)");
114 }
115 else if (_vendorSQLServer || _vendorSybase) {
116 return matcher.replaceAll("CAST($1 AS NVARCHAR)");
117 }
118 else {
119 return matcher.replaceAll("$1");
120 }
121 }
122
123 private String _replaceIntegerDivision(String sql) {
124 Matcher matcher = _integerDivisionPattern.matcher(sql);
125
126 if (_vendorMySQL) {
127 return matcher.replaceAll("$1 DIV $2");
128 }
129 else if (_vendorOracle) {
130 return matcher.replaceAll("TRUNC($1 / $2)");
131 }
132 else {
133 return matcher.replaceAll("$1 / $2");
134 }
135 }
136
137 private String _replaceMod(String sql) {
138 Matcher matcher = _modPattern.matcher(sql);
139
140 return matcher.replaceAll("$1 % $2");
141 }
142
143 private String _replaceNegativeComparison(String sql) {
144 Matcher matcher = _negativeComparisonPattern.matcher(sql);
145
146 return matcher.replaceAll("$1 ($2)");
147 }
148
149 private String _replaceUnion(String sql) {
150 Matcher matcher = _unionAllPattern.matcher(sql);
151
152 return matcher.replaceAll("$1 $2");
153 }
154
155 private String _transform(String sql) {
156 if (sql == null) {
157 return sql;
158 }
159
160 String newSQL = sql;
161
162 newSQL = _replaceCastText(newSQL);
163 newSQL = _replaceIntegerDivision(newSQL);
164
165 if (_vendorDerby) {
166 newSQL = _replaceUnion(newSQL);
167 }
168 else if (_vendorMySQL) {
169 DB db = DBFactoryUtil.getDB();
170
171 if (!db.isSupportsStringCaseSensitiveQuery()) {
172 newSQL = _removeLower(newSQL);
173 }
174 }
175 else if (_vendorPostgreSQL) {
176 newSQL = _replaceNegativeComparison(newSQL);
177 }
178 else if (_vendorSQLServer || _vendorSybase) {
179 newSQL = _replaceMod(newSQL);
180 }
181 if (_log.isDebugEnabled()) {
182 _log.debug("Original SQL " + sql);
183 _log.debug("Modified SQL " + newSQL);
184 }
185
186 return newSQL;
187 }
188
189 private static final String _LOWER_CLOSE = StringPool.CLOSE_PARENTHESIS;
190
191 private static final String _LOWER_OPEN = "lower(";
192
193 private static Log _log = LogFactoryUtil.getLog(SQLTransformer.class);
194
195 private static SQLTransformer _instance = new SQLTransformer();
196
197 private static Pattern _castTextPattern = Pattern.compile(
198 "CAST_TEXT\\((.+?)\\)", Pattern.CASE_INSENSITIVE);
199 private static Pattern _integerDivisionPattern = Pattern.compile(
200 "INTEGER_DIV\\((.+?),(.+?)\\)", Pattern.CASE_INSENSITIVE);
201 private static Pattern _modPattern = Pattern.compile(
202 "MOD\\((.+?),(.+?)\\)", Pattern.CASE_INSENSITIVE);
203 private static Pattern _negativeComparisonPattern = Pattern.compile(
204 "(!= )?(-([0-9]+)?)", Pattern.CASE_INSENSITIVE);
205 private static Pattern _unionAllPattern = Pattern.compile(
206 "SELECT \\* FROM(.*)TEMP_TABLE(.*)", Pattern.CASE_INSENSITIVE);
207
208 private boolean _vendorDB2;
209 private boolean _vendorDerby;
210 private boolean _vendorMySQL;
211 private boolean _vendorOracle;
212 private boolean _vendorPostgreSQL;
213 private boolean _vendorSQLServer;
214 private boolean _vendorSybase;
215
216 }