public List<ContentVersionVO> getContentVersionVOList(Integer contentTypeDefinitionId, Integer excludeContentTypeDefinitionId, Integer languageId, boolean showDeletedItems, Integer stateId, Integer lastContentVersionId, Integer limit, Integer cvIdSpan, boolean ascendingOrder, Database db, boolean includeSiteNode, Integer maxContentVersionIdForLanguageVersion) throws Exception
{
logger.info("maxContentVersionIdForLanguageVersion for " + languageId + "=" + maxContentVersionIdForLanguageVersion);
List<ContentVersionVO> contentVersionVOList = new ArrayList<ContentVersionVO>();
Timer t = new Timer();
StringBuffer SQL = new StringBuffer();
if(CmsPropertyHandler.getUseShortTableNames() != null && CmsPropertyHandler.getUseShortTableNames().equalsIgnoreCase("true"))
{
SQL.append("CALL SQL select * from (select cv.contVerId, cv.stateId, cv.modifiedDateTime, cv.verComment, cv.isCheckedOut, cv.isActive, cv.contId, cv.languageId, cv.versionModifier, cv.verValue, (select count(*) from cmContVerDigAsset cvda where cvda.contVerId = cv.contVerId) AS assetCount ");
if(includeSiteNode)
SQL.append(", (select sn.siNoId from cmSiNo sn where sn.metaInfoContentId = c.contId) AS siNoId, (select sn.name from cmSiNo sn where sn.metaInfoContentId = c.contId) AS siteNodeName ");
else
SQL.append(", -1 as siNoId, '' as siteNodeName ");
SQL.append(" from cmCont c, cmContVer cv ");
SQL.append("WHERE ");
SQL.append("c.isDeleted = $1 AND ");
SQL.append("cv.stateId >= $2 AND ");
SQL.append("cv.isActive = $3 AND ");
SQL.append("cv.contId = c.contId AND ");
SQL.append("cv.contVerId = ( ");
SQL.append(" select max(contVerId) from cmContVer cv2 ");
SQL.append(" WHERE ");
SQL.append(" cv2.contId = cv.contId AND ");
SQL.append(" cv2.languageId = cv.languageId AND ");
SQL.append(" cv2.isActive = cv.isActive AND ");
SQL.append(" cv2.stateId >= $4 ");
SQL.append(" ) ");
int index = 5;
if(contentTypeDefinitionId != null)
{
SQL.append(" AND c.contentTypeDefId = $" + index + "");
index++;
}
if(excludeContentTypeDefinitionId != null)
{
SQL.append(" AND c.contentTypeDefId <> $" + index + "");
index++;
}
if(languageId != null)
{
SQL.append(" AND cv.languageId = $" + index + "");
index++;
}
if(lastContentVersionId != null && lastContentVersionId > 0)
{
SQL.append(" AND cv.contVerId > $" + index + " and cv.contVerId < $" + (index+1) + "");
index++;
index++;
}
else
{
SQL.append(" AND cv.contVerId > $" + index + " and cv.contVerId < $" + (index+1) + "");
index++;
index++;
}
//SQL.append(" AND rownum<=$" + index + " ");
SQL.append(" order by cv.contVerId " + (ascendingOrder ? "" : "DESC") + ") where rownum<=$" + index + " AS org.infoglue.cms.entities.content.impl.simple.IndexFriendlyContentVersionImpl");
}
else
{
SQL.append("CALL SQL select cv.contentVersionId, cv.stateId, cv.modifiedDateTime, cv.versionComment, cv.isCheckedOut, cv.isActive, cv.contentId, cv.languageId, cv.versionModifier, cv.versionValue, (select count(*) from cmContentVersionDigitalAsset cvda where cvda.contentVersionId = cv.contentVersionId) AS assetCount ");
if(includeSiteNode)
SQL.append(", (select sn.siteNodeId from cmSiteNode sn where sn.metaInfoContentId = c.contentId) AS siteNodeId, (select sn.name from cmSiteNode sn where sn.metaInfoContentId = c.contentId) AS siteNodeName ");
else
SQL.append(", -1 as siteNodeId, '' as siteNodeName ");
SQL.append(" from cmContent c, cmContentVersion cv ");
SQL.append("WHERE ");
SQL.append("c.isDeleted = $1 AND ");
SQL.append("cv.stateId >= $2 AND ");
SQL.append("cv.isActive = $3 AND ");
SQL.append("cv.contentId = c.contentId AND ");
SQL.append("cv.contentVersionId = ( ");
SQL.append(" select max(contentVersionId) from cmContentVersion cv2 ");
SQL.append(" WHERE ");
SQL.append(" cv2.contentId = cv.contentId AND ");
SQL.append(" cv2.languageId = cv.languageId AND ");
SQL.append(" cv2.isActive = cv.isActive AND ");
SQL.append(" cv2.stateId >= $4 ");
SQL.append(" ) ");
int index = 5;
if(contentTypeDefinitionId != null)
{
SQL.append(" AND c.contentTypeDefinitionId = $" + index + "");
index++;
}
if(excludeContentTypeDefinitionId != null)
{
SQL.append(" AND c.contentTypeDefinitionId <> $" + index + "");
index++;
}
if(languageId != null)
{
SQL.append(" AND cv.languageId = $" + index + "");
index++;
}
if(lastContentVersionId != null && lastContentVersionId > 0)
{
SQL.append(" AND cv.contentVersionId > $" + index + " and cv.contentVersionId < $" + (index+1) + "");
index++;
index++;
}
else
{
SQL.append(" AND cv.contentVersionId > $" + index + " and cv.contentVersionId < $" + (index+1) + "");
index++;
index++;
}
SQL.append(" order by cv.contentVersionId " + (ascendingOrder ? "" : "DESC") + " limit $" + index + " AS org.infoglue.cms.entities.content.impl.simple.IndexFriendlyContentVersionImpl");
}
//logger.error("SQL:" + SQL);
//logger.info("SQL:" + SQL);
//logger.info("parentSiteNodeId:" + parentSiteNodeId);
//logger.info("showDeletedItems:" + showDeletedItems);
OQLQuery oql = db.getOQLQuery(SQL.toString());
oql.bind(showDeletedItems);
oql.bind(stateId);
oql.bind(true);
oql.bind(stateId);
if(contentTypeDefinitionId != null)
oql.bind(contentTypeDefinitionId);
if(excludeContentTypeDefinitionId != null)
oql.bind(excludeContentTypeDefinitionId);
if(languageId != null)
oql.bind(languageId);
if(lastContentVersionId != null && lastContentVersionId > 0)
{
oql.bind(lastContentVersionId);
oql.bind(lastContentVersionId + (ascendingOrder? cvIdSpan : -cvIdSpan));
}
else
{
oql.bind(0);
oql.bind((ascendingOrder? cvIdSpan : -cvIdSpan));
}
//if(CmsPropertyHandler.getUseShortTableNames() == null || !CmsPropertyHandler.getUseShortTableNames().equalsIgnoreCase("true"))
oql.bind(limit);
QueryResults results = oql.execute(Database.READONLY);
logger.info("Getting all IndexFriendlyContentVersionImpl took:" + t.getElapsedTime());
while (results.hasMore())
{
IndexFriendlyContentVersionImpl contentVersion = (IndexFriendlyContentVersionImpl)results.next();
contentVersionVOList.add(contentVersion.getValueObject());
//System.out.print(".");
//String versionKey = "" + contentVersion.getValueObject().getContentId() + "_" + contentVersion.getLanguageId() + "_" + stateId + "_contentVersionVO";
//CacheController.cacheObjectInAdvancedCache("contentVersionCache", versionKey, contentVersion.getValueObject(), new String[]{CacheController.getPooledString(2, contentVersion.getValueObject().getId()), CacheController.getPooledString(1, contentVersion.getValueObject().getContentId())}, true);
//CacheController.cacheObjectInAdvancedCache("contentVersionCache", "" + contentVersion.getId(), contentVersion.getValueObject(), new String[]{CacheController.getPooledString(2, contentVersion.getValueObject().getId()), CacheController.getPooledString(1, contentVersion.getValueObject().getContentId())}, true);
}
logger.info("Fetching all IndexFriendlyContentVersionImpl took:" + t.getElapsedTime() + " and returned " + contentVersionVOList.size());
results.close();
oql.close();
if(maxContentVersionIdForLanguageVersion < 1000)