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