Examples with Command org.teiid.query.sql.lang.Command used on opensource projects

Example 76 with Command

use of org.teiid.query.sql.lang.Command in project teiid by teiid.

the class TestInsertProcessing method helpInsertIntoWithSubquery2.

public void helpInsertIntoWithSubquery2(boolean doBatching, boolean doBulkInsert) {
    FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
    BasicSourceCapabilities caps = TestOptimizer.getTypicalCapabilities();
    caps.setCapabilitySupport(Capability.BATCHED_UPDATES, doBatching);
    caps.setCapabilitySupport(Capability.BULK_UPDATE, doBulkInsert);
    caps.setCapabilitySupport(Capability.INSERT_WITH_ITERATOR, doBulkInsert);
    caps.setCapabilitySupport(Capability.QUERY_FROM_GROUP_ALIAS, false);
    // $NON-NLS-1$
    capFinder.addCapabilities("pm1", caps);
    QueryMetadataInterface metadata = RealMetadataFactory.example1Cached();
    HardcodedDataManager dataManager = new HardcodedDataManager();
    // $NON-NLS-1$
    dataManager.addData(// $NON-NLS-1$
    "SELECT pm1.g1.e1, pm1.g1.e2, pm1.g1.e3, pm1.g1.e4 FROM pm1.g1", new List[] { // $NON-NLS-1$
    Arrays.asList(new Object[] { "1", new Integer(1), Boolean.FALSE, new Double(1) }), // $NON-NLS-1$
    Arrays.asList(new Object[] { "2", new Integer(2), Boolean.TRUE, new Double(2) }) });
    if (doBulkInsert) {
        // $NON-NLS-1$
        dataManager.addData(// $NON-NLS-1$
        "INSERT INTO pm1.g2 (e1, e2, e3, e4) VALUES (...)", new List[] { Arrays.asList(1), Arrays.asList(1), Arrays.asList(1), Arrays.asList(1) });
    } else if (doBatching) {
        // $NON-NLS-1$
        dataManager.addData(// $NON-NLS-1$
        "BatchedUpdate{I,I}", new List[] { Arrays.asList(1), Arrays.asList(1) });
    } else {
        // $NON-NLS-1$
        dataManager.addData(// $NON-NLS-1$
        "INSERT INTO pm1.g2 (e1, e2, e3, e4) VALUES ('1', 1, FALSE, 1.0)", new List[] { Arrays.asList(new Object[] { new Integer(1) }) });
        // $NON-NLS-1$
        dataManager.addData(// $NON-NLS-1$
        "INSERT INTO pm1.g2 (e1, e2, e3, e4) VALUES ('2', 2, TRUE, 2.0)", new List[] { Arrays.asList(new Object[] { new Integer(1) }) });
    }
    // $NON-NLS-1$
    String sql = "INSERT INTO pm1.g2 SELECT pm1.g1.e1, pm1.g1.e2, pm1.g1.e3, pm1.g1.e4 from pm1.g1 UNION ALL SELECT pm1.g1.e1, pm1.g1.e2, pm1.g1.e3, pm1.g1.e4 from pm1.g1";
    // String sql = "SELECT pm1.g1.e1, pm1.g1.e2, pm1.g1.e3, pm1.g1.e4 INTO pm1.g2 from pm1.g1"; //$NON-NLS-1$
    Command command = helpParse(sql);
    ProcessorPlan plan = helpGetPlan(command, metadata, capFinder);
    List<?>[] expected = new List[] { Arrays.asList(new Object[] { new Integer(4) }) };
    helpProcess(plan, dataManager, expected);
    // check the command hist to ensure it contains the expected commands
    if (!doBulkInsert && doBatching) {
        BatchedUpdateCommand bu = (BatchedUpdateCommand) dataManager.getCommandHistory().get(2);
        assertEquals(2, bu.getUpdateCommands().size());
        // $NON-NLS-1$
        assertEquals("INSERT INTO pm1.g2 (e1, e2, e3, e4) VALUES ('1', 1, FALSE, 1.0)", bu.getUpdateCommands().get(0).toString());
        // $NON-NLS-1$
        assertEquals("INSERT INTO pm1.g2 (e1, e2, e3, e4) VALUES ('2', 2, TRUE, 2.0)", bu.getUpdateCommands().get(1).toString());
    }
}

Example 77 with Command

use of org.teiid.query.sql.lang.Command in project teiid by teiid.

the class TestInsertProcessing method testInsertTriggerWithTypeChanges.

