# HG changeset patch # User jbe # Date 1366474957 -7200 # Node ID ae69cf82c05f778ce943364cef6ad64279c417da # Parent 0b0bfcf2e60c17ec020d8ad189b63c19134b96b2 Added support for delegation checks and login recovery; Removed unnecessary join in event views diff -r 0b0bfcf2e60c -r ae69cf82c05f core.sql --- a/core.sql Sat Mar 23 13:11:08 2013 +0100 +++ b/core.sql Sat Apr 20 18:22:37 2013 +0200 @@ -7,7 +7,7 @@ BEGIN; CREATE VIEW "liquid_feedback_version" AS - SELECT * FROM (VALUES ('2.2.3', 2, 2, 3)) + SELECT * FROM (VALUES ('2.2.4', 2, 2, 4)) AS "subquery"("string", "major", "minor", "revision"); @@ -104,6 +104,7 @@ "activated" TIMESTAMPTZ, "last_activity" DATE, "last_login" TIMESTAMPTZ, + "last_delegation_check" TIMESTAMPTZ, "login" TEXT UNIQUE, "password" TEXT, "locked" BOOLEAN NOT NULL DEFAULT FALSE, @@ -116,6 +117,7 @@ "notify_email_secret_expiry" TIMESTAMPTZ, "notify_email_lock_expiry" TIMESTAMPTZ, "notify_level" "notify_level", + "login_recovery_expiry" TIMESTAMPTZ, "password_reset_secret" TEXT UNIQUE, "password_reset_secret_expiry" TIMESTAMPTZ, "name" TEXT UNIQUE, @@ -159,6 +161,7 @@ COMMENT ON COLUMN "member"."activated" IS 'Timestamp of first activation of account (i.e. usage of "invite_code"); required to be set for "active" members'; COMMENT ON COLUMN "member"."last_activity" IS 'Date of last activity of member; required to be set for "active" members'; COMMENT ON COLUMN "member"."last_login" IS 'Timestamp of last login'; +COMMENT ON COLUMN "member"."last_delegation_check" IS 'Timestamp of last delegation check (i.e. confirmation of all unit and area delegations)'; COMMENT ON COLUMN "member"."login" IS 'Login name'; COMMENT ON COLUMN "member"."password" IS 'Password (preferably as crypto-hash, depending on the frontend or access layer)'; COMMENT ON COLUMN "member"."locked" IS 'Locked members can not log in.'; @@ -171,6 +174,9 @@ COMMENT ON COLUMN "member"."notify_email_secret_expiry" IS 'Expiry date/time for "notify_email_secret"'; COMMENT ON COLUMN "member"."notify_email_lock_expiry" IS 'Date/time until no further email confirmation mails may be sent (abuse protection)'; COMMENT ON COLUMN "member"."notify_level" IS 'Selects which event notifications are to be sent to the "notify_email" mail address, may be NULL if member did not make any selection yet'; +COMMENT ON COLUMN "member"."login_recovery_expiry" IS 'Date/time after which another login recovery attempt is allowed'; +COMMENT ON COLUMN "member"."password_reset_secret" IS 'Secret string sent via e-mail for password recovery'; +COMMENT ON COLUMN "member"."password_reset_secret_expiry" IS 'Date/time until the password recovery secret is valid, and date/time after which another password recovery attempt is allowed'; COMMENT ON COLUMN "member"."name" IS 'Distinct name of the member, may be NULL if account has not been activated yet'; COMMENT ON COLUMN "member"."identification" IS 'Optional identification number or code of the member'; COMMENT ON COLUMN "member"."authentication" IS 'Information about how this member was authenticated'; @@ -329,6 +335,7 @@ "additional_secret" TEXT, "expiry" TIMESTAMPTZ NOT NULL DEFAULT now() + '24 hours', "member_id" INT8 REFERENCES "member" ("id") ON DELETE SET NULL, + "needs_delegation_check" BOOLEAN NOT NULL DEFAULT FALSE, "lang" TEXT ); CREATE INDEX "session_expiry_idx" ON "session" ("expiry"); @@ -337,6 +344,7 @@ COMMENT ON COLUMN "session"."ident" IS 'Secret session identifier (i.e. random string)'; COMMENT ON COLUMN "session"."additional_secret" IS 'Additional field to store a secret, which can be used against CSRF attacks'; COMMENT ON COLUMN "session"."member_id" IS 'Reference to member, who is logged in'; +COMMENT ON COLUMN "session"."needs_delegation_check" IS 'Set to TRUE, if member must perform a delegation check to proceed with login; see column "last_delegation_check" in "member" table'; COMMENT ON COLUMN "session"."lang" IS 'Language code of the selected language'; @@ -2245,9 +2253,6 @@ LEFT JOIN "interest" ON "member"."id" = "interest"."member_id" AND "event"."issue_id" = "interest"."issue_id" - LEFT JOIN "supporter" - ON "member"."id" = "supporter"."member_id" - AND "event"."initiative_id" = "supporter"."initiative_id" LEFT JOIN "ignored_member" ON "member"."id" = "ignored_member"."member_id" AND "event"."member_id" = "ignored_member"."other_member_id" @@ -2255,7 +2260,6 @@ ON "member"."id" = "ignored_initiative"."member_id" AND "event"."initiative_id" = "ignored_initiative"."initiative_id" WHERE ( - "supporter"."member_id" NOTNULL OR "interest"."member_id" NOTNULL OR ( "membership"."member_id" NOTNULL AND "event"."event" IN ( @@ -2306,9 +2310,6 @@ LEFT JOIN "interest" ON "member"."id" = "interest"."member_id" AND "event"."issue_id" = "interest"."issue_id" - LEFT JOIN "supporter" - ON "member"."id" = "supporter"."member_id" - AND "event"."initiative_id" = "supporter"."initiative_id" LEFT JOIN "ignored_member" ON "member"."id" = "ignored_member"."member_id" AND "event"."member_id" = "ignored_member"."other_member_id" @@ -2332,7 +2333,6 @@ 'discussion', 'canceled_after_revocation_during_discussion' ) ) ) AND ( - "supporter"."member_id" NOTNULL OR "interest"."member_id" NOTNULL OR ( "membership"."member_id" NOTNULL AND "event"."event" IN ( @@ -4391,6 +4391,7 @@ BEGIN UPDATE "member" SET "last_login" = NULL, + "last_delegation_check" = NULL, "login" = NULL, "password" = NULL, "locked" = TRUE, @@ -4400,6 +4401,7 @@ "notify_email_secret" = NULL, "notify_email_secret_expiry" = NULL, "notify_email_lock_expiry" = NULL, + "login_recovery_expiry" = NULL, "password_reset_secret" = NULL, "password_reset_secret_expiry" = NULL, "organizational_unit" = NULL, @@ -4455,6 +4457,7 @@ "invite_code_expiry" = NULL, "admin_comment" = NULL, "last_login" = NULL, + "last_delegation_check" = NULL, "login" = NULL, "password" = NULL, "lang" = NULL, @@ -4464,6 +4467,7 @@ "notify_email_secret_expiry" = NULL, "notify_email_lock_expiry" = NULL, "notify_level" = NULL, + "login_recovery_expiry" = NULL, "password_reset_secret" = NULL, "password_reset_secret_expiry" = NULL, "organizational_unit" = NULL, diff -r 0b0bfcf2e60c -r ae69cf82c05f update/core-update.v2.2.3-v2.2.4.sql --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/update/core-update.v2.2.3-v2.2.4.sql Sat Apr 20 18:22:37 2013 +0200 @@ -0,0 +1,261 @@ +BEGIN; + +CREATE OR REPLACE VIEW "liquid_feedback_version" AS + SELECT * FROM (VALUES ('2.2.4', 2, 2, 4)) + AS "subquery"("string", "major", "minor", "revision"); + + +ALTER TABLE "member" ADD COLUMN "last_delegation_check" TIMESTAMPTZ; +ALTER TABLE "member" ADD COLUMN "login_recovery_expiry" TIMESTAMPTZ; + +COMMENT ON COLUMN "member"."last_delegation_check" IS 'Timestamp of last delegation check (i.e. confirmation of all unit and area delegations)'; +COMMENT ON COLUMN "member"."login_recovery_expiry" IS 'Date/time after which another login recovery attempt is allowed'; +COMMENT ON COLUMN "member"."password_reset_secret" IS 'Secret string sent via e-mail for password recovery'; +COMMENT ON COLUMN "member"."password_reset_secret_expiry" IS 'Date/time until the password recovery secret is valid, and date/time after which another password recovery attempt is allowed'; + +ALTER TABLE "session" ADD COLUMN "needs_delegation_check" BOOLEAN NOT NULL DEFAULT FALSE; +COMMENT ON COLUMN "session"."needs_delegation_check" IS 'Set to TRUE, if member must perform a delegation check to proceed with login; see column "last_delegation_check" in "member" table'; + +CREATE OR REPLACE VIEW "event_seen_by_member" AS + SELECT + "member"."id" AS "seen_by_member_id", + CASE WHEN "event"."state" IN ( + 'voting', + 'finished_without_winner', + 'finished_with_winner' + ) THEN + 'voting'::"notify_level" + ELSE + CASE WHEN "event"."state" IN ( + 'verification', + 'canceled_after_revocation_during_verification', + 'canceled_no_initiative_admitted' + ) THEN + 'verification'::"notify_level" + ELSE + CASE WHEN "event"."state" IN ( + 'discussion', + 'canceled_after_revocation_during_discussion' + ) THEN + 'discussion'::"notify_level" + ELSE + 'all'::"notify_level" + END + END + END AS "notify_level", + "event".* + FROM "member" CROSS JOIN "event" + LEFT JOIN "issue" + ON "event"."issue_id" = "issue"."id" + LEFT JOIN "membership" + ON "member"."id" = "membership"."member_id" + AND "issue"."area_id" = "membership"."area_id" + LEFT JOIN "interest" + ON "member"."id" = "interest"."member_id" + AND "event"."issue_id" = "interest"."issue_id" + LEFT JOIN "ignored_member" + ON "member"."id" = "ignored_member"."member_id" + AND "event"."member_id" = "ignored_member"."other_member_id" + LEFT JOIN "ignored_initiative" + ON "member"."id" = "ignored_initiative"."member_id" + AND "event"."initiative_id" = "ignored_initiative"."initiative_id" + WHERE ( + "interest"."member_id" NOTNULL OR + ( "membership"."member_id" NOTNULL AND + "event"."event" IN ( + 'issue_state_changed', + 'initiative_created_in_new_issue', + 'initiative_created_in_existing_issue', + 'initiative_revoked' ) ) ) + AND "ignored_member"."member_id" ISNULL + AND "ignored_initiative"."member_id" ISNULL; + +CREATE OR REPLACE VIEW "selected_event_seen_by_member" AS + SELECT + "member"."id" AS "seen_by_member_id", + CASE WHEN "event"."state" IN ( + 'voting', + 'finished_without_winner', + 'finished_with_winner' + ) THEN + 'voting'::"notify_level" + ELSE + CASE WHEN "event"."state" IN ( + 'verification', + 'canceled_after_revocation_during_verification', + 'canceled_no_initiative_admitted' + ) THEN + 'verification'::"notify_level" + ELSE + CASE WHEN "event"."state" IN ( + 'discussion', + 'canceled_after_revocation_during_discussion' + ) THEN + 'discussion'::"notify_level" + ELSE + 'all'::"notify_level" + END + END + END AS "notify_level", + "event".* + FROM "member" CROSS JOIN "event" + LEFT JOIN "issue" + ON "event"."issue_id" = "issue"."id" + LEFT JOIN "membership" + ON "member"."id" = "membership"."member_id" + AND "issue"."area_id" = "membership"."area_id" + LEFT JOIN "interest" + ON "member"."id" = "interest"."member_id" + AND "event"."issue_id" = "interest"."issue_id" + LEFT JOIN "ignored_member" + ON "member"."id" = "ignored_member"."member_id" + AND "event"."member_id" = "ignored_member"."other_member_id" + LEFT JOIN "ignored_initiative" + ON "member"."id" = "ignored_initiative"."member_id" + AND "event"."initiative_id" = "ignored_initiative"."initiative_id" + WHERE ( + ( "member"."notify_level" >= 'all' ) OR + ( "member"."notify_level" >= 'voting' AND + "event"."state" IN ( + 'voting', + 'finished_without_winner', + 'finished_with_winner' ) ) OR + ( "member"."notify_level" >= 'verification' AND + "event"."state" IN ( + 'verification', + 'canceled_after_revocation_during_verification', + 'canceled_no_initiative_admitted' ) ) OR + ( "member"."notify_level" >= 'discussion' AND + "event"."state" IN ( + 'discussion', + 'canceled_after_revocation_during_discussion' ) ) ) + AND ( + "interest"."member_id" NOTNULL OR + ( "membership"."member_id" NOTNULL AND + "event"."event" IN ( + 'issue_state_changed', + 'initiative_created_in_new_issue', + 'initiative_created_in_existing_issue', + 'initiative_revoked' ) ) ) + AND "ignored_member"."member_id" ISNULL + AND "ignored_initiative"."member_id" ISNULL; + +CREATE OR REPLACE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE) + RETURNS VOID + LANGUAGE 'plpgsql' VOLATILE AS $$ + BEGIN + UPDATE "member" SET + "last_login" = NULL, + "last_delegation_check" = NULL, + "login" = NULL, + "password" = NULL, + "locked" = TRUE, + "active" = FALSE, + "notify_email" = NULL, + "notify_email_unconfirmed" = NULL, + "notify_email_secret" = NULL, + "notify_email_secret_expiry" = NULL, + "notify_email_lock_expiry" = NULL, + "login_recovery_expiry" = NULL, + "password_reset_secret" = NULL, + "password_reset_secret_expiry" = NULL, + "organizational_unit" = NULL, + "internal_posts" = NULL, + "realname" = NULL, + "birthday" = NULL, + "address" = NULL, + "email" = NULL, + "xmpp_address" = NULL, + "website" = NULL, + "phone" = NULL, + "mobile_phone" = NULL, + "profession" = NULL, + "external_memberships" = NULL, + "external_posts" = NULL, + "statement" = NULL + WHERE "id" = "member_id_p"; + -- "text_search_data" is updated by triggers + DELETE FROM "setting" WHERE "member_id" = "member_id_p"; + DELETE FROM "setting_map" WHERE "member_id" = "member_id_p"; + DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p"; + DELETE FROM "member_image" WHERE "member_id" = "member_id_p"; + DELETE FROM "contact" WHERE "member_id" = "member_id_p"; + DELETE FROM "ignored_member" WHERE "member_id" = "member_id_p"; + DELETE FROM "session" WHERE "member_id" = "member_id_p"; + DELETE FROM "area_setting" WHERE "member_id" = "member_id_p"; + DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p"; + DELETE FROM "ignored_initiative" WHERE "member_id" = "member_id_p"; + DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p"; + DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p"; + DELETE FROM "membership" WHERE "member_id" = "member_id_p"; + DELETE FROM "delegation" WHERE "truster_id" = "member_id_p"; + DELETE FROM "non_voter" WHERE "member_id" = "member_id_p"; + DELETE FROM "direct_voter" USING "issue" + WHERE "direct_voter"."issue_id" = "issue"."id" + AND "issue"."closed" ISNULL + AND "member_id" = "member_id_p"; + RETURN; + END; + $$; + +CREATE OR REPLACE FUNCTION "delete_private_data"() + RETURNS VOID + LANGUAGE 'plpgsql' VOLATILE AS $$ + BEGIN + DELETE FROM "temporary_transaction_data"; + DELETE FROM "member" WHERE "activated" ISNULL; + UPDATE "member" SET + "invite_code" = NULL, + "invite_code_expiry" = NULL, + "admin_comment" = NULL, + "last_login" = NULL, + "last_delegation_check" = NULL, + "login" = NULL, + "password" = NULL, + "lang" = NULL, + "notify_email" = NULL, + "notify_email_unconfirmed" = NULL, + "notify_email_secret" = NULL, + "notify_email_secret_expiry" = NULL, + "notify_email_lock_expiry" = NULL, + "notify_level" = NULL, + "login_recovery_expiry" = NULL, + "password_reset_secret" = NULL, + "password_reset_secret_expiry" = NULL, + "organizational_unit" = NULL, + "internal_posts" = NULL, + "realname" = NULL, + "birthday" = NULL, + "address" = NULL, + "email" = NULL, + "xmpp_address" = NULL, + "website" = NULL, + "phone" = NULL, + "mobile_phone" = NULL, + "profession" = NULL, + "external_memberships" = NULL, + "external_posts" = NULL, + "formatting_engine" = NULL, + "statement" = NULL; + -- "text_search_data" is updated by triggers + DELETE FROM "setting"; + DELETE FROM "setting_map"; + DELETE FROM "member_relation_setting"; + DELETE FROM "member_image"; + DELETE FROM "contact"; + DELETE FROM "ignored_member"; + DELETE FROM "session"; + DELETE FROM "area_setting"; + DELETE FROM "issue_setting"; + DELETE FROM "ignored_initiative"; + DELETE FROM "initiative_setting"; + DELETE FROM "suggestion_setting"; + DELETE FROM "non_voter"; + DELETE FROM "direct_voter" USING "issue" + WHERE "direct_voter"."issue_id" = "issue"."id" + AND "issue"."closed" ISNULL; + RETURN; + END; + $$; + +COMMIT;