001    /**
002     * Copyright (c) 2000-2010 Liferay, Inc. All rights reserved.
003     *
004     * This library is free software; you can redistribute it and/or modify it under
005     * the terms of the GNU Lesser General Public License as published by the Free
006     * Software Foundation; either version 2.1 of the License, or (at your option)
007     * any later version.
008     *
009     * This library is distributed in the hope that it will be useful, but WITHOUT
010     * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
011     * FOR A PARTICULAR PURPOSE. See the GNU Lesser General Public License for more
012     * details.
013     */
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    /**
027     * @author Brian Wing Shun Chan
028     */
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    }