On Tue, Feb 27, 2007 at 06:53:50PM +0000, Tuomo Valkonen wrote:
> On 2007-02-27, Sam Mason <[EMAIL PROTECTED]> wrote:
> > In more human terms I would have a table of files, the purpose of which
> > is to give you the fileid (sort of like the inode number), a table of
> > tags that define what tags are known, and a table containing the pairs
> > (tag,fileid) that define what files have what tags.
>
> Yeah, that's one quite simple layout but how would you make the
> intersection of the search results for different tags then?
It looks as though Ted has shown you how to use INTERSECT, another
(hacky) option is:
SELECT fileid
FROM filetags
WHERE tag IN ('foo', 'bar')
GROUP BY fileid
HAVING COUNT(*) = 2;
> A
> simple "same row" intersection doesn't work anymore. Does SQL
> have a more complicated intersection operation? And how efficient
> would it be? That's the bottleneck, I think.
Efficient? I thought this was supposed to be an initial exploration of
the problem! It'll be fine for a few thousand objects, but I really
don't know.
> Plus there's no
> structure for quick access to a known file name, which would seem
> mportant to me.
There's nothing in the database to check to see that the set of tags are
actually unique either. I'd put a column in the files table and use a
trigger to keep it up to date. You could put a unique constraint on the
key to make the database check that the key remains unique and use this
table to find the file associated with a key.
I've just written some code[1] for Postgres that should do everything
I've mentioned. I've not played much with triggers before so if anyone
knows how to do it better let me know!
> (Yes, I am total newbie wrt. SQL.)
We all have to start somewhere! Like all languages, it VERY good for
solving some problems in and crap at others. It's a reasonable fit
here, but not perfect.
Sam
[1] http://sam.samason.me.uk/~sam/repos/setfs/filetags.sql
