BibsonomyFeed

Ideas

Current messages

What can be seen in BibsonomyFeed?

UserX has "title" reviewed 5 \ * / show beginning of the review
UserX has commented "title" / show beginning of the comment
UserX has added "title"
New document for "title" / was updated
- Only for your own entries or groups with document-function
New link for "title"
New member in group "groupname"
Friend UserX added title
Group Member UserX added title
UserX added titles to the group "groupname"
- Relevant for group
NameX has updated his profile

Pictures and icons

Picture of person to show?
Picture of the website at URL or just websites icon
Publication-Icon show at publication
"Document added" icon
Profile-Icon
Group-Icon
Statistics-Icon:
"Did you know" icon for recurring messages?

Recurring messages

"Did you know ... " --- different background color?

Blog-Icon Blog-post title
Statistics-Icon: Total number of publications or bookmarks in .. (of which x max \ * rating and average of reviews)
- Total-System
- Groups
- your own
5 most popular tags (last 7 days)
3 Most Popular Authors (last 7 days)
The most active user (number of new entries in the past 30 days)
Most discussed entry
Most discussed entry (last 7 days)

Miscellaneous

No different feeds on different pages
View on all pages
Show only personalized feeds, if logged in.
Follow the day - e.g. user added the publication xxx by the followed tag..

Implementation in Bibsonomy

Activity-Types and used SQL-Queries

UserX has added "title"
~Query as to the user's user-page~ What users should be considered? Friends and group members and all user in their own sphere ,aswell as pursued users
UserX reviewed, commented "title"
Query like in dicussed-page
New entry for bibTeX (only for own entries, or groups with document-function)
Missing - document table with hash and date
Document for bibTeX has been updated (only for own entries or groups with document-function)
- Missing - does not work, there is no update-field
New URL for bibTeX entry
- Missing - does not work, the URL field does not have an update-field
New member name group "groupname"
- Missing, working with "start_date" field in groups table
Friend UserX added "title"
- like on Friends-page
Group member UserX added "title"
- like on Group-page, each on "/ group / XXXX"
UserX added titles to the group "groupname", add / relevant for group
- like on Group-page / relevant for "/ group / XXXX"
NameX updated his Profile
- Missing, works with date in "user_wiki" table
Blogpost-Title
- ?
Statistic
- ?
5 most popular tags (last 7 days)
- Popular-page
3 most popular authors (last 7 days)
- ?
The most active users (number of new entries in the past 30 days)
- ?
Most discussed entry / - (last 7 days) -?
Follow the day - user A has added the publication X with the pursued tag..
- Ok, on "/tags xxx"

Queries

Idea

UNION

Get from each area the last 10 relevant entries
Combine the result set and get the last 10 entries

Long confusing requests
Less flexible
Individual queries
Single queries in Java
Will be an object with multiple lists (divided by type) output (Java / JSON)
Widget: configurable which news you want to receive (Blog news, new users group, statistics, the day-following, ...)

SQL

UNION groups, friends, persecuted users, limited to 1000 entries on Biblicious: 1659 rows in set (14.04 sec)

