Merge lp:~stevenk/launchpad/db-specification-new-access-policy into lp:launchpad/db-devel

[Steve Kowalik]

Add new columns Specification.access_{policy,grants}.

Add new columns on Specification, access_{policy,grants} -- this work mirrors the earlier work done on both Bug and Branch, and brings Specification into line with them.

[Stuart Bishop (stub)]

Looks good.

70 +CREATE OR REPLACE FUNCTION specification_maintain_access_cache_trig() RETURNS trigger
71 + LANGUAGE plpgsql AS $$
72 +BEGIN
73 + PERFORM specification_denorm_access(NEW.id);
74 + RETURN NULL;
75 +END;
76 +$$;

If you want you can add an 'if NEW.id <> OLD.id then specification_denorm_access(OLD.id)' here to cope with someone updating the id column, not that we ever actually do that.

review: Approve (db)

=== added file 'database/schema/patch-2209-28-6.sql'
--- database/schema/patch-2209-28-6.sql	1970-01-01 00:00:00 +0000
+++ database/schema/patch-2209-28-6.sql	2013-01-15 05:37:22 +0000
@@ -0,0 +1,78 @@
+-- Copyright 2013 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 new access policy for Specification.
+ALTER TABLE Specification ADD COLUMN access_policy integer;
+ALTER TABLE Specification ADD COLUMN access_grants integer[];
+
+-- New specification function, like bug_flatten_access.
+CREATE OR REPLACE FUNCTION specification_denorm_access(spec_id integer)
+    RETURNS void LANGUAGE sql SECURITY DEFINER SET search_path = public AS $$
+    UPDATE specification
+        SET access_policy = policies[1], access_grants = grants
+        FROM
+            build_access_cache(
+                (SELECT id FROM accessartifact WHERE specification = $1),
+                (SELECT information_type FROM specification WHERE id = $1))
+            AS (policies integer[], grants integer[])
+        WHERE id = $1;
+$$;
+
+CREATE OR REPLACE FUNCTION accessartifact_denorm_to_artifacts(artifact_id integer)
+    RETURNS void
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+    artifact_row accessartifact%ROWTYPE;
+BEGIN
+    SELECT * INTO artifact_row FROM accessartifact WHERE id = artifact_id;
+    IF artifact_row.bug IS NOT NULL THEN
+        PERFORM bug_flatten_access(artifact_row.bug);
+    END IF;
+    IF artifact_row.branch IS NOT NULL THEN
+        PERFORM branch_denorm_access(artifact_row.branch);
+    END IF;
+    IF artifact_row.specification IS NOT NULL THEN
+        PERFORM specification_denorm_access(artifact_row.specification);
+    END IF;
+    RETURN;
+END;
+$$;
+COMMENT ON FUNCTION accessartifact_denorm_to_artifacts(artifact_id integer) IS
+    'Denormalize the policy access and artifact grants to bugs, branches and specifications.';
+
+CREATE OR REPLACE FUNCTION accessartifact_maintain_denorm_to_artifacts_trig()
+    RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+BEGIN
+    IF TG_OP = 'INSERT' THEN
+        PERFORM accessartifact_denorm_to_artifacts(NEW.artifact);
+    ELSIF TG_OP = 'UPDATE' THEN
+        PERFORM accessartifact_denorm_to_artifacts(NEW.artifact);
+        IF OLD.artifact != NEW.artifact THEN
+            PERFORM accessartifact_denorm_to_artifacts(OLD.artifact);
+        END IF;
+    ELSIF TG_OP = 'DELETE' THEN
+        PERFORM accessartifact_denorm_to_artifacts(OLD.artifact);
+    END IF;
+    RETURN NULL;
+END;
+$$;
+
+-- A trigger to handle specification.information_type changes.
+CREATE OR REPLACE FUNCTION specification_maintain_access_cache_trig() RETURNS trigger
+    LANGUAGE plpgsql AS $$
+BEGIN
+    PERFORM specification_denorm_access(NEW.id);
+    RETURN NULL;
+END;
+$$;
+
+CREATE TRIGGER specification_maintain_access_cache
+    AFTER INSERT OR UPDATE OF information_type ON specification
+    FOR EACH ROW EXECUTE PROCEDURE specification_maintain_access_cache_trig();
+
+INSERT INTO LaunchpadDatabaseRevision VALUES (2209, 28, 6);

