jbe@385: BEGIN;
jbe@385: 
jbe@385: CREATE OR REPLACE VIEW "liquid_feedback_version" AS
jbe@385:   SELECT * FROM (VALUES ('2.2.3', 2, 2, 3))
jbe@385:   AS "subquery"("string", "major", "minor", "revision");
jbe@385: 
jbe@385: DROP TABLE "internal_session_store";
jbe@385: 
jbe@385: CREATE TABLE "temporary_transaction_data" (
jbe@385:         PRIMARY KEY ("txid", "key"),
jbe@385:         "txid"                  INT8            DEFAULT txid_current(),
jbe@385:          "key"                   TEXT,
jbe@385:          "value"                 TEXT            NOT NULL );
jbe@385: 
jbe@385: COMMENT ON TABLE "temporary_transaction_data" IS 'Table to store temporary transaction data; shall be emptied before a transaction is committed';
jbe@385: 
jbe@385: COMMENT ON COLUMN "temporary_transaction_data"."txid" IS 'Value returned by function txid_current(); should be added to WHERE clause, when doing SELECT on this table, but ignored when doing DELETE on this table';
jbe@385: 
jbe@385: CREATE OR REPLACE FUNCTION "forbid_changes_on_closed_issue_trigger"()
jbe@385:   RETURNS TRIGGER
jbe@385:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@385:     DECLARE
jbe@385:       "issue_id_v" "issue"."id"%TYPE;
jbe@385:       "issue_row"  "issue"%ROWTYPE;
jbe@385:     BEGIN
jbe@385:       IF EXISTS (
jbe@385:         SELECT NULL FROM "temporary_transaction_data"
jbe@385:         WHERE "txid" = txid_current()
jbe@385:         AND "key" = 'override_protection_triggers'
jbe@385:         AND "value" = TRUE::TEXT
jbe@385:       ) THEN
jbe@385:         RETURN NULL;
jbe@385:       END IF;
jbe@385:       IF TG_OP = 'DELETE' THEN
jbe@385:         "issue_id_v" := OLD."issue_id";
jbe@385:       ELSE
jbe@385:         "issue_id_v" := NEW."issue_id";
jbe@385:       END IF;
jbe@385:       SELECT INTO "issue_row" * FROM "issue"
jbe@385:         WHERE "id" = "issue_id_v" FOR SHARE;
jbe@385:       IF (
jbe@385:         "issue_row"."closed" NOTNULL OR (
jbe@385:           "issue_row"."state" = 'voting' AND
jbe@385:           "issue_row"."phase_finished" NOTNULL
jbe@385:         )
jbe@385:       ) THEN
jbe@385:         IF
jbe@385:           TG_RELID = 'direct_voter'::regclass AND
jbe@385:           TG_OP = 'UPDATE'
jbe@385:         THEN
jbe@385:           IF
jbe@385:             OLD."issue_id"  = NEW."issue_id"  AND
jbe@385:             OLD."member_id" = NEW."member_id" AND
jbe@385:             OLD."weight" = NEW."weight"
jbe@385:           THEN
jbe@385:             RETURN NULL;  -- allows changing of voter comment
jbe@385:           END IF;
jbe@385:         END IF;
jbe@385:         RAISE EXCEPTION 'Tried to modify data after voting has been closed.';
jbe@385:       END IF;
jbe@385:       RETURN NULL;
jbe@385:     END;
jbe@385:   $$;
jbe@385: 
jbe@385: CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
jbe@385:   RETURNS VOID
jbe@385:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@385:     DECLARE
jbe@385:       "area_id_v"   "area"."id"%TYPE;
jbe@385:       "unit_id_v"   "unit"."id"%TYPE;
jbe@385:       "member_id_v" "member"."id"%TYPE;
jbe@385:     BEGIN
jbe@385:       PERFORM "require_transaction_isolation"();
jbe@385:       SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
jbe@385:       SELECT "unit_id" INTO "unit_id_v" FROM "area"  WHERE "id" = "area_id_v";
jbe@385:       -- override protection triggers:
jbe@385:       INSERT INTO "temporary_transaction_data" ("key", "value")
jbe@385:         VALUES ('override_protection_triggers', TRUE::TEXT);
jbe@385:       -- delete timestamp of voting comment:
jbe@385:       UPDATE "direct_voter" SET "comment_changed" = NULL
jbe@385:         WHERE "issue_id" = "issue_id_p";
jbe@385:       -- delete delegating votes (in cases of manual reset of issue state):
jbe@385:       DELETE FROM "delegating_voter"
jbe@385:         WHERE "issue_id" = "issue_id_p";
jbe@385:       -- delete votes from non-privileged voters:
jbe@385:       DELETE FROM "direct_voter"
jbe@385:         USING (
jbe@385:           SELECT
jbe@385:             "direct_voter"."member_id"
jbe@385:           FROM "direct_voter"
jbe@385:           JOIN "member" ON "direct_voter"."member_id" = "member"."id"
jbe@385:           LEFT JOIN "privilege"
jbe@385:           ON "privilege"."unit_id" = "unit_id_v"
jbe@385:           AND "privilege"."member_id" = "direct_voter"."member_id"
jbe@385:           WHERE "direct_voter"."issue_id" = "issue_id_p" AND (
jbe@385:             "member"."active" = FALSE OR
jbe@385:             "privilege"."voting_right" ISNULL OR
jbe@385:             "privilege"."voting_right" = FALSE
jbe@385:           )
jbe@385:         ) AS "subquery"
jbe@385:         WHERE "direct_voter"."issue_id" = "issue_id_p"
jbe@385:         AND "direct_voter"."member_id" = "subquery"."member_id";
jbe@385:       -- consider delegations:
jbe@385:       UPDATE "direct_voter" SET "weight" = 1
jbe@385:         WHERE "issue_id" = "issue_id_p";
jbe@385:       PERFORM "add_vote_delegations"("issue_id_p");
jbe@385:       -- finish overriding protection triggers (avoids garbage):
jbe@385:       DELETE FROM "temporary_transaction_data"
jbe@385:         WHERE "key" = 'override_protection_triggers';
jbe@385:       -- materialize battle_view:
jbe@385:       -- NOTE: "closed" column of issue must be set at this point
jbe@385:       DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
jbe@385:       INSERT INTO "battle" (
jbe@385:         "issue_id",
jbe@385:         "winning_initiative_id", "losing_initiative_id",
jbe@385:         "count"
jbe@385:       ) SELECT
jbe@385:         "issue_id",
jbe@385:         "winning_initiative_id", "losing_initiative_id",
jbe@385:         "count"
jbe@385:         FROM "battle_view" WHERE "issue_id" = "issue_id_p";
jbe@385:       -- set voter count:
jbe@385:       UPDATE "issue" SET
jbe@385:         "voter_count" = (
jbe@385:           SELECT coalesce(sum("weight"), 0)
jbe@385:           FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
jbe@385:         )
jbe@385:         WHERE "id" = "issue_id_p";
jbe@385:       -- copy "positive_votes" and "negative_votes" from "battle" table:
jbe@385:       UPDATE "initiative" SET
jbe@385:         "positive_votes" = "battle_win"."count",
jbe@385:         "negative_votes" = "battle_lose"."count"
jbe@385:         FROM "battle" AS "battle_win", "battle" AS "battle_lose"
jbe@385:         WHERE
jbe@385:           "battle_win"."issue_id" = "issue_id_p" AND
jbe@385:           "battle_win"."winning_initiative_id" = "initiative"."id" AND
jbe@385:           "battle_win"."losing_initiative_id" ISNULL AND
jbe@385:           "battle_lose"."issue_id" = "issue_id_p" AND
jbe@385:           "battle_lose"."losing_initiative_id" = "initiative"."id" AND
jbe@385:           "battle_lose"."winning_initiative_id" ISNULL;
jbe@385:     END;
jbe@385:   $$;
jbe@385: 
jbe@385: CREATE OR REPLACE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE)
jbe@385:   RETURNS VOID
jbe@385:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@385:     BEGIN
jbe@385:       IF EXISTS (
jbe@385:         SELECT NULL FROM "issue" WHERE "id" = "issue_id_p" AND "cleaned" ISNULL
jbe@385:       ) THEN
jbe@385:         -- override protection triggers:
jbe@385:         INSERT INTO "temporary_transaction_data" ("key", "value")
jbe@385:           VALUES ('override_protection_triggers', TRUE::TEXT);
jbe@385:         -- clean data:
jbe@385:         DELETE FROM "delegating_voter"
jbe@385:           WHERE "issue_id" = "issue_id_p";
jbe@385:         DELETE FROM "direct_voter"
jbe@385:           WHERE "issue_id" = "issue_id_p";
jbe@385:         DELETE FROM "delegating_interest_snapshot"
jbe@385:           WHERE "issue_id" = "issue_id_p";
jbe@385:         DELETE FROM "direct_interest_snapshot"
jbe@385:           WHERE "issue_id" = "issue_id_p";
jbe@385:         DELETE FROM "delegating_population_snapshot"
jbe@385:           WHERE "issue_id" = "issue_id_p";
jbe@385:         DELETE FROM "direct_population_snapshot"
jbe@385:           WHERE "issue_id" = "issue_id_p";
jbe@385:         DELETE FROM "non_voter"
jbe@385:           WHERE "issue_id" = "issue_id_p";
jbe@385:         DELETE FROM "delegation"
jbe@385:           WHERE "issue_id" = "issue_id_p";
jbe@385:         DELETE FROM "supporter"
jbe@385:           USING "initiative"  -- NOTE: due to missing index on issue_id
jbe@385:           WHERE "initiative"."issue_id" = "issue_id_p"
jbe@385:           AND "supporter"."initiative_id" = "initiative_id";
jbe@385:         -- mark issue as cleaned:
jbe@385:         UPDATE "issue" SET "cleaned" = now() WHERE "id" = "issue_id_p";
jbe@385:         -- finish overriding protection triggers (avoids garbage):
jbe@385:         DELETE FROM "temporary_transaction_data"
jbe@385:           WHERE "key" = 'override_protection_triggers';
jbe@385:       END IF;
jbe@385:       RETURN;
jbe@385:     END;
jbe@385:   $$;
jbe@385: 
jbe@385: CREATE OR REPLACE FUNCTION "delete_private_data"()
jbe@385:   RETURNS VOID
jbe@385:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@385:     BEGIN
jbe@385:       DELETE FROM "temporary_transaction_data";
jbe@385:       DELETE FROM "member" WHERE "activated" ISNULL;
jbe@385:       UPDATE "member" SET
jbe@385:         "invite_code"                  = NULL,
jbe@385:         "invite_code_expiry"           = NULL,
jbe@385:         "admin_comment"                = NULL,
jbe@385:         "last_login"                   = NULL,
jbe@385:         "login"                        = NULL,
jbe@385:         "password"                     = NULL,
jbe@385:         "lang"                         = NULL,
jbe@385:         "notify_email"                 = NULL,
jbe@385:         "notify_email_unconfirmed"     = NULL,
jbe@385:         "notify_email_secret"          = NULL,
jbe@385:         "notify_email_secret_expiry"   = NULL,
jbe@385:         "notify_email_lock_expiry"     = NULL,
jbe@385:         "notify_level"                 = NULL,
jbe@385:         "password_reset_secret"        = NULL,
jbe@385:         "password_reset_secret_expiry" = NULL,
jbe@385:         "organizational_unit"          = NULL,
jbe@385:         "internal_posts"               = NULL,
jbe@385:         "realname"                     = NULL,
jbe@385:         "birthday"                     = NULL,
jbe@385:         "address"                      = NULL,
jbe@385:         "email"                        = NULL,
jbe@385:         "xmpp_address"                 = NULL,
jbe@385:         "website"                      = NULL,
jbe@385:         "phone"                        = NULL,
jbe@385:         "mobile_phone"                 = NULL,
jbe@385:         "profession"                   = NULL,
jbe@385:         "external_memberships"         = NULL,
jbe@385:         "external_posts"               = NULL,
jbe@385:         "formatting_engine"            = NULL,
jbe@385:         "statement"                    = NULL;
jbe@385:       -- "text_search_data" is updated by triggers
jbe@385:       DELETE FROM "setting";
jbe@385:       DELETE FROM "setting_map";
jbe@385:       DELETE FROM "member_relation_setting";
jbe@385:       DELETE FROM "member_image";
jbe@385:       DELETE FROM "contact";
jbe@385:       DELETE FROM "ignored_member";
jbe@385:       DELETE FROM "session";
jbe@385:       DELETE FROM "area_setting";
jbe@385:       DELETE FROM "issue_setting";
jbe@385:       DELETE FROM "ignored_initiative";
jbe@385:       DELETE FROM "initiative_setting";
jbe@385:       DELETE FROM "suggestion_setting";
jbe@385:       DELETE FROM "non_voter";
jbe@385:       DELETE FROM "direct_voter" USING "issue"
jbe@385:         WHERE "direct_voter"."issue_id" = "issue"."id"
jbe@385:         AND "issue"."closed" ISNULL;
jbe@385:       RETURN;
jbe@385:     END;
jbe@385:   $$;
jbe@385: 
jbe@385: COMMIT;