@Test
public void testInsertTriggerWithTypeChanges() throws Exception {
    TransformationMetadata tm = RealMetadataFactory.fromDDL("CREATE FOREIGN TABLE SmallA (IntKey integer PRIMARY KEY,         StringKey string,         IntNum integer,         StringNum string,         FloatNum float,         LongNum bigint,         DoubleNum double,         ByteNum smallint,         DateValue date,         TimeValue time,         TimestampValue timestamp,         BooleanValue boolean,         CharValue char(1),         ShortValue smallint,         BigIntegerValue decimal,         BigDecimalValue decimal,         ObjectValue blob)     OPTIONS (UPDATABLE 'TRUE'); " + " CREATE VIEW SmallAV (IntKey integer PRIMARY KEY,     StringKey string,     IntNum integer,     StringNum string,     FloatNum float,     LongNum long,     DoubleNum double,     ByteNum byte,     DateValue date,     TimeValue time,     TimestampValue timestamp,     BooleanValue boolean,     CharValue char,     ShortValue short,     BigIntegerValue biginteger,     BigDecimalValue bigdecimal,     ObjectValue object) OPTIONS (UPDATABLE 'TRUE') AS SELECT IntKey, StringKey, IntNum,     StringNum, FloatNum, LongNum, DoubleNum,     convert(ByteNum, byte) AS ByteNum, DateValue, TimeValue, TimestampValue,     BooleanValue, CharValue, ShortValue,     convert(BigIntegerValue, biginteger) AS BigIntegerValue, BigDecimalValue,     convert(ObjectValue, object) AS ObjectValue FROM SmallA; " + " CREATE TRIGGER ON SmallAV INSTEAD OF INSERT AS FOR EACH ROW BEGIN ATOMIC     INSERT INTO smalla (IntKey, StringKey, IntNum, StringNum, FloatNum, LongNum, DoubleNum, ByteNum, DateValue, TimeValue, TimestampValue, BooleanValue, CharValue, ShortValue, BigIntegerValue, BigDecimalValue, ObjectValue) VALUES         (NEW.IntKey, NEW.StringKey, NEW.IntNum, NEW.StringNum, NEW.FloatNum, NEW.LongNum, NEW.DoubleNum, NEW.ByteNum, NEW.DateValue, NEW.TimeValue, NEW.TimestampValue,         NEW.BooleanValue, NEW.CharValue, NEW.ShortValue, NEW.BigIntegerValue, NEW.BigDecimalValue, to_bytes(convert(NEW.ObjectValue, string), 'UTF-8')); END;", "x", "y");
    // $NON-NLS-1$
    String sql = "INSERT INTO smallav (IntKey, IntNum) VALUES (1, null), (2, 2)";
    List<?>[] expected = new List[] { Arrays.asList(1) };
    final List[] secondResult = new List[] { Arrays.asList(2, null, 2, null, null, null, null, null, null, null, null, null, null, null, null, null, null) };
    HardcodedDataManager dataManager = new HardcodedDataManager() {

        @Override
        public TupleSource registerRequest(CommandContext context, Command command, String modelName, RegisterRequestParameter parameterObject) throws TeiidComponentException {
            TupleSource ts = ((Insert) command).getTupleSource();
            try {
                List<?> tuple = ts.nextTuple();
                assertEquals(Arrays.asList(1, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null), tuple);
                tuple = ts.nextTuple();
                assertEquals(secondResult[0], tuple);
                assertNull(ts.nextTuple());
            } catch (TeiidProcessingException e) {
                throw new TeiidRuntimeException(e);
            }
            return super.registerRequest(context, command, modelName, parameterObject);
        }
    };
    dataManager.addData("INSERT INTO smalla (IntKey, StringKey, IntNum, StringNum, FloatNum, LongNum, DoubleNum, ByteNum, DateValue, TimeValue, TimestampValue, BooleanValue, CharValue, ShortValue, BigIntegerValue, BigDecimalValue, ObjectValue) VALUES (...)", Arrays.asList(2));
    BasicSourceCapabilities bsc = new BasicSourceCapabilities();
    bsc.setCapabilitySupport(Capability.INSERT_WITH_ITERATOR, true);
    ProcessorPlan plan = helpGetPlan(sql, tm, new DefaultCapabilitiesFinder(bsc));
    helpProcess(plan, dataManager, expected);
    sql = "INSERT INTO smallav (IntKey, CharValue) VALUES (1, null), (2, convert('+', char))";
    plan = helpGetPlan(sql, tm, new DefaultCapabilitiesFinder(bsc));
    secondResult[0] = Arrays.asList(2, null, null, null, null, null, null, null, null, null, null, null, '+', null, null, null, null);
    helpProcess(plan, dataManager, expected);
}

