Code review comment for lp:~mwhudson/launchpad/recipe-db-schema

Allocated patch number is patch-2207-25-0.sql

SourcePackageRecipeDataInstruction.recipe_data should be NOT NULL

The sourcepackagerecipedatainstruction__name__recipe_data should be called
sourcepackagerecipedatainstruction__name__recipe_data__key. Similarly other unique constraints should end with __key.

Please change:

ALTER TABLE SourcePackageRecipeDataInstruction
ADD CONSTRAINT sourcepackagerecipedatainstruction__line_number__recipe_data
UNIQUE (line_number, recipe_data);

into the more useful to PostgreSQL variant:

ALTER TABLE SourcePackageRecipeDataInstruction ADD CONSTRAINT
sourcepackagerecipedatainstruction__recipe_data__line_number__key
UNIQUE (recipe_data, line_number);

sourcepackage is one word in Launchpad, so the .*source_package.* columns
need to be renamed .*sourcepackage.*.

As discussed on IRC and seen at http://pastebin.ubuntu.com/355335/, its better to make SourcepackageRecipeData reference recipe and build rather than the other way around, as we can then enforce the constraint that one and only one should be set.

Needed indexes:

CREATE INDEX sourcepackagerecipedata__base_branch__idx
ON SourcepackageRecipeData(base_branch);

CREATE INDEX sourcepackagerecipedatainstruction__branch__idx
ON SourcepackageRecipeDataInstruction(branch);

CREATE INDEX sourcepackagerecipe__registrant__idx
ON SourcepackageRecipe(registrant);

--CREATE INDEX sourcepackagerecipe__owner__idx
--ON SourcepackageRecipe(owner);

CREATE INDEX sourcepackagerecipebuild__distroseries__idx
ON SourcepackageRecipeBuild(distroseries);

CREATE INDEX sourcepackagerecipebuild__sourcepackagename__idx
ON SourcepackageRecipeBuild(sourcepackagename);

CREATE INDEX sourcepackagerecipebuild__build_log__idx
ON SourcepackageRecipeBuild(build_log) WHERE build_log IS NOT NULL;

CREATE INDEX sourcepackagerecipebuild__builder__idx
ON SourcepackageRecipeBuild(builder);

CREATE INDEX sourcepackagerecipebuild__requester__idx
ON SourcepackageRecipeBuild(requester);

CREATE INDEX sourcepackagerecipebuild__recipe__idx
ON SourcepackageRecipeBuild(recipe);

CREATE INDEX sourcepackagebuildupload__registrant__idx
ON SourcepackageRecipeBuildUpload(registrant);