liquid_feedback_core

annotate update/core-update.v1.1.0-v1.2.0.sql @ 78:4fc1779a9c98

find changesets by author, revision, files, or words in the commit message
add test policies

the test policies have values that move them quickly to the target area for frontend development
author Daniel Poelzleithner <poelzi@poelzi.org>
date Mon Oct 04 14:24:29 2010 +0200 (2010-10-04)
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