Alex Teslik wrote:
On Wed, 30 May 2007 15:18:18 -0400, John Elrick wrote
<snip>
After running a simple test, I confirmed a suspicion. VACUUM
doesn't reorder the ROWIDs, so you still have breaks.
My tests show otherwise:
SNIP
did I do something incorrectly?
Not incorrectly, just differently. In my test I unintentionally used an
INTEGER PRIMARY KEY:
CREATE TABLE FOO(
ID INTEGER PRIMARY KEY,
MYSTUFF TEXT
);
If you retrieve the ROWID from this test (full Ruby program at end):
CREATE TABLE FOO(
ID INTEGER INTEGER PRIMARY KEY,
MYSTUFF TEXT
);
INSERT INTO FOO VALUES (1, 'One');
INSERT INTO FOO VALUES (2, 'Two');
INSERT INTO FOO VALUES (3, 'Three');
You get this result:
["rowid", "ID", "MYSTUFF"]
["1", "1", "One"]
["2", "2", "Two"]
["3", "3", "Three"]
["rowid", "ID", "MYSTUFF"]
["1", "1", "One"]
["3", "3", "Three"]
I have confirmed that removing the PRIMARY KEY designator permits VACUUM
to reorder the ROWID. So I would amend my statement:
"After running a simple test, I confirmed a suspicion. VACUUM may not
reorder the ROWIDs if you use an INTEGER PRIMARY KEY, so you would still
have breaks under those conditions."
Thanks for running your test. I wouldn't have thought there would be a
difference.
John
Full program:
-------------
require 'sqlite3'
FILENAME = ":memory:"
$db = SQLite3::Database.new( FILENAME )
$db.execute_batch(<<eof
CREATE TABLE FOO(
ID INTEGER INTEGER PRIMARY KEY,
MYSTUFF TEXT
);
INSERT INTO FOO VALUES (1, 'One');
INSERT INTO FOO VALUES (2, 'Two');
INSERT INTO FOO VALUES (3, 'Three');
eof
)
$db.execute2('SELECT ROWID,* FROM FOO') do |i|
p i
end
$db.execute('DELETE FROM FOO WHERE ID = 2')
$db.execute('VACUUM')
$db.execute2('SELECT ROWID,* FROM FOO') do |i|
p i
end
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