(SELECT b.address, b.annote, b.booktitle, b.chapter, b.crossref, b.edition, b.howpublished, b.institution, b.journal, b.bkey, b.month, b.note, b.number, b.organization, b.pages, b.publisher, b.school, b.series, b.type, b.volume, b.day, b.url, b.content_id, b.description, b.bibtexKey, b.misc, b.bibtexAbstract, b.user_name, b.date, b.title, b.author, b.editor, b.year, b.entrytype, IF('folke' = b.user_name, b.privnote, NULL) AS privnote , b.scraperid , b.simhash1 AS interHash, b.simhash2 AS intraHash , t.change_date, t.tag_name, g.group_name, g.group, h.ctr AS count, c.rating_arithmetic_mean AS rating, c.number_of_ratings FROM (SELECT b.* FROM bibtex b, followers f WHERE f.user_name = 'folke' AND b.user_name = f.f_user_name AND b.group IN (0) ORDER BY date DESC LIMIT 1000 OFFSET 0 ) AS b JOIN bibhash h ON b.simhash1 = h.hash AND h.type = 1 LEFT JOIN bibtex my ON b.simHash1 = my.simHash1 AND my.user_name = 'folke' LEFT OUTER JOIN tas AS t ON b.content_id = t.content_id LEFT JOIN review_ratings_cache c ON (c.interHash = b.simhash1) , groupids AS g WHERE t.group = g.group AND my.user_name IS NULL ORDER BY b.date DESC, b.content_id DESC ) UNION ( SELECT b.address, b.annote, b.booktitle, b.chapter, b.crossref, b.edition, b.howpublished, b.institution, b.journal, b.bkey, b.month, b.note, b.number, b.organization, b.pages, b.publisher, b.school, b.series, b.type, b.volume, b.day, b.url, b.content_id, b.description, b.bibtexKey, b.misc, b.bibtexAbstract, b.user_name, b.date, b.title, b.author, b.editor, b.year, b.entrytype, IF('folke' = b.user_name, b.privnote, NULL) AS privnote , b.scraperid , b.simhash1 AS interHash, b.simhash2 AS intraHash , t.change_date, t.tag_name, g.group_name, h.ctr as count, g.group, c.rating_arithmetic_mean AS rating, c.number_of_ratings FROM bibtex b LEFT JOIN review_ratings_cache c ON (c.interHash = b.simhash1) , bibhash h, ((SELECT content_id, date FROM bibtex t1, groups g WHERE g.group = -1 AND g.user_name = t1.user_name AND t1.group IN ( 0 ) )UNION( SELECT content_id, date FROM bibtex b, groups g, friends f WHERE f.f_user_name = 'folke' AND f.tag_name = 'sys:network:bibsonomy-friend' AND g.user_name = f.user_name AND b.user_name = f.user_name AND g.group = -1 AND b.group = 2 )UNION( SELECT content_id, date  FROM bibtex b, groups g WHERE b.user_name = 'folke' AND g.user_name = b.user_name AND g.group = -1 ) ORDER BY date DESC LIMIT 1000 OFFSET 0) AS bb LEFT OUTER JOIN tas AS t ON bb.content_id = t.content_id, groupids AS g WHERE t.group = g.group AND b.simhash1 = h.hash AND h.type = 1 AND b.content_id = bb.content_id ORDER BY bb.date DESC, bb.content_id DESC ) UNION ( SELECT b.address, b.annote, b.booktitle, b.chapter, b.crossref, b.edition, b.howpublished, b.institution, b.journal, b.bkey, b.month, b.note, b.number, b.organization, b.pages, b.publisher, b.school, b.series, b.type, b.volume, b.day, b.url, b.content_id, b.description, b.bibtexKey, b.misc, b.bibtexAbstract, b.user_name, b.date, b.title, b.author, b.editor, b.year, b.entrytype, IF('folke' = b.user_name, b.privnote, NULL) AS privnote , b.scraperid , b.simhash1 AS interHash, b.simhash2 AS intraHash , t.change_date, t.tag_name, g.group_name, g.group, h.ctr AS count,  c.rating_arithmetic_mean AS rating, c.number_of_ratings FROM bibtex b LEFT JOIN review_ratings_cache c ON (c.interHash = b.simhash1) , bibhash h, (SELECT content_id, date FROM  bibtex b, friends f WHERE f.f_user_name = 'folke' AND f.tag_name = 'sys:network:bibsonomy-friend' AND b.user_name = f.user_name AND b.group = -1 ORDER BY date DESC LIMIT 1000 OFFSET 0 ) AS bb LEFT OUTER JOIN tas AS t ON bb.content_id = t.content_id, groupids AS g WHERE t.group = g.group AND b.simhash1 = h.hash AND h.type = 1 AND b.content_id = bb.content_id ORDER BY bb.date DESC, bb.content_id DESC );

UNION groups, friends, persecuted users, limited to 10 each Entries per L1573 and 10 total entries 10 rows in set (0.04 sec) - also after a RESET QUERY CACHE

