liquid_feedback_core

annotate update/core-update.v3.0.3-v3.0.4.sql @ 504:d07e6a046d41

find changesets by author, revision, files, or words in the commit message
New column "notification_sent" in "member" table; New view "scheduled_notification_to_send"
author jbe
date Tue Apr 05 00:55:31 2016 +0200 (2016-04-05)
parents f5c78b0590c6
children
rev   line source
jbe@440 1 BEGIN;
jbe@440 2
jbe@440 3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
jbe@440 4 SELECT * FROM (VALUES ('3.0.4', 3, 0, 4))
jbe@440 5 AS "subquery"("string", "major", "minor", "revision");
jbe@440 6
jbe@440 7 ALTER TABLE "member" ADD COLUMN "authority" TEXT;
jbe@440 8 ALTER TABLE "member" ADD COLUMN "authority_uid" TEXT;
jbe@440 9 ALTER TABLE "member" ADD COLUMN "authority_login" TEXT;
jbe@440 10
jbe@440 11 COMMENT ON COLUMN "member"."authority" IS 'NULL if LiquidFeedback Core is authoritative for the member account; otherwise a string that indicates the source/authority of the external account (e.g. ''LDAP'' for an LDAP account)';
jbe@440 12 COMMENT ON COLUMN "member"."authority_uid" IS 'Unique identifier (unique per "authority") that allows to identify an external account (e.g. even if the login name changes)';
jbe@440 13 COMMENT ON COLUMN "member"."authority_login" IS 'Login name for external accounts (field is not unique!)';
jbe@440 14
jbe@440 15 ALTER TABLE "member" ADD CONSTRAINT "authority_requires_uid_and_vice_versa"
jbe@440 16 CHECK ("authority" NOTNULL = "authority_uid" NOTNULL);
jbe@440 17
jbe@440 18 ALTER TABLE "member" ADD CONSTRAINT "authority_uid_unique_per_authority"
jbe@440 19 UNIQUE ("authority", "authority_uid");
jbe@440 20
jbe@440 21 ALTER TABLE "member" ADD CONSTRAINT "authority_login_requires_authority"
jbe@440 22 CHECK ("authority" NOTNULL OR "authority_login" ISNULL);
jbe@440 23
jbe@440 24 CREATE INDEX "member_authority_login_idx" ON "member" ("authority_login");
jbe@440 25
jbe@440 26 ALTER TABLE "session" ADD COLUMN "authority" TEXT;
jbe@440 27 ALTER TABLE "session" ADD COLUMN "authority_uid" TEXT;
jbe@440 28 ALTER TABLE "session" ADD COLUMN "authority_login" TEXT;
jbe@440 29
jbe@440 30 COMMENT ON COLUMN "session"."authority" IS 'Temporary store for "member"."authority" during member account creation';
jbe@440 31 COMMENT ON COLUMN "session"."authority_uid" IS 'Temporary store for "member"."authority_uid" during member account creation';
jbe@440 32 COMMENT ON COLUMN "session"."authority_login" IS 'Temporary store for "member"."authority_login" during member account creation';
jbe@440 33
jbe@441 34 CREATE OR REPLACE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)
jbe@441 35 RETURNS VOID
jbe@441 36 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@441 37 BEGIN
jbe@441 38 UPDATE "member" SET
jbe@441 39 "last_login" = NULL,
jbe@441 40 "last_delegation_check" = NULL,
jbe@441 41 "login" = NULL,
jbe@441 42 "password" = NULL,
jbe@441 43 "authority" = NULL,
jbe@441 44 "authority_uid" = NULL,
jbe@441 45 "authority_login" = NULL,
jbe@441 46 "locked" = TRUE,
jbe@441 47 "active" = FALSE,
jbe@441 48 "notify_email" = NULL,
jbe@441 49 "notify_email_unconfirmed" = NULL,
jbe@441 50 "notify_email_secret" = NULL,
jbe@441 51 "notify_email_secret_expiry" = NULL,
jbe@441 52 "notify_email_lock_expiry" = NULL,
jbe@441 53 "login_recovery_expiry" = NULL,
jbe@441 54 "password_reset_secret" = NULL,
jbe@441 55 "password_reset_secret_expiry" = NULL,
jbe@441 56 "organizational_unit" = NULL,
jbe@441 57 "internal_posts" = NULL,
jbe@441 58 "realname" = NULL,
jbe@441 59 "birthday" = NULL,
jbe@441 60 "address" = NULL,
jbe@441 61 "email" = NULL,
jbe@441 62 "xmpp_address" = NULL,
jbe@441 63 "website" = NULL,
jbe@441 64 "phone" = NULL,
jbe@441 65 "mobile_phone" = NULL,
jbe@441 66 "profession" = NULL,
jbe@441 67 "external_memberships" = NULL,
jbe@441 68 "external_posts" = NULL,
jbe@441 69 "statement" = NULL
jbe@441 70 WHERE "id" = "member_id_p";
jbe@441 71 -- "text_search_data" is updated by triggers
jbe@441 72 DELETE FROM "setting" WHERE "member_id" = "member_id_p";
jbe@441 73 DELETE FROM "setting_map" WHERE "member_id" = "member_id_p";
jbe@441 74 DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p";
jbe@441 75 DELETE FROM "member_image" WHERE "member_id" = "member_id_p";
jbe@441 76 DELETE FROM "contact" WHERE "member_id" = "member_id_p";
jbe@441 77 DELETE FROM "ignored_member" WHERE "member_id" = "member_id_p";
jbe@441 78 DELETE FROM "session" WHERE "member_id" = "member_id_p";
jbe@441 79 DELETE FROM "area_setting" WHERE "member_id" = "member_id_p";
jbe@441 80 DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p";
jbe@441 81 DELETE FROM "ignored_initiative" WHERE "member_id" = "member_id_p";
jbe@441 82 DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p";
jbe@441 83 DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p";
jbe@441 84 DELETE FROM "membership" WHERE "member_id" = "member_id_p";
jbe@441 85 DELETE FROM "delegation" WHERE "truster_id" = "member_id_p";
jbe@441 86 DELETE FROM "non_voter" WHERE "member_id" = "member_id_p";
jbe@441 87 DELETE FROM "direct_voter" USING "issue"
jbe@441 88 WHERE "direct_voter"."issue_id" = "issue"."id"
jbe@441 89 AND "issue"."closed" ISNULL
jbe@441 90 AND "member_id" = "member_id_p";
jbe@441 91 RETURN;
jbe@441 92 END;
jbe@441 93 $$;
jbe@441 94
jbe@441 95 CREATE OR REPLACE FUNCTION "delete_private_data"()
jbe@441 96 RETURNS VOID
jbe@441 97 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@441 98 BEGIN
jbe@441 99 DELETE FROM "temporary_transaction_data";
jbe@441 100 DELETE FROM "member" WHERE "activated" ISNULL;
jbe@441 101 UPDATE "member" SET
jbe@441 102 "invite_code" = NULL,
jbe@441 103 "invite_code_expiry" = NULL,
jbe@441 104 "admin_comment" = NULL,
jbe@441 105 "last_login" = NULL,
jbe@441 106 "last_delegation_check" = NULL,
jbe@441 107 "login" = NULL,
jbe@441 108 "password" = NULL,
jbe@441 109 "authority" = NULL,
jbe@441 110 "authority_uid" = NULL,
jbe@441 111 "authority_login" = NULL,
jbe@441 112 "lang" = NULL,
jbe@441 113 "notify_email" = NULL,
jbe@441 114 "notify_email_unconfirmed" = NULL,
jbe@441 115 "notify_email_secret" = NULL,
jbe@441 116 "notify_email_secret_expiry" = NULL,
jbe@441 117 "notify_email_lock_expiry" = NULL,
jbe@441 118 "notify_level" = NULL,
jbe@441 119 "login_recovery_expiry" = NULL,
jbe@441 120 "password_reset_secret" = NULL,
jbe@441 121 "password_reset_secret_expiry" = NULL,
jbe@441 122 "organizational_unit" = NULL,
jbe@441 123 "internal_posts" = NULL,
jbe@441 124 "realname" = NULL,
jbe@441 125 "birthday" = NULL,
jbe@441 126 "address" = NULL,
jbe@441 127 "email" = NULL,
jbe@441 128 "xmpp_address" = NULL,
jbe@441 129 "website" = NULL,
jbe@441 130 "phone" = NULL,
jbe@441 131 "mobile_phone" = NULL,
jbe@441 132 "profession" = NULL,
jbe@441 133 "external_memberships" = NULL,
jbe@441 134 "external_posts" = NULL,
jbe@441 135 "formatting_engine" = NULL,
jbe@441 136 "statement" = NULL;
jbe@441 137 -- "text_search_data" is updated by triggers
jbe@441 138 DELETE FROM "setting";
jbe@441 139 DELETE FROM "setting_map";
jbe@441 140 DELETE FROM "member_relation_setting";
jbe@441 141 DELETE FROM "member_image";
jbe@441 142 DELETE FROM "contact";
jbe@441 143 DELETE FROM "ignored_member";
jbe@441 144 DELETE FROM "session";
jbe@441 145 DELETE FROM "area_setting";
jbe@441 146 DELETE FROM "issue_setting";
jbe@441 147 DELETE FROM "ignored_initiative";
jbe@441 148 DELETE FROM "initiative_setting";
jbe@441 149 DELETE FROM "suggestion_setting";
jbe@441 150 DELETE FROM "non_voter";
jbe@441 151 DELETE FROM "direct_voter" USING "issue"
jbe@441 152 WHERE "direct_voter"."issue_id" = "issue"."id"
jbe@441 153 AND "issue"."closed" ISNULL;
jbe@441 154 RETURN;
jbe@441 155 END;
jbe@441 156 $$;
jbe@441 157
jbe@440 158 COMMIT;

Impressum / About Us