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. 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.
-scott 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] > ----------------------------------------------------------------------------- > > ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------