liquid_feedback_core

changeset 116:1cec513e27ac

Fixed file name of SQL update script (v1.3.1(!) to v1.4.0_rc1)
author jbe
date Mon Mar 07 03:50:00 2011 +0100 (2011-03-07)
parents 30e0200f82e9
children a5d39efbfe5b
files update/core-update.v1.3.0-v1.4.0_rc1.sql update/core-update.v1.3.1-v1.4.0_rc1.sql
line diff
     1.1 --- a/update/core-update.v1.3.0-v1.4.0_rc1.sql	Mon Mar 07 03:35:25 2011 +0100
     1.2 +++ /dev/null	Thu Jan 01 00:00:00 1970 +0000
     1.3 @@ -1,2081 +0,0 @@
     1.4 -BEGIN;  -- NOTE: file contains additional statements AFTER this BEGIN/COMMIT block!
     1.5 -
     1.6 -
     1.7 --- Update version information:
     1.8 -
     1.9 -CREATE OR REPLACE VIEW "liquid_feedback_version" AS
    1.10 -  SELECT * FROM (VALUES ('1.4.0_rc1', 1, 4, -1))
    1.11 -  AS "subquery"("string", "major", "minor", "revision");
    1.12 -
    1.13 -
    1.14 --- New columns "notify_level" and "notify_event_id" in "member" table:
    1.15 -
    1.16 -CREATE TYPE "notify_level" AS ENUM
    1.17 -  ('none', 'voting', 'verification', 'discussion', 'all');
    1.18 -
    1.19 -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';
    1.20 -
    1.21 -ALTER TABLE "member" ADD "notify_level" "notify_level" NOT NULL DEFAULT 'none';
    1.22 -ALTER TABLE "member" ADD "notify_event_id" INT8;
    1.23 -
    1.24 -COMMENT ON COLUMN "member"."notify_level"    IS 'Selects which event notifications are to be sent to the "notify_email" mail address';
    1.25 -COMMENT ON COLUMN "member"."notify_event_id" IS 'Latest "id" of an "event" the member was notified about';
    1.26 -
    1.27 -
    1.28 --- Add primary key with type SERIAL8 (INT8) for "invite_code" table:
    1.29 -
    1.30 -ALTER TABLE "invite_code" DROP CONSTRAINT "invite_code_pkey";
    1.31 -ALTER TABLE "invite_code" ALTER "code" SET NOT NULL;
    1.32 -ALTER TABLE "invite_code" ADD UNIQUE ("code");
    1.33 -ALTER TABLE "invite_code" ADD "id" SERIAL8 PRIMARY KEY;
    1.34 -
    1.35 -
    1.36 --- Add index for "other_member_id" column of "contact" table:
    1.37 -
    1.38 -CREATE INDEX "contact_other_member_id_idx" ON "contact" ("other_member_id");
    1.39 -
    1.40 -
    1.41 --- New table "ignored_member":
    1.42 -
    1.43 -CREATE TABLE "ignored_member" (
    1.44 -        PRIMARY KEY ("member_id", "other_member_id"),
    1.45 -        "member_id"             INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    1.46 -        "other_member_id"       INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
    1.47 -CREATE INDEX "ignored_member_other_member_id_idx" ON "ignored_member" ("other_member_id");
    1.48 -
    1.49 -COMMENT ON TABLE "ignored_member" IS 'Possibility to filter other members';
    1.50 -
    1.51 -COMMENT ON COLUMN "ignored_member"."member_id"       IS 'Member ignoring someone';
    1.52 -COMMENT ON COLUMN "ignored_member"."other_member_id" IS 'Member being ignored';
    1.53 -
    1.54 -
    1.55 --- New table "unit" with default entry:
    1.56 -
    1.57 -CREATE TABLE "unit" (
    1.58 -        "id"                    SERIAL4         PRIMARY KEY,
    1.59 -        "parent_id"             INT4            REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    1.60 -        "active"                BOOLEAN         NOT NULL DEFAULT TRUE,
    1.61 -        "name"                  TEXT            NOT NULL,
    1.62 -        "description"           TEXT            NOT NULL DEFAULT '',
    1.63 -        "member_count"          INT4,
    1.64 -        "text_search_data"      TSVECTOR );
    1.65 -CREATE INDEX "unit_root_idx" ON "unit" ("id") WHERE "parent_id" ISNULL;
    1.66 -CREATE INDEX "unit_parent_id_idx" ON "unit" ("parent_id");
    1.67 -CREATE INDEX "unit_active_idx" ON "unit" ("active");
    1.68 -CREATE INDEX "unit_text_search_data_idx" ON "unit" USING gin ("text_search_data");
    1.69 -CREATE TRIGGER "update_text_search_data"
    1.70 -  BEFORE INSERT OR UPDATE ON "unit"
    1.71 -  FOR EACH ROW EXECUTE PROCEDURE
    1.72 -  tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
    1.73 -    "name", "description" );
    1.74 -
    1.75 -COMMENT ON TABLE "unit" IS 'Organizational units organized as trees; Delegations are not inherited through these trees.';
    1.76 -
    1.77 -COMMENT ON COLUMN "unit"."parent_id"    IS 'Parent id of tree node; Multiple roots allowed';
    1.78 -COMMENT ON COLUMN "unit"."active"       IS 'TRUE means new issues can be created in units of this area';
    1.79 -COMMENT ON COLUMN "unit"."member_count" IS 'Count of members as determined by column "voting_right" in table "privilege"';
    1.80 -
    1.81 -INSERT INTO "unit" ("name") VALUES ('Main');  -- NOTE: gets id 1
    1.82 -
    1.83 -
    1.84 --- New column "unit_id" in table "area":
    1.85 -
    1.86 -ALTER TABLE "area" ADD "unit_id" INT4 DEFAULT 1
    1.87 -  NOT NULL REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
    1.88 -ALTER TABLE "area" ALTER "unit_id" DROP DEFAULT;
    1.89 -
    1.90 -CREATE INDEX "area_unit_id_idx" ON "area" ("unit_id");
    1.91 -
    1.92 -
    1.93 --- Issue states:
    1.94 -
    1.95 -CREATE TYPE "issue_state" AS ENUM (
    1.96 -        'admission', 'discussion', 'verification', 'voting',
    1.97 -        'canceled_revoked_before_accepted',
    1.98 -        'canceled_issue_not_accepted',
    1.99 -        'canceled_after_revocation_during_discussion',
   1.100 -        'canceled_after_revocation_during_verification',
   1.101 -        'calculation',
   1.102 -        'canceled_no_initiative_admitted',
   1.103 -        'finished_without_winner', 'finished_with_winner');
   1.104 -
   1.105 -COMMENT ON TYPE "issue_state" IS 'State of issues';
   1.106 -
   1.107 -ALTER TABLE "issue" ADD "state" "issue_state";
   1.108 -
   1.109 --- NOTE: Filling new column with values is done after this transaction (see below)
   1.110 -
   1.111 -
   1.112 --- New column "revoked_by_member_id" in table "initiative":
   1.113 -
   1.114 -ALTER TABLE "initiative" ADD "revoked_by_member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE;
   1.115 -
   1.116 -COMMENT ON COLUMN "initiative"."revoked_by_member_id" IS 'Member, who decided to revoked the initiative';
   1.117 -
   1.118 --- NOTE: Filling new column with values is done after this transaction (see below)
   1.119 -
   1.120 -
   1.121 --- New table "ignored_initiative":
   1.122 -
   1.123 -CREATE TABLE "ignored_initiative" (
   1.124 -        PRIMARY KEY ("initiative_id", "member_id"),
   1.125 -        "initiative_id"         INT4            REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
   1.126 -        "member_id"             INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
   1.127 -CREATE INDEX "ignored_initiative_member_id_idx" ON "ignored_initiative" ("member_id");
   1.128 -
   1.129 -COMMENT ON TABLE "ignored_initiative" IS 'Possibility to filter initiatives';
   1.130 -
   1.131 -
   1.132 --- New table "invite_code_unit":
   1.133 -
   1.134 -CREATE TABLE "invite_code_unit" (
   1.135 -        PRIMARY KEY ("invite_code_id", "unit_id"),
   1.136 -        "invite_code_id"        INT8            REFERENCES "invite_code" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
   1.137 -        "unit_id"               INT4            REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
   1.138 -
   1.139 -COMMENT ON TABLE "invite_code_unit" IS 'Units where accounts created with a given invite codes get voting rights';
   1.140 -
   1.141 --- TODO: Table is filled after this transaction (see below)
   1.142 -
   1.143 -
   1.144 --- New table "privilege":
   1.145 -
   1.146 -CREATE TABLE "privilege" (
   1.147 -        PRIMARY KEY ("unit_id", "member_id"),
   1.148 -        "unit_id"               INT4            REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
   1.149 -        "member_id"             INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
   1.150 -        "admin_manager"         BOOLEAN         NOT NULL DEFAULT FALSE,
   1.151 -        "unit_manager"          BOOLEAN         NOT NULL DEFAULT FALSE,
   1.152 -        "area_manager"          BOOLEAN         NOT NULL DEFAULT FALSE,
   1.153 -        "voting_right_manager"  BOOLEAN         NOT NULL DEFAULT FALSE,
   1.154 -        "voting_right"          BOOLEAN         NOT NULL DEFAULT TRUE );
   1.155 -
   1.156 -COMMENT ON TABLE "privilege" IS 'Members rights related to each unit';
   1.157 -
   1.158 -COMMENT ON COLUMN "privilege"."admin_manager"        IS 'Grant/revoke admin privileges to/from other users';
   1.159 -COMMENT ON COLUMN "privilege"."unit_manager"         IS 'Create or lock sub units';
   1.160 -COMMENT ON COLUMN "privilege"."area_manager"         IS 'Create or lock areas and set area parameters';
   1.161 -COMMENT ON COLUMN "privilege"."voting_right_manager" IS 'Select which members are allowed to discuss and vote inside the unit';
   1.162 -COMMENT ON COLUMN "privilege"."voting_right"         IS 'Right to discuss and vote';
   1.163 -
   1.164 -
   1.165 --- Remove table "ignored_issue", which is no longer existent:
   1.166 -
   1.167 -DROP TABLE "ignored_issue";
   1.168 -
   1.169 -
   1.170 --- Replace TYPE "delegation_scope" with a new type, where 'global' is replaced by 'unit':
   1.171 -
   1.172 -ALTER TYPE "delegation_scope" RENAME TO "delegation_scope_old";  -- NOTE: dropped later
   1.173 -CREATE TYPE "delegation_scope" AS ENUM ('unit', 'area', 'issue');
   1.174 -COMMENT ON TYPE "delegation_scope" IS 'Scope for delegations: ''unit'', ''area'', or ''issue'' (order is relevant)';
   1.175 -
   1.176 -
   1.177 --- Delete views and functions being dependent on TYPE "delegation_scope":
   1.178 -
   1.179 -DROP FUNCTION "delegation_chain"
   1.180 -  ( "member_id_p" "member"."id"%TYPE,
   1.181 -    "area_id_p"   "area"."id"%TYPE,
   1.182 -    "issue_id_p"  "issue"."id"%TYPE );
   1.183 -
   1.184 -DROP FUNCTION "delegation_chain"
   1.185 -  ( "member_id_p"           "member"."id"%TYPE,
   1.186 -    "area_id_p"             "area"."id"%TYPE,
   1.187 -    "issue_id_p"            "issue"."id"%TYPE,
   1.188 -    "simulate_trustee_id_p" "member"."id"%TYPE );
   1.189 -
   1.190 -DROP TYPE "delegation_chain_row";
   1.191 -
   1.192 -DROP VIEW "issue_delegation";
   1.193 -DROP VIEW "area_delegation";
   1.194 -DROP VIEW "global_delegation";
   1.195 -DROP VIEW "active_delegation";
   1.196 -
   1.197 -
   1.198 --- Modify "delegation" table to use new "delegation_scope" TYPE:
   1.199 -
   1.200 -ALTER TABLE "delegation" DROP CONSTRAINT "no_global_delegation_to_null";
   1.201 -ALTER TABLE "delegation" DROP CONSTRAINT "area_id_and_issue_id_set_according_to_scope";
   1.202 -
   1.203 -DROP INDEX "delegation_global_truster_id_unique_idx";
   1.204 -
   1.205 -ALTER TABLE "delegation" ALTER "scope" TYPE "delegation_scope"
   1.206 -  USING CASE WHEN "scope" = 'global'
   1.207 -  THEN 'unit'::"delegation_scope"
   1.208 -  ELSE "scope"::text::"delegation_scope" END;
   1.209 -
   1.210 -ALTER TABLE "delegation" ADD "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
   1.211 -
   1.212 -ALTER TABLE "delegation" ADD CONSTRAINT "no_unit_delegation_to_null"
   1.213 -  CHECK ("trustee_id" NOTNULL OR "scope" != 'unit');
   1.214 -
   1.215 -ALTER TABLE "delegation" ADD UNIQUE ("unit_id", "truster_id");
   1.216 -
   1.217 -COMMENT ON COLUMN "delegation"."unit_id"  IS 'Reference to unit, if delegation is unit-wide, otherwise NULL';
   1.218 -
   1.219 --- NOTE: Column "unit_id" filled after transaction (see below)
   1.220 -
   1.221 -
   1.222 --- Modify snapshot tables to use new "delegation_scope" TYPE:
   1.223 -
   1.224 -ALTER TABLE "delegating_population_snapshot" ALTER "scope" TYPE "delegation_scope"
   1.225 -  USING CASE WHEN "scope" = 'global'
   1.226 -  THEN 'unit'::"delegation_scope"
   1.227 -  ELSE "scope"::text::"delegation_scope" END;
   1.228 -
   1.229 -ALTER TABLE "delegating_interest_snapshot" ALTER "scope" TYPE "delegation_scope"
   1.230 -  USING CASE WHEN "scope" = 'global'
   1.231 -  THEN 'unit'::"delegation_scope"
   1.232 -  ELSE "scope"::text::"delegation_scope" END;
   1.233 -
   1.234 -ALTER TABLE "delegating_voter" ALTER "scope" TYPE "delegation_scope"
   1.235 -  USING CASE WHEN "scope" = 'global'
   1.236 -  THEN 'unit'::"delegation_scope"
   1.237 -  ELSE "scope"::text::"delegation_scope" END;
   1.238 -
   1.239 -
   1.240 --- New table "non_voter":
   1.241 -
   1.242 -CREATE TABLE "non_voter" (
   1.243 -        PRIMARY KEY ("issue_id", "member_id"),
   1.244 -        "issue_id"              INT4            REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
   1.245 -        "member_id"             INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
   1.246 -CREATE INDEX "non_voter_member_id_idx" ON "non_voter" ("member_id");
   1.247 -
   1.248 -COMMENT ON TABLE "non_voter" IS 'Members who decided to not vote directly on an issue';
   1.249 -
   1.250 -
   1.251 --- New table "issue_comment":
   1.252 -
   1.253 -CREATE TABLE "issue_comment" (
   1.254 -        PRIMARY KEY ("issue_id", "member_id"),
   1.255 -        "issue_id"              INT4            REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
   1.256 -        "member_id"             INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
   1.257 -        "changed"               TIMESTAMPTZ     NOT NULL DEFAULT now(),
   1.258 -        "formatting_engine"     TEXT,
   1.259 -        "content"               TEXT            NOT NULL,
   1.260 -        "text_search_data"      TSVECTOR );
   1.261 -CREATE INDEX "issue_comment_member_id_idx" ON "issue_comment" ("member_id");
   1.262 -CREATE INDEX "issue_comment_text_search_data_idx" ON "issue_comment" USING gin ("text_search_data");
   1.263 -CREATE TRIGGER "update_text_search_data"
   1.264 -  BEFORE INSERT OR UPDATE ON "issue_comment"
   1.265 -  FOR EACH ROW EXECUTE PROCEDURE
   1.266 -  tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "content");
   1.267 -
   1.268 -COMMENT ON TABLE "issue_comment" IS 'Place to store free comments of members related to issues';
   1.269 -
   1.270 -COMMENT ON COLUMN "issue_comment"."changed" IS 'Time the comment was last changed';
   1.271 -
   1.272 -
   1.273 --- New table "rendered_issue_comment":
   1.274 -
   1.275 -CREATE TABLE "rendered_issue_comment" (
   1.276 -        PRIMARY KEY ("issue_id", "member_id", "format"),
   1.277 -        FOREIGN KEY ("issue_id", "member_id")
   1.278 -          REFERENCES "issue_comment" ("issue_id", "member_id")
   1.279 -          ON DELETE CASCADE ON UPDATE CASCADE,
   1.280 -        "issue_id"              INT4,
   1.281 -        "member_id"             INT4,
   1.282 -        "format"                TEXT,
   1.283 -        "content"               TEXT            NOT NULL );
   1.284 -
   1.285 -COMMENT ON TABLE "rendered_issue_comment" IS 'This table may be used by frontends to cache "rendered" issue comments (e.g. HTML output generated from wiki text)';
   1.286 -
   1.287 -
   1.288 --- New table "voting_comment":
   1.289 -
   1.290 -CREATE TABLE "voting_comment" (
   1.291 -        PRIMARY KEY ("issue_id", "member_id"),
   1.292 -        "issue_id"              INT4            REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
   1.293 -        "member_id"             INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
   1.294 -        "changed"               TIMESTAMPTZ,
   1.295 -        "formatting_engine"     TEXT,
   1.296 -        "content"               TEXT            NOT NULL,
   1.297 -        "text_search_data"      TSVECTOR );
   1.298 -CREATE INDEX "voting_comment_member_id_idx" ON "voting_comment" ("member_id");
   1.299 -CREATE INDEX "voting_comment_text_search_data_idx" ON "voting_comment" USING gin ("text_search_data");
   1.300 -CREATE TRIGGER "update_text_search_data"
   1.301 -  BEFORE INSERT OR UPDATE ON "voting_comment"
   1.302 -  FOR EACH ROW EXECUTE PROCEDURE
   1.303 -  tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "content");
   1.304 -
   1.305 -COMMENT ON TABLE "voting_comment" IS 'Storage for comments of voters to be published after voting has finished.';
   1.306 -
   1.307 -COMMENT ON COLUMN "voting_comment"."changed" IS 'Is to be set or updated by the frontend, if comment was inserted or updated AFTER the issue has been closed. Otherwise it shall be set to NULL.';
   1.308 -
   1.309 -
   1.310 --- New table "rendered_voting_comment":
   1.311 -
   1.312 -CREATE TABLE "rendered_voting_comment" (
   1.313 -        PRIMARY KEY ("issue_id", "member_id", "format"),
   1.314 -        FOREIGN KEY ("issue_id", "member_id")
   1.315 -          REFERENCES "voting_comment" ("issue_id", "member_id")
   1.316 -          ON DELETE CASCADE ON UPDATE CASCADE,
   1.317 -        "issue_id"              INT4,
   1.318 -        "member_id"             INT4,
   1.319 -        "format"                TEXT,
   1.320 -        "content"               TEXT            NOT NULL );
   1.321 -
   1.322 -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)';
   1.323 -
   1.324 -
   1.325 --- New table "event":
   1.326 -
   1.327 -CREATE TYPE "event_type" AS ENUM (
   1.328 -        'issue_state_changed',
   1.329 -        'initiative_created_in_new_issue',
   1.330 -        'initiative_created_in_existing_issue',
   1.331 -        'initiative_revoked',
   1.332 -        'new_draft_created',
   1.333 -        'suggestion_created');
   1.334 -
   1.335 -COMMENT ON TYPE "event_type" IS 'Type used for column "event" of table "event"';
   1.336 -
   1.337 -CREATE TABLE "event" (
   1.338 -        "id"                    SERIAL8         PRIMARY KEY,
   1.339 -        "occurrence"            TIMESTAMPTZ     NOT NULL DEFAULT now(),
   1.340 -        "event"                 "event_type"    NOT NULL,
   1.341 -        "member_id"             INT4            REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
   1.342 -        "issue_id"              INT4            REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
   1.343 -        "state"                 "issue_state"   CHECK ("state" != 'calculation'),
   1.344 -        "initiative_id"         INT4,
   1.345 -        "draft_id"              INT8,
   1.346 -        "suggestion_id"         INT8,
   1.347 -        FOREIGN KEY ("issue_id", "initiative_id")
   1.348 -          REFERENCES "initiative" ("issue_id", "id")
   1.349 -          ON DELETE CASCADE ON UPDATE CASCADE,
   1.350 -        FOREIGN KEY ("initiative_id", "draft_id")
   1.351 -          REFERENCES "draft" ("initiative_id", "id")
   1.352 -          ON DELETE CASCADE ON UPDATE CASCADE,
   1.353 -        FOREIGN KEY ("initiative_id", "suggestion_id")
   1.354 -          REFERENCES "suggestion" ("initiative_id", "id")
   1.355 -          ON DELETE CASCADE ON UPDATE CASCADE,
   1.356 -        CONSTRAINT "null_constraints_for_issue_state_changed" CHECK (
   1.357 -          "event" != 'issue_state_changed' OR (
   1.358 -            "member_id"     ISNULL  AND
   1.359 -            "issue_id"      NOTNULL AND
   1.360 -            "state"         NOTNULL AND
   1.361 -            "initiative_id" ISNULL  AND
   1.362 -            "draft_id"      ISNULL  AND
   1.363 -            "suggestion_id" ISNULL  )),
   1.364 -        CONSTRAINT "null_constraints_for_initiative_creation_or_revocation_or_new_draft" CHECK (
   1.365 -          "event" NOT IN (
   1.366 -            'initiative_created_in_new_issue',
   1.367 -            'initiative_created_in_existing_issue',
   1.368 -            'initiative_revoked',
   1.369 -            'new_draft_created'
   1.370 -          ) OR (
   1.371 -            "member_id"     NOTNULL AND
   1.372 -            "issue_id"      NOTNULL AND
   1.373 -            "state"         NOTNULL AND
   1.374 -            "initiative_id" NOTNULL AND
   1.375 -            "draft_id"      NOTNULL AND
   1.376 -            "suggestion_id" ISNULL  )),
   1.377 -        CONSTRAINT "null_constraints_for_suggestion_creation" CHECK (
   1.378 -          "event" != 'suggestion_created' OR (
   1.379 -            "member_id"     NOTNULL AND
   1.380 -            "issue_id"      NOTNULL AND
   1.381 -            "state"         NOTNULL AND
   1.382 -            "initiative_id" NOTNULL AND
   1.383 -            "draft_id"      ISNULL  AND
   1.384 -            "suggestion_id" NOTNULL )) );
   1.385 -
   1.386 -COMMENT ON TABLE "event" IS 'Event table, automatically filled by triggers';
   1.387 -
   1.388 -COMMENT ON COLUMN "event"."occurrence" IS 'Point in time, when event occurred';
   1.389 -COMMENT ON COLUMN "event"."event"      IS 'Type of event (see TYPE "event_type")';
   1.390 -COMMENT ON COLUMN "event"."member_id"  IS 'Member who caused the event, if applicable';
   1.391 -COMMENT ON COLUMN "event"."state"      IS 'If issue_id is set: state of affected issue; If state changed: new state';
   1.392 -
   1.393 -
   1.394 --- Triggers to fill "event" table:
   1.395 -
   1.396 -CREATE FUNCTION "write_event_issue_state_changed_trigger"()
   1.397 -  RETURNS TRIGGER
   1.398 -  LANGUAGE 'plpgsql' VOLATILE AS $$
   1.399 -    BEGIN
   1.400 -      IF NEW."state" != OLD."state" AND NEW."state" != 'calculation' THEN
   1.401 -        INSERT INTO "event" ("event", "issue_id", "state")
   1.402 -          VALUES ('issue_state_changed', NEW."id", NEW."state");
   1.403 -      END IF;
   1.404 -      RETURN NULL;
   1.405 -    END;
   1.406 -  $$;
   1.407 -
   1.408 -CREATE TRIGGER "write_event_issue_state_changed"
   1.409 -  AFTER UPDATE ON "issue" FOR EACH ROW EXECUTE PROCEDURE
   1.410 -  "write_event_issue_state_changed_trigger"();
   1.411 -
   1.412 -COMMENT ON FUNCTION "write_event_issue_state_changed_trigger"() IS 'Implementation of trigger "write_event_issue_state_changed" on table "issue"';
   1.413 -COMMENT ON TRIGGER "write_event_issue_state_changed" ON "issue" IS 'Create entry in "event" table on "state" change';
   1.414 -
   1.415 -CREATE FUNCTION "write_event_initiative_or_draft_created_trigger"()
   1.416 -  RETURNS TRIGGER
   1.417 -  LANGUAGE 'plpgsql' VOLATILE AS $$
   1.418 -    DECLARE
   1.419 -      "initiative_row" "initiative"%ROWTYPE;
   1.420 -      "issue_row"      "issue"%ROWTYPE;
   1.421 -      "event_v"        "event_type";
   1.422 -    BEGIN
   1.423 -      SELECT * INTO "initiative_row" FROM "initiative"
   1.424 -        WHERE "id" = NEW."initiative_id";
   1.425 -      SELECT * INTO "issue_row" FROM "issue"
   1.426 -        WHERE "id" = "initiative_row"."issue_id";
   1.427 -      IF EXISTS (
   1.428 -        SELECT NULL FROM "draft"
   1.429 -        WHERE "initiative_id" = NEW."initiative_id"
   1.430 -        AND "id" != NEW."id"
   1.431 -      ) THEN
   1.432 -        "event_v" := 'new_draft_created';
   1.433 -      ELSE
   1.434 -        IF EXISTS (
   1.435 -          SELECT NULL FROM "initiative"
   1.436 -          WHERE "issue_id" = "initiative_row"."issue_id"
   1.437 -          AND "id" != "initiative_row"."id"
   1.438 -        ) THEN
   1.439 -          "event_v" := 'initiative_created_in_existing_issue';
   1.440 -        ELSE
   1.441 -          "event_v" := 'initiative_created_in_new_issue';
   1.442 -        END IF;
   1.443 -      END IF;
   1.444 -      INSERT INTO "event" (
   1.445 -          "event", "member_id",
   1.446 -          "issue_id", "state", "initiative_id", "draft_id"
   1.447 -        ) VALUES (
   1.448 -          "event_v",
   1.449 -          NEW."author_id",
   1.450 -          "initiative_row"."issue_id",
   1.451 -          "issue_row"."state",
   1.452 -          "initiative_row"."id",
   1.453 -          NEW."id" );
   1.454 -      RETURN NULL;
   1.455 -    END;
   1.456 -  $$;
   1.457 -
   1.458 -CREATE TRIGGER "write_event_initiative_or_draft_created"
   1.459 -  AFTER INSERT ON "draft" FOR EACH ROW EXECUTE PROCEDURE
   1.460 -  "write_event_initiative_or_draft_created_trigger"();
   1.461 -
   1.462 -COMMENT ON FUNCTION "write_event_initiative_or_draft_created_trigger"() IS 'Implementation of trigger "write_event_initiative_or_draft_created" on table "issue"';
   1.463 -COMMENT ON TRIGGER "write_event_initiative_or_draft_created" ON "draft" IS 'Create entry in "event" table on draft creation';
   1.464 -
   1.465 -CREATE FUNCTION "write_event_initiative_revoked_trigger"()
   1.466 -  RETURNS TRIGGER
   1.467 -  LANGUAGE 'plpgsql' VOLATILE AS $$
   1.468 -    DECLARE
   1.469 -      "issue_row"      "issue"%ROWTYPE;
   1.470 -    BEGIN
   1.471 -      SELECT * INTO "issue_row" FROM "issue"
   1.472 -        WHERE "id" = NEW."issue_id";
   1.473 -      IF OLD."revoked" ISNULL AND NEW."revoked" NOTNULL THEN
   1.474 -        INSERT INTO "event" (
   1.475 -            "event", "member_id", "issue_id", "state", "initiative_id"
   1.476 -          ) VALUES (
   1.477 -            'initiative_revoked',
   1.478 -            NEW."revoked_by_member_id",
   1.479 -            NEW."issue_id",
   1.480 -            "issue_row"."state",
   1.481 -            NEW."id" );
   1.482 -      END IF;
   1.483 -      RETURN NULL;
   1.484 -    END;
   1.485 -  $$;
   1.486 -
   1.487 -CREATE TRIGGER "write_event_initiative_revoked"
   1.488 -  AFTER UPDATE ON "initiative" FOR EACH ROW EXECUTE PROCEDURE
   1.489 -  "write_event_initiative_revoked_trigger"();
   1.490 -
   1.491 -COMMENT ON FUNCTION "write_event_initiative_revoked_trigger"()      IS 'Implementation of trigger "write_event_initiative_revoked" on table "issue"';
   1.492 -COMMENT ON TRIGGER "write_event_initiative_revoked" ON "initiative" IS 'Create entry in "event" table, when an initiative is revoked';
   1.493 -
   1.494 -CREATE FUNCTION "write_event_suggestion_created_trigger"()
   1.495 -  RETURNS TRIGGER
   1.496 -  LANGUAGE 'plpgsql' VOLATILE AS $$
   1.497 -    DECLARE
   1.498 -      "initiative_row" "initiative"%ROWTYPE;
   1.499 -      "issue_row"      "issue"%ROWTYPE;
   1.500 -    BEGIN
   1.501 -      SELECT * INTO "initiative_row" FROM "initiative"
   1.502 -        WHERE "id" = NEW."initiative_id";
   1.503 -      SELECT * INTO "issue_row" FROM "issue"
   1.504 -        WHERE "id" = "initiative_row"."issue_id";
   1.505 -      INSERT INTO "event" (
   1.506 -          "event", "member_id",
   1.507 -          "issue_id", "state", "initiative_id", "suggestion_id"
   1.508 -        ) VALUES (
   1.509 -          'suggestion_created',
   1.510 -          NEW."author_id",
   1.511 -          "initiative_row"."issue_id",
   1.512 -          "issue_row"."state",
   1.513 -          "initiative_row"."id",
   1.514 -          NEW."id" );
   1.515 -      RETURN NULL;
   1.516 -    END;
   1.517 -  $$;
   1.518 -
   1.519 -CREATE TRIGGER "write_event_suggestion_created"
   1.520 -  AFTER INSERT ON "suggestion" FOR EACH ROW EXECUTE PROCEDURE
   1.521 -  "write_event_suggestion_created_trigger"();
   1.522 -
   1.523 -COMMENT ON FUNCTION "write_event_suggestion_created_trigger"()      IS 'Implementation of trigger "write_event_suggestion_created" on table "issue"';
   1.524 -COMMENT ON TRIGGER "write_event_suggestion_created" ON "suggestion" IS 'Create entry in "event" table on suggestion creation';
   1.525 -
   1.526 -
   1.527 --- Modified views:
   1.528 -
   1.529 -CREATE VIEW "unit_delegation" AS
   1.530 -  SELECT
   1.531 -    "unit"."id" AS "unit_id",
   1.532 -    "delegation"."id",
   1.533 -    "delegation"."truster_id",
   1.534 -    "delegation"."trustee_id",
   1.535 -    "delegation"."scope"
   1.536 -  FROM "unit"
   1.537 -  JOIN "delegation"
   1.538 -    ON "delegation"."unit_id" = "unit"."id"
   1.539 -  JOIN "member"
   1.540 -    ON "delegation"."truster_id" = "member"."id"
   1.541 -  JOIN "privilege"
   1.542 -    ON "delegation"."unit_id" = "privilege"."unit_id"
   1.543 -    AND "delegation"."truster_id" = "privilege"."member_id"
   1.544 -  WHERE "member"."active" AND "privilege"."voting_right";
   1.545 -
   1.546 -COMMENT ON VIEW "unit_delegation" IS 'Unit delegations where trusters are active and have voting right';
   1.547 -
   1.548 -CREATE VIEW "area_delegation" AS
   1.549 -  SELECT DISTINCT ON ("area"."id", "delegation"."truster_id")
   1.550 -    "area"."id" AS "area_id",
   1.551 -    "delegation"."id",
   1.552 -    "delegation"."truster_id",
   1.553 -    "delegation"."trustee_id",
   1.554 -    "delegation"."scope"
   1.555 -  FROM "area"
   1.556 -  JOIN "delegation"
   1.557 -    ON "delegation"."unit_id" = "area"."unit_id"
   1.558 -    OR "delegation"."area_id" = "area"."id"
   1.559 -  JOIN "member"
   1.560 -    ON "delegation"."truster_id" = "member"."id"
   1.561 -  JOIN "privilege"
   1.562 -    ON "area"."unit_id" = "privilege"."unit_id"
   1.563 -    AND "delegation"."truster_id" = "privilege"."member_id"
   1.564 -  WHERE "member"."active" AND "privilege"."voting_right"
   1.565 -  ORDER BY
   1.566 -    "area"."id",
   1.567 -    "delegation"."truster_id",
   1.568 -    "delegation"."scope" DESC;
   1.569 -
   1.570 -COMMENT ON VIEW "area_delegation" IS 'Area delegations where trusters are active and have voting right';
   1.571 -
   1.572 -CREATE VIEW "issue_delegation" AS
   1.573 -  SELECT DISTINCT ON ("issue"."id", "delegation"."truster_id")
   1.574 -    "issue"."id" AS "issue_id",
   1.575 -    "delegation"."id",
   1.576 -    "delegation"."truster_id",
   1.577 -    "delegation"."trustee_id",
   1.578 -    "delegation"."scope"
   1.579 -  FROM "issue"
   1.580 -  JOIN "area"
   1.581 -    ON "area"."id" = "issue"."area_id"
   1.582 -  JOIN "delegation"
   1.583 -    ON "delegation"."unit_id" = "area"."unit_id"
   1.584 -    OR "delegation"."area_id" = "area"."id"
   1.585 -    OR "delegation"."issue_id" = "issue"."id"
   1.586 -  JOIN "member"
   1.587 -    ON "delegation"."truster_id" = "member"."id"
   1.588 -  JOIN "privilege"
   1.589 -    ON "area"."unit_id" = "privilege"."unit_id"
   1.590 -    AND "delegation"."truster_id" = "privilege"."member_id"
   1.591 -  WHERE "member"."active" AND "privilege"."voting_right"
   1.592 -  ORDER BY
   1.593 -    "issue"."id",
   1.594 -    "delegation"."truster_id",
   1.595 -    "delegation"."scope" DESC;
   1.596 -
   1.597 -COMMENT ON VIEW "issue_delegation" IS 'Issue delegations where trusters are active and have voting right';
   1.598 -
   1.599 -CREATE VIEW "unit_member_count" AS
   1.600 -  SELECT
   1.601 -    "unit"."id" AS "unit_id",
   1.602 -    sum("member"."id") AS "member_count"
   1.603 -  FROM "unit"
   1.604 -  LEFT JOIN "privilege"
   1.605 -  ON "privilege"."unit_id" = "unit"."id" 
   1.606 -  AND "privilege"."voting_right"
   1.607 -  LEFT JOIN "member"
   1.608 -  ON "member"."id" = "privilege"."member_id"
   1.609 -  AND "member"."active"
   1.610 -  GROUP BY "unit"."id";
   1.611 -
   1.612 -COMMENT ON VIEW "unit_member_count" IS 'View used to update "member_count" column of "unit" table';
   1.613 -
   1.614 -DROP VIEW "area_member_count";
   1.615 -CREATE VIEW "area_member_count" AS
   1.616 -  SELECT
   1.617 -    "area"."id" AS "area_id",
   1.618 -    count("member"."id") AS "direct_member_count",
   1.619 -    coalesce(
   1.620 -      sum(
   1.621 -        CASE WHEN "member"."id" NOTNULL THEN
   1.622 -          "membership_weight"("area"."id", "member"."id")
   1.623 -        ELSE 0 END
   1.624 -      )
   1.625 -    ) AS "member_weight",
   1.626 -    coalesce(
   1.627 -      sum(
   1.628 -        CASE WHEN "member"."id" NOTNULL AND "membership"."autoreject" THEN
   1.629 -          "membership_weight"("area"."id", "member"."id")
   1.630 -        ELSE 0 END
   1.631 -      )
   1.632 -    ) AS "autoreject_weight"
   1.633 -  FROM "area"
   1.634 -  LEFT JOIN "membership"
   1.635 -  ON "area"."id" = "membership"."area_id"
   1.636 -  LEFT JOIN "privilege"
   1.637 -  ON "privilege"."unit_id" = "area"."unit_id"
   1.638 -  AND "privilege"."member_id" = "membership"."member_id"
   1.639 -  AND "privilege"."voting_right"
   1.640 -  LEFT JOIN "member"
   1.641 -  ON "member"."id" = "privilege"."member_id"  -- NOTE: no membership here!
   1.642 -  AND "member"."active"
   1.643 -  GROUP BY "area"."id";
   1.644 -
   1.645 -COMMENT ON VIEW "area_member_count" IS 'View used to update "direct_member_count", "member_weight" and "autoreject_weight" columns of table "area"';
   1.646 -
   1.647 -
   1.648 --- New view "event_seen_by_member":
   1.649 -
   1.650 -CREATE VIEW "event_seen_by_member" AS
   1.651 -  SELECT
   1.652 -    "member"."id" AS "seen_by_member_id",
   1.653 -    CASE WHEN "event"."state" IN (
   1.654 -      'voting',
   1.655 -      'finished_without_winner',
   1.656 -      'finished_with_winner'
   1.657 -    ) THEN
   1.658 -      'voting'::"notify_level"
   1.659 -    ELSE
   1.660 -      CASE WHEN "event"."state" IN (
   1.661 -        'verification',
   1.662 -        'canceled_after_revocation_during_verification',
   1.663 -        'canceled_no_initiative_admitted'
   1.664 -      ) THEN
   1.665 -        'verification'::"notify_level"
   1.666 -      ELSE
   1.667 -        CASE WHEN "event"."state" IN (
   1.668 -          'discussion',
   1.669 -          'canceled_after_revocation_during_discussion'
   1.670 -        ) THEN
   1.671 -          'discussion'::"notify_level"
   1.672 -        ELSE
   1.673 -          'all'::"notify_level"
   1.674 -        END
   1.675 -      END
   1.676 -    END AS "notify_level",
   1.677 -    "event".*
   1.678 -  FROM "member" CROSS JOIN "event"
   1.679 -  LEFT JOIN "issue"
   1.680 -    ON "event"."issue_id" = "issue"."id"
   1.681 -  LEFT JOIN "membership"
   1.682 -    ON "member"."id" = "membership"."member_id"
   1.683 -    AND "issue"."area_id" = "membership"."area_id"
   1.684 -  LEFT JOIN "interest"
   1.685 -    ON "member"."id" = "interest"."member_id"
   1.686 -    AND "event"."issue_id" = "interest"."issue_id"
   1.687 -  LEFT JOIN "supporter"
   1.688 -    ON "member"."id" = "supporter"."member_id"
   1.689 -    AND "event"."initiative_id" = "supporter"."initiative_id"
   1.690 -  LEFT JOIN "ignored_member"
   1.691 -    ON "member"."id" = "ignored_member"."member_id"
   1.692 -    AND "event"."member_id" = "ignored_member"."other_member_id"
   1.693 -  LEFT JOIN "ignored_initiative"
   1.694 -    ON "member"."id" = "ignored_initiative"."member_id"
   1.695 -    AND "event"."initiative_id" = "ignored_initiative"."initiative_id"
   1.696 -  WHERE (
   1.697 -    "supporter"."member_id" NOTNULL OR
   1.698 -    "interest"."member_id" NOTNULL OR
   1.699 -    ( "membership"."member_id" NOTNULL AND
   1.700 -      "event"."event" IN (
   1.701 -        'issue_state_changed',
   1.702 -        'initiative_created_in_new_issue',
   1.703 -        'initiative_created_in_existing_issue',
   1.704 -        'initiative_revoked' ) ) )
   1.705 -  AND "ignored_member"."member_id" ISNULL
   1.706 -  AND "ignored_initiative"."member_id" ISNULL;
   1.707 -
   1.708 -COMMENT ON VIEW "event_seen_by_member" IS 'Events as seen by a member, depending on its memberships, interests and support';
   1.709 -
   1.710 -
   1.711 --- New view "pending_notification":
   1.712 -
   1.713 -CREATE VIEW "pending_notification" AS
   1.714 -  SELECT
   1.715 -    "member"."id" AS "seen_by_member_id",
   1.716 -    "event".*
   1.717 -  FROM "member" CROSS JOIN "event"
   1.718 -  LEFT JOIN "issue"
   1.719 -    ON "event"."issue_id" = "issue"."id"
   1.720 -  LEFT JOIN "membership"
   1.721 -    ON "member"."id" = "membership"."member_id"
   1.722 -    AND "issue"."area_id" = "membership"."area_id"
   1.723 -  LEFT JOIN "interest"
   1.724 -    ON "member"."id" = "interest"."member_id"
   1.725 -    AND "event"."issue_id" = "interest"."issue_id"
   1.726 -  LEFT JOIN "supporter"
   1.727 -    ON "member"."id" = "supporter"."member_id"
   1.728 -    AND "event"."initiative_id" = "supporter"."initiative_id"
   1.729 -  LEFT JOIN "ignored_member"
   1.730 -    ON "member"."id" = "ignored_member"."member_id"
   1.731 -    AND "event"."member_id" = "ignored_member"."other_member_id"
   1.732 -  LEFT JOIN "ignored_initiative"
   1.733 -    ON "member"."id" = "ignored_initiative"."member_id"
   1.734 -    AND "event"."initiative_id" = "ignored_initiative"."initiative_id"
   1.735 -  WHERE (
   1.736 -    "member"."notify_event_id" ISNULL OR
   1.737 -    ( "member"."notify_event_id" NOTNULL AND
   1.738 -      "member"."notify_event_id" < "event"."id" ) )
   1.739 -  AND (
   1.740 -    ( "member"."notify_level" >= 'all' ) OR
   1.741 -    ( "member"."notify_level" >= 'voting' AND
   1.742 -      "event"."state" IN (
   1.743 -        'voting',
   1.744 -        'finished_without_winner',
   1.745 -        'finished_with_winner' ) ) OR
   1.746 -    ( "member"."notify_level" >= 'verification' AND
   1.747 -      "event"."state" IN (
   1.748 -        'verification',
   1.749 -        'canceled_after_revocation_during_verification',
   1.750 -        'canceled_no_initiative_admitted' ) ) OR
   1.751 -    ( "member"."notify_level" >= 'discussion' AND
   1.752 -      "event"."state" IN (
   1.753 -        'discussion',
   1.754 -        'canceled_after_revocation_during_discussion' ) ) )
   1.755 -  AND (
   1.756 -    "supporter"."member_id" NOTNULL OR
   1.757 -    "interest"."member_id" NOTNULL OR
   1.758 -    ( "membership"."member_id" NOTNULL AND
   1.759 -      "event"."event" IN (
   1.760 -        'issue_state_changed',
   1.761 -        'initiative_created_in_new_issue',
   1.762 -        'initiative_created_in_existing_issue',
   1.763 -        'initiative_revoked' ) ) )
   1.764 -  AND "ignored_member"."member_id" ISNULL
   1.765 -  AND "ignored_initiative"."member_id" ISNULL;
   1.766 -
   1.767 -COMMENT ON VIEW "pending_notification" IS 'Events to be sent to "notify_email" address of member referred to by "seen_by_member_id"';
   1.768 -
   1.769 -
   1.770 -COMMENT ON TYPE "timeline_event" IS 'Types of event in timeline tables (DEPRECATED)';
   1.771 -COMMENT ON VIEW "timeline_issue" IS 'Helper view for "timeline" view (DEPRECATED)';
   1.772 -COMMENT ON VIEW "timeline_initiative" IS 'Helper view for "timeline" view (DEPRECATED)';
   1.773 -COMMENT ON VIEW "timeline_draft" IS 'Helper view for "timeline" view (DEPRECATED)';
   1.774 -COMMENT ON VIEW "timeline_suggestion" IS 'Helper view for "timeline" view (DEPRECATED)';
   1.775 -COMMENT ON VIEW "timeline" IS 'Aggregation of different events in the system (DEPRECATED)';
   1.776 -
   1.777 -
   1.778 --- Modified "delegation_chain" functions:
   1.779 -
   1.780 -CREATE TYPE "delegation_chain_row" AS (
   1.781 -        "index"                 INT4,
   1.782 -        "member_id"             INT4,
   1.783 -        "member_valid"          BOOLEAN,
   1.784 -        "participation"         BOOLEAN,
   1.785 -        "overridden"            BOOLEAN,
   1.786 -        "scope_in"              "delegation_scope",
   1.787 -        "scope_out"             "delegation_scope",
   1.788 -        "disabled_out"          BOOLEAN,
   1.789 -        "loop"                  "delegation_chain_loop_tag" );
   1.790 -
   1.791 -COMMENT ON TYPE "delegation_chain_row" IS 'Type of rows returned by "delegation_chain"(...) functions';
   1.792 -
   1.793 -COMMENT ON COLUMN "delegation_chain_row"."index"         IS 'Index starting with 0 and counting up';
   1.794 -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';
   1.795 -COMMENT ON COLUMN "delegation_chain_row"."overridden"    IS 'True, if an entry with lower index has "participation" set to true';
   1.796 -COMMENT ON COLUMN "delegation_chain_row"."scope_in"      IS 'Scope of used incoming delegation';
   1.797 -COMMENT ON COLUMN "delegation_chain_row"."scope_out"     IS 'Scope of used outgoing delegation';
   1.798 -COMMENT ON COLUMN "delegation_chain_row"."disabled_out"  IS 'Outgoing delegation is explicitly disabled by a delegation with trustee_id set to NULL';
   1.799 -COMMENT ON COLUMN "delegation_chain_row"."loop"          IS 'Not null, if member is part of a loop, see "delegation_chain_loop_tag" type';
   1.800 -
   1.801 -
   1.802 -CREATE FUNCTION "delegation_chain"
   1.803 -  ( "member_id_p"           "member"."id"%TYPE,
   1.804 -    "unit_id_p"             "unit"."id"%TYPE,
   1.805 -    "area_id_p"             "area"."id"%TYPE,
   1.806 -    "issue_id_p"            "issue"."id"%TYPE,
   1.807 -    "simulate_trustee_id_p" "member"."id"%TYPE )
   1.808 -  RETURNS SETOF "delegation_chain_row"
   1.809 -  LANGUAGE 'plpgsql' STABLE AS $$
   1.810 -    DECLARE
   1.811 -      "scope_v"            "delegation_scope";
   1.812 -      "unit_id_v"          "unit"."id"%TYPE;
   1.813 -      "area_id_v"          "area"."id"%TYPE;
   1.814 -      "visited_member_ids" INT4[];  -- "member"."id"%TYPE[]
   1.815 -      "loop_member_id_v"   "member"."id"%TYPE;
   1.816 -      "output_row"         "delegation_chain_row";
   1.817 -      "output_rows"        "delegation_chain_row"[];
   1.818 -      "delegation_row"     "delegation"%ROWTYPE;
   1.819 -      "row_count"          INT4;
   1.820 -      "i"                  INT4;
   1.821 -      "loop_v"             BOOLEAN;
   1.822 -    BEGIN
   1.823 -      IF
   1.824 -        "unit_id_p" NOTNULL AND
   1.825 -        "area_id_p" ISNULL AND
   1.826 -        "issue_id_p" ISNULL
   1.827 -      THEN
   1.828 -        "scope_v" := 'unit';
   1.829 -        "unit_id_v" := "unit_id_p";
   1.830 -      ELSIF
   1.831 -        "unit_id_p" ISNULL AND
   1.832 -        "area_id_p" NOTNULL AND
   1.833 -        "issue_id_p" ISNULL
   1.834 -      THEN
   1.835 -        "scope_v" := 'area';
   1.836 -        "area_id_v" := "area_id_p";
   1.837 -        SELECT "unit_id" INTO "unit_id_v"
   1.838 -          FROM "area" WHERE "id" = "area_id_v";
   1.839 -      ELSIF
   1.840 -        "unit_id_p" ISNULL AND
   1.841 -        "area_id_p" ISNULL AND
   1.842 -        "issue_id_p" NOTNULL
   1.843 -      THEN
   1.844 -        "scope_v" := 'issue';
   1.845 -        SELECT "area_id" INTO "area_id_v"
   1.846 -          FROM "issue" WHERE "id" = "issue_id_p";
   1.847 -        SELECT "unit_id" INTO "unit_id_v"
   1.848 -          FROM "area"  WHERE "id" = "area_id_v";
   1.849 -      ELSE
   1.850 -        RAISE EXCEPTION 'Exactly one of unit_id_p, area_id_p, or issue_id_p must be NOTNULL.';
   1.851 -      END IF;
   1.852 -      "visited_member_ids" := '{}';
   1.853 -      "loop_member_id_v"   := NULL;
   1.854 -      "output_rows"        := '{}';
   1.855 -      "output_row"."index"         := 0;
   1.856 -      "output_row"."member_id"     := "member_id_p";
   1.857 -      "output_row"."member_valid"  := TRUE;
   1.858 -      "output_row"."participation" := FALSE;
   1.859 -      "output_row"."overridden"    := FALSE;
   1.860 -      "output_row"."disabled_out"  := FALSE;
   1.861 -      "output_row"."scope_out"     := NULL;
   1.862 -      LOOP
   1.863 -        IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN
   1.864 -          "loop_member_id_v" := "output_row"."member_id";
   1.865 -        ELSE
   1.866 -          "visited_member_ids" :=
   1.867 -            "visited_member_ids" || "output_row"."member_id";
   1.868 -        END IF;
   1.869 -        IF "output_row"."participation" THEN
   1.870 -          "output_row"."overridden" := TRUE;
   1.871 -        END IF;
   1.872 -        "output_row"."scope_in" := "output_row"."scope_out";
   1.873 -        IF EXISTS (
   1.874 -          SELECT NULL FROM "member" JOIN "privilege"
   1.875 -          ON "privilege"."member_id" = "member"."id"
   1.876 -          AND "privilege"."unit_id" = "unit_id_v"
   1.877 -          WHERE "id" = "output_row"."member_id"
   1.878 -          AND "member"."active" AND "privilege"."voting_right"
   1.879 -        ) THEN
   1.880 -          IF "scope_v" = 'unit' THEN
   1.881 -            SELECT * INTO "delegation_row" FROM "delegation"
   1.882 -              WHERE "truster_id" = "output_row"."member_id"
   1.883 -              AND "unit_id" = "unit_id_v";
   1.884 -          ELSIF "scope_v" = 'area' THEN
   1.885 -            "output_row"."participation" := EXISTS (
   1.886 -              SELECT NULL FROM "membership"
   1.887 -              WHERE "area_id" = "area_id_p"
   1.888 -              AND "member_id" = "output_row"."member_id"
   1.889 -            );
   1.890 -            SELECT * INTO "delegation_row" FROM "delegation"
   1.891 -              WHERE "truster_id" = "output_row"."member_id"
   1.892 -              AND (
   1.893 -                "unit_id" = "unit_id_v" OR
   1.894 -                "area_id" = "area_id_v"
   1.895 -              )
   1.896 -              ORDER BY "scope" DESC;
   1.897 -          ELSIF "scope_v" = 'issue' THEN
   1.898 -            "output_row"."participation" := EXISTS (
   1.899 -              SELECT NULL FROM "interest"
   1.900 -              WHERE "issue_id" = "issue_id_p"
   1.901 -              AND "member_id" = "output_row"."member_id"
   1.902 -            );
   1.903 -            SELECT * INTO "delegation_row" FROM "delegation"
   1.904 -              WHERE "truster_id" = "output_row"."member_id"
   1.905 -              AND (
   1.906 -                "unit_id" = "unit_id_v" OR
   1.907 -                "area_id" = "area_id_v" OR
   1.908 -                "issue_id" = "issue_id_p"
   1.909 -              )
   1.910 -              ORDER BY "scope" DESC;
   1.911 -          END IF;
   1.912 -        ELSE
   1.913 -          "output_row"."member_valid"  := FALSE;
   1.914 -          "output_row"."participation" := FALSE;
   1.915 -          "output_row"."scope_out"     := NULL;
   1.916 -          "delegation_row" := ROW(NULL);
   1.917 -        END IF;
   1.918 -        IF
   1.919 -          "output_row"."member_id" = "member_id_p" AND
   1.920 -          "simulate_trustee_id_p" NOTNULL
   1.921 -        THEN
   1.922 -          "output_row"."scope_out" := "scope_v";
   1.923 -          "output_rows" := "output_rows" || "output_row";
   1.924 -          "output_row"."member_id" := "simulate_trustee_id_p";
   1.925 -        ELSIF "delegation_row"."trustee_id" NOTNULL THEN
   1.926 -          "output_row"."scope_out" := "delegation_row"."scope";
   1.927 -          "output_rows" := "output_rows" || "output_row";
   1.928 -          "output_row"."member_id" := "delegation_row"."trustee_id";
   1.929 -        ELSIF "delegation_row"."scope" NOTNULL THEN
   1.930 -          "output_row"."scope_out" := "delegation_row"."scope";
   1.931 -          "output_row"."disabled_out" := TRUE;
   1.932 -          "output_rows" := "output_rows" || "output_row";
   1.933 -          EXIT;
   1.934 -        ELSE
   1.935 -          "output_row"."scope_out" := NULL;
   1.936 -          "output_rows" := "output_rows" || "output_row";
   1.937 -          EXIT;
   1.938 -        END IF;
   1.939 -        EXIT WHEN "loop_member_id_v" NOTNULL;
   1.940 -        "output_row"."index" := "output_row"."index" + 1;
   1.941 -      END LOOP;
   1.942 -      "row_count" := array_upper("output_rows", 1);
   1.943 -      "i"      := 1;
   1.944 -      "loop_v" := FALSE;
   1.945 -      LOOP
   1.946 -        "output_row" := "output_rows"["i"];
   1.947 -        EXIT WHEN "output_row" ISNULL;  -- NOTE: ISNULL and NOT ... NOTNULL produce different results!
   1.948 -        IF "loop_v" THEN
   1.949 -          IF "i" + 1 = "row_count" THEN
   1.950 -            "output_row"."loop" := 'last';
   1.951 -          ELSIF "i" = "row_count" THEN
   1.952 -            "output_row"."loop" := 'repetition';
   1.953 -          ELSE
   1.954 -            "output_row"."loop" := 'intermediate';
   1.955 -          END IF;
   1.956 -        ELSIF "output_row"."member_id" = "loop_member_id_v" THEN
   1.957 -          "output_row"."loop" := 'first';
   1.958 -          "loop_v" := TRUE;
   1.959 -        END IF;
   1.960 -        IF "scope_v" = 'unit' THEN
   1.961 -          "output_row"."participation" := NULL;
   1.962 -        END IF;
   1.963 -        RETURN NEXT "output_row";
   1.964 -        "i" := "i" + 1;
   1.965 -      END LOOP;
   1.966 -      RETURN;
   1.967 -    END;
   1.968 -  $$;
   1.969 -
   1.970 -COMMENT ON FUNCTION "delegation_chain"
   1.971 -  ( "member"."id"%TYPE,
   1.972 -    "unit"."id"%TYPE,
   1.973 -    "area"."id"%TYPE,
   1.974 -    "issue"."id"%TYPE,
   1.975 -    "member"."id"%TYPE )
   1.976 -  IS 'Helper function for frontends to display delegation chains; Not part of internal voting logic';
   1.977 -
   1.978 -
   1.979 -CREATE FUNCTION "delegation_chain"
   1.980 -  ( "member_id_p" "member"."id"%TYPE,
   1.981 -    "unit_id_p"   "unit"."id"%TYPE,
   1.982 -    "area_id_p"   "area"."id"%TYPE,
   1.983 -    "issue_id_p"  "issue"."id"%TYPE )
   1.984 -  RETURNS SETOF "delegation_chain_row"
   1.985 -  LANGUAGE 'plpgsql' STABLE AS $$
   1.986 -    DECLARE
   1.987 -      "result_row" "delegation_chain_row";
   1.988 -    BEGIN
   1.989 -      FOR "result_row" IN
   1.990 -        SELECT * FROM "delegation_chain"(
   1.991 -          "member_id_p", "area_id_p", "issue_id_p", NULL
   1.992 -        )
   1.993 -      LOOP
   1.994 -        RETURN NEXT "result_row";
   1.995 -      END LOOP;
   1.996 -      RETURN;
   1.997 -    END;
   1.998 -  $$;
   1.999 -
  1.1000 -COMMENT ON FUNCTION "delegation_chain"
  1.1001 -  ( "member"."id"%TYPE,
  1.1002 -    "unit"."id"%TYPE,
  1.1003 -    "area"."id"%TYPE,
  1.1004 -    "issue"."id"%TYPE )
  1.1005 -  IS 'Shortcut for "delegation_chain"(...) function where 4th parameter is null';
  1.1006 -
  1.1007 -
  1.1008 --- Modified core functions:
  1.1009 -
  1.1010 -CREATE OR REPLACE FUNCTION "lock_issue"
  1.1011 -  ( "issue_id_p" "issue"."id"%TYPE )
  1.1012 -  RETURNS VOID
  1.1013 -  LANGUAGE 'plpgsql' VOLATILE AS $$
  1.1014 -    BEGIN
  1.1015 -      LOCK TABLE "member"     IN SHARE MODE;
  1.1016 -      LOCK TABLE "privilege"  IN SHARE MODE;
  1.1017 -      LOCK TABLE "membership" IN SHARE MODE;
  1.1018 -      LOCK TABLE "policy"     IN SHARE MODE;
  1.1019 -      PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE;
  1.1020 -      -- NOTE: The row-level exclusive lock in combination with the
  1.1021 -      -- share_row_lock_issue(_via_initiative)_trigger functions (which
  1.1022 -      -- acquire a row-level share lock on the issue) ensure that no data
  1.1023 -      -- is changed, which could affect calculation of snapshots or
  1.1024 -      -- counting of votes. Table "delegation" must be table-level-locked,
  1.1025 -      -- as it also contains issue- and global-scope delegations.
  1.1026 -      LOCK TABLE "delegation" IN SHARE MODE;
  1.1027 -      LOCK TABLE "direct_population_snapshot"     IN EXCLUSIVE MODE;
  1.1028 -      LOCK TABLE "delegating_population_snapshot" IN EXCLUSIVE MODE;
  1.1029 -      LOCK TABLE "direct_interest_snapshot"       IN EXCLUSIVE MODE;
  1.1030 -      LOCK TABLE "delegating_interest_snapshot"   IN EXCLUSIVE MODE;
  1.1031 -      LOCK TABLE "direct_supporter_snapshot"      IN EXCLUSIVE MODE;
  1.1032 -      RETURN;
  1.1033 -    END;
  1.1034 -  $$;
  1.1035 -
  1.1036 -CREATE OR REPLACE FUNCTION "calculate_member_counts"()
  1.1037 -  RETURNS VOID
  1.1038 -  LANGUAGE 'plpgsql' VOLATILE AS $$
  1.1039 -    BEGIN
  1.1040 -      LOCK TABLE "member"       IN SHARE MODE;
  1.1041 -      LOCK TABLE "member_count" IN EXCLUSIVE MODE;
  1.1042 -      LOCK TABLE "unit"         IN EXCLUSIVE MODE;
  1.1043 -      LOCK TABLE "area"         IN EXCLUSIVE MODE;
  1.1044 -      LOCK TABLE "privilege"    IN SHARE MODE;
  1.1045 -      LOCK TABLE "membership"   IN SHARE MODE;
  1.1046 -      DELETE FROM "member_count";
  1.1047 -      INSERT INTO "member_count" ("total_count")
  1.1048 -        SELECT "total_count" FROM "member_count_view";
  1.1049 -      UPDATE "unit" SET "member_count" = "view"."member_count"
  1.1050 -        FROM "unit_member_count" AS "view"
  1.1051 -        WHERE "view"."unit_id" = "unit"."id";
  1.1052 -      UPDATE "area" SET
  1.1053 -        "direct_member_count" = "view"."direct_member_count",
  1.1054 -        "member_weight"       = "view"."member_weight",
  1.1055 -        "autoreject_weight"   = "view"."autoreject_weight"
  1.1056 -        FROM "area_member_count" AS "view"
  1.1057 -        WHERE "view"."area_id" = "area"."id";
  1.1058 -      RETURN;
  1.1059 -    END;
  1.1060 -  $$;
  1.1061 -
  1.1062 -CREATE OR REPLACE FUNCTION "create_population_snapshot"
  1.1063 -  ( "issue_id_p" "issue"."id"%TYPE )
  1.1064 -  RETURNS VOID
  1.1065 -  LANGUAGE 'plpgsql' VOLATILE AS $$
  1.1066 -    DECLARE
  1.1067 -      "member_id_v" "member"."id"%TYPE;
  1.1068 -    BEGIN
  1.1069 -      DELETE FROM "direct_population_snapshot"
  1.1070 -        WHERE "issue_id" = "issue_id_p"
  1.1071 -        AND "event" = 'periodic';
  1.1072 -      DELETE FROM "delegating_population_snapshot"
  1.1073 -        WHERE "issue_id" = "issue_id_p"
  1.1074 -        AND "event" = 'periodic';
  1.1075 -      INSERT INTO "direct_population_snapshot"
  1.1076 -        ("issue_id", "event", "member_id")
  1.1077 -        SELECT
  1.1078 -          "issue_id_p"                 AS "issue_id",
  1.1079 -          'periodic'::"snapshot_event" AS "event",
  1.1080 -          "member"."id"                AS "member_id"
  1.1081 -        FROM "issue"
  1.1082 -        JOIN "area" ON "issue"."area_id" = "area"."id"
  1.1083 -        JOIN "membership" ON "area"."id" = "membership"."area_id"
  1.1084 -        JOIN "member" ON "membership"."member_id" = "member"."id"
  1.1085 -        JOIN "privilege"
  1.1086 -          ON "privilege"."unit_id" = "area"."unit_id"
  1.1087 -          AND "privilege"."member_id" = "member"."id"
  1.1088 -        WHERE "issue"."id" = "issue_id_p"
  1.1089 -        AND "member"."active" AND "privilege"."voting_right"
  1.1090 -        UNION
  1.1091 -        SELECT
  1.1092 -          "issue_id_p"                 AS "issue_id",
  1.1093 -          'periodic'::"snapshot_event" AS "event",
  1.1094 -          "member"."id"                AS "member_id"
  1.1095 -        FROM "issue"
  1.1096 -        JOIN "area" ON "issue"."area_id" = "area"."id"
  1.1097 -        JOIN "interest" ON "issue"."id" = "interest"."issue_id"
  1.1098 -        JOIN "member" ON "interest"."member_id" = "member"."id"
  1.1099 -        JOIN "privilege"
  1.1100 -          ON "privilege"."unit_id" = "area"."unit_id"
  1.1101 -          AND "privilege"."member_id" = "member"."id"
  1.1102 -        WHERE "issue"."id" = "issue_id_p"
  1.1103 -        AND "member"."active" AND "privilege"."voting_right";
  1.1104 -      FOR "member_id_v" IN
  1.1105 -        SELECT "member_id" FROM "direct_population_snapshot"
  1.1106 -        WHERE "issue_id" = "issue_id_p"
  1.1107 -        AND "event" = 'periodic'
  1.1108 -      LOOP
  1.1109 -        UPDATE "direct_population_snapshot" SET
  1.1110 -          "weight" = 1 +
  1.1111 -            "weight_of_added_delegations_for_population_snapshot"(
  1.1112 -              "issue_id_p",
  1.1113 -              "member_id_v",
  1.1114 -              '{}'
  1.1115 -            )
  1.1116 -          WHERE "issue_id" = "issue_id_p"
  1.1117 -          AND "event" = 'periodic'
  1.1118 -          AND "member_id" = "member_id_v";
  1.1119 -      END LOOP;
  1.1120 -      RETURN;
  1.1121 -    END;
  1.1122 -  $$;
  1.1123 -
  1.1124 -CREATE OR REPLACE FUNCTION "create_interest_snapshot"
  1.1125 -  ( "issue_id_p" "issue"."id"%TYPE )
  1.1126 -  RETURNS VOID
  1.1127 -  LANGUAGE 'plpgsql' VOLATILE AS $$
  1.1128 -    DECLARE
  1.1129 -      "member_id_v" "member"."id"%TYPE;
  1.1130 -    BEGIN
  1.1131 -      DELETE FROM "direct_interest_snapshot"
  1.1132 -        WHERE "issue_id" = "issue_id_p"
  1.1133 -        AND "event" = 'periodic';
  1.1134 -      DELETE FROM "delegating_interest_snapshot"
  1.1135 -        WHERE "issue_id" = "issue_id_p"
  1.1136 -        AND "event" = 'periodic';
  1.1137 -      DELETE FROM "direct_supporter_snapshot"
  1.1138 -        WHERE "issue_id" = "issue_id_p"
  1.1139 -        AND "event" = 'periodic';
  1.1140 -      INSERT INTO "direct_interest_snapshot"
  1.1141 -        ("issue_id", "event", "member_id", "voting_requested")
  1.1142 -        SELECT
  1.1143 -          "issue_id_p"  AS "issue_id",
  1.1144 -          'periodic'    AS "event",
  1.1145 -          "member"."id" AS "member_id",
  1.1146 -          "interest"."voting_requested"
  1.1147 -        FROM "issue"
  1.1148 -        JOIN "area" ON "issue"."area_id" = "area"."id"
  1.1149 -        JOIN "interest" ON "issue"."id" = "interest"."issue_id"
  1.1150 -        JOIN "member" ON "interest"."member_id" = "member"."id"
  1.1151 -        JOIN "privilege"
  1.1152 -          ON "privilege"."unit_id" = "area"."unit_id"
  1.1153 -          AND "privilege"."member_id" = "member"."id"
  1.1154 -        WHERE "issue"."id" = "issue_id_p"
  1.1155 -        AND "member"."active" AND "privilege"."voting_right";
  1.1156 -      FOR "member_id_v" IN
  1.1157 -        SELECT "member_id" FROM "direct_interest_snapshot"
  1.1158 -        WHERE "issue_id" = "issue_id_p"
  1.1159 -        AND "event" = 'periodic'
  1.1160 -      LOOP
  1.1161 -        UPDATE "direct_interest_snapshot" SET
  1.1162 -          "weight" = 1 +
  1.1163 -            "weight_of_added_delegations_for_interest_snapshot"(
  1.1164 -              "issue_id_p",
  1.1165 -              "member_id_v",
  1.1166 -              '{}'
  1.1167 -            )
  1.1168 -          WHERE "issue_id" = "issue_id_p"
  1.1169 -          AND "event" = 'periodic'
  1.1170 -          AND "member_id" = "member_id_v";
  1.1171 -      END LOOP;
  1.1172 -      INSERT INTO "direct_supporter_snapshot"
  1.1173 -        ( "issue_id", "initiative_id", "event", "member_id",
  1.1174 -          "informed", "satisfied" )
  1.1175 -        SELECT
  1.1176 -          "issue_id_p"            AS "issue_id",
  1.1177 -          "initiative"."id"       AS "initiative_id",
  1.1178 -          'periodic'              AS "event",
  1.1179 -          "supporter"."member_id" AS "member_id",
  1.1180 -          "supporter"."draft_id" = "current_draft"."id" AS "informed",
  1.1181 -          NOT EXISTS (
  1.1182 -            SELECT NULL FROM "critical_opinion"
  1.1183 -            WHERE "initiative_id" = "initiative"."id"
  1.1184 -            AND "member_id" = "supporter"."member_id"
  1.1185 -          ) AS "satisfied"
  1.1186 -        FROM "initiative"
  1.1187 -        JOIN "supporter"
  1.1188 -        ON "supporter"."initiative_id" = "initiative"."id"
  1.1189 -        JOIN "current_draft"
  1.1190 -        ON "initiative"."id" = "current_draft"."initiative_id"
  1.1191 -        JOIN "direct_interest_snapshot"
  1.1192 -        ON "supporter"."member_id" = "direct_interest_snapshot"."member_id"
  1.1193 -        AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
  1.1194 -        AND "event" = 'periodic'
  1.1195 -        WHERE "initiative"."issue_id" = "issue_id_p";
  1.1196 -      RETURN;
  1.1197 -    END;
  1.1198 -  $$;
  1.1199 -
  1.1200 -CREATE OR REPLACE FUNCTION "freeze_after_snapshot"
  1.1201 -  ( "issue_id_p" "issue"."id"%TYPE )
  1.1202 -  RETURNS VOID
  1.1203 -  LANGUAGE 'plpgsql' VOLATILE AS $$
  1.1204 -    DECLARE
  1.1205 -      "issue_row"      "issue"%ROWTYPE;
  1.1206 -      "policy_row"     "policy"%ROWTYPE;
  1.1207 -      "initiative_row" "initiative"%ROWTYPE;
  1.1208 -    BEGIN
  1.1209 -      SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
  1.1210 -      SELECT * INTO "policy_row"
  1.1211 -        FROM "policy" WHERE "id" = "issue_row"."policy_id";
  1.1212 -      PERFORM "set_snapshot_event"("issue_id_p", 'full_freeze');
  1.1213 -      FOR "initiative_row" IN
  1.1214 -        SELECT * FROM "initiative"
  1.1215 -        WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
  1.1216 -      LOOP
  1.1217 -        IF
  1.1218 -          "initiative_row"."satisfied_supporter_count" > 0 AND
  1.1219 -          "initiative_row"."satisfied_supporter_count" *
  1.1220 -          "policy_row"."initiative_quorum_den" >=
  1.1221 -          "issue_row"."population" * "policy_row"."initiative_quorum_num"
  1.1222 -        THEN
  1.1223 -          UPDATE "initiative" SET "admitted" = TRUE
  1.1224 -            WHERE "id" = "initiative_row"."id";
  1.1225 -        ELSE
  1.1226 -          UPDATE "initiative" SET "admitted" = FALSE
  1.1227 -            WHERE "id" = "initiative_row"."id";
  1.1228 -        END IF;
  1.1229 -      END LOOP;
  1.1230 -      IF EXISTS (
  1.1231 -        SELECT NULL FROM "initiative"
  1.1232 -        WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE
  1.1233 -      ) THEN
  1.1234 -        UPDATE "issue" SET
  1.1235 -          "state"        = 'voting',
  1.1236 -          "accepted"     = coalesce("accepted", now()),
  1.1237 -          "half_frozen"  = coalesce("half_frozen", now()),
  1.1238 -          "fully_frozen" = now()
  1.1239 -          WHERE "id" = "issue_id_p";
  1.1240 -      ELSE
  1.1241 -        UPDATE "issue" SET
  1.1242 -          "state"        = 'canceled_no_initiative_admitted',
  1.1243 -          "accepted"     = coalesce("accepted", now()),
  1.1244 -          "half_frozen"  = coalesce("half_frozen", now()),
  1.1245 -          "fully_frozen" = now(),
  1.1246 -          "closed"       = now()
  1.1247 -          WHERE "id" = "issue_id_p";
  1.1248 -        -- NOTE: The following DELETE statements have effect only when
  1.1249 -        --       issue state has been manipulated
  1.1250 -        DELETE FROM "direct_voter"     WHERE "issue_id" = "issue_id_p";
  1.1251 -        DELETE FROM "delegating_voter" WHERE "issue_id" = "issue_id_p";
  1.1252 -        DELETE FROM "battle"           WHERE "issue_id" = "issue_id_p";
  1.1253 -      END IF;
  1.1254 -      RETURN;
  1.1255 -    END;
  1.1256 -  $$;
  1.1257 -
  1.1258 -CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
  1.1259 -  RETURNS VOID
  1.1260 -  LANGUAGE 'plpgsql' VOLATILE AS $$
  1.1261 -    DECLARE
  1.1262 -      "area_id_v"   "area"."id"%TYPE;
  1.1263 -      "unit_id_v"   "unit"."id"%TYPE;
  1.1264 -      "member_id_v" "member"."id"%TYPE;
  1.1265 -    BEGIN
  1.1266 -      PERFORM "lock_issue"("issue_id_p");
  1.1267 -      SELECT "id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
  1.1268 -      SELECT "id" INTO "unit_id_v" FROM "area"  WHERE "id" = "area_id_v";
  1.1269 -      DELETE FROM "delegating_voter"
  1.1270 -        WHERE "issue_id" = "issue_id_p";
  1.1271 -      DELETE FROM "direct_voter"
  1.1272 -        WHERE "issue_id" = "issue_id_p"
  1.1273 -        AND "autoreject" = TRUE;
  1.1274 -      DELETE FROM "direct_voter"
  1.1275 -        USING (
  1.1276 -          SELECT
  1.1277 -            "direct_voter"."member_id"
  1.1278 -          FROM "direct_voter"
  1.1279 -          JOIN "member" ON "direct_voter"."member_id" = "member"."id"
  1.1280 -          LEFT JOIN "privilege"
  1.1281 -          ON "privilege"."unit_id" = "unit_id_v"
  1.1282 -          AND "privilege"."member_id" = "direct_voter"."member_id"
  1.1283 -          WHERE "direct_voter"."issue_id" = "issue_id_p" AND (
  1.1284 -            "member"."active" = FALSE OR
  1.1285 -            "privilege"."voting_right" ISNULL OR
  1.1286 -            "privilege"."voting_right" = FALSE
  1.1287 -          )
  1.1288 -        ) AS "subquery"
  1.1289 -        WHERE "direct_voter"."issue_id" = "issue_id_p"
  1.1290 -        AND "direct_voter"."member_id" = "subquery"."member_id";
  1.1291 -      UPDATE "direct_voter" SET "weight" = 1
  1.1292 -        WHERE "issue_id" = "issue_id_p";
  1.1293 -      PERFORM "add_vote_delegations"("issue_id_p");
  1.1294 -      FOR "member_id_v" IN
  1.1295 -        SELECT "interest"."member_id"
  1.1296 -          FROM "interest"
  1.1297 -          JOIN "member"
  1.1298 -            ON "interest"."member_id" = "member"."id"
  1.1299 -          LEFT JOIN "direct_voter"
  1.1300 -            ON "interest"."member_id" = "direct_voter"."member_id"
  1.1301 -            AND "interest"."issue_id" = "direct_voter"."issue_id"
  1.1302 -          LEFT JOIN "delegating_voter"
  1.1303 -            ON "interest"."member_id" = "delegating_voter"."member_id"
  1.1304 -            AND "interest"."issue_id" = "delegating_voter"."issue_id"
  1.1305 -          WHERE "interest"."issue_id" = "issue_id_p"
  1.1306 -          AND "interest"."autoreject" = TRUE
  1.1307 -          AND "member"."active"
  1.1308 -          AND "direct_voter"."member_id" ISNULL
  1.1309 -          AND "delegating_voter"."member_id" ISNULL
  1.1310 -        UNION SELECT "membership"."member_id"
  1.1311 -          FROM "membership"
  1.1312 -          JOIN "member"
  1.1313 -            ON "membership"."member_id" = "member"."id"
  1.1314 -          LEFT JOIN "interest"
  1.1315 -            ON "membership"."member_id" = "interest"."member_id"
  1.1316 -            AND "interest"."issue_id" = "issue_id_p"
  1.1317 -          LEFT JOIN "direct_voter"
  1.1318 -            ON "membership"."member_id" = "direct_voter"."member_id"
  1.1319 -            AND "direct_voter"."issue_id" = "issue_id_p"
  1.1320 -          LEFT JOIN "delegating_voter"
  1.1321 -            ON "membership"."member_id" = "delegating_voter"."member_id"
  1.1322 -            AND "delegating_voter"."issue_id" = "issue_id_p"
  1.1323 -          WHERE "membership"."area_id" = "area_id_v"
  1.1324 -          AND "membership"."autoreject" = TRUE
  1.1325 -          AND "member"."active"
  1.1326 -          AND "interest"."autoreject" ISNULL
  1.1327 -          AND "direct_voter"."member_id" ISNULL
  1.1328 -          AND "delegating_voter"."member_id" ISNULL
  1.1329 -      LOOP
  1.1330 -        INSERT INTO "direct_voter"
  1.1331 -          ("member_id", "issue_id", "weight", "autoreject") VALUES
  1.1332 -          ("member_id_v", "issue_id_p", 1, TRUE);
  1.1333 -        INSERT INTO "vote" (
  1.1334 -          "member_id",
  1.1335 -          "issue_id",
  1.1336 -          "initiative_id",
  1.1337 -          "grade"
  1.1338 -          ) SELECT
  1.1339 -            "member_id_v" AS "member_id",
  1.1340 -            "issue_id_p"  AS "issue_id",
  1.1341 -            "id"          AS "initiative_id",
  1.1342 -            -1            AS "grade"
  1.1343 -          FROM "initiative" WHERE "issue_id" = "issue_id_p";
  1.1344 -      END LOOP;
  1.1345 -      PERFORM "add_vote_delegations"("issue_id_p");
  1.1346 -      UPDATE "issue" SET
  1.1347 -        "state"  = 'calculation',
  1.1348 -        "closed" = now(),
  1.1349 -        "voter_count" = (
  1.1350 -          SELECT coalesce(sum("weight"), 0)
  1.1351 -          FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
  1.1352 -        )
  1.1353 -        WHERE "id" = "issue_id_p";
  1.1354 -      UPDATE "initiative" SET
  1.1355 -        "positive_votes" = "vote_counts"."positive_votes",
  1.1356 -        "negative_votes" = "vote_counts"."negative_votes",
  1.1357 -        "agreed" = CASE WHEN "majority_strict" THEN
  1.1358 -          "vote_counts"."positive_votes" * "majority_den" >
  1.1359 -          "majority_num" *
  1.1360 -          ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
  1.1361 -        ELSE
  1.1362 -          "vote_counts"."positive_votes" * "majority_den" >=
  1.1363 -          "majority_num" *
  1.1364 -          ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
  1.1365 -        END
  1.1366 -        FROM
  1.1367 -          ( SELECT
  1.1368 -              "initiative"."id" AS "initiative_id",
  1.1369 -              coalesce(
  1.1370 -                sum(
  1.1371 -                  CASE WHEN "grade" > 0 THEN "direct_voter"."weight" ELSE 0 END
  1.1372 -                ),
  1.1373 -                0
  1.1374 -              ) AS "positive_votes",
  1.1375 -              coalesce(
  1.1376 -                sum(
  1.1377 -                  CASE WHEN "grade" < 0 THEN "direct_voter"."weight" ELSE 0 END
  1.1378 -                ),
  1.1379 -                0
  1.1380 -              ) AS "negative_votes"
  1.1381 -            FROM "initiative"
  1.1382 -            JOIN "issue" ON "initiative"."issue_id" = "issue"."id"
  1.1383 -            JOIN "policy" ON "issue"."policy_id" = "policy"."id"
  1.1384 -            LEFT JOIN "direct_voter"
  1.1385 -              ON "direct_voter"."issue_id" = "initiative"."issue_id"
  1.1386 -            LEFT JOIN "vote"
  1.1387 -              ON "vote"."initiative_id" = "initiative"."id"
  1.1388 -              AND "vote"."member_id" = "direct_voter"."member_id"
  1.1389 -            WHERE "initiative"."issue_id" = "issue_id_p"
  1.1390 -            AND "initiative"."admitted"  -- NOTE: NULL case is handled too
  1.1391 -            GROUP BY "initiative"."id"
  1.1392 -          ) AS "vote_counts",
  1.1393 -          "issue",
  1.1394 -          "policy"
  1.1395 -        WHERE "vote_counts"."initiative_id" = "initiative"."id"
  1.1396 -        AND "issue"."id" = "initiative"."issue_id"
  1.1397 -        AND "policy"."id" = "issue"."policy_id";
  1.1398 -      -- NOTE: "closed" column of issue must be set at this point
  1.1399 -      DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
  1.1400 -      INSERT INTO "battle" (
  1.1401 -        "issue_id",
  1.1402 -        "winning_initiative_id", "losing_initiative_id",
  1.1403 -        "count"
  1.1404 -      ) SELECT
  1.1405 -        "issue_id",
  1.1406 -        "winning_initiative_id", "losing_initiative_id",
  1.1407 -        "count"
  1.1408 -        FROM "battle_view" WHERE "issue_id" = "issue_id_p";
  1.1409 -    END;
  1.1410 -  $$;
  1.1411 -
  1.1412 -CREATE OR REPLACE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE)
  1.1413 -  RETURNS VOID
  1.1414 -  LANGUAGE 'plpgsql' VOLATILE AS $$
  1.1415 -    DECLARE
  1.1416 -      "dimension_v"     INTEGER;
  1.1417 -      "vote_matrix"     INT4[][];  -- absolute votes
  1.1418 -      "matrix"          INT8[][];  -- defeat strength / best paths
  1.1419 -      "i"               INTEGER;
  1.1420 -      "j"               INTEGER;
  1.1421 -      "k"               INTEGER;
  1.1422 -      "battle_row"      "battle"%ROWTYPE;
  1.1423 -      "rank_ary"        INT4[];
  1.1424 -      "rank_v"          INT4;
  1.1425 -      "done_v"          INTEGER;
  1.1426 -      "winners_ary"     INTEGER[];
  1.1427 -      "initiative_id_v" "initiative"."id"%TYPE;
  1.1428 -    BEGIN
  1.1429 -      PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE;
  1.1430 -      SELECT count(1) INTO "dimension_v" FROM "initiative"
  1.1431 -        WHERE "issue_id" = "issue_id_p" AND "agreed";
  1.1432 -      IF "dimension_v" = 1 THEN
  1.1433 -        UPDATE "initiative" SET "rank" = 1
  1.1434 -          WHERE "issue_id" = "issue_id_p" AND "agreed";
  1.1435 -      ELSIF "dimension_v" > 1 THEN
  1.1436 -        -- Create "vote_matrix" with absolute number of votes in pairwise
  1.1437 -        -- comparison:
  1.1438 -        "vote_matrix" := "square_matrix_init_string"("dimension_v");  -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
  1.1439 -        "i" := 1;
  1.1440 -        "j" := 2;
  1.1441 -        FOR "battle_row" IN
  1.1442 -          SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"
  1.1443 -          ORDER BY "winning_initiative_id", "losing_initiative_id"
  1.1444 -        LOOP
  1.1445 -          "vote_matrix"["i"]["j"] := "battle_row"."count";
  1.1446 -          IF "j" = "dimension_v" THEN
  1.1447 -            "i" := "i" + 1;
  1.1448 -            "j" := 1;
  1.1449 -          ELSE
  1.1450 -            "j" := "j" + 1;
  1.1451 -            IF "j" = "i" THEN
  1.1452 -              "j" := "j" + 1;
  1.1453 -            END IF;
  1.1454 -          END IF;
  1.1455 -        END LOOP;
  1.1456 -        IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN
  1.1457 -          RAISE EXCEPTION 'Wrong battle count (should not happen)';
  1.1458 -        END IF;
  1.1459 -        -- Store defeat strengths in "matrix" using "defeat_strength"
  1.1460 -        -- function:
  1.1461 -        "matrix" := "square_matrix_init_string"("dimension_v");  -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
  1.1462 -        "i" := 1;
  1.1463 -        LOOP
  1.1464 -          "j" := 1;
  1.1465 -          LOOP
  1.1466 -            IF "i" != "j" THEN
  1.1467 -              "matrix"["i"]["j"] := "defeat_strength"(
  1.1468 -                "vote_matrix"["i"]["j"],
  1.1469 -                "vote_matrix"["j"]["i"]
  1.1470 -              );
  1.1471 -            END IF;
  1.1472 -            EXIT WHEN "j" = "dimension_v";
  1.1473 -            "j" := "j" + 1;
  1.1474 -          END LOOP;
  1.1475 -          EXIT WHEN "i" = "dimension_v";
  1.1476 -          "i" := "i" + 1;
  1.1477 -        END LOOP;
  1.1478 -        -- Find best paths:
  1.1479 -        "i" := 1;
  1.1480 -        LOOP
  1.1481 -          "j" := 1;
  1.1482 -          LOOP
  1.1483 -            IF "i" != "j" THEN
  1.1484 -              "k" := 1;
  1.1485 -              LOOP
  1.1486 -                IF "i" != "k" AND "j" != "k" THEN
  1.1487 -                  IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN
  1.1488 -                    IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN
  1.1489 -                      "matrix"["j"]["k"] := "matrix"["j"]["i"];
  1.1490 -                    END IF;
  1.1491 -                  ELSE
  1.1492 -                    IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN
  1.1493 -                      "matrix"["j"]["k"] := "matrix"["i"]["k"];
  1.1494 -                    END IF;
  1.1495 -                  END IF;
  1.1496 -                END IF;
  1.1497 -                EXIT WHEN "k" = "dimension_v";
  1.1498 -                "k" := "k" + 1;
  1.1499 -              END LOOP;
  1.1500 -            END IF;
  1.1501 -            EXIT WHEN "j" = "dimension_v";
  1.1502 -            "j" := "j" + 1;
  1.1503 -          END LOOP;
  1.1504 -          EXIT WHEN "i" = "dimension_v";
  1.1505 -          "i" := "i" + 1;
  1.1506 -        END LOOP;
  1.1507 -        -- Determine order of winners:
  1.1508 -        "rank_ary" := "array_init_string"("dimension_v");  -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
  1.1509 -        "rank_v" := 1;
  1.1510 -        "done_v" := 0;
  1.1511 -        LOOP
  1.1512 -          "winners_ary" := '{}';
  1.1513 -          "i" := 1;
  1.1514 -          LOOP
  1.1515 -            IF "rank_ary"["i"] ISNULL THEN
  1.1516 -              "j" := 1;
  1.1517 -              LOOP
  1.1518 -                IF
  1.1519 -                  "i" != "j" AND
  1.1520 -                  "rank_ary"["j"] ISNULL AND
  1.1521 -                  "matrix"["j"]["i"] > "matrix"["i"]["j"]
  1.1522 -                THEN
  1.1523 -                  -- someone else is better
  1.1524 -                  EXIT;
  1.1525 -                END IF;
  1.1526 -                IF "j" = "dimension_v" THEN
  1.1527 -                  -- noone is better
  1.1528 -                  "winners_ary" := "winners_ary" || "i";
  1.1529 -                  EXIT;
  1.1530 -                END IF;
  1.1531 -                "j" := "j" + 1;
  1.1532 -              END LOOP;
  1.1533 -            END IF;
  1.1534 -            EXIT WHEN "i" = "dimension_v";
  1.1535 -            "i" := "i" + 1;
  1.1536 -          END LOOP;
  1.1537 -          "i" := 1;
  1.1538 -          LOOP
  1.1539 -            "rank_ary"["winners_ary"["i"]] := "rank_v";
  1.1540 -            "done_v" := "done_v" + 1;
  1.1541 -            EXIT WHEN "i" = array_upper("winners_ary", 1);
  1.1542 -            "i" := "i" + 1;
  1.1543 -          END LOOP;
  1.1544 -          EXIT WHEN "done_v" = "dimension_v";
  1.1545 -          "rank_v" := "rank_v" + 1;
  1.1546 -        END LOOP;
  1.1547 -        -- write preliminary ranks:
  1.1548 -        "i" := 1;
  1.1549 -        FOR "initiative_id_v" IN
  1.1550 -          SELECT "id" FROM "initiative"
  1.1551 -          WHERE "issue_id" = "issue_id_p" AND "agreed"
  1.1552 -          ORDER BY "id"
  1.1553 -        LOOP
  1.1554 -          UPDATE "initiative" SET "rank" = "rank_ary"["i"]
  1.1555 -            WHERE "id" = "initiative_id_v";
  1.1556 -          "i" := "i" + 1;
  1.1557 -        END LOOP;
  1.1558 -        IF "i" != "dimension_v" + 1 THEN
  1.1559 -          RAISE EXCEPTION 'Wrong winner count (should not happen)';
  1.1560 -        END IF;
  1.1561 -        -- straighten ranks (start counting with 1, no equal ranks):
  1.1562 -        "rank_v" := 1;
  1.1563 -        FOR "initiative_id_v" IN
  1.1564 -          SELECT "id" FROM "initiative"
  1.1565 -          WHERE "issue_id" = "issue_id_p" AND "rank" NOTNULL
  1.1566 -          ORDER BY
  1.1567 -            "rank",
  1.1568 -            "vote_ratio"("positive_votes", "negative_votes") DESC,
  1.1569 -            "id"
  1.1570 -        LOOP
  1.1571 -          UPDATE "initiative" SET "rank" = "rank_v"
  1.1572 -            WHERE "id" = "initiative_id_v";
  1.1573 -          "rank_v" := "rank_v" + 1;
  1.1574 -        END LOOP;
  1.1575 -      END IF;
  1.1576 -      -- mark issue as finished
  1.1577 -      UPDATE "issue" SET
  1.1578 -        "state" =
  1.1579 -          CASE WHEN NOT EXISTS (
  1.1580 -            SELECT NULL FROM "initiative"
  1.1581 -            WHERE "issue_id" = "issue_id_p" AND "admitted"
  1.1582 -          ) THEN
  1.1583 -            'canceled_no_initiative_admitted'::"issue_state"
  1.1584 -          ELSE
  1.1585 -            CASE WHEN "dimension_v" = 0 THEN
  1.1586 -              'finished_without_winner'::"issue_state"
  1.1587 -            ELSE
  1.1588 -              'finished_with_winner'::"issue_state"
  1.1589 -            END
  1.1590 -          END,
  1.1591 -        "ranks_available" = TRUE
  1.1592 -        WHERE "id" = "issue_id_p";
  1.1593 -      RETURN;
  1.1594 -    END;
  1.1595 -  $$;
  1.1596 -
  1.1597 -CREATE OR REPLACE FUNCTION "check_issue"
  1.1598 -  ( "issue_id_p" "issue"."id"%TYPE )
  1.1599 -  RETURNS VOID
  1.1600 -  LANGUAGE 'plpgsql' VOLATILE AS $$
  1.1601 -    DECLARE
  1.1602 -      "issue_row"         "issue"%ROWTYPE;
  1.1603 -      "policy_row"        "policy"%ROWTYPE;
  1.1604 -      "voting_requested_v" BOOLEAN;
  1.1605 -    BEGIN
  1.1606 -      PERFORM "lock_issue"("issue_id_p");
  1.1607 -      SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
  1.1608 -      -- only process open issues:
  1.1609 -      IF "issue_row"."closed" ISNULL THEN
  1.1610 -        SELECT * INTO "policy_row" FROM "policy"
  1.1611 -          WHERE "id" = "issue_row"."policy_id";
  1.1612 -        -- create a snapshot, unless issue is already fully frozen:
  1.1613 -        IF "issue_row"."fully_frozen" ISNULL THEN
  1.1614 -          PERFORM "create_snapshot"("issue_id_p");
  1.1615 -          SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
  1.1616 -        END IF;
  1.1617 -        -- eventually close or accept issues, which have not been accepted:
  1.1618 -        IF "issue_row"."accepted" ISNULL THEN
  1.1619 -          IF EXISTS (
  1.1620 -            SELECT NULL FROM "initiative"
  1.1621 -            WHERE "issue_id" = "issue_id_p"
  1.1622 -            AND "supporter_count" > 0
  1.1623 -            AND "supporter_count" * "policy_row"."issue_quorum_den"
  1.1624 -            >= "issue_row"."population" * "policy_row"."issue_quorum_num"
  1.1625 -          ) THEN
  1.1626 -            -- accept issues, if supporter count is high enough
  1.1627 -            PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
  1.1628 -            -- NOTE: "issue_row" used later
  1.1629 -            "issue_row"."state" := 'discussion';
  1.1630 -            "issue_row"."accepted" := now();
  1.1631 -            UPDATE "issue" SET
  1.1632 -              "state"    = "issue_row"."state",
  1.1633 -              "accepted" = "issue_row"."accepted"
  1.1634 -              WHERE "id" = "issue_row"."id";
  1.1635 -          ELSIF
  1.1636 -            now() >= "issue_row"."created" + "issue_row"."admission_time"
  1.1637 -          THEN
  1.1638 -            -- close issues, if admission time has expired
  1.1639 -            PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
  1.1640 -            UPDATE "issue" SET
  1.1641 -              "state" = 'canceled_issue_not_accepted',
  1.1642 -              "closed" = now()
  1.1643 -              WHERE "id" = "issue_row"."id";
  1.1644 -          END IF;
  1.1645 -        END IF;
  1.1646 -        -- eventually half freeze issues:
  1.1647 -        IF
  1.1648 -          -- NOTE: issue can't be closed at this point, if it has been accepted
  1.1649 -          "issue_row"."accepted" NOTNULL AND
  1.1650 -          "issue_row"."half_frozen" ISNULL
  1.1651 -        THEN
  1.1652 -          SELECT
  1.1653 -            CASE
  1.1654 -              WHEN "vote_now" * 2 > "issue_row"."population" THEN
  1.1655 -                TRUE
  1.1656 -              WHEN "vote_later" * 2 > "issue_row"."population" THEN
  1.1657 -                FALSE
  1.1658 -              ELSE NULL
  1.1659 -            END
  1.1660 -            INTO "voting_requested_v"
  1.1661 -            FROM "issue" WHERE "id" = "issue_id_p";
  1.1662 -          IF
  1.1663 -            "voting_requested_v" OR (
  1.1664 -              "voting_requested_v" ISNULL AND
  1.1665 -              now() >= "issue_row"."accepted" + "issue_row"."discussion_time"
  1.1666 -            )
  1.1667 -          THEN
  1.1668 -            PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze');
  1.1669 -            -- NOTE: "issue_row" used later
  1.1670 -            "issue_row"."state" := 'verification';
  1.1671 -            "issue_row"."half_frozen" := now();
  1.1672 -            UPDATE "issue" SET
  1.1673 -              "state"       = "issue_row"."state",
  1.1674 -              "half_frozen" = "issue_row"."half_frozen"
  1.1675 -              WHERE "id" = "issue_row"."id";
  1.1676 -          END IF;
  1.1677 -        END IF;
  1.1678 -        -- close issues after some time, if all initiatives have been revoked:
  1.1679 -        IF
  1.1680 -          "issue_row"."closed" ISNULL AND
  1.1681 -          NOT EXISTS (
  1.1682 -            -- all initiatives are revoked
  1.1683 -            SELECT NULL FROM "initiative"
  1.1684 -            WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
  1.1685 -          ) AND (
  1.1686 -            -- and issue has not been accepted yet
  1.1687 -            "issue_row"."accepted" ISNULL OR
  1.1688 -            NOT EXISTS (
  1.1689 -              -- or no initiatives have been revoked lately
  1.1690 -              SELECT NULL FROM "initiative"
  1.1691 -              WHERE "issue_id" = "issue_id_p"
  1.1692 -              AND now() < "revoked" + "issue_row"."verification_time"
  1.1693 -            ) OR (
  1.1694 -              -- or verification time has elapsed
  1.1695 -              "issue_row"."half_frozen" NOTNULL AND
  1.1696 -              "issue_row"."fully_frozen" ISNULL AND
  1.1697 -              now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
  1.1698 -            )
  1.1699 -          )
  1.1700 -        THEN
  1.1701 -          -- NOTE: "issue_row" used later
  1.1702 -          IF "issue_row"."accepted" ISNULL THEN
  1.1703 -            "issue_row"."state" := 'canceled_revoked_before_accepted';
  1.1704 -          ELSIF "issue_row"."half_frozen" ISNULL THEN
  1.1705 -            "issue_row"."state" := 'canceled_after_revocation_during_discussion';
  1.1706 -          ELSE
  1.1707 -            "issue_row"."state" := 'canceled_after_revocation_during_verification';
  1.1708 -          END IF;
  1.1709 -          "issue_row"."closed" := now();
  1.1710 -          UPDATE "issue" SET
  1.1711 -            "state"  = "issue_row"."state",
  1.1712 -            "closed" = "issue_row"."closed"
  1.1713 -            WHERE "id" = "issue_row"."id";
  1.1714 -        END IF;
  1.1715 -        -- fully freeze issue after verification time:
  1.1716 -        IF
  1.1717 -          "issue_row"."half_frozen" NOTNULL AND
  1.1718 -          "issue_row"."fully_frozen" ISNULL AND
  1.1719 -          "issue_row"."closed" ISNULL AND
  1.1720 -          now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
  1.1721 -        THEN
  1.1722 -          PERFORM "freeze_after_snapshot"("issue_id_p");
  1.1723 -          -- NOTE: "issue" might change, thus "issue_row" has to be updated below
  1.1724 -        END IF;
  1.1725 -        SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
  1.1726 -        -- close issue by calling close_voting(...) after voting time:
  1.1727 -        IF
  1.1728 -          "issue_row"."closed" ISNULL AND
  1.1729 -          "issue_row"."fully_frozen" NOTNULL AND
  1.1730 -          now() >= "issue_row"."fully_frozen" + "issue_row"."voting_time"
  1.1731 -        THEN
  1.1732 -          PERFORM "close_voting"("issue_id_p");
  1.1733 -          -- calculate ranks will not consume much time and can be done now
  1.1734 -          PERFORM "calculate_ranks"("issue_id_p");
  1.1735 -        END IF;
  1.1736 -      END IF;
  1.1737 -      RETURN;
  1.1738 -    END;
  1.1739 -  $$;
  1.1740 -
  1.1741 -CREATE OR REPLACE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE)
  1.1742 -  RETURNS VOID
  1.1743 -  LANGUAGE 'plpgsql' VOLATILE AS $$
  1.1744 -    DECLARE
  1.1745 -      "issue_row" "issue"%ROWTYPE;
  1.1746 -    BEGIN
  1.1747 -      SELECT * INTO "issue_row"
  1.1748 -        FROM "issue" WHERE "id" = "issue_id_p"
  1.1749 -        FOR UPDATE;
  1.1750 -      IF "issue_row"."cleaned" ISNULL THEN
  1.1751 -        UPDATE "issue" SET
  1.1752 -          "closed" = NULL,
  1.1753 -          "ranks_available" = FALSE
  1.1754 -          WHERE "id" = "issue_id_p";
  1.1755 -        DELETE FROM "delegating_voter"
  1.1756 -          WHERE "issue_id" = "issue_id_p";
  1.1757 -        DELETE FROM "direct_voter"
  1.1758 -          WHERE "issue_id" = "issue_id_p";
  1.1759 -        DELETE FROM "delegating_interest_snapshot"
  1.1760 -          WHERE "issue_id" = "issue_id_p";
  1.1761 -        DELETE FROM "direct_interest_snapshot"
  1.1762 -          WHERE "issue_id" = "issue_id_p";
  1.1763 -        DELETE FROM "delegating_population_snapshot"
  1.1764 -          WHERE "issue_id" = "issue_id_p";
  1.1765 -        DELETE FROM "direct_population_snapshot"
  1.1766 -          WHERE "issue_id" = "issue_id_p";
  1.1767 -        DELETE FROM "non_voter"
  1.1768 -          WHERE "issue_id" = "issue_id_p";
  1.1769 -        DELETE FROM "delegation"
  1.1770 -          WHERE "issue_id" = "issue_id_p";
  1.1771 -        DELETE FROM "supporter"
  1.1772 -          WHERE "issue_id" = "issue_id_p";
  1.1773 -        UPDATE "issue" SET
  1.1774 -          "closed"          = "issue_row"."closed",
  1.1775 -          "ranks_available" = "issue_row"."ranks_available",
  1.1776 -          "cleaned"         = now()
  1.1777 -          WHERE "id" = "issue_id_p";
  1.1778 -      END IF;
  1.1779 -      RETURN;
  1.1780 -    END;
  1.1781 -  $$;
  1.1782 -
  1.1783 -CREATE OR REPLACE FUNCTION "check_issue"
  1.1784 -  ( "issue_id_p" "issue"."id"%TYPE )
  1.1785 -  RETURNS VOID
  1.1786 -  LANGUAGE 'plpgsql' VOLATILE AS $$
  1.1787 -    DECLARE
  1.1788 -      "issue_row"         "issue"%ROWTYPE;
  1.1789 -      "policy_row"        "policy"%ROWTYPE;
  1.1790 -      "voting_requested_v" BOOLEAN;
  1.1791 -    BEGIN
  1.1792 -      PERFORM "lock_issue"("issue_id_p");
  1.1793 -      SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
  1.1794 -      -- only process open issues:
  1.1795 -      IF "issue_row"."closed" ISNULL THEN
  1.1796 -        SELECT * INTO "policy_row" FROM "policy"
  1.1797 -          WHERE "id" = "issue_row"."policy_id";
  1.1798 -        -- create a snapshot, unless issue is already fully frozen:
  1.1799 -        IF "issue_row"."fully_frozen" ISNULL THEN
  1.1800 -          PERFORM "create_snapshot"("issue_id_p");
  1.1801 -          SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
  1.1802 -        END IF;
  1.1803 -        -- eventually close or accept issues, which have not been accepted:
  1.1804 -        IF "issue_row"."accepted" ISNULL THEN
  1.1805 -          IF EXISTS (
  1.1806 -            SELECT NULL FROM "initiative"
  1.1807 -            WHERE "issue_id" = "issue_id_p"
  1.1808 -            AND "supporter_count" > 0
  1.1809 -            AND "supporter_count" * "policy_row"."issue_quorum_den"
  1.1810 -            >= "issue_row"."population" * "policy_row"."issue_quorum_num"
  1.1811 -          ) THEN
  1.1812 -            -- accept issues, if supporter count is high enough
  1.1813 -            PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
  1.1814 -            -- NOTE: "issue_row" used later
  1.1815 -            "issue_row"."state" := 'discussion';
  1.1816 -            "issue_row"."accepted" := now();
  1.1817 -            UPDATE "issue" SET
  1.1818 -              "state"    = "issue_row"."state",
  1.1819 -              "accepted" = "issue_row"."accepted"
  1.1820 -              WHERE "id" = "issue_row"."id";
  1.1821 -          ELSIF
  1.1822 -            now() >= "issue_row"."created" + "issue_row"."admission_time"
  1.1823 -          THEN
  1.1824 -            -- close issues, if admission time has expired
  1.1825 -            PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
  1.1826 -            UPDATE "issue" SET
  1.1827 -              "state" = 'canceled_issue_not_accepted',
  1.1828 -              "closed" = now()
  1.1829 -              WHERE "id" = "issue_row"."id";
  1.1830 -          END IF;
  1.1831 -        END IF;
  1.1832 -        -- eventually half freeze issues:
  1.1833 -        IF
  1.1834 -          -- NOTE: issue can't be closed at this point, if it has been accepted
  1.1835 -          "issue_row"."accepted" NOTNULL AND
  1.1836 -          "issue_row"."half_frozen" ISNULL
  1.1837 -        THEN
  1.1838 -          SELECT
  1.1839 -            CASE
  1.1840 -              WHEN "vote_now" * 2 > "issue_row"."population" THEN
  1.1841 -                TRUE
  1.1842 -              WHEN "vote_later" * 2 > "issue_row"."population" THEN
  1.1843 -                FALSE
  1.1844 -              ELSE NULL
  1.1845 -            END
  1.1846 -            INTO "voting_requested_v"
  1.1847 -            FROM "issue" WHERE "id" = "issue_id_p";
  1.1848 -          IF
  1.1849 -            "voting_requested_v" OR (
  1.1850 -              "voting_requested_v" ISNULL AND
  1.1851 -              now() >= "issue_row"."accepted" + "issue_row"."discussion_time"
  1.1852 -            )
  1.1853 -          THEN
  1.1854 -            PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze');
  1.1855 -            -- NOTE: "issue_row" used later
  1.1856 -            "issue_row"."state" := 'verification';
  1.1857 -            "issue_row"."half_frozen" := now();
  1.1858 -            UPDATE "issue" SET
  1.1859 -              "state"       = "issue_row"."state",
  1.1860 -              "half_frozen" = "issue_row"."half_frozen"
  1.1861 -              WHERE "id" = "issue_row"."id";
  1.1862 -          END IF;
  1.1863 -        END IF;
  1.1864 -        -- close issues after some time, if all initiatives have been revoked:
  1.1865 -        IF
  1.1866 -          "issue_row"."closed" ISNULL AND
  1.1867 -          NOT EXISTS (
  1.1868 -            -- all initiatives are revoked
  1.1869 -            SELECT NULL FROM "initiative"
  1.1870 -            WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
  1.1871 -          ) AND (
  1.1872 -            -- and issue has not been accepted yet
  1.1873 -            "issue_row"."accepted" ISNULL OR
  1.1874 -            NOT EXISTS (
  1.1875 -              -- or no initiatives have been revoked lately
  1.1876 -              SELECT NULL FROM "initiative"
  1.1877 -              WHERE "issue_id" = "issue_id_p"
  1.1878 -              AND now() < "revoked" + "issue_row"."verification_time"
  1.1879 -            ) OR (
  1.1880 -              -- or verification time has elapsed
  1.1881 -              "issue_row"."half_frozen" NOTNULL AND
  1.1882 -              "issue_row"."fully_frozen" ISNULL AND
  1.1883 -              now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
  1.1884 -            )
  1.1885 -          )
  1.1886 -        THEN
  1.1887 -          -- NOTE: "issue_row" used later
  1.1888 -          IF "issue_row"."accepted" ISNULL THEN
  1.1889 -            "issue_row"."state" := 'canceled_revoked_before_accepted';
  1.1890 -          ELSIF "issue_row"."half_frozen" ISNULL THEN
  1.1891 -            "issue_row"."state" := 'canceled_after_revocation_during_discussion';
  1.1892 -          ELSE
  1.1893 -            "issue_row"."state" := 'canceled_after_revocation_during_verification';
  1.1894 -          END IF;
  1.1895 -          "issue_row"."closed" := now();
  1.1896 -          UPDATE "issue" SET
  1.1897 -            "state"  = "issue_row"."state",
  1.1898 -            "closed" = "issue_row"."closed"
  1.1899 -            WHERE "id" = "issue_row"."id";
  1.1900 -        END IF;
  1.1901 -        -- fully freeze issue after verification time:
  1.1902 -        IF
  1.1903 -          "issue_row"."half_frozen" NOTNULL AND
  1.1904 -          "issue_row"."fully_frozen" ISNULL AND
  1.1905 -          "issue_row"."closed" ISNULL AND
  1.1906 -          now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
  1.1907 -        THEN
  1.1908 -          PERFORM "freeze_after_snapshot"("issue_id_p");
  1.1909 -          -- NOTE: "issue" might change, thus "issue_row" has to be updated below
  1.1910 -        END IF;
  1.1911 -        SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
  1.1912 -        -- close issue by calling close_voting(...) after voting time:
  1.1913 -        IF
  1.1914 -          "issue_row"."closed" ISNULL AND
  1.1915 -          "issue_row"."fully_frozen" NOTNULL AND
  1.1916 -          now() >= "issue_row"."fully_frozen" + "issue_row"."voting_time"
  1.1917 -        THEN
  1.1918 -          PERFORM "close_voting"("issue_id_p");
  1.1919 -          -- calculate ranks will not consume much time and can be done now
  1.1920 -          PERFORM "calculate_ranks"("issue_id_p");
  1.1921 -        END IF;
  1.1922 -      END IF;
  1.1923 -      RETURN;
  1.1924 -    END;
  1.1925 -  $$;
  1.1926 -
  1.1927 -CREATE OR REPLACE FUNCTION "delete_private_data"()
  1.1928 -  RETURNS VOID
  1.1929 -  LANGUAGE 'plpgsql' VOLATILE AS $$
  1.1930 -    BEGIN
  1.1931 -      UPDATE "member" SET
  1.1932 -        "last_login"                   = NULL,
  1.1933 -        "login"                        = NULL,
  1.1934 -        "password"                     = NULL,
  1.1935 -        "notify_email"                 = NULL,
  1.1936 -        "notify_email_unconfirmed"     = NULL,
  1.1937 -        "notify_email_secret"          = NULL,
  1.1938 -        "notify_email_secret_expiry"   = NULL,
  1.1939 -        "notify_email_lock_expiry"     = NULL,
  1.1940 -        "password_reset_secret"        = NULL,
  1.1941 -        "password_reset_secret_expiry" = NULL,
  1.1942 -        "organizational_unit"          = NULL,
  1.1943 -        "internal_posts"               = NULL,
  1.1944 -        "realname"                     = NULL,
  1.1945 -        "birthday"                     = NULL,
  1.1946 -        "address"                      = NULL,
  1.1947 -        "email"                        = NULL,
  1.1948 -        "xmpp_address"                 = NULL,
  1.1949 -        "website"                      = NULL,
  1.1950 -        "phone"                        = NULL,
  1.1951 -        "mobile_phone"                 = NULL,
  1.1952 -        "profession"                   = NULL,
  1.1953 -        "external_memberships"         = NULL,
  1.1954 -        "external_posts"               = NULL,
  1.1955 -        "statement"                    = NULL;
  1.1956 -      -- "text_search_data" is updated by triggers
  1.1957 -      DELETE FROM "invite_code";
  1.1958 -      DELETE FROM "setting";
  1.1959 -      DELETE FROM "setting_map";
  1.1960 -      DELETE FROM "member_relation_setting";
  1.1961 -      DELETE FROM "member_image";
  1.1962 -      DELETE FROM "contact";
  1.1963 -      DELETE FROM "ignored_member";
  1.1964 -      DELETE FROM "session";
  1.1965 -      DELETE FROM "area_setting";
  1.1966 -      DELETE FROM "issue_setting";
  1.1967 -      DELETE FROM "ignored_initiative";
  1.1968 -      DELETE FROM "initiative_setting";
  1.1969 -      DELETE FROM "suggestion_setting";
  1.1970 -      DELETE FROM "non_voter";
  1.1971 -      DELETE FROM "direct_voter" USING "issue"
  1.1972 -        WHERE "direct_voter"."issue_id" = "issue"."id"
  1.1973 -        AND "issue"."closed" ISNULL;
  1.1974 -      RETURN;
  1.1975 -    END;
  1.1976 -  $$;
  1.1977 -
  1.1978 -
  1.1979 --- Delete old "delegation_scope" TYPE:
  1.1980 -
  1.1981 -DROP TYPE "delegation_scope_old";
  1.1982 -
  1.1983 -
  1.1984 -COMMIT;
  1.1985 -
  1.1986 -
  1.1987 --- Generate issue states:
  1.1988 -
  1.1989 -UPDATE "issue" SET "state" =
  1.1990 -  CASE
  1.1991 -  WHEN "closed" ISNULL THEN
  1.1992 -    CASE
  1.1993 -    WHEN "accepted" ISNULL THEN
  1.1994 -      'admission'::"issue_state"
  1.1995 -    WHEN "half_frozen" ISNULL THEN
  1.1996 -      'discussion'::"issue_state"
  1.1997 -    WHEN "fully_frozen" ISNULL THEN
  1.1998 -      'verification'::"issue_state"
  1.1999 -    ELSE
  1.2000 -      'voting'::"issue_state"
  1.2001 -    END
  1.2002 -  WHEN "fully_frozen" NOTNULL THEN
  1.2003 -    CASE
  1.2004 -    WHEN "fully_frozen" = "closed" THEN
  1.2005 -      'canceled_no_initiative_admitted'::"issue_state"
  1.2006 -    ELSE
  1.2007 -      'finished_without_winner'::"issue_state"  -- NOTE: corrected later
  1.2008 -    END
  1.2009 -  WHEN "half_frozen" NOTNULL THEN
  1.2010 -    'canceled_after_revocation_during_verification'::"issue_state"
  1.2011 -  WHEN "accepted" NOTNULL THEN
  1.2012 -    'canceled_after_revocation_during_discussion'::"issue_state"
  1.2013 -  ELSE
  1.2014 -    'canceled_revoked_before_accepted'::"issue_state"  -- NOTE: corrected later
  1.2015 -  END;
  1.2016 -
  1.2017 -ALTER TABLE "issue" ALTER "state" SET NOT NULL;
  1.2018 -
  1.2019 -ALTER TABLE "issue" DROP CONSTRAINT "valid_state";
  1.2020 -ALTER TABLE "issue" ADD CONSTRAINT "valid_state" CHECK ((
  1.2021 -    ("accepted" ISNULL  AND "half_frozen" ISNULL  AND "fully_frozen" ISNULL  AND "closed" ISNULL  AND "ranks_available" = FALSE) OR
  1.2022 -    ("accepted" ISNULL  AND "half_frozen" ISNULL  AND "fully_frozen" ISNULL  AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
  1.2023 -    ("accepted" NOTNULL AND "half_frozen" ISNULL  AND "fully_frozen" ISNULL  AND "closed" ISNULL  AND "ranks_available" = FALSE) OR
  1.2024 -    ("accepted" NOTNULL AND "half_frozen" ISNULL  AND "fully_frozen" ISNULL  AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
  1.2025 -    ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL  AND "closed" ISNULL  AND "ranks_available" = FALSE) OR
  1.2026 -    ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL  AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
  1.2027 -    ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" ISNULL  AND "ranks_available" = FALSE) OR
  1.2028 -    ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
  1.2029 -    ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = TRUE)
  1.2030 -  ) AND (
  1.2031 -    ("state" = 'admission'    AND "closed" ISNULL AND "accepted" ISNULL) OR
  1.2032 -    ("state" = 'discussion'   AND "closed" ISNULL AND "accepted" NOTNULL AND "half_frozen" ISNULL) OR
  1.2033 -    ("state" = 'verification' AND "closed" ISNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL) OR
  1.2034 -    ("state" = 'voting'       AND "closed" ISNULL AND "fully_frozen" NOTNULL) OR
  1.2035 -    ("state" = 'canceled_revoked_before_accepted'              AND "closed" NOTNULL AND "accepted" ISNULL) OR
  1.2036 -    ("state" = 'canceled_issue_not_accepted'                   AND "closed" NOTNULL AND "accepted" ISNULL) OR
  1.2037 -    ("state" = 'canceled_after_revocation_during_discussion'   AND "closed" NOTNULL AND "half_frozen"  ISNULL) OR
  1.2038 -    ("state" = 'canceled_after_revocation_during_verification' AND "closed" NOTNULL AND "fully_frozen" ISNULL) OR
  1.2039 -    ("state" = 'calculation'                     AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = FALSE) OR
  1.2040 -    ("state" = 'canceled_no_initiative_admitted' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE) OR
  1.2041 -    ("state" = 'finished_without_winner'         AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE) OR
  1.2042 -    ("state" = 'finished_with_winner'            AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE)
  1.2043 -  ));
  1.2044 -
  1.2045 -UPDATE "issue" SET "state" = 'finished_with_winner'
  1.2046 -  FROM "initiative"
  1.2047 -  WHERE "issue"."id" = "initiative"."issue_id"
  1.2048 -  AND "issue"."state" = 'finished_without_winner'
  1.2049 -  AND "initiative"."agreed";
  1.2050 -UPDATE "issue" SET "state" = 'canceled_issue_not_accepted'
  1.2051 -  FROM "initiative"
  1.2052 -  WHERE "issue"."id" = "initiative"."issue_id"
  1.2053 -  AND "issue"."state" = 'canceled_revoked_before_accepted'
  1.2054 -  AND "initiative"."revoked" ISNULL;
  1.2055 -
  1.2056 -
  1.2057 --- Guess "revoked_by_member_id" values based on author of current draft:
  1.2058 -
  1.2059 -UPDATE "initiative" SET "revoked_by_member_id" = "author_id"
  1.2060 -  FROM "current_draft"
  1.2061 -  WHERE "initiative"."id" = "current_draft"."initiative_id"
  1.2062 -  AND "initiative"."revoked" NOTNULL;
  1.2063 -
  1.2064 -ALTER TABLE "initiative" ADD
  1.2065 -  CONSTRAINT "all_or_none_of_revoked_and_revoked_by_member_id_must_be_null"
  1.2066 -  CHECK ("revoked" NOTNULL = "revoked_by_member_id" NOTNULL);
  1.2067 -
  1.2068 -
  1.2069 --- Attach existing and unused invite codes with unit id 1:
  1.2070 -
  1.2071 -INSERT INTO "invite_code_unit" ("invite_code_id", "unit_id")
  1.2072 -  SELECT "id" AS "invite_code_id", 1 AS "unit_id"
  1.2073 -  FROM "invite_code" WHERE "used" ISNULL;
  1.2074 -
  1.2075 -
  1.2076 --- Fill "unit_id" column with default value where neccessary:
  1.2077 -
  1.2078 -UPDATE "delegation" SET "unit_id" = 1 WHERE "scope" = 'unit';
  1.2079 -
  1.2080 -ALTER TABLE "delegation" ADD CONSTRAINT "area_id_and_issue_id_set_according_to_scope"
  1.2081 -  CHECK (
  1.2082 -    ("scope" = 'unit'  AND "unit_id" NOTNULL AND "area_id" ISNULL  AND "issue_id" ISNULL ) OR
  1.2083 -    ("scope" = 'area'  AND "unit_id" ISNULL  AND "area_id" NOTNULL AND "issue_id" ISNULL ) OR
  1.2084 -    ("scope" = 'issue' AND "unit_id" ISNULL  AND "area_id" ISNULL  AND "issue_id" NOTNULL) );
     2.1 --- /dev/null	Thu Jan 01 00:00:00 1970 +0000
     2.2 +++ b/update/core-update.v1.3.1-v1.4.0_rc1.sql	Mon Mar 07 03:50:00 2011 +0100
     2.3 @@ -0,0 +1,2081 @@
     2.4 +BEGIN;  -- NOTE: file contains additional statements AFTER this BEGIN/COMMIT block!
     2.5 +
     2.6 +
     2.7 +-- Update version information:
     2.8 +
     2.9 +CREATE OR REPLACE VIEW "liquid_feedback_version" AS
    2.10 +  SELECT * FROM (VALUES ('1.4.0_rc1', 1, 4, -1))
    2.11 +  AS "subquery"("string", "major", "minor", "revision");
    2.12 +
    2.13 +
    2.14 +-- New columns "notify_level" and "notify_event_id" in "member" table:
    2.15 +
    2.16 +CREATE TYPE "notify_level" AS ENUM
    2.17 +  ('none', 'voting', 'verification', 'discussion', 'all');
    2.18 +
    2.19 +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';
    2.20 +
    2.21 +ALTER TABLE "member" ADD "notify_level" "notify_level" NOT NULL DEFAULT 'none';
    2.22 +ALTER TABLE "member" ADD "notify_event_id" INT8;
    2.23 +
    2.24 +COMMENT ON COLUMN "member"."notify_level"    IS 'Selects which event notifications are to be sent to the "notify_email" mail address';
    2.25 +COMMENT ON COLUMN "member"."notify_event_id" IS 'Latest "id" of an "event" the member was notified about';
    2.26 +
    2.27 +
    2.28 +-- Add primary key with type SERIAL8 (INT8) for "invite_code" table:
    2.29 +
    2.30 +ALTER TABLE "invite_code" DROP CONSTRAINT "invite_code_pkey";
    2.31 +ALTER TABLE "invite_code" ALTER "code" SET NOT NULL;
    2.32 +ALTER TABLE "invite_code" ADD UNIQUE ("code");
    2.33 +ALTER TABLE "invite_code" ADD "id" SERIAL8 PRIMARY KEY;
    2.34 +
    2.35 +
    2.36 +-- Add index for "other_member_id" column of "contact" table:
    2.37 +
    2.38 +CREATE INDEX "contact_other_member_id_idx" ON "contact" ("other_member_id");
    2.39 +
    2.40 +
    2.41 +-- New table "ignored_member":
    2.42 +
    2.43 +CREATE TABLE "ignored_member" (
    2.44 +        PRIMARY KEY ("member_id", "other_member_id"),
    2.45 +        "member_id"             INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    2.46 +        "other_member_id"       INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
    2.47 +CREATE INDEX "ignored_member_other_member_id_idx" ON "ignored_member" ("other_member_id");
    2.48 +
    2.49 +COMMENT ON TABLE "ignored_member" IS 'Possibility to filter other members';
    2.50 +
    2.51 +COMMENT ON COLUMN "ignored_member"."member_id"       IS 'Member ignoring someone';
    2.52 +COMMENT ON COLUMN "ignored_member"."other_member_id" IS 'Member being ignored';
    2.53 +
    2.54 +
    2.55 +-- New table "unit" with default entry:
    2.56 +
    2.57 +CREATE TABLE "unit" (
    2.58 +        "id"                    SERIAL4         PRIMARY KEY,
    2.59 +        "parent_id"             INT4            REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    2.60 +        "active"                BOOLEAN         NOT NULL DEFAULT TRUE,
    2.61 +        "name"                  TEXT            NOT NULL,
    2.62 +        "description"           TEXT            NOT NULL DEFAULT '',
    2.63 +        "member_count"          INT4,
    2.64 +        "text_search_data"      TSVECTOR );
    2.65 +CREATE INDEX "unit_root_idx" ON "unit" ("id") WHERE "parent_id" ISNULL;
    2.66 +CREATE INDEX "unit_parent_id_idx" ON "unit" ("parent_id");
    2.67 +CREATE INDEX "unit_active_idx" ON "unit" ("active");
    2.68 +CREATE INDEX "unit_text_search_data_idx" ON "unit" USING gin ("text_search_data");
    2.69 +CREATE TRIGGER "update_text_search_data"
    2.70 +  BEFORE INSERT OR UPDATE ON "unit"
    2.71 +  FOR EACH ROW EXECUTE PROCEDURE
    2.72 +  tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
    2.73 +    "name", "description" );
    2.74 +
    2.75 +COMMENT ON TABLE "unit" IS 'Organizational units organized as trees; Delegations are not inherited through these trees.';
    2.76 +
    2.77 +COMMENT ON COLUMN "unit"."parent_id"    IS 'Parent id of tree node; Multiple roots allowed';
    2.78 +COMMENT ON COLUMN "unit"."active"       IS 'TRUE means new issues can be created in units of this area';
    2.79 +COMMENT ON COLUMN "unit"."member_count" IS 'Count of members as determined by column "voting_right" in table "privilege"';
    2.80 +
    2.81 +INSERT INTO "unit" ("name") VALUES ('Main');  -- NOTE: gets id 1
    2.82 +
    2.83 +
    2.84 +-- New column "unit_id" in table "area":
    2.85 +
    2.86 +ALTER TABLE "area" ADD "unit_id" INT4 DEFAULT 1
    2.87 +  NOT NULL REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
    2.88 +ALTER TABLE "area" ALTER "unit_id" DROP DEFAULT;
    2.89 +
    2.90 +CREATE INDEX "area_unit_id_idx" ON "area" ("unit_id");
    2.91 +
    2.92 +
    2.93 +-- Issue states:
    2.94 +
    2.95 +CREATE TYPE "issue_state" AS ENUM (
    2.96 +        'admission', 'discussion', 'verification', 'voting',
    2.97 +        'canceled_revoked_before_accepted',
    2.98 +        'canceled_issue_not_accepted',
    2.99 +        'canceled_after_revocation_during_discussion',
   2.100 +        'canceled_after_revocation_during_verification',
   2.101 +        'calculation',
   2.102 +        'canceled_no_initiative_admitted',
   2.103 +        'finished_without_winner', 'finished_with_winner');
   2.104 +
   2.105 +COMMENT ON TYPE "issue_state" IS 'State of issues';
   2.106 +
   2.107 +ALTER TABLE "issue" ADD "state" "issue_state";
   2.108 +
   2.109 +-- NOTE: Filling new column with values is done after this transaction (see below)
   2.110 +
   2.111 +
   2.112 +-- New column "revoked_by_member_id" in table "initiative":
   2.113 +
   2.114 +ALTER TABLE "initiative" ADD "revoked_by_member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE;
   2.115 +
   2.116 +COMMENT ON COLUMN "initiative"."revoked_by_member_id" IS 'Member, who decided to revoked the initiative';
   2.117 +
   2.118 +-- NOTE: Filling new column with values is done after this transaction (see below)
   2.119 +
   2.120 +
   2.121 +-- New table "ignored_initiative":
   2.122 +
   2.123 +CREATE TABLE "ignored_initiative" (
   2.124 +        PRIMARY KEY ("initiative_id", "member_id"),
   2.125 +        "initiative_id"         INT4            REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
   2.126 +        "member_id"             INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
   2.127 +CREATE INDEX "ignored_initiative_member_id_idx" ON "ignored_initiative" ("member_id");
   2.128 +
   2.129 +COMMENT ON TABLE "ignored_initiative" IS 'Possibility to filter initiatives';
   2.130 +
   2.131 +
   2.132 +-- New table "invite_code_unit":
   2.133 +
   2.134 +CREATE TABLE "invite_code_unit" (
   2.135 +        PRIMARY KEY ("invite_code_id", "unit_id"),
   2.136 +        "invite_code_id"        INT8            REFERENCES "invite_code" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
   2.137 +        "unit_id"               INT4            REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
   2.138 +
   2.139 +COMMENT ON TABLE "invite_code_unit" IS 'Units where accounts created with a given invite codes get voting rights';
   2.140 +
   2.141 +-- TODO: Table is filled after this transaction (see below)
   2.142 +
   2.143 +
   2.144 +-- New table "privilege":
   2.145 +
   2.146 +CREATE TABLE "privilege" (
   2.147 +        PRIMARY KEY ("unit_id", "member_id"),
   2.148 +        "unit_id"               INT4            REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
   2.149 +        "member_id"             INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
   2.150 +        "admin_manager"         BOOLEAN         NOT NULL DEFAULT FALSE,
   2.151 +        "unit_manager"          BOOLEAN         NOT NULL DEFAULT FALSE,
   2.152 +        "area_manager"          BOOLEAN         NOT NULL DEFAULT FALSE,
   2.153 +        "voting_right_manager"  BOOLEAN         NOT NULL DEFAULT FALSE,
   2.154 +        "voting_right"          BOOLEAN         NOT NULL DEFAULT TRUE );
   2.155 +
   2.156 +COMMENT ON TABLE "privilege" IS 'Members rights related to each unit';
   2.157 +
   2.158 +COMMENT ON COLUMN "privilege"."admin_manager"        IS 'Grant/revoke admin privileges to/from other users';
   2.159 +COMMENT ON COLUMN "privilege"."unit_manager"         IS 'Create or lock sub units';
   2.160 +COMMENT ON COLUMN "privilege"."area_manager"         IS 'Create or lock areas and set area parameters';
   2.161 +COMMENT ON COLUMN "privilege"."voting_right_manager" IS 'Select which members are allowed to discuss and vote inside the unit';
   2.162 +COMMENT ON COLUMN "privilege"."voting_right"         IS 'Right to discuss and vote';
   2.163 +
   2.164 +
   2.165 +-- Remove table "ignored_issue", which is no longer existent:
   2.166 +
   2.167 +DROP TABLE "ignored_issue";
   2.168 +
   2.169 +
   2.170 +-- Replace TYPE "delegation_scope" with a new type, where 'global' is replaced by 'unit':
   2.171 +
   2.172 +ALTER TYPE "delegation_scope" RENAME TO "delegation_scope_old";  -- NOTE: dropped later
   2.173 +CREATE TYPE "delegation_scope" AS ENUM ('unit', 'area', 'issue');
   2.174 +COMMENT ON TYPE "delegation_scope" IS 'Scope for delegations: ''unit'', ''area'', or ''issue'' (order is relevant)';
   2.175 +
   2.176 +
   2.177 +-- Delete views and functions being dependent on TYPE "delegation_scope":
   2.178 +
   2.179 +DROP FUNCTION "delegation_chain"
   2.180 +  ( "member_id_p" "member"."id"%TYPE,
   2.181 +    "area_id_p"   "area"."id"%TYPE,
   2.182 +    "issue_id_p"  "issue"."id"%TYPE );
   2.183 +
   2.184 +DROP FUNCTION "delegation_chain"
   2.185 +  ( "member_id_p"           "member"."id"%TYPE,
   2.186 +    "area_id_p"             "area"."id"%TYPE,
   2.187 +    "issue_id_p"            "issue"."id"%TYPE,
   2.188 +    "simulate_trustee_id_p" "member"."id"%TYPE );
   2.189 +
   2.190 +DROP TYPE "delegation_chain_row";
   2.191 +
   2.192 +DROP VIEW "issue_delegation";
   2.193 +DROP VIEW "area_delegation";
   2.194 +DROP VIEW "global_delegation";
   2.195 +DROP VIEW "active_delegation";
   2.196 +
   2.197 +
   2.198 +-- Modify "delegation" table to use new "delegation_scope" TYPE:
   2.199 +
   2.200 +ALTER TABLE "delegation" DROP CONSTRAINT "no_global_delegation_to_null";
   2.201 +ALTER TABLE "delegation" DROP CONSTRAINT "area_id_and_issue_id_set_according_to_scope";
   2.202 +
   2.203 +DROP INDEX "delegation_global_truster_id_unique_idx";
   2.204 +
   2.205 +ALTER TABLE "delegation" ALTER "scope" TYPE "delegation_scope"
   2.206 +  USING CASE WHEN "scope" = 'global'
   2.207 +  THEN 'unit'::"delegation_scope"
   2.208 +  ELSE "scope"::text::"delegation_scope" END;
   2.209 +
   2.210 +ALTER TABLE "delegation" ADD "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
   2.211 +
   2.212 +ALTER TABLE "delegation" ADD CONSTRAINT "no_unit_delegation_to_null"
   2.213 +  CHECK ("trustee_id" NOTNULL OR "scope" != 'unit');
   2.214 +
   2.215 +ALTER TABLE "delegation" ADD UNIQUE ("unit_id", "truster_id");
   2.216 +
   2.217 +COMMENT ON COLUMN "delegation"."unit_id"  IS 'Reference to unit, if delegation is unit-wide, otherwise NULL';
   2.218 +
   2.219 +-- NOTE: Column "unit_id" filled after transaction (see below)
   2.220 +
   2.221 +
   2.222 +-- Modify snapshot tables to use new "delegation_scope" TYPE:
   2.223 +
   2.224 +ALTER TABLE "delegating_population_snapshot" ALTER "scope" TYPE "delegation_scope"
   2.225 +  USING CASE WHEN "scope" = 'global'
   2.226 +  THEN 'unit'::"delegation_scope"
   2.227 +  ELSE "scope"::text::"delegation_scope" END;
   2.228 +
   2.229 +ALTER TABLE "delegating_interest_snapshot" ALTER "scope" TYPE "delegation_scope"
   2.230 +  USING CASE WHEN "scope" = 'global'
   2.231 +  THEN 'unit'::"delegation_scope"
   2.232 +  ELSE "scope"::text::"delegation_scope" END;
   2.233 +
   2.234 +ALTER TABLE "delegating_voter" ALTER "scope" TYPE "delegation_scope"
   2.235 +  USING CASE WHEN "scope" = 'global'
   2.236 +  THEN 'unit'::"delegation_scope"
   2.237 +  ELSE "scope"::text::"delegation_scope" END;
   2.238 +
   2.239 +
   2.240 +-- New table "non_voter":
   2.241 +
   2.242 +CREATE TABLE "non_voter" (
   2.243 +        PRIMARY KEY ("issue_id", "member_id"),
   2.244 +        "issue_id"              INT4            REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
   2.245 +        "member_id"             INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
   2.246 +CREATE INDEX "non_voter_member_id_idx" ON "non_voter" ("member_id");
   2.247 +
   2.248 +COMMENT ON TABLE "non_voter" IS 'Members who decided to not vote directly on an issue';
   2.249 +
   2.250 +
   2.251 +-- New table "issue_comment":
   2.252 +
   2.253 +CREATE TABLE "issue_comment" (
   2.254 +        PRIMARY KEY ("issue_id", "member_id"),
   2.255 +        "issue_id"              INT4            REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
   2.256 +        "member_id"             INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
   2.257 +        "changed"               TIMESTAMPTZ     NOT NULL DEFAULT now(),
   2.258 +        "formatting_engine"     TEXT,
   2.259 +        "content"               TEXT            NOT NULL,
   2.260 +        "text_search_data"      TSVECTOR );
   2.261 +CREATE INDEX "issue_comment_member_id_idx" ON "issue_comment" ("member_id");
   2.262 +CREATE INDEX "issue_comment_text_search_data_idx" ON "issue_comment" USING gin ("text_search_data");
   2.263 +CREATE TRIGGER "update_text_search_data"
   2.264 +  BEFORE INSERT OR UPDATE ON "issue_comment"
   2.265 +  FOR EACH ROW EXECUTE PROCEDURE
   2.266 +  tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "content");
   2.267 +
   2.268 +COMMENT ON TABLE "issue_comment" IS 'Place to store free comments of members related to issues';
   2.269 +
   2.270 +COMMENT ON COLUMN "issue_comment"."changed" IS 'Time the comment was last changed';
   2.271 +
   2.272 +
   2.273 +-- New table "rendered_issue_comment":
   2.274 +
   2.275 +CREATE TABLE "rendered_issue_comment" (
   2.276 +        PRIMARY KEY ("issue_id", "member_id", "format"),
   2.277 +        FOREIGN KEY ("issue_id", "member_id")
   2.278 +          REFERENCES "issue_comment" ("issue_id", "member_id")
   2.279 +          ON DELETE CASCADE ON UPDATE CASCADE,
   2.280 +        "issue_id"              INT4,
   2.281 +        "member_id"             INT4,
   2.282 +        "format"                TEXT,
   2.283 +        "content"               TEXT            NOT NULL );
   2.284 +
   2.285 +COMMENT ON TABLE "rendered_issue_comment" IS 'This table may be used by frontends to cache "rendered" issue comments (e.g. HTML output generated from wiki text)';
   2.286 +
   2.287 +
   2.288 +-- New table "voting_comment":
   2.289 +
   2.290 +CREATE TABLE "voting_comment" (
   2.291 +        PRIMARY KEY ("issue_id", "member_id"),
   2.292 +        "issue_id"              INT4            REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
   2.293 +        "member_id"             INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
   2.294 +        "changed"               TIMESTAMPTZ,
   2.295 +        "formatting_engine"     TEXT,
   2.296 +        "content"               TEXT            NOT NULL,
   2.297 +        "text_search_data"      TSVECTOR );
   2.298 +CREATE INDEX "voting_comment_member_id_idx" ON "voting_comment" ("member_id");
   2.299 +CREATE INDEX "voting_comment_text_search_data_idx" ON "voting_comment" USING gin ("text_search_data");
   2.300 +CREATE TRIGGER "update_text_search_data"
   2.301 +  BEFORE INSERT OR UPDATE ON "voting_comment"
   2.302 +  FOR EACH ROW EXECUTE PROCEDURE
   2.303 +  tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "content");
   2.304 +
   2.305 +COMMENT ON TABLE "voting_comment" IS 'Storage for comments of voters to be published after voting has finished.';
   2.306 +
   2.307 +COMMENT ON COLUMN "voting_comment"."changed" IS 'Is to be set or updated by the frontend, if comment was inserted or updated AFTER the issue has been closed. Otherwise it shall be set to NULL.';
   2.308 +
   2.309 +
   2.310 +-- New table "rendered_voting_comment":
   2.311 +
   2.312 +CREATE TABLE "rendered_voting_comment" (
   2.313 +        PRIMARY KEY ("issue_id", "member_id", "format"),
   2.314 +        FOREIGN KEY ("issue_id", "member_id")
   2.315 +          REFERENCES "voting_comment" ("issue_id", "member_id")
   2.316 +          ON DELETE CASCADE ON UPDATE CASCADE,
   2.317 +        "issue_id"              INT4,
   2.318 +        "member_id"             INT4,
   2.319 +        "format"                TEXT,
   2.320 +        "content"               TEXT            NOT NULL );
   2.321 +
   2.322 +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)';
   2.323 +
   2.324 +
   2.325 +-- New table "event":
   2.326 +
   2.327 +CREATE TYPE "event_type" AS ENUM (
   2.328 +        'issue_state_changed',
   2.329 +        'initiative_created_in_new_issue',
   2.330 +        'initiative_created_in_existing_issue',
   2.331 +        'initiative_revoked',
   2.332 +        'new_draft_created',
   2.333 +        'suggestion_created');
   2.334 +
   2.335 +COMMENT ON TYPE "event_type" IS 'Type used for column "event" of table "event"';
   2.336 +
   2.337 +CREATE TABLE "event" (
   2.338 +        "id"                    SERIAL8         PRIMARY KEY,
   2.339 +        "occurrence"            TIMESTAMPTZ     NOT NULL DEFAULT now(),
   2.340 +        "event"                 "event_type"    NOT NULL,
   2.341 +        "member_id"             INT4            REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
   2.342 +        "issue_id"              INT4            REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
   2.343 +        "state"                 "issue_state"   CHECK ("state" != 'calculation'),
   2.344 +        "initiative_id"         INT4,
   2.345 +        "draft_id"              INT8,
   2.346 +        "suggestion_id"         INT8,
   2.347 +        FOREIGN KEY ("issue_id", "initiative_id")
   2.348 +          REFERENCES "initiative" ("issue_id", "id")
   2.349 +          ON DELETE CASCADE ON UPDATE CASCADE,
   2.350 +        FOREIGN KEY ("initiative_id", "draft_id")
   2.351 +          REFERENCES "draft" ("initiative_id", "id")
   2.352 +          ON DELETE CASCADE ON UPDATE CASCADE,
   2.353 +        FOREIGN KEY ("initiative_id", "suggestion_id")
   2.354 +          REFERENCES "suggestion" ("initiative_id", "id")
   2.355 +          ON DELETE CASCADE ON UPDATE CASCADE,
   2.356 +        CONSTRAINT "null_constraints_for_issue_state_changed" CHECK (
   2.357 +          "event" != 'issue_state_changed' OR (
   2.358 +            "member_id"     ISNULL  AND
   2.359 +            "issue_id"      NOTNULL AND
   2.360 +            "state"         NOTNULL AND
   2.361 +            "initiative_id" ISNULL  AND
   2.362 +            "draft_id"      ISNULL  AND
   2.363 +            "suggestion_id" ISNULL  )),
   2.364 +        CONSTRAINT "null_constraints_for_initiative_creation_or_revocation_or_new_draft" CHECK (
   2.365 +          "event" NOT IN (
   2.366 +            'initiative_created_in_new_issue',
   2.367 +            'initiative_created_in_existing_issue',
   2.368 +            'initiative_revoked',
   2.369 +            'new_draft_created'
   2.370 +          ) OR (
   2.371 +            "member_id"     NOTNULL AND
   2.372 +            "issue_id"      NOTNULL AND
   2.373 +            "state"         NOTNULL AND
   2.374 +            "initiative_id" NOTNULL AND
   2.375 +            "draft_id"      NOTNULL AND
   2.376 +            "suggestion_id" ISNULL  )),
   2.377 +        CONSTRAINT "null_constraints_for_suggestion_creation" CHECK (
   2.378 +          "event" != 'suggestion_created' OR (
   2.379 +            "member_id"     NOTNULL AND
   2.380 +            "issue_id"      NOTNULL AND
   2.381 +            "state"         NOTNULL AND
   2.382 +            "initiative_id" NOTNULL AND
   2.383 +            "draft_id"      ISNULL  AND
   2.384 +            "suggestion_id" NOTNULL )) );
   2.385 +
   2.386 +COMMENT ON TABLE "event" IS 'Event table, automatically filled by triggers';
   2.387 +
   2.388 +COMMENT ON COLUMN "event"."occurrence" IS 'Point in time, when event occurred';
   2.389 +COMMENT ON COLUMN "event"."event"      IS 'Type of event (see TYPE "event_type")';
   2.390 +COMMENT ON COLUMN "event"."member_id"  IS 'Member who caused the event, if applicable';
   2.391 +COMMENT ON COLUMN "event"."state"      IS 'If issue_id is set: state of affected issue; If state changed: new state';
   2.392 +
   2.393 +
   2.394 +-- Triggers to fill "event" table:
   2.395 +
   2.396 +CREATE FUNCTION "write_event_issue_state_changed_trigger"()
   2.397 +  RETURNS TRIGGER
   2.398 +  LANGUAGE 'plpgsql' VOLATILE AS $$
   2.399 +    BEGIN
   2.400 +      IF NEW."state" != OLD."state" AND NEW."state" != 'calculation' THEN
   2.401 +        INSERT INTO "event" ("event", "issue_id", "state")
   2.402 +          VALUES ('issue_state_changed', NEW."id", NEW."state");
   2.403 +      END IF;
   2.404 +      RETURN NULL;
   2.405 +    END;
   2.406 +  $$;
   2.407 +
   2.408 +CREATE TRIGGER "write_event_issue_state_changed"
   2.409 +  AFTER UPDATE ON "issue" FOR EACH ROW EXECUTE PROCEDURE
   2.410 +  "write_event_issue_state_changed_trigger"();
   2.411 +
   2.412 +COMMENT ON FUNCTION "write_event_issue_state_changed_trigger"() IS 'Implementation of trigger "write_event_issue_state_changed" on table "issue"';
   2.413 +COMMENT ON TRIGGER "write_event_issue_state_changed" ON "issue" IS 'Create entry in "event" table on "state" change';
   2.414 +
   2.415 +CREATE FUNCTION "write_event_initiative_or_draft_created_trigger"()
   2.416 +  RETURNS TRIGGER
   2.417 +  LANGUAGE 'plpgsql' VOLATILE AS $$
   2.418 +    DECLARE
   2.419 +      "initiative_row" "initiative"%ROWTYPE;
   2.420 +      "issue_row"      "issue"%ROWTYPE;
   2.421 +      "event_v"        "event_type";
   2.422 +    BEGIN
   2.423 +      SELECT * INTO "initiative_row" FROM "initiative"
   2.424 +        WHERE "id" = NEW."initiative_id";
   2.425 +      SELECT * INTO "issue_row" FROM "issue"
   2.426 +        WHERE "id" = "initiative_row"."issue_id";
   2.427 +      IF EXISTS (
   2.428 +        SELECT NULL FROM "draft"
   2.429 +        WHERE "initiative_id" = NEW."initiative_id"
   2.430 +        AND "id" != NEW."id"
   2.431 +      ) THEN
   2.432 +        "event_v" := 'new_draft_created';
   2.433 +      ELSE
   2.434 +        IF EXISTS (
   2.435 +          SELECT NULL FROM "initiative"
   2.436 +          WHERE "issue_id" = "initiative_row"."issue_id"
   2.437 +          AND "id" != "initiative_row"."id"
   2.438 +        ) THEN
   2.439 +          "event_v" := 'initiative_created_in_existing_issue';
   2.440 +        ELSE
   2.441 +          "event_v" := 'initiative_created_in_new_issue';
   2.442 +        END IF;
   2.443 +      END IF;
   2.444 +      INSERT INTO "event" (
   2.445 +          "event", "member_id",
   2.446 +          "issue_id", "state", "initiative_id", "draft_id"
   2.447 +        ) VALUES (
   2.448 +          "event_v",
   2.449 +          NEW."author_id",
   2.450 +          "initiative_row"."issue_id",
   2.451 +          "issue_row"."state",
   2.452 +          "initiative_row"."id",
   2.453 +          NEW."id" );
   2.454 +      RETURN NULL;
   2.455 +    END;
   2.456 +  $$;
   2.457 +
   2.458 +CREATE TRIGGER "write_event_initiative_or_draft_created"
   2.459 +  AFTER INSERT ON "draft" FOR EACH ROW EXECUTE PROCEDURE
   2.460 +  "write_event_initiative_or_draft_created_trigger"();
   2.461 +
   2.462 +COMMENT ON FUNCTION "write_event_initiative_or_draft_created_trigger"() IS 'Implementation of trigger "write_event_initiative_or_draft_created" on table "issue"';
   2.463 +COMMENT ON TRIGGER "write_event_initiative_or_draft_created" ON "draft" IS 'Create entry in "event" table on draft creation';
   2.464 +
   2.465 +CREATE FUNCTION "write_event_initiative_revoked_trigger"()
   2.466 +  RETURNS TRIGGER
   2.467 +  LANGUAGE 'plpgsql' VOLATILE AS $$
   2.468 +    DECLARE
   2.469 +      "issue_row"      "issue"%ROWTYPE;
   2.470 +    BEGIN
   2.471 +      SELECT * INTO "issue_row" FROM "issue"
   2.472 +        WHERE "id" = NEW."issue_id";
   2.473 +      IF OLD."revoked" ISNULL AND NEW."revoked" NOTNULL THEN
   2.474 +        INSERT INTO "event" (
   2.475 +            "event", "member_id", "issue_id", "state", "initiative_id"
   2.476 +          ) VALUES (
   2.477 +            'initiative_revoked',
   2.478 +            NEW."revoked_by_member_id",
   2.479 +            NEW."issue_id",
   2.480 +            "issue_row"."state",
   2.481 +            NEW."id" );
   2.482 +      END IF;
   2.483 +      RETURN NULL;
   2.484 +    END;
   2.485 +  $$;
   2.486 +
   2.487 +CREATE TRIGGER "write_event_initiative_revoked"
   2.488 +  AFTER UPDATE ON "initiative" FOR EACH ROW EXECUTE PROCEDURE
   2.489 +  "write_event_initiative_revoked_trigger"();
   2.490 +
   2.491 +COMMENT ON FUNCTION "write_event_initiative_revoked_trigger"()      IS 'Implementation of trigger "write_event_initiative_revoked" on table "issue"';
   2.492 +COMMENT ON TRIGGER "write_event_initiative_revoked" ON "initiative" IS 'Create entry in "event" table, when an initiative is revoked';
   2.493 +
   2.494 +CREATE FUNCTION "write_event_suggestion_created_trigger"()
   2.495 +  RETURNS TRIGGER
   2.496 +  LANGUAGE 'plpgsql' VOLATILE AS $$
   2.497 +    DECLARE
   2.498 +      "initiative_row" "initiative"%ROWTYPE;
   2.499 +      "issue_row"      "issue"%ROWTYPE;
   2.500 +    BEGIN
   2.501 +      SELECT * INTO "initiative_row" FROM "initiative"
   2.502 +        WHERE "id" = NEW."initiative_id";
   2.503 +      SELECT * INTO "issue_row" FROM "issue"
   2.504 +        WHERE "id" = "initiative_row"."issue_id";
   2.505 +      INSERT INTO "event" (
   2.506 +          "event", "member_id",
   2.507 +          "issue_id", "state", "initiative_id", "suggestion_id"
   2.508 +        ) VALUES (
   2.509 +          'suggestion_created',
   2.510 +          NEW."author_id",
   2.511 +          "initiative_row"."issue_id",
   2.512 +          "issue_row"."state",
   2.513 +          "initiative_row"."id",
   2.514 +          NEW."id" );
   2.515 +      RETURN NULL;
   2.516 +    END;
   2.517 +  $$;
   2.518 +
   2.519 +CREATE TRIGGER "write_event_suggestion_created"
   2.520 +  AFTER INSERT ON "suggestion" FOR EACH ROW EXECUTE PROCEDURE
   2.521 +  "write_event_suggestion_created_trigger"();
   2.522 +
   2.523 +COMMENT ON FUNCTION "write_event_suggestion_created_trigger"()      IS 'Implementation of trigger "write_event_suggestion_created" on table "issue"';
   2.524 +COMMENT ON TRIGGER "write_event_suggestion_created" ON "suggestion" IS 'Create entry in "event" table on suggestion creation';
   2.525 +
   2.526 +
   2.527 +-- Modified views:
   2.528 +
   2.529 +CREATE VIEW "unit_delegation" AS
   2.530 +  SELECT
   2.531 +    "unit"."id" AS "unit_id",
   2.532 +    "delegation"."id",
   2.533 +    "delegation"."truster_id",
   2.534 +    "delegation"."trustee_id",
   2.535 +    "delegation"."scope"
   2.536 +  FROM "unit"
   2.537 +  JOIN "delegation"
   2.538 +    ON "delegation"."unit_id" = "unit"."id"
   2.539 +  JOIN "member"
   2.540 +    ON "delegation"."truster_id" = "member"."id"
   2.541 +  JOIN "privilege"
   2.542 +    ON "delegation"."unit_id" = "privilege"."unit_id"
   2.543 +    AND "delegation"."truster_id" = "privilege"."member_id"
   2.544 +  WHERE "member"."active" AND "privilege"."voting_right";
   2.545 +
   2.546 +COMMENT ON VIEW "unit_delegation" IS 'Unit delegations where trusters are active and have voting right';
   2.547 +
   2.548 +CREATE VIEW "area_delegation" AS
   2.549 +  SELECT DISTINCT ON ("area"."id", "delegation"."truster_id")
   2.550 +    "area"."id" AS "area_id",
   2.551 +    "delegation"."id",
   2.552 +    "delegation"."truster_id",
   2.553 +    "delegation"."trustee_id",
   2.554 +    "delegation"."scope"
   2.555 +  FROM "area"
   2.556 +  JOIN "delegation"
   2.557 +    ON "delegation"."unit_id" = "area"."unit_id"
   2.558 +    OR "delegation"."area_id" = "area"."id"
   2.559 +  JOIN "member"
   2.560 +    ON "delegation"."truster_id" = "member"."id"
   2.561 +  JOIN "privilege"
   2.562 +    ON "area"."unit_id" = "privilege"."unit_id"
   2.563 +    AND "delegation"."truster_id" = "privilege"."member_id"
   2.564 +  WHERE "member"."active" AND "privilege"."voting_right"
   2.565 +  ORDER BY
   2.566 +    "area"."id",
   2.567 +    "delegation"."truster_id",
   2.568 +    "delegation"."scope" DESC;
   2.569 +
   2.570 +COMMENT ON VIEW "area_delegation" IS 'Area delegations where trusters are active and have voting right';
   2.571 +
   2.572 +CREATE VIEW "issue_delegation" AS
   2.573 +  SELECT DISTINCT ON ("issue"."id", "delegation"."truster_id")
   2.574 +    "issue"."id" AS "issue_id",
   2.575 +    "delegation"."id",
   2.576 +    "delegation"."truster_id",
   2.577 +    "delegation"."trustee_id",
   2.578 +    "delegation"."scope"
   2.579 +  FROM "issue"
   2.580 +  JOIN "area"
   2.581 +    ON "area"."id" = "issue"."area_id"
   2.582 +  JOIN "delegation"
   2.583 +    ON "delegation"."unit_id" = "area"."unit_id"
   2.584 +    OR "delegation"."area_id" = "area"."id"
   2.585 +    OR "delegation"."issue_id" = "issue"."id"
   2.586 +  JOIN "member"
   2.587 +    ON "delegation"."truster_id" = "member"."id"
   2.588 +  JOIN "privilege"
   2.589 +    ON "area"."unit_id" = "privilege"."unit_id"
   2.590 +    AND "delegation"."truster_id" = "privilege"."member_id"
   2.591 +  WHERE "member"."active" AND "privilege"."voting_right"
   2.592 +  ORDER BY
   2.593 +    "issue"."id",
   2.594 +    "delegation"."truster_id",
   2.595 +    "delegation"."scope" DESC;
   2.596 +
   2.597 +COMMENT ON VIEW "issue_delegation" IS 'Issue delegations where trusters are active and have voting right';
   2.598 +
   2.599 +CREATE VIEW "unit_member_count" AS
   2.600 +  SELECT
   2.601 +    "unit"."id" AS "unit_id",
   2.602 +    sum("member"."id") AS "member_count"
   2.603 +  FROM "unit"
   2.604 +  LEFT JOIN "privilege"
   2.605 +  ON "privilege"."unit_id" = "unit"."id" 
   2.606 +  AND "privilege"."voting_right"
   2.607 +  LEFT JOIN "member"
   2.608 +  ON "member"."id" = "privilege"."member_id"
   2.609 +  AND "member"."active"
   2.610 +  GROUP BY "unit"."id";
   2.611 +
   2.612 +COMMENT ON VIEW "unit_member_count" IS 'View used to update "member_count" column of "unit" table';
   2.613 +
   2.614 +DROP VIEW "area_member_count";
   2.615 +CREATE VIEW "area_member_count" AS
   2.616 +  SELECT
   2.617 +    "area"."id" AS "area_id",
   2.618 +    count("member"."id") AS "direct_member_count",
   2.619 +    coalesce(
   2.620 +      sum(
   2.621 +        CASE WHEN "member"."id" NOTNULL THEN
   2.622 +          "membership_weight"("area"."id", "member"."id")
   2.623 +        ELSE 0 END
   2.624 +      )
   2.625 +    ) AS "member_weight",
   2.626 +    coalesce(
   2.627 +      sum(
   2.628 +        CASE WHEN "member"."id" NOTNULL AND "membership"."autoreject" THEN
   2.629 +          "membership_weight"("area"."id", "member"."id")
   2.630 +        ELSE 0 END
   2.631 +      )
   2.632 +    ) AS "autoreject_weight"
   2.633 +  FROM "area"
   2.634 +  LEFT JOIN "membership"
   2.635 +  ON "area"."id" = "membership"."area_id"
   2.636 +  LEFT JOIN "privilege"
   2.637 +  ON "privilege"."unit_id" = "area"."unit_id"
   2.638 +  AND "privilege"."member_id" = "membership"."member_id"
   2.639 +  AND "privilege"."voting_right"
   2.640 +  LEFT JOIN "member"
   2.641 +  ON "member"."id" = "privilege"."member_id"  -- NOTE: no membership here!
   2.642 +  AND "member"."active"
   2.643 +  GROUP BY "area"."id";
   2.644 +
   2.645 +COMMENT ON VIEW "area_member_count" IS 'View used to update "direct_member_count", "member_weight" and "autoreject_weight" columns of table "area"';
   2.646 +
   2.647 +
   2.648 +-- New view "event_seen_by_member":
   2.649 +
   2.650 +CREATE VIEW "event_seen_by_member" AS
   2.651 +  SELECT
   2.652 +    "member"."id" AS "seen_by_member_id",
   2.653 +    CASE WHEN "event"."state" IN (
   2.654 +      'voting',
   2.655 +      'finished_without_winner',
   2.656 +      'finished_with_winner'
   2.657 +    ) THEN
   2.658 +      'voting'::"notify_level"
   2.659 +    ELSE
   2.660 +      CASE WHEN "event"."state" IN (
   2.661 +        'verification',
   2.662 +        'canceled_after_revocation_during_verification',
   2.663 +        'canceled_no_initiative_admitted'
   2.664 +      ) THEN
   2.665 +        'verification'::"notify_level"
   2.666 +      ELSE
   2.667 +        CASE WHEN "event"."state" IN (
   2.668 +          'discussion',
   2.669 +          'canceled_after_revocation_during_discussion'
   2.670 +        ) THEN
   2.671 +          'discussion'::"notify_level"
   2.672 +        ELSE
   2.673 +          'all'::"notify_level"
   2.674 +        END
   2.675 +      END
   2.676 +    END AS "notify_level",
   2.677 +    "event".*
   2.678 +  FROM "member" CROSS JOIN "event"
   2.679 +  LEFT JOIN "issue"
   2.680 +    ON "event"."issue_id" = "issue"."id"
   2.681 +  LEFT JOIN "membership"
   2.682 +    ON "member"."id" = "membership"."member_id"
   2.683 +    AND "issue"."area_id" = "membership"."area_id"
   2.684 +  LEFT JOIN "interest"
   2.685 +    ON "member"."id" = "interest"."member_id"
   2.686 +    AND "event"."issue_id" = "interest"."issue_id"
   2.687 +  LEFT JOIN "supporter"
   2.688 +    ON "member"."id" = "supporter"."member_id"
   2.689 +    AND "event"."initiative_id" = "supporter"."initiative_id"
   2.690 +  LEFT JOIN "ignored_member"
   2.691 +    ON "member"."id" = "ignored_member"."member_id"
   2.692 +    AND "event"."member_id" = "ignored_member"."other_member_id"
   2.693 +  LEFT JOIN "ignored_initiative"
   2.694 +    ON "member"."id" = "ignored_initiative"."member_id"
   2.695 +    AND "event"."initiative_id" = "ignored_initiative"."initiative_id"
   2.696 +  WHERE (
   2.697 +    "supporter"."member_id" NOTNULL OR
   2.698 +    "interest"."member_id" NOTNULL OR
   2.699 +    ( "membership"."member_id" NOTNULL AND
   2.700 +      "event"."event" IN (
   2.701 +        'issue_state_changed',
   2.702 +        'initiative_created_in_new_issue',
   2.703 +        'initiative_created_in_existing_issue',
   2.704 +        'initiative_revoked' ) ) )
   2.705 +  AND "ignored_member"."member_id" ISNULL
   2.706 +  AND "ignored_initiative"."member_id" ISNULL;
   2.707 +
   2.708 +COMMENT ON VIEW "event_seen_by_member" IS 'Events as seen by a member, depending on its memberships, interests and support';
   2.709 +
   2.710 +
   2.711 +-- New view "pending_notification":
   2.712 +
   2.713 +CREATE VIEW "pending_notification" AS
   2.714 +  SELECT
   2.715 +    "member"."id" AS "seen_by_member_id",
   2.716 +    "event".*
   2.717 +  FROM "member" CROSS JOIN "event"
   2.718 +  LEFT JOIN "issue"
   2.719 +    ON "event"."issue_id" = "issue"."id"
   2.720 +  LEFT JOIN "membership"
   2.721 +    ON "member"."id" = "membership"."member_id"
   2.722 +    AND "issue"."area_id" = "membership"."area_id"
   2.723 +  LEFT JOIN "interest"
   2.724 +    ON "member"."id" = "interest"."member_id"
   2.725 +    AND "event"."issue_id" = "interest"."issue_id"
   2.726 +  LEFT JOIN "supporter"
   2.727 +    ON "member"."id" = "supporter"."member_id"
   2.728 +    AND "event"."initiative_id" = "supporter"."initiative_id"
   2.729 +  LEFT JOIN "ignored_member"
   2.730 +    ON "member"."id" = "ignored_member"."member_id"
   2.731 +    AND "event"."member_id" = "ignored_member"."other_member_id"
   2.732 +  LEFT JOIN "ignored_initiative"
   2.733 +    ON "member"."id" = "ignored_initiative"."member_id"
   2.734 +    AND "event"."initiative_id" = "ignored_initiative"."initiative_id"
   2.735 +  WHERE (
   2.736 +    "member"."notify_event_id" ISNULL OR
   2.737 +    ( "member"."notify_event_id" NOTNULL AND
   2.738 +      "member"."notify_event_id" < "event"."id" ) )
   2.739 +  AND (
   2.740 +    ( "member"."notify_level" >= 'all' ) OR
   2.741 +    ( "member"."notify_level" >= 'voting' AND
   2.742 +      "event"."state" IN (
   2.743 +        'voting',
   2.744 +        'finished_without_winner',
   2.745 +        'finished_with_winner' ) ) OR
   2.746 +    ( "member"."notify_level" >= 'verification' AND
   2.747 +      "event"."state" IN (
   2.748 +        'verification',
   2.749 +        'canceled_after_revocation_during_verification',
   2.750 +        'canceled_no_initiative_admitted' ) ) OR
   2.751 +    ( "member"."notify_level" >= 'discussion' AND
   2.752 +      "event"."state" IN (
   2.753 +        'discussion',
   2.754 +        'canceled_after_revocation_during_discussion' ) ) )
   2.755 +  AND (
   2.756 +    "supporter"."member_id" NOTNULL OR
   2.757 +    "interest"."member_id" NOTNULL OR
   2.758 +    ( "membership"."member_id" NOTNULL AND
   2.759 +      "event"."event" IN (
   2.760 +        'issue_state_changed',
   2.761 +        'initiative_created_in_new_issue',
   2.762 +        'initiative_created_in_existing_issue',
   2.763 +        'initiative_revoked' ) ) )
   2.764 +  AND "ignored_member"."member_id" ISNULL
   2.765 +  AND "ignored_initiative"."member_id" ISNULL;
   2.766 +
   2.767 +COMMENT ON VIEW "pending_notification" IS 'Events to be sent to "notify_email" address of member referred to by "seen_by_member_id"';
   2.768 +
   2.769 +
   2.770 +COMMENT ON TYPE "timeline_event" IS 'Types of event in timeline tables (DEPRECATED)';
   2.771 +COMMENT ON VIEW "timeline_issue" IS 'Helper view for "timeline" view (DEPRECATED)';
   2.772 +COMMENT ON VIEW "timeline_initiative" IS 'Helper view for "timeline" view (DEPRECATED)';
   2.773 +COMMENT ON VIEW "timeline_draft" IS 'Helper view for "timeline" view (DEPRECATED)';
   2.774 +COMMENT ON VIEW "timeline_suggestion" IS 'Helper view for "timeline" view (DEPRECATED)';
   2.775 +COMMENT ON VIEW "timeline" IS 'Aggregation of different events in the system (DEPRECATED)';
   2.776 +
   2.777 +
   2.778 +-- Modified "delegation_chain" functions:
   2.779 +
   2.780 +CREATE TYPE "delegation_chain_row" AS (
   2.781 +        "index"                 INT4,
   2.782 +        "member_id"             INT4,
   2.783 +        "member_valid"          BOOLEAN,
   2.784 +        "participation"         BOOLEAN,
   2.785 +        "overridden"            BOOLEAN,
   2.786 +        "scope_in"              "delegation_scope",
   2.787 +        "scope_out"             "delegation_scope",
   2.788 +        "disabled_out"          BOOLEAN,
   2.789 +        "loop"                  "delegation_chain_loop_tag" );
   2.790 +
   2.791 +COMMENT ON TYPE "delegation_chain_row" IS 'Type of rows returned by "delegation_chain"(...) functions';
   2.792 +
   2.793 +COMMENT ON COLUMN "delegation_chain_row"."index"         IS 'Index starting with 0 and counting up';
   2.794 +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';
   2.795 +COMMENT ON COLUMN "delegation_chain_row"."overridden"    IS 'True, if an entry with lower index has "participation" set to true';
   2.796 +COMMENT ON COLUMN "delegation_chain_row"."scope_in"      IS 'Scope of used incoming delegation';
   2.797 +COMMENT ON COLUMN "delegation_chain_row"."scope_out"     IS 'Scope of used outgoing delegation';
   2.798 +COMMENT ON COLUMN "delegation_chain_row"."disabled_out"  IS 'Outgoing delegation is explicitly disabled by a delegation with trustee_id set to NULL';
   2.799 +COMMENT ON COLUMN "delegation_chain_row"."loop"          IS 'Not null, if member is part of a loop, see "delegation_chain_loop_tag" type';
   2.800 +
   2.801 +
   2.802 +CREATE FUNCTION "delegation_chain"
   2.803 +  ( "member_id_p"           "member"."id"%TYPE,
   2.804 +    "unit_id_p"             "unit"."id"%TYPE,
   2.805 +    "area_id_p"             "area"."id"%TYPE,
   2.806 +    "issue_id_p"            "issue"."id"%TYPE,
   2.807 +    "simulate_trustee_id_p" "member"."id"%TYPE )
   2.808 +  RETURNS SETOF "delegation_chain_row"
   2.809 +  LANGUAGE 'plpgsql' STABLE AS $$
   2.810 +    DECLARE
   2.811 +      "scope_v"            "delegation_scope";
   2.812 +      "unit_id_v"          "unit"."id"%TYPE;
   2.813 +      "area_id_v"          "area"."id"%TYPE;
   2.814 +      "visited_member_ids" INT4[];  -- "member"."id"%TYPE[]
   2.815 +      "loop_member_id_v"   "member"."id"%TYPE;
   2.816 +      "output_row"         "delegation_chain_row";
   2.817 +      "output_rows"        "delegation_chain_row"[];
   2.818 +      "delegation_row"     "delegation"%ROWTYPE;
   2.819 +      "row_count"          INT4;
   2.820 +      "i"                  INT4;
   2.821 +      "loop_v"             BOOLEAN;
   2.822 +    BEGIN
   2.823 +      IF
   2.824 +        "unit_id_p" NOTNULL AND
   2.825 +        "area_id_p" ISNULL AND
   2.826 +        "issue_id_p" ISNULL
   2.827 +      THEN
   2.828 +        "scope_v" := 'unit';
   2.829 +        "unit_id_v" := "unit_id_p";
   2.830 +      ELSIF
   2.831 +        "unit_id_p" ISNULL AND
   2.832 +        "area_id_p" NOTNULL AND
   2.833 +        "issue_id_p" ISNULL
   2.834 +      THEN
   2.835 +        "scope_v" := 'area';
   2.836 +        "area_id_v" := "area_id_p";
   2.837 +        SELECT "unit_id" INTO "unit_id_v"
   2.838 +          FROM "area" WHERE "id" = "area_id_v";
   2.839 +      ELSIF
   2.840 +        "unit_id_p" ISNULL AND
   2.841 +        "area_id_p" ISNULL AND
   2.842 +        "issue_id_p" NOTNULL
   2.843 +      THEN
   2.844 +        "scope_v" := 'issue';
   2.845 +        SELECT "area_id" INTO "area_id_v"
   2.846 +          FROM "issue" WHERE "id" = "issue_id_p";
   2.847 +        SELECT "unit_id" INTO "unit_id_v"
   2.848 +          FROM "area"  WHERE "id" = "area_id_v";
   2.849 +      ELSE
   2.850 +        RAISE EXCEPTION 'Exactly one of unit_id_p, area_id_p, or issue_id_p must be NOTNULL.';
   2.851 +      END IF;
   2.852 +      "visited_member_ids" := '{}';
   2.853 +      "loop_member_id_v"   := NULL;
   2.854 +      "output_rows"        := '{}';
   2.855 +      "output_row"."index"         := 0;
   2.856 +      "output_row"."member_id"     := "member_id_p";
   2.857 +      "output_row"."member_valid"  := TRUE;
   2.858 +      "output_row"."participation" := FALSE;
   2.859 +      "output_row"."overridden"    := FALSE;
   2.860 +      "output_row"."disabled_out"  := FALSE;
   2.861 +      "output_row"."scope_out"     := NULL;
   2.862 +      LOOP
   2.863 +        IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN
   2.864 +          "loop_member_id_v" := "output_row"."member_id";
   2.865 +        ELSE
   2.866 +          "visited_member_ids" :=
   2.867 +            "visited_member_ids" || "output_row"."member_id";
   2.868 +        END IF;
   2.869 +        IF "output_row"."participation" THEN
   2.870 +          "output_row"."overridden" := TRUE;
   2.871 +        END IF;
   2.872 +        "output_row"."scope_in" := "output_row"."scope_out";
   2.873 +        IF EXISTS (
   2.874 +          SELECT NULL FROM "member" JOIN "privilege"
   2.875 +          ON "privilege"."member_id" = "member"."id"
   2.876 +          AND "privilege"."unit_id" = "unit_id_v"
   2.877 +          WHERE "id" = "output_row"."member_id"
   2.878 +          AND "member"."active" AND "privilege"."voting_right"
   2.879 +        ) THEN
   2.880 +          IF "scope_v" = 'unit' THEN
   2.881 +            SELECT * INTO "delegation_row" FROM "delegation"
   2.882 +              WHERE "truster_id" = "output_row"."member_id"
   2.883 +              AND "unit_id" = "unit_id_v";
   2.884 +          ELSIF "scope_v" = 'area' THEN
   2.885 +            "output_row"."participation" := EXISTS (
   2.886 +              SELECT NULL FROM "membership"
   2.887 +              WHERE "area_id" = "area_id_p"
   2.888 +              AND "member_id" = "output_row"."member_id"
   2.889 +            );
   2.890 +            SELECT * INTO "delegation_row" FROM "delegation"
   2.891 +              WHERE "truster_id" = "output_row"."member_id"
   2.892 +              AND (
   2.893 +                "unit_id" = "unit_id_v" OR
   2.894 +                "area_id" = "area_id_v"
   2.895 +              )
   2.896 +              ORDER BY "scope" DESC;
   2.897 +          ELSIF "scope_v" = 'issue' THEN
   2.898 +            "output_row"."participation" := EXISTS (
   2.899 +              SELECT NULL FROM "interest"
   2.900 +              WHERE "issue_id" = "issue_id_p"
   2.901 +              AND "member_id" = "output_row"."member_id"
   2.902 +            );
   2.903 +            SELECT * INTO "delegation_row" FROM "delegation"
   2.904 +              WHERE "truster_id" = "output_row"."member_id"
   2.905 +              AND (
   2.906 +                "unit_id" = "unit_id_v" OR
   2.907 +                "area_id" = "area_id_v" OR
   2.908 +                "issue_id" = "issue_id_p"
   2.909 +              )
   2.910 +              ORDER BY "scope" DESC;
   2.911 +          END IF;
   2.912 +        ELSE
   2.913 +          "output_row"."member_valid"  := FALSE;
   2.914 +          "output_row"."participation" := FALSE;
   2.915 +          "output_row"."scope_out"     := NULL;
   2.916 +          "delegation_row" := ROW(NULL);
   2.917 +        END IF;
   2.918 +        IF
   2.919 +          "output_row"."member_id" = "member_id_p" AND
   2.920 +          "simulate_trustee_id_p" NOTNULL
   2.921 +        THEN
   2.922 +          "output_row"."scope_out" := "scope_v";
   2.923 +          "output_rows" := "output_rows" || "output_row";
   2.924 +          "output_row"."member_id" := "simulate_trustee_id_p";
   2.925 +        ELSIF "delegation_row"."trustee_id" NOTNULL THEN
   2.926 +          "output_row"."scope_out" := "delegation_row"."scope";
   2.927 +          "output_rows" := "output_rows" || "output_row";
   2.928 +          "output_row"."member_id" := "delegation_row"."trustee_id";
   2.929 +        ELSIF "delegation_row"."scope" NOTNULL THEN
   2.930 +          "output_row"."scope_out" := "delegation_row"."scope";
   2.931 +          "output_row"."disabled_out" := TRUE;
   2.932 +          "output_rows" := "output_rows" || "output_row";
   2.933 +          EXIT;
   2.934 +        ELSE
   2.935 +          "output_row"."scope_out" := NULL;
   2.936 +          "output_rows" := "output_rows" || "output_row";
   2.937 +          EXIT;
   2.938 +        END IF;
   2.939 +        EXIT WHEN "loop_member_id_v" NOTNULL;
   2.940 +        "output_row"."index" := "output_row"."index" + 1;
   2.941 +      END LOOP;
   2.942 +      "row_count" := array_upper("output_rows", 1);
   2.943 +      "i"      := 1;
   2.944 +      "loop_v" := FALSE;
   2.945 +      LOOP
   2.946 +        "output_row" := "output_rows"["i"];
   2.947 +        EXIT WHEN "output_row" ISNULL;  -- NOTE: ISNULL and NOT ... NOTNULL produce different results!
   2.948 +        IF "loop_v" THEN
   2.949 +          IF "i" + 1 = "row_count" THEN
   2.950 +            "output_row"."loop" := 'last';
   2.951 +          ELSIF "i" = "row_count" THEN
   2.952 +            "output_row"."loop" := 'repetition';
   2.953 +          ELSE
   2.954 +            "output_row"."loop" := 'intermediate';
   2.955 +          END IF;
   2.956 +        ELSIF "output_row"."member_id" = "loop_member_id_v" THEN
   2.957 +          "output_row"."loop" := 'first';
   2.958 +          "loop_v" := TRUE;
   2.959 +        END IF;
   2.960 +        IF "scope_v" = 'unit' THEN
   2.961 +          "output_row"."participation" := NULL;
   2.962 +        END IF;
   2.963 +        RETURN NEXT "output_row";
   2.964 +        "i" := "i" + 1;
   2.965 +      END LOOP;
   2.966 +      RETURN;
   2.967 +    END;
   2.968 +  $$;
   2.969 +
   2.970 +COMMENT ON FUNCTION "delegation_chain"
   2.971 +  ( "member"."id"%TYPE,
   2.972 +    "unit"."id"%TYPE,
   2.973 +    "area"."id"%TYPE,
   2.974 +    "issue"."id"%TYPE,
   2.975 +    "member"."id"%TYPE )
   2.976 +  IS 'Helper function for frontends to display delegation chains; Not part of internal voting logic';
   2.977 +
   2.978 +
   2.979 +CREATE FUNCTION "delegation_chain"
   2.980 +  ( "member_id_p" "member"."id"%TYPE,
   2.981 +    "unit_id_p"   "unit"."id"%TYPE,
   2.982 +    "area_id_p"   "area"."id"%TYPE,
   2.983 +    "issue_id_p"  "issue"."id"%TYPE )
   2.984 +  RETURNS SETOF "delegation_chain_row"
   2.985 +  LANGUAGE 'plpgsql' STABLE AS $$
   2.986 +    DECLARE
   2.987 +      "result_row" "delegation_chain_row";
   2.988 +    BEGIN
   2.989 +      FOR "result_row" IN
   2.990 +        SELECT * FROM "delegation_chain"(
   2.991 +          "member_id_p", "area_id_p", "issue_id_p", NULL
   2.992 +        )
   2.993 +      LOOP
   2.994 +        RETURN NEXT "result_row";
   2.995 +      END LOOP;
   2.996 +      RETURN;
   2.997 +    END;
   2.998 +  $$;
   2.999 +
  2.1000 +COMMENT ON FUNCTION "delegation_chain"
  2.1001 +  ( "member"."id"%TYPE,
  2.1002 +    "unit"."id"%TYPE,
  2.1003 +    "area"."id"%TYPE,
  2.1004 +    "issue"."id"%TYPE )
  2.1005 +  IS 'Shortcut for "delegation_chain"(...) function where 4th parameter is null';
  2.1006 +
  2.1007 +
  2.1008 +-- Modified core functions:
  2.1009 +
  2.1010 +CREATE OR REPLACE FUNCTION "lock_issue"
  2.1011 +  ( "issue_id_p" "issue"."id"%TYPE )
  2.1012 +  RETURNS VOID
  2.1013 +  LANGUAGE 'plpgsql' VOLATILE AS $$
  2.1014 +    BEGIN
  2.1015 +      LOCK TABLE "member"     IN SHARE MODE;
  2.1016 +      LOCK TABLE "privilege"  IN SHARE MODE;
  2.1017 +      LOCK TABLE "membership" IN SHARE MODE;
  2.1018 +      LOCK TABLE "policy"     IN SHARE MODE;
  2.1019 +      PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE;
  2.1020 +      -- NOTE: The row-level exclusive lock in combination with the
  2.1021 +      -- share_row_lock_issue(_via_initiative)_trigger functions (which
  2.1022 +      -- acquire a row-level share lock on the issue) ensure that no data
  2.1023 +      -- is changed, which could affect calculation of snapshots or
  2.1024 +      -- counting of votes. Table "delegation" must be table-level-locked,
  2.1025 +      -- as it also contains issue- and global-scope delegations.
  2.1026 +      LOCK TABLE "delegation" IN SHARE MODE;
  2.1027 +      LOCK TABLE "direct_population_snapshot"     IN EXCLUSIVE MODE;
  2.1028 +      LOCK TABLE "delegating_population_snapshot" IN EXCLUSIVE MODE;
  2.1029 +      LOCK TABLE "direct_interest_snapshot"       IN EXCLUSIVE MODE;
  2.1030 +      LOCK TABLE "delegating_interest_snapshot"   IN EXCLUSIVE MODE;
  2.1031 +      LOCK TABLE "direct_supporter_snapshot"      IN EXCLUSIVE MODE;
  2.1032 +      RETURN;
  2.1033 +    END;
  2.1034 +  $$;
  2.1035 +
  2.1036 +CREATE OR REPLACE FUNCTION "calculate_member_counts"()
  2.1037 +  RETURNS VOID
  2.1038 +  LANGUAGE 'plpgsql' VOLATILE AS $$
  2.1039 +    BEGIN
  2.1040 +      LOCK TABLE "member"       IN SHARE MODE;
  2.1041 +      LOCK TABLE "member_count" IN EXCLUSIVE MODE;
  2.1042 +      LOCK TABLE "unit"         IN EXCLUSIVE MODE;
  2.1043 +      LOCK TABLE "area"         IN EXCLUSIVE MODE;
  2.1044 +      LOCK TABLE "privilege"    IN SHARE MODE;
  2.1045 +      LOCK TABLE "membership"   IN SHARE MODE;
  2.1046 +      DELETE FROM "member_count";
  2.1047 +      INSERT INTO "member_count" ("total_count")
  2.1048 +        SELECT "total_count" FROM "member_count_view";
  2.1049 +      UPDATE "unit" SET "member_count" = "view"."member_count"
  2.1050 +        FROM "unit_member_count" AS "view"
  2.1051 +        WHERE "view"."unit_id" = "unit"."id";
  2.1052 +      UPDATE "area" SET
  2.1053 +        "direct_member_count" = "view"."direct_member_count",
  2.1054 +        "member_weight"       = "view"."member_weight",
  2.1055 +        "autoreject_weight"   = "view"."autoreject_weight"
  2.1056 +        FROM "area_member_count" AS "view"
  2.1057 +        WHERE "view"."area_id" = "area"."id";
  2.1058 +      RETURN;
  2.1059 +    END;
  2.1060 +  $$;
  2.1061 +
  2.1062 +CREATE OR REPLACE FUNCTION "create_population_snapshot"
  2.1063 +  ( "issue_id_p" "issue"."id"%TYPE )
  2.1064 +  RETURNS VOID
  2.1065 +  LANGUAGE 'plpgsql' VOLATILE AS $$
  2.1066 +    DECLARE
  2.1067 +      "member_id_v" "member"."id"%TYPE;
  2.1068 +    BEGIN
  2.1069 +      DELETE FROM "direct_population_snapshot"
  2.1070 +        WHERE "issue_id" = "issue_id_p"
  2.1071 +        AND "event" = 'periodic';
  2.1072 +      DELETE FROM "delegating_population_snapshot"
  2.1073 +        WHERE "issue_id" = "issue_id_p"
  2.1074 +        AND "event" = 'periodic';
  2.1075 +      INSERT INTO "direct_population_snapshot"
  2.1076 +        ("issue_id", "event", "member_id")
  2.1077 +        SELECT
  2.1078 +          "issue_id_p"                 AS "issue_id",
  2.1079 +          'periodic'::"snapshot_event" AS "event",
  2.1080 +          "member"."id"                AS "member_id"
  2.1081 +        FROM "issue"
  2.1082 +        JOIN "area" ON "issue"."area_id" = "area"."id"
  2.1083 +        JOIN "membership" ON "area"."id" = "membership"."area_id"
  2.1084 +        JOIN "member" ON "membership"."member_id" = "member"."id"
  2.1085 +        JOIN "privilege"
  2.1086 +          ON "privilege"."unit_id" = "area"."unit_id"
  2.1087 +          AND "privilege"."member_id" = "member"."id"
  2.1088 +        WHERE "issue"."id" = "issue_id_p"
  2.1089 +        AND "member"."active" AND "privilege"."voting_right"
  2.1090 +        UNION
  2.1091 +        SELECT
  2.1092 +          "issue_id_p"                 AS "issue_id",
  2.1093 +          'periodic'::"snapshot_event" AS "event",
  2.1094 +          "member"."id"                AS "member_id"
  2.1095 +        FROM "issue"
  2.1096 +        JOIN "area" ON "issue"."area_id" = "area"."id"
  2.1097 +        JOIN "interest" ON "issue"."id" = "interest"."issue_id"
  2.1098 +        JOIN "member" ON "interest"."member_id" = "member"."id"
  2.1099 +        JOIN "privilege"
  2.1100 +          ON "privilege"."unit_id" = "area"."unit_id"
  2.1101 +          AND "privilege"."member_id" = "member"."id"
  2.1102 +        WHERE "issue"."id" = "issue_id_p"
  2.1103 +        AND "member"."active" AND "privilege"."voting_right";
  2.1104 +      FOR "member_id_v" IN
  2.1105 +        SELECT "member_id" FROM "direct_population_snapshot"
  2.1106 +        WHERE "issue_id" = "issue_id_p"
  2.1107 +        AND "event" = 'periodic'
  2.1108 +      LOOP
  2.1109 +        UPDATE "direct_population_snapshot" SET
  2.1110 +          "weight" = 1 +
  2.1111 +            "weight_of_added_delegations_for_population_snapshot"(
  2.1112 +              "issue_id_p",
  2.1113 +              "member_id_v",
  2.1114 +              '{}'
  2.1115 +            )
  2.1116 +          WHERE "issue_id" = "issue_id_p"
  2.1117 +          AND "event" = 'periodic'
  2.1118 +          AND "member_id" = "member_id_v";
  2.1119 +      END LOOP;
  2.1120 +      RETURN;
  2.1121 +    END;
  2.1122 +  $$;
  2.1123 +
  2.1124 +CREATE OR REPLACE FUNCTION "create_interest_snapshot"
  2.1125 +  ( "issue_id_p" "issue"."id"%TYPE )
  2.1126 +  RETURNS VOID
  2.1127 +  LANGUAGE 'plpgsql' VOLATILE AS $$
  2.1128 +    DECLARE
  2.1129 +      "member_id_v" "member"."id"%TYPE;
  2.1130 +    BEGIN
  2.1131 +      DELETE FROM "direct_interest_snapshot"
  2.1132 +        WHERE "issue_id" = "issue_id_p"
  2.1133 +        AND "event" = 'periodic';
  2.1134 +      DELETE FROM "delegating_interest_snapshot"
  2.1135 +        WHERE "issue_id" = "issue_id_p"
  2.1136 +        AND "event" = 'periodic';
  2.1137 +      DELETE FROM "direct_supporter_snapshot"
  2.1138 +        WHERE "issue_id" = "issue_id_p"
  2.1139 +        AND "event" = 'periodic';
  2.1140 +      INSERT INTO "direct_interest_snapshot"
  2.1141 +        ("issue_id", "event", "member_id", "voting_requested")
  2.1142 +        SELECT
  2.1143 +          "issue_id_p"  AS "issue_id",
  2.1144 +          'periodic'    AS "event",
  2.1145 +          "member"."id" AS "member_id",
  2.1146 +          "interest"."voting_requested"
  2.1147 +        FROM "issue"
  2.1148 +        JOIN "area" ON "issue"."area_id" = "area"."id"
  2.1149 +        JOIN "interest" ON "issue"."id" = "interest"."issue_id"
  2.1150 +        JOIN "member" ON "interest"."member_id" = "member"."id"
  2.1151 +        JOIN "privilege"
  2.1152 +          ON "privilege"."unit_id" = "area"."unit_id"
  2.1153 +          AND "privilege"."member_id" = "member"."id"
  2.1154 +        WHERE "issue"."id" = "issue_id_p"
  2.1155 +        AND "member"."active" AND "privilege"."voting_right";
  2.1156 +      FOR "member_id_v" IN
  2.1157 +        SELECT "member_id" FROM "direct_interest_snapshot"
  2.1158 +        WHERE "issue_id" = "issue_id_p"
  2.1159 +        AND "event" = 'periodic'
  2.1160 +      LOOP
  2.1161 +        UPDATE "direct_interest_snapshot" SET
  2.1162 +          "weight" = 1 +
  2.1163 +            "weight_of_added_delegations_for_interest_snapshot"(
  2.1164 +              "issue_id_p",
  2.1165 +              "member_id_v",
  2.1166 +              '{}'
  2.1167 +            )
  2.1168 +          WHERE "issue_id" = "issue_id_p"
  2.1169 +          AND "event" = 'periodic'
  2.1170 +          AND "member_id" = "member_id_v";
  2.1171 +      END LOOP;
  2.1172 +      INSERT INTO "direct_supporter_snapshot"
  2.1173 +        ( "issue_id", "initiative_id", "event", "member_id",
  2.1174 +          "informed", "satisfied" )
  2.1175 +        SELECT
  2.1176 +          "issue_id_p"            AS "issue_id",
  2.1177 +          "initiative"."id"       AS "initiative_id",
  2.1178 +          'periodic'              AS "event",
  2.1179 +          "supporter"."member_id" AS "member_id",
  2.1180 +          "supporter"."draft_id" = "current_draft"."id" AS "informed",
  2.1181 +          NOT EXISTS (
  2.1182 +            SELECT NULL FROM "critical_opinion"
  2.1183 +            WHERE "initiative_id" = "initiative"."id"
  2.1184 +            AND "member_id" = "supporter"."member_id"
  2.1185 +          ) AS "satisfied"
  2.1186 +        FROM "initiative"
  2.1187 +        JOIN "supporter"
  2.1188 +        ON "supporter"."initiative_id" = "initiative"."id"
  2.1189 +        JOIN "current_draft"
  2.1190 +        ON "initiative"."id" = "current_draft"."initiative_id"
  2.1191 +        JOIN "direct_interest_snapshot"
  2.1192 +        ON "supporter"."member_id" = "direct_interest_snapshot"."member_id"
  2.1193 +        AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
  2.1194 +        AND "event" = 'periodic'
  2.1195 +        WHERE "initiative"."issue_id" = "issue_id_p";
  2.1196 +      RETURN;
  2.1197 +    END;
  2.1198 +  $$;
  2.1199 +
  2.1200 +CREATE OR REPLACE FUNCTION "freeze_after_snapshot"
  2.1201 +  ( "issue_id_p" "issue"."id"%TYPE )
  2.1202 +  RETURNS VOID
  2.1203 +  LANGUAGE 'plpgsql' VOLATILE AS $$
  2.1204 +    DECLARE
  2.1205 +      "issue_row"      "issue"%ROWTYPE;
  2.1206 +      "policy_row"     "policy"%ROWTYPE;
  2.1207 +      "initiative_row" "initiative"%ROWTYPE;
  2.1208 +    BEGIN
  2.1209 +      SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
  2.1210 +      SELECT * INTO "policy_row"
  2.1211 +        FROM "policy" WHERE "id" = "issue_row"."policy_id";
  2.1212 +      PERFORM "set_snapshot_event"("issue_id_p", 'full_freeze');
  2.1213 +      FOR "initiative_row" IN
  2.1214 +        SELECT * FROM "initiative"
  2.1215 +        WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
  2.1216 +      LOOP
  2.1217 +        IF
  2.1218 +          "initiative_row"."satisfied_supporter_count" > 0 AND
  2.1219 +          "initiative_row"."satisfied_supporter_count" *
  2.1220 +          "policy_row"."initiative_quorum_den" >=
  2.1221 +          "issue_row"."population" * "policy_row"."initiative_quorum_num"
  2.1222 +        THEN
  2.1223 +          UPDATE "initiative" SET "admitted" = TRUE
  2.1224 +            WHERE "id" = "initiative_row"."id";
  2.1225 +        ELSE
  2.1226 +          UPDATE "initiative" SET "admitted" = FALSE
  2.1227 +            WHERE "id" = "initiative_row"."id";
  2.1228 +        END IF;
  2.1229 +      END LOOP;
  2.1230 +      IF EXISTS (
  2.1231 +        SELECT NULL FROM "initiative"
  2.1232 +        WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE
  2.1233 +      ) THEN
  2.1234 +        UPDATE "issue" SET
  2.1235 +          "state"        = 'voting',
  2.1236 +          "accepted"     = coalesce("accepted", now()),
  2.1237 +          "half_frozen"  = coalesce("half_frozen", now()),
  2.1238 +          "fully_frozen" = now()
  2.1239 +          WHERE "id" = "issue_id_p";
  2.1240 +      ELSE
  2.1241 +        UPDATE "issue" SET
  2.1242 +          "state"        = 'canceled_no_initiative_admitted',
  2.1243 +          "accepted"     = coalesce("accepted", now()),
  2.1244 +          "half_frozen"  = coalesce("half_frozen", now()),
  2.1245 +          "fully_frozen" = now(),
  2.1246 +          "closed"       = now()
  2.1247 +          WHERE "id" = "issue_id_p";
  2.1248 +        -- NOTE: The following DELETE statements have effect only when
  2.1249 +        --       issue state has been manipulated
  2.1250 +        DELETE FROM "direct_voter"     WHERE "issue_id" = "issue_id_p";
  2.1251 +        DELETE FROM "delegating_voter" WHERE "issue_id" = "issue_id_p";
  2.1252 +        DELETE FROM "battle"           WHERE "issue_id" = "issue_id_p";
  2.1253 +      END IF;
  2.1254 +      RETURN;
  2.1255 +    END;
  2.1256 +  $$;
  2.1257 +
  2.1258 +CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
  2.1259 +  RETURNS VOID
  2.1260 +  LANGUAGE 'plpgsql' VOLATILE AS $$
  2.1261 +    DECLARE
  2.1262 +      "area_id_v"   "area"."id"%TYPE;
  2.1263 +      "unit_id_v"   "unit"."id"%TYPE;
  2.1264 +      "member_id_v" "member"."id"%TYPE;
  2.1265 +    BEGIN
  2.1266 +      PERFORM "lock_issue"("issue_id_p");
  2.1267 +      SELECT "id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
  2.1268 +      SELECT "id" INTO "unit_id_v" FROM "area"  WHERE "id" = "area_id_v";
  2.1269 +      DELETE FROM "delegating_voter"
  2.1270 +        WHERE "issue_id" = "issue_id_p";
  2.1271 +      DELETE FROM "direct_voter"
  2.1272 +        WHERE "issue_id" = "issue_id_p"
  2.1273 +        AND "autoreject" = TRUE;
  2.1274 +      DELETE FROM "direct_voter"
  2.1275 +        USING (
  2.1276 +          SELECT
  2.1277 +            "direct_voter"."member_id"
  2.1278 +          FROM "direct_voter"
  2.1279 +          JOIN "member" ON "direct_voter"."member_id" = "member"."id"
  2.1280 +          LEFT JOIN "privilege"
  2.1281 +          ON "privilege"."unit_id" = "unit_id_v"
  2.1282 +          AND "privilege"."member_id" = "direct_voter"."member_id"
  2.1283 +          WHERE "direct_voter"."issue_id" = "issue_id_p" AND (
  2.1284 +            "member"."active" = FALSE OR
  2.1285 +            "privilege"."voting_right" ISNULL OR
  2.1286 +            "privilege"."voting_right" = FALSE
  2.1287 +          )
  2.1288 +        ) AS "subquery"
  2.1289 +        WHERE "direct_voter"."issue_id" = "issue_id_p"
  2.1290 +        AND "direct_voter"."member_id" = "subquery"."member_id";
  2.1291 +      UPDATE "direct_voter" SET "weight" = 1
  2.1292 +        WHERE "issue_id" = "issue_id_p";
  2.1293 +      PERFORM "add_vote_delegations"("issue_id_p");
  2.1294 +      FOR "member_id_v" IN
  2.1295 +        SELECT "interest"."member_id"
  2.1296 +          FROM "interest"
  2.1297 +          JOIN "member"
  2.1298 +            ON "interest"."member_id" = "member"."id"
  2.1299 +          LEFT JOIN "direct_voter"
  2.1300 +            ON "interest"."member_id" = "direct_voter"."member_id"
  2.1301 +            AND "interest"."issue_id" = "direct_voter"."issue_id"
  2.1302 +          LEFT JOIN "delegating_voter"
  2.1303 +            ON "interest"."member_id" = "delegating_voter"."member_id"
  2.1304 +            AND "interest"."issue_id" = "delegating_voter"."issue_id"
  2.1305 +          WHERE "interest"."issue_id" = "issue_id_p"
  2.1306 +          AND "interest"."autoreject" = TRUE
  2.1307 +          AND "member"."active"
  2.1308 +          AND "direct_voter"."member_id" ISNULL
  2.1309 +          AND "delegating_voter"."member_id" ISNULL
  2.1310 +        UNION SELECT "membership"."member_id"
  2.1311 +          FROM "membership"
  2.1312 +          JOIN "member"
  2.1313 +            ON "membership"."member_id" = "member"."id"
  2.1314 +          LEFT JOIN "interest"
  2.1315 +            ON "membership"."member_id" = "interest"."member_id"
  2.1316 +            AND "interest"."issue_id" = "issue_id_p"
  2.1317 +          LEFT JOIN "direct_voter"
  2.1318 +            ON "membership"."member_id" = "direct_voter"."member_id"
  2.1319 +            AND "direct_voter"."issue_id" = "issue_id_p"
  2.1320 +          LEFT JOIN "delegating_voter"
  2.1321 +            ON "membership"."member_id" = "delegating_voter"."member_id"
  2.1322 +            AND "delegating_voter"."issue_id" = "issue_id_p"
  2.1323 +          WHERE "membership"."area_id" = "area_id_v"
  2.1324 +          AND "membership"."autoreject" = TRUE
  2.1325 +          AND "member"."active"
  2.1326 +          AND "interest"."autoreject" ISNULL
  2.1327 +          AND "direct_voter"."member_id" ISNULL
  2.1328 +          AND "delegating_voter"."member_id" ISNULL
  2.1329 +      LOOP
  2.1330 +        INSERT INTO "direct_voter"
  2.1331 +          ("member_id", "issue_id", "weight", "autoreject") VALUES
  2.1332 +          ("member_id_v", "issue_id_p", 1, TRUE);
  2.1333 +        INSERT INTO "vote" (
  2.1334 +          "member_id",
  2.1335 +          "issue_id",
  2.1336 +          "initiative_id",
  2.1337 +          "grade"
  2.1338 +          ) SELECT
  2.1339 +            "member_id_v" AS "member_id",
  2.1340 +            "issue_id_p"  AS "issue_id",
  2.1341 +            "id"          AS "initiative_id",
  2.1342 +            -1            AS "grade"
  2.1343 +          FROM "initiative" WHERE "issue_id" = "issue_id_p";
  2.1344 +      END LOOP;
  2.1345 +      PERFORM "add_vote_delegations"("issue_id_p");
  2.1346 +      UPDATE "issue" SET
  2.1347 +        "state"  = 'calculation',
  2.1348 +        "closed" = now(),
  2.1349 +        "voter_count" = (
  2.1350 +          SELECT coalesce(sum("weight"), 0)
  2.1351 +          FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
  2.1352 +        )
  2.1353 +        WHERE "id" = "issue_id_p";
  2.1354 +      UPDATE "initiative" SET
  2.1355 +        "positive_votes" = "vote_counts"."positive_votes",
  2.1356 +        "negative_votes" = "vote_counts"."negative_votes",
  2.1357 +        "agreed" = CASE WHEN "majority_strict" THEN
  2.1358 +          "vote_counts"."positive_votes" * "majority_den" >
  2.1359 +          "majority_num" *
  2.1360 +          ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
  2.1361 +        ELSE
  2.1362 +          "vote_counts"."positive_votes" * "majority_den" >=
  2.1363 +          "majority_num" *
  2.1364 +          ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
  2.1365 +        END
  2.1366 +        FROM
  2.1367 +          ( SELECT
  2.1368 +              "initiative"."id" AS "initiative_id",
  2.1369 +              coalesce(
  2.1370 +                sum(
  2.1371 +                  CASE WHEN "grade" > 0 THEN "direct_voter"."weight" ELSE 0 END
  2.1372 +                ),
  2.1373 +                0
  2.1374 +              ) AS "positive_votes",
  2.1375 +              coalesce(
  2.1376 +                sum(
  2.1377 +                  CASE WHEN "grade" < 0 THEN "direct_voter"."weight" ELSE 0 END
  2.1378 +                ),
  2.1379 +                0
  2.1380 +              ) AS "negative_votes"
  2.1381 +            FROM "initiative"
  2.1382 +            JOIN "issue" ON "initiative"."issue_id" = "issue"."id"
  2.1383 +            JOIN "policy" ON "issue"."policy_id" = "policy"."id"
  2.1384 +            LEFT JOIN "direct_voter"
  2.1385 +              ON "direct_voter"."issue_id" = "initiative"."issue_id"
  2.1386 +            LEFT JOIN "vote"
  2.1387 +              ON "vote"."initiative_id" = "initiative"."id"
  2.1388 +              AND "vote"."member_id" = "direct_voter"."member_id"
  2.1389 +            WHERE "initiative"."issue_id" = "issue_id_p"
  2.1390 +            AND "initiative"."admitted"  -- NOTE: NULL case is handled too
  2.1391 +            GROUP BY "initiative"."id"
  2.1392 +          ) AS "vote_counts",
  2.1393 +          "issue",
  2.1394 +          "policy"
  2.1395 +        WHERE "vote_counts"."initiative_id" = "initiative"."id"
  2.1396 +        AND "issue"."id" = "initiative"."issue_id"
  2.1397 +        AND "policy"."id" = "issue"."policy_id";
  2.1398 +      -- NOTE: "closed" column of issue must be set at this point
  2.1399 +      DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
  2.1400 +      INSERT INTO "battle" (
  2.1401 +        "issue_id",
  2.1402 +        "winning_initiative_id", "losing_initiative_id",
  2.1403 +        "count"
  2.1404 +      ) SELECT
  2.1405 +        "issue_id",
  2.1406 +        "winning_initiative_id", "losing_initiative_id",
  2.1407 +        "count"
  2.1408 +        FROM "battle_view" WHERE "issue_id" = "issue_id_p";
  2.1409 +    END;
  2.1410 +  $$;
  2.1411 +
  2.1412 +CREATE OR REPLACE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE)
  2.1413 +  RETURNS VOID
  2.1414 +  LANGUAGE 'plpgsql' VOLATILE AS $$
  2.1415 +    DECLARE
  2.1416 +      "dimension_v"     INTEGER;
  2.1417 +      "vote_matrix"     INT4[][];  -- absolute votes
  2.1418 +      "matrix"          INT8[][];  -- defeat strength / best paths
  2.1419 +      "i"               INTEGER;
  2.1420 +      "j"               INTEGER;
  2.1421 +      "k"               INTEGER;
  2.1422 +      "battle_row"      "battle"%ROWTYPE;
  2.1423 +      "rank_ary"        INT4[];
  2.1424 +      "rank_v"          INT4;
  2.1425 +      "done_v"          INTEGER;
  2.1426 +      "winners_ary"     INTEGER[];
  2.1427 +      "initiative_id_v" "initiative"."id"%TYPE;
  2.1428 +    BEGIN
  2.1429 +      PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE;
  2.1430 +      SELECT count(1) INTO "dimension_v" FROM "initiative"
  2.1431 +        WHERE "issue_id" = "issue_id_p" AND "agreed";
  2.1432 +      IF "dimension_v" = 1 THEN
  2.1433 +        UPDATE "initiative" SET "rank" = 1
  2.1434 +          WHERE "issue_id" = "issue_id_p" AND "agreed";
  2.1435 +      ELSIF "dimension_v" > 1 THEN
  2.1436 +        -- Create "vote_matrix" with absolute number of votes in pairwise
  2.1437 +        -- comparison:
  2.1438 +        "vote_matrix" := "square_matrix_init_string"("dimension_v");  -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
  2.1439 +        "i" := 1;
  2.1440 +        "j" := 2;
  2.1441 +        FOR "battle_row" IN
  2.1442 +          SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"
  2.1443 +          ORDER BY "winning_initiative_id", "losing_initiative_id"
  2.1444 +        LOOP
  2.1445 +          "vote_matrix"["i"]["j"] := "battle_row"."count";
  2.1446 +          IF "j" = "dimension_v" THEN
  2.1447 +            "i" := "i" + 1;
  2.1448 +            "j" := 1;
  2.1449 +          ELSE
  2.1450 +            "j" := "j" + 1;
  2.1451 +            IF "j" = "i" THEN
  2.1452 +              "j" := "j" + 1;
  2.1453 +            END IF;
  2.1454 +          END IF;
  2.1455 +        END LOOP;
  2.1456 +        IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN
  2.1457 +          RAISE EXCEPTION 'Wrong battle count (should not happen)';
  2.1458 +        END IF;
  2.1459 +        -- Store defeat strengths in "matrix" using "defeat_strength"
  2.1460 +        -- function:
  2.1461 +        "matrix" := "square_matrix_init_string"("dimension_v");  -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
  2.1462 +        "i" := 1;
  2.1463 +        LOOP
  2.1464 +          "j" := 1;
  2.1465 +          LOOP
  2.1466 +            IF "i" != "j" THEN
  2.1467 +              "matrix"["i"]["j"] := "defeat_strength"(
  2.1468 +                "vote_matrix"["i"]["j"],
  2.1469 +                "vote_matrix"["j"]["i"]
  2.1470 +              );
  2.1471 +            END IF;
  2.1472 +            EXIT WHEN "j" = "dimension_v";
  2.1473 +            "j" := "j" + 1;
  2.1474 +          END LOOP;
  2.1475 +          EXIT WHEN "i" = "dimension_v";
  2.1476 +          "i" := "i" + 1;
  2.1477 +        END LOOP;
  2.1478 +        -- Find best paths:
  2.1479 +        "i" := 1;
  2.1480 +        LOOP
  2.1481 +          "j" := 1;
  2.1482 +          LOOP
  2.1483 +            IF "i" != "j" THEN
  2.1484 +              "k" := 1;
  2.1485 +              LOOP
  2.1486 +                IF "i" != "k" AND "j" != "k" THEN
  2.1487 +                  IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN
  2.1488 +                    IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN
  2.1489 +                      "matrix"["j"]["k"] := "matrix"["j"]["i"];
  2.1490 +                    END IF;
  2.1491 +                  ELSE
  2.1492 +                    IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN
  2.1493 +                      "matrix"["j"]["k"] := "matrix"["i"]["k"];
  2.1494 +                    END IF;
  2.1495 +                  END IF;
  2.1496 +                END IF;
  2.1497 +                EXIT WHEN "k" = "dimension_v";
  2.1498 +                "k" := "k" + 1;
  2.1499 +              END LOOP;
  2.1500 +            END IF;
  2.1501 +            EXIT WHEN "j" = "dimension_v";
  2.1502 +            "j" := "j" + 1;
  2.1503 +          END LOOP;
  2.1504 +          EXIT WHEN "i" = "dimension_v";
  2.1505 +          "i" := "i" + 1;
  2.1506 +        END LOOP;
  2.1507 +        -- Determine order of winners:
  2.1508 +        "rank_ary" := "array_init_string"("dimension_v");  -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
  2.1509 +        "rank_v" := 1;
  2.1510 +        "done_v" := 0;
  2.1511 +        LOOP
  2.1512 +          "winners_ary" := '{}';
  2.1513 +          "i" := 1;
  2.1514 +          LOOP
  2.1515 +            IF "rank_ary"["i"] ISNULL THEN
  2.1516 +              "j" := 1;
  2.1517 +              LOOP
  2.1518 +                IF
  2.1519 +                  "i" != "j" AND
  2.1520 +                  "rank_ary"["j"] ISNULL AND
  2.1521 +                  "matrix"["j"]["i"] > "matrix"["i"]["j"]
  2.1522 +                THEN
  2.1523 +                  -- someone else is better
  2.1524 +                  EXIT;
  2.1525 +                END IF;
  2.1526 +                IF "j" = "dimension_v" THEN
  2.1527 +                  -- noone is better
  2.1528 +                  "winners_ary" := "winners_ary" || "i";
  2.1529 +                  EXIT;
  2.1530 +                END IF;
  2.1531 +                "j" := "j" + 1;
  2.1532 +              END LOOP;
  2.1533 +            END IF;
  2.1534 +            EXIT WHEN "i" = "dimension_v";
  2.1535 +            "i" := "i" + 1;
  2.1536 +          END LOOP;
  2.1537 +          "i" := 1;
  2.1538 +          LOOP
  2.1539 +            "rank_ary"["winners_ary"["i"]] := "rank_v";
  2.1540 +            "done_v" := "done_v" + 1;
  2.1541 +            EXIT WHEN "i" = array_upper("winners_ary", 1);
  2.1542 +            "i" := "i" + 1;
  2.1543 +          END LOOP;
  2.1544 +          EXIT WHEN "done_v" = "dimension_v";
  2.1545 +          "rank_v" := "rank_v" + 1;
  2.1546 +        END LOOP;
  2.1547 +        -- write preliminary ranks:
  2.1548 +        "i" := 1;
  2.1549 +        FOR "initiative_id_v" IN
  2.1550 +          SELECT "id" FROM "initiative"
  2.1551 +          WHERE "issue_id" = "issue_id_p" AND "agreed"
  2.1552 +          ORDER BY "id"
  2.1553 +        LOOP
  2.1554 +          UPDATE "initiative" SET "rank" = "rank_ary"["i"]
  2.1555 +            WHERE "id" = "initiative_id_v";
  2.1556 +          "i" := "i" + 1;
  2.1557 +        END LOOP;
  2.1558 +        IF "i" != "dimension_v" + 1 THEN
  2.1559 +          RAISE EXCEPTION 'Wrong winner count (should not happen)';
  2.1560 +        END IF;
  2.1561 +        -- straighten ranks (start counting with 1, no equal ranks):
  2.1562 +        "rank_v" := 1;
  2.1563 +        FOR "initiative_id_v" IN
  2.1564 +          SELECT "id" FROM "initiative"
  2.1565 +          WHERE "issue_id" = "issue_id_p" AND "rank" NOTNULL
  2.1566 +          ORDER BY
  2.1567 +            "rank",
  2.1568 +            "vote_ratio"("positive_votes", "negative_votes") DESC,
  2.1569 +            "id"
  2.1570 +        LOOP
  2.1571 +          UPDATE "initiative" SET "rank" = "rank_v"
  2.1572 +            WHERE "id" = "initiative_id_v";
  2.1573 +          "rank_v" := "rank_v" + 1;
  2.1574 +        END LOOP;
  2.1575 +      END IF;
  2.1576 +      -- mark issue as finished
  2.1577 +      UPDATE "issue" SET
  2.1578 +        "state" =
  2.1579 +          CASE WHEN NOT EXISTS (
  2.1580 +            SELECT NULL FROM "initiative"
  2.1581 +            WHERE "issue_id" = "issue_id_p" AND "admitted"
  2.1582 +          ) THEN
  2.1583 +            'canceled_no_initiative_admitted'::"issue_state"
  2.1584 +          ELSE
  2.1585 +            CASE WHEN "dimension_v" = 0 THEN
  2.1586 +              'finished_without_winner'::"issue_state"
  2.1587 +            ELSE
  2.1588 +              'finished_with_winner'::"issue_state"
  2.1589 +            END
  2.1590 +          END,
  2.1591 +        "ranks_available" = TRUE
  2.1592 +        WHERE "id" = "issue_id_p";
  2.1593 +      RETURN;
  2.1594 +    END;
  2.1595 +  $$;
  2.1596 +
  2.1597 +CREATE OR REPLACE FUNCTION "check_issue"
  2.1598 +  ( "issue_id_p" "issue"."id"%TYPE )
  2.1599 +  RETURNS VOID
  2.1600 +  LANGUAGE 'plpgsql' VOLATILE AS $$
  2.1601 +    DECLARE
  2.1602 +      "issue_row"         "issue"%ROWTYPE;
  2.1603 +      "policy_row"        "policy"%ROWTYPE;
  2.1604 +      "voting_requested_v" BOOLEAN;
  2.1605 +    BEGIN
  2.1606 +      PERFORM "lock_issue"("issue_id_p");
  2.1607 +      SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
  2.1608 +      -- only process open issues:
  2.1609 +      IF "issue_row"."closed" ISNULL THEN
  2.1610 +        SELECT * INTO "policy_row" FROM "policy"
  2.1611 +          WHERE "id" = "issue_row"."policy_id";
  2.1612 +        -- create a snapshot, unless issue is already fully frozen:
  2.1613 +        IF "issue_row"."fully_frozen" ISNULL THEN
  2.1614 +          PERFORM "create_snapshot"("issue_id_p");
  2.1615 +          SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
  2.1616 +        END IF;
  2.1617 +        -- eventually close or accept issues, which have not been accepted:
  2.1618 +        IF "issue_row"."accepted" ISNULL THEN
  2.1619 +          IF EXISTS (
  2.1620 +            SELECT NULL FROM "initiative"
  2.1621 +            WHERE "issue_id" = "issue_id_p"
  2.1622 +            AND "supporter_count" > 0
  2.1623 +            AND "supporter_count" * "policy_row"."issue_quorum_den"
  2.1624 +            >= "issue_row"."population" * "policy_row"."issue_quorum_num"
  2.1625 +          ) THEN
  2.1626 +            -- accept issues, if supporter count is high enough
  2.1627 +            PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
  2.1628 +            -- NOTE: "issue_row" used later
  2.1629 +            "issue_row"."state" := 'discussion';
  2.1630 +            "issue_row"."accepted" := now();
  2.1631 +            UPDATE "issue" SET
  2.1632 +              "state"    = "issue_row"."state",
  2.1633 +              "accepted" = "issue_row"."accepted"
  2.1634 +              WHERE "id" = "issue_row"."id";
  2.1635 +          ELSIF
  2.1636 +            now() >= "issue_row"."created" + "issue_row"."admission_time"
  2.1637 +          THEN
  2.1638 +            -- close issues, if admission time has expired
  2.1639 +            PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
  2.1640 +            UPDATE "issue" SET
  2.1641 +              "state" = 'canceled_issue_not_accepted',
  2.1642 +              "closed" = now()
  2.1643 +              WHERE "id" = "issue_row"."id";
  2.1644 +          END IF;
  2.1645 +        END IF;
  2.1646 +        -- eventually half freeze issues:
  2.1647 +        IF
  2.1648 +          -- NOTE: issue can't be closed at this point, if it has been accepted
  2.1649 +          "issue_row"."accepted" NOTNULL AND
  2.1650 +          "issue_row"."half_frozen" ISNULL
  2.1651 +        THEN
  2.1652 +          SELECT
  2.1653 +            CASE
  2.1654 +              WHEN "vote_now" * 2 > "issue_row"."population" THEN
  2.1655 +                TRUE
  2.1656 +              WHEN "vote_later" * 2 > "issue_row"."population" THEN
  2.1657 +                FALSE
  2.1658 +              ELSE NULL
  2.1659 +            END
  2.1660 +            INTO "voting_requested_v"
  2.1661 +            FROM "issue" WHERE "id" = "issue_id_p";
  2.1662 +          IF
  2.1663 +            "voting_requested_v" OR (
  2.1664 +              "voting_requested_v" ISNULL AND
  2.1665 +              now() >= "issue_row"."accepted" + "issue_row"."discussion_time"
  2.1666 +            )
  2.1667 +          THEN
  2.1668 +            PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze');
  2.1669 +            -- NOTE: "issue_row" used later
  2.1670 +            "issue_row"."state" := 'verification';
  2.1671 +            "issue_row"."half_frozen" := now();
  2.1672 +            UPDATE "issue" SET
  2.1673 +              "state"       = "issue_row"."state",
  2.1674 +              "half_frozen" = "issue_row"."half_frozen"
  2.1675 +              WHERE "id" = "issue_row"."id";
  2.1676 +          END IF;
  2.1677 +        END IF;
  2.1678 +        -- close issues after some time, if all initiatives have been revoked:
  2.1679 +        IF
  2.1680 +          "issue_row"."closed" ISNULL AND
  2.1681 +          NOT EXISTS (
  2.1682 +            -- all initiatives are revoked
  2.1683 +            SELECT NULL FROM "initiative"
  2.1684 +            WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
  2.1685 +          ) AND (
  2.1686 +            -- and issue has not been accepted yet
  2.1687 +            "issue_row"."accepted" ISNULL OR
  2.1688 +            NOT EXISTS (
  2.1689 +              -- or no initiatives have been revoked lately
  2.1690 +              SELECT NULL FROM "initiative"
  2.1691 +              WHERE "issue_id" = "issue_id_p"
  2.1692 +              AND now() < "revoked" + "issue_row"."verification_time"
  2.1693 +            ) OR (
  2.1694 +              -- or verification time has elapsed
  2.1695 +              "issue_row"."half_frozen" NOTNULL AND
  2.1696 +              "issue_row"."fully_frozen" ISNULL AND
  2.1697 +              now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
  2.1698 +            )
  2.1699 +          )
  2.1700 +        THEN
  2.1701 +          -- NOTE: "issue_row" used later
  2.1702 +          IF "issue_row"."accepted" ISNULL THEN
  2.1703 +            "issue_row"."state" := 'canceled_revoked_before_accepted';
  2.1704 +          ELSIF "issue_row"."half_frozen" ISNULL THEN
  2.1705 +            "issue_row"."state" := 'canceled_after_revocation_during_discussion';
  2.1706 +          ELSE
  2.1707 +            "issue_row"."state" := 'canceled_after_revocation_during_verification';
  2.1708 +          END IF;
  2.1709 +          "issue_row"."closed" := now();
  2.1710 +          UPDATE "issue" SET
  2.1711 +            "state"  = "issue_row"."state",
  2.1712 +            "closed" = "issue_row"."closed"
  2.1713 +            WHERE "id" = "issue_row"."id";
  2.1714 +        END IF;
  2.1715 +        -- fully freeze issue after verification time:
  2.1716 +        IF
  2.1717 +          "issue_row"."half_frozen" NOTNULL AND
  2.1718 +          "issue_row"."fully_frozen" ISNULL AND
  2.1719 +          "issue_row"."closed" ISNULL AND
  2.1720 +          now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
  2.1721 +        THEN
  2.1722 +          PERFORM "freeze_after_snapshot"("issue_id_p");
  2.1723 +          -- NOTE: "issue" might change, thus "issue_row" has to be updated below
  2.1724 +        END IF;
  2.1725 +        SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
  2.1726 +        -- close issue by calling close_voting(...) after voting time:
  2.1727 +        IF
  2.1728 +          "issue_row"."closed" ISNULL AND
  2.1729 +          "issue_row"."fully_frozen" NOTNULL AND
  2.1730 +          now() >= "issue_row"."fully_frozen" + "issue_row"."voting_time"
  2.1731 +        THEN
  2.1732 +          PERFORM "close_voting"("issue_id_p");
  2.1733 +          -- calculate ranks will not consume much time and can be done now
  2.1734 +          PERFORM "calculate_ranks"("issue_id_p");
  2.1735 +        END IF;
  2.1736 +      END IF;
  2.1737 +      RETURN;
  2.1738 +    END;
  2.1739 +  $$;
  2.1740 +
  2.1741 +CREATE OR REPLACE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE)
  2.1742 +  RETURNS VOID
  2.1743 +  LANGUAGE 'plpgsql' VOLATILE AS $$
  2.1744 +    DECLARE
  2.1745 +      "issue_row" "issue"%ROWTYPE;
  2.1746 +    BEGIN
  2.1747 +      SELECT * INTO "issue_row"
  2.1748 +        FROM "issue" WHERE "id" = "issue_id_p"
  2.1749 +        FOR UPDATE;
  2.1750 +      IF "issue_row"."cleaned" ISNULL THEN
  2.1751 +        UPDATE "issue" SET
  2.1752 +          "closed" = NULL,
  2.1753 +          "ranks_available" = FALSE
  2.1754 +          WHERE "id" = "issue_id_p";
  2.1755 +        DELETE FROM "delegating_voter"
  2.1756 +          WHERE "issue_id" = "issue_id_p";
  2.1757 +        DELETE FROM "direct_voter"
  2.1758 +          WHERE "issue_id" = "issue_id_p";
  2.1759 +        DELETE FROM "delegating_interest_snapshot"
  2.1760 +          WHERE "issue_id" = "issue_id_p";
  2.1761 +        DELETE FROM "direct_interest_snapshot"
  2.1762 +          WHERE "issue_id" = "issue_id_p";
  2.1763 +        DELETE FROM "delegating_population_snapshot"
  2.1764 +          WHERE "issue_id" = "issue_id_p";
  2.1765 +        DELETE FROM "direct_population_snapshot"
  2.1766 +          WHERE "issue_id" = "issue_id_p";
  2.1767 +        DELETE FROM "non_voter"
  2.1768 +          WHERE "issue_id" = "issue_id_p";
  2.1769 +        DELETE FROM "delegation"
  2.1770 +          WHERE "issue_id" = "issue_id_p";
  2.1771 +        DELETE FROM "supporter"
  2.1772 +          WHERE "issue_id" = "issue_id_p";
  2.1773 +        UPDATE "issue" SET
  2.1774 +          "closed"          = "issue_row"."closed",
  2.1775 +          "ranks_available" = "issue_row"."ranks_available",
  2.1776 +          "cleaned"         = now()
  2.1777 +          WHERE "id" = "issue_id_p";
  2.1778 +      END IF;
  2.1779 +      RETURN;
  2.1780 +    END;
  2.1781 +  $$;
  2.1782 +
  2.1783 +CREATE OR REPLACE FUNCTION "check_issue"
  2.1784 +  ( "issue_id_p" "issue"."id"%TYPE )
  2.1785 +  RETURNS VOID
  2.1786 +  LANGUAGE 'plpgsql' VOLATILE AS $$
  2.1787 +    DECLARE
  2.1788 +      "issue_row"         "issue"%ROWTYPE;
  2.1789 +      "policy_row"        "policy"%ROWTYPE;
  2.1790 +      "voting_requested_v" BOOLEAN;
  2.1791 +    BEGIN
  2.1792 +      PERFORM "lock_issue"("issue_id_p");
  2.1793 +      SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
  2.1794 +      -- only process open issues:
  2.1795 +      IF "issue_row"."closed" ISNULL THEN
  2.1796 +        SELECT * INTO "policy_row" FROM "policy"
  2.1797 +          WHERE "id" = "issue_row"."policy_id";
  2.1798 +        -- create a snapshot, unless issue is already fully frozen:
  2.1799 +        IF "issue_row"."fully_frozen" ISNULL THEN
  2.1800 +          PERFORM "create_snapshot"("issue_id_p");
  2.1801 +          SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
  2.1802 +        END IF;
  2.1803 +        -- eventually close or accept issues, which have not been accepted:
  2.1804 +        IF "issue_row"."accepted" ISNULL THEN
  2.1805 +          IF EXISTS (
  2.1806 +            SELECT NULL FROM "initiative"
  2.1807 +            WHERE "issue_id" = "issue_id_p"
  2.1808 +            AND "supporter_count" > 0
  2.1809 +            AND "supporter_count" * "policy_row"."issue_quorum_den"
  2.1810 +            >= "issue_row"."population" * "policy_row"."issue_quorum_num"
  2.1811 +          ) THEN
  2.1812 +            -- accept issues, if supporter count is high enough
  2.1813 +            PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
  2.1814 +            -- NOTE: "issue_row" used later
  2.1815 +            "issue_row"."state" := 'discussion';
  2.1816 +            "issue_row"."accepted" := now();
  2.1817 +            UPDATE "issue" SET
  2.1818 +              "state"    = "issue_row"."state",
  2.1819 +              "accepted" = "issue_row"."accepted"
  2.1820 +              WHERE "id" = "issue_row"."id";
  2.1821 +          ELSIF
  2.1822 +            now() >= "issue_row"."created" + "issue_row"."admission_time"
  2.1823 +          THEN
  2.1824 +            -- close issues, if admission time has expired
  2.1825 +            PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
  2.1826 +            UPDATE "issue" SET
  2.1827 +              "state" = 'canceled_issue_not_accepted',
  2.1828 +              "closed" = now()
  2.1829 +              WHERE "id" = "issue_row"."id";
  2.1830 +          END IF;
  2.1831 +        END IF;
  2.1832 +        -- eventually half freeze issues:
  2.1833 +        IF
  2.1834 +          -- NOTE: issue can't be closed at this point, if it has been accepted
  2.1835 +          "issue_row"."accepted" NOTNULL AND
  2.1836 +          "issue_row"."half_frozen" ISNULL
  2.1837 +        THEN
  2.1838 +          SELECT
  2.1839 +            CASE
  2.1840 +              WHEN "vote_now" * 2 > "issue_row"."population" THEN
  2.1841 +                TRUE
  2.1842 +              WHEN "vote_later" * 2 > "issue_row"."population" THEN
  2.1843 +                FALSE
  2.1844 +              ELSE NULL
  2.1845 +            END
  2.1846 +            INTO "voting_requested_v"
  2.1847 +            FROM "issue" WHERE "id" = "issue_id_p";
  2.1848 +          IF
  2.1849 +            "voting_requested_v" OR (
  2.1850 +              "voting_requested_v" ISNULL AND
  2.1851 +              now() >= "issue_row"."accepted" + "issue_row"."discussion_time"
  2.1852 +            )
  2.1853 +          THEN
  2.1854 +            PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze');
  2.1855 +            -- NOTE: "issue_row" used later
  2.1856 +            "issue_row"."state" := 'verification';
  2.1857 +            "issue_row"."half_frozen" := now();
  2.1858 +            UPDATE "issue" SET
  2.1859 +              "state"       = "issue_row"."state",
  2.1860 +              "half_frozen" = "issue_row"."half_frozen"
  2.1861 +              WHERE "id" = "issue_row"."id";
  2.1862 +          END IF;
  2.1863 +        END IF;
  2.1864 +        -- close issues after some time, if all initiatives have been revoked:
  2.1865 +        IF
  2.1866 +          "issue_row"."closed" ISNULL AND
  2.1867 +          NOT EXISTS (
  2.1868 +            -- all initiatives are revoked
  2.1869 +            SELECT NULL FROM "initiative"
  2.1870 +            WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
  2.1871 +          ) AND (
  2.1872 +            -- and issue has not been accepted yet
  2.1873 +            "issue_row"."accepted" ISNULL OR
  2.1874 +            NOT EXISTS (
  2.1875 +              -- or no initiatives have been revoked lately
  2.1876 +              SELECT NULL FROM "initiative"
  2.1877 +              WHERE "issue_id" = "issue_id_p"
  2.1878 +              AND now() < "revoked" + "issue_row"."verification_time"
  2.1879 +            ) OR (
  2.1880 +              -- or verification time has elapsed
  2.1881 +              "issue_row"."half_frozen" NOTNULL AND
  2.1882 +              "issue_row"."fully_frozen" ISNULL AND
  2.1883 +              now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
  2.1884 +            )
  2.1885 +          )
  2.1886 +        THEN
  2.1887 +          -- NOTE: "issue_row" used later
  2.1888 +          IF "issue_row"."accepted" ISNULL THEN
  2.1889 +            "issue_row"."state" := 'canceled_revoked_before_accepted';
  2.1890 +          ELSIF "issue_row"."half_frozen" ISNULL THEN
  2.1891 +            "issue_row"."state" := 'canceled_after_revocation_during_discussion';
  2.1892 +          ELSE
  2.1893 +            "issue_row"."state" := 'canceled_after_revocation_during_verification';
  2.1894 +          END IF;
  2.1895 +          "issue_row"."closed" := now();
  2.1896 +          UPDATE "issue" SET
  2.1897 +            "state"  = "issue_row"."state",
  2.1898 +            "closed" = "issue_row"."closed"
  2.1899 +            WHERE "id" = "issue_row"."id";
  2.1900 +        END IF;
  2.1901 +        -- fully freeze issue after verification time:
  2.1902 +        IF
  2.1903 +          "issue_row"."half_frozen" NOTNULL AND
  2.1904 +          "issue_row"."fully_frozen" ISNULL AND
  2.1905 +          "issue_row"."closed" ISNULL AND
  2.1906 +          now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
  2.1907 +        THEN
  2.1908 +          PERFORM "freeze_after_snapshot"("issue_id_p");
  2.1909 +          -- NOTE: "issue" might change, thus "issue_row" has to be updated below
  2.1910 +        END IF;
  2.1911 +        SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
  2.1912 +        -- close issue by calling close_voting(...) after voting time:
  2.1913 +        IF
  2.1914 +          "issue_row"."closed" ISNULL AND
  2.1915 +          "issue_row"."fully_frozen" NOTNULL AND
  2.1916 +          now() >= "issue_row"."fully_frozen" + "issue_row"."voting_time"
  2.1917 +        THEN
  2.1918 +          PERFORM "close_voting"("issue_id_p");
  2.1919 +          -- calculate ranks will not consume much time and can be done now
  2.1920 +          PERFORM "calculate_ranks"("issue_id_p");
  2.1921 +        END IF;
  2.1922 +      END IF;
  2.1923 +      RETURN;
  2.1924 +    END;
  2.1925 +  $$;
  2.1926 +
  2.1927 +CREATE OR REPLACE FUNCTION "delete_private_data"()
  2.1928 +  RETURNS VOID
  2.1929 +  LANGUAGE 'plpgsql' VOLATILE AS $$
  2.1930 +    BEGIN
  2.1931 +      UPDATE "member" SET
  2.1932 +        "last_login"                   = NULL,
  2.1933 +        "login"                        = NULL,
  2.1934 +        "password"                     = NULL,
  2.1935 +        "notify_email"                 = NULL,
  2.1936 +        "notify_email_unconfirmed"     = NULL,
  2.1937 +        "notify_email_secret"          = NULL,
  2.1938 +        "notify_email_secret_expiry"   = NULL,
  2.1939 +        "notify_email_lock_expiry"     = NULL,
  2.1940 +        "password_reset_secret"        = NULL,
  2.1941 +        "password_reset_secret_expiry" = NULL,
  2.1942 +        "organizational_unit"          = NULL,
  2.1943 +        "internal_posts"               = NULL,
  2.1944 +        "realname"                     = NULL,
  2.1945 +        "birthday"                     = NULL,
  2.1946 +        "address"                      = NULL,
  2.1947 +        "email"                        = NULL,
  2.1948 +        "xmpp_address"                 = NULL,
  2.1949 +        "website"                      = NULL,
  2.1950 +        "phone"                        = NULL,
  2.1951 +        "mobile_phone"                 = NULL,
  2.1952 +        "profession"                   = NULL,
  2.1953 +        "external_memberships"         = NULL,
  2.1954 +        "external_posts"               = NULL,
  2.1955 +        "statement"                    = NULL;
  2.1956 +      -- "text_search_data" is updated by triggers
  2.1957 +      DELETE FROM "invite_code";
  2.1958 +      DELETE FROM "setting";
  2.1959 +      DELETE FROM "setting_map";
  2.1960 +      DELETE FROM "member_relation_setting";
  2.1961 +      DELETE FROM "member_image";
  2.1962 +      DELETE FROM "contact";
  2.1963 +      DELETE FROM "ignored_member";
  2.1964 +      DELETE FROM "session";
  2.1965 +      DELETE FROM "area_setting";
  2.1966 +      DELETE FROM "issue_setting";
  2.1967 +      DELETE FROM "ignored_initiative";
  2.1968 +      DELETE FROM "initiative_setting";
  2.1969 +      DELETE FROM "suggestion_setting";
  2.1970 +      DELETE FROM "non_voter";
  2.1971 +      DELETE FROM "direct_voter" USING "issue"
  2.1972 +        WHERE "direct_voter"."issue_id" = "issue"."id"
  2.1973 +        AND "issue"."closed" ISNULL;
  2.1974 +      RETURN;
  2.1975 +    END;
  2.1976 +  $$;
  2.1977 +
  2.1978 +
  2.1979 +-- Delete old "delegation_scope" TYPE:
  2.1980 +
  2.1981 +DROP TYPE "delegation_scope_old";
  2.1982 +
  2.1983 +
  2.1984 +COMMIT;
  2.1985 +
  2.1986 +
  2.1987 +-- Generate issue states:
  2.1988 +
  2.1989 +UPDATE "issue" SET "state" =
  2.1990 +  CASE
  2.1991 +  WHEN "closed" ISNULL THEN
  2.1992 +    CASE
  2.1993 +    WHEN "accepted" ISNULL THEN
  2.1994 +      'admission'::"issue_state"
  2.1995 +    WHEN "half_frozen" ISNULL THEN
  2.1996 +      'discussion'::"issue_state"
  2.1997 +    WHEN "fully_frozen" ISNULL THEN
  2.1998 +      'verification'::"issue_state"
  2.1999 +    ELSE
  2.2000 +      'voting'::"issue_state"
  2.2001 +    END
  2.2002 +  WHEN "fully_frozen" NOTNULL THEN
  2.2003 +    CASE
  2.2004 +    WHEN "fully_frozen" = "closed" THEN
  2.2005 +      'canceled_no_initiative_admitted'::"issue_state"
  2.2006 +    ELSE
  2.2007 +      'finished_without_winner'::"issue_state"  -- NOTE: corrected later
  2.2008 +    END
  2.2009 +  WHEN "half_frozen" NOTNULL THEN
  2.2010 +    'canceled_after_revocation_during_verification'::"issue_state"
  2.2011 +  WHEN "accepted" NOTNULL THEN
  2.2012 +    'canceled_after_revocation_during_discussion'::"issue_state"
  2.2013 +  ELSE
  2.2014 +    'canceled_revoked_before_accepted'::"issue_state"  -- NOTE: corrected later
  2.2015 +  END;
  2.2016 +
  2.2017 +ALTER TABLE "issue" ALTER "state" SET NOT NULL;
  2.2018 +
  2.2019 +ALTER TABLE "issue" DROP CONSTRAINT "valid_state";
  2.2020 +ALTER TABLE "issue" ADD CONSTRAINT "valid_state" CHECK ((
  2.2021 +    ("accepted" ISNULL  AND "half_frozen" ISNULL  AND "fully_frozen" ISNULL  AND "closed" ISNULL  AND "ranks_available" = FALSE) OR
  2.2022 +    ("accepted" ISNULL  AND "half_frozen" ISNULL  AND "fully_frozen" ISNULL  AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
  2.2023 +    ("accepted" NOTNULL AND "half_frozen" ISNULL  AND "fully_frozen" ISNULL  AND "closed" ISNULL  AND "ranks_available" = FALSE) OR
  2.2024 +    ("accepted" NOTNULL AND "half_frozen" ISNULL  AND "fully_frozen" ISNULL  AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
  2.2025 +    ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL  AND "closed" ISNULL  AND "ranks_available" = FALSE) OR
  2.2026 +    ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL  AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
  2.2027 +    ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" ISNULL  AND "ranks_available" = FALSE) OR
  2.2028 +    ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
  2.2029 +    ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = TRUE)
  2.2030 +  ) AND (
  2.2031 +    ("state" = 'admission'    AND "closed" ISNULL AND "accepted" ISNULL) OR
  2.2032 +    ("state" = 'discussion'   AND "closed" ISNULL AND "accepted" NOTNULL AND "half_frozen" ISNULL) OR
  2.2033 +    ("state" = 'verification' AND "closed" ISNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL) OR
  2.2034 +    ("state" = 'voting'       AND "closed" ISNULL AND "fully_frozen" NOTNULL) OR
  2.2035 +    ("state" = 'canceled_revoked_before_accepted'              AND "closed" NOTNULL AND "accepted" ISNULL) OR
  2.2036 +    ("state" = 'canceled_issue_not_accepted'                   AND "closed" NOTNULL AND "accepted" ISNULL) OR
  2.2037 +    ("state" = 'canceled_after_revocation_during_discussion'   AND "closed" NOTNULL AND "half_frozen"  ISNULL) OR
  2.2038 +    ("state" = 'canceled_after_revocation_during_verification' AND "closed" NOTNULL AND "fully_frozen" ISNULL) OR
  2.2039 +    ("state" = 'calculation'                     AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = FALSE) OR
  2.2040 +    ("state" = 'canceled_no_initiative_admitted' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE) OR
  2.2041 +    ("state" = 'finished_without_winner'         AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE) OR
  2.2042 +    ("state" = 'finished_with_winner'            AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE)
  2.2043 +  ));
  2.2044 +
  2.2045 +UPDATE "issue" SET "state" = 'finished_with_winner'
  2.2046 +  FROM "initiative"
  2.2047 +  WHERE "issue"."id" = "initiative"."issue_id"
  2.2048 +  AND "issue"."state" = 'finished_without_winner'
  2.2049 +  AND "initiative"."agreed";
  2.2050 +UPDATE "issue" SET "state" = 'canceled_issue_not_accepted'
  2.2051 +  FROM "initiative"
  2.2052 +  WHERE "issue"."id" = "initiative"."issue_id"
  2.2053 +  AND "issue"."state" = 'canceled_revoked_before_accepted'
  2.2054 +  AND "initiative"."revoked" ISNULL;
  2.2055 +
  2.2056 +
  2.2057 +-- Guess "revoked_by_member_id" values based on author of current draft:
  2.2058 +
  2.2059 +UPDATE "initiative" SET "revoked_by_member_id" = "author_id"
  2.2060 +  FROM "current_draft"
  2.2061 +  WHERE "initiative"."id" = "current_draft"."initiative_id"
  2.2062 +  AND "initiative"."revoked" NOTNULL;
  2.2063 +
  2.2064 +ALTER TABLE "initiative" ADD
  2.2065 +  CONSTRAINT "all_or_none_of_revoked_and_revoked_by_member_id_must_be_null"
  2.2066 +  CHECK ("revoked" NOTNULL = "revoked_by_member_id" NOTNULL);
  2.2067 +
  2.2068 +
  2.2069 +-- Attach existing and unused invite codes with unit id 1:
  2.2070 +
  2.2071 +INSERT INTO "invite_code_unit" ("invite_code_id", "unit_id")
  2.2072 +  SELECT "id" AS "invite_code_id", 1 AS "unit_id"
  2.2073 +  FROM "invite_code" WHERE "used" ISNULL;
  2.2074 +
  2.2075 +
  2.2076 +-- Fill "unit_id" column with default value where neccessary:
  2.2077 +
  2.2078 +UPDATE "delegation" SET "unit_id" = 1 WHERE "scope" = 'unit';
  2.2079 +
  2.2080 +ALTER TABLE "delegation" ADD CONSTRAINT "area_id_and_issue_id_set_according_to_scope"
  2.2081 +  CHECK (
  2.2082 +    ("scope" = 'unit'  AND "unit_id" NOTNULL AND "area_id" ISNULL  AND "issue_id" ISNULL ) OR
  2.2083 +    ("scope" = 'area'  AND "unit_id" ISNULL  AND "area_id" NOTNULL AND "issue_id" ISNULL ) OR
  2.2084 +    ("scope" = 'issue' AND "unit_id" ISNULL  AND "area_id" ISNULL  AND "issue_id" NOTNULL) );

Impressum / About Us