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

Example 21 with Insert

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

the class TestPreparedStatementBatchedUpdate method testBatchedUpdatePushdown1.

@Test
public void testBatchedUpdatePushdown1() throws Exception {
    // TODO: just use straight ddl
    TransformationMetadata metadata = TestUpdateValidator.example1();
    TestUpdateValidator.createView("select 1 as x, 2 as y", metadata, "GX");
    Table t = metadata.getMetadataStore().getSchemas().get("VM1").getTables().get("GX");
    t.setDeletePlan("");
    t.setUpdatePlan("");
    t.setInsertPlan("FOR EACH ROW BEGIN insert into pm1.g1 (e1) values (new.x); END");
    // $NON-NLS-1$
    String preparedSql = "insert into gx (x, y) values (?,?)";
    // Create a testable prepared plan cache
    SessionAwareCache<PreparedPlan> prepPlanCache = new SessionAwareCache<PreparedPlan>("preparedplan", DefaultCacheFactory.INSTANCE, SessionAwareCache.Type.PREPAREDPLAN, 0);
    // Construct data manager with data
    HardcodedDataManager dataManager = new HardcodedDataManager(metadata);
    // $NON-NLS-1$
    dataManager.addData("INSERT INTO g1 (e1) VALUES (convert(?, string))", new List[] { Arrays.asList(2) });
    // Source capabilities must support batched updates
    FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
    BasicSourceCapabilities caps = TestOptimizer.getTypicalCapabilities();
    caps.setFunctionSupport(SourceSystemFunctions.CONVERT, true);
    caps.setCapabilitySupport(Capability.BULK_UPDATE, true);
    // $NON-NLS-1$
    capFinder.addCapabilities("pm1", caps);
    // batch with two commands
    ArrayList<ArrayList<Object>> values = new ArrayList<ArrayList<Object>>(2);
    // $NON-NLS-1$
    values.add(new ArrayList<Object>(Arrays.asList(3, 4)));
    values.add(new ArrayList<Object>(Arrays.asList(5, 6)));
    List<?>[] expected = new List[] { Arrays.asList(2) };
    // Create the plan and process the query
    TestPreparedStatement.helpTestProcessing(preparedSql, values, expected, dataManager, capFinder, metadata, prepPlanCache, false, false, false, RealMetadataFactory.example1VDB());
    org.teiid.language.Insert insert = (org.teiid.language.Insert) dataManager.getPushdownCommands().iterator().next();
    Parameter p = CollectorVisitor.collectObjects(Parameter.class, insert).iterator().next();
    assertEquals(0, p.getValueIndex());
    assertEquals(Arrays.asList(3), insert.getParameterValues().next());
    assertTrue(insert.getParameterValues().hasNext());
}

Example 22 with Insert

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

the class TestPreparedStatementBatchedUpdate method testBulkBytePushdown.

@Test
public void testBulkBytePushdown() throws Exception {
    // $NON-NLS-1$
    String preparedSql = "insert into g1 (e1) values (?)";
    TransformationMetadata tm = RealMetadataFactory.fromDDL("create foreign table g1 (e1 varbinary) options (updatable true)", "y", "z");
    // Create a testable prepared plan cache
    SessionAwareCache<PreparedPlan> prepPlanCache = new SessionAwareCache<PreparedPlan>("preparedplan", DefaultCacheFactory.INSTANCE, SessionAwareCache.Type.PREPAREDPLAN, 0);
    // Construct data manager with data
    HardcodedDataManager dataManager = new HardcodedDataManager();
    // $NON-NLS-1$
    dataManager.addData("INSERT INTO g1 (e1) VALUES (?)", new List[] { Arrays.asList(1), Arrays.asList(1) });
    // Source capabilities must support batched updates
    FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
    BasicSourceCapabilities caps = TestOptimizer.getTypicalCapabilities();
    caps.setCapabilitySupport(Capability.BULK_UPDATE, true);
    // $NON-NLS-1$
    capFinder.addCapabilities("z", caps);
    ArrayList<List<?>> values = new ArrayList<List<?>>(2);
    // $NON-NLS-1$
    values.add(Arrays.asList(new byte[1]));
    // $NON-NLS-1$
    values.add(Arrays.asList(new byte[1]));
    List<?>[] expected = new List[] { Arrays.asList(1), Arrays.asList(1) };
    // Create the plan and process the query
    TestPreparedStatement.helpTestProcessing(preparedSql, values, expected, dataManager, capFinder, tm, prepPlanCache, false, false, false, tm.getVdbMetaData());
    Insert insert = (Insert) dataManager.getCommandHistory().iterator().next();
    Constant c = (Constant) insert.getValues().get(0);
    assertTrue(c.isMultiValued());
    assertTrue(((List<?>) c.getValue()).get(0) instanceof BinaryType);
}

