1.1 --- /dev/null	Thu Jan 01 00:00:00 1970 +0000
     1.2 +++ b/update/core-update.v2.0.12-v2.1.0.sql	Fri Oct 05 13:45:37 2012 +0200
     1.3 @@ -0,0 +1,458 @@
     1.4 +BEGIN;
     1.5 +
     1.6 +
     1.7 +-- update version number
     1.8 +
     1.9 +CREATE OR REPLACE VIEW "liquid_feedback_version" AS
    1.10 +  SELECT * FROM (VALUES ('2.1.0', 2, 1, 0))
    1.11 +  AS "subquery"("string", "major", "minor", "revision");
    1.12 +
    1.13 +
    1.14 +-- old API tables are now deprecated
    1.15 +
    1.16 +COMMENT ON TYPE "application_access_level" IS 'DEPRECATED, WILL BE REMOVED! Access privileges for applications using the API';
    1.17 +COMMENT ON TABLE "member_application" IS 'DEPRECATED, WILL BE REMOVED! Registered application being allowed to use the API';
    1.18 +
    1.19 +
    1.20 +-- new polling mode and changed privileges
    1.21 +
    1.22 +ALTER TABLE "policy" ADD COLUMN "polling" BOOLEAN NOT NULL DEFAULT FALSE;
    1.23 +ALTER TABLE "policy" ALTER COLUMN "admission_time"    DROP NOT NULL;
    1.24 +ALTER TABLE "policy" ALTER COLUMN "discussion_time"   DROP NOT NULL;
    1.25 +ALTER TABLE "policy" ALTER COLUMN "verification_time" DROP NOT NULL;
    1.26 +ALTER TABLE "policy" ALTER COLUMN "voting_time"       DROP NOT NULL;
    1.27 +ALTER TABLE "policy" ALTER COLUMN "issue_quorum_num"  DROP NOT NULL;
    1.28 +ALTER TABLE "policy" ALTER COLUMN "issue_quorum_den"  DROP NOT NULL;
    1.29 +ALTER TABLE "policy" ADD CONSTRAINT "timing" CHECK (
    1.30 +          ( "polling" = FALSE AND
    1.31 +            "admission_time" NOTNULL AND "discussion_time" NOTNULL AND
    1.32 +            "verification_time" NOTNULL AND "voting_time" NOTNULL ) OR
    1.33 +          ( "polling" = TRUE AND
    1.34 +            "admission_time" ISNULL AND "discussion_time" NOTNULL AND
    1.35 +            "verification_time" NOTNULL AND "voting_time" NOTNULL ) OR
    1.36 +          ( "polling" = TRUE AND
    1.37 +            "admission_time" ISNULL AND "discussion_time" ISNULL AND
    1.38 +            "verification_time" ISNULL AND "voting_time" ISNULL ) );
    1.39 +ALTER TABLE "policy" ADD CONSTRAINT "issue_quorum_if_and_only_if_not_polling" CHECK (
    1.40 +          "polling" = "issue_quorum_num" ISNULL AND
    1.41 +          "polling" = "issue_quorum_den" ISNULL );
    1.42 +COMMENT ON COLUMN "policy"."polling" IS 'TRUE = special policy for non-user-generated issues without issue quorum, where certain initiatives (those having the "polling" flag set) do not need to pass the initiative quorum; "admission_time" MUST be set to NULL, the other timings may be set to NULL altogether, allowing individual timing for those issues';
    1.43 +
    1.44 +ALTER TABLE "issue" ALTER COLUMN "admission_time" DROP NOT NULL;
    1.45 +ALTER TABLE "issue" ADD CONSTRAINT "admission_time_not_null_unless_instantly_accepted" CHECK (
    1.46 +  "admission_time" NOTNULL OR ("accepted" NOTNULL AND "accepted" = "created") );
    1.47 +
    1.48 +ALTER TABLE "initiative" ADD COLUMN "polling" BOOLEAN NOT NULL DEFAULT FALSE;
    1.49 +COMMENT ON COLUMN "initiative"."polling" IS 'Initiative does not need to pass the initiative quorum (see "policy"."polling")';
    1.50 +
    1.51 +ALTER TABLE "privilege" RENAME COLUMN "voting_right_manager" TO "member_manager";
    1.52 +ALTER TABLE "privilege" ADD COLUMN "initiative_right" BOOLEAN NOT NULL DEFAULT TRUE;
    1.53 +ALTER TABLE "privilege" ADD COLUMN "polling_right"    BOOLEAN NOT NULL DEFAULT FALSE;
    1.54 +UPDATE "privilege" SET "initiative_right" = "voting_right";
    1.55 +COMMENT ON COLUMN "privilege"."admin_manager"    IS 'Grant/revoke any privileges to/from other members';
    1.56 +COMMENT ON COLUMN "privilege"."member_manager"   IS 'Adding/removing members from the unit, granting or revoking "initiative_right" and "voting_right"';
    1.57 +COMMENT ON COLUMN "privilege"."initiative_right" IS 'Right to create an initiative';
    1.58 +COMMENT ON COLUMN "privilege"."voting_right"     IS 'Right to support initiatives, create and rate suggestions, and to vote';
    1.59 +COMMENT ON COLUMN "privilege"."polling_right"    IS 'Right to create issues with policies having the "policy"."polling" flag set, and to add initiatives having the "initiative"."polling" flag set to those issues';
    1.60 +
    1.61 +DROP VIEW "member_contingent_left";
    1.62 +DROP VIEW "member_contingent";
    1.63 +ALTER TABLE "contingent" DROP CONSTRAINT "contingent_pkey";
    1.64 +ALTER TABLE "contingent" ALTER COLUMN "time_frame" DROP NOT NULL;
    1.65 +ALTER TABLE "contingent" ADD COLUMN "polling" BOOLEAN DEFAULT FALSE;
    1.66 +ALTER TABLE "contingent" ADD PRIMARY KEY ("polling", "time_frame");
    1.67 +ALTER TABLE "contingent" ALTER COLUMN "polling" DROP DEFAULT;
    1.68 +COMMENT ON COLUMN "contingent"."polling" IS 'Determines if settings are for creating initiatives and new drafts of initiatives with "polling" flag set';
    1.69 +
    1.70 +CREATE VIEW "member_contingent" AS
    1.71 +  SELECT
    1.72 +    "member"."id" AS "member_id",
    1.73 +    "contingent"."polling",
    1.74 +    "contingent"."time_frame",
    1.75 +    CASE WHEN "contingent"."text_entry_limit" NOTNULL THEN
    1.76 +      (
    1.77 +        SELECT count(1) FROM "draft"
    1.78 +        JOIN "initiative" ON "initiative"."id" = "draft"."initiative_id"
    1.79 +        WHERE "draft"."author_id" = "member"."id"
    1.80 +        AND "initiative"."polling" = "contingent"."polling"
    1.81 +        AND "draft"."created" > now() - "contingent"."time_frame"
    1.82 +      ) + (
    1.83 +        SELECT count(1) FROM "suggestion"
    1.84 +        JOIN "initiative" ON "initiative"."id" = "suggestion"."initiative_id"
    1.85 +        WHERE "suggestion"."author_id" = "member"."id"
    1.86 +        AND "contingent"."polling" = FALSE
    1.87 +        AND "suggestion"."created" > now() - "contingent"."time_frame"
    1.88 +      )
    1.89 +    ELSE NULL END AS "text_entry_count",
    1.90 +    "contingent"."text_entry_limit",
    1.91 +    CASE WHEN "contingent"."initiative_limit" NOTNULL THEN (
    1.92 +      SELECT count(1) FROM "opening_draft" AS "draft"
    1.93 +        JOIN "initiative" ON "initiative"."id" = "draft"."initiative_id"
    1.94 +      WHERE "draft"."author_id" = "member"."id"
    1.95 +      AND "initiative"."polling" = "contingent"."polling"
    1.96 +      AND "draft"."created" > now() - "contingent"."time_frame"
    1.97 +    ) ELSE NULL END AS "initiative_count",
    1.98 +    "contingent"."initiative_limit"
    1.99 +  FROM "member" CROSS JOIN "contingent";
   1.100 +
   1.101 +COMMENT ON VIEW "member_contingent" IS 'Actual counts of text entries and initiatives are calculated per member for each limit in the "contingent" table.';
   1.102 +
   1.103 +COMMENT ON COLUMN "member_contingent"."text_entry_count" IS 'Only calculated when "text_entry_limit" is not null in the same row';
   1.104 +COMMENT ON COLUMN "member_contingent"."initiative_count" IS 'Only calculated when "initiative_limit" is not null in the same row';
   1.105 +
   1.106 +CREATE VIEW "member_contingent_left" AS
   1.107 +  SELECT
   1.108 +    "member_id",
   1.109 +    "polling",
   1.110 +    max("text_entry_limit" - "text_entry_count") AS "text_entries_left",
   1.111 +    max("initiative_limit" - "initiative_count") AS "initiatives_left"
   1.112 +  FROM "member_contingent" GROUP BY "member_id", "polling";
   1.113 +
   1.114 +COMMENT ON VIEW "member_contingent_left" IS 'Amount of text entries or initiatives which can be posted now instantly by a member. This view should be used by a frontend to determine, if the contingent for posting is exhausted.';
   1.115 +
   1.116 +CREATE OR REPLACE FUNCTION "freeze_after_snapshot"
   1.117 +  ( "issue_id_p" "issue"."id"%TYPE )
   1.118 +  RETURNS VOID
   1.119 +  LANGUAGE 'plpgsql' VOLATILE AS $$
   1.120 +    DECLARE
   1.121 +      "issue_row"      "issue"%ROWTYPE;
   1.122 +      "policy_row"     "policy"%ROWTYPE;
   1.123 +      "initiative_row" "initiative"%ROWTYPE;
   1.124 +    BEGIN
   1.125 +      SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
   1.126 +      SELECT * INTO "policy_row"
   1.127 +        FROM "policy" WHERE "id" = "issue_row"."policy_id";
   1.128 +      PERFORM "set_snapshot_event"("issue_id_p", 'full_freeze');
   1.129 +      FOR "initiative_row" IN
   1.130 +        SELECT * FROM "initiative"
   1.131 +        WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
   1.132 +      LOOP
   1.133 +        IF
   1.134 +          "initiative_row"."polling" OR (
   1.135 +            "initiative_row"."satisfied_supporter_count" > 0 AND
   1.136 +            "initiative_row"."satisfied_supporter_count" *
   1.137 +            "policy_row"."initiative_quorum_den" >=
   1.138 +            "issue_row"."population" * "policy_row"."initiative_quorum_num"
   1.139 +          )
   1.140 +        THEN
   1.141 +          UPDATE "initiative" SET "admitted" = TRUE
   1.142 +            WHERE "id" = "initiative_row"."id";
   1.143 +        ELSE
   1.144 +          UPDATE "initiative" SET "admitted" = FALSE
   1.145 +            WHERE "id" = "initiative_row"."id";
   1.146 +        END IF;
   1.147 +      END LOOP;
   1.148 +      IF EXISTS (
   1.149 +        SELECT NULL FROM "initiative"
   1.150 +        WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE
   1.151 +      ) THEN
   1.152 +        UPDATE "issue" SET
   1.153 +          "state"        = 'voting',
   1.154 +          "accepted"     = coalesce("accepted", now()),
   1.155 +          "half_frozen"  = coalesce("half_frozen", now()),
   1.156 +          "fully_frozen" = now()
   1.157 +          WHERE "id" = "issue_id_p";
   1.158 +      ELSE
   1.159 +        UPDATE "issue" SET
   1.160 +          "state"           = 'canceled_no_initiative_admitted',
   1.161 +          "accepted"        = coalesce("accepted", now()),
   1.162 +          "half_frozen"     = coalesce("half_frozen", now()),
   1.163 +          "fully_frozen"    = now(),
   1.164 +          "closed"          = now(),
   1.165 +          "ranks_available" = TRUE
   1.166 +          WHERE "id" = "issue_id_p";
   1.167 +        -- NOTE: The following DELETE statements have effect only when
   1.168 +        --       issue state has been manipulated
   1.169 +        DELETE FROM "direct_voter"     WHERE "issue_id" = "issue_id_p";
   1.170 +        DELETE FROM "delegating_voter" WHERE "issue_id" = "issue_id_p";
   1.171 +        DELETE FROM "battle"           WHERE "issue_id" = "issue_id_p";
   1.172 +      END IF;
   1.173 +      RETURN;
   1.174 +    END;
   1.175 +  $$;
   1.176 +
   1.177 +
   1.178 +-- issue comments removed, voting comments integrated in "direct_voter" table
   1.179 +
   1.180 +ALTER TABLE "direct_voter" ADD COLUMN "comment_changed"   TIMESTAMPTZ;
   1.181 +ALTER TABLE "direct_voter" ADD COLUMN "formatting_engine" TEXT;
   1.182 +ALTER TABLE "direct_voter" ADD COLUMN "comment"           TEXT;
   1.183 +ALTER TABLE "direct_voter" ADD COLUMN "text_search_data"  TSVECTOR;
   1.184 +CREATE INDEX "direct_voter_text_search_data_idx" ON "direct_voter" USING gin ("text_search_data");
   1.185 +CREATE TRIGGER "update_text_search_data"
   1.186 +  BEFORE INSERT OR UPDATE ON "direct_voter"
   1.187 +  FOR EACH ROW EXECUTE PROCEDURE
   1.188 +  tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "comment");
   1.189 +
   1.190 +COMMENT ON COLUMN "direct_voter"."comment_changed"   IS 'Shall be set on comment change, to indicate a comment being modified after voting has been finished; Automatically set to NULL after voting phase; Automatically set to NULL by trigger, if "comment" is set to NULL';
   1.191 +COMMENT ON COLUMN "direct_voter"."formatting_engine" IS 'Allows different formatting engines (i.e. wiki formats) to be used for "direct_voter"."comment"; Automatically set to NULL by trigger, if "comment" is set to NULL';
   1.192 +COMMENT ON COLUMN "direct_voter"."comment"           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.193 +
   1.194 +CREATE TABLE "rendered_voter_comment" (
   1.195 +        PRIMARY KEY ("issue_id", "member_id", "format"),
   1.196 +        FOREIGN KEY ("issue_id", "member_id")
   1.197 +          REFERENCES "direct_voter" ("issue_id", "member_id")
   1.198 +          ON DELETE CASCADE ON UPDATE CASCADE,
   1.199 +        "issue_id"              INT4,
   1.200 +        "member_id"             INT4,
   1.201 +        "format"                TEXT,
   1.202 +        "content"               TEXT            NOT NULL );
   1.203 +
   1.204 +COMMENT ON TABLE "rendered_voter_comment" IS 'This table may be used by frontends to cache "rendered" voter comments (e.g. HTML output generated from wiki text)';
   1.205 +
   1.206 +DROP TABLE "rendered_issue_comment";
   1.207 +DROP TABLE "issue_comment";
   1.208 +DROP TABLE "rendered_voting_comment";
   1.209 +DROP TABLE "voting_comment";
   1.210 +
   1.211 +CREATE FUNCTION "voter_comment_fields_only_set_when_voter_comment_is_set_trigger"()
   1.212 +  RETURNS TRIGGER
   1.213 +  LANGUAGE 'plpgsql' VOLATILE AS $$
   1.214 +    BEGIN
   1.215 +      IF NEW."comment" ISNULL THEN
   1.216 +        NEW."comment_changed" := NULL;
   1.217 +        NEW."formatting_engine" := NULL;
   1.218 +      END IF;
   1.219 +      RETURN NEW;
   1.220 +    END;
   1.221 +  $$;
   1.222 +
   1.223 +CREATE TRIGGER "voter_comment_fields_only_set_when_voter_comment_is_set"
   1.224 +  BEFORE INSERT OR UPDATE ON "direct_voter"
   1.225 +  FOR EACH ROW EXECUTE PROCEDURE
   1.226 +  "voter_comment_fields_only_set_when_voter_comment_is_set_trigger"();
   1.227 +
   1.228 +COMMENT ON FUNCTION "voter_comment_fields_only_set_when_voter_comment_is_set_trigger"() IS 'Implementation of trigger "voter_comment_fields_only_set_when_voter_comment_is_set" ON table "direct_voter"';
   1.229 +COMMENT ON TRIGGER "voter_comment_fields_only_set_when_voter_comment_is_set" ON "direct_voter" IS 'If "comment" is set to NULL, then other comment related fields are also set to NULL.';
   1.230 +
   1.231 +CREATE OR REPLACE FUNCTION "forbid_changes_on_closed_issue_trigger"()
   1.232 +  RETURNS TRIGGER
   1.233 +  LANGUAGE 'plpgsql' VOLATILE AS $$
   1.234 +    DECLARE
   1.235 +      "issue_id_v" "issue"."id"%TYPE;
   1.236 +      "issue_row"  "issue"%ROWTYPE;
   1.237 +    BEGIN
   1.238 +      IF TG_RELID = 'direct_voter'::regclass AND TG_OP = 'UPDATE' THEN
   1.239 +        IF
   1.240 +          OLD."issue_id"  = NEW."issue_id"  AND
   1.241 +          OLD."member_id" = NEW."member_id" AND
   1.242 +          OLD."weight"    = NEW."weight"
   1.243 +        THEN
   1.244 +          RETURN NULL;  -- allows changing of voter comment
   1.245 +        END IF;
   1.246 +      END IF;
   1.247 +      IF TG_OP = 'DELETE' THEN
   1.248 +        "issue_id_v" := OLD."issue_id";
   1.249 +      ELSE
   1.250 +        "issue_id_v" := NEW."issue_id";
   1.251 +      END IF;
   1.252 +      SELECT INTO "issue_row" * FROM "issue"
   1.253 +        WHERE "id" = "issue_id_v" FOR SHARE;
   1.254 +      IF "issue_row"."closed" NOTNULL THEN
   1.255 +        RAISE EXCEPTION 'Tried to modify data belonging to a closed issue.';
   1.256 +      END IF;
   1.257 +      RETURN NULL;
   1.258 +    END;
   1.259 +  $$;
   1.260 +
   1.261 +CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
   1.262 +  RETURNS VOID
   1.263 +  LANGUAGE 'plpgsql' VOLATILE AS $$
   1.264 +    DECLARE
   1.265 +      "area_id_v"   "area"."id"%TYPE;
   1.266 +      "unit_id_v"   "unit"."id"%TYPE;
   1.267 +      "member_id_v" "member"."id"%TYPE;
   1.268 +    BEGIN
   1.269 +      PERFORM "lock_issue"("issue_id_p");
   1.270 +      SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
   1.271 +      SELECT "unit_id" INTO "unit_id_v" FROM "area"  WHERE "id" = "area_id_v";
   1.272 +      -- delete timestamp of voting comment:
   1.273 +      UPDATE "direct_voter" SET "comment_changed" = NULL
   1.274 +        WHERE "issue_id" = "issue_id_p";
   1.275 +      -- delete delegating votes (in cases of manual reset of issue state):
   1.276 +      DELETE FROM "delegating_voter"
   1.277 +        WHERE "issue_id" = "issue_id_p";
   1.278 +      -- delete votes from non-privileged voters:
   1.279 +      DELETE FROM "direct_voter"
   1.280 +        USING (
   1.281 +          SELECT
   1.282 +            "direct_voter"."member_id"
   1.283 +          FROM "direct_voter"
   1.284 +          JOIN "member" ON "direct_voter"."member_id" = "member"."id"
   1.285 +          LEFT JOIN "privilege"
   1.286 +          ON "privilege"."unit_id" = "unit_id_v"
   1.287 +          AND "privilege"."member_id" = "direct_voter"."member_id"
   1.288 +          WHERE "direct_voter"."issue_id" = "issue_id_p" AND (
   1.289 +            "member"."active" = FALSE OR
   1.290 +            "privilege"."voting_right" ISNULL OR
   1.291 +            "privilege"."voting_right" = FALSE
   1.292 +          )
   1.293 +        ) AS "subquery"
   1.294 +        WHERE "direct_voter"."issue_id" = "issue_id_p"
   1.295 +        AND "direct_voter"."member_id" = "subquery"."member_id";
   1.296 +      -- consider delegations:
   1.297 +      UPDATE "direct_voter" SET "weight" = 1
   1.298 +        WHERE "issue_id" = "issue_id_p";
   1.299 +      PERFORM "add_vote_delegations"("issue_id_p");
   1.300 +      -- set voter count and mark issue as being calculated:
   1.301 +      UPDATE "issue" SET
   1.302 +        "state"  = 'calculation',
   1.303 +        "closed" = now(),
   1.304 +        "voter_count" = (
   1.305 +          SELECT coalesce(sum("weight"), 0)
   1.306 +          FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
   1.307 +        )
   1.308 +        WHERE "id" = "issue_id_p";
   1.309 +      -- materialize battle_view:
   1.310 +      -- NOTE: "closed" column of issue must be set at this point
   1.311 +      DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
   1.312 +      INSERT INTO "battle" (
   1.313 +        "issue_id",
   1.314 +        "winning_initiative_id", "losing_initiative_id",
   1.315 +        "count"
   1.316 +      ) SELECT
   1.317 +        "issue_id",
   1.318 +        "winning_initiative_id", "losing_initiative_id",
   1.319 +        "count"
   1.320 +        FROM "battle_view" WHERE "issue_id" = "issue_id_p";
   1.321 +      -- copy "positive_votes" and "negative_votes" from "battle" table:
   1.322 +      UPDATE "initiative" SET
   1.323 +        "positive_votes" = "battle_win"."count",
   1.324 +        "negative_votes" = "battle_lose"."count"
   1.325 +        FROM "battle" AS "battle_win", "battle" AS "battle_lose"
   1.326 +        WHERE
   1.327 +          "battle_win"."issue_id" = "issue_id_p" AND
   1.328 +          "battle_win"."winning_initiative_id" = "initiative"."id" AND
   1.329 +          "battle_win"."losing_initiative_id" ISNULL AND
   1.330 +          "battle_lose"."issue_id" = "issue_id_p" AND
   1.331 +          "battle_lose"."losing_initiative_id" = "initiative"."id" AND
   1.332 +          "battle_lose"."winning_initiative_id" ISNULL;
   1.333 +    END;
   1.334 +  $$;
   1.335 +
   1.336 +CREATE OR REPLACE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE)
   1.337 +  RETURNS VOID
   1.338 +  LANGUAGE 'plpgsql' VOLATILE AS $$
   1.339 +    DECLARE
   1.340 +      "issue_row" "issue"%ROWTYPE;
   1.341 +    BEGIN
   1.342 +      SELECT * INTO "issue_row"
   1.343 +        FROM "issue" WHERE "id" = "issue_id_p"
   1.344 +        FOR UPDATE;
   1.345 +      IF "issue_row"."cleaned" ISNULL THEN
   1.346 +        UPDATE "issue" SET
   1.347 +          "state"           = 'voting',
   1.348 +          "closed"          = NULL,
   1.349 +          "ranks_available" = FALSE
   1.350 +          WHERE "id" = "issue_id_p";
   1.351 +        DELETE FROM "delegating_voter"
   1.352 +          WHERE "issue_id" = "issue_id_p";
   1.353 +        DELETE FROM "direct_voter"
   1.354 +          WHERE "issue_id" = "issue_id_p";
   1.355 +        DELETE FROM "delegating_interest_snapshot"
   1.356 +          WHERE "issue_id" = "issue_id_p";
   1.357 +        DELETE FROM "direct_interest_snapshot"
   1.358 +          WHERE "issue_id" = "issue_id_p";
   1.359 +        DELETE FROM "delegating_population_snapshot"
   1.360 +          WHERE "issue_id" = "issue_id_p";
   1.361 +        DELETE FROM "direct_population_snapshot"
   1.362 +          WHERE "issue_id" = "issue_id_p";
   1.363 +        DELETE FROM "non_voter"
   1.364 +          WHERE "issue_id" = "issue_id_p";
   1.365 +        DELETE FROM "delegation"
   1.366 +          WHERE "issue_id" = "issue_id_p";
   1.367 +        DELETE FROM "supporter"
   1.368 +          WHERE "issue_id" = "issue_id_p";
   1.369 +        UPDATE "issue" SET
   1.370 +          "state"           = "issue_row"."state",
   1.371 +          "closed"          = "issue_row"."closed",
   1.372 +          "ranks_available" = "issue_row"."ranks_available",
   1.373 +          "cleaned"         = now()
   1.374 +          WHERE "id" = "issue_id_p";
   1.375 +      END IF;
   1.376 +      RETURN;
   1.377 +    END;
   1.378 +  $$;
   1.379 +
   1.380 +
   1.381 +-- "non_voter" deletes "direct_voter" and vice versa
   1.382 +
   1.383 +CREATE FUNCTION "non_voter_deletes_direct_voter_trigger"()
   1.384 +  RETURNS TRIGGER
   1.385 +  LANGUAGE 'plpgsql' VOLATILE AS $$
   1.386 +    BEGIN
   1.387 +      DELETE FROM "direct_voter"
   1.388 +        WHERE "issue_id" = NEW."issue_id" AND "member_id" = NEW."member_id";
   1.389 +      RETURN NULL;
   1.390 +    END;
   1.391 +  $$;
   1.392 +
   1.393 +CREATE TRIGGER "non_voter_deletes_direct_voter"
   1.394 +  AFTER INSERT OR UPDATE ON "non_voter"
   1.395 +  FOR EACH ROW EXECUTE PROCEDURE
   1.396 +  "non_voter_deletes_direct_voter_trigger"();
   1.397 +
   1.398 +COMMENT ON FUNCTION "non_voter_deletes_direct_voter_trigger"()     IS 'Implementation of trigger "non_voter_deletes_direct_voter" on table "non_voter"';
   1.399 +COMMENT ON TRIGGER "non_voter_deletes_direct_voter" ON "non_voter" IS 'An entry in the "non_voter" table deletes an entry in the "direct_voter" table (and vice versa due to trigger "direct_voter_deletes_non_voter" on table "direct_voter")';
   1.400 +
   1.401 +CREATE FUNCTION "direct_voter_deletes_non_voter_trigger"()
   1.402 +  RETURNS TRIGGER
   1.403 +  LANGUAGE 'plpgsql' VOLATILE AS $$
   1.404 +    BEGIN
   1.405 +      DELETE FROM "non_voter"
   1.406 +        WHERE "issue_id" = NEW."issue_id" AND "member_id" = NEW."member_id";
   1.407 +      RETURN NULL;
   1.408 +    END;
   1.409 +  $$;
   1.410 +
   1.411 +CREATE TRIGGER "direct_voter_deletes_non_voter"
   1.412 +  AFTER INSERT OR UPDATE ON "direct_voter"
   1.413 +  FOR EACH ROW EXECUTE PROCEDURE
   1.414 +  "direct_voter_deletes_non_voter_trigger"();
   1.415 +
   1.416 +COMMENT ON FUNCTION "direct_voter_deletes_non_voter_trigger"()        IS 'Implementation of trigger "direct_voter_deletes_non_voter" on table "direct_voter"';
   1.417 +COMMENT ON TRIGGER "direct_voter_deletes_non_voter" ON "direct_voter" IS 'An entry in the "direct_voter" table deletes an entry in the "non_voter" table (and vice versa due to trigger "non_voter_deletes_direct_voter" on table "non_voter")';
   1.418 +
   1.419 +
   1.420 +-- different locking levels and different locking order to avoid deadlocks
   1.421 +
   1.422 +CREATE OR REPLACE FUNCTION "lock_issue"
   1.423 +  ( "issue_id_p" "issue"."id"%TYPE )
   1.424 +  RETURNS VOID
   1.425 +  LANGUAGE 'plpgsql' VOLATILE AS $$
   1.426 +    BEGIN
   1.427 +      -- The following locking order is used:
   1.428 +      -- 1st) row-level lock on the issue
   1.429 +      -- 2nd) table-level locks in order of occurrence in the core.sql file
   1.430 +      PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE;
   1.431 +      -- NOTE: The row-level exclusive lock in combination with the
   1.432 +      -- share_row_lock_issue(_via_initiative)_trigger functions (which
   1.433 +      -- acquire a row-level share lock on the issue) ensure that no data
   1.434 +      -- is changed, which could affect calculation of snapshots or
   1.435 +      -- counting of votes. Table "delegation" must be table-level-locked,
   1.436 +      -- as it also contains issue- and global-scope delegations.
   1.437 +      PERFORM NULL FROM "member" WHERE "active" FOR SHARE;
   1.438 +      -- NOTE: As we later cause implicit row-level share locks on many
   1.439 +      -- active members, we lock them before locking any other table
   1.440 +      -- to avoid deadlocks
   1.441 +      LOCK TABLE "member"     IN SHARE MODE;
   1.442 +      LOCK TABLE "privilege"  IN SHARE MODE;
   1.443 +      LOCK TABLE "membership" IN SHARE MODE;
   1.444 +      LOCK TABLE "policy"     IN SHARE MODE;
   1.445 +      LOCK TABLE "delegation" IN SHARE MODE;
   1.446 +      LOCK TABLE "direct_population_snapshot"     IN EXCLUSIVE MODE;
   1.447 +      LOCK TABLE "delegating_population_snapshot" IN EXCLUSIVE MODE;
   1.448 +      LOCK TABLE "direct_interest_snapshot"       IN EXCLUSIVE MODE;
   1.449 +      LOCK TABLE "delegating_interest_snapshot"   IN EXCLUSIVE MODE;
   1.450 +      LOCK TABLE "direct_supporter_snapshot"      IN EXCLUSIVE MODE;
   1.451 +      RETURN;
   1.452 +    END;
   1.453 +  $$;
   1.454 +
   1.455 +
   1.456 +-- new comment on function "delete_private_data"()
   1.457 +
   1.458 +COMMENT ON FUNCTION "delete_private_data"() IS 'Used by lf_export script. DO NOT USE on productive database, but only on a copy! This function deletes all data which should not be publicly available, and can be used to create a database dump for publication. See source code to see which data is deleted. If you need a different behaviour, copy this function and modify lf_export accordingly, to avoid data-leaks after updating.';
   1.459 +
   1.460 +
   1.461 +COMMIT;