liquid_feedback_core

annotate update/core-update.v2.0.3-v2.0.4.sql @ 494:b4b660562322

find changesets by author, revision, files, or words in the commit message
Another bugfix in function "get_initiatives_for_notification"
author jbe
date Sun Apr 03 20:00:20 2016 +0200 (2016-04-03)
parents 10a231cfd585
children
rev   line source
jbe@224 1 BEGIN;
jbe@224 2
jbe@224 3 -- update version number:
jbe@224 4 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
jbe@224 5 SELECT * FROM (VALUES ('2.0.4', 2, 0, 4))
jbe@224 6 AS "subquery"("string", "major", "minor", "revision");
jbe@224 7
jbe@225 8 -- drop NOT NULL constraints on columns "name" and "notify_level"
jbe@225 9 -- in table "member", and add new constraint for "name":
jbe@225 10 ALTER TABLE "member" ALTER COLUMN "notify_level" DROP NOT NULL;
jbe@225 11 ALTER TABLE "member" ALTER COLUMN "name" DROP NOT NULL;
jbe@225 12 ALTER TABLE "member" ADD CONSTRAINT "name_not_null_if_activated" CHECK ("activated" ISNULL OR "name" NOTNULL);
jbe@225 13 COMMENT ON COLUMN "member"."notify_level" IS 'Selects which event notifications are to be sent to the "notify_email" mail address, may be NULL if member did not make any selection yet';
jbe@225 14 COMMENT ON COLUMN "member"."name" IS 'Distinct name of the member, may be NULL if account has not been activated yet';
jbe@225 15
jbe@224 16 -- add table "session":
jbe@224 17 CREATE TABLE "session" (
jbe@224 18 "ident" TEXT PRIMARY KEY,
jbe@224 19 "additional_secret" TEXT,
jbe@224 20 "expiry" TIMESTAMPTZ NOT NULL DEFAULT now() + '24 hours',
jbe@224 21 "member_id" INT8 REFERENCES "member" ("id") ON DELETE SET NULL,
jbe@224 22 "lang" TEXT );
jbe@224 23 CREATE INDEX "session_expiry_idx" ON "session" ("expiry");
jbe@224 24 COMMENT ON TABLE "session" IS 'Sessions, i.e. for a web-frontend or API layer';
jbe@224 25 COMMENT ON COLUMN "session"."ident" IS 'Secret session identifier (i.e. random string)';
jbe@224 26 COMMENT ON COLUMN "session"."additional_secret" IS 'Additional field to store a secret, which can be used against CSRF attacks';
jbe@224 27 COMMENT ON COLUMN "session"."member_id" IS 'Reference to member, who is logged in';
jbe@224 28 COMMENT ON COLUMN "session"."lang" IS 'Language code of the selected language';
jbe@224 29
jbe@224 30 -- add column "lang" to table "member":
jbe@224 31 ALTER TABLE "member" ADD COLUMN "lang" TEXT;
jbe@224 32 COMMENT ON COLUMN "member"."lang" IS 'Language code of the preferred language of the member';
jbe@224 33
jbe@224 34 -- drop view "pending_notification":
jbe@224 35 DROP VIEW "pending_notification";
jbe@224 36
jbe@224 37 -- remove column "notify_event_id" of table "member":
jbe@224 38 ALTER TABLE "member" DROP COLUMN "notify_event_id";
jbe@224 39
jbe@224 40 -- add table "notification_sent":
jbe@224 41 CREATE TABLE "notification_sent" (
jbe@224 42 "event_id" INT8 NOT NULL );
jbe@224 43 CREATE UNIQUE INDEX "notification_sent_singleton_idx" ON "notification_sent" ((1));
jbe@224 44 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 45 COMMENT ON INDEX "notification_sent_singleton_idx" IS 'This index ensures that "notification_sent" only contains one row maximum.';
jbe@224 46
jbe@224 47 -- add view "selected_event_seen_by_member":
jbe@224 48 CREATE VIEW "selected_event_seen_by_member" AS
jbe@224 49 SELECT
jbe@224 50 "member"."id" AS "seen_by_member_id",
jbe@224 51 CASE WHEN "event"."state" IN (
jbe@224 52 'voting',
jbe@224 53 'finished_without_winner',
jbe@224 54 'finished_with_winner'
jbe@224 55 ) THEN
jbe@224 56 'voting'::"notify_level"
jbe@224 57 ELSE
jbe@224 58 CASE WHEN "event"."state" IN (
jbe@224 59 'verification',
jbe@224 60 'canceled_after_revocation_during_verification',
jbe@224 61 'canceled_no_initiative_admitted'
jbe@224 62 ) THEN
jbe@224 63 'verification'::"notify_level"
jbe@224 64 ELSE
jbe@224 65 CASE WHEN "event"."state" IN (
jbe@224 66 'discussion',
jbe@224 67 'canceled_after_revocation_during_discussion'
jbe@224 68 ) THEN
jbe@224 69 'discussion'::"notify_level"
jbe@224 70 ELSE
jbe@224 71 'all'::"notify_level"
jbe@224 72 END
jbe@224 73 END
jbe@224 74 END AS "notify_level",
jbe@224 75 "event".*
jbe@224 76 FROM "member" CROSS JOIN "event"
jbe@224 77 LEFT JOIN "issue"
jbe@224 78 ON "event"."issue_id" = "issue"."id"
jbe@224 79 LEFT JOIN "membership"
jbe@224 80 ON "member"."id" = "membership"."member_id"
jbe@224 81 AND "issue"."area_id" = "membership"."area_id"
jbe@224 82 LEFT JOIN "interest"
jbe@224 83 ON "member"."id" = "interest"."member_id"
jbe@224 84 AND "event"."issue_id" = "interest"."issue_id"
jbe@224 85 LEFT JOIN "supporter"
jbe@224 86 ON "member"."id" = "supporter"."member_id"
jbe@224 87 AND "event"."initiative_id" = "supporter"."initiative_id"
jbe@224 88 LEFT JOIN "ignored_member"
jbe@224 89 ON "member"."id" = "ignored_member"."member_id"
jbe@224 90 AND "event"."member_id" = "ignored_member"."other_member_id"
jbe@224 91 LEFT JOIN "ignored_initiative"
jbe@224 92 ON "member"."id" = "ignored_initiative"."member_id"
jbe@224 93 AND "event"."initiative_id" = "ignored_initiative"."initiative_id"
jbe@224 94 WHERE (
jbe@224 95 ( "member"."notify_level" >= 'all' ) OR
jbe@224 96 ( "member"."notify_level" >= 'voting' AND
jbe@224 97 "event"."state" IN (
jbe@224 98 'voting',
jbe@224 99 'finished_without_winner',
jbe@224 100 'finished_with_winner' ) ) OR
jbe@224 101 ( "member"."notify_level" >= 'verification' AND
jbe@224 102 "event"."state" IN (
jbe@224 103 'verification',
jbe@224 104 'canceled_after_revocation_during_verification',
jbe@224 105 'canceled_no_initiative_admitted' ) ) OR
jbe@224 106 ( "member"."notify_level" >= 'discussion' AND
jbe@224 107 "event"."state" IN (
jbe@224 108 'discussion',
jbe@224 109 'canceled_after_revocation_during_discussion' ) ) )
jbe@224 110 AND (
jbe@224 111 "supporter"."member_id" NOTNULL OR
jbe@224 112 "interest"."member_id" NOTNULL OR
jbe@224 113 ( "membership"."member_id" NOTNULL AND
jbe@224 114 "event"."event" IN (
jbe@224 115 'issue_state_changed',
jbe@224 116 'initiative_created_in_new_issue',
jbe@224 117 'initiative_created_in_existing_issue',
jbe@224 118 'initiative_revoked' ) ) )
jbe@224 119 AND "ignored_member"."member_id" ISNULL
jbe@224 120 AND "ignored_initiative"."member_id" ISNULL;
jbe@224 121 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 122
jbe@226 123 -- delete non-activated members in function "delete_private_data":
jbe@226 124 CREATE OR REPLACE FUNCTION "delete_private_data"()
jbe@226 125 RETURNS VOID
jbe@226 126 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@226 127 BEGIN
jbe@226 128 DELETE FROM "member" WHERE "activated" ISNULL;
jbe@226 129 UPDATE "member" SET
jbe@226 130 "invite_code" = NULL,
jbe@226 131 "last_login" = NULL,
jbe@226 132 "login" = NULL,
jbe@226 133 "password" = NULL,
jbe@226 134 "notify_email" = NULL,
jbe@226 135 "notify_email_unconfirmed" = NULL,
jbe@226 136 "notify_email_secret" = NULL,
jbe@226 137 "notify_email_secret_expiry" = NULL,
jbe@226 138 "notify_email_lock_expiry" = NULL,
jbe@226 139 "password_reset_secret" = NULL,
jbe@226 140 "password_reset_secret_expiry" = NULL,
jbe@226 141 "organizational_unit" = NULL,
jbe@226 142 "internal_posts" = NULL,
jbe@226 143 "realname" = NULL,
jbe@226 144 "birthday" = NULL,
jbe@226 145 "address" = NULL,
jbe@226 146 "email" = NULL,
jbe@226 147 "xmpp_address" = NULL,
jbe@226 148 "website" = NULL,
jbe@226 149 "phone" = NULL,
jbe@226 150 "mobile_phone" = NULL,
jbe@226 151 "profession" = NULL,
jbe@226 152 "external_memberships" = NULL,
jbe@226 153 "external_posts" = NULL,
jbe@226 154 "statement" = NULL;
jbe@226 155 -- "text_search_data" is updated by triggers
jbe@226 156 DELETE FROM "setting";
jbe@226 157 DELETE FROM "setting_map";
jbe@226 158 DELETE FROM "member_relation_setting";
jbe@226 159 DELETE FROM "member_image";
jbe@226 160 DELETE FROM "contact";
jbe@226 161 DELETE FROM "ignored_member";
jbe@226 162 DELETE FROM "area_setting";
jbe@226 163 DELETE FROM "issue_setting";
jbe@226 164 DELETE FROM "ignored_initiative";
jbe@226 165 DELETE FROM "initiative_setting";
jbe@226 166 DELETE FROM "suggestion_setting";
jbe@226 167 DELETE FROM "non_voter";
jbe@226 168 DELETE FROM "direct_voter" USING "issue"
jbe@226 169 WHERE "direct_voter"."issue_id" = "issue"."id"
jbe@226 170 AND "issue"."closed" ISNULL;
jbe@226 171 RETURN;
jbe@226 172 END;
jbe@226 173 $$;
jbe@226 174
jbe@224 175 COMMIT;

Impressum / About Us