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$