=== modified file 'database/schema/security.cfg'
--- database/schema/security.cfg	2012-12-14 04:42:41 +0000
+++ database/schema/security.cfg	2013-01-15 05:37:22 +0000
@@ -77,6 +77,7 @@
 public.replication_lag(integer)            = EXECUTE
 public.sane_version(text)                  = EXECUTE
 public.sha1(text)                          = EXECUTE
+public.specification_denorm_access(integer) = EXECUTE
 public.ulower(text)                        = EXECUTE
 public.update_database_disk_utilization()  =
 public.valid_absolute_url(text)            = EXECUTE

=== added file 'lib/lp/blueprints/tests/test_specification_access_policy_triggers.py'
--- lib/lp/blueprints/tests/test_specification_access_policy_triggers.py	1970-01-01 00:00:00 +0000
+++ lib/lp/blueprints/tests/test_specification_access_policy_triggers.py	2013-01-15 05:37:22 +0000
@@ -0,0 +1,46 @@
+# Copyright 2013 Canonical Ltd.  This software is licensed under the
+# GNU Affero General Public License version 3 (see the file LICENSE).
+
+__metaclass__ = type
+
+from zope.component import getUtility
+from zope.security.proxy import removeSecurityProxy
+
+from lp.app.enums import InformationType
+from lp.blueprints.model.specification import Specification
+from lp.registry.interfaces.accesspolicy import IAccessPolicySource
+from lp.services.database.lpstorm import IStore
+from lp.testing import TestCaseWithFactory
+from lp.testing.layers import DatabaseFunctionalLayer
+
+
+class TestSpecificationAccessPolicyTriggers(TestCaseWithFactory):
+    layer = DatabaseFunctionalLayer
+
+    def fetchPolicies(self, specification):
+        # We may be dealing with private specs, so just ignore security.
+        return IStore(Specification).execute(
+            "SELECT access_policy, access_grants FROM specification WHERE "
+            "id = ?", (removeSecurityProxy(specification).id,)).get_one()
+
+    def assertAccess(self, specification, expected_policy, expected_grants):
+        policy, grants = self.fetchPolicies(specification)
+        self.assertEqual(expected_policy, policy)
+        self.assertEqual(expected_grants, grants)
+
+    def test_no_access_policy_for_public_specifications(self):
+        # A public specification has no access policy or grants.
+        self.assertAccess(self.factory.makeSpecification(), None, None)
+
+    def test_adding_aag_with_private_specification(self):
+        # Adding a new AAG updates the specification columns via trigger.
+        owner = self.factory.makePerson()
+        specification = self.factory.makeSpecification(
+            information_type=InformationType.PROPRIETARY, owner=owner)
+        [ap] = getUtility(IAccessPolicySource).find(
+            [(removeSecurityProxy(specification).product,
+            InformationType.PROPRIETARY)])
+        self.assertAccess(specification, ap.id, [])
+        artifact = self.factory.makeAccessArtifact(concrete=specification)
+        grant = self.factory.makeAccessArtifactGrant(artifact=artifact)
+        self.assertAccess(specification, ap.id, [grant.grantee.id])

=== modified file 'lib/lp/testing/factory.py'
--- lib/lp/testing/factory.py	2012-12-26 01:12:37 +0000
+++ lib/lp/testing/factory.py	2013-01-15 05:37:22 +0000
@@ -2097,7 +2097,7 @@
         if distribution is None and product is None:
             if proprietary:
                 specification_sharing_policy = (
-                SpecificationSharingPolicy.EMBARGOED_OR_PROPRIETARY)
+                    SpecificationSharingPolicy.EMBARGOED_OR_PROPRIETARY)
             else:
                 specification_sharing_policy = None
             product = self.makeProduct(