tool/spaceanal.tcl

   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 if {[catch {
   7
   8 # Argument $tname is the name of a table within the database opened by
   9 # database handle [db]. Return true if it is a WITHOUT ROWID table, or
  10 # false otherwise.
  11 #
  12 proc is_without_rowid {tname} {
  13   set t [string map {' ''} $tname]
  14   db eval "PRAGMA index_list = '$t'" o {
  15     if {$o(origin) == "pk"} {
  16       set n $o(name)
  17       if {0==[db one { SELECT count(*) FROM sqlite_master WHERE name=$n }]} {
  18         return 1
  19       }
  20     }
  21   }
  22   return 0
  23 }
  24
  25 # Read and run TCL commands from standard input.  Used to implement
  26 # the --tclsh option.
  27 #
  28 proc tclsh {} {
  29   set line {}
  30   while {![eof stdin]} {
  31     if {$line!=""} {
  32       puts -nonewline "> "
  33     } else {
  34       puts -nonewline "% "
  35     }
  36     flush stdout
  37     append line [gets stdin]
  38     if {[info complete $line]} {
  39       if {[catch {uplevel #0 $line} result]} {
  40         puts stderr "Error: $result"
  41       } elseif {$result!=""} {
  42         puts $result
  43       }
  44       set line {}
  45     } else {
  46       append line \n
  47     }
  48   }
  49 }
  50
  51
  52 # Get the name of the database to analyze
  53 #
  54 proc usage {} {
  55   set argv0 [file rootname [file tail [info nameofexecutable]]]
  56   puts stderr "Usage: $argv0 ?--pageinfo? ?--stats? database-filename"
  57   puts stderr {
  58 Analyze the SQLite3 database file specified by the "database-filename"
  59 argument and output a report detailing size and storage efficiency
  60 information for the database and its constituent tables and indexes.
  61
  62 Options:
  63
  64    --pageinfo   Show how each page of the database-file is used
  65
  66    --stats      Output SQL text that creates a new database containing
  67                 statistics about the database that was analyzed
  68
  69    --tclsh      Run the built-in TCL interpreter interactively (for debugging)
  70
  71    --version    Show the version number of SQLite
  72 }
  73   exit 1
  74 }
  75 set file_to_analyze {}
  76 set flags(-pageinfo) 0
  77 set flags(-stats) 0
  78 set flags(-debug) 0
  79 append argv {}
  80 foreach arg $argv {
  81   if {[regexp {^-+pageinfo$} $arg]} {
  82     set flags(-pageinfo) 1
  83   } elseif {[regexp {^-+stats$} $arg]} {
  84     set flags(-stats) 1
  85   } elseif {[regexp {^-+debug$} $arg]} {
  86     set flags(-debug) 1
  87   } elseif {[regexp {^-+tclsh$} $arg]} {
  88     tclsh
  89     exit 0
  90   } elseif {[regexp {^-+version$} $arg]} {
  91     sqlite3 mem :memory:
  92     puts [mem one {SELECT sqlite_version()||' '||sqlite_source_id()}]
  93     mem close
  94     exit 0
  95   } elseif {[regexp {^-} $arg]} {
  96     puts stderr "Unknown option: $arg"
  97     usage
  98   } elseif {$file_to_analyze!=""} {
  99     usage
 100   } else {
 101     set file_to_analyze $arg
 102   }
 103 }
 104 if {$file_to_analyze==""} usage
 105 set root_filename $file_to_analyze
 106 regexp {^file:(//)?([^?]*)} $file_to_analyze all x1 root_filename
 107 if {![file exists $root_filename]} {
 108   puts stderr "No such file: $root_filename"
 109   exit 1
 110 }
 111 if {![file readable $root_filename]} {
 112   puts stderr "File is not readable: $root_filename"
 113   exit 1
 114 }
 115 set true_file_size [file size $root_filename]
 116 if {$true_file_size<512} {
 117   puts stderr "Empty or malformed database: $root_filename"
 118   exit 1
 119 }
 120
 121 # Compute the total file size assuming test_multiplexor is being used.
 122 # Assume that SQLITE_ENABLE_8_3_NAMES might be enabled
 123 #
 124 set extension [file extension $root_filename]
 125 set pattern $root_filename
 126 append pattern {[0-3][0-9][0-9]}
 127 foreach f [glob -nocomplain $pattern] {
 128   incr true_file_size [file size $f]
 129   set extension {}
 130 }
 131 if {[string length $extension]>=2 && [string length $extension]<=4} {
 132   set pattern [file rootname $root_filename]
 133   append pattern {.[0-3][0-9][0-9]}
 134   foreach f [glob -nocomplain $pattern] {
 135     incr true_file_size [file size $f]
 136   }
 137 }
 138
 139 # Open the database
 140 #
 141 if {[catch {sqlite3 db $file_to_analyze -uri 1} msg]} {
 142   puts stderr "error trying to open $file_to_analyze: $msg"
 143   exit 1
 144 }
 145 if {$flags(-debug)} {
 146   proc dbtrace {txt} {puts $txt; flush stdout;}
 147   db trace ::dbtrace
 148 }
 149
 150 db eval {SELECT count(*) FROM sqlite_master}
 151 set pageSize [expr {wide([db one {PRAGMA page_size}])}]
 152
 153 if {$flags(-pageinfo)} {
 154   db eval {CREATE VIRTUAL TABLE temp.stat USING dbstat}
 155   db eval {SELECT name, path, pageno FROM temp.stat ORDER BY pageno} {
 156     puts "$pageno $name $path"
 157   }
 158   exit 0
 159 }
 160 if {$flags(-stats)} {
 161   db eval {CREATE VIRTUAL TABLE temp.stat USING dbstat}
 162   puts "BEGIN;"
 163   puts "CREATE TABLE stats("
 164   puts "  name       STRING,           /* Name of table or index */"
 165   puts "  path       INTEGER,          /* Path to page from root */"
 166   puts "  pageno     INTEGER,          /* Page number */"
 167   puts "  pagetype   STRING,           /* 'internal', 'leaf' or 'overflow' */"
 168   puts "  ncell      INTEGER,          /* Cells on page (0 for overflow) */"
 169   puts "  payload    INTEGER,          /* Bytes of payload on this page */"
 170   puts "  unused     INTEGER,          /* Bytes of unused space on this page */"
 171   puts "  mx_payload INTEGER,          /* Largest payload size of all cells */"
 172   puts "  pgoffset   INTEGER,          /* Offset of page in file */"
 173   puts "  pgsize     INTEGER           /* Size of the page */"
 174   puts ");"
 175   db eval {SELECT quote(name) || ',' ||
 176                   quote(path) || ',' ||
 177                   quote(pageno) || ',' ||
 178                   quote(pagetype) || ',' ||
 179                   quote(ncell) || ',' ||
 180                   quote(payload) || ',' ||
 181                   quote(unused) || ',' ||
 182                   quote(mx_payload) || ',' ||
 183                   quote(pgoffset) || ',' ||
 184                   quote(pgsize) AS x FROM stat} {
 185     puts "INSERT INTO stats VALUES($x);"
 186   }
 187   puts "COMMIT;"
 188   exit 0
 189 }
 190
 191
 192 # In-memory database for collecting statistics. This script loops through
 193 # the tables and indices in the database being analyzed, adding a row for each
 194 # to an in-memory database (for which the schema is shown below). It then
 195 # queries the in-memory db to produce the space-analysis report.
 196 #
 197 sqlite3 mem :memory:
 198 if {$flags(-debug)} {
 199   proc dbtrace {txt} {puts $txt; flush stdout;}
 200   mem trace ::dbtrace
 201 }
 202 set tabledef {CREATE TABLE space_used(
 203    name clob,        -- Name of a table or index in the database file
 204    tblname clob,     -- Name of associated table
 205    is_index boolean, -- TRUE if it is an index, false for a table
 206    is_without_rowid boolean, -- TRUE if WITHOUT ROWID table
 207    nentry int,       -- Number of entries in the BTree
 208    leaf_entries int, -- Number of leaf entries
 209    depth int,        -- Depth of the b-tree
 210    payload int,      -- Total amount of data stored in this table or index
 211    ovfl_payload int, -- Total amount of data stored on overflow pages
 212    ovfl_cnt int,     -- Number of entries that use overflow
 213    mx_payload int,   -- Maximum payload size
 214    int_pages int,    -- Number of interior pages used
 215    leaf_pages int,   -- Number of leaf pages used
 216    ovfl_pages int,   -- Number of overflow pages used
 217    int_unused int,   -- Number of unused bytes on interior pages
 218    leaf_unused int,  -- Number of unused bytes on primary pages
 219    ovfl_unused int,  -- Number of unused bytes on overflow pages
 220    gap_cnt int,      -- Number of gaps in the page layout
 221    compressed_size int  -- Total bytes stored on disk
 222 );}
 223 mem eval $tabledef
 224
 225 # Create a temporary "dbstat" virtual table.
 226 #
 227 db eval {CREATE VIRTUAL TABLE temp.stat USING dbstat}
 228 db eval {CREATE TEMP TABLE dbstat AS SELECT * FROM temp.stat
 229          ORDER BY name, path}
 230 db eval {DROP TABLE temp.stat}
 231
 232 set isCompressed 0
 233 set compressOverhead 0
 234 set depth 0
 235 set sql { SELECT name, tbl_name FROM sqlite_master WHERE rootpage>0 }
 236 foreach {name tblname} [concat sqlite_master sqlite_master [db eval $sql]] {
 237
 238   set is_index [expr {$name!=$tblname}]
 239   set is_without_rowid [is_without_rowid $name]
 240   db eval {
 241     SELECT
 242       sum(ncell) AS nentry,
 243       sum((pagetype=='leaf')*ncell) AS leaf_entries,
 244       sum(payload) AS payload,
 245       sum((pagetype=='overflow') * payload) AS ovfl_payload,
 246       sum(path LIKE '%+000000') AS ovfl_cnt,
 247       max(mx_payload) AS mx_payload,
 248       sum(pagetype=='internal') AS int_pages,
 249       sum(pagetype=='leaf') AS leaf_pages,
 250       sum(pagetype=='overflow') AS ovfl_pages,
 251       sum((pagetype=='internal') * unused) AS int_unused,
 252       sum((pagetype=='leaf') * unused) AS leaf_unused,
 253       sum((pagetype=='overflow') * unused) AS ovfl_unused,
 254       sum(pgsize) AS compressed_size,
 255       max((length(CASE WHEN path LIKE '%+%' THEN '' ELSE path END)+3)/4)
 256         AS depth
 257     FROM temp.dbstat WHERE name = $name
 258   } break
 259
 260   set total_pages [expr {$leaf_pages+$int_pages+$ovfl_pages}]
 261   set storage [expr {$total_pages*$pageSize}]
 262   if {!$isCompressed && $storage>$compressed_size} {
 263     set isCompressed 1
 264     set compressOverhead 14
 265   }
 266
 267   # Column 'gap_cnt' is set to the number of non-contiguous entries in the
 268   # list of pages visited if the b-tree structure is traversed in a top-down
 269   # fashion (each node visited before its child-tree is passed). Any overflow
 270   # chains present are traversed from start to finish before any child-tree
 271   # is.
 272   #
 273   set gap_cnt 0
 274   set prev 0
 275   db eval {
 276     SELECT pageno, pagetype FROM temp.dbstat
 277      WHERE name=$name
 278      ORDER BY pageno
 279   } {
 280     if {$prev>0 && $pagetype=="leaf" && $pageno!=$prev+1} {
 281       incr gap_cnt
 282     }
 283     set prev $pageno
 284   }
 285   mem eval {
 286     INSERT INTO space_used VALUES(
 287       $name,
 288       $tblname,
 289       $is_index,
 290       $is_without_rowid,
 291       $nentry,
 292       $leaf_entries,
 293       $depth,
 294       $payload,
 295       $ovfl_payload,
 296       $ovfl_cnt,
 297       $mx_payload,
 298       $int_pages,
 299       $leaf_pages,
 300       $ovfl_pages,
 301       $int_unused,
 302       $leaf_unused,
 303       $ovfl_unused,
 304       $gap_cnt,
 305       $compressed_size
 306     );
 307   }
 308 }
 309
 310 proc integerify {real} {
 311   if {[string is double -strict $real]} {
 312     return [expr {wide($real)}]
 313   } else {
 314     return 0
 315   }
 316 }
 317 mem function int integerify
 318
 319 # Quote a string for use in an SQL query. Examples:
 320 #
 321 # [quote {hello world}]   == {'hello world'}
 322 # [quote {hello world's}] == {'hello world''s'}
 323 #
 324 proc quote {txt} {
 325   return [string map {' ''} $txt]
 326 }
 327
 328 # Output a title line
 329 #
 330 proc titleline {title} {
 331   if {$title==""} {
 332     puts [string repeat * 79]
 333   } else {
 334     set len [string length $title]
 335     set stars [string repeat * [expr 79-$len-5]]
 336     puts "*** $title $stars"
 337   }
 338 }
 339
 340 # Generate a single line of output in the statistics section of the
 341 # report.
 342 #
 343 proc statline {title value {extra {}}} {
 344   set len [string length $title]
 345   set dots [string repeat . [expr 50-$len]]
 346   set len [string length $value]
 347   set sp2 [string range {          } $len end]
 348   if {$extra ne ""} {
 349     set extra " $extra"
 350   }
 351   puts "$title$dots $value$sp2$extra"
 352 }
 353
 354 # Generate a formatted percentage value for $num/$denom
 355 #
 356 proc percent {num denom {of {}}} {
 357   if {$denom==0.0} {return ""}
 358   set v [expr {$num*100.0/$denom}]
 359   set of {}
 360   if {$v==100.0 || $v<0.001 || ($v>1.0 && $v<99.0)} {
 361     return [format {%5.1f%% %s} $v $of]
 362   } elseif {$v<0.1 || $v>99.9} {
 363     return [format {%7.3f%% %s} $v $of]
 364   } else {
 365     return [format {%6.2f%% %s} $v $of]
 366   }
 367 }
 368
 369 proc divide {num denom} {
 370   if {$denom==0} {return 0.0}
 371   return [format %.2f [expr double($num)/double($denom)]]
 372 }
 373
 374 # Generate a subreport that covers some subset of the database.
 375 # the $where clause determines which subset to analyze.
 376 #
 377 proc subreport {title where showFrag} {
 378   global pageSize file_pgcnt compressOverhead
 379
 380   # Query the in-memory database for the sum of various statistics
 381   # for the subset of tables/indices identified by the WHERE clause in
 382   # $where. Note that even if the WHERE clause matches no rows, the
 383   # following query returns exactly one row (because it is an aggregate).
 384   #
 385   # The results of the query are stored directly by SQLite into local
 386   # variables (i.e. $nentry, $payload etc.).
 387   #
 388   mem eval "
 389     SELECT
 390       int(sum(
 391         CASE WHEN (is_without_rowid OR is_index) THEN nentry
 392              ELSE leaf_entries
 393         END
 394       )) AS nentry,
 395       int(sum(payload)) AS payload,
 396       int(sum(ovfl_payload)) AS ovfl_payload,
 397       max(mx_payload) AS mx_payload,
 398       int(sum(ovfl_cnt)) as ovfl_cnt,
 399       int(sum(leaf_pages)) AS leaf_pages,
 400       int(sum(int_pages)) AS int_pages,
 401       int(sum(ovfl_pages)) AS ovfl_pages,
 402       int(sum(leaf_unused)) AS leaf_unused,
 403       int(sum(int_unused)) AS int_unused,
 404       int(sum(ovfl_unused)) AS ovfl_unused,
 405       int(sum(gap_cnt)) AS gap_cnt,
 406       int(sum(compressed_size)) AS compressed_size,
 407       int(max(depth)) AS depth,
 408       count(*) AS cnt
 409     FROM space_used WHERE $where" {} {}
 410
 411   # Output the sub-report title, nicely decorated with * characters.
 412   #
 413   puts ""
 414   titleline $title
 415   puts ""
 416
 417   # Calculate statistics and store the results in TCL variables, as follows:
 418   #
 419   # total_pages: Database pages consumed.
 420   # total_pages_percent: Pages consumed as a percentage of the file.
 421   # storage: Bytes consumed.
 422   # payload_percent: Payload bytes used as a percentage of $storage.
 423   # total_unused: Unused bytes on pages.
 424   # avg_payload: Average payload per btree entry.
 425   # avg_fanout: Average fanout for internal pages.
 426   # avg_unused: Average unused bytes per btree entry.
 427   # avg_meta: Average metadata overhead per entry.
 428   # ovfl_cnt_percent: Percentage of btree entries that use overflow pages.
 429   #
 430   set total_pages [expr {$leaf_pages+$int_pages+$ovfl_pages}]
 431   set total_pages_percent [percent $total_pages $file_pgcnt]
 432   set storage [expr {$total_pages*$pageSize}]
 433   set payload_percent [percent $payload $storage {of storage consumed}]
 434   set total_unused [expr {$ovfl_unused+$int_unused+$leaf_unused}]
 435   set avg_payload [divide $payload $nentry]
 436   set avg_unused [divide $total_unused $nentry]
 437   set total_meta [expr {$storage - $payload - $total_unused}]
 438   set total_meta [expr {$total_meta + 4*($ovfl_pages - $ovfl_cnt)}]
 439   set meta_percent [percent $total_meta $storage {of metadata}]
 440   set avg_meta [divide $total_meta $nentry]
 441   if {$int_pages>0} {
 442     # TODO: Is this formula correct?
 443     set nTab [mem eval "
 444       SELECT count(*) FROM (
 445           SELECT DISTINCT tblname FROM space_used WHERE $where AND is_index=0
 446       )
 447     "]
 448     set avg_fanout [mem eval "
 449       SELECT (sum(leaf_pages+int_pages)-$nTab)/sum(int_pages) FROM space_used
 450           WHERE $where
 451     "]
 452     set avg_fanout [format %.2f $avg_fanout]
 453   }
 454   set ovfl_cnt_percent [percent $ovfl_cnt $nentry {of all entries}]
 455
 456   # Print out the sub-report statistics.
 457   #
 458   statline {Percentage of total database} $total_pages_percent
 459   statline {Number of entries} $nentry
 460   statline {Bytes of storage consumed} $storage
 461   if {$compressed_size!=$storage} {
 462     set compressed_size [expr {$compressed_size+$compressOverhead*$total_pages}]
 463     set pct [expr {$compressed_size*100.0/$storage}]
 464     set pct [format {%5.1f%%} $pct]
 465     statline {Bytes used after compression} $compressed_size $pct
 466   }
 467   statline {Bytes of payload} $payload $payload_percent
 468   statline {Bytes of metadata} $total_meta $meta_percent
 469   if {$cnt==1} {statline {B-tree depth} $depth}
 470   statline {Average payload per entry} $avg_payload
 471   statline {Average unused bytes per entry} $avg_unused
 472   statline {Average metadata per entry} $avg_meta
 473   if {[info exists avg_fanout]} {
 474     statline {Average fanout} $avg_fanout
 475   }
 476   if {$showFrag && $total_pages>1} {
 477     set fragmentation [percent $gap_cnt [expr {$total_pages-1}]]
 478     statline {Non-sequential pages} $gap_cnt $fragmentation
 479   }
 480   statline {Maximum payload per entry} $mx_payload
 481   statline {Entries that use overflow} $ovfl_cnt $ovfl_cnt_percent
 482   if {$int_pages>0} {
 483     statline {Index pages used} $int_pages
 484   }
 485   statline {Primary pages used} $leaf_pages
 486   statline {Overflow pages used} $ovfl_pages
 487   statline {Total pages used} $total_pages
 488   if {$int_unused>0} {
 489     set int_unused_percent [
 490          percent $int_unused [expr {$int_pages*$pageSize}] {of index space}]
 491     statline "Unused bytes on index pages" $int_unused $int_unused_percent
 492   }
 493   statline "Unused bytes on primary pages" $leaf_unused [
 494      percent $leaf_unused [expr {$leaf_pages*$pageSize}] {of primary space}]
 495   statline "Unused bytes on overflow pages" $ovfl_unused [
 496      percent $ovfl_unused [expr {$ovfl_pages*$pageSize}] {of overflow space}]
 497   statline "Unused bytes on all pages" $total_unused [
 498                percent $total_unused $storage {of all space}]
 499   return 1
 500 }
 501
 502 # Calculate the overhead in pages caused by auto-vacuum.
 503 #
 504 # This procedure calculates and returns the number of pages used by the
 505 # auto-vacuum 'pointer-map'. If the database does not support auto-vacuum,
 506 # then 0 is returned. The two arguments are the size of the database file in
 507 # pages and the page size used by the database (in bytes).
 508 proc autovacuum_overhead {filePages pageSize} {
 509
 510   # Set $autovacuum to non-zero for databases that support auto-vacuum.
 511   set autovacuum [db one {PRAGMA auto_vacuum}]
 512
 513   # If the database is not an auto-vacuum database or the file consists
 514   # of one page only then there is no overhead for auto-vacuum. Return zero.
 515   if {0==$autovacuum || $filePages==1} {
 516     return 0
 517   }
 518
 519   # The number of entries on each pointer map page. The layout of the
 520   # database file is one pointer-map page, followed by $ptrsPerPage other
 521   # pages, followed by a pointer-map page etc. The first pointer-map page
 522   # is the second page of the file overall.
 523   set ptrsPerPage [expr double($pageSize/5)]
 524
 525   # Return the number of pointer map pages in the database.
 526   return [expr wide(ceil( ($filePages-1.0)/($ptrsPerPage+1.0) ))]
 527 }
 528
 529
 530 # Calculate the summary statistics for the database and store the results
 531 # in TCL variables. They are output below. Variables are as follows:
 532 #
 533 # pageSize:      Size of each page in bytes.
 534 # file_bytes:    File size in bytes.
 535 # file_pgcnt:    Number of pages in the file.
 536 # file_pgcnt2:   Number of pages in the file (calculated).
 537 # av_pgcnt:      Pages consumed by the auto-vacuum pointer-map.
 538 # av_percent:    Percentage of the file consumed by auto-vacuum pointer-map.
 539 # inuse_pgcnt:   Data pages in the file.
 540 # inuse_percent: Percentage of pages used to store data.
 541 # free_pgcnt:    Free pages calculated as (<total pages> - <in-use pages>)
 542 # free_pgcnt2:   Free pages in the file according to the file header.
 543 # free_percent:  Percentage of file consumed by free pages (calculated).
 544 # free_percent2: Percentage of file consumed by free pages (header).
 545 # ntable:        Number of tables in the db.
 546 # nindex:        Number of indices in the db.
 547 # nautoindex:    Number of indices created automatically.
 548 # nmanindex:     Number of indices created manually.
 549 # user_payload:  Number of bytes of payload in table btrees
 550 #                (not including sqlite_master)
 551 # user_percent:  $user_payload as a percentage of total file size.
 552
 553 ### The following, setting $file_bytes based on the actual size of the file
 554 ### on disk, causes this tool to choke on zipvfs databases. So set it based
 555 ### on the return of [PRAGMA page_count] instead.
 556 if 0 {
 557   set file_bytes  [file size $file_to_analyze]
 558   set file_pgcnt  [expr {$file_bytes/$pageSize}]
 559 }
 560 set file_pgcnt  [db one {PRAGMA page_count}]
 561 set file_bytes  [expr {$file_pgcnt * $pageSize}]
 562
 563 set av_pgcnt    [autovacuum_overhead $file_pgcnt $pageSize]
 564 set av_percent  [percent $av_pgcnt $file_pgcnt]
 565
 566 set sql {SELECT sum(leaf_pages+int_pages+ovfl_pages) FROM space_used}
 567 set inuse_pgcnt   [expr wide([mem eval $sql])]
 568 set inuse_percent [percent $inuse_pgcnt $file_pgcnt]
 569
 570 set free_pgcnt    [expr {$file_pgcnt-$inuse_pgcnt-$av_pgcnt}]
 571 set free_percent  [percent $free_pgcnt $file_pgcnt]
 572 set free_pgcnt2   [db one {PRAGMA freelist_count}]
 573 set free_percent2 [percent $free_pgcnt2 $file_pgcnt]
 574
 575 set file_pgcnt2 [expr {$inuse_pgcnt+$free_pgcnt2+$av_pgcnt}]
 576
 577 set ntable [db eval {SELECT count(*)+1 FROM sqlite_master WHERE type='table'}]
 578 set nindex [db eval {SELECT count(*) FROM sqlite_master WHERE type='index'}]
 579 set sql {SELECT count(*) FROM sqlite_master WHERE name LIKE 'sqlite_autoindex%'}
 580 set nautoindex [db eval $sql]
 581 set nmanindex [expr {$nindex-$nautoindex}]
 582
 583 # set total_payload [mem eval "SELECT sum(payload) FROM space_used"]
 584 set user_payload [mem one {SELECT int(sum(payload)) FROM space_used
 585      WHERE NOT is_index AND name NOT LIKE 'sqlite_master'}]
 586 set user_percent [percent $user_payload $file_bytes]
 587
 588 # Output the summary statistics calculated above.
 589 #
 590 puts "/** Disk-Space Utilization Report For $root_filename"
 591 puts ""
 592 statline {Page size in bytes} $pageSize
 593 statline {Pages in the whole file (measured)} $file_pgcnt
 594 statline {Pages in the whole file (calculated)} $file_pgcnt2
 595 statline {Pages that store data} $inuse_pgcnt $inuse_percent
 596 statline {Pages on the freelist (per header)} $free_pgcnt2 $free_percent2
 597 statline {Pages on the freelist (calculated)} $free_pgcnt $free_percent
 598 statline {Pages of auto-vacuum overhead} $av_pgcnt $av_percent
 599 statline {Number of tables in the database} $ntable
 600 statline {Number of indices} $nindex
 601 statline {Number of defined indices} $nmanindex
 602 statline {Number of implied indices} $nautoindex
 603 if {$isCompressed} {
 604   statline {Size of uncompressed content in bytes} $file_bytes
 605   set efficiency [percent $true_file_size $file_bytes]
 606   statline {Size of compressed file on disk} $true_file_size $efficiency
 607 } else {
 608   statline {Size of the file in bytes} $file_bytes
 609 }
 610 statline {Bytes of user payload stored} $user_payload $user_percent
 611
 612 # Output table rankings
 613 #
 614 puts ""
 615 titleline "Page counts for all tables with their indices"
 616 puts ""
 617 mem eval {SELECT tblname, count(*) AS cnt,
 618               int(sum(int_pages+leaf_pages+ovfl_pages)) AS size
 619           FROM space_used GROUP BY tblname ORDER BY size+0 DESC, tblname} {} {
 620   statline [string toupper $tblname] $size [percent $size $file_pgcnt]
 621 }
 622 puts ""
 623 titleline "Page counts for all tables and indices separately"
 624 puts ""
 625 mem eval {
 626   SELECT
 627        upper(name) AS nm,
 628        int(int_pages+leaf_pages+ovfl_pages) AS size
 629     FROM space_used
 630    ORDER BY size+0 DESC, name} {} {
 631   statline $nm $size [percent $size $file_pgcnt]
 632 }
 633 if {$isCompressed} {
 634   puts ""
 635   titleline "Bytes of disk space used after compression"
 636   puts ""
 637   set csum 0
 638   mem eval {SELECT tblname,
 639                   int(sum(compressed_size)) +
 640                          $compressOverhead*sum(int_pages+leaf_pages+ovfl_pages)
 641                         AS csize
 642           FROM space_used GROUP BY tblname ORDER BY csize+0 DESC, tblname} {} {
 643     incr csum $csize
 644     statline [string toupper $tblname] $csize [percent $csize $true_file_size]
 645   }
 646   set overhead [expr {$true_file_size - $csum}]
 647   if {$overhead>0} {
 648     statline {Header and free space} $overhead [percent $overhead $true_file_size]
 649   }
 650 }
 651
 652 # Output subreports
 653 #
 654 if {$nindex>0} {
 655   subreport {All tables and indices} 1 0
 656 }
 657 subreport {All tables} {NOT is_index} 0
 658 if {$nindex>0} {
 659   subreport {All indices} {is_index} 0
 660 }
 661 foreach tbl [mem eval {SELECT DISTINCT tblname name FROM space_used
 662                        ORDER BY name}] {
 663   set qn [quote $tbl]
 664   set name [string toupper $tbl]
 665   set n [mem eval {SELECT count(*) FROM space_used WHERE tblname=$tbl}]
 666   if {$n>1} {
 667     set idxlist [mem eval "SELECT name FROM space_used
 668                             WHERE tblname='$qn' AND is_index
 669                             ORDER BY 1"]
 670     subreport "Table $name and all its indices" "tblname='$qn'" 0
 671     subreport "Table $name w/o any indices" "name='$qn'" 1
 672     if {[llength $idxlist]>1} {
 673       subreport "Indices of table $name" "tblname='$qn' AND is_index" 0
 674     }
 675     foreach idx $idxlist {
 676       set qidx [quote $idx]
 677       subreport "Index [string toupper $idx] of table $name" "name='$qidx'" 1
 678     }
 679   } else {
 680     subreport "Table $name" "name='$qn'" 1
 681   }
 682 }
 683
 684 # Output instructions on what the numbers above mean.
 685 #
 686 puts ""
 687 titleline Definitions
 688 puts {
 689 Page size in bytes
 690
 691     The number of bytes in a single page of the database file.
 692     Usually 1024.
 693
 694 Number of pages in the whole file
 695 }
 696 puts "    The number of $pageSize-byte pages that go into forming the complete
 697     database"
 698 puts {
 699 Pages that store data
 700
 701     The number of pages that store data, either as primary B*Tree pages or
 702     as overflow pages.  The number at the right is the data pages divided by
 703     the total number of pages in the file.
 704
 705 Pages on the freelist
 706
 707     The number of pages that are not currently in use but are reserved for
 708     future use.  The percentage at the right is the number of freelist pages
 709     divided by the total number of pages in the file.
 710
 711 Pages of auto-vacuum overhead
 712
 713     The number of pages that store data used by the database to facilitate
 714     auto-vacuum. This is zero for databases that do not support auto-vacuum.
 715
 716 Number of tables in the database
 717
 718     The number of tables in the database, including the SQLITE_MASTER table
 719     used to store schema information.
 720
 721 Number of indices
 722
 723     The total number of indices in the database.
 724
 725 Number of defined indices
 726
 727     The number of indices created using an explicit CREATE INDEX statement.
 728
 729 Number of implied indices
 730
 731     The number of indices used to implement PRIMARY KEY or UNIQUE constraints
 732     on tables.
 733
 734 Size of the file in bytes
 735
 736     The total amount of disk space used by the entire database files.
 737
 738 Bytes of user payload stored
 739
 740     The total number of bytes of user payload stored in the database. The
 741     schema information in the SQLITE_MASTER table is not counted when
 742     computing this number.  The percentage at the right shows the payload
 743     divided by the total file size.
 744
 745 Percentage of total database
 746
 747     The amount of the complete database file that is devoted to storing
 748     information described by this category.
 749
 750 Number of entries
 751
 752     The total number of B-Tree key/value pairs stored under this category.
 753
 754 Bytes of storage consumed
 755
 756     The total amount of disk space required to store all B-Tree entries
 757     under this category.  The is the total number of pages used times
 758     the pages size.
 759
 760 Bytes of payload
 761
 762     The amount of payload stored under this category.  Payload is the data
 763     part of table entries and the key part of index entries.  The percentage
 764     at the right is the bytes of payload divided by the bytes of storage
 765     consumed.
 766
 767 Bytes of metadata
 768
 769     The amount of formatting and structural information stored in the
 770     table or index.  Metadata includes the btree page header, the cell pointer
 771     array, the size field for each cell, the left child pointer or non-leaf
 772     cells, the overflow pointers for overflow cells, and the rowid value for
 773     rowid table cells.  In other words, metadata is everything that is neither
 774     unused space nor content.  The record header in the payload is counted as
 775     content, not metadata.
 776
 777 Average payload per entry
 778
 779     The average amount of payload on each entry.  This is just the bytes of
 780     payload divided by the number of entries.
 781
 782 Average unused bytes per entry
 783
 784     The average amount of free space remaining on all pages under this
 785     category on a per-entry basis.  This is the number of unused bytes on
 786     all pages divided by the number of entries.
 787
 788 Non-sequential pages
 789
 790     The number of pages in the table or index that are out of sequence.
 791     Many filesystems are optimized for sequential file access so a small
 792     number of non-sequential pages might result in faster queries,
 793     especially for larger database files that do not fit in the disk cache.
 794     Note that after running VACUUM, the root page of each table or index is
 795     at the beginning of the database file and all other pages are in a
 796     separate part of the database file, resulting in a single non-
 797     sequential page.
 798
 799 Maximum payload per entry
 800
 801     The largest payload size of any entry.
 802
 803 Entries that use overflow
 804
 805     The number of entries that user one or more overflow pages.
 806
 807 Total pages used
 808
 809     This is the number of pages used to hold all information in the current
 810     category.  This is the sum of index, primary, and overflow pages.
 811
 812 Index pages used
 813
 814     This is the number of pages in a table B-tree that hold only key (rowid)
 815     information and no data.
 816
 817 Primary pages used
 818
 819     This is the number of B-tree pages that hold both key and data.
 820
 821 Overflow pages used
 822
 823     The total number of overflow pages used for this category.
 824
 825 Unused bytes on index pages
 826
 827     The total number of bytes of unused space on all index pages.  The
 828     percentage at the right is the number of unused bytes divided by the
 829     total number of bytes on index pages.
 830
 831 Unused bytes on primary pages
 832
 833     The total number of bytes of unused space on all primary pages.  The
 834     percentage at the right is the number of unused bytes divided by the
 835     total number of bytes on primary pages.
 836
 837 Unused bytes on overflow pages
 838
 839     The total number of bytes of unused space on all overflow pages.  The
 840     percentage at the right is the number of unused bytes divided by the
 841     total number of bytes on overflow pages.
 842
 843 Unused bytes on all pages
 844
 845     The total number of bytes of unused space on all primary and overflow
 846     pages.  The percentage at the right is the number of unused bytes
 847     divided by the total number of bytes.
 848 }
 849
 850 # Output a dump of the in-memory database. This can be used for more
 851 # complex offline analysis.
 852 #
 853 titleline {}
 854 puts "The entire text of this report can be sourced into any SQL database"
 855 puts "engine for further analysis.  All of the text above is an SQL comment."
 856 puts "The data used to generate this report follows:"
 857 puts "*/"
 858 puts "BEGIN;"
 859 puts $tabledef
 860 unset -nocomplain x
 861 mem eval {SELECT * FROM space_used} x {
 862   puts -nonewline "INSERT INTO space_used VALUES"
 863   set sep (
 864   foreach col $x(*) {
 865     set v $x($col)
 866     if {$v=="" || ![string is double $v]} {set v '[quote $v]'}
 867     puts -nonewline $sep$v
 868     set sep ,
 869   }
 870   puts ");"
 871 }
 872 puts "COMMIT;"
 873
 874 } err]} {
 875   puts "ERROR: $err"
 876   puts $errorInfo
 877   exit 1
 878 }