Examples with SqlPattern mondrian.test.SqlPattern used on opensource projects

Example 21 with SqlPattern

use of mondrian.test.SqlPattern in project mondrian by pentaho.

the class SqlQueryTest method assertSqlQueryToStringMatches.

private void assertSqlQueryToStringMatches(SqlQuery query, SqlPattern[] patterns) {
    Dialect dialect = getTestContext().getDialect();
    Dialect.DatabaseProduct d = dialect.getDatabaseProduct();
    boolean patternFound = false;
    for (SqlPattern sqlPattern : patterns) {
        if (!sqlPattern.hasDatabaseProduct(d)) {
            // message if required.
            continue;
        }
        patternFound = true;
        String trigger = sqlPattern.getTriggerSql();
        trigger = dialectize(d, trigger);
        assertEquals(dialectize(dialect.getDatabaseProduct(), trigger), dialectize(query.getDialect().getDatabaseProduct(), query.toString()));
    }
    // 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]");
        }
    }
}

Example 22 with SqlPattern

use of mondrian.test.SqlPattern in project mondrian by pentaho.

the class SqlQueryTest method testLimitedRollupMemberRetrievableFromCache.

public void testLimitedRollupMemberRetrievableFromCache() throws Exception {
    final String mdx = "select NON EMPTY { [Store].[Store].[Store State].members } on 0 from [Sales]";
    final TestContext context = TestContext.instance().create(null, null, null, null, null, " <Role name='justCA'>\n" + " <SchemaGrant access='all'>\n" + " <CubeGrant cube='Sales' access='all'>\n" + " <HierarchyGrant hierarchy='[Store]' access='custom' rollupPolicy='partial'>\n" + " <MemberGrant member='[Store].[USA].[CA]' access='all'/>\n" + " </HierarchyGrant>\n" + " </CubeGrant>\n" + " </SchemaGrant>\n" + " </Role>\n").withRole("justCA");
    String pgSql = "select \"store\".\"store_country\" as \"c0\"," + " \"store\".\"store_state\" as \"c1\"" + " from \"sales_fact_1997\" as \"sales_fact_1997\"," + " \"store\" as \"store\" " + "where (\"store\".\"store_country\" = 'USA') " + "and (\"store\".\"store_state\" = 'CA') " + "and \"sales_fact_1997\".\"store_id\" = \"store\".\"store_id\" " + "group by \"store\".\"store_country\", \"store\".\"store_state\" " + "order by \"store\".\"store_country\" ASC NULLS LAST," + " \"store\".\"store_state\" ASC NULLS LAST";
    SqlPattern pgPattern = new SqlPattern(POSTGRESQL, pgSql, pgSql.length());
    String mySql = "select `store`.`store_country` as `c0`," + " `store`.`store_state` as `c1`" + " from `store` as `store`, `sales_fact_1997` as `sales_fact_1997` " + "where `sales_fact_1997`.`store_id` = `store`.`store_id` " + "and `store`.`store_country` = 'USA' " + "and `store`.`store_state` = 'CA' " + "group by `store`.`store_country`, `store`.`store_state` " + "order by ISNULL(`store`.`store_country`) ASC," + " `store`.`store_country` ASC," + " ISNULL(`store`.`store_state`) ASC, `store`.`store_state` ASC";
    SqlPattern myPattern = new SqlPattern(MYSQL, mySql, mySql.length());
    SqlPattern[] patterns = { pgPattern, myPattern };
    context.executeQuery(mdx);
    assertQuerySqlOrNot(context, mdx, patterns, true, false, false);
}

Example 23 with SqlPattern

use of mondrian.test.SqlPattern in project mondrian by pentaho.

the class GroupingSetQueryTest method testGroupingSetForSingleColumnConstraint.

