1.1 --- /dev/null	Thu Jan 01 00:00:00 1970 +0000
     1.2 +++ b/update/core-update.v2.0.3-v2.0.4.sql	Sat Feb 25 16:37:18 2012 +0100
     1.3 @@ -0,0 +1,115 @@
     1.4 +BEGIN;
     1.5 +
     1.6 +-- update version number:
     1.7 +CREATE OR REPLACE VIEW "liquid_feedback_version" AS
     1.8 +  SELECT * FROM (VALUES ('2.0.4', 2, 0, 4))
     1.9 +  AS "subquery"("string", "major", "minor", "revision");
    1.10 +
    1.11 +-- add table "session":
    1.12 +CREATE TABLE "session" (
    1.13 +        "ident"                 TEXT            PRIMARY KEY,
    1.14 +        "additional_secret"     TEXT,
    1.15 +        "expiry"                TIMESTAMPTZ     NOT NULL DEFAULT now() + '24 hours',
    1.16 +        "member_id"             INT8            REFERENCES "member" ("id") ON DELETE SET NULL,
    1.17 +        "lang"                  TEXT );
    1.18 +CREATE INDEX "session_expiry_idx" ON "session" ("expiry");
    1.19 +COMMENT ON TABLE "session" IS 'Sessions, i.e. for a web-frontend or API layer';
    1.20 +COMMENT ON COLUMN "session"."ident"             IS 'Secret session identifier (i.e. random string)';
    1.21 +COMMENT ON COLUMN "session"."additional_secret" IS 'Additional field to store a secret, which can be used against CSRF attacks';
    1.22 +COMMENT ON COLUMN "session"."member_id"         IS 'Reference to member, who is logged in';
    1.23 +COMMENT ON COLUMN "session"."lang"              IS 'Language code of the selected language';
    1.24 +
    1.25 +-- add column "lang" to table "member":
    1.26 +ALTER TABLE "member" ADD COLUMN "lang" TEXT;
    1.27 +COMMENT ON COLUMN "member"."lang" IS 'Language code of the preferred language of the member';
    1.28 +
    1.29 +-- drop view "pending_notification":
    1.30 +DROP VIEW "pending_notification";
    1.31 +
    1.32 +-- remove column "notify_event_id" of table "member":
    1.33 +ALTER TABLE "member" DROP COLUMN "notify_event_id";
    1.34 +
    1.35 +-- add table "notification_sent":
    1.36 +CREATE TABLE "notification_sent" (
    1.37 +        "event_id"              INT8            NOT NULL );
    1.38 +CREATE UNIQUE INDEX "notification_sent_singleton_idx" ON "notification_sent" ((1));
    1.39 +COMMENT ON TABLE "notification_sent" IS 'This table stores one row with the last event_id, for which notifications have been sent out';
    1.40 +COMMENT ON INDEX "notification_sent_singleton_idx" IS 'This index ensures that "notification_sent" only contains one row maximum.';
    1.41 +
    1.42 +-- add view "selected_event_seen_by_member":
    1.43 +CREATE VIEW "selected_event_seen_by_member" AS
    1.44 +  SELECT
    1.45 +    "member"."id" AS "seen_by_member_id",
    1.46 +    CASE WHEN "event"."state" IN (
    1.47 +      'voting',
    1.48 +      'finished_without_winner',
    1.49 +      'finished_with_winner'
    1.50 +    ) THEN
    1.51 +      'voting'::"notify_level"
    1.52 +    ELSE
    1.53 +      CASE WHEN "event"."state" IN (
    1.54 +        'verification',
    1.55 +        'canceled_after_revocation_during_verification',
    1.56 +        'canceled_no_initiative_admitted'
    1.57 +      ) THEN
    1.58 +        'verification'::"notify_level"
    1.59 +      ELSE
    1.60 +        CASE WHEN "event"."state" IN (
    1.61 +          'discussion',
    1.62 +          'canceled_after_revocation_during_discussion'
    1.63 +        ) THEN
    1.64 +          'discussion'::"notify_level"
    1.65 +        ELSE
    1.66 +          'all'::"notify_level"
    1.67 +        END
    1.68 +      END
    1.69 +    END AS "notify_level",
    1.70 +    "event".*
    1.71 +  FROM "member" CROSS JOIN "event"
    1.72 +  LEFT JOIN "issue"
    1.73 +    ON "event"."issue_id" = "issue"."id"
    1.74 +  LEFT JOIN "membership"
    1.75 +    ON "member"."id" = "membership"."member_id"
    1.76 +    AND "issue"."area_id" = "membership"."area_id"
    1.77 +  LEFT JOIN "interest"
    1.78 +    ON "member"."id" = "interest"."member_id"
    1.79 +    AND "event"."issue_id" = "interest"."issue_id"
    1.80 +  LEFT JOIN "supporter"
    1.81 +    ON "member"."id" = "supporter"."member_id"
    1.82 +    AND "event"."initiative_id" = "supporter"."initiative_id"
    1.83 +  LEFT JOIN "ignored_member"
    1.84 +    ON "member"."id" = "ignored_member"."member_id"
    1.85 +    AND "event"."member_id" = "ignored_member"."other_member_id"
    1.86 +  LEFT JOIN "ignored_initiative"
    1.87 +    ON "member"."id" = "ignored_initiative"."member_id"
    1.88 +    AND "event"."initiative_id" = "ignored_initiative"."initiative_id"
    1.89 +  WHERE (
    1.90 +    ( "member"."notify_level" >= 'all' ) OR
    1.91 +    ( "member"."notify_level" >= 'voting' AND
    1.92 +      "event"."state" IN (
    1.93 +        'voting',
    1.94 +        'finished_without_winner',
    1.95 +        'finished_with_winner' ) ) OR
    1.96 +    ( "member"."notify_level" >= 'verification' AND
    1.97 +      "event"."state" IN (
    1.98 +        'verification',
    1.99 +        'canceled_after_revocation_during_verification',
   1.100 +        'canceled_no_initiative_admitted' ) ) OR
   1.101 +    ( "member"."notify_level" >= 'discussion' AND
   1.102 +      "event"."state" IN (
   1.103 +        'discussion',
   1.104 +        'canceled_after_revocation_during_discussion' ) ) )
   1.105 +  AND (
   1.106 +    "supporter"."member_id" NOTNULL OR
   1.107 +    "interest"."member_id" NOTNULL OR
   1.108 +    ( "membership"."member_id" NOTNULL AND
   1.109 +      "event"."event" IN (
   1.110 +        'issue_state_changed',
   1.111 +        'initiative_created_in_new_issue',
   1.112 +        'initiative_created_in_existing_issue',
   1.113 +        'initiative_revoked' ) ) )
   1.114 +  AND "ignored_member"."member_id" ISNULL
   1.115 +  AND "ignored_initiative"."member_id" ISNULL;
   1.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"';
   1.117 +
   1.118 +COMMIT;