Re: Feature freeze imminent

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