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 * Dialect to DB2 with compatibility features for Oracle applications.
030 * <p>
031 * Limit clause:
032 *  <code>
033 *  SELECT TEXT FROM SESSION.SEARCHRESULTS
034     WHERE ROWNUM BETWEEN 20 AND 40
035     ORDER BY ID
036 *  </code>
037 * </p>
038 * <ul>
039 *  <li>Supports limits? true</li>
040 *  <li>Supports limit off set? true</li>
041 *  <li>Supports rownum? true</li>
042 * </ul>
043 * <p>
044 * The DB2_COMPATIBILITY_VECTOR registry variable enables one or more DB2® compatibility features. 
045 * These features ease the task of migrating applications that were written for relational database 
046 * products other than the DB2 product to DB2 Version 9.5 or later.
047 * </p>
048 * <p>
049 * This registry variable is supported on Linux, UNIX, and Windows operating systems.
050 * You can enable individual DB2 compatibility features by specify a hexadecimal value 
051 * for the registry variable. Each bit in the variable value enables a different feature. Values are as follows:
052 * </p>
053 * <pre>
054 *   NULL (default)
055 *   0000 - FFFF
056 *   ORA, to take full advantage of the DB2 compatibility features for Oracle applications
057 *   SYB, to take full advantage of the DB2 compatibility features for Sybase applications
058 *   MYS, to take full advantage of the DB2 compatibility features for MySQL applications
059 * </pre>
060 * 
061 * @see <a href="http://www.ibm.com/support/knowledgecenter/SSEPGG_10.1.0/com.ibm.db2.luw.apdv.porting.doc/doc/r0052867.html">DB2 compatibility Oracle/Sybase/MySQL</a>
062 *
063 * @author Alisson Gomes 
064 * @since 0.6.0
065 */
066public class DB2EnableORADialect extends AnsiDialect
067{
068    public DB2EnableORADialect()
069    {
070        super();
071        // Support LIMIT using rownum. Native clause not exists.
072        addFeature(SqlFeatureFactory.newInstance(SqlFeatureSupport.LIMIT, true));
073        addFeature(SqlFeatureFactory.newInstance(SqlFeatureSupport.LIMIT_OFF_SET, true));
074        //* To support rownum DB2_COMPATIBILITY_VECTOR must be enable.
075        //* <code>
076        //*  db2set DB2_COMPATIBILITY_VECTOR=ORA
077        //*  db2stop
078        //*  db2start
079        addFeature(SqlFeatureFactory.newInstance(SqlFeatureSupport.ROWNUM, true));
080    }
081    
082    /**
083     * Using rownum to paging the select, COMPATIBILITY_VECTOR=ORA must be enabled.
084     *  
085     *  @return Return query pattern: 
086     *  <code>
087     *   select * from (%1$s) where rownum between %3$s and %2$s
088     * </code>
089     */
090    @Override
091    public String getSqlPatternPaging()
092    {
093        return "select * from (%1$s) where ROWNUM BETWEEN %3$s AND %2$s";
094    }
095    
096    //@Override public void buildSqlLimits()
097    @Override
098    public String buildQueryPaging(final String sqlText, int offset, int max)
099    {
100        String sqlTextPaginated = null;
101        if (supportsFeature(SqlFeatureSupport.LIMIT))
102        {
103            String pagingSelect = getSqlPatternPaging();
104            //sqlText = queryable.getSql().getSql(queryable.getParams());
105            Matcher matcher = sqlEndWithForUpdate(sqlText);
106            String forUpdate = "";
107            String sqlWithoutForUpdate = sqlText;
108            if (matcher.find())
109            {
110                forUpdate = sqlText.substring(matcher.start(), matcher.end());// select name from author ^for update^
111                sqlWithoutForUpdate = sqlText.substring(0, matcher.start());// ^select name from author^ for update
112            }
113            sqlTextPaginated = String.format(pagingSelect, sqlWithoutForUpdate,
114                    max + offset, offset) + forUpdate;
115        }
116//        else
117//        {
118//            this.sql = queryable.getSql().getSql(queryable.getParams());
119//        }
120//        replaceForQuestionMark();
121        return sqlTextPaginated;
122    }
123}