liquid_feedback_core

changeset 469:c1e283fd6483

New view "new_issue_for_notification" (helper view for "issue_for_notification")
author jbe
date Sat Mar 26 23:44:56 2016 +0100 (2016-03-26)
parents c39ff9540f4d
children a96c920cd6a5
files core.sql
line diff
     1.1 --- a/core.sql	Sat Mar 26 22:24:23 2016 +0100
     1.2 +++ b/core.sql	Sat Mar 26 23:44:56 2016 +0100
     1.3 @@ -1276,17 +1276,17 @@
     1.4  COMMENT ON INDEX "notification_sent_singleton_idx" IS 'This index ensures that "notification_sent" only contains one row maximum.';
     1.5  
     1.6  
     1.7 -CREATE TABLE "advertisement" (  -- TODO: two tables: one for initiatives, one for suggestions
     1.8 +CREATE TABLE "advertised_initiative" (  -- TODO: two tables: one for initiatives, one for suggestions
     1.9          PRIMARY KEY ("time_serial", "initiative_id", "member_id"),
    1.10          "time_serial"           SERIAL8,
    1.11          "initiative_id"         INT4            NOT NULL REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    1.12          "member_id"             INT4            NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
    1.13 -CREATE INDEX "advertisement_initiative_id_idx" ON "advertisement" ("initiative_id");
    1.14 -CREATE INDEX "advertisement_member_id_idx" ON "advertisement" ("member_id");
    1.15 -
    1.16 -COMMENT ON TABLE "advertisement" IS 'Stores which initiatives have been advertised to a member in a mail digest';
    1.17 -
    1.18 -COMMENT ON COLUMN "advertisement"."time_serial" IS 'An increasing integer that may be used to determine which issue or initiative was advertised the longest ago (may be equal or consecutive for those initiatives that are sent in the same notification)';
    1.19 +CREATE INDEX "advertised_initiative_initiative_id_idx" ON "advertised_initiative" ("initiative_id");
    1.20 +CREATE INDEX "advertised_initiative_member_id_idx" ON "advertised_initiative" ("member_id");
    1.21 +
    1.22 +COMMENT ON TABLE "advertised_initiative" IS 'Stores which initiatives have been advertised to a member in a mail digest';
    1.23 +
    1.24 +COMMENT ON COLUMN "advertised_initiative"."time_serial" IS 'An increasing integer that may be used to determine which issue or initiative was advertised the longest ago (may be equal or consecutive for those initiatives that are sent in the same notification)';
    1.25  
    1.26  
    1.27  
    1.28 @@ -2393,7 +2393,7 @@
    1.29  COMMENT ON VIEW "event_seen_by_member" IS 'Events as seen by a member, depending on its memberships, interests and support, but ignoring members "notify_level"';
    1.30  
    1.31  
    1.32 -CREATE VIEW "issue_for_notification" AS
    1.33 +CREATE VIEW "relevant_issue_for_notification" AS
    1.34    SELECT
    1.35      "member"."id" AS "member_id",
    1.36      "issue"."id" AS "issue_id"
    1.37 @@ -2417,7 +2417,67 @@
    1.38      ( ( "issue"."state" IN ('admission', 'discussion', 'verification') AND
    1.39          "interest"."member_id" NOTNULL ) OR
    1.40        ( "issue"."state" IN ('discussion', 'verification') AND
    1.41 -        "ignored_area"."member_id" ISNULL ) );  -- TODO: add certain issues in admission phase
    1.42 +        "ignored_area"."member_id" ISNULL ) );
    1.43 +
    1.44 +COMMENT ON VIEW "relevant_issue_for_notification" IS 'Helper view for "issue_for_notification" containing issues which are relevant to the member';
    1.45 +
    1.46 +
    1.47 +CREATE VIEW "new_issue_for_notification" AS
    1.48 +  SELECT DISTINCT ON ("member_id", "area_id")
    1.49 +    "member"."id"     AS "member_id",
    1.50 +    "issue"."area_id" AS "area_id",
    1.51 +    "issue"."id"      AS "issue_id"
    1.52 +  FROM "member" CROSS JOIN "issue"
    1.53 +  JOIN "area" ON "area"."id" = "issue"."area_id"
    1.54 +  LEFT JOIN "privilege"
    1.55 +    ON "privilege"."member_id" = "member"."id"
    1.56 +    AND "privilege"."unit_id" = "area"."unit_id"
    1.57 +  LEFT JOIN "subscription"
    1.58 +    ON "subscription"."member_id" = "member"."id"
    1.59 +    AND "subscription"."unit_id" = "area"."unit_id"
    1.60 +  LEFT JOIN "interest"
    1.61 +    ON "interest"."member_id" = "member"."id"
    1.62 +    AND "interest"."issue_id" = "issue"."id"
    1.63 +  LEFT JOIN "ignored_area"
    1.64 +    ON "ignored_area"."member_id" = "member"."id"
    1.65 +    AND "ignored_area"."area_id" = "issue"."area_id"
    1.66 +  LEFT JOIN
    1.67 +    ( "advertised_initiative" JOIN "initiative"
    1.68 +      ON "advertised_initiative"."initiative_id" = "initiative"."id" )
    1.69 +    ON "advertised_initiative"."member_id" = "member"."id"
    1.70 +    AND "initiative"."issue_id" = "issue"."id"
    1.71 +  JOIN "issue_order_in_admission_state"
    1.72 +    ON "issue_order_in_admission_state"."id" = "issue"."id"
    1.73 +  WHERE
    1.74 +    ( "privilege"."initiative_right" OR "privilege"."voting_right" OR
    1.75 +      "subscription"."member_id" NOTNULL ) AND
    1.76 +    "issue"."state" IN ('admission') AND
    1.77 +    "interest"."member_id" ISNULL AND
    1.78 +    "advertised_initiative"."member_id" ISNULL
    1.79 +  ORDER BY
    1.80 +    "member_id",
    1.81 +    "area_id",
    1.82 +    "issue_order_in_admission_state"."order_in_area";
    1.83 +
    1.84 +COMMENT ON VIEW "new_issue_for_notification" IS 'Helper view for "issue_for_notification" containing one not-yet-advertised issue per "area" which is in ''admission'' phase and has the best proportional ranking';
    1.85 +
    1.86 +
    1.87 +CREATE VIEW "new_updated_issue_for_notification" AS
    1.88 +  SELECT
    1.89 +    "member"."id" AS "member_id",
    1.90 +    "issue"."id" AS "issue_id"
    1.91 +  FROM "member" CROSS JOIN "issue"
    1.92 +  WHERE FALSE;  -- TODO
    1.93 +
    1.94 +COMMENT ON VIEW "new_updated_issue_for_notification" IS 'TODO';
    1.95 +
    1.96 +
    1.97 +CREATE VIEW "issue_for_notification" AS
    1.98 +  SELECT "member_id", "issue_id" FROM "relevant_issue_for_notification"
    1.99 +  UNION
   1.100 +  SELECT "member_id", "issue_id" FROM "new_issue_for_notification"
   1.101 +  UNION
   1.102 +  SELECT "member_id", "issue_id" FROM "new_updated_issue_for_notification";
   1.103  
   1.104  COMMENT ON VIEW "issue_for_notification" IS 'Issues that are considered in notifications sent to the member';
   1.105  

Impressum / About Us