liquid_feedback_core
changeset 54:964cab0880ce
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 | c391eb3cd040 |
children | b63515611a60 |
files | core.sql update/core-update.v1.0.3-v1.1.0.sql |
line diff
1.1 --- a/core.sql Thu Jun 17 23:12:34 2010 +0200 1.2 +++ b/core.sql Mon Jun 28 15:08:06 2010 +0200 1.3 @@ -6,7 +6,7 @@ 1.4 BEGIN; 1.5 1.6 CREATE VIEW "liquid_feedback_version" AS 1.7 - SELECT * FROM (VALUES ('1.0.3', 1, 0, 3)) 1.8 + SELECT * FROM (VALUES ('1.1.0', 1, 1, 0)) 1.9 AS "subquery"("string", "major", "minor", "revision"); 1.10 1.11 1.12 @@ -648,15 +648,13 @@ 1.13 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.14 "event" "snapshot_event", 1.15 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT, 1.16 - "weight" INT4, 1.17 - "interest_exists" BOOLEAN NOT NULL ); 1.18 + "weight" INT4 ); 1.19 CREATE INDEX "direct_population_snapshot_member_id_idx" ON "direct_population_snapshot" ("member_id"); 1.20 1.21 COMMENT ON TABLE "direct_population_snapshot" IS 'Snapshot of active members having either a "membership" in the "area" or an "interest" in the "issue"'; 1.22 1.23 COMMENT ON COLUMN "direct_population_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details'; 1.24 COMMENT ON COLUMN "direct_population_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_population_snapshot"'; 1.25 -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'; 1.26 1.27 1.28 CREATE TABLE "delegating_population_snapshot" ( 1.29 @@ -2047,35 +2045,26 @@ 1.30 WHERE "issue_id" = "issue_id_p" 1.31 AND "event" = 'periodic'; 1.32 INSERT INTO "direct_population_snapshot" 1.33 - ("issue_id", "event", "member_id", "interest_exists") 1.34 - SELECT DISTINCT ON ("issue_id", "member_id") 1.35 - "issue_id_p" AS "issue_id", 1.36 - 'periodic' AS "event", 1.37 - "subquery"."member_id", 1.38 - "subquery"."interest_exists" 1.39 - FROM ( 1.40 - SELECT 1.41 - "member"."id" AS "member_id", 1.42 - FALSE AS "interest_exists" 1.43 - FROM "issue" 1.44 - JOIN "area" ON "issue"."area_id" = "area"."id" 1.45 - JOIN "membership" ON "area"."id" = "membership"."area_id" 1.46 - JOIN "member" ON "membership"."member_id" = "member"."id" 1.47 - WHERE "issue"."id" = "issue_id_p" 1.48 - AND "member"."active" 1.49 - UNION 1.50 - SELECT 1.51 - "member"."id" AS "member_id", 1.52 - TRUE AS "interest_exists" 1.53 - FROM "interest" JOIN "member" 1.54 - ON "interest"."member_id" = "member"."id" 1.55 - WHERE "interest"."issue_id" = "issue_id_p" 1.56 - AND "member"."active" 1.57 - ) AS "subquery" 1.58 - ORDER BY 1.59 - "issue_id_p", 1.60 - "subquery"."member_id", 1.61 - "subquery"."interest_exists" DESC; 1.62 + ("issue_id", "event", "member_id") 1.63 + SELECT 1.64 + "issue_id_p" AS "issue_id", 1.65 + 'periodic'::"snapshot_event" AS "event", 1.66 + "member"."id" AS "member_id" 1.67 + FROM "issue" 1.68 + JOIN "area" ON "issue"."area_id" = "area"."id" 1.69 + JOIN "membership" ON "area"."id" = "membership"."area_id" 1.70 + JOIN "member" ON "membership"."member_id" = "member"."id" 1.71 + WHERE "issue"."id" = "issue_id_p" 1.72 + AND "member"."active" 1.73 + UNION 1.74 + SELECT 1.75 + "issue_id_p" AS "issue_id", 1.76 + 'periodic'::"snapshot_event" AS "event", 1.77 + "member"."id" AS "member_id" 1.78 + FROM "interest" JOIN "member" 1.79 + ON "interest"."member_id" = "member"."id" 1.80 + WHERE "interest"."issue_id" = "issue_id_p" 1.81 + AND "member"."active"; 1.82 FOR "member_id_v" IN 1.83 SELECT "member_id" FROM "direct_population_snapshot" 1.84 WHERE "issue_id" = "issue_id_p" 1.85 @@ -3188,13 +3177,14 @@ 1.86 ------------------------------ 1.87 1.88 1.89 -CREATE FUNCTION "delete_member_data"("member_id_p" "member"."id"%TYPE) 1.90 +CREATE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE) 1.91 RETURNS VOID 1.92 LANGUAGE 'plpgsql' VOLATILE AS $$ 1.93 BEGIN 1.94 UPDATE "member" SET 1.95 "login" = NULL, 1.96 "password" = NULL, 1.97 + "active" = FALSE, 1.98 "notify_email" = NULL, 1.99 "notify_email_unconfirmed" = NULL, 1.100 "notify_email_secret" = NULL, 1.101 @@ -3224,17 +3214,17 @@ 1.102 DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p"; 1.103 DELETE FROM "member_image" WHERE "member_id" = "member_id_p"; 1.104 DELETE FROM "contact" WHERE "member_id" = "member_id_p"; 1.105 - DELETE FROM "delegation" WHERE "truster_id" = "member_id_p"; 1.106 - DELETE FROM "membership" WHERE "member_id" = "member_id_p"; 1.107 DELETE FROM "area_setting" WHERE "member_id" = "member_id_p"; 1.108 DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p"; 1.109 DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p"; 1.110 DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p"; 1.111 + DELETE FROM "membership" WHERE "member_id" = "member_id_p"; 1.112 + DELETE FROM "delegation" WHERE "truster_id" = "member_id_p"; 1.113 RETURN; 1.114 END; 1.115 $$; 1.116 1.117 -COMMENT ON FUNCTION "delete_member_data"("member_id_p" "member"."id"%TYPE) IS 'Clear certain settings and data of a particular member (data protection)'; 1.118 +COMMENT ON FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE) IS 'Clear certain settings and data of a particular member (data protection)'; 1.119 1.120 1.121 CREATE FUNCTION "delete_private_data"() 1.122 @@ -3266,17 +3256,17 @@ 1.123 "statement" = NULL; 1.124 -- "text_search_data" is updated by triggers 1.125 UPDATE "member_history" SET "login" = NULL; 1.126 + DELETE FROM "invite_code"; 1.127 DELETE FROM "setting"; 1.128 DELETE FROM "setting_map"; 1.129 DELETE FROM "member_relation_setting"; 1.130 DELETE FROM "member_image"; 1.131 DELETE FROM "contact"; 1.132 + DELETE FROM "session"; 1.133 DELETE FROM "area_setting"; 1.134 DELETE FROM "issue_setting"; 1.135 DELETE FROM "initiative_setting"; 1.136 DELETE FROM "suggestion_setting"; 1.137 - DELETE FROM "invite_code"; 1.138 - DELETE FROM "session"; 1.139 DELETE FROM "direct_voter" USING "issue" 1.140 WHERE "direct_voter"."issue_id" = "issue"."id" 1.141 AND "issue"."closed" ISNULL;
2.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000 2.2 +++ b/update/core-update.v1.0.3-v1.1.0.sql Mon Jun 28 15:08:06 2010 +0200 2.3 @@ -0,0 +1,161 @@ 2.4 +BEGIN; 2.5 + 2.6 +CREATE OR REPLACE VIEW "liquid_feedback_version" AS 2.7 + SELECT * FROM (VALUES ('1.1.0', 1, 1, 0)) 2.8 + AS "subquery"("string", "major", "minor", "revision"); 2.9 + 2.10 +ALTER TABLE "direct_population_snapshot" DROP COLUMN "interest_exists"; 2.11 + 2.12 +CREATE OR REPLACE FUNCTION "create_population_snapshot" 2.13 + ( "issue_id_p" "issue"."id"%TYPE ) 2.14 + RETURNS VOID 2.15 + LANGUAGE 'plpgsql' VOLATILE AS $$ 2.16 + DECLARE 2.17 + "member_id_v" "member"."id"%TYPE; 2.18 + BEGIN 2.19 + DELETE FROM "direct_population_snapshot" 2.20 + WHERE "issue_id" = "issue_id_p" 2.21 + AND "event" = 'periodic'; 2.22 + DELETE FROM "delegating_population_snapshot" 2.23 + WHERE "issue_id" = "issue_id_p" 2.24 + AND "event" = 'periodic'; 2.25 + INSERT INTO "direct_population_snapshot" 2.26 + ("issue_id", "event", "member_id") 2.27 + SELECT 2.28 + "issue_id_p" AS "issue_id", 2.29 + 'periodic'::"snapshot_event" AS "event", 2.30 + "member"."id" AS "member_id" 2.31 + FROM "issue" 2.32 + JOIN "area" ON "issue"."area_id" = "area"."id" 2.33 + JOIN "membership" ON "area"."id" = "membership"."area_id" 2.34 + JOIN "member" ON "membership"."member_id" = "member"."id" 2.35 + WHERE "issue"."id" = "issue_id_p" 2.36 + AND "member"."active" 2.37 + UNION 2.38 + SELECT 2.39 + "issue_id_p" AS "issue_id", 2.40 + 'periodic'::"snapshot_event" AS "event", 2.41 + "member"."id" AS "member_id" 2.42 + FROM "interest" JOIN "member" 2.43 + ON "interest"."member_id" = "member"."id" 2.44 + WHERE "interest"."issue_id" = "issue_id_p" 2.45 + AND "member"."active"; 2.46 + FOR "member_id_v" IN 2.47 + SELECT "member_id" FROM "direct_population_snapshot" 2.48 + WHERE "issue_id" = "issue_id_p" 2.49 + AND "event" = 'periodic' 2.50 + LOOP 2.51 + UPDATE "direct_population_snapshot" SET 2.52 + "weight" = 1 + 2.53 + "weight_of_added_delegations_for_population_snapshot"( 2.54 + "issue_id_p", 2.55 + "member_id_v", 2.56 + '{}' 2.57 + ) 2.58 + WHERE "issue_id" = "issue_id_p" 2.59 + AND "event" = 'periodic' 2.60 + AND "member_id" = "member_id_v"; 2.61 + END LOOP; 2.62 + RETURN; 2.63 + END; 2.64 + $$; 2.65 + 2.66 +DROP FUNCTION "delete_member_data"("member"."id"%TYPE); 2.67 + 2.68 +CREATE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE) 2.69 + RETURNS VOID 2.70 + LANGUAGE 'plpgsql' VOLATILE AS $$ 2.71 + BEGIN 2.72 + UPDATE "member" SET 2.73 + "login" = NULL, 2.74 + "password" = NULL, 2.75 + "active" = FALSE, 2.76 + "notify_email" = NULL, 2.77 + "notify_email_unconfirmed" = NULL, 2.78 + "notify_email_secret" = NULL, 2.79 + "notify_email_secret_expiry" = NULL, 2.80 + "password_reset_secret" = NULL, 2.81 + "password_reset_secret_expiry" = NULL, 2.82 + "organizational_unit" = NULL, 2.83 + "internal_posts" = NULL, 2.84 + "realname" = NULL, 2.85 + "birthday" = NULL, 2.86 + "address" = NULL, 2.87 + "email" = NULL, 2.88 + "xmpp_address" = NULL, 2.89 + "website" = NULL, 2.90 + "phone" = NULL, 2.91 + "mobile_phone" = NULL, 2.92 + "profession" = NULL, 2.93 + "external_memberships" = NULL, 2.94 + "external_posts" = NULL, 2.95 + "statement" = NULL 2.96 + WHERE "id" = "member_id_p"; 2.97 + -- "text_search_data" is updated by triggers 2.98 + UPDATE "member_history" SET "login" = NULL 2.99 + WHERE "member_id" = "member_id_p"; 2.100 + DELETE FROM "setting" WHERE "member_id" = "member_id_p"; 2.101 + DELETE FROM "setting_map" WHERE "member_id" = "member_id_p"; 2.102 + DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p"; 2.103 + DELETE FROM "member_image" WHERE "member_id" = "member_id_p"; 2.104 + DELETE FROM "contact" WHERE "member_id" = "member_id_p"; 2.105 + DELETE FROM "area_setting" WHERE "member_id" = "member_id_p"; 2.106 + DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p"; 2.107 + DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p"; 2.108 + DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p"; 2.109 + DELETE FROM "membership" WHERE "member_id" = "member_id_p"; 2.110 + DELETE FROM "delegation" WHERE "truster_id" = "member_id_p"; 2.111 + RETURN; 2.112 + END; 2.113 + $$; 2.114 + 2.115 +COMMENT ON FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE) IS 'Clear certain settings and data of a particular member (data protection)'; 2.116 + 2.117 +CREATE OR REPLACE FUNCTION "delete_private_data"() 2.118 + RETURNS VOID 2.119 + LANGUAGE 'plpgsql' VOLATILE AS $$ 2.120 + BEGIN 2.121 + UPDATE "member" SET 2.122 + "login" = NULL, 2.123 + "password" = NULL, 2.124 + "notify_email" = NULL, 2.125 + "notify_email_unconfirmed" = NULL, 2.126 + "notify_email_secret" = NULL, 2.127 + "notify_email_secret_expiry" = NULL, 2.128 + "password_reset_secret" = NULL, 2.129 + "password_reset_secret_expiry" = NULL, 2.130 + "organizational_unit" = NULL, 2.131 + "internal_posts" = NULL, 2.132 + "realname" = NULL, 2.133 + "birthday" = NULL, 2.134 + "address" = NULL, 2.135 + "email" = NULL, 2.136 + "xmpp_address" = NULL, 2.137 + "website" = NULL, 2.138 + "phone" = NULL, 2.139 + "mobile_phone" = NULL, 2.140 + "profession" = NULL, 2.141 + "external_memberships" = NULL, 2.142 + "external_posts" = NULL, 2.143 + "statement" = NULL; 2.144 + -- "text_search_data" is updated by triggers 2.145 + UPDATE "member_history" SET "login" = NULL; 2.146 + DELETE FROM "invite_code"; 2.147 + DELETE FROM "setting"; 2.148 + DELETE FROM "setting_map"; 2.149 + DELETE FROM "member_relation_setting"; 2.150 + DELETE FROM "member_image"; 2.151 + DELETE FROM "contact"; 2.152 + DELETE FROM "session"; 2.153 + DELETE FROM "area_setting"; 2.154 + DELETE FROM "issue_setting"; 2.155 + DELETE FROM "initiative_setting"; 2.156 + DELETE FROM "suggestion_setting"; 2.157 + DELETE FROM "direct_voter" USING "issue" 2.158 + WHERE "direct_voter"."issue_id" = "issue"."id" 2.159 + AND "issue"."closed" ISNULL; 2.160 + RETURN; 2.161 + END; 2.162 + $$; 2.163 + 2.164 +COMMIT;