Re: [sqlite] rowid versus docid for fts3.
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] -
Re: [sqlite] rowid versus docid for fts3.
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] -
Re: [sqlite] rowid versus docid for fts3.
--- 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? Making rowid public changes the default INSERT behavior of a table. sqlite> create table t(a); sqlite> insert into t values(1); sqlite> drop table t; sqlite> create table t(rowid INTEGER PRIMARY KEY, a); sqlite> insert into t values(1); SQL error: table t has 2 columns but 1 values were supplied But if you always use named columns for INSERT, you would be okay. Sick sense of humor? Visit Yahoo! TV's Comedy with an Edge to see what's on, when. http://tv.yahoo.com/collections/222 - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] rowid versus docid for fts3.
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] -
[sqlite] rowid versus docid for fts3.
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] -
