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_DERBY)) {
039 _vendorDerby = true;
040 }
041 else if (db.getType().equals(DB.TYPE_MYSQL)) {
042 _vendorMySQL = 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 else if (db.getType().equals(DB.TYPE_SYBASE)) {
051 _vendorSybase = true;
052 }
053 }
054
055 private String _removeLower(String sql) {
056 int x = sql.indexOf(_LOWER_OPEN);
057
058 if (x == -1) {
059 return sql;
060 }
061
062 StringBuilder sb = new StringBuilder(sql.length());
063
064 int y = 0;
065
066 while (true) {
067 sb.append(sql.substring(y, x));
068
069 y = sql.indexOf(_LOWER_CLOSE, x);
070
071 if (y == -1) {
072 sb.append(sql.substring(x));
073
074 break;
075 }
076
077 sb.append(sql.substring(x + _LOWER_OPEN.length(), y));
078
079 y++;
080
081 x = sql.indexOf(_LOWER_OPEN, y);
082
083 if (x == -1) {
084 sb.append(sql.substring(y));
085
086 break;
087 }
088 }
089
090 sql = sb.toString();
091
092 return sql;
093 }
094
095 private String _replaceCastText(String sql) {
096 Matcher matcher = _castTextPattern.matcher(sql);
097
098 if (_vendorDerby) {
099 return matcher.replaceAll("CAST($1 AS CHAR(254))");
100 }
101 else if (_vendorPostgreSQL) {
102 return matcher.replaceAll("CAST($1 AS TEXT)");
103 }
104 else if (_vendorSQLServer || _vendorSybase) {
105 return matcher.replaceAll("CAST($1 AS NVARCHAR)");
106 }
107 else {
108 return matcher.replaceAll("$1");
109 }
110 }
111
112 private String _replaceMod(String sql) {
113 Matcher matcher = _modPattern.matcher(sql);
114
115 return matcher.replaceAll("$1 % $2");
116 }
117
118 private String _replaceUnion(String sql) {
119 Matcher matcher = _unionAllPattern.matcher(sql);
120
121 return matcher.replaceAll("$1 $2");
122 }
123
124 private String _transform(String sql) {
125 if (sql == null) {
126 return sql;
127 }
128
129 String newSQL = sql;
130
131 newSQL = _replaceCastText(newSQL);
132
133 if (_vendorDerby) {
134 newSQL = _replaceUnion(newSQL);
135 }
136 else if (_vendorMySQL) {
137 DB db = DBFactoryUtil.getDB();
138
139 if (!db.isSupportsStringCaseSensitiveQuery()) {
140 newSQL = _removeLower(newSQL);
141 }
142 }
143 else if (_vendorSQLServer || _vendorSybase) {
144 newSQL = _replaceMod(newSQL);
145 }
146
147 if (_log.isDebugEnabled()) {
148 _log.debug("Original SQL " + sql);
149 _log.debug("Modified SQL " + newSQL);
150 }
151
152 return newSQL;
153 }
154
155 private static final String _LOWER_CLOSE = StringPool.CLOSE_PARENTHESIS;
156
157 private static final String _LOWER_OPEN = "lower(";
158
159 private static Log _log = LogFactoryUtil.getLog(SQLTransformer.class);
160
161 private static SQLTransformer _instance = new SQLTransformer();
162
163 private static Pattern _castTextPattern = Pattern.compile(
164 "CAST_TEXT\\((.+?)\\)", Pattern.CASE_INSENSITIVE);
165 private static Pattern _modPattern = Pattern.compile(
166 "MOD\\((.+?),(.+?)\\)", Pattern.CASE_INSENSITIVE);
167 private static Pattern _unionAllPattern = Pattern.compile(
168 "SELECT \\* FROM(.*)TEMP_TABLE(.*)", Pattern.CASE_INSENSITIVE);
169
170 private boolean _vendorDerby;
171 private boolean _vendorMySQL;
172 private boolean _vendorPostgreSQL;
173 private boolean _vendorSQLServer;
174 private boolean _vendorSybase;
175
176 }