[sql] Import SQLite 3.17.0.

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
 }
 
+# Read and run TCL commands from standard input.  Used to implement
+# the --tclsh option.
+#
+proc tclsh {} {
+  set line {}
+  while {![eof stdin]} {
+    if {$line!=""} {
+      puts -nonewline "> "
+    } else {
+      puts -nonewline "% "
+    }
+    flush stdout
+    append line [gets stdin]
+    if {[info complete $line]} {
+      if {[catch {uplevel #0 $line} result]} {
+        puts stderr "Error: $result"
+      } elseif {$result!=""} {
+        puts $result
+      }
+      set line {}
+    } else {
+      append line \n
+    }
+  }
+}
+
+
 # Get the name of the database to analyze
 #
 proc usage {} {
   set argv0 [file rootname [file tail [info nameofexecutable]]]
   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
 
-   --pageinfo     Show how each page of the database-file is used
+   --stats      Output SQL text that creates a new database containing
+                statistics about the database that was analyzed
+
+   --tclsh      Run the built-in TCL interpreter interactively (for debugging)
+
+   --version    Show the version number of SQLite
 }
   exit 1
 }
 set file_to_analyze {}
 set flags(-pageinfo) 0
 set flags(-stats) 0
+set flags(-debug) 0
 append argv {}
 foreach arg $argv {
   if {[regexp {^-+pageinfo$} $arg]} {
     set flags(-pageinfo) 1
   } elseif {[regexp {^-+stats$} $arg]} {
     set flags(-stats) 1
+  } elseif {[regexp {^-+debug$} $arg]} {
+    set flags(-debug) 1
+  } elseif {[regexp {^-+tclsh$} $arg]} {
+    tclsh
+    exit 0
+  } elseif {[regexp {^-+version$} $arg]} {
+    sqlite3 mem :memory:
+    puts [mem one {SELECT sqlite_version()||' '||sqlite_source_id()}]
+    mem close
+    exit 0
   } elseif {[regexp {^-} $arg]} {
     puts stderr "Unknown option: $arg"
     usage
   } elseif {$file_to_analyze!=""} {
     usage
   } else {
     set file_to_analyze $arg
   }
 }
 if {$file_to_analyze==""} usage
@@ skipping 30 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
 }
+if {$flags(-debug)} {
+  proc dbtrace {txt} {puts $txt; flush stdout;}
+  db trace ::dbtrace
+}
 
 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 22 matching lines @@
                   quote(unused) || ',' ||
                   quote(mx_payload) || ',' ||
                   quote(pgoffset) || ',' ||
                   quote(pgsize) AS x FROM stat} {
     puts "INSERT INTO stats VALUES($x);"
   }
   puts "COMMIT;"
   exit 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:
+if {$flags(-debug)} {
+  proc dbtrace {txt} {puts $txt; flush stdout;}
+  mem trace ::dbtrace
+}
 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
+   is_without_rowid boolean, -- TRUE if WITHOUT ROWID 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
@@ skipping 12 matching lines @@
          ORDER BY name, path}
 db eval {DROP TABLE temp.stat}
 
 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]}]
+  set is_without_rowid [is_without_rowid $name]
   db eval {
     SELECT 
       sum(ncell) AS nentry,
       sum((pagetype=='leaf')*ncell) AS leaf_entries,
       sum(payload) AS payload,
       sum((pagetype=='overflow') * payload) AS ovfl_payload,
       sum(path LIKE '%+000000') AS ovfl_cnt,
       max(mx_payload) AS mx_payload,
       sum(pagetype=='internal') AS int_pages,
       sum(pagetype=='leaf') AS leaf_pages,
@@ skipping 30 matching lines @@
     if {$prev>0 && $pagetype=="leaf" && $pageno!=$prev+1} {
       incr gap_cnt
     }
     set prev $pageno
   }
   mem eval {
     INSERT INTO space_used VALUES(
       $name,
       $tblname,
       $is_index,
+      $is_without_rowid,
       $nentry,
       $leaf_entries,
       $depth,
       $payload,     
       $ovfl_payload,
       $ovfl_cnt,   
       $mx_payload,
       $int_pages,
       $leaf_pages,  
       $ovfl_pages, 
@@ skipping 75 matching lines @@
 #
 proc subreport {title where showFrag} {
   global pageSize file_pgcnt compressOverhead
 
   # 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.).
+  # variables (i.e. $nentry, $payload etc.).
   #
   mem eval "
     SELECT
-      int(sum(nentry)) AS nentry,
-      int(sum(leaf_entries)) AS nleaf,
+      int(sum(
+        CASE WHEN (is_without_rowid OR is_index) THEN nentry 
+             ELSE leaf_entries 
+        END
+      )) AS nentry,
       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,
@@ skipping 19 matching lines @@
   # 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]
+  set avg_payload [divide $payload $nentry]
+  set avg_unused [divide $total_unused $nentry]
   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
     "]
     set avg_fanout [format %.2f $avg_fanout]
   }
-  set ovfl_cnt_percent [percent $ovfl_cnt $nleaf {of all entries}]
+  set ovfl_cnt_percent [percent $ovfl_cnt $nentry {of all entries}]
 
   # Print out the sub-report statistics.
   #
   statline {Percentage of total database} $total_pages_percent
-  statline {Number of entries} $nleaf
+  statline {Number of entries} $nentry
   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
@@ skipping 387 matching lines @@
   }
   puts ");"
 }
 puts "COMMIT;"
 
 } err]} {
   puts "ERROR: $err"
   puts $errorInfo
   exit 1
 }