diff options
Diffstat (limited to 'ext/pdo_sqlite/sqlite/tool')
| -rw-r--r-- | ext/pdo_sqlite/sqlite/tool/lemon.c | 33 | ||||
| -rw-r--r-- | ext/pdo_sqlite/sqlite/tool/lempar.c | 37 | ||||
| -rw-r--r-- | ext/pdo_sqlite/sqlite/tool/memleak.awk | 2 | ||||
| -rw-r--r-- | ext/pdo_sqlite/sqlite/tool/memleak3.tcl | 164 | ||||
| -rw-r--r-- | ext/pdo_sqlite/sqlite/tool/mkkeywordhash.c | 53 | ||||
| -rw-r--r-- | ext/pdo_sqlite/sqlite/tool/spaceanal.tcl | 510 |
6 files changed, 604 insertions, 195 deletions
diff --git a/ext/pdo_sqlite/sqlite/tool/lemon.c b/ext/pdo_sqlite/sqlite/tool/lemon.c index 708b3538d7..8f6e87330a 100644 --- a/ext/pdo_sqlite/sqlite/tool/lemon.c +++ b/ext/pdo_sqlite/sqlite/tool/lemon.c @@ -1606,12 +1606,11 @@ int k; FILE *err; { int spcnt, i; - spcnt = 0; if( argv[0] ) fprintf(err,"%s",argv[0]); spcnt = strlen(argv[0]) + 1; for(i=1; i<n && argv[i]; i++){ fprintf(err," %s",argv[i]); - spcnt += strlen(argv[i]+1); + spcnt += strlen(argv[i])+1; } spcnt += k; for(; argv[i]; i++) fprintf(err," %s",argv[i]); @@ -2305,7 +2304,7 @@ to follow the previous rule."); ** macros. This routine looks for "%ifdef" and "%ifndef" and "%endif" and ** comments them out. Text in between is also commented out as appropriate. */ -static preprocess_input(char *z){ +static void preprocess_input(char *z){ int i, j, k, n; int exclude = 0; int start; @@ -3664,6 +3663,20 @@ int mhflag; /* Output in makeheaders format if true */ fprintf(out," break;\n"); lineno++; } } + if( lemp->vardest ){ + struct symbol *dflt_sp = 0; + for(i=0; i<lemp->nsymbol; i++){ + struct symbol *sp = lemp->symbols[i]; + if( sp==0 || sp->type==TERMINAL || + sp->index<=0 || sp->destructor!=0 ) continue; + fprintf(out," case %d:\n",sp->index); lineno++; + dflt_sp = sp; + } + if( dflt_sp!=0 ){ + emit_destructor_code(out,dflt_sp,lemp,&lineno); + fprintf(out," break;\n"); lineno++; + } + } for(i=0; i<lemp->nsymbol; i++){ struct symbol *sp = lemp->symbols[i]; if( sp==0 || sp->type==TERMINAL || sp->destructor==0 ) continue; @@ -3683,20 +3696,6 @@ int mhflag; /* Output in makeheaders format if true */ emit_destructor_code(out,lemp->symbols[i],lemp,&lineno); fprintf(out," break;\n"); lineno++; } - if( lemp->vardest ){ - struct symbol *dflt_sp = 0; - for(i=0; i<lemp->nsymbol; i++){ - struct symbol *sp = lemp->symbols[i]; - if( sp==0 || sp->type==TERMINAL || - sp->index<=0 || sp->destructor!=0 ) continue; - fprintf(out," case %d:\n",sp->index); lineno++; - dflt_sp = sp; - } - if( dflt_sp!=0 ){ - emit_destructor_code(out,dflt_sp,lemp,&lineno); - fprintf(out," break;\n"); lineno++; - } - } tplt_xfer(lemp->name,in,out,&lineno); /* Generate code which executes whenever the parser stack overflows */ diff --git a/ext/pdo_sqlite/sqlite/tool/lempar.c b/ext/pdo_sqlite/sqlite/tool/lempar.c index aac842f10c..57ec97f6a8 100644 --- a/ext/pdo_sqlite/sqlite/tool/lempar.c +++ b/ext/pdo_sqlite/sqlite/tool/lempar.c @@ -364,11 +364,11 @@ static int yy_find_shift_action( ** return YY_NO_ACTION. */ static int yy_find_reduce_action( - yyParser *pParser, /* The parser */ + int stateno, /* Current state number */ int iLookAhead /* The look-ahead token */ ){ int i; - int stateno = pParser->yystack[pParser->yyidx].stateno; + /* int stateno = pParser->yystack[pParser->yyidx].stateno; */ i = yy_reduce_ofst[stateno]; if( i==YY_REDUCE_USE_DFLT ){ @@ -462,6 +462,18 @@ static void yy_reduce( } #endif /* NDEBUG */ +#ifndef NDEBUG + /* Silence complaints from purify about yygotominor being uninitialized + ** in some cases when it is copied into the stack after the following + ** switch. yygotominor is uninitialized when a rule reduces that does + ** not set the value of its left-hand side nonterminal. Leaving the + ** value of the nonterminal uninitialized is utterly harmless as long + ** as the value is never used. So really the only thing this code + ** accomplishes is to quieten purify. + */ + memset(&yygotominor, 0, sizeof(yygotominor)); +#endif + switch( yyruleno ){ /* Beginning here are the reduction cases. A typical example ** follows: @@ -476,9 +488,24 @@ static void yy_reduce( yygoto = yyRuleInfo[yyruleno].lhs; yysize = yyRuleInfo[yyruleno].nrhs; yypParser->yyidx -= yysize; - yyact = yy_find_reduce_action(yypParser,yygoto); + yyact = yy_find_reduce_action(yymsp[-yysize].stateno,yygoto); if( yyact < YYNSTATE ){ - yy_shift(yypParser,yyact,yygoto,&yygotominor); +#ifdef NDEBUG + /* If we are not debugging and the reduce action popped at least + ** one element off the stack, then we can push the new element back + ** onto the stack here, and skip the stack overflow test in yy_shift(). + ** That gives a significant speed improvement. */ + if( yysize ){ + yypParser->yyidx++; + yymsp -= yysize-1; + yymsp->stateno = yyact; + yymsp->major = yygoto; + yymsp->minor = yygotominor; + }else +#endif + { + yy_shift(yypParser,yyact,yygoto,&yygotominor); + } }else if( yyact == YYNSTATE + YYNRULE + 1 ){ yy_accept(yypParser); } @@ -570,7 +597,7 @@ void Parse( /* (re)initialize the parser, if necessary */ yypParser = (yyParser*)yyp; if( yypParser->yyidx<0 ){ - if( yymajor==0 ) return; + /* if( yymajor==0 ) return; // not sure why this was here... */ yypParser->yyidx = 0; yypParser->yyerrcnt = -1; yypParser->yystack[0].stateno = 0; diff --git a/ext/pdo_sqlite/sqlite/tool/memleak.awk b/ext/pdo_sqlite/sqlite/tool/memleak.awk index 185f174897..928d3b69dc 100644 --- a/ext/pdo_sqlite/sqlite/tool/memleak.awk +++ b/ext/pdo_sqlite/sqlite/tool/memleak.awk @@ -1,6 +1,6 @@ # # This script looks for memory leaks by analyzing the output of "sqlite" -# when compiled with the MEMORY_DEBUG=2 option. +# when compiled with the SQLITE_DEBUG=2 option. # /[0-9]+ malloc / { mem[$6] = $0 diff --git a/ext/pdo_sqlite/sqlite/tool/memleak3.tcl b/ext/pdo_sqlite/sqlite/tool/memleak3.tcl index 69bc4ae88e..2e3f43bc13 100644 --- a/ext/pdo_sqlite/sqlite/tool/memleak3.tcl +++ b/ext/pdo_sqlite/sqlite/tool/memleak3.tcl @@ -13,7 +13,8 @@ exec `which tclsh` $0 "$@" set doco " This script is a tool to help track down memory leaks in the sqlite library. The library must be compiled with the preprocessor symbol -SQLITE_DEBUG set to at least 2. It must be set to 3 to enable stack traces. +SQLITE_MEMDEBUG set to at least 2. It must be set to 3 to enable stack +traces. To use, run the leaky application and save the standard error output. Then, execute this program with the first argument the name of the @@ -24,29 +25,88 @@ If all goes well a summary of unfreed allocations is printed out. If the GNU C library is in use and SQLITE_DEBUG is 3 or greater a stack trace is printed out for each unmatched allocation. +If the \"-r <n>\" option is passed, then the program stops and prints out +the state of the heap immediately after the <n>th call to malloc() or +realloc(). + Example: $ ./testfixture ../sqlite/test/select1.test 2> memtrace.out -$ tclsh $argv0 ./testfixture memtrace.out +$ tclsh $argv0 ?-r <malloc-number>? ./testfixture memtrace.out " -# If stack traces are enabled, the 'addr2line' program is called to -# translate a binary stack address into a human-readable form. -set addr2line addr2line -if { [llength $argv]!=2 } { - puts "Usage: $argv0 <binary file> <mem trace file>" +proc usage {} { + set prg [file tail $::argv0] + puts "Usage: $prg ?-r <malloc-number>? <binary file> <mem trace file>" puts "" - puts [string trim $doco] + puts [string trim $::doco] exit -1 } +proc shift {listvar} { + upvar $listvar l + set ret [lindex $l 0] + set l [lrange $l 1 end] + return $ret +} + +# Argument handling. The following vars are set: +# +# $exe - the name of the executable (i.e. "testfixture" or "./sqlite3") +# $memfile - the name of the file containing the trace output. +# $report_at - The malloc number to stop and report at. Or -1 to read +# all of $memfile. +# +set report_at -1 +while {[llength $argv]>2} { + set arg [shift argv] + switch -- $arg { + "-r" { + set report_at [shift argv] + } + default { + usage + } + } +} +if {[llength $argv]!=2} usage +set exe [lindex $argv 0] +set memfile [lindex $argv 1] + +# If stack traces are enabled, the 'addr2line' program is called to +# translate a binary stack address into a human-readable form. +set addr2line addr2line + +# When the SQLITE_MEMDEBUG is set as described above, SQLite prints +# out a line for each malloc(), realloc() or free() call that the +# library makes. If SQLITE_MEMDEBUG is 3, then a stack trace is printed +# out before each malloc() and realloc() line. +# +# This program parses each line the SQLite library outputs and updates +# the following global Tcl variables to reflect the "current" state of +# the heap used by SQLite. +# +set nBytes 0 ;# Total number of bytes currently allocated. +set nMalloc 0 ;# Total number of malloc()/realloc() calls. +set nPeak 0 ;# Peak of nBytes. +set iPeak 0 ;# nMalloc when nPeak was set. +# +# More detailed state information is stored in the $memmap array. +# Each key in the memmap array is the address of a chunk of memory +# currently allocated from the heap. The value is a list of the +# following form +# +# {<number-of-bytes> <malloc id> <stack trace>} +# +array unset memmap proc process_input {input_file array_name} { upvar $array_name mem set input [open $input_file] set MALLOC {([[:digit:]]+) malloc ([[:digit:]]+) bytes at 0x([[:xdigit:]]+)} + # set STACK {^[[:digit:]]+: STACK: (.*)$} set STACK {^STACK: (.*)$} set FREE {[[:digit:]]+ free ([[:digit:]]+) bytes at 0x([[:xdigit:]]+)} set REALLOC {([[:digit:]]+) realloc ([[:digit:]]+) to ([[:digit:]]+)} @@ -66,6 +126,17 @@ proc process_input {input_file array_name} { set mem($addr) [list $bytes "malloc $mallocid" $stack] set stack "" + # Increase the current heap usage + incr ::nBytes $bytes + + # Increase the number of malloc() calls + incr ::nMalloc + + if {$::nBytes > $::nPeak} { + set ::nPeak $::nBytes + set ::iPeak $::nMalloc + } + } elseif { [regexp $FREE $line dummy bytes addr] } { # If this is a 'free' line, remove the entry from the mem array. If the # entry does not exist, or is the wrong number of bytes, announce a @@ -76,31 +147,86 @@ proc process_input {input_file array_name} { } unset mem($addr) + # Decrease the current heap usage + incr ::nBytes [expr -1 * $bytes] + } elseif { [regexp $REALLOC $line dummy mallocid ob b oa a] } { - # If it is a realloc line, remove the old mem entry and add a new one. + # "free" the old allocation in the internal model: + incr ::nBytes [expr -1 * $ob] unset mem($oa); + + # "malloc" the new allocation set mem($a) [list $b "realloc $mallocid" $stack] + incr ::nBytes $b set stack "" + + # Increase the number of malloc() calls + incr ::nMalloc + + if {$::nBytes > $::nPeak} { + set ::nPeak $::nBytes + set ::iPeak $::nMalloc + } + } else { # puts "REJECT: $line" } + + if {$::nMalloc==$::report_at} report } close $input } -process_input [lindex $argv 1] mem -set exe [lindex $argv 0] - -foreach key [array names mem] { - set bytes [lindex $mem($key) 0] - set mallocid [lindex $mem($key) 1] - set stack [lindex $mem($key) 2] - puts "Leaked $bytes bytes at 0x$key: $mallocid" - foreach frame [lrange $stack 1 10] { - foreach {f l} [split [exec $addr2line -f --exe=$exe $frame] \n] {} +proc printstack {stack} { + set fcount 10 + if {[llength $stack]<10} { + set fcount [llength $stack] + } + foreach frame [lrange $stack 1 $fcount] { + foreach {f l} [split [exec $::addr2line -f --exe=$::exe $frame] \n] {} puts [format "%-30s %s" $f $l] } if {[llength $stack]>0 } {puts ""} } +proc report {} { + + foreach key [array names ::memmap] { + set stack [lindex $::memmap($key) 2] + set bytes [lindex $::memmap($key) 0] + lappend summarymap($stack) $bytes + } + + foreach stack [array names summarymap] { + set allocs $summarymap($stack) + set sum 0 + foreach a $allocs { + incr sum $a + } + lappend sorted [list $sum $stack] + } + + set sorted [lsort -integer -index 0 $sorted] + foreach s $sorted { + set sum [lindex $s 0] + set stack [lindex $s 1] + set allocs $summarymap($stack) + puts "$sum bytes in [llength $allocs] chunks ($allocs)" + printstack $stack + } + + # Print out summary statistics + puts "Total allocations : $::nMalloc" + puts "Total outstanding allocations: [array size ::memmap]" + puts "Current heap usage : $::nBytes bytes" + puts "Peak heap usage : $::nPeak bytes (malloc #$::iPeak)" + + exit +} + +process_input $memfile memmap +report + + + diff --git a/ext/pdo_sqlite/sqlite/tool/mkkeywordhash.c b/ext/pdo_sqlite/sqlite/tool/mkkeywordhash.c index 265e3d1ea2..58d4b9cb78 100644 --- a/ext/pdo_sqlite/sqlite/tool/mkkeywordhash.c +++ b/ext/pdo_sqlite/sqlite/tool/mkkeywordhash.c @@ -32,68 +32,78 @@ struct Keyword { #ifdef SQLITE_OMIT_ALTERTABLE # define ALTER 0 #else -# define ALTER 1 +# define ALTER 0x00000001 +#endif +#define ALWAYS 0x00000002 +#ifdef SQLITE_OMIT_ANALYZE +# define ANALYZE 0 +#else +# define ANALYZE 0x00000004 #endif -#define ALWAYS 2 #ifdef SQLITE_OMIT_ATTACH # define ATTACH 0 #else -# define ATTACH 4 +# define ATTACH 0x00000008 #endif #ifdef SQLITE_OMIT_AUTOINCREMENT # define AUTOINCR 0 #else -# define AUTOINCR 8 +# define AUTOINCR 0x00000010 +#endif +#ifdef SQLITE_OMIT_CAST +# define CAST 0 +#else +# define CAST 0x00000020 #endif #ifdef SQLITE_OMIT_COMPOUND_SELECT # define COMPOUND 0 #else -# define COMPOUND 16 +# define COMPOUND 0x00000040 #endif #ifdef SQLITE_OMIT_CONFLICT_CLAUSE # define CONFLICT 0 #else -# define CONFLICT 32 +# define CONFLICT 0x00000080 #endif #ifdef SQLITE_OMIT_EXPLAIN # define EXPLAIN 0 #else -# define EXPLAIN 128 +# define EXPLAIN 0x00000100 #endif #ifdef SQLITE_OMIT_FOREIGN_KEY # define FKEY 0 #else -# define FKEY 256 +# define FKEY 0x00000200 #endif #ifdef SQLITE_OMIT_PRAGMA # define PRAGMA 0 #else -# define PRAGMA 512 +# define PRAGMA 0x00000400 #endif #ifdef SQLITE_OMIT_REINDEX # define REINDEX 0 #else -# define REINDEX 1024 +# define REINDEX 0x00000800 #endif #ifdef SQLITE_OMIT_SUBQUERY # define SUBQUERY 0 #else -# define SUBQUERY 2048 +# define SUBQUERY 0x00001000 #endif #ifdef SQLITE_OMIT_TRIGGER # define TRIGGER 0 #else -# define TRIGGER 4096 +# define TRIGGER 0x00002000 #endif #ifdef SQLITE_OMIT_VACUUM # define VACUUM 0 #else -# define VACUUM 8192 +# define VACUUM 0x00004000 #endif #ifdef SQLITE_OMIT_VIEW # define VIEW 0 #else -# define VIEW 16384 +# define VIEW 0x00008000 #endif @@ -102,9 +112,11 @@ struct Keyword { */ static Keyword aKeywordTable[] = { { "ABORT", "TK_ABORT", CONFLICT|TRIGGER }, + { "ADD", "TK_ADD", ALTER }, { "AFTER", "TK_AFTER", TRIGGER }, { "ALL", "TK_ALL", ALWAYS }, { "ALTER", "TK_ALTER", ALTER }, + { "ANALYZE", "TK_ANALYZE", ANALYZE }, { "AND", "TK_AND", ALWAYS }, { "AS", "TK_AS", ALWAYS }, { "ASC", "TK_ASC", ALWAYS }, @@ -116,16 +128,18 @@ static Keyword aKeywordTable[] = { { "BY", "TK_BY", ALWAYS }, { "CASCADE", "TK_CASCADE", FKEY }, { "CASE", "TK_CASE", ALWAYS }, + { "CAST", "TK_CAST", CAST }, { "CHECK", "TK_CHECK", ALWAYS }, { "COLLATE", "TK_COLLATE", ALWAYS }, + { "COLUMN", "TK_COLUMNKW", ALTER }, { "COMMIT", "TK_COMMIT", ALWAYS }, { "CONFLICT", "TK_CONFLICT", CONFLICT }, { "CONSTRAINT", "TK_CONSTRAINT", ALWAYS }, { "CREATE", "TK_CREATE", ALWAYS }, { "CROSS", "TK_JOIN_KW", ALWAYS }, - { "CURRENT_DATE", "TK_CDATE", ALWAYS }, - { "CURRENT_TIME", "TK_CTIME", ALWAYS }, - { "CURRENT_TIMESTAMP","TK_CTIMESTAMP", ALWAYS }, + { "CURRENT_DATE", "TK_CTIME_KW", ALWAYS }, + { "CURRENT_TIME", "TK_CTIME_KW", ALWAYS }, + { "CURRENT_TIMESTAMP","TK_CTIME_KW", ALWAYS }, { "DATABASE", "TK_DATABASE", ATTACH }, { "DEFAULT", "TK_DEFAULT", ALWAYS }, { "DEFERRED", "TK_DEFERRED", ALWAYS }, @@ -148,7 +162,7 @@ static Keyword aKeywordTable[] = { { "FOREIGN", "TK_FOREIGN", FKEY }, { "FROM", "TK_FROM", ALWAYS }, { "FULL", "TK_JOIN_KW", ALWAYS }, - { "GLOB", "TK_GLOB", ALWAYS }, + { "GLOB", "TK_LIKE_KW", ALWAYS }, { "GROUP", "TK_GROUP", ALWAYS }, { "HAVING", "TK_HAVING", ALWAYS }, { "IGNORE", "TK_IGNORE", CONFLICT|TRIGGER }, @@ -166,7 +180,7 @@ static Keyword aKeywordTable[] = { { "JOIN", "TK_JOIN", ALWAYS }, { "KEY", "TK_KEY", ALWAYS }, { "LEFT", "TK_JOIN_KW", ALWAYS }, - { "LIKE", "TK_LIKE", ALWAYS }, + { "LIKE", "TK_LIKE_KW", ALWAYS }, { "LIMIT", "TK_LIMIT", ALWAYS }, { "MATCH", "TK_MATCH", ALWAYS }, { "NATURAL", "TK_JOIN_KW", ALWAYS }, @@ -183,6 +197,7 @@ static Keyword aKeywordTable[] = { { "PRIMARY", "TK_PRIMARY", ALWAYS }, { "RAISE", "TK_RAISE", TRIGGER }, { "REFERENCES", "TK_REFERENCES", FKEY }, + { "REGEXP", "TK_LIKE_KW", ALWAYS }, { "REINDEX", "TK_REINDEX", REINDEX }, { "RENAME", "TK_RENAME", ALTER }, { "REPLACE", "TK_REPLACE", CONFLICT }, diff --git a/ext/pdo_sqlite/sqlite/tool/spaceanal.tcl b/ext/pdo_sqlite/sqlite/tool/spaceanal.tcl index e42fb28de4..c9b8f92e25 100644 --- a/ext/pdo_sqlite/sqlite/tool/spaceanal.tcl +++ b/ext/pdo_sqlite/sqlite/tool/spaceanal.tcl @@ -5,7 +5,7 @@ # Get the name of the database to analyze # -set argv $argv0 +#set argv $argv0 if {[llength $argv]!=1} { puts stderr "Usage: $argv0 database-name" exit 1 @@ -29,7 +29,10 @@ if {[file size $file_to_analyze]<512} { sqlite3 db [lindex $argv 0] set DB [btree_open [lindex $argv 0] 1000 0] -# In-memory database for collecting statistics +# 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\ @@ -52,32 +55,80 @@ set tabledef\ );} mem eval $tabledef -# This query will be used to find the root page number for every table -# in the database. -# -set sql { - SELECT name, rootpage - FROM sqlite_master WHERE type='table' - UNION ALL - SELECT 'sqlite_master', 1 - ORDER BY 1 +proc integerify {real} { + return [expr int($real)] } +mem function int integerify -# Quote a string for SQL +# Quote a string for use in an SQL query. Examples: # -proc quote txt { +# [quote {hello world}] == {'hello world'} +# [quote {hello world's}] == {'hello world''s'} +# +proc quote {txt} { regsub -all ' $txt '' q return '$q' } -# Analyze every table in the database, one at a time. +# 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 +} foreach {name rootpage} [db eval $sql] { puts stderr "Analyzing table $name..." - set cursor [btree_cursor $DB $rootpage 0] - set go [btree_first $cursor] - catch {unset seen} + + # 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 0 ;# Payload space used by all entries set total_ovfl 0 ;# Payload space on overflow pages set unused_int 0 ;# Unused space on interior nodes @@ -90,14 +141,35 @@ foreach {name rootpage} [db eval $sql] { set ovfl_pages 0 ;# Number of overflow pages used set leaf_pages 0 ;# Number of leaf pages set int_pages 0 ;# Number of interior pages - while {$go==0} { + + # 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 - set stat [btree_cursor_info $cursor] - set payload [lindex $stat 6] - if {$payload>$mx_payload} {set mx_payload $payload} - incr total_payload $payload - set local [lindex $stat 8] - set ovfl [expr {$payload-$local}] + + # 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 @@ -105,33 +177,59 @@ foreach {name rootpage} [db eval $sql] { incr ovfl_pages $n incr unused_ovfl [expr {$n*($pageSize-4) - $ovfl}] } - set pgno [lindex $stat 0] - if {![info exists seen($pgno)]} { - set seen($pgno) 1 + + # 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 [lindex $stat 4] - set parent [lindex $stat 9] - set up 0 - while {$parent!=0 && ![info exists seen($parent)]} { - incr up - set stat [btree_cursor_info $cursor $up] - set seen($parent) 1 + 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 [lindex $stat 2] - incr unused_int [lindex $stat 4] - set parent [lindex $stat 9] + incr cnt_int_entry $ci(page_entries) + incr unused_int $ci(page_freebytes) } } - set go [btree_next $cursor] } - btree_close_cursor $cursor + 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 - set unused_leaf [expr {$pageSize-8}] - } elseif {$rootpage==1 && ![info exists seen(1)]} { - incr int_pages - incr unused_int [expr {$pageSize-112}] + 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]" @@ -152,23 +250,22 @@ foreach {name rootpage} [db eval $sql] { mem eval $sql } -# This query will be used to find the root page number for every index -# in the database. +# 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' + SELECT name, tbl_name, rootpage FROM sqlite_master WHERE type='index' ORDER BY 2, 1 } - -# Analyze every index in the database, one at a time. -# -set pageSize [db eval {PRAGMA page_size}] foreach {name tbl_name rootpage} [db eval $sql] { puts stderr "Analyzing index $name of table $tbl_name..." - set cursor [btree_cursor $DB $rootpage 0] - set go [btree_first $cursor] - catch {unset seen} + + # 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 0 ;# Payload space used by all entries set total_ovfl 0 ;# Payload space on overflow pages set unused_leaf 0 ;# Unused space on leaf nodes @@ -178,14 +275,36 @@ foreach {name tbl_name rootpage} [db eval $sql] { set mx_payload 0 ;# Maximum payload size set ovfl_pages 0 ;# Number of overflow pages used set leaf_pages 0 ;# Number of leaf pages - while {$go==0} { + + # 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 - set stat [btree_cursor_info $cursor] - set payload [btree_keysize $cursor] + + # 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 - set local [lindex $stat 8] - set ovfl [expr {$payload-$local}] + + # 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 @@ -193,19 +312,29 @@ foreach {name tbl_name rootpage} [db eval $sql] { incr ovfl_pages $n incr unused_ovfl [expr {$n*($pageSize-4) - $ovfl}] } - set pgno [lindex $stat 0] - if {![info exists seen($pgno)]} { - set seen($pgno) 1 + + # 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 [lindex $stat 4] + incr unused_leaf $ci(page_freebytes) } - set go [btree_next $cursor] } - btree_close_cursor $cursor + 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]" @@ -246,7 +375,7 @@ proc percent {num denom {of {}}} { if {$denom==0.0} {return ""} set v [expr {$num*100.0/$denom}] set of {} - if {$v==1.0 || $v==0.0 || ($v>1.0 && $v<99.0)} { + 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] @@ -255,63 +384,106 @@ proc percent {num denom {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 - set hit 0 + 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 - sum(nentry) AS nentry, - sum(leaf_entries) AS nleaf, - sum(payload) AS payload, - sum(ovfl_payload) AS ovfl_payload, + 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, - sum(ovfl_cnt) as ovfl_cnt, - sum(leaf_pages) AS leaf_pages, - sum(int_pages) AS int_pages, - sum(ovfl_pages) AS ovfl_pages, - sum(leaf_unused) AS leaf_unused, - sum(int_unused) AS int_unused, - sum(ovfl_unused) AS ovfl_unused - FROM space_used WHERE $where" {} {set hit 1} - if {!$hit} {return 0} + 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] - incr len 5 - set stars "***********************************" - append stars $stars - set stars [string range $stars $len end] + 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}] - statline "Percentage of total database" [percent $total_pages $::file_pgcnt] - statline "Number of entries" $nleaf - set total_unused [expr {$ovfl_unused+$int_unused+$leaf_unused}] + set total_pages_percent [percent $total_pages $file_pgcnt] set storage [expr {$total_pages*$pageSize}] - statline "Bytes of storage consumed" $storage - statline "Bytes of payload" $payload \ - [percent $payload $storage {of storage consumed}] - statline "Average payload per entry" [expr {$nleaf>0?$payload/$nleaf:0}] - set avgunused [expr {$nleaf>0?$total_unused/$nleaf:0}] - statline "Average unused bytes per entry" $avgunused - set nint [expr {$nentry-$nleaf}] + 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} { - statline "Average fanout" [format %.2f [expr {($nint+0.0)/$int_pages}]] + # 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 \ - [percent $ovfl_cnt $nleaf {of all entries}] + 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 {Index pages used} $int_pages } - statline "Primary pages used" $leaf_pages - statline "Overflow pages used" $ovfl_pages - statline "Total pages used" $total_pages + statline {Primary pages used} $leaf_pages + statline {Overflow pages used} $ovfl_pages + statline {Total pages used} $total_pages if {$int_unused>0} { - statline "Unused bytes on index pages" $int_unused \ + 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}] @@ -322,42 +494,106 @@ proc subreport {title where} { return 1 } -# Output summary statistics: +# 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 -set fsize [file size $file_to_analyze] -set file_pgcnt [expr {$fsize/$pageSize}] -set usedcnt [mem eval \ - {SELECT sum(leaf_pages+int_pages+ovfl_pages) FROM space_used}] -set freecnt [expr {$file_pgcnt-$usedcnt}] -set freecnt2 [lindex [btree_get_meta $DB] 0] statline {Pages in the whole file (measured)} $file_pgcnt -set file_pgcnt2 [expr {$usedcnt+$freecnt2}] statline {Pages in the whole file (calculated)} $file_pgcnt2 -statline {Pages that store data} $usedcnt [percent $usedcnt $file_pgcnt] -statline {Pages on the freelist (per header)}\ - $freecnt2 [percent $freecnt2 $file_pgcnt] -statline {Pages on the freelist (calculated)}\ - $freecnt [percent $freecnt $file_pgcnt] - -set ntable [db eval {SELECT count(*)+1 FROM sqlite_master WHERE type='table'}] +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 -set nindex [db eval {SELECT count(*) FROM sqlite_master WHERE type='index'}] -set autoindex [db eval {SELECT count(*) FROM sqlite_master - WHERE type='index' AND name LIKE '(% autoindex %)'}] -set manindex [expr {$nindex-$autoindex}] statline {Number of indices} $nindex -statline {Number of named indices} $manindex -statline {Automatically generated indices} $autoindex -set total_payload [mem eval "SELECT sum(payload) FROM space_used"] -statline "Size of the file in bytes" $fsize -set user_payload [mem one {SELECT sum(payload) FROM space_used - WHERE NOT is_index AND name NOT LIKE 'sqlite_master'}] -statline "Bytes of user payload stored" $user_payload \ - [percent $user_payload $fsize] +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 # @@ -365,8 +601,8 @@ puts "" puts "*** Page counts for all tables with their indices ********************" puts "" mem eval {SELECT tblname, count(*) AS cnt, - sum(int_pages+leaf_pages+ovfl_pages) AS size - FROM space_used GROUP BY tblname ORDER BY size DESC, tblname} {} { + 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] } @@ -422,6 +658,11 @@ Pages on the freelist 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 @@ -535,7 +776,8 @@ Unused bytes on all pages divided by the total number of bytes. } -# Output the database +# 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" |