Examples with Dialect mondrian.spi.Dialect used on opensource projects

Example 51 with Dialect

use of mondrian.spi.Dialect in project mondrian by pentaho.

the class DialectTest method testOracleTypeMapQuirks.

public void testOracleTypeMapQuirks() throws SQLException {
    MockResultSetMetadata mockResultSetMeta = new MockResultSetMetadata();
    Dialect oracleDialect = new OracleDialect();
    assertTrue("Oracle dialect NUMERIC type with 0 precision, 0 scale should map " + "to INT, unless column starts with 'm'", oracleDialect.getType(mockResultSetMeta.withColumnName("c0").withColumnType(Types.NUMERIC).withPrecision(0).withScale(0).build(), 0) == SqlStatement.Type.INT);
    assertTrue("Oracle dialect NUMERIC type with non-zero precision, -127 scale " + " should map to DOUBLE.  MONDRIAN-1044", oracleDialect.getType(mockResultSetMeta.withColumnName("c0").withColumnType(Types.NUMERIC).withPrecision(5).withScale(-127).build(), 0) == SqlStatement.Type.DOUBLE);
    assertTrue("Oracle dialect NUMERIC type with precision less than 10, 0 scale " + " should map to INT. ", oracleDialect.getType(mockResultSetMeta.withColumnName("c0").withColumnType(Types.NUMERIC).withPrecision(9).withScale(0).build(), 0) == SqlStatement.Type.INT);
    assertTrue("Oracle dialect NUMERIC type with precision = 38, scale = 0" + " should map to INT.  38 is a magic number in Oracle " + " for integers of unspecified precision.", oracleDialect.getType(mockResultSetMeta.withColumnName("c0").withColumnType(Types.NUMERIC).withPrecision(38).withScale(0).build(), 0) == SqlStatement.Type.INT);
    assertTrue("Oracle dialect DECIMAL type with precision > 9, scale = 0" + " should map to DOUBLE (unless magic #38)", oracleDialect.getType(mockResultSetMeta.withColumnName("c0").withColumnType(Types.NUMERIC).withPrecision(20).withScale(0).build(), 0) == SqlStatement.Type.DOUBLE);
    assertTrue("Oracle dialect NUMBER type with precision =0 , scale = -127" + " should map to INT.  GROUPING SETS queries can shift" + " scale for columns to -127, whether INT or other NUMERIC." + " Assume INT unless the column name indicates it is a measure.", oracleDialect.getType(mockResultSetMeta.withColumnName("c0").withColumnType(Types.NUMERIC).withPrecision(0).withScale(-127).build(), 0) == SqlStatement.Type.INT);
    assertTrue("Oracle dialect NUMBER type with precision =0 , scale = -127" + " should map to OBJECT if measure name starts with 'm'", oracleDialect.getType(mockResultSetMeta.withColumnName("m0").withColumnType(Types.NUMERIC).withPrecision(0).withScale(-127).build(), 0) == SqlStatement.Type.OBJECT);
}

Example 52 with Dialect

use of mondrian.spi.Dialect in project mondrian by pentaho.

the class DrillThroughTest method testBug1438285.

/**
 * This tests for bug 1438285, "nameColumn cannot be column in level
 * definition".
 */
