liquid_feedback_core
view update/core-update.v3.1.0-v3.2.0.sql @ 478:2f79434d0226
Added preliminary update script
| author | jbe |
|---|---|
| date | Thu Mar 31 20:35:10 2016 +0200 (2016-03-31) |
| parents | |
| children | 8ded0e66a542 |
line source
1 BEGIN;
3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
4 SELECT * FROM (VALUES ('3.2.0', 3, 2, 0))
5 AS "subquery"("string", "major", "minor", "revision");
7 ALTER TABLE "member" ADD COLUMN "disable_notifications" BOOLEAN NOT NULL DEFAULT FALSE;
8 ALTER TABLE "member" ADD COLUMN "notification_counter" INT4 NOT NULL DEFAULT 0;
9 ALTER TABLE "member" ADD COLUMN "sample_size" INT4 NOT NULL DEFAULT 3;
10 ALTER TABLE "member" ADD COLUMN "last_notified_suggestion_id" INT8;
12 UPDATE "member" SET "disable_notifications" = TRUE WHERE "notify_level" = 'none';
14 CREATE TABLE "subscription_time" (
15 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
16 "day_of_week" INT2 CONSTRAINT "day_of_week_range" CHECK ("day_of_week" BETWEEN 0 AND 6),
17 "time_of_day" TIME NOT NULL );
18 CREATE UNIQUE INDEX "subscription_time_all_days_of_week_time_of_day_idx" ON "subscription_time" ("time_of_day", "member_id") WHERE ("day_of_week" ISNULL);
19 CREATE UNIQUE INDEX "subscription_time_day_of_week_time_of_day_idx" ON "subscription_time" ("day_of_week", "time_of_day", "member_id");
21 CREATE TABLE "subscription" (
22 PRIMARY KEY ("member_id", "unit_id"),
23 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
24 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
25 CREATE INDEX "subscription_unit_id_idx" ON "subscription" ("unit_id");
27 DROP VIEW "selected_event_seen_by_member";
29 CREATE VIEW "updated_initiative" AS
30 SELECT
31 "member"."id" AS "seen_by_member_id",
32 CASE WHEN "event"."state" IN (
33 'voting',
34 'finished_without_winner',
35 'finished_with_winner'
36 ) THEN
37 'voting'::"notify_level"
38 ELSE
39 CASE WHEN "event"."state" IN (
40 'verification',
41 'canceled_after_revocation_during_verification',
42 'canceled_no_initiative_admitted'
43 ) THEN
44 'verification'::"notify_level"
45 ELSE
46 CASE WHEN "event"."state" IN (
47 'discussion',
48 'canceled_after_revocation_during_discussion'
49 ) THEN
50 'discussion'::"notify_level"
51 ELSE
52 'all'::"notify_level"
53 END
54 END
55 END AS "notify_level",
56 "event".*
57 FROM "member" CROSS JOIN "event"
58 LEFT JOIN "issue"
59 ON "event"."issue_id" = "issue"."id"
60 LEFT JOIN "membership"
61 ON "member"."id" = "membership"."member_id"
62 AND "issue"."area_id" = "membership"."area_id"
63 LEFT JOIN "interest"
64 ON "member"."id" = "interest"."member_id"
65 AND "event"."issue_id" = "interest"."issue_id"
66 LEFT JOIN "ignored_member"
67 ON "member"."id" = "ignored_member"."member_id"
68 AND "event"."member_id" = "ignored_member"."other_member_id"
69 LEFT JOIN "ignored_initiative"
70 ON "member"."id" = "ignored_initiative"."member_id"
71 AND "event"."initiative_id" = "ignored_initiative"."initiative_id"
72 WHERE (
73 ( "member"."notify_level" >= 'all' ) OR
74 ( "member"."notify_level" >= 'voting' AND
75 "event"."state" IN (
76 'voting',
77 'finished_without_winner',
78 'finished_with_winner' ) ) OR
79 ( "member"."notify_level" >= 'verification' AND
80 "event"."state" IN (
81 'verification',
82 'canceled_after_revocation_during_verification',
83 'canceled_no_initiative_admitted' ) ) OR
84 ( "member"."notify_level" >= 'discussion' AND
85 "event"."state" IN (
86 'discussion',
87 'canceled_after_revocation_during_discussion' ) ) )
88 TRUE AS "supported",
89 EXISTS (
90 SELECT NULL FROM "draft"
91 WHERE "draft"."initiative_id" = "initiative"."id"
92 AND "draft"."id" > "supporter"."draft_id"
93 ) AS "new_draft",
94 ( SELECT count(1) FROM "suggestion"
95 WHERE "suggestion"."initiative_id" = "initiative"."id"
96 AND COALESCE(
97 "suggestion"."id" > "member"."last_notified_suggestion_id",
98 TRUE
99 )
100 ) AS "new_suggestion_count",
101 FALSE AS "featured",
102 NOT EXISTS (
103 SELECT NULL FROM "initiative" AS "better_initiative"
104 WHERE
105 ("better_initiative"."harmonic_weight", -"better_initiative"."id") >
106 ("initiative"."harmonic_weight", -"better_initiative"."id")
107 ) AS "leading",
108 "initiative".*
109 FROM "member" CROSS JOIN "initiative"
110 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
111 JOIN "supporter" ON
112 "supporter"."member_id" = "member"."id" AND
113 "supporter"."initiative_id" = "initiative"."id"
114 WHERE "issue"."state" IN ('admission', 'discussion') ISNULL
115 AND (
116 EXISTS (
117 SELECT NULL FROM "draft"
118 WHERE "draft"."initiative_id" = "initiative"."id"
119 AND "draft"."id" > "supporter"."draft_id"
120 ) OR EXISTS (
121 SELECT NULL FROM "suggestion"
122 WHERE "suggestion"."initiative_id" = "initiative"."id"
123 AND COALESCE(
124 "suggestion"."id" > "member"."last_notified_suggestion_id",
125 TRUE
126 )
127 )
128 );
130 CREATE FUNCTION "featured_initiative"
131 ( "member_id_p" "member"."id"%TYPE,
132 "area_id_p" "area"."id"%TYPE )
133 RETURNS SETOF "initiative"
134 LANGUAGE 'plpgsql' STABLE AS $$
135 DECLARE
136 "sample_size_v" INT4;
137 "member_id_v" "member"."id"%TYPE;
138 "seed_v" TEXT;
139 "result_row" "initiative"%ROWTYPE;
140 "match_v" BOOLEAN;
141 "initiative_id_ary" INT4[]; --"initiative"."id"%TYPE[]
142 BEGIN
143 SELECT INTO "sample_size_v" "sample_size" FROM "member" WHERE "id" = "member_id_p";
144 "initiative_id_ary" := '{}';
145 LOOP
146 "match_v" := FALSE;
147 FOR "member_id_v", "seed_v" IN
148 SELECT * FROM (
149 SELECT DISTINCT
150 "supporter"."member_id",
151 md5("member_id" || '-' || "member"."notification_counter" || '-' || "area_id_p") AS "seed"
152 FROM "supporter"
153 JOIN "member" ON "member"."id" = "supporter"."member_id"
154 JOIN "initiative" ON "initiative"."id" = "supporter"."initiative_id"
155 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
156 WHERE "supporter"."member_id" != "member_id_p"
157 AND "issue"."area_id" = "area_id_p"
158 AND "issue"."state" IN ('admission', 'discussion', 'verification')
159 ) AS "subquery"
160 ORDER BY "seed"
161 LOOP
162 SELECT "initiative".* INTO "result_row"
163 FROM "initiative"
164 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
165 JOIN "supporter" ON "supporter"."initiative_id" = "initiative"."id"
166 LEFT JOIN "supporter" AS "self_support" ON
167 "self_support"."initiative_id" = "initiative"."id" AND
168 "self_support"."member_id" = "member_id_p"
169 WHERE "supporter"."member_id" = "member_id_v"
170 AND "issue"."area_id" = "area_id_p"
171 AND "issue"."state" IN ('admission', 'discussion', 'verification')
172 AND "self_support"."member_id" ISNULL
173 AND NOT "initiative_id_ary" @> ARRAY["initiative"."id"]
174 ORDER BY md5("seed_v" || '-' || "initiative"."id")
175 LIMIT 1;
176 IF FOUND THEN
177 "match_v" := TRUE;
178 "initiative_id_ary" := "initiative_id_ary" || "result_row"."id";
179 RETURN NEXT "result_row";
180 IF array_length("initiative_id_ary", 1) >= "sample_size_v" THEN
181 RETURN;
182 END IF;
183 END IF;
184 END LOOP;
185 EXIT WHEN NOT "match_v";
186 END LOOP;
187 RETURN;
188 END;
189 $$;
191 CREATE VIEW "updated_or_featured_initiative" AS
192 SELECT * FROM "updated_initiative"
193 UNION ALL
194 SELECT
195 "member"."id" AS "seen_by_member_id",
196 FALSE AS "supported",
197 NULL::BOOLEAN AS "new_draft",
198 NULL::INTEGER AS "new_suggestion_count",
199 TRUE AS "featured",
200 NOT EXISTS (
201 SELECT NULL FROM "initiative" AS "better_initiative"
202 WHERE
203 ("better_initiative"."harmonic_weight", -"better_initiative"."id") >
204 ("initiative"."harmonic_weight", -"better_initiative"."id")
205 ) AS "leading",
206 "initiative".*
207 FROM "member" CROSS JOIN "area"
208 CROSS JOIN LATERAL
209 "featured_initiative"("member"."id", "area"."id") AS "initiative";
211 CREATE VIEW "leading_complement_initiative" AS
212 SELECT * FROM (
213 SELECT DISTINCT ON ("seen_by_member_id", "initiative"."issue_id")
214 "updated_or_featured_initiative"."seen_by_member_id",
215 FALSE AS "supported",
216 NULL::BOOLEAN AS "new_draft",
217 NULL::INTEGER AS "new_suggestion_count",
218 FALSE AS "featured",
219 TRUE AS "leading",
220 "initiative".*
221 FROM "updated_or_featured_initiative"
222 JOIN "initiative"
223 ON "updated_or_featured_initiative"."issue_id" = "initiative"."issue_id"
224 ORDER BY
225 "seen_by_member_id",
226 "initiative"."issue_id",
227 "initiative"."harmonic_weight" DESC,
228 "initiative"."id"
229 ) AS "subquery"
230 WHERE NOT EXISTS (
231 SELECT NULL FROM "updated_or_featured_initiative" AS "other"
232 WHERE "other"."seen_by_member_id" = "subquery"."seen_by_member_id"
233 AND "other"."id" = "subquery"."id"
234 );
236 CREATE VIEW "initiative_for_notification" AS
237 SELECT * FROM "updated_or_featured_initiative"
238 UNION ALL
239 SELECT * FROM "leading_complement_initiative";
241 COMMIT;