[gnizr commit] r932 - 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 11:23:25 2008
New Revision: 932
Added:
trunk/gnizr-db/src/main/resources/sql/sp-popular.sql
trunk/gnizr-db/src/test/java/com/gnizr/db/dao/bookmark/TestBookmarkDBDao4.java
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/loadsp.sh
trunk/gnizr-db/src/test/resources/dbunit/bmarkdbdao/TestBookmarkDBDao4-input.xml
Log:
impl stored procedure to find popular bookmarks that are saved by users
during a specific period of time.
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 11:23:25 2008
@@ -496,6 +496,35 @@
}
return result;
}
+
+ public List<Bookmark> getPopularCommunityBookmarks(int inPastDays,
+ int maxCount) {
+ logger.debug("getPopularCommunityBookmarks: inPastDays="+inPastDays
+ ", 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);
+ ResultSet rs = stmt.executeQuery();
+ while(rs.next()){
+ Bookmark b = createBookmarkObject2(rs);
+ logger.debug("found bmark="+b);
+ bmarks.add(b);
+ }
+ } catch (SQLException e) {
+ logger.fatal(e);
+ }finally{
+ try {
+ DBUtil.cleanup(conn, stmt);
+ } catch (SQLException e) {
+ logger.fatal(e);
+ }
+ }
+ return bmarks;
+ }
}
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 11:23:25 2008
@@ -111,4 +111,13 @@
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.
+ * @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);
+
}
Modified: trunk/gnizr-db/src/main/resources/sql/loadsp.sh
==============================================================================
--- trunk/gnizr-db/src/main/resources/sql/loadsp.sh (original)
+++ trunk/gnizr-db/src/main/resources/sql/loadsp.sh Mon Jun 9 11:23:25 2008
@@ -40,3 +40,6 @@
echo "loading sp-geommarker.sql into $DB"
mysql -u $2 --password=$3 $DB < sp-geommarker.sql
+echo "loading sp-popular.sql into $DB"
+mysql -u $2 --password=$3 $DB < sp-popular.sql
+
Added: trunk/gnizr-db/src/main/resources/sql/sp-popular.sql
==============================================================================
--- (empty file)
+++ trunk/gnizr-db/src/main/resources/sql/sp-popular.sql Mon Jun 9
11:23:25 2008
@@ -0,0 +1,23 @@
+delimiter //
+###############################################################
+# PROCEDURE: getPopularBookmarks
+# INPUT: inPastDays, count
+# OUTPUT: NONE
+DROP PROCEDURE IF EXISTS getPopularBookmarks//
+CREATE PROCEDURE getPopularBookmarks(IN inPastDays INT,
+ IN maxCount INT)
+BEGIN
+ SET @inPastDays = inPastDays;
+ 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()
+ 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;
+END//
\ No newline at end of file
Added: trunk/gnizr-db/src/test/java/com/gnizr/db/dao/bookmark/TestBookmarkDBDao4.java
==============================================================================
--- (empty file)
+++
trunk/gnizr-db/src/test/java/com/gnizr/db/dao/bookmark/TestBookmarkDBDao4.java
Mon Jun 9 11:23:25 2008
@@ -0,0 +1,65 @@
+package com.gnizr.db.dao.bookmark;
+
+import java.util.GregorianCalendar;
+import java.util.List;
+
+import org.dbunit.dataset.IDataSet;
+import org.dbunit.dataset.xml.FlatXmlDataSet;
+
+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 {
+
+ private BookmarkDao bookmarkDao;
+
+ 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 {
+ super.tearDown();
+ }
+
+ @Override
+ protected IDataSet getDataSet() throws Exception {
+ return new FlatXmlDataSet(TestBookmarkDBDao4.class.getResourceAsStream("/dbunit/bmarkdbdao/TestBookmarkDBDao4-input.xml"));
+ }
+
+ public void testGetPopularCommunityBookmarks() throws Exception{
+ List<Bookmark> bmarks = bookmarkDao.getPopularCommunityBookmarks(1,2);
+ assertEquals(2,bmarks.size());
+ Bookmark bm1 = bmarks.get(0);
+ assertEquals(2,bm1.getLink().getId());
+
+ bmarks = bookmarkDao.getPopularCommunityBookmarks(1,4);
+ assertEquals(4,bmarks.size());
+ }
+
+}
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 11:23:25 2008
@@ -1,43 +1,21 @@
-<?xml version='1.0' encoding='UTF-8'?>
+<?xml version='1.0' encoding='UTF-8'?>
<dataset>
+
+ <user id="1" username="gnizr"
password="63073e692013dffeed4b7e0f30920ff8" fullname="gnizr"
created_on="2007-02-26 11:04:19.0" email="" acct_status="1"/>
+ <user id="2" username="hchen1"
password="cf95caeb19ac6b9743cb6398d68da02e" fullname="hchen1"
created_on="2007-02-26 11:04:33.0" email="harry...@gmail.com" acct_status="1"/>
+ <user id="3" username="joe"
password="cbff98184193fb964111f2662c38fe0d" fullname="joe"
created_on="2007-02-26 18:36:49.0" email="j...@example.com" acct_status="0"/>
+
+ <link id="1" mime_type_id="0"
url="http://www.w3.org/2005/Incubator/geo/Wiki/Geo_Update" url_hash="129ccb651f4172ce14b85ae538667253"/>
+ <link id="2" mime_type_id="0"
url="http://www.eclipse.org/projects/dev_process/bugzilla-use.php" url_hash="e317a5fad150e660dcab24f8ceb625a9"/>
+ <link id="3" mime_type_id="0"
url="http://www.databasejournal.com/features/mysql/article.php/10897_1587371_1" url_hash="a4be21d01fb334f8277ffb0f779c46e8"/>
+ <link id="4" mime_type_id="0"
url="http://www.databasejournal.com/features/mysql/article.php/1578331" url_hash="d0392c3cdfcbf29f3ed3521ac67f9833"/>
+ <link id="5" mime_type_id="0" url="http://www.acm.org/pubs/cacm/" url_hash="8faf3ae22299cbafc7f2c362ddb843cb"/>
+ <link id="6" mime_type_id="0"
url="http://www.economistshop.com/asp/default.asp" url_hash="7d28f151f775e984da8522a582c8c300"/>
+ <link id="7" mime_type_id="0"
url="http://www.geekyweekly.com/mycategoryorder" url_hash="8d5d402ae625e72bff236966b49c0dba"/>
+ <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"/>
- <user id="1" username="gnizr" password="63073e692013dffeed4b7e0f30920ff8"
- fullname="gnizr" created_on="2007-02-26 11:04:19.0" email=""
- acct_status="1"/>
- <user id="2" username="hchen1" password="cf95caeb19ac6b9743cb6398d68da02e"
- fullname="hchen1" created_on="2007-02-26 11:04:33.0"
- email="harry...@gmail.com" acct_status="1"/>
- <user id="3" username="joe" password="cbff98184193fb964111f2662c38fe0d"
- fullname="joe" created_on="2007-02-26 18:36:49.0" email="j...@example.com"
- acct_status="0"/>
-
- <link id="1" mime_type_id="0"
- url="http://www.w3.org/2005/Incubator/geo/Wiki/Geo_Update"
- url_hash="129ccb651f4172ce14b85ae538667253"/>
- <link id="2" mime_type_id="0"
- url="http://www.eclipse.org/projects/dev_process/bugzilla-use.php"
- url_hash="e317a5fad150e660dcab24f8ceb625a9"/>
- <link id="3" mime_type_id="0"
- url="http://www.databasejournal.com/features/mysql/article.php/10897_1587371_1"
- url_hash="a4be21d01fb334f8277ffb0f779c46e8"/>
-
- <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"
- />
-
- <tag id="1" tag="loc:columbia,md" count="3"/>
-
- <user_tag_idx id="1" user_id="2" tag_id="1" count="2"/>
-
- <bookmark_tag_idx id="1" bookmark_id="1" tag_id="1" count="1" position="0"/>
- <bookmark_tag_idx id="2" bookmark_id="2" tag_id="1" count="1" position="0"/>
-
- <link_tag_idx id="1" link_id="1" tag_id="1" count="1"/>
- <link_tag_idx id="2" link_id="2" tag_id="1" count="1"/>
-
-</dataset>
+ <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" />
+
+</dataset>
\ No newline at end of file