liquid_feedback_core

annotate update/core-update.v2.2.3-v2.2.4.sql @ 605:f50171274974

find changesets by author, revision, files, or words in the commit message
Included hint in README on solving apparently missing data types from extensions
author jbe
date Tue Feb 11 17:10:40 2020 +0100 (2020-02-11)
parents ae69cf82c05f
children
rev   line source
jbe@387 1 BEGIN;
jbe@387 2
jbe@387 3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
jbe@387 4 SELECT * FROM (VALUES ('2.2.4', 2, 2, 4))
jbe@387 5 AS "subquery"("string", "major", "minor", "revision");
jbe@387 6
jbe@387 7
jbe@387 8 ALTER TABLE "member" ADD COLUMN "last_delegation_check" TIMESTAMPTZ;
jbe@387 9 ALTER TABLE "member" ADD COLUMN "login_recovery_expiry" TIMESTAMPTZ;
jbe@387 10
jbe@387 11 COMMENT ON COLUMN "member"."last_delegation_check" IS 'Timestamp of last delegation check (i.e. confirmation of all unit and area delegations)';
jbe@387 12 COMMENT ON COLUMN "member"."login_recovery_expiry" IS 'Date/time after which another login recovery attempt is allowed';
jbe@387 13 COMMENT ON COLUMN "member"."password_reset_secret" IS 'Secret string sent via e-mail for password recovery';
jbe@387 14 COMMENT ON COLUMN "member"."password_reset_secret_expiry" IS 'Date/time until the password recovery secret is valid, and date/time after which another password recovery attempt is allowed';
jbe@387 15
jbe@387 16 ALTER TABLE "session" ADD COLUMN "needs_delegation_check" BOOLEAN NOT NULL DEFAULT FALSE;
jbe@387 17 COMMENT ON COLUMN "session"."needs_delegation_check" IS 'Set to TRUE, if member must perform a delegation check to proceed with login; see column "last_delegation_check" in "member" table';
jbe@387 18
jbe@387 19 CREATE OR REPLACE VIEW "event_seen_by_member" AS
jbe@387 20 SELECT
jbe@387 21 "member"."id" AS "seen_by_member_id",
jbe@387 22 CASE WHEN "event"."state" IN (
jbe@387 23 'voting',
jbe@387 24 'finished_without_winner',
jbe@387 25 'finished_with_winner'
jbe@387 26 ) THEN
jbe@387 27 'voting'::"notify_level"
jbe@387 28 ELSE
jbe@387 29 CASE WHEN "event"."state" IN (
jbe@387 30 'verification',
jbe@387 31 'canceled_after_revocation_during_verification',
jbe@387 32 'canceled_no_initiative_admitted'
jbe@387 33 ) THEN
jbe@387 34 'verification'::"notify_level"
jbe@387 35 ELSE
jbe@387 36 CASE WHEN "event"."state" IN (
jbe@387 37 'discussion',
jbe@387 38 'canceled_after_revocation_during_discussion'
jbe@387 39 ) THEN
jbe@387 40 'discussion'::"notify_level"
jbe@387 41 ELSE
jbe@387 42 'all'::"notify_level"
jbe@387 43 END
jbe@387 44 END
jbe@387 45 END AS "notify_level",
jbe@387 46 "event".*
jbe@387 47 FROM "member" CROSS JOIN "event"
jbe@387 48 LEFT JOIN "issue"
jbe@387 49 ON "event"."issue_id" = "issue"."id"
jbe@387 50 LEFT JOIN "membership"
jbe@387 51 ON "member"."id" = "membership"."member_id"
jbe@387 52 AND "issue"."area_id" = "membership"."area_id"
jbe@387 53 LEFT JOIN "interest"
jbe@387 54 ON "member"."id" = "interest"."member_id"
jbe@387 55 AND "event"."issue_id" = "interest"."issue_id"
jbe@387 56 LEFT JOIN "ignored_member"
jbe@387 57 ON "member"."id" = "ignored_member"."member_id"
jbe@387 58 AND "event"."member_id" = "ignored_member"."other_member_id"
jbe@387 59 LEFT JOIN "ignored_initiative"
jbe@387 60 ON "member"."id" = "ignored_initiative"."member_id"
jbe@387 61 AND "event"."initiative_id" = "ignored_initiative"."initiative_id"
jbe@387 62 WHERE (
jbe@387 63 "interest"."member_id" NOTNULL OR
jbe@387 64 ( "membership"."member_id" NOTNULL AND
jbe@387 65 "event"."event" IN (
jbe@387 66 'issue_state_changed',
jbe@387 67 'initiative_created_in_new_issue',
jbe@387 68 'initiative_created_in_existing_issue',
jbe@387 69 'initiative_revoked' ) ) )
jbe@387 70 AND "ignored_member"."member_id" ISNULL
jbe@387 71 AND "ignored_initiative"."member_id" ISNULL;
jbe@387 72
jbe@387 73 CREATE OR REPLACE VIEW "selected_event_seen_by_member" AS
jbe@387 74 SELECT
jbe@387 75 "member"."id" AS "seen_by_member_id",
jbe@387 76 CASE WHEN "event"."state" IN (
jbe@387 77 'voting',
jbe@387 78 'finished_without_winner',
jbe@387 79 'finished_with_winner'
jbe@387 80 ) THEN
jbe@387 81 'voting'::"notify_level"
jbe@387 82 ELSE
jbe@387 83 CASE WHEN "event"."state" IN (
jbe@387 84 'verification',
jbe@387 85 'canceled_after_revocation_during_verification',
jbe@387 86 'canceled_no_initiative_admitted'
jbe@387 87 ) THEN
jbe@387 88 'verification'::"notify_level"
jbe@387 89 ELSE
jbe@387 90 CASE WHEN "event"."state" IN (
jbe@387 91 'discussion',
jbe@387 92 'canceled_after_revocation_during_discussion'
jbe@387 93 ) THEN
jbe@387 94 'discussion'::"notify_level"
jbe@387 95 ELSE
jbe@387 96 'all'::"notify_level"
jbe@387 97 END
jbe@387 98 END
jbe@387 99 END AS "notify_level",
jbe@387 100 "event".*
jbe@387 101 FROM "member" CROSS JOIN "event"
jbe@387 102 LEFT JOIN "issue"
jbe@387 103 ON "event"."issue_id" = "issue"."id"
jbe@387 104 LEFT JOIN "membership"
jbe@387 105 ON "member"."id" = "membership"."member_id"
jbe@387 106 AND "issue"."area_id" = "membership"."area_id"
jbe@387 107 LEFT JOIN "interest"
jbe@387 108 ON "member"."id" = "interest"."member_id"
jbe@387 109 AND "event"."issue_id" = "interest"."issue_id"
jbe@387 110 LEFT JOIN "ignored_member"
jbe@387 111 ON "member"."id" = "ignored_member"."member_id"
jbe@387 112 AND "event"."member_id" = "ignored_member"."other_member_id"
jbe@387 113 LEFT JOIN "ignored_initiative"
jbe@387 114 ON "member"."id" = "ignored_initiative"."member_id"
jbe@387 115 AND "event"."initiative_id" = "ignored_initiative"."initiative_id"
jbe@387 116 WHERE (
jbe@387 117 ( "member"."notify_level" >= 'all' ) OR
jbe@387 118 ( "member"."notify_level" >= 'voting' AND
jbe@387 119 "event"."state" IN (
jbe@387 120 'voting',
jbe@387 121 'finished_without_winner',
jbe@387 122 'finished_with_winner' ) ) OR
jbe@387 123 ( "member"."notify_level" >= 'verification' AND
jbe@387 124 "event"."state" IN (
jbe@387 125 'verification',
jbe@387 126 'canceled_after_revocation_during_verification',
jbe@387 127 'canceled_no_initiative_admitted' ) ) OR
jbe@387 128 ( "member"."notify_level" >= 'discussion' AND
jbe@387 129 "event"."state" IN (
jbe@387 130 'discussion',
jbe@387 131 'canceled_after_revocation_during_discussion' ) ) )
jbe@387 132 AND (
jbe@387 133 "interest"."member_id" NOTNULL OR
jbe@387 134 ( "membership"."member_id" NOTNULL AND
jbe@387 135 "event"."event" IN (
jbe@387 136 'issue_state_changed',
jbe@387 137 'initiative_created_in_new_issue',
jbe@387 138 'initiative_created_in_existing_issue',
jbe@387 139 'initiative_revoked' ) ) )
jbe@387 140 AND "ignored_member"."member_id" ISNULL
jbe@387 141 AND "ignored_initiative"."member_id" ISNULL;
jbe@387 142
jbe@387 143 CREATE OR REPLACE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)
jbe@387 144 RETURNS VOID
jbe@387 145 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@387 146 BEGIN
jbe@387 147 UPDATE "member" SET
jbe@387 148 "last_login" = NULL,
jbe@387 149 "last_delegation_check" = NULL,
jbe@387 150 "login" = NULL,
jbe@387 151 "password" = NULL,
jbe@387 152 "locked" = TRUE,
jbe@387 153 "active" = FALSE,
jbe@387 154 "notify_email" = NULL,
jbe@387 155 "notify_email_unconfirmed" = NULL,
jbe@387 156 "notify_email_secret" = NULL,
jbe@387 157 "notify_email_secret_expiry" = NULL,
jbe@387 158 "notify_email_lock_expiry" = NULL,
jbe@387 159 "login_recovery_expiry" = NULL,
jbe@387 160 "password_reset_secret" = NULL,
jbe@387 161 "password_reset_secret_expiry" = NULL,
jbe@387 162 "organizational_unit" = NULL,
jbe@387 163 "internal_posts" = NULL,
jbe@387 164 "realname" = NULL,
jbe@387 165 "birthday" = NULL,
jbe@387 166 "address" = NULL,
jbe@387 167 "email" = NULL,
jbe@387 168 "xmpp_address" = NULL,
jbe@387 169 "website" = NULL,
jbe@387 170 "phone" = NULL,
jbe@387 171 "mobile_phone" = NULL,
jbe@387 172 "profession" = NULL,
jbe@387 173 "external_memberships" = NULL,
jbe@387 174 "external_posts" = NULL,
jbe@387 175 "statement" = NULL
jbe@387 176 WHERE "id" = "member_id_p";
jbe@387 177 -- "text_search_data" is updated by triggers
jbe@387 178 DELETE FROM "setting" WHERE "member_id" = "member_id_p";
jbe@387 179 DELETE FROM "setting_map" WHERE "member_id" = "member_id_p";
jbe@387 180 DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p";
jbe@387 181 DELETE FROM "member_image" WHERE "member_id" = "member_id_p";
jbe@387 182 DELETE FROM "contact" WHERE "member_id" = "member_id_p";
jbe@387 183 DELETE FROM "ignored_member" WHERE "member_id" = "member_id_p";
jbe@387 184 DELETE FROM "session" WHERE "member_id" = "member_id_p";
jbe@387 185 DELETE FROM "area_setting" WHERE "member_id" = "member_id_p";
jbe@387 186 DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p";
jbe@387 187 DELETE FROM "ignored_initiative" WHERE "member_id" = "member_id_p";
jbe@387 188 DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p";
jbe@387 189 DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p";
jbe@387 190 DELETE FROM "membership" WHERE "member_id" = "member_id_p";
jbe@387 191 DELETE FROM "delegation" WHERE "truster_id" = "member_id_p";
jbe@387 192 DELETE FROM "non_voter" WHERE "member_id" = "member_id_p";
jbe@387 193 DELETE FROM "direct_voter" USING "issue"
jbe@387 194 WHERE "direct_voter"."issue_id" = "issue"."id"
jbe@387 195 AND "issue"."closed" ISNULL
jbe@387 196 AND "member_id" = "member_id_p";
jbe@387 197 RETURN;
jbe@387 198 END;
jbe@387 199 $$;
jbe@387 200
jbe@387 201 CREATE OR REPLACE FUNCTION "delete_private_data"()
jbe@387 202 RETURNS VOID
jbe@387 203 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@387 204 BEGIN
jbe@387 205 DELETE FROM "temporary_transaction_data";
jbe@387 206 DELETE FROM "member" WHERE "activated" ISNULL;
jbe@387 207 UPDATE "member" SET
jbe@387 208 "invite_code" = NULL,
jbe@387 209 "invite_code_expiry" = NULL,
jbe@387 210 "admin_comment" = NULL,
jbe@387 211 "last_login" = NULL,
jbe@387 212 "last_delegation_check" = NULL,
jbe@387 213 "login" = NULL,
jbe@387 214 "password" = NULL,
jbe@387 215 "lang" = NULL,
jbe@387 216 "notify_email" = NULL,
jbe@387 217 "notify_email_unconfirmed" = NULL,
jbe@387 218 "notify_email_secret" = NULL,
jbe@387 219 "notify_email_secret_expiry" = NULL,
jbe@387 220 "notify_email_lock_expiry" = NULL,
jbe@387 221 "notify_level" = NULL,
jbe@387 222 "login_recovery_expiry" = NULL,
jbe@387 223 "password_reset_secret" = NULL,
jbe@387 224 "password_reset_secret_expiry" = NULL,
jbe@387 225 "organizational_unit" = NULL,
jbe@387 226 "internal_posts" = NULL,
jbe@387 227 "realname" = NULL,
jbe@387 228 "birthday" = NULL,
jbe@387 229 "address" = NULL,
jbe@387 230 "email" = NULL,
jbe@387 231 "xmpp_address" = NULL,
jbe@387 232 "website" = NULL,
jbe@387 233 "phone" = NULL,
jbe@387 234 "mobile_phone" = NULL,
jbe@387 235 "profession" = NULL,
jbe@387 236 "external_memberships" = NULL,
jbe@387 237 "external_posts" = NULL,
jbe@387 238 "formatting_engine" = NULL,
jbe@387 239 "statement" = NULL;
jbe@387 240 -- "text_search_data" is updated by triggers
jbe@387 241 DELETE FROM "setting";
jbe@387 242 DELETE FROM "setting_map";
jbe@387 243 DELETE FROM "member_relation_setting";
jbe@387 244 DELETE FROM "member_image";
jbe@387 245 DELETE FROM "contact";
jbe@387 246 DELETE FROM "ignored_member";
jbe@387 247 DELETE FROM "session";
jbe@387 248 DELETE FROM "area_setting";
jbe@387 249 DELETE FROM "issue_setting";
jbe@387 250 DELETE FROM "ignored_initiative";
jbe@387 251 DELETE FROM "initiative_setting";
jbe@387 252 DELETE FROM "suggestion_setting";
jbe@387 253 DELETE FROM "non_voter";
jbe@387 254 DELETE FROM "direct_voter" USING "issue"
jbe@387 255 WHERE "direct_voter"."issue_id" = "issue"."id"
jbe@387 256 AND "issue"."closed" ISNULL;
jbe@387 257 RETURN;
jbe@387 258 END;
jbe@387 259 $$;
jbe@387 260
jbe@387 261 COMMIT;

Impressum / About Us