liquid_feedback_core
view update/core-update.v2.0.0-v2.0.1.sql @ 378:e88d0606891f
Bugfix regarding "proportional_order" of suggestions:
Use NULL values explicitly to be sorted last
(includes new suggestions as well as suggestions without any individual rankings)
Use NULL values explicitly to be sorted last
(includes new suggestions as well as suggestions without any individual rankings)
author | jbe |
---|---|
date | Mon Mar 18 09:36:21 2013 +0100 (2013-03-18) |
parents | c23b288fd771 |
children |
line source
1 BEGIN;
3 -- update version number:
4 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
5 SELECT * FROM (VALUES ('2.0.1', 2, 0, 1))
6 AS "subquery"("string", "major", "minor", "revision");
8 -- fix errors in update script from v1.3.1 to v1.4.0:
9 ALTER TABLE "issue" ALTER "state" SET DEFAULT 'admission';
11 -- change comments in "member" table:
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';
13 COMMENT ON COLUMN "member"."statement" IS 'Freely chosen text of the member for his/her profile';
15 -- change comments in "policy" table:
16 COMMENT ON COLUMN "policy"."admission_time" IS 'Maximum duration of issue state ''admission''; Maximum time an issue stays open without being "accepted"';
17 COMMENT ON COLUMN "policy"."discussion_time" IS 'Duration of issue state ''discussion''; Regular time until an issue is "half_frozen" after being "accepted"';
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"';
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'')';
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''';
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''';
23 -- change comment in "unit" table:
24 COMMENT ON COLUMN "unit"."active" IS 'TRUE means new issues can be created in areas of this unit';
26 -- new table "unit_setting":
27 CREATE TABLE "unit_setting" (
28 PRIMARY KEY ("member_id", "key", "unit_id"),
29 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
30 "key" TEXT NOT NULL,
31 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
32 "value" TEXT NOT NULL );
33 COMMENT ON TABLE "unit_setting" IS 'Place for frontend to store unit specific settings of members as strings';
35 -- change comments in "initiative" table:
36 COMMENT ON COLUMN "initiative"."revoked" IS 'Point in time, when one initiator decided to revoke the initiative';
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';
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"';
40 -- change comments in "privilege" table:
41 COMMENT ON COLUMN "privilege"."admin_manager" IS 'Grant/revoke admin privileges to/from other members';
42 COMMENT ON COLUMN "privilege"."unit_manager" IS 'Create and disable sub units';
43 COMMENT ON COLUMN "privilege"."area_manager" IS 'Create and disable areas and set area parameters';
44 COMMENT ON COLUMN "privilege"."voting_right_manager" IS 'Select which members are allowed to discuss and vote within the unit';
46 -- add comment to "supporter" table:
47 COMMENT ON COLUMN "supporter"."issue_id" IS 'WARNING: No index: For selections use column "initiative_id" and join via table "initiative" where neccessary';
49 -- add column "draft_id" to table "direct_supporter_snapshot":
50 ALTER TABLE "direct_supporter_snapshot" ADD COLUMN "draft_id" INT8;
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";
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;
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;
54 ALTER TABLE "direct_supporter_snapshot" ALTER COLUMN "draft_id" SET NOT NULL;
56 -- add comment to "direct_supporter_snapshot" table:
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';
59 -- add comment to "vote" table:
60 COMMENT ON COLUMN "vote"."issue_id" IS 'WARNING: No index: For selections use column "initiative_id" and join via table "initiative" where neccessary';
62 -- change function "create_interest_snapshot" to set "direct_supporter_snapshot"."draft_id":
63 CREATE OR REPLACE FUNCTION "create_interest_snapshot"
64 ( "issue_id_p" "issue"."id"%TYPE )
65 RETURNS VOID
66 LANGUAGE 'plpgsql' VOLATILE AS $$
67 DECLARE
68 "member_id_v" "member"."id"%TYPE;
69 BEGIN
70 DELETE FROM "direct_interest_snapshot"
71 WHERE "issue_id" = "issue_id_p"
72 AND "event" = 'periodic';
73 DELETE FROM "delegating_interest_snapshot"
74 WHERE "issue_id" = "issue_id_p"
75 AND "event" = 'periodic';
76 DELETE FROM "direct_supporter_snapshot"
77 WHERE "issue_id" = "issue_id_p"
78 AND "event" = 'periodic';
79 INSERT INTO "direct_interest_snapshot"
80 ("issue_id", "event", "member_id")
81 SELECT
82 "issue_id_p" AS "issue_id",
83 'periodic' AS "event",
84 "member"."id" AS "member_id"
85 FROM "issue"
86 JOIN "area" ON "issue"."area_id" = "area"."id"
87 JOIN "interest" ON "issue"."id" = "interest"."issue_id"
88 JOIN "member" ON "interest"."member_id" = "member"."id"
89 JOIN "privilege"
90 ON "privilege"."unit_id" = "area"."unit_id"
91 AND "privilege"."member_id" = "member"."id"
92 WHERE "issue"."id" = "issue_id_p"
93 AND "member"."active" AND "privilege"."voting_right";
94 FOR "member_id_v" IN
95 SELECT "member_id" FROM "direct_interest_snapshot"
96 WHERE "issue_id" = "issue_id_p"
97 AND "event" = 'periodic'
98 LOOP
99 UPDATE "direct_interest_snapshot" SET
100 "weight" = 1 +
101 "weight_of_added_delegations_for_interest_snapshot"(
102 "issue_id_p",
103 "member_id_v",
104 '{}'
105 )
106 WHERE "issue_id" = "issue_id_p"
107 AND "event" = 'periodic'
108 AND "member_id" = "member_id_v";
109 END LOOP;
110 INSERT INTO "direct_supporter_snapshot"
111 ( "issue_id", "initiative_id", "event", "member_id",
112 "draft_id", "informed", "satisfied" )
113 SELECT
114 "issue_id_p" AS "issue_id",
115 "initiative"."id" AS "initiative_id",
116 'periodic' AS "event",
117 "supporter"."member_id" AS "member_id",
118 "supporter"."draft_id" AS "draft_id",
119 "supporter"."draft_id" = "current_draft"."id" AS "informed",
120 NOT EXISTS (
121 SELECT NULL FROM "critical_opinion"
122 WHERE "initiative_id" = "initiative"."id"
123 AND "member_id" = "supporter"."member_id"
124 ) AS "satisfied"
125 FROM "initiative"
126 JOIN "supporter"
127 ON "supporter"."initiative_id" = "initiative"."id"
128 JOIN "current_draft"
129 ON "initiative"."id" = "current_draft"."initiative_id"
130 JOIN "direct_interest_snapshot"
131 ON "supporter"."member_id" = "direct_interest_snapshot"."member_id"
132 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
133 AND "event" = 'periodic'
134 WHERE "initiative"."issue_id" = "issue_id_p";
135 RETURN;
136 END;
137 $$;
139 -- change function "delete_private_data" to delete invite codes in "member" instead of "invite_code" table:
140 CREATE OR REPLACE FUNCTION "delete_private_data"()
141 RETURNS VOID
142 LANGUAGE 'plpgsql' VOLATILE AS $$
143 BEGIN
144 UPDATE "member" SET
145 "invite_code" = NULL,
146 "last_login" = NULL,
147 "login" = NULL,
148 "password" = NULL,
149 "notify_email" = NULL,
150 "notify_email_unconfirmed" = NULL,
151 "notify_email_secret" = NULL,
152 "notify_email_secret_expiry" = NULL,
153 "notify_email_lock_expiry" = NULL,
154 "password_reset_secret" = NULL,
155 "password_reset_secret_expiry" = NULL,
156 "organizational_unit" = NULL,
157 "internal_posts" = NULL,
158 "realname" = NULL,
159 "birthday" = NULL,
160 "address" = NULL,
161 "email" = NULL,
162 "xmpp_address" = NULL,
163 "website" = NULL,
164 "phone" = NULL,
165 "mobile_phone" = NULL,
166 "profession" = NULL,
167 "external_memberships" = NULL,
168 "external_posts" = NULL,
169 "statement" = NULL;
170 -- "text_search_data" is updated by triggers
171 DELETE FROM "setting";
172 DELETE FROM "setting_map";
173 DELETE FROM "member_relation_setting";
174 DELETE FROM "member_image";
175 DELETE FROM "contact";
176 DELETE FROM "ignored_member";
177 DELETE FROM "area_setting";
178 DELETE FROM "issue_setting";
179 DELETE FROM "ignored_initiative";
180 DELETE FROM "initiative_setting";
181 DELETE FROM "suggestion_setting";
182 DELETE FROM "non_voter";
183 DELETE FROM "direct_voter" USING "issue"
184 WHERE "direct_voter"."issue_id" = "issue"."id"
185 AND "issue"."closed" ISNULL;
186 RETURN;
187 END;
188 $$;
190 COMMIT;