Example 23 with Insert

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

the class TestInsertProcessing method testSelectInto_Case5412b.

@Test
public void testSelectInto_Case5412b() {
    MetadataStore metadataStore = new MetadataStore();
    // test setting BULK_INSERT capability to false
    // $NON-NLS-1$
    Schema pm1 = RealMetadataFactory.createPhysicalModel("pm1", metadataStore);
    // $NON-NLS-1$
    Table pm1g1 = RealMetadataFactory.createPhysicalGroup("g1", pm1);
    FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
    BasicSourceCapabilities caps = TestOptimizer.getTypicalCapabilities();
    caps.setCapabilitySupport(Capability.BULK_UPDATE, false);
    // $NON-NLS-1$
    capFinder.addCapabilities("pm1", caps);
    caps.setFunctionSupport(SourceSystemFunctions.CONVERT, true);
    RealMetadataFactory.createElements(pm1g1, // $NON-NLS-1$ //$NON-NLS-2$
    new String[] { "e1", "e2" }, new String[] { DataTypeManager.DefaultDataTypes.BIG_INTEGER, DataTypeManager.DefaultDataTypes.FLOAT });
    QueryMetadataInterface metadata = RealMetadataFactory.createTransformationMetadata(metadataStore, "foo");
    HardcodedDataManager dataManager = new HardcodedDataManager();
    // $NON-NLS-1$
    dataManager.addData(// $NON-NLS-1$
    "INSERT INTO pm1.g1 (e1, e2) VALUES (1, 1.0)", new List[] { Arrays.asList(new Object[] { new Integer(1) }) });
    // $NON-NLS-1$
    String sql = "SELECT 1, convert(1, float) INTO pm1.g1";
    Command command = helpParse(sql);
    ProcessorPlan plan = helpGetPlan(command, metadata, capFinder);
    List<?>[] expected = new List[] { Arrays.asList(new Object[] { new Integer(1) }) };
    helpProcess(plan, dataManager, expected);
    Insert insert = (Insert) dataManager.getCommandHistory().iterator().next();
    Constant value0 = (Constant) insert.getValues().get(0);
    Constant value1 = (Constant) insert.getValues().get(1);
    assertEquals(DataTypeManager.DefaultDataClasses.BIG_INTEGER, value0.getValue().getClass());
    assertEquals(DataTypeManager.DefaultDataClasses.FLOAT, value1.getValue().getClass());
}

Example 24 with Insert

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

the class TestInsertProcessing method testSelectIntoWithTypeConversion.

@Test
public void testSelectIntoWithTypeConversion() {
    MetadataStore metadataStore = new MetadataStore();
    // $NON-NLS-1$
    Schema pm1 = RealMetadataFactory.createPhysicalModel("pm1", metadataStore);
    // $NON-NLS-1$
    Table pm1g1 = RealMetadataFactory.createPhysicalGroup("g1", pm1);
    FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
    BasicSourceCapabilities caps = TestOptimizer.getTypicalCapabilities();
    caps.setCapabilitySupport(Capability.BATCHED_UPDATES, true);
    caps.setFunctionSupport(SourceSystemFunctions.CONVERT, true);
    // $NON-NLS-1$
    capFinder.addCapabilities("pm1", caps);
    RealMetadataFactory.createElements(pm1g1, // $NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
    new String[] { "e1", "e2", "e3" }, new String[] { DataTypeManager.DefaultDataTypes.BIG_INTEGER, DataTypeManager.DefaultDataTypes.FLOAT, DataTypeManager.DefaultDataTypes.FLOAT });
    QueryMetadataInterface metadata = RealMetadataFactory.createTransformationMetadata(metadataStore, "foo");
    HardcodedDataManager dataManager = new HardcodedDataManager();
    // $NON-NLS-1$
    dataManager.addData(// $NON-NLS-1$
    "BatchedUpdate{I}", new List[] { Arrays.asList(new Object[] { new Integer(1) }) });
    // $NON-NLS-1$
    String sql = "SELECT 1, convert(1, float), convert(1, float) INTO pm1.g1";
    Command command = helpParse(sql);
    ProcessorPlan plan = helpGetPlan(command, metadata, capFinder);
    List<?>[] expected = new List[] { Arrays.asList(new Object[] { new Integer(1) }) };
    helpProcess(plan, dataManager, expected);
    BatchedUpdateCommand buc = (BatchedUpdateCommand) dataManager.getCommandHistory().iterator().next();
    Insert insert = (Insert) buc.getUpdateCommands().get(0);
    Constant value0 = (Constant) insert.getValues().get(0);
    Constant value1 = (Constant) insert.getValues().get(1);
    assertEquals(DataTypeManager.DefaultDataClasses.BIG_INTEGER, value0.getValue().getClass());
    assertEquals(DataTypeManager.DefaultDataClasses.FLOAT, value1.getValue().getClass());
}