Example 78 with Command

use of org.teiid.query.sql.lang.Command in project teiid by teiid.

the class TestInsertProcessing method testInsertQueryExpression.

@Test
public void testInsertQueryExpression() throws Exception {
    // $NON-NLS-1$
    String sql = "insert into pm1.g1 select * from pm1.g2";
    BasicSourceCapabilities caps = TestOptimizer.getTypicalCapabilities();
    caps.setCapabilitySupport(Capability.INSERT_WITH_QUERYEXPRESSION, true);
    DefaultCapabilitiesFinder capFinder = new DefaultCapabilitiesFinder(caps);
    QueryMetadataInterface metadata = RealMetadataFactory.example1Cached();
    Command command = helpParse(sql);
    ProcessorPlan plan = helpGetPlan(command, metadata, capFinder);
    HardcodedDataManager dataManager = new HardcodedDataManager(metadata);
    List<?>[] expected = new List<?>[] { Arrays.asList(1) };
    dataManager.addData("INSERT INTO g1 (e1, e2, e3, e4) SELECT g_0.e1, g_0.e2, g_0.e3, g_0.e4 FROM g2 AS g_0", expected);
    helpProcess(plan, dataManager, expected);
}

Example 79 with Command

use of org.teiid.query.sql.lang.Command in project teiid by teiid.

the class TestJoinWithFunction method testDeterministicPrePostJoin.

/**
 * <p>Test the use of a deterministic function on the sub-command of a user
 * command and the user command itself, which performs a JOIN of two sources.</p>
 *
 * <p>This test combines the PostJoin and PreJoin test cases.</p>
 * @throws TeiidComponentException
 * @throws QueryMetadataException
 * @see #testDeterministicPostJoin
 * @see #testDeterministicPreJoin
 */
public void testDeterministicPrePostJoin() throws Exception {
    // sub-query for one side of a JOIN
    String leftQuery = // $NON-NLS-1$
    "SELECT pm1.g1.e1 as ID, pm1.g1.e2, pm1.g1.e3, pm1.g1.e4, SQRT(100) AS SqrtLeft " + // $NON-NLS-1$
    "FROM pm1.g1";
    // sub-query for other side of a JOIN
    String rightQuery = // $NON-NLS-1$
    "SELECT pm2.g2.e1 as ID, pm2.g2.e2, pm2.g2.e3, pm2.g2.e4 " + // $NON-NLS-1$
    "FROM pm2.g2";
    // User Command
    /*
		 * Return everything from the JOIN. SqrtTop is the use of SQRT(100) on
		 * the user command while SqrtLeft is the use of SQRT() within a
		 * source node.
		 */
    String sql = // $NON-NLS-1$
    "SELECT l.ID, l.e2, l.e3, l.e4, r.ID, r.e2, r.e3, r.e4, l.SqrtLeft, SQRT(100) AS SqrtTop " + "FROM (" + leftQuery + // $NON-NLS-1$ //$NON-NLS-2$
    ") AS l, " + "(" + rightQuery + // $NON-NLS-1$ //$NON-NLS-2$
    ") AS r " + // $NON-NLS-1$
    "WHERE l.ID = r.ID";
    /*
		 * Populate a List with our expected results. 
		 */
    List<?>[] expected = new List[] { Arrays.asList(new Object[] { // $NON-NLS-1$
    "a", // $NON-NLS-1$
    new Integer(0), // $NON-NLS-1$
    new Boolean(false), // $NON-NLS-1$
    new Double(2), // $NON-NLS-1$
    "a", // $NON-NLS-1$
    new Integer(1), new Boolean(true), new Double(2.0), new Double(10.0), new Double(10.0) }), Arrays.asList(new Object[] { // $NON-NLS-1$
    "b", // $NON-NLS-1$
    new Integer(1), // $NON-NLS-1$
    new Boolean(true), // $NON-NLS-1$
    null, // $NON-NLS-1$
    "b", // $NON-NLS-1$
    new Integer(0), new Boolean(false), new Double(0.0), new Double(10.0), new Double(10.0) }), Arrays.asList(new Object[] { // $NON-NLS-1$
    "b", // $NON-NLS-1$
    new Integer(1), // $NON-NLS-1$
    new Boolean(true), // $NON-NLS-1$
    null, // $NON-NLS-1$
    "b", // $NON-NLS-1$
    new Integer(5), new Boolean(true), new Double(2.0), new Double(10.0), new Double(10.0) }), Arrays.asList(new Object[] { // $NON-NLS-1$
    "b", // $NON-NLS-1$
    new Integer(1), // $NON-NLS-1$
    new Boolean(true), // $NON-NLS-1$
    null, // $NON-NLS-1$
    "b", // $NON-NLS-1$
    new Integer(2), new Boolean(false), null, new Double(10.0), new Double(10.0) }) };
    // Construct data manager with data
    FakeDataManager dataManager = new FakeDataManager();
    FakeDataStore.sampleData2(dataManager);
    // Plan query
    FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
    BasicSourceCapabilities caps = new BasicSourceCapabilities();
    // $NON-NLS-1$
    capFinder.addCapabilities("pm1", caps);
    // $NON-NLS-1$
    capFinder.addCapabilities("pm2", caps);
    Command command = TestProcessor.helpParse(sql);
    ProcessorPlan plan = TestProcessor.helpGetPlan(command, RealMetadataFactory.example1Cached(), capFinder);
    // Run query
    TestProcessor.helpProcess(plan, dataManager, expected);
}

