textreport

#!/bin/sh

#==============================================================================
# textreport
# File ID: c7d15602-a00d-11e6-8f51-dad378945b90
#
# Create some stats from repos.sqlite
#
# Author: Øyvind A. Holm <sunny@sunbase.org>
# License: GNU General Public License version 2 or later.
#==============================================================================

progname=textreport
VERSION=0.3.3

db=repos.sqlite

gencols() {
	local form="$*"
	local comma=""
	local f
	for f in bazaar cvs git mercurial subversion; do
		echo "$comma $(echo "$form" | sed s/§/$f/g) AS $f"
		comma=","
	done
}

gensel() {
	local form="$*"
	echo "
.width -10 -10 -10 -10 -10
SELECT $(gencols "$form") FROM repos LIMIT 1;
" | sqlite3 -column -header "$db"
}

test -f repos.sqlite || make repos.sqlite || {
	echo $progname: Could not create repos.sqlite >&2
	exit 1
}

startdate="$(sqlite3 "$db" "SELECT min(date) FROM repos;")"

instotal() {
	local name="$1"
	local form="$2"
	echo "
INSERT INTO tmp
VALUES (
  '$name',
  (SELECT $form FROM repos),
  (SELECT 100.0 * ($form) / $totalrepos FROM repos)
);
"
}

totals() {
	echo "
SELECT name, val, printf('(%.02f%%)', percent)
  FROM tmp
  ORDER BY val DESC, name;
SELECT '----------', '------', '---------';
SELECT 'Total', $totalrepos,
  printf('(%.02f%%)', (SELECT sum(percent) FROM tmp));
"
}

first() {
	echo -n "(SELECT $1 FROM repos ORDER BY date LIMIT 1)"
}

latest() {
	echo -n "(SELECT $1 FROM repos ORDER BY date DESC LIMIT 1)"
}

echo
echo "Repository statistics from Open Hub <https://openhub.net>"
echo Last updated $(
    sqlite3 "$db" "SELECT max(date) FROM repos;"
) UTC

totalrepos="$(sqlite3 "$db" "
SELECT
  $(latest bazaar) + $(latest cvs) + $(latest git) +
  $(latest mercurial) + $(latest subversion)
  FROM repos LIMIT 1;
")"

echo
echo Total number of repositories:
echo
echo "
.width 10 -6 -9
CREATE TEMPORARY TABLE tmp (name TEXT, val INTEGER, percent REAL);
$(instotal bazaar "$(latest bazaar)")
$(instotal cvs "$(latest cvs)")
$(instotal git "$(latest git)")
$(instotal mercurial "$(latest mercurial)")
$(instotal subversion "$(latest subversion)")
$(totals)
" | sqlite3 -column repos.sqlite
echo

totalrepos="$(sqlite3 "$db" "
SELECT
  $(latest bazaar) - $(first bazaar) +
  $(latest cvs) - $(first cvs) +
  $(latest git) - $(first git) +
  $(latest mercurial) - $(first mercurial) +
  $(latest subversion) - $(first subversion)
  FROM repos LIMIT 1;
")"

echo New repositories since $startdate UTC:
echo
echo "
.width 10 -6 -9
CREATE TEMPORARY TABLE tmp (name TEXT, val INTEGER, percent REAL);
$(instotal bazaar "$(latest bazaar)-$(first bazaar)")
$(instotal cvs "$(latest cvs)-$(first cvs)")
$(instotal git "$(latest git)-$(first git)")
$(instotal mercurial "$(latest mercurial)-$(first mercurial)")
$(instotal subversion "$(latest subversion)-$(first subversion)")
$(totals)
" | sqlite3 -column repos.sqlite
echo

echo New repositories per week:
echo
gensel "printf('%.02f', round(($(latest §) - $(first §)) /
        (julianday($(latest date)) - julianday($(first date))) * 7, 2))"
echo

# vim: set ts=8 sw=8 sts=8 noet fo+=w tw=79 fenc=UTF-8 :