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#
8if {[llength $argv]!=1} {
9  puts stderr "Usage: $argv0 database-name"
10  exit 1
11}
12set file_to_analyze [lindex $argv 0]
13
14# Open the database
15#
16sqlite db [lindex $argv 0]
17set DB [btree_open [lindex $argv 0]]
18
19# Output the schema for the generated report
20#
21puts \
22{BEGIN;
23CREATE 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#
36set 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#
45set total_size 0
46set total_primary 0
47set total_overflow 0
48set total_unused_primary 0
49set total_unused_ovfl 0
50
51# Analyze every table in the database, one at a time.
52#
53foreach {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#
91puts "-- Total payload size: $total_size"
92puts "-- Total pages used: $total_primary primary and $total_overflow overflow"
93set file_pgcnt [expr {[file size [lindex $argv 0]]/1024}]
94puts -nonewline "-- Total unused bytes on primary pages: $total_unused_primary"
95if {$total_primary>0} {
96  set upp [expr {$total_unused_primary/$total_primary}]
97  puts " (avg $upp bytes/page)"
98} else {
99  puts ""
100}
101puts -nonewline "-- Total unused bytes on overflow pages: $total_unused_ovfl"
102if {$total_overflow>0} {
103  set upp [expr {$total_unused_ovfl/$total_overflow}]
104  puts " (avg $upp bytes/page)"
105} else {
106  puts ""
107}
108set n_free [expr {$file_pgcnt-$total_primary-$total_overflow}]
109if {$n_free>0} {incr n_free -1}
110puts "-- Total pages on freelist: $n_free"
111puts "COMMIT;"
112