liquid_feedback_core

changeset 609:314626831c04

Issue based privileges to override unit based privileges
author jbe
date Sat May 16 17:22:30 2020 +0200 (5 months ago)
parents fa3c406a6775
children 22c6d1da7d78
files core.sql
line diff
     1.1 --- a/core.sql	Fri May 15 17:43:06 2020 +0200
     1.2 +++ b/core.sql	Sat May 16 17:22:30 2020 +0200
     1.3 @@ -1119,6 +1119,19 @@
     1.4  COMMENT ON COLUMN "privilege"."weight"           IS 'Voting weight of member in unit';
     1.5  
     1.6  
     1.7 +CREATE TABLE "issue_privilege" (
     1.8 +        PRIMARY KEY ("issue_id", "member_id"),
     1.9 +        "issue_id"              INT4            REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    1.10 +        "member_id"             INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    1.11 +        "initiative_right"      BOOLEAN,
    1.12 +        "voting_right"          BOOLEAN,
    1.13 +        "polling_right"         BOOLEAN,
    1.14 +        "weight"                INT4            CHECK ("weight" >= 0) );
    1.15 +CREATE INDEX "issue_privilege_idx" ON "issue_privilege" ("member_id");
    1.16 +
    1.17 +COMMENT ON TABLE "issue_privilege" IS 'Override of "privilege" table for rights of members in certain issues';
    1.18 +
    1.19 +
    1.20  CREATE TABLE "interest" (
    1.21          PRIMARY KEY ("issue_id", "member_id"),
    1.22          "issue_id"              INT4            REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    1.23 @@ -3739,7 +3752,7 @@
    1.24      "delegation"."id",
    1.25      "delegation"."truster_id",
    1.26      "delegation"."trustee_id",
    1.27 -    "privilege"."weight",
    1.28 +    COALESCE("issue_privilege"."weight", "privilege"."weight") AS "weight",
    1.29      "delegation"."scope"
    1.30    FROM "issue"
    1.31    JOIN "area"
    1.32 @@ -3750,10 +3763,14 @@
    1.33      OR "delegation"."issue_id" = "issue"."id"
    1.34    JOIN "member"
    1.35      ON "delegation"."truster_id" = "member"."id"
    1.36 -  JOIN "privilege"
    1.37 +  LEFT JOIN "privilege"
    1.38      ON "area"."unit_id" = "privilege"."unit_id"
    1.39      AND "delegation"."truster_id" = "privilege"."member_id"
    1.40 -  WHERE "member"."active" AND "privilege"."voting_right"
    1.41 +  LEFT JOIN "issue_privilege"
    1.42 +    ON "issue"."id" = "issue_privilege"."issue_id"
    1.43 +    AND "delegation"."truster_id" = "issue_privilege"."member_id"
    1.44 +  WHERE "member"."active"
    1.45 +  AND COALESCE("issue_privilege"."voting_right", "privilege"."voting_right")
    1.46    ORDER BY
    1.47      "issue"."id",
    1.48      "delegation"."truster_id",
    1.49 @@ -3770,12 +3787,10 @@
    1.50  
    1.51  CREATE VIEW "unit_member" AS
    1.52    SELECT
    1.53 -    "unit"."id"   AS "unit_id",
    1.54 -    "member"."id" AS "member_id",
    1.55 +    "privilege"."unit_id" AS "unit_id",
    1.56 +    "member"."id"         AS "member_id",
    1.57      "privilege"."weight"
    1.58 -  FROM "privilege"
    1.59 -  JOIN "unit"   ON "unit"."id"   = "privilege"."unit_id"
    1.60 -  JOIN "member" ON "member"."id" = "privilege"."member_id"
    1.61 +  FROM "privilege" JOIN "member" ON "member"."id" = "privilege"."member_id"
    1.62    WHERE "privilege"."voting_right" AND "member"."active";
    1.63  
    1.64  COMMENT ON VIEW "unit_member" IS 'Active members with voting right in a unit';
    1.65 @@ -4029,8 +4044,10 @@
    1.66    JOIN "area" ON "area"."id" = "issue"."area_id"
    1.67    LEFT JOIN "privilege" ON
    1.68      "privilege"."member_id" = "member"."id" AND
    1.69 -    "privilege"."unit_id" = "area"."unit_id" AND
    1.70 -    "privilege"."voting_right" = TRUE
    1.71 +    "privilege"."unit_id" = "area"."unit_id"
    1.72 +  LEFT JOIN "issue_privilege" ON
    1.73 +    "issue_privilege"."member_id" = "member"."id" AND
    1.74 +    "issue_privilege"."issue_id" = "event"."issue_id"
    1.75    LEFT JOIN "subscription" ON
    1.76      "subscription"."member_id" = "member"."id" AND
    1.77      "subscription"."unit_id" = "area"."unit_id"
    1.78 @@ -4043,8 +4060,10 @@
    1.79    LEFT JOIN "supporter" ON
    1.80      "supporter"."member_id" = "member"."id" AND
    1.81      "supporter"."initiative_id" = "event"."initiative_id"
    1.82 -  WHERE ("privilege"."member_id" NOTNULL OR "subscription"."member_id" NOTNULL)
    1.83 -  AND ("ignored_area"."member_id" ISNULL OR "interest"."member_id" NOTNULL)
    1.84 +  WHERE (
    1.85 +    COALESCE("issue_privilege"."voting_right", "privilege"."voting_right") OR
    1.86 +    "subscription"."member_id" NOTNULL
    1.87 +  ) AND ("ignored_area"."member_id" ISNULL OR "interest"."member_id" NOTNULL)
    1.88    AND (
    1.89      "event"."event" = 'issue_state_changed'::"event_type" OR
    1.90      ( "event"."event" = 'initiative_revoked'::"event_type" AND
    1.91 @@ -4151,8 +4170,10 @@
    1.92                "self_support"."member_id" = "recipient_id_p"
    1.93              LEFT JOIN "privilege" ON
    1.94                "privilege"."member_id" = "recipient_id_p" AND
    1.95 -              "privilege"."unit_id" = "area"."unit_id" AND
    1.96 -              "privilege"."voting_right" = TRUE
    1.97 +              "privilege"."unit_id" = "area"."unit_id"
    1.98 +            LEFT JOIN "issue_privilege" ON
    1.99 +              "privilege"."member_id" = "recipient_id_p" AND
   1.100 +              "privilege"."issue_id" = "initiative"."issue_id"
   1.101              LEFT JOIN "subscription" ON
   1.102                "subscription"."member_id" = "recipient_id_p" AND
   1.103                "subscription"."unit_id" = "area"."unit_id"
   1.104 @@ -4166,8 +4187,9 @@
   1.105              AND "self_support"."member_id" ISNULL
   1.106              AND NOT "initiative_id_ary" @> ARRAY["initiative"."id"]
   1.107              AND (
   1.108 -              "privilege"."member_id" NOTNULL OR
   1.109 -              "subscription"."member_id" NOTNULL )
   1.110 +              COALESCE(
   1.111 +                "issue_privilege"."voting_right", "privilege"."voting_right"
   1.112 +              ) OR "subscription"."member_id" NOTNULL )
   1.113              AND "ignored_initiative"."member_id" ISNULL
   1.114              AND NOT EXISTS (
   1.115                SELECT NULL FROM "draft"
   1.116 @@ -4597,11 +4619,17 @@
   1.117          END IF;
   1.118          "output_row"."scope_in" := "output_row"."scope_out";
   1.119          "output_row"."member_valid" := EXISTS (
   1.120 -          SELECT NULL FROM "member" JOIN "privilege"
   1.121 +          SELECT NULL FROM "member"
   1.122 +          LEFT JOIN "privilege"
   1.123            ON "privilege"."member_id" = "member"."id"
   1.124            AND "privilege"."unit_id" = "unit_id_v"
   1.125 +          LEFT JOIN "issue_privilege"
   1.126 +          ON "issue_privilege"."member_id" = "member"."id"
   1.127 +          AND "issue_privilege"."issue_id" = "issue_id_p"
   1.128            WHERE "id" = "output_row"."member_id"
   1.129 -          AND "member"."active" AND "privilege"."voting_right"
   1.130 +          AND "member"."active"
   1.131 +          AND COALESCE(
   1.132 +            "issue_privilege"."voting_weight", "privilege"."voting_right")
   1.133          );
   1.134          "simulate_here_v" := (
   1.135            "simulate_v" AND
   1.136 @@ -5347,8 +5375,19 @@
   1.137          VALUES ("area_id_v", "issue_id_p")
   1.138          RETURNING "id" INTO "snapshot_id_v";
   1.139        INSERT INTO "snapshot_population" ("snapshot_id", "member_id", "weight")
   1.140 -        SELECT "snapshot_id_v", "member_id", "weight"
   1.141 -        FROM "unit_member" WHERE "unit_id" = "unit_id_v";
   1.142 +        SELECT
   1.143 +          "snapshot_id_v",
   1.144 +          "member"."id",
   1.145 +          COALESCE("issue_privilege"."weight", "privilege"."weight")
   1.146 +        FROM "member"
   1.147 +        LEFT JOIN "privilege"
   1.148 +        ON "privilege"."unit_id" = "unit_id_v"
   1.149 +        AND "privilege"."member_id" = "member"."id"
   1.150 +        LEFT JOIN "issue_privilege"
   1.151 +        ON "issue_privilege"."issue_id" = "issue_id_v"
   1.152 +        AND "issue_privilege"."member_id" = "member"."id"
   1.153 +        WHERE "member"."active" AND COALESCE(
   1.154 +          "issue_privilege"."voting_right", "privilege"."voting_right");
   1.155        UPDATE "snapshot" SET
   1.156          "population" = (
   1.157            SELECT sum("weight") FROM "snapshot_population"
   1.158 @@ -5371,16 +5410,22 @@
   1.159              "snapshot_id_v" AS "snapshot_id",
   1.160              "issue_id_v"    AS "issue_id",
   1.161              "member"."id"   AS "member_id",
   1.162 -            "privilege"."weight" AS "ownweight"
   1.163 +            COALESCE(
   1.164 +              "issue_privilege"."weight", "privilege"."weight"
   1.165 +            ) AS "ownweight"
   1.166            FROM "issue"
   1.167            JOIN "area" ON "issue"."area_id" = "area"."id"
   1.168            JOIN "interest" ON "issue"."id" = "interest"."issue_id"
   1.169            JOIN "member" ON "interest"."member_id" = "member"."id"
   1.170 -          JOIN "privilege"
   1.171 +          LEFT JOIN "privilege"
   1.172              ON "privilege"."unit_id" = "area"."unit_id"
   1.173              AND "privilege"."member_id" = "member"."id"
   1.174 +          LEFT JOIN "issue_privilege"
   1.175 +            ON "issue_privilege"."issue_id" = "issue_id_v"
   1.176 +            AND "issue_privilege"."member_id" = "member"."id"
   1.177            WHERE "issue"."id" = "issue_id_v"
   1.178 -          AND "member"."active" AND "privilege"."voting_right";
   1.179 +          AND "member"."active" AND COALESCE(
   1.180 +            "issue_privilege"."voting_right", "privilege"."voting_right");
   1.181          FOR "member_id_v" IN
   1.182            SELECT "member_id" FROM "direct_interest_snapshot"
   1.183            WHERE "snapshot_id" = "snapshot_id_v"
   1.184 @@ -5755,10 +5800,16 @@
   1.185            LEFT JOIN "privilege"
   1.186            ON "privilege"."unit_id" = "unit_id_v"
   1.187            AND "privilege"."member_id" = "direct_voter"."member_id"
   1.188 +          LEFT JOIN "issue_privilege"
   1.189 +          ON "issue_privilege"."issue_id" = "issue_id_p"
   1.190 +          AND "issue_privilege"."member_id" = "direct_voter"."member_id"
   1.191            WHERE "direct_voter"."issue_id" = "issue_id_p" AND (
   1.192              "member"."active" = FALSE OR
   1.193 -            "privilege"."voting_right" ISNULL OR
   1.194 -            "privilege"."voting_right" = FALSE
   1.195 +            COALESCE(
   1.196 +              "issue_privilege"."voting_right",
   1.197 +              "privilege"."voting_right",
   1.198 +              FALSE
   1.199 +            ) = FALSE
   1.200            )
   1.201          ) AS "subquery"
   1.202          WHERE "direct_voter"."issue_id" = "issue_id_p"
   1.203 @@ -5769,6 +5820,11 @@
   1.204          WHERE "issue_id" = "issue_id_p"
   1.205          AND "privilege"."unit_id" = "unit_id_v"
   1.206          AND "privilege"."member_id" = "direct_voter"."member_id";
   1.207 +      UPDATE "direct_voter" SET "ownweight" = "issue_privilege"."weight"
   1.208 +        FROM "issue_privilege"
   1.209 +        WHERE "direct_voter"."issue_id" = "issue_id_p"
   1.210 +        AND "issue_privilege"."issue_id" = "issue_id_p"
   1.211 +        AND "issue_privilege"."member_id" = "direct_voter"."member_id";
   1.212        PERFORM "add_vote_delegations"("issue_id_p");
   1.213        -- mark first preferences:
   1.214        UPDATE "vote" SET "first_preference" = "subquery"."first_preference"

Impressum / About Us