liquid_feedback_core
changeset 556:3f21631a7f6d
Backed out changeset 5d098bcc631a (member-unit verification and "verified_privilege" view)
| author | jbe |
|---|---|
| date | Sun Sep 17 13:19:37 2017 +0200 (2017-09-17) |
| parents | 5d098bcc631a |
| children | 0fc78541dc15 |
| files | core.sql update/core-update.v3.2.2-v4.0.0.sql |
line diff
1.1 --- a/core.sql Sun Sep 17 01:48:45 2017 +0200 1.2 +++ b/core.sql Sun Sep 17 13:19:37 2017 +0200 1.3 @@ -597,21 +597,10 @@ 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 view "verified_privilege" (only active members counted)'; 1.8 +COMMENT ON COLUMN "unit"."member_count" IS 'Count of members as determined by column "voting_right" in table "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 @@ -1070,18 +1059,6 @@ 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 @@ -3336,20 +3313,6 @@ 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 @@ -3362,10 +3325,10 @@ 1.67 ON "delegation"."unit_id" = "unit"."id" 1.68 JOIN "member" 1.69 ON "delegation"."truster_id" = "member"."id" 1.70 - JOIN "verified_privilege" 1.71 - ON "delegation"."unit_id" = "verified_privilege"."unit_id" 1.72 - AND "delegation"."truster_id" = "verified_privilege"."member_id" 1.73 - WHERE "member"."active" AND "verified_privilege"."voting_right"; 1.74 + JOIN "privilege" 1.75 + ON "delegation"."unit_id" = "privilege"."unit_id" 1.76 + AND "delegation"."truster_id" = "privilege"."member_id" 1.77 + WHERE "member"."active" AND "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 @@ -3383,10 +3346,10 @@ 1.82 OR "delegation"."area_id" = "area"."id" 1.83 JOIN "member" 1.84 ON "delegation"."truster_id" = "member"."id" 1.85 - JOIN "verified_privilege" 1.86 - ON "area"."unit_id" = "verified_privilege"."unit_id" 1.87 - AND "delegation"."truster_id" = "verified_privilege"."member_id" 1.88 - WHERE "member"."active" AND "verified_privilege"."voting_right" 1.89 + JOIN "privilege" 1.90 + ON "area"."unit_id" = "privilege"."unit_id" 1.91 + AND "delegation"."truster_id" = "privilege"."member_id" 1.92 + WHERE "member"."active" AND "privilege"."voting_right" 1.93 ORDER BY 1.94 "area"."id", 1.95 "delegation"."truster_id", 1.96 @@ -3411,10 +3374,10 @@ 1.97 OR "delegation"."issue_id" = "issue"."id" 1.98 JOIN "member" 1.99 ON "delegation"."truster_id" = "member"."id" 1.100 - JOIN "verified_privilege" 1.101 - ON "area"."unit_id" = "verified_privilege"."unit_id" 1.102 - AND "delegation"."truster_id" = "verified_privilege"."member_id" 1.103 - WHERE "member"."active" AND "verified_privilege"."voting_right" 1.104 + JOIN "privilege" 1.105 + ON "area"."unit_id" = "privilege"."unit_id" 1.106 + AND "delegation"."truster_id" = "privilege"."member_id" 1.107 + WHERE "member"."active" AND "privilege"."voting_right" 1.108 ORDER BY 1.109 "issue"."id", 1.110 "delegation"."truster_id", 1.111 @@ -3433,10 +3396,10 @@ 1.112 SELECT 1.113 "unit"."id" AS "unit_id", 1.114 "member"."id" AS "member_id" 1.115 - FROM "verified_privilege" 1.116 - JOIN "unit" ON "unit_id" = "verified_privilege"."unit_id" 1.117 - JOIN "member" ON "member"."id" = "verified_privilege"."member_id" 1.118 - WHERE "verified_privilege"."voting_right" AND "member"."active"; 1.119 + FROM "privilege" 1.120 + JOIN "unit" ON "unit_id" = "privilege"."unit_id" 1.121 + JOIN "member" ON "member"."id" = "privilege"."member_id" 1.122 + WHERE "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 @@ -3697,10 +3660,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 "verified_privilege" ON 1.131 - "verified_privilege"."member_id" = "member"."id" AND 1.132 - "verified_privilege"."unit_id" = "area"."unit_id" AND 1.133 - "verified_privilege"."voting_right" = TRUE 1.134 + LEFT JOIN "privilege" ON 1.135 + "privilege"."member_id" = "member"."id" AND 1.136 + "privilege"."unit_id" = "area"."unit_id" AND 1.137 + "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 @@ -3713,9 +3676,7 @@ 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 ( 1.146 - "verified_privilege"."member_id" NOTNULL OR 1.147 - "subscription"."member_id" NOTNULL ) 1.148 + WHERE ("privilege"."member_id" NOTNULL OR "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 @@ -3821,10 +3782,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 "verified_privilege" ON 1.157 - "verified_privilege"."member_id" = "recipient_id_p" AND 1.158 - "verified_privilege"."unit_id" = "area"."unit_id" AND 1.159 - "verified_privilege"."voting_right" = TRUE 1.160 + LEFT JOIN "privilege" ON 1.161 + "privilege"."member_id" = "recipient_id_p" AND 1.162 + "privilege"."unit_id" = "area"."unit_id" AND 1.163 + "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 @@ -3838,7 +3799,7 @@ 1.168 AND "self_support"."member_id" ISNULL 1.169 AND NOT "initiative_id_ary" @> ARRAY["initiative"."id"] 1.170 AND ( 1.171 - "verified_privilege"."member_id" NOTNULL OR 1.172 + "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 @@ -4064,10 +4025,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 "verified_privilege" ON 1.181 - "verified_privilege"."member_id" = "member"."id" AND 1.182 - "verified_privilege"."unit_id" = "newsletter"."unit_id" AND 1.183 - "verified_privilege"."voting_right" = TRUE 1.184 + LEFT JOIN "privilege" ON 1.185 + "privilege"."member_id" = "member"."id" AND 1.186 + "privilege"."unit_id" = "newsletter"."unit_id" AND 1.187 + "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 @@ -4078,7 +4039,7 @@ 1.192 "newsletter"."include_all_members" = TRUE ) 1.193 AND ( 1.194 "newsletter"."unit_id" ISNULL OR 1.195 - "verified_privilege"."member_id" NOTNULL OR 1.196 + "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 @@ -4269,11 +4230,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 "verified_privilege" 1.205 - ON "verified_privilege"."member_id" = "member"."id" 1.206 - AND "verified_privilege"."unit_id" = "unit_id_v" 1.207 + SELECT NULL FROM "member" JOIN "privilege" 1.208 + ON "privilege"."member_id" = "member"."id" 1.209 + AND "privilege"."unit_id" = "unit_id_v" 1.210 WHERE "id" = "output_row"."member_id" 1.211 - AND "member"."active" AND "verified_privilege"."voting_right" 1.212 + AND "member"."active" AND "privilege"."voting_right" 1.213 ); 1.214 "simulate_here_v" := ( 1.215 "simulate_v" AND 1.216 @@ -4978,11 +4939,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 "verified_privilege" 1.221 - ON "verified_privilege"."unit_id" = "area"."unit_id" 1.222 - AND "verified_privilege"."member_id" = "member"."id" 1.223 + JOIN "privilege" 1.224 + ON "privilege"."unit_id" = "area"."unit_id" 1.225 + AND "privilege"."member_id" = "member"."id" 1.226 WHERE "issue"."id" = "issue_id_v" 1.227 - AND "member"."active" AND "verified_privilege"."voting_right"; 1.228 + AND "member"."active" AND "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 @@ -5337,13 +5298,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 "verified_privilege" 1.237 - ON "verified_privilege"."unit_id" = "unit_id_v" 1.238 - AND "verified_privilege"."member_id" = "direct_voter"."member_id" 1.239 + LEFT JOIN "privilege" 1.240 + ON "privilege"."unit_id" = "unit_id_v" 1.241 + AND "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 - "verified_privilege"."voting_right" ISNULL OR 1.245 - "verified_privilege"."voting_right" = FALSE 1.246 + "privilege"."voting_right" ISNULL OR 1.247 + "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 Sun Sep 17 01:48:45 2017 +0200 2.2 +++ b/update/core-update.v3.2.2-v4.0.0.sql Sun Sep 17 13:19:37 2017 +0200 2.3 @@ -151,43 +151,6 @@ 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 @@ -251,10 +214,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 "verified_privilege" ON 2.52 - "verified_privilege"."member_id" = "member"."id" AND 2.53 - "verified_privilege"."unit_id" = "newsletter"."unit_id" AND 2.54 - "verified_privilege"."voting_right" = TRUE 2.55 + LEFT JOIN "privilege" ON 2.56 + "privilege"."member_id" = "member"."id" AND 2.57 + "privilege"."unit_id" = "newsletter"."unit_id" AND 2.58 + "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 @@ -265,7 +228,7 @@ 2.63 "newsletter"."include_all_members" = TRUE ) 2.64 AND ( 2.65 "newsletter"."unit_id" ISNULL OR 2.66 - "verified_privilege"."member_id" NOTNULL OR 2.67 + "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 @@ -489,7 +452,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 view "verified_privilege" (only active members counted)'; 2.76 +COMMENT ON COLUMN "unit"."member_count" IS 'Count of members as determined by column "voting_right" in table "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 @@ -2094,10 +2057,10 @@ 2.81 OR "delegation"."issue_id" = "issue"."id" 2.82 JOIN "member" 2.83 ON "delegation"."truster_id" = "member"."id" 2.84 - JOIN "verified_privilege" 2.85 - ON "area"."unit_id" = "verified_privilege"."unit_id" 2.86 - AND "delegation"."truster_id" = "verified_privilege"."member_id" 2.87 - WHERE "member"."active" AND "verified_privilege"."voting_right" 2.88 + JOIN "privilege" 2.89 + ON "area"."unit_id" = "privilege"."unit_id" 2.90 + AND "delegation"."truster_id" = "privilege"."member_id" 2.91 + WHERE "member"."active" AND "privilege"."voting_right" 2.92 ORDER BY 2.93 "issue"."id", 2.94 "delegation"."truster_id", 2.95 @@ -2108,10 +2071,10 @@ 2.96 SELECT 2.97 "unit"."id" AS "unit_id", 2.98 "member"."id" AS "member_id" 2.99 - FROM "verified_privilege" 2.100 - JOIN "unit" ON "unit_id" = "verified_privilege"."unit_id" 2.101 - JOIN "member" ON "member"."id" = "verified_privilege"."member_id" 2.102 - WHERE "verified_privilege"."voting_right" AND "member"."active"; 2.103 + FROM "privilege" 2.104 + JOIN "unit" ON "unit_id" = "privilege"."unit_id" 2.105 + JOIN "member" ON "member"."id" = "privilege"."member_id" 2.106 + WHERE "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 @@ -2336,11 +2299,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 "verified_privilege" 2.115 - ON "verified_privilege"."member_id" = "member"."id" 2.116 - AND "verified_privilege"."unit_id" = "unit_id_v" 2.117 + SELECT NULL FROM "member" JOIN "privilege" 2.118 + ON "privilege"."member_id" = "member"."id" 2.119 + AND "privilege"."unit_id" = "unit_id_v" 2.120 WHERE "id" = "output_row"."member_id" 2.121 - AND "member"."active" AND "verified_privilege"."voting_right" 2.122 + AND "member"."active" AND "privilege"."voting_right" 2.123 ); 2.124 "simulate_here_v" := ( 2.125 "simulate_v" AND 2.126 @@ -2754,11 +2717,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 "verified_privilege" 2.131 - ON "verified_privilege"."unit_id" = "area"."unit_id" 2.132 - AND "verified_privilege"."member_id" = "member"."id" 2.133 + JOIN "privilege" 2.134 + ON "privilege"."unit_id" = "area"."unit_id" 2.135 + AND "privilege"."member_id" = "member"."id" 2.136 WHERE "issue"."id" = "issue_id_v" 2.137 - AND "member"."active" AND "verified_privilege"."voting_right"; 2.138 + AND "member"."active" AND "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 @@ -3610,286 +3573,4 @@ 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;