1.1 --- a/update/core-update.v1.4.0-v1.5.0.sql	Fri Jul 29 16:04:17 2011 +0200
     1.2 +++ b/update/core-update.v1.4.0-v1.5.0.sql	Fri Jul 29 20:26:45 2011 +0200
     1.3 @@ -9,13 +9,16 @@
     1.4  ALTER TABLE "member" ADD COLUMN "invite_code" TEXT UNIQUE;
     1.5  ALTER TABLE "member" ADD COLUMN "admin_comment" TEXT;
     1.6  ALTER TABLE "member" ADD COLUMN "activated" TIMESTAMPTZ;
     1.7 +ALTER TABLE "member" ADD COLUMN "last_activity" DATE;
     1.8 +ALTER TABLE "member" DROP COLUMN "last_login_public";
     1.9  ALTER TABLE "member" ALTER COLUMN "active" SET DEFAULT FALSE;
    1.10  ALTER TABLE "member" ADD COLUMN "formatting_engine" TEXT;
    1.11  
    1.12  COMMENT ON COLUMN "member"."created"           IS 'Creation of member record and/or invite code';
    1.13  COMMENT ON COLUMN "member"."invite_code"       IS 'Optional invite code, to allow a member to initialize his/her account the first time';
    1.14 -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.15 -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.16 +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.17 +COMMENT ON COLUMN "member"."last_activity"     IS 'Date of last activity of member; required to be set for "active" members';
    1.18 +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.19  COMMENT ON COLUMN "member"."formatting_engine" IS 'Allows different formatting engines (i.e. wiki formats) to be used for "member"."statement"';
    1.20  
    1.21  CREATE TABLE "rendered_member_statement" (
    1.22 @@ -288,6 +291,27 @@
    1.23  
    1.24  COMMENT ON VIEW "battle_view" IS 'Number of members preferring one initiative (or status-quo) to another initiative (or status-quo); Used to fill "battle" table';
    1.25  
    1.26 +DROP FUNCTION "check_last_login"();
    1.27 +
    1.28 +CREATE FUNCTION "check_activity"()
    1.29 +  RETURNS VOID
    1.30 +  LANGUAGE 'plpgsql' VOLATILE AS $$
    1.31 +    DECLARE
    1.32 +      "system_setting_row" "system_setting"%ROWTYPE;
    1.33 +    BEGIN
    1.34 +      SELECT * INTO "system_setting_row" FROM "system_setting";
    1.35 +      LOCK TABLE "member" IN SHARE ROW EXCLUSIVE MODE;
    1.36 +      IF "system_setting_row"."member_ttl" NOTNULL THEN
    1.37 +        UPDATE "member" SET "active" = FALSE
    1.38 +          WHERE "active" = TRUE
    1.39 +          AND "last_activity" < (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 "check_activity"() IS 'Deactivates members when "last_activity" is older than "system_setting"."member_ttl".';
    1.46 +
    1.47  CREATE OR REPLACE FUNCTION "create_interest_snapshot"
    1.48    ( "issue_id_p" "issue"."id"%TYPE )
    1.49    RETURNS VOID
    1.50 @@ -1050,7 +1074,7 @@
    1.51      DECLARE
    1.52        "issue_id_v" "issue"."id"%TYPE;
    1.53      BEGIN
    1.54 -      PERFORM "check_last_login"();
    1.55 +      PERFORM "check_activity"();
    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");
    1.59 @@ -1068,7 +1092,6 @@
    1.60      BEGIN
    1.61        UPDATE "member" SET
    1.62          "last_login"                   = NULL,
    1.63 -        "last_login_public"            = NULL,
    1.64          "login"                        = NULL,
    1.65          "password"                     = NULL,
    1.66          "locked"                       = TRUE,
    1.67 @@ -1172,7 +1195,13 @@
    1.68  
    1.69  BEGIN;
    1.70  
    1.71 -UPDATE "member" SET "activated" = "created";
    1.72 +UPDATE "member" SET
    1.73 +  "activated" = "created",
    1.74 +  "last_activity" = CASE WHEN "active" THEN
    1.75 +    coalesce("last_login"::DATE, now())
    1.76 +  ELSE
    1.77 +    "last_login"::DATE
    1.78 +  END;
    1.79  
    1.80  UPDATE "member" SET
    1.81    "created" = "invite_code"."created",
    1.82 @@ -1254,6 +1283,6 @@
    1.83  
    1.84  COMMIT;
    1.85  
    1.86 -ALTER TABLE "member" ADD CONSTRAINT "not_active_without_activated"
    1.87 -  CHECK ("activated" NOTNULL OR "active" = FALSE);
    1.88 +ALTER TABLE "member" ADD CONSTRAINT "active_requires_activated_and_last_activity"
    1.89 +  CHECK ("active" = FALSE OR ("activated" NOTNULL AND "last_activity" NOTNULL));
    1.90  ALTER TABLE "suggestion" ALTER COLUMN "draft_id" SET NOT NULL;