liquid_feedback_core

annotate update/core-update.v1.0.3-v1.1.0.sql @ 54:964cab0880ce

find changesets by author, revision, files, or words in the commit message
Removed column "interest_exists" from table "direct_population_snapshot"; Replaced function "delete_member_data" by function "delete_member"
author jbe
date Mon Jun 28 15:08:06 2010 +0200 (2010-06-28)
parents
children b63515611a60
rev   line source
jbe@54 1 BEGIN;
jbe@54 2
jbe@54 3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
jbe@54 4 SELECT * FROM (VALUES ('1.1.0', 1, 1, 0))
jbe@54 5 AS "subquery"("string", "major", "minor", "revision");
jbe@54 6
jbe@54 7 ALTER TABLE "direct_population_snapshot" DROP COLUMN "interest_exists";
jbe@54 8
jbe@54 9 CREATE OR REPLACE FUNCTION "create_population_snapshot"
jbe@54 10 ( "issue_id_p" "issue"."id"%TYPE )
jbe@54 11 RETURNS VOID
jbe@54 12 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@54 13 DECLARE
jbe@54 14 "member_id_v" "member"."id"%TYPE;
jbe@54 15 BEGIN
jbe@54 16 DELETE FROM "direct_population_snapshot"
jbe@54 17 WHERE "issue_id" = "issue_id_p"
jbe@54 18 AND "event" = 'periodic';
jbe@54 19 DELETE FROM "delegating_population_snapshot"
jbe@54 20 WHERE "issue_id" = "issue_id_p"
jbe@54 21 AND "event" = 'periodic';
jbe@54 22 INSERT INTO "direct_population_snapshot"
jbe@54 23 ("issue_id", "event", "member_id")
jbe@54 24 SELECT
jbe@54 25 "issue_id_p" AS "issue_id",
jbe@54 26 'periodic'::"snapshot_event" AS "event",
jbe@54 27 "member"."id" AS "member_id"
jbe@54 28 FROM "issue"
jbe@54 29 JOIN "area" ON "issue"."area_id" = "area"."id"
jbe@54 30 JOIN "membership" ON "area"."id" = "membership"."area_id"
jbe@54 31 JOIN "member" ON "membership"."member_id" = "member"."id"
jbe@54 32 WHERE "issue"."id" = "issue_id_p"
jbe@54 33 AND "member"."active"
jbe@54 34 UNION
jbe@54 35 SELECT
jbe@54 36 "issue_id_p" AS "issue_id",
jbe@54 37 'periodic'::"snapshot_event" AS "event",
jbe@54 38 "member"."id" AS "member_id"
jbe@54 39 FROM "interest" JOIN "member"
jbe@54 40 ON "interest"."member_id" = "member"."id"
jbe@54 41 WHERE "interest"."issue_id" = "issue_id_p"
jbe@54 42 AND "member"."active";
jbe@54 43 FOR "member_id_v" IN
jbe@54 44 SELECT "member_id" FROM "direct_population_snapshot"
jbe@54 45 WHERE "issue_id" = "issue_id_p"
jbe@54 46 AND "event" = 'periodic'
jbe@54 47 LOOP
jbe@54 48 UPDATE "direct_population_snapshot" SET
jbe@54 49 "weight" = 1 +
jbe@54 50 "weight_of_added_delegations_for_population_snapshot"(
jbe@54 51 "issue_id_p",
jbe@54 52 "member_id_v",
jbe@54 53 '{}'
jbe@54 54 )
jbe@54 55 WHERE "issue_id" = "issue_id_p"
jbe@54 56 AND "event" = 'periodic'
jbe@54 57 AND "member_id" = "member_id_v";
jbe@54 58 END LOOP;
jbe@54 59 RETURN;
jbe@54 60 END;
jbe@54 61 $$;
jbe@54 62
jbe@54 63 DROP FUNCTION "delete_member_data"("member"."id"%TYPE);
jbe@54 64
jbe@54 65 CREATE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)
jbe@54 66 RETURNS VOID
jbe@54 67 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@54 68 BEGIN
jbe@54 69 UPDATE "member" SET
jbe@54 70 "login" = NULL,
jbe@54 71 "password" = NULL,
jbe@54 72 "active" = FALSE,
jbe@54 73 "notify_email" = NULL,
jbe@54 74 "notify_email_unconfirmed" = NULL,
jbe@54 75 "notify_email_secret" = NULL,
jbe@54 76 "notify_email_secret_expiry" = NULL,
jbe@54 77 "password_reset_secret" = NULL,
jbe@54 78 "password_reset_secret_expiry" = NULL,
jbe@54 79 "organizational_unit" = NULL,
jbe@54 80 "internal_posts" = NULL,
jbe@54 81 "realname" = NULL,
jbe@54 82 "birthday" = NULL,
jbe@54 83 "address" = NULL,
jbe@54 84 "email" = NULL,
jbe@54 85 "xmpp_address" = NULL,
jbe@54 86 "website" = NULL,
jbe@54 87 "phone" = NULL,
jbe@54 88 "mobile_phone" = NULL,
jbe@54 89 "profession" = NULL,
jbe@54 90 "external_memberships" = NULL,
jbe@54 91 "external_posts" = NULL,
jbe@54 92 "statement" = NULL
jbe@54 93 WHERE "id" = "member_id_p";
jbe@54 94 -- "text_search_data" is updated by triggers
jbe@54 95 UPDATE "member_history" SET "login" = NULL
jbe@54 96 WHERE "member_id" = "member_id_p";
jbe@54 97 DELETE FROM "setting" WHERE "member_id" = "member_id_p";
jbe@54 98 DELETE FROM "setting_map" WHERE "member_id" = "member_id_p";
jbe@54 99 DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p";
jbe@54 100 DELETE FROM "member_image" WHERE "member_id" = "member_id_p";
jbe@54 101 DELETE FROM "contact" WHERE "member_id" = "member_id_p";
jbe@54 102 DELETE FROM "area_setting" WHERE "member_id" = "member_id_p";
jbe@54 103 DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p";
jbe@54 104 DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p";
jbe@54 105 DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p";
jbe@54 106 DELETE FROM "membership" WHERE "member_id" = "member_id_p";
jbe@54 107 DELETE FROM "delegation" WHERE "truster_id" = "member_id_p";
jbe@54 108 RETURN;
jbe@54 109 END;
jbe@54 110 $$;
jbe@54 111
jbe@54 112 COMMENT ON FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE) IS 'Clear certain settings and data of a particular member (data protection)';
jbe@54 113
jbe@54 114 CREATE OR REPLACE FUNCTION "delete_private_data"()
jbe@54 115 RETURNS VOID
jbe@54 116 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@54 117 BEGIN
jbe@54 118 UPDATE "member" SET
jbe@54 119 "login" = NULL,
jbe@54 120 "password" = NULL,
jbe@54 121 "notify_email" = NULL,
jbe@54 122 "notify_email_unconfirmed" = NULL,
jbe@54 123 "notify_email_secret" = NULL,
jbe@54 124 "notify_email_secret_expiry" = NULL,
jbe@54 125 "password_reset_secret" = NULL,
jbe@54 126 "password_reset_secret_expiry" = NULL,
jbe@54 127 "organizational_unit" = NULL,
jbe@54 128 "internal_posts" = NULL,
jbe@54 129 "realname" = NULL,
jbe@54 130 "birthday" = NULL,
jbe@54 131 "address" = NULL,
jbe@54 132 "email" = NULL,
jbe@54 133 "xmpp_address" = NULL,
jbe@54 134 "website" = NULL,
jbe@54 135 "phone" = NULL,
jbe@54 136 "mobile_phone" = NULL,
jbe@54 137 "profession" = NULL,
jbe@54 138 "external_memberships" = NULL,
jbe@54 139 "external_posts" = NULL,
jbe@54 140 "statement" = NULL;
jbe@54 141 -- "text_search_data" is updated by triggers
jbe@54 142 UPDATE "member_history" SET "login" = NULL;
jbe@54 143 DELETE FROM "invite_code";
jbe@54 144 DELETE FROM "setting";
jbe@54 145 DELETE FROM "setting_map";
jbe@54 146 DELETE FROM "member_relation_setting";
jbe@54 147 DELETE FROM "member_image";
jbe@54 148 DELETE FROM "contact";
jbe@54 149 DELETE FROM "session";
jbe@54 150 DELETE FROM "area_setting";
jbe@54 151 DELETE FROM "issue_setting";
jbe@54 152 DELETE FROM "initiative_setting";
jbe@54 153 DELETE FROM "suggestion_setting";
jbe@54 154 DELETE FROM "direct_voter" USING "issue"
jbe@54 155 WHERE "direct_voter"."issue_id" = "issue"."id"
jbe@54 156 AND "issue"."closed" ISNULL;
jbe@54 157 RETURN;
jbe@54 158 END;
jbe@54 159 $$;
jbe@54 160
jbe@54 161 COMMIT;

Impressum / About Us