liquid_feedback_core
view update/core-update.v1.3.0-v1.3.1.sql @ 575:8771e783d898
Split-up "discovery_baseurl" into "base_url" and "manifest_url"
author | jbe |
---|---|
date | Mon Oct 23 14:35:35 2017 +0200 (2017-10-23) |
parents | 53ae1d37e5de |
children |
line source
1 BEGIN;
3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
4 SELECT * FROM (VALUES ('1.3.1', 1, 3, 1))
5 AS "subquery"("string", "major", "minor", "revision");
7 CREATE TABLE "system_setting" (
8 "member_ttl" INTERVAL );
9 CREATE UNIQUE INDEX "system_setting_singleton_idx" ON "system_setting" ((1));
11 COMMENT ON TABLE "system_setting" IS 'This table contains only one row with different settings in each column.';
12 COMMENT ON INDEX "system_setting_singleton_idx" IS 'This index ensures that "system_setting" only contains one row maximum.';
13 COMMENT ON COLUMN "system_setting"."member_ttl" IS 'Time after members get their "active" flag set to FALSE, if they do not login anymore.';
15 ALTER TABLE "member" ADD COLUMN "last_login_public" DATE;
16 ALTER TABLE "member" ADD COLUMN "locked" BOOLEAN NOT NULL DEFAULT FALSE;
18 UPDATE "member" SET "locked" = TRUE WHERE "active" = FALSE;
20 COMMENT ON COLUMN "member"."last_login" IS 'Timestamp of last login';
21 COMMENT ON COLUMN "member"."last_login_public" IS 'Date of last login (time stripped for privacy reasons, updated only after day change)';
22 COMMENT ON COLUMN "member"."locked" IS 'Locked members can not log in.';
23 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.';
25 CREATE FUNCTION "check_last_login"()
26 RETURNS VOID
27 LANGUAGE 'plpgsql' VOLATILE AS $$
28 DECLARE
29 "system_setting_row" "system_setting"%ROWTYPE;
30 BEGIN
31 SELECT * INTO "system_setting_row" FROM "system_setting";
32 LOCK TABLE "member" IN SHARE ROW EXCLUSIVE MODE;
33 UPDATE "member" SET "last_login_public" = "last_login"::date
34 FROM (
35 SELECT DISTINCT "member"."id"
36 FROM "member" LEFT JOIN "member_history"
37 ON "member"."id" = "member_history"."member_id"
38 WHERE "member"."last_login"::date < 'today' OR (
39 "member_history"."until"::date >= 'today' AND
40 "member_history"."active" = FALSE AND "member"."active" = TRUE
41 )
42 ) AS "subquery"
43 WHERE "member"."id" = "subquery"."id";
44 IF "system_setting_row"."member_ttl" NOTNULL THEN
45 UPDATE "member" SET "active" = FALSE
46 WHERE "active" = TRUE
47 AND "last_login"::date < 'today'
48 AND "last_login_public" <
49 (now() - "system_setting_row"."member_ttl")::date;
50 END IF;
51 RETURN;
52 END;
53 $$;
55 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).';
57 CREATE OR REPLACE FUNCTION "create_interest_snapshot"
58 ( "issue_id_p" "issue"."id"%TYPE )
59 RETURNS VOID
60 LANGUAGE 'plpgsql' VOLATILE AS $$
61 DECLARE
62 "member_id_v" "member"."id"%TYPE;
63 BEGIN
64 DELETE FROM "direct_interest_snapshot"
65 WHERE "issue_id" = "issue_id_p"
66 AND "event" = 'periodic';
67 DELETE FROM "delegating_interest_snapshot"
68 WHERE "issue_id" = "issue_id_p"
69 AND "event" = 'periodic';
70 DELETE FROM "direct_supporter_snapshot"
71 WHERE "issue_id" = "issue_id_p"
72 AND "event" = 'periodic';
73 INSERT INTO "direct_interest_snapshot"
74 ("issue_id", "event", "member_id", "voting_requested")
75 SELECT
76 "issue_id_p" AS "issue_id",
77 'periodic' AS "event",
78 "member"."id" AS "member_id",
79 "interest"."voting_requested"
80 FROM "interest" JOIN "member"
81 ON "interest"."member_id" = "member"."id"
82 WHERE "interest"."issue_id" = "issue_id_p"
83 AND "member"."active";
84 FOR "member_id_v" IN
85 SELECT "member_id" FROM "direct_interest_snapshot"
86 WHERE "issue_id" = "issue_id_p"
87 AND "event" = 'periodic'
88 LOOP
89 UPDATE "direct_interest_snapshot" SET
90 "weight" = 1 +
91 "weight_of_added_delegations_for_interest_snapshot"(
92 "issue_id_p",
93 "member_id_v",
94 '{}'
95 )
96 WHERE "issue_id" = "issue_id_p"
97 AND "event" = 'periodic'
98 AND "member_id" = "member_id_v";
99 END LOOP;
100 INSERT INTO "direct_supporter_snapshot"
101 ( "issue_id", "initiative_id", "event", "member_id",
102 "informed", "satisfied" )
103 SELECT
104 "issue_id_p" AS "issue_id",
105 "initiative"."id" AS "initiative_id",
106 'periodic' AS "event",
107 "supporter"."member_id" AS "member_id",
108 "supporter"."draft_id" = "current_draft"."id" AS "informed",
109 NOT EXISTS (
110 SELECT NULL FROM "critical_opinion"
111 WHERE "initiative_id" = "initiative"."id"
112 AND "member_id" = "supporter"."member_id"
113 ) AS "satisfied"
114 FROM "initiative"
115 JOIN "supporter"
116 ON "supporter"."initiative_id" = "initiative"."id"
117 JOIN "current_draft"
118 ON "initiative"."id" = "current_draft"."initiative_id"
119 JOIN "direct_interest_snapshot"
120 ON "supporter"."member_id" = "direct_interest_snapshot"."member_id"
121 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
122 AND "event" = 'periodic'
123 WHERE "initiative"."issue_id" = "issue_id_p";
124 RETURN;
125 END;
126 $$;
128 CREATE OR REPLACE FUNCTION "check_everything"()
129 RETURNS VOID
130 LANGUAGE 'plpgsql' VOLATILE AS $$
131 DECLARE
132 "issue_id_v" "issue"."id"%TYPE;
133 BEGIN
134 DELETE FROM "expired_session";
135 PERFORM "check_last_login"();
136 PERFORM "calculate_member_counts"();
137 FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP
138 PERFORM "check_issue"("issue_id_v");
139 END LOOP;
140 FOR "issue_id_v" IN SELECT "id" FROM "issue_with_ranks_missing" LOOP
141 PERFORM "calculate_ranks"("issue_id_v");
142 END LOOP;
143 RETURN;
144 END;
145 $$;
147 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.';
149 CREATE OR REPLACE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)
150 RETURNS VOID
151 LANGUAGE 'plpgsql' VOLATILE AS $$
152 BEGIN
153 UPDATE "member" SET
154 "last_login" = NULL,
155 "last_login_public" = NULL,
156 "login" = NULL,
157 "password" = NULL,
158 "locked" = TRUE,
159 "active" = FALSE,
160 "notify_email" = NULL,
161 "notify_email_unconfirmed" = NULL,
162 "notify_email_secret" = NULL,
163 "notify_email_secret_expiry" = NULL,
164 "notify_email_lock_expiry" = NULL,
165 "password_reset_secret" = NULL,
166 "password_reset_secret_expiry" = NULL,
167 "organizational_unit" = NULL,
168 "internal_posts" = NULL,
169 "realname" = NULL,
170 "birthday" = NULL,
171 "address" = NULL,
172 "email" = NULL,
173 "xmpp_address" = NULL,
174 "website" = NULL,
175 "phone" = NULL,
176 "mobile_phone" = NULL,
177 "profession" = NULL,
178 "external_memberships" = NULL,
179 "external_posts" = NULL,
180 "statement" = NULL
181 WHERE "id" = "member_id_p";
182 -- "text_search_data" is updated by triggers
183 DELETE FROM "setting" WHERE "member_id" = "member_id_p";
184 DELETE FROM "setting_map" WHERE "member_id" = "member_id_p";
185 DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p";
186 DELETE FROM "member_image" WHERE "member_id" = "member_id_p";
187 DELETE FROM "contact" WHERE "member_id" = "member_id_p";
188 DELETE FROM "area_setting" WHERE "member_id" = "member_id_p";
189 DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p";
190 DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p";
191 DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p";
192 DELETE FROM "membership" WHERE "member_id" = "member_id_p";
193 DELETE FROM "ignored_issue" WHERE "member_id" = "member_id_p";
194 DELETE FROM "delegation" WHERE "truster_id" = "member_id_p";
195 DELETE FROM "direct_voter" USING "issue"
196 WHERE "direct_voter"."issue_id" = "issue"."id"
197 AND "issue"."closed" ISNULL
198 AND "member_id" = "member_id_p";
199 RETURN;
200 END;
201 $$;
203 COMMIT;