# HG changeset patch # User jbe # Date 1295723358 -3600 # Node ID bc8aa59b0945de8b487da20c00fd369507ec173a # Parent 2daa8ce3d743f11534802580e9df115f07e1c635 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) diff -r 2daa8ce3d743 -r bc8aa59b0945 core.sql --- a/core.sql Tue Jan 04 02:55:01 2011 +0100 +++ b/core.sql Sat Jan 22 20:09:18 2011 +0100 @@ -58,6 +58,7 @@ "id" SERIAL4 PRIMARY KEY, "created" TIMESTAMPTZ NOT NULL DEFAULT now(), "last_login" TIMESTAMPTZ, + "last_login_public" DATE, "login" TEXT UNIQUE, "password" TEXT, "locked" BOOLEAN NOT NULL DEFAULT FALSE, @@ -98,6 +99,8 @@ COMMENT ON TABLE "member" IS 'Users of the system, e.g. members of an organization'; +COMMENT ON COLUMN "member"."last_login" IS 'Timestamp of last login'; +COMMENT ON COLUMN "member"."last_login_public" IS 'Date of last login (time stripped for privacy reasons, updated only after day change)'; COMMENT ON COLUMN "member"."login" IS 'Login name'; COMMENT ON COLUMN "member"."password" IS 'Password (preferably as crypto-hash, depending on the frontend or access layer)'; COMMENT ON COLUMN "member"."locked" IS 'Locked members can not log in.'; @@ -2038,9 +2041,23 @@ -------------------------------- --- Materialize member counts -- -------------------------------- +------------------------------------------------------------------------ +-- Regular tasks, except calculcation of snapshots and voting results -- +------------------------------------------------------------------------ + +CREATE FUNCTION "publish_last_login"() + RETURNS VOID + LANGUAGE 'plpgsql' VOLATILE AS $$ + BEGIN + LOCK TABLE "member" IN SHARE ROW EXCLUSIVE MODE; + UPDATE "member" SET "last_login_public" = "last_login"::date + WHERE "last_login"::date < 'today'; + RETURN; + END; + $$; + +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.'; + CREATE FUNCTION "calculate_member_counts"() RETURNS VOID @@ -3280,6 +3297,7 @@ "issue_id_v" "issue"."id"%TYPE; BEGIN DELETE FROM "expired_session"; + PERFORM "publish_last_login"(); PERFORM "calculate_member_counts"(); FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP PERFORM "check_issue"("issue_id_v"); @@ -3291,7 +3309,7 @@ END; $$; -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.'; +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.'; @@ -3351,6 +3369,7 @@ BEGIN UPDATE "member" SET "last_login" = NULL, + "last_login_public" = NULL, "login" = NULL, "password" = NULL, "locked" = TRUE, @@ -3450,7 +3469,7 @@ END; $$; -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.'; +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.'; diff -r 2daa8ce3d743 -r bc8aa59b0945 lf_update.c --- a/lf_update.c Tue Jan 04 02:55:01 2011 +0100 +++ b/lf_update.c Sat Jan 22 20:09:18 2011 +0100 @@ -70,6 +70,22 @@ PQclear(status); } + // publish last login: + status = PQexec(db, "SELECT \"publish_last_login\"()"); + if (!status) { + fprintf(stderr, "Error in pqlib while sending SQL command publishing last logins\n"); + err = 1; + } else if ( + PQresultStatus(status) != PGRES_COMMAND_OK && + PQresultStatus(status) != PGRES_TUPLES_OK + ) { + fprintf(stderr, "Error while executing SQL command publishing last logins:\n%s", PQresultErrorMessage(status)); + err = 1; + PQclear(status); + } else { + PQclear(status); + } + // calculate member counts: status = PQexec(db, "SELECT \"calculate_member_counts\"()"); if (!status) { diff -r 2daa8ce3d743 -r bc8aa59b0945 update/core-update.v1.3.0-v1.3.1.sql --- a/update/core-update.v1.3.0-v1.3.1.sql Tue Jan 04 02:55:01 2011 +0100 +++ b/update/core-update.v1.3.0-v1.3.1.sql Sat Jan 22 20:09:18 2011 +0100 @@ -4,10 +4,26 @@ SELECT * FROM (VALUES ('1.3.1', 1, 3, 1)) AS "subquery"("string", "major", "minor", "revision"); +ALTER TABLE "member" ADD COLUMN "last_login_public" DATE; ALTER TABLE "member" ADD COLUMN "locked" BOOLEAN NOT NULL DEFAULT FALSE; -COMMENT ON COLUMN "member"."locked" IS 'Locked members can not log in.'; -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.'; +COMMENT ON COLUMN "member"."last_login" IS 'Timestamp of last login'; +COMMENT ON COLUMN "member"."last_login_public" IS 'Date of last login (time stripped for privacy reasons, updated only after day change)'; +COMMENT ON COLUMN "member"."locked" IS 'Locked members can not log in.'; +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.'; + +CREATE FUNCTION "publish_last_login"() + RETURNS VOID + LANGUAGE 'plpgsql' VOLATILE AS $$ + BEGIN + LOCK TABLE "member" IN SHARE ROW EXCLUSIVE MODE; + UPDATE "member" SET "last_login_public" = "last_login"::date + WHERE "last_login"::date < 'today'; + RETURN; + END; + $$; + +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.'; CREATE OR REPLACE FUNCTION "create_interest_snapshot" ( "issue_id_p" "issue"."id"%TYPE ) @@ -80,4 +96,79 @@ END; $$; +CREATE OR REPLACE FUNCTION "check_everything"() + RETURNS VOID + LANGUAGE 'plpgsql' VOLATILE AS $$ + DECLARE + "issue_id_v" "issue"."id"%TYPE; + BEGIN + DELETE FROM "expired_session"; + PERFORM "publish_last_login"(); + PERFORM "calculate_member_counts"(); + FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP + PERFORM "check_issue"("issue_id_v"); + END LOOP; + FOR "issue_id_v" IN SELECT "id" FROM "issue_with_ranks_missing" LOOP + PERFORM "calculate_ranks"("issue_id_v"); + END LOOP; + RETURN; + END; + $$; + +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.'; + +CREATE OR REPLACE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE) + RETURNS VOID + LANGUAGE 'plpgsql' VOLATILE AS $$ + BEGIN + UPDATE "member" SET + "last_login" = NULL, + "last_login_public" = NULL, + "login" = NULL, + "password" = NULL, + "locked" = TRUE, + "active" = FALSE, + "notify_email" = NULL, + "notify_email_unconfirmed" = NULL, + "notify_email_secret" = NULL, + "notify_email_secret_expiry" = NULL, + "notify_email_lock_expiry" = NULL, + "password_reset_secret" = NULL, + "password_reset_secret_expiry" = NULL, + "organizational_unit" = NULL, + "internal_posts" = NULL, + "realname" = NULL, + "birthday" = NULL, + "address" = NULL, + "email" = NULL, + "xmpp_address" = NULL, + "website" = NULL, + "phone" = NULL, + "mobile_phone" = NULL, + "profession" = NULL, + "external_memberships" = NULL, + "external_posts" = NULL, + "statement" = NULL + WHERE "id" = "member_id_p"; + -- "text_search_data" is updated by triggers + DELETE FROM "setting" WHERE "member_id" = "member_id_p"; + DELETE FROM "setting_map" WHERE "member_id" = "member_id_p"; + DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p"; + DELETE FROM "member_image" WHERE "member_id" = "member_id_p"; + DELETE FROM "contact" WHERE "member_id" = "member_id_p"; + DELETE FROM "area_setting" WHERE "member_id" = "member_id_p"; + DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p"; + DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p"; + DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p"; + DELETE FROM "membership" WHERE "member_id" = "member_id_p"; + DELETE FROM "ignored_issue" WHERE "member_id" = "member_id_p"; + DELETE FROM "delegation" WHERE "truster_id" = "member_id_p"; + DELETE FROM "direct_voter" USING "issue" + WHERE "direct_voter"."issue_id" = "issue"."id" + AND "issue"."closed" ISNULL + AND "member_id" = "member_id_p"; + RETURN; + END; + $$; + COMMIT;