reproducible debian: add and use query_to_csv bash function

Signed-off-by: Mattia Rizzolo <mattia@debian.org>
Signed-off-by: Holger Levsen <holger@layer-acht.org>

1 files changed, 29 insertions, 7 deletions

diff --git a/bin/reproducible_common.sh b/bin/reproducible_common.sh
index 07a209e2..b4af8375 100755
--- a/bin/reproducible_common.sh
+++ b/bin/reproducible_common.sh
@@ -39,6 +39,11 @@ query_db() {
 	sqlite3 -init ${INIT} ${PACKAGES_DB} "$@"
 }
 
+# query reproducible database, output to csv format
+query_to_csv() {
+	sqlite3 -init ${INIT} -csv ${PACKAGES_DB} "$@"
+}
+
 # common variables
 REPRODUCIBLE_URL=https://tests.reproducible-builds.org
 DEBIAN_URL=https://tests.reproducible-builds.org/debian
@@ -683,7 +688,23 @@ create_png_from_table() {
 	if [ $1 -eq 1 ] ; then
 		# not sure if it's worth to generate the following query...
 		WHERE_EXTRA="AND architecture='$ARCH'"
-		sqlite3 -init ${INIT} --nullvalue 0 -csv ${PACKAGES_DB} "SELECT s.datum,
+
+		# This query becomes much more obnoxious when gaining
+		# compatibility with postgres
+		query_to_csv "SELECT stats.datum,
+			 COALESCE(reproducible_testing,0) AS reproducible_testing,
+			 COALESCE(reproducible_unstable,0) AS reproducible_unstable,
+			 COALESCE(reproducible_experimental,0) AS reproducible_experimental,
+			 COALESCE(unreproducible_testing,0) AS unreproducible_testing,
+			 COALESCE(unreproducible_unstable,0) AS unreproducible_unstable,
+			 COALESCE(unreproducible_experimental,0) AS unreproducible_experimental,
+			 COALESCE(FTBFS_testing,0) AS FTBFS_testing,
+			 COALESCE(FTBFS_unstable,0) AS FTBFS_unstable,
+			 COALESCE(FTBFS_experimental,0) AS FTBFS_experimental,
+			 COALESCE(other_testing,0) AS other_testing,
+			 COALESCE(other_unstable,0) AS other_unstable,
+			 COALESCE(other_experimental,0) AS other_experimental
+			FROM (SELECT s.datum,
 			 COALESCE((SELECT e.reproducible FROM stats_builds_per_day AS e WHERE s.datum=e.datum AND suite='testing' $WHERE_EXTRA),0) AS reproducible_testing,
 			 COALESCE((SELECT e.reproducible FROM stats_builds_per_day AS e WHERE s.datum=e.datum AND suite='unstable' $WHERE_EXTRA),0) AS reproducible_unstable,
 			 COALESCE((SELECT e.reproducible FROM stats_builds_per_day AS e WHERE s.datum=e.datum AND suite='experimental' $WHERE_EXTRA),0) AS reproducible_experimental,
@@ -696,18 +717,19 @@ create_png_from_table() {
 			 (SELECT e.other FROM stats_builds_per_day e WHERE s.datum=e.datum AND suite='testing' $WHERE_EXTRA) AS other_testing,
 			 (SELECT e.other FROM stats_builds_per_day e WHERE s.datum=e.datum AND suite='unstable' $WHERE_EXTRA) AS other_unstable,
 			 (SELECT e.other FROM stats_builds_per_day e WHERE s.datum=e.datum AND suite='experimental' $WHERE_EXTRA) AS other_experimental
-			 FROM stats_builds_per_day AS s $WHERE2_EXTRA GROUP BY s.datum" >> ${TABLE[$1]}.csv
+			 FROM stats_builds_per_day AS s $WHERE2_EXTRA GROUP BY s.datum) as stats
+			ORDER BY datum" >> ${TABLE[$1]}.csv
 	elif [ $1 -eq 2 ] ; then
 		# just make a graph of the oldest reproducible build (ignore FTBFS and unreproducible)
-		sqlite3 -init ${INIT} -csv ${PACKAGES_DB} "SELECT datum, oldest_reproducible FROM ${TABLE[$1]} ${WHERE_EXTRA} ORDER BY datum" >> ${TABLE[$1]}.csv
+		query_to_csv "SELECT datum, oldest_reproducible FROM ${TABLE[$1]} ${WHERE_EXTRA} ORDER BY datum" >> ${TABLE[$1]}.csv
 	elif [ $1 -eq 7 ] ; then
-		sqlite3 -init ${INIT} -csv ${PACKAGES_DB} "SELECT datum, $SUM_DONE, $SUM_OPEN from ${TABLE[3]} ORDER BY datum" >> ${TABLE[$1]}.csv
+		query_to_csv "SELECT datum, $SUM_DONE, $SUM_OPEN from ${TABLE[3]} ORDER BY datum" >> ${TABLE[$1]}.csv
 	elif [ $1 -eq 8 ] ; then
-		sqlite3 -init ${INIT} -csv ${PACKAGES_DB} "SELECT ${FIELDS[$1]} from ${TABLE[3]} ${WHERE_EXTRA} ORDER BY datum" >> ${TABLE[$1]}.csv
+		query_to_csv "SELECT ${FIELDS[$1]} from ${TABLE[3]} ${WHERE_EXTRA} ORDER BY datum" >> ${TABLE[$1]}.csv
 	elif [ $1 -eq 9 ] ; then
-		sqlite3 -init ${INIT} -csv ${PACKAGES_DB} "SELECT datum, $REPRODUCIBLE_DONE, $REPRODUCIBLE_OPEN from ${TABLE[3]} ORDER BY datum" >> ${TABLE[$1]}.csv
+		query_to_csv "SELECT datum, $REPRODUCIBLE_DONE, $REPRODUCIBLE_OPEN from ${TABLE[3]} ORDER BY datum" >> ${TABLE[$1]}.csv
 	else
-		sqlite3 -init ${INIT} -csv ${PACKAGES_DB} "SELECT ${FIELDS[$1]} from ${TABLE[$1]} ${WHERE_EXTRA} ORDER BY datum" >> ${TABLE[$1]}.csv
+		query_to_csv "SELECT ${FIELDS[$1]} from ${TABLE[$1]} ${WHERE_EXTRA} ORDER BY datum" >> ${TABLE[$1]}.csv
 	fi
 	# this is a gross hack: normally we take the number of colors a table should have...
 	#  for the builds_age table we only want one color, but different ones, so this hack: