Get the aggregate=TRUE feature working on the DBSTAT virtual table.
FossilOrigin-Name: 16fef3db063830884de46d53a289f637a7204fe84fcdee7ea81dbb8bca578952
diff --git a/manifest b/manifest
index 3b7bf3e..bb2af22 100644
--- a/manifest
+++ b/manifest
@@ -1,5 +1,5 @@
-C Begin\san\senhancement\seffort\sfor\sthe\sbuilt-in\sDBSTAT\svirtual\stable.
-D 2019-11-19T14:01:51.987
+C Get\sthe\saggregate=TRUE\sfeature\sworking\son\sthe\sDBSTAT\svirtual\s\stable.
+D 2019-11-19T18:48:11.254
F .fossil-settings/empty-dirs dbb81e8fc0401ac46a1491ab34a7f2c7c0452f2f06b54ebb845d024ca8283ef1
F .fossil-settings/ignore-glob 35175cdfcf539b2318cb04a9901442804be81cd677d8b889fcc9149c21f239ea
F LICENSE.md df5091916dbb40e6e9686186587125e1b2ff51f022cc334e886c19a0e9982724
@@ -476,7 +476,7 @@
F src/ctime.c 1b0724e66f95f33b160b1af85caaf9cceb325d22abf39bd24df4f54a73982251
F src/date.c e1d8ac7102f3f283e63e13867acb0efa33861cf34f0faf4cdbaf9fa7a1eb7041
F src/dbpage.c 135eb3b5e74f9ef74bde5cec2571192c90c86984fa534c88bf4a055076fa19b7
-F src/dbstat.c 191351e68acfb71ad7adf0da464d462a4ead24284dfa81fd4f5911c6cb2e44d1
+F src/dbstat.c 6c407e549406c10fde9ac3987f6d734459205239ad370369bc5fcd683084a4fa
F src/delete.c a5c59b9c0251cf7682bc52af0d64f09b1aefc6781a63592c8f1136f7b73c66e4
F src/expr.c a138de8ae79628a73da2597617dbeafb0f083172be81d93ffa1cafa45161ee8c
F src/fault.c 460f3e55994363812d9d60844b2a6de88826e007
@@ -1373,7 +1373,7 @@
F test/sqldiff1.test 28cd737cf1b0078b1ec1bbf425e674c47785835e
F test/sqllimits1.test 264f4b0f941800ba139d25e33ee919c5d95fea06dfbe8ac291d6811a30984ca5
F test/sqllog.test 6af6cb0b09f4e44e1917e06ce85be7670302517a
-F test/stat.test a08fd3f87e809680824cf1940c362ee34ff83d0cda03121376dfacfebbb352e0
+F test/stat.test 05669008edc5ed950e817c24e8c4b66840fda64d8c76bae27e5fc1bd23d95675
F test/statfault.test f525a7bf633e50afd027700e9a486090684b1ac1
F test/stmt.test 54ed2cc0764bf3e48a058331813c3dbd19fc1d0827c3d8369914a5d8f564ec75
F test/stmtvtab1.test 6873dfb24f8e79cbb5b799b95c2e4349060eb7a3b811982749a84b359468e2d5
@@ -1850,10 +1850,7 @@
F vsixtest/vsixtest.vcxproj.data 2ed517e100c66dc455b492e1a33350c1b20fbcdc
F vsixtest/vsixtest.vcxproj.filters 37e51ffedcdb064aad6ff33b6148725226cd608e
F vsixtest/vsixtest_TemporaryKey.pfx e5b1b036facdb453873e7084e1cae9102ccc67a0
-P 4330f0795dbc2ab41dddd41d5979331fb9b78c477c66367c4be52f929531a45f
-R 3244f69d482823d13732c8928074f447
-T *branch * dbstat-enhancements
-T *sym-dbstat-enhancements *
-T -sym-trunk *
+P 9b5722f0fe666b99677e5f333dd8413aefb9ace7a461d74f6558f0ac53768719
+R 021ee7df1dd822f13f493e93c0d0159d
U drh
-Z 6e47b17aa53d8cb9bfcbdbcbe411679c
+Z 75520593b5d5b7a30bf9bf33072d0e93
diff --git a/manifest.uuid b/manifest.uuid
index 4dd1ca8..c5fbc32 100644
--- a/manifest.uuid
+++ b/manifest.uuid
@@ -1 +1 @@
-9b5722f0fe666b99677e5f333dd8413aefb9ace7a461d74f6558f0ac53768719
\ No newline at end of file
+16fef3db063830884de46d53a289f637a7204fe84fcdee7ea81dbb8bca578952
\ No newline at end of file
diff --git a/src/dbstat.c b/src/dbstat.c
index b3523e0..e219330 100644
--- a/src/dbstat.c
+++ b/src/dbstat.c
@@ -56,21 +56,22 @@
**
** '/1c2/000/' // Left-most child of 451st child of root
*/
-#define VTAB_SCHEMA \
- "CREATE TABLE xx( " \
- " name TEXT," /* 0 Name of table or index */ \
- " path TEXT," /* 1 Path to page from root */ \
- " pageno INTEGER," /* 2 Page number */ \
- " pagetype TEXT," /* 3 'internal', 'leaf' or 'overflow' */ \
- " ncell INTEGER," /* 4 Cells on page (0 for overflow) */ \
- " payload INTEGER," /* 5 Bytes of payload on this page */ \
- " unused INTEGER," /* 6 Bytes of unused space on this page */ \
- " mx_payload INTEGER," /* 7 Largest payload size of all cells */ \
- " pgoffset INTEGER," /* 8 Offset of page in file */ \
- " pgsize INTEGER," /* 9 Size of the page */ \
- " schema TEXT HIDDEN," /* 10 Database schema being analyzed */ \
- " aggregate BOOLEAN HIDDEN" /* 11 aggregate info for each table */ \
- ");"
+static const char zDbstatSchema[] =
+ "CREATE TABLE x("
+ " name TEXT," /* 0 Name of table or index */
+ " path TEXT," /* 1 Path to page from root (NULL for agg) */
+ " pageno INTEGER," /* 2 Page number (page count for aggregates) */
+ " pagetype TEXT," /* 3 'internal', 'leaf', 'overflow', or NULL */
+ " ncell INTEGER," /* 4 Cells on page (0 for overflow) */
+ " payload INTEGER," /* 5 Bytes of payload on this page */
+ " unused INTEGER," /* 6 Bytes of unused space on this page */
+ " mx_payload INTEGER," /* 7 Largest payload size of all cells */
+ " pgoffset INTEGER," /* 8 Offset of page in file (NULL for agg) */
+ " pgsize INTEGER," /* 9 Size of the page (sum for aggregate) */
+ " schema TEXT HIDDEN," /* 10 Database schema being analyzed */
+ " aggregate BOOLEAN HIDDEN" /* 11 aggregate info for each table */
+ ")"
+;
/* Forward reference to data structured used in this module */
typedef struct StatTable StatTable;
@@ -109,24 +110,25 @@
struct StatCursor {
sqlite3_vtab_cursor base; /* base class. MUST BE FIRST! */
sqlite3_stmt *pStmt; /* Iterates through set of root pages */
- int isEof; /* After pStmt has returned SQLITE_DONE */
+ u8 isEof; /* After pStmt has returned SQLITE_DONE */
+ u8 isAgg; /* Aggregate results for each table */
int iDb; /* Schema used for this query */
- int isAgg; /* Aggregate results for each table */
StatPage aPage[32]; /* Pages in path to current page */
int iPage; /* Current entry in aPage[] */
/* Values to return. */
+ u32 iPageno; /* Value of 'pageno' column */
char *zName; /* Value of 'name' column */
char *zPath; /* Value of 'path' column */
- u32 iPageno; /* Value of 'pageno' column */
char *zPagetype; /* Value of 'pagetype' column */
+ int nPage; /* Number of pages in current btree */
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 nUnused; /* Value of 'unused' column */
+ i64 nPayload; /* Value of 'payload' column */
i64 iOffset; /* Value of 'pgOffset' column */
- int szPage; /* Value of 'pgSize' column */
+ i64 szPage; /* Value of 'pgSize' column */
};
/* An instance of the DBSTAT virtual table */
@@ -165,7 +167,7 @@
}else{
iDb = 0;
}
- rc = sqlite3_declare_vtab(db, VTAB_SCHEMA);
+ rc = sqlite3_declare_vtab(db, zDbstatSchema);
if( rc==SQLITE_OK ){
pTab = (StatTable *)sqlite3_malloc64(sizeof(StatTable));
if( pTab==0 ) rc = SQLITE_NOMEM_BKPT;
@@ -273,7 +275,7 @@
}
/*
-** Open a new statvfs cursor.
+** Open a new DBSTAT cursor.
*/
static int statOpen(sqlite3_vtab *pVTab, sqlite3_vtab_cursor **ppCursor){
StatTable *pTab = (StatTable *)pVTab;
@@ -323,8 +325,18 @@
pCsr->isEof = 0;
}
+/* Resize the space-used counters inside of the cursor */
+static void statResetCounts(StatCursor *pCsr){
+ pCsr->nCell = 0;
+ pCsr->nMxPayload = 0;
+ pCsr->nUnused = 0;
+ pCsr->nPayload = 0;
+ pCsr->szPage = 0;
+ pCsr->nPage = 0;
+}
+
/*
-** Close a statvfs cursor.
+** Close a DBSTAT cursor.
*/
static int statClose(sqlite3_vtab_cursor *pCursor){
StatCursor *pCsr = (StatCursor *)pCursor;
@@ -361,6 +373,9 @@
return nLocal;
}
+/* Populate the StatPage object with information about the all
+** cells found on the page currently under analysis.
+*/
static int statDecodePage(Btree *pBt, StatPage *p){
int nUnused;
int iOff;
@@ -481,23 +496,25 @@
sqlite3_file *fd;
sqlite3_int64 x[2];
- /* The default page size and offset */
- pCsr->szPage = sqlite3BtreeGetPageSize(pBt);
- pCsr->iOffset = (i64)pCsr->szPage * (pCsr->iPageno - 1);
-
- /* If connected to a ZIPVFS backend, override the page size and
- ** offset with actual values obtained from ZIPVFS.
+ /* If connected to a ZIPVFS backend, find the page size and
+ ** offset from ZIPVFS.
*/
fd = sqlite3PagerFile(pPager);
x[0] = pCsr->iPageno;
if( sqlite3OsFileControl(fd, 230440, &x)==SQLITE_OK ){
pCsr->iOffset = x[0];
- pCsr->szPage = (int)x[1];
+ pCsr->szPage += x[1];
+ }else{
+ /* Not ZIPVFS: The default page size and offset */
+ pCsr->szPage += sqlite3BtreeGetPageSize(pBt);
+ pCsr->iOffset = (i64)pCsr->szPage * (pCsr->iPageno - 1);
}
}
/*
-** Move a statvfs cursor to the next entry in the file.
+** Move a DBSTAT cursor to the next entry. Normally, the next
+** entry will be the next page, but in aggregated mode (pCsr->isAgg!=0),
+** the next entry is the next btree.
*/
static int statNext(sqlite3_vtab_cursor *pCursor){
int rc;
@@ -513,6 +530,8 @@
statNextRestart:
if( pCsr->aPage[0].pPg==0 ){
+ /* Start measuring space on the next btree */
+ statResetCounts(pCsr);
rc = sqlite3_step(pCsr->pStmt);
if( rc==SQLITE_ROW ){
int nPage;
@@ -525,44 +544,47 @@
rc = sqlite3PagerGet(pPager, iRoot, &pCsr->aPage[0].pPg, 0);
pCsr->aPage[0].iPgno = iRoot;
pCsr->aPage[0].iCell = 0;
- pCsr->aPage[0].zPath = z = sqlite3_mprintf("/");
+ if( !pCsr->isAgg ){
+ pCsr->aPage[0].zPath = z = sqlite3_mprintf("/");
+ if( z==0 ) rc = SQLITE_NOMEM_BKPT;
+ }
pCsr->iPage = 0;
- if( z==0 ) rc = SQLITE_NOMEM_BKPT;
+ pCsr->nPage = 1;
}else{
pCsr->isEof = 1;
return sqlite3_reset(pCsr->pStmt);
}
}else{
-
- /* Page p itself has already been visited. */
+ /* Continue analyzing the btree previously started */
StatPage *p = &pCsr->aPage[pCsr->iPage];
-
+ if( !pCsr->isAgg ) statResetCounts(pCsr);
while( p->iCell<p->nCell ){
StatCell *pCell = &p->aCell[p->iCell];
- if( pCell->iOvfl<pCell->nOvfl ){
- int nUsable;
+ while( pCell->iOvfl<pCell->nOvfl ){
+ int nUsable, iOvfl;
sqlite3BtreeEnter(pBt);
nUsable = sqlite3BtreeGetPageSize(pBt) -
sqlite3BtreeGetReserveNoMutex(pBt);
sqlite3BtreeLeave(pBt);
- pCsr->zName = (char *)sqlite3_column_text(pCsr->pStmt, 0);
- pCsr->iPageno = pCell->aOvfl[pCell->iOvfl];
- pCsr->zPagetype = "overflow";
- pCsr->nCell = 0;
- pCsr->nMxPayload = 0;
- pCsr->zPath = z = sqlite3_mprintf(
- "%s%.3x+%.6x", p->zPath, p->iCell, pCell->iOvfl
- );
- if( pCell->iOvfl<pCell->nOvfl-1 ){
- pCsr->nUnused = 0;
- pCsr->nPayload = nUsable - 4;
- }else{
- pCsr->nPayload = pCell->nLastOvfl;
- pCsr->nUnused = nUsable - 4 - pCsr->nPayload;
- }
- pCell->iOvfl++;
+ pCsr->nPage++;
statSizeAndOffset(pCsr);
- return z==0 ? SQLITE_NOMEM_BKPT : SQLITE_OK;
+ if( pCell->iOvfl<pCell->nOvfl-1 ){
+ pCsr->nPayload += nUsable - 4;
+ }else{
+ pCsr->nPayload += pCell->nLastOvfl;
+ pCsr->nUnused += nUsable - 4 - pCell->nLastOvfl;
+ }
+ iOvfl = pCell->iOvfl;
+ pCell->iOvfl++;
+ if( !pCsr->isAgg ){
+ pCsr->zName = (char *)sqlite3_column_text(pCsr->pStmt, 0);
+ pCsr->iPageno = pCell->aOvfl[iOvfl];
+ pCsr->zPagetype = "overflow";
+ pCsr->zPath = z = sqlite3_mprintf(
+ "%s%.3x+%.6x", p->zPath, p->iCell, iOvfl
+ );
+ return z==0 ? SQLITE_NOMEM_BKPT : SQLITE_OK;
+ }
}
if( p->iRightChildPg ) break;
p->iCell++;
@@ -570,8 +592,13 @@
if( !p->iRightChildPg || p->iCell>p->nCell ){
statClearPage(p);
- if( pCsr->iPage==0 ) return statNext(pCursor);
- pCsr->iPage--;
+ if( pCsr->iPage>0 ){
+ pCsr->iPage--;
+ }else if( pCsr->isAgg ){
+ /* label-statNext-done: When computing aggregate space usage over
+ ** an entire btree, this is the exit point from this function */
+ return SQLITE_OK;
+ }
goto statNextRestart; /* Tail recursion */
}
pCsr->iPage++;
@@ -587,10 +614,13 @@
p[1].iPgno = p->aCell[p->iCell].iChildPg;
}
rc = sqlite3PagerGet(pPager, p[1].iPgno, &p[1].pPg, 0);
+ pCsr->nPage++;
p[1].iCell = 0;
- p[1].zPath = z = sqlite3_mprintf("%s%.3x/", p->zPath, p->iCell);
+ if( !pCsr->isAgg ){
+ p[1].zPath = z = sqlite3_mprintf("%s%.3x/", p->zPath, p->iCell);
+ if( z==0 ) rc = SQLITE_NOMEM_BKPT;
+ }
p->iCell++;
- if( z==0 ) rc = SQLITE_NOMEM_BKPT;
}
@@ -620,16 +650,23 @@
pCsr->zPagetype = "corrupted";
break;
}
- pCsr->nCell = p->nCell;
- pCsr->nUnused = p->nUnused;
- pCsr->nMxPayload = p->nMxPayload;
- pCsr->zPath = z = sqlite3_mprintf("%s", p->zPath);
- if( z==0 ) rc = SQLITE_NOMEM_BKPT;
+ pCsr->nCell += p->nCell;
+ pCsr->nUnused += p->nUnused;
+ if( p->nMxPayload>pCsr->nMxPayload ) pCsr->nMxPayload = p->nMxPayload;
+ if( !pCsr->isAgg ){
+ pCsr->zPath = z = sqlite3_mprintf("%s", p->zPath);
+ if( z==0 ) rc = SQLITE_NOMEM_BKPT;
+ }
nPayload = 0;
for(i=0; i<p->nCell; i++){
nPayload += p->aCell[i].nLocal;
}
- pCsr->nPayload = nPayload;
+ pCsr->nPayload += nPayload;
+
+ /* If computing aggregate space usage by btree, continue with the
+ ** next page. The loop will exit via the return at label-statNext-done
+ */
+ if( pCsr->isAgg ) goto statNextRestart;
}
}
@@ -722,13 +759,21 @@
sqlite3_result_text(ctx, pCsr->zName, -1, SQLITE_TRANSIENT);
break;
case 1: /* path */
- sqlite3_result_text(ctx, pCsr->zPath, -1, SQLITE_TRANSIENT);
+ if( !pCsr->isAgg ){
+ sqlite3_result_text(ctx, pCsr->zPath, -1, SQLITE_TRANSIENT);
+ }
break;
case 2: /* pageno */
- sqlite3_result_int64(ctx, pCsr->iPageno);
+ if( pCsr->isAgg ){
+ sqlite3_result_int64(ctx, pCsr->nPage);
+ }else{
+ sqlite3_result_int64(ctx, pCsr->iPageno);
+ }
break;
case 3: /* pagetype */
- sqlite3_result_text(ctx, pCsr->zPagetype, -1, SQLITE_STATIC);
+ if( !pCsr->isAgg ){
+ sqlite3_result_text(ctx, pCsr->zPagetype, -1, SQLITE_STATIC);
+ }
break;
case 4: /* ncell */
sqlite3_result_int(ctx, pCsr->nCell);
@@ -743,7 +788,9 @@
sqlite3_result_int(ctx, pCsr->nMxPayload);
break;
case 8: /* pgoffset */
- sqlite3_result_int64(ctx, pCsr->iOffset);
+ if( !pCsr->isAgg ){
+ sqlite3_result_int64(ctx, pCsr->iOffset);
+ }
break;
case 9: /* pgsize */
sqlite3_result_int(ctx, pCsr->szPage);
diff --git a/test/stat.test b/test/stat.test
index f13f3ad..1d2c23f 100644
--- a/test/stat.test
+++ b/test/stat.test
@@ -134,6 +134,14 @@
t3 /00f/ 23 leaf 2 738 268 370 \
]
+do_execsql_test stat-2.1agg {
+ SELECT * FROM dbstat WHERE aggregate=TRUE ORDER BY name;
+} [list \
+ sqlite_autoindex_t3_1 {} 5 {} 32 3898 1065 132 {} 5120 \
+ sqlite_master {} 1 {} 2 84 824 49 {} 1024 \
+ t3 {} 17 {} 47 11188 5815 370 {} 17408 \
+]
+
# With every index entry overflowing, make sure no pages are missed
# (other than the locking page which is 64 in this test build.)
#
@@ -171,6 +179,15 @@
t4 /000+000006 18 overflow 0 1020 0 0 \
]
+do_execsql_test stat-3.2 {
+ SELECT *, '|' FROM dbstat WHERE aggregate=TRUE ORDER BY name;
+} [list \
+ i4 {} 9 {} 1 7782 1386 7782 {} 9216 | \
+ sqlite_master {} 1 {} 2 74 834 40 {} 1024 | \
+ t4 {} 8 {} 1 7780 367 7780 {} 8192 | \
+]
+
+
do_execsql_test stat-4.1 {
CREATE TABLE t5(x);
CREATE INDEX i5 ON t5(x);
@@ -201,6 +218,16 @@
t1 /001+000000 4 overflow 0 1020 0 0 \
]
+do_execsql_test stat-5.20 {
+ SELECT name, quote(path), pageno, quote(pagetype), ncell, payload,
+ unused, mx_payload, '|' FROM dbstat('main',1);
+} {sqlite_master NULL 1 NULL 1 34 878 34 | tx NULL 1 NULL 0 0 1016 0 |}
+do_execsql_test stat-5.21 {
+ SELECT name, quote(path), pageno, quote(pagetype), ncell, payload,
+ unused, mx_payload, '|' FROM dbstat('aux1',1);
+} {sqlite_master NULL 1 NULL 1 34 878 34 | t1 NULL 3 NULL 2 3033 5 1517 |}
+
+
do_catchsql_test stat-6.1 {
CREATE VIRTUAL TABLE temp.s2 USING dbstat(mainx);
} {1 {no such database: mainx}}
