jbe@236: BEGIN; jbe@236: jbe@236: -- NOTE: "lf_update" needs to be recompiled to complete this update! jbe@236: jbe@236: CREATE OR REPLACE VIEW "liquid_feedback_version" AS jbe@236: SELECT * FROM (VALUES ('2.0.7', 2, 0, 7)) jbe@236: AS "subquery"("string", "major", "minor", "revision"); jbe@236: jbe@236: CREATE VIEW "expired_session" AS jbe@236: SELECT * FROM "session" WHERE now() > "expiry"; jbe@236: jbe@236: CREATE RULE "delete" AS ON DELETE TO "expired_session" DO INSTEAD jbe@236: DELETE FROM "session" WHERE "ident" = OLD."ident"; jbe@236: jbe@236: COMMENT ON VIEW "expired_session" IS 'View containing all expired sessions where DELETE is possible'; jbe@236: COMMENT ON RULE "delete" ON "expired_session" IS 'Rule allowing DELETE on rows in "expired_session" view, i.e. DELETE FROM "expired_session"'; jbe@236: jbe@236: CREATE OR REPLACE FUNCTION "check_everything"() jbe@236: RETURNS VOID jbe@236: LANGUAGE 'plpgsql' VOLATILE AS $$ jbe@236: DECLARE jbe@236: "issue_id_v" "issue"."id"%TYPE; jbe@236: BEGIN jbe@236: DELETE FROM "expired_session"; jbe@236: PERFORM "check_activity"(); jbe@236: PERFORM "calculate_member_counts"(); jbe@236: FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP jbe@236: PERFORM "check_issue"("issue_id_v"); jbe@236: END LOOP; jbe@236: FOR "issue_id_v" IN SELECT "id" FROM "issue_with_ranks_missing" LOOP jbe@236: PERFORM "calculate_ranks"("issue_id_v"); jbe@236: END LOOP; jbe@236: RETURN; jbe@236: END; jbe@236: $$; jbe@236: jbe@236: CREATE OR REPLACE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE) jbe@236: RETURNS VOID jbe@236: LANGUAGE 'plpgsql' VOLATILE AS $$ jbe@236: BEGIN jbe@236: UPDATE "member" SET jbe@236: "last_login" = NULL, jbe@236: "login" = NULL, jbe@236: "password" = NULL, jbe@236: "locked" = TRUE, jbe@236: "active" = FALSE, jbe@236: "notify_email" = NULL, jbe@236: "notify_email_unconfirmed" = NULL, jbe@236: "notify_email_secret" = NULL, jbe@236: "notify_email_secret_expiry" = NULL, jbe@236: "notify_email_lock_expiry" = NULL, jbe@236: "password_reset_secret" = NULL, jbe@236: "password_reset_secret_expiry" = NULL, jbe@236: "organizational_unit" = NULL, jbe@236: "internal_posts" = NULL, jbe@236: "realname" = NULL, jbe@236: "birthday" = NULL, jbe@236: "address" = NULL, jbe@236: "email" = NULL, jbe@236: "xmpp_address" = NULL, jbe@236: "website" = NULL, jbe@236: "phone" = NULL, jbe@236: "mobile_phone" = NULL, jbe@236: "profession" = NULL, jbe@236: "external_memberships" = NULL, jbe@236: "external_posts" = NULL, jbe@236: "statement" = NULL jbe@236: WHERE "id" = "member_id_p"; jbe@236: -- "text_search_data" is updated by triggers jbe@236: DELETE FROM "setting" WHERE "member_id" = "member_id_p"; jbe@236: DELETE FROM "setting_map" WHERE "member_id" = "member_id_p"; jbe@236: DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p"; jbe@236: DELETE FROM "member_image" WHERE "member_id" = "member_id_p"; jbe@236: DELETE FROM "contact" WHERE "member_id" = "member_id_p"; jbe@236: DELETE FROM "ignored_member" WHERE "member_id" = "member_id_p"; jbe@236: DELETE FROM "session" WHERE "member_id" = "member_id_p"; jbe@236: DELETE FROM "area_setting" WHERE "member_id" = "member_id_p"; jbe@236: DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p"; jbe@236: DELETE FROM "ignored_initiative" WHERE "member_id" = "member_id_p"; jbe@236: DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p"; jbe@236: DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p"; jbe@236: DELETE FROM "membership" WHERE "member_id" = "member_id_p"; jbe@236: DELETE FROM "delegation" WHERE "truster_id" = "member_id_p"; jbe@236: DELETE FROM "non_voter" WHERE "member_id" = "member_id_p"; jbe@236: DELETE FROM "direct_voter" USING "issue" jbe@236: WHERE "direct_voter"."issue_id" = "issue"."id" jbe@236: AND "issue"."closed" ISNULL jbe@236: AND "member_id" = "member_id_p"; jbe@236: RETURN; jbe@236: END; jbe@236: $$; jbe@236: jbe@236: CREATE OR REPLACE FUNCTION "delete_private_data"() jbe@236: RETURNS VOID jbe@236: LANGUAGE 'plpgsql' VOLATILE AS $$ jbe@236: BEGIN jbe@236: DELETE FROM "member" WHERE "activated" ISNULL; jbe@236: UPDATE "member" SET jbe@236: "invite_code" = NULL, jbe@236: "invite_code_expiry" = NULL, jbe@236: "admin_comment" = NULL, jbe@236: "last_login" = NULL, jbe@236: "login" = NULL, jbe@236: "password" = NULL, jbe@236: "notify_email" = NULL, jbe@236: "notify_email_unconfirmed" = NULL, jbe@236: "notify_email_secret" = NULL, jbe@236: "notify_email_secret_expiry" = NULL, jbe@236: "notify_email_lock_expiry" = NULL, jbe@236: "password_reset_secret" = NULL, jbe@236: "password_reset_secret_expiry" = NULL, jbe@236: "organizational_unit" = NULL, jbe@236: "internal_posts" = NULL, jbe@236: "realname" = NULL, jbe@236: "birthday" = NULL, jbe@236: "address" = NULL, jbe@236: "email" = NULL, jbe@236: "xmpp_address" = NULL, jbe@236: "website" = NULL, jbe@236: "phone" = NULL, jbe@236: "mobile_phone" = NULL, jbe@236: "profession" = NULL, jbe@236: "external_memberships" = NULL, jbe@236: "external_posts" = NULL, jbe@236: "statement" = NULL; jbe@236: -- "text_search_data" is updated by triggers jbe@236: DELETE FROM "setting"; jbe@236: DELETE FROM "setting_map"; jbe@236: DELETE FROM "member_relation_setting"; jbe@236: DELETE FROM "member_image"; jbe@236: DELETE FROM "contact"; jbe@236: DELETE FROM "ignored_member"; jbe@236: DELETE FROM "session"; jbe@236: DELETE FROM "area_setting"; jbe@236: DELETE FROM "issue_setting"; jbe@236: DELETE FROM "ignored_initiative"; jbe@236: DELETE FROM "initiative_setting"; jbe@236: DELETE FROM "suggestion_setting"; jbe@236: DELETE FROM "non_voter"; jbe@236: DELETE FROM "direct_voter" USING "issue" jbe@236: WHERE "direct_voter"."issue_id" = "issue"."id" jbe@236: AND "issue"."closed" ISNULL; jbe@236: RETURN; jbe@236: END; jbe@236: $$; jbe@236: jbe@236: COMMIT;