001    /**
002     * Copyright (c) 2000-2013 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.hibernate;
016    
017    import com.liferay.portal.kernel.util.CharPool;
018    import com.liferay.portal.kernel.util.StringBundler;
019    import com.liferay.portal.kernel.util.StringPool;
020    import com.liferay.portal.kernel.util.StringUtil;
021    import com.liferay.portal.kernel.util.Validator;
022    
023    import java.util.regex.Matcher;
024    import java.util.regex.Pattern;
025    
026    /**
027     * @author Minhchau Dang
028     * @author Steven Cao
029     */
030    public class SQLServerLimitStringUtil {
031    
032            public static String getLimitString(String sql, int offset, int limit) {
033                    String sqlLowerCase = StringUtil.toLowerCase(sql);
034    
035                    int fromPos = sqlLowerCase.indexOf(" from ");
036    
037                    String selectFrom = sql.substring(0, fromPos);
038    
039                    int orderByPos = sqlLowerCase.lastIndexOf(" order by ");
040    
041                    String selectFromWhere = null;
042    
043                    String orderBy = StringPool.BLANK;
044    
045                    if (orderByPos > 0) {
046                            selectFromWhere = sql.substring(fromPos, orderByPos);
047    
048                            orderBy = sql.substring(orderByPos + 9);
049                    }
050                    else {
051                            selectFromWhere = sql.substring(fromPos);
052                    }
053    
054                    String[] splitOrderBy = _splitOrderBy(selectFrom, orderBy);
055    
056                    String innerOrderBy = splitOrderBy[0];
057                    String outerOrderBy = splitOrderBy[1];
058    
059                    String innerSelectFrom = _getInnerSelectFrom(
060                            selectFrom, innerOrderBy, limit);
061    
062                    StringBundler sb = new StringBundler(15);
063    
064                    sb.append("select * from (");
065                    sb.append("select *, row_number() over (");
066                    sb.append(outerOrderBy);
067                    sb.append(") as _page_row_num from (");
068                    sb.append(innerSelectFrom);
069                    sb.append(selectFromWhere);
070                    sb.append(innerOrderBy);
071                    sb.append(" ) _temp_table_1 ) _temp_table_2");
072                    sb.append(" where _page_row_num between ");
073                    sb.append(offset + 1);
074                    sb.append(" and ");
075                    sb.append(limit);
076                    sb.append(" order by _page_row_num");
077    
078                    return sb.toString();
079            }
080    
081            private static final String[] _splitOrderBy(
082                    String selectFrom, String orderBy) {
083    
084                    StringBundler innerOrderBySB = new StringBundler();
085                    StringBundler outerOrderBySB = new StringBundler();
086    
087                    String[] orderByColumns = StringUtil.split(orderBy, CharPool.COMMA);
088    
089                    for (String orderByColumn : orderByColumns) {
090                            orderByColumn = orderByColumn.trim();
091    
092                            String orderByColumnName = orderByColumn;
093                            String orderByType = "ASC";
094    
095                            int spacePos = orderByColumn.lastIndexOf(CharPool.SPACE);
096    
097                            if (spacePos != -1) {
098                                    int parenPos = orderByColumn.indexOf(
099                                            CharPool.OPEN_PARENTHESIS, spacePos);
100    
101                                    if (parenPos == -1) {
102                                            orderByColumnName = orderByColumn.substring(0, spacePos);
103                                            orderByType = orderByColumn.substring(spacePos + 1);
104                                    }
105                            }
106    
107                            String patternString = "\\Q".concat(orderByColumnName).concat(
108                                    "\\E as (\\w+)");
109    
110                            Pattern pattern = Pattern.compile(
111                                    patternString, Pattern.CASE_INSENSITIVE);
112    
113                            Matcher matcher = pattern.matcher(selectFrom);
114    
115                            if (matcher.find()) {
116                                    orderByColumnName = matcher.group(1);
117                            }
118    
119                            if (selectFrom.contains(orderByColumnName)) {
120                                    if (outerOrderBySB.length() == 0) {
121                                            outerOrderBySB.append(" order by ");
122                                    }
123                                    else {
124                                            outerOrderBySB.append(StringPool.COMMA);
125                                    }
126    
127                                    matcher = _qualifiedColumnPattern.matcher(orderByColumnName);
128    
129                                    orderByColumnName = matcher.replaceAll("$1");
130    
131                                    outerOrderBySB.append(orderByColumnName);
132                                    outerOrderBySB.append(StringPool.SPACE);
133                                    outerOrderBySB.append(orderByType);
134                            }
135                            else {
136                                    if (innerOrderBySB.length() == 0) {
137                                            innerOrderBySB.append(" order by ");
138                                    }
139                                    else {
140                                            innerOrderBySB.append(StringPool.COMMA);
141                                    }
142    
143                                    innerOrderBySB.append(orderByColumnName);
144                                    innerOrderBySB.append(StringPool.SPACE);
145                                    innerOrderBySB.append(orderByType);
146                            }
147                    }
148    
149                    if (outerOrderBySB.length() == 0) {
150                            outerOrderBySB.append(" order by CURRENT_TIMESTAMP");
151                    }
152    
153                    return new String[] {
154                            innerOrderBySB.toString(), outerOrderBySB.toString()
155                    };
156            }
157    
158            private static String _getInnerSelectFrom(
159                    String selectFrom, String innerOrderBy, int limit) {
160    
161                    String innerSelectFrom = selectFrom;
162    
163                    if (Validator.isNotNull(innerOrderBy)) {
164                            Matcher matcher = _selectPattern.matcher(innerSelectFrom);
165    
166                            innerSelectFrom = matcher.replaceAll(
167                                    "select top ".concat(String.valueOf(limit)).concat(
168                                            StringPool.SPACE));
169                    }
170    
171                    return innerSelectFrom;
172            }
173    
174            private static Pattern _qualifiedColumnPattern = Pattern.compile(
175                    "\\w+\\.([\\w\\*]+)");
176            private static Pattern _selectPattern = Pattern.compile(
177                    "SELECT ", Pattern.CASE_INSENSITIVE);
178    
179    }