liquid_feedback_core

annotate update/core-update.v2.0.6-v2.0.7.sql @ 610:22c6d1da7d78

find changesets by author, revision, files, or words in the commit message
Bugfix regarding "issue_privilege"."voting_right" in "delegation_chain" function
author jbe
date Sat May 16 17:59:54 2020 +0200 (2020-05-16)
parents 4346d48b1780
children
rev   line source
jbe@236 1 BEGIN;
jbe@236 2
jbe@236 3 -- NOTE: "lf_update" needs to be recompiled to complete this update!
jbe@236 4
jbe@236 5 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
jbe@236 6 SELECT * FROM (VALUES ('2.0.7', 2, 0, 7))
jbe@236 7 AS "subquery"("string", "major", "minor", "revision");
jbe@236 8
jbe@236 9 CREATE VIEW "expired_session" AS
jbe@236 10 SELECT * FROM "session" WHERE now() > "expiry";
jbe@236 11
jbe@236 12 CREATE RULE "delete" AS ON DELETE TO "expired_session" DO INSTEAD
jbe@236 13 DELETE FROM "session" WHERE "ident" = OLD."ident";
jbe@236 14
jbe@236 15 COMMENT ON VIEW "expired_session" IS 'View containing all expired sessions where DELETE is possible';
jbe@236 16 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 17
jbe@236 18 CREATE OR REPLACE FUNCTION "check_everything"()
jbe@236 19 RETURNS VOID
jbe@236 20 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@236 21 DECLARE
jbe@236 22 "issue_id_v" "issue"."id"%TYPE;
jbe@236 23 BEGIN
jbe@236 24 DELETE FROM "expired_session";
jbe@236 25 PERFORM "check_activity"();
jbe@236 26 PERFORM "calculate_member_counts"();
jbe@236 27 FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP
jbe@236 28 PERFORM "check_issue"("issue_id_v");
jbe@236 29 END LOOP;
jbe@236 30 FOR "issue_id_v" IN SELECT "id" FROM "issue_with_ranks_missing" LOOP
jbe@236 31 PERFORM "calculate_ranks"("issue_id_v");
jbe@236 32 END LOOP;
jbe@236 33 RETURN;
jbe@236 34 END;
jbe@236 35 $$;
jbe@236 36
jbe@236 37 CREATE OR REPLACE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)
jbe@236 38 RETURNS VOID
jbe@236 39 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@236 40 BEGIN
jbe@236 41 UPDATE "member" SET
jbe@236 42 "last_login" = NULL,
jbe@236 43 "login" = NULL,
jbe@236 44 "password" = NULL,
jbe@236 45 "locked" = TRUE,
jbe@236 46 "active" = FALSE,
jbe@236 47 "notify_email" = NULL,
jbe@236 48 "notify_email_unconfirmed" = NULL,
jbe@236 49 "notify_email_secret" = NULL,
jbe@236 50 "notify_email_secret_expiry" = NULL,
jbe@236 51 "notify_email_lock_expiry" = NULL,
jbe@236 52 "password_reset_secret" = NULL,
jbe@236 53 "password_reset_secret_expiry" = NULL,
jbe@236 54 "organizational_unit" = NULL,
jbe@236 55 "internal_posts" = NULL,
jbe@236 56 "realname" = NULL,
jbe@236 57 "birthday" = NULL,
jbe@236 58 "address" = NULL,
jbe@236 59 "email" = NULL,
jbe@236 60 "xmpp_address" = NULL,
jbe@236 61 "website" = NULL,
jbe@236 62 "phone" = NULL,
jbe@236 63 "mobile_phone" = NULL,
jbe@236 64 "profession" = NULL,
jbe@236 65 "external_memberships" = NULL,
jbe@236 66 "external_posts" = NULL,
jbe@236 67 "statement" = NULL
jbe@236 68 WHERE "id" = "member_id_p";
jbe@236 69 -- "text_search_data" is updated by triggers
jbe@236 70 DELETE FROM "setting" WHERE "member_id" = "member_id_p";
jbe@236 71 DELETE FROM "setting_map" WHERE "member_id" = "member_id_p";
jbe@236 72 DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p";
jbe@236 73 DELETE FROM "member_image" WHERE "member_id" = "member_id_p";
jbe@236 74 DELETE FROM "contact" WHERE "member_id" = "member_id_p";
jbe@236 75 DELETE FROM "ignored_member" WHERE "member_id" = "member_id_p";
jbe@236 76 DELETE FROM "session" WHERE "member_id" = "member_id_p";
jbe@236 77 DELETE FROM "area_setting" WHERE "member_id" = "member_id_p";
jbe@236 78 DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p";
jbe@236 79 DELETE FROM "ignored_initiative" WHERE "member_id" = "member_id_p";
jbe@236 80 DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p";
jbe@236 81 DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p";
jbe@236 82 DELETE FROM "membership" WHERE "member_id" = "member_id_p";
jbe@236 83 DELETE FROM "delegation" WHERE "truster_id" = "member_id_p";
jbe@236 84 DELETE FROM "non_voter" WHERE "member_id" = "member_id_p";
jbe@236 85 DELETE FROM "direct_voter" USING "issue"
jbe@236 86 WHERE "direct_voter"."issue_id" = "issue"."id"
jbe@236 87 AND "issue"."closed" ISNULL
jbe@236 88 AND "member_id" = "member_id_p";
jbe@236 89 RETURN;
jbe@236 90 END;
jbe@236 91 $$;
jbe@236 92
jbe@236 93 CREATE OR REPLACE FUNCTION "delete_private_data"()
jbe@236 94 RETURNS VOID
jbe@236 95 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@236 96 BEGIN
jbe@236 97 DELETE FROM "member" WHERE "activated" ISNULL;
jbe@236 98 UPDATE "member" SET
jbe@236 99 "invite_code" = NULL,
jbe@236 100 "invite_code_expiry" = NULL,
jbe@236 101 "admin_comment" = NULL,
jbe@236 102 "last_login" = NULL,
jbe@236 103 "login" = NULL,
jbe@236 104 "password" = NULL,
jbe@236 105 "notify_email" = NULL,
jbe@236 106 "notify_email_unconfirmed" = NULL,
jbe@236 107 "notify_email_secret" = NULL,
jbe@236 108 "notify_email_secret_expiry" = NULL,
jbe@236 109 "notify_email_lock_expiry" = NULL,
jbe@236 110 "password_reset_secret" = NULL,
jbe@236 111 "password_reset_secret_expiry" = NULL,
jbe@236 112 "organizational_unit" = NULL,
jbe@236 113 "internal_posts" = NULL,
jbe@236 114 "realname" = NULL,
jbe@236 115 "birthday" = NULL,
jbe@236 116 "address" = NULL,
jbe@236 117 "email" = NULL,
jbe@236 118 "xmpp_address" = NULL,
jbe@236 119 "website" = NULL,
jbe@236 120 "phone" = NULL,
jbe@236 121 "mobile_phone" = NULL,
jbe@236 122 "profession" = NULL,
jbe@236 123 "external_memberships" = NULL,
jbe@236 124 "external_posts" = NULL,
jbe@236 125 "statement" = NULL;
jbe@236 126 -- "text_search_data" is updated by triggers
jbe@236 127 DELETE FROM "setting";
jbe@236 128 DELETE FROM "setting_map";
jbe@236 129 DELETE FROM "member_relation_setting";
jbe@236 130 DELETE FROM "member_image";
jbe@236 131 DELETE FROM "contact";
jbe@236 132 DELETE FROM "ignored_member";
jbe@236 133 DELETE FROM "session";
jbe@236 134 DELETE FROM "area_setting";
jbe@236 135 DELETE FROM "issue_setting";
jbe@236 136 DELETE FROM "ignored_initiative";
jbe@236 137 DELETE FROM "initiative_setting";
jbe@236 138 DELETE FROM "suggestion_setting";
jbe@236 139 DELETE FROM "non_voter";
jbe@236 140 DELETE FROM "direct_voter" USING "issue"
jbe@236 141 WHERE "direct_voter"."issue_id" = "issue"."id"
jbe@236 142 AND "issue"."closed" ISNULL;
jbe@236 143 RETURN;
jbe@236 144 END;
jbe@236 145 $$;
jbe@236 146
jbe@236 147 COMMIT;

Impressum / About Us