1   /**
2    * Copyright (c) 2000-2010 Liferay, Inc. All rights reserved.
3    *
4    * The contents of this file are subject to the terms of the Liferay Enterprise
5    * Subscription License ("License"). You may not use this file except in
6    * compliance with the License. You can obtain a copy of the License by
7    * contacting Liferay, Inc. See the License for the specific language governing
8    * permissions and limitations under the License, including but not limited to
9    * distribution rights of the Software.
10   *
11   *
12   *
13   */
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  /**
27   * <a href="SQLTransformer.java.html"><b><i>View Source</i></b></a>
28   *
29   * @author Brian Wing Shun Chan
30   */
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 }