Import SQLite 3.10.2.

third_party/sqlite/src/tool/spaceanal.tcl

 # 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 {
+
+# Argument $tname is the name of a table within the database opened by
+# database handle [db]. Return true if it is a WITHOUT ROWID table, or
+# false otherwise.
+#
+proc is_without_rowid {tname} {
+  set t [string map {' ''} $tname]
+  db eval "PRAGMA index_list = '$t'" o {
+    if {$o(origin) == "pk"} {
+      set n $o(name)
+      if {0==[db one { SELECT count(*) FROM sqlite_master WHERE name=$n }]} {
+        return 1
+      }
+    }
+  }
+  return 0
+}
+
 # Get the name of the database to analyze
 #
 proc usage {} {
   set argv0 [file rootname [file tail [info nameofexecutable]]]
-  puts stderr "Usage: $argv0 database-name"
+  puts stderr "Usage: $argv0 ?--pageinfo? ?--stats? database-filename"
+  puts stderr {
+Analyze the SQLite3 database file specified by the "database-filename"
+argument and output a report detailing size and storage efficiency
+information for the database and its constituent tables and indexes.
+
+Options:
+
+   --stats        Output SQL text that creates a new database containing
+                  statistics about the database that was analyzed
+
+   --pageinfo     Show how each page of the database-file is used
+}
   exit 1
 }
 set file_to_analyze {}
 set flags(-pageinfo) 0
 set flags(-stats) 0
 append argv {}
 foreach arg $argv {
   if {[regexp {^-+pageinfo$} $arg]} {
     set flags(-pageinfo) 1
   } elseif {[regexp {^-+stats$} $arg]} {
@@ skipping 41 matching lines @@
     incr true_file_size [file size $f]
   }
 }
 
 # Open the database
 #
 if {[catch {sqlite3 db $file_to_analyze -uri 1} msg]} {
   puts stderr "error trying to open $file_to_analyze: $msg"
   exit 1
 }
-register_dbstat_vtab db
 
 db eval {SELECT count(*) FROM sqlite_master}
 set pageSize [expr {wide([db one {PRAGMA page_size}])}]
 
 if {$flags(-pageinfo)} {
   db eval {CREATE VIRTUAL TABLE temp.stat USING dbstat}
   db eval {SELECT name, path, pageno FROM temp.stat ORDER BY pageno} {
     puts "$pageno $name $path"
   }
   exit 0
@@ skipping 34 matching lines @@
 # 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
+   depth int,        -- Depth of the b-tree
    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
    gap_cnt int,      -- Number of gaps in the page layout
    compressed_size int  -- Total bytes stored on disk
 );}
 mem eval $tabledef
 
 # Create a temporary "dbstat" virtual table.
 #
 db eval {CREATE VIRTUAL TABLE temp.stat USING dbstat}
 db eval {CREATE TEMP TABLE dbstat AS SELECT * FROM temp.stat
          ORDER BY name, path}
 db eval {DROP TABLE temp.stat}
 
-proc isleaf {pagetype is_index} {
-  return [expr {$pagetype == "leaf" || ($pagetype == "internal" && $is_index)}]
-}
-proc isoverflow {pagetype is_index} {
-  return [expr {$pagetype == "overflow"}]
-}
-proc isinternal {pagetype is_index} {
-  return [expr {$pagetype == "internal" && $is_index==0}]
-}
-
-db func isleaf isleaf
-db func isinternal isinternal
-db func isoverflow isoverflow
-
 set isCompressed 0
 set compressOverhead 0
+set depth 0
 set sql { SELECT name, tbl_name FROM sqlite_master WHERE rootpage>0 }
 foreach {name tblname} [concat sqlite_master sqlite_master [db eval $sql]] {
 
   set is_index [expr {$name!=$tblname}]
+  set idx_btree [expr {$is_index || [is_without_rowid $name]}]
   db eval {
     SELECT 
       sum(ncell) AS nentry,
-      sum(isleaf(pagetype, $is_index) * ncell) AS leaf_entries,
+      sum((pagetype=='leaf')*ncell) AS leaf_entries,
       sum(payload) AS payload,
-      sum(isoverflow(pagetype, $is_index) * payload) AS ovfl_payload,
+      sum((pagetype=='overflow') * payload) AS ovfl_payload,
       sum(path LIKE '%+000000') AS ovfl_cnt,
       max(mx_payload) AS mx_payload,
-      sum(isinternal(pagetype, $is_index)) AS int_pages,
-      sum(isleaf(pagetype, $is_index)) AS leaf_pages,
-      sum(isoverflow(pagetype, $is_index)) AS ovfl_pages,
-      sum(isinternal(pagetype, $is_index) * unused) AS int_unused,
-      sum(isleaf(pagetype, $is_index) * unused) AS leaf_unused,
-      sum(isoverflow(pagetype, $is_index) * unused) AS ovfl_unused,
-      sum(pgsize) AS compressed_size
+      sum(pagetype=='internal') AS int_pages,
+      sum(pagetype=='leaf') AS leaf_pages,
+      sum(pagetype=='overflow') AS ovfl_pages,
+      sum((pagetype=='internal') * unused) AS int_unused,
+      sum((pagetype=='leaf') * unused) AS leaf_unused,
+      sum((pagetype=='overflow') * unused) AS ovfl_unused,
+      sum(pgsize) AS compressed_size,
+      max((length(CASE WHEN path LIKE '%+%' THEN '' ELSE path END)+3)/4)
+        AS depth
     FROM temp.dbstat WHERE name = $name
   } break
 
   set total_pages [expr {$leaf_pages+$int_pages+$ovfl_pages}]
   set storage [expr {$total_pages*$pageSize}]
   if {!$isCompressed && $storage>$compressed_size} {
     set isCompressed 1
     set compressOverhead 14
   }
 
@@ skipping 15 matching lines @@
     }
     set prev $pageno
   }
   mem eval {
     INSERT INTO space_used VALUES(
       $name,
       $tblname,
       $is_index,
       $nentry,
       $leaf_entries,
+      $depth,
       $payload,     
       $ovfl_payload,
       $ovfl_cnt,   
       $mx_payload,
       $int_pages,
       $leaf_pages,  
       $ovfl_pages, 
       $int_unused, 
       $leaf_unused,
       $ovfl_unused,
@@ skipping 89 matching lines @@
       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,
       int(sum(gap_cnt)) AS gap_cnt,
-      int(sum(compressed_size)) AS compressed_size
+      int(sum(compressed_size)) AS compressed_size,
+      int(max(depth)) AS depth,
+      count(*) AS cnt
     FROM space_used WHERE $where" {} {}
 
   # Output the sub-report title, nicely decorated with * characters.
   #
   puts ""
   titleline $title
   puts ""
 
   # Calculate statistics and store the results in TCL variables, as follows:
   #
@@ skipping 16 matching lines @@
   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
+          WHERE $where
     "]
     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
   if {$compressed_size!=$storage} {
     set compressed_size [expr {$compressed_size+$compressOverhead*$total_pages}]
     set pct [expr {$compressed_size*100.0/$storage}]
     set pct [format {%5.1f%%} $pct]
     statline {Bytes used after compression} $compressed_size $pct
   }
   statline {Bytes of payload} $payload $payload_percent
+  if {$cnt==1} {statline {B-tree depth} $depth}
   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
   }
   if {$showFrag && $total_pages>1} {
     set fragmentation [percent $gap_cnt [expr {$total_pages-1}]]
     statline {Non-sequential pages} $gap_cnt $fragmentation
   }
   statline {Maximum payload per entry} $mx_payload
@@ skipping 378 matching lines @@
   }
   puts ");"
 }
 puts "COMMIT;"
 
 } err]} {
   puts "ERROR: $err"
   puts $errorInfo
   exit 1
 }