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

Example 11 with Dialect

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

the class DrillThroughTest method testDrillThroughMeasureExp.

/**
 * Tests that proper SQL is being generated for a Measure specified
 * as an expression.
 */
public void testDrillThroughMeasureExp() {
    Result result = executeQuery("SELECT {[Measures].[Promotion Sales]} on columns,\n" + " {[Product].Children} on rows\n" + "from Sales");
    String sql = result.getCell(new int[] { 0, 0 }).getDrillThroughSQL(false);
    String expectedSql = "select" + " `time_by_day`.`the_year` as `Year`," + " `product_class`.`product_family` as `Product Family`," + " (case when `sales_fact_1997`.`promotion_id` = 0 then 0" + " else `sales_fact_1997`.`store_sales` end)" + " as `Promotion Sales` " + "from `time_by_day` =as= `time_by_day`," + " `sales_fact_1997` =as= `sales_fact_1997`," + " `product_class` =as= `product_class`," + " `product` =as= `product` " + "where `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 `product_class`.`product_family` = 'Drink' " + (TestContext.instance().getDialect().requiresOrderByAlias() ? "order by `Year` ASC, `Product Family` ASC" : "order by `time_by_day`.`the_year` ASC, `product_class`.`product_family` ASC");
    final Cube cube = result.getQuery().getCube();
    RolapStar star = ((RolapCube) cube).getStar();
    // Adjust expected SQL for dialect differences in FoodMart.xml.
    Dialect dialect = star.getSqlQueryDialect();
    final String caseStmt = " \\(case when `sales_fact_1997`.`promotion_id` = 0 then 0" + " else `sales_fact_1997`.`store_sales` end\\)";
    switch(dialect.getDatabaseProduct()) {
        case ACCESS:
            expectedSql = expectedSql.replaceAll(caseStmt, " Iif(`sales_fact_1997`.`promotion_id` = 0, 0," + " `sales_fact_1997`.`store_sales`)");
            break;
        case INFOBRIGHT:
            expectedSql = expectedSql.replaceAll(caseStmt, " `sales_fact_1997`.`store_sales`");
            break;
    }
    getTestContext().assertSqlEquals(expectedSql, sql, 7978);
}

Example 12 with Dialect

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

the class SqlQueryTest method setUp.

protected void setUp() throws Exception {
    super.setUp();
    origWarnIfNoPatternForDialect = prop.WarnIfNoPatternForDialect.get();
    // This test warns of missing sql patterns for MYSQL.
    final Dialect dialect = getTestContext().getDialect();
    if (prop.WarnIfNoPatternForDialect.get().equals("ANY") || dialect.getDatabaseProduct() == MYSQL) {
        prop.WarnIfNoPatternForDialect.set(dialect.getDatabaseProduct().toString());
    } else {
        // Do not warn unless the dialect is "MYSQL", or
        // if the test chooses to warn regardless of the dialect.
        prop.WarnIfNoPatternForDialect.set("NONE");
    }
}

Example 13 with Dialect

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

the class SqlQueryTest method testToStringForForcedIndexHint.

public void testToStringForForcedIndexHint() {
    Map<String, String> hints = new HashMap<String, String>();
    hints.put("force_index", "myIndex");
    String unformattedMysql = "select c1 as `c0`, c2 as `c1` " + "from `s`.`t1` as `t1alias`" + " FORCE INDEX (myIndex)" + " where a=b";
    String formattedMysql = "select\n" + "    c1 as `c0`,\n" + "    c2 as `c1`\n" + "from\n" + "    `s`.`t1` as `t1alias` FORCE INDEX (myIndex)\n" + "where\n" + "    a=b";
    SqlPattern[] unformattedSqlPatterns = { new SqlPattern(MYSQL, unformattedMysql, null) };
    SqlPattern[] formattedSqlPatterns = { new SqlPattern(MYSQL, formattedMysql, null) };
    for (boolean formatted : new boolean[] { false, true }) {
        Dialect dialect = getTestContext().getDialect();
        SqlQuery sqlQuery = new SqlQuery(dialect, formatted);
        sqlQuery.setAllowHints(true);
        sqlQuery.addSelect("c1", null);
        sqlQuery.addSelect("c2", null);
        sqlQuery.addGroupingFunction("gf0");
        sqlQuery.addFromTable("s", "t1", "t1alias", null, hints, true);
        sqlQuery.addWhere("a=b");
        SqlPattern[] expected;
        if (!formatted) {
            expected = unformattedSqlPatterns;
        } else {
            expected = formattedSqlPatterns;
        }
        assertSqlQueryToStringMatches(sqlQuery, expected);
    }
}

Example 14 with Dialect

use of mondrian.spi.Dialect 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 15 with Dialect

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

the class XmlaBasicTest method filter.

protected String filter(String testCaseName, String filename, String content) {
    if (testCaseName.startsWith("testDrillThrough") && filename.equals("response")) {
        // Different databases have slightly different column types, which
        // results in slightly different inferred xml schema for the drill-
        // through result.
        Dialect dialect = TestContext.instance().getDialect();
        switch(dialect.getDatabaseProduct()) {
            case ORACLE:
                content = Util.replace(content, " type=\"xsd:double\"", " type=\"xsd:decimal\"");
                content = Util.replace(content, " type=\"xsd:integer\"", " type=\"xsd:decimal\"");
                break;
            case POSTGRESQL:
                content = Util.replace(content, " sql:field=\"Store Sqft\" type=\"xsd:double\"", " sql:field=\"Store Sqft\" type=\"xsd:integer\"");
                content = Util.replace(content, " sql:field=\"Unit Sales\" type=\"xsd:double\"", " sql:field=\"Unit Sales\" type=\"xsd:decimal\"");
                break;
            case DERBY:
            case HSQLDB:
            case INFOBRIGHT:
            case LUCIDDB:
            case MYSQL:
            case MARIADB:
            case NEOVIEW:
            case NETEZZA:
            case TERADATA:
                content = Util.replace(content, " sql:field=\"Store Sqft\" type=\"xsd:double\"", " sql:field=\"Store Sqft\" type=\"xsd:integer\"");
                content = Util.replace(content, " sql:field=\"Unit Sales\" type=\"xsd:double\"", " sql:field=\"Unit Sales\" type=\"xsd:string\"");
                content = Util.replace(content, " sql:field=\"Week\" type=\"xsd:decimal\"", " sql:field=\"Week\" type=\"xsd:integer\"");
                content = Util.replace(content, " sql:field=\"Day\" type=\"xsd:decimal\"", " sql:field=\"Day\" type=\"xsd:integer\"");
                break;
            case VERTICA:
                // vertica has no int32, bigint is being translated to
                // integer in sqlToXsdType
                content = Util.replace(content, "type=\"xsd:int\"", "type=\"xsd:integer\"");
                content = Util.replace(content, "type=\"xsd:decimal\"", "type=\"xsd:double\"");
                break;
            case ACCESS:
                content = Util.replace(content, " sql:field=\"Week\" type=\"xsd:decimal\"", " sql:field=\"Week\" type=\"xsd:double\"");
                content = Util.replace(content, " sql:field=\"Day\" type=\"xsd:decimal\"", " sql:field=\"Day\" type=\"xsd:integer\"");
                break;
        }
    }
    return content;
}