Examples with SqlQuery mondrian.rolap.sql.SqlQuery used on opensource projects

Example 11 with SqlQuery

use of mondrian.rolap.sql.SqlQuery in project mondrian by pentaho.

the class AggGen method insertIntoCollapsed.

/**
 * Return the sql code to populate a collapsed dimension table from
 * the fact table.
 */
public String insertIntoCollapsed() {
    StringWriter sw = new StringWriter(512);
    PrintWriter pw = new PrintWriter(sw);
    String prefix = "    ";
    String factTableName = getFactTableName();
    SqlQuery sqlQuery = getSqlQuery();
    pw.print("INSERT INTO ");
    pw.print(makeCollapsedAggregateTableName(getFactTableName()));
    pw.println(" (");
    for (List<JdbcSchema.Table.Column.Usage> list : collapsedColumnUsages.values()) {
        for (JdbcSchema.Table.Column.Usage usage : list) {
            JdbcSchema.Table.Column c = usage.getColumn();
            pw.print(prefix);
            if (usage.usagePrefix != null) {
                pw.print(usage.usagePrefix);
            }
            pw.print(c.getName());
            pw.println(',');
        }
    }
    for (JdbcSchema.Table.Column.Usage usage : measures) {
        JdbcSchema.Table.Column c = usage.getColumn();
        pw.print(prefix);
        String name = getUsageName(usage);
        pw.print(name);
        // pw.print(c.getName());
        pw.println(',');
    }
    // do fact_count
    pw.print(prefix);
    pw.print(getFactCount());
    pw.println(")");
    pw.println("SELECT");
    for (List<JdbcSchema.Table.Column.Usage> list : collapsedColumnUsages.values()) {
        for (JdbcSchema.Table.Column.Usage usage : list) {
            JdbcSchema.Table.Column c = usage.getColumn();
            JdbcSchema.Table t = c.getTable();
            pw.print(prefix);
            pw.print(sqlQuery.getDialect().quoteIdentifier(t.getName(), c.getName()));
            pw.print(" AS ");
            String n = (usage.usagePrefix == null) ? c.getName() : usage.usagePrefix + c.getName();
            pw.print(sqlQuery.getDialect().quoteIdentifier(n));
            pw.println(',');
        }
    }
    for (JdbcSchema.Table.Column.Usage usage : measures) {
        JdbcSchema.Table.Column c = usage.getColumn();
        JdbcSchema.Table t = c.getTable();
        RolapAggregator agg = usage.getAggregator();
        pw.print(prefix);
        pw.print(agg.getExpression(sqlQuery.getDialect().quoteIdentifier(t.getName(), c.getName())));
        pw.print(" AS ");
        pw.print(sqlQuery.getDialect().quoteIdentifier(c.getName()));
        pw.println(',');
    }
    // do fact_count
    pw.print(prefix);
    pw.print("COUNT(*) AS ");
    pw.println(sqlQuery.getDialect().quoteIdentifier(getFactCount()));
    pw.println("FROM ");
    pw.print(prefix);
    pw.print(sqlQuery.getDialect().quoteIdentifier(factTableName));
    pw.print(" ");
    pw.print(sqlQuery.getDialect().quoteIdentifier(factTableName));
    pw.println(',');
    // add dimension tables
    int k = 0;
    for (RolapStar.Table rt : collapsedColumnUsages.keySet()) {
        if (k++ > 0) {
            pw.println(',');
        }
        pw.print(prefix);
        pw.print(sqlQuery.getDialect().quoteIdentifier(rt.getAlias()));
        pw.print(" AS ");
        pw.print(sqlQuery.getDialect().quoteIdentifier(rt.getAlias()));
        // walk up tables
        if (rt.getParentTable() != null) {
            while (rt.getParentTable().getParentTable() != null) {
                rt = rt.getParentTable();
                pw.println(',');
                pw.print(prefix);
                pw.print(sqlQuery.getDialect().quoteIdentifier(rt.getAlias()));
                pw.print(" AS ");
                pw.print(sqlQuery.getDialect().quoteIdentifier(rt.getAlias()));
            }
        }
    }
    pw.println();
    pw.println("WHERE ");
    k = 0;
    for (RolapStar.Table rt : collapsedColumnUsages.keySet()) {
        if (k++ > 0) {
            pw.println(" and");
        }
        RolapStar.Condition cond = rt.getJoinCondition();
        if (cond == null) {
            continue;
        }
        pw.print(prefix);
        pw.print(cond.toString(sqlQuery));
        if (rt.getParentTable() != null) {
            while (rt.getParentTable().getParentTable() != null) {
                rt = rt.getParentTable();
                cond = rt.getJoinCondition();
                pw.println(" and");
                pw.print(prefix);
                pw.print(cond.toString(sqlQuery));
            }
        }
    }
    pw.println();
    pw.println("GROUP BY ");
    k = 0;
    for (List<JdbcSchema.Table.Column.Usage> list : collapsedColumnUsages.values()) {
        for (JdbcSchema.Table.Column.Usage usage : list) {
            if (k++ > 0) {
                pw.println(",");
            }
            JdbcSchema.Table.Column c = usage.getColumn();
            JdbcSchema.Table t = c.getTable();
            String n = (usage.usagePrefix == null) ? c.getName() : usage.usagePrefix + c.getName();
            pw.print(prefix);
            pw.print(sqlQuery.getDialect().quoteIdentifier(t.getName(), n));
        }
    }
    pw.println(';');
    return sw.toString();
}

