reproducible: keep arch specific stats and introduce suite_$arch_stats

3 files changed, 99 insertions, 35 deletions

diff --git a/bin/reproducible_common.sh b/bin/reproducible_common.sh
index 0019a8af..d9fc358c 100755
--- a/bin/reproducible_common.sh
+++ b/bin/reproducible_common.sh
@@ -152,9 +152,9 @@ write_page_header() {
 	rm -f $PAGE
 	MAINVIEW="stats"
 	ALLSTATES="reproducible FTBR FTBFS depwait not_for_us 404 blacklisted"
-	ALLVIEWS="issues notes no_notes scheduled last_24h last_48h all_abc notify dd-list pkg_sets suite_stats repositories stats"
+	ALLVIEWS="issues notes no_notes scheduled last_24h last_48h all_abc notify dd-list pkg_sets suite_amd64_stats suite_armhf_stats repositories stats"
 	GLOBALVIEWS="issues scheduled notify repositories stats"
-	SUITEVIEWS="dd-list suite_stats"
+	SUITEVIEWS="dd-list suite_amd64_stats suite_armhf_stats"
 	SPOKENTARGET["issues"]="issues"
 	SPOKENTARGET["notes"]="packages with notes"
 	SPOKENTARGET["no_notes"]="packages without notes"
@@ -199,6 +199,10 @@ write_page_header() {
 			# no pkg_sets are tested in experimental
 			continue
 		fi
+		if [ "$TARGET" = "pkg_sets" ] && [ "$ARCH" = "armhf" ] ; then
+			# no pkg_sets for armhf _yet_
+			continue
+		fi
 		SPOKEN_TARGET=${SPOKENTARGET[$TARGET]}
 		BASEURL="/$SUITE/$ARCH"
 		local i
@@ -214,7 +218,11 @@ write_page_header() {
 		done
 		if [ "$TARGET" = "suite_stats" ] ; then
 			for i in $SUITES ; do
-				write_page "<li><a href=\"/$i\">suite: $i</a></li>"
+				if [ "$SUITE" != "unstable" ] && [ "$ARCH" = "armhf" ] ; then
+					# only unstable is tested on armhf atm
+					continue
+				fi
+				write_page "<li><a href=\"/$i/$ARCH\">suite: $i</a></li>"
 			done
 		elif [ "$TARGET" = "notify" ] ; then
 			write_page "<li><a href=\"$BASEURL/index_${TARGET}.html\" title=\"notify icon\">${SPOKEN_TARGET}</a></li>"
@@ -225,7 +233,7 @@ write_page_header() {
 	write_page "<li><a href=\"https://wiki.debian.org/ReproducibleBuilds\" target=\"_blank\">wiki</a></li>"
 	write_page "</ul>"
 	if [ "$1" = "$MAINVIEW" ] ; then
-		LATEST=$(sqlite3 -init $INIT ${PACKAGES_DB} "SELECT s.name FROM results AS r JOIN sources AS s ON r.package_id = s.id WHERE r.status IN ('unreproducible') AND s.suite = 'unstable' AND s.id NOT IN (SELECT package_id FROM notes) ORDER BY build_date DESC LIMIT 23"|sort -R|head -1)
+		LATEST=$(sqlite3 -init $INIT ${PACKAGES_DB} "SELECT s.name FROM results AS r JOIN sources AS s ON r.package_id = s.id WHERE r.status IN ('unreproducible') AND s.suite = 'unstable' AND s.arch = 'amd64' AND s.id NOT IN (SELECT package_id FROM notes) ORDER BY build_date DESC LIMIT 23"|sort -R|head -1)
 		write_page "<form onsubmit=\"location.href='https://reproducible.debian.net/' + document.getElementById('SrcPkg').value; return false;\">"
 		write_page "https://reproducible.debian.net/<input type=\"text\" id=\"SrcPkg\" value=\"$LATEST\"/>"
 		write_page "<input type=\"submit\" value=\"submit source package name\" />"
diff --git a/bin/reproducible_db_maintenance.py b/bin/reproducible_db_maintenance.py
index a8642b91..527e3686 100755
--- a/bin/reproducible_db_maintenance.py
+++ b/bin/reproducible_db_maintenance.py
@@ -427,6 +427,62 @@ schema_updates = {
         '''ALTER TABLE stats_bugs ADD COLUMN open_locale INTEGER DEFAULT "0"''',
         '''ALTER TABLE stats_bugs ADD COLUMN done_locale INTEGER DEFAULT "0"''',
         'INSERT INTO rb_schema VALUES ("18", "' + now + '")'],
+    19: [ # add column architecture to stats_pkg_state and use (datum, suite, architecture) as primary key
+        '''CREATE TABLE stats_pkg_state_tmp
+           (datum TEXT NOT NULL,
+            suite TEXT NOT NULL,
+            architecture TEXT NOT NULL,
+            untested INTEGER,
+            reproducible INTEGER,
+            unreproducible INTEGER,
+            FTBFS INTEGER,
+            other INTEGER,
+            PRIMARY KEY (datum, suite, architecture))''',
+        '''INSERT INTO stats_pkg_state_tmp (datum, suite, untested,
+            reproducible, unreproducible, FTBFS, other)
+            SELECT datum, suite, untested, reproducible, unreproducible,
+            FTBFS, other FROM stats_pkg_state;''',
+        '''DROP TABLE stats_pkg_state;''',
+        '''ALTER TABLE stats_pkg_state_tmp RENAME TO stats_pkg_state;''',
+        'INSERT INTO rb_schema VALUES ("19", "' + now + '")'],
+    20: [ # add column architecture to stats_builds_per_day and use (datum, suite, architecture) as primary key
+        '''CREATE TABLE stats_builds_per_day_tmp
+                     (datum TEXT NOT NULL,
+                      suite TEXT NOT NULL,
+                      architecture TEXT NOT NULL,
+                      reproducible INTEGER,
+                      unreproducible INTEGER,
+                      FTBFS INTEGER,
+                      other INTEGER,
+                      PRIMARY KEY (datum, suite, architecture))''',
+        '''INSERT INTO stats_builds_per_day_tmp (datum, suite,
+            reproducible, unreproducible, FTBFS, other)
+            SELECT datum, suite, reproducible, unreproducible,
+            FTBFS, other FROM stats_builds_per_day;''',
+        '''DROP TABLE stats_builds_per_day;''',
+        '''ALTER TABLE stats_builds_per_day_tmp RENAME TO stats_builds_per_day;''',
+        'INSERT INTO rb_schema VALUES ("20", "' + now + '")'],
+    21: [ # add column architecture to stats_builds_age and use (datum, suite, architecture) as primary key
+        '''CREATE TABLE stats_builds_age_tmp
+                     (datum TEXT NOT NULL,
+                      suite TEXT NOT NULL,
+                      architecture TEXT NOT NULL,
+                      oldest_reproducible REAL,
+                      oldest_unreproducible REAL,
+                      oldest_FTBFS REAL,
+                      PRIMARY KEY (datum, suite, architecture))''',
+        '''INSERT INTO stats_builds_age_tmp (datum, suite,
+            oldest_reproducible, oldest_unreproducible, oldest_FTBFS)
+            SELECT datum, suite, oldest_reproducible, oldest_unreproducible,
+            oldest_FTBFS FROM stats_builds_age;''',
+        '''DROP TABLE stats_builds_age;''',
+        '''ALTER TABLE stats_builds_age_tmp RENAME TO stats_builds_age;''',
+        'INSERT INTO rb_schema VALUES ("21", "' + now + '")'],
+    22: [ # we've only tested amd64 so far
+        'UPDATE stats_pkg_state SET architecture = "amd64" WHERE architecture = ""',
+        'UPDATE stats_builds_per_day SET architecture = "amd64" WHERE architecture = ""',
+        'UPDATE stats_builds_age SET architecture = "amd64" WHERE architecture = ""',
+        'INSERT INTO rb_schema VALUES ("22", "' + now + '")'],
 }
 
 
diff --git a/bin/reproducible_html_dashboards.sh b/bin/reproducible_html_dashboards.sh
index 9b4bd386..e43e7de3 100755
--- a/bin/reproducible_html_dashboards.sh
+++ b/bin/reproducible_html_dashboards.sh
@@ -66,22 +66,22 @@ YLABEL[7]="Amount of bugs open / closed"
 #
 # update package + build stats
 #
-update_suite_stats() {
+update_suite_arch_stats() {
 	RESULT=$(sqlite3 -init ${INIT} ${PACKAGES_DB} "SELECT datum,suite from ${TABLE[0]} WHERE datum = \"$DATE\" AND suite = \"$SUITE\"")
 	if [ -z $RESULT ] ; then
-		echo "Updating packages and builds stats for $SUITE on $DATE."
-		ALL=$(sqlite3 -init ${INIT} ${PACKAGES_DB} "SELECT count(name) FROM sources WHERE suite='${SUITE}'")
-		GOOD=$(sqlite3 -init ${INIT} ${PACKAGES_DB} "SELECT count(r.status) FROM results AS r JOIN sources AS s ON r.package_id=s.id WHERE s.suite='$SUITE' AND r.status = 'reproducible' AND date(r.build_date)<='$DATE';")
-		GOOAY=$(sqlite3 -init ${INIT} ${PACKAGES_DB} "SELECT count(r.status) FROM results AS r JOIN sources AS s ON r.package_id=s.id WHERE s.suite='$SUITE' AND r.status = 'reproducible' AND date(r.build_date)='$DATE';")
-		BAD=$(sqlite3 -init ${INIT} ${PACKAGES_DB} "SELECT count(r.status) FROM results AS r JOIN sources AS s ON r.package_id=s.id WHERE s.suite='$SUITE' AND r.status = 'unreproducible' AND date(r.build_date)<='$DATE';")
-		BAAY=$(sqlite3 -init ${INIT} ${PACKAGES_DB} "SELECT count(r.status) FROM results AS r JOIN sources AS s ON r.package_id=s.id  WHERE s.suite='$SUITE' AND r.status = 'unreproducible' AND date(r.build_date)='$DATE';")
-		UGLY=$(sqlite3 -init ${INIT} ${PACKAGES_DB} "SELECT count(r.status) FROM results AS r JOIN sources AS s ON r.package_id=s.id  WHERE s.suite='$SUITE' AND r.status = 'FTBFS' AND date(r.build_date)<='$DATE';")
-		UGLDAY=$(sqlite3 -init ${INIT} ${PACKAGES_DB} "SELECT count(r.status) FROM results AS r JOIN sources AS s ON r.package_id=s.id  WHERE s.suite='$SUITE' AND r.status = 'FTBFS' AND date(r.build_date)='$DATE';")
-		REST=$(sqlite3 -init ${INIT} ${PACKAGES_DB} "SELECT count(r.status) FROM results AS r JOIN sources AS s ON r.package_id=s.id WHERE (r.status != 'FTBFS' AND r.status != 'unreproducible' AND r.status != 'reproducible') AND s.suite='$SUITE' AND date(r.build_date)<='$DATE';")
-		RESDAY=$(sqlite3 -init ${INIT} ${PACKAGES_DB} "SELECT count(r.status) FROM results AS r JOIN sources AS s ON r.package_id=s.id WHERE (r.status != 'FTBFS' AND r.status != 'unreproducible' AND r.status != 'reproducible') AND s.suite='$SUITE' AND date(r.build_date)='$DATE';")
-		OLDESTG=$(sqlite3 -init ${INIT} ${PACKAGES_DB} "SELECT r.build_date FROM results AS r JOIN sources AS s ON r.package_id=s.id WHERE r.status = 'reproducible' AND s.suite='$SUITE' AND NOT date(r.build_date)>='$DATE' ORDER BY r.build_date LIMIT 1;")
-		OLDESTB=$(sqlite3 -init ${INIT} ${PACKAGES_DB} "SELECT r.build_date FROM results AS r JOIN sources AS s ON r.package_id=s.id WHERE s.suite='$SUITE' AND r.status = 'unreproducible' AND NOT date(r.build_date)>='$DATE' ORDER BY r.build_date LIMIT 1;")
-		OLDESTU=$(sqlite3 -init ${INIT} ${PACKAGES_DB} "SELECT r.build_date FROM results AS r JOIN sources AS s ON r.package_id=s.id WHERE s.suite='$SUITE' AND r.status = 'FTBFS' AND NOT date(r.build_date)>='$DATE' ORDER BY r.build_date LIMIT 1;")
+		echo "Updating packages and builds stats for $SUITE/$ARCH in $DATE."
+		ALL=$(sqlite3 -init ${INIT} ${PACKAGES_DB} "SELECT count(name) FROM sources WHERE suite='${SUITE}' AND architecture='$ARCH'")
+		GOOD=$(sqlite3 -init ${INIT} ${PACKAGES_DB} "SELECT count(r.status) FROM results AS r JOIN sources AS s ON r.package_id=s.id WHERE s.suite='$SUITE' AND s.architecture='$ARCH' AND r.status = 'reproducible' AND date(r.build_date)<='$DATE';")
+		GOOAY=$(sqlite3 -init ${INIT} ${PACKAGES_DB} "SELECT count(r.status) FROM results AS r JOIN sources AS s ON r.package_id=s.id WHERE s.suite='$SUITE' AND s.architecture='$ARCH' AND r.status = 'reproducible' AND date(r.build_date)='$DATE';")
+		BAD=$(sqlite3 -init ${INIT} ${PACKAGES_DB} "SELECT count(r.status) FROM results AS r JOIN sources AS s ON r.package_id=s.id WHERE s.suite='$SUITE' AND s.architecture='$ARCH' AND r.status = 'unreproducible' AND date(r.build_date)<='$DATE';")
+		BAAY=$(sqlite3 -init ${INIT} ${PACKAGES_DB} "SELECT count(r.status) FROM results AS r JOIN sources AS s ON r.package_id=s.id  WHERE s.suite='$SUITE' AND s.architecture='$ARCH' AND r.status = 'unreproducible' AND date(r.build_date)='$DATE';")
+		UGLY=$(sqlite3 -init ${INIT} ${PACKAGES_DB} "SELECT count(r.status) FROM results AS r JOIN sources AS s ON r.package_id=s.id  WHERE s.suite='$SUITE' AND s.architecture='$ARCH' AND r.status = 'FTBFS' AND date(r.build_date)<='$DATE';")
+		UGLDAY=$(sqlite3 -init ${INIT} ${PACKAGES_DB} "SELECT count(r.status) FROM results AS r JOIN sources AS s ON r.package_id=s.id  WHERE s.suite='$SUITE' AND s.architecture='$ARCH' AND r.status = 'FTBFS' AND date(r.build_date)='$DATE';")
+		REST=$(sqlite3 -init ${INIT} ${PACKAGES_DB} "SELECT count(r.status) FROM results AS r JOIN sources AS s ON r.package_id=s.id WHERE (r.status != 'FTBFS' AND r.status != 'unreproducible' AND r.status != 'reproducible') AND s.suite='$SUITE' AND s.architecture='$ARCH' AND date(r.build_date)<='$DATE';")
+		RESDAY=$(sqlite3 -init ${INIT} ${PACKAGES_DB} "SELECT count(r.status) FROM results AS r JOIN sources AS s ON r.package_id=s.id WHERE (r.status != 'FTBFS' AND r.status != 'unreproducible' AND r.status != 'reproducible') AND s.suite='$SUITE' AND s.architecture='$ARCH' AND date(r.build_date)='$DATE';")
+		OLDESTG=$(sqlite3 -init ${INIT} ${PACKAGES_DB} "SELECT r.build_date FROM results AS r JOIN sources AS s ON r.package_id=s.id WHERE r.status = 'reproducible' AND s.suite='$SUITE' AND s.architecture='$ARCH' AND NOT date(r.build_date)>='$DATE' ORDER BY r.build_date LIMIT 1;")
+		OLDESTB=$(sqlite3 -init ${INIT} ${PACKAGES_DB} "SELECT r.build_date FROM results AS r JOIN sources AS s ON r.package_id=s.id WHERE s.suite='$SUITE' AND s.architecture='$ARCH' AND r.status = 'unreproducible' AND NOT date(r.build_date)>='$DATE' ORDER BY r.build_date LIMIT 1;")
+		OLDESTU=$(sqlite3 -init ${INIT} ${PACKAGES_DB} "SELECT r.build_date FROM results AS r JOIN sources AS s ON r.package_id=s.id WHERE s.suite='$SUITE' AND s.architecture='$ARCH' AND r.status = 'FTBFS' AND NOT date(r.build_date)>='$DATE' ORDER BY r.build_date LIMIT 1;")
 		DIFFG=$(sqlite3 -init ${INIT} ${PACKAGES_DB} "SELECT julianday('$DATE') - julianday('$OLDESTG');")
 		if [ -z $DIFFG ] ; then DIFFG=0 ; fi
 		DIFFB=$(sqlite3 -init ${INIT} ${PACKAGES_DB} "SELECT julianday('$DATE') - julianday('$OLDESTB');")
@@ -94,9 +94,9 @@ update_suite_stats() {
 		else
 			UNTESTED=0
 		fi
-		sqlite3 -init ${INIT} ${PACKAGES_DB} "INSERT INTO ${TABLE[0]} VALUES (\"$DATE\", \"$SUITE\", $UNTESTED, $GOOD, $BAD, $UGLY, $REST)" 
-		sqlite3 -init ${INIT} ${PACKAGES_DB} "INSERT INTO ${TABLE[1]} VALUES (\"$DATE\", \"$SUITE\", $GOOAY, $BAAY, $UGLDAY, $RESDAY)"
-		sqlite3 -init ${INIT} ${PACKAGES_DB} "INSERT INTO ${TABLE[2]} VALUES (\"$DATE\", \"$SUITE\", \"$DIFFG\", \"$DIFFB\", \"$DIFFU\")"
+		sqlite3 -init ${INIT} ${PACKAGES_DB} "INSERT INTO ${TABLE[0]} VALUES (\"$DATE\", \"$SUITE\", \"$ARCH\", $UNTESTED, $GOOD, $BAD, $UGLY, $REST)" 
+		sqlite3 -init ${INIT} ${PACKAGES_DB} "INSERT INTO ${TABLE[1]} VALUES (\"$DATE\", \"$SUITE\", \"$ARCH\", $GOOAY, $BAAY, $UGLDAY, $RESDAY)"
+		sqlite3 -init ${INIT} ${PACKAGES_DB} "INSERT INTO ${TABLE[2]} VALUES (\"$DATE\", \"$SUITE\", \"$ARCH\", \"$DIFFG\", \"$DIFFB\", \"$DIFFU\")"
 		# we do 3 later and 6 is special anyway...
 		for i in 0 1 2 4 5 ; do
 			PREFIX=""
@@ -241,15 +241,15 @@ write_usertag_table() {
 #
 # create suite stats page
 #
-create_suite_stats_page() {
-	VIEW=suite_stats
+create_suite_arch_stats_page() {
+	VIEW=suite_${ARCH}_stats
 	PAGE=index_${VIEW}.html
-	MAINLABEL[0]="Reproducibility status for packages in '$SUITE'"
-	MAINLABEL[2]="Age in days of oldest reproducible build result in '$SUITE'"
+	MAINLABEL[0]="Reproducibility status for packages in '$SUITE' for '$ARCH'"
+	MAINLABEL[2]="Age in days of oldest reproducible build result in '$SUITE' for '$ARCH'"
 	echo "$(date) - starting to write $PAGE page."
-	write_page_header $VIEW "Overview of reproducible builds for packages in $SUITE"
+	write_page_header $VIEW "Overview of reproducible builds for packages in $SUITE for $ARCH"
 	if [ $(echo $PERCENT_TOTAL/1|bc) -lt 98 ] ; then
-		write_page "<p>$COUNT_TOTAL packages have been attempted to be build so far, that's $PERCENT_TOTAL% of $AMOUNT source packages in Debian $SUITE.</p>"
+		write_page "<p>$COUNT_TOTAL packages have been attempted to be build so far, that's $PERCENT_TOTAL% of $AMOUNT source packages in Debian $SUITE/$ARCH.</p>"
 	fi
 	write_page "<p>"
 	set_icon reproducible
@@ -282,11 +282,11 @@ create_suite_stats_page() {
 	write_icon
 	write_page "$COUNT_BLACKLISTED blacklisted packages neither.</p>"
 	write_page "<p>"
-	write_page " <a href=\"/userContent/$SUITE/${TABLE[0]}.png\"><img src=\"/userContent/$SUITE/${TABLE[0]}.png\" alt=\"${MAINLABEL[0]}\"></a>"
+	write_page " <a href=\"/userContent/$SUITE/$ARCH/${TABLE[0]}.png\"><img src=\"/userContent/$SUITE/$ARCH/${TABLE[0]}.png\" alt=\"${MAINLABEL[0]}\"></a>"
 	for i in 0 2 ; do
 		# recreate png once a day
-		if [ ! -f $BASE/$SUITE/${TABLE[$i]}.png ] || [ ! -z $(find $BASE/$SUITE -maxdepth 1 -mtime +0 -name ${TABLE[$i]}.png) ] ; then
-			create_png_from_table $i $SUITE/${TABLE[$i]}.png
+		if [ ! -f $BASE/$SUITE/$ARCH/${TABLE[$i]}.png ] || [ ! -z $(find $BASE/$SUITE/$ARCH -maxdepth 1 -mtime +0 -name ${TABLE[$i]}.png) ] ; then
+			create_png_from_table $i $SUITE/$ARCH/${TABLE[$i]}.png
 		fi
 	done
 	write_page "</p>"
@@ -397,9 +397,9 @@ create_main_stats_page() {
 	done
 	# write build performace stats
 	write_page "<table class=\"main\"><tr><th colspan=\"2\">Build statistics</th></tr>"
-	AGE_TESTING=$(sqlite3 -init ${INIT} ${PACKAGES_DB} "SELECT CAST(max(oldest_reproducible, oldest_unreproducible, oldest_FTBFS) AS INTEGER) FROM ${TABLE[2]} WHERE suite='testing' AND datum='$DATE'")
-	AGE_UNSTABLE=$(sqlite3 -init ${INIT} ${PACKAGES_DB} "SELECT CAST(max(oldest_reproducible, oldest_unreproducible, oldest_FTBFS) AS INTEGER) FROM ${TABLE[2]} WHERE suite='unstable' AND datum='$DATE'")
-	AGE_EXPERIMENTAL=$(sqlite3 -init ${INIT} ${PACKAGES_DB} "SELECT CAST(max(oldest_reproducible, oldest_unreproducible, oldest_FTBFS) AS INTEGER) FROM ${TABLE[2]} WHERE suite='experimental' AND datum='$DATE'")
+	AGE_TESTING=$(sqlite3 -init ${INIT} ${PACKAGES_DB} "SELECT CAST(max(oldest_reproducible, oldest_unreproducible, oldest_FTBFS) AS INTEGER) FROM ${TABLE[2]} WHERE suite='testing' AND architecture='$ARCH' AND datum='$DATE'")
+	AGE_UNSTABLE=$(sqlite3 -init ${INIT} ${PACKAGES_DB} "SELECT CAST(max(oldest_reproducible, oldest_unreproducible, oldest_FTBFS) AS INTEGER) FROM ${TABLE[2]} WHERE suite='unstable' AND architecture='$ARCH' AND datum='$DATE'")
+	AGE_EXPERIMENTAL=$(sqlite3 -init ${INIT} ${PACKAGES_DB} "SELECT CAST(max(oldest_reproducible, oldest_unreproducible, oldest_FTBFS) AS INTEGER) FROM ${TABLE[2]} WHERE suite='experimental' AND architecture='$ARCH' AND datum='$DATE'")
 	write_page "<tr><td>oldest build result in testing / unstable / experimental</td><td>$AGE_TESTING / $AGE_UNSTABLE / $AGE_EXPERIMENTAL days</td></tr>"
 	RESULT=$(sqlite3 -init ${INIT} ${PACKAGES_DB} "SELECT CAST(AVG(r.build_duration) AS INTEGER) FROM results AS r WHERE r.build_duration!='' AND r.build_duration!='0' AND r.build_date LIKE '%$DATE%'")
 	MIN=$(echo $RESULT/60|bc)
@@ -432,9 +432,9 @@ update_bug_stats
 update_notes_stats
 for ARCH in ${ARCHS} ; do
 	for SUITE in $SUITES ; do
-		update_suite_stats
+		update_suite_arch_stats
 		gather_suite_arch_stats
-		create_suite_stats_page
+		create_suite_arch_stats_page
 	done
 done
 ARCH="amd64"