/**
* In this test we try to test :<br>
* - if the formula engine picks the 2 specified columns from 2 different business tables<br>
* - if we calculate the multiplication of the sums <br>
*/
public void testMultiTableColumnFormulasAggregate2() {
BusinessColumn quantityOrdered =
ordersModel.findBusinessColumn("BC_ORDER_DETAILS_QUANTITYORDERED");
assertNotNull("Expected to find the business column 'quantity ordered'", quantityOrdered);
BusinessColumn buyPrice = ordersModel.findBusinessColumn("BC_PRODUCTS_BUYPRICE");
assertNotNull("Expected to find the business column 'buy price'", buyPrice);
// let's enable the aggregations of the quantity ordered...
//
AggregationSettings qaBackup = quantityOrdered.getAggregationType();
AggregationSettings paBackup = buyPrice.getAggregationType();
quantityOrdered.setAggregationType(AggregationSettings.SUM);
buyPrice.setAggregationType(AggregationSettings.SUM);
// This changes the expected result...
//
String formula =
"[BT_ORDER_DETAILS.BC_ORDER_DETAILS_QUANTITYORDERED] * [BT_PRODUCTS.BC_PRODUCTS_BUYPRICE]";
String sql = "SUM(BT_ORDER_DETAILS.QUANTITYORDERED) * SUM(BT_PRODUCTS.BUYPRICE)";
handleFormula(ordersModel, "Hypersonic", formula, sql);
// Set it back to the way it was for further testing.
quantityOrdered.setAggregationType(qaBackup);
buyPrice.setAggregationType(paBackup);
}
public void testToFromXML() throws PentahoMetadataException {
String mqlfile1 = "test-res/mqlquery01.xmql"; // $NON-NLS-1$
String mqlfile2 =
"test-res/mqlquery02.xmql"; //$NON-NLS-1$ // Distinct is off, but otherwise the same
String mqlfile3 = "test-res/mqlquery_oldformat.xmql"; // $NON-NLS-1$
String mqlfile4 = "test-res/mqlquery03.xmql"; // $NON-NLS-1$
String mqlfile5 = "test-res/mqlquery04.xmql"; // $NON-NLS-1$
String mqldata = loadXmlFile(mqlfile1);
assertNotNull(mqldata);
MQLQuery mqlquery = null;
mqlquery = MQLQueryFactory.getMQLQuery(mqldata, null, "en_US", cwmSchemaFactory); // $NON-NLS-1$
// Tests parsing the options tag to get the boolean into the MQL query
mqldata = null;
mqldata = loadXmlFile(mqlfile2);
assertNotNull(mqldata);
MQLQuery mqlquery2 = null;
mqlquery2 =
MQLQueryFactory.getMQLQuery(mqldata, null, "en_US", cwmSchemaFactory); // $NON-NLS-1$
assertEquals(((MQLQueryImpl) mqlquery2).getDisableDistinct(), true);
// Tests parsing of conditions
mqldata = null;
mqldata = loadXmlFile(mqlfile4);
assertNotNull(mqldata);
MQLQuery mqlquery4 = null;
mqlquery4 =
MQLQueryFactory.getMQLQuery(mqldata, null, "en_US", cwmSchemaFactory); // $NON-NLS-1$
assertEquals(mqlquery4.getConstraints().size(), 1);
// Tests parsing an old-format (without options tag) still works, and defaults disableDistinct
// to false
mqldata = null;
mqldata = loadXmlFile(mqlfile3);
assertNotNull(mqldata);
MQLQuery mqlquery3 = null;
mqlquery3 =
MQLQueryFactory.getMQLQuery(mqldata, null, "en_US", cwmSchemaFactory); // $NON-NLS-1$
assertEquals(((MQLQueryImpl) mqlquery3).getDisableDistinct(), false);
assertTrue(((MQLQueryImpl) mqlquery3).getLimit() < 0);
// Tests parsing an old-format (without options tag) still works, and defaults disableDistinct
// to false
mqldata = null;
mqldata = loadXmlFile(mqlfile5);
assertNotNull(mqldata);
MQLQuery mqlquery5 = null;
mqlquery5 =
MQLQueryFactory.getMQLQuery(mqldata, null, "en_US", cwmSchemaFactory); // $NON-NLS-1$
assertEquals(10, ((MQLQueryImpl) mqlquery5).getLimit());
assertNotNull(mqlquery);
assertNotNull(((MQLQueryImpl) mqlquery).getSchemaMeta());
assertEquals(
"Orders", ((MQLQueryImpl) mqlquery).getSchemaMeta().getDomainName()); // $NON-NLS-1$
assertNotNull(mqlquery.getModel());
assertEquals("Orders", mqlquery.getModel().getId()); // $NON-NLS-1$
List<? extends Selection> selections = mqlquery.getSelections();
assertNotNull(selections);
assertEquals(2, selections.size());
BusinessColumn col1 = selections.get(0).getBusinessColumn();
assertEquals("BT_CUSTOMERS", col1.getBusinessTable().getId()); // $NON-NLS-1$
assertEquals("BC_CUSTOMERS_CUSTOMERNAME", col1.getId()); // $NON-NLS-1$
BusinessColumn col2 = selections.get(1).getBusinessColumn();
assertEquals("BT_CUSTOMERS", col2.getBusinessTable().getId()); // $NON-NLS-1$
assertEquals("BC_CUSTOMERS_COUNTRY", col2.getId()); // $NON-NLS-1$
List orders = ((MQLQueryImpl) mqlquery).getOrder();
assertNotNull(orders);
assertEquals(1, orders.size());
assertTrue(orders.get(0) instanceof OrderBy);
OrderBy order = (OrderBy) orders.get(0);
assertEquals(true, order.isAscending());
assertNotNull(order.getSelection().getBusinessColumn());
assertEquals(
"BT_CUSTOMERS",
order.getSelection().getBusinessColumn().getBusinessTable().getId()); // $NON-NLS-1$
assertEquals(
"BC_CUSTOMERS_COUNTRY", order.getSelection().getBusinessColumn().getId()); // $NON-NLS-1$
// NOW TEST XML OUTPUT
try {
File file = new File(mqlfile1);
FileInputStream fileInputStream = new FileInputStream(file);
byte[] bytes = new byte[(int) file.length()];
fileInputStream.read(bytes);
fileInputStream.close();
String data = new String(bytes, Const.XML_ENCODING);
// remove the <?xml version="1.0" encoding="UTF-8"?>, it appears differently in different JVM
// versions
data = data.replaceAll("<\\?.*\\?>", ""); // $NON-NLS-1$ //$NON-NLS-2$
data = data.replaceAll("[\r\n\t]", ""); // $NON-NLS-1$ //$NON-NLS-2$
String xml = mqlquery.getXML();
assertNotNull(xml);
// remove the <?xml version="1.0" encoding="UTF-8"?>, it appears differently in different JVM
// versions
xml = xml.replaceAll("<\\?.*\\?>", ""); // $NON-NLS-1$ //$NON-NLS-2$
xml = xml.replaceAll("[\r\n\t]", ""); // $NON-NLS-1$ //$NON-NLS-2$
xml = xml.replaceAll("<limit>-1</limit>", "");
/*
* System.out.println("Generated XML"); System.out.println(xml); System.out.println("File XML");
* System.out.println(data);
*/
assertEquals(data, xml);
// Checks that the rendered XML has the option with true instead of false
xml = mqlquery2.getXML();
assertNotNull(xml);
// remove the <?xml version="1.0" encoding="UTF-8"?>, it appears differently in different JVM
// versions
xml = xml.replaceAll("<\\?.*\\?>", ""); // $NON-NLS-1$ //$NON-NLS-2$
xml = xml.replaceAll("[\r\n\t]", ""); // $NON-NLS-1$ //$NON-NLS-2$
assertFalse(data.equals(xml));
// Tests that newly generated XML has the correct false flag in it.
xml = mqlquery3.getXML();
assertNotNull(xml);
// remove the <?xml version="1.0" encoding="UTF-8"?>, it appears differently in different JVM
// versions
xml = xml.replaceAll("<\\?.*\\?>", ""); // $NON-NLS-1$ //$NON-NLS-2$
xml = xml.replaceAll("[\r\n\t]", ""); // $NON-NLS-1$ //$NON-NLS-2$
xml = xml.replaceAll("<limit>-1</limit>", "");
assertEquals(data, xml);
} catch (IOException e) {
e.printStackTrace();
fail();
}
// TEST CONSTRAINT XML OUTPUT
try {
File file = new File(mqlfile4);
FileInputStream fileInputStream = new FileInputStream(file);
byte[] bytes = new byte[(int) file.length()];
fileInputStream.read(bytes);
fileInputStream.close();
String data = new String(bytes, Const.XML_ENCODING);
data = data.replaceAll("<\\?.*\\?>", ""); // $NON-NLS-1$ //$NON-NLS-2$
data = data.replaceAll("[\r\n\t]", ""); // $NON-NLS-1$ //$NON-NLS-2$
data = data.replaceAll("<!--.*[-][-][>]", ""); // $NON-NLS-1$ //$NON-NLS-2$
String xml = mqlquery4.getXML();
assertNotNull(xml);
xml = xml.replaceAll("<\\?.*\\?>", ""); // $NON-NLS-1$ //$NON-NLS-2$
xml = xml.replaceAll("[\r\n\t]", ""); // $NON-NLS-1$ //$NON-NLS-2$
xml =
xml.replaceAll(
"<options><disable_distinct>false</disable_distinct><limit>-1</limit></options>",
""); //$NON-NLS-1$ //$NON-NLS-2$
assertEquals(data, xml);
} catch (IOException e) {
e.printStackTrace();
fail();
}
try {
File file = new File(mqlfile5);
FileInputStream fileInputStream = new FileInputStream(file);
byte[] bytes = new byte[(int) file.length()];
fileInputStream.read(bytes);
fileInputStream.close();
String data = new String(bytes, Const.XML_ENCODING);
data = data.replaceAll("<\\?.*\\?>", ""); // $NON-NLS-1$ //$NON-NLS-2$
data = data.replaceAll("[\r\n\t]", ""); // $NON-NLS-1$ //$NON-NLS-2$
data = data.replaceAll("<!--.*[-][-][>]", ""); // $NON-NLS-1$ //$NON-NLS-2$
String xml = mqlquery5.getXML();
assertNotNull(xml);
xml = xml.replaceAll("<\\?.*\\?>", ""); // $NON-NLS-1$ //$NON-NLS-2$
xml = xml.replaceAll("[\r\n\t]", ""); // $NON-NLS-1$ //$NON-NLS-2$
assertEquals(data, xml);
} catch (IOException e) {
e.printStackTrace();
fail();
}
try {
// Now, look at generated SQL for distinct -vs- no distinct...
String SQL1 = mqlquery.getQuery().getQuery();
assertNotNull(SQL1);
assertTrue(SQL1.startsWith("SELECT DISTINCT"));
// System.out.println("SQL1: " + SQL1);
String SQL2 = mqlquery2.getQuery().getQuery();
assertNotNull(SQL2);
assertFalse(SQL2.startsWith("SELECT DISTINCT"));
// System.out.println("SQL2: " + SQL2);
String SQL3 = mqlquery3.getQuery().getQuery();
assertNotNull(SQL3);
assertTrue(SQL3.startsWith("SELECT DISTINCT"));
// System.out.println("SQL3: " + SQL3);
} catch (PentahoMetadataException e) {
e.printStackTrace();
fail();
}
// NOW TEST MQL QUERY EXCEPTIONS
String mqlfaildata01 =
mqldata.replaceAll(
"<constraints/>", //$NON-NLS-1$
"<constraints>"
+ //$NON-NLS-1$
" <constraint>"
+ //$NON-NLS-1$
" <condition></condition>"
+ //$NON-NLS-1$
" </constraint>"
+ //$NON-NLS-1$
"</constraints>" //$NON-NLS-1$
);
try {
mqlquery =
MQLQueryFactory.getMQLQuery(
mqlfaildata01, null, "en_US", cwmSchemaFactory); // $NON-NLS-1$
fail();
} catch (PentahoMetadataException e) {
assertEquals(
"MQLQuery.ERROR_0001 - Condition not specified in MQL constraint",
e.getMessage()); // $NON-NLS-1$
}
String mqlfaildata02 =
mqldata.replaceAll(
"<constraints/>", //$NON-NLS-1$
"<constraints>"
+ //$NON-NLS-1$
" <constraint/>"
+ //$NON-NLS-1$
"</constraints>" //$NON-NLS-1$
);
try {
mqlquery =
MQLQueryFactory.getMQLQuery(
mqlfaildata01, null, "en_US", cwmSchemaFactory); // $NON-NLS-1$
fail();
} catch (PentahoMetadataException e) {
assertEquals(
"MQLQuery.ERROR_0001 - Condition not specified in MQL constraint",
e.getMessage()); // $NON-NLS-1$
}
}