liquid_feedback_core
changeset 113:76ffbafb23b5
Work on event and notification system; Added more "issue_state"s; Replaced "ignored_issue" table; Removal of sessions in "delete_member" function
- "state" column of table "event" is now always filled
- splitted revocation state into 3 new "issue_state"s:
- 'canceled_revoked_before_accepted'
- 'canceled_after_revocation_during_discussion'
- 'canceled_after_revocation_during_verification'
- Added columns "notify_level" and "notify_event_id" to "member" table
- Replaced view "ignored_issue" by three new views:
- TABLE "ignored_member"
- TABLE "ignored_initiative"
- TABLE "non_voter"
- Function "delete_member" now removes "session"s
- Added member specific views on events:
- VIEW "event_seen_by_member"
- VIEW "pending_notification"
- "state" column of table "event" is now always filled
- splitted revocation state into 3 new "issue_state"s:
- 'canceled_revoked_before_accepted'
- 'canceled_after_revocation_during_discussion'
- 'canceled_after_revocation_during_verification'
- Added columns "notify_level" and "notify_event_id" to "member" table
- Replaced view "ignored_issue" by three new views:
- TABLE "ignored_member"
- TABLE "ignored_initiative"
- TABLE "non_voter"
- Function "delete_member" now removes "session"s
- Added member specific views on events:
- VIEW "event_seen_by_member"
- VIEW "pending_notification"
author | jbe |
---|---|
date | Sat Mar 05 22:05:13 2011 +0100 (2011-03-05) |
parents | 1b1e266df99b |
children | 2abc6bc59f06 |
files | core.sql |
line diff
1.1 --- a/core.sql Fri Mar 04 17:15:33 2011 +0100 1.2 +++ b/core.sql Sat Mar 05 22:05:13 2011 +0100 1.3 @@ -7,7 +7,7 @@ 1.4 BEGIN; 1.5 1.6 CREATE VIEW "liquid_feedback_version" AS 1.7 - SELECT * FROM (VALUES ('1.4.0', 1, 4, 0)) 1.8 + SELECT * FROM (VALUES ('1.4.0_rc1', 1, 4, 0)) 1.9 AS "subquery"("string", "major", "minor", "revision"); 1.10 1.11 1.12 @@ -75,6 +75,12 @@ 1.13 COMMENT ON COLUMN "contingent"."initiative_limit" IS 'Number of new initiatives to be opened by each member within a given time frame'; 1.14 1.15 1.16 +CREATE TYPE "notify_level" AS ENUM 1.17 + ('none', 'voting', 'verification', 'discussion', 'all'); 1.18 + 1.19 +COMMENT ON TYPE "notify_level" IS 'Level of notification: ''none'' = no notifications, ''voting'' = notifications about finished issues and issues in voting, ''verification'' = notifications about finished issues, issues in voting and verification phase, ''discussion'' = notifications about everything except issues in admission phase, ''all'' = notifications about everything'; 1.20 + 1.21 + 1.22 CREATE TABLE "member" ( 1.23 "id" SERIAL4 PRIMARY KEY, 1.24 "created" TIMESTAMPTZ NOT NULL DEFAULT now(), 1.25 @@ -90,6 +96,8 @@ 1.26 "notify_email_secret" TEXT UNIQUE, 1.27 "notify_email_secret_expiry" TIMESTAMPTZ, 1.28 "notify_email_lock_expiry" TIMESTAMPTZ, 1.29 + "notify_level" "notify_level" NOT NULL DEFAULT 'none', 1.30 + "notify_event_id" INT8, 1.31 "password_reset_secret" TEXT UNIQUE, 1.32 "password_reset_secret_expiry" TIMESTAMPTZ, 1.33 "name" TEXT NOT NULL UNIQUE, 1.34 @@ -132,6 +140,8 @@ 1.35 COMMENT ON COLUMN "member"."notify_email_secret" IS 'Secret sent to the address in "notify_email_unconformed"'; 1.36 COMMENT ON COLUMN "member"."notify_email_secret_expiry" IS 'Expiry date/time for "notify_email_secret"'; 1.37 COMMENT ON COLUMN "member"."notify_email_lock_expiry" IS 'Date/time until no further email confirmation mails may be sent (abuse protection)'; 1.38 +COMMENT ON COLUMN "member"."notify_level" IS 'Selects which event notifications are to be sent to the "notify_email" mail address'; 1.39 +COMMENT ON COLUMN "member"."notify_event_id" IS 'Latest "id" of an "event" the member was notified about'; 1.40 COMMENT ON COLUMN "member"."name" IS 'Distinct name of the member'; 1.41 COMMENT ON COLUMN "member"."identification" IS 'Optional identification number or code of the member'; 1.42 COMMENT ON COLUMN "member"."organizational_unit" IS 'Branch or division of the organization the member belongs to'; 1.43 @@ -247,6 +257,7 @@ 1.44 "public" BOOLEAN NOT NULL DEFAULT FALSE, 1.45 CONSTRAINT "cant_save_yourself_as_contact" 1.46 CHECK ("member_id" != "other_member_id") ); 1.47 +CREATE INDEX "contact_other_member_id_idx" ON "contact" ("other_member_id"); 1.48 1.49 COMMENT ON TABLE "contact" IS 'Contact lists'; 1.50 1.51 @@ -255,6 +266,18 @@ 1.52 COMMENT ON COLUMN "contact"."public" IS 'TRUE = display contact publically'; 1.53 1.54 1.55 +CREATE TABLE "ignored_member" ( 1.56 + PRIMARY KEY ("member_id", "other_member_id"), 1.57 + "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.58 + "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE ); 1.59 +CREATE INDEX "ignored_member_other_member_id_idx" ON "ignored_member" ("other_member_id"); 1.60 + 1.61 +COMMENT ON TABLE "ignored_member" IS 'Possibility to filter other members'; 1.62 + 1.63 +COMMENT ON COLUMN "ignored_member"."member_id" IS 'Member ignoring someone'; 1.64 +COMMENT ON COLUMN "ignored_member"."other_member_id" IS 'Member being ignored'; 1.65 + 1.66 + 1.67 CREATE TABLE "session" ( 1.68 "ident" TEXT PRIMARY KEY, 1.69 "additional_secret" TEXT, 1.70 @@ -388,8 +411,12 @@ 1.71 1.72 CREATE TYPE "issue_state" AS ENUM ( 1.73 'admission', 'discussion', 'verification', 'voting', 1.74 - 'canceled_all_initiatives_revoked', 'canceled_issue_not_accepted', 1.75 - 'calculation', 'canceled_no_initiative_admitted', 1.76 + 'canceled_revoked_before_accepted', 1.77 + 'canceled_issue_not_accepted', 1.78 + 'canceled_after_revocation_during_discussion', 1.79 + 'canceled_after_revocation_during_verification', 1.80 + 'calculation', 1.81 + 'canceled_no_initiative_admitted', 1.82 'finished_without_winner', 'finished_with_winner'); 1.83 1.84 COMMENT ON TYPE "issue_state" IS 'State of issues'; 1.85 @@ -431,12 +458,14 @@ 1.86 ("state" = 'discussion' AND "closed" ISNULL AND "accepted" NOTNULL AND "half_frozen" ISNULL) OR 1.87 ("state" = 'verification' AND "closed" ISNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL) OR 1.88 ("state" = 'voting' AND "closed" ISNULL AND "fully_frozen" NOTNULL) OR 1.89 - ("state" = 'canceled_all_initiatives_revoked' AND "closed" NOTNULL AND "fully_frozen" ISNULL) OR 1.90 - ("state" = 'canceled_issue_not_accepted' AND "closed" NOTNULL AND "accepted" ISNULL) OR 1.91 - ("state" = 'calculation' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = FALSE) OR 1.92 - ("state" = 'canceled_no_initiative_admitted' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE) OR 1.93 - ("state" = 'finished_without_winner' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE) OR 1.94 - ("state" = 'finished_with_winner' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE) 1.95 + ("state" = 'canceled_revoked_before_accepted' AND "closed" NOTNULL AND "accepted" ISNULL) OR 1.96 + ("state" = 'canceled_issue_not_accepted' AND "closed" NOTNULL AND "accepted" ISNULL) OR 1.97 + ("state" = 'canceled_after_revocation_during_discussion' AND "closed" NOTNULL AND "half_frozen" ISNULL) OR 1.98 + ("state" = 'canceled_after_revocation_during_verification' AND "closed" NOTNULL AND "fully_frozen" ISNULL) OR 1.99 + ("state" = 'calculation' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = FALSE) OR 1.100 + ("state" = 'canceled_no_initiative_admitted' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE) OR 1.101 + ("state" = 'finished_without_winner' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE) OR 1.102 + ("state" = 'finished_with_winner' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE) 1.103 )), 1.104 CONSTRAINT "state_change_order" CHECK ( 1.105 "created" <= "accepted" AND 1.106 @@ -560,6 +589,15 @@ 1.107 COMMENT ON TABLE "battle" IS 'Number of members preferring one initiative to another; Filled by "battle_view" when closing an issue'; 1.108 1.109 1.110 +CREATE TABLE "ignored_initiative" ( 1.111 + PRIMARY KEY ("initiative_id", "member_id"), 1.112 + "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.113 + "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE ); 1.114 +CREATE INDEX "ignored_initiative_member_id_idx" ON "ignored_initiative" ("member_id"); 1.115 + 1.116 +COMMENT ON TABLE "ignored_initiative" IS 'Possibility to filter initiatives'; 1.117 + 1.118 + 1.119 CREATE TABLE "initiative_setting" ( 1.120 PRIMARY KEY ("member_id", "key", "initiative_id"), 1.121 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.122 @@ -677,31 +715,16 @@ 1.123 COMMENT ON COLUMN "privilege"."voting_right" IS 'Right to discuss and vote'; 1.124 1.125 1.126 -CREATE TYPE "notify_level" AS ENUM ('never', 'supported', 'interested', 'always'); 1.127 - 1.128 -COMMENT ON TYPE "notify_level" IS 'Type used to indicate when a member wants to get certain notifications'; 1.129 - 1.130 - 1.131 CREATE TABLE "membership" ( 1.132 PRIMARY KEY ("area_id", "member_id"), 1.133 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.134 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.135 - "autoreject" BOOLEAN NOT NULL DEFAULT FALSE, 1.136 - "notify_issue" "notify_level" NOT NULL DEFAULT 'always' CHECK ("notify_issue" IN ('never', 'always')), 1.137 - "notify_state" "notify_level" NOT NULL DEFAULT 'interested' CHECK ("notify_state" IN ('never', 'interested', 'always')), 1.138 - "notify_initiative" "notify_level" NOT NULL DEFAULT 'interested' CHECK ("notify_initiative" IN ('never', 'interested', 'always')), 1.139 - "notify_draft" "notify_level" NOT NULL DEFAULT 'supported', 1.140 - "notify_suggestion" "notify_level" NOT NULL DEFAULT 'supported'); 1.141 + "autoreject" BOOLEAN NOT NULL DEFAULT FALSE ); 1.142 CREATE INDEX "membership_member_id_idx" ON "membership" ("member_id"); 1.143 1.144 COMMENT ON TABLE "membership" IS 'Interest of members in topic areas'; 1.145 1.146 COMMENT ON COLUMN "membership"."autoreject" IS 'TRUE = member votes against all initiatives, if he is neither direct_ or delegating_voter; Entries in the "interest" table can override this setting.'; 1.147 -COMMENT ON COLUMN "membership"."notify_issue" IS 'Selects when member gets notifications about a new issue (first initiative)'; 1.148 -COMMENT ON COLUMN "membership"."notify_state" IS 'Selects when member gets notifications about issue state changes'; 1.149 -COMMENT ON COLUMN "membership"."notify_initiative" IS 'Selects when member gets notifications about new initiatives'; 1.150 -COMMENT ON COLUMN "membership"."notify_draft" IS 'Selects when member gets notifications about new drafts'; 1.151 -COMMENT ON COLUMN "membership"."notify_suggestion" IS 'Selects when member gets notifications about new suggestions'; 1.152 1.153 1.154 CREATE TABLE "interest" ( 1.155 @@ -718,24 +741,6 @@ 1.156 COMMENT ON COLUMN "interest"."voting_requested" IS 'TRUE = member wants to vote now, FALSE = member wants to vote later, NULL = policy rules should apply'; 1.157 1.158 1.159 -CREATE TABLE "ignored_issue" ( 1.160 - PRIMARY KEY ("issue_id", "member_id"), 1.161 - "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.162 - "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.163 - "new" BOOLEAN NOT NULL DEFAULT FALSE, 1.164 - "accepted" BOOLEAN NOT NULL DEFAULT FALSE, 1.165 - "half_frozen" BOOLEAN NOT NULL DEFAULT FALSE, 1.166 - "fully_frozen" BOOLEAN NOT NULL DEFAULT FALSE ); 1.167 -CREATE INDEX "ignored_issue_member_id_idx" ON "ignored_issue" ("member_id"); 1.168 - 1.169 -COMMENT ON TABLE "ignored_issue" IS 'Table to store member specific options to ignore issues in selected states'; 1.170 - 1.171 -COMMENT ON COLUMN "ignored_issue"."new" IS 'Apply when issue is neither closed nor accepted'; 1.172 -COMMENT ON COLUMN "ignored_issue"."accepted" IS 'Apply when issue is accepted but not (half_)frozen or closed'; 1.173 -COMMENT ON COLUMN "ignored_issue"."half_frozen" IS 'Apply when issue is half_frozen but not fully_frozen or closed'; 1.174 -COMMENT ON COLUMN "ignored_issue"."fully_frozen" IS 'Apply when issue is fully_frozen (in voting) and not closed'; 1.175 - 1.176 - 1.177 CREATE TABLE "initiator" ( 1.178 PRIMARY KEY ("initiative_id", "member_id"), 1.179 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.180 @@ -897,6 +902,15 @@ 1.181 COMMENT ON COLUMN "direct_supporter_snapshot"."satisfied" IS 'Supporter has no "critical_opinion"s'; 1.182 1.183 1.184 +CREATE TABLE "non_voter" ( 1.185 + PRIMARY KEY ("issue_id", "member_id"), 1.186 + "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.187 + "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE ); 1.188 +CREATE INDEX "non_voter_member_id_idx" ON "non_voter" ("member_id"); 1.189 + 1.190 +COMMENT ON TABLE "non_voter" IS 'Members who decided to not vote directly on an issue'; 1.191 + 1.192 + 1.193 CREATE TABLE "direct_voter" ( 1.194 PRIMARY KEY ("issue_id", "member_id"), 1.195 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.196 @@ -1042,6 +1056,7 @@ 1.197 "event" != 'issue_state_changed' OR ( 1.198 "member_id" ISNULL AND 1.199 "issue_id" NOTNULL AND 1.200 + "state" NOTNULL AND 1.201 "initiative_id" ISNULL AND 1.202 "draft_id" ISNULL AND 1.203 "suggestion_id" ISNULL )), 1.204 @@ -1054,6 +1069,7 @@ 1.205 ) OR ( 1.206 "member_id" NOTNULL AND 1.207 "issue_id" NOTNULL AND 1.208 + "state" NOTNULL AND 1.209 "initiative_id" NOTNULL AND 1.210 "draft_id" NOTNULL AND 1.211 "suggestion_id" ISNULL )), 1.212 @@ -1061,6 +1077,7 @@ 1.213 "event" != 'suggestion_created' OR ( 1.214 "member_id" NOTNULL AND 1.215 "issue_id" NOTNULL AND 1.216 + "state" NOTNULL AND 1.217 "initiative_id" NOTNULL AND 1.218 "draft_id" ISNULL AND 1.219 "suggestion_id" NOTNULL )) ); 1.220 @@ -1122,10 +1139,13 @@ 1.221 LANGUAGE 'plpgsql' VOLATILE AS $$ 1.222 DECLARE 1.223 "initiative_row" "initiative"%ROWTYPE; 1.224 + "issue_row" "issue"%ROWTYPE; 1.225 "event_v" "event_type"; 1.226 BEGIN 1.227 SELECT * INTO "initiative_row" FROM "initiative" 1.228 WHERE "id" = NEW."initiative_id"; 1.229 + SELECT * INTO "issue_row" FROM "issue" 1.230 + WHERE "id" = "initiative_row"."issue_id"; 1.231 IF EXISTS ( 1.232 SELECT NULL FROM "draft" 1.233 WHERE "initiative_id" = NEW."initiative_id" 1.234 @@ -1145,11 +1165,12 @@ 1.235 END IF; 1.236 INSERT INTO "event" ( 1.237 "event", "member_id", 1.238 - "issue_id", "initiative_id", "draft_id" 1.239 + "issue_id", "state", "initiative_id", "draft_id" 1.240 ) VALUES ( 1.241 "event_v", 1.242 NEW."author_id", 1.243 "initiative_row"."issue_id", 1.244 + "issue_row"."state", 1.245 "initiative_row"."id", 1.246 NEW."id" ); 1.247 RETURN NULL; 1.248 @@ -1167,14 +1188,19 @@ 1.249 CREATE FUNCTION "write_event_initiative_revoked_trigger"() 1.250 RETURNS TRIGGER 1.251 LANGUAGE 'plpgsql' VOLATILE AS $$ 1.252 + DECLARE 1.253 + "issue_row" "issue"%ROWTYPE; 1.254 BEGIN 1.255 + SELECT * INTO "issue_row" FROM "issue" 1.256 + WHERE "id" = NEW."issue_id"; 1.257 IF OLD."revoked" ISNULL AND NEW."revoked" NOTNULL THEN 1.258 INSERT INTO "event" ( 1.259 - "event", "member_id", "issue_id", "initiative_id" 1.260 + "event", "member_id", "issue_id", "state", "initiative_id" 1.261 ) VALUES ( 1.262 'initiative_revoked', 1.263 NEW."revoked_by_member_id", 1.264 NEW."issue_id", 1.265 + "issue_row"."state", 1.266 NEW."id" ); 1.267 END IF; 1.268 RETURN NULL; 1.269 @@ -1194,16 +1220,20 @@ 1.270 LANGUAGE 'plpgsql' VOLATILE AS $$ 1.271 DECLARE 1.272 "initiative_row" "initiative"%ROWTYPE; 1.273 + "issue_row" "issue"%ROWTYPE; 1.274 BEGIN 1.275 SELECT * INTO "initiative_row" FROM "initiative" 1.276 WHERE "id" = NEW."initiative_id"; 1.277 + SELECT * INTO "issue_row" FROM "issue" 1.278 + WHERE "id" = "initiative_row"."issue_id"; 1.279 INSERT INTO "event" ( 1.280 "event", "member_id", 1.281 - "issue_id", "initiative_id", "suggestion_id" 1.282 + "issue_id", "state", "initiative_id", "suggestion_id" 1.283 ) VALUES ( 1.284 'suggestion_created', 1.285 NEW."author_id", 1.286 "initiative_row"."issue_id", 1.287 + "issue_row"."state", 1.288 "initiative_row"."id", 1.289 NEW."id" ); 1.290 RETURN NULL; 1.291 @@ -1937,6 +1967,124 @@ 1.292 COMMENT ON VIEW "member_contingent_left" IS 'Amount of text entries or initiatives which can be posted now instantly by a member. This view should be used by a frontend to determine, if the contingent for posting is exhausted.'; 1.293 1.294 1.295 +CREATE VIEW "event_seen_by_member" AS 1.296 + SELECT 1.297 + "member"."id" AS "seen_by_member_id", 1.298 + CASE WHEN "event"."state" IN ( 1.299 + 'voting', 1.300 + 'finished_without_winner', 1.301 + 'finished_with_winner' 1.302 + ) THEN 1.303 + 'voting'::"notify_level" 1.304 + ELSE 1.305 + CASE WHEN "event"."state" IN ( 1.306 + 'verification', 1.307 + 'canceled_after_revocation_during_verification', 1.308 + 'canceled_no_initiative_admitted' 1.309 + ) THEN 1.310 + 'verification'::"notify_level" 1.311 + ELSE 1.312 + CASE WHEN "event"."state" IN ( 1.313 + 'discussion', 1.314 + 'canceled_after_revocation_during_discussion' 1.315 + ) THEN 1.316 + 'discussion'::"notify_level" 1.317 + ELSE 1.318 + 'all'::"notify_level" 1.319 + END 1.320 + END 1.321 + END AS "notify_level", 1.322 + "event".* 1.323 + FROM "member" CROSS JOIN "event" 1.324 + LEFT JOIN "issue" 1.325 + ON "event"."issue_id" = "issue"."id" 1.326 + LEFT JOIN "membership" 1.327 + ON "member"."id" = "membership"."member_id" 1.328 + AND "issue"."area_id" = "membership"."area_id" 1.329 + LEFT JOIN "interest" 1.330 + ON "member"."id" = "interest"."member_id" 1.331 + AND "event"."issue_id" = "interest"."issue_id" 1.332 + LEFT JOIN "supporter" 1.333 + ON "member"."id" = "supporter"."member_id" 1.334 + AND "event"."initiative_id" = "supporter"."initiative_id" 1.335 + LEFT JOIN "ignored_member" 1.336 + ON "member"."id" = "ignored_member"."member_id" 1.337 + AND "event"."member_id" = "ignored_member"."other_member_id" 1.338 + LEFT JOIN "ignored_initiative" 1.339 + ON "member"."id" = "ignored_initiative"."member_id" 1.340 + AND "event"."initiative_id" = "ignored_initiative"."initiative_id" 1.341 + WHERE ( 1.342 + "supporter"."member_id" NOTNULL OR 1.343 + "interest"."member_id" NOTNULL OR 1.344 + ( "membership"."member_id" NOTNULL AND 1.345 + "event"."event" IN ( 1.346 + 'issue_state_changed', 1.347 + 'initiative_created_in_new_issue', 1.348 + 'initiative_created_in_existing_issue', 1.349 + 'initiative_revoked' ) ) ) 1.350 + AND "ignored_member"."member_id" ISNULL 1.351 + AND "ignored_initiative"."member_id" ISNULL; 1.352 + 1.353 +COMMENT ON VIEW "event_seen_by_member" IS 'Events as seen by a member, depending on its memberships, interests and support'; 1.354 + 1.355 + 1.356 +CREATE VIEW "pending_notification" AS 1.357 + SELECT 1.358 + "member"."id" AS "seen_by_member_id", 1.359 + "event".* 1.360 + FROM "member" CROSS JOIN "event" 1.361 + LEFT JOIN "issue" 1.362 + ON "event"."issue_id" = "issue"."id" 1.363 + LEFT JOIN "membership" 1.364 + ON "member"."id" = "membership"."member_id" 1.365 + AND "issue"."area_id" = "membership"."area_id" 1.366 + LEFT JOIN "interest" 1.367 + ON "member"."id" = "interest"."member_id" 1.368 + AND "event"."issue_id" = "interest"."issue_id" 1.369 + LEFT JOIN "supporter" 1.370 + ON "member"."id" = "supporter"."member_id" 1.371 + AND "event"."initiative_id" = "supporter"."initiative_id" 1.372 + LEFT JOIN "ignored_member" 1.373 + ON "member"."id" = "ignored_member"."member_id" 1.374 + AND "event"."member_id" = "ignored_member"."other_member_id" 1.375 + LEFT JOIN "ignored_initiative" 1.376 + ON "member"."id" = "ignored_initiative"."member_id" 1.377 + AND "event"."initiative_id" = "ignored_initiative"."initiative_id" 1.378 + WHERE ( 1.379 + "member"."notify_event_id" ISNULL OR 1.380 + ( "member"."notify_event_id" NOTNULL AND 1.381 + "member"."notify_event_id" < "event"."id" ) ) 1.382 + AND ( 1.383 + ( "member"."notify_level" >= 'all' ) OR 1.384 + ( "member"."notify_level" >= 'voting' AND 1.385 + "event"."state" IN ( 1.386 + 'voting', 1.387 + 'finished_without_winner', 1.388 + 'finished_with_winner' ) ) OR 1.389 + ( "member"."notify_level" >= 'verification' AND 1.390 + "event"."state" IN ( 1.391 + 'verification', 1.392 + 'canceled_after_revocation_during_verification', 1.393 + 'canceled_no_initiative_admitted' ) ) OR 1.394 + ( "member"."notify_level" >= 'discussion' AND 1.395 + "event"."state" IN ( 1.396 + 'discussion', 1.397 + 'canceled_after_revocation_during_discussion' ) ) ) 1.398 + AND ( 1.399 + "supporter"."member_id" NOTNULL OR 1.400 + "interest"."member_id" NOTNULL OR 1.401 + ( "membership"."member_id" NOTNULL AND 1.402 + "event"."event" IN ( 1.403 + 'issue_state_changed', 1.404 + 'initiative_created_in_new_issue', 1.405 + 'initiative_created_in_existing_issue', 1.406 + 'initiative_revoked' ) ) ) 1.407 + AND "ignored_member"."member_id" ISNULL 1.408 + AND "ignored_initiative"."member_id" ISNULL; 1.409 + 1.410 +COMMENT ON VIEW "pending_notification" IS 'Events to be sent to "notify_email" address of member referred to by "seen_by_member_id"'; 1.411 + 1.412 + 1.413 CREATE TYPE "timeline_event" AS ENUM ( 1.414 'issue_created', 1.415 'issue_canceled', 1.416 @@ -3076,12 +3224,6 @@ 1.417 SELECT * INTO "policy_row" 1.418 FROM "policy" WHERE "id" = "issue_row"."policy_id"; 1.419 PERFORM "set_snapshot_event"("issue_id_p", 'full_freeze'); 1.420 - UPDATE "issue" SET 1.421 - "state" = 'voting', 1.422 - "accepted" = coalesce("accepted", now()), 1.423 - "half_frozen" = coalesce("half_frozen", now()), 1.424 - "fully_frozen" = now() 1.425 - WHERE "id" = "issue_id_p"; 1.426 FOR "initiative_row" IN 1.427 SELECT * FROM "initiative" 1.428 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL 1.429 @@ -3099,11 +3241,29 @@ 1.430 WHERE "id" = "initiative_row"."id"; 1.431 END IF; 1.432 END LOOP; 1.433 - IF NOT EXISTS ( 1.434 + IF EXISTS ( 1.435 SELECT NULL FROM "initiative" 1.436 WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE 1.437 ) THEN 1.438 - PERFORM "close_voting"("issue_id_p"); 1.439 + UPDATE "issue" SET 1.440 + "state" = 'voting', 1.441 + "accepted" = coalesce("accepted", now()), 1.442 + "half_frozen" = coalesce("half_frozen", now()), 1.443 + "fully_frozen" = now() 1.444 + WHERE "id" = "issue_id_p"; 1.445 + ELSE 1.446 + UPDATE "issue" SET 1.447 + "state" = 'canceled_no_initiative_admitted', 1.448 + "accepted" = coalesce("accepted", now()), 1.449 + "half_frozen" = coalesce("half_frozen", now()), 1.450 + "fully_frozen" = now(), 1.451 + "closed" = now() 1.452 + WHERE "id" = "issue_id_p"; 1.453 + -- NOTE: The following DELETE statements have effect only when 1.454 + -- issue state has been manipulated 1.455 + DELETE FROM "direct_voter" WHERE "issue_id" = "issue_id_p"; 1.456 + DELETE FROM "delegating_voter" WHERE "issue_id" = "issue_id_p"; 1.457 + DELETE FROM "battle" WHERE "issue_id" = "issue_id_p"; 1.458 END IF; 1.459 RETURN; 1.460 END; 1.461 @@ -3769,7 +3929,13 @@ 1.462 ) 1.463 THEN 1.464 -- NOTE: "issue_row" used later 1.465 - "issue_row"."state" := 'canceled_all_initiatives_revoked'; 1.466 + IF "issue_row"."accepted" ISNULL THEN 1.467 + "issue_row"."state" := 'canceled_revoked_before_accepted'; 1.468 + ELSIF "issue_row"."half_frozen" ISNULL THEN 1.469 + "issue_row"."state" := 'canceled_after_revocation_during_discussion'; 1.470 + ELSE 1.471 + "issue_row"."state" := 'canceled_after_revocation_during_verification'; 1.472 + END IF; 1.473 "issue_row"."closed" := now(); 1.474 UPDATE "issue" SET 1.475 "state" = "issue_row"."state", 1.476 @@ -3861,7 +4027,7 @@ 1.477 WHERE "issue_id" = "issue_id_p"; 1.478 DELETE FROM "direct_population_snapshot" 1.479 WHERE "issue_id" = "issue_id_p"; 1.480 - DELETE FROM "ignored_issue" 1.481 + DELETE FROM "non_voter" 1.482 WHERE "issue_id" = "issue_id_p"; 1.483 DELETE FROM "delegation" 1.484 WHERE "issue_id" = "issue_id_p"; 1.485 @@ -3919,13 +4085,16 @@ 1.486 DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p"; 1.487 DELETE FROM "member_image" WHERE "member_id" = "member_id_p"; 1.488 DELETE FROM "contact" WHERE "member_id" = "member_id_p"; 1.489 + DELETE FROM "ignored_member" WHERE "member_id" = "member_id_p"; 1.490 + DELETE FROM "session" WHERE "member_id" = "member_id_p"; 1.491 DELETE FROM "area_setting" WHERE "member_id" = "member_id_p"; 1.492 DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p"; 1.493 + DELETE FROM "ignored_initiative" WHERE "member_id" = "member_id_p"; 1.494 DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p"; 1.495 DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p"; 1.496 DELETE FROM "membership" WHERE "member_id" = "member_id_p"; 1.497 - DELETE FROM "ignored_issue" WHERE "member_id" = "member_id_p"; 1.498 DELETE FROM "delegation" WHERE "truster_id" = "member_id_p"; 1.499 + DELETE FROM "non_voter" WHERE "member_id" = "member_id_p"; 1.500 DELETE FROM "direct_voter" USING "issue" 1.501 WHERE "direct_voter"."issue_id" = "issue"."id" 1.502 AND "issue"."closed" ISNULL 1.503 @@ -3973,12 +4142,14 @@ 1.504 DELETE FROM "member_relation_setting"; 1.505 DELETE FROM "member_image"; 1.506 DELETE FROM "contact"; 1.507 + DELETE FROM "ignored_member"; 1.508 DELETE FROM "session"; 1.509 DELETE FROM "area_setting"; 1.510 DELETE FROM "issue_setting"; 1.511 + DELETE FROM "ignored_initiative"; 1.512 DELETE FROM "initiative_setting"; 1.513 DELETE FROM "suggestion_setting"; 1.514 - DELETE FROM "ignored_issue"; 1.515 + DELETE FROM "non_voter"; 1.516 DELETE FROM "direct_voter" USING "issue" 1.517 WHERE "direct_voter"."issue_id" = "issue"."id" 1.518 AND "issue"."closed" ISNULL;