archweb

archweb copied to clipboard

This query returns a list of orphan packages and finds possible maintainers for those by looking at all packages that directly depend on the orphan.Please make this a report page in the developer area of archweb.

SELECT distinct sub.depname as "(orphan) child", sub.pkgbase as "parent", coalesce(user.username, "orphan") as "parent maintainer", p.packager_str as "last child packager" FROM ( SELECT distinct p.pkgbase, dep.depname FROM package_depends as dep JOIN packages as p on p.id = dep.pkg_id WHERE dep.depname in ( SELECT distinct p.pkgbase FROM packages as p LEFT JOIN packages_packagerelation as rel on rel.pkgbase = p.pkgbase WHERE rel.id is null) ) as sub LEFT JOIN packages_packagerelation as rel on rel.pkgbase = sub.pkgbase LEFT JOIN auth_user as user on user.id = rel.user_id LEFT JOIN packages as p on sub.depname = p.pkgname ORDER BY sub.depname, sub.pkgbase;

The query is somewhat slow (~1sec execution time) and probably needs to be optimized.

A more optimised version: SELECT DISTINCT pp.pkgbase, ppr.user_id, child.pkgname FROM package_depends ppd JOIN packages pp ON ppd.pkg_id = pp.id JOIN packages_packagerelation ppr ON pp.pkgbase = ppr.pkgbase JOIN (SELECT DISTINCT cp.pkgname FROM packages cp LEFT JOIN packages_packagerelation pr ON cp.pkgbase = pr.pkgbase WHERE pr.id IS NULL) child ON ppd.depname = child.pkgname ORDER BY child.pkgname;

This feature should only be shown for logged in users because of the possible performance penalty.

These SQL query's don't work anymore. The feature should be a report as the other reports in devel/reports.py.

An solution is the following code, except it's slow since it does a query per dependency.

from main.models import Package
from packages.models import PackageRelation

from django.contrib.auth.models import User

owned = PackageRelation.objects.all().values('pkgbase')

user = User.objects.filter(username='jlichtblau').first()
maintained = PackageRelation.objects.filter(user=user, type=PackageRelation.MAINTAINER).values('pkgbase')
packages = Package.objects.normal()
packages = packages.filter(pkgbase__in=maintained)

for pkg in packages:
    print("package: " + pkg.pkgname)
    for dep in pkg.depends.all():
        dep_pkg = dep.get_best_satisfier()
        if not dep_pkg:
            continue
        found = PackageRelation.objects.filter(pkgbase=dep_pkg.pkgbase).first()
        if not found:
            print(dep_pkg)

Updated the query to our current DB model: SELECT DISTINCT pp.pkgbase, ppr.user_id, child.pkgname FROM packages_depend ppd JOIN packages pp ON ppd.pkg_id = pp.id JOIN packages_packagerelation ppr ON pp.pkgbase = ppr.pkgbase JOIN (SELECT DISTINCT cp.pkgname FROM packages cp LEFT JOIN packages_packagerelation pr ON cp.pkgbase = pr.pkgbase WHERE pr.id IS NULL) child ON ppd.name = child.pkgname ORDER BY child.pkgname;

The reports view is sadly not that useful for this as it would only allow adding a the "orphan - required package"