Example 25 with Insert

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

the class TestInsertProcessing method helpInsertIntoWithSubquery.

public void helpInsertIntoWithSubquery(Capability cap, boolean txn) throws Exception {
    FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
    BasicSourceCapabilities caps = TestOptimizer.getTypicalCapabilities();
    caps.setCapabilitySupport(cap, true);
    caps.setCapabilitySupport(Capability.QUERY_FROM_GROUP_ALIAS, false);
    // $NON-NLS-1$
    capFinder.addCapabilities("pm1", caps);
    QueryMetadataInterface metadata = RealMetadataFactory.example1Cached();
    HardcodedDataManager dataManager = new HardcodedDataManager() {

        @Override
        public TupleSource registerRequest(CommandContext context, Command command, String modelName, RegisterRequestParameter parameterObject) throws TeiidComponentException {
            if (command instanceof Insert) {
                Insert insert = (Insert) command;
                if (insert.getTupleSource() != null) {
                    commandHistory.add(insert);
                    TupleSource ts = insert.getTupleSource();
                    int count = 0;
                    try {
                        while (ts.nextTuple() != null) {
                            count++;
                        }
                        return CollectionTupleSource.createUpdateCountArrayTupleSource(count);
                    } catch (TeiidProcessingException e) {
                        throw new RuntimeException(e);
                    }
                }
            }
            return super.registerRequest(context, command, modelName, parameterObject);
        }
    };
    List[] data = new List[txn ? 2 : 50];
    for (int i = 1; i <= data.length; i++) {
        data[i - 1] = Arrays.asList(String.valueOf(i), i, (i % 2 == 0) ? Boolean.TRUE : Boolean.FALSE, Double.valueOf(i));
    }
    // $NON-NLS-1$
    dataManager.addData(// $NON-NLS-1$
    "SELECT pm1.g1.e1, pm1.g1.e2, pm1.g1.e3, pm1.g1.e4 FROM pm1.g1", data);
    if (cap != null) {
        switch(cap) {
            case BATCHED_UPDATES:
                // $NON-NLS-1$
                dataManager.addData(// $NON-NLS-1$
                "BatchedUpdate{I,I}", new List[] { Arrays.asList(1), Arrays.asList(1) });
                break;
            case INSERT_WITH_QUERYEXPRESSION:
                // $NON-NLS-1$
                dataManager.addData(// $NON-NLS-1$
                "INSERT INTO pm1.g2 (e1, e2, e3, e4) SELECT pm1.g1.e1, pm1.g1.e2, pm1.g1.e3, pm1.g1.e4 FROM pm1.g1", new List[] { Arrays.asList(new Object[] { 2 }) });
                break;
        }
    } 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";
    Command command = helpParse(sql);
    ProcessorPlan plan = helpGetPlan(command, metadata, capFinder);
    List<?>[] expected = new List[] { Arrays.asList(txn ? 2 : 50) };
    CommandContext cc = TestProcessor.createCommandContext();
    if (!txn) {
        TransactionContext tc = new TransactionContext();
        cc.setTransactionContext(tc);
        cc.setBufferManager(null);
        cc.setProcessorBatchSize(2);
    }
    helpProcess(plan, cc, dataManager, expected);
    // check the command hist to ensure it contains the expected commands
    if (cap == Capability.BATCHED_UPDATES) {
        BatchedUpdateCommand bu = (BatchedUpdateCommand) dataManager.getCommandHistory().get(1);
        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());
    } else if (cap == Capability.INSERT_WITH_ITERATOR) {
        assertEquals(txn ? 6 : 9, dataManager.getCommandHistory().size());
    }
}