sqlmembersource.java

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

    public void getMemberChildren(RolapMember parentMember, List children) {
        Connection jdbcConnection;
        try {
            jdbcConnection = dataSource.getConnection();
        } catch (SQLException e) {
            throw Util.newInternal(
                e, "Error while creating connection from data source");
        }
        try {
            getMemberChildren(parentMember, children, jdbcConnection);
        } finally {
            try {
                jdbcConnection.close();
            } catch (SQLException e) {
                // ignore
            }
        }
    }

    private void getMemberChildren(RolapMember parentMember,
                                   List children,
                                   Connection jdbcConnection) {
        String sql;
        boolean parentChild;
        final RolapLevel parentLevel = (RolapLevel) parentMember.getLevel();
        RolapLevel childLevel;
        if (parentLevel.getParentExp() != null) {
            sql = makeChildMemberSqlPC(parentMember, jdbcConnection);
            parentChild = true;
            childLevel = parentLevel;
        } else {
            RolapLevel[] levels = (RolapLevel[]) hierarchy.getLevels();
            int childDepth = parentLevel.getDepth() + 1;
            if (childDepth >= levels.length) {
                // member is at last level, so can have no children
                return;
            }
            childLevel = levels[childDepth];
            if (childLevel.getParentExp() != null) {
                sql = makeChildMemberSql_PCRoot(parentMember, jdbcConnection);
                parentChild = true;
            } else {
                sql = makeChildMemberSql(parentMember, jdbcConnection);
                parentChild = false;
            }
        }
        ResultSet resultSet = null;
        try {
            resultSet = RolapUtil.executeQuery(
                jdbcConnection, sql, "SqlMemberSource.getMemberChildren");

            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));
                }

                Object value = resultSet.getObject(1);
                if (value == null) {
                    value = RolapUtil.sqlNullValue;
                }
                Object captionValue;
                if (childLevel.hasCaptionColumn()){
                    captionValue=resultSet.getObject(2);
                } else {
                    captionValue = null;
                }
                Object key = cache.makeKey(parentMember, value);
                RolapMember member = cache.getMember(key);
                if (member == null) {
                    member = makeMember(
                            parentMember, childLevel, value, captionValue,
                            parentChild, resultSet, key, 1);
                }
                if (value == RolapUtil.sqlNullValue) {
                    addAsOldestSibling(children, member);
                } else {
                    children.add(member);
                }
            }
        } catch (SQLException e) {
            throw Util.newInternal(e,
                    "while building member cache; sql=[" + sql + "]");
        } finally {
            try {
                if (resultSet != null) {
                    resultSet.getStatement().close();
                    resultSet.close();
                }
            } catch (SQLException e) {
                // ignore
            }
        }
    }

    private RolapMember makeMember(
            RolapMember parentMember,
            RolapLevel childLevel,
            Object value,
            Object captionValue,
            boolean parentChild,
            ResultSet resultSet,
            Object key,
            int columnOffset)
            throws SQLException {

        RolapMember member = new RolapMember(parentMember, childLevel, value);
        if (childLevel.getOrdinalExp() != childLevel.getKeyExp()) {
            member.setOrdinal(lastOrdinal++);
        }
        if (captionValue != null) {
            member.setCaption(captionValue.toString());
        }
        if (parentChild) {
            // Create a 'public' and a 'data' member. The public member is
            // calculated, and its value is the aggregation of the data member
            // and all of the children. The children and the data member belong
            // to the parent member; the data member does not have any
            // children.
            final RolapParentChildMember parentChildMember =
                childLevel.hasClosedPeer() ?
                    new RolapParentChildMember(
                            parentMember, childLevel, value, member)
                    : new RolapParentChildMemberNoClosure(
                            parentMember, childLevel, value, member);

            member = parentChildMember;
        }
        Property[] properties = childLevel.getProperties();
        for (int j = 0; j < properties.length; j++) {
            Property property = properties[j];
            member.setProperty(
                    property.getName(),
                    resultSet.getObject(columnOffset + j + 1));
        }
        cache.putMember(key, member);
        return member;
    }

    /**
     * Generates the SQL to find all root members of a parent-child hierarchy.
     * For example, <blockquote>
     *
     * <pre>SELECT "employee_id"
     * FROM "employee"
     * WHERE "supervisor_id" IS NULL
     * GROUP BY "employee_id"</pre>
     * </blockquote> retrieves the root members of the <code>[Employee]</code>
     * hierarchy.
     *
     * <p>Currently, parent-child hierarchies may have only one level (plus the
     * 'All' level).
     */
    private String makeChildMemberSql_PCRoot(RolapMember member,
                                             Connection jdbcConnection) {
        SqlQuery sqlQuery = newQuery(jdbcConnection,
            "while generating query to retrieve children of parent/child hierarchy member " + member);
        Util.assertTrue(member.isAll(), "In the current implementation, parent/child hierarchies must have only one level (plus the 'All' level).");

        RolapLevel level = (RolapLevel) member.getLevel().getChildLevel();

        Util.assertTrue(!level.isAll(), "all level cannot be parent-child");
        Util.assertTrue(level.isUnique(), "parent-child level '"
            + level + "' must be unique");

        hierarchy.addToFrom(sqlQuery, level.getParentExp());
        String parentId = level.getParentExp().getExpression(sqlQuery);
        StringBuffer condition = new StringBuffer(64);
        condition.append(parentId);
        if (level.getNullParentValue() == null ||
                level.getNullParentValue().equalsIgnoreCase("NULL")) {
            condition.append(" IS NULL");
        } else {
            // Quote the value if it doesn't seem to be a number.
            try {
                Util.discard(Double.parseDouble(level.getNullParentValue()));
                condition.append(" = ");
                condition.append(level.getNullParentValue());
            } catch (NumberFormatException e) {
                condition.append(" = ");
                Util.singleQuoteString(level.getNullParentValue(), condition);
            }
        }
        sqlQuery.addWhere(condition.toString());
        hierarchy.addToFrom(sqlQuery, level.getKeyExp());
        String childId = level.getKeyExp().getExpression(sqlQuery);
        sqlQuery.addSelect(childId);
        sqlQuery.addGroupBy(childId);
        hierarchy.addToFrom(sqlQuery, level.getOrdinalExp());
        String orderBy = level.getOrdinalExp().getExpression(sqlQuery);
        sqlQuery.addOrderBy(orderBy);
        if (!orderBy.equals(childId)) {
            sqlQuery.addGroupBy(orderBy);
        }

        RolapProperty[] properties = level.getRolapProperties();
        for (int j = 0; j < properties.length; j++) {
            RolapProperty property = properties[j];
            final MondrianDef.Expression exp = property.getExp();
            hierarchy.addToFrom(sqlQuery, exp);
            final String s = exp.getExpression(sqlQuery);
            sqlQuery.addSelect(s);
            sqlQuery.addGroupBy(s);
        }
        return sqlQuery.toString();
    }

    /**
     * Generates the SQL statement to access the children of
     * <code>member</code> in a parent-child hierarchy. For example,
     * <blockquote>
     *
     * <pre>SELECT "employee_id"
     * FROM "employee"
     * WHERE "supervisor_id" = 5</pre>
     * </blockquote> retrieves the children of the member
     * <code>[Employee].[5]</code>. See also {@link #makeLevelSql}.
     **/
    private String makeChildMemberSqlPC(RolapMember member,
                                        Connection jdbcConnection) {
        SqlQuery sqlQuery = newQuery(jdbcConnection,
            "while generating query to retrieve children of parent/child hierarchy member " + member);
        RolapLevel level = (RolapLevel) member.getLevel();

        Util.assertTrue(!level.isAll(), "all level cannot be parent-child");
        Util.assertTrue(level.isUnique(), "parent-child level '"
            + level + "' must be unique");

        hierarchy.addToFrom(sqlQuery, level.getParentExp());
        String parentId = level.getParentExp().getExpression(sqlQuery);
        sqlQuery.addWhere(parentId, " = ", member.quoteKeyForSql());

        hierarchy.addToFrom(sqlQuery, level.getKeyExp());
        String childId = level.getKeyExp().getExpression(sqlQuery);
        sqlQuery.addSelect(childId);
        sqlQuery.addGroupBy(childId);
        hierarchy.addToFrom(sqlQuery, level.getOrdinalExp());
        String orderBy = level.getOrdinalExp().getExpression(sqlQuery);
        sqlQuery.addOrderBy(orderBy);
        if (!orderBy.equals(childId)) {
            sqlQuery.addGroupBy(orderBy);
        }

        RolapProperty[] properties = level.getRolapProperties();
        for (int j = 0; j < properties.length; j++) {
            RolapProperty property = properties[j];
            final MondrianDef.Expression exp = property.getExp();
            hierarchy.addToFrom(sqlQuery, exp);
            final String s = exp.getExpression(sqlQuery);
            sqlQuery.addSelect(s);
            sqlQuery.addGroupBy(s);
        }
        return sqlQuery.toString();
    }

    // implement MemberReader
    public RolapMember getLeadMember(RolapMember member, int n) {
        throw new UnsupportedOperationException();
    }

    public void getMemberRange(RolapLevel level,
                               RolapMember startMember,
                               RolapMember endMember,
                               List memberList) {
        throw new UnsupportedOperationException();
    }

    public int compare(RolapMember m1,
                       RolapMember m2,
                       boolean siblingsAreEqual) {
        throw new UnsupportedOperationException();
    }

    public void getMemberDescendants(RolapMember member,
                                     List result,
                                     RolapLevel level,
                                     boolean before,
                                     boolean self,
                                     boolean after) {
        throw new UnsupportedOperationException();
    }

    /**
     * Member of a parent-child dimension which has a closure table.
     *
     * <p>When looking up cells, this member will automatically be converted
     * to a corresponding member of the auxiliary dimension which maps onto
     * the closure table.
     */
    private static class RolapParentChildMember extends RolapMember {
        private final RolapMember dataMember;
        private int depth = 0;
        public RolapParentChildMember(RolapMember parentMember,
                                      RolapLevel childLevel,
                                      Object value,
                                      RolapMember dataMember) {
            super(parentMember, childLevel, value);
            this.dataMember = dataMember;
            this.depth = (parentMember != null)
                ? parentMember.getDepth() + 1
                : 0;
        }

        public Member getDataMember() {
            return dataMember;
        }

        public Object getPropertyValue(String name) {
            if (name.equals(Property.CONTRIBUTING_CHILDREN.name)) {
                List list = new ArrayList();
                list.add(dataMember);
                RolapHierarchy hierarchy = (RolapHierarchy) getHierarchy();
                hierarchy.memberReader.getMemberChildren(this, list);
                return list;
            } else {
                return super.getPropertyValue(name);
            }
        }

        /**
         * @return the members's depth
         * @see mondrian.olap.Member#getDepth()
         */
        public int getDepth() {
            return depth;
        }
    }

    /**
     * Member of a parent-child dimension which has no closure table.
     *
     * <p>This member is calculcated. When you ask for its value, it returns
     * an expression which aggregates the values of its child members.
     * This calculation is very inefficient, and we can only support
     * aggregatable measures ("count distinct" is non-aggregatable).
     * Unfortunately it's the best we can do without a closure table.
     */
    private static class RolapParentChildMemberNoClosure
        extends RolapParentChildMember {

        public RolapParentChildMemberNoClosure(RolapMember parentMember,
                RolapLevel childLevel, Object value, RolapMember dataMember) {
            super(parentMember, childLevel, value, dataMember);
        }

        public boolean isCalculated() {
            return true;
        }

        public Exp getExpression() {
            final RolapHierarchy hierarchy = (RolapHierarchy) getHierarchy();
            return hierarchy.getAggregateChildrenExpression();
        }
    }
}

// End SqlMemberSource.java