The following commit has been merged in the master branch:
commit 2a4823456e896473aad3be46e3e9666e6981310b
Author: Andreas Tille <[email protected]>
Date: Fri Feb 5 00:30:57 2016 +0100
Rudimentary query to help edam
diff --git a/misc/sql/edam.sh b/misc/sql/edam.sh
new file mode 100755
index 0000000..baa0d29
--- /dev/null
+++ b/misc/sql/edam.sh
@@ -0,0 +1,60 @@
+#!/bin/sh
+# Gather data needed to replace DebianGIS Package Thermometer available at
+#
+# http://pkg-grass.alioth.debian.org/debiangis-status.html
+
+PORT="-p 5452"
+
+SERVICE="service=udd"
+#if there is a local UDD clone just use this
+if psql $PORT -l 2>/dev/null | grep -qw udd ; then
+ SERVICE=udd
+fi
+
+team="'[email protected]'"
+
+psql $PORT $SERVICE >edam.out <<EOT
+ SELECT DISTINCT
+ p.package, p.distribution, p.release, p.component, p.version,
+ p.source, p.homepage
+ FROM (
+ SELECT DISTINCT
+ package, distribution, release, component,
strip_binary_upload(version) AS version,
+ maintainer, source, section, homepage, description,
description_md5
+ FROM packages
+ WHERE package IN
+ (SELECT DISTINCT package FROM blends_dependencies WHERE
blend = 'debian-med' AND task IN ('bio', 'bio-dev'))
+ ) p
+ LEFT OUTER JOIN descriptions en ON en.language = 'en' AND en.package =
p.package AND en.release = p.release AND en.description_md5 = p.description_md5
+ JOIN (
+ -- select packages which have versions outside experimental
+ SELECT px.package, strip_binary_upload(px.version) AS version, (SELECT
release FROM releases WHERE sort = MAX(rx.sort)) AS release
+ FROM (
+ -- select highest version which is not in experimental - except if
a package resides in experimental only
+ SELECT pex.package, CASE WHEN pnoex.version IS NOT NULL THEN
pnoex.version ELSE pex.version END AS version FROM
+ (SELECT package, MAX(version) AS version FROM packages
+ WHERE package IN
+ (SELECT DISTINCT package FROM blends_dependencies WHERE
blend = 'debian-med' AND task IN ('bio', 'bio-dev'))
+ GROUP BY package
+ ) pex
+ LEFT OUTER JOIN
+ (SELECT package, MAX(version) AS version FROM packages
+ WHERE package IN
+ (SELECT DISTINCT package FROM blends_dependencies WHERE
blend = 'debian-med' AND task IN ('bio', 'bio-dev'))
+ AND release != 'experimental'
+ GROUP BY package
+ ) pnoex ON pex.package = pnoex.package
+ ) px
+ JOIN (
+ -- select the release in which this version is available
+ SELECT DISTINCT package, version, release FROM packages
+ WHERE package IN
+ (SELECT DISTINCT package FROM blends_dependencies WHERE
blend = 'debian-med' AND task IN ('bio', 'bio-dev'))
+ ) py ON px.package = py.package AND px.version = py.version
+ JOIN releases rx ON py.release = rx.release
+ GROUP BY px.package, px.version
+ ) pvar ON pvar.package = p.package AND pvar.version = p.version AND
pvar.release = p.release
+
+ ORDER BY p.source, p.package;
+
+EOT
--
Static and dynamic websites for Debian Pure Blends
_______________________________________________
Blends-commit mailing list
[email protected]
http://lists.alioth.debian.org/cgi-bin/mailman/listinfo/blends-commit
