1
14
15 package com.liferay.portal.dao.orm.common;
16
17 import com.liferay.portal.kernel.dao.db.DB;
18 import com.liferay.portal.kernel.dao.db.DBFactoryUtil;
19 import com.liferay.portal.kernel.log.Log;
20 import com.liferay.portal.kernel.log.LogFactoryUtil;
21 import com.liferay.portal.kernel.util.StringPool;
22
23 import java.util.regex.Matcher;
24 import java.util.regex.Pattern;
25
26
31 public class SQLTransformer {
32
33 public static String transform(String sql) {
34 return _instance._transform(sql);
35 }
36
37 private SQLTransformer() {
38 DB db = DBFactoryUtil.getDB();
39
40 if (db.getType().equals(DB.TYPE_DERBY)) {
41 _vendorDerby = true;
42 }
43 else if (db.getType().equals(DB.TYPE_MYSQL)) {
44 _vendorMySQL = true;
45 }
46 else if (db.getType().equals(DB.TYPE_POSTGRESQL)) {
47 _vendorPostgreSQL = true;
48 }
49 else if (db.getType().equals(DB.TYPE_SQLSERVER)) {
50 _vendorSQLServer = true;
51 }
52 else if (db.getType().equals(DB.TYPE_SYBASE)) {
53 _vendorSybase = true;
54 }
55 }
56
57 private String _removeLower(String sql) {
58 int x = sql.indexOf(_LOWER_OPEN);
59
60 if (x == -1) {
61 return sql;
62 }
63
64 StringBuilder sb = new StringBuilder(sql.length());
65
66 int y = 0;
67
68 while (true) {
69 sb.append(sql.substring(y, x));
70
71 y = sql.indexOf(_LOWER_CLOSE, x);
72
73 if (y == -1) {
74 sb.append(sql.substring(x));
75
76 break;
77 }
78
79 sb.append(sql.substring(x + _LOWER_OPEN.length(), y));
80
81 y++;
82
83 x = sql.indexOf(_LOWER_OPEN, y);
84
85 if (x == -1) {
86 sb.append(sql.substring(y));
87
88 break;
89 }
90 }
91
92 sql = sb.toString();
93
94 return sql;
95 }
96
97 private String _replaceCastText(String sql) {
98 Matcher matcher = _castTextPattern.matcher(sql);
99
100 if (_vendorDerby) {
101 return matcher.replaceAll("CAST($1 AS CHAR(254))");
102 }
103 else if (_vendorPostgreSQL) {
104 return matcher.replaceAll("CAST($1 AS TEXT)");
105 }
106 else if (_vendorSQLServer || _vendorSybase) {
107 return matcher.replaceAll("CAST($1 AS NVARCHAR)");
108 }
109 else {
110 return matcher.replaceAll("$1");
111 }
112 }
113
114 private String _replaceMod(String sql) {
115 Matcher matcher = _modPattern.matcher(sql);
116
117 return matcher.replaceAll("$1 % $2");
118 }
119
120 private String _replaceUnion(String sql) {
121 Matcher matcher = _unionAllPattern.matcher(sql);
122
123 return matcher.replaceAll("$1 $2");
124 }
125
126 private String _transform(String sql) {
127 if (sql == null) {
128 return sql;
129 }
130
131 String newSQL = sql;
132
133 newSQL = _replaceCastText(newSQL);
134
135 if (_vendorDerby) {
136 newSQL = _replaceUnion(newSQL);
137 }
138 else if (_vendorMySQL) {
139 DB db = DBFactoryUtil.getDB();
140
141 if (!db.isSupportsStringCaseSensitiveQuery()) {
142 newSQL = _removeLower(newSQL);
143 }
144 }
145 else if (_vendorSQLServer || _vendorSybase) {
146 newSQL = _replaceMod(newSQL);
147 }
148
149 if (_log.isDebugEnabled()) {
150 _log.debug("Original SQL " + sql);
151 _log.debug("Modified SQL " + newSQL);
152 }
153
154 return newSQL;
155 }
156
157 private static final String _LOWER_CLOSE = StringPool.CLOSE_PARENTHESIS;
158
159 private static final String _LOWER_OPEN = "lower(";
160
161 private static Log _log = LogFactoryUtil.getLog(SQLTransformer.class);
162
163 private static SQLTransformer _instance = new SQLTransformer();
164
165 private static Pattern _castTextPattern = Pattern.compile(
166 "CAST_TEXT\\((.+?)\\)", Pattern.CASE_INSENSITIVE);
167 private static Pattern _modPattern = Pattern.compile(
168 "MOD\\((.+?),(.+?)\\)", Pattern.CASE_INSENSITIVE);
169 private static Pattern _unionAllPattern = Pattern.compile(
170 "SELECT \\* FROM(.*)TEMP_TABLE(.*)", Pattern.CASE_INSENSITIVE);
171
172 private boolean _vendorDerby;
173 private boolean _vendorMySQL;
174 private boolean _vendorPostgreSQL;
175 private boolean _vendorSQLServer;
176 private boolean _vendorSybase;
177
178 }