Merge lp://staging/~bryce/launchpad/lp-617679-db into lp://staging/launchpad/db-devel

OMMENT ON COLUMN BugTrackerComponent.is_active IS 'Whether to display or hide the item in the Launchpad user interface.';

perhaps is_shown, if that is what you mean.

If you mean that bugwatches etc will ignore it in future, then the comment is wrong. Either way something is mismatched.

I don't parse ." Some bug trackers do not group components thus all BugTrackerComponents will belong to a default componentgroup.';" easily; could you consider expanding or rephrasing. For instance, do you mean 'one default componentgroup for all trackers'.

source_package->distro_source_package please, for clarity

If these are going to be shown in LP, and traversed to, they have to have a constraint on the name to match our url restrictions; if they aren't, then you may have some difficulty writing uis for them. If the contstraint will conflict with external names (e.g. someone has a component called Foo and the capital letter matters), then you need two fields: a field for LP to use and a field for the external tracker. (Or, don't ever traverse to these things in the web UI/API).

Approved with the tweaks above. Stuart will have more for you on the schema itself.

Allocated DB patch is patch-2208-09-0.sql

Missing constraint on BugTrackerComponentGroup:

ALTER TABLE BugTrackerComponentGroup
    ADD CONSTRAINT bugtrackercomponentgroup__bug_tracker__name__key
    UNIQUE (bug_tracker, name);

The bugtrackercomponentgroup__bugtracker__idx index is incorrect (you reference the wrong table). I suspect you wanted the index on BugTrackerComponentGroup(bugtracker), which is now unnecessary with the above constraint, so just drop it.

On BugTrackerComponent:

    - you are declaring the source_package foreign key twice. Either remove the REFERENCES clause in the column definition, or the CONSTRAINT clause after the column definitions.

    - The boolean columns are missing NOT NULL constraints.

    - Missing constraint on (component_group, name).

      ALTER TABLE BugTrackerComponent
        ADD CONSTRAINT bugtrackercomponent__component_group__name__key
        UNIQUE (component_group, name);

    - The bugtrackercomponent__componentgroup__idx index becomes unnecessary
      with the above constraint.

    - I'm not sure if source_package should be UNIQUE. I suspect it is.

      ALTER TABLE BugTrackerComponent
        ADD CONSTRAINT bugtrackercomponent__source_package__key
        UNIQUE (source_package);

    - If the above UNIQUE constraint on source_package is added, the
      bugtrackercomponent__distributionsourcepackage__idx index is
      unnecessary. It has the wrong name anyway
      (bugtrackercomponent__source_package__idx).

Thanks, I've updated the branch with this feedback. Please doublecheck that I've done the constraint for the name properly.

Robert, I'm a little fuzzy on what you mean by 'traverse to'. If you mean, can the bugzilla component text appear in a link on a launchpad webpage, yes that is part of the intent of this. Is the valid_name constraint sufficient, or do I need to add a separate field like 'displayname'?

valid_name is sufficient, as long as that is sufficient for the bug
trackers we're talking to.

traverse to - 'generate urls to it in the web UI/API' - as in, if you
embed it in a page, name won't matter; if we call canonical_url() on
something, the result has to meet the validity constraints for all our
urls.

Fine.

Consider is_visible rather than is_shown - we use visible elsewhere but 'shown' might be more appropriate here.