/ Check-in [19b8eaaf]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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

Overview
Comment:Enhance the stat VFS to report out the total size of all pages used by a table, even if the ZIPVFS compression backend is in play. Update the sqlite3_analyzer logic to use these new outputs.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 19b8eaaf70db82d401d33beb7fd36045d5e8326f
User & Date: drh 2011-09-28 00:50:14
Context
2011-09-28
01:10
In the shell, allow arbitrary table names on the ".import" command. Ticket [d1d84037b90a449]. check-in: f4dd32d3 user: drh tags: trunk
00:50
Enhance the stat VFS to report out the total size of all pages used by a table, even if the ZIPVFS compression backend is in play. Update the sqlite3_analyzer logic to use these new outputs. check-in: 19b8eaaf user: drh tags: trunk
2011-09-27
13:40
Changes to sqlite3_analyzer to try to avoid integer overflow problems when linking against older versions of TCL. check-in: 8c846311 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/test_stat.c.

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
...
122
123
124
125
126
127
128


129
130
131
132
133
134
135
...
279
280
281
282
283
284
285

286
287
288
289
290
291
292
...
299
300
301
302
303
304
305

306
307
308
309
310
311
312
313
314
315
316
...
447
448
449
450
451
452
453


454
455
456
457
458














459
460
461
462
463
464
465
...
527
528
529
530
531
532
533






534
535
536
537
538
539
540
...
603
604
605
606
607
608
609
610
  "  name       STRING,           /* Name of table or index */"             \
  "  path       INTEGER,          /* Path to page from root */"             \
  "  pageno     INTEGER,          /* Page number */"                        \
  "  pagetype   STRING,           /* 'internal', 'leaf' or 'overflow' */"   \
  "  ncell      INTEGER,          /* Cells on page (0 for overflow) */"     \
  "  payload    INTEGER,          /* Bytes of payload on this page */"      \
  "  unused     INTEGER,          /* Bytes of unused space on this page */" \
  "  mx_payload INTEGER           /* Largest payload size of all cells */"  \
  ");"

#if 0
#define VTAB_SCHEMA2                                                        \
  "CREATE TABLE yy( "                                                       \
  "  pageno   INTEGER,            /* B-tree page number */"                 \
  "  cellno   INTEGER,            /* Cell number within page */"            \
  "  local    INTEGER,            /* Bytes of content stored locally */"    \
  "  payload  INTEGER,            /* Total cell payload size */"            \
  "  novfl    INTEGER             /* Number of overflow pages */"           \

  ");"
#endif


typedef struct StatTable StatTable;
typedef struct StatCursor StatCursor;
typedef struct StatPage StatPage;
typedef struct StatCell StatCell;

struct StatCell {
................................................................................
  char *zPath;                    /* Value of 'path' column */
  u32 iPageno;                    /* Value of 'pageno' column */
  char *zPagetype;                /* Value of 'pagetype' column */
  int nCell;                      /* Value of 'ncell' column */
  int nPayload;                   /* Value of 'payload' column */
  int nUnused;                    /* Value of 'unused' column */
  int nMxPayload;                 /* Value of 'mx_payload' column */


};

struct StatTable {
  sqlite3_vtab base;
  sqlite3 *db;
};

................................................................................
}

