postgresql – selecting maximum for each group

Posted on by
3

I had a requirement to return the thread with the most replies in each forum at JavaRanch‘s Coderanch forums.  In Postgresql 8.4, this would be very easy – just use the window functions.  Unfortunately, we aren’t on Postgresql 8.4 yet.  The other common pattern is something like

select stuff
from mytable t1
where date = (select max(date)
from mytable t2
where t1.key= t2.key)

This doesn’t work well for me either because the value I am comparing (number of posts is dynamic.)  I decided to use a stored procedure to “simplify” things.  I’ve written some stored procedures in Postgresql to do updates before and stored procedures in other languages to do queries so this didn’t seem like a huge task.

Postgresql calls them stored functions, so let’s proceed.  First you need to create a type to represent a row that gets returned by the stored function.

CREATE TYPE highlighted_topic_per_forum_holder AS
(last_user_id integer,
post_time timestamp without time zone,
<other columns go here>);

Then you create the stored procedure.  The outer loop goes through each forum.  The inner loop is the SQL that finds the post with the most replies that was posted to in some time period.  It uses a nested query with a limit to return only 1 thread per forum.  The rest of the SQL adds the relevant data.  See postgresql and JDBC for why it takes varchar rather than timestamp.

CREATE or replace FUNCTION highlighted_topic_per_forum(character varying)
RETURNS SETOF highlighted_topic_per_forum_holder AS
$BODY$
DECLARE
forum RECORD;
r highlighted_topic_per_forum_holder%rowtype;
BEGIN
for forum in EXECUTE 'select forum_id from jforum_forums where categories_id in (1,7)' loop
for r in EXECUTE 'select p.user_id AS last_user_id, p.post_time, p.attach AS attach, t.* '
|| 'from jforum_topics t, jforum_posts p, '
|| '(select topic_id, count(*) from jforum_posts '
|| ' where post_time >= date '' ' || $1 || ' '' '
|| ' and forum_id = ' || forum.forum_id
|| ' AND need_moderate = 0 '
|| ' group by topic_id order by 2 desc limit 1) nested '
|| ' where p.topic_id = nested.topic_id '
|| ' and p.post_id = t.topic_last_post_id '
|| ' order by post_time desc' loop
return next r;
end loop;
end loop;
return;
END;
$BODY$
LANGUAGE 'plpgsql';

find friends in social networking without a password

Posted on by
2

I’ve always been concerned about the whole “give us your e-mail password and we will tell you which of your friends are registered on our service” thing on social networking sites.  To the point that I refuse to give out the password.  If I give out my password, the sites can do whatever they want with it.  Surely there is a better way!

While I’ve been reading about open standards for such things, today was the first day I actually saw it in practice.  I registered for GoodReads this week.  When clicking on find friends, you see the usual – click yahoo/hotmail/gmail/AOL/facebook/twitter/plaxo.  When clicking you have the option to type your password.  For some, you have an alternate choice.  Marked as “new”.  This alternate choice actually looks secure.

Summary of providers

Provider Allows providing password to glean contacts Comments on Non-password access to glean contacts
Yahoo Yes Worked well – similar to google as described below
Hotmail Yes Allows, but don’t have a hotmail account so untried
Gmail Yes Worked great; see below
AOL Yes No access
Facebook No Allows, but didn’t try.  I have to allow GoodReads access to write on my wall not just see contacts and didn’t want to go through the remove process at Facebook.
Twitter Yes Have to temporarily allow more access, but easy to revoke after from twitter’s connections page.
Plaxo No Not sure.  Plaxo wasn’t clear enough about what information they would be getting so I didn’t say ok.

Walking through gmail

  1. Click “Or: sign in directly on Gmail. (new)”
  2. Takes to page at a GOOGLE URL saying “The site www.goodreads.com is requesting access to your Google Account for the product(s) listed below.  Google Contacts“
  3. Choose “grant access”
  4. [do stuff on GoodReads]
  5. Optional which I did because I only want to grant one time access – remove GoodReads from accessing my contacts list:
    1. Go to Google Accounts
    2. Click “change authorized websites”
    3. Click “revoke access”

The good

I am giving google my password.  Google already has my gmail password and is just checking it is correct.  I’m not passing it through GoodReads.  Google is also telling me specifically what information they are letting GoodReads see.

The bad

Just because I e-mailed someone once and they are in my Google contact list doesn’t mean I know them.  I also have to trust GoodReads won’t spam all my contacts.  Both of these problems exist with the old “give me your password” method.  I’m willing to accept both of these on a reputable site and not willing to provide a password.  So great progress.

my iPad in $10 and 10 days

Posted on by
Reply

I’ve now had my iPad for 10 days and spent $10 setting it up to be functional.  Or $610 if you count the iPad itself and the case.

Why I bought the iPad

I wanted to be able to read technical documents from the park.  The files could be PDFs, ZIP or HTML.  I will have many other uses for it as time goes on.  And already have – like checking my twitter and my e-mail from the couch!  My experiences so far have been mostly around being able to accomplish the original goal though.

What applications I downloaded right away

  1. GoodReader $0.99 – A much better PDF reader than the built in one.  Turning pages is harder than it needs to be, but the GoodReader team is working on a fix.
  2. iUnarchive $2.99 – To unzip files.  Works exactly as one would expect.
  3. DropBox free – A great way to get files from your computer to the iPad.  Just drop them in a folder on “main computer” and download from the iPad.  Only trick is to remember to choose as favorite so available offline on the iPad.
  4. Twitterrific $4.99 (ad supported version is free) – Twitter client.  The cute tweet noise for new tweets will get old fast.  I’ll turn that off once I get tired of it’s cuteness.

Total – $9.76

Thanks to my friends at JavaRanch for recommending these (and more applications) to greatly cut down on research time.

What I learned

A few “less than obvious” things I learned in the first 10 days:

  • Double tap to see menu when GoodReader in full screen mode
  • I still need to figure how to take notes while reading

Other setup

I was surprised by how little setup I had to do to get up and running.  I was able to create my iTunes account and activate in the Apple store so I didn’t need to waste time downloading it at home.  The only setting change I made was to password protect the iPad.

Speaking of the Apple store, it was a little odd that on Wednesday they told me there was a wait list and they couldn’t possibly estimate how long it was.  (implying weeks/months.)  The following day, I got a call my iPad was there.

Finally – my impressions

  1. The iPad is great.  It lets me read PDFs and zip files of text or Java code outside away from my laptop.  (It’s awkward using a laptop outside.)  I had no problem reading outside.  Even in the sun, I used the iPad case as a sun visor to protect the screen.
  2. I still need to find out if there is a way to easily take notes while reading.  Not just PDFs, but any application.  It’s a pain when looking at a zipped file because it doesn’t retain where you are when you get back.  I’ve resorted to paper.
  3. I haven’t missed the 3g I decided not to get one bit.  A surprisingly large number of places have free wifi.  And DropBox caches my documents for offline use very well.
  4. I can touch type alphabetic text on the iPad!  I type about twice as slow on the iPad than on a real computer.  But that’s much better than my hunt and peck speed!
  5. Special characters like the pipe are hidden well.  To get to them, you have to choose the numeric keyboard (obvious) and then the “#+=” button (not obvious)
  6. I have a lot more to explore.  I am happy with the iPad meeting my initial purposes and looking forward to it meeting even more.