001/*
002// This software is subject to the terms of the Eclipse Public License v1.0
003// Agreement, available at the following URL:
004// http://www.eclipse.org/legal/epl-v10.html.
005// You must accept the terms of that agreement to use this software.
006//
007// Copyright (C) 2008-2013 Pentaho
008// All Rights Reserved.
009*/
010package mondrian.spi.impl;
011
012import mondrian.rolap.SqlStatement;
013
014import java.sql.*;
015import java.util.List;
016import java.util.regex.*;
017
018/**
019 * Implementation of {@link mondrian.spi.Dialect} for the Oracle database.
020 *
021 * @author jhyde
022 * @since Nov 23, 2008
023 */
024public class OracleDialect extends JdbcDialectImpl {
025
026    private final String flagsRegexp = "^(\\(\\?([a-zA-Z])\\)).*$";
027    private final Pattern flagsPattern = Pattern.compile(flagsRegexp);
028    private final String escapeRegexp = "(\\\\Q([^\\\\Q]+)\\\\E)";
029    private final Pattern escapePattern = Pattern.compile(escapeRegexp);
030
031    public static final JdbcDialectFactory FACTORY =
032        new JdbcDialectFactory(
033            OracleDialect.class,
034            DatabaseProduct.ORACLE);
035
036    /**
037     * Creates an OracleDialect.
038     *
039     * @param connection Connection
040     */
041    public OracleDialect(Connection connection) throws SQLException {
042        super(connection);
043    }
044
045    public OracleDialect() {
046    }
047
048    public boolean allowsAs() {
049        return false;
050    }
051
052    public String generateInline(
053        List<String> columnNames,
054        List<String> columnTypes,
055        List<String[]> valueList)
056    {
057        return generateInlineGeneric(
058            columnNames, columnTypes, valueList,
059            " from dual", false);
060    }
061
062    public boolean supportsGroupingSets() {
063        return true;
064    }
065
066    @Override
067    public String generateOrderByNulls(
068        String expr,
069        boolean ascending,
070        boolean collateNullsLast)
071    {
072        return generateOrderByNullsAnsi(expr, ascending, collateNullsLast);
073    }
074
075    @Override
076    public boolean allowsJoinOn() {
077        return false;
078    }
079
080    @Override
081    public boolean allowsRegularExpressionInWhereClause() {
082        return true;
083    }
084
085    @Override
086    public String generateRegularExpression(
087        String source,
088        String javaRegex)
089    {
090        try {
091            Pattern.compile(javaRegex);
092        } catch (PatternSyntaxException e) {
093            // Not a valid Java regex. Too risky to continue.
094            return null;
095        }
096        final Matcher flagsMatcher = flagsPattern.matcher(javaRegex);
097        final String suffix;
098        if (flagsMatcher.matches()) {
099            // We need to convert leading flags into oracle
100            // specific flags
101            final StringBuilder suffixSb = new StringBuilder();
102            final String flags = flagsMatcher.group(2);
103            if (flags.contains("i")) {
104                suffixSb.append("i");
105            }
106            if (flags.contains("c")) {
107                suffixSb.append("c");
108            }
109            if (flags.contains("m")) {
110                suffixSb.append("m");
111            }
112            suffix = suffixSb.toString();
113            javaRegex =
114                javaRegex.substring(0, flagsMatcher.start(1))
115                + javaRegex.substring(flagsMatcher.end(1));
116        } else {
117            suffix = "";
118        }
119        final Matcher escapeMatcher = escapePattern.matcher(javaRegex);
120        while (escapeMatcher.find()) {
121            javaRegex =
122                javaRegex.replace(
123                    escapeMatcher.group(1),
124                    escapeMatcher.group(2));
125        }
126        final StringBuilder sb = new StringBuilder();
127        sb.append("REGEXP_LIKE(");
128        sb.append(source);
129        sb.append(", ");
130        quoteStringLiteral(sb, javaRegex);
131        sb.append(", ");
132        quoteStringLiteral(sb, suffix);
133        sb.append(")");
134        return sb.toString();
135    }
136
137    public void quoteDateLiteral(StringBuilder buf, String value) {
138        Date date;
139        try {
140              // The format of the 'value' parameter is not certain.
141              // Some JDBC drivers will return a timestamp even though
142              // we ask for a date (oracle is one of them). We must try to
143              // convert both formats.
144            date = Date.valueOf(value);
145        } catch (IllegalArgumentException ex) {
146            try {
147                date =
148                    new Date(Timestamp.valueOf(value).getTime());
149            } catch (IllegalArgumentException ex2) {
150                throw new NumberFormatException(
151                    "Illegal DATE literal:  " + value);
152            }
153        }
154        quoteDateLiteral(buf, value, date);
155    }
156
157    /**
158     * Chooses the most appropriate type for accessing the values of a
159     * column in a result set.
160     *
161     * The OracleDialect implementation handles some of the specific
162     * quirks of Oracle:  e.g. scale = -127 has special meaning with
163     * NUMERIC types and may indicate a FLOAT value if precision is non-zero.
164     *
165     * @param metaData  Resultset metadata
166     * @param columnIndex  index of the column in the result set
167     * @return  For Types.NUMERIC and Types.DECIMAL, getType()
168     * will return a Type.INT, Type.DOUBLE, or Type.OBJECT based on
169     * scale, precision, and column name.
170     *
171     * @throws SQLException
172     */
173    @Override
174    public SqlStatement.Type getType(
175        ResultSetMetaData metaData, int columnIndex)
176        throws SQLException
177    {
178        final int columnType = metaData.getColumnType(columnIndex + 1);
179        final int precision = metaData.getPrecision(columnIndex + 1);
180        final int scale = metaData.getScale(columnIndex + 1);
181        final String columnName = metaData.getColumnName(columnIndex + 1);
182        SqlStatement.Type type;
183
184        if (columnType == Types.NUMERIC || columnType == Types.DECIMAL) {
185            if (scale == -127 && precision != 0) {
186                // non zero precision w/ -127 scale means float in Oracle.
187                type = SqlStatement.Type.DOUBLE;
188            } else if (columnType == Types.NUMERIC
189                && (scale == 0 || scale == -127)
190                && precision == 0 && columnName.startsWith("m"))
191            {
192                // In GROUPING SETS queries, Oracle
193                // loosens the type of columns compared to mere GROUP BY
194                // queries. We need integer GROUP BY columns to remain integers,
195                // otherwise the segments won't be found; but if we convert
196                // measure (whose column names are like "m0", "m1") to integers,
197                // data loss will occur.
198                type = SqlStatement.Type.OBJECT;
199            } else if (scale == -127 && precision ==0) {
200                type = SqlStatement.Type.INT;
201            } else if (scale == 0 && (precision == 38 || precision == 0)) {
202                // NUMBER(38, 0) is conventionally used in
203                // Oracle for integers of unspecified precision, so let's be
204                // bold and assume that they can fit into an int.
205                type = SqlStatement.Type.INT;
206            } else if (scale == 0 && precision <= 9) {
207                // An int (up to 2^31 = 2.1B) can hold any NUMBER(10, 0) value
208                // (up to 10^9 = 1B).
209                type = SqlStatement.Type.INT;
210            } else {
211                type = SqlStatement.Type.DOUBLE;
212            }
213
214        } else {
215            type = super.getType(metaData, columnIndex);
216        }
217        logTypeInfo(metaData, columnIndex, type);
218        return type;
219    }
220}
221
222// End OracleDialect.java