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