| rev |
line source |
|
jbe@205
|
1 BEGIN;
|
|
jbe@205
|
2
|
|
jbe@212
|
3 -- update version number:
|
|
jbe@211
|
4 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
|
|
jbe@211
|
5 SELECT * FROM (VALUES ('2.0.1', 2, 0, 1))
|
|
jbe@211
|
6 AS "subquery"("string", "major", "minor", "revision");
|
|
jbe@205
|
7
|
|
jbe@212
|
8 -- fix errors in update script from v1.3.1 to v1.4.0:
|
|
jbe@212
|
9 ALTER TABLE "issue" ALTER "state" SET DEFAULT 'admission';
|
|
jbe@205
|
10
|
|
jbe@212
|
11 -- change comments in "member" table:
|
|
jbe@211
|
12 COMMENT ON COLUMN "member"."activated" IS 'Timestamp of first activation of account (i.e. usage of "invite_code"); required to be set for "active" members';
|
|
jbe@211
|
13 COMMENT ON COLUMN "member"."statement" IS 'Freely chosen text of the member for his/her profile';
|
|
jbe@211
|
14
|
|
jbe@212
|
15 -- change comments in "policy" table:
|
|
jbe@211
|
16 COMMENT ON COLUMN "policy"."admission_time" IS 'Maximum duration of issue state ''admission''; Maximum time an issue stays open without being "accepted"';
|
|
jbe@211
|
17 COMMENT ON COLUMN "policy"."discussion_time" IS 'Duration of issue state ''discussion''; Regular time until an issue is "half_frozen" after being "accepted"';
|
|
jbe@211
|
18 COMMENT ON COLUMN "policy"."verification_time" IS 'Duration of issue state ''verification''; Regular time until an issue is "fully_frozen" (e.g. entering issue state ''voting'') after being "half_frozen"';
|
|
jbe@211
|
19 COMMENT ON COLUMN "policy"."voting_time" IS 'Duration of issue state ''voting''; Time after an issue is "fully_frozen" but not "closed" (duration of issue state ''voting'')';
|
|
jbe@211
|
20 COMMENT ON COLUMN "policy"."issue_quorum_num" IS 'Numerator of potential supporter quorum to be reached by one initiative of an issue to be "accepted" and enter issue state ''discussion''';
|
|
jbe@211
|
21 COMMENT ON COLUMN "policy"."issue_quorum_den" IS 'Denominator of potential supporter quorum to be reached by one initiative of an issue to be "accepted" and enter issue state ''discussion''';
|
|
jbe@211
|
22
|
|
jbe@212
|
23 -- change comment in "unit" table:
|
|
jbe@212
|
24 COMMENT ON COLUMN "unit"."active" IS 'TRUE means new issues can be created in areas of this unit';
|
|
jbe@211
|
25
|
|
jbe@212
|
26 -- new table "unit_setting":
|
|
jbe@211
|
27 CREATE TABLE "unit_setting" (
|
|
jbe@211
|
28 PRIMARY KEY ("member_id", "key", "unit_id"),
|
|
jbe@211
|
29 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
jbe@211
|
30 "key" TEXT NOT NULL,
|
|
jbe@211
|
31 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
jbe@211
|
32 "value" TEXT NOT NULL );
|
|
jbe@211
|
33 COMMENT ON TABLE "unit_setting" IS 'Place for frontend to store unit specific settings of members as strings';
|
|
jbe@211
|
34
|
|
jbe@212
|
35 -- change comments in "initiative" table:
|
|
jbe@212
|
36 COMMENT ON COLUMN "initiative"."revoked" IS 'Point in time, when one initiator decided to revoke the initiative';
|
|
jbe@212
|
37 COMMENT ON COLUMN "initiative"."reverse_beat_path" IS 'TRUE, if there is a beat path (may include ties) from this initiative to the status quo';
|
|
jbe@212
|
38 COMMENT ON COLUMN "initiative"."eligible" IS 'Initiative has a "direct_majority" and an "indirect_majority", is "better_than_status_quo" and depending on selected policy the initiative has no "reverse_beat_path" or "multistage_majority"';
|
|
jbe@211
|
39
|
|
jbe@212
|
40 -- change comments in "privilege" table:
|
|
jbe@211
|
41 COMMENT ON COLUMN "privilege"."admin_manager" IS 'Grant/revoke admin privileges to/from other members';
|
|
jbe@211
|
42 COMMENT ON COLUMN "privilege"."unit_manager" IS 'Create and disable sub units';
|
|
jbe@211
|
43 COMMENT ON COLUMN "privilege"."area_manager" IS 'Create and disable areas and set area parameters';
|
|
jbe@211
|
44 COMMENT ON COLUMN "privilege"."voting_right_manager" IS 'Select which members are allowed to discuss and vote within the unit';
|
|
jbe@211
|
45
|
|
jbe@212
|
46 -- add comment to "supporter" table:
|
|
jbe@211
|
47 COMMENT ON COLUMN "supporter"."issue_id" IS 'WARNING: No index: For selections use column "initiative_id" and join via table "initiative" where neccessary';
|
|
jbe@211
|
48
|
|
jbe@212
|
49 -- add column "draft_id" to table "direct_supporter_snapshot":
|
|
jbe@211
|
50 ALTER TABLE "direct_supporter_snapshot" ADD COLUMN "draft_id" INT8;
|
|
jbe@211
|
51 UPDATE "direct_supporter_snapshot" SET "draft_id" = "supporter"."draft_id" FROM "supporter" WHERE "direct_supporter_snapshot"."initiative_id" = "supporter"."initiative_id" AND "direct_supporter_snapshot"."member_id" = "supporter"."member_id";
|
|
jbe@211
|
52 UPDATE "direct_supporter_snapshot" SET "draft_id" = "current_draft"."id" FROM "current_draft" WHERE "direct_supporter_snapshot"."initiative_id" = "current_draft"."initiative_id" AND "direct_supporter_snapshot"."draft_id" ISNULL;
|
|
jbe@211
|
53 ALTER TABLE "direct_supporter_snapshot" ADD FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE NO ACTION ON UPDATE CASCADE;
|
|
jbe@211
|
54 ALTER TABLE "direct_supporter_snapshot" ALTER COLUMN "draft_id" SET NOT NULL;
|
|
jbe@211
|
55
|
|
jbe@212
|
56 -- add comment to "direct_supporter_snapshot" table:
|
|
jbe@212
|
57 COMMENT ON COLUMN "direct_supporter_snapshot"."issue_id" IS 'WARNING: No index: For selections use column "initiative_id" and join via table "initiative" where neccessary';
|
|
jbe@211
|
58
|
|
jbe@212
|
59 -- add comment to "vote" table:
|
|
jbe@211
|
60 COMMENT ON COLUMN "vote"."issue_id" IS 'WARNING: No index: For selections use column "initiative_id" and join via table "initiative" where neccessary';
|
|
jbe@211
|
61
|
|
jbe@212
|
62 -- change function "create_interest_snapshot" to set "direct_supporter_snapshot"."draft_id":
|
|
jbe@211
|
63 CREATE OR REPLACE FUNCTION "create_interest_snapshot"
|
|
jbe@211
|
64 ( "issue_id_p" "issue"."id"%TYPE )
|
|
jbe@211
|
65 RETURNS VOID
|
|
jbe@211
|
66 LANGUAGE 'plpgsql' VOLATILE AS $$
|
|
jbe@211
|
67 DECLARE
|
|
jbe@211
|
68 "member_id_v" "member"."id"%TYPE;
|
|
jbe@211
|
69 BEGIN
|
|
jbe@211
|
70 DELETE FROM "direct_interest_snapshot"
|
|
jbe@211
|
71 WHERE "issue_id" = "issue_id_p"
|
|
jbe@211
|
72 AND "event" = 'periodic';
|
|
jbe@211
|
73 DELETE FROM "delegating_interest_snapshot"
|
|
jbe@211
|
74 WHERE "issue_id" = "issue_id_p"
|
|
jbe@211
|
75 AND "event" = 'periodic';
|
|
jbe@211
|
76 DELETE FROM "direct_supporter_snapshot"
|
|
jbe@211
|
77 WHERE "issue_id" = "issue_id_p"
|
|
jbe@211
|
78 AND "event" = 'periodic';
|
|
jbe@211
|
79 INSERT INTO "direct_interest_snapshot"
|
|
jbe@211
|
80 ("issue_id", "event", "member_id")
|
|
jbe@211
|
81 SELECT
|
|
jbe@211
|
82 "issue_id_p" AS "issue_id",
|
|
jbe@211
|
83 'periodic' AS "event",
|
|
jbe@211
|
84 "member"."id" AS "member_id"
|
|
jbe@211
|
85 FROM "issue"
|
|
jbe@211
|
86 JOIN "area" ON "issue"."area_id" = "area"."id"
|
|
jbe@211
|
87 JOIN "interest" ON "issue"."id" = "interest"."issue_id"
|
|
jbe@211
|
88 JOIN "member" ON "interest"."member_id" = "member"."id"
|
|
jbe@211
|
89 JOIN "privilege"
|
|
jbe@211
|
90 ON "privilege"."unit_id" = "area"."unit_id"
|
|
jbe@211
|
91 AND "privilege"."member_id" = "member"."id"
|
|
jbe@211
|
92 WHERE "issue"."id" = "issue_id_p"
|
|
jbe@211
|
93 AND "member"."active" AND "privilege"."voting_right";
|
|
jbe@211
|
94 FOR "member_id_v" IN
|
|
jbe@211
|
95 SELECT "member_id" FROM "direct_interest_snapshot"
|
|
jbe@211
|
96 WHERE "issue_id" = "issue_id_p"
|
|
jbe@211
|
97 AND "event" = 'periodic'
|
|
jbe@211
|
98 LOOP
|
|
jbe@211
|
99 UPDATE "direct_interest_snapshot" SET
|
|
jbe@211
|
100 "weight" = 1 +
|
|
jbe@211
|
101 "weight_of_added_delegations_for_interest_snapshot"(
|
|
jbe@211
|
102 "issue_id_p",
|
|
jbe@211
|
103 "member_id_v",
|
|
jbe@211
|
104 '{}'
|
|
jbe@211
|
105 )
|
|
jbe@211
|
106 WHERE "issue_id" = "issue_id_p"
|
|
jbe@211
|
107 AND "event" = 'periodic'
|
|
jbe@211
|
108 AND "member_id" = "member_id_v";
|
|
jbe@211
|
109 END LOOP;
|
|
jbe@211
|
110 INSERT INTO "direct_supporter_snapshot"
|
|
jbe@211
|
111 ( "issue_id", "initiative_id", "event", "member_id",
|
|
jbe@211
|
112 "draft_id", "informed", "satisfied" )
|
|
jbe@211
|
113 SELECT
|
|
jbe@211
|
114 "issue_id_p" AS "issue_id",
|
|
jbe@211
|
115 "initiative"."id" AS "initiative_id",
|
|
jbe@211
|
116 'periodic' AS "event",
|
|
jbe@211
|
117 "supporter"."member_id" AS "member_id",
|
|
jbe@211
|
118 "supporter"."draft_id" AS "draft_id",
|
|
jbe@211
|
119 "supporter"."draft_id" = "current_draft"."id" AS "informed",
|
|
jbe@211
|
120 NOT EXISTS (
|
|
jbe@211
|
121 SELECT NULL FROM "critical_opinion"
|
|
jbe@211
|
122 WHERE "initiative_id" = "initiative"."id"
|
|
jbe@211
|
123 AND "member_id" = "supporter"."member_id"
|
|
jbe@211
|
124 ) AS "satisfied"
|
|
jbe@211
|
125 FROM "initiative"
|
|
jbe@211
|
126 JOIN "supporter"
|
|
jbe@211
|
127 ON "supporter"."initiative_id" = "initiative"."id"
|
|
jbe@211
|
128 JOIN "current_draft"
|
|
jbe@211
|
129 ON "initiative"."id" = "current_draft"."initiative_id"
|
|
jbe@211
|
130 JOIN "direct_interest_snapshot"
|
|
jbe@211
|
131 ON "supporter"."member_id" = "direct_interest_snapshot"."member_id"
|
|
jbe@211
|
132 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
|
|
jbe@211
|
133 AND "event" = 'periodic'
|
|
jbe@211
|
134 WHERE "initiative"."issue_id" = "issue_id_p";
|
|
jbe@211
|
135 RETURN;
|
|
jbe@211
|
136 END;
|
|
jbe@211
|
137 $$;
|
|
jbe@211
|
138
|
|
jbe@212
|
139 -- change function "delete_private_data" to delete invite codes in "member" instead of "invite_code" table:
|
|
jbe@211
|
140 CREATE OR REPLACE FUNCTION "delete_private_data"()
|
|
jbe@211
|
141 RETURNS VOID
|
|
jbe@211
|
142 LANGUAGE 'plpgsql' VOLATILE AS $$
|
|
jbe@211
|
143 BEGIN
|
|
jbe@211
|
144 UPDATE "member" SET
|
|
jbe@211
|
145 "invite_code" = NULL,
|
|
jbe@211
|
146 "last_login" = NULL,
|
|
jbe@211
|
147 "login" = NULL,
|
|
jbe@211
|
148 "password" = NULL,
|
|
jbe@211
|
149 "notify_email" = NULL,
|
|
jbe@211
|
150 "notify_email_unconfirmed" = NULL,
|
|
jbe@211
|
151 "notify_email_secret" = NULL,
|
|
jbe@211
|
152 "notify_email_secret_expiry" = NULL,
|
|
jbe@211
|
153 "notify_email_lock_expiry" = NULL,
|
|
jbe@211
|
154 "password_reset_secret" = NULL,
|
|
jbe@211
|
155 "password_reset_secret_expiry" = NULL,
|
|
jbe@211
|
156 "organizational_unit" = NULL,
|
|
jbe@211
|
157 "internal_posts" = NULL,
|
|
jbe@211
|
158 "realname" = NULL,
|
|
jbe@211
|
159 "birthday" = NULL,
|
|
jbe@211
|
160 "address" = NULL,
|
|
jbe@211
|
161 "email" = NULL,
|
|
jbe@211
|
162 "xmpp_address" = NULL,
|
|
jbe@211
|
163 "website" = NULL,
|
|
jbe@211
|
164 "phone" = NULL,
|
|
jbe@211
|
165 "mobile_phone" = NULL,
|
|
jbe@211
|
166 "profession" = NULL,
|
|
jbe@211
|
167 "external_memberships" = NULL,
|
|
jbe@211
|
168 "external_posts" = NULL,
|
|
jbe@211
|
169 "statement" = NULL;
|
|
jbe@211
|
170 -- "text_search_data" is updated by triggers
|
|
jbe@211
|
171 DELETE FROM "setting";
|
|
jbe@211
|
172 DELETE FROM "setting_map";
|
|
jbe@211
|
173 DELETE FROM "member_relation_setting";
|
|
jbe@211
|
174 DELETE FROM "member_image";
|
|
jbe@211
|
175 DELETE FROM "contact";
|
|
jbe@211
|
176 DELETE FROM "ignored_member";
|
|
jbe@211
|
177 DELETE FROM "area_setting";
|
|
jbe@211
|
178 DELETE FROM "issue_setting";
|
|
jbe@211
|
179 DELETE FROM "ignored_initiative";
|
|
jbe@211
|
180 DELETE FROM "initiative_setting";
|
|
jbe@211
|
181 DELETE FROM "suggestion_setting";
|
|
jbe@211
|
182 DELETE FROM "non_voter";
|
|
jbe@211
|
183 DELETE FROM "direct_voter" USING "issue"
|
|
jbe@211
|
184 WHERE "direct_voter"."issue_id" = "issue"."id"
|
|
jbe@211
|
185 AND "issue"."closed" ISNULL;
|
|
jbe@211
|
186 RETURN;
|
|
jbe@211
|
187 END;
|
|
jbe@211
|
188 $$;
|
|
jbe@211
|
189
|
|
jbe@205
|
190 COMMIT;
|