liquid_feedback_core

changeset 103:bc8aa59b0945

Introduced "last_login_public" field, which is only updated after each day and does only contain date but not time for privacy reasons
(required changes in lf_update)
author jbe
date Sat Jan 22 20:09:18 2011 +0100 (2011-01-22)
parents 2daa8ce3d743
children 0d03c57ebae5
files core.sql lf_update.c update/core-update.v1.3.0-v1.3.1.sql
line diff
     1.1 --- a/core.sql	Tue Jan 04 02:55:01 2011 +0100
     1.2 +++ b/core.sql	Sat Jan 22 20:09:18 2011 +0100
     1.3 @@ -58,6 +58,7 @@
     1.4          "id"                    SERIAL4         PRIMARY KEY,
     1.5          "created"               TIMESTAMPTZ     NOT NULL DEFAULT now(),
     1.6          "last_login"            TIMESTAMPTZ,
     1.7 +        "last_login_public"     DATE,
     1.8          "login"                 TEXT            UNIQUE,
     1.9          "password"              TEXT,
    1.10          "locked"                BOOLEAN         NOT NULL DEFAULT FALSE,
    1.11 @@ -98,6 +99,8 @@
    1.12  
    1.13  COMMENT ON TABLE "member" IS 'Users of the system, e.g. members of an organization';
    1.14  
    1.15 +COMMENT ON COLUMN "member"."last_login"           IS 'Timestamp of last login';
    1.16 +COMMENT ON COLUMN "member"."last_login_public"    IS 'Date of last login (time stripped for privacy reasons, updated only after day change)';
    1.17  COMMENT ON COLUMN "member"."login"                IS 'Login name';
    1.18  COMMENT ON COLUMN "member"."password"             IS 'Password (preferably as crypto-hash, depending on the frontend or access layer)';
    1.19  COMMENT ON COLUMN "member"."locked"               IS 'Locked members can not log in.';
    1.20 @@ -2038,9 +2041,23 @@
    1.21  
    1.22  
    1.23  
    1.24 --------------------------------
    1.25 --- Materialize member counts --
    1.26 --------------------------------
    1.27 +------------------------------------------------------------------------
    1.28 +-- Regular tasks, except calculcation of snapshots and voting results --
    1.29 +------------------------------------------------------------------------
    1.30 +
    1.31 +CREATE FUNCTION "publish_last_login"()
    1.32 +  RETURNS VOID
    1.33 +  LANGUAGE 'plpgsql' VOLATILE AS $$
    1.34 +    BEGIN
    1.35 +      LOCK TABLE "member" IN SHARE ROW EXCLUSIVE MODE;
    1.36 +      UPDATE "member" SET "last_login_public" = "last_login"::date
    1.37 +        WHERE "last_login"::date < 'today';
    1.38 +      RETURN;
    1.39 +    END;
    1.40 +  $$;
    1.41 +
    1.42 +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.43 +
    1.44  
    1.45  CREATE FUNCTION "calculate_member_counts"()
    1.46    RETURNS VOID
    1.47 @@ -3280,6 +3297,7 @@
    1.48        "issue_id_v" "issue"."id"%TYPE;
    1.49      BEGIN
    1.50        DELETE FROM "expired_session";
    1.51 +      PERFORM "publish_last_login"();
    1.52        PERFORM "calculate_member_counts"();
    1.53        FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP
    1.54          PERFORM "check_issue"("issue_id_v");
    1.55 @@ -3291,7 +3309,7 @@
    1.56      END;
    1.57    $$;
    1.58  
    1.59 -COMMENT ON FUNCTION "check_everything"() IS 'Perform "check_issue" for every open issue, and if possible, automatically calculate ranks. Use this function only for development and debugging purposes, as long transactions with exclusive locking may result.';
    1.60 +COMMENT ON FUNCTION "check_everything"() IS 'Amongst other regular tasks this function performs "check_issue" for every open issue, and if possible, automatically calculates ranks. Use this function only for development and debugging purposes, as long transactions with exclusive locking may result. In productive environments you should call the lf_update program instead.';
    1.61  
    1.62  
    1.63  
    1.64 @@ -3351,6 +3369,7 @@
    1.65      BEGIN
    1.66        UPDATE "member" SET
    1.67          "last_login"                   = NULL,
    1.68 +        "last_login_public"            = NULL,
    1.69          "login"                        = NULL,
    1.70          "password"                     = NULL,
    1.71          "locked"                       = TRUE,
    1.72 @@ -3450,7 +3469,7 @@
    1.73      END;
    1.74    $$;
    1.75  
    1.76 -COMMENT ON FUNCTION "delete_private_data"() IS 'DO NOT USE on productive database, but only on a copy! This function deletes all data which should not be publicly available, and can be used to create a database dump for publication.';
    1.77 +COMMENT ON FUNCTION "delete_private_data"() IS 'Used by lf_export script. DO NOT USE on productive database, but only on a copy! This function deletes all data which should not be publicly available, and can be used to create a database dump for publication.';
    1.78  
    1.79  
    1.80  
     2.1 --- a/lf_update.c	Tue Jan 04 02:55:01 2011 +0100
     2.2 +++ b/lf_update.c	Sat Jan 22 20:09:18 2011 +0100
     2.3 @@ -70,6 +70,22 @@
     2.4      PQclear(status);
     2.5    }
     2.6  
     2.7 +  // publish last login:
     2.8 +  status = PQexec(db, "SELECT \"publish_last_login\"()");
     2.9 +  if (!status) {
    2.10 +    fprintf(stderr, "Error in pqlib while sending SQL command publishing last logins\n");
    2.11 +    err = 1;
    2.12 +  } else if (
    2.13 +    PQresultStatus(status) != PGRES_COMMAND_OK &&
    2.14 +    PQresultStatus(status) != PGRES_TUPLES_OK
    2.15 +  ) {
    2.16 +    fprintf(stderr, "Error while executing SQL command publishing last logins:\n%s", PQresultErrorMessage(status));
    2.17 +    err = 1;
    2.18 +    PQclear(status);
    2.19 +  } else {
    2.20 +    PQclear(status);
    2.21 +  }
    2.22 +
    2.23    // calculate member counts:
    2.24    status = PQexec(db, "SELECT \"calculate_member_counts\"()");
    2.25    if (!status) {
     3.1 --- a/update/core-update.v1.3.0-v1.3.1.sql	Tue Jan 04 02:55:01 2011 +0100
     3.2 +++ b/update/core-update.v1.3.0-v1.3.1.sql	Sat Jan 22 20:09:18 2011 +0100
     3.3 @@ -4,10 +4,26 @@
     3.4    SELECT * FROM (VALUES ('1.3.1', 1, 3, 1))
     3.5    AS "subquery"("string", "major", "minor", "revision");
     3.6  
     3.7 +ALTER TABLE "member" ADD COLUMN "last_login_public" DATE;
     3.8  ALTER TABLE "member" ADD COLUMN "locked" BOOLEAN NOT NULL DEFAULT FALSE;
     3.9  
    3.10 -COMMENT ON COLUMN "member"."locked" IS 'Locked members can not log in.';
    3.11 -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.12 +COMMENT ON COLUMN "member"."last_login"        IS 'Timestamp of last login';
    3.13 +COMMENT ON COLUMN "member"."last_login_public" IS 'Date of last login (time stripped for privacy reasons, updated only after day change)';
    3.14 +COMMENT ON COLUMN "member"."locked"            IS 'Locked members can not log in.';
    3.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.';
    3.16 +
    3.17 +CREATE FUNCTION "publish_last_login"()
    3.18 +  RETURNS VOID
    3.19 +  LANGUAGE 'plpgsql' VOLATILE AS $$
    3.20 +    BEGIN
    3.21 +      LOCK TABLE "member" IN SHARE ROW EXCLUSIVE MODE;
    3.22 +      UPDATE "member" SET "last_login_public" = "last_login"::date
    3.23 +        WHERE "last_login"::date < 'today';
    3.24 +      RETURN;
    3.25 +    END;
    3.26 +  $$;
    3.27 +
    3.28 +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.29  
    3.30  CREATE OR REPLACE FUNCTION "create_interest_snapshot"
    3.31    ( "issue_id_p" "issue"."id"%TYPE )
    3.32 @@ -80,4 +96,79 @@
    3.33      END;
    3.34    $$;
    3.35  
    3.36 +CREATE OR REPLACE FUNCTION "check_everything"()
    3.37 +  RETURNS VOID
    3.38 +  LANGUAGE 'plpgsql' VOLATILE AS $$
    3.39 +    DECLARE
    3.40 +      "issue_id_v" "issue"."id"%TYPE;
    3.41 +    BEGIN
    3.42 +      DELETE FROM "expired_session";
    3.43 +      PERFORM "publish_last_login"();
    3.44 +      PERFORM "calculate_member_counts"();
    3.45 +      FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP
    3.46 +        PERFORM "check_issue"("issue_id_v");
    3.47 +      END LOOP;
    3.48 +      FOR "issue_id_v" IN SELECT "id" FROM "issue_with_ranks_missing" LOOP
    3.49 +        PERFORM "calculate_ranks"("issue_id_v");
    3.50 +      END LOOP;
    3.51 +      RETURN;
    3.52 +    END;
    3.53 +  $$;
    3.54 +
    3.55 +COMMENT ON FUNCTION "check_everything"() IS 'Amongst other regular tasks this function performs "check_issue" for every open issue, and if possible, automatically calculates ranks. Use this function only for development and debugging purposes, as long transactions with exclusive locking may result. In productive environments you should call the lf_update program instead.';
    3.56 +
    3.57 +CREATE OR REPLACE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)
    3.58 +  RETURNS VOID
    3.59 +  LANGUAGE 'plpgsql' VOLATILE AS $$
    3.60 +    BEGIN
    3.61 +      UPDATE "member" SET
    3.62 +        "last_login"                   = NULL,
    3.63 +        "last_login_public"            = NULL,
    3.64 +        "login"                        = NULL,
    3.65 +        "password"                     = NULL,
    3.66 +        "locked"                       = TRUE,
    3.67 +        "active"                       = FALSE,
    3.68 +        "notify_email"                 = NULL,
    3.69 +        "notify_email_unconfirmed"     = NULL,
    3.70 +        "notify_email_secret"          = NULL,
    3.71 +        "notify_email_secret_expiry"   = NULL,
    3.72 +        "notify_email_lock_expiry"     = NULL,
    3.73 +        "password_reset_secret"        = NULL,
    3.74 +        "password_reset_secret_expiry" = NULL,
    3.75 +        "organizational_unit"          = NULL,
    3.76 +        "internal_posts"               = NULL,
    3.77 +        "realname"                     = NULL,
    3.78 +        "birthday"                     = NULL,
    3.79 +        "address"                      = NULL,
    3.80 +        "email"                        = NULL,
    3.81 +        "xmpp_address"                 = NULL,
    3.82 +        "website"                      = NULL,
    3.83 +        "phone"                        = NULL,
    3.84 +        "mobile_phone"                 = NULL,
    3.85 +        "profession"                   = NULL,
    3.86 +        "external_memberships"         = NULL,
    3.87 +        "external_posts"               = NULL,
    3.88 +        "statement"                    = NULL
    3.89 +        WHERE "id" = "member_id_p";
    3.90 +      -- "text_search_data" is updated by triggers
    3.91 +      DELETE FROM "setting"            WHERE "member_id" = "member_id_p";
    3.92 +      DELETE FROM "setting_map"        WHERE "member_id" = "member_id_p";
    3.93 +      DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p";
    3.94 +      DELETE FROM "member_image"       WHERE "member_id" = "member_id_p";
    3.95 +      DELETE FROM "contact"            WHERE "member_id" = "member_id_p";
    3.96 +      DELETE FROM "area_setting"       WHERE "member_id" = "member_id_p";
    3.97 +      DELETE FROM "issue_setting"      WHERE "member_id" = "member_id_p";
    3.98 +      DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p";
    3.99 +      DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p";
   3.100 +      DELETE FROM "membership"         WHERE "member_id" = "member_id_p";
   3.101 +      DELETE FROM "ignored_issue"      WHERE "member_id" = "member_id_p";
   3.102 +      DELETE FROM "delegation"         WHERE "truster_id" = "member_id_p";
   3.103 +      DELETE FROM "direct_voter" USING "issue"
   3.104 +        WHERE "direct_voter"."issue_id" = "issue"."id"
   3.105 +        AND "issue"."closed" ISNULL
   3.106 +        AND "member_id" = "member_id_p";
   3.107 +      RETURN;
   3.108 +    END;
   3.109 +  $$;
   3.110 +
   3.111  COMMIT;

Impressum / About Us