[Qa-jenkins-scm] [jenkins.debian.net] 03/04: reproducible debian: make bash sql sqlite/postgres agnostic

Holger Levsen holger at layer-acht.org
Tue Nov 8 15:53:38 UTC 2016


This is an automated email from the git hooks/post-receive script.

holger pushed a commit to branch master
in repository jenkins.debian.net.

commit 21ce98cd746a35df07891084baea6bbde1876166
Author: Valerie R Young <spectranaut at riseup.net>
Date:   Fri Sep 16 21:09:49 2016 -0400

    reproducible debian: make bash sql sqlite/postgres agnostic
    
    This commit does the following for all bash scripts:
    - remove sqlite specfic datetime functions in sql queries
    - remove double quotes from sql queries
    - replace the "REPLACE INTO" sql queries
    
    Signed-off-by: Mattia Rizzolo <mattia at debian.org>
    Signed-off-by: Holger Levsen <holger at layer-acht.org>
---
 bin/reproducible_blacklist.sh      |  7 +++-
 bin/reproducible_build.sh          | 15 +++++---
 bin/reproducible_html_dashboard.sh | 70 +++++++++++++++++++++-----------------
 bin/reproducible_maintenance.sh    |  3 +-
 bin/reproducible_nodes_info.sh     |  7 ++--
 5 files changed, 61 insertions(+), 41 deletions(-)

diff --git a/bin/reproducible_blacklist.sh b/bin/reproducible_blacklist.sh
index bbd9b7f..5e94e96 100755
--- a/bin/reproducible_blacklist.sh
+++ b/bin/reproducible_blacklist.sh
@@ -17,7 +17,12 @@ blacklist_packages() {
 		VERSION=$(query_db "SELECT version FROM sources WHERE name='$PKG' AND suite='$SUITE' AND architecture='$ARCH';")
 		PKGID=$(query_db "SELECT id FROM sources WHERE name='$PKG' AND suite='$SUITE' AND architecture='$ARCH';")
 		cleanup_pkg_files
-		query_db "REPLACE INTO results (package_id, version, status, build_date, job) VALUES ('$PKGID', '$VERSION', 'blacklisted', '$DATE', '');"
+		RESULTID=$(query_db "SELECT id FROM results WHERE package_id=$PKGID")
+		if [ ! -z "$RESULTID" ] ; then
+			query_db "UPDATE results set package_id='$PKGID', version='$VERSION', status='blacklisted', build_date='$DATE', job='' WHERE id=$RESULTID;"
+		else
+			query_db "INSERT into results (package_id, version, status, build_date, job) VALUES ('$PKGID', '$VERSION', 'blacklisted', '$DATE', '');"
+		fi
 		query_db "DELETE FROM schedule WHERE package_id='$PKGID'"
 	done
 }
