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"
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;

Impressum / About Us