/ Check-in [bd6bc3b8]
Login

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

Overview
Comment:Changes to support fragmentation analysis in sqlite3_analyzer. (CVS 3634)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:bd6bc3b8f06919000fb082087dff7bbd335d07e9
User & Date: drh 2007-02-10 19:22:36
Context
2007-02-13
01:38
Additional fixes to the new fragmentation feature of sqlite3_analyzer. (CVS 3635) check-in: 82aed271 user: drh tags: trunk
2007-02-10
19:22
Changes to support fragmentation analysis in sqlite3_analyzer. (CVS 3634) check-in: bd6bc3b8 user: drh tags: trunk
2007-02-07
13:09
Explicit collations always override implicit collations. This is backwards compatible since SQLite has not previously supported explicit collations. Need to add tests of this new behavior. (CVS 3633) check-in: 3638823a user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/btree.c.

5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
....
5886
5887
5888
5889
5890
5891
5892

5893
5894
5895
5896
5897
5898
5899
....
5933
5934
5935
5936
5937
5938
5939





5940
5941
5942
5943
5944
5945
5946
** 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.334 2007/01/27 02:24:55 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

**
** 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;





  }
  releaseTempCursor(&tmpCur);
  return SQLITE_OK;
}
#endif

/*







|







 







>







 







>
>
>
>
>







5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
....
5886
5887
5888
5889
5890
5891
5892
5893
5894
5895
5896
5897
5898
5899
5900
....
5934
5935
5936
5937
5938
5939
5940
5941
5942
5943
5944
5945
5946
5947
5948
5949
5950
5951
5952
** 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

/*

Changes to src/test3.c.

9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
...
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
....
1182
1183
1184
1185
1186
1187
1188

1189
1190
1191
1192
1193
1194
1195
1196
1197
1198
1199
1200
1201
1202
1203
1204
1205
1206
1207
....
1220
1221
1222
1223
1224
1225
1226






































































1227
1228
1229
1230
1231
1232
1233
....
1436
1437
1438
1439
1440
1441
1442

1443
1444
1445
1446
1447
1448
1449
**    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.69 2007/01/27 02:24:56 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 = 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(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

*/
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[10];
  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;
}







































































/*
** 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_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);
  }







|







 







|








|







 







>











|







 







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







 







>







9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
...
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
....
1182
1183
1184
1185
1186
1187
1188
1189
1190
1191
1192
1193
1194
1195
1196
1197
1198
1199
1200
1201
1202
1203
1204
1205
1206
1207
1208
....
1221
1222
1223
1224
1225
1226
1227
1228
1229
1230
1231
1232
1233
1234
1235
1236
1237
1238
1239
1240
1241
1242
1243
1244
1245
1246
1247
1248
1249
1250
1251
1252
1253
1254
1255
1256
1257
1258
1259
1260
1261
1262
1263
1264
1265
1266
1267
1268
1269
1270
1271
1272
1273
1274
1275
1276
1277
1278
1279
1280
1281
1282
1283
1284
1285
1286
1287
1288
1289
1290
1291
1292
1293
1294
1295
1296
1297
1298
1299
1300
1301
1302
1303
1304
....
1507
1508
1509
1510
1511
1512
1513
1514
1515
1516
1517
1518
1519
1520
1521
**    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);
  }

Added tool/fragck.tcl.











































































































































































































































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
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
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
# 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

Changes to tool/spaceanal.tcl.

21
22
23
24
25
26
27




28
29
30
31
32
33
34
..
49
50
51
52
53
54
55
56

57
58
59
60
61
62
63
...
101
102
103
104
105
106
107

108
109
110
111
112
113
114
115
...
141
142
143
144
145
146
147


148
149
150
151
152
153
154
...
176
177
178
179
180
181
182



183
184
185
186
187
188
189
190
191
192
193

194
195
196
197
198
199
200
...
206
207
208
209
210
211
212
213












214
215
216
217
218
219
220
...
246
247
248
249
250
251
252

253
254
255
256
257
258
259
...
275
276
277
278
279
280
281


282
283
284
285
286
287
288
...
320
321
322
323
324
325
326





327
328
329
330
331
332
333
...
351
352
353
354
355
356
357

358
359
360
361
362
363
364
...
416
417
418
419
420
421
422
423

424
425
426
427
428
429
430
...
446
447
448
449
450
451
452

453
454
455
456
457
458
459
...
472
473
474
475
476
477
478



479
480
481
482
483
484
485
  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
}





# 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

);}
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) ] [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}]

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



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



    }

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


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












    }
  }
  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 );
  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



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





    }
  }
  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 );
  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

    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]

  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
  }



  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







>
>
>
>







 







|
>







 







>
|







 







>
>







 







>
>
>











>







 







|
>
>
>
>
>
>
>
>
>
>
>
>







 







>







 







>
>







 







>
>
>
>
>







 







>







 







|
>







 







>







 







>
>
>







21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
..
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
...
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
...
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
...
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
...
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
...
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
...
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
...
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
...
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
...
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
...
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
...
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
  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)
................................................................................
        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