The Ultimate Debian Database

[Christian von Essen <christian@mvonessen.de>]

Hi,

my name is Christian von Essen and I'm the GSOC student working on the
Ultimate Debian Database project this year.

The idea behind this is to collect a part of the information about the Debian
project and import it into a database. The goal is to answer questions like

"What are the packages currently in unstable but not in testing, ordered by
their popcon score?"

Currently we're importing the data from *Sources, *Packages, popcon (those
three from Debian as well as Ubuntu) and the history of testing migrations.
bugs.debian.org is next.

Some information about this project can be read at
http://wiki.debian.org/UltimateDebianDatabase

For example, if you'd like to know the packages which are currently
in unstable, but not in testing, sorted by their popcon score,
you would write the following:

SELECT DISTINCT unstable.package, (vote + olde + recent + nofiles) as pvote
        FROM (SELECT DISTINCT package FROM packages
                WHERE distribution = 'debian' and release = 'sid')
          AS unstable,
             popcon
        WHERE NOT EXISTS (SELECT * FROM packages where distribution = 'debian'
                          AND release = 'lenny' and package = unstable.package)
              AND popcon.name = unstable.package ORDER BY pvote;

Or, given a source package, if you want to know the maximum of the popcon scores
of the packages build from that package, you just use the following query:

SELECT * FROM popcon_max WHERE package = '<package>';

Or if you'd like to know the number of packages build from each source package:

SELECT DISTINCT source, COUNT(package)
    FROM (SELECT DISTINCT source, package FROM packages
                 WHERE distribution = 'debian' AND release = 'sid') AS foo
    GROUP BY source
    ORDER BY COUNT(package);

Feel free to edit the pages if you see fit. I'd be glad to hear about what you
are thinking about the project and its ideas as you are the target users of
the database.

Christian von Essen