Scott Hess wrote: >Unfortunately, the reason fts2 couldn't be "fixed" was because you >can't perform the necessary ALTER TABLE if the column you're adding is >a primary key.
Sure, I was aware of this problem. >Since the only alternative would be to build a new >table and copy everything over, it seemed more reasonable to just let >the app developer do that, rather than forcing it on them under the >covers. True also. I know that my "compatible" proposal would not update existing FTS2.0 tables (with the vacuum bug) to FTS2.1 (fixed vacuum bug with rowid INTEGER PRIMARY key). But it should at least be possible to continue using old FTS2.0 tables with this new FTS2.1. It should also be possible (untested and highly speculative) for FTS2.0 to read tables generated by the new FTS2.1. Old FTS2.0 will just not be able to write to or update tables created by FTS2.1. However, since reading should work well, it update existing tables can be updated with the FTS2.1 module only, alleviating the need for a 2nd FTS modules just for updating. To sum up, I expect these benefits from my "rowid INTEGER PRIMARY KEY" suggestion: Reading: Fully upward and backward compatible. Not at all with FTS3. Writing: Upward compatible. Not with FTS3. Updating: Possible within the same FTS2 module. Requires extra FTS3 module otherwise. I have not written any code to test if all this does indeed make sense. Is anyone aware of any fallbacks, before I try? Regards, Ralf >On 8/31/07, Ralf Junker <[EMAIL PROTECTED]> wrote: >> This one just came to my mind: >> >> CREATE TABLE (rowid INTEGER PRIMARY KEY, t TEXT); >> >> This promotes "rowid" to a visible column "rowid" which does not change >> during a VACUUM. "rowid" is already a reserved word in SQLite. Maybe this >> option is even compatible to FTS2? >> >> Ralf >> >> >ext/fts3.c in the current code fixes the fts2-vs-vacuum problem by >> >adding "docid INTEGER PRIMARY KEY" to the %_content table. This >> >becomes an alias for rowid, and thus causes vacuum to not renumber >> >rowids. It is safe to add that column because the other columns in >> >%_content are constructed such that even the following: >> > >> >CREATE VIRTUAL TABLE t USING fts3(docid); >> > >> >will work fine. >> > >> >I'm considering whether I should take it one step further, and make >> >docid a reserved column name for fts3 tables. My rational is that >> >fts3 rowids are not quite the same as the rowids of regular tables - >> >in fact, some use-cases would encourage users of fts3 to use rowids in >> >exactly the way that fts2 was inappropriately using them! >> > >> >docid would be a hidden column, like rowid. That means that you'll >> >only see the column in SELECT and INSERT statements if you explicitly >> >reference it. It would operate WRT rowid exactly as an INTEGER >> >PRIMARY KEY column would. >> > >> >Opinions? >> > >> >-scott ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
