[gnizr commit] r933 - in trunk/gnizr-db/src: main/java/com/gnizr/db/dao/bookmark main/resources/sql test/java/co...
codesite...@google.com
Date: Mon Jun 9 13:37:08 2008
New Revision: 933
Modified:
trunk/gnizr-db/src/main/java/com/gnizr/db/dao/bookmark/BookmarkDBDao.java
trunk/gnizr-db/src/main/java/com/gnizr/db/dao/bookmark/BookmarkDao.java
trunk/gnizr-db/src/main/resources/sql/sp-popular.sql
trunk/gnizr-db/src/test/java/com/gnizr/db/dao/bookmark/TestBookmarkDBDao4.java
trunk/gnizr-db/src/test/resources/dbunit/bmarkdbdao/TestBookmarkDBDao4-input.xml
Log:
changed getPopularBookmarks method to allow the clients to defined the
start and end time of the interval
Modified: trunk/gnizr-db/src/main/java/com/gnizr/db/dao/bookmark/BookmarkDBDao.java
==============================================================================
---
trunk/gnizr-db/src/main/java/com/gnizr/db/dao/bookmark/BookmarkDBDao.java (original)
+++
trunk/gnizr-db/src/main/java/com/gnizr/db/dao/bookmark/BookmarkDBDao.java
Mon Jun 9 13:37:08 2008
@@ -24,6 +24,7 @@
import java.sql.Timestamp;
import java.sql.Types;
import java.util.ArrayList;
+import java.util.Date;
import java.util.List;
import javax.sql.DataSource;
@@ -497,17 +498,17 @@
return result;
}
- public List<Bookmark> getPopularCommunityBookmarks(int inPastDays,
- int maxCount) {
- logger.debug("getPopularCommunityBookmarks: inPastDays="+inPastDays
+ ", maxCount="+maxCount);
+ public List<Bookmark> getPopularCommunityBookmarks(Date start, Date
end, int maxCount) {
+ logger.debug("getPopularCommunityBookmarks: start= " + start + ",
end="+ end + ", maxCount="+maxCount);
Connection conn = null;
CallableStatement stmt = null;
List<Bookmark> bmarks = new ArrayList<Bookmark>();
try {
conn = dataSource.getConnection();
- stmt = conn.prepareCall("call getPopularBookmarks(?,?)");
- stmt.setLong(1,inPastDays);
- stmt.setLong(2,maxCount);
+ stmt = conn.prepareCall("call getPopularBookmarks(?,?,?)");
+ stmt.setTimestamp(1,new Timestamp(start.getTime()));
+ stmt.setTimestamp(2,new Timestamp(end.getTime()));
+ stmt.setInt(3,maxCount);
ResultSet rs = stmt.executeQuery();
while(rs.next()){
Bookmark b = createBookmarkObject2(rs);
Modified: trunk/gnizr-db/src/main/java/com/gnizr/db/dao/bookmark/BookmarkDao.java
==============================================================================
---
trunk/gnizr-db/src/main/java/com/gnizr/db/dao/bookmark/BookmarkDao.java (original)
+++
trunk/gnizr-db/src/main/java/com/gnizr/db/dao/bookmark/BookmarkDao.java
Mon Jun 9 13:37:08 2008
@@ -17,6 +17,7 @@
package com.gnizr.db.dao.bookmark;
import java.io.Serializable;
+import java.util.Date;
import java.util.List;
import com.gnizr.db.dao.Bookmark;
@@ -112,12 +113,12 @@
public boolean updateBookmark(Bookmark bm);
/**
- * Returns popular bookmarks saved by users in the past
<code>N</code> days.
- * @param inPastDays only look for popular bookmarks that are saved
in the defined
- * number of days in the past.
+ * Returns popular bookmarks saved by users during a time interval
+ * @param start the start date/time of the time interval
+ * @param end the end date/time of the time interval
* @param maxCount returns no more than this number of bookmarks
* @return a non-null list of bookmarks
*/
- public List<Bookmark> getPopularCommunityBookmarks(int inPastDays,
int maxCount);
+ public List<Bookmark> getPopularCommunityBookmarks(Date start, Date
end, int maxCount);
}
Modified: trunk/gnizr-db/src/main/resources/sql/sp-popular.sql
==============================================================================
--- trunk/gnizr-db/src/main/resources/sql/sp-popular.sql (original)
+++ trunk/gnizr-db/src/main/resources/sql/sp-popular.sql Mon Jun 9
13:37:08 2008
@@ -4,20 +4,22 @@
# INPUT: inPastDays, count
# OUTPUT: NONE
DROP PROCEDURE IF EXISTS getPopularBookmarks//
-CREATE PROCEDURE getPopularBookmarks(IN inPastDays INT,
+CREATE PROCEDURE getPopularBookmarks(IN start DATETIME,
+ IN end DATETIME,
IN maxCount INT)
BEGIN
- SET @inPastDays = inPastDays;
+ SET @start = start;
+ SET @end = end;
SET @maxCount = maxCount;
PREPARE STMT FROM "
SELECT *, bookmarkTags(bookmark.id) as tags, null as folders
FROM link, user,
(SELECT *, count(*) as count FROM bookmark
- WHERE bookmark.last_updated > SUBDATE(NOW(), INTERVAL ? DAY)
- AND bookmark.last_updated < NOW()
+ WHERE ? <= bookmark.last_updated
+ AND bookmark.last_updated < ?
GROUP BY bookmark.link_id
ORDER BY count DESC LIMIT ?) AS bookmark
WHERE link.id = bookmark.link_id
AND user.id = bookmark.user_id";
- EXECUTE STMT USING @inPastDays, @maxCount;
+ EXECUTE STMT USING @start, @end, @maxCount;
END//
Modified: trunk/gnizr-db/src/test/java/com/gnizr/db/dao/bookmark/TestBookmarkDBDao4.java
==============================================================================
---
trunk/gnizr-db/src/test/java/com/gnizr/db/dao/bookmark/TestBookmarkDBDao4.java (original)
+++
trunk/gnizr-db/src/test/java/com/gnizr/db/dao/bookmark/TestBookmarkDBDao4.java
Mon Jun 9 13:37:08 2008
@@ -1,6 +1,7 @@
package com.gnizr.db.dao.bookmark;
-import java.util.GregorianCalendar;
+import java.text.SimpleDateFormat;
+import java.util.Date;
import java.util.List;
import org.dbunit.dataset.IDataSet;
@@ -8,8 +9,6 @@
import com.gnizr.db.dao.Bookmark;
import com.gnizr.db.dao.GnizrDBTestBase;
-import com.gnizr.db.dao.Link;
-import com.gnizr.db.dao.User;
public class TestBookmarkDBDao4 extends GnizrDBTestBase {
@@ -17,30 +16,7 @@
protected void setUp() throws Exception {
super.setUp();
-
bookmarkDao = new BookmarkDBDao(getDataSource());
- for(int i = 1; i < 6; i++){
- for(int j = 1; j < 4; j++){
- Bookmark bm1= new Bookmark();
- bm1.setLink(new Link(i));
- bm1.setTitle("BM_TITLE_l"+i+"_u"+j);
- bm1.setUser(new User(j));
- bm1.setCreatedOn(GregorianCalendar.getInstance().getTime());
- bm1.setLastUpdated(GregorianCalendar.getInstance().getTime());
- bookmarkDao.createBookmark(bm1);
- Thread.sleep(100);
- if((j % 2)>0){
- Bookmark bm2= new Bookmark();
- bm2.setLink(new Link(8));
- bm2.setTitle("BM_TITLE_l8_u"+j);
- bm2.setUser(new User(j));
- bm2.setCreatedOn(GregorianCalendar.getInstance().getTime());
- bm2.setLastUpdated(GregorianCalendar.getInstance().getTime());
- bookmarkDao.createBookmark(bm2);
- Thread.sleep(100);
- }
- }
- }
}
protected void tearDown() throws Exception {
@@ -53,13 +29,17 @@
}
public void testGetPopularCommunityBookmarks() throws Exception{
- List<Bookmark> bmarks = bookmarkDao.getPopularCommunityBookmarks(1,2);
+
+ SimpleDateFormat dformat = new SimpleDateFormat("yyyy-MM-dd");
+ Date start = dformat.parse("2007-03-01");
+ Date end = dformat.parse("2007-04-24");
+
+ List<Bookmark> bmarks = bookmarkDao.getPopularCommunityBookmarks(start,end,2);
assertEquals(2,bmarks.size());
Bookmark bm1 = bmarks.get(0);
- assertEquals(2,bm1.getLink().getId());
-
- bmarks = bookmarkDao.getPopularCommunityBookmarks(1,4);
- assertEquals(4,bmarks.size());
+ assertEquals(3,bm1.getLink().getId());
+ Bookmark bm2 = bmarks.get(1);
+ assertEquals(5,bm2.getLink().getId());
}
}
Modified: trunk/gnizr-db/src/test/resources/dbunit/bmarkdbdao/TestBookmarkDBDao4-input.xml
==============================================================================
---
trunk/gnizr-db/src/test/resources/dbunit/bmarkdbdao/TestBookmarkDBDao4-input.xml (original)
+++
trunk/gnizr-db/src/test/resources/dbunit/bmarkdbdao/TestBookmarkDBDao4-input.xml
Mon Jun 9 13:37:08 2008
@@ -15,7 +15,26 @@
<link id="8" mime_type_id="0"
url="http://www.gamepro.com/news.cfm?article_id=98728" url_hash="d1a8e491759cb30d11357c4776be9c66"/>
<link id="9" mime_type_id="0" url="http://www.gwu.edu/~nsarchiv/" url_hash="ee28bac7e55abb1dd886c7b531ead3fd"/>
<link id="10" mime_type_id="0" url="http://pipes.yahoo.com/" url_hash="cfde41e41831eaee774d11505166eb54"/>
-
+
<bookmark id="1" user_id="2" link_id="1" title="Geo Update - W3C
Geospatial Incubator Group Wiki" notes="" created_on="2007-02-26
11:04:36.0" last_updated="2007-02-26 11:04:36.0" />
+ <bookmark id="2" user_id="2" link_id="2" title="Eclipse Bugzilla
Use" notes="how the eclipse project uses Bugzilla"
created_on="2007-02-26 11:04:36.0" last_updated="2007-02-26 11:04:36.0"
/>
+ <bookmark id="3" user_id="2" link_id="3" title="Using Fulltext
Indexes in MySQL - Part 2, Boolean searches" notes=""
created_on="2007-02-26 11:04:36.0" last_updated="2007-02-26 11:04:36.0" />
+ <bookmark id="4" user_id="2" link_id="4" title="Using Fulltext
Indexes in MySQL - Part 1" notes="Using Fulltext Indexes in MySQL -
Part 1" created_on="2007-02-26 11:04:37.0" last_updated="2007-02-26
11:04:37.0" />
+ <bookmark id="5" user_id="2" link_id="5" title="Communications of
the ACM" notes="" created_on="2007-02-26 11:04:37.0"
last_updated="2007-02-26 11:04:37.0" />
+ <bookmark id="6" user_id="2" link_id="6" title="For all Economist
books and range of exclusive business accessories" notes=""
created_on="2007-02-26 11:04:37.0" last_updated="2007-02-26 11:04:37.0" />
+ <bookmark id="7" user_id="2" link_id="7" title="My Category Order -
WordPress Plugin" notes="" created_on="2007-02-26 11:04:37.0"
last_updated="2007-02-26 11:04:37.0" />
+ <bookmark id="8" user_id="2" link_id="8" title="New Wii feature
unveiled: Wii voting" notes="" created_on="2007-02-26 11:04:38.0"
last_updated="2007-02-26 11:04:38.0"/>
+ <bookmark id="9" user_id="2" link_id="9" title="The National
Security Archive" notes="The National Security Archive is an
independent non-governmental research institute and library located at
The George Washington University in Washington, D.C. The Archive
collects and publishes declassified documents acquired through the
Freedom of Info" created_on="2007-02-26 11:04:38.0"
last_updated="2007-02-26 11:04:38.0" />
+ <bookmark id="10" user_id="2" link_id="10" title="Pipes: Rewire the
web" notes="" created_on="2007-02-26 11:04:38.0"
last_updated="2007-02-26 11:04:38.0"/>
+
+ <bookmark id="11" user_id="3" link_id="1" title="Geo Update - W3C
Geospatial Incubator Group Wiki" notes="" created_on="2007-02-26
11:04:36.0" last_updated="2007-02-26 11:04:36.0" />
+ <bookmark id="12" user_id="3" link_id="2" title="Eclipse Bugzilla
Use" notes="how the eclipse project uses Bugzilla"
created_on="2007-02-26 11:04:36.0" last_updated="2007-02-26 11:04:36.0"
/>
+ <bookmark id="13" user_id="3" link_id="3" title="Using Fulltext
Indexes in MySQL - Part 2, Boolean searches" notes=""
created_on="2007-02-26 11:04:36.0" last_updated="2007-02-26 11:04:36.0" />
+ <bookmark id="14" user_id="3" link_id="4" title="Using Fulltext
Indexes in MySQL - Part 1" notes="Using Fulltext Indexes in MySQL -
Part 1" created_on="2007-02-26 11:04:37.0" last_updated="2007-02-26
11:04:37.0" />
+ <bookmark id="15" user_id="3" link_id="5" title="Communications of
the ACM" notes="" created_on="2007-02-26 11:04:37.0"
last_updated="2007-03-26 11:04:37.0" />
+
+ <bookmark id="16" user_id="1" link_id="1" title="Geo Update - W3C
Geospatial Incubator Group Wiki" notes="" created_on="2007-02-26
11:04:36.0" last_updated="2007-01-26 11:04:36.0" />
+ <bookmark id="17" user_id="1" link_id="2" title="Eclipse Bugzilla
Use" notes="how the eclipse project uses Bugzilla"
created_on="2007-02-26 11:04:36.0" last_updated="2007-02-26 11:04:36.0"
/>
+ <bookmark id="18" user_id="1" link_id="3" title="Using Fulltext
Indexes in MySQL - Part 2, Boolean searches" notes=""
created_on="2007-02-26 11:04:36.0" last_updated="2007-03-26 11:04:36.0" />
</dataset>