liquid_feedback_core

view update/core-update.beta26-v1.0.0.sql @ 142:54ac8c473263

find changesets by author, revision, files, or words in the commit message
Use an improved definition for "disqualified" initiatives

"initiative"."disqualified" is TRUE, if the initiative may not win, because it either (a) has no better rank than the status quo, or (b) because there exists a better ranked initiative X, which directly beats this initiative, and either more voters prefer X to this initiative than voters preferring X to the status quo or less voters prefer this initiative to X than voters preferring the status quo to X
author jbe
date Wed Jun 01 16:58:00 2011 +0200 (2011-06-01)
parents 5a01d558565b
children
line source
1 BEGIN;
2
3 DROP VIEW "liquid_feedback_version";
4 CREATE VIEW "liquid_feedback_version" AS
5 SELECT * FROM (VALUES ('1.0.0', 1, 0, 0))
6 AS "subquery"("string", "major", "minor", "revision");
7
8 ALTER TABLE "member" ALTER COLUMN "login" DROP NOT NULL;
9
10 ALTER TABLE "member_history" ALTER COLUMN "login" DROP NOT NULL;
11
12 CREATE INDEX "member_history_member_id_idx" ON "member_history" ("member_id");
13
14 ALTER TABLE "direct_population_snapshot" DROP
15 CONSTRAINT "direct_population_snapshot_member_id_fkey";
16 ALTER TABLE "direct_population_snapshot" ADD
17 CONSTRAINT "direct_population_snapshot_member_id_fkey"
18 FOREIGN KEY ("member_id")
19 REFERENCES "member"("id") ON DELETE RESTRICT ON UPDATE RESTRICT;
20
21 ALTER TABLE "delegating_population_snapshot" DROP
22 CONSTRAINT "delegating_population_snapshot_member_id_fkey";
23 ALTER TABLE "delegating_population_snapshot" ADD
24 CONSTRAINT "delegating_population_snapshot_member_id_fkey"
25 FOREIGN KEY ("member_id")
26 REFERENCES "member"("id") ON DELETE RESTRICT ON UPDATE RESTRICT;
27
28 ALTER TABLE "direct_interest_snapshot" DROP
29 CONSTRAINT "direct_interest_snapshot_member_id_fkey";
30 ALTER TABLE "direct_interest_snapshot" ADD
31 CONSTRAINT "direct_interest_snapshot_member_id_fkey"
32 FOREIGN KEY ("member_id")
33 REFERENCES "member"("id") ON DELETE RESTRICT ON UPDATE RESTRICT;
34
35 ALTER TABLE "delegating_interest_snapshot" DROP
36 CONSTRAINT "delegating_interest_snapshot_member_id_fkey";
37 ALTER TABLE "delegating_interest_snapshot" ADD
38 CONSTRAINT "delegating_interest_snapshot_member_id_fkey"
39 FOREIGN KEY ("member_id")
40 REFERENCES "member"("id") ON DELETE RESTRICT ON UPDATE RESTRICT;
41
42 ALTER TABLE "direct_supporter_snapshot" DROP
43 CONSTRAINT "direct_supporter_snapshot_member_id_fkey";
44 ALTER TABLE "direct_supporter_snapshot" ADD
45 CONSTRAINT "direct_supporter_snapshot_member_id_fkey"
46 FOREIGN KEY ("member_id")
47 REFERENCES "member"("id") ON DELETE RESTRICT ON UPDATE RESTRICT;
48
49 ALTER TABLE "direct_voter" DROP
50 CONSTRAINT "direct_voter_member_id_fkey";
51 ALTER TABLE "direct_voter" ADD
52 CONSTRAINT "direct_voter_member_id_fkey"
53 FOREIGN KEY ("member_id")
54 REFERENCES "member"("id") ON DELETE RESTRICT ON UPDATE RESTRICT;
55
56 ALTER TABLE "delegating_voter" DROP
57 CONSTRAINT "delegating_voter_member_id_fkey";
58 ALTER TABLE "delegating_voter" ADD
59 CONSTRAINT "delegating_voter_member_id_fkey"
60 FOREIGN KEY ("member_id")
61 REFERENCES "member"("id") ON DELETE RESTRICT ON UPDATE RESTRICT;
62
63 CREATE OR REPLACE FUNCTION "write_member_history_trigger"()
64 RETURNS TRIGGER
65 LANGUAGE 'plpgsql' VOLATILE AS $$
66 BEGIN
67 IF
68 ( NEW."login" NOTNULL AND OLD."login" NOTNULL AND
69 NEW."login" != OLD."login" ) OR
70 ( NEW."login" NOTNULL AND OLD."login" ISNULL ) OR
71 ( NEW."login" ISNULL AND OLD."login" NOTNULL ) OR
72 NEW."active" != OLD."active" OR
73 NEW."name" != OLD."name"
74 THEN
75 INSERT INTO "member_history"
76 ("member_id", "login", "active", "name")
77 VALUES (NEW."id", OLD."login", OLD."active", OLD."name");
78 END IF;
79 RETURN NULL;
80 END;
81 $$;
82
83 CREATE FUNCTION "delete_member_data"("member_id_p" "member"."id"%TYPE)
84 RETURNS VOID
85 LANGUAGE 'plpgsql' VOLATILE AS $$
86 BEGIN
87 UPDATE "member" SET
88 "login" = NULL,
89 "password" = NULL,
90 "notify_email" = NULL,
91 "notify_email_unconfirmed" = NULL,
92 "notify_email_secret" = NULL,
93 "notify_email_secret_expiry" = NULL,
94 "password_reset_secret" = NULL,
95 "password_reset_secret_expiry" = NULL,
96 "organizational_unit" = NULL,
97 "internal_posts" = NULL,
98 "realname" = NULL,
99 "birthday" = NULL,
100 "address" = NULL,
101 "email" = NULL,
102 "xmpp_address" = NULL,
103 "website" = NULL,
104 "phone" = NULL,
105 "mobile_phone" = NULL,
106 "profession" = NULL,
107 "external_memberships" = NULL,
108 "external_posts" = NULL,
109 "statement" = NULL
110 WHERE "id" = "member_id_p";
111 -- "text_search_data" is updated by triggers
112 UPDATE "member_history" SET "login" = NULL
113 WHERE "member_id" = "member_id_p";
114 DELETE FROM "setting" WHERE "member_id" = "member_id_p";
115 DELETE FROM "setting_map" WHERE "member_id" = "member_id_p";
116 DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p";
117 DELETE FROM "member_image" WHERE "member_id" = "member_id_p";
118 DELETE FROM "contact" WHERE "member_id" = "member_id_p";
119 DELETE FROM "area_setting" WHERE "member_id" = "member_id_p";
120 DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p";
121 DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p";
122 DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p";
123 RETURN;
124 END;
125 $$;
126 COMMENT ON FUNCTION "delete_member_data"("member_id_p" "member"."id"%TYPE) IS 'Clear certain settings and data of a particular member (data protection)';
127
128 CREATE OR REPLACE FUNCTION "delete_private_data"()
129 RETURNS VOID
130 LANGUAGE 'plpgsql' VOLATILE AS $$
131 BEGIN
132 PERFORM "delete_member_data"("id") FROM "member";
133 DELETE FROM "invite_code";
134 DELETE FROM "session";
135 DELETE FROM "direct_voter" USING "issue"
136 WHERE "direct_voter"."issue_id" = "issue"."id"
137 AND "issue"."closed" ISNULL;
138 RETURN;
139 END;
140 $$;
141
142 COMMIT;

Impressum / About Us