/*
* 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.resolver;
import static org.junit.Assert.*;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Map;
import org.junit.Test;
import org.teiid.api.exception.query.QueryMetadataException;
import org.teiid.api.exception.query.QueryParserException;
import org.teiid.api.exception.query.QueryResolverException;
import org.teiid.core.TeiidComponentException;
import org.teiid.core.types.DataTypeManager;
import org.teiid.dqp.internal.datamgr.LanguageBridgeFactory;
import org.teiid.language.Call;
import org.teiid.metadata.Table;
import org.teiid.query.eval.Evaluator;
import org.teiid.query.metadata.QueryMetadataInterface;
import org.teiid.query.metadata.TempMetadataAdapter;
import org.teiid.query.metadata.TempMetadataID;
import org.teiid.query.metadata.TransformationMetadata;
import org.teiid.query.parser.QueryParser;
import org.teiid.query.rewriter.QueryRewriter;
import org.teiid.query.sql.ProcedureReservedWords;
import org.teiid.query.sql.lang.Command;
import org.teiid.query.sql.lang.Insert;
import org.teiid.query.sql.lang.ProcedureContainer;
import org.teiid.query.sql.lang.SPParameter;
import org.teiid.query.sql.lang.StoredProcedure;
import org.teiid.query.sql.proc.AssignmentStatement;
import org.teiid.query.sql.proc.Block;
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.TriggerAction;
import org.teiid.query.sql.symbol.Array;
import org.teiid.query.sql.symbol.Constant;
import org.teiid.query.sql.symbol.ElementSymbol;
import org.teiid.query.sql.symbol.Expression;
import org.teiid.query.sql.symbol.Symbol;
import org.teiid.query.sql.visitor.CommandCollectorVisitor;
import org.teiid.query.sql.visitor.ElementCollectorVisitor;
import org.teiid.query.unittest.RealMetadataFactory;
import org.teiid.query.validator.TestValidator;
@SuppressWarnings("nls")
public class TestProcedureResolving {
private void helpFailUpdateProcedure(String procedure, String userUpdateStr, Table.TriggerEvent procedureType) {
helpFailUpdateProcedure(procedure, userUpdateStr, procedureType, null);
}
private void helpFailUpdateProcedure(String procedure, String userUpdateStr, Table.TriggerEvent procedureType, String msg) {
// resolve
try {
helpResolveUpdateProcedure(procedure, userUpdateStr, procedureType);
fail("Expected a QueryResolverException but got none."); //$NON-NLS-1$
} catch(QueryResolverException ex) {
if (msg != null) {
assertEquals(msg, ex.getMessage());
}
} catch (TeiidComponentException e) {
throw new RuntimeException(e);
} catch (QueryParserException e) {
throw new RuntimeException(e);
}
}
@Test public void testDefect13029_CorrectlySetUpdateProcedureTempGroupIDs() throws Exception {
StringBuffer proc = new StringBuffer("FOR EACH ROW") //$NON-NLS-1$
.append("\nBEGIN") //$NON-NLS-1$
.append("\nDECLARE string var1;") //$NON-NLS-1$
.append("\nvar1 = '';") //$NON-NLS-1$
.append("\n LOOP ON (SELECT pm1.g1.e1 FROM pm1.g1) AS loopCursor") //$NON-NLS-1$
.append("\n BEGIN") //$NON-NLS-1$
.append("\n LOOP ON (SELECT pm1.g2.e1 FROM pm1.g2 WHERE loopCursor.e1 = pm1.g2.e1) AS loopCursor2") //$NON-NLS-1$
.append("\n BEGIN") //$NON-NLS-1$
.append("\n var1 = CONCAT(var1, CONCAT(' ', loopCursor2.e1));") //$NON-NLS-1$
.append("\n END") //$NON-NLS-1$
.append("\n END") //$NON-NLS-1$
.append("\nEND"); //$NON-NLS-1$
String userUpdateStr = "UPDATE vm1.g1 SET e1='x'"; //$NON-NLS-1$
Command command = helpResolveUpdateProcedure(proc.toString(), userUpdateStr, Table.TriggerEvent.UPDATE);
Map<String, TempMetadataID> tempIDs = command.getTemporaryMetadata().getData();
assertNotNull(tempIDs);
assertNull(tempIDs.get("LOOPCURSOR")); //$NON-NLS-1$
assertNull(tempIDs.get("LOOPCURSOR2")); //$NON-NLS-1$
Command subCommand = CommandCollectorVisitor.getCommands(command).get(0);
tempIDs = subCommand.getTemporaryMetadata().getData();
assertNotNull(tempIDs);
assertNull(tempIDs.get("LOOPCURSOR")); //$NON-NLS-1$
assertNull(tempIDs.get("LOOPCURSOR2")); //$NON-NLS-1$
subCommand = CommandCollectorVisitor.getCommands(command).get(1);
tempIDs = subCommand.getTemporaryMetadata().getData();
assertNotNull(tempIDs);
assertNotNull(tempIDs.get("LOOPCURSOR")); //$NON-NLS-1$
assertNull(tempIDs.get("LOOPCURSOR2")); //$NON-NLS-1$
}
private TriggerAction helpResolveUpdateProcedure(String procedure, String userUpdateStr, Table.TriggerEvent procedureType) throws QueryParserException, QueryResolverException, TeiidComponentException {
QueryMetadataInterface metadata = RealMetadataFactory.exampleUpdateProc(procedureType, procedure);
return (TriggerAction) resolveProcedure(userUpdateStr, metadata);
}
private Command resolveProcedure(String userUpdateStr,
QueryMetadataInterface metadata) throws QueryParserException,
QueryResolverException, TeiidComponentException,
QueryMetadataException {
ProcedureContainer userCommand = (ProcedureContainer)QueryParser.getQueryParser().parseCommand(userUpdateStr);
QueryResolver.resolveCommand(userCommand, metadata);
metadata = new TempMetadataAdapter(metadata, userCommand.getTemporaryMetadata());
return QueryResolver.expandCommand(userCommand, metadata, null);
}
private void helpResolveException(String userUpdateStr, QueryMetadataInterface metadata, String msg) throws QueryParserException, TeiidComponentException {
try {
helpResolve(userUpdateStr, metadata);
fail();
} catch (QueryResolverException e) {
assertEquals(msg, e.getMessage());
}
}
private Command helpResolve(String userUpdateStr, QueryMetadataInterface metadata) throws QueryParserException, QueryResolverException, TeiidComponentException {
return resolveProcedure(userUpdateStr, metadata);
}
/**
* Constants will now auto resolve if they are consistently representable in the target type
*/
@Test public void testDefect23257() throws Exception{
CreateProcedureCommand command = (CreateProcedureCommand) helpResolve("EXEC pm6.vsp59()", RealMetadataFactory.example1Cached()); //$NON-NLS-1$
CommandStatement cs = (CommandStatement)command.getBlock().getStatements().get(1);
Insert insert = (Insert)cs.getCommand();
assertEquals(DataTypeManager.DefaultDataClasses.SHORT, ((Expression)insert.getValues().get(1)).getType());
}
@Test public void testProcedureScoping() throws Exception {
StringBuffer proc = new StringBuffer("FOR EACH ROW") //$NON-NLS-1$
.append("\nBEGIN") //$NON-NLS-1$
//note that this declare takes presedense over the proc INPUTS.e1 and CHANGING.e1 variables
.append("\n declare integer e1 = 1;") //$NON-NLS-1$
.append("\n e1 = e1;") //$NON-NLS-1$
.append("\n LOOP ON (SELECT pm1.g1.e1 FROM pm1.g1) AS loopCursor") //$NON-NLS-1$
.append("\n BEGIN") //$NON-NLS-1$
//inside the scope of the loop, an unqualified e1 should resolve to the loop variable group
.append("\n variables.e1 = convert(e1, integer);") //$NON-NLS-1$
.append("\n END") //$NON-NLS-1$
.append("\nEND"); //$NON-NLS-1$
String userUpdateStr = "UPDATE vm1.g1 SET e1='x'"; //$NON-NLS-1$
TriggerAction command = helpResolveUpdateProcedure(proc.toString(), userUpdateStr,
Table.TriggerEvent.UPDATE);
Block block = command.getBlock();
AssignmentStatement assStmt = (AssignmentStatement)block.getStatements().get(1);
assertEquals(ProcedureReservedWords.VARIABLES, assStmt.getVariable().getGroupSymbol().getName());
assertEquals(ProcedureReservedWords.VARIABLES, ((ElementSymbol)assStmt.getExpression()).getGroupSymbol().getName());
Block inner = ((LoopStatement)block.getStatements().get(2)).getBlock();
assStmt = (AssignmentStatement)inner.getStatements().get(0);
ElementSymbol value = ElementCollectorVisitor.getElements(assStmt.getExpression(), false).iterator().next();
assertEquals("loopCursor", value.getGroupSymbol().getName()); //$NON-NLS-1$
}
@Test(expected=QueryResolverException.class) public void testBlockResolving() throws Exception {
StringBuffer proc = new StringBuffer("FOR EACH ROW") //$NON-NLS-1$
.append("\nBEGIN") //$NON-NLS-1$
//note that this declare takes presedense over the proc INPUTS.e1 and CHANGING.e1 variables
.append("\n declare integer e1 = 1;") //$NON-NLS-1$
.append("\n BEGIN") //$NON-NLS-1$
//inside the scope of the loop, an unqualified e1 should resolve to the loop variable group
.append("\n variables.e1 = e2;") //$NON-NLS-1$
.append("\n END") //$NON-NLS-1$
.append("\nEND"); //$NON-NLS-1$
String userUpdateStr = "UPDATE vm1.g1 SET e1='x'"; //$NON-NLS-1$
helpResolveUpdateProcedure(proc.toString(), userUpdateStr,
Table.TriggerEvent.UPDATE);
}
// variable resolution, variable used in if statement, variable compared against
// different datatype element
@Test public void testCreateUpdateProcedure4() {
String procedure = "FOR EACH ROW "; //$NON-NLS-1$
procedure = procedure + "BEGIN\n"; //$NON-NLS-1$
procedure = procedure + "DECLARE boolean var1;\n"; //$NON-NLS-1$
procedure = procedure + "if(var1 =1);\n"; //$NON-NLS-1$
procedure = procedure + "var1 = Select pm1.g1.e2 from pm1.g1;\n"; //$NON-NLS-1$
procedure = procedure + "END\n"; //$NON-NLS-1$
String userUpdateStr = "UPDATE vm1.g1 SET e1='x'"; //$NON-NLS-1$
helpFailUpdateProcedure(procedure, userUpdateStr,
Table.TriggerEvent.UPDATE);
}
// variable resolution, variable used in if statement, invalid operation on variable
@Test public void testCreateUpdateProcedure5() {
String procedure = "FOR EACH ROW "; //$NON-NLS-1$
procedure = procedure + "BEGIN\n"; //$NON-NLS-1$
procedure = procedure + "DECLARE boolean var1;\n"; //$NON-NLS-1$
procedure = procedure + "var1 = var1 + var1;\n"; //$NON-NLS-1$
procedure = procedure + "var1 = Select pm1.g1.e2 from pm1.g1 whwre var1 = var1+var1;\n"; //$NON-NLS-1$
procedure = procedure + "END\n"; //$NON-NLS-1$
String userUpdateStr = "UPDATE vm1.g1 SET e1='x'"; //$NON-NLS-1$
helpFailUpdateProcedure(procedure, userUpdateStr,
Table.TriggerEvent.UPDATE);
}
// variable resolution, variables declared in different blocks local variables
// should not override
@Test public void testCreateUpdateProcedure6() {
String procedure = "FOR EACH ROW "; //$NON-NLS-1$
procedure = procedure + "BEGIN\n"; //$NON-NLS-1$
procedure = procedure + "DECLARE integer var1;\n"; //$NON-NLS-1$
procedure = procedure + "if(var1 =1)\n"; //$NON-NLS-1$
procedure = procedure + "BEGIN\n"; //$NON-NLS-1$
procedure = procedure + "DECLARE boolean var1;\n"; //$NON-NLS-1$
procedure = procedure + "Select pm1.g1.e2 from pm1.g1 where var1 = pm1.g1.e3;\n"; //$NON-NLS-1$
procedure = procedure + "END\n"; //$NON-NLS-1$
procedure = procedure + "END\n"; //$NON-NLS-1$
String userUpdateStr = "UPDATE vm1.g1 SET e1='x'"; //$NON-NLS-1$
helpFailUpdateProcedure(procedure, userUpdateStr,
Table.TriggerEvent.UPDATE, "Variable var1 was previously declared."); //$NON-NLS-1$
}
// variable resolution, variables declared in different blocks local variables
// inner block using outer block variables
@Test public void testCreateUpdateProcedure7() throws Exception {
String procedure = "FOR EACH ROW "; //$NON-NLS-1$
procedure = procedure + "BEGIN\n"; //$NON-NLS-1$
procedure = procedure + "DECLARE integer var1;\n"; //$NON-NLS-1$
procedure = procedure + "if(var1 =1)\n"; //$NON-NLS-1$
procedure = procedure + "BEGIN\n"; //$NON-NLS-1$
procedure = procedure + "DECLARE boolean var2;\n"; //$NON-NLS-1$
procedure = procedure + "Select pm1.g1.e2 from pm1.g1 where var1 = pm1.g1.e1;\n"; //$NON-NLS-1$
procedure = procedure + "END\n"; //$NON-NLS-1$
procedure = procedure + "END\n"; //$NON-NLS-1$
String userUpdateStr = "UPDATE vm1.g1 SET e1='x'"; //$NON-NLS-1$
helpResolveUpdateProcedure(procedure, userUpdateStr,
Table.TriggerEvent.UPDATE);
}
// variable resolution, variables declared in different blocks local variables
// outer block cannot use inner block variables
@Test public void testCreateUpdateProcedure8() {
String procedure = "FOR EACH ROW "; //$NON-NLS-1$
procedure = procedure + "BEGIN\n"; //$NON-NLS-1$
procedure = procedure + "DECLARE integer var1;\n"; //$NON-NLS-1$
procedure = procedure + "if(var1 =1)\n"; //$NON-NLS-1$
procedure = procedure + "BEGIN\n"; //$NON-NLS-1$
procedure = procedure + "DECLARE integer var2;\n"; //$NON-NLS-1$
procedure = procedure + "Select pm1.g1.e2 from pm1.g1 where var1 = pm1.g1.e1;\n"; //$NON-NLS-1$
procedure = procedure + "END\n"; //$NON-NLS-1$
procedure = procedure + "var2 = 1\n"; //$NON-NLS-1$
procedure = procedure + "END\n"; //$NON-NLS-1$
String userUpdateStr = "UPDATE vm1.g1 SET e1='x'"; //$NON-NLS-1$
helpFailUpdateProcedure(procedure, userUpdateStr,
Table.TriggerEvent.UPDATE);
}
// variable resolution, variables declared in different blocks local variables
// should override, outer block variables still valid afetr inner block is declared
@Test public void testCreateUpdateProcedure9() {
String procedure = "FOR EACH ROW "; //$NON-NLS-1$
procedure = procedure + "BEGIN\n"; //$NON-NLS-1$
procedure = procedure + "DECLARE integer var1;\n"; //$NON-NLS-1$
procedure = procedure + "if(var1 =1)\n"; //$NON-NLS-1$
procedure = procedure + "BEGIN\n"; //$NON-NLS-1$
procedure = procedure + "DECLARE boolean var1;\n"; //$NON-NLS-1$
procedure = procedure + "Select pm1.g1.e2 from pm1.g1 where var1 = pm1.g1.e3;\n"; //$NON-NLS-1$
procedure = procedure + "END\n"; //$NON-NLS-1$
procedure = procedure + "var1 = var1 +1;\n"; //$NON-NLS-1$
procedure = procedure + "END\n"; //$NON-NLS-1$
String userUpdateStr = "UPDATE vm1.g1 SET e1='x'"; //$NON-NLS-1$
helpFailUpdateProcedure(procedure, userUpdateStr,
Table.TriggerEvent.UPDATE);
}
// using declare variable that has parts
@Test public void testCreateUpdateProcedure24() {
String procedure = "FOR EACH ROW "; //$NON-NLS-1$
procedure = procedure + "BEGIN\n"; //$NON-NLS-1$
procedure = procedure + "DECLARE integer var2.var1;\n"; //$NON-NLS-1$
procedure = procedure + "END\n"; //$NON-NLS-1$
String userUpdateStr = "UPDATE vm1.g1 SET e1='x'"; //$NON-NLS-1$
helpFailUpdateProcedure(procedure, userUpdateStr,
Table.TriggerEvent.UPDATE);
}
// using declare variable is qualified
@Test public void testCreateUpdateProcedure26() throws Exception {
String procedure = "FOR EACH ROW "; //$NON-NLS-1$
procedure = procedure + "BEGIN\n"; //$NON-NLS-1$
procedure = procedure + "DECLARE integer VARIABLES.var1;\n"; //$NON-NLS-1$
procedure = procedure + "END\n"; //$NON-NLS-1$
String userUpdateStr = "UPDATE vm1.g1 SET e1='x'"; //$NON-NLS-1$
helpResolveUpdateProcedure(procedure, userUpdateStr,
Table.TriggerEvent.UPDATE);
}
// using declare variable is qualified but has more parts
@Test public void testCreateUpdateProcedure27() {
String procedure = "FOR EACH ROW "; //$NON-NLS-1$
procedure = procedure + "BEGIN\n"; //$NON-NLS-1$
procedure = procedure + "DECLARE integer VARIABLES.var1.var2;\n"; //$NON-NLS-1$
procedure = procedure + "END\n"; //$NON-NLS-1$
String userUpdateStr = "UPDATE vm1.g1 SET e1='x'"; //$NON-NLS-1$
helpFailUpdateProcedure(procedure, userUpdateStr,
Table.TriggerEvent.UPDATE);
}
// using a variable that has not been declared in an assignment stmt
@Test public void testCreateUpdateProcedure28() {
String procedure = "FOR EACH ROW "; //$NON-NLS-1$
procedure = procedure + "BEGIN\n"; //$NON-NLS-1$
procedure = procedure + "var1 = Select pm1.g1.e2 from pm1.g1;\n"; //$NON-NLS-1$
procedure = procedure + "END\n"; //$NON-NLS-1$
String userUpdateStr = "UPDATE vm1.g1 SET e1='x'"; //$NON-NLS-1$
helpFailUpdateProcedure(procedure, userUpdateStr,
Table.TriggerEvent.UPDATE);
}
// using a variable that has not been declared in an assignment stmt
@Test public void testCreateUpdateProcedure29() {
String procedure = "FOR EACH ROW "; //$NON-NLS-1$
procedure = procedure + "BEGIN\n"; //$NON-NLS-1$
procedure = procedure + "var1 = 1;\n"; //$NON-NLS-1$
procedure = procedure + "END\n"; //$NON-NLS-1$
String userUpdateStr = "UPDATE vm1.g1 SET e1='x'"; //$NON-NLS-1$
helpFailUpdateProcedure(procedure, userUpdateStr,
Table.TriggerEvent.UPDATE);
}
// using invalid function in assignment expr
@Test public void testCreateUpdateProcedure30() {
String procedure = "FOR EACH ROW "; //$NON-NLS-1$
procedure = procedure + "BEGIN\n"; //$NON-NLS-1$
procedure = procedure + "Declare integer var1;\n"; //$NON-NLS-1$
procedure = procedure + "var1 = 'x' + ROWS_UPDATED;\n"; //$NON-NLS-1$
procedure = procedure + "END\n"; //$NON-NLS-1$
String userUpdateStr = "UPDATE vm1.g1 SET e1='x'"; //$NON-NLS-1$
helpFailUpdateProcedure(procedure, userUpdateStr,
Table.TriggerEvent.UPDATE);
}
// using invalid function in assignment expr
@Test public void testCreateUpdateProcedure31() {
String procedure = "FOR EACH ROW "; //$NON-NLS-1$
procedure = procedure + "BEGIN\n"; //$NON-NLS-1$
procedure = procedure + "Declare integer var1;\n"; //$NON-NLS-1$
procedure = procedure + "var1 = 'x' + ROWS_UPDATED;\n"; //$NON-NLS-1$
procedure = procedure + "END\n"; //$NON-NLS-1$
String userUpdateStr = "UPDATE vm1.g1 SET e1='x'"; //$NON-NLS-1$
helpFailUpdateProcedure(procedure, userUpdateStr,
Table.TriggerEvent.UPDATE);
}
// using a variable being used inside a subcomand
@Test public void testCreateUpdateProcedure32() throws Exception {
String procedure = "FOR EACH ROW "; //$NON-NLS-1$
procedure = procedure + "BEGIN\n"; //$NON-NLS-1$
procedure = procedure + "Declare integer var1;\n"; //$NON-NLS-1$
procedure = procedure + "Select var1 from pm1.g1;\n"; //$NON-NLS-1$
procedure = procedure + "END\n"; //$NON-NLS-1$
String userUpdateStr = "UPDATE vm1.g1 SET e1='x'"; //$NON-NLS-1$
helpResolveUpdateProcedure(procedure, userUpdateStr,
Table.TriggerEvent.UPDATE);
}
// variable resolution, variables declared in different blocks local variables
// should override, outer block variables still valid afetr inner block is declared
// fails as variable being compared against incorrect type
@Test public void testCreateUpdateProcedure33() {
String procedure = "FOR EACH ROW "; //$NON-NLS-1$
procedure = procedure + "BEGIN\n"; //$NON-NLS-1$
procedure = procedure + "DECLARE integer var1;\n"; //$NON-NLS-1$
procedure = procedure + "if(var1 =1)\n"; //$NON-NLS-1$
procedure = procedure + "BEGIN\n"; //$NON-NLS-1$
procedure = procedure + "DECLARE timestamp var1;\n"; //$NON-NLS-1$
procedure = procedure + "Select pm1.g1.e2 from pm1.g1 where var1 = pm1.g1.e2;\n"; //$NON-NLS-1$
procedure = procedure + "END\n"; //$NON-NLS-1$
procedure = procedure + "var1 = var1 +1;\n"; //$NON-NLS-1$
procedure = procedure + "END\n"; //$NON-NLS-1$
String userUpdateStr = "UPDATE vm1.g1 SET e1='x'"; //$NON-NLS-1$
helpFailUpdateProcedure(procedure, userUpdateStr,
Table.TriggerEvent.UPDATE);
}
// physical elements used on criteria of the if statement
@Test public void testCreateUpdateProcedure34() {
String procedure = "FOR EACH ROW "; //$NON-NLS-1$
procedure = procedure + "BEGIN\n"; //$NON-NLS-1$
procedure = procedure + "DECLARE integer var1;\n"; //$NON-NLS-1$
procedure = procedure + "if(pm1.g1.e2 =1 and var1=1)\n"; //$NON-NLS-1$
procedure = procedure + "BEGIN\n"; //$NON-NLS-1$
procedure = procedure + "Select pm1.g1.e2 from pm1.g1;\n"; //$NON-NLS-1$
procedure = procedure + "END\n"; //$NON-NLS-1$
procedure = procedure + "END\n"; //$NON-NLS-1$
String userUpdateStr = "UPDATE vm1.g1 SET e1='x'"; //$NON-NLS-1$
helpFailUpdateProcedure(procedure, userUpdateStr,
Table.TriggerEvent.UPDATE, "TEIID31119 Symbol pm1.g1.e2 is specified with an unknown group context"); //$NON-NLS-1$
}
// physical elements used on criteria of the if statement
@Test public void testCreateUpdateProcedure36() {
String procedure = "FOR EACH ROW "; //$NON-NLS-1$
procedure = procedure + "BEGIN\n"; //$NON-NLS-1$
procedure = procedure + "DECLARE integer var1;\n"; //$NON-NLS-1$
procedure = procedure + "if(pm1.g1.e2 =1 and var1=1)\n"; //$NON-NLS-1$
procedure = procedure + "BEGIN\n"; //$NON-NLS-1$
procedure = procedure + "Select pm1.g1.e2 from pm1.g1;\n"; //$NON-NLS-1$
procedure = procedure + "END\n"; //$NON-NLS-1$
procedure = procedure + "END\n"; //$NON-NLS-1$
String userUpdateStr = "UPDATE vm1.g1 SET e1='x'"; //$NON-NLS-1$
helpFailUpdateProcedure(procedure, userUpdateStr,
Table.TriggerEvent.UPDATE);
}
// physical elements used on criteria of the if statement
@Test public void testCreateUpdateProcedure39() {
String procedure = "FOR EACH ROW "; //$NON-NLS-1$
procedure = procedure + "BEGIN\n"; //$NON-NLS-1$
procedure = procedure + "DECLARE integer var1;\n"; //$NON-NLS-1$
procedure = procedure + "if(pm1.g1.e2 =1 and var1=1)\n"; //$NON-NLS-1$
procedure = procedure + "BEGIN\n"; //$NON-NLS-1$
procedure = procedure + "Select pm1.g1.e2 from pm1.g1;\n"; //$NON-NLS-1$
procedure = procedure + "END\n"; //$NON-NLS-1$
procedure = procedure + "END\n"; //$NON-NLS-1$
String userUpdateStr = "UPDATE vm1.g1 SET e1='x'"; //$NON-NLS-1$
helpFailUpdateProcedure(procedure, userUpdateStr,
Table.TriggerEvent.UPDATE);
}
// resolving AssignmentStatement, variable type and assigned type
// do not match and no implicit conversion available
@Test public void testCreateUpdateProcedure53() {
String procedure = "FOR EACH ROW "; //$NON-NLS-1$
procedure = procedure + "BEGIN\n"; //$NON-NLS-1$
procedure = procedure + "DECLARE integer var1;\n"; //$NON-NLS-1$
procedure = procedure + "var1 = INPUTS.e4;"; //$NON-NLS-1$
procedure = procedure + "ROWS_UPDATED =0;\n"; //$NON-NLS-1$
procedure = procedure + "END\n"; //$NON-NLS-1$
String userUpdateStr = "UPDATE vm1.g1 SET e1='x'"; //$NON-NLS-1$
helpFailUpdateProcedure(procedure, userUpdateStr,
Table.TriggerEvent.UPDATE);
}
// resolving AssignmentStatement, variable type and assigned type
// do not match, but implicit conversion available
@Test public void testCreateUpdateProcedure54() throws Exception {
String procedure = "FOR EACH ROW "; //$NON-NLS-1$
procedure = procedure + "BEGIN\n"; //$NON-NLS-1$
procedure = procedure + "DECLARE string var1;\n"; //$NON-NLS-1$
procedure = procedure + "var1 = 1+1;"; //$NON-NLS-1$
procedure = procedure + "END\n"; //$NON-NLS-1$
String userUpdateStr = "UPDATE vm1.g1 SET e1='x'"; //$NON-NLS-1$
helpResolveUpdateProcedure(procedure, userUpdateStr,
Table.TriggerEvent.UPDATE);
}
@Test public void testDefect14912_CreateUpdateProcedure57_FunctionWithElementParamInAssignmentStatement() {
// Tests that the function params are resolved before the function for assignment statements
String procedure = "FOR EACH ROW "; //$NON-NLS-1$
procedure = procedure + "BEGIN\n"; //$NON-NLS-1$
procedure = procedure + "DECLARE string var1;\n"; //$NON-NLS-1$
procedure = procedure + "var1 = badFunction(badElement);"; //$NON-NLS-1$
procedure = procedure + "ROWS_UPDATED =0;\n"; //$NON-NLS-1$
procedure = procedure + "END\n"; //$NON-NLS-1$
String userCommand = "UPDATE vm1.g1 SET e1='x'"; //$NON-NLS-1$
helpFailUpdateProcedure(procedure, userCommand, Table.TriggerEvent.UPDATE, "TEIID31118 Element \"badElement\" is not defined by any relevant group."); //$NON-NLS-1$
}
// addresses Cases 4624. Before change to UpdateProcedureResolver,
// this case failed with assertion exception.
@Test public void testCase4624() {
String procedure = "FOR EACH ROW "; //$NON-NLS-1$
procedure = procedure + "BEGIN\n"; //$NON-NLS-1$
procedure = procedure + "DECLARE boolean var1;\n"; //$NON-NLS-1$
procedure = procedure + "var1 = {b'false'};\n"; //$NON-NLS-1$
procedure = procedure + "IF(var1 = {b 'true'})\n"; //$NON-NLS-1$
procedure = procedure + "BEGIN\n"; //$NON-NLS-1$
procedure = procedure + "SELECT Rack_ID, RACK_MDT_TYPE INTO #racks FROM Bert_MAP.BERT3.RACK;\n"; //$NON-NLS-1$
procedure = procedure + "END\n"; //$NON-NLS-1$
procedure = procedure + "END\n"; //$NON-NLS-1$
String userCommand = "UPDATE vm1.g1 SET e1='x'"; //$NON-NLS-1$
helpFailUpdateProcedure(procedure, userCommand, Table.TriggerEvent.UPDATE, "Group does not exist: Bert_MAP.BERT3.RACK"); //$NON-NLS-1$
}
// addresses Cases 5474.
@Test public void testCase5474() throws Exception {
String procedure = "CREATE VIRTUAL PROCEDURE "; //$NON-NLS-1$
procedure = procedure + "BEGIN\n"; //$NON-NLS-1$
procedure = procedure + "DECLARE integer VARIABLES.NLEVELS;\n"; //$NON-NLS-1$
procedure = procedure + "VARIABLES.NLEVELS = SELECT COUNT(*) FROM (SELECT oi.e1 AS Col1, oi.e2 AS Col2, oi.e3 FROM pm1.g2 AS oi) AS TOBJ, pm2.g2 AS TModel WHERE TModel.e3 = TOBJ.e3;\n"; //$NON-NLS-1$
procedure = procedure + "END\n"; //$NON-NLS-1$
TestResolver.helpResolve(procedure, RealMetadataFactory.example1Cached());
}
// addresses Cases 5474.
@Test public void testProcWithReturn() throws Exception {
String procedure = "CREATE VIRTUAL PROCEDURE "; //$NON-NLS-1$
procedure = procedure + "BEGIN\n"; //$NON-NLS-1$
procedure = procedure + "call sptest9(1);\n"; //$NON-NLS-1$
procedure = procedure + "END\n"; //$NON-NLS-1$
TestResolver.helpResolve(procedure, RealMetadataFactory.exampleBQTCached());
}
@Test public void testIssue174102() throws Exception {
String procedure = "CREATE VIRTUAL PROCEDURE \n"; //$NON-NLS-1$
procedure = procedure + "BEGIN\n"; //$NON-NLS-1$
procedure = procedure + "DECLARE string crit = 'WHERE pm1.sq2.in = \"test\"';\n"; //$NON-NLS-1$
procedure = procedure + "CREATE LOCAL TEMPORARY TABLE #TTable (e1 string);"; //$NON-NLS-1$
procedure = procedure + "EXECUTE STRING ('SELECT e1 FROM pm1.sq2 ' || crit ) AS e1 string INTO #TTable;\n"; //$NON-NLS-1$
procedure = procedure + "END\n"; //$NON-NLS-1$
TestResolver.helpResolve(procedure, RealMetadataFactory.example1Cached());
}
/*@Test public void testCommandUpdatingCountFromLastStatement() throws Exception {
String procedure = "CREATE VIRTUAL PROCEDURE \n"; //$NON-NLS-1$
procedure = procedure + "BEGIN\n"; //$NON-NLS-1$
procedure = procedure + "declare integer x = convert(pm1.sq1.in, integer) + 5;\n"; //$NON-NLS-1$
procedure = procedure + "insert into pm1.g1 values (null, null, null, null);"; //$NON-NLS-1$
procedure = procedure + "END\n"; //$NON-NLS-1$
QueryMetadataInterface metadata = exampleStoredProcedure(procedure);
Command command = helpResolve(helpParse("exec pm1.sq1(1)"), metadata, null); //$NON-NLS-1$
assertEquals(1, command.updatingModelCount(new TempMetadataAdapter(metadata, new TempMetadataStore())));
}*/
//baseline test to ensure that a declare assignment cannot contain the declared variable
@Test public void testDeclareStatement() {
String procedure = "FOR EACH ROW "; //$NON-NLS-1$
procedure = procedure + "BEGIN\n"; //$NON-NLS-1$
procedure = procedure + "DECLARE integer VARIABLES.var1 = VARIABLES.var1;\n"; //$NON-NLS-1$
procedure = procedure + "ROWS_UPDATED =0;\n"; //$NON-NLS-1$
procedure = procedure + "END\n"; //$NON-NLS-1$
String userUpdateStr = "UPDATE vm1.g1 SET e1='x'"; //$NON-NLS-1$
helpFailUpdateProcedure(procedure, userUpdateStr, Table.TriggerEvent.UPDATE);
}
@Test public void testDynamicIntoInProc() throws Exception {
String userUpdateStr = "UPDATE vm1.g1 SET e1='x'"; //$NON-NLS-1$
StringBuffer procedure = new StringBuffer("FOR EACH ROW ") //$NON-NLS-1$
.append("BEGIN\n") //$NON-NLS-1$
.append("execute string 'SELECT e1, e2, e3, e4 FROM pm1.g2' as e1 string, e2 string, e3 string, e4 string INTO #myTempTable;\n") //$NON-NLS-1$
.append("select e1 from #myTempTable;\n") //$NON-NLS-1$
.append("END\n"); //$NON-NLS-1$
helpResolveUpdateProcedure(procedure.toString(), userUpdateStr,
Table.TriggerEvent.UPDATE);
}
@Test public void testDynamicStatement() throws Exception {
String userUpdateStr = "UPDATE vm1.g1 SET e1='x'"; //$NON-NLS-1$
StringBuffer procedure = new StringBuffer("FOR EACH ROW ") //$NON-NLS-1$
.append("BEGIN\n") //$NON-NLS-1$
.append("execute string 'SELECT e1, e2, e3, e4 FROM pm1.g2';\n") //$NON-NLS-1$
.append("END\n"); //$NON-NLS-1$
helpResolveUpdateProcedure(procedure.toString(), userUpdateStr,
Table.TriggerEvent.UPDATE);
}
@Test public void testDynamicStatementType() {
String userUpdateStr = "UPDATE vm1.g1 SET e1='x'"; //$NON-NLS-1$
StringBuffer procedure = new StringBuffer("FOR EACH ROW ") //$NON-NLS-1$
.append("BEGIN\n") //$NON-NLS-1$
.append("DECLARE object VARIABLES.X = null;\n") //$NON-NLS-1$
.append("execute string VARIABLES.X;\n") //$NON-NLS-1$
.append("ROWS_UPDATED =0;\n") //$NON-NLS-1$
.append("END\n"); //$NON-NLS-1$
helpFailUpdateProcedure(procedure.toString(), userUpdateStr, Table.TriggerEvent.UPDATE);
}
// variable resolution
@Test public void testCreateUpdateProcedure1() throws Exception {
String procedure = "FOR EACH ROW "; //$NON-NLS-1$
procedure = procedure + "BEGIN\n"; //$NON-NLS-1$
procedure = procedure + "DECLARE integer var1;\n"; //$NON-NLS-1$
procedure = procedure + "var1 = Select pm1.g1.e2 from pm1.g1;\n"; //$NON-NLS-1$
procedure = procedure + "UPDATE pm1.g1 SET pm1.g1.e1 = 1, pm1.g1.e2 = var1;\n"; //$NON-NLS-1$
procedure = procedure + "END\n"; //$NON-NLS-1$
String userUpdateStr = "UPDATE vm1.g1 SET e1=1"; //$NON-NLS-1$
helpResolveUpdateProcedure(procedure, userUpdateStr,
Table.TriggerEvent.UPDATE);
}
// variable resolution, variable used in if statement
@Test public void testCreateUpdateProcedure3() throws Exception {
String procedure = "FOR EACH ROW "; //$NON-NLS-1$
procedure = procedure + "BEGIN\n"; //$NON-NLS-1$
procedure = procedure + "DECLARE integer var1;\n"; //$NON-NLS-1$
procedure = procedure + "if(var1 =1)\n"; //$NON-NLS-1$
procedure = procedure + "BEGIN\n"; //$NON-NLS-1$
procedure = procedure + "END\n"; //$NON-NLS-1$
procedure = procedure + "END\n"; //$NON-NLS-1$
String userUpdateStr = "UPDATE vm1.g1 SET e1='x'"; //$NON-NLS-1$
helpResolveUpdateProcedure(procedure, userUpdateStr,
Table.TriggerEvent.UPDATE);
}
@Test public void testSelectIntoInProc() throws Exception {
StringBuffer procedure = new StringBuffer("FOR EACH ROW ") //$NON-NLS-1$
.append("BEGIN\n") //$NON-NLS-1$
.append("SELECT e1, e2, e3, e4 INTO pm1.g1 FROM pm1.g2;\n") //$NON-NLS-1$
.append("END\n"); //$NON-NLS-1$
String userUpdateStr = "UPDATE vm1.g1 SET e1='x'"; //$NON-NLS-1$
helpResolveUpdateProcedure(procedure.toString(), userUpdateStr,
Table.TriggerEvent.UPDATE);
procedure = new StringBuffer("FOR EACH ROW ") //$NON-NLS-1$
.append("BEGIN\n") //$NON-NLS-1$
.append("SELECT e1, e2, e3, e4 INTO #myTempTable FROM pm1.g2;\n") //$NON-NLS-1$
.append("END\n"); //$NON-NLS-1$
helpResolveUpdateProcedure(procedure.toString(), userUpdateStr,
Table.TriggerEvent.UPDATE);
}
@Test public void testSelectIntoInProcNoFrom() throws Exception {
StringBuffer procedure = new StringBuffer("FOR EACH ROW ") //$NON-NLS-1$
.append("BEGIN\n") //$NON-NLS-1$
.append("SELECT 'a', 19, {b'true'}, 13.999 INTO pm1.g1;\n") //$NON-NLS-1$
.append("END\n"); //$NON-NLS-1$
String userUpdateStr = "UPDATE vm1.g1 SET e1='x'"; //$NON-NLS-1$
helpResolveUpdateProcedure(procedure.toString(), userUpdateStr,
Table.TriggerEvent.UPDATE);
procedure = new StringBuffer("FOR EACH ROW ") //$NON-NLS-1$
.append("BEGIN\n") //$NON-NLS-1$
.append("SELECT 'a', 19, {b'true'}, 13.999 INTO #myTempTable;\n") //$NON-NLS-1$
.append("END\n"); //$NON-NLS-1$
helpResolveUpdateProcedure(procedure.toString(), userUpdateStr,
Table.TriggerEvent.UPDATE);
}
// validating INPUT element assigned
@Test public void testAssignInput() {
String procedure = "FOR EACH ROW "; //$NON-NLS-1$
procedure = procedure + "BEGIN\n"; //$NON-NLS-1$
procedure = procedure + "DECLARE integer var1;\n"; //$NON-NLS-1$
procedure = procedure + "INPUTS.e1 = Select pm1.g1.e1 from pm1.g1;\n"; //$NON-NLS-1$
procedure = procedure + "ROWS_UPDATED =0;\n"; //$NON-NLS-1$
procedure = procedure + "END\n"; //$NON-NLS-1$
String userUpdateStr = "UPDATE vm1.g1 SET e1='x'"; //$NON-NLS-1$
helpFailUpdateProcedure(procedure, userUpdateStr,
Table.TriggerEvent.UPDATE);
}
// validating CHANGING element assigned
@Test public void testAssignChanging() {
String procedure = "FOR EACH ROW "; //$NON-NLS-1$
procedure = procedure + "BEGIN\n"; //$NON-NLS-1$
procedure = procedure + "DECLARE integer var1;\n"; //$NON-NLS-1$
procedure = procedure + "CHANGING.e1 = Select pm1.g1.e1 from pm1.g1;\n"; //$NON-NLS-1$
procedure = procedure + "ROWS_UPDATED =0;\n"; //$NON-NLS-1$
procedure = procedure + "END\n"; //$NON-NLS-1$
String userUpdateStr = "UPDATE vm1.g1 SET e1='x'"; //$NON-NLS-1$
helpFailUpdateProcedure(procedure, userUpdateStr,
Table.TriggerEvent.UPDATE);
}
// variables cannot be used among insert elements
@Test public void testVariableInInsert() {
String procedure = "FOR EACH ROW "; //$NON-NLS-1$
procedure = procedure + "BEGIN\n"; //$NON-NLS-1$
procedure = procedure + "DECLARE integer var1;\n"; //$NON-NLS-1$
procedure = procedure + "Insert into pm1.g1 (pm1.g1.e2, var1) values (1, 2);\n"; //$NON-NLS-1$
procedure = procedure + "ROWS_UPDATED =0;\n"; //$NON-NLS-1$
procedure = procedure + "END\n"; //$NON-NLS-1$
String userQuery = "UPDATE vm1.g3 SET x='x' where e3= 1"; //$NON-NLS-1$
helpFailUpdateProcedure(procedure, userQuery,
Table.TriggerEvent.UPDATE, "TEIID30126 Column variables do not reference columns on group \"pm1.g1\": [Unable to resolve 'var1': TEIID31118 Element \"var1\" is not defined by any relevant group.]"); //$NON-NLS-1$
}
// variables cannot be used among insert elements
@Test public void testVariableInInsert2() {
String procedure = "FOR EACH ROW "; //$NON-NLS-1$
procedure = procedure + "BEGIN\n"; //$NON-NLS-1$
procedure = procedure + "DECLARE integer var1;\n"; //$NON-NLS-1$
procedure = procedure + "Insert into pm1.g1 (pm1.g1.e2, INPUTS.x) values (1, 2);\n"; //$NON-NLS-1$
procedure = procedure + "ROWS_UPDATED =0;\n"; //$NON-NLS-1$
procedure = procedure + "END\n"; //$NON-NLS-1$
String userQuery = "UPDATE vm1.g3 SET x='x' where e3= 1"; //$NON-NLS-1$
helpFailUpdateProcedure(procedure, userQuery,
Table.TriggerEvent.UPDATE, "TEIID30126 Column variables do not reference columns on group \"pm1.g1\": [Unable to resolve 'INPUTS.x': TEIID31119 Symbol INPUTS.x is specified with an unknown group context]"); //$NON-NLS-1$
}
//should resolve first to the table's column
@Test public void testVariableInInsert3() throws Exception {
String procedure = "FOR EACH ROW "; //$NON-NLS-1$
procedure = procedure + "BEGIN\n"; //$NON-NLS-1$
procedure = procedure + "DECLARE integer e2;\n"; //$NON-NLS-1$
procedure = procedure + "Insert into pm1.g1 (e2) values (1);\n"; //$NON-NLS-1$
procedure = procedure + "END\n"; //$NON-NLS-1$
String userQuery = "UPDATE vm1.g3 SET x='x' where e3= 1"; //$NON-NLS-1$
helpResolveUpdateProcedure(procedure, userQuery,
Table.TriggerEvent.UPDATE);
}
@Test public void testAmbigousInput() {
String procedure = "FOR EACH ROW "; //$NON-NLS-1$
procedure = procedure + "BEGIN ATOMIC\n"; //$NON-NLS-1$
procedure = procedure + "select e1;\n"; //$NON-NLS-1$
procedure = procedure + "END\n"; //$NON-NLS-1$
String userUpdateStr = "UPDATE vm1.g1 SET e1='x'"; //$NON-NLS-1$
helpFailUpdateProcedure(procedure, userUpdateStr,
Table.TriggerEvent.UPDATE, "TEIID31117 Element \"e1\" is ambiguous and should be qualified, at a single scope it exists in [CHANGING, \"NEW\", \"OLD\"]"); //$NON-NLS-1$
}
@Test public void testLoopRedefinition() {
StringBuffer proc = new StringBuffer("FOR EACH ROW") //$NON-NLS-1$
.append("\nBEGIN") //$NON-NLS-1$
.append("\n declare string var1;") //$NON-NLS-1$
.append("\n LOOP ON (SELECT pm1.g1.e1 FROM pm1.g1) AS loopCursor") //$NON-NLS-1$
.append("\n BEGIN") //$NON-NLS-1$
.append("\n LOOP ON (SELECT pm1.g2.e1 FROM pm1.g2 WHERE loopCursor.e1 = pm1.g2.e1) AS loopCursor") //$NON-NLS-1$
.append("\n BEGIN") //$NON-NLS-1$
.append("\n var1 = CONCAT(var1, CONCAT(' ', loopCursor.e1));") //$NON-NLS-1$
.append("\n END") //$NON-NLS-1$
.append("\n END") //$NON-NLS-1$
.append("\n END"); //$NON-NLS-1$
String userUpdateStr = "UPDATE vm1.g1 SET e1='x'"; //$NON-NLS-1$
helpFailUpdateProcedure(proc.toString(), userUpdateStr,
Table.TriggerEvent.UPDATE, "TEIID30124 Loop cursor or exception group name loopCursor already exists."); //$NON-NLS-1$
}
@Test public void testTempGroupElementShouldNotBeResolable() {
StringBuffer proc = new StringBuffer("FOR EACH ROW") //$NON-NLS-1$
.append("\nBEGIN") //$NON-NLS-1$
.append("\n select 1 as a into #temp;") //$NON-NLS-1$
.append("\n select #temp.a from pm1.g1;") //$NON-NLS-1$
.append("\nEND"); //$NON-NLS-1$
String userUpdateStr = "UPDATE vm1.g1 SET e1='x'"; //$NON-NLS-1$
helpFailUpdateProcedure(proc.toString(), userUpdateStr,
Table.TriggerEvent.UPDATE, "TEIID31119 Symbol #temp.a is specified with an unknown group context"); //$NON-NLS-1$
}
@Test public void testTempGroupElementShouldNotBeResolable1() {
StringBuffer proc = new StringBuffer("FOR EACH ROW") //$NON-NLS-1$
.append("\nBEGIN") //$NON-NLS-1$
.append("\n select 1 as a into #temp;") //$NON-NLS-1$
.append("\n insert into #temp (a) values (#temp.a);") //$NON-NLS-1$
.append("\nEND"); //$NON-NLS-1$
String userUpdateStr = "UPDATE vm1.g1 SET e1='x'"; //$NON-NLS-1$
helpFailUpdateProcedure(proc.toString(), userUpdateStr,
Table.TriggerEvent.UPDATE, "TEIID31119 Symbol #temp.a is specified with an unknown group context"); //$NON-NLS-1$
}
@Test public void testProcedureCreate() throws Exception {
StringBuffer proc = new StringBuffer("FOR EACH ROW") //$NON-NLS-1$
.append("\nBEGIN") //$NON-NLS-1$
.append("\n create local temporary table t1 (e1 string);") //$NON-NLS-1$
.append("\n select e1 from t1;") //$NON-NLS-1$
.append("\n create local temporary table t1 (e1 string, e2 integer);") //$NON-NLS-1$
.append("\n select e2 from t1;") //$NON-NLS-1$
.append("\nEND"); //$NON-NLS-1$
String userUpdateStr = "UPDATE vm1.g1 SET e1='x'"; //$NON-NLS-1$
helpResolveUpdateProcedure(proc.toString(), userUpdateStr, Table.TriggerEvent.UPDATE);
}
/**
* it is not ok to redefine the loopCursor
*/
@Test public void testProcedureCreate1() {
StringBuffer proc = new StringBuffer("FOR EACH ROW") //$NON-NLS-1$
.append("\nBEGIN") //$NON-NLS-1$
.append("\n LOOP ON (SELECT pm1.g1.e1 FROM pm1.g1) AS loopCursor") //$NON-NLS-1$
.append("\n BEGIN") //$NON-NLS-1$
.append("\n create local temporary table loopCursor (e1 string);") //$NON-NLS-1$
.append("\nEND") //$NON-NLS-1$
.append("\nEND"); //$NON-NLS-1$
String userUpdateStr = "UPDATE vm1.g1 SET e1='x'"; //$NON-NLS-1$
helpFailUpdateProcedure(proc.toString(), userUpdateStr, Table.TriggerEvent.UPDATE, "TEIID30118 Cannot create temporary table \"loopCursor\". An object with the same name already exists."); //$NON-NLS-1$
}
@Test public void testProcedureCreateDrop() {
StringBuffer proc = new StringBuffer("FOR EACH ROW") //$NON-NLS-1$
.append("\nBEGIN") //$NON-NLS-1$
.append("\n drop table t1;") //$NON-NLS-1$
.append("\n create local temporary table t1 (e1 string);") //$NON-NLS-1$
.append("\nEND"); //$NON-NLS-1$
String userUpdateStr = "UPDATE vm1.g1 SET e1='x'"; //$NON-NLS-1$
helpFailUpdateProcedure(proc.toString(), userUpdateStr, Table.TriggerEvent.UPDATE, "Group does not exist: t1"); //$NON-NLS-1$
}
@Test public void testProcedureCreateDrop1() throws Exception {
StringBuffer proc = new StringBuffer("FOR EACH ROW") //$NON-NLS-1$
.append("\nBEGIN") //$NON-NLS-1$
.append("\n create local temporary table t1 (e1 string);") //$NON-NLS-1$
.append("\n drop table t1;") //$NON-NLS-1$
.append("\nEND"); //$NON-NLS-1$
String userUpdateStr = "UPDATE vm1.g1 SET e1='x'"; //$NON-NLS-1$
helpResolveUpdateProcedure(proc.toString(), userUpdateStr, Table.TriggerEvent.UPDATE);
}
@Test public void testCreateAfterImplicitTempTable() throws Exception {
StringBuffer proc = new StringBuffer("FOR EACH ROW") //$NON-NLS-1$
.append("\nBEGIN") //$NON-NLS-1$
.append("\n select e1 into #temp from pm1.g1;") //$NON-NLS-1$
.append("\n create local temporary table #temp (e1 string);") //$NON-NLS-1$
.append("\nEND"); //$NON-NLS-1$
String userUpdateStr = "UPDATE vm1.g1 SET e1='x'"; //$NON-NLS-1$
helpResolveUpdateProcedure(proc.toString(), userUpdateStr, Table.TriggerEvent.UPDATE);
}
@Test public void testInsertAfterCreate() throws Exception {
StringBuffer proc = new StringBuffer("FOR EACH ROW") //$NON-NLS-1$
.append("\nBEGIN") //$NON-NLS-1$
.append("\n create local temporary table #temp (e1 string, e2 string);") //$NON-NLS-1$
.append("\n insert into #temp (e1) values ('a');") //$NON-NLS-1$
.append("\nEND"); //$NON-NLS-1$
String userUpdateStr = "UPDATE vm1.g1 SET e1='x'"; //$NON-NLS-1$
helpResolveUpdateProcedure(proc.toString(), userUpdateStr, Table.TriggerEvent.UPDATE);
}
/**
* delete procedures should not reference input or changing vars.
*/
@Test public void testDefect16451() {
String procedure = "FOR EACH ROW "; //$NON-NLS-1$
procedure += "BEGIN ATOMIC\n"; //$NON-NLS-1$
procedure += "Select pm1.g1.e2 from pm1.g1 where e1 = NEW.e1;\n"; //$NON-NLS-1$
procedure += "END\n"; //$NON-NLS-1$
String userUpdateStr = "delete from vm1.g1 where e1='x'"; //$NON-NLS-1$
helpFailUpdateProcedure(procedure, userUpdateStr,
Table.TriggerEvent.DELETE, "TEIID31119 Symbol \"NEW\".e1 is specified with an unknown group context"); //$NON-NLS-1$
}
@Test public void testInvalidVirtualProcedure3() throws Exception {
helpResolveException("EXEC pm1.vsp18()", RealMetadataFactory.example1Cached(), "Group does not exist: temptable"); //$NON-NLS-1$ //$NON-NLS-2$
}
// variable resolution, variable compared against
// different datatype element for which there is no implicit transformation)
@Test public void testCreateUpdateProcedure2() {
String procedure = "FOR EACH ROW "; //$NON-NLS-1$
procedure += "BEGIN\n"; //$NON-NLS-1$
procedure += "DECLARE boolean var1;\n"; //$NON-NLS-1$
procedure += "ROWS_UPDATED = UPDATE pm1.g1 SET pm1.g1.e4 = convert(var1, string), pm1.g1.e1 = var1;\n"; //$NON-NLS-1$
procedure += "END\n"; //$NON-NLS-1$
String userUpdateStr = "UPDATE vm1.g1 SET e1=1"; //$NON-NLS-1$
helpFailUpdateProcedure(procedure, userUpdateStr,
Table.TriggerEvent.UPDATE, "TEIID30082 Cannot set symbol 'pm1.g1.e4' with expected type double to expression 'convert(var1, string)'"); //$NON-NLS-1$
}
// special variable INPUT compared against invalid type
@Test public void testInvalidInputInUpdate() {
String procedure = "FOR EACH ROW "; //$NON-NLS-1$
procedure += "BEGIN ATOMIC\n"; //$NON-NLS-1$
procedure += "DECLARE integer var1;\n"; //$NON-NLS-1$
procedure += "Select pm1.g1.e2, new.e2 from pm1.g1;\n"; //$NON-NLS-1$
procedure += "UPDATE pm1.g1 SET pm1.g1.e1 = new.e1, pm1.g1.e2 = new.e1;\n"; //$NON-NLS-1$
procedure += "END\n"; //$NON-NLS-1$
String userUpdateStr = "UPDATE vm1.g1 SET e1='x'"; //$NON-NLS-1$
helpFailUpdateProcedure(procedure, userUpdateStr,
Table.TriggerEvent.UPDATE, "TEIID30082 Cannot set symbol 'pm1.g1.e2' with expected type integer to expression '\"new\".e1'"); //$NON-NLS-1$
}
@Test public void testVirtualProcedure() throws Exception {
helpResolve("EXEC pm1.vsp1()", RealMetadataFactory.example1Cached()); //$NON-NLS-1$
}
@Test public void testVirtualProcedure2() throws Exception {
helpResolve("EXEC pm1.vsp14()", RealMetadataFactory.example1Cached()); //$NON-NLS-1$
}
@Test public void testVirtualProcedurePartialParameterReference() throws Exception {
helpResolve("EXEC pm1.vsp58(5)", RealMetadataFactory.example1Cached()); //$NON-NLS-1$
}
//cursor starts with "#" Defect14924
@Test public void testVirtualProcedureInvalid1() throws Exception {
helpResolveException("EXEC pm1.vsp32()",RealMetadataFactory.example1Cached(), "TEIID30125 Cursor or exception group names cannot begin with \"#\" as that indicates the name of a temporary table: #mycursor."); //$NON-NLS-1$ //$NON-NLS-2$
}
@Test public void testVirtualProcedureWithOrderBy() throws Exception {
helpResolve("EXEC pm1.vsp29()", RealMetadataFactory.example1Cached()); //$NON-NLS-1$
}
@Test public void testVirtualProcedureWithTempTableAndOrderBy() throws Exception {
helpResolve("EXEC pm1.vsp33()", RealMetadataFactory.example1Cached()); //$NON-NLS-1$
}
@Test public void testVirtualProcedureWithConstAndOrderBy() throws Exception {
helpResolve("EXEC pm1.vsp34()", RealMetadataFactory.example1Cached()); //$NON-NLS-1$
}
@Test public void testVirtualProcedureWithNoFromAndOrderBy() throws Exception {
helpResolve("EXEC pm1.vsp28()", RealMetadataFactory.example1Cached()); //$NON-NLS-1$
}
@Test public void testInvalidVirtualProcedure2() throws Exception {
helpResolveException("EXEC pm1.vsp12()", RealMetadataFactory.example1Cached(), "TEIID31119 Symbol mycursor.e2 is specified with an unknown group context"); //$NON-NLS-1$ //$NON-NLS-2$
}
@Test public void testLoopRedefinition2() throws Exception {
helpResolveException("EXEC pm1.vsp11()", RealMetadataFactory.example1Cached(), "TEIID30124 Loop cursor or exception group name mycursor already exists."); //$NON-NLS-1$ //$NON-NLS-2$
}
@Test public void testVariableResolutionWithIntervening() throws Exception {
StringBuffer proc = new StringBuffer("CREATE VIRTUAL PROCEDURE") //$NON-NLS-1$
.append("\nBEGIN") //$NON-NLS-1$
.append("\n declare string x;") //$NON-NLS-1$
.append("\n x = '1';") //$NON-NLS-1$
.append("\n declare string y;") //$NON-NLS-1$
.append("\n y = '1';") //$NON-NLS-1$
.append("\nEND"); //$NON-NLS-1$
TestResolver.helpResolve(proc.toString(), RealMetadataFactory.example1Cached());
}
@Test public void testVDBQualified() throws Exception {
helpResolve("EXEC example1.pm1.vsp29()", RealMetadataFactory.example1Cached()); //$NON-NLS-1$
}
@Test public void testOptionalParams() throws Exception {
String ddl = "create foreign procedure proc (x integer, y string);\n";
TransformationMetadata tm = createMetadata(ddl);
String sql = "call proc (1)"; //$NON-NLS-1$
StoredProcedure sp = (StoredProcedure) TestResolver.helpResolve(sql, tm);
assertEquals(new Constant(null, DataTypeManager.DefaultDataClasses.STRING), sp.getParameter(2).getExpression());
sql = "call proc (1, 'a')"; //$NON-NLS-1$
sp = (StoredProcedure) TestResolver.helpResolve(sql, tm);
assertEquals(new Constant("a", DataTypeManager.DefaultDataClasses.STRING), sp.getParameter(2).getExpression());
}
public static TransformationMetadata createMetadata(String ddl) throws Exception {
return RealMetadataFactory.fromDDL(ddl, "test", "test");
}
@Test public void testOptionalParams1() throws Exception {
String ddl = "create foreign procedure proc (x integer, y string NOT NULL, z integer);\n";
TransformationMetadata tm = createMetadata(ddl);
String sql = "call proc (1, 'a')"; //$NON-NLS-1$
StoredProcedure sp = (StoredProcedure) TestResolver.helpResolve(sql, tm);
assertEquals(new Constant("a", DataTypeManager.DefaultDataClasses.STRING), sp.getParameter(2).getExpression());
}
@Test public void testVarArgs() throws Exception {
String ddl = "create foreign procedure proc (x integer, VARIADIC z integer) returns (x string);\n";
TransformationMetadata tm = createMetadata(ddl);
String sql = "call proc (1, 2, 3)"; //$NON-NLS-1$
StoredProcedure sp = (StoredProcedure) TestResolver.helpResolve(sql, tm);
assertEquals("EXEC proc(1, 2, 3)", sp.toString());
assertEquals(new Constant(1), sp.getParameter(1).getExpression());
assertEquals(new Array(DataTypeManager.DefaultDataClasses.INTEGER, Arrays.asList((Expression)new Constant(2), new Constant(3))), sp.getParameter(2).getExpression());
assertEquals(SPParameter.RESULT_SET, sp.getParameter(3).getParameterType());
sql = "call proc (1)"; //$NON-NLS-1$
sp = (StoredProcedure) TestResolver.helpResolve(sql, tm);
assertEquals("EXEC proc(1)", sp.toString());
assertEquals(new Array(DataTypeManager.DefaultDataClasses.INTEGER, new ArrayList<Expression>(0)), sp.getParameter(2).getExpression());
sp = (StoredProcedure) QueryRewriter.evaluateAndRewrite(sp, new Evaluator(null, null, null), null, tm);
LanguageBridgeFactory lbf = new LanguageBridgeFactory(tm);
Call call = (Call)lbf.translate(sp);
assertEquals("EXEC proc(1)", call.toString());
sql = "call proc (1, (2, 3))"; //$NON-NLS-1$
sp = (StoredProcedure) TestResolver.helpResolve(sql, tm);
assertEquals("EXEC proc(1, (2, 3))", sp.toString());
assertEquals(new Constant(1), sp.getParameter(1).getExpression());
assertEquals(new Array(DataTypeManager.DefaultDataClasses.INTEGER, Arrays.asList((Expression)new Constant(2), new Constant(3))), sp.getParameter(2).getExpression());
assertEquals(SPParameter.RESULT_SET, sp.getParameter(3).getParameterType());
}
@Test public void testVarArgs1() throws Exception {
String ddl = "create foreign procedure proc (VARIADIC z integer) returns (x string);\n";
TransformationMetadata tm = createMetadata(ddl);
String sql = "call proc ()"; //$NON-NLS-1$
StoredProcedure sp = (StoredProcedure) TestResolver.helpResolve(sql, tm);
assertEquals("EXEC proc()", sp.toString());
assertEquals(new Array(DataTypeManager.DefaultDataClasses.INTEGER, new ArrayList<Expression>(0)), sp.getParameter(1).getExpression());
sp = (StoredProcedure) QueryRewriter.evaluateAndRewrite(sp, new Evaluator(null, null, null), null, tm);
LanguageBridgeFactory lbf = new LanguageBridgeFactory(tm);
Call call = (Call)lbf.translate(sp);
assertEquals("EXEC proc()", call.toString());
//we pass to the translator level flattened, so no argument
assertEquals(0, call.getArguments().size());
}
@Test public void testVarArgs2() throws Exception {
String ddl = "create foreign procedure proc (VARIADIC z object) returns (x string);\n";
TransformationMetadata tm = createMetadata(ddl);
String sql = "call proc ()"; //$NON-NLS-1$
StoredProcedure sp = (StoredProcedure) TestResolver.helpResolve(sql, tm);
assertEquals("EXEC proc()", sp.toString());
assertEquals(new Array(DataTypeManager.DefaultDataClasses.OBJECT, new ArrayList<Expression>(0)), sp.getParameter(1).getExpression());
sql = "call proc (1, (2, 3))"; //$NON-NLS-1$
sp = (StoredProcedure) TestResolver.helpResolve(sql, tm);
assertEquals("EXEC proc(1, (2, 3))", sp.toString());
ArrayList<Expression> expressions = new ArrayList<Expression>();
expressions.add(new Constant(1));
expressions.add(new Array(DataTypeManager.DefaultDataClasses.INTEGER, Arrays.asList((Expression)new Constant(2), new Constant(3))));
assertEquals(new Array(DataTypeManager.DefaultDataClasses.OBJECT, expressions), sp.getParameter(1).getExpression());
}
@Test public void testAnonBlock() throws Exception {
String sql = "begin select 1 as something; end"; //$NON-NLS-1$
CreateProcedureCommand sp = (CreateProcedureCommand) TestResolver.helpResolve(sql, RealMetadataFactory.example1Cached());
assertEquals(1, sp.getResultSetColumns().size());
assertEquals("something", Symbol.getName(sp.getResultSetColumns().get(0)));
assertEquals(1, sp.getProjectedSymbols().size());
assertTrue(sp.returnsResultSet());
}
@Test public void testAnonBlockNoResult() throws Exception {
String sql = "begin select 1 as something without return; end"; //$NON-NLS-1$
CreateProcedureCommand sp = (CreateProcedureCommand) TestResolver.helpResolve(sql, RealMetadataFactory.example1Cached());
assertEquals(0, sp.getProjectedSymbols().size());
assertFalse(sp.returnsResultSet());
}
@Test public void testReturnAndResultSet() throws Exception {
String ddl = "CREATE FOREIGN PROCEDURE proc (OUT param STRING RESULT) RETURNS TABLE (a INTEGER, b STRING);"; //$NON-NLS-1$
TransformationMetadata tm = RealMetadataFactory.fromDDL(ddl, "x", "y");
StoredProcedure sp = (StoredProcedure) TestResolver.helpResolve("exec proc()", tm);
assertEquals(2, sp.getProjectedSymbols().size());
assertEquals("y.proc.b", sp.getProjectedSymbols().get(1).toString());
assertTrue(sp.returnsResultSet());
sp.setCallableStatement(true);
assertEquals(3, sp.getProjectedSymbols().size());
assertEquals("y.proc.param", sp.getProjectedSymbols().get(2).toString());
CreateProcedureCommand cpc = (CreateProcedureCommand) TestResolver.helpResolve("begin exec proc(); end", tm);
assertEquals(2, cpc.getProjectedSymbols().size());
assertEquals(2, ((CommandStatement)cpc.getBlock().getStatements().get(0)).getCommand().getProjectedSymbols().size());
assertTrue(cpc.returnsResultSet());
TestValidator.helpValidate("begin declare string var; var = exec proc(); select var; end", new String[] {"SELECT var;"}, tm);
}
}