liquid_feedback_core

changeset 104:0d03c57ebae5

Introduced "system_setting" table with "member_ttl" setting
author jbe
date Mon Jan 24 17:30:18 2011 +0100 (2011-01-24)
parents bc8aa59b0945
children 6bf35cfa3ba8
files core.sql lf_update.c update/core-update.v1.3.0-v1.3.1.sql
line diff
     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");
     2.1 --- a/lf_update.c	Sat Jan 22 20:09:18 2011 +0100
     2.2 +++ b/lf_update.c	Mon Jan 24 17:30:18 2011 +0100
     2.3 @@ -70,16 +70,16 @@
     2.4      PQclear(status);
     2.5    }
     2.6  
     2.7 -  // publish last login:
     2.8 -  status = PQexec(db, "SELECT \"publish_last_login\"()");
     2.9 +  // check last login:
    2.10 +  status = PQexec(db, "SELECT \"check_last_login\"()");
    2.11    if (!status) {
    2.12 -    fprintf(stderr, "Error in pqlib while sending SQL command publishing last logins\n");
    2.13 +    fprintf(stderr, "Error in pqlib while sending SQL command checking last logins\n");
    2.14      err = 1;
    2.15    } else if (
    2.16      PQresultStatus(status) != PGRES_COMMAND_OK &&
    2.17      PQresultStatus(status) != PGRES_TUPLES_OK
    2.18    ) {
    2.19 -    fprintf(stderr, "Error while executing SQL command publishing last logins:\n%s", PQresultErrorMessage(status));
    2.20 +    fprintf(stderr, "Error while executing SQL command checking last logins:\n%s", PQresultErrorMessage(status));
    2.21      err = 1;
    2.22      PQclear(status);
    2.23    } else {
     3.1 --- a/update/core-update.v1.3.0-v1.3.1.sql	Sat Jan 22 20:09:18 2011 +0100
     3.2 +++ b/update/core-update.v1.3.0-v1.3.1.sql	Mon Jan 24 17:30:18 2011 +0100
     3.3 @@ -4,6 +4,14 @@
     3.4    SELECT * FROM (VALUES ('1.3.1', 1, 3, 1))
     3.5    AS "subquery"("string", "major", "minor", "revision");
     3.6  
     3.7 +CREATE TABLE "system_setting" (
     3.8 +        "member_ttl"            INTERVAL );
     3.9 +CREATE UNIQUE INDEX "system_setting_singleton_idx" ON "system_setting" ((1));
    3.10 +
    3.11 +COMMENT ON TABLE "system_setting" IS 'This table contains only one row with different settings in each column.';
    3.12 +COMMENT ON INDEX "system_setting_singleton_idx" IS 'This index ensures that "system_setting" only contains one row maximum.';
    3.13 +COMMENT ON COLUMN "system_setting"."member_ttl" IS 'Time after members get their "active" flag set to FALSE, if they do not login anymore.';
    3.14 +
    3.15  ALTER TABLE "member" ADD COLUMN "last_login_public" DATE;
    3.16  ALTER TABLE "member" ADD COLUMN "locked" BOOLEAN NOT NULL DEFAULT FALSE;
    3.17  
    3.18 @@ -12,18 +20,27 @@
    3.19  COMMENT ON COLUMN "member"."locked"            IS 'Locked members can not log in.';
    3.20  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.';
    3.21  
    3.22 -CREATE FUNCTION "publish_last_login"()
    3.23 +CREATE FUNCTION "check_last_login"()
    3.24    RETURNS VOID
    3.25    LANGUAGE 'plpgsql' VOLATILE AS $$
    3.26 +    DECLARE
    3.27 +      "system_setting_row" "system_setting"%ROWTYPE;
    3.28      BEGIN
    3.29 +      SELECT * INTO "system_setting_row" FROM "system_setting";
    3.30        LOCK TABLE "member" IN SHARE ROW EXCLUSIVE MODE;
    3.31        UPDATE "member" SET "last_login_public" = "last_login"::date
    3.32          WHERE "last_login"::date < 'today';
    3.33 +      IF "system_setting_row"."member_ttl" NOTNULL THEN
    3.34 +        UPDATE "member" SET "active" = FALSE
    3.35 +          WHERE "active" = TRUE
    3.36 +          AND "last_login_public" <
    3.37 +            (now() - "system_setting_row"."member_ttl")::date;
    3.38 +      END IF;
    3.39        RETURN;
    3.40      END;
    3.41    $$;
    3.42  
    3.43 -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.';
    3.44 +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.';
    3.45  
    3.46  CREATE OR REPLACE FUNCTION "create_interest_snapshot"
    3.47    ( "issue_id_p" "issue"."id"%TYPE )
    3.48 @@ -103,7 +120,7 @@
    3.49        "issue_id_v" "issue"."id"%TYPE;
    3.50      BEGIN
    3.51        DELETE FROM "expired_session";
    3.52 -      PERFORM "publish_last_login"();
    3.53 +      PERFORM "check_last_login"();
    3.54        PERFORM "calculate_member_counts"();
    3.55        FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP
    3.56          PERFORM "check_issue"("issue_id_v");

Impressum / About Us