liquid_feedback_core

annotate update/core-update.v1.1.0-v1.2.0.sql @ 296:bcfde30040f6

find changesets by author, revision, files, or words in the commit message
Use SHARE MODE table locks in function "lock_issue"("issue"."id") for tables neither referenced nor changed
author jbe
date Mon Sep 24 22:47:26 2012 +0200 (2012-09-24)
parents a7ad50614d82
children
rev   line source
jbe@57 1 BEGIN;
jbe@57 2
jbe@57 3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
jbe@57 4 SELECT * FROM (VALUES ('1.2.0', 1, 2, 0))
jbe@57 5 AS "subquery"("string", "major", "minor", "revision");
jbe@57 6
jbe@57 7 ALTER TABLE "member_history" DROP COLUMN "login";
jbe@57 8
jbe@57 9 COMMENT ON TABLE "member_history" IS 'Filled by trigger; keeps information about old names and active flag of members';
jbe@57 10 COMMENT ON COLUMN "member_history"."until" IS 'Timestamp until the data was valid';
jbe@57 11
jbe@57 12 CREATE OR REPLACE FUNCTION "write_member_history_trigger"()
jbe@57 13 RETURNS TRIGGER
jbe@57 14 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@57 15 BEGIN
jbe@57 16 IF
jbe@57 17 NEW."active" != OLD."active" OR
jbe@57 18 NEW."name" != OLD."name"
jbe@57 19 THEN
jbe@57 20 INSERT INTO "member_history"
jbe@57 21 ("member_id", "active", "name")
jbe@57 22 VALUES (NEW."id", OLD."active", OLD."name");
jbe@57 23 END IF;
jbe@57 24 RETURN NULL;
jbe@57 25 END;
jbe@57 26 $$;
jbe@57 27
jbe@57 28 COMMENT ON TRIGGER "write_member_history" ON "member" IS 'When changing certain fields of a member, create a history entry in "member_history" table';
jbe@57 29
jbe@57 30 CREATE OR REPLACE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)
jbe@57 31 RETURNS VOID
jbe@57 32 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@57 33 BEGIN
jbe@57 34 UPDATE "member" SET
jbe@57 35 "last_login" = NULL,
jbe@57 36 "login" = NULL,
jbe@57 37 "password" = NULL,
jbe@57 38 "active" = FALSE,
jbe@57 39 "notify_email" = NULL,
jbe@57 40 "notify_email_unconfirmed" = NULL,
jbe@57 41 "notify_email_secret" = NULL,
jbe@57 42 "notify_email_secret_expiry" = NULL,
jbe@57 43 "notify_email_lock_expiry" = NULL,
jbe@57 44 "password_reset_secret" = NULL,
jbe@57 45 "password_reset_secret_expiry" = NULL,
jbe@57 46 "organizational_unit" = NULL,
jbe@57 47 "internal_posts" = NULL,
jbe@57 48 "realname" = NULL,
jbe@57 49 "birthday" = NULL,
jbe@57 50 "address" = NULL,
jbe@57 51 "email" = NULL,
jbe@57 52 "xmpp_address" = NULL,
jbe@57 53 "website" = NULL,
jbe@57 54 "phone" = NULL,
jbe@57 55 "mobile_phone" = NULL,
jbe@57 56 "profession" = NULL,
jbe@57 57 "external_memberships" = NULL,
jbe@57 58 "external_posts" = NULL,
jbe@57 59 "statement" = NULL
jbe@57 60 WHERE "id" = "member_id_p";
jbe@57 61 -- "text_search_data" is updated by triggers
jbe@57 62 DELETE FROM "setting" WHERE "member_id" = "member_id_p";
jbe@57 63 DELETE FROM "setting_map" WHERE "member_id" = "member_id_p";
jbe@57 64 DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p";
jbe@57 65 DELETE FROM "member_image" WHERE "member_id" = "member_id_p";
jbe@57 66 DELETE FROM "contact" WHERE "member_id" = "member_id_p";
jbe@57 67 DELETE FROM "area_setting" WHERE "member_id" = "member_id_p";
jbe@57 68 DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p";
jbe@57 69 DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p";
jbe@57 70 DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p";
jbe@57 71 DELETE FROM "membership" WHERE "member_id" = "member_id_p";
jbe@57 72 DELETE FROM "delegation" WHERE "truster_id" = "member_id_p";
jbe@57 73 DELETE FROM "direct_voter" USING "issue"
jbe@57 74 WHERE "direct_voter"."issue_id" = "issue"."id"
jbe@57 75 AND "issue"."closed" ISNULL
jbe@57 76 AND "member_id" = "member_id_p";
jbe@57 77 RETURN;
jbe@57 78 END;
jbe@57 79 $$;
jbe@57 80
jbe@57 81 COMMENT ON FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE) IS 'Deactivate member and clear certain settings and data of this member (data protection)';
jbe@57 82
jbe@57 83 CREATE OR REPLACE FUNCTION "delete_private_data"()
jbe@57 84 RETURNS VOID
jbe@57 85 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@57 86 BEGIN
jbe@57 87 UPDATE "member" SET
jbe@57 88 "last_login" = NULL,
jbe@57 89 "login" = NULL,
jbe@57 90 "password" = NULL,
jbe@57 91 "notify_email" = NULL,
jbe@57 92 "notify_email_unconfirmed" = NULL,
jbe@57 93 "notify_email_secret" = NULL,
jbe@57 94 "notify_email_secret_expiry" = NULL,
jbe@57 95 "notify_email_lock_expiry" = NULL,
jbe@57 96 "password_reset_secret" = NULL,
jbe@57 97 "password_reset_secret_expiry" = NULL,
jbe@57 98 "organizational_unit" = NULL,
jbe@57 99 "internal_posts" = NULL,
jbe@57 100 "realname" = NULL,
jbe@57 101 "birthday" = NULL,
jbe@57 102 "address" = NULL,
jbe@57 103 "email" = NULL,
jbe@57 104 "xmpp_address" = NULL,
jbe@57 105 "website" = NULL,
jbe@57 106 "phone" = NULL,
jbe@57 107 "mobile_phone" = NULL,
jbe@57 108 "profession" = NULL,
jbe@57 109 "external_memberships" = NULL,
jbe@57 110 "external_posts" = NULL,
jbe@57 111 "statement" = NULL;
jbe@57 112 -- "text_search_data" is updated by triggers
jbe@57 113 DELETE FROM "invite_code";
jbe@57 114 DELETE FROM "setting";
jbe@57 115 DELETE FROM "setting_map";
jbe@57 116 DELETE FROM "member_relation_setting";
jbe@57 117 DELETE FROM "member_image";
jbe@57 118 DELETE FROM "contact";
jbe@57 119 DELETE FROM "session";
jbe@57 120 DELETE FROM "area_setting";
jbe@57 121 DELETE FROM "issue_setting";
jbe@57 122 DELETE FROM "initiative_setting";
jbe@57 123 DELETE FROM "suggestion_setting";
jbe@57 124 DELETE FROM "direct_voter" USING "issue"
jbe@57 125 WHERE "direct_voter"."issue_id" = "issue"."id"
jbe@57 126 AND "issue"."closed" ISNULL;
jbe@57 127 RETURN;
jbe@57 128 END;
jbe@57 129 $$;
jbe@57 130
jbe@57 131 COMMIT;

Impressum / About Us