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);
}
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");
}
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());
}
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);
}
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;
}
Aggregations