Java源码示例：org.pentaho.metadata.query.model.util.QueryXmlHelper
示例1
@Test
public void testQueryXmlSerialization() throws PentahoMetadataException {
LogicalModel model = domain.findLogicalModel( "MODEL_1" );
Query query = new Query( domain, model );
Category category = model.findCategory( Settings.getBusinessCategoryIDPrefix() + "newdatasource" );
LogicalColumn column = category.findLogicalColumn( "bc_CUSTOMERNAME" );
query.getSelections().add( new Selection( category, column, null ) );
query.getConstraints().add( new Constraint( CombinationType.AND, "[CATEGORY.bc_CUSTOMERNAME] = \"bob\"" ) );
query.getOrders().add( new Order( new Selection( category, column, null ), Order.Type.ASC ) );
QueryXmlHelper helper = new QueryXmlHelper();
String xml = helper.toXML( query );
InMemoryMetadataDomainRepository repo = new InMemoryMetadataDomainRepository();
try {
repo.storeDomain( domain, true );
} catch ( Exception e ) {
e.printStackTrace();
fail();
}
Query newQuery = null;
newQuery = helper.fromXML( repo, xml );
// verify that when we serialize and deserialize, the xml stays the same.
assertEquals( xml, helper.toXML( newQuery ) );
}
示例2
public String saveQuery( MqlQuery query ) {
if ( query.getColumns().isEmpty() ) {
// UI allowed user to create a query without columns
throw new RuntimeException( "query is not valid without columns" ); //$NON-NLS-1$
}
if ( domainRepository != null ) {
org.pentaho.metadata.model.Domain thinDomain = domainRepository.getDomain( query.getDomain().getName() );
if ( thinDomain != null ) {
org.pentaho.metadata.query.model.Query queryModel = convertQueryModel( thinDomain, query );
return new QueryXmlHelper().toXML( queryModel );
}
}
MQLQuery fatQ = convertModel( query );
if ( fatQ != null ) {
return fatQ.getXML();
} else {
return "";
}
}
示例3
@Test
public void testComplexJoinsInXmi() throws Exception {
// This unit test loads an XMI domain containing
// a complex join, and also executes a basic query
// verifying that the complex join is resolved.
Domain domain = parser.parseXmi( getClass().getResourceAsStream( "/samples/complex_join.xmi" ) );
domain.setId( "test domain" );
assertTrue( domain.getLogicalModels().get( 0 ).getLogicalRelationships().get( 0 ).isComplex() );
assertEquals( "[BT_ORDERS_ORDERS.BC_ORDERS_ORDERNUMBER]=[BT_ORDERFACT_ORDERFACT.BC_ORDERFACT_ORDERNUMBER]",
domain.getLogicalModels().get( 0 ).getLogicalRelationships().get( 0 ).getComplexJoin() );
String mql =
"<mql>" + "<domain_type>relational</domain_type>" + "<domain_id>test domain</domain_id>"
+ "<model_id>BV_MODEL_1</model_id>" + "<model_name>Model 1</model_name>" + "<options>"
+ " <disable_distinct>false</disable_distinct>" + "</options>" + "<selections>" + " <selection>"
+ " <view>BC_ORDERS</view>" + " <column>BC_ORDERS_STATUS</column>"
+ " <aggregation>none</aggregation>" + " </selection>" + " <selection>"
+ " <view>BC_ORDERFACT</view>" + " <column>BC_ORDERFACT_PRODUCTCODE</column>"
+ " <aggregation>none</aggregation>" + " </selection>" + "</selections>" + "<constraints/>"
+ "<orders/>" + "</mql>";
InMemoryMetadataDomainRepository repo = new InMemoryMetadataDomainRepository();
repo.storeDomain( domain, false );
QueryXmlHelper helper = new QueryXmlHelper();
Query query = helper.fromXML( repo, mql );
SqlGenerator generator = new SqlGenerator();
DatabaseMeta databaseMeta = new DatabaseMeta( "", "ORACLE", "Native", "", "", "", "", "" ); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$ //$NON-NLS-6$ //$NON-NLS-7$ //$NON-NLS-8$
MappedQuery queryObj = generator.generateSql( query, "en_US", repo, databaseMeta );
// TestHelper.printOutJava(queryObj.getQuery());
TestHelper
.assertEqualsIgnoreWhitespaces( "SELECT DISTINCT \n" + " BT_ORDERS_ORDERS.STATUS AS COL0\n"
+ " ,BT_ORDERFACT_ORDERFACT.PRODUCTCODE AS COL1\n" + "FROM \n"
+ " ORDERFACT BT_ORDERFACT_ORDERFACT\n" + " ,ORDERS BT_ORDERS_ORDERS\n" + "WHERE \n"
+ " ( BT_ORDERS_ORDERS.ORDERNUMBER = BT_ORDERFACT_ORDERFACT.ORDERNUMBER )\n", queryObj
.getQuery() );
}
示例4
@Test
public void testMqlDateParams() throws Exception {
Domain steelWheelsDomain = new XmiParser().parseXmi( getClass().getResourceAsStream( "/steel-wheels.xmi" ) );
String mql =
"<?xml version=\"1.0\" encoding=\"UTF-8\"?>" + "<mql>" + "<domain_id>Steel-Wheels</domain_id>"
+ "<model_id>BV_ORDERS</model_id>" + "<options>" + "<disable_distinct>false</disable_distinct>"
+ "</options>" + "<parameters>" + "<parameter defaultValue=\"2004-01-01\" name=\"date\" type=\"STRING\"/>"
+ "</parameters>" + "<selections>" + "<selection>" + "<view>BC_CUSTOMER_W_TER_</view>"
+ "<column>BC_CUSTOMER_W_TER_CUSTOMERNUMBER</column>" + "<aggregation>NONE</aggregation>" + "</selection>"
+ "<selection>" + "<view>CAT_ORDERS</view>" + "<column>BC_ORDERS_ORDERDATE</column>"
+ "<aggregation>NONE</aggregation>" + "</selection>" + "</selections>" + "<constraints>" + "<constraint>"
+ "<operator/>" + "<condition>[CAT_ORDERS.BC_ORDERS_ORDERDATE] "
+ ">DATEVALUE([param:date])</condition>" + "</constraint>" + "</constraints>" + "<orders/>" + "</mql>";
QueryXmlHelper helper = new QueryXmlHelper();
InMemoryMetadataDomainRepository repo = new InMemoryMetadataDomainRepository();
steelWheelsDomain.setId( "Steel-Wheels" );
repo.storeDomain( steelWheelsDomain, false );
Query query = helper.fromXML( repo, mql );
DatabaseMeta databaseMeta = new DatabaseMeta( "", "ORACLE", "Native", "", "", "", "", "" ); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$ //$NON-NLS-6$ //$NON-NLS-7$
SqlGenerator generator = new SqlGenerator();
MappedQuery mappedQuery = generator.generateSql( query, "en_US", repo, databaseMeta );
TestHelper.assertEqualsIgnoreWhitespaces( "SELECT DISTINCT \n"
+ " BT_CUSTOMER_W_TER_CUSTOMER_W01.CUSTOMERNUMBER AS COL0\n"
+ " ,BT_ORDERS_ORDERS.ORDERDATE AS COL1\n" + "FROM \n"
+ " CUSTOMER_W_TER BT_CUSTOMER_W_TER_CUSTOMER_W01\n" + " ,ORDERS BT_ORDERS_ORDERS\n"
+ "WHERE \n"
+ " ( BT_ORDERS_ORDERS.CUSTOMERNUMBER = BT_CUSTOMER_W_TER_CUSTOMER_W01.CUSTOMERNUMBER )\n"
+ " AND \n" + " (\n" + " (\n"
+ " BT_ORDERS_ORDERS.ORDERDATE > TO_DATE('2004-01-01','YYYY-MM-DD')\n" + " )\n"
+ " )\n", mappedQuery.getQuery() );
}
示例5
public void setQuery( String query ) throws PentahoMetadataException {
if ( query == null ) {
mainController.clearWorkspace();
} else {
QueryXmlHelper helper = new QueryXmlHelper();
Query queryObject = helper.fromXML( repo, query );
org.pentaho.commons.metadata.mqleditor.beans.Query thinQuery =
(org.pentaho.commons.metadata.mqleditor.beans.Query) this.delegate.convertModelToThin( queryObject );
mainController.setSavedQuery( thinQuery );
}
}
示例6
@Test
public void testMqlDateParams_with_Date_object() throws Exception {
Domain steelWheelsDomain = new XmiParser().parseXmi( getClass().getResourceAsStream( "/steel-wheels.xmi" ) );
String mql =
"<?xml version=\"1.0\" encoding=\"UTF-8\"?>" + "<mql>" + "<domain_id>Steel-Wheels</domain_id>"
+ "<model_id>BV_ORDERS</model_id>" + "<options>" + "<disable_distinct>false</disable_distinct>"
+ "</options>" + "<parameters>" + "<parameter defaultValue=\"2004-01-01\" name=\"date\" type=\"STRING\"/>"
+ "</parameters>" + "<selections>" + "<selection>" + "<view>BC_CUSTOMER_W_TER_</view>"
+ "<column>BC_CUSTOMER_W_TER_CUSTOMERNUMBER</column>" + "<aggregation>NONE</aggregation>" + "</selection>"
+ "<selection>" + "<view>CAT_ORDERS</view>" + "<column>BC_ORDERS_ORDERDATE</column>"
+ "<aggregation>NONE</aggregation>" + "</selection>" + "</selections>" + "<constraints>" + "<constraint>"
+ "<operator/>" + "<condition>[CAT_ORDERS.BC_ORDERS_ORDERDATE] "
+ ">DATEVALUE([param:date])</condition>" + "</constraint>" + "</constraints>" + "<orders/>" + "</mql>";
QueryXmlHelper helper = new QueryXmlHelper();
InMemoryMetadataDomainRepository repo = new InMemoryMetadataDomainRepository();
steelWheelsDomain.setId( "Steel-Wheels" );
repo.storeDomain( steelWheelsDomain, false );
Query query = helper.fromXML( repo, mql );
DatabaseMeta databaseMeta = new DatabaseMeta( "", "ORACLE", "Native", "", "", "", "", "" ); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$ //$NON-NLS-6$ //$NON-NLS-7$
SqlGenerator generator = new SqlGenerator();
Map<String, Object> parameters = new HashMap<String, Object>();
Date now = new Date();
parameters.put( "date", now );
MappedQuery mappedQuery = generator.generateSql( query, "en_US", repo, databaseMeta, parameters, false );
SimpleDateFormat sdf = new SimpleDateFormat( "yyyy-MM-dd" );
String nowAsString = sdf.format( now );
TestHelper.assertEqualsIgnoreWhitespaces( "SELECT DISTINCT \n"
+ " BT_CUSTOMER_W_TER_CUSTOMER_W01.CUSTOMERNUMBER AS COL0\n"
+ " ,BT_ORDERS_ORDERS.ORDERDATE AS COL1\n" + "FROM \n"
+ " CUSTOMER_W_TER BT_CUSTOMER_W_TER_CUSTOMER_W01\n" + " ,ORDERS BT_ORDERS_ORDERS\n"
+ "WHERE \n"
+ " ( BT_ORDERS_ORDERS.CUSTOMERNUMBER = BT_CUSTOMER_W_TER_CUSTOMER_W01.CUSTOMERNUMBER )\n"
+ " AND \n" + " (\n" + " (\n" + " BT_ORDERS_ORDERS.ORDERDATE > TO_DATE('"
+ nowAsString + "','YYYY-MM-DD')\n" + " )\n" + " )\n", mappedQuery.getQuery() );
}
示例7
@Test
public void testMqlConstraints() throws Exception {
Domain steelWheelsDomain = new XmiParser().parseXmi( getClass().getResourceAsStream( "/steel-wheels.xmi" ));
String mql =
"<?xml version=\"1.0\" encoding=\"UTF-8\"?>" + "<mql>" + "<domain_id>Steel-Wheels</domain_id>"
+ "<model_id>BV_ORDERS</model_id>" + "<options>" + "<disable_distinct>false</disable_distinct>"
+ "</options>" + "<selections>" + "<selection>" + "<view>BC_CUSTOMER_W_TER_</view>"
+ "<column>BC_CUSTOMER_W_TER_CUSTOMERNUMBER</column>" + "<aggregation>NONE</aggregation>" + "</selection>"
+ "<selection>" + "<view>CAT_ORDERS</view>" + "<column>BC_ORDERS_ORDERDATE</column>"
+ "<aggregation>NONE</aggregation>" + "</selection>" + "</selections>" + "<constraints>" + "<constraint>"
+ "<operator/>" + "<condition>[CAT_ORDERS.BC_ORDERS_ORDERDATE] "
+ ">DATEVALUE(\"2009-12-12\")</condition>" + "</constraint>" + "<constraint>"
+ "<operator>AND NOT</operator>" + "<condition>[CAT_ORDERS.BC_ORDERS_ORDERDATE] "
+ "<DATEVALUE(\"2009-12-13\")</condition>" + "</constraint>" + "</constraints>" + "<orders/>" + "</mql>";
QueryXmlHelper helper = new QueryXmlHelper();
InMemoryMetadataDomainRepository repo = new InMemoryMetadataDomainRepository();
steelWheelsDomain.setId( "Steel-Wheels" );
repo.storeDomain( steelWheelsDomain, false );
Query query = helper.fromXML( repo, mql );
DatabaseMeta databaseMeta = new DatabaseMeta( "", "ORACLE", "Native", "", "", "", "", "" ); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$ //$NON-NLS-6$ //$NON-NLS-7$
SqlGenerator generator = new SqlGenerator();
MappedQuery mappedQuery = generator.generateSql( query, "en_US", repo, databaseMeta );
// TestHelper.printOutJava(mappedQuery.getQuery());
TestHelper.assertEqualsIgnoreWhitespaces( "SELECT DISTINCT \n"
+ " BT_CUSTOMER_W_TER_CUSTOMER_W01.CUSTOMERNUMBER AS COL0\n"
+ " ,BT_ORDERS_ORDERS.ORDERDATE AS COL1\n" + "FROM \n"
+ " CUSTOMER_W_TER BT_CUSTOMER_W_TER_CUSTOMER_W01\n" + " ,ORDERS BT_ORDERS_ORDERS\n"
+ "WHERE \n"
+ " ( BT_ORDERS_ORDERS.CUSTOMERNUMBER = BT_CUSTOMER_W_TER_CUSTOMER_W01.CUSTOMERNUMBER )\n"
+ " AND \n" + " (\n" + " (\n"
+ " BT_ORDERS_ORDERS.ORDERDATE > TO_DATE('2009-12-12','YYYY-MM-DD')\n" + " )\n"
+ " AND NOT (\n" + " BT_ORDERS_ORDERS.ORDERDATE < TO_DATE('2009-12-13','YYYY-MM-DD')\n"
+ " )\n" + " )\n", mappedQuery.getQuery() );
}