diff --git a/bin/reproducible_build.sh b/bin/reproducible_build.sh
index b730d4c..b2d617f 100755
--- a/bin/reproducible_build.sh
+++ b/bin/reproducible_build.sh
@@ -145,8 +145,15 @@ update_db_and_html() {
 			echo "$(date -u +'%Y-%m-%d %H:%M') $DEBIAN_URL/$SUITE/$ARCH/$SRCPACKAGE changed from $OLD_STATUS -> $STATUS" >> /srv/reproducible-results/notification-emails/$SRCPACKAGE
 		fi
 	fi
-	query_db "REPLACE INTO results (package_id, version, status, build_date, build_duration, node1, node2, job) VALUES ('$SRCPKGID', '$VERSION', '$STATUS', '$DATE', '$DURATION', '$NODE1', '$NODE2', '$JOB')" || \
-	query_db "REPLACE INTO results (package_id, version, status, build_date, build_duration, node1, node2, job) VALUES ('$SRCPKGID', '$VERSION', '$STATUS', '$DATE', '$DURATION', '$NODE1', '$NODE2', '$JOB')"
+	RESULTID=$(query_db "SELECT id FROM results WHERE package_id=$SRCPKGID")
+	# Insert or replace existing entry in results table
+	if [ ! -z "$RESULTID" ] ; then
+		query_db "UPDATE results set package_id='$SRCPKGID', version='$VERSION', status='$STATUS', build_date='$DATE', build_duration='$DURATION', node1='$NODE1', node2='$NODE2', job='$JOB' WHERE id=$RESULTID" || \
+		query_db "UPDATE results set package_id='$SRCPKGID', version='$VERSION', status='$STATUS', build_date='$DATE', build_duration='$DURATION', node1='$NODE1', node2='$NODE2', job='$JOB' WHERE id=$RESULTID"
+	else
+		query_db "INSERT INTO results (package_id, version, status, build_date, build_duration, node1, node2, job) VALUES ('$SRCPKGID', '$VERSION', '$STATUS', '$DATE', '$DURATION', '$NODE1', '$NODE2', '$JOB')" || \
+		query_db "INSERT INTO results (package_id, version, status, build_date, build_duration, node1, node2, job) VALUES ('$SRCPKGID', '$VERSION', '$STATUS', '$DATE', '$DURATION', '$NODE1', '$NODE2', '$JOB')"
+	fi
 	if [ ! -z "$DURATION" ] ; then  # this happens when not 404 and not_for_us
 		query_db "INSERT INTO stats_build (name, version, suite, architecture, status, build_date, build_duration, node1, node2, job, schedule_message) VALUES ('$SRCPACKAGE', '$VERSION', '$SUITE', '$ARCH', '$STATUS', '$DATE', '$DURATION', '$NODE1', '$NODE2', '$JOB', '$SCHEDULE_MESSAGE')" || \
 		query_db "INSERT INTO stats_build (name, version, suite, architecture, status, build_date, build_duration, node1, node2, job, schedule_message) VALUES ('$SRCPACKAGE', '$VERSION', '$SUITE', '$ARCH', '$STATUS', '$DATE', '$DURATION', '$NODE1', '$NODE2', '$JOB', '$SCHEDULE_MESSAGE')"
@@ -195,7 +202,7 @@ handle_404() {
 	log_warning "Download of ${SRCPACKAGE} sources from ${SUITE} failed."
 	ls -l ${SRCPACKAGE}* | log_file -
 	log_warning "Maybe there was a network problem, or ${SRCPACKAGE} is not a source package in ${SUITE}, or it was removed or renamed. Please investigate. Sleeping 30m as this should not happen."
-	DURATION=''
+	DURATION=0
 	EVERSION="None"
 	update_rbuildlog
 	update_db_and_html "404"
@@ -218,7 +225,7 @@ handle_depwait() {
 handle_not_for_us() {
 	# a list of valid architecture for this package should be passed to this function
 	log_info "Package ${SRCPACKAGE} (${VERSION}) shall only be build on \"$(echo "$@" | xargs echo )\" and thus was skipped."
-	DURATION=''
+	DURATION=0
 	update_rbuildlog
 	update_db_and_html "not for us"
 	if [ $SAVE_ARTIFACTS -eq 1 ] ; then SAVE_ARTIFACTS=0 ; fi
diff --git a/bin/reproducible_html_dashboard.sh b/bin/reproducible_html_dashboard.sh
index 1adc0ff..dcabec1 100755
--- a/bin/reproducible_html_dashboard.sh
+++ b/bin/reproducible_html_dashboard.sh
@@ -103,7 +103,7 @@ YLABEL[9]="Amount of bugs open / closed"
 # update package + build stats
 #
 update_suite_arch_stats() {
-	RESULT=$(query_db "SELECT datum,suite from ${TABLE[0]} WHERE datum = \"$DATE\" AND suite = \"$SUITE\" AND architecture = \"$ARCH\"")
+	RESULT=$(query_db "SELECT datum,suite from ${TABLE[0]} WHERE datum = '$DATE' AND suite = '$SUITE' AND architecture = '$ARCH'")
 	if [ -z $RESULT ] ; then
 		echo "Updating packages and builds stats for $SUITE/$ARCH in $DATE."
 		ALL=$(query_db "SELECT count(name) FROM sources WHERE suite='${SUITE}' AND architecture='$ARCH'")
@@ -130,9 +130,9 @@ update_suite_arch_stats() {
 		else
 			UNTESTED=0
 		fi
-		query_db "INSERT INTO ${TABLE[0]} VALUES (\"$DATE\", \"$SUITE\", \"$ARCH\", $UNTESTED, $GOOD, $BAD, $UGLY, $REST)" 
-		query_db "INSERT INTO ${TABLE[1]} VALUES (\"$DATE\", \"$SUITE\", \"$ARCH\", $GOOAY, $BAAY, $UGLDAY, $RESDAY)"
-		query_db "INSERT INTO ${TABLE[2]} VALUES (\"$DATE\", \"$SUITE\", \"$ARCH\", \"$DIFFG\", \"$DIFFB\", \"$DIFFU\")"
+		query_db "INSERT INTO ${TABLE[0]} VALUES ('$DATE', '$SUITE', '$ARCH', $UNTESTED, $GOOD, $BAD, $UGLY, $REST)"
+		query_db "INSERT INTO ${TABLE[1]} VALUES ('$DATE', '$SUITE', '$ARCH', $GOOAY, $BAAY, $UGLDAY, $RESDAY)"
+		query_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=""
@@ -152,30 +152,30 @@ update_suite_arch_stats() {
 # update notes stats
 #
 update_notes_stats() {
-	NOTES=$(query_db "SELECT COUNT(package_id) FROM notes AS n JOIN sources AS s ON n.package_id=s.id WHERE s.suite=\"unstable\" AND s.architecture=\"amd64\"")
+	NOTES=$(query_db "SELECT COUNT(package_id) FROM notes AS n JOIN sources AS s ON n.package_id=s.id WHERE s.suite='unstable' AND s.architecture='amd64'")
 	ISSUES=$(query_db "SELECT COUNT(name) FROM issues")
 	# the following is a hack to workaround the bad sql db design which is the issue_s_ column in the notes table...
 	# it assumes we don't have packages with more than 7 issues. (we have one with 6...)
 	COUNT_ISSUES=$(query_db "SELECT \
-		(SELECT COUNT(issues) FROM notes AS n JOIN sources AS s ON n.package_id=s.id WHERE s.suite=\"unstable\" AND s.architecture=\"amd64\" AND n.issues = \"[]\") \
+		(SELECT COUNT(issues) FROM notes AS n JOIN sources AS s ON n.package_id=s.id WHERE s.suite='unstable' AND s.architecture='amd64' AND n.issues = '[]') \
 		+ \
-		(SELECT COUNT(issues) FROM notes AS n JOIN sources AS s ON n.package_id=s.id WHERE s.suite=\"unstable\" AND s.architecture=\"amd64\" AND n.issues != \"[]\" AND n.issues NOT LIKE \"%,%\") \
+		(SELECT COUNT(issues) FROM notes AS n JOIN sources AS s ON n.package_id=s.id WHERE s.suite='unstable' AND s.architecture='amd64' AND n.issues != '[]' AND n.issues NOT LIKE '%,%') \
 		+ \
-		(SELECT COUNT(issues) FROM notes AS n JOIN sources AS s ON n.package_id=s.id WHERE s.suite=\"unstable\" AND s.architecture=\"amd64\" AND n.issues LIKE \"%,%\") \
+		(SELECT COUNT(issues) FROM notes AS n JOIN sources AS s ON n.package_id=s.id WHERE s.suite='unstable' AND s.architecture='amd64' AND n.issues LIKE '%,%') \
 		+ \
-		(SELECT COUNT(issues) FROM notes AS n JOIN sources AS s ON n.package_id=s.id WHERE s.suite=\"unstable\" AND s.architecture=\"amd64\" AND n.issues LIKE \"%,%,%\") \
+		(SELECT COUNT(issues) FROM notes AS n JOIN sources AS s ON n.package_id=s.id WHERE s.suite='unstable' AND s.architecture='amd64' AND n.issues LIKE '%,%,%') \
 		+ \
-		(SELECT COUNT(issues) FROM notes AS n JOIN sources AS s ON n.package_id=s.id WHERE s.suite=\"unstable\" AND s.architecture=\"amd64\" AND n.issues LIKE \"%,%,%,%\") \
+		(SELECT COUNT(issues) FROM notes AS n JOIN sources AS s ON n.package_id=s.id WHERE s.suite='unstable' AND s.architecture='amd64' AND n.issues LIKE '%,%,%,%') \
 		+ \
-		(SELECT COUNT(issues) FROM notes AS n JOIN sources AS s ON n.package_id=s.id WHERE s.suite=\"unstable\" AND s.architecture=\"amd64\" AND n.issues LIKE \"%,%,%,%,%\") \
+		(SELECT COUNT(issues) FROM notes AS n JOIN sources AS s ON n.package_id=s.id WHERE s.suite='unstable' AND s.architecture='amd64' AND n.issues LIKE '%,%,%,%,%') \
 		+ \
-		(SELECT COUNT(issues) FROM notes AS n JOIN sources AS s ON n.package_id=s.id WHERE s.suite=\"unstable\" AND s.architecture=\"amd64\" AND n.issues LIKE \"%,%,%,%,%,%\") \
+		(SELECT COUNT(issues) FROM notes AS n JOIN sources AS s ON n.package_id=s.id WHERE s.suite='unstable' AND s.architecture='amd64' AND n.issues LIKE '%,%,%,%,%,%') \
 		")
-	RESULT=$(query_db "SELECT datum from ${TABLE[4]} WHERE datum = \"$DATE\"")
+	RESULT=$(query_db "SELECT datum from ${TABLE[4]} WHERE datum = '$DATE'")
 	if [ -z $RESULT ] ; then
 		echo "Updating notes stats for $DATE."
-		query_db "INSERT INTO ${TABLE[4]} VALUES (\"$DATE\", \"$NOTES\")"
-		query_db "INSERT INTO ${TABLE[5]} VALUES (\"$DATE\", \"$ISSUES\")"
+		query_db "INSERT INTO ${TABLE[4]} VALUES ('$DATE', '$NOTES')"
+		query_db "INSERT INTO ${TABLE[5]} VALUES ('$DATE', '$ISSUES')"
 	fi
 }
 
@@ -183,15 +183,15 @@ update_notes_stats() {
 # gather suite/arch stats
 #
 gather_suite_arch_stats() {
-	AMOUNT=$(query_db "SELECT count(*) FROM sources WHERE suite=\"${SUITE}\" AND architecture=\"$ARCH\"")
-	COUNT_TOTAL=$(query_db "SELECT COUNT(*) FROM results AS r JOIN sources AS s ON r.package_id=s.id WHERE s.suite=\"${SUITE}\" AND s.architecture=\"$ARCH\"")
-	COUNT_GOOD=$(query_db "SELECT COUNT(*) 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\"")
-	COUNT_BAD=$(query_db "SELECT COUNT(s.name) 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\"")
-	COUNT_UGLY=$(query_db "SELECT COUNT(s.name) 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\"")
-	COUNT_SOURCELESS=$(query_db "SELECT COUNT(s.name) 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 = \"404\"")
-	COUNT_NOTFORUS=$(query_db "SELECT COUNT(s.name) 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 = \"not for us\"")
-	COUNT_BLACKLISTED=$(query_db "SELECT COUNT(s.name) 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 = \"blacklisted\"")
-	COUNT_DEPWAIT=$(query_db "SELECT COUNT(s.name) 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 = \"depwait\"")
+	AMOUNT=$(query_db "SELECT count(*) FROM sources WHERE suite='${SUITE}' AND architecture='$ARCH'")
+	COUNT_TOTAL=$(query_db "SELECT COUNT(*) FROM results AS r JOIN sources AS s ON r.package_id=s.id WHERE s.suite='$SUITE' AND s.architecture='$ARCH'")
+	COUNT_GOOD=$(query_db "SELECT COUNT(*) 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'")
+	COUNT_BAD=$(query_db "SELECT COUNT(s.name) 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'")
+	COUNT_UGLY=$(query_db "SELECT COUNT(s.name) 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'")
+	COUNT_SOURCELESS=$(query_db "SELECT COUNT(s.name) 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 = '404'")
+	COUNT_NOTFORUS=$(query_db "SELECT COUNT(s.name) 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 = 'not for us'")
+	COUNT_BLACKLISTED=$(query_db "SELECT COUNT(s.name) 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 = 'blacklisted'")
+	COUNT_DEPWAIT=$(query_db "SELECT COUNT(s.name) 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 = 'depwait'")
 	COUNT_OTHER=$(( $COUNT_SOURCELESS+$COUNT_NOTFORUS+$COUNT_BLACKLISTED+$COUNT_DEPWAIT ))
 	PERCENT_TOTAL=$(echo "scale=1 ; ($COUNT_TOTAL*100/$AMOUNT)" | bc)
 	PERCENT_GOOD=$(echo "scale=1 ; ($COUNT_GOOD*100/$COUNT_TOTAL)" | bc || echo 0)
@@ -208,13 +208,13 @@ gather_suite_arch_stats() {
 # update bug stats
 #
 update_bug_stats() {
-	RESULT=$(query_db "SELECT * from ${TABLE[3]} WHERE datum = \"$DATE\"")
+	RESULT=$(query_db "SELECT * from ${TABLE[3]} WHERE datum = '$DATE'")
 	if [ -z $RESULT ] ; then
 		echo "Updating bug stats for $DATE."
 		declare -a DONE
 		declare -a OPEN
 		GOT_BTS_RESULTS=false
-		SQL="INSERT INTO ${TABLE[3]} VALUES (\"$DATE\" "
+		SQL="INSERT INTO ${TABLE[3]} VALUES ('$DATE' "
 		for TAG in $USERTAGS ; do
 			OPEN[$TAG]=$(bts select usertag:$TAG users:reproducible-builds at lists.alioth.debian.org status:open status:forwarded 2>/dev/null|wc -l)
 			DONE[$TAG]=$(bts select usertag:$TAG users:reproducible-builds at lists.alioth.debian.org status:done archive:both 2>/dev/null|wc -l)
@@ -249,7 +249,7 @@ update_bug_stats() {
 # gather bugs stats and generate html table
 #
 write_usertag_table() {
-	RESULT=$(query_db "SELECT ${FIELDS[3]} from ${TABLE[3]} WHERE datum = \"$DATE\"")
+	RESULT=$(query_db "SELECT ${FIELDS[3]} from ${TABLE[3]} WHERE datum = '$DATE'")
 	if [ ! -z "$RESULT" ] ; then
 		COUNT=0
 		TOPEN=0 ; TDONE=0 ; TTOTAL=0
@@ -308,9 +308,12 @@ write_build_performance_stats() {
 	done
 	local TIMESPAN_VERBOSE="4 weeks"
 	local TIMESPAN_RAW="28"
+	# Find stats for 28 days since yesterday, no stats exist for today
+	local TIMESPAN="$(echo $TIMESPAN_RAW-1|bc)"
+	local TIMESPAN_DATE=$(date '+%Y-%m-%d %H:%M' -d "$TIMESPAN days")
 	write_page "</tr><tr><td class=\"left\">average test duration (in the last $TIMESPAN_VERBOSE)</td>"
 	for ARCH in ${ARCHS} ; do
-		RESULT=$(query_db "SELECT CAST(AVG(r.build_duration) AS INTEGER) FROM results AS r JOIN sources AS s ON r.package_id=s.id WHERE r.build_duration!='' AND r.build_duration!='0' AND r.build_date > datetime('$DATE', '-$TIMESPAN_RAW days') AND s.architecture='$ARCH'")
+		RESULT=$(query_db "SELECT CAST(AVG(r.build_duration) AS INTEGER) FROM results AS r JOIN sources AS s ON r.package_id=s.id WHERE r.build_duration!='' AND r.build_duration!='0' AND r.build_date > '$TIMESPAN_DATE' AND s.architecture='$ARCH'")
 		MIN=$(echo $RESULT/60|bc)
 		SEC=$(echo "$RESULT-($MIN*60)"|bc)
 		write_page "<td>$MIN minutes, $SEC seconds</td>"
@@ -322,20 +325,23 @@ write_build_performance_stats() {
 	done
 	write_page "</tr><tr><td class=\"left\">packages tested in the last 24h</td>"
 	for ARCH in ${ARCHS} ; do
-		RESULT=$(query_db "SELECT COUNT(r.build_date) FROM stats_build AS r WHERE r.build_date > datetime('$(date -u '+%Y-%m-%d %H:%m')', '-24 hours') AND r.architecture='$ARCH'")
+		RESULT=$(query_db "SELECT COUNT(r.build_date) FROM stats_build AS r WHERE r.build_date > '$(date '+%Y-%m-%d %H:%M' -d '-1 days')' AND r.architecture='$ARCH'")
 		write_page "<td>$RESULT</td>"
 	done
 	write_page "</tr><tr><td class=\"left\">packages tested on average per day in the last $TIMESPAN_VERBOSE</td>"
 	for ARCH in ${ARCHS} ; do
-		RESULT=$(query_db "SELECT COUNT(r.build_date) FROM stats_build AS r WHERE r.build_date > datetime('$DATE', '-$TIMESPAN_RAW days') AND r.architecture='$ARCH'")
+		RESULT=$(query_db "SELECT COUNT(r.build_date) FROM stats_build AS r WHERE r.build_date > '$TIMESPAN_DATE' AND r.architecture='$ARCH'")
 		RESULT="$(echo $RESULT/$TIMESPAN_RAW|bc)"
 		write_page "<td>$RESULT</td>"
 	done
 	local TIMESPAN_VERBOSE="3 months"
-	local TIMESPAN_RAW="91.5"
+	local TIMESPAN_RAW="91"
+	# Find stats for 91 days since yesterday, no stats exist for today
+	local TIMESPAN="$(echo $TIMESPAN_RAW-1|bc)"
+	local TIMESPAN_DATE=$(date '+%Y-%m-%d %H:%M' -d "$TIMESPAN days")
 	write_page "</tr><tr><td class=\"left\">packages tested on average per day in the last $TIMESPAN_VERBOSE</td>"
 	for ARCH in ${ARCHS} ; do
-		RESULT=$(query_db "SELECT COUNT(r.build_date) FROM stats_build AS r WHERE r.build_date > datetime('$DATE', '-$TIMESPAN_RAW days') AND r.architecture='$ARCH'")
+		RESULT=$(query_db "SELECT COUNT(r.build_date) FROM stats_build AS r WHERE r.build_date > '$TIMESPAN_DATE' AND r.architecture='$ARCH'")
 		RESULT="$(echo $RESULT/$TIMESPAN_RAW|bc)"
 		write_page "<td>$RESULT</td>"
 	done
diff --git a/bin/reproducible_maintenance.sh b/bin/reproducible_maintenance.sh
index 5450395..6ff3d10 100755
--- a/bin/reproducible_maintenance.sh
+++ b/bin/reproducible_maintenance.sh
@@ -249,12 +249,13 @@ if [ "$HOSTNAME" = "$MAINNODE" ] ; then
 	# find packages which build didnt end correctly
 	#
 	echo "$(date -u) - Rescheduling builds which didn't end correctly."
+	DATE=$(date '+%Y-%m-%d %H:%M' -d "-2 days")
 	QUERY="
 		SELECT s.id, s.name, p.date_scheduled, p.date_build_started
 			FROM schedule AS p JOIN sources AS s ON p.package_id=s.id
 			WHERE p.date_scheduled != ''
 			AND p.date_build_started IS NOT NULL
-			AND p.date_build_started < datetime('now', '-48 hours')
+			AND p.date_build_started < '$DATE'
 			ORDER BY p.date_scheduled
 		"
 	PACKAGES=$(mktemp --tmpdir=$TEMPDIR maintenance-XXXXXXXXXXXX)
diff --git a/bin/reproducible_nodes_info.sh b/bin/reproducible_nodes_info.sh
index f7ab93f..034b0b0 100755
--- a/bin/reproducible_nodes_info.sh
+++ b/bin/reproducible_nodes_info.sh
@@ -57,13 +57,14 @@ TMPFILE2=$(mktemp)
 TMPFILE3=$(mktemp)
 NOW=$(date -u '+%Y-%m-%d %H:%m')
 for i in $BUILD_NODES ; do
-	query_db "SELECT build_date FROM stats_build AS r WHERE ( r.node1=\"$i\" OR r.node2=\"$i\" )" > $TMPFILE1 2>/dev/null
+	query_db "SELECT build_date FROM stats_build AS r WHERE ( r.node1='$i' OR r.node2='$i' )" > $TMPFILE1 2>/dev/null
 	j=$(wc -l $TMPFILE1|cut -d " " -f1)
 	k=$(cat $TMPFILE1|cut -d " " -f1|sort -u|wc -l)
 	l=$(echo "scale=1 ; ($j/$k)" | bc)
 	echo "$l builds/day ($j/$k) on $i" >> $TMPFILE2
-	m=$(query_db "SELECT count(build_date) FROM stats_build AS r WHERE ( r.node1=\"$i\" OR r.node2=\"$i\" ) AND r.build_date > datetime('$NOW', '-24 hours') " 2>/dev/null)
-	echo "$m builds in the last 24h on $i" >> $TMPFILE3 
+	DATE=$(date '+%Y-%m-%d %H:%M' -d "-1 days")
+	m=$(query_db "SELECT count(build_date) FROM stats_build AS r WHERE ( r.node1='$i' OR r.node2='$i' ) AND r.build_date > '$DATE' " 2>/dev/null)
+	echo "$m builds in the last 24h on $i" >> $TMPFILE3
 done
 rm $TMPFILE1 >/dev/null
 sort -g -r $TMPFILE2

-- 
Alioth's /usr/local/bin/git-commit-notice on /srv/git.debian.org/git/qa/jenkins.debian.net.git



More information about the Qa-jenkins-scm mailing list