The following commit has been merged in the master branch: commit 71c921a6b846e9da4e3a26df9ce6fd8c05044dfd Author: Andreas Tille <ti...@debian.org> Date: Mon Jul 1 22:34:27 2013 +0200
Thermometer in Python diff --git a/misc/sql/gis-thermometer.sh b/misc/thermometer/thermometer.py similarity index 63% copy from misc/sql/gis-thermometer.sh copy to misc/thermometer/thermometer.py index 49908ce..abfba3d 100755 --- a/misc/sql/gis-thermometer.sh +++ b/misc/thermometer/thermometer.py @@ -1,24 +1,74 @@ -#!/bin/sh -# Gather data needed to replace DebianGIS Package Thermometer available at -# -# http://pkg-grass.alioth.debian.org/debiangis-status.html +#!/usr/bin/python +# Copyright 2013: Andreas Tille <ti...@debian.org> +# License: GPL -if [ $# -lt 1 ] ; then - echo "$0: Argument for Blend is missing" - exit -fi +#PORT=5441 +UDDPORT=5452 +PORT=UDDPORT +DEFAULTPORT=5432 -# Note: The query below does not regard packages not available under a source -# name in stable or testing that is different from the name in unstable +from sys import stderr, exit +import psycopg2 +import json +import re -SERVICE="service=udd" -#if there is a local UDD clone just use this -if psql -l 2>/dev/null | grep -qw udd ; then - SERVICE=udd -fi +########################################################################################### +# Define several prepared statements to query UDD +try: + conn = psycopg2.connect(host="localhost",port=PORT,user="guest",database="udd") +except psycopg2.OperationalError, err: + try: + conn = psycopg2.connect(host="udd.debian.org",port=UDDPORT,user="guest",database="udd") + except psycopg2.OperationalError, err: + # logger not known at this state: logger.warning + print >>stderr, "PostgreSQL does not seem to run on port %i .. trying default port %i.\n\tMessage: %s" % (PORT, DEFAULTPORT, str(err)) + try: + conn = psycopg2.connect(host="localhost",port=DEFAULTPORT,user="guest",database="udd") + except psycopg2.OperationalError: + # Hmmm, I observed a really strange behaviour on one of my machines where connecting to + # localhost does not work but 127.0.0.1 works fine. No idea why ... but this should + # do the trick for the moment + conn = psycopg2.connect(host="127.0.0.1",port=DEFAULTPORT,user="guest",database="udd") -psql $SERVICE >$1.out <<EOT -SELECT b.source, +curs = conn.cursor() +# uddlog = open('logs/uddquery.log', 'w') + +def _execute_udd_query(query): + try: + curs.execute(query) + except psycopg2.ProgrammingError, err: + print >>stderr, "Problem with query\n%s" % (query) + print >>stderr, err + exit(-1) + except psycopg2.DataError, err: + print >>stderr, "%s; query was\n%s" % (err, query) + +def RowDictionaries(cursor): + """Return a list of dictionaries which specify the values by their column names""" + + description = cursor.description + if not description: + # even if there are no data sets to return the description should contain the table structure. If not something went + # wrong and we return NULL as to represent a problem + return NULL + if cursor.rowcount <= 0: + # if there are no rows in the cursor we return an empty list + return [] + + data = cursor.fetchall() + result = [] + + for row in data: + resultrow = {} + i = 0 + for dd in description: + resultrow[dd[0]] = row[i] + i += 1 + result.append(resultrow) + return result + +query = """PREPARE query_thermometer (text) AS + SELECT b.source, stable.version AS stable, testing.version AS testing, unstable.version AS unstable, @@ -36,32 +86,32 @@ SELECT b.source, SELECT DISTINCT p.source, '' AS wnpp FROM packages p JOIN blends_dependencies bd ON bd.package = p.package JOIN releases r ON p.release = r.release - WHERE bd.blend = '$1' AND + WHERE bd.blend = $1 AND (r.sort >= (SELECT sort FROM releases WHERE role = 'stable') OR r.sort = 0) -- forget older releases than stable but allow experimental UNION SELECT DISTINCT u.source, '' AS wnpp FROM ubuntu_packages u JOIN blends_dependencies bd ON bd.package = u.package - WHERE bd.blend = '$1' AND bd.distribution = 'ubuntu' + WHERE bd.blend = $1 AND bd.distribution = 'ubuntu' UNION SELECT DISTINCT pr.source, CASE WHEN wnpp!=0 THEN CAST(pr.wnpp AS text) ELSE '' END AS wnpp FROM blends_prospectivepackages pr JOIN blends_dependencies bd ON bd.package = pr.package - WHERE bd.blend = '$1' AND bd.distribution = 'prospective' + WHERE bd.blend = $1 AND bd.distribution = 'prospective' ) b LEFT OUTER JOIN ( SELECT source, homepage FROM ( SELECT source, homepage, row_number() OVER (PARTITION BY source ORDER BY version DESC) FROM ( SELECT DISTINCT p.source, p.homepage, p.version FROM packages p JOIN blends_dependencies bd ON bd.package = p.package JOIN releases r ON p.release = r.release - WHERE bd.blend = '$1' AND + WHERE bd.blend = $1 AND (r.sort >= (SELECT sort FROM releases WHERE role = 'stable') OR r.sort = 0) -- forget older releases than stable but allow experimental UNION SELECT DISTINCT u.source, u.homepage, u.version FROM ubuntu_packages u JOIN blends_dependencies bd ON bd.package = u.package - WHERE bd.blend = '$1' AND bd.distribution = 'ubuntu' + WHERE bd.blend = $1 AND bd.distribution = 'ubuntu' UNION SELECT DISTINCT pr.source, pr.homepage, pr.chlog_version as version FROM blends_prospectivepackages pr JOIN blends_dependencies bd ON bd.package = pr.package - WHERE bd.blend = '$1' AND bd.distribution = 'prospective' + WHERE bd.blend = $1 AND bd.distribution = 'prospective' ) hpversion GROUP BY source, homepage, version ) tmp @@ -74,7 +124,7 @@ SELECT b.source, JOIN blends_dependencies b ON b.package = p.package -- make sure we get the right source version that does not necessarily match binary version JOIN sources s ON p.source = s.source AND p.release = s.release - WHERE b.blend = '$1' AND r.role = 'unstable' AND p.distribution = 'debian' + WHERE b.blend = $1 AND r.role = 'unstable' AND p.distribution = 'debian' GROUP BY p.source ORDER BY p.source ) unstable ON b.source = unstable.source @@ -85,7 +135,7 @@ SELECT b.source, JOIN blends_dependencies b ON b.package = p.package -- make sure we get the right source version that does not necessarily match binary version JOIN sources s ON p.source = s.source AND p.release = s.release - WHERE b.blend = '$1' AND r.role = 'testing' AND p.distribution = 'debian' + WHERE b.blend = $1 AND r.role = 'testing' AND p.distribution = 'debian' GROUP BY p.source ORDER BY p.source ) testing ON b.source = testing.source @@ -96,7 +146,7 @@ SELECT b.source, JOIN blends_dependencies b ON b.package = p.package -- make sure we get the right source version that does not necessarily match binary version JOIN sources s ON p.source = s.source AND p.release = s.release - WHERE b.blend = '$1' AND r.role = 'stable' AND p.distribution = 'debian' + WHERE b.blend = $1 AND r.role = 'stable' AND p.distribution = 'debian' GROUP BY p.source ORDER BY p.source ) stable ON b.source = stable.source @@ -107,7 +157,7 @@ SELECT b.source, JOIN blends_dependencies b ON b.package = p.package -- make sure we get the right source version that does not necessarily match binary version JOIN sources s ON p.source = s.source AND p.release = s.release - WHERE b.blend = '$1' AND r.role = 'experimental' AND p.distribution = 'debian' + WHERE b.blend = $1 AND r.role = 'experimental' AND p.distribution = 'debian' GROUP BY p.source ORDER BY p.source ) experimental ON b.source = experimental.source @@ -118,7 +168,7 @@ SELECT b.source, JOIN blends_dependencies b ON b.package = p.package -- make sure we get the right source version that does not necessarily match binary version JOIN sources s ON p.source = s.source AND p.release = s.release - WHERE b.blend = '$1' AND p.release = (SELECT release FROM releases WHERE role='stable') || '-backports' AND p.distribution = 'debian' + WHERE b.blend = $1 AND p.release = (SELECT release FROM releases WHERE role='stable') || '-backports' AND p.distribution = 'debian' GROUP BY p.source ORDER BY p.source ) stable_bpo ON b.source = stable_bpo.source @@ -130,13 +180,13 @@ SELECT b.source, -- make sure we get the right source version that does not necessarily match binary version JOIN sources s ON p.source = s.source AND p.release = s.release JOIN vcs v ON s.source = v.source - WHERE b.blend = '$1' AND v.distribution = 'UNRELEASED' + WHERE b.blend = $1 AND v.distribution = 'UNRELEASED' GROUP BY p.source UNION SELECT DISTINCT pr.source, strip_binary_upload(chlog_version) AS version FROM blends_dependencies b JOIN blends_prospectivepackages pr ON b.package = pr.package - WHERE b.blend = '$1' + WHERE b.blend = $1 ) tmp ) unreleased ON b.source = unreleased.source LEFT OUTER JOIN ( @@ -144,7 +194,7 @@ SELECT b.source, FROM ubuntu_packages u JOIN blends_dependencies b ON b.package = u.package JOIN ubuntu_sources s ON u.source = s.source AND u.release = s.release - WHERE b.blend = '$1' AND u.release = 'precise' + WHERE b.blend = $1 AND u.release = 'precise' GROUP BY u.source ORDER BY u.source ) precise ON b.source = precise.source @@ -153,7 +203,7 @@ SELECT b.source, FROM ubuntu_packages u JOIN blends_dependencies b ON b.package = u.package JOIN ubuntu_sources s ON u.source = s.source AND u.release = s.release - WHERE b.blend = '$1' AND u.release = 'quantal' + WHERE b.blend = $1 AND u.release = 'quantal' GROUP BY u.source ORDER BY u.source ) quantal ON b.source = quantal.source @@ -162,12 +212,21 @@ SELECT b.source, FROM ubuntu_packages u JOIN blends_dependencies b ON b.package = u.package JOIN ubuntu_sources s ON u.source = s.source AND u.release = s.release - WHERE b.blend = '$1' AND u.release = 'raring' + WHERE b.blend = $1 AND u.release = 'raring' GROUP BY u.source ORDER BY u.source ) raring ON b.source = raring.source LEFT OUTER JOIN dehs d ON b.source = d.source ORDER BY b.source ; +""" +_execute_udd_query(query) + +blendname = 'debian-med' +_execute_udd_query( "EXECUTE query_thermometer('%s')" % blendname) + +if curs.rowcount > 0: + f = open('thermometer.json', 'w') + print >>f, json.dumps(RowDictionaries(curs)) + f.close() -EOT -- Static and dynamic websites for Debian Pure Blends _______________________________________________ Blends-commit mailing list Blends-commit@lists.alioth.debian.org http://lists.alioth.debian.org/cgi-bin/mailman/listinfo/blends-commit