Example #1
0
  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());
  }
Example #2
0
  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)));
  }
Example #3
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)));
  }
Example #4
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)));
  }
Example #5
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));
  }
Example #6
0
  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());
  }
Example #7
0
  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());
  }
Example #8
0
  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());
  }