public void testBug1438285() {
    final Dialect dialect = getTestContext().getDialect();
    if (dialect.getDatabaseProduct() == Dialect.DatabaseProduct.TERADATA) {
        // space to run this query.
        return;
    }
    // Specify the column and nameColumn to be the same
    // in order to reproduce the problem
    final TestContext testContext = TestContext.instance().createSubstitutingCube("Sales", "  <Dimension name=\"Store2\" foreignKey=\"store_id\">\n" + "    <Hierarchy hasAll=\"true\" allMemberName=\"All Stores\" >\n" + "      <Table name=\"store_ragged\"/>\n" + "      <Level name=\"Store Id\" column=\"store_id\" nameColumn=\"store_id\" ordinalColumn=\"region_id\" uniqueMembers=\"true\">\n" + "     </Level>" + "    </Hierarchy>\n" + "  </Dimension>\n");
    Result result = testContext.executeQuery("SELECT {[Measures].[Unit Sales]} on columns, " + "{[Store2].members} on rows FROM [Sales]");
    // Prior to fix the request for the drill through SQL would result in
    // an assertion error
    String sql = result.getCell(new int[] { 0, 0 }).getDrillThroughSQL(true);
    String nameExpStr = getNameExp(result, "Customers", "Name");
    String expectedSql = "select " + "`store`.`store_country` as `Store Country`," + " `store`.`store_state` as `Store State`," + " `store`.`store_city` as `Store City`," + " `store`.`store_name` as `Store Name`," + " `store`.`store_sqft` as `Store Sqft`," + " `store`.`store_type` as `Store Type`," + " `time_by_day`.`the_year` as `Year`," + " `time_by_day`.`quarter` as `Quarter`," + " `time_by_day`.`month_of_year` as `Month`," + " `time_by_day`.`week_of_year` as `Week`," + " `time_by_day`.`day_of_month` as `Day`," + " `product_class`.`product_family` as `Product Family`," + " `product_class`.`product_department` as `Product Department`," + " `product_class`.`product_category` as `Product Category`," + " `product_class`.`product_subcategory` as `Product Subcategory`," + " `product`.`brand_name` as `Brand Name`," + " `product`.`product_name` as `Product Name`," + " `store_ragged`.`store_id` as `Store Id`," + " `promotion`.`media_type` as `Media Type`," + " `promotion`.`promotion_name` as `Promotion Name`," + " `customer`.`country` as `Country`," + " `customer`.`state_province` as `State Province`," + " `customer`.`city` as `City`," + " " + nameExpStr + " as `Name`," + " `customer`.`customer_id` as `Name (Key)`," + " `customer`.`education` as `Education Level`," + " `customer`.`gender` as `Gender`," + " `customer`.`marital_status` as `Marital Status`," + " `customer`.`yearly_income` as `Yearly Income`," + " `sales_fact_1997`.`unit_sales` as `Unit Sales`" + " from `store` =as= `store`," + " `sales_fact_1997` =as= `sales_fact_1997`," + " `time_by_day` =as= `time_by_day`," + " `product_class` =as= `product_class`," + " `product` =as= `product`," + " `store_ragged` =as= `store_ragged`," + " `promotion` =as= `promotion`," + " `customer` =as= `customer`" + " where `sales_fact_1997`.`store_id` = `store`.`store_id`" + " and `sales_fact_1997`.`time_id` = `time_by_day`.`time_id`" + " and `time_by_day`.`the_year` = 1997" + " and `sales_fact_1997`.`product_id` = `product`.`product_id`" + " and `product`.`product_class_id` = `product_class`.`product_class_id`" + " and `sales_fact_1997`.`store_id` = `store_ragged`.`store_id`" + " and `sales_fact_1997`.`promotion_id` = `promotion`.`promotion_id`" + " and `sales_fact_1997`.`customer_id` = `customer`.`customer_id`" + " order by" + (TestContext.instance().getDialect().requiresOrderByAlias() ? " `Store Country` ASC," + " `Store State` ASC," + " `Store City` ASC," + " `Store Name` ASC," + " `Store Sqft` ASC," + " `Store Type` ASC," + " `Year` ASC," + " `Quarter` ASC," + " `Month` ASC," + " `Week` ASC," + " `Day` ASC," + " `Product Family` ASC," + " `Product Department` ASC," + " `Product Category` ASC," + " `Product Subcategory` ASC," + " `Brand Name` ASC," + " `Product Name` ASC," + " `Store Id` ASC," + " `Media Type` ASC," + " `Promotion Name` ASC," + " `Country` ASC," + " `State Province` ASC," + " `City` ASC," + " `Name` ASC," + " `Name (Key)` ASC," + " `Education Level` ASC," + " `Gender` ASC," + " `Marital Status` ASC," + " `Yearly Income` ASC" : " `store`.`store_country` ASC," + " `store`.`store_state` ASC," + " `store`.`store_city` ASC," + " `store`.`store_name` ASC," + " `store`.`store_sqft` ASC," + " `store`.`store_type` ASC," + " `time_by_day`.`the_year` ASC," + " `time_by_day`.`quarter` ASC," + " `time_by_day`.`month_of_year` ASC," + " `time_by_day`.`week_of_year` ASC," + " `time_by_day`.`day_of_month` ASC," + " `product_class`.`product_family` ASC," + " `product_class`.`product_department` ASC," + " `product_class`.`product_category` ASC," + " `product_class`.`product_subcategory` ASC," + " `product`.`brand_name` ASC," + " `product`.`product_name` ASC," + " `store_ragged`.`store_id` ASC," + " `promotion`.`media_type` ASC," + " `promotion`.`promotion_name` ASC," + " `customer`.`country` ASC," + " `customer`.`state_province` ASC," + " `customer`.`city` ASC," + " " + nameExpStr + " ASC," + " `customer`.`customer_id` ASC," + " `customer`.`education` ASC," + " `customer`.`gender` ASC," + " `customer`.`marital_status` ASC," + " `customer`.`yearly_income` ASC");
    getTestContext().assertSqlEquals(expectedSql, sql, 86837);
}

Example 53 with Dialect

use of mondrian.spi.Dialect in project mondrian by pentaho.

the class ClearViewBase method buildSqlPatternArray.

private SqlPattern[] buildSqlPatternArray() {
    DiffRepository diffRepos = getDiffRepos();
    Dialect d = getTestContext().getDialect();
    Dialect.DatabaseProduct dialect = d.getDatabaseProduct();
    String testCaseName = getName();
    String sql = diffRepos.get(testCaseName, "expectedSql", dialect.name());
    if (sql != null) {
        sql = sql.replaceAll("[ \t\n\f\r]+", " ").trim();
        return new SqlPattern[] { new SqlPattern(dialect, sql, null) };
    }
    return null;
}