liquid_feedback_core
changeset 236:4346d48b1780 v2.0.7
Update script to v2.0.7
author | jbe |
---|---|
date | Tue Mar 13 18:20:17 2012 +0100 (2012-03-13) |
parents | 2a6984869ba3 |
children | ce62f8451659 |
files | update/core-update.v2.0.6-v2.0.7.sql |
line diff
1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000 1.2 +++ b/update/core-update.v2.0.6-v2.0.7.sql Tue Mar 13 18:20:17 2012 +0100 1.3 @@ -0,0 +1,147 @@ 1.4 +BEGIN; 1.5 + 1.6 +-- NOTE: "lf_update" needs to be recompiled to complete this update! 1.7 + 1.8 +CREATE OR REPLACE VIEW "liquid_feedback_version" AS 1.9 + SELECT * FROM (VALUES ('2.0.7', 2, 0, 7)) 1.10 + AS "subquery"("string", "major", "minor", "revision"); 1.11 + 1.12 +CREATE VIEW "expired_session" AS 1.13 + SELECT * FROM "session" WHERE now() > "expiry"; 1.14 + 1.15 +CREATE RULE "delete" AS ON DELETE TO "expired_session" DO INSTEAD 1.16 + DELETE FROM "session" WHERE "ident" = OLD."ident"; 1.17 + 1.18 +COMMENT ON VIEW "expired_session" IS 'View containing all expired sessions where DELETE is possible'; 1.19 +COMMENT ON RULE "delete" ON "expired_session" IS 'Rule allowing DELETE on rows in "expired_session" view, i.e. DELETE FROM "expired_session"'; 1.20 + 1.21 +CREATE OR REPLACE FUNCTION "check_everything"() 1.22 + RETURNS VOID 1.23 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.24 + DECLARE 1.25 + "issue_id_v" "issue"."id"%TYPE; 1.26 + BEGIN 1.27 + DELETE FROM "expired_session"; 1.28 + PERFORM "check_activity"(); 1.29 + PERFORM "calculate_member_counts"(); 1.30 + FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP 1.31 + PERFORM "check_issue"("issue_id_v"); 1.32 + END LOOP; 1.33 + FOR "issue_id_v" IN SELECT "id" FROM "issue_with_ranks_missing" LOOP 1.34 + PERFORM "calculate_ranks"("issue_id_v"); 1.35 + END LOOP; 1.36 + RETURN; 1.37 + END; 1.38 + $$; 1.39 + 1.40 +CREATE OR REPLACE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE) 1.41 + RETURNS VOID 1.42 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.43 + BEGIN 1.44 + UPDATE "member" SET 1.45 + "last_login" = NULL, 1.46 + "login" = NULL, 1.47 + "password" = NULL, 1.48 + "locked" = TRUE, 1.49 + "active" = FALSE, 1.50 + "notify_email" = NULL, 1.51 + "notify_email_unconfirmed" = NULL, 1.52 + "notify_email_secret" = NULL, 1.53 + "notify_email_secret_expiry" = NULL, 1.54 + "notify_email_lock_expiry" = NULL, 1.55 + "password_reset_secret" = NULL, 1.56 + "password_reset_secret_expiry" = NULL, 1.57 + "organizational_unit" = NULL, 1.58 + "internal_posts" = NULL, 1.59 + "realname" = NULL, 1.60 + "birthday" = NULL, 1.61 + "address" = NULL, 1.62 + "email" = NULL, 1.63 + "xmpp_address" = NULL, 1.64 + "website" = NULL, 1.65 + "phone" = NULL, 1.66 + "mobile_phone" = NULL, 1.67 + "profession" = NULL, 1.68 + "external_memberships" = NULL, 1.69 + "external_posts" = NULL, 1.70 + "statement" = NULL 1.71 + WHERE "id" = "member_id_p"; 1.72 + -- "text_search_data" is updated by triggers 1.73 + DELETE FROM "setting" WHERE "member_id" = "member_id_p"; 1.74 + DELETE FROM "setting_map" WHERE "member_id" = "member_id_p"; 1.75 + DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p"; 1.76 + DELETE FROM "member_image" WHERE "member_id" = "member_id_p"; 1.77 + DELETE FROM "contact" WHERE "member_id" = "member_id_p"; 1.78 + DELETE FROM "ignored_member" WHERE "member_id" = "member_id_p"; 1.79 + DELETE FROM "session" WHERE "member_id" = "member_id_p"; 1.80 + DELETE FROM "area_setting" WHERE "member_id" = "member_id_p"; 1.81 + DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p"; 1.82 + DELETE FROM "ignored_initiative" WHERE "member_id" = "member_id_p"; 1.83 + DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p"; 1.84 + DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p"; 1.85 + DELETE FROM "membership" WHERE "member_id" = "member_id_p"; 1.86 + DELETE FROM "delegation" WHERE "truster_id" = "member_id_p"; 1.87 + DELETE FROM "non_voter" WHERE "member_id" = "member_id_p"; 1.88 + DELETE FROM "direct_voter" USING "issue" 1.89 + WHERE "direct_voter"."issue_id" = "issue"."id" 1.90 + AND "issue"."closed" ISNULL 1.91 + AND "member_id" = "member_id_p"; 1.92 + RETURN; 1.93 + END; 1.94 + $$; 1.95 + 1.96 +CREATE OR REPLACE FUNCTION "delete_private_data"() 1.97 + RETURNS VOID 1.98 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.99 + BEGIN 1.100 + DELETE FROM "member" WHERE "activated" ISNULL; 1.101 + UPDATE "member" SET 1.102 + "invite_code" = NULL, 1.103 + "invite_code_expiry" = NULL, 1.104 + "admin_comment" = NULL, 1.105 + "last_login" = NULL, 1.106 + "login" = NULL, 1.107 + "password" = NULL, 1.108 + "notify_email" = NULL, 1.109 + "notify_email_unconfirmed" = NULL, 1.110 + "notify_email_secret" = NULL, 1.111 + "notify_email_secret_expiry" = NULL, 1.112 + "notify_email_lock_expiry" = NULL, 1.113 + "password_reset_secret" = NULL, 1.114 + "password_reset_secret_expiry" = NULL, 1.115 + "organizational_unit" = NULL, 1.116 + "internal_posts" = NULL, 1.117 + "realname" = NULL, 1.118 + "birthday" = NULL, 1.119 + "address" = NULL, 1.120 + "email" = NULL, 1.121 + "xmpp_address" = NULL, 1.122 + "website" = NULL, 1.123 + "phone" = NULL, 1.124 + "mobile_phone" = NULL, 1.125 + "profession" = NULL, 1.126 + "external_memberships" = NULL, 1.127 + "external_posts" = NULL, 1.128 + "statement" = NULL; 1.129 + -- "text_search_data" is updated by triggers 1.130 + DELETE FROM "setting"; 1.131 + DELETE FROM "setting_map"; 1.132 + DELETE FROM "member_relation_setting"; 1.133 + DELETE FROM "member_image"; 1.134 + DELETE FROM "contact"; 1.135 + DELETE FROM "ignored_member"; 1.136 + DELETE FROM "session"; 1.137 + DELETE FROM "area_setting"; 1.138 + DELETE FROM "issue_setting"; 1.139 + DELETE FROM "ignored_initiative"; 1.140 + DELETE FROM "initiative_setting"; 1.141 + DELETE FROM "suggestion_setting"; 1.142 + DELETE FROM "non_voter"; 1.143 + DELETE FROM "direct_voter" USING "issue" 1.144 + WHERE "direct_voter"."issue_id" = "issue"."id" 1.145 + AND "issue"."closed" ISNULL; 1.146 + RETURN; 1.147 + END; 1.148 + $$; 1.149 + 1.150 +COMMIT;