1.1 --- a/update/core-update.v3.2.2-v4.0.0.sql	Sun Jun 18 02:24:50 2017 +0200
     1.2 +++ b/update/core-update.v3.2.2-v4.0.0.sql	Mon Jun 26 15:54:26 2017 +0200
     1.3 @@ -1,3 +1,12 @@
     1.4 +ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'unit_created';
     1.5 +ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'unit_updated';
     1.6 +ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'unit_removed';
     1.7 +ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'subject_area_created';
     1.8 +ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'subject_area_updated';
     1.9 +ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'subject_area_removed';
    1.10 +ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'policy_created';
    1.11 +ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'policy_updated';
    1.12 +ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'policy_removed';
    1.13  ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'suggestion_removed';
    1.14  ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'member_activated';
    1.15  ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'member_removed';
    1.16 @@ -472,6 +481,8 @@
    1.17  
    1.18  ALTER TABLE "issue" ADD UNIQUE ("area_id", "id");
    1.19  DROP INDEX "issue_area_id_idx";
    1.20 +ALTER TABLE "issue" ADD UNIQUE ("policy_id", "id");
    1.21 +DROP INDEX "issue_policy_id_idx";
    1.22  
    1.23  ALTER TABLE "issue" RENAME COLUMN "snapshot" TO "calculated";
    1.24  
    1.25 @@ -653,6 +664,7 @@
    1.26  ALTER TABLE "event" ADD COLUMN "scope"           "delegation_scope";
    1.27  ALTER TABLE "event" ADD COLUMN "unit_id"         INT4    REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
    1.28  ALTER TABLE "event" ADD COLUMN "area_id"         INT4;
    1.29 +ALTER TABLE "event" ADD COLUMN "policy_id"       INT4    REFERENCES "policy" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
    1.30  ALTER TABLE "event" ADD COLUMN "boolean_value"   BOOLEAN;
    1.31  ALTER TABLE "event" ADD COLUMN "numeric_value"   INT4;
    1.32  ALTER TABLE "event" ADD COLUMN "text_value"      TEXT;
    1.33 @@ -660,6 +672,7 @@
    1.34  
    1.35  ALTER TABLE "event" ADD FOREIGN KEY ("unit_id", "area_id") REFERENCES "area" ("unit_id", "id") ON DELETE CASCADE ON UPDATE CASCADE;
    1.36  ALTER TABLE "event" ADD FOREIGN KEY ("area_id", "issue_id") REFERENCES "issue" ("area_id", "id") ON DELETE CASCADE ON UPDATE CASCADE;
    1.37 +ALTER TABLE "event" ADD FOREIGN KEY ("policy_id", "issue_id") REFERENCES "issue" ("policy_id", "id") ON DELETE CASCADE ON UPDATE CASCADE;
    1.38  
    1.39  ALTER TABLE "event" DROP CONSTRAINT "event_initiative_id_fkey1";
    1.40  ALTER TABLE "event" DROP CONSTRAINT "null_constr_for_issue_state_changed";
    1.41 @@ -677,6 +690,7 @@
    1.42              "scope"           ISNULL  AND
    1.43              "unit_id"         NOTNULL AND
    1.44              "area_id"         NOTNULL AND
    1.45 +            "policy_id"       NOTNULL AND
    1.46              "issue_id"        NOTNULL AND
    1.47              "state"           NOTNULL AND
    1.48              "initiative_id"   ISNULL  AND
    1.49 @@ -698,6 +712,7 @@
    1.50              "scope"           ISNULL  AND
    1.51              "unit_id"         NOTNULL AND
    1.52              "area_id"         NOTNULL AND
    1.53 +            "policy_id"       NOTNULL AND
    1.54              "issue_id"        NOTNULL AND
    1.55              "state"           NOTNULL AND
    1.56              "initiative_id"   NOTNULL AND
    1.57 @@ -714,6 +729,7 @@
    1.58              "scope"           ISNULL  AND
    1.59              "unit_id"         NOTNULL AND
    1.60              "area_id"         NOTNULL AND
    1.61 +            "policy_id"       NOTNULL AND
    1.62              "issue_id"        NOTNULL AND
    1.63              "state"           NOTNULL AND
    1.64              "initiative_id"   NOTNULL AND
    1.65 @@ -730,6 +746,7 @@
    1.66              "scope"           ISNULL  AND
    1.67              "unit_id"         NOTNULL AND
    1.68              "area_id"         NOTNULL AND
    1.69 +            "policy_id"       NOTNULL AND
    1.70              "issue_id"        NOTNULL AND
    1.71              "state"           NOTNULL AND
    1.72              "initiative_id"   NOTNULL AND
    1.73 @@ -751,6 +768,7 @@
    1.74              "scope"           ISNULL  AND
    1.75              "unit_id"         ISNULL  AND
    1.76              "area_id"         ISNULL  AND
    1.77 +            "policy_id"       ISNULL  AND
    1.78              "issue_id"        ISNULL  AND
    1.79              "state"           ISNULL  AND
    1.80              "initiative_id"   ISNULL  AND
    1.81 @@ -767,6 +785,7 @@
    1.82              "scope"           ISNULL  AND
    1.83              "unit_id"         ISNULL  AND
    1.84              "area_id"         ISNULL  AND
    1.85 +            "policy_id"       ISNULL  AND
    1.86              "issue_id"        ISNULL  AND
    1.87              "state"           ISNULL  AND
    1.88              "initiative_id"   ISNULL  AND
    1.89 @@ -783,6 +802,7 @@
    1.90              "scope"           ISNULL  AND
    1.91              "unit_id"         ISNULL  AND
    1.92              "area_id"         ISNULL  AND
    1.93 +            "policy_id"       ISNULL  AND
    1.94              "issue_id"        ISNULL  AND
    1.95              "state"           ISNULL  AND
    1.96              "initiative_id"   ISNULL  AND
    1.97 @@ -799,6 +819,7 @@
    1.98              "scope"           ISNULL  AND
    1.99              "unit_id"         NOTNULL AND
   1.100              "area_id"         NOTNULL AND
   1.101 +            "policy_id"       NOTNULL AND
   1.102              "issue_id"        NOTNULL AND
   1.103              "state"           NOTNULL AND
   1.104              "initiative_id"   ISNULL  AND
   1.105 @@ -815,6 +836,7 @@
   1.106              "scope"           ISNULL  AND
   1.107              "unit_id"         NOTNULL AND
   1.108              "area_id"         NOTNULL AND
   1.109 +            "policy_id"       NOTNULL AND
   1.110              "issue_id"        NOTNULL AND
   1.111              "state"           NOTNULL AND
   1.112              "initiative_id"   NOTNULL AND
   1.113 @@ -831,6 +853,7 @@
   1.114              "scope"           ISNULL  AND
   1.115              "unit_id"         NOTNULL AND
   1.116              "area_id"         NOTNULL AND
   1.117 +            "policy_id"       NOTNULL AND
   1.118              "issue_id"        NOTNULL AND
   1.119              "state"           NOTNULL AND
   1.120              "initiative_id"   NOTNULL AND
   1.121 @@ -847,6 +870,7 @@
   1.122              "scope"           ISNULL  AND
   1.123              "unit_id"         NOTNULL AND
   1.124              "area_id"         NOTNULL AND
   1.125 +            "policy_id"       NOTNULL AND
   1.126              "issue_id"        NOTNULL AND
   1.127              "state"           NOTNULL AND
   1.128              "initiative_id"   NOTNULL AND
   1.129 @@ -863,6 +887,7 @@
   1.130              "scope"           ISNULL  AND
   1.131              "unit_id"         NOTNULL AND
   1.132              "area_id"         NOTNULL AND
   1.133 +            "policy_id"       NOTNULL AND
   1.134              "issue_id"        NOTNULL AND
   1.135              "state"           NOTNULL AND
   1.136              "initiative_id"   NOTNULL AND
   1.137 @@ -880,6 +905,7 @@
   1.138              "scope"           NOTNULL AND
   1.139              "unit_id"         NOTNULL AND
   1.140              ("area_id"  NOTNULL) = ("scope" != 'unit'::"delegation_scope") AND
   1.141 +            "policy_id"       ISNULL  AND
   1.142              ("issue_id" NOTNULL) = ("scope" = 'issue'::"delegation_scope") AND
   1.143              ("state"    NOTNULL) = ("scope" = 'issue'::"delegation_scope") AND
   1.144              "initiative_id"   ISNULL  AND
   1.145 @@ -896,6 +922,7 @@
   1.146              "scope"           ISNULL  AND
   1.147              "unit_id"         ISNULL  AND
   1.148              "area_id"         ISNULL  AND
   1.149 +            "policy_id"       ISNULL  AND
   1.150              "issue_id"        ISNULL  AND
   1.151              "state"           ISNULL  AND
   1.152              "initiative_id"   ISNULL  AND
   1.153 @@ -925,10 +952,11 @@
   1.154            FOR SHARE;
   1.155          INSERT INTO "event" (
   1.156              "event",
   1.157 -            "unit_id", "area_id", "issue_id", "state"
   1.158 +            "unit_id", "area_id", "policy_id", "issue_id", "state"
   1.159            ) VALUES (
   1.160              'issue_state_changed',
   1.161 -            "area_row"."unit_id", NEW."area_id", NEW."id", NEW."state"
   1.162 +            "area_row"."unit_id", NEW."area_id", NEW."policy_id",
   1.163 +            NEW."id", NEW."state"
   1.164            );
   1.165        END IF;
   1.166        RETURN NULL;
   1.167 @@ -971,11 +999,11 @@
   1.168        END IF;
   1.169        INSERT INTO "event" (
   1.170            "event", "member_id",
   1.171 -          "unit_id", "area_id", "issue_id", "state",
   1.172 +          "unit_id", "area_id", "policy_id", "issue_id", "state",
   1.173            "initiative_id", "draft_id"
   1.174          ) VALUES (
   1.175            "event_v", NEW."author_id",
   1.176 -          "area_row"."unit_id", "issue_row"."area_id",
   1.177 +          "area_row"."unit_id", "issue_row"."area_id", "issue_row"."policy_id",
   1.178            "initiative_row"."issue_id", "issue_row"."state",
   1.179            NEW."initiative_id", NEW."id"
   1.180          );
   1.181 @@ -1001,11 +1029,12 @@
   1.182            WHERE "initiative_id" = NEW."id" FOR SHARE;
   1.183          INSERT INTO "event" (
   1.184              "event", "member_id",
   1.185 -            "unit_id", "area_id", "issue_id", "state",
   1.186 +            "unit_id", "area_id", "policy_id", "issue_id", "state",
   1.187              "initiative_id", "draft_id"
   1.188            ) VALUES (
   1.189              'initiative_revoked', NEW."revoked_by_member_id",
   1.190              "area_row"."unit_id", "issue_row"."area_id",
   1.191 +            "issue_row"."policy_id",
   1.192              NEW."issue_id", "issue_row"."state",
   1.193              NEW."id", "draft_id_v"
   1.194            );
   1.195 @@ -1031,11 +1060,11 @@
   1.196          WHERE "id" = "issue_row"."area_id" FOR SHARE;
   1.197        INSERT INTO "event" (
   1.198            "event", "member_id",
   1.199 -          "unit_id", "area_id", "issue_id", "state",
   1.200 +          "unit_id", "area_id", "policy_id", "issue_id", "state",
   1.201            "initiative_id", "suggestion_id"
   1.202          ) VALUES (
   1.203            'suggestion_created', NEW."author_id",
   1.204 -          "area_row"."unit_id", "issue_row"."area_id",
   1.205 +          "area_row"."unit_id", "issue_row"."area_id", "issue_row"."policy_id",
   1.206            "initiative_row"."issue_id", "issue_row"."state",
   1.207            NEW."initiative_id", NEW."id"
   1.208          );
   1.209 @@ -1061,11 +1090,12 @@
   1.210            WHERE "id" = "issue_row"."area_id" FOR SHARE;
   1.211          INSERT INTO "event" (
   1.212              "event",
   1.213 -            "unit_id", "area_id", "issue_id", "state",
   1.214 +            "unit_id", "area_id", "policy_id", "issue_id", "state",
   1.215              "initiative_id", "suggestion_id"
   1.216            ) VALUES (
   1.217              'suggestion_removed',
   1.218              "area_row"."unit_id", "issue_row"."area_id",
   1.219 +            "issue_row"."policy_id",
   1.220              "initiative_row"."issue_id", "issue_row"."state",
   1.221              OLD."initiative_id", OLD."id"
   1.222            );
   1.223 @@ -1229,11 +1259,12 @@
   1.224          IF "issue_row"."id" NOTNULL THEN
   1.225            INSERT INTO "event" (
   1.226                "event", "member_id",
   1.227 -              "unit_id", "area_id", "issue_id", "state",
   1.228 +              "unit_id", "area_id", "policy_id", "issue_id", "state",
   1.229                "boolean_value"
   1.230              ) VALUES (
   1.231                'interest', OLD."member_id",
   1.232                "area_row"."unit_id", "issue_row"."area_id",
   1.233 +              "issue_row"."policy_id",
   1.234                OLD."issue_id", "issue_row"."state",
   1.235                FALSE
   1.236              );
   1.237 @@ -1246,11 +1277,12 @@
   1.238            WHERE "id" = "issue_row"."area_id" FOR SHARE;
   1.239          INSERT INTO "event" (
   1.240              "event", "member_id",
   1.241 -            "unit_id", "area_id", "issue_id", "state",
   1.242 +            "unit_id", "area_id", "policy_id", "issue_id", "state",
   1.243              "boolean_value"
   1.244            ) VALUES (
   1.245              'interest', NEW."member_id",
   1.246              "area_row"."unit_id", "issue_row"."area_id",
   1.247 +            "issue_row"."policy_id",
   1.248              NEW."issue_id", "issue_row"."state",
   1.249              TRUE
   1.250            );
   1.251 @@ -1295,11 +1327,12 @@
   1.252                WHERE "id" = "issue_row"."area_id" FOR SHARE;
   1.253              INSERT INTO "event" (
   1.254                  "event", "member_id",
   1.255 -                "unit_id", "area_id", "issue_id", "state",
   1.256 +                "unit_id", "area_id", "policy_id", "issue_id", "state",
   1.257                  "initiative_id", "boolean_value"
   1.258                ) VALUES (
   1.259                  'initiator', OLD."member_id",
   1.260                  "area_row"."unit_id", "issue_row"."area_id",
   1.261 +                "issue_row"."policy_id",
   1.262                  "issue_row"."id", "issue_row"."state",
   1.263                  OLD."initiative_id", FALSE
   1.264                );
   1.265 @@ -1316,11 +1349,12 @@
   1.266              WHERE "id" = "issue_row"."area_id" FOR SHARE;
   1.267            INSERT INTO "event" (
   1.268                "event", "member_id",
   1.269 -              "unit_id", "area_id", "issue_id", "state",
   1.270 +              "unit_id", "area_id", "policy_id", "issue_id", "state",
   1.271                "initiative_id", "boolean_value"
   1.272              ) VALUES (
   1.273                'initiator', NEW."member_id",
   1.274                "area_row"."unit_id", "issue_row"."area_id",
   1.275 +              "issue_row"."policy_id",
   1.276                "issue_row"."id", "issue_row"."state",
   1.277                NEW."initiative_id", TRUE
   1.278              );
   1.279 @@ -1357,11 +1391,12 @@
   1.280                WHERE "id" = "issue_row"."area_id" FOR SHARE;
   1.281              INSERT INTO "event" (
   1.282                  "event", "member_id",
   1.283 -                "unit_id", "area_id", "issue_id", "state",
   1.284 +                "unit_id", "area_id", "policy_id", "issue_id", "state",
   1.285                  "initiative_id", "draft_id"
   1.286                ) VALUES (
   1.287                  'support_updated', NEW."member_id",
   1.288                  "area_row"."unit_id", "issue_row"."area_id",
   1.289 +                "issue_row"."policy_id",
   1.290                  "issue_row"."id", "issue_row"."state",
   1.291                  NEW."initiative_id", NEW."draft_id"
   1.292                );
   1.293 @@ -1380,11 +1415,12 @@
   1.294              WHERE "id" = "issue_row"."area_id" FOR SHARE;
   1.295            INSERT INTO "event" (
   1.296                "event", "member_id",
   1.297 -              "unit_id", "area_id", "issue_id", "state",
   1.298 +              "unit_id", "area_id", "policy_id", "issue_id", "state",
   1.299                "initiative_id", "boolean_value"
   1.300              ) VALUES (
   1.301                'support', OLD."member_id",
   1.302                "area_row"."unit_id", "issue_row"."area_id",
   1.303 +              "issue_row"."policy_id",
   1.304                "issue_row"."id", "issue_row"."state",
   1.305                OLD."initiative_id", FALSE
   1.306              );
   1.307 @@ -1397,11 +1433,12 @@
   1.308            WHERE "id" = "issue_row"."area_id" FOR SHARE;
   1.309          INSERT INTO "event" (
   1.310              "event", "member_id",
   1.311 -            "unit_id", "area_id", "issue_id", "state",
   1.312 +            "unit_id", "area_id", "policy_id", "issue_id", "state",
   1.313              "initiative_id", "draft_id", "boolean_value"
   1.314            ) VALUES (
   1.315              'support', NEW."member_id",
   1.316              "area_row"."unit_id", "issue_row"."area_id",
   1.317 +            "issue_row"."policy_id",
   1.318              "issue_row"."id", "issue_row"."state",
   1.319              NEW."initiative_id", NEW."draft_id", TRUE
   1.320            );
   1.321 @@ -1454,12 +1491,13 @@
   1.322              WHERE "id" = "issue_row"."area_id" FOR SHARE;
   1.323            INSERT INTO "event" (
   1.324                "event", "member_id",
   1.325 -              "unit_id", "area_id", "issue_id", "state",
   1.326 +              "unit_id", "area_id", "policy_id", "issue_id", "state",
   1.327                "initiative_id", "suggestion_id",
   1.328                "boolean_value", "numeric_value"
   1.329              ) VALUES (
   1.330                'suggestion_rated', OLD."member_id",
   1.331                "area_row"."unit_id", "issue_row"."area_id",
   1.332 +              "issue_row"."policy_id",
   1.333                "initiative_row"."issue_id", "issue_row"."state",
   1.334                OLD."initiative_id", OLD."suggestion_id",
   1.335                NULL, 0
   1.336 @@ -1475,12 +1513,13 @@
   1.337            WHERE "id" = "issue_row"."area_id" FOR SHARE;
   1.338          INSERT INTO "event" (
   1.339              "event", "member_id",
   1.340 -            "unit_id", "area_id", "issue_id", "state",
   1.341 +            "unit_id", "area_id", "policy_id", "issue_id", "state",
   1.342              "initiative_id", "suggestion_id",
   1.343              "boolean_value", "numeric_value"
   1.344            ) VALUES (
   1.345              'suggestion_rated', NEW."member_id",
   1.346              "area_row"."unit_id", "issue_row"."area_id",
   1.347 +            "issue_row"."policy_id",
   1.348              "initiative_row"."issue_id", "issue_row"."state",
   1.349              NEW."initiative_id", NEW."suggestion_id",
   1.350              NEW."fulfilled", NEW."degree"