registry.db files table should have unique constraint over id and path columns

[ryandesign (Ryan Carsten Schmidt)]

The registry.db files table should have a unique constraint over the id and path columns, shouldn't it? (Or a primary key constraint, since it looks like sqlite does support compound keys that span multiple columns.)

I have a corrupt registry which, after repairing by using .dump and .read, has duplicate entries in the files table. ​This causes activation of those ports to fail:

--->  Activating python2_select @0.0_3
DEBUG: Using /usr/bin/tar
DEBUG: Using /usr/bin/bzip2
DEBUG: system: /usr/bin/bzip2 -d -c /opt/local/var/macports/software/python2_select/python2_select-0.0_3.darwin_16.noarch.tbz2 | ( /usr/bin/tar -xvpf - )
x ./
x ./+COMMENT
x ./+CONTENTS
x ./+DESC
x ./+PORTFILE
x ./+STATE
x ./opt/
x ./opt/local/
x ./opt/local/etc/
x ./opt/local/etc/select/
x ./opt/local/etc/select/python2/
x ./opt/local/etc/select/python2/base
x ./opt/local/etc/select/python2/none
x ./opt/local/etc/select/python2/python26-apple
x ./opt/local/etc/select/python2/python27-apple
DEBUG: Activation failed, rolling back.
Error: Failed to activate python2_select: /opt/local/etc/select/python2/base is being used by another port
DEBUG: Error code: registry::already-active
DEBUG: Backtrace: /opt/local/etc/select/python2/base is being used by another port
    while executing
"$port activate $imagefiles"
    invoked from within
"try {
                $port activate $imagefiles
                foreach file $files {
                    if {[_activate_file "${extracted_dir}${file..."
    invoked from within
"registry::write {
            # Activate it, and catch errors so we can roll-back
            try {
                $port activate $imagefiles
       ..."
    invoked from within
"try {
        registry::write {
            foreach file $imagefiles {
                set srcfile "${extracted_dir}${file}"

                # To be ..."
    (procedure "_activate_contents" line 20)
    invoked from within
"_activate_contents $requested $rename_list"
    (procedure "portimage::activate" line 61)
    invoked from within
"registry_activate $subport $version $revision $portvariants $optionlist"
    (procedure "portactivate::activate_main" line 15)
    invoked from within
"$procedure $targetname"

/opt/local/etc/select/python2/base is not being used by another port, but it is "owned" by this port three times according to the registry.

I have checked other non-corrupt registries and they do not have any duplicate entries in the files table.

I am guessing that the corruption did not cause the duplicate entries, but that MacPorts got confused by the registry corruption and inserted those duplicate entries while activating, deactivating, installing or uninstalling a port. Whatever the cause of the duplicate entries, a proper unique or primary key constraint would have prevented this incoherent registry state from being created.

Perhaps we need some more primary or unique keys in the other tables too.

If there have been prior discussions about this that I've missed, let me know.