/*
* JBoss, Home of Professional Open Source.
* See the COPYRIGHT.txt file distributed with this work for information
* regarding copyright ownership. Some portions may be licensed
* to Red Hat, Inc. under one or more contributor license agreements.
*
* This library is free software; you can redistribute it and/or
* modify it under the terms of the GNU Lesser General Public
* License as published by the Free Software Foundation; either
* version 2.1 of the License, or (at your option) any later version.
*
* This library is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
* Lesser General Public License for more details.
*
* You should have received a copy of the GNU Lesser General Public
* License along with this library; if not, write to the Free Software
* Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
* 02110-1301 USA.
*/
package org.teiid.translator.jdbc.netezza;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import org.teiid.language.Expression;
import org.teiid.language.Function;
import org.teiid.language.LanguageObject;
import org.teiid.language.Like;
import org.teiid.language.Like.MatchMode;
import org.teiid.language.Limit;
import org.teiid.translator.ExecutionContext;
import org.teiid.translator.SourceSystemFunctions;
import org.teiid.translator.Translator;
import org.teiid.translator.TranslatorException;
import org.teiid.translator.TranslatorProperty;
import org.teiid.translator.TypeFacility.RUNTIME_NAMES;
import org.teiid.translator.jdbc.AliasModifier;
import org.teiid.translator.jdbc.ConvertModifier;
import org.teiid.translator.jdbc.ExtractFunctionModifier;
import org.teiid.translator.jdbc.FunctionModifier;
import org.teiid.translator.jdbc.JDBCExecutionFactory;
import org.teiid.translator.jdbc.LocateFunctionModifier;
import org.teiid.util.Version;
@Translator(name = "netezza", description = "A translator for Netezza Database")
public class NetezzaExecutionFactory extends JDBCExecutionFactory {
private static final String TIME_FORMAT = "HH24:MI:SS"; //$NON-NLS-1$
private static final String DATE_FORMAT = "YYYY-MM-DD"; //$NON-NLS-1$
private static final String DATETIME_FORMAT = DATE_FORMAT + " " + TIME_FORMAT; //$NON-NLS-1$
private static final String TIMESTAMP_FORMAT = DATETIME_FORMAT + ".MS"; //$NON-NLS-1$
private static final Version SEVEN_0 = Version.getVersion("7.0"); //$NON-NLS-1$
private boolean sqlExtensionsInstalled;
public NetezzaExecutionFactory() {
setSupportsFullOuterJoins(true);
setSupportsOrderBy(true);
setSupportsOuterJoins(true);
setSupportsSelectDistinct(true);
setSupportsInnerJoins(true);
}
public void start() throws TranslatorException {
super.start();
//STRING FUNCTION MODIFIERS
////////////////////////////////////
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.LOCATE, new LocateFunctionModifier(getLanguageFactory(), "INSTR", true)); //$NON-NLS-1$
registerFunctionModifier(SourceSystemFunctions.CONCAT, new AliasModifier("||")); //$NON-NLS-1$
///NUMERIC FUNCTION MODIFIERS
////////////////////////////////////
registerFunctionModifier(SourceSystemFunctions.CEILING, new AliasModifier("ceil")); //$NON-NLS-1$
registerFunctionModifier(SourceSystemFunctions.POWER, new AliasModifier("pow")); //$NON-NLS-1$
registerFunctionModifier(SourceSystemFunctions.LOG, new AliasModifier("LN")); //$NON-NLS-1$
///BIT FUNCTION MODIFIERS
////////////////////////////////////
registerFunctionModifier(SourceSystemFunctions.BITAND, new AliasModifier("int4and")); //$NON-NLS-1$
registerFunctionModifier(SourceSystemFunctions.BITNOT, new AliasModifier("int4not")); //$NON-NLS-1$
registerFunctionModifier(SourceSystemFunctions.BITOR, new AliasModifier("int4or")); //$NON-NLS-1$
registerFunctionModifier(SourceSystemFunctions.BITXOR, new AliasModifier("int4xor")); //$NON-NLS-1$
//DATE FUNCTION MODIFIERS
//////////////////////////////////////////
registerFunctionModifier(SourceSystemFunctions.YEAR, new ExtractFunctionModifier());
registerFunctionModifier(SourceSystemFunctions.DAYOFYEAR, new ExtractModifier("DOY")); //$NON-NLS-1$
registerFunctionModifier(SourceSystemFunctions.QUARTER, new ExtractFunctionModifier());
registerFunctionModifier(SourceSystemFunctions.MONTH, new ExtractFunctionModifier());
registerFunctionModifier(SourceSystemFunctions.DAYOFMONTH, new ExtractModifier("DAY")); //$NON-NLS-1$
registerFunctionModifier(SourceSystemFunctions.WEEK, new ExtractFunctionModifier());
registerFunctionModifier(SourceSystemFunctions.DAYOFWEEK, new ExtractModifier("DOW")); //$NON-NLS-1$
registerFunctionModifier(SourceSystemFunctions.HOUR, new ExtractFunctionModifier());
registerFunctionModifier(SourceSystemFunctions.MINUTE, new ExtractFunctionModifier());
registerFunctionModifier(SourceSystemFunctions.SECOND, new ExtractFunctionModifier());
registerFunctionModifier(SourceSystemFunctions.CURDATE, new AliasModifier("CURRENT_DATE")); //$NON-NLS-1$
registerFunctionModifier(SourceSystemFunctions.CURTIME, new AliasModifier("CURRENT_TIME")); //$NON-NLS-1$
//SYSTEM FUNCTIONS
////////////////////////////////////
registerFunctionModifier(SourceSystemFunctions.IFNULL,new AliasModifier("NVL")); //$NON-NLS-1$
// DATA TYPE CONVERSION
///////////////////////////////////////////
ConvertModifier convertModifier = new ConvertModifier();
convertModifier.addTypeMapping("char(1)", FunctionModifier.CHAR); //$NON-NLS-1$
convertModifier.addTypeMapping("byteint", FunctionModifier.BYTE); //$NON-NLS-1$
convertModifier.addTypeMapping("smallint", FunctionModifier.SHORT); //$NON-NLS-1$
convertModifier.addTypeMapping("bigint", FunctionModifier.LONG); //$NON-NLS-1$
convertModifier.addTypeMapping("numeric(38)", FunctionModifier.BIGINTEGER); //$NON-NLS-1$
convertModifier.addTypeMapping("numeric(38,18)", FunctionModifier.BIGDECIMAL); //$NON-NLS-1$
convertModifier.addTypeMapping("varchar(4000)", FunctionModifier.STRING); //$NON-NLS-1$
//convertModifier.addTypeMapping("nvarchar(5)", FunctionModifier.BOOLEAN);
///NO BOOLEAN, INTEGER, FLOAT, DATE, TIME, TIMESTAMP, as they are directly available in netezza
///NO NULL, CLOB, BLOB, OBJECT, XML
///BOOLEAN--BYTE, SHORT, INTEGER, LONG, FLOAT, DOUBLE, BIGINTEGER, BIGDECIMAL--AS IT DOESN'T WORK IMPLICITLY IN NETEZZA
convertModifier.addConvert(FunctionModifier.BOOLEAN, FunctionModifier.INTEGER, new BooleanToNumericConversionModifier());
convertModifier.addConvert(FunctionModifier.BOOLEAN, FunctionModifier.BYTE, new BooleanToNumericConversionModifier());
convertModifier.addConvert(FunctionModifier.BOOLEAN, FunctionModifier.SHORT, new BooleanToNumericConversionModifier());
convertModifier.addConvert(FunctionModifier.BOOLEAN, FunctionModifier.LONG, new BooleanToNumericConversionModifier());
convertModifier.addConvert(FunctionModifier.BOOLEAN, FunctionModifier.FLOAT, new BooleanToNumericConversionModifier());
convertModifier.addConvert(FunctionModifier.BOOLEAN, FunctionModifier.DOUBLE, new BooleanToNumericConversionModifier());
convertModifier.addConvert(FunctionModifier.BOOLEAN, FunctionModifier.BIGINTEGER, new BooleanToNumericConversionModifier());
convertModifier.addConvert(FunctionModifier.BOOLEAN, FunctionModifier.BIGDECIMAL, new BooleanToNumericConversionModifier());
convertModifier.addConvert(FunctionModifier.BOOLEAN, FunctionModifier.STRING, new BooleanToStringConversionModifier());
convertModifier.addConvert(FunctionModifier.STRING, FunctionModifier.BOOLEAN, new FunctionModifier() {
@Override
public List<?> translate(Function function) {
Expression stringValue = function.getParameters().get(0);
return Arrays.asList("CASE WHEN ", stringValue, " IN ('false', '0') THEN '0' WHEN ", stringValue, " IS NOT NULL THEN '1' END"); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
}
});
convertModifier.addTypeConversion(new FunctionModifier() {
@Override
public List<?> translate(Function function) {
Expression stringValue = function.getParameters().get(0);
return Arrays.asList("CASE WHEN ", stringValue, " = 0 THEN '0' WHEN ", stringValue, " IS NOT NULL THEN '1' END"); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
}
}, FunctionModifier.BOOLEAN);
////////STRING TO DATATYPE CONVERSION OTHER THAN DATE/TIME
convertModifier.addConvert(FunctionModifier.STRING, FunctionModifier.INTEGER, new CastModifier("integer")); //$NON-NLS-1$
convertModifier.addConvert(FunctionModifier.STRING, FunctionModifier.FLOAT, new CastModifier("float")); //$NON-NLS-1$
convertModifier.addConvert(FunctionModifier.STRING, FunctionModifier.DOUBLE, new CastModifier("double")); //$NON-NLS-1$
///// STRING --> CHAR, BYTE, SHORT, LONG, BIGI, BIGD, BOOLEAN is taken care by Type Mapping
///// NO conversion support for NULL, CLOB, BLOB, OBJECT, XML
////STRING TO DATE/TIME CONVERSION////
//////////////////////////////////////
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_timestamp", TIME_FORMAT)); //$NON-NLS-1$
convertModifier.addConvert(FunctionModifier.STRING, FunctionModifier.TIMESTAMP, new ConvertModifier.FormatModifier("to_timestamp", TIMESTAMP_FORMAT)); //$NON-NLS-1$
//////DATE/TIME INTERNAL CONVERSION/////////
convertModifier.addConvert(FunctionModifier.TIMESTAMP, FunctionModifier.TIME, new CastModifier("TIME")); //$NON-NLS-1$
convertModifier.addConvert(FunctionModifier.TIMESTAMP, FunctionModifier.DATE, new CastModifier("DATE")); //$NON-NLS-1$
convertModifier.addConvert(FunctionModifier.DATE, FunctionModifier.TIMESTAMP, new CastModifier("TIMESTAMP")); //$NON-NLS-1$
//convertModifier.addConvert(FunctionModifier.TIME, FunctionModifier.TIMESTAMP, new CastModifier("TIMESTAMP")); //TIME --> TIMESTAMP --DOESN't WORK IN NETEZZA-NO FUNCTION SUPPORT
////DATE/TIME to STRING CONVERION////
/////////////////////////////////////
convertModifier.addConvert(FunctionModifier.TIMESTAMP, FunctionModifier.STRING, new ConvertModifier.FormatModifier("to_char", TIMESTAMP_FORMAT)); //$NON-NLS-1$
///NO NETEZAA FUNCTION for DATE, TIME to STRING
convertModifier.setWideningNumericImplicit(true);
registerFunctionModifier(SourceSystemFunctions.CONVERT, convertModifier);
if (sqlExtensionsInstalled) {
addPushDownFunction("netezza", "regexp_extract", RUNTIME_NAMES.STRING, RUNTIME_NAMES.STRING, RUNTIME_NAMES.STRING); //$NON-NLS-1$ //$NON-NLS-2$
addPushDownFunction("netezza", "regexp_extract", RUNTIME_NAMES.STRING, RUNTIME_NAMES.STRING, RUNTIME_NAMES.STRING, RUNTIME_NAMES.INTEGER, RUNTIME_NAMES.INTEGER); //$NON-NLS-1$ //$NON-NLS-2$
addPushDownFunction("netezza", "regexp_extract", RUNTIME_NAMES.STRING, RUNTIME_NAMES.STRING, RUNTIME_NAMES.STRING, RUNTIME_NAMES.INTEGER, RUNTIME_NAMES.INTEGER, RUNTIME_NAMES.STRING); //$NON-NLS-1$ //$NON-NLS-2$
addPushDownFunction("netezza", "regexp_extract_all", RUNTIME_NAMES.STRING+"[]", RUNTIME_NAMES.STRING, RUNTIME_NAMES.STRING); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
addPushDownFunction("netezza", "regexp_extract_all", RUNTIME_NAMES.STRING+"[]", RUNTIME_NAMES.STRING, RUNTIME_NAMES.STRING, RUNTIME_NAMES.INTEGER); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
addPushDownFunction("netezza", "regexp_extract_all", RUNTIME_NAMES.STRING+"[]", RUNTIME_NAMES.STRING, RUNTIME_NAMES.STRING, RUNTIME_NAMES.INTEGER, RUNTIME_NAMES.STRING); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
addPushDownFunction("netezza", "regexp_extract_all_sp", RUNTIME_NAMES.STRING+"[]", RUNTIME_NAMES.STRING, RUNTIME_NAMES.STRING); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
addPushDownFunction("netezza", "regexp_extract_all_sp", RUNTIME_NAMES.STRING+"[]", RUNTIME_NAMES.STRING, RUNTIME_NAMES.STRING, RUNTIME_NAMES.INTEGER); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
addPushDownFunction("netezza", "regexp_extract_all_sp", RUNTIME_NAMES.STRING+"[]", RUNTIME_NAMES.STRING, RUNTIME_NAMES.STRING, RUNTIME_NAMES.INTEGER, RUNTIME_NAMES.STRING); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
addPushDownFunction("netezza", "regexp_extract_sp", RUNTIME_NAMES.STRING, RUNTIME_NAMES.STRING, RUNTIME_NAMES.STRING, RUNTIME_NAMES.INTEGER, RUNTIME_NAMES.INTEGER); //$NON-NLS-1$ //$NON-NLS-2$
addPushDownFunction("netezza", "regexp_extract_sp", RUNTIME_NAMES.STRING, RUNTIME_NAMES.STRING, RUNTIME_NAMES.STRING, RUNTIME_NAMES.INTEGER, RUNTIME_NAMES.INTEGER, RUNTIME_NAMES.STRING); //$NON-NLS-1$ //$NON-NLS-2$
addPushDownFunction("netezza", "regexp_instr", RUNTIME_NAMES.STRING, RUNTIME_NAMES.STRING, RUNTIME_NAMES.STRING); //$NON-NLS-1$ //$NON-NLS-2$
addPushDownFunction("netezza", "regexp_instr", RUNTIME_NAMES.STRING, RUNTIME_NAMES.STRING, RUNTIME_NAMES.STRING, RUNTIME_NAMES.INTEGER, RUNTIME_NAMES.INTEGER); //$NON-NLS-1$ //$NON-NLS-2$
addPushDownFunction("netezza", "regexp_instr", RUNTIME_NAMES.STRING, RUNTIME_NAMES.STRING, RUNTIME_NAMES.STRING, RUNTIME_NAMES.INTEGER, RUNTIME_NAMES.INTEGER, RUNTIME_NAMES.STRING); //$NON-NLS-1$ //$NON-NLS-2$
addPushDownFunction("netezza", "regexp_like", RUNTIME_NAMES.BOOLEAN, RUNTIME_NAMES.STRING, RUNTIME_NAMES.STRING); //$NON-NLS-1$ //$NON-NLS-2$
addPushDownFunction("netezza", "regexp_like", RUNTIME_NAMES.BOOLEAN, RUNTIME_NAMES.STRING, RUNTIME_NAMES.STRING, RUNTIME_NAMES.STRING); //$NON-NLS-1$ //$NON-NLS-2$
addPushDownFunction("netezza", "regexp_like", RUNTIME_NAMES.BOOLEAN, RUNTIME_NAMES.STRING, RUNTIME_NAMES.STRING, RUNTIME_NAMES.INTEGER, RUNTIME_NAMES.STRING); //$NON-NLS-1$ //$NON-NLS-2$
addPushDownFunction("netezza", "regexp_match_count", RUNTIME_NAMES.INTEGER, RUNTIME_NAMES.STRING, RUNTIME_NAMES.STRING); //$NON-NLS-1$ //$NON-NLS-2$
addPushDownFunction("netezza", "regexp_match_count", RUNTIME_NAMES.INTEGER, RUNTIME_NAMES.STRING, RUNTIME_NAMES.STRING, RUNTIME_NAMES.INTEGER); //$NON-NLS-1$ //$NON-NLS-2$
addPushDownFunction("netezza", "regexp_match_count", RUNTIME_NAMES.INTEGER, RUNTIME_NAMES.STRING, RUNTIME_NAMES.STRING, RUNTIME_NAMES.INTEGER, RUNTIME_NAMES.STRING); //$NON-NLS-1$ //$NON-NLS-2$
addPushDownFunction("netezza", "regexp_replace", RUNTIME_NAMES.STRING, RUNTIME_NAMES.STRING, RUNTIME_NAMES.STRING, RUNTIME_NAMES.STRING); //$NON-NLS-1$ //$NON-NLS-2$
addPushDownFunction("netezza", "regexp_replace", RUNTIME_NAMES.STRING, RUNTIME_NAMES.STRING, RUNTIME_NAMES.STRING, RUNTIME_NAMES.STRING, RUNTIME_NAMES.INTEGER, RUNTIME_NAMES.INTEGER); //$NON-NLS-1$ //$NON-NLS-2$
addPushDownFunction("netezza", "regexp_replace", RUNTIME_NAMES.STRING, RUNTIME_NAMES.STRING, RUNTIME_NAMES.STRING, RUNTIME_NAMES.STRING, RUNTIME_NAMES.INTEGER, RUNTIME_NAMES.INTEGER, RUNTIME_NAMES.STRING); //$NON-NLS-1$ //$NON-NLS-2$
addPushDownFunction("netezza", "regexp_replace_sp", RUNTIME_NAMES.STRING, RUNTIME_NAMES.STRING, RUNTIME_NAMES.STRING+"[]"); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
addPushDownFunction("netezza", "regexp_replace_sp", RUNTIME_NAMES.STRING, RUNTIME_NAMES.STRING, RUNTIME_NAMES.STRING+"[]", RUNTIME_NAMES.INTEGER); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
addPushDownFunction("netezza", "regexp_replace_sp", RUNTIME_NAMES.STRING, RUNTIME_NAMES.STRING, RUNTIME_NAMES.STRING+"[]", RUNTIME_NAMES.INTEGER, RUNTIME_NAMES.STRING); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
}
}
@Override
public List<String> getSupportedFunctions() {
List<String> supportedFunctions = new ArrayList<String>();
supportedFunctions.addAll(super.getSupportedFunctions());
////////////////////////////////////////////////////////////
//STRING FUNCTIONS
//////////////////////////////////////////////////////////
supportedFunctions.add(SourceSystemFunctions.ASCII);// taken care with alias function modifier
supportedFunctions.add(SourceSystemFunctions.CHAR);//ALIAS to use 'chr'
supportedFunctions.add(SourceSystemFunctions.CONCAT); // ALIAS ||
supportedFunctions.add(SourceSystemFunctions.INITCAP);
supportedFunctions.add(SourceSystemFunctions.LCASE);//ALIAS 'lower'
supportedFunctions.add(SourceSystemFunctions.LPAD);
supportedFunctions.add(SourceSystemFunctions.LENGTH);
supportedFunctions.add(SourceSystemFunctions.LOCATE); //LOCATE FUNCTIO MODIFIER
supportedFunctions.add(SourceSystemFunctions.LTRIM);
//supportedFunctions.add(SourceSystemFunctions.REPEAT);
supportedFunctions.add(SourceSystemFunctions.RPAD);
supportedFunctions.add(SourceSystemFunctions.RTRIM);
supportedFunctions.add(SourceSystemFunctions.SUBSTRING); //No Need of ALIAS as both substring and substr work in netezza
supportedFunctions.add(SourceSystemFunctions.UCASE); //ALIAS UPPER
// FUNCTION DIFFERENCE = "difference"; ///NO FUNCTION FOUND--DIFFERENCE
// FUNCTION INSERT = "insert";
// supportedFunctions.add(SourceSystemFunctions.LEFT); //is this available or is it simply for LEFT OUTER JOIN?
// FUNCTION REPLACE = "replace"; // NO REPLACE Function
// supportedFunctions.add(SourceSystemFunctions.RIGHT);--is this available or is it simply for RIGHT OUTER JOIN?
// FUNCTION SOUNDEX = "soundex";
// FUNCTION TO_BYTES = "to_bytes";
// FUNCTION TO_CHARS = "to_chars";
////////// ////////////////////////////////////////////////////////////////////
//NUMERIC FUNCTIONS////////////////////////////////////////////////////////////
//////////////////////////////////////////////////////////////////////////////
//supportedFunctions.add(SourceSystemFunctions.ABS);
supportedFunctions.add(SourceSystemFunctions.ACOS);
supportedFunctions.add(SourceSystemFunctions.ASIN);
supportedFunctions.add(SourceSystemFunctions.ATAN);
supportedFunctions.add(SourceSystemFunctions.ATAN2);
supportedFunctions.add(SourceSystemFunctions.CEILING); ///ALIAS-ceil
supportedFunctions.add(SourceSystemFunctions.COS);
supportedFunctions.add(SourceSystemFunctions.COT);
supportedFunctions.add(SourceSystemFunctions.DEGREES);
//supportedFunctions.add(SourceSystemFunctions.EXP);
supportedFunctions.add(SourceSystemFunctions.FLOOR);
supportedFunctions.add(SourceSystemFunctions.LOG);
supportedFunctions.add(SourceSystemFunctions.MOD);
supportedFunctions.add(SourceSystemFunctions.PI);
supportedFunctions.add(SourceSystemFunctions.POWER);// ALIAS-POW
supportedFunctions.add(SourceSystemFunctions.RADIANS);
supportedFunctions.add(SourceSystemFunctions.ROUND);
supportedFunctions.add(SourceSystemFunctions.SIGN);
supportedFunctions.add(SourceSystemFunctions.SIN);
supportedFunctions.add(SourceSystemFunctions.SQRT);
supportedFunctions.add(SourceSystemFunctions.TAN);
// FUNCTION TRANSLATE = "translate";
// FUNCTION TRUNCATE = "truncate";
// FUNCTION FORMATINTEGER = "formatinteger";
// FUNCTION FORMATLONG = "formatlong";
// FUNCTION FORMATDOUBLE = "formatdouble";
// FUNCTION FORMATFLOAT = "formatfloat";
// FUNCTION FORMATBIGINTEGER = "formatbiginteger";
// FUNCTION FORMATBIGDECIMAL = "formatbigdecimal";
// FUNCTION LOG10 = "log10";
// FUNCTION PARSEINTEGER = "parseinteger";
// FUNCTION PARSELONG = "parselong";
// FUNCTION PARSEDOUBLE = "parsedouble";
// FUNCTION PARSEFLOAT = "parsefloat";
// FUNCTION PARSEBIGINTEGER = "parsebiginteger";
// FUNCTION PARSEBIGDECIMAL = "parsebigdecimal";
// supportedFunctions.add(SourceSystemFunctions.RAND); --Needs Alias--But, is it required to even have an alias???
/////////////////////////////////////////////////////////////////////
//BIT FUNCTIONS//////////////////////////////////////////////////////
//ALIAS FUNCTION MODIFIER IS APPLIED//////////////////////////////
supportedFunctions.add(SourceSystemFunctions.BITAND);
supportedFunctions.add(SourceSystemFunctions.BITOR);
supportedFunctions.add(SourceSystemFunctions.BITNOT);
supportedFunctions.add(SourceSystemFunctions.BITXOR);
// DATE FUNCTIONS
supportedFunctions.add(SourceSystemFunctions.CURDATE);
supportedFunctions.add(SourceSystemFunctions.CURTIME);
supportedFunctions.add(SourceSystemFunctions.DAYOFMONTH);
supportedFunctions.add(SourceSystemFunctions.DAYOFYEAR);
supportedFunctions.add(SourceSystemFunctions.DAYOFWEEK);
supportedFunctions.add(SourceSystemFunctions.HOUR);
supportedFunctions.add(SourceSystemFunctions.MINUTE);
supportedFunctions.add(SourceSystemFunctions.MONTH);
supportedFunctions.add(SourceSystemFunctions.QUARTER);
supportedFunctions.add(SourceSystemFunctions.SECOND);
supportedFunctions.add(SourceSystemFunctions.WEEK);
supportedFunctions.add(SourceSystemFunctions.YEAR);
// FUNCTION DAYNAME = "dayname";
// FUNCTION FORMATTIMESTAMP = "formattimestamp";
// FUNCTION MODIFYTIMEZONE = "modifytimezone";
// FUNCTION MONTHNAME = "monthname";
// FUNCTION NOW = "now";
// FUNCTION PARSETIMESTAMP = "parsetimestamp";
// FUNCTION TIMESTAMPADD = "timestampadd";
// FUNCTION TIMESTAMPCREATE = "timestampcreate";
// FUNCTION TIMESTAMPDIFF = "timestampdiff";
//SYSTEM FUNCTIONS
supportedFunctions.add(SourceSystemFunctions.IFNULL); //ALIAS-NVL
supportedFunctions.add(SourceSystemFunctions.COALESCE);
supportedFunctions.add(SourceSystemFunctions.NULLIF);
//CONVERSION functions
supportedFunctions.add(SourceSystemFunctions.CONVERT);
return supportedFunctions;
}
public static class ExtractModifier extends FunctionModifier {
private String type;
public ExtractModifier(String type) {
this.type = type;
}
@Override
public List<?> translate(Function function) {
return Arrays.asList("extract(",this.type," from ",function.getParameters().get(0) ,")"); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
}
}
public static class BooleanToNumericConversionModifier extends FunctionModifier {
@Override
public List<?> translate(Function function) {
Expression booleanValue = function.getParameters().get(0);
if (booleanValue instanceof Function) {
Function nested = (Function)booleanValue;
if (nested.getName().equalsIgnoreCase("convert") && Number.class.isAssignableFrom(nested.getParameters().get(0).getType())) { //$NON-NLS-1$
booleanValue = nested.getParameters().get(0);
}
}
return Arrays.asList("(CASE WHEN ", booleanValue, " IN ( '0', 'FALSE') THEN 0 WHEN ", booleanValue, " IS NOT NULL THEN 1 END)"); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
}
}
public static class BooleanToStringConversionModifier extends FunctionModifier {
@Override
public List<?> translate(Function function) {
Expression booleanValue = function.getParameters().get(0);
if (booleanValue instanceof Function) {
Function nested = (Function)booleanValue;
if (nested.getName().equalsIgnoreCase("convert") && Number.class.isAssignableFrom(nested.getParameters().get(0).getType())) { //$NON-NLS-1$
booleanValue = nested.getParameters().get(0);
}
}
return Arrays.asList("CASE WHEN ", booleanValue, " = '0' THEN 'false' WHEN ", booleanValue, " IS NOT NULL THEN 'true' END"); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
}
}
public static class CastModifier extends FunctionModifier {
private String target;
public CastModifier(String target) {
this.target = target;
}
@Override
public List<?> translate(Function function) {
return Arrays.asList("cast(", function.getParameters().get(0), " AS "+this.target+")"); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
}
}
@Override
public List<?> translateLimit(Limit limit, ExecutionContext context) {
if (limit.getRowOffset() > 0) {
return Arrays.asList("LIMIT ", limit.getRowLimit(), " OFFSET ", limit.getRowOffset()); //$NON-NLS-1$ //$NON-NLS-2$
}
return null;
}
@Override
public List<?> translate(LanguageObject obj, ExecutionContext context) {
if (obj instanceof Like) {
Like like = (Like)obj;
if (like.getMode() == MatchMode.REGEX) {
if (like.isNegated()) {
return Arrays.asList("NOT(REGEXP_LIKE(", like.getLeftExpression(), ", ", like.getRightExpression(), "))"); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
}
return Arrays.asList("REGEXP_LIKE(", like.getLeftExpression(), ", ", like.getRightExpression(), ")"); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
}
}
return super.translate(obj, context);
}
@Override
public boolean supportsCorrelatedSubqueries() {
return false;
}
@Override
public boolean supportsIntersect() {
return true;
}
@Override
public boolean supportsExcept() {
return true;
}
@Override
public boolean supportsInlineViews() {
return true;
}
@Override
public boolean supportsRowLimit() {
return true;
}
@Override
public boolean supportsRowOffset() {
return true;
}
@Override
public boolean supportsAggregatesEnhancedNumeric() {
return true;
}
@Override
public boolean supportsLikeRegex() {
return sqlExtensionsInstalled;
}
@TranslatorProperty(display="SQL Extensions Installed", description="True if SQL Extensions including support fo REGEXP_LIKE are installed",advanced=true)
public boolean isSqlExtensionsInstalled() {
return sqlExtensionsInstalled;
}
public void setSqlExtensionsInstalled(boolean sqlExtensionsInstalled) {
this.sqlExtensionsInstalled = sqlExtensionsInstalled;
}
@Override
public boolean supportsCommonTableExpressions() {
return getVersion().compareTo(SEVEN_0) >= 0;
}
@Override
protected boolean usesDatabaseVersion() {
return true;
}
}