@Override public List<?> translate(Function function) { Expression ex = function.getParameters().get(0); if ((ex instanceof ColumnReference && "date" .equalsIgnoreCase( ((ColumnReference) ex).getMetadataObject().getNativeType())) // $NON-NLS-1$ || (!(ex instanceof ColumnReference) && !(ex instanceof Literal) && !(ex instanceof Function))) { ex = ConvertModifier.createConvertFunction( getLanguageFactory(), function.getParameters().get(0), TypeFacility.RUNTIME_NAMES.TIMESTAMP); function.getParameters().set(0, ex); } return super.translate(function); }
public void start() throws TranslatorException { super.start(); registerFunctionModifier(SourceSystemFunctions.CHAR, new AliasModifier("chr")); // $NON-NLS-1$ registerFunctionModifier( SourceSystemFunctions.LCASE, new AliasModifier("lower")); // $NON-NLS-1$ registerFunctionModifier( SourceSystemFunctions.UCASE, new AliasModifier("upper")); // $NON-NLS-1$ registerFunctionModifier(SourceSystemFunctions.IFNULL, new AliasModifier("nvl")); // $NON-NLS-1$ registerFunctionModifier(SourceSystemFunctions.LOG, new AliasModifier("ln")); // $NON-NLS-1$ registerFunctionModifier( SourceSystemFunctions.CEILING, new AliasModifier("ceil")); // $NON-NLS-1$ registerFunctionModifier( SourceSystemFunctions.LOG10, new Log10FunctionModifier(getLanguageFactory())); registerFunctionModifier(SourceSystemFunctions.HOUR, new DateAwareExtract()); registerFunctionModifier(SourceSystemFunctions.YEAR, new ExtractFunctionModifier()); registerFunctionModifier(SourceSystemFunctions.MINUTE, new DateAwareExtract()); registerFunctionModifier(SourceSystemFunctions.SECOND, new DateAwareExtract()); registerFunctionModifier(SourceSystemFunctions.MONTH, new ExtractFunctionModifier()); registerFunctionModifier(SourceSystemFunctions.DAYOFMONTH, new ExtractFunctionModifier()); registerFunctionModifier( SourceSystemFunctions.MONTHNAME, new MonthOrDayNameFunctionModifier(getLanguageFactory(), "Month")); // $NON-NLS-1$ registerFunctionModifier( SourceSystemFunctions.DAYNAME, new MonthOrDayNameFunctionModifier(getLanguageFactory(), "Day")); // $NON-NLS-1$ registerFunctionModifier( SourceSystemFunctions.WEEK, new DayWeekQuarterFunctionModifier("WW")); // $NON-NLS-1$ registerFunctionModifier( SourceSystemFunctions.QUARTER, new DayWeekQuarterFunctionModifier("Q")); // $NON-NLS-1$ registerFunctionModifier( SourceSystemFunctions.DAYOFWEEK, new DayWeekQuarterFunctionModifier("D")); // $NON-NLS-1$ registerFunctionModifier( SourceSystemFunctions.DAYOFYEAR, new DayWeekQuarterFunctionModifier("DDD")); // $NON-NLS-1$ registerFunctionModifier( SourceSystemFunctions.LOCATE, new LocateFunctionModifier(getLanguageFactory(), "INSTR", true)); // $NON-NLS-1$ registerFunctionModifier( SourceSystemFunctions.SUBSTRING, new AliasModifier("substr")); // $NON-NLS-1$ registerFunctionModifier( SourceSystemFunctions.LEFT, new LeftOrRightFunctionModifier(getLanguageFactory())); registerFunctionModifier( SourceSystemFunctions.RIGHT, new LeftOrRightFunctionModifier(getLanguageFactory())); registerFunctionModifier( SourceSystemFunctions.CONCAT, new ConcatFunctionModifier(getLanguageFactory())); registerFunctionModifier(SourceSystemFunctions.CONCAT2, new AliasModifier("||")); // $NON-NLS-1$ registerFunctionModifier( SourceSystemFunctions.COT, new FunctionModifier() { @Override public List<?> translate(Function function) { function.setName(SourceSystemFunctions.TAN); return Arrays.asList( getLanguageFactory() .createFunction( SourceSystemFunctions.DIVIDE_OP, new Expression[] { new Literal(1, TypeFacility.RUNTIME_TYPES.INTEGER), function }, TypeFacility.RUNTIME_TYPES.DOUBLE)); } }); // spatial functions registerFunctionModifier(OracleExecutionFactory.RELATE, new OracleSpatialFunctionModifier()); registerFunctionModifier( OracleExecutionFactory.NEAREST_NEIGHBOR, new OracleSpatialFunctionModifier()); registerFunctionModifier(OracleExecutionFactory.FILTER, new OracleSpatialFunctionModifier()); registerFunctionModifier( OracleExecutionFactory.WITHIN_DISTANCE, new OracleSpatialFunctionModifier()); registerFunctionModifier( SourceSystemFunctions.PARSETIMESTAMP, new OracleFormatFunctionModifier("TO_TIMESTAMP(")); // $NON-NLS-1$ registerFunctionModifier( SourceSystemFunctions.FORMATTIMESTAMP, new OracleFormatFunctionModifier("TO_CHAR(")); // $NON-NLS-1$ // add in type conversion ConvertModifier convertModifier = new ConvertModifier(); convertModifier.addTypeMapping("char(1)", FunctionModifier.CHAR); // $NON-NLS-1$ convertModifier.addTypeMapping( "date", FunctionModifier.DATE, FunctionModifier.TIME); // $NON-NLS-1$ convertModifier.addTypeMapping("timestamp", FunctionModifier.TIMESTAMP); // $NON-NLS-1$ convertModifier.addConvert( FunctionModifier.TIMESTAMP, FunctionModifier.TIME, new FunctionModifier() { @Override public List<?> translate(Function function) { return Arrays.asList( "case when ", function.getParameters().get(0), " is null then null else to_date('1970-01-01 ' || to_char(", function.getParameters().get(0), ", 'HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS') end"); //$NON-NLS-1$ //$NON-NLS-2$ // //$NON-NLS-3$ } }); convertModifier.addConvert( FunctionModifier.TIMESTAMP, FunctionModifier.DATE, new FunctionModifier() { @Override public List<?> translate(Function function) { return Arrays.asList( "trunc(cast(", function.getParameters().get(0), " AS date))"); //$NON-NLS-1$ //$NON-NLS-2$ } }); convertModifier.addConvert( FunctionModifier.DATE, FunctionModifier.STRING, new ConvertModifier.FormatModifier("to_char", DATE_FORMAT)); // $NON-NLS-1$ convertModifier.addConvert( FunctionModifier.TIME, FunctionModifier.STRING, new ConvertModifier.FormatModifier("to_char", TIME_FORMAT)); // $NON-NLS-1$ convertModifier.addConvert( FunctionModifier.TIMESTAMP, FunctionModifier.STRING, new FunctionModifier() { @Override public List<?> translate(Function function) { // if column and type is date, just use date format Expression ex = function.getParameters().get(0); String format = TIMESTAMP_FORMAT; if (ex instanceof ColumnReference && "date" .equalsIgnoreCase( ((ColumnReference) ex) .getMetadataObject() .getNativeType())) { //$NON-NLS-1$ format = DATETIME_FORMAT; } else if (!(ex instanceof Literal) && !(ex instanceof Function)) { // this isn't needed in every case, but it's simpler than inspecting the expression // more ex = ConvertModifier.createConvertFunction( getLanguageFactory(), function.getParameters().get(0), TypeFacility.RUNTIME_NAMES.TIMESTAMP); } return Arrays.asList( "to_char(", ex, ", '", format, "')"); // $NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ } }); convertModifier.addConvert( FunctionModifier.STRING, FunctionModifier.DATE, new ConvertModifier.FormatModifier("to_date", DATE_FORMAT)); // $NON-NLS-1$ convertModifier.addConvert( FunctionModifier.STRING, FunctionModifier.TIME, new ConvertModifier.FormatModifier("to_date", TIME_FORMAT)); // $NON-NLS-1$ convertModifier.addConvert( FunctionModifier.STRING, FunctionModifier.TIMESTAMP, new ConvertModifier.FormatModifier("to_timestamp", TIMESTAMP_FORMAT)); // $NON-NLS-1$ convertModifier.addTypeConversion( new ConvertModifier.FormatModifier("to_char"), FunctionModifier.STRING); // $NON-NLS-1$ // NOTE: numeric handling in Oracle is split only between integral vs. floating/decimal types convertModifier.addTypeConversion( new ConvertModifier.FormatModifier("to_number"), // $NON-NLS-1$ FunctionModifier.FLOAT, FunctionModifier.DOUBLE, FunctionModifier.BIGDECIMAL); convertModifier.addTypeConversion( new FunctionModifier() { @Override public List<?> translate(Function function) { if (Number.class.isAssignableFrom(function.getParameters().get(0).getType())) { return Arrays.asList( "trunc(", function.getParameters().get(0), ")"); // $NON-NLS-1$ //$NON-NLS-2$ } return Arrays.asList( "trunc(to_number(", function.getParameters().get(0), "))"); //$NON-NLS-1$ //$NON-NLS-2$ } }, FunctionModifier.BYTE, FunctionModifier.SHORT, FunctionModifier.INTEGER, FunctionModifier.LONG, FunctionModifier.BIGINTEGER); convertModifier.addNumericBooleanConversions(); convertModifier.setWideningNumericImplicit(true); registerFunctionModifier(SourceSystemFunctions.CONVERT, convertModifier); addPushDownFunction(ORACLE_SDO, RELATE, STRING, STRING, STRING, STRING); addPushDownFunction(ORACLE_SDO, RELATE, STRING, OBJECT, OBJECT, STRING); addPushDownFunction(ORACLE_SDO, RELATE, STRING, STRING, OBJECT, STRING); addPushDownFunction(ORACLE_SDO, RELATE, STRING, OBJECT, STRING, STRING); addPushDownFunction(ORACLE_SDO, NEAREST_NEIGHBOR, STRING, STRING, OBJECT, STRING, INTEGER); addPushDownFunction(ORACLE_SDO, NEAREST_NEIGHBOR, STRING, OBJECT, OBJECT, STRING, INTEGER); addPushDownFunction(ORACLE_SDO, NEAREST_NEIGHBOR, STRING, OBJECT, STRING, STRING, INTEGER); addPushDownFunction(ORACLE_SDO, NEAREST_NEIGHBOR_DISTANCE, INTEGER, INTEGER); addPushDownFunction(ORACLE_SDO, WITHIN_DISTANCE, STRING, OBJECT, OBJECT, STRING); addPushDownFunction(ORACLE_SDO, WITHIN_DISTANCE, STRING, STRING, OBJECT, STRING); addPushDownFunction(ORACLE_SDO, WITHIN_DISTANCE, STRING, OBJECT, STRING, STRING); addPushDownFunction(ORACLE_SDO, FILTER, STRING, OBJECT, STRING, STRING); addPushDownFunction(ORACLE_SDO, FILTER, STRING, OBJECT, OBJECT, STRING); addPushDownFunction(ORACLE_SDO, FILTER, STRING, STRING, OBJECT, STRING); }