Adam,
As it currently stands, fts2 (and fts1, if you ever do a delete) is
broken if you use VACUUM. It's not an issue of how the table is
defined, it's an issue of the internal definitions - when fixed, it
will just be fixed, you won't define your tables differently.
[There's been some discussion and a bug entered.]
I'm being a little patient with the fix. Initially I was freaking
out, I thought this was a recent change, but it looks like fts2 and
fts1 have possibly been broken for their entire existence, and nobody
has noticed until now, so I'm more concerned with a good fix than a
fast fix. That and I have other demands on my time. Probably will be
fixed this coming week.
-scott
On 7/21/07, Adam Megacz <[EMAIL PROTECTED]> wrote:
"Scott Hess" <[EMAIL PROTECTED]> writes:
> In fts tables all columns other than rowid are of type TEXT. It
> doesn't matter what you put in the type, they will be of type TEXT.
> The rowid is the standard SQLite rowid, so it does provide an INTEGER
> PRIMARY KEY AUTOINCREMENT column.
Scott, thanks for your reply. However, it seems that by default, the
magic ROWID column is of the eternally-unique variety; rather, it is
of the currently-unique variety:
If no ROWID is specified on the insert, an appropriate ROWID is
created automatically. The usual algorithm is to give the newly
created row a ROWID that is one larger than the largest ROWID in
the table prior to the insert. If the table is initially empty,
then a ROWID of 1 is used. If the largest ROWID is equal to the
largest possible integer (9223372036854775807 in SQLite version
3.0 and later) then the database engine starts picking candidate
ROWIDs at random until it finds one that is not previously used.
...
If a column has the type INTEGER PRIMARY KEY AUTOINCREMENT then a
slightly different ROWID selection algorithm is used....
http://www.sqlite.org/autoinc.html
What I was looking for is a column whose value is unique across the
lifetime of the table (not just over the rows currently present in
it). It seems that adding AUTOINCREMENT to a column in the table
achieves this effect. Is this not possible under FTS2?
Thanks again for your help.
- a
--
PGP/GPG: 5C9F F366 C9CF 2145 E770 B1B8 EFB1 462D A146 C380
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
