Registry schema cleanup

[Joshua Root]

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