[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