Search in sources :

Example 51 with MetadataStore

use of org.teiid.metadata.MetadataStore 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");
}
Also used : MetadataStore(org.teiid.metadata.MetadataStore) Table(org.teiid.metadata.Table) Column(org.teiid.metadata.Column) QueryNode(org.teiid.query.mapping.relational.QueryNode) Schema(org.teiid.metadata.Schema)

Example 52 with MetadataStore

use of org.teiid.metadata.MetadataStore 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());
}
Also used : MetadataStore(org.teiid.metadata.MetadataStore) TransformationMetadata(org.teiid.query.metadata.TransformationMetadata) Table(org.teiid.metadata.Table) QueryNode(org.teiid.query.mapping.relational.QueryNode) Schema(org.teiid.metadata.Schema) List(java.util.List) DefaultCapabilitiesFinder(org.teiid.query.optimizer.capabilities.DefaultCapabilitiesFinder) Test(org.junit.Test)

Example 53 with MetadataStore

use of org.teiid.metadata.MetadataStore 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);
}
Also used : MetadataStore(org.teiid.metadata.MetadataStore) TransformationMetadata(org.teiid.query.metadata.TransformationMetadata) Table(org.teiid.metadata.Table) QueryNode(org.teiid.query.mapping.relational.QueryNode) Schema(org.teiid.metadata.Schema) List(java.util.List) Test(org.junit.Test)

Example 54 with MetadataStore

use of org.teiid.metadata.MetadataStore in project teiid by teiid.

the class TestResolver method example_12968.

public static TransformationMetadata example_12968() {
    MetadataStore metadataStore = new MetadataStore();
    // Create models
    // $NON-NLS-1$
    Schema pm1 = RealMetadataFactory.createPhysicalModel("myModel", metadataStore);
    // $NON-NLS-1$
    Schema pm2 = RealMetadataFactory.createPhysicalModel("myModel2", metadataStore);
    // $NON-NLS-1$
    Table pm1g1 = RealMetadataFactory.createPhysicalGroup("myTable", pm1);
    // $NON-NLS-1$
    Table pm2g1 = RealMetadataFactory.createPhysicalGroup("mySchema.myTable2", pm2);
    RealMetadataFactory.createElements(pm1g1, // $NON-NLS-1$ //$NON-NLS-2$
    new String[] { "myColumn", "myColumn2" }, new String[] { DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.INTEGER });
    RealMetadataFactory.createElements(pm2g1, // $NON-NLS-1$ //$NON-NLS-2$
    new String[] { "myColumn", "myColumn2" }, new String[] { DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.INTEGER });
    return RealMetadataFactory.createTransformationMetadata(metadataStore, "12968");
}
Also used : MetadataStore(org.teiid.metadata.MetadataStore) TempMetadataStore(org.teiid.query.metadata.TempMetadataStore) Table(org.teiid.metadata.Table) Schema(org.teiid.metadata.Schema)

Example 55 with MetadataStore

use of org.teiid.metadata.MetadataStore in project teiid by teiid.

the class TestResolver method testExecWithDuplicateNames.

@Test
public void testExecWithDuplicateNames() {
    MetadataStore metadataStore = new MetadataStore();
    Schema pm1 = RealMetadataFactory.createPhysicalModel("pm1", metadataStore);
    // $NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
    ColumnSet<Procedure> rs2 = RealMetadataFactory.createResultSet("rs2", new String[] { "in", "e2" }, new String[] { DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.INTEGER });
    // $NON-NLS-1$
    ProcedureParameter rs2p2 = RealMetadataFactory.createParameter("in", ParameterInfo.IN, DataTypeManager.DefaultDataTypes.STRING);
    // $NON-NLS-1$
    Procedure sq2 = RealMetadataFactory.createStoredProcedure("sq2", pm1, Arrays.asList(rs2p2));
    sq2.setResultSet(rs2);
    QueryMetadataInterface metadata = RealMetadataFactory.createTransformationMetadata(metadataStore, "example1");
    // $NON-NLS-1$ //$NON-NLS-2$
    helpResolveException("select * from pm1.sq2", metadata, "TEIID30114 Cannot access procedure pm1.sq2 using table semantics since the parameter and result set column names are not all unique.");
}
Also used : MetadataStore(org.teiid.metadata.MetadataStore) TempMetadataStore(org.teiid.query.metadata.TempMetadataStore) ProcedureParameter(org.teiid.metadata.ProcedureParameter) Schema(org.teiid.metadata.Schema) Procedure(org.teiid.metadata.Procedure) QueryMetadataInterface(org.teiid.query.metadata.QueryMetadataInterface) Test(org.junit.Test)

Aggregations

MetadataStore (org.teiid.metadata.MetadataStore)73 Schema (org.teiid.metadata.Schema)47 Test (org.junit.Test)38 Table (org.teiid.metadata.Table)37 TransformationMetadata (org.teiid.query.metadata.TransformationMetadata)22 QueryNode (org.teiid.query.mapping.relational.QueryNode)19 Column (org.teiid.metadata.Column)17 VDBMetaData (org.teiid.adminapi.impl.VDBMetaData)15 Properties (java.util.Properties)12 MetadataFactory (org.teiid.metadata.MetadataFactory)12 QueryMetadataInterface (org.teiid.query.metadata.QueryMetadataInterface)12 Procedure (org.teiid.metadata.Procedure)11 List (java.util.List)9 ModelMetaData (org.teiid.adminapi.impl.ModelMetaData)9 Connection (java.sql.Connection)8 ConnectorManagerRepository (org.teiid.dqp.internal.datamgr.ConnectorManagerRepository)8 ArrayList (java.util.ArrayList)7 ProcedureParameter (org.teiid.metadata.ProcedureParameter)7 BasicSourceCapabilities (org.teiid.query.optimizer.capabilities.BasicSourceCapabilities)7 TempMetadataStore (org.teiid.query.metadata.TempMetadataStore)6