liquid_feedback_core

annotate update/core-update.v2.0.11-v2.1.0.sql @ 293:9ab561a91035

find changesets by author, revision, files, or words in the commit message
Split contingent into polling and non-polling contingent
author jbe
date Sat Aug 25 20:55:33 2012 +0200 (2012-08-25)
parents 3de42ea02dc2
children 703986b6de29
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@293 62 ALTER TABLE "contingent" ADD COLUMN "polling" BOOLEAN;
jbe@293 63 ALTER TABLE "contingent" ADD PRIMARY KEY ("polling", "time_frame");
jbe@293 64 COMMENT ON COLUMN "contingent"."polling" IS 'Determines if settings are for creating initiatives and new drafts of initiatives with "polling" flag set';
jbe@293 65
jbe@293 66 CREATE VIEW "member_contingent" AS
jbe@293 67 SELECT
jbe@293 68 "member"."id" AS "member_id",
jbe@293 69 "contingent"."polling",
jbe@293 70 "contingent"."time_frame",
jbe@293 71 CASE WHEN "contingent"."text_entry_limit" NOTNULL THEN
jbe@293 72 (
jbe@293 73 SELECT count(1) FROM "draft"
jbe@293 74 JOIN "initiative" ON "initiative"."id" = "draft"."initiative_id"
jbe@293 75 WHERE "draft"."author_id" = "member"."id"
jbe@293 76 AND "initiative"."polling" = "contingent"."polling"
jbe@293 77 AND "draft"."created" > now() - "contingent"."time_frame"
jbe@293 78 ) + (
jbe@293 79 SELECT count(1) FROM "suggestion"
jbe@293 80 JOIN "initiative" ON "initiative"."id" = "suggestion"."initiative_id"
jbe@293 81 WHERE "suggestion"."author_id" = "member"."id"
jbe@293 82 AND "contingent"."polling" = FALSE
jbe@293 83 AND "suggestion"."created" > now() - "contingent"."time_frame"
jbe@293 84 )
jbe@293 85 ELSE NULL END AS "text_entry_count",
jbe@293 86 "contingent"."text_entry_limit",
jbe@293 87 CASE WHEN "contingent"."initiative_limit" NOTNULL THEN (
jbe@293 88 SELECT count(1) FROM "opening_draft" AS "draft"
jbe@293 89 JOIN "initiative" ON "initiative"."id" = "draft"."initiative_id"
jbe@293 90 WHERE "draft"."author_id" = "member"."id"
jbe@293 91 AND "initiative"."polling" = "contingent"."polling"
jbe@293 92 AND "draft"."created" > now() - "contingent"."time_frame"
jbe@293 93 ) ELSE NULL END AS "initiative_count",
jbe@293 94 "contingent"."initiative_limit"
jbe@293 95 FROM "member" CROSS JOIN "contingent";
jbe@293 96
jbe@293 97 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 98
jbe@293 99 COMMENT ON COLUMN "member_contingent"."text_entry_count" IS 'Only calculated when "text_entry_limit" is not null in the same row';
jbe@293 100 COMMENT ON COLUMN "member_contingent"."initiative_count" IS 'Only calculated when "initiative_limit" is not null in the same row';
jbe@293 101
jbe@293 102 CREATE VIEW "member_contingent_left" AS
jbe@293 103 SELECT
jbe@293 104 "member_id",
jbe@293 105 "polling",
jbe@293 106 max("text_entry_limit" - "text_entry_count") AS "text_entries_left",
jbe@293 107 max("initiative_limit" - "initiative_count") AS "initiatives_left"
jbe@293 108 FROM "member_contingent" GROUP BY "member_id", "polling";
jbe@293 109
jbe@293 110 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 111
jbe@262 112 CREATE OR REPLACE FUNCTION "freeze_after_snapshot"
jbe@262 113 ( "issue_id_p" "issue"."id"%TYPE )
jbe@262 114 RETURNS VOID
jbe@262 115 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@262 116 DECLARE
jbe@262 117 "issue_row" "issue"%ROWTYPE;
jbe@262 118 "policy_row" "policy"%ROWTYPE;
jbe@262 119 "initiative_row" "initiative"%ROWTYPE;
jbe@262 120 BEGIN
jbe@262 121 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
jbe@262 122 SELECT * INTO "policy_row"
jbe@262 123 FROM "policy" WHERE "id" = "issue_row"."policy_id";
jbe@262 124 PERFORM "set_snapshot_event"("issue_id_p", 'full_freeze');
jbe@262 125 FOR "initiative_row" IN
jbe@262 126 SELECT * FROM "initiative"
jbe@262 127 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
jbe@262 128 LOOP
jbe@262 129 IF
jbe@262 130 "initiative_row"."polling" OR (
jbe@262 131 "initiative_row"."satisfied_supporter_count" > 0 AND
jbe@262 132 "initiative_row"."satisfied_supporter_count" *
jbe@262 133 "policy_row"."initiative_quorum_den" >=
jbe@262 134 "issue_row"."population" * "policy_row"."initiative_quorum_num"
jbe@262 135 )
jbe@262 136 THEN
jbe@262 137 UPDATE "initiative" SET "admitted" = TRUE
jbe@262 138 WHERE "id" = "initiative_row"."id";
jbe@262 139 ELSE
jbe@262 140 UPDATE "initiative" SET "admitted" = FALSE
jbe@262 141 WHERE "id" = "initiative_row"."id";
jbe@262 142 END IF;
jbe@262 143 END LOOP;
jbe@262 144 IF EXISTS (
jbe@262 145 SELECT NULL FROM "initiative"
jbe@262 146 WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE
jbe@262 147 ) THEN
jbe@262 148 UPDATE "issue" SET
jbe@262 149 "state" = 'voting',
jbe@262 150 "accepted" = coalesce("accepted", now()),
jbe@262 151 "half_frozen" = coalesce("half_frozen", now()),
jbe@262 152 "fully_frozen" = now()
jbe@262 153 WHERE "id" = "issue_id_p";
jbe@262 154 ELSE
jbe@262 155 UPDATE "issue" SET
jbe@262 156 "state" = 'canceled_no_initiative_admitted',
jbe@262 157 "accepted" = coalesce("accepted", now()),
jbe@262 158 "half_frozen" = coalesce("half_frozen", now()),
jbe@262 159 "fully_frozen" = now(),
jbe@262 160 "closed" = now(),
jbe@262 161 "ranks_available" = TRUE
jbe@262 162 WHERE "id" = "issue_id_p";
jbe@262 163 -- NOTE: The following DELETE statements have effect only when
jbe@262 164 -- issue state has been manipulated
jbe@262 165 DELETE FROM "direct_voter" WHERE "issue_id" = "issue_id_p";
jbe@262 166 DELETE FROM "delegating_voter" WHERE "issue_id" = "issue_id_p";
jbe@262 167 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
jbe@262 168 END IF;
jbe@262 169 RETURN;
jbe@262 170 END;
jbe@262 171 $$;
jbe@262 172
jbe@287 173
jbe@287 174 -- issue comments removed, voting comments integrated in "direct_voter" table
jbe@287 175
jbe@287 176 ALTER TABLE "direct_voter" ADD COLUMN "comment_changed" TIMESTAMPTZ;
jbe@287 177 ALTER TABLE "direct_voter" ADD COLUMN "formatting_engine" TEXT;
jbe@287 178 ALTER TABLE "direct_voter" ADD COLUMN "comment" TEXT;
jbe@287 179 ALTER TABLE "direct_voter" ADD COLUMN "text_search_data" TSVECTOR;
jbe@287 180 CREATE INDEX "direct_voter_text_search_data_idx" ON "direct_voter" USING gin ("text_search_data");
jbe@287 181 CREATE TRIGGER "update_text_search_data"
jbe@287 182 BEFORE INSERT OR UPDATE ON "direct_voter"
jbe@287 183 FOR EACH ROW EXECUTE PROCEDURE
jbe@287 184 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "comment");
jbe@287 185
jbe@287 186 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 187 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 188 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 189
jbe@287 190 CREATE TABLE "rendered_voter_comment" (
jbe@287 191 PRIMARY KEY ("issue_id", "member_id", "format"),
jbe@287 192 FOREIGN KEY ("issue_id", "member_id")
jbe@287 193 REFERENCES "direct_voter" ("issue_id", "member_id")
jbe@287 194 ON DELETE CASCADE ON UPDATE CASCADE,
jbe@287 195 "issue_id" INT4,
jbe@287 196 "member_id" INT4,
jbe@287 197 "format" TEXT,
jbe@287 198 "content" TEXT NOT NULL );
jbe@287 199
jbe@287 200 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 201
jbe@287 202 DROP TABLE "rendered_issue_comment";
jbe@287 203 DROP TABLE "issue_comment";
jbe@287 204 DROP TABLE "rendered_voting_comment";
jbe@287 205 DROP TABLE "voting_comment";
jbe@287 206
jbe@287 207 CREATE FUNCTION "voter_comment_fields_only_set_when_voter_comment_is_set_trigger"()
jbe@287 208 RETURNS TRIGGER
jbe@287 209 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@287 210 BEGIN
jbe@287 211 IF NEW."comment" ISNULL THEN
jbe@287 212 NEW."comment_changed" := NULL;
jbe@287 213 NEW."formatting_engine" := NULL;
jbe@287 214 END IF;
jbe@287 215 RETURN NEW;
jbe@287 216 END;
jbe@287 217 $$;
jbe@287 218
jbe@287 219 CREATE TRIGGER "voter_comment_fields_only_set_when_voter_comment_is_set"
jbe@287 220 BEFORE INSERT OR UPDATE ON "direct_voter"
jbe@287 221 FOR EACH ROW EXECUTE PROCEDURE
jbe@287 222 "voter_comment_fields_only_set_when_voter_comment_is_set_trigger"();
jbe@287 223
jbe@287 224 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 225 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 226
jbe@290 227 CREATE OR REPLACE FUNCTION "forbid_changes_on_closed_issue_trigger"()
jbe@290 228 RETURNS TRIGGER
jbe@290 229 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@290 230 DECLARE
jbe@290 231 "issue_id_v" "issue"."id"%TYPE;
jbe@290 232 "issue_row" "issue"%ROWTYPE;
jbe@290 233 BEGIN
jbe@290 234 IF TG_RELID = 'direct_voter'::regclass AND TG_OP = 'UPDATE' THEN
jbe@290 235 IF
jbe@290 236 OLD."issue_id" = NEW."issue_id" AND
jbe@290 237 OLD."member_id" = NEW."member_id" AND
jbe@290 238 OLD."weight" = NEW."weight"
jbe@290 239 THEN
jbe@290 240 RETURN NULL; -- allows changing of voter comment
jbe@290 241 END IF;
jbe@290 242 END IF;
jbe@290 243 IF TG_OP = 'DELETE' THEN
jbe@290 244 "issue_id_v" := OLD."issue_id";
jbe@290 245 ELSE
jbe@290 246 "issue_id_v" := NEW."issue_id";
jbe@290 247 END IF;
jbe@290 248 SELECT INTO "issue_row" * FROM "issue"
jbe@290 249 WHERE "id" = "issue_id_v" FOR SHARE;
jbe@290 250 IF "issue_row"."closed" NOTNULL THEN
jbe@290 251 RAISE EXCEPTION 'Tried to modify data belonging to a closed issue.';
jbe@290 252 END IF;
jbe@290 253 RETURN NULL;
jbe@290 254 END;
jbe@290 255 $$;
jbe@290 256
jbe@285 257 CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
jbe@285 258 RETURNS VOID
jbe@285 259 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@285 260 DECLARE
jbe@285 261 "area_id_v" "area"."id"%TYPE;
jbe@285 262 "unit_id_v" "unit"."id"%TYPE;
jbe@285 263 "member_id_v" "member"."id"%TYPE;
jbe@285 264 BEGIN
jbe@285 265 PERFORM "lock_issue"("issue_id_p");
jbe@285 266 SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
jbe@285 267 SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v";
jbe@285 268 -- delete timestamp of voting comment:
jbe@285 269 UPDATE "direct_voter" SET "comment_changed" = NULL
jbe@285 270 WHERE "issue_id" = "issue_id_p";
jbe@285 271 -- delete delegating votes (in cases of manual reset of issue state):
jbe@285 272 DELETE FROM "delegating_voter"
jbe@285 273 WHERE "issue_id" = "issue_id_p";
jbe@285 274 -- delete votes from non-privileged voters:
jbe@285 275 DELETE FROM "direct_voter"
jbe@285 276 USING (
jbe@285 277 SELECT
jbe@285 278 "direct_voter"."member_id"
jbe@285 279 FROM "direct_voter"
jbe@285 280 JOIN "member" ON "direct_voter"."member_id" = "member"."id"
jbe@285 281 LEFT JOIN "privilege"
jbe@285 282 ON "privilege"."unit_id" = "unit_id_v"
jbe@285 283 AND "privilege"."member_id" = "direct_voter"."member_id"
jbe@285 284 WHERE "direct_voter"."issue_id" = "issue_id_p" AND (
jbe@285 285 "member"."active" = FALSE OR
jbe@285 286 "privilege"."voting_right" ISNULL OR
jbe@285 287 "privilege"."voting_right" = FALSE
jbe@285 288 )
jbe@285 289 ) AS "subquery"
jbe@285 290 WHERE "direct_voter"."issue_id" = "issue_id_p"
jbe@285 291 AND "direct_voter"."member_id" = "subquery"."member_id";
jbe@285 292 -- consider delegations:
jbe@285 293 UPDATE "direct_voter" SET "weight" = 1
jbe@285 294 WHERE "issue_id" = "issue_id_p";
jbe@285 295 PERFORM "add_vote_delegations"("issue_id_p");
jbe@285 296 -- set voter count and mark issue as being calculated:
jbe@285 297 UPDATE "issue" SET
jbe@285 298 "state" = 'calculation',
jbe@285 299 "closed" = now(),
jbe@285 300 "voter_count" = (
jbe@285 301 SELECT coalesce(sum("weight"), 0)
jbe@285 302 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
jbe@285 303 )
jbe@285 304 WHERE "id" = "issue_id_p";
jbe@285 305 -- materialize battle_view:
jbe@285 306 -- NOTE: "closed" column of issue must be set at this point
jbe@285 307 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
jbe@285 308 INSERT INTO "battle" (
jbe@285 309 "issue_id",
jbe@285 310 "winning_initiative_id", "losing_initiative_id",
jbe@285 311 "count"
jbe@285 312 ) SELECT
jbe@285 313 "issue_id",
jbe@285 314 "winning_initiative_id", "losing_initiative_id",
jbe@285 315 "count"
jbe@285 316 FROM "battle_view" WHERE "issue_id" = "issue_id_p";
jbe@285 317 -- copy "positive_votes" and "negative_votes" from "battle" table:
jbe@285 318 UPDATE "initiative" SET
jbe@285 319 "positive_votes" = "battle_win"."count",
jbe@285 320 "negative_votes" = "battle_lose"."count"
jbe@285 321 FROM "battle" AS "battle_win", "battle" AS "battle_lose"
jbe@285 322 WHERE
jbe@285 323 "battle_win"."issue_id" = "issue_id_p" AND
jbe@285 324 "battle_win"."winning_initiative_id" = "initiative"."id" AND
jbe@285 325 "battle_win"."losing_initiative_id" ISNULL AND
jbe@285 326 "battle_lose"."issue_id" = "issue_id_p" AND
jbe@285 327 "battle_lose"."losing_initiative_id" = "initiative"."id" AND
jbe@285 328 "battle_lose"."winning_initiative_id" ISNULL;
jbe@285 329 END;
jbe@285 330 $$;
jbe@285 331
jbe@288 332 CREATE OR REPLACE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE)
jbe@288 333 RETURNS VOID
jbe@288 334 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@288 335 DECLARE
jbe@288 336 "issue_row" "issue"%ROWTYPE;
jbe@288 337 BEGIN
jbe@288 338 SELECT * INTO "issue_row"
jbe@288 339 FROM "issue" WHERE "id" = "issue_id_p"
jbe@288 340 FOR UPDATE;
jbe@288 341 IF "issue_row"."cleaned" ISNULL THEN
jbe@288 342 UPDATE "issue" SET
jbe@288 343 "state" = 'voting',
jbe@288 344 "closed" = NULL,
jbe@288 345 "ranks_available" = FALSE
jbe@288 346 WHERE "id" = "issue_id_p";
jbe@288 347 DELETE FROM "delegating_voter"
jbe@288 348 WHERE "issue_id" = "issue_id_p";
jbe@288 349 DELETE FROM "direct_voter"
jbe@288 350 WHERE "issue_id" = "issue_id_p";
jbe@288 351 DELETE FROM "delegating_interest_snapshot"
jbe@288 352 WHERE "issue_id" = "issue_id_p";
jbe@288 353 DELETE FROM "direct_interest_snapshot"
jbe@288 354 WHERE "issue_id" = "issue_id_p";
jbe@288 355 DELETE FROM "delegating_population_snapshot"
jbe@288 356 WHERE "issue_id" = "issue_id_p";
jbe@288 357 DELETE FROM "direct_population_snapshot"
jbe@288 358 WHERE "issue_id" = "issue_id_p";
jbe@288 359 DELETE FROM "non_voter"
jbe@288 360 WHERE "issue_id" = "issue_id_p";
jbe@288 361 DELETE FROM "delegation"
jbe@288 362 WHERE "issue_id" = "issue_id_p";
jbe@288 363 DELETE FROM "supporter"
jbe@288 364 WHERE "issue_id" = "issue_id_p";
jbe@288 365 UPDATE "issue" SET
jbe@288 366 "state" = "issue_row"."state",
jbe@288 367 "closed" = "issue_row"."closed",
jbe@288 368 "ranks_available" = "issue_row"."ranks_available",
jbe@288 369 "cleaned" = now()
jbe@288 370 WHERE "id" = "issue_id_p";
jbe@288 371 END IF;
jbe@288 372 RETURN;
jbe@288 373 END;
jbe@288 374 $$;
jbe@288 375
jbe@287 376
jbe@287 377 -- "non_voter" deletes "direct_voter" and vice versa
jbe@287 378
jbe@287 379 CREATE FUNCTION "non_voter_deletes_direct_voter_trigger"()
jbe@287 380 RETURNS TRIGGER
jbe@287 381 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@287 382 BEGIN
jbe@287 383 DELETE FROM "direct_voter"
jbe@287 384 WHERE "issue_id" = NEW."issue_id" AND "member_id" = NEW."member_id";
jbe@287 385 RETURN NULL;
jbe@287 386 END;
jbe@287 387 $$;
jbe@287 388
jbe@287 389 CREATE TRIGGER "non_voter_deletes_direct_voter"
jbe@287 390 AFTER INSERT OR UPDATE ON "non_voter"
jbe@287 391 FOR EACH ROW EXECUTE PROCEDURE
jbe@287 392 "non_voter_deletes_direct_voter_trigger"();
jbe@287 393
jbe@287 394 COMMENT ON FUNCTION "non_voter_deletes_direct_voter_trigger"() IS 'Implementation of trigger "non_voter_deletes_direct_voter" on table "non_voter"';
jbe@287 395 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 396
jbe@287 397 CREATE FUNCTION "direct_voter_deletes_non_voter_trigger"()
jbe@287 398 RETURNS TRIGGER
jbe@287 399 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@287 400 BEGIN
jbe@287 401 DELETE FROM "non_voter"
jbe@287 402 WHERE "issue_id" = NEW."issue_id" AND "member_id" = NEW."member_id";
jbe@287 403 RETURN NULL;
jbe@287 404 END;
jbe@287 405 $$;
jbe@287 406
jbe@287 407 CREATE TRIGGER "direct_voter_deletes_non_voter"
jbe@287 408 AFTER INSERT OR UPDATE ON "direct_voter"
jbe@287 409 FOR EACH ROW EXECUTE PROCEDURE
jbe@287 410 "direct_voter_deletes_non_voter_trigger"();
jbe@287 411
jbe@287 412 COMMENT ON FUNCTION "direct_voter_deletes_non_voter_trigger"() IS 'Implementation of trigger "direct_voter_deletes_non_voter" on table "direct_voter"';
jbe@287 413 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 414
jbe@287 415
jbe@287 416 -- new comment on function "delete_private_data"()
jbe@287 417
jbe@283 418 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 419
jbe@287 420
jbe@262 421 COMMIT;

Impressum / About Us