[MacPorts] #57730: registry.db files table should have unique constraint over id and path columns

[MacPorts]

#57730: registry.db files table should have unique constraint over id and path
columns
------------------------+--------------------
 Reporter:  ryandesign  |      Owner:  (none)
     Type:  defect      |     Status:  new
 Priority:  Normal      |  Milestone:
Component:  base        |    Version:  2.5.99
 Keywords:              |       Port:
------------------------+--------------------
 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 [ticket:57570 corrupt registry] which, after repairing by using
 `.dump` and `.read`, has duplicate entries in the ''files'' table.
 [https://build.macports.org/builders/ports-10.12_x86_64-builder/builds/74763/steps
 /install-dependencies/logs/stdio 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.

-- 
Ticket URL: <https://trac.macports.org/ticket/57730>
MacPorts <https://www.macports.org/>
Ports system for macOS