liquid_feedback_core
diff core.sql @ 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 | 3fbf9e18ee52 |
children | b63515611a60 |
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;