| rev |
line source |
|
jbe@478
|
1 BEGIN;
|
|
jbe@478
|
2
|
|
jbe@478
|
3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
|
|
jbe@478
|
4 SELECT * FROM (VALUES ('3.2.0', 3, 2, 0))
|
|
jbe@478
|
5 AS "subquery"("string", "major", "minor", "revision");
|
|
jbe@478
|
6
|
|
jbe@478
|
7 ALTER TABLE "member" ADD COLUMN "disable_notifications" BOOLEAN NOT NULL DEFAULT FALSE;
|
|
jbe@478
|
8 ALTER TABLE "member" ADD COLUMN "notification_counter" INT4 NOT NULL DEFAULT 0;
|
|
jbe@478
|
9 ALTER TABLE "member" ADD COLUMN "sample_size" INT4 NOT NULL DEFAULT 3;
|
|
jbe@478
|
10 ALTER TABLE "member" ADD COLUMN "last_notified_suggestion_id" INT8;
|
|
jbe@478
|
11
|
|
jbe@478
|
12 UPDATE "member" SET "disable_notifications" = TRUE WHERE "notify_level" = 'none';
|
|
jbe@478
|
13
|
|
jbe@478
|
14 CREATE TABLE "subscription_time" (
|
|
jbe@478
|
15 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
jbe@478
|
16 "day_of_week" INT2 CONSTRAINT "day_of_week_range" CHECK ("day_of_week" BETWEEN 0 AND 6),
|
|
jbe@478
|
17 "time_of_day" TIME NOT NULL );
|
|
jbe@478
|
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);
|
|
jbe@478
|
19 CREATE UNIQUE INDEX "subscription_time_day_of_week_time_of_day_idx" ON "subscription_time" ("day_of_week", "time_of_day", "member_id");
|
|
jbe@478
|
20
|
|
jbe@478
|
21 CREATE TABLE "subscription" (
|
|
jbe@478
|
22 PRIMARY KEY ("member_id", "unit_id"),
|
|
jbe@478
|
23 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
jbe@478
|
24 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
|
|
jbe@478
|
25 CREATE INDEX "subscription_unit_id_idx" ON "subscription" ("unit_id");
|
|
jbe@478
|
26
|
|
jbe@478
|
27 DROP VIEW "selected_event_seen_by_member";
|
|
jbe@478
|
28
|
|
jbe@478
|
29 CREATE VIEW "updated_initiative" AS
|
|
jbe@478
|
30 SELECT
|
|
jbe@478
|
31 "member"."id" AS "seen_by_member_id",
|
|
jbe@478
|
32 TRUE AS "supported",
|
|
jbe@478
|
33 EXISTS (
|
|
jbe@478
|
34 SELECT NULL FROM "draft"
|
|
jbe@478
|
35 WHERE "draft"."initiative_id" = "initiative"."id"
|
|
jbe@478
|
36 AND "draft"."id" > "supporter"."draft_id"
|
|
jbe@478
|
37 ) AS "new_draft",
|
|
jbe@478
|
38 ( SELECT count(1) FROM "suggestion"
|
|
jbe@478
|
39 WHERE "suggestion"."initiative_id" = "initiative"."id"
|
|
jbe@478
|
40 AND COALESCE(
|
|
jbe@478
|
41 "suggestion"."id" > "member"."last_notified_suggestion_id",
|
|
jbe@478
|
42 TRUE
|
|
jbe@478
|
43 )
|
|
jbe@478
|
44 ) AS "new_suggestion_count",
|
|
jbe@478
|
45 FALSE AS "featured",
|
|
jbe@478
|
46 NOT EXISTS (
|
|
jbe@478
|
47 SELECT NULL FROM "initiative" AS "better_initiative"
|
|
jbe@478
|
48 WHERE
|
|
jbe@481
|
49 "better_initiative"."issue_id" = "initiative"."issue_id" AND
|
|
jbe@478
|
50 ("better_initiative"."harmonic_weight", -"better_initiative"."id") >
|
|
jbe@478
|
51 ("initiative"."harmonic_weight", -"better_initiative"."id")
|
|
jbe@478
|
52 ) AS "leading",
|
|
jbe@478
|
53 "initiative".*
|
|
jbe@478
|
54 FROM "member" CROSS JOIN "initiative"
|
|
jbe@478
|
55 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
|
|
jbe@478
|
56 JOIN "supporter" ON
|
|
jbe@478
|
57 "supporter"."member_id" = "member"."id" AND
|
|
jbe@478
|
58 "supporter"."initiative_id" = "initiative"."id"
|
|
jbe@480
|
59 WHERE "issue"."state" IN ('admission', 'discussion')
|
|
jbe@478
|
60 AND (
|
|
jbe@478
|
61 EXISTS (
|
|
jbe@478
|
62 SELECT NULL FROM "draft"
|
|
jbe@478
|
63 WHERE "draft"."initiative_id" = "initiative"."id"
|
|
jbe@478
|
64 AND "draft"."id" > "supporter"."draft_id"
|
|
jbe@478
|
65 ) OR EXISTS (
|
|
jbe@478
|
66 SELECT NULL FROM "suggestion"
|
|
jbe@478
|
67 WHERE "suggestion"."initiative_id" = "initiative"."id"
|
|
jbe@478
|
68 AND COALESCE(
|
|
jbe@478
|
69 "suggestion"."id" > "member"."last_notified_suggestion_id",
|
|
jbe@478
|
70 TRUE
|
|
jbe@478
|
71 )
|
|
jbe@478
|
72 )
|
|
jbe@478
|
73 );
|
|
jbe@478
|
74
|
|
jbe@478
|
75 CREATE FUNCTION "featured_initiative"
|
|
jbe@478
|
76 ( "member_id_p" "member"."id"%TYPE,
|
|
jbe@478
|
77 "area_id_p" "area"."id"%TYPE )
|
|
jbe@478
|
78 RETURNS SETOF "initiative"
|
|
jbe@478
|
79 LANGUAGE 'plpgsql' STABLE AS $$
|
|
jbe@478
|
80 DECLARE
|
|
jbe@482
|
81 "member_row" "member"%ROWTYPE;
|
|
jbe@478
|
82 "member_id_v" "member"."id"%TYPE;
|
|
jbe@478
|
83 "seed_v" TEXT;
|
|
jbe@478
|
84 "result_row" "initiative"%ROWTYPE;
|
|
jbe@478
|
85 "match_v" BOOLEAN;
|
|
jbe@478
|
86 "initiative_id_ary" INT4[]; --"initiative"."id"%TYPE[]
|
|
jbe@478
|
87 BEGIN
|
|
jbe@482
|
88 SELECT INTO "member_row" * FROM "member" WHERE "id" = "member_id_p";
|
|
jbe@478
|
89 "initiative_id_ary" := '{}';
|
|
jbe@478
|
90 LOOP
|
|
jbe@478
|
91 "match_v" := FALSE;
|
|
jbe@478
|
92 FOR "member_id_v", "seed_v" IN
|
|
jbe@478
|
93 SELECT * FROM (
|
|
jbe@478
|
94 SELECT DISTINCT
|
|
jbe@478
|
95 "supporter"."member_id",
|
|
jbe@482
|
96 md5("member_id_p" || '-' || "member_row"."notification_counter" || '-' || "area_id_p" || '-' || "supporter"."member_id") AS "seed"
|
|
jbe@478
|
97 FROM "supporter"
|
|
jbe@478
|
98 JOIN "member" ON "member"."id" = "supporter"."member_id"
|
|
jbe@478
|
99 JOIN "initiative" ON "initiative"."id" = "supporter"."initiative_id"
|
|
jbe@478
|
100 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
|
|
jbe@478
|
101 WHERE "supporter"."member_id" != "member_id_p"
|
|
jbe@478
|
102 AND "issue"."area_id" = "area_id_p"
|
|
jbe@478
|
103 AND "issue"."state" IN ('admission', 'discussion', 'verification')
|
|
jbe@478
|
104 ) AS "subquery"
|
|
jbe@478
|
105 ORDER BY "seed"
|
|
jbe@478
|
106 LOOP
|
|
jbe@478
|
107 SELECT "initiative".* INTO "result_row"
|
|
jbe@478
|
108 FROM "initiative"
|
|
jbe@478
|
109 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
|
|
jbe@478
|
110 JOIN "supporter" ON "supporter"."initiative_id" = "initiative"."id"
|
|
jbe@478
|
111 LEFT JOIN "supporter" AS "self_support" ON
|
|
jbe@478
|
112 "self_support"."initiative_id" = "initiative"."id" AND
|
|
jbe@478
|
113 "self_support"."member_id" = "member_id_p"
|
|
jbe@478
|
114 WHERE "supporter"."member_id" = "member_id_v"
|
|
jbe@478
|
115 AND "issue"."area_id" = "area_id_p"
|
|
jbe@478
|
116 AND "issue"."state" IN ('admission', 'discussion', 'verification')
|
|
jbe@478
|
117 AND "self_support"."member_id" ISNULL
|
|
jbe@478
|
118 AND NOT "initiative_id_ary" @> ARRAY["initiative"."id"]
|
|
jbe@478
|
119 ORDER BY md5("seed_v" || '-' || "initiative"."id")
|
|
jbe@478
|
120 LIMIT 1;
|
|
jbe@478
|
121 IF FOUND THEN
|
|
jbe@478
|
122 "match_v" := TRUE;
|
|
jbe@478
|
123 "initiative_id_ary" := "initiative_id_ary" || "result_row"."id";
|
|
jbe@478
|
124 RETURN NEXT "result_row";
|
|
jbe@483
|
125 IF array_length("initiative_id_ary", 1) >= "member_row"."sample_size" THEN
|
|
jbe@478
|
126 RETURN;
|
|
jbe@478
|
127 END IF;
|
|
jbe@478
|
128 END IF;
|
|
jbe@478
|
129 END LOOP;
|
|
jbe@478
|
130 EXIT WHEN NOT "match_v";
|
|
jbe@478
|
131 END LOOP;
|
|
jbe@478
|
132 RETURN;
|
|
jbe@478
|
133 END;
|
|
jbe@478
|
134 $$;
|
|
jbe@478
|
135
|
|
jbe@478
|
136 CREATE VIEW "updated_or_featured_initiative" AS
|
|
jbe@478
|
137 SELECT * FROM "updated_initiative"
|
|
jbe@478
|
138 UNION ALL
|
|
jbe@478
|
139 SELECT
|
|
jbe@478
|
140 "member"."id" AS "seen_by_member_id",
|
|
jbe@478
|
141 FALSE AS "supported",
|
|
jbe@478
|
142 NULL::BOOLEAN AS "new_draft",
|
|
jbe@478
|
143 NULL::INTEGER AS "new_suggestion_count",
|
|
jbe@478
|
144 TRUE AS "featured",
|
|
jbe@478
|
145 NOT EXISTS (
|
|
jbe@478
|
146 SELECT NULL FROM "initiative" AS "better_initiative"
|
|
jbe@478
|
147 WHERE
|
|
jbe@481
|
148 "better_initiative"."issue_id" = "initiative"."issue_id" AND
|
|
jbe@478
|
149 ("better_initiative"."harmonic_weight", -"better_initiative"."id") >
|
|
jbe@478
|
150 ("initiative"."harmonic_weight", -"better_initiative"."id")
|
|
jbe@478
|
151 ) AS "leading",
|
|
jbe@478
|
152 "initiative".*
|
|
jbe@478
|
153 FROM "member" CROSS JOIN "area"
|
|
jbe@478
|
154 CROSS JOIN LATERAL
|
|
jbe@478
|
155 "featured_initiative"("member"."id", "area"."id") AS "initiative";
|
|
jbe@478
|
156
|
|
jbe@478
|
157 CREATE VIEW "leading_complement_initiative" AS
|
|
jbe@478
|
158 SELECT * FROM (
|
|
jbe@478
|
159 SELECT DISTINCT ON ("seen_by_member_id", "initiative"."issue_id")
|
|
jbe@478
|
160 "updated_or_featured_initiative"."seen_by_member_id",
|
|
jbe@478
|
161 FALSE AS "supported",
|
|
jbe@478
|
162 NULL::BOOLEAN AS "new_draft",
|
|
jbe@478
|
163 NULL::INTEGER AS "new_suggestion_count",
|
|
jbe@478
|
164 FALSE AS "featured",
|
|
jbe@478
|
165 TRUE AS "leading",
|
|
jbe@478
|
166 "initiative".*
|
|
jbe@478
|
167 FROM "updated_or_featured_initiative"
|
|
jbe@478
|
168 JOIN "initiative"
|
|
jbe@478
|
169 ON "updated_or_featured_initiative"."issue_id" = "initiative"."issue_id"
|
|
jbe@478
|
170 ORDER BY
|
|
jbe@478
|
171 "seen_by_member_id",
|
|
jbe@478
|
172 "initiative"."issue_id",
|
|
jbe@478
|
173 "initiative"."harmonic_weight" DESC,
|
|
jbe@478
|
174 "initiative"."id"
|
|
jbe@478
|
175 ) AS "subquery"
|
|
jbe@478
|
176 WHERE NOT EXISTS (
|
|
jbe@478
|
177 SELECT NULL FROM "updated_or_featured_initiative" AS "other"
|
|
jbe@478
|
178 WHERE "other"."seen_by_member_id" = "subquery"."seen_by_member_id"
|
|
jbe@478
|
179 AND "other"."id" = "subquery"."id"
|
|
jbe@478
|
180 );
|
|
jbe@478
|
181
|
|
jbe@478
|
182 CREATE VIEW "initiative_for_notification" AS
|
|
jbe@478
|
183 SELECT * FROM "updated_or_featured_initiative"
|
|
jbe@478
|
184 UNION ALL
|
|
jbe@478
|
185 SELECT * FROM "leading_complement_initiative";
|
|
jbe@478
|
186
|
|
jbe@478
|
187 COMMIT;
|