1.1 --- a/core.sql	Fri Jul 29 16:04:17 2011 +0200
     1.2 +++ b/core.sql	Fri Jul 29 20:26:45 2011 +0200
     1.3 @@ -61,7 +61,7 @@
     1.4  COMMENT ON TABLE "system_setting" IS 'This table contains only one row with different settings in each column.';
     1.5  COMMENT ON INDEX "system_setting_singleton_idx" IS 'This index ensures that "system_setting" only contains one row maximum.';
     1.6  
     1.7 -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.8 +COMMENT ON COLUMN "system_setting"."member_ttl" IS 'Time after members get their "active" flag set to FALSE, if they do not show any activity.';
     1.9  
    1.10  
    1.11  CREATE TABLE "contingent" (
    1.12 @@ -87,8 +87,8 @@
    1.13          "invite_code"           TEXT            UNIQUE,
    1.14          "admin_comment"         TEXT,
    1.15          "activated"             TIMESTAMPTZ,
    1.16 +        "last_activity"         DATE,
    1.17          "last_login"            TIMESTAMPTZ,
    1.18 -        "last_login_public"     DATE,
    1.19          "login"                 TEXT            UNIQUE,
    1.20          "password"              TEXT,
    1.21          "locked"                BOOLEAN         NOT NULL DEFAULT FALSE,
    1.22 @@ -121,8 +121,8 @@
    1.23          "formatting_engine"     TEXT,
    1.24          "statement"             TEXT,
    1.25          "text_search_data"      TSVECTOR,
    1.26 -        CONSTRAINT "not_active_without_activated"
    1.27 -          CHECK ("activated" NOTNULL OR "active" = FALSE) );
    1.28 +        CONSTRAINT "active_requires_activated_and_last_activity"
    1.29 +          CHECK ("active" = FALSE OR ("activated" NOTNULL AND "last_activity" NOTNULL)) );
    1.30  CREATE INDEX "member_active_idx" ON "member" ("active");
    1.31  CREATE INDEX "member_text_search_data_idx" ON "member" USING gin ("text_search_data");
    1.32  CREATE TRIGGER "update_text_search_data"
    1.33 @@ -137,13 +137,13 @@
    1.34  COMMENT ON COLUMN "member"."created"              IS 'Creation of member record and/or invite code';
    1.35  COMMENT ON COLUMN "member"."invite_code"          IS 'Optional invite code, to allow a member to initialize his/her account the first time';
    1.36  COMMENT ON COLUMN "member"."admin_comment"        IS 'Hidden comment for administrative purposes';
    1.37 -COMMENT ON COLUMN "member"."activated"            IS 'Timestamp of activation of account (i.e. usage of "invite_code"); needs to be set for "active" members';
    1.38 +COMMENT ON COLUMN "member"."activated"            IS 'Timestamp of activation of account (i.e. usage of "invite_code"); required to be set for "active" members';
    1.39 +COMMENT ON COLUMN "member"."last_activity"        IS 'Date of last activity of member; required to be set for "active" members';
    1.40  COMMENT ON COLUMN "member"."last_login"           IS 'Timestamp of last login';
    1.41 -COMMENT ON COLUMN "member"."last_login_public"    IS 'Date of last login (time stripped for privacy reasons, updated only after day change)';
    1.42  COMMENT ON COLUMN "member"."login"                IS 'Login name';
    1.43  COMMENT ON COLUMN "member"."password"             IS 'Password (preferably as crypto-hash, depending on the frontend or access layer)';
    1.44  COMMENT ON COLUMN "member"."locked"               IS 'Locked members can not log in.';
    1.45 -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 (has to be set to TRUE by frontend on every login).';
    1.46 +COMMENT ON COLUMN "member"."active"               IS 'Memberships, support and votes are taken into account when corresponding members are marked as active. Automatically set to FALSE, if "last_activity" is older than "system_setting"."member_ttl".';
    1.47  COMMENT ON COLUMN "member"."admin"                IS 'TRUE for admins, which can administrate other users and setup policies and areas';
    1.48  COMMENT ON COLUMN "member"."notify_email"         IS 'Email address where notifications of the system are sent to';
    1.49  COMMENT ON COLUMN "member"."notify_email_unconfirmed"   IS 'Unconfirmed email address provided by the member to be copied into "notify_email" field after verification';
    1.50 @@ -2654,7 +2654,7 @@
    1.51  -- Regular tasks, except calculcation of snapshots and voting results --
    1.52  ------------------------------------------------------------------------
    1.53  
    1.54 -CREATE FUNCTION "check_last_login"()
    1.55 +CREATE FUNCTION "check_activity"()
    1.56    RETURNS VOID
    1.57    LANGUAGE 'plpgsql' VOLATILE AS $$
    1.58      DECLARE
    1.59 @@ -2662,29 +2662,16 @@
    1.60      BEGIN
    1.61        SELECT * INTO "system_setting_row" FROM "system_setting";
    1.62        LOCK TABLE "member" IN SHARE ROW EXCLUSIVE MODE;
    1.63 -      UPDATE "member" SET "last_login_public" = "last_login"::date
    1.64 -        FROM (
    1.65 -          SELECT DISTINCT "member"."id"
    1.66 -          FROM "member" LEFT JOIN "member_history"
    1.67 -          ON "member"."id" = "member_history"."member_id"
    1.68 -          WHERE "member"."last_login"::date < 'today' OR (
    1.69 -            "member_history"."until"::date >= 'today' AND
    1.70 -            "member_history"."active" = FALSE AND "member"."active" = TRUE
    1.71 -          )
    1.72 -        ) AS "subquery"
    1.73 -        WHERE "member"."id" = "subquery"."id";
    1.74        IF "system_setting_row"."member_ttl" NOTNULL THEN
    1.75          UPDATE "member" SET "active" = FALSE
    1.76            WHERE "active" = TRUE
    1.77 -          AND "last_login"::date < 'today'
    1.78 -          AND "last_login_public" <
    1.79 -            (now() - "system_setting_row"."member_ttl")::date;
    1.80 +          AND "last_activity" < (now() - "system_setting_row"."member_ttl")::DATE;
    1.81        END IF;
    1.82        RETURN;
    1.83      END;
    1.84    $$;
    1.85  
    1.86 -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 (except when member was reactivated today).';
    1.87 +COMMENT ON FUNCTION "check_activity"() IS 'Deactivates members when "last_activity" is older than "system_setting"."member_ttl".';
    1.88  
    1.89  
    1.90  CREATE FUNCTION "calculate_member_counts"()
    1.91 @@ -3950,7 +3937,7 @@
    1.92      DECLARE
    1.93        "issue_id_v" "issue"."id"%TYPE;
    1.94      BEGIN
    1.95 -      PERFORM "check_last_login"();
    1.96 +      PERFORM "check_activity"();
    1.97        PERFORM "calculate_member_counts"();
    1.98        FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP
    1.99          PERFORM "check_issue"("issue_id_v");
   1.100 @@ -4028,7 +4015,6 @@
   1.101      BEGIN
   1.102        UPDATE "member" SET
   1.103          "last_login"                   = NULL,
   1.104 -        "last_login_public"            = NULL,
   1.105          "login"                        = NULL,
   1.106          "password"                     = NULL,
   1.107          "locked"                       = TRUE,