Examples with SQL org.pentaho.di.core.sql.SQL used on opensource projects

Example 51 with SQL

use of org.pentaho.di.core.sql.SQL in project pdi-dataservice-server-plugin by pentaho.

the class SqlTransGeneratorTest method testGenTransUsingSqlWithAggAndAliases.

@Test
public void testGenTransUsingSqlWithAggAndAliases() throws KettleException {
    SQL sql = new SQL("SELECT \"FACT\".\"mth\" AS \"COL0\",AVG(\"FACT\".\"bmi\") AS \"COL1\"," + "\"FACT\".\"gender\" AS \"COL2\" FROM \"FACT\" GROUP BY  \"FACT\".\"mth\",\"FACT\".\"gender\"");
    RowMetaInterface rowMeta = new RowMeta();
    rowMeta.addValueMeta(new ValueMetaString("mth"));
    rowMeta.addValueMeta(new ValueMetaInteger("bmi"));
    rowMeta.addValueMeta(new ValueMetaString("gender"));
    sql.parse(rowMeta);
    SqlTransGenerator generator = new SqlTransGenerator(sql, 0);
    SelectValuesMeta selectValuesMeta = getSelectStepValuesMeta(generator.generateTransMeta());
    assertThat(selectValuesMeta.getSelectName(), equalTo(new String[] { "mth", "COL1", "gender" }));
    assertThat(selectValuesMeta.getSelectRename(), equalTo(new String[] { "COL0", null, "COL2" }));
}

Example 52 with SQL

use of org.pentaho.di.core.sql.SQL in project pdi-dataservice-server-plugin by pentaho.

the class SqlTransGeneratorTest method testServiceLimit.

@Test
public void testServiceLimit() throws KettleException {
    SQL sql = new SQL("SELECT * FROM table");
    RowMetaInterface rowMeta = new RowMeta();
    rowMeta.addValueMeta(new ValueMetaString("foo"));
    sql.parse(rowMeta);
    final String genLimitStep = "Limit input rows";
    SqlTransGenerator generator = new SqlTransGenerator(sql, 0, 2);
    TransMeta transMeta = generator.generateTransMeta();
    SampleRowsMeta limitInput = (SampleRowsMeta) getStepByName(transMeta, genLimitStep);
    assertEquals("limit not generated", "1..2", limitInput.getLinesRange());
    // bad value
    generator = new SqlTransGenerator(sql, 0, -3);
    transMeta = generator.generateTransMeta();
    assertTrue("limit<=0 not ignored", Arrays.asList(transMeta.getStepNames()).indexOf(genLimitStep) < 0);
}

Example 53 with SQL

use of org.pentaho.di.core.sql.SQL in project pdi-dataservice-server-plugin by pentaho.

the class SqlTransGeneratorTest method testGenTransAllAggsWithAliases.

@Test
public void testGenTransAllAggsWithAliases() throws KettleException {
    SQL sql = new SQL("SELECT sum(foo) as c1, avg(bar) as c2, max(baz) as c3 FROM table");
    RowMetaInterface rowMeta = new RowMeta();
    rowMeta.addValueMeta(new ValueMetaString("foo"));
    rowMeta.addValueMeta(new ValueMetaInteger("bar"));
    rowMeta.addValueMeta(new ValueMetaString("baz"));
    sql.parse(rowMeta);
    SqlTransGenerator generator = new SqlTransGenerator(sql, 0);
    SelectValuesMeta selectValuesMeta = getSelectStepValuesMeta(generator.generateTransMeta());
    assertThat(selectValuesMeta.getSelectName(), equalTo(new String[] { "c1", "c2", "c3" }));
    assertThat(selectValuesMeta.getSelectRename(), equalTo(new String[] { null, null, null }));
}

Example 54 with SQL

use of org.pentaho.di.core.sql.SQL in project pdi-dataservice-server-plugin by pentaho.

the class SqlTransGeneratorTest method testRowLimit.

@Test
public void testRowLimit() throws KettleException {
    SQL sql = new SQL("SELECT * FROM table");
    RowMetaInterface rowMeta = new RowMeta();
    rowMeta.addValueMeta(new ValueMetaString("foo"));
    sql.parse(rowMeta);
    SqlTransGenerator generator = new SqlTransGenerator(sql, 1);
    SelectValuesMeta selectValuesMeta = getSelectStepValuesMeta(generator.generateTransMeta());
    assertThat(selectValuesMeta.getSelectName(), equalTo(new String[] { "foo" }));
    assertThat(selectValuesMeta.getSelectRename(), equalTo(new String[] { null }));
}

Example 55 with SQL

use of org.pentaho.di.core.sql.SQL in project pdi-dataservice-server-plugin by pentaho.

the class ExecutorQueryServiceTest method testQueryBuildsWithMetastore.

@Test
public void testQueryBuildsWithMetastore() throws Exception {
    final DataServiceFactory factory = mock(DataServiceFactory.class);
    final DataServiceContext context = mock(DataServiceContext.class);
    final DataServiceResolver dataServiceResolver = mock(DataServiceResolver.class);
    final DataServiceExecutor dataServiceExecutor = mock(DataServiceExecutor.class);
    final Trans serviceTrans = mock(Trans.class);
    final Trans genTrans = mock(Trans.class);
    when(context.getMetaStoreUtil()).thenReturn(factory);
    DataServiceExecutor.Builder builder = mock(DataServiceExecutor.Builder.class);
    final IMetaStore metastore = mock(IMetaStore.class);
    final MetastoreLocator metastoreLocator = mock(MetastoreLocator.class);
    when(metastoreLocator.getMetastore()).thenReturn(metastore);
    SQL sql = new SQL("select field from table");
    HashMap<String, String> parameters = new HashMap<>();
    int rowLimit = 5432;
    ExecutorQueryService executorQueryService = new ExecutorQueryService(dataServiceResolver, metastoreLocator);
    when(dataServiceResolver.createBuilder(argThat(matchesSql(sql)))).thenReturn(builder);
    when(factory.getMetaStore()).thenReturn(metastore);
    when(builder.rowLimit(rowLimit)).thenReturn(builder);
    when(builder.parameters(parameters)).thenReturn(builder);
    when(builder.metastore(metastore)).thenReturn(builder);
    when(builder.windowMode(null)).thenReturn(builder);
    when(builder.windowSize(0)).thenReturn(builder);
    when(builder.windowEvery(0)).thenReturn(builder);
    when(builder.windowLimit(0)).thenReturn(builder);
    when(builder.build()).thenReturn(dataServiceExecutor);
    when(dataServiceExecutor.getServiceTrans()).thenReturn(serviceTrans);
    when(dataServiceExecutor.getGenTrans()).thenReturn(genTrans);
    Query result = executorQueryService.prepareQuery(sql.getSqlString(), rowLimit, parameters);
    assertEquals(ImmutableList.of(serviceTrans, genTrans), result.getTransList());
    verify(builder).rowLimit(rowLimit);
    verify(builder).parameters(parameters);
    verify(builder).metastore(metastore);
}