001/*
002// This software is subject to the terms of the Eclipse Public License v1.0
003// Agreement, available at the following URL:
004// http://www.eclipse.org/legal/epl-v10.html.
005// You must accept the terms of that agreement to use this software.
006//
007// Copyright (C) 2012-2012 Pentaho
008// All Rights Reserved.
009*/
010package mondrian.spi.impl;
011
012import mondrian.rolap.RolapUtil;
013import mondrian.rolap.SqlStatement;
014import mondrian.server.Execution;
015import mondrian.server.Locus;
016import mondrian.spi.Dialect;
017import mondrian.spi.StatisticsProvider;
018
019import java.sql.*;
020import java.util.Arrays;
021import javax.sql.DataSource;
022
023/**
024 * Implementation of {@link mondrian.spi.StatisticsProvider} that generates
025 * SQL queries to count rows and distinct values.
026 */
027public class SqlStatisticsProvider implements StatisticsProvider {
028    public int getTableCardinality(
029        Dialect dialect,
030        DataSource dataSource,
031        String catalog,
032        String schema,
033        String table,
034        Execution execution)
035    {
036        StringBuilder buf = new StringBuilder("select count(*) from ");
037        dialect.quoteIdentifier(buf, catalog, schema, table);
038        final String sql = buf.toString();
039        SqlStatement stmt =
040            RolapUtil.executeQuery(
041                dataSource,
042                sql,
043                new Locus(
044                    execution,
045                    "SqlStatisticsProvider.getTableCardinality",
046                    "Reading row count from table "
047                    + Arrays.asList(catalog, schema, table)));
048        try {
049            ResultSet resultSet = stmt.getResultSet();
050            if (resultSet.next()) {
051                ++stmt.rowCount;
052                return resultSet.getInt(1);
053            }
054            return -1; // huh?
055        } catch (SQLException e) {
056            throw stmt.handle(e);
057        } finally {
058            stmt.close();
059        }
060    }
061
062    public int getQueryCardinality(
063        Dialect dialect,
064        DataSource dataSource,
065        String sql,
066        Execution execution)
067    {
068        final StringBuilder buf = new StringBuilder();
069        buf.append(
070            "select count(*) from (").append(sql).append(")");
071        if (dialect.requiresAliasForFromQuery()) {
072            if (dialect.allowsAs()) {
073                buf.append(" as ");
074            } else {
075                buf.append(" ");
076            }
077            dialect.quoteIdentifier(buf, "init");
078        }
079        final String countSql = buf.toString();
080        SqlStatement stmt =
081            RolapUtil.executeQuery(
082                dataSource,
083                countSql,
084                new Locus(
085                    execution,
086                    "SqlStatisticsProvider.getQueryCardinality",
087                    "Reading row count from query [" + sql + "]"));
088        try {
089            ResultSet resultSet = stmt.getResultSet();
090            if (resultSet.next()) {
091                ++stmt.rowCount;
092                return resultSet.getInt(1);
093            }
094            return -1; // huh?
095        } catch (SQLException e) {
096            throw stmt.handle(e);
097        } finally {
098            stmt.close();
099        }
100    }
101
102    public int getColumnCardinality(
103        Dialect dialect,
104        DataSource dataSource,
105        String catalog,
106        String schema,
107        String table,
108        String column,
109        Execution execution)
110    {
111        final String sql =
112            generateColumnCardinalitySql(
113                dialect, schema, table, column);
114        if (sql == null) {
115            return -1;
116        }
117        SqlStatement stmt =
118            RolapUtil.executeQuery(
119                dataSource,
120                sql,
121                new Locus(
122                    execution,
123                    "SqlStatisticsProvider.getColumnCardinality",
124                    "Reading cardinality for column "
125                    + Arrays.asList(catalog, schema, table, column)));
126        try {
127            ResultSet resultSet = stmt.getResultSet();
128            if (resultSet.next()) {
129                ++stmt.rowCount;
130                return resultSet.getInt(1);
131            }
132            return -1; // huh?
133        } catch (SQLException e) {
134            throw stmt.handle(e);
135        } finally {
136            stmt.close();
137        }
138    }
139
140    private static String generateColumnCardinalitySql(
141        Dialect dialect,
142        String schema,
143        String table,
144        String column)
145    {
146        final StringBuilder buf = new StringBuilder();
147        String exprString = dialect.quoteIdentifier(column);
148        if (dialect.allowsCountDistinct()) {
149            // e.g. "select count(distinct product_id) from product"
150            buf.append("select count(distinct ")
151                .append(exprString)
152                .append(") from ");
153            dialect.quoteIdentifier(buf, schema, table);
154            return buf.toString();
155        } else if (dialect.allowsFromQuery()) {
156            // Some databases (e.g. Access) don't like 'count(distinct)',
157            // so use, e.g., "select count(*) from (select distinct
158            // product_id from product)"
159            buf.append("select count(*) from (select distinct ")
160                .append(exprString)
161                .append(" from ");
162            dialect.quoteIdentifier(buf, schema, table);
163            buf.append(")");
164            if (dialect.requiresAliasForFromQuery()) {
165                if (dialect.allowsAs()) {
166                    buf.append(" as ");
167                } else {
168                    buf.append(' ');
169                }
170                dialect.quoteIdentifier(buf, "init");
171            }
172            return buf.toString();
173        } else {
174            // Cannot compute cardinality: this database neither supports COUNT
175            // DISTINCT nor SELECT in the FROM clause.
176            return null;
177        }
178    }
179}
180
181// End SqlStatisticsProvider.java