Merge lp:~wgrant/launchpad/validpersoncache-no-emailaddress-account into lp:launchpad/db-devel

[William Grant]

Switch ValidPerson(OrTeam)Cache to use EmailAddress.person instead of EmailAddress.account.

Now that SSO is separate, EmailAddress.account is redundant with Person.account. Before we can do away with the former, the ValidPerson(OrTeam)Cache views must be adjusted to use EmailAddress.person.

[Stuart Bishop (stub)]

> +CREATE OR REPLACE VIEW validpersoncache AS (
> +    SELECT emailaddress.person AS id
> +    FROM emailaddress, person, account
> +    WHERE
> +        emailaddress.person = person.id
> +        AND person.account = account.id
> +        AND emailaddress.person IS NOT NULL
> +        AND emailaddress.status = 4
> +        AND account.status = 20
> +);

The emailaddress.person IS NOT NULL clause is redundant, as the first
clause means it will never be NULL.

It is interesting that we don't currently check that person.merged IS
NULL here, although we do in the validpersonorteamcache view. We might
have assumed it was redundant as merged person records will never have
an account? In which case it would have guarded against bad data for
no real cost. I guess we should leave it as it is as I think this view
is only used by legacy code.

> +CREATE OR REPLACE VIEW validpersonorteamcache AS (
> +    SELECT person.id
> +    FROM
> +        person
> +        LEFT JOIN emailaddress ON person.id = emailaddress.person
> +        LEFT JOIN account ON person.account = account.id
> +    WHERE
> +        person.teamowner IS NOT NULL
> +        AND person.merged IS NULL
> +        OR person.teamowner IS NULL
> +        AND account.status = 20
> +        AND emailaddress.status = 4
> +);

I like brackets in mixed AND and OR statements so I don't need to
worry about precedence, rules :)

I just looked at using UNION ALL for the second view, but the
estimates are slightly slower.

[Robert Collins (lifeless)]

what stub says.

review: Approve

[Stuart Bishop (stub)]

review: Approve (db)

=== added file 'database/schema/patch-2209-01-0.sql'
--- database/schema/patch-2209-01-0.sql	1970-01-01 00:00:00 +0000
+++ database/schema/patch-2209-01-0.sql	2012-01-04 10:54:25 +0000
@@ -0,0 +1,30 @@
+-- Copyright 2012 Canonical Ltd.  This software is licensed under the
+-- GNU Affero General Public License version 3 (see the file LICENSE).
+SET client_min_messages=ERROR;
+
+CREATE OR REPLACE VIEW validpersoncache AS (
+    SELECT emailaddress.person AS id
+    FROM emailaddress, person, account
+    WHERE
+        emailaddress.person = person.id
+        AND person.account = account.id
+        AND emailaddress.status = 4
+        AND account.status = 20
+);
+
+CREATE OR REPLACE VIEW validpersonorteamcache AS (
+    SELECT person.id
+    FROM
+        person
+        LEFT JOIN emailaddress ON person.id = emailaddress.person
+        LEFT JOIN account ON person.account = account.id
+    WHERE
+        (person.teamowner IS NOT NULL
+         AND person.merged IS NULL)
+        OR
+        (person.teamowner IS NULL
+         AND account.status = 20
+         AND emailaddress.status = 4)
+);
+
+INSERT INTO LaunchpadDatabaseRevision VALUES (2209, 01, 0);