Fossil SCM

Add the /artifact_stats page for use by administrators.

drh 2017-12-09 22:01 trunk merge
Commit d96bee1c76276787a3b4b8760839776429c814f92cff39b66f54742f49887bde
Parent 561fa8a3b7f2a63…
1 file changed +161
~ src/stat.c
M src/stat.c
+161
--- src/stat.c
+++ src/stat.c
@@ -95,10 +95,13 @@
9595
 if( !brief ){
9696
 @ <tr><th>Number&nbsp;Of&nbsp;Artifacts:</th><td>
9797
 n = db_int(0, "SELECT count(*) FROM blob");
9898
 m = db_int(0, "SELECT count(*) FROM delta");
9999
 @ %d(n) (%d(n-m) fulltext and %d(m) deltas)
100
+ if( g.perm.Admin ){
101
+ @ <a href='%R/artifact_stats'>Details</a>
102
+ }
100103
 @ </td></tr>
101104
 if( n>0 ){
102105
 int a, b;
103106
 Stmt q;
104107
 @ <tr><th>Uncompressed&nbsp;Artifact&nbsp;Size:</th><td>
@@ -569,5 +572,163 @@
569572
 piechart_render(800,500,PIE_OTHER|PIE_PERCENT);
570573
 @ </svg></center>
571574
 }
572575
 style_footer();
573576
 }
577
+
578
+/*
579
+** Gather statistics on artifact types, counts, and sizes.
580
+*/
581
+static void gather_artifact_stats(void){
582
+ static const char zSql[] =
583
+ @ CREATE TEMP TABLE artstat(
584
+ @ id INTEGER PRIMARY KEY, -- Corresponds to BLOB.RID
585
+ @ atype TEXT, -- 'data', 'manifest', 'tag', 'wiki', etc.
586
+ @ isDelta BOOLEAN, -- true if stored as a delta
587
+ @ szExp, -- expanded, uncompressed size
588
+ @ szCmpr -- size as stored on disk
589
+ @ );
590
+ @ INSERT INTO artstat(id,atype,isDelta,szExp,szCmpr)
591
+ @ SELECT blob.rid, NULL,
592
+ @ EXISTS(SELECT 1 FROM delta WHERE delta.rid=blob.rid),
593
+ @ size, length(content)
594
+ @ FROM blob
595
+ @ WHERE content IS NOT NULL;
596
+ @ UPDATE artstat SET atype='file'
597
+ @ WHERE id IN (SELECT fid FROM mlink)
598
+ @ AND atype IS NULL;
599
+ @ UPDATE artstat SET atype='manifest'
600
+ @ WHERE id IN (SELECT objid FROM event WHERE type='ci') AND atype IS NULL;
601
+ @ UPDATE artstat SET atype='cluster'
602
+ @ WHERE atype IS NULL
603
+ @ AND id IN (SELECT rid FROM tagxref
604
+ @ WHERE tagid=(SELECT tagid FROM tag
605
+ @ WHERE tagname='cluster'));
606
+ @ UPDATE artstat SET atype='ticket'
607
+ @ WHERE atype IS NULL
608
+ @ AND id IN (SELECT rid FROM tagxref
609
+ @ WHERE tagid IN (SELECT tagid FROM tag
610
+ @ WHERE tagname GLOB 'tkt-*'));
611
+ @ UPDATE artstat SET atype='wiki'
612
+ @ WHERE atype IS NULL
613
+ @ AND id IN (SELECT rid FROM tagxref
614
+ @ WHERE tagid IN (SELECT tagid FROM tag
615
+ @ WHERE tagname GLOB 'wiki-*'));
616
+ @ UPDATE artstat SET atype='technote'
617
+ @ WHERE atype IS NULL
618
+ @ AND id IN (SELECT rid FROM tagxref
619
+ @ WHERE tagid IN (SELECT tagid FROM tag
620
+ @ WHERE tagname GLOB 'event-*'));
621
+ @ UPDATE artstat SET atype='attachment'
622
+ @ WHERE atype IS NULL
623
+ @ AND id IN (SELECT attachid FROM attachment UNION
624
+ @ SELECT blob.rid FROM attachment JOIN blob ON uuid=src);
625
+ @ UPDATE artstat SET atype='tag'
626
+ @ WHERE atype IS NULL
627
+ @ AND id IN (SELECT srcid FROM tagxref);
628
+ @ UPDATE artstat SET atype='tag'
629
+ @ WHERE atype IS NULL
630
+ @ AND id IN (SELECT objid FROM event WHERE type='g');
631
+ @ UPDATE artstat SET atype='unknown' WHERE atype IS NULL;
632
+ ;
633
+ db_multi_exec("%s", zSql/*safe-for-%s*/);
634
+}
635
+
636
+/*
637
+** WEBPAGE: artifact_stats
638
+**
639
+** Show information about the sizes of artifacts in this repository
640
+*/
641
+void artifact_stats_page(void){
642
+ Stmt q;
643
+ login_check_credentials();
644
+ if( !g.perm.Admin ){ login_needed(g.anon.Admin); return; }
645
+ style_header("Artifact Statistics");
646
+ gather_artifact_stats();
647
+ db_prepare(&q,
648
+ "SELECT atype, count(*), sum(isDelta), sum(szCmpr), sum(szExp)"
649
+ " FROM artstat GROUP BY 1"
650
+ " UNION ALL "
651
+ "SELECT 'ALL', count(*), sum(isDelta), sum(szCmpr), sum(szExp)"
652
+ " FROM artstat"
653
+ " ORDER BY 5;"
654
+ );
655
+ @ <table class='sortable' border='1' \
656
+ @ data-column-types='tkkkkk' data-init-sort='5'>
657
+ @ <thead><tr>
658
+ @ <th>Artifact Type</th>
659
+ @ <th>Count</th>
660
+ @ <th>Full-Text</th>
661
+ @ <th>Delta</th>
662
+ @ <th>Compressed Size</th>
663
+ @ <th>Uncompressed Size</th>
664
+ @ </tr></thead><tbody>
665
+ while( db_step(&q)==SQLITE_ROW ){
666
+ const char *zType = db_column_text(&q, 0);
667
+ int nTotal = db_column_int(&q, 1);
668
+ int nDelta = db_column_int(&q, 2);
669
+ int nFull = nTotal - nDelta;
670
+ sqlite3_int64 szCmpr = db_column_int64(&q, 3);
671
+ sqlite3_int64 szExp = db_column_int64(&q, 4);
672
+ char *z;
673
+ @ <tr><td>%h(zType)</td>
674
+
675
+ z = sqlite3_mprintf("%,d", nTotal);
676
+ @ <td data-sortkey='%08x(nTotal)' align='right'>%s(z)</td>
677
+ sqlite3_free(z);
678
+
679
+ z = sqlite3_mprintf("%,d", nFull);
680
+ @ <td data-sortkey='%08x(nFull)' align='right'>%s(z)</td>
681
+ sqlite3_free(z);
682
+
683
+ z = sqlite3_mprintf("%,d", nDelta);
684
+ @ <td data-sortkey='%08x(nDelta)' align='right'>%s(z)</td>
685
+ sqlite3_free(z);
686
+
687
+ z = sqlite3_mprintf("%,lld", szCmpr);
688
+ @ <td data-sortkey='%016x(szCmpr)' align='right'>%s(z)</td>
689
+ sqlite3_free(z);
690
+
691
+ z = sqlite3_mprintf("%,lld", szExp);
692
+ @ <td data-sortkey='%016x(szExp)' align='right'>%s(z)</td>
693
+ sqlite3_free(z);
694
+ }
695
+ @ </tbody></table>
696
+ db_finalize(&q);
697
+
698
+ if( db_exists("SELECT 1 FROM artstat WHERE atype='unknown'") ){
699
+ @ <h2>Unknown Artifacts</h2>
700
+ db_prepare(&q,
701
+ "SELECT artstat.id, blob.uuid, user.login,"
702
+ " datetime(rcvfrom.mtime), rcvfrom.ipaddr"
703
+ " FROM artstat JOIN blob ON artstat.id=blob.rid"
704
+ " LEFT JOIN rcvfrom USING(rcvid)"
705
+ " LEFT JOIN user USING(uid)"
706
+ " WHERE atype='unknown'"
707
+ );
708
+ @ <table class='not-sortable' border='1' \
709
+ @ data-column-types='ntttt' data-init-sort='0'>
710
+ @ <thead><tr>
711
+ @ <th>RecordID</th>
712
+ @ <th>Hash</th>
713
+ @ <th>User</th>
714
+ @ <th>Date</th>
715
+ @ <th>IP-Addr</th>
716
+ @ </tr></thead><tbody>
717
+ while( db_step(&q)==SQLITE_ROW ){
718
+ int rid = db_column_int(&q, 0);
719
+ const char *zHash = db_column_text(&q, 1);
720
+ const char *zUser = db_column_text(&q, 2);
721
+ const char *zDate = db_column_text(&q, 3);
722
+ const char *zIpAddr = db_column_text(&q, 4);
723
+ @ <tr><td>%d(rid)</td>
724
+ @ <td>%z(href("%R/info/%!S",zHash))%S(zHash)</a></td>
725
+ @ <td>%h(zUser)</td>
726
+ @ <td>%h(zDate)</td>
727
+ @ <td>%h(zIpAddr)</td></tr>
728
+ }
729
+ @ </tbody></table></div>
730
+ db_finalize(&q);
731
+ }
732
+ style_table_sorter();
733
+ style_footer();
734
+}
574735
--- src/stat.c
+++ src/stat.c
@@ -95,10 +95,13 @@
95 if( !brief ){
96 @ <tr><th>Number&nbsp;Of&nbsp;Artifacts:</th><td>
97 n = db_int(0, "SELECT count(*) FROM blob");
98 m = db_int(0, "SELECT count(*) FROM delta");
99 @ %d(n) (%d(n-m) fulltext and %d(m) deltas)
 
 
 
100 @ </td></tr>
101 if( n>0 ){
102 int a, b;
103 Stmt q;
104 @ <tr><th>Uncompressed&nbsp;Artifact&nbsp;Size:</th><td>
@@ -569,5 +572,163 @@
569 piechart_render(800,500,PIE_OTHER|PIE_PERCENT);
570 @ </svg></center>
571 }
572 style_footer();
573 }
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
574
--- src/stat.c
+++ src/stat.c
@@ -95,10 +95,13 @@
95 if( !brief ){
96 @ <tr><th>Number&nbsp;Of&nbsp;Artifacts:</th><td>
97 n = db_int(0, "SELECT count(*) FROM blob");
98 m = db_int(0, "SELECT count(*) FROM delta");
99 @ %d(n) (%d(n-m) fulltext and %d(m) deltas)
100 if( g.perm.Admin ){
101 @ <a href='%R/artifact_stats'>Details</a>
102 }
103 @ </td></tr>
104 if( n>0 ){
105 int a, b;
106 Stmt q;
107 @ <tr><th>Uncompressed&nbsp;Artifact&nbsp;Size:</th><td>
@@ -569,5 +572,163 @@
572 piechart_render(800,500,PIE_OTHER|PIE_PERCENT);
573 @ </svg></center>
574 }
575 style_footer();
576 }
577
578 /*
579 ** Gather statistics on artifact types, counts, and sizes.
580 */
581 static void gather_artifact_stats(void){
582 static const char zSql[] =
583 @ CREATE TEMP TABLE artstat(
584 @ id INTEGER PRIMARY KEY, -- Corresponds to BLOB.RID
585 @ atype TEXT, -- 'data', 'manifest', 'tag', 'wiki', etc.
586 @ isDelta BOOLEAN, -- true if stored as a delta
587 @ szExp, -- expanded, uncompressed size
588 @ szCmpr -- size as stored on disk
589 @ );
590 @ INSERT INTO artstat(id,atype,isDelta,szExp,szCmpr)
591 @ SELECT blob.rid, NULL,
592 @ EXISTS(SELECT 1 FROM delta WHERE delta.rid=blob.rid),
593 @ size, length(content)
594 @ FROM blob
595 @ WHERE content IS NOT NULL;
596 @ UPDATE artstat SET atype='file'
597 @ WHERE id IN (SELECT fid FROM mlink)
598 @ AND atype IS NULL;
599 @ UPDATE artstat SET atype='manifest'
600 @ WHERE id IN (SELECT objid FROM event WHERE type='ci') AND atype IS NULL;
601 @ UPDATE artstat SET atype='cluster'
602 @ WHERE atype IS NULL
603 @ AND id IN (SELECT rid FROM tagxref
604 @ WHERE tagid=(SELECT tagid FROM tag
605 @ WHERE tagname='cluster'));
606 @ UPDATE artstat SET atype='ticket'
607 @ WHERE atype IS NULL
608 @ AND id IN (SELECT rid FROM tagxref
609 @ WHERE tagid IN (SELECT tagid FROM tag
610 @ WHERE tagname GLOB 'tkt-*'));
611 @ UPDATE artstat SET atype='wiki'
612 @ WHERE atype IS NULL
613 @ AND id IN (SELECT rid FROM tagxref
614 @ WHERE tagid IN (SELECT tagid FROM tag
615 @ WHERE tagname GLOB 'wiki-*'));
616 @ UPDATE artstat SET atype='technote'
617 @ WHERE atype IS NULL
618 @ AND id IN (SELECT rid FROM tagxref
619 @ WHERE tagid IN (SELECT tagid FROM tag
620 @ WHERE tagname GLOB 'event-*'));
621 @ UPDATE artstat SET atype='attachment'
622 @ WHERE atype IS NULL
623 @ AND id IN (SELECT attachid FROM attachment UNION
624 @ SELECT blob.rid FROM attachment JOIN blob ON uuid=src);
625 @ UPDATE artstat SET atype='tag'
626 @ WHERE atype IS NULL
627 @ AND id IN (SELECT srcid FROM tagxref);
628 @ UPDATE artstat SET atype='tag'
629 @ WHERE atype IS NULL
630 @ AND id IN (SELECT objid FROM event WHERE type='g');
631 @ UPDATE artstat SET atype='unknown' WHERE atype IS NULL;
632 ;
633 db_multi_exec("%s", zSql/*safe-for-%s*/);
634 }
635
636 /*
637 ** WEBPAGE: artifact_stats
638 **
639 ** Show information about the sizes of artifacts in this repository
640 */
641 void artifact_stats_page(void){
642 Stmt q;
643 login_check_credentials();
644 if( !g.perm.Admin ){ login_needed(g.anon.Admin); return; }
645 style_header("Artifact Statistics");
646 gather_artifact_stats();
647 db_prepare(&q,
648 "SELECT atype, count(*), sum(isDelta), sum(szCmpr), sum(szExp)"
649 " FROM artstat GROUP BY 1"
650 " UNION ALL "
651 "SELECT 'ALL', count(*), sum(isDelta), sum(szCmpr), sum(szExp)"
652 " FROM artstat"
653 " ORDER BY 5;"
654 );
655 @ <table class='sortable' border='1' \
656 @ data-column-types='tkkkkk' data-init-sort='5'>
657 @ <thead><tr>
658 @ <th>Artifact Type</th>
659 @ <th>Count</th>
660 @ <th>Full-Text</th>
661 @ <th>Delta</th>
662 @ <th>Compressed Size</th>
663 @ <th>Uncompressed Size</th>
664 @ </tr></thead><tbody>
665 while( db_step(&q)==SQLITE_ROW ){
666 const char *zType = db_column_text(&q, 0);
667 int nTotal = db_column_int(&q, 1);
668 int nDelta = db_column_int(&q, 2);
669 int nFull = nTotal - nDelta;
670 sqlite3_int64 szCmpr = db_column_int64(&q, 3);
671 sqlite3_int64 szExp = db_column_int64(&q, 4);
672 char *z;
673 @ <tr><td>%h(zType)</td>
674
675 z = sqlite3_mprintf("%,d", nTotal);
676 @ <td data-sortkey='%08x(nTotal)' align='right'>%s(z)</td>
677 sqlite3_free(z);
678
679 z = sqlite3_mprintf("%,d", nFull);
680 @ <td data-sortkey='%08x(nFull)' align='right'>%s(z)</td>
681 sqlite3_free(z);
682
683 z = sqlite3_mprintf("%,d", nDelta);
684 @ <td data-sortkey='%08x(nDelta)' align='right'>%s(z)</td>
685 sqlite3_free(z);
686
687 z = sqlite3_mprintf("%,lld", szCmpr);
688 @ <td data-sortkey='%016x(szCmpr)' align='right'>%s(z)</td>
689 sqlite3_free(z);
690
691 z = sqlite3_mprintf("%,lld", szExp);
692 @ <td data-sortkey='%016x(szExp)' align='right'>%s(z)</td>
693 sqlite3_free(z);
694 }
695 @ </tbody></table>
696 db_finalize(&q);
697
698 if( db_exists("SELECT 1 FROM artstat WHERE atype='unknown'") ){
699 @ <h2>Unknown Artifacts</h2>
700 db_prepare(&q,
701 "SELECT artstat.id, blob.uuid, user.login,"
702 " datetime(rcvfrom.mtime), rcvfrom.ipaddr"
703 " FROM artstat JOIN blob ON artstat.id=blob.rid"
704 " LEFT JOIN rcvfrom USING(rcvid)"
705 " LEFT JOIN user USING(uid)"
706 " WHERE atype='unknown'"
707 );
708 @ <table class='not-sortable' border='1' \
709 @ data-column-types='ntttt' data-init-sort='0'>
710 @ <thead><tr>
711 @ <th>RecordID</th>
712 @ <th>Hash</th>
713 @ <th>User</th>
714 @ <th>Date</th>
715 @ <th>IP-Addr</th>
716 @ </tr></thead><tbody>
717 while( db_step(&q)==SQLITE_ROW ){
718 int rid = db_column_int(&q, 0);
719 const char *zHash = db_column_text(&q, 1);
720 const char *zUser = db_column_text(&q, 2);
721 const char *zDate = db_column_text(&q, 3);
722 const char *zIpAddr = db_column_text(&q, 4);
723 @ <tr><td>%d(rid)</td>
724 @ <td>%z(href("%R/info/%!S",zHash))%S(zHash)</a></td>
725 @ <td>%h(zUser)</td>
726 @ <td>%h(zDate)</td>
727 @ <td>%h(zIpAddr)</td></tr>
728 }
729 @ </tbody></table></div>
730 db_finalize(&q);
731 }
732 style_table_sorter();
733 style_footer();
734 }
735

Keyboard Shortcuts

Open search /
Next entry (timeline) j
Previous entry (timeline) k
Open focused entry Enter
Show this help ?
Toggle theme Top nav button