liquid_feedback_core

changeset 536:750b0be5acb6

Work on unit/area/policy events: added column "policy_id" to "event" table, added new "event_type"s
author jbe
date Mon Jun 26 15:54:26 2017 +0200 (2017-06-26)
parents 8b6433096a58
children aa261389c993
files core.sql update/core-update.v3.2.2-v4.0.0.sql
line diff
     1.1 --- a/core.sql	Sun Jun 18 02:24:50 2017 +0200
     1.2 +++ b/core.sql	Mon Jun 26 15:54:26 2017 +0200
     1.3 @@ -738,6 +738,7 @@
     1.4  
     1.5  CREATE TABLE "issue" (
     1.6          UNIQUE ("area_id", "id"),  -- index needed for foreign-key on table "event"
     1.7 +        UNIQUE ("policy_id", "id"),  -- index needed for foreign-key on table "event"
     1.8          "id"                    SERIAL4         PRIMARY KEY,
     1.9          "area_id"               INT4            NOT NULL REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    1.10          "policy_id"             INT4            NOT NULL REFERENCES "policy" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
    1.11 @@ -804,7 +805,6 @@
    1.12            --("accepted" ISNULL OR "admission_snapshot_id" NOTNULL) AND
    1.13            ("half_frozen" ISNULL OR "half_freeze_snapshot_id" NOTNULL) AND
    1.14            ("fully_frozen" ISNULL OR "full_freeze_snapshot_id" NOTNULL) ) );
    1.15 -CREATE INDEX "issue_policy_id_idx" ON "issue" ("policy_id");
    1.16  CREATE INDEX "issue_state_idx" ON "issue" ("state");
    1.17  CREATE INDEX "issue_created_idx" ON "issue" ("created");
    1.18  CREATE INDEX "issue_accepted_idx" ON "issue" ("accepted");
    1.19 @@ -1382,6 +1382,15 @@
    1.20  
    1.21  
    1.22  CREATE TYPE "event_type" AS ENUM (
    1.23 +        'unit_created',
    1.24 +        'unit_updated',
    1.25 +        'unit_removed',
    1.26 +        'subject_area_created',
    1.27 +        'subject_area_updated',
    1.28 +        'subject_area_removed',
    1.29 +        'policy_created',
    1.30 +        'policy_updated',
    1.31 +        'policy_removed',
    1.32          'issue_state_changed',
    1.33          'initiative_created_in_new_issue',
    1.34          'initiative_created_in_existing_issue',
    1.35 @@ -1416,8 +1425,10 @@
    1.36          "unit_id"               INT4            REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    1.37          "area_id"               INT4,
    1.38          FOREIGN KEY ("unit_id", "area_id") REFERENCES "area" ("unit_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
    1.39 +        "policy_id"             INT4            REFERENCES "policy" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    1.40          "issue_id"              INT4            REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    1.41          FOREIGN KEY ("area_id", "issue_id") REFERENCES "issue" ("area_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
    1.42 +        FOREIGN KEY ("policy_id", "issue_id") REFERENCES "issue" ("policy_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
    1.43          "state"                 "issue_state",
    1.44          "initiative_id"         INT4,
    1.45          "draft_id"              INT8,
    1.46 @@ -1444,6 +1455,7 @@
    1.47              "scope"           ISNULL  AND
    1.48              "unit_id"         NOTNULL AND
    1.49              "area_id"         NOTNULL AND
    1.50 +            "policy_id"       NOTNULL AND
    1.51              "issue_id"        NOTNULL AND
    1.52              "state"           NOTNULL AND
    1.53              "initiative_id"   ISNULL  AND
    1.54 @@ -1465,6 +1477,7 @@
    1.55              "scope"           ISNULL  AND
    1.56              "unit_id"         NOTNULL AND
    1.57              "area_id"         NOTNULL AND
    1.58 +            "policy_id"       NOTNULL AND
    1.59              "issue_id"        NOTNULL AND
    1.60              "state"           NOTNULL AND
    1.61              "initiative_id"   NOTNULL AND
    1.62 @@ -1481,6 +1494,7 @@
    1.63              "scope"           ISNULL  AND
    1.64              "unit_id"         NOTNULL AND
    1.65              "area_id"         NOTNULL AND
    1.66 +            "policy_id"       NOTNULL AND
    1.67              "issue_id"        NOTNULL AND
    1.68              "state"           NOTNULL AND
    1.69              "initiative_id"   NOTNULL AND
    1.70 @@ -1497,6 +1511,7 @@
    1.71              "scope"           ISNULL  AND
    1.72              "unit_id"         NOTNULL AND
    1.73              "area_id"         NOTNULL AND
    1.74 +            "policy_id"       NOTNULL AND
    1.75              "issue_id"        NOTNULL AND
    1.76              "state"           NOTNULL AND
    1.77              "initiative_id"   NOTNULL AND
    1.78 @@ -1518,6 +1533,7 @@
    1.79              "scope"           ISNULL  AND
    1.80              "unit_id"         ISNULL  AND
    1.81              "area_id"         ISNULL  AND
    1.82 +            "policy_id"       ISNULL  AND
    1.83              "issue_id"        ISNULL  AND
    1.84              "state"           ISNULL  AND
    1.85              "initiative_id"   ISNULL  AND
    1.86 @@ -1534,6 +1550,7 @@
    1.87              "scope"           ISNULL  AND
    1.88              "unit_id"         ISNULL  AND
    1.89              "area_id"         ISNULL  AND
    1.90 +            "policy_id"       ISNULL  AND
    1.91              "issue_id"        ISNULL  AND
    1.92              "state"           ISNULL  AND
    1.93              "initiative_id"   ISNULL  AND
    1.94 @@ -1550,6 +1567,7 @@
    1.95              "scope"           ISNULL  AND
    1.96              "unit_id"         ISNULL  AND
    1.97              "area_id"         ISNULL  AND
    1.98 +            "policy_id"       ISNULL  AND
    1.99              "issue_id"        ISNULL  AND
   1.100              "state"           ISNULL  AND
   1.101              "initiative_id"   ISNULL  AND
   1.102 @@ -1566,6 +1584,7 @@
   1.103              "scope"           ISNULL  AND
   1.104              "unit_id"         NOTNULL AND
   1.105              "area_id"         NOTNULL AND
   1.106 +            "policy_id"       NOTNULL AND
   1.107              "issue_id"        NOTNULL AND
   1.108              "state"           NOTNULL AND
   1.109              "initiative_id"   ISNULL  AND
   1.110 @@ -1582,6 +1601,7 @@
   1.111              "scope"           ISNULL  AND
   1.112              "unit_id"         NOTNULL AND
   1.113              "area_id"         NOTNULL AND
   1.114 +            "policy_id"       NOTNULL AND
   1.115              "issue_id"        NOTNULL AND
   1.116              "state"           NOTNULL AND
   1.117              "initiative_id"   NOTNULL AND
   1.118 @@ -1598,6 +1618,7 @@
   1.119              "scope"           ISNULL  AND
   1.120              "unit_id"         NOTNULL AND
   1.121              "area_id"         NOTNULL AND
   1.122 +            "policy_id"       NOTNULL AND
   1.123              "issue_id"        NOTNULL AND
   1.124              "state"           NOTNULL AND
   1.125              "initiative_id"   NOTNULL AND
   1.126 @@ -1614,6 +1635,7 @@
   1.127              "scope"           ISNULL  AND
   1.128              "unit_id"         NOTNULL AND
   1.129              "area_id"         NOTNULL AND
   1.130 +            "policy_id"       NOTNULL AND
   1.131              "issue_id"        NOTNULL AND
   1.132              "state"           NOTNULL AND
   1.133              "initiative_id"   NOTNULL AND
   1.134 @@ -1630,6 +1652,7 @@
   1.135              "scope"           ISNULL  AND
   1.136              "unit_id"         NOTNULL AND
   1.137              "area_id"         NOTNULL AND
   1.138 +            "policy_id"       NOTNULL AND
   1.139              "issue_id"        NOTNULL AND
   1.140              "state"           NOTNULL AND
   1.141              "initiative_id"   NOTNULL AND
   1.142 @@ -1647,6 +1670,7 @@
   1.143              "scope"           NOTNULL AND
   1.144              "unit_id"         NOTNULL AND
   1.145              ("area_id"  NOTNULL) = ("scope" != 'unit'::"delegation_scope") AND
   1.146 +            "policy_id"       ISNULL  AND
   1.147              ("issue_id" NOTNULL) = ("scope" = 'issue'::"delegation_scope") AND
   1.148              ("state"    NOTNULL) = ("scope" = 'issue'::"delegation_scope") AND
   1.149              "initiative_id"   ISNULL  AND
   1.150 @@ -1663,6 +1687,7 @@
   1.151              "scope"           ISNULL  AND
   1.152              "unit_id"         ISNULL  AND
   1.153              "area_id"         ISNULL  AND
   1.154 +            "policy_id"       ISNULL  AND
   1.155              "issue_id"        ISNULL  AND
   1.156              "state"           ISNULL  AND
   1.157              "initiative_id"   ISNULL  AND
   1.158 @@ -1768,10 +1793,11 @@
   1.159            FOR SHARE;
   1.160          INSERT INTO "event" (
   1.161              "event",
   1.162 -            "unit_id", "area_id", "issue_id", "state"
   1.163 +            "unit_id", "area_id", "policy_id", "issue_id", "state"
   1.164            ) VALUES (
   1.165              'issue_state_changed',
   1.166 -            "area_row"."unit_id", NEW."area_id", NEW."id", NEW."state"
   1.167 +            "area_row"."unit_id", NEW."area_id", NEW."policy_id",
   1.168 +            NEW."id", NEW."state"
   1.169            );
   1.170        END IF;
   1.171        RETURN NULL;
   1.172 @@ -1821,11 +1847,11 @@
   1.173        END IF;
   1.174        INSERT INTO "event" (
   1.175            "event", "member_id",
   1.176 -          "unit_id", "area_id", "issue_id", "state",
   1.177 +          "unit_id", "area_id", "policy_id", "issue_id", "state",
   1.178            "initiative_id", "draft_id"
   1.179          ) VALUES (
   1.180            "event_v", NEW."author_id",
   1.181 -          "area_row"."unit_id", "issue_row"."area_id",
   1.182 +          "area_row"."unit_id", "issue_row"."area_id", "issue_row"."policy_id",
   1.183            "initiative_row"."issue_id", "issue_row"."state",
   1.184            NEW."initiative_id", NEW."id"
   1.185          );
   1.186 @@ -1858,11 +1884,12 @@
   1.187            WHERE "initiative_id" = NEW."id" FOR SHARE;
   1.188          INSERT INTO "event" (
   1.189              "event", "member_id",
   1.190 -            "unit_id", "area_id", "issue_id", "state",
   1.191 +            "unit_id", "area_id", "policy_id", "issue_id", "state",
   1.192              "initiative_id", "draft_id"
   1.193            ) VALUES (
   1.194              'initiative_revoked', NEW."revoked_by_member_id",
   1.195              "area_row"."unit_id", "issue_row"."area_id",
   1.196 +            "issue_row"."policy_id",
   1.197              NEW."issue_id", "issue_row"."state",
   1.198              NEW."id", "draft_id_v"
   1.199            );
   1.200 @@ -1895,11 +1922,11 @@
   1.201          WHERE "id" = "issue_row"."area_id" FOR SHARE;
   1.202        INSERT INTO "event" (
   1.203            "event", "member_id",
   1.204 -          "unit_id", "area_id", "issue_id", "state",
   1.205 +          "unit_id", "area_id", "policy_id", "issue_id", "state",
   1.206            "initiative_id", "suggestion_id"
   1.207          ) VALUES (
   1.208            'suggestion_created', NEW."author_id",
   1.209 -          "area_row"."unit_id", "issue_row"."area_id",
   1.210 +          "area_row"."unit_id", "issue_row"."area_id", "issue_row"."policy_id",
   1.211            "initiative_row"."issue_id", "issue_row"."state",
   1.212            NEW."initiative_id", NEW."id"
   1.213          );
   1.214 @@ -1932,11 +1959,12 @@
   1.215            WHERE "id" = "issue_row"."area_id" FOR SHARE;
   1.216          INSERT INTO "event" (
   1.217              "event",
   1.218 -            "unit_id", "area_id", "issue_id", "state",
   1.219 +            "unit_id", "area_id", "policy_id", "issue_id", "state",
   1.220              "initiative_id", "suggestion_id"
   1.221            ) VALUES (
   1.222              'suggestion_removed',
   1.223              "area_row"."unit_id", "issue_row"."area_id",
   1.224 +            "issue_row"."policy_id",
   1.225              "initiative_row"."issue_id", "issue_row"."state",
   1.226              OLD."initiative_id", OLD."id"
   1.227            );
   1.228 @@ -1984,6 +2012,7 @@
   1.229          END IF;
   1.230          IF
   1.231            OLD."activated" NOTNULL AND
   1.232 +          -- TODO: NEW."activated" ISNULL? OLD."login" NOTNULL?
   1.233            NEW."last_login"      ISNULL AND
   1.234            NEW."login"           ISNULL AND
   1.235            NEW."authority_login" ISNULL AND
   1.236 @@ -2100,11 +2129,12 @@
   1.237          IF "issue_row"."id" NOTNULL THEN
   1.238            INSERT INTO "event" (
   1.239                "event", "member_id",
   1.240 -              "unit_id", "area_id", "issue_id", "state",
   1.241 +              "unit_id", "area_id", "policy_id", "issue_id", "state",
   1.242                "boolean_value"
   1.243              ) VALUES (
   1.244                'interest', OLD."member_id",
   1.245                "area_row"."unit_id", "issue_row"."area_id",
   1.246 +              "issue_row"."policy_id",
   1.247                OLD."issue_id", "issue_row"."state",
   1.248                FALSE
   1.249              );
   1.250 @@ -2117,11 +2147,12 @@
   1.251            WHERE "id" = "issue_row"."area_id" FOR SHARE;
   1.252          INSERT INTO "event" (
   1.253              "event", "member_id",
   1.254 -            "unit_id", "area_id", "issue_id", "state",
   1.255 +            "unit_id", "area_id", "policy_id", "issue_id", "state",
   1.256              "boolean_value"
   1.257            ) VALUES (
   1.258              'interest', NEW."member_id",
   1.259              "area_row"."unit_id", "issue_row"."area_id",
   1.260 +            "issue_row"."policy_id",
   1.261              NEW."issue_id", "issue_row"."state",
   1.262              TRUE
   1.263            );
   1.264 @@ -2166,11 +2197,12 @@
   1.265                WHERE "id" = "issue_row"."area_id" FOR SHARE;
   1.266              INSERT INTO "event" (
   1.267                  "event", "member_id",
   1.268 -                "unit_id", "area_id", "issue_id", "state",
   1.269 +                "unit_id", "area_id", "policy_id", "issue_id", "state",
   1.270                  "initiative_id", "boolean_value"
   1.271                ) VALUES (
   1.272                  'initiator', OLD."member_id",
   1.273                  "area_row"."unit_id", "issue_row"."area_id",
   1.274 +                "issue_row"."policy_id",
   1.275                  "issue_row"."id", "issue_row"."state",
   1.276                  OLD."initiative_id", FALSE
   1.277                );
   1.278 @@ -2187,11 +2219,12 @@
   1.279              WHERE "id" = "issue_row"."area_id" FOR SHARE;
   1.280            INSERT INTO "event" (
   1.281                "event", "member_id",
   1.282 -              "unit_id", "area_id", "issue_id", "state",
   1.283 +              "unit_id", "area_id", "policy_id", "issue_id", "state",
   1.284                "initiative_id", "boolean_value"
   1.285              ) VALUES (
   1.286                'initiator', NEW."member_id",
   1.287                "area_row"."unit_id", "issue_row"."area_id",
   1.288 +              "issue_row"."policy_id",
   1.289                "issue_row"."id", "issue_row"."state",
   1.290                NEW."initiative_id", TRUE
   1.291              );
   1.292 @@ -2228,11 +2261,12 @@
   1.293                WHERE "id" = "issue_row"."area_id" FOR SHARE;
   1.294              INSERT INTO "event" (
   1.295                  "event", "member_id",
   1.296 -                "unit_id", "area_id", "issue_id", "state",
   1.297 +                "unit_id", "area_id", "policy_id", "issue_id", "state",
   1.298                  "initiative_id", "draft_id"
   1.299                ) VALUES (
   1.300                  'support_updated', NEW."member_id",
   1.301                  "area_row"."unit_id", "issue_row"."area_id",
   1.302 +                "issue_row"."policy_id",
   1.303                  "issue_row"."id", "issue_row"."state",
   1.304                  NEW."initiative_id", NEW."draft_id"
   1.305                );
   1.306 @@ -2251,11 +2285,12 @@
   1.307              WHERE "id" = "issue_row"."area_id" FOR SHARE;
   1.308            INSERT INTO "event" (
   1.309                "event", "member_id",
   1.310 -              "unit_id", "area_id", "issue_id", "state",
   1.311 +              "unit_id", "area_id", "policy_id", "issue_id", "state",
   1.312                "initiative_id", "boolean_value"
   1.313              ) VALUES (
   1.314                'support', OLD."member_id",
   1.315                "area_row"."unit_id", "issue_row"."area_id",
   1.316 +              "issue_row"."policy_id",
   1.317                "issue_row"."id", "issue_row"."state",
   1.318                OLD."initiative_id", FALSE
   1.319              );
   1.320 @@ -2268,11 +2303,12 @@
   1.321            WHERE "id" = "issue_row"."area_id" FOR SHARE;
   1.322          INSERT INTO "event" (
   1.323              "event", "member_id",
   1.324 -            "unit_id", "area_id", "issue_id", "state",
   1.325 +            "unit_id", "area_id", "policy_id", "issue_id", "state",
   1.326              "initiative_id", "draft_id", "boolean_value"
   1.327            ) VALUES (
   1.328              'support', NEW."member_id",
   1.329              "area_row"."unit_id", "issue_row"."area_id",
   1.330 +            "issue_row"."policy_id",
   1.331              "issue_row"."id", "issue_row"."state",
   1.332              NEW."initiative_id", NEW."draft_id", TRUE
   1.333            );
   1.334 @@ -2325,12 +2361,13 @@
   1.335              WHERE "id" = "issue_row"."area_id" FOR SHARE;
   1.336            INSERT INTO "event" (
   1.337                "event", "member_id",
   1.338 -              "unit_id", "area_id", "issue_id", "state",
   1.339 +              "unit_id", "area_id", "policy_id", "issue_id", "state",
   1.340                "initiative_id", "suggestion_id",
   1.341                "boolean_value", "numeric_value"
   1.342              ) VALUES (
   1.343                'suggestion_rated', OLD."member_id",
   1.344                "area_row"."unit_id", "issue_row"."area_id",
   1.345 +              "issue_row"."policy_id",
   1.346                "initiative_row"."issue_id", "issue_row"."state",
   1.347                OLD."initiative_id", OLD."suggestion_id",
   1.348                NULL, 0
   1.349 @@ -2346,12 +2383,13 @@
   1.350            WHERE "id" = "issue_row"."area_id" FOR SHARE;
   1.351          INSERT INTO "event" (
   1.352              "event", "member_id",
   1.353 -            "unit_id", "area_id", "issue_id", "state",
   1.354 +            "unit_id", "area_id", "policy_id", "issue_id", "state",
   1.355              "initiative_id", "suggestion_id",
   1.356              "boolean_value", "numeric_value"
   1.357            ) VALUES (
   1.358              'suggestion_rated', NEW."member_id",
   1.359              "area_row"."unit_id", "issue_row"."area_id",
   1.360 +            "issue_row"."policy_id",
   1.361              "initiative_row"."issue_id", "issue_row"."state",
   1.362              NEW."initiative_id", NEW."suggestion_id",
   1.363              NEW."fulfilled", NEW."degree"
     2.1 --- a/update/core-update.v3.2.2-v4.0.0.sql	Sun Jun 18 02:24:50 2017 +0200
     2.2 +++ b/update/core-update.v3.2.2-v4.0.0.sql	Mon Jun 26 15:54:26 2017 +0200
     2.3 @@ -1,3 +1,12 @@
     2.4 +ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'unit_created';
     2.5 +ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'unit_updated';
     2.6 +ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'unit_removed';
     2.7 +ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'subject_area_created';
     2.8 +ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'subject_area_updated';
     2.9 +ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'subject_area_removed';
    2.10 +ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'policy_created';
    2.11 +ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'policy_updated';
    2.12 +ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'policy_removed';
    2.13  ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'suggestion_removed';
    2.14  ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'member_activated';
    2.15  ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'member_removed';
    2.16 @@ -472,6 +481,8 @@
    2.17  
    2.18  ALTER TABLE "issue" ADD UNIQUE ("area_id", "id");
    2.19  DROP INDEX "issue_area_id_idx";
    2.20 +ALTER TABLE "issue" ADD UNIQUE ("policy_id", "id");
    2.21 +DROP INDEX "issue_policy_id_idx";
    2.22  
    2.23  ALTER TABLE "issue" RENAME COLUMN "snapshot" TO "calculated";
    2.24  
    2.25 @@ -653,6 +664,7 @@
    2.26  ALTER TABLE "event" ADD COLUMN "scope"           "delegation_scope";
    2.27  ALTER TABLE "event" ADD COLUMN "unit_id"         INT4    REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
    2.28  ALTER TABLE "event" ADD COLUMN "area_id"         INT4;
    2.29 +ALTER TABLE "event" ADD COLUMN "policy_id"       INT4    REFERENCES "policy" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
    2.30  ALTER TABLE "event" ADD COLUMN "boolean_value"   BOOLEAN;
    2.31  ALTER TABLE "event" ADD COLUMN "numeric_value"   INT4;
    2.32  ALTER TABLE "event" ADD COLUMN "text_value"      TEXT;
    2.33 @@ -660,6 +672,7 @@
    2.34  
    2.35  ALTER TABLE "event" ADD FOREIGN KEY ("unit_id", "area_id") REFERENCES "area" ("unit_id", "id") ON DELETE CASCADE ON UPDATE CASCADE;
    2.36  ALTER TABLE "event" ADD FOREIGN KEY ("area_id", "issue_id") REFERENCES "issue" ("area_id", "id") ON DELETE CASCADE ON UPDATE CASCADE;
    2.37 +ALTER TABLE "event" ADD FOREIGN KEY ("policy_id", "issue_id") REFERENCES "issue" ("policy_id", "id") ON DELETE CASCADE ON UPDATE CASCADE;
    2.38  
    2.39  ALTER TABLE "event" DROP CONSTRAINT "event_initiative_id_fkey1";
    2.40  ALTER TABLE "event" DROP CONSTRAINT "null_constr_for_issue_state_changed";
    2.41 @@ -677,6 +690,7 @@
    2.42              "scope"           ISNULL  AND
    2.43              "unit_id"         NOTNULL AND
    2.44              "area_id"         NOTNULL AND
    2.45 +            "policy_id"       NOTNULL AND
    2.46              "issue_id"        NOTNULL AND
    2.47              "state"           NOTNULL AND
    2.48              "initiative_id"   ISNULL  AND
    2.49 @@ -698,6 +712,7 @@
    2.50              "scope"           ISNULL  AND
    2.51              "unit_id"         NOTNULL AND
    2.52              "area_id"         NOTNULL AND
    2.53 +            "policy_id"       NOTNULL AND
    2.54              "issue_id"        NOTNULL AND
    2.55              "state"           NOTNULL AND
    2.56              "initiative_id"   NOTNULL AND
    2.57 @@ -714,6 +729,7 @@
    2.58              "scope"           ISNULL  AND
    2.59              "unit_id"         NOTNULL AND
    2.60              "area_id"         NOTNULL AND
    2.61 +            "policy_id"       NOTNULL AND
    2.62              "issue_id"        NOTNULL AND
    2.63              "state"           NOTNULL AND
    2.64              "initiative_id"   NOTNULL AND
    2.65 @@ -730,6 +746,7 @@
    2.66              "scope"           ISNULL  AND
    2.67              "unit_id"         NOTNULL AND
    2.68              "area_id"         NOTNULL AND
    2.69 +            "policy_id"       NOTNULL AND
    2.70              "issue_id"        NOTNULL AND
    2.71              "state"           NOTNULL AND
    2.72              "initiative_id"   NOTNULL AND
    2.73 @@ -751,6 +768,7 @@
    2.74              "scope"           ISNULL  AND
    2.75              "unit_id"         ISNULL  AND
    2.76              "area_id"         ISNULL  AND
    2.77 +            "policy_id"       ISNULL  AND
    2.78              "issue_id"        ISNULL  AND
    2.79              "state"           ISNULL  AND
    2.80              "initiative_id"   ISNULL  AND
    2.81 @@ -767,6 +785,7 @@
    2.82              "scope"           ISNULL  AND
    2.83              "unit_id"         ISNULL  AND
    2.84              "area_id"         ISNULL  AND
    2.85 +            "policy_id"       ISNULL  AND
    2.86              "issue_id"        ISNULL  AND
    2.87              "state"           ISNULL  AND
    2.88              "initiative_id"   ISNULL  AND
    2.89 @@ -783,6 +802,7 @@
    2.90              "scope"           ISNULL  AND
    2.91              "unit_id"         ISNULL  AND
    2.92              "area_id"         ISNULL  AND
    2.93 +            "policy_id"       ISNULL  AND
    2.94              "issue_id"        ISNULL  AND
    2.95              "state"           ISNULL  AND
    2.96              "initiative_id"   ISNULL  AND
    2.97 @@ -799,6 +819,7 @@
    2.98              "scope"           ISNULL  AND
    2.99              "unit_id"         NOTNULL AND
   2.100              "area_id"         NOTNULL AND
   2.101 +            "policy_id"       NOTNULL AND
   2.102              "issue_id"        NOTNULL AND
   2.103              "state"           NOTNULL AND
   2.104              "initiative_id"   ISNULL  AND
   2.105 @@ -815,6 +836,7 @@
   2.106              "scope"           ISNULL  AND
   2.107              "unit_id"         NOTNULL AND
   2.108              "area_id"         NOTNULL AND
   2.109 +            "policy_id"       NOTNULL AND
   2.110              "issue_id"        NOTNULL AND
   2.111              "state"           NOTNULL AND
   2.112              "initiative_id"   NOTNULL AND
   2.113 @@ -831,6 +853,7 @@
   2.114              "scope"           ISNULL  AND
   2.115              "unit_id"         NOTNULL AND
   2.116              "area_id"         NOTNULL AND
   2.117 +            "policy_id"       NOTNULL AND
   2.118              "issue_id"        NOTNULL AND
   2.119              "state"           NOTNULL AND
   2.120              "initiative_id"   NOTNULL AND
   2.121 @@ -847,6 +870,7 @@
   2.122              "scope"           ISNULL  AND
   2.123              "unit_id"         NOTNULL AND
   2.124              "area_id"         NOTNULL AND
   2.125 +            "policy_id"       NOTNULL AND
   2.126              "issue_id"        NOTNULL AND
   2.127              "state"           NOTNULL AND
   2.128              "initiative_id"   NOTNULL AND
   2.129 @@ -863,6 +887,7 @@
   2.130              "scope"           ISNULL  AND
   2.131              "unit_id"         NOTNULL AND
   2.132              "area_id"         NOTNULL AND
   2.133 +            "policy_id"       NOTNULL AND
   2.134              "issue_id"        NOTNULL AND
   2.135              "state"           NOTNULL AND
   2.136              "initiative_id"   NOTNULL AND
   2.137 @@ -880,6 +905,7 @@
   2.138              "scope"           NOTNULL AND
   2.139              "unit_id"         NOTNULL AND
   2.140              ("area_id"  NOTNULL) = ("scope" != 'unit'::"delegation_scope") AND
   2.141 +            "policy_id"       ISNULL  AND
   2.142              ("issue_id" NOTNULL) = ("scope" = 'issue'::"delegation_scope") AND
   2.143              ("state"    NOTNULL) = ("scope" = 'issue'::"delegation_scope") AND
   2.144              "initiative_id"   ISNULL  AND
   2.145 @@ -896,6 +922,7 @@
   2.146              "scope"           ISNULL  AND
   2.147              "unit_id"         ISNULL  AND
   2.148              "area_id"         ISNULL  AND
   2.149 +            "policy_id"       ISNULL  AND
   2.150              "issue_id"        ISNULL  AND
   2.151              "state"           ISNULL  AND
   2.152              "initiative_id"   ISNULL  AND
   2.153 @@ -925,10 +952,11 @@
   2.154            FOR SHARE;
   2.155          INSERT INTO "event" (
   2.156              "event",
   2.157 -            "unit_id", "area_id", "issue_id", "state"
   2.158 +            "unit_id", "area_id", "policy_id", "issue_id", "state"
   2.159            ) VALUES (
   2.160              'issue_state_changed',
   2.161 -            "area_row"."unit_id", NEW."area_id", NEW."id", NEW."state"
   2.162 +            "area_row"."unit_id", NEW."area_id", NEW."policy_id",
   2.163 +            NEW."id", NEW."state"
   2.164            );
   2.165        END IF;
   2.166        RETURN NULL;
   2.167 @@ -971,11 +999,11 @@
   2.168        END IF;
   2.169        INSERT INTO "event" (
   2.170            "event", "member_id",
   2.171 -          "unit_id", "area_id", "issue_id", "state",
   2.172 +          "unit_id", "area_id", "policy_id", "issue_id", "state",
   2.173            "initiative_id", "draft_id"
   2.174          ) VALUES (
   2.175            "event_v", NEW."author_id",
   2.176 -          "area_row"."unit_id", "issue_row"."area_id",
   2.177 +          "area_row"."unit_id", "issue_row"."area_id", "issue_row"."policy_id",
   2.178            "initiative_row"."issue_id", "issue_row"."state",
   2.179            NEW."initiative_id", NEW."id"
   2.180          );
   2.181 @@ -1001,11 +1029,12 @@
   2.182            WHERE "initiative_id" = NEW."id" FOR SHARE;
   2.183          INSERT INTO "event" (
   2.184              "event", "member_id",
   2.185 -            "unit_id", "area_id", "issue_id", "state",
   2.186 +            "unit_id", "area_id", "policy_id", "issue_id", "state",
   2.187              "initiative_id", "draft_id"
   2.188            ) VALUES (
   2.189              'initiative_revoked', NEW."revoked_by_member_id",
   2.190              "area_row"."unit_id", "issue_row"."area_id",
   2.191 +            "issue_row"."policy_id",
   2.192              NEW."issue_id", "issue_row"."state",
   2.193              NEW."id", "draft_id_v"
   2.194            );
   2.195 @@ -1031,11 +1060,11 @@
   2.196          WHERE "id" = "issue_row"."area_id" FOR SHARE;
   2.197        INSERT INTO "event" (
   2.198            "event", "member_id",
   2.199 -          "unit_id", "area_id", "issue_id", "state",
   2.200 +          "unit_id", "area_id", "policy_id", "issue_id", "state",
   2.201            "initiative_id", "suggestion_id"
   2.202          ) VALUES (
   2.203            'suggestion_created', NEW."author_id",
   2.204 -          "area_row"."unit_id", "issue_row"."area_id",
   2.205 +          "area_row"."unit_id", "issue_row"."area_id", "issue_row"."policy_id",
   2.206            "initiative_row"."issue_id", "issue_row"."state",
   2.207            NEW."initiative_id", NEW."id"
   2.208          );
   2.209 @@ -1061,11 +1090,12 @@
   2.210            WHERE "id" = "issue_row"."area_id" FOR SHARE;
   2.211          INSERT INTO "event" (
   2.212              "event",
   2.213 -            "unit_id", "area_id", "issue_id", "state",
   2.214 +            "unit_id", "area_id", "policy_id", "issue_id", "state",
   2.215              "initiative_id", "suggestion_id"
   2.216            ) VALUES (
   2.217              'suggestion_removed',
   2.218              "area_row"."unit_id", "issue_row"."area_id",
   2.219 +            "issue_row"."policy_id",
   2.220              "initiative_row"."issue_id", "issue_row"."state",
   2.221              OLD."initiative_id", OLD."id"
   2.222            );
   2.223 @@ -1229,11 +1259,12 @@
   2.224          IF "issue_row"."id" NOTNULL THEN
   2.225            INSERT INTO "event" (
   2.226                "event", "member_id",
   2.227 -              "unit_id", "area_id", "issue_id", "state",
   2.228 +              "unit_id", "area_id", "policy_id", "issue_id", "state",
   2.229                "boolean_value"
   2.230              ) VALUES (
   2.231                'interest', OLD."member_id",
   2.232                "area_row"."unit_id", "issue_row"."area_id",
   2.233 +              "issue_row"."policy_id",
   2.234                OLD."issue_id", "issue_row"."state",
   2.235                FALSE
   2.236              );
   2.237 @@ -1246,11 +1277,12 @@
   2.238            WHERE "id" = "issue_row"."area_id" FOR SHARE;
   2.239          INSERT INTO "event" (
   2.240              "event", "member_id",
   2.241 -            "unit_id", "area_id", "issue_id", "state",
   2.242 +            "unit_id", "area_id", "policy_id", "issue_id", "state",
   2.243              "boolean_value"
   2.244            ) VALUES (
   2.245              'interest', NEW."member_id",
   2.246              "area_row"."unit_id", "issue_row"."area_id",
   2.247 +            "issue_row"."policy_id",
   2.248              NEW."issue_id", "issue_row"."state",
   2.249              TRUE
   2.250            );
   2.251 @@ -1295,11 +1327,12 @@
   2.252                WHERE "id" = "issue_row"."area_id" FOR SHARE;
   2.253              INSERT INTO "event" (
   2.254                  "event", "member_id",
   2.255 -                "unit_id", "area_id", "issue_id", "state",
   2.256 +                "unit_id", "area_id", "policy_id", "issue_id", "state",
   2.257                  "initiative_id", "boolean_value"
   2.258                ) VALUES (
   2.259                  'initiator', OLD."member_id",
   2.260                  "area_row"."unit_id", "issue_row"."area_id",
   2.261 +                "issue_row"."policy_id",
   2.262                  "issue_row"."id", "issue_row"."state",
   2.263                  OLD."initiative_id", FALSE
   2.264                );
   2.265 @@ -1316,11 +1349,12 @@
   2.266              WHERE "id" = "issue_row"."area_id" FOR SHARE;
   2.267            INSERT INTO "event" (
   2.268                "event", "member_id",
   2.269 -              "unit_id", "area_id", "issue_id", "state",
   2.270 +              "unit_id", "area_id", "policy_id", "issue_id", "state",
   2.271                "initiative_id", "boolean_value"
   2.272              ) VALUES (
   2.273                'initiator', NEW."member_id",
   2.274                "area_row"."unit_id", "issue_row"."area_id",
   2.275 +              "issue_row"."policy_id",
   2.276                "issue_row"."id", "issue_row"."state",
   2.277                NEW."initiative_id", TRUE
   2.278              );
   2.279 @@ -1357,11 +1391,12 @@
   2.280                WHERE "id" = "issue_row"."area_id" FOR SHARE;
   2.281              INSERT INTO "event" (
   2.282                  "event", "member_id",
   2.283 -                "unit_id", "area_id", "issue_id", "state",
   2.284 +                "unit_id", "area_id", "policy_id", "issue_id", "state",
   2.285                  "initiative_id", "draft_id"
   2.286                ) VALUES (
   2.287                  'support_updated', NEW."member_id",
   2.288                  "area_row"."unit_id", "issue_row"."area_id",
   2.289 +                "issue_row"."policy_id",
   2.290                  "issue_row"."id", "issue_row"."state",
   2.291                  NEW."initiative_id", NEW."draft_id"
   2.292                );
   2.293 @@ -1380,11 +1415,12 @@
   2.294              WHERE "id" = "issue_row"."area_id" FOR SHARE;
   2.295            INSERT INTO "event" (
   2.296                "event", "member_id",
   2.297 -              "unit_id", "area_id", "issue_id", "state",
   2.298 +              "unit_id", "area_id", "policy_id", "issue_id", "state",
   2.299                "initiative_id", "boolean_value"
   2.300              ) VALUES (
   2.301                'support', OLD."member_id",
   2.302                "area_row"."unit_id", "issue_row"."area_id",
   2.303 +              "issue_row"."policy_id",
   2.304                "issue_row"."id", "issue_row"."state",
   2.305                OLD."initiative_id", FALSE
   2.306              );
   2.307 @@ -1397,11 +1433,12 @@
   2.308            WHERE "id" = "issue_row"."area_id" FOR SHARE;
   2.309          INSERT INTO "event" (
   2.310              "event", "member_id",
   2.311 -            "unit_id", "area_id", "issue_id", "state",
   2.312 +            "unit_id", "area_id", "policy_id", "issue_id", "state",
   2.313              "initiative_id", "draft_id", "boolean_value"
   2.314            ) VALUES (
   2.315              'support', NEW."member_id",
   2.316              "area_row"."unit_id", "issue_row"."area_id",
   2.317 +            "issue_row"."policy_id",
   2.318              "issue_row"."id", "issue_row"."state",
   2.319              NEW."initiative_id", NEW."draft_id", TRUE
   2.320            );
   2.321 @@ -1454,12 +1491,13 @@
   2.322              WHERE "id" = "issue_row"."area_id" FOR SHARE;
   2.323            INSERT INTO "event" (
   2.324                "event", "member_id",
   2.325 -              "unit_id", "area_id", "issue_id", "state",
   2.326 +              "unit_id", "area_id", "policy_id", "issue_id", "state",
   2.327                "initiative_id", "suggestion_id",
   2.328                "boolean_value", "numeric_value"
   2.329              ) VALUES (
   2.330                'suggestion_rated', OLD."member_id",
   2.331                "area_row"."unit_id", "issue_row"."area_id",
   2.332 +              "issue_row"."policy_id",
   2.333                "initiative_row"."issue_id", "issue_row"."state",
   2.334                OLD."initiative_id", OLD."suggestion_id",
   2.335                NULL, 0
   2.336 @@ -1475,12 +1513,13 @@
   2.337            WHERE "id" = "issue_row"."area_id" FOR SHARE;
   2.338          INSERT INTO "event" (
   2.339              "event", "member_id",
   2.340 -            "unit_id", "area_id", "issue_id", "state",
   2.341 +            "unit_id", "area_id", "policy_id", "issue_id", "state",
   2.342              "initiative_id", "suggestion_id",
   2.343              "boolean_value", "numeric_value"
   2.344            ) VALUES (
   2.345              'suggestion_rated', NEW."member_id",
   2.346              "area_row"."unit_id", "issue_row"."area_id",
   2.347 +            "issue_row"."policy_id",
   2.348              "initiative_row"."issue_id", "issue_row"."state",
   2.349              NEW."initiative_id", NEW."suggestion_id",
   2.350              NEW."fulfilled", NEW."degree"

Impressum / About Us