
jbe@54: BEGIN;
jbe@54: 
jbe@54: CREATE OR REPLACE VIEW "liquid_feedback_version" AS
jbe@54:   SELECT * FROM (VALUES ('1.1.0', 1, 1, 0))
jbe@54:   AS "subquery"("string", "major", "minor", "revision");
jbe@54: 
jbe@54: ALTER TABLE "direct_population_snapshot" DROP COLUMN "interest_exists";
jbe@54: 
jbe@54: CREATE OR REPLACE FUNCTION "create_population_snapshot"
jbe@54:   ( "issue_id_p" "issue"."id"%TYPE )
jbe@54:   RETURNS VOID
jbe@54:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@54:     DECLARE
jbe@54:       "member_id_v" "member"."id"%TYPE;
jbe@54:     BEGIN
jbe@54:       DELETE FROM "direct_population_snapshot"
jbe@54:         WHERE "issue_id" = "issue_id_p"
jbe@54:         AND "event" = 'periodic';
jbe@54:       DELETE FROM "delegating_population_snapshot"
jbe@54:         WHERE "issue_id" = "issue_id_p"
jbe@54:         AND "event" = 'periodic';
jbe@54:       INSERT INTO "direct_population_snapshot"
jbe@54:         ("issue_id", "event", "member_id")
jbe@54:         SELECT
jbe@54:           "issue_id_p"                 AS "issue_id",
jbe@54:           'periodic'::"snapshot_event" AS "event",
jbe@54:           "member"."id"                AS "member_id"
jbe@54:         FROM "issue"
jbe@54:         JOIN "area" ON "issue"."area_id" = "area"."id"
jbe@54:         JOIN "membership" ON "area"."id" = "membership"."area_id"
jbe@54:         JOIN "member" ON "membership"."member_id" = "member"."id"
jbe@54:         WHERE "issue"."id" = "issue_id_p"
jbe@54:         AND "member"."active"
jbe@54:         UNION
jbe@54:         SELECT
jbe@54:           "issue_id_p"                 AS "issue_id",
jbe@54:           'periodic'::"snapshot_event" AS "event",
jbe@54:           "member"."id"                AS "member_id"
jbe@54:         FROM "interest" JOIN "member"
jbe@54:         ON "interest"."member_id" = "member"."id"
jbe@54:         WHERE "interest"."issue_id" = "issue_id_p"
jbe@54:         AND "member"."active";
jbe@54:       FOR "member_id_v" IN
jbe@54:         SELECT "member_id" FROM "direct_population_snapshot"
jbe@54:         WHERE "issue_id" = "issue_id_p"
jbe@54:         AND "event" = 'periodic'
jbe@54:       LOOP
jbe@54:         UPDATE "direct_population_snapshot" SET
jbe@54:           "weight" = 1 +
jbe@54:             "weight_of_added_delegations_for_population_snapshot"(
jbe@54:               "issue_id_p",
jbe@54:               "member_id_v",
jbe@54:               '{}'
jbe@54:             )
jbe@54:           WHERE "issue_id" = "issue_id_p"
jbe@54:           AND "event" = 'periodic'
jbe@54:           AND "member_id" = "member_id_v";
jbe@54:       END LOOP;
jbe@54:       RETURN;
jbe@54:     END;
jbe@54:   $$;
jbe@54: 
jbe@54: DROP FUNCTION "delete_member_data"("member"."id"%TYPE);
jbe@54: 
jbe@54: CREATE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)
jbe@54:   RETURNS VOID
jbe@54:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@54:     BEGIN
jbe@54:       UPDATE "member" SET
jbe@54:         "login"                        = NULL,
jbe@54:         "password"                     = NULL,
jbe@54:         "active"                       = FALSE,
jbe@54:         "notify_email"                 = NULL,
jbe@54:         "notify_email_unconfirmed"     = NULL,
jbe@54:         "notify_email_secret"          = NULL,
jbe@54:         "notify_email_secret_expiry"   = NULL,
jbe@54:         "password_reset_secret"        = NULL,
jbe@54:         "password_reset_secret_expiry" = NULL,
jbe@54:         "organizational_unit"          = NULL,
jbe@54:         "internal_posts"               = NULL,
jbe@54:         "realname"                     = NULL,
jbe@54:         "birthday"                     = NULL,
jbe@54:         "address"                      = NULL,
jbe@54:         "email"                        = NULL,
jbe@54:         "xmpp_address"                 = NULL,
jbe@54:         "website"                      = NULL,
jbe@54:         "phone"                        = NULL,
jbe@54:         "mobile_phone"                 = NULL,
jbe@54:         "profession"                   = NULL,
jbe@54:         "external_memberships"         = NULL,
jbe@54:         "external_posts"               = NULL,
jbe@54:         "statement"                    = NULL
jbe@54:         WHERE "id" = "member_id_p";
jbe@54:       -- "text_search_data" is updated by triggers
jbe@54:       UPDATE "member_history" SET "login" = NULL
jbe@54:         WHERE "member_id" = "member_id_p";
jbe@54:       DELETE FROM "setting"            WHERE "member_id" = "member_id_p";
jbe@54:       DELETE FROM "setting_map"        WHERE "member_id" = "member_id_p";
jbe@54:       DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p";
jbe@54:       DELETE FROM "member_image"       WHERE "member_id" = "member_id_p";
jbe@54:       DELETE FROM "contact"            WHERE "member_id" = "member_id_p";
jbe@54:       DELETE FROM "area_setting"       WHERE "member_id" = "member_id_p";
jbe@54:       DELETE FROM "issue_setting"      WHERE "member_id" = "member_id_p";
jbe@54:       DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p";
jbe@54:       DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p";
jbe@54:       DELETE FROM "membership"         WHERE "member_id" = "member_id_p";
jbe@54:       DELETE FROM "delegation"         WHERE "truster_id" = "member_id_p";
jbe@54:       RETURN;
jbe@54:     END;
jbe@54:   $$;
jbe@54: 
jbe@54: COMMENT ON FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE) IS 'Clear certain settings and data of a particular member (data protection)';
jbe@54: 
jbe@54: CREATE OR REPLACE FUNCTION "delete_private_data"()
jbe@54:   RETURNS VOID
jbe@54:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@54:     BEGIN
jbe@54:       UPDATE "member" SET
jbe@54:         "login"                        = NULL,
jbe@54:         "password"                     = NULL,
jbe@54:         "notify_email"                 = NULL,
jbe@54:         "notify_email_unconfirmed"     = NULL,
jbe@54:         "notify_email_secret"          = NULL,
jbe@54:         "notify_email_secret_expiry"   = NULL,
jbe@54:         "password_reset_secret"        = NULL,
jbe@54:         "password_reset_secret_expiry" = NULL,
jbe@54:         "organizational_unit"          = NULL,
jbe@54:         "internal_posts"               = NULL,
jbe@54:         "realname"                     = NULL,
jbe@54:         "birthday"                     = NULL,
jbe@54:         "address"                      = NULL,
jbe@54:         "email"                        = NULL,
jbe@54:         "xmpp_address"                 = NULL,
jbe@54:         "website"                      = NULL,
jbe@54:         "phone"                        = NULL,
jbe@54:         "mobile_phone"                 = NULL,
jbe@54:         "profession"                   = NULL,
jbe@54:         "external_memberships"         = NULL,
jbe@54:         "external_posts"               = NULL,
jbe@54:         "statement"                    = NULL;
jbe@54:       -- "text_search_data" is updated by triggers
jbe@54:       UPDATE "member_history" SET "login" = NULL;
jbe@54:       DELETE FROM "invite_code";
jbe@54:       DELETE FROM "setting";
jbe@54:       DELETE FROM "setting_map";
jbe@54:       DELETE FROM "member_relation_setting";
jbe@54:       DELETE FROM "member_image";
jbe@54:       DELETE FROM "contact";
jbe@54:       DELETE FROM "session";
jbe@54:       DELETE FROM "area_setting";
jbe@54:       DELETE FROM "issue_setting";
jbe@54:       DELETE FROM "initiative_setting";
jbe@54:       DELETE FROM "suggestion_setting";
jbe@54:       DELETE FROM "direct_voter" USING "issue"
jbe@54:         WHERE "direct_voter"."issue_id" = "issue"."id"
jbe@54:         AND "issue"."closed" ISNULL;
jbe@54:       RETURN;
jbe@54:     END;
jbe@54:   $$;
jbe@54: 
jbe@54: COMMIT;




