Ulrik Petersen wrote:
> [T]he way I understand SQLite, if you delete a row with the DELETE
> statement, the row will not actually be deleted, only marked as
> deleted. Then the next time you insert a row, the row may be reused.
> At least that's how I understand it... could somebody please correct
> me if I'm wrong?
>
Not correct. SQLite really does delete rows when you do a DELETE.
Under normal circumstances, a ROWID might be reused. The algorithm
for chosing a ROWID (assuming the user doesn't specify it) is to
first pick a integer that is one more than the current largest
ROWID. If the current largest ROWID is the maximum integer
(9223372036854775807) then an unused ROWID is picked at random.
So if you do this:
CREATE TABLE test1(x);
INSERT INTO test1 VALUES('one');
INSERT INTO test1 VALUES('two');
INSERT INTO test1 VALUES('three');
The rowids chosen will be 1, 2, and 3, respectively. If you
then do:
DELETE FROM test1 WHERE x='three';
INSERT INTO test1 VALUES('four');
When the insert of 'four' occurs, the largest existing
ROWID is 2, so 3 is chosen as the rowid of 'four'. The
ROWID of 3 gets reused. If you later do this:
DELETE FROM test1 WHERE x='two';
INSERT INTO test1 VALUES('five');
The ROWID chosen for 'five' is 4 because 4 is one more
than the current larest ROWID of 3. The ROWID of 2 is
not chosen even though it is available because that is not
how the rowid chooser algorithm described above works.
There is a new feature in CVS HEAD (not yet released) that
will always cause SQLite to generate a unique ROWID - one that
has never been used before and which is greater than every prior
ROWID. You can now use the AUTOINCREMENT keyword on an integer
primary key, like this:
CREATE TABLE test2(id INTEGER PRIMARY KEY AUTOINCREMENT, x);
An integer primary key is just an alias for the ROWID. If you
insert a NULL into the integer primary key, SQLite selects the
ROWID using the algorithm described above. But if the AUTOINCREMENT
keyword is specified, a different algorithm is used. The new
ROWID chosen is always one greater than the largest ROWID that
has ever existed in the table before. This guararantees that
ROWIDs chosen by SQLite will always be unique and will always
be increasing, even if you delete rows.
AUTOINCREMENT is a little slower than the normal way of
chosing ROWIDs so you should not use it unless you really do
need ROWIDs that are unique over the lifetime of the table.
But the speed penalty is not that great, so if unique and
monotonic ROWIDs are important to your application,
AUTOINCREMENT can be a handy feature.
Scheduled release date is Dec 14.
--
D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
