On Thu, 7 Jul 2005, Dennis Jenkins wrote: >Hello. > > Several months ago I ported the "free pages" code from sqlite v2 to >v3. I found the original at [1]. My copy is at [2]. I unsuccessfully >tried to contact the original author. > > This code simply computes the amount of "slack space" in the >database. Our database will sometimes grow quite large and then most of >the rows will be deleted. We want to reclaim the space, but due to the >way our application works, it is not efficient to vacuum the database >right after we delete the rows in question. So, we periodically call >the "free space" routine and vacuum our database if the amount of slack >space exceeds a certain threshold. Good, bad or ugly, that is what we do :)
Why bother? That the file got that big in the first place indicates space is not a problem. KISS, disk space is cheap. > > I would like to see this code ultimately merged into the official >sqlite database distribution (so that we are not distributing a "hacked" >version). However, I am not an sqlite genius. I would very much >appreciate some peer-review. (For example, I removed all of the >database integrity checks b/c the v2 code failed on a v3 system. I also >had to copy many struct definitions from other files as these structures >were not exposed via header files. These hacks seem ugly to me but I'm >not sure what to do about it). Rather than bypassing the btree and pager code, such functionality could be added to the Btree API, and PRAGMAs created to retrieve information. Something like: PRAGMA get_page_count; PRAGMA get_free_page_count; Problem here is that there is no way of specifying which attached database to get the counts for. The other alternative is to add the btree functions and write a custom shell that links against btree.o etc., much like the original freepages.c. > > I grant my code to the community. I can be licensed under the >current sqlite license. Your comments are appreciated. Thank you. > > >[1] http://web.utk.edu/~jplyon/sqlite/code/freepages.c > >[2] http://unwg.no-ip.com/freepages.c > -- /"\ \ / ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
