[Qa-jenkins-scm] [jenkins.debian.net] 04/06: reproducible Debian: switch bash scripts to postgres
Holger Levsen
holger at layer-acht.org
Mon Dec 19 11:23:35 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 a55f5a0c1b2843bd3ff96f6b8a532c224b71d42d
Author: Valerie R Young <spectranaut at riseup.net>
Date: Mon Sep 19 15:19:40 2016 -0400
reproducible Debian: switch bash scripts to postgres
Signed-off-by: Holger Levsen <holger at layer-acht.org>
---
bin/reproducible_common.sh | 34 +++++-----------------------------
bin/reproducible_html_dashboard.sh | 24 ++++++++++++------------
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 7a4d523..166332f 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 9ee02f2..e754104 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 bab44c7..9d24fe5 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."
--
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