dbforum.java

一套会员管理系统组件

        for (int i=0; i<propertyCount; i++) {
            query.append(" AND "+Globals.getConfig(Globals._TABLE_THREAD)+".threadID=p").append(i).append(".threadID");
            query.append(" AND p").append(i).append(".name='");
            query.append(resultFilter.getPropertyName(i));
            query.append("' AND p").append(i).append(".propValue='");
            query.append(resultFilter.getPropertyValue(i)).append("'");
        }
        // Sort on thread name
        if (!countQuery && sortField == Globals.THREAD_NAME) {
            query.append(" AND "+Globals.getConfig(Globals._TABLE_THREAD)+".threadID="+Globals.getConfig(Globals._TABLE_MESSAGE)+".threadID");
        }
        // Sort on properties
        if (!countQuery && sortField == Globals.EXTENDED_PROPERTY) {
            query.append(" AND "+Globals.getConfig(Globals._TABLE_THREAD)+".threadID=propTable.threadID");
        }

        // Creation date range
        if (filterCreationDate) {
            if (resultFilter.getCreationDateRangeMin() != null) {
                query.append(" AND "+Globals.getConfig(Globals._TABLE_THREAD)+".creationDate >= '");
                query.append(StringUtils.dateToMillis(
                        resultFilter.getCreationDateRangeMin()));
                query.append("'");
            }
            if (resultFilter.getCreationDateRangeMax() != null) {
                query.append(" AND "+Globals.getConfig(Globals._TABLE_THREAD)+".creationDate <= '");
                query.append(StringUtils.dateToMillis(
                        resultFilter.getCreationDateRangeMax()));
                query.append("'");
            }
        }

        // Modified date range
        if (filterModifiedDate) {
            if (resultFilter.getModifiedDateRangeMin() != null) {
                query.append(" AND "+Globals.getConfig(Globals._TABLE_THREAD)+".modifiedDate >= '");
                query.append(StringUtils.dateToMillis(
                        resultFilter.getModifiedDateRangeMin()));
                query.append("'");
            }
            if (resultFilter.getModifiedDateRangeMax() != null) {
                query.append(" AND "+Globals.getConfig(Globals._TABLE_THREAD)+".modifiedDate <= '");
                query.append(StringUtils.dateToMillis(
                        resultFilter.getModifiedDateRangeMax()));
                query.append("'");
            }
        }

        // Moderation range
        int moderationRangeMin = resultFilter.getModerationRangeMin();
        // If the minimum moderation value was not set, it should default
        // to the minimum threshold for the forum.
        if (moderationRangeMin == ResultFilter.NULL_INT) {
            moderationRangeMin = getModerationMinThreadValue();
        }
        int moderationRangeMax = resultFilter.getModerationRangeMax();
        if (moderationRangeMin == moderationRangeMax) {
            query.append(" AND "+Globals.getConfig(Globals._TABLE_THREAD)+".modValue = ");
            query.append(moderationRangeMin);
        }
        else {
            // Ignore any min less than negative 1 million.
            if (moderationRangeMin > -1000000) {
                query.append(" AND "+Globals.getConfig(Globals._TABLE_THREAD)+".modValue >= ");
                query.append(moderationRangeMin);
            }
            // Use a max value if it's defined.
            if (moderationRangeMax != ResultFilter.NULL_INT) {
                query.append(" AND "+Globals.getConfig(Globals._TABLE_THREAD)+".modValue <= ");
                query.append(moderationRangeMax);
            }
        }

        // ORDER BY
        if (!countQuery) {
            query.append(" ORDER BY ");
            switch(sortField) {
                case Globals.THREAD_NAME:
                    query.append(Globals.getConfig(Globals._TABLE_MESSAGE)+".subject");
                    break;
                case Globals.MODIFIED_DATE:
                    query.append(Globals.getConfig(Globals._TABLE_THREAD)+".modifiedDate");
                    break;
                case Globals.CREATION_DATE:
                    query.append(Globals.getConfig(Globals._TABLE_THREAD)+".creationDate");
                    break;
                case Globals.EXTENDED_PROPERTY:
                    query.append("propTable.propValue");
                    break;
            }
            if (resultFilter.getSortOrder() == ResultFilter.DESCENDING) {
                query.append(" DESC");
            }
            else {
                query.append(" ASC");
            }
        }
        return query.toString();
    }

    /**
     * Returns the SQL statement corresponding to a ResultFilter for messages.
     */
    protected String getMessageListSQL(ResultFilter resultFilter, boolean countQuery) {
        int sortField = resultFilter.getSortField();
        // Make sure the sort field is valid.
        if (!countQuery && !( sortField == Globals.MODIFIED_DATE ||
                sortField == Globals.CREATION_DATE ||
                sortField == Globals.MESSAGE_SUBJECT ||
                sortField == Globals.MESSAGE_BODY ||
                ( sortField == Globals.EXTENDED_PROPERTY &&
                  resultFilter.getSortPropertyName() != null
                )
              )
            )
        {
            throw new IllegalArgumentException("The specified sort field is not valid.");
        }

        // We'll accumlate the query in a StringBuffer.
        StringBuffer query = new StringBuffer(80);
        if (!countQuery) {
            query.append("SELECT "+Globals.getConfig(Globals._TABLE_MESSAGE)+".messageID");
        }
        else {
            query.append("SELECT count(1)");
        }

        boolean filterUser = resultFilter.getUserID() != ResultFilter.NULL_INT;
        boolean filterCreationDate = resultFilter.getCreationDateRangeMin() != null ||
                                     resultFilter.getCreationDateRangeMax() != null;
        boolean filterModifiedDate = resultFilter.getModifiedDateRangeMin() != null ||
                                     resultFilter.getModifiedDateRangeMax() != null;
        int propertyCount = resultFilter.getPropertyCount();

        // SELECT -- need to add value that we sort on
        if (!countQuery) {
            switch(sortField) {
                case Globals.MESSAGE_SUBJECT:
                    query.append(", subject");
                    break;
                case Globals.MODIFIED_DATE:
                    query.append(", modifiedDate");
                    break;
                case Globals.CREATION_DATE:
                    query.append(", creationDate");
                    break;
                case Globals.EXTENDED_PROPERTY:
                    query.append(", propTable.propValue");
                    break;
            }
        }

        // FROM -- values (in addition to memberThread)
        query.append(" FROM "+Globals.getConfig(Globals._TABLE_MESSAGE));
        for (int i=0; i<propertyCount; i++) {
            query.append(", "+Globals.getConfig(Globals._TABLE_MESSAGE_PROP)+" p").append(i);
        }
        if (!countQuery &&
                resultFilter.getSortField() == Globals.EXTENDED_PROPERTY)
        {
            query.append(", "+Globals.getConfig(Globals._TABLE_MESSAGE_PROP)+" propTable");
        }

        // WHERE BLOCK
        query.append(" WHERE forumID=").append(this.id);
        // User
        if (filterUser) {
            query.append(" AND userID=").append(resultFilter.getUserID());
        }
        // Properties
        for (int i=0; i<propertyCount; i++) {
            query.append(" AND "+Globals.getConfig(Globals._TABLE_MESSAGE)+".messageID=p").append(i).append(".messageID");
            query.append(" AND p").append(i).append(".name='");
            query.append(resultFilter.getPropertyName(i));
            query.append("' AND p").append(i).append(".propValue='");
            query.append(resultFilter.getPropertyValue(i)).append("'");
        }
        // Sort on properties
        if (!countQuery && sortField == Globals.EXTENDED_PROPERTY) {
            query.append(" AND "+Globals.getConfig(Globals._TABLE_MESSAGE)+".messageID=propTable.messageID");
        }

        // Creation date range
        if (filterCreationDate) {
            if (resultFilter.getCreationDateRangeMin() != null) {
                query.append(" AND creationDate >= '");
                query.append(StringUtils.dateToMillis(
                        resultFilter.getCreationDateRangeMin()));
                query.append("'");
            }
            if (resultFilter.getCreationDateRangeMax() != null) {
                query.append(" AND creationDate <= '");
                query.append(StringUtils.dateToMillis(
                        resultFilter.getCreationDateRangeMax()));
                query.append("'");
            }
        }

        // Modified date range
        if (filterModifiedDate) {
            if (resultFilter.getModifiedDateRangeMin() != null) {
                query.append(" AND modifiedDate >= '");
                query.append(StringUtils.dateToMillis(
                        resultFilter.getModifiedDateRangeMin()));
                query.append("'");
            }
            if (resultFilter.getModifiedDateRangeMax() != null) {
                query.append(" AND modifiedDate <= '");
                query.append(StringUtils.dateToMillis(
                        resultFilter.getModifiedDateRangeMax()));
                query.append("'");
            }
        }

        // Moderation range
        int moderationRangeMin = resultFilter.getModerationRangeMin();
        // If the minimum moderation value was not set, it should default
        // to the minimum threshold for the forum.
        if (moderationRangeMin == ResultFilter.NULL_INT) {
            moderationRangeMin = getModerationMinMessageValue();
        }
        int moderationRangeMax = resultFilter.getModerationRangeMax();
        if (moderationRangeMin == moderationRangeMax) {
            query.append(" AND modValue = ");
            query.append(moderationRangeMin);
        }
        else {
            // Ignore any min less than negative 1 million.
            if (moderationRangeMin > -1000000) {
                query.append(" AND modValue >= ");
                query.append(moderationRangeMin);
            }
            // Use a max value if it's defined.
            if (moderationRangeMax != ResultFilter.NULL_INT) {
                query.append(" AND modValue <= ");
                query.append(moderationRangeMax);
            }
        }

        // ORDER BY
        if (!countQuery) {
            query.append(" ORDER BY ");
            switch(sortField) {
                case Globals.MESSAGE_SUBJECT:
                    query.append("subject");
                    break;
                case Globals.MODIFIED_DATE:
                    query.append("modifiedDate");
                    break;
                case Globals.CREATION_DATE:
                    query.append("creationDate");
                    break;
                case Globals.EXTENDED_PROPERTY:
                    query.append("propTable.propValue");
                    break;
            }
            if (resultFilter.getSortOrder() == ResultFilter.DESCENDING) {
                query.append(" DESC");
            }
            else {
                query.append(" ASC");
            }
        }
        return query.toString();
    }

    /**
     * Returns a block of threadID's from a query and performs transparent
     * caching of those blocks. The two parameters specify a database query
     * and a startIndex for the results in that query.
     *
     * @param query the SQL thread list query to cache blocks from.
     * @param startIndex the startIndex in the list to get a block for.
     */
    protected long[] getThreadBlock(String query, int startIndex) {
        // First, discover what block number the results will be in.
        int blockID = startIndex / THREAD_BLOCK_SIZE;
        int blockStart = blockID * THREAD_BLOCK_SIZE;
        // Now, check cache to see if the block is already cached. The key is
        // simply the query plus the blockID.
        String key = query + blockID;
        CacheableLongArray longArray = (CacheableLongArray)threadListCache.get(key);
        //If already in cache, return the block.
        if (longArray != null) {
            /**
             * The actual block may be smaller than THREAD_BLOCK_SIZE. If that's
             * the case, it means two things:
             *  1) We're at the end boundary of all the results.
             *  2) If the start index is greater than the length of the current
             *     block, than there aren't really any results to return.
             */
            long [] threads = longArray.getLongArray();
            if (startIndex >= blockStart + threads.length) {
                // Return an empty array
                return EMPTY_BLOCK;
            }
            else {
                return threads;
            }
        }
        // Otherwise, we have to load up the block from the database.
        else {
            LongList threadsList = new LongList(THREAD_BLOCK_SIZE);
            Connection con = null;
            Statement stmt = null;
            try {
                con = ConnectionManager.getConnection();
                stmt = con.createStatement();
                // Set the maxium number of rows to end at the end of this block.
                ConnectionManager.setMaxRows(stmt, THREAD_BLOCK_SIZE * (blockID+1));
				System.err.println("[thread_block] [query: "+query+"] [startIndex:"+startIndex+"]");
                ResultSet rs = stmt.executeQuery(query);
                // Grab THREAD_BLOCK_ROWS rows at a time.
                ConnectionManager.setFetchSize(rs, THREAD_BLOCK_SIZE);
                // Many JDBC drivers don't implement scrollable cursors the real
                // way, but instead load all results into memory. Looping through
                // the results ourselves is more efficient.
                for (int i=0; i<blockStart; i++) {
                    rs.next();
                }
                // Keep reading results until the result set is exaughsted or
                // we come to the end of the block.
                int count = 0;