liquid_feedback_core

view update/core-update.v2.0.0-v2.0.1.sql @ 593:e7f772ca0621

Removed an index on table "initiative"
author jbe
date Sat Dec 08 01:37:23 2018 +0100 (20 months ago)
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;

Impressum / About Us