[teiid-commits] teiid SVN: r4467 - in trunk/engine/src: main/java/org/teiid/query/resolver and 10 other directories.

Author: shawkins Date: 2012-09-24 21:48:29 -0400 (Mon, 24 Sep 2012) New Revision: 4467 Modified: trunk/engine/src/main/java/org/teiid/query/QueryPlugin.java trunk/engine/src/main/java/org/teiid/query/resolver/QueryResolver.java trunk/engine/src/main/java/org/teiid/query/resolver/command/UpdateProcedureResolver.java trunk/engine/src/main/java/org/teiid/query/sql/lang/ProcedureContainer.java trunk/engine/src/main/java/org/teiid/query/sql/proc/CommandStatement.java trunk/engine/src/main/java/org/teiid/query/sql/proc/CreateProcedureCommand.java trunk/engine/src/main/java/org/teiid/query/validator/ValidationVisitor.java trunk/engine/src/main/resources/org/teiid/query/i18n.properties trunk/engine/src/test/java/org/teiid/query/metadata/TestMetadataValidator.java trunk/engine/src/test/java/org/teiid/query/processor/proc/TestProcedureProcessor.java trunk/engine/src/test/java/org/teiid/query/resolver/TestAlterResolving.java trunk/engine/src/test/java/org/teiid/query/sql/proc/TestCreateUpdateProcedureCommand.java trunk/engine/src/test/java/org/teiid/query/unittest/RealMetadataFactory.java Log: TEIID-2226 adding proc rs validation Modified: trunk/engine/src/main/java/org/teiid/query/QueryPlugin.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/QueryPlugin.java 2012-09-24 15:36:49 UTC (rev 4466) +++ trunk/engine/src/main/java/org/teiid/query/QueryPlugin.java 2012-09-25 01:48:29 UTC (rev 4467) @@ -535,6 +535,7 @@ TEIID31117, TEIID31118, TEIID31119, - TEIID31120, + TEIID31120, + TEIID31121, } } Modified: trunk/engine/src/main/java/org/teiid/query/resolver/QueryResolver.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/resolver/QueryResolver.java 2012-09-24 15:36:49 UTC (rev 4466) +++ trunk/engine/src/main/java/org/teiid/query/resolver/QueryResolver.java 2012-09-25 01:48:29 UTC (rev 4467) @@ -109,6 +109,7 @@ //if the subcommand is virtual stored procedure, it must have the same //projected symbol as its parent. cupCommand.setProjectedSymbols(proc.getProjectedSymbols()); + cupCommand.setResultSetColumns(proc.getResultSetColumns()); } resolveCommand(command, proc.getGroup(), proc.getType(), metadata.getDesignTimeMetadata()); return command; @@ -471,8 +472,8 @@ } public static void validateProjectedSymbols(GroupSymbol virtualGroup, - List<ElementSymbol> symbols, - List<Expression> projectedSymbols) + List<? extends Expression> symbols, + List<? extends Expression> projectedSymbols) throws QueryValidatorException { if (symbols.size() != projectedSymbols.size()) { throw new QueryValidatorException(QueryPlugin.Event.TEIID30066, QueryPlugin.Util.gs(QueryPlugin.Event.TEIID30066, virtualGroup, symbols.size(), projectedSymbols.size())); Modified: trunk/engine/src/main/java/org/teiid/query/resolver/command/UpdateProcedureResolver.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/resolver/command/UpdateProcedureResolver.java 2012-09-24 15:36:49 UTC (rev 4466) +++ trunk/engine/src/main/java/org/teiid/query/resolver/command/UpdateProcedureResolver.java 2012-09-25 01:48:29 UTC (rev 4467) @@ -22,6 +22,7 @@ package org.teiid.query.resolver.command; +import java.util.ArrayList; import java.util.Arrays; import java.util.Collections; import java.util.LinkedList; @@ -64,7 +65,12 @@ /** */ public class UpdateProcedureResolver implements CommandResolver { - + + public static class StatementNode { + CommandStatement cs; + List<StatementNode> children; + } + /** * @see org.teiid.query.resolver.CommandResolver#resolveCommand(org.teiid.query.sql.lang.Command, TempMetadataAdapter, boolean) */ @@ -73,7 +79,7 @@ if (command instanceof TriggerAction) { TriggerAction ta = (TriggerAction)command; - resolveBlock(new CreateProcedureCommand(), ta.getBlock(), ta.getExternalGroupContexts(), metadata); + resolveBlock(new CreateProcedureCommand(), ta.getBlock(), ta.getExternalGroupContexts(), metadata, null); return; } @@ -89,12 +95,42 @@ updateCount.setType(DataTypeManager.DefaultDataClasses.INTEGER); symbols.add(updateCount); - ProcedureContainerResolver.addScalarGroup(ProcedureReservedWords.VARIABLES, metadata.getMetadataStore(), externalGroups, symbols); - resolveBlock(procCommand, procCommand.getBlock(), externalGroups, metadata); + ProcedureContainerResolver.addScalarGroup(ProcedureReservedWords.VARIABLES, metadata.getMetadataStore(), externalGroups, symbols); + StatementNode sn = new StatementNode(); + resolveBlock(procCommand, procCommand.getBlock(), externalGroups, metadata, sn); + + //validate the possible returned resultsets + if (procCommand.getResultSetColumns() == null || !procCommand.getResultSetColumns().isEmpty()) { + setReturnable(procCommand, sn); + } } + private void setReturnable(CreateProcedureCommand procCommand, StatementNode sn) { + if (sn.cs != null) { + sn.cs.setReturnable(true); + //this logic is for designer, which want's to populate a resultset from the proc text + if (procCommand.getResultSetColumns() == null) { + List<? extends Expression> symbols = sn.cs.getCommand().getProjectedSymbols(); + if (sn.cs.getCommand() instanceof StoredProcedure) { + StoredProcedure sp = (StoredProcedure)sn.cs.getCommand(); + if (sp.isCallableStatement()) { + symbols = sp.getResultSetColumns(); + } + } + procCommand.setResultSetColumns(symbols); + //TODO: what about out parameters + procCommand.setProjectedSymbols(symbols); + } + } + if (sn.children != null) { + for (StatementNode child : sn.children) { + setReturnable(procCommand, child); + } + } + } + public void resolveBlock(CreateProcedureCommand command, Block block, GroupContext externalGroups, - TempMetadataAdapter metadata) + TempMetadataAdapter metadata, org.teiid.query.resolver.command.UpdateProcedureResolver.StatementNode sn) throws QueryResolverException, QueryMetadataException, TeiidComponentException { LogManager.logTrace(org.teiid.logging.LogConstants.CTX_QUERY_RESOLVER, new Object[]{"Resolving block", block}); //$NON-NLS-1$ @@ -107,11 +143,11 @@ GroupSymbol variables = ProcedureContainerResolver.addScalarGroup(ProcedureReservedWords.VARIABLES, store, externalGroups, new LinkedList<Expression>()); for (Statement statement : block.getStatements()) { - resolveStatement(command, statement, externalGroups, variables, metadata); + resolveStatement(command, statement, externalGroups, variables, metadata, sn); } } - private void resolveStatement(CreateProcedureCommand command, Statement statement, GroupContext externalGroups, GroupSymbol variables, TempMetadataAdapter metadata) + private void resolveStatement(CreateProcedureCommand command, Statement statement, GroupContext externalGroups, GroupSymbol variables, TempMetadataAdapter metadata, StatementNode sn) throws QueryResolverException, QueryMetadataException, TeiidComponentException { LogManager.logTrace(org.teiid.logging.LogConstants.CTX_QUERY_RESOLVER, new Object[]{"Resolving statement", statement}); //$NON-NLS-1$ @@ -123,9 +159,9 @@ resolveEmbeddedCommand(metadata, externalGroups, container.getCommand()); } ResolverVisitor.resolveLanguageObject(ifCrit, null, externalGroups, metadata); - resolveBlock(command, ifStmt.getIfBlock(), externalGroups, metadata); + resolveBlock(command, ifStmt.getIfBlock(), externalGroups, metadata, pushChild(sn)); if(ifStmt.hasElseBlock()) { - resolveBlock(command, ifStmt.getElseBlock(), externalGroups, metadata); + resolveBlock(command, ifStmt.getElseBlock(), externalGroups, metadata, pushChild(sn)); } break; case Statement.TYPE_COMMAND: @@ -168,20 +204,17 @@ dynCommand.setAsColumns(command.getProjectedSymbols()); } } - - //don't bother using the metadata when it doesn't matter - if (command.getResultsCommand() != null && command.getResultsCommand().getType() == Command.TYPE_DYNAMIC) { - DynamicCommand dynamicCommand = (DynamicCommand)command.getResultsCommand(); - if (!dynamicCommand.isAsClauseSet()) { - dynamicCommand.setAsColumns(Collections.EMPTY_LIST); - } + + //this could be the last select statement, set the projected symbol + //on the virtual procedure command + if (subCommand.returnsResultSet() && sn != null) { + updateDynamicAs(sn); + + sn.cs = cmdStmt; + if (sn.children != null) { + sn.children.clear(); + } } - - if (subCommand.returnsResultSet()) { - //this could be the last select statement, set the projected symbol - //on the virtual procedure command - command.setResultsCommand(subCommand); - } break; case Statement.TYPE_ERROR: @@ -231,7 +264,7 @@ resolveEmbeddedCommand(metadata, externalGroups, container.getCommand()); } ResolverVisitor.resolveLanguageObject(whileCrit, null, externalGroups, metadata); - resolveBlock(command, whileStmt.getBlock(), externalGroups, metadata); + resolveBlock(command, whileStmt.getBlock(), externalGroups, metadata, pushChild(sn)); break; case Statement.TYPE_LOOP: LoopStatement loopStmt = (LoopStatement) statement; @@ -257,11 +290,37 @@ ProcedureContainerResolver.addScalarGroup(groupName, store, externalGroups, symbols, false); - resolveBlock(command, loopStmt.getBlock(), externalGroups, metadata); + resolveBlock(command, loopStmt.getBlock(), externalGroups, metadata, pushChild(sn)); break; } } + private void updateDynamicAs(StatementNode sn) { + if (sn.cs != null && sn.cs.getCommand().getType() == Command.TYPE_DYNAMIC) { + DynamicCommand dynamicCommand = (DynamicCommand)sn.cs.getCommand(); + if (!dynamicCommand.isAsClauseSet()) { + dynamicCommand.setAsColumns(Collections.EMPTY_LIST); + } + } + if (sn.children != null) { + for (StatementNode child : sn.children) { + updateDynamicAs(child); + } + } + } + + private StatementNode pushChild(StatementNode sn) { + if (sn != null) { + if (sn.children == null) { + sn.children = new ArrayList<StatementNode>(); + } + StatementNode child = new StatementNode(); + sn.children.add(child); + sn = child; + } + return sn; + } + private boolean isAssignable(TempMetadataAdapter metadata, SPParameter param) throws TeiidComponentException, QueryMetadataException { if (!(param.getExpression() instanceof ElementSymbol)) { Modified: trunk/engine/src/main/java/org/teiid/query/sql/lang/ProcedureContainer.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/sql/lang/ProcedureContainer.java 2012-09-24 15:36:49 UTC (rev 4466) +++ trunk/engine/src/main/java/org/teiid/query/sql/lang/ProcedureContainer.java 2012-09-25 01:48:29 UTC (rev 4467) @@ -22,6 +22,8 @@ package org.teiid.query.sql.lang; +import java.util.List; + import org.teiid.query.validator.UpdateValidator.UpdateInfo; @@ -64,5 +66,9 @@ public void setUpdateInfo(UpdateInfo updateInfo) { this.updateInfo = updateInfo; } + + public List getResultSetColumns() { + return getProjectedSymbols(); + } } Modified: trunk/engine/src/main/java/org/teiid/query/sql/proc/CommandStatement.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/sql/proc/CommandStatement.java 2012-09-24 15:36:49 UTC (rev 4466) +++ trunk/engine/src/main/java/org/teiid/query/sql/proc/CommandStatement.java 2012-09-25 01:48:29 UTC (rev 4467) @@ -37,6 +37,7 @@ // the command this statement represents Command command; + private boolean returnable; /** * Constructor for CommandStatement. @@ -87,7 +88,9 @@ * @return Deep clone */ public Object clone() { - return new CommandStatement((Command)this.command.clone()); + CommandStatement cs = new CommandStatement((Command)this.command.clone()); + cs.returnable = this.returnable; + return cs; } /** @@ -119,5 +122,13 @@ // This hash code relies on the commands hash code return this.getCommand().hashCode(); } + + public boolean isReturnable() { + return returnable; + } + + public void setReturnable(boolean returnable) { + this.returnable = returnable; + } } // END CLASS Modified: trunk/engine/src/main/java/org/teiid/query/sql/proc/CreateProcedureCommand.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/sql/proc/CreateProcedureCommand.java 2012-09-24 15:36:49 UTC (rev 4466) +++ trunk/engine/src/main/java/org/teiid/query/sql/proc/CreateProcedureCommand.java 2012-09-25 01:48:29 UTC (rev 4467) @@ -22,18 +22,17 @@ package org.teiid.query.sql.proc; -import java.util.ArrayList; import java.util.Collections; import java.util.List; import org.teiid.core.util.EquivalenceUtil; import org.teiid.core.util.HashCodeUtil; +import org.teiid.query.sql.LanguageObject; import org.teiid.query.sql.LanguageVisitor; import org.teiid.query.sql.lang.Command; import org.teiid.query.sql.lang.Query; -import org.teiid.query.sql.lang.StoredProcedure; -import org.teiid.query.sql.symbol.GroupSymbol; import org.teiid.query.sql.symbol.Expression; +import org.teiid.query.sql.symbol.GroupSymbol; import org.teiid.query.sql.visitor.SQLStringVisitor; @@ -51,11 +50,10 @@ private Command userCommand; private List projectedSymbols; + private List resultSetColumns; private GroupSymbol virtualGroup; - //command that returns resultset. For virtual procedure only. - private Command resultsCommand; /** * Constructor for CreateUpdateProcedureCommand. */ @@ -132,8 +130,11 @@ copy.setBlock(this.block.clone()); } if (this.projectedSymbols != null) { - copy.setProjectedSymbols(new ArrayList(this.projectedSymbols)); + copy.projectedSymbols = LanguageObject.Util.deepClone(this.projectedSymbols, Expression.class); } + if (this.resultSetColumns != null) { + copy.resultSetColumns = LanguageObject.Util.deepClone(this.resultSetColumns, Expression.class); + } if (this.virtualGroup != null) { copy.virtualGroup = this.virtualGroup.clone(); } @@ -199,21 +200,18 @@ if(this.projectedSymbols != null){ return this.projectedSymbols; } - if(this.resultsCommand == null){ - //user may have not entered any query yet - return Collections.EMPTY_LIST; - } - List<? extends Expression> symbols = this.resultsCommand.getProjectedSymbols(); - if (this.resultsCommand instanceof StoredProcedure) { - StoredProcedure sp = (StoredProcedure)this.resultsCommand; - if (sp.isCallableStatement()) { - symbols = sp.getResultSetColumns(); - } - } - setProjectedSymbols(symbols); - return this.projectedSymbols; + //user may have not entered any query yet + return Collections.EMPTY_LIST; } - + + public List getResultSetColumns() { + return resultSetColumns; + } + + public void setResultSetColumns(List resultSetColumns) { + this.resultSetColumns = resultSetColumns; + } + /** * @param projSymbols */ @@ -221,20 +219,6 @@ projectedSymbols = projSymbols; } - /** - * @return Command - */ - public Command getResultsCommand() { - return resultsCommand; - } - - /** - * @param command - */ - public void setResultsCommand(Command command) { - resultsCommand = command; - } - /** * @see org.teiid.query.sql.lang.Command#areResultsCachable() */ Modified: trunk/engine/src/main/java/org/teiid/query/validator/ValidationVisitor.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/validator/ValidationVisitor.java 2012-09-24 15:36:49 UTC (rev 4466) +++ trunk/engine/src/main/java/org/teiid/query/validator/ValidationVisitor.java 2012-09-25 01:48:29 UTC (rev 4467) @@ -70,6 +70,7 @@ import org.teiid.query.sql.navigator.PreOrderNavigator; import org.teiid.query.sql.proc.Block; import org.teiid.query.sql.proc.BranchingStatement; +import org.teiid.query.sql.proc.CommandStatement; import org.teiid.query.sql.proc.CreateProcedureCommand; import org.teiid.query.sql.proc.LoopStatement; import org.teiid.query.sql.proc.WhileStatement; @@ -110,13 +111,14 @@ // State during validation private boolean isXML = false; // only used for Query commands - private boolean inQuery; + private CreateProcedureCommand createProc; public void reset() { super.reset(); this.isXML = false; this.inQuery = false; + this.createProc = null; } // ############### Visitor methods for language objects ################## @@ -409,6 +411,7 @@ if (GroupCollectorVisitor.getGroups(obj,true).contains(obj.getVirtualGroup())) { handleValidationError(QueryPlugin.Util.getString("ValidationVisitor.Procedure_has_group_self_reference"),obj); //$NON-NLS-1$ } + this.createProc = obj; } public void visit(CompoundCriteria obj) { @@ -1465,6 +1468,24 @@ } @Override + public void visit(CommandStatement obj) { + if (obj.isReturnable() && this.createProc != null) { + List<? extends Expression> symbols = obj.getCommand().getProjectedSymbols(); + if (obj.getCommand() instanceof StoredProcedure) { + StoredProcedure sp = (StoredProcedure)obj.getCommand(); + if (sp.isCallableStatement()) { + symbols = sp.getResultSetColumns(); + } + } + try { + QueryResolver.validateProjectedSymbols(createProc.getVirtualGroup(), createProc.getResultSetColumns(), symbols); + } catch (QueryValidatorException e) { + handleValidationError(QueryPlugin.Util.gs(QueryPlugin.Event.TEIID31121, createProc.getVirtualGroup(), obj, e.getMessage()), obj); + } + } + } + + @Override public void visit(BranchingStatement obj) { boolean matchedLabel = false; boolean inLoop = false; Modified: trunk/engine/src/main/resources/org/teiid/query/i18n.properties =================================================================== --- trunk/engine/src/main/resources/org/teiid/query/i18n.properties 2012-09-24 15:36:49 UTC (rev 4466) +++ trunk/engine/src/main/resources/org/teiid/query/i18n.properties 2012-09-25 01:48:29 UTC (rev 4467) @@ -1033,6 +1033,7 @@ TEIID31115=Function based index {1} expression {0} is not deterministic. TEIID31116=Function based index {1} expression {0} could not be resolved: {2} TEIID31120=An exception may only be chained to another exception. {0} is not valid. +TEIID31121=The expected result set of the procedure {0} does not match the result set from returnable statement {1} - {2} SQLParser.proc_type_conflict=Result type {1} conflicts with return type {2} for procedure {0} SQLParser.param_out=Procedure {0} RESULT param {1} must be of type OUT. Modified: trunk/engine/src/test/java/org/teiid/query/metadata/TestMetadataValidator.java =================================================================== --- trunk/engine/src/test/java/org/teiid/query/metadata/TestMetadataValidator.java 2012-09-24 15:36:49 UTC (rev 4466) +++ trunk/engine/src/test/java/org/teiid/query/metadata/TestMetadataValidator.java 2012-09-25 01:48:29 UTC (rev 4467) @@ -350,4 +350,15 @@ report = new MetadataValidator().validate(this.vdb, this.store); assertTrue(printError(report), report.hasItems()); } + + @Test public void testResultSet() throws Exception { + String ddl = "create virtual procedure vproc (x integer) returns table (y integer) as begin if (x = 1) select 1; else select 1, 2; end;"; + buildModel("pm1", true, this.vdb, this.store, ddl); + + buildTransformationMetadata(); + + ValidatorReport report = new ValidatorReport(); + report = new MetadataValidator().validate(this.vdb, this.store); + assertTrue(printError(report), report.hasItems()); + } } Modified: trunk/engine/src/test/java/org/teiid/query/processor/proc/TestProcedureProcessor.java =================================================================== --- trunk/engine/src/test/java/org/teiid/query/processor/proc/TestProcedureProcessor.java 2012-09-24 15:36:49 UTC (rev 4466) +++ trunk/engine/src/test/java/org/teiid/query/processor/proc/TestProcedureProcessor.java 2012-09-25 01:48:29 UTC (rev 4467) @@ -30,7 +30,7 @@ import org.junit.Test; import org.mockito.Mockito; -import org.teiid.api.exception.query.QueryMetadataException; +import org.teiid.api.exception.query.QueryPlannerException; import org.teiid.api.exception.query.QueryProcessingException; import org.teiid.api.exception.query.QueryValidatorException; import org.teiid.client.metadata.ParameterInfo; @@ -629,7 +629,7 @@ // Create expected results List[] expected = new List[] { - Arrays.asList(1)}; + Arrays.asList("1")}; helpTestProcess(plan, expected, dataMgr, metadata); } @@ -804,13 +804,11 @@ helpTestProcess(plan, expected, dataMgr, metadata); } - private void addProc(TransformationMetadata metadata, String query) - throws QueryMetadataException { + private void addProc(TransformationMetadata metadata, String query) { addProc(metadata, "sq2", query, new String[] { "e1" }, new String[] { DataTypeManager.DefaultDataTypes.STRING }, new String[0], new String[0]); } - private void addProc(TransformationMetadata metadata, String name, String query, String[] rsCols, String[] rsTypes, String[] params, String[] paramTypes) - throws QueryMetadataException { + private void addProc(TransformationMetadata metadata, String name, String query, String[] rsCols, String[] rsTypes, String[] params, String[] paramTypes) { Schema pm1 = metadata.getMetadataStore().getSchema("PM1"); //$NON-NLS-1$ pm1.getProcedures().remove(name.toUpperCase()); ColumnSet<Procedure> rs2 = RealMetadataFactory.createResultSet("rs1", rsCols, rsTypes); @@ -837,7 +835,7 @@ procedure.append("IF (mycursor.e2>5) \n"); //$NON-NLS-1$ procedure.append("VARIABLES.e2_total=VARIABLES.e2_total+mycursor.e2;\n"); //$NON-NLS-1$ procedure.append("END\n"); //$NON-NLS-1$ - procedure.append("SELECT VARIABLES.e2_total;\n"); //$NON-NLS-1$ + procedure.append("SELECT cast(VARIABLES.e2_total as string);\n"); //$NON-NLS-1$ procedure.append("END"); //$NON-NLS-1$ addProc(metadata, procedure.toString()); @@ -850,10 +848,10 @@ //Create expected results List[] expected = new List[] { - Arrays.asList(new Object[] { Integer.valueOf(66)}), + Arrays.asList(new Object[] { "66"}), }; helpTestProcess(plan, expected, dataMgr, metadata); - } + } @Test public void testDynamicCommandWithParameter() throws Exception { TransformationMetadata metadata = RealMetadataFactory.example1(); @@ -919,7 +917,7 @@ TransformationMetadata metadata = RealMetadataFactory.example1(); addProc(metadata, "sq2", "CREATE VIRTUAL PROCEDURE BEGIN\n" //$NON-NLS-1$ //$NON-NLS-2$ - + "declare object VARIABLES.x; execute string 'SELECT xmlelement(name elem, x)'; select 1; END", new String[] { "e1", "e2" } + + "declare object VARIABLES.x; execute string 'SELECT xmlelement(name elem, x)'; select '1', 2; END", new String[] { "e1", "e2" } , new String[] { DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.INTEGER }, new String[] {"in"}, new String[] {DataTypeManager.DefaultDataTypes.STRING}); String userUpdateStr = "EXEC pm1.sq2('First')"; //$NON-NLS-1$ @@ -995,7 +993,7 @@ "TEIID30168 Couldn't execute the dynamic SQL command \"EXECUTE IMMEDIATE 'EXEC pm1.sq2(''First'')' AS e1 string, e2 integer\" with the SQL statement \"'EXEC pm1.sq2(''First'')'\" due to: TEIID30347 There is a recursive invocation of group 'pm1.sq2'. Please correct the SQL.", metadata); //$NON-NLS-1$ } - @Test public void testDynamicCommandIncorrectProjectSymbolCount() throws Exception { + @Test(expected=QueryPlannerException.class) public void testDynamicCommandIncorrectProjectSymbolCount() throws Exception { //Tests dynamic query with incorrect number of elements TransformationMetadata metadata = RealMetadataFactory.example1(); @@ -1009,11 +1007,7 @@ String userUpdateStr = "EXEC pm1.sq2('test')"; //$NON-NLS-1$ - FakeDataManager dataMgr = exampleDataManager(metadata); - - ProcessorPlan plan = getProcedurePlan(userUpdateStr, metadata); - - helpTestProcessFailure(plan, dataMgr, "TEIID30168 Couldn't execute the dynamic SQL command \"EXECUTE IMMEDIATE 'EXEC pm1.sq1(''First'')' AS e1 string, e2 integer\" with the SQL statement \"'EXEC pm1.sq1(''First'')'\" due to: The dynamic sql string contains an incorrect number of elements.", metadata); //$NON-NLS-1$ + getProcedurePlan(userUpdateStr, metadata); } @Test public void testDynamicCommandPositional() throws Exception { @@ -1106,7 +1100,7 @@ TransformationMetadata metadata = RealMetadataFactory.example1(); addProc(metadata, "sq2", "CREATE VIRTUAL PROCEDURE BEGIN\n" //$NON-NLS-1$ //$NON-NLS-2$ - + "execute string 'select e1,e2 from pm5.g3' as e1 string, e2 integer INTO #temp; select * from #temp; END", new String[] { "e1", "e2"}, new String[] { DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.SHORT}, new String[0], new String[0]); //$NON-NLS-1$ + + "execute string 'select e1,e2 from pm5.g3' as e1 string, e2 integer INTO #temp; select * from #temp; END", new String[] { "e1", "e2"}, new String[] { DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.INTEGER}, new String[0], new String[0]); //$NON-NLS-1$ String userUpdateStr = "EXEC pm1.sq2()"; //$NON-NLS-1$ @@ -1210,7 +1204,7 @@ procedure.append("select e1 into #temp from pm1.g1;\n"); //$NON-NLS-1$ procedure.append("VARIABLES.e2_total=select count(*) from #temp;\n"); //$NON-NLS-1$ procedure.append("END\n"); //$NON-NLS-1$ - procedure.append("SELECT VARIABLES.e2_total;\n"); //$NON-NLS-1$ + procedure.append("SELECT cast(VARIABLES.e2_total as string);\n"); //$NON-NLS-1$ procedure.append("END"); //$NON-NLS-1$ addProc(metadata, procedure.toString()); @@ -1223,7 +1217,7 @@ //Create expected results List[] expected = new List[] { - Arrays.asList(new Object[] { Integer.valueOf(3)}), + Arrays.asList("3"), }; helpTestProcess(plan, expected, dataMgr, metadata); } @@ -1244,7 +1238,7 @@ procedure.append("BEGIN\n"); //$NON-NLS-1$ procedure.append("e2_total=e2_total+mycursor.x;"); //$NON-NLS-1$ procedure.append("END\n"); //$NON-NLS-1$ - procedure.append("SELECT VARIABLES.e2_total;\n"); //$NON-NLS-1$ + procedure.append("SELECT cast(VARIABLES.e2_total as string);\n"); //$NON-NLS-1$ procedure.append("END"); //$NON-NLS-1$ addProc(metadata, procedure.toString()); @@ -1257,7 +1251,7 @@ //Create expected results List[] expected = new List[] { - Arrays.asList(new Object[] { Integer.valueOf(76)}), + Arrays.asList("76"), }; helpTestProcess(plan, expected, dataMgr, metadata); } @@ -1312,7 +1306,7 @@ procedure.append("BEGIN\n"); //$NON-NLS-1$ procedure.append("create local temporary table t1 (e1 integer);\n"); //$NON-NLS-1$ procedure.append("create local temporary table T1 (e1 integer);\n"); //$NON-NLS-1$ - procedure.append("SELECT e1 from t1;\n"); //$NON-NLS-1$ + procedure.append("SELECT cast(e1 as string) from t1;\n"); //$NON-NLS-1$ procedure.append("END"); //$NON-NLS-1$ addProc(metadata, procedure.toString()); @@ -1339,7 +1333,7 @@ procedure.append("select e1 into t1 from pm1.g1;\n"); //$NON-NLS-1$ procedure.append("drop table t1;\n"); //$NON-NLS-1$ procedure.append("drop table t1;\n"); //$NON-NLS-1$ - procedure.append("SELECT 1;\n"); //$NON-NLS-1$ + procedure.append("SELECT '1';\n"); //$NON-NLS-1$ procedure.append("END"); //$NON-NLS-1$ addProc(metadata, procedure.toString()); @@ -1350,7 +1344,7 @@ ProcessorPlan plan = getProcedurePlan(userUpdateStr, metadata); - helpTestProcess(plan, new List[] {Arrays.asList(1)}, dataMgr, metadata); + helpTestProcess(plan, new List[] {Arrays.asList("1")}, dataMgr, metadata); } /** @@ -1504,7 +1498,7 @@ .append("insert into #temp (e1) values (convert(rand() * 1000, integer));\n") //$NON-NLS-1$ .append("x = x + 1;\n") //$NON-NLS-1$ .append("END\n") //$NON-NLS-1$ - .append("SELECT * FROM #TEMP;\n") //$NON-NLS-1$ + .append("SELECT cast(e1 as string) FROM #TEMP;\n") //$NON-NLS-1$ .append("END"); //$NON-NLS-1$ QueryMetadataInterface metadata = createProcedureMetadata(procedure.toString()); @@ -1517,11 +1511,11 @@ helpTestProcess(plan, new List[] { - Arrays.asList(new Object[] {Integer.valueOf(240)}), - Arrays.asList(new Object[] {Integer.valueOf(637)})}, dataMgr, metadata); + Arrays.asList("240"), + Arrays.asList("637")}, dataMgr, metadata); } - private QueryMetadataInterface createProcedureMetadata(String procedure) throws QueryMetadataException { + private QueryMetadataInterface createProcedureMetadata(String procedure) { TransformationMetadata metadata = RealMetadataFactory.example1(); addProc(metadata, "sq1", procedure, new String[] { "e1" }, new String[] { DataTypeManager.DefaultDataTypes.STRING }, new String[0], new String[0]); return metadata; @@ -1589,7 +1583,7 @@ ColumnSet<Procedure> rs2 = RealMetadataFactory.createResultSet("pm1.rs2", new String[] { "e1" }, new String[] { DataTypeManager.DefaultDataTypes.XML }); //$NON-NLS-1$ //$NON-NLS-2$ ProcedureParameter rs2p2 = RealMetadataFactory.createParameter("input", ParameterInfo.IN, DataTypeManager.DefaultDataTypes.INTEGER); //$NON-NLS-1$ QueryNode sq2n1 = new QueryNode("CREATE VIRTUAL PROCEDURE BEGIN\n" //$NON-NLS-1$ //$NON-NLS-2$ - + "declare integer VARIABLES.x = proc.input; declare xml y = SELECT * FROM xmltest.doc9 WHERE context(SupplierID, OrderID)=x OR OrderID='2'; select convert(y, string); END"); //$NON-NLS-1$ + + "declare integer VARIABLES.x = proc.input; declare xml y = SELECT * FROM xmltest.doc9 WHERE context(SupplierID, OrderID)=x OR OrderID='2'; select y; END"); //$NON-NLS-1$ Procedure sq2 = RealMetadataFactory.createVirtualProcedure("proc", pm1, Arrays.asList(rs2p2), sq2n1); //$NON-NLS-1$ sq2.setResultSet(rs2); @@ -1734,7 +1728,7 @@ procedure += " statement. */ "; //$NON-NLS-1$ procedure += " VARIABLES.var1 = L1.BCol3;\n"; //$NON-NLS-1$ procedure += " END\n"; //$NON-NLS-1$ - procedure += " SELECT VARIABLES.Var1 AS e1;\n"; //$NON-NLS-1$ + procedure += " SELECT cast(VARIABLES.Var1 as string) AS e1;\n"; //$NON-NLS-1$ procedure += "END\n"; //$NON-NLS-1$ QueryMetadataInterface metadata = createProcedureMetadata(procedure); @@ -1742,7 +1736,7 @@ FakeDataManager dataMgr = exampleDataManager(metadata); ProcessorPlan plan = getProcedurePlan(userQuery, metadata); - List[] expected = new List[] {Arrays.asList(new Object[] {Integer.valueOf(3)})}; + List[] expected = new List[] {Arrays.asList("3")}; helpTestProcess(plan, expected, dataMgr, metadata); } @@ -1776,7 +1770,8 @@ " " + sql + ";" + //$NON-NLS-1$ //$NON-NLS-2$ "END"; //$NON-NLS-1$ - QueryMetadataInterface metadata = createProcedureMetadata(proc); + TransformationMetadata metadata = RealMetadataFactory.example1(); + addProc(metadata, "sq1", proc, new String[] { "e1", "e2", "e3", "e4" }, new String[] { DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.INTEGER, DataTypeManager.DefaultDataTypes.INTEGER }, new String[0], new String[0]); String userQuery = "SELECT * FROM (EXEC pm1.sq1()) as proc"; //$NON-NLS-1$ FakeDataManager dataMgr = exampleDataManager2(metadata); ProcessorPlan plan = getProcedurePlan(userQuery, metadata); @@ -1822,7 +1817,8 @@ " " + sql + ";" + //$NON-NLS-1$ //$NON-NLS-2$ "END"; //$NON-NLS-1$ - QueryMetadataInterface metadata = createProcedureMetadata(proc); + TransformationMetadata metadata = RealMetadataFactory.example1(); + addProc(metadata, "sq1", proc, new String[] { "e1", "e2", "e3", "e4" }, new String[] { DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.INTEGER, DataTypeManager.DefaultDataTypes.INTEGER }, new String[0], new String[0]); String userQuery = "SELECT * FROM (EXEC pm1.sq1()) as proc"; //$NON-NLS-1$ FakeDataManager dataMgr = exampleDataManager2(metadata); ProcessorPlan plan = getProcedurePlan(userQuery, metadata, TestOptimizer.getGenericFinder()); @@ -1860,7 +1856,7 @@ "BEGIN " + //$NON-NLS-1$ " create local temporary table t1 (e1 string);\n" + //$NON-NLS-1$ " select e1 into t1 from pm1.g1;\n" + //$NON-NLS-1$ - " select e2 from (exec pm1.sq2((select max(e1) from t1))) x;\n" + //$NON-NLS-1$ + " select cast(e2 as string) from (exec pm1.sq2((select max(e1) from t1))) x;\n" + //$NON-NLS-1$ "END"; //$NON-NLS-1$ QueryMetadataInterface metadata = createProcedureMetadata(proc); @@ -1869,7 +1865,7 @@ ProcessorPlan plan = getProcedurePlan(userQuery, metadata, TestOptimizer.getGenericFinder()); List[] expected = new List[] { - Arrays.asList( 51 ), + Arrays.asList( "51" ), }; helpTestProcess(plan, expected, dataMgr, metadata); } @@ -1909,7 +1905,7 @@ procedure.append("exec pm1.sq2(in1 || 'foo');\n"); //$NON-NLS-1$ procedure.append("END"); //$NON-NLS-1$ - addProc(metadata, "sq1", procedure.toString(), new String[] { "e1" }, new String[] { DataTypeManager.DefaultDataTypes.STRING }, new String[] {"in1"}, new String[] {DataTypeManager.DefaultDataTypes.INTEGER}); + addProc(metadata, "sq1", procedure.toString(), new String[] { "e1", "e2" }, new String[] { DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.INTEGER }, new String[] {"in1"}, new String[] {DataTypeManager.DefaultDataTypes.INTEGER}); String userUpdateStr = "EXEC pm1.sq1(1)"; //$NON-NLS-1$ Modified: trunk/engine/src/test/java/org/teiid/query/resolver/TestAlterResolving.java =================================================================== --- trunk/engine/src/test/java/org/teiid/query/resolver/TestAlterResolving.java 2012-09-24 15:36:49 UTC (rev 4466) +++ trunk/engine/src/test/java/org/teiid/query/resolver/TestAlterResolving.java 2012-09-25 01:48:29 UTC (rev 4467) @@ -30,6 +30,7 @@ import org.teiid.query.sql.lang.AlterTrigger; import org.teiid.query.sql.lang.AlterView; import org.teiid.query.sql.lang.Query; +import org.teiid.query.sql.proc.CommandStatement; import org.teiid.query.sql.symbol.ElementSymbol; import org.teiid.query.unittest.RealMetadataFactory; @@ -44,7 +45,7 @@ @Test public void testAlterProcedure() { AlterProcedure alterProc = (AlterProcedure) helpResolve("alter procedure MMSP5 as begin select param1; end", RealMetadataFactory.exampleBQTCached()); assertNotNull(alterProc.getTarget().getMetadataID()); - Query q = (Query)alterProc.getDefinition().getResultsCommand(); + Query q = (Query)((CommandStatement)alterProc.getDefinition().getBlock().getStatements().get(0)).getCommand(); assertTrue(((ElementSymbol)q.getSelect().getSymbol(0)).isExternalReference()); } Modified: trunk/engine/src/test/java/org/teiid/query/sql/proc/TestCreateUpdateProcedureCommand.java =================================================================== --- trunk/engine/src/test/java/org/teiid/query/sql/proc/TestCreateUpdateProcedureCommand.java 2012-09-24 15:36:49 UTC (rev 4466) +++ trunk/engine/src/test/java/org/teiid/query/sql/proc/TestCreateUpdateProcedureCommand.java 2012-09-25 01:48:29 UTC (rev 4467) @@ -22,27 +22,18 @@ package org.teiid.query.sql.proc; -import junit.framework.TestCase; +import static org.junit.Assert.*; +import org.junit.Test; import org.teiid.core.util.UnitTestUtil; -import org.teiid.query.resolver.TestResolver; -import org.teiid.query.sql.lang.StoredProcedure; -import org.teiid.query.unittest.RealMetadataFactory; /** * * @author gchadalavadaDec 9, 2002 */ -@SuppressWarnings("nls") -public class TestCreateUpdateProcedureCommand extends TestCase { +public class TestCreateUpdateProcedureCommand { - // ################################## FRAMEWORK ################################ - - public TestCreateUpdateProcedureCommand(String name) { - super(name); - } - // ################################## TEST HELPERS ################################ public static final CreateProcedureCommand sample1() { @@ -59,7 +50,7 @@ // ################################## ACTUAL TESTS ################################ - public void testGetBlock() { + @Test public void testGetBlock() { CreateProcedureCommand b1 = sample1(); assertTrue("Incorrect Block on command", b1.getBlock().equals(TestBlock.sample1())); //$NON-NLS-1$ } @@ -99,12 +90,4 @@ UnitTestUtil.helpTestEquivalence(0, s1, s2); } - public void testProjectedSymbols() { - CreateProcedureCommand cupc = new CreateProcedureCommand(); - StoredProcedure sp = (StoredProcedure)TestResolver.helpResolve("call TEIIDSP9(p1=>1, p2=>?)", RealMetadataFactory.exampleBQTCached()); - sp.setCallableStatement(true); - cupc.setResultsCommand(sp); - assertEquals(1, cupc.getProjectedSymbols().size()); - } - } Modified: trunk/engine/src/test/java/org/teiid/query/unittest/RealMetadataFactory.java =================================================================== --- trunk/engine/src/test/java/org/teiid/query/unittest/RealMetadataFactory.java 2012-09-24 15:36:49 UTC (rev 4466) +++ trunk/engine/src/test/java/org/teiid/query/unittest/RealMetadataFactory.java 2012-09-25 01:48:29 UTC (rev 4467) @@ -1105,14 +1105,14 @@ QueryNode sq16n1 = new QueryNode("CREATE VIRTUAL PROCEDURE BEGIN INSERT INTO pm1.g1 ( e1, e2 ) VALUES( 1, 2 ); END"); //$NON-NLS-1$ //$NON-NLS-2$ createVirtualProcedure("sq16", pm1, null, sq16n1); //$NON-NLS-1$ - ColumnSet<Procedure> rs19 = createResultSet("pm1.rs19", new String[] { "xml" }, new String[] { DataTypeManager.DefaultDataTypes.STRING }); //$NON-NLS-1$ //$NON-NLS-2$ + ColumnSet<Procedure> rs19 = createResultSet("pm1.rs19", new String[] { "xml" }, new String[] { DataTypeManager.DefaultDataTypes.XML }); //$NON-NLS-1$ //$NON-NLS-2$ QueryNode sq17n1 = new QueryNode("CREATE VIRTUAL PROCEDURE BEGIN SELECT * FROM xmltest.doc1; END"); //$NON-NLS-1$ //$NON-NLS-2$ Procedure sq17 = createVirtualProcedure("sq17", pm1, null, sq17n1); //$NON-NLS-1$ sq17.setResultSet(rs19); createStoredProcedure("sp3", pm1, null); //$NON-NLS-1$ //$NON-NLS-2$ - ColumnSet<Procedure> rs20 = createResultSet("pm1.rs20", new String[] { "xml" }, new String[] { DataTypeManager.DefaultDataTypes.STRING }); //$NON-NLS-1$ //$NON-NLS-2$ + ColumnSet<Procedure> rs20 = createResultSet("pm1.rs20", new String[] { "xml" }, new String[] { DataTypeManager.DefaultDataTypes.XML }); //$NON-NLS-1$ //$NON-NLS-2$ QueryNode sq18n1 = new QueryNode("CREATE VIRTUAL PROCEDURE BEGIN SELECT * FROM xmltest.doc1; END"); //$NON-NLS-1$ //$NON-NLS-2$ Procedure sq18 = createVirtualProcedure("sq18", pm1, null, sq18n1); //$NON-NLS-1$ sq18.setResultSet(rs20); @@ -1238,7 +1238,7 @@ QueryNode vspqn24 = new QueryNode("CREATE VIRTUAL PROCEDURE BEGIN SELECT e1, e2 INTO #temptable FROM pm1.g1; SELECT #temptable.e1 FROM #temptable WHERE #temptable.e2=15; END"); //$NON-NLS-1$ //$NON-NLS-2$ Procedure vsp24 = createVirtualProcedure("vsp24", pm1, null, vspqn24); //$NON-NLS-1$ - vsp24.setResultSet(vspp4()); + vsp24.setResultSet(vsprs1()); QueryNode vspqn25 = new QueryNode("CREATE VIRTUAL PROCEDURE BEGIN SELECT e1 INTO #temptable FROM pm1.g1 WHERE e1 ='no match'; SELECT e1 FROM #temptable; END"); //$NON-NLS-1$ //$NON-NLS-2$ Procedure vsp25 = createVirtualProcedure("vsp25", pm1, null, vspqn25); //$NON-NLS-1$ @@ -1283,7 +1283,7 @@ Procedure vsp41 = createVirtualProcedure("vsp41", pm1, null, vspqn41); //$NON-NLS-1$ vsp41.setResultSet(vsprs1()); - QueryNode vspqn37 = new QueryNode("CREATE VIRTUAL PROCEDURE BEGIN DECLARE integer x; VARIABLES.x=5; INSERT INTO vm1.g1(e2) values(VARIABLES.x); SELECT ROWCOUNT; END"); //$NON-NLS-1$ //$NON-NLS-2$ + QueryNode vspqn37 = new QueryNode("CREATE VIRTUAL PROCEDURE BEGIN DECLARE integer x; VARIABLES.x=5; INSERT INTO vm1.g1(e2) values(VARIABLES.x); SELECT cast(ROWCOUNT as string); END"); //$NON-NLS-1$ //$NON-NLS-2$ Procedure vsp37 = createVirtualProcedure("vsp37", pm1, null, vspqn37); //$NON-NLS-1$ vsp37.setResultSet(vsprs1()); @@ -1467,7 +1467,7 @@ ProcedureParameter vspp55 = createParameter("param1", ParameterInfo.IN, DataTypeManager.DefaultDataTypes.INTEGER); //$NON-NLS-1$ QueryNode vspqn55 = new QueryNode("CREATE VIRTUAL PROCEDURE BEGIN select e1, param1 as a from vm1.g1; END"); //$NON-NLS-1$ //$NON-NLS-2$ Procedure vsp55 = createVirtualProcedure("vsp55", pm1, Arrays.asList( vspp55 ), vspqn55); //$NON-NLS-1$ - vsp55.setResultSet(vsprs1()); + vsp55.setResultSet(vspp4()); QueryNode vspqn56 = new QueryNode("CREATE VIRTUAL PROCEDURE BEGIN SELECT * INTO #temptable FROM pm1.g1; SELECT #temptable.e1 FROM #temptable; END"); //$NON-NLS-1$ //$NON-NLS-2$ Procedure vsp56 = createVirtualProcedure("vsp56", pm1, null, vspqn56); //$NON-NLS-1$