liquid_feedback_core

changeset 115:30e0200f82e9

Added SQL update script from v1.3.0 to v1.4.0_rc1
author jbe
date Mon Mar 07 03:35:25 2011 +0100 (2011-03-07)
parents 2abc6bc59f06
children 1cec513e27ac
files core.sql update/core-update.v1.3.0-v1.4.0_rc1.sql
line diff
     1.1 --- a/core.sql	Mon Mar 07 03:34:26 2011 +0100
     1.2 +++ b/core.sql	Mon Mar 07 03:35:25 2011 +0100
     1.3 @@ -7,7 +7,7 @@
     1.4  BEGIN;
     1.5  
     1.6  CREATE VIEW "liquid_feedback_version" AS
     1.7 -  SELECT * FROM (VALUES ('1.4.0_rc1', 1, 4, 0))
     1.8 +  SELECT * FROM (VALUES ('1.4.0_rc1', 1, 4, -1))
     1.9    AS "subquery"("string", "major", "minor", "revision");
    1.10  
    1.11  
     2.1 --- /dev/null	Thu Jan 01 00:00:00 1970 +0000
     2.2 +++ b/update/core-update.v1.3.0-v1.4.0_rc1.sql	Mon Mar 07 03:35:25 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