Re: Feature freeze imminent

On Tue, 27 Feb 2007 18:53:50 +0000 (UTC) Tuomo Valkonen <[email protected]> wrote: 

TV> 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.

TV> Yeah, that's one quite simple layout, but how would you make the
TV> intersection of the search results for different tags then? A
TV> simple "same row" intersection doesn't work anymore. Does SQL 
TV> have a more complicated intersection operation? And how efficient
TV> would it be? That's the bottleneck, I think. Plus there's no 
TV> structure for quick access to a known file name, which would seem

SQL is pretty good about about searching, especially if you index your
columns well.  Something like this, from the Oracle docs, should be
available in most good databases:

INTERSECT Example

The following statement combines the results with the INTERSECT
operator, which returns only those rows returned by both queries:

SELECT part 
    FROM orders_list1 
INTERSECT 
SELECT part 
    FROM orders_list2;

PART 
---------- 
TAILPIPE 

So for your case, it would be

SELECT fileid FROM tagset WHERE tag = 'A'
INTERSECT
SELECT fileid FROM tagset WHERE tag = 'B'

The known file name is just an extra column in the files table, and
searching for it is fast if the column is indexed.

Ted