On Thu 29 Mar 2007, Tom Heath <tom.heath(a)gmail.com> wrote:
Using the whole-hog approach of expressing "who
tagged what with
what and when" might be a bit tricky, because if you edit an entry
that I placed in the Wolverton locale, who did the tagging? me?
both of us? and when? I'm not sure if this degree of provenance data
is held in the db or not.
You can get it out of the database, e.g.:
node name | version | category | username
-----------------------------+---------+----------------------+--------------
Assa, WC2H 8LH | 2 | Korean Food | bob
Assa, WC2H 8LH | 3 | Korean Food | Kake
Assa, WC2H 8LH | 1 | Restaurants | Claudia
Assa, WC2H 8LH | 2 | Restaurants | bob
Assa, WC2H 8LH | 3 | Restaurants | Kake
This means that Claudia added Assa and tagged it as a restaurant, then Bob
added the Korean Food tag. It also means that Bob agrees (or, at least,
doesn't disagree) with Claudia that it's a restaurant, and that Kake agrees
with both Bob and Claudia that it's a restaurant serving Korean Food.
(You can get the dates of the edits too, I just didn't bother in this extract.)
(I talked with zool about this many years ago - in a way, an edit that doesn't
remove a category or locale is a "vote" for that category/locale being
correct. We were wondering if we could use this in some way to "define" the
extent of a locale - as in, if 20 people agree that a point is in Bermondsey,
then it probably is. I don't think we have enough contributors for this to
work, but theoretically it's a nice idea.)
Here's the SQL, linewrapped for readability; the "substring" stuff was just
to make the output fit in 80 characters.
london=> select substring(node.name from 1 for 27) as "node name",
content.version,
substring(mc.metadata_value from 1 for 20) as "category",
mn.metadata_value as "username"
from content
inner join node on (content.node_id=node.id)
inner join metadata as mn on (content.node_id=mn.node_id
and content.version=mn.version
and mn.metadata_type='username')
inner join metadata as mc on (content.node_id=mc.node_id
and content.version=mc.version
and mc.metadata_type='category' )
order by node.name, mc.metadata_value, content.version;
Kake