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

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;"