Examples with CommandBuilder org.teiid.cdk.CommandBuilder used on opensource projects

Example 16 with CommandBuilder

use of org.teiid.cdk.CommandBuilder in project teiid by teiid.

the class TestOracleTranslator method testRowLimitWithUnionOrderBy.

@Test
public void testRowLimitWithUnionOrderBy() throws Exception {
    // $NON-NLS-1$
    String input = "(select intkey from bqt1.smalla limit 50, 100) union select intnum from bqt1.smalla order by intkey";
    // $NON-NLS-1$
    String output = "(SELECT c_0 FROM (SELECT VIEW_FOR_LIMIT.*, ROWNUM ROWNUM_ FROM (SELECT g_1.IntKey AS c_0 FROM SmallA g_1) VIEW_FOR_LIMIT WHERE ROWNUM <= 150) WHERE ROWNUM_ > 50) UNION SELECT g_0.IntNum AS c_0 FROM SmallA g_0 ORDER BY c_0";
    CommandBuilder commandBuilder = new CommandBuilder(RealMetadataFactory.exampleBQTCached());
    Command obj = commandBuilder.getCommand(input, true, true);
    this.helpTestVisitor(obj, EMPTY_CONTEXT, null, output);
}

Example 17 with CommandBuilder

use of org.teiid.cdk.CommandBuilder in project teiid by teiid.

the class TestOracleTranslator method testDependentJoin.

@Test
public void testDependentJoin() throws Exception {
    CommandBuilder commandBuilder = new CommandBuilder(getOracleSpecificMetadata());
    Select command = (Select) commandBuilder.getCommand("select id from smalla where description = 'a'");
    Parameter param = new Parameter();
    param.setType(TypeFacility.RUNTIME_TYPES.STRING);
    param.setDependentValueId("x");
    param.setValueIndex(0);
    Map<String, List<? extends List<?>>> dependentValues = new HashMap<String, List<? extends List<?>>>();
    dependentValues.put("x", Arrays.asList(Arrays.asList("a"), Arrays.asList("b")));
    command.setDependentValues(dependentValues);
    ((Comparison) command.getWhere()).setRightExpression(param);
    Connection connection = Mockito.mock(Connection.class);
    Statement statement = Mockito.mock(Statement.class);
    Mockito.stub(connection.createStatement()).toReturn(statement);
    PreparedStatement ps = Mockito.mock(PreparedStatement.class);
    Mockito.stub(ps.executeBatch()).toReturn(new int[] { -2, -2 });
    // $NON-NLS-1$
    Mockito.stub(connection.prepareStatement("INSERT INTO TEIID_DKJ1 (COL1) VALUES (?)")).toReturn(ps);
    // we won't bother to retrieve the results, but we expect the following join query
    PreparedStatement ps1 = Mockito.mock(PreparedStatement.class);
    // $NON-NLS-1$
    Mockito.stub(connection.prepareStatement("SELECT SmallishA.ID FROM TEIID_DKJ1, SmallishA WHERE SmallishA.description = TEIID_DKJ1.COL1")).toReturn(ps1);
    OracleExecutionFactory ef = new OracleExecutionFactory() {

        public String getTemporaryTableName(String prefix) {
            // don't use random for testing
            return prefix;
        }
    };
    ef.setDatabaseVersion(Version.DEFAULT_VERSION);
    ef.start();
    JDBCQueryExecution e = new JDBCQueryExecution(command, connection, new FakeExecutionContextImpl(), ef);
    e.execute();
    Mockito.verify(statement, Mockito.times(1)).execute("DECLARE PRAGMA AUTONOMOUS_TRANSACTION; BEGIN EXECUTE IMMEDIATE 'create global temporary table TEIID_DKJ1 (COL1 varchar2(100 char)) on commit delete rows; END;");
    Mockito.verify(ps, Mockito.times(1)).setObject(1, "a", Types.VARCHAR);
    Mockito.verify(ps, Mockito.times(1)).setObject(1, "b", Types.VARCHAR);
    Mockito.verify(ps, Mockito.times(2)).addBatch();
    Mockito.verify(ps, Mockito.times(1)).executeBatch();
}

