SQLite

/*
** 2004 April 6
**
** The author disclaims copyright to this source code.  In place of
** a legal notice, here is a blessing:
**
**    May you do good and not evil.
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** $Id: btree.c,v 1.335 2007/02/10 19:22:36 drh Exp $
**
** This file implements a external (disk-based) database using BTrees.
** For a detailed discussion of BTrees, refer to
**
**     Donald E. Knuth, THE ART OF COMPUTER PROGRAMMING, Volume 3:
**     "Sorting And Searching", pages 473-480. Addison-Wesley
**     Publishing Company, Reading, Massachusetts.

**   aResult[3] =  Cell size (local payload + header)
**   aResult[4] =  Number of free bytes on this page
**   aResult[5] =  Number of free blocks on the page
**   aResult[6] =  Total payload size (local + overflow)
**   aResult[7] =  Header size in bytes
**   aResult[8] =  Local payload size
**   aResult[9] =  Parent page number
**   aResult[10]=  Page number of the first overflow page
**
** This routine is used for testing and debugging only.
*/
int sqlite3BtreeCursorInfo(BtCursor *pCur, int *aResult, int upCnt){
  int cnt, idx;
  MemPage *pPage = pCur->pPage;
  BtCursor tmpCur;

    idx = get2byte(&pPage->aData[idx]);
  }
  aResult[5] = cnt;
  if( pPage->pParent==0 || isRootPage(pPage) ){
    aResult[9] = 0;
  }else{
    aResult[9] = pPage->pParent->pgno;
  }
  if( tmpCur.info.iOverflow ){
    aResult[10] = get4byte(&tmpCur.info.pCell[tmpCur.info.iOverflow]);
  }else{
    aResult[10] = 0;
  }
  releaseTempCursor(&tmpCur);
  return SQLITE_OK;
}
#endif

/*

**    May you share freely, never taking more than you give.
**
*************************************************************************
** Code for testing the btree.c module in SQLite.  This code
** is not included in the SQLite library.  It is used for automated
** testing of the SQLite library.
**
** $Id: test3.c,v 1.70 2007/02/10 19:22:36 drh Exp $
*/
#include "sqliteInt.h"
#include "pager.h"
#include "btree.h"
#include "tcl.h"
#include <stdlib.h>
#include <string.h>

  if( argc<3 ){
    Tcl_AppendResult(interp, "wrong # args: should be \"", argv[0],
       " ID ROOT ...\"", 0);
    return TCL_ERROR;
  }
  pBt = sqlite3TextToPtr(argv[1]);
  nRoot = argc-2;
  aRoot = (int*)malloc( sizeof(int)*(argc-2) );
  for(i=0; i<argc-2; i++){
    if( Tcl_GetInt(interp, argv[i+2], &aRoot[i]) ) return TCL_ERROR;
  }
#ifndef SQLITE_OMIT_INTEGRITY_CHECK
  zResult = sqlite3BtreeIntegrityCheck(pBt, aRoot, nRoot, 10000, &nErr);
#else
  zResult = 0;
#endif
  free((void*)aRoot);
  if( zResult ){
    Tcl_AppendResult(interp, zResult, 0);
    sqliteFree(zResult); 
  }
  return TCL_OK;
}

**   aResult[3] =  Cell size (local payload + header)
**   aResult[4] =  Number of free bytes on this page
**   aResult[5] =  Number of free blocks on the page
**   aResult[6] =  Total payload size (local + overflow)
**   aResult[7] =  Header size in bytes
**   aResult[8] =  Local payload size
**   aResult[9] =  Parent page number
**   aResult[10]=  Page number of the first overflow page
*/
static int btree_cursor_info(
  void *NotUsed,
  Tcl_Interp *interp,    /* The TCL interpreter that invoked this command */
  int argc,              /* Number of arguments */
  const char **argv      /* Text of each argument */
){
  BtCursor *pCur;
  int rc;
  int i, j;
  int up;
  int aResult[11];
  char zBuf[400];

  if( argc!=2 && argc!=3 ){
    Tcl_AppendResult(interp, "wrong # args: should be \"", argv[0],
       " ID ?UP-CNT?\"", 0);
    return TCL_ERROR;
  }

  for(i=0; i<sizeof(aResult)/sizeof(aResult[0]); i++){
    sqlite3_snprintf(40,&zBuf[j]," %d", aResult[i]);
    j += strlen(&zBuf[j]);
  }
  Tcl_AppendResult(interp, &zBuf[1], 0);
  return SQLITE_OK;
}

