liquid_feedback_core
changeset 387:ae69cf82c05f v2.2.4
Added support for delegation checks and login recovery; Removed unnecessary join in event views
author | jbe |
---|---|
date | Sat Apr 20 18:22:37 2013 +0200 (2013-04-20) |
parents | 0b0bfcf2e60c |
children | d990f212ee55 |
files | core.sql update/core-update.v2.2.3-v2.2.4.sql |
line diff
1.1 --- a/core.sql Sat Mar 23 13:11:08 2013 +0100 1.2 +++ b/core.sql Sat Apr 20 18:22:37 2013 +0200 1.3 @@ -7,7 +7,7 @@ 1.4 BEGIN; 1.5 1.6 CREATE VIEW "liquid_feedback_version" AS 1.7 - SELECT * FROM (VALUES ('2.2.3', 2, 2, 3)) 1.8 + SELECT * FROM (VALUES ('2.2.4', 2, 2, 4)) 1.9 AS "subquery"("string", "major", "minor", "revision"); 1.10 1.11 1.12 @@ -104,6 +104,7 @@ 1.13 "activated" TIMESTAMPTZ, 1.14 "last_activity" DATE, 1.15 "last_login" TIMESTAMPTZ, 1.16 + "last_delegation_check" TIMESTAMPTZ, 1.17 "login" TEXT UNIQUE, 1.18 "password" TEXT, 1.19 "locked" BOOLEAN NOT NULL DEFAULT FALSE, 1.20 @@ -116,6 +117,7 @@ 1.21 "notify_email_secret_expiry" TIMESTAMPTZ, 1.22 "notify_email_lock_expiry" TIMESTAMPTZ, 1.23 "notify_level" "notify_level", 1.24 + "login_recovery_expiry" TIMESTAMPTZ, 1.25 "password_reset_secret" TEXT UNIQUE, 1.26 "password_reset_secret_expiry" TIMESTAMPTZ, 1.27 "name" TEXT UNIQUE, 1.28 @@ -159,6 +161,7 @@ 1.29 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'; 1.30 COMMENT ON COLUMN "member"."last_activity" IS 'Date of last activity of member; required to be set for "active" members'; 1.31 COMMENT ON COLUMN "member"."last_login" IS 'Timestamp of last login'; 1.32 +COMMENT ON COLUMN "member"."last_delegation_check" IS 'Timestamp of last delegation check (i.e. confirmation of all unit and area delegations)'; 1.33 COMMENT ON COLUMN "member"."login" IS 'Login name'; 1.34 COMMENT ON COLUMN "member"."password" IS 'Password (preferably as crypto-hash, depending on the frontend or access layer)'; 1.35 COMMENT ON COLUMN "member"."locked" IS 'Locked members can not log in.'; 1.36 @@ -171,6 +174,9 @@ 1.37 COMMENT ON COLUMN "member"."notify_email_secret_expiry" IS 'Expiry date/time for "notify_email_secret"'; 1.38 COMMENT ON COLUMN "member"."notify_email_lock_expiry" IS 'Date/time until no further email confirmation mails may be sent (abuse protection)'; 1.39 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'; 1.40 +COMMENT ON COLUMN "member"."login_recovery_expiry" IS 'Date/time after which another login recovery attempt is allowed'; 1.41 +COMMENT ON COLUMN "member"."password_reset_secret" IS 'Secret string sent via e-mail for password recovery'; 1.42 +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'; 1.43 COMMENT ON COLUMN "member"."name" IS 'Distinct name of the member, may be NULL if account has not been activated yet'; 1.44 COMMENT ON COLUMN "member"."identification" IS 'Optional identification number or code of the member'; 1.45 COMMENT ON COLUMN "member"."authentication" IS 'Information about how this member was authenticated'; 1.46 @@ -329,6 +335,7 @@ 1.47 "additional_secret" TEXT, 1.48 "expiry" TIMESTAMPTZ NOT NULL DEFAULT now() + '24 hours', 1.49 "member_id" INT8 REFERENCES "member" ("id") ON DELETE SET NULL, 1.50 + "needs_delegation_check" BOOLEAN NOT NULL DEFAULT FALSE, 1.51 "lang" TEXT ); 1.52 CREATE INDEX "session_expiry_idx" ON "session" ("expiry"); 1.53 1.54 @@ -337,6 +344,7 @@ 1.55 COMMENT ON COLUMN "session"."ident" IS 'Secret session identifier (i.e. random string)'; 1.56 COMMENT ON COLUMN "session"."additional_secret" IS 'Additional field to store a secret, which can be used against CSRF attacks'; 1.57 COMMENT ON COLUMN "session"."member_id" IS 'Reference to member, who is logged in'; 1.58 +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'; 1.59 COMMENT ON COLUMN "session"."lang" IS 'Language code of the selected language'; 1.60 1.61 1.62 @@ -2245,9 +2253,6 @@ 1.63 LEFT JOIN "interest" 1.64 ON "member"."id" = "interest"."member_id" 1.65 AND "event"."issue_id" = "interest"."issue_id" 1.66 - LEFT JOIN "supporter" 1.67 - ON "member"."id" = "supporter"."member_id" 1.68 - AND "event"."initiative_id" = "supporter"."initiative_id" 1.69 LEFT JOIN "ignored_member" 1.70 ON "member"."id" = "ignored_member"."member_id" 1.71 AND "event"."member_id" = "ignored_member"."other_member_id" 1.72 @@ -2255,7 +2260,6 @@ 1.73 ON "member"."id" = "ignored_initiative"."member_id" 1.74 AND "event"."initiative_id" = "ignored_initiative"."initiative_id" 1.75 WHERE ( 1.76 - "supporter"."member_id" NOTNULL OR 1.77 "interest"."member_id" NOTNULL OR 1.78 ( "membership"."member_id" NOTNULL AND 1.79 "event"."event" IN ( 1.80 @@ -2306,9 +2310,6 @@ 1.81 LEFT JOIN "interest" 1.82 ON "member"."id" = "interest"."member_id" 1.83 AND "event"."issue_id" = "interest"."issue_id" 1.84 - LEFT JOIN "supporter" 1.85 - ON "member"."id" = "supporter"."member_id" 1.86 - AND "event"."initiative_id" = "supporter"."initiative_id" 1.87 LEFT JOIN "ignored_member" 1.88 ON "member"."id" = "ignored_member"."member_id" 1.89 AND "event"."member_id" = "ignored_member"."other_member_id" 1.90 @@ -2332,7 +2333,6 @@ 1.91 'discussion', 1.92 'canceled_after_revocation_during_discussion' ) ) ) 1.93 AND ( 1.94 - "supporter"."member_id" NOTNULL OR 1.95 "interest"."member_id" NOTNULL OR 1.96 ( "membership"."member_id" NOTNULL AND 1.97 "event"."event" IN ( 1.98 @@ -4391,6 +4391,7 @@ 1.99 BEGIN 1.100 UPDATE "member" SET 1.101 "last_login" = NULL, 1.102 + "last_delegation_check" = NULL, 1.103 "login" = NULL, 1.104 "password" = NULL, 1.105 "locked" = TRUE, 1.106 @@ -4400,6 +4401,7 @@ 1.107 "notify_email_secret" = NULL, 1.108 "notify_email_secret_expiry" = NULL, 1.109 "notify_email_lock_expiry" = NULL, 1.110 + "login_recovery_expiry" = NULL, 1.111 "password_reset_secret" = NULL, 1.112 "password_reset_secret_expiry" = NULL, 1.113 "organizational_unit" = NULL, 1.114 @@ -4455,6 +4457,7 @@ 1.115 "invite_code_expiry" = NULL, 1.116 "admin_comment" = NULL, 1.117 "last_login" = NULL, 1.118 + "last_delegation_check" = NULL, 1.119 "login" = NULL, 1.120 "password" = NULL, 1.121 "lang" = NULL, 1.122 @@ -4464,6 +4467,7 @@ 1.123 "notify_email_secret_expiry" = NULL, 1.124 "notify_email_lock_expiry" = NULL, 1.125 "notify_level" = NULL, 1.126 + "login_recovery_expiry" = NULL, 1.127 "password_reset_secret" = NULL, 1.128 "password_reset_secret_expiry" = NULL, 1.129 "organizational_unit" = NULL,
2.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000 2.2 +++ b/update/core-update.v2.2.3-v2.2.4.sql Sat Apr 20 18:22:37 2013 +0200 2.3 @@ -0,0 +1,261 @@ 2.4 +BEGIN; 2.5 + 2.6 +CREATE OR REPLACE VIEW "liquid_feedback_version" AS 2.7 + SELECT * FROM (VALUES ('2.2.4', 2, 2, 4)) 2.8 + AS "subquery"("string", "major", "minor", "revision"); 2.9 + 2.10 + 2.11 +ALTER TABLE "member" ADD COLUMN "last_delegation_check" TIMESTAMPTZ; 2.12 +ALTER TABLE "member" ADD COLUMN "login_recovery_expiry" TIMESTAMPTZ; 2.13 + 2.14 +COMMENT ON COLUMN "member"."last_delegation_check" IS 'Timestamp of last delegation check (i.e. confirmation of all unit and area delegations)'; 2.15 +COMMENT ON COLUMN "member"."login_recovery_expiry" IS 'Date/time after which another login recovery attempt is allowed'; 2.16 +COMMENT ON COLUMN "member"."password_reset_secret" IS 'Secret string sent via e-mail for password recovery'; 2.17 +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'; 2.18 + 2.19 +ALTER TABLE "session" ADD COLUMN "needs_delegation_check" BOOLEAN NOT NULL DEFAULT FALSE; 2.20 +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'; 2.21 + 2.22 +CREATE OR REPLACE VIEW "event_seen_by_member" AS 2.23 + SELECT 2.24 + "member"."id" AS "seen_by_member_id", 2.25 + CASE WHEN "event"."state" IN ( 2.26 + 'voting', 2.27 + 'finished_without_winner', 2.28 + 'finished_with_winner' 2.29 + ) THEN 2.30 + 'voting'::"notify_level" 2.31 + ELSE 2.32 + CASE WHEN "event"."state" IN ( 2.33 + 'verification', 2.34 + 'canceled_after_revocation_during_verification', 2.35 + 'canceled_no_initiative_admitted' 2.36 + ) THEN 2.37 + 'verification'::"notify_level" 2.38 + ELSE 2.39 + CASE WHEN "event"."state" IN ( 2.40 + 'discussion', 2.41 + 'canceled_after_revocation_during_discussion' 2.42 + ) THEN 2.43 + 'discussion'::"notify_level" 2.44 + ELSE 2.45 + 'all'::"notify_level" 2.46 + END 2.47 + END 2.48 + END AS "notify_level", 2.49 + "event".* 2.50 + FROM "member" CROSS JOIN "event" 2.51 + LEFT JOIN "issue" 2.52 + ON "event"."issue_id" = "issue"."id" 2.53 + LEFT JOIN "membership" 2.54 + ON "member"."id" = "membership"."member_id" 2.55 + AND "issue"."area_id" = "membership"."area_id" 2.56 + LEFT JOIN "interest" 2.57 + ON "member"."id" = "interest"."member_id" 2.58 + AND "event"."issue_id" = "interest"."issue_id" 2.59 + LEFT JOIN "ignored_member" 2.60 + ON "member"."id" = "ignored_member"."member_id" 2.61 + AND "event"."member_id" = "ignored_member"."other_member_id" 2.62 + LEFT JOIN "ignored_initiative" 2.63 + ON "member"."id" = "ignored_initiative"."member_id" 2.64 + AND "event"."initiative_id" = "ignored_initiative"."initiative_id" 2.65 + WHERE ( 2.66 + "interest"."member_id" NOTNULL OR 2.67 + ( "membership"."member_id" NOTNULL AND 2.68 + "event"."event" IN ( 2.69 + 'issue_state_changed', 2.70 + 'initiative_created_in_new_issue', 2.71 + 'initiative_created_in_existing_issue', 2.72 + 'initiative_revoked' ) ) ) 2.73 + AND "ignored_member"."member_id" ISNULL 2.74 + AND "ignored_initiative"."member_id" ISNULL; 2.75 + 2.76 +CREATE OR REPLACE VIEW "selected_event_seen_by_member" AS 2.77 + SELECT 2.78 + "member"."id" AS "seen_by_member_id", 2.79 + CASE WHEN "event"."state" IN ( 2.80 + 'voting', 2.81 + 'finished_without_winner', 2.82 + 'finished_with_winner' 2.83 + ) THEN 2.84 + 'voting'::"notify_level" 2.85 + ELSE 2.86 + CASE WHEN "event"."state" IN ( 2.87 + 'verification', 2.88 + 'canceled_after_revocation_during_verification', 2.89 + 'canceled_no_initiative_admitted' 2.90 + ) THEN 2.91 + 'verification'::"notify_level" 2.92 + ELSE 2.93 + CASE WHEN "event"."state" IN ( 2.94 + 'discussion', 2.95 + 'canceled_after_revocation_during_discussion' 2.96 + ) THEN 2.97 + 'discussion'::"notify_level" 2.98 + ELSE 2.99 + 'all'::"notify_level" 2.100 + END 2.101 + END 2.102 + END AS "notify_level", 2.103 + "event".* 2.104 + FROM "member" CROSS JOIN "event" 2.105 + LEFT JOIN "issue" 2.106 + ON "event"."issue_id" = "issue"."id" 2.107 + LEFT JOIN "membership" 2.108 + ON "member"."id" = "membership"."member_id" 2.109 + AND "issue"."area_id" = "membership"."area_id" 2.110 + LEFT JOIN "interest" 2.111 + ON "member"."id" = "interest"."member_id" 2.112 + AND "event"."issue_id" = "interest"."issue_id" 2.113 + LEFT JOIN "ignored_member" 2.114 + ON "member"."id" = "ignored_member"."member_id" 2.115 + AND "event"."member_id" = "ignored_member"."other_member_id" 2.116 + LEFT JOIN "ignored_initiative" 2.117 + ON "member"."id" = "ignored_initiative"."member_id" 2.118 + AND "event"."initiative_id" = "ignored_initiative"."initiative_id" 2.119 + WHERE ( 2.120 + ( "member"."notify_level" >= 'all' ) OR 2.121 + ( "member"."notify_level" >= 'voting' AND 2.122 + "event"."state" IN ( 2.123 + 'voting', 2.124 + 'finished_without_winner', 2.125 + 'finished_with_winner' ) ) OR 2.126 + ( "member"."notify_level" >= 'verification' AND 2.127 + "event"."state" IN ( 2.128 + 'verification', 2.129 + 'canceled_after_revocation_during_verification', 2.130 + 'canceled_no_initiative_admitted' ) ) OR 2.131 + ( "member"."notify_level" >= 'discussion' AND 2.132 + "event"."state" IN ( 2.133 + 'discussion', 2.134 + 'canceled_after_revocation_during_discussion' ) ) ) 2.135 + AND ( 2.136 + "interest"."member_id" NOTNULL OR 2.137 + ( "membership"."member_id" NOTNULL AND 2.138 + "event"."event" IN ( 2.139 + 'issue_state_changed', 2.140 + 'initiative_created_in_new_issue', 2.141 + 'initiative_created_in_existing_issue', 2.142 + 'initiative_revoked' ) ) ) 2.143 + AND "ignored_member"."member_id" ISNULL 2.144 + AND "ignored_initiative"."member_id" ISNULL; 2.145 + 2.146 +CREATE OR REPLACE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE) 2.147 + RETURNS VOID 2.148 + LANGUAGE 'plpgsql' VOLATILE AS $$ 2.149 + BEGIN 2.150 + UPDATE "member" SET 2.151 + "last_login" = NULL, 2.152 + "last_delegation_check" = NULL, 2.153 + "login" = NULL, 2.154 + "password" = NULL, 2.155 + "locked" = TRUE, 2.156 + "active" = FALSE, 2.157 + "notify_email" = NULL, 2.158 + "notify_email_unconfirmed" = NULL, 2.159 + "notify_email_secret" = NULL, 2.160 + "notify_email_secret_expiry" = NULL, 2.161 + "notify_email_lock_expiry" = NULL, 2.162 + "login_recovery_expiry" = NULL, 2.163 + "password_reset_secret" = NULL, 2.164 + "password_reset_secret_expiry" = NULL, 2.165 + "organizational_unit" = NULL, 2.166 + "internal_posts" = NULL, 2.167 + "realname" = NULL, 2.168 + "birthday" = NULL, 2.169 + "address" = NULL, 2.170 + "email" = NULL, 2.171 + "xmpp_address" = NULL, 2.172 + "website" = NULL, 2.173 + "phone" = NULL, 2.174 + "mobile_phone" = NULL, 2.175 + "profession" = NULL, 2.176 + "external_memberships" = NULL, 2.177 + "external_posts" = NULL, 2.178 + "statement" = NULL 2.179 + WHERE "id" = "member_id_p"; 2.180 + -- "text_search_data" is updated by triggers 2.181 + DELETE FROM "setting" WHERE "member_id" = "member_id_p"; 2.182 + DELETE FROM "setting_map" WHERE "member_id" = "member_id_p"; 2.183 + DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p"; 2.184 + DELETE FROM "member_image" WHERE "member_id" = "member_id_p"; 2.185 + DELETE FROM "contact" WHERE "member_id" = "member_id_p"; 2.186 + DELETE FROM "ignored_member" WHERE "member_id" = "member_id_p"; 2.187 + DELETE FROM "session" WHERE "member_id" = "member_id_p"; 2.188 + DELETE FROM "area_setting" WHERE "member_id" = "member_id_p"; 2.189 + DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p"; 2.190 + DELETE FROM "ignored_initiative" WHERE "member_id" = "member_id_p"; 2.191 + DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p"; 2.192 + DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p"; 2.193 + DELETE FROM "membership" WHERE "member_id" = "member_id_p"; 2.194 + DELETE FROM "delegation" WHERE "truster_id" = "member_id_p"; 2.195 + DELETE FROM "non_voter" WHERE "member_id" = "member_id_p"; 2.196 + DELETE FROM "direct_voter" USING "issue" 2.197 + WHERE "direct_voter"."issue_id" = "issue"."id" 2.198 + AND "issue"."closed" ISNULL 2.199 + AND "member_id" = "member_id_p"; 2.200 + RETURN; 2.201 + END; 2.202 + $$; 2.203 + 2.204 +CREATE OR REPLACE FUNCTION "delete_private_data"() 2.205 + RETURNS VOID 2.206 + LANGUAGE 'plpgsql' VOLATILE AS $$ 2.207 + BEGIN 2.208 + DELETE FROM "temporary_transaction_data"; 2.209 + DELETE FROM "member" WHERE "activated" ISNULL; 2.210 + UPDATE "member" SET 2.211 + "invite_code" = NULL, 2.212 + "invite_code_expiry" = NULL, 2.213 + "admin_comment" = NULL, 2.214 + "last_login" = NULL, 2.215 + "last_delegation_check" = NULL, 2.216 + "login" = NULL, 2.217 + "password" = NULL, 2.218 + "lang" = NULL, 2.219 + "notify_email" = NULL, 2.220 + "notify_email_unconfirmed" = NULL, 2.221 + "notify_email_secret" = NULL, 2.222 + "notify_email_secret_expiry" = NULL, 2.223 + "notify_email_lock_expiry" = NULL, 2.224 + "notify_level" = NULL, 2.225 + "login_recovery_expiry" = NULL, 2.226 + "password_reset_secret" = NULL, 2.227 + "password_reset_secret_expiry" = NULL, 2.228 + "organizational_unit" = NULL, 2.229 + "internal_posts" = NULL, 2.230 + "realname" = NULL, 2.231 + "birthday" = NULL, 2.232 + "address" = NULL, 2.233 + "email" = NULL, 2.234 + "xmpp_address" = NULL, 2.235 + "website" = NULL, 2.236 + "phone" = NULL, 2.237 + "mobile_phone" = NULL, 2.238 + "profession" = NULL, 2.239 + "external_memberships" = NULL, 2.240 + "external_posts" = NULL, 2.241 + "formatting_engine" = NULL, 2.242 + "statement" = NULL; 2.243 + -- "text_search_data" is updated by triggers 2.244 + DELETE FROM "setting"; 2.245 + DELETE FROM "setting_map"; 2.246 + DELETE FROM "member_relation_setting"; 2.247 + DELETE FROM "member_image"; 2.248 + DELETE FROM "contact"; 2.249 + DELETE FROM "ignored_member"; 2.250 + DELETE FROM "session"; 2.251 + DELETE FROM "area_setting"; 2.252 + DELETE FROM "issue_setting"; 2.253 + DELETE FROM "ignored_initiative"; 2.254 + DELETE FROM "initiative_setting"; 2.255 + DELETE FROM "suggestion_setting"; 2.256 + DELETE FROM "non_voter"; 2.257 + DELETE FROM "direct_voter" USING "issue" 2.258 + WHERE "direct_voter"."issue_id" = "issue"."id" 2.259 + AND "issue"."closed" ISNULL; 2.260 + RETURN; 2.261 + END; 2.262 + $$; 2.263 + 2.264 +COMMIT;