liquid_feedback_core

changeset 184:af3d208e81be

Member deactivation based on last_activity instead of last_login
author jbe
date Fri Jul 29 20:26:45 2011 +0200 (2011-07-29)
parents ed2f94a397cd
children b0b7e0b18d78
files core.sql demo.sql lf_update.c update/core-update.v1.4.0-v1.5.0.sql
line diff
     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,
     2.1 --- a/demo.sql	Fri Jul 29 16:04:17 2011 +0200
     2.2 +++ b/demo.sql	Fri Jul 29 20:26:45 2011 +0200
     2.3 @@ -3,30 +3,30 @@
     2.4  
     2.5  BEGIN;
     2.6  
     2.7 -INSERT INTO "member" ("activated", "active", "login", "name") VALUES
     2.8 -  ('now', TRUE, 'user1',  'User #1'),   -- id  1
     2.9 -  ('now', TRUE, 'user2',  'User #2'),   -- id  2
    2.10 -  ('now', TRUE, 'user3',  'User #3'),   -- id  3
    2.11 -  ('now', TRUE, 'user4',  'User #4'),   -- id  4
    2.12 -  ('now', TRUE, 'user5',  'User #5'),   -- id  5
    2.13 -  ('now', TRUE, 'user6',  'User #6'),   -- id  6
    2.14 -  ('now', TRUE, 'user7',  'User #7'),   -- id  7
    2.15 -  ('now', TRUE, 'user8',  'User #8'),   -- id  8
    2.16 -  ('now', TRUE, 'user9',  'User #9'),   -- id  9
    2.17 -  ('now', TRUE, 'user10', 'User #10'),  -- id 10
    2.18 -  ('now', TRUE, 'user11', 'User #11'),  -- id 11
    2.19 -  ('now', TRUE, 'user12', 'User #12'),  -- id 12
    2.20 -  ('now', TRUE, 'user13', 'User #13'),  -- id 13
    2.21 -  ('now', TRUE, 'user14', 'User #14'),  -- id 14
    2.22 -  ('now', TRUE, 'user15', 'User #15'),  -- id 15
    2.23 -  ('now', TRUE, 'user16', 'User #16'),  -- id 16
    2.24 -  ('now', TRUE, 'user17', 'User #17'),  -- id 17
    2.25 -  ('now', TRUE, 'user18', 'User #18'),  -- id 18
    2.26 -  ('now', TRUE, 'user19', 'User #19'),  -- id 19
    2.27 -  ('now', TRUE, 'user20', 'User #20'),  -- id 20
    2.28 -  ('now', TRUE, 'user21', 'User #21'),  -- id 21
    2.29 -  ('now', TRUE, 'user22', 'User #22'),  -- id 22
    2.30 -  ('now', TRUE, 'user23', 'User #23');  -- id 23
    2.31 +INSERT INTO "member" ("activated", "last_activity", "active", "login", "name") VALUES
    2.32 +  ('now', 'now', TRUE, 'user1',  'User #1'),   -- id  1
    2.33 +  ('now', 'now', TRUE, 'user2',  'User #2'),   -- id  2
    2.34 +  ('now', 'now', TRUE, 'user3',  'User #3'),   -- id  3
    2.35 +  ('now', 'now', TRUE, 'user4',  'User #4'),   -- id  4
    2.36 +  ('now', 'now', TRUE, 'user5',  'User #5'),   -- id  5
    2.37 +  ('now', 'now', TRUE, 'user6',  'User #6'),   -- id  6
    2.38 +  ('now', 'now', TRUE, 'user7',  'User #7'),   -- id  7
    2.39 +  ('now', 'now', TRUE, 'user8',  'User #8'),   -- id  8
    2.40 +  ('now', 'now', TRUE, 'user9',  'User #9'),   -- id  9
    2.41 +  ('now', 'now', TRUE, 'user10', 'User #10'),  -- id 10
    2.42 +  ('now', 'now', TRUE, 'user11', 'User #11'),  -- id 11
    2.43 +  ('now', 'now', TRUE, 'user12', 'User #12'),  -- id 12
    2.44 +  ('now', 'now', TRUE, 'user13', 'User #13'),  -- id 13
    2.45 +  ('now', 'now', TRUE, 'user14', 'User #14'),  -- id 14
    2.46 +  ('now', 'now', TRUE, 'user15', 'User #15'),  -- id 15
    2.47 +  ('now', 'now', TRUE, 'user16', 'User #16'),  -- id 16
    2.48 +  ('now', 'now', TRUE, 'user17', 'User #17'),  -- id 17
    2.49 +  ('now', 'now', TRUE, 'user18', 'User #18'),  -- id 18
    2.50 +  ('now', 'now', TRUE, 'user19', 'User #19'),  -- id 19
    2.51 +  ('now', 'now', TRUE, 'user20', 'User #20'),  -- id 20
    2.52 +  ('now', 'now', TRUE, 'user21', 'User #21'),  -- id 21
    2.53 +  ('now', 'now', TRUE, 'user22', 'User #22'),  -- id 22
    2.54 +  ('now', 'now', TRUE, 'user23', 'User #23');  -- id 23
    2.55  
    2.56  -- set password to "login"
    2.57  UPDATE "member" SET "password" = '$1$PcI6b1Bg$2SHjAZH2nMLFp0fxHis.Q0';
     3.1 --- a/lf_update.c	Fri Jul 29 16:04:17 2011 +0200
     3.2 +++ b/lf_update.c	Fri Jul 29 20:26:45 2011 +0200
     3.3 @@ -54,16 +54,16 @@
     3.4      return 1;
     3.5    }
     3.6  
     3.7 -  // check last login:
     3.8 -  status = PQexec(db, "SELECT \"check_last_login\"()");
     3.9 +  // check member activity:
    3.10 +  status = PQexec(db, "SELECT \"check_activity\"()");
    3.11    if (!status) {
    3.12 -    fprintf(stderr, "Error in pqlib while sending SQL command checking last logins\n");
    3.13 +    fprintf(stderr, "Error in pqlib while sending SQL command checking member activity\n");
    3.14      err = 1;
    3.15    } else if (
    3.16      PQresultStatus(status) != PGRES_COMMAND_OK &&
    3.17      PQresultStatus(status) != PGRES_TUPLES_OK
    3.18    ) {
    3.19 -    fprintf(stderr, "Error while executing SQL command checking last logins:\n%s", PQresultErrorMessage(status));
    3.20 +    fprintf(stderr, "Error while executing SQL command checking member activity:\n%s", PQresultErrorMessage(status));
    3.21      err = 1;
    3.22      PQclear(status);
    3.23    } else {
     4.1 --- a/update/core-update.v1.4.0-v1.5.0.sql	Fri Jul 29 16:04:17 2011 +0200
     4.2 +++ b/update/core-update.v1.4.0-v1.5.0.sql	Fri Jul 29 20:26:45 2011 +0200
     4.3 @@ -9,13 +9,16 @@
     4.4  ALTER TABLE "member" ADD COLUMN "invite_code" TEXT UNIQUE;
     4.5  ALTER TABLE "member" ADD COLUMN "admin_comment" TEXT;
     4.6  ALTER TABLE "member" ADD COLUMN "activated" TIMESTAMPTZ;
     4.7 +ALTER TABLE "member" ADD COLUMN "last_activity" DATE;
     4.8 +ALTER TABLE "member" DROP COLUMN "last_login_public";
     4.9  ALTER TABLE "member" ALTER COLUMN "active" SET DEFAULT FALSE;
    4.10  ALTER TABLE "member" ADD COLUMN "formatting_engine" TEXT;
    4.11  
    4.12  COMMENT ON COLUMN "member"."created"           IS 'Creation of member record and/or invite code';
    4.13  COMMENT ON COLUMN "member"."invite_code"       IS 'Optional invite code, to allow a member to initialize his/her account the first time';
    4.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';
    4.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).';
    4.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';
    4.17 +COMMENT ON COLUMN "member"."last_activity"     IS 'Date of last activity of member; required to be set for "active" members';
    4.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".';
    4.19  COMMENT ON COLUMN "member"."formatting_engine" IS 'Allows different formatting engines (i.e. wiki formats) to be used for "member"."statement"';
    4.20  
    4.21  CREATE TABLE "rendered_member_statement" (
    4.22 @@ -288,6 +291,27 @@
    4.23  
    4.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';
    4.25  
    4.26 +DROP FUNCTION "check_last_login"();
    4.27 +
    4.28 +CREATE FUNCTION "check_activity"()
    4.29 +  RETURNS VOID
    4.30 +  LANGUAGE 'plpgsql' VOLATILE AS $$
    4.31 +    DECLARE
    4.32 +      "system_setting_row" "system_setting"%ROWTYPE;
    4.33 +    BEGIN
    4.34 +      SELECT * INTO "system_setting_row" FROM "system_setting";
    4.35 +      LOCK TABLE "member" IN SHARE ROW EXCLUSIVE MODE;
    4.36 +      IF "system_setting_row"."member_ttl" NOTNULL THEN
    4.37 +        UPDATE "member" SET "active" = FALSE
    4.38 +          WHERE "active" = TRUE
    4.39 +          AND "last_activity" < (now() - "system_setting_row"."member_ttl")::DATE;
    4.40 +      END IF;
    4.41 +      RETURN;
    4.42 +    END;
    4.43 +  $$;
    4.44 +
    4.45 +COMMENT ON FUNCTION "check_activity"() IS 'Deactivates members when "last_activity" is older than "system_setting"."member_ttl".';
    4.46 +
    4.47  CREATE OR REPLACE FUNCTION "create_interest_snapshot"
    4.48    ( "issue_id_p" "issue"."id"%TYPE )
    4.49    RETURNS VOID
    4.50 @@ -1050,7 +1074,7 @@
    4.51      DECLARE
    4.52        "issue_id_v" "issue"."id"%TYPE;
    4.53      BEGIN
    4.54 -      PERFORM "check_last_login"();
    4.55 +      PERFORM "check_activity"();
    4.56        PERFORM "calculate_member_counts"();
    4.57        FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP
    4.58          PERFORM "check_issue"("issue_id_v");
    4.59 @@ -1068,7 +1092,6 @@
    4.60      BEGIN
    4.61        UPDATE "member" SET
    4.62          "last_login"                   = NULL,
    4.63 -        "last_login_public"            = NULL,
    4.64          "login"                        = NULL,
    4.65          "password"                     = NULL,
    4.66          "locked"                       = TRUE,
    4.67 @@ -1172,7 +1195,13 @@
    4.68  
    4.69  BEGIN;
    4.70  
    4.71 -UPDATE "member" SET "activated" = "created";
    4.72 +UPDATE "member" SET
    4.73 +  "activated" = "created",
    4.74 +  "last_activity" = CASE WHEN "active" THEN
    4.75 +    coalesce("last_login"::DATE, now())
    4.76 +  ELSE
    4.77 +    "last_login"::DATE
    4.78 +  END;
    4.79  
    4.80  UPDATE "member" SET
    4.81    "created" = "invite_code"."created",
    4.82 @@ -1254,6 +1283,6 @@
    4.83  
    4.84  COMMIT;
    4.85  
    4.86 -ALTER TABLE "member" ADD CONSTRAINT "not_active_without_activated"
    4.87 -  CHECK ("activated" NOTNULL OR "active" = FALSE);
    4.88 +ALTER TABLE "member" ADD CONSTRAINT "active_requires_activated_and_last_activity"
    4.89 +  CHECK ("active" = FALSE OR ("activated" NOTNULL AND "last_activity" NOTNULL));
    4.90  ALTER TABLE "suggestion" ALTER COLUMN "draft_id" SET NOT NULL;

Impressum / About Us