SQLite

Check-in [baa80c7bc3]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Add the --pageinfo and --stats options to the sqlite3_analyzer utility.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: baa80c7bc31900decae0d8e6090b30fcde377492
User & Date: drh 2011-10-05 19:46:03.527
Context
2011-10-07
12:59
Enhance the sqlite3_data_count() routine so that it can be used to determine if SQLITE_DONE has been seen on the prepared statement. (check-in: 9913996e7b user: drh tags: trunk)
2011-10-05
19:46
Add the --pageinfo and --stats options to the sqlite3_analyzer utility. (check-in: baa80c7bc3 user: drh tags: trunk)
18:18
Update the sqlite3_analyzer utility program to provide more details about the compression performance of ZIPVFS database files. (check-in: fa5ed53296 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to tool/spaceanal.tcl.
︙︙
9
10
11
12
13
14
15

16
17
18
19


20
21
22
23
24
25
26

proc usage {} {
  set argv0 [file rootname [file tail [info nameofexecutable]]]
  puts stderr "Usage: $argv0 database-name"
  exit 1
}
set file_to_analyze {}
set flags(-pageinfo) 0

append argv {}
foreach arg $argv {
  if {[regexp {^-+pageinfo$} $arg]} {
    set flags(-pageinfo) 1


  } elseif {[regexp {^-} $arg]} {
    puts stderr "Unknown option: $arg"
    usage
  } elseif {$file_to_analyze!=""} {
    usage
  } else {
    set file_to_analyze $arg








>




>
>








9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

proc usage {} {
  set argv0 [file rootname [file tail [info nameofexecutable]]]
  puts stderr "Usage: $argv0 database-name"
  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]} {
    set flags(-stats) 1
  } elseif {[regexp {^-} $arg]} {
    puts stderr "Unknown option: $arg"
    usage
  } elseif {$file_to_analyze!=""} {
    usage
  } else {
    set file_to_analyze $arg
︙︙
44
45
46
47
48
49
50






































51
52
53
54
55
56
57

# Open the database
#
sqlite3 db $file_to_analyze
register_dbstat_vtab db

db eval {SELECT count(*) FROM sqlite_master}
set pageSize [expr {wide([db one {PRAGMA page_size}])}]







































# 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:








>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>








47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98

# Open the database
#
sqlite3 db $file_to_analyze
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
}
if {$flags(-stats)} {
  db eval {CREATE VIRTUAL TABLE temp.stat USING dbstat}
  puts "BEGIN;"
  puts "CREATE TABLE stats("
  puts "  name       STRING,           /* Name of table or index */"
  puts "  path       INTEGER,          /* Path to page from root */"
  puts "  pageno     INTEGER,          /* Page number */"
  puts "  pagetype   STRING,           /* 'internal', 'leaf' or 'overflow' */"
  puts "  ncell      INTEGER,          /* Cells on page (0 for overflow) */"
  puts "  payload    INTEGER,          /* Bytes of payload on this page */"
  puts "  unused     INTEGER,          /* Bytes of unused space on this page */"
  puts "  mx_payload INTEGER,          /* Largest payload size of all cells */"
  puts "  pgoffset   INTEGER,          /* Offset of page in file */"
  puts "  pgsize     INTEGER           /* Size of the page */"
  puts ");"
  db eval {SELECT quote(name) || ',' ||
                  quote(path) || ',' ||
                  quote(pageno) || ',' ||
                  quote(pagetype) || ',' ||
                  quote(ncell) || ',' ||
                  quote(payload) || ',' ||
                  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:
︙︙