Examples with QueryNode org.teiid.query.mapping.relational.QueryNode used on opensource projects

Example 31 with QueryNode

use of org.teiid.query.mapping.relational.QueryNode in project teiid by teiid.

the class TestProcedureProcessor method testFunctionInput.

/**
 * defect 23975
 */
@Test
public void testFunctionInput() throws Exception {
    MetadataStore metadataStore = new MetadataStore();
    // $NON-NLS-1$
    Schema v1 = RealMetadataFactory.createVirtualModel("v1", metadataStore);
    // $NON-NLS-1$
    ProcedureParameter p1 = RealMetadataFactory.createParameter("in", ParameterInfo.IN, DataTypeManager.DefaultDataTypes.STRING);
    // $NON-NLS-1$ //$NON-NLS-2$
    ColumnSet<Procedure> rs1 = RealMetadataFactory.createResultSet("v1.rs1", new String[] { "e1" }, new String[] { DataTypeManager.DefaultDataTypes.STRING });
    // $NON-NLS-1$ //$NON-NLS-2$
    QueryNode n1 = new QueryNode("CREATE VIRTUAL PROCEDURE BEGIN declare string VARIABLES.x = '1'; exec v1.vp2(concat(x, v1.vp1.in)); END");
    // $NON-NLS-1$
    Procedure vt1 = RealMetadataFactory.createVirtualProcedure("vp1", v1, Arrays.asList(p1), n1);
    vt1.setResultSet(rs1);
    // $NON-NLS-1$
    ProcedureParameter p2 = RealMetadataFactory.createParameter("in", ParameterInfo.IN, DataTypeManager.DefaultDataTypes.STRING);
    // $NON-NLS-1$ //$NON-NLS-2$
    QueryNode n2 = new QueryNode("CREATE VIRTUAL PROCEDURE BEGIN select v1.vp2.in; end");
    // $NON-NLS-1$
    Procedure vt2 = RealMetadataFactory.createVirtualProcedure("vp2", v1, Arrays.asList(p2), n2);
    // $NON-NLS-1$ //$NON-NLS-2$
    vt2.setResultSet(RealMetadataFactory.createResultSet("v1.rs1", new String[] { "e1" }, new String[] { DataTypeManager.DefaultDataTypes.STRING }));
    // $NON-NLS-1$
    String sql = "exec v1.vp1('1')";
    List[] expected = new List[] { // $NON-NLS-1$
    Arrays.asList(new Object[] { "11" }) };
    QueryMetadataInterface metadata = RealMetadataFactory.createTransformationMetadata(metadataStore, "foo");
    // Construct data manager with data
    // Plan query
    ProcessorPlan plan = getProcedurePlan(sql, metadata);
    // Run query
    helpTestProcess(plan, expected, new FakeDataManager(), metadata);
}

Example 32 with QueryNode

use of org.teiid.query.mapping.relational.QueryNode in project teiid by teiid.

the class TestVirtualDepJoin method exampleVirtualDepJoin.

