/*
// This software is subject to the terms of the Eclipse Public License v1.0
// Agreement, available at the following URL:
// http://www.eclipse.org/legal/epl-v10.html.
// You must accept the terms of that agreement to use this software.
//
// Copyright (C) 2005-2005 Julian Hyde
// Copyright (C) 2005-2012 Pentaho
// All Rights Reserved.
*/
package mondrian.test;
import mondrian.olap.*;
import mondrian.olap.Axis;
import mondrian.olap.Cell;
import mondrian.olap.type.*;
import mondrian.spi.CellFormatter;
import mondrian.spi.MemberFormatter;
import mondrian.spi.PropertyFormatter;
import mondrian.spi.*;
import org.apache.log4j.MDC;
import org.olap4j.*;
import org.olap4j.metadata.Property;
import java.sql.SQLException;
import java.util.*;
import java.util.regex.Pattern;
/**
* Unit-test for {@link UserDefinedFunction user-defined functions}.
* Also tests {@link mondrian.spi.CellFormatter cell formatters}
* and {@link mondrian.spi.MemberFormatter member formatters}.
*
* <p>TODO:
* 1. test that function which does not return a name, description etc.
* gets a sensible error
* 2. document UDFs
*
* @author jhyde
* @since Apr 29, 2005
*/
public class UdfTest extends FoodMartTestCase {
public UdfTest() {
}
public UdfTest(String name) {
super(name);
}
/**
* Test context which uses the local FoodMart schema, and adds a "PlusOne"
* user-defined function.
*/
private TestContext tc;
@Override
protected void setUp() throws Exception {
super.setUp();
tc = udfTestContext(
"<UserDefinedFunction name=\"PlusOne\" className=\""
+ PlusOneUdf.class.getName()
+ "\"/>\n");
}
@Override
protected void tearDown() throws Exception {
super.tearDown();
tc = null; // allow gc
}
public TestContext getTestContext() {
return tc;
}
/**
* Shorthand for containing a test context that consists of the standard
* FoodMart schema plus a UDF.
*
* @param xmlUdf UDF definition
* @return Test context
*/
private TestContext udfTestContext(String xmlUdf) {
return TestContext.instance().create(
null, null, null, null, xmlUdf, null);
}
/**
* Shorthand for containing a test context that consists of the standard
* FoodMart Sales cube plus one measure.
*
* @param xmlMeasure Measure definition
* @return Test context
*/
private TestContext measureTestContext(String xmlMeasure) {
return TestContext.instance().createSubstitutingCube(
"Sales", null, xmlMeasure, null, null);
}
/**
* Shorthand for containing a test context that consists of the standard
* FoodMart Sales cube plus one calculated member.
*
* @param xmlCalcMember Calculated member definition
* @return Test context
*/
private TestContext calcMemberTestContext(String xmlCalcMember) {
return TestContext.instance().createSubstitutingCube(
"Sales", null, null, xmlCalcMember, null);
}
// ~ Tests follow ----------------------------------------------------------
public void testSanity() {
// sanity check, make sure the schema is loading correctly
assertQueryReturns(
"SELECT {[Measures].[Store Sqft]} ON COLUMNS, {[Store Type]} ON ROWS FROM [Store]",
"Axis #0:\n"
+ "{}\n"
+ "Axis #1:\n"
+ "{[Measures].[Store Sqft]}\n"
+ "Axis #2:\n"
+ "{[Store Type].[All Store Types]}\n"
+ "Row #0: 571,596\n");
}
public void testFun() {
assertQueryReturns(
"WITH MEMBER [Measures].[Sqft Plus One] AS 'PlusOne([Measures].[Store Sqft])'\n"
+ "SELECT {[Measures].[Sqft Plus One]} ON COLUMNS, \n"
+ " {[Store Type].children} ON ROWS \n"
+ "FROM [Store]",
"Axis #0:\n"
+ "{}\n"
+ "Axis #1:\n"
+ "{[Measures].[Sqft Plus One]}\n"
+ "Axis #2:\n"
+ "{[Store Type].[Deluxe Supermarket]}\n"
+ "{[Store Type].[Gourmet Supermarket]}\n"
+ "{[Store Type].[HeadQuarters]}\n"
+ "{[Store Type].[Mid-Size Grocery]}\n"
+ "{[Store Type].[Small Grocery]}\n"
+ "{[Store Type].[Supermarket]}\n"
+ "Row #0: 146,046\n"
+ "Row #1: 47,448\n"
+ "Row #2: \n"
+ "Row #3: 109,344\n"
+ "Row #4: 75,282\n"
+ "Row #5: 193,481\n");
}
/**
* Test case for bug
* <a href="http://jira.pentaho.com/browse/MONDRIAN-1200">MONDRIAN-1200,
* "User-defined function + profiling causes NPE in CalcWriter"</a>.
* The bug only occurs if manually enable "mondrian.profile" logger before
* running this test. (The bug requires olap4j, plus profiling, plus a
* query that calls a UDF with one or more arguments on an axis.)
*
* @throws SQLException on error
*/
public void testFunWithProfiling() throws SQLException {
OlapConnection connection = null;
OlapStatement statement = null;
CellSet x = null;
try {
connection = getTestContext().getOlap4jConnection();
statement = connection.createStatement();
x = statement.executeOlapQuery(
"SELECT { CurrentDateMember([Time].[Time], "
+ "\"[Ti\\me]\\.[yyyy]\\.[Qq]\\.[m]\", BEFORE)} "
+ "ON COLUMNS FROM [Sales]");
Util.discard(TestContext.toString(x));
} finally {
Util.close(x, statement, connection);
}
}
public void testLastNonEmpty() {
assertQueryReturns(
"WITH MEMBER [Measures].[Last Unit Sales] AS \n"
+ " '([Measures].[Unit Sales], \n"
+ " LastNonEmpty(Descendants([Time].[Time]), [Measures].[Unit Sales]))'\n"
+ "SELECT {[Measures].[Last Unit Sales]} ON COLUMNS,\n"
+ " CrossJoin(\n"
+ " {[Time].[1997], [Time].[1997].[Q1], [Time].[1997].[Q1].Children},\n"
+ " {[Product].[All Products].[Drink].[Alcoholic Beverages].[Beer and Wine].[Beer].children}) ON ROWS\n"
+ "FROM [Sales]\n"
+ "WHERE ([Store].[All Stores].[USA].[OR].[Portland].[Store 11])",
"Axis #0:\n"
+ "{[Store].[USA].[OR].[Portland].[Store 11]}\n"
+ "Axis #1:\n"
+ "{[Measures].[Last Unit Sales]}\n"
+ "Axis #2:\n"
+ "{[Time].[1997], [Product].[Drink].[Alcoholic Beverages].[Beer and Wine].[Beer].[Good]}\n"
+ "{[Time].[1997], [Product].[Drink].[Alcoholic Beverages].[Beer and Wine].[Beer].[Pearl]}\n"
+ "{[Time].[1997], [Product].[Drink].[Alcoholic Beverages].[Beer and Wine].[Beer].[Portsmouth]}\n"
+ "{[Time].[1997], [Product].[Drink].[Alcoholic Beverages].[Beer and Wine].[Beer].[Top Measure]}\n"
+ "{[Time].[1997], [Product].[Drink].[Alcoholic Beverages].[Beer and Wine].[Beer].[Walrus]}\n"
+ "{[Time].[1997].[Q1], [Product].[Drink].[Alcoholic Beverages].[Beer and Wine].[Beer].[Good]}\n"
+ "{[Time].[1997].[Q1], [Product].[Drink].[Alcoholic Beverages].[Beer and Wine].[Beer].[Pearl]}\n"
+ "{[Time].[1997].[Q1], [Product].[Drink].[Alcoholic Beverages].[Beer and Wine].[Beer].[Portsmouth]}\n"
+ "{[Time].[1997].[Q1], [Product].[Drink].[Alcoholic Beverages].[Beer and Wine].[Beer].[Top Measure]}\n"
+ "{[Time].[1997].[Q1], [Product].[Drink].[Alcoholic Beverages].[Beer and Wine].[Beer].[Walrus]}\n"
+ "{[Time].[1997].[Q1].[1], [Product].[Drink].[Alcoholic Beverages].[Beer and Wine].[Beer].[Good]}\n"
+ "{[Time].[1997].[Q1].[1], [Product].[Drink].[Alcoholic Beverages].[Beer and Wine].[Beer].[Pearl]}\n"
+ "{[Time].[1997].[Q1].[1], [Product].[Drink].[Alcoholic Beverages].[Beer and Wine].[Beer].[Portsmouth]}\n"
+ "{[Time].[1997].[Q1].[1], [Product].[Drink].[Alcoholic Beverages].[Beer and Wine].[Beer].[Top Measure]}\n"
+ "{[Time].[1997].[Q1].[1], [Product].[Drink].[Alcoholic Beverages].[Beer and Wine].[Beer].[Walrus]}\n"
+ "{[Time].[1997].[Q1].[2], [Product].[Drink].[Alcoholic Beverages].[Beer and Wine].[Beer].[Good]}\n"
+ "{[Time].[1997].[Q1].[2], [Product].[Drink].[Alcoholic Beverages].[Beer and Wine].[Beer].[Pearl]}\n"
+ "{[Time].[1997].[Q1].[2], [Product].[Drink].[Alcoholic Beverages].[Beer and Wine].[Beer].[Portsmouth]}\n"
+ "{[Time].[1997].[Q1].[2], [Product].[Drink].[Alcoholic Beverages].[Beer and Wine].[Beer].[Top Measure]}\n"
+ "{[Time].[1997].[Q1].[2], [Product].[Drink].[Alcoholic Beverages].[Beer and Wine].[Beer].[Walrus]}\n"
+ "{[Time].[1997].[Q1].[3], [Product].[Drink].[Alcoholic Beverages].[Beer and Wine].[Beer].[Good]}\n"
+ "{[Time].[1997].[Q1].[3], [Product].[Drink].[Alcoholic Beverages].[Beer and Wine].[Beer].[Pearl]}\n"
+ "{[Time].[1997].[Q1].[3], [Product].[Drink].[Alcoholic Beverages].[Beer and Wine].[Beer].[Portsmouth]}\n"
+ "{[Time].[1997].[Q1].[3], [Product].[Drink].[Alcoholic Beverages].[Beer and Wine].[Beer].[Top Measure]}\n"
+ "{[Time].[1997].[Q1].[3], [Product].[Drink].[Alcoholic Beverages].[Beer and Wine].[Beer].[Walrus]}\n"
+ "Row #0: 2\n"
+ "Row #1: 7\n"
+ "Row #2: 6\n"
+ "Row #3: 7\n"
+ "Row #4: 4\n"
+ "Row #5: 3\n"
+ "Row #6: 4\n"
+ "Row #7: 3\n"
+ "Row #8: 4\n"
+ "Row #9: 2\n"
+ "Row #10: \n"
+ "Row #11: 4\n"
+ "Row #12: \n"
+ "Row #13: 2\n"
+ "Row #14: \n"
+ "Row #15: \n"
+ "Row #16: 2\n"
+ "Row #17: \n"
+ "Row #18: 4\n"
+ "Row #19: \n"
+ "Row #20: 3\n"
+ "Row #21: 4\n"
+ "Row #22: 3\n"
+ "Row #23: 4\n"
+ "Row #24: 2\n");
}
/**
* Tests a performance issue with LastNonEmpty (bug 1533677). The naive
* implementation of LastNonEmpty crawls backward one period at a time,
* generates a cache miss, and the next iteration reads precisely one cell.
* So the query soon exceeds the {@link MondrianProperties#MaxEvalDepth}
* property.
*/
public void testLastNonEmptyBig() {
assertQueryReturns(
"with\n"
+ " member\n"
+ " [Measures].[Last Sale] as ([Measures].[Unit Sales],\n"
+ " LastNonEmpty(Descendants([Time].[Time].CurrentMember, [Time].[Month]),\n"
+ " [Measures].[Unit Sales]))\n"
+ "select\n"
+ " NON EMPTY {[Measures].[Last Sale]} ON columns,\n"
+ " NON EMPTY Order([Store].[All Stores].Children,\n"
+ " [Measures].[Last Sale], DESC) ON rows\n"
+ "from [Sales]\n"
+ "where [Time].[Time].LastSibling",
"Axis #0:\n"
+ "{[Time].[1998]}\n"
+ "Axis #1:\n"
+ "Axis #2:\n");
}
public void testBadFun() {
final TestContext tc = udfTestContext(
"<UserDefinedFunction name=\"BadPlusOne\" className=\""
+ BadPlusOneUdf.class.getName()
+ "\"/>\n");
try {
tc.executeQuery("SELECT {} ON COLUMNS FROM [Sales]");
fail("Expected exception");
} catch (Exception e) {
final String s = e.getMessage();
assertEquals(
"Mondrian Error:Internal error: Invalid "
+ "user-defined function 'BadPlusOne': return type is null", s);
}
}
public void testGenericFun() {
final TestContext tc = udfTestContext(
"<UserDefinedFunction name=\"GenericPlusOne\" className=\""
+ PlusOrMinusOneUdf.class.getName()
+ "\"/>\n"
+ "<UserDefinedFunction name=\"GenericMinusOne\" className=\""
+ PlusOrMinusOneUdf.class.getName()
+ "\"/>\n");
tc.assertExprReturns("GenericPlusOne(3)", "4");
tc.assertExprReturns("GenericMinusOne(3)", "2");
}
public void testComplexFun() {
assertQueryReturns(
"WITH MEMBER [Measures].[InverseNormal] AS 'InverseNormal([Measures].[Grocery Sqft] / [Measures].[Store Sqft])', FORMAT_STRING = \"0.000\"\n"
+ "SELECT {[Measures].[InverseNormal]} ON COLUMNS, \n"
+ " {[Store Type].children} ON ROWS \n"
+ "FROM [Store]",
"Axis #0:\n"
+ "{}\n"
+ "Axis #1:\n"
+ "{[Measures].[InverseNormal]}\n"
+ "Axis #2:\n"
+ "{[Store Type].[Deluxe Supermarket]}\n"
+ "{[Store Type].[Gourmet Supermarket]}\n"
+ "{[Store Type].[HeadQuarters]}\n"
+ "{[Store Type].[Mid-Size Grocery]}\n"
+ "{[Store Type].[Small Grocery]}\n"
+ "{[Store Type].[Supermarket]}\n"
+ "Row #0: 0.467\n"
+ "Row #1: 0.463\n"
+ "Row #2: \n"
+ "Row #3: 0.625\n"
+ "Row #4: 0.521\n"
+ "Row #5: 0.504\n");
}
public void testException() {
Result result = executeQuery(
"WITH MEMBER [Measures].[InverseNormal] "
+ " AS 'InverseNormal([Measures].[Store Sqft] / [Measures].[Grocery Sqft])',"
+ " FORMAT_STRING = \"0.000000\"\n"
+ "SELECT {[Measures].[InverseNormal]} ON COLUMNS, \n"
+ " {[Store Type].children} ON ROWS \n"
+ "FROM [Store]");
Axis rowAxis = result.getAxes()[0];
assertTrue(rowAxis.getPositions().size() == 1);
Axis colAxis = result.getAxes()[1];
assertTrue(colAxis.getPositions().size() == 6);
Cell cell = result.getCell(new int[]{0, 0});
assertTrue(cell.isError());
getTestContext().assertMatchesVerbose(
Pattern.compile(
"(?s).*Invalid value for inverse normal distribution: 1.4708.*"),
cell.getValue().toString());
cell = result.getCell(new int[]{0, 5});
assertTrue(cell.isError());
getTestContext().assertMatchesVerbose(
Pattern.compile(
"(?s).*Invalid value for inverse normal distribution: 1.4435.*"),
cell.getValue().toString());
}
public void testCurrentDateString()
{
String actual = executeExpr("CurrentDateString(\"Ddd mmm dd yyyy\")");
Date currDate = new Date();
String dateString = currDate.toString();
String expected =
dateString.substring(0, 11)
+ dateString.substring(dateString.length() - 4);
assertEquals(expected, actual);
}
public void testCurrentDateMemberBefore() {
assertQueryReturns(
"SELECT { CurrentDateMember([Time].[Time], "
+ "\"[Ti\\me]\\.[yyyy]\\.[Qq]\\.[m]\", BEFORE)} "
+ "ON COLUMNS FROM [Sales]",
"Axis #0:\n"
+ "{}\n"
+ "Axis #1:\n"
+ "{[Time].[1998].[Q4].[12]}\n"
+ "Row #0: \n");
}
public void testCurrentDateMemberBeforeUsingQuotes()
{
assertAxisReturns(
MondrianProperties.instance().SsasCompatibleNaming.get()
? "CurrentDateMember([Time].[Time], "
+ "'\"[Time].[Time].[\"yyyy\"].[Q\"q\"].[\"m\"]\"', BEFORE)"
: "CurrentDateMember([Time], "
+ "'\"[Time].[\"yyyy\"].[Q\"q\"].[\"m\"]\"', BEFORE)",
"[Time].[1998].[Q4].[12]");
}
public void testCurrentDateMemberAfter()
{
// CurrentDateMember will return null member since the latest date in
// FoodMart is from '98
assertQueryReturns(
"SELECT { CurrentDateMember([Time].[Time], "
+ "\"[Ti\\me]\\.[yyyy]\\.[Qq]\\.[m]\", AFTER)} "
+ "ON COLUMNS FROM [Sales]",
"Axis #0:\n"
+ "{}\n"
+ "Axis #1:\n");
}
public void testCurrentDateMemberExact()
{
// CurrentDateMember will return null member since the latest date in
// FoodMart is from '98; apply a function on the return value to
// ensure null member instead of null is returned
assertQueryReturns(
"SELECT { CurrentDateMember([Time].[Time], "
+ "\"[Ti\\me]\\.[yyyy]\\.[Qq]\\.[m]\", EXACT).lag(1)} "
+ "ON COLUMNS FROM [Sales]",
"Axis #0:\n"
+ "{}\n"
+ "Axis #1:\n");
}
public void testCurrentDateMemberNoFindArg()
{
// CurrentDateMember will return null member since the latest date in
// FoodMart is from '98
assertQueryReturns(
"SELECT { CurrentDateMember([Time].[Time], "
+ "\"[Ti\\me]\\.[yyyy]\\.[Qq]\\.[m]\")} "
+ "ON COLUMNS FROM [Sales]",
"Axis #0:\n"
+ "{}\n"
+ "Axis #1:\n");
}
public void testCurrentDateMemberHierarchy() {
final String query =
MondrianProperties.instance().SsasCompatibleNaming.get()
? "SELECT { CurrentDateMember([Time.Weekly], "
+ "\"[Ti\\me\\.Weekl\\y]\\.[All Weekl\\y\\s]\\.[yyyy]\\.[ww]\", BEFORE)} "
+ "ON COLUMNS FROM [Sales]"
: "SELECT { CurrentDateMember([Time.Weekly], "
+ "\"[Ti\\me\\.Weekl\\y]\\.[All Ti\\me\\.Weekl\\y\\s]\\.[yyyy]\\.[ww]\", BEFORE)} "
+ "ON COLUMNS FROM [Sales]";
assertQueryReturns(
query,
"Axis #0:\n"
+ "{}\n"
+ "Axis #1:\n"
+ "{[Time].[Weekly].[1998].[52]}\n"
+ "Row #0: \n");
}
public void testCurrentDateMemberHierarchyNullReturn() {
// CurrentDateMember will return null member since the latest date in
// FoodMart is from '98; note that first arg is a hierarchy rather
// than a dimension
assertQueryReturns(
"SELECT { CurrentDateMember([Time.Weekly], "
+ "\"[Ti\\me]\\.[yyyy]\\.[Qq]\\.[m]\")} "
+ "ON COLUMNS FROM [Sales]",
"Axis #0:\n"
+ "{}\n"
+ "Axis #1:\n");
}
public void testCurrentDateMemberRealAfter() {
// omit formatting characters from the format so the current date
// is hard-coded to actual value in the database so we can test the
// after logic
assertQueryReturns(
"SELECT { CurrentDateMember([Time].[Time], "
+ "\"[Ti\\me]\\.[1996]\\.[Q4]\", after)} "
+ "ON COLUMNS FROM [Sales]",
"Axis #0:\n"
+ "{}\n"
+ "Axis #1:\n"
+ "{[Time].[1997].[Q1]}\n"
+ "Row #0: 66,291\n");
}
public void testCurrentDateMemberRealExact1() {
// omit formatting characters from the format so the current date
// is hard-coded to actual value in the database so we can test the
// exact logic
assertQueryReturns(
"SELECT { CurrentDateMember([Time].[Time], "
+ "\"[Ti\\me]\\.[1997]\")} "
+ "ON COLUMNS FROM [Sales]",
"Axis #0:\n"
+ "{}\n"
+ "Axis #1:\n"
+ "{[Time].[1997]}\n"
+ "Row #0: 266,773\n");
}
public void testCurrentDateMemberRealExact2() {
// omit formatting characters from the format so the current date
// is hard-coded to actual value in the database so we can test the
// exact logic
assertQueryReturns(
"SELECT { CurrentDateMember([Time].[Time], "
+ "\"[Ti\\me]\\.[1997]\\.[Q2]\\.[5]\")} "
+ "ON COLUMNS FROM [Sales]",
"Axis #0:\n"
+ "{}\n"
+ "Axis #1:\n"
+ "{[Time].[1997].[Q2].[5]}\n"
+ "Row #0: 21,081\n");
}
public void testCurrentDateMemberPrev() {
// apply a function on the result of the UDF
assertQueryReturns(
"SELECT { CurrentDateMember([Time].[Time], "
+ "\"[Ti\\me]\\.[yyyy]\\.[Qq]\\.[m]\", BEFORE).PrevMember} "
+ "ON COLUMNS FROM [Sales]",
"Axis #0:\n"
+ "{}\n"
+ "Axis #1:\n"
+ "{[Time].[1998].[Q4].[11]}\n"
+ "Row #0: \n");
}
public void testCurrentDateLag() {
// Also, try a different style of quoting, because single quote followed
// by double quote (used in other examples) is difficult to read.
assertQueryReturns(
"SELECT\n"
+ " { [Measures].[Unit Sales] } ON COLUMNS,\n"
+ " { CurrentDateMember([Time].[Time], '[\"Time\"]\\.[yyyy]\\.[\"Q\"q]\\.[m]', BEFORE).Lag(3) : "
+ " CurrentDateMember([Time].[Time], '[\"Time\"]\\.[yyyy]\\.[\"Q\"q]\\.[m]', BEFORE) } ON ROWS\n"
+ "FROM [Sales]",
"Axis #0:\n"
+ "{}\n"
+ "Axis #1:\n"
+ "{[Measures].[Unit Sales]}\n"
+ "Axis #2:\n"
+ "{[Time].[1998].[Q3].[9]}\n"
+ "{[Time].[1998].[Q4].[10]}\n"
+ "{[Time].[1998].[Q4].[11]}\n"
+ "{[Time].[1998].[Q4].[12]}\n"
+ "Row #0: \n"
+ "Row #1: \n"
+ "Row #2: \n"
+ "Row #3: \n");
}
public void testMatches() {
assertQueryReturns(
"SELECT {[Measures].[Org Salary]} ON COLUMNS, "
+ "Filter({[Employees].MEMBERS}, "
+ "[Employees].CurrentMember.Name MATCHES '(?i)sam.*') ON ROWS "
+ "FROM [HR]",
"Axis #0:\n"
+ "{}\n"
+ "Axis #1:\n"
+ "{[Measures].[Org Salary]}\n"
+ "Axis #2:\n"
+ "{[Employees].[Sheri Nowmer].[Derrick Whelply].[Beverly Baker].[Jacqueline Wyllie].[Ralph Mccoy].[Anne Tuck].[Samuel Johnson]}\n"
+ "{[Employees].[Sheri Nowmer].[Derrick Whelply].[Pedro Castillo].[Jose Bernard].[Mary Hunt].[Bonnie Bruno].[Sam Warren]}\n"
+ "{[Employees].[Sheri Nowmer].[Derrick Whelply].[Pedro Castillo].[Charles Macaluso].[Barbara Wallin].[Michael Suggs].[Sam Adair]}\n"
+ "{[Employees].[Sheri Nowmer].[Derrick Whelply].[Pedro Castillo].[Lois Wood].[Dell Gras].[Kristine Aldred].[Sam Zeller]}\n"
+ "{[Employees].[Sheri Nowmer].[Derrick Whelply].[Laurie Borges].[Cody Goldey].[Shanay Steelman].[Neal Hasty].[Sam Wheeler]}\n"
+ "{[Employees].[Sheri Nowmer].[Maya Gutierrez].[Brenda Blumberg].[Wayne Banack].[Samuel Agcaoili]}\n"
+ "{[Employees].[Sheri Nowmer].[Maya Gutierrez].[Jonathan Murraiin].[James Thompson].[Samantha Weller]}\n"
+ "Row #0: $40.62\n"
+ "Row #1: $40.31\n"
+ "Row #2: $75.60\n"
+ "Row #3: $40.35\n"
+ "Row #4: $47.52\n"
+ "Row #5: \n"
+ "Row #6: \n");
}
public void testNotMatches() {
assertQueryReturns(
"SELECT {[Measures].[Store Sales]} ON COLUMNS, "
+ "Filter({[Store Type].MEMBERS}, "
+ "[Store Type].CurrentMember.Name NOT MATCHES "
+ "'.*Grocery.*') ON ROWS "
+ "FROM [Sales]",
"Axis #0:\n"
+ "{}\n"
+ "Axis #1:\n"
+ "{[Measures].[Store Sales]}\n"
+ "Axis #2:\n"
+ "{[Store Type].[All Store Types]}\n"
+ "{[Store Type].[Deluxe Supermarket]}\n"
+ "{[Store Type].[Gourmet Supermarket]}\n"
+ "{[Store Type].[HeadQuarters]}\n"
+ "{[Store Type].[Supermarket]}\n"
+ "Row #0: 565,238.13\n"
+ "Row #1: 162,062.24\n"
+ "Row #2: 45,750.24\n"
+ "Row #3: \n"
+ "Row #4: 319,210.04\n");
}
public void testIn() {
assertQueryReturns(
"SELECT {[Measures].[Unit Sales]} ON COLUMNS, "
+ "FILTER([Product].[Product Family].MEMBERS, "
+ "[Product].[Product Family].CurrentMember IN "
+ "{[Product].[All Products].firstChild, "
+ "[Product].[All Products].lastChild}) ON ROWS "
+ "FROM [Sales]",
"Axis #0:\n"
+ "{}\n"
+ "Axis #1:\n"
+ "{[Measures].[Unit Sales]}\n"
+ "Axis #2:\n"
+ "{[Product].[Drink]}\n"
+ "{[Product].[Non-Consumable]}\n"
+ "Row #0: 24,597\n"
+ "Row #1: 50,236\n");
}
public void testNotIn() {
assertQueryReturns(
"SELECT {[Measures].[Unit Sales]} ON COLUMNS, "
+ "FILTER([Product].[Product Family].MEMBERS, "
+ "[Product].[Product Family].CurrentMember NOT IN "
+ "{[Product].[All Products].firstChild, "
+ "[Product].[All Products].lastChild}) ON ROWS "
+ "FROM [Sales]",
"Axis #0:\n"
+ "{}\n"
+ "Axis #1:\n"
+ "{[Measures].[Unit Sales]}\n"
+ "Axis #2:\n"
+ "{[Product].[Food]}\n"
+ "Row #0: 191,940\n");
}
public void testChildMemberIn() {
assertQueryReturns(
"SELECT {[Measures].[Store Sales]} ON COLUMNS, "
+ "{[Store].[Store Name].MEMBERS} ON ROWS "
+ "FROM [Sales]",
"Axis #0:\n"
+ "{}\n"
+ "Axis #1:\n"
+ "{[Measures].[Store Sales]}\n"
+ "Axis #2:\n"
+ "{[Store].[Canada].[BC].[Vancouver].[Store 19]}\n"
+ "{[Store].[Canada].[BC].[Victoria].[Store 20]}\n"
+ "{[Store].[Mexico].[DF].[Mexico City].[Store 9]}\n"
+ "{[Store].[Mexico].[DF].[San Andres].[Store 21]}\n"
+ "{[Store].[Mexico].[Guerrero].[Acapulco].[Store 1]}\n"
+ "{[Store].[Mexico].[Jalisco].[Guadalajara].[Store 5]}\n"
+ "{[Store].[Mexico].[Veracruz].[Orizaba].[Store 10]}\n"
+ "{[Store].[Mexico].[Yucatan].[Merida].[Store 8]}\n"
+ "{[Store].[Mexico].[Zacatecas].[Camacho].[Store 4]}\n"
+ "{[Store].[Mexico].[Zacatecas].[Hidalgo].[Store 12]}\n"
+ "{[Store].[Mexico].[Zacatecas].[Hidalgo].[Store 18]}\n"
+ "{[Store].[USA].[CA].[Alameda].[HQ]}\n"
+ "{[Store].[USA].[CA].[Beverly Hills].[Store 6]}\n"
+ "{[Store].[USA].[CA].[Los Angeles].[Store 7]}\n"
+ "{[Store].[USA].[CA].[San Diego].[Store 24]}\n"
+ "{[Store].[USA].[CA].[San Francisco].[Store 14]}\n"
+ "{[Store].[USA].[OR].[Portland].[Store 11]}\n"
+ "{[Store].[USA].[OR].[Salem].[Store 13]}\n"
+ "{[Store].[USA].[WA].[Bellingham].[Store 2]}\n"
+ "{[Store].[USA].[WA].[Bremerton].[Store 3]}\n"
+ "{[Store].[USA].[WA].[Seattle].[Store 15]}\n"
+ "{[Store].[USA].[WA].[Spokane].[Store 16]}\n"
+ "{[Store].[USA].[WA].[Tacoma].[Store 17]}\n"
+ "{[Store].[USA].[WA].[Walla Walla].[Store 22]}\n"
+ "{[Store].[USA].[WA].[Yakima].[Store 23]}\n"
+ "Row #0: \n"
+ "Row #1: \n"
+ "Row #2: \n"
+ "Row #3: \n"
+ "Row #4: \n"
+ "Row #5: \n"
+ "Row #6: \n"
+ "Row #7: \n"
+ "Row #8: \n"
+ "Row #9: \n"
+ "Row #10: \n"
+ "Row #11: \n"
+ "Row #12: 45,750.24\n"
+ "Row #13: 54,545.28\n"
+ "Row #14: 54,431.14\n"
+ "Row #15: 4,441.18\n"
+ "Row #16: 55,058.79\n"
+ "Row #17: 87,218.28\n"
+ "Row #18: 4,739.23\n"
+ "Row #19: 52,896.30\n"
+ "Row #20: 52,644.07\n"
+ "Row #21: 49,634.46\n"
+ "Row #22: 74,843.96\n"
+ "Row #23: 4,705.97\n"
+ "Row #24: 24,329.23\n");
// test when the member arg is at a different level
// from the set argument
assertQueryReturns(
"SELECT {[Measures].[Store Sales]} ON COLUMNS, "
+ "Filter({[Store].[Store Name].MEMBERS}, "
+ "[Store].[Store Name].CurrentMember IN "
+ "{[Store].[All Stores].[Mexico], "
+ "[Store].[All Stores].[USA]}) ON ROWS "
+ "FROM [Sales]",
"Axis #0:\n"
+ "{}\n"
+ "Axis #1:\n"
+ "{[Measures].[Store Sales]}\n"
+ "Axis #2:\n");
}
/**
* Tests that the inferred return type is correct for a UDF whose return
* type is not the same as would be guessed by the default implementation
* of {@link mondrian.olap.fun.FunDefBase#getResultType}, which simply
* guesses based on the type of the first argument.
*/
public void testNonGuessableReturnType() {
TestContext tc = udfTestContext(
"<UserDefinedFunction name=\"StringMult\" className=\""
+ StringMultUdf.class.getName()
+ "\"/>\n");
// The default implementation of getResultType would assume that
// StringMult(int, string) returns an int, whereas it returns a string.
tc.assertExprReturns(
"StringMult(5, 'foo') || 'bar'", "foofoofoofoofoobar");
}
/**
* Test case for the problem where a string expression gave a
* ClassCastException because it was evaluating to a member, whereas the
* member should have been evaluated to a scalar.
*/
public void testUdfToString() {
TestContext tc = udfTestContext(
"<UserDefinedFunction name=\"StringMult\" className=\""
+ StringMultUdf.class.getName()
+ "\"/>\n");
tc.assertQueryReturns(
"with member [Measures].[ABC] as StringMult(1, 'A')\n"
+ "member [Measures].[Unit Sales Formatted] as\n"
+ " [Measures].[Unit Sales],\n"
+ " FORMAT_STRING = '#,###|color=' ||\n"
+ " Iif([Measures].[ABC] = 'A', 'red', 'green')\n"
+ "select [Measures].[Unit Sales Formatted] on 0\n"
+ "from [Sales]",
"Axis #0:\n"
+ "{}\n"
+ "Axis #1:\n"
+ "{[Measures].[Unit Sales Formatted]}\n"
+ "Row #0: 266,773|color=red\n");
}
/**
* Tests a UDF whose return type is not the same as its first
* parameter. The return type needs to have full dimensional information;
* in this case, HierarchyType(dimension=Time, hierarchy=unknown).
*
* <p>Also tests applying a UDF to arguments of coercible type. In this
* case, applies f(member,dimension) to args(member,hierarchy).
*/
public void testAnotherMemberFun() {
final TestContext tc = udfTestContext(
"<UserDefinedFunction name=\"PlusOne\" className=\""
+ PlusOneUdf.class.getName() + "\"/>\n"
+ "<UserDefinedFunction name=\"AnotherMemberError\" className=\""
+ AnotherMemberErrorUdf.class.getName() + "\"/>");
tc.assertQueryReturns(
"WITH MEMBER [Measures].[Test] AS "
+ "'([Measures].[Store Sales],[Product].[Food],AnotherMemberError([Product].[Drink],[Time].[Time]))'\n"
+ "SELECT {[Measures].[Test]} ON COLUMNS, \n"
+ " {[Customers].DefaultMember} ON ROWS \n"
+ "FROM [Sales]",
"Axis #0:\n"
+ "{}\n"
+ "Axis #1:\n"
+ "{[Measures].[Test]}\n"
+ "Axis #2:\n"
+ "{[Customers].[All Customers]}\n"
+ "Row #0: 409,035.59\n");
}
public void testCachingCurrentDate() {
assertQueryReturns(
"SELECT {filter([Time].[Month].Members, "
+ "[Time].[Time].CurrentMember in {CurrentDateMember([Time]"
+ ".[Time], '[\"Time\"]\\.[yyyy]\\.[\"Q\"q]\\.[m]', "
+ "BEFORE)})} ON COLUMNS "
+ "from [Sales]",
"Axis #0:\n"
+ "{}\n"
+ "Axis #1:\n"
+ "{[Time].[1998].[Q4].[12]}\n"
+ "Row #0: \n");
}
/**
* Test case for a UDF that returns a list.
*
* <p>Test case for bug
* <a href="http://jira.pentaho.com/browse/MONDRIAN-588">MONDRIAN-588,
* "UDF returning List works under 2.4, fails under 3.1.1"</a>.
*
* <p>Also test case for bug
* <a href="http://jira.pentaho.com/browse/MONDRIAN-589">MONDRIAN-589,
* "UDF expecting List gets anonymous
* mondrian.rolap.RolapNamedSetEvaluator$1 instead"</a>.
*/
public void testListUdf() {
checkListUdf(ReverseFunction.class);
checkListUdf(ReverseIterableFunction.class);
}
/**
* Helper for {@link #testListUdf()}.
*
* @param functionClass Class that implements the "Reverse" function.
*/
private void checkListUdf(
final Class<? extends ReverseFunction> functionClass)
{
TestContext tc = udfTestContext(
"<UserDefinedFunction name=\"Reverse\" className=\""
+ functionClass.getName()
+ "\"/>\n");
final String expectedResult =
"Axis #0:\n"
+ "{}\n"
+ "Axis #1:\n"
+ "{[Gender].[M]}\n"
+ "{[Gender].[F]}\n"
+ "{[Gender].[All Gender]}\n"
+ "Row #0: 135,215\n"
+ "Row #0: 131,558\n"
+ "Row #0: 266,773\n";
// UDF called directly in axis expression.
tc.assertQueryReturns(
"select Reverse([Gender].Members) on 0\n"
+ "from [Sales]",
expectedResult);
// UDF as calc set definition
tc.assertQueryReturns(
"with set [Foo] as Reverse([Gender].Members)\n"
+ "select [Foo] on 0\n"
+ "from [Sales]",
expectedResult);
// UDF applied to calc set -- exhibited MONDRIAN-589
tc.assertQueryReturns(
"with set [Foo] as [Gender].Members\n"
+ "select Reverse([Foo]) on 0\n"
+ "from [Sales]", expectedResult);
}
/**
* Tests that a non-static function gives an error.
*/
public void testNonStaticUdfFails() {
if (Util.PreJdk15) {
// Cannot detect non-static inner classes in JDK 1.4, because
// such things are not supposed to exist.
return;
}
TestContext tc = udfTestContext(
"<UserDefinedFunction name=\"Reverse2\" className=\""
+ ReverseFunctionNotStatic.class.getName()
+ "\"/>\n");
tc.assertQueryThrows(
"select Reverse2([Gender].Members) on 0\n" + "from [Sales]",
"Failed to load user-defined function 'Reverse2': class "
+ "'mondrian.test.UdfTest$ReverseFunctionNotStatic' must be "
+ "public "
+ "and static");
}
/**
* Tests a function that takes a member as argument. Want to make sure that
* Mondrian leaves it as a member, does not try to evaluate it to a scalar
* value.
*/
public void testMemberUdfDoesNotEvaluateToScalar() {
TestContext tc = udfTestContext(
"<UserDefinedFunction name=\"MemberName\" className=\""
+ MemberNameFunction.class.getName()
+ "\"/>\n");
tc.assertExprReturns(
"MemberName([Gender].[F])", "F");
}
/**
* Unit test that ensures that a UDF has either a script or a className.
*/
public void testUdfNeitherScriptNorClassname() {
TestContext tc = udfTestContext(
"<UserDefinedFunction name='StringMult'/>\n");
tc.assertQueryThrows(
"select from [Sales]",
"Must specify either className attribute or Script element");
}
/**
* Unit test that ensures that a UDF does not have both a script
* and a className.
*/
public void testUdfBothScriptAndClassname() {
TestContext tc = udfTestContext(
"<UserDefinedFunction name='StringMult' className='foo'>\n"
+ " <Script>bar</Script>\n"
+ "</UserDefinedFunction>");
tc.assertQueryThrows(
"select from [Sales]",
"Must not specify both className attribute and Script element");
}
/**
* Unit test that ensures that a UDF has either a script or a className.
*/
public void testUdfScriptBadLanguage() {
TestContext tc = udfTestContext(
"<UserDefinedFunction name='StringMult'>\n"
+ " <Script language='bad'>bar</Script>\n"
+ "</UserDefinedFunction>");
tc.assertQueryThrows(
"select from [Sales]",
"Invalid script language 'bad'");
}
/**
* Unit test that ensures that script UDFs fail before JDK 1.6.
*/
public void testUdfScriptBadJdk() {
if (!Util.PreJdk16) {
return;
}
TestContext tc = udfTestContext(
"<UserDefinedFunction name='StringMult'>\n"
+ " <Script language='JavaScript'>bar</Script>\n"
+ "</UserDefinedFunction>");
tc.assertQueryThrows(
"select from [Sales]", "Scripting not supported until Java 1.6");
}
/**
* Unit test for a UDF defined in JavaScript.
*/
public void testScriptUdf() {
if (Util.PreJdk16) {
return;
}
TestContext tc = udfTestContext(
"<UserDefinedFunction name='StringMult'>\n"
+ " <Script language='JavaScript'>\n"
+ " function getParameterTypes() {\n"
+ " return new Array(\n"
+ " new mondrian.olap.type.NumericType(),\n"
+ " new mondrian.olap.type.StringType());\n"
+ " }\n"
+ " function getReturnType(parameterTypes) {\n"
+ " return new mondrian.olap.type.StringType();\n"
+ " }\n"
+ " function execute(evaluator, arguments) {\n"
+ " var n = arguments[0].evaluateScalar(evaluator);\n"
+ " var s = arguments[1].evaluateScalar(evaluator);\n"
+ " var r = \"\";\n"
+ " while (n-- > 0) {\n"
+ " r = r + s;\n"
+ " }\n"
+ " return r;\n"
+ " }\n"
+ " </Script>\n"
+ "</UserDefinedFunction>\n");
tc.assertQueryReturns(
"with member [Measures].[ABC] as StringMult(1, 'A')\n"
+ "member [Measures].[Unit Sales Formatted] as\n"
+ " [Measures].[Unit Sales],\n"
+ " FORMAT_STRING = '#,###|color=' ||\n"
+ " Iif([Measures].[ABC] = 'A', 'red', 'green')\n"
+ "select [Measures].[Unit Sales Formatted] on 0\n"
+ "from [Sales]",
"Axis #0:\n"
+ "{}\n"
+ "Axis #1:\n"
+ "{[Measures].[Unit Sales Formatted]}\n"
+ "Row #0: 266,773|color=red\n");
}
/**
* Unit test for a UDF defined in JavaScript, this time the factorial
* function. We also use 'CDATA' section to mask the '<' symbol.
*/
public void testScriptUdfFactorial() {
if (Util.PreJdk16) {
return;
}
TestContext tc = udfTestContext(
"<UserDefinedFunction name='Factorial'>\n"
+ " <Script language='JavaScript'><![CDATA[\n"
+ " function getParameterTypes() {\n"
+ " return new Array(\n"
+ " new mondrian.olap.type.NumericType());\n"
+ " }\n"
+ " function getReturnType(parameterTypes) {\n"
+ " return new mondrian.olap.type.NumericType();\n"
+ " }\n"
+ " function execute(evaluator, arguments) {\n"
+ " var n = arguments[0].evaluateScalar(evaluator);\n"
+ " return factorial(n);\n"
+ " }\n"
+ " function factorial(n) {\n"
+ " return n <= 1 ? 1 : n * factorial(n - 1);\n"
+ " }\n"
+ " ]]>\n"
+ " </Script>\n"
+ "</UserDefinedFunction>\n");
tc.assertExprReturns(
"Factorial(4 + 2)",
"720");
}
/**
* Unit test that we get a nice error if a script UDF contains an error.
*/
public void testScriptUdfInvalid() {
if (Util.PreJdk16) {
return;
}
TestContext tc = udfTestContext(
"<UserDefinedFunction name='Factorial'>\n"
+ " <Script language='JavaScript'><![CDATA[\n"
+ " function getParameterTypes() {\n"
+ " return new Array(\n"
+ " new mondrian.olap.type.NumericType());\n"
+ " }\n"
+ " function getReturnType(parameterTypes) {\n"
+ " return new mondrian.olap.type.NumericType();\n"
+ " }\n"
+ " function execute(evaluator, arguments) {\n"
+ " var n = arguments[0].evaluateScalar(evaluator);\n"
+ " return factorial(n);\n"
+ " }\n"
+ " function factorial(n) {\n"
+ " return n <= 1 ? 1 : n * factorial_xx(n - 1);\n"
+ " }\n"
+ " ]]>\n"
+ " </Script>\n"
+ "</UserDefinedFunction>\n");
final Cell cell = tc.executeExprRaw("Factorial(4 + 2)");
getTestContext().assertMatchesVerbose(
Pattern.compile(
"(?s).*ReferenceError: \"factorial_xx\" is not defined..*"),
cell.getValue().toString());
}
/**
* Unit test for a cell formatter defined in the old way -- a 'formatter'
* attribute of a Measure element.
*/
public void testCellFormatter() {
// Note that
// formatString="Standard"
// is ignored.
TestContext tc = measureTestContext(
"<Measure name='Unit Sales Foo Bar' column='unit_sales'\n"
+ " aggregator='sum' formatString='Standard' formatter='"
+ FooBarCellFormatter.class.getName()
+ "'/>");
tc.assertQueryReturns(
"select {[Measures].[Unit Sales],\n"
+ " [Measures].[Unit Sales Foo Bar]} on 0\n"
+ "from [Sales]",
"Axis #0:\n"
+ "{}\n"
+ "Axis #1:\n"
+ "{[Measures].[Unit Sales]}\n"
+ "{[Measures].[Unit Sales Foo Bar]}\n"
+ "Row #0: 266,773\n"
+ "Row #0: foo266773.0bar\n");
}
/**
* As {@link #testCellFormatter()}, but using new-style nested
* CellFormatter element.
*/
public void testCellFormatterNested() {
// Note that
// formatString="Standard"
// is ignored.
TestContext tc = measureTestContext(
"<Measure name='Unit Sales Foo Bar' column='unit_sales'\n"
+ " aggregator='sum' formatString='Standard'>\n"
+ " <CellFormatter className='"
+ FooBarCellFormatter.class.getName()
+ "'/>\n"
+ "</Measure>");
tc.assertQueryReturns(
"select {[Measures].[Unit Sales],\n"
+ " [Measures].[Unit Sales Foo Bar]} on 0\n"
+ "from [Sales]",
"Axis #0:\n"
+ "{}\n"
+ "Axis #1:\n"
+ "{[Measures].[Unit Sales]}\n"
+ "{[Measures].[Unit Sales Foo Bar]}\n"
+ "Row #0: 266,773\n"
+ "Row #0: foo266773.0bar\n");
}
/**
* As {@link #testCellFormatterNested()}, but using a script.
*/
public void testCellFormatterScript() {
if (Util.PreJdk16) {
return;
}
TestContext tc = measureTestContext(
"<Measure name='Unit Sales Foo Bar' column='unit_sales'\n"
+ " aggregator='sum' formatString='Standard'>\n"
+ " <CellFormatter>\n"
+ " <Script>\n"
+ " return \"foo\" + value + \"bar\";\n"
+ " </Script>\n"
+ " </CellFormatter>\n"
+ "</Measure>");
// Note that the result is slightly different to above (a missing ".0").
// Not a great concern -- in fact it proves that the scripted UDF is
// being used.
tc.assertQueryReturns(
"select {[Measures].[Unit Sales],\n"
+ " [Measures].[Unit Sales Foo Bar]} on 0\n"
+ "from [Sales]",
"Axis #0:\n"
+ "{}\n"
+ "Axis #1:\n"
+ "{[Measures].[Unit Sales]}\n"
+ "{[Measures].[Unit Sales Foo Bar]}\n"
+ "Row #0: 266,773\n"
+ "Row #0: foo266773bar\n");
}
/**
* Unit test for a cell formatter defined against a calculated member,
* using the old syntax (a member property called "CELL_FORMATTER").
*/
public void testCellFormatterOnCalcMember() {
TestContext tc = calcMemberTestContext(
"<CalculatedMember\n"
+ " name='Unit Sales Foo Bar'\n"
+ " dimension='Measures'>\n"
+ " <Formula>[Measures].[Unit Sales]</Formula>\n"
+ " <CalculatedMemberProperty name='CELL_FORMATTER' value='"
+ FooBarCellFormatter.class.getName()
+ "'/>\n"
+ "</CalculatedMember>");
tc.assertQueryReturns(
"select {[Measures].[Unit Sales],\n"
+ " [Measures].[Unit Sales Foo Bar]} on 0\n"
+ "from [Sales]",
"Axis #0:\n"
+ "{}\n"
+ "Axis #1:\n"
+ "{[Measures].[Unit Sales]}\n"
+ "{[Measures].[Unit Sales Foo Bar]}\n"
+ "Row #0: 266,773\n"
+ "Row #0: foo266773.0bar\n");
}
/**
* Unit test for a cell formatter defined against a calculated member,
* using the new syntax (a nested CellFormatter element).
*/
public void testCellFormatterOnCalcMemberNested() {
TestContext tc = calcMemberTestContext(
"<CalculatedMember\n"
+ " name='Unit Sales Foo Bar'\n"
+ " dimension='Measures'>\n"
+ " <Formula>[Measures].[Unit Sales]</Formula>\n"
+ " <CellFormatter className='"
+ FooBarCellFormatter.class.getName()
+ "'/>\n"
+ "</CalculatedMember>");
tc.assertQueryReturns(
"select {[Measures].[Unit Sales],\n"
+ " [Measures].[Unit Sales Foo Bar]} on 0\n"
+ "from [Sales]",
"Axis #0:\n"
+ "{}\n"
+ "Axis #1:\n"
+ "{[Measures].[Unit Sales]}\n"
+ "{[Measures].[Unit Sales Foo Bar]}\n"
+ "Row #0: 266,773\n"
+ "Row #0: foo266773.0bar\n");
}
/**
* Unit test for a cell formatter defined against a calculated member,
* using a script.
*/
public void testCellFormatterOnCalcMemberScript() {
if (Util.PreJdk16) {
return;
}
TestContext tc = calcMemberTestContext(
"<CalculatedMember\n"
+ " name='Unit Sales Foo Bar'\n"
+ " dimension='Measures'>\n"
+ " <Formula>[Measures].[Unit Sales]</Formula>\n"
+ " <CellFormatter>\n"
+ " <Script>\n"
+ " return \"foo\" + value + \"bar\";\n"
+ " </Script>\n"
+ " </CellFormatter>\n"
+ "</CalculatedMember>");
tc.assertQueryReturns(
"select {[Measures].[Unit Sales],\n"
+ " [Measures].[Unit Sales Foo Bar]} on 0\n"
+ "from [Sales]",
"Axis #0:\n"
+ "{}\n"
+ "Axis #1:\n"
+ "{[Measures].[Unit Sales]}\n"
+ "{[Measures].[Unit Sales Foo Bar]}\n"
+ "Row #0: 266,773\n"
+ "Row #0: foo266773bar\n");
}
/**
* Unit test for a member formatter defined in the old way -- a 'formatter'
* attribute of a Measure element.
*/
public void testMemberFormatter() {
TestContext tc = TestContext.instance().createSubstitutingCube(
"Sales",
" <Dimension name='Promotion Media2' foreignKey='promotion_id'>\n"
+ " <Hierarchy hasAll='true' allMemberName='All Media' primaryKey='promotion_id'>\n"
+ " <Table name='promotion'/>\n"
+ " <Level name='Media Type' column='media_type'\n"
+ " uniqueMembers='true' formatter='"
+ FooBarMemberFormatter.class.getName()
+ "'/>\n"
+ " </Hierarchy>\n"
+ " </Dimension>");
tc.assertExprReturns(
"[Promotion Media2].FirstChild.Caption",
"fooBulk Mailbar");
}
/**
* As {@link #testMemberFormatter()}, but using new-style nested
* memberFormatter element.
*/
public void testMemberFormatterNested() {
TestContext tc = TestContext.instance().createSubstitutingCube(
"Sales",
" <Dimension name='Promotion Media2' foreignKey='promotion_id'>\n"
+ " <Hierarchy hasAll='true' allMemberName='All Media' primaryKey='promotion_id'>\n"
+ " <Table name='promotion'/>\n"
+ " <Level name='Media Type' column='media_type'\n"
+ " uniqueMembers='true'>\n"
+ " <MemberFormatter className='"
+ FooBarMemberFormatter.class.getName()
+ "'/>\n"
+ " </Level>\n"
+ " </Hierarchy>\n"
+ " </Dimension>");
tc.assertExprReturns(
"[Promotion Media2].FirstChild.Caption",
"fooBulk Mailbar");
}
/**
* As {@link #testMemberFormatterNested()}, but using a script.
*/
public void testMemberFormatterScript() {
if (Util.PreJdk16) {
return;
}
TestContext tc = TestContext.instance().createSubstitutingCube(
"Sales",
" <Dimension name='Promotion Media2' foreignKey='promotion_id'>\n"
+ " <Hierarchy hasAll='true' allMemberName='All Media' primaryKey='promotion_id'>\n"
+ " <Table name='promotion'/>\n"
+ " <Level name='Media Type' column='media_type'\n"
+ " uniqueMembers='true'>\n"
+ " <MemberFormatter>\n"
+ " <Script language='JavaScript'>\n"
+ " return \"foo\" + member.getName() + \"bar\"\n"
+ " </Script>\n"
+ " </MemberFormatter>\n"
+ " </Level>\n"
+ " </Hierarchy>\n"
+ " </Dimension>");
tc.assertExprReturns(
"[Promotion Media2].FirstChild.Caption",
"fooBulk Mailbar");
}
/**
* Unit test for a property formatter defined in the old way -- a
* 'formatter' attribute of a Property element.
*
* @throws java.sql.SQLException on error
*/
public void testPropertyFormatter() throws SQLException {
TestContext tc = TestContext.instance().createSubstitutingCube(
"Sales",
"<Dimension name='Promotions2' foreignKey='promotion_id'>\n"
+ " <Hierarchy hasAll='true' allMemberName='All Promotions' primaryKey='promotion_id' defaultMember='[All Promotions]'>\n"
+ " <Table name='promotion'/>\n"
+ " <Level name='Promotion Name' column='promotion_id' uniqueMembers='true'>\n"
+ " <Property name='Medium' column='media_type' formatter='"
+ FooBarPropertyFormatter.class.getName()
+ "'/>\n"
+ " </Level>\n"
+ " </Hierarchy>\n"
+ "</Dimension>");
final CellSet result =
tc.executeOlap4jQuery(
"select [Promotions2].Children on 0\n"
+ "from [Sales]");
final org.olap4j.metadata.Member member =
result.getAxes().get(0).getPositions().get(0).getMembers().get(0);
final Property property = member.getProperties().get("Medium");
assertEquals(
"foo0/Medium/No Mediabar",
member.getPropertyFormattedValue(property));
}
/**
* As {@link #testPropertyFormatter()}, but using new-style nested
* PropertyFormatter element.
*
* @throws java.sql.SQLException on error
*/
public void testPropertyFormatterNested() throws SQLException {
TestContext tc = TestContext.instance().createSubstitutingCube(
"Sales",
"<Dimension name='Promotions2' foreignKey='promotion_id'>\n"
+ " <Hierarchy hasAll='true' allMemberName='All Promotions' primaryKey='promotion_id' defaultMember='[All Promotions]'>\n"
+ " <Table name='promotion'/>\n"
+ " <Level name='Promotion Name' column='promotion_id' uniqueMembers='true'>\n"
+ " <Property name='Medium' column='media_type'>\n"
+ " <PropertyFormatter className='"
+ FooBarPropertyFormatter.class.getName()
+ "'/>\n"
+ " </Property>\n"
+ " </Level>\n"
+ " </Hierarchy>\n"
+ "</Dimension>");
final CellSet result =
tc.executeOlap4jQuery(
"select [Promotions2].Children on 0\n"
+ "from [Sales]");
final org.olap4j.metadata.Member member =
result.getAxes().get(0).getPositions().get(0).getMembers().get(0);
final Property property = member.getProperties().get("Medium");
assertEquals(
"foo0/Medium/No Mediabar",
member.getPropertyFormattedValue(property));
}
/**
* As {@link #testPropertyFormatterNested()}, but using a script.
*
* @throws java.sql.SQLException on error
*/
public void testPropertyFormatterScript() throws SQLException {
if (Util.PreJdk16) {
return;
}
TestContext tc = TestContext.instance().createSubstitutingCube(
"Sales",
"<Dimension name='Promotions2' foreignKey='promotion_id'>\n"
+ " <Hierarchy hasAll='true' allMemberName='All Promotions' primaryKey='promotion_id' defaultMember='[All Promotions]'>\n"
+ " <Table name='promotion'/>\n"
+ " <Level name='Promotion Name' column='promotion_id' uniqueMembers='true'>\n"
+ " <Property name='Medium' column='media_type'>\n"
+ " <PropertyFormatter>\n"
+ " <Script language='JavaScript'>\n"
+ " return \"foo\" + member.getName() + \"/\"\n"
+ " + propertyName + \"/\"\n"
+ " + propertyValue + \"bar\";\n"
+ " </Script>\n"
+ " </PropertyFormatter>\n"
+ " </Property>\n"
+ " </Level>\n"
+ " </Hierarchy>\n"
+ "</Dimension>");
final CellSet result =
tc.executeOlap4jQuery(
"select [Promotions2].Children on 0\n"
+ "from [Sales]");
final org.olap4j.metadata.Member member =
result.getAxes().get(0).getPositions().get(0).getMembers().get(0);
final Property property = member.getProperties().get("Medium");
assertEquals(
"foo0/Medium/No Mediabar",
member.getPropertyFormattedValue(property));
}
private static String MDC_KEY = "Chunky Bacon!";
private static Object MDC_OBJECT = new Object();
/**
* This is a test for
* <a href="http://jira.pentaho.com/browse/MONDRIAN-994">MONDRIAN-994</a>.
* It checks that the MDC logging context is passed through all the
* threads.
*/
public void testMdc() {
final TestContext context =
udfTestContext(
"<UserDefinedFunction name=\"Mdc\" className=\""
+ MdcUdf.class.getName()
+ "\"/>\n");
MDC.put(MDC_KEY, MDC_OBJECT);
try {
context.executeQuery(
"with member [Measures].[MDC] as 'Mdc([Measures].[Unit Sales])' "
+ "select {[Measures].[MDC]} on columns from [Sales]");
} finally {
MDC.remove(MDC_KEY);
}
}
// ~ Inner classes --------------------------------------------------------
/**
* A simple UDF that checks the object inside of the MDC logging context.
*/
public static class MdcUdf implements UserDefinedFunction {
public String getName() {
return "Mdc";
}
public String getDescription() {
return "";
}
public Syntax getSyntax() {
return Syntax.Function;
}
public Type getReturnType(Type[] parameterTypes) {
return new NumericType();
}
public Type[] getParameterTypes() {
return new Type[] {new NumericType()};
}
public Object execute(Evaluator evaluator, Argument[] arguments) {
Map<String, Object> context = org.apache.log4j.MDC.getContext();
if (!context.containsKey(MDC_KEY)
|| context.get(MDC_KEY) != MDC_OBJECT)
{
fail();
}
return arguments[0].evaluateScalar(evaluator);
}
public String[] getReservedWords() {
return null;
}
}
/**
* A simple user-defined function which adds one to its argument.
*/
public static class PlusOneUdf implements UserDefinedFunction {
public String getName() {
return "PlusOne";
}
public String getDescription() {
return "Returns its argument plus one";
}
public Syntax getSyntax() {
return Syntax.Function;
}
public Type getReturnType(Type[] parameterTypes) {
return new NumericType();
}
public Type[] getParameterTypes() {
return new Type[] {new NumericType()};
}
public Object execute(Evaluator evaluator, Argument[] arguments) {
final Object argValue = arguments[0].evaluateScalar(evaluator);
if (argValue instanceof Number) {
return ((Number) argValue).doubleValue() + 1.0;
} else {
// Argument might be a RuntimeException indicating that
// the cache does not yet have the required cell value. The
// function will be called again when the cache is loaded.
return null;
}
}
public String[] getReservedWords() {
return null;
}
}
/**
* A simple user-defined function which adds one to its argument.
*/
public static class BadPlusOneUdf extends PlusOneUdf {
private final String name;
public BadPlusOneUdf(String name) {
this.name = name;
}
public String getName() {
return name;
}
public Type getReturnType(Type[] parameterTypes) {
// Will cause error.
return null;
}
}
/**
* A user-defined function which, depending on its given name, either adds
* one to, or subtracts one from, its argument.
*/
public static class PlusOrMinusOneUdf implements UserDefinedFunction {
private final String name;
public PlusOrMinusOneUdf(String name) {
if (!(name.equals("GenericPlusOne")
|| name.equals("GenericMinusOne")))
{
throw new IllegalArgumentException();
}
this.name = name;
}
public String getName() {
return name;
}
public String getDescription() {
return
"A user-defined function which, depending on its given name, "
+ "either addsone to, or subtracts one from, its argument";
}
public Syntax getSyntax() {
return Syntax.Function;
}
public Type getReturnType(Type[] parameterTypes) {
return new NumericType();
}
public String[] getReservedWords() {
return null;
}
public Type[] getParameterTypes() {
return new Type[] {new NumericType()};
}
public Object execute(Evaluator evaluator, Argument[] arguments) {
final Object argValue = arguments[0].evaluateScalar(evaluator);
if (argValue instanceof Number) {
return ((Number) argValue).doubleValue()
+ (name.equals("GenericPlusOne") ? 1.0 : -1.0);
} else {
// Argument might be a RuntimeException indicating that
// the cache does not yet have the required cell value. The
// function will be called again when the cache is loaded.
return null;
}
}
}
/**
* The "TimesString" user-defined function. We wanted a function whose
* actual return type (string) is not the same as the guessed return type
* (integer).
*/
public static class StringMultUdf implements UserDefinedFunction {
public String getName() {
return "StringMult";
}
public String getDescription() {
return "Returns N copies of its string argument";
}
public Syntax getSyntax() {
return Syntax.Function;
}
public Type getReturnType(Type[] parameterTypes) {
return new StringType();
}
public Type[] getParameterTypes() {
return new Type[] {
new NumericType(), new StringType()
};
}
public Object execute(Evaluator evaluator, Argument[] arguments) {
final Object argValue = arguments[0].evaluateScalar(evaluator);
int n;
if (argValue instanceof Number) {
n = ((Number) argValue).intValue();
} else {
// Argument might be a RuntimeException indicating that
// the cache does not yet have the required cell value. The
// function will be called again when the cache is loaded.
return null;
}
String s;
final Object argValue2 = arguments[1].evaluateScalar(evaluator);
if (argValue2 instanceof String) {
s = (String) argValue2;
} else {
return null;
}
if (n < 0) {
return null;
}
StringBuilder buf = new StringBuilder(s.length() * n);
for (int i = 0; i < n; i++) {
buf.append(s);
}
return buf.toString();
}
public String[] getReservedWords() {
return null;
}
}
/**
* A user-defined function which returns ignores its first parameter (a
* member) and returns the default member from the second parameter (a
* hierarchy).
*/
public static class AnotherMemberErrorUdf implements UserDefinedFunction {
public String getName() {
return "AnotherMemberError";
}
public String getDescription() {
return "Returns default member from hierarchy, "
+ "specified as a second parameter. "
+ "First parameter - any member from any hierarchy";
}
public Syntax getSyntax() {
return Syntax.Function;
}
public Type getReturnType(Type[] parameterTypes) {
HierarchyType hierType = (HierarchyType) parameterTypes[1];
return MemberType.forType(hierType);
}
public Type[] getParameterTypes() {
return new Type[] {
// The first argument must be a member.
MemberType.Unknown,
// The second argument must be a hierarchy.
HierarchyType.Unknown
};
}
public Object execute(Evaluator evaluator, Argument[] arguments) {
// Simply ignore first parameter
Member member = (Member)arguments[0].evaluate(evaluator);
Util.discard(member);
Hierarchy hierarchy = (Hierarchy)arguments[1].evaluate(evaluator);
return hierarchy.getDefaultMember();
}
public String[] getReservedWords() {
return null;
}
}
/**
* Function that reverses a list of members.
*/
public static class ReverseFunction implements UserDefinedFunction {
public Object execute(Evaluator eval, Argument[] args) {
// Note: must call Argument.evaluateList. If we call
// Argument.evaluate we may get an Iterable.
List<?> list = args[0].evaluateList(eval);
// We do need to copy before we reverse. The list is not guaranteed
// to be mutable.
list = new ArrayList(list);
Collections.reverse(list);
return list;
}
public String getDescription() {
return "Reverses the order of a set";
}
public String getName() {
return "Reverse";
}
public Type[] getParameterTypes() {
return new Type[] {new SetType(MemberType.Unknown)};
}
public String[] getReservedWords() {
return null;
}
public Type getReturnType(Type[] arg0) {
return arg0[0];
}
public Syntax getSyntax() {
return Syntax.Function;
}
}
/**
* Function that is non-static.
*/
public class ReverseFunctionNotStatic extends ReverseFunction {
}
/**
* Function that takes a set of members as argument, and returns a set of
* members.
*/
public static class ReverseIterableFunction extends ReverseFunction {
public Object execute(Evaluator eval, Argument[] args) {
// Note: must call Argument.evaluateList. If we call
// Argument.evaluate we may get an Iterable.
Iterable iterable = args[0].evaluateIterable(eval);
List<Object> list = new ArrayList<Object>();
for (Object o : iterable) {
list.add(o);
}
Collections.reverse(list);
return list;
}
}
/**
* Function that takes a member and returns a name.
*/
public static class MemberNameFunction implements UserDefinedFunction {
public Object execute(Evaluator eval, Argument[] args) {
Member member = (Member) args[0].evaluate(eval);
return member.getName();
}
public String getDescription() {
return "Returns the name of a member";
}
public String getName() {
return "MemberName";
}
public Type[] getParameterTypes() {
return new Type[] {MemberType.Unknown};
}
public String[] getReservedWords() {
return null;
}
public Type getReturnType(Type[] arg0) {
return new StringType();
}
public Syntax getSyntax() {
return Syntax.Function;
}
}
/**
* Member formatter for test purposes. Returns name of the member prefixed
* with "foo" and suffixed with "bar".
*/
public static class FooBarMemberFormatter implements MemberFormatter {
public String formatMember(Member member) {
return "foo" + member.getName() + "bar";
}
}
/**
* Cell formatter for test purposes. Returns value of the cell prefixed
* with "foo" and suffixed with "bar".
*/
public static class FooBarCellFormatter implements CellFormatter {
public String formatCell(Object value) {
return "foo" + value + "bar";
}
}
/**
* Property formatter for test purposes. Returns name of the member and
* property, then the value, prefixed with "foo" and suffixed with "bar".
*/
public static class FooBarPropertyFormatter implements PropertyFormatter {
public String formatProperty(
Member member, String propertyName, Object propertyValue)
{
return "foo" + member.getName() + "/" + propertyName + "/"
+ propertyValue + "bar";
}
}
}
// End UdfTest.java