On May 8, 2008, at 2:22 PM, Roar Bjørgum Rotvik wrote: > Hi, > > I see that when I perform a "delete from ..." to delete a row in > the database, the actual > data still remains in the database file. > > Creates a test database and insert some values: > # sqlite3 test.db > SQLite version 3.3.6 > Enter ".help" for instructions > sqlite> create table foo (a integer, b text); > sqlite> insert into foo values (1, "one"); > sqlite> insert into foo values (2, "two"); > sqlite> insert into foo values (3, "three"); > sqlite> .q > > # hexdump -C test.db > 00000000 53 51 4c 69 74 65 20 66 6f 72 6d 61 74 20 33 00 |SQLite > format 3.| > 00000010 04 00 01 01 00 40 20 20 00 00 00 04 00 00 00 00 > |.....@ ........| > 00000020 00 00 00 00 00 00 00 00 00 00 00 01 00 00 00 04 > |................| > 00000030 00 00 00 00 00 00 00 00 00 00 00 01 00 00 00 00 > |................| > 00000040 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 > |................| > * > 00000060 00 00 00 00 0d 00 00 00 01 03 c8 00 03 c8 00 00 > |................| > 00000070 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 > |................| > * > 000003c0 00 00 00 00 00 00 00 00 36 01 06 17 13 13 01 55 > |........6......U| > 000003d0 74 61 62 6c 65 66 6f 6f 66 6f 6f 02 43 52 45 41 | > tablefoofoo.CREA| > 000003e0 54 45 20 54 41 42 4c 45 20 66 6f 6f 20 28 61 20 |TE > TABLE foo (a | > 000003f0 69 6e 74 65 67 65 72 2c 20 62 20 74 65 78 74 29 | > integer, b text)| > 00000400 0d 00 00 00 03 03 e4 00 03 f8 03 ef 03 e4 00 00 > |................| > 00000410 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 > |................| > * > 000007e0 00 00 00 00 09 03 03 01 17 03 74 68 72 65 65 07 > |..........three.| > 000007f0 02 03 01 13 02 74 77 6f 06 01 03 09 13 6f 6e 65 > |.....two.....one| > 00000800 > > Delete row with a=2: > # sqlite3 test.db > SQLite version 3.3.6 > Enter ".help" for instructions > sqlite> delete from foo where a=2; > sqlite> select * from foo; > 1|one > 3|three > sqlite> .q > > See that sqlite does not show row 2 as it is deleted. > > # hexdump -C test.db > 00000000 53 51 4c 69 74 65 20 66 6f 72 6d 61 74 20 33 00 |SQLite > format 3.| > 00000010 04 00 01 01 00 40 20 20 00 00 00 05 00 00 00 00 > |.....@ ........| > 00000020 00 00 00 00 00 00 00 00 00 00 00 01 00 00 00 04 > |................| > 00000030 00 00 00 00 00 00 00 00 00 00 00 01 00 00 00 00 > |................| > 00000040 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 > |................| > * > 00000060 00 00 00 00 0d 00 00 00 01 03 c8 00 03 c8 00 00 > |................| > 00000070 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 > |................| > * > 000003c0 00 00 00 00 00 00 00 00 36 01 06 17 13 13 01 55 > |........6......U| > 000003d0 74 61 62 6c 65 66 6f 6f 66 6f 6f 02 43 52 45 41 | > tablefoofoo.CREA| > 000003e0 54 45 20 54 41 42 4c 45 20 66 6f 6f 20 28 61 20 |TE > TABLE foo (a | > 000003f0 69 6e 74 65 67 65 72 2c 20 62 20 74 65 78 74 29 | > integer, b text)| > 00000400 0d 03 ef 00 02 03 e4 00 03 f8 03 e4 03 e4 00 00 > |................| > 00000410 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 > |................| > * > 000007e0 00 00 00 00 09 03 03 01 17 03 74 68 72 65 65 00 > |..........three.| > 000007f0 00 00 09 13 02 74 77 6f 06 01 03 09 13 6f 6e 65 > |.....two.....one| > 00000800 > > But the data "two" (and possibly rest of data from this deleted > row) is still intact in > the database. > > I guess this is because the data blocks used to contain the second > row (deleted row) is > now marked as deleted by sqlite and may be reused later?
Correct. > I tried two more inserts into the table, but the "two" string is > still visible in the > database file, the file instead grows. What is the rule for reusing > a deleted block (if it > is so)? I guess it depends on the size of the new records inserted how space is allocated for them. Assuming they are small (i.e. 4,'four') I would have expected them to be inserted somewhere between 0x400 and 0x800. > Is it possible to configure/tweak sqlite to actually delete or at > least memset(0) a > deleted row? I need to make sure that a deleted row is actually > deleted from the database > file (at the same time as the sql command "delete" is performed), > is this possible? Compile with SQLITE_SECURE_DELETE to memset(0) deleted data. Dan. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