(SELECT b.address, b.annote, b.booktitle, b.chapter, b.crossref, b.edition, b.howpublished, b.institution, b.journal, b.bkey, b.month, b.note, b.number, b.organization, b.pages, b.publisher, b.school, b.series, b.type, b.volume, b.day, b.url, b.content_id, b.description, b.bibtexKey, b.misc, b.bibtexAbstract, b.user_name, b.date, b.title, b.author, b.editor, b.year, b.entrytype, IF('folke' = b.user_name, b.privnote, NULL) AS privnote , b.scraperid , b.simhash1 AS interHash, b.simhash2 AS intraHash , t.change_date, t.tag_name, g.group_name, g.group, h.ctr AS count, c.rating_arithmetic_mean AS rating, c.number_of_ratings FROM (SELECT b.* FROM bibtex b, followers f WHERE f.user_name = 'folke' AND b.user_name = f.f_user_name AND b.group IN (0) ORDER BY date DESC LIMIT 10 OFFSET 0 ) AS b JOIN bibhash h ON b.simhash1 = h.hash AND h.type = 1 LEFT JOIN bibtex my ON b.simHash1 = my.simHash1 AND my.user_name = 'folke' LEFT OUTER JOIN tas AS t ON b.content_id = t.content_id LEFT JOIN review_ratings_cache c ON (c.interHash = b.simhash1) , groupids AS g WHERE t.group = g.group AND my.user_name IS NULL ORDER BY b.date DESC, b.content_id DESC ) UNION ( SELECT b.address, b.annote, b.booktitle, b.chapter, b.crossref, b.edition, b.howpublished, b.institution, b.journal, b.bkey, b.month, b.note, b.number, b.organization, b.pages, b.publisher, b.school, b.series, b.type, b.volume, b.day, b.url, b.content_id, b.description, b.bibtexKey, b.misc, b.bibtexAbstract, b.user_name, b.date, b.title, b.author, b.editor, b.year, b.entrytype, IF('folke' = b.user_name, b.privnote, NULL) AS privnote , b.scraperid , b.simhash1 AS interHash, b.simhash2 AS intraHash , t.change_date, t.tag_name, g.group_name, h.ctr as count, g.group, c.rating_arithmetic_mean AS rating, c.number_of_ratings FROM bibtex b LEFT JOIN review_ratings_cache c ON (c.interHash = b.simhash1) , bibhash h, ((SELECT content_id, date FROM bibtex t1, groups g WHERE g.group = -1 AND g.user_name = t1.user_name AND t1.group IN ( 0 ) )UNION( SELECT content_id, date FROM bibtex b, groups g, friends f WHERE f.f_user_name = 'folke' AND f.tag_name = 'sys:network:bibsonomy-friend' AND g.user_name = f.user_name AND b.user_name = f.user_name AND g.group = -1 AND b.group = 2 )UNION( SELECT content_id, date  FROM bibtex b, groups g WHERE b.user_name = 'folke' AND g.user_name = b.user_name AND g.group = -1 ) ORDER BY date DESC LIMIT 10 OFFSET 0) AS bb LEFT OUTER JOIN tas AS t ON bb.content_id = t.content_id, groupids AS g WHERE t.group = g.group AND b.simhash1 = h.hash AND h.type = 1 AND b.content_id = bb.content_id ORDER BY bb.date DESC, bb.content_id DESC ) UNION ( SELECT b.address, b.annote, b.booktitle, b.chapter, b.crossref, b.edition, b.howpublished, b.institution, b.journal, b.bkey, b.month, b.note, b.number, b.organization, b.pages, b.publisher, b.school, b.series, b.type, b.volume, b.day, b.url, b.content_id, b.description, b.bibtexKey, b.misc, b.bibtexAbstract, b.user_name, b.date, b.title, b.author, b.editor, b.year, b.entrytype, IF('folke' = b.user_name, b.privnote, NULL) AS privnote , b.scraperid , b.simhash1 AS interHash, b.simhash2 AS intraHash , t.change_date, t.tag_name, g.group_name, g.group, h.ctr AS count,  c.rating_arithmetic_mean AS rating, c.number_of_ratings FROM bibtex b LEFT JOIN review_ratings_cache c ON (c.interHash = b.simhash1) , bibhash h, (SELECT content_id, date FROM  bibtex b, friends f WHERE f.f_user_name = 'folke' AND f.tag_name = 'sys:network:bibsonomy-friend' AND b.user_name = f.user_name AND b.group = -1 ORDER BY date DESC LIMIT 10 OFFSET 0 ) AS bb LEFT OUTER JOIN tas AS t ON bb.content_id = t.content_id, groupids AS g WHERE t.group = g.group AND b.simhash1 = h.hash AND h.type = 1 AND b.content_id = bb.content_id ORDER BY bb.date DESC, bb.content_id DESC ) UNION ( SELECT b2.address, b2.annote, b2.booktitle, b2.chapter, b2.crossref, b2.edition, b2.howpublished, b2.institution, b2.journal, b2.bkey, b2.month, b2.note, b2.number, b2.organization, b2.pages, b2.publisher,   b2.school, b2.series, b2.type, b2.volume, b2.day, b2.url, b2.content_id, b2.description, b2.bibtexKey,   b2.misc, b2.bibtexAbstract, b2.user_name, b2.date, b2.title, b2.author, b2.editor, b2.year, b2.entrytype,   IF('folke' = b2.user_name, b2.privnote, NULL) AS privnote, b2.simhash1 AS interHash, b2.simhash2 AS intraHash    , b2.scraperid  , t.change_date, t.tag_name, g.group_name, g.group, b2.count,    c.rating_arithmetic_mean AS rating, c.number_of_ratings FROM (SELECT t1.*, h.ctr AS count, if (d.change_date is not null, d.change_date, d.date ) orderdate FROM bibtex t1 FORCE INDEX(user_name_date_content_id_idx), bibhash h, discussion d WHERE    t1.simhash1 = h.hash AND h.type = 1 AND t1.user_name = 'folke' AND d.interHash = t1.simhash1 ORDER BY orderdate DESC LIMIT 10 OFFSET 0) AS b2 LEFT OUTER JOIN tas AS t ON b2.content_id = t.content_id LEFT JOIN review_ratings_cache c ON (c.interHash = b2.simhash1), groupids AS g   WHERE b2.group = g.group   ORDER BY orderdate DESC, b2.content_id DESC ) ORDER BY date DESC LIMIT 10 OFFSET 0;