public static TransformationMetadata exampleVirtualDepJoin() {
    MetadataStore metadataStore = new MetadataStore();
    // $NON-NLS-1$
    Schema us = RealMetadataFactory.createPhysicalModel("US", metadataStore);
    // $NON-NLS-1$
    Table usAccts = RealMetadataFactory.createPhysicalGroup("Accounts", us);
    usAccts.setCardinality(1000000);
    List<Column> usAcctsElem = RealMetadataFactory.createElements(usAccts, // $NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$
    new String[] { "customer", "account", "txn", "txnid", "pennies" }, new String[] { DataTypeManager.DefaultDataTypes.LONG, DataTypeManager.DefaultDataTypes.INTEGER, DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.INTEGER, DataTypeManager.DefaultDataTypes.INTEGER });
    setStats(usAcctsElem, // NDV per column
    new int[] { 1000, 1250, 4, 1000000, 800000 }, // NNV per column
    new int[] { 0, 0, 0, 0, 0 }, // min per column - use defaults //$NON-NLS-1$ //$NON-NLS-2$
    new String[] { "0", null, null, null, "-10" }, // max per column - use defaults //$NON-NLS-1$ //$NON-NLS-2$
    new String[] { "1000", null, null, null, "-5" });
    // $NON-NLS-1$
    Schema europe = RealMetadataFactory.createPhysicalModel("Europe", metadataStore);
    // $NON-NLS-1$
    Table euAccts = RealMetadataFactory.createPhysicalGroup("CustAccts", europe);
    euAccts.setCardinality(1000000);
    List<Column> euAcctsElem = RealMetadataFactory.createElements(euAccts, // $NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$
    new String[] { "id", "accid", "type", "amount" }, new String[] { DataTypeManager.DefaultDataTypes.LONG, DataTypeManager.DefaultDataTypes.LONG, DataTypeManager.DefaultDataTypes.SHORT, DataTypeManager.DefaultDataTypes.BIG_DECIMAL });
    setStats(euAcctsElem, // NDV per column
    new int[] { 10000, 1000000, 4, 1000000, 750000 }, // NNV per column
    new int[] { 0, 0, 0, 0, 0 }, // min per column - use defaults
    null, // max per column - use defaults
    null);
    // $NON-NLS-1$
    Schema cust = RealMetadataFactory.createPhysicalModel("CustomerMaster", metadataStore);
    // $NON-NLS-1$
    Table customers = RealMetadataFactory.createPhysicalGroup("Customers", cust);
    customers.setCardinality(1000);
    List<Column> customersElem = RealMetadataFactory.createElements(customers, // $NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$
    new String[] { "id", "first", "last", "birthday" }, new String[] { DataTypeManager.DefaultDataTypes.LONG, DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.DATE });
    setStats(customersElem, // NDV per column
    new int[] { 1000, 800, 800, 365 }, // NNV per column
    new int[] { 0, 0, 0, 0 }, // min per column - use defaults
    null, // max per column - use defaults
    null);
    // $NON-NLS-1$
    Table locations = RealMetadataFactory.createPhysicalGroup("Locations", cust);
    locations.setCardinality(1200);
    List<Column> locationsElem = RealMetadataFactory.createElements(locations, // $NON-NLS-1$ //$NON-NLS-2$
    new String[] { "id", "location" }, new String[] { DataTypeManager.DefaultDataTypes.LONG, DataTypeManager.DefaultDataTypes.STRING });
    setStats(locationsElem, // NDV per column
    new int[] { 1000, 2 }, // NNV per column
    new int[] { 0, 0, 0, 0 }, // min per column - use defaults
    null, // max per column - use defaults
    null);
    // $NON-NLS-1$
    Schema vAccts = RealMetadataFactory.createVirtualModel("Accounts", metadataStore);
    QueryNode accountsPlan = new QueryNode(// $NON-NLS-1$
    "SELECT customer as customer_id, convert(account, long) as account_id, convert(txnid, long) as transaction_id, case txn when 'DEP' then 1 when 'TFR' then 2 when 'WD' then 3 else -1 end as txn_type, (pennies + convert('0.00', bigdecimal)) / 100 as amount, 'US' as source FROM US.Accounts where txn != 'X'" + // $NON-NLS-1$
    "UNION ALL " + // $NON-NLS-1$
    "SELECT id, convert(accid / 10000, long), mod(accid, 10000), convert(\"type\", integer), amount, 'EU' from Europe.CustAccts");
    // $NON-NLS-1$
    Table accounts = RealMetadataFactory.createVirtualGroup("Accounts", vAccts, accountsPlan);
    RealMetadataFactory.createElements(accounts, // $NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$ //$NON-NLS-6$
    new String[] { "customer_id", "account_id", "transaction_id", "txn_type", "amount", "source" }, new String[] { DataTypeManager.DefaultDataTypes.LONG, DataTypeManager.DefaultDataTypes.LONG, DataTypeManager.DefaultDataTypes.LONG, DataTypeManager.DefaultDataTypes.INTEGER, DataTypeManager.DefaultDataTypes.BIG_DECIMAL, DataTypeManager.DefaultDataTypes.STRING });
    // $NON-NLS-1$
    Schema master = RealMetadataFactory.createVirtualModel("Master", metadataStore);
    // $NON-NLS-1$
    QueryNode masterPlan = new QueryNode("select id as CustomerID, First, Last, a.account_id as AccountID, transaction_id as TransactionID, txn_type AS TxnCode, Amount from CustomerMaster.Customers c, Accounts.Accounts a where c.id=a.customer_id");
    // $NON-NLS-1$
    Table transactions = RealMetadataFactory.createVirtualGroup("Transactions", master, masterPlan);
    RealMetadataFactory.createElements(transactions, // $NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$ //$NON-NLS-6$ //$NON-NLS-7$
    new String[] { "CustomerID", "First", "Last", "AccountID", "TransactionID", "TxnCode", "Amount" }, new String[] { DataTypeManager.DefaultDataTypes.LONG, DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.LONG, DataTypeManager.DefaultDataTypes.LONG, DataTypeManager.DefaultDataTypes.INTEGER, DataTypeManager.DefaultDataTypes.BIG_DECIMAL });
    return RealMetadataFactory.createTransformationMetadata(metadataStore, "virtualDepJoin");
}

