protected void setUp() throws Exception {
super.setUp(); // parent setUp enabled agg
propSaver.set(propSaver.properties.EnableNativeCrossJoin, true);
propSaver.set(propSaver.properties.EnableNativeNonEmpty, true);
propSaver.set(propSaver.properties.GenerateFormattedSql, true);
TestContext.instance().flushSchemaCache();
}
private RolapCube lookupCube(String cubeName) {
Connection connection = TestContext.instance().getConnection();
for (Cube cube : connection.getSchema().getCubes()) {
if (cube.getName().equals(cubeName)) {
return (RolapCube) cube;
}
}
return null;
}
public void testJndiConnection() throws NamingException {
// Cannot guarantee that this test will work if they have chosen to
// resolve data sources other than by JNDI.
if (MondrianProperties.instance().DataSourceResolverClass.isSet()) {
return;
}
// get a regular connection
Util.PropertyList properties = TestContext.instance().getConnectionProperties().clone();
final StringBuilder buf = new StringBuilder();
final DataSource dataSource = RolapConnection.createDataSource(null, properties, buf);
// Don't know what the connect string is - it differs with database
// and with the user's set up - but we know that it contains a JDBC
// connect string. Best we can do is check that createDataSource is
// setting it to something.
final String desc = buf.toString();
assertTrue(desc, desc.startsWith("Jdbc="));
final List<String> lookupCalls = new ArrayList<String>();
// mock the JNDI naming manager to provide that datasource
THREAD_INITIAL_CONTEXT.set(
// Use lazy initialization. Otherwise during initialization of this
// initial context JNDI tries to create a default initial context
// and bumps into itself coming the other way.
new InitialContext(true) {
public Object lookup(String str) {
lookupCalls.add("Called");
return dataSource;
}
});
// Use the datasource property to connect to the database.
// Remove user and password, because some data sources (those using
// pools) don't allow you to override user.
Util.PropertyList properties2 = TestContext.instance().getConnectionProperties().clone();
properties2.remove(RolapConnectionProperties.Jdbc.name());
properties2.remove(RolapConnectionProperties.JdbcUser.name());
properties2.remove(RolapConnectionProperties.JdbcPassword.name());
properties2.put(RolapConnectionProperties.DataSource.name(), "jnditest");
DriverManager.getConnection(properties2, null);
// if we've made it here with lookupCalls,
// we've successfully used JNDI
assertTrue(lookupCalls.size() > 0);
}
public void testNativeFilterWithCompoundSlicerWithAggs() {
propSaver.set(MondrianProperties.instance().UseAggregates, true);
propSaver.set(MondrianProperties.instance().ReadAggregates, true);
propSaver.set(MondrianProperties.instance().GenerateFormattedSql, true);
final String mdx =
"with member measures.avgQtrs as 'avg( filter( time.quarter.members, measures.[unit sales] > 80))' "
+ "select measures.avgQtrs * gender.members on 0 from sales where head( product.[product name].members, 3)";
if (MondrianProperties.instance().EnableNativeFilter.get()
&& MondrianProperties.instance().EnableNativeNonEmpty.get()) {
final String sqlMysql =
"select\n"
+ " `agg_c_14_sales_fact_1997`.`the_year` as `c0`,\n"
+ " `agg_c_14_sales_fact_1997`.`quarter` as `c1`\n"
+ "from\n"
+ " `agg_c_14_sales_fact_1997` as `agg_c_14_sales_fact_1997`,\n"
+ " `product` as `product`,\n"
+ " `customer` as `customer`\n"
+ "where\n"
+ " `agg_c_14_sales_fact_1997`.`product_id` = `product`.`product_id`\n"
+ "and\n"
+ " `product`.`product_name` in ('Good Imported Beer', 'Good Light Beer', 'Pearl Imported Beer')\n"
+ "and\n"
+ " `agg_c_14_sales_fact_1997`.`customer_id` = `customer`.`customer_id`\n"
+ "and\n"
+ " `customer`.`gender` = 'M'\n"
+ "group by\n"
+ " `agg_c_14_sales_fact_1997`.`the_year`,\n"
+ " `agg_c_14_sales_fact_1997`.`quarter`\n"
+ "having\n"
+ " (sum(`agg_c_14_sales_fact_1997`.`unit_sales`) > 80)\n"
+ "order by\n"
+ (TestContext.instance().getDialect().requiresOrderByAlias()
? " ISNULL(`c0`) ASC, `c0` ASC,\n" + " ISNULL(`c1`) ASC, `c1` ASC"
: " ISNULL(`agg_c_14_sales_fact_1997`.`the_year`) ASC, `agg_c_14_sales_fact_1997`.`the_year` ASC,\n"
+ " ISNULL(`agg_c_14_sales_fact_1997`.`quarter`) ASC, `agg_c_14_sales_fact_1997`.`quarter` ASC");
final SqlPattern[] patterns = mysqlPattern(sqlMysql);
// Make sure the tuples list is using the HAVING clause.
assertQuerySqlOrNot(getTestContext(), mdx, patterns, false, true, true);
}
// Make sure the numbers are right
assertQueryReturns(
mdx,
"Axis #0:\n"
+ "{[Product].[Drink].[Alcoholic Beverages].[Beer and Wine].[Beer].[Good].[Good Imported Beer]}\n"
+ "{[Product].[Drink].[Alcoholic Beverages].[Beer and Wine].[Beer].[Good].[Good Light Beer]}\n"
+ "{[Product].[Drink].[Alcoholic Beverages].[Beer and Wine].[Beer].[Pearl].[Pearl Imported Beer]}\n"
+ "Axis #1:\n"
+ "{[Measures].[avgQtrs], [Gender].[All Gender]}\n"
+ "{[Measures].[avgQtrs], [Gender].[F]}\n"
+ "{[Measures].[avgQtrs], [Gender].[M]}\n"
+ "Row #0: 111\n"
+ "Row #0: \n"
+ "Row #0: \n");
}
public void testVirtualCube() {
// Schema has two cubes sharing a dimension, and a virtual cube built
// over these two cubes.
// Query from the virtual cube.
TestContext testContext =
TestContext.instance()
.create(sharedDimension, cubeA + "\n" + cubeB, virtualCube, null, null, null);
testContext.assertQueryReturns(queryVirtualCube, resultVirtualCube);
}
public void testPooledConnectionWithProperties() throws SQLException {
Util.PropertyList properties = TestContext.instance().getConnectionProperties().clone();
// Only the JDBC-ODBC bridge gives the error necessary for this
// test to succeed. So trivially succeed for all other JDBC
// drivers.
final String jdbc = properties.get("Jdbc");
if (jdbc != null && !jdbc.startsWith("jdbc:odbc:")) {
return;
}
// JDBC-ODBC driver does not support UTF-16, so this test succeeds
// because creating the connection from the DataSource will fail.
properties.put("jdbc.charSet", "UTF-16");
final StringBuilder buf = new StringBuilder();
DataSource dataSource = RolapConnection.createDataSource(null, properties, buf);
final String desc = buf.toString();
assertTrue(desc.startsWith("Jdbc="));
Connection connection;
try {
connection = dataSource.getConnection();
connection.close();
fail("Expected exception");
} catch (SQLException e) {
if (e.getClass().getName().equals("org.apache.commons.dbcp.DbcpException")) {
// This is expected. (We use string-comparison so that the
// compiler doesn't warn about using a deprecated class.)
} else if (e.getClass() == SQLException.class
&& e.getCause() == null
&& e.getMessage() != null
&& e.getMessage().equals("")) {
// This is expected, from a later version of Dbcp.
} else {
fail("Expected exception, but got a different one: " + e);
}
} catch (IllegalArgumentException e) {
handleIllegalArgumentException(properties, e);
} finally {
RolapConnectionPool.instance().clearPool();
}
}
public void testNegativeMatching() throws Exception {
if (!MondrianProperties.instance().EnableNativeFilter.get()) {
// No point testing these if the native filters
// are turned off.
return;
}
final String sqlOracle =
"select \"customer\".\"country\" as \"c0\", \"customer\".\"state_province\" as \"c1\", \"customer\".\"city\" as \"c2\", \"customer\".\"customer_id\" as \"c3\", \"fname\" || ' ' || \"lname\" as \"c4\", \"fname\" || ' ' || \"lname\" as \"c5\", \"customer\".\"gender\" as \"c6\", \"customer\".\"marital_status\" as \"c7\", \"customer\".\"education\" as \"c8\", \"customer\".\"yearly_income\" as \"c9\" from \"customer\" \"customer\" group by \"customer\".\"country\", \"customer\".\"state_province\", \"customer\".\"city\", \"customer\".\"customer_id\", \"fname\" || ' ' || \"lname\", \"customer\".\"gender\", \"customer\".\"marital_status\", \"customer\".\"education\", \"customer\".\"yearly_income\" having NOT(REGEXP_LIKE(\"fname\" || ' ' || \"lname\", '.*jeanne.*', 'i')) order by \"customer\".\"country\" ASC NULLS LAST, \"customer\".\"state_province\" ASC NULLS LAST, \"customer\".\"city\" ASC NULLS LAST, \"fname\" || ' ' || \"lname\" ASC NULLS LAST";
final String sqlPgsql =
"select \"customer\".\"country\" as \"c0\", \"customer\".\"state_province\" as \"c1\", \"customer\".\"city\" as \"c2\", \"customer\".\"customer_id\" as \"c3\", fullname as \"c4\", fullname as \"c5\", \"customer\".\"gender\" as \"c6\", \"customer\".\"marital_status\" as \"c7\", \"customer\".\"education\" as \"c8\", \"customer\".\"yearly_income\" as \"c9\" from \"customer\" as \"customer\" group by \"customer\".\"country\", \"customer\".\"state_province\", \"customer\".\"city\", \"customer\".\"customer_id\", fullname, \"customer\".\"gender\", \"customer\".\"marital_status\", \"customer\".\"education\", \"customer\".\"yearly_income\" having NOT(cast(fullname as text) ~ '(?i).*jeanne.*') order by \"customer\".\"country\" ASC NULLS LAST, \"customer\".\"state_province\" ASC NULLS LAST, \"customer\".\"city\" ASC NULLS LAST, fullname ASC NULLS LAST";
final String sqlMysql =
"select `customer`.`country` as `c0`, `customer`.`state_province` as `c1`, `customer`.`city` as `c2`, `customer`.`customer_id` as `c3`, CONCAT(`customer`.`fname`, ' ', `customer`.`lname`) as `c4`, CONCAT(`customer`.`fname`, ' ', `customer`.`lname`) as `c5`, `customer`.`gender` as `c6`, `customer`.`marital_status` as `c7`, `customer`.`education` as `c8`, `customer`.`yearly_income` as `c9` from `customer` as `customer` group by `customer`.`country`, `customer`.`state_province`, `customer`.`city`, `customer`.`customer_id`, CONCAT(`customer`.`fname`, ' ', `customer`.`lname`), `customer`.`gender`, `customer`.`marital_status`, `customer`.`education`, `customer`.`yearly_income` having NOT(UPPER(c5) REGEXP '.*JEANNE.*') order by "
+ (TestContext.instance().getDialect().requiresOrderByAlias()
? "ISNULL(`c0`) ASC, `c0` ASC, "
+ "ISNULL(`c1`) ASC, `c1` ASC, "
+ "ISNULL(`c2`) ASC, `c2` ASC, "
+ "ISNULL(`c4`) ASC, `c4` ASC"
: "ISNULL(`customer`.`country`) ASC, `customer`.`country` ASC, ISNULL(`customer`.`state_province`) ASC, `customer`.`state_province` ASC, ISNULL(`customer`.`city`) ASC, `customer`.`city` ASC, ISNULL(CONCAT(`customer`.`fname`, ' ', `customer`.`lname`)) ASC, CONCAT(`customer`.`fname`, ' ', `customer`.`lname`) ASC");
SqlPattern[] patterns = {
new SqlPattern(Dialect.DatabaseProduct.ORACLE, sqlOracle, sqlOracle.length()),
new SqlPattern(Dialect.DatabaseProduct.MYSQL, sqlMysql, sqlMysql.length()),
new SqlPattern(Dialect.DatabaseProduct.POSTGRESQL, sqlPgsql, sqlPgsql.length())
};
final String query =
"With\n"
+ "Set [*NATIVE_CJ_SET] as 'Filter([*BASE_MEMBERS_Customers], Not IsEmpty ([Measures].[Unit Sales]))'\n"
+ "Set [*SORTED_COL_AXIS] as 'Order([*CJ_COL_AXIS],[Customers].CurrentMember.OrderKey,BASC,Ancestor([Customers].CurrentMember,[Customers].[City]).OrderKey,BASC)'\n"
+ "Set [*BASE_MEMBERS_Customers] as 'Filter([Customers].[Name].Members,[Customers].CurrentMember.Caption Not Matches (\"(?i).*\\Qjeanne\\E.*\"))'\n"
+ "Set [*BASE_MEMBERS_Measures] as '{[Measures].[*FORMATTED_MEASURE_0]}'\n"
+ "Set [*CJ_COL_AXIS] as 'Generate([*NATIVE_CJ_SET], {([Customers].currentMember)})'\n"
+ "Member [Measures].[*FORMATTED_MEASURE_0] as '[Measures].[Unit Sales]', FORMAT_STRING = 'Standard', SOLVE_ORDER=400\n"
+ "Select\n"
+ "CrossJoin([*SORTED_COL_AXIS],[*BASE_MEMBERS_Measures]) on columns\n"
+ "From [Sales]";
assertQuerySqlOrNot(getTestContext(), query, patterns, false, true, true);
final Result result = executeQuery(query);
final String resultString = TestContext.toString(result);
assertFalse(resultString.contains("Jeanne"));
verifySameNativeAndNot(query, null, getTestContext());
}
public void testNonAllPromotionMembers() {
TestContext testContext =
TestContext.instance()
.createSubstitutingCube(
"Sales",
"<Dimension name=\"Promotions2\" foreignKey=\"promotion_id\">\n"
+ " <Hierarchy hasAll=\"false\" primaryKey=\"promotion_id\">\n"
+ " <Table name=\"promotion\"/>\n"
+ " <Level name=\"Promotion2 Name\" column=\"promotion_name\" uniqueMembers=\"true\"/>\n"
+ " </Hierarchy>\n"
+ "</Dimension>");
testContext.assertQueryReturns(
"select {[Promotion2 Name].[Price Winners], [Promotion2 Name].[Sale Winners]} * {Tail([Time].[Year].Members,3)} ON COLUMNS, "
+ "NON EMPTY Crossjoin({[Store].CurrentMember.Children}, {[Store Type].[All Store Types].Children}) ON ROWS "
+ "from [Sales]",
"Axis #0:\n"
+ "{}\n"
+ "Axis #1:\n"
+ "{[Promotions2].[Price Winners], [Time].[1997]}\n"
+ "{[Promotions2].[Price Winners], [Time].[1998]}\n"
+ "{[Promotions2].[Sale Winners], [Time].[1997]}\n"
+ "{[Promotions2].[Sale Winners], [Time].[1998]}\n"
+ "Axis #2:\n"
+ "{[Store].[USA], [Store Type].[Mid-Size Grocery]}\n"
+ "{[Store].[USA], [Store Type].[Small Grocery]}\n"
+ "{[Store].[USA], [Store Type].[Supermarket]}\n"
+ "Row #0: \n"
+ "Row #0: \n"
+ "Row #0: 444\n"
+ "Row #0: \n"
+ "Row #1: 23\n"
+ "Row #1: \n"
+ "Row #1: \n"
+ "Row #1: \n"
+ "Row #2: 1,271\n"
+ "Row #2: \n"
+ "Row #2: \n"
+ "Row #2: \n");
}
public void testNonPooledConnectionWithProperties() {
Util.PropertyList properties = TestContext.instance().getConnectionProperties().clone();
// Only the JDBC-ODBC bridge gives the error necessary for this
// test to succeed. So trivially succeed for all other JDBC
// drivers.
final String jdbc = properties.get("Jdbc");
if (jdbc != null && !jdbc.startsWith("jdbc:odbc:")) {
return;
}
// This test is just like the test testPooledConnectionWithProperties
// except with non-pooled connections.
properties.put("jdbc.charSet", "UTF-16");
properties.put(RolapConnectionProperties.PoolNeeded.name(), "false");
final StringBuilder buf = new StringBuilder();
DataSource dataSource = RolapConnection.createDataSource(null, properties, buf);
final String desc = buf.toString();
assertTrue(desc.startsWith("Jdbc="));
Connection connection = null;
try {
connection = dataSource.getConnection();
fail("Expected exception");
} catch (SQLException se) {
// this is expected
} catch (IllegalArgumentException e) {
handleIllegalArgumentException(properties, e);
} finally {
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
// ignore
}
}
}
}
public void testConnectSansCatalogFails() {
Util.PropertyList properties = TestContext.instance().getConnectionProperties().clone();
properties.remove(RolapConnectionProperties.Catalog.name());
properties.remove(RolapConnectionProperties.CatalogContent.name());
if (RolapUtil.SQL_LOGGER.isDebugEnabled()) {
RolapUtil.SQL_LOGGER.debug(
this.getName() + "\n [Connection Properties | " + properties + "]\n");
} else {
System.out.println(properties);
}
try {
DriverManager.getConnection(properties, null);
fail("expected exception");
} catch (MondrianException e) {
assertTrue(
e.getMessage()
.indexOf(
"Connect string must contain property 'Catalog' or "
+ "property 'CatalogContent'")
>= 0);
}
}
static TestContext setupMultiColDimCube(
String aggName,
String yearCols,
String qtrCols,
String monthCols,
String monthProp,
String defaultMeasure) {
String cube =
"<?xml version=\"1.0\"?>\n"
+ "<Schema name=\"FoodMart\">\n"
+ " <Dimension name=\"Store\">\n"
+ " <Hierarchy hasAll=\"true\" primaryKey=\"store_id\">\n"
+ " <Table name=\"store\"/>\n"
+ " <Level name=\"Store Country\" column=\"store_country\" uniqueMembers=\"true\"/>\n"
+ " <Level name=\"Store State\" column=\"store_state\" uniqueMembers=\"true\"/>\n"
+ " <Level name=\"Store City\" column=\"store_city\" uniqueMembers=\"false\"/>\n"
+ " <Level name=\"Store Name\" column=\"store_name\" uniqueMembers=\"true\">\n"
+ " <Property name=\"Street address\" column=\"store_street_address\" type=\"String\"/>\n"
+ " </Level>\n"
+ " </Hierarchy>\n"
+ " </Dimension>\n"
+ " <Dimension name=\"Product\">\n"
+ " <Hierarchy hasAll=\"true\" primaryKey=\"product_id\" primaryKeyTable=\"product\">\n"
+ " <Join leftKey=\"product_class_id\" rightKey=\"product_class_id\">\n"
+ " <Table name=\"product\"/>\n"
+ " <Table name=\"product_class\"/>\n"
+ " </Join>\n"
+ " <Level name=\"Product Family\" table=\"product_class\" column=\"product_family\"\n"
+ " uniqueMembers=\"true\"/>\n"
+ " <Level name=\"Product Department\" table=\"product_class\" column=\"product_department\"\n"
+ " uniqueMembers=\"false\"/>\n"
+ " <Level name=\"Product Category\" table=\"product_class\" column=\"product_category\"\n"
+ " uniqueMembers=\"false\"/>\n"
+ " </Hierarchy>\n"
+ " </Dimension>\n"
+ "<Cube name=\"ExtraCol\" defaultMeasure='#DEFMEASURE#'>\n"
+ " <Table name=\"sales_fact_1997\">\n"
+ " #AGGNAME# "
+ " </Table>"
+ " <Dimension name=\"TimeExtra\" foreignKey=\"time_id\">\n"
+ " <Hierarchy hasAll=\"false\" primaryKey=\"time_id\">\n"
+ " <Table name=\"time_by_day\"/>\n"
+ " <Level name=\"Year\" #YEARCOLS# type=\"Numeric\" uniqueMembers=\"true\""
+ " levelType=\"TimeYears\">\n"
+ " </Level>\n"
+ " <Level name=\"Quarter\" #QTRCOLS# uniqueMembers=\"false\""
+ " levelType=\"TimeQuarters\">\n"
+ " </Level>\n"
+ " <Level name=\"Month\" #MONTHCOLS# uniqueMembers=\"false\" type=\"Numeric\""
+ " levelType=\"TimeMonths\">\n"
+ " #MONTHPROP# "
+ " </Level>\n"
+ " </Hierarchy>\n"
+ " </Dimension>\n"
+ " <Dimension name=\"Gender\" foreignKey=\"customer_id\">\n"
+ " <Hierarchy hasAll=\"true\" primaryKey=\"customer_id\">\n"
+ " <Table name=\"customer\"/>\n"
+ " <Level name=\"Gender\" column=\"gender\" uniqueMembers=\"true\"/>\n"
+ " </Hierarchy>\n"
+ " </Dimension> "
+ " <DimensionUsage name=\"Store\" source=\"Store\" foreignKey=\"store_id\"/>"
+ " <DimensionUsage name=\"Product\" source=\"Product\" foreignKey=\"product_id\"/>"
+ "<Measure name=\"Unit Sales\" column=\"unit_sales\" aggregator=\"sum\"\n"
+ " formatString=\"Standard\" visible=\"false\"/>\n"
+ "<Measure name=\"Avg Unit Sales\" column=\"unit_sales\" aggregator=\"avg\"\n"
+ " formatString=\"Standard\" visible=\"false\"/>\n"
+ " <Measure name=\"Store Cost\" column=\"store_cost\" aggregator=\"sum\"\n"
+ " formatString=\"#,###.00\"/>\n"
+ "<Measure name=\"Customer Count\" column=\"customer_id\" aggregator=\"distinct-count\" formatString=\"#,###\"/>"
+ "</Cube>\n"
+ "</Schema>";
cube =
cube.replace("#AGGNAME#", aggName)
.replace("#YEARCOLS#", yearCols)
.replace("#QTRCOLS#", qtrCols)
.replace("#MONTHCOLS#", monthCols)
.replace("#MONTHPROP#", monthProp)
.replace("#DEFMEASURE#", defaultMeasure);
return TestContext.instance().withSchema(cube);
}
public void testDataSourceOverrideUserPass() throws SQLException, NamingException {
// use the datasource property to connect to the database
Util.PropertyList properties = TestContext.instance().getConnectionProperties().clone();
final Dialect dialect = TestContext.instance().getDialect();
if (dialect.getDatabaseProduct() == Dialect.DatabaseProduct.ACCESS) {
// Access doesn't accept user/password, so this test is pointless.
return;
}
final String jdbcUser = properties.get(RolapConnectionProperties.JdbcUser.name());
final String jdbcPassword = properties.get(RolapConnectionProperties.JdbcPassword.name());
if (jdbcUser == null || jdbcPassword == null) {
// Can only run this test if username and password are explicit.
return;
}
// Define a data source with bogus user and password.
properties.put(RolapConnectionProperties.JdbcUser.name(), "bogususer");
properties.put(RolapConnectionProperties.JdbcPassword.name(), "boguspassword");
properties.put(RolapConnectionProperties.PoolNeeded.name(), "false");
final StringBuilder buf = new StringBuilder();
final DataSource dataSource = RolapConnection.createDataSource(null, properties, buf);
final String desc = buf.toString();
assertTrue(desc, desc.startsWith("Jdbc="));
assertTrue(desc, desc.indexOf("JdbcUser=bogususer; JdbcPassword=boguspassword") >= 0);
final String jndiName = "jndiDataSource";
THREAD_INITIAL_CONTEXT.set(
new InitialContext() {
public Object lookup(String str) {
return str.equals(jndiName) ? dataSource : null;
}
});
// Create a property list that we will use for the actual mondrian
// connection. Replace the original JDBC info with the data source we
// just created.
final Util.PropertyList properties2 = new Util.PropertyList();
for (Pair<String, String> entry : properties) {
properties2.put(entry.getKey(), entry.getValue());
}
properties2.remove(RolapConnectionProperties.Jdbc.name());
properties2.put(RolapConnectionProperties.DataSource.name(), jndiName);
// With JdbcUser and JdbcPassword credentials in the mondrian connect
// string, the data source's "user" and "password" properties are
// overridden and the connection succeeds.
properties2.put(RolapConnectionProperties.JdbcUser.name(), jdbcUser);
properties2.put(RolapConnectionProperties.JdbcPassword.name(), jdbcPassword);
mondrian.olap.Connection connection = null;
try {
connection = DriverManager.getConnection(properties2, null);
Query query = connection.parseQuery("select from [Sales]");
final Result result = connection.execute(query);
assertNotNull(result);
} finally {
if (connection != null) {
connection.close();
connection = null;
}
}
// If we don't specify JdbcUser and JdbcPassword in the mondrian
// connection properties, mondrian uses the data source's
// bogus credentials, and the connection fails.
properties2.remove(RolapConnectionProperties.JdbcUser.name());
properties2.remove(RolapConnectionProperties.JdbcPassword.name());
for (String poolNeeded : Arrays.asList("false", "true")) {
// Important to test with & without pooling. Connection pools
// typically do not let you change user, so it's important that
// mondrian handles these right.
properties2.put(RolapConnectionProperties.PoolNeeded.name(), poolNeeded);
try {
connection = DriverManager.getConnection(properties2, null);
fail("Expected exception");
} catch (MondrianException e) {
final String s = TestContext.getStackTrace(e);
assertTrue(
s,
s.indexOf("Error while creating SQL connection: " + "DataSource=jndiDataSource") >= 0);
switch (dialect.getDatabaseProduct()) {
case DERBY:
assertTrue(
s,
s.indexOf(
"Caused by: java.sql.SQLException: " + "Schema 'BOGUSUSER' does not exist")
>= 0);
break;
case ORACLE:
assertTrue(
s,
s.indexOf(
"Caused by: java.sql.SQLException: ORA-01017: "
+ "invalid username/password; logon denied")
>= 0);
break;
case MYSQL:
assertTrue(
s,
s.indexOf(
"Caused by: java.sql.SQLException: Access denied " + "for user 'bogususer'")
>= 0);
break;
case POSTGRESQL:
assertTrue(
s,
s.indexOf(
"Caused by: org.postgresql.util.PSQLException: "
+ "FATAL: password authentication failed for "
+ "user \"bogususer\"")
>= 0);
break;
}
} finally {
if (connection != null) {
connection.close();
connection = null;
}
}
}
}
public void testNativeFilterWithCompoundSlicer_1() {
propSaver.set(MondrianProperties.instance().GenerateFormattedSql, true);
final String mdx =
"with member [measures].[avgQtrs] as 'count(filter([Customers].[Name].Members, [Measures].[Unit Sales] > 0))' "
+ "select [measures].[avgQtrs] on 0 from sales where ( {[Product].[Drink].[Alcoholic Beverages].[Beer and Wine].[Beer], [Product].[Food].[Baked Goods].[Bread].[Muffins]} )";
if (MondrianProperties.instance().EnableNativeFilter.get()
&& MondrianProperties.instance().EnableNativeNonEmpty.get()) {
boolean requiresOrderByAlias = TestContext.instance().getDialect().requiresOrderByAlias();
final String sqlMysql =
propSaver.properties.UseAggregates.get() == false
? "select\n"
+ " `customer`.`country` as `c0`,\n"
+ " `customer`.`state_province` as `c1`,\n"
+ " `customer`.`city` as `c2`,\n"
+ " `customer`.`customer_id` as `c3`,\n"
+ " CONCAT(`customer`.`fname`, ' ', `customer`.`lname`) as `c4`,\n"
+ " CONCAT(`customer`.`fname`, ' ', `customer`.`lname`) as `c5`,\n"
+ " `customer`.`gender` as `c6`,\n"
+ " `customer`.`marital_status` as `c7`,\n"
+ " `customer`.`education` as `c8`,\n"
+ " `customer`.`yearly_income` as `c9`\n"
+ "from\n"
+ " `customer` as `customer`,\n"
+ " `sales_fact_1997` as `sales_fact_1997`,\n"
+ " `time_by_day` as `time_by_day`,\n"
+ " `product_class` as `product_class`,\n"
+ " `product` as `product`\n"
+ "where\n"
+ " `sales_fact_1997`.`customer_id` = `customer`.`customer_id`\n"
+ "and\n"
+ " `sales_fact_1997`.`time_id` = `time_by_day`.`time_id`\n"
+ "and\n"
+ " `time_by_day`.`the_year` = 1997\n"
+ "and\n"
+ " `sales_fact_1997`.`product_id` = `product`.`product_id`\n"
//
+ "and\n"
+ " `product`.`product_class_id` = `product_class`.`product_class_id`\n"
+ "and\n"
+ " `product_class`.`product_family` in ('Drink', 'Food')\n"
+ "and\n"
+ " `product_class`.`product_department` in ('Alcoholic Beverages', 'Baked Goods')\n"
+ "and\n"
+ " `product_class`.`product_category` in ('Beer and Wine', 'Bread')\n"
+ "and\n"
+ " `product_class`.`product_subcategory` in ('Beer', 'Muffins')\n"
+ "group by\n"
+ " `customer`.`country`,\n"
+ " `customer`.`state_province`,\n"
+ " `customer`.`city`,\n"
+ " `customer`.`customer_id`,\n"
+ " CONCAT(`customer`.`fname`, ' ', `customer`.`lname`),\n"
+ " `customer`.`gender`,\n"
+ " `customer`.`marital_status`,\n"
+ " `customer`.`education`,\n"
+ " `customer`.`yearly_income`\n"
+ "having\n"
+ " (sum(`sales_fact_1997`.`unit_sales`) > 0)\n"
// ^^^^ This is what we are interested in. ^^^^
+ "order by\n"
+ (requiresOrderByAlias
? " ISNULL(`c0`) ASC, `c0` ASC,\n"
+ " ISNULL(`c1`) ASC, `c1` ASC,\n"
+ " ISNULL(`c2`) ASC, `c2` ASC,\n"
+ " ISNULL(`c4`) ASC, `c4` ASC"
: " ISNULL(`customer`.`country`) ASC, `customer`.`country` ASC,\n"
+ " ISNULL(`customer`.`state_province`) ASC, `customer`.`state_province` ASC,\n"
+ " ISNULL(`customer`.`city`) ASC, `customer`.`city` ASC,\n"
+ " ISNULL(CONCAT(`customer`.`fname`, ' ', `customer`.`lname`)) ASC, CONCAT(`customer`.`fname`, ' ', `customer`.`lname`) ASC")
: "select\n"
+ " `customer`.`country` as `c0`,\n"
+ " `customer`.`state_province` as `c1`,\n"
+ " `customer`.`city` as `c2`,\n"
+ " `customer`.`customer_id` as `c3`,\n"
+ " CONCAT(`customer`.`fname`, ' ', `customer`.`lname`) as `c4`,\n"
+ " CONCAT(`customer`.`fname`, ' ', `customer`.`lname`) as `c5`,\n"
+ " `customer`.`gender` as `c6`,\n"
+ " `customer`.`marital_status` as `c7`,\n"
+ " `customer`.`education` as `c8`,\n"
+ " `customer`.`yearly_income` as `c9`\n"
+ "from\n"
+ " `customer` as `customer`,\n"
+ " `agg_c_14_sales_fact_1997` as `agg_c_14_sales_fact_1997`,\n"
+ " `product_class` as `product_class`,\n"
+ " `product` as `product`\n"
+ "where\n"
+ " `agg_c_14_sales_fact_1997`.`customer_id` = `customer`.`customer_id`\n"
+ "and\n"
+ " `agg_c_14_sales_fact_1997`.`the_year` = 1997\n"
+ "and\n"
+ " `agg_c_14_sales_fact_1997`.`product_id` = `product`.`product_id`\n"
+ "and\n"
+ " `product`.`product_class_id` = `product_class`.`product_class_id`\n"
+ "and\n"
+ " `product_class`.`product_family` in ('Drink', 'Food')\n"
+ "and\n"
+ " `product_class`.`product_department` in ('Alcoholic Beverages', 'Baked Goods')\n"
+ "and\n"
+ " `product_class`.`product_category` in ('Beer and Wine', 'Bread')\n"
+ "and\n"
+ " `product_class`.`product_subcategory` in ('Beer', 'Muffins')\n"
+ "group by\n"
+ " `customer`.`country`,\n"
+ " `customer`.`state_province`,\n"
+ " `customer`.`city`,\n"
+ " `customer`.`customer_id`,\n"
+ " CONCAT(`customer`.`fname`, ' ', `customer`.`lname`),\n"
+ " `customer`.`gender`,\n"
+ " `customer`.`marital_status`,\n"
+ " `customer`.`education`,\n"
+ " `customer`.`yearly_income`\n"
+ "having\n"
+ " (sum(`agg_c_14_sales_fact_1997`.`unit_sales`) > 0)\n"
// ^^^^ This is what we are interested in. ^^^^
+ "order by\n"
+ " ISNULL(`customer`.`country`) ASC, `customer`.`country` ASC,\n"
+ " ISNULL(`customer`.`state_province`) ASC, `customer`.`state_province` ASC,\n"
+ " ISNULL(`customer`.`city`) ASC, `customer`.`city` ASC,\n"
+ " ISNULL(CONCAT(`customer`.`fname`, ' ', `customer`.`lname`)) ASC, CONCAT(`customer`.`fname`, ' ', `customer`.`lname`) ASC";
final SqlPattern[] patterns = mysqlPattern(sqlMysql);
// Make sure the tuples list is using the HAVING clause.
assertQuerySqlOrNot(getTestContext(), mdx, patterns, false, true, true);
}
// Make sure the numbers are right
assertQueryReturns(
mdx,
"Axis #0:\n"
+ "{[Product].[Drink].[Alcoholic Beverages].[Beer and Wine].[Beer]}\n"
+ "{[Product].[Food].[Baked Goods].[Bread].[Muffins]}\n"
+ "Axis #1:\n"
+ "{[Measures].[avgQtrs]}\n"
+ "Row #0: 1,281\n");
}
public void testPositiveMatching() throws Exception {
if (!MondrianProperties.instance().EnableNativeFilter.get()) {
// No point testing these if the native filters
// are turned off.
return;
}
final String sqlOracle =
"select \"customer\".\"country\" as \"c0\", \"customer\".\"state_province\" as \"c1\", \"customer\".\"city\" as \"c2\", \"customer\".\"customer_id\" as \"c3\", \"fname\" || ' ' || \"lname\" as \"c4\", \"fname\" || ' ' || \"lname\" as \"c5\", \"customer\".\"gender\" as \"c6\", \"customer\".\"marital_status\" as \"c7\", \"customer\".\"education\" as \"c8\", \"customer\".\"yearly_income\" as \"c9\" from \"customer\" \"customer\" group by \"customer\".\"country\", \"customer\".\"state_province\", \"customer\".\"city\", \"customer\".\"customer_id\", \"fname\" || ' ' || \"lname\", \"customer\".\"gender\", \"customer\".\"marital_status\", \"customer\".\"education\", \"customer\".\"yearly_income\" having REGEXP_LIKE(\"fname\" || ' ' || \"lname\", '.*jeanne.*', 'i') order by \"customer\".\"country\" ASC NULLS LAST, \"customer\".\"state_province\" ASC NULLS LAST, \"customer\".\"city\" ASC NULLS LAST, \"fname\" || ' ' || \"lname\" ASC NULLS LAST";
final String sqlPgsql =
"select \"customer\".\"country\" as \"c0\", \"customer\".\"state_province\" as \"c1\", \"customer\".\"city\" as \"c2\", \"customer\".\"customer_id\" as \"c3\", fullname as \"c4\", fullname as \"c5\", \"customer\".\"gender\" as \"c6\", \"customer\".\"marital_status\" as \"c7\", \"customer\".\"education\" as \"c8\", \"customer\".\"yearly_income\" as \"c9\" from \"customer\" as \"customer\" group by \"customer\".\"country\", \"customer\".\"state_province\", \"customer\".\"city\", \"customer\".\"customer_id\", fullname, \"customer\".\"gender\", \"customer\".\"marital_status\", \"customer\".\"education\", \"customer\".\"yearly_income\" having cast(fullname as text) ~ '(?i).*jeanne.*' order by \"customer\".\"country\" ASC NULLS LAST, \"customer\".\"state_province\" ASC NULLS LAST, \"customer\".\"city\" ASC NULLS LAST, fullname ASC NULLS LAST";
final String sqlMysql =
"select `customer`.`country` as `c0`, `customer`.`state_province` as `c1`, `customer`.`city` as `c2`, `customer`.`customer_id` as `c3`, CONCAT(`customer`.`fname`, ' ', `customer`.`lname`) as `c4`, CONCAT(`customer`.`fname`, ' ', `customer`.`lname`) as `c5`, `customer`.`gender` as `c6`, `customer`.`marital_status` as `c7`, `customer`.`education` as `c8`, `customer`.`yearly_income` as `c9` from `customer` as `customer` group by `customer`.`country`, `customer`.`state_province`, `customer`.`city`, `customer`.`customer_id`, CONCAT(`customer`.`fname`, ' ', `customer`.`lname`), `customer`.`gender`, `customer`.`marital_status`, `customer`.`education`, `customer`.`yearly_income` having UPPER(c5) REGEXP '.*JEANNE.*' order by "
+ (TestContext.instance().getDialect().requiresOrderByAlias()
? "ISNULL(`c0`) ASC, `c0` ASC, "
+ "ISNULL(`c1`) ASC, `c1` ASC, "
+ "ISNULL(`c2`) ASC, `c2` ASC, "
+ "ISNULL(`c4`) ASC, `c4` ASC"
: "ISNULL(`customer`.`country`) ASC, `customer`.`country` ASC, ISNULL(`customer`.`state_province`) ASC, `customer`.`state_province` ASC, ISNULL(`customer`.`city`) ASC, `customer`.`city` ASC, ISNULL(CONCAT(`customer`.`fname`, ' ', `customer`.`lname`)) ASC, CONCAT(`customer`.`fname`, ' ', `customer`.`lname`) ASC");
SqlPattern[] patterns = {
new SqlPattern(Dialect.DatabaseProduct.ORACLE, sqlOracle, sqlOracle.length()),
new SqlPattern(Dialect.DatabaseProduct.MYSQL, sqlMysql, sqlMysql.length()),
new SqlPattern(Dialect.DatabaseProduct.POSTGRESQL, sqlPgsql, sqlPgsql.length())
};
final String queryResults =
"Axis #0:\n"
+ "{}\n"
+ "Axis #1:\n"
+ "{[Customers].[USA].[WA].[Issaquah].[Jeanne Derry], [Measures].[*FORMATTED_MEASURE_0]}\n"
+ "{[Customers].[USA].[CA].[Los Angeles].[Jeannette Eldridge], [Measures].[*FORMATTED_MEASURE_0]}\n"
+ "{[Customers].[USA].[CA].[Burbank].[Jeanne Bohrnstedt], [Measures].[*FORMATTED_MEASURE_0]}\n"
+ "{[Customers].[USA].[OR].[Portland].[Jeanne Zysko], [Measures].[*FORMATTED_MEASURE_0]}\n"
+ "{[Customers].[USA].[WA].[Everett].[Jeanne McDill], [Measures].[*FORMATTED_MEASURE_0]}\n"
+ "{[Customers].[USA].[CA].[West Covina].[Jeanne Whitaker], [Measures].[*FORMATTED_MEASURE_0]}\n"
+ "{[Customers].[USA].[WA].[Everett].[Jeanne Turner], [Measures].[*FORMATTED_MEASURE_0]}\n"
+ "{[Customers].[USA].[WA].[Puyallup].[Jeanne Wentz], [Measures].[*FORMATTED_MEASURE_0]}\n"
+ "{[Customers].[USA].[OR].[Albany].[Jeannette Bura], [Measures].[*FORMATTED_MEASURE_0]}\n"
+ "{[Customers].[USA].[WA].[Lynnwood].[Jeanne Ibarra], [Measures].[*FORMATTED_MEASURE_0]}\n"
+ "Row #0: 50\n"
+ "Row #0: 21\n"
+ "Row #0: 31\n"
+ "Row #0: 42\n"
+ "Row #0: 110\n"
+ "Row #0: 59\n"
+ "Row #0: 42\n"
+ "Row #0: 157\n"
+ "Row #0: 146\n"
+ "Row #0: 78\n";
final String query =
"With\n"
+ "Set [*NATIVE_CJ_SET] as 'Filter([*BASE_MEMBERS_Customers], Not IsEmpty ([Measures].[Unit Sales]))'\n"
+ "Set [*SORTED_COL_AXIS] as 'Order([*CJ_COL_AXIS],[Customers].CurrentMember.OrderKey,BASC,Ancestor([Customers].CurrentMember,[Customers].[City]).OrderKey,BASC)'\n"
+ "Set [*BASE_MEMBERS_Customers] as 'Filter([Customers].[Name].Members,[Customers].CurrentMember.Caption Matches (\"(?i).*\\Qjeanne\\E.*\"))'\n"
+ "Set [*BASE_MEMBERS_Measures] as '{[Measures].[*FORMATTED_MEASURE_0]}'\n"
+ "Set [*CJ_COL_AXIS] as 'Generate([*NATIVE_CJ_SET], {([Customers].currentMember)})'\n"
+ "Member [Measures].[*FORMATTED_MEASURE_0] as '[Measures].[Unit Sales]', FORMAT_STRING = 'Standard', SOLVE_ORDER=400\n"
+ "Select\n"
+ "CrossJoin([*SORTED_COL_AXIS],[*BASE_MEMBERS_Measures]) on columns\n"
+ "From [Sales]";
assertQuerySqlOrNot(getTestContext(), query, patterns, false, true, true);
assertQueryReturns(query, queryResults);
verifySameNativeAndNot(query, null, getTestContext());
}
protected Connection getFoodMartConnection() {
return TestContext.instance().getConnection();
}
