Fossil

Check-in [2d916a5e]
Login

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

Overview
Comment:Fixes to the /artifact_stats page. Make it Admin-only.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | artifact-stats
Files: files | file ages | folders
SHA3-256:2d916a5e0575f88a7ab6d934e9dc0a93140b23c5b043778429c7b3c138412780
User & Date: drh 2017-12-09 22:00:19
Context
2017-12-09
22:01
Add the /artifact_stats page for use by administrators. check-in: d96bee1c user: drh tags: trunk
22:00
Fixes to the /artifact_stats page. Make it Admin-only. Leaf check-in: 2d916a5e user: drh tags: artifact-stats
17:49
Rough prototype for the /artifact_stats page. Still many issues. check-in: dd1c8fb3 user: drh tags: artifact-stats
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/stat.c.

93
94
95
96
97
98
99



100
101
102
103
104
105
106
...
581
582
583
584
585
586
587
588
589
590
591

592




































593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614

615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655


































656
657

658
659
660
  @ %s(zBuf)
  @ </td></tr>
  if( !brief ){
    @ <tr><th>Number&nbsp;Of&nbsp;Artifacts:</th><td>
    n = db_int(0, "SELECT count(*) FROM blob");
    m = db_int(0, "SELECT count(*) FROM delta");
    @ %d(n) (%d(n-m) fulltext and %d(m) deltas)



    @ </td></tr>
    if( n>0 ){
      int a, b;
      Stmt q;
      @ <tr><th>Uncompressed&nbsp;Artifact&nbsp;Size:</th><td>
      db_prepare(&q, "SELECT total(size), avg(size), max(size)"
                     " FROM blob WHERE size>0 /*scan*/");
................................................................................
    @   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,
    @           (SELECT type FROM description WHERE description.rid=blob.rid),
    @           EXISTS(SELECT 1 FROM delta WHERE delta.rid=blob.rid),
    @           size, length(content)

    @      FROM blob WHERE content IS NOT NULL;




































  ;
  describe_artifacts("IS NOT NULL");
  db_multi_exec("%s", zSql/*safe-for-%s*/);
}

/*
** WEBPAGE: artifact_stats
**
** Show information about the sizes of artifacts in this repository
*/
void artifact_stats_page(void){
  Stmt q;
  login_check_credentials();
  if( !g.perm.Read ){ login_needed(g.anon.Read); return; }
  style_header("Artifact Statistics");
  gather_artifact_stats();
  db_prepare(&q,
    "SELECT atype, count(*), sum(isDelta), sum(szCmpr), sum(szExp)"
    "  FROM artstat GROUP BY 1"
    " UNION ALL "
    "SELECT 'TOTAL', count(*), sum(isDelta), sum(szCmpr), sum(szExp)"
    "  FROM artstat;"

  );
  @ <table class='sortable' border='1'\
  @ data-column-types='tkkkkk' data-init-sort='0'>
  @ <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 - nTotal;
    sqlite3_int64 szCmpr = db_column_int64(&q, 3);
    sqlite3_int64 szExp = db_column_int64(&q, 4);
    char *z;
    @ <tr><td>%h(zType)</td>

    z = sqlite3_mprintf("%,d", nTotal);
    @ <td data-sortkey='%08x(nTotal)' align='right'>%s(z)</td>
    sqlite3_free(z);

    z = sqlite3_mprintf("%,d", nDelta);
    @ <td data-sortkey='%08x(nDelta)' align='right'>%s(z)</td>
    sqlite3_free(z);

    z = sqlite3_mprintf("%,d", nFull);
    @ <td data-sortkey='%08x(nFull)' align='right'>%s(z)</td>
    sqlite3_free(z);

    z = sqlite3_mprintf("%,lld", szCmpr);
    @ <td data-sortkey='%016x(szCmpr)' align='right'>%s(z)</td>
    sqlite3_free(z);

    z = sqlite3_mprintf("%,lld", szExp);
    @ <td data-sortkey='%016x(szExp)' align='right'>%s(z)</td>
    sqlite3_free(z);
  }


































  @ </tbody></table></div>
  db_finalize(&q);

  style_table_sorter();
  style_footer();
}







