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;