Return to space_used.tcl CVS log

Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / tool

Annotation of embedaddon/sqlite3/tool/space_used.tcl, revision 1.1

1.1     ! misho       1: # Run this TCL script using "testfixture" in order get a report that shows
        !             2: # how much disk space is used by a particular data to actually store data
        !             3: # versus how much space is unused.
        !             4: #
        !             5: 
        !             6: # Get the name of the database to analyze
        !             7: #
        !             8: if {[llength $argv]!=1} {
        !             9:   puts stderr "Usage: $argv0 database-name"
        !            10:   exit 1
        !            11: }
        !            12: set file_to_analyze [lindex $argv 0]
        !            13: 
        !            14: # Open the database
        !            15: #
        !            16: sqlite db [lindex $argv 0]
        !            17: set DB [btree_open [lindex $argv 0]]
        !            18: 
        !            19: # Output the schema for the generated report
        !            20: #
        !            21: puts \
        !            22: {BEGIN;
        !            23: CREATE TABLE space_used(
        !            24:    name clob,        -- Name of a table or index in the database file
        !            25:    is_index boolean, -- TRUE if it is an index, false for a table
        !            26:    payload int,      -- Total amount of data stored in this table or index
        !            27:    pri_pages int,    -- Number of primary pages used
        !            28:    ovfl_pages int,   -- Number of overflow pages used
        !            29:    pri_unused int,   -- Number of unused bytes on primary pages
        !            30:    ovfl_unused int   -- Number of unused bytes on overflow pages
        !            31: );}
        !            32: 
        !            33: # This query will be used to find the root page number for every index and
        !            34: # table in the database.
        !            35: #
        !            36: set sql {
        !            37:   SELECT name, type, rootpage FROM sqlite_master
        !            38:   UNION ALL
        !            39:   SELECT 'sqlite_master', 'table', 2
        !            40:   ORDER BY 1
        !            41: }
        !            42: 
        !            43: # Initialize variables used for summary statistics.
        !            44: #
        !            45: set total_size 0
        !            46: set total_primary 0
        !            47: set total_overflow 0
        !            48: set total_unused_primary 0
        !            49: set total_unused_ovfl 0
        !            50: 
        !            51: # Analyze every table in the database, one at a time.
        !            52: #
        !            53: foreach {name type rootpage} [db eval $sql] {
        !            54:   set cursor [btree_cursor $DB $rootpage 0]
        !            55:   set go [btree_first $cursor]
        !            56:   set size 0
        !            57:   catch {unset pg_used}
        !            58:   set unused_ovfl 0
        !            59:   set n_overflow 0
        !            60:   while {$go==0} {
        !            61:     set payload [btree_payload_size $cursor]
        !            62:     incr size $payload
        !            63:     set stat [btree_cursor_dump $cursor]
        !            64:     set pgno [lindex $stat 0]
        !            65:     set freebytes [lindex $stat 4]
        !            66:     set pg_used($pgno) $freebytes
        !            67:     if {$payload>238} {
        !            68:       set n [expr {($payload-238+1019)/1020}]
        !            69:       incr n_overflow $n
        !            70:       incr unused_ovfl [expr {$n*1020+238-$payload}]
        !            71:     }
        !            72:     set go [btree_next $cursor]
        !            73:   }
        !            74:   btree_close_cursor $cursor
        !            75:   set n_primary [llength [array names pg_used]]
        !            76:   set unused_primary 0
        !            77:   foreach x [array names pg_used] {incr unused_primary $pg_used($x)}
        !            78:   regsub -all ' $name '' name
        !            79:   puts -nonewline "INSERT INTO space_used VALUES('$name'"
        !            80:   puts -nonewline ",[expr {$type=="index"}]"
        !            81:   puts ",$size,$n_primary,$n_overflow,$unused_primary,$unused_ovfl);"
        !            82:   incr total_size $size
        !            83:   incr total_primary $n_primary
        !            84:   incr total_overflow $n_overflow
        !            85:   incr total_unused_primary $unused_primary
        !            86:   incr total_unused_ovfl $unused_ovfl
        !            87: }
        !            88: 
        !            89: # Output summary statistics:
        !            90: #
        !            91: puts "-- Total payload size: $total_size"
        !            92: puts "-- Total pages used: $total_primary primary and $total_overflow overflow"
        !            93: set file_pgcnt [expr {[file size [lindex $argv 0]]/1024}]
        !            94: puts -nonewline "-- Total unused bytes on primary pages: $total_unused_primary"
        !            95: if {$total_primary>0} {
        !            96:   set upp [expr {$total_unused_primary/$total_primary}]
        !            97:   puts " (avg $upp bytes/page)"
        !            98: } else {
        !            99:   puts ""
        !           100: }
        !           101: puts -nonewline "-- Total unused bytes on overflow pages: $total_unused_ovfl"
        !           102: if {$total_overflow>0} {
        !           103:   set upp [expr {$total_unused_ovfl/$total_overflow}]
        !           104:   puts " (avg $upp bytes/page)"
        !           105: } else {
        !           106:   puts ""
        !           107: }
        !           108: set n_free [expr {$file_pgcnt-$total_primary-$total_overflow}]
        !           109: if {$n_free>0} {incr n_free -1}
        !           110: puts "-- Total pages on freelist: $n_free"
        !           111: puts "COMMIT;"