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

Example 51 with SqlPattern

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

the class TestAggregationManager method testFemaleUnitSalesSql.

/**
 * Tests that a request for ([Measures].[Unit Sales], [Gender].[F])
 * generates the correct SQL.
 */
public void testFemaleUnitSalesSql() {
    if (!(MondrianProperties.instance().UseAggregates.get() && MondrianProperties.instance().ReadAggregates.get())) {
        return;
    }
    CellRequest request = createRequest("Sales", "[Measures].[Unit Sales]", "customer", "gender", "F");
    SqlPattern[] patterns = { new SqlPattern(ACCESS_MYSQL, "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` as `agg_g_ms_pcat_sales_fact_1997` " + "where `agg_g_ms_pcat_sales_fact_1997`.`gender` = 'F' " + "group by `agg_g_ms_pcat_sales_fact_1997`.`gender`", 26) };
    assertRequestSql(new CellRequest[] { request }, patterns);
}

Example 52 with SqlPattern

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

the class TestAggregationManager method testAggMembers.

/**
 * Tests that an aggregate table is used to speed up a
 * <code>&lt;Member&gt;.Children</code> expression.
 */
public void testAggMembers() {
    if (MondrianProperties.instance().TestExpDependencies.get() > 0) {
        return;
    }
    if (!(MondrianProperties.instance().UseAggregates.get() && MondrianProperties.instance().ReadAggregates.get())) {
        return;
    }
    if (!(MondrianProperties.instance().EnableNativeCrossJoin.get())) {
        return;
    }
    SqlPattern[] patterns = { new SqlPattern(Dialect.DatabaseProduct.ACCESS, "select `store`.`store_country` as `c0` " + "from `agg_c_14_sales_fact_1997` as `agg_c_14_sales_fact_1997`," + " `store` as `store` " + "where `agg_c_14_sales_fact_1997`.`the_year` = 1998 " + "and `agg_c_14_sales_fact_1997`.`store_id` = `store`.`store_id` " + "group by `store`.`store_country` " + "order by Iif(`store`.`store_country` IS NULL, 1, 0)," + " `store`.`store_country` ASC", 26), new SqlPattern(Dialect.DatabaseProduct.MYSQL, "select `store`.`store_country` as `c0` " + "from `agg_c_14_sales_fact_1997` as `agg_c_14_sales_fact_1997`," + " `store` as `store` " + "where `agg_c_14_sales_fact_1997`.`the_year` = 1998 " + "and `agg_c_14_sales_fact_1997`.`store_id` = `store`.`store_id` " + "group by `store`.`store_country` " + "order by ISNULL(`store`.`store_country`) ASC, `store`.`store_country` ASC", 26) };
    assertQuerySql("select NON EMPTY {[Customers].[USA]} ON COLUMNS,\n" + "       NON EMPTY Crossjoin(Hierarchize(Union({[Store].[All Stores]},\n" + "           [Store].[All Stores].Children)), {[Product].[All Products]}) \n" + "           ON ROWS\n" + "    from [Sales]\n" + "    where ([Measures].[Unit Sales], [Time].[1998])", patterns);
}

Example 53 with SqlPattern

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

the class TestAggregationManager method _testFemaleUnitSalesSql_withAggs.

/**
 * As {@link #testFemaleUnitSalesSql()}, but with aggregate tables switched
 * on.
 *
 * TODO: Enable this test.
 */
private void _testFemaleUnitSalesSql_withAggs() {
    CellRequest request = createRequest("Sales", "[Measures].[Unit Sales]", "customer", "gender", "F");
    SqlPattern[] patterns = { new SqlPattern(ACCESS_MYSQL, "select `customer`.`gender` as `c0`," + " sum(`agg_l_03_sales_fact_1997`.`unit_sales`) as `m0` " + "from `customer` as `customer`," + " `agg_l_03_sales_fact_1997` as `agg_l_03_sales_fact_1997` " + "where `agg_l_03_sales_fact_1997`.`customer_id` = `customer`.`customer_id` " + "and `customer`.`gender` = 'F' " + "group by `customer`.`gender`", 26) };
    assertRequestSql(new CellRequest[] { request }, patterns);
}

