On 2014-11-19 06:48 , Clemens Lang wrote: > Hi all, > > I'd like to clean up the database schema of the registry. We have a couple > of fields in there that are currently unused, as well as a few possible > indices that could improve performance and at least one index that is > actively harmful. > > In detail: > > I'd like to drop the `url' column from the ports table. All ports I have > installed have this set to NULL and I couldn't find a use-case where it > was being used. I tried installing a port from a URL, but that doesn't > set the field either. > Even worse, the `url' column is part of a unique index `UNIQUE(url, > epoch, version, revision, variants)', which is bad, because if `url' is > always NULL, that means you can never install two ports with the same > tuple of (epoch, version, revision, variants). I'm surprised we haven't > hit this yet.
SQLite considers NULLs to be distinct in this case. <https://www.sqlite.org/nulls.html> > Consequently, this implies removal of the port_url index. > >>From the files table, I'd remove `mtime', `md5sum' and `editable', which > are all unused and set to dummy values at the moment. I know those were > intended to be used in configuration management, but this hasn't > happened so far and we can always re-add the fields later, if we ever > finish implementing this. > > In the dependencies table, we should add a `dep_port` index on the `id' > column to improve join performance. > > We currently do not have any indices for the portgroups table. We should > add an index on the `id' column for joins and one over (id, name, > version, size, sha256) to speed up opening portgroups and executing > Portfiles from registry. > > Of course, these changes imply the corresponding changes in registry2.0 > and cregistry. > > If nobody objects to the changes, I have a patch ready to commit. All sounds fine to me. - Josh _______________________________________________ macports-dev mailing list [email protected] https://lists.macosforge.org/mailman/listinfo/macports-dev