static int statDecodePage(Btree *pBt, StatPage *p){
  int nUnused;
  int iOff;
  int nHdr;
  int isLeaf;


  u8 *aData = sqlite3PagerGetData(p->pPg);
  u8 *aHdr = &aData[p->iPgno==1 ? 100 : 0];

  p->flags = aHdr[0];
  p->nCell = get2byte(&aHdr[3]);
  p->nMxPayload = 0;
................................................................................
  iOff = get2byte(&aHdr[1]);
  while( iOff ){
    nUnused += get2byte(&aData[iOff+2]);
    iOff = get2byte(&aData[iOff]);
  }
  p->nUnused = nUnused;
  p->iRightChildPg = isLeaf ? 0 : sqlite3Get4byte(&aHdr[8]);


  if( p->nCell ){
    int i;                        /* Used to iterate through cells */
    int nUsable = sqlite3BtreeGetPageSize(pBt) - sqlite3BtreeGetReserve(pBt);

    p->aCell = sqlite3_malloc((p->nCell+1) * sizeof(StatCell));
    memset(p->aCell, 0, (p->nCell+1) * sizeof(StatCell));

    for(i=0; i<p->nCell; i++){
      StatCell *pCell = &p->aCell[i];

................................................................................


  /* Populate the StatCursor fields with the values to be returned
  ** by the xColumn() and xRowid() methods.
  */
  if( rc==SQLITE_OK ){
    int i;


    StatPage *p = &pCsr->aPage[pCsr->iPage];
    pCsr->zName = (char *)sqlite3_column_text(pCsr->pStmt, 0);
    pCsr->iPageno = p->iPgno;

    statDecodePage(pBt, p);















    switch( p->flags ){
      case 0x05:             /* table internal */
      case 0x02:             /* index internal */
        pCsr->zPagetype = "internal";
        break;
      case 0x0D:             /* table leaf */
................................................................................
      break;
    case 6:            /* unused */
      sqlite3_result_int(ctx, pCsr->nUnused);
      break;
    case 7:            /* mx_payload */
      sqlite3_result_int(ctx, pCsr->nMxPayload);
      break;






  }
  return SQLITE_OK;
}

static int statRowid(sqlite3_vtab_cursor *pCursor, sqlite_int64 *pRowid){
  StatCursor *pCsr = (StatCursor *)pCursor;
  *pRowid = pCsr->iPageno;
................................................................................
}

int SqlitetestStat_Init(Tcl_Interp *interp){
  Tcl_CreateObjCommand(interp, "register_dbstat_vtab", test_dbstat, 0, 0);
  return TCL_OK;
}
#endif /* if defined(SQLITE_TEST) || TCLSH==2 */








|
|
<
<
<
<
<
<
<
<
<
>

<








 







>
>







 







>







 







>



|







 







>
>





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







 







>
>
>
>
>
>







 







<
60
61
62
63
64
65
66
67
68









69
70

71
72
73
74
75
76
77
78
...
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
...
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
...
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
...
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
...
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
...
620
621
622
623
624
625
626

  "  name       STRING,           /* Name of table or index */"             \
  "  path       INTEGER,          /* Path to page from root */"             \
  "  pageno     INTEGER,          /* Page number */"                        \
  "  pagetype   STRING,           /* 'internal', 'leaf' or 'overflow' */"   \
  "  ncell      INTEGER,          /* Cells on page (0 for overflow) */"     \
  "  payload    INTEGER,          /* Bytes of payload on this page */"      \
  "  unused     INTEGER,          /* Bytes of unused space on this page */" \
  "  mx_payload INTEGER,          /* Largest payload size of all cells */"  \
  "  pgoffset   INTEGER,          /* Offset of page in file */"             \









  "  pgsize     INTEGER           /* Size of the page */"                   \
  ");"



typedef struct StatTable StatTable;
typedef struct StatCursor StatCursor;
typedef struct StatPage StatPage;
typedef struct StatCell StatCell;

struct StatCell {
................................................................................
  char *zPath;                    /* Value of 'path' column */
  u32 iPageno;                    /* Value of 'pageno' column */
  char *zPagetype;                /* Value of 'pagetype' column */
  int nCell;                      /* Value of 'ncell' column */
  int nPayload;                   /* Value of 'payload' column */
  int nUnused;                    /* Value of 'unused' column */
  int nMxPayload;                 /* Value of 'mx_payload' column */
  i64 iOffset;                    /* Value of 'pgOffset' column */
  int szPage;                     /* Value of 'pgSize' column */
};

struct StatTable {
  sqlite3_vtab base;
  sqlite3 *db;
};

................................................................................
}

static int statDecodePage(Btree *pBt, StatPage *p){
  int nUnused;
  int iOff;
  int nHdr;
  int isLeaf;
  int szPage;

  u8 *aData = sqlite3PagerGetData(p->pPg);
  u8 *aHdr = &aData[p->iPgno==1 ? 100 : 0];

  p->flags = aHdr[0];
  p->nCell = get2byte(&aHdr[3]);
  p->nMxPayload = 0;
................................................................................
  iOff = get2byte(&aHdr[1]);
  while( iOff ){
    nUnused += get2byte(&aData[iOff+2]);
    iOff = get2byte(&aData[iOff]);
  }
  p->nUnused = nUnused;
  p->iRightChildPg = isLeaf ? 0 : sqlite3Get4byte(&aHdr[8]);
  szPage = sqlite3BtreeGetPageSize(pBt);

  if( p->nCell ){
    int i;                        /* Used to iterate through cells */
    int nUsable = szPage - sqlite3BtreeGetReserve(pBt);

    p->aCell = sqlite3_malloc((p->nCell+1) * sizeof(StatCell));
    memset(p->aCell, 0, (p->nCell+1) * sizeof(StatCell));

    for(i=0; i<p->nCell; i++){
      StatCell *pCell = &p->aCell[i];

................................................................................


  /* Populate the StatCursor fields with the values to be returned
  ** by the xColumn() and xRowid() methods.
  */
  if( rc==SQLITE_OK ){
    int i;
    sqlite3_file *fd;
    sqlite3_int64 x[2];
    StatPage *p = &pCsr->aPage[pCsr->iPage];
    pCsr->zName = (char *)sqlite3_column_text(pCsr->pStmt, 0);
    pCsr->iPageno = p->iPgno;

    statDecodePage(pBt, p);

    /* The default page size and offset */
    pCsr->szPage = sqlite3BtreeGetPageSize(pBt);
    pCsr->iOffset = pCsr->szPage * (p->iPgno - 1);

    /* If connected to a ZIPVFS backend, override the page size and
    ** offset with actual values obtained from ZIPVFS.
    */
    fd = sqlite3PagerFile(pPager);
    x[0] = p->iPgno;
    if( sqlite3OsFileControl(fd, 230440, &x)==SQLITE_OK ){
      pCsr->iOffset = x[0];
      pCsr->szPage = x[1];
    }

    switch( p->flags ){
      case 0x05:             /* table internal */
      case 0x02:             /* index internal */
        pCsr->zPagetype = "internal";
        break;
      case 0x0D:             /* table leaf */
................................................................................
      break;
    case 6:            /* unused */
      sqlite3_result_int(ctx, pCsr->nUnused);
      break;
    case 7:            /* mx_payload */
      sqlite3_result_int(ctx, pCsr->nMxPayload);
      break;
    case 8:            /* pgoffset */
      sqlite3_result_int64(ctx, pCsr->iOffset);
      break;
    case 9:            /* pgsize */
      sqlite3_result_int(ctx, pCsr->szPage);
      break;
  }
  return SQLITE_OK;
}

static int statRowid(sqlite3_vtab_cursor *pCursor, sqlite_int64 *pRowid){
  StatCursor *pCsr = (StatCursor *)pCursor;
  *pRowid = pCsr->iPageno;
................................................................................
}

int SqlitetestStat_Init(Tcl_Interp *interp){
  Tcl_CreateObjCommand(interp, "register_dbstat_vtab", test_dbstat, 0, 0);
  return TCL_OK;
}
#endif /* if defined(SQLITE_TEST) || TCLSH==2 */

Changes to test/stat.test.

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
..
73
74
75
76
77
78
79

80
81
82
83
84
85
86
87
...
104
105
106
107
108
109
110

111
112
113
114
115
116
117
118
...
128
129
130
131
132
133
134

135
136
137
138
139
140
141
142
...
145
146
147
148
149
150
151

152
153
154
155
156
157
158
159
  SELECT * FROM stat;
} {}

ifcapable wal {
  do_execsql_test stat-0.1 {
    PRAGMA journal_mode = WAL;
    PRAGMA journal_mode = delete;

    SELECT * FROM stat;
  } {wal delete sqlite_master / 1 leaf 0 0 916 0}
}

do_test stat-1.0 {
  execsql {
    CREATE TABLE t1(a, b);
    CREATE INDEX i1 ON t1(b);
    INSERT INTO t1(rowid, a, b) VALUES(2, 2, 3);
    INSERT INTO t1(rowid, a, b) VALUES(3, 4, 5);
  }
} {}
do_test stat-1.1 {
  execsql {

    SELECT * FROM stat WHERE name = 't1';
  }
} {t1 / 2 leaf 2 10 998 5}
do_test stat-1.2 {
  execsql {

    SELECT * FROM stat WHERE name = 'i1';
  }
} {i1 / 3 leaf 2 10 1000 5}
do_test stat-1.3 {
  execsql {

    SELECT * FROM stat WHERE name = 'sqlite_master';
  }
} {sqlite_master / 1 leaf 2 77 831 40}
do_test stat-1.4 {
  execsql {
    DROP TABLE t1;
  }
} {}
................................................................................
  CREATE TABLE t3(a PRIMARY KEY, b);
  INSERT INTO t3(rowid, a, b) VALUES(2, a_string(111), a_string(222));
  INSERT INTO t3 SELECT a_string(110+rowid), a_string(221+rowid) FROM t3;
  INSERT INTO t3 SELECT a_string(110+rowid), a_string(221+rowid) FROM t3;
  INSERT INTO t3 SELECT a_string(110+rowid), a_string(221+rowid) FROM t3;
  INSERT INTO t3 SELECT a_string(110+rowid), a_string(221+rowid) FROM t3;
  INSERT INTO t3 SELECT a_string(110+rowid), a_string(221+rowid) FROM t3;

  SELECT * FROM stat WHERE name != 'sqlite_master';
} [list \
  sqlite_autoindex_t3_1 / 3 internal 3 368 623 125       \
  sqlite_autoindex_t3_1 /000/ 8 leaf 8 946 46 123        \
  sqlite_autoindex_t3_1 /001/ 9 leaf 8 988 2 131         \
  sqlite_autoindex_t3_1 /002/ 15 leaf 7 857 137 132      \
  sqlite_autoindex_t3_1 /003/ 20 leaf 6 739 257 129      \
  t3 / 2 internal 15 0 907 0                             \
................................................................................
]
do_execsql_test stat-2.2 { DROP TABLE t3 } {}

do_execsql_test stat-3.1 {
  CREATE TABLE t4(x);
  CREATE INDEX i4 ON t4(x);
  INSERT INTO t4(rowid, x) VALUES(2, a_string(7777));

  SELECT * FROM stat WHERE name != 'sqlite_master';
} [list \
  i4 / 3 leaf 1 103 905 7782                 \
  i4 /000+000000 9 overflow 0 1020 0 0       \
  i4 /000+000001 10 overflow 0 1020 0 0      \
  i4 /000+000002 11 overflow 0 1020 0 0      \
  i4 /000+000003 12 overflow 0 1020 0 0      \
  i4 /000+000004 13 overflow 0 1020 0 0      \
................................................................................
  t4 /000+000005 18 overflow 0 1020 0 0      \
  t4 /000+000006 17 overflow 0 1020 0 0      \
]

do_execsql_test stat-4.1 {
  CREATE TABLE t5(x);
  CREATE INDEX i5 ON t5(x);

  SELECT * FROM stat WHERE name = 't5' OR name = 'i5';
} [list  \
  i5 / 5 leaf 0 0 1016 0 \
  t5 / 4 leaf 0 0 1016 0 \
]

db close
forcedelete test.db
................................................................................
breakpoint
do_execsql_test stat-5.1 {
  PRAGMA auto_vacuum = OFF;
  CREATE VIRTUAL TABLE temp.stat USING dbstat;
  CREATE TABLE t1(x);
  INSERT INTO t1 VALUES(zeroblob(1513));
  INSERT INTO t1 VALUES(zeroblob(1514));

  SELECT * FROM stat WHERE name = 't1';
} [list \
  t1 / 2 leaf 2 993 5 1517                \
  t1 /000+000000 3 overflow 0 1020 0 0    \
  t1 /001+000000 4 overflow 0 1020 0 0    \
]

finish_test







>
|













>
|




>
|




>
|







 







>
|







 







>
|







 







>
|







 







>
|







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
..
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
...
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
...
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
...
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
  SELECT * FROM stat;
} {}

ifcapable wal {
  do_execsql_test stat-0.1 {
    PRAGMA journal_mode = WAL;
    PRAGMA journal_mode = delete;
    SELECT name, path, pageno, pagetype, ncell, payload, unused, mx_payload
      FROM stat;
  } {wal delete sqlite_master / 1 leaf 0 0 916 0}
}

do_test stat-1.0 {
  execsql {
    CREATE TABLE t1(a, b);
    CREATE INDEX i1 ON t1(b);
    INSERT INTO t1(rowid, a, b) VALUES(2, 2, 3);
    INSERT INTO t1(rowid, a, b) VALUES(3, 4, 5);
  }
} {}
do_test stat-1.1 {
  execsql {
    SELECT name, path, pageno, pagetype, ncell, payload, unused, mx_payload
      FROM stat WHERE name = 't1';
  }
} {t1 / 2 leaf 2 10 998 5}
do_test stat-1.2 {
  execsql {
    SELECT name, path, pageno, pagetype, ncell, payload, unused, mx_payload
      FROM stat WHERE name = 'i1';
  }
} {i1 / 3 leaf 2 10 1000 5}
do_test stat-1.3 {
  execsql {
    SELECT name, path, pageno, pagetype, ncell, payload, unused, mx_payload
      FROM stat WHERE name = 'sqlite_master';
  }
} {sqlite_master / 1 leaf 2 77 831 40}
do_test stat-1.4 {
  execsql {
    DROP TABLE t1;
  }
} {}
................................................................................
  CREATE TABLE t3(a PRIMARY KEY, b);
  INSERT INTO t3(rowid, a, b) VALUES(2, a_string(111), a_string(222));
  INSERT INTO t3 SELECT a_string(110+rowid), a_string(221+rowid) FROM t3;
  INSERT INTO t3 SELECT a_string(110+rowid), a_string(221+rowid) FROM t3;
  INSERT INTO t3 SELECT a_string(110+rowid), a_string(221+rowid) FROM t3;
  INSERT INTO t3 SELECT a_string(110+rowid), a_string(221+rowid) FROM t3;
  INSERT INTO t3 SELECT a_string(110+rowid), a_string(221+rowid) FROM t3;
  SELECT name, path, pageno, pagetype, ncell, payload, unused, mx_payload
    FROM stat WHERE name != 'sqlite_master';
} [list \
  sqlite_autoindex_t3_1 / 3 internal 3 368 623 125       \
  sqlite_autoindex_t3_1 /000/ 8 leaf 8 946 46 123        \
  sqlite_autoindex_t3_1 /001/ 9 leaf 8 988 2 131         \
  sqlite_autoindex_t3_1 /002/ 15 leaf 7 857 137 132      \
  sqlite_autoindex_t3_1 /003/ 20 leaf 6 739 257 129      \
  t3 / 2 internal 15 0 907 0                             \
................................................................................
]
do_execsql_test stat-2.2 { DROP TABLE t3 } {}

do_execsql_test stat-3.1 {
  CREATE TABLE t4(x);
  CREATE INDEX i4 ON t4(x);
  INSERT INTO t4(rowid, x) VALUES(2, a_string(7777));
  SELECT name, path, pageno, pagetype, ncell, payload, unused, mx_payload
    FROM stat WHERE name != 'sqlite_master';
} [list \
  i4 / 3 leaf 1 103 905 7782                 \
  i4 /000+000000 9 overflow 0 1020 0 0       \
  i4 /000+000001 10 overflow 0 1020 0 0      \
  i4 /000+000002 11 overflow 0 1020 0 0      \
  i4 /000+000003 12 overflow 0 1020 0 0      \
  i4 /000+000004 13 overflow 0 1020 0 0      \
................................................................................
  t4 /000+000005 18 overflow 0 1020 0 0      \
  t4 /000+000006 17 overflow 0 1020 0 0      \
]

do_execsql_test stat-4.1 {
  CREATE TABLE t5(x);
  CREATE INDEX i5 ON t5(x);
  SELECT name, path, pageno, pagetype, ncell, payload, unused, mx_payload
    FROM stat WHERE name = 't5' OR name = 'i5';
} [list  \
  i5 / 5 leaf 0 0 1016 0 \
  t5 / 4 leaf 0 0 1016 0 \
]

db close
forcedelete test.db
................................................................................
breakpoint
do_execsql_test stat-5.1 {
  PRAGMA auto_vacuum = OFF;
  CREATE VIRTUAL TABLE temp.stat USING dbstat;
  CREATE TABLE t1(x);
  INSERT INTO t1 VALUES(zeroblob(1513));
  INSERT INTO t1 VALUES(zeroblob(1514));
  SELECT name, path, pageno, pagetype, ncell, payload, unused, mx_payload
    FROM stat WHERE name = 't1';
} [list \
  t1 / 2 leaf 2 993 5 1517                \
  t1 /000+000000 3 overflow 0 1020 0 0    \
  t1 /001+000000 4 overflow 0 1020 0 0    \
]

finish_test

Changes to tool/spaceanal.tcl.

55
56
57
58
59
60
61
62

63
64
65
66
67
68
69
..
97
98
99
100
101
102
103
104

105
106
107
108
109
110
111
...
134
135
136
137
138
139
140
141

142
143
144
145
146
147
148
...
220
221
222
223
224
225
226
227

228
229
230
231
232
233
234
...
270
271
272
273
274
275
276





277
278
279
280
281
282
283
   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

# Create a temporary "dbstat" virtual table.
#
db eval {CREATE VIRTUAL TABLE temp.stat USING dbstat}
db eval {CREATE TEMP TABLE dbstat AS SELECT * FROM temp.stat
................................................................................
      sum(path LIKE '%+000000') AS ovfl_cnt,
      max(mx_payload) AS mx_payload,
      sum(isinternal(pagetype, $is_index)) AS int_pages,
      sum(isleaf(pagetype, $is_index)) AS leaf_pages,
      sum(isoverflow(pagetype, $is_index)) AS ovfl_pages,
      sum(isinternal(pagetype, $is_index) * unused) AS int_unused,
      sum(isleaf(pagetype, $is_index) * unused) AS leaf_unused,
      sum(isoverflow(pagetype, $is_index) * unused) AS ovfl_unused

    FROM temp.dbstat WHERE name = $name
  } break

  # Column 'gap_cnt' is set to the number of non-contiguous entries in the
  # list of pages visited if the b-tree structure is traversed in a top-down
  # fashion (each node visited before its child-tree is passed). Any overflow
  # chains present are traversed from start to finish before any child-tree
................................................................................
      $mx_payload,
      $int_pages,
      $leaf_pages,  
      $ovfl_pages, 
      $int_unused, 
      $leaf_unused,
      $ovfl_unused,
      $gap_cnt

    );
  }
}

