Re: Wasted Space Within Files

--- In sqlite-F5Bj5G+ccuY@xxxxxxxxxxxxxxxx, "Brady Hutmacher (AKA Capt. 
Hack'em)"
<chackem-KGpdMs7P1Kc@xxxxxxxxxxxxxxxx> wrote:
> 
> Does anyone know of a simple way to determine how much "wasted
space" is in 
> an sqlite file. As I'm sure we all know, when you delete records,
drop a 
> table, etc. the data is still there. I'm writing a program in which
this 
> will happen fairly regularly, and I was wondering if there was a
relatively 
> easy way to determine how much of a file is "wasted space" with all
this 
> deleted data. Is it just as simple as adding up the existing data and 
> deleting that amount from the actual file size, or is there a better
method?

A quick and dirty method is to compact the database by dumping it.
Information on how to do this is in the docs. This is a one-liner.

Note that there is a distinction between wasted "space" and wasted
pages. All databases contain wasted "space" due to btree overhead in
the database file, and unused space on normal and overflow pages, so
just adding up the amount of raw data in the database won't work.

The following function computes the number of free pages in the
database. It uses several private defines and functions, so you either
need to add it at the bottom of "btree.c", or explicitly copy in the
needed symbol, function, and structure definitions/declarations.
You'll also need to copy in the static functions lockBtree() and
unlockBtree().

/*
** Find the number of free pages in the database.
** This requires a database created with the same SQLITE_PAGE_SIZE.
** Returns -1 on error.
*/
int sqlite_count_free_pages(sqlite* db)
{
        Btree *pBtree;
        PageOne *page1;
        int nFree;
        int rc;

        assert(db);
        if (!db) return -1;

        pBtree = db->pBe;
        assert(pBtree);
        if (!pBtree) return -1;

        /*
        ** We must acquire a lock to fill in the page1 info.
        */
        rc = lockBtree(pBtree);
        if (rc != SQLITE_OK)
                return -1;

        /*
        ** The PageOne struct contains the free page list info.
        */
        page1 = pBtree->page1;
        assert(page1);
        if (!page1) return -1;

        nFree = SWAB32(pBtree, page1->nFree);

        /*
        ** Release the lock on the btree.
        */
        unlockBtreeIfUnused(pBtree);

        return nFree;
}



To unsubscribe from this group, send an email to:
sqlite-unsubscribe-VgYJa0VH1e9BDgjK7y7TUQ@xxxxxxxxxxxxxxxx

 

Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/