Re: [sqlite] listing free bytes in sqlite files
On Wed, Nov 18, 2009 at 08:31:24PM -, Matt scratched on the wall: > Hello, > > Given a sqlite database file I would like to create two lists: one of > bytes which are in use and one of bytes which are "free". > What would be the most efficient way to do this? You have to define "free." Blocks of bytes are managed in pages. There are free pages, and then there is free space within a page. Free pages can be re-used for nearly anything. Free space within a page is a lot harder to recover, and can only be used for specific things, depending on how the page is allocated. "Free" space within pages is just a fact of life, not unlike block allocations in a filesystem. PRAGMA page_count will return the total number of pages in a database file. PRAGMA freelist_count will return the number of free pages. PRAGMA page_size will return the number of bytes per page. You can expect a VACUUM to shrink a database file at least freelist_count * page_size bytes. Maybe a bit more if it can recover some of the intra-page free space. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string." --from Anathem by Neal Stephenson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] listing free bytes in sqlite files
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Matt wrote: > Given a sqlite database file I would like to create two lists: one of bytes > which are in use and one of bytes which are "free". > What would be the most efficient way to do this? What are you actually trying to achieve? If you are trying to work out how much slack space there is in the file then just turn on auto-vacuum which will remove slack space as appropriate. This inefficient method will give you an answer: - Make a backup using the backup API - Run vacuum on the backup - Compare that file size to current file size You should also note that SQLite works on pages not bytes. If you are trying to do steganography then you really do have to understand the file format. > I have taken a look over the file format documentation but can't see a clear > way to achieve this. The information is there. Look at the list of page types. Section 2.4 describes the free page list including a single field in the header saying how many free page list members there are. If you are also trying to find partially used pages then you'll have to iterate over each page of each type (section 2.2.2). Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAksEX30ACgkQmOOfHg372QQs/gCfWF7CG+UBXvxKagln+2xeB6d5 CbAAnjyIH1YROJNFqH2Qq7fGdOYv+rkE =25Ux -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] listing free bytes in sqlite files
On 18 Nov 2009, at 8:31pm, Matt wrote: > Given a sqlite database file I would like to create two lists: one of bytes > which are in use and one of bytes which are "free". > What would be the most efficient way to do this? > > I have taken a look over the file format documentation but can't see a clear > way to achieve this. You have to implement a routine that understands the file format and walks through the data structure. Or you could do it any number of silly ways, like change random bytes then see whether the file content has changed or the PRAGMA reports corruption. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