/*
** Copied from btree.c:
*/
static u32 get4byte(unsigned char *p){
  return (p[0]<<24) | (p[1]<<16) | (p[2]<<8) | p[3];
}

/*
**   btree_ovfl_info  BTREE  CURSOR
**
** Given a cursor, return the sequence of pages number that form the
** overflow pages for the data of the entry that the cursor is point
** to.
*/ 
static int btree_ovfl_info(
  void *NotUsed,
  Tcl_Interp *interp,    /* The TCL interpreter that invoked this command */
  int argc,              /* Number of arguments */
  const char **argv      /* Text of each argument */
){
  Btree *pBt;
  BtCursor *pCur;
  Pager *pPager;
  int rc;
  int n;
  int dataSize;
  u32 pgno;
  void *pPage;
  int aResult[11];
  char zElem[100];
  Tcl_DString str;

  if( argc!=3 ){
    Tcl_AppendResult(interp, "wrong # args: should be \"", argv[0], 
                    " BTREE CURSOR", 0);
    return TCL_ERROR;
  }
  pBt = sqlite3TextToPtr(argv[1]);
  pCur = sqlite3TextToPtr(argv[2]);
  if( (*(void**)pCur) != (void*)pBt ){
    Tcl_AppendResult(interp, "Cursor ", argv[2], " does not belong to btree ",
       argv[1], 0);
    return TCL_ERROR;
  }
  pPager = sqlite3BtreePager(pBt);
  rc = sqlite3BtreeCursorInfo(pCur, aResult, 0);
  if( rc ){
    Tcl_AppendResult(interp, errorName(rc), 0);
    return TCL_ERROR;
  }
  dataSize = sqlite3BtreeGetPageSize(pBt) - sqlite3BtreeGetReserve(pBt);
  Tcl_DStringInit(&str);
  n = aResult[6] - aResult[8];
  n = (n + dataSize - 1)/dataSize;
  pgno = (u32)aResult[10];
  while( pgno && n-- ){
    sprintf(zElem, "%d", pgno);
    Tcl_DStringAppendElement(&str, zElem);
    if( sqlite3pager_get(pPager, pgno, &pPage)!=SQLITE_OK ){
      Tcl_DStringFree(&str);
      Tcl_AppendResult(interp, "unable to get page ", zElem, 0);
      return TCL_ERROR;
    }
    pgno = get4byte((unsigned char*)pPage);
    sqlite3pager_unref(pPage);
  }
  Tcl_DStringResult(interp, &str);
  return SQLITE_OK;
}