Example 18 with CommandBuilder

use of org.teiid.cdk.CommandBuilder in project teiid by teiid.

the class TestSqlServerConversionVisitor method testRecursiveCTEWithTypeMatching.

@Test
public void testRecursiveCTEWithTypeMatching() throws Exception {
    String input = "with a (intkey, stringkey, bigintegervalue) as (select intkey, NULL as stringkey, bigintegervalue from bqt1.smalla where intkey = 1 " + "union all " + " select n.intkey, n.stringkey, 1 from bqt1.smalla n inner join a rcte on n.intkey = rcte.intkey + 1) " + "select * from a";
    String output = "WITH a (intkey, stringkey, bigintegervalue) AS (SELECT cast(g_2.IntKey AS int) AS c_0, cast(NULL AS char) AS c_1, cast(g_2.BigIntegerValue AS numeric(38, 0)) AS c_2 FROM SmallA g_2 WHERE g_2.IntKey = 1 UNION ALL SELECT cast(g_0.IntKey AS int) AS c_0, g_0.StringKey AS c_1, cast(1 AS numeric(38, 0)) AS c_2 FROM SmallA g_0 INNER JOIN a g_1 ON g_0.IntKey = (g_1.intkey + 1)) SELECT g_3.intkey, g_3.stringkey, g_3.bigintegervalue FROM a g_3";
    CommandBuilder commandBuilder = new CommandBuilder(RealMetadataFactory.exampleBQTCached());
    Command obj = commandBuilder.getCommand(input, true, true);
    TranslationHelper.helpTestVisitor(output, trans, obj);
}

Example 19 with CommandBuilder

use of org.teiid.cdk.CommandBuilder in project teiid by teiid.

the class TestSqlServerConversionVisitor method testParseFormat.

@Test
public void testParseFormat() throws Exception {
    // $NON-NLS-1$
    String input = "select parsetimestamp(smalla.timestampvalue, 'yyyy.MM.dd'), formattimestamp(smalla.timestampvalue, 'yy.MM.dd') from bqt1.smalla";
    // $NON-NLS-1$
    String output = "SELECT CONVERT(DATETIME, convert(varchar, g_0.TimestampValue, 21), 102), CONVERT(VARCHAR, g_0.TimestampValue, 2) FROM SmallA g_0";
    CommandBuilder commandBuilder = new CommandBuilder(RealMetadataFactory.exampleBQTCached());
    Command obj = commandBuilder.getCommand(input, true, true);
    TranslationHelper.helpTestVisitor(output, trans, obj);
}

Example 20 with CommandBuilder

use of org.teiid.cdk.CommandBuilder in project teiid by teiid.

the class TestSqlServerConversionVisitor method testBooleanExpression.

/**
 * the first column expression needs converted to a single clause case
 * the second column condition in the case should not be altered
 * the third column nested boolean expression needs converted accounting for 3 level logic
 */
@Test
public void testBooleanExpression() throws Exception {
    // $NON-NLS-1$
    String input = "select stringkey is null, case when stringkey = 'b' then 1 end, coalesce(intkey = 1, true) from bqt1.smalla";
    // $NON-NLS-1$
    String output = "SELECT CASE WHEN g_0.StringKey IS NULL THEN 1 ELSE 0 END, CASE WHEN g_0.StringKey = 'b' THEN 1 END, isnull(CASE WHEN g_0.IntKey = 1 THEN 1 WHEN NOT (g_0.IntKey = 1) THEN 0 END, 1) FROM SmallA g_0";
    CommandBuilder commandBuilder = new CommandBuilder(RealMetadataFactory.exampleBQTCached());
    Command obj = commandBuilder.getCommand(input, true, true);
    TranslationHelper.helpTestVisitor(output, trans, obj);
}