/* // $Id: //guest/julian_hyde/mondrian/src/main/mondrian/rolap/RolapStar.java#2 $ // This software is subject to the terms of the Common Public License // Agreement, available at the following URL: // http://www.opensource.org/licenses/cpl.html. // (C) Copyright 2001-2002 Kana Software, Inc. and others. // All Rights Reserved. // You must accept the terms of that agreement to use this software. // // jhyde, 12 August, 2001 */ package mondrian.rolap; import mondrian.olap.Util; import java.sql.*; import java.util.*; /** * A <code>RolapStar</code> is a star schema. It is the means to read cell * values. * * todo: put this in package which specicializes in relational aggregation, * doesn't know anything about hierarchies etc. * * @author jhyde * @since 12 August, 2001 * @version $Id: //guest/julian_hyde/mondrian/src/main/mondrian/rolap/RolapStar.java#2 $ **/ public class RolapStar { Connection jdbcConnection; Measure[] measures; Table factTable; Table[] tables; /** todo: better, the dimensional model should hold the mapping **/ Hashtable mapLevelToColumn = new Hashtable(); /** * Reads a cell of <code>measure</code>, where <code>columns</code> are * constrained to <code>values</code>. <code>values</code> must be the * same length as <code>columns</code>; null values are left unconstrained. **/ Object getCell(CellRequest request) { Measure measure = request.getMeasure(); Column[] columns = request.getColumns(); Object[] values = request.getSingleValues(); Util.assert(columns.length == values.length); RolapConnection.SqlQuery sqlQuery = new RolapConnection.SqlQuery(); Hashtable tablesAdded = new Hashtable(); // add measure Util.assert(measure.table == factTable); tablesAdded.put(factTable,factTable); sqlQuery.addFromQuery(factTable.sql, factTable.alias); sqlQuery.addSelect( measure.aggregator + "(" + RolapUtil.doubleQuoteForSql(factTable.alias, measure.name) + ")"); // add constraining dimensions for (int i = 0; i < columns.length; i++) { Object value = values[i]; if (value == null) { continue; // not constrained } Column column = columns[i]; Table table = column.table; if (table.sql == null) { // this is a funky dimension -- ignore for now continue; } if (tablesAdded.get(table) == null) { tablesAdded.put(table,table); sqlQuery.addFromQuery(table.sql, table.alias); sqlQuery.addWhere( RolapUtil.doubleQuoteForSql( table.alias, table.primaryKey) + " = " + RolapUtil.doubleQuoteForSql( factTable.alias, table.foreignKey)); sqlQuery.addWhere( RolapUtil.doubleQuoteForSql(table.alias, column.name) + " = " + column.quoteValue(value)); } } String sql = sqlQuery.toString(); Statement statement = null; ResultSet resultSet = null; try { if (RolapUtil.debugOut != null) { RolapUtil.debugOut.println( "RolapStar.getCell: executing sql [" + sql + "]"); } statement = jdbcConnection.createStatement(); resultSet = statement.executeQuery(sql); Object o = null; if (resultSet.next()) { o = resultSet.getObject(1); } if (o == null) { o = Util.nullValue; // convert to placeholder } return o; } catch (SQLException e) { throw Util.getRes().newInternal( e, "while computing cell; sql=[" + sql + "]"); } finally { try { if (resultSet != null) { resultSet.close(); } if (statement != null) { statement.close(); } } catch (SQLException e) { // ignore } } } /** * Reads a segment of <code>measure</code>, where <code>columns</code> are * constrained to <code>values</code>. Each entry in <code>values</code> * can be null, meaning don't constrain, or can have several values. For * example, <code>getSegment({Unit_sales}, {Region, State, Year}, {"West", * {"CA", "OR", "WA"}, null})</code> returns sales in states CA, OR and WA * in the Western region, for all years. **/ SegmentDataset load(Segment segment) { Measure measure = segment.aggregation.measure; Column[] columns = segment.aggregation.columns; int arity = columns.length; Util.assert(arity == segment.axes.length); RolapConnection.SqlQuery sqlQuery = new RolapConnection.SqlQuery(); Hashtable tablesAdded = new Hashtable(); // add constraining dimensions for (int i = 0; i < arity; i++) { Object[] constraints = segment.axes[i].constraints; Column column = columns[i]; Table table = column.table; if (table.sql == null) { // this is a funky dimension -- ignore for now continue; } if (tablesAdded.get(table) == null) { tablesAdded.put(table,table); sqlQuery.addFromQuery(table.sql, table.alias); sqlQuery.addWhere( RolapUtil.doubleQuoteForSql( table.alias, table.primaryKey) + " = " + RolapUtil.doubleQuoteForSql( factTable.alias, table.foreignKey)); } if (constraints != null) { sqlQuery.addWhere( RolapUtil.doubleQuoteForSql(table.alias, column.name) + " in " + column.quoteValues(constraints)); } sqlQuery.addSelect( RolapUtil.doubleQuoteForSql(table.alias, column.name)); sqlQuery.addGroupBy( RolapUtil.doubleQuoteForSql(table.alias, column.name)); } // add measure Util.assert(measure.table == factTable); tablesAdded.put(factTable,factTable); sqlQuery.addFromQuery(factTable.sql, factTable.alias); sqlQuery.addSelect( measure.aggregator + "(" + RolapUtil.doubleQuoteForSql(factTable.alias, measure.name) + ")"); // execute String sql = sqlQuery.toString(); Statement statement = null; ResultSet resultSet = null; try { if (RolapUtil.debugOut != null) { RolapUtil.debugOut.println( "RolapStar.getSegment: executing sql [" + sql + "]"); } statement = jdbcConnection.createStatement(); resultSet = statement.executeQuery(sql); Vector rows = new Vector(); while (resultSet.next()) { Object[] row = new Object[arity + 1]; // get the columns for (int i = 0; i < arity; i++) { Object o = resultSet.getObject(i + 1); Hashtable h = segment.axes[i].mapKeyToOffset; Integer offsetInteger = (Integer) h.get(o); if (offsetInteger == null) { h.put(o, new Integer(h.size())); } row[i] = o; } // get the measure Object o = resultSet.getObject(arity + 1); if (o == null) { o = Util.nullValue; // convert to placeholder } row[arity] = o; rows.addElement(row); } // figure out size of dense array, and allocate it (todo: use // sparse array sometimes) int n = 1; for (int i = 0; i < arity; i++) { RolapAggregation.Axis axis = segment.axes[i]; int size = axis.mapKeyToOffset.size(); axis.keys = new Object[size]; Enumeration keys = axis.mapKeyToOffset.keys(); while (keys.hasMoreElements()) { Object o = keys.nextElement(); Integer offsetInteger = (Integer) axis.mapKeyToOffset.get(o); int offset = offsetInteger.intValue(); Util.assert(axis.keys[offset] == null); axis.keys[offset] = o; } n *= size; } Object[] values = new Object[n]; // now convert the rows into a dense array for (int i = 0, count = rows.size(); i < count; i++) { Object[] row = (Object[]) rows.elementAt(i); int k = 0; for (int j = 0; j < arity; j++) { k *= segment.axes[j].keys.length; Object o = row[j]; RolapAggregation.Axis axis = segment.axes[j]; Integer offsetInteger = (Integer) axis.mapKeyToOffset.get(o); int offset = offsetInteger.intValue(); k += offset; } values[k] = row[arity]; } DenseSegmentDataset data = new DenseSegmentDataset(); data.segment = segment; data.values = values; return data; } catch (SQLException e) { throw Util.getRes().newInternal( e, "while computing cell; sql=[" + sql + "]"); } finally { try { if (resultSet != null) { resultSet.close(); } if (statement != null) { statement.close(); } } catch (SQLException e) { // ignore } } } // static class Segment // { // Object[][] keys; // Object[] values; // }; // private static class Axis // { // Column column; // Vector values = new Vector(); // }; static class CellRequest { private Measure measure; private HashableVector columnsVector = new HashableVector(); private Vector valuesVector = new Vector(); CellRequest(Measure measure) { this.measure = measure; this.columnsVector.addElement(measure); } void addConstrainedColumn(Column column, Object[] values) { columnsVector.addElement(column); valuesVector.addElement(values); } void addConstrainedColumn(Column column, Object value) { columnsVector.addElement(column); valuesVector.addElement(value); } void addColumn(Column column) { addConstrainedColumn(column, null); } Measure getMeasure() { return measure; } Column[] getColumns() { // ignore the measure, the 0th element of columnsVector Column[] a = new Column[columnsVector.size() - 1]; for (int i = 0; i < a.length; i++) { a[i] = (Column) columnsVector.elementAt(i + 1); } return a; } /** Returns a vector which identifies which batch this request will * belong to. The vector contains the measure as well as the * columns. **/ HashableVector getBatchKey() { return columnsVector; } Vector getValuesVector() { return valuesVector; } /* Object[][] getMultiValues() { Object[][] a = new Object[valuesVector.size()][]; valuesVector.copyInto(a); return a; } */ Object[] getSingleValues() { Object[] a = new Object[valuesVector.size()]; for (int i = 0, n = valuesVector.size(); i < n; i++) { Object value = valuesVector.elementAt(i); if (value instanceof Object[]) { throw Util.newInternal("multi value in cell request"); } a[i] = value; } return a; } }; static class Column { Table table; String name; boolean isNumeric; int cardinality = -1; String quoteValue(Object value) { String s = value.toString(); if (isNumeric) { return s; } else { return RolapUtil.singleQuoteForSql(s); } } String quoteValues(Object[] values) { StringBuffer sb = new StringBuffer("("); for (int i = 0; i < values.length; i++) { if (i > 0) { sb.append(", "); } sb.append(quoteValue(values[i])); } sb.append(")"); return sb.toString(); } int getCardinality() { if (cardinality == -1) { Statement statement = null; ResultSet resultSet = null; String sql = "select count(*) from (select distinct " + RolapUtil.doubleQuoteForSql(name) + " from (" + table.sql + "))"; if (RolapUtil.debugOut != null) { RolapUtil.debugOut.println( "RolapStar.Column.getCardinality: executing sql [" + sql + "]"); } try { statement = table.star.jdbcConnection.createStatement(); resultSet = statement.executeQuery(sql); Util.assert(resultSet.next()); cardinality = resultSet.getInt(1); } catch (SQLException e) { throw Util.getRes().newInternal( e, "while counting distinct values of column '" + name + "'; sql=[" + sql + "]"); } finally { try { if (resultSet != null) { resultSet.close(); } if (statement != null) { statement.close(); } } catch (SQLException e) { // ignore } } } return cardinality; } }; static class Measure extends Column { String aggregator; }; static class Table { RolapStar star; String alias; String sql; String primaryKey; String foreignKey; Column[] columns; }; } // End RolapStar.java
# | Change | User | Description | Committed | |
---|---|---|---|---|---|
#4 | 1603 | Julian Hyde |
mondrian: Add Andreas' taglib; Rename 'mondrian.rolap.Util.assert' to 'assertTrue', because 'assert' is a keyword in jdk 1.4; Fix 'NON EMPTY'; JUnit framework for tests; Add Oracle dataset. |
||
#3 | 1576 | Julian Hyde |
mondrian: fix dataset (add column customer.ordinal); create dataset for oracle; get queries working on oracle; get format strings working; refactor out new packages mondrian.rolap.agg and mondrian.rolap.sql. |
||
#2 | 1496 | Julian Hyde |
Mondrian: Qualify table names with schemas. Add 'Hierarchy.schema', 'Cube.factSchema','Hierarchy.table' attributes. MondrianDef is now generated (so we need boot.jar). |
||
#1 | 1453 | Julian Hyde | mondrian: first source check-in |