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? 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)? 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? -- Roar Bjørgum Rotvik _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
