liquid_feedback_core

view update/core-update.v2.0.11-v2.1.0.sql @ 267:5bf2c973ec3c

Work on OAuth 2.0 authorization support
author jbe
date Wed Aug 08 15:20:34 2012 +0200 (2012-08-08)
parents 30465830ad9c
children a00b58b7a510
line source
1 BEGIN;
3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
4 SELECT * FROM (VALUES ('2.1.0', 2, 1, 0))
5 AS "subquery"("string", "major", "minor", "revision");
7 ALTER TABLE "policy" ADD COLUMN "polling" BOOLEAN NOT NULL DEFAULT FALSE;
8 ALTER TABLE "policy" ALTER COLUMN "admission_time" DROP NOT NULL;
9 ALTER TABLE "policy" ALTER COLUMN "discussion_time" DROP NOT NULL;
10 ALTER TABLE "policy" ALTER COLUMN "verification_time" DROP NOT NULL;
11 ALTER TABLE "policy" ALTER COLUMN "voting_time" DROP NOT NULL;
12 ALTER TABLE "policy" ADD CONSTRAINT "timing" CHECK (
13 ( "polling" = FALSE AND
14 "admission_time" NOTNULL AND "discussion_time" NOTNULL AND
15 "verification_time" NOTNULL AND "voting_time" NOTNULL ) OR
16 ( "polling" = TRUE AND
17 "admission_time" ISNULL AND "discussion_time" NOTNULL AND
18 "verification_time" NOTNULL AND "voting_time" NOTNULL ) OR
19 ( "polling" = TRUE AND
20 "admission_time" ISNULL AND "discussion_time" ISNULL AND
21 "verification_time" ISNULL AND "voting_time" ISNULL ) );
22 COMMENT ON COLUMN "policy"."polling" IS 'TRUE = special policy for non-user-generated issues, i.e. polls ("admission_time" MUST be set to NULL, the other timings may be set to NULL altogether, allowing individual timing for issues)';
24 ALTER TABLE "initiative" ADD COLUMN "polling" BOOLEAN NOT NULL DEFAULT FALSE;
25 COMMENT ON COLUMN "initiative"."polling" IS 'Initiative is an option for a poll (see "policy"."polling"), and does not need to pass the initiative quorum';
27 ALTER TABLE "privilege" RENAME COLUMN "voting_right_manager" TO "member_manager";
28 ALTER TABLE "privilege" ADD COLUMN "initiative_right" BOOLEAN NOT NULL DEFAULT TRUE;
29 ALTER TABLE "privilege" ADD COLUMN "polling_right" BOOLEAN NOT NULL DEFAULT FALSE;
30 UPDATE "privilege" SET "initiative_right" = "voting_right";
31 COMMENT ON COLUMN "privilege"."admin_manager" IS 'Grant/revoke any privileges to/from other members';
32 COMMENT ON COLUMN "privilege"."member_manager" IS 'Adding/removing members from the unit, granting or revoking "initiative_right" and "voting_right"';
33 COMMENT ON COLUMN "privilege"."initiative_right" IS 'Right to create an initiative';
34 COMMENT ON COLUMN "privilege"."voting_right" IS 'Right to support initiatives, create and rate suggestions, and to vote';
35 COMMENT ON COLUMN "privilege"."polling_right" IS 'Right to create polls (see "policy"."polling" and "initiative"."polling")';
37 DROP TABLE "rendered_issue_comment";
38 DROP TABLE "issue_comment";
40 CREATE OR REPLACE FUNCTION "freeze_after_snapshot"
41 ( "issue_id_p" "issue"."id"%TYPE )
42 RETURNS VOID
43 LANGUAGE 'plpgsql' VOLATILE AS $$
44 DECLARE
45 "issue_row" "issue"%ROWTYPE;
46 "policy_row" "policy"%ROWTYPE;
47 "initiative_row" "initiative"%ROWTYPE;
48 BEGIN
49 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
50 SELECT * INTO "policy_row"
51 FROM "policy" WHERE "id" = "issue_row"."policy_id";
52 PERFORM "set_snapshot_event"("issue_id_p", 'full_freeze');
53 FOR "initiative_row" IN
54 SELECT * FROM "initiative"
55 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
56 LOOP
57 IF
58 "initiative_row"."polling" OR (
59 "initiative_row"."satisfied_supporter_count" > 0 AND
60 "initiative_row"."satisfied_supporter_count" *
61 "policy_row"."initiative_quorum_den" >=
62 "issue_row"."population" * "policy_row"."initiative_quorum_num"
63 )
64 THEN
65 UPDATE "initiative" SET "admitted" = TRUE
66 WHERE "id" = "initiative_row"."id";
67 ELSE
68 UPDATE "initiative" SET "admitted" = FALSE
69 WHERE "id" = "initiative_row"."id";
70 END IF;
71 END LOOP;
72 IF EXISTS (
73 SELECT NULL FROM "initiative"
74 WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE
75 ) THEN
76 UPDATE "issue" SET
77 "state" = 'voting',
78 "accepted" = coalesce("accepted", now()),
79 "half_frozen" = coalesce("half_frozen", now()),
80 "fully_frozen" = now()
81 WHERE "id" = "issue_id_p";
82 ELSE
83 UPDATE "issue" SET
84 "state" = 'canceled_no_initiative_admitted',
85 "accepted" = coalesce("accepted", now()),
86 "half_frozen" = coalesce("half_frozen", now()),
87 "fully_frozen" = now(),
88 "closed" = now(),
89 "ranks_available" = TRUE
90 WHERE "id" = "issue_id_p";
91 -- NOTE: The following DELETE statements have effect only when
92 -- issue state has been manipulated
93 DELETE FROM "direct_voter" WHERE "issue_id" = "issue_id_p";
94 DELETE FROM "delegating_voter" WHERE "issue_id" = "issue_id_p";
95 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
96 END IF;
97 RETURN;
98 END;
99 $$;
101 CREATE OR REPLACE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE)
102 RETURNS VOID
103 LANGUAGE 'plpgsql' VOLATILE AS $$
104 DECLARE
105 "issue_row" "issue"%ROWTYPE;
106 BEGIN
107 SELECT * INTO "issue_row"
108 FROM "issue" WHERE "id" = "issue_id_p"
109 FOR UPDATE;
110 IF "issue_row"."cleaned" ISNULL THEN
111 UPDATE "issue" SET
112 "state" = 'voting',
113 "closed" = NULL,
114 "ranks_available" = FALSE
115 WHERE "id" = "issue_id_p";
116 DELETE FROM "voting_comment"
117 WHERE "issue_id" = "issue_id_p";
118 DELETE FROM "delegating_voter"
119 WHERE "issue_id" = "issue_id_p";
120 DELETE FROM "direct_voter"
121 WHERE "issue_id" = "issue_id_p";
122 DELETE FROM "delegating_interest_snapshot"
123 WHERE "issue_id" = "issue_id_p";
124 DELETE FROM "direct_interest_snapshot"
125 WHERE "issue_id" = "issue_id_p";
126 DELETE FROM "delegating_population_snapshot"
127 WHERE "issue_id" = "issue_id_p";
128 DELETE FROM "direct_population_snapshot"
129 WHERE "issue_id" = "issue_id_p";
130 DELETE FROM "non_voter"
131 WHERE "issue_id" = "issue_id_p";
132 DELETE FROM "delegation"
133 WHERE "issue_id" = "issue_id_p";
134 DELETE FROM "supporter"
135 WHERE "issue_id" = "issue_id_p";
136 UPDATE "issue" SET
137 "state" = "issue_row"."state",
138 "closed" = "issue_row"."closed",
139 "ranks_available" = "issue_row"."ranks_available",
140 "cleaned" = now()
141 WHERE "id" = "issue_id_p";
142 END IF;
143 RETURN;
144 END;
145 $$;
147 COMMIT;

Impressum / About Us