liquid_feedback_core
view update/core-update.v2.0.3-v2.0.4.sql @ 295:69d6fba0f84c
Use EXCLUSIVE MODE table locks in function "lock_issue"("issue"."id")
Avoids deadlocks caused by explicit FOR UPDATE row locks when updating member statements and implicit FOR SHARE row locks when writing snapshots.
Avoids deadlocks caused by explicit FOR UPDATE row locks when updating member statements and implicit FOR SHARE row locks when writing snapshots.
| author | jbe |
|---|---|
| date | Thu Sep 13 17:02:22 2012 +0200 (2012-09-13) |
| parents | 10a231cfd585 |
| children |
line source
1 BEGIN;
3 -- update version number:
4 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
5 SELECT * FROM (VALUES ('2.0.4', 2, 0, 4))
6 AS "subquery"("string", "major", "minor", "revision");
8 -- drop NOT NULL constraints on columns "name" and "notify_level"
9 -- in table "member", and add new constraint for "name":
10 ALTER TABLE "member" ALTER COLUMN "notify_level" DROP NOT NULL;
11 ALTER TABLE "member" ALTER COLUMN "name" DROP NOT NULL;
12 ALTER TABLE "member" ADD CONSTRAINT "name_not_null_if_activated" CHECK ("activated" ISNULL OR "name" NOTNULL);
13 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';
14 COMMENT ON COLUMN "member"."name" IS 'Distinct name of the member, may be NULL if account has not been activated yet';
16 -- add table "session":
17 CREATE TABLE "session" (
18 "ident" TEXT PRIMARY KEY,
19 "additional_secret" TEXT,
20 "expiry" TIMESTAMPTZ NOT NULL DEFAULT now() + '24 hours',
21 "member_id" INT8 REFERENCES "member" ("id") ON DELETE SET NULL,
22 "lang" TEXT );
23 CREATE INDEX "session_expiry_idx" ON "session" ("expiry");
24 COMMENT ON TABLE "session" IS 'Sessions, i.e. for a web-frontend or API layer';
25 COMMENT ON COLUMN "session"."ident" IS 'Secret session identifier (i.e. random string)';
26 COMMENT ON COLUMN "session"."additional_secret" IS 'Additional field to store a secret, which can be used against CSRF attacks';
27 COMMENT ON COLUMN "session"."member_id" IS 'Reference to member, who is logged in';
28 COMMENT ON COLUMN "session"."lang" IS 'Language code of the selected language';
30 -- add column "lang" to table "member":
31 ALTER TABLE "member" ADD COLUMN "lang" TEXT;
32 COMMENT ON COLUMN "member"."lang" IS 'Language code of the preferred language of the member';
34 -- drop view "pending_notification":
35 DROP VIEW "pending_notification";
37 -- remove column "notify_event_id" of table "member":
38 ALTER TABLE "member" DROP COLUMN "notify_event_id";
40 -- add table "notification_sent":
41 CREATE TABLE "notification_sent" (
42 "event_id" INT8 NOT NULL );
43 CREATE UNIQUE INDEX "notification_sent_singleton_idx" ON "notification_sent" ((1));
44 COMMENT ON TABLE "notification_sent" IS 'This table stores one row with the last event_id, for which notifications have been sent out';
45 COMMENT ON INDEX "notification_sent_singleton_idx" IS 'This index ensures that "notification_sent" only contains one row maximum.';
47 -- add view "selected_event_seen_by_member":
48 CREATE VIEW "selected_event_seen_by_member" AS
49 SELECT
50 "member"."id" AS "seen_by_member_id",
51 CASE WHEN "event"."state" IN (
52 'voting',
53 'finished_without_winner',
54 'finished_with_winner'
55 ) THEN
56 'voting'::"notify_level"
57 ELSE
58 CASE WHEN "event"."state" IN (
59 'verification',
60 'canceled_after_revocation_during_verification',
61 'canceled_no_initiative_admitted'
62 ) THEN
63 'verification'::"notify_level"
64 ELSE
65 CASE WHEN "event"."state" IN (
66 'discussion',
67 'canceled_after_revocation_during_discussion'
68 ) THEN
69 'discussion'::"notify_level"
70 ELSE
71 'all'::"notify_level"
72 END
73 END
74 END AS "notify_level",
75 "event".*
76 FROM "member" CROSS JOIN "event"
77 LEFT JOIN "issue"
78 ON "event"."issue_id" = "issue"."id"
79 LEFT JOIN "membership"
80 ON "member"."id" = "membership"."member_id"
81 AND "issue"."area_id" = "membership"."area_id"
82 LEFT JOIN "interest"
83 ON "member"."id" = "interest"."member_id"
84 AND "event"."issue_id" = "interest"."issue_id"
85 LEFT JOIN "supporter"
86 ON "member"."id" = "supporter"."member_id"
87 AND "event"."initiative_id" = "supporter"."initiative_id"
88 LEFT JOIN "ignored_member"
89 ON "member"."id" = "ignored_member"."member_id"
90 AND "event"."member_id" = "ignored_member"."other_member_id"
91 LEFT JOIN "ignored_initiative"
92 ON "member"."id" = "ignored_initiative"."member_id"
93 AND "event"."initiative_id" = "ignored_initiative"."initiative_id"
94 WHERE (
95 ( "member"."notify_level" >= 'all' ) OR
96 ( "member"."notify_level" >= 'voting' AND
97 "event"."state" IN (
98 'voting',
99 'finished_without_winner',
100 'finished_with_winner' ) ) OR
101 ( "member"."notify_level" >= 'verification' AND
102 "event"."state" IN (
103 'verification',
104 'canceled_after_revocation_during_verification',
105 'canceled_no_initiative_admitted' ) ) OR
106 ( "member"."notify_level" >= 'discussion' AND
107 "event"."state" IN (
108 'discussion',
109 'canceled_after_revocation_during_discussion' ) ) )
110 AND (
111 "supporter"."member_id" NOTNULL OR
112 "interest"."member_id" NOTNULL OR
113 ( "membership"."member_id" NOTNULL AND
114 "event"."event" IN (
115 'issue_state_changed',
116 'initiative_created_in_new_issue',
117 'initiative_created_in_existing_issue',
118 'initiative_revoked' ) ) )
119 AND "ignored_member"."member_id" ISNULL
120 AND "ignored_initiative"."member_id" ISNULL;
121 COMMENT ON VIEW "selected_event_seen_by_member" IS 'Events as seen by a member, depending on its memberships, interests, support and members "notify_level"';
123 -- delete non-activated members in function "delete_private_data":
124 CREATE OR REPLACE FUNCTION "delete_private_data"()
125 RETURNS VOID
126 LANGUAGE 'plpgsql' VOLATILE AS $$
127 BEGIN
128 DELETE FROM "member" WHERE "activated" ISNULL;
129 UPDATE "member" SET
130 "invite_code" = NULL,
131 "last_login" = NULL,
132 "login" = NULL,
133 "password" = NULL,
134 "notify_email" = NULL,
135 "notify_email_unconfirmed" = NULL,
136 "notify_email_secret" = NULL,
137 "notify_email_secret_expiry" = NULL,
138 "notify_email_lock_expiry" = NULL,
139 "password_reset_secret" = NULL,
140 "password_reset_secret_expiry" = NULL,
141 "organizational_unit" = NULL,
142 "internal_posts" = NULL,
143 "realname" = NULL,
144 "birthday" = NULL,
145 "address" = NULL,
146 "email" = NULL,
147 "xmpp_address" = NULL,
148 "website" = NULL,
149 "phone" = NULL,
150 "mobile_phone" = NULL,
151 "profession" = NULL,
152 "external_memberships" = NULL,
153 "external_posts" = NULL,
154 "statement" = NULL;
155 -- "text_search_data" is updated by triggers
156 DELETE FROM "setting";
157 DELETE FROM "setting_map";
158 DELETE FROM "member_relation_setting";
159 DELETE FROM "member_image";
160 DELETE FROM "contact";
161 DELETE FROM "ignored_member";
162 DELETE FROM "area_setting";
163 DELETE FROM "issue_setting";
164 DELETE FROM "ignored_initiative";
165 DELETE FROM "initiative_setting";
166 DELETE FROM "suggestion_setting";
167 DELETE FROM "non_voter";
168 DELETE FROM "direct_voter" USING "issue"
169 WHERE "direct_voter"."issue_id" = "issue"."id"
170 AND "issue"."closed" ISNULL;
171 RETURN;
172 END;
173 $$;
175 COMMIT;