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(); }