| /* |
| ** 2026-04-13 |
| ** |
| ** 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. |
| ** |
| ****************************************************************************** |
| ** |
| ** Partial reimplement of the sqlite3_analyzer utility program as |
| ** loadable SQL function. |
| */ |
| #include "sqlite3ext.h" |
| SQLITE_EXTENSION_INIT1 |
| #include <assert.h> |
| #include <string.h> |
| #include <ctype.h> |
| #include <math.h> |
| |
| /* |
| ** State information for the analysis |
| */ |
| typedef struct Analysis Analysis; |
| struct Analysis { |
| sqlite3 *db; /* Database connection */ |
| sqlite3_context *context; /* SQL function context */ |
| sqlite3_str *pOut; /* Write output here */ |
| char *zSU; /* Name of the temp.space_used table */ |
| const char *zSchema; /* Schema to be analyzed */ |
| }; |
| |
| /* |
| ** Free all resources that the Analysis object references and |
| ** reset the Analysis object. |
| ** |
| ** Call this routine multiple times on the same Analysis object |
| ** is a harmless no-op, as long as the memory for the object itself |
| ** has not been freed. |
| */ |
| static void analysisReset(Analysis *p){ |
| if( p->zSU ){ |
| char *zSql = sqlite3_mprintf("DROP TABLE temp.%s;", p->zSU); |
| if( zSql ){ |
| sqlite3_exec(p->db, zSql, 0, 0, 0); |
| sqlite3_free(zSql); |
| } |
| } |
| sqlite3_str_free(p->pOut); |
| sqlite3_free(p->zSU); |
| memset(p, 0, sizeof(*p)); |
| } |
| |
| /* |
| ** Report an error using formatted text. If zFormat==NULL then report |
| ** an OOM error. |
| */ |
| static void analysisError(Analysis *p, const char *zFormat, ...){ |
| char *zErr; |
| if( zFormat ){ |
| va_list ap; |
| va_start(ap, zFormat); |
| zErr = sqlite3_vmprintf(zFormat, ap); |
| va_end(ap); |
| }else{ |
| zErr = 0; |
| } |
| if( zErr==0 ){ |
| sqlite3_result_error_nomem(p->context); |
| }else{ |
| sqlite3_result_error(p->context, zErr, -1); |
| sqlite3_free(zErr); |
| } |
| analysisReset(p); |
| } |
| |
| /* |
| ** Prepare and return an SQL statement. |
| */ |
| static sqlite3_stmt *analysisVPrep(Analysis *p, const char *zFmt, va_list ap){ |
| char *zSql; |
| int rc; |
| sqlite3_stmt *pStmt = 0; |
| zSql = sqlite3_vmprintf(zFmt, ap); |
| if( zSql==0 ){ analysisError(p,0); return 0; } |
| rc = sqlite3_prepare_v2(p->db, zSql, -1, &pStmt, 0); |
| if( rc ){ |
| analysisError(p, "SQL parse error: %s\nOriginal SQL: %s", |
| sqlite3_errmsg(p->db), zSql); |
| sqlite3_finalize(pStmt); |
| analysisReset(p); |
| pStmt = 0; |
| } |
| sqlite3_free(zSql); |
| return pStmt; |
| } |
| static sqlite3_stmt *analysisPrepare(Analysis *p, const char *zFormat, ...){ |
| va_list ap; |
| sqlite3_stmt *pStmt = 0; |
| va_start(ap, zFormat); |
| pStmt = analysisVPrep(p,zFormat,ap); |
| va_end(ap); |
| return pStmt; |
| } |
| |
| /* |
| ** If rc is something other than SQLITE_DONE or SQLITE_OK, then report |
| ** an error and return true. |
| ** |
| ** If rc is SQLITE_DONE or SQLITE_OK, then return false. |
| ** |
| ** The prepared statement is closed in either case. |
| */ |
| static int analysisStmtFinish(Analysis *p, int rc, sqlite3_stmt *pStmt){ |
| if( rc==SQLITE_DONE ){ |
| rc = SQLITE_OK; |
| } |
| if( rc!=SQLITE_OK || (rc = sqlite3_reset(pStmt))!=SQLITE_OK ){ |
| analysisError(p, "SQL run-time error: %s\nOriginal SQL: %s", |
| sqlite3_errmsg(p->db), sqlite3_sql(pStmt)); |
| analysisReset(p); |
| } |
| sqlite3_finalize(pStmt); |
| return rc; |
| } |
| |
| /* |
| ** Run SQL. Return the number of errors. |
| */ |
| static int analysisSql(Analysis *p, const char *zFormat, ...){ |
| va_list ap; |
| int rc; |
| sqlite3_stmt *pStmt = 0; |
| va_start(ap, zFormat); |
| pStmt = analysisVPrep(p,zFormat,ap); |
| va_end(ap); |
| if( pStmt==0 ) return 1; |
| while( (rc = sqlite3_step(pStmt))==SQLITE_ROW ){} |
| if( rc==SQLITE_DONE ){ |
| rc = SQLITE_OK; |
| }else{ |
| analysisError(p, "SQL run-time error: %s\nOriginal SQL: %s", |
| sqlite3_errmsg(p->db), sqlite3_sql(pStmt)); |
| analysisReset(p); |
| } |
| sqlite3_finalize(pStmt); |
| return rc; |
| } |
| |
| /* |
| ** Run an SQL query that returns an integer. Write that integer |
| ** into *piRes. Return the number of errors. |
| */ |
| static int analysisSqlInt( |
| Analysis *p, |
| sqlite3_int64 *piRes, |
| const char *zFormat, ... |
| ){ |
| va_list ap; |
| int rc; |
| sqlite3_stmt *pStmt = 0; |
| va_start(ap, zFormat); |
| pStmt = analysisVPrep(p,zFormat,ap); |
| va_end(ap); |
| if( pStmt==0 ) return 1; |
| rc = sqlite3_step(pStmt); |
| if( rc==SQLITE_ROW ){ |
| *piRes = sqlite3_column_int64(pStmt, 0); |
| rc = SQLITE_OK; |
| }else if( rc==SQLITE_DONE ){ |
| rc = SQLITE_OK; |
| }else{ |
| if( p->db ){ |
| /* p->db is NULL if there was some prior error */ |
| analysisError(p, "SQL run-time error: %s\nOriginal SQL: %s", |
| sqlite3_errmsg(p->db), sqlite3_sql(pStmt)); |
| } |
| analysisReset(p); |
| } |
| sqlite3_finalize(pStmt); |
| return rc; |
| } |
| |
| /* |
| ** Add to the output a title line that contains the text determined |
| ** by the format string. If the output is initially empty, begin |
| ** the title line with "/" so that it forms the beginning of a C-style |
| ** comment. Otherwise begin with a new-line. Always finish with a |
| ** newline. |
| */ |
| static void analysisTitle(Analysis *p, const char *zFormat, ...){ |
| char *zFirst; |
| char *zTitle; |
| size_t nTitle; |
| va_list ap; |
| va_start(ap, zFormat); |
| zTitle = sqlite3_vmprintf(zFormat, ap); |
| va_end(ap); |
| if( zTitle==0 ){ |
| analysisError(p, 0); |
| return; |
| } |
| zFirst = sqlite3_str_length(p->pOut)==0 ? "/" : "\n*"; |
| nTitle = strlen(zTitle); |
| if( nTitle>=75 ){ |
| sqlite3_str_appendf(p->pOut, "%s** %z\n\n", zFirst, zTitle); |
| }else{ |
| int nExtra = 74 - (int)nTitle; |
| sqlite3_str_appendf(p->pOut, "%s** %z %.*c\n\n", zFirst, zTitle, |
| nExtra, '*'); |
| } |
| } |
| |
| /* |
| ** Add an output line that begins with the zDesc text extended out to |
| ** 50 columns with "." characters, and followed by whatever text is |
| ** described by zFormat. |
| */ |
| static void analysisLine( |
| Analysis *p, /* Analysis context */ |
| const char *zDesc, /* Description */ |
| const char *zFormat, /* Argument to the description */ |
| ... |
| ){ |
| char *zTxt; |
| size_t nDesc; |
| va_list ap; |
| va_start(ap, zFormat); |
| zTxt = sqlite3_vmprintf(zFormat, ap); |
| va_end(ap); |
| if( zTxt==0 ){ |
| analysisError(p, 0); |
| return; |
| } |
| nDesc = strlen(zDesc); |
| if( nDesc>=50 ){ |
| sqlite3_str_appendf(p->pOut, "%s %z", zDesc, zTxt); |
| }else{ |
| int nExtra = 50 - (int)nDesc; |
| sqlite3_str_appendf(p->pOut, "%s%.*c %z", zDesc, nExtra, '.', zTxt); |
| } |
| } |
| |
| /* |
| ** Write a percentage into the output. The number written should show |
| ** two or three significant digits, with the decimal point being the fourth |
| ** character. |
| */ |
| static void analysisPercent(Analysis *p, double r){ |
| char zNum[100]; |
| char *zDP; |
| int nLeadingDigit; |
| int sz; |
| sqlite3_snprintf(sizeof(zNum)-5, zNum, r>=10.0 ? "%.3g" :"%.2g", r); |
| sz = (int)strlen(zNum); |
| zDP = strchr(zNum, '.'); |
| if( zDP==0 ){ |
| memcpy(zNum+sz,".0",3); |
| nLeadingDigit = sz; |
| sz += 2; |
| }else{ |
| nLeadingDigit = (int)(zDP - zNum); |
| } |
| if( nLeadingDigit<3 ){ |
| sqlite3_str_appendchar(p->pOut, 3-nLeadingDigit, ' '); |
| } |
| sqlite3_str_append(p->pOut, zNum, sz); |
| sqlite3_str_append(p->pOut, "%\n", 2); |
| } |
| |
| /* |
| ** Create a subreport on a subset of tables and/or indexes. |
| ** |
| ** The title if the subreport is given by zTitle. zWhere is |
| ** a boolean expression that can go in the WHERE clause to select |
| ** the relevant rows of the s.zSU table. |
| */ |
| static int analysisSubreport( |
| Analysis *p, /* Analysis context */ |
| char *zTitle, /* Title for this subreport */ |
| char *zWhere, /* WHERE clause for this subreport */ |
| sqlite3_int64 pgsz, /* Database page size */ |
| sqlite3_int64 nPage /* Number of pages in entire database */ |
| ){ |
| sqlite3_stmt *pStmt; /* Statement to query p->zSU */ |
| sqlite3_int64 nentry; /* Number of btree entires */ |
| sqlite3_int64 payload; /* Payload in bytes */ |
| sqlite3_int64 ovfl_payload; /* overflow payload in bytes */ |
| sqlite3_int64 mx_payload; /* largest individual payload */ |
| sqlite3_int64 ovfl_cnt; /* Number entries using overflow */ |
| sqlite3_int64 leaf_pages; /* Leaf pages */ |
| sqlite3_int64 int_pages; /* internal pages */ |
| sqlite3_int64 ovfl_pages; /* overflow pages */ |
| sqlite3_int64 leaf_unused; /* unused bytes on leaf pages */ |
| sqlite3_int64 int_unused; /* unused bytes on internal pages */ |
| sqlite3_int64 ovfl_unused; /* unused bytes on overflow pages */ |
| sqlite3_int64 int_cell; /* B-tree entries on internal pages */ |
| sqlite3_int64 depth; /* btree depth */ |
| sqlite3_int64 cnt; /* Number of s.zSU entries that match */ |
| sqlite3_int64 storage; /* Total bytes */ |
| sqlite3_int64 total_pages; /* Total page count */ |
| sqlite3_int64 total_unused; /* Total unused bytes */ |
| sqlite3_int64 total_meta; /* Total metadata */ |
| int rc; |
| |
| if( zTitle==0 || zWhere==0 ){ |
| analysisError(p, 0); |
| return SQLITE_NOMEM; |
| } |
| pStmt = analysisPrepare(p, |
| "SELECT\n" |
| " sum(if(is_without_rowid OR is_index,nentry,leaf_entries)),\n" /* 0 */ |
| " sum(payload),\n" /* 1 */ |
| " sum(ovfl_payload),\n" /* 2 */ |
| " max(mx_payload),\n" /* 3 */ |
| " sum(ovfl_cnt),\n" /* 4 */ |
| " sum(leaf_pages),\n" /* 5 */ |
| " sum(int_pages),\n" /* 6 */ |
| " sum(ovfl_pages),\n" /* 7 */ |
| " sum(leaf_unused),\n" /* 8 */ |
| " sum(int_unused),\n" /* 9 */ |
| " sum(ovfl_unused),\n" /* 10 */ |
| " max(depth),\n" /* 11 */ |
| " count(*),\n" /* 12 */ |
| " sum(int_entries)\n" /* 13 */ |
| " FROM temp.%s WHERE %s", |
| p->zSU, zWhere); |
| if( pStmt==0 ) return 1; |
| rc = sqlite3_step(pStmt); |
| if( rc==SQLITE_ROW ){ |
| analysisTitle(p, zTitle); |
| |
| nentry = sqlite3_column_int64(pStmt, 0); |
| payload = sqlite3_column_int64(pStmt, 1); |
| ovfl_payload = sqlite3_column_int64(pStmt, 2); |
| mx_payload = sqlite3_column_int64(pStmt, 3); |
| ovfl_cnt = sqlite3_column_int64(pStmt, 4); |
| leaf_pages = sqlite3_column_int64(pStmt, 5); |
| int_pages = sqlite3_column_int64(pStmt, 6); |
| ovfl_pages = sqlite3_column_int64(pStmt, 7); |
| leaf_unused = sqlite3_column_int64(pStmt, 8); |
| int_unused = sqlite3_column_int64(pStmt, 9); |
| ovfl_unused = sqlite3_column_int64(pStmt, 10); |
| depth = sqlite3_column_int64(pStmt, 11); |
| cnt = sqlite3_column_int64(pStmt, 12); |
| int_cell = sqlite3_column_int64(pStmt, 13); |
| rc = SQLITE_DONE; |
| |
| total_pages = leaf_pages + int_pages + ovfl_pages; |
| analysisLine(p, "Percentage of total database", "%.3g%%\n", |
| (total_pages*100.0)/(double)nPage); |
| analysisLine(p, "Number of entries", "%lld\n", nentry); |
| storage = total_pages*pgsz; |
| analysisLine(p, "Bytes of storage consumed", "%lld\n", storage); |
| analysisLine(p, "Bytes of payload", "%-11lld ", payload); |
| analysisPercent(p, payload*100.0/(double)storage); |
| if( ovfl_cnt>0 ){ |
| analysisLine(p, "Bytes of payload in overflow","%-11lld ",ovfl_payload); |
| analysisPercent(p, ovfl_payload*100.0/(double)payload); |
| } |
| total_unused = leaf_unused + int_unused + ovfl_unused; |
| total_meta = storage - payload - total_unused; |
| analysisLine(p, "Bytes of metadata","%-11lld ", total_meta); |
| analysisPercent(p, total_meta*100.0/(double)storage); |
| if( cnt==1 ){ |
| analysisLine(p, "B-tree depth", "%lld\n", depth); |
| if( int_cell>1 ){ |
| analysisLine(p, "Average fanout", "%.1f\n", |
| (double)(int_cell+int_pages)/(double)int_pages); |
| } |
| } |
| if( nentry>0 ){ |
| analysisLine(p, "Average payload per entry", "%.1f\n", |
| (double)payload/(double)nentry); |
| analysisLine(p, "Average unused bytes per entry", "%.1f\n", |
| (double)total_unused/(double)nentry); |
| analysisLine(p, "Average metadata per entry", "%.1f\n", |
| (double)total_meta/(double)nentry); |
| } |
| analysisLine(p, "Maximum single-entry payload", "%lld\n", mx_payload); |
| if( nentry>0 ){ |
| analysisLine(p, "Entries that use overflow", "%-11lld ", ovfl_cnt); |
| analysisPercent(p, ovfl_cnt*100.0/(double)nentry); |
| } |
| if( int_pages>0 ){ |
| analysisLine(p, "Index pages used", "%lld\n", int_pages); |
| } |
| analysisLine(p, "Primary pages used", "%lld\n", leaf_pages); |
| if( ovfl_cnt ){ |
| analysisLine(p, "Overflow pages used", "%lld\n", ovfl_pages); |
| } |
| analysisLine(p, "Total pages used", "%lld\n", total_pages); |
| if( int_pages>0 ){ |
| analysisLine(p, "Unused bytes on index pages", "%lld\n", int_unused); |
| } |
| analysisLine(p, "Unused bytes on primary pages", "%lld\n", leaf_unused); |
| if( ovfl_cnt ){ |
| analysisLine(p, "Unused bytes on overflow pages", "%lld\n", ovfl_unused); |
| } |
| analysisLine(p, "Unused bytes on all pages", "%-11lld ", total_unused); |
| analysisPercent(p, total_unused*100.0/(double)storage); |
| } |
| return analysisStmtFinish(p, rc, pStmt); |
| } |
| |
| /* |
| ** SQL Function: analyze(SCHEMA) |
| ** |
| ** Analyze the database schema named in the argument. Return text |
| ** containing the analysis. |
| */ |
| static void analyzeFunc( |
| sqlite3_context *context, |
| int argc, |
| sqlite3_value **argv |
| ){ |
| int rc; |
| sqlite3_stmt *pStmt; |
| int n; |
| sqlite3_int64 i64; |
| sqlite3_int64 pgsz; |
| sqlite3_int64 nPage; |
| sqlite3_int64 nPageInUse; |
| sqlite3_int64 nFreeList; |
| sqlite3_int64 nIndex; |
| sqlite3_int64 nWORowid; |
| Analysis s; |
| sqlite3_uint64 r[2]; |
| |
| memset(&s, 0, sizeof(s)); |
| s.db = sqlite3_context_db_handle(context); |
| s.context = context; |
| s.pOut = sqlite3_str_new(0); |
| if( sqlite3_str_errcode(s.pOut) ){ |
| analysisError(&s, 0); |
| return; |
| } |
| s.zSchema = (const char*)sqlite3_value_text(argv[0]); |
| if( s.zSchema==0 ){ |
| s.zSchema = "main"; |
| }else if( sqlite3_strlike("temp",s.zSchema,0)==0 ){ |
| analysisReset(&s); |
| sqlite3_result_text(context, "cannot analyze \"temp\"",-1,SQLITE_STATIC); |
| return; |
| } |
| i64 = 0; |
| rc = analysisSqlInt(&s,&i64,"SELECT 1 FROM pragma_database_list" |
| " WHERE name=%Q COLLATE nocase",s.zSchema); |
| if( rc || i64==0 ){ |
| analysisReset(&s); |
| sqlite3_result_text(context,"no such database",-1,SQLITE_STATIC); |
| return; |
| } |
| sqlite3_randomness(sizeof(r), &r); |
| s.zSU = sqlite3_mprintf("analysis%016llx%016llx", r[0], r[1]); |
| if( s.zSU==0 ){ analysisError(&s, 0); return; } |
| |
| /* The s.zSU table contains the data used for the analysis. |
| ** The table name contains 128-bits of randomness to avoid |
| ** collisions with preexisting tables in temp. |
| */ |
| rc = analysisSql(&s, |
| "CREATE TABLE temp.%s(\n" |
| " name text, -- A table or index\n" |
| " tblname text, -- Table that owns name\n" |
| " is_index boolean, -- TRUE if it is an index\n" |
| " is_without_rowid boolean, -- TRUE if WITHOUT ROWID table\n" |
| " nentry int, -- Number of entries in the BTree\n" |
| " leaf_entries int, -- Number of leaf entries\n" |
| " depth int, -- Depth of the b-tree\n" |
| " payload int, -- Total data stored in this table/index\n" |
| " ovfl_payload int, -- Total data stored on overflow pages\n" |
| " ovfl_cnt int, -- Number of entries that use overflow\n" |
| " mx_payload int, -- Maximum payload size\n" |
| " int_pages int, -- Interior pages used\n" |
| " leaf_pages int, -- Leaf pages used\n" |
| " ovfl_pages int, -- Overflow pages used\n" |
| " int_unused int, -- Unused bytes on interior pages\n" |
| " leaf_unused int, -- Unused bytes on primary pages\n" |
| " ovfl_unused int, -- Unused bytes on overflow pages\n" |
| " int_entries int -- Btree cells on internal pages\n" |
| ");", |
| s.zSU |
| ); |
| if( rc ) return; |
| |
| /* Populate the s.zSU table |
| */ |
| rc = analysisSql(&s, |
| "WITH\n" |
| " allidx(idxname) AS (\n" |
| " SELECT name FROM \"%w\".sqlite_schema WHERE type='index'\n" |
| " ),\n" |
| " allobj(allname,tblname,isidx,isworowid) AS (\n" |
| " SELECT 'sqlite_schema',\n" |
| " 'sqlite_schema',\n" |
| " 0,\n" |
| " 0\n" |
| " UNION ALL\n" |
| " SELECT name,\n" |
| " tbl_name,\n" |
| " type='index',\n" |
| " EXISTS(SELECT 1\n" |
| " FROM pragma_index_list(sqlite_schema.name,%Q)\n" |
| " WHERE pragma_index_list.origin='pk'\n" |
| " AND pragma_index_list.name NOT IN allidx)\n" |
| " FROM \"%w\".sqlite_schema\n" |
| " )\n" |
| "INSERT INTO temp.%s\n" |
| " SELECT\n" |
| " allname,\n" |
| " tblname,\n" |
| " isidx,\n" |
| " isworowid,\n" |
| " sum(ncell),\n" |
| " sum((pagetype='leaf')*ncell),\n" |
| " max((length(if(path GLOB '*+*','',path))+3)/4),\n" |
| " sum(payload),\n" |
| " sum((pagetype='overflow')*payload),\n" |
| " sum(path GLOB '*+000000'),\n" |
| " max(mx_payload),\n" |
| " sum(pagetype='internal'),\n" |
| " sum(pagetype='leaf'),\n" |
| " sum(pagetype='overflow'),\n" |
| " sum((pagetype='internal')*unused),\n" |
| " sum((pagetype='leaf')*unused),\n" |
| " sum((pagetype='overflow')*unused),\n" |
| " sum(if(pagetype='internal',ncell))\n" |
| " FROM allobj CROSS JOIN dbstat(%Q) \n" |
| " WHERE dbstat.name=allobj.allname\n" |
| " GROUP BY allname;\n", |
| s.zSchema, /* %w.sqlite_schema -- in allidx */ |
| s.zSchema, /* pragma_index_list(...,%Q) */ |
| s.zSchema, /* %w.sqlite_schema */ |
| s.zSU, /* INTO temp.%s */ |
| s.zSchema /* JOIN dbstat(%Q) */ |
| ); |
| if( rc ) return; |
| |
| nPage = 0; |
| rc = analysisSqlInt(&s, &nPage, "PRAGMA \"%w\".page_count", s.zSchema); |
| if( rc ) return; |
| if( nPage<=0 ){ |
| /* Very brief reply for an empty database */ |
| analysisReset(&s); |
| sqlite3_result_text(context, "empty database", -1, SQLITE_STATIC); |
| return; |
| } |
| |
| /* Begin generating the report */ |
| analysisTitle(&s, "Database storage utilization report"); |
| pgsz = 0; |
| rc = analysisSqlInt(&s, &pgsz, "PRAGMA \"%w\".page_size", s.zSchema); |
| if( rc ) return; |
| analysisLine(&s, "Page size in bytes","%lld\n",pgsz); |
| analysisLine(&s, "Pages in the database", "%lld\n", nPage); |
| |
| nPageInUse = 0; |
| rc = analysisSqlInt(&s, &nPageInUse, |
| "SELECT sum(leaf_pages+int_pages+ovfl_pages) FROM temp.%s", s.zSU); |
| if( rc ) return; |
| analysisLine(&s, "Pages that store data", "%-11lld ", nPageInUse); |
| analysisPercent(&s, (nPageInUse*100.0)/(double)nPage); |
| |
| nFreeList = 0; |
| rc = analysisSqlInt(&s, &nFreeList, "PRAGMA \"%w\".freelist_count",s.zSchema); |
| if( rc ) return; |
| analysisLine(&s, "Pages on the freelist", "%-11lld ", nFreeList); |
| analysisPercent(&s, (nFreeList*100.0)/(double)nPage); |
| |
| i64 = 0; |
| rc = analysisSqlInt(&s, &i64, "PRAGMA \"%w\".auto_vacuum", s.zSchema); |
| if( rc ) return; |
| if( i64==0 || nPage<=1 ){ |
| i64 = 0; |
| }else{ |
| double rPtrsPerPage = pgsz/5; |
| double rAvPage = (nPage-1.0)/(rPtrsPerPage+1.0); |
| i64 = (sqlite3_int64)ceil(rAvPage); |
| } |
| analysisLine(&s, "Pages of auto-vacuum overhead", "%-11lld ", i64); |
| analysisPercent(&s, (i64*100.0)/(double)nPage); |
| |
| i64 = 0; |
| rc = analysisSqlInt(&s, &i64, |
| "SELECT count(*)+1 FROM \"%w\".sqlite_schema WHERE type='table'", |
| s.zSchema); |
| if( rc ) return; |
| analysisLine(&s, "Number of tables", "%lld\n", i64); |
| nWORowid = 0; |
| rc = analysisSqlInt(&s, &nWORowid, |
| "SELECT count(*) FROM \"%w\".pragma_table_list WHERE wr", |
| s.zSchema); |
| if( rc ) return; |
| if( nWORowid>0 ){ |
| analysisLine(&s, "Number of WITHOUT ROWID tables", "%lld\n", nWORowid); |
| analysisLine(&s, "Number of rowid tables", "%lld\n", i64 - nWORowid); |
| } |
| nIndex = 0; |
| rc = analysisSqlInt(&s, &nIndex, |
| "SELECT count(*) FROM \"%w\".sqlite_schema WHERE type='index'", |
| s.zSchema); |
| if( rc ) return; |
| analysisLine(&s, "Number of indexes", "%lld\n", nIndex); |
| i64 = 0; |
| rc = analysisSqlInt(&s, &i64, |
| "SELECT count(*) FROM \"%w\".sqlite_schema" |
| " WHERE name GLOB 'sqlite_autoindex_*' AND type='index'", |
| s.zSchema); |
| if( rc ) return; |
| analysisLine(&s, "Number of defined indexes", "%lld\n", nIndex - i64); |
| analysisLine(&s, "Number of implied indexes", "%lld\n", i64); |
| analysisLine(&s, "Size of the database in bytes", "%lld\n", pgsz*nPage); |
| i64 = 0; |
| rc = analysisSqlInt(&s, &i64, |
| "SELECT sum(payload) FROM temp.%s" |
| " WHERE NOT is_index AND name NOT LIKE 'sqlite_schema'", |
| s.zSU); |
| if( rc ) return; |
| analysisLine(&s, "Bytes of payload", "%-11lld ", i64); |
| analysisPercent(&s, i64*100.0/(double)(pgsz*nPage)); |
| |
| analysisTitle(&s, "Page counts for all tables with their indexes"); |
| pStmt = analysisPrepare(&s, |
| "SELECT upper(tblname),\n" |
| " sum(int_pages+leaf_pages+ovfl_pages)\n" |
| " FROM temp.%s\n" |
| " GROUP BY 1\n" |
| " ORDER BY 2 DESC, 1;", |
| s.zSU); |
| if( pStmt==0 ) return; |
| while( (rc = sqlite3_step(pStmt))==SQLITE_ROW ){ |
| sqlite3_int64 nn = sqlite3_column_int64(pStmt,1); |
| analysisLine(&s, (const char*)sqlite3_column_text(pStmt,0), "%-11lld ", nn); |
| analysisPercent(&s, (nn*100.0)/(double)nPage); |
| } |
| if( analysisStmtFinish(&s, rc, pStmt) ) return; |
| |
| analysisTitle(&s, "Page counts for all tables and indexes separately"); |
| pStmt = analysisPrepare(&s, |
| "SELECT upper(name),\n" |
| " sum(int_pages+leaf_pages+ovfl_pages)\n" |
| " FROM temp.%s\n" |
| " GROUP BY 1\n" |
| " ORDER BY 2 DESC, 1;", |
| s.zSU); |
| if( pStmt==0 ) return; |
| while( (rc = sqlite3_step(pStmt))==SQLITE_ROW ){ |
| sqlite3_int64 nn = sqlite3_column_int64(pStmt,1); |
| analysisLine(&s, (const char*)sqlite3_column_text(pStmt,0), "%-11lld ", nn); |
| analysisPercent(&s, (nn*100.0)/(double)nPage); |
| } |
| if( analysisStmtFinish(&s, rc, pStmt) ) return; |
| |
| rc = analysisSubreport(&s, "All tables and indexes", "1", pgsz, nPage); |
| if( rc ) return; |
| rc = analysisSubreport(&s, "All tables", "NOT is_index", pgsz, nPage); |
| if( rc ) return; |
| if( nWORowid>0 ){ |
| rc = analysisSubreport(&s, "All WITHOUT ROWID tables", "is_without_rowid", |
| pgsz, nPage); |
| if( rc ) return; |
| rc = analysisSubreport(&s, "All rowid tables", |
| "NOT is_without_rowid AND NOT is_index", |
| pgsz, nPage); |
| if( rc ) return; |
| } |
| rc = analysisSubreport(&s, "All indexes", "is_index", pgsz, nPage); |
| if( rc ) return; |
| |
| pStmt = analysisPrepare(&s, |
| "SELECT upper(tblname), tblname, sum(is_index) FROM temp.%s" |
| " GROUP BY 1 ORDER BY 1", |
| s.zSU); |
| if( pStmt==0 ) return; |
| while( (rc = sqlite3_step(pStmt))==SQLITE_ROW ){ |
| const char *zUpper = (const char*)sqlite3_column_text(pStmt, 0); |
| const char *zName = (const char*)sqlite3_column_text(pStmt, 1); |
| int nSubIndex = sqlite3_column_int(pStmt, 2); |
| if( nSubIndex==0 ){ |
| char *zTitle = sqlite3_mprintf("Table %s", zUpper); |
| char *zWhere = sqlite3_mprintf("name=%Q", zName); |
| rc = analysisSubreport(&s, zTitle, zWhere, pgsz, nPage); |
| sqlite3_free(zTitle); |
| sqlite3_free(zWhere); |
| if( rc ) break; |
| }else{ |
| sqlite3_stmt *pS2; |
| char *zTitle = sqlite3_mprintf("Table %s and all its indexes", zUpper); |
| char *zWhere = sqlite3_mprintf("tblname=%Q", zName); |
| rc = analysisSubreport(&s, zTitle, zWhere, pgsz, nPage); |
| sqlite3_free(zTitle); |
| sqlite3_free(zWhere); |
| if( rc ) break; |
| zTitle = sqlite3_mprintf("Table %s w/o any indexes", zUpper); |
| zWhere = sqlite3_mprintf("name=%Q", zName); |
| rc = analysisSubreport(&s, zTitle, zWhere, pgsz, nPage); |
| sqlite3_free(zTitle); |
| sqlite3_free(zWhere); |
| if( rc ) break; |
| if( nSubIndex>1 ){ |
| zTitle = sqlite3_mprintf("All indexes of table %s", zUpper); |
| zWhere = sqlite3_mprintf("tblname=%Q AND is_index", zName); |
| rc = analysisSubreport(&s, zTitle, zWhere, pgsz, nPage); |
| sqlite3_free(zTitle); |
| sqlite3_free(zWhere); |
| if( rc ) break; |
| } |
| pS2 = analysisPrepare(&s, |
| "SELECT name, upper(name) FROM temp.%s" |
| " WHERE is_index AND tblname=%Q", |
| s.zSU, zName); |
| if( pS2==0 ){ |
| rc = SQLITE_NOMEM; |
| break; |
| } |
| while( (rc = sqlite3_step(pS2))==SQLITE_ROW ){ |
| const char *zU = (const char*)sqlite3_column_text(pS2, 1); |
| const char *zN = (const char*)sqlite3_column_text(pS2, 0); |
| zTitle = sqlite3_mprintf("Index %s", zU); |
| zWhere = sqlite3_mprintf("name=%Q", zN); |
| rc = analysisSubreport(&s, zTitle, zWhere, pgsz, nPage); |
| sqlite3_free(zTitle); |
| sqlite3_free(zWhere); |
| if( rc ) break; |
| } |
| rc = analysisStmtFinish(&s, rc, pS2); |
| if( rc ) break; |
| } |
| } |
| if( analysisStmtFinish(&s, rc, pStmt) ) return; |
| |
| /* Append SQL statements that will recreate the raw data used for |
| ** the analysis. |
| */ |
| analysisTitle(&s, "Raw data used to generate this report"); |
| sqlite3_str_appendf(s.pOut, |
| "The following SQL will create a table named \"space_used\" which\n" |
| "contains most of the information used to generate the report above.\n" |
| "*/\n" |
| ); |
| sqlite3_str_appendf(s.pOut, |
| "BEGIN;\n" |
| "CREATE TABLE space_used(\n" |
| " name text, -- A table or index\n" /* 0 */ |
| " tblname text, -- Table that owns name\n" /* 1 */ |
| " is_index boolean, -- TRUE if it is an index\n" /* 2 */ |
| " is_without_rowid boolean, -- TRUE if WITHOUT ROWID table\n" /* 3 */ |
| " nentry int, -- Number of entries in the BTree\n" /* 4 */ |
| " leaf_entries int, -- Number of leaf entries\n" /* 5 */ |
| " depth int, -- Depth of the b-tree\n" /* 6 */ |
| " payload int, -- Total data in this table/index\n" /* 7 */ |
| " ovfl_payload int, -- Total data on overflow pages\n" /* 8 */ |
| " ovfl_cnt int, -- Entries that use overflow\n" /* 9 */ |
| " mx_payload int, -- Maximum payload size\n" /* 10 */ |
| " int_pages int, -- Interior pages used\n" /* 11 */ |
| " leaf_pages int, -- Leaf pages used\n" /* 12 */ |
| " ovfl_pages int, -- Overflow pages used\n" /* 13 */ |
| " int_unused int, -- Unused bytes on interior pages\n" /* 14 */ |
| " leaf_unused int, -- Unused bytes on primary pages\n" /* 15 */ |
| " ovfl_unused int, -- Unused bytes on overflow pages\n" /* 16 */ |
| " int_entries int -- B-tree entries on internal pages\n"/* 17 */ |
| ");\n" |
| "INSERT INTO space_used VALUES\n" |
| ); |
| pStmt = analysisPrepare(&s, |
| "SELECT quote(name), quote(tblname),\n" /* 0..1 */ |
| " is_index, is_without_rowid, nentry, leaf_entries,\n" /* 2..5 */ |
| " depth, payload, ovfl_payload, ovfl_cnt, mx_payload,\n" /* 6..10 */ |
| " int_pages, leaf_pages, ovfl_pages, int_unused,\n" /* 11..14 */ |
| " leaf_unused, ovfl_unused, int_entries\n" /* 15..17 */ |
| " FROM temp.%s;", |
| s.zSU); |
| if( pStmt==0 ) return; |
| n = 0; |
| while( (rc = sqlite3_step(pStmt))==SQLITE_ROW ){ |
| if( n++ ) sqlite3_str_appendf(s.pOut,",\n"); |
| sqlite3_str_appendf(s.pOut, |
| " (%s,%s,%lld,%lld,%lld,%lld,%lld,%lld,%lld," |
| "%lld,%lld,%lld,%lld,%lld,%lld,%lld,%lld,%lld)", |
| sqlite3_column_text(pStmt, 0), |
| sqlite3_column_text(pStmt, 1), |
| sqlite3_column_int64(pStmt, 2), |
| sqlite3_column_int64(pStmt, 3), |
| sqlite3_column_int64(pStmt, 4), |
| sqlite3_column_int64(pStmt, 5), |
| sqlite3_column_int64(pStmt, 6), |
| sqlite3_column_int64(pStmt, 7), |
| sqlite3_column_int64(pStmt, 8), |
| sqlite3_column_int64(pStmt, 9), |
| sqlite3_column_int64(pStmt, 10), |
| sqlite3_column_int64(pStmt, 11), |
| sqlite3_column_int64(pStmt, 12), |
| sqlite3_column_int64(pStmt, 13), |
| sqlite3_column_int64(pStmt, 14), |
| sqlite3_column_int64(pStmt, 15), |
| sqlite3_column_int64(pStmt, 16), |
| sqlite3_column_int64(pStmt, 17)); |
| } |
| if( rc!=SQLITE_DONE ){ |
| analysisError(&s, "SQL run-time error: %s\nSQL: %s", |
| sqlite3_errmsg(s.db), sqlite3_sql(pStmt)); |
| sqlite3_finalize(pStmt); |
| return; |
| } |
| sqlite3_str_appendf(s.pOut,";\nCOMMIT;"); |
| sqlite3_finalize(pStmt); |
| |
| if( sqlite3_str_length(s.pOut) ){ |
| sqlite3_result_text(context, sqlite3_str_finish(s.pOut), -1, |
| sqlite3_free); |
| s.pOut = 0; |
| } |
| analysisReset(&s); |
| } |
| |
| |
| #ifdef _WIN32 |
| __declspec(dllexport) |
| #endif |
| int sqlite3_analyze_init( |
| sqlite3 *db, |
| char **pzErrMsg, |
| const sqlite3_api_routines *pApi |
| ){ |
| int rc = SQLITE_OK; |
| SQLITE_EXTENSION_INIT2(pApi); |
| (void)pzErrMsg; /* Unused parameter */ |
| rc = sqlite3_create_function(db, "analyze", 1, |
| SQLITE_UTF8|SQLITE_INNOCUOUS, |
| 0, analyzeFunc, 0, 0); |
| return rc; |
| } |
