jbe@224: BEGIN;
jbe@224: 
jbe@224: -- update version number:
jbe@224: CREATE OR REPLACE VIEW "liquid_feedback_version" AS
jbe@224:   SELECT * FROM (VALUES ('2.0.4', 2, 0, 4))
jbe@224:   AS "subquery"("string", "major", "minor", "revision");
jbe@224: 
jbe@224: -- add table "session":
jbe@224: CREATE TABLE "session" (
jbe@224:         "ident"                 TEXT            PRIMARY KEY,
jbe@224:         "additional_secret"     TEXT,
jbe@224:         "expiry"                TIMESTAMPTZ     NOT NULL DEFAULT now() + '24 hours',
jbe@224:         "member_id"             INT8            REFERENCES "member" ("id") ON DELETE SET NULL,
jbe@224:         "lang"                  TEXT );
jbe@224: CREATE INDEX "session_expiry_idx" ON "session" ("expiry");
jbe@224: COMMENT ON TABLE "session" IS 'Sessions, i.e. for a web-frontend or API layer';
jbe@224: COMMENT ON COLUMN "session"."ident"             IS 'Secret session identifier (i.e. random string)';
jbe@224: COMMENT ON COLUMN "session"."additional_secret" IS 'Additional field to store a secret, which can be used against CSRF attacks';
jbe@224: COMMENT ON COLUMN "session"."member_id"         IS 'Reference to member, who is logged in';
jbe@224: COMMENT ON COLUMN "session"."lang"              IS 'Language code of the selected language';
jbe@224: 
jbe@224: -- add column "lang" to table "member":
jbe@224: ALTER TABLE "member" ADD COLUMN "lang" TEXT;
jbe@224: COMMENT ON COLUMN "member"."lang" IS 'Language code of the preferred language of the member';
jbe@224: 
jbe@224: -- drop view "pending_notification":
jbe@224: DROP VIEW "pending_notification";
jbe@224: 
jbe@224: -- remove column "notify_event_id" of table "member":
jbe@224: ALTER TABLE "member" DROP COLUMN "notify_event_id";
jbe@224: 
jbe@224: -- add table "notification_sent":
jbe@224: CREATE TABLE "notification_sent" (
jbe@224:         "event_id"              INT8            NOT NULL );
jbe@224: CREATE UNIQUE INDEX "notification_sent_singleton_idx" ON "notification_sent" ((1));
jbe@224: COMMENT ON TABLE "notification_sent" IS 'This table stores one row with the last event_id, for which notifications have been sent out';
jbe@224: COMMENT ON INDEX "notification_sent_singleton_idx" IS 'This index ensures that "notification_sent" only contains one row maximum.';
jbe@224: 
jbe@224: -- add view "selected_event_seen_by_member":
jbe@224: CREATE VIEW "selected_event_seen_by_member" AS
jbe@224:   SELECT
jbe@224:     "member"."id" AS "seen_by_member_id",
jbe@224:     CASE WHEN "event"."state" IN (
jbe@224:       'voting',
jbe@224:       'finished_without_winner',
jbe@224:       'finished_with_winner'
jbe@224:     ) THEN
jbe@224:       'voting'::"notify_level"
jbe@224:     ELSE
jbe@224:       CASE WHEN "event"."state" IN (
jbe@224:         'verification',
jbe@224:         'canceled_after_revocation_during_verification',
jbe@224:         'canceled_no_initiative_admitted'
jbe@224:       ) THEN
jbe@224:         'verification'::"notify_level"
jbe@224:       ELSE
jbe@224:         CASE WHEN "event"."state" IN (
jbe@224:           'discussion',
jbe@224:           'canceled_after_revocation_during_discussion'
jbe@224:         ) THEN
jbe@224:           'discussion'::"notify_level"
jbe@224:         ELSE
jbe@224:           'all'::"notify_level"
jbe@224:         END
jbe@224:       END
jbe@224:     END AS "notify_level",
jbe@224:     "event".*
jbe@224:   FROM "member" CROSS JOIN "event"
jbe@224:   LEFT JOIN "issue"
jbe@224:     ON "event"."issue_id" = "issue"."id"
jbe@224:   LEFT JOIN "membership"
jbe@224:     ON "member"."id" = "membership"."member_id"
jbe@224:     AND "issue"."area_id" = "membership"."area_id"
jbe@224:   LEFT JOIN "interest"
jbe@224:     ON "member"."id" = "interest"."member_id"
jbe@224:     AND "event"."issue_id" = "interest"."issue_id"
jbe@224:   LEFT JOIN "supporter"
jbe@224:     ON "member"."id" = "supporter"."member_id"
jbe@224:     AND "event"."initiative_id" = "supporter"."initiative_id"
jbe@224:   LEFT JOIN "ignored_member"
jbe@224:     ON "member"."id" = "ignored_member"."member_id"
jbe@224:     AND "event"."member_id" = "ignored_member"."other_member_id"
jbe@224:   LEFT JOIN "ignored_initiative"
jbe@224:     ON "member"."id" = "ignored_initiative"."member_id"
jbe@224:     AND "event"."initiative_id" = "ignored_initiative"."initiative_id"
jbe@224:   WHERE (
jbe@224:     ( "member"."notify_level" >= 'all' ) OR
jbe@224:     ( "member"."notify_level" >= 'voting' AND
jbe@224:       "event"."state" IN (
jbe@224:         'voting',
jbe@224:         'finished_without_winner',
jbe@224:         'finished_with_winner' ) ) OR
jbe@224:     ( "member"."notify_level" >= 'verification' AND
jbe@224:       "event"."state" IN (
jbe@224:         'verification',
jbe@224:         'canceled_after_revocation_during_verification',
jbe@224:         'canceled_no_initiative_admitted' ) ) OR
jbe@224:     ( "member"."notify_level" >= 'discussion' AND
jbe@224:       "event"."state" IN (
jbe@224:         'discussion',
jbe@224:         'canceled_after_revocation_during_discussion' ) ) )
jbe@224:   AND (
jbe@224:     "supporter"."member_id" NOTNULL OR
jbe@224:     "interest"."member_id" NOTNULL OR
jbe@224:     ( "membership"."member_id" NOTNULL AND
jbe@224:       "event"."event" IN (
jbe@224:         'issue_state_changed',
jbe@224:         'initiative_created_in_new_issue',
jbe@224:         'initiative_created_in_existing_issue',
jbe@224:         'initiative_revoked' ) ) )
jbe@224:   AND "ignored_member"."member_id" ISNULL
jbe@224:   AND "ignored_initiative"."member_id" ISNULL;
jbe@224: 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"';
jbe@224: 
jbe@224: COMMIT;