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

Example 6 with Dialect

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

the class DialectTest method testComplexRegularExpression.

/**
 * This is a test for
 * <a href="http://jira.pentaho.com/browse/MONDRIAN-1057">
 * http://jira.pentaho.com/browse/MONDRIAN-1057</a>
 * Some dialects are not removing the \Q and \E markers if they
 * are in the middle of the regexp.
 */
public void testComplexRegularExpression() throws Exception {
    final String regexp = "(?i).*\\QJeanne\\E.*|.*\\QSheri\\E.*|.*\\QJonathan\\E.*|.*\\QJewel\\E.*";
    Dialect dialect = getDialect();
    if (dialect.allowsRegularExpressionInWhereClause()) {
        assertNotNull(dialect.generateRegularExpression(dialect.quoteIdentifier("customer", "fname"), regexp));
        StringBuilder sb = new StringBuilder("select " + dialect.quoteIdentifier("customer", "fname") + " from " + dialectizeTableName(dialect.quoteIdentifier("customer")) + " group by " + dialect.quoteIdentifier("customer", "fname") + " having " + dialect.generateRegularExpression(dialect.quoteIdentifier("customer", "fname"), regexp));
        final ResultSet resultSet = getConnection().createStatement().executeQuery(sb.toString());
        int i = 0;
        while (resultSet.next()) {
            i++;
        }
        assertEquals(7, i);
        resultSet.close();
    } else {
        assertNull(dialect.generateRegularExpression("Foo", "(?i).*\\QBar\\E.*"));
    }
}

Example 7 with Dialect

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

the class DialectTest method testAllowsSelectNotInGroupBy.

/**
 * Unit test for {@link Dialect#allowsSelectNotInGroupBy}.
 */
public void testAllowsSelectNotInGroupBy() throws SQLException {
    Dialect dialect = getDialect();
    String sql = "select " + dialect.quoteIdentifier("time_id") + ", " + dialect.quoteIdentifier("the_month") + " from " + dialectizeTableName(dialect.quoteIdentifier("time_by_day")) + " group by " + dialect.quoteIdentifier("time_id");
    if (dialect.allowsSelectNotInGroupBy()) {
        final ResultSet resultSet = getConnection().createStatement().executeQuery(sql);
        assertTrue(resultSet.next());
        resultSet.close();
    } else {
        String[] errs = { // oracle
        "ORA-00979: not a GROUP BY expression\n", // derby
        "The SELECT list of a grouped query contains at least one " + "invalid expression. If a SELECT list has a GROUP BY, the " + "list may only contain valid grouping expressions and valid " + "aggregate expressions.  ", // hive
        "(?s).*line 1:18 Expression Not In Group By Key `the_month`.*", // hsqldb
        "(?s)Not in aggregate function or group by clause: .*", // mysql (if sql_mode contains ONLY_FULL_GROUP_BY)
        "ERROR 1055 (42000): 'foodmart.time_by_day.the_month' isn't in " + "GROUP BY", // access
        "\\[Microsoft\\]\\[ODBC Microsoft Access Driver\\] You tried " + "to execute a query that does not include the specified " + "expression 'the_month' as part of an aggregate function.", // luciddb
        "From line 1, column 19 to line 1, column 29: Expression " + "'the_month' is not being grouped", // neoview
        ".* ERROR\\[4005\\] Column reference \"the_month\" must be a " + "grouping column or be specified within an aggregate. .*", // teradata
        ".*Selected non-aggregate values must be part of the " + "associated group.", // Greenplum & postgresql
        "(?s).*ERROR: column \"time_by_day.the_month\" must appear in " + "the GROUP BY clause or be used in an aggregate function.*", // Vectorwise
        "line 1, The columns in the SELECT clause must be contained in the GROUP BY clause\\.", // MonetDB
        "SELECT: cannot use non GROUP BY column 'the_month' in query results without an aggregate function", // SQL Server 2008
        "Column 'time_by_day.the_month' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.", // impala
        "(?s).*select list expression not produced by aggregation output.*missing from GROUP BY clause.*", // NuoDB
        "(?s).*scolumn mondrian.time_by_day.the_month must appear in the GROUP BY clause or be used in an aggregate function.*", // Vertica 6
        "(?s).*ERROR: Column \"time_by_day.the_month\" must appear in " + "the GROUP BY clause or be used in an aggregate function.*", // BigQuery
        "(?s).*SELECT list expression references column the_month which is neither grouped nor aggregated.*" };
        assertQueryFails(sql, errs);
    }
}