proc integerify {real} {
  if {[string is double -strict $real]} {
    return [expr {wide($real)}]
................................................................................
      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 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
  }
  if {$total_pages>1} {







|
>







 







|
>







 







|
>







 







|
>







 







>
>
>
>
>







55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
..
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
...
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
...
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
...
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
   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
   compressed_size int  -- Total bytes stored on disk
);}
mem eval $tabledef

# Create a temporary "dbstat" virtual table.
#
db eval {CREATE VIRTUAL TABLE temp.stat USING dbstat}
db eval {CREATE TEMP TABLE dbstat AS SELECT * FROM temp.stat
................................................................................
      sum(path LIKE '%+000000') AS ovfl_cnt,
      max(mx_payload) AS mx_payload,
      sum(isinternal(pagetype, $is_index)) AS int_pages,
      sum(isleaf(pagetype, $is_index)) AS leaf_pages,
      sum(isoverflow(pagetype, $is_index)) AS ovfl_pages,
      sum(isinternal(pagetype, $is_index) * unused) AS int_unused,
      sum(isleaf(pagetype, $is_index) * unused) AS leaf_unused,
      sum(isoverflow(pagetype, $is_index) * unused) AS ovfl_unused,
      sum(pgsize) AS compressed_size
    FROM temp.dbstat WHERE name = $name
  } break

  # Column 'gap_cnt' is set to the number of non-contiguous entries in the
  # list of pages visited if the b-tree structure is traversed in a top-down
  # fashion (each node visited before its child-tree is passed). Any overflow
  # chains present are traversed from start to finish before any child-tree
................................................................................
      $mx_payload,
      $int_pages,
      $leaf_pages,  
      $ovfl_pages, 
      $int_unused, 
      $leaf_unused,
      $ovfl_unused,
      $gap_cnt,
      $compressed_size
    );
  }
}

proc integerify {real} {
  if {[string is double -strict $real]} {
    return [expr {wide($real)}]
................................................................................
      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,
      int(sum(compressed_size)) AS compressed_size
    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 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
  if {$compressed_size!=$storage} {
    set pct [expr {$compressed_size*100.0/$storage}]
    set pct [format {%5.1f%%} $pct]
    statline {Bytes used after compression} $compressed_size $pct
  }
  statline {Bytes of payload} $payload $payload_percent
  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} {