liquid_feedback_core

annotate update/core-update.v1.3.0-v1.3.1.sql @ 106:4d121276bf04

find changesets by author, revision, files, or words in the commit message
Race condition fixed in "check_last_login"()
author jbe
date Sat Feb 05 16:18:07 2011 +0100 (2011-02-05)
parents 0d03c57ebae5
children 53ae1d37e5de
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@104 7 CREATE TABLE "system_setting" (
jbe@104 8 "member_ttl" INTERVAL );
jbe@104 9 CREATE UNIQUE INDEX "system_setting_singleton_idx" ON "system_setting" ((1));
jbe@104 10
jbe@104 11 COMMENT ON TABLE "system_setting" IS 'This table contains only one row with different settings in each column.';
jbe@104 12 COMMENT ON INDEX "system_setting_singleton_idx" IS 'This index ensures that "system_setting" only contains one row maximum.';
jbe@104 13 COMMENT ON COLUMN "system_setting"."member_ttl" IS 'Time after members get their "active" flag set to FALSE, if they do not login anymore.';
jbe@104 14
jbe@103 15 ALTER TABLE "member" ADD COLUMN "last_login_public" DATE;
jbe@102 16 ALTER TABLE "member" ADD COLUMN "locked" BOOLEAN NOT NULL DEFAULT FALSE;
jbe@102 17
jbe@103 18 COMMENT ON COLUMN "member"."last_login" IS 'Timestamp of last login';
jbe@103 19 COMMENT ON COLUMN "member"."last_login_public" IS 'Date of last login (time stripped for privacy reasons, updated only after day change)';
jbe@103 20 COMMENT ON COLUMN "member"."locked" IS 'Locked members can not log in.';
jbe@103 21 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 22
jbe@104 23 CREATE FUNCTION "check_last_login"()
jbe@103 24 RETURNS VOID
jbe@103 25 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@104 26 DECLARE
jbe@104 27 "system_setting_row" "system_setting"%ROWTYPE;
jbe@103 28 BEGIN
jbe@104 29 SELECT * INTO "system_setting_row" FROM "system_setting";
jbe@103 30 LOCK TABLE "member" IN SHARE ROW EXCLUSIVE MODE;
jbe@103 31 UPDATE "member" SET "last_login_public" = "last_login"::date
jbe@103 32 WHERE "last_login"::date < 'today';
jbe@104 33 IF "system_setting_row"."member_ttl" NOTNULL THEN
jbe@104 34 UPDATE "member" SET "active" = FALSE
jbe@104 35 WHERE "active" = TRUE
jbe@104 36 AND "last_login_public" <
jbe@104 37 (now() - "system_setting_row"."member_ttl")::date;
jbe@104 38 END IF;
jbe@103 39 RETURN;
jbe@103 40 END;
jbe@103 41 $$;
jbe@103 42
jbe@104 43 COMMENT ON FUNCTION "check_last_login"() IS 'Updates "last_login_public" field, which contains the date but not the time of the last login, and deactivates members who do not login for the time specified in "system_setting"."member_ttl". For privacy reasons this function does not update "last_login_public", if the last login of a member has been today.';
jbe@102 44
jbe@102 45 CREATE OR REPLACE FUNCTION "create_interest_snapshot"
jbe@102 46 ( "issue_id_p" "issue"."id"%TYPE )
jbe@102 47 RETURNS VOID
jbe@102 48 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@102 49 DECLARE
jbe@102 50 "member_id_v" "member"."id"%TYPE;
jbe@102 51 BEGIN
jbe@102 52 DELETE FROM "direct_interest_snapshot"
jbe@102 53 WHERE "issue_id" = "issue_id_p"
jbe@102 54 AND "event" = 'periodic';
jbe@102 55 DELETE FROM "delegating_interest_snapshot"
jbe@102 56 WHERE "issue_id" = "issue_id_p"
jbe@102 57 AND "event" = 'periodic';
jbe@102 58 DELETE FROM "direct_supporter_snapshot"
jbe@102 59 WHERE "issue_id" = "issue_id_p"
jbe@102 60 AND "event" = 'periodic';
jbe@102 61 INSERT INTO "direct_interest_snapshot"
jbe@102 62 ("issue_id", "event", "member_id", "voting_requested")
jbe@102 63 SELECT
jbe@102 64 "issue_id_p" AS "issue_id",
jbe@102 65 'periodic' AS "event",
jbe@102 66 "member"."id" AS "member_id",
jbe@102 67 "interest"."voting_requested"
jbe@102 68 FROM "interest" JOIN "member"
jbe@102 69 ON "interest"."member_id" = "member"."id"
jbe@102 70 WHERE "interest"."issue_id" = "issue_id_p"
jbe@102 71 AND "member"."active";
jbe@102 72 FOR "member_id_v" IN
jbe@102 73 SELECT "member_id" FROM "direct_interest_snapshot"
jbe@102 74 WHERE "issue_id" = "issue_id_p"
jbe@102 75 AND "event" = 'periodic'
jbe@102 76 LOOP
jbe@102 77 UPDATE "direct_interest_snapshot" SET
jbe@102 78 "weight" = 1 +
jbe@102 79 "weight_of_added_delegations_for_interest_snapshot"(
jbe@102 80 "issue_id_p",
jbe@102 81 "member_id_v",
jbe@102 82 '{}'
jbe@102 83 )
jbe@102 84 WHERE "issue_id" = "issue_id_p"
jbe@102 85 AND "event" = 'periodic'
jbe@102 86 AND "member_id" = "member_id_v";
jbe@102 87 END LOOP;
jbe@102 88 INSERT INTO "direct_supporter_snapshot"
jbe@102 89 ( "issue_id", "initiative_id", "event", "member_id",
jbe@102 90 "informed", "satisfied" )
jbe@102 91 SELECT
jbe@102 92 "issue_id_p" AS "issue_id",
jbe@102 93 "initiative"."id" AS "initiative_id",
jbe@102 94 'periodic' AS "event",
jbe@102 95 "supporter"."member_id" AS "member_id",
jbe@102 96 "supporter"."draft_id" = "current_draft"."id" AS "informed",
jbe@102 97 NOT EXISTS (
jbe@102 98 SELECT NULL FROM "critical_opinion"
jbe@102 99 WHERE "initiative_id" = "initiative"."id"
jbe@102 100 AND "member_id" = "supporter"."member_id"
jbe@102 101 ) AS "satisfied"
jbe@102 102 FROM "initiative"
jbe@102 103 JOIN "supporter"
jbe@102 104 ON "supporter"."initiative_id" = "initiative"."id"
jbe@102 105 JOIN "current_draft"
jbe@102 106 ON "initiative"."id" = "current_draft"."initiative_id"
jbe@102 107 JOIN "direct_interest_snapshot"
jbe@102 108 ON "supporter"."member_id" = "direct_interest_snapshot"."member_id"
jbe@102 109 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
jbe@102 110 AND "event" = 'periodic'
jbe@102 111 WHERE "initiative"."issue_id" = "issue_id_p";
jbe@102 112 RETURN;
jbe@102 113 END;
jbe@102 114 $$;
jbe@102 115
jbe@103 116 CREATE OR REPLACE FUNCTION "check_everything"()
jbe@103 117 RETURNS VOID
jbe@103 118 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@103 119 DECLARE
jbe@103 120 "issue_id_v" "issue"."id"%TYPE;
jbe@103 121 BEGIN
jbe@103 122 DELETE FROM "expired_session";
jbe@104 123 PERFORM "check_last_login"();
jbe@103 124 PERFORM "calculate_member_counts"();
jbe@103 125 FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP
jbe@103 126 PERFORM "check_issue"("issue_id_v");
jbe@103 127 END LOOP;
jbe@103 128 FOR "issue_id_v" IN SELECT "id" FROM "issue_with_ranks_missing" LOOP
jbe@103 129 PERFORM "calculate_ranks"("issue_id_v");
jbe@103 130 END LOOP;
jbe@103 131 RETURN;
jbe@103 132 END;
jbe@103 133 $$;
jbe@103 134
jbe@103 135 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 136
jbe@103 137 CREATE OR REPLACE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)
jbe@103 138 RETURNS VOID
jbe@103 139 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@103 140 BEGIN
jbe@103 141 UPDATE "member" SET
jbe@103 142 "last_login" = NULL,
jbe@103 143 "last_login_public" = NULL,
jbe@103 144 "login" = NULL,
jbe@103 145 "password" = NULL,
jbe@103 146 "locked" = TRUE,
jbe@103 147 "active" = FALSE,
jbe@103 148 "notify_email" = NULL,
jbe@103 149 "notify_email_unconfirmed" = NULL,
jbe@103 150 "notify_email_secret" = NULL,
jbe@103 151 "notify_email_secret_expiry" = NULL,
jbe@103 152 "notify_email_lock_expiry" = NULL,
jbe@103 153 "password_reset_secret" = NULL,
jbe@103 154 "password_reset_secret_expiry" = NULL,
jbe@103 155 "organizational_unit" = NULL,
jbe@103 156 "internal_posts" = NULL,
jbe@103 157 "realname" = NULL,
jbe@103 158 "birthday" = NULL,
jbe@103 159 "address" = NULL,
jbe@103 160 "email" = NULL,
jbe@103 161 "xmpp_address" = NULL,
jbe@103 162 "website" = NULL,
jbe@103 163 "phone" = NULL,
jbe@103 164 "mobile_phone" = NULL,
jbe@103 165 "profession" = NULL,
jbe@103 166 "external_memberships" = NULL,
jbe@103 167 "external_posts" = NULL,
jbe@103 168 "statement" = NULL
jbe@103 169 WHERE "id" = "member_id_p";
jbe@103 170 -- "text_search_data" is updated by triggers
jbe@103 171 DELETE FROM "setting" WHERE "member_id" = "member_id_p";
jbe@103 172 DELETE FROM "setting_map" WHERE "member_id" = "member_id_p";
jbe@103 173 DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p";
jbe@103 174 DELETE FROM "member_image" WHERE "member_id" = "member_id_p";
jbe@103 175 DELETE FROM "contact" WHERE "member_id" = "member_id_p";
jbe@103 176 DELETE FROM "area_setting" WHERE "member_id" = "member_id_p";
jbe@103 177 DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p";
jbe@103 178 DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p";
jbe@103 179 DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p";
jbe@103 180 DELETE FROM "membership" WHERE "member_id" = "member_id_p";
jbe@103 181 DELETE FROM "ignored_issue" WHERE "member_id" = "member_id_p";
jbe@103 182 DELETE FROM "delegation" WHERE "truster_id" = "member_id_p";
jbe@103 183 DELETE FROM "direct_voter" USING "issue"
jbe@103 184 WHERE "direct_voter"."issue_id" = "issue"."id"
jbe@103 185 AND "issue"."closed" ISNULL
jbe@103 186 AND "member_id" = "member_id_p";
jbe@103 187 RETURN;
jbe@103 188 END;
jbe@103 189 $$;
jbe@103 190
jbe@102 191 COMMIT;

Impressum / About Us