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