Example 8 with Dialect

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

the class DialectTest method testJdbcDialectTypeMap.

public void testJdbcDialectTypeMap() throws SQLException {
    MockResultSetMetadata mockResultSetMeta = new MockResultSetMetadata();
    Dialect postgresDialect = new JdbcDialectImpl();
    assertTrue("JdbcDialectImpl NUMERIC/DECIMAL types w/ precision 0-9" + " and scale=0 should return INT", postgresDialect.getType(mockResultSetMeta.withColumnType(Types.NUMERIC).withPrecision(5).withScale(0).build(), 0) == SqlStatement.Type.INT);
    assertTrue("JdbcDialectImpl NUMERIC/DECIMAL types w/ precision 0-9" + " and scale=0 should return INT", postgresDialect.getType(mockResultSetMeta.withColumnType(Types.DECIMAL).withPrecision(5).withScale(0).build(), 0) == SqlStatement.Type.INT);
}

Example 9 with Dialect

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

the class DialectTest method testPostgresGreenplumTypeMapQuirks.

public void testPostgresGreenplumTypeMapQuirks() throws SQLException {
    MockResultSetMetadata mockResultSetMeta = new MockResultSetMetadata();
    Dialect greenplumDialect = TestContext.getFakeDialect(Dialect.DatabaseProduct.GREENPLUM);
    assertTrue("Postgres/Greenplum dialect NUMBER with precision =0, scale = 0" + ", measure name starts with 'm' maps to OBJECT", greenplumDialect.getType(mockResultSetMeta.withColumnName("m0").withColumnType(Types.NUMERIC).withPrecision(0).withScale(0).build(), 0) == SqlStatement.Type.OBJECT);
}

Example 10 with Dialect

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

the class DrillThroughTest method testTruncateLevelName.

/**
 * Tests that long levels do not result in column aliases larger than the
 * database can handle. For example, Access allows maximum of 64; Oracle
 * allows 30.
 *
 * <p>Testcase for bug 1893959, "Generated drill-through columns too long
 * for DBMS".
 *
 * @throws Exception on error
 */
public void testTruncateLevelName() throws Exception {
    TestContext testContext = TestContext.instance().createSubstitutingCube("Sales", "  <Dimension name=\"Education Level2\" foreignKey=\"customer_id\">\n" + "    <Hierarchy hasAll=\"true\" primaryKey=\"customer_id\">\n" + "      <Table name=\"customer\"/>\n" + "      <Level name=\"Education Level but with a very long name that will be too long if converted directly into a column\" column=\"education\" uniqueMembers=\"true\"/>\n" + "    </Hierarchy>\n" + "  </Dimension>", null);
    Result result = testContext.executeQuery("SELECT {[Measures].[Unit Sales]} on columns,\n" + "{[Education Level2].Children} on rows\n" + "FROM [Sales]\n" + "WHERE ([Time].[1997].[Q1].[1], [Product].[Non-Consumable].[Carousel].[Specialty].[Sunglasses].[ADJ].[ADJ Rosy Sunglasses]) ");
    String sql = result.getCell(new int[] { 0, 0 }).getDrillThroughSQL(false);
    // Check that SQL is valid.
    java.sql.Connection connection = null;
    try {
        DataSource dataSource = getConnection().getDataSource();
        connection = dataSource.getConnection();
        final Statement statement = connection.createStatement();
        final ResultSet resultSet = statement.executeQuery(sql);
        final int columnCount = resultSet.getMetaData().getColumnCount();
        final Dialect dialect = testContext.getDialect();
        if (dialect.getDatabaseProduct() == Dialect.DatabaseProduct.DERBY) {
            // derby counts ORDER BY columns as columns. insane!
            assertEquals(11, columnCount);
        } else {
            assertEquals(6, columnCount);
        }
        final String columnName = resultSet.getMetaData().getColumnLabel(5);
        assertTrue(columnName, columnName.startsWith("Education Level but with a"));
        int n = 0;
        while (resultSet.next()) {
            ++n;
        }
        assertEquals(2, n);
    } finally {
        if (connection != null) {
            connection.close();
        }
    }
}