Example 54 with SqlPattern

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

the class TestAggregationManager method testCountDistinctAggMiss.

public void testCountDistinctAggMiss() {
    CellRequest request = createRequest("Sales", "[Measures].[Customer Count]", new String[] { "time_by_day", "time_by_day" }, new String[] { "the_year", "quarter" }, new String[] { "1997", "Q1" });
    String accessSql = "select" + " `d0` as `c0`," + " `d1` as `c1`," + " count(`m0`) as `c2` " + "from (" + "select distinct `time_by_day`.`the_year` as `d0`, " + "`time_by_day`.`quarter` as `d1`, " + "`sales_fact_1997`.`customer_id` as `m0` " + "from " + "`time_by_day` as `time_by_day`, " + "`sales_fact_1997` as `sales_fact_1997` " + "where " + "`sales_fact_1997`.`time_id` = `time_by_day`.`time_id` and " + "`time_by_day`.`the_year` = 1997 and " + "`time_by_day`.`quarter` = 'Q1'" + ") as `dummyname` " + "group by `d0`, `d1`";
    String mysqlSql = "select" + " `time_by_day`.`the_year` as `c0`," + " `time_by_day`.`quarter` 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` " + "where `sales_fact_1997`.`time_id` = `time_by_day`.`time_id`" + " and `time_by_day`.`the_year` = 1997" + " and `time_by_day`.`quarter` = 'Q1' " + "group by `time_by_day`.`the_year`," + " `time_by_day`.`quarter`";
    String derbySql = "select " + "\"time_by_day\".\"the_year\" as \"c0\", " + "\"time_by_day\".\"quarter\" 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\" " + "where " + "\"sales_fact_1997\".\"time_id\" = \"time_by_day\".\"time_id\" and " + "\"time_by_day\".\"the_year\" = 1997 and " + "\"time_by_day\".\"quarter\" = 'Q1' " + "group by \"time_by_day\".\"the_year\", \"time_by_day\".\"quarter\"";
    SqlPattern[] patterns = { new SqlPattern(Dialect.DatabaseProduct.ACCESS, accessSql, 26), new SqlPattern(Dialect.DatabaseProduct.MYSQL, mysqlSql, 26), new SqlPattern(Dialect.DatabaseProduct.DERBY, derbySql, derbySql) };
    assertRequestSql(new CellRequest[] { request }, patterns);
}

Example 55 with SqlPattern

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

the class TestAggregationManager method testAggNameApproxRowCount.

/**
 * This is a test for MONDRIAN-918 and MONDRIAN-903. We have added
 * an attribute to AggName called approxRowCount so that the
 * aggregation manager can optimize the aggregation tables without
 * having to issue a select count() query.
 */
