1.1 --- a/core.sql	Sat Mar 26 01:34:36 2016 +0100
     1.2 +++ b/core.sql	Sat Mar 26 02:13:42 2016 +0100
     1.3 @@ -1276,7 +1276,7 @@
     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" (
     1.8 +CREATE TABLE "advertisement" (  -- 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 @@ -2393,6 +2393,25 @@
    1.13  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.14  
    1.15  
    1.16 +CREATE VIEW "issues_for_notification" AS
    1.17 +  SELECT
    1.18 +    "member"."id" AS "member_id",
    1.19 +    "issue"."id" AS "issue_id"
    1.20 +  FROM "member" CROSS JOIN "issue"
    1.21 +  JOIN "area" ON "area"."id" = "issue"."area_id"
    1.22 +  LEFT JOIN "privilege"
    1.23 +    ON "privilege"."member_id" = "member"."id"
    1.24 +    AND "privilege"."unit_id" = "area"."unit_id"
    1.25 +  LEFT JOIN "subscription"
    1.26 +    ON "subscription"."member_id" = "member"."id"
    1.27 +    AND "subscription"."unit_id" = "area"."unit_id"
    1.28 +  WHERE
    1.29 +    ( "privilege"."initiative_right" OR "privilege"."voting_right" OR
    1.30 +      "subscription"."member_id" NOTNULL );  -- TODO: add further conditions
    1.31 +
    1.32 +COMMENT ON VIEW "issues_for_notification" IS 'Issues that are considered in notifications sent to the member';
    1.33 +
    1.34 +
    1.35  
    1.36  ------------------------------------------------------
    1.37  -- Row set returning function for delegation chains --