>
>
>







 







|
<


>
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

<











|






|
|
>

|
|












|









|
|


|
|










>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
|
|
>



93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
...
584
585
586
587
588
589
590
591

592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632

633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
  @ %s(zBuf)
  @ </td></tr>
  if( !brief ){
    @ <tr><th>Number&nbsp;Of&nbsp;Artifacts:</th><td>
    n = db_int(0, "SELECT count(*) FROM blob");
    m = db_int(0, "SELECT count(*) FROM delta");
    @ %d(n) (%d(n-m) fulltext and %d(m) deltas)
    if( g.perm.Admin ){
      @ <a href='%R/artifact_stats'>Details</a>
    }
    @ </td></tr>
    if( n>0 ){
      int a, b;
      Stmt q;
      @ <tr><th>Uncompressed&nbsp;Artifact&nbsp;Size:</th><td>
      db_prepare(&q, "SELECT total(size), avg(size), max(size)"
                     " FROM blob WHERE size>0 /*scan*/");
................................................................................
    @   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,

    @           EXISTS(SELECT 1 FROM delta WHERE delta.rid=blob.rid),
    @           size, length(content)
    @      FROM blob
    @     WHERE content IS NOT NULL;
    @ UPDATE artstat SET atype='file'
    @  WHERE id IN (SELECT fid FROM mlink)
    @    AND atype IS NULL;
    @ UPDATE artstat SET atype='manifest'
    @  WHERE id IN (SELECT objid FROM event WHERE type='ci') 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='unknown' WHERE atype IS NULL;
  ;

  db_multi_exec("%s", zSql/*safe-for-%s*/);
}

/*
** WEBPAGE: artifact_stats
**
** Show information about the sizes of artifacts in this repository
*/
void artifact_stats_page(void){
  Stmt q;
  login_check_credentials();
  if( !g.perm.Admin ){ login_needed(g.anon.Admin); return; }
  style_header("Artifact Statistics");
  gather_artifact_stats();
  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 5;"
  );
  @ <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);
    char *z;
    @ <tr><td>%h(zType)</td>

    z = sqlite3_mprintf("%,d", nTotal);
    @ <td data-sortkey='%08x(nTotal)' align='right'>%s(z)</td>
    sqlite3_free(z);

    z = sqlite3_mprintf("%,d", nFull);
    @ <td data-sortkey='%08x(nFull)' align='right'>%s(z)</td>
    sqlite3_free(z);

    z = sqlite3_mprintf("%,d", nDelta);
    @ <td data-sortkey='%08x(nDelta)' align='right'>%s(z)</td>
    sqlite3_free(z);

    z = sqlite3_mprintf("%,lld", szCmpr);
    @ <td data-sortkey='%016x(szCmpr)' align='right'>%s(z)</td>
    sqlite3_free(z);

    z = sqlite3_mprintf("%,lld", szExp);
    @ <td data-sortkey='%016x(szExp)' align='right'>%s(z)</td>
    sqlite3_free(z);
  }
  @ </tbody></table>
  db_finalize(&q);

  if( db_exists("SELECT 1 FROM artstat WHERE atype='unknown'") ){
    @ <h2>Unknown Artifacts</h2>
    db_prepare(&q,
      "SELECT artstat.id, blob.uuid, user.login,"
      "       datetime(rcvfrom.mtime), rcvfrom.ipaddr"
      "  FROM artstat JOIN blob ON artstat.id=blob.rid"
      "       LEFT JOIN rcvfrom USING(rcvid)"
      "       LEFT JOIN user USING(uid)"
      " WHERE atype='unknown'"
    );
    @ <table class='not-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>IP-Addr</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);
      const char *zIpAddr = db_column_text(&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>%h(zIpAddr)</td></tr>
    }
    @ </tbody></table></div>
    db_finalize(&q);
  }
  style_table_sorter();
  style_footer();
}