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

Example 41 with Dialect

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

the class SqlConstraintUtils method generateSingleValueInExpr.

/**
 * Generates a multi-value IN expression corresponding to a list of
 * member expressions, and adds the expression to the WHERE clause
 * of a query, provided the member values are all non-null
 *
 * @param sqlQuery query containing the where clause
 * @param baseCube base cube if virtual
 * @param aggStar aggregate star if available
 * @param members list of constraining members
 * @param fromLevel lowest parent level that is unique
 * @param restrictMemberTypes defines the behavior when calculated members
 *        are present
 * @param exclude whether to exclude the members. Default is false.
 * @param includeParentLevels whether to include IN list constraint
 *                            for parent levels.
 * @return a non-empty String if IN list was generated for the members.
 */
private static String generateSingleValueInExpr(SqlQuery sqlQuery, RolapCube baseCube, AggStar aggStar, List<RolapMember> members, RolapLevel fromLevel, boolean restrictMemberTypes, boolean exclude, boolean includeParentLevels) {
    int maxConstraints = MondrianProperties.instance().MaxConstraints.get();
    Dialect dialect = sqlQuery.getDialect();
    String condition = "";
    boolean firstLevel = true;
    for (Collection<RolapMember> c = members; !c.isEmpty(); c = getUniqueParentMembers(c)) {
        RolapMember m = c.iterator().next();
        if (m.isAll()) {
            continue;
        }
        if (m.isNull()) {
            return "1 = 0";
        }
        if (m.isCalculated() && !m.isParentChildLeaf()) {
            if (restrictMemberTypes) {
                throw Util.newInternal("addMemberConstraint: cannot " + "restrict SQL to calculated member :" + m);
            }
            continue;
        }
        boolean containsNullKey = false;
        Iterator<RolapMember> it = c.iterator();
        while (it.hasNext()) {
            m = it.next();
            if (m.getKey() == RolapUtil.sqlNullValue) {
                containsNullKey = true;
            }
        }
        RolapLevel level = m.getLevel();
        RolapHierarchy hierarchy = level.getHierarchy();
        // this method can be called within the context of shared members,
        // outside of the normal rolap star, therefore we need to
        // check the level to see if it is a shared or cube level.
        RolapStar.Column column = null;
        if (level instanceof RolapCubeLevel) {
            column = ((RolapCubeLevel) level).getBaseStarKeyColumn(baseCube);
        }
        String q;
        if (column != null) {
            if (aggStar != null) {
                int bitPos = column.getBitPosition();
                AggStar.Table.Column aggColumn = aggStar.lookupColumn(bitPos);
                if (aggColumn == null) {
                    throw Util.newInternal("AggStar " + aggStar + " has no column for " + column + " (bitPos " + bitPos + ")");
                }
                AggStar.Table table = aggColumn.getTable();
                table.addToFrom(sqlQuery, false, true);
                q = aggColumn.generateExprString(sqlQuery);
            } else {
                RolapStar.Table targetTable = column.getTable();
                hierarchy.addToFrom(sqlQuery, targetTable);
                q = column.generateExprString(sqlQuery);
            }
        } else {
            assert (aggStar == null);
            hierarchy.addToFrom(sqlQuery, level.getKeyExp());
            q = level.getKeyExp().getExpression(sqlQuery);
        }
        StarColumnPredicate cc = getColumnPredicates(column, c);
        if (!dialect.supportsUnlimitedValueList() && cc instanceof ListColumnPredicate && ((ListColumnPredicate) cc).getPredicates().size() > maxConstraints) {
            // Simply get them all, do not create where-clause.
            // Below are two alternative approaches (and code). They
            // both have problems.
            LOG.debug(MondrianResource.instance().NativeSqlInClauseTooLarge.str(level.getUniqueName(), maxConstraints + ""));
            sqlQuery.setSupported(false);
        } else {
            String where = RolapStar.Column.createInExpr(q, cc, level.getDatatype(), sqlQuery);
            if (!where.equals("true")) {
                if (!firstLevel) {
                    if (exclude) {
                        condition += " or ";
                    } else {
                        condition += " and ";
                    }
                } else {
                    firstLevel = false;
                }
                if (exclude) {
                    where = "not (" + where + ")";
                    if (!containsNullKey) {
                        // Null key fails all filters so should add it here
                        // if not already excluded.  E.g., if the original
                        // exclusion filter is :
                        // 
                        // not(year = '1997' and quarter in ('Q1','Q3'))
                        // 
                        // then with IS NULL checks added, the filter
                        // becomes:
                        // 
                        // (not(year = '1997') or year is null) or
                        // (not(quarter in ('Q1','Q3')) or quarter is null)
                        where = "(" + where + " or " + "(" + q + " is null))";
                    }
                }
                condition += where;
            }
        }
        if (m.getLevel().isUnique() || m.getLevel() == fromLevel || !includeParentLevels) {
            // no further qualification needed
            break;
        }
    }
    return condition;
}

Example 42 with Dialect

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

the class XmlaCognosTest method testCognosMDXSuiteHR_002.

public void testCognosMDXSuiteHR_002() throws Exception {
    Dialect dialect = TestContext.instance().getDialect();
    switch(dialect.getDatabaseProduct()) {
        case DERBY:
            // Derby gives right answer, but many cells have wrong xsi:type.
            return;
    }
    executeMDX();
}

Example 43 with Dialect

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

the class XmlaExcel2007Test method filter.

