/*
** Copyright (c) 2007 D. Richard Hipp
**
** This program is free software; you can redistribute it and/or
** modify it under the terms of the Simplified BSD License (also
** known as the "2-Clause License" or "FreeBSD License".)
** This program is distributed in the hope that it will be useful,
** but without any warranty; without even the implied warranty of
** merchantability or fitness for a particular purpose.
**
** Author contact information:
** drh@hwaci.com
** http://www.hwaci.com/drh/
**
*******************************************************************************
**
** This file contains code to implement the stat web page
**
*/
#include "VERSION.h"
#include "config.h"
#include <string.h>
#include "stat.h"
/*
** For a sufficiently large integer, provide an alternative
** representation as MB or GB or TB.
*/
void bigSizeName(int nOut, char *zOut, sqlite3_int64 v){
if( v<100000 ){
sqlite3_snprintf(nOut, zOut, "%,lld bytes", v);
}else if( v<1000000000 ){
sqlite3_snprintf(nOut, zOut, "%,lld bytes (%.1fMB)",
v, (double)v/1000000.0);
}else{
sqlite3_snprintf(nOut, zOut, "%,lld bytes (%.1fGB)",
v, (double)v/1000000000.0);
}
}
/*
** Return the approximate size as KB, MB, GB, or TB.
*/
void approxSizeName(int nOut, char *zOut, sqlite3_int64 v){
if( v<1000 ){
sqlite3_snprintf(nOut, zOut, "%,lld bytes", v);
}else if( v<1000000 ){
sqlite3_snprintf(nOut, zOut, "%.1fKB", (double)v/1000.0);
}else if( v<1000000000 ){
sqlite3_snprintf(nOut, zOut, "%.1fMB", (double)v/1000000.0);
}else{
sqlite3_snprintf(nOut, zOut, "%.1fGB", (double)v/1000000000.0);
}
}
/*
** Generate stats for the email notification subsystem.
*/
void stats_for_email(void){
const char *zDest = db_get("email-send-method",0);
int nSub, nASub, nPend, nDPend;
const char *zDir, *zDb, *zCmd, *zRelay;
int iCutoff;
double rDigest;
@ <tr><th>Outgoing Email:</th><td>
if( fossil_strcmp(zDest,"pipe")==0
&& (zCmd = db_get("email-send-command",0))!=0
){
@ Piped to command "%h(zCmd)"
}else
if( fossil_strcmp(zDest,"db")==0
&& (zDb = db_get("email-send-db",0))!=0
){
sqlite3 *db;
sqlite3_stmt *pStmt;
int rc;
@ Queued to database "%h(zDb)"
g.dbIgnoreErrors++;
rc = sqlite3_open(zDb, &db);
if( rc==SQLITE_OK ){
rc = sqlite3_prepare_v2(db, "SELECT count(*) FROM email",-1,&pStmt,0);
if( rc==SQLITE_OK && sqlite3_step(pStmt)==SQLITE_ROW ){
@ (%,d(sqlite3_column_int(pStmt,0)) messages,
@ %,d(file_size(zDb,ExtFILE)) bytes)
}
sqlite3_finalize(pStmt);
}
g.dbIgnoreErrors--;
if( rc ){
@ ← cannot access database!
}
sqlite3_close(db);
}else
if( fossil_strcmp(zDest,"dir")==0
&& (zDir = db_get("email-send-dir",0))!=0
){
@ Written to files in "%h(zDir)"
@ (%,d(file_directory_size(zDir,0,1)) messages)
}else
if( fossil_strcmp(zDest,"relay")==0
&& (zRelay = db_get("email-send-relayhost",0))!=0
){
@ Relay to %h(zRelay) using SMTP
}
else{
@ Off
}
@ </td></tr>
nPend = db_int(0,"SELECT count(*) FROM pending_alert WHERE NOT sentSep");
nDPend = db_int(0,"SELECT count(*) FROM pending_alert"
" WHERE NOT sentDigest");
@ <tr><th>Pending Alerts:</th><td>
@ %,d(nPend) normal, %,d(nDPend) digest
@ </td></tr>
if( g.perm.Admin ){
@ <tr><th><a href="%R/subscribers">Subscribers:</a></th><td>
}else{
@ <tr><th>Subscribers:</th><td>
}
nSub = db_int(0, "SELECT count(*) FROM subscriber");
iCutoff = db_get_int("email-renew-cutoff",0);
nASub = db_int(0, "SELECT count(*) FROM subscriber WHERE sverified"
" AND NOT sdonotcall AND octet_length(ssub)>1"
" AND lastContact>=%d;", iCutoff);
@ %,d(nASub) active, %,d(nSub) total
@ </td></tr>
rDigest = db_double(-1.0, "SELECT (julianday('now') - value)*24.0"
" FROM config WHERE name='email-last-digest'");
if( rDigest>0.0 ){
@ <tr><th>Last Digest:</th><td>Approximately \
if( rDigest>48.0 ){
@ %.1f(rDigest/24.0) days ago</td>
}else{
@ %.1f(rDigest) hours ago</td>
}
}
}
/*
** WEBPAGE: stat
**
** Show statistics and global information about the repository.
*/
void stat_page(void){
i64 t, fsize;
int n, m;
int szMax, szAvg;
int brief;
const char *p;
char *z;
int Y, M, D;
login_check_credentials();
if( !g.perm.Read ){ login_needed(g.anon.Read); return; }
brief = P("brief")!=0;
style_header("Repository Statistics");
style_adunit_config(ADUNIT_RIGHT_OK);
if( g.perm.Admin ){
style_submenu_element("URLs", "urllist");
style_submenu_element("Schema", "repo_schema");
style_submenu_element("Web-Cache", "cachestat");
}
style_submenu_element("Activity Reports", "reports");
style_submenu_element("Hash Collisions", "hash-collisions");
style_submenu_element("Artifacts", "bloblist");
if( sqlite3_compileoption_used("ENABLE_DBSTAT_VTAB") ){
style_submenu_element("Table Sizes", "repo-tabsize");
}
if( g.perm.Admin || g.perm.Setup || db_get_boolean("test_env_enable",0) ){
style_submenu_element("Environment", "test_env");
}
@ <table class="label-value">
fsize = file_size(g.zRepositoryName, ExtFILE);
@ <tr><th>Repository Size:</th><td>%,lld(fsize) bytes</td>
@ </td></tr>
if( !brief ){
@ <tr><th>Number Of Artifacts:</th><td>
n = db_int(0, "SELECT count(*) FROM blob WHERE content IS NOT NULL");
m = db_int(0, "SELECT count(*) FROM delta");
@ %,d(n) (%,d(n-m) fulltext and %,d(m) deltas)
if( g.perm.Write ){
@ <a href='%R/artifact_stats'>Details</a>
}
@ </td></tr>
if( n>0 ){
int a, b;
Stmt q;
@ <tr><th>Uncompressed Artifact Size:</th><td>
db_prepare(&q, "SELECT total(size), avg(size), max(size)"
" FROM blob WHERE content IS NOT NULL /*scan*/");
db_step(&q);
t = db_column_int64(&q, 0);
szAvg = db_column_int(&q, 1);
szMax = db_column_int(&q, 2);
db_finalize(&q);
@ %,d(szAvg) bytes average, %,d(szMax) bytes max, %,lld(t) total
@ </td></tr>
@ <tr><th>Compression Ratio:</th><td>
if( t/fsize < 5 ){
b = 10;
a = t/(fsize/10);
}else{
b = 1;
a = t/fsize;
}
@ %d(a):%d(b)
@ </td></tr>
}
if( db_table_exists("repository","unversioned") ){
Stmt q;
char zStored[100];
db_prepare(&q,
"SELECT count(*), sum(sz), sum(octet_length(content))"
" FROM unversioned"
" WHERE length(hash)>1"
);
if( db_step(&q)==SQLITE_ROW && (n = db_column_int(&q,0))>0 ){
sqlite3_int64 iStored, pct;
iStored = db_column_int64(&q,2);
pct = (iStored*100 + fsize/2)/fsize;
approxSizeName(sizeof(zStored), zStored, iStored);
@ <tr><th>Unversioned Files:</th><td>
@ %z(href("%R/uvlist"))%d(n) files</a>,
@ %s(zStored) compressed, %d(pct)%% of total repository space
@ </td></tr>
}
db_finalize(&q);
}
@ <tr><th>Number Of Check-ins:</th><td>
n = db_int(0, "SELECT count(*) FROM event WHERE type='ci' /*scan*/");
@ %,d(n)
@ </td></tr>
@ <tr><th>Number Of Files:</th><td>
n = db_int(0, "SELECT count(*) FROM filename /*scan*/");
@ %,d(n)
@ </td></tr>
@ <tr><th>Number Of Wiki Pages:</th><td>
n = db_int(0, "SELECT count(*) FROM tag /*scan*/"
" WHERE +tagname GLOB 'wiki-*'");
@ %,d(n)
@ </td></tr>
if( g.perm.Chat && db_table_exists("repository","chat") ){
sqlite3_int64 sz = 0;
char zSz[100];
n = db_int(0, "SELECT max(msgid) FROM chat");
m = db_int(0, "SELECT count(*) FROM chat WHERE mdel IS NOT TRUE");
sz = db_int64(0, "SELECT sum(coalesce(octet_length(xmsg),0)+"
"coalesce(octet_length(file),0)) FROM chat");
approxSizeName(sizeof(zSz), zSz, sz);
@ <tr><th>Number Of Chat Messages:</th>
@ <td>%,d(n) (%,d(m) still alive, %s(zSz) in size)</td></tr>
}
n = db_int(0, "SELECT count(*) FROM tag /*scan*/"
" WHERE +tagname GLOB 'tkt-*'");
if( n>0 ){
@ <tr><th>Number Of Tickets:</th><td>%,d(n)</td></tr>
}
if( db_table_exists("repository","forumpost") ){
n = db_int(0, "SELECT count(*) FROM forumpost/*scan*/");
if( n>0 ){
int nThread = db_int(0, "SELECT count(*) FROM forumpost"
" WHERE froot=fpid");
@ <tr><th>Number Of Forum Posts:</th>
@ <td>%,d(n) on %d(nThread) threads</td></tr>
}
}
}
@ <tr><th>Project Age:</th><td>
z = db_text(0, "SELECT timediff('now',(SELECT min(mtime) FROM event));");
sscanf(z, "+%d-%d-%d", &Y, &M, &D);
if( Y>0 ){
@ %d(Y) years, \
}
if( M>0 ){
@ %d(M) months, \
}
@ %d(D) days
@ </td></tr>
p = db_get("project-code", 0);
if( p ){
@ <tr><th>Project ID:</th>
@ <td>%h(p) %h(db_get("project-name",""))</td></tr>
}
p = db_get("parent-project-code", 0);
if( p ){
@ <tr><th>Parent Project ID:</th>
@ <td>%h(p) %h(db_get("parent-project-name",""))</td></tr>
}
/* @ <tr><th>Server ID:</th><td>%h(db_get("server-code",""))</td></tr> */
@ <tr><th>Fossil Version:</th><td>
@ %h(MANIFEST_DATE) %h(MANIFEST_VERSION)
@ (%h(RELEASE_VERSION)) <a href='version?verbose'>(details)</a>
@ </td></tr>
@ <tr><th>SQLite Version:</th><td>%.19s(sqlite3_sourceid())
@ [%.10s(&sqlite3_sourceid()[20])] (%s(sqlite3_libversion()))
@ <a href='version?verbose'>(details)</a></td></tr>
if( g.perm.Admin ){
const char *zCgi = P("SERVER_SOFTWARE");
@ <tr><th>OpenSSL Version:</th>
@ <td>%z(fossil_openssl_version())</td></tr>
if( zCgi ){
@ <tr><th>Web Server:</th><td>%s(zCgi)</td></tr>
}
}
if( g.eHashPolicy!=HPOLICY_AUTO ){
@ <tr><th>Schema Version:</th><td>%h(g.zAuxSchema),
@ %s(hpolicy_name())</td></tr>
}else{
@ <tr><th>Schema Version:</th><td>%h(g.zAuxSchema)</td></tr>
}
@ <tr><th>Repository Rebuilt:</th><td>
@ %h(db_get_mtime("rebuilt","%Y-%m-%d %H:%M:%S","Never"))
@ By Fossil %h(db_get("rebuilt","Unknown"))</td></tr>
@ <tr><th>Database Stats:</th><td>
@ %,d(db_int(0, "PRAGMA repository.page_count")) pages,
@ %d(db_int(0, "PRAGMA repository.page_size")) bytes/page,
@ %,d(db_int(0, "PRAGMA repository.freelist_count")) free pages,
@ %s(db_text(0, "PRAGMA repository.encoding")),
@ %s(db_text(0, "PRAGMA repository.journal_mode")) mode
@ </td></tr>
if( g.perm.Admin && g.zErrlog && g.zErrlog[0] ){
i64 szFile = file_size(g.zErrlog, ExtFILE);
if( szFile>=0 ){
@ <tr><th>Error Log:</th>
@ <td><a href='%R/errorlog'>%h(g.zErrlog)</a> (%,lld(szFile) bytes)
}
@ </td></tr>
}
if( g.perm.Admin ){
@ <tr><th>Backoffice:</th>
@ <td>Last run: %z(backoffice_last_run())</td></tr>
}
if( g.perm.Admin && alert_enabled() ){
stats_for_email();
}
@ </table>
style_finish_page();
}
/*
** COMMAND: dbstat
**
** Usage: %fossil dbstat OPTIONS
**
** Shows statistics and global information about the repository and/or
** verify the integrity of a repository.
**
** Options:
** -b|--brief Only show essential elements
** --db-check Run "PRAGMA quick_check" on the repository database
** --db-verify Run a full verification of the repository integrity.
** This involves decoding and reparsing all artifacts
** and can take significant time.
** --omit-version-info Omit the SQLite and Fossil version information
*/
void dbstat_cmd(void){
i64 t, fsize;
int n, m;
int szMax, szAvg;
int brief;
int omitVers; /* Omit Fossil and SQLite version information */
int dbCheck; /* True for the --db-check option */
const int colWidth = -19 /* printf alignment/width for left column */;
const char *p, *z;
brief = find_option("brief", "b",0)!=0;
omitVers = find_option("omit-version-info", 0, 0)!=0;
dbCheck = find_option("db-check",0,0)!=0;
if( find_option("db-verify",0,0)!=0 ) dbCheck = 2;
db_find_and_open_repository(0,0);
/* We should be done with options.. */
verify_all_options();
if( (z = db_get("project-name",0))!=0
|| (z = db_get("short-project-name",0))!=0
){
fossil_print("%*s%s\n", colWidth, "project-name:", z);
}
fsize = file_size(g.zRepositoryName, ExtFILE);
fossil_print( "%*s%,lld bytes\n", colWidth, "repository-size:", fsize);
if( !brief ){
n = db_int(0, "SELECT count(*) FROM blob WHERE content IS NOT NULL");
m = db_int(0, "SELECT count(*) FROM delta");
fossil_print("%*s%,d (stored as %,d full text and %,d deltas)\n",
colWidth, "artifact-count:",
n, n-m, m);
if( n>0 ){
int a, b;
Stmt q;
db_prepare(&q, "SELECT total(size), avg(size), max(size)"
" FROM blob WHERE size>0");
db_step(&q);
t = db_column_int64(&q, 0);
szAvg = db_column_int(&q, 1);
szMax = db_column_int(&q, 2);
db_finalize(&q);
fossil_print( "%*s%,d average, "
"%,d max, %,lld total\n",
colWidth, "artifact-sizes:",
szAvg, szMax, t);
if( t/fsize < 5 ){
b = 10;
fsize /= 10;
}else{
b = 1;
}
a = t/fsize;
fossil_print("%*s%d:%d\n", colWidth, "compression-ratio:", a, b);
}
n = db_int(0, "SELECT COUNT(*) FROM event e WHERE e.type='ci'");
fossil_print("%*s%,d\n", colWidth, "check-ins:", n);
n = db_int(0, "SELECT count(*) FROM filename /*scan*/");
fossil_print("%*s%,d across all branches\n", colWidth, "files:", n);
n = db_int(0, "SELECT count(*) FROM ("
"SELECT DISTINCT substr(tagname,6) "
"FROM tag JOIN tagxref USING('tagid')"
" WHERE tagname GLOB 'wiki-*'"
" AND TYPEOF(tagxref.value+0)='integer'"
")");
m = db_int(0, "SELECT COUNT(*) FROM event WHERE type='w'");
fossil_print("%*s%,d (%,d changes)\n", colWidth, "wiki-pages:", n, m);
n = db_int(0, "SELECT count(*) FROM tag /*scan*/"
" WHERE tagname GLOB 'tkt-*'");
m = db_int(0, "SELECT COUNT(*) FROM event WHERE type='t'");
fossil_print("%*s%,d (%,d changes)\n", colWidth, "tickets:", n, m);
n = db_int(0, "SELECT COUNT(*) FROM event WHERE type='e'");
fossil_print("%*s%,d\n", colWidth, "events:", n);
if( db_table_exists("repository","forumpost") ){
n = db_int(0, "SELECT count(*) FROM forumpost/*scan*/");
if( n>0 ){
int nThread = db_int(0, "SELECT count(*) FROM forumpost"
" WHERE froot=fpid");
fossil_print("%*s%,d (on %,d threads)\n", colWidth, "forum-posts:",
n, nThread);
}
}
n = db_int(0, "SELECT COUNT(*) FROM event WHERE type='g'");
fossil_print("%*s%,d\n", colWidth, "tag-changes:", n);
z = db_text(0, "SELECT datetime(mtime) || ' - about ' ||"
" CAST(julianday('now') - mtime AS INTEGER)"
" || ' days ago' FROM event "
" ORDER BY mtime DESC LIMIT 1");
fossil_print("%*s%s\n", colWidth, "latest-change:", z);
}
n = db_int(0, "SELECT julianday('now') - (SELECT min(mtime) FROM event)"
" + 0.99");
fossil_print("%*s%,d days or approximately %.2f years.\n",
colWidth, "project-age:", n, n/365.2425);
if( !brief ){
p = db_get("project-code", 0);
if( p ){
fossil_print("%*s%s\n", colWidth, "project-id:", p);
}
}
#if 0
/* Server-id is not useful information any more */
fossil_print("%*s%s\n", colWidth, "server-id:", db_get("server-code", 0));
#endif
fossil_print("%*s%s\n", colWidth, "schema-version:", g.zAuxSchema);
if( !omitVers ){
fossil_print("%*s%s %s [%s] (%s)\n",
colWidth, "fossil-version:",
MANIFEST_DATE, MANIFEST_VERSION, RELEASE_VERSION,
COMPILER_NAME);
fossil_print("%*s%.19s [%.10s] (%s)\n",
colWidth, "sqlite-version:",
sqlite3_sourceid(), &sqlite3_sourceid()[20],
sqlite3_libversion());
}
fossil_print("%*s%,d pages, %d bytes/pg, %,d free pages, "
"%s, %s mode\n",
colWidth, "database-stats:",
db_int(0, "PRAGMA repository.page_count"),
db_int(0, "PRAGMA repository.page_size"),
db_int(0, "PRAGMA repository.freelist_count"),
db_text(0, "PRAGMA repository.encoding"),
db_text(0, "PRAGMA repository.journal_mode"));
if( dbCheck ){
if( dbCheck<2 ){
char *zRes = db_text(0, "PRAGMA repository.quick_check(1)");
fossil_print("%*s%s\n", colWidth, "database-check:", zRes);
}else{
char *newArgv[3];
newArgv[0] = g.argv[0];
newArgv[1] = "test-integrity";
newArgv[2] = 0;
g.argv = newArgv;
g.argc = 2;
fossil_print("Full repository verification follows:\n");
test_integrity();
}
}
}
/*
** Return a string which is the public URL used to access this repository.
** Or return a NULL pointer if this repository does not have a public
** access URL.
**
** Algorithm:
**
** The public URL is given by the email-url property. But it is only
** returned if there have been one more more accesses (as recorded by
** "baseurl:URL" entries in the CONFIG table).
*/
const char *public_url(void){
const char *zUrl = db_get("email-url", 0);
if( zUrl==0 ) return 0;
if( !db_exists("SELECT 1 FROM config WHERE name='baseurl:%q'", zUrl) ){
return 0;
}
return zUrl;
}
/*
** WEBPAGE: urllist
**
** Show ways in which this repository has been accessed
*/
void urllist_page(void){
Stmt q;
int cnt;
int total = 0;
int showAll = P("all")!=0;
int nOmitted;
sqlite3_int64 iNow;
char *zPriorRepo = 0;
login_check_credentials();
if( !g.perm.Admin ){ login_needed(0); return; }
style_set_current_feature("stat");
style_header("URLs and Checkouts");
style_adunit_config(ADUNIT_RIGHT_OK);
style_submenu_element("Stat", "stat");
style_submenu_element("Schema", "repo_schema");
iNow = db_int64(0, "SELECT strftime('%%s','now')");
db_prepare(&q, "SELECT substr(name,9), datetime(mtime,'unixepoch'), mtime"
" FROM config WHERE name GLOB 'baseurl:*' ORDER BY 3 DESC");
cnt = 0;
nOmitted = 0;
while( db_step(&q)==SQLITE_ROW ){
if( cnt==0 ){
@ <div class="section">URLs used to access this repository</div>
@ <table border="0" width='100%%'>
}
if( !showAll && db_column_int64(&q,2)<(iNow - 3600*24*30) && cnt>8 ){
nOmitted++;
}else{
@ <tr><td width='100%%'>%h(db_column_text(&q,0))</td>
@ <td><nobr>%h(db_column_text(&q,1))</nobr></td></tr>
}
cnt++;
}
db_finalize(&q);
if( nOmitted ){
@ <tr><td><a href="urllist?all"><i>Show %d(nOmitted) more...</i></a>
}
if( cnt ){
@ </table>
total += cnt;
}
if( P("urlonly") ){
style_finish_page();
return;
}
db_prepare(&q, "SELECT substr(name,7), datetime(mtime,'unixepoch')"
" FROM config WHERE name GLOB 'ckout:*' ORDER BY 2 DESC");
cnt = 0;
while( db_step(&q)==SQLITE_ROW ){
const char *zPath = db_column_text(&q,0);
if( vfile_top_of_checkout(zPath) ){
if( cnt==0 ){
@ <div class="section">Checkouts</div>
@ <table border="0" width='100%%'>
}
@ <tr><td width='100%%'>%h(zPath)</td>
@ <td><nobr>%h(db_column_text(&q,1))</nobr></td></tr>
cnt++;
}
}
db_finalize(&q);
if( cnt ){
@ </table>
total += cnt;
}
cnt = 0;
db_prepare(&q,
"SELECT substr(name,10), datetime(mtime,'unixepoch')"
" FROM config WHERE name GLOB 'syncwith:*'"
"UNION ALL "
"SELECT substr(name,10), datetime(mtime,'unixepoch')"
" FROM config WHERE name GLOB 'syncfrom:*'"
"UNION ALL "
"SELECT substr(name,9), datetime(mtime,'unixepoch')"
" FROM config WHERE name GLOB 'gitpush:*'"
"GROUP BY 1 ORDER BY 2 DESC"
);
while( db_step(&q)==SQLITE_ROW ){
const char *zURL = db_column_text(&q,0);
UrlData x;
if( cnt==0 ){
@ <div class="section">Recently synced with these URLs</div>
@ <table border='0' width='100%%'>
}
memset(&x, 0, sizeof(x));
url_parse_local(zURL, URL_OMIT_USER, &x);
@ <tr><td width='100%%'><a href='%h(x.canonical)'>%h(x.canonical)</a>
@ <td><nobr>%h(db_column_text(&q,1))</nobr></td></tr>
cnt++;
url_unparse(&x);
}
db_finalize(&q);
if( cnt ){
@ </table>
total += cnt;
}
cnt = 0;
db_prepare(&q,
"SELECT"
" substr(name,6),"
" datetime(mtime,'unixepoch'),"
" value->>'type',"
" value->>'src'\n"
"FROM config\n"
"WHERE name GLOB 'link:*'\n"
"AND json_valid(value)\n"
"ORDER BY 4, 2 DESC"
);
while( db_step(&q)==SQLITE_ROW ){
const char *zUrl = db_column_text(&q, 0);
const char *zType = db_column_text(&q, 2);
const char *zSrc = db_column_text(&q, 3);
if( zUrl==0 || zSrc==0 ) continue;
if( cnt++==0 ){
@ <div class="section">Links from other repositories</div>
@ <table border='0' width='100%%'>
}
if( zPriorRepo==0 || strcmp(zPriorRepo,zSrc)!=0 ){
fossil_free(zPriorRepo);
zPriorRepo = fossil_strdup(zSrc);
@ <tr><td colspan="4">\
@ From <a href='%T(zSrc)'>%h(zSrc)</a>...</td></tr>
}
@ <tr><td> </td>
@ <td width='90%%'><a href='%h(zUrl)'>%h(zUrl)</a></td>
if( zType ){
@ <td> (%h(zType)) </td>
}else{
@ <td> </td>
}
@ <td><nobr>%h(db_column_text(&q,1))</nobr></td></tr>
}
db_finalize(&q);
fossil_free(zPriorRepo);
if( cnt ){
@ </table>
total += cnt;
}
cnt = 0;
db_prepare(&q,
"SELECT"
" value,"
" url_nouser(value),"
" substr(name,10),"
" datetime(mtime,'unixepoch')"
"FROM config\n"
"WHERE name GLOB 'sync-url:*'\n"
"ORDER BY 2"
);
while( db_step(&q)==SQLITE_ROW ){
const char *zUrl = db_column_text(&q, 0);
const char *zLink = db_column_text(&q, 1);
const char *zName = db_column_text(&q, 2);
if( cnt++==0 ){
@ <div class="section">Defined sync targets</div>
@ <table border='0' width='100%%'>
}
@ <tr><td>%h(zName)</td><td> </td>
@ <td width='95%%'><a href='%h(zLink)'>%h(zUrl)</a></td>
@ <td><nobr>%h(db_column_text(&q,3))</nobr></td></tr>
}
db_finalize(&q);
if( cnt ){
@ </table>
total += cnt;
}
if( total==0 ){
@ <p>No record of any URLs or checkouts</p>
}
style_finish_page();
}
/*
** WEBPAGE: repo_schema
**
** Show the repository schema
*/
void repo_schema_page(void){
Stmt q;
Blob sql;
const char *zArg = P("n");
login_check_credentials();
if( !g.perm.Admin ){ login_needed(0); return; }
if( zArg!=0
&& db_table_exists("repository",zArg)
&& cgi_csrf_safe(1)
){
if( P("analyze")!=0 ){
db_multi_exec("ANALYZE \"%w\"", zArg);
}else if( P("analyze200")!=0 ){
db_multi_exec("PRAGMA analysis_limit=200; ANALYZE \"%w\"", zArg);
}else if( P("deanalyze")!=0 ){
db_unprotect(PROTECT_ALL);
db_multi_exec("DELETE FROM repository.sqlite_stat1"
" WHERE tbl LIKE %Q", zArg);
db_protect_pop();
}
}
style_set_current_feature("stat");
style_header("Repository Schema");
style_adunit_config(ADUNIT_RIGHT_OK);
style_submenu_element("Stat", "stat");
style_submenu_element("URLs", "urllist");
if( sqlite3_compileoption_used("ENABLE_DBSTAT_VTAB") ){
style_submenu_element("Table Sizes", "repo-tabsize");
}
blob_init(&sql,
"SELECT sql FROM repository.sqlite_schema WHERE sql IS NOT NULL", -1);
if( zArg ){
style_submenu_element("All", "repo_schema");
blob_appendf(&sql, " AND (tbl_name=%Q OR name=%Q)", zArg, zArg);
}
blob_appendf(&sql, " ORDER BY tbl_name, type<>'table', name");
db_prepare(&q, "%s", blob_str(&sql)/*safe-for-%s*/);
blob_reset(&sql);
@ <pre>
while( db_step(&q)==SQLITE_ROW ){
@ %h(db_column_text(&q, 0));
}
@ </pre>
db_finalize(&q);
if( db_table_exists("repository","sqlite_stat1") ){
if( zArg ){
db_prepare(&q,
"SELECT tbl, idx, stat FROM repository.sqlite_stat1"
" WHERE tbl LIKE %Q OR idx LIKE %Q"
" ORDER BY tbl, idx", zArg, zArg);
@ <hr>
@ <pre>
while( db_step(&q)==SQLITE_ROW ){
const char *zTab = db_column_text(&q,0);
const char *zIdx = db_column_text(&q,1);
const char *zStat = db_column_text(&q,2);
@ INSERT INTO sqlite_stat1 VALUES('%h(zTab)','%h(zIdx)','%h(zStat)');
}
@ </pre>
db_finalize(&q);
}else{
style_submenu_element("Stat1","repo_stat1");
}
}
@ <hr><form method="POST">
@ <input type="submit" name="analyze" value="Run ANALYZE"><br />
@ <input type="submit" name="analyze200"\
@ value="Run ANALYZE with limit=200"><br />
@ <input type="submit" name="deanalyze" value="De-ANALYZE">
@ </form>
style_finish_page();
}
/*
** WEBPAGE: repo_stat1
**
** Show the sqlite_stat1 table for the repository schema
*/
void repo_stat1_page(void){
int bTabular;
login_check_credentials();
if( !g.perm.Admin ){ login_needed(0); return; }
bTabular = PB("tabular");
if( P("analyze")!=0 && cgi_csrf_safe(1) ){
db_multi_exec("ANALYZE");
}else if( P("analyze200")!=0 && cgi_csrf_safe(1) ){
db_multi_exec("PRAGMA analysis_limit=200; ANALYZE;");
}else if( P("deanalyze")!=0 && cgi_csrf_safe(1) ){
db_unprotect(PROTECT_ALL);
db_multi_exec("DELETE FROM repository.sqlite_stat1;");
db_protect_pop();
}
style_set_current_feature("stat");
style_header("Repository STAT1 Table");
style_adunit_config(ADUNIT_RIGHT_OK);
style_submenu_element("Stat", "stat");
style_submenu_element("Schema", "repo_schema");
style_submenu_checkbox("tabular", "Tabular", 0, 0);
if( db_table_exists("repository","sqlite_stat1") ){
Stmt q;
db_prepare(&q,
"SELECT tbl, idx, stat FROM repository.sqlite_stat1"
" ORDER BY tbl, idx");
if( bTabular ){
@ <table border="1" cellpadding="0" cellspacing="0">
@ <tr><th>Table<th>Index<th>Stat
}else{
@ <pre>
}
while( db_step(&q)==SQLITE_ROW ){
const char *zTab = db_column_text(&q,0);
const char *zIdx = db_column_text(&q,1);
const char *zStat = db_column_text(&q,2);
char *zUrl = href("%R/repo_schema?n=%t",zTab);
if( bTabular ){
@ <tr><td>%z(zUrl)%h(zTab)</a><td>%h(zIdx)<td>%h(zStat)
}else{
@ INSERT INTO sqlite_stat1 \
@ VALUES('%z(zUrl)%h(zTab)</a>','%h(zIdx)','%h(zStat)');
}
}
if( bTabular ){
@ </table>
}else{
@ </pre>
}
db_finalize(&q);
}
@ <p><form method="POST">
if( bTabular ){
@ <input type="hidden" name="tabular" value="1">
}
@ <input type="submit" name="analyze" value="Run ANALYZE"><br />
@ <input type="submit" name="analyze200"\
@ value="Run ANALYZE with limit=200"><br>
@ <input type="submit" name="deanalyze"\
@ value="De-ANALYZE">
@ </form>
style_finish_page();
}
/*
** WEBPAGE: repo-tabsize
**
** Show relative sizes of tables in the repository database.
*/
void repo_tabsize_page(void){
int nPageFree;
sqlite3_int64 fsize;
char zBuf[100];
login_check_credentials();
if( !g.perm.Read ){ login_needed(g.anon.Read); return; }
cgi_check_for_malice();
style_set_current_feature("stat");
style_header("Repository Table Sizes");
style_adunit_config(ADUNIT_RIGHT_OK);
style_submenu_element("Stat", "stat");
if( g.perm.Admin ){
style_submenu_element("Schema", "repo_schema");
}
db_multi_exec(
"CREATE TEMP TABLE trans(name TEXT PRIMARY KEY,tabname TEXT)WITHOUT ROWID;"
"INSERT INTO trans(name,tabname)"
" SELECT name, tbl_name FROM repository.sqlite_schema;"
"CREATE TEMP TABLE piechart(amt REAL, label TEXT);"
"INSERT INTO piechart(amt,label)"
" SELECT sum(pageno),"
" coalesce((SELECT tabname FROM trans WHERE trans.name=dbstat.name),name)"
" FROM dbstat('repository',TRUE)"
" GROUP BY 2 ORDER BY 2;"
);
nPageFree = db_int(0, "PRAGMA repository.freelist_count");
if( nPageFree>0 ){
db_multi_exec(
"INSERT INTO piechart(amt,label) VALUES(%d,'freelist')",
nPageFree
);
}
fsize = file_size(g.zRepositoryName, ExtFILE);
approxSizeName(sizeof(zBuf), zBuf, fsize);
@ <h2>Repository Size: %s(zBuf)</h2>
@ <center><svg width='800' height='500'>
piechart_render(800,500,PIE_OTHER|PIE_PERCENT);
@ </svg></center>
if( g.localOpen ){
db_multi_exec(
"DELETE FROM trans;"
"INSERT INTO trans(name,tabname)"
" SELECT name, tbl_name FROM localdb.sqlite_schema;"
"DELETE FROM piechart;"
"INSERT INTO piechart(amt,label)"
" SELECT sum(pageno), "
" coalesce((SELECT tabname FROM trans WHERE trans.name=dbstat.name),name)"
" FROM dbstat('localdb',TRUE)"
" GROUP BY 2 ORDER BY 2;"
);
nPageFree = db_int(0, "PRAGMA localdb.freelist_count");
if( nPageFree>0 ){
db_multi_exec(
"INSERT INTO piechart(amt,label) VALUES(%d,'freelist')",
nPageFree
);
}
fsize = file_size(g.zLocalDbName, ExtFILE);
approxSizeName(sizeof(zBuf), zBuf, fsize);
@ <h2>%h(file_tail(g.zLocalDbName)) Size: %s(zBuf)</h2>
@ <center><svg width='800' height='500'>
piechart_render(800,500,PIE_OTHER|PIE_PERCENT);
@ </svg></center>
}
style_finish_page();
}
/*
** Gather statistics on artifact types, counts, and sizes.
**
** Only populate the artstat.atype field if the bWithTypes parameter is true.
*/
void gather_artifact_stats(int bWithTypes){
static const char zSql[] =
@ CREATE TEMP TABLE artstat(
@ id INTEGER PRIMARY KEY, -- Corresponds to BLOB.RID
@ atype TEXT, -- 'data', 'manifest', 'tag', 'wiki', etc.
@ isDelta BOOLEAN, -- true if stored as a delta
@ szExp, -- expanded, uncompressed size
@ szCmpr -- size as stored on disk
@ );
@ INSERT INTO artstat(id,atype,isDelta,szExp,szCmpr)
@ SELECT blob.rid, NULL,
@ delta.rid IS NOT NULL,
@ size, octet_length(content)
@ FROM blob LEFT JOIN delta ON blob.rid=delta.rid
@ WHERE content IS NOT NULL;
;
static const char zSql2[] =
@ UPDATE artstat SET atype='file'
@ WHERE +id IN (SELECT fid FROM mlink);
@ UPDATE artstat SET atype='manifest'
@ WHERE id IN (SELECT objid FROM event WHERE type='ci') AND atype IS NULL;
@ UPDATE artstat SET atype='forum'
@ WHERE id IN (SELECT objid FROM event WHERE type='f') AND atype IS NULL;
@ UPDATE artstat SET atype='cluster'
@ WHERE atype IS NULL
@ AND id IN (SELECT rid FROM tagxref
@ WHERE tagid=(SELECT tagid FROM tag
@ WHERE tagname='cluster'));
@ UPDATE artstat SET atype='ticket'
@ WHERE atype IS NULL
@ AND id IN (SELECT rid FROM tagxref
@ WHERE tagid IN (SELECT tagid FROM tag
@ WHERE tagname GLOB 'tkt-*'));
@ UPDATE artstat SET atype='wiki'
@ WHERE atype IS NULL
@ AND id IN (SELECT rid FROM tagxref
@ WHERE tagid IN (SELECT tagid FROM tag
@ WHERE tagname GLOB 'wiki-*'));
@ UPDATE artstat SET atype='technote'
@ WHERE atype IS NULL
@ AND id IN (SELECT rid FROM tagxref
@ WHERE tagid IN (SELECT tagid FROM tag
@ WHERE tagname GLOB 'event-*'));
@ UPDATE artstat SET atype='attachment'
@ WHERE atype IS NULL
@ AND id IN (SELECT attachid FROM attachment UNION
@ SELECT blob.rid FROM attachment JOIN blob ON uuid=src);
@ UPDATE artstat SET atype='tag'
@ WHERE atype IS NULL
@ AND id IN (SELECT srcid FROM tagxref);
@ UPDATE artstat SET atype='tag'
@ WHERE atype IS NULL
@ AND id IN (SELECT objid FROM event WHERE type='g');
@ UPDATE artstat SET atype='unused' WHERE atype IS NULL;
;
db_multi_exec("%s", zSql/*safe-for-%s*/);
if( bWithTypes ){
db_multi_exec("%s", zSql2/*safe-for-%s*/);
}
}
/*
** Output text "the largest N artifacts". Make this text a hyperlink
** to bigbloblist if N is not too big.
*/
static void largest_n_artifacts(int N){
if( N>250 ){
@ (the largest %,d(N) artifacts)
}else{
@ (the <a href='%R/bigbloblist?n=%d(N)'>largest %d(N) artifacts</a>)
}
}
/*
** WEBPAGE: artifact_stats
**
** Show information about the sizes of artifacts in this repository
*/
void artifact_stats_page(void){
Stmt q;
int nTotal = 0; /* Total number of artifacts */
int nDelta = 0; /* Total number of deltas */
int nFull = 0; /* Total number of full-texts */
double avgCmpr = 0.0; /* Average size of an artifact after compression */
double avgExp = 0.0; /* Average size of an uncompressed artifact */
int mxCmpr = 0; /* Maximum compressed artifact size */
int mxExp = 0; /* Maximum uncompressed artifact size */
sqlite3_int64 sumCmpr = 0; /* Total size of all compressed artifacts */
sqlite3_int64 sumExp = 0; /* Total size of all expanded artifacts */
sqlite3_int64 sz1pct = 0; /* Space used by largest 1% */
sqlite3_int64 sz10pct = 0; /* Space used by largest 10% */
sqlite3_int64 sz25pct = 0; /* Space used by largest 25% */
sqlite3_int64 sz50pct = 0; /* Space used by largest 50% */
int n50pct = 0; /* Artifacts using the first 50% of space */
int n; /* Loop counter */
int medCmpr = 0; /* Median compressed artifact size */
int medExp = 0; /* Median expanded artifact size */
int med;
double r;
login_check_credentials();
/* These stats are expensive to compute. To disable them for
** user without check-in privileges, to prevent excessive usage by
** robots and random passers-by on the internet
*/
if( !g.perm.Write && !db_get_boolean("artifact_stats_enable",0) ){
login_needed(g.anon.Write);
return;
}
cgi_check_for_malice();
fossil_nice_default();
style_set_current_feature("stat");
style_header("Artifact Statistics");
style_submenu_element("Repository Stats", "stat");
style_submenu_element("Artifact List", "bloblist");
gather_artifact_stats(1);
db_prepare(&q,
"SELECT count(*), sum(isDelta), max(szCmpr),"
" max(szExp), sum(szCmpr), sum(szExp)"
" FROM artstat"
);
db_step(&q);
nTotal = db_column_int(&q,0);
nDelta = db_column_int(&q,1);
nFull = nTotal - nDelta;
mxCmpr = db_column_int(&q, 2);
mxExp = db_column_int(&q, 3);
sumCmpr = db_column_int64(&q, 4);
sumExp = db_column_int64(&q, 5);
db_finalize(&q);
if( nTotal==0 ){
@ No artifacts in this repository!
style_finish_page();
return;
}
avgCmpr = (double)sumCmpr/nTotal;
avgExp = (double)sumExp/nTotal;
db_prepare(&q, "SELECT szCmpr FROM artstat ORDER BY 1 DESC");
r = 0;
n = 0;
while( db_step(&q)==SQLITE_ROW ){
r += db_column_int(&q, 0);
if( n50pct==0 && r>=sumCmpr/2 ) n50pct = n;
if( n==(nTotal+99)/100 ) sz1pct = (sqlite3_int64)r;
if( n==(nTotal+9)/10 ) sz10pct = (sqlite3_int64)r;
if( n==(nTotal+4)/5 ) sz25pct = (sqlite3_int64)r;
if( n==(nTotal+1)/2 ){
sz50pct = (sqlite3_int64)r;
medCmpr = db_column_int(&q,0);
}
n++;
}
db_finalize(&q);
@ <h1>Overall Artifact Size Statistics:</h1>
@ <table class="label-value">
@ <tr><th>Number of artifacts:</th><td>%,d(nTotal)</td></tr>
@ <tr><th>Number of deltas:</th>\
@ <td>%,d(nDelta) (%d(nDelta*100/nTotal)%%)</td></tr>
@ <tr><th>Number of full-text:</th><td>%,d(nFull) \
@ (%d(nFull*100/nTotal)%%)</td></tr>
medExp = db_int(0, "SELECT szExp FROM artstat ORDER BY szExp"
" LIMIT 1 OFFSET %d", nTotal/2);
@ <tr><th>Uncompressed artifact sizes:</th>\
@ <td>largest: %,d(mxExp), average: %,d((int)avgExp), median: %,d(medExp)</td>
@ <tr><th>Compressed artifact sizes:</th>\
@ <td>largest: %,d(mxCmpr), average: %,d((int)avgCmpr), \
@ median: %,d(medCmpr)</td>
db_prepare(&q,
"SELECT avg(szCmpr), max(szCmpr) FROM artstat WHERE isDelta"
);
if( db_step(&q)==SQLITE_ROW ){
int mxDelta = db_column_int(&q,1);
double avgDelta = db_column_double(&q,0);
med = db_int(0, "SELECT szCmpr FROM artstat WHERE isDelta ORDER BY szCmpr"
" LIMIT 1 OFFSET %d", nDelta/2);
@ <tr><th>Delta artifact sizes:</th>\
@ <td>largest: %,d(mxDelta), average: %,d((int)avgDelta), \
@ median: %,d(med)</td>
}
db_finalize(&q);
r = db_double(0.0, "SELECT avg(szCmpr) FROM artstat WHERE NOT isDelta;");
med = db_int(0, "SELECT szCmpr FROM artstat WHERE NOT isDelta ORDER BY szCmpr"
" LIMIT 1 OFFSET %d", nFull/2);
@ <tr><th>Full-text artifact sizes:</th>
@ <td>largest: %,d(mxCmpr), average: %,d((int)r), median: %,d(med)</td>
@ </table>
@ <h1>Artifact Size Distribution Facts:</h1>
@ <ol>
@ <li><p>The largest %.2f(n50pct*100.0/nTotal)%% of artifacts
largest_n_artifacts(n50pct);
@ use 50%% of the total artifact space.
@ <li><p>The largest 1%% of artifacts
largest_n_artifacts((nTotal+99)/100);
@ use %lld(sz1pct*100/sumCmpr)%% of the total artifact space.
@ <li><p>The largest 10%% of artifacts
largest_n_artifacts((nTotal+9)/10);
@ use %lld(sz10pct*100/sumCmpr)%% of the total artifact space.
@ <li><p>The largest 25%% of artifacts
largest_n_artifacts((nTotal+4)/5);
@ use %lld(sz25pct*100/sumCmpr)%% of the total artifact space.
@ <li><p>The largest 50%% of artifacts
largest_n_artifacts((nTotal+1)/2);
@ use %lld(sz50pct*100/sumCmpr)%% of the total artifact space.
@ </ol>
@ <h1>Artifact Sizes By Type:</h1>
db_prepare(&q,
"SELECT atype, count(*), sum(isDelta), sum(szCmpr), sum(szExp)"
" FROM artstat GROUP BY 1"
" UNION ALL "
"SELECT 'ALL', count(*), sum(isDelta), sum(szCmpr), sum(szExp)"
" FROM artstat"
" ORDER BY 4;"
);
@ <table class='sortable' border='1' \
@ data-column-types='tkkkkk' data-init-sort='5'>
@ <thead><tr>
@ <th>Artifact Type</th>
@ <th>Count</th>
@ <th>Full-Text</th>
@ <th>Delta</th>
@ <th>Compressed Size</th>
@ <th>Uncompressed Size</th>
@ </tr></thead><tbody>
while( db_step(&q)==SQLITE_ROW ){
const char *zType = db_column_text(&q, 0);
int nTotal = db_column_int(&q, 1);
int nDelta = db_column_int(&q, 2);
int nFull = nTotal - nDelta;
sqlite3_int64 szCmpr = db_column_int64(&q, 3);
sqlite3_int64 szExp = db_column_int64(&q, 4);
@ <tr><td>%h(zType)</td>
@ <td data-sortkey='%08x(nTotal)' align='right'>%,d(nTotal)</td>
@ <td data-sortkey='%08x(nFull)' align='right'>%,d(nFull)</td>
@ <td data-sortkey='%08x(nDelta)' align='right'>%,d(nDelta)</td>
@ <td data-sortkey='%016llx(szCmpr)' align='right'>%,lld(szCmpr)</td>
@ <td data-sortkey='%016llx(szExp)' align='right'>%,lld(szExp)</td>
}
@ </tbody></table>
db_finalize(&q);
if( db_exists("SELECT 1 FROM artstat WHERE atype='unused'") ){
@ <h1>Unused Artifacts:</h1>
db_prepare(&q,
"SELECT artstat.id, blob.uuid, user.login,"
" datetime(rcvfrom.mtime), rcvfrom.rcvid"
" FROM artstat JOIN blob ON artstat.id=blob.rid"
" LEFT JOIN rcvfrom USING(rcvid)"
" LEFT JOIN user USING(uid)"
" WHERE atype='unused'"
);
@ <table class='sortable' border='1' \
@ data-column-types='ntttt' data-init-sort='0'>
@ <thead><tr>
@ <th>RecordID</th>
@ <th>Hash</th>
@ <th>User</th>
@ <th>Date</th>
@ <th>RcvID</th>
@ </tr></thead><tbody>
while( db_step(&q)==SQLITE_ROW ){
int rid = db_column_int(&q, 0);
const char *zHash = db_column_text(&q, 1);
const char *zUser = db_column_text(&q, 2);
const char *zDate = db_column_text(&q, 3);
int iRcvid = db_column_int(&q, 4);
@ <tr><td>%d(rid)</td>
@ <td>%z(href("%R/info/%!S",zHash))%S(zHash)</a></td>
@ <td>%h(zUser)</td>
@ <td>%h(zDate)</td>
@ <td>%z(href("%R/rcvfrom?rcvid=%d",iRcvid))%d(iRcvid)</a></td></tr>
}
@ </tbody></table></div>
db_finalize(&q);
}
style_table_sorter();
style_finish_page();
}