liquid_feedback_core

annotate update/core-update.v1.3.1-v1.4.0_rc1.sql @ 496:044af1eec28b

find changesets by author, revision, files, or words in the commit message
New table "newsletter"
author jbe
date Sun Apr 03 20:46:10 2016 +0200 (2016-04-03)
parents eee75cff3e5a
children
rev   line source
jbe@115 1 BEGIN; -- NOTE: file contains additional statements AFTER this BEGIN/COMMIT block!
jbe@115 2
jbe@115 3
jbe@115 4 -- Update version information:
jbe@115 5
jbe@115 6 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
jbe@115 7 SELECT * FROM (VALUES ('1.4.0_rc1', 1, 4, -1))
jbe@115 8 AS "subquery"("string", "major", "minor", "revision");
jbe@115 9
jbe@115 10
jbe@115 11 -- New columns "notify_level" and "notify_event_id" in "member" table:
jbe@115 12
jbe@115 13 CREATE TYPE "notify_level" AS ENUM
jbe@115 14 ('none', 'voting', 'verification', 'discussion', 'all');
jbe@115 15
jbe@115 16 COMMENT ON TYPE "notify_level" IS 'Level of notification: ''none'' = no notifications, ''voting'' = notifications about finished issues and issues in voting, ''verification'' = notifications about finished issues, issues in voting and verification phase, ''discussion'' = notifications about everything except issues in admission phase, ''all'' = notifications about everything';
jbe@115 17
jbe@115 18 ALTER TABLE "member" ADD "notify_level" "notify_level" NOT NULL DEFAULT 'none';
jbe@115 19 ALTER TABLE "member" ADD "notify_event_id" INT8;
jbe@115 20
jbe@115 21 COMMENT ON COLUMN "member"."notify_level" IS 'Selects which event notifications are to be sent to the "notify_email" mail address';
jbe@115 22 COMMENT ON COLUMN "member"."notify_event_id" IS 'Latest "id" of an "event" the member was notified about';
jbe@115 23
jbe@115 24
jbe@115 25 -- Add primary key with type SERIAL8 (INT8) for "invite_code" table:
jbe@115 26
jbe@115 27 ALTER TABLE "invite_code" DROP CONSTRAINT "invite_code_pkey";
jbe@115 28 ALTER TABLE "invite_code" ALTER "code" SET NOT NULL;
jbe@115 29 ALTER TABLE "invite_code" ADD UNIQUE ("code");
jbe@115 30 ALTER TABLE "invite_code" ADD "id" SERIAL8 PRIMARY KEY;
jbe@115 31
jbe@115 32
jbe@115 33 -- Add index for "other_member_id" column of "contact" table:
jbe@115 34
jbe@115 35 CREATE INDEX "contact_other_member_id_idx" ON "contact" ("other_member_id");
jbe@115 36
jbe@115 37
jbe@115 38 -- New table "ignored_member":
jbe@115 39
jbe@115 40 CREATE TABLE "ignored_member" (
jbe@115 41 PRIMARY KEY ("member_id", "other_member_id"),
jbe@115 42 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@115 43 "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
jbe@115 44 CREATE INDEX "ignored_member_other_member_id_idx" ON "ignored_member" ("other_member_id");
jbe@115 45
jbe@115 46 COMMENT ON TABLE "ignored_member" IS 'Possibility to filter other members';
jbe@115 47
jbe@115 48 COMMENT ON COLUMN "ignored_member"."member_id" IS 'Member ignoring someone';
jbe@115 49 COMMENT ON COLUMN "ignored_member"."other_member_id" IS 'Member being ignored';
jbe@115 50
jbe@115 51
jbe@115 52 -- New table "unit" with default entry:
jbe@115 53
jbe@115 54 CREATE TABLE "unit" (
jbe@115 55 "id" SERIAL4 PRIMARY KEY,
jbe@115 56 "parent_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@115 57 "active" BOOLEAN NOT NULL DEFAULT TRUE,
jbe@115 58 "name" TEXT NOT NULL,
jbe@115 59 "description" TEXT NOT NULL DEFAULT '',
jbe@115 60 "member_count" INT4,
jbe@115 61 "text_search_data" TSVECTOR );
jbe@115 62 CREATE INDEX "unit_root_idx" ON "unit" ("id") WHERE "parent_id" ISNULL;
jbe@115 63 CREATE INDEX "unit_parent_id_idx" ON "unit" ("parent_id");
jbe@115 64 CREATE INDEX "unit_active_idx" ON "unit" ("active");
jbe@115 65 CREATE INDEX "unit_text_search_data_idx" ON "unit" USING gin ("text_search_data");
jbe@115 66 CREATE TRIGGER "update_text_search_data"
jbe@115 67 BEFORE INSERT OR UPDATE ON "unit"
jbe@115 68 FOR EACH ROW EXECUTE PROCEDURE
jbe@115 69 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
jbe@115 70 "name", "description" );
jbe@115 71
jbe@115 72 COMMENT ON TABLE "unit" IS 'Organizational units organized as trees; Delegations are not inherited through these trees.';
jbe@115 73
jbe@115 74 COMMENT ON COLUMN "unit"."parent_id" IS 'Parent id of tree node; Multiple roots allowed';
jbe@115 75 COMMENT ON COLUMN "unit"."active" IS 'TRUE means new issues can be created in units of this area';
jbe@115 76 COMMENT ON COLUMN "unit"."member_count" IS 'Count of members as determined by column "voting_right" in table "privilege"';
jbe@115 77
jbe@115 78 INSERT INTO "unit" ("name") VALUES ('Main'); -- NOTE: gets id 1
jbe@115 79
jbe@115 80
jbe@115 81 -- New column "unit_id" in table "area":
jbe@115 82
jbe@115 83 ALTER TABLE "area" ADD "unit_id" INT4 DEFAULT 1
jbe@115 84 NOT NULL REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
jbe@115 85 ALTER TABLE "area" ALTER "unit_id" DROP DEFAULT;
jbe@115 86
jbe@115 87 CREATE INDEX "area_unit_id_idx" ON "area" ("unit_id");
jbe@115 88
jbe@115 89
jbe@115 90 -- Issue states:
jbe@115 91
jbe@115 92 CREATE TYPE "issue_state" AS ENUM (
jbe@115 93 'admission', 'discussion', 'verification', 'voting',
jbe@115 94 'canceled_revoked_before_accepted',
jbe@115 95 'canceled_issue_not_accepted',
jbe@115 96 'canceled_after_revocation_during_discussion',
jbe@115 97 'canceled_after_revocation_during_verification',
jbe@115 98 'calculation',
jbe@115 99 'canceled_no_initiative_admitted',
jbe@115 100 'finished_without_winner', 'finished_with_winner');
jbe@115 101
jbe@115 102 COMMENT ON TYPE "issue_state" IS 'State of issues';
jbe@115 103
jbe@205 104 ALTER TABLE "issue" ADD "state" "issue_state" DEFAULT NULL;
jbe@205 105 ALTER TABLE "issue" ALTER "state" SET DEFAULT 'admission';
jbe@115 106
jbe@115 107 -- NOTE: Filling new column with values is done after this transaction (see below)
jbe@115 108
jbe@115 109
jbe@115 110 -- New column "revoked_by_member_id" in table "initiative":
jbe@115 111
jbe@115 112 ALTER TABLE "initiative" ADD "revoked_by_member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE;
jbe@115 113
jbe@115 114 COMMENT ON COLUMN "initiative"."revoked_by_member_id" IS 'Member, who decided to revoked the initiative';
jbe@115 115
jbe@115 116 -- NOTE: Filling new column with values is done after this transaction (see below)
jbe@115 117
jbe@115 118
jbe@115 119 -- New table "ignored_initiative":
jbe@115 120
jbe@115 121 CREATE TABLE "ignored_initiative" (
jbe@115 122 PRIMARY KEY ("initiative_id", "member_id"),
jbe@115 123 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@115 124 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
jbe@115 125 CREATE INDEX "ignored_initiative_member_id_idx" ON "ignored_initiative" ("member_id");
jbe@115 126
jbe@115 127 COMMENT ON TABLE "ignored_initiative" IS 'Possibility to filter initiatives';
jbe@115 128
jbe@115 129
jbe@115 130 -- New table "invite_code_unit":
jbe@115 131
jbe@115 132 CREATE TABLE "invite_code_unit" (
jbe@115 133 PRIMARY KEY ("invite_code_id", "unit_id"),
jbe@115 134 "invite_code_id" INT8 REFERENCES "invite_code" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@115 135 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
jbe@115 136
jbe@115 137 COMMENT ON TABLE "invite_code_unit" IS 'Units where accounts created with a given invite codes get voting rights';
jbe@115 138
jbe@117 139 INSERT INTO "invite_code_unit" ("invite_code_id", "unit_id")
jbe@124 140 SELECT "id" AS "invite_code_id", 1 AS "unit_id" FROM "invite_code";
jbe@115 141
jbe@115 142
jbe@115 143 -- New table "privilege":
jbe@115 144
jbe@115 145 CREATE TABLE "privilege" (
jbe@115 146 PRIMARY KEY ("unit_id", "member_id"),
jbe@115 147 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@115 148 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@115 149 "admin_manager" BOOLEAN NOT NULL DEFAULT FALSE,
jbe@115 150 "unit_manager" BOOLEAN NOT NULL DEFAULT FALSE,
jbe@115 151 "area_manager" BOOLEAN NOT NULL DEFAULT FALSE,
jbe@115 152 "voting_right_manager" BOOLEAN NOT NULL DEFAULT FALSE,
jbe@115 153 "voting_right" BOOLEAN NOT NULL DEFAULT TRUE );
jbe@115 154
jbe@115 155 COMMENT ON TABLE "privilege" IS 'Members rights related to each unit';
jbe@115 156
jbe@115 157 COMMENT ON COLUMN "privilege"."admin_manager" IS 'Grant/revoke admin privileges to/from other users';
jbe@115 158 COMMENT ON COLUMN "privilege"."unit_manager" IS 'Create or lock sub units';
jbe@115 159 COMMENT ON COLUMN "privilege"."area_manager" IS 'Create or lock areas and set area parameters';
jbe@115 160 COMMENT ON COLUMN "privilege"."voting_right_manager" IS 'Select which members are allowed to discuss and vote inside the unit';
jbe@115 161 COMMENT ON COLUMN "privilege"."voting_right" IS 'Right to discuss and vote';
jbe@115 162
jbe@124 163 INSERT INTO "privilege" ("unit_id", "member_id", "voting_right")
jbe@124 164 SELECT 1 AS "unit_id", "id" AS "member_id", TRUE AS "voting_right"
jbe@124 165 FROM "member";
jbe@124 166
jbe@115 167
jbe@115 168 -- Remove table "ignored_issue", which is no longer existent:
jbe@115 169
jbe@115 170 DROP TABLE "ignored_issue";
jbe@115 171
jbe@115 172
jbe@115 173 -- Replace TYPE "delegation_scope" with a new type, where 'global' is replaced by 'unit':
jbe@115 174
jbe@115 175 ALTER TYPE "delegation_scope" RENAME TO "delegation_scope_old"; -- NOTE: dropped later
jbe@115 176 CREATE TYPE "delegation_scope" AS ENUM ('unit', 'area', 'issue');
jbe@115 177 COMMENT ON TYPE "delegation_scope" IS 'Scope for delegations: ''unit'', ''area'', or ''issue'' (order is relevant)';
jbe@115 178
jbe@115 179
jbe@122 180 -- Delete views and functions being dependent on type "delegation_scope":
jbe@115 181
jbe@115 182 DROP FUNCTION "delegation_chain"
jbe@115 183 ( "member_id_p" "member"."id"%TYPE,
jbe@115 184 "area_id_p" "area"."id"%TYPE,
jbe@115 185 "issue_id_p" "issue"."id"%TYPE );
jbe@115 186
jbe@115 187 DROP FUNCTION "delegation_chain"
jbe@115 188 ( "member_id_p" "member"."id"%TYPE,
jbe@115 189 "area_id_p" "area"."id"%TYPE,
jbe@115 190 "issue_id_p" "issue"."id"%TYPE,
jbe@115 191 "simulate_trustee_id_p" "member"."id"%TYPE );
jbe@115 192
jbe@115 193 DROP TYPE "delegation_chain_row";
jbe@115 194
jbe@115 195 DROP VIEW "issue_delegation";
jbe@115 196 DROP VIEW "area_delegation";
jbe@115 197 DROP VIEW "global_delegation";
jbe@115 198 DROP VIEW "active_delegation";
jbe@115 199
jbe@115 200
jbe@122 201 -- Modify "delegation" table to use new "delegation_scope" type:
jbe@115 202
jbe@115 203 ALTER TABLE "delegation" DROP CONSTRAINT "no_global_delegation_to_null";
jbe@115 204 ALTER TABLE "delegation" DROP CONSTRAINT "area_id_and_issue_id_set_according_to_scope";
jbe@115 205
jbe@115 206 DROP INDEX "delegation_global_truster_id_unique_idx";
jbe@115 207
jbe@115 208 ALTER TABLE "delegation" ALTER "scope" TYPE "delegation_scope"
jbe@115 209 USING CASE WHEN "scope" = 'global'
jbe@115 210 THEN 'unit'::"delegation_scope"
jbe@115 211 ELSE "scope"::text::"delegation_scope" END;
jbe@115 212
jbe@115 213 ALTER TABLE "delegation" ADD "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
jbe@115 214
jbe@115 215 ALTER TABLE "delegation" ADD CONSTRAINT "no_unit_delegation_to_null"
jbe@115 216 CHECK ("trustee_id" NOTNULL OR "scope" != 'unit');
jbe@115 217
jbe@115 218 ALTER TABLE "delegation" ADD UNIQUE ("unit_id", "truster_id");
jbe@115 219
jbe@115 220 COMMENT ON COLUMN "delegation"."unit_id" IS 'Reference to unit, if delegation is unit-wide, otherwise NULL';
jbe@115 221
jbe@115 222 -- NOTE: Column "unit_id" filled after transaction (see below)
jbe@115 223
jbe@115 224
jbe@122 225 -- Modify snapshot tables to use new "delegation_scope" type:
jbe@115 226
jbe@115 227 ALTER TABLE "delegating_population_snapshot" ALTER "scope" TYPE "delegation_scope"
jbe@115 228 USING CASE WHEN "scope" = 'global'
jbe@115 229 THEN 'unit'::"delegation_scope"
jbe@115 230 ELSE "scope"::text::"delegation_scope" END;
jbe@115 231
jbe@115 232 ALTER TABLE "delegating_interest_snapshot" ALTER "scope" TYPE "delegation_scope"
jbe@115 233 USING CASE WHEN "scope" = 'global'
jbe@115 234 THEN 'unit'::"delegation_scope"
jbe@115 235 ELSE "scope"::text::"delegation_scope" END;
jbe@115 236
jbe@115 237 ALTER TABLE "delegating_voter" ALTER "scope" TYPE "delegation_scope"
jbe@115 238 USING CASE WHEN "scope" = 'global'
jbe@115 239 THEN 'unit'::"delegation_scope"
jbe@115 240 ELSE "scope"::text::"delegation_scope" END;
jbe@115 241
jbe@115 242
jbe@115 243 -- New table "non_voter":
jbe@115 244
jbe@115 245 CREATE TABLE "non_voter" (
jbe@115 246 PRIMARY KEY ("issue_id", "member_id"),
jbe@115 247 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@115 248 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
jbe@115 249 CREATE INDEX "non_voter_member_id_idx" ON "non_voter" ("member_id");
jbe@115 250
jbe@115 251 COMMENT ON TABLE "non_voter" IS 'Members who decided to not vote directly on an issue';
jbe@115 252
jbe@115 253
jbe@122 254 -- New tables "issue_comment" and "rendered_issue_comment":
jbe@115 255
jbe@115 256 CREATE TABLE "issue_comment" (
jbe@115 257 PRIMARY KEY ("issue_id", "member_id"),
jbe@115 258 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@115 259 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@115 260 "changed" TIMESTAMPTZ NOT NULL DEFAULT now(),
jbe@115 261 "formatting_engine" TEXT,
jbe@115 262 "content" TEXT NOT NULL,
jbe@115 263 "text_search_data" TSVECTOR );
jbe@115 264 CREATE INDEX "issue_comment_member_id_idx" ON "issue_comment" ("member_id");
jbe@115 265 CREATE INDEX "issue_comment_text_search_data_idx" ON "issue_comment" USING gin ("text_search_data");
jbe@115 266 CREATE TRIGGER "update_text_search_data"
jbe@115 267 BEFORE INSERT OR UPDATE ON "issue_comment"
jbe@115 268 FOR EACH ROW EXECUTE PROCEDURE
jbe@115 269 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "content");
jbe@115 270
jbe@115 271 COMMENT ON TABLE "issue_comment" IS 'Place to store free comments of members related to issues';
jbe@115 272
jbe@115 273 COMMENT ON COLUMN "issue_comment"."changed" IS 'Time the comment was last changed';
jbe@115 274
jbe@115 275 CREATE TABLE "rendered_issue_comment" (
jbe@115 276 PRIMARY KEY ("issue_id", "member_id", "format"),
jbe@115 277 FOREIGN KEY ("issue_id", "member_id")
jbe@115 278 REFERENCES "issue_comment" ("issue_id", "member_id")
jbe@115 279 ON DELETE CASCADE ON UPDATE CASCADE,
jbe@115 280 "issue_id" INT4,
jbe@115 281 "member_id" INT4,
jbe@115 282 "format" TEXT,
jbe@115 283 "content" TEXT NOT NULL );
jbe@115 284
jbe@115 285 COMMENT ON TABLE "rendered_issue_comment" IS 'This table may be used by frontends to cache "rendered" issue comments (e.g. HTML output generated from wiki text)';
jbe@115 286
jbe@115 287
jbe@122 288 -- New tables "voting_comment" and "rendered_voting_comment":
jbe@115 289
jbe@115 290 CREATE TABLE "voting_comment" (
jbe@115 291 PRIMARY KEY ("issue_id", "member_id"),
jbe@115 292 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@115 293 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@115 294 "changed" TIMESTAMPTZ,
jbe@115 295 "formatting_engine" TEXT,
jbe@115 296 "content" TEXT NOT NULL,
jbe@115 297 "text_search_data" TSVECTOR );
jbe@115 298 CREATE INDEX "voting_comment_member_id_idx" ON "voting_comment" ("member_id");
jbe@115 299 CREATE INDEX "voting_comment_text_search_data_idx" ON "voting_comment" USING gin ("text_search_data");
jbe@115 300 CREATE TRIGGER "update_text_search_data"
jbe@115 301 BEFORE INSERT OR UPDATE ON "voting_comment"
jbe@115 302 FOR EACH ROW EXECUTE PROCEDURE
jbe@115 303 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "content");
jbe@115 304
jbe@115 305 COMMENT ON TABLE "voting_comment" IS 'Storage for comments of voters to be published after voting has finished.';
jbe@115 306
jbe@115 307 COMMENT ON COLUMN "voting_comment"."changed" 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@115 308
jbe@115 309 CREATE TABLE "rendered_voting_comment" (
jbe@115 310 PRIMARY KEY ("issue_id", "member_id", "format"),
jbe@115 311 FOREIGN KEY ("issue_id", "member_id")
jbe@115 312 REFERENCES "voting_comment" ("issue_id", "member_id")
jbe@115 313 ON DELETE CASCADE ON UPDATE CASCADE,
jbe@115 314 "issue_id" INT4,
jbe@115 315 "member_id" INT4,
jbe@115 316 "format" TEXT,
jbe@115 317 "content" TEXT NOT NULL );
jbe@115 318
jbe@115 319 COMMENT ON TABLE "rendered_voting_comment" IS 'This table may be used by frontends to cache "rendered" voting comments (e.g. HTML output generated from wiki text)';
jbe@115 320
jbe@115 321
jbe@115 322 -- New table "event":
jbe@115 323
jbe@115 324 CREATE TYPE "event_type" AS ENUM (
jbe@115 325 'issue_state_changed',
jbe@115 326 'initiative_created_in_new_issue',
jbe@115 327 'initiative_created_in_existing_issue',
jbe@115 328 'initiative_revoked',
jbe@115 329 'new_draft_created',
jbe@115 330 'suggestion_created');
jbe@115 331
jbe@115 332 COMMENT ON TYPE "event_type" IS 'Type used for column "event" of table "event"';
jbe@115 333
jbe@115 334 CREATE TABLE "event" (
jbe@115 335 "id" SERIAL8 PRIMARY KEY,
jbe@115 336 "occurrence" TIMESTAMPTZ NOT NULL DEFAULT now(),
jbe@115 337 "event" "event_type" NOT NULL,
jbe@115 338 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
jbe@115 339 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@115 340 "state" "issue_state" CHECK ("state" != 'calculation'),
jbe@115 341 "initiative_id" INT4,
jbe@115 342 "draft_id" INT8,
jbe@115 343 "suggestion_id" INT8,
jbe@115 344 FOREIGN KEY ("issue_id", "initiative_id")
jbe@115 345 REFERENCES "initiative" ("issue_id", "id")
jbe@115 346 ON DELETE CASCADE ON UPDATE CASCADE,
jbe@115 347 FOREIGN KEY ("initiative_id", "draft_id")
jbe@115 348 REFERENCES "draft" ("initiative_id", "id")
jbe@115 349 ON DELETE CASCADE ON UPDATE CASCADE,
jbe@115 350 FOREIGN KEY ("initiative_id", "suggestion_id")
jbe@115 351 REFERENCES "suggestion" ("initiative_id", "id")
jbe@115 352 ON DELETE CASCADE ON UPDATE CASCADE,
jbe@115 353 CONSTRAINT "null_constraints_for_issue_state_changed" CHECK (
jbe@115 354 "event" != 'issue_state_changed' OR (
jbe@115 355 "member_id" ISNULL AND
jbe@115 356 "issue_id" NOTNULL AND
jbe@115 357 "state" NOTNULL AND
jbe@115 358 "initiative_id" ISNULL AND
jbe@115 359 "draft_id" ISNULL AND
jbe@115 360 "suggestion_id" ISNULL )),
jbe@115 361 CONSTRAINT "null_constraints_for_initiative_creation_or_revocation_or_new_draft" CHECK (
jbe@115 362 "event" NOT IN (
jbe@115 363 'initiative_created_in_new_issue',
jbe@115 364 'initiative_created_in_existing_issue',
jbe@115 365 'initiative_revoked',
jbe@115 366 'new_draft_created'
jbe@115 367 ) OR (
jbe@115 368 "member_id" NOTNULL AND
jbe@115 369 "issue_id" NOTNULL AND
jbe@115 370 "state" NOTNULL AND
jbe@115 371 "initiative_id" NOTNULL AND
jbe@115 372 "draft_id" NOTNULL AND
jbe@115 373 "suggestion_id" ISNULL )),
jbe@115 374 CONSTRAINT "null_constraints_for_suggestion_creation" CHECK (
jbe@115 375 "event" != 'suggestion_created' OR (
jbe@115 376 "member_id" NOTNULL AND
jbe@115 377 "issue_id" NOTNULL AND
jbe@115 378 "state" NOTNULL AND
jbe@115 379 "initiative_id" NOTNULL AND
jbe@115 380 "draft_id" ISNULL AND
jbe@115 381 "suggestion_id" NOTNULL )) );
jbe@115 382
jbe@115 383 COMMENT ON TABLE "event" IS 'Event table, automatically filled by triggers';
jbe@115 384
jbe@115 385 COMMENT ON COLUMN "event"."occurrence" IS 'Point in time, when event occurred';
jbe@115 386 COMMENT ON COLUMN "event"."event" IS 'Type of event (see TYPE "event_type")';
jbe@115 387 COMMENT ON COLUMN "event"."member_id" IS 'Member who caused the event, if applicable';
jbe@115 388 COMMENT ON COLUMN "event"."state" IS 'If issue_id is set: state of affected issue; If state changed: new state';
jbe@115 389
jbe@115 390
jbe@115 391 -- Triggers to fill "event" table:
jbe@115 392
jbe@115 393 CREATE FUNCTION "write_event_issue_state_changed_trigger"()
jbe@115 394 RETURNS TRIGGER
jbe@115 395 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@115 396 BEGIN
jbe@115 397 IF NEW."state" != OLD."state" AND NEW."state" != 'calculation' THEN
jbe@115 398 INSERT INTO "event" ("event", "issue_id", "state")
jbe@115 399 VALUES ('issue_state_changed', NEW."id", NEW."state");
jbe@115 400 END IF;
jbe@115 401 RETURN NULL;
jbe@115 402 END;
jbe@115 403 $$;
jbe@115 404
jbe@115 405 CREATE TRIGGER "write_event_issue_state_changed"
jbe@115 406 AFTER UPDATE ON "issue" FOR EACH ROW EXECUTE PROCEDURE
jbe@115 407 "write_event_issue_state_changed_trigger"();
jbe@115 408
jbe@115 409 COMMENT ON FUNCTION "write_event_issue_state_changed_trigger"() IS 'Implementation of trigger "write_event_issue_state_changed" on table "issue"';
jbe@115 410 COMMENT ON TRIGGER "write_event_issue_state_changed" ON "issue" IS 'Create entry in "event" table on "state" change';
jbe@115 411
jbe@115 412 CREATE FUNCTION "write_event_initiative_or_draft_created_trigger"()
jbe@115 413 RETURNS TRIGGER
jbe@115 414 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@115 415 DECLARE
jbe@115 416 "initiative_row" "initiative"%ROWTYPE;
jbe@115 417 "issue_row" "issue"%ROWTYPE;
jbe@115 418 "event_v" "event_type";
jbe@115 419 BEGIN
jbe@115 420 SELECT * INTO "initiative_row" FROM "initiative"
jbe@115 421 WHERE "id" = NEW."initiative_id";
jbe@115 422 SELECT * INTO "issue_row" FROM "issue"
jbe@115 423 WHERE "id" = "initiative_row"."issue_id";
jbe@115 424 IF EXISTS (
jbe@115 425 SELECT NULL FROM "draft"
jbe@115 426 WHERE "initiative_id" = NEW."initiative_id"
jbe@115 427 AND "id" != NEW."id"
jbe@115 428 ) THEN
jbe@115 429 "event_v" := 'new_draft_created';
jbe@115 430 ELSE
jbe@115 431 IF EXISTS (
jbe@115 432 SELECT NULL FROM "initiative"
jbe@115 433 WHERE "issue_id" = "initiative_row"."issue_id"
jbe@115 434 AND "id" != "initiative_row"."id"
jbe@115 435 ) THEN
jbe@115 436 "event_v" := 'initiative_created_in_existing_issue';
jbe@115 437 ELSE
jbe@115 438 "event_v" := 'initiative_created_in_new_issue';
jbe@115 439 END IF;
jbe@115 440 END IF;
jbe@115 441 INSERT INTO "event" (
jbe@115 442 "event", "member_id",
jbe@115 443 "issue_id", "state", "initiative_id", "draft_id"
jbe@115 444 ) VALUES (
jbe@115 445 "event_v",
jbe@115 446 NEW."author_id",
jbe@115 447 "initiative_row"."issue_id",
jbe@115 448 "issue_row"."state",
jbe@115 449 "initiative_row"."id",
jbe@115 450 NEW."id" );
jbe@115 451 RETURN NULL;
jbe@115 452 END;
jbe@115 453 $$;
jbe@115 454
jbe@115 455 CREATE TRIGGER "write_event_initiative_or_draft_created"
jbe@115 456 AFTER INSERT ON "draft" FOR EACH ROW EXECUTE PROCEDURE
jbe@115 457 "write_event_initiative_or_draft_created_trigger"();
jbe@115 458
jbe@115 459 COMMENT ON FUNCTION "write_event_initiative_or_draft_created_trigger"() IS 'Implementation of trigger "write_event_initiative_or_draft_created" on table "issue"';
jbe@115 460 COMMENT ON TRIGGER "write_event_initiative_or_draft_created" ON "draft" IS 'Create entry in "event" table on draft creation';
jbe@115 461
jbe@115 462 CREATE FUNCTION "write_event_initiative_revoked_trigger"()
jbe@115 463 RETURNS TRIGGER
jbe@115 464 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@115 465 DECLARE
jbe@115 466 "issue_row" "issue"%ROWTYPE;
jbe@115 467 BEGIN
jbe@115 468 SELECT * INTO "issue_row" FROM "issue"
jbe@115 469 WHERE "id" = NEW."issue_id";
jbe@115 470 IF OLD."revoked" ISNULL AND NEW."revoked" NOTNULL THEN
jbe@115 471 INSERT INTO "event" (
jbe@115 472 "event", "member_id", "issue_id", "state", "initiative_id"
jbe@115 473 ) VALUES (
jbe@115 474 'initiative_revoked',
jbe@115 475 NEW."revoked_by_member_id",
jbe@115 476 NEW."issue_id",
jbe@115 477 "issue_row"."state",
jbe@115 478 NEW."id" );
jbe@115 479 END IF;
jbe@115 480 RETURN NULL;
jbe@115 481 END;
jbe@115 482 $$;
jbe@115 483
jbe@115 484 CREATE TRIGGER "write_event_initiative_revoked"
jbe@115 485 AFTER UPDATE ON "initiative" FOR EACH ROW EXECUTE PROCEDURE
jbe@115 486 "write_event_initiative_revoked_trigger"();
jbe@115 487
jbe@115 488 COMMENT ON FUNCTION "write_event_initiative_revoked_trigger"() IS 'Implementation of trigger "write_event_initiative_revoked" on table "issue"';
jbe@115 489 COMMENT ON TRIGGER "write_event_initiative_revoked" ON "initiative" IS 'Create entry in "event" table, when an initiative is revoked';
jbe@115 490
jbe@115 491 CREATE FUNCTION "write_event_suggestion_created_trigger"()
jbe@115 492 RETURNS TRIGGER
jbe@115 493 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@115 494 DECLARE
jbe@115 495 "initiative_row" "initiative"%ROWTYPE;
jbe@115 496 "issue_row" "issue"%ROWTYPE;
jbe@115 497 BEGIN
jbe@115 498 SELECT * INTO "initiative_row" FROM "initiative"
jbe@115 499 WHERE "id" = NEW."initiative_id";
jbe@115 500 SELECT * INTO "issue_row" FROM "issue"
jbe@115 501 WHERE "id" = "initiative_row"."issue_id";
jbe@115 502 INSERT INTO "event" (
jbe@115 503 "event", "member_id",
jbe@115 504 "issue_id", "state", "initiative_id", "suggestion_id"
jbe@115 505 ) VALUES (
jbe@115 506 'suggestion_created',
jbe@115 507 NEW."author_id",
jbe@115 508 "initiative_row"."issue_id",
jbe@115 509 "issue_row"."state",
jbe@115 510 "initiative_row"."id",
jbe@115 511 NEW."id" );
jbe@115 512 RETURN NULL;
jbe@115 513 END;
jbe@115 514 $$;
jbe@115 515
jbe@115 516 CREATE TRIGGER "write_event_suggestion_created"
jbe@115 517 AFTER INSERT ON "suggestion" FOR EACH ROW EXECUTE PROCEDURE
jbe@115 518 "write_event_suggestion_created_trigger"();
jbe@115 519
jbe@115 520 COMMENT ON FUNCTION "write_event_suggestion_created_trigger"() IS 'Implementation of trigger "write_event_suggestion_created" on table "issue"';
jbe@115 521 COMMENT ON TRIGGER "write_event_suggestion_created" ON "suggestion" IS 'Create entry in "event" table on suggestion creation';
jbe@115 522
jbe@115 523
jbe@115 524 -- Modified views:
jbe@115 525
jbe@115 526 CREATE VIEW "unit_delegation" AS
jbe@115 527 SELECT
jbe@115 528 "unit"."id" AS "unit_id",
jbe@115 529 "delegation"."id",
jbe@115 530 "delegation"."truster_id",
jbe@115 531 "delegation"."trustee_id",
jbe@115 532 "delegation"."scope"
jbe@115 533 FROM "unit"
jbe@115 534 JOIN "delegation"
jbe@115 535 ON "delegation"."unit_id" = "unit"."id"
jbe@115 536 JOIN "member"
jbe@115 537 ON "delegation"."truster_id" = "member"."id"
jbe@115 538 JOIN "privilege"
jbe@115 539 ON "delegation"."unit_id" = "privilege"."unit_id"
jbe@115 540 AND "delegation"."truster_id" = "privilege"."member_id"
jbe@115 541 WHERE "member"."active" AND "privilege"."voting_right";
jbe@115 542
jbe@115 543 COMMENT ON VIEW "unit_delegation" IS 'Unit delegations where trusters are active and have voting right';
jbe@115 544
jbe@115 545 CREATE VIEW "area_delegation" AS
jbe@115 546 SELECT DISTINCT ON ("area"."id", "delegation"."truster_id")
jbe@115 547 "area"."id" AS "area_id",
jbe@115 548 "delegation"."id",
jbe@115 549 "delegation"."truster_id",
jbe@115 550 "delegation"."trustee_id",
jbe@115 551 "delegation"."scope"
jbe@115 552 FROM "area"
jbe@115 553 JOIN "delegation"
jbe@115 554 ON "delegation"."unit_id" = "area"."unit_id"
jbe@115 555 OR "delegation"."area_id" = "area"."id"
jbe@115 556 JOIN "member"
jbe@115 557 ON "delegation"."truster_id" = "member"."id"
jbe@115 558 JOIN "privilege"
jbe@115 559 ON "area"."unit_id" = "privilege"."unit_id"
jbe@115 560 AND "delegation"."truster_id" = "privilege"."member_id"
jbe@115 561 WHERE "member"."active" AND "privilege"."voting_right"
jbe@115 562 ORDER BY
jbe@115 563 "area"."id",
jbe@115 564 "delegation"."truster_id",
jbe@115 565 "delegation"."scope" DESC;
jbe@115 566
jbe@115 567 COMMENT ON VIEW "area_delegation" IS 'Area delegations where trusters are active and have voting right';
jbe@115 568
jbe@115 569 CREATE VIEW "issue_delegation" AS
jbe@115 570 SELECT DISTINCT ON ("issue"."id", "delegation"."truster_id")
jbe@115 571 "issue"."id" AS "issue_id",
jbe@115 572 "delegation"."id",
jbe@115 573 "delegation"."truster_id",
jbe@115 574 "delegation"."trustee_id",
jbe@115 575 "delegation"."scope"
jbe@115 576 FROM "issue"
jbe@115 577 JOIN "area"
jbe@115 578 ON "area"."id" = "issue"."area_id"
jbe@115 579 JOIN "delegation"
jbe@115 580 ON "delegation"."unit_id" = "area"."unit_id"
jbe@115 581 OR "delegation"."area_id" = "area"."id"
jbe@115 582 OR "delegation"."issue_id" = "issue"."id"
jbe@115 583 JOIN "member"
jbe@115 584 ON "delegation"."truster_id" = "member"."id"
jbe@115 585 JOIN "privilege"
jbe@115 586 ON "area"."unit_id" = "privilege"."unit_id"
jbe@115 587 AND "delegation"."truster_id" = "privilege"."member_id"
jbe@115 588 WHERE "member"."active" AND "privilege"."voting_right"
jbe@115 589 ORDER BY
jbe@115 590 "issue"."id",
jbe@115 591 "delegation"."truster_id",
jbe@115 592 "delegation"."scope" DESC;
jbe@115 593
jbe@115 594 COMMENT ON VIEW "issue_delegation" IS 'Issue delegations where trusters are active and have voting right';
jbe@115 595
jbe@115 596 CREATE VIEW "unit_member_count" AS
jbe@115 597 SELECT
jbe@115 598 "unit"."id" AS "unit_id",
jbe@115 599 sum("member"."id") AS "member_count"
jbe@115 600 FROM "unit"
jbe@115 601 LEFT JOIN "privilege"
jbe@115 602 ON "privilege"."unit_id" = "unit"."id"
jbe@115 603 AND "privilege"."voting_right"
jbe@115 604 LEFT JOIN "member"
jbe@115 605 ON "member"."id" = "privilege"."member_id"
jbe@115 606 AND "member"."active"
jbe@115 607 GROUP BY "unit"."id";
jbe@115 608
jbe@115 609 COMMENT ON VIEW "unit_member_count" IS 'View used to update "member_count" column of "unit" table';
jbe@115 610
jbe@115 611 DROP VIEW "area_member_count";
jbe@115 612 CREATE VIEW "area_member_count" AS
jbe@115 613 SELECT
jbe@115 614 "area"."id" AS "area_id",
jbe@115 615 count("member"."id") AS "direct_member_count",
jbe@115 616 coalesce(
jbe@115 617 sum(
jbe@115 618 CASE WHEN "member"."id" NOTNULL THEN
jbe@115 619 "membership_weight"("area"."id", "member"."id")
jbe@115 620 ELSE 0 END
jbe@115 621 )
jbe@115 622 ) AS "member_weight",
jbe@115 623 coalesce(
jbe@115 624 sum(
jbe@115 625 CASE WHEN "member"."id" NOTNULL AND "membership"."autoreject" THEN
jbe@115 626 "membership_weight"("area"."id", "member"."id")
jbe@115 627 ELSE 0 END
jbe@115 628 )
jbe@115 629 ) AS "autoreject_weight"
jbe@115 630 FROM "area"
jbe@115 631 LEFT JOIN "membership"
jbe@115 632 ON "area"."id" = "membership"."area_id"
jbe@115 633 LEFT JOIN "privilege"
jbe@115 634 ON "privilege"."unit_id" = "area"."unit_id"
jbe@115 635 AND "privilege"."member_id" = "membership"."member_id"
jbe@115 636 AND "privilege"."voting_right"
jbe@115 637 LEFT JOIN "member"
jbe@115 638 ON "member"."id" = "privilege"."member_id" -- NOTE: no membership here!
jbe@115 639 AND "member"."active"
jbe@115 640 GROUP BY "area"."id";
jbe@115 641
jbe@115 642 COMMENT ON VIEW "area_member_count" IS 'View used to update "direct_member_count", "member_weight" and "autoreject_weight" columns of table "area"';
jbe@115 643
jbe@115 644
jbe@115 645 -- New view "event_seen_by_member":
jbe@115 646
jbe@115 647 CREATE VIEW "event_seen_by_member" AS
jbe@115 648 SELECT
jbe@115 649 "member"."id" AS "seen_by_member_id",
jbe@115 650 CASE WHEN "event"."state" IN (
jbe@115 651 'voting',
jbe@115 652 'finished_without_winner',
jbe@115 653 'finished_with_winner'
jbe@115 654 ) THEN
jbe@115 655 'voting'::"notify_level"
jbe@115 656 ELSE
jbe@115 657 CASE WHEN "event"."state" IN (
jbe@115 658 'verification',
jbe@115 659 'canceled_after_revocation_during_verification',
jbe@115 660 'canceled_no_initiative_admitted'
jbe@115 661 ) THEN
jbe@115 662 'verification'::"notify_level"
jbe@115 663 ELSE
jbe@115 664 CASE WHEN "event"."state" IN (
jbe@115 665 'discussion',
jbe@115 666 'canceled_after_revocation_during_discussion'
jbe@115 667 ) THEN
jbe@115 668 'discussion'::"notify_level"
jbe@115 669 ELSE
jbe@115 670 'all'::"notify_level"
jbe@115 671 END
jbe@115 672 END
jbe@115 673 END AS "notify_level",
jbe@115 674 "event".*
jbe@115 675 FROM "member" CROSS JOIN "event"
jbe@115 676 LEFT JOIN "issue"
jbe@115 677 ON "event"."issue_id" = "issue"."id"
jbe@115 678 LEFT JOIN "membership"
jbe@115 679 ON "member"."id" = "membership"."member_id"
jbe@115 680 AND "issue"."area_id" = "membership"."area_id"
jbe@115 681 LEFT JOIN "interest"
jbe@115 682 ON "member"."id" = "interest"."member_id"
jbe@115 683 AND "event"."issue_id" = "interest"."issue_id"
jbe@115 684 LEFT JOIN "supporter"
jbe@115 685 ON "member"."id" = "supporter"."member_id"
jbe@115 686 AND "event"."initiative_id" = "supporter"."initiative_id"
jbe@115 687 LEFT JOIN "ignored_member"
jbe@115 688 ON "member"."id" = "ignored_member"."member_id"
jbe@115 689 AND "event"."member_id" = "ignored_member"."other_member_id"
jbe@115 690 LEFT JOIN "ignored_initiative"
jbe@115 691 ON "member"."id" = "ignored_initiative"."member_id"
jbe@115 692 AND "event"."initiative_id" = "ignored_initiative"."initiative_id"
jbe@115 693 WHERE (
jbe@115 694 "supporter"."member_id" NOTNULL OR
jbe@115 695 "interest"."member_id" NOTNULL OR
jbe@115 696 ( "membership"."member_id" NOTNULL AND
jbe@115 697 "event"."event" IN (
jbe@115 698 'issue_state_changed',
jbe@115 699 'initiative_created_in_new_issue',
jbe@115 700 'initiative_created_in_existing_issue',
jbe@115 701 'initiative_revoked' ) ) )
jbe@115 702 AND "ignored_member"."member_id" ISNULL
jbe@115 703 AND "ignored_initiative"."member_id" ISNULL;
jbe@115 704
jbe@115 705 COMMENT ON VIEW "event_seen_by_member" IS 'Events as seen by a member, depending on its memberships, interests and support';
jbe@115 706
jbe@115 707
jbe@115 708 -- New view "pending_notification":
jbe@115 709
jbe@115 710 CREATE VIEW "pending_notification" AS
jbe@115 711 SELECT
jbe@115 712 "member"."id" AS "seen_by_member_id",
jbe@115 713 "event".*
jbe@115 714 FROM "member" CROSS JOIN "event"
jbe@115 715 LEFT JOIN "issue"
jbe@115 716 ON "event"."issue_id" = "issue"."id"
jbe@115 717 LEFT JOIN "membership"
jbe@115 718 ON "member"."id" = "membership"."member_id"
jbe@115 719 AND "issue"."area_id" = "membership"."area_id"
jbe@115 720 LEFT JOIN "interest"
jbe@115 721 ON "member"."id" = "interest"."member_id"
jbe@115 722 AND "event"."issue_id" = "interest"."issue_id"
jbe@115 723 LEFT JOIN "supporter"
jbe@115 724 ON "member"."id" = "supporter"."member_id"
jbe@115 725 AND "event"."initiative_id" = "supporter"."initiative_id"
jbe@115 726 LEFT JOIN "ignored_member"
jbe@115 727 ON "member"."id" = "ignored_member"."member_id"
jbe@115 728 AND "event"."member_id" = "ignored_member"."other_member_id"
jbe@115 729 LEFT JOIN "ignored_initiative"
jbe@115 730 ON "member"."id" = "ignored_initiative"."member_id"
jbe@115 731 AND "event"."initiative_id" = "ignored_initiative"."initiative_id"
jbe@115 732 WHERE (
jbe@115 733 "member"."notify_event_id" ISNULL OR
jbe@115 734 ( "member"."notify_event_id" NOTNULL AND
jbe@115 735 "member"."notify_event_id" < "event"."id" ) )
jbe@115 736 AND (
jbe@115 737 ( "member"."notify_level" >= 'all' ) OR
jbe@115 738 ( "member"."notify_level" >= 'voting' AND
jbe@115 739 "event"."state" IN (
jbe@115 740 'voting',
jbe@115 741 'finished_without_winner',
jbe@115 742 'finished_with_winner' ) ) OR
jbe@115 743 ( "member"."notify_level" >= 'verification' AND
jbe@115 744 "event"."state" IN (
jbe@115 745 'verification',
jbe@115 746 'canceled_after_revocation_during_verification',
jbe@115 747 'canceled_no_initiative_admitted' ) ) OR
jbe@115 748 ( "member"."notify_level" >= 'discussion' AND
jbe@115 749 "event"."state" IN (
jbe@115 750 'discussion',
jbe@115 751 'canceled_after_revocation_during_discussion' ) ) )
jbe@115 752 AND (
jbe@115 753 "supporter"."member_id" NOTNULL OR
jbe@115 754 "interest"."member_id" NOTNULL OR
jbe@115 755 ( "membership"."member_id" NOTNULL AND
jbe@115 756 "event"."event" IN (
jbe@115 757 'issue_state_changed',
jbe@115 758 'initiative_created_in_new_issue',
jbe@115 759 'initiative_created_in_existing_issue',
jbe@115 760 'initiative_revoked' ) ) )
jbe@115 761 AND "ignored_member"."member_id" ISNULL
jbe@115 762 AND "ignored_initiative"."member_id" ISNULL;
jbe@115 763
jbe@115 764 COMMENT ON VIEW "pending_notification" IS 'Events to be sent to "notify_email" address of member referred to by "seen_by_member_id"';
jbe@115 765
jbe@115 766
jbe@115 767 COMMENT ON TYPE "timeline_event" IS 'Types of event in timeline tables (DEPRECATED)';
jbe@115 768 COMMENT ON VIEW "timeline_issue" IS 'Helper view for "timeline" view (DEPRECATED)';
jbe@115 769 COMMENT ON VIEW "timeline_initiative" IS 'Helper view for "timeline" view (DEPRECATED)';
jbe@115 770 COMMENT ON VIEW "timeline_draft" IS 'Helper view for "timeline" view (DEPRECATED)';
jbe@115 771 COMMENT ON VIEW "timeline_suggestion" IS 'Helper view for "timeline" view (DEPRECATED)';
jbe@115 772 COMMENT ON VIEW "timeline" IS 'Aggregation of different events in the system (DEPRECATED)';
jbe@115 773
jbe@115 774
jbe@115 775 -- Modified "delegation_chain" functions:
jbe@115 776
jbe@115 777 CREATE TYPE "delegation_chain_row" AS (
jbe@115 778 "index" INT4,
jbe@115 779 "member_id" INT4,
jbe@115 780 "member_valid" BOOLEAN,
jbe@115 781 "participation" BOOLEAN,
jbe@115 782 "overridden" BOOLEAN,
jbe@115 783 "scope_in" "delegation_scope",
jbe@115 784 "scope_out" "delegation_scope",
jbe@115 785 "disabled_out" BOOLEAN,
jbe@115 786 "loop" "delegation_chain_loop_tag" );
jbe@115 787
jbe@115 788 COMMENT ON TYPE "delegation_chain_row" IS 'Type of rows returned by "delegation_chain"(...) functions';
jbe@115 789
jbe@115 790 COMMENT ON COLUMN "delegation_chain_row"."index" IS 'Index starting with 0 and counting up';
jbe@115 791 COMMENT ON COLUMN "delegation_chain_row"."participation" IS 'In case of delegation chains for issues: interest, for areas: membership, for global delegation chains: always null';
jbe@115 792 COMMENT ON COLUMN "delegation_chain_row"."overridden" IS 'True, if an entry with lower index has "participation" set to true';
jbe@115 793 COMMENT ON COLUMN "delegation_chain_row"."scope_in" IS 'Scope of used incoming delegation';
jbe@115 794 COMMENT ON COLUMN "delegation_chain_row"."scope_out" IS 'Scope of used outgoing delegation';
jbe@115 795 COMMENT ON COLUMN "delegation_chain_row"."disabled_out" IS 'Outgoing delegation is explicitly disabled by a delegation with trustee_id set to NULL';
jbe@115 796 COMMENT ON COLUMN "delegation_chain_row"."loop" IS 'Not null, if member is part of a loop, see "delegation_chain_loop_tag" type';
jbe@115 797
jbe@115 798
jbe@115 799 CREATE FUNCTION "delegation_chain"
jbe@115 800 ( "member_id_p" "member"."id"%TYPE,
jbe@115 801 "unit_id_p" "unit"."id"%TYPE,
jbe@115 802 "area_id_p" "area"."id"%TYPE,
jbe@115 803 "issue_id_p" "issue"."id"%TYPE,
jbe@115 804 "simulate_trustee_id_p" "member"."id"%TYPE )
jbe@115 805 RETURNS SETOF "delegation_chain_row"
jbe@115 806 LANGUAGE 'plpgsql' STABLE AS $$
jbe@115 807 DECLARE
jbe@115 808 "scope_v" "delegation_scope";
jbe@115 809 "unit_id_v" "unit"."id"%TYPE;
jbe@115 810 "area_id_v" "area"."id"%TYPE;
jbe@115 811 "visited_member_ids" INT4[]; -- "member"."id"%TYPE[]
jbe@115 812 "loop_member_id_v" "member"."id"%TYPE;
jbe@115 813 "output_row" "delegation_chain_row";
jbe@115 814 "output_rows" "delegation_chain_row"[];
jbe@115 815 "delegation_row" "delegation"%ROWTYPE;
jbe@115 816 "row_count" INT4;
jbe@115 817 "i" INT4;
jbe@115 818 "loop_v" BOOLEAN;
jbe@115 819 BEGIN
jbe@115 820 IF
jbe@115 821 "unit_id_p" NOTNULL AND
jbe@115 822 "area_id_p" ISNULL AND
jbe@115 823 "issue_id_p" ISNULL
jbe@115 824 THEN
jbe@115 825 "scope_v" := 'unit';
jbe@115 826 "unit_id_v" := "unit_id_p";
jbe@115 827 ELSIF
jbe@115 828 "unit_id_p" ISNULL AND
jbe@115 829 "area_id_p" NOTNULL AND
jbe@115 830 "issue_id_p" ISNULL
jbe@115 831 THEN
jbe@115 832 "scope_v" := 'area';
jbe@115 833 "area_id_v" := "area_id_p";
jbe@115 834 SELECT "unit_id" INTO "unit_id_v"
jbe@115 835 FROM "area" WHERE "id" = "area_id_v";
jbe@115 836 ELSIF
jbe@115 837 "unit_id_p" ISNULL AND
jbe@115 838 "area_id_p" ISNULL AND
jbe@115 839 "issue_id_p" NOTNULL
jbe@115 840 THEN
jbe@115 841 "scope_v" := 'issue';
jbe@115 842 SELECT "area_id" INTO "area_id_v"
jbe@115 843 FROM "issue" WHERE "id" = "issue_id_p";
jbe@115 844 SELECT "unit_id" INTO "unit_id_v"
jbe@115 845 FROM "area" WHERE "id" = "area_id_v";
jbe@115 846 ELSE
jbe@115 847 RAISE EXCEPTION 'Exactly one of unit_id_p, area_id_p, or issue_id_p must be NOTNULL.';
jbe@115 848 END IF;
jbe@115 849 "visited_member_ids" := '{}';
jbe@115 850 "loop_member_id_v" := NULL;
jbe@115 851 "output_rows" := '{}';
jbe@115 852 "output_row"."index" := 0;
jbe@115 853 "output_row"."member_id" := "member_id_p";
jbe@115 854 "output_row"."member_valid" := TRUE;
jbe@115 855 "output_row"."participation" := FALSE;
jbe@115 856 "output_row"."overridden" := FALSE;
jbe@115 857 "output_row"."disabled_out" := FALSE;
jbe@115 858 "output_row"."scope_out" := NULL;
jbe@115 859 LOOP
jbe@115 860 IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN
jbe@115 861 "loop_member_id_v" := "output_row"."member_id";
jbe@115 862 ELSE
jbe@115 863 "visited_member_ids" :=
jbe@115 864 "visited_member_ids" || "output_row"."member_id";
jbe@115 865 END IF;
jbe@115 866 IF "output_row"."participation" THEN
jbe@115 867 "output_row"."overridden" := TRUE;
jbe@115 868 END IF;
jbe@115 869 "output_row"."scope_in" := "output_row"."scope_out";
jbe@115 870 IF EXISTS (
jbe@115 871 SELECT NULL FROM "member" JOIN "privilege"
jbe@115 872 ON "privilege"."member_id" = "member"."id"
jbe@115 873 AND "privilege"."unit_id" = "unit_id_v"
jbe@115 874 WHERE "id" = "output_row"."member_id"
jbe@115 875 AND "member"."active" AND "privilege"."voting_right"
jbe@115 876 ) THEN
jbe@115 877 IF "scope_v" = 'unit' THEN
jbe@115 878 SELECT * INTO "delegation_row" FROM "delegation"
jbe@115 879 WHERE "truster_id" = "output_row"."member_id"
jbe@115 880 AND "unit_id" = "unit_id_v";
jbe@115 881 ELSIF "scope_v" = 'area' THEN
jbe@115 882 "output_row"."participation" := EXISTS (
jbe@115 883 SELECT NULL FROM "membership"
jbe@115 884 WHERE "area_id" = "area_id_p"
jbe@115 885 AND "member_id" = "output_row"."member_id"
jbe@115 886 );
jbe@115 887 SELECT * INTO "delegation_row" FROM "delegation"
jbe@115 888 WHERE "truster_id" = "output_row"."member_id"
jbe@115 889 AND (
jbe@115 890 "unit_id" = "unit_id_v" OR
jbe@115 891 "area_id" = "area_id_v"
jbe@115 892 )
jbe@115 893 ORDER BY "scope" DESC;
jbe@115 894 ELSIF "scope_v" = 'issue' THEN
jbe@115 895 "output_row"."participation" := EXISTS (
jbe@115 896 SELECT NULL FROM "interest"
jbe@115 897 WHERE "issue_id" = "issue_id_p"
jbe@115 898 AND "member_id" = "output_row"."member_id"
jbe@115 899 );
jbe@115 900 SELECT * INTO "delegation_row" FROM "delegation"
jbe@115 901 WHERE "truster_id" = "output_row"."member_id"
jbe@115 902 AND (
jbe@115 903 "unit_id" = "unit_id_v" OR
jbe@115 904 "area_id" = "area_id_v" OR
jbe@115 905 "issue_id" = "issue_id_p"
jbe@115 906 )
jbe@115 907 ORDER BY "scope" DESC;
jbe@115 908 END IF;
jbe@115 909 ELSE
jbe@115 910 "output_row"."member_valid" := FALSE;
jbe@115 911 "output_row"."participation" := FALSE;
jbe@115 912 "output_row"."scope_out" := NULL;
jbe@115 913 "delegation_row" := ROW(NULL);
jbe@115 914 END IF;
jbe@115 915 IF
jbe@115 916 "output_row"."member_id" = "member_id_p" AND
jbe@115 917 "simulate_trustee_id_p" NOTNULL
jbe@115 918 THEN
jbe@115 919 "output_row"."scope_out" := "scope_v";
jbe@115 920 "output_rows" := "output_rows" || "output_row";
jbe@115 921 "output_row"."member_id" := "simulate_trustee_id_p";
jbe@115 922 ELSIF "delegation_row"."trustee_id" NOTNULL THEN
jbe@115 923 "output_row"."scope_out" := "delegation_row"."scope";
jbe@115 924 "output_rows" := "output_rows" || "output_row";
jbe@115 925 "output_row"."member_id" := "delegation_row"."trustee_id";
jbe@115 926 ELSIF "delegation_row"."scope" NOTNULL THEN
jbe@115 927 "output_row"."scope_out" := "delegation_row"."scope";
jbe@115 928 "output_row"."disabled_out" := TRUE;
jbe@115 929 "output_rows" := "output_rows" || "output_row";
jbe@115 930 EXIT;
jbe@115 931 ELSE
jbe@115 932 "output_row"."scope_out" := NULL;
jbe@115 933 "output_rows" := "output_rows" || "output_row";
jbe@115 934 EXIT;
jbe@115 935 END IF;
jbe@115 936 EXIT WHEN "loop_member_id_v" NOTNULL;
jbe@115 937 "output_row"."index" := "output_row"."index" + 1;
jbe@115 938 END LOOP;
jbe@115 939 "row_count" := array_upper("output_rows", 1);
jbe@115 940 "i" := 1;
jbe@115 941 "loop_v" := FALSE;
jbe@115 942 LOOP
jbe@115 943 "output_row" := "output_rows"["i"];
jbe@115 944 EXIT WHEN "output_row" ISNULL; -- NOTE: ISNULL and NOT ... NOTNULL produce different results!
jbe@115 945 IF "loop_v" THEN
jbe@115 946 IF "i" + 1 = "row_count" THEN
jbe@115 947 "output_row"."loop" := 'last';
jbe@115 948 ELSIF "i" = "row_count" THEN
jbe@115 949 "output_row"."loop" := 'repetition';
jbe@115 950 ELSE
jbe@115 951 "output_row"."loop" := 'intermediate';
jbe@115 952 END IF;
jbe@115 953 ELSIF "output_row"."member_id" = "loop_member_id_v" THEN
jbe@115 954 "output_row"."loop" := 'first';
jbe@115 955 "loop_v" := TRUE;
jbe@115 956 END IF;
jbe@115 957 IF "scope_v" = 'unit' THEN
jbe@115 958 "output_row"."participation" := NULL;
jbe@115 959 END IF;
jbe@115 960 RETURN NEXT "output_row";
jbe@115 961 "i" := "i" + 1;
jbe@115 962 END LOOP;
jbe@115 963 RETURN;
jbe@115 964 END;
jbe@115 965 $$;
jbe@115 966
jbe@115 967 COMMENT ON FUNCTION "delegation_chain"
jbe@115 968 ( "member"."id"%TYPE,
jbe@115 969 "unit"."id"%TYPE,
jbe@115 970 "area"."id"%TYPE,
jbe@115 971 "issue"."id"%TYPE,
jbe@115 972 "member"."id"%TYPE )
jbe@115 973 IS 'Helper function for frontends to display delegation chains; Not part of internal voting logic';
jbe@115 974
jbe@115 975
jbe@115 976 CREATE FUNCTION "delegation_chain"
jbe@115 977 ( "member_id_p" "member"."id"%TYPE,
jbe@115 978 "unit_id_p" "unit"."id"%TYPE,
jbe@115 979 "area_id_p" "area"."id"%TYPE,
jbe@115 980 "issue_id_p" "issue"."id"%TYPE )
jbe@115 981 RETURNS SETOF "delegation_chain_row"
jbe@115 982 LANGUAGE 'plpgsql' STABLE AS $$
jbe@115 983 DECLARE
jbe@115 984 "result_row" "delegation_chain_row";
jbe@115 985 BEGIN
jbe@115 986 FOR "result_row" IN
jbe@115 987 SELECT * FROM "delegation_chain"(
jbe@123 988 "member_id_p", "unit_id_p", "area_id_p", "issue_id_p", NULL
jbe@115 989 )
jbe@115 990 LOOP
jbe@115 991 RETURN NEXT "result_row";
jbe@115 992 END LOOP;
jbe@115 993 RETURN;
jbe@115 994 END;
jbe@115 995 $$;
jbe@115 996
jbe@115 997 COMMENT ON FUNCTION "delegation_chain"
jbe@115 998 ( "member"."id"%TYPE,
jbe@115 999 "unit"."id"%TYPE,
jbe@115 1000 "area"."id"%TYPE,
jbe@115 1001 "issue"."id"%TYPE )
jbe@115 1002 IS 'Shortcut for "delegation_chain"(...) function where 4th parameter is null';
jbe@115 1003
jbe@115 1004
jbe@122 1005 -- Other modified functions:
jbe@115 1006
jbe@115 1007 CREATE OR REPLACE FUNCTION "lock_issue"
jbe@115 1008 ( "issue_id_p" "issue"."id"%TYPE )
jbe@115 1009 RETURNS VOID
jbe@115 1010 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@115 1011 BEGIN
jbe@115 1012 LOCK TABLE "member" IN SHARE MODE;
jbe@115 1013 LOCK TABLE "privilege" IN SHARE MODE;
jbe@115 1014 LOCK TABLE "membership" IN SHARE MODE;
jbe@115 1015 LOCK TABLE "policy" IN SHARE MODE;
jbe@115 1016 PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE;
jbe@115 1017 -- NOTE: The row-level exclusive lock in combination with the
jbe@115 1018 -- share_row_lock_issue(_via_initiative)_trigger functions (which
jbe@115 1019 -- acquire a row-level share lock on the issue) ensure that no data
jbe@115 1020 -- is changed, which could affect calculation of snapshots or
jbe@115 1021 -- counting of votes. Table "delegation" must be table-level-locked,
jbe@115 1022 -- as it also contains issue- and global-scope delegations.
jbe@115 1023 LOCK TABLE "delegation" IN SHARE MODE;
jbe@115 1024 LOCK TABLE "direct_population_snapshot" IN EXCLUSIVE MODE;
jbe@115 1025 LOCK TABLE "delegating_population_snapshot" IN EXCLUSIVE MODE;
jbe@115 1026 LOCK TABLE "direct_interest_snapshot" IN EXCLUSIVE MODE;
jbe@115 1027 LOCK TABLE "delegating_interest_snapshot" IN EXCLUSIVE MODE;
jbe@115 1028 LOCK TABLE "direct_supporter_snapshot" IN EXCLUSIVE MODE;
jbe@115 1029 RETURN;
jbe@115 1030 END;
jbe@115 1031 $$;
jbe@115 1032
jbe@115 1033 CREATE OR REPLACE FUNCTION "calculate_member_counts"()
jbe@115 1034 RETURNS VOID
jbe@115 1035 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@115 1036 BEGIN
jbe@115 1037 LOCK TABLE "member" IN SHARE MODE;
jbe@115 1038 LOCK TABLE "member_count" IN EXCLUSIVE MODE;
jbe@115 1039 LOCK TABLE "unit" IN EXCLUSIVE MODE;
jbe@115 1040 LOCK TABLE "area" IN EXCLUSIVE MODE;
jbe@115 1041 LOCK TABLE "privilege" IN SHARE MODE;
jbe@115 1042 LOCK TABLE "membership" IN SHARE MODE;
jbe@115 1043 DELETE FROM "member_count";
jbe@115 1044 INSERT INTO "member_count" ("total_count")
jbe@115 1045 SELECT "total_count" FROM "member_count_view";
jbe@115 1046 UPDATE "unit" SET "member_count" = "view"."member_count"
jbe@115 1047 FROM "unit_member_count" AS "view"
jbe@115 1048 WHERE "view"."unit_id" = "unit"."id";
jbe@115 1049 UPDATE "area" SET
jbe@115 1050 "direct_member_count" = "view"."direct_member_count",
jbe@115 1051 "member_weight" = "view"."member_weight",
jbe@115 1052 "autoreject_weight" = "view"."autoreject_weight"
jbe@115 1053 FROM "area_member_count" AS "view"
jbe@115 1054 WHERE "view"."area_id" = "area"."id";
jbe@115 1055 RETURN;
jbe@115 1056 END;
jbe@115 1057 $$;
jbe@115 1058
jbe@115 1059 CREATE OR REPLACE FUNCTION "create_population_snapshot"
jbe@115 1060 ( "issue_id_p" "issue"."id"%TYPE )
jbe@115 1061 RETURNS VOID
jbe@115 1062 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@115 1063 DECLARE
jbe@115 1064 "member_id_v" "member"."id"%TYPE;
jbe@115 1065 BEGIN
jbe@115 1066 DELETE FROM "direct_population_snapshot"
jbe@115 1067 WHERE "issue_id" = "issue_id_p"
jbe@115 1068 AND "event" = 'periodic';
jbe@115 1069 DELETE FROM "delegating_population_snapshot"
jbe@115 1070 WHERE "issue_id" = "issue_id_p"
jbe@115 1071 AND "event" = 'periodic';
jbe@115 1072 INSERT INTO "direct_population_snapshot"
jbe@115 1073 ("issue_id", "event", "member_id")
jbe@115 1074 SELECT
jbe@115 1075 "issue_id_p" AS "issue_id",
jbe@115 1076 'periodic'::"snapshot_event" AS "event",
jbe@115 1077 "member"."id" AS "member_id"
jbe@115 1078 FROM "issue"
jbe@115 1079 JOIN "area" ON "issue"."area_id" = "area"."id"
jbe@115 1080 JOIN "membership" ON "area"."id" = "membership"."area_id"
jbe@115 1081 JOIN "member" ON "membership"."member_id" = "member"."id"
jbe@115 1082 JOIN "privilege"
jbe@115 1083 ON "privilege"."unit_id" = "area"."unit_id"
jbe@115 1084 AND "privilege"."member_id" = "member"."id"
jbe@115 1085 WHERE "issue"."id" = "issue_id_p"
jbe@115 1086 AND "member"."active" AND "privilege"."voting_right"
jbe@115 1087 UNION
jbe@115 1088 SELECT
jbe@115 1089 "issue_id_p" AS "issue_id",
jbe@115 1090 'periodic'::"snapshot_event" AS "event",
jbe@115 1091 "member"."id" AS "member_id"
jbe@115 1092 FROM "issue"
jbe@115 1093 JOIN "area" ON "issue"."area_id" = "area"."id"
jbe@115 1094 JOIN "interest" ON "issue"."id" = "interest"."issue_id"
jbe@115 1095 JOIN "member" ON "interest"."member_id" = "member"."id"
jbe@115 1096 JOIN "privilege"
jbe@115 1097 ON "privilege"."unit_id" = "area"."unit_id"
jbe@115 1098 AND "privilege"."member_id" = "member"."id"
jbe@115 1099 WHERE "issue"."id" = "issue_id_p"
jbe@115 1100 AND "member"."active" AND "privilege"."voting_right";
jbe@115 1101 FOR "member_id_v" IN
jbe@115 1102 SELECT "member_id" FROM "direct_population_snapshot"
jbe@115 1103 WHERE "issue_id" = "issue_id_p"
jbe@115 1104 AND "event" = 'periodic'
jbe@115 1105 LOOP
jbe@115 1106 UPDATE "direct_population_snapshot" SET
jbe@115 1107 "weight" = 1 +
jbe@115 1108 "weight_of_added_delegations_for_population_snapshot"(
jbe@115 1109 "issue_id_p",
jbe@115 1110 "member_id_v",
jbe@115 1111 '{}'
jbe@115 1112 )
jbe@115 1113 WHERE "issue_id" = "issue_id_p"
jbe@115 1114 AND "event" = 'periodic'
jbe@115 1115 AND "member_id" = "member_id_v";
jbe@115 1116 END LOOP;
jbe@115 1117 RETURN;
jbe@115 1118 END;
jbe@115 1119 $$;
jbe@115 1120
jbe@115 1121 CREATE OR REPLACE FUNCTION "create_interest_snapshot"
jbe@115 1122 ( "issue_id_p" "issue"."id"%TYPE )
jbe@115 1123 RETURNS VOID
jbe@115 1124 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@115 1125 DECLARE
jbe@115 1126 "member_id_v" "member"."id"%TYPE;
jbe@115 1127 BEGIN
jbe@115 1128 DELETE FROM "direct_interest_snapshot"
jbe@115 1129 WHERE "issue_id" = "issue_id_p"
jbe@115 1130 AND "event" = 'periodic';
jbe@115 1131 DELETE FROM "delegating_interest_snapshot"
jbe@115 1132 WHERE "issue_id" = "issue_id_p"
jbe@115 1133 AND "event" = 'periodic';
jbe@115 1134 DELETE FROM "direct_supporter_snapshot"
jbe@115 1135 WHERE "issue_id" = "issue_id_p"
jbe@115 1136 AND "event" = 'periodic';
jbe@115 1137 INSERT INTO "direct_interest_snapshot"
jbe@115 1138 ("issue_id", "event", "member_id", "voting_requested")
jbe@115 1139 SELECT
jbe@115 1140 "issue_id_p" AS "issue_id",
jbe@115 1141 'periodic' AS "event",
jbe@115 1142 "member"."id" AS "member_id",
jbe@115 1143 "interest"."voting_requested"
jbe@115 1144 FROM "issue"
jbe@115 1145 JOIN "area" ON "issue"."area_id" = "area"."id"
jbe@115 1146 JOIN "interest" ON "issue"."id" = "interest"."issue_id"
jbe@115 1147 JOIN "member" ON "interest"."member_id" = "member"."id"
jbe@115 1148 JOIN "privilege"
jbe@115 1149 ON "privilege"."unit_id" = "area"."unit_id"
jbe@115 1150 AND "privilege"."member_id" = "member"."id"
jbe@115 1151 WHERE "issue"."id" = "issue_id_p"
jbe@115 1152 AND "member"."active" AND "privilege"."voting_right";
jbe@115 1153 FOR "member_id_v" IN
jbe@115 1154 SELECT "member_id" FROM "direct_interest_snapshot"
jbe@115 1155 WHERE "issue_id" = "issue_id_p"
jbe@115 1156 AND "event" = 'periodic'
jbe@115 1157 LOOP
jbe@115 1158 UPDATE "direct_interest_snapshot" SET
jbe@115 1159 "weight" = 1 +
jbe@115 1160 "weight_of_added_delegations_for_interest_snapshot"(
jbe@115 1161 "issue_id_p",
jbe@115 1162 "member_id_v",
jbe@115 1163 '{}'
jbe@115 1164 )
jbe@115 1165 WHERE "issue_id" = "issue_id_p"
jbe@115 1166 AND "event" = 'periodic'
jbe@115 1167 AND "member_id" = "member_id_v";
jbe@115 1168 END LOOP;
jbe@115 1169 INSERT INTO "direct_supporter_snapshot"
jbe@115 1170 ( "issue_id", "initiative_id", "event", "member_id",
jbe@115 1171 "informed", "satisfied" )
jbe@115 1172 SELECT
jbe@115 1173 "issue_id_p" AS "issue_id",
jbe@115 1174 "initiative"."id" AS "initiative_id",
jbe@115 1175 'periodic' AS "event",
jbe@115 1176 "supporter"."member_id" AS "member_id",
jbe@115 1177 "supporter"."draft_id" = "current_draft"."id" AS "informed",
jbe@115 1178 NOT EXISTS (
jbe@115 1179 SELECT NULL FROM "critical_opinion"
jbe@115 1180 WHERE "initiative_id" = "initiative"."id"
jbe@115 1181 AND "member_id" = "supporter"."member_id"
jbe@115 1182 ) AS "satisfied"
jbe@115 1183 FROM "initiative"
jbe@115 1184 JOIN "supporter"
jbe@115 1185 ON "supporter"."initiative_id" = "initiative"."id"
jbe@115 1186 JOIN "current_draft"
jbe@115 1187 ON "initiative"."id" = "current_draft"."initiative_id"
jbe@115 1188 JOIN "direct_interest_snapshot"
jbe@115 1189 ON "supporter"."member_id" = "direct_interest_snapshot"."member_id"
jbe@115 1190 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
jbe@115 1191 AND "event" = 'periodic'
jbe@115 1192 WHERE "initiative"."issue_id" = "issue_id_p";
jbe@115 1193 RETURN;
jbe@115 1194 END;
jbe@115 1195 $$;
jbe@115 1196
jbe@115 1197 CREATE OR REPLACE FUNCTION "freeze_after_snapshot"
jbe@115 1198 ( "issue_id_p" "issue"."id"%TYPE )
jbe@115 1199 RETURNS VOID
jbe@115 1200 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@115 1201 DECLARE
jbe@115 1202 "issue_row" "issue"%ROWTYPE;
jbe@115 1203 "policy_row" "policy"%ROWTYPE;
jbe@115 1204 "initiative_row" "initiative"%ROWTYPE;
jbe@115 1205 BEGIN
jbe@115 1206 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
jbe@115 1207 SELECT * INTO "policy_row"
jbe@115 1208 FROM "policy" WHERE "id" = "issue_row"."policy_id";
jbe@115 1209 PERFORM "set_snapshot_event"("issue_id_p", 'full_freeze');
jbe@115 1210 FOR "initiative_row" IN
jbe@115 1211 SELECT * FROM "initiative"
jbe@115 1212 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
jbe@115 1213 LOOP
jbe@115 1214 IF
jbe@115 1215 "initiative_row"."satisfied_supporter_count" > 0 AND
jbe@115 1216 "initiative_row"."satisfied_supporter_count" *
jbe@115 1217 "policy_row"."initiative_quorum_den" >=
jbe@115 1218 "issue_row"."population" * "policy_row"."initiative_quorum_num"
jbe@115 1219 THEN
jbe@115 1220 UPDATE "initiative" SET "admitted" = TRUE
jbe@115 1221 WHERE "id" = "initiative_row"."id";
jbe@115 1222 ELSE
jbe@115 1223 UPDATE "initiative" SET "admitted" = FALSE
jbe@115 1224 WHERE "id" = "initiative_row"."id";
jbe@115 1225 END IF;
jbe@115 1226 END LOOP;
jbe@115 1227 IF EXISTS (
jbe@115 1228 SELECT NULL FROM "initiative"
jbe@115 1229 WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE
jbe@115 1230 ) THEN
jbe@115 1231 UPDATE "issue" SET
jbe@115 1232 "state" = 'voting',
jbe@115 1233 "accepted" = coalesce("accepted", now()),
jbe@115 1234 "half_frozen" = coalesce("half_frozen", now()),
jbe@115 1235 "fully_frozen" = now()
jbe@115 1236 WHERE "id" = "issue_id_p";
jbe@115 1237 ELSE
jbe@115 1238 UPDATE "issue" SET
jbe@121 1239 "state" = 'canceled_no_initiative_admitted',
jbe@121 1240 "accepted" = coalesce("accepted", now()),
jbe@121 1241 "half_frozen" = coalesce("half_frozen", now()),
jbe@121 1242 "fully_frozen" = now(),
jbe@121 1243 "closed" = now(),
jbe@121 1244 "ranks_available" = TRUE
jbe@115 1245 WHERE "id" = "issue_id_p";
jbe@115 1246 -- NOTE: The following DELETE statements have effect only when
jbe@115 1247 -- issue state has been manipulated
jbe@115 1248 DELETE FROM "direct_voter" WHERE "issue_id" = "issue_id_p";
jbe@115 1249 DELETE FROM "delegating_voter" WHERE "issue_id" = "issue_id_p";
jbe@115 1250 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
jbe@115 1251 END IF;
jbe@115 1252 RETURN;
jbe@115 1253 END;
jbe@115 1254 $$;
jbe@115 1255
jbe@115 1256 CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
jbe@115 1257 RETURNS VOID
jbe@115 1258 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@115 1259 DECLARE
jbe@115 1260 "area_id_v" "area"."id"%TYPE;
jbe@115 1261 "unit_id_v" "unit"."id"%TYPE;
jbe@115 1262 "member_id_v" "member"."id"%TYPE;
jbe@115 1263 BEGIN
jbe@115 1264 PERFORM "lock_issue"("issue_id_p");
jbe@115 1265 SELECT "id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
jbe@115 1266 SELECT "id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v";
jbe@115 1267 DELETE FROM "delegating_voter"
jbe@115 1268 WHERE "issue_id" = "issue_id_p";
jbe@115 1269 DELETE FROM "direct_voter"
jbe@115 1270 WHERE "issue_id" = "issue_id_p"
jbe@115 1271 AND "autoreject" = TRUE;
jbe@115 1272 DELETE FROM "direct_voter"
jbe@115 1273 USING (
jbe@115 1274 SELECT
jbe@115 1275 "direct_voter"."member_id"
jbe@115 1276 FROM "direct_voter"
jbe@115 1277 JOIN "member" ON "direct_voter"."member_id" = "member"."id"
jbe@115 1278 LEFT JOIN "privilege"
jbe@115 1279 ON "privilege"."unit_id" = "unit_id_v"
jbe@115 1280 AND "privilege"."member_id" = "direct_voter"."member_id"
jbe@115 1281 WHERE "direct_voter"."issue_id" = "issue_id_p" AND (
jbe@115 1282 "member"."active" = FALSE OR
jbe@115 1283 "privilege"."voting_right" ISNULL OR
jbe@115 1284 "privilege"."voting_right" = FALSE
jbe@115 1285 )
jbe@115 1286 ) AS "subquery"
jbe@115 1287 WHERE "direct_voter"."issue_id" = "issue_id_p"
jbe@115 1288 AND "direct_voter"."member_id" = "subquery"."member_id";
jbe@115 1289 UPDATE "direct_voter" SET "weight" = 1
jbe@115 1290 WHERE "issue_id" = "issue_id_p";
jbe@115 1291 PERFORM "add_vote_delegations"("issue_id_p");
jbe@115 1292 FOR "member_id_v" IN
jbe@115 1293 SELECT "interest"."member_id"
jbe@115 1294 FROM "interest"
jbe@115 1295 JOIN "member"
jbe@115 1296 ON "interest"."member_id" = "member"."id"
jbe@115 1297 LEFT JOIN "direct_voter"
jbe@115 1298 ON "interest"."member_id" = "direct_voter"."member_id"
jbe@115 1299 AND "interest"."issue_id" = "direct_voter"."issue_id"
jbe@115 1300 LEFT JOIN "delegating_voter"
jbe@115 1301 ON "interest"."member_id" = "delegating_voter"."member_id"
jbe@115 1302 AND "interest"."issue_id" = "delegating_voter"."issue_id"
jbe@115 1303 WHERE "interest"."issue_id" = "issue_id_p"
jbe@115 1304 AND "interest"."autoreject" = TRUE
jbe@115 1305 AND "member"."active"
jbe@115 1306 AND "direct_voter"."member_id" ISNULL
jbe@115 1307 AND "delegating_voter"."member_id" ISNULL
jbe@115 1308 UNION SELECT "membership"."member_id"
jbe@115 1309 FROM "membership"
jbe@115 1310 JOIN "member"
jbe@115 1311 ON "membership"."member_id" = "member"."id"
jbe@115 1312 LEFT JOIN "interest"
jbe@115 1313 ON "membership"."member_id" = "interest"."member_id"
jbe@115 1314 AND "interest"."issue_id" = "issue_id_p"
jbe@115 1315 LEFT JOIN "direct_voter"
jbe@115 1316 ON "membership"."member_id" = "direct_voter"."member_id"
jbe@115 1317 AND "direct_voter"."issue_id" = "issue_id_p"
jbe@115 1318 LEFT JOIN "delegating_voter"
jbe@115 1319 ON "membership"."member_id" = "delegating_voter"."member_id"
jbe@115 1320 AND "delegating_voter"."issue_id" = "issue_id_p"
jbe@115 1321 WHERE "membership"."area_id" = "area_id_v"
jbe@115 1322 AND "membership"."autoreject" = TRUE
jbe@115 1323 AND "member"."active"
jbe@115 1324 AND "interest"."autoreject" ISNULL
jbe@115 1325 AND "direct_voter"."member_id" ISNULL
jbe@115 1326 AND "delegating_voter"."member_id" ISNULL
jbe@115 1327 LOOP
jbe@115 1328 INSERT INTO "direct_voter"
jbe@115 1329 ("member_id", "issue_id", "weight", "autoreject") VALUES
jbe@115 1330 ("member_id_v", "issue_id_p", 1, TRUE);
jbe@115 1331 INSERT INTO "vote" (
jbe@115 1332 "member_id",
jbe@115 1333 "issue_id",
jbe@115 1334 "initiative_id",
jbe@115 1335 "grade"
jbe@115 1336 ) SELECT
jbe@115 1337 "member_id_v" AS "member_id",
jbe@115 1338 "issue_id_p" AS "issue_id",
jbe@115 1339 "id" AS "initiative_id",
jbe@115 1340 -1 AS "grade"
jbe@115 1341 FROM "initiative" WHERE "issue_id" = "issue_id_p";
jbe@115 1342 END LOOP;
jbe@115 1343 PERFORM "add_vote_delegations"("issue_id_p");
jbe@115 1344 UPDATE "issue" SET
jbe@115 1345 "state" = 'calculation',
jbe@115 1346 "closed" = now(),
jbe@115 1347 "voter_count" = (
jbe@115 1348 SELECT coalesce(sum("weight"), 0)
jbe@115 1349 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
jbe@115 1350 )
jbe@115 1351 WHERE "id" = "issue_id_p";
jbe@115 1352 UPDATE "initiative" SET
jbe@115 1353 "positive_votes" = "vote_counts"."positive_votes",
jbe@115 1354 "negative_votes" = "vote_counts"."negative_votes",
jbe@115 1355 "agreed" = CASE WHEN "majority_strict" THEN
jbe@115 1356 "vote_counts"."positive_votes" * "majority_den" >
jbe@115 1357 "majority_num" *
jbe@115 1358 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
jbe@115 1359 ELSE
jbe@115 1360 "vote_counts"."positive_votes" * "majority_den" >=
jbe@115 1361 "majority_num" *
jbe@115 1362 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
jbe@115 1363 END
jbe@115 1364 FROM
jbe@115 1365 ( SELECT
jbe@115 1366 "initiative"."id" AS "initiative_id",
jbe@115 1367 coalesce(
jbe@115 1368 sum(
jbe@115 1369 CASE WHEN "grade" > 0 THEN "direct_voter"."weight" ELSE 0 END
jbe@115 1370 ),
jbe@115 1371 0
jbe@115 1372 ) AS "positive_votes",
jbe@115 1373 coalesce(
jbe@115 1374 sum(
jbe@115 1375 CASE WHEN "grade" < 0 THEN "direct_voter"."weight" ELSE 0 END
jbe@115 1376 ),
jbe@115 1377 0
jbe@115 1378 ) AS "negative_votes"
jbe@115 1379 FROM "initiative"
jbe@115 1380 JOIN "issue" ON "initiative"."issue_id" = "issue"."id"
jbe@115 1381 JOIN "policy" ON "issue"."policy_id" = "policy"."id"
jbe@115 1382 LEFT JOIN "direct_voter"
jbe@115 1383 ON "direct_voter"."issue_id" = "initiative"."issue_id"
jbe@115 1384 LEFT JOIN "vote"
jbe@115 1385 ON "vote"."initiative_id" = "initiative"."id"
jbe@115 1386 AND "vote"."member_id" = "direct_voter"."member_id"
jbe@115 1387 WHERE "initiative"."issue_id" = "issue_id_p"
jbe@115 1388 AND "initiative"."admitted" -- NOTE: NULL case is handled too
jbe@115 1389 GROUP BY "initiative"."id"
jbe@115 1390 ) AS "vote_counts",
jbe@115 1391 "issue",
jbe@115 1392 "policy"
jbe@115 1393 WHERE "vote_counts"."initiative_id" = "initiative"."id"
jbe@115 1394 AND "issue"."id" = "initiative"."issue_id"
jbe@115 1395 AND "policy"."id" = "issue"."policy_id";
jbe@115 1396 -- NOTE: "closed" column of issue must be set at this point
jbe@115 1397 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
jbe@115 1398 INSERT INTO "battle" (
jbe@115 1399 "issue_id",
jbe@115 1400 "winning_initiative_id", "losing_initiative_id",
jbe@115 1401 "count"
jbe@115 1402 ) SELECT
jbe@115 1403 "issue_id",
jbe@115 1404 "winning_initiative_id", "losing_initiative_id",
jbe@115 1405 "count"
jbe@115 1406 FROM "battle_view" WHERE "issue_id" = "issue_id_p";
jbe@115 1407 END;
jbe@115 1408 $$;
jbe@115 1409
jbe@115 1410 CREATE OR REPLACE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE)
jbe@115 1411 RETURNS VOID
jbe@115 1412 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@115 1413 DECLARE
jbe@115 1414 "dimension_v" INTEGER;
jbe@115 1415 "vote_matrix" INT4[][]; -- absolute votes
jbe@115 1416 "matrix" INT8[][]; -- defeat strength / best paths
jbe@115 1417 "i" INTEGER;
jbe@115 1418 "j" INTEGER;
jbe@115 1419 "k" INTEGER;
jbe@115 1420 "battle_row" "battle"%ROWTYPE;
jbe@115 1421 "rank_ary" INT4[];
jbe@115 1422 "rank_v" INT4;
jbe@115 1423 "done_v" INTEGER;
jbe@115 1424 "winners_ary" INTEGER[];
jbe@115 1425 "initiative_id_v" "initiative"."id"%TYPE;
jbe@115 1426 BEGIN
jbe@115 1427 PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE;
jbe@115 1428 SELECT count(1) INTO "dimension_v" FROM "initiative"
jbe@115 1429 WHERE "issue_id" = "issue_id_p" AND "agreed";
jbe@115 1430 IF "dimension_v" = 1 THEN
jbe@115 1431 UPDATE "initiative" SET "rank" = 1
jbe@115 1432 WHERE "issue_id" = "issue_id_p" AND "agreed";
jbe@115 1433 ELSIF "dimension_v" > 1 THEN
jbe@115 1434 -- Create "vote_matrix" with absolute number of votes in pairwise
jbe@115 1435 -- comparison:
jbe@115 1436 "vote_matrix" := "square_matrix_init_string"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
jbe@115 1437 "i" := 1;
jbe@115 1438 "j" := 2;
jbe@115 1439 FOR "battle_row" IN
jbe@115 1440 SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"
jbe@115 1441 ORDER BY "winning_initiative_id", "losing_initiative_id"
jbe@115 1442 LOOP
jbe@115 1443 "vote_matrix"["i"]["j"] := "battle_row"."count";
jbe@115 1444 IF "j" = "dimension_v" THEN
jbe@115 1445 "i" := "i" + 1;
jbe@115 1446 "j" := 1;
jbe@115 1447 ELSE
jbe@115 1448 "j" := "j" + 1;
jbe@115 1449 IF "j" = "i" THEN
jbe@115 1450 "j" := "j" + 1;
jbe@115 1451 END IF;
jbe@115 1452 END IF;
jbe@115 1453 END LOOP;
jbe@115 1454 IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN
jbe@115 1455 RAISE EXCEPTION 'Wrong battle count (should not happen)';
jbe@115 1456 END IF;
jbe@115 1457 -- Store defeat strengths in "matrix" using "defeat_strength"
jbe@115 1458 -- function:
jbe@115 1459 "matrix" := "square_matrix_init_string"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
jbe@115 1460 "i" := 1;
jbe@115 1461 LOOP
jbe@115 1462 "j" := 1;
jbe@115 1463 LOOP
jbe@115 1464 IF "i" != "j" THEN
jbe@115 1465 "matrix"["i"]["j"] := "defeat_strength"(
jbe@115 1466 "vote_matrix"["i"]["j"],
jbe@115 1467 "vote_matrix"["j"]["i"]
jbe@115 1468 );
jbe@115 1469 END IF;
jbe@115 1470 EXIT WHEN "j" = "dimension_v";
jbe@115 1471 "j" := "j" + 1;
jbe@115 1472 END LOOP;
jbe@115 1473 EXIT WHEN "i" = "dimension_v";
jbe@115 1474 "i" := "i" + 1;
jbe@115 1475 END LOOP;
jbe@115 1476 -- Find best paths:
jbe@115 1477 "i" := 1;
jbe@115 1478 LOOP
jbe@115 1479 "j" := 1;
jbe@115 1480 LOOP
jbe@115 1481 IF "i" != "j" THEN
jbe@115 1482 "k" := 1;
jbe@115 1483 LOOP
jbe@115 1484 IF "i" != "k" AND "j" != "k" THEN
jbe@115 1485 IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN
jbe@115 1486 IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN
jbe@115 1487 "matrix"["j"]["k"] := "matrix"["j"]["i"];
jbe@115 1488 END IF;
jbe@115 1489 ELSE
jbe@115 1490 IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN
jbe@115 1491 "matrix"["j"]["k"] := "matrix"["i"]["k"];
jbe@115 1492 END IF;
jbe@115 1493 END IF;
jbe@115 1494 END IF;
jbe@115 1495 EXIT WHEN "k" = "dimension_v";
jbe@115 1496 "k" := "k" + 1;
jbe@115 1497 END LOOP;
jbe@115 1498 END IF;
jbe@115 1499 EXIT WHEN "j" = "dimension_v";
jbe@115 1500 "j" := "j" + 1;
jbe@115 1501 END LOOP;
jbe@115 1502 EXIT WHEN "i" = "dimension_v";
jbe@115 1503 "i" := "i" + 1;
jbe@115 1504 END LOOP;
jbe@115 1505 -- Determine order of winners:
jbe@115 1506 "rank_ary" := "array_init_string"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
jbe@115 1507 "rank_v" := 1;
jbe@115 1508 "done_v" := 0;
jbe@115 1509 LOOP
jbe@115 1510 "winners_ary" := '{}';
jbe@115 1511 "i" := 1;
jbe@115 1512 LOOP
jbe@115 1513 IF "rank_ary"["i"] ISNULL THEN
jbe@115 1514 "j" := 1;
jbe@115 1515 LOOP
jbe@115 1516 IF
jbe@115 1517 "i" != "j" AND
jbe@115 1518 "rank_ary"["j"] ISNULL AND
jbe@115 1519 "matrix"["j"]["i"] > "matrix"["i"]["j"]
jbe@115 1520 THEN
jbe@115 1521 -- someone else is better
jbe@115 1522 EXIT;
jbe@115 1523 END IF;
jbe@115 1524 IF "j" = "dimension_v" THEN
jbe@115 1525 -- noone is better
jbe@115 1526 "winners_ary" := "winners_ary" || "i";
jbe@115 1527 EXIT;
jbe@115 1528 END IF;
jbe@115 1529 "j" := "j" + 1;
jbe@115 1530 END LOOP;
jbe@115 1531 END IF;
jbe@115 1532 EXIT WHEN "i" = "dimension_v";
jbe@115 1533 "i" := "i" + 1;
jbe@115 1534 END LOOP;
jbe@115 1535 "i" := 1;
jbe@115 1536 LOOP
jbe@115 1537 "rank_ary"["winners_ary"["i"]] := "rank_v";
jbe@115 1538 "done_v" := "done_v" + 1;
jbe@115 1539 EXIT WHEN "i" = array_upper("winners_ary", 1);
jbe@115 1540 "i" := "i" + 1;
jbe@115 1541 END LOOP;
jbe@115 1542 EXIT WHEN "done_v" = "dimension_v";
jbe@115 1543 "rank_v" := "rank_v" + 1;
jbe@115 1544 END LOOP;
jbe@115 1545 -- write preliminary ranks:
jbe@115 1546 "i" := 1;
jbe@115 1547 FOR "initiative_id_v" IN
jbe@115 1548 SELECT "id" FROM "initiative"
jbe@115 1549 WHERE "issue_id" = "issue_id_p" AND "agreed"
jbe@115 1550 ORDER BY "id"
jbe@115 1551 LOOP
jbe@115 1552 UPDATE "initiative" SET "rank" = "rank_ary"["i"]
jbe@115 1553 WHERE "id" = "initiative_id_v";
jbe@115 1554 "i" := "i" + 1;
jbe@115 1555 END LOOP;
jbe@115 1556 IF "i" != "dimension_v" + 1 THEN
jbe@115 1557 RAISE EXCEPTION 'Wrong winner count (should not happen)';
jbe@115 1558 END IF;
jbe@115 1559 -- straighten ranks (start counting with 1, no equal ranks):
jbe@115 1560 "rank_v" := 1;
jbe@115 1561 FOR "initiative_id_v" IN
jbe@115 1562 SELECT "id" FROM "initiative"
jbe@115 1563 WHERE "issue_id" = "issue_id_p" AND "rank" NOTNULL
jbe@115 1564 ORDER BY
jbe@115 1565 "rank",
jbe@115 1566 "vote_ratio"("positive_votes", "negative_votes") DESC,
jbe@115 1567 "id"
jbe@115 1568 LOOP
jbe@115 1569 UPDATE "initiative" SET "rank" = "rank_v"
jbe@115 1570 WHERE "id" = "initiative_id_v";
jbe@115 1571 "rank_v" := "rank_v" + 1;
jbe@115 1572 END LOOP;
jbe@115 1573 END IF;
jbe@115 1574 -- mark issue as finished
jbe@115 1575 UPDATE "issue" SET
jbe@115 1576 "state" =
jbe@121 1577 CASE WHEN "dimension_v" = 0 THEN
jbe@121 1578 'finished_without_winner'::"issue_state"
jbe@115 1579 ELSE
jbe@121 1580 'finished_with_winner'::"issue_state"
jbe@115 1581 END,
jbe@115 1582 "ranks_available" = TRUE
jbe@115 1583 WHERE "id" = "issue_id_p";
jbe@115 1584 RETURN;
jbe@115 1585 END;
jbe@115 1586 $$;
jbe@115 1587
jbe@115 1588 CREATE OR REPLACE FUNCTION "check_issue"
jbe@115 1589 ( "issue_id_p" "issue"."id"%TYPE )
jbe@115 1590 RETURNS VOID
jbe@115 1591 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@115 1592 DECLARE
jbe@115 1593 "issue_row" "issue"%ROWTYPE;
jbe@115 1594 "policy_row" "policy"%ROWTYPE;
jbe@115 1595 "voting_requested_v" BOOLEAN;
jbe@115 1596 BEGIN
jbe@115 1597 PERFORM "lock_issue"("issue_id_p");
jbe@115 1598 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
jbe@115 1599 -- only process open issues:
jbe@115 1600 IF "issue_row"."closed" ISNULL THEN
jbe@115 1601 SELECT * INTO "policy_row" FROM "policy"
jbe@115 1602 WHERE "id" = "issue_row"."policy_id";
jbe@115 1603 -- create a snapshot, unless issue is already fully frozen:
jbe@115 1604 IF "issue_row"."fully_frozen" ISNULL THEN
jbe@115 1605 PERFORM "create_snapshot"("issue_id_p");
jbe@115 1606 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
jbe@115 1607 END IF;
jbe@115 1608 -- eventually close or accept issues, which have not been accepted:
jbe@115 1609 IF "issue_row"."accepted" ISNULL THEN
jbe@115 1610 IF EXISTS (
jbe@115 1611 SELECT NULL FROM "initiative"
jbe@115 1612 WHERE "issue_id" = "issue_id_p"
jbe@115 1613 AND "supporter_count" > 0
jbe@115 1614 AND "supporter_count" * "policy_row"."issue_quorum_den"
jbe@115 1615 >= "issue_row"."population" * "policy_row"."issue_quorum_num"
jbe@115 1616 ) THEN
jbe@115 1617 -- accept issues, if supporter count is high enough
jbe@115 1618 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
jbe@115 1619 -- NOTE: "issue_row" used later
jbe@115 1620 "issue_row"."state" := 'discussion';
jbe@115 1621 "issue_row"."accepted" := now();
jbe@115 1622 UPDATE "issue" SET
jbe@115 1623 "state" = "issue_row"."state",
jbe@115 1624 "accepted" = "issue_row"."accepted"
jbe@115 1625 WHERE "id" = "issue_row"."id";
jbe@115 1626 ELSIF
jbe@115 1627 now() >= "issue_row"."created" + "issue_row"."admission_time"
jbe@115 1628 THEN
jbe@115 1629 -- close issues, if admission time has expired
jbe@115 1630 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
jbe@115 1631 UPDATE "issue" SET
jbe@115 1632 "state" = 'canceled_issue_not_accepted',
jbe@115 1633 "closed" = now()
jbe@115 1634 WHERE "id" = "issue_row"."id";
jbe@115 1635 END IF;
jbe@115 1636 END IF;
jbe@115 1637 -- eventually half freeze issues:
jbe@115 1638 IF
jbe@115 1639 -- NOTE: issue can't be closed at this point, if it has been accepted
jbe@115 1640 "issue_row"."accepted" NOTNULL AND
jbe@115 1641 "issue_row"."half_frozen" ISNULL
jbe@115 1642 THEN
jbe@115 1643 SELECT
jbe@115 1644 CASE
jbe@115 1645 WHEN "vote_now" * 2 > "issue_row"."population" THEN
jbe@115 1646 TRUE
jbe@115 1647 WHEN "vote_later" * 2 > "issue_row"."population" THEN
jbe@115 1648 FALSE
jbe@115 1649 ELSE NULL
jbe@115 1650 END
jbe@115 1651 INTO "voting_requested_v"
jbe@115 1652 FROM "issue" WHERE "id" = "issue_id_p";
jbe@115 1653 IF
jbe@115 1654 "voting_requested_v" OR (
jbe@115 1655 "voting_requested_v" ISNULL AND
jbe@115 1656 now() >= "issue_row"."accepted" + "issue_row"."discussion_time"
jbe@115 1657 )
jbe@115 1658 THEN
jbe@115 1659 PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze');
jbe@115 1660 -- NOTE: "issue_row" used later
jbe@115 1661 "issue_row"."state" := 'verification';
jbe@115 1662 "issue_row"."half_frozen" := now();
jbe@115 1663 UPDATE "issue" SET
jbe@115 1664 "state" = "issue_row"."state",
jbe@115 1665 "half_frozen" = "issue_row"."half_frozen"
jbe@115 1666 WHERE "id" = "issue_row"."id";
jbe@115 1667 END IF;
jbe@115 1668 END IF;
jbe@115 1669 -- close issues after some time, if all initiatives have been revoked:
jbe@115 1670 IF
jbe@115 1671 "issue_row"."closed" ISNULL AND
jbe@115 1672 NOT EXISTS (
jbe@115 1673 -- all initiatives are revoked
jbe@115 1674 SELECT NULL FROM "initiative"
jbe@115 1675 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
jbe@115 1676 ) AND (
jbe@115 1677 -- and issue has not been accepted yet
jbe@115 1678 "issue_row"."accepted" ISNULL OR
jbe@115 1679 NOT EXISTS (
jbe@115 1680 -- or no initiatives have been revoked lately
jbe@115 1681 SELECT NULL FROM "initiative"
jbe@115 1682 WHERE "issue_id" = "issue_id_p"
jbe@115 1683 AND now() < "revoked" + "issue_row"."verification_time"
jbe@115 1684 ) OR (
jbe@115 1685 -- or verification time has elapsed
jbe@115 1686 "issue_row"."half_frozen" NOTNULL AND
jbe@115 1687 "issue_row"."fully_frozen" ISNULL AND
jbe@115 1688 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
jbe@115 1689 )
jbe@115 1690 )
jbe@115 1691 THEN
jbe@115 1692 -- NOTE: "issue_row" used later
jbe@115 1693 IF "issue_row"."accepted" ISNULL THEN
jbe@115 1694 "issue_row"."state" := 'canceled_revoked_before_accepted';
jbe@115 1695 ELSIF "issue_row"."half_frozen" ISNULL THEN
jbe@115 1696 "issue_row"."state" := 'canceled_after_revocation_during_discussion';
jbe@115 1697 ELSE
jbe@115 1698 "issue_row"."state" := 'canceled_after_revocation_during_verification';
jbe@115 1699 END IF;
jbe@115 1700 "issue_row"."closed" := now();
jbe@115 1701 UPDATE "issue" SET
jbe@115 1702 "state" = "issue_row"."state",
jbe@115 1703 "closed" = "issue_row"."closed"
jbe@115 1704 WHERE "id" = "issue_row"."id";
jbe@115 1705 END IF;
jbe@115 1706 -- fully freeze issue after verification time:
jbe@115 1707 IF
jbe@115 1708 "issue_row"."half_frozen" NOTNULL AND
jbe@115 1709 "issue_row"."fully_frozen" ISNULL AND
jbe@115 1710 "issue_row"."closed" ISNULL AND
jbe@115 1711 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
jbe@115 1712 THEN
jbe@115 1713 PERFORM "freeze_after_snapshot"("issue_id_p");
jbe@115 1714 -- NOTE: "issue" might change, thus "issue_row" has to be updated below
jbe@115 1715 END IF;
jbe@115 1716 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
jbe@115 1717 -- close issue by calling close_voting(...) after voting time:
jbe@115 1718 IF
jbe@115 1719 "issue_row"."closed" ISNULL AND
jbe@115 1720 "issue_row"."fully_frozen" NOTNULL AND
jbe@115 1721 now() >= "issue_row"."fully_frozen" + "issue_row"."voting_time"
jbe@115 1722 THEN
jbe@115 1723 PERFORM "close_voting"("issue_id_p");
jbe@115 1724 -- calculate ranks will not consume much time and can be done now
jbe@115 1725 PERFORM "calculate_ranks"("issue_id_p");
jbe@115 1726 END IF;
jbe@115 1727 END IF;
jbe@115 1728 RETURN;
jbe@115 1729 END;
jbe@115 1730 $$;
jbe@115 1731
jbe@115 1732 CREATE OR REPLACE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE)
jbe@115 1733 RETURNS VOID
jbe@115 1734 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@115 1735 DECLARE
jbe@115 1736 "issue_row" "issue"%ROWTYPE;
jbe@115 1737 BEGIN
jbe@115 1738 SELECT * INTO "issue_row"
jbe@115 1739 FROM "issue" WHERE "id" = "issue_id_p"
jbe@115 1740 FOR UPDATE;
jbe@115 1741 IF "issue_row"."cleaned" ISNULL THEN
jbe@115 1742 UPDATE "issue" SET
jbe@115 1743 "closed" = NULL,
jbe@115 1744 "ranks_available" = FALSE
jbe@115 1745 WHERE "id" = "issue_id_p";
jbe@115 1746 DELETE FROM "delegating_voter"
jbe@115 1747 WHERE "issue_id" = "issue_id_p";
jbe@115 1748 DELETE FROM "direct_voter"
jbe@115 1749 WHERE "issue_id" = "issue_id_p";
jbe@115 1750 DELETE FROM "delegating_interest_snapshot"
jbe@115 1751 WHERE "issue_id" = "issue_id_p";
jbe@115 1752 DELETE FROM "direct_interest_snapshot"
jbe@115 1753 WHERE "issue_id" = "issue_id_p";
jbe@115 1754 DELETE FROM "delegating_population_snapshot"
jbe@115 1755 WHERE "issue_id" = "issue_id_p";
jbe@115 1756 DELETE FROM "direct_population_snapshot"
jbe@115 1757 WHERE "issue_id" = "issue_id_p";
jbe@115 1758 DELETE FROM "non_voter"
jbe@115 1759 WHERE "issue_id" = "issue_id_p";
jbe@115 1760 DELETE FROM "delegation"
jbe@115 1761 WHERE "issue_id" = "issue_id_p";
jbe@115 1762 DELETE FROM "supporter"
jbe@115 1763 WHERE "issue_id" = "issue_id_p";
jbe@115 1764 UPDATE "issue" SET
jbe@115 1765 "closed" = "issue_row"."closed",
jbe@115 1766 "ranks_available" = "issue_row"."ranks_available",
jbe@115 1767 "cleaned" = now()
jbe@115 1768 WHERE "id" = "issue_id_p";
jbe@115 1769 END IF;
jbe@115 1770 RETURN;
jbe@115 1771 END;
jbe@115 1772 $$;
jbe@115 1773
jbe@115 1774 CREATE OR REPLACE FUNCTION "check_issue"
jbe@115 1775 ( "issue_id_p" "issue"."id"%TYPE )
jbe@115 1776 RETURNS VOID
jbe@115 1777 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@115 1778 DECLARE
jbe@115 1779 "issue_row" "issue"%ROWTYPE;
jbe@115 1780 "policy_row" "policy"%ROWTYPE;
jbe@115 1781 "voting_requested_v" BOOLEAN;
jbe@115 1782 BEGIN
jbe@115 1783 PERFORM "lock_issue"("issue_id_p");
jbe@115 1784 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
jbe@115 1785 -- only process open issues:
jbe@115 1786 IF "issue_row"."closed" ISNULL THEN
jbe@115 1787 SELECT * INTO "policy_row" FROM "policy"
jbe@115 1788 WHERE "id" = "issue_row"."policy_id";
jbe@115 1789 -- create a snapshot, unless issue is already fully frozen:
jbe@115 1790 IF "issue_row"."fully_frozen" ISNULL THEN
jbe@115 1791 PERFORM "create_snapshot"("issue_id_p");
jbe@115 1792 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
jbe@115 1793 END IF;
jbe@115 1794 -- eventually close or accept issues, which have not been accepted:
jbe@115 1795 IF "issue_row"."accepted" ISNULL THEN
jbe@115 1796 IF EXISTS (
jbe@115 1797 SELECT NULL FROM "initiative"
jbe@115 1798 WHERE "issue_id" = "issue_id_p"
jbe@115 1799 AND "supporter_count" > 0
jbe@115 1800 AND "supporter_count" * "policy_row"."issue_quorum_den"
jbe@115 1801 >= "issue_row"."population" * "policy_row"."issue_quorum_num"
jbe@115 1802 ) THEN
jbe@115 1803 -- accept issues, if supporter count is high enough
jbe@115 1804 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
jbe@115 1805 -- NOTE: "issue_row" used later
jbe@115 1806 "issue_row"."state" := 'discussion';
jbe@115 1807 "issue_row"."accepted" := now();
jbe@115 1808 UPDATE "issue" SET
jbe@115 1809 "state" = "issue_row"."state",
jbe@115 1810 "accepted" = "issue_row"."accepted"
jbe@115 1811 WHERE "id" = "issue_row"."id";
jbe@115 1812 ELSIF
jbe@115 1813 now() >= "issue_row"."created" + "issue_row"."admission_time"
jbe@115 1814 THEN
jbe@115 1815 -- close issues, if admission time has expired
jbe@115 1816 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
jbe@115 1817 UPDATE "issue" SET
jbe@115 1818 "state" = 'canceled_issue_not_accepted',
jbe@115 1819 "closed" = now()
jbe@115 1820 WHERE "id" = "issue_row"."id";
jbe@115 1821 END IF;
jbe@115 1822 END IF;
jbe@115 1823 -- eventually half freeze issues:
jbe@115 1824 IF
jbe@115 1825 -- NOTE: issue can't be closed at this point, if it has been accepted
jbe@115 1826 "issue_row"."accepted" NOTNULL AND
jbe@115 1827 "issue_row"."half_frozen" ISNULL
jbe@115 1828 THEN
jbe@115 1829 SELECT
jbe@115 1830 CASE
jbe@115 1831 WHEN "vote_now" * 2 > "issue_row"."population" THEN
jbe@115 1832 TRUE
jbe@115 1833 WHEN "vote_later" * 2 > "issue_row"."population" THEN
jbe@115 1834 FALSE
jbe@115 1835 ELSE NULL
jbe@115 1836 END
jbe@115 1837 INTO "voting_requested_v"
jbe@115 1838 FROM "issue" WHERE "id" = "issue_id_p";
jbe@115 1839 IF
jbe@115 1840 "voting_requested_v" OR (
jbe@115 1841 "voting_requested_v" ISNULL AND
jbe@115 1842 now() >= "issue_row"."accepted" + "issue_row"."discussion_time"
jbe@115 1843 )
jbe@115 1844 THEN
jbe@115 1845 PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze');
jbe@115 1846 -- NOTE: "issue_row" used later
jbe@115 1847 "issue_row"."state" := 'verification';
jbe@115 1848 "issue_row"."half_frozen" := now();
jbe@115 1849 UPDATE "issue" SET
jbe@115 1850 "state" = "issue_row"."state",
jbe@115 1851 "half_frozen" = "issue_row"."half_frozen"
jbe@115 1852 WHERE "id" = "issue_row"."id";
jbe@115 1853 END IF;
jbe@115 1854 END IF;
jbe@115 1855 -- close issues after some time, if all initiatives have been revoked:
jbe@115 1856 IF
jbe@115 1857 "issue_row"."closed" ISNULL AND
jbe@115 1858 NOT EXISTS (
jbe@115 1859 -- all initiatives are revoked
jbe@115 1860 SELECT NULL FROM "initiative"
jbe@115 1861 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
jbe@115 1862 ) AND (
jbe@115 1863 -- and issue has not been accepted yet
jbe@115 1864 "issue_row"."accepted" ISNULL OR
jbe@115 1865 NOT EXISTS (
jbe@115 1866 -- or no initiatives have been revoked lately
jbe@115 1867 SELECT NULL FROM "initiative"
jbe@115 1868 WHERE "issue_id" = "issue_id_p"
jbe@115 1869 AND now() < "revoked" + "issue_row"."verification_time"
jbe@115 1870 ) OR (
jbe@115 1871 -- or verification time has elapsed
jbe@115 1872 "issue_row"."half_frozen" NOTNULL AND
jbe@115 1873 "issue_row"."fully_frozen" ISNULL AND
jbe@115 1874 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
jbe@115 1875 )
jbe@115 1876 )
jbe@115 1877 THEN
jbe@115 1878 -- NOTE: "issue_row" used later
jbe@115 1879 IF "issue_row"."accepted" ISNULL THEN
jbe@115 1880 "issue_row"."state" := 'canceled_revoked_before_accepted';
jbe@115 1881 ELSIF "issue_row"."half_frozen" ISNULL THEN
jbe@115 1882 "issue_row"."state" := 'canceled_after_revocation_during_discussion';
jbe@115 1883 ELSE
jbe@115 1884 "issue_row"."state" := 'canceled_after_revocation_during_verification';
jbe@115 1885 END IF;
jbe@115 1886 "issue_row"."closed" := now();
jbe@115 1887 UPDATE "issue" SET
jbe@115 1888 "state" = "issue_row"."state",
jbe@115 1889 "closed" = "issue_row"."closed"
jbe@115 1890 WHERE "id" = "issue_row"."id";
jbe@115 1891 END IF;
jbe@115 1892 -- fully freeze issue after verification time:
jbe@115 1893 IF
jbe@115 1894 "issue_row"."half_frozen" NOTNULL AND
jbe@115 1895 "issue_row"."fully_frozen" ISNULL AND
jbe@115 1896 "issue_row"."closed" ISNULL AND
jbe@115 1897 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
jbe@115 1898 THEN
jbe@115 1899 PERFORM "freeze_after_snapshot"("issue_id_p");
jbe@115 1900 -- NOTE: "issue" might change, thus "issue_row" has to be updated below
jbe@115 1901 END IF;
jbe@115 1902 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
jbe@115 1903 -- close issue by calling close_voting(...) after voting time:
jbe@115 1904 IF
jbe@115 1905 "issue_row"."closed" ISNULL AND
jbe@115 1906 "issue_row"."fully_frozen" NOTNULL AND
jbe@115 1907 now() >= "issue_row"."fully_frozen" + "issue_row"."voting_time"
jbe@115 1908 THEN
jbe@115 1909 PERFORM "close_voting"("issue_id_p");
jbe@115 1910 -- calculate ranks will not consume much time and can be done now
jbe@115 1911 PERFORM "calculate_ranks"("issue_id_p");
jbe@115 1912 END IF;
jbe@115 1913 END IF;
jbe@115 1914 RETURN;
jbe@115 1915 END;
jbe@115 1916 $$;
jbe@115 1917
jbe@115 1918 CREATE OR REPLACE FUNCTION "delete_private_data"()
jbe@115 1919 RETURNS VOID
jbe@115 1920 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@115 1921 BEGIN
jbe@115 1922 UPDATE "member" SET
jbe@115 1923 "last_login" = NULL,
jbe@115 1924 "login" = NULL,
jbe@115 1925 "password" = NULL,
jbe@115 1926 "notify_email" = NULL,
jbe@115 1927 "notify_email_unconfirmed" = NULL,
jbe@115 1928 "notify_email_secret" = NULL,
jbe@115 1929 "notify_email_secret_expiry" = NULL,
jbe@115 1930 "notify_email_lock_expiry" = NULL,
jbe@115 1931 "password_reset_secret" = NULL,
jbe@115 1932 "password_reset_secret_expiry" = NULL,
jbe@115 1933 "organizational_unit" = NULL,
jbe@115 1934 "internal_posts" = NULL,
jbe@115 1935 "realname" = NULL,
jbe@115 1936 "birthday" = NULL,
jbe@115 1937 "address" = NULL,
jbe@115 1938 "email" = NULL,
jbe@115 1939 "xmpp_address" = NULL,
jbe@115 1940 "website" = NULL,
jbe@115 1941 "phone" = NULL,
jbe@115 1942 "mobile_phone" = NULL,
jbe@115 1943 "profession" = NULL,
jbe@115 1944 "external_memberships" = NULL,
jbe@115 1945 "external_posts" = NULL,
jbe@115 1946 "statement" = NULL;
jbe@115 1947 -- "text_search_data" is updated by triggers
jbe@115 1948 DELETE FROM "invite_code";
jbe@115 1949 DELETE FROM "setting";
jbe@115 1950 DELETE FROM "setting_map";
jbe@115 1951 DELETE FROM "member_relation_setting";
jbe@115 1952 DELETE FROM "member_image";
jbe@115 1953 DELETE FROM "contact";
jbe@115 1954 DELETE FROM "ignored_member";
jbe@115 1955 DELETE FROM "session";
jbe@115 1956 DELETE FROM "area_setting";
jbe@115 1957 DELETE FROM "issue_setting";
jbe@115 1958 DELETE FROM "ignored_initiative";
jbe@115 1959 DELETE FROM "initiative_setting";
jbe@115 1960 DELETE FROM "suggestion_setting";
jbe@115 1961 DELETE FROM "non_voter";
jbe@115 1962 DELETE FROM "direct_voter" USING "issue"
jbe@115 1963 WHERE "direct_voter"."issue_id" = "issue"."id"
jbe@115 1964 AND "issue"."closed" ISNULL;
jbe@115 1965 RETURN;
jbe@115 1966 END;
jbe@115 1967 $$;
jbe@115 1968
jbe@115 1969
jbe@115 1970 -- Delete old "delegation_scope" TYPE:
jbe@115 1971
jbe@115 1972 DROP TYPE "delegation_scope_old";
jbe@115 1973
jbe@115 1974
jbe@115 1975 COMMIT;
jbe@115 1976
jbe@115 1977
jbe@122 1978 -- Generate issue states and add constraints:
jbe@115 1979
jbe@115 1980 UPDATE "issue" SET "state" =
jbe@115 1981 CASE
jbe@115 1982 WHEN "closed" ISNULL THEN
jbe@115 1983 CASE
jbe@115 1984 WHEN "accepted" ISNULL THEN
jbe@115 1985 'admission'::"issue_state"
jbe@115 1986 WHEN "half_frozen" ISNULL THEN
jbe@115 1987 'discussion'::"issue_state"
jbe@115 1988 WHEN "fully_frozen" ISNULL THEN
jbe@115 1989 'verification'::"issue_state"
jbe@115 1990 ELSE
jbe@115 1991 'voting'::"issue_state"
jbe@115 1992 END
jbe@115 1993 WHEN "fully_frozen" NOTNULL THEN
jbe@115 1994 CASE
jbe@115 1995 WHEN "fully_frozen" = "closed" THEN
jbe@115 1996 'canceled_no_initiative_admitted'::"issue_state"
jbe@115 1997 ELSE
jbe@115 1998 'finished_without_winner'::"issue_state" -- NOTE: corrected later
jbe@115 1999 END
jbe@115 2000 WHEN "half_frozen" NOTNULL THEN
jbe@115 2001 'canceled_after_revocation_during_verification'::"issue_state"
jbe@115 2002 WHEN "accepted" NOTNULL THEN
jbe@115 2003 'canceled_after_revocation_during_discussion'::"issue_state"
jbe@115 2004 ELSE
jbe@115 2005 'canceled_revoked_before_accepted'::"issue_state" -- NOTE: corrected later
jbe@115 2006 END;
jbe@117 2007 UPDATE "issue" SET "state" = 'finished_with_winner'
jbe@117 2008 FROM "initiative"
jbe@117 2009 WHERE "issue"."id" = "initiative"."issue_id"
jbe@117 2010 AND "issue"."state" = 'finished_without_winner'
jbe@117 2011 AND "initiative"."agreed";
jbe@117 2012 UPDATE "issue" SET "state" = 'canceled_issue_not_accepted'
jbe@117 2013 FROM "initiative"
jbe@117 2014 WHERE "issue"."id" = "initiative"."issue_id"
jbe@117 2015 AND "issue"."state" = 'canceled_revoked_before_accepted'
jbe@117 2016 AND "initiative"."revoked" ISNULL;
jbe@115 2017
jbe@115 2018 ALTER TABLE "issue" ALTER "state" SET NOT NULL;
jbe@115 2019
jbe@115 2020 ALTER TABLE "issue" DROP CONSTRAINT "valid_state";
jbe@115 2021 ALTER TABLE "issue" ADD CONSTRAINT "valid_state" CHECK ((
jbe@115 2022 ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
jbe@115 2023 ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
jbe@115 2024 ("accepted" NOTNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
jbe@115 2025 ("accepted" NOTNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
jbe@115 2026 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
jbe@115 2027 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
jbe@115 2028 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
jbe@115 2029 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
jbe@115 2030 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = TRUE)
jbe@115 2031 ) AND (
jbe@115 2032 ("state" = 'admission' AND "closed" ISNULL AND "accepted" ISNULL) OR
jbe@115 2033 ("state" = 'discussion' AND "closed" ISNULL AND "accepted" NOTNULL AND "half_frozen" ISNULL) OR
jbe@115 2034 ("state" = 'verification' AND "closed" ISNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL) OR
jbe@115 2035 ("state" = 'voting' AND "closed" ISNULL AND "fully_frozen" NOTNULL) OR
jbe@115 2036 ("state" = 'canceled_revoked_before_accepted' AND "closed" NOTNULL AND "accepted" ISNULL) OR
jbe@115 2037 ("state" = 'canceled_issue_not_accepted' AND "closed" NOTNULL AND "accepted" ISNULL) OR
jbe@115 2038 ("state" = 'canceled_after_revocation_during_discussion' AND "closed" NOTNULL AND "half_frozen" ISNULL) OR
jbe@115 2039 ("state" = 'canceled_after_revocation_during_verification' AND "closed" NOTNULL AND "fully_frozen" ISNULL) OR
jbe@115 2040 ("state" = 'calculation' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = FALSE) OR
jbe@115 2041 ("state" = 'canceled_no_initiative_admitted' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE) OR
jbe@115 2042 ("state" = 'finished_without_winner' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE) OR
jbe@115 2043 ("state" = 'finished_with_winner' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE)
jbe@115 2044 ));
jbe@115 2045
jbe@115 2046
jbe@122 2047 -- Guess "revoked_by_member_id" values based on author of current draft and add constraint:
jbe@115 2048
jbe@115 2049 UPDATE "initiative" SET "revoked_by_member_id" = "author_id"
jbe@115 2050 FROM "current_draft"
jbe@115 2051 WHERE "initiative"."id" = "current_draft"."initiative_id"
jbe@115 2052 AND "initiative"."revoked" NOTNULL;
jbe@115 2053
jbe@115 2054 ALTER TABLE "initiative" ADD
jbe@115 2055 CONSTRAINT "all_or_none_of_revoked_and_revoked_by_member_id_must_be_null"
jbe@115 2056 CHECK ("revoked" NOTNULL = "revoked_by_member_id" NOTNULL);
jbe@115 2057
jbe@115 2058
jbe@122 2059 -- Fill "unit_id" column with default value where neccessary and add constraints:
jbe@115 2060
jbe@115 2061 UPDATE "delegation" SET "unit_id" = 1 WHERE "scope" = 'unit';
jbe@115 2062
jbe@115 2063 ALTER TABLE "delegation" ADD CONSTRAINT "area_id_and_issue_id_set_according_to_scope"
jbe@115 2064 CHECK (
jbe@115 2065 ("scope" = 'unit' AND "unit_id" NOTNULL AND "area_id" ISNULL AND "issue_id" ISNULL ) OR
jbe@115 2066 ("scope" = 'area' AND "unit_id" ISNULL AND "area_id" NOTNULL AND "issue_id" ISNULL ) OR
jbe@115 2067 ("scope" = 'issue' AND "unit_id" ISNULL AND "area_id" ISNULL AND "issue_id" NOTNULL) );
jbe@117 2068
jbe@117 2069
jbe@118 2070 -- Filling of "event" table with old (reconstructed) events:
jbe@118 2071
jbe@118 2072 DELETE FROM "event";
jbe@118 2073 SELECT setval('event_id_seq', 1, false);
jbe@118 2074
jbe@118 2075 INSERT INTO "event"
jbe@118 2076 ( "occurrence", "event", "member_id", "issue_id", "state",
jbe@118 2077 "initiative_id", "draft_id", "suggestion_id" )
jbe@118 2078 SELECT * FROM (
jbe@118 2079 SELECT * FROM (
jbe@118 2080 SELECT DISTINCT ON ("initiative"."id")
jbe@118 2081 "timeline"."occurrence",
jbe@118 2082 CASE WHEN "issue_creation"."issue_id" NOTNULL THEN
jbe@118 2083 'initiative_created_in_new_issue'::"event_type"
jbe@118 2084 ELSE
jbe@118 2085 'initiative_created_in_existing_issue'::"event_type"
jbe@118 2086 END,
jbe@118 2087 "draft"."author_id",
jbe@118 2088 "issue"."id",
jbe@118 2089 CASE
jbe@118 2090 WHEN "timeline"."occurrence" < "issue"."accepted" THEN
jbe@118 2091 'admission'::"issue_state"
jbe@118 2092 WHEN "timeline"."occurrence" < "issue"."half_frozen" THEN
jbe@118 2093 'discussion'::"issue_state"
jbe@118 2094 ELSE
jbe@118 2095 'verification'::"issue_state"
jbe@118 2096 END,
jbe@118 2097 "initiative"."id",
jbe@118 2098 "draft"."id",
jbe@118 2099 NULL::INT8
jbe@118 2100 FROM "timeline"
jbe@118 2101 JOIN "initiative" ON "timeline"."initiative_id" = "initiative"."id"
jbe@118 2102 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
jbe@118 2103 LEFT JOIN "timeline" AS "issue_creation"
jbe@118 2104 ON "initiative"."issue_id" = "issue_creation"."issue_id"
jbe@118 2105 AND "issue_creation"."event" = 'issue_created'
jbe@118 2106 AND "timeline"."occurrence" = "issue_creation"."occurrence"
jbe@118 2107 JOIN "draft"
jbe@118 2108 ON "initiative"."id" = "draft"."initiative_id"
jbe@118 2109 WHERE "timeline"."event" = 'initiative_created'
jbe@118 2110 ORDER BY "initiative"."id", "draft"."id"
jbe@118 2111 ) AS "subquery" -- NOTE: subquery needed due to DISTINCT/ORDER
jbe@118 2112 UNION ALL
jbe@118 2113 SELECT
jbe@118 2114 "timeline"."occurrence",
jbe@118 2115 'issue_state_changed'::"event_type",
jbe@118 2116 NULL,
jbe@118 2117 "issue"."id",
jbe@118 2118 CASE
jbe@118 2119 WHEN "timeline"."event" IN (
jbe@118 2120 'issue_canceled',
jbe@118 2121 'issue_finished_without_voting',
jbe@118 2122 'issue_finished_after_voting'
jbe@118 2123 ) THEN
jbe@118 2124 "issue"."state"
jbe@118 2125 WHEN "timeline"."event" = 'issue_accepted' THEN
jbe@118 2126 'discussion'::"issue_state"
jbe@118 2127 WHEN "timeline"."event" = 'issue_half_frozen' THEN
jbe@118 2128 'verification'::"issue_state"
jbe@118 2129 WHEN "timeline"."event" = 'issue_voting_started' THEN
jbe@118 2130 'voting'::"issue_state"
jbe@118 2131 END,
jbe@118 2132 NULL,
jbe@118 2133 NULL,
jbe@118 2134 NULL
jbe@118 2135 FROM "timeline"
jbe@118 2136 JOIN "issue" ON "timeline"."issue_id" = "issue"."id"
jbe@118 2137 WHERE "timeline"."event" IN (
jbe@118 2138 'issue_canceled',
jbe@118 2139 'issue_accepted',
jbe@118 2140 'issue_half_frozen',
jbe@118 2141 'issue_finished_without_voting',
jbe@118 2142 'issue_voting_started',
jbe@118 2143 'issue_finished_after_voting' )
jbe@118 2144 UNION ALL
jbe@118 2145 SELECT
jbe@118 2146 "timeline"."occurrence",
jbe@118 2147 'initiative_revoked'::"event_type",
jbe@118 2148 "initiative"."revoked_by_member_id",
jbe@118 2149 "issue"."id",
jbe@118 2150 CASE
jbe@118 2151 WHEN "timeline"."occurrence" < "issue"."accepted" THEN
jbe@118 2152 'admission'::"issue_state"
jbe@118 2153 WHEN "timeline"."occurrence" < "issue"."half_frozen" THEN
jbe@118 2154 'discussion'::"issue_state"
jbe@118 2155 ELSE
jbe@118 2156 'verification'::"issue_state"
jbe@118 2157 END,
jbe@118 2158 "initiative"."id",
jbe@118 2159 "current_draft"."id",
jbe@118 2160 NULL
jbe@118 2161 FROM "timeline"
jbe@118 2162 JOIN "initiative" ON "timeline"."initiative_id" = "initiative"."id"
jbe@118 2163 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
jbe@118 2164 JOIN "current_draft" ON "initiative"."id" = "current_draft"."initiative_id"
jbe@118 2165 WHERE "timeline"."event" = 'initiative_revoked'
jbe@118 2166 UNION ALL
jbe@118 2167 SELECT
jbe@118 2168 "timeline"."occurrence",
jbe@118 2169 'new_draft_created'::"event_type",
jbe@118 2170 "draft"."author_id",
jbe@118 2171 "issue"."id",
jbe@118 2172 CASE
jbe@118 2173 WHEN "timeline"."occurrence" < "issue"."accepted" THEN
jbe@118 2174 'admission'::"issue_state"
jbe@118 2175 WHEN "timeline"."occurrence" < "issue"."half_frozen" THEN
jbe@118 2176 'discussion'::"issue_state"
jbe@118 2177 ELSE
jbe@118 2178 'verification'::"issue_state"
jbe@118 2179 END,
jbe@118 2180 "initiative"."id",
jbe@118 2181 "draft"."id",
jbe@118 2182 NULL
jbe@118 2183 FROM "timeline"
jbe@118 2184 JOIN "draft" ON "timeline"."draft_id" = "draft"."id"
jbe@118 2185 JOIN "initiative" ON "draft"."initiative_id" = "initiative"."id"
jbe@118 2186 JOIN "issue" ON "initiative"."issue_id" = "issue"."id"
jbe@118 2187 LEFT JOIN "timeline" AS "initiative_creation"
jbe@118 2188 ON "initiative"."id" = "initiative_creation"."initiative_id"
jbe@118 2189 AND "initiative_creation"."event" = 'initiative_created'
jbe@118 2190 AND "timeline"."occurrence" = "initiative_creation"."occurrence"
jbe@118 2191 WHERE "timeline"."event" = 'draft_created'
jbe@118 2192 AND "initiative_creation"."initiative_id" ISNULL
jbe@118 2193 UNION ALL
jbe@118 2194 SELECT
jbe@118 2195 "timeline"."occurrence",
jbe@118 2196 'suggestion_created'::"event_type",
jbe@118 2197 "suggestion"."author_id",
jbe@118 2198 "issue"."id",
jbe@118 2199 CASE
jbe@118 2200 WHEN "timeline"."occurrence" < "issue"."accepted" THEN
jbe@118 2201 'admission'::"issue_state"
jbe@118 2202 WHEN "timeline"."occurrence" < "issue"."half_frozen" THEN
jbe@118 2203 'discussion'::"issue_state"
jbe@118 2204 ELSE
jbe@118 2205 'verification'::"issue_state"
jbe@118 2206 END,
jbe@118 2207 "initiative"."id",
jbe@118 2208 NULL,
jbe@118 2209 "suggestion"."id"
jbe@118 2210 FROM "timeline"
jbe@118 2211 JOIN "suggestion" ON "timeline"."suggestion_id" = "suggestion"."id"
jbe@118 2212 JOIN "initiative" ON "suggestion"."initiative_id" = "initiative"."id"
jbe@118 2213 JOIN "issue" ON "initiative"."issue_id" = "issue"."id"
jbe@118 2214 WHERE "timeline"."event" = 'suggestion_created'
jbe@118 2215 ) AS "subquery"
jbe@118 2216 ORDER BY "occurrence";
jbe@118 2217
jbe@118 2218

Impressum / About Us