1.1 --- a/core.sql	Sat Feb 25 15:22:34 2012 +0100
     1.2 +++ b/core.sql	Sat Feb 25 16:37:18 2012 +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 ('2.0.3', 2, 0, 3))
     1.8 +  SELECT * FROM (VALUES ('2.0.4', 2, 0, 4))
     1.9    AS "subquery"("string", "major", "minor", "revision");
    1.10  
    1.11  

     2.1 --- /dev/null	Thu Jan 01 00:00:00 1970 +0000
     2.2 +++ b/update/core-update.v2.0.3-v2.0.4.sql	Sat Feb 25 16:37:18 2012 +0100
     2.3 @@ -0,0 +1,115 @@
     2.4 +BEGIN;
     2.5 +
     2.6 +-- update version number:
     2.7 +CREATE OR REPLACE VIEW "liquid_feedback_version" AS
     2.8 +  SELECT * FROM (VALUES ('2.0.4', 2, 0, 4))
     2.9 +  AS "subquery"("string", "major", "minor", "revision");
    2.10 +
    2.11 +-- add table "session":
    2.12 +CREATE TABLE "session" (
    2.13 +        "ident"                 TEXT            PRIMARY KEY,
    2.14 +        "additional_secret"     TEXT,
    2.15 +        "expiry"                TIMESTAMPTZ     NOT NULL DEFAULT now() + '24 hours',
    2.16 +        "member_id"             INT8            REFERENCES "member" ("id") ON DELETE SET NULL,
    2.17 +        "lang"                  TEXT );
    2.18 +CREATE INDEX "session_expiry_idx" ON "session" ("expiry");
    2.19 +COMMENT ON TABLE "session" IS 'Sessions, i.e. for a web-frontend or API layer';
    2.20 +COMMENT ON COLUMN "session"."ident"             IS 'Secret session identifier (i.e. random string)';
    2.21 +COMMENT ON COLUMN "session"."additional_secret" IS 'Additional field to store a secret, which can be used against CSRF attacks';
    2.22 +COMMENT ON COLUMN "session"."member_id"         IS 'Reference to member, who is logged in';
    2.23 +COMMENT ON COLUMN "session"."lang"              IS 'Language code of the selected language';
    2.24 +
    2.25 +-- add column "lang" to table "member":
    2.26 +ALTER TABLE "member" ADD COLUMN "lang" TEXT;
    2.27 +COMMENT ON COLUMN "member"."lang" IS 'Language code of the preferred language of the member';
    2.28 +
    2.29 +-- drop view "pending_notification":
    2.30 +DROP VIEW "pending_notification";
    2.31 +
    2.32 +-- remove column "notify_event_id" of table "member":
    2.33 +ALTER TABLE "member" DROP COLUMN "notify_event_id";
    2.34 +
    2.35 +-- add table "notification_sent":
    2.36 +CREATE TABLE "notification_sent" (
    2.37 +        "event_id"              INT8            NOT NULL );
    2.38 +CREATE UNIQUE INDEX "notification_sent_singleton_idx" ON "notification_sent" ((1));
    2.39 +COMMENT ON TABLE "notification_sent" IS 'This table stores one row with the last event_id, for which notifications have been sent out';
    2.40 +COMMENT ON INDEX "notification_sent_singleton_idx" IS 'This index ensures that "notification_sent" only contains one row maximum.';
    2.41 +
    2.42 +-- add view "selected_event_seen_by_member":
    2.43 +CREATE VIEW "selected_event_seen_by_member" AS
    2.44 +  SELECT
    2.45 +    "member"."id" AS "seen_by_member_id",
    2.46 +    CASE WHEN "event"."state" IN (
    2.47 +      'voting',
    2.48 +      'finished_without_winner',
    2.49 +      'finished_with_winner'
    2.50 +    ) THEN
    2.51 +      'voting'::"notify_level"
    2.52 +    ELSE
    2.53 +      CASE WHEN "event"."state" IN (
    2.54 +        'verification',
    2.55 +        'canceled_after_revocation_during_verification',
    2.56 +        'canceled_no_initiative_admitted'
    2.57 +      ) THEN
    2.58 +        'verification'::"notify_level"
    2.59 +      ELSE
    2.60 +        CASE WHEN "event"."state" IN (
    2.61 +          'discussion',
    2.62 +          'canceled_after_revocation_during_discussion'
    2.63 +        ) THEN
    2.64 +          'discussion'::"notify_level"
    2.65 +        ELSE
    2.66 +          'all'::"notify_level"
    2.67 +        END
    2.68 +      END
    2.69 +    END AS "notify_level",
    2.70 +    "event".*
    2.71 +  FROM "member" CROSS JOIN "event"
    2.72 +  LEFT JOIN "issue"
    2.73 +    ON "event"."issue_id" = "issue"."id"
    2.74 +  LEFT JOIN "membership"
    2.75 +    ON "member"."id" = "membership"."member_id"
    2.76 +    AND "issue"."area_id" = "membership"."area_id"
    2.77 +  LEFT JOIN "interest"
    2.78 +    ON "member"."id" = "interest"."member_id"
    2.79 +    AND "event"."issue_id" = "interest"."issue_id"
    2.80 +  LEFT JOIN "supporter"
    2.81 +    ON "member"."id" = "supporter"."member_id"
    2.82 +    AND "event"."initiative_id" = "supporter"."initiative_id"
    2.83 +  LEFT JOIN "ignored_member"
    2.84 +    ON "member"."id" = "ignored_member"."member_id"
    2.85 +    AND "event"."member_id" = "ignored_member"."other_member_id"
    2.86 +  LEFT JOIN "ignored_initiative"
    2.87 +    ON "member"."id" = "ignored_initiative"."member_id"
    2.88 +    AND "event"."initiative_id" = "ignored_initiative"."initiative_id"
    2.89 +  WHERE (
    2.90 +    ( "member"."notify_level" >= 'all' ) OR
    2.91 +    ( "member"."notify_level" >= 'voting' AND
    2.92 +      "event"."state" IN (
    2.93 +        'voting',
    2.94 +        'finished_without_winner',
    2.95 +        'finished_with_winner' ) ) OR
    2.96 +    ( "member"."notify_level" >= 'verification' AND
    2.97 +      "event"."state" IN (
    2.98 +        'verification',
    2.99 +        'canceled_after_revocation_during_verification',
   2.100 +        'canceled_no_initiative_admitted' ) ) OR
   2.101 +    ( "member"."notify_level" >= 'discussion' AND
   2.102 +      "event"."state" IN (
   2.103 +        'discussion',
   2.104 +        'canceled_after_revocation_during_discussion' ) ) )
   2.105 +  AND (
   2.106 +    "supporter"."member_id" NOTNULL OR
   2.107 +    "interest"."member_id" NOTNULL OR
   2.108 +    ( "membership"."member_id" NOTNULL AND
   2.109 +      "event"."event" IN (
   2.110 +        'issue_state_changed',
   2.111 +        'initiative_created_in_new_issue',
   2.112 +        'initiative_created_in_existing_issue',
   2.113 +        'initiative_revoked' ) ) )
   2.114 +  AND "ignored_member"."member_id" ISNULL
   2.115 +  AND "ignored_initiative"."member_id" ISNULL;
   2.116 +COMMENT ON VIEW "selected_event_seen_by_member" IS 'Events as seen by a member, depending on its memberships, interests, support and members "notify_level"';
   2.117 +
   2.118 +COMMIT;