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

Example 31 with Dialect

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

the class FastBatchingCellReaderTest method testDoesDBSupportGroupingSets.

public void testDoesDBSupportGroupingSets() {
    final Dialect dialect = getTestContext().getDialect();
    FastBatchingCellReader fbcr = new FastBatchingCellReader(e, salesCube, aggMgr) {

        Dialect getDialect() {
            return dialect;
        }
    };
    switch(dialect.getDatabaseProduct()) {
        case ORACLE:
        case TERADATA:
        case DB2:
        case DB2_AS400:
        case DB2_OLD_AS400:
        case GREENPLUM:
            assertTrue(fbcr.getDialect().supportsGroupingSets());
            break;
        default:
            assertFalse(fbcr.getDialect().supportsGroupingSets());
            break;
    }
}

Example 32 with Dialect

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

the class GroupingSetQueryTest method setUp.

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

Example 33 with Dialect

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

the class AbstractQuerySpec method distinctGenerateSql.

/**
 * Generates a SQL query to retrieve the values in this segment using
 * an algorithm which converts distinct-aggregates to non-distinct
 * aggregates over subqueries.
 *
 * @param outerSqlQuery Query to modify
 * @param countOnly If true, only generate a single row: no need to
 *   generate a GROUP BY clause or put any constraining columns in the
 *   SELECT clause
 * @return A map of aliases used in the inner query if grouping sets
 * were enabled.
 */
protected Map<String, String> distinctGenerateSql(final SqlQuery outerSqlQuery, boolean countOnly) {
    final Dialect dialect = outerSqlQuery.getDialect();
    final Dialect.DatabaseProduct databaseProduct = dialect.getDatabaseProduct();
    final Map<String, String> groupingSetsAliases = new HashMap<String, String>();
    // Generate something like
    // 
    // select d0, d1, count(m0)
    // from (
    // select distinct dim1.x as d0, dim2.y as d1, f.z as m0
    // from f, dim1, dim2
    // where dim1.k = f.k1
    // and dim2.k = f.k2) as dummyname
    // group by d0, d1
    // 
    // or, if countOnly=true
    // 
    // select count(m0)
    // from (
    // select distinct f.z as m0
    // from f, dim1, dim2
    // where dim1.k = f.k1
    // and dim2.k = f.k2) as dummyname
    final SqlQuery innerSqlQuery = newSqlQuery();
    if (databaseProduct == Dialect.DatabaseProduct.GREENPLUM) {
        innerSqlQuery.setDistinct(false);
    } else {
        innerSqlQuery.setDistinct(true);
    }
    // add constraining dimensions
    RolapStar.Column[] columns = getColumns();
    int arity = columns.length;
    for (int i = 0; i < arity; i++) {
        RolapStar.Column column = columns[i];
        RolapStar.Table table = column.getTable();
        if (table.isFunky()) {
            // this is a funky dimension -- ignore for now
            continue;
        }
        table.addToFrom(innerSqlQuery, false, true);
        String expr = column.generateExprString(innerSqlQuery);
        StarColumnPredicate predicate = getColumnPredicate(i);
        final String where = RolapStar.Column.createInExpr(expr, predicate, column.getDatatype(), innerSqlQuery);
        if (!where.equals("true")) {
            innerSqlQuery.addWhere(where);
        }
        if (countOnly) {
            continue;
        }
        String alias = "d" + i;
        alias = innerSqlQuery.addSelect(expr, null, alias);
        if (databaseProduct == Dialect.DatabaseProduct.GREENPLUM) {
            innerSqlQuery.addGroupBy(expr, alias);
        }
        final String quotedAlias = dialect.quoteIdentifier(alias);
        outerSqlQuery.addSelectGroupBy(quotedAlias, null);
        // Add this alias to the map of grouping sets aliases
        groupingSetsAliases.put(expr, dialect.quoteIdentifier("dummyname." + alias));
    }
    // add predicates not associated with columns
    extraPredicates(innerSqlQuery);
    // add measures
    for (int i = 0, count = getMeasureCount(); i < count; i++) {
        RolapStar.Measure measure = getMeasure(i);
        Util.assertTrue(measure.getTable() == getStar().getFactTable());
        measure.getTable().addToFrom(innerSqlQuery, false, true);
        String alias = getMeasureAlias(i);
        String expr = measure.generateExprString(outerSqlQuery);
        innerSqlQuery.addSelect(expr, measure.getInternalType(), alias);
        if (databaseProduct == Dialect.DatabaseProduct.GREENPLUM) {
            innerSqlQuery.addGroupBy(expr, alias);
        }
        outerSqlQuery.addSelect(measure.getAggregator().getNonDistinctAggregator().getExpression(dialect.quoteIdentifier(alias)), measure.getInternalType());
    }
    outerSqlQuery.addFrom(innerSqlQuery, "dummyname", true);
    return groupingSetsAliases;
}

Example 34 with Dialect

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

the class AbstractQuerySpec method nonDistinctGenerateSql.

protected Map<String, String> nonDistinctGenerateSql(SqlQuery sqlQuery) {
    // add constraining dimensions
    RolapStar.Column[] columns = getColumns();
    int arity = columns.length;
    if (countOnly) {
        sqlQuery.addSelect("count(*)", SqlStatement.Type.INT);
    }
    for (int i = 0; i < arity; i++) {
        RolapStar.Column column = columns[i];
        RolapStar.Table table = column.getTable();
        if (table.isFunky()) {
            // this is a funky dimension -- ignore for now
            continue;
        }
        table.addToFrom(sqlQuery, false, true);
        String expr = column.generateExprString(sqlQuery);
        StarColumnPredicate predicate = getColumnPredicate(i);
        final String where = RolapStar.Column.createInExpr(expr, predicate, column.getDatatype(), sqlQuery);
        if (!where.equals("true")) {
            sqlQuery.addWhere(where);
        }
        if (countOnly) {
            continue;
        }
        if (!isPartOfSelect(column)) {
            continue;
        }
        // some DB2 (AS400) versions throw an error, if a column alias is
        // there and *not* used in a subsequent order by/group by
        final Dialect dialect = sqlQuery.getDialect();
        final String alias;
        final Dialect.DatabaseProduct databaseProduct = dialect.getDatabaseProduct();
        if (databaseProduct == Dialect.DatabaseProduct.DB2_AS400) {
            alias = sqlQuery.addSelect(expr, column.getInternalType(), null);
        } else {
            alias = sqlQuery.addSelect(expr, column.getInternalType(), getColumnAlias(i));
        }
        if (isAggregate()) {
            sqlQuery.addGroupBy(expr, alias);
        }
        // Derby has a bug with ORDER BY, so ignore it.
        if (isOrdered()) {
            sqlQuery.addOrderBy(expr, alias, true, false, false, true);
        }
    }
    // Add compound member predicates
    extraPredicates(sqlQuery);
    // add measures
    for (int i = 0, count = getMeasureCount(); i < count; i++) {
        addMeasure(i, sqlQuery);
    }
    return Collections.emptyMap();
}

Example 35 with Dialect

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

the class SqlQueryTest method testToStringForMultipleGroupingSetsSql.

public void testToStringForMultipleGroupingSetsSql() {
    if (!isGroupingSetsSupported()) {
        return;
    }
    final Dialect dialect = getTestContext().getDialect();
    for (boolean b : new boolean[] { false, true }) {
        SqlQuery sqlQuery = new SqlQuery(dialect, b);
        sqlQuery.addSelect("c0", null);
        sqlQuery.addSelect("c1", null);
        sqlQuery.addSelect("c2", null);
        sqlQuery.addSelect("m1", null, "m1");
        sqlQuery.addFromTable("s", "t1", "t1alias", null, null, true);
        sqlQuery.addWhere("a=b");
        sqlQuery.addGroupingFunction("c0");
        sqlQuery.addGroupingFunction("c1");
        sqlQuery.addGroupingFunction("c2");
        ArrayList<String> groupingSetlist1 = new ArrayList<String>();
        groupingSetlist1.add("c0");
        groupingSetlist1.add("c1");
        groupingSetlist1.add("c2");
        sqlQuery.addGroupingSet(groupingSetlist1);
        ArrayList<String> groupingsetsList2 = new ArrayList<String>();
        groupingsetsList2.add("c1");
        groupingsetsList2.add("c2");
        sqlQuery.addGroupingSet(groupingsetsList2);
        String expected;
        if (b) {
            expected = "select\n" + "    c0 as \"c0\",\n" + "    c1 as \"c1\",\n" + "    c2 as \"c2\",\n" + "    m1 as \"m1\",\n" + "    grouping(c0) as \"g0\",\n" + "    grouping(c1) as \"g1\",\n" + "    grouping(c2) as \"g2\"\n" + "from\n" + "    \"s\".\"t1\" =as= \"t1alias\"\n" + "where\n" + "    a=b\n" + "group by grouping sets (\n" + "    (c0, c1, c2),\n" + "    (c1, c2))";
        } else {
            expected = "select c0 as \"c0\", c1 as \"c1\", c2 as \"c2\", m1 as \"m1\", " + "grouping(c0) as \"g0\", grouping(c1) as \"g1\", grouping(c2) as \"g2\" " + "from \"s\".\"t1\" =as= \"t1alias\" where a=b " + "group by grouping sets ((c0, c1, c2), (c1, c2))";
        }
        assertEquals(dialectize(dialect.getDatabaseProduct(), expected), dialectize(sqlQuery.getDialect().getDatabaseProduct(), sqlQuery.toString()));
    }
}