liquid_feedback_core
view update/core-update.v1.3.0-v1.3.1.sql @ 102:2daa8ce3d743
Update script added
author | jbe |
---|---|
date | Tue Jan 04 02:55:01 2011 +0100 (2011-01-04) |
parents | |
children | bc8aa59b0945 |
line source
1 BEGIN;
3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
4 SELECT * FROM (VALUES ('1.3.1', 1, 3, 1))
5 AS "subquery"("string", "major", "minor", "revision");
7 ALTER TABLE "member" ADD COLUMN "locked" BOOLEAN NOT NULL DEFAULT FALSE;
9 COMMENT ON COLUMN "member"."locked" IS 'Locked members can not log in.';
10 COMMENT ON COLUMN "member"."active" IS 'Memberships, support and votes are taken into account when corresponding members are marked as active. When the user does not log in for an extended period of time, this flag may be set to FALSE. If the user is not locked, he/she may reset the active flag by logging in.';
12 CREATE OR REPLACE FUNCTION "create_interest_snapshot"
13 ( "issue_id_p" "issue"."id"%TYPE )
14 RETURNS VOID
15 LANGUAGE 'plpgsql' VOLATILE AS $$
16 DECLARE
17 "member_id_v" "member"."id"%TYPE;
18 BEGIN
19 DELETE FROM "direct_interest_snapshot"
20 WHERE "issue_id" = "issue_id_p"
21 AND "event" = 'periodic';
22 DELETE FROM "delegating_interest_snapshot"
23 WHERE "issue_id" = "issue_id_p"
24 AND "event" = 'periodic';
25 DELETE FROM "direct_supporter_snapshot"
26 WHERE "issue_id" = "issue_id_p"
27 AND "event" = 'periodic';
28 INSERT INTO "direct_interest_snapshot"
29 ("issue_id", "event", "member_id", "voting_requested")
30 SELECT
31 "issue_id_p" AS "issue_id",
32 'periodic' AS "event",
33 "member"."id" AS "member_id",
34 "interest"."voting_requested"
35 FROM "interest" JOIN "member"
36 ON "interest"."member_id" = "member"."id"
37 WHERE "interest"."issue_id" = "issue_id_p"
38 AND "member"."active";
39 FOR "member_id_v" IN
40 SELECT "member_id" FROM "direct_interest_snapshot"
41 WHERE "issue_id" = "issue_id_p"
42 AND "event" = 'periodic'
43 LOOP
44 UPDATE "direct_interest_snapshot" SET
45 "weight" = 1 +
46 "weight_of_added_delegations_for_interest_snapshot"(
47 "issue_id_p",
48 "member_id_v",
49 '{}'
50 )
51 WHERE "issue_id" = "issue_id_p"
52 AND "event" = 'periodic'
53 AND "member_id" = "member_id_v";
54 END LOOP;
55 INSERT INTO "direct_supporter_snapshot"
56 ( "issue_id", "initiative_id", "event", "member_id",
57 "informed", "satisfied" )
58 SELECT
59 "issue_id_p" AS "issue_id",
60 "initiative"."id" AS "initiative_id",
61 'periodic' AS "event",
62 "supporter"."member_id" AS "member_id",
63 "supporter"."draft_id" = "current_draft"."id" AS "informed",
64 NOT EXISTS (
65 SELECT NULL FROM "critical_opinion"
66 WHERE "initiative_id" = "initiative"."id"
67 AND "member_id" = "supporter"."member_id"
68 ) AS "satisfied"
69 FROM "initiative"
70 JOIN "supporter"
71 ON "supporter"."initiative_id" = "initiative"."id"
72 JOIN "current_draft"
73 ON "initiative"."id" = "current_draft"."initiative_id"
74 JOIN "direct_interest_snapshot"
75 ON "supporter"."member_id" = "direct_interest_snapshot"."member_id"
76 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
77 AND "event" = 'periodic'
78 WHERE "initiative"."issue_id" = "issue_id_p";
79 RETURN;
80 END;
81 $$;
83 COMMIT;