liquid_feedback_core
view update/core-update.v3.1.0-v3.2.0.sql @ 488:056ad11bbe66
Ignore suggestions with opinion also in returned column "new_suggestion_count" of view "updated_initiative"
| author | jbe |
|---|---|
| date | Sun Apr 03 16:42:01 2016 +0200 (2016-04-03) |
| parents | e431f1b73c35 |
| children | 917a65357efb |
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 TRUE AS "supported",
33 EXISTS (
34 SELECT NULL FROM "draft"
35 WHERE "draft"."initiative_id" = "initiative"."id"
36 AND "draft"."id" > "supporter"."draft_id"
37 ) AS "new_draft",
38 ( SELECT count(1) FROM "suggestion"
39 WHERE "suggestion"."initiative_id" = "initiative"."id"
40 AND COALESCE(
41 "suggestion"."id" > "member"."last_notified_suggestion_id",
42 TRUE
43 )
44 ) AS "new_suggestion_count",
45 FALSE AS "featured",
46 NOT EXISTS (
47 SELECT NULL FROM "initiative" AS "better_initiative"
48 WHERE
49 "better_initiative"."issue_id" = "initiative"."issue_id"
50 AND
51 ( COALESCE("better_initiative"."harmonic_weight", -1),
52 -"better_initiative"."id" ) >
53 ( COALESCE("initiative"."harmonic_weight", -1),
54 -"initiative"."id" )
55 ) AS "leading",
56 "initiative".*
57 FROM "member" CROSS JOIN "initiative"
58 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
59 JOIN "supporter" ON
60 "supporter"."member_id" = "member"."id" AND
61 "supporter"."initiative_id" = "initiative"."id"
62 WHERE "issue"."state" IN ('admission', 'discussion')
63 AND (
64 EXISTS (
65 SELECT NULL FROM "draft"
66 WHERE "draft"."initiative_id" = "initiative"."id"
67 AND "draft"."id" > "supporter"."draft_id"
68 ) OR EXISTS (
69 SELECT NULL FROM "suggestion"
70 WHERE "suggestion"."initiative_id" = "initiative"."id"
71 AND COALESCE(
72 "suggestion"."id" > "member"."last_notified_suggestion_id",
73 TRUE
74 )
75 )
76 );
78 CREATE FUNCTION "featured_initiative"
79 ( "member_id_p" "member"."id"%TYPE,
80 "area_id_p" "area"."id"%TYPE )
81 RETURNS SETOF "initiative"
82 LANGUAGE 'plpgsql' STABLE AS $$
83 DECLARE
84 "member_row" "member"%ROWTYPE;
85 "member_id_v" "member"."id"%TYPE;
86 "seed_v" TEXT;
87 "result_row" "initiative"%ROWTYPE;
88 "match_v" BOOLEAN;
89 "initiative_id_ary" INT4[]; --"initiative"."id"%TYPE[]
90 BEGIN
91 SELECT INTO "member_row" * FROM "member" WHERE "id" = "member_id_p";
92 "initiative_id_ary" := '{}';
93 LOOP
94 "match_v" := FALSE;
95 FOR "member_id_v", "seed_v" IN
96 SELECT * FROM (
97 SELECT DISTINCT
98 "supporter"."member_id",
99 md5("member_id_p" || '-' || "member_row"."notification_counter" || '-' || "area_id_p" || '-' || "supporter"."member_id") AS "seed"
100 FROM "supporter"
101 JOIN "member" ON "member"."id" = "supporter"."member_id"
102 JOIN "initiative" ON "initiative"."id" = "supporter"."initiative_id"
103 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
104 WHERE "supporter"."member_id" != "member_id_p"
105 AND "issue"."area_id" = "area_id_p"
106 AND "issue"."state" IN ('admission', 'discussion', 'verification')
107 ) AS "subquery"
108 ORDER BY "seed"
109 LOOP
110 SELECT "initiative".* INTO "result_row"
111 FROM "initiative"
112 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
113 JOIN "supporter" ON "supporter"."initiative_id" = "initiative"."id"
114 LEFT JOIN "supporter" AS "self_support" ON
115 "self_support"."initiative_id" = "initiative"."id" AND
116 "self_support"."member_id" = "member_id_p"
117 WHERE "supporter"."member_id" = "member_id_v"
118 AND "issue"."area_id" = "area_id_p"
119 AND "issue"."state" IN ('admission', 'discussion', 'verification')
120 AND "self_support"."member_id" ISNULL
121 AND NOT "initiative_id_ary" @> ARRAY["initiative"."id"]
122 ORDER BY md5("seed_v" || '-' || "initiative"."id")
123 LIMIT 1;
124 IF FOUND THEN
125 "match_v" := TRUE;
126 "initiative_id_ary" := "initiative_id_ary" || "result_row"."id";
127 RETURN NEXT "result_row";
128 IF array_length("initiative_id_ary", 1) >= "member_row"."sample_size" THEN
129 RETURN;
130 END IF;
131 END IF;
132 END LOOP;
133 EXIT WHEN NOT "match_v";
134 END LOOP;
135 RETURN;
136 END;
137 $$;
139 CREATE VIEW "updated_or_featured_initiative" AS
140 SELECT * FROM "updated_initiative"
141 UNION ALL
142 SELECT
143 "member"."id" AS "seen_by_member_id",
144 FALSE AS "supported",
145 NULL::BOOLEAN AS "new_draft",
146 NULL::INTEGER AS "new_suggestion_count",
147 TRUE AS "featured",
148 NOT EXISTS (
149 SELECT NULL FROM "initiative" AS "better_initiative"
150 WHERE
151 "better_initiative"."issue_id" = "initiative"."issue_id"
152 AND
153 ( COALESCE("better_initiative"."harmonic_weight", -1),
154 -"better_initiative"."id" ) >
155 ( COALESCE("initiative"."harmonic_weight", -1),
156 -"initiative"."id" )
157 ) AS "leading",
158 "initiative".*
159 FROM "member" CROSS JOIN "area"
160 CROSS JOIN LATERAL
161 "featured_initiative"("member"."id", "area"."id") AS "initiative";
163 CREATE VIEW "leading_complement_initiative" AS
164 SELECT * FROM (
165 SELECT DISTINCT ON ("seen_by_member_id", "initiative"."issue_id")
166 "updated_or_featured_initiative"."seen_by_member_id",
167 FALSE AS "supported",
168 NULL::BOOLEAN AS "new_draft",
169 NULL::INTEGER AS "new_suggestion_count",
170 FALSE AS "featured",
171 TRUE AS "leading",
172 "initiative".*
173 FROM "updated_or_featured_initiative"
174 JOIN "initiative"
175 ON "updated_or_featured_initiative"."issue_id" = "initiative"."issue_id"
176 ORDER BY
177 "seen_by_member_id",
178 "initiative"."issue_id",
179 "initiative"."harmonic_weight" DESC,
180 "initiative"."id"
181 ) AS "subquery"
182 WHERE NOT EXISTS (
183 SELECT NULL FROM "updated_or_featured_initiative" AS "other"
184 WHERE "other"."seen_by_member_id" = "subquery"."seen_by_member_id"
185 AND "other"."id" = "subquery"."id"
186 );
188 CREATE VIEW "initiative_for_notification" AS
189 SELECT * FROM "updated_or_featured_initiative"
190 UNION ALL
191 SELECT * FROM "leading_complement_initiative";
193 COMMIT;