/*
** The command is provided for the purpose of setting breakpoints.
** in regression test scripts.
**
** By setting a GDB breakpoint on this procedure and executing the
** btree_breakpoint command in a test script, we can stop GDB at

     { "btree_varint_test",        (Tcl_CmdProc*)btree_varint_test        },
     { "btree_begin_statement",    (Tcl_CmdProc*)btree_begin_statement    },
     { "btree_commit_statement",   (Tcl_CmdProc*)btree_commit_statement   },
     { "btree_rollback_statement", (Tcl_CmdProc*)btree_rollback_statement },
     { "btree_from_db",            (Tcl_CmdProc*)btree_from_db            },
     { "btree_set_cache_size",     (Tcl_CmdProc*)btree_set_cache_size     },
     { "btree_cursor_info",        (Tcl_CmdProc*)btree_cursor_info        },
     { "btree_ovfl_info",          (Tcl_CmdProc*)btree_ovfl_info          },
     { "btree_cursor_list",        (Tcl_CmdProc*)btree_cursor_list        },
  };
  int i;

  for(i=0; i<sizeof(aCmd)/sizeof(aCmd[0]); i++){
    Tcl_CreateCommand(interp, aCmd[i].zName, aCmd[i].xProc, 0, 0);
  }

# Run this TCL script using "testfixture" to get a report that shows
# the sequence of database pages used by a particular table or index.
# This information is used for fragmentation analysis.
#

# Get the name of the database to analyze
#

if {[llength $argv]!=2} {
  puts stderr "Usage: $argv0 database-name table-or-index-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
}
set objname [lindex $argv 1]

# Open the database
#
sqlite3 db [lindex $argv 0]
set DB [btree_open [lindex $argv 0] 1000 0]

# 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) \
                a(first_ovfl) ] [btree_cursor_info $csr $up] break
}

# Determine the page-size of the database. This global variable is used
# throughout the script.
#
set pageSize [db eval {PRAGMA page_size}]

# Find the root page of table or index to be analyzed.  Also find out
# if the object is a table or an index.
#
if {$objname=="sqlite_master"} {
  set rootpage 1
  set type table
} else {
  db eval {
    SELECT rootpage, type FROM sqlite_master
     WHERE name=$objname
  } break
  if {![info exists rootpage]} {
    puts stderr "no such table or index: $objname"
    exit 1
  }
  if {$type!="table" && $type!="index"} {
    puts stderr "$objname is something other than a table or index"
    exit 1
  }
  if {![string is integer -strict $rootpage]} {
    puts stderr "invalid root page for $objname: $rootpage"
    exit 1
  } 
}

# The cursor $csr is pointing to an entry.  Print out information
# about the page that $up levels above that page that contains
# the entry.  If $up==0 use the page that contains the entry.
# 
# If information about the page has been printed already, then
# this is a no-op.
# 
proc page_info {csr up} {
  global seen
  cursor_info ci $csr $up
  set pg $ci(page_no)
  if {[info exists seen($pg)]} return
  set seen($pg) 1

  # Do parent pages first
  #
  if {$ci(parent)} {
    page_info $csr [expr {$up+1}]
  }

  # Find the depth of this page
  #
  set depth 1
  set i $up
  while {$ci(parent)} {
    incr i
    incr depth
    cursor_info ci $csr $i
  }

  # print the results
  #
  puts [format {LEVEL %d:  %6d} $depth $pg]
}  

  
  

# Loop through the object and print out page numbers
#
set csr [btree_cursor $DB $rootpage 0]
for {btree_first $csr} {![btree_eof $csr]} {btree_next $csr} {
  page_info $csr 0
  set i 1
  foreach pg [btree_ovfl_info $DB $csr] {
    puts [format {OVFL %3d: %6d} $i $pg]
    incr i
  }
}
exit 0

  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
}

# Maximum distance between pages before we consider it a "gap"
#
set MAXGAP 3

# 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

   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
);}
mem eval $tabledef

proc integerify {real} {
  return [expr int($real)]
}
mem function int integerify

                a(page_entries) \
                a(cell_size) \
                a(page_freebytes) \
                a(page_freeblocks) \
                a(payload_bytes) \
                a(header_bytes) \
                a(local_payload_bytes) \
                a(parent) \
                a(first_ovfl) ] [btree_cursor_info $csr $up] break
}

# Determine the page-size of the database. This global variable is used
# throughout the script.
#
set pageSize [db eval {PRAGMA page_size}]

  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
  set gap_cnt 0                      ;# Number of holes in the page sequence
  set prev_pgno 0                    ;# Last page number seen

  # 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

    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}]
      set pglist [btree_ovfl_info $DB $csr]
    } else {
      set pglist {}
    }

    # 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)
      set pglist "$ci(page_no) $pglist"

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

        # parent pages come before their first child
        set pglist "$ci(page_no) $pglist"
      }
    }

    # Check the page list for fragmentation
    #
    foreach pg $pglist {
      if {($pg<$prev_pgno || $pg>$prev_pgno+$MAXGAP) && $prev_pgno>0} {
        incr gap_cnt
      }
      set prev_pgno $pg
    }
  }
  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).

  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 ",$gap_cnt"
  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 

  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
  set gap_cnt 0                      ;# Number of holes in the page sequence
  set prev_pgno 0                    ;# Last page number seen

  # 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

    # 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)
      set pg $ci(page_no)
      if {$prev_pgno>0 && ($prev_pgno<$pg-$MAXGAP || $prev_pgno>$pg)} {
        incr gap_cnt
      }
      set prev_pgno $ci(page_no)
    }
  }
  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).

  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 ",$gap_cnt"
  append sql );
  mem eval $sql
}

# Generate a single line of output in the statistics section of the
# report.
#

      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
    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]]

  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 fragmentation [percent $gap_cnt $total_pages {fragmentation}]
  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
      )
    "]

  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
  }
  if {$total_pages>1} {
    statline {Fragmentation} $fragmentation
  }
  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