liquid_feedback_core

annotate update/core-update.v1.3.0-v1.3.1.sql @ 103:bc8aa59b0945

find changesets by author, revision, files, or words in the commit message
Introduced "last_login_public" field, which is only updated after each day and does only contain date but not time for privacy reasons
(required changes in lf_update)
author jbe
date Sat Jan 22 20:09:18 2011 +0100 (2011-01-22)
parents 2daa8ce3d743
children 0d03c57ebae5
rev   line source
jbe@102 1 BEGIN;
jbe@102 2
jbe@102 3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
jbe@102 4 SELECT * FROM (VALUES ('1.3.1', 1, 3, 1))
jbe@102 5 AS "subquery"("string", "major", "minor", "revision");
jbe@102 6
jbe@103 7 ALTER TABLE "member" ADD COLUMN "last_login_public" DATE;
jbe@102 8 ALTER TABLE "member" ADD COLUMN "locked" BOOLEAN NOT NULL DEFAULT FALSE;
jbe@102 9
jbe@103 10 COMMENT ON COLUMN "member"."last_login" IS 'Timestamp of last login';
jbe@103 11 COMMENT ON COLUMN "member"."last_login_public" IS 'Date of last login (time stripped for privacy reasons, updated only after day change)';
jbe@103 12 COMMENT ON COLUMN "member"."locked" IS 'Locked members can not log in.';
jbe@103 13 COMMENT ON COLUMN "member"."active" IS 'Memberships, support and votes are taken into account when corresponding members are marked as active. When the user does not log in for an extended period of time, this flag may be set to FALSE. If the user is not locked, he/she may reset the active flag by logging in.';
jbe@103 14
jbe@103 15 CREATE FUNCTION "publish_last_login"()
jbe@103 16 RETURNS VOID
jbe@103 17 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@103 18 BEGIN
jbe@103 19 LOCK TABLE "member" IN SHARE ROW EXCLUSIVE MODE;
jbe@103 20 UPDATE "member" SET "last_login_public" = "last_login"::date
jbe@103 21 WHERE "last_login"::date < 'today';
jbe@103 22 RETURN;
jbe@103 23 END;
jbe@103 24 $$;
jbe@103 25
jbe@103 26 COMMENT ON FUNCTION "publish_last_login"() IS 'Updates "last_login_public" field, which contains the date but not the time of the last login. For privacy reasons this function does not update "last_login_public", if the last login of a member has been today.';
jbe@102 27
jbe@102 28 CREATE OR REPLACE FUNCTION "create_interest_snapshot"
jbe@102 29 ( "issue_id_p" "issue"."id"%TYPE )
jbe@102 30 RETURNS VOID
jbe@102 31 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@102 32 DECLARE
jbe@102 33 "member_id_v" "member"."id"%TYPE;
jbe@102 34 BEGIN
jbe@102 35 DELETE FROM "direct_interest_snapshot"
jbe@102 36 WHERE "issue_id" = "issue_id_p"
jbe@102 37 AND "event" = 'periodic';
jbe@102 38 DELETE FROM "delegating_interest_snapshot"
jbe@102 39 WHERE "issue_id" = "issue_id_p"
jbe@102 40 AND "event" = 'periodic';
jbe@102 41 DELETE FROM "direct_supporter_snapshot"
jbe@102 42 WHERE "issue_id" = "issue_id_p"
jbe@102 43 AND "event" = 'periodic';
jbe@102 44 INSERT INTO "direct_interest_snapshot"
jbe@102 45 ("issue_id", "event", "member_id", "voting_requested")
jbe@102 46 SELECT
jbe@102 47 "issue_id_p" AS "issue_id",
jbe@102 48 'periodic' AS "event",
jbe@102 49 "member"."id" AS "member_id",
jbe@102 50 "interest"."voting_requested"
jbe@102 51 FROM "interest" JOIN "member"
jbe@102 52 ON "interest"."member_id" = "member"."id"
jbe@102 53 WHERE "interest"."issue_id" = "issue_id_p"
jbe@102 54 AND "member"."active";
jbe@102 55 FOR "member_id_v" IN
jbe@102 56 SELECT "member_id" FROM "direct_interest_snapshot"
jbe@102 57 WHERE "issue_id" = "issue_id_p"
jbe@102 58 AND "event" = 'periodic'
jbe@102 59 LOOP
jbe@102 60 UPDATE "direct_interest_snapshot" SET
jbe@102 61 "weight" = 1 +
jbe@102 62 "weight_of_added_delegations_for_interest_snapshot"(
jbe@102 63 "issue_id_p",
jbe@102 64 "member_id_v",
jbe@102 65 '{}'
jbe@102 66 )
jbe@102 67 WHERE "issue_id" = "issue_id_p"
jbe@102 68 AND "event" = 'periodic'
jbe@102 69 AND "member_id" = "member_id_v";
jbe@102 70 END LOOP;
jbe@102 71 INSERT INTO "direct_supporter_snapshot"
jbe@102 72 ( "issue_id", "initiative_id", "event", "member_id",
jbe@102 73 "informed", "satisfied" )
jbe@102 74 SELECT
jbe@102 75 "issue_id_p" AS "issue_id",
jbe@102 76 "initiative"."id" AS "initiative_id",
jbe@102 77 'periodic' AS "event",
jbe@102 78 "supporter"."member_id" AS "member_id",
jbe@102 79 "supporter"."draft_id" = "current_draft"."id" AS "informed",
jbe@102 80 NOT EXISTS (
jbe@102 81 SELECT NULL FROM "critical_opinion"
jbe@102 82 WHERE "initiative_id" = "initiative"."id"
jbe@102 83 AND "member_id" = "supporter"."member_id"
jbe@102 84 ) AS "satisfied"
jbe@102 85 FROM "initiative"
jbe@102 86 JOIN "supporter"
jbe@102 87 ON "supporter"."initiative_id" = "initiative"."id"
jbe@102 88 JOIN "current_draft"
jbe@102 89 ON "initiative"."id" = "current_draft"."initiative_id"
jbe@102 90 JOIN "direct_interest_snapshot"
jbe@102 91 ON "supporter"."member_id" = "direct_interest_snapshot"."member_id"
jbe@102 92 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
jbe@102 93 AND "event" = 'periodic'
jbe@102 94 WHERE "initiative"."issue_id" = "issue_id_p";
jbe@102 95 RETURN;
jbe@102 96 END;
jbe@102 97 $$;
jbe@102 98
jbe@103 99 CREATE OR REPLACE FUNCTION "check_everything"()
jbe@103 100 RETURNS VOID
jbe@103 101 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@103 102 DECLARE
jbe@103 103 "issue_id_v" "issue"."id"%TYPE;
jbe@103 104 BEGIN
jbe@103 105 DELETE FROM "expired_session";
jbe@103 106 PERFORM "publish_last_login"();
jbe@103 107 PERFORM "calculate_member_counts"();
jbe@103 108 FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP
jbe@103 109 PERFORM "check_issue"("issue_id_v");
jbe@103 110 END LOOP;
jbe@103 111 FOR "issue_id_v" IN SELECT "id" FROM "issue_with_ranks_missing" LOOP
jbe@103 112 PERFORM "calculate_ranks"("issue_id_v");
jbe@103 113 END LOOP;
jbe@103 114 RETURN;
jbe@103 115 END;
jbe@103 116 $$;
jbe@103 117
jbe@103 118 COMMENT ON FUNCTION "check_everything"() IS 'Amongst other regular tasks this function performs "check_issue" for every open issue, and if possible, automatically calculates ranks. Use this function only for development and debugging purposes, as long transactions with exclusive locking may result. In productive environments you should call the lf_update program instead.';
jbe@103 119
jbe@103 120 CREATE OR REPLACE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)
jbe@103 121 RETURNS VOID
jbe@103 122 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@103 123 BEGIN
jbe@103 124 UPDATE "member" SET
jbe@103 125 "last_login" = NULL,
jbe@103 126 "last_login_public" = NULL,
jbe@103 127 "login" = NULL,
jbe@103 128 "password" = NULL,
jbe@103 129 "locked" = TRUE,
jbe@103 130 "active" = FALSE,
jbe@103 131 "notify_email" = NULL,
jbe@103 132 "notify_email_unconfirmed" = NULL,
jbe@103 133 "notify_email_secret" = NULL,
jbe@103 134 "notify_email_secret_expiry" = NULL,
jbe@103 135 "notify_email_lock_expiry" = NULL,
jbe@103 136 "password_reset_secret" = NULL,
jbe@103 137 "password_reset_secret_expiry" = NULL,
jbe@103 138 "organizational_unit" = NULL,
jbe@103 139 "internal_posts" = NULL,
jbe@103 140 "realname" = NULL,
jbe@103 141 "birthday" = NULL,
jbe@103 142 "address" = NULL,
jbe@103 143 "email" = NULL,
jbe@103 144 "xmpp_address" = NULL,
jbe@103 145 "website" = NULL,
jbe@103 146 "phone" = NULL,
jbe@103 147 "mobile_phone" = NULL,
jbe@103 148 "profession" = NULL,
jbe@103 149 "external_memberships" = NULL,
jbe@103 150 "external_posts" = NULL,
jbe@103 151 "statement" = NULL
jbe@103 152 WHERE "id" = "member_id_p";
jbe@103 153 -- "text_search_data" is updated by triggers
jbe@103 154 DELETE FROM "setting" WHERE "member_id" = "member_id_p";
jbe@103 155 DELETE FROM "setting_map" WHERE "member_id" = "member_id_p";
jbe@103 156 DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p";
jbe@103 157 DELETE FROM "member_image" WHERE "member_id" = "member_id_p";
jbe@103 158 DELETE FROM "contact" WHERE "member_id" = "member_id_p";
jbe@103 159 DELETE FROM "area_setting" WHERE "member_id" = "member_id_p";
jbe@103 160 DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p";
jbe@103 161 DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p";
jbe@103 162 DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p";
jbe@103 163 DELETE FROM "membership" WHERE "member_id" = "member_id_p";
jbe@103 164 DELETE FROM "ignored_issue" WHERE "member_id" = "member_id_p";
jbe@103 165 DELETE FROM "delegation" WHERE "truster_id" = "member_id_p";
jbe@103 166 DELETE FROM "direct_voter" USING "issue"
jbe@103 167 WHERE "direct_voter"."issue_id" = "issue"."id"
jbe@103 168 AND "issue"."closed" ISNULL
jbe@103 169 AND "member_id" = "member_id_p";
jbe@103 170 RETURN;
jbe@103 171 END;
jbe@103 172 $$;
jbe@103 173
jbe@102 174 COMMIT;

Impressum / About Us