liquid_feedback_core
annotate update/core-update.v2.0.12-v2.1.0.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 | 5c98265b39a0 |
children |
rev | line source |
---|---|
jbe@262 | 1 BEGIN; |
jbe@262 | 2 |
jbe@287 | 3 |
jbe@287 | 4 -- update version number |
jbe@287 | 5 |
jbe@262 | 6 CREATE OR REPLACE VIEW "liquid_feedback_version" AS |
jbe@262 | 7 SELECT * FROM (VALUES ('2.1.0', 2, 1, 0)) |
jbe@262 | 8 AS "subquery"("string", "major", "minor", "revision"); |
jbe@262 | 9 |
jbe@287 | 10 |
jbe@287 | 11 -- old API tables are now deprecated |
jbe@287 | 12 |
jbe@286 | 13 COMMENT ON TYPE "application_access_level" IS 'DEPRECATED, WILL BE REMOVED! Access privileges for applications using the API'; |
jbe@286 | 14 COMMENT ON TABLE "member_application" IS 'DEPRECATED, WILL BE REMOVED! Registered application being allowed to use the API'; |
jbe@286 | 15 |
jbe@287 | 16 |
jbe@287 | 17 -- new polling mode and changed privileges |
jbe@287 | 18 |
jbe@262 | 19 ALTER TABLE "policy" ADD COLUMN "polling" BOOLEAN NOT NULL DEFAULT FALSE; |
jbe@262 | 20 ALTER TABLE "policy" ALTER COLUMN "admission_time" DROP NOT NULL; |
jbe@262 | 21 ALTER TABLE "policy" ALTER COLUMN "discussion_time" DROP NOT NULL; |
jbe@262 | 22 ALTER TABLE "policy" ALTER COLUMN "verification_time" DROP NOT NULL; |
jbe@262 | 23 ALTER TABLE "policy" ALTER COLUMN "voting_time" DROP NOT NULL; |
jbe@292 | 24 ALTER TABLE "policy" ALTER COLUMN "issue_quorum_num" DROP NOT NULL; |
jbe@292 | 25 ALTER TABLE "policy" ALTER COLUMN "issue_quorum_den" DROP NOT NULL; |
jbe@262 | 26 ALTER TABLE "policy" ADD CONSTRAINT "timing" CHECK ( |
jbe@262 | 27 ( "polling" = FALSE AND |
jbe@262 | 28 "admission_time" NOTNULL AND "discussion_time" NOTNULL AND |
jbe@262 | 29 "verification_time" NOTNULL AND "voting_time" NOTNULL ) OR |
jbe@262 | 30 ( "polling" = TRUE AND |
jbe@263 | 31 "admission_time" ISNULL AND "discussion_time" NOTNULL AND |
jbe@262 | 32 "verification_time" NOTNULL AND "voting_time" NOTNULL ) OR |
jbe@262 | 33 ( "polling" = TRUE AND |
jbe@262 | 34 "admission_time" ISNULL AND "discussion_time" ISNULL AND |
jbe@262 | 35 "verification_time" ISNULL AND "voting_time" ISNULL ) ); |
jbe@292 | 36 ALTER TABLE "policy" ADD CONSTRAINT "issue_quorum_if_and_only_if_not_polling" CHECK ( |
jbe@292 | 37 "polling" = "issue_quorum_num" ISNULL AND |
jbe@292 | 38 "polling" = "issue_quorum_den" ISNULL ); |
jbe@289 | 39 COMMENT ON COLUMN "policy"."polling" IS 'TRUE = special policy for non-user-generated issues without issue quorum, where certain initiatives (those having the "polling" flag set) do not need to pass the initiative quorum; "admission_time" MUST be set to NULL, the other timings may be set to NULL altogether, allowing individual timing for those issues'; |
jbe@262 | 40 |
jbe@291 | 41 ALTER TABLE "issue" ALTER COLUMN "admission_time" DROP NOT NULL; |
jbe@291 | 42 ALTER TABLE "issue" ADD CONSTRAINT "admission_time_not_null_unless_instantly_accepted" CHECK ( |
jbe@291 | 43 "admission_time" NOTNULL OR ("accepted" NOTNULL AND "accepted" = "created") ); |
jbe@291 | 44 |
jbe@262 | 45 ALTER TABLE "initiative" ADD COLUMN "polling" BOOLEAN NOT NULL DEFAULT FALSE; |
jbe@289 | 46 COMMENT ON COLUMN "initiative"."polling" IS 'Initiative does not need to pass the initiative quorum (see "policy"."polling")'; |
jbe@262 | 47 |
jbe@262 | 48 ALTER TABLE "privilege" RENAME COLUMN "voting_right_manager" TO "member_manager"; |
jbe@262 | 49 ALTER TABLE "privilege" ADD COLUMN "initiative_right" BOOLEAN NOT NULL DEFAULT TRUE; |
jbe@262 | 50 ALTER TABLE "privilege" ADD COLUMN "polling_right" BOOLEAN NOT NULL DEFAULT FALSE; |
jbe@262 | 51 UPDATE "privilege" SET "initiative_right" = "voting_right"; |
jbe@262 | 52 COMMENT ON COLUMN "privilege"."admin_manager" IS 'Grant/revoke any privileges to/from other members'; |
jbe@262 | 53 COMMENT ON COLUMN "privilege"."member_manager" IS 'Adding/removing members from the unit, granting or revoking "initiative_right" and "voting_right"'; |
jbe@262 | 54 COMMENT ON COLUMN "privilege"."initiative_right" IS 'Right to create an initiative'; |
jbe@262 | 55 COMMENT ON COLUMN "privilege"."voting_right" IS 'Right to support initiatives, create and rate suggestions, and to vote'; |
jbe@289 | 56 COMMENT ON COLUMN "privilege"."polling_right" IS 'Right to create issues with policies having the "policy"."polling" flag set, and to add initiatives having the "initiative"."polling" flag set to those issues'; |
jbe@262 | 57 |
jbe@293 | 58 DROP VIEW "member_contingent_left"; |
jbe@293 | 59 DROP VIEW "member_contingent"; |
jbe@293 | 60 ALTER TABLE "contingent" DROP CONSTRAINT "contingent_pkey"; |
jbe@293 | 61 ALTER TABLE "contingent" ALTER COLUMN "time_frame" DROP NOT NULL; |
jbe@294 | 62 ALTER TABLE "contingent" ADD COLUMN "polling" BOOLEAN DEFAULT FALSE; |
jbe@293 | 63 ALTER TABLE "contingent" ADD PRIMARY KEY ("polling", "time_frame"); |
jbe@294 | 64 ALTER TABLE "contingent" ALTER COLUMN "polling" DROP DEFAULT; |
jbe@293 | 65 COMMENT ON COLUMN "contingent"."polling" IS 'Determines if settings are for creating initiatives and new drafts of initiatives with "polling" flag set'; |
jbe@293 | 66 |
jbe@293 | 67 CREATE VIEW "member_contingent" AS |
jbe@293 | 68 SELECT |
jbe@293 | 69 "member"."id" AS "member_id", |
jbe@293 | 70 "contingent"."polling", |
jbe@293 | 71 "contingent"."time_frame", |
jbe@293 | 72 CASE WHEN "contingent"."text_entry_limit" NOTNULL THEN |
jbe@293 | 73 ( |
jbe@293 | 74 SELECT count(1) FROM "draft" |
jbe@293 | 75 JOIN "initiative" ON "initiative"."id" = "draft"."initiative_id" |
jbe@293 | 76 WHERE "draft"."author_id" = "member"."id" |
jbe@293 | 77 AND "initiative"."polling" = "contingent"."polling" |
jbe@293 | 78 AND "draft"."created" > now() - "contingent"."time_frame" |
jbe@293 | 79 ) + ( |
jbe@293 | 80 SELECT count(1) FROM "suggestion" |
jbe@293 | 81 JOIN "initiative" ON "initiative"."id" = "suggestion"."initiative_id" |
jbe@293 | 82 WHERE "suggestion"."author_id" = "member"."id" |
jbe@293 | 83 AND "contingent"."polling" = FALSE |
jbe@293 | 84 AND "suggestion"."created" > now() - "contingent"."time_frame" |
jbe@293 | 85 ) |
jbe@293 | 86 ELSE NULL END AS "text_entry_count", |
jbe@293 | 87 "contingent"."text_entry_limit", |
jbe@293 | 88 CASE WHEN "contingent"."initiative_limit" NOTNULL THEN ( |
jbe@293 | 89 SELECT count(1) FROM "opening_draft" AS "draft" |
jbe@293 | 90 JOIN "initiative" ON "initiative"."id" = "draft"."initiative_id" |
jbe@293 | 91 WHERE "draft"."author_id" = "member"."id" |
jbe@293 | 92 AND "initiative"."polling" = "contingent"."polling" |
jbe@293 | 93 AND "draft"."created" > now() - "contingent"."time_frame" |
jbe@293 | 94 ) ELSE NULL END AS "initiative_count", |
jbe@293 | 95 "contingent"."initiative_limit" |
jbe@293 | 96 FROM "member" CROSS JOIN "contingent"; |
jbe@293 | 97 |
jbe@293 | 98 COMMENT ON VIEW "member_contingent" IS 'Actual counts of text entries and initiatives are calculated per member for each limit in the "contingent" table.'; |
jbe@293 | 99 |
jbe@293 | 100 COMMENT ON COLUMN "member_contingent"."text_entry_count" IS 'Only calculated when "text_entry_limit" is not null in the same row'; |
jbe@293 | 101 COMMENT ON COLUMN "member_contingent"."initiative_count" IS 'Only calculated when "initiative_limit" is not null in the same row'; |
jbe@293 | 102 |
jbe@293 | 103 CREATE VIEW "member_contingent_left" AS |
jbe@293 | 104 SELECT |
jbe@293 | 105 "member_id", |
jbe@293 | 106 "polling", |
jbe@293 | 107 max("text_entry_limit" - "text_entry_count") AS "text_entries_left", |
jbe@293 | 108 max("initiative_limit" - "initiative_count") AS "initiatives_left" |
jbe@293 | 109 FROM "member_contingent" GROUP BY "member_id", "polling"; |
jbe@293 | 110 |
jbe@293 | 111 COMMENT ON VIEW "member_contingent_left" IS 'Amount of text entries or initiatives which can be posted now instantly by a member. This view should be used by a frontend to determine, if the contingent for posting is exhausted.'; |
jbe@293 | 112 |
jbe@262 | 113 CREATE OR REPLACE FUNCTION "freeze_after_snapshot" |
jbe@262 | 114 ( "issue_id_p" "issue"."id"%TYPE ) |
jbe@262 | 115 RETURNS VOID |
jbe@262 | 116 LANGUAGE 'plpgsql' VOLATILE AS $$ |
jbe@262 | 117 DECLARE |
jbe@262 | 118 "issue_row" "issue"%ROWTYPE; |
jbe@262 | 119 "policy_row" "policy"%ROWTYPE; |
jbe@262 | 120 "initiative_row" "initiative"%ROWTYPE; |
jbe@262 | 121 BEGIN |
jbe@262 | 122 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"; |
jbe@262 | 123 SELECT * INTO "policy_row" |
jbe@262 | 124 FROM "policy" WHERE "id" = "issue_row"."policy_id"; |
jbe@262 | 125 PERFORM "set_snapshot_event"("issue_id_p", 'full_freeze'); |
jbe@262 | 126 FOR "initiative_row" IN |
jbe@262 | 127 SELECT * FROM "initiative" |
jbe@262 | 128 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL |
jbe@262 | 129 LOOP |
jbe@262 | 130 IF |
jbe@262 | 131 "initiative_row"."polling" OR ( |
jbe@262 | 132 "initiative_row"."satisfied_supporter_count" > 0 AND |
jbe@262 | 133 "initiative_row"."satisfied_supporter_count" * |
jbe@262 | 134 "policy_row"."initiative_quorum_den" >= |
jbe@262 | 135 "issue_row"."population" * "policy_row"."initiative_quorum_num" |
jbe@262 | 136 ) |
jbe@262 | 137 THEN |
jbe@262 | 138 UPDATE "initiative" SET "admitted" = TRUE |
jbe@262 | 139 WHERE "id" = "initiative_row"."id"; |
jbe@262 | 140 ELSE |
jbe@262 | 141 UPDATE "initiative" SET "admitted" = FALSE |
jbe@262 | 142 WHERE "id" = "initiative_row"."id"; |
jbe@262 | 143 END IF; |
jbe@262 | 144 END LOOP; |
jbe@262 | 145 IF EXISTS ( |
jbe@262 | 146 SELECT NULL FROM "initiative" |
jbe@262 | 147 WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE |
jbe@262 | 148 ) THEN |
jbe@262 | 149 UPDATE "issue" SET |
jbe@262 | 150 "state" = 'voting', |
jbe@262 | 151 "accepted" = coalesce("accepted", now()), |
jbe@262 | 152 "half_frozen" = coalesce("half_frozen", now()), |
jbe@262 | 153 "fully_frozen" = now() |
jbe@262 | 154 WHERE "id" = "issue_id_p"; |
jbe@262 | 155 ELSE |
jbe@262 | 156 UPDATE "issue" SET |
jbe@262 | 157 "state" = 'canceled_no_initiative_admitted', |
jbe@262 | 158 "accepted" = coalesce("accepted", now()), |
jbe@262 | 159 "half_frozen" = coalesce("half_frozen", now()), |
jbe@262 | 160 "fully_frozen" = now(), |
jbe@262 | 161 "closed" = now(), |
jbe@262 | 162 "ranks_available" = TRUE |
jbe@262 | 163 WHERE "id" = "issue_id_p"; |
jbe@262 | 164 -- NOTE: The following DELETE statements have effect only when |
jbe@262 | 165 -- issue state has been manipulated |
jbe@262 | 166 DELETE FROM "direct_voter" WHERE "issue_id" = "issue_id_p"; |
jbe@262 | 167 DELETE FROM "delegating_voter" WHERE "issue_id" = "issue_id_p"; |
jbe@262 | 168 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p"; |
jbe@262 | 169 END IF; |
jbe@262 | 170 RETURN; |
jbe@262 | 171 END; |
jbe@262 | 172 $$; |
jbe@262 | 173 |
jbe@287 | 174 |
jbe@287 | 175 -- issue comments removed, voting comments integrated in "direct_voter" table |
jbe@287 | 176 |
jbe@287 | 177 ALTER TABLE "direct_voter" ADD COLUMN "comment_changed" TIMESTAMPTZ; |
jbe@287 | 178 ALTER TABLE "direct_voter" ADD COLUMN "formatting_engine" TEXT; |
jbe@287 | 179 ALTER TABLE "direct_voter" ADD COLUMN "comment" TEXT; |
jbe@287 | 180 ALTER TABLE "direct_voter" ADD COLUMN "text_search_data" TSVECTOR; |
jbe@287 | 181 CREATE INDEX "direct_voter_text_search_data_idx" ON "direct_voter" USING gin ("text_search_data"); |
jbe@287 | 182 CREATE TRIGGER "update_text_search_data" |
jbe@287 | 183 BEFORE INSERT OR UPDATE ON "direct_voter" |
jbe@287 | 184 FOR EACH ROW EXECUTE PROCEDURE |
jbe@287 | 185 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "comment"); |
jbe@287 | 186 |
jbe@287 | 187 COMMENT ON COLUMN "direct_voter"."comment_changed" IS 'Shall be set on comment change, to indicate a comment being modified after voting has been finished; Automatically set to NULL after voting phase; Automatically set to NULL by trigger, if "comment" is set to NULL'; |
jbe@287 | 188 COMMENT ON COLUMN "direct_voter"."formatting_engine" IS 'Allows different formatting engines (i.e. wiki formats) to be used for "direct_voter"."comment"; Automatically set to NULL by trigger, if "comment" is set to NULL'; |
jbe@287 | 189 COMMENT ON COLUMN "direct_voter"."comment" IS 'Is to be set or updated by the frontend, if comment was inserted or updated AFTER the issue has been closed. Otherwise it shall be set to NULL.'; |
jbe@287 | 190 |
jbe@287 | 191 CREATE TABLE "rendered_voter_comment" ( |
jbe@287 | 192 PRIMARY KEY ("issue_id", "member_id", "format"), |
jbe@287 | 193 FOREIGN KEY ("issue_id", "member_id") |
jbe@287 | 194 REFERENCES "direct_voter" ("issue_id", "member_id") |
jbe@287 | 195 ON DELETE CASCADE ON UPDATE CASCADE, |
jbe@287 | 196 "issue_id" INT4, |
jbe@287 | 197 "member_id" INT4, |
jbe@287 | 198 "format" TEXT, |
jbe@287 | 199 "content" TEXT NOT NULL ); |
jbe@287 | 200 |
jbe@287 | 201 COMMENT ON TABLE "rendered_voter_comment" IS 'This table may be used by frontends to cache "rendered" voter comments (e.g. HTML output generated from wiki text)'; |
jbe@287 | 202 |
jbe@287 | 203 DROP TABLE "rendered_issue_comment"; |
jbe@287 | 204 DROP TABLE "issue_comment"; |
jbe@287 | 205 DROP TABLE "rendered_voting_comment"; |
jbe@287 | 206 DROP TABLE "voting_comment"; |
jbe@287 | 207 |
jbe@287 | 208 CREATE FUNCTION "voter_comment_fields_only_set_when_voter_comment_is_set_trigger"() |
jbe@287 | 209 RETURNS TRIGGER |
jbe@287 | 210 LANGUAGE 'plpgsql' VOLATILE AS $$ |
jbe@287 | 211 BEGIN |
jbe@287 | 212 IF NEW."comment" ISNULL THEN |
jbe@287 | 213 NEW."comment_changed" := NULL; |
jbe@287 | 214 NEW."formatting_engine" := NULL; |
jbe@287 | 215 END IF; |
jbe@287 | 216 RETURN NEW; |
jbe@287 | 217 END; |
jbe@287 | 218 $$; |
jbe@287 | 219 |
jbe@287 | 220 CREATE TRIGGER "voter_comment_fields_only_set_when_voter_comment_is_set" |
jbe@287 | 221 BEFORE INSERT OR UPDATE ON "direct_voter" |
jbe@287 | 222 FOR EACH ROW EXECUTE PROCEDURE |
jbe@287 | 223 "voter_comment_fields_only_set_when_voter_comment_is_set_trigger"(); |
jbe@287 | 224 |
jbe@287 | 225 COMMENT ON FUNCTION "voter_comment_fields_only_set_when_voter_comment_is_set_trigger"() IS 'Implementation of trigger "voter_comment_fields_only_set_when_voter_comment_is_set" ON table "direct_voter"'; |
jbe@287 | 226 COMMENT ON TRIGGER "voter_comment_fields_only_set_when_voter_comment_is_set" ON "direct_voter" IS 'If "comment" is set to NULL, then other comment related fields are also set to NULL.'; |
jbe@287 | 227 |
jbe@290 | 228 CREATE OR REPLACE FUNCTION "forbid_changes_on_closed_issue_trigger"() |
jbe@290 | 229 RETURNS TRIGGER |
jbe@290 | 230 LANGUAGE 'plpgsql' VOLATILE AS $$ |
jbe@290 | 231 DECLARE |
jbe@290 | 232 "issue_id_v" "issue"."id"%TYPE; |
jbe@290 | 233 "issue_row" "issue"%ROWTYPE; |
jbe@290 | 234 BEGIN |
jbe@290 | 235 IF TG_RELID = 'direct_voter'::regclass AND TG_OP = 'UPDATE' THEN |
jbe@290 | 236 IF |
jbe@290 | 237 OLD."issue_id" = NEW."issue_id" AND |
jbe@290 | 238 OLD."member_id" = NEW."member_id" AND |
jbe@290 | 239 OLD."weight" = NEW."weight" |
jbe@290 | 240 THEN |
jbe@290 | 241 RETURN NULL; -- allows changing of voter comment |
jbe@290 | 242 END IF; |
jbe@290 | 243 END IF; |
jbe@290 | 244 IF TG_OP = 'DELETE' THEN |
jbe@290 | 245 "issue_id_v" := OLD."issue_id"; |
jbe@290 | 246 ELSE |
jbe@290 | 247 "issue_id_v" := NEW."issue_id"; |
jbe@290 | 248 END IF; |
jbe@290 | 249 SELECT INTO "issue_row" * FROM "issue" |
jbe@290 | 250 WHERE "id" = "issue_id_v" FOR SHARE; |
jbe@290 | 251 IF "issue_row"."closed" NOTNULL THEN |
jbe@290 | 252 RAISE EXCEPTION 'Tried to modify data belonging to a closed issue.'; |
jbe@290 | 253 END IF; |
jbe@290 | 254 RETURN NULL; |
jbe@290 | 255 END; |
jbe@290 | 256 $$; |
jbe@290 | 257 |
jbe@285 | 258 CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE) |
jbe@285 | 259 RETURNS VOID |
jbe@285 | 260 LANGUAGE 'plpgsql' VOLATILE AS $$ |
jbe@285 | 261 DECLARE |
jbe@285 | 262 "area_id_v" "area"."id"%TYPE; |
jbe@285 | 263 "unit_id_v" "unit"."id"%TYPE; |
jbe@285 | 264 "member_id_v" "member"."id"%TYPE; |
jbe@285 | 265 BEGIN |
jbe@285 | 266 PERFORM "lock_issue"("issue_id_p"); |
jbe@285 | 267 SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p"; |
jbe@285 | 268 SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v"; |
jbe@285 | 269 -- delete timestamp of voting comment: |
jbe@285 | 270 UPDATE "direct_voter" SET "comment_changed" = NULL |
jbe@285 | 271 WHERE "issue_id" = "issue_id_p"; |
jbe@285 | 272 -- delete delegating votes (in cases of manual reset of issue state): |
jbe@285 | 273 DELETE FROM "delegating_voter" |
jbe@285 | 274 WHERE "issue_id" = "issue_id_p"; |
jbe@285 | 275 -- delete votes from non-privileged voters: |
jbe@285 | 276 DELETE FROM "direct_voter" |
jbe@285 | 277 USING ( |
jbe@285 | 278 SELECT |
jbe@285 | 279 "direct_voter"."member_id" |
jbe@285 | 280 FROM "direct_voter" |
jbe@285 | 281 JOIN "member" ON "direct_voter"."member_id" = "member"."id" |
jbe@285 | 282 LEFT JOIN "privilege" |
jbe@285 | 283 ON "privilege"."unit_id" = "unit_id_v" |
jbe@285 | 284 AND "privilege"."member_id" = "direct_voter"."member_id" |
jbe@285 | 285 WHERE "direct_voter"."issue_id" = "issue_id_p" AND ( |
jbe@285 | 286 "member"."active" = FALSE OR |
jbe@285 | 287 "privilege"."voting_right" ISNULL OR |
jbe@285 | 288 "privilege"."voting_right" = FALSE |
jbe@285 | 289 ) |
jbe@285 | 290 ) AS "subquery" |
jbe@285 | 291 WHERE "direct_voter"."issue_id" = "issue_id_p" |
jbe@285 | 292 AND "direct_voter"."member_id" = "subquery"."member_id"; |
jbe@285 | 293 -- consider delegations: |
jbe@285 | 294 UPDATE "direct_voter" SET "weight" = 1 |
jbe@285 | 295 WHERE "issue_id" = "issue_id_p"; |
jbe@285 | 296 PERFORM "add_vote_delegations"("issue_id_p"); |
jbe@285 | 297 -- set voter count and mark issue as being calculated: |
jbe@285 | 298 UPDATE "issue" SET |
jbe@285 | 299 "state" = 'calculation', |
jbe@285 | 300 "closed" = now(), |
jbe@285 | 301 "voter_count" = ( |
jbe@285 | 302 SELECT coalesce(sum("weight"), 0) |
jbe@285 | 303 FROM "direct_voter" WHERE "issue_id" = "issue_id_p" |
jbe@285 | 304 ) |
jbe@285 | 305 WHERE "id" = "issue_id_p"; |
jbe@285 | 306 -- materialize battle_view: |
jbe@285 | 307 -- NOTE: "closed" column of issue must be set at this point |
jbe@285 | 308 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p"; |
jbe@285 | 309 INSERT INTO "battle" ( |
jbe@285 | 310 "issue_id", |
jbe@285 | 311 "winning_initiative_id", "losing_initiative_id", |
jbe@285 | 312 "count" |
jbe@285 | 313 ) SELECT |
jbe@285 | 314 "issue_id", |
jbe@285 | 315 "winning_initiative_id", "losing_initiative_id", |
jbe@285 | 316 "count" |
jbe@285 | 317 FROM "battle_view" WHERE "issue_id" = "issue_id_p"; |
jbe@285 | 318 -- copy "positive_votes" and "negative_votes" from "battle" table: |
jbe@285 | 319 UPDATE "initiative" SET |
jbe@285 | 320 "positive_votes" = "battle_win"."count", |
jbe@285 | 321 "negative_votes" = "battle_lose"."count" |
jbe@285 | 322 FROM "battle" AS "battle_win", "battle" AS "battle_lose" |
jbe@285 | 323 WHERE |
jbe@285 | 324 "battle_win"."issue_id" = "issue_id_p" AND |
jbe@285 | 325 "battle_win"."winning_initiative_id" = "initiative"."id" AND |
jbe@285 | 326 "battle_win"."losing_initiative_id" ISNULL AND |
jbe@285 | 327 "battle_lose"."issue_id" = "issue_id_p" AND |
jbe@285 | 328 "battle_lose"."losing_initiative_id" = "initiative"."id" AND |
jbe@285 | 329 "battle_lose"."winning_initiative_id" ISNULL; |
jbe@285 | 330 END; |
jbe@285 | 331 $$; |
jbe@285 | 332 |
jbe@288 | 333 CREATE OR REPLACE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE) |
jbe@288 | 334 RETURNS VOID |
jbe@288 | 335 LANGUAGE 'plpgsql' VOLATILE AS $$ |
jbe@288 | 336 DECLARE |
jbe@288 | 337 "issue_row" "issue"%ROWTYPE; |
jbe@288 | 338 BEGIN |
jbe@288 | 339 SELECT * INTO "issue_row" |
jbe@288 | 340 FROM "issue" WHERE "id" = "issue_id_p" |
jbe@288 | 341 FOR UPDATE; |
jbe@288 | 342 IF "issue_row"."cleaned" ISNULL THEN |
jbe@288 | 343 UPDATE "issue" SET |
jbe@288 | 344 "state" = 'voting', |
jbe@288 | 345 "closed" = NULL, |
jbe@288 | 346 "ranks_available" = FALSE |
jbe@288 | 347 WHERE "id" = "issue_id_p"; |
jbe@288 | 348 DELETE FROM "delegating_voter" |
jbe@288 | 349 WHERE "issue_id" = "issue_id_p"; |
jbe@288 | 350 DELETE FROM "direct_voter" |
jbe@288 | 351 WHERE "issue_id" = "issue_id_p"; |
jbe@288 | 352 DELETE FROM "delegating_interest_snapshot" |
jbe@288 | 353 WHERE "issue_id" = "issue_id_p"; |
jbe@288 | 354 DELETE FROM "direct_interest_snapshot" |
jbe@288 | 355 WHERE "issue_id" = "issue_id_p"; |
jbe@288 | 356 DELETE FROM "delegating_population_snapshot" |
jbe@288 | 357 WHERE "issue_id" = "issue_id_p"; |
jbe@288 | 358 DELETE FROM "direct_population_snapshot" |
jbe@288 | 359 WHERE "issue_id" = "issue_id_p"; |
jbe@288 | 360 DELETE FROM "non_voter" |
jbe@288 | 361 WHERE "issue_id" = "issue_id_p"; |
jbe@288 | 362 DELETE FROM "delegation" |
jbe@288 | 363 WHERE "issue_id" = "issue_id_p"; |
jbe@288 | 364 DELETE FROM "supporter" |
jbe@288 | 365 WHERE "issue_id" = "issue_id_p"; |
jbe@288 | 366 UPDATE "issue" SET |
jbe@288 | 367 "state" = "issue_row"."state", |
jbe@288 | 368 "closed" = "issue_row"."closed", |
jbe@288 | 369 "ranks_available" = "issue_row"."ranks_available", |
jbe@288 | 370 "cleaned" = now() |
jbe@288 | 371 WHERE "id" = "issue_id_p"; |
jbe@288 | 372 END IF; |
jbe@288 | 373 RETURN; |
jbe@288 | 374 END; |
jbe@288 | 375 $$; |
jbe@288 | 376 |
jbe@287 | 377 |
jbe@287 | 378 -- "non_voter" deletes "direct_voter" and vice versa |
jbe@287 | 379 |
jbe@287 | 380 CREATE FUNCTION "non_voter_deletes_direct_voter_trigger"() |
jbe@287 | 381 RETURNS TRIGGER |
jbe@287 | 382 LANGUAGE 'plpgsql' VOLATILE AS $$ |
jbe@287 | 383 BEGIN |
jbe@287 | 384 DELETE FROM "direct_voter" |
jbe@287 | 385 WHERE "issue_id" = NEW."issue_id" AND "member_id" = NEW."member_id"; |
jbe@287 | 386 RETURN NULL; |
jbe@287 | 387 END; |
jbe@287 | 388 $$; |
jbe@287 | 389 |
jbe@287 | 390 CREATE TRIGGER "non_voter_deletes_direct_voter" |
jbe@287 | 391 AFTER INSERT OR UPDATE ON "non_voter" |
jbe@287 | 392 FOR EACH ROW EXECUTE PROCEDURE |
jbe@287 | 393 "non_voter_deletes_direct_voter_trigger"(); |
jbe@287 | 394 |
jbe@287 | 395 COMMENT ON FUNCTION "non_voter_deletes_direct_voter_trigger"() IS 'Implementation of trigger "non_voter_deletes_direct_voter" on table "non_voter"'; |
jbe@287 | 396 COMMENT ON TRIGGER "non_voter_deletes_direct_voter" ON "non_voter" IS 'An entry in the "non_voter" table deletes an entry in the "direct_voter" table (and vice versa due to trigger "direct_voter_deletes_non_voter" on table "direct_voter")'; |
jbe@287 | 397 |
jbe@287 | 398 CREATE FUNCTION "direct_voter_deletes_non_voter_trigger"() |
jbe@287 | 399 RETURNS TRIGGER |
jbe@287 | 400 LANGUAGE 'plpgsql' VOLATILE AS $$ |
jbe@287 | 401 BEGIN |
jbe@287 | 402 DELETE FROM "non_voter" |
jbe@287 | 403 WHERE "issue_id" = NEW."issue_id" AND "member_id" = NEW."member_id"; |
jbe@287 | 404 RETURN NULL; |
jbe@287 | 405 END; |
jbe@287 | 406 $$; |
jbe@287 | 407 |
jbe@287 | 408 CREATE TRIGGER "direct_voter_deletes_non_voter" |
jbe@287 | 409 AFTER INSERT OR UPDATE ON "direct_voter" |
jbe@287 | 410 FOR EACH ROW EXECUTE PROCEDURE |
jbe@287 | 411 "direct_voter_deletes_non_voter_trigger"(); |
jbe@287 | 412 |
jbe@287 | 413 COMMENT ON FUNCTION "direct_voter_deletes_non_voter_trigger"() IS 'Implementation of trigger "direct_voter_deletes_non_voter" on table "direct_voter"'; |
jbe@287 | 414 COMMENT ON TRIGGER "direct_voter_deletes_non_voter" ON "direct_voter" IS 'An entry in the "direct_voter" table deletes an entry in the "non_voter" table (and vice versa due to trigger "non_voter_deletes_direct_voter" on table "non_voter")'; |
jbe@287 | 415 |
jbe@287 | 416 |
jbe@297 | 417 -- different locking levels and different locking order to avoid deadlocks |
jbe@297 | 418 |
jbe@297 | 419 CREATE OR REPLACE FUNCTION "lock_issue" |
jbe@297 | 420 ( "issue_id_p" "issue"."id"%TYPE ) |
jbe@297 | 421 RETURNS VOID |
jbe@297 | 422 LANGUAGE 'plpgsql' VOLATILE AS $$ |
jbe@297 | 423 BEGIN |
jbe@297 | 424 -- The following locking order is used: |
jbe@297 | 425 -- 1st) row-level lock on the issue |
jbe@297 | 426 -- 2nd) table-level locks in order of occurrence in the core.sql file |
jbe@297 | 427 PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE; |
jbe@297 | 428 -- NOTE: The row-level exclusive lock in combination with the |
jbe@297 | 429 -- share_row_lock_issue(_via_initiative)_trigger functions (which |
jbe@297 | 430 -- acquire a row-level share lock on the issue) ensure that no data |
jbe@297 | 431 -- is changed, which could affect calculation of snapshots or |
jbe@297 | 432 -- counting of votes. Table "delegation" must be table-level-locked, |
jbe@297 | 433 -- as it also contains issue- and global-scope delegations. |
jbe@301 | 434 PERFORM NULL FROM "member" WHERE "active" FOR SHARE; |
jbe@299 | 435 -- NOTE: As we later cause implicit row-level share locks on many |
jbe@301 | 436 -- active members, we lock them before locking any other table |
jbe@301 | 437 -- to avoid deadlocks |
jbe@301 | 438 LOCK TABLE "member" IN SHARE MODE; |
jbe@297 | 439 LOCK TABLE "privilege" IN SHARE MODE; |
jbe@297 | 440 LOCK TABLE "membership" IN SHARE MODE; |
jbe@297 | 441 LOCK TABLE "policy" IN SHARE MODE; |
jbe@297 | 442 LOCK TABLE "delegation" IN SHARE MODE; |
jbe@297 | 443 LOCK TABLE "direct_population_snapshot" IN EXCLUSIVE MODE; |
jbe@297 | 444 LOCK TABLE "delegating_population_snapshot" IN EXCLUSIVE MODE; |
jbe@297 | 445 LOCK TABLE "direct_interest_snapshot" IN EXCLUSIVE MODE; |
jbe@297 | 446 LOCK TABLE "delegating_interest_snapshot" IN EXCLUSIVE MODE; |
jbe@297 | 447 LOCK TABLE "direct_supporter_snapshot" IN EXCLUSIVE MODE; |
jbe@297 | 448 RETURN; |
jbe@297 | 449 END; |
jbe@297 | 450 $$; |
jbe@297 | 451 |
jbe@297 | 452 |
jbe@287 | 453 -- new comment on function "delete_private_data"() |
jbe@287 | 454 |
jbe@283 | 455 COMMENT ON FUNCTION "delete_private_data"() IS 'Used by lf_export script. DO NOT USE on productive database, but only on a copy! This function deletes all data which should not be publicly available, and can be used to create a database dump for publication. See source code to see which data is deleted. If you need a different behaviour, copy this function and modify lf_export accordingly, to avoid data-leaks after updating.'; |
jbe@283 | 456 |
jbe@287 | 457 |
jbe@308 | 458 -- NOTE: The first version of the previous update script didn't |
jbe@308 | 459 -- remove the "vote_ratio" function. |
jbe@308 | 460 -- The function is therefore removed here as well, if existent. |
jbe@308 | 461 |
jbe@308 | 462 DROP FUNCTION IF EXISTS "vote_ratio" |
jbe@308 | 463 ( "initiative"."positive_votes"%TYPE, |
jbe@308 | 464 "initiative"."negative_votes"%TYPE ); |
jbe@308 | 465 |
jbe@308 | 466 |
jbe@262 | 467 COMMIT; |