@Test
public void testResultSetScriptValidationCombination() {
String sql = "select ORDERTYPES, STATUS from orders where ID=5";
reset(jdbcTemplate);
Map<String, Object> resultMap = new HashMap<String, Object>();
resultMap.put("ORDERTYPE", "small");
resultMap.put("STATUS", "in_progress");
expect(jdbcTemplate.queryForList(sql)).andReturn(Collections.singletonList(resultMap));
replay(jdbcTemplate);
List<String> stmts = Collections.singletonList(sql);
executeSQLQueryAction.setStatements(stmts);
Map<String, List<String>> controlResultSet = new HashMap<String, List<String>>();
controlResultSet.put("ORDERTYPE", Collections.singletonList("small"));
controlResultSet.put("STATUS", Collections.singletonList("in_progress"));
executeSQLQueryAction.setControlResultSet(controlResultSet);
ScriptValidationContext scriptValidationContext =
new ScriptValidationContext(
"assert rows.size() == 1\n"
+ "assert rows[0].ORDERTYPE == 'small'\n"
+ "assert rows[0] == [ORDERTYPE:'small', STATUS:'in_progress']",
ScriptTypes.GROOVY);
executeSQLQueryAction.setScriptValidationContext(scriptValidationContext);
executeSQLQueryAction.execute(context);
}
@Test
public void testResultSetValidationError() {
String sql = "select ORDERTYPE, STATUS from orders where ID=5";
reset(jdbcTemplate);
Map<String, Object> resultMap = new HashMap<String, Object>();
resultMap.put("ORDERTYPE", "small");
resultMap.put("STATUS", "in_progress");
expect(jdbcTemplate.queryForList(sql)).andReturn(Collections.singletonList(resultMap));
replay(jdbcTemplate);
List<String> stmts = Collections.singletonList(sql);
executeSQLQueryAction.setStatements(stmts);
Map<String, List<String>> controlResultSet = new HashMap<String, List<String>>();
controlResultSet.put(
"ORDERTYPE", Collections.singletonList("xxl")); // this is supposed to cause an error
controlResultSet.put("STATUS", Collections.singletonList("in_progress"));
executeSQLQueryAction.setControlResultSet(controlResultSet);
try {
executeSQLQueryAction.execute(context);
} catch (ValidationException e) {
Assert.assertNull(context.getVariables().get("${ORDERTYPE}"));
Assert.assertNull(context.getVariables().get("${STATUS}"));
return;
}
Assert.fail("Expected test to fail with " + ValidationException.class + " but was successful");
}
@Test
public void testResultSetScriptValidationWrongValue() {
String sql = "select ORDERTYPES, STATUS from orders where ID=5";
reset(jdbcTemplate);
Map<String, Object> resultMap = new HashMap<String, Object>();
resultMap.put("ORDERTYPE", "small");
resultMap.put("STATUS", "in_progress");
expect(jdbcTemplate.queryForList(sql)).andReturn(Collections.singletonList(resultMap));
replay(jdbcTemplate);
List<String> stmts = Collections.singletonList(sql);
executeSQLQueryAction.setStatements(stmts);
ScriptValidationContext scriptValidationContext =
new ScriptValidationContext(
"assert rows.size() == 1\n"
+ "assert rows[0] == [ORDERTYPE:'big', STATUS:'in_progress']",
ScriptTypes.GROOVY);
executeSQLQueryAction.setScriptValidationContext(scriptValidationContext);
try {
executeSQLQueryAction.execute(context);
} catch (ValidationException e) {
Assert.assertTrue(e.getCause() instanceof AssertionError);
return;
}
Assert.fail("Missing validation exception due to script validation error");
}
@Test
public void testSQLStatementsWithFileResource() {
String sql1 = "select ORDERTYPE, STATUS from orders where ID=5;";
String sql2 = "select NAME, HEIGHT\nfrom customers\nwhere ID=1;";
reset(jdbcTemplate);
Map<String, Object> resultMap1 = new HashMap<String, Object>();
resultMap1.put("ORDERTYPE", "small");
resultMap1.put("STATUS", "in_progress");
expect(jdbcTemplate.queryForList(sql1)).andReturn(Collections.singletonList(resultMap1));
Map<String, Object> resultMap2 = new HashMap<String, Object>();
resultMap2.put("NAME", "Mickey Mouse");
resultMap2.put("HEIGHT", "0,3");
expect(jdbcTemplate.queryForList(sql2)).andReturn(Collections.singletonList(resultMap2));
replay(jdbcTemplate);
executeSQLQueryAction.setSqlResource(
new ClassPathResource("test-sql-query-statements.sql", ExecuteSQLQueryActionTest.class));
executeSQLQueryAction.execute(context);
Assert.assertNotNull(context.getVariable("${ORDERTYPE}"));
Assert.assertEquals(context.getVariable("${ORDERTYPE}"), "small");
Assert.assertNotNull(context.getVariable("${STATUS}"));
Assert.assertEquals(context.getVariable("${STATUS}"), "in_progress");
Assert.assertNotNull(context.getVariable("${NAME}"));
Assert.assertEquals(context.getVariable("${NAME}"), "Mickey Mouse");
Assert.assertNotNull(context.getVariable("${HEIGHT}"));
Assert.assertEquals(context.getVariable("${HEIGHT}"), "0,3");
}
@Test
public void testResultSetValidationWithAliasNames() {
String sql = "select ORDERTYPE AS TYPE, STATUS AS STATE from orders where ID=5";
reset(jdbcTemplate);
Map<String, Object> resultMap = new HashMap<String, Object>();
resultMap.put("TYPE", "small");
resultMap.put("STATE", "in_progress");
expect(jdbcTemplate.queryForList(sql)).andReturn(Collections.singletonList(resultMap));
replay(jdbcTemplate);
List<String> stmts = Collections.singletonList(sql);
executeSQLQueryAction.setStatements(stmts);
Map<String, List<String>> controlResultSet = new HashMap<String, List<String>>();
controlResultSet.put("TYPE", Collections.singletonList("small"));
controlResultSet.put("STATE", Collections.singletonList("in_progress"));
executeSQLQueryAction.setControlResultSet(controlResultSet);
executeSQLQueryAction.execute(context);
Assert.assertNotNull(context.getVariable("${TYPE}"));
Assert.assertEquals(context.getVariable("${TYPE}"), "small");
Assert.assertNotNull(context.getVariable("${STATE}"));
Assert.assertEquals(context.getVariable("${STATE}"), "in_progress");
}
@Test
public void testExtractToVariables() {
String sql = "select ORDERTYPE, STATUS from orders where ID=5";
reset(jdbcTemplate);
Map<String, Object> resultMap = new HashMap<String, Object>();
resultMap.put("ORDERTYPE", "small");
resultMap.put("STATUS", "in_progress");
expect(jdbcTemplate.queryForList(sql)).andReturn(Collections.singletonList(resultMap));
replay(jdbcTemplate);
List<String> stmts = Collections.singletonList(sql);
executeSQLQueryAction.setStatements(stmts);
Map<String, String> extractVariables = new HashMap<String, String>();
extractVariables.put("STATUS", "orderStatus");
executeSQLQueryAction.setExtractVariables(extractVariables);
executeSQLQueryAction.execute(context);
Assert.assertNotNull(context.getVariable("${orderStatus}"));
Assert.assertEquals(context.getVariable("${orderStatus}"), "in_progress");
Assert.assertNotNull(context.getVariable("${ORDERTYPE}"));
Assert.assertEquals(context.getVariable("${ORDERTYPE}"), "small");
Assert.assertNotNull(context.getVariable("${STATUS}"));
Assert.assertEquals(context.getVariable("${STATUS}"), "in_progress");
}
@Test
public void testSQLResource() {
Resource sqlResource = new ClassPathResource("test-query.sql", ExecuteSQLQueryActionTest.class);
String sql1 = "SELECT ORDERTYPE, STATUS FROM orders WHERE ID=5;";
String sql2 = "SELECT NAME, HEIGHT FROM customers WHERE ID=1;";
reset(jdbcTemplate);
Map<String, Object> resultMap1 = new HashMap<String, Object>();
resultMap1.put("ORDERTYPE", "small");
resultMap1.put("STATUS", "in_progress");
expect(jdbcTemplate.queryForList(sql1)).andReturn(Collections.singletonList(resultMap1));
Map<String, Object> resultMap2 = new HashMap<String, Object>();
resultMap2.put("NAME", "Mickey Mouse");
resultMap2.put("HEIGHT", "0,3");
expect(jdbcTemplate.queryForList(sql2)).andReturn(Collections.singletonList(resultMap2));
replay(jdbcTemplate);
executeSQLQueryAction.setSqlResource(sqlResource);
executeSQLQueryAction.execute(context);
Assert.assertNotNull(context.getVariable("${ORDERTYPE}"));
Assert.assertEquals(context.getVariable("${ORDERTYPE}"), "small");
Assert.assertNotNull(context.getVariable("${STATUS}"));
Assert.assertEquals(context.getVariable("${STATUS}"), "in_progress");
Assert.assertNotNull(context.getVariable("${NAME}"));
Assert.assertEquals(context.getVariable("${NAME}"), "Mickey Mouse");
Assert.assertNotNull(context.getVariable("${HEIGHT}"));
Assert.assertEquals(context.getVariable("${HEIGHT}"), "0,3");
}
@Test
public void testExtractMultipleRowValues() {
String sql = "select distinct STATUS from orders";
reset(jdbcTemplate);
List<Map<String, Object>> resultList = new ArrayList<Map<String, Object>>();
Map<String, Object> resultRow1 = new HashMap<String, Object>();
Map<String, Object> resultRow2 = new HashMap<String, Object>();
Map<String, Object> resultRow3 = new HashMap<String, Object>();
resultRow1.put("ORDERTYPE", "small");
resultRow1.put("STATUS", "started");
resultList.add(resultRow1);
resultRow2.put("ORDERTYPE", null);
resultRow2.put("STATUS", "in_progress");
resultList.add(resultRow2);
resultRow3.put("ORDERTYPE", "big");
resultRow3.put("STATUS", "finished");
resultList.add(resultRow3);
expect(jdbcTemplate.queryForList(sql)).andReturn(resultList);
replay(jdbcTemplate);
List<String> stmts = Collections.singletonList(sql);
executeSQLQueryAction.setStatements(stmts);
Map<String, String> extractVariables = new HashMap<String, String>();
extractVariables.put("STATUS", "orderStatus");
extractVariables.put("ORDERTYPE", "orderType");
executeSQLQueryAction.setExtractVariables(extractVariables);
Map<String, List<String>> controlResultSet = new HashMap<String, List<String>>();
List<String> ordertypeValues = new ArrayList<String>();
ordertypeValues.add("small");
ordertypeValues.add(CitrusConstants.IGNORE_PLACEHOLDER);
ordertypeValues.add("big");
controlResultSet.put("ORDERTYPE", ordertypeValues);
List<String> statusValues = new ArrayList<String>();
statusValues.add("started");
statusValues.add("in_progress");
statusValues.add("finished");
controlResultSet.put("STATUS", statusValues);
executeSQLQueryAction.setControlResultSet(controlResultSet);
executeSQLQueryAction.execute(context);
Assert.assertNotNull(context.getVariable("orderType"));
Assert.assertEquals(context.getVariable("orderType"), "small;NULL;big");
Assert.assertNotNull(context.getVariable("orderStatus"));
Assert.assertEquals(context.getVariable("orderStatus"), "started;in_progress;finished");
Assert.assertNotNull(context.getVariable("ORDERTYPE"));
Assert.assertEquals(context.getVariable("ORDERTYPE"), "small");
Assert.assertNotNull(context.getVariable("STATUS"));
Assert.assertEquals(context.getVariable("STATUS"), "started");
}
@Test
public void testNullValuesInMultipleRowsValidation() {
String sql = "select ORDERTYPE, STATUS from orders where ID < 5";
reset(jdbcTemplate);
List<Map<String, Object>> resultList = new ArrayList<Map<String, Object>>();
Map<String, Object> resultRow1 = new HashMap<String, Object>();
Map<String, Object> resultRow2 = new HashMap<String, Object>();
Map<String, Object> resultRow3 = new HashMap<String, Object>();
resultRow1.put("ORDERTYPE", "small");
resultRow1.put("STATUS", null);
resultList.add(resultRow1);
resultRow2.put("ORDERTYPE", "medium");
resultRow2.put("STATUS", "in_progress");
resultList.add(resultRow2);
resultRow3.put("ORDERTYPE", null);
resultRow3.put("STATUS", "finished");
resultList.add(resultRow3);
expect(jdbcTemplate.queryForList(sql)).andReturn(resultList);
replay(jdbcTemplate);
List<String> stmts = Collections.singletonList(sql);
executeSQLQueryAction.setStatements(stmts);
Map<String, List<String>> controlResultSet = new HashMap<String, List<String>>();
List<String> ordertypeValues = new ArrayList<String>();
ordertypeValues.add("small");
ordertypeValues.add("medium");
ordertypeValues.add(""); // 1st possibility to validate null values
controlResultSet.put("ORDERTYPE", ordertypeValues);
List<String> statusValues = new ArrayList<String>();
statusValues.add("NULL"); // 2nd possibility to validate null values
statusValues.add("in_progress");
statusValues.add("finished");
controlResultSet.put("STATUS", statusValues);
executeSQLQueryAction.setControlResultSet(controlResultSet);
executeSQLQueryAction.execute(context);
Assert.assertNotNull(context.getVariable("ORDERTYPE"));
Assert.assertEquals(context.getVariable("ORDERTYPE"), "small");
Assert.assertNotNull(context.getVariable("STATUS"));
Assert.assertEquals(context.getVariable("STATUS"), "NULL");
}
@Test
public void testMultipleStatementsValidationError() {
String sql1 = "select ORDERTYPE, STATUS from orders where ID=5";
String sql2 = "select NAME, HEIGHT from customers where ID=1";
reset(jdbcTemplate);
Map<String, Object> resultMap1 = new HashMap<String, Object>();
resultMap1.put("ORDERTYPE", "small");
resultMap1.put("STATUS", "in_progress");
expect(jdbcTemplate.queryForList(sql1)).andReturn(Collections.singletonList(resultMap1));
Map<String, Object> resultMap2 = new HashMap<String, Object>();
resultMap2.put("NAME", "Mickey Mouse");
resultMap2.put("HEIGHT", "0,3");
expect(jdbcTemplate.queryForList(sql2)).andReturn(Collections.singletonList(resultMap2));
replay(jdbcTemplate);
List<String> stmts = new ArrayList<String>();
stmts.add(sql1);
stmts.add(sql2);
executeSQLQueryAction.setStatements(stmts);
Map<String, List<String>> controlResultSet = new HashMap<String, List<String>>();
controlResultSet.put("ORDERTYPE", Collections.singletonList("small"));
controlResultSet.put("STATUS", Collections.singletonList("in_progress"));
controlResultSet.put(
"NAME", Collections.singletonList("Donald Duck")); // this is supposed to cause an error
controlResultSet.put("HEIGHT", Collections.singletonList("0,3"));
executeSQLQueryAction.setControlResultSet(controlResultSet);
try {
executeSQLQueryAction.execute(context);
} catch (ValidationException e) {
Assert.assertNull(context.getVariables().get("${ORDERTYPE}"));
Assert.assertNull(context.getVariables().get("${STATUS}"));
Assert.assertNull(context.getVariables().get("${NAME}"));
Assert.assertNull(context.getVariables().get("${HEIGHT}"));
return;
}
Assert.fail("Expected test to fail with " + ValidationException.class + " but was successful");
}
@Test
public void testResultSetScriptValidationMultipleStmts() {
String sql1 = "select ORDERTYPES, STATUS from orders where ID=5";
String sql2 = "select ERRORTYPES from types";
reset(jdbcTemplate);
Map<String, Object> resultMap = new HashMap<String, Object>();
resultMap.put("ORDERTYPE", "small");
resultMap.put("STATUS", "in_progress");
List<Map<String, Object>> results = new ArrayList<Map<String, Object>>();
for (int i = 1; i < 4; i++) {
Map<String, Object> columnMap = new HashMap<String, Object>();
columnMap.put("ID", String.valueOf(i));
columnMap.put("NAME", "error" + i);
results.add(columnMap);
}
expect(jdbcTemplate.queryForList(sql1)).andReturn(Collections.singletonList(resultMap)).once();
expect(jdbcTemplate.queryForList(sql2)).andReturn(results).once();
replay(jdbcTemplate);
List<String> stmts = new ArrayList<String>();
stmts.add(sql1);
stmts.add(sql2);
executeSQLQueryAction.setStatements(stmts);
ScriptValidationContext scriptValidationContext =
new ScriptValidationContext(
"assert rows.size() == 4\n"
+ "assert rows[0].ORDERTYPE == 'small'\n"
+ "assert rows[0] == [ORDERTYPE:'small', STATUS:'in_progress']\n"
+ "assert rows[1].ID == '1'\n"
+ "assert rows[3].NAME == 'error3'\n",
ScriptTypes.GROOVY);
executeSQLQueryAction.setScriptValidationContext(scriptValidationContext);
executeSQLQueryAction.execute(context);
}
@Test(expectedExceptions = {CitrusRuntimeException.class})
public void testExtractToVariablesUnknownColumnMapping() {
String sql = "select ORDERTYPE, STATUS from orders where ID=5";
reset(jdbcTemplate);
Map<String, Object> resultMap = new HashMap<String, Object>();
resultMap.put("ORDERTYPE", "small");
resultMap.put("STATUS", "in_progress");
expect(jdbcTemplate.queryForList(sql)).andReturn(Collections.singletonList(resultMap));
replay(jdbcTemplate);
List<String> stmts = Collections.singletonList(sql);
executeSQLQueryAction.setStatements(stmts);
Map<String, String> extractVariables = new HashMap<String, String>();
extractVariables.put("UNKNOWN_COLUMN", "orderStatus");
executeSQLQueryAction.setExtractVariables(extractVariables);
executeSQLQueryAction.execute(context);
}
@Test
public void testSQLMultipleStatements() {
String sql1 = "select ORDERTYPE, STATUS from orders where ID=5";
String sql2 = "select NAME, HEIGHT from customers where ID=1";
reset(jdbcTemplate);
Map<String, Object> resultMap1 = new HashMap<String, Object>();
resultMap1.put("ORDERTYPE", "small");
resultMap1.put("STATUS", "in_progress");
expect(jdbcTemplate.queryForList(sql1)).andReturn(Collections.singletonList(resultMap1));
Map<String, Object> resultMap2 = new HashMap<String, Object>();
resultMap2.put("NAME", "Mickey Mouse");
resultMap2.put("HEIGHT", "0,3");
expect(jdbcTemplate.queryForList(sql2)).andReturn(Collections.singletonList(resultMap2));
replay(jdbcTemplate);
List<String> stmts = new ArrayList<String>();
stmts.add(sql1);
stmts.add(sql2);
executeSQLQueryAction.setStatements(stmts);
executeSQLQueryAction.execute(context);
Assert.assertNotNull(context.getVariable("${ORDERTYPE}"));
Assert.assertEquals(context.getVariable("${ORDERTYPE}"), "small");
Assert.assertNotNull(context.getVariable("${STATUS}"));
Assert.assertEquals(context.getVariable("${STATUS}"), "in_progress");
Assert.assertNotNull(context.getVariable("${NAME}"));
Assert.assertEquals(context.getVariable("${NAME}"), "Mickey Mouse");
Assert.assertNotNull(context.getVariable("${HEIGHT}"));
Assert.assertEquals(context.getVariable("${HEIGHT}"), "0,3");
}