sqlmembersource.java

来自「数据仓库展示程序」· Java 代码 · 共 1,093 行 · 第 1/3 页

/*
// $Id: //open/mondrian/src/main/mondrian/rolap/SqlMemberSource.java#52 $
// 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.
// Copyright (C) 2001-2005 Kana Software, Inc. and others.
// All Rights Reserved.
// You must accept the terms of that agreement to use this software.
//
// jhyde, 21 December, 2001
*/

package mondrian.rolap;
import mondrian.olap.*;
import mondrian.rolap.sql.SqlQuery;
import mondrian.resource.MondrianResource;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Map;
import java.util.HashMap;
import java.util.List;

/**
 * A <code>SqlMemberSource</code> reads members from a SQL database.
 *
 * <p>It's a good idea to put a {@link CacheMemberReader} on top of this.
 *
 * @author jhyde
 * @since 21 December, 2001
 * @version $Id: //open/mondrian/src/main/mondrian/rolap/SqlMemberSource.java#52 $
 */
class SqlMemberSource implements MemberReader {
    private final RolapHierarchy hierarchy;
    private final DataSource dataSource;
    private MemberCache cache;
    private int lastOrdinal = 0;

    SqlMemberSource(RolapHierarchy hierarchy) {
        this.hierarchy = hierarchy;
        this.dataSource =
            hierarchy.getRolapSchema().getInternalConnection().getDataSource();
    }

    // implement MemberSource
    public RolapHierarchy getHierarchy() {
        return hierarchy;
    }

    // implement MemberSource
    public boolean setCache(MemberCache cache) {
        this.cache = cache;
        return true; // yes, we support cache writeback
    }

    // implement MemberSource
    public int getMemberCount() {
        RolapLevel[] levels = (RolapLevel[]) hierarchy.getLevels();
        int count = 0;
        for (int i = 0; i < levels.length; i++) {
            count += getLevelMemberCount(levels[i]);
        }
        return count;
    }

    public RolapMember lookupMember(String[] uniqueNameParts,
                                    boolean failIfNotFound) {
        throw new UnsupportedOperationException();
    }

    private int getLevelMemberCount(RolapLevel level) {
        if (level.isAll()) {
            return 1;
        }
        Connection jdbcConnection;
        try {
            jdbcConnection = dataSource.getConnection();
        } catch (SQLException e) {
            throw Util.newInternal(
                e, "Error while creating connection from data source");
        }
        try {
            return getMemberCount(level, jdbcConnection);
        } finally {
            try {
                jdbcConnection.close();
            } catch (SQLException e) {
                // ignore
            }
        }
    }

    private int getMemberCount(RolapLevel level, Connection jdbcConnection) {
        boolean[] mustCount = new boolean[1];
        String sql = makeLevelMemberCountSql(level, jdbcConnection, mustCount);
        ResultSet resultSet = null;
        try {
            resultSet = RolapUtil.executeQuery(
                    jdbcConnection, sql, "SqlMemberSource.getLevelMemberCount");
            if (! mustCount[0] ) {
                Util.assertTrue(resultSet.next());
                return resultSet.getInt(1);
            } else {
                // count distinct "manually"
                ResultSetMetaData rmd = resultSet.getMetaData();
                int nColumns = rmd.getColumnCount();
                int count = 0;
                String[] colStrings = new String[nColumns];
                while (resultSet.next()) {
                    boolean isEqual = true;
                    for (int i = 0; i < nColumns; i++ ) {
                        String colStr = resultSet.getString(i+1);
                        if (!colStr.equals(colStrings[i])) {
                            isEqual = false;
                        }
                        colStrings[i] = colStr;
                    }
                    if (!isEqual) {
                        count++;
                    }
                }
                return count;
            }
        } catch (SQLException e) {
            throw Util.newInternal(e,
                    "while counting members of level '" + level +
                    "'; sql=[" + sql + "]");
        } finally {
            try {
                if (resultSet != null) {
                    resultSet.getStatement().close();
                    resultSet.close();
                }
            } catch (SQLException e) {
                // ignore
            }
        }
    }


