fts1 and fts2 have a design flaw which assumes semantics for the standard SQLite rowid which aren't actually provided by SQLite across calls to VACUUM. fts3 will fix this, either making the rowid be a persistent idenfier across VACUUM, or by exposing a new id (or docid) column which operates as a persistent identifier. Either way, you could reliably tie an fts3 table to some other table. Ideally, you could hide this all behind a view, but I'm not sure that the MATCH operator can be exposed through a view.
The problem with your ideal solution is that there are likely to be three or four such solutions which don't have significant overlap. I would like to avoid generalizing fts too much, and keep things focussed very tightly on the text-indexing part, leaving higher-level stuff to SQLite proper, where possible. This may leave fts feeling a little bit grafted on, but ... well, it _is_ a little bit grafted on, that's a feature! -scott On 8/15/07, Samuel R. Neff <[EMAIL PROTECTED]> wrote: > > With FTS3 can you specify the rowid to use in SQL or is it always automatic? > It seems like most commonly you'd want the FTS data to match up with a real > table using the same key and not have to store the FTS key in a separate > table. Ideally I'd want to be able to include a single foreign key indexed > integer field so a FTS table would like like: > > OID > FK_ID > FTS_FIELDS > > So you can associate multiple FTS records with a single standard record > without having to create a linking table (linking tables typically define a > many-to-many relationship and here we have a one-to-many relationship). > > FTS's job isn't to enforce constraints, but it allowing users to link FTS > data to regular data. > > Sam > > > ------------------------------------------- > We're Hiring! Seeking a passionate developer to join our team building Flex > based products. Position is in the Washington D.C. metro area. If interested > contact [EMAIL PROTECTED] > > > > ----------------------------------------------------------------------------- > To unsubscribe, send email to [EMAIL PROTECTED] > ----------------------------------------------------------------------------- > > ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
