liquid_feedback_core

changeset 555:5d098bcc631a

Member verification through organizational units
author jbe
date Sun Sep 17 01:48:45 2017 +0200 (2017-09-17)
parents 3e7ad7233404
children 3f21631a7f6d
files core.sql update/core-update.v3.2.2-v4.0.0.sql
line diff
     1.1 --- a/core.sql	Sat Sep 16 21:20:38 2017 +0200
     1.2 +++ b/core.sql	Sun Sep 17 01:48:45 2017 +0200
     1.3 @@ -597,10 +597,21 @@
     1.4  COMMENT ON COLUMN "unit"."parent_id"          IS 'Parent id of tree node; Multiple roots allowed';
     1.5  COMMENT ON COLUMN "unit"."active"             IS 'TRUE means new issues can be created in areas of this unit';
     1.6  COMMENT ON COLUMN "unit"."external_reference" IS 'Opaque data field to store an external reference';
     1.7 -COMMENT ON COLUMN "unit"."member_count"       IS 'Count of members as determined by column "voting_right" in table "privilege" (only active members counted)';
     1.8 +COMMENT ON COLUMN "unit"."member_count"       IS 'Count of members as determined by column "voting_right" in view "verified_privilege" (only active members counted)';
     1.9  COMMENT ON COLUMN "unit"."location"           IS 'Geographic location on earth as GeoJSON object indicating valid coordinates for initiatives of issues with this policy';
    1.10  
    1.11  
    1.12 +CREATE TABLE "cross_unit_verification" (
    1.13 +        PRIMARY KEY ("unit_id", "trusted_unit_id"),
    1.14 +        "unit_id"               INT4            REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    1.15 +        "trusted_unit_id"       INT4            REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
    1.16 +
    1.17 +COMMENT ON TABLE "cross_unit_verification" IS 'Enables member verifications in a unit to count for other units as well (non-transitively)';
    1.18 +
    1.19 +COMMENT ON COLUMN "cross_unit_verification"."unit_id"         IS 'Unit for which verification in "trusted_unit_id" is also taken into account';
    1.20 +COMMENT ON COLUMN "cross_unit_verification"."trusted_unit_id" IS 'Unit where verification exists';
    1.21 +
    1.22 +
    1.23  CREATE TABLE "subscription" (
    1.24          PRIMARY KEY ("member_id", "unit_id"),
    1.25          "member_id"             INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    1.26 @@ -1059,6 +1070,18 @@
    1.27  COMMENT ON COLUMN "temporary_suggestion_counts"."id"  IS 'References "suggestion" ("id") but has no referential integrity trigger associated, due to performance/locking issues';
    1.28  
    1.29  
    1.30 +CREATE TABLE "verification" (
    1.31 +        "unit_id"               INT4            REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    1.32 +        "member_id"             INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    1.33 +        "created"               TIMESTAMPTZ     NOT NULL DEFAULT now(),
    1.34 +        --"expiry"                TIMESTAMPTZ,  -- TODO
    1.35 +        "comment"               TEXT );
    1.36 +CREATE INDEX "verification_unit_id_idx" ON "verification" ("unit_id");
    1.37 +CREATE INDEX "verification_member_id_idx" ON "verification" ("member_id");
    1.38 +
    1.39 +COMMENT ON TABLE "verification" IS 'Member verification status';
    1.40 +
    1.41 +
    1.42  CREATE TABLE "privilege" (
    1.43          PRIMARY KEY ("unit_id", "member_id"),
    1.44          "unit_id"               INT4            REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    1.45 @@ -3313,6 +3336,20 @@
    1.46  COMMENT ON VIEW "issue_for_admission" IS 'Contains up to 1 issue per area eligible to pass from ''admission'' to ''discussion'' state; needs to be recalculated after admitting the issue in this view';
    1.47  
    1.48  
    1.49 +CREATE VIEW "verified_privilege" AS
    1.50 +  SELECT DISTINCT "privilege".* FROM "privilege"
    1.51 +  LEFT JOIN "cross_unit_verification" AS "cross"
    1.52 +    ON "privilege"."unit_id" = "cross"."unit_id"
    1.53 +  LEFT JOIN "verification"
    1.54 +    ON (
    1.55 +      "privilege"."unit_id" = "verification"."unit_id" OR
    1.56 +      "cross"."trusted_unit_id" = "verification"."unit_id"
    1.57 +    )
    1.58 +    AND "privilege"."member_id" = "verification"."member_id";
    1.59 +
    1.60 +COMMENT ON VIEW "verified_privilege" IS 'View on privilege table containing only entries where verification of member in unit has been done';
    1.61 +
    1.62 +
    1.63  CREATE VIEW "unit_delegation" AS
    1.64    SELECT
    1.65      "unit"."id" AS "unit_id",
    1.66 @@ -3325,10 +3362,10 @@
    1.67      ON "delegation"."unit_id" = "unit"."id"
    1.68    JOIN "member"
    1.69      ON "delegation"."truster_id" = "member"."id"
    1.70 -  JOIN "privilege"
    1.71 -    ON "delegation"."unit_id" = "privilege"."unit_id"
    1.72 -    AND "delegation"."truster_id" = "privilege"."member_id"
    1.73 -  WHERE "member"."active" AND "privilege"."voting_right";
    1.74 +  JOIN "verified_privilege"
    1.75 +    ON "delegation"."unit_id" = "verified_privilege"."unit_id"
    1.76 +    AND "delegation"."truster_id" = "verified_privilege"."member_id"
    1.77 +  WHERE "member"."active" AND "verified_privilege"."voting_right";
    1.78  
    1.79  COMMENT ON VIEW "unit_delegation" IS 'Unit delegations where trusters are active and have voting right';
    1.80  
    1.81 @@ -3346,10 +3383,10 @@
    1.82      OR "delegation"."area_id" = "area"."id"
    1.83    JOIN "member"
    1.84      ON "delegation"."truster_id" = "member"."id"
    1.85 -  JOIN "privilege"
    1.86 -    ON "area"."unit_id" = "privilege"."unit_id"
    1.87 -    AND "delegation"."truster_id" = "privilege"."member_id"
    1.88 -  WHERE "member"."active" AND "privilege"."voting_right"
    1.89 +  JOIN "verified_privilege"
    1.90 +    ON "area"."unit_id" = "verified_privilege"."unit_id"
    1.91 +    AND "delegation"."truster_id" = "verified_privilege"."member_id"
    1.92 +  WHERE "member"."active" AND "verified_privilege"."voting_right"
    1.93    ORDER BY
    1.94      "area"."id",
    1.95      "delegation"."truster_id",
    1.96 @@ -3374,10 +3411,10 @@
    1.97      OR "delegation"."issue_id" = "issue"."id"
    1.98    JOIN "member"
    1.99      ON "delegation"."truster_id" = "member"."id"
   1.100 -  JOIN "privilege"
   1.101 -    ON "area"."unit_id" = "privilege"."unit_id"
   1.102 -    AND "delegation"."truster_id" = "privilege"."member_id"
   1.103 -  WHERE "member"."active" AND "privilege"."voting_right"
   1.104 +  JOIN "verified_privilege"
   1.105 +    ON "area"."unit_id" = "verified_privilege"."unit_id"
   1.106 +    AND "delegation"."truster_id" = "verified_privilege"."member_id"
   1.107 +  WHERE "member"."active" AND "verified_privilege"."voting_right"
   1.108    ORDER BY
   1.109      "issue"."id",
   1.110      "delegation"."truster_id",
   1.111 @@ -3396,10 +3433,10 @@
   1.112    SELECT
   1.113      "unit"."id"   AS "unit_id",
   1.114      "member"."id" AS "member_id"
   1.115 -  FROM "privilege"
   1.116 -  JOIN "unit"   ON "unit_id"     = "privilege"."unit_id"
   1.117 -  JOIN "member" ON "member"."id" = "privilege"."member_id"
   1.118 -  WHERE "privilege"."voting_right" AND "member"."active";
   1.119 +  FROM "verified_privilege"
   1.120 +  JOIN "unit"   ON "unit_id"     = "verified_privilege"."unit_id"
   1.121 +  JOIN "member" ON "member"."id" = "verified_privilege"."member_id"
   1.122 +  WHERE "verified_privilege"."voting_right" AND "member"."active";
   1.123  
   1.124  COMMENT ON VIEW "unit_member" IS 'Active members with voting right in a unit';
   1.125  
   1.126 @@ -3660,10 +3697,10 @@
   1.127    FROM "member" CROSS JOIN "event"
   1.128    JOIN "issue" ON "issue"."id" = "event"."issue_id"
   1.129    JOIN "area" ON "area"."id" = "issue"."area_id"
   1.130 -  LEFT JOIN "privilege" ON
   1.131 -    "privilege"."member_id" = "member"."id" AND
   1.132 -    "privilege"."unit_id" = "area"."unit_id" AND
   1.133 -    "privilege"."voting_right" = TRUE
   1.134 +  LEFT JOIN "verified_privilege" ON
   1.135 +    "verified_privilege"."member_id" = "member"."id" AND
   1.136 +    "verified_privilege"."unit_id" = "area"."unit_id" AND
   1.137 +    "verified_privilege"."voting_right" = TRUE
   1.138    LEFT JOIN "subscription" ON
   1.139      "subscription"."member_id" = "member"."id" AND
   1.140      "subscription"."unit_id" = "area"."unit_id"
   1.141 @@ -3676,7 +3713,9 @@
   1.142    LEFT JOIN "supporter" ON
   1.143      "supporter"."member_id" = "member"."id" AND
   1.144      "supporter"."initiative_id" = "event"."initiative_id"
   1.145 -  WHERE ("privilege"."member_id" NOTNULL OR "subscription"."member_id" NOTNULL)
   1.146 +  WHERE (
   1.147 +    "verified_privilege"."member_id" NOTNULL OR
   1.148 +    "subscription"."member_id" NOTNULL )
   1.149    AND ("ignored_area"."member_id" ISNULL OR "interest"."member_id" NOTNULL)
   1.150    AND (
   1.151      "event"."event" = 'issue_state_changed'::"event_type" OR
   1.152 @@ -3782,10 +3821,10 @@
   1.153              LEFT JOIN "supporter" AS "self_support" ON
   1.154                "self_support"."initiative_id" = "initiative"."id" AND
   1.155                "self_support"."member_id" = "recipient_id_p"
   1.156 -            LEFT JOIN "privilege" ON
   1.157 -              "privilege"."member_id" = "recipient_id_p" AND
   1.158 -              "privilege"."unit_id" = "area"."unit_id" AND
   1.159 -              "privilege"."voting_right" = TRUE
   1.160 +            LEFT JOIN "verified_privilege" ON
   1.161 +              "verified_privilege"."member_id" = "recipient_id_p" AND
   1.162 +              "verified_privilege"."unit_id" = "area"."unit_id" AND
   1.163 +              "verified_privilege"."voting_right" = TRUE
   1.164              LEFT JOIN "subscription" ON
   1.165                "subscription"."member_id" = "recipient_id_p" AND
   1.166                "subscription"."unit_id" = "area"."unit_id"
   1.167 @@ -3799,7 +3838,7 @@
   1.168              AND "self_support"."member_id" ISNULL
   1.169              AND NOT "initiative_id_ary" @> ARRAY["initiative"."id"]
   1.170              AND (
   1.171 -              "privilege"."member_id" NOTNULL OR
   1.172 +              "verified_privilege"."member_id" NOTNULL OR
   1.173                "subscription"."member_id" NOTNULL )
   1.174              AND "ignored_initiative"."member_id" ISNULL
   1.175              AND NOT EXISTS (
   1.176 @@ -4025,10 +4064,10 @@
   1.177      "newsletter"."id" AS "newsletter_id",
   1.178      "newsletter"."published"
   1.179    FROM "newsletter" CROSS JOIN "member_eligible_to_be_notified" AS "member"
   1.180 -  LEFT JOIN "privilege" ON
   1.181 -    "privilege"."member_id" = "member"."id" AND
   1.182 -    "privilege"."unit_id" = "newsletter"."unit_id" AND
   1.183 -    "privilege"."voting_right" = TRUE
   1.184 +  LEFT JOIN "verified_privilege" ON
   1.185 +    "verified_privilege"."member_id" = "member"."id" AND
   1.186 +    "verified_privilege"."unit_id" = "newsletter"."unit_id" AND
   1.187 +    "verified_privilege"."voting_right" = TRUE
   1.188    LEFT JOIN "subscription" ON
   1.189      "subscription"."member_id" = "member"."id" AND
   1.190      "subscription"."unit_id" = "newsletter"."unit_id"
   1.191 @@ -4039,7 +4078,7 @@
   1.192      "newsletter"."include_all_members" = TRUE )
   1.193    AND (
   1.194      "newsletter"."unit_id" ISNULL OR
   1.195 -    "privilege"."member_id" NOTNULL OR
   1.196 +    "verified_privilege"."member_id" NOTNULL OR
   1.197      "subscription"."member_id" NOTNULL );
   1.198  
   1.199  COMMENT ON VIEW "newsletter_to_send" IS 'List of "newsletter_id"s for each member that are due to be sent out';
   1.200 @@ -4230,11 +4269,11 @@
   1.201          END IF;
   1.202          "output_row"."scope_in" := "output_row"."scope_out";
   1.203          "output_row"."member_valid" := EXISTS (
   1.204 -          SELECT NULL FROM "member" JOIN "privilege"
   1.205 -          ON "privilege"."member_id" = "member"."id"
   1.206 -          AND "privilege"."unit_id" = "unit_id_v"
   1.207 +          SELECT NULL FROM "member" JOIN "verified_privilege"
   1.208 +          ON "verified_privilege"."member_id" = "member"."id"
   1.209 +          AND "verified_privilege"."unit_id" = "unit_id_v"
   1.210            WHERE "id" = "output_row"."member_id"
   1.211 -          AND "member"."active" AND "privilege"."voting_right"
   1.212 +          AND "member"."active" AND "verified_privilege"."voting_right"
   1.213          );
   1.214          "simulate_here_v" := (
   1.215            "simulate_v" AND
   1.216 @@ -4939,11 +4978,11 @@
   1.217            JOIN "area" ON "issue"."area_id" = "area"."id"
   1.218            JOIN "interest" ON "issue"."id" = "interest"."issue_id"
   1.219            JOIN "member" ON "interest"."member_id" = "member"."id"
   1.220 -          JOIN "privilege"
   1.221 -            ON "privilege"."unit_id" = "area"."unit_id"
   1.222 -            AND "privilege"."member_id" = "member"."id"
   1.223 +          JOIN "verified_privilege"
   1.224 +            ON "verified_privilege"."unit_id" = "area"."unit_id"
   1.225 +            AND "verified_privilege"."member_id" = "member"."id"
   1.226            WHERE "issue"."id" = "issue_id_v"
   1.227 -          AND "member"."active" AND "privilege"."voting_right";
   1.228 +          AND "member"."active" AND "verified_privilege"."voting_right";
   1.229          FOR "member_id_v" IN
   1.230            SELECT "member_id" FROM "direct_interest_snapshot"
   1.231            WHERE "snapshot_id" = "snapshot_id_v"
   1.232 @@ -5298,13 +5337,13 @@
   1.233              "direct_voter"."member_id"
   1.234            FROM "direct_voter"
   1.235            JOIN "member" ON "direct_voter"."member_id" = "member"."id"
   1.236 -          LEFT JOIN "privilege"
   1.237 -          ON "privilege"."unit_id" = "unit_id_v"
   1.238 -          AND "privilege"."member_id" = "direct_voter"."member_id"
   1.239 +          LEFT JOIN "verified_privilege"
   1.240 +          ON "verified_privilege"."unit_id" = "unit_id_v"
   1.241 +          AND "verified_privilege"."member_id" = "direct_voter"."member_id"
   1.242            WHERE "direct_voter"."issue_id" = "issue_id_p" AND (
   1.243              "member"."active" = FALSE OR
   1.244 -            "privilege"."voting_right" ISNULL OR
   1.245 -            "privilege"."voting_right" = FALSE
   1.246 +            "verified_privilege"."voting_right" ISNULL OR
   1.247 +            "verified_privilege"."voting_right" = FALSE
   1.248            )
   1.249          ) AS "subquery"
   1.250          WHERE "direct_voter"."issue_id" = "issue_id_p"
     2.1 --- a/update/core-update.v3.2.2-v4.0.0.sql	Sat Sep 16 21:20:38 2017 +0200
     2.2 +++ b/update/core-update.v3.2.2-v4.0.0.sql	Sun Sep 17 01:48:45 2017 +0200
     2.3 @@ -151,6 +151,43 @@
     2.4      "name", "identification");
     2.5   
     2.6  
     2.7 +CREATE TABLE "cross_unit_verification" (
     2.8 +        PRIMARY KEY ("unit_id", "trusted_unit_id"),
     2.9 +        "unit_id"               INT4            REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    2.10 +        "trusted_unit_id"       INT4            REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
    2.11 +
    2.12 +COMMENT ON TABLE "cross_unit_verification" IS 'Enables member verifications in a unit to count for other units as well (non-transitively)';
    2.13 +
    2.14 +COMMENT ON COLUMN "cross_unit_verification"."unit_id"         IS 'Unit for which verification in "trusted_unit_id" is also taken into account';
    2.15 +COMMENT ON COLUMN "cross_unit_verification"."trusted_unit_id" IS 'Unit where verification exists';
    2.16 +
    2.17 +
    2.18 +CREATE TABLE "verification" (
    2.19 +        "unit_id"               INT4            REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    2.20 +        "member_id"             INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    2.21 +        "created"               TIMESTAMPTZ     NOT NULL DEFAULT now(),
    2.22 +        --"expiry"                TIMESTAMPTZ,  -- TODO
    2.23 +        "comment"               TEXT );
    2.24 +CREATE INDEX "verification_unit_id_idx" ON "verification" ("unit_id");
    2.25 +CREATE INDEX "verification_member_id_idx" ON "verification" ("member_id");
    2.26 +
    2.27 +COMMENT ON TABLE "verification" IS 'Member verification status';
    2.28 +
    2.29 +
    2.30 +CREATE VIEW "verified_privilege" AS
    2.31 +  SELECT DISTINCT "privilege".* FROM "privilege"
    2.32 +  LEFT JOIN "cross_unit_verification" AS "cross"
    2.33 +    ON "privilege"."unit_id" = "cross"."unit_id"
    2.34 +  LEFT JOIN "verification"
    2.35 +    ON (
    2.36 +      "privilege"."unit_id" = "verification"."unit_id" OR
    2.37 +      "cross"."trusted_unit_id" = "verification"."unit_id"
    2.38 +    )
    2.39 +    AND "privilege"."member_id" = "verification"."member_id";
    2.40 +
    2.41 +COMMENT ON VIEW "verified_privilege" IS 'View on privilege table containing only entries where verification of member in unit has been done';
    2.42 +
    2.43 +
    2.44  CREATE VIEW "member_eligible_to_be_notified" AS
    2.45    SELECT * FROM "member"
    2.46    WHERE "activated" NOTNULL AND "locked" = FALSE;
    2.47 @@ -214,10 +251,10 @@
    2.48      "newsletter"."id" AS "newsletter_id",
    2.49      "newsletter"."published"
    2.50    FROM "newsletter" CROSS JOIN "member_eligible_to_be_notified" AS "member"
    2.51 -  LEFT JOIN "privilege" ON
    2.52 -    "privilege"."member_id" = "member"."id" AND
    2.53 -    "privilege"."unit_id" = "newsletter"."unit_id" AND
    2.54 -    "privilege"."voting_right" = TRUE
    2.55 +  LEFT JOIN "verified_privilege" ON
    2.56 +    "verified_privilege"."member_id" = "member"."id" AND
    2.57 +    "verified_privilege"."unit_id" = "newsletter"."unit_id" AND
    2.58 +    "verified_privilege"."voting_right" = TRUE
    2.59    LEFT JOIN "subscription" ON
    2.60      "subscription"."member_id" = "member"."id" AND
    2.61      "subscription"."unit_id" = "newsletter"."unit_id"
    2.62 @@ -228,7 +265,7 @@
    2.63      "newsletter"."include_all_members" = TRUE )
    2.64    AND (
    2.65      "newsletter"."unit_id" ISNULL OR
    2.66 -    "privilege"."member_id" NOTNULL OR
    2.67 +    "verified_privilege"."member_id" NOTNULL OR
    2.68      "subscription"."member_id" NOTNULL );
    2.69  
    2.70  COMMENT ON VIEW "newsletter_to_send" IS 'List of "newsletter_id"s for each member that are due to be sent out';
    2.71 @@ -452,7 +489,7 @@
    2.72  
    2.73  CREATE INDEX "unit_location_idx" ON "unit" USING gist ((GeoJSON_to_ecluster("location")));
    2.74  
    2.75 -COMMENT ON COLUMN "unit"."member_count" IS 'Count of members as determined by column "voting_right" in table "privilege" (only active members counted)';
    2.76 +COMMENT ON COLUMN "unit"."member_count" IS 'Count of members as determined by column "voting_right" in view "verified_privilege" (only active members counted)';
    2.77  COMMENT ON COLUMN "unit"."location"     IS 'Geographic location on earth as GeoJSON object indicating valid coordinates for initiatives of issues with this policy';
    2.78   
    2.79  
    2.80 @@ -2057,10 +2094,10 @@
    2.81      OR "delegation"."issue_id" = "issue"."id"
    2.82    JOIN "member"
    2.83      ON "delegation"."truster_id" = "member"."id"
    2.84 -  JOIN "privilege"
    2.85 -    ON "area"."unit_id" = "privilege"."unit_id"
    2.86 -    AND "delegation"."truster_id" = "privilege"."member_id"
    2.87 -  WHERE "member"."active" AND "privilege"."voting_right"
    2.88 +  JOIN "verified_privilege"
    2.89 +    ON "area"."unit_id" = "verified_privilege"."unit_id"
    2.90 +    AND "delegation"."truster_id" = "verified_privilege"."member_id"
    2.91 +  WHERE "member"."active" AND "verified_privilege"."voting_right"
    2.92    ORDER BY
    2.93      "issue"."id",
    2.94      "delegation"."truster_id",
    2.95 @@ -2071,10 +2108,10 @@
    2.96    SELECT
    2.97      "unit"."id"   AS "unit_id",
    2.98      "member"."id" AS "member_id"
    2.99 -  FROM "privilege"
   2.100 -  JOIN "unit"   ON "unit_id"     = "privilege"."unit_id"
   2.101 -  JOIN "member" ON "member"."id" = "privilege"."member_id"
   2.102 -  WHERE "privilege"."voting_right" AND "member"."active";
   2.103 +  FROM "verified_privilege"
   2.104 +  JOIN "unit"   ON "unit_id"     = "verified_privilege"."unit_id"
   2.105 +  JOIN "member" ON "member"."id" = "verified_privilege"."member_id"
   2.106 +  WHERE "verified_privilege"."voting_right" AND "member"."active";
   2.107  
   2.108  COMMENT ON VIEW "unit_member" IS 'Active members with voting right in a unit';
   2.109   
   2.110 @@ -2299,11 +2336,11 @@
   2.111          END IF;
   2.112          "output_row"."scope_in" := "output_row"."scope_out";
   2.113          "output_row"."member_valid" := EXISTS (
   2.114 -          SELECT NULL FROM "member" JOIN "privilege"
   2.115 -          ON "privilege"."member_id" = "member"."id"
   2.116 -          AND "privilege"."unit_id" = "unit_id_v"
   2.117 +          SELECT NULL FROM "member" JOIN "verified_privilege"
   2.118 +          ON "verified_privilege"."member_id" = "member"."id"
   2.119 +          AND "verified_privilege"."unit_id" = "unit_id_v"
   2.120            WHERE "id" = "output_row"."member_id"
   2.121 -          AND "member"."active" AND "privilege"."voting_right"
   2.122 +          AND "member"."active" AND "verified_privilege"."voting_right"
   2.123          );
   2.124          "simulate_here_v" := (
   2.125            "simulate_v" AND
   2.126 @@ -2717,11 +2754,11 @@
   2.127            JOIN "area" ON "issue"."area_id" = "area"."id"
   2.128            JOIN "interest" ON "issue"."id" = "interest"."issue_id"
   2.129            JOIN "member" ON "interest"."member_id" = "member"."id"
   2.130 -          JOIN "privilege"
   2.131 -            ON "privilege"."unit_id" = "area"."unit_id"
   2.132 -            AND "privilege"."member_id" = "member"."id"
   2.133 +          JOIN "verified_privilege"
   2.134 +            ON "verified_privilege"."unit_id" = "area"."unit_id"
   2.135 +            AND "verified_privilege"."member_id" = "member"."id"
   2.136            WHERE "issue"."id" = "issue_id_v"
   2.137 -          AND "member"."active" AND "privilege"."voting_right";
   2.138 +          AND "member"."active" AND "verified_privilege"."voting_right";
   2.139          FOR "member_id_v" IN
   2.140            SELECT "member_id" FROM "direct_interest_snapshot"
   2.141            WHERE "snapshot_id" = "snapshot_id_v"
   2.142 @@ -3573,4 +3610,286 @@
   2.143    ("fully_frozen" ISNULL OR "full_freeze_snapshot_id" NOTNULL) );
   2.144  
   2.145  
   2.146 +CREATE OR REPLACE VIEW "unit_delegation" AS
   2.147 +  SELECT
   2.148 +    "unit"."id" AS "unit_id",
   2.149 +    "delegation"."id",
   2.150 +    "delegation"."truster_id",
   2.151 +    "delegation"."trustee_id",
   2.152 +    "delegation"."scope"
   2.153 +  FROM "unit"
   2.154 +  JOIN "delegation"
   2.155 +    ON "delegation"."unit_id" = "unit"."id"
   2.156 +  JOIN "member"
   2.157 +    ON "delegation"."truster_id" = "member"."id"
   2.158 +  JOIN "verified_privilege"
   2.159 +    ON "delegation"."unit_id" = "verified_privilege"."unit_id"
   2.160 +    AND "delegation"."truster_id" = "verified_privilege"."member_id"
   2.161 +  WHERE "member"."active" AND "verified_privilege"."voting_right";
   2.162 +
   2.163 +CREATE OR REPLACE VIEW "area_delegation" AS
   2.164 +  SELECT DISTINCT ON ("area"."id", "delegation"."truster_id")
   2.165 +    "area"."id" AS "area_id",
   2.166 +    "delegation"."id",
   2.167 +    "delegation"."truster_id",
   2.168 +    "delegation"."trustee_id",
   2.169 +    "delegation"."scope"
   2.170 +  FROM "area"
   2.171 +  JOIN "delegation"
   2.172 +    ON "delegation"."unit_id" = "area"."unit_id"
   2.173 +    OR "delegation"."area_id" = "area"."id"
   2.174 +  JOIN "member"
   2.175 +    ON "delegation"."truster_id" = "member"."id"
   2.176 +  JOIN "verified_privilege"
   2.177 +    ON "area"."unit_id" = "verified_privilege"."unit_id"
   2.178 +    AND "delegation"."truster_id" = "verified_privilege"."member_id"
   2.179 +  WHERE "member"."active" AND "verified_privilege"."voting_right"
   2.180 +  ORDER BY
   2.181 +    "area"."id",
   2.182 +    "delegation"."truster_id",
   2.183 +    "delegation"."scope" DESC;
   2.184 +
   2.185 +CREATE OR REPLACE VIEW "event_for_notification" AS
   2.186 +  SELECT
   2.187 +    "member"."id" AS "recipient_id",
   2.188 +    "event".*
   2.189 +  FROM "member" CROSS JOIN "event"
   2.190 +  JOIN "issue" ON "issue"."id" = "event"."issue_id"
   2.191 +  JOIN "area" ON "area"."id" = "issue"."area_id"
   2.192 +  LEFT JOIN "verified_privilege" ON
   2.193 +    "verified_privilege"."member_id" = "member"."id" AND
   2.194 +    "verified_privilege"."unit_id" = "area"."unit_id" AND
   2.195 +    "verified_privilege"."voting_right" = TRUE
   2.196 +  LEFT JOIN "subscription" ON
   2.197 +    "subscription"."member_id" = "member"."id" AND
   2.198 +    "subscription"."unit_id" = "area"."unit_id"
   2.199 +  LEFT JOIN "ignored_area" ON
   2.200 +    "ignored_area"."member_id" = "member"."id" AND
   2.201 +    "ignored_area"."area_id" = "issue"."area_id"
   2.202 +  LEFT JOIN "interest" ON
   2.203 +    "interest"."member_id" = "member"."id" AND
   2.204 +    "interest"."issue_id" = "event"."issue_id"
   2.205 +  LEFT JOIN "supporter" ON
   2.206 +    "supporter"."member_id" = "member"."id" AND
   2.207 +    "supporter"."initiative_id" = "event"."initiative_id"
   2.208 +  WHERE (
   2.209 +    "verified_privilege"."member_id" NOTNULL OR
   2.210 +    "subscription"."member_id" NOTNULL )
   2.211 +  AND ("ignored_area"."member_id" ISNULL OR "interest"."member_id" NOTNULL)
   2.212 +  AND (
   2.213 +    "event"."event" = 'issue_state_changed'::"event_type" OR
   2.214 +    ( "event"."event" = 'initiative_revoked'::"event_type" AND
   2.215 +      "supporter"."member_id" NOTNULL ) );
   2.216 +
   2.217 +CREATE OR REPLACE FUNCTION "featured_initiative"
   2.218 +  ( "recipient_id_p" "member"."id"%TYPE,
   2.219 +    "area_id_p"      "area"."id"%TYPE )
   2.220 +  RETURNS SETOF "initiative"."id"%TYPE
   2.221 +  LANGUAGE 'plpgsql' STABLE AS $$
   2.222 +    DECLARE
   2.223 +      "counter_v"         "member"."notification_counter"%TYPE;
   2.224 +      "sample_size_v"     "member"."notification_sample_size"%TYPE;
   2.225 +      "initiative_id_ary" INT4[];  --"initiative"."id"%TYPE[]
   2.226 +      "match_v"           BOOLEAN;
   2.227 +      "member_id_v"       "member"."id"%TYPE;
   2.228 +      "seed_v"            TEXT;
   2.229 +      "initiative_id_v"   "initiative"."id"%TYPE;
   2.230 +    BEGIN
   2.231 +      SELECT "notification_counter", "notification_sample_size"
   2.232 +        INTO "counter_v", "sample_size_v"
   2.233 +        FROM "member" WHERE "id" = "recipient_id_p";
   2.234 +      IF COALESCE("sample_size_v" <= 0, TRUE) THEN
   2.235 +        RETURN;
   2.236 +      END IF;
   2.237 +      "initiative_id_ary" := '{}';
   2.238 +      LOOP
   2.239 +        "match_v" := FALSE;
   2.240 +        FOR "member_id_v", "seed_v" IN
   2.241 +          SELECT * FROM (
   2.242 +            SELECT DISTINCT
   2.243 +              "supporter"."member_id",
   2.244 +              md5(
   2.245 +                "recipient_id_p" || '-' ||
   2.246 +                "counter_v"      || '-' ||
   2.247 +                "area_id_p"      || '-' ||
   2.248 +                "supporter"."member_id"
   2.249 +              ) AS "seed"
   2.250 +            FROM "supporter"
   2.251 +            JOIN "initiative" ON "initiative"."id" = "supporter"."initiative_id"
   2.252 +            JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
   2.253 +            WHERE "supporter"."member_id" != "recipient_id_p"
   2.254 +            AND "issue"."area_id" = "area_id_p"
   2.255 +            AND "issue"."state" IN ('admission', 'discussion', 'verification')
   2.256 +          ) AS "subquery"
   2.257 +          ORDER BY "seed"
   2.258 +        LOOP
   2.259 +          SELECT "initiative"."id" INTO "initiative_id_v"
   2.260 +            FROM "initiative"
   2.261 +            JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
   2.262 +            JOIN "area" ON "area"."id" = "issue"."area_id"
   2.263 +            JOIN "supporter" ON "supporter"."initiative_id" = "initiative"."id"
   2.264 +            LEFT JOIN "supporter" AS "self_support" ON
   2.265 +              "self_support"."initiative_id" = "initiative"."id" AND
   2.266 +              "self_support"."member_id" = "recipient_id_p"
   2.267 +            LEFT JOIN "verified_privilege" ON
   2.268 +              "verified_privilege"."member_id" = "recipient_id_p" AND
   2.269 +              "verified_privilege"."unit_id" = "area"."unit_id" AND
   2.270 +              "verified_privilege"."voting_right" = TRUE
   2.271 +            LEFT JOIN "subscription" ON
   2.272 +              "subscription"."member_id" = "recipient_id_p" AND
   2.273 +              "subscription"."unit_id" = "area"."unit_id"
   2.274 +            LEFT JOIN "ignored_initiative" ON
   2.275 +              "ignored_initiative"."member_id" = "recipient_id_p" AND
   2.276 +              "ignored_initiative"."initiative_id" = "initiative"."id"
   2.277 +            WHERE "supporter"."member_id" = "member_id_v"
   2.278 +            AND "issue"."area_id" = "area_id_p"
   2.279 +            AND "issue"."state" IN ('admission', 'discussion', 'verification')
   2.280 +            AND "initiative"."revoked" ISNULL
   2.281 +            AND "self_support"."member_id" ISNULL
   2.282 +            AND NOT "initiative_id_ary" @> ARRAY["initiative"."id"]
   2.283 +            AND (
   2.284 +              "verified_privilege"."member_id" NOTNULL OR
   2.285 +              "subscription"."member_id" NOTNULL )
   2.286 +            AND "ignored_initiative"."member_id" ISNULL
   2.287 +            AND NOT EXISTS (
   2.288 +              SELECT NULL FROM "draft"
   2.289 +              JOIN "ignored_member" ON
   2.290 +                "ignored_member"."member_id" = "recipient_id_p" AND
   2.291 +                "ignored_member"."other_member_id" = "draft"."author_id"
   2.292 +              WHERE "draft"."initiative_id" = "initiative"."id"
   2.293 +            )
   2.294 +            ORDER BY md5("seed_v" || '-' || "initiative"."id")
   2.295 +            LIMIT 1;
   2.296 +          IF FOUND THEN
   2.297 +            "match_v" := TRUE;
   2.298 +            RETURN NEXT "initiative_id_v";
   2.299 +            IF array_length("initiative_id_ary", 1) + 1 >= "sample_size_v" THEN
   2.300 +              RETURN;
   2.301 +            END IF;
   2.302 +            "initiative_id_ary" := "initiative_id_ary" || "initiative_id_v";
   2.303 +          END IF;
   2.304 +        END LOOP;
   2.305 +        EXIT WHEN NOT "match_v";
   2.306 +      END LOOP;
   2.307 +      RETURN;
   2.308 +    END;
   2.309 +  $$;
   2.310 +
   2.311 +CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
   2.312 +  RETURNS VOID
   2.313 +  LANGUAGE 'plpgsql' VOLATILE AS $$
   2.314 +    DECLARE
   2.315 +      "area_id_v"   "area"."id"%TYPE;
   2.316 +      "unit_id_v"   "unit"."id"%TYPE;
   2.317 +      "member_id_v" "member"."id"%TYPE;
   2.318 +    BEGIN
   2.319 +      PERFORM "require_transaction_isolation"();
   2.320 +      SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
   2.321 +      SELECT "unit_id" INTO "unit_id_v" FROM "area"  WHERE "id" = "area_id_v";
   2.322 +      -- override protection triggers:
   2.323 +      INSERT INTO "temporary_transaction_data" ("key", "value")
   2.324 +        VALUES ('override_protection_triggers', TRUE::TEXT);
   2.325 +      -- delete timestamp of voting comment:
   2.326 +      UPDATE "direct_voter" SET "comment_changed" = NULL
   2.327 +        WHERE "issue_id" = "issue_id_p";
   2.328 +      -- delete delegating votes (in cases of manual reset of issue state):
   2.329 +      DELETE FROM "delegating_voter"
   2.330 +        WHERE "issue_id" = "issue_id_p";
   2.331 +      -- delete votes from non-privileged voters:
   2.332 +      DELETE FROM "direct_voter"
   2.333 +        USING (
   2.334 +          SELECT
   2.335 +            "direct_voter"."member_id"
   2.336 +          FROM "direct_voter"
   2.337 +          JOIN "member" ON "direct_voter"."member_id" = "member"."id"
   2.338 +          LEFT JOIN "verified_privilege"
   2.339 +          ON "verified_privilege"."unit_id" = "unit_id_v"
   2.340 +          AND "verified_privilege"."member_id" = "direct_voter"."member_id"
   2.341 +          WHERE "direct_voter"."issue_id" = "issue_id_p" AND (
   2.342 +            "member"."active" = FALSE OR
   2.343 +            "verified_privilege"."voting_right" ISNULL OR
   2.344 +            "verified_privilege"."voting_right" = FALSE
   2.345 +          )
   2.346 +        ) AS "subquery"
   2.347 +        WHERE "direct_voter"."issue_id" = "issue_id_p"
   2.348 +        AND "direct_voter"."member_id" = "subquery"."member_id";
   2.349 +      -- consider delegations:
   2.350 +      UPDATE "direct_voter" SET "weight" = 1
   2.351 +        WHERE "issue_id" = "issue_id_p";
   2.352 +      PERFORM "add_vote_delegations"("issue_id_p");
   2.353 +      -- mark first preferences:
   2.354 +      UPDATE "vote" SET "first_preference" = "subquery"."first_preference"
   2.355 +        FROM (
   2.356 +          SELECT
   2.357 +            "vote"."initiative_id",
   2.358 +            "vote"."member_id",
   2.359 +            CASE WHEN "vote"."grade" > 0 THEN
   2.360 +              CASE WHEN "vote"."grade" = max("agg"."grade") THEN TRUE ELSE FALSE END
   2.361 +            ELSE NULL
   2.362 +            END AS "first_preference"
   2.363 +          FROM "vote"
   2.364 +          JOIN "initiative"  -- NOTE: due to missing index on issue_id
   2.365 +          ON "vote"."issue_id" = "initiative"."issue_id"
   2.366 +          JOIN "vote" AS "agg"
   2.367 +          ON "initiative"."id" = "agg"."initiative_id"
   2.368 +          AND "vote"."member_id" = "agg"."member_id"
   2.369 +          GROUP BY "vote"."initiative_id", "vote"."member_id", "vote"."grade"
   2.370 +        ) AS "subquery"
   2.371 +        WHERE "vote"."issue_id" = "issue_id_p"
   2.372 +        AND "vote"."initiative_id" = "subquery"."initiative_id"
   2.373 +        AND "vote"."member_id" = "subquery"."member_id";
   2.374 +      -- finish overriding protection triggers (avoids garbage):
   2.375 +      DELETE FROM "temporary_transaction_data"
   2.376 +        WHERE "key" = 'override_protection_triggers';
   2.377 +      -- materialize battle_view:
   2.378 +      -- NOTE: "closed" column of issue must be set at this point
   2.379 +      DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
   2.380 +      INSERT INTO "battle" (
   2.381 +        "issue_id",
   2.382 +        "winning_initiative_id", "losing_initiative_id",
   2.383 +        "count"
   2.384 +      ) SELECT
   2.385 +        "issue_id",
   2.386 +        "winning_initiative_id", "losing_initiative_id",
   2.387 +        "count"
   2.388 +        FROM "battle_view" WHERE "issue_id" = "issue_id_p";
   2.389 +      -- set voter count:
   2.390 +      UPDATE "issue" SET
   2.391 +        "voter_count" = (
   2.392 +          SELECT coalesce(sum("weight"), 0)
   2.393 +          FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
   2.394 +        )
   2.395 +        WHERE "id" = "issue_id_p";
   2.396 +      -- copy "positive_votes" and "negative_votes" from "battle" table:
   2.397 +      -- NOTE: "first_preference_votes" is set to a default of 0 at this step
   2.398 +      UPDATE "initiative" SET
   2.399 +        "first_preference_votes" = 0,
   2.400 +        "positive_votes" = "battle_win"."count",
   2.401 +        "negative_votes" = "battle_lose"."count"
   2.402 +        FROM "battle" AS "battle_win", "battle" AS "battle_lose"
   2.403 +        WHERE
   2.404 +          "battle_win"."issue_id" = "issue_id_p" AND
   2.405 +          "battle_win"."winning_initiative_id" = "initiative"."id" AND
   2.406 +          "battle_win"."losing_initiative_id" ISNULL AND
   2.407 +          "battle_lose"."issue_id" = "issue_id_p" AND
   2.408 +          "battle_lose"."losing_initiative_id" = "initiative"."id" AND
   2.409 +          "battle_lose"."winning_initiative_id" ISNULL;
   2.410 +      -- calculate "first_preference_votes":
   2.411 +      -- NOTE: will only set values not equal to zero
   2.412 +      UPDATE "initiative" SET "first_preference_votes" = "subquery"."sum"
   2.413 +        FROM (
   2.414 +          SELECT "vote"."initiative_id", sum("direct_voter"."weight")
   2.415 +          FROM "vote" JOIN "direct_voter"
   2.416 +          ON "vote"."issue_id" = "direct_voter"."issue_id"
   2.417 +          AND "vote"."member_id" = "direct_voter"."member_id"
   2.418 +          WHERE "vote"."first_preference"
   2.419 +          GROUP BY "vote"."initiative_id"
   2.420 +        ) AS "subquery"
   2.421 +        WHERE "initiative"."issue_id" = "issue_id_p"
   2.422 +        AND "initiative"."admitted"
   2.423 +        AND "initiative"."id" = "subquery"."initiative_id";
   2.424 +    END;
   2.425 +  $$;
   2.426 +
   2.427 +
   2.428  COMMIT;

Impressum / About Us