/*
* JBoss, Home of Professional Open Source.
* See the COPYRIGHT.txt file distributed with this work for information
* regarding copyright ownership. Some portions may be licensed
* to Red Hat, Inc. under one or more contributor license agreements.
*
* This library is free software; you can redistribute it and/or
* modify it under the terms of the GNU Lesser General Public
* License as published by the Free Software Foundation; either
* version 2.1 of the License, or (at your option) any later version.
*
* This library is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
* Lesser General Public License for more details.
*
* You should have received a copy of the GNU Lesser General Public
* License along with this library; if not, write to the Free Software
* Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
* 02110-1301 USA.
*/
package org.teiid.query.processor.xml;
import static org.junit.Assert.*;
import java.util.Arrays;
import java.util.List;
import java.util.Map;
import org.junit.Ignore;
import org.junit.Test;
import org.teiid.client.metadata.ParameterInfo;
import org.teiid.common.buffer.BufferManager;
import org.teiid.core.types.DataTypeManager;
import org.teiid.metadata.ColumnSet;
import org.teiid.metadata.Procedure;
import org.teiid.metadata.ProcedureParameter;
import org.teiid.metadata.Schema;
import org.teiid.metadata.Table;
import org.teiid.query.mapping.relational.QueryNode;
import org.teiid.query.mapping.xml.MappingAttribute;
import org.teiid.query.mapping.xml.MappingDocument;
import org.teiid.query.mapping.xml.MappingElement;
import org.teiid.query.metadata.QueryMetadataInterface;
import org.teiid.query.metadata.TransformationMetadata;
import org.teiid.query.optimizer.TestOptimizer;
import org.teiid.query.optimizer.relational.rules.RuleChooseDependent;
import org.teiid.query.processor.FakeDataManager;
import org.teiid.query.processor.ProcessorPlan;
import org.teiid.query.unittest.RealMetadataFactory;
@SuppressWarnings("nls")
public class TestXMLPlanningEnhancements {
private TransformationMetadata getMetadata(String query) {
TransformationMetadata metadata = TestXMLProcessor.exampleMetadata();
Schema xmltest = metadata.getMetadataStore().getSchemas().get("XMLTEST");
QueryNode rsQueryY = new QueryNode(query); //$NON-NLS-1$
rsQueryY.addBinding("xmltest.group.items.itemNum"); //$NON-NLS-1$
rsQueryY.addBinding("xmltest.group.items.itemNum"); //$NON-NLS-1$
Table rsQY = RealMetadataFactory.createVirtualGroup("suppliersY", xmltest, rsQueryY); //$NON-NLS-1$
RealMetadataFactory.createElements(rsQY,
new String[] { "supplierNum", "supplierName", "supplierZipCode" }, //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
new String[] { DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.STRING });
RealMetadataFactory.createXmlDocument("doc18a", xmltest, TestXMLProcessor.createXMLPlanNested("xmltest.suppliersY")); //$NON-NLS-1$ //$NON-NLS-2$
return metadata;
}
private TransformationMetadata getTempTableMetadata(boolean simpleTempSelect) {
TransformationMetadata metadata = TestXMLProcessor.exampleMetadata();
Schema xmltest = metadata.getMetadataStore().getSchemas().get("XMLTEST");
RealMetadataFactory.createXmlDocument("docJoin", xmltest, createXMLPlanNestedJoin()); //$NON-NLS-1$
QueryNode tempQueryJoin = null;
if (!simpleTempSelect) {
tempQueryJoin = new QueryNode("SELECT stock.orders.* FROM stock.orders"); //$NON-NLS-1$ //$NON-NLS-2$
} else {
tempQueryJoin = new QueryNode("SELECT stock.orders.* FROM stock.orders join stock.suppliers on stock.orders.supplierFK=stock.suppliers.supplierNum"); //$NON-NLS-1$ //$NON-NLS-2$
}
Table tempJoin = RealMetadataFactory.createXmlStagingTable("docJoin.orders", xmltest, tempQueryJoin); //$NON-NLS-1$
// Created virtual group w/ nested result set & binding - selects from 2nd temp root group
QueryNode rsQueryJoin = null;
if (!simpleTempSelect) {
rsQueryJoin = new QueryNode("SELECT orderNum, orderDate, orderQty, orderStatus FROM docJoin.orders join stock.suppliers on docJoin.orders.supplierFK=stock.suppliers.supplierNum WHERE itemFK = ? AND supplierNameFK = ?"); //$NON-NLS-1$ //$NON-NLS-2$
rsQueryJoin.addBinding("xmltest.group.items.itemNum"); //$NON-NLS-1$
rsQueryJoin.addBinding("xmltest.suppliers.supplierName"); //$NON-NLS-1$
} else {
rsQueryJoin = new QueryNode("SELECT orderNum, orderDate, orderQty, orderStatus FROM docJoin.orders WHERE supplierNameFK = ?"); //$NON-NLS-1$ //$NON-NLS-2$
rsQueryJoin.addBinding("xmltest.suppliers.supplierName"); //$NON-NLS-1$
}
Table rsJoin = RealMetadataFactory.createVirtualGroup("ordersC", xmltest, rsQueryJoin); //$NON-NLS-1$
RealMetadataFactory.createElements(tempJoin,
new String[] { "orderNum", "itemFK", "supplierFK", "supplierNameFK", "orderDate", "orderQty", "orderStatus" }, //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$ //$NON-NLS-6$ //$NON-NLS-7$
new String[] { DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.INTEGER, DataTypeManager.DefaultDataTypes.STRING});
RealMetadataFactory.createElements(rsJoin,
new String[] { "orderNum", "orderDate", "orderQty", "orderStatus" }, //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$
new String[] { DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.INTEGER, DataTypeManager.DefaultDataTypes.STRING});
return metadata;
}
private static MappingDocument createXMLPlanNestedJoin() {
MappingDocument doc = new MappingDocument(true);
MappingElement root = doc.addChildElement(new MappingElement("Catalogs")); //$NON-NLS-1$
root.setStagingTables(Arrays.asList("xmltest.docJoin.orders")); //$NON-NLS-1$
MappingElement cats = root.addChildElement(new MappingElement("Catalog")); //$NON-NLS-1$
MappingElement items = cats.addChildElement(new MappingElement("Items")); //$NON-NLS-1$
MappingElement item = items.addChildElement(new MappingElement("Item")); //$NON-NLS-1$
item.setSource("xmltest.group.items"); //$NON-NLS-1$
item.setMaxOccurrs(-1);
item.addAttribute(new MappingAttribute("ItemID", "xmltest.group.items.itemNum")); //$NON-NLS-1$ //$NON-NLS-2$
item.addChildElement(new MappingElement("Name", "xmltest.group.items.itemName")); //$NON-NLS-1$ //$NON-NLS-2$
item.addChildElement(new MappingElement("Quantity", "xmltest.group.items.itemQuantity")); //$NON-NLS-1$ //$NON-NLS-2$
//NESTED STUFF======================================================================
MappingElement nestedWrapper = item.addChildElement(new MappingElement("Suppliers")); //$NON-NLS-1$
MappingElement supplier = nestedWrapper.addChildElement(new MappingElement("Supplier")); //$NON-NLS-1$
supplier.setSource("xmltest.suppliers"); //$NON-NLS-1$
supplier.setMaxOccurrs(-1);
supplier.addAttribute(new MappingAttribute("SupplierID", "xmltest.suppliers.supplierNum")); //$NON-NLS-1$ //$NON-NLS-2$
supplier.addChildElement(new MappingElement("Name","xmltest.suppliers.supplierName")); //$NON-NLS-1$ //$NON-NLS-2$
supplier.addChildElement(new MappingElement("Zip", "xmltest.suppliers.supplierZipCode")); //$NON-NLS-1$ //$NON-NLS-2$
MappingElement ordersWrapper = supplier.addChildElement(new MappingElement("Orders")); //$NON-NLS-1$
MappingElement order = ordersWrapper.addChildElement(new MappingElement("Order")); //$NON-NLS-1$
order.setSource("xmltest.ordersC"); //$NON-NLS-1$
order.setMaxOccurrs(-1);
order.addAttribute(new MappingAttribute("OrderID", "xmltest.ordersC.orderNum")); //$NON-NLS-1$ //$NON-NLS-2$
order.addChildElement(new MappingElement("OrderDate", "xmltest.ordersC.orderDate")); //$NON-NLS-1$ //$NON-NLS-2$
order.addChildElement(new MappingElement("OrderQuantity", "xmltest.ordersC.orderQty")); //$NON-NLS-1$ //$NON-NLS-2$
order.addChildElement(new MappingElement("OrderStatus", "xmltest.ordersC.orderStatus")) //$NON-NLS-1$ //$NON-NLS-2$
.setMinOccurrs(0);
//NESTED STUFF======================================================================
return doc;
}
@Test public void testThreeWayOrWithNestedSiblings() throws Exception {
QueryMetadataInterface metadata = TestXMLProcessor.exampleMetadataNestedWithSibling();
FakeDataManager dataMgr = TestXMLProcessor.exampleDataManagerNestedWithSibling(metadata);
String resultFile = "TestXMLPlanningEnhancements-testThreeWayOrWithNestedSiblings.xml"; //$NON-NLS-1$
String expectedDoc = TestXMLProcessor.readFile(resultFile);
TestXMLProcessor.helpTestProcess("select * from xmltest.doc9c where itemid='003' or supplierid = '600' or orderid = '1'", expectedDoc, metadata, dataMgr); //$NON-NLS-1$
}
@Test public void testBaseballPlayersDocDefect19541() throws Exception {
QueryMetadataInterface metadata = RealMetadataFactory.exampleCase3225();
FakeDataManager dataMgr = TestXMLProcessor.exampleDataManagerCase3225(metadata);
String expectedDoc =
"<?xml version=\"1.0\" encoding=\"UTF-8\"?>\r\n" + //$NON-NLS-1$
"<BaseballPlayers>\r\n" + //$NON-NLS-1$
" <Player PlayerID=\"1001\">\r\n" + //$NON-NLS-1$
" <FirstName>Albert</FirstName>\r\n" + //$NON-NLS-1$
" <LastName>Pujols</LastName>\r\n" + //$NON-NLS-1$
" <Manager ManagerID=\"1004\">\r\n" + //$NON-NLS-1$
" <FirstName>Tony</FirstName>\r\n" + //$NON-NLS-1$
" <LastName>LaRussa</LastName>\r\n" + //$NON-NLS-1$
" <Owner OwnerID=\"1009\">\r\n" + //$NON-NLS-1$
" <FirstName>Bill</FirstName>\r\n" + //$NON-NLS-1$
" <LastName>DeWitt</LastName>\r\n" + //$NON-NLS-1$
" </Owner>\r\n" + //$NON-NLS-1$
" </Manager>\r\n" + //$NON-NLS-1$
" </Player>\r\n" + //$NON-NLS-1$
" <Player PlayerID=\"1002\">\r\n" + //$NON-NLS-1$
" <FirstName>Jim</FirstName>\r\n" + //$NON-NLS-1$
" <LastName>Edmunds</LastName>\r\n" + //$NON-NLS-1$
" <Manager ManagerID=\"1004\">\r\n" + //$NON-NLS-1$
" <FirstName>Tony</FirstName>\r\n" + //$NON-NLS-1$
" <LastName>LaRussa</LastName>\r\n" + //$NON-NLS-1$
" <Owner OwnerID=\"1009\">\r\n" + //$NON-NLS-1$
" <FirstName>Bill</FirstName>\r\n" + //$NON-NLS-1$
" <LastName>DeWitt</LastName>\r\n" + //$NON-NLS-1$
" </Owner>\r\n" + //$NON-NLS-1$
" </Manager>\r\n" + //$NON-NLS-1$
" </Player>\r\n" + //$NON-NLS-1$
" <Player PlayerID=\"1003\">\r\n" + //$NON-NLS-1$
" <FirstName>David</FirstName>\r\n" + //$NON-NLS-1$
" <LastName>Eckstein</LastName>\r\n" + //$NON-NLS-1$
" <Manager ManagerID=\"1004\">\r\n" + //$NON-NLS-1$
" <FirstName>Tony</FirstName>\r\n" + //$NON-NLS-1$
" <LastName>LaRussa</LastName>\r\n" + //$NON-NLS-1$
" <Owner OwnerID=\"1009\">\r\n" + //$NON-NLS-1$
" <FirstName>Bill</FirstName>\r\n" + //$NON-NLS-1$
" <LastName>DeWitt</LastName>\r\n" + //$NON-NLS-1$
" </Owner>\r\n" + //$NON-NLS-1$
" </Manager>\r\n" + //$NON-NLS-1$
" </Player>\r\n" + //$NON-NLS-1$
"</BaseballPlayers>\r\n\r\n"; //$NON-NLS-1$
TestXMLProcessor.helpTestProcess("select * from xmltest.playersDoc where owner.@ownerid = '1009'", expectedDoc, metadata, dataMgr); //$NON-NLS-1$
}
@Test public void testNested2WithContextCriteria5c() throws Exception {
QueryMetadataInterface metadata = TestXMLProcessor.exampleMetadataCached();
FakeDataManager dataMgr = TestXMLProcessor.exampleDataManagerNested(metadata);
String resultFile = "TestXMLProcessor-testNested2WithContextCriteria5c.xml"; //$NON-NLS-1$
String expectedDoc = TestXMLProcessor.readFile(resultFile);
TestXMLProcessor.helpTestProcess("SELECT * FROM xmltest.doc9 WHERE NOT(SupplierID='52') AND (OrderID='5' OR OrderID='2')", expectedDoc, metadata, dataMgr); //$NON-NLS-1$
}
@Test public void testNested2WithContextCriteria5d() throws Exception {
QueryMetadataInterface metadata = TestXMLProcessor.exampleMetadataCached();
FakeDataManager dataMgr = TestXMLProcessor.exampleDataManagerNested(metadata);
String resultFile = "TestXMLProcessor-testNested2WithContextCriteria5d.xml"; //$NON-NLS-1$
String expectedDoc = TestXMLProcessor.readFile(resultFile);
TestXMLProcessor.helpTestProcess("SELECT * FROM xmltest.doc9 WHERE OrderID='5' OR context(SupplierID, OrderID)='2'", expectedDoc, metadata, dataMgr); //$NON-NLS-1$
}
@Test public void testNested2WithContextCriteria5d1() throws Exception {
QueryMetadataInterface metadata = TestXMLProcessor.exampleMetadataCached();
FakeDataManager dataMgr = TestXMLProcessor.exampleDataManagerNested(metadata);
String resultFile = "TestXMLProcessor-testNested2WithContextCriteria5d.xml"; //$NON-NLS-1$
String expectedDoc = TestXMLProcessor.readFile(resultFile);
TestXMLProcessor.helpTestProcess("SELECT * FROM xmltest.doc9 WHERE context(SupplierID, OrderID)='5' OR OrderID='2'", expectedDoc, metadata, dataMgr); //$NON-NLS-1$
}
@Test public void testNested2WithContextCriteria5e() throws Exception {
QueryMetadataInterface metadata = TestXMLProcessor.exampleMetadataCached();
FakeDataManager dataMgr = TestXMLProcessor.exampleDataManagerNested(metadata);
String resultFile = "TestXMLProcessor-testNested2WithContextCriteria5e.xml"; //$NON-NLS-1$
String expectedDoc = TestXMLProcessor.readFile(resultFile);
TestXMLProcessor.helpTestProcess("SELECT * FROM xmltest.doc9 WHERE OrderID='5' OR SupplierID='52'", expectedDoc, metadata, dataMgr); //$NON-NLS-1$
}
@Test public void testXQTChoice_6796() throws Exception {
QueryMetadataInterface metadata = TestXMLProcessor.exampleMetadata2();
FakeDataManager dataMgr = TestXMLProcessor.exampleXQTDataManager(metadata);
String resultFile = "TestXMLProcessor-testXQTChoice_6796.xml"; //$NON-NLS-1$
String expectedDoc = TestXMLProcessor.readFile(resultFile);
TestXMLProcessor.helpTestProcess("SELECT * FROM xqttest.doc4 WHERE root.key.keys.nestedkey = 4", expectedDoc, metadata, dataMgr); //$NON-NLS-1$
}
@Test public void testOrderByWithChoiceCriteriaElement() throws Exception {
QueryMetadataInterface metadata = TestXMLProcessor.exampleMetadata2();
FakeDataManager dataMgr = TestXMLProcessor.exampleXQTDataManager(metadata);
String expectedDoc = "<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n<root>\n <wrapper/>\n <wrapper/>\n <wrapper/>\n <wrapper/>\n <wrapper/>\n <wrapper/>\n <wrapper/>\n <wrapper/>\n <wrapper/>\n</root>"; //$NON-NLS-1$
TestXMLProcessor.helpTestProcess("SELECT * FROM xqttest.doc5 order by root.wrapper.key", expectedDoc, metadata, dataMgr); //$NON-NLS-1$
}
@Test public void testXQTChoice_withContextCriteria() throws Exception {
QueryMetadataInterface metadata = TestXMLProcessor.exampleMetadata2();
FakeDataManager dataMgr = TestXMLProcessor.exampleXQTDataManager(metadata);
String resultFile = "TestXMLProcessor-testXQTChoice_withContextCriteria.xml"; //$NON-NLS-1$
String expectedDoc = TestXMLProcessor.readFile(resultFile);
TestXMLProcessor.helpTestProcess("SELECT * FROM xqttest.doc4 WHERE context(root.key.keys.nestedkey, root.key.keys.nestedkey) = 4", expectedDoc, metadata, dataMgr); //$NON-NLS-1$
}
@Test public void testXQTChoice_withContextCriteria1() throws Exception {
QueryMetadataInterface metadata = TestXMLProcessor.exampleMetadata2();
FakeDataManager dataMgr = TestXMLProcessor.exampleXQTDataManager(metadata);
String resultFile = "TestXMLProcessor-testXQTChoice_withContextCriteria1.xml"; //$NON-NLS-1$
String expectedDoc = TestXMLProcessor.readFile(resultFile);
TestXMLProcessor.helpTestProcess("SELECT * FROM xqttest.doc4 WHERE context(root.key.keys.nestedkey, root.key.keys.nestedkey) = 4 and context(root.wrapper.key.keys.nestedkey, root.wrapper.key.keys.nestedkey) = 3", expectedDoc, metadata, dataMgr); //$NON-NLS-1$
}
@Test public void testMappingClassWithInlineViewAndCriteria() throws Exception {
QueryMetadataInterface metadata = getMetadata("SELECT upper(x.supplierNum) as supplierNum, x.supplierName, x.supplierZipCode from (select stock.suppliers.supplierNum, supplierName, supplierZipCode, itemNum FROM stock.suppliers, stock.item_supplier WHERE stock.suppliers.supplierNum = stock.item_supplier.supplierNum) x where x.itemNum = ?"); //$NON-NLS-1$
FakeDataManager dataMgr = TestXMLProcessor.exampleDataManagerNested(metadata);
String expectedDoc = TestXMLProcessor.readFile("TestXMLPlanningEnhancements-testMappingClassWithStoredProcedureAndCriteria.xml"); //$NON-NLS-1$
TestXMLProcessor.helpTestProcess("SELECT * FROM xmltest.doc18a where supplierID<56", expectedDoc, metadata, dataMgr); //$NON-NLS-1$
}
@Test public void testMappingClassWithUnionAndCriteria() throws Exception {
QueryMetadataInterface metadata = getMetadata("SELECT concat(stock.suppliers.supplierNum, '') as supplierNum, supplierName, supplierZipCode FROM stock.suppliers, stock.item_supplier WHERE stock.suppliers.supplierNum = stock.item_supplier.supplierNum AND stock.item_supplier.itemNum = ? union all SELECT concat(stock.suppliers.supplierNum, '1'), supplierName, convert(12345, string) FROM stock.suppliers WHERE stock.suppliers.supplierNum = ?"); //$NON-NLS-1$
FakeDataManager dataMgr = TestXMLProcessor.exampleDataManagerNested(metadata);
String expectedDoc = TestXMLProcessor.readFile("TestXMLPlanningEnhancements-testMappingClassWithStoredProcedureAndCriteria.xml"); //$NON-NLS-1$
TestXMLProcessor.helpTestProcess("SELECT * FROM xmltest.doc18a where supplierID<56", expectedDoc, metadata, dataMgr); //$NON-NLS-1$
}
@Test public void testMappingClassWithInputSetElementNameConflict() throws Exception {
QueryMetadataInterface metadata = getMetadata("SELECT concat(stock.suppliers.supplierNum, '') as supplierNum, supplierName, supplierZipCode FROM stock.suppliers, stock.item_supplier WHERE stock.suppliers.supplierNum = stock.item_supplier.supplierNum AND stock.item_supplier.supplierNum = ?"); //$NON-NLS-1$
FakeDataManager dataMgr = TestXMLProcessor.exampleDataManagerNested(metadata);
String expectedDoc = "<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n<Catalogs>\n<Catalog>\n<Items/>\n</Catalog>\n</Catalogs>"; //$NON-NLS-1$
TestXMLProcessor.helpTestProcess("SELECT * FROM xmltest.doc18a where supplierID<56", expectedDoc, metadata, dataMgr); //$NON-NLS-1$
}
@Ignore("Will plan with a dependent join")
@Test public void testAutoStagingFailsForMappingClassWithProcRelational() throws Exception {
TransformationMetadata metadata = getMetadata("SELECT supplierNum, supplierName, supplierZipCode FROM v1.supplierProc where itemnum = ?"); //$NON-NLS-1$
Schema v1 = RealMetadataFactory.createVirtualModel("v1", metadata.getMetadataStore()); //$NON-NLS-1$
ColumnSet<Procedure> rs1 = RealMetadataFactory.createResultSet("v1.rs1", new String[] {"supplierNum", "supplierName", "supplierZipCode"}, new String[] { DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.STRING,DataTypeManager.DefaultDataTypes.STRING }); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$
ProcedureParameter rs1p2 = RealMetadataFactory.createParameter("itemNum", ParameterInfo.IN, DataTypeManager.DefaultDataTypes.STRING); //$NON-NLS-1$
QueryNode n1 = new QueryNode("CREATE VIRTUAL PROCEDURE BEGIN SELECT concat(stock.suppliers.supplierNum, '') as supplierNum, supplierName, supplierZipCode FROM stock.suppliers, stock.item_supplier WHERE stock.suppliers.supplierNum = stock.item_supplier.supplierNum AND stock.item_supplier.itemNum = v1.supplierProc.itemNum; END"); //$NON-NLS-1$ //$NON-NLS-2$
Procedure vt1 = RealMetadataFactory.createVirtualProcedure("supplierProc", v1, Arrays.asList(rs1p2), n1); //$NON-NLS-1$
vt1.setResultSet(rs1);
FakeDataManager dataMgr = TestXMLProcessor.exampleDataManagerNested(metadata);
String expectedDoc = TestXMLProcessor.readFile("TestXMLPlanningEnhancements-testMappingClassWithStoredProcedureAndCriteria.xml"); //$NON-NLS-1$
XMLPlan plan = (XMLPlan)TestXMLProcessor.helpTestProcess("SELECT * FROM xmltest.doc18a where supplierID<56", expectedDoc, metadata, dataMgr); //$NON-NLS-1$
Map<Class<?>, List<ProcessorInstruction>> stats = XMLProgramUtil.getProgramStats(plan.getOriginalProgram());
assertNull(stats.get(ExecStagingTableInstruction.class));
}
@Test public void testMappingClassWitSelectDistinctAndCriteria() throws Exception {
QueryMetadataInterface metadata = getMetadata("SELECT distinct '1' as supplierNum, '2' as supplierName, '3' as supplierZipCode FROM stock.suppliers, stock.item_supplier WHERE stock.suppliers.supplierNum = stock.item_supplier.supplierNum AND stock.item_supplier.itemNum like substring(?,1,1) || '%'"); //$NON-NLS-1$
FakeDataManager dataMgr = TestXMLProcessor.exampleDataManagerNested(metadata);
String expectedDoc = TestXMLProcessor.readFile("TestXMLPlanningEnhancements-testMappingClassWitSelectDistinctAndCriteria.xml"); //$NON-NLS-1$
TestXMLProcessor.helpTestProcess("SELECT * FROM xmltest.doc18a", expectedDoc, metadata, dataMgr); //$NON-NLS-1$
}
/**
* based upon testNestedWithStoredQueryInMappingClass
*
* Ensures that correlated references to outer scoped groups can
* be used as inputs
*/
@Test public void testMappingClassWithStoredProcedureAndCriteria() throws Exception {
QueryMetadataInterface metadata = TestXMLProcessor.exampleMetadataCached();
FakeDataManager dataMgr = TestXMLProcessor.exampleDataManagerNested(metadata);
String expectedDoc = TestXMLProcessor.readFile("TestXMLPlanningEnhancements-testMappingClassWithStoredProcedureAndCriteria.xml"); //$NON-NLS-1$
TestXMLProcessor.helpTestProcess("SELECT * FROM xmltest.doc18 where supplierID<56", expectedDoc, metadata, dataMgr); //$NON-NLS-1$
}
public void defer_testXMLQueryWithFalseRootCriteria() throws Exception {
QueryMetadataInterface metadata = TestXMLProcessor.exampleMetadataCached();
FakeDataManager dataMgr = TestXMLProcessor.exampleDataManagerNested(metadata);
String expectedDoc = ""; //$NON-NLS-1$
TestXMLProcessor.helpTestProcess("SELECT * FROM xmltest.doc18 where 1 = 0", expectedDoc, metadata, dataMgr); //$NON-NLS-1$
}
/**
* @see #testNested2WithCriteria2
*/
@Test public void testAutoStagingByCosting() throws Exception {
TransformationMetadata metadata = TestXMLProcessor.exampleMetadata();
FakeDataManager dataMgr = TestXMLProcessor.exampleDataManagerNested(metadata);
Table suppliers = metadata.getGroupID("stock.suppliers"); //$NON-NLS-1$
Table itemSuppliers = metadata.getGroupID("stock.item_supplier"); //$NON-NLS-1$
suppliers.setCardinality(10);
itemSuppliers.setCardinality(10);
String expectedDoc = TestXMLProcessor.readFile("TestXMLProcessor-FullSuppliers.xml"); //$NON-NLS-1$
XMLPlan plan = (XMLPlan)TestXMLProcessor.helpTestProcess("SELECT * FROM xmltest.doc9", expectedDoc, metadata, dataMgr); //$NON-NLS-1$
//check for staging; one for staging and for unloading
Map<Class<?>, List<ProcessorInstruction>> stats = XMLProgramUtil.getProgramStats(plan.getOriginalProgram());
assertEquals(2, stats.get(ExecStagingTableInstruction.class).size());
}
/**
* @see #testNested2WithCriteria2
*/
@Ignore("Will use a dependent join instead")
@Test public void testAutoStagingFailsByCosting() throws Exception {
TransformationMetadata metadata = TestXMLProcessor.exampleMetadata();
FakeDataManager dataMgr = TestXMLProcessor.exampleDataManagerNested(metadata);
Table suppliers = metadata.getGroupID("stock.suppliers"); //$NON-NLS-1$
Table itemSuppliers = metadata.getGroupID("stock.item_supplier"); //$NON-NLS-1$
suppliers.setCardinality(10000);
itemSuppliers.setCardinality(10000);
String expectedDoc = TestXMLProcessor.readFile("TestXMLProcessor-FullSuppliers.xml"); //$NON-NLS-1$
XMLPlan plan = (XMLPlan)TestXMLProcessor.helpTestProcess("SELECT * FROM xmltest.doc9", expectedDoc, metadata, dataMgr); //$NON-NLS-1$
//check for no staging
Map<Class<?>, List<ProcessorInstruction>> stats = XMLProgramUtil.getProgramStats(plan.getOriginalProgram());
assertNull(stats.get(ExecStagingTableInstruction.class));
}
@Ignore("Will use dependent join instead")
@Test public void testAutoStagingFailsByNoCache() throws Exception {
QueryMetadataInterface metadata = TestXMLProcessor.exampleMetadataCached();
FakeDataManager dataMgr = TestXMLProcessor.exampleDataManagerNested(metadata);
String expectedDoc = TestXMLProcessor.readFile("TestXMLProcessor-FullSuppliers.xml"); //$NON-NLS-1$
XMLPlan plan = (XMLPlan)TestXMLProcessor.helpTestProcess("SELECT * FROM xmltest.doc9 OPTION NOCACHE", expectedDoc, metadata, dataMgr); //$NON-NLS-1$
//check for no staging
Map<Class<?>, List<ProcessorInstruction>> stats = XMLProgramUtil.getProgramStats(plan.getOriginalProgram());
assertNull(stats.get(ExecStagingTableInstruction.class));
}
@Ignore("Will use dependent join")
@Test public void testAutoStagingFailsByNoCacheByGroup() throws Exception {
QueryMetadataInterface metadata = TestXMLProcessor.exampleMetadataCached();
FakeDataManager dataMgr = TestXMLProcessor.exampleDataManagerNested(metadata);
String expectedDoc = TestXMLProcessor.readFile("TestXMLProcessor-FullSuppliers.xml"); //$NON-NLS-1$
XMLPlan plan = (XMLPlan)TestXMLProcessor.helpTestProcess("SELECT * FROM xmltest.doc9 OPTION NOCACHE XMLTEST.SUPPLIERS", expectedDoc, metadata, dataMgr); //$NON-NLS-1$
//check for no staging by the mapping class name
Map<Class<?>, List<ProcessorInstruction>> stats = XMLProgramUtil.getProgramStats(plan.getOriginalProgram());
assertNull(stats.get(ExecStagingTableInstruction.class));
plan = (XMLPlan)TestXMLProcessor.helpTestProcess("SELECT * FROM xmltest.doc9 OPTION NOCACHE XMLTEST.SUPPLIERS", expectedDoc, metadata, dataMgr); //$NON-NLS-1$
//check for no staging by the alias mapping class name
stats = XMLProgramUtil.getProgramStats(plan.getOriginalProgram());
assertNull(stats.get(ExecStagingTableInstruction.class));
}
// see the next test with costing information too.
@Test public void testUseOfStagingCardinalityOnDependentJoinsNoCost() throws Exception {
QueryMetadataInterface metadata = getTempTableMetadata(false);
FakeDataManager dataMgr = TestXMLProcessor.exampleDataManagerNested(metadata);
String expectedDoc = TestXMLProcessor.readFile("TestXMLProcessor-FullSuppliers.xml"); //$NON-NLS-1$
XMLPlan xmlPlan = (XMLPlan)TestXMLProcessor.helpTestProcess("SELECT * FROM xmltest.docJoin", expectedDoc, metadata, dataMgr); //$NON-NLS-1$
Map<Class<?>, List<ProcessorInstruction>> stats = XMLProgramUtil.getProgramStats(xmlPlan.getOriginalProgram());
List<ProcessorInstruction> list = stats.get(ExecSqlInstruction.class);
ExecSqlInstruction instr = (ExecSqlInstruction)list.get(2);
ProcessorPlan plan = instr.info.getPlan();
TestOptimizer.checkNodeTypes(plan, new int[] {
2, // Access
0, // DependentAccess
0, // DependentSelect
0, // DependentProject
0, // DupRemove
0, // Grouping
0, // Join
1, // MergeJoin (**We are merge join with out any costing info**)
0, // Null
0, // PlanExecution
1, // Project
1, // Select
0, // Sort
0 // UnionAll
});
TestOptimizer.checkDependentJoinCount(plan, 1);
}
@Test public void testUseOfStagingCardinalityOnDependentJoinsWithCost() throws Exception {
TransformationMetadata metadata = getTempTableMetadata(false);
FakeDataManager dataMgr = TestXMLProcessor.exampleDataManagerNested(metadata);
Table orders = metadata.getGroupID("stock.orders"); //$NON-NLS-1$
Table suppliers = metadata.getGroupID("stock.suppliers"); //$NON-NLS-1$
orders.setCardinality(BufferManager.DEFAULT_PROCESSOR_BATCH_SIZE - 1);
suppliers.setCardinality(RuleChooseDependent.DEFAULT_INDEPENDENT_CARDINALITY - 1);
String expectedDoc = TestXMLProcessor.readFile("TestXMLProcessor-FullSuppliers.xml"); //$NON-NLS-1$
XMLPlan xmlPlan = (XMLPlan)TestXMLProcessor.helpTestProcess("SELECT * FROM xmltest.docJoin", metadata, dataMgr, null, TestOptimizer.getGenericFinder(false), expectedDoc); //$NON-NLS-1$
Map<Class<?>, List<ProcessorInstruction>> stats = XMLProgramUtil.getProgramStats(xmlPlan.getOriginalProgram());
List<ProcessorInstruction> list = stats.get(ExecSqlInstruction.class);
ExecSqlInstruction instr = (ExecSqlInstruction)list.get(2);
ProcessorPlan plan = instr.info.getPlan();
TestOptimizer.checkNodeTypes(plan, new int[] {
1, // Access
1, // DependentAccess
0, // DependentSelect
0, // DependentProject
0, // DupRemove
0, // Grouping
0, // Join
1, // MergeJoin (**We are merge join with out any costing info**)
0, // Null
0, // PlanExecution
1, // Project
0, // Select
0, // Sort
0 // UnionAll
});
TestOptimizer.checkDependentJoinCount(plan, 1);
}
@Test public void testNoRedundentStagingTables() throws Exception {
TransformationMetadata metadata = getTempTableMetadata(true);
FakeDataManager dataMgr = TestXMLProcessor.exampleDataManagerNested(metadata);
Table suppliers = metadata.getGroupID("stock.suppliers"); //$NON-NLS-1$
Table orders = metadata.getGroupID("stock.orders"); //$NON-NLS-1$
suppliers.setCardinality(10);
orders.setCardinality(10);
String expectedDoc = TestXMLProcessor.readFile("TestXMLProcessor-OnlySupplier51.xml"); //$NON-NLS-1$
XMLPlan xmlPlan = (XMLPlan)TestXMLProcessor.helpTestProcess("SELECT * FROM xmltest.docJoin where context(Supplier, SupplierID) = 51", expectedDoc, metadata, dataMgr); //$NON-NLS-1$
//check for staging; one for staging and for unloading - only two pairs are expected
Map<Class<?>, List<ProcessorInstruction>> stats = XMLProgramUtil.getProgramStats(xmlPlan.getOriginalProgram());
assertEquals(4, stats.get(ExecStagingTableInstruction.class).size());
}
}