Example 33 with QueryNode

use of org.teiid.query.mapping.relational.QueryNode in project teiid by teiid.

the class TestSQLXMLProcessing method testXmlTableInView.

@Test
public void testXmlTableInView() throws Exception {
    // $NON-NLS-1$
    String sql = "select * from g1";
    List<?>[] expected = new List<?>[] { Arrays.asList(null, "first"), Arrays.asList("attr", "second") };
    MetadataStore metadataStore = new MetadataStore();
    // Create models
    // $NON-NLS-1$
    Schema vm1 = RealMetadataFactory.createVirtualModel("vm1", metadataStore);
    // $NON-NLS-1$ //$NON-NLS-2$
    QueryNode vm1g1n1 = new QueryNode("select * from xmltable('/a/b' passing convert('<a><b>first</b><b x=\"attr\">second</b></a>', xml) columns x string path '@x', val string path '/.') as x");
    // $NON-NLS-1$
    Table vm1g1 = RealMetadataFactory.createVirtualGroup("g1", vm1, vm1g1n1);
    RealMetadataFactory.createElements(vm1g1, // $NON-NLS-1$
    new String[] { "x", "val" }, new String[] { DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.STRING });
    // Create the facade from the store
    TransformationMetadata metadata = RealMetadataFactory.createTransformationMetadata(metadataStore, "example");
    ProcessorPlan plan = helpGetPlan(helpParse(sql), metadata, new DefaultCapabilitiesFinder(), createCommandContext());
    helpProcess(plan, createCommandContext(), dataManager, expected);
    plan = helpGetPlan(helpParse(sql), metadata, new DefaultCapabilitiesFinder(), createCommandContext());
    doProcess(plan, dataManager, expected, createCommandContext());
}

Example 34 with QueryNode

use of org.teiid.query.mapping.relational.QueryNode in project teiid by teiid.

the class TestSourceHints method testHintInView.

@Test
public void testHintInView() {
    MetadataStore metadataStore = new MetadataStore();
    // $NON-NLS-1$
    Schema p1 = RealMetadataFactory.createPhysicalModel("p1", metadataStore);
    // $NON-NLS-1$
    Table t1 = RealMetadataFactory.createPhysicalGroup("t", p1);
    // $NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$
    RealMetadataFactory.createElements(t1, new String[] { "a", "b" }, new String[] { "string", "string" });
    // $NON-NLS-1$
    Schema v1 = RealMetadataFactory.createVirtualModel("v1", metadataStore);
    // $NON-NLS-1$ //$NON-NLS-2$
    QueryNode n1 = new QueryNode("SELECT /*+ sh:'x' */ a as c, b FROM p1.t");
    // $NON-NLS-1$
    Table vt1 = RealMetadataFactory.createVirtualGroup("t1", v1, n1);
    // $NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$
    RealMetadataFactory.createElements(vt1, new String[] { "c", "b" }, new String[] { "string", "string" });
    TransformationMetadata metadata = RealMetadataFactory.createTransformationMetadata(metadataStore, "metadata");
    // top level applies
    HardcodedDataManager manager = manager("foo x", "leading");
    // $NON-NLS-1$
    String sql = "SELECT /*+ sh:'foo' bar:'leading' */ c from t1 order by c limit 1";
    ProcessorPlan plan = helpGetPlan(sql, metadata);
    List<?>[] expected = new List[] {};
    helpProcess(plan, manager, expected);
    // use the underlying hint
    manager = manager("x", null);
    // $NON-NLS-1$
    sql = "SELECT c from t1 order by c limit 1";
    plan = helpGetPlan(sql, metadata);
    helpProcess(plan, manager, expected);
    // $NON-NLS-1$
    sql = "SELECT c from t1 union all select c from t1";
    plan = helpGetPlan(sql, metadata);
    helpProcess(plan, manager, expected);
}

Example 35 with QueryNode

use of org.teiid.query.mapping.relational.QueryNode in project teiid by teiid.

the class RealMetadataFactory method exampleBusObjStore.

