# HG changeset patch # User jbe # Date 1277730486 -7200 # Node ID 964cab0880ce897d6c726b8388f5c3c2fff004f6 # Parent c391eb3cd0402ff9da8bcc075802803a055ae0fe Removed column "interest_exists" from table "direct_population_snapshot"; Replaced function "delete_member_data" by function "delete_member" diff -r c391eb3cd040 -r 964cab0880ce core.sql --- a/core.sql Thu Jun 17 23:12:34 2010 +0200 +++ b/core.sql Mon Jun 28 15:08:06 2010 +0200 @@ -6,7 +6,7 @@ BEGIN; CREATE VIEW "liquid_feedback_version" AS - SELECT * FROM (VALUES ('1.0.3', 1, 0, 3)) + SELECT * FROM (VALUES ('1.1.0', 1, 1, 0)) AS "subquery"("string", "major", "minor", "revision"); @@ -648,15 +648,13 @@ "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE, "event" "snapshot_event", "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT, - "weight" INT4, - "interest_exists" BOOLEAN NOT NULL ); + "weight" INT4 ); CREATE INDEX "direct_population_snapshot_member_id_idx" ON "direct_population_snapshot" ("member_id"); COMMENT ON TABLE "direct_population_snapshot" IS 'Snapshot of active members having either a "membership" in the "area" or an "interest" in the "issue"'; COMMENT ON COLUMN "direct_population_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details'; COMMENT ON COLUMN "direct_population_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_population_snapshot"'; -COMMENT ON COLUMN "direct_population_snapshot"."interest_exists" IS 'TRUE if entry is due to interest in issue, FALSE if entry is only due to membership in area'; CREATE TABLE "delegating_population_snapshot" ( @@ -2047,35 +2045,26 @@ WHERE "issue_id" = "issue_id_p" AND "event" = 'periodic'; INSERT INTO "direct_population_snapshot" - ("issue_id", "event", "member_id", "interest_exists") - SELECT DISTINCT ON ("issue_id", "member_id") - "issue_id_p" AS "issue_id", - 'periodic' AS "event", - "subquery"."member_id", - "subquery"."interest_exists" - FROM ( - SELECT - "member"."id" AS "member_id", - FALSE AS "interest_exists" - FROM "issue" - JOIN "area" ON "issue"."area_id" = "area"."id" - JOIN "membership" ON "area"."id" = "membership"."area_id" - JOIN "member" ON "membership"."member_id" = "member"."id" - WHERE "issue"."id" = "issue_id_p" - AND "member"."active" - UNION - SELECT - "member"."id" AS "member_id", - TRUE AS "interest_exists" - FROM "interest" JOIN "member" - ON "interest"."member_id" = "member"."id" - WHERE "interest"."issue_id" = "issue_id_p" - AND "member"."active" - ) AS "subquery" - ORDER BY - "issue_id_p", - "subquery"."member_id", - "subquery"."interest_exists" DESC; + ("issue_id", "event", "member_id") + SELECT + "issue_id_p" AS "issue_id", + 'periodic'::"snapshot_event" AS "event", + "member"."id" AS "member_id" + FROM "issue" + JOIN "area" ON "issue"."area_id" = "area"."id" + JOIN "membership" ON "area"."id" = "membership"."area_id" + JOIN "member" ON "membership"."member_id" = "member"."id" + WHERE "issue"."id" = "issue_id_p" + AND "member"."active" + UNION + SELECT + "issue_id_p" AS "issue_id", + 'periodic'::"snapshot_event" AS "event", + "member"."id" AS "member_id" + FROM "interest" JOIN "member" + ON "interest"."member_id" = "member"."id" + WHERE "interest"."issue_id" = "issue_id_p" + AND "member"."active"; FOR "member_id_v" IN SELECT "member_id" FROM "direct_population_snapshot" WHERE "issue_id" = "issue_id_p" @@ -3188,13 +3177,14 @@ ------------------------------ -CREATE FUNCTION "delete_member_data"("member_id_p" "member"."id"%TYPE) +CREATE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE) RETURNS VOID LANGUAGE 'plpgsql' VOLATILE AS $$ BEGIN UPDATE "member" SET "login" = NULL, "password" = NULL, + "active" = FALSE, "notify_email" = NULL, "notify_email_unconfirmed" = NULL, "notify_email_secret" = NULL, @@ -3224,17 +3214,17 @@ DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p"; DELETE FROM "member_image" WHERE "member_id" = "member_id_p"; DELETE FROM "contact" WHERE "member_id" = "member_id_p"; - DELETE FROM "delegation" WHERE "truster_id" = "member_id_p"; - DELETE FROM "membership" WHERE "member_id" = "member_id_p"; DELETE FROM "area_setting" WHERE "member_id" = "member_id_p"; DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p"; DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p"; DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p"; + DELETE FROM "membership" WHERE "member_id" = "member_id_p"; + DELETE FROM "delegation" WHERE "truster_id" = "member_id_p"; RETURN; END; $$; -COMMENT ON FUNCTION "delete_member_data"("member_id_p" "member"."id"%TYPE) IS 'Clear certain settings and data of a particular member (data protection)'; +COMMENT ON FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE) IS 'Clear certain settings and data of a particular member (data protection)'; CREATE FUNCTION "delete_private_data"() @@ -3266,17 +3256,17 @@ "statement" = NULL; -- "text_search_data" is updated by triggers UPDATE "member_history" SET "login" = NULL; + DELETE FROM "invite_code"; DELETE FROM "setting"; DELETE FROM "setting_map"; DELETE FROM "member_relation_setting"; DELETE FROM "member_image"; DELETE FROM "contact"; + DELETE FROM "session"; DELETE FROM "area_setting"; DELETE FROM "issue_setting"; DELETE FROM "initiative_setting"; DELETE FROM "suggestion_setting"; - DELETE FROM "invite_code"; - DELETE FROM "session"; DELETE FROM "direct_voter" USING "issue" WHERE "direct_voter"."issue_id" = "issue"."id" AND "issue"."closed" ISNULL; diff -r c391eb3cd040 -r 964cab0880ce update/core-update.v1.0.3-v1.1.0.sql --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/update/core-update.v1.0.3-v1.1.0.sql Mon Jun 28 15:08:06 2010 +0200 @@ -0,0 +1,161 @@ +BEGIN; + +CREATE OR REPLACE VIEW "liquid_feedback_version" AS + SELECT * FROM (VALUES ('1.1.0', 1, 1, 0)) + AS "subquery"("string", "major", "minor", "revision"); + +ALTER TABLE "direct_population_snapshot" DROP COLUMN "interest_exists"; + +CREATE OR REPLACE FUNCTION "create_population_snapshot" + ( "issue_id_p" "issue"."id"%TYPE ) + RETURNS VOID + LANGUAGE 'plpgsql' VOLATILE AS $$ + DECLARE + "member_id_v" "member"."id"%TYPE; + BEGIN + DELETE FROM "direct_population_snapshot" + WHERE "issue_id" = "issue_id_p" + AND "event" = 'periodic'; + DELETE FROM "delegating_population_snapshot" + WHERE "issue_id" = "issue_id_p" + AND "event" = 'periodic'; + INSERT INTO "direct_population_snapshot" + ("issue_id", "event", "member_id") + SELECT + "issue_id_p" AS "issue_id", + 'periodic'::"snapshot_event" AS "event", + "member"."id" AS "member_id" + FROM "issue" + JOIN "area" ON "issue"."area_id" = "area"."id" + JOIN "membership" ON "area"."id" = "membership"."area_id" + JOIN "member" ON "membership"."member_id" = "member"."id" + WHERE "issue"."id" = "issue_id_p" + AND "member"."active" + UNION + SELECT + "issue_id_p" AS "issue_id", + 'periodic'::"snapshot_event" AS "event", + "member"."id" AS "member_id" + FROM "interest" JOIN "member" + ON "interest"."member_id" = "member"."id" + WHERE "interest"."issue_id" = "issue_id_p" + AND "member"."active"; + FOR "member_id_v" IN + SELECT "member_id" FROM "direct_population_snapshot" + WHERE "issue_id" = "issue_id_p" + AND "event" = 'periodic' + LOOP + UPDATE "direct_population_snapshot" SET + "weight" = 1 + + "weight_of_added_delegations_for_population_snapshot"( + "issue_id_p", + "member_id_v", + '{}' + ) + WHERE "issue_id" = "issue_id_p" + AND "event" = 'periodic' + AND "member_id" = "member_id_v"; + END LOOP; + RETURN; + END; + $$; + +DROP FUNCTION "delete_member_data"("member"."id"%TYPE); + +CREATE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE) + RETURNS VOID + LANGUAGE 'plpgsql' VOLATILE AS $$ + BEGIN + UPDATE "member" SET + "login" = NULL, + "password" = NULL, + "active" = FALSE, + "notify_email" = NULL, + "notify_email_unconfirmed" = NULL, + "notify_email_secret" = NULL, + "notify_email_secret_expiry" = NULL, + "password_reset_secret" = NULL, + "password_reset_secret_expiry" = NULL, + "organizational_unit" = NULL, + "internal_posts" = NULL, + "realname" = NULL, + "birthday" = NULL, + "address" = NULL, + "email" = NULL, + "xmpp_address" = NULL, + "website" = NULL, + "phone" = NULL, + "mobile_phone" = NULL, + "profession" = NULL, + "external_memberships" = NULL, + "external_posts" = NULL, + "statement" = NULL + WHERE "id" = "member_id_p"; + -- "text_search_data" is updated by triggers + UPDATE "member_history" SET "login" = NULL + WHERE "member_id" = "member_id_p"; + DELETE FROM "setting" WHERE "member_id" = "member_id_p"; + DELETE FROM "setting_map" WHERE "member_id" = "member_id_p"; + DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p"; + DELETE FROM "member_image" WHERE "member_id" = "member_id_p"; + DELETE FROM "contact" WHERE "member_id" = "member_id_p"; + DELETE FROM "area_setting" WHERE "member_id" = "member_id_p"; + DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p"; + DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p"; + DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p"; + DELETE FROM "membership" WHERE "member_id" = "member_id_p"; + DELETE FROM "delegation" WHERE "truster_id" = "member_id_p"; + RETURN; + END; + $$; + +COMMENT ON FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE) IS 'Clear certain settings and data of a particular member (data protection)'; + +CREATE OR REPLACE FUNCTION "delete_private_data"() + RETURNS VOID + LANGUAGE 'plpgsql' VOLATILE AS $$ + BEGIN + UPDATE "member" SET + "login" = NULL, + "password" = NULL, + "notify_email" = NULL, + "notify_email_unconfirmed" = NULL, + "notify_email_secret" = NULL, + "notify_email_secret_expiry" = NULL, + "password_reset_secret" = NULL, + "password_reset_secret_expiry" = NULL, + "organizational_unit" = NULL, + "internal_posts" = NULL, + "realname" = NULL, + "birthday" = NULL, + "address" = NULL, + "email" = NULL, + "xmpp_address" = NULL, + "website" = NULL, + "phone" = NULL, + "mobile_phone" = NULL, + "profession" = NULL, + "external_memberships" = NULL, + "external_posts" = NULL, + "statement" = NULL; + -- "text_search_data" is updated by triggers + UPDATE "member_history" SET "login" = NULL; + DELETE FROM "invite_code"; + DELETE FROM "setting"; + DELETE FROM "setting_map"; + DELETE FROM "member_relation_setting"; + DELETE FROM "member_image"; + DELETE FROM "contact"; + DELETE FROM "session"; + DELETE FROM "area_setting"; + DELETE FROM "issue_setting"; + DELETE FROM "initiative_setting"; + DELETE FROM "suggestion_setting"; + DELETE FROM "direct_voter" USING "issue" + WHERE "direct_voter"."issue_id" = "issue"."id" + AND "issue"."closed" ISNULL; + RETURN; + END; + $$; + +COMMIT;