Basic-Query

Groups

Last 10 Posts of the members of my groups At present, only one group is queried. The query must be adjusted so that all groups are considered.

   <select id="getBibTexForGroup" resultMap="BibTexCommon.bibtexPost" parameterClass="bibtexParam">
        SELECT <include refid ="allBibtexAttributesWithSimHashes"/>, t.change_date, t.tag_name, g.group_name, h.ctr as count, g.group, <include refid="reviewRatingsRows" />
        FROM bibtex b <include refid="reviewRatingsJoinPublication" />, bibhash h,
             ((SELECT content_id, date                <!-- publications from users of group which currUser may see -->
               FROM bibtex t1, groups g
               WHERE g.group = #groupId#
                     AND g.user_name = t1.user_name    <!-- user owns this publication -->
                     <include refid="inGroups"/>
              )UNION(
               SELECT content_id, date                <!-- publications from users of group which have currUser as friend -->
               FROM bibtex b, groups g, friends f
               WHERE <include refid="matchGroupMemberTrust"/>    <!-- currUser is friend of a group member (user) -->
                     AND b.user_name = f.user_name    <!-- user owns this publication -->
                     AND g.group = #groupId#
                     AND b.group = #groupTypeFriends# <!-- publication is only for friends -->
              )UNION(
               SELECT content_id, date                <!-- currUsers publications, ... -->
               FROM bibtex b, groups g
               WHERE b.user_name = #userName#
                     AND g.user_name = b.user_name    <!-- only, if currUser ... -->
                     AND g.group = #groupId#          <!-- is in this group       -->
              )
              ORDER BY date DESC
              LIMIT #limit# OFFSET #offset#) AS bb
        LEFT OUTER JOIN tas AS t ON bb.content_id = t.content_id, groupids AS g  <!-- join with tas (get tags) -->
        WHERE t.group = g.group                                                  <!-- join groupname -->
              AND <include refid="bibtexSimHash"/>                               <!-- join counts, ... -->
              AND b.content_id = bb.content_id                                   <!-- join rest of entry -->
              <include refid="sysTagsEntryType"/>
              <include refid="sysTagsYear"/>
        ORDER BY bb.date DESC, bb.content_id DESC
    </select>

Friends

Last 10 posts of my friends

   <select id="getBibTexByUserFriends" resultMap="BibTexCommon.bibtexPost" parameterClass="bibtexParam">
        SELECT <include refid="allBibtexAttributesWithSimHashes"/>, t.change_date, t.tag_name, g.group_name, g.group, h.ctr AS count, <include refid="reviewRatingsRows" />
        FROM bibtex b <include refid="reviewRatingsJoinPublication" />, bibhash h,
            (SELECT content_id, date                <!-- publications from users of group which currUser may see -->
             FROM bibtex b, friends f
             WHERE <include refid="matchTrustersResource"/>  <!-- take alle rows, which are owned by friend --> 
                   AND b.group = #groupId#
             ORDER BY date DESC
             LIMIT #limit# OFFSET #offset# ) AS bb
        LEFT OUTER JOIN tas AS t ON bb.content_id = t.content_id, groupids AS g  <!-- join with tas (get tags) -->
        WHERE t.group = g.group                                                  <!-- join groupname -->
              AND <include refid="bibtexSimHash"/>                               <!-- join counts, ... -->
              AND b.content_id = bb.content_id                                   <!-- join rest of entry -->
              <include refid="sysTagsEntryType"/>
        ORDER BY bb.date DESC, bb.content_id DESC
    </select>

Pursued Users

Last 10 posts of my pursued users

   <select id="getBibTexByFollowedUsers" resultMap="BibTexCommon.bibtexPost" parameterClass="bibtexParam">
        SELECT <include refid="allBibtexAttributesWithSimHashes"/>, t.change_date, t.tag_name, g.group_name, g.group, h.ctr AS count,  <include refid="reviewRatingsRows" />
        FROM (SELECT b.*                             <!-- publications from users currUser is following which currUser may see -->
              FROM bibtex b, followers f
              WHERE f.user_name = #userName#         <!-- retrieve all people wich currUser is following -->
                    AND b.user_name = f.f_user_name  <!-- take alle rows, which are owned by followed users -->                                                   
                    AND b.group IN                   <!--  iterate over all groups the logged-in user may see -->
                            (<iterate property="groups" conjunction=",">$groups[]$</iterate>)
              ORDER BY date DESC
              LIMIT #limit# OFFSET #offset# ) AS b
        JOIN bibhash h ON <include refid="bibtexSimHash"/>                         <!-- join counts -->
        LEFT JOIN bibtex my ON b.simHash1 = my.simHash1 AND my.user_name = #userName#  <!--  exclude posts of logged-in user (1) -->
        LEFT OUTER JOIN tas AS t ON b.content_id = t.content_id <include refid="reviewRatingsJoinPublication" />, groupids AS g     <!-- join with tas (get tags) -->
        WHERE t.group = g.group                                                    <!-- join groupname -->
              AND my.user_name IS NULL                                             <!--  exclude posts of logged-in user (2) -->
        ORDER BY b.date DESC, b.content_id DESC
    </select>

My last discussed entries

   <select id="getBibTexForUserLastRatedbySomeone" resultMap="BibTexCommon.bibtexPost" parameterClass="bibtexParam">
        SELECT <include refid ="bibtexAttributes2"/>, t.change_date, t.tag_name, g.*, b2.count, <include refid="reviewRatingsRows" />, if (d.change_date is not null, d.change_date, d.date ) orderdate
        FROM (SELECT t1.*, h.ctr AS count
              FROM bibtex t1 FORCE INDEX(user_name_date_content_id_idx), bibhash h
              WHERE <include refid="bibtexSimHash3"/>
                    AND t1.user_name = #requestedUserName#  
                    <include refid="restrictToGroups"/>
                    <include refid="sysTagsEntryType"/>
                    <include refid="sysTagsYear"/>                    
              ORDER BY date DESC
              LIMIT #limit# OFFSET #offset#) AS b2
        LEFT OUTER JOIN tas AS t ON b2.content_id = t.content_id 
        INNER JOIN discussion d ON (d.interHash = b2.simhash1) 
        <include refid="reviewRatingsJoinPublication2" />, groupids AS g
        WHERE b2.group = g.group
        ORDER BY orderdate DESC, b2.content_id DESC
    </select>

Spheres

Last 10 Posts by members of my Sphere

Currently, only one Sphere is always queried. It has to be adjusted to consider all Spheres in one query.

Server

There are essentially two types, to get new data by PUSH from the server (Comet). When streaming a permanent connection is established. The server sends data to the client and the connection remains. With Ajax long polling a connection is established and the client asks the server for new data. The connection remains for as long until the server returns the data, then it is dismantled and immediately a new one established.

Advantages of long polling via XMLHttpRequest

Almost all browsers support this method
Error handling is possible
Many servers support this method
For each browser there is constantly one connection established

For more information about the web application model Comet please visit

http://en.wikipedia.org/wiki/Comet_(programming)

Wiki

BibSonomy / development / features / BibsonomyFeed