John Hoffmann's Weblog

All | AI | Comedy | Cool Threads | General | Java | Open Source | Robotics | Solaris 10 | Wiki
« Wikis and SnipSnap... | Main | Referrer Spam »

20040722 Thursday July 22, 2004

Sortable Interface for Roller Announcing a new sortable interface for blogs.sun.com. The default sort is the standard reverse cronological - most recent post at the top, but the more interesting sorts are by number of blog posts by author and most commented on which should indicate the hottest threads. I've noticed over several days of refining this, that hot threads are not developing very often so this column is not changing much. Perhaps a better approach than simply number of comments might be a metric like: "currently hot" = ("number of comments" - "number of days since last comment"). So that cooling threads would fade away. Perhaps the most accurate would be to assign a declining weight for number of days old to every comment and sum. Thinking about the SQL for that makes my head hurt, here is the current query for you fellow SQL jockies out there (had to push MySql 4.0.18 with a left join since sub queries and views are not available in that version yet). 
select 
   substring(w.name,1,50) as weblog_name, 
   substring(e.title,1,50) as entry_title, 
   u.username as author, 
   e.pubtime as raw_date, 
   date_format(e.pubtime,'%M %e %H:%i') as pubtime, 
   count(c.id) as comment_count 
from 
   weblogentry as e, 
   website as w, 
   rolleruser as u 
left join 
   comment as c on c.entryid = e.id 
where 
   e.publishentry = 1 and 
   e.pubtime <= CURRENT_TIMESTAMP and 
   w.id = e.websiteid and 
   u.id = w.userid 
group by 
   e.id 
order by 
   weblog_name, 
   raw_date desc
I am always at my happiest when coding SQL - can't explain it, perhaps its the feeling of buring more CPU cyles per character of code than anything else I write. I then use a sorting class designed by Matthew to order the results - wouldn't have needed it with Oracle at my disposal, but when MySql gets views in v5.0 it will do the trick.

I wanted to add the category for each post, but that would naturally invite sorting and since I am displaying two posts per blogger, that would result in two different categories per row - and present a weird sorting experience.

I welcome any comments on the design and or ideas on new metrics.

(2004-07-22 16:10:31.0) Permalink Comments [5]

Comments:

Not to sound like a troll, but if you need views, etc, couldn't you just use PostgreSQL ?

Posted by Derek on July 22, 2004 at 07:45 PM PDT #

Well, the authors of Roller chose to use MySql. So when Sun chose to use Roller for blogs.sun.com that is what we installed. I did not know going in that MySql was so limited as to not have views and subselects. I would never choose it if I was writing my own app from scratch knowing what I know now.

I just did a quick search on PostGres docs, it looks like the only schema change would be to alter the MySql tinyint to PostGres smallint.

Now that we have to support both Oracle and MySql, I doubt anyone is interested in introducing PostGres to the mix - however it looks like migrating from MySql to PostGres is alot simpler than migration from MySql to Oracle.

Posted by hoffie on July 22, 2004 at 09:24 PM PDT #

Roller should be usable on any database supported by Hibernate. Currently it expressly supports MySql, Postgres, and hsqldb. Someone recently did the work to support MS-SQL (though it hasn't gotten into cvs yet). Oracle support should be possible. The admins of blogs.sun.com probably chose MySql because it is the 'common' deployment database for Roller.

Posted by Lance on July 23, 2004 at 07:13 AM PDT #

I use Roller with PostgreSQL at work and Roller 1.0 will support PostgreSQL out of the box.

Posted by Dave Johnson on August 04, 2004 at 07:39 PM PDT #

Thanks Roller authors! We are now centralizing a few MySql instances onto a set of redundant MySql systems supported 24/7. So now that MySql is becomming the institutionalized open source DB, at least in my local part of Sun, we'll probably stick with it and gain the desired functionality when we upgrade to MySql 5.x.

Posted by hoffie on August 06, 2004 at 02:21 PM PDT #

Post a Comment:

Comments are closed for this entry.