Hello,
This is my first post to this mailing list. While reading through the Sqlite Wiki I came across an interesting function at 'http://web.utk.edu/~jplyon/sqlite/code/freepages.c' for counting the number of free pages in a database by James P. Lyon. I wanted to use this code in sqlite3, but the database structures have changes. So I hacked on it for a few hours and I think that I've managed to convert it into a function that is useful to me. I did not readily find an email address for James, so I'll post a link to my code here.
http://unwg.no-ip.com/freepages.c
In my application program I check the number of total pages and the number of free pages when the program starts up. I call "vacuum" if the number of free pages is more than 25% of the database size. (Items get inserted slowly and deleted in large batches (20 to 80% of the database at once); I use sqlite as a data cache).
Unfortunately, I had to copy the "struct" definitions from "btree.c" and "pager.c" into the file. I feel that this is not a viable long term solution. Also, don't freak when you see the line "*lFree = get4byte((char*)page1 + 36);". Compare my hack with the previous author's. :)
I was totally clueless about fixing the section of code in "lockBtree" that verifies the database file is valid. My attempts to load "page1" did not work as expected. So I just commented it out. :)
I ask two things:
1) Did I do it correctly? The function gives back meaningful results to me. That is, I can take a database and vacuum it. There are zero free pages. I can then delete some rows and check the free page count; it is now positive. If I vacuum the database again, the free page count goes back to zero. However, I don't know if I'm actually getting the correct data element. "page1->adata" is NULL and "page1->nFree" is always "257", so I made an educated guess based on the last author's code.
2) Can this code be formally placed into sqlite so that I (and others) don't have to hack it in if we want to use it in future updates? Like the original author, I disclaim all copyrights.
-- Dennis Jenkins