public static MetadataStore exampleBusObjStore() {
    MetadataStore metadataStore = new MetadataStore();
    // Create db2 tables
    // $NON-NLS-1$
    Schema db2Model = createPhysicalModel("db2model", metadataStore);
    // $NON-NLS-1$
    Table db2Table = createPhysicalGroup("DB2_TABLE", db2Model);
    createElements(db2Table, // $NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
    new String[] { "PRODUCT", "REGION", "SALES" }, new String[] { DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.DOUBLE });
    // $NON-NLS-1$
    Table salesTable = createPhysicalGroup("SALES", db2Model);
    salesTable.setCardinality(1000000);
    createElements(salesTable, // $NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
    new String[] { "CITY", "MONTH", "SALES" }, new String[] { DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.DOUBLE });
    // $NON-NLS-1$
    Table geographyTable2 = createPhysicalGroup("GEOGRAPHY2", db2Model);
    geographyTable2.setCardinality(1000);
    List<Column> geographyElem2 = createElements(geographyTable2, // $NON-NLS-1$ //$NON-NLS-2$
    new String[] { "CITY", "REGION" }, new String[] { DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.STRING });
    List<Column> geoPkElem2 = new ArrayList<Column>();
    geoPkElem2.add(geographyElem2.get(0));
    // $NON-NLS-1$
    createKey(KeyRecord.Type.Primary, "db2model.GEOGRAPHY2.GEOGRAPHY_PK", geographyTable2, geoPkElem2);
    // $NON-NLS-1$
    Table db2Table2 = createPhysicalGroup("DB2TABLE", db2Model);
    createElements(db2Table2, // $NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
    new String[] { "c0", "c1", "c2" }, new String[] { DataTypeManager.DefaultDataTypes.INTEGER, DataTypeManager.DefaultDataTypes.INTEGER, DataTypeManager.DefaultDataTypes.INTEGER });
    // Create oracle tables
    // $NON-NLS-1$
    Schema oraModel = createPhysicalModel("oraclemodel", metadataStore);
    // $NON-NLS-1$
    Table oraTable = createPhysicalGroup("Oracle_table", oraModel);
    createElements(oraTable, // $NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
    new String[] { "COSTS", "REGION", "YEAR" }, new String[] { DataTypeManager.DefaultDataTypes.DOUBLE, DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.STRING });
    // $NON-NLS-1$
    Table geographyTable = createPhysicalGroup("GEOGRAPHY", oraModel);
    geographyTable.setCardinality(1000);
    List<Column> geographyElem = createElements(geographyTable, // $NON-NLS-1$ //$NON-NLS-2$
    new String[] { "CITY", "REGION" }, new String[] { DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.STRING });
    List<Column> geoPkElem = new ArrayList<Column>();
    geoPkElem.add(geographyElem.get(0));
    // $NON-NLS-1$
    createKey(KeyRecord.Type.Primary, "oraclemodel.GEOGRAPHY.GEOGRAPHY_PK", geographyTable, geoPkElem);
    // $NON-NLS-1$
    Table oraTable2 = createPhysicalGroup("OraTable", oraModel);
    createElements(oraTable2, // $NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
    new String[] { "b0", "b1", "b2" }, new String[] { DataTypeManager.DefaultDataTypes.DOUBLE, DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.STRING });
    // Create sql server tables
    // $NON-NLS-1$
    Schema msModel = createPhysicalModel("msmodel", metadataStore);
    // $NON-NLS-1$
    Table timeTable = createPhysicalGroup("TIME", msModel);
    timeTable.setCardinality(120);
    List<Column> timeElem = createElements(timeTable, // $NON-NLS-1$ //$NON-NLS-2$
    new String[] { "MONTH", "YEAR" }, new String[] { DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.STRING });
    List<Column> timePkElem = new ArrayList<Column>();
    timePkElem.add(timeElem.get(0));
    // $NON-NLS-1$
    createKey(KeyRecord.Type.Primary, "msmodel.TIME.TIME_PK", timeTable, timePkElem);
    // $NON-NLS-1$
    Schema virtModel = createVirtualModel("logical", metadataStore);
    // $NON-NLS-1$ //$NON-NLS-2$
    QueryNode n1 = new QueryNode("select sum(c0) as c0, c1, c2 from db2Table group by c1, c2");
    // $NON-NLS-1$
    Table logicalTable1 = createVirtualGroup("logicalTable1", virtModel, n1);
    createElements(logicalTable1, // $NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
    new String[] { "c0", "c1", "c2" }, new String[] { DataTypeManager.DefaultDataTypes.LONG, DataTypeManager.DefaultDataTypes.INTEGER, DataTypeManager.DefaultDataTypes.INTEGER });
    // $NON-NLS-1$ //$NON-NLS-2$
    QueryNode n2 = new QueryNode("select sum(c0) as c0, c1, c2 from db2Table group by c1, c2");
    // $NON-NLS-1$
    Table logicalTable2 = createVirtualGroup("logicalTable2", virtModel, n2);
    createElements(logicalTable2, // $NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
    new String[] { "b0", "b1", "b2" }, new String[] { DataTypeManager.DefaultDataTypes.LONG, DataTypeManager.DefaultDataTypes.INTEGER, DataTypeManager.DefaultDataTypes.INTEGER });
    return metadataStore;
}