public void testAggNameApproxRowCount() {
    propSaver.set(MondrianProperties.instance().UseAggregates, true);
    propSaver.set(MondrianProperties.instance().ReadAggregates, true);
    final TestContext context = TestContext.instance().withSchema("<schema name=\"FooSchema\"><Cube name=\"Sales_Foo\" defaultMeasure=\"Unit Sales\">\n" + "  <Table name=\"sales_fact_1997\">\n" + " <AggName name=\"agg_pl_01_sales_fact_1997\" approxRowCount=\"86000\">\n" + "     <AggFactCount column=\"FACT_COUNT\"/>\n" + "     <AggForeignKey factColumn=\"product_id\" aggColumn=\"PRODUCT_ID\" />\n" + "     <AggForeignKey factColumn=\"customer_id\" aggColumn=\"CUSTOMER_ID\" />\n" + "     <AggForeignKey factColumn=\"time_id\" aggColumn=\"TIME_ID\" />\n" + "     <AggMeasure name=\"[Measures].[Unit Sales]\" column=\"UNIT_SALES_SUM\" />\n" + "     <AggMeasure name=\"[Measures].[Store Cost]\" column=\"STORE_COST_SUM\" />\n" + "     <AggMeasure name=\"[Measures].[Store Sales]\" column=\"STORE_SALES_SUM\" />\n" + " </AggName>\n" + "    <AggExclude name=\"agg_c_special_sales_fact_1997\" />\n" + "    <AggExclude name=\"agg_lc_100_sales_fact_1997\" />\n" + "    <AggExclude name=\"agg_lc_10_sales_fact_1997\" />\n" + "    <AggExclude name=\"agg_pc_10_sales_fact_1997\" />\n" + "  </Table>\n" + "<Dimension name=\"Time\" type=\"TimeDimension\" foreignKey=\"time_id\">\n" + "    <Hierarchy hasAll=\"true\" name=\"Weekly\" primaryKey=\"time_id\">\n" + "      <Table name=\"time_by_day\"/>\n" + "      <Level name=\"Year\" column=\"the_year\" type=\"Numeric\" uniqueMembers=\"true\"\n" + "          levelType=\"TimeYears\"/>\n" + "      <Level name=\"Week\" column=\"week_of_year\" type=\"Numeric\" uniqueMembers=\"false\"\n" + "          levelType=\"TimeWeeks\"/>\n" + "      <Level name=\"Day\" column=\"day_of_month\" uniqueMembers=\"false\" type=\"Numeric\"\n" + "          levelType=\"TimeDays\"/>\n" + "    </Hierarchy>\n" + "</Dimension>\n" + "<Dimension name=\"Product\" foreignKey=\"product_id\">\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" + "      <Level name=\"Product Subcategory\" table=\"product_class\" column=\"product_subcategory\"\n" + "          uniqueMembers=\"false\"/>\n" + "      <Level name=\"Brand Name\" table=\"product\" column=\"brand_name\" uniqueMembers=\"false\"/>\n" + "      <Level name=\"Product Name\" table=\"product\" column=\"product_name\"\n" + "          uniqueMembers=\"true\"/>\n" + "    </Hierarchy>\n" + "</Dimension>\n" + "  <Dimension name=\"Customers\" foreignKey=\"customer_id\">\n" + "    <Hierarchy hasAll=\"true\" allMemberName=\"All Customers\" primaryKey=\"customer_id\">\n" + "      <Table name=\"customer\"/>\n" + "      <Level name=\"Country\" column=\"country\" uniqueMembers=\"true\"/>\n" + "      <Level name=\"State Province\" column=\"state_province\" uniqueMembers=\"true\"/>\n" + "      <Level name=\"City\" column=\"city\" uniqueMembers=\"false\"/>\n" + "      <Level name=\"Name\" column=\"customer_id\" type=\"Numeric\" uniqueMembers=\"true\">\n" + "        <NameExpression>\n" + "          <SQL dialect=\"oracle\">\n" + "\"fname\" || ' ' || \"lname\"\n" + "          </SQL>\n" + "          <SQL dialect=\"hive\">\n" + "`customer`.`fullname`\n" + "          </SQL>\n" + "          <SQL dialect=\"hsqldb\">\n" + "\"fname\" || ' ' || \"lname\"\n" + "          </SQL>\n" + "          <SQL dialect=\"access\">\n" + "fname + ' ' + lname\n" + "          </SQL>\n" + "          <SQL dialect=\"postgres\">\n" + "\"fname\" || ' ' || \"lname\"\n" + "          </SQL>\n" + "          <SQL dialect=\"mysql\">\n" + "CONCAT(`customer`.`fname`, ' ', `customer`.`lname`)\n" + "          </SQL>\n" + "          <SQL dialect=\"mssql\">\n" + "fname + ' ' + lname\n" + "          </SQL>\n" + "          <SQL dialect=\"derby\">\n" + "\"customer\".\"fullname\"\n" + "          </SQL>\n" + "          <SQL dialect=\"db2\">\n" + "CONCAT(CONCAT(\"customer\".\"fname\", ' '), \"customer\".\"lname\")\n" + "          </SQL>\n" + "          <SQL dialect=\"luciddb\">\n" + "\"fname\" || ' ' || \"lname\"\n" + "          </SQL>\n" + "          <SQL dialect=\"neoview\">\n" + "\"customer\".\"fullname\"\n" + "          </SQL>\n" + "          <SQL dialect=\"teradata\">\n" + "\"fname\" || ' ' || \"lname\"\n" + "          </SQL>\n" + "          <SQL dialect=\"generic\">\n" + "fullname\n" + "          </SQL>\n" + "        </NameExpression>\n" + "        <OrdinalExpression>\n" + "          <SQL dialect=\"oracle\">\n" + "\"fname\" || ' ' || \"lname\"\n" + "          </SQL>\n" + "          <SQL dialect=\"hsqldb\">\n" + "\"fname\" || ' ' || \"lname\"\n" + "          </SQL>\n" + "          <SQL dialect=\"access\">\n" + "fname + ' ' + lname\n" + "          </SQL>\n" + "          <SQL dialect=\"postgres\">\n" + "\"fname\" || ' ' || \"lname\"\n" + "          </SQL>\n" + "          <SQL dialect=\"mysql\">\n" + "CONCAT(`customer`.`fname`, ' ', `customer`.`lname`)\n" + "          </SQL>\n" + "          <SQL dialect=\"mssql\">\n" + "fname + ' ' + lname\n" + "          </SQL>\n" + "          <SQL dialect=\"neoview\">\n" + "\"customer\".\"fullname\"\n" + "          </SQL>\n" + "          <SQL dialect=\"derby\">\n" + "\"customer\".\"fullname\"\n" + "          </SQL>\n" + "          <SQL dialect=\"db2\">\n" + "CONCAT(CONCAT(\"customer\".\"fname\", ' '), \"customer\".\"lname\")\n" + "          </SQL>\n" + "          <SQL dialect=\"luciddb\">\n" + "\"fname\" || ' ' || \"lname\"\n" + "          </SQL>\n" + "          <SQL dialect=\"generic\">\n" + "fullname\n" + "          </SQL>\n" + "        </OrdinalExpression>\n" + "        <Property name=\"Gender\" column=\"gender\"/>\n" + "        <Property name=\"Marital Status\" column=\"marital_status\"/>\n" + "        <Property name=\"Education\" column=\"education\"/>\n" + "        <Property name=\"Yearly Income\" column=\"yearly_income\"/>\n" + "      </Level>\n" + "    </Hierarchy>\n" + "  </Dimension>\n" + "  <Measure name=\"Unit Sales\" column=\"unit_sales\" aggregator=\"sum\"\n" + "      formatString=\"Standard\"/>\n" + "  <Measure name=\"Store Cost\" column=\"store_cost\" aggregator=\"sum\"\n" + "      formatString=\"#,###.00\"/>\n" + "  <Measure name=\"Store Sales\" column=\"store_sales\" aggregator=\"sum\"\n" + "      formatString=\"#,###.00\"/>\n" + "  <Measure name=\"Sales Count\" column=\"product_id\" aggregator=\"count\"\n" + "      formatString=\"#,###\"/>\n" + "  <Measure name=\"Customer Count\" column=\"customer_id\"\n" + "      aggregator=\"distinct-count\" formatString=\"#,###\"/>\n" + "</Cube></schema>\n");
    final String mdxQuery = "select {[Measures].[Unit Sales]} on columns, " + "non empty CrossJoin({[Time.Weekly].[1997].[1].[15]},CrossJoin({[Customers].[USA].[CA].[Lincoln Acres].[William Smith]}, {[Product].[Drink].[Beverages].[Carbonated Beverages].[Soda].[Washington].[Washington Diet Cola]})) on rows " + "from [Sales_Foo] ";
    final String sqlOracle = "select count(*) as \"c0\" from \"agg_pl_01_sales_fact_1997\" \"agg_pl_01_sales_fact_1997\"";
    final String sqlMysql = "select count(*) as `c0` from `agg_pl_01_sales_fact_1997` as `agg_pl_01_sales_fact_1997`";
    // If the approxRowcount is used, there should not be
    // a query like : select count(*) from agg_pl_01_sales_fact_1997
    assertQuerySqlOrNot(context, mdxQuery, new SqlPattern[] { new SqlPattern(Dialect.DatabaseProduct.ORACLE, sqlOracle, sqlOracle.length()), new SqlPattern(Dialect.DatabaseProduct.MYSQL, sqlMysql, sqlMysql.length()) }, true, false, false);
}