On Sat, Apr 18, 2020 at 06:53:49PM +0200, Marc Espie wrote: > On Sat, Apr 18, 2020 at 06:07:41PM +0200, Thomas L. wrote: > > On Sat, 18 Apr 2020 15:08:59 +0200 > > Marc Espie <[email protected]> wrote: > > > It's very likely to miss a canonical at least. > > > > > > Specifically, _Ports.FullPkgPath is not guaranteed to match every > > > _Paths.Id > > > > > > the _Ports table get canonicalized to save space and avoid duplicates. > > > > 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! > _Ports has guaranteed lines for all canonical values.
Actually, let me rephrase that statement: select distinct id from _paths where id not in (select fullpkgpath from _ports); will yield 1013 entries which do NOT have any lines in _ports. select distinct canonical from _paths where canonical not in (select fullpkgpath from _ports); will still yield 173 entries, but those are "parsing entries". For instance, there's: 11922|x11/ruby-tk|11922|11922 that's because the full schema is: CREATE TABLE _Paths (Id INTEGER PRIMARY KEY, FullPkgPath TEXT NOT NULL UNIQUE, PkgPath INTEGER NOT NULL REFERENCES _Paths(Id), Canonical INTEGER NOT NULL REFERENCES _Paths(Id)); and so THAT entry exists so that flavored x11/ruby-tk have a pkgpath to refer to: 11922|x11/ruby-tk|11922|11922 11923|x11/ruby-tk,ruby25|11922|11923 11924|x11/ruby-tk,ruby26|11922|11924 11925|x11/ruby-tk,ruby27|11922|11925 But every entry read while scanning the ports tree WILL end up as a canonical entry: For instance, 12117|x11/qt5/qttools,,-main|120|12116 refers to 120|x11/qt5/qttools|120|120 and uses 12116|x11/qt5/qttools,|120|12116 as canonical entry (which is the one with a _ports line).
