From 21ce98cd746a35df07891084baea6bbde1876166 Mon Sep 17 00:00:00 2001 From: Valerie R Young Date: Fri, 16 Sep 2016 21:09:49 -0400 Subject: 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 Signed-off-by: Holger Levsen --- 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(-) (limited to 'bin') diff --git a/bin/reproducible_blacklist.sh b/bin/reproducible_blacklist.sh index bbd9b7fc..5e94e962 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 b730d4c6..b2d617fa 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 1adc0ff5..dcabec14 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@lists.alioth.debian.org status:open status:forwarded 2>/dev/null|wc -l) DONE[$TAG]=$(bts select usertag:$TAG users:reproducible-builds@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 "average test duration (in the last $TIMESPAN_VERBOSE)" 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 "$MIN minutes, $SEC seconds" @@ -322,20 +325,23 @@ write_build_performance_stats() { done write_page "packages tested in the last 24h" 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 "$RESULT" done write_page "packages tested on average per day in the last $TIMESPAN_VERBOSE" 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 "$RESULT" 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 "packages tested on average per day in the last $TIMESPAN_VERBOSE" 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 "$RESULT" done diff --git a/bin/reproducible_maintenance.sh b/bin/reproducible_maintenance.sh index 5450395a..6ff3d106 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 f7ab93fd..034b0b02 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 -- cgit v1.2.3-54-g00ecf