jbe@387: BEGIN;
jbe@387: 
jbe@387: CREATE OR REPLACE VIEW "liquid_feedback_version" AS
jbe@387:   SELECT * FROM (VALUES ('2.2.4', 2, 2, 4))
jbe@387:   AS "subquery"("string", "major", "minor", "revision");
jbe@387: 
jbe@387: 
jbe@387: ALTER TABLE "member" ADD COLUMN "last_delegation_check" TIMESTAMPTZ;
jbe@387: ALTER TABLE "member" ADD COLUMN "login_recovery_expiry" TIMESTAMPTZ;
jbe@387: 
jbe@387: COMMENT ON COLUMN "member"."last_delegation_check" IS 'Timestamp of last delegation check (i.e. confirmation of all unit and area delegations)';
jbe@387: COMMENT ON COLUMN "member"."login_recovery_expiry"        IS 'Date/time after which another login recovery attempt is allowed';
jbe@387: COMMENT ON COLUMN "member"."password_reset_secret"        IS 'Secret string sent via e-mail for password recovery';
jbe@387: COMMENT ON COLUMN "member"."password_reset_secret_expiry" IS 'Date/time until the password recovery secret is valid, and date/time after which another password recovery attempt is allowed';
jbe@387: 
jbe@387: ALTER TABLE "session" ADD COLUMN "needs_delegation_check" BOOLEAN NOT NULL DEFAULT FALSE;
jbe@387: COMMENT ON COLUMN "session"."needs_delegation_check" IS 'Set to TRUE, if member must perform a delegation check to proceed with login; see column "last_delegation_check" in "member" table';
jbe@387:  
jbe@387: CREATE OR REPLACE VIEW "event_seen_by_member" AS
jbe@387:   SELECT
jbe@387:     "member"."id" AS "seen_by_member_id",
jbe@387:     CASE WHEN "event"."state" IN (
jbe@387:       'voting',
jbe@387:       'finished_without_winner',
jbe@387:       'finished_with_winner'
jbe@387:     ) THEN
jbe@387:       'voting'::"notify_level"
jbe@387:     ELSE
jbe@387:       CASE WHEN "event"."state" IN (
jbe@387:         'verification',
jbe@387:         'canceled_after_revocation_during_verification',
jbe@387:         'canceled_no_initiative_admitted'
jbe@387:       ) THEN
jbe@387:         'verification'::"notify_level"
jbe@387:       ELSE
jbe@387:         CASE WHEN "event"."state" IN (
jbe@387:           'discussion',
jbe@387:           'canceled_after_revocation_during_discussion'
jbe@387:         ) THEN
jbe@387:           'discussion'::"notify_level"
jbe@387:         ELSE
jbe@387:           'all'::"notify_level"
jbe@387:         END
jbe@387:       END
jbe@387:     END AS "notify_level",
jbe@387:     "event".*
jbe@387:   FROM "member" CROSS JOIN "event"
jbe@387:   LEFT JOIN "issue"
jbe@387:     ON "event"."issue_id" = "issue"."id"
jbe@387:   LEFT JOIN "membership"
jbe@387:     ON "member"."id" = "membership"."member_id"
jbe@387:     AND "issue"."area_id" = "membership"."area_id"
jbe@387:   LEFT JOIN "interest"
jbe@387:     ON "member"."id" = "interest"."member_id"
jbe@387:     AND "event"."issue_id" = "interest"."issue_id"
jbe@387:   LEFT JOIN "ignored_member"
jbe@387:     ON "member"."id" = "ignored_member"."member_id"
jbe@387:     AND "event"."member_id" = "ignored_member"."other_member_id"
jbe@387:   LEFT JOIN "ignored_initiative"
jbe@387:     ON "member"."id" = "ignored_initiative"."member_id"
jbe@387:     AND "event"."initiative_id" = "ignored_initiative"."initiative_id"
jbe@387:   WHERE (
jbe@387:     "interest"."member_id" NOTNULL OR
jbe@387:     ( "membership"."member_id" NOTNULL AND
jbe@387:       "event"."event" IN (
jbe@387:         'issue_state_changed',
jbe@387:         'initiative_created_in_new_issue',
jbe@387:         'initiative_created_in_existing_issue',
jbe@387:         'initiative_revoked' ) ) )
jbe@387:   AND "ignored_member"."member_id" ISNULL
jbe@387:   AND "ignored_initiative"."member_id" ISNULL;
jbe@387: 
jbe@387: CREATE OR REPLACE VIEW "selected_event_seen_by_member" AS
jbe@387:   SELECT
jbe@387:     "member"."id" AS "seen_by_member_id",
jbe@387:     CASE WHEN "event"."state" IN (
jbe@387:       'voting',
jbe@387:       'finished_without_winner',
jbe@387:       'finished_with_winner'
jbe@387:     ) THEN
jbe@387:       'voting'::"notify_level"
jbe@387:     ELSE
jbe@387:       CASE WHEN "event"."state" IN (
jbe@387:         'verification',
jbe@387:         'canceled_after_revocation_during_verification',
jbe@387:         'canceled_no_initiative_admitted'
jbe@387:       ) THEN
jbe@387:         'verification'::"notify_level"
jbe@387:       ELSE
jbe@387:         CASE WHEN "event"."state" IN (
jbe@387:           'discussion',
jbe@387:           'canceled_after_revocation_during_discussion'
jbe@387:         ) THEN
jbe@387:           'discussion'::"notify_level"
jbe@387:         ELSE
jbe@387:           'all'::"notify_level"
jbe@387:         END
jbe@387:       END
jbe@387:     END AS "notify_level",
jbe@387:     "event".*
jbe@387:   FROM "member" CROSS JOIN "event"
jbe@387:   LEFT JOIN "issue"
jbe@387:     ON "event"."issue_id" = "issue"."id"
jbe@387:   LEFT JOIN "membership"
jbe@387:     ON "member"."id" = "membership"."member_id"
jbe@387:     AND "issue"."area_id" = "membership"."area_id"
jbe@387:   LEFT JOIN "interest"
jbe@387:     ON "member"."id" = "interest"."member_id"
jbe@387:     AND "event"."issue_id" = "interest"."issue_id"
jbe@387:   LEFT JOIN "ignored_member"
jbe@387:     ON "member"."id" = "ignored_member"."member_id"
jbe@387:     AND "event"."member_id" = "ignored_member"."other_member_id"
jbe@387:   LEFT JOIN "ignored_initiative"
jbe@387:     ON "member"."id" = "ignored_initiative"."member_id"
jbe@387:     AND "event"."initiative_id" = "ignored_initiative"."initiative_id"
jbe@387:   WHERE (
jbe@387:     ( "member"."notify_level" >= 'all' ) OR
jbe@387:     ( "member"."notify_level" >= 'voting' AND
jbe@387:       "event"."state" IN (
jbe@387:         'voting',
jbe@387:         'finished_without_winner',
jbe@387:         'finished_with_winner' ) ) OR
jbe@387:     ( "member"."notify_level" >= 'verification' AND
jbe@387:       "event"."state" IN (
jbe@387:         'verification',
jbe@387:         'canceled_after_revocation_during_verification',
jbe@387:         'canceled_no_initiative_admitted' ) ) OR
jbe@387:     ( "member"."notify_level" >= 'discussion' AND
jbe@387:       "event"."state" IN (
jbe@387:         'discussion',
jbe@387:         'canceled_after_revocation_during_discussion' ) ) )
jbe@387:   AND (
jbe@387:     "interest"."member_id" NOTNULL OR
jbe@387:     ( "membership"."member_id" NOTNULL AND
jbe@387:       "event"."event" IN (
jbe@387:         'issue_state_changed',
jbe@387:         'initiative_created_in_new_issue',
jbe@387:         'initiative_created_in_existing_issue',
jbe@387:         'initiative_revoked' ) ) )
jbe@387:   AND "ignored_member"."member_id" ISNULL
jbe@387:   AND "ignored_initiative"."member_id" ISNULL;
jbe@387: 
jbe@387: CREATE OR REPLACE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)
jbe@387:   RETURNS VOID
jbe@387:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@387:     BEGIN
jbe@387:       UPDATE "member" SET
jbe@387:         "last_login"                   = NULL,
jbe@387:         "last_delegation_check"        = NULL,
jbe@387:         "login"                        = NULL,
jbe@387:         "password"                     = NULL,
jbe@387:         "locked"                       = TRUE,
jbe@387:         "active"                       = FALSE,
jbe@387:         "notify_email"                 = NULL,
jbe@387:         "notify_email_unconfirmed"     = NULL,
jbe@387:         "notify_email_secret"          = NULL,
jbe@387:         "notify_email_secret_expiry"   = NULL,
jbe@387:         "notify_email_lock_expiry"     = NULL,
jbe@387:         "login_recovery_expiry"        = NULL,
jbe@387:         "password_reset_secret"        = NULL,
jbe@387:         "password_reset_secret_expiry" = NULL,
jbe@387:         "organizational_unit"          = NULL,
jbe@387:         "internal_posts"               = NULL,
jbe@387:         "realname"                     = NULL,
jbe@387:         "birthday"                     = NULL,
jbe@387:         "address"                      = NULL,
jbe@387:         "email"                        = NULL,
jbe@387:         "xmpp_address"                 = NULL,
jbe@387:         "website"                      = NULL,
jbe@387:         "phone"                        = NULL,
jbe@387:         "mobile_phone"                 = NULL,
jbe@387:         "profession"                   = NULL,
jbe@387:         "external_memberships"         = NULL,
jbe@387:         "external_posts"               = NULL,
jbe@387:         "statement"                    = NULL
jbe@387:         WHERE "id" = "member_id_p";
jbe@387:       -- "text_search_data" is updated by triggers
jbe@387:       DELETE FROM "setting"            WHERE "member_id" = "member_id_p";
jbe@387:       DELETE FROM "setting_map"        WHERE "member_id" = "member_id_p";
jbe@387:       DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p";
jbe@387:       DELETE FROM "member_image"       WHERE "member_id" = "member_id_p";
jbe@387:       DELETE FROM "contact"            WHERE "member_id" = "member_id_p";
jbe@387:       DELETE FROM "ignored_member"     WHERE "member_id" = "member_id_p";
jbe@387:       DELETE FROM "session"            WHERE "member_id" = "member_id_p";
jbe@387:       DELETE FROM "area_setting"       WHERE "member_id" = "member_id_p";
jbe@387:       DELETE FROM "issue_setting"      WHERE "member_id" = "member_id_p";
jbe@387:       DELETE FROM "ignored_initiative" WHERE "member_id" = "member_id_p";
jbe@387:       DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p";
jbe@387:       DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p";
jbe@387:       DELETE FROM "membership"         WHERE "member_id" = "member_id_p";
jbe@387:       DELETE FROM "delegation"         WHERE "truster_id" = "member_id_p";
jbe@387:       DELETE FROM "non_voter"          WHERE "member_id" = "member_id_p";
jbe@387:       DELETE FROM "direct_voter" USING "issue"
jbe@387:         WHERE "direct_voter"."issue_id" = "issue"."id"
jbe@387:         AND "issue"."closed" ISNULL
jbe@387:         AND "member_id" = "member_id_p";
jbe@387:       RETURN;
jbe@387:     END;
jbe@387:   $$;
jbe@387: 
jbe@387: CREATE OR REPLACE FUNCTION "delete_private_data"()
jbe@387:   RETURNS VOID
jbe@387:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@387:     BEGIN
jbe@387:       DELETE FROM "temporary_transaction_data";
jbe@387:       DELETE FROM "member" WHERE "activated" ISNULL;
jbe@387:       UPDATE "member" SET
jbe@387:         "invite_code"                  = NULL,
jbe@387:         "invite_code_expiry"           = NULL,
jbe@387:         "admin_comment"                = NULL,
jbe@387:         "last_login"                   = NULL,
jbe@387:         "last_delegation_check"        = NULL,
jbe@387:         "login"                        = NULL,
jbe@387:         "password"                     = NULL,
jbe@387:         "lang"                         = NULL,
jbe@387:         "notify_email"                 = NULL,
jbe@387:         "notify_email_unconfirmed"     = NULL,
jbe@387:         "notify_email_secret"          = NULL,
jbe@387:         "notify_email_secret_expiry"   = NULL,
jbe@387:         "notify_email_lock_expiry"     = NULL,
jbe@387:         "notify_level"                 = NULL,
jbe@387:         "login_recovery_expiry"        = NULL,
jbe@387:         "password_reset_secret"        = NULL,
jbe@387:         "password_reset_secret_expiry" = NULL,
jbe@387:         "organizational_unit"          = NULL,
jbe@387:         "internal_posts"               = NULL,
jbe@387:         "realname"                     = NULL,
jbe@387:         "birthday"                     = NULL,
jbe@387:         "address"                      = NULL,
jbe@387:         "email"                        = NULL,
jbe@387:         "xmpp_address"                 = NULL,
jbe@387:         "website"                      = NULL,
jbe@387:         "phone"                        = NULL,
jbe@387:         "mobile_phone"                 = NULL,
jbe@387:         "profession"                   = NULL,
jbe@387:         "external_memberships"         = NULL,
jbe@387:         "external_posts"               = NULL,
jbe@387:         "formatting_engine"            = NULL,
jbe@387:         "statement"                    = NULL;
jbe@387:       -- "text_search_data" is updated by triggers
jbe@387:       DELETE FROM "setting";
jbe@387:       DELETE FROM "setting_map";
jbe@387:       DELETE FROM "member_relation_setting";
jbe@387:       DELETE FROM "member_image";
jbe@387:       DELETE FROM "contact";
jbe@387:       DELETE FROM "ignored_member";
jbe@387:       DELETE FROM "session";
jbe@387:       DELETE FROM "area_setting";
jbe@387:       DELETE FROM "issue_setting";
jbe@387:       DELETE FROM "ignored_initiative";
jbe@387:       DELETE FROM "initiative_setting";
jbe@387:       DELETE FROM "suggestion_setting";
jbe@387:       DELETE FROM "non_voter";
jbe@387:       DELETE FROM "direct_voter" USING "issue"
jbe@387:         WHERE "direct_voter"."issue_id" = "issue"."id"
jbe@387:         AND "issue"."closed" ISNULL;
jbe@387:       RETURN;
jbe@387:     END;
jbe@387:   $$;
jbe@387: 
jbe@387: COMMIT;