    private SqlQuery newQuery(Connection jdbcConnection, String err) {
        try {
            return new SqlQuery(jdbcConnection.getMetaData());
        } catch (SQLException e) {
            throw Util.newInternal(e, err);
        }
    }

    /**
     * Generates the SQL statement to count the members in
     * <code>level</code>. For example, <blockquote>
     *
     * <pre>SELECT count(*) FROM (
     *   SELECT DISTINCT "country", "state_province"
     *   FROM "customer") AS "init"</pre>
     *
     * </blockquote> counts the non-leaf "state_province" level. MySQL
     * doesn't allow SELECT-in-FROM, so we use the syntax<blockquote>
     *
     * <pre>SELECT count(DISTINCT "country", "state_province")
     * FROM "customer"</pre>
     *
     * </blockquote>. The leaf level requires a different query:<blockquote>
     *
     * <pre>SELECT count(*) FROM "customer"</pre>
     *
     * </blockquote> counts the leaf "name" level of the "customer" hierarchy.
     **/
    private String makeLevelMemberCountSql(RolapLevel level,
                                           Connection jdbcConnection,
                                           boolean[] mustCount) {
        mustCount[0] = false;
        SqlQuery sqlQuery = newQuery(jdbcConnection,
                "while generating query to count members in level " + level);
        int levelDepth = level.getDepth();
        RolapLevel[] levels = (RolapLevel[]) hierarchy.getLevels();
        if (levelDepth == levels.length) {
            // "select count(*) from schema.customer"
            sqlQuery.addSelect("count(*)");
            hierarchy.addToFrom(sqlQuery, level.getKeyExp());
            return sqlQuery.toString();
        }
        if (!sqlQuery.getDialect().allowsFromQuery()) {
            String columnList = "";
            int columnCount = 0;
            for (int i = levelDepth; i >= 0; i--) {
                RolapLevel level2 = levels[i];
                if (level2.isAll()) {
                     continue;
                }
                if (columnCount > 0) {
                    if (sqlQuery.getDialect().allowsCompoundCountDistinct()) {
                        columnList += ", ";
                    } else if (true) {
                        // for databases where both SELECT-in-FROM and
                        // COUNT DISTINCT do not work, we do not
                        // generate any count and do the count
                        // distinct "manually".
                        mustCount[0] = true;
                    } else if (sqlQuery.getDialect().isSybase()) {
                        // "select count(distinct convert(varchar, c1) +
                        // convert(varchar, c2)) from table"
                        if (columnCount == 1) {
                            // Conversion to varchar is expensive, so we only
                            // do it when we know we are dealing with a
                            // compound key.
                            columnList = "convert(varchar, " + columnList + ")";
                        }
                        columnList += " + ";
                    } else {
                        // Apparently this database allows neither
                        // SELECT-in-FROM nor compound COUNT DISTINCT. I don't
                        // know any database where this happens. If you receive
                        // this error, try a workaround similar to the Sybase
                        // workaround above.
                        throw Util.newInternal(
                            "Cannot generate query to count members of level '" +
                            level.getUniqueName() +
                            "': database supports neither SELECT-in-FROM nor compound COUNT DISTINCT");
                    }
                }
                hierarchy.addToFrom(sqlQuery, level2.getKeyExp());

                String keyExp = level2.getKeyExp().getExpression(sqlQuery);
                if (columnCount > 0 &&
                    !sqlQuery.getDialect().allowsCompoundCountDistinct() &&
                    sqlQuery.getDialect().isSybase()) {

                    keyExp = "convert(varchar, " + columnList + ")";
                }
                columnList += keyExp;

                if (level2.isUnique()) {
                    break; // no further qualification needed
                }
                ++columnCount;
            }
            if (mustCount[0]) {
                sqlQuery.addSelect(columnList);
                sqlQuery.addOrderBy(columnList);
            } else {
                sqlQuery.addSelect("count(DISTINCT " + columnList + ")");
            }
            return sqlQuery.toString();

        } else {
            sqlQuery.setDistinct(true);
            for (int i = levelDepth; i >= 0; i--) {
                RolapLevel level2 = levels[i];
                if (level2.isAll()) {
                    continue;
                }
                hierarchy.addToFrom(sqlQuery, level2.getKeyExp());
                sqlQuery.addSelect(level2.getKeyExp().getExpression(sqlQuery));
                if (level2.isUnique()) {
                    break; // no further qualification needed
                }
            }
            SqlQuery outerQuery = newQuery(jdbcConnection,
                "while generating query to count members in level " + level);
            outerQuery.addSelect("count(*)");
            // Note: the "init" is for Postgres, which requires
            // FROM-queries to have an alias
            boolean failIfExists = true;
            outerQuery.addFrom(sqlQuery, "init", failIfExists);
            return outerQuery.toString();
        }
    }


