Author: shawkins Date: 2011-08-10 14:01:12 -0400 (Wed, 10 Aug 2011) New Revision: 3366 Added: trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/netezza/ trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/netezza/NetezzaExecutionFactory.java trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/netezza/ trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/netezza/TestNetezzaConvertModifier.java trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/netezza/TestNetezzaTranslatorCapabilities.java trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/netezza/TestNetezzaTranslatorDatetimeConversion.java trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/netezza/TestNetezzaTranslatorSourceSystemFunctions.java trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/netezza/TestNetezzaTranslatorTypeMapping.java trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/netezza/TestSubstringFunctionModifier.java trunk/engine/src/main/java/org/teiid/query/tempdata/AlterTempTable.java trunk/engine/src/main/java/org/teiid/query/xquery/saxon/XQueryEvaluator.java Removed: trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/netezza/NetezzaExecutionFactory.java trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/netezza/TestNetezzaConvertModifier.java trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/netezza/TestNetezzaTranslatorCapabilities.java trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/netezza/TestNetezzaTranslatorDatetimeConversion.java trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/netezza/TestNetezzaTranslatorSourceSystemFunctions.java trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/netezza/TestNetezzaTranslatorTypeMapping.java trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/netezza/TestSubstringFunctionModifier.java Modified: trunk/build/kits/jboss-container/teiid-releasenotes.html trunk/client/src/main/java/org/teiid/jdbc/StatementImpl.java trunk/connectors/translator-jdbc/src/main/resources/META-INF/jboss-beans.xml trunk/connectors/translator-ldap/src/main/java/org/teiid/translator/ldap/LDAPExecutionFactory.java trunk/connectors/translator-ldap/src/main/java/org/teiid/translator/ldap/LDAPSyncQueryExecution.java trunk/documentation/admin-guide/src/main/docbook/en-US/content/admin-console.xml trunk/documentation/admin-guide/src/main/docbook/en-US/images/admin_console.png trunk/documentation/caching-guide/src/main/docbook/en-US/content/codetable.xml trunk/documentation/reference/src/main/docbook/en-US/content/scalar_functions.xml trunk/documentation/reference/src/main/docbook/en-US/content/translators.xml trunk/engine/src/main/java/org/teiid/dqp/internal/process/CachedResults.java trunk/engine/src/main/java/org/teiid/dqp/internal/process/RequestWorkItem.java trunk/engine/src/main/java/org/teiid/query/analysis/AnalysisRecord.java trunk/engine/src/main/java/org/teiid/query/eval/Evaluator.java trunk/engine/src/main/java/org/teiid/query/mapping/xml/ResultSetInfo.java trunk/engine/src/main/java/org/teiid/query/metadata/TempMetadataAdapter.java trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleMergeCriteria.java trunk/engine/src/main/java/org/teiid/query/optimizer/xml/XMLPlanToProcessVisitor.java trunk/engine/src/main/java/org/teiid/query/optimizer/xml/XMLPlanner.java trunk/engine/src/main/java/org/teiid/query/optimizer/xml/XMLPlannerEnvironment.java trunk/engine/src/main/java/org/teiid/query/optimizer/xml/XMLQueryPlanner.java trunk/engine/src/main/java/org/teiid/query/optimizer/xml/XMLStagaingQueryPlanner.java trunk/engine/src/main/java/org/teiid/query/processor/xml/ExecSqlInstruction.java trunk/engine/src/main/java/org/teiid/query/processor/xml/ExecStagingTableInstruction.java trunk/engine/src/main/java/org/teiid/query/processor/xml/RelationalPlanExecutor.java trunk/engine/src/main/java/org/teiid/query/processor/xml/XMLProcessorEnvironment.java trunk/engine/src/main/java/org/teiid/query/resolver/command/InsertResolver.java trunk/engine/src/main/java/org/teiid/query/resolver/util/ResolverVisitor.java trunk/engine/src/main/java/org/teiid/query/tempdata/TempTableDataManager.java trunk/engine/src/main/java/org/teiid/query/xquery/saxon/SaxonXQueryExpression.java trunk/engine/src/main/java/org/teiid/query/xquery/saxon/StreamingUtils.java trunk/engine/src/test/java/org/teiid/query/processor/eval/TestExpressionEvaluator.java trunk/engine/src/test/java/org/teiid/query/processor/xml/TestXMLPlanningEnhancements.java trunk/engine/src/test/java/org/teiid/query/processor/xml/TestXMLProcessor.java trunk/engine/src/test/java/org/teiid/query/rewriter/TestQueryRewriter.java Log: forward merge from 7.4.1 Modified: trunk/build/kits/jboss-container/teiid-releasenotes.html =================================================================== --- trunk/build/kits/jboss-container/teiid-releasenotes.html 2011-08-06 14:57:03 UTC (rev 3365) +++ trunk/build/kits/jboss-container/teiid-releasenotes.html 2011-08-10 18:01:12 UTC (rev 3366) @@ -43,6 +43,7 @@ <LI><B>Sync at Start</B> - When a node is restarted in a cluster, at end of start cycle Teiid will asynchronously fetch the cached internal materialized view contents from other nodes. </UL> </LI> + <LI><B>LDAP Pagination</B> - the ldap translator now supports pagination for retrieving large results from directory servers like Active Directory and OpenLDAP. </UL> <h2><a name="Compatibility">Compatibility Issues</a></h2> Modified: trunk/client/src/main/java/org/teiid/jdbc/StatementImpl.java =================================================================== --- trunk/client/src/main/java/org/teiid/jdbc/StatementImpl.java 2011-08-06 14:57:03 UTC (rev 3365) +++ trunk/client/src/main/java/org/teiid/jdbc/StatementImpl.java 2011-08-10 18:01:12 UTC (rev 3366) @@ -1060,9 +1060,15 @@ } protected void setAnalysisInfo(ResultsMessage resultsMsg) { - this.debugLog = resultsMsg.getDebugLog(); - this.currentPlanDescription = resultsMsg.getPlanDescription(); - this.annotations = resultsMsg.getAnnotations(); + if (resultsMsg.getDebugLog() != null) { + this.debugLog = resultsMsg.getDebugLog(); + } + if (resultsMsg.getPlanDescription() != null) { + this.currentPlanDescription = resultsMsg.getPlanDescription(); + } + if (resultsMsg.getAnnotations() != null) { + this.annotations = resultsMsg.getAnnotations(); + } this.driverConnection.setDebugLog(debugLog); this.driverConnection.setCurrentPlanDescription(currentPlanDescription); this.driverConnection.setAnnotations(annotations); Property changes on: trunk/client/src/main/java/org/teiid/jdbc/StatementImpl.java ___________________________________________________________________ Modified: svn:mergeinfo - /branches/7.4.x/client/src/main/java/org/teiid/jdbc/StatementImpl.java:3149-3217,3220-3275,3281-3325 + /branches/7.4.x/client/src/main/java/org/teiid/jdbc/StatementImpl.java:3149-3217,3220-3275,3281-3325,3355-3365 Deleted: trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/netezza/NetezzaExecutionFactory.java =================================================================== --- branches/7.4.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/netezza/NetezzaExecutionFactory.java 2011-08-06 14:57:03 UTC (rev 3365) +++ trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/netezza/NetezzaExecutionFactory.java 2011-08-10 18:01:12 UTC (rev 3366) @@ -1,382 +0,0 @@ -/* - * 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.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.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; - - -@Translator(name = "netezza", description = "A translator for Netezza Database") -public class NetezzaExecutionFactory extends JDBCExecutionFactory { - - private static final String TIME_FORMAT = "HH24:MI:SS"; - private static final String DATE_FORMAT = "YYYY-MM-DD"; - private static final String DATETIME_FORMAT = DATE_FORMAT + " " + TIME_FORMAT; - private static final String TIMESTAMP_FORMAT = DATETIME_FORMAT + ".MS"; - - 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")); - registerFunctionModifier(SourceSystemFunctions.LCASE,new AliasModifier("lower")); - registerFunctionModifier(SourceSystemFunctions.UCASE,new AliasModifier("upper")); - registerFunctionModifier(SourceSystemFunctions.LOCATE, new LocateFunctionModifier(getLanguageFactory(), "INSTR", true)); - registerFunctionModifier(SourceSystemFunctions.CONCAT, new AliasModifier("||")); - ///NUMERIC FUNCTION MODIFIERS - //////////////////////////////////// - registerFunctionModifier(SourceSystemFunctions.CEILING, new AliasModifier("ceil")); - registerFunctionModifier(SourceSystemFunctions.POWER, new AliasModifier("pow")); - registerFunctionModifier(SourceSystemFunctions.LOG, new AliasModifier("LN")); - ///BIT FUNCTION MODIFIERS - //////////////////////////////////// - registerFunctionModifier(SourceSystemFunctions.BITAND, new AliasModifier("intNand")); - registerFunctionModifier(SourceSystemFunctions.BITNOT, new AliasModifier("intNnot")); - registerFunctionModifier(SourceSystemFunctions.BITOR, new AliasModifier("intNor")); - registerFunctionModifier(SourceSystemFunctions.BITXOR, new AliasModifier("intNxor")); - //DATE FUNCTION MODIFIERS - ////////////////////////////////////////// - registerFunctionModifier(SourceSystemFunctions.YEAR, new ExtractFunctionModifier()); - registerFunctionModifier(SourceSystemFunctions.DAYOFYEAR, new ExtractModifier("DOY")); - registerFunctionModifier(SourceSystemFunctions.QUARTER, new ExtractFunctionModifier()); - registerFunctionModifier(SourceSystemFunctions.MONTH, new ExtractFunctionModifier()); - registerFunctionModifier(SourceSystemFunctions.DAYOFMONTH, new ExtractModifier("DAY")); - registerFunctionModifier(SourceSystemFunctions.WEEK, new ExtractFunctionModifier()); - registerFunctionModifier(SourceSystemFunctions.DAYOFWEEK, new ExtractModifier("DOW")); - registerFunctionModifier(SourceSystemFunctions.HOUR, new ExtractFunctionModifier()); - registerFunctionModifier(SourceSystemFunctions.MINUTE, new ExtractFunctionModifier()); - registerFunctionModifier(SourceSystemFunctions.SECOND, new ExtractFunctionModifier()); - registerFunctionModifier(SourceSystemFunctions.CURDATE, new AliasModifier("CURRENT_DATE")); - registerFunctionModifier(SourceSystemFunctions.CURTIME, new AliasModifier("CURRENT_TIME")); - //SYSTEM FUNCTIONS - //////////////////////////////////// - registerFunctionModifier(SourceSystemFunctions.IFNULL,new AliasModifier("NVL")); - - - // DATA TYPE CONVERSION - /////////////////////////////////////////// - ConvertModifier convertModifier = new ConvertModifier(); - convertModifier.addTypeMapping("char(1)", FunctionModifier.CHAR); - convertModifier.addTypeMapping("byteint", FunctionModifier.BYTE); - convertModifier.addTypeMapping("smallint", FunctionModifier.SHORT); - convertModifier.addTypeMapping("bigint", FunctionModifier.LONG); - convertModifier.addTypeMapping("numeric(38)", FunctionModifier.BIGINTEGER); - convertModifier.addTypeMapping("numeric(38,18)", FunctionModifier.BIGDECIMAL); - convertModifier.addTypeMapping("varchar(4000)", FunctionModifier.STRING); - //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"); - } - }); - 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"); - } - }, FunctionModifier.BOOLEAN); - - - - - ////////STRING TO DATATYPE CONVERSION OTHER THAN DATE/TIME - convertModifier.addConvert(FunctionModifier.STRING, FunctionModifier.INTEGER, new CastModifier("integer")); - convertModifier.addConvert(FunctionModifier.STRING, FunctionModifier.FLOAT, new CastModifier("float")); - convertModifier.addConvert(FunctionModifier.STRING, FunctionModifier.DOUBLE, new CastModifier("double")); - ///// 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)); - convertModifier.addConvert(FunctionModifier.STRING, FunctionModifier.TIME, new ConvertModifier.FormatModifier("to_timestamp", TIME_FORMAT)); - convertModifier.addConvert(FunctionModifier.STRING, FunctionModifier.TIMESTAMP, new ConvertModifier.FormatModifier("to_timestamp", TIMESTAMP_FORMAT)); - //////DATE/TIME INTERNAL CONVERSION///////// - convertModifier.addConvert(FunctionModifier.TIMESTAMP, FunctionModifier.TIME, new CastModifier("TIME")); - convertModifier.addConvert(FunctionModifier.TIMESTAMP, FunctionModifier.DATE, new CastModifier("DATE")); - convertModifier.addConvert(FunctionModifier.DATE, FunctionModifier.TIMESTAMP, new CastModifier("TIMESTAMP")); - //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)); - ///NO NETEZAA FUNCTION for DATE, TIME to STRING - - - convertModifier.setWideningNumericImplicit(true); - registerFunctionModifier(SourceSystemFunctions.CONVERT, convertModifier); - } - - @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) ,")"); - } - } - - 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())) { - booleanValue = nested.getParameters().get(0); - } - } - return Arrays.asList("(CASE WHEN ", booleanValue, " IN ( '0', 'FALSE') THEN 0 WHEN ", booleanValue, " IS NOT NULL THEN 1 END)"); - } - - } - 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())) { - booleanValue = nested.getParameters().get(0); - } - } - return Arrays.asList("CASE WHEN ", booleanValue, " = '0' THEN 'false' WHEN ", booleanValue, " IS NOT NULL THEN 'true' END"); - } - - } - - - 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+")"); - } - } - - - @Override - public List<?> translateLimit(Limit limit, ExecutionContext context) { - if (limit.getRowOffset() > 0) { - return Arrays.asList("LIMIT ", limit.getRowLimit(), " OFFSET ", limit.getRowOffset()); - } - return null; - } - - @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; - } - -} Copied: trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/netezza/NetezzaExecutionFactory.java (from rev 3365, branches/7.4.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/netezza/NetezzaExecutionFactory.java) =================================================================== --- trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/netezza/NetezzaExecutionFactory.java (rev 0) +++ trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/netezza/NetezzaExecutionFactory.java 2011-08-10 18:01:12 UTC (rev 3366) @@ -0,0 +1,382 @@ +/* + * 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.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.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; + + +@Translator(name = "netezza", description = "A translator for Netezza Database") +public class NetezzaExecutionFactory extends JDBCExecutionFactory { + + private static final String TIME_FORMAT = "HH24:MI:SS"; + private static final String DATE_FORMAT = "YYYY-MM-DD"; + private static final String DATETIME_FORMAT = DATE_FORMAT + " " + TIME_FORMAT; + private static final String TIMESTAMP_FORMAT = DATETIME_FORMAT + ".MS"; + + 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")); + registerFunctionModifier(SourceSystemFunctions.LCASE,new AliasModifier("lower")); + registerFunctionModifier(SourceSystemFunctions.UCASE,new AliasModifier("upper")); + registerFunctionModifier(SourceSystemFunctions.LOCATE, new LocateFunctionModifier(getLanguageFactory(), "INSTR", true)); + registerFunctionModifier(SourceSystemFunctions.CONCAT, new AliasModifier("||")); + ///NUMERIC FUNCTION MODIFIERS + //////////////////////////////////// + registerFunctionModifier(SourceSystemFunctions.CEILING, new AliasModifier("ceil")); + registerFunctionModifier(SourceSystemFunctions.POWER, new AliasModifier("pow")); + registerFunctionModifier(SourceSystemFunctions.LOG, new AliasModifier("LN")); + ///BIT FUNCTION MODIFIERS + //////////////////////////////////// + registerFunctionModifier(SourceSystemFunctions.BITAND, new AliasModifier("intNand")); + registerFunctionModifier(SourceSystemFunctions.BITNOT, new AliasModifier("intNnot")); + registerFunctionModifier(SourceSystemFunctions.BITOR, new AliasModifier("intNor")); + registerFunctionModifier(SourceSystemFunctions.BITXOR, new AliasModifier("intNxor")); + //DATE FUNCTION MODIFIERS + ////////////////////////////////////////// + registerFunctionModifier(SourceSystemFunctions.YEAR, new ExtractFunctionModifier()); + registerFunctionModifier(SourceSystemFunctions.DAYOFYEAR, new ExtractModifier("DOY")); + registerFunctionModifier(SourceSystemFunctions.QUARTER, new ExtractFunctionModifier()); + registerFunctionModifier(SourceSystemFunctions.MONTH, new ExtractFunctionModifier()); + registerFunctionModifier(SourceSystemFunctions.DAYOFMONTH, new ExtractModifier("DAY")); + registerFunctionModifier(SourceSystemFunctions.WEEK, new ExtractFunctionModifier()); + registerFunctionModifier(SourceSystemFunctions.DAYOFWEEK, new ExtractModifier("DOW")); + registerFunctionModifier(SourceSystemFunctions.HOUR, new ExtractFunctionModifier()); + registerFunctionModifier(SourceSystemFunctions.MINUTE, new ExtractFunctionModifier()); + registerFunctionModifier(SourceSystemFunctions.SECOND, new ExtractFunctionModifier()); + registerFunctionModifier(SourceSystemFunctions.CURDATE, new AliasModifier("CURRENT_DATE")); + registerFunctionModifier(SourceSystemFunctions.CURTIME, new AliasModifier("CURRENT_TIME")); + //SYSTEM FUNCTIONS + //////////////////////////////////// + registerFunctionModifier(SourceSystemFunctions.IFNULL,new AliasModifier("NVL")); + + + // DATA TYPE CONVERSION + /////////////////////////////////////////// + ConvertModifier convertModifier = new ConvertModifier(); + convertModifier.addTypeMapping("char(1)", FunctionModifier.CHAR); + convertModifier.addTypeMapping("byteint", FunctionModifier.BYTE); + convertModifier.addTypeMapping("smallint", FunctionModifier.SHORT); + convertModifier.addTypeMapping("bigint", FunctionModifier.LONG); + convertModifier.addTypeMapping("numeric(38)", FunctionModifier.BIGINTEGER); + convertModifier.addTypeMapping("numeric(38,18)", FunctionModifier.BIGDECIMAL); + convertModifier.addTypeMapping("varchar(4000)", FunctionModifier.STRING); + //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"); + } + }); + 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"); + } + }, FunctionModifier.BOOLEAN); + + + + + ////////STRING TO DATATYPE CONVERSION OTHER THAN DATE/TIME + convertModifier.addConvert(FunctionModifier.STRING, FunctionModifier.INTEGER, new CastModifier("integer")); + convertModifier.addConvert(FunctionModifier.STRING, FunctionModifier.FLOAT, new CastModifier("float")); + convertModifier.addConvert(FunctionModifier.STRING, FunctionModifier.DOUBLE, new CastModifier("double")); + ///// 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)); + convertModifier.addConvert(FunctionModifier.STRING, FunctionModifier.TIME, new ConvertModifier.FormatModifier("to_timestamp", TIME_FORMAT)); + convertModifier.addConvert(FunctionModifier.STRING, FunctionModifier.TIMESTAMP, new ConvertModifier.FormatModifier("to_timestamp", TIMESTAMP_FORMAT)); + //////DATE/TIME INTERNAL CONVERSION///////// + convertModifier.addConvert(FunctionModifier.TIMESTAMP, FunctionModifier.TIME, new CastModifier("TIME")); + convertModifier.addConvert(FunctionModifier.TIMESTAMP, FunctionModifier.DATE, new CastModifier("DATE")); + convertModifier.addConvert(FunctionModifier.DATE, FunctionModifier.TIMESTAMP, new CastModifier("TIMESTAMP")); + //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)); + ///NO NETEZAA FUNCTION for DATE, TIME to STRING + + + convertModifier.setWideningNumericImplicit(true); + registerFunctionModifier(SourceSystemFunctions.CONVERT, convertModifier); + } + + @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) ,")"); + } + } + + 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())) { + booleanValue = nested.getParameters().get(0); + } + } + return Arrays.asList("(CASE WHEN ", booleanValue, " IN ( '0', 'FALSE') THEN 0 WHEN ", booleanValue, " IS NOT NULL THEN 1 END)"); + } + + } + 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())) { + booleanValue = nested.getParameters().get(0); + } + } + return Arrays.asList("CASE WHEN ", booleanValue, " = '0' THEN 'false' WHEN ", booleanValue, " IS NOT NULL THEN 'true' END"); + } + + } + + + 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+")"); + } + } + + + @Override + public List<?> translateLimit(Limit limit, ExecutionContext context) { + if (limit.getRowOffset() > 0) { + return Arrays.asList("LIMIT ", limit.getRowLimit(), " OFFSET ", limit.getRowOffset()); + } + return null; + } + + @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; + } + +} Modified: trunk/connectors/translator-jdbc/src/main/resources/META-INF/jboss-beans.xml =================================================================== --- trunk/connectors/translator-jdbc/src/main/resources/META-INF/jboss-beans.xml 2011-08-06 14:57:03 UTC (rev 3365) +++ trunk/connectors/translator-jdbc/src/main/resources/META-INF/jboss-beans.xml 2011-08-10 18:01:12 UTC (rev 3366) @@ -335,5 +335,21 @@ <parameter class="java.lang.String">translator-intersystems-cache</parameter> <parameter class="java.lang.String">InterSystems Cache</parameter> </constructor> - </bean> + </bean> + + <!-- Netezza --> + <bean name="translator-neteza-template" class="org.teiid.templates.TranslatorDeploymentTemplate"> + <property name="info"><inject bean="translator-netezza" /> </property> + <property name="managedObjectFactory"> <inject bean="ManagedObjectFactory" /> </property> + </bean> + + <bean name="translator-netezza" class="org.teiid.templates.TranslatorTemplateInfo"> + <constructor factoryMethod="createTemplateInfo"> + <factory bean="TranslatorDeploymentTemplateInfoFactory" /> + <parameter class="java.lang.Class">org.teiid.templates.TranslatorTemplateInfo</parameter> + <parameter class="java.lang.Class">org.teiid.translator.jdbc.netezza.NetezzaExecutionFactory</parameter> + <parameter class="java.lang.String">translator-netezza</parameter> + <parameter class="java.lang.String">Netezza</parameter> + </constructor> + </bean> </deployment> \ No newline at end of file Deleted: trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/netezza/TestNetezzaConvertModifier.java =================================================================== --- branches/7.4.x/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/netezza/TestNetezzaConvertModifier.java 2011-08-06 14:57:03 UTC (rev 3365) +++ trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/netezza/TestNetezzaConvertModifier.java 2011-08-10 18:01:12 UTC (rev 3366) @@ -1,509 +0,0 @@ -/* - * 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.math.BigDecimal; -import java.math.BigInteger; -import java.util.Arrays; - -import junit.framework.TestCase; - -import org.teiid.language.Expression; -import org.teiid.language.Function; -import org.teiid.language.LanguageFactory; -import org.teiid.translator.TypeFacility; -import org.teiid.translator.jdbc.SQLConversionVisitor; - - -/** - */ -public class TestNetezzaConvertModifier extends TestCase { - - private static final LanguageFactory LANG_FACTORY = new LanguageFactory(); - - public TestNetezzaConvertModifier(String name) { - super(name); - } - - public String helpGetString(Expression expr) throws Exception { - NetezzaExecutionFactory trans = new NetezzaExecutionFactory(); - trans.start(); - SQLConversionVisitor sqlVisitor = trans.getSQLConversionVisitor(); - sqlVisitor.append(expr); - - return sqlVisitor.toString(); - } - - public void helpTest(Expression srcExpression, String tgtType, String expectedExpression) throws Exception { - Function func = LANG_FACTORY.createFunction("convert", //$NON-NLS-1$ - Arrays.asList( - srcExpression, - LANG_FACTORY.createLiteral(tgtType, String.class)), - TypeFacility.getDataTypeClass(tgtType)); - - assertEquals("Error converting from " + srcExpression.getType() + " to " + tgtType, - expectedExpression, helpGetString(func)); - } - - - - - // Source = STRING - public void testStringToChar() throws Exception { - helpTest(LANG_FACTORY.createLiteral("5", String.class), "char", "cast('5' AS char(1))"); - } - - public void testStringToBoolean() throws Exception { - helpTest(LANG_FACTORY.createLiteral("5", String.class), "boolean", "CASE WHEN '5' IN ('false', '0') THEN '0' WHEN '5' IS NOT NULL THEN '1' END"); - } - - public void testStringToByte() throws Exception { - helpTest(LANG_FACTORY.createLiteral("5", String.class), "byte", "cast('5' AS byteint)"); - } - - public void testStringToShort() throws Exception { - helpTest(LANG_FACTORY.createLiteral("5", String.class), "short", "cast('5' AS smallint)"); - } - - public void testStringToInteger() throws Exception { - helpTest(LANG_FACTORY.createLiteral("5", String.class), "integer", "cast('5' AS integer)"); - } - - public void testStringToLong() throws Exception { - helpTest(LANG_FACTORY.createLiteral("5", String.class), "long", "cast('5' AS bigint)"); - } - - public void testStringToBigInteger() throws Exception { - helpTest(LANG_FACTORY.createLiteral("5", String.class), "biginteger", "cast('5' AS numeric(38))"); //$NON-NLS-1$//$NON-NLS-2$ //$NON-NLS-3$ - } - - public void testStringToFloat() throws Exception { - helpTest(LANG_FACTORY.createLiteral("5", String.class), "float", "cast('5' AS float)"); - } - - public void testStringToDouble() throws Exception { - helpTest(LANG_FACTORY.createLiteral("5", String.class), "double", "cast('5' AS double)"); - } - - public void testStringToDate() throws Exception { - helpTest(LANG_FACTORY.createLiteral("2004-06-29", String.class), "date", "to_date('2004-06-29', 'YYYY-MM-DD')"); - } - - public void testStringToTime() throws Exception { - helpTest(LANG_FACTORY.createLiteral("23:59:59", String.class), "time", "to_timestamp('23:59:59', 'HH24:MI:SS')"); - } - - public void testStringToTimestamp() throws Exception { - helpTest(LANG_FACTORY.createLiteral("2004-06-29 23:59:59.987", String.class), "timestamp", "to_timestamp('2004-06-29 23:59:59.987', 'YYYY-MM-DD HH24:MI:SS.MS')"); - } - - public void testStringToBigDecimal() throws Exception { - helpTest(LANG_FACTORY.createLiteral("5", String.class), "bigdecimal", "cast('5' AS numeric(38,18))"); - } - - // Source = CHAR - - public void testCharToString() throws Exception { - helpTest(LANG_FACTORY.createLiteral(new Character('5'), Character.class), "string", "'5'"); - } - - // Source = BOOLEAN - - public void testBooleanToString() throws Exception { - helpTest(LANG_FACTORY.createLiteral(Boolean.TRUE, Boolean.class), "string", "CASE WHEN 1 = '0' THEN 'false' WHEN 1 IS NOT NULL THEN 'true' END"); - } - - public void testBooleanToByte() throws Exception { - helpTest(LANG_FACTORY.createLiteral(Boolean.FALSE, Boolean.class), "byte", "(CASE WHEN 0 IN ( '0', 'FALSE') THEN 0 WHEN 0 IS NOT NULL THEN 1 END)"); - } - - public void testBooleanToShort() throws Exception { - helpTest(LANG_FACTORY.createLiteral(Boolean.FALSE, Boolean.class), "short", "(CASE WHEN 0 IN ( '0', 'FALSE') THEN 0 WHEN 0 IS NOT NULL THEN 1 END)"); - } - - public void testBooleanToInteger() throws Exception { - helpTest(LANG_FACTORY.createLiteral(Boolean.FALSE, Boolean.class), "integer", "(CASE WHEN 0 IN ( '0', 'FALSE') THEN 0 WHEN 0 IS NOT NULL THEN 1 END)"); - } - - public void testBooleanToLong() throws Exception { - helpTest(LANG_FACTORY.createLiteral(Boolean.FALSE, Boolean.class), "long", "(CASE WHEN 0 IN ( '0', 'FALSE') THEN 0 WHEN 0 IS NOT NULL THEN 1 END)"); - } - - public void testBooleanToBigInteger() throws Exception { - helpTest(LANG_FACTORY.createLiteral(Boolean.FALSE, Boolean.class), "biginteger", "(CASE WHEN 0 IN ( '0', 'FALSE') THEN 0 WHEN 0 IS NOT NULL THEN 1 END)"); - } - - public void testBooleanToFloat() throws Exception { - helpTest(LANG_FACTORY.createLiteral(Boolean.FALSE, Boolean.class), "float", "(CASE WHEN 0 IN ( '0', 'FALSE') THEN 0 WHEN 0 IS NOT NULL THEN 1 END)"); - } - - public void testBooleanToDouble() throws Exception { - helpTest(LANG_FACTORY.createLiteral(Boolean.FALSE, Boolean.class), "double", "(CASE WHEN 0 IN ( '0', 'FALSE') THEN 0 WHEN 0 IS NOT NULL THEN 1 END)"); - } - - public void testBooleanToBigDecimal() throws Exception { - helpTest(LANG_FACTORY.createLiteral(Boolean.FALSE, Boolean.class), "bigdecimal", "(CASE WHEN 0 IN ( '0', 'FALSE') THEN 0 WHEN 0 IS NOT NULL THEN 1 END)"); - } - - // Source = BYTE - - public void testByteToString() throws Exception { - helpTest(LANG_FACTORY.createLiteral(new Byte((byte)1), Byte.class), "string", "cast(1 AS varchar(4000))"); - } - - public void testByteToBoolean() throws Exception { - helpTest(LANG_FACTORY.createLiteral(new Byte((byte)1), Byte.class), "boolean", "CASE WHEN 1 = 0 THEN '0' WHEN 1 IS NOT NULL THEN '1' END"); - } - -// public void testByteToShort() throws Exception { -// helpTest(LANG_FACTORY.createLiteral(new Byte((byte)1), Byte.class), "short", "1"); -// } -// -// public void testByteToInteger() throws Exception { -// helpTest(LANG_FACTORY.createLiteral(new Byte((byte)1), Byte.class), "integer", "integer(1)"); -// } -// -// public void testByteToLong() throws Exception { -// helpTest(LANG_FACTORY.createLiteral(new Byte((byte)1), Byte.class), "long", "bigint(1)"); -// } -// -// public void testByteToBigInteger() throws Exception { -// helpTest(LANG_FACTORY.createLiteral(new Byte((byte)1), Byte.class), "biginteger", "cast(1 AS numeric(31,0))"); -// } -// -// public void testByteToFloat() throws Exception { -// helpTest(LANG_FACTORY.createLiteral(new Byte((byte)1), Byte.class), "float", "cast(1 AS real)"); -// } -// -// public void testByteToDouble() throws Exception { -// helpTest(LANG_FACTORY.createLiteral(new Byte((byte)1), Byte.class), "double", "double(1)"); -// } -// -// public void testByteToBigDecimal() throws Exception { -// helpTest(LANG_FACTORY.createLiteral(new Byte((byte)1), Byte.class), "bigdecimal", "cast(1 AS numeric(31,12))"); -// } - - // Source = SHORT - - public void testShortToString() throws Exception { - helpTest(LANG_FACTORY.createLiteral(new Short((short)1), Short.class), "string", "cast(1 AS varchar(4000))"); - } - - public void testShortToBoolean() throws Exception { - helpTest(LANG_FACTORY.createLiteral(new Short((short)1), Short.class), "boolean", "CASE WHEN 1 = 0 THEN '0' WHEN 1 IS NOT NULL THEN '1' END"); - } - -// public void testShortToByte() throws Exception { -// helpTest(LANG_FACTORY.createLiteral(new Short((short)1), Short.class), "byte", "1"); -// } -// -// public void testShortToInteger() throws Exception { -// helpTest(LANG_FACTORY.createLiteral(new Short((short)1), Short.class), "integer", "integer(1)"); -// } -// -// public void testShortToLong() throws Exception { -// helpTest(LANG_FACTORY.createLiteral(new Short((short)1), Short.class), "long", "bigint(1)"); -// } -// -// public void testShortToBigInteger() throws Exception { -// helpTest(LANG_FACTORY.createLiteral(new Short((short)1), Short.class), "biginteger", "cast(1 AS numeric(31,0))"); -// } -// -// public void testShortToFloat() throws Exception { -// helpTest(LANG_FACTORY.createLiteral(new Short((short)1), Short.class), "float", "cast(1 AS real)"); -// } -// -// public void testShortToDouble() throws Exception { -// helpTest(LANG_FACTORY.createLiteral(new Short((short)1), Short.class), "double", "double(1)"); -// } -// -// public void testShortToBigDecimal() throws Exception { -// helpTest(LANG_FACTORY.createLiteral(new Short((short)1), Short.class), "bigdecimal", "cast(1 AS numeric(31,12))"); -// } - - // Source = INTEGER - - public void testIntegerToString() throws Exception { - helpTest(LANG_FACTORY.createLiteral(new Integer(1), Integer.class), "string", "cast(1 AS varchar(4000))"); - } - - public void testIntegerToBoolean() throws Exception { - helpTest(LANG_FACTORY.createLiteral(new Integer(1), Integer.class), "boolean", "CASE WHEN 1 = 0 THEN '0' WHEN 1 IS NOT NULL THEN '1' END"); - } - -// public void testIntegerToByte() throws Exception { -// helpTest(LANG_FACTORY.createLiteral(new Integer(1), Integer.class), "byte", "smallint(1)"); -// } -// -// public void testIntegerToShort() throws Exception { -// helpTest(LANG_FACTORY.createLiteral(new Integer(1), Integer.class), "short", "smallint(1)"); -// } -// -// public void testIntegerToLong() throws Exception { -// helpTest(LANG_FACTORY.createLiteral(new Integer(1), Integer.class), "long", "bigint(1)"); -// } -// -// public void testIntegerToBigInteger() throws Exception { -// helpTest(LANG_FACTORY.createLiteral(new Integer(1), Integer.class), "biginteger", "cast(1 AS numeric(31,0))"); -// } -// -// public void testIntegerToFloat() throws Exception { -// helpTest(LANG_FACTORY.createLiteral(new Integer(1), Integer.class), "float", "cast(1 AS real)"); -// } -// -// public void testIntegerToDouble() throws Exception { -// helpTest(LANG_FACTORY.createLiteral(new Integer(1), Integer.class), "double", "double(1)"); -// } -// -// public void testIntegerToBigDecimal() throws Exception { -// helpTest(LANG_FACTORY.createLiteral(new Integer(1), Integer.class), "bigdecimal", "cast(1 AS numeric(31,12))"); -// } - - // Source = LONG - - public void testLongToString() throws Exception { - helpTest(LANG_FACTORY.createLiteral(new Long(1), Long.class), "string", "cast(1 AS varchar(4000))"); - } - - public void testLongToBoolean() throws Exception { - helpTest(LANG_FACTORY.createLiteral(new Long(1), Long.class), "boolean", "CASE WHEN 1 = 0 THEN '0' WHEN 1 IS NOT NULL THEN '1' END"); - } - -// public void testLongToByte() throws Exception { -// helpTest(LANG_FACTORY.createLiteral(new Long(1), Long.class), "byte", "smallint(1)"); -// } -// -// public void testLongToShort() throws Exception { -// helpTest(LANG_FACTORY.createLiteral(new Long(1), Long.class), "short", "smallint(1)"); -// } -// -// public void testLongToInteger() throws Exception { -// helpTest(LANG_FACTORY.createLiteral(new Long(1), Long.class), "integer", "integer(1)"); -// } -// -// public void testLongToBigInteger() throws Exception { -// helpTest(LANG_FACTORY.createLiteral(new Long(1), Long.class), "biginteger", "cast(1 AS numeric(31,0))"); -// } -// -// public void testLongToFloat() throws Exception { -// helpTest(LANG_FACTORY.createLiteral(new Long(1), Long.class), "float", "cast(1 AS real)"); -// } -// -// public void testLongToDouble() throws Exception { -// helpTest(LANG_FACTORY.createLiteral(new Long(1), Long.class), "double", "double(1)"); -// } -// -// public void testLongToBigDecimal() throws Exception { -// helpTest(LANG_FACTORY.createLiteral(new Long(1), Long.class), "bigdecimal", "cast(1 AS numeric(31,12))"); -// } - - // Source = BIGINTEGER - - public void testBigIntegerToString() throws Exception { - helpTest(LANG_FACTORY.createLiteral(new BigInteger("1"), BigInteger.class), "string", "cast(1 AS varchar(4000))"); - } - - public void testBigIntegerToBoolean() throws Exception { - helpTest(LANG_FACTORY.createLiteral(new BigInteger("1"), BigInteger.class), "boolean", "CASE WHEN 1 = 0 THEN '0' WHEN 1 IS NOT NULL THEN '1' END"); - } - -// public void testBigIntegerToByte() throws Exception { -// helpTest(LANG_FACTORY.createLiteral(new BigInteger("1"), BigInteger.class), "byte", "smallint(1)"); -// } -// -// public void testBigIntegerToShort() throws Exception { -// helpTest(LANG_FACTORY.createLiteral(new BigInteger("1"), BigInteger.class), "short", "smallint(1)"); -// } -// -// public void testBigIntegerToInteger() throws Exception { -// helpTest(LANG_FACTORY.createLiteral(new BigInteger("1"), BigInteger.class), "integer", "integer(1)"); -// } -// -// public void testBigIntegerToLong() throws Exception { -// helpTest(LANG_FACTORY.createLiteral(new BigInteger("1"), BigInteger.class), "long", "bigint(1)"); -// } -// -// public void testBigIntegerToFloat() throws Exception { -// helpTest(LANG_FACTORY.createLiteral(new BigInteger("1"), BigInteger.class), "float", "cast(1 AS real)"); -// } -// -// public void testBigIntegerToDouble() throws Exception { -// helpTest(LANG_FACTORY.createLiteral(new BigInteger("1"), BigInteger.class), "double", "double(1)"); -// } -// -// public void testBigIntegerToBigDecimal() throws Exception { -// helpTest(LANG_FACTORY.createLiteral(new BigInteger("1"), BigInteger.class), "bigdecimal", "cast(1 AS numeric(31,12))"); -// } - - // Source = FLOAT - - public void testFloatToString() throws Exception { - helpTest(LANG_FACTORY.createLiteral(new Float(1.2f), Float.class), "string", "cast(1.2 AS varchar(4000))"); - } - - public void testFloatToBoolean() throws Exception { - helpTest(LANG_FACTORY.createLiteral(new Float(1.2f), Float.class), "boolean", "CASE WHEN 1.2 = 0 THEN '0' WHEN 1.2 IS NOT NULL THEN '1' END"); - } - -// public void testFloatToByte() throws Exception { -// helpTest(LANG_FACTORY.createLiteral(new Float(1.2f), Float.class), "byte", "smallint(1.2)"); -// } -// -// public void testFloatToShort() throws Exception { -// helpTest(LANG_FACTORY.createLiteral(new Float(1.2f), Float.class), "short", "smallint(1.2)"); -// } -// -// public void testFloatToInteger() throws Exception { -// helpTest(LANG_FACTORY.createLiteral(new Float(1.2f), Float.class), "integer", "integer(1.2)"); -// } -// -// public void testFloatToLong() throws Exception { -// helpTest(LANG_FACTORY.createLiteral(new Float(1.2f), Float.class), "long", "bigint(1.2)"); -// } -// -// public void testFloatToBigInteger() throws Exception { -// helpTest(LANG_FACTORY.createLiteral(new Float(1.2f), Float.class), "biginteger", "cast(1.2 AS numeric(31,0))"); -// } -// -// public void testFloatToDouble() throws Exception { -// helpTest(LANG_FACTORY.createLiteral(new Float(1.2f), Float.class), "double", "double(1.2)"); -// } -// -// public void testFloatToBigDecimal() throws Exception { -// helpTest(LANG_FACTORY.createLiteral(new Float(1.2f), Float.class), "bigdecimal", "cast(1.2 AS numeric(31,12))"); -// } - - // Source = DOUBLE - - public void testDoubleToString() throws Exception { - helpTest(LANG_FACTORY.createLiteral(new Double(1.2), Double.class), "string", "cast(1.2 AS varchar(4000))"); - } - - public void testDoubleToBoolean() throws Exception { - helpTest(LANG_FACTORY.createLiteral(new Double(1.2), Double.class), "boolean", "CASE WHEN 1.2 = 0 THEN '0' WHEN 1.2 IS NOT NULL THEN '1' END"); - } - -// public void testDoubleToByte() throws Exception { -// helpTest(LANG_FACTORY.createLiteral(new Double(1.2), Double.class), "byte", "smallint(1.2)"); -// } -// -// public void testDoubleToShort() throws Exception { -// helpTest(LANG_FACTORY.createLiteral(new Double(1.2), Double.class), "short", "smallint(1.2)"); -// } -// -// public void testDoubleToInteger() throws Exception { -// helpTest(LANG_FACTORY.createLiteral(new Double(1.2), Double.class), "integer", "integer(1.2)"); -// } -// -// public void testDoubleToLong() throws Exception { -// helpTest(LANG_FACTORY.createLiteral(new Double(1.2), Double.class), "long", "bigint(1.2)"); -// } -// -// public void testDoubleToBigInteger() throws Exception { -// helpTest(LANG_FACTORY.createLiteral(new Double(1.2), Double.class), "biginteger", "cast(1.2 AS numeric(31,0))"); -// } -// -// public void testDoubleToFloat() throws Exception { -// helpTest(LANG_FACTORY.createLiteral(new Double(1.2), Double.class), "float", "cast(1.2 AS real)"); -// } -// -// public void testDoubleToBigDecimal() throws Exception { -// helpTest(LANG_FACTORY.createLiteral(new Double(1.2), Double.class), "bigdecimal", "cast(1.2 AS numeric(31,12))"); -// } - - // Source = BIGDECIMAL - - public void testBigDecimalToString() throws Exception { - helpTest(LANG_FACTORY.createLiteral(new BigDecimal("1.0"), BigDecimal.class), "string", "cast(1.0 AS varchar(4000))"); - } - - public void testBigDecimalToBoolean() throws Exception { - helpTest(LANG_FACTORY.createLiteral(new BigDecimal("1.0"), BigDecimal.class), "boolean", "CASE WHEN 1.0 = 0 THEN '0' WHEN 1.0 IS NOT NULL THEN '1' END"); - } - -// public void testBigDecimalToByte() throws Exception { -// helpTest(LANG_FACTORY.createLiteral(new BigDecimal("1.0"), BigDecimal.class), "byte", "smallint(1.0)"); -// } -// -// public void testBigDecimalToShort() throws Exception { -// helpTest(LANG_FACTORY.createLiteral(new BigDecimal("1.0"), BigDecimal.class), "short", "smallint(1.0)"); -// } -// -// public void testBigDecimalToInteger() throws Exception { -// helpTest(LANG_FACTORY.createLiteral(new BigDecimal("1.0"), BigDecimal.class), "integer", "integer(1.0)"); -// } -// -// public void testBigDecimalToLong() throws Exception { -// helpTest(LANG_FACTORY.createLiteral(new BigDecimal("1.0"), BigDecimal.class), "long", "bigint(1.0)"); -// } -// -// public void testBigDecimalToBigInteger() throws Exception { -// helpTest(LANG_FACTORY.createLiteral(new BigDecimal("1.0"), BigDecimal.class), "biginteger", "cast(1.0 AS numeric(31,0))"); -// } -// -// public void testBigDecimalToFloat() throws Exception { -// helpTest(LANG_FACTORY.createLiteral(new BigDecimal("1.0"), BigDecimal.class), "float", "cast(1.0 AS real)"); -// } -// -// public void testBigDecimalToDouble() throws Exception { -// helpTest(LANG_FACTORY.createLiteral(new BigDecimal("1.0"), BigDecimal.class), "double", "double(1.0)"); -// } - -// // Source = DATE -// -// public void testDateToString() throws Exception { -// helpTest(LANG_FACTORY.createLiteral(TimestampUtil.createDate(103, 10, 1), java.sql.Date.class), "string", "char({d '2003-11-01'})"); -// } -// -// public void testDateToTimestamp() throws Exception { -// helpTest(LANG_FACTORY.createLiteral(TimestampUtil.createDate(103, 10, 1), java.sql.Date.class), "timestamp", "timestamp({d '2003-11-01'}, '00:00:00')"); -// } -// -// // Source = TIME -// -// public void testTimeToString() throws Exception { -// helpTest(LANG_FACTORY.createLiteral(TimestampUtil.createTime(23, 59, 59), java.sql.Time.class), "string", "char({t '23:59:59'})"); -// } -// -// public void testTimeToTimestamp() throws Exception { -// helpTest(LANG_FACTORY.createLiteral(TimestampUtil.createTime(23, 59, 59), java.sql.Time.class), "timestamp", "timestamp('1970-01-01', {t '23:59:59'})"); -// } -// -// // Source = TIMESTAMP -// -// public void testTimestampToString() throws Exception { -// Timestamp ts = TimestampUtil.createTimestamp(103, 10, 1, 12, 5, 2, 0); -// helpTest(LANG_FACTORY.createLiteral(ts, Timestamp.class), "string", "char({ts '2003-11-01 12:05:02.0'})"); -// } -// -// public void testTimestampToDate() throws Exception { -// Timestamp ts = TimestampUtil.createTimestamp(103, 10, 1, 12, 5, 2, 0); -// helpTest(LANG_FACTORY.createLiteral(ts, Timestamp.class), "date", "date({ts '2003-11-01 12:05:02.0'})"); -// } -// -// public void testTimestampToTime() throws Exception { -// Timestamp ts = TimestampUtil.createTimestamp(103, 10, 1, 12, 5, 2, 0); -// helpTest(LANG_FACTORY.createLiteral(ts, Timestamp.class), "time", "time({ts '2003-11-01 12:05:02.0'})"); -// } - -} Copied: trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/netezza/TestNetezzaConvertModifier.java (from rev 3365, branches/7.4.x/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/netezza/TestNetezzaConvertModifier.java) =================================================================== --- trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/netezza/TestNetezzaConvertModifier.java (rev 0) +++ trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/netezza/TestNetezzaConvertModifier.java 2011-08-10 18:01:12 UTC (rev 3366) @@ -0,0 +1,509 @@ +/* + * 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.math.BigDecimal; +import java.math.BigInteger; +import java.util.Arrays; + +import junit.framework.TestCase; + +import org.teiid.language.Expression; +import org.teiid.language.Function; +import org.teiid.language.LanguageFactory; +import org.teiid.translator.TypeFacility; +import org.teiid.translator.jdbc.SQLConversionVisitor; + + +/** + */ +public class TestNetezzaConvertModifier extends TestCase { + + private static final LanguageFactory LANG_FACTORY = new LanguageFactory(); + + public TestNetezzaConvertModifier(String name) { + super(name); + } + + public String helpGetString(Expression expr) throws Exception { + NetezzaExecutionFactory trans = new NetezzaExecutionFactory(); + trans.start(); + SQLConversionVisitor sqlVisitor = trans.getSQLConversionVisitor(); + sqlVisitor.append(expr); + + return sqlVisitor.toString(); + } + + public void helpTest(Expression srcExpression, String tgtType, String expectedExpression) throws Exception { + Function func = LANG_FACTORY.createFunction("convert", //$NON-NLS-1$ + Arrays.asList( + srcExpression, + LANG_FACTORY.createLiteral(tgtType, String.class)), + TypeFacility.getDataTypeClass(tgtType)); + + assertEquals("Error converting from " + srcExpression.getType() + " to " + tgtType, + expectedExpression, helpGetString(func)); + } + + + + + // Source = STRING + public void testStringToChar() throws Exception { + helpTest(LANG_FACTORY.createLiteral("5", String.class), "char", "cast('5' AS char(1))"); + } + + public void testStringToBoolean() throws Exception { + helpTest(LANG_FACTORY.createLiteral("5", String.class), "boolean", "CASE WHEN '5' IN ('false', '0') THEN '0' WHEN '5' IS NOT NULL THEN '1' END"); + } + + public void testStringToByte() throws Exception { + helpTest(LANG_FACTORY.createLiteral("5", String.class), "byte", "cast('5' AS byteint)"); + } + + public void testStringToShort() throws Exception { + helpTest(LANG_FACTORY.createLiteral("5", String.class), "short", "cast('5' AS smallint)"); + } + + public void testStringToInteger() throws Exception { + helpTest(LANG_FACTORY.createLiteral("5", String.class), "integer", "cast('5' AS integer)"); + } + + public void testStringToLong() throws Exception { + helpTest(LANG_FACTORY.createLiteral("5", String.class), "long", "cast('5' AS bigint)"); + } + + public void testStringToBigInteger() throws Exception { + helpTest(LANG_FACTORY.createLiteral("5", String.class), "biginteger", "cast('5' AS numeric(38))"); //$NON-NLS-1$//$NON-NLS-2$ //$NON-NLS-3$ + } + + public void testStringToFloat() throws Exception { + helpTest(LANG_FACTORY.createLiteral("5", String.class), "float", "cast('5' AS float)"); + } + + public void testStringToDouble() throws Exception { + helpTest(LANG_FACTORY.createLiteral("5", String.class), "double", "cast('5' AS double)"); + } + + public void testStringToDate() throws Exception { + helpTest(LANG_FACTORY.createLiteral("2004-06-29", String.class), "date", "to_date('2004-06-29', 'YYYY-MM-DD')"); + } + + public void testStringToTime() throws Exception { + helpTest(LANG_FACTORY.createLiteral("23:59:59", String.class), "time", "to_timestamp('23:59:59', 'HH24:MI:SS')"); + } + + public void testStringToTimestamp() throws Exception { + helpTest(LANG_FACTORY.createLiteral("2004-06-29 23:59:59.987", String.class), "timestamp", "to_timestamp('2004-06-29 23:59:59.987', 'YYYY-MM-DD HH24:MI:SS.MS')"); + } + + public void testStringToBigDecimal() throws Exception { + helpTest(LANG_FACTORY.createLiteral("5", String.class), "bigdecimal", "cast('5' AS numeric(38,18))"); + } + + // Source = CHAR + + public void testCharToString() throws Exception { + helpTest(LANG_FACTORY.createLiteral(new Character('5'), Character.class), "string", "'5'"); + } + + // Source = BOOLEAN + + public void testBooleanToString() throws Exception { + helpTest(LANG_FACTORY.createLiteral(Boolean.TRUE, Boolean.class), "string", "CASE WHEN 1 = '0' THEN 'false' WHEN 1 IS NOT NULL THEN 'true' END"); + } + + public void testBooleanToByte() throws Exception { + helpTest(LANG_FACTORY.createLiteral(Boolean.FALSE, Boolean.class), "byte", "(CASE WHEN 0 IN ( '0', 'FALSE') THEN 0 WHEN 0 IS NOT NULL THEN 1 END)"); + } + + public void testBooleanToShort() throws Exception { + helpTest(LANG_FACTORY.createLiteral(Boolean.FALSE, Boolean.class), "short", "(CASE WHEN 0 IN ( '0', 'FALSE') THEN 0 WHEN 0 IS NOT NULL THEN 1 END)"); + } + + public void testBooleanToInteger() throws Exception { + helpTest(LANG_FACTORY.createLiteral(Boolean.FALSE, Boolean.class), "integer", "(CASE WHEN 0 IN ( '0', 'FALSE') THEN 0 WHEN 0 IS NOT NULL THEN 1 END)"); + } + + public void testBooleanToLong() throws Exception { + helpTest(LANG_FACTORY.createLiteral(Boolean.FALSE, Boolean.class), "long", "(CASE WHEN 0 IN ( '0', 'FALSE') THEN 0 WHEN 0 IS NOT NULL THEN 1 END)"); + } + + public void testBooleanToBigInteger() throws Exception { + helpTest(LANG_FACTORY.createLiteral(Boolean.FALSE, Boolean.class), "biginteger", "(CASE WHEN 0 IN ( '0', 'FALSE') THEN 0 WHEN 0 IS NOT NULL THEN 1 END)"); + } + + public void testBooleanToFloat() throws Exception { + helpTest(LANG_FACTORY.createLiteral(Boolean.FALSE, Boolean.class), "float", "(CASE WHEN 0 IN ( '0', 'FALSE') THEN 0 WHEN 0 IS NOT NULL THEN 1 END)"); + } + + public void testBooleanToDouble() throws Exception { + helpTest(LANG_FACTORY.createLiteral(Boolean.FALSE, Boolean.class), "double", "(CASE WHEN 0 IN ( '0', 'FALSE') THEN 0 WHEN 0 IS NOT NULL THEN 1 END)"); + } + + public void testBooleanToBigDecimal() throws Exception { + helpTest(LANG_FACTORY.createLiteral(Boolean.FALSE, Boolean.class), "bigdecimal", "(CASE WHEN 0 IN ( '0', 'FALSE') THEN 0 WHEN 0 IS NOT NULL THEN 1 END)"); + } + + // Source = BYTE + + public void testByteToString() throws Exception { + helpTest(LANG_FACTORY.createLiteral(new Byte((byte)1), Byte.class), "string", "cast(1 AS varchar(4000))"); + } + + public void testByteToBoolean() throws Exception { + helpTest(LANG_FACTORY.createLiteral(new Byte((byte)1), Byte.class), "boolean", "CASE WHEN 1 = 0 THEN '0' WHEN 1 IS NOT NULL THEN '1' END"); + } + +// public void testByteToShort() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new Byte((byte)1), Byte.class), "short", "1"); +// } +// +// public void testByteToInteger() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new Byte((byte)1), Byte.class), "integer", "integer(1)"); +// } +// +// public void testByteToLong() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new Byte((byte)1), Byte.class), "long", "bigint(1)"); +// } +// +// public void testByteToBigInteger() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new Byte((byte)1), Byte.class), "biginteger", "cast(1 AS numeric(31,0))"); +// } +// +// public void testByteToFloat() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new Byte((byte)1), Byte.class), "float", "cast(1 AS real)"); +// } +// +// public void testByteToDouble() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new Byte((byte)1), Byte.class), "double", "double(1)"); +// } +// +// public void testByteToBigDecimal() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new Byte((byte)1), Byte.class), "bigdecimal", "cast(1 AS numeric(31,12))"); +// } + + // Source = SHORT + + public void testShortToString() throws Exception { + helpTest(LANG_FACTORY.createLiteral(new Short((short)1), Short.class), "string", "cast(1 AS varchar(4000))"); + } + + public void testShortToBoolean() throws Exception { + helpTest(LANG_FACTORY.createLiteral(new Short((short)1), Short.class), "boolean", "CASE WHEN 1 = 0 THEN '0' WHEN 1 IS NOT NULL THEN '1' END"); + } + +// public void testShortToByte() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new Short((short)1), Short.class), "byte", "1"); +// } +// +// public void testShortToInteger() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new Short((short)1), Short.class), "integer", "integer(1)"); +// } +// +// public void testShortToLong() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new Short((short)1), Short.class), "long", "bigint(1)"); +// } +// +// public void testShortToBigInteger() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new Short((short)1), Short.class), "biginteger", "cast(1 AS numeric(31,0))"); +// } +// +// public void testShortToFloat() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new Short((short)1), Short.class), "float", "cast(1 AS real)"); +// } +// +// public void testShortToDouble() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new Short((short)1), Short.class), "double", "double(1)"); +// } +// +// public void testShortToBigDecimal() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new Short((short)1), Short.class), "bigdecimal", "cast(1 AS numeric(31,12))"); +// } + + // Source = INTEGER + + public void testIntegerToString() throws Exception { + helpTest(LANG_FACTORY.createLiteral(new Integer(1), Integer.class), "string", "cast(1 AS varchar(4000))"); + } + + public void testIntegerToBoolean() throws Exception { + helpTest(LANG_FACTORY.createLiteral(new Integer(1), Integer.class), "boolean", "CASE WHEN 1 = 0 THEN '0' WHEN 1 IS NOT NULL THEN '1' END"); + } + +// public void testIntegerToByte() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new Integer(1), Integer.class), "byte", "smallint(1)"); +// } +// +// public void testIntegerToShort() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new Integer(1), Integer.class), "short", "smallint(1)"); +// } +// +// public void testIntegerToLong() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new Integer(1), Integer.class), "long", "bigint(1)"); +// } +// +// public void testIntegerToBigInteger() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new Integer(1), Integer.class), "biginteger", "cast(1 AS numeric(31,0))"); +// } +// +// public void testIntegerToFloat() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new Integer(1), Integer.class), "float", "cast(1 AS real)"); +// } +// +// public void testIntegerToDouble() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new Integer(1), Integer.class), "double", "double(1)"); +// } +// +// public void testIntegerToBigDecimal() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new Integer(1), Integer.class), "bigdecimal", "cast(1 AS numeric(31,12))"); +// } + + // Source = LONG + + public void testLongToString() throws Exception { + helpTest(LANG_FACTORY.createLiteral(new Long(1), Long.class), "string", "cast(1 AS varchar(4000))"); + } + + public void testLongToBoolean() throws Exception { + helpTest(LANG_FACTORY.createLiteral(new Long(1), Long.class), "boolean", "CASE WHEN 1 = 0 THEN '0' WHEN 1 IS NOT NULL THEN '1' END"); + } + +// public void testLongToByte() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new Long(1), Long.class), "byte", "smallint(1)"); +// } +// +// public void testLongToShort() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new Long(1), Long.class), "short", "smallint(1)"); +// } +// +// public void testLongToInteger() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new Long(1), Long.class), "integer", "integer(1)"); +// } +// +// public void testLongToBigInteger() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new Long(1), Long.class), "biginteger", "cast(1 AS numeric(31,0))"); +// } +// +// public void testLongToFloat() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new Long(1), Long.class), "float", "cast(1 AS real)"); +// } +// +// public void testLongToDouble() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new Long(1), Long.class), "double", "double(1)"); +// } +// +// public void testLongToBigDecimal() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new Long(1), Long.class), "bigdecimal", "cast(1 AS numeric(31,12))"); +// } + + // Source = BIGINTEGER + + public void testBigIntegerToString() throws Exception { + helpTest(LANG_FACTORY.createLiteral(new BigInteger("1"), BigInteger.class), "string", "cast(1 AS varchar(4000))"); + } + + public void testBigIntegerToBoolean() throws Exception { + helpTest(LANG_FACTORY.createLiteral(new BigInteger("1"), BigInteger.class), "boolean", "CASE WHEN 1 = 0 THEN '0' WHEN 1 IS NOT NULL THEN '1' END"); + } + +// public void testBigIntegerToByte() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new BigInteger("1"), BigInteger.class), "byte", "smallint(1)"); +// } +// +// public void testBigIntegerToShort() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new BigInteger("1"), BigInteger.class), "short", "smallint(1)"); +// } +// +// public void testBigIntegerToInteger() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new BigInteger("1"), BigInteger.class), "integer", "integer(1)"); +// } +// +// public void testBigIntegerToLong() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new BigInteger("1"), BigInteger.class), "long", "bigint(1)"); +// } +// +// public void testBigIntegerToFloat() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new BigInteger("1"), BigInteger.class), "float", "cast(1 AS real)"); +// } +// +// public void testBigIntegerToDouble() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new BigInteger("1"), BigInteger.class), "double", "double(1)"); +// } +// +// public void testBigIntegerToBigDecimal() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new BigInteger("1"), BigInteger.class), "bigdecimal", "cast(1 AS numeric(31,12))"); +// } + + // Source = FLOAT + + public void testFloatToString() throws Exception { + helpTest(LANG_FACTORY.createLiteral(new Float(1.2f), Float.class), "string", "cast(1.2 AS varchar(4000))"); + } + + public void testFloatToBoolean() throws Exception { + helpTest(LANG_FACTORY.createLiteral(new Float(1.2f), Float.class), "boolean", "CASE WHEN 1.2 = 0 THEN '0' WHEN 1.2 IS NOT NULL THEN '1' END"); + } + +// public void testFloatToByte() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new Float(1.2f), Float.class), "byte", "smallint(1.2)"); +// } +// +// public void testFloatToShort() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new Float(1.2f), Float.class), "short", "smallint(1.2)"); +// } +// +// public void testFloatToInteger() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new Float(1.2f), Float.class), "integer", "integer(1.2)"); +// } +// +// public void testFloatToLong() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new Float(1.2f), Float.class), "long", "bigint(1.2)"); +// } +// +// public void testFloatToBigInteger() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new Float(1.2f), Float.class), "biginteger", "cast(1.2 AS numeric(31,0))"); +// } +// +// public void testFloatToDouble() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new Float(1.2f), Float.class), "double", "double(1.2)"); +// } +// +// public void testFloatToBigDecimal() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new Float(1.2f), Float.class), "bigdecimal", "cast(1.2 AS numeric(31,12))"); +// } + + // Source = DOUBLE + + public void testDoubleToString() throws Exception { + helpTest(LANG_FACTORY.createLiteral(new Double(1.2), Double.class), "string", "cast(1.2 AS varchar(4000))"); + } + + public void testDoubleToBoolean() throws Exception { + helpTest(LANG_FACTORY.createLiteral(new Double(1.2), Double.class), "boolean", "CASE WHEN 1.2 = 0 THEN '0' WHEN 1.2 IS NOT NULL THEN '1' END"); + } + +// public void testDoubleToByte() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new Double(1.2), Double.class), "byte", "smallint(1.2)"); +// } +// +// public void testDoubleToShort() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new Double(1.2), Double.class), "short", "smallint(1.2)"); +// } +// +// public void testDoubleToInteger() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new Double(1.2), Double.class), "integer", "integer(1.2)"); +// } +// +// public void testDoubleToLong() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new Double(1.2), Double.class), "long", "bigint(1.2)"); +// } +// +// public void testDoubleToBigInteger() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new Double(1.2), Double.class), "biginteger", "cast(1.2 AS numeric(31,0))"); +// } +// +// public void testDoubleToFloat() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new Double(1.2), Double.class), "float", "cast(1.2 AS real)"); +// } +// +// public void testDoubleToBigDecimal() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new Double(1.2), Double.class), "bigdecimal", "cast(1.2 AS numeric(31,12))"); +// } + + // Source = BIGDECIMAL + + public void testBigDecimalToString() throws Exception { + helpTest(LANG_FACTORY.createLiteral(new BigDecimal("1.0"), BigDecimal.class), "string", "cast(1.0 AS varchar(4000))"); + } + + public void testBigDecimalToBoolean() throws Exception { + helpTest(LANG_FACTORY.createLiteral(new BigDecimal("1.0"), BigDecimal.class), "boolean", "CASE WHEN 1.0 = 0 THEN '0' WHEN 1.0 IS NOT NULL THEN '1' END"); + } + +// public void testBigDecimalToByte() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new BigDecimal("1.0"), BigDecimal.class), "byte", "smallint(1.0)"); +// } +// +// public void testBigDecimalToShort() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new BigDecimal("1.0"), BigDecimal.class), "short", "smallint(1.0)"); +// } +// +// public void testBigDecimalToInteger() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new BigDecimal("1.0"), BigDecimal.class), "integer", "integer(1.0)"); +// } +// +// public void testBigDecimalToLong() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new BigDecimal("1.0"), BigDecimal.class), "long", "bigint(1.0)"); +// } +// +// public void testBigDecimalToBigInteger() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new BigDecimal("1.0"), BigDecimal.class), "biginteger", "cast(1.0 AS numeric(31,0))"); +// } +// +// public void testBigDecimalToFloat() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new BigDecimal("1.0"), BigDecimal.class), "float", "cast(1.0 AS real)"); +// } +// +// public void testBigDecimalToDouble() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(new BigDecimal("1.0"), BigDecimal.class), "double", "double(1.0)"); +// } + +// // Source = DATE +// +// public void testDateToString() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(TimestampUtil.createDate(103, 10, 1), java.sql.Date.class), "string", "char({d '2003-11-01'})"); +// } +// +// public void testDateToTimestamp() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(TimestampUtil.createDate(103, 10, 1), java.sql.Date.class), "timestamp", "timestamp({d '2003-11-01'}, '00:00:00')"); +// } +// +// // Source = TIME +// +// public void testTimeToString() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(TimestampUtil.createTime(23, 59, 59), java.sql.Time.class), "string", "char({t '23:59:59'})"); +// } +// +// public void testTimeToTimestamp() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(TimestampUtil.createTime(23, 59, 59), java.sql.Time.class), "timestamp", "timestamp('1970-01-01', {t '23:59:59'})"); +// } +// +// // Source = TIMESTAMP +// +// public void testTimestampToString() throws Exception { +// Timestamp ts = TimestampUtil.createTimestamp(103, 10, 1, 12, 5, 2, 0); +// helpTest(LANG_FACTORY.createLiteral(ts, Timestamp.class), "string", "char({ts '2003-11-01 12:05:02.0'})"); +// } +// +// public void testTimestampToDate() throws Exception { +// Timestamp ts = TimestampUtil.createTimestamp(103, 10, 1, 12, 5, 2, 0); +// helpTest(LANG_FACTORY.createLiteral(ts, Timestamp.class), "date", "date({ts '2003-11-01 12:05:02.0'})"); +// } +// +// public void testTimestampToTime() throws Exception { +// Timestamp ts = TimestampUtil.createTimestamp(103, 10, 1, 12, 5, 2, 0); +// helpTest(LANG_FACTORY.createLiteral(ts, Timestamp.class), "time", "time({ts '2003-11-01 12:05:02.0'})"); +// } + +} Deleted: trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/netezza/TestNetezzaTranslatorCapabilities.java =================================================================== --- branches/7.4.x/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/netezza/TestNetezzaTranslatorCapabilities.java 2011-08-06 14:57:03 UTC (rev 3365) +++ trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/netezza/TestNetezzaTranslatorCapabilities.java 2011-08-10 18:01:12 UTC (rev 3366) @@ -1,394 +0,0 @@ -/* - * 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 junit.framework.TestCase; - -import org.junit.Test; -import org.mockito.Mockito; -import org.teiid.cdk.unittest.FakeTranslationFactory; -import org.teiid.dqp.internal.datamgr.FakeExecutionContextImpl; -import org.teiid.language.Command; -import org.teiid.translator.ExecutionContext; -import org.teiid.translator.TranslatorException; -import org.teiid.translator.jdbc.TranslatedCommand; -import org.teiid.translator.jdbc.TranslationHelper; - -public class TestNetezzaTranslatorCapabilities extends TestCase { - - private static NetezzaExecutionFactory TRANSLATOR; - private static ExecutionContext EMPTY_CONTEXT = new FakeExecutionContextImpl(); - - static { - try { - TRANSLATOR = new NetezzaExecutionFactory(); - TRANSLATOR.start(); - } catch(TranslatorException e) { - e.printStackTrace(); - } - } - - - private String getTestBQTVDB() { - return TranslationHelper.BQT_VDB; - } - - public void helpTestVisitor(String input, String expectedOutput) throws TranslatorException { - // Convert from sql to objects - Command obj = FakeTranslationFactory.getInstance().getBQTTranslationUtility().parseCommand(input); - - TranslatedCommand tc = new TranslatedCommand(Mockito.mock(ExecutionContext.class), TRANSLATOR); - tc.translateCommand(obj); - - - // Check stuff - assertEquals("Did not get correct sql", expectedOutput, tc.getSql()); - } - - - /////////BASIC TEST CASES FOR CAPABILITIES///////////// - ///////////////////////////////////////////////// - @Test - public void testRowLimit() throws Exception { - String input = "select intkey from bqt1.smalla limit 100"; - String output = "SELECT SmallA.IntKey FROM SmallA LIMIT 100"; - - helpTestVisitor( - input, - output); - - } - @Test - public void testSelectDistinct() throws Exception { - String input = "select distinct intkey from bqt1.smalla limit 100"; - String output = "SELECT DISTINCT SmallA.IntKey FROM SmallA LIMIT 100"; - - helpTestVisitor( - input, - output); - } - @Test - public void testSelectExpression() throws Exception { - String input = "select intkey, intkey + longnum / 2 as test from bqt1.smalla"; - String output = "SELECT SmallA.IntKey, (SmallA.IntKey + (SmallA.LongNum / 2)) AS test FROM SmallA"; - - helpTestVisitor( - input, - output); - } - - public void testBetweenCriteria() throws Exception { - String input = "select intkey, intnum from bqt1.smalla where intnum BETWEEN 2 AND 10"; - String output = "SELECT SmallA.IntKey, SmallA.IntNum FROM SmallA WHERE SmallA.IntNum >= 2 AND SmallA.IntNum <= 10"; - - helpTestVisitor( - input, - output); - } - public void testCompareCriteriaEquals() throws Exception { - String input = "select intkey, intnum from bqt1.smalla where intnum = 10"; - String output = "SELECT SmallA.IntKey, SmallA.IntNum FROM SmallA WHERE SmallA.IntNum = 10"; - - helpTestVisitor( - input, - output); - } - public void testCompareCriteriaOrdered() throws Exception { - String input = "select intkey, intnum from bqt1.smalla where intnum < 10"; - String output = "SELECT SmallA.IntKey, SmallA.IntNum FROM SmallA WHERE SmallA.IntNum < 10"; - - helpTestVisitor( - input, - output); - } - public void testLikeCriteria() throws Exception { - String input = "select intkey, intnum from bqt1.smalla where stringkey like '4%'"; - String output = "SELECT SmallA.IntKey, SmallA.IntNum FROM SmallA WHERE SmallA.StringKey LIKE '4%'"; - - helpTestVisitor( - input, - output); - } - public void testLikeWithEscapeCriteria() throws Exception { - String input = "select intkey, intnum from bqt1.smalla where stringkey like '4\\%'"; - String output = "SELECT SmallA.IntKey, SmallA.IntNum FROM SmallA WHERE SmallA.StringKey LIKE '4\\%'"; - - helpTestVisitor( - input, - output); - } - - public void testInCriteria() throws Exception { - String input = "select intkey, intnum from bqt1.smalla where stringkey IN ('10', '11', '12')"; - String output = "SELECT SmallA.IntKey, SmallA.IntNum FROM SmallA WHERE SmallA.StringKey IN ('10', '11', '12')"; - - helpTestVisitor( - input, - output); - } - public void testInCriteriaSubQuery() throws Exception { - String input = "select intkey, intnum from bqt1.smalla where stringkey IN (select stringkey from bqt1.smalla where intkey < 10)"; - String output = "SELECT SmallA.IntKey, SmallA.IntNum FROM SmallA WHERE SmallA.StringKey IN (SELECT SmallA.StringKey FROM SmallA WHERE SmallA.IntKey < 10)"; - - helpTestVisitor( - input, - output); - } - - public void testIsNullCriteria() throws Exception { - String input = "select intkey, intnum from bqt1.smalla where intnum IS NULL"; - String output = "SELECT SmallA.IntKey, SmallA.IntNum FROM SmallA WHERE SmallA.IntNum IS NULL"; - - helpTestVisitor( - input, - output); - } - public void testOrCriteria() throws Exception { - String input = "select intkey, intnum from bqt1.smalla where intnum < 2 OR intnum > 10"; - String output = "SELECT SmallA.IntKey, SmallA.IntNum FROM SmallA WHERE SmallA.IntNum < 2 OR SmallA.IntNum > 10"; - - helpTestVisitor( - input, - output); - } - @Test public void testIsNotNullCriteria() throws Exception { - String input = "select intkey, intnum from bqt1.smalla where intnum IS NOT NULL"; - String output = "SELECT SmallA.IntKey, SmallA.IntNum FROM SmallA WHERE SmallA.IntNum IS NOT NULL"; - - helpTestVisitor( - input, - output); - } - - @Test public void testExistsCriteria() throws Exception { - String input = "select intkey, intnum from bqt1.smalla where exists (select intkey from bqt1.smallb)"; - String output = "SELECT SmallA.IntKey, SmallA.IntNum FROM SmallA WHERE EXISTS (SELECT SmallB.IntKey FROM SmallB LIMIT 1)"; - - helpTestVisitor( - input, - output); - } - @Test public void testHavingClauseCriteria() throws Exception { - String input = "SELECT INTKEY FROM BQT1.SMALLA GROUP BY INTKEY HAVING INTKEY = (SELECT INTKEY FROM BQT1.SMALLA WHERE STRINGKEY = 20)"; - String output = "SELECT SmallA.IntKey FROM SmallA GROUP BY SmallA.IntKey HAVING SmallA.IntKey = (SELECT SmallA.IntKey FROM SmallA WHERE SmallA.StringKey = '20' LIMIT 2)"; - - helpTestVisitor( - input, - output); - } - - @Test public void testScalarSubQuery() throws Exception { - String input = "select intkey, intnum from bqt1.smalla where intnum < (0.01 * (select sum(intnum) from bqt1.smalla ))"; - String output = "SELECT SmallA.IntKey, SmallA.IntNum FROM SmallA WHERE SmallA.IntNum < (0.010000000000000 * (SELECT SUM(SmallA.IntNum) FROM SmallA))"; - - helpTestVisitor( - input, - output); - } - - @Test public void testSimpleCaseExpression() throws Exception { - String input = "SELECT stringnum, intnum, CASE BOOLEANVALUE WHEN 'true' then 'true' WHEN false THEN 'FALSE' ELSE 'GOOD' END FROM bqt1.smalla;"; - String output = "SELECT SmallA.StringNum, SmallA.IntNum, CASE WHEN SmallA.BooleanValue = 1 THEN 'true' WHEN SmallA.BooleanValue = 0 THEN 'FALSE' ELSE 'GOOD' END FROM SmallA"; - - helpTestVisitor( - input, - output); - } - - @Test public void testSearchedCaseExpression() throws Exception { - String input = "SELECT AVG(CASE WHEN intnum > 10 THEN intnum ELSE intkey END) \"Average\" FROM bqt1.smalla"; - String output = "SELECT AVG(CASE WHEN SmallA.IntNum > 10 THEN SmallA.IntNum ELSE SmallA.IntKey END) AS Average FROM SmallA"; - - helpTestVisitor( - input, - output); - } - -// @Test public void testQuantifiedCompareSOMEorANY() throws Exception { -// String input = "SELECT INTKEY, BYTENUM FROM BQT1.SmallA WHERE BYTENUM = ANY (SELECT BYTENUM FROM BQT1.SmallA WHERE BYTENUM >= '-108')"; -// String output = "SELECT SmallA.IntKey, SmallA.ByteNum FROM SmallA WHERE SmallA.ByteNum = SOME (SELECT SmallA.ByteNum FROM SmallA WHERE SmallA.ByteNum >= -108)"; -// -// helpTestVisitor( -// input, -// output); -// } - - @Test public void testQuantifiedCompareALL() throws Exception { - String input = "SELECT INTKEY, STRINGKEY FROM BQT1.SMALLA WHERE STRINGKEY = ALL (SELECT STRINGKEY FROM BQT1.SMALLA WHERE INTKEY = 40)"; - String output = "SELECT SmallA.IntKey, SmallA.StringKey FROM SmallA WHERE SmallA.StringKey = ALL (SELECT SmallA.StringKey FROM SmallA WHERE SmallA.IntKey = 40)"; - - helpTestVisitor( - input, - output); - } - - @Test public void testSelfJoin() throws Exception { - String input = "SELECT x.intnum, y.intkey FROM bqt1.smalla x, bqt1.smalla y WHERE x.stringnum = y.intnum;"; - String output = "SELECT x.IntNum, y.IntKey FROM SmallA AS x, SmallA AS y WHERE x.StringNum = cast(y.IntNum AS varchar(4000))"; - - helpTestVisitor( - input, - output); - } - - @Test public void testLimitWithNestedInlineView() throws Exception { - String input = "select max(intkey), stringkey from (select intkey, stringkey from bqt1.smalla order by intkey limit 100) x group by stringkey"; - String output = "SELECT MAX(x.intkey), x.stringkey FROM (SELECT SmallA.IntKey, SmallA.StringKey FROM SmallA ORDER BY SmallA.IntKey LIMIT 100) AS x GROUP BY x.stringkey"; - - helpTestVisitor( input, output); - } - - @Test public void testAggregatesAndEnhancedNumeric() throws Exception { - String input = "select count(*), min(intkey), max(intkey), sum(intkey), avg(intkey), count(intkey), STDDEV_SAMP(intkey), STDDEV_POP(intkey), VAR_SAMP(intkey), VAR_POP(intkey) from bqt1.smalla"; - String output = "SELECT COUNT(*), MIN(SmallA.IntKey), MAX(SmallA.IntKey), SUM(SmallA.IntKey), AVG(SmallA.IntKey), COUNT(SmallA.IntKey), STDDEV_SAMP(SmallA.IntKey), STDDEV_POP(SmallA.IntKey), VAR_SAMP(SmallA.IntKey), VAR_POP(SmallA.IntKey) FROM SmallA"; - - helpTestVisitor( input, output); - } - @Test public void testAggregatesDistinct() throws Exception { - String input = "select avg(DISTINCT intnum) from bqt1.smalla"; - String output = "SELECT AVG(DISTINCT SmallA.IntNum) FROM SmallA"; - - helpTestVisitor( input, output); - } - - - @Test public void testExceptAsMinus() throws Exception { - String input = "select intkey, intnum from bqt1.smalla except select intnum, intkey from bqt1.smallb"; - String output = "SELECT SmallA.IntKey, SmallA.IntNum FROM SmallA EXCEPT SELECT SmallB.IntNum, SmallB.IntKey FROM SmallB"; - - helpTestVisitor( input, output); - } - - @Test public void testUnionAsPlus() throws Exception { - String input = "select intkey, intnum from bqt1.smalla union select intnum, intkey from bqt1.smallb"; - String output = "SELECT SmallA.IntKey, SmallA.IntNum FROM SmallA UNION SELECT SmallB.IntNum, SmallB.IntKey FROM SmallB"; - - helpTestVisitor( input, output); - } - @Test public void testUnionAllAsPlus() throws Exception { - String input = "select intkey, intnum from bqt1.smalla union all select intnum, intkey from bqt1.smallb"; - String output = "SELECT SmallA.IntKey, SmallA.IntNum FROM SmallA UNION ALL SELECT SmallB.IntNum, SmallB.IntKey FROM SmallB"; - - helpTestVisitor( input, output); - } - - @Test public void testUnionAllAsPlusWithAggregates() throws Exception { - String input = "select intkey, Sum(intnum) from bqt1.smalla group by intkey union all select intnum, intkey from bqt1.smallb"; - String output = "SELECT SmallA.IntKey, SUM(SmallA.IntNum) FROM SmallA GROUP BY SmallA.IntKey UNION ALL SELECT SmallB.IntNum, SmallB.IntKey AS IntKey FROM SmallB"; - - helpTestVisitor( input, output); - } - - - @Test public void testintersect() throws Exception { - String input = "select intkey from bqt1.smalla where intkey < 20 INTERSECT select intkey from bqt1.smalla where intkey > 10"; - String output = "SELECT SmallA.IntKey FROM SmallA WHERE SmallA.IntKey < 20 INTERSECT SELECT SmallA.IntKey FROM SmallA WHERE SmallA.IntKey > 10"; - - - helpTestVisitor( input, output); - } - - - @Test public void testUnionOrderBy() throws Exception { - String input = "(select intkey from bqt1.smalla) union select intnum from bqt1.smalla order by intkey"; - String output = "SELECT SmallA.IntKey FROM SmallA UNION SELECT SmallA.IntNum FROM SmallA ORDER BY intkey"; - - helpTestVisitor( input, output); - - } - - @Test public void testIntersectOrderBy() throws Exception { - String input = "(select intkey from bqt1.smalla) intersect select intnum from bqt1.smalla order by intkey"; - String output = "SELECT SmallA.IntKey FROM SmallA INTERSECT SELECT SmallA.IntNum FROM SmallA ORDER BY intkey"; - - helpTestVisitor( input, output); - - } - - @Test public void testExceptOrderBy() throws Exception { - String input = "(select intkey from bqt1.smalla) except select intnum from bqt1.smalla order by intkey"; - String output = "SELECT SmallA.IntKey FROM SmallA EXCEPT SELECT SmallA.IntNum FROM SmallA ORDER BY intkey"; - - helpTestVisitor( input, output); - - } - - - @Test public void testRowLimitOFFSET() throws Exception { - String input = "select intkey from bqt1.smalla limit 20, 30"; - String output = "SELECT SmallA.IntKey FROM SmallA LIMIT 30 OFFSET 20"; - - helpTestVisitor( input, output); - } - - - @Test public void testOrderByNullsFirstLast() throws Exception { - String input = "select intkey, longnum from bqt1.smalla order by longnum NULLS LAST"; - String output = "SELECT SmallA.IntKey, SmallA.LongNum FROM SmallA ORDER BY SmallA.LongNum NULLS LAST"; - - helpTestVisitor( input, output); - } - - @Test public void testOrderByUnRelated() throws Exception { - String input = "select intkey, longnum from bqt1.smalla order by floatnum"; - String output = "SELECT SmallA.IntKey, SmallA.LongNum FROM SmallA ORDER BY SmallA.FloatNum"; - - helpTestVisitor( input, output); - } - - - @Test public void testInnerJoin() throws Exception { - String input = "SELECT BQT1.SmallA.IntKey FROM BQT1.SmallA, BQT2.SmallB WHERE BQT1.SmallA.IntKey = BQT2.SmallB.IntKey AND BQT1.SmallA.IntKey >= 0 AND BQT2.SmallB.IntKey >= 0 ORDER BY BQT1.SmallA.IntKey"; - String output = "SELECT SmallA.IntKey FROM SmallA, SmallB WHERE SmallA.IntKey = SmallB.IntKey AND SmallA.IntKey >= 0 AND SmallB.IntKey >= 0 ORDER BY SmallA.IntKey"; - - helpTestVisitor( input, output); - } - - - @Test public void testOuterJoin() throws Exception { - String input = "SELECT BQT1.SmallA.IntKey FROM BQT1.SmallA, BQT2.SmallB WHERE BQT1.SmallA.IntKey = BQT2.SmallB.IntKey AND BQT1.SmallA.IntKey >= 0 AND BQT2.SmallB.IntKey >= 0 ORDER BY BQT1.SmallA.IntKey"; - String output = "SELECT SmallA.IntKey FROM SmallA, SmallB WHERE SmallA.IntKey = SmallB.IntKey AND SmallA.IntKey >= 0 AND SmallB.IntKey >= 0 ORDER BY SmallA.IntKey"; - - helpTestVisitor( input, output); - } - - @Test public void testFullOuterJoin() throws Exception { - String input = "SELECT BQT1.SmallA.IntNum, BQT2.SmallB.IntNum FROM BQT1.SmallA FULL OUTER JOIN BQT2.SmallB ON BQT1.SmallA.IntNum = BQT2.SmallB.IntNum ORDER BY BQT1.SmallA.IntNum"; - String output = "SELECT SmallA.IntNum, SmallB.IntNum FROM SmallA FULL OUTER JOIN SmallB ON SmallA.IntNum = SmallB.IntNum ORDER BY SmallA.IntNum"; - - helpTestVisitor( input, output); - } - - @Test public void testRightOuterJoin() throws Exception { - String input = "SELECT BQT1.SmallA.IntNum, BQT2.SmallB.IntNum FROM BQT1.SmallA RIGHT OUTER JOIN BQT2.SmallB ON BQT1.SmallA.IntNum = BQT2.SmallB.IntNum ORDER BY BQT2.SmallB.IntNum"; - String output= "SELECT SmallA.IntNum, SmallB.IntNum FROM SmallB LEFT OUTER JOIN SmallA ON SmallA.IntNum = SmallB.IntNum ORDER BY SmallB.IntNum"; - - helpTestVisitor( input, output); - } - @Test public void testLeftOuterJoin() throws Exception { - String input = "SELECT BQT1.SmallA.IntNum, BQT2.SmallB.IntNum FROM BQT1.SmallA LEFT OUTER JOIN BQT2.SmallB ON BQT1.SmallA.IntNum = BQT2.SmallB.IntNum ORDER BY BQT1.SmallA.IntNum"; - String output = "SELECT SmallA.IntNum, SmallB.IntNum FROM SmallA LEFT OUTER JOIN SmallB ON SmallA.IntNum = SmallB.IntNum ORDER BY SmallA.IntNum"; - - helpTestVisitor( input, output); - } - -} Copied: trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/netezza/TestNetezzaTranslatorCapabilities.java (from rev 3365, branches/7.4.x/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/netezza/TestNetezzaTranslatorCapabilities.java) =================================================================== --- trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/netezza/TestNetezzaTranslatorCapabilities.java (rev 0) +++ trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/netezza/TestNetezzaTranslatorCapabilities.java 2011-08-10 18:01:12 UTC (rev 3366) @@ -0,0 +1,394 @@ +/* + * 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 junit.framework.TestCase; + +import org.junit.Test; +import org.mockito.Mockito; +import org.teiid.cdk.unittest.FakeTranslationFactory; +import org.teiid.dqp.internal.datamgr.FakeExecutionContextImpl; +import org.teiid.language.Command; +import org.teiid.translator.ExecutionContext; +import org.teiid.translator.TranslatorException; +import org.teiid.translator.jdbc.TranslatedCommand; +import org.teiid.translator.jdbc.TranslationHelper; + +public class TestNetezzaTranslatorCapabilities extends TestCase { + + private static NetezzaExecutionFactory TRANSLATOR; + private static ExecutionContext EMPTY_CONTEXT = new FakeExecutionContextImpl(); + + static { + try { + TRANSLATOR = new NetezzaExecutionFactory(); + TRANSLATOR.start(); + } catch(TranslatorException e) { + e.printStackTrace(); + } + } + + + private String getTestBQTVDB() { + return TranslationHelper.BQT_VDB; + } + + public void helpTestVisitor(String input, String expectedOutput) throws TranslatorException { + // Convert from sql to objects + Command obj = FakeTranslationFactory.getInstance().getBQTTranslationUtility().parseCommand(input); + + TranslatedCommand tc = new TranslatedCommand(Mockito.mock(ExecutionContext.class), TRANSLATOR); + tc.translateCommand(obj); + + + // Check stuff + assertEquals("Did not get correct sql", expectedOutput, tc.getSql()); + } + + + /////////BASIC TEST CASES FOR CAPABILITIES///////////// + ///////////////////////////////////////////////// + @Test + public void testRowLimit() throws Exception { + String input = "select intkey from bqt1.smalla limit 100"; + String output = "SELECT SmallA.IntKey FROM SmallA LIMIT 100"; + + helpTestVisitor( + input, + output); + + } + @Test + public void testSelectDistinct() throws Exception { + String input = "select distinct intkey from bqt1.smalla limit 100"; + String output = "SELECT DISTINCT SmallA.IntKey FROM SmallA LIMIT 100"; + + helpTestVisitor( + input, + output); + } + @Test + public void testSelectExpression() throws Exception { + String input = "select intkey, intkey + longnum / 2 as test from bqt1.smalla"; + String output = "SELECT SmallA.IntKey, (SmallA.IntKey + (SmallA.LongNum / 2)) AS test FROM SmallA"; + + helpTestVisitor( + input, + output); + } + + public void testBetweenCriteria() throws Exception { + String input = "select intkey, intnum from bqt1.smalla where intnum BETWEEN 2 AND 10"; + String output = "SELECT SmallA.IntKey, SmallA.IntNum FROM SmallA WHERE SmallA.IntNum >= 2 AND SmallA.IntNum <= 10"; + + helpTestVisitor( + input, + output); + } + public void testCompareCriteriaEquals() throws Exception { + String input = "select intkey, intnum from bqt1.smalla where intnum = 10"; + String output = "SELECT SmallA.IntKey, SmallA.IntNum FROM SmallA WHERE SmallA.IntNum = 10"; + + helpTestVisitor( + input, + output); + } + public void testCompareCriteriaOrdered() throws Exception { + String input = "select intkey, intnum from bqt1.smalla where intnum < 10"; + String output = "SELECT SmallA.IntKey, SmallA.IntNum FROM SmallA WHERE SmallA.IntNum < 10"; + + helpTestVisitor( + input, + output); + } + public void testLikeCriteria() throws Exception { + String input = "select intkey, intnum from bqt1.smalla where stringkey like '4%'"; + String output = "SELECT SmallA.IntKey, SmallA.IntNum FROM SmallA WHERE SmallA.StringKey LIKE '4%'"; + + helpTestVisitor( + input, + output); + } + public void testLikeWithEscapeCriteria() throws Exception { + String input = "select intkey, intnum from bqt1.smalla where stringkey like '4\\%'"; + String output = "SELECT SmallA.IntKey, SmallA.IntNum FROM SmallA WHERE SmallA.StringKey LIKE '4\\%'"; + + helpTestVisitor( + input, + output); + } + + public void testInCriteria() throws Exception { + String input = "select intkey, intnum from bqt1.smalla where stringkey IN ('10', '11', '12')"; + String output = "SELECT SmallA.IntKey, SmallA.IntNum FROM SmallA WHERE SmallA.StringKey IN ('10', '11', '12')"; + + helpTestVisitor( + input, + output); + } + public void testInCriteriaSubQuery() throws Exception { + String input = "select intkey, intnum from bqt1.smalla where stringkey IN (select stringkey from bqt1.smalla where intkey < 10)"; + String output = "SELECT SmallA.IntKey, SmallA.IntNum FROM SmallA WHERE SmallA.StringKey IN (SELECT SmallA.StringKey FROM SmallA WHERE SmallA.IntKey < 10)"; + + helpTestVisitor( + input, + output); + } + + public void testIsNullCriteria() throws Exception { + String input = "select intkey, intnum from bqt1.smalla where intnum IS NULL"; + String output = "SELECT SmallA.IntKey, SmallA.IntNum FROM SmallA WHERE SmallA.IntNum IS NULL"; + + helpTestVisitor( + input, + output); + } + public void testOrCriteria() throws Exception { + String input = "select intkey, intnum from bqt1.smalla where intnum < 2 OR intnum > 10"; + String output = "SELECT SmallA.IntKey, SmallA.IntNum FROM SmallA WHERE SmallA.IntNum < 2 OR SmallA.IntNum > 10"; + + helpTestVisitor( + input, + output); + } + @Test public void testIsNotNullCriteria() throws Exception { + String input = "select intkey, intnum from bqt1.smalla where intnum IS NOT NULL"; + String output = "SELECT SmallA.IntKey, SmallA.IntNum FROM SmallA WHERE SmallA.IntNum IS NOT NULL"; + + helpTestVisitor( + input, + output); + } + + @Test public void testExistsCriteria() throws Exception { + String input = "select intkey, intnum from bqt1.smalla where exists (select intkey from bqt1.smallb)"; + String output = "SELECT SmallA.IntKey, SmallA.IntNum FROM SmallA WHERE EXISTS (SELECT SmallB.IntKey FROM SmallB LIMIT 1)"; + + helpTestVisitor( + input, + output); + } + @Test public void testHavingClauseCriteria() throws Exception { + String input = "SELECT INTKEY FROM BQT1.SMALLA GROUP BY INTKEY HAVING INTKEY = (SELECT INTKEY FROM BQT1.SMALLA WHERE STRINGKEY = 20)"; + String output = "SELECT SmallA.IntKey FROM SmallA GROUP BY SmallA.IntKey HAVING SmallA.IntKey = (SELECT SmallA.IntKey FROM SmallA WHERE SmallA.StringKey = '20' LIMIT 2)"; + + helpTestVisitor( + input, + output); + } + + @Test public void testScalarSubQuery() throws Exception { + String input = "select intkey, intnum from bqt1.smalla where intnum < (0.01 * (select sum(intnum) from bqt1.smalla ))"; + String output = "SELECT SmallA.IntKey, SmallA.IntNum FROM SmallA WHERE SmallA.IntNum < (0.010000000000000 * (SELECT SUM(SmallA.IntNum) FROM SmallA))"; + + helpTestVisitor( + input, + output); + } + + @Test public void testSimpleCaseExpression() throws Exception { + String input = "SELECT stringnum, intnum, CASE BOOLEANVALUE WHEN 'true' then 'true' WHEN false THEN 'FALSE' ELSE 'GOOD' END FROM bqt1.smalla;"; + String output = "SELECT SmallA.StringNum, SmallA.IntNum, CASE WHEN SmallA.BooleanValue = 1 THEN 'true' WHEN SmallA.BooleanValue = 0 THEN 'FALSE' ELSE 'GOOD' END FROM SmallA"; + + helpTestVisitor( + input, + output); + } + + @Test public void testSearchedCaseExpression() throws Exception { + String input = "SELECT AVG(CASE WHEN intnum > 10 THEN intnum ELSE intkey END) \"Average\" FROM bqt1.smalla"; + String output = "SELECT AVG(CASE WHEN SmallA.IntNum > 10 THEN SmallA.IntNum ELSE SmallA.IntKey END) AS Average FROM SmallA"; + + helpTestVisitor( + input, + output); + } + +// @Test public void testQuantifiedCompareSOMEorANY() throws Exception { +// String input = "SELECT INTKEY, BYTENUM FROM BQT1.SmallA WHERE BYTENUM = ANY (SELECT BYTENUM FROM BQT1.SmallA WHERE BYTENUM >= '-108')"; +// String output = "SELECT SmallA.IntKey, SmallA.ByteNum FROM SmallA WHERE SmallA.ByteNum = SOME (SELECT SmallA.ByteNum FROM SmallA WHERE SmallA.ByteNum >= -108)"; +// +// helpTestVisitor( +// input, +// output); +// } + + @Test public void testQuantifiedCompareALL() throws Exception { + String input = "SELECT INTKEY, STRINGKEY FROM BQT1.SMALLA WHERE STRINGKEY = ALL (SELECT STRINGKEY FROM BQT1.SMALLA WHERE INTKEY = 40)"; + String output = "SELECT SmallA.IntKey, SmallA.StringKey FROM SmallA WHERE SmallA.StringKey = ALL (SELECT SmallA.StringKey FROM SmallA WHERE SmallA.IntKey = 40)"; + + helpTestVisitor( + input, + output); + } + + @Test public void testSelfJoin() throws Exception { + String input = "SELECT x.intnum, y.intkey FROM bqt1.smalla x, bqt1.smalla y WHERE x.stringnum = y.intnum;"; + String output = "SELECT x.IntNum, y.IntKey FROM SmallA AS x, SmallA AS y WHERE x.StringNum = cast(y.IntNum AS varchar(4000))"; + + helpTestVisitor( + input, + output); + } + + @Test public void testLimitWithNestedInlineView() throws Exception { + String input = "select max(intkey), stringkey from (select intkey, stringkey from bqt1.smalla order by intkey limit 100) x group by stringkey"; + String output = "SELECT MAX(x.intkey), x.stringkey FROM (SELECT SmallA.IntKey, SmallA.StringKey FROM SmallA ORDER BY SmallA.IntKey LIMIT 100) AS x GROUP BY x.stringkey"; + + helpTestVisitor( input, output); + } + + @Test public void testAggregatesAndEnhancedNumeric() throws Exception { + String input = "select count(*), min(intkey), max(intkey), sum(intkey), avg(intkey), count(intkey), STDDEV_SAMP(intkey), STDDEV_POP(intkey), VAR_SAMP(intkey), VAR_POP(intkey) from bqt1.smalla"; + String output = "SELECT COUNT(*), MIN(SmallA.IntKey), MAX(SmallA.IntKey), SUM(SmallA.IntKey), AVG(SmallA.IntKey), COUNT(SmallA.IntKey), STDDEV_SAMP(SmallA.IntKey), STDDEV_POP(SmallA.IntKey), VAR_SAMP(SmallA.IntKey), VAR_POP(SmallA.IntKey) FROM SmallA"; + + helpTestVisitor( input, output); + } + @Test public void testAggregatesDistinct() throws Exception { + String input = "select avg(DISTINCT intnum) from bqt1.smalla"; + String output = "SELECT AVG(DISTINCT SmallA.IntNum) FROM SmallA"; + + helpTestVisitor( input, output); + } + + + @Test public void testExceptAsMinus() throws Exception { + String input = "select intkey, intnum from bqt1.smalla except select intnum, intkey from bqt1.smallb"; + String output = "SELECT SmallA.IntKey, SmallA.IntNum FROM SmallA EXCEPT SELECT SmallB.IntNum, SmallB.IntKey FROM SmallB"; + + helpTestVisitor( input, output); + } + + @Test public void testUnionAsPlus() throws Exception { + String input = "select intkey, intnum from bqt1.smalla union select intnum, intkey from bqt1.smallb"; + String output = "SELECT SmallA.IntKey, SmallA.IntNum FROM SmallA UNION SELECT SmallB.IntNum, SmallB.IntKey FROM SmallB"; + + helpTestVisitor( input, output); + } + @Test public void testUnionAllAsPlus() throws Exception { + String input = "select intkey, intnum from bqt1.smalla union all select intnum, intkey from bqt1.smallb"; + String output = "SELECT SmallA.IntKey, SmallA.IntNum FROM SmallA UNION ALL SELECT SmallB.IntNum, SmallB.IntKey FROM SmallB"; + + helpTestVisitor( input, output); + } + + @Test public void testUnionAllAsPlusWithAggregates() throws Exception { + String input = "select intkey, Sum(intnum) from bqt1.smalla group by intkey union all select intnum, intkey from bqt1.smallb"; + String output = "SELECT SmallA.IntKey, SUM(SmallA.IntNum) FROM SmallA GROUP BY SmallA.IntKey UNION ALL SELECT SmallB.IntNum, SmallB.IntKey AS IntKey FROM SmallB"; + + helpTestVisitor( input, output); + } + + + @Test public void testintersect() throws Exception { + String input = "select intkey from bqt1.smalla where intkey < 20 INTERSECT select intkey from bqt1.smalla where intkey > 10"; + String output = "SELECT SmallA.IntKey FROM SmallA WHERE SmallA.IntKey < 20 INTERSECT SELECT SmallA.IntKey FROM SmallA WHERE SmallA.IntKey > 10"; + + + helpTestVisitor( input, output); + } + + + @Test public void testUnionOrderBy() throws Exception { + String input = "(select intkey from bqt1.smalla) union select intnum from bqt1.smalla order by intkey"; + String output = "SELECT SmallA.IntKey FROM SmallA UNION SELECT SmallA.IntNum FROM SmallA ORDER BY intkey"; + + helpTestVisitor( input, output); + + } + + @Test public void testIntersectOrderBy() throws Exception { + String input = "(select intkey from bqt1.smalla) intersect select intnum from bqt1.smalla order by intkey"; + String output = "SELECT SmallA.IntKey FROM SmallA INTERSECT SELECT SmallA.IntNum FROM SmallA ORDER BY intkey"; + + helpTestVisitor( input, output); + + } + + @Test public void testExceptOrderBy() throws Exception { + String input = "(select intkey from bqt1.smalla) except select intnum from bqt1.smalla order by intkey"; + String output = "SELECT SmallA.IntKey FROM SmallA EXCEPT SELECT SmallA.IntNum FROM SmallA ORDER BY intkey"; + + helpTestVisitor( input, output); + + } + + + @Test public void testRowLimitOFFSET() throws Exception { + String input = "select intkey from bqt1.smalla limit 20, 30"; + String output = "SELECT SmallA.IntKey FROM SmallA LIMIT 30 OFFSET 20"; + + helpTestVisitor( input, output); + } + + + @Test public void testOrderByNullsFirstLast() throws Exception { + String input = "select intkey, longnum from bqt1.smalla order by longnum NULLS LAST"; + String output = "SELECT SmallA.IntKey, SmallA.LongNum FROM SmallA ORDER BY SmallA.LongNum NULLS LAST"; + + helpTestVisitor( input, output); + } + + @Test public void testOrderByUnRelated() throws Exception { + String input = "select intkey, longnum from bqt1.smalla order by floatnum"; + String output = "SELECT SmallA.IntKey, SmallA.LongNum FROM SmallA ORDER BY SmallA.FloatNum"; + + helpTestVisitor( input, output); + } + + + @Test public void testInnerJoin() throws Exception { + String input = "SELECT BQT1.SmallA.IntKey FROM BQT1.SmallA, BQT2.SmallB WHERE BQT1.SmallA.IntKey = BQT2.SmallB.IntKey AND BQT1.SmallA.IntKey >= 0 AND BQT2.SmallB.IntKey >= 0 ORDER BY BQT1.SmallA.IntKey"; + String output = "SELECT SmallA.IntKey FROM SmallA, SmallB WHERE SmallA.IntKey = SmallB.IntKey AND SmallA.IntKey >= 0 AND SmallB.IntKey >= 0 ORDER BY SmallA.IntKey"; + + helpTestVisitor( input, output); + } + + + @Test public void testOuterJoin() throws Exception { + String input = "SELECT BQT1.SmallA.IntKey FROM BQT1.SmallA, BQT2.SmallB WHERE BQT1.SmallA.IntKey = BQT2.SmallB.IntKey AND BQT1.SmallA.IntKey >= 0 AND BQT2.SmallB.IntKey >= 0 ORDER BY BQT1.SmallA.IntKey"; + String output = "SELECT SmallA.IntKey FROM SmallA, SmallB WHERE SmallA.IntKey = SmallB.IntKey AND SmallA.IntKey >= 0 AND SmallB.IntKey >= 0 ORDER BY SmallA.IntKey"; + + helpTestVisitor( input, output); + } + + @Test public void testFullOuterJoin() throws Exception { + String input = "SELECT BQT1.SmallA.IntNum, BQT2.SmallB.IntNum FROM BQT1.SmallA FULL OUTER JOIN BQT2.SmallB ON BQT1.SmallA.IntNum = BQT2.SmallB.IntNum ORDER BY BQT1.SmallA.IntNum"; + String output = "SELECT SmallA.IntNum, SmallB.IntNum FROM SmallA FULL OUTER JOIN SmallB ON SmallA.IntNum = SmallB.IntNum ORDER BY SmallA.IntNum"; + + helpTestVisitor( input, output); + } + + @Test public void testRightOuterJoin() throws Exception { + String input = "SELECT BQT1.SmallA.IntNum, BQT2.SmallB.IntNum FROM BQT1.SmallA RIGHT OUTER JOIN BQT2.SmallB ON BQT1.SmallA.IntNum = BQT2.SmallB.IntNum ORDER BY BQT2.SmallB.IntNum"; + String output= "SELECT SmallA.IntNum, SmallB.IntNum FROM SmallB LEFT OUTER JOIN SmallA ON SmallA.IntNum = SmallB.IntNum ORDER BY SmallB.IntNum"; + + helpTestVisitor( input, output); + } + @Test public void testLeftOuterJoin() throws Exception { + String input = "SELECT BQT1.SmallA.IntNum, BQT2.SmallB.IntNum FROM BQT1.SmallA LEFT OUTER JOIN BQT2.SmallB ON BQT1.SmallA.IntNum = BQT2.SmallB.IntNum ORDER BY BQT1.SmallA.IntNum"; + String output = "SELECT SmallA.IntNum, SmallB.IntNum FROM SmallA LEFT OUTER JOIN SmallB ON SmallA.IntNum = SmallB.IntNum ORDER BY SmallA.IntNum"; + + helpTestVisitor( input, output); + } + +} Deleted: trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/netezza/TestNetezzaTranslatorDatetimeConversion.java =================================================================== --- branches/7.4.x/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/netezza/TestNetezzaTranslatorDatetimeConversion.java 2011-08-06 14:57:03 UTC (rev 3365) +++ trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/netezza/TestNetezzaTranslatorDatetimeConversion.java 2011-08-10 18:01:12 UTC (rev 3366) @@ -1,141 +0,0 @@ -/* - * 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 static org.junit.Assert.assertEquals; - -import java.util.Arrays; - -import org.junit.BeforeClass; -import org.junit.Test; -import org.teiid.language.Expression; -import org.teiid.language.Function; -import org.teiid.language.LanguageFactory; -import org.teiid.translator.TranslatorException; -import org.teiid.translator.TypeFacility; -import org.teiid.translator.jdbc.SQLConversionVisitor; -import org.teiid.translator.jdbc.TranslationHelper; - -/** - */ -public class TestNetezzaTranslatorDatetimeConversion { - - private static NetezzaExecutionFactory TRANSLATOR; - private static final LanguageFactory LANG_FACTORY = new LanguageFactory(); - - @BeforeClass public static void oneTimeSetup() throws TranslatorException { - TRANSLATOR = new NetezzaExecutionFactory(); - TRANSLATOR.setUseBindVariables(false); - TRANSLATOR.start(); - - - } - - /////////////////UTILLITY FUNCTIONS///////// - //////////////////////////////////////////// - - private String getTestVDB() { - //return TranslationHelper.NETEZZA_VDB; - return TranslationHelper.PARTS_VDB; - } - - private String getTestBQTVDB() { - return TranslationHelper.BQT_VDB; - } - - public void helpTest(Expression srcExpression, String tgtType, String expectedExpression) throws Exception { - Function func = LANG_FACTORY.createFunction("convert", - Arrays.asList( srcExpression,LANG_FACTORY.createLiteral(tgtType, String.class)),TypeFacility.getDataTypeClass(tgtType)); - - assertEquals("Error converting from " + srcExpression.getType() + " to " + tgtType, - expectedExpression, helpGetString(func)); - } - public String helpGetString(Expression expr) throws Exception { - SQLConversionVisitor sqlVisitor = TRANSLATOR.getSQLConversionVisitor(); - sqlVisitor.append(expr); - - return sqlVisitor.toString(); - } - - - ///////////////DATE/TIME CONVERSION TESTCASES/////// - //////////////////////////////////////////////////// - - @Test public void testdayofmonth() throws Exception { - String input = "SELECT dayofmonth(datevalue) FROM BQT1.SMALLA"; - String output = "SELECT extract(DAY from SmallA.DateValue) FROM SmallA"; - - TranslationHelper.helpTestVisitor(getTestBQTVDB(), input, output, TRANSLATOR); - } - - - ///BEGIN--FROM TIMESTAMP->DATE, TIME, STRING//////// - @Test public void testTimestampToDate() throws Exception { - String input = "SELECT convert(convert(TIMESTAMPVALUE, date), string) FROM BQT1.SMALLA"; - String output = "SELECT cast(cast(SmallA.TimestampValue AS DATE) AS varchar(4000)) FROM SmallA"; - - TranslationHelper.helpTestVisitor(getTestBQTVDB(), input, output, TRANSLATOR); - } - @Test public void testTimestampToTime() throws Exception { - String input = "SELECT convert(convert(TIMESTAMPVALUE, time), string) FROM BQT1.SMALLA"; - String output = "SELECT cast(cast(SmallA.TimestampValue AS TIME) AS varchar(4000)) FROM SmallA"; - - TranslationHelper.helpTestVisitor(getTestBQTVDB(), input, output, TRANSLATOR); - } - - @Test public void testTimestampToString() throws Exception { - String input = "SELECT convert(timestampvalue, string) FROM BQT1.SMALLA"; - String output = "SELECT to_char(SmallA.TimestampValue, 'YYYY-MM-DD HH24:MI:SS.MS') FROM SmallA"; - - TranslationHelper.helpTestVisitor(getTestBQTVDB(), input, output, TRANSLATOR); - } - ///END--FROM TIMESTAMP->DATE, TIME, STRING//////// - - ///BEGIN--FROM DATE->TIMESTAMP//////// - @Test public void testDateToTimestamp() throws Exception { - String input = "SELECT convert(convert(datevalue, timestamp), string) FROM BQT1.SMALLA"; - String output = "SELECT to_char(cast(SmallA.DateValue AS TIMESTAMP), 'YYYY-MM-DD HH24:MI:SS.MS') FROM SmallA"; - - TranslationHelper.helpTestVisitor(getTestBQTVDB(), input, output, TRANSLATOR); - } - ///END--FROM DATE->TIMESTAMP//////// - - ///BEGIN--FROM TIME->TIMESTAMP//////// - @Test public void testTimeToTimestamp() throws Exception { - String input = "SELECT convert(convert(TIMEVALUE, timestamp), string) FROM BQT1.SMALLA"; - //String output = "SELECT to_char(cast(SmallA.TimeValue AS timestamp), 'YYYY-MM-DD HH24:MI:SS.FF') FROM SmallA"; - String output = "SELECT to_char(SmallA.TimeValue, 'YYYY-MM-DD HH24:MI:SS.MS') FROM SmallA"; - - TranslationHelper.helpTestVisitor(getTestBQTVDB(), input, output, TRANSLATOR); - } - ///END--FROM TIME->TIMESTAMP//////// - - -// @Test public void testTimestampToTime() throws Exception { -// helpTest(LANG_FACTORY.createLiteral(TimestampUtil.createTimestamp(111, 4, 5, 9, 16, 34, 220000000), Timestamp.class), "TIME", "cast(cast('2011-05-05 09:16:34.22' AS TIMESTAMP(6)) AS TIME)"); -// } - - - - -} Copied: trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/netezza/TestNetezzaTranslatorDatetimeConversion.java (from rev 3365, branches/7.4.x/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/netezza/TestNetezzaTranslatorDatetimeConversion.java) =================================================================== --- trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/netezza/TestNetezzaTranslatorDatetimeConversion.java (rev 0) +++ trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/netezza/TestNetezzaTranslatorDatetimeConversion.java 2011-08-10 18:01:12 UTC (rev 3366) @@ -0,0 +1,141 @@ +/* + * 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 static org.junit.Assert.assertEquals; + +import java.util.Arrays; + +import org.junit.BeforeClass; +import org.junit.Test; +import org.teiid.language.Expression; +import org.teiid.language.Function; +import org.teiid.language.LanguageFactory; +import org.teiid.translator.TranslatorException; +import org.teiid.translator.TypeFacility; +import org.teiid.translator.jdbc.SQLConversionVisitor; +import org.teiid.translator.jdbc.TranslationHelper; + +/** + */ +public class TestNetezzaTranslatorDatetimeConversion { + + private static NetezzaExecutionFactory TRANSLATOR; + private static final LanguageFactory LANG_FACTORY = new LanguageFactory(); + + @BeforeClass public static void oneTimeSetup() throws TranslatorException { + TRANSLATOR = new NetezzaExecutionFactory(); + TRANSLATOR.setUseBindVariables(false); + TRANSLATOR.start(); + + + } + + /////////////////UTILLITY FUNCTIONS///////// + //////////////////////////////////////////// + + private String getTestVDB() { + //return TranslationHelper.NETEZZA_VDB; + return TranslationHelper.PARTS_VDB; + } + + private String getTestBQTVDB() { + return TranslationHelper.BQT_VDB; + } + + public void helpTest(Expression srcExpression, String tgtType, String expectedExpression) throws Exception { + Function func = LANG_FACTORY.createFunction("convert", + Arrays.asList( srcExpression,LANG_FACTORY.createLiteral(tgtType, String.class)),TypeFacility.getDataTypeClass(tgtType)); + + assertEquals("Error converting from " + srcExpression.getType() + " to " + tgtType, + expectedExpression, helpGetString(func)); + } + public String helpGetString(Expression expr) throws Exception { + SQLConversionVisitor sqlVisitor = TRANSLATOR.getSQLConversionVisitor(); + sqlVisitor.append(expr); + + return sqlVisitor.toString(); + } + + + ///////////////DATE/TIME CONVERSION TESTCASES/////// + //////////////////////////////////////////////////// + + @Test public void testdayofmonth() throws Exception { + String input = "SELECT dayofmonth(datevalue) FROM BQT1.SMALLA"; + String output = "SELECT extract(DAY from SmallA.DateValue) FROM SmallA"; + + TranslationHelper.helpTestVisitor(getTestBQTVDB(), input, output, TRANSLATOR); + } + + + ///BEGIN--FROM TIMESTAMP->DATE, TIME, STRING//////// + @Test public void testTimestampToDate() throws Exception { + String input = "SELECT convert(convert(TIMESTAMPVALUE, date), string) FROM BQT1.SMALLA"; + String output = "SELECT cast(cast(SmallA.TimestampValue AS DATE) AS varchar(4000)) FROM SmallA"; + + TranslationHelper.helpTestVisitor(getTestBQTVDB(), input, output, TRANSLATOR); + } + @Test public void testTimestampToTime() throws Exception { + String input = "SELECT convert(convert(TIMESTAMPVALUE, time), string) FROM BQT1.SMALLA"; + String output = "SELECT cast(cast(SmallA.TimestampValue AS TIME) AS varchar(4000)) FROM SmallA"; + + TranslationHelper.helpTestVisitor(getTestBQTVDB(), input, output, TRANSLATOR); + } + + @Test public void testTimestampToString() throws Exception { + String input = "SELECT convert(timestampvalue, string) FROM BQT1.SMALLA"; + String output = "SELECT to_char(SmallA.TimestampValue, 'YYYY-MM-DD HH24:MI:SS.MS') FROM SmallA"; + + TranslationHelper.helpTestVisitor(getTestBQTVDB(), input, output, TRANSLATOR); + } + ///END--FROM TIMESTAMP->DATE, TIME, STRING//////// + + ///BEGIN--FROM DATE->TIMESTAMP//////// + @Test public void testDateToTimestamp() throws Exception { + String input = "SELECT convert(convert(datevalue, timestamp), string) FROM BQT1.SMALLA"; + String output = "SELECT to_char(cast(SmallA.DateValue AS TIMESTAMP), 'YYYY-MM-DD HH24:MI:SS.MS') FROM SmallA"; + + TranslationHelper.helpTestVisitor(getTestBQTVDB(), input, output, TRANSLATOR); + } + ///END--FROM DATE->TIMESTAMP//////// + + ///BEGIN--FROM TIME->TIMESTAMP//////// + @Test public void testTimeToTimestamp() throws Exception { + String input = "SELECT convert(convert(TIMEVALUE, timestamp), string) FROM BQT1.SMALLA"; + //String output = "SELECT to_char(cast(SmallA.TimeValue AS timestamp), 'YYYY-MM-DD HH24:MI:SS.FF') FROM SmallA"; + String output = "SELECT to_char(SmallA.TimeValue, 'YYYY-MM-DD HH24:MI:SS.MS') FROM SmallA"; + + TranslationHelper.helpTestVisitor(getTestBQTVDB(), input, output, TRANSLATOR); + } + ///END--FROM TIME->TIMESTAMP//////// + + +// @Test public void testTimestampToTime() throws Exception { +// helpTest(LANG_FACTORY.createLiteral(TimestampUtil.createTimestamp(111, 4, 5, 9, 16, 34, 220000000), Timestamp.class), "TIME", "cast(cast('2011-05-05 09:16:34.22' AS TIMESTAMP(6)) AS TIME)"); +// } + + + + +} Deleted: trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/netezza/TestNetezzaTranslatorSourceSystemFunctions.java =================================================================== --- branches/7.4.x/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/netezza/TestNetezzaTranslatorSourceSystemFunctions.java 2011-08-06 14:57:03 UTC (rev 3365) +++ trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/netezza/TestNetezzaTranslatorSourceSystemFunctions.java 2011-08-10 18:01:12 UTC (rev 3366) @@ -1,243 +0,0 @@ -/* - * 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 org.junit.BeforeClass; -import org.junit.Test; -import org.teiid.language.LanguageFactory; -import org.teiid.translator.TranslatorException; -import org.teiid.translator.jdbc.TranslationHelper; - -/** - */ -public class TestNetezzaTranslatorSourceSystemFunctions { - - private static NetezzaExecutionFactory TRANSLATOR; - private static final LanguageFactory LANG_FACTORY = new LanguageFactory(); - - @BeforeClass public static void oneTimeSetup() throws TranslatorException { - TRANSLATOR = new NetezzaExecutionFactory(); - TRANSLATOR.setUseBindVariables(false); - TRANSLATOR.start(); - - - } - - - /////////////////UTILLITY FUNCTIONS///////// - //////////////////////////////////////////// - - - private String getTestBQTVDB() { - return TranslationHelper.BQT_VDB; - } - - - /////SOURCESYSTEM FUNCTION TESTCASES////////////// - /////////////////////////////////////////////// - - - //////////////////BEGIN---STRING FUNCTIONS TESTCASES/////////////////// - - @Test - public void testLcaseUcase() throws Exception { - String input = "select lcase(StringKey), ucase(StringKey) FROM BQT1.SmallA"; - String output = "SELECT lower(SmallA.StringKey), upper(SmallA.StringKey) FROM SmallA"; - - TranslationHelper.helpTestVisitor(getTestBQTVDB(), input, output,TRANSLATOR); - } - @Test public void testPad() throws Exception { - String input = "select lpad(smalla.stringkey, 18), rpad(smalla.stringkey, 12) from bqt1.smalla"; //$NON-NLS-1$ - String output = "SELECT lpad(SmallA.StringKey, 18), rpad(SmallA.StringKey, 12) FROM SmallA"; //$NON-NLS-1$ - - TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB, - input, - output, TRANSLATOR); - } - - @Test - public void testIFNull() throws Exception { - String input = "SELECT ifnull(StringKey, 'otherString') FROM BQT1.SmallA"; - String output = "SELECT NVL(SmallA.StringKey, 'otherString') FROM SmallA"; - //SELECT IFNULL(GL_ACTG_APPL_ID, 'otherString') FROM ACTG_UNIT_BAL_FACT - //SELECT nvl(GL_ACTG_APPL_ID, 'otherString') FROM ACTG_UNIT_BAL_FACT - - TranslationHelper.helpTestVisitor(getTestBQTVDB(), input, output, TRANSLATOR); - } - - - @Test public void testSubstring1() throws Exception { - - //////////BOTH SUBSTRING AND SUBSTR work in NETEZZA// - ////////////////////////////////////////////////////// - String input = "SELECT substring(StringKey, 1) FROM BQT1.SmallA"; - String output = "SELECT substring(SmallA.StringKey, 1) FROM SmallA"; - //SELECT substring(FDL_PMF_ACCT, 3) FROM ACTG_UNIT_BAL_FACT - //SELECT substr(FDL_PMF_ACCT, 3) FROM ACTG_UNIT_BAL_FACT - TranslationHelper.helpTestVisitor(getTestBQTVDB(), input, output, TRANSLATOR); - } - @Test public void testSubstring2() throws Exception { - - //////////BOTH SUBSTRING AND SUBSTR work in NETEZZA// - ////////////////////////////////////////////////////// - String input = "SELECT substring(StringKey, 1, 5) FROM BQT1.SmallA"; - String output = "SELECT substring(SmallA.StringKey, 1, 5) FROM SmallA"; - TranslationHelper.helpTestVisitor(getTestBQTVDB(), input, output, TRANSLATOR); - } - - - - @Test public void testConcat_withLiteral() throws Exception { -// String sql = "select stringnum || '1' from BQT1.Smalla"; -// String expected = "SELECT CASE WHEN SmallA.StringNum IS NULL THEN NULL ELSE concat(SmallA.StringNum, '1') END FROM SmallA"; -// helpTestVisitor(FakeMetadataFactory.exampleBQTCached(), sql, EMPTY_CONTEXT, null, expected); - String input = "select stringnum || '1' from BQT1.Smalla"; - String output = "SELECT (SmallA.StringNum || '1') FROM SmallA"; - - TranslationHelper.helpTestVisitor(getTestBQTVDB(), input, output, TRANSLATOR); - } - - - - ////BEGIN-LOCATE FUNCTION - @Test public void testLocate() throws Exception { - String input = "SELECT locate(INTNUM, 'chimp', 1) FROM BQT1.SMALLA"; - String output = "SELECT INSTR('chimp', cast(SmallA.IntNum AS varchar(4000)), 1) FROM SmallA"; - - TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB, - input, output, - TRANSLATOR); - } - - @Test public void testLocate2() throws Exception { - String input = "SELECT locate(STRINGNUM, 'chimp') FROM BQT1.SMALLA"; - String output = "SELECT INSTR('chimp', SmallA.StringNum) FROM SmallA"; - - TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB, - input, output, - TRANSLATOR); - } - - @Test public void testLocate3() throws Exception { - String input = "SELECT locate(INTNUM, '234567890', 1) FROM BQT1.SMALLA WHERE INTKEY = 26"; - String output = "SELECT INSTR('234567890', cast(SmallA.IntNum AS varchar(4000)), 1) FROM SmallA WHERE SmallA.IntKey = 26"; - - TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB, input, output, TRANSLATOR); - } - - @Test public void testLocate4() throws Exception { - String input = "SELECT locate('c', 'chimp', 1) FROM BQT1.SMALLA"; - String output = "SELECT 1 FROM SmallA"; - - TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB, input, output, TRANSLATOR); - } - - @Test public void testLocate5() throws Exception { - String input = "SELECT locate(STRINGNUM, 'chimp', -5) FROM BQT1.SMALLA"; - String output = "SELECT INSTR('chimp', SmallA.StringNum, 1) FROM SmallA"; - - TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB, input, output, TRANSLATOR); - } - - @Test public void testLocate6() throws Exception { - String input = "SELECT locate(STRINGNUM, 'chimp', INTNUM) FROM BQT1.SMALLA"; - String output = "SELECT INSTR('chimp', SmallA.StringNum, CASE WHEN SmallA.IntNum < 1 THEN 1 ELSE SmallA.IntNum END) FROM SmallA"; - - TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB, input, output, TRANSLATOR); - } - - @Test public void testLocate7() throws Exception { - String input = "SELECT locate(STRINGNUM, 'chimp', LOCATE(STRINGNUM, 'chimp') + 1) FROM BQT1.SMALLA"; - String output = "SELECT INSTR('chimp', SmallA.StringNum, CASE WHEN (INSTR('chimp', SmallA.StringNum) + 1) < 1 THEN 1 ELSE (INSTR('chimp', SmallA.StringNum) + 1) END) FROM SmallA"; - - TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB, input, output, TRANSLATOR); - } - ////END-LOCATE FUNCTION - - - - - //////////////////BEGIN---NUMERIC FUNCTIONS TESTCASES/////////////////// - @Test public void testCeil() throws Exception { - //select ceiling(sqrt(MEAS_PRD_ID)) from ACTG_UNIT_BAL_FACT - //select ceil(sqrt(MEAS_PRD_ID)) from ACTG_UNIT_BAL_FACT - String input = "SELECT ceiling(sqrt(INTKEY)) FROM BQT1.SMALLA"; - String output = "SELECT ceil(sqrt(SmallA.IntKey)) FROM SmallA"; - TranslationHelper.helpTestVisitor(getTestBQTVDB(), - input, - output, TRANSLATOR); - } - - @Test public void testPower() throws Exception { - - //select power(MEAS_PRD_ID, 2) from ACTG_UNIT_BAL_FACT - //select pow(MEAS_PRD_ID, 2) from ACTG_UNIT_BAL_FACT - String input = "SELECT power(INTKEY, 2) FROM BQT1.SMALLA"; - String output = "SELECT pow(SmallA.IntKey, 2) FROM SmallA"; - TranslationHelper.helpTestVisitor(getTestBQTVDB(), - input, - output, TRANSLATOR); - } - //////////////////END---NUMERIC FUNCTIONS TESTCASES/////////////////// - - - //////////////////BEGIN---BIT FUNCTIONS CONVERSION TESTCASES/////////////////// - /////////////////////////////////////////////////////////////////// - - @Test public void testBitAnd() throws Exception { - String input = "select bitand(intkey, intnum) from bqt1.smalla"; - String output = "SELECT intNand(SmallA.IntKey, SmallA.IntNum) FROM SmallA"; - - TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB, - input, - output, TRANSLATOR); - } - @Test public void testBitNot() throws Exception { - String input = "select bitnot(intkey) from bqt1.smalla"; - String output = "SELECT intNnot(SmallA.IntKey) FROM SmallA"; - - TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB, - input, - output, TRANSLATOR); - } - @Test public void testBitOr() throws Exception { - String input = "select bitor(intkey, intnum) from bqt1.smalla"; - String output = "SELECT intNor(SmallA.IntKey, SmallA.IntNum) FROM SmallA"; - - TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB, - input, - output, TRANSLATOR); - } - @Test public void testBitXor() throws Exception { - String input = "select bitxor(intkey, intnum) from bqt1.smalla"; - String output = "SELECT intNxor(SmallA.IntKey, SmallA.IntNum) FROM SmallA"; - - TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB, - input, - output, TRANSLATOR); - } - //////////////////END---BIT FUNCTIONS CONVERSION TESTCASES/////////////////// - ///////////////////////////////////////////////////////////////////////////// - - -} Copied: trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/netezza/TestNetezzaTranslatorSourceSystemFunctions.java (from rev 3365, branches/7.4.x/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/netezza/TestNetezzaTranslatorSourceSystemFunctions.java) =================================================================== --- trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/netezza/TestNetezzaTranslatorSourceSystemFunctions.java (rev 0) +++ trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/netezza/TestNetezzaTranslatorSourceSystemFunctions.java 2011-08-10 18:01:12 UTC (rev 3366) @@ -0,0 +1,243 @@ +/* + * 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 org.junit.BeforeClass; +import org.junit.Test; +import org.teiid.language.LanguageFactory; +import org.teiid.translator.TranslatorException; +import org.teiid.translator.jdbc.TranslationHelper; + +/** + */ +public class TestNetezzaTranslatorSourceSystemFunctions { + + private static NetezzaExecutionFactory TRANSLATOR; + private static final LanguageFactory LANG_FACTORY = new LanguageFactory(); + + @BeforeClass public static void oneTimeSetup() throws TranslatorException { + TRANSLATOR = new NetezzaExecutionFactory(); + TRANSLATOR.setUseBindVariables(false); + TRANSLATOR.start(); + + + } + + + /////////////////UTILLITY FUNCTIONS///////// + //////////////////////////////////////////// + + + private String getTestBQTVDB() { + return TranslationHelper.BQT_VDB; + } + + + /////SOURCESYSTEM FUNCTION TESTCASES////////////// + /////////////////////////////////////////////// + + + //////////////////BEGIN---STRING FUNCTIONS TESTCASES/////////////////// + + @Test + public void testLcaseUcase() throws Exception { + String input = "select lcase(StringKey), ucase(StringKey) FROM BQT1.SmallA"; + String output = "SELECT lower(SmallA.StringKey), upper(SmallA.StringKey) FROM SmallA"; + + TranslationHelper.helpTestVisitor(getTestBQTVDB(), input, output,TRANSLATOR); + } + @Test public void testPad() throws Exception { + String input = "select lpad(smalla.stringkey, 18), rpad(smalla.stringkey, 12) from bqt1.smalla"; //$NON-NLS-1$ + String output = "SELECT lpad(SmallA.StringKey, 18), rpad(SmallA.StringKey, 12) FROM SmallA"; //$NON-NLS-1$ + + TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB, + input, + output, TRANSLATOR); + } + + @Test + public void testIFNull() throws Exception { + String input = "SELECT ifnull(StringKey, 'otherString') FROM BQT1.SmallA"; + String output = "SELECT NVL(SmallA.StringKey, 'otherString') FROM SmallA"; + //SELECT IFNULL(GL_ACTG_APPL_ID, 'otherString') FROM ACTG_UNIT_BAL_FACT + //SELECT nvl(GL_ACTG_APPL_ID, 'otherString') FROM ACTG_UNIT_BAL_FACT + + TranslationHelper.helpTestVisitor(getTestBQTVDB(), input, output, TRANSLATOR); + } + + + @Test public void testSubstring1() throws Exception { + + //////////BOTH SUBSTRING AND SUBSTR work in NETEZZA// + ////////////////////////////////////////////////////// + String input = "SELECT substring(StringKey, 1) FROM BQT1.SmallA"; + String output = "SELECT substring(SmallA.StringKey, 1) FROM SmallA"; + //SELECT substring(FDL_PMF_ACCT, 3) FROM ACTG_UNIT_BAL_FACT + //SELECT substr(FDL_PMF_ACCT, 3) FROM ACTG_UNIT_BAL_FACT + TranslationHelper.helpTestVisitor(getTestBQTVDB(), input, output, TRANSLATOR); + } + @Test public void testSubstring2() throws Exception { + + //////////BOTH SUBSTRING AND SUBSTR work in NETEZZA// + ////////////////////////////////////////////////////// + String input = "SELECT substring(StringKey, 1, 5) FROM BQT1.SmallA"; + String output = "SELECT substring(SmallA.StringKey, 1, 5) FROM SmallA"; + TranslationHelper.helpTestVisitor(getTestBQTVDB(), input, output, TRANSLATOR); + } + + + + @Test public void testConcat_withLiteral() throws Exception { +// String sql = "select stringnum || '1' from BQT1.Smalla"; +// String expected = "SELECT CASE WHEN SmallA.StringNum IS NULL THEN NULL ELSE concat(SmallA.StringNum, '1') END FROM SmallA"; +// helpTestVisitor(FakeMetadataFactory.exampleBQTCached(), sql, EMPTY_CONTEXT, null, expected); + String input = "select stringnum || '1' from BQT1.Smalla"; + String output = "SELECT (SmallA.StringNum || '1') FROM SmallA"; + + TranslationHelper.helpTestVisitor(getTestBQTVDB(), input, output, TRANSLATOR); + } + + + + ////BEGIN-LOCATE FUNCTION + @Test public void testLocate() throws Exception { + String input = "SELECT locate(INTNUM, 'chimp', 1) FROM BQT1.SMALLA"; + String output = "SELECT INSTR('chimp', cast(SmallA.IntNum AS varchar(4000)), 1) FROM SmallA"; + + TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB, + input, output, + TRANSLATOR); + } + + @Test public void testLocate2() throws Exception { + String input = "SELECT locate(STRINGNUM, 'chimp') FROM BQT1.SMALLA"; + String output = "SELECT INSTR('chimp', SmallA.StringNum) FROM SmallA"; + + TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB, + input, output, + TRANSLATOR); + } + + @Test public void testLocate3() throws Exception { + String input = "SELECT locate(INTNUM, '234567890', 1) FROM BQT1.SMALLA WHERE INTKEY = 26"; + String output = "SELECT INSTR('234567890', cast(SmallA.IntNum AS varchar(4000)), 1) FROM SmallA WHERE SmallA.IntKey = 26"; + + TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB, input, output, TRANSLATOR); + } + + @Test public void testLocate4() throws Exception { + String input = "SELECT locate('c', 'chimp', 1) FROM BQT1.SMALLA"; + String output = "SELECT 1 FROM SmallA"; + + TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB, input, output, TRANSLATOR); + } + + @Test public void testLocate5() throws Exception { + String input = "SELECT locate(STRINGNUM, 'chimp', -5) FROM BQT1.SMALLA"; + String output = "SELECT INSTR('chimp', SmallA.StringNum, 1) FROM SmallA"; + + TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB, input, output, TRANSLATOR); + } + + @Test public void testLocate6() throws Exception { + String input = "SELECT locate(STRINGNUM, 'chimp', INTNUM) FROM BQT1.SMALLA"; + String output = "SELECT INSTR('chimp', SmallA.StringNum, CASE WHEN SmallA.IntNum < 1 THEN 1 ELSE SmallA.IntNum END) FROM SmallA"; + + TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB, input, output, TRANSLATOR); + } + + @Test public void testLocate7() throws Exception { + String input = "SELECT locate(STRINGNUM, 'chimp', LOCATE(STRINGNUM, 'chimp') + 1) FROM BQT1.SMALLA"; + String output = "SELECT INSTR('chimp', SmallA.StringNum, CASE WHEN (INSTR('chimp', SmallA.StringNum) + 1) < 1 THEN 1 ELSE (INSTR('chimp', SmallA.StringNum) + 1) END) FROM SmallA"; + + TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB, input, output, TRANSLATOR); + } + ////END-LOCATE FUNCTION + + + + + //////////////////BEGIN---NUMERIC FUNCTIONS TESTCASES/////////////////// + @Test public void testCeil() throws Exception { + //select ceiling(sqrt(MEAS_PRD_ID)) from ACTG_UNIT_BAL_FACT + //select ceil(sqrt(MEAS_PRD_ID)) from ACTG_UNIT_BAL_FACT + String input = "SELECT ceiling(sqrt(INTKEY)) FROM BQT1.SMALLA"; + String output = "SELECT ceil(sqrt(SmallA.IntKey)) FROM SmallA"; + TranslationHelper.helpTestVisitor(getTestBQTVDB(), + input, + output, TRANSLATOR); + } + + @Test public void testPower() throws Exception { + + //select power(MEAS_PRD_ID, 2) from ACTG_UNIT_BAL_FACT + //select pow(MEAS_PRD_ID, 2) from ACTG_UNIT_BAL_FACT + String input = "SELECT power(INTKEY, 2) FROM BQT1.SMALLA"; + String output = "SELECT pow(SmallA.IntKey, 2) FROM SmallA"; + TranslationHelper.helpTestVisitor(getTestBQTVDB(), + input, + output, TRANSLATOR); + } + //////////////////END---NUMERIC FUNCTIONS TESTCASES/////////////////// + + + //////////////////BEGIN---BIT FUNCTIONS CONVERSION TESTCASES/////////////////// + /////////////////////////////////////////////////////////////////// + + @Test public void testBitAnd() throws Exception { + String input = "select bitand(intkey, intnum) from bqt1.smalla"; + String output = "SELECT intNand(SmallA.IntKey, SmallA.IntNum) FROM SmallA"; + + TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB, + input, + output, TRANSLATOR); + } + @Test public void testBitNot() throws Exception { + String input = "select bitnot(intkey) from bqt1.smalla"; + String output = "SELECT intNnot(SmallA.IntKey) FROM SmallA"; + + TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB, + input, + output, TRANSLATOR); + } + @Test public void testBitOr() throws Exception { + String input = "select bitor(intkey, intnum) from bqt1.smalla"; + String output = "SELECT intNor(SmallA.IntKey, SmallA.IntNum) FROM SmallA"; + + TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB, + input, + output, TRANSLATOR); + } + @Test public void testBitXor() throws Exception { + String input = "select bitxor(intkey, intnum) from bqt1.smalla"; + String output = "SELECT intNxor(SmallA.IntKey, SmallA.IntNum) FROM SmallA"; + + TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB, + input, + output, TRANSLATOR); + } + //////////////////END---BIT FUNCTIONS CONVERSION TESTCASES/////////////////// + ///////////////////////////////////////////////////////////////////////////// + + +} Deleted: trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/netezza/TestNetezzaTranslatorTypeMapping.java =================================================================== --- branches/7.4.x/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/netezza/TestNetezzaTranslatorTypeMapping.java 2011-08-06 14:57:03 UTC (rev 3365) +++ trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/netezza/TestNetezzaTranslatorTypeMapping.java 2011-08-10 18:01:12 UTC (rev 3366) @@ -1,246 +0,0 @@ -/* - * 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 static org.junit.Assert.assertEquals; - -import java.util.Arrays; - -import org.junit.BeforeClass; -import org.junit.Test; -import org.teiid.language.Expression; -import org.teiid.language.Function; -import org.teiid.language.LanguageFactory; -import org.teiid.translator.TranslatorException; -import org.teiid.translator.TypeFacility; -import org.teiid.translator.jdbc.SQLConversionVisitor; -import org.teiid.translator.jdbc.TranslationHelper; - - -public class TestNetezzaTranslatorTypeMapping { - - private static NetezzaExecutionFactory TRANSLATOR; - private static final LanguageFactory LANG_FACTORY = new LanguageFactory(); - - @BeforeClass public static void oneTimeSetup() throws TranslatorException { - TRANSLATOR = new NetezzaExecutionFactory(); - TRANSLATOR.setUseBindVariables(false); - TRANSLATOR.start(); - - - } - - - /////////////////UTILLITY FUNCTIONS///////// - //////////////////////////////////////////// - - private String getTestBQTVDB() { - - return TranslationHelper.BQT_VDB; - } - - - public void helpTest(Expression srcExpression, String tgtType, String expectedExpression) throws Exception { - Function func = LANG_FACTORY.createFunction("convert", - Arrays.asList( srcExpression,LANG_FACTORY.createLiteral(tgtType, String.class)),TypeFacility.getDataTypeClass(tgtType)); - - assertEquals("Error converting from " + srcExpression.getType() + " to " + tgtType, - expectedExpression, helpGetString(func)); - } - public String helpGetString(Expression expr) throws Exception { - SQLConversionVisitor sqlVisitor = TRANSLATOR.getSQLConversionVisitor(); - sqlVisitor.append(expr); - - return sqlVisitor.toString(); - } - - - /////////TYPE MAPPING TESTS///////// - /////////////////////////////////// - - @Test public void testCHARtoChar1() throws Exception { - String input = "SELECT convert(StringKey, CHAR) FROM BQT1.SmallA"; - String output = "SELECT cast(SmallA.StringKey AS char(1)) FROM SmallA"; - - TranslationHelper.helpTestVisitor(getTestBQTVDB(), - input, - output, TRANSLATOR); - } - - @Test public void testLongToBigInt() throws Exception { - String input = "SELECT convert(convert(StringKey, long), string) FROM BQT1.SmallA"; - String output = "SELECT cast(cast(SmallA.StringKey AS bigint) AS varchar(4000)) FROM SmallA"; - - TranslationHelper.helpTestVisitor(getTestBQTVDB(), - input, - output, TRANSLATOR); - } - @Test public void testStringToByte() throws Exception { - String input = "SELECT char(convert(stringnum, byte) + 100) FROM BQT1.SMALLA"; - String output = "SELECT chr((cast(SmallA.StringNum AS byteint) + 100)) FROM SmallA"; - - TranslationHelper.helpTestVisitor(getTestBQTVDB(), - input, - output, TRANSLATOR); - } - @Test public void testStringToShort() throws Exception { - String input = "SELECT char(convert(stringnum, short) + 100) FROM BQT1.SMALLA"; - String output = "SELECT chr((cast(SmallA.StringNum AS smallint) + 100)) FROM SmallA"; - - TranslationHelper.helpTestVisitor(getTestBQTVDB(), - input, - output, TRANSLATOR); - } - @Test public void testStringToLong() throws Exception { -// String input = "SELECT char(convert(PART_WEIGHT, long) + 100) FROM PARTS"; -// String output = "SELECT chr((cast(PARTS.PART_WEIGHT AS bigint) + 100)) FROM PARTS"; - String input = "SELECT convert(stringnum, long) FROM BQT1.SMALLA"; - String output = "SELECT cast(SmallA.StringNum AS bigint) FROM SmallA"; - - TranslationHelper.helpTestVisitor(getTestBQTVDB(), - input, - output, TRANSLATOR); - } - @Test public void testStringToBiginteger() throws Exception { - String input = "SELECT convert(stringnum, BIGINTEGER) FROM BQT1.SMALLA"; - String output = "SELECT cast(SmallA.StringNum AS numeric(38)) FROM SmallA"; - - TranslationHelper.helpTestVisitor(getTestBQTVDB(), - input, - output, TRANSLATOR); - } - - @Test public void testStringToBigdecimal() throws Exception { - String input = "SELECT convert(stringnum, BIGDECIMAL) FROM BQT1.SMALLA"; - String output = "SELECT cast(SmallA.StringNum AS numeric(38,18)) FROM SmallA"; - - TranslationHelper.helpTestVisitor(getTestBQTVDB(), - input, - output, TRANSLATOR); - } - - @Test public void testStringToVarchar() throws Exception { - String input = "SELECT convert(intkey, STRING) FROM BQT1.SMALLA"; - String output = "SELECT cast(SmallA.IntKey AS varchar(4000)) FROM SmallA"; - - TranslationHelper.helpTestVisitor(getTestBQTVDB(), - input, - output, TRANSLATOR); - } - - - - - ////NON-MAPPED TYPES TEST///////////// - ////////////////////////////////////// - - @Test public void testStringToInteger() throws Exception { - String input = "SELECT char(convert(stringnum, integer) + 100) FROM BQT1.SMALLA"; - String output = "SELECT chr((cast(SmallA.StringNum AS integer) + 100)) FROM SmallA"; - - TranslationHelper.helpTestVisitor(getTestBQTVDB(), - input, - output, TRANSLATOR); - } - - @Test public void testStringToFloat() throws Exception { - String input = "SELECT convert(stringnum, float) FROM BQT1.SMALLA"; - String output = "SELECT cast(SmallA.StringNum AS float) FROM SmallA"; - - TranslationHelper.helpTestVisitor(getTestBQTVDB(), - input, - output, TRANSLATOR); - } - @Test public void testStringToReal() throws Exception { - String input = "SELECT convert(stringnum, real) FROM BQT1.SMALLA"; - String output = "SELECT cast(SmallA.StringNum AS float) FROM SmallA"; - - TranslationHelper.helpTestVisitor(getTestBQTVDB(), - input, - output, TRANSLATOR); - } - @Test public void testStringToDouble() throws Exception { - String input = "SELECT convert(stringnum, double) FROM BQT1.SMALLA"; - String output = "SELECT cast(SmallA.StringNum AS double) FROM SmallA"; - //SELECT cast(MEAS_PRD_ID AS DOUBLE) from ACTG_UNIT_BAL_FACT - - TranslationHelper.helpTestVisitor(getTestBQTVDB(), - input, - output, TRANSLATOR); - } - @Test public void testStringToBoolean() throws Exception { - String input = "SELECT convert(stringnum, boolean) FROM BQT1.SMALLA"; - String output = "SELECT CASE WHEN SmallA.StringNum IN ('false', '0') THEN '0' WHEN SmallA.StringNum IS NOT NULL THEN '1' END FROM SmallA"; - - TranslationHelper.helpTestVisitor(getTestBQTVDB(), - input, - output, TRANSLATOR); - } - @Test public void testStringToDate() throws Exception { - String input = "SELECT convert(stringnum, date) FROM BQT1.SMALLA"; - String output = "SELECT to_date(SmallA.StringNum, 'YYYY-MM-DD') FROM SmallA"; - - TranslationHelper.helpTestVisitor(getTestBQTVDB(), - input, - output, TRANSLATOR); - } - @Test public void testStringToTime() throws Exception { - String input = "SELECT convert(stringnum, time) FROM BQT1.SMALLA"; - String output = "SELECT to_timestamp(SmallA.StringNum, 'HH24:MI:SS') FROM SmallA"; - - TranslationHelper.helpTestVisitor(getTestBQTVDB(), - input, - output, TRANSLATOR); - } - @Test public void testStringToTimestamp() throws Exception { - String input = "SELECT convert(stringnum, timestamp) FROM BQT1.SMALLA"; - String output = "SELECT to_timestamp(SmallA.StringNum, 'YYYY-MM-DD HH24:MI:SS.MS') FROM SmallA"; - - TranslationHelper.helpTestVisitor(getTestBQTVDB(), - input, - output, TRANSLATOR); - } - - - @Test public void testbooleanToIntegerConversion() throws Exception { - String input = "SELECT BQT1.SmallA.IntNum, BQT2.SmallB.BooleanValue FROM BQT1.SmallA, BQT2.SmallB WHERE BQT1.SmallA.IntNum = convert(BQT2.SmallB.booleanvalue, integer)"; - String output = "SELECT SmallA.IntNum, SmallB.BooleanValue FROM SmallA, SmallB WHERE SmallA.IntNum = (CASE WHEN SmallB.BooleanValue IN ( '0', 'FALSE') THEN 0 WHEN SmallB.BooleanValue IS NOT NULL THEN 1 END)"; - TranslationHelper.helpTestVisitor(getTestBQTVDB(), - input, - output, TRANSLATOR); - - - } - - @Test public void testIntegerToBooleanconversion() throws Exception { - String input = "SELECT BQT1.SmallA.IntNum, BQT2.SmallB.BooleanValue FROM BQT1.SmallA, BQT2.SmallB WHERE BQT1.SmallA.booleanvalue = convert(BQT2.SmallB.IntNum, boolean) AND BQT1.SmallA.IntKey >= 0 AND BQT2.SmallB.IntKey >= 0 ORDER BY BQT1.SmallA.IntNum"; - String output = "SELECT SmallA.IntNum, SmallB.BooleanValue FROM SmallA, SmallB WHERE SmallA.BooleanValue = CASE WHEN SmallB.IntNum = 0 THEN '0' WHEN SmallB.IntNum IS NOT NULL THEN '1' END AND SmallA.IntKey >= 0 AND SmallB.IntKey >= 0 ORDER BY SmallA.IntNum"; - TranslationHelper.helpTestVisitor(getTestBQTVDB(), - input, - output, TRANSLATOR); - - - } - - -} Copied: trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/netezza/TestNetezzaTranslatorTypeMapping.java (from rev 3365, branches/7.4.x/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/netezza/TestNetezzaTranslatorTypeMapping.java) =================================================================== --- trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/netezza/TestNetezzaTranslatorTypeMapping.java (rev 0) +++ trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/netezza/TestNetezzaTranslatorTypeMapping.java 2011-08-10 18:01:12 UTC (rev 3366) @@ -0,0 +1,246 @@ +/* + * 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 static org.junit.Assert.assertEquals; + +import java.util.Arrays; + +import org.junit.BeforeClass; +import org.junit.Test; +import org.teiid.language.Expression; +import org.teiid.language.Function; +import org.teiid.language.LanguageFactory; +import org.teiid.translator.TranslatorException; +import org.teiid.translator.TypeFacility; +import org.teiid.translator.jdbc.SQLConversionVisitor; +import org.teiid.translator.jdbc.TranslationHelper; + + +public class TestNetezzaTranslatorTypeMapping { + + private static NetezzaExecutionFactory TRANSLATOR; + private static final LanguageFactory LANG_FACTORY = new LanguageFactory(); + + @BeforeClass public static void oneTimeSetup() throws TranslatorException { + TRANSLATOR = new NetezzaExecutionFactory(); + TRANSLATOR.setUseBindVariables(false); + TRANSLATOR.start(); + + + } + + + /////////////////UTILLITY FUNCTIONS///////// + //////////////////////////////////////////// + + private String getTestBQTVDB() { + + return TranslationHelper.BQT_VDB; + } + + + public void helpTest(Expression srcExpression, String tgtType, String expectedExpression) throws Exception { + Function func = LANG_FACTORY.createFunction("convert", + Arrays.asList( srcExpression,LANG_FACTORY.createLiteral(tgtType, String.class)),TypeFacility.getDataTypeClass(tgtType)); + + assertEquals("Error converting from " + srcExpression.getType() + " to " + tgtType, + expectedExpression, helpGetString(func)); + } + public String helpGetString(Expression expr) throws Exception { + SQLConversionVisitor sqlVisitor = TRANSLATOR.getSQLConversionVisitor(); + sqlVisitor.append(expr); + + return sqlVisitor.toString(); + } + + + /////////TYPE MAPPING TESTS///////// + /////////////////////////////////// + + @Test public void testCHARtoChar1() throws Exception { + String input = "SELECT convert(StringKey, CHAR) FROM BQT1.SmallA"; + String output = "SELECT cast(SmallA.StringKey AS char(1)) FROM SmallA"; + + TranslationHelper.helpTestVisitor(getTestBQTVDB(), + input, + output, TRANSLATOR); + } + + @Test public void testLongToBigInt() throws Exception { + String input = "SELECT convert(convert(StringKey, long), string) FROM BQT1.SmallA"; + String output = "SELECT cast(cast(SmallA.StringKey AS bigint) AS varchar(4000)) FROM SmallA"; + + TranslationHelper.helpTestVisitor(getTestBQTVDB(), + input, + output, TRANSLATOR); + } + @Test public void testStringToByte() throws Exception { + String input = "SELECT char(convert(stringnum, byte) + 100) FROM BQT1.SMALLA"; + String output = "SELECT chr((cast(SmallA.StringNum AS byteint) + 100)) FROM SmallA"; + + TranslationHelper.helpTestVisitor(getTestBQTVDB(), + input, + output, TRANSLATOR); + } + @Test public void testStringToShort() throws Exception { + String input = "SELECT char(convert(stringnum, short) + 100) FROM BQT1.SMALLA"; + String output = "SELECT chr((cast(SmallA.StringNum AS smallint) + 100)) FROM SmallA"; + + TranslationHelper.helpTestVisitor(getTestBQTVDB(), + input, + output, TRANSLATOR); + } + @Test public void testStringToLong() throws Exception { +// String input = "SELECT char(convert(PART_WEIGHT, long) + 100) FROM PARTS"; +// String output = "SELECT chr((cast(PARTS.PART_WEIGHT AS bigint) + 100)) FROM PARTS"; + String input = "SELECT convert(stringnum, long) FROM BQT1.SMALLA"; + String output = "SELECT cast(SmallA.StringNum AS bigint) FROM SmallA"; + + TranslationHelper.helpTestVisitor(getTestBQTVDB(), + input, + output, TRANSLATOR); + } + @Test public void testStringToBiginteger() throws Exception { + String input = "SELECT convert(stringnum, BIGINTEGER) FROM BQT1.SMALLA"; + String output = "SELECT cast(SmallA.StringNum AS numeric(38)) FROM SmallA"; + + TranslationHelper.helpTestVisitor(getTestBQTVDB(), + input, + output, TRANSLATOR); + } + + @Test public void testStringToBigdecimal() throws Exception { + String input = "SELECT convert(stringnum, BIGDECIMAL) FROM BQT1.SMALLA"; + String output = "SELECT cast(SmallA.StringNum AS numeric(38,18)) FROM SmallA"; + + TranslationHelper.helpTestVisitor(getTestBQTVDB(), + input, + output, TRANSLATOR); + } + + @Test public void testStringToVarchar() throws Exception { + String input = "SELECT convert(intkey, STRING) FROM BQT1.SMALLA"; + String output = "SELECT cast(SmallA.IntKey AS varchar(4000)) FROM SmallA"; + + TranslationHelper.helpTestVisitor(getTestBQTVDB(), + input, + output, TRANSLATOR); + } + + + + + ////NON-MAPPED TYPES TEST///////////// + ////////////////////////////////////// + + @Test public void testStringToInteger() throws Exception { + String input = "SELECT char(convert(stringnum, integer) + 100) FROM BQT1.SMALLA"; + String output = "SELECT chr((cast(SmallA.StringNum AS integer) + 100)) FROM SmallA"; + + TranslationHelper.helpTestVisitor(getTestBQTVDB(), + input, + output, TRANSLATOR); + } + + @Test public void testStringToFloat() throws Exception { + String input = "SELECT convert(stringnum, float) FROM BQT1.SMALLA"; + String output = "SELECT cast(SmallA.StringNum AS float) FROM SmallA"; + + TranslationHelper.helpTestVisitor(getTestBQTVDB(), + input, + output, TRANSLATOR); + } + @Test public void testStringToReal() throws Exception { + String input = "SELECT convert(stringnum, real) FROM BQT1.SMALLA"; + String output = "SELECT cast(SmallA.StringNum AS float) FROM SmallA"; + + TranslationHelper.helpTestVisitor(getTestBQTVDB(), + input, + output, TRANSLATOR); + } + @Test public void testStringToDouble() throws Exception { + String input = "SELECT convert(stringnum, double) FROM BQT1.SMALLA"; + String output = "SELECT cast(SmallA.StringNum AS double) FROM SmallA"; + //SELECT cast(MEAS_PRD_ID AS DOUBLE) from ACTG_UNIT_BAL_FACT + + TranslationHelper.helpTestVisitor(getTestBQTVDB(), + input, + output, TRANSLATOR); + } + @Test public void testStringToBoolean() throws Exception { + String input = "SELECT convert(stringnum, boolean) FROM BQT1.SMALLA"; + String output = "SELECT CASE WHEN SmallA.StringNum IN ('false', '0') THEN '0' WHEN SmallA.StringNum IS NOT NULL THEN '1' END FROM SmallA"; + + TranslationHelper.helpTestVisitor(getTestBQTVDB(), + input, + output, TRANSLATOR); + } + @Test public void testStringToDate() throws Exception { + String input = "SELECT convert(stringnum, date) FROM BQT1.SMALLA"; + String output = "SELECT to_date(SmallA.StringNum, 'YYYY-MM-DD') FROM SmallA"; + + TranslationHelper.helpTestVisitor(getTestBQTVDB(), + input, + output, TRANSLATOR); + } + @Test public void testStringToTime() throws Exception { + String input = "SELECT convert(stringnum, time) FROM BQT1.SMALLA"; + String output = "SELECT to_timestamp(SmallA.StringNum, 'HH24:MI:SS') FROM SmallA"; + + TranslationHelper.helpTestVisitor(getTestBQTVDB(), + input, + output, TRANSLATOR); + } + @Test public void testStringToTimestamp() throws Exception { + String input = "SELECT convert(stringnum, timestamp) FROM BQT1.SMALLA"; + String output = "SELECT to_timestamp(SmallA.StringNum, 'YYYY-MM-DD HH24:MI:SS.MS') FROM SmallA"; + + TranslationHelper.helpTestVisitor(getTestBQTVDB(), + input, + output, TRANSLATOR); + } + + + @Test public void testbooleanToIntegerConversion() throws Exception { + String input = "SELECT BQT1.SmallA.IntNum, BQT2.SmallB.BooleanValue FROM BQT1.SmallA, BQT2.SmallB WHERE BQT1.SmallA.IntNum = convert(BQT2.SmallB.booleanvalue, integer)"; + String output = "SELECT SmallA.IntNum, SmallB.BooleanValue FROM SmallA, SmallB WHERE SmallA.IntNum = (CASE WHEN SmallB.BooleanValue IN ( '0', 'FALSE') THEN 0 WHEN SmallB.BooleanValue IS NOT NULL THEN 1 END)"; + TranslationHelper.helpTestVisitor(getTestBQTVDB(), + input, + output, TRANSLATOR); + + + } + + @Test public void testIntegerToBooleanconversion() throws Exception { + String input = "SELECT BQT1.SmallA.IntNum, BQT2.SmallB.BooleanValue FROM BQT1.SmallA, BQT2.SmallB WHERE BQT1.SmallA.booleanvalue = convert(BQT2.SmallB.IntNum, boolean) AND BQT1.SmallA.IntKey >= 0 AND BQT2.SmallB.IntKey >= 0 ORDER BY BQT1.SmallA.IntNum"; + String output = "SELECT SmallA.IntNum, SmallB.BooleanValue FROM SmallA, SmallB WHERE SmallA.BooleanValue = CASE WHEN SmallB.IntNum = 0 THEN '0' WHEN SmallB.IntNum IS NOT NULL THEN '1' END AND SmallA.IntKey >= 0 AND SmallB.IntKey >= 0 ORDER BY SmallA.IntNum"; + TranslationHelper.helpTestVisitor(getTestBQTVDB(), + input, + output, TRANSLATOR); + + + } + + +} Deleted: trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/netezza/TestSubstringFunctionModifier.java =================================================================== --- branches/7.4.x/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/netezza/TestSubstringFunctionModifier.java 2011-08-06 14:57:03 UTC (rev 3365) +++ trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/netezza/TestSubstringFunctionModifier.java 2011-08-10 18:01:12 UTC (rev 3366) @@ -1,98 +0,0 @@ -/* - * 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.Arrays; - -import junit.framework.TestCase; - -import org.teiid.language.Expression; -import org.teiid.language.Function; -import org.teiid.language.LanguageFactory; -import org.teiid.translator.TypeFacility; -import org.teiid.translator.jdbc.SQLConversionVisitor; - -/** - */ -public class TestSubstringFunctionModifier extends TestCase { - - private static final LanguageFactory LANG_FACTORY = new LanguageFactory(); - - - /** - * Constructor for TestSubstringFunctionModifier. - * @param name - */ - public TestSubstringFunctionModifier(String name) { - super(name); - } - - public void helpTestMod(Expression[] args, String expectedStr) throws Exception { - Function func = LANG_FACTORY.createFunction("substring", - Arrays.asList(args), TypeFacility.RUNTIME_TYPES.STRING); - - NetezzaExecutionFactory trans = new NetezzaExecutionFactory(); - trans.start(); - - SQLConversionVisitor sqlVisitor = trans.getSQLConversionVisitor(); - sqlVisitor.append(func); - - assertEquals(expectedStr, sqlVisitor.toString()); - } - - public void testTwoArgs() throws Exception { - Expression[] args = new Expression[] { - LANG_FACTORY.createLiteral("a.b.c", String.class), - LANG_FACTORY.createLiteral(new Integer(1), Integer.class) - }; - helpTestMod(args, "substring('a.b.c', 1)"); - } - - public void testThreeArgsWithConstant() throws Exception { - Expression[] args = new Expression[] { - LANG_FACTORY.createLiteral("a.b.c", String.class), - LANG_FACTORY.createLiteral(new Integer(3), Integer.class), - LANG_FACTORY.createLiteral(new Integer(1), Integer.class) - }; - helpTestMod(args, "substring('a.b.c', 3, 1)"); - } - - public void testThreeArgsWithElement() throws Exception { - Expression[] args = new Expression[] { - LANG_FACTORY.createLiteral("a.b.c", String.class), - LANG_FACTORY.createColumnReference("e1", null, null, Integer.class), - LANG_FACTORY.createLiteral(new Integer(1), Integer.class) - }; - helpTestMod(args, "substring('a.b.c', e1, 1)"); - } - - public void testThreeArgsWithNull() throws Exception { - Expression[] args = new Expression[] { - LANG_FACTORY.createLiteral("a.b.c", String.class), - LANG_FACTORY.createLiteral(null, Integer.class), - LANG_FACTORY.createLiteral(new Integer(5), Integer.class) - }; - helpTestMod(args, "substring('a.b.c', NULL, 5)"); - } - -} Copied: trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/netezza/TestSubstringFunctionModifier.java (from rev 3365, branches/7.4.x/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/netezza/TestSubstringFunctionModifier.java) =================================================================== --- trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/netezza/TestSubstringFunctionModifier.java (rev 0) +++ trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/netezza/TestSubstringFunctionModifier.java 2011-08-10 18:01:12 UTC (rev 3366) @@ -0,0 +1,98 @@ +/* + * 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.Arrays; + +import junit.framework.TestCase; + +import org.teiid.language.Expression; +import org.teiid.language.Function; +import org.teiid.language.LanguageFactory; +import org.teiid.translator.TypeFacility; +import org.teiid.translator.jdbc.SQLConversionVisitor; + +/** + */ +public class TestSubstringFunctionModifier extends TestCase { + + private static final LanguageFactory LANG_FACTORY = new LanguageFactory(); + + + /** + * Constructor for TestSubstringFunctionModifier. + * @param name + */ + public TestSubstringFunctionModifier(String name) { + super(name); + } + + public void helpTestMod(Expression[] args, String expectedStr) throws Exception { + Function func = LANG_FACTORY.createFunction("substring", + Arrays.asList(args), TypeFacility.RUNTIME_TYPES.STRING); + + NetezzaExecutionFactory trans = new NetezzaExecutionFactory(); + trans.start(); + + SQLConversionVisitor sqlVisitor = trans.getSQLConversionVisitor(); + sqlVisitor.append(func); + + assertEquals(expectedStr, sqlVisitor.toString()); + } + + public void testTwoArgs() throws Exception { + Expression[] args = new Expression[] { + LANG_FACTORY.createLiteral("a.b.c", String.class), + LANG_FACTORY.createLiteral(new Integer(1), Integer.class) + }; + helpTestMod(args, "substring('a.b.c', 1)"); + } + + public void testThreeArgsWithConstant() throws Exception { + Expression[] args = new Expression[] { + LANG_FACTORY.createLiteral("a.b.c", String.class), + LANG_FACTORY.createLiteral(new Integer(3), Integer.class), + LANG_FACTORY.createLiteral(new Integer(1), Integer.class) + }; + helpTestMod(args, "substring('a.b.c', 3, 1)"); + } + + public void testThreeArgsWithElement() throws Exception { + Expression[] args = new Expression[] { + LANG_FACTORY.createLiteral("a.b.c", String.class), + LANG_FACTORY.createColumnReference("e1", null, null, Integer.class), + LANG_FACTORY.createLiteral(new Integer(1), Integer.class) + }; + helpTestMod(args, "substring('a.b.c', e1, 1)"); + } + + public void testThreeArgsWithNull() throws Exception { + Expression[] args = new Expression[] { + LANG_FACTORY.createLiteral("a.b.c", String.class), + LANG_FACTORY.createLiteral(null, Integer.class), + LANG_FACTORY.createLiteral(new Integer(5), Integer.class) + }; + helpTestMod(args, "substring('a.b.c', NULL, 5)"); + } + +} Modified: trunk/connectors/translator-ldap/src/main/java/org/teiid/translator/ldap/LDAPExecutionFactory.java =================================================================== --- trunk/connectors/translator-ldap/src/main/java/org/teiid/translator/ldap/LDAPExecutionFactory.java 2011-08-06 14:57:03 UTC (rev 3365) +++ trunk/connectors/translator-ldap/src/main/java/org/teiid/translator/ldap/LDAPExecutionFactory.java 2011-08-10 18:01:12 UTC (rev 3366) @@ -53,6 +53,7 @@ private String searchDefaultBaseDN; private boolean restrictToObjectClass; private SearchDefaultScope searchDefaultScope = SearchDefaultScope.ONELEVEL_SCOPE; + private boolean usePagination; public LDAPExecutionFactory() { this.setMaxInCriteriaSize(1000); @@ -89,7 +90,7 @@ @Override public ResultSetExecution createResultSetExecution(QueryExpression command,ExecutionContext executionContext, RuntimeMetadata metadata, LdapContext context) throws TranslatorException { - return new LDAPSyncQueryExecution((Select)command, this, context); + return new LDAPSyncQueryExecution((Select)command, this, executionContext, context); } @Override @@ -142,5 +143,15 @@ @Override public boolean supportsNotCriteria() { return true; + } + + @TranslatorProperty(display="Use Pagination", description="Use a PagedResultsControl to page through large results. This is not supported by all directory servers.") + public boolean usePagination() { + return usePagination; } + + public void setUsePagination(boolean usePagination) { + this.usePagination = usePagination; + } + } Modified: trunk/connectors/translator-ldap/src/main/java/org/teiid/translator/ldap/LDAPSyncQueryExecution.java =================================================================== --- trunk/connectors/translator-ldap/src/main/java/org/teiid/translator/ldap/LDAPSyncQueryExecution.java 2011-08-06 14:57:03 UTC (rev 3365) +++ trunk/connectors/translator-ldap/src/main/java/org/teiid/translator/ldap/LDAPSyncQueryExecution.java 2011-08-10 18:01:12 UTC (rev 3366) @@ -91,6 +91,8 @@ import javax.naming.directory.SearchResult; import javax.naming.ldap.Control; import javax.naming.ldap.LdapContext; +import javax.naming.ldap.PagedResultsControl; +import javax.naming.ldap.PagedResultsResponseControl; import javax.naming.ldap.SortControl; import javax.naming.ldap.SortKey; @@ -98,6 +100,7 @@ import org.teiid.logging.LogConstants; import org.teiid.logging.LogManager; import org.teiid.metadata.Column; +import org.teiid.translator.ExecutionContext; import org.teiid.translator.ResultSetExecution; import org.teiid.translator.TranslatorException; import org.teiid.translator.TypeFacility; @@ -119,6 +122,8 @@ private IQueryToLdapSearchParser parser; private Select query; private LDAPExecutionFactory executionFactory; + private ExecutionContext executionContext; + private SearchControls ctrls; /** * Constructor @@ -127,10 +132,11 @@ * @param logger the ConnectorLogger * @param ldapCtx the LDAP Context */ - public LDAPSyncQueryExecution(Select query, LDAPExecutionFactory factory, LdapContext ldapCtx) { + public LDAPSyncQueryExecution(Select query, LDAPExecutionFactory factory, ExecutionContext context, LdapContext ldapCtx) { this.ldapConnection = ldapCtx; this.query = query; this.executionFactory = factory; + this.executionContext = context; } /** @@ -146,31 +152,40 @@ // Create and configure the new search context. createSearchContext(); - SearchControls ctrls = setSearchControls(); - setStandardRequestControls(); + ctrls = setSearchControls(); + String ctxName = searchDetails.getContextName(); + String filter = searchDetails.getContextFilter(); + if (ctxName == null || filter == null || ctrls == null) { + throw new TranslatorException(LogConstants.CTX_CONNECTOR, "Search context, filter, or controls were null. Cannot execute search."); //$NON-NLS-1$ + } + setRequestControls(null); // Execute the search. - executeSearch(ctrls); + executeSearch(); } /** * Set the standard request controls */ - private void setStandardRequestControls() throws TranslatorException { - Control[] sortCtrl = new Control[1]; + private void setRequestControls(byte[] cookie) throws TranslatorException { + List<Control> ctrl = new ArrayList<Control>(); SortKey[] keys = searchDetails.getSortKeys(); - if (keys != null) { - try { - sortCtrl[0] = new SortControl(keys, Control.NONCRITICAL); - this.ldapCtx.setRequestControls(sortCtrl); - LogManager.logTrace(LogConstants.CTX_CONNECTOR, "Sort ordering was requested, and sort control was created successfully."); //$NON-NLS-1$ - } catch (NamingException ne) { - final String msg = LDAPPlugin.Util.getString("LDAPSyncQueryExecution.setControlsError") + //$NON-NLS-1$ - " : "+ne.getExplanation(); //$NON-NLS-1$ - throw new TranslatorException(msg); - } catch(IOException e) { - final String msg = LDAPPlugin.Util.getString("LDAPSyncQueryExecution.setControlsError"); //$NON-NLS-1$ - throw new TranslatorException(e,msg); + try { + if (keys != null) { + ctrl.add(new SortControl(keys, Control.NONCRITICAL)); } + if (this.executionFactory.usePagination()) { + ctrl.add(new PagedResultsControl(this.executionContext.getBatchSize(), cookie, Control.CRITICAL)); + } + if (!ctrl.isEmpty()) { + this.ldapCtx.setRequestControls(ctrl.toArray(new Control[ctrl.size()])); + LogManager.logTrace(LogConstants.CTX_CONNECTOR, "Sort/pagination controls were created successfully."); //$NON-NLS-1$ + } + } catch (NamingException ne) { + final String msg = LDAPPlugin.Util.getString("LDAPSyncQueryExecution.setControlsError") + //$NON-NLS-1$ + " : "+ne.getExplanation(); //$NON-NLS-1$ + throw new TranslatorException(ne, msg); + } catch(IOException e) { + throw new TranslatorException(e); } } @@ -219,22 +234,14 @@ * Perform the LDAP search against the subcontext, using the filter and * search controls appropriate to the query and model metadata. */ - private void executeSearch(SearchControls ctrls) throws TranslatorException { - String ctxName = searchDetails.getContextName(); + private void executeSearch() throws TranslatorException { String filter = searchDetails.getContextFilter(); - if (ctxName == null || filter == null || ctrls == null) { - LogManager.logError(LogConstants.CTX_CONNECTOR, "Search context, filter, or controls were null. Cannot execute search."); //$NON-NLS-1$ - } try { searchEnumeration = this.ldapCtx.search("", filter, ctrls); //$NON-NLS-1$ } catch (NamingException ne) { - LogManager.logError(LogConstants.CTX_CONNECTOR, "LDAP search failed. Attempted to search context " //$NON-NLS-1$ - + ctxName + " using filter " + filter); //$NON-NLS-1$ final String msg = LDAPPlugin.Util.getString("LDAPSyncQueryExecution.execSearchError"); //$NON-NLS-1$ - throw new TranslatorException(msg + " : " + ne.getExplanation()); //$NON-NLS-1$ + throw new TranslatorException(ne, msg + " : " + ne.getExplanation()); //$NON-NLS-1$ } catch(Exception e) { - LogManager.logError(LogConstants.CTX_CONNECTOR, "LDAP search failed. Attempted to search context " //$NON-NLS-1$ - + ctxName + " using filter " + filter); //$NON-NLS-1$ final String msg = LDAPPlugin.Util.getString("LDAPSyncQueryExecution.execSearchError"); //$NON-NLS-1$ throw new TranslatorException(e, msg); } @@ -294,6 +301,27 @@ SearchResult searchResult = (SearchResult) searchEnumeration.next(); result = getRow(searchResult); } + + if (result == null && this.executionFactory.usePagination()) { + byte[] cookie = null; + Control[] controls = ldapCtx.getResponseControls(); + if (controls != null) { + for (int i = 0; i < controls.length; i++) { + if (controls[i] instanceof PagedResultsResponseControl) { + PagedResultsResponseControl prrc = (PagedResultsResponseControl)controls[i]; + cookie = prrc.getCookie(); + } + } + } + + if (cookie == null) { + return null; + } + + setRequestControls(cookie); + executeSearch(); + return next(); + } return result; } catch (SizeLimitExceededException e) { @@ -451,22 +479,4 @@ } } - - /** - * Active Directory and OpenLDAP supports PagedResultsControls, so I left - * this method in here in case we decide to extend support for this control - * in the future. - */ -// private void setADRequestControls(int maxBatchSize) { -// try { -// ldapCtx.setRequestControls(new Control[] { new PagedResultsControl( -// maxBatchSize, Control.CRITICAL) }); -// } catch (NamingException ne) { -// logger.logError("Failed to set page size for LDAP results. Please ensure that paged results controls are supported by the LDAP server implementation."); //$NON-NLS-1$ -// ne.printStackTrace(); -// } catch (IOException ioe) { -// logger.logError("IO Exception while setting paged results control."); //$NON-NLS-1$ -// ioe.printStackTrace(); -// } -// } } Modified: trunk/documentation/admin-guide/src/main/docbook/en-US/content/admin-console.xml =================================================================== --- trunk/documentation/admin-guide/src/main/docbook/en-US/content/admin-console.xml 2011-08-06 14:57:03 UTC (rev 3365) +++ trunk/documentation/admin-guide/src/main/docbook/en-US/content/admin-console.xml 2011-08-10 18:01:12 UTC (rev 3366) @@ -1,92 +1,199 @@ -<?xml version="1.0" encoding="UTF-8"?> +<?xml version='1.0'?> <!DOCTYPE chapter PUBLIC "-//OASIS//DTD DocBook XML V4.5//EN" "http://www.oasis-open.org/docbook/xml/4.5/docbookx.dtd"> <chapter id="admin-console"> - <title>Teiid Admin Console</title> - <para>The Teiid Admin Console is a web based administrative and monitoring tool for Teiid. - Teiid's Admin Console is built using the <ulink url="http://www.jboss.org/embjopr">Embedded JOPR</ulink> library and adds a additional plugin into the Embeeded JOPR program already available in the - <ulink url="http://www.jboss.org/jbossas">JBoss AS</ulink>.</para> - - <mediaobject> - <imageobject> - <imagedata scalefit="1" fileref="../images/admin_console.png"/> - </imageobject> - </mediaobject> - - <section> - <title>What can be monitored and/or configured?</title> - <para>Here are the steps to follow to install Teiid</para> - - <orderedlist> - <listitem> <para><emphasis>The Teiid Runtime Engine</emphasis> (Data Services node in the tree)</para> </listitem> - <listitem> <para><emphasis>VDBs</emphasis> - Virtual databases</para> + <title>Teiid Admin Console</title> + <para>The Teiid Admin Console is a web based administrative and + monitoring tool for Teiid. Teiid&apos;s Admin Console is built using + the <ulink url="http://www.jboss.org/embjopr">Embedded JOPR</ulink> + library and adds a additional plugin into the Embedded JOPR program + already available in the <ulink url="http://www.jboss.org/jbossas"> + JBoss AS</ulink>.</para> + <mediaobject><imageobject> + <imagedata fileref="../images/admin_console.png" scalefit="1"/></imageobject></mediaobject> + <section> + <title>What can be monitored and/or configured?</title> + <para>Here are the steps to follow to install Teiid</para> + <orderedlist> + <listitem> + <para><emphasis>The Teiid Runtime Engine</emphasis> (Data + Services node in the tree)</para> + </listitem> + <listitem> + <para><emphasis>VDBs</emphasis> - Virtual databases</para> + <orderedlist> + <listitem> + <para><emphasis>Models</emphasis></para> <orderedlist> - <listitem> <para><emphasis>Models</emphasis></para> - <orderedlist> - <listitem> <para><emphasis>Source</emphasis>- these are physical sources</para></listitem> - <listitem> <para><emphasis>Multi-source</emphasis> - these are multiple sourced models</para></listitem> - <listitem> <para><emphasis>Logical</emphasis> - these are virtual sources</para></listitem> - </orderedlist> - </listitem> - <listitem> <para><emphasis>Translator instances</emphasis>- any Translator instances defined for use by this VDB</para> </listitem> + <listitem> + <para><emphasis>Source</emphasis>- these are physical + sources</para> + </listitem> + <listitem> + <para><emphasis>Multi-source</emphasis> - these are + multiple sourced models</para> + </listitem> + <listitem> + <para><emphasis>Logical</emphasis> - these are virtual + sources</para> + </listitem> </orderedlist> + </listitem> + <listitem> + <para><emphasis>Translator instances</emphasis>- any + Translator instances defined for use by this VDB</para> + </listitem> + <listitem> + <para><emphasis>Data Roles</emphasis>- any + data roles defined for use by this VDB</para> + </listitem> + </orderedlist> + </listitem> + <listitem> + <para>Translators - These are the extensions to supported + datasources that come with Teiid out-of-the-box.</para> + </listitem> + </orderedlist> + <note> + <para>The creation/modification of the datasource is managed by + the JBossAS plugin.</para> + </note> + <section> + <title>Configuration</title> + <orderedlist> + <listitem> + <para>Settings for the Data Services plugin</para> </listitem> - <listitem> <para>Translators - These are the extensions to supported datasources that come with Teiid out-of-the-box.</para> </listitem> - </orderedlist> - - <note> - <para>The creation/modification of the datasource is managed by the JBossAS plugin.</para> - </note> - + <listitem> + <para>Runtime Engine properties</para> + </listitem> + <listitem> + <para>Buffer Service</para> + </listitem> + <listitem> + <para>Jdbc Socket configuration</para> + </listitem> + <listitem> + <para>Session Service</para> + </listitem> + </orderedlist> + </section> <section> - <title>Configuration</title> - <orderedlist> - <listitem> <para>Runtime Engine properties</para> </listitem> - <listitem> <para>Buffer Service</para> </listitem> - <listitem> <para>Jdbc Socket configuration</para> </listitem> - <listitem> <para>Session Service</para> </listitem> - </orderedlist> + <title>Metrics</title> + <orderedlist> + <listitem> + <para>Long Running Query count</para> + </listitem> + <listitem> + <para>Active Query count</para> + </listitem> + <listitem> + <para>Active Session count</para> + </listitem> + <listitem> + <para>Prepared Plan Cache Hit Ratio %</para> + </listitem> + <listitem> + <para>Prepared Plan Cache Size</para> + </listitem> + <listitem> + <para>Prepared Plan Cache # of Requests</para> + </listitem> + <listitem> + <para>ResultSet Cache Hit Ratio %</para> + </listitem> + <listitem> + <para>ResultSet Cache Size</para> + </listitem> + <listitem> + <para>ResultSet Cache # of Requests</para> + </listitem> + <listitem> + <para>Used Buffer Space</para> + </listitem> + </orderedlist> </section> - <section> - <title>Metrics</title> + <title>Control (Operations)</title> + <listitem> + <para>Data Services Engine</para> <orderedlist> - <listitem> <para>Long Running Query count</para> </listitem> - <listitem> <para>Active Query count</para> </listitem> - <listitem> <para>Active Session count</para> </listitem> + <listitem> + <para>View Long Running Queries</para> + </listitem> + <listitem> + <para>View Current Sessions</para> + </listitem> + <listitem> + <para>Deploy a VDB via URL</para> + </listitem> + <listitem> + <para>Terminate Session</para> + </listitem> + <listitem> + <para>View Current Requests</para> + </listitem> + <listitem> + <para>Terminate requests</para> + </listitem> + <listitem> + <para>View Current Transactions</para> + </listitem> + <listitem> + <para>Terminate Transaction</para> + </listitem> </orderedlist> - </section> - - <section> - <title>Control (Operations)</title> + </listitem> + <listitem> + <para>Virtual Database</para> <orderedlist> - <listitem> <para>View Long Running Queries</para> </listitem> - <listitem> <para>View Current Sessions</para> </listitem> - <listitem> <para>Terminate Session</para> </listitem> - <listitem> <para>View Current Requests</para> </listitem> - <listitem> <para>Terminate requests</para> </listitem> - <listitem> <para>View Current Transactions</para> </listitem> - <listitem> <para>Terminate Transaction</para> </listitem> + <listitem> + <para>View VDB Requests</para> + </listitem> + <listitem> + <para>View VDB Sessions</para> + </listitem> + <listitem> + <para>List Materialized View Info</para> + </listitem> + <listitem> + <para>Refresh a Materialized View</para> + </listitem> + <listitem> + <para>Clear Cache</para> + </listitem> </orderedlist> + </listitem> </section> - <section id="vdb_deploy"> - <title>Deploying the VDB</title> - <para>VDB archive files created it the Designer Tool or Dynamic VDBs can be deployed into Teiid server using - the Admin Console.</para> - - <orderedlist> - <listitem> <para>Select the Virtual Database node in the Admin Console tree and click the Add New Resource button.</para> </listitem> - <listitem> <para>Select the VDB archive file from the file system and click continue.</para> </listitem> - <listitem> <para>The VDB will deploy if no fatal errors are found in the archive. The status of - the VDB will be UP if no errors are found with the models in the VDB.</para> </listitem> - <listitem> <para>If there are model errors, the VDB will be deployed with a status of DOWN and the errors will be - listed on the configuration tab of the VDB. VDBs that are not UP will be marked with a red X in the tree.</para> </listitem> - </orderedlist> - - <para>Only Model's "connection-jndi-name" can be modified using this tool by clicking on the "configuration" - tab, all other proeprties are read-only.</para> - </section> - - </section> - -</chapter> \ No newline at end of file + <title>Deploying the VDB</title> + <para>VDB archive files created it the Designer Tool or Dynamic + VDBs can be deployed into Teiid server using the Admin + Console.</para> + <orderedlist> + <listitem> + <para>Select the Virtual Database node in the Admin Console + tree and click the Add New Resource button.</para> + </listitem> + <listitem> + <para>Select the VDB archive file from the file system and + click continue.</para> + </listitem> + <listitem> + <para>The VDB will deploy if no fatal errors are found in the + archive. The status of the VDB will be UP if no errors are + found with the models in the VDB.</para> + </listitem> + <listitem> + <para>If there are model errors, the VDB will be deployed + with a status of DOWN and the errors will be listed on the + configuration tab of the VDB. VDBs that are not UP will be + marked with a red X in the tree.</para> + </listitem> + </orderedlist> + <para>Only Model&apos;s &quot;connection-jndi-name&quot; can be + modified using this tool by clicking on the + &quot;configuration&quot; tab, all other properties are + read-only.</para> + </section> + </section> +</chapter> Modified: trunk/documentation/admin-guide/src/main/docbook/en-US/images/admin_console.png =================================================================== (Binary files differ) Modified: trunk/documentation/caching-guide/src/main/docbook/en-US/content/codetable.xml =================================================================== --- trunk/documentation/caching-guide/src/main/docbook/en-US/content/codetable.xml 2011-08-06 14:57:03 UTC (rev 3365) +++ trunk/documentation/caching-guide/src/main/docbook/en-US/content/codetable.xml 2011-08-10 18:01:12 UTC (rev 3366) @@ -29,7 +29,7 @@ <para>See the Reference for more information on use of the lookup function.</para> <example> <title>Country Code Lookup</title> - <programlisting>lookup(‘ISOCountryCodes, ‘CountryName’, ‘CountryCode’, ‘US’)</programlisting> + <programlisting>lookup('ISOCountryCodes', 'CountryName', 'CountryCode', 'US')</programlisting> </example> </section> <section> @@ -47,7 +47,12 @@ </section> <section> <title>Materialized View Alternative</title> - <para>The lookup function is a shortcut to create an internal materialized view. In many situations, it may be better to directly create the analogous materialized view rather than to use a code table.</para> + <para>The lookup function is a shortcut to create an internal materialized view with an appropriate primary key. In many situations, it may be better to directly create the analogous materialized view rather than to use a code table.</para> + <example> + <title>Country Code Lookup Against A Mat View</title> + <programlisting>SELECT (SELECT CountryCode From MatISOCountryCodes WHERE CountryName = tbl.CountryName) as cc FROM tbl</programlisting> + <para>Here MatISOCountryCodes is a view selecting from ISOCountryCodes that has been marked as materialized and has a primary key or index on CountryName. The scalar subquery will use the index to lookup the country code for each country name in tbl.</para> + </example> <itemizedlist> <title>Reasons to use a materialized view:</title> <listitem> @@ -63,7 +68,7 @@ <para>The ability to use <link linkend="nocache">OPTION NOCACHE</link>.</para> </listitem> <listitem> - <para>Usage of a materialized view lookup as an uncorrelated subquery is no different than the use of the lookup function.</para> + <para>There is almost no performance difference.</para> </listitem> </itemizedlist> <orderedlist> @@ -72,7 +77,7 @@ <para>Create a view selecting the appropriate columns from the desired table. In general, this view may have an arbitrarily complicated transformation query.</para> </listitem> <listitem> - <para>Designate the appropriate column(s) as the primary key.</para> + <para>Designate the appropriate column(s) as the primary key. Additional indexes can be added if needed.</para> </listitem> <listitem> <para>Set the materialized property to true.</para> Modified: trunk/documentation/reference/src/main/docbook/en-US/content/scalar_functions.xml =================================================================== --- trunk/documentation/reference/src/main/docbook/en-US/content/scalar_functions.xml 2011-08-06 14:57:03 UTC (rev 3365) +++ trunk/documentation/reference/src/main/docbook/en-US/content/scalar_functions.xml 2011-08-10 18:01:12 UTC (rev 3366) @@ -1824,20 +1824,21 @@ <para><synopsis>LOOKUP(codeTable, returnColumn, keyColumn, keyValue)</synopsis></para> <para>In the lookup table codeTable, find the row where keyColumn has the value keyValue and return the - associated returnColumn. codeTable must be a fully-qualified string - literal containing metadata identifiers, keyValue datatype - must match datatype of the keyColumn, return datatype - matches that of returnColumn. returnColumn and - keyColumn parameters should use their shortened names. + associated returnColumn value or null if no matching key is found. codeTable must be a string literal that is the fully-qualified name of the target table. + returnColumn and key Column must also be string literals of just the relevant column names. + The keyValue can be any expression that must match the datatype of the keyColumn. The return datatype + matches that of returnColumn. </para> - <para>For example, a StatePostalCodes table used to translate postal codes to - complete state names might represent an example of this type of - lookup table. One column, PostalCode, represents a key column. - Other tables refer to this two-letter code. A - second column, StateDisplayName, would represent the complete name - of the state. Hence, a query to this lookup table would typically - provide the PostalCode and expect the StateDisplayName in response. - </para> + <example> + <title>Country Code Lookup</title> + <programlisting>lookup('ISOCountryCodes', 'CountryName', 'CountryCode', 'US')</programlisting> + <para>A ISOCountryCodes table used to translate country name to + ISO codes. One column, CountryName, represents a key column. + A second column, CountryCode, would represent the ISO code of the country. + Hence, a query to this lookup table would + provide a CountryName, shown above as 'US', and expect a CountryCode value in response. + </para> + </example> <para>When you call this function for any combination of codeTable, returnColumn, and keyColumn for the first time, the Teiid System caches the result. The Teiid System uses this cache for all @@ -1846,6 +1847,7 @@ the Teiid System. Thus, you should not use this function for data that is subject to updates. Instead, you can use it against static data that does not change over time.</para> + <para>See the Caching Guide for more on the caching aspects of the lookup function.</para> <note> <itemizedlist> <listitem> Modified: trunk/documentation/reference/src/main/docbook/en-US/content/translators.xml =================================================================== --- trunk/documentation/reference/src/main/docbook/en-US/content/translators.xml 2011-08-06 14:57:03 UTC (rev 3365) +++ trunk/documentation/reference/src/main/docbook/en-US/content/translators.xml 2011-08-10 18:01:12 UTC (rev 3366) @@ -545,6 +545,11 @@ <entry>Restrict Searches to objectClass named in the Name field for a table</entry> <entry>false</entry> </row> + <row> + <entry>UsePagination</entry> + <entry>Use a PagedResultsControl to page through large results. This is not supported by all directory servers.</entry> + <entry>false</entry> + </row> </tbody> </tgroup> </table> Modified: trunk/engine/src/main/java/org/teiid/dqp/internal/process/CachedResults.java =================================================================== --- trunk/engine/src/main/java/org/teiid/dqp/internal/process/CachedResults.java 2011-08-06 14:57:03 UTC (rev 3365) +++ trunk/engine/src/main/java/org/teiid/dqp/internal/process/CachedResults.java 2011-08-10 18:01:12 UTC (rev 3366) @@ -41,7 +41,6 @@ import org.teiid.logging.LogConstants; import org.teiid.logging.LogManager; import org.teiid.query.QueryPlugin; -import org.teiid.query.analysis.AnalysisRecord; import org.teiid.query.metadata.QueryMetadataInterface; import org.teiid.query.parser.ParseInfo; import org.teiid.query.parser.QueryParser; @@ -58,8 +57,6 @@ private transient Command command; private transient TupleBuffer results; - private AnalysisRecord analysisRecord; - private String[] types; private CacheHint hint; private int batchSize; @@ -73,14 +70,6 @@ return this.uuid; } - public AnalysisRecord getAnalysisRecord() { - return analysisRecord; - } - - public void setAnalysisRecord(AnalysisRecord analysisRecord) { - this.analysisRecord = analysisRecord; - } - public TupleBuffer getResults() { return results; } Modified: trunk/engine/src/main/java/org/teiid/dqp/internal/process/RequestWorkItem.java =================================================================== --- trunk/engine/src/main/java/org/teiid/dqp/internal/process/RequestWorkItem.java 2011-08-06 14:57:03 UTC (rev 3365) +++ trunk/engine/src/main/java/org/teiid/dqp/internal/process/RequestWorkItem.java 2011-08-10 18:01:12 UTC (rev 3366) @@ -456,7 +456,6 @@ CachedResults cr = rsCache.get(cacheId); if (cr != null) { this.resultsBuffer = cr.getResults(); - this.analysisRecord = cr.getAnalysisRecord(); request.initMetadata(); this.originalCommand = cr.getCommand(requestMsg.getCommandString(), request.metadata, pi); request.validateAccess(this.originalCommand); @@ -530,7 +529,6 @@ Determinism determinismLevel = processor.getContext().getDeterminismLevel(); CachedResults cr = new CachedResults(); cr.setCommand(originalCommand); - cr.setAnalysisRecord(analysisRecord); cr.setResults(resultsBuffer, processor.getProcessorPlan()); if (originalCommand.getCacheHint() != null) { LogManager.logDetail(LogConstants.CTX_DQP, requestID, "Using cache hint", originalCommand.getCacheHint()); //$NON-NLS-1$ @@ -659,6 +657,7 @@ if (requestMsg.getShowPlan() == ShowPlan.DEBUG) { response.setDebugLog(analysisRecord.getDebugLog()); } + this.analysisRecord = null; } } Modified: trunk/engine/src/main/java/org/teiid/query/analysis/AnalysisRecord.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/analysis/AnalysisRecord.java 2011-08-06 14:57:03 UTC (rev 3365) +++ trunk/engine/src/main/java/org/teiid/query/analysis/AnalysisRecord.java 2011-08-10 18:01:12 UTC (rev 3366) @@ -53,7 +53,7 @@ * <li>Debug trace information, if requested</LI> * </ul> */ -public class AnalysisRecord implements Serializable { +public class AnalysisRecord { // Common public static final String PROP_OUTPUT_COLS = "Output Columns"; //$NON-NLS-1$ Modified: trunk/engine/src/main/java/org/teiid/query/eval/Evaluator.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/eval/Evaluator.java 2011-08-06 14:57:03 UTC (rev 3365) +++ trunk/engine/src/main/java/org/teiid/query/eval/Evaluator.java 2011-08-10 18:01:12 UTC (rev 3366) @@ -118,6 +118,7 @@ import org.teiid.query.sql.util.VariableContext; import org.teiid.query.util.CommandContext; import org.teiid.query.xquery.saxon.SaxonXQueryExpression; +import org.teiid.query.xquery.saxon.XQueryEvaluator; import org.teiid.query.xquery.saxon.SaxonXQueryExpression.Result; import org.teiid.query.xquery.saxon.SaxonXQueryExpression.RowProcessor; import org.teiid.translator.WSConnection.Util; @@ -203,7 +204,7 @@ } } - public final static char[] REGEX_RESERVED = new char[] {'$', '(', ')', '*', '.', '?', '[', '\\', ']', '^', '{', '|', '}'}; //in sorted order + public final static char[] REGEX_RESERVED = new char[] {'$', '(', ')', '*', '+', '.', '?', '[', '\\', ']', '^', '{', '|', '}'}; //in sorted order private final static MatchCriteria.PatternTranslator LIKE_TO_REGEX = new MatchCriteria.PatternTranslator(new char[] {'%', '_'}, new String[] {".*", "."}, REGEX_RESERVED, '\\', Pattern.DOTALL); //$NON-NLS-1$ //$NON-NLS-2$ private final static char[] SIMILAR_REGEX_RESERVED = new char[] {'$', '.', '\\', '^'}; //in sorted order @@ -933,7 +934,7 @@ parameters.put(passing.getAlias(), value); } } - return xquery.evaluateXQuery(contextItem, parameters, processor, context); + return XQueryEvaluator.evaluateXQuery(xquery, contextItem, parameters, processor, context); } private Evaluator.NameValuePair<Object>[] getNameValuePairs(List<?> tuple, List<DerivedColumn> args, boolean xmlNames) Modified: trunk/engine/src/main/java/org/teiid/query/mapping/xml/ResultSetInfo.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/mapping/xml/ResultSetInfo.java 2011-08-06 14:57:03 UTC (rev 3365) +++ trunk/engine/src/main/java/org/teiid/query/mapping/xml/ResultSetInfo.java 2011-08-10 18:01:12 UTC (rev 3366) @@ -23,11 +23,13 @@ package org.teiid.query.mapping.xml; import java.util.HashSet; +import java.util.List; import java.util.Set; import org.teiid.query.processor.ProcessorPlan; import org.teiid.query.sql.lang.Command; import org.teiid.query.sql.lang.Criteria; +import org.teiid.query.sql.lang.Insert; import org.teiid.query.sql.lang.OrderBy; import org.teiid.query.sql.symbol.ElementSymbol; @@ -61,21 +63,21 @@ private boolean criteriaRaised = false; - private boolean stagedResult = false; - - //joined source node state - private int mappingClassNumber = 0; private ElementSymbol mappingClassSymbol; private boolean inputSet; private boolean isCritNullDependent; + + //auto-staging related info + private String stagingRoot; + private String tempTable; + private Command tempSelect; + private Insert tempInsert; + private Command tempDrop; + private boolean isAutoStaged; + private List<ElementSymbol> fkColumns; public ResultSetInfo(String resultName) { - this(resultName, false); - } - - public ResultSetInfo(String resultName, boolean staged) { this.resultSetName = resultName; - this.stagedResult = staged; } public String getResultSetName() { @@ -144,14 +146,19 @@ } public Object clone() { - ResultSetInfo clone = new ResultSetInfo(this.resultSetName, this.stagedResult); + ResultSetInfo clone = new ResultSetInfo(this.resultSetName); clone.rsPlan = this.rsPlan; clone.userRowLimit = this.userRowLimit; clone.exceptionOnRowLimit = this.exceptionOnRowLimit; clone.rsCommand = (Command)this.rsCommand.clone(); clone.criteriaRaised = this.criteriaRaised; - clone.mappingClassNumber = this.mappingClassNumber; clone.mappingClassSymbol = this.mappingClassSymbol; + clone.tempInsert = this.tempInsert; + clone.tempSelect = this.tempSelect; + clone.tempTable = this.tempTable; + clone.tempDrop = this.tempDrop; + clone.isAutoStaged = this.isAutoStaged; + clone.fkColumns = this.fkColumns; return clone; } @@ -159,14 +166,6 @@ return resultSetName + ", resultSetObject " + rsCommand; //$NON-NLS-1$ } - public int getMappingClassNumber() { - return this.mappingClassNumber; - } - - public void setMappingClassNumber(int mappingClassNumber) { - this.mappingClassNumber = mappingClassNumber; - } - public ElementSymbol getMappingClassSymbol() { return this.mappingClassSymbol; } @@ -175,10 +174,6 @@ this.mappingClassSymbol = mappingClassSymbol; } - public boolean isStagedResult() { - return this.stagedResult; - } - public boolean hasInputSet() { return inputSet; } @@ -194,4 +189,60 @@ public boolean isCritNullDependent(){ return this.isCritNullDependent; } + + public String getStagingRoot() { + return stagingRoot; + } + + public void setStagingRoot(String stagingRoot) { + this.stagingRoot = stagingRoot; + } + + public void setTempSelect(Command tempSelect) { + this.tempSelect = tempSelect; + } + + public Command getTempSelect() { + return tempSelect; + } + + public void setTempInsert(Insert tempInsert) { + this.tempInsert = tempInsert; + } + + public Insert getTempInsert() { + return tempInsert; + } + + public Command getTempDrop() { + return tempDrop; + } + + public void setTempDrop(Command tempDrop) { + this.tempDrop = tempDrop; + } + + public String getTempTable() { + return tempTable; + } + + public void setTempTable(String rsTempTable) { + this.tempTable = rsTempTable; + } + + public boolean isAutoStaged() { + return isAutoStaged; + } + + public void setAutoStaged(boolean isAutoStaged) { + this.isAutoStaged = isAutoStaged; + } + + public void setFkColumns(List<ElementSymbol> fkColumns) { + this.fkColumns = fkColumns; + } + + public List<ElementSymbol> getFkColumns() { + return this.fkColumns; + } } Modified: trunk/engine/src/main/java/org/teiid/query/metadata/TempMetadataAdapter.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/metadata/TempMetadataAdapter.java 2011-08-06 14:57:03 UTC (rev 3365) +++ trunk/engine/src/main/java/org/teiid/query/metadata/TempMetadataAdapter.java 2011-08-10 18:01:12 UTC (rev 3366) @@ -325,7 +325,7 @@ return true; } - if(groupID instanceof TempMetadataID) { + if(groupID instanceof TempMetadataID && !(actualMetadata instanceof TempMetadataAdapter)) { return false; } @@ -347,7 +347,7 @@ } } - if(groupID instanceof TempMetadataID) { + if(groupID instanceof TempMetadataID && !(actualMetadata instanceof TempMetadataAdapter)) { return null; } Modified: trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleMergeCriteria.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleMergeCriteria.java 2011-08-06 14:57:03 UTC (rev 3365) +++ trunk/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleMergeCriteria.java 2011-08-10 18:01:12 UTC (rev 3366) @@ -420,6 +420,7 @@ result.not = exists.isNegated(); //the correlations can only be in where (if no group by or aggregates) or having result.mergeJoin = exists.getSubqueryHint().isMergeJoin(); + result.makeInd = exists.getSubqueryHint().isDepJoin(); if (!UNNEST && !result.mergeJoin) { return result; } Modified: trunk/engine/src/main/java/org/teiid/query/optimizer/xml/XMLPlanToProcessVisitor.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/optimizer/xml/XMLPlanToProcessVisitor.java 2011-08-06 14:57:03 UTC (rev 3365) +++ trunk/engine/src/main/java/org/teiid/query/optimizer/xml/XMLPlanToProcessVisitor.java 2011-08-10 18:01:12 UTC (rev 3366) @@ -22,6 +22,8 @@ package org.teiid.query.optimizer.xml; +import java.util.HashMap; +import java.util.LinkedList; import java.util.List; import java.util.Map; import java.util.Stack; @@ -70,7 +72,7 @@ Stack<Program> programStack = new Stack<Program>(); XMLPlannerEnvironment planEnv; Program originalProgram ; - Program cleanupProgram = new Program(); + Map<String, List<String>> unloadInstructions = new HashMap<String, List<String>>(); public XMLPlanToProcessVisitor(XMLPlannerEnvironment env) { this.planEnv = env; @@ -85,8 +87,7 @@ // remove the current program from the stack; we no longer need this originalProgram=this.programStack.pop(); - // cleanup program will have instructions to unload the staging table. - originalProgram.addInstructions(cleanupProgram); + addUnloads(originalProgram, null); } public void start(MappingAttribute attribute, Map context){ @@ -289,7 +290,6 @@ Program currentProgram = programStack.peek(); String source = node.getActualResultSetName(); - ResultSetInfo info= node.getResultSetInfo(); // move to next row. currentProgram.addInstruction(new MoveCursorInstruction(source)); @@ -327,15 +327,22 @@ } List<String> stagingTables = node.getStagingTables(); + Program currentProgram = programStack.peek(); + for (String table : stagingTables) { - Program currentProgram = programStack.peek(); - // load staging - currentProgram.addInstruction(new ExecStagingTableInstruction(table, planEnv.getStagingTableResultsInfo(table))); + ResultSetInfo stagingTableResultsInfo = planEnv.getStagingTableResultsInfo(table); + currentProgram.addInstruction(new ExecStagingTableInstruction(table, stagingTableResultsInfo)); // unload staging String unloadName = planEnv.unLoadResultName(table); - cleanupProgram.addInstruction(new ExecStagingTableInstruction(unloadName, planEnv.getStagingTableResultsInfo(unloadName))); + String parent = stagingTableResultsInfo.getStagingRoot(); + List<String> instructions = this.unloadInstructions.get(parent); + if (instructions == null) { + instructions = new LinkedList<String>(); + this.unloadInstructions.put(parent, instructions); + } + instructions.add(unloadName); } // for } @@ -344,7 +351,22 @@ // stop recording and update the program endRootRecursive(node, context); } + + Program currentProgram = programStack.peek(); + if (node instanceof MappingSourceNode) { + String name = node.getSource(); + addUnloads(currentProgram, name); + } } + + private void addUnloads(Program currentProgram, String name) { + List<String> unloads = this.unloadInstructions.get(name); + if (unloads != null) { + for (String string : unloads) { + currentProgram.addInstruction(new ExecStagingTableInstruction(string, planEnv.getStagingTableResultsInfo(string))); + } + } + } public void start(final MappingRecursiveElement element, Map context){ Program currentProgram = programStack.peek(); Modified: trunk/engine/src/main/java/org/teiid/query/optimizer/xml/XMLPlanner.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/optimizer/xml/XMLPlanner.java 2011-08-06 14:57:03 UTC (rev 3365) +++ trunk/engine/src/main/java/org/teiid/query/optimizer/xml/XMLPlanner.java 2011-08-10 18:01:12 UTC (rev 3366) @@ -226,8 +226,8 @@ } static void removeExcluded(MappingNode node) { - for (Iterator i = node.getChildren().iterator(); i.hasNext();) { - MappingNode child = (MappingNode)i.next(); + for (Iterator<MappingNode> i = node.getChildren().iterator(); i.hasNext();) { + MappingNode child = i.next(); if (!(node instanceof MappingRecursiveElement) && child.isExcluded()) { i.remove(); } else { @@ -305,7 +305,7 @@ by = new OrderBy(); } ElementSymbol mappedSymbol = (ElementSymbol)sourceNode.getSymbolMap().get(new ElementSymbol(elementNode.getNameInSource())); - by.addVariable(mappedSymbol.clone(), ((Boolean)types.get(i)).booleanValue()); + by.addVariable(mappedSymbol.clone(), types.get(i)); rs.setOrderBy(by); } } Modified: trunk/engine/src/main/java/org/teiid/query/optimizer/xml/XMLPlannerEnvironment.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/optimizer/xml/XMLPlannerEnvironment.java 2011-08-06 14:57:03 UTC (rev 3365) +++ trunk/engine/src/main/java/org/teiid/query/optimizer/xml/XMLPlannerEnvironment.java 2011-08-10 18:01:12 UTC (rev 3366) @@ -124,7 +124,7 @@ public ResultSetInfo getStagingTableResultsInfo(String groupName) { ResultSetInfo info = (ResultSetInfo)this.stagingResultsInfo.get(groupName.toUpperCase()); if (info == null) { - info = new ResultSetInfo(groupName, true); + info = new ResultSetInfo(groupName); this.stagingResultsInfo.put(info.getResultSetName().toUpperCase(), info); } return info; Modified: trunk/engine/src/main/java/org/teiid/query/optimizer/xml/XMLQueryPlanner.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/optimizer/xml/XMLQueryPlanner.java 2011-08-06 14:57:03 UTC (rev 3365) +++ trunk/engine/src/main/java/org/teiid/query/optimizer/xml/XMLQueryPlanner.java 2011-08-10 18:01:12 UTC (rev 3366) @@ -55,7 +55,6 @@ import org.teiid.query.processor.relational.RelationalPlan; import org.teiid.query.resolver.QueryResolver; import org.teiid.query.resolver.util.ResolverUtil; -import org.teiid.query.rewriter.QueryRewriter; import org.teiid.query.sql.lang.Command; import org.teiid.query.sql.lang.Criteria; import org.teiid.query.sql.lang.Drop; @@ -140,10 +139,10 @@ prepareQuery(sourceNode, planEnv, command); - QueryUtil.rewriteQuery(command, planEnv.getGlobalMetadata(), planEnv.context); + Command cmd = QueryUtil.rewriteQuery(command, planEnv.getGlobalMetadata(), planEnv.context); // Plan the result set. - ProcessorPlan queryPlan = optimizePlan(command, planEnv); + ProcessorPlan queryPlan = optimizePlan(cmd, planEnv); rsInfo.setPlan(queryPlan); } catch (Exception e) { throw new TeiidRuntimeException(e); @@ -441,7 +440,7 @@ return finder.msn; } - private static void updateSymbolMap(Map symbolMap, String oldGroup, final String newGroup, QueryMetadataInterface metadata) + static void updateSymbolMap(Map symbolMap, String oldGroup, final String newGroup, QueryMetadataInterface metadata) throws QueryResolverException,QueryMetadataException,TeiidComponentException { GroupSymbol oldGroupSymbol = new GroupSymbol(oldGroup); @@ -485,7 +484,7 @@ GroupSymbol srcGroup = QueryUtil.createResolvedGroup(srcGroupName, planEnv.getGlobalMetadata()); - String intoGroupName = "#"+stageGroupName.replace('.', '_'); //$NON-NLS-1$ + String intoGroupName = getTempTableName(stageGroupName); GroupSymbol intoGroupSymbol = new GroupSymbol(intoGroupName); query.setInto(new Into(intoGroupSymbol)); @@ -571,6 +570,11 @@ return true; } + + static String getTempTableName(String stageGroupName) { + String intoGroupName = "#"+stageGroupName.replace('.', '_'); //$NON-NLS-1$ + return intoGroupName; + } /** * This builds a command in the following form; If staging table name is "FOO" Modified: trunk/engine/src/main/java/org/teiid/query/optimizer/xml/XMLStagaingQueryPlanner.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/optimizer/xml/XMLStagaingQueryPlanner.java 2011-08-06 14:57:03 UTC (rev 3365) +++ trunk/engine/src/main/java/org/teiid/query/optimizer/xml/XMLStagaingQueryPlanner.java 2011-08-10 18:01:12 UTC (rev 3366) @@ -22,23 +22,54 @@ package org.teiid.query.optimizer.xml; +import java.util.ArrayList; +import java.util.Arrays; import java.util.Collection; +import java.util.HashMap; +import java.util.LinkedHashSet; +import java.util.List; +import java.util.Map; import org.teiid.api.exception.query.QueryMetadataException; +import org.teiid.api.exception.query.QueryParserException; import org.teiid.api.exception.query.QueryPlannerException; import org.teiid.api.exception.query.QueryResolverException; import org.teiid.core.TeiidComponentException; import org.teiid.core.TeiidRuntimeException; +import org.teiid.logging.LogConstants; +import org.teiid.logging.LogManager; import org.teiid.query.mapping.relational.QueryNode; +import org.teiid.query.mapping.xml.MappingBaseNode; import org.teiid.query.mapping.xml.MappingDocument; +import org.teiid.query.mapping.xml.MappingNode; import org.teiid.query.mapping.xml.MappingSourceNode; import org.teiid.query.mapping.xml.MappingVisitor; import org.teiid.query.mapping.xml.Navigator; import org.teiid.query.mapping.xml.ResultSetInfo; +import org.teiid.query.metadata.TempMetadataStore; import org.teiid.query.optimizer.relational.RelationalPlanner; +import org.teiid.query.parser.QueryParser; +import org.teiid.query.resolver.QueryResolver; +import org.teiid.query.resolver.util.ResolverUtil; +import org.teiid.query.resolver.util.ResolverVisitor; +import org.teiid.query.sql.lang.Command; +import org.teiid.query.sql.lang.CompareCriteria; +import org.teiid.query.sql.lang.Criteria; +import org.teiid.query.sql.lang.ExistsCriteria; +import org.teiid.query.sql.lang.From; +import org.teiid.query.sql.lang.GroupBy; +import org.teiid.query.sql.lang.Insert; import org.teiid.query.sql.lang.Option; import org.teiid.query.sql.lang.Query; +import org.teiid.query.sql.lang.Select; +import org.teiid.query.sql.lang.UnaryFromClause; +import org.teiid.query.sql.symbol.Constant; +import org.teiid.query.sql.symbol.ElementSymbol; +import org.teiid.query.sql.symbol.ExpressionSymbol; import org.teiid.query.sql.symbol.GroupSymbol; +import org.teiid.query.sql.symbol.Reference; +import org.teiid.query.sql.symbol.SingleElementSymbol; +import org.teiid.query.sql.visitor.ExpressionMappingVisitor; import org.teiid.query.sql.visitor.GroupCollectorVisitor; @@ -47,7 +78,6 @@ */ public class XMLStagaingQueryPlanner { - static void stageQueries(MappingDocument doc, final XMLPlannerEnvironment planEnv) throws QueryPlannerException, QueryMetadataException, TeiidComponentException { @@ -86,7 +116,7 @@ } static boolean stagePlannedQuery(MappingSourceNode sourceNode, XMLPlannerEnvironment planEnv) - throws QueryPlannerException, QueryMetadataException, TeiidComponentException, QueryResolverException { + throws QueryPlannerException, QueryMetadataException, TeiidComponentException, QueryResolverException, QueryParserException { Option option = planEnv.xmlCommand.getOption(); @@ -114,18 +144,6 @@ return false; } - //id as mapping class - Object metadataID = planEnv.getGlobalMetadata().getGroupID(sourceNode.getResultName()); - if (RelationalPlanner.isNoCacheGroup(planEnv.getGlobalMetadata(), metadataID, option)) { - return false; - } - - //id as generated mapping class name - metadataID = planEnv.getGlobalMetadata().getGroupID(sourceNode.getActualResultSetName()); - if (RelationalPlanner.isNoCacheGroup(planEnv.getGlobalMetadata(), metadataID, option)) { - return false; - } - // get the original transformation of the mapping class before planning. Query stagableQuery = (Query)QueryUtil.getQueryFromQueryNode(groupName, planEnv); @@ -140,17 +158,192 @@ } } + Criteria crit = ((Query)rsInfo.getCommand()).getCriteria(); + + GroupSymbol parent = null; + LinkedHashSet<ElementSymbol> outerReferences = new LinkedHashSet<ElementSymbol>(); + LinkedHashSet<ElementSymbol> fkColumns = new LinkedHashSet<ElementSymbol>(); + //see if we can perform a dependent join + for (Criteria conjunct : Criteria.separateCriteriaByAnd(crit)) { + if (!(conjunct instanceof CompareCriteria)) { + continue; + } + CompareCriteria cc = (CompareCriteria)conjunct; + if (cc.getOperator() != CompareCriteria.EQ) { + continue; + } + if (!(cc.getLeftExpression() instanceof ElementSymbol) + || !(cc.getRightExpression() instanceof ElementSymbol)) { + continue; + } + ElementSymbol les = (ElementSymbol)cc.getLeftExpression(); + ElementSymbol res = (ElementSymbol)cc.getRightExpression(); + if (les.getGroupSymbol().getNonCorrelationName().equalsIgnoreCase(groupName)) { + parent = res.getGroupSymbol(); + outerReferences.add(res.clone()); + fkColumns.add(les.clone()); + } else if (res.getGroupSymbol().getNonCorrelationName().equalsIgnoreCase(groupName)) { + parent = les.getGroupSymbol(); + outerReferences.add(les.clone()); + fkColumns.add(res.clone()); + } + } + String stagingGroupName = planEnv.getStagedResultName(groupName); + + boolean recursive = false; + MappingSourceNode msn = sourceNode; + while (!recursive && msn != null) { + MappingNode mappingNode = msn.getChildren().get(0); + if (mappingNode instanceof MappingBaseNode) { + recursive = ((MappingBaseNode)mappingNode).isRootRecursiveNode(); + } + msn = msn.getParentSourceNode(); + } + + if (parent != null && !recursive) { + stagableQuery = (Query)stagableQuery.clone(); + String parentName = parent.getNonCorrelationName(); + + String parentStagingName = planEnv.getStagedResultName(parentName); + GroupSymbol parentTempTable = new GroupSymbol(XMLQueryPlanner.getTempTableName(parentStagingName)); + ResultSetInfo parentRsInfo = planEnv.getStagingTableResultsInfo(parentStagingName); + String stagingRoot = sourceNode.getParentSourceNode().getSource(); + + boolean parentStaged = parentRsInfo.getPlan() != null; + //TODO: check to see if the parent was manually staged + + if (!parentStaged) { + //TODO: if not a level 1 child we could use the old auto staging logic instead + + //switch the parent over to the source + parentRsInfo = sourceNode.getParentSourceNode().getResultSetInfo(); + + if (parentRsInfo.getTempTable() == null) { + //create a temp table to represent the resultset + List<SingleElementSymbol> projectedSymbols = parentRsInfo.getCommand().getProjectedSymbols(); + ArrayList<SingleElementSymbol> elements = new ArrayList<SingleElementSymbol>(projectedSymbols.size()); + for (SingleElementSymbol singleElementSymbol : projectedSymbols) { + singleElementSymbol = (SingleElementSymbol) singleElementSymbol.clone(); + ResolverVisitor.resolveLanguageObject(singleElementSymbol, planEnv.getGlobalMetadata()); + elements.add(singleElementSymbol); + } + TempMetadataStore store = planEnv.getGlobalMetadata().getMetadataStore(); + // create a new group name and to the temp store + GroupSymbol newGroup = new GroupSymbol(SourceNodePlannerVisitor.getNewName("#" + planEnv.getAliasName(parentName) + "_RS", store)); //$NON-NLS-1$ //$NON-NLS-2$ + newGroup.setMetadataID(store.addTempGroup(newGroup.getName(), elements, false, true)); + + parentStagingName = newGroup.getName(); + parentTempTable = newGroup; + } else { + parentStagingName = parentRsInfo.getTempTable(); + parentTempTable = new GroupSymbol(parentRsInfo.getTempTable()); + parentStaged = true; + } + } else { + stagingRoot = parentRsInfo.getStagingRoot(); + } + + Query query = new Query(); + query.setSelect(new Select(Arrays.asList(new ExpressionSymbol("expr", new Constant(1))))); //$NON-NLS-1$ + + query.setFrom(new From(Arrays.asList(new UnaryFromClause(parentTempTable)))); + + Map symbolMap = new HashMap(); + String inlineViewName = planEnv.getAliasName(rsInfo.getResultSetName()); + XMLQueryPlanner.updateSymbolMap(symbolMap, rsInfo.getResultSetName(), inlineViewName, planEnv.getGlobalMetadata()); + XMLQueryPlanner.updateSymbolMap(symbolMap, parentName, parentTempTable.getName(), planEnv.getGlobalMetadata()); + + crit = (Criteria) crit.clone(); + ExpressionMappingVisitor.mapExpressions(crit, symbolMap); + + if (!stagableQuery.getSelect().isDistinct()) { + query.setHaving(crit); + //group by is added so that subquery planning sees that we are distinct + query.setGroupBy(new GroupBy(new ArrayList<ElementSymbol>(outerReferences))); + ExpressionMappingVisitor.mapExpressions(query.getGroupBy(), symbolMap); + } else { + query.setCriteria(crit); + } + ExistsCriteria ec = new ExistsCriteria(); + ec.setSubqueryHint(new ExistsCriteria.SubqueryHint()); + ec.getSubqueryHint().setDepJoin(true); + ec.setCommand(query); + Criteria existing = stagableQuery.getCriteria(); + stagableQuery.setCriteria(Criteria.combineCriteria(existing, ec)); + if (!XMLQueryPlanner.planStagaingQuery(false, groupName, stagingGroupName, stagableQuery, planEnv)) { + return false; + } + if (!parentStaged) { + //need to associate temp load/get/drop with the rsinfo for use by the execsqlinstruction + Insert insert = new Insert(); + insert.setGroup(parentTempTable); + int valCount = parentRsInfo.getCommand().getProjectedSymbols().size(); + ArrayList<Reference> vals = new ArrayList<Reference>(valCount); + for (int i = 0; i < valCount; i++) { + vals.add(new Reference(i+1)); + } + insert.setValues(vals); + QueryResolver.resolveCommand(insert, planEnv.getGlobalMetadata()); + + Command tempCommand = QueryParser.getQueryParser().parseCommand("select * from " + parentStagingName); //$NON-NLS-1$ + QueryResolver.resolveCommand(tempCommand, planEnv.getGlobalMetadata()); + + Command dropCommand = QueryParser.getQueryParser().parseCommand("drop table " + parentStagingName); //$NON-NLS-1$ + QueryResolver.resolveCommand(dropCommand, planEnv.getGlobalMetadata()); + + parentRsInfo.setTempTable(parentStagingName); + parentRsInfo.setTempSelect(tempCommand); + parentRsInfo.setTempInsert(insert); + parentRsInfo.setTempDrop(dropCommand); + } + LogManager.logDetail(LogConstants.CTX_XML_PLANNER, "Using a dependent join to load the mapping class", groupName); //$NON-NLS-1$ + // add to the document that a staging table has been added + sourceNode.addStagingTable(stagingGroupName); + GroupSymbol tempGroup = new GroupSymbol(XMLQueryPlanner.getTempTableName(stagingGroupName)); + ResolverUtil.resolveGroup(tempGroup, planEnv.getGlobalMetadata()); + Collection<GroupSymbol> temp = Arrays.asList(tempGroup); + List<ElementSymbol> fk = new ArrayList<ElementSymbol>(fkColumns.size()); + for (ElementSymbol elementSymbol : fkColumns) { + ElementSymbol es = new ElementSymbol(elementSymbol.getShortName()); + ResolverVisitor.resolveLanguageObject(es, temp, planEnv.getGlobalMetadata()); + fk.add(es); + } + ResultSetInfo stagedInfo = planEnv.getStagingTableResultsInfo(stagingGroupName); + stagedInfo.setStagingRoot(stagingRoot); + stagedInfo.setAutoStaged(true); + stagedInfo.setFkColumns(fk); + stagedInfo.setTempTable(tempGroup.getName()); + + rsInfo.setAutoStaged(true); + return true; + } + + //id as mapping class + Object metadataID = planEnv.getGlobalMetadata().getGroupID(sourceNode.getResultName()); + if (RelationalPlanner.isNoCacheGroup(planEnv.getGlobalMetadata(), metadataID, option)) { + return false; + } + + //id as generated mapping class name + metadataID = planEnv.getGlobalMetadata().getGroupID(sourceNode.getActualResultSetName()); + if (RelationalPlanner.isNoCacheGroup(planEnv.getGlobalMetadata(), metadataID, option)) { + return false; + } + stagableQuery = (Query)stagableQuery.clone(); - + // stage the transformation query and it is successful - String stagingGroupName = planEnv.getStagedResultName(groupName); if (!XMLQueryPlanner.planStagaingQuery(true, groupName, stagingGroupName, stagableQuery, planEnv)) { return false; } // add to the document that a staging table has been added sourceNode.addStagingTable(stagingGroupName); - + ResultSetInfo stagedInfo = planEnv.getStagingTableResultsInfo(stagingGroupName); + stagedInfo.setAutoStaged(true); + stagedInfo.setTempTable(XMLQueryPlanner.getTempTableName(stagingGroupName)); + + rsInfo.setAutoStaged(true); return true; } Modified: trunk/engine/src/main/java/org/teiid/query/processor/xml/ExecSqlInstruction.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/processor/xml/ExecSqlInstruction.java 2011-08-06 14:57:03 UTC (rev 3365) +++ trunk/engine/src/main/java/org/teiid/query/processor/xml/ExecSqlInstruction.java 2011-08-10 18:01:12 UTC (rev 3366) @@ -36,7 +36,7 @@ * Executes a SQL statement, defines a result set. */ public class ExecSqlInstruction extends ProcessorInstruction { - private String resultSetName; + String resultSetName; ResultSetInfo info; public ExecSqlInstruction(String resultSetName, ResultSetInfo info) { @@ -48,7 +48,16 @@ public XMLContext process(XMLProcessorEnvironment env, XMLContext context) throws BlockedException, TeiidComponentException, TeiidProcessingException{ - LogManager.logTrace(org.teiid.logging.LogConstants.CTX_XML_PLAN, new Object[]{"SQL: Result set DOESN'T exist:",resultSetName}); //$NON-NLS-1$ + execute(env, context); + + env.incrementCurrentProgramCounter(); + return context; + } + + protected void execute(XMLProcessorEnvironment env, XMLContext context) + throws TeiidComponentException, BlockedException, + TeiidProcessingException { + LogManager.logTrace(org.teiid.logging.LogConstants.CTX_XML_PLAN, new Object[]{"SQL: Result set DOESN'T exist:",resultSetName}); //$NON-NLS-1$ PlanExecutor executor = getPlanExecutor(env, context); // this execute can throw the blocked exception @@ -60,10 +69,7 @@ // save this executioner in the context, so that all the nodes // below can access the data. context.setResultSet(this.resultSetName, executor); - - env.incrementCurrentProgramCounter(); - return context; - } + } public PlanExecutor getPlanExecutor(XMLProcessorEnvironment env, XMLContext context) throws TeiidComponentException { Modified: trunk/engine/src/main/java/org/teiid/query/processor/xml/ExecStagingTableInstruction.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/processor/xml/ExecStagingTableInstruction.java 2011-08-06 14:57:03 UTC (rev 3365) +++ trunk/engine/src/main/java/org/teiid/query/processor/xml/ExecStagingTableInstruction.java 2011-08-10 18:01:12 UTC (rev 3366) @@ -24,13 +24,10 @@ import static org.teiid.query.analysis.AnalysisRecord.*; -import java.util.Map; - import org.teiid.client.plan.PlanNode; import org.teiid.common.buffer.BlockedException; import org.teiid.core.TeiidComponentException; import org.teiid.core.TeiidProcessingException; -import org.teiid.logging.LogManager; import org.teiid.query.mapping.xml.ResultSetInfo; @@ -38,20 +35,14 @@ /** * This instruction is to start loading a staging table. The difference between the loading the * staging table and execute sql node is that sql node will capture the results and save them in - * the context object, where as staging does not care about the results, beacuse they are actully + * the context object, where as staging does not care about the results, because they are actually * stored in the temp table store, will be accessed by required query directly from there, as these * results are nothing do with producing the document directly. - * - * NOTE: In future we can improve this to load parallelly, if there are more than single - * staging table defined on the mapping document */ -public class ExecStagingTableInstruction extends ProcessorInstruction { - String resultSetName; - ResultSetInfo info; +public class ExecStagingTableInstruction extends ExecSqlInstruction { public ExecStagingTableInstruction(String resultName, ResultSetInfo info) { - this.resultSetName = resultName; - this.info = info; + super(resultName, info); } /** @@ -61,19 +52,8 @@ throws BlockedException, TeiidComponentException, TeiidProcessingException { if (!env.isStagingTableLoaded(this.resultSetName)) { - LogManager.logTrace(org.teiid.logging.LogConstants.CTX_XML_PLAN, new Object[]{"SQL: Result set DOESN'T exist:",resultSetName}); //$NON-NLS-1$ + super.execute(env, context); - PlanExecutor executor = context.getResultExecutor(resultSetName); - if (executor == null) { - executor = env.createResultExecutor(resultSetName, info); - context.setResultExecutor(resultSetName, executor); - } - - // this execute can throw the blocked exception; note that staging tables will not have any - // bound references; they are not part of the document; so they do not know about document - // details. - Map referenceValues = null; - executor.execute(referenceValues, false); env.markStagingTableAsLoaded(this.resultSetName); // now that we done executing the plan; remove the plan from context Modified: trunk/engine/src/main/java/org/teiid/query/processor/xml/RelationalPlanExecutor.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/processor/xml/RelationalPlanExecutor.java 2011-08-06 14:57:03 UTC (rev 3365) +++ trunk/engine/src/main/java/org/teiid/query/processor/xml/RelationalPlanExecutor.java 2011-08-10 18:01:12 UTC (rev 3366) @@ -27,32 +27,53 @@ import org.teiid.common.buffer.BlockedException; import org.teiid.common.buffer.BufferManager; -import org.teiid.common.buffer.IndexedTupleSource; +import org.teiid.common.buffer.TupleSource; import org.teiid.core.TeiidComponentException; import org.teiid.core.TeiidProcessingException; +import org.teiid.logging.LogConstants; import org.teiid.logging.LogManager; import org.teiid.query.QueryPlugin; import org.teiid.query.mapping.xml.ResultSetInfo; +import org.teiid.query.metadata.TempMetadataAdapter; import org.teiid.query.processor.BatchIterator; import org.teiid.query.processor.ProcessorDataManager; import org.teiid.query.processor.ProcessorPlan; import org.teiid.query.processor.QueryProcessor; +import org.teiid.query.sql.lang.Insert; import org.teiid.query.sql.symbol.ElementSymbol; import org.teiid.query.sql.util.VariableContext; +import org.teiid.query.tempdata.AlterTempTable; import org.teiid.query.util.CommandContext; - - /** * This is a helper class which can execute a supplied relational plan and supply * resulting query results to the caller. - * Note: in future we would want to replace this class with submitting the requests directly to - * process worker queue. */ class RelationalPlanExecutor implements PlanExecutor { - QueryProcessor internalProcessor; + private final class TempLoadTupleSource implements TupleSource { + @Override + public List<?> nextTuple() throws TeiidComponentException, + TeiidProcessingException { + try { + List<?> tuple = tupleSource.nextTuple(); + if (tuple == null) { + doneLoading = true; + } + return tuple; + } catch (BlockedException e) { + return null; + } + } + @Override + public void closeSource() { + tupleSource.closeSource(); + } + } + + QueryProcessor internalProcessor; + // information about the result set. ResultSetInfo resultInfo; // buffer store @@ -60,16 +81,20 @@ // flag to denote the end of rows boolean endOfRows = false; // results after the execution bucket. - IndexedTupleSource tupleSource; + TupleSource tupleSource; // cached current row of results. - List currentRow; + List<?> currentRow; int currentRowNumber = 0; + private ProcessorDataManager dataManager; + private boolean executed; + private boolean doneLoading; public RelationalPlanExecutor (ResultSetInfo resultInfo, CommandContext context, ProcessorDataManager dataMgr, BufferManager bufferMgr) throws TeiidComponentException{ this.resultInfo = resultInfo; this.bufferMgr = bufferMgr; + this.dataManager = dataMgr; ProcessorPlan plan = resultInfo.getPlan(); CommandContext subContext = context.clone(); @@ -97,9 +122,38 @@ internalProcessor.init(); } } - if (!openOnly) { - //force execution - this.tupleSource.hasNext(); + if (!openOnly && !executed) { + String tempTable = this.resultInfo.getTempTable(); + if (tempTable != null && !doneLoading && !this.resultInfo.isAutoStaged()) { + LogManager.logDetail(LogConstants.CTX_XML_PLAN, "Loading result set temp table", tempTable); //$NON-NLS-1$ + + Insert insert = this.resultInfo.getTempInsert(); + insert.setTupleSource(new TempLoadTupleSource()); + this.dataManager.registerRequest(this.internalProcessor.getContext(), insert, TempMetadataAdapter.TEMP_MODEL.getName(), null, 0, -1); + if (!doneLoading) { + throw BlockedException.block("Blocking on result set load"); //$NON-NLS-1$ + } + internalProcessor.closeProcessing(); + AlterTempTable att = new AlterTempTable(tempTable); + //mark the temp table as non-updatable + this.dataManager.registerRequest(this.internalProcessor.getContext(), att, TempMetadataAdapter.TEMP_MODEL.getName(), null, 0, -1); + this.tupleSource = this.dataManager.registerRequest(this.internalProcessor.getContext(), this.resultInfo.getTempSelect(), TempMetadataAdapter.TEMP_MODEL.getName(), null, 0, -1); + } + //force execution + currentRow(); + + if (this.resultInfo.isAutoStaged() && tempTable != null) { + AlterTempTable att = new AlterTempTable(tempTable); + int size = (Integer)this.currentRow.get(0); + if (size > this.bufferMgr.getProcessorBatchSize() * 2) { + //TODO: if the parent is small, then this is not necessary + att.setIndexColumns(this.resultInfo.getFkColumns()); + } + this.dataManager.registerRequest(this.internalProcessor.getContext(), att, TempMetadataAdapter.TEMP_MODEL.getName(), null, 0, -1); + } + + this.currentRowNumber = 0; + this.executed = true; } } @@ -122,7 +176,9 @@ if (!endOfRows) { // get the next row - this.currentRow = this.tupleSource.nextTuple(); + if (this.currentRow == null || this.currentRowNumber > 0) { + this.currentRow = this.tupleSource.nextTuple(); + } this.currentRowNumber++; // check if we walked over the row limit @@ -159,6 +215,19 @@ */ public void close() throws TeiidComponentException { this.internalProcessor.closeProcessing(); + if (this.tupleSource != null) { + this.tupleSource.closeSource(); + } + String rsTempTable = this.resultInfo.getTempTable(); + if (rsTempTable != null) { + LogManager.logDetail(LogConstants.CTX_XML_PLAN, "Unloading result set temp table", rsTempTable); //$NON-NLS-1$ + internalProcessor.closeProcessing(); + try { + this.tupleSource = this.dataManager.registerRequest(this.internalProcessor.getContext(), this.resultInfo.getTempDrop(), TempMetadataAdapter.TEMP_MODEL.getName(), null, 0, -1); + } catch (TeiidProcessingException e) { + LogManager.logDetail(org.teiid.logging.LogConstants.CTX_XML_PLAN, e, "Error dropping result set temp table", rsTempTable); //$NON-NLS-1$ + } + } LogManager.logTrace(org.teiid.logging.LogConstants.CTX_XML_PLAN, new Object[]{"closed executor", resultInfo.getResultSetName()}); //$NON-NLS-1$ } Modified: trunk/engine/src/main/java/org/teiid/query/processor/xml/XMLProcessorEnvironment.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/processor/xml/XMLProcessorEnvironment.java 2011-08-06 14:57:03 UTC (rev 3365) +++ trunk/engine/src/main/java/org/teiid/query/processor/xml/XMLProcessorEnvironment.java 2011-08-10 18:01:12 UTC (rev 3366) @@ -179,18 +179,25 @@ if (programState.programCounter >= programState.lookaheadCounter && instrs.size() > programState.programCounter + 1) { for (programState.lookaheadCounter = programState.programCounter; programState.lookaheadCounter < instrs.size(); programState.lookaheadCounter++) { ProcessorInstruction pi = instrs.get(programState.lookaheadCounter); + boolean staging = false; if (pi instanceof ExecStagingTableInstruction) { - //need to load staging tables prior to source queries - break; + staging = true; + ExecStagingTableInstruction esti = (ExecStagingTableInstruction)pi; + if (!esti.info.isAutoStaged()) { + //need to load staging tables prior to source queries + break; + } } if (pi instanceof ExecSqlInstruction) { ExecSqlInstruction esi = (ExecSqlInstruction)pi; + if (!staging && esi.info.isAutoStaged() && esi.info.getTempTable() == null) { + continue; //derived load + } PlanExecutor pe = esi.getPlanExecutor(this, context); pe.execute(context.getReferenceValues(), true); } } } - return programState.program.getInstructionAt(programState.programCounter); } Modified: trunk/engine/src/main/java/org/teiid/query/resolver/command/InsertResolver.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/resolver/command/InsertResolver.java 2011-08-06 14:57:03 UTC (rev 3365) +++ trunk/engine/src/main/java/org/teiid/query/resolver/command/InsertResolver.java 2011-08-10 18:01:12 UTC (rev 3366) @@ -97,9 +97,9 @@ if (insert.getVariables().isEmpty()) { if (insert.getGroup().isResolved()) { - List variables = ResolverUtil.resolveElementsInGroup(insert.getGroup(), metadata); - for (Iterator i = variables.iterator(); i.hasNext();) { - insert.addVariable((ElementSymbol)((ElementSymbol)i.next()).clone()); + List<ElementSymbol> variables = ResolverUtil.resolveElementsInGroup(insert.getGroup(), metadata); + for (Iterator<ElementSymbol> i = variables.iterator(); i.hasNext();) { + insert.addVariable(i.next().clone()); } } else { for (int i = 0; i < values.size(); i++) { @@ -178,11 +178,11 @@ } Iterator valueIter = values.iterator(); - Iterator varIter = insert.getVariables().iterator(); + Iterator<ElementSymbol> varIter = insert.getVariables().iterator(); while(valueIter.hasNext()) { // Walk through both elements and expressions, which should match up Expression expression = (Expression) valueIter.next(); - ElementSymbol element = (ElementSymbol) varIter.next(); + ElementSymbol element = varIter.next(); if (!usingQuery) { ResolverUtil.setDesiredType(expression, element.getType(), insert); Modified: trunk/engine/src/main/java/org/teiid/query/resolver/util/ResolverVisitor.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/resolver/util/ResolverVisitor.java 2011-08-06 14:57:03 UTC (rev 3365) +++ trunk/engine/src/main/java/org/teiid/query/resolver/util/ResolverVisitor.java 2011-08-10 18:01:12 UTC (rev 3366) @@ -34,9 +34,11 @@ import org.teiid.api.exception.query.QueryMetadataException; import org.teiid.api.exception.query.QueryResolverException; import org.teiid.api.exception.query.UnresolvedSymbolDescription; +import org.teiid.core.CoreConstants; import org.teiid.core.TeiidComponentException; import org.teiid.core.types.DataTypeManager; import org.teiid.core.types.DataTypeManager.DefaultDataClasses; +import org.teiid.core.util.StringUtil; import org.teiid.query.QueryPlugin; import org.teiid.query.function.FunctionDescriptor; import org.teiid.query.function.FunctionForm; @@ -75,7 +77,9 @@ public class ResolverVisitor extends LanguageVisitor { - private static class ElementMatch { + private static final String SYS_PREFIX = CoreConstants.SYSTEM_MODEL + '.'; + + private static class ElementMatch { ElementSymbol element; GroupSymbol group; @@ -547,6 +551,9 @@ function.setFunctionDescriptor(fd); function.setType(fd.getReturnType()); + if (CoreConstants.SYSTEM_MODEL.equals(fd.getSchema()) && StringUtil.startsWithIgnoreCase(function.getName(), SYS_PREFIX)) { + function.setName(function.getName().substring(SYS_PREFIX.length())); + } } /** Copied: trunk/engine/src/main/java/org/teiid/query/tempdata/AlterTempTable.java (from rev 3365, branches/7.4.x/engine/src/main/java/org/teiid/query/tempdata/AlterTempTable.java) =================================================================== --- trunk/engine/src/main/java/org/teiid/query/tempdata/AlterTempTable.java (rev 0) +++ trunk/engine/src/main/java/org/teiid/query/tempdata/AlterTempTable.java 2011-08-10 18:01:12 UTC (rev 3366) @@ -0,0 +1,78 @@ +package org.teiid.query.tempdata; + +import java.util.List; + +import org.teiid.query.sql.LanguageVisitor; +import org.teiid.query.sql.lang.Command; +import org.teiid.query.sql.symbol.ElementSymbol; +import org.teiid.query.sql.symbol.SingleElementSymbol; + +/* + * 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. + */ + +public class AlterTempTable extends Command { + + private String tempTable; + private List<ElementSymbol> indexColumns; + + public AlterTempTable(String tempTable) { + this.tempTable = tempTable; + } + + public String getTempTable() { + return tempTable; + } + + public List<ElementSymbol> getIndexColumns() { + return indexColumns; + } + + public void setIndexColumns(List<ElementSymbol> indexColumns) { + this.indexColumns = indexColumns; + } + + @Override + public boolean areResultsCachable() { + return false; + } + + @Override + public Object clone() { + return this; + } + + @Override + public List<SingleElementSymbol> getProjectedSymbols() { + return Command.getUpdateCommandSymbol(); + } + + @Override + public int getType() { + return Command.TYPE_UNKNOWN; + } + + @Override + public void acceptVisitor(LanguageVisitor visitor) { + + } + +} Modified: trunk/engine/src/main/java/org/teiid/query/tempdata/TempTableDataManager.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/tempdata/TempTableDataManager.java 2011-08-06 14:57:03 UTC (rev 3365) +++ trunk/engine/src/main/java/org/teiid/query/tempdata/TempTableDataManager.java 2011-08-10 18:01:12 UTC (rev 3366) @@ -249,6 +249,15 @@ contextStore.removeTempTableByName(tempTableName); return CollectionTupleSource.createUpdateCountTupleSource(0); } + if (command instanceof AlterTempTable) { + AlterTempTable att = (AlterTempTable)command; + TempTable tt = contextStore.getOrCreateTempTable(att.getTempTable().toUpperCase(), command, bufferManager, true); + tt.setUpdatable(false); + if (att.getIndexColumns() != null) { + tt.addIndex(att.getIndexColumns(), false); + } + return CollectionTupleSource.createUpdateCountTupleSource(0); + } return null; } Modified: trunk/engine/src/main/java/org/teiid/query/xquery/saxon/SaxonXQueryExpression.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/xquery/saxon/SaxonXQueryExpression.java 2011-08-06 14:57:03 UTC (rev 3365) +++ trunk/engine/src/main/java/org/teiid/query/xquery/saxon/SaxonXQueryExpression.java 2011-08-10 18:01:12 UTC (rev 3366) @@ -23,9 +23,7 @@ package org.teiid.query.xquery.saxon; import java.io.IOException; -import java.io.InputStream; import java.io.Writer; -import java.sql.SQLXML; import java.util.Arrays; import java.util.HashMap; import java.util.HashSet; @@ -40,9 +38,7 @@ import javax.xml.transform.Source; import javax.xml.transform.TransformerException; -import net.sf.saxon.AugmentedSource; import net.sf.saxon.Configuration; -import net.sf.saxon.event.ProxyReceiver; import net.sf.saxon.expr.AxisExpression; import net.sf.saxon.expr.ContextItemExpression; import net.sf.saxon.expr.Expression; @@ -53,14 +49,12 @@ import net.sf.saxon.expr.PathMap.PathMapNodeSet; import net.sf.saxon.expr.PathMap.PathMapRoot; import net.sf.saxon.om.Axis; -import net.sf.saxon.om.DocumentInfo; import net.sf.saxon.om.Item; import net.sf.saxon.om.NodeInfo; import net.sf.saxon.om.SequenceIterator; import net.sf.saxon.om.StructuredQName; import net.sf.saxon.pattern.AnyNodeTest; import net.sf.saxon.pattern.NodeKindTest; -import net.sf.saxon.query.DynamicQueryContext; import net.sf.saxon.query.QueryResult; import net.sf.saxon.query.StaticQueryContext; import net.sf.saxon.query.XQueryExpression; @@ -72,12 +66,6 @@ import net.sf.saxon.type.ItemType; import net.sf.saxon.type.TypeHierarchy; import net.sf.saxon.value.SequenceType; -import nu.xom.Builder; -import nu.xom.Element; -import nu.xom.Nodes; -import nu.xom.ParsingException; -import nux.xom.xquery.StreamingPathFilter; -import nux.xom.xquery.StreamingTransform; import org.teiid.api.exception.query.QueryResolverException; import org.teiid.common.buffer.BufferManager; @@ -89,9 +77,6 @@ import org.teiid.core.types.XMLTranslator; import org.teiid.core.types.XMLType; import org.teiid.core.types.XMLType.Type; -import org.teiid.logging.LogConstants; -import org.teiid.logging.LogManager; -import org.teiid.logging.MessageLevel; import org.teiid.query.QueryPlugin; import org.teiid.query.analysis.AnalysisRecord; import org.teiid.query.function.source.XMLSystemFunctions; @@ -100,7 +85,6 @@ import org.teiid.query.sql.symbol.DerivedColumn; import org.teiid.query.sql.symbol.XMLNamespaces; import org.teiid.query.sql.symbol.XMLNamespaces.NamespaceItem; -import org.teiid.query.util.CommandContext; import org.teiid.translator.WSConnection.Util; @SuppressWarnings("serial") @@ -113,15 +97,6 @@ DEFAULT_OUTPUT_PROPERTIES.setProperty(OutputKeys.OMIT_XML_DECLARATION, "yes"); //$NON-NLS-1$ } - private static Nodes NONE = new Nodes(); - private static InputStream FAKE_IS = new InputStream() { - - @Override - public int read() throws IOException { - return 0; - } - }; - public interface RowProcessor { void processRow(NodeInfo row); @@ -182,12 +157,12 @@ } }; - private XQueryExpression xQuery; - private String xQueryString; - private Map<String, String> namespaceMap = new HashMap<String, String>(); - private Configuration config = new Configuration(); - private PathMapRoot contextRoot; - private StreamingPathFilter streamingPathFilter; + XQueryExpression xQuery; + String xQueryString; + Map<String, String> namespaceMap = new HashMap<String, String>(); + Configuration config = new Configuration(); + PathMapRoot contextRoot; + String streamingPath; public SaxonXQueryExpression(String xQueryString, XMLNamespaces namespaces, List<DerivedColumn> passing, List<XMLTable.XMLColumn> columns) throws QueryResolverException { @@ -246,7 +221,7 @@ clone.config = config; clone.contextRoot = contextRoot; clone.namespaceMap = namespaceMap; - clone.streamingPathFilter = streamingPathFilter; + clone.streamingPath = streamingPath; return clone; } @@ -256,7 +231,7 @@ public void useDocumentProjection(List<XMLTable.XMLColumn> columns, AnalysisRecord record) { try { - streamingPathFilter = StreamingUtils.getStreamingPathFilter(xQueryString, namespaceMap); + streamingPath = StreamingUtils.getStreamingPath(xQueryString, namespaceMap); } catch (IllegalArgumentException e) { if (record.recordDebug()) { record.println("Document streaming will not be used: " + e.getMessage()); //$NON-NLS-1$ @@ -362,8 +337,8 @@ continue; } for (PathMapArc arc : subContextRoot.getArcs()) { - if (streamingPathFilter != null && !validateColumnForStreaming(record, xmlColumn, arc)) { - streamingPathFilter = null; + if (streamingPath != null && !validateColumnForStreaming(record, xmlColumn, arc)) { + streamingPath = null; } finalNode.createArc(arc.getStep(), arc.getTarget()); } @@ -491,90 +466,7 @@ } } - public Result evaluateXQuery(Object context, Map<String, Object> parameterValues, final RowProcessor processor, CommandContext commandContext) throws TeiidProcessingException { - DynamicQueryContext dynamicContext = new DynamicQueryContext(config); - - Result result = new Result(); - try { - try { - for (Map.Entry<String, Object> entry : parameterValues.entrySet()) { - Object value = entry.getValue(); - if(value instanceof SQLXML) { - value = XMLSystemFunctions.convertToSource(value); - result.sources.add((Source)value); - } else if (value instanceof java.util.Date) { - value = XMLSystemFunctions.convertToAtomicValue(value); - } - dynamicContext.setParameter(entry.getKey(), value); - } - } catch (TransformerException e) { - throw new TeiidProcessingException(e); - } - if (context != null) { - Source source = XMLSystemFunctions.convertToSource(context); - result.sources.add(source); - if (contextRoot != null) { - //create our own filter as this logic is not provided in the free saxon - ProxyReceiver filter = new PathMapFilter(contextRoot); - AugmentedSource sourceInput = AugmentedSource.makeAugmentedSource(source); - sourceInput.addFilter(filter); - source = sourceInput; - - //use streamable processing instead - if (streamingPathFilter != null && processor != null) { - if (LogManager.isMessageToBeRecorded(LogConstants.CTX_DQP, MessageLevel.DETAIL)) { - LogManager.logDetail(LogConstants.CTX_DQP, "Using stream processing for evaluation of", this.xQueryString); //$NON-NLS-1$ - } - //set to non-blocking in case default expression evaluation blocks - boolean isNonBlocking = commandContext.isNonBlocking(); - commandContext.setNonBlocking(true); - - final StreamingTransform myTransform = new StreamingTransform() { - public Nodes transform(Element elem) { - processor.processRow(StreamingUtils.wrap(elem, config)); - return NONE; - } - }; - - Builder builder = new Builder(new SaxonReader(config, sourceInput), false, - streamingPathFilter.createNodeFactory(null, myTransform)); - try { - //the builder is hard wired to parse the source, but the api will throw an exception if the stream is null - builder.build(FAKE_IS); - return result; - } catch (ParsingException e) { - throw new TeiidProcessingException(e, QueryPlugin.Util.getString("SaxonXQueryExpression.bad_context")); //$NON-NLS-1$ - } catch (IOException e) { - throw new TeiidProcessingException(e, QueryPlugin.Util.getString("SaxonXQueryExpression.bad_context")); //$NON-NLS-1$ - } finally { - if (!isNonBlocking) { - commandContext.setNonBlocking(false); - } - } - } - } - DocumentInfo doc; - try { - doc = config.buildDocument(source); - } catch (XPathException e) { - throw new TeiidProcessingException(e, QueryPlugin.Util.getString("SaxonXQueryExpression.bad_context")); //$NON-NLS-1$ - } - dynamicContext.setContextItem(doc); - } - try { - result.iter = xQuery.iterator(dynamicContext); - return result; - } catch (TransformerException e) { - throw new TeiidProcessingException(e, QueryPlugin.Util.getString("SaxonXQueryExpression.bad_xquery")); //$NON-NLS-1$ - } - } finally { - if (result.iter == null) { - result.close(); - } - } - } - - public XMLType createXMLType(final SequenceIterator iter, BufferManager bufferManager, boolean emptyOnEmpty) throws XPathException, TeiidComponentException, TeiidProcessingException { + public XMLType createXMLType(final SequenceIterator iter, BufferManager bufferManager, boolean emptyOnEmpty) throws XPathException, TeiidComponentException, TeiidProcessingException { Item item = iter.next(); if (item == null && !emptyOnEmpty) { return null; @@ -634,7 +526,7 @@ } public boolean isStreaming() { - return streamingPathFilter != null; + return streamingPath != null; } } Modified: trunk/engine/src/main/java/org/teiid/query/xquery/saxon/StreamingUtils.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/xquery/saxon/StreamingUtils.java 2011-08-06 14:57:03 UTC (rev 3365) +++ trunk/engine/src/main/java/org/teiid/query/xquery/saxon/StreamingUtils.java 2011-08-10 18:01:12 UTC (rev 3366) @@ -32,11 +32,7 @@ import net.sf.saxon.event.ProxyReceiver; import net.sf.saxon.event.Receiver; import net.sf.saxon.om.Name11Checker; -import net.sf.saxon.om.NodeInfo; import net.sf.saxon.trans.XPathException; -import nu.xom.DocType; -import nu.xom.Node; -import nux.xom.xquery.StreamingPathFilter; import org.xml.sax.ContentHandler; import org.xml.sax.DTDHandler; @@ -51,28 +47,6 @@ final class StreamingUtils { /** - * Converts a xom node into something readable by Saxon - * @param node - * @param config - * @return - */ - static NodeInfo wrap(Node node, Configuration config) { - if (node == null) - throw new IllegalArgumentException("node must not be null"); //$NON-NLS-1$ - if (node instanceof DocType) - throw new IllegalArgumentException("DocType can't be queried by XQuery/XPath"); //$NON-NLS-1$ - - Node root = node; - while (root.getParent() != null) { - root = root.getParent(); - } - - DocumentWrapper docWrapper = new DocumentWrapper(root, root.getBaseURI(), config); - - return docWrapper.wrap(node); - } - - /** * Pre-parser that adds validation and handles a default name space * * TODO: add support for more general paths including node tests @@ -83,7 +57,7 @@ * @param prefixMap * @return */ - public static StreamingPathFilter getStreamingPathFilter(String locationPath, Map<String, String> prefixMap) { + public static String getStreamingPath(String locationPath, Map<String, String> prefixMap) { if (locationPath.indexOf("//") >= 0) //$NON-NLS-1$ throw new IllegalArgumentException("DESCENDANT axis is not supported"); //$NON-NLS-1$ @@ -124,7 +98,7 @@ } fixedPath += localNames[i]; } - return new StreamingPathFilter(fixedPath, prefixMap); + return fixedPath; } } Copied: trunk/engine/src/main/java/org/teiid/query/xquery/saxon/XQueryEvaluator.java (from rev 3365, branches/7.4.x/engine/src/main/java/org/teiid/query/xquery/saxon/XQueryEvaluator.java) =================================================================== --- trunk/engine/src/main/java/org/teiid/query/xquery/saxon/XQueryEvaluator.java (rev 0) +++ trunk/engine/src/main/java/org/teiid/query/xquery/saxon/XQueryEvaluator.java 2011-08-10 18:01:12 UTC (rev 3366) @@ -0,0 +1,177 @@ +/* + * 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.query.xquery.saxon; + +import java.io.IOException; +import java.io.InputStream; +import java.sql.SQLXML; +import java.util.Map; + +import javax.xml.transform.Source; +import javax.xml.transform.TransformerException; + +import net.sf.saxon.AugmentedSource; +import net.sf.saxon.Configuration; +import net.sf.saxon.event.ProxyReceiver; +import net.sf.saxon.om.DocumentInfo; +import net.sf.saxon.om.NodeInfo; +import net.sf.saxon.query.DynamicQueryContext; +import net.sf.saxon.trans.XPathException; +import nu.xom.Builder; +import nu.xom.DocType; +import nu.xom.Element; +import nu.xom.Node; +import nu.xom.Nodes; +import nu.xom.ParsingException; +import nux.xom.xquery.StreamingPathFilter; +import nux.xom.xquery.StreamingTransform; + +import org.teiid.core.TeiidProcessingException; +import org.teiid.logging.LogConstants; +import org.teiid.logging.LogManager; +import org.teiid.logging.MessageLevel; +import org.teiid.query.QueryPlugin; +import org.teiid.query.function.source.XMLSystemFunctions; +import org.teiid.query.util.CommandContext; +import org.teiid.query.xquery.saxon.SaxonXQueryExpression.RowProcessor; + +/** + * Used to isolate the xom/nux dependency and to better isolate the saxon processing logic. + */ +public class XQueryEvaluator { + + private static Nodes NONE = new Nodes(); + private static InputStream FAKE_IS = new InputStream() { + + @Override + public int read() throws IOException { + return 0; + } + }; + + public static SaxonXQueryExpression.Result evaluateXQuery(final SaxonXQueryExpression xquery, Object context, Map<String, Object> parameterValues, final RowProcessor processor, CommandContext commandContext) throws TeiidProcessingException { + DynamicQueryContext dynamicContext = new DynamicQueryContext(xquery.config); + + SaxonXQueryExpression.Result result = new SaxonXQueryExpression.Result(); + try { + try { + for (Map.Entry<String, Object> entry : parameterValues.entrySet()) { + Object value = entry.getValue(); + if(value instanceof SQLXML) { + value = XMLSystemFunctions.convertToSource(value); + result.sources.add((Source)value); + } else if (value instanceof java.util.Date) { + value = XMLSystemFunctions.convertToAtomicValue(value); + } + dynamicContext.setParameter(entry.getKey(), value); + } + } catch (TransformerException e) { + throw new TeiidProcessingException(e); + } + if (context != null) { + Source source = XMLSystemFunctions.convertToSource(context); + result.sources.add(source); + if (xquery.contextRoot != null) { + //create our own filter as this logic is not provided in the free saxon + ProxyReceiver filter = new PathMapFilter(xquery.contextRoot); + AugmentedSource sourceInput = AugmentedSource.makeAugmentedSource(source); + sourceInput.addFilter(filter); + source = sourceInput; + + //use streamable processing instead + if (xquery.streamingPath != null && processor != null) { + if (LogManager.isMessageToBeRecorded(LogConstants.CTX_DQP, MessageLevel.DETAIL)) { + LogManager.logDetail(LogConstants.CTX_DQP, "Using stream processing for evaluation of", xquery.xQueryString); //$NON-NLS-1$ + } + //set to non-blocking in case default expression evaluation blocks + boolean isNonBlocking = commandContext.isNonBlocking(); + commandContext.setNonBlocking(true); + + final StreamingTransform myTransform = new StreamingTransform() { + public Nodes transform(Element elem) { + processor.processRow(XQueryEvaluator.wrap(elem, xquery.config)); + return NONE; + } + }; + + Builder builder = new Builder(new SaxonReader(xquery.config, sourceInput), false, + new StreamingPathFilter(xquery.streamingPath, xquery.namespaceMap).createNodeFactory(null, myTransform)); + try { + //the builder is hard wired to parse the source, but the api will throw an exception if the stream is null + builder.build(FAKE_IS); + return result; + } catch (ParsingException e) { + throw new TeiidProcessingException(e, QueryPlugin.Util.getString("SaxonXQueryExpression.bad_context")); //$NON-NLS-1$ + } catch (IOException e) { + throw new TeiidProcessingException(e, QueryPlugin.Util.getString("SaxonXQueryExpression.bad_context")); //$NON-NLS-1$ + } finally { + if (!isNonBlocking) { + commandContext.setNonBlocking(false); + } + } + } + } + DocumentInfo doc; + try { + doc = xquery.config.buildDocument(source); + } catch (XPathException e) { + throw new TeiidProcessingException(e, QueryPlugin.Util.getString("SaxonXQueryExpression.bad_context")); //$NON-NLS-1$ + } + dynamicContext.setContextItem(doc); + } + try { + result.iter = xquery.xQuery.iterator(dynamicContext); + return result; + } catch (TransformerException e) { + throw new TeiidProcessingException(e, QueryPlugin.Util.getString("SaxonXQueryExpression.bad_xquery")); //$NON-NLS-1$ + } + } finally { + if (result.iter == null) { + result.close(); + } + } + } + + /** + * Converts a xom node into something readable by Saxon + * @param node + * @param config + * @return + */ + static NodeInfo wrap(Node node, Configuration config) { + if (node == null) + throw new IllegalArgumentException("node must not be null"); //$NON-NLS-1$ + if (node instanceof DocType) + throw new IllegalArgumentException("DocType can't be queried by XQuery/XPath"); //$NON-NLS-1$ + + Node root = node; + while (root.getParent() != null) { + root = root.getParent(); + } + + DocumentWrapper docWrapper = new DocumentWrapper(root, root.getBaseURI(), config); + + return docWrapper.wrap(node); + } + +} Modified: trunk/engine/src/test/java/org/teiid/query/processor/eval/TestExpressionEvaluator.java =================================================================== --- trunk/engine/src/test/java/org/teiid/query/processor/eval/TestExpressionEvaluator.java 2011-08-06 14:57:03 UTC (rev 3365) +++ trunk/engine/src/test/java/org/teiid/query/processor/eval/TestExpressionEvaluator.java 2011-08-10 18:01:12 UTC (rev 3366) @@ -502,4 +502,9 @@ assertEquals(Boolean.FALSE, Evaluator.evaluate(ex)); } + @Test public void testLikePlus() throws Exception { + Expression ex = TestFunctionResolving.getExpression("'+' like '+'"); + assertEquals(Boolean.TRUE, Evaluator.evaluate(ex)); + } + } Modified: trunk/engine/src/test/java/org/teiid/query/processor/xml/TestXMLPlanningEnhancements.java =================================================================== --- trunk/engine/src/test/java/org/teiid/query/processor/xml/TestXMLPlanningEnhancements.java 2011-08-06 14:57:03 UTC (rev 3365) +++ trunk/engine/src/test/java/org/teiid/query/processor/xml/TestXMLPlanningEnhancements.java 2011-08-10 18:01:12 UTC (rev 3366) @@ -28,6 +28,7 @@ import java.util.List; import java.util.Map; +import org.junit.Ignore; import org.junit.Test; import org.teiid.client.metadata.ParameterInfo; import org.teiid.common.buffer.BufferManager; @@ -309,6 +310,7 @@ TestXMLProcessor.helpTestProcess("SELECT * FROM xmltest.doc18a where supplierID<56", expectedDoc, metadata, dataMgr); //$NON-NLS-1$ } + @Ignore("Will plan with a dependent join") @Test public void testAutoStagingFailsForMappingClassWithProcRelational() throws Exception { TransformationMetadata metadata = getMetadata("SELECT supplierNum, supplierName, supplierZipCode FROM v1.supplierProc where itemnum = ?"); //$NON-NLS-1$ @@ -386,6 +388,7 @@ /** * @see #testNested2WithCriteria2 */ + @Ignore("Will use a dependent join instead") @Test public void testAutoStagingFailsByCosting() throws Exception { TransformationMetadata metadata = TestXMLProcessor.exampleMetadata(); FakeDataManager dataMgr = TestXMLProcessor.exampleDataManagerNested(metadata); @@ -404,6 +407,7 @@ assertNull(stats.get(ExecStagingTableInstruction.class)); } + @Ignore("Will use dependent join instead") @Test public void testAutoStagingFailsByNoCache() throws Exception { QueryMetadataInterface metadata = TestXMLProcessor.exampleMetadataCached(); FakeDataManager dataMgr = TestXMLProcessor.exampleDataManagerNested(metadata); @@ -417,6 +421,7 @@ assertNull(stats.get(ExecStagingTableInstruction.class)); } + @Ignore("Will use dependent join") @Test public void testAutoStagingFailsByNoCacheByGroup() throws Exception { QueryMetadataInterface metadata = TestXMLProcessor.exampleMetadataCached(); FakeDataManager dataMgr = TestXMLProcessor.exampleDataManagerNested(metadata); Modified: trunk/engine/src/test/java/org/teiid/query/processor/xml/TestXMLProcessor.java =================================================================== --- trunk/engine/src/test/java/org/teiid/query/processor/xml/TestXMLProcessor.java 2011-08-06 14:57:03 UTC (rev 3365) +++ trunk/engine/src/test/java/org/teiid/query/processor/xml/TestXMLProcessor.java 2011-08-10 18:01:12 UTC (rev 3366) @@ -8866,33 +8866,6 @@ helpTestProcess("SELECT * FROM xmltest.doc1 WHERE NOT (ItemID IN (SELECT itemNum FROM stock.items WHERE itemNum = '001') )", expectedDoc, metadata, dataMgr); //$NON-NLS-1$ } - public void XXXtestSubqueryInXMLQueryCriteria4() throws Exception { - QueryMetadataInterface metadata = exampleMetadataCached(); - FakeDataManager dataMgr = exampleDataManager(metadata); - String expectedDoc = - "<?xml version=\"1.0\" encoding=\"UTF-8\"?>\r\n" + //$NON-NLS-1$ - "<Catalogs xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\">\r\n... + //$NON-NLS-1$ - " <Catalog>\r\n" + //$NON-NLS-1$ - " <Items>\r\n" + //$NON-NLS-1$ - " <Item ItemID=\"001\">\r\n" + //$NON-NLS-1$ - " <Name>Lamp</Name>\r\n" + //$NON-NLS-1$ - " <Quantity>5</Quantity>\r\n" + //$NON-NLS-1$ - " </Item>\r\n" + //$NON-NLS-1$ - " <Item ItemID=\"002\">\r\n" + //$NON-NLS-1$ - " <Name>Screwdriver</Name>\r\n" + //$NON-NLS-1$ - " <Quantity>100</Quantity>\r\n" + //$NON-NLS-1$ - " </Item>\r\n" + //$NON-NLS-1$ - " <Item ItemID=\"003\">\r\n" + //$NON-NLS-1$ - " <Name>Goat</Name>\r\n" + //$NON-NLS-1$ - " <Quantity>4</Quantity>\r\n" + //$NON-NLS-1$ - " </Item>\r\n" + //$NON-NLS-1$ - " </Items>\r\n" + //$NON-NLS-1$ - " </Catalog>\r\n" + //$NON-NLS-1$ - "</Catalogs>\r\n\r\n"; //$NON-NLS-1$ - - helpTestProcess("SELECT * FROM xmltest.doc1 WHERE EXISTS (SELECT itemNum FROM stock.items WHERE itemNum = '001')", expectedDoc, metadata, dataMgr); //$NON-NLS-1$ - } - @Test public void testSubqueryInXMLQueryCriteriaNestedSubquery() throws Exception { QueryMetadataInterface metadata = exampleMetadataCached(); FakeDataManager dataMgr = exampleDataManager(metadata); Modified: trunk/engine/src/test/java/org/teiid/query/rewriter/TestQueryRewriter.java =================================================================== --- trunk/engine/src/test/java/org/teiid/query/rewriter/TestQueryRewriter.java 2011-08-06 14:57:03 UTC (rev 3365) +++ trunk/engine/src/test/java/org/teiid/query/rewriter/TestQueryRewriter.java 2011-08-10 18:01:12 UTC (rev 3366) @@ -2069,7 +2069,7 @@ } @Test public void testRewriteConcat2() { - helpTestRewriteCriteria("concat2('a','b') = 'ab'", "1 = 1"); //$NON-NLS-1$ //$NON-NLS-2$ + helpTestRewriteCriteria("sys.concat2('a','b') = 'ab'", "1 = 1"); //$NON-NLS-1$ //$NON-NLS-2$ } @Test public void testRewriteConcat2_1() {