jbe@102: BEGIN; jbe@102: jbe@102: CREATE OR REPLACE VIEW "liquid_feedback_version" AS jbe@102: SELECT * FROM (VALUES ('1.3.1', 1, 3, 1)) jbe@102: AS "subquery"("string", "major", "minor", "revision"); jbe@102: jbe@104: CREATE TABLE "system_setting" ( jbe@104: "member_ttl" INTERVAL ); jbe@104: CREATE UNIQUE INDEX "system_setting_singleton_idx" ON "system_setting" ((1)); jbe@104: jbe@104: COMMENT ON TABLE "system_setting" IS 'This table contains only one row with different settings in each column.'; jbe@104: COMMENT ON INDEX "system_setting_singleton_idx" IS 'This index ensures that "system_setting" only contains one row maximum.'; jbe@104: COMMENT ON COLUMN "system_setting"."member_ttl" IS 'Time after members get their "active" flag set to FALSE, if they do not login anymore.'; jbe@104: jbe@103: ALTER TABLE "member" ADD COLUMN "last_login_public" DATE; jbe@102: ALTER TABLE "member" ADD COLUMN "locked" BOOLEAN NOT NULL DEFAULT FALSE; jbe@102: jbe@107: UPDATE "member" SET "locked" = TRUE WHERE "active" = FALSE; jbe@107: jbe@103: COMMENT ON COLUMN "member"."last_login" IS 'Timestamp of last login'; jbe@103: COMMENT ON COLUMN "member"."last_login_public" IS 'Date of last login (time stripped for privacy reasons, updated only after day change)'; jbe@103: COMMENT ON COLUMN "member"."locked" IS 'Locked members can not log in.'; jbe@103: 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.'; jbe@103: jbe@104: CREATE FUNCTION "check_last_login"() jbe@103: RETURNS VOID jbe@103: LANGUAGE 'plpgsql' VOLATILE AS $$ jbe@104: DECLARE jbe@104: "system_setting_row" "system_setting"%ROWTYPE; jbe@103: BEGIN jbe@104: SELECT * INTO "system_setting_row" FROM "system_setting"; jbe@103: LOCK TABLE "member" IN SHARE ROW EXCLUSIVE MODE; jbe@103: UPDATE "member" SET "last_login_public" = "last_login"::date jbe@107: FROM ( jbe@107: SELECT DISTINCT "member"."id" jbe@107: FROM "member" LEFT JOIN "member_history" jbe@107: ON "member"."id" = "member_history"."member_id" jbe@107: WHERE "member"."last_login"::date < 'today' OR ( jbe@107: "member_history"."until"::date >= 'today' AND jbe@107: "member_history"."active" = FALSE AND "member"."active" = TRUE jbe@107: ) jbe@107: ) AS "subquery" jbe@107: WHERE "member"."id" = "subquery"."id"; jbe@104: IF "system_setting_row"."member_ttl" NOTNULL THEN jbe@104: UPDATE "member" SET "active" = FALSE jbe@104: WHERE "active" = TRUE jbe@107: AND "last_login"::date < 'today' jbe@104: AND "last_login_public" < jbe@104: (now() - "system_setting_row"."member_ttl")::date; jbe@104: END IF; jbe@103: RETURN; jbe@103: END; jbe@103: $$; jbe@103: jbe@107: 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).'; jbe@102: jbe@102: CREATE OR REPLACE FUNCTION "create_interest_snapshot" jbe@102: ( "issue_id_p" "issue"."id"%TYPE ) jbe@102: RETURNS VOID jbe@102: LANGUAGE 'plpgsql' VOLATILE AS $$ jbe@102: DECLARE jbe@102: "member_id_v" "member"."id"%TYPE; jbe@102: BEGIN jbe@102: DELETE FROM "direct_interest_snapshot" jbe@102: WHERE "issue_id" = "issue_id_p" jbe@102: AND "event" = 'periodic'; jbe@102: DELETE FROM "delegating_interest_snapshot" jbe@102: WHERE "issue_id" = "issue_id_p" jbe@102: AND "event" = 'periodic'; jbe@102: DELETE FROM "direct_supporter_snapshot" jbe@102: WHERE "issue_id" = "issue_id_p" jbe@102: AND "event" = 'periodic'; jbe@102: INSERT INTO "direct_interest_snapshot" jbe@102: ("issue_id", "event", "member_id", "voting_requested") jbe@102: SELECT jbe@102: "issue_id_p" AS "issue_id", jbe@102: 'periodic' AS "event", jbe@102: "member"."id" AS "member_id", jbe@102: "interest"."voting_requested" jbe@102: FROM "interest" JOIN "member" jbe@102: ON "interest"."member_id" = "member"."id" jbe@102: WHERE "interest"."issue_id" = "issue_id_p" jbe@102: AND "member"."active"; jbe@102: FOR "member_id_v" IN jbe@102: SELECT "member_id" FROM "direct_interest_snapshot" jbe@102: WHERE "issue_id" = "issue_id_p" jbe@102: AND "event" = 'periodic' jbe@102: LOOP jbe@102: UPDATE "direct_interest_snapshot" SET jbe@102: "weight" = 1 + jbe@102: "weight_of_added_delegations_for_interest_snapshot"( jbe@102: "issue_id_p", jbe@102: "member_id_v", jbe@102: '{}' jbe@102: ) jbe@102: WHERE "issue_id" = "issue_id_p" jbe@102: AND "event" = 'periodic' jbe@102: AND "member_id" = "member_id_v"; jbe@102: END LOOP; jbe@102: INSERT INTO "direct_supporter_snapshot" jbe@102: ( "issue_id", "initiative_id", "event", "member_id", jbe@102: "informed", "satisfied" ) jbe@102: SELECT jbe@102: "issue_id_p" AS "issue_id", jbe@102: "initiative"."id" AS "initiative_id", jbe@102: 'periodic' AS "event", jbe@102: "supporter"."member_id" AS "member_id", jbe@102: "supporter"."draft_id" = "current_draft"."id" AS "informed", jbe@102: NOT EXISTS ( jbe@102: SELECT NULL FROM "critical_opinion" jbe@102: WHERE "initiative_id" = "initiative"."id" jbe@102: AND "member_id" = "supporter"."member_id" jbe@102: ) AS "satisfied" jbe@102: FROM "initiative" jbe@102: JOIN "supporter" jbe@102: ON "supporter"."initiative_id" = "initiative"."id" jbe@102: JOIN "current_draft" jbe@102: ON "initiative"."id" = "current_draft"."initiative_id" jbe@102: JOIN "direct_interest_snapshot" jbe@102: ON "supporter"."member_id" = "direct_interest_snapshot"."member_id" jbe@102: AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id" jbe@102: AND "event" = 'periodic' jbe@102: WHERE "initiative"."issue_id" = "issue_id_p"; jbe@102: RETURN; jbe@102: END; jbe@102: $$; jbe@102: jbe@103: CREATE OR REPLACE FUNCTION "check_everything"() jbe@103: RETURNS VOID jbe@103: LANGUAGE 'plpgsql' VOLATILE AS $$ jbe@103: DECLARE jbe@103: "issue_id_v" "issue"."id"%TYPE; jbe@103: BEGIN jbe@103: DELETE FROM "expired_session"; jbe@104: PERFORM "check_last_login"(); jbe@103: PERFORM "calculate_member_counts"(); jbe@103: FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP jbe@103: PERFORM "check_issue"("issue_id_v"); jbe@103: END LOOP; jbe@103: FOR "issue_id_v" IN SELECT "id" FROM "issue_with_ranks_missing" LOOP jbe@103: PERFORM "calculate_ranks"("issue_id_v"); jbe@103: END LOOP; jbe@103: RETURN; jbe@103: END; jbe@103: $$; jbe@103: jbe@103: 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.'; jbe@103: jbe@103: CREATE OR REPLACE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE) jbe@103: RETURNS VOID jbe@103: LANGUAGE 'plpgsql' VOLATILE AS $$ jbe@103: BEGIN jbe@103: UPDATE "member" SET jbe@103: "last_login" = NULL, jbe@103: "last_login_public" = NULL, jbe@103: "login" = NULL, jbe@103: "password" = NULL, jbe@103: "locked" = TRUE, jbe@103: "active" = FALSE, jbe@103: "notify_email" = NULL, jbe@103: "notify_email_unconfirmed" = NULL, jbe@103: "notify_email_secret" = NULL, jbe@103: "notify_email_secret_expiry" = NULL, jbe@103: "notify_email_lock_expiry" = NULL, jbe@103: "password_reset_secret" = NULL, jbe@103: "password_reset_secret_expiry" = NULL, jbe@103: "organizational_unit" = NULL, jbe@103: "internal_posts" = NULL, jbe@103: "realname" = NULL, jbe@103: "birthday" = NULL, jbe@103: "address" = NULL, jbe@103: "email" = NULL, jbe@103: "xmpp_address" = NULL, jbe@103: "website" = NULL, jbe@103: "phone" = NULL, jbe@103: "mobile_phone" = NULL, jbe@103: "profession" = NULL, jbe@103: "external_memberships" = NULL, jbe@103: "external_posts" = NULL, jbe@103: "statement" = NULL jbe@103: WHERE "id" = "member_id_p"; jbe@103: -- "text_search_data" is updated by triggers jbe@103: DELETE FROM "setting" WHERE "member_id" = "member_id_p"; jbe@103: DELETE FROM "setting_map" WHERE "member_id" = "member_id_p"; jbe@103: DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p"; jbe@103: DELETE FROM "member_image" WHERE "member_id" = "member_id_p"; jbe@103: DELETE FROM "contact" WHERE "member_id" = "member_id_p"; jbe@103: DELETE FROM "area_setting" WHERE "member_id" = "member_id_p"; jbe@103: DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p"; jbe@103: DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p"; jbe@103: DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p"; jbe@103: DELETE FROM "membership" WHERE "member_id" = "member_id_p"; jbe@103: DELETE FROM "ignored_issue" WHERE "member_id" = "member_id_p"; jbe@103: DELETE FROM "delegation" WHERE "truster_id" = "member_id_p"; jbe@103: DELETE FROM "direct_voter" USING "issue" jbe@103: WHERE "direct_voter"."issue_id" = "issue"."id" jbe@103: AND "issue"."closed" ISNULL jbe@103: AND "member_id" = "member_id_p"; jbe@103: RETURN; jbe@103: END; jbe@103: $$; jbe@103: jbe@102: COMMIT;