CODEWITHSUNDEEP
c++databasesqlitesql-delete
Nocs
Nocs

Reputation: 71

C++ and Sqlite DELETE query doesn`t actually delete the value from the database file

I`ve came accross this issue on SQlite and c++ and i can't find any answer on it.

Everything is working fine in SQlite and c++ all queries all outputs all functions but i have this question that can`t find any solution around it.

I create a database MyTest.db I create a table test with an id and a name as fields I enter 2 values to each id=1 name=Name1 and id=2 name=Name2 I delete the 2nd value

The data inside table now says that i have only the id=1 with name=Name1

When i open my Mytest.db with notepad.exe the values that i have deleted such as id=2 name=Name2 are still inside the database file though that it doesn`t come to the data results of this table but still exists though.

What i like to ask from anyone that knows about it is this : Is there any other way that the value has to be deleted also from the database file or is it my mistake with the DELETE option of SQLITE (that i doubt it)

Its like the database file keeps collecting all the trash inside it without removing DELETED values from its tables...

Any help or suggestion is much appreciated

Upvotes: 2

Views: 1650

Answers (2)

Richard Hipp
Richard Hipp

Reputation: 161

If you use "PRAGMA secure_delete=ON;" then SQLite overwrites deleted content with zeros. See https://www.sqlite.org/pragma.html#pragma_secure_delete

Even with secure_delete=OFF, the deleted space will be reused (and overwritten) to store new content the next time you INSERT. SQLite does not leak disk space, nor is it necessary to VACUUM in order to reclaim space. But normally, deleted content is not overwritten as that uses extra CPU cycles and disk I/O.

Upvotes: 5

Sami Kuhmonen
Sami Kuhmonen

Reputation: 31153

Basically all databases only mark rows active or inactive, they won't delete the actual data from the file immediately. It would be a huge waste of time and resources, since that part of the file can just be reused.

Since your queries show that the row isn't active in results, is this in some way an issue? You can always run a VACUUM on the database if you want to reclaim the space, but I would just let the database engine handle everything by itself. It won't "keep collecting all the trash inside it", don't worry.

If you see that the file size is growing and the space is not reused, then you can issue vacuums from time to time.

You can also test this by just inserting other rows after deleting old ones. The engine should reuse those parts of the file at some point.

Upvotes: 1

Related Questions