public void test02_SelectFrom() throws Exception {
KettleEnvironment.init();
String sqlQuery =
"SELECT Category, Country, products_sold as nr, sales_amount as sales FROM Service";
SqlTransExecutor executor = new SqlTransExecutor(sqlQuery, getServices());
final List<RowMetaAndData> rows = new ArrayList<RowMetaAndData>();
// print the eventual result rows...
//
executor.executeQuery(
new RowAdapter() {
@Override
public void rowWrittenEvent(RowMetaInterface rowMeta, Object[] row)
throws KettleStepException {
rows.add(new RowMetaAndData(rowMeta, row));
}
});
// Now the generated transformation is waiting for input so we
// can start the service transformation
//
executor.waitUntilFinished();
assertEquals(8, rows.size());
}
public void test06_SelectFromGroupByOrderBy() throws Exception {
KettleEnvironment.init();
String sqlQuery =
"SELECT Country, SUM(products_sold) as count, SUM(sales_amount) as sales FROM Service GROUP BY Country ORDER BY SUM(sales_amount) DESC";
SqlTransExecutor executor = new SqlTransExecutor(sqlQuery, getServices());
final List<RowMetaAndData> rows = new ArrayList<RowMetaAndData>();
// collect the eventual result rows...
//
executor.executeQuery(
new RowAdapter() {
@Override
public void rowWrittenEvent(RowMetaInterface rowMeta, Object[] row)
throws KettleStepException {
rows.add(new RowMetaAndData(rowMeta, row));
}
});
// Now the generated transformation is waiting for input so we
// can start the service transformation
//
executor.waitUntilFinished();
assertEquals(4, rows.size());
// Validate results...
//
int rowNr = 0;
assertEquals("Germany", rows.get(rowNr).getString("Country", null));
assertEquals(76, rows.get(rowNr).getInteger("count", -1));
assertEquals(12697, Math.round(rows.get(rowNr).getNumber("sales", -1.0)));
rowNr++;
assertEquals("Great Britain", rows.get(rowNr).getString("Country", null));
assertEquals(18, rows.get(rowNr).getInteger("count", -1));
assertEquals(11657, Math.round(rows.get(rowNr).getNumber("sales", -1.0)));
rowNr++;
assertEquals("France", rows.get(rowNr).getString("Country", null));
assertEquals(24, rows.get(rowNr).getInteger("count", -1));
assertEquals(2021, Math.round(rows.get(rowNr).getNumber("sales", -1.0)));
rowNr++;
assertEquals("Belgium", rows.get(rowNr).getString("Country", null));
assertEquals(10, rows.get(rowNr).getInteger("count", -1));
assertEquals(780, Math.round(rows.get(rowNr).getNumber("sales", -1.0)));
}
public void test10_SelectFromGroupSumAlias() throws Exception {
KettleEnvironment.init();
String sqlQuery =
"SELECT Category, SUM(sales_amount) as \"Sales\"\nFROM Service\nGROUP BY Category\nORDER BY SUM(sales_amount) DESC";
SqlTransExecutor executor = new SqlTransExecutor(sqlQuery, getServices());
final List<RowMetaAndData> rows = new ArrayList<RowMetaAndData>();
// collect the eventual result rows...
//
executor.executeQuery(
new RowAdapter() {
@Override
public void rowWrittenEvent(RowMetaInterface rowMeta, Object[] row)
throws KettleStepException {
rows.add(new RowMetaAndData(rowMeta, row));
}
});
// Save to temp file for checking
//
File file = new File("/tmp/gen.ktr");
FileOutputStream fos = new FileOutputStream(file);
fos.write(org.pentaho.di.core.xml.XMLHandler.getXMLHeader().getBytes("UTF-8"));
fos.write(executor.getGenTransMeta().getXML().getBytes("UTF-8"));
fos.close();
// Now the generated transformation is waiting for input so we
// can start the service transformation
//
executor.waitUntilFinished();
assertEquals(2, rows.size());
// Validate results...
//
int rowNr = 0;
assertEquals("A", rows.get(rowNr).getString("Category", null));
assertEquals(19013, Math.round(rows.get(rowNr).getNumber("Sales", -1.0)));
rowNr++;
assertEquals("B", rows.get(rowNr).getString("Category", null));
assertEquals(8142, Math.round(rows.get(rowNr).getNumber("Sales", -1.0)));
}
public void test09_SelectFromGroupMaxHaving() throws Exception {
KettleEnvironment.init();
String sqlQuery =
"SELECT Country, MAX(products_sold) max_count FROM Service GROUP BY Country HAVING MAX(products_sold) > 10 ORDER BY max_count DESC";
SqlTransExecutor executor = new SqlTransExecutor(sqlQuery, getServices());
final List<RowMetaAndData> rows = new ArrayList<RowMetaAndData>();
// collect the eventual result rows...
//
executor.executeQuery(
new RowAdapter() {
@Override
public void rowWrittenEvent(RowMetaInterface rowMeta, Object[] row)
throws KettleStepException {
rows.add(new RowMetaAndData(rowMeta, row));
}
});
// Save to temp file for checking
//
File file = new File("/tmp/gen.ktr");
FileOutputStream fos = new FileOutputStream(file);
fos.write(org.pentaho.di.core.xml.XMLHandler.getXMLHeader().getBytes("UTF-8"));
fos.write(executor.getGenTransMeta().getXML().getBytes("UTF-8"));
fos.close();
// Now the generated transformation is waiting for input so we
// can start the service transformation
//
executor.waitUntilFinished();
assertEquals(2, rows.size());
// Validate results...
//
int rowNr = 0;
assertEquals("Germany", rows.get(rowNr).getString("Country", null));
assertEquals(38, Math.round(rows.get(rowNr).getNumber("max_count", -1.0)));
rowNr++;
assertEquals("France", rows.get(rowNr).getString("Country", null));
assertEquals(12, Math.round(rows.get(rowNr).getNumber("max_count", -1.0)));
}
public void test07_SelectFromGroupCountDistinct() throws Exception {
KettleEnvironment.init();
String sqlQuery =
"SELECT Category, COUNT(DISTINCT Country) as \"Number of countries\" FROM Service GROUP BY Category ORDER BY COUNT(DISTINCT Country) DESC";
SqlTransExecutor executor = new SqlTransExecutor(sqlQuery, getServices());
final List<RowMetaAndData> rows = new ArrayList<RowMetaAndData>();
// collect the eventual result rows...
//
executor.executeQuery(
new RowAdapter() {
@Override
public void rowWrittenEvent(RowMetaInterface rowMeta, Object[] row)
throws KettleStepException {
rows.add(new RowMetaAndData(rowMeta, row));
}
});
// Now the generated transformation is waiting for input so we
// can start the service transformation
//
executor.waitUntilFinished();
assertEquals(2, rows.size());
// Validate results...
//
int rowNr = 0;
assertEquals("A", rows.get(rowNr).getString("Category", null));
assertEquals(4, rows.get(rowNr).getInteger("Number of countries", -1));
rowNr++;
assertEquals("B", rows.get(rowNr).getString("Category", null));
assertEquals(4, rows.get(rowNr).getInteger("Number of countries", -1));
}
public void test03_SelectFromWhere() throws Exception {
KettleEnvironment.init();
String sqlQuery =
"SELECT Category, Country, products_sold as nr, sales_amount as sales FROM Service WHERE Category = 'A'";
SqlTransExecutor executor = new SqlTransExecutor(sqlQuery, getServices());
final List<RowMetaAndData> rows = new ArrayList<RowMetaAndData>();
// print the eventual result rows...
//
executor.executeQuery(
new RowAdapter() {
@Override
public void rowWrittenEvent(RowMetaInterface rowMeta, Object[] row)
throws KettleStepException {
rows.add(new RowMetaAndData(rowMeta, row));
}
});
// Now the generated transformation is waiting for input so we
// can start the service transformation
//
executor.waitUntilFinished();
// Save to temp file for checking
//
File file = new File("/tmp/gen.ktr");
FileOutputStream fos = new FileOutputStream(file);
fos.write(org.pentaho.di.core.xml.XMLHandler.getXMLHeader().getBytes("UTF-8"));
fos.write(executor.getGenTransMeta().getXML().getBytes("UTF-8"));
fos.close();
assertEquals(4, rows.size());
}
public void test13_DistinctOrderFromPMD() throws Exception {
KettleEnvironment.init();
String sqlQuery =
"SELECT DISTINCT BT_SERVICE_SERVICE.Category AS COL0 ,BT_SERVICE_SERVICE.Country AS COL1 FROM Service BT_SERVICE_SERVICE ORDER BY COL0";
SqlTransExecutor executor = new SqlTransExecutor(sqlQuery, getServices());
final List<RowMetaAndData> rows = new ArrayList<RowMetaAndData>();
// collect the eventual result rows...
//
executor.executeQuery(
new RowAdapter() {
@Override
public void rowWrittenEvent(RowMetaInterface rowMeta, Object[] row)
throws KettleStepException {
rows.add(new RowMetaAndData(rowMeta, row));
}
});
// Save to temp file for checking
//
File file = new File("/tmp/gen.ktr");
FileOutputStream fos = new FileOutputStream(file);
fos.write(org.pentaho.di.core.xml.XMLHandler.getXMLHeader().getBytes("UTF-8"));
fos.write(executor.getGenTransMeta().getXML().getBytes("UTF-8"));
fos.close();
// Now the generated transformation is waiting for input so we
// can start the service transformation
//
executor.waitUntilFinished();
assertEquals(8, rows.size());
}
public void test11_NotSelectedHaving() throws Exception {
KettleEnvironment.init();
String sqlQuery =
"select \"Service\".\"Category\" as \"c0\", \"Service\".\"Country\" as \"c1\" from \"Service\" as \"Service\" where (\"Service\".\"Category\" = 'A') group by \"Service\".\"Category\", \"Service\".\"Country\" having (NOT((sum(\"Service\".\"sales_amount\") is null)) OR NOT((sum(\"Service\".\"products_sold\") is null)) ) order by CASE WHEN \"Service\".\"Category\" IS NULL THEN 1 ELSE 0 END, \"Service\".\"Category\" ASC, CASE WHEN \"Service\".\"Country\" IS NULL THEN 1 ELSE 0 END";
SqlTransExecutor executor = new SqlTransExecutor(sqlQuery, getServices());
final List<RowMetaAndData> rows = new ArrayList<RowMetaAndData>();
// collect the eventual result rows...
//
executor.executeQuery(
new RowAdapter() {
@Override
public void rowWrittenEvent(RowMetaInterface rowMeta, Object[] row)
throws KettleStepException {
rows.add(new RowMetaAndData(rowMeta, row));
}
});
// Save to temp file for checking
//
File file = new File("/tmp/gen.ktr");
FileOutputStream fos = new FileOutputStream(file);
fos.write(org.pentaho.di.core.xml.XMLHandler.getXMLHeader().getBytes("UTF-8"));
fos.write(executor.getGenTransMeta().getXML().getBytes("UTF-8"));
fos.close();
// Now the generated transformation is waiting for input so we
// can start the service transformation
//
executor.waitUntilFinished();
assertEquals(4, rows.size());
}