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