Gabriel Corneanu wrote:
> 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.
Check
PRAGMA secure_delete;
(and disable if it was enabled by default; it would be rendered ineffective by
your trick anyway).
> 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")
Probably there are way to make it work (altering PRAGMA schema_version; or
something), but I think it is way to hackerish and unsafe to use such tricks in
anything resembling production code.
Basically, you break consistency of your database image (and then kind-of-"fix"
it with vacuum;).
> (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.
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
