[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.