diff options
| -rwxr-xr-x | bin/reproducible_common.sh | 34 | ||||
| -rwxr-xr-x | bin/reproducible_html_dashboard.sh | 24 | ||||
| -rwxr-xr-x | bin/reproducible_maintenance.sh | 27 |
3 files changed, 31 insertions, 54 deletions
diff --git a/bin/reproducible_common.sh b/bin/reproducible_common.sh index 7a4d523d..166332f7 100755 --- a/bin/reproducible_common.sh +++ b/bin/reproducible_common.sh @@ -7,41 +7,17 @@ # included by all reproducible_*.sh scripts, so be quiet set +x -# define db -PACKAGES_DB=/var/lib/jenkins/reproducible.db -INIT=/var/lib/jenkins/reproducible.init -MAINNODE="jenkins" # host which contains reproducible.db -if [ -f $PACKAGES_DB ] && [ -f $INIT ] ; then - if [ -f ${PACKAGES_DB}.lock ] ; then - for i in $(seq 0 200) ; do - sleep 15 - echo "sleeping 15s, $PACKAGES_DB is locked." - if [ ! -f ${PACKAGES_DB}.lock ] ; then - break - fi - done - if [ -f ${PACKAGES_DB}.lock ] ; then - echo "${PACKAGES_DB}.lock still exist, exiting." - exit 1 - fi - fi -elif [ ! -f ${PACKAGES_DB} ] && [ "$HOSTNAME" = "$MAINNODE" ] ; then - echo "Warning: $PACKAGES_DB doesn't exist, creating it now." - /srv/jenkins/bin/reproducible_db_maintenance.py - # 60 seconds timeout when trying to get a lock - cat > $INIT <<-EOF -.timeout 60000 -EOF -fi +# postgres database definitions +export PGDATABASE=reproducibledb # query reproducible database query_db() { - sqlite3 -init ${INIT} ${PACKAGES_DB} "$@" + psql -t --no-align -c "$@" } # query reproducible database, output to csv format query_to_csv() { - sqlite3 -init ${INIT} -csv ${PACKAGES_DB} "$@" + psql -c "COPY ($@) to STDOUT with csv DELIMITER ','" } # common variables @@ -674,7 +650,7 @@ create_png_from_table() { fi if [ $1 -eq 0 ] || [ $1 -eq 2 ] ; then # TABLE[0+2] have a architecture column: - WHERE_EXTRA="$WHERE_EXTRA AND architecture = \"$ARCH\"" + WHERE_EXTRA="$WHERE_EXTRA AND architecture = '$ARCH'" if [ "$ARCH" = "armhf" ] ; then if [ $1 -eq 2 ] ; then # unstable/armhf was only build since 2015-08-30 (and experimental/armhf since 2015-12-19 and testing/armhf since 2016-01-01) diff --git a/bin/reproducible_html_dashboard.sh b/bin/reproducible_html_dashboard.sh index 9ee02f29..e754104f 100755 --- a/bin/reproducible_html_dashboard.sh +++ b/bin/reproducible_html_dashboard.sh @@ -121,11 +121,11 @@ update_suite_arch_stats() { OLDESTG=$(query_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=$(query_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=$(query_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=$(query_db "SELECT julianday('$DATE') - julianday('$OLDESTG');") + DIFFG=$(query_db "SELECT (date '$DATE' - date '$OLDESTG');") if [ -z $DIFFG ] ; then DIFFG=0 ; fi - DIFFB=$(query_db "SELECT julianday('$DATE') - julianday('$OLDESTB');") + DIFFB=$(query_db "SELECT (date '$DATE' - date '$OLDESTB');") if [ -z $DIFFB ] ; then DIFFB=0 ; fi - DIFFU=$(query_db "SELECT julianday('$DATE') - julianday('$OLDESTU');") + DIFFU=$(query_db "SELECT (date '$DATE' - date '$OLDESTU');") if [ -z $DIFFU ] ; then DIFFU=0 ; fi let "TOTAL=GOOD+BAD+UGLY+REST" || true # let FOO=0+0 returns error in bash... if [ "$ALL" != "$TOTAL" ] ; then @@ -236,7 +236,7 @@ update_bug_stats() { SQL="$SQL)" echo $SQL if $GOT_BTS_RESULTS ; then - echo "Updating ${PACKAGES_DB} with bug stats for $DATE." + echo "Updating database with bug stats for $DATE." query_db "$SQL" # force regeneration of the image local i=0 @@ -297,14 +297,14 @@ write_build_performance_stats() { done write_page "</tr><tr><td class=\"left\">oldest build result in testing / unstable / experimental</td>" for ARCH in ${ARCHS} ; do - AGE_UNSTABLE=$(query_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=$(query_db "SELECT CAST(max(oldest_reproducible, oldest_unreproducible, oldest_FTBFS) AS INTEGER) FROM ${TABLE[2]} WHERE suite='experimental' AND architecture='$ARCH' AND datum='$DATE'") - AGE_TESTING=$(query_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=$(query_db "SELECT CAST(greatest(max(oldest_reproducible), max(oldest_unreproducible), max(oldest_FTBFS)) AS INTEGER) FROM ${TABLE[2]} WHERE suite='unstable' AND architecture='$ARCH' AND datum='$DATE'") + AGE_EXPERIMENTAL=$(query_db "SELECT CAST(greatest(max(oldest_reproducible), max(oldest_unreproducible), max(oldest_FTBFS)) AS INTEGER) FROM ${TABLE[2]} WHERE suite='experimental' AND architecture='$ARCH' AND datum='$DATE'") + AGE_TESTING=$(query_db "SELECT CAST(greatest(max(oldest_reproducible), max(oldest_unreproducible), max(oldest_FTBFS)) AS INTEGER) FROM ${TABLE[2]} WHERE suite='testing' AND architecture='$ARCH' AND datum='$DATE'") write_page "<td>$AGE_TESTING / $AGE_UNSTABLE / $AGE_EXPERIMENTAL days</td>" done write_page "</tr><tr><td class=\"left\">average test duration (on $DATE)</td>" for ARCH in ${ARCHS} ; do - RESULT=$(query_db "SELECT COALESCE(CAST(AVG(r.build_duration) AS INTEGER), 0) 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 LIKE '%$DATE%' AND s.architecture='$ARCH'") + RESULT=$(query_db "SELECT COALESCE(CAST(AVG(r.build_duration) AS INTEGER), 0) FROM results AS r JOIN sources AS s ON r.package_id=s.id WHERE r.build_duration!='0' AND r.build_date LIKE '%$DATE%' AND s.architecture='$ARCH'") MIN=$(echo $RESULT/60|bc) SEC=$(echo "$RESULT-($MIN*60)"|bc) write_page "<td>$MIN minutes, $SEC seconds</td>" @@ -318,7 +318,7 @@ write_build_performance_stats() { 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 COALESCE(CAST(AVG(r.build_duration) AS INTEGER), 0) 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'") + RESULT=$(query_db "SELECT COALESCE(CAST(AVG(r.build_duration) AS INTEGER), 0) FROM results AS r JOIN sources AS s ON r.package_id=s.id WHERE 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>" @@ -522,11 +522,11 @@ create_dashboard_page() { gather_suite_arch_stats TD_PKG_SID_ISSUES="$TD_PKG_SID_ISSUES<td>$(echo $COUNT_BAD + $COUNT_UGLY |bc) / $(echo $PERCENT_BAD + $PERCENT_UGLY|bc)%</td>" - RESULT=$(query_db "SELECT COUNT(*) FROM (SELECT s.id FROM sources AS s JOIN results AS r ON r.package_id=s.id WHERE r.status IN ('unreproducible', 'FTBFS', 'blacklisted') AND s.id NOT IN (SELECT package_id FROM notes) AND s.suite='$SUITE' AND s.architecture='$ARCH')") + RESULT=$(query_db "SELECT COUNT(*) FROM (SELECT s.id FROM sources AS s JOIN results AS r ON r.package_id=s.id WHERE r.status IN ('unreproducible', 'FTBFS', 'blacklisted') AND s.id NOT IN (SELECT package_id FROM notes) AND s.suite='$SUITE' AND s.architecture='$ARCH') tmp") TD_PKG_SID_NOISSUES="$TD_PKG_SID_NOISSUES<td><a href=\"/debian/$SUITE/$ARCH/index_no_notes.html\">$RESULT</a> / $(echo "scale=1 ; ($RESULT*100/$COUNT_TOTAL)" | bc)%</td>" - RESULT=$(query_db "SELECT COUNT(*) FROM (SELECT s.id FROM sources AS s JOIN results AS r ON r.package_id=s.id WHERE r.status='unreproducible' AND s.id NOT IN (SELECT package_id FROM notes) AND s.suite='$SUITE' AND s.architecture='$ARCH')") + RESULT=$(query_db "SELECT COUNT(*) FROM (SELECT s.id FROM sources AS s JOIN results AS r ON r.package_id=s.id WHERE r.status='unreproducible' AND s.id NOT IN (SELECT package_id FROM notes) AND s.suite='$SUITE' AND s.architecture='$ARCH') tmp") TD_PKG_SID_FTBR="$TD_PKG_SID_FTBR<td>$RESULT / $(echo "scale=1 ; ($RESULT*100/$COUNT_TOTAL)" | bc)%</td>" - RESULT=$(query_db "SELECT COUNT(*) FROM (SELECT s.id FROM sources AS s JOIN results AS r ON r.package_id=s.id WHERE r.status='FTBFS' AND s.id NOT IN (SELECT package_id FROM notes) AND s.suite='$SUITE' AND s.architecture='$ARCH')") + RESULT=$(query_db "SELECT COUNT(*) FROM (SELECT s.id FROM sources AS s JOIN results AS r ON r.package_id=s.id WHERE r.status='FTBFS' AND s.id NOT IN (SELECT package_id FROM notes) AND s.suite='$SUITE' AND s.architecture='$ARCH') tmp") TD_PKG_SID_FTBFS="$TD_PKG_SID_FTBFS<td>$RESULT / $(echo "scale=1 ; ($RESULT*100/$COUNT_TOTAL)" | bc)%</td>" SUITE="testing" diff --git a/bin/reproducible_maintenance.sh b/bin/reproducible_maintenance.sh index bab44c76..9d24fe5d 100755 --- a/bin/reproducible_maintenance.sh +++ b/bin/reproducible_maintenance.sh @@ -17,7 +17,7 @@ REP_RESULTS=/srv/reproducible-results # query reproducible database, print output query_to_print() { - printf ".width 0 25 \n %s ; " "$1" | sqlite3 -init $INIT -header -column ${PACKAGES_DB} + printf "$(psql -c "$@")" } # backup db @@ -25,26 +25,27 @@ if [ "$HOSTNAME" = "$MAINNODE" ] ; then echo "$(date -u) - backup db and update public copy." # prepare backup mkdir -p $REP_RESULTS/backup - cd $REP_RESULTS/backup # keep 30 days and the 1st of the month DAY=(date -d "30 day ago" '+%d') DATE=$(date -d "30 day ago" '+%Y-%m-%d') - if [ "$DAY" != "01" ] && [ -f reproducible_$DATE.db.xz ] ; then - rm -f reproducible_$DATE.db.xz + BACKUPFILE="$REP_RESULTS/backup/reproducible_$DATE.sql.xz" + if [ "$DAY" != "01" ] && [ -f "$BACKUPFILE" ] ; then + rm -f "$BACKUPFILE" fi - # actually do the backup + # Make a daily backup of database DATE=$(date '+%Y-%m-%d') - if [ ! -f reproducible_$DATE.db.xz ] ; then - cp -v $PACKAGES_DB . + BACKUPFILE="$REP_RESULTS/backup/reproducible_$DATE.sql" + if [ ! -f $BACKUPFILE.xz ] ; then + # make the backup DATE=$(date '+%Y-%m-%d') - mv -v reproducible.db reproducible_$DATE.db - xz reproducible_$DATE.db - fi + pg_dump $PGDATABASE > "$BACKUPFILE" + xz "$BACKUPFILE" - # provide copy for external backups - cp -v $PACKAGES_DB $BASE/ + # make the backup public + ln -s -f "$BACKUPFILE.xz" $BASE/reproducible.sql.xz + fi fi # for Debian, first run some checks… @@ -258,7 +259,7 @@ if [ "$HOSTNAME" = "$MAINNODE" ] ; then query_to_print "$QUERY" 2> /dev/null || echo "Warning: SQL query '$QUERY' failed." echo for PKG in $(cat $PACKAGES | cut -d "|" -f1) ; do - echo "sqlite3 ${PACKAGES_DB} \"DELETE FROM schedule WHERE package_id = '$PKG';\"" + echo "query_db \"DELETE FROM schedule WHERE package_id = '$PKG';\"" query_db "DELETE FROM schedule WHERE package_id = '$PKG';" done echo "Packages have been removed from scheduling." |