liquid_feedback_core

changeset 187:aaf5f62b1aa2

Added update script from v1.3.1 to v1.4.0_rc4
author jbe
date Sat Jul 30 01:54:59 2011 +0200 (2011-07-30)
parents 969ce2bea98c
children 8b496fa85a65
files update/core-update.v1.3.1-v1.4.0_rc4.sql
line diff
     1.1 --- /dev/null	Thu Jan 01 00:00:00 1970 +0000
     1.2 +++ b/update/core-update.v1.3.1-v1.4.0_rc4.sql	Sat Jul 30 01:54:59 2011 +0200
     1.3 @@ -0,0 +1,2232 @@
     1.4 +BEGIN;  -- NOTE: file contains additional statements AFTER this BEGIN/COMMIT block!
     1.5 +
     1.6 +
     1.7 +-- Update version information:
     1.8 +
     1.9 +CREATE OR REPLACE VIEW "liquid_feedback_version" AS
    1.10 +  SELECT * FROM (VALUES ('1.4.0_rc4', 1, 4, -1))
    1.11 +  AS "subquery"("string", "major", "minor", "revision");
    1.12 +
    1.13 +
    1.14 +-- New columns "notify_level" and "notify_event_id" in "member" table:
    1.15 +
    1.16 +CREATE TYPE "notify_level" AS ENUM
    1.17 +  ('none', 'voting', 'verification', 'discussion', 'all');
    1.18 +
    1.19 +COMMENT ON TYPE "notify_level" IS 'Level of notification: ''none'' = no notifications, ''voting'' = notifications about finished issues and issues in voting, ''verification'' = notifications about finished issues, issues in voting and verification phase, ''discussion'' = notifications about everything except issues in admission phase, ''all'' = notifications about everything';
    1.20 +
    1.21 +ALTER TABLE "member" ADD "notify_level" "notify_level" NOT NULL DEFAULT 'none';
    1.22 +ALTER TABLE "member" ADD "notify_event_id" INT8;
    1.23 +
    1.24 +COMMENT ON COLUMN "member"."notify_level"    IS 'Selects which event notifications are to be sent to the "notify_email" mail address';
    1.25 +COMMENT ON COLUMN "member"."notify_event_id" IS 'Latest "id" of an "event" the member was notified about';
    1.26 +
    1.27 +
    1.28 +-- Add primary key with type SERIAL8 (INT8) for "invite_code" table:
    1.29 +
    1.30 +ALTER TABLE "invite_code" DROP CONSTRAINT "invite_code_pkey";
    1.31 +ALTER TABLE "invite_code" ALTER "code" SET NOT NULL;
    1.32 +ALTER TABLE "invite_code" ADD UNIQUE ("code");
    1.33 +ALTER TABLE "invite_code" ADD "id" SERIAL8 PRIMARY KEY;
    1.34 +
    1.35 +
    1.36 +-- Add index for "other_member_id" column of "contact" table:
    1.37 +
    1.38 +CREATE INDEX "contact_other_member_id_idx" ON "contact" ("other_member_id");
    1.39 +
    1.40 +
    1.41 +-- New table "ignored_member":
    1.42 +
    1.43 +CREATE TABLE "ignored_member" (
    1.44 +        PRIMARY KEY ("member_id", "other_member_id"),
    1.45 +        "member_id"             INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    1.46 +        "other_member_id"       INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
    1.47 +CREATE INDEX "ignored_member_other_member_id_idx" ON "ignored_member" ("other_member_id");
    1.48 +
    1.49 +COMMENT ON TABLE "ignored_member" IS 'Possibility to filter other members';
    1.50 +
    1.51 +COMMENT ON COLUMN "ignored_member"."member_id"       IS 'Member ignoring someone';
    1.52 +COMMENT ON COLUMN "ignored_member"."other_member_id" IS 'Member being ignored';
    1.53 +
    1.54 +
    1.55 +-- New table "unit" with default entry:
    1.56 +
    1.57 +CREATE TABLE "unit" (
    1.58 +        "id"                    SERIAL4         PRIMARY KEY,
    1.59 +        "parent_id"             INT4            REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    1.60 +        "active"                BOOLEAN         NOT NULL DEFAULT TRUE,
    1.61 +        "name"                  TEXT            NOT NULL,
    1.62 +        "description"           TEXT            NOT NULL DEFAULT '',
    1.63 +        "member_count"          INT4,
    1.64 +        "text_search_data"      TSVECTOR );
    1.65 +CREATE INDEX "unit_root_idx" ON "unit" ("id") WHERE "parent_id" ISNULL;
    1.66 +CREATE INDEX "unit_parent_id_idx" ON "unit" ("parent_id");
    1.67 +CREATE INDEX "unit_active_idx" ON "unit" ("active");
    1.68 +CREATE INDEX "unit_text_search_data_idx" ON "unit" USING gin ("text_search_data");
    1.69 +CREATE TRIGGER "update_text_search_data"
    1.70 +  BEFORE INSERT OR UPDATE ON "unit"
    1.71 +  FOR EACH ROW EXECUTE PROCEDURE
    1.72 +  tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
    1.73 +    "name", "description" );
    1.74 +
    1.75 +COMMENT ON TABLE "unit" IS 'Organizational units organized as trees; Delegations are not inherited through these trees.';
    1.76 +
    1.77 +COMMENT ON COLUMN "unit"."parent_id"    IS 'Parent id of tree node; Multiple roots allowed';
    1.78 +COMMENT ON COLUMN "unit"."active"       IS 'TRUE means new issues can be created in units of this area';
    1.79 +COMMENT ON COLUMN "unit"."member_count" IS 'Count of members as determined by column "voting_right" in table "privilege"';
    1.80 +
    1.81 +INSERT INTO "unit" ("name") VALUES ('Main');  -- NOTE: gets id 1
    1.82 +
    1.83 +
    1.84 +-- New column "unit_id" in table "area":
    1.85 +
    1.86 +ALTER TABLE "area" ADD "unit_id" INT4 DEFAULT 1
    1.87 +  NOT NULL REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
    1.88 +ALTER TABLE "area" ALTER "unit_id" DROP DEFAULT;
    1.89 +
    1.90 +CREATE INDEX "area_unit_id_idx" ON "area" ("unit_id");
    1.91 +
    1.92 +
    1.93 +-- Issue states:
    1.94 +
    1.95 +CREATE TYPE "issue_state" AS ENUM (
    1.96 +        'admission', 'discussion', 'verification', 'voting',
    1.97 +        'canceled_revoked_before_accepted',
    1.98 +        'canceled_issue_not_accepted',
    1.99 +        'canceled_after_revocation_during_discussion',
   1.100 +        'canceled_after_revocation_during_verification',
   1.101 +        'calculation',
   1.102 +        'canceled_no_initiative_admitted',
   1.103 +        'finished_without_winner', 'finished_with_winner');
   1.104 +
   1.105 +COMMENT ON TYPE "issue_state" IS 'State of issues';
   1.106 +
   1.107 +ALTER TABLE "issue" ADD "state" "issue_state";
   1.108 +
   1.109 +-- NOTE: Filling new column with values is done after this transaction (see below)
   1.110 +
   1.111 +
   1.112 +-- New column "revoked_by_member_id" in table "initiative":
   1.113 +
   1.114 +ALTER TABLE "initiative" ADD "revoked_by_member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE;
   1.115 +
   1.116 +COMMENT ON COLUMN "initiative"."revoked_by_member_id" IS 'Member, who decided to revoked the initiative';
   1.117 +
   1.118 +-- NOTE: Filling new column with values is done after this transaction (see below)
   1.119 +
   1.120 +
   1.121 +-- New table "ignored_initiative":
   1.122 +
   1.123 +CREATE TABLE "ignored_initiative" (
   1.124 +        PRIMARY KEY ("initiative_id", "member_id"),
   1.125 +        "initiative_id"         INT4            REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
   1.126 +        "member_id"             INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
   1.127 +CREATE INDEX "ignored_initiative_member_id_idx" ON "ignored_initiative" ("member_id");
   1.128 +
   1.129 +COMMENT ON TABLE "ignored_initiative" IS 'Possibility to filter initiatives';
   1.130 +
   1.131 +
   1.132 +-- New table "invite_code_unit":
   1.133 +
   1.134 +CREATE TABLE "invite_code_unit" (
   1.135 +        PRIMARY KEY ("invite_code_id", "unit_id"),
   1.136 +        "invite_code_id"        INT8            REFERENCES "invite_code" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
   1.137 +        "unit_id"               INT4            REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
   1.138 +
   1.139 +COMMENT ON TABLE "invite_code_unit" IS 'Units where accounts created with a given invite codes get voting rights';
   1.140 +
   1.141 +INSERT INTO "invite_code_unit" ("invite_code_id", "unit_id")
   1.142 +  SELECT "id" AS "invite_code_id", 1 AS "unit_id" FROM "invite_code";
   1.143 +
   1.144 +
   1.145 +-- New table "privilege":
   1.146 +
   1.147 +CREATE TABLE "privilege" (
   1.148 +        PRIMARY KEY ("unit_id", "member_id"),
   1.149 +        "unit_id"               INT4            REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
   1.150 +        "member_id"             INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
   1.151 +        "admin_manager"         BOOLEAN         NOT NULL DEFAULT FALSE,
   1.152 +        "unit_manager"          BOOLEAN         NOT NULL DEFAULT FALSE,
   1.153 +        "area_manager"          BOOLEAN         NOT NULL DEFAULT FALSE,
   1.154 +        "voting_right_manager"  BOOLEAN         NOT NULL DEFAULT FALSE,
   1.155 +        "voting_right"          BOOLEAN         NOT NULL DEFAULT TRUE );
   1.156 +
   1.157 +COMMENT ON TABLE "privilege" IS 'Members rights related to each unit';
   1.158 +
   1.159 +COMMENT ON COLUMN "privilege"."admin_manager"        IS 'Grant/revoke admin privileges to/from other users';
   1.160 +COMMENT ON COLUMN "privilege"."unit_manager"         IS 'Create or lock sub units';
   1.161 +COMMENT ON COLUMN "privilege"."area_manager"         IS 'Create or lock areas and set area parameters';
   1.162 +COMMENT ON COLUMN "privilege"."voting_right_manager" IS 'Select which members are allowed to discuss and vote inside the unit';
   1.163 +COMMENT ON COLUMN "privilege"."voting_right"         IS 'Right to discuss and vote';
   1.164 +
   1.165 +INSERT INTO "privilege" ("unit_id", "member_id", "voting_right")
   1.166 +  SELECT 1 AS "unit_id", "id" AS "member_id", TRUE AS "voting_right"
   1.167 +  FROM "member";
   1.168 +
   1.169 +
   1.170 +-- Remove table "ignored_issue", which is no longer existent:
   1.171 +
   1.172 +DROP TABLE "ignored_issue";
   1.173 +
   1.174 +
   1.175 +-- Replace TYPE "delegation_scope" with a new type, where 'global' is replaced by 'unit':
   1.176 +
   1.177 +ALTER TYPE "delegation_scope" RENAME TO "delegation_scope_old";  -- NOTE: dropped later
   1.178 +CREATE TYPE "delegation_scope" AS ENUM ('unit', 'area', 'issue');
   1.179 +COMMENT ON TYPE "delegation_scope" IS 'Scope for delegations: ''unit'', ''area'', or ''issue'' (order is relevant)';
   1.180 +
   1.181 +
   1.182 +-- Delete views and functions being dependent on type "delegation_scope":
   1.183 +
   1.184 +DROP FUNCTION "delegation_chain"
   1.185 +  ( "member_id_p" "member"."id"%TYPE,
   1.186 +    "area_id_p"   "area"."id"%TYPE,
   1.187 +    "issue_id_p"  "issue"."id"%TYPE );
   1.188 +
   1.189 +DROP FUNCTION "delegation_chain"
   1.190 +  ( "member_id_p"           "member"."id"%TYPE,
   1.191 +    "area_id_p"             "area"."id"%TYPE,
   1.192 +    "issue_id_p"            "issue"."id"%TYPE,
   1.193 +    "simulate_trustee_id_p" "member"."id"%TYPE );
   1.194 +
   1.195 +DROP TYPE "delegation_chain_row";
   1.196 +
   1.197 +DROP VIEW "issue_delegation";
   1.198 +DROP VIEW "area_delegation";
   1.199 +DROP VIEW "global_delegation";
   1.200 +DROP VIEW "active_delegation";
   1.201 +
   1.202 +
   1.203 +-- Modify "delegation" table to use new "delegation_scope" type:
   1.204 +
   1.205 +ALTER TABLE "delegation" DROP CONSTRAINT "no_global_delegation_to_null";
   1.206 +ALTER TABLE "delegation" DROP CONSTRAINT "area_id_and_issue_id_set_according_to_scope";
   1.207 +
   1.208 +DROP INDEX "delegation_global_truster_id_unique_idx";
   1.209 +
   1.210 +ALTER TABLE "delegation" ALTER "scope" TYPE "delegation_scope"
   1.211 +  USING CASE WHEN "scope" = 'global'
   1.212 +  THEN 'unit'::"delegation_scope"
   1.213 +  ELSE "scope"::text::"delegation_scope" END;
   1.214 +
   1.215 +ALTER TABLE "delegation" ADD "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
   1.216 +
   1.217 +ALTER TABLE "delegation" ADD CONSTRAINT "no_unit_delegation_to_null"
   1.218 +  CHECK ("trustee_id" NOTNULL OR "scope" != 'unit');
   1.219 +
   1.220 +ALTER TABLE "delegation" ADD UNIQUE ("unit_id", "truster_id");
   1.221 +
   1.222 +COMMENT ON COLUMN "delegation"."unit_id"  IS 'Reference to unit, if delegation is unit-wide, otherwise NULL';
   1.223 +
   1.224 +-- NOTE: Column "unit_id" filled after transaction (see below)
   1.225 +
   1.226 +
   1.227 +-- Modify snapshot tables to use new "delegation_scope" type:
   1.228 +
   1.229 +ALTER TABLE "delegating_population_snapshot" ALTER "scope" TYPE "delegation_scope"
   1.230 +  USING CASE WHEN "scope" = 'global'
   1.231 +  THEN 'unit'::"delegation_scope"
   1.232 +  ELSE "scope"::text::"delegation_scope" END;
   1.233 +
   1.234 +ALTER TABLE "delegating_interest_snapshot" ALTER "scope" TYPE "delegation_scope"
   1.235 +  USING CASE WHEN "scope" = 'global'
   1.236 +  THEN 'unit'::"delegation_scope"
   1.237 +  ELSE "scope"::text::"delegation_scope" END;
   1.238 +
   1.239 +ALTER TABLE "delegating_voter" ALTER "scope" TYPE "delegation_scope"
   1.240 +  USING CASE WHEN "scope" = 'global'
   1.241 +  THEN 'unit'::"delegation_scope"
   1.242 +  ELSE "scope"::text::"delegation_scope" END;
   1.243 +
   1.244 +
   1.245 +-- New table "non_voter":
   1.246 +
   1.247 +CREATE TABLE "non_voter" (
   1.248 +        PRIMARY KEY ("issue_id", "member_id"),
   1.249 +        "issue_id"              INT4            REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
   1.250 +        "member_id"             INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
   1.251 +CREATE INDEX "non_voter_member_id_idx" ON "non_voter" ("member_id");
   1.252 +
   1.253 +COMMENT ON TABLE "non_voter" IS 'Members who decided to not vote directly on an issue';
   1.254 +
   1.255 +
   1.256 +-- New tables "issue_comment" and "rendered_issue_comment":
   1.257 +
   1.258 +CREATE TABLE "issue_comment" (
   1.259 +        PRIMARY KEY ("issue_id", "member_id"),
   1.260 +        "issue_id"              INT4            REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
   1.261 +        "member_id"             INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
   1.262 +        "changed"               TIMESTAMPTZ     NOT NULL DEFAULT now(),
   1.263 +        "formatting_engine"     TEXT,
   1.264 +        "content"               TEXT            NOT NULL,
   1.265 +        "text_search_data"      TSVECTOR );
   1.266 +CREATE INDEX "issue_comment_member_id_idx" ON "issue_comment" ("member_id");
   1.267 +CREATE INDEX "issue_comment_text_search_data_idx" ON "issue_comment" USING gin ("text_search_data");
   1.268 +CREATE TRIGGER "update_text_search_data"
   1.269 +  BEFORE INSERT OR UPDATE ON "issue_comment"
   1.270 +  FOR EACH ROW EXECUTE PROCEDURE
   1.271 +  tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "content");
   1.272 +
   1.273 +COMMENT ON TABLE "issue_comment" IS 'Place to store free comments of members related to issues';
   1.274 +
   1.275 +COMMENT ON COLUMN "issue_comment"."changed" IS 'Time the comment was last changed';
   1.276 +
   1.277 +CREATE TABLE "rendered_issue_comment" (
   1.278 +        PRIMARY KEY ("issue_id", "member_id", "format"),
   1.279 +        FOREIGN KEY ("issue_id", "member_id")
   1.280 +          REFERENCES "issue_comment" ("issue_id", "member_id")
   1.281 +          ON DELETE CASCADE ON UPDATE CASCADE,
   1.282 +        "issue_id"              INT4,
   1.283 +        "member_id"             INT4,
   1.284 +        "format"                TEXT,
   1.285 +        "content"               TEXT            NOT NULL );
   1.286 +
   1.287 +COMMENT ON TABLE "rendered_issue_comment" IS 'This table may be used by frontends to cache "rendered" issue comments (e.g. HTML output generated from wiki text)';
   1.288 +
   1.289 +
   1.290 +-- New tables "voting_comment" and "rendered_voting_comment":
   1.291 +
   1.292 +CREATE TABLE "voting_comment" (
   1.293 +        PRIMARY KEY ("issue_id", "member_id"),
   1.294 +        "issue_id"              INT4            REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
   1.295 +        "member_id"             INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
   1.296 +        "changed"               TIMESTAMPTZ,
   1.297 +        "formatting_engine"     TEXT,
   1.298 +        "content"               TEXT            NOT NULL,
   1.299 +        "text_search_data"      TSVECTOR );
   1.300 +CREATE INDEX "voting_comment_member_id_idx" ON "voting_comment" ("member_id");
   1.301 +CREATE INDEX "voting_comment_text_search_data_idx" ON "voting_comment" USING gin ("text_search_data");
   1.302 +CREATE TRIGGER "update_text_search_data"
   1.303 +  BEFORE INSERT OR UPDATE ON "voting_comment"
   1.304 +  FOR EACH ROW EXECUTE PROCEDURE
   1.305 +  tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "content");
   1.306 +
   1.307 +COMMENT ON TABLE "voting_comment" IS 'Storage for comments of voters to be published after voting has finished.';
   1.308 +
   1.309 +COMMENT ON COLUMN "voting_comment"."changed" IS 'Is to be set or updated by the frontend, if comment was inserted or updated AFTER the issue has been closed. Otherwise it shall be set to NULL.';
   1.310 +
   1.311 +CREATE TABLE "rendered_voting_comment" (
   1.312 +        PRIMARY KEY ("issue_id", "member_id", "format"),
   1.313 +        FOREIGN KEY ("issue_id", "member_id")
   1.314 +          REFERENCES "voting_comment" ("issue_id", "member_id")
   1.315 +          ON DELETE CASCADE ON UPDATE CASCADE,
   1.316 +        "issue_id"              INT4,
   1.317 +        "member_id"             INT4,
   1.318 +        "format"                TEXT,
   1.319 +        "content"               TEXT            NOT NULL );
   1.320 +
   1.321 +COMMENT ON TABLE "rendered_voting_comment" IS 'This table may be used by frontends to cache "rendered" voting comments (e.g. HTML output generated from wiki text)';
   1.322 +
   1.323 +
   1.324 +-- New table "event":
   1.325 +
   1.326 +CREATE TYPE "event_type" AS ENUM (
   1.327 +        'issue_state_changed',
   1.328 +        'initiative_created_in_new_issue',
   1.329 +        'initiative_created_in_existing_issue',
   1.330 +        'initiative_revoked',
   1.331 +        'new_draft_created',
   1.332 +        'suggestion_created');
   1.333 +
   1.334 +COMMENT ON TYPE "event_type" IS 'Type used for column "event" of table "event"';
   1.335 +
   1.336 +CREATE TABLE "event" (
   1.337 +        "id"                    SERIAL8         PRIMARY KEY,
   1.338 +        "occurrence"            TIMESTAMPTZ     NOT NULL DEFAULT now(),
   1.339 +        "event"                 "event_type"    NOT NULL,
   1.340 +        "member_id"             INT4            REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
   1.341 +        "issue_id"              INT4            REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
   1.342 +        "state"                 "issue_state"   CHECK ("state" != 'calculation'),
   1.343 +        "initiative_id"         INT4,
   1.344 +        "draft_id"              INT8,
   1.345 +        "suggestion_id"         INT8,
   1.346 +        FOREIGN KEY ("issue_id", "initiative_id")
   1.347 +          REFERENCES "initiative" ("issue_id", "id")
   1.348 +          ON DELETE CASCADE ON UPDATE CASCADE,
   1.349 +        FOREIGN KEY ("initiative_id", "draft_id")
   1.350 +          REFERENCES "draft" ("initiative_id", "id")
   1.351 +          ON DELETE CASCADE ON UPDATE CASCADE,
   1.352 +        FOREIGN KEY ("initiative_id", "suggestion_id")
   1.353 +          REFERENCES "suggestion" ("initiative_id", "id")
   1.354 +          ON DELETE CASCADE ON UPDATE CASCADE,
   1.355 +        CONSTRAINT "null_constraints_for_issue_state_changed" CHECK (
   1.356 +          "event" != 'issue_state_changed' OR (
   1.357 +            "member_id"     ISNULL  AND
   1.358 +            "issue_id"      NOTNULL AND
   1.359 +            "state"         NOTNULL AND
   1.360 +            "initiative_id" ISNULL  AND
   1.361 +            "draft_id"      ISNULL  AND
   1.362 +            "suggestion_id" ISNULL  )),
   1.363 +        CONSTRAINT "null_constraints_for_initiative_creation_or_revocation_or_new_draft" CHECK (
   1.364 +          "event" NOT IN (
   1.365 +            'initiative_created_in_new_issue',
   1.366 +            'initiative_created_in_existing_issue',
   1.367 +            'initiative_revoked',
   1.368 +            'new_draft_created'
   1.369 +          ) OR (
   1.370 +            "member_id"     NOTNULL AND
   1.371 +            "issue_id"      NOTNULL AND
   1.372 +            "state"         NOTNULL AND
   1.373 +            "initiative_id" NOTNULL AND
   1.374 +            "draft_id"      NOTNULL AND
   1.375 +            "suggestion_id" ISNULL  )),
   1.376 +        CONSTRAINT "null_constraints_for_suggestion_creation" CHECK (
   1.377 +          "event" != 'suggestion_created' OR (
   1.378 +            "member_id"     NOTNULL AND
   1.379 +            "issue_id"      NOTNULL AND
   1.380 +            "state"         NOTNULL AND
   1.381 +            "initiative_id" NOTNULL AND
   1.382 +            "draft_id"      ISNULL  AND
   1.383 +            "suggestion_id" NOTNULL )) );
   1.384 +
   1.385 +COMMENT ON TABLE "event" IS 'Event table, automatically filled by triggers';
   1.386 +
   1.387 +COMMENT ON COLUMN "event"."occurrence" IS 'Point in time, when event occurred';
   1.388 +COMMENT ON COLUMN "event"."event"      IS 'Type of event (see TYPE "event_type")';
   1.389 +COMMENT ON COLUMN "event"."member_id"  IS 'Member who caused the event, if applicable';
   1.390 +COMMENT ON COLUMN "event"."state"      IS 'If issue_id is set: state of affected issue; If state changed: new state';
   1.391 +
   1.392 +
   1.393 +-- Triggers to fill "event" table:
   1.394 +
   1.395 +CREATE FUNCTION "write_event_issue_state_changed_trigger"()
   1.396 +  RETURNS TRIGGER
   1.397 +  LANGUAGE 'plpgsql' VOLATILE AS $$
   1.398 +    BEGIN
   1.399 +      IF NEW."state" != OLD."state" AND NEW."state" != 'calculation' THEN
   1.400 +        INSERT INTO "event" ("event", "issue_id", "state")
   1.401 +          VALUES ('issue_state_changed', NEW."id", NEW."state");
   1.402 +      END IF;
   1.403 +      RETURN NULL;
   1.404 +    END;
   1.405 +  $$;
   1.406 +
   1.407 +CREATE TRIGGER "write_event_issue_state_changed"
   1.408 +  AFTER UPDATE ON "issue" FOR EACH ROW EXECUTE PROCEDURE
   1.409 +  "write_event_issue_state_changed_trigger"();
   1.410 +
   1.411 +COMMENT ON FUNCTION "write_event_issue_state_changed_trigger"() IS 'Implementation of trigger "write_event_issue_state_changed" on table "issue"';
   1.412 +COMMENT ON TRIGGER "write_event_issue_state_changed" ON "issue" IS 'Create entry in "event" table on "state" change';
   1.413 +
   1.414 +CREATE FUNCTION "write_event_initiative_or_draft_created_trigger"()
   1.415 +  RETURNS TRIGGER
   1.416 +  LANGUAGE 'plpgsql' VOLATILE AS $$
   1.417 +    DECLARE
   1.418 +      "initiative_row" "initiative"%ROWTYPE;
   1.419 +      "issue_row"      "issue"%ROWTYPE;
   1.420 +      "event_v"        "event_type";
   1.421 +    BEGIN
   1.422 +      SELECT * INTO "initiative_row" FROM "initiative"
   1.423 +        WHERE "id" = NEW."initiative_id";
   1.424 +      SELECT * INTO "issue_row" FROM "issue"
   1.425 +        WHERE "id" = "initiative_row"."issue_id";
   1.426 +      IF EXISTS (
   1.427 +        SELECT NULL FROM "draft"
   1.428 +        WHERE "initiative_id" = NEW."initiative_id"
   1.429 +        AND "id" != NEW."id"
   1.430 +      ) THEN
   1.431 +        "event_v" := 'new_draft_created';
   1.432 +      ELSE
   1.433 +        IF EXISTS (
   1.434 +          SELECT NULL FROM "initiative"
   1.435 +          WHERE "issue_id" = "initiative_row"."issue_id"
   1.436 +          AND "id" != "initiative_row"."id"
   1.437 +        ) THEN
   1.438 +          "event_v" := 'initiative_created_in_existing_issue';
   1.439 +        ELSE
   1.440 +          "event_v" := 'initiative_created_in_new_issue';
   1.441 +        END IF;
   1.442 +      END IF;
   1.443 +      INSERT INTO "event" (
   1.444 +          "event", "member_id",
   1.445 +          "issue_id", "state", "initiative_id", "draft_id"
   1.446 +        ) VALUES (
   1.447 +          "event_v",
   1.448 +          NEW."author_id",
   1.449 +          "initiative_row"."issue_id",
   1.450 +          "issue_row"."state",
   1.451 +          "initiative_row"."id",
   1.452 +          NEW."id" );
   1.453 +      RETURN NULL;
   1.454 +    END;
   1.455 +  $$;
   1.456 +
   1.457 +CREATE TRIGGER "write_event_initiative_or_draft_created"
   1.458 +  AFTER INSERT ON "draft" FOR EACH ROW EXECUTE PROCEDURE
   1.459 +  "write_event_initiative_or_draft_created_trigger"();
   1.460 +
   1.461 +COMMENT ON FUNCTION "write_event_initiative_or_draft_created_trigger"() IS 'Implementation of trigger "write_event_initiative_or_draft_created" on table "issue"';
   1.462 +COMMENT ON TRIGGER "write_event_initiative_or_draft_created" ON "draft" IS 'Create entry in "event" table on draft creation';
   1.463 +
   1.464 +CREATE FUNCTION "write_event_initiative_revoked_trigger"()
   1.465 +  RETURNS TRIGGER
   1.466 +  LANGUAGE 'plpgsql' VOLATILE AS $$
   1.467 +    DECLARE
   1.468 +      "issue_row"      "issue"%ROWTYPE;
   1.469 +    BEGIN
   1.470 +      SELECT * INTO "issue_row" FROM "issue"
   1.471 +        WHERE "id" = NEW."issue_id";
   1.472 +      IF OLD."revoked" ISNULL AND NEW."revoked" NOTNULL THEN
   1.473 +        INSERT INTO "event" (
   1.474 +            "event", "member_id", "issue_id", "state", "initiative_id"
   1.475 +          ) VALUES (
   1.476 +            'initiative_revoked',
   1.477 +            NEW."revoked_by_member_id",
   1.478 +            NEW."issue_id",
   1.479 +            "issue_row"."state",
   1.480 +            NEW."id" );
   1.481 +      END IF;
   1.482 +      RETURN NULL;
   1.483 +    END;
   1.484 +  $$;
   1.485 +
   1.486 +CREATE TRIGGER "write_event_initiative_revoked"
   1.487 +  AFTER UPDATE ON "initiative" FOR EACH ROW EXECUTE PROCEDURE
   1.488 +  "write_event_initiative_revoked_trigger"();
   1.489 +
   1.490 +COMMENT ON FUNCTION "write_event_initiative_revoked_trigger"()      IS 'Implementation of trigger "write_event_initiative_revoked" on table "issue"';
   1.491 +COMMENT ON TRIGGER "write_event_initiative_revoked" ON "initiative" IS 'Create entry in "event" table, when an initiative is revoked';
   1.492 +
   1.493 +CREATE FUNCTION "write_event_suggestion_created_trigger"()
   1.494 +  RETURNS TRIGGER
   1.495 +  LANGUAGE 'plpgsql' VOLATILE AS $$
   1.496 +    DECLARE
   1.497 +      "initiative_row" "initiative"%ROWTYPE;
   1.498 +      "issue_row"      "issue"%ROWTYPE;
   1.499 +    BEGIN
   1.500 +      SELECT * INTO "initiative_row" FROM "initiative"
   1.501 +        WHERE "id" = NEW."initiative_id";
   1.502 +      SELECT * INTO "issue_row" FROM "issue"
   1.503 +        WHERE "id" = "initiative_row"."issue_id";
   1.504 +      INSERT INTO "event" (
   1.505 +          "event", "member_id",
   1.506 +          "issue_id", "state", "initiative_id", "suggestion_id"
   1.507 +        ) VALUES (
   1.508 +          'suggestion_created',
   1.509 +          NEW."author_id",
   1.510 +          "initiative_row"."issue_id",
   1.511 +          "issue_row"."state",
   1.512 +          "initiative_row"."id",
   1.513 +          NEW."id" );
   1.514 +      RETURN NULL;
   1.515 +    END;
   1.516 +  $$;
   1.517 +
   1.518 +CREATE TRIGGER "write_event_suggestion_created"
   1.519 +  AFTER INSERT ON "suggestion" FOR EACH ROW EXECUTE PROCEDURE
   1.520 +  "write_event_suggestion_created_trigger"();
   1.521 +
   1.522 +COMMENT ON FUNCTION "write_event_suggestion_created_trigger"()      IS 'Implementation of trigger "write_event_suggestion_created" on table "issue"';
   1.523 +COMMENT ON TRIGGER "write_event_suggestion_created" ON "suggestion" IS 'Create entry in "event" table on suggestion creation';
   1.524 +
   1.525 +
   1.526 +-- Modified views:
   1.527 +
   1.528 +CREATE VIEW "unit_delegation" AS
   1.529 +  SELECT
   1.530 +    "unit"."id" AS "unit_id",
   1.531 +    "delegation"."id",
   1.532 +    "delegation"."truster_id",
   1.533 +    "delegation"."trustee_id",
   1.534 +    "delegation"."scope"
   1.535 +  FROM "unit"
   1.536 +  JOIN "delegation"
   1.537 +    ON "delegation"."unit_id" = "unit"."id"
   1.538 +  JOIN "member"
   1.539 +    ON "delegation"."truster_id" = "member"."id"
   1.540 +  JOIN "privilege"
   1.541 +    ON "delegation"."unit_id" = "privilege"."unit_id"
   1.542 +    AND "delegation"."truster_id" = "privilege"."member_id"
   1.543 +  WHERE "member"."active" AND "privilege"."voting_right";
   1.544 +
   1.545 +COMMENT ON VIEW "unit_delegation" IS 'Unit delegations where trusters are active and have voting right';
   1.546 +
   1.547 +CREATE VIEW "area_delegation" AS
   1.548 +  SELECT DISTINCT ON ("area"."id", "delegation"."truster_id")
   1.549 +    "area"."id" AS "area_id",
   1.550 +    "delegation"."id",
   1.551 +    "delegation"."truster_id",
   1.552 +    "delegation"."trustee_id",
   1.553 +    "delegation"."scope"
   1.554 +  FROM "area"
   1.555 +  JOIN "delegation"
   1.556 +    ON "delegation"."unit_id" = "area"."unit_id"
   1.557 +    OR "delegation"."area_id" = "area"."id"
   1.558 +  JOIN "member"
   1.559 +    ON "delegation"."truster_id" = "member"."id"
   1.560 +  JOIN "privilege"
   1.561 +    ON "area"."unit_id" = "privilege"."unit_id"
   1.562 +    AND "delegation"."truster_id" = "privilege"."member_id"
   1.563 +  WHERE "member"."active" AND "privilege"."voting_right"
   1.564 +  ORDER BY
   1.565 +    "area"."id",
   1.566 +    "delegation"."truster_id",
   1.567 +    "delegation"."scope" DESC;
   1.568 +
   1.569 +COMMENT ON VIEW "area_delegation" IS 'Area delegations where trusters are active and have voting right';
   1.570 +
   1.571 +CREATE VIEW "issue_delegation" AS
   1.572 +  SELECT DISTINCT ON ("issue"."id", "delegation"."truster_id")
   1.573 +    "issue"."id" AS "issue_id",
   1.574 +    "delegation"."id",
   1.575 +    "delegation"."truster_id",
   1.576 +    "delegation"."trustee_id",
   1.577 +    "delegation"."scope"
   1.578 +  FROM "issue"
   1.579 +  JOIN "area"
   1.580 +    ON "area"."id" = "issue"."area_id"
   1.581 +  JOIN "delegation"
   1.582 +    ON "delegation"."unit_id" = "area"."unit_id"
   1.583 +    OR "delegation"."area_id" = "area"."id"
   1.584 +    OR "delegation"."issue_id" = "issue"."id"
   1.585 +  JOIN "member"
   1.586 +    ON "delegation"."truster_id" = "member"."id"
   1.587 +  JOIN "privilege"
   1.588 +    ON "area"."unit_id" = "privilege"."unit_id"
   1.589 +    AND "delegation"."truster_id" = "privilege"."member_id"
   1.590 +  WHERE "member"."active" AND "privilege"."voting_right"
   1.591 +  ORDER BY
   1.592 +    "issue"."id",
   1.593 +    "delegation"."truster_id",
   1.594 +    "delegation"."scope" DESC;
   1.595 +
   1.596 +COMMENT ON VIEW "issue_delegation" IS 'Issue delegations where trusters are active and have voting right';
   1.597 +
   1.598 +CREATE VIEW "unit_member_count" AS
   1.599 +  SELECT
   1.600 +    "unit"."id" AS "unit_id",
   1.601 +    sum("member"."id") AS "member_count"
   1.602 +  FROM "unit"
   1.603 +  LEFT JOIN "privilege"
   1.604 +  ON "privilege"."unit_id" = "unit"."id" 
   1.605 +  AND "privilege"."voting_right"
   1.606 +  LEFT JOIN "member"
   1.607 +  ON "member"."id" = "privilege"."member_id"
   1.608 +  AND "member"."active"
   1.609 +  GROUP BY "unit"."id";
   1.610 +
   1.611 +COMMENT ON VIEW "unit_member_count" IS 'View used to update "member_count" column of "unit" table';
   1.612 +
   1.613 +DROP VIEW "area_member_count";
   1.614 +CREATE VIEW "area_member_count" AS
   1.615 +  SELECT
   1.616 +    "area"."id" AS "area_id",
   1.617 +    count("member"."id") AS "direct_member_count",
   1.618 +    coalesce(
   1.619 +      sum(
   1.620 +        CASE WHEN "member"."id" NOTNULL THEN
   1.621 +          "membership_weight"("area"."id", "member"."id")
   1.622 +        ELSE 0 END
   1.623 +      )
   1.624 +    ) AS "member_weight",
   1.625 +    coalesce(
   1.626 +      sum(
   1.627 +        CASE WHEN "member"."id" NOTNULL AND "membership"."autoreject" THEN
   1.628 +          "membership_weight"("area"."id", "member"."id")
   1.629 +        ELSE 0 END
   1.630 +      )
   1.631 +    ) AS "autoreject_weight"
   1.632 +  FROM "area"
   1.633 +  LEFT JOIN "membership"
   1.634 +  ON "area"."id" = "membership"."area_id"
   1.635 +  LEFT JOIN "privilege"
   1.636 +  ON "privilege"."unit_id" = "area"."unit_id"
   1.637 +  AND "privilege"."member_id" = "membership"."member_id"
   1.638 +  AND "privilege"."voting_right"
   1.639 +  LEFT JOIN "member"
   1.640 +  ON "member"."id" = "privilege"."member_id"  -- NOTE: no membership here!
   1.641 +  AND "member"."active"
   1.642 +  GROUP BY "area"."id";
   1.643 +
   1.644 +COMMENT ON VIEW "area_member_count" IS 'View used to update "direct_member_count", "member_weight" and "autoreject_weight" columns of table "area"';
   1.645 +
   1.646 +
   1.647 +-- New view "event_seen_by_member":
   1.648 +
   1.649 +CREATE VIEW "event_seen_by_member" AS
   1.650 +  SELECT
   1.651 +    "member"."id" AS "seen_by_member_id",
   1.652 +    CASE WHEN "event"."state" IN (
   1.653 +      'voting',
   1.654 +      'finished_without_winner',
   1.655 +      'finished_with_winner'
   1.656 +    ) THEN
   1.657 +      'voting'::"notify_level"
   1.658 +    ELSE
   1.659 +      CASE WHEN "event"."state" IN (
   1.660 +        'verification',
   1.661 +        'canceled_after_revocation_during_verification',
   1.662 +        'canceled_no_initiative_admitted'
   1.663 +      ) THEN
   1.664 +        'verification'::"notify_level"
   1.665 +      ELSE
   1.666 +        CASE WHEN "event"."state" IN (
   1.667 +          'discussion',
   1.668 +          'canceled_after_revocation_during_discussion'
   1.669 +        ) THEN
   1.670 +          'discussion'::"notify_level"
   1.671 +        ELSE
   1.672 +          'all'::"notify_level"
   1.673 +        END
   1.674 +      END
   1.675 +    END AS "notify_level",
   1.676 +    "event".*
   1.677 +  FROM "member" CROSS JOIN "event"
   1.678 +  LEFT JOIN "issue"
   1.679 +    ON "event"."issue_id" = "issue"."id"
   1.680 +  LEFT JOIN "membership"
   1.681 +    ON "member"."id" = "membership"."member_id"
   1.682 +    AND "issue"."area_id" = "membership"."area_id"
   1.683 +  LEFT JOIN "interest"
   1.684 +    ON "member"."id" = "interest"."member_id"
   1.685 +    AND "event"."issue_id" = "interest"."issue_id"
   1.686 +  LEFT JOIN "supporter"
   1.687 +    ON "member"."id" = "supporter"."member_id"
   1.688 +    AND "event"."initiative_id" = "supporter"."initiative_id"
   1.689 +  LEFT JOIN "ignored_member"
   1.690 +    ON "member"."id" = "ignored_member"."member_id"
   1.691 +    AND "event"."member_id" = "ignored_member"."other_member_id"
   1.692 +  LEFT JOIN "ignored_initiative"
   1.693 +    ON "member"."id" = "ignored_initiative"."member_id"
   1.694 +    AND "event"."initiative_id" = "ignored_initiative"."initiative_id"
   1.695 +  WHERE (
   1.696 +    "supporter"."member_id" NOTNULL OR
   1.697 +    "interest"."member_id" NOTNULL OR
   1.698 +    ( "membership"."member_id" NOTNULL AND
   1.699 +      "event"."event" IN (
   1.700 +        'issue_state_changed',
   1.701 +        'initiative_created_in_new_issue',
   1.702 +        'initiative_created_in_existing_issue',
   1.703 +        'initiative_revoked' ) ) )
   1.704 +  AND "ignored_member"."member_id" ISNULL
   1.705 +  AND "ignored_initiative"."member_id" ISNULL;
   1.706 +
   1.707 +COMMENT ON VIEW "event_seen_by_member" IS 'Events as seen by a member, depending on its memberships, interests and support';
   1.708 +
   1.709 +
   1.710 +-- New view "pending_notification":
   1.711 +
   1.712 +CREATE VIEW "pending_notification" AS
   1.713 +  SELECT
   1.714 +    "member"."id" AS "seen_by_member_id",
   1.715 +    "event".*
   1.716 +  FROM "member" CROSS JOIN "event"
   1.717 +  LEFT JOIN "issue"
   1.718 +    ON "event"."issue_id" = "issue"."id"
   1.719 +  LEFT JOIN "membership"
   1.720 +    ON "member"."id" = "membership"."member_id"
   1.721 +    AND "issue"."area_id" = "membership"."area_id"
   1.722 +  LEFT JOIN "interest"
   1.723 +    ON "member"."id" = "interest"."member_id"
   1.724 +    AND "event"."issue_id" = "interest"."issue_id"
   1.725 +  LEFT JOIN "supporter"
   1.726 +    ON "member"."id" = "supporter"."member_id"
   1.727 +    AND "event"."initiative_id" = "supporter"."initiative_id"
   1.728 +  LEFT JOIN "ignored_member"
   1.729 +    ON "member"."id" = "ignored_member"."member_id"
   1.730 +    AND "event"."member_id" = "ignored_member"."other_member_id"
   1.731 +  LEFT JOIN "ignored_initiative"
   1.732 +    ON "member"."id" = "ignored_initiative"."member_id"
   1.733 +    AND "event"."initiative_id" = "ignored_initiative"."initiative_id"
   1.734 +  WHERE (
   1.735 +    "member"."notify_event_id" ISNULL OR
   1.736 +    ( "member"."notify_event_id" NOTNULL AND
   1.737 +      "member"."notify_event_id" < "event"."id" ) )
   1.738 +  AND (
   1.739 +    ( "member"."notify_level" >= 'all' ) OR
   1.740 +    ( "member"."notify_level" >= 'voting' AND
   1.741 +      "event"."state" IN (
   1.742 +        'voting',
   1.743 +        'finished_without_winner',
   1.744 +        'finished_with_winner' ) ) OR
   1.745 +    ( "member"."notify_level" >= 'verification' AND
   1.746 +      "event"."state" IN (
   1.747 +        'verification',
   1.748 +        'canceled_after_revocation_during_verification',
   1.749 +        'canceled_no_initiative_admitted' ) ) OR
   1.750 +    ( "member"."notify_level" >= 'discussion' AND
   1.751 +      "event"."state" IN (
   1.752 +        'discussion',
   1.753 +        'canceled_after_revocation_during_discussion' ) ) )
   1.754 +  AND (
   1.755 +    "supporter"."member_id" NOTNULL OR
   1.756 +    "interest"."member_id" NOTNULL OR
   1.757 +    ( "membership"."member_id" NOTNULL AND
   1.758 +      "event"."event" IN (
   1.759 +        'issue_state_changed',
   1.760 +        'initiative_created_in_new_issue',
   1.761 +        'initiative_created_in_existing_issue',
   1.762 +        'initiative_revoked' ) ) )
   1.763 +  AND "ignored_member"."member_id" ISNULL
   1.764 +  AND "ignored_initiative"."member_id" ISNULL;
   1.765 +
   1.766 +COMMENT ON VIEW "pending_notification" IS 'Events to be sent to "notify_email" address of member referred to by "seen_by_member_id"';
   1.767 +
   1.768 +
   1.769 +COMMENT ON TYPE "timeline_event" IS 'Types of event in timeline tables (DEPRECATED)';
   1.770 +COMMENT ON VIEW "timeline_issue" IS 'Helper view for "timeline" view (DEPRECATED)';
   1.771 +COMMENT ON VIEW "timeline_initiative" IS 'Helper view for "timeline" view (DEPRECATED)';
   1.772 +COMMENT ON VIEW "timeline_draft" IS 'Helper view for "timeline" view (DEPRECATED)';
   1.773 +COMMENT ON VIEW "timeline_suggestion" IS 'Helper view for "timeline" view (DEPRECATED)';
   1.774 +COMMENT ON VIEW "timeline" IS 'Aggregation of different events in the system (DEPRECATED)';
   1.775 +
   1.776 +
   1.777 +-- Modified "delegation_chain" functions:
   1.778 +
   1.779 +CREATE TYPE "delegation_chain_row" AS (
   1.780 +        "index"                 INT4,
   1.781 +        "member_id"             INT4,
   1.782 +        "member_valid"          BOOLEAN,
   1.783 +        "participation"         BOOLEAN,
   1.784 +        "overridden"            BOOLEAN,
   1.785 +        "scope_in"              "delegation_scope",
   1.786 +        "scope_out"             "delegation_scope",
   1.787 +        "disabled_out"          BOOLEAN,
   1.788 +        "loop"                  "delegation_chain_loop_tag" );
   1.789 +
   1.790 +COMMENT ON TYPE "delegation_chain_row" IS 'Type of rows returned by "delegation_chain"(...) functions';
   1.791 +
   1.792 +COMMENT ON COLUMN "delegation_chain_row"."index"         IS 'Index starting with 0 and counting up';
   1.793 +COMMENT ON COLUMN "delegation_chain_row"."participation" IS 'In case of delegation chains for issues: interest, for areas: membership, for global delegation chains: always null';
   1.794 +COMMENT ON COLUMN "delegation_chain_row"."overridden"    IS 'True, if an entry with lower index has "participation" set to true';
   1.795 +COMMENT ON COLUMN "delegation_chain_row"."scope_in"      IS 'Scope of used incoming delegation';
   1.796 +COMMENT ON COLUMN "delegation_chain_row"."scope_out"     IS 'Scope of used outgoing delegation';
   1.797 +COMMENT ON COLUMN "delegation_chain_row"."disabled_out"  IS 'Outgoing delegation is explicitly disabled by a delegation with trustee_id set to NULL';
   1.798 +COMMENT ON COLUMN "delegation_chain_row"."loop"          IS 'Not null, if member is part of a loop, see "delegation_chain_loop_tag" type';
   1.799 +
   1.800 +
   1.801 +CREATE FUNCTION "delegation_chain"
   1.802 +  ( "member_id_p"           "member"."id"%TYPE,
   1.803 +    "unit_id_p"             "unit"."id"%TYPE,
   1.804 +    "area_id_p"             "area"."id"%TYPE,
   1.805 +    "issue_id_p"            "issue"."id"%TYPE,
   1.806 +    "simulate_trustee_id_p" "member"."id"%TYPE )
   1.807 +  RETURNS SETOF "delegation_chain_row"
   1.808 +  LANGUAGE 'plpgsql' STABLE AS $$
   1.809 +    DECLARE
   1.810 +      "scope_v"            "delegation_scope";
   1.811 +      "unit_id_v"          "unit"."id"%TYPE;
   1.812 +      "area_id_v"          "area"."id"%TYPE;
   1.813 +      "visited_member_ids" INT4[];  -- "member"."id"%TYPE[]
   1.814 +      "loop_member_id_v"   "member"."id"%TYPE;
   1.815 +      "output_row"         "delegation_chain_row";
   1.816 +      "output_rows"        "delegation_chain_row"[];
   1.817 +      "delegation_row"     "delegation"%ROWTYPE;
   1.818 +      "row_count"          INT4;
   1.819 +      "i"                  INT4;
   1.820 +      "loop_v"             BOOLEAN;
   1.821 +    BEGIN
   1.822 +      IF
   1.823 +        "unit_id_p" NOTNULL AND
   1.824 +        "area_id_p" ISNULL AND
   1.825 +        "issue_id_p" ISNULL
   1.826 +      THEN
   1.827 +        "scope_v" := 'unit';
   1.828 +        "unit_id_v" := "unit_id_p";
   1.829 +      ELSIF
   1.830 +        "unit_id_p" ISNULL AND
   1.831 +        "area_id_p" NOTNULL AND
   1.832 +        "issue_id_p" ISNULL
   1.833 +      THEN
   1.834 +        "scope_v" := 'area';
   1.835 +        "area_id_v" := "area_id_p";
   1.836 +        SELECT "unit_id" INTO "unit_id_v"
   1.837 +          FROM "area" WHERE "id" = "area_id_v";
   1.838 +      ELSIF
   1.839 +        "unit_id_p" ISNULL AND
   1.840 +        "area_id_p" ISNULL AND
   1.841 +        "issue_id_p" NOTNULL
   1.842 +      THEN
   1.843 +        "scope_v" := 'issue';
   1.844 +        SELECT "area_id" INTO "area_id_v"
   1.845 +          FROM "issue" WHERE "id" = "issue_id_p";
   1.846 +        SELECT "unit_id" INTO "unit_id_v"
   1.847 +          FROM "area"  WHERE "id" = "area_id_v";
   1.848 +      ELSE
   1.849 +        RAISE EXCEPTION 'Exactly one of unit_id_p, area_id_p, or issue_id_p must be NOTNULL.';
   1.850 +      END IF;
   1.851 +      "visited_member_ids" := '{}';
   1.852 +      "loop_member_id_v"   := NULL;
   1.853 +      "output_rows"        := '{}';
   1.854 +      "output_row"."index"         := 0;
   1.855 +      "output_row"."member_id"     := "member_id_p";
   1.856 +      "output_row"."member_valid"  := TRUE;
   1.857 +      "output_row"."participation" := FALSE;
   1.858 +      "output_row"."overridden"    := FALSE;
   1.859 +      "output_row"."disabled_out"  := FALSE;
   1.860 +      "output_row"."scope_out"     := NULL;
   1.861 +      LOOP
   1.862 +        IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN
   1.863 +          "loop_member_id_v" := "output_row"."member_id";
   1.864 +        ELSE
   1.865 +          "visited_member_ids" :=
   1.866 +            "visited_member_ids" || "output_row"."member_id";
   1.867 +        END IF;
   1.868 +        IF "output_row"."participation" THEN
   1.869 +          "output_row"."overridden" := TRUE;
   1.870 +        END IF;
   1.871 +        "output_row"."scope_in" := "output_row"."scope_out";
   1.872 +        IF EXISTS (
   1.873 +          SELECT NULL FROM "member" JOIN "privilege"
   1.874 +          ON "privilege"."member_id" = "member"."id"
   1.875 +          AND "privilege"."unit_id" = "unit_id_v"
   1.876 +          WHERE "id" = "output_row"."member_id"
   1.877 +          AND "member"."active" AND "privilege"."voting_right"
   1.878 +        ) THEN
   1.879 +          IF "scope_v" = 'unit' THEN
   1.880 +            SELECT * INTO "delegation_row" FROM "delegation"
   1.881 +              WHERE "truster_id" = "output_row"."member_id"
   1.882 +              AND "unit_id" = "unit_id_v";
   1.883 +          ELSIF "scope_v" = 'area' THEN
   1.884 +            "output_row"."participation" := EXISTS (
   1.885 +              SELECT NULL FROM "membership"
   1.886 +              WHERE "area_id" = "area_id_p"
   1.887 +              AND "member_id" = "output_row"."member_id"
   1.888 +            );
   1.889 +            SELECT * INTO "delegation_row" FROM "delegation"
   1.890 +              WHERE "truster_id" = "output_row"."member_id"
   1.891 +              AND (
   1.892 +                "unit_id" = "unit_id_v" OR
   1.893 +                "area_id" = "area_id_v"
   1.894 +              )
   1.895 +              ORDER BY "scope" DESC;
   1.896 +          ELSIF "scope_v" = 'issue' THEN
   1.897 +            "output_row"."participation" := EXISTS (
   1.898 +              SELECT NULL FROM "interest"
   1.899 +              WHERE "issue_id" = "issue_id_p"
   1.900 +              AND "member_id" = "output_row"."member_id"
   1.901 +            );
   1.902 +            SELECT * INTO "delegation_row" FROM "delegation"
   1.903 +              WHERE "truster_id" = "output_row"."member_id"
   1.904 +              AND (
   1.905 +                "unit_id" = "unit_id_v" OR
   1.906 +                "area_id" = "area_id_v" OR
   1.907 +                "issue_id" = "issue_id_p"
   1.908 +              )
   1.909 +              ORDER BY "scope" DESC;
   1.910 +          END IF;
   1.911 +        ELSE
   1.912 +          "output_row"."member_valid"  := FALSE;
   1.913 +          "output_row"."participation" := FALSE;
   1.914 +          "output_row"."scope_out"     := NULL;
   1.915 +          "delegation_row" := ROW(NULL);
   1.916 +        END IF;
   1.917 +        IF
   1.918 +          "output_row"."member_id" = "member_id_p" AND
   1.919 +          "simulate_trustee_id_p" NOTNULL
   1.920 +        THEN
   1.921 +          "output_row"."scope_out" := "scope_v";
   1.922 +          "output_rows" := "output_rows" || "output_row";
   1.923 +          "output_row"."member_id" := "simulate_trustee_id_p";
   1.924 +        ELSIF "delegation_row"."trustee_id" NOTNULL THEN
   1.925 +          "output_row"."scope_out" := "delegation_row"."scope";
   1.926 +          "output_rows" := "output_rows" || "output_row";
   1.927 +          "output_row"."member_id" := "delegation_row"."trustee_id";
   1.928 +        ELSIF "delegation_row"."scope" NOTNULL THEN
   1.929 +          "output_row"."scope_out" := "delegation_row"."scope";
   1.930 +          "output_row"."disabled_out" := TRUE;
   1.931 +          "output_rows" := "output_rows" || "output_row";
   1.932 +          EXIT;
   1.933 +        ELSE
   1.934 +          "output_row"."scope_out" := NULL;
   1.935 +          "output_rows" := "output_rows" || "output_row";
   1.936 +          EXIT;
   1.937 +        END IF;
   1.938 +        EXIT WHEN "loop_member_id_v" NOTNULL;
   1.939 +        "output_row"."index" := "output_row"."index" + 1;
   1.940 +      END LOOP;
   1.941 +      "row_count" := array_upper("output_rows", 1);
   1.942 +      "i"      := 1;
   1.943 +      "loop_v" := FALSE;
   1.944 +      LOOP
   1.945 +        "output_row" := "output_rows"["i"];
   1.946 +        EXIT WHEN "output_row" ISNULL;  -- NOTE: ISNULL and NOT ... NOTNULL produce different results!
   1.947 +        IF "loop_v" THEN
   1.948 +          IF "i" + 1 = "row_count" THEN
   1.949 +            "output_row"."loop" := 'last';
   1.950 +          ELSIF "i" = "row_count" THEN
   1.951 +            "output_row"."loop" := 'repetition';
   1.952 +          ELSE
   1.953 +            "output_row"."loop" := 'intermediate';
   1.954 +          END IF;
   1.955 +        ELSIF "output_row"."member_id" = "loop_member_id_v" THEN
   1.956 +          "output_row"."loop" := 'first';
   1.957 +          "loop_v" := TRUE;
   1.958 +        END IF;
   1.959 +        IF "scope_v" = 'unit' THEN
   1.960 +          "output_row"."participation" := NULL;
   1.961 +        END IF;
   1.962 +        RETURN NEXT "output_row";
   1.963 +        "i" := "i" + 1;
   1.964 +      END LOOP;
   1.965 +      RETURN;
   1.966 +    END;
   1.967 +  $$;
   1.968 +
   1.969 +COMMENT ON FUNCTION "delegation_chain"
   1.970 +  ( "member"."id"%TYPE,
   1.971 +    "unit"."id"%TYPE,
   1.972 +    "area"."id"%TYPE,
   1.973 +    "issue"."id"%TYPE,
   1.974 +    "member"."id"%TYPE )
   1.975 +  IS 'Helper function for frontends to display delegation chains; Not part of internal voting logic';
   1.976 +
   1.977 +
   1.978 +CREATE FUNCTION "delegation_chain"
   1.979 +  ( "member_id_p" "member"."id"%TYPE,
   1.980 +    "unit_id_p"   "unit"."id"%TYPE,
   1.981 +    "area_id_p"   "area"."id"%TYPE,
   1.982 +    "issue_id_p"  "issue"."id"%TYPE )
   1.983 +  RETURNS SETOF "delegation_chain_row"
   1.984 +  LANGUAGE 'plpgsql' STABLE AS $$
   1.985 +    DECLARE
   1.986 +      "result_row" "delegation_chain_row";
   1.987 +    BEGIN
   1.988 +      FOR "result_row" IN
   1.989 +        SELECT * FROM "delegation_chain"(
   1.990 +          "member_id_p", "unit_id_p", "area_id_p", "issue_id_p", NULL
   1.991 +        )
   1.992 +      LOOP
   1.993 +        RETURN NEXT "result_row";
   1.994 +      END LOOP;
   1.995 +      RETURN;
   1.996 +    END;
   1.997 +  $$;
   1.998 +
   1.999 +COMMENT ON FUNCTION "delegation_chain"
  1.1000 +  ( "member"."id"%TYPE,
  1.1001 +    "unit"."id"%TYPE,
  1.1002 +    "area"."id"%TYPE,
  1.1003 +    "issue"."id"%TYPE )
  1.1004 +  IS 'Shortcut for "delegation_chain"(...) function where 4th parameter is null';
  1.1005 +
  1.1006 +
  1.1007 +-- Other modified functions:
  1.1008 +
  1.1009 +CREATE OR REPLACE FUNCTION "lock_issue"
  1.1010 +  ( "issue_id_p" "issue"."id"%TYPE )
  1.1011 +  RETURNS VOID
  1.1012 +  LANGUAGE 'plpgsql' VOLATILE AS $$
  1.1013 +    BEGIN
  1.1014 +      LOCK TABLE "member"     IN SHARE MODE;
  1.1015 +      LOCK TABLE "privilege"  IN SHARE MODE;
  1.1016 +      LOCK TABLE "membership" IN SHARE MODE;
  1.1017 +      LOCK TABLE "policy"     IN SHARE MODE;
  1.1018 +      PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE;
  1.1019 +      -- NOTE: The row-level exclusive lock in combination with the
  1.1020 +      -- share_row_lock_issue(_via_initiative)_trigger functions (which
  1.1021 +      -- acquire a row-level share lock on the issue) ensure that no data
  1.1022 +      -- is changed, which could affect calculation of snapshots or
  1.1023 +      -- counting of votes. Table "delegation" must be table-level-locked,
  1.1024 +      -- as it also contains issue- and global-scope delegations.
  1.1025 +      LOCK TABLE "delegation" IN SHARE MODE;
  1.1026 +      LOCK TABLE "direct_population_snapshot"     IN EXCLUSIVE MODE;
  1.1027 +      LOCK TABLE "delegating_population_snapshot" IN EXCLUSIVE MODE;
  1.1028 +      LOCK TABLE "direct_interest_snapshot"       IN EXCLUSIVE MODE;
  1.1029 +      LOCK TABLE "delegating_interest_snapshot"   IN EXCLUSIVE MODE;
  1.1030 +      LOCK TABLE "direct_supporter_snapshot"      IN EXCLUSIVE MODE;
  1.1031 +      RETURN;
  1.1032 +    END;
  1.1033 +  $$;
  1.1034 +
  1.1035 +CREATE OR REPLACE FUNCTION "calculate_member_counts"()
  1.1036 +  RETURNS VOID
  1.1037 +  LANGUAGE 'plpgsql' VOLATILE AS $$
  1.1038 +    BEGIN
  1.1039 +      LOCK TABLE "member"       IN SHARE MODE;
  1.1040 +      LOCK TABLE "member_count" IN EXCLUSIVE MODE;
  1.1041 +      LOCK TABLE "unit"         IN EXCLUSIVE MODE;
  1.1042 +      LOCK TABLE "area"         IN EXCLUSIVE MODE;
  1.1043 +      LOCK TABLE "privilege"    IN SHARE MODE;
  1.1044 +      LOCK TABLE "membership"   IN SHARE MODE;
  1.1045 +      DELETE FROM "member_count";
  1.1046 +      INSERT INTO "member_count" ("total_count")
  1.1047 +        SELECT "total_count" FROM "member_count_view";
  1.1048 +      UPDATE "unit" SET "member_count" = "view"."member_count"
  1.1049 +        FROM "unit_member_count" AS "view"
  1.1050 +        WHERE "view"."unit_id" = "unit"."id";
  1.1051 +      UPDATE "area" SET
  1.1052 +        "direct_member_count" = "view"."direct_member_count",
  1.1053 +        "member_weight"       = "view"."member_weight",
  1.1054 +        "autoreject_weight"   = "view"."autoreject_weight"
  1.1055 +        FROM "area_member_count" AS "view"
  1.1056 +        WHERE "view"."area_id" = "area"."id";
  1.1057 +      RETURN;
  1.1058 +    END;
  1.1059 +  $$;
  1.1060 +
  1.1061 +CREATE OR REPLACE FUNCTION "create_population_snapshot"
  1.1062 +  ( "issue_id_p" "issue"."id"%TYPE )
  1.1063 +  RETURNS VOID
  1.1064 +  LANGUAGE 'plpgsql' VOLATILE AS $$
  1.1065 +    DECLARE
  1.1066 +      "member_id_v" "member"."id"%TYPE;
  1.1067 +    BEGIN
  1.1068 +      DELETE FROM "direct_population_snapshot"
  1.1069 +        WHERE "issue_id" = "issue_id_p"
  1.1070 +        AND "event" = 'periodic';
  1.1071 +      DELETE FROM "delegating_population_snapshot"
  1.1072 +        WHERE "issue_id" = "issue_id_p"
  1.1073 +        AND "event" = 'periodic';
  1.1074 +      INSERT INTO "direct_population_snapshot"
  1.1075 +        ("issue_id", "event", "member_id")
  1.1076 +        SELECT
  1.1077 +          "issue_id_p"                 AS "issue_id",
  1.1078 +          'periodic'::"snapshot_event" AS "event",
  1.1079 +          "member"."id"                AS "member_id"
  1.1080 +        FROM "issue"
  1.1081 +        JOIN "area" ON "issue"."area_id" = "area"."id"
  1.1082 +        JOIN "membership" ON "area"."id" = "membership"."area_id"
  1.1083 +        JOIN "member" ON "membership"."member_id" = "member"."id"
  1.1084 +        JOIN "privilege"
  1.1085 +          ON "privilege"."unit_id" = "area"."unit_id"
  1.1086 +          AND "privilege"."member_id" = "member"."id"
  1.1087 +        WHERE "issue"."id" = "issue_id_p"
  1.1088 +        AND "member"."active" AND "privilege"."voting_right"
  1.1089 +        UNION
  1.1090 +        SELECT
  1.1091 +          "issue_id_p"                 AS "issue_id",
  1.1092 +          'periodic'::"snapshot_event" AS "event",
  1.1093 +          "member"."id"                AS "member_id"
  1.1094 +        FROM "issue"
  1.1095 +        JOIN "area" ON "issue"."area_id" = "area"."id"
  1.1096 +        JOIN "interest" ON "issue"."id" = "interest"."issue_id"
  1.1097 +        JOIN "member" ON "interest"."member_id" = "member"."id"
  1.1098 +        JOIN "privilege"
  1.1099 +          ON "privilege"."unit_id" = "area"."unit_id"
  1.1100 +          AND "privilege"."member_id" = "member"."id"
  1.1101 +        WHERE "issue"."id" = "issue_id_p"
  1.1102 +        AND "member"."active" AND "privilege"."voting_right";
  1.1103 +      FOR "member_id_v" IN
  1.1104 +        SELECT "member_id" FROM "direct_population_snapshot"
  1.1105 +        WHERE "issue_id" = "issue_id_p"
  1.1106 +        AND "event" = 'periodic'
  1.1107 +      LOOP
  1.1108 +        UPDATE "direct_population_snapshot" SET
  1.1109 +          "weight" = 1 +
  1.1110 +            "weight_of_added_delegations_for_population_snapshot"(
  1.1111 +              "issue_id_p",
  1.1112 +              "member_id_v",
  1.1113 +              '{}'
  1.1114 +            )
  1.1115 +          WHERE "issue_id" = "issue_id_p"
  1.1116 +          AND "event" = 'periodic'
  1.1117 +          AND "member_id" = "member_id_v";
  1.1118 +      END LOOP;
  1.1119 +      RETURN;
  1.1120 +    END;
  1.1121 +  $$;
  1.1122 +
  1.1123 +CREATE OR REPLACE FUNCTION "create_interest_snapshot"
  1.1124 +  ( "issue_id_p" "issue"."id"%TYPE )
  1.1125 +  RETURNS VOID
  1.1126 +  LANGUAGE 'plpgsql' VOLATILE AS $$
  1.1127 +    DECLARE
  1.1128 +      "member_id_v" "member"."id"%TYPE;
  1.1129 +    BEGIN
  1.1130 +      DELETE FROM "direct_interest_snapshot"
  1.1131 +        WHERE "issue_id" = "issue_id_p"
  1.1132 +        AND "event" = 'periodic';
  1.1133 +      DELETE FROM "delegating_interest_snapshot"
  1.1134 +        WHERE "issue_id" = "issue_id_p"
  1.1135 +        AND "event" = 'periodic';
  1.1136 +      DELETE FROM "direct_supporter_snapshot"
  1.1137 +        WHERE "issue_id" = "issue_id_p"
  1.1138 +        AND "event" = 'periodic';
  1.1139 +      INSERT INTO "direct_interest_snapshot"
  1.1140 +        ("issue_id", "event", "member_id", "voting_requested")
  1.1141 +        SELECT
  1.1142 +          "issue_id_p"  AS "issue_id",
  1.1143 +          'periodic'    AS "event",
  1.1144 +          "member"."id" AS "member_id",
  1.1145 +          "interest"."voting_requested"
  1.1146 +        FROM "issue"
  1.1147 +        JOIN "area" ON "issue"."area_id" = "area"."id"
  1.1148 +        JOIN "interest" ON "issue"."id" = "interest"."issue_id"
  1.1149 +        JOIN "member" ON "interest"."member_id" = "member"."id"
  1.1150 +        JOIN "privilege"
  1.1151 +          ON "privilege"."unit_id" = "area"."unit_id"
  1.1152 +          AND "privilege"."member_id" = "member"."id"
  1.1153 +        WHERE "issue"."id" = "issue_id_p"
  1.1154 +        AND "member"."active" AND "privilege"."voting_right";
  1.1155 +      FOR "member_id_v" IN
  1.1156 +        SELECT "member_id" FROM "direct_interest_snapshot"
  1.1157 +        WHERE "issue_id" = "issue_id_p"
  1.1158 +        AND "event" = 'periodic'
  1.1159 +      LOOP
  1.1160 +        UPDATE "direct_interest_snapshot" SET
  1.1161 +          "weight" = 1 +
  1.1162 +            "weight_of_added_delegations_for_interest_snapshot"(
  1.1163 +              "issue_id_p",
  1.1164 +              "member_id_v",
  1.1165 +              '{}'
  1.1166 +            )
  1.1167 +          WHERE "issue_id" = "issue_id_p"
  1.1168 +          AND "event" = 'periodic'
  1.1169 +          AND "member_id" = "member_id_v";
  1.1170 +      END LOOP;
  1.1171 +      INSERT INTO "direct_supporter_snapshot"
  1.1172 +        ( "issue_id", "initiative_id", "event", "member_id",
  1.1173 +          "informed", "satisfied" )
  1.1174 +        SELECT
  1.1175 +          "issue_id_p"            AS "issue_id",
  1.1176 +          "initiative"."id"       AS "initiative_id",
  1.1177 +          'periodic'              AS "event",
  1.1178 +          "supporter"."member_id" AS "member_id",
  1.1179 +          "supporter"."draft_id" = "current_draft"."id" AS "informed",
  1.1180 +          NOT EXISTS (
  1.1181 +            SELECT NULL FROM "critical_opinion"
  1.1182 +            WHERE "initiative_id" = "initiative"."id"
  1.1183 +            AND "member_id" = "supporter"."member_id"
  1.1184 +          ) AS "satisfied"
  1.1185 +        FROM "initiative"
  1.1186 +        JOIN "supporter"
  1.1187 +        ON "supporter"."initiative_id" = "initiative"."id"
  1.1188 +        JOIN "current_draft"
  1.1189 +        ON "initiative"."id" = "current_draft"."initiative_id"
  1.1190 +        JOIN "direct_interest_snapshot"
  1.1191 +        ON "supporter"."member_id" = "direct_interest_snapshot"."member_id"
  1.1192 +        AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
  1.1193 +        AND "event" = 'periodic'
  1.1194 +        WHERE "initiative"."issue_id" = "issue_id_p";
  1.1195 +      RETURN;
  1.1196 +    END;
  1.1197 +  $$;
  1.1198 +
  1.1199 +CREATE OR REPLACE FUNCTION "freeze_after_snapshot"
  1.1200 +  ( "issue_id_p" "issue"."id"%TYPE )
  1.1201 +  RETURNS VOID
  1.1202 +  LANGUAGE 'plpgsql' VOLATILE AS $$
  1.1203 +    DECLARE
  1.1204 +      "issue_row"      "issue"%ROWTYPE;
  1.1205 +      "policy_row"     "policy"%ROWTYPE;
  1.1206 +      "initiative_row" "initiative"%ROWTYPE;
  1.1207 +    BEGIN
  1.1208 +      SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
  1.1209 +      SELECT * INTO "policy_row"
  1.1210 +        FROM "policy" WHERE "id" = "issue_row"."policy_id";
  1.1211 +      PERFORM "set_snapshot_event"("issue_id_p", 'full_freeze');
  1.1212 +      FOR "initiative_row" IN
  1.1213 +        SELECT * FROM "initiative"
  1.1214 +        WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
  1.1215 +      LOOP
  1.1216 +        IF
  1.1217 +          "initiative_row"."satisfied_supporter_count" > 0 AND
  1.1218 +          "initiative_row"."satisfied_supporter_count" *
  1.1219 +          "policy_row"."initiative_quorum_den" >=
  1.1220 +          "issue_row"."population" * "policy_row"."initiative_quorum_num"
  1.1221 +        THEN
  1.1222 +          UPDATE "initiative" SET "admitted" = TRUE
  1.1223 +            WHERE "id" = "initiative_row"."id";
  1.1224 +        ELSE
  1.1225 +          UPDATE "initiative" SET "admitted" = FALSE
  1.1226 +            WHERE "id" = "initiative_row"."id";
  1.1227 +        END IF;
  1.1228 +      END LOOP;
  1.1229 +      IF EXISTS (
  1.1230 +        SELECT NULL FROM "initiative"
  1.1231 +        WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE
  1.1232 +      ) THEN
  1.1233 +        UPDATE "issue" SET
  1.1234 +          "state"        = 'voting',
  1.1235 +          "accepted"     = coalesce("accepted", now()),
  1.1236 +          "half_frozen"  = coalesce("half_frozen", now()),
  1.1237 +          "fully_frozen" = now()
  1.1238 +          WHERE "id" = "issue_id_p";
  1.1239 +      ELSE
  1.1240 +        UPDATE "issue" SET
  1.1241 +          "state"           = 'canceled_no_initiative_admitted',
  1.1242 +          "accepted"        = coalesce("accepted", now()),
  1.1243 +          "half_frozen"     = coalesce("half_frozen", now()),
  1.1244 +          "fully_frozen"    = now(),
  1.1245 +          "closed"          = now(),
  1.1246 +          "ranks_available" = TRUE
  1.1247 +          WHERE "id" = "issue_id_p";
  1.1248 +        -- NOTE: The following DELETE statements have effect only when
  1.1249 +        --       issue state has been manipulated
  1.1250 +        DELETE FROM "direct_voter"     WHERE "issue_id" = "issue_id_p";
  1.1251 +        DELETE FROM "delegating_voter" WHERE "issue_id" = "issue_id_p";
  1.1252 +        DELETE FROM "battle"           WHERE "issue_id" = "issue_id_p";
  1.1253 +      END IF;
  1.1254 +      RETURN;
  1.1255 +    END;
  1.1256 +  $$;
  1.1257 +
  1.1258 +CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
  1.1259 +  RETURNS VOID
  1.1260 +  LANGUAGE 'plpgsql' VOLATILE AS $$
  1.1261 +    DECLARE
  1.1262 +      "area_id_v"   "area"."id"%TYPE;
  1.1263 +      "unit_id_v"   "unit"."id"%TYPE;
  1.1264 +      "member_id_v" "member"."id"%TYPE;
  1.1265 +    BEGIN
  1.1266 +      PERFORM "lock_issue"("issue_id_p");
  1.1267 +      SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
  1.1268 +      SELECT "unit_id" INTO "unit_id_v" FROM "area"  WHERE "id" = "area_id_v";
  1.1269 +      DELETE FROM "delegating_voter"
  1.1270 +        WHERE "issue_id" = "issue_id_p";
  1.1271 +      DELETE FROM "direct_voter"
  1.1272 +        WHERE "issue_id" = "issue_id_p"
  1.1273 +        AND "autoreject" = TRUE;
  1.1274 +      DELETE FROM "direct_voter"
  1.1275 +        USING (
  1.1276 +          SELECT
  1.1277 +            "direct_voter"."member_id"
  1.1278 +          FROM "direct_voter"
  1.1279 +          JOIN "member" ON "direct_voter"."member_id" = "member"."id"
  1.1280 +          LEFT JOIN "privilege"
  1.1281 +          ON "privilege"."unit_id" = "unit_id_v"
  1.1282 +          AND "privilege"."member_id" = "direct_voter"."member_id"
  1.1283 +          WHERE "direct_voter"."issue_id" = "issue_id_p" AND (
  1.1284 +            "member"."active" = FALSE OR
  1.1285 +            "privilege"."voting_right" ISNULL OR
  1.1286 +            "privilege"."voting_right" = FALSE
  1.1287 +          )
  1.1288 +        ) AS "subquery"
  1.1289 +        WHERE "direct_voter"."issue_id" = "issue_id_p"
  1.1290 +        AND "direct_voter"."member_id" = "subquery"."member_id";
  1.1291 +      UPDATE "direct_voter" SET "weight" = 1
  1.1292 +        WHERE "issue_id" = "issue_id_p";
  1.1293 +      PERFORM "add_vote_delegations"("issue_id_p");
  1.1294 +      FOR "member_id_v" IN
  1.1295 +        SELECT "interest"."member_id"
  1.1296 +          FROM "interest"
  1.1297 +          JOIN "member"
  1.1298 +            ON "interest"."member_id" = "member"."id"
  1.1299 +          JOIN "privilege"
  1.1300 +            ON "privilege"."unit_id" = "unit_id_v"
  1.1301 +            AND "privilege"."member_id" = "member"."id"
  1.1302 +          LEFT JOIN "direct_voter"
  1.1303 +            ON "interest"."member_id" = "direct_voter"."member_id"
  1.1304 +            AND "interest"."issue_id" = "direct_voter"."issue_id"
  1.1305 +          LEFT JOIN "delegating_voter"
  1.1306 +            ON "interest"."member_id" = "delegating_voter"."member_id"
  1.1307 +            AND "interest"."issue_id" = "delegating_voter"."issue_id"
  1.1308 +          WHERE "interest"."issue_id" = "issue_id_p"
  1.1309 +          AND "interest"."autoreject" = TRUE
  1.1310 +          AND "member"."active"
  1.1311 +          AND "privilege"."voting_right"
  1.1312 +          AND "direct_voter"."member_id" ISNULL
  1.1313 +          AND "delegating_voter"."member_id" ISNULL
  1.1314 +        UNION SELECT "membership"."member_id"
  1.1315 +          FROM "membership"
  1.1316 +          JOIN "member"
  1.1317 +            ON "membership"."member_id" = "member"."id"
  1.1318 +          JOIN "privilege"
  1.1319 +            ON "privilege"."unit_id" = "unit_id_v"
  1.1320 +            AND "privilege"."member_id" = "member"."id"
  1.1321 +          LEFT JOIN "interest"
  1.1322 +            ON "membership"."member_id" = "interest"."member_id"
  1.1323 +            AND "interest"."issue_id" = "issue_id_p"
  1.1324 +          LEFT JOIN "direct_voter"
  1.1325 +            ON "membership"."member_id" = "direct_voter"."member_id"
  1.1326 +            AND "direct_voter"."issue_id" = "issue_id_p"
  1.1327 +          LEFT JOIN "delegating_voter"
  1.1328 +            ON "membership"."member_id" = "delegating_voter"."member_id"
  1.1329 +            AND "delegating_voter"."issue_id" = "issue_id_p"
  1.1330 +          WHERE "membership"."area_id" = "area_id_v"
  1.1331 +          AND "membership"."autoreject" = TRUE
  1.1332 +          AND "member"."active"
  1.1333 +          AND "privilege"."voting_right"
  1.1334 +          AND "interest"."autoreject" ISNULL
  1.1335 +          AND "direct_voter"."member_id" ISNULL
  1.1336 +          AND "delegating_voter"."member_id" ISNULL
  1.1337 +      LOOP
  1.1338 +        INSERT INTO "direct_voter"
  1.1339 +          ("member_id", "issue_id", "weight", "autoreject") VALUES
  1.1340 +          ("member_id_v", "issue_id_p", 1, TRUE);
  1.1341 +        INSERT INTO "vote" (
  1.1342 +          "member_id",
  1.1343 +          "issue_id",
  1.1344 +          "initiative_id",
  1.1345 +          "grade"
  1.1346 +          ) SELECT
  1.1347 +            "member_id_v" AS "member_id",
  1.1348 +            "issue_id_p"  AS "issue_id",
  1.1349 +            "id"          AS "initiative_id",
  1.1350 +            -1            AS "grade"
  1.1351 +          FROM "initiative"
  1.1352 +          WHERE "issue_id" = "issue_id_p" AND "admitted";
  1.1353 +      END LOOP;
  1.1354 +      PERFORM "add_vote_delegations"("issue_id_p");
  1.1355 +      UPDATE "issue" SET
  1.1356 +        "state"  = 'calculation',
  1.1357 +        "closed" = now(),
  1.1358 +        "voter_count" = (
  1.1359 +          SELECT coalesce(sum("weight"), 0)
  1.1360 +          FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
  1.1361 +        )
  1.1362 +        WHERE "id" = "issue_id_p";
  1.1363 +      UPDATE "initiative" SET
  1.1364 +        "positive_votes" = "vote_counts"."positive_votes",
  1.1365 +        "negative_votes" = "vote_counts"."negative_votes",
  1.1366 +        "agreed" = CASE WHEN "majority_strict" THEN
  1.1367 +          "vote_counts"."positive_votes" * "majority_den" >
  1.1368 +          "majority_num" *
  1.1369 +          ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
  1.1370 +        ELSE
  1.1371 +          "vote_counts"."positive_votes" * "majority_den" >=
  1.1372 +          "majority_num" *
  1.1373 +          ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
  1.1374 +        END
  1.1375 +        FROM
  1.1376 +          ( SELECT
  1.1377 +              "initiative"."id" AS "initiative_id",
  1.1378 +              coalesce(
  1.1379 +                sum(
  1.1380 +                  CASE WHEN "grade" > 0 THEN "direct_voter"."weight" ELSE 0 END
  1.1381 +                ),
  1.1382 +                0
  1.1383 +              ) AS "positive_votes",
  1.1384 +              coalesce(
  1.1385 +                sum(
  1.1386 +                  CASE WHEN "grade" < 0 THEN "direct_voter"."weight" ELSE 0 END
  1.1387 +                ),
  1.1388 +                0
  1.1389 +              ) AS "negative_votes"
  1.1390 +            FROM "initiative"
  1.1391 +            JOIN "issue" ON "initiative"."issue_id" = "issue"."id"
  1.1392 +            JOIN "policy" ON "issue"."policy_id" = "policy"."id"
  1.1393 +            LEFT JOIN "direct_voter"
  1.1394 +              ON "direct_voter"."issue_id" = "initiative"."issue_id"
  1.1395 +            LEFT JOIN "vote"
  1.1396 +              ON "vote"."initiative_id" = "initiative"."id"
  1.1397 +              AND "vote"."member_id" = "direct_voter"."member_id"
  1.1398 +            WHERE "initiative"."issue_id" = "issue_id_p"
  1.1399 +            AND "initiative"."admitted"  -- NOTE: NULL case is handled too
  1.1400 +            GROUP BY "initiative"."id"
  1.1401 +          ) AS "vote_counts",
  1.1402 +          "issue",
  1.1403 +          "policy"
  1.1404 +        WHERE "vote_counts"."initiative_id" = "initiative"."id"
  1.1405 +        AND "issue"."id" = "initiative"."issue_id"
  1.1406 +        AND "policy"."id" = "issue"."policy_id";
  1.1407 +      -- NOTE: "closed" column of issue must be set at this point
  1.1408 +      DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
  1.1409 +      INSERT INTO "battle" (
  1.1410 +        "issue_id",
  1.1411 +        "winning_initiative_id", "losing_initiative_id",
  1.1412 +        "count"
  1.1413 +      ) SELECT
  1.1414 +        "issue_id",
  1.1415 +        "winning_initiative_id", "losing_initiative_id",
  1.1416 +        "count"
  1.1417 +        FROM "battle_view" WHERE "issue_id" = "issue_id_p";
  1.1418 +    END;
  1.1419 +  $$;
  1.1420 +
  1.1421 +CREATE OR REPLACE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE)
  1.1422 +  RETURNS VOID
  1.1423 +  LANGUAGE 'plpgsql' VOLATILE AS $$
  1.1424 +    DECLARE
  1.1425 +      "dimension_v"     INTEGER;
  1.1426 +      "vote_matrix"     INT4[][];  -- absolute votes
  1.1427 +      "matrix"          INT8[][];  -- defeat strength / best paths
  1.1428 +      "i"               INTEGER;
  1.1429 +      "j"               INTEGER;
  1.1430 +      "k"               INTEGER;
  1.1431 +      "battle_row"      "battle"%ROWTYPE;
  1.1432 +      "rank_ary"        INT4[];
  1.1433 +      "rank_v"          INT4;
  1.1434 +      "done_v"          INTEGER;
  1.1435 +      "winners_ary"     INTEGER[];
  1.1436 +      "initiative_id_v" "initiative"."id"%TYPE;
  1.1437 +    BEGIN
  1.1438 +      PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE;
  1.1439 +      SELECT count(1) INTO "dimension_v" FROM "initiative"
  1.1440 +        WHERE "issue_id" = "issue_id_p" AND "agreed";
  1.1441 +      IF "dimension_v" = 1 THEN
  1.1442 +        UPDATE "initiative" SET "rank" = 1
  1.1443 +          WHERE "issue_id" = "issue_id_p" AND "agreed";
  1.1444 +      ELSIF "dimension_v" > 1 THEN
  1.1445 +        -- Create "vote_matrix" with absolute number of votes in pairwise
  1.1446 +        -- comparison:
  1.1447 +        "vote_matrix" := "square_matrix_init_string"("dimension_v");  -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
  1.1448 +        "i" := 1;
  1.1449 +        "j" := 2;
  1.1450 +        FOR "battle_row" IN
  1.1451 +          SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"
  1.1452 +          ORDER BY "winning_initiative_id", "losing_initiative_id"
  1.1453 +        LOOP
  1.1454 +          "vote_matrix"["i"]["j"] := "battle_row"."count";
  1.1455 +          IF "j" = "dimension_v" THEN
  1.1456 +            "i" := "i" + 1;
  1.1457 +            "j" := 1;
  1.1458 +          ELSE
  1.1459 +            "j" := "j" + 1;
  1.1460 +            IF "j" = "i" THEN
  1.1461 +              "j" := "j" + 1;
  1.1462 +            END IF;
  1.1463 +          END IF;
  1.1464 +        END LOOP;
  1.1465 +        IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN
  1.1466 +          RAISE EXCEPTION 'Wrong battle count (should not happen)';
  1.1467 +        END IF;
  1.1468 +        -- Store defeat strengths in "matrix" using "defeat_strength"
  1.1469 +        -- function:
  1.1470 +        "matrix" := "square_matrix_init_string"("dimension_v");  -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
  1.1471 +        "i" := 1;
  1.1472 +        LOOP
  1.1473 +          "j" := 1;
  1.1474 +          LOOP
  1.1475 +            IF "i" != "j" THEN
  1.1476 +              "matrix"["i"]["j"] := "defeat_strength"(
  1.1477 +                "vote_matrix"["i"]["j"],
  1.1478 +                "vote_matrix"["j"]["i"]
  1.1479 +              );
  1.1480 +            END IF;
  1.1481 +            EXIT WHEN "j" = "dimension_v";
  1.1482 +            "j" := "j" + 1;
  1.1483 +          END LOOP;
  1.1484 +          EXIT WHEN "i" = "dimension_v";
  1.1485 +          "i" := "i" + 1;
  1.1486 +        END LOOP;
  1.1487 +        -- Find best paths:
  1.1488 +        "i" := 1;
  1.1489 +        LOOP
  1.1490 +          "j" := 1;
  1.1491 +          LOOP
  1.1492 +            IF "i" != "j" THEN
  1.1493 +              "k" := 1;
  1.1494 +              LOOP
  1.1495 +                IF "i" != "k" AND "j" != "k" THEN
  1.1496 +                  IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN
  1.1497 +                    IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN
  1.1498 +                      "matrix"["j"]["k"] := "matrix"["j"]["i"];
  1.1499 +                    END IF;
  1.1500 +                  ELSE
  1.1501 +                    IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN
  1.1502 +                      "matrix"["j"]["k"] := "matrix"["i"]["k"];
  1.1503 +                    END IF;
  1.1504 +                  END IF;
  1.1505 +                END IF;
  1.1506 +                EXIT WHEN "k" = "dimension_v";
  1.1507 +                "k" := "k" + 1;
  1.1508 +              END LOOP;
  1.1509 +            END IF;
  1.1510 +            EXIT WHEN "j" = "dimension_v";
  1.1511 +            "j" := "j" + 1;
  1.1512 +          END LOOP;
  1.1513 +          EXIT WHEN "i" = "dimension_v";
  1.1514 +          "i" := "i" + 1;
  1.1515 +        END LOOP;
  1.1516 +        -- Determine order of winners:
  1.1517 +        "rank_ary" := "array_init_string"("dimension_v");  -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
  1.1518 +        "rank_v" := 1;
  1.1519 +        "done_v" := 0;
  1.1520 +        LOOP
  1.1521 +          "winners_ary" := '{}';
  1.1522 +          "i" := 1;
  1.1523 +          LOOP
  1.1524 +            IF "rank_ary"["i"] ISNULL THEN
  1.1525 +              "j" := 1;
  1.1526 +              LOOP
  1.1527 +                IF
  1.1528 +                  "i" != "j" AND
  1.1529 +                  "rank_ary"["j"] ISNULL AND
  1.1530 +                  "matrix"["j"]["i"] > "matrix"["i"]["j"]
  1.1531 +                THEN
  1.1532 +                  -- someone else is better
  1.1533 +                  EXIT;
  1.1534 +                END IF;
  1.1535 +                IF "j" = "dimension_v" THEN
  1.1536 +                  -- noone is better
  1.1537 +                  "winners_ary" := "winners_ary" || "i";
  1.1538 +                  EXIT;
  1.1539 +                END IF;
  1.1540 +                "j" := "j" + 1;
  1.1541 +              END LOOP;
  1.1542 +            END IF;
  1.1543 +            EXIT WHEN "i" = "dimension_v";
  1.1544 +            "i" := "i" + 1;
  1.1545 +          END LOOP;
  1.1546 +          "i" := 1;
  1.1547 +          LOOP
  1.1548 +            "rank_ary"["winners_ary"["i"]] := "rank_v";
  1.1549 +            "done_v" := "done_v" + 1;
  1.1550 +            EXIT WHEN "i" = array_upper("winners_ary", 1);
  1.1551 +            "i" := "i" + 1;
  1.1552 +          END LOOP;
  1.1553 +          EXIT WHEN "done_v" = "dimension_v";
  1.1554 +          "rank_v" := "rank_v" + 1;
  1.1555 +        END LOOP;
  1.1556 +        -- write preliminary ranks:
  1.1557 +        "i" := 1;
  1.1558 +        FOR "initiative_id_v" IN
  1.1559 +          SELECT "id" FROM "initiative"
  1.1560 +          WHERE "issue_id" = "issue_id_p" AND "agreed"
  1.1561 +          ORDER BY "id"
  1.1562 +        LOOP
  1.1563 +          UPDATE "initiative" SET "rank" = "rank_ary"["i"]
  1.1564 +            WHERE "id" = "initiative_id_v";
  1.1565 +          "i" := "i" + 1;
  1.1566 +        END LOOP;
  1.1567 +        IF "i" != "dimension_v" + 1 THEN
  1.1568 +          RAISE EXCEPTION 'Wrong winner count (should not happen)';
  1.1569 +        END IF;
  1.1570 +        -- straighten ranks (start counting with 1, no equal ranks):
  1.1571 +        "rank_v" := 1;
  1.1572 +        FOR "initiative_id_v" IN
  1.1573 +          SELECT "id" FROM "initiative"
  1.1574 +          WHERE "issue_id" = "issue_id_p" AND "rank" NOTNULL
  1.1575 +          ORDER BY
  1.1576 +            "rank",
  1.1577 +            "vote_ratio"("positive_votes", "negative_votes") DESC,
  1.1578 +            "id"
  1.1579 +        LOOP
  1.1580 +          UPDATE "initiative" SET "rank" = "rank_v"
  1.1581 +            WHERE "id" = "initiative_id_v";
  1.1582 +          "rank_v" := "rank_v" + 1;
  1.1583 +        END LOOP;
  1.1584 +      END IF;
  1.1585 +      -- mark issue as finished
  1.1586 +      UPDATE "issue" SET
  1.1587 +        "state" =
  1.1588 +          CASE WHEN "dimension_v" = 0 THEN
  1.1589 +            'finished_without_winner'::"issue_state"
  1.1590 +          ELSE
  1.1591 +            'finished_with_winner'::"issue_state"
  1.1592 +          END,
  1.1593 +        "ranks_available" = TRUE
  1.1594 +        WHERE "id" = "issue_id_p";
  1.1595 +      RETURN;
  1.1596 +    END;
  1.1597 +  $$;
  1.1598 +
  1.1599 +CREATE OR REPLACE FUNCTION "check_issue"
  1.1600 +  ( "issue_id_p" "issue"."id"%TYPE )
  1.1601 +  RETURNS VOID
  1.1602 +  LANGUAGE 'plpgsql' VOLATILE AS $$
  1.1603 +    DECLARE
  1.1604 +      "issue_row"         "issue"%ROWTYPE;
  1.1605 +      "policy_row"        "policy"%ROWTYPE;
  1.1606 +      "voting_requested_v" BOOLEAN;
  1.1607 +    BEGIN
  1.1608 +      PERFORM "lock_issue"("issue_id_p");
  1.1609 +      SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
  1.1610 +      -- only process open issues:
  1.1611 +      IF "issue_row"."closed" ISNULL THEN
  1.1612 +        SELECT * INTO "policy_row" FROM "policy"
  1.1613 +          WHERE "id" = "issue_row"."policy_id";
  1.1614 +        -- create a snapshot, unless issue is already fully frozen:
  1.1615 +        IF "issue_row"."fully_frozen" ISNULL THEN
  1.1616 +          PERFORM "create_snapshot"("issue_id_p");
  1.1617 +          SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
  1.1618 +        END IF;
  1.1619 +        -- eventually close or accept issues, which have not been accepted:
  1.1620 +        IF "issue_row"."accepted" ISNULL THEN
  1.1621 +          IF EXISTS (
  1.1622 +            SELECT NULL FROM "initiative"
  1.1623 +            WHERE "issue_id" = "issue_id_p"
  1.1624 +            AND "supporter_count" > 0
  1.1625 +            AND "supporter_count" * "policy_row"."issue_quorum_den"
  1.1626 +            >= "issue_row"."population" * "policy_row"."issue_quorum_num"
  1.1627 +          ) THEN
  1.1628 +            -- accept issues, if supporter count is high enough
  1.1629 +            PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
  1.1630 +            -- NOTE: "issue_row" used later
  1.1631 +            "issue_row"."state" := 'discussion';
  1.1632 +            "issue_row"."accepted" := now();
  1.1633 +            UPDATE "issue" SET
  1.1634 +              "state"    = "issue_row"."state",
  1.1635 +              "accepted" = "issue_row"."accepted"
  1.1636 +              WHERE "id" = "issue_row"."id";
  1.1637 +          ELSIF
  1.1638 +            now() >= "issue_row"."created" + "issue_row"."admission_time"
  1.1639 +          THEN
  1.1640 +            -- close issues, if admission time has expired
  1.1641 +            PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
  1.1642 +            UPDATE "issue" SET
  1.1643 +              "state" = 'canceled_issue_not_accepted',
  1.1644 +              "closed" = now()
  1.1645 +              WHERE "id" = "issue_row"."id";
  1.1646 +          END IF;
  1.1647 +        END IF;
  1.1648 +        -- eventually half freeze issues:
  1.1649 +        IF
  1.1650 +          -- NOTE: issue can't be closed at this point, if it has been accepted
  1.1651 +          "issue_row"."accepted" NOTNULL AND
  1.1652 +          "issue_row"."half_frozen" ISNULL
  1.1653 +        THEN
  1.1654 +          SELECT
  1.1655 +            CASE
  1.1656 +              WHEN "vote_now" * 2 > "issue_row"."population" THEN
  1.1657 +                TRUE
  1.1658 +              WHEN "vote_later" * 2 > "issue_row"."population" THEN
  1.1659 +                FALSE
  1.1660 +              ELSE NULL
  1.1661 +            END
  1.1662 +            INTO "voting_requested_v"
  1.1663 +            FROM "issue" WHERE "id" = "issue_id_p";
  1.1664 +          IF
  1.1665 +            "voting_requested_v" OR (
  1.1666 +              "voting_requested_v" ISNULL AND
  1.1667 +              now() >= "issue_row"."accepted" + "issue_row"."discussion_time"
  1.1668 +            )
  1.1669 +          THEN
  1.1670 +            PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze');
  1.1671 +            -- NOTE: "issue_row" used later
  1.1672 +            "issue_row"."state" := 'verification';
  1.1673 +            "issue_row"."half_frozen" := now();
  1.1674 +            UPDATE "issue" SET
  1.1675 +              "state"       = "issue_row"."state",
  1.1676 +              "half_frozen" = "issue_row"."half_frozen"
  1.1677 +              WHERE "id" = "issue_row"."id";
  1.1678 +          END IF;
  1.1679 +        END IF;
  1.1680 +        -- close issues after some time, if all initiatives have been revoked:
  1.1681 +        IF
  1.1682 +          "issue_row"."closed" ISNULL AND
  1.1683 +          NOT EXISTS (
  1.1684 +            -- all initiatives are revoked
  1.1685 +            SELECT NULL FROM "initiative"
  1.1686 +            WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
  1.1687 +          ) AND (
  1.1688 +            -- and issue has not been accepted yet
  1.1689 +            "issue_row"."accepted" ISNULL OR
  1.1690 +            NOT EXISTS (
  1.1691 +              -- or no initiatives have been revoked lately
  1.1692 +              SELECT NULL FROM "initiative"
  1.1693 +              WHERE "issue_id" = "issue_id_p"
  1.1694 +              AND now() < "revoked" + "issue_row"."verification_time"
  1.1695 +            ) OR (
  1.1696 +              -- or verification time has elapsed
  1.1697 +              "issue_row"."half_frozen" NOTNULL AND
  1.1698 +              "issue_row"."fully_frozen" ISNULL AND
  1.1699 +              now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
  1.1700 +            )
  1.1701 +          )
  1.1702 +        THEN
  1.1703 +          -- NOTE: "issue_row" used later
  1.1704 +          IF "issue_row"."accepted" ISNULL THEN
  1.1705 +            "issue_row"."state" := 'canceled_revoked_before_accepted';
  1.1706 +          ELSIF "issue_row"."half_frozen" ISNULL THEN
  1.1707 +            "issue_row"."state" := 'canceled_after_revocation_during_discussion';
  1.1708 +          ELSE
  1.1709 +            "issue_row"."state" := 'canceled_after_revocation_during_verification';
  1.1710 +          END IF;
  1.1711 +          "issue_row"."closed" := now();
  1.1712 +          UPDATE "issue" SET
  1.1713 +            "state"  = "issue_row"."state",
  1.1714 +            "closed" = "issue_row"."closed"
  1.1715 +            WHERE "id" = "issue_row"."id";
  1.1716 +        END IF;
  1.1717 +        -- fully freeze issue after verification time:
  1.1718 +        IF
  1.1719 +          "issue_row"."half_frozen" NOTNULL AND
  1.1720 +          "issue_row"."fully_frozen" ISNULL AND
  1.1721 +          "issue_row"."closed" ISNULL AND
  1.1722 +          now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
  1.1723 +        THEN
  1.1724 +          PERFORM "freeze_after_snapshot"("issue_id_p");
  1.1725 +          -- NOTE: "issue" might change, thus "issue_row" has to be updated below
  1.1726 +        END IF;
  1.1727 +        SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
  1.1728 +        -- close issue by calling close_voting(...) after voting time:
  1.1729 +        IF
  1.1730 +          "issue_row"."closed" ISNULL AND
  1.1731 +          "issue_row"."fully_frozen" NOTNULL AND
  1.1732 +          now() >= "issue_row"."fully_frozen" + "issue_row"."voting_time"
  1.1733 +        THEN
  1.1734 +          PERFORM "close_voting"("issue_id_p");
  1.1735 +          -- calculate ranks will not consume much time and can be done now
  1.1736 +          PERFORM "calculate_ranks"("issue_id_p");
  1.1737 +        END IF;
  1.1738 +      END IF;
  1.1739 +      RETURN;
  1.1740 +    END;
  1.1741 +  $$;
  1.1742 +
  1.1743 +CREATE OR REPLACE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE)
  1.1744 +  RETURNS VOID
  1.1745 +  LANGUAGE 'plpgsql' VOLATILE AS $$
  1.1746 +    DECLARE
  1.1747 +      "issue_row" "issue"%ROWTYPE;
  1.1748 +    BEGIN
  1.1749 +      SELECT * INTO "issue_row"
  1.1750 +        FROM "issue" WHERE "id" = "issue_id_p"
  1.1751 +        FOR UPDATE;
  1.1752 +      IF "issue_row"."cleaned" ISNULL THEN
  1.1753 +        UPDATE "issue" SET
  1.1754 +          "state"           = 'voting',
  1.1755 +          "closed"          = NULL,
  1.1756 +          "ranks_available" = FALSE
  1.1757 +          WHERE "id" = "issue_id_p";
  1.1758 +        DELETE FROM "issue_comment"
  1.1759 +          WHERE "issue_id" = "issue_id_p";
  1.1760 +        DELETE FROM "voting_comment"
  1.1761 +          WHERE "issue_id" = "issue_id_p";
  1.1762 +        DELETE FROM "delegating_voter"
  1.1763 +          WHERE "issue_id" = "issue_id_p";
  1.1764 +        DELETE FROM "direct_voter"
  1.1765 +          WHERE "issue_id" = "issue_id_p";
  1.1766 +        DELETE FROM "delegating_interest_snapshot"
  1.1767 +          WHERE "issue_id" = "issue_id_p";
  1.1768 +        DELETE FROM "direct_interest_snapshot"
  1.1769 +          WHERE "issue_id" = "issue_id_p";
  1.1770 +        DELETE FROM "delegating_population_snapshot"
  1.1771 +          WHERE "issue_id" = "issue_id_p";
  1.1772 +        DELETE FROM "direct_population_snapshot"
  1.1773 +          WHERE "issue_id" = "issue_id_p";
  1.1774 +        DELETE FROM "non_voter"
  1.1775 +          WHERE "issue_id" = "issue_id_p";
  1.1776 +        DELETE FROM "delegation"
  1.1777 +          WHERE "issue_id" = "issue_id_p";
  1.1778 +        DELETE FROM "supporter"
  1.1779 +          WHERE "issue_id" = "issue_id_p";
  1.1780 +        UPDATE "issue" SET
  1.1781 +          "state"           = "issue_row"."state",
  1.1782 +          "closed"          = "issue_row"."closed",
  1.1783 +          "ranks_available" = "issue_row"."ranks_available",
  1.1784 +          "cleaned"         = now()
  1.1785 +          WHERE "id" = "issue_id_p";
  1.1786 +      END IF;
  1.1787 +      RETURN;
  1.1788 +    END;
  1.1789 +  $$;
  1.1790 +
  1.1791 +CREATE OR REPLACE FUNCTION "check_issue"
  1.1792 +  ( "issue_id_p" "issue"."id"%TYPE )
  1.1793 +  RETURNS VOID
  1.1794 +  LANGUAGE 'plpgsql' VOLATILE AS $$
  1.1795 +    DECLARE
  1.1796 +      "issue_row"         "issue"%ROWTYPE;
  1.1797 +      "policy_row"        "policy"%ROWTYPE;
  1.1798 +      "voting_requested_v" BOOLEAN;
  1.1799 +    BEGIN
  1.1800 +      PERFORM "lock_issue"("issue_id_p");
  1.1801 +      SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
  1.1802 +      -- only process open issues:
  1.1803 +      IF "issue_row"."closed" ISNULL THEN
  1.1804 +        SELECT * INTO "policy_row" FROM "policy"
  1.1805 +          WHERE "id" = "issue_row"."policy_id";
  1.1806 +        -- create a snapshot, unless issue is already fully frozen:
  1.1807 +        IF "issue_row"."fully_frozen" ISNULL THEN
  1.1808 +          PERFORM "create_snapshot"("issue_id_p");
  1.1809 +          SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
  1.1810 +        END IF;
  1.1811 +        -- eventually close or accept issues, which have not been accepted:
  1.1812 +        IF "issue_row"."accepted" ISNULL THEN
  1.1813 +          IF EXISTS (
  1.1814 +            SELECT NULL FROM "initiative"
  1.1815 +            WHERE "issue_id" = "issue_id_p"
  1.1816 +            AND "supporter_count" > 0
  1.1817 +            AND "supporter_count" * "policy_row"."issue_quorum_den"
  1.1818 +            >= "issue_row"."population" * "policy_row"."issue_quorum_num"
  1.1819 +          ) THEN
  1.1820 +            -- accept issues, if supporter count is high enough
  1.1821 +            PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
  1.1822 +            -- NOTE: "issue_row" used later
  1.1823 +            "issue_row"."state" := 'discussion';
  1.1824 +            "issue_row"."accepted" := now();
  1.1825 +            UPDATE "issue" SET
  1.1826 +              "state"    = "issue_row"."state",
  1.1827 +              "accepted" = "issue_row"."accepted"
  1.1828 +              WHERE "id" = "issue_row"."id";
  1.1829 +          ELSIF
  1.1830 +            now() >= "issue_row"."created" + "issue_row"."admission_time"
  1.1831 +          THEN
  1.1832 +            -- close issues, if admission time has expired
  1.1833 +            PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
  1.1834 +            UPDATE "issue" SET
  1.1835 +              "state" = 'canceled_issue_not_accepted',
  1.1836 +              "closed" = now()
  1.1837 +              WHERE "id" = "issue_row"."id";
  1.1838 +          END IF;
  1.1839 +        END IF;
  1.1840 +        -- eventually half freeze issues:
  1.1841 +        IF
  1.1842 +          -- NOTE: issue can't be closed at this point, if it has been accepted
  1.1843 +          "issue_row"."accepted" NOTNULL AND
  1.1844 +          "issue_row"."half_frozen" ISNULL
  1.1845 +        THEN
  1.1846 +          SELECT
  1.1847 +            CASE
  1.1848 +              WHEN "vote_now" * 2 > "issue_row"."population" THEN
  1.1849 +                TRUE
  1.1850 +              WHEN "vote_later" * 2 > "issue_row"."population" THEN
  1.1851 +                FALSE
  1.1852 +              ELSE NULL
  1.1853 +            END
  1.1854 +            INTO "voting_requested_v"
  1.1855 +            FROM "issue" WHERE "id" = "issue_id_p";
  1.1856 +          IF
  1.1857 +            "voting_requested_v" OR (
  1.1858 +              "voting_requested_v" ISNULL AND
  1.1859 +              now() >= "issue_row"."accepted" + "issue_row"."discussion_time"
  1.1860 +            )
  1.1861 +          THEN
  1.1862 +            PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze');
  1.1863 +            -- NOTE: "issue_row" used later
  1.1864 +            "issue_row"."state" := 'verification';
  1.1865 +            "issue_row"."half_frozen" := now();
  1.1866 +            UPDATE "issue" SET
  1.1867 +              "state"       = "issue_row"."state",
  1.1868 +              "half_frozen" = "issue_row"."half_frozen"
  1.1869 +              WHERE "id" = "issue_row"."id";
  1.1870 +          END IF;
  1.1871 +        END IF;
  1.1872 +        -- close issues after some time, if all initiatives have been revoked:
  1.1873 +        IF
  1.1874 +          "issue_row"."closed" ISNULL AND
  1.1875 +          NOT EXISTS (
  1.1876 +            -- all initiatives are revoked
  1.1877 +            SELECT NULL FROM "initiative"
  1.1878 +            WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
  1.1879 +          ) AND (
  1.1880 +            -- and issue has not been accepted yet
  1.1881 +            "issue_row"."accepted" ISNULL OR
  1.1882 +            NOT EXISTS (
  1.1883 +              -- or no initiatives have been revoked lately
  1.1884 +              SELECT NULL FROM "initiative"
  1.1885 +              WHERE "issue_id" = "issue_id_p"
  1.1886 +              AND now() < "revoked" + "issue_row"."verification_time"
  1.1887 +            ) OR (
  1.1888 +              -- or verification time has elapsed
  1.1889 +              "issue_row"."half_frozen" NOTNULL AND
  1.1890 +              "issue_row"."fully_frozen" ISNULL AND
  1.1891 +              now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
  1.1892 +            )
  1.1893 +          )
  1.1894 +        THEN
  1.1895 +          -- NOTE: "issue_row" used later
  1.1896 +          IF "issue_row"."accepted" ISNULL THEN
  1.1897 +            "issue_row"."state" := 'canceled_revoked_before_accepted';
  1.1898 +          ELSIF "issue_row"."half_frozen" ISNULL THEN
  1.1899 +            "issue_row"."state" := 'canceled_after_revocation_during_discussion';
  1.1900 +          ELSE
  1.1901 +            "issue_row"."state" := 'canceled_after_revocation_during_verification';
  1.1902 +          END IF;
  1.1903 +          "issue_row"."closed" := now();
  1.1904 +          UPDATE "issue" SET
  1.1905 +            "state"  = "issue_row"."state",
  1.1906 +            "closed" = "issue_row"."closed"
  1.1907 +            WHERE "id" = "issue_row"."id";
  1.1908 +        END IF;
  1.1909 +        -- fully freeze issue after verification time:
  1.1910 +        IF
  1.1911 +          "issue_row"."half_frozen" NOTNULL AND
  1.1912 +          "issue_row"."fully_frozen" ISNULL AND
  1.1913 +          "issue_row"."closed" ISNULL AND
  1.1914 +          now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
  1.1915 +        THEN
  1.1916 +          PERFORM "freeze_after_snapshot"("issue_id_p");
  1.1917 +          -- NOTE: "issue" might change, thus "issue_row" has to be updated below
  1.1918 +        END IF;
  1.1919 +        SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
  1.1920 +        -- close issue by calling close_voting(...) after voting time:
  1.1921 +        IF
  1.1922 +          "issue_row"."closed" ISNULL AND
  1.1923 +          "issue_row"."fully_frozen" NOTNULL AND
  1.1924 +          now() >= "issue_row"."fully_frozen" + "issue_row"."voting_time"
  1.1925 +        THEN
  1.1926 +          PERFORM "close_voting"("issue_id_p");
  1.1927 +          -- calculate ranks will not consume much time and can be done now
  1.1928 +          PERFORM "calculate_ranks"("issue_id_p");
  1.1929 +        END IF;
  1.1930 +      END IF;
  1.1931 +      RETURN;
  1.1932 +    END;
  1.1933 +  $$;
  1.1934 +
  1.1935 +CREATE OR REPLACE FUNCTION "delete_private_data"()
  1.1936 +  RETURNS VOID
  1.1937 +  LANGUAGE 'plpgsql' VOLATILE AS $$
  1.1938 +    BEGIN
  1.1939 +      UPDATE "member" SET
  1.1940 +        "last_login"                   = NULL,
  1.1941 +        "login"                        = NULL,
  1.1942 +        "password"                     = NULL,
  1.1943 +        "notify_email"                 = NULL,
  1.1944 +        "notify_email_unconfirmed"     = NULL,
  1.1945 +        "notify_email_secret"          = NULL,
  1.1946 +        "notify_email_secret_expiry"   = NULL,
  1.1947 +        "notify_email_lock_expiry"     = NULL,
  1.1948 +        "password_reset_secret"        = NULL,
  1.1949 +        "password_reset_secret_expiry" = NULL,
  1.1950 +        "organizational_unit"          = NULL,
  1.1951 +        "internal_posts"               = NULL,
  1.1952 +        "realname"                     = NULL,
  1.1953 +        "birthday"                     = NULL,
  1.1954 +        "address"                      = NULL,
  1.1955 +        "email"                        = NULL,
  1.1956 +        "xmpp_address"                 = NULL,
  1.1957 +        "website"                      = NULL,
  1.1958 +        "phone"                        = NULL,
  1.1959 +        "mobile_phone"                 = NULL,
  1.1960 +        "profession"                   = NULL,
  1.1961 +        "external_memberships"         = NULL,
  1.1962 +        "external_posts"               = NULL,
  1.1963 +        "statement"                    = NULL;
  1.1964 +      -- "text_search_data" is updated by triggers
  1.1965 +      DELETE FROM "invite_code";
  1.1966 +      DELETE FROM "setting";
  1.1967 +      DELETE FROM "setting_map";
  1.1968 +      DELETE FROM "member_relation_setting";
  1.1969 +      DELETE FROM "member_image";
  1.1970 +      DELETE FROM "contact";
  1.1971 +      DELETE FROM "ignored_member";
  1.1972 +      DELETE FROM "session";
  1.1973 +      DELETE FROM "area_setting";
  1.1974 +      DELETE FROM "issue_setting";
  1.1975 +      DELETE FROM "ignored_initiative";
  1.1976 +      DELETE FROM "initiative_setting";
  1.1977 +      DELETE FROM "suggestion_setting";
  1.1978 +      DELETE FROM "non_voter";
  1.1979 +      DELETE FROM "direct_voter" USING "issue"
  1.1980 +        WHERE "direct_voter"."issue_id" = "issue"."id"
  1.1981 +        AND "issue"."closed" ISNULL;
  1.1982 +      RETURN;
  1.1983 +    END;
  1.1984 +  $$;
  1.1985 +
  1.1986 +
  1.1987 +-- Delete old "delegation_scope" TYPE:
  1.1988 +
  1.1989 +DROP TYPE "delegation_scope_old";
  1.1990 +
  1.1991 +
  1.1992 +COMMIT;
  1.1993 +
  1.1994 +
  1.1995 +-- Generate issue states and add constraints:
  1.1996 +
  1.1997 +UPDATE "issue" SET "state" =
  1.1998 +  CASE
  1.1999 +  WHEN "closed" ISNULL THEN
  1.2000 +    CASE
  1.2001 +    WHEN "accepted" ISNULL THEN
  1.2002 +      'admission'::"issue_state"
  1.2003 +    WHEN "half_frozen" ISNULL THEN
  1.2004 +      'discussion'::"issue_state"
  1.2005 +    WHEN "fully_frozen" ISNULL THEN
  1.2006 +      'verification'::"issue_state"
  1.2007 +    ELSE
  1.2008 +      'voting'::"issue_state"
  1.2009 +    END
  1.2010 +  WHEN "fully_frozen" NOTNULL THEN
  1.2011 +    CASE
  1.2012 +    WHEN "fully_frozen" = "closed" THEN
  1.2013 +      'canceled_no_initiative_admitted'::"issue_state"
  1.2014 +    ELSE
  1.2015 +      'finished_without_winner'::"issue_state"  -- NOTE: corrected later
  1.2016 +    END
  1.2017 +  WHEN "half_frozen" NOTNULL THEN
  1.2018 +    'canceled_after_revocation_during_verification'::"issue_state"
  1.2019 +  WHEN "accepted" NOTNULL THEN
  1.2020 +    'canceled_after_revocation_during_discussion'::"issue_state"
  1.2021 +  ELSE
  1.2022 +    'canceled_revoked_before_accepted'::"issue_state"  -- NOTE: corrected later
  1.2023 +  END;
  1.2024 +UPDATE "issue" SET "state" = 'finished_with_winner'
  1.2025 +  FROM "initiative"
  1.2026 +  WHERE "issue"."id" = "initiative"."issue_id"
  1.2027 +  AND "issue"."state" = 'finished_without_winner'
  1.2028 +  AND "initiative"."agreed";
  1.2029 +UPDATE "issue" SET "state" = 'canceled_issue_not_accepted'
  1.2030 +  FROM "initiative"
  1.2031 +  WHERE "issue"."id" = "initiative"."issue_id"
  1.2032 +  AND "issue"."state" = 'canceled_revoked_before_accepted'
  1.2033 +  AND "initiative"."revoked" ISNULL;
  1.2034 +
  1.2035 +ALTER TABLE "issue" ALTER "state" SET NOT NULL;
  1.2036 +
  1.2037 +ALTER TABLE "issue" DROP CONSTRAINT "valid_state";
  1.2038 +ALTER TABLE "issue" ADD CONSTRAINT "valid_state" CHECK ((
  1.2039 +    ("accepted" ISNULL  AND "half_frozen" ISNULL  AND "fully_frozen" ISNULL  AND "closed" ISNULL  AND "ranks_available" = FALSE) OR
  1.2040 +    ("accepted" ISNULL  AND "half_frozen" ISNULL  AND "fully_frozen" ISNULL  AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
  1.2041 +    ("accepted" NOTNULL AND "half_frozen" ISNULL  AND "fully_frozen" ISNULL  AND "closed" ISNULL  AND "ranks_available" = FALSE) OR
  1.2042 +    ("accepted" NOTNULL AND "half_frozen" ISNULL  AND "fully_frozen" ISNULL  AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
  1.2043 +    ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL  AND "closed" ISNULL  AND "ranks_available" = FALSE) OR
  1.2044 +    ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL  AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
  1.2045 +    ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" ISNULL  AND "ranks_available" = FALSE) OR
  1.2046 +    ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
  1.2047 +    ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = TRUE)
  1.2048 +  ) AND (
  1.2049 +    ("state" = 'admission'    AND "closed" ISNULL AND "accepted" ISNULL) OR
  1.2050 +    ("state" = 'discussion'   AND "closed" ISNULL AND "accepted" NOTNULL AND "half_frozen" ISNULL) OR
  1.2051 +    ("state" = 'verification' AND "closed" ISNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL) OR
  1.2052 +    ("state" = 'voting'       AND "closed" ISNULL AND "fully_frozen" NOTNULL) OR
  1.2053 +    ("state" = 'canceled_revoked_before_accepted'              AND "closed" NOTNULL AND "accepted" ISNULL) OR
  1.2054 +    ("state" = 'canceled_issue_not_accepted'                   AND "closed" NOTNULL AND "accepted" ISNULL) OR
  1.2055 +    ("state" = 'canceled_after_revocation_during_discussion'   AND "closed" NOTNULL AND "half_frozen"  ISNULL) OR
  1.2056 +    ("state" = 'canceled_after_revocation_during_verification' AND "closed" NOTNULL AND "fully_frozen" ISNULL) OR
  1.2057 +    ("state" = 'calculation'                     AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = FALSE) OR
  1.2058 +    ("state" = 'canceled_no_initiative_admitted' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE) OR
  1.2059 +    ("state" = 'finished_without_winner'         AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE) OR
  1.2060 +    ("state" = 'finished_with_winner'            AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE)
  1.2061 +  ));
  1.2062 +
  1.2063 +
  1.2064 +-- Guess "revoked_by_member_id" values based on author of current draft and add constraint:
  1.2065 +
  1.2066 +UPDATE "initiative" SET "revoked_by_member_id" = "author_id"
  1.2067 +  FROM "current_draft"
  1.2068 +  WHERE "initiative"."id" = "current_draft"."initiative_id"
  1.2069 +  AND "initiative"."revoked" NOTNULL;
  1.2070 +
  1.2071 +ALTER TABLE "initiative" ADD
  1.2072 +  CONSTRAINT "all_or_none_of_revoked_and_revoked_by_member_id_must_be_null"
  1.2073 +  CHECK ("revoked" NOTNULL = "revoked_by_member_id" NOTNULL);
  1.2074 +
  1.2075 +
  1.2076 +-- Fill "unit_id" column with default value where neccessary and add constraints:
  1.2077 +
  1.2078 +UPDATE "delegation" SET "unit_id" = 1 WHERE "scope" = 'unit';
  1.2079 +
  1.2080 +ALTER TABLE "delegation" ADD CONSTRAINT "area_id_and_issue_id_set_according_to_scope"
  1.2081 +  CHECK (
  1.2082 +    ("scope" = 'unit'  AND "unit_id" NOTNULL AND "area_id" ISNULL  AND "issue_id" ISNULL ) OR
  1.2083 +    ("scope" = 'area'  AND "unit_id" ISNULL  AND "area_id" NOTNULL AND "issue_id" ISNULL ) OR
  1.2084 +    ("scope" = 'issue' AND "unit_id" ISNULL  AND "area_id" ISNULL  AND "issue_id" NOTNULL) );
  1.2085 +
  1.2086 +
  1.2087 +-- Filling of "event" table with old (reconstructed) events:
  1.2088 +
  1.2089 +DELETE FROM "event";
  1.2090 +SELECT setval('event_id_seq', 1, false);
  1.2091 +
  1.2092 +INSERT INTO "event"
  1.2093 +  ( "occurrence", "event", "member_id", "issue_id", "state",
  1.2094 +    "initiative_id", "draft_id", "suggestion_id" )
  1.2095 +  SELECT * FROM (
  1.2096 +    SELECT * FROM (
  1.2097 +      SELECT DISTINCT ON ("initiative"."id")
  1.2098 +        "timeline"."occurrence",
  1.2099 +        CASE WHEN "issue_creation"."issue_id" NOTNULL THEN
  1.2100 +          'initiative_created_in_new_issue'::"event_type"
  1.2101 +        ELSE
  1.2102 +          'initiative_created_in_existing_issue'::"event_type"
  1.2103 +        END,
  1.2104 +        "draft"."author_id",
  1.2105 +        "issue"."id",
  1.2106 +        CASE
  1.2107 +          WHEN "timeline"."occurrence" < "issue"."accepted" THEN
  1.2108 +            'admission'::"issue_state"
  1.2109 +          WHEN "timeline"."occurrence" < "issue"."half_frozen" THEN
  1.2110 +            'discussion'::"issue_state"
  1.2111 +          ELSE
  1.2112 +            'verification'::"issue_state"
  1.2113 +        END,
  1.2114 +        "initiative"."id",
  1.2115 +        "draft"."id",
  1.2116 +        NULL::INT8
  1.2117 +      FROM "timeline"
  1.2118 +      JOIN "initiative" ON "timeline"."initiative_id" = "initiative"."id"
  1.2119 +      JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
  1.2120 +      LEFT JOIN "timeline" AS "issue_creation"
  1.2121 +        ON "initiative"."issue_id" = "issue_creation"."issue_id"
  1.2122 +        AND "issue_creation"."event" = 'issue_created'
  1.2123 +        AND "timeline"."occurrence" = "issue_creation"."occurrence"
  1.2124 +      JOIN "draft"
  1.2125 +        ON "initiative"."id" = "draft"."initiative_id"
  1.2126 +      WHERE "timeline"."event" = 'initiative_created'
  1.2127 +      ORDER BY "initiative"."id", "draft"."id"
  1.2128 +    ) AS "subquery"  -- NOTE: subquery needed due to DISTINCT/ORDER
  1.2129 +  UNION ALL
  1.2130 +    SELECT
  1.2131 +      "timeline"."occurrence",
  1.2132 +      'issue_state_changed'::"event_type",
  1.2133 +      NULL,
  1.2134 +      "issue"."id",
  1.2135 +      CASE
  1.2136 +        WHEN "timeline"."event" IN (
  1.2137 +          'issue_canceled',
  1.2138 +          'issue_finished_without_voting',
  1.2139 +          'issue_finished_after_voting'
  1.2140 +        ) THEN
  1.2141 +          "issue"."state"
  1.2142 +        WHEN "timeline"."event" = 'issue_accepted' THEN
  1.2143 +          'discussion'::"issue_state"
  1.2144 +        WHEN "timeline"."event" = 'issue_half_frozen' THEN
  1.2145 +          'verification'::"issue_state"
  1.2146 +        WHEN "timeline"."event" = 'issue_voting_started' THEN
  1.2147 +          'voting'::"issue_state"
  1.2148 +      END,
  1.2149 +      NULL,
  1.2150 +      NULL,
  1.2151 +      NULL
  1.2152 +    FROM "timeline"
  1.2153 +    JOIN "issue" ON "timeline"."issue_id" = "issue"."id"
  1.2154 +    WHERE "timeline"."event" IN (
  1.2155 +      'issue_canceled',
  1.2156 +      'issue_accepted',
  1.2157 +      'issue_half_frozen',
  1.2158 +      'issue_finished_without_voting',
  1.2159 +      'issue_voting_started',
  1.2160 +      'issue_finished_after_voting' )
  1.2161 +  UNION ALL
  1.2162 +    SELECT
  1.2163 +      "timeline"."occurrence",
  1.2164 +      'initiative_revoked'::"event_type",
  1.2165 +      "initiative"."revoked_by_member_id",
  1.2166 +      "issue"."id",
  1.2167 +      CASE
  1.2168 +        WHEN "timeline"."occurrence" < "issue"."accepted" THEN
  1.2169 +          'admission'::"issue_state"
  1.2170 +        WHEN "timeline"."occurrence" < "issue"."half_frozen" THEN
  1.2171 +          'discussion'::"issue_state"
  1.2172 +        ELSE
  1.2173 +          'verification'::"issue_state"
  1.2174 +      END,
  1.2175 +      "initiative"."id",
  1.2176 +      "current_draft"."id",
  1.2177 +      NULL
  1.2178 +    FROM "timeline"
  1.2179 +    JOIN "initiative" ON "timeline"."initiative_id" = "initiative"."id"
  1.2180 +    JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
  1.2181 +    JOIN "current_draft" ON "initiative"."id" = "current_draft"."initiative_id"
  1.2182 +    WHERE "timeline"."event" = 'initiative_revoked'
  1.2183 +  UNION ALL
  1.2184 +    SELECT
  1.2185 +      "timeline"."occurrence",
  1.2186 +      'new_draft_created'::"event_type",
  1.2187 +      "draft"."author_id",
  1.2188 +      "issue"."id",
  1.2189 +      CASE
  1.2190 +        WHEN "timeline"."occurrence" < "issue"."accepted" THEN
  1.2191 +          'admission'::"issue_state"
  1.2192 +        WHEN "timeline"."occurrence" < "issue"."half_frozen" THEN
  1.2193 +          'discussion'::"issue_state"
  1.2194 +        ELSE
  1.2195 +          'verification'::"issue_state"
  1.2196 +      END,
  1.2197 +      "initiative"."id",
  1.2198 +      "draft"."id",
  1.2199 +      NULL
  1.2200 +    FROM "timeline"
  1.2201 +    JOIN "draft" ON "timeline"."draft_id" = "draft"."id"
  1.2202 +    JOIN "initiative" ON "draft"."initiative_id" = "initiative"."id"
  1.2203 +    JOIN "issue" ON "initiative"."issue_id" = "issue"."id"
  1.2204 +    LEFT JOIN "timeline" AS "initiative_creation"
  1.2205 +      ON "initiative"."id" = "initiative_creation"."initiative_id"
  1.2206 +      AND "initiative_creation"."event" = 'initiative_created'
  1.2207 +      AND "timeline"."occurrence" = "initiative_creation"."occurrence"
  1.2208 +    WHERE "timeline"."event" = 'draft_created'
  1.2209 +    AND "initiative_creation"."initiative_id" ISNULL
  1.2210 +  UNION ALL
  1.2211 +    SELECT
  1.2212 +      "timeline"."occurrence",
  1.2213 +      'suggestion_created'::"event_type",
  1.2214 +      "suggestion"."author_id",
  1.2215 +      "issue"."id",
  1.2216 +      CASE
  1.2217 +        WHEN "timeline"."occurrence" < "issue"."accepted" THEN
  1.2218 +          'admission'::"issue_state"
  1.2219 +        WHEN "timeline"."occurrence" < "issue"."half_frozen" THEN
  1.2220 +          'discussion'::"issue_state"
  1.2221 +        ELSE
  1.2222 +          'verification'::"issue_state"
  1.2223 +      END,
  1.2224 +      "initiative"."id",
  1.2225 +      NULL,
  1.2226 +      "suggestion"."id"
  1.2227 +    FROM "timeline"
  1.2228 +    JOIN "suggestion" ON "timeline"."suggestion_id" = "suggestion"."id"
  1.2229 +    JOIN "initiative" ON "suggestion"."initiative_id" = "initiative"."id"
  1.2230 +    JOIN "issue" ON "initiative"."issue_id" = "issue"."id"
  1.2231 +    WHERE "timeline"."event" = 'suggestion_created'
  1.2232 +  ) AS "subquery"
  1.2233 +  ORDER BY "occurrence";
  1.2234 +
  1.2235 +

Impressum / About Us