Example 12 with SqlQuery

use of mondrian.rolap.sql.SqlQuery in project mondrian by pentaho.

the class Recognizer method getFactCountExpr.

/**
 * Given an aggregate table column usage, find the column name of the
 * table's fact count column usage.
 *
 * @param aggUsage Aggregate table column usage
 * @return The name of the column which holds the fact count.
 */
protected String getFactCountExpr(final JdbcSchema.Table.Column.Usage aggUsage) {
    String tableName = aggTable.getName();
    String factCountColumnName = getFactCountColumnName(aggUsage);
    // we want the fact count expression
    MondrianDef.Column column = new MondrianDef.Column(tableName, factCountColumnName);
    SqlQuery sqlQuery = star.getSqlQuery();
    return column.getExpression(sqlQuery);
}

Example 13 with SqlQuery

use of mondrian.rolap.sql.SqlQuery in project mondrian by pentaho.

the class RolapStar method generateSql.

/**
 * Generates a SQL statement to read all instances of the given attributes.
 *
 * <p>The SQL statement is of the form {@code SELECT ... FROM ... JOIN ...
 * GROUP BY ...}. It is useful for populating an aggregate table.
 *
 * @param columnList List of columns (attributes and measures)
 * @param columnNameList List of column names (must have same cardinality
 *     as {@code columnList})
 * @return SQL SELECT statement
 */
public String generateSql(List<Column> columnList, List<String> columnNameList) {
    final SqlQuery query = new SqlQuery(sqlQueryDialect, true);
    query.addFrom(factTable.relation, factTable.relation.getAlias(), false);
    int k = -1;
    for (Column column : columnList) {
        ++k;
        column.table.addToFrom(query, false, true);
        String columnExpr = column.generateExprString(query);
        if (column instanceof Measure) {
            Measure measure = (Measure) column;
            columnExpr = measure.getAggregator().getExpression(columnExpr);
        }
        final String columnName = columnNameList.get(k);
        String alias = query.addSelect(columnExpr, null, columnName);
        if (!(column instanceof Measure)) {
            query.addGroupBy(columnExpr, alias);
        }
    }
    // remove whitespace from query - in particular, the trailing newline
    return query.toString().trim();
}

Example 14 with SqlQuery

use of mondrian.rolap.sql.SqlQuery in project mondrian by pentaho.

the class RolapStatisticsCache method getColumnCardinality.

public long getColumnCardinality(MondrianDef.Relation relation, MondrianDef.Expression expression, long approxCardinality) {
    if (approxCardinality >= 0) {
        return approxCardinality;
    }
    if (relation instanceof MondrianDef.Table && expression instanceof MondrianDef.Column) {
        final MondrianDef.Table table = (MondrianDef.Table) relation;
        final MondrianDef.Column column = (MondrianDef.Column) expression;
        return getColumnCardinality(null, table.schema, table.name, column.name);
    } else {
        final SqlQuery sqlQuery = star.getSqlQuery();
        sqlQuery.setDistinct(true);
        sqlQuery.addSelect(expression.getExpression(sqlQuery), null);
        sqlQuery.addFrom(relation, null, true);
        return getQueryCardinality(sqlQuery.toString());
    }
}

Example 15 with SqlQuery

use of mondrian.rolap.sql.SqlQuery in project mondrian by pentaho.

the class RolapStatisticsCache method getRelationCardinality.

public long getRelationCardinality(MondrianDef.Relation relation, String alias, long approxRowCount) {
    if (approxRowCount >= 0) {
        return approxRowCount;
    }
    if (relation instanceof MondrianDef.Table) {
        final MondrianDef.Table table = (MondrianDef.Table) relation;
        return getTableCardinality(null, table.schema, table.name);
    } else {
        final SqlQuery sqlQuery = star.getSqlQuery();
        sqlQuery.addSelect("*", null);
        sqlQuery.addFrom(relation, null, true);
        return getQueryCardinality(sqlQuery.toString());
    }
}