Source code

001/* 
002 * JKNIV, SQLegance keeping queries maintainable.
003 * 
004 * Copyright (C) 2017, the original author or authors.
005 *
006 * This library is free software; you can redistribute it and/or
007 * modify it under the terms of the GNU Lesser General Public
008 * License as published by the Free Software Foundation; either
009 * version 2.1 of the License.
010 * 
011 * This library is distributed in the hope that it will be useful,
012 * but WITHOUT ANY WARRANTY; without even the implied warranty of
013 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
014 * Lesser General Public License for more details.
015 * 
016 * You should have received a copy of the GNU Lesser General Public
017 * License along with this library; if not, write to the Free Software Foundation, Inc., 
018 * 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301  USA
019 */
020package net.sf.jkniv.sqlegance.dialect;
021
022import java.util.HashMap;
023import java.util.regex.Matcher;
024import java.util.regex.Pattern;
025
026import net.sf.jkniv.asserts.Assertable;
027import net.sf.jkniv.asserts.AssertsFactory;
028import net.sf.jkniv.reflect.beans.PropertyAccess;
029import net.sf.jkniv.sqlegance.params.ParamMarkType;
030
031/**
032 * Represents the support from SQL ANSI that are queries cross-platform.
033 *
034 * Represents a dialect of SQL implemented by a particular RDBMS. Subclasses implement Hibernate compatibility
035 * with different systems.  Subclasses should provide a public default constructor that register a set of type
036 * mappings and default Hibernate properties.  Subclasses should be immutable.
037 *
038 * <ul>
039 *  <li>Supports limits? false</li>
040 *  <li>Supports limit off set? false</li>
041 *  <li>Supports rownum? false</li>
042 * </ul>
043 * 
044 * @author Alisson Gomes
045 *
046 */
047public class AnsiDialect implements SqlDialect
048{
049    private static final Assertable                      notNull                     = AssertsFactory.getNotNull();
050    // find the pattern start with 'with'
051    private static final String                          REGEX_START_WITH            = "^\\s*(with)\\s";
052    // find the pattern start with 'select'
053    private static final String                          REGEX_START_SELECT          = "^\\s*(select)\\s";
054    
055    // find the pattern start with 'select distinct'
056    private static final String                          REGEX_START_SELECT_DISTINCT = "^\\s*(select\\s+distinct|select)\\s";
057    
058    // find the pattern end with'for update'
059    private static final String                          REGEX_ENDS_FORUPDATE        = "\\s+(for\\s+update)\\s*$";
060    
061    private static final String                          REGEX_ENDS_ORDERBY          = "\\s*(order\\s+by)\\s*[a-zA-Z0-9,_\\.\\)\\s]*$";
062    
063    private static final Pattern                         patternWith                 = Pattern.compile(REGEX_START_WITH,
064            Pattern.MULTILINE | Pattern.CASE_INSENSITIVE);
065    private static final Pattern                         patternSelect               = Pattern
066            .compile(REGEX_START_SELECT, Pattern.MULTILINE | Pattern.CASE_INSENSITIVE);
067    private static final Pattern                         patternSelectDistinct       = Pattern
068            .compile(REGEX_START_SELECT_DISTINCT, Pattern.MULTILINE | Pattern.CASE_INSENSITIVE);
069    private static final Pattern                         patternForUpdate            = Pattern
070            .compile(REGEX_ENDS_FORUPDATE, Pattern.MULTILINE | Pattern.CASE_INSENSITIVE);
071    public static final Pattern                          patternORDER_BY             = Pattern
072            .compile(REGEX_ENDS_ORDERBY, Pattern.MULTILINE | Pattern.CASE_INSENSITIVE);
073    
074    protected String                                     name;
075    private final HashMap<SqlFeatureSupport, SqlFeature> sqlFeatures;
076    private int                                          maxOfParameters;
077    private PropertyAccess                               propertyAccessId;
078    private PropertyAccess                               propertyAccessRevision;
079    
080    public AnsiDialect()
081    {
082        this.name = getClass().getSimpleName();
083        this.sqlFeatures = new HashMap<SqlFeatureSupport, SqlFeature>();
084        this.sqlFeatures.put(SqlFeatureSupport.LIMIT, 
085                             SqlFeatureFactory.newInstance(SqlFeatureSupport.LIMIT));
086        this.sqlFeatures.put(SqlFeatureSupport.LIMIT_OFF_SET,
087                             SqlFeatureFactory.newInstance(SqlFeatureSupport.LIMIT_OFF_SET));
088        this.sqlFeatures.put(SqlFeatureSupport.ROWNUM, 
089                             SqlFeatureFactory.newInstance(SqlFeatureSupport.ROWNUM));
090        this.sqlFeatures.put(SqlFeatureSupport.STMT_HOLDABILITY,
091                             SqlFeatureFactory.newInstance(SqlFeatureSupport.STMT_HOLDABILITY));
092        this.sqlFeatures.put(SqlFeatureSupport.CONN_HOLDABILITY,
093                             SqlFeatureFactory.newInstance(SqlFeatureSupport.CONN_HOLDABILITY, true));
094        this.sqlFeatures.put(SqlFeatureSupport.BOOKMARK_QUERY,
095                             SqlFeatureFactory.newInstance(SqlFeatureSupport.BOOKMARK_QUERY));
096        this.sqlFeatures.put(SqlFeatureSupport.PAGING_ROUNDTRIP,
097                             SqlFeatureFactory.newInstance(SqlFeatureSupport.PAGING_ROUNDTRIP, true));
098        this.sqlFeatures.put(SqlFeatureSupport.SEQUENCE,
099                             SqlFeatureFactory.newInstance(SqlFeatureSupport.SEQUENCE));
100        this.maxOfParameters = Integer.MAX_VALUE;
101        this.propertyAccessId = new PropertyAccess("id", "getId", "setId");
102        this.propertyAccessRevision = new PropertyAccess("rev", "getRev", "setRev");
103        //this.countParams = 0;
104    }
105    
106    public String name()
107    {
108        return this.name;
109    }
110    
111    @Override
112    public boolean supportsFeature(SqlFeatureSupport feature)
113    {
114        boolean answer = false;
115        SqlFeature sqlFeature = sqlFeatures.get(feature);
116        if (sqlFeature != null)
117            answer = sqlFeature.supports();
118        
119        return answer;
120    }
121    
122    @Override
123    public boolean supportsParmMark(ParamMarkType paramParse)
124    {
125        return true;
126    }
127    
128    @Override
129    public SqlFeature addFeature(SqlFeature sqlFeature)
130    {
131        return this.sqlFeatures.put(sqlFeature.getSqlFeature(), sqlFeature);
132    }
133    
134    @Override
135    public int getMaxOfParameters()
136    {
137        return maxOfParameters;
138    }
139    
140    @Override
141    public void setMaxOfParameters(int max)
142    {
143        if (max > 0)
144            this.maxOfParameters = max;
145    }
146    
147    @Override
148    public String getSqlPatternCount()
149    {
150        // using String.format argument index
151        return "select count(1) from (%1$s) jkniv_ct_tmp_table";
152    }
153    
154    @Override
155    public String getSqlPatternPaging()
156    {
157        StringBuilder pattern = new StringBuilder("%1$s");
158        if (supportsFeature(SqlFeatureSupport.LIMIT))
159        {
160            pattern.append(" LIMIT %2$s");
161            if (supportsFeature(SqlFeatureSupport.LIMIT_OFF_SET))
162                pattern.append(", %3$s");
163        }
164        return pattern.toString();//%1$s LIMIT %2$s, %3$s
165    }
166    
167    //protected void buildSqlLimits()
168    @Override
169    public String buildQueryPaging(final String sqlText, int offset, int max)
170    {
171        String sqlTextPaginated = null;
172        if (supportsFeature(SqlFeatureSupport.LIMIT))
173        {
174            String pagingSelectTemplate = getSqlPatternPaging();
175            //sqlText = queryable.getSql().getSql(queryable.getParams());
176            //assertSelect(this.sql);
177            Matcher matcher = sqlEndWithForUpdate(sqlText);
178            String forUpdate = "";
179            String sqlTextWithouForUpdate = sqlText;
180            if (matcher.find())
181            {
182                forUpdate = sqlText.substring(matcher.start(), matcher.end());// select name from author ^for update^
183                sqlTextWithouForUpdate = sqlText.substring(0, matcher.start());// ^select name from author^ for update
184            }
185            if (supportsFeature(SqlFeatureSupport.LIMIT_OFF_SET))
186                sqlTextPaginated = String.format(pagingSelectTemplate, sqlTextWithouForUpdate, max, offset) + forUpdate;
187            else
188                sqlTextPaginated = String.format(pagingSelectTemplate, sqlTextWithouForUpdate, max) + forUpdate;
189            
190        }
191        //replaceForQuestionMark();
192        return sqlTextPaginated;
193    }
194    
195    @Override
196    public String buildQueryPaging(final String sqlText, int offset, int max, String bookmark)
197    {
198        return buildQueryPaging(sqlText, offset, max);
199    }
200    
201    @Override
202    public PropertyAccess getAccessId()
203    {
204        return this.propertyAccessId;
205    }
206    
207    @Override
208    public PropertyAccess getAccessRevision()
209    {
210        return this.propertyAccessRevision;
211    }
212    
213    /*
214    protected void replaceForQuestionMark()
215    {
216        this.paramsNames = queryable.getSql().getParamParser().find(sql);
217        if(sqlWithLimit != null)
218            this.sqlWithLimit = queryable.getSql().getParamParser().replaceForQuestionMark(sqlWithLimit, queryable.getParams());
219        this.sql = queryable.getSql().getParamParser().replaceForQuestionMark(sql, queryable.getParams());
220        this.countParams = countOccurrencesOf(this.sql, "?");
221    }
222    */
223    
224    @Override
225    public String buildQueryCount(String sqlText)
226    {
227        String sqlToCount = null;
228        Matcher matcher = sqlEndWithForUpdate(sqlText);
229        if (matcher.find())
230            sqlToCount = String.format(getSqlPatternCount(), sqlText.substring(0, matcher.start()));// ^select name from author^ for update
231        else
232            sqlToCount = String.format(getSqlPatternCount(), removeOrderBy(sqlText));
233        
234        return sqlToCount;
235    }
236    
237    protected Matcher sqlStartWithSelect(String sql)
238    {
239        Matcher matcher = patternSelect.matcher(sql);
240        return matcher;
241    }
242    
243    protected Matcher sqlStartWithSelectOrDistinct(String sql)
244    {
245        Matcher matcher = patternSelectDistinct.matcher(sql);
246        return matcher;
247    }
248    
249    protected Matcher sqlEndsWithOrderBy(String sql)
250    {
251        Matcher matcher = patternORDER_BY.matcher(sql);
252        return matcher;
253    }
254    
255    protected Matcher sqlEndWithForUpdate(String sql)
256    {
257        Matcher matcher = patternForUpdate.matcher(sql);
258        return matcher;
259    }
260    
261    /**
262     * Remove the order by clause from the query.
263     * 
264     * @param hql
265     *            SQL, JPQL or HQL
266     * @return return the query without order by clause.
267     */
268    private String removeOrderBy(String hql)// TODO Single Responsibility
269    {
270        Matcher m = patternORDER_BY.matcher(hql);
271        StringBuffer sb = new StringBuffer();
272        while (m.find())
273        {
274            if (m.hitEnd())
275                m.appendReplacement(sb, "");
276        }
277        m.appendTail(sb);
278        return sb.toString();
279    }
280    //    protected boolean isSelect()
281    //    {
282    //        return queryable.getSql().isSelect();
283    //    }
284    
285    /** 
286     * Count the occurrences of the substring in string s.
287     * @param str string to search in. Return 0 if this is null.
288     * @param sub string to search for. Return 0 if this is null.
289     * @return number of occurrences from {@code str}
290     */
291    protected int countOccurrencesOf(String str, String sub)
292    {
293        if (str == null || sub == null || str.length() == 0 || sub.length() == 0)
294        {
295            return 0;
296        }
297        int count = 0;
298        int pos = 0;
299        int idx;
300        while ((idx = str.indexOf(sub, pos)) != -1)
301        {
302            ++count;
303            pos = idx + sub.length();
304        }
305        return count;
306    }
307    
308    /*
309    protected void assertSelect(String sql)
310    {
311        if (sqlStartWithSelectDistinct(sql).find() || sqlStartWithSelect(sql).find())
312            return;
313        else if (patternWith.matcher(sql).find())
314            throw new RepositoryException("JDBC repository doesn't support paginate query started with WITH clause");
315        
316        throw new RepositoryException(
317                "JDBC repository cannot paginate [DELETE | UPDATE | INSERT] queries, just SELECT");
318    }
319    
320    protected void assertSelect(ISql sql)
321    {
322        if (sql.getSqlCommandType() != SqlCommandType.SELECT)
323            throw new RepositoryException(
324                    "JDBC repository cannot paginate [DELETE | UPDATE | INSERT] queries, just SELECT");
325    }
326    */
327}