summaryrefslogtreecommitdiff
path: root/ext/pdo_sqlite/sqlite/tool/spaceanal.tcl
diff options
context:
space:
mode:
authorSVN Migration <svn@php.net>2006-10-15 21:09:28 +0000
committerSVN Migration <svn@php.net>2006-10-15 21:09:28 +0000
commit88ec761548b66f58acc1a86cdd0fc164ca925476 (patch)
treed0af978fa00d83bb1d82c613f66477fbd6bb18aa /ext/pdo_sqlite/sqlite/tool/spaceanal.tcl
parent268984b4787e797db6054313fc9ba3b9e845306e (diff)
downloadphp-git-PECL_OPENSSL.tar.gz
This commit was manufactured by cvs2svn to create branch 'PECL_OPENSSL'.PECL_OPENSSL
Diffstat (limited to 'ext/pdo_sqlite/sqlite/tool/spaceanal.tcl')
-rw-r--r--ext/pdo_sqlite/sqlite/tool/spaceanal.tcl810
1 files changed, 0 insertions, 810 deletions
diff --git a/ext/pdo_sqlite/sqlite/tool/spaceanal.tcl b/ext/pdo_sqlite/sqlite/tool/spaceanal.tcl
deleted file mode 100644
index c0fe5b5d87..0000000000
--- a/ext/pdo_sqlite/sqlite/tool/spaceanal.tcl
+++ /dev/null
@@ -1,810 +0,0 @@
-# Run this TCL script using "testfixture" in order get a report that shows
-# how much disk space is used by a particular data to actually store data
-# versus how much space is unused.
-#
-
-if {[catch {
-
-# Get the name of the database to analyze
-#
-#set argv $argv0
-if {[llength $argv]!=1} {
- puts stderr "Usage: $argv0 database-name"
- exit 1
-}
-set file_to_analyze [lindex $argv 0]
-if {![file exists $file_to_analyze]} {
- puts stderr "No such file: $file_to_analyze"
- exit 1
-}
-if {![file readable $file_to_analyze]} {
- puts stderr "File is not readable: $file_to_analyze"
- exit 1
-}
-if {[file size $file_to_analyze]<512} {
- puts stderr "Empty or malformed database: $file_to_analyze"
- exit 1
-}
-
-# Open the database
-#
-sqlite3 db [lindex $argv 0]
-set DB [btree_open [lindex $argv 0] 1000 0]
-
-# In-memory database for collecting statistics. This script loops through
-# the tables and indices in the database being analyzed, adding a row for each
-# to an in-memory database (for which the schema is shown below). It then
-# queries the in-memory db to produce the space-analysis report.
-#
-sqlite3 mem :memory:
-set tabledef\
-{CREATE TABLE space_used(
- name clob, -- Name of a table or index in the database file
- tblname clob, -- Name of associated table
- is_index boolean, -- TRUE if it is an index, false for a table
- nentry int, -- Number of entries in the BTree
- leaf_entries int, -- Number of leaf entries
- payload int, -- Total amount of data stored in this table or index
- ovfl_payload int, -- Total amount of data stored on overflow pages
- ovfl_cnt int, -- Number of entries that use overflow
- mx_payload int, -- Maximum payload size
- int_pages int, -- Number of interior pages used
- leaf_pages int, -- Number of leaf pages used
- ovfl_pages int, -- Number of overflow pages used
- int_unused int, -- Number of unused bytes on interior pages
- leaf_unused int, -- Number of unused bytes on primary pages
- ovfl_unused int -- Number of unused bytes on overflow pages
-);}
-mem eval $tabledef
-
-proc integerify {real} {
- return [expr int($real)]
-}
-mem function int integerify
-
-# Quote a string for use in an SQL query. Examples:
-#
-# [quote {hello world}] == {'hello world'}
-# [quote {hello world's}] == {'hello world''s'}
-#
-proc quote {txt} {
- regsub -all ' $txt '' q
- return '$q'
-}
-
-# This proc is a wrapper around the btree_cursor_info command. The
-# second argument is an open btree cursor returned by [btree_cursor].
-# The first argument is the name of an array variable that exists in
-# the scope of the caller. If the third argument is non-zero, then
-# info is returned for the page that lies $up entries upwards in the
-# tree-structure. (i.e. $up==1 returns the parent page, $up==2 the
-# grandparent etc.)
-#
-# The following entries in that array are filled in with information retrieved
-# using [btree_cursor_info]:
-#
-# $arrayvar(page_no) = The page number
-# $arrayvar(entry_no) = The entry number
-# $arrayvar(page_entries) = Total number of entries on this page
-# $arrayvar(cell_size) = Cell size (local payload + header)
-# $arrayvar(page_freebytes) = Number of free bytes on this page
-# $arrayvar(page_freeblocks) = Number of free blocks on the page
-# $arrayvar(payload_bytes) = Total payload size (local + overflow)
-# $arrayvar(header_bytes) = Header size in bytes
-# $arrayvar(local_payload_bytes) = Local payload size
-# $arrayvar(parent) = Parent page number
-#
-proc cursor_info {arrayvar csr {up 0}} {
- upvar $arrayvar a
- foreach [list a(page_no) \
- a(entry_no) \
- a(page_entries) \
- a(cell_size) \
- a(page_freebytes) \
- a(page_freeblocks) \
- a(payload_bytes) \
- a(header_bytes) \
- a(local_payload_bytes) \
- a(parent) ] [btree_cursor_info $csr $up] {}
-}
-
-# Determine the page-size of the database. This global variable is used
-# throughout the script.
-#
-set pageSize [db eval {PRAGMA page_size}]
-
-# Analyze every table in the database, one at a time.
-#
-# The following query returns the name and root-page of each table in the
-# database, including the sqlite_master table.
-#
-set sql {
- SELECT name, rootpage FROM sqlite_master WHERE type='table'
- UNION ALL
- SELECT 'sqlite_master', 1
- ORDER BY 1
-}
-set wideZero [expr {10000000000 - 10000000000}]
-foreach {name rootpage} [db eval $sql] {
- puts stderr "Analyzing table $name..."
-
- # Code below traverses the table being analyzed (table name $name), using the
- # btree cursor $cursor. Statistics related to table $name are accumulated in
- # the following variables:
- #
- set total_payload $wideZero ;# Payload space used by all entries
- set total_ovfl $wideZero ;# Payload space on overflow pages
- set unused_int $wideZero ;# Unused space on interior nodes
- set unused_leaf $wideZero ;# Unused space on leaf nodes
- set unused_ovfl $wideZero ;# Unused space on overflow pages
- set cnt_ovfl $wideZero ;# Number of entries that use overflows
- set cnt_leaf_entry $wideZero ;# Number of leaf entries
- set cnt_int_entry $wideZero ;# Number of interor entries
- set mx_payload $wideZero ;# Maximum payload size
- set ovfl_pages $wideZero ;# Number of overflow pages used
- set leaf_pages $wideZero ;# Number of leaf pages
- set int_pages $wideZero ;# Number of interior pages
-
- # As the btree is traversed, the array variable $seen($pgno) is set to 1
- # the first time page $pgno is encountered.
- #
- catch {unset seen}
-
- # The following loop runs once for each entry in table $name. The table
- # is traversed using the btree cursor stored in variable $csr
- #
- set csr [btree_cursor $DB $rootpage 0]
- for {btree_first $csr} {![btree_eof $csr]} {btree_next $csr} {
- incr cnt_leaf_entry
-
- # Retrieve information about the entry the btree-cursor points to into
- # the array variable $ci (cursor info).
- #
- cursor_info ci $csr
-
- # Check if the payload of this entry is greater than the current
- # $mx_payload statistic for the table. Also increase the $total_payload
- # statistic.
- #
- if {$ci(payload_bytes)>$mx_payload} {set mx_payload $ci(payload_bytes)}
- incr total_payload $ci(payload_bytes)
-
- # If this entry uses overflow pages, then update the $cnt_ovfl,
- # $total_ovfl, $ovfl_pages and $unused_ovfl statistics.
- #
- set ovfl [expr {$ci(payload_bytes)-$ci(local_payload_bytes)}]
- if {$ovfl} {
- incr cnt_ovfl
- incr total_ovfl $ovfl
- set n [expr {int(ceil($ovfl/($pageSize-4.0)))}]
- incr ovfl_pages $n
- incr unused_ovfl [expr {$n*($pageSize-4) - $ovfl}]
- }
-
- # If this is the first table entry analyzed for the page, then update
- # the page-related statistics $leaf_pages and $unused_leaf. Also, if
- # this page has a parent page that has not been analyzed, retrieve
- # info for the parent and update statistics for it too.
- #
- if {![info exists seen($ci(page_no))]} {
- set seen($ci(page_no)) 1
- incr leaf_pages
- incr unused_leaf $ci(page_freebytes)
-
- # Now check if the page has a parent that has not been analyzed. If
- # so, update the $int_pages, $cnt_int_entry and $unused_int statistics
- # accordingly. Then check if the parent page has a parent that has
- # not yet been analyzed etc.
- #
- # set parent $ci(parent_page_no)
- for {set up 1} \
- {$ci(parent)!=0 && ![info exists seen($ci(parent))]} {incr up} \
- {
- # Mark the parent as seen.
- #
- set seen($ci(parent)) 1
-
- # Retrieve info for the parent and update statistics.
- cursor_info ci $csr $up
- incr int_pages
- incr cnt_int_entry $ci(page_entries)
- incr unused_int $ci(page_freebytes)
- }
- }
- }
- btree_close_cursor $csr
-
- # Handle the special case where a table contains no data. In this case
- # all statistics are zero, except for the number of leaf pages (1) and
- # the unused bytes on leaf pages ($pageSize - 8).
- #
- # An exception to the above is the sqlite_master table. If it is empty
- # then all statistics are zero except for the number of leaf pages (1),
- # and the number of unused bytes on leaf pages ($pageSize - 112).
- #
- if {[llength [array names seen]]==0} {
- set leaf_pages 1
- if {$rootpage==1} {
- set unused_leaf [expr {$pageSize-112}]
- } else {
- set unused_leaf [expr {$pageSize-8}]
- }
- }
-
- # Insert the statistics for the table analyzed into the in-memory database.
- #
- set sql "INSERT INTO space_used VALUES("
- append sql [quote $name]
- append sql ",[quote $name]"
- append sql ",0"
- append sql ",[expr {$cnt_leaf_entry+$cnt_int_entry}]"
- append sql ",$cnt_leaf_entry"
- append sql ",$total_payload"
- append sql ",$total_ovfl"
- append sql ",$cnt_ovfl"
- append sql ",$mx_payload"
- append sql ",$int_pages"
- append sql ",$leaf_pages"
- append sql ",$ovfl_pages"
- append sql ",$unused_int"
- append sql ",$unused_leaf"
- append sql ",$unused_ovfl"
- append sql );
- mem eval $sql
-}
-
-# Analyze every index in the database, one at a time.
-#
-# The query below returns the name, associated table and root-page number
-# for every index in the database.
-#
-set sql {
- SELECT name, tbl_name, rootpage FROM sqlite_master WHERE type='index'
- ORDER BY 2, 1
-}
-foreach {name tbl_name rootpage} [db eval $sql] {
- puts stderr "Analyzing index $name of table $tbl_name..."
-
- # Code below traverses the index being analyzed (index name $name), using the
- # btree cursor $cursor. Statistics related to index $name are accumulated in
- # the following variables:
- #
- set total_payload $wideZero ;# Payload space used by all entries
- set total_ovfl $wideZero ;# Payload space on overflow pages
- set unused_leaf $wideZero ;# Unused space on leaf nodes
- set unused_ovfl $wideZero ;# Unused space on overflow pages
- set cnt_ovfl $wideZero ;# Number of entries that use overflows
- set cnt_leaf_entry $wideZero ;# Number of leaf entries
- set mx_payload $wideZero ;# Maximum payload size
- set ovfl_pages $wideZero ;# Number of overflow pages used
- set leaf_pages $wideZero ;# Number of leaf pages
-
- # As the btree is traversed, the array variable $seen($pgno) is set to 1
- # the first time page $pgno is encountered.
- #
- catch {unset seen}
-
- # The following loop runs once for each entry in index $name. The index
- # is traversed using the btree cursor stored in variable $csr
- #
- set csr [btree_cursor $DB $rootpage 0]
- for {btree_first $csr} {![btree_eof $csr]} {btree_next $csr} {
- incr cnt_leaf_entry
-
- # Retrieve information about the entry the btree-cursor points to into
- # the array variable $ci (cursor info).
- #
- cursor_info ci $csr
-
- # Check if the payload of this entry is greater than the current
- # $mx_payload statistic for the table. Also increase the $total_payload
- # statistic.
- #
- set payload [btree_keysize $csr]
- if {$payload>$mx_payload} {set mx_payload $payload}
- incr total_payload $payload
-
- # If this entry uses overflow pages, then update the $cnt_ovfl,
- # $total_ovfl, $ovfl_pages and $unused_ovfl statistics.
- #
- set ovfl [expr {$payload-$ci(local_payload_bytes)}]
- if {$ovfl} {
- incr cnt_ovfl
- incr total_ovfl $ovfl
- set n [expr {int(ceil($ovfl/($pageSize-4.0)))}]
- incr ovfl_pages $n
- incr unused_ovfl [expr {$n*($pageSize-4) - $ovfl}]
- }
-
- # If this is the first table entry analyzed for the page, then update
- # the page-related statistics $leaf_pages and $unused_leaf.
- #
- if {![info exists seen($ci(page_no))]} {
- set seen($ci(page_no)) 1
- incr leaf_pages
- incr unused_leaf $ci(page_freebytes)
- }
- }
- btree_close_cursor $csr
-
- # Handle the special case where a index contains no data. In this case
- # all statistics are zero, except for the number of leaf pages (1) and
- # the unused bytes on leaf pages ($pageSize - 8).
- #
- if {[llength [array names seen]]==0} {
- set leaf_pages 1
- set unused_leaf [expr {$pageSize-8}]
- }
-
- # Insert the statistics for the index analyzed into the in-memory database.
- #
- set sql "INSERT INTO space_used VALUES("
- append sql [quote $name]
- append sql ",[quote $tbl_name]"
- append sql ",1"
- append sql ",$cnt_leaf_entry"
- append sql ",$cnt_leaf_entry"
- append sql ",$total_payload"
- append sql ",$total_ovfl"
- append sql ",$cnt_ovfl"
- append sql ",$mx_payload"
- append sql ",0"
- append sql ",$leaf_pages"
- append sql ",$ovfl_pages"
- append sql ",0"
- append sql ",$unused_leaf"
- append sql ",$unused_ovfl"
- append sql );
- mem eval $sql
-}
-
-# Generate a single line of output in the statistics section of the
-# report.
-#
-proc statline {title value {extra {}}} {
- set len [string length $title]
- set dots [string range {......................................} $len end]
- set len [string length $value]
- set sp2 [string range { } $len end]
- if {$extra ne ""} {
- set extra " $extra"
- }
- puts "$title$dots $value$sp2$extra"
-}
-
-# Generate a formatted percentage value for $num/$denom
-#
-proc percent {num denom {of {}}} {
- if {$denom==0.0} {return ""}
- set v [expr {$num*100.0/$denom}]
- set of {}
- if {$v==100.0 || $v<0.001 || ($v>1.0 && $v<99.0)} {
- return [format {%5.1f%% %s} $v $of]
- } elseif {$v<0.1 || $v>99.9} {
- return [format {%7.3f%% %s} $v $of]
- } else {
- return [format {%6.2f%% %s} $v $of]
- }
-}
-
-proc divide {num denom} {
- if {$denom==0} {return 0.0}
- return [format %.2f [expr double($num)/double($denom)]]
-}
-
-# Generate a subreport that covers some subset of the database.
-# the $where clause determines which subset to analyze.
-#
-proc subreport {title where} {
- global pageSize file_pgcnt
-
- # Query the in-memory database for the sum of various statistics
- # for the subset of tables/indices identified by the WHERE clause in
- # $where. Note that even if the WHERE clause matches no rows, the
- # following query returns exactly one row (because it is an aggregate).
- #
- # The results of the query are stored directly by SQLite into local
- # variables (i.e. $nentry, $nleaf etc.).
- #
- mem eval "
- SELECT
- int(sum(nentry)) AS nentry,
- int(sum(leaf_entries)) AS nleaf,
- int(sum(payload)) AS payload,
- int(sum(ovfl_payload)) AS ovfl_payload,
- max(mx_payload) AS mx_payload,
- int(sum(ovfl_cnt)) as ovfl_cnt,
- int(sum(leaf_pages)) AS leaf_pages,
- int(sum(int_pages)) AS int_pages,
- int(sum(ovfl_pages)) AS ovfl_pages,
- int(sum(leaf_unused)) AS leaf_unused,
- int(sum(int_unused)) AS int_unused,
- int(sum(ovfl_unused)) AS ovfl_unused
- FROM space_used WHERE $where" {} {}
-
- # Output the sub-report title, nicely decorated with * characters.
- #
- puts ""
- set len [string length $title]
- set stars [string repeat * [expr 65-$len]]
- puts "*** $title $stars"
- puts ""
-
- # Calculate statistics and store the results in TCL variables, as follows:
- #
- # total_pages: Database pages consumed.
- # total_pages_percent: Pages consumed as a percentage of the file.
- # storage: Bytes consumed.
- # payload_percent: Payload bytes used as a percentage of $storage.
- # total_unused: Unused bytes on pages.
- # avg_payload: Average payload per btree entry.
- # avg_fanout: Average fanout for internal pages.
- # avg_unused: Average unused bytes per btree entry.
- # ovfl_cnt_percent: Percentage of btree entries that use overflow pages.
- #
- set total_pages [expr {$leaf_pages+$int_pages+$ovfl_pages}]
- set total_pages_percent [percent $total_pages $file_pgcnt]
- set storage [expr {$total_pages*$pageSize}]
- set payload_percent [percent $payload $storage {of storage consumed}]
- set total_unused [expr {$ovfl_unused+$int_unused+$leaf_unused}]
- set avg_payload [divide $payload $nleaf]
- set avg_unused [divide $total_unused $nleaf]
- if {$int_pages>0} {
- # TODO: Is this formula correct?
- set nTab [mem eval "
- SELECT count(*) FROM (
- SELECT DISTINCT tblname FROM space_used WHERE $where AND is_index=0
- )
- "]
- set avg_fanout [mem eval "
- SELECT (sum(leaf_pages+int_pages)-$nTab)/sum(int_pages) FROM space_used
- WHERE $where AND is_index = 0
- "]
- set avg_fanout [format %.2f $avg_fanout]
- }
- set ovfl_cnt_percent [percent $ovfl_cnt $nleaf {of all entries}]
-
- # Print out the sub-report statistics.
- #
- statline {Percentage of total database} $total_pages_percent
- statline {Number of entries} $nleaf
- statline {Bytes of storage consumed} $storage
- statline {Bytes of payload} $payload $payload_percent
- statline {Average payload per entry} $avg_payload
- statline {Average unused bytes per entry} $avg_unused
- if {[info exists avg_fanout]} {
- statline {Average fanout} $avg_fanout
- }
- statline {Maximum payload per entry} $mx_payload
- statline {Entries that use overflow} $ovfl_cnt $ovfl_cnt_percent
- if {$int_pages>0} {
- statline {Index pages used} $int_pages
- }
- statline {Primary pages used} $leaf_pages
- statline {Overflow pages used} $ovfl_pages
- statline {Total pages used} $total_pages
- if {$int_unused>0} {
- set int_unused_percent \
- [percent $int_unused [expr {$int_pages*$pageSize}] {of index space}]
- statline "Unused bytes on index pages" $int_unused $int_unused_percent
- }
- statline "Unused bytes on primary pages" $leaf_unused \
- [percent $leaf_unused [expr {$leaf_pages*$pageSize}] {of primary space}]
- statline "Unused bytes on overflow pages" $ovfl_unused \
- [percent $ovfl_unused [expr {$ovfl_pages*$pageSize}] {of overflow space}]
- statline "Unused bytes on all pages" $total_unused \
- [percent $total_unused $storage {of all space}]
- return 1
-}
-
-# Calculate the overhead in pages caused by auto-vacuum.
-#
-# This procedure calculates and returns the number of pages used by the
-# auto-vacuum 'pointer-map'. If the database does not support auto-vacuum,
-# then 0 is returned. The two arguments are the size of the database file in
-# pages and the page size used by the database (in bytes).
-proc autovacuum_overhead {filePages pageSize} {
-
- # Read the value of meta 4. If non-zero, then the database supports
- # auto-vacuum. It would be possible to use "PRAGMA auto_vacuum" instead,
- # but that would not work if the SQLITE_OMIT_PRAGMA macro was defined
- # when the library was built.
- set meta4 [lindex [btree_get_meta $::DB] 4]
-
- # If the database is not an auto-vacuum database or the file consists
- # of one page only then there is no overhead for auto-vacuum. Return zero.
- if {0==$meta4 || $filePages==1} {
- return 0
- }
-
- # The number of entries on each pointer map page. The layout of the
- # database file is one pointer-map page, followed by $ptrsPerPage other
- # pages, followed by a pointer-map page etc. The first pointer-map page
- # is the second page of the file overall.
- set ptrsPerPage [expr double($pageSize/5)]
-
- # Return the number of pointer map pages in the database.
- return [expr int(ceil( ($filePages-1.0)/($ptrsPerPage+1.0) ))]
-}
-
-
-# Calculate the summary statistics for the database and store the results
-# in TCL variables. They are output below. Variables are as follows:
-#
-# pageSize: Size of each page in bytes.
-# file_bytes: File size in bytes.
-# file_pgcnt: Number of pages in the file.
-# file_pgcnt2: Number of pages in the file (calculated).
-# av_pgcnt: Pages consumed by the auto-vacuum pointer-map.
-# av_percent: Percentage of the file consumed by auto-vacuum pointer-map.
-# inuse_pgcnt: Data pages in the file.
-# inuse_percent: Percentage of pages used to store data.
-# free_pgcnt: Free pages calculated as (<total pages> - <in-use pages>)
-# free_pgcnt2: Free pages in the file according to the file header.
-# free_percent: Percentage of file consumed by free pages (calculated).
-# free_percent2: Percentage of file consumed by free pages (header).
-# ntable: Number of tables in the db.
-# nindex: Number of indices in the db.
-# nautoindex: Number of indices created automatically.
-# nmanindex: Number of indices created manually.
-# user_payload: Number of bytes of payload in table btrees
-# (not including sqlite_master)
-# user_percent: $user_payload as a percentage of total file size.
-
-set file_bytes [file size $file_to_analyze]
-set file_pgcnt [expr {$file_bytes/$pageSize}]
-
-set av_pgcnt [autovacuum_overhead $file_pgcnt $pageSize]
-set av_percent [percent $av_pgcnt $file_pgcnt]
-
-set sql {SELECT sum(leaf_pages+int_pages+ovfl_pages) FROM space_used}
-set inuse_pgcnt [expr int([mem eval $sql])]
-set inuse_percent [percent $inuse_pgcnt $file_pgcnt]
-
-set free_pgcnt [expr $file_pgcnt-$inuse_pgcnt-$av_pgcnt]
-set free_percent [percent $free_pgcnt $file_pgcnt]
-set free_pgcnt2 [lindex [btree_get_meta $DB] 0]
-set free_percent2 [percent $free_pgcnt2 $file_pgcnt]
-
-set file_pgcnt2 [expr {$inuse_pgcnt+$free_pgcnt2+$av_pgcnt}]
-
-set ntable [db eval {SELECT count(*)+1 FROM sqlite_master WHERE type='table'}]
-set nindex [db eval {SELECT count(*) FROM sqlite_master WHERE type='index'}]
-set sql {SELECT count(*) FROM sqlite_master WHERE name LIKE 'sqlite_autoindex%'}
-set nautoindex [db eval $sql]
-set nmanindex [expr {$nindex-$nautoindex}]
-
-# set total_payload [mem eval "SELECT sum(payload) FROM space_used"]
-set user_payload [mem one {SELECT int(sum(payload)) FROM space_used
- WHERE NOT is_index AND name NOT LIKE 'sqlite_master'}]
-set user_percent [percent $user_payload $file_bytes]
-
-# Output the summary statistics calculated above.
-#
-puts "/** Disk-Space Utilization Report For $file_to_analyze"
-puts "*** As of [clock format [clock seconds] -format {%Y-%b-%d %H:%M:%S}]"
-puts ""
-statline {Page size in bytes} $pageSize
-statline {Pages in the whole file (measured)} $file_pgcnt
-statline {Pages in the whole file (calculated)} $file_pgcnt2
-statline {Pages that store data} $inuse_pgcnt $inuse_percent
-statline {Pages on the freelist (per header)} $free_pgcnt2 $free_percent2
-statline {Pages on the freelist (calculated)} $free_pgcnt $free_percent
-statline {Pages of auto-vacuum overhead} $av_pgcnt $av_percent
-statline {Number of tables in the database} $ntable
-statline {Number of indices} $nindex
-statline {Number of named indices} $nmanindex
-statline {Automatically generated indices} $nautoindex
-statline {Size of the file in bytes} $file_bytes
-statline {Bytes of user payload stored} $user_payload $user_percent
-
-# Output table rankings
-#
-puts ""
-puts "*** Page counts for all tables with their indices ********************"
-puts ""
-mem eval {SELECT tblname, count(*) AS cnt,
- int(sum(int_pages+leaf_pages+ovfl_pages)) AS size
- FROM space_used GROUP BY tblname ORDER BY size+0 DESC, tblname} {} {
- statline [string toupper $tblname] $size [percent $size $file_pgcnt]
-}
-
-# Output subreports
-#
-if {$nindex>0} {
- subreport {All tables and indices} 1
-}
-subreport {All tables} {NOT is_index}
-if {$nindex>0} {
- subreport {All indices} {is_index}
-}
-foreach tbl [mem eval {SELECT name FROM space_used WHERE NOT is_index
- ORDER BY name}] {
- regsub ' $tbl '' qn
- set name [string toupper $tbl]
- set n [mem eval "SELECT count(*) FROM space_used WHERE tblname='$qn'"]
- if {$n>1} {
- subreport "Table $name and all its indices" "tblname='$qn'"
- subreport "Table $name w/o any indices" "name='$qn'"
- subreport "Indices of table $name" "tblname='$qn' AND is_index"
- } else {
- subreport "Table $name" "name='$qn'"
- }
-}
-
-# Output instructions on what the numbers above mean.
-#
-puts {
-*** Definitions ******************************************************
-
-Page size in bytes
-
- The number of bytes in a single page of the database file.
- Usually 1024.
-
-Number of pages in the whole file
-}
-puts \
-" The number of $pageSize-byte pages that go into forming the complete
- database"
-puts \
-{
-Pages that store data
-
- The number of pages that store data, either as primary B*Tree pages or
- as overflow pages. The number at the right is the data pages divided by
- the total number of pages in the file.
-
-Pages on the freelist
-
- The number of pages that are not currently in use but are reserved for
- future use. The percentage at the right is the number of freelist pages
- divided by the total number of pages in the file.
-
-Pages of auto-vacuum overhead
-
- The number of pages that store data used by the database to facilitate
- auto-vacuum. This is zero for databases that do not support auto-vacuum.
-
-Number of tables in the database
-
- The number of tables in the database, including the SQLITE_MASTER table
- used to store schema information.
-
-Number of indices
-
- The total number of indices in the database.
-
-Number of named indices
-
- The number of indices created using an explicit CREATE INDEX statement.
-
-Automatically generated indices
-
- The number of indices used to implement PRIMARY KEY or UNIQUE constraints
- on tables.
-
-Size of the file in bytes
-
- The total amount of disk space used by the entire database files.
-
-Bytes of user payload stored
-
- The total number of bytes of user payload stored in the database. The
- schema information in the SQLITE_MASTER table is not counted when
- computing this number. The percentage at the right shows the payload
- divided by the total file size.
-
-Percentage of total database
-
- The amount of the complete database file that is devoted to storing
- information described by this category.
-
-Number of entries
-
- The total number of B-Tree key/value pairs stored under this category.
-
-Bytes of storage consumed
-
- The total amount of disk space required to store all B-Tree entries
- under this category. The is the total number of pages used times
- the pages size.
-
-Bytes of payload
-
- The amount of payload stored under this category. Payload is the data
- part of table entries and the key part of index entries. The percentage
- at the right is the bytes of payload divided by the bytes of storage
- consumed.
-
-Average payload per entry
-
- The average amount of payload on each entry. This is just the bytes of
- payload divided by the number of entries.
-
-Average unused bytes per entry
-
- The average amount of free space remaining on all pages under this
- category on a per-entry basis. This is the number of unused bytes on
- all pages divided by the number of entries.
-
-Maximum payload per entry
-
- The largest payload size of any entry.
-
-Entries that use overflow
-
- The number of entries that user one or more overflow pages.
-
-Total pages used
-
- This is the number of pages used to hold all information in the current
- category. This is the sum of index, primary, and overflow pages.
-
-Index pages used
-
- This is the number of pages in a table B-tree that hold only key (rowid)
- information and no data.
-
-Primary pages used
-
- This is the number of B-tree pages that hold both key and data.
-
-Overflow pages used
-
- The total number of overflow pages used for this category.
-
-Unused bytes on index pages
-
- The total number of bytes of unused space on all index pages. The
- percentage at the right is the number of unused bytes divided by the
- total number of bytes on index pages.
-
-Unused bytes on primary pages
-
- The total number of bytes of unused space on all primary pages. The
- percentage at the right is the number of unused bytes divided by the
- total number of bytes on primary pages.
-
-Unused bytes on overflow pages
-
- The total number of bytes of unused space on all overflow pages. The
- percentage at the right is the number of unused bytes divided by the
- total number of bytes on overflow pages.
-
-Unused bytes on all pages
-
- The total number of bytes of unused space on all primary and overflow
- pages. The percentage at the right is the number of unused bytes
- divided by the total number of bytes.
-}
-
-# Output a dump of the in-memory database. This can be used for more
-# complex offline analysis.
-#
-puts "**********************************************************************"
-puts "The entire text of this report can be sourced into any SQL database"
-puts "engine for further analysis. All of the text above is an SQL comment."
-puts "The data used to generate this report follows:"
-puts "*/"
-puts "BEGIN;"
-puts $tabledef
-unset -nocomplain x
-mem eval {SELECT * FROM space_used} x {
- puts -nonewline "INSERT INTO space_used VALUES"
- set sep (
- foreach col $x(*) {
- set v $x($col)
- if {$v=="" || ![string is double $v]} {set v [quote $v]}
- puts -nonewline $sep$v
- set sep ,
- }
- puts ");"
-}
-puts "COMMIT;"
-
-} err]} {
- puts "ERROR: $err"
- puts $errorInfo
- exit 1
-}