liquid_feedback_core
diff core.sql @ 387:ae69cf82c05f
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 | e474e9e1240a |
children | 582d270d2653 |
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,