public void testQueryWithIndex() {
final Connection conn = getConnection();
// Query using name
final String queryStr1 =
"SELECT {[Measures].[Org Salary], [Measures].[Count]} "
+ "ON COLUMNS, "
+ "{[Employees].[Sheri Nowmer]} "
+ "ON ROWS FROM [HR]";
final Query query1 = conn.parseQuery(queryStr1);
final Result result1 = conn.execute(query1);
// Query using key
final String queryStr2 =
"SELECT {[Measures].[Org Salary], [Measures].[Count]} "
+ "ON COLUMNS, "
+ "{[Employees].&[1]} "
+ "ON ROWS FROM [HR]";
final Query query2 = conn.parseQuery(queryStr2);
final Result result2 = conn.execute(query2);
// Results of two previous queries must be the same
assertEquals(
result2.getCell(new int[] {0, 0}).getValue(), result1.getCell(new int[] {0, 0}).getValue());
assertEquals(
result2.getCell(new int[] {1, 0}).getValue(), result1.getCell(new int[] {1, 0}).getValue());
}
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;
}
protected RolapStar.Measure getMeasure(String cube, String measureName) {
final Connection connection = getFoodMartConnection();
final boolean fail = true;
Cube salesCube = connection.getSchema().lookupCube(cube, fail);
Member measure =
salesCube
.getSchemaReader(null)
.getMemberByUniqueName(Util.parseIdentifier(measureName), fail);
return RolapStar.getStarMeasure(measure);
}
/*
* (non-Javadoc)
*
* @see org.pentaho.connection.IPentahoConnection#executeQuery(java.lang.String)
*/
public IPentahoResultSet executeQuery(final String query) {
Query mdxQuery = nativeConnection.parseQuery(query);
Result result = nativeConnection.execute(mdxQuery);
resultSet = new MDXResultSet(result, nativeConnection, useExtendedColumnNames);
return resultSet;
}
/*
* (non-Javadoc)
*
* @see org.pentaho.connection.IPentahoConnection#close()
*/
public void close() {
if (nativeConnection != null) {
nativeConnection.close();
}
}
protected void init(Util.PropertyList properties) {
try {
if (nativeConnection != null) { // Assume we're open
close();
}
// Set a locale for this connection if specified in the platform's mondrian metadata
// This is required if mondrian.i18n.LocalizingDynamicSchemaProcessor is being used
if (properties.get(RolapConnectionProperties.Locale.name()) == null) {
properties.put(
RolapConnectionProperties.Locale.name(), LocaleHelper.getLocale().toString());
}
String dataSourceName = properties.get(RolapConnectionProperties.DataSource.name());
mapPlatformRolesToMondrianRoles(properties);
if (dataSourceName != null) {
IDBDatasourceService datasourceService =
PentahoSystem.getObjectFactory().get(IDBDatasourceService.class, null);
DataSource dataSourceImpl = datasourceService.getDataSource(dataSourceName);
if (dataSourceImpl != null) {
properties.remove(RolapConnectionProperties.DataSource.name());
nativeConnection = DriverManager.getConnection(properties, null, dataSourceImpl);
} else {
nativeConnection = DriverManager.getConnection(properties, null);
}
} else {
nativeConnection = DriverManager.getConnection(properties, null);
}
if (nativeConnection != null) {
if (role != null) {
nativeConnection.setRole(role);
}
}
if (nativeConnection == null) {
logger.error(
Messages.getInstance()
.getErrorString(
"MDXConnection.ERROR_0002_INVALID_CONNECTION",
properties != null
? properties.toString()
: "null")); //$NON-NLS-1$ //$NON-NLS-2$
}
} catch (Throwable t) {
if (logger != null) {
logger.error(
Messages.getInstance()
.getErrorString(
"MDXConnection.ERROR_0002_INVALID_CONNECTION",
properties != null ? properties.toString() : "null"),
t); //$NON-NLS-1$ //$NON-NLS-2$
} else {
Logger.error(
this.getClass().getName(),
Messages.getInstance()
.getErrorString(
"MDXConnection.ERROR_0002_INVALID_CONNECTION",
properties != null ? properties.toString() : "null"),
t); //$NON-NLS-1$ //$NON-NLS-2$
}
}
}
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;
}
}
}
}
/*
* (non-Javadoc)
*
* @see org.pentaho.connection.IPentahoResultSet#closeConnection()
*/
public void closeConnection() {
nativeResultSet.close();
nativeConnection.close();
}
protected Result executeQuery(String mdx, Connection connection) {
Query query = connection.parseQuery(mdx);
query.setResultStyle(ResultStyle.LIST);
return connection.execute(query);
}
/**
* Runs a query twice, with native crossjoin optimization enabled and disabled. If both results
* are equal,and both aggree with the expected result, it is considered correct.
*
* <p>Optionally the query can be run with fresh connection. This is useful if the test case sets
* its certain mondrian properties, e.g. native properties like: mondrian.native.filter.enable
*
* @param resultLimit Maximum result size of all the MDX operations in this query. This might be
* hard to estimate as it is usually larger than the rowCount of the final result. Setting it
* to 0 will cause this limit to be ignored.
* @param rowCount Number of rows returned. (That is, the number of positions on the last axis of
* the query.)
* @param mdx Query
* @param expectedResult Expected result string
* @param freshConnection Whether fresh connection is required
*/
protected void checkNative(
int resultLimit, int rowCount, String mdx, String expectedResult, boolean freshConnection) {
// Don't run the test if we're testing expression dependencies.
// Expression dependencies cause spurious interval calls to
// 'level.getMembers()' which create false negatives in this test.
if (MondrianProperties.instance().TestExpDependencies.get() > 0) {
return;
}
getConnection().getCacheControl(null).flushSchemaCache();
try {
Logger.getLogger(getClass()).debug("*** Native: " + mdx);
boolean reuseConnection = !freshConnection;
Connection con = getTestContext().withSchemaPool(reuseConnection).getConnection();
RolapNativeRegistry reg = getRegistry(con);
reg.useHardCache(true);
TestListener listener = new TestListener();
reg.setListener(listener);
reg.setEnabled(true);
TestCase c = new TestCase(con, resultLimit, rowCount, mdx);
Result result = c.run();
String nativeResult = TestContext.toString(result);
if (!listener.isFoundEvaluator()) {
fail("expected native execution of " + mdx);
}
if (!listener.isExecuteSql()) {
fail("cache is empty: expected SQL query to be executed");
}
if (MondrianProperties.instance().EnableRolapCubeMemberCache.get()) {
// run once more to make sure that the result comes from cache
// now
listener.setExecuteSql(false);
c.run();
if (listener.isExecuteSql()) {
fail("expected result from cache when query runs twice");
}
}
con.close();
Logger.getLogger(getClass()).debug("*** Interpreter: " + mdx);
getConnection().getCacheControl(null).flushSchemaCache();
con = getTestContext().withSchemaPool(false).getConnection();
reg = getRegistry(con);
listener.setFoundEvaluator(false);
reg.setListener(listener);
// disable RolapNativeSet
reg.setEnabled(false);
result = executeQuery(mdx, con);
String interpretedResult = TestContext.toString(result);
if (listener.isFoundEvaluator()) {
fail("did not expect native executions of " + mdx);
}
if (expectedResult != null) {
TestContext.assertEqualsVerbose(
expectedResult,
nativeResult,
false,
"Native implementation returned different result than " + "expected; MDX=" + mdx);
TestContext.assertEqualsVerbose(
expectedResult,
interpretedResult,
false,
"Interpreter implementation returned different result than " + "expected; MDX=" + mdx);
}
if (!nativeResult.equals(interpretedResult)) {
TestContext.assertEqualsVerbose(
interpretedResult,
nativeResult,
false,
"Native implementation returned different result than " + "interpreter; MDX=" + mdx);
}
} finally {
Connection con = getConnection();
RolapNativeRegistry reg = getRegistry(con);
reg.setEnabled(true);
reg.useHardCache(false);
}
}
RolapNativeRegistry getRegistry(Connection connection) {
RolapCube cube = (RolapCube) connection.getSchema().lookupCube("Sales", true);
RolapSchemaReader schemaReader = (RolapSchemaReader) cube.getSchemaReader();
return schemaReader.getSchema().getNativeRegistry();
}
protected RolapCube getCube(final String cube) {
final Connection connection = getFoodMartConnection();
final boolean fail = true;
return (RolapCube) connection.getSchema().lookupCube(cube, fail);
}
/**
* During MDX query parse and execution, checks that the query results (or does not result) in a
* particular SQL statement being generated.
*
* <p>Parses and executes the MDX query once for each SQL pattern in the current dialect. If there
* are multiple patterns, runs the MDX query multiple times, and expects to see each SQL statement
* appear. If there are no patterns in this dialect, the test trivially succeeds.
*
* @param testContext non-default test context if required
* @param mdxQuery MDX query
* @param patterns Set of patterns
* @param negative false to assert if SQL is generated; true to assert if SQL is NOT generated
* @param bypassSchemaCache whether to grab a new connection and bypass the schema cache before
* parsing the MDX query
* @param clearCache whether to clear cache before executing the MDX query
*/
protected void assertQuerySqlOrNot(
TestContext testContext,
String mdxQuery,
SqlPattern[] patterns,
boolean negative,
boolean bypassSchemaCache,
boolean clearCache) {
Connection connection = testContext.getConnection();
mdxQuery = testContext.upgradeQuery(mdxQuery);
// Run the test once for each pattern in this dialect.
// (We could optimize and run it once, collecting multiple queries, and
// comparing all queries at the end.)
Dialect dialect = testContext.getDialect();
Dialect.DatabaseProduct d = dialect.getDatabaseProduct();
boolean patternFound = false;
for (SqlPattern sqlPattern : patterns) {
if (!sqlPattern.hasDatabaseProduct(d)) {
// If the dialect is not one in the pattern set, skip the
// test. If in the end no pattern is located, print a warning
// message if required.
continue;
}
patternFound = true;
String sql = sqlPattern.getSql();
String trigger = sqlPattern.getTriggerSql();
sql = dialectize(d, sql);
trigger = dialectize(d, trigger);
// Create a dummy DataSource which will throw a 'bomb' if it is
// asked to execute a particular SQL statement, but will otherwise
// behave exactly the same as the current DataSource.
RolapUtil.setHook(new TriggerHook(trigger));
Bomb bomb = null;
try {
if (bypassSchemaCache) {
connection = testContext.withSchemaPool(false).getConnection();
}
final Query query = connection.parseQuery(mdxQuery);
if (clearCache) {
clearCache((RolapCube) query.getCube());
}
final Result result = connection.execute(query);
Util.discard(result);
bomb = null;
} catch (Bomb e) {
bomb = e;
} catch (RuntimeException e) {
// Walk up the exception tree and see if the root cause
// was a SQL bomb.
bomb = Util.getMatchingCause(e, Bomb.class);
if (bomb == null) {
throw e;
}
} finally {
RolapUtil.setHook(null);
}
if (negative) {
if (bomb != null) {
fail("forbidden query [" + sql + "] detected");
}
} else {
if (bomb == null) {
fail("expected query [" + sql + "] did not occur");
}
assertEquals(
replaceQuotes(sql.replaceAll("\r\n", "\n")),
replaceQuotes(bomb.sql.replaceAll("\r\n", "\n")));
}
}
// Print warning message that no pattern was specified for the current
// dialect.
if (!patternFound) {
String warnDialect = MondrianProperties.instance().WarnIfNoPatternForDialect.get();
if (warnDialect.equals(d.toString())) {
System.out.println(
"[No expected SQL statements found for dialect \""
+ dialect.toString()
+ "\" and test not run]");
}
}
}
