BlogDAO.java

/*
 * JBoss, Home of Professional Open Source
 *
 * Distributable under LGPL license.
 * See terms of license at gnu.org.
 */
package org.jboss.seam.wiki.plugin.blog;

import org.hibernate.Hibernate;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.Query;
import org.hibernate.transform.ResultTransformer;
import org.jboss.seam.annotations.In;
import org.jboss.seam.annotations.Name;
import org.jboss.seam.annotations.Scope;
import org.jboss.seam.annotations.AutoCreate;
import org.jboss.seam.wiki.core.model.WikiDirectory;
import org.jboss.seam.wiki.core.model.WikiDocument;
import org.jboss.seam.wiki.core.model.WikiNode;
import org.jboss.seam.wiki.core.action.Pager;
import org.jboss.seam.wiki.core.dao.WikiNodeDAO;
import org.jboss.seam.ScopeType;

import javax.persistence.EntityManager;
import java.util.List;
import java.util.Map;
import java.util.HashMap;
import java.io.Serializable;
import java.math.BigInteger;

/**
 * Queries and database operations related to blog entries (mostly aggregation queries).
 * <p/>
 * TODO: The queries (especially since they are all SQL) should be externalized to a hbm.xml file. However,
 * building them dynamically here is less duplication for now.
 *
 * @author Christian Bauer
 */
@Name("blogDAO")
@Scope(ScopeType.CONVERSATION)
@AutoCreate
public class BlogDAO implements Serializable {

    @In
    EntityManager restrictedEntityManager;

    @In
    WikiNodeDAO wikiNodeDAO;

    @In
    Integer currentAccessLevel;

    // Too bad, but we really need a SQL query here... better use SQL queries for ALL queries in this DAO and generalize things
    private String[] getWikiDocumentSQLColumnNames() {
        return new String[]{
            "doc2.NODE_ID",
            "doc2.OBJ_VERSION", "doc2.PARENT_NODE_ID", "doc2.RATING", "doc2.MESSAGE_ID",
            "doc2.AREA_NR", "doc2.NAME", "doc2.WIKINAME", "doc2.CREATED_BY_USER_ID", "doc2.CREATED_ON", "doc2.WRITE_PROTECTED",
            "doc2.LAST_MODIFIED_BY_USER_ID", "doc2.LAST_MODIFIED_ON", "doc2.READ_ACCESS_LEVEL", "doc2.WRITE_ACCESS_LEVEL",
            "doc1.FILE_REVISION",
            "doc.NAME_AS_TITLE", "doc.ENABLE_COMMENTS", "doc.ENABLE_COMMENT_FORM", "doc.ENABLE_COMMENTS_ON_FEEDS",
            "doc.HEADER", "doc.HEADER_MACROS", "doc.CONTENT", "doc.CONTENT_MACROS", "doc.FOOTER", "doc.FOOTER_MACROS"
        };
    }

    private String getblogEntryFromClause(String tag) {
        StringBuilder clause = new StringBuilder();
        clause.append("from WIKI_DOCUMENT doc").append(" ");
        clause.append("inner join WIKI_FILE doc1 on doc.NODE_ID=doc1.NODE_ID").append(" ");
        clause.append("inner join WIKI_NODE doc2 on doc.NODE_ID=doc2.NODE_ID").append(" ");
        if (tag != null && tag.length() > 0) clause.append("inner join WIKI_TAG t on t.FILE_ID = doc1.NODE_ID").append(" ");
        return clause.toString();
    }

    private String getBlogEntryWhereClause(WikiDocument ignoreDoc, Integer year, Integer month, Integer day, String tag) {
        StringBuilder clause = new StringBuilder();
        clause.append("where doc2.PARENT_NODE_ID in (:directoryIDs)").append(" ");
        clause.append("and doc.HEADER_MACROS like '%blogEntry%'").append(" ");
        clause.append("and doc2.READ_ACCESS_LEVEL <= :currentAccessLevel").append(" ");
        if (ignoreDoc != null && ignoreDoc.getId() != null) clause.append("and doc.NODE_ID<>:ignoreDoc").append(" ");
        if (tag != null && tag.length()>0)                  clause.append("and t.TAG = :tag").append(" ");
        if (year != null)   clause.append("and year(doc2.CREATED_ON) = :limitYear").append(" ");
        if (month != null)  clause.append("and month(doc2.CREATED_ON) = :limitMonth").append(" ");
        if (day != null)    clause.append("and day(doc2.CREATED_ON) = :limitDay").append(" ");
        return clause.toString();
    }

    private void bindBlogEntryWhereClause(Query query, WikiDirectory startDir, WikiDocument ignoreDoc,
                                          Integer year, Integer month, Integer day, String tag) {
        query.setParameterList("directoryIDs", wikiNodeDAO.findWikiDirectoryTreeIDs(startDir));
        query.setParameter("currentAccessLevel", currentAccessLevel);

        if (ignoreDoc != null && ignoreDoc.getId() != null) query.setParameter("ignoreDoc", ignoreDoc);
        if (tag != null && tag.length()>0)                  query.setParameter("tag", tag);
        if (year != null)   query.setParameter("limitYear", year);
        if (month != null)  query.setParameter("limitMonth", month);
        if (day != null)    query.setParameter("limitDay", day);
    }

    public List<BlogEntry> findBlogEntriesInDirectory(WikiDirectory startDir,
                                                      WikiDocument ignoreDoc,
                                                      Pager pager,
                                                      Integer year,
                                                      Integer month,
                                                      Integer day,
                                                      String tag,
                                                      boolean countComments) {

        final Map<Long, BlogEntry> blogEntryMap = new HashMap<Long, BlogEntry>();

        StringBuilder queryString = new StringBuilder();
        queryString.append("select").append(" ");
        for (int i = 0; i < getWikiDocumentSQLColumnNames().length; i++) {
            queryString.append(getWikiDocumentSQLColumnNames()[i]);
            if (i != getWikiDocumentSQLColumnNames().length-1) queryString.append(", ");
        }
        queryString.append(", '0' as COMMENT_COUNT").append(" ");
        queryString.append(getblogEntryFromClause(tag));
        queryString.append(getBlogEntryWhereClause(ignoreDoc, year, month, day, tag));

        queryString.append(" ");
        queryString.append("order by doc2.CREATED_ON desc");

        SQLQuery query = getSession().createSQLQuery(queryString.toString());

        bindBlogEntryWhereClause(query, startDir, ignoreDoc, year, month, day, tag);

        query.setComment("Finding all blogEntry documents recursively in dir: " + startDir.getName());
        query.addEntity(WikiDocument.class);
        query.addScalar("COMMENT_COUNT", Hibernate.LONG);
        query.setFirstResult( pager.getQueryFirstResult() );
        query.setMaxResults( pager.getQueryMaxResults() );

        query.setResultTransformer(
            new ResultTransformer() {
                public Object transformTuple(Object[] result, String[] aliases) {
                    BlogEntry be = new BlogEntry();
                    be.setEntryDocument( (WikiDocument)result[0]);
                    blogEntryMap.put(be.getEntryDocument().getId(), be); // Put in map so we can attach comment count later
                    return be;
                }
                public List transformList(List list) { return list; }
            }
        );

        List<BlogEntry> result = (List<BlogEntry>)query.list();

        if (countComments && result.size() > 0) {
            // The risk here is that pager.getQueryMaxResults() is too large for the IN() operator of some DBs...
            StringBuilder commentQueryString = new StringBuilder();
            commentQueryString.append("select doc.NODE_ID as DOC_ID, count(c1.NODE_ID) as COMMENT_COUNT").append(" ");
            commentQueryString.append("from WIKI_DOCUMENT doc").append(" ");
            commentQueryString.append("left outer join WIKI_NODE c1 on doc.NODE_ID = c1.PARENT_NODE_ID").append(" ");
            commentQueryString.append("where doc.NODE_ID in (:blogEntriesIds)").append(" ");
            commentQueryString.append("group by doc.NODE_ID");

            SQLQuery commentQuery = getSession().createSQLQuery(commentQueryString.toString());
            commentQuery.setComment("Finding comment count for blog entries");
            commentQuery.addScalar("DOC_ID");
            commentQuery.addScalar("COMMENT_COUNT");
            commentQuery.setParameterList("blogEntriesIds", blogEntryMap.keySet());

            commentQuery.setResultTransformer(
              new ResultTransformer() {
                  public Object transformTuple(Object[] result, String[] aliases) {
                      BlogEntry be = blogEntryMap.get( ((BigInteger)result[0]).longValue() );
                      be.setCommentCount( ((BigInteger)result[1]).longValue() );
                      return null;
                  }
                  public List transformList(List list) { return list; }
              }
            );
            commentQuery.list();
        }

        return result;
    }

    public Long countBlogEntries(WikiDirectory startDir, WikiDocument ignoreDoc, Integer year, Integer month, Integer day, String tag) {
        return countBlogEntries(startDir, ignoreDoc, false, false, false, year, month, day, tag).get(0).getNumOfEntries();
    }

    public List<BlogEntryCount> countAllBlogEntriesGroupByYearMonth(WikiDirectory startDir, WikiDocument ignoreDoc, String tag) {
        return countBlogEntries(startDir, ignoreDoc, true, true, false, null, null, null, tag);
    }

    private List<BlogEntryCount> countBlogEntries(WikiDirectory startDir, WikiDocument ignoreDoc,
                                                 final boolean projectYear, final boolean projectMonth, final boolean projectDay,
                                                 Integer limitYear, Integer limitMonth, Integer limitDay,
                                                 String tag) {

        // Sanity input check
        if (projectDay && (!projectMonth || !projectYear))
            throw new IllegalArgumentException("Can't project on day without months or year");
        if (projectMonth && !projectYear)
            throw new IllegalArgumentException("Can't project on month without year");

        StringBuilder queryString = new StringBuilder();

        queryString.append("select count(doc.NODE_ID) as NUM_OF_ENTRIES").append(" ");
        if (projectYear) queryString.append(", ").append("year(doc2.CREATED_ON) as YEAR");
        if (projectMonth) queryString.append(", ").append("month(doc2.CREATED_ON) as MONTH");
        if (projectDay) queryString.append(", ").append("day(doc2.CREATED_ON) as DAY");
        queryString.append(" ");

        queryString.append(getblogEntryFromClause(tag));
        queryString.append(getBlogEntryWhereClause(ignoreDoc, limitYear, limitMonth, limitDay, tag));

        if (projectYear || projectMonth || projectDay) queryString.append("group by").append(" ");
        if (projectYear)    queryString.append("year(doc2.CREATED_ON)");
        if (projectMonth)   queryString.append(", month(doc2.CREATED_ON)");
        if (projectDay)     queryString.append(", day(doc2.CREATED_ON)");

        if (projectYear || projectMonth || projectDay) queryString.append("order by").append(" ");
        if (projectYear)    queryString.append("YEAR desc");
        if (projectMonth)   queryString.append(", MONTH desc");
        if (projectDay)     queryString.append(", DAY desc");

        SQLQuery query = getSession().createSQLQuery(queryString.toString());

        bindBlogEntryWhereClause(query, startDir, ignoreDoc, limitYear, limitMonth, limitDay, tag);

        query.setComment("Finding blogEntry counts");
        query.addScalar("NUM_OF_ENTRIES", Hibernate.LONG);
        if (projectYear)    query.addScalar("YEAR", Hibernate.INTEGER);
        if (projectMonth)   query.addScalar("MONTH", Hibernate.INTEGER);
        if (projectDay)     query.addScalar("DAY", Hibernate.INTEGER);

        query.setResultTransformer(
            new ResultTransformer() {
                public Object transformTuple(Object[] result, String[] aliases) {
                    BlogEntryCount beCount = new BlogEntryCount();
                    beCount.setNumOfEntries( (Long)result[0] );
                    if (projectYear)    beCount.setYear( (Integer)result[1] );
                    if (projectMonth)   beCount.setMonth( (Integer)result[2] );
                    if (projectDay)     beCount.setDay( (Integer)result[3] );
                    return beCount;
                }
                public List transformList(List list) { return list; }
            }
        );

        return (List<BlogEntryCount>) query.list();
    }

    private Session getSession() {
        return ((Session)((org.jboss.seam.persistence.EntityManagerProxy) restrictedEntityManager).getDelegate());
    }

}