    public RolapMember[] getMembers() {
        Connection jdbcConnection;
        try {
            jdbcConnection = dataSource.getConnection();
        } catch (SQLException e) {
            throw Util.newInternal(
                e, "Error while creating connection from data source");
        }
        try {
            return getMembers(jdbcConnection);
        } finally {
            try {
                jdbcConnection.close();
            } catch (SQLException e) {
                // ignore
            }
        }
    }

    private RolapMember[] getMembers(Connection jdbcConnection) {
        String sql = makeKeysSql(jdbcConnection);
        RolapLevel[] levels = (RolapLevel[]) hierarchy.getLevels();
        ResultSet resultSet = null;
        try {
            resultSet = RolapUtil.executeQuery(
                jdbcConnection, sql, "SqlMemberSource.getMembers");
            List list = new ArrayList();
            Map map = new HashMap();
            RolapMember root = null;
            if (hierarchy.hasAll()) {
                root = new RolapMember(null,
                                       (RolapLevel) hierarchy.getLevels()[0],
                                       null,
                                       hierarchy.getAllMemberName(),
                                       Member.ALL_MEMBER_TYPE);
                // assign "all member" caption
                if (hierarchy.xmlHierarchy != null &&
                    hierarchy.xmlHierarchy.allMemberCaption != null &&
                        hierarchy.xmlHierarchy.allMemberCaption.length() > 0)
                    root.setCaption(hierarchy.xmlHierarchy.allMemberCaption );

                root.setOrdinal(lastOrdinal++);
                list.add(root);
            }

            int limit = MondrianProperties.instance().ResultLimit.get();
            int nFetch = 0;

            while (resultSet.next()) {

                if (limit > 0 && limit < ++nFetch) {
                    // result limit exceeded, throw an exception
                    throw MondrianResource.instance().MemberFetchLimitExceeded.ex(
                            new Long(limit));
                }

                int column = 0;
                RolapMember member = root;
                for (int i = 0; i < levels.length; i++) {
                    RolapLevel level = levels[i];
                    if (level.isAll()) {
                        continue;
                    }
                    Object value = resultSet.getObject(column + 1);
                    if (value == null) {
                        value = RolapUtil.sqlNullValue;
                    }
                    RolapMember parent = member;
                    MemberKey key = new MemberKey(parent, value);
                    member = (RolapMember) map.get(key);
                    if (member == null) {
                        member = new RolapMember(parent, level, value);
                        member.setOrdinal(lastOrdinal++);
/*
RME is this right
                        if (level.getOrdinalExp() != level.getKeyExp()) {
                            member.setOrdinal(lastOrdinal++);
                        }
*/
                        if (value == RolapUtil.sqlNullValue) {
                            addAsOldestSibling(list, member);
                        } else {
                            list.add(member);
                        }
                        map.put(key, member);
                    }
                    column++;

                    Property[] properties = level.getProperties();
                    for (int j = 0; j < properties.length; j++) {
                        Property property = properties[j];