liquid_feedback_core

annotate update/core-update.v1.3.1-v1.4.0_rc1.sql @ 117:a5d39efbfe5b

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

Impressum / About Us