Hello,
I have the following scenario: I need to "clear"/"initialize" a db file
while potential readers are active (polling for data).
The "normal" way to do it is begin a transaction, drop all tables, recreate
tables, commit (vacuum to regain space).
The biggest problem is that dropping a "very large" table (many GB) takes a
lot of time.
I could very well delete the file, but that fails if any reader has it open.
I tried with
delete from sqlite_master where type in ("table","view","index")
(of course after setting writable schema)
This works very fast, but I have a different problem...
I can't recreate tables because it looks like sqlite still knows about them
?? Even if "select * from sqlite_master" returns nothing??
Dropping takes the same long time as originally did...
It WORKS if I do a "vacuum" first, but it won't work inside a transaction...
I had to wrap everything in a transaction to block readers seeing an
"empty" file. Therefore I also can't close/reopen etc...
Test this from shell in a test db:
<Code>
create table test(a);
insert into test values(1);
select * from sqlite_master;
pragma writable_schema=1;
delete from sqlite_master where type="table";
pragma writable_schema=0;
select * from sqlite_master;
create table test(b);
vacuum;
create table test(b);
</Code>
How can I solve this??
Any help appreciated.
Gabriel
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
