liquid_feedback_core
diff core.sql @ 103:bc8aa59b0945
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)
(required changes in lf_update)
author | jbe |
---|---|
date | Sat Jan 22 20:09:18 2011 +0100 (2011-01-22) |
parents | 9b18ee4682c7 |
children | 0d03c57ebae5 |
line diff
1.1 --- a/core.sql Tue Jan 04 02:55:01 2011 +0100 1.2 +++ b/core.sql Sat Jan 22 20:09:18 2011 +0100 1.3 @@ -58,6 +58,7 @@ 1.4 "id" SERIAL4 PRIMARY KEY, 1.5 "created" TIMESTAMPTZ NOT NULL DEFAULT now(), 1.6 "last_login" TIMESTAMPTZ, 1.7 + "last_login_public" DATE, 1.8 "login" TEXT UNIQUE, 1.9 "password" TEXT, 1.10 "locked" BOOLEAN NOT NULL DEFAULT FALSE, 1.11 @@ -98,6 +99,8 @@ 1.12 1.13 COMMENT ON TABLE "member" IS 'Users of the system, e.g. members of an organization'; 1.14 1.15 +COMMENT ON COLUMN "member"."last_login" IS 'Timestamp of last login'; 1.16 +COMMENT ON COLUMN "member"."last_login_public" IS 'Date of last login (time stripped for privacy reasons, updated only after day change)'; 1.17 COMMENT ON COLUMN "member"."login" IS 'Login name'; 1.18 COMMENT ON COLUMN "member"."password" IS 'Password (preferably as crypto-hash, depending on the frontend or access layer)'; 1.19 COMMENT ON COLUMN "member"."locked" IS 'Locked members can not log in.'; 1.20 @@ -2038,9 +2041,23 @@ 1.21 1.22 1.23 1.24 -------------------------------- 1.25 --- Materialize member counts -- 1.26 -------------------------------- 1.27 +------------------------------------------------------------------------ 1.28 +-- Regular tasks, except calculcation of snapshots and voting results -- 1.29 +------------------------------------------------------------------------ 1.30 + 1.31 +CREATE FUNCTION "publish_last_login"() 1.32 + RETURNS VOID 1.33 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.34 + BEGIN 1.35 + LOCK TABLE "member" IN SHARE ROW EXCLUSIVE MODE; 1.36 + UPDATE "member" SET "last_login_public" = "last_login"::date 1.37 + WHERE "last_login"::date < 'today'; 1.38 + RETURN; 1.39 + END; 1.40 + $$; 1.41 + 1.42 +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.'; 1.43 + 1.44 1.45 CREATE FUNCTION "calculate_member_counts"() 1.46 RETURNS VOID 1.47 @@ -3280,6 +3297,7 @@ 1.48 "issue_id_v" "issue"."id"%TYPE; 1.49 BEGIN 1.50 DELETE FROM "expired_session"; 1.51 + PERFORM "publish_last_login"(); 1.52 PERFORM "calculate_member_counts"(); 1.53 FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP 1.54 PERFORM "check_issue"("issue_id_v"); 1.55 @@ -3291,7 +3309,7 @@ 1.56 END; 1.57 $$; 1.58 1.59 -COMMENT ON FUNCTION "check_everything"() IS 'Perform "check_issue" for every open issue, and if possible, automatically calculate ranks. Use this function only for development and debugging purposes, as long transactions with exclusive locking may result.'; 1.60 +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.'; 1.61 1.62 1.63 1.64 @@ -3351,6 +3369,7 @@ 1.65 BEGIN 1.66 UPDATE "member" SET 1.67 "last_login" = NULL, 1.68 + "last_login_public" = NULL, 1.69 "login" = NULL, 1.70 "password" = NULL, 1.71 "locked" = TRUE, 1.72 @@ -3450,7 +3469,7 @@ 1.73 END; 1.74 $$; 1.75 1.76 -COMMENT ON FUNCTION "delete_private_data"() IS 'DO NOT USE on productive database, but only on a copy! This function deletes all data which should not be publicly available, and can be used to create a database dump for publication.'; 1.77 +COMMENT ON FUNCTION "delete_private_data"() IS 'Used by lf_export script. DO NOT USE on productive database, but only on a copy! This function deletes all data which should not be publicly available, and can be used to create a database dump for publication.'; 1.78 1.79 1.80