Example 80 with Command

use of org.teiid.query.sql.lang.Command in project teiid by teiid.

the class TestProcedureRelational method testIssue119.

/*
     * The following are tests that were removed from the validator.  We are no longer trying to validate a priori whether 
     * procedure input criteria is valid.  This can be addressed later more generally when we do up front validation of
     * access patterns and access patterns have a wider range of semantics.
     * 
    @Test public void testProcInVirtualGroupDefect14609_1() throws Exception{
        helpValidate("select ve3 from vm1.vgvp1 where ve1=1.1 and ve2='a'", new String[] {"ve1 = 1.1"}, RealMetadataFactory.example1Cached()); //$NON-NLS-1$ //$NON-NLS-2$
    }
   
    @Test public void testProcInVirtualGroupDefect14609_2() throws Exception{
        helpValidate("select ve3 from vm1.vgvp1 where convert(ve1, integer)=1 and ve2='a'", new String[] {"convert(ve1, integer) = 1" }, RealMetadataFactory.example1Cached()); //$NON-NLS-1$ //$NON-NLS-2$
    }
    
    @Test public void testProcInVirtualGroupDefect14609_3() throws Exception{
        helpValidate("select ve3 from vm1.vgvp1 where 1.1=ve1 and ve2='a'", new String[] {"1.1 = ve1" }, RealMetadataFactory.example1Cached()); //$NON-NLS-1$ //$NON-NLS-2$
    }
    
    @Test public void testProcInVirtualGroupDefect14609_4() throws Exception{
        helpValidate("select ve3 from vm1.vgvp1 where 1=convert(ve1, integer) and ve2='a'", new String[] {"1 = convert(ve1, integer)" }, RealMetadataFactory.example1Cached()); //$NON-NLS-1$ //$NON-NLS-2$
    }    

    @Test public void testDefect15861() throws Exception{
        helpValidate("select ve3 from vm1.vgvp1 where (ve1=1 or ve1=2) and ve2='a'", new String[] {"(ve1 = 1) OR (ve1 = 2)", "ve1 = 2"}, RealMetadataFactory.example1Cached()); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
    }

    @Test public void testProcInVirtualGroup1_Defect20164() {
        helpFailProcedure("select ve3 from vm1.vgvp2 where (ve1=1 and ve2='a') or ve3='c'", RealMetadataFactory.example1Cached()); //$NON-NLS-1$
    }

    @Test public void testProcInVirtualGroup2_Defect20164() {
        helpFailProcedure("select ve3 from vm1.vgvp2 where ve1=1 or ve2='a'", RealMetadataFactory.example1Cached()); //$NON-NLS-1$
    }

    @Test public void testProcInVirtualGroup3_Defect20164() {
        helpFailProcedure("select ve3 from vm1.vgvp2, pm1.g1 where ve1=pm1.g1.e2 and ve2='a'", RealMetadataFactory.example1Cached()); //$NON-NLS-1$
    }

    @Test public void testProcInVirtualGroup4_Defect20164() {
        helpValidate("select ve3 from vm1.vgvp2 where (ve1=1 and ve2='a') and (ve3='a' OR ve3='c')", new String[0], RealMetadataFactory.example1Cached()); //$NON-NLS-1$
    }

    @Test public void testProcInVirtualGroup5_Defect20164() {
        helpFailProcedure("select ve3 from vm1.vgvp2 where ve1=1 and NOT(ve2='a')", RealMetadataFactory.example1Cached()); //$NON-NLS-1$
    }

    @Test public void testProcInVirtualGroup6_Defect20164() {
        helpValidate("select ve3 from vm1.vgvp2 where ve1=1 and ve2 is null", new String[0], RealMetadataFactory.example1Cached()); //$NON-NLS-1$
    }

    @Test public void testProcInVirtualGroup7_Defect20164() {
        helpFailProcedure("select ve3 from vm1.vgvp2 where ve1=1 and ve2 is not null", RealMetadataFactory.example1Cached()); //$NON-NLS-1$
    }*/
/**
 * Ensures that dependent procedures are processed 1 at a time so that projected input values
 * are set correctly.
 */
@Test
public void testIssue119() throws Exception {
    MetadataStore metadataStore = new MetadataStore();
    // $NON-NLS-1$
    Schema v1 = RealMetadataFactory.createVirtualModel("v1", metadataStore);
    // $NON-NLS-1$
    Schema pm1 = RealMetadataFactory.createPhysicalModel("pm1", metadataStore);
    // $NON-NLS-1$
    ProcedureParameter in = RealMetadataFactory.createParameter("in1", SPParameter.IN, DataTypeManager.DefaultDataTypes.INTEGER);
    // $NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$ //$NON-NLS-6$
    ColumnSet<Procedure> rs1 = RealMetadataFactory.createResultSet("v1.vp1.rs1", new String[] { "e1", "e2", "e3", "e4", "e5" }, new String[] { DataTypeManager.DefaultDataTypes.INTEGER, DataTypeManager.DefaultDataTypes.INTEGER, DataTypeManager.DefaultDataTypes.INTEGER, DataTypeManager.DefaultDataTypes.INTEGER, DataTypeManager.DefaultDataTypes.INTEGER });
    // $NON-NLS-1$
    QueryNode n1 = new QueryNode("CREATE VIRTUAL PROCEDURE BEGIN SELECT vp1.in1 e1, x.in1 e2, x.e1 e3, y.in1 e4, y.e1 e5 FROM pm1.sp119 x, pm1.sp119 y where x.in1 = vp1.in1 and y.in1 = x.e1; END");
    // $NON-NLS-1$
    Procedure vt1 = RealMetadataFactory.createVirtualProcedure("vp1", v1, Arrays.asList(in), n1);
    vt1.setResultSet(rs1);
    // $NON-NLS-1$
    ProcedureParameter in1 = RealMetadataFactory.createParameter("in1", SPParameter.IN, DataTypeManager.DefaultDataTypes.INTEGER);
    // $NON-NLS-1$ //$NON-NLS-2$
    ColumnSet<Procedure> rs3 = RealMetadataFactory.createResultSet("pm1.sp119.rs1", new String[] { "e1" }, new String[] { DataTypeManager.DefaultDataTypes.INTEGER });
    // $NON-NLS-1$
    Procedure sp1 = RealMetadataFactory.createStoredProcedure("sp119", pm1, Arrays.asList(in1));
    sp1.setResultSet(rs3);
    // $NON-NLS-1$
    String sql = "select * from (exec v1.vp1(1)) foo order by e4, e5";
    List<?>[] expected = new List[] { Arrays.asList(1, 1, 3, 3, 5), Arrays.asList(1, 1, 3, 3, 8), Arrays.asList(1, 1, 6, 6, 8), Arrays.asList(1, 1, 6, 6, 11) };
    QueryMetadataInterface metadata = RealMetadataFactory.createTransformationMetadata(metadataStore, "foo");
    // Construct data manager with data
    // Plan query
    ProcessorPlan plan = TestProcedureProcessor.getProcedurePlan(sql, metadata);
    // Run query
    HardcodedDataManager dataManager = new HardcodedDataManager() {

        @Override
        public TupleSource registerRequest(CommandContext context, Command command, String modelName, RegisterRequestParameter parameterObject) throws TeiidComponentException {
            if (command instanceof StoredProcedure) {
                StoredProcedure proc = (StoredProcedure) command;
                List<SPParameter> params = proc.getInputParameters();
                assertEquals(1, params.size());
                int value = (Integer) ((Constant) params.get(0).getExpression()).getValue();
                return new FakeTupleSource(command.getProjectedSymbols(), new List[] { Arrays.asList(value + 2), Arrays.asList(value + 5) });
            }
            return super.registerRequest(context, command, modelName, parameterObject);
        }
    };
    TestProcedureProcessor.helpTestProcess(plan, expected, dataManager, metadata);
}