001/* 
002 * JKNIV, whinstone one contract to access your database.
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.whinstone.jdbc.dialect;
021
022import java.util.regex.Matcher;
023
024import net.sf.jkniv.sqlegance.dialect.AnsiDialect;
025import net.sf.jkniv.sqlegance.dialect.SqlFeatureFactory;
026import net.sf.jkniv.sqlegance.dialect.SqlFeatureSupport;
027
028/**
029 * Default dialect do Oracle
030 * 
031 * <p>
032 * Limit clause:
033 *  <code>select name from author LIMIT 1 OFFSET 2</code>
034 *  <code>
035 *  select * from ( select row_.*, rownum rownum_ from 
036     (select name from author ) 
037     row_ where rownum &lt;= 5) where rownum_ &gt; 15
038 *  </code>
039 * </p>
040 * 
041 * <ul>
042 *  <li>Supports limits? true</li>
043 *  <li>Supports limit off set? true</li>
044 *  <li>Supports rownum? true</li>
045 * </ul>
046 *
047 * @author Alisson Gomes
048 * @since 0.6.0
049 */
050public class OracleDialect extends AnsiDialect
051{
052    public OracleDialect()
053    {
054        super();
055        addFeature(SqlFeatureFactory.newInstance(SqlFeatureSupport.LIMIT, true));
056        addFeature(SqlFeatureFactory.newInstance(SqlFeatureSupport.LIMIT_OFF_SET, true));
057        addFeature(SqlFeatureFactory.newInstance(SqlFeatureSupport.ROWNUM, true));
058    }
059        
060    /*
061     * Support LIMIT using rownum. Native clause not exists.
062     * @return supported by Oracle
063     *
064    @Override
065    public boolean supportsLimit()
066    {
067        return true;
068    }
069    
070    /**
071     * Support OFFSET using rownum. Native clause not exists.
072     * @return supported by Oracle
073     *
074    @Override
075    public boolean supportsLimitOffset()
076    {
077        return true;
078    }
079    
080    /**
081     * Supports ROWNUM at query statement
082     * @return supported by Oracle
083     *
084    @Override
085    public boolean supportsRownum()
086    {
087        return true;
088    }
089    */
090    
091    @Override
092    public int getMaxOfParameters()
093    {
094        return 1000;
095    }
096
097    /**
098     * @return  Return query pattern:
099     * <code>
100     *   select * from ( select row_.*, rownum rownum_ from 
101     *    (%1$s) 
102     *   row_ where rownum &lt;= %2$s) where rownum_ &gt; %3$s
103     * </code>
104     */
105    @Override
106    public String getSqlPatternPaging()
107    {
108        final StringBuilder pagingSelect = new StringBuilder(100);
109        pagingSelect.append("select * from ( select row_.*, rownum rownum_ from ( ");
110        pagingSelect.append(" %1$s ");
111        pagingSelect.append(" ) row_ where rownum <= %2$s) where rownum_ > %3$s");
112        return pagingSelect.toString();
113    }
114    
115    //@Override protected void buildSqlLimits()
116    @Override
117    public String buildQueryPaging(final String sqlText, int offset, int max)
118    {
119        String sqlTextPaginated = null;
120        if (supportsFeature(SqlFeatureSupport.LIMIT))
121        {
122            String pagingSelect = getSqlPatternPaging();
123            Matcher matcher = sqlEndWithForUpdate(sqlText);
124            String forUpdate = "";
125            String sqlTextWithouForUpdate = sqlText;
126            if (matcher.find())
127            {
128                forUpdate = sqlText.substring(matcher.start(), matcher.end());// select name from author ^for update^
129                sqlTextWithouForUpdate = sqlText.substring(0, matcher.start());// ^select name from author^ for update
130            }
131            sqlTextPaginated = String.format(pagingSelect, sqlTextWithouForUpdate, max + offset, offset) + forUpdate;
132        }
133//        else
134//        {
135//            this.sql = queryable.getSql().getSql(queryable.getParams());
136//        }
137//        replaceForQuestionMark();
138        return sqlTextPaginated;
139    }
140}