protected String filter(String testCaseName, String filename, String content) {
    if ((testCaseName.startsWith("testMemberPropertiesAndSlicer") || testCaseName.equals("testBugMondrian761")) && filename.equals("response")) {
        Dialect dialect = TestContext.instance().getDialect();
        switch(dialect.getDatabaseProduct()) {
            case MYSQL:
            case MARIADB:
            case VERTICA:
                content = foo(content, "Has_x0020_coffee_x0020_bar", "1", "true");
                content = foo(content, "Has_x0020_coffee_x0020_bar", "0", "false");
                break;
            case ACCESS:
                content = foo(content, "Has_x0020_coffee_x0020_bar", "1", "true");
                content = foo(content, "Has_x0020_coffee_x0020_bar", "0", "false");
                content = foo(content, "Store_x0020_Sqft", "23688", "23688.0");
                content = foo(content, "Grocery_x0020_Sqft", "15337", "15336.753169821777");
                content = foo(content, "Frozen_x0020_Sqft", "5011", "5010.748098106934");
                content = foo(content, "Meat_x0020_Sqft", "3340", "3340.4987320712894");
                content = foo(content, "Store_x0020_Sqft", "23598", "23598.0");
                content = foo(content, "Grocery_x0020_Sqft", "14210", "14210.378025591175");
                content = foo(content, "Frozen_x0020_Sqft", "5633", "5632.5731846452945");
                content = foo(content, "Meat_x0020_Sqft", "3755", "3755.0487897635303");
                break;
        }
    }
    return content;
}

Example 44 with Dialect

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

the class XmlaHandlerTypeTest method testDatatypeConsistency.

/**
 * Checks whether Cell.getValue() returns a consistent datatype whether
 * retrieved from Olap4jXmla, Olap4j, or native Mondrian.
 * @throws SQLException
 */
public void testDatatypeConsistency() throws SQLException {
    TestContext context = getTestContext();
    // MDX cast expressions
    String[] castedTypes = { "Cast(1 as String)", "Cast(1 as Numeric)", "Cast(1 as Boolean)", "Cast(1 as Integer)" };
    for (String castedType : castedTypes) {
        String mdx = "with member measures.type as '" + castedType + "' " + "select measures.type on 0 from sales";
        CellSet olap4jXmlaCellset = context.executeOlap4jXmlaQuery(mdx);
        CellSet olap4jCellset = context.executeOlap4jQuery(mdx);
        Result nativeMondrianResult = context.executeQuery(mdx);
        assertEquals("Checking olap4jXmla datatype against native Mondrian. \n" + "Unexpected datatype when running mdx " + mdx + "\n", nativeMondrianResult.getCell(new int[] { 0 }).getValue().getClass(), olap4jXmlaCellset.getCell(0).getValue().getClass());
        assertEquals("Checking olap4jXmla datatype against native Mondrian. \n" + "Unexpected datatype when running mdx " + mdx + "\n", olap4jXmlaCellset.getCell(0).getValue().getClass(), olap4jCellset.getCell(0).getValue().getClass());
    }
    RolapCube cube = (RolapCube) context.executeQuery("select from sales").getQuery().getCube();
    Dialect dialect = cube.getStar().getSqlQueryDialect();
    if (!dialect.getDatabaseProduct().equals(Dialect.DatabaseProduct.MYSQL) && !dialect.getDatabaseProduct().equals(Dialect.DatabaseProduct.ORACLE)) {
        return;
    }
    // map of sql expressions to the corresponding (optional) datatype
    // attribute (RolapBaseCubeMeasure.Datatype)
    Map<String, String> expressionTypeMap = new HashMap<String, String>();
    expressionTypeMap.put("'StringValue'", "String");
    expressionTypeMap.put("cast(1.0001 as decimal)", null);
    expressionTypeMap.put("cast(1.0001 as decimal)", "Numeric");
    expressionTypeMap.put("cast(10.101 as decimal(10,8))", null);
    expressionTypeMap.put("cast(10.101 as decimal(10,8))", "Numeric");
    for (String expression : expressionTypeMap.keySet()) {
        String query = "Select measures.typeMeasure on 0 from Sales";
        context = getContextWithMeasureExpression(expression, expressionTypeMap.get(expression));
        CellSet olap4jXmlaCellset = context.executeOlap4jXmlaQuery(query);
        CellSet olap4jCellset = context.executeOlap4jQuery(query);
        Result nativeMondrianResult = context.executeQuery(query);
        assertEquals("Checking olap4jXmla datatype against native Mondrian. \n" + "Unexpected datatype for measure expression " + expression + " with datatype attribute " + expressionTypeMap.get(expression) + "\n", nativeMondrianResult.getCell(new int[] { 0 }).getValue().getClass(), olap4jXmlaCellset.getCell(0).getValue().getClass());
        assertEquals("Checking olap4jXmla datatype against olap4j in process. \n" + "Unexpected datatype for expression " + expression + " with datatype attribute " + expressionTypeMap.get(expression) + "\n", olap4jXmlaCellset.getCell(0).getValue().getClass(), olap4jCellset.getCell(0).getValue().getClass());
    }
}

Example 45 with Dialect

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

the class DialectTest method checkRegex.

/**
 * Translates a regular expression into SQL and executes the query.
 * Returns whether the dialect was able to translate the regex.
 *
 * @param regex Java regular expression string
 * @return Whether dialect could translate regex to SQL.
 * @throws SQLException on error
 */
private boolean checkRegex(String regex) throws SQLException {
    Dialect dialect = getDialect();
    final String sqlRegex = dialect.generateRegularExpression(dialect.quoteIdentifier("customer", "fname"), regex);
    if (sqlRegex != null) {
        String sql = "select * from " + dialectizeTableName(dialect.quoteIdentifier("customer")) + " where " + sqlRegex;
        final ResultSet resultSet = getConnection().createStatement().executeQuery(sql);
        assertFalse(resultSet.next());
        resultSet.close();
        return true;
    } else {
        return false;
    }
}