1.1 --- a/core.sql	Sat Jan 22 20:09:18 2011 +0100
     1.2 +++ b/core.sql	Mon Jan 24 17:30:18 2011 +0100
     1.3 @@ -54,6 +54,16 @@
     1.4  -------------------------
     1.5  
     1.6  
     1.7 +CREATE TABLE "system_setting" (
     1.8 +        "member_ttl"            INTERVAL );
     1.9 +CREATE UNIQUE INDEX "system_setting_singleton_idx" ON "system_setting" ((1));
    1.10 +
    1.11 +COMMENT ON TABLE "system_setting" IS 'This table contains only one row with different settings in each column.';
    1.12 +COMMENT ON INDEX "system_setting_singleton_idx" IS 'This index ensures that "system_setting" only contains one row maximum.';
    1.13 +
    1.14 +COMMENT ON COLUMN "system_setting"."member_ttl" IS 'Time after members get their "active" flag set to FALSE, if they do not login anymore.';
    1.15 +
    1.16 +
    1.17  CREATE TABLE "member" (
    1.18          "id"                    SERIAL4         PRIMARY KEY,
    1.19          "created"               TIMESTAMPTZ     NOT NULL DEFAULT now(),
    1.20 @@ -2045,18 +2055,27 @@
    1.21  -- Regular tasks, except calculcation of snapshots and voting results --
    1.22  ------------------------------------------------------------------------
    1.23  
    1.24 -CREATE FUNCTION "publish_last_login"()
    1.25 +CREATE FUNCTION "check_last_login"()
    1.26    RETURNS VOID
    1.27    LANGUAGE 'plpgsql' VOLATILE AS $$
    1.28 +    DECLARE
    1.29 +      "system_setting_row" "system_setting"%ROWTYPE;
    1.30      BEGIN
    1.31 +      SELECT * INTO "system_setting_row" FROM "system_setting";
    1.32        LOCK TABLE "member" IN SHARE ROW EXCLUSIVE MODE;
    1.33        UPDATE "member" SET "last_login_public" = "last_login"::date
    1.34          WHERE "last_login"::date < 'today';
    1.35 +      IF "system_setting_row"."member_ttl" NOTNULL THEN
    1.36 +        UPDATE "member" SET "active" = FALSE
    1.37 +          WHERE "active" = TRUE
    1.38 +          AND "last_login_public" <
    1.39 +            (now() - "system_setting_row"."member_ttl")::date;
    1.40 +      END IF;
    1.41        RETURN;
    1.42      END;
    1.43    $$;
    1.44  
    1.45 -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.46 +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.';
    1.47  
    1.48  
    1.49  CREATE FUNCTION "calculate_member_counts"()
    1.50 @@ -3297,7 +3316,7 @@
    1.51        "issue_id_v" "issue"."id"%TYPE;
    1.52      BEGIN
    1.53        DELETE FROM "expired_session";
    1.54 -      PERFORM "publish_last_login"();
    1.55 +      PERFORM "check_last_login"();
    1.56        PERFORM "calculate_member_counts"();
    1.57        FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP
    1.58          PERFORM "check_issue"("issue_id_v");