[seam-commits] Seam SVN: r7515 - in trunk/examples/wiki: src/main/org/jboss/seam/wiki/core/dao and 3 other directories.

Author: christian.bauer(a)jboss.com Date: 2008-03-06 05:11:52 -0500 (Thu, 06 Mar 2008) New Revision: 7515 Added: trunk/examples/wiki/view/themes/sfwkorg/img/seam_icon_large.png Modified: trunk/examples/wiki/src/etc/WEB-INF/urlrewrite.xml trunk/examples/wiki/src/main/org/jboss/seam/wiki/core/dao/TagDAO.java trunk/examples/wiki/src/main/org/jboss/seam/wiki/plugin/forum/ForumDAO.java trunk/examples/wiki/src/main/org/jboss/seam/wiki/plugin/forum/ForumQueries.hbm.xml trunk/examples/wiki/view/plugins/tags/plugin.xhtml Log: Optimized forum queries Modified: trunk/examples/wiki/src/etc/WEB-INF/urlrewrite.xml =================================================================== --- trunk/examples/wiki/src/etc/WEB-INF/urlrewrite.xml 2008-03-05 19:03:14 UTC (rev 7514) +++ trunk/examples/wiki/src/etc/WEB-INF/urlrewrite.xml 2008-03-06 10:11:52 UTC (rev 7515) @@ -31,6 +31,13 @@ <to last="true" type="temporary-redirect">/robots.txt</to> </rule> + <!-- Disable anything that comes out of Microsoft --> + <rule> + <from>.*</from> + <condition name="remote-addr">65.55.210.81</condition> + <to last="true" type="temporary-redirect">/robots.txt</to> + </rule> + <!-- Default browser --> <rule> <from>.*</from> Modified: trunk/examples/wiki/src/main/org/jboss/seam/wiki/core/dao/TagDAO.java =================================================================== --- trunk/examples/wiki/src/main/org/jboss/seam/wiki/core/dao/TagDAO.java 2008-03-05 19:03:14 UTC (rev 7514) +++ trunk/examples/wiki/src/main/org/jboss/seam/wiki/core/dao/TagDAO.java 2008-03-06 10:11:52 UTC (rev 7515) @@ -28,6 +28,7 @@ @In protected EntityManager restrictedEntityManager; + // TODO: This query needs to be optimized, the nested subselect with in() is not good for MySQL, needs to be a join public List<DisplayTagCount> findTagCounts(WikiDirectory startDir, WikiFile ignoreFile, int limit, long minimumCount) { StringBuilder queryString = new StringBuilder(); Modified: trunk/examples/wiki/src/main/org/jboss/seam/wiki/plugin/forum/ForumDAO.java =================================================================== --- trunk/examples/wiki/src/main/org/jboss/seam/wiki/plugin/forum/ForumDAO.java 2008-03-05 19:03:14 UTC (rev 7514) +++ trunk/examples/wiki/src/main/org/jboss/seam/wiki/plugin/forum/ForumDAO.java 2008-03-06 10:11:52 UTC (rev 7515) @@ -159,7 +159,8 @@ .list(); getSession(true).getNamedQuery(unreadRepliesQuery) - .setParameter("parentDir", directory) + .setParameter("parentDirId", directory.getId()) + .setParameter("readAccessLevel", currentAccessLevel) .setParameter("lastLoginDate", lastLoginDate) .setComment("Finding unread replies") .setCacheable(true) @@ -220,10 +221,10 @@ .setResultTransformer( new ResultTransformer() { public Object transformTuple(Object[] result, String[] strings) { - if (topicInfoMap.containsKey((Long)result[0])) { - TopicInfo info = topicInfoMap.get( (Long)result[0] ); - info.setNumOfReplies((Long)result[1]); - info.setLastComment((WikiComment)result[2]); + if (topicInfoMap.containsKey((Long)result[1])) { + TopicInfo info = topicInfoMap.get( (Long)result[1] ); + info.setNumOfReplies((Long)result[2]); + info.setLastComment((WikiComment)result[0]); } return null; } Modified: trunk/examples/wiki/src/main/org/jboss/seam/wiki/plugin/forum/ForumQueries.hbm.xml =================================================================== --- trunk/examples/wiki/src/main/org/jboss/seam/wiki/plugin/forum/ForumQueries.hbm.xml 2008-03-05 19:03:14 UTC (rev 7514) +++ trunk/examples/wiki/src/main/org/jboss/seam/wiki/plugin/forum/ForumQueries.hbm.xml 2008-03-06 10:11:52 UTC (rev 7515) @@ -57,6 +57,7 @@ group by dir0.NODE_ID </sql-query> + <!-- TODO: This is based on the idea that the last topic is "one in a second", we should use a subselect with limit 1 order by --> <query name="forumLastTopic"> select f.id, t @@ -137,19 +138,32 @@ and t.createdOn > :lastLoginDate ]]></query> - <!-- TODO: Optimize this with native SQL, move where-clause subselect into from-clause join --> - <query name="forumUnreadReplies"><![CDATA[ - select - distinct t.id, t.parent.id - from - WikiDirectory f, WikiDocument t, WikiComment c - where - f.parent = :parentDir - and t.parent = f - and (t.headerMacrosString like '%forumPosting%' or t.headerMacrosString like '%forumStickyPosting%') - and c.nodeInfo.nsThread in (select c2.nodeInfo.nsThread from WikiComment c2 where c2.parent = t) - and c.createdOn > :lastLoginDate - ]]></query> + <sql-query name="forumUnreadReplies"> + <return-scalar column="UNREAD_TOPIC_ID" type="long"/> + <return-scalar column="FORUM_ID" type="long"/> + <![CDATA[ + select + distinct doc0.NODE_ID as UNREAD_TOPIC_ID, doc1.PARENT_NODE_ID as FORUM_ID + from + WIKI_DIRECTORY dir0 + inner join WIKI_NODE dir1 on dir0.NODE_ID=dir1.NODE_ID, + WIKI_DOCUMENT doc0 + inner join WIKI_NODE doc1 on doc0.NODE_ID=doc1.NODE_ID + inner join WIKI_NODE ct0 on ct0.PARENT_NODE_ID = doc1.NODE_ID + inner join WIKI_COMMENT ct1 on ct0.NODE_ID = ct1.NODE_ID, + WIKI_COMMENT c0 + inner join WIKI_NODE c1 on c0.NODE_ID=c1.NODE_ID + where + dir1.READ_ACCESS_LEVEL <= :readAccessLevel + and doc1.READ_ACCESS_LEVEL <= :readAccessLevel + and c1.READ_ACCESS_LEVEL <= :readAccessLevel + and dir1.PARENT_NODE_ID= :parentDirId + and doc1.PARENT_NODE_ID=dir1.NODE_ID + and (doc0.HEADER_MACROS like '%forumPosting%' or doc0.HEADER_MACROS like '%forumStickyPosting%') + and c0.NS_THREAD in (ct1.NS_THREAD) + and c1.CREATED_ON > :lastLoginDate + ]]> + </sql-query> <query name="forumUnreadTopicsInForum"><![CDATA[ select @@ -162,18 +176,28 @@ and t.createdOn > :lastLoginDate ]]></query> - <!-- TODO: Optimize this with native SQL, move where-clause subselect into from-clause join --> - <query name="forumUnreadRepliesInForum"><![CDATA[ - select - distinct t.id, t.parent.id - from - WikiDocument t, WikiComment c - where - t.parent = :parentDir - and (t.headerMacrosString like '%forumPosting%' or t.headerMacrosString like '%forumStickyPosting%') - and c.nodeInfo.nsThread in (select c2.nodeInfo.nsThread from WikiComment c2 where c2.parent = t) - and c.createdOn > :lastLoginDate - ]]></query> + <sql-query name="forumUnreadRepliesInForum"> + <return-scalar column="UNREAD_TOPIC_ID" type="long"/> + <return-scalar column="FORUM_ID" type="long"/> + <![CDATA[ + select + distinct doc0.NODE_ID as UNREAD_TOPIC_ID, doc1.PARENT_NODE_ID as FORUM_ID + from + WIKI_DOCUMENT doc0 + inner join WIKI_NODE doc1 on doc0.NODE_ID=doc1.NODE_ID + inner join WIKI_NODE ct0 on ct0.PARENT_NODE_ID = doc1.NODE_ID + inner join WIKI_COMMENT ct1 on ct0.NODE_ID = ct1.NODE_ID, + WIKI_COMMENT c0 + inner join WIKI_NODE c1 on c0.NODE_ID=c1.NODE_ID + where + doc1.READ_ACCESS_LEVEL <= :readAccessLevel + and c1.READ_ACCESS_LEVEL <= :readAccessLevel + and doc1.PARENT_NODE_ID= :parentDirId + and (doc0.HEADER_MACROS like '%forumPosting%' or doc0.HEADER_MACROS like '%forumStickyPosting%') + and c0.NS_THREAD in (ct1.NS_THREAD) + and c1.CREATED_ON > :lastLoginDate + ]]> + </sql-query> <query name="forumTopicsCount"> select count(t) from WikiDocument t @@ -240,35 +264,78 @@ by STICKY desc, LAST_POST desc ]]></sql-query> - <!-- TODO: Optimize this with native SQL, move where-clause subselect into from-clause join--> - <query name="forumTopicsReplies"><![CDATA[ + <sql-query name="forumTopicsReplies"> + <return alias="c" class="org.jboss.seam.wiki.core.model.WikiComment"/> + <return-join alias="u" property="c.createdBy"/> + <return-scalar column="TOPIC_ID" type="long"/> + <return-scalar column="NUM_OF_REPLIES" type="long"/> + <![CDATA[ select - t.id, - count(c), - c2 + cl1.NODE_ID as NODE_ID, + cl1.OBJ_VERSION as OBJ_VERSION, + cl1.RATING as RATING, + cl1.AREA_NR as AREA_NR, + cl1.NAME as NAME, + cl1.WIKINAME as WIKINAME, + cl1.CREATED_ON as CREATED_ON, + cl1.CREATED_BY_USER_ID as CREATED_BY_USER_ID, + cl1.LAST_MODIFIED_ON as LAST_MODIFIED_ON, + cl1.LAST_MODIFIED_BY_USER_ID as LAST_MODIFIED_BY_USER_ID, + cl1.WRITE_ACCESS_LEVEL as WRITE_ACCESS_LEVEL, + cl1.READ_ACCESS_LEVEL as READ_ACCESS_LEVEL, + cl1.WRITE_PROTECTED as WRITE_PROTECTED, + cl1.PARENT_NODE_ID as PARENT_NODE_ID, + + cl0.SUBJECT as SUBJECT, + cl0.FROM_USER_NAME as FROM_USER_NAME, + cl0.FROM_USER_EMAIL as FROM_USER_EMAIL, + cl0.FROM_USER_HOMEPAGE as FROM_USER_HOMEPAGE, + cl0.USE_WIKI_TEXT as USE_WIKI_TEXT, + cl0.NS_LEFT as NS_LEFT, + cl0.NS_RIGHT as NS_RIGHT, + cl0.NS_THREAD as NS_THREAD, + + u.USER_ID, u.ACTIVATED, u.ACTIVATION_CODE, u.CREATED_ON, u.EMAIL, u.FIRSTNAME, u.LAST_LOGIN_ON, + u.LASTNAME, u.MEMBER_HOME_WIKI_DIRECTORY_ID, u.PASSWORDHASH, u.USER_PROFILE_ID, u.USERNAME, u.OBJ_VERSION, + + doc0.NODE_ID as TOPIC_ID, + count(c0.NODE_ID) as NUM_OF_REPLIES from - WikiDocument t, - WikiComment c, - WikiComment c2 join fetch c2.createdBy u + WIKI_DOCUMENT doc0 + inner join WIKI_NODE ct0 on ct0.PARENT_NODE_ID = doc0.NODE_ID + inner join WIKI_COMMENT ct1 on ct0.NODE_ID = ct1.NODE_ID, + WIKI_COMMENT c0, + WIKI_COMMENT cl0 + inner join WIKI_NODE cl1 on cl0.NODE_ID = cl1.NODE_ID + inner join USERS u on cl1.CREATED_BY_USER_ID = u.USER_ID where - t.id in (:topicIds) - and c.nodeInfo.nsThread in (select c3.nodeInfo.nsThread from WikiComment c3 where c3.parent = t) - and c2.createdOn = - (select max(c4.createdOn) from WikiComment c4 where c4.nodeInfo.nsThread in - (select c5.nodeInfo.nsThread from WikiComment c5 where c5.parent = t) ) + doc0.NODE_ID in (:topicIds) + and c0.NS_THREAD in (ct1.NS_THREAD) + and cl0.NODE_ID = ( + select ct3.NODE_ID + from WIKI_COMMENT ct + inner join WIKI_NODE ct1 on ct.NODE_ID = ct1.NODE_ID + inner join WIKI_COMMENT ct2 on ct2.NS_THREAD in (ct.NS_THREAD) + inner join WIKI_NODE ct3 on ct2.NODE_ID = ct3.NODE_ID, + WIKI_NODE d0 + inner join WIKI_DOCUMENT d1 on d0.NODE_ID = d1.NODE_ID + where + d0.NODE_ID = doc0.NODE_ID and + ct1.PARENT_NODE_ID = d0.NODE_ID + order by ct3.CREATED_ON desc + limit 1 + ) group by - t.id, + cl1.OBJ_VERSION, cl1.RATING, cl1.AREA_NR, cl1.NAME, cl1.WIKINAME, cl1.CREATED_ON, cl1.CREATED_BY_USER_ID, cl1.LAST_MODIFIED_ON, + cl1.LAST_MODIFIED_BY_USER_ID, cl1.WRITE_ACCESS_LEVEL, cl1.READ_ACCESS_LEVEL, cl1.WRITE_PROTECTED, cl1.PARENT_NODE_ID, + cl0.SUBJECT, cl0.FROM_USER_NAME, cl0.FROM_USER_EMAIL, cl0.FROM_USER_HOMEPAGE, cl0.USE_WIKI_TEXT, cl0.NS_LEFT, cl0.NS_RIGHT, cl0.NS_THREAD, - c2.id, c2.nodeInfo.nsLeft, c2.nodeInfo.nsRight, c2.nodeInfo.nsThread, - c2.version, c2.parent, - c2.areaNumber, c2.name, c2.wikiname, c2.createdBy, c2.createdOn, c2.lastModifiedBy, c2.lastModifiedOn, c2.readAccessLevel, c2.writeAccessLevel, c2.writeProtected, - c2.subject, c2.fromUserName, c2.fromUserEmail, c2.fromUserHomepage, c2.useWikiText, + u.USER_ID, u.ACTIVATED, u.ACTIVATION_CODE, u.CREATED_ON, u.EMAIL, u.FIRSTNAME, u.LAST_LOGIN_ON, + u.LASTNAME, u.MEMBER_HOME_WIKI_DIRECTORY_ID, u.PASSWORDHASH, u.USER_PROFILE_ID, u.USERNAME, u.OBJ_VERSION, - u.id, u.version, u.firstname, u.lastname, u.username, u.passwordHash, u.email, u.activated, u.activationCode, u.createdOn, u.lastLoginOn, u.memberHome, u.profile + doc0.NODE_ID + ]]></sql-query> - ]]></query> - - <!-- TODO: Optimize this with native SQL, move where-clause subselect into from-clause join --> <sql-query name="forumPostersAndRatingPoints"> <return class="org.jboss.seam.wiki.core.model.User"/> <return-scalar column="RATING_POINTS" type="long"/> @@ -284,21 +351,17 @@ com2.RATING from WIKI_DOCUMENT doc0 - inner join WIKI_FILE doc1 on doc0.NODE_ID=doc1.NODE_ID - inner join WIKI_NODE doc2 on doc0.NODE_ID=doc2.NODE_ID, + inner join WIKI_NODE doc1 on doc0.NODE_ID=doc1.NODE_ID + inner join WIKI_NODE ct0 on ct0.PARENT_NODE_ID = doc1.NODE_ID + inner join WIKI_COMMENT ct1 on ct0.NODE_ID = ct1.NODE_ID, WIKI_COMMENT com1 inner join WIKI_NODE com2 on com1.NODE_ID=com2.NODE_ID inner join USERS user2 on com2.CREATED_BY_USER_ID=user2.USER_ID inner join USER_ROLE roles1 on user2.USER_ID=roles1.USER_ID inner join ROLES roles2 on roles1.ROLE_ID=roles2.ROLE_ID where - doc2.PARENT_NODE_ID=:parentDirId - and (com1.NS_THREAD in - (select com3.NS_THREAD from WIKI_COMMENT com3 - inner join WIKI_NODE com4 on com3.NODE_ID=com4.NODE_ID - where com4.PARENT_NODE_ID=doc0.NODE_ID - ) - ) + doc1.PARENT_NODE_ID=:parentDirId + and com1.NS_THREAD in (ct1.NS_THREAD) and (roles2.NAME not in (:ignoreUserInRoles)) and com2.RATING<>0 ) as ur Modified: trunk/examples/wiki/view/plugins/tags/plugin.xhtml =================================================================== --- trunk/examples/wiki/view/plugins/tags/plugin.xhtml 2008-03-05 19:03:14 UTC (rev 7514) +++ trunk/examples/wiki/view/plugins/tags/plugin.xhtml 2008-03-06 10:11:52 UTC (rev 7515) @@ -44,7 +44,7 @@ <s:fragment rendered="#{preferences.get('Tags', currentMacro).cloud}"> <ui:repeat var="tagCount" value="#{tagsSortedByCount}"> - <s:span styleClass="tagCloudItem noWrapWhitespace" style="font-size: #{50+(100/tagsAggregator.highestTagCount*tagCount.count)}%;"> + <s:span styleClass="tagCloudItem noWrapWhitespace" style="font-size: #{70+(100/tagsAggregator.highestTagCount*tagCount.count)}%;"> <s:span rendered="#{param.tag != tagCount.tag and preferences.get('Tags', currentMacro).linkToCurrentDocument}" styleClass="undecoratedLink"> <h:outputLink value="#{wikiURLRenderer.renderURL(currentDocument)}/Tag/#{wiki:encodeURL(tagCount.tag)}">#{tagCount.tag}</h:outputLink> </s:span> Added: trunk/examples/wiki/view/themes/sfwkorg/img/seam_icon_large.png =================================================================== (Binary files differ) Property changes on: trunk/examples/wiki/view/themes/sfwkorg/img/seam_icon_large.png ___________________________________________________________________ Name: svn:mime-type + application/octet-stream