On Sat, 18 Apr 2020 18:53:49 +0200
Marc Espie <[email protected]> wrote:
> On Sat, Apr 18, 2020 at 06:07:41PM +0200, Thomas L. wrote:
> > I saw the Canonical field in _Paths and wondered about its use, but
> > I am still unclear after your explanation. _Ports.FullPkgPath has a
> > foreign key constraint on _Paths.Id and _Paths.PkgPath has a
> > constraint on _Paths.Id, too. Constraint enforcement is disabled,
> > but SELECT pkgstem FROM _ports
> > LEFT JOIN _paths ON _paths.id = _ports.fullpkgpath
> > LEFT JOIN _paths AS _pkgpaths ON _pkgpaths.id =
> > _paths.pkgpath WHERE _pkgpaths.fullpkgpath IS NULL;
> > shows there are no violations.
> >
>
> all _Ports.fullpkgpath are valid _paths.id, but the reverse is not
> true!
Sure, but in the proposed query
SELECT _PkgPaths.FullPkgPath FROM _Paths AS _PkgPaths
JOIN _Paths ON _Paths.PkgPath = _PkgPaths.Id
JOIN _Ports ON _Ports.FullPkgPath = _Paths.Id
WHERE PKGSTEM = ?
ORDER BY LENGTH(_PkgPaths.FullPkgPath)
a _Paths.Id for every _Ports.FullPkgPath and a _Paths.Id for every
_Paths.PkgPath is enough to guarantee a result if _Ports.PKGSTEM
matches.
> _Ports has guaranteed lines for all canonical values.
Thats good to know, thanks.
