liquid_feedback_core
annotate update/core-update.v2.0.11-v2.1.0.sql @ 297:779ba19a07df
Different locking levels and different locking order in function "lock_issue"("issue"."id") to avoid deadlocks
(includes update of update/core-update.v2.0.11-v2.1.0.sql)
(includes update of update/core-update.v2.0.11-v2.1.0.sql)
author | jbe |
---|---|
date | Tue Sep 25 02:32:51 2012 +0200 (2012-09-25) |
parents | 703986b6de29 |
children | dbd237d86a8a |
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@297 | 434 LOCK TABLE "member" IN EXCLUSIVE MODE; -- exclusive avoids deadlocks |
jbe@297 | 435 LOCK TABLE "privilege" IN SHARE MODE; |
jbe@297 | 436 LOCK TABLE "membership" IN SHARE MODE; |
jbe@297 | 437 LOCK TABLE "policy" IN SHARE MODE; |
jbe@297 | 438 LOCK TABLE "delegation" IN SHARE MODE; |
jbe@297 | 439 LOCK TABLE "direct_population_snapshot" IN EXCLUSIVE MODE; |
jbe@297 | 440 LOCK TABLE "delegating_population_snapshot" IN EXCLUSIVE MODE; |
jbe@297 | 441 LOCK TABLE "direct_interest_snapshot" IN EXCLUSIVE MODE; |
jbe@297 | 442 LOCK TABLE "delegating_interest_snapshot" IN EXCLUSIVE MODE; |
jbe@297 | 443 LOCK TABLE "direct_supporter_snapshot" IN EXCLUSIVE MODE; |
jbe@297 | 444 RETURN; |
jbe@297 | 445 END; |
jbe@297 | 446 $$; |
jbe@297 | 447 |
jbe@297 | 448 |
jbe@287 | 449 -- new comment on function "delete_private_data"() |
jbe@287 | 450 |
jbe@283 | 451 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 | 452 |
jbe@287 | 453 |
jbe@262 | 454 COMMIT; |