public void testGroupingSetForSingleColumnConstraint() {
    propSaver.set(prop.DisableCaching, false);
    CellRequest request1 = createRequest(cubeNameSales2, measureUnitSales, tableCustomer, fieldGender, "M");
    CellRequest request2 = createRequest(cubeNameSales2, measureUnitSales, tableCustomer, fieldGender, "F");
    CellRequest request3 = createRequest(cubeNameSales2, measureUnitSales, null, "", "");
    SqlPattern[] patternsWithGsets = { new SqlPattern(ORACLE_TERADATA, "select \"customer\".\"gender\" as \"c0\", sum(\"sales_fact_1997\".\"unit_sales\") as \"m0\", " + "grouping(\"customer\".\"gender\") as \"g0\" " + "from \"customer\" =as= \"customer\", \"sales_fact_1997\" =as= \"sales_fact_1997\" " + "where \"sales_fact_1997\".\"customer_id\" = \"customer\".\"customer_id\" " + "group by grouping sets ((\"customer\".\"gender\"), ())", 26) };
    // If aggregates are enabled, mondrian should use them. Results should
    // be the same with or without grouping sets enabled.
    SqlPattern[] patternsWithAggs = { new SqlPattern(ORACLE_TERADATA, "select sum(\"agg_c_10_sales_fact_1997\".\"unit_sales\") as \"m0\"" + " from \"agg_c_10_sales_fact_1997\" \"agg_c_10_sales_fact_1997\"", null), new SqlPattern(ORACLE_TERADATA, "select \"agg_g_ms_pcat_sales_fact_1997\".\"gender\" as \"c0\"," + " sum(\"agg_g_ms_pcat_sales_fact_1997\".\"unit_sales\") as \"m0\" " + "from \"agg_g_ms_pcat_sales_fact_1997\" \"agg_g_ms_pcat_sales_fact_1997\" " + "group by \"agg_g_ms_pcat_sales_fact_1997\".\"gender\"", null) };
    SqlPattern[] patternsWithoutGsets = { new SqlPattern(Dialect.DatabaseProduct.ACCESS, "select \"customer\".\"gender\" as \"c0\", sum(\"sales_fact_1997\".\"unit_sales\") as \"m0\" " + "from \"customer\" as \"customer\", \"sales_fact_1997\" as \"sales_fact_1997\" " + "where \"sales_fact_1997\".\"customer_id\" = \"customer\".\"customer_id\" " + "group by \"customer\".\"gender\"", 26), new SqlPattern(ORACLE_TERADATA, "select \"customer\".\"gender\" as \"c0\", sum(\"sales_fact_1997\".\"unit_sales\") as \"m0\" " + "from \"customer\" =as= \"customer\", \"sales_fact_1997\" =as= \"sales_fact_1997\" " + "where \"sales_fact_1997\".\"customer_id\" = \"customer\".\"customer_id\" " + "group by \"customer\".\"gender\"", 26) };
    propSaver.set(prop.EnableGroupingSets, true);
    if (prop.ReadAggregates.get() && prop.UseAggregates.get()) {
        assertRequestSql(new CellRequest[] { request3, request1, request2 }, patternsWithAggs);
    } else {
        assertRequestSql(new CellRequest[] { request3, request1, request2 }, patternsWithGsets);
    }
    propSaver.set(prop.EnableGroupingSets, false);
    if (prop.ReadAggregates.get() && prop.UseAggregates.get()) {
        assertRequestSql(new CellRequest[] { request3, request1, request2 }, patternsWithAggs);
    } else {
        assertRequestSql(new CellRequest[] { request3, request1, request2 }, patternsWithoutGsets);
    }
}

Example 24 with SqlPattern

use of mondrian.test.SqlPattern in project mondrian by pentaho.

the class GroupingSetQueryTest method testNotUsingGroupingSet.

public void testNotUsingGroupingSet() {
    if (prop.ReadAggregates.get() && prop.UseAggregates.get()) {
        return;
    }
    propSaver.set(prop.EnableGroupingSets, true);
    CellRequest request1 = createRequest(cubeNameSales2, measureUnitSales, tableCustomer, fieldGender, "M");
    CellRequest request2 = createRequest(cubeNameSales2, measureUnitSales, tableCustomer, fieldGender, "F");
    SqlPattern[] patternsWithGsets = { new SqlPattern(ORACLE_TERADATA, "select \"customer\".\"gender\" as \"c0\", sum(\"sales_fact_1997\".\"unit_sales\") as \"m0\" " + "from \"customer\" =as= \"customer\", \"sales_fact_1997\" =as= \"sales_fact_1997\" " + "where \"sales_fact_1997\".\"customer_id\" = \"customer\".\"customer_id\" " + "group by \"customer\".\"gender\"", 72) };
    assertRequestSql(new CellRequest[] { request1, request2 }, patternsWithGsets);
    propSaver.set(prop.EnableGroupingSets, false);
    SqlPattern[] patternsWithoutGsets = { new SqlPattern(Dialect.DatabaseProduct.ACCESS, "select \"customer\".\"gender\" as \"c0\", sum(\"sales_fact_1997\".\"unit_sales\") as \"m0\" " + "from \"customer\" as \"customer\", \"sales_fact_1997\" as \"sales_fact_1997\" " + "where \"sales_fact_1997\".\"customer_id\" = \"customer\".\"customer_id\" " + "group by \"customer\".\"gender\"", 72), new SqlPattern(ORACLE_TERADATA, "select \"customer\".\"gender\" as \"c0\", sum(\"sales_fact_1997\".\"unit_sales\") as \"m0\" " + "from \"customer\" =as= \"customer\", \"sales_fact_1997\" =as= \"sales_fact_1997\" " + "where \"sales_fact_1997\".\"customer_id\" = \"customer\".\"customer_id\" " + "group by \"customer\".\"gender\"", 72) };
    assertRequestSql(new CellRequest[] { request1, request2 }, patternsWithoutGsets);
}

Example 25 with SqlPattern

use of mondrian.test.SqlPattern in project mondrian by pentaho.

the class GroupingSetQueryTest method testGroupingSetForMultipleColumnConstraintAndCompoundConstraint.

public void testGroupingSetForMultipleColumnConstraintAndCompoundConstraint() {
    if (prop.ReadAggregates.get() && prop.UseAggregates.get()) {
        return;
    }
    List<String[]> compoundMembers = new ArrayList<String[]>();
    compoundMembers.add(new String[] { "USA", "OR" });
    compoundMembers.add(new String[] { "CANADA", "BC" });
    CellRequestConstraint constraint = makeConstraintCountryState(compoundMembers);
    CellRequest request1 = createRequest(cubeNameSales2, measureCustomerCount, new String[] { tableCustomer, tableTime }, new String[] { fieldGender, fieldYear }, new String[] { "M", "1997" }, constraint);
    CellRequest request2 = createRequest(cubeNameSales2, measureCustomerCount, new String[] { tableCustomer, tableTime }, new String[] { fieldGender, fieldYear }, new String[] { "F", "1997" }, constraint);
    CellRequest request3 = createRequest(cubeNameSales2, measureCustomerCount, tableTime, fieldYear, "1997", constraint);
    String sqlWithoutGS = "select \"time_by_day\".\"the_year\" as \"c0\", \"customer\".\"gender\" as \"c1\", " + "count(distinct \"sales_fact_1997\".\"customer_id\") as \"m0\" from \"time_by_day\" =as= \"time_by_day\", " + "\"sales_fact_1997\" =as= \"sales_fact_1997\", \"customer\" =as= \"customer\", \"store\" =as= \"store\" " + "where \"sales_fact_1997\".\"time_id\" = \"time_by_day\".\"time_id\" and \"time_by_day\".\"the_year\" = 1997 " + "and \"sales_fact_1997\".\"customer_id\" = \"customer\".\"customer_id\" and " + "\"sales_fact_1997\".\"store_id\" = \"store\".\"store_id\" and " + "((\"store\".\"store_country\" = 'USA' and \"store\".\"store_state\" = 'OR') or " + "(\"store\".\"store_country\" = 'CANADA' and \"store\".\"store_state\" = 'BC')) " + "group by \"time_by_day\".\"the_year\", \"customer\".\"gender\"";
    SqlPattern[] patternsGSDisabled = { new SqlPattern(ORACLE_TERADATA, sqlWithoutGS, sqlWithoutGS) };
    // as of change 12310 GS has been removed from distinct count queries,
    // since there is little or no performance benefit and there is a bug
    // related to it (2207515)
    SqlPattern[] patternsGSEnabled = patternsGSDisabled;
    propSaver.set(prop.EnableGroupingSets, true);
    assertRequestSql(new CellRequest[] { request3, request1, request2 }, patternsGSEnabled);
    propSaver.set(prop.EnableGroupingSets, false);
    assertRequestSql(new CellRequest[] { request3, request1, request2 }, patternsGSDisabled);
}