liquid_feedback_core

annotate update/core-update.v3.1.0-v3.2.0.sql @ 478:2f79434d0226

find changesets by author, revision, files, or words in the commit message
Added preliminary update script
author jbe
date Thu Mar 31 20:35:10 2016 +0200 (2016-03-31)
parents
children 8ded0e66a542
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 CASE WHEN "event"."state" IN (
jbe@478 33 'voting',
jbe@478 34 'finished_without_winner',
jbe@478 35 'finished_with_winner'
jbe@478 36 ) THEN
jbe@478 37 'voting'::"notify_level"
jbe@478 38 ELSE
jbe@478 39 CASE WHEN "event"."state" IN (
jbe@478 40 'verification',
jbe@478 41 'canceled_after_revocation_during_verification',
jbe@478 42 'canceled_no_initiative_admitted'
jbe@478 43 ) THEN
jbe@478 44 'verification'::"notify_level"
jbe@478 45 ELSE
jbe@478 46 CASE WHEN "event"."state" IN (
jbe@478 47 'discussion',
jbe@478 48 'canceled_after_revocation_during_discussion'
jbe@478 49 ) THEN
jbe@478 50 'discussion'::"notify_level"
jbe@478 51 ELSE
jbe@478 52 'all'::"notify_level"
jbe@478 53 END
jbe@478 54 END
jbe@478 55 END AS "notify_level",
jbe@478 56 "event".*
jbe@478 57 FROM "member" CROSS JOIN "event"
jbe@478 58 LEFT JOIN "issue"
jbe@478 59 ON "event"."issue_id" = "issue"."id"
jbe@478 60 LEFT JOIN "membership"
jbe@478 61 ON "member"."id" = "membership"."member_id"
jbe@478 62 AND "issue"."area_id" = "membership"."area_id"
jbe@478 63 LEFT JOIN "interest"
jbe@478 64 ON "member"."id" = "interest"."member_id"
jbe@478 65 AND "event"."issue_id" = "interest"."issue_id"
jbe@478 66 LEFT JOIN "ignored_member"
jbe@478 67 ON "member"."id" = "ignored_member"."member_id"
jbe@478 68 AND "event"."member_id" = "ignored_member"."other_member_id"
jbe@478 69 LEFT JOIN "ignored_initiative"
jbe@478 70 ON "member"."id" = "ignored_initiative"."member_id"
jbe@478 71 AND "event"."initiative_id" = "ignored_initiative"."initiative_id"
jbe@478 72 WHERE (
jbe@478 73 ( "member"."notify_level" >= 'all' ) OR
jbe@478 74 ( "member"."notify_level" >= 'voting' AND
jbe@478 75 "event"."state" IN (
jbe@478 76 'voting',
jbe@478 77 'finished_without_winner',
jbe@478 78 'finished_with_winner' ) ) OR
jbe@478 79 ( "member"."notify_level" >= 'verification' AND
jbe@478 80 "event"."state" IN (
jbe@478 81 'verification',
jbe@478 82 'canceled_after_revocation_during_verification',
jbe@478 83 'canceled_no_initiative_admitted' ) ) OR
jbe@478 84 ( "member"."notify_level" >= 'discussion' AND
jbe@478 85 "event"."state" IN (
jbe@478 86 'discussion',
jbe@478 87 'canceled_after_revocation_during_discussion' ) ) )
jbe@478 88 TRUE AS "supported",
jbe@478 89 EXISTS (
jbe@478 90 SELECT NULL FROM "draft"
jbe@478 91 WHERE "draft"."initiative_id" = "initiative"."id"
jbe@478 92 AND "draft"."id" > "supporter"."draft_id"
jbe@478 93 ) AS "new_draft",
jbe@478 94 ( SELECT count(1) FROM "suggestion"
jbe@478 95 WHERE "suggestion"."initiative_id" = "initiative"."id"
jbe@478 96 AND COALESCE(
jbe@478 97 "suggestion"."id" > "member"."last_notified_suggestion_id",
jbe@478 98 TRUE
jbe@478 99 )
jbe@478 100 ) AS "new_suggestion_count",
jbe@478 101 FALSE AS "featured",
jbe@478 102 NOT EXISTS (
jbe@478 103 SELECT NULL FROM "initiative" AS "better_initiative"
jbe@478 104 WHERE
jbe@478 105 ("better_initiative"."harmonic_weight", -"better_initiative"."id") >
jbe@478 106 ("initiative"."harmonic_weight", -"better_initiative"."id")
jbe@478 107 ) AS "leading",
jbe@478 108 "initiative".*
jbe@478 109 FROM "member" CROSS JOIN "initiative"
jbe@478 110 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
jbe@478 111 JOIN "supporter" ON
jbe@478 112 "supporter"."member_id" = "member"."id" AND
jbe@478 113 "supporter"."initiative_id" = "initiative"."id"
jbe@478 114 WHERE "issue"."state" IN ('admission', 'discussion') ISNULL
jbe@478 115 AND (
jbe@478 116 EXISTS (
jbe@478 117 SELECT NULL FROM "draft"
jbe@478 118 WHERE "draft"."initiative_id" = "initiative"."id"
jbe@478 119 AND "draft"."id" > "supporter"."draft_id"
jbe@478 120 ) OR EXISTS (
jbe@478 121 SELECT NULL FROM "suggestion"
jbe@478 122 WHERE "suggestion"."initiative_id" = "initiative"."id"
jbe@478 123 AND COALESCE(
jbe@478 124 "suggestion"."id" > "member"."last_notified_suggestion_id",
jbe@478 125 TRUE
jbe@478 126 )
jbe@478 127 )
jbe@478 128 );
jbe@478 129
jbe@478 130 CREATE FUNCTION "featured_initiative"
jbe@478 131 ( "member_id_p" "member"."id"%TYPE,
jbe@478 132 "area_id_p" "area"."id"%TYPE )
jbe@478 133 RETURNS SETOF "initiative"
jbe@478 134 LANGUAGE 'plpgsql' STABLE AS $$
jbe@478 135 DECLARE
jbe@478 136 "sample_size_v" INT4;
jbe@478 137 "member_id_v" "member"."id"%TYPE;
jbe@478 138 "seed_v" TEXT;
jbe@478 139 "result_row" "initiative"%ROWTYPE;
jbe@478 140 "match_v" BOOLEAN;
jbe@478 141 "initiative_id_ary" INT4[]; --"initiative"."id"%TYPE[]
jbe@478 142 BEGIN
jbe@478 143 SELECT INTO "sample_size_v" "sample_size" FROM "member" WHERE "id" = "member_id_p";
jbe@478 144 "initiative_id_ary" := '{}';
jbe@478 145 LOOP
jbe@478 146 "match_v" := FALSE;
jbe@478 147 FOR "member_id_v", "seed_v" IN
jbe@478 148 SELECT * FROM (
jbe@478 149 SELECT DISTINCT
jbe@478 150 "supporter"."member_id",
jbe@478 151 md5("member_id" || '-' || "member"."notification_counter" || '-' || "area_id_p") AS "seed"
jbe@478 152 FROM "supporter"
jbe@478 153 JOIN "member" ON "member"."id" = "supporter"."member_id"
jbe@478 154 JOIN "initiative" ON "initiative"."id" = "supporter"."initiative_id"
jbe@478 155 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
jbe@478 156 WHERE "supporter"."member_id" != "member_id_p"
jbe@478 157 AND "issue"."area_id" = "area_id_p"
jbe@478 158 AND "issue"."state" IN ('admission', 'discussion', 'verification')
jbe@478 159 ) AS "subquery"
jbe@478 160 ORDER BY "seed"
jbe@478 161 LOOP
jbe@478 162 SELECT "initiative".* INTO "result_row"
jbe@478 163 FROM "initiative"
jbe@478 164 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
jbe@478 165 JOIN "supporter" ON "supporter"."initiative_id" = "initiative"."id"
jbe@478 166 LEFT JOIN "supporter" AS "self_support" ON
jbe@478 167 "self_support"."initiative_id" = "initiative"."id" AND
jbe@478 168 "self_support"."member_id" = "member_id_p"
jbe@478 169 WHERE "supporter"."member_id" = "member_id_v"
jbe@478 170 AND "issue"."area_id" = "area_id_p"
jbe@478 171 AND "issue"."state" IN ('admission', 'discussion', 'verification')
jbe@478 172 AND "self_support"."member_id" ISNULL
jbe@478 173 AND NOT "initiative_id_ary" @> ARRAY["initiative"."id"]
jbe@478 174 ORDER BY md5("seed_v" || '-' || "initiative"."id")
jbe@478 175 LIMIT 1;
jbe@478 176 IF FOUND THEN
jbe@478 177 "match_v" := TRUE;
jbe@478 178 "initiative_id_ary" := "initiative_id_ary" || "result_row"."id";
jbe@478 179 RETURN NEXT "result_row";
jbe@478 180 IF array_length("initiative_id_ary", 1) >= "sample_size_v" THEN
jbe@478 181 RETURN;
jbe@478 182 END IF;
jbe@478 183 END IF;
jbe@478 184 END LOOP;
jbe@478 185 EXIT WHEN NOT "match_v";
jbe@478 186 END LOOP;
jbe@478 187 RETURN;
jbe@478 188 END;
jbe@478 189 $$;
jbe@478 190
jbe@478 191 CREATE VIEW "updated_or_featured_initiative" AS
jbe@478 192 SELECT * FROM "updated_initiative"
jbe@478 193 UNION ALL
jbe@478 194 SELECT
jbe@478 195 "member"."id" AS "seen_by_member_id",
jbe@478 196 FALSE AS "supported",
jbe@478 197 NULL::BOOLEAN AS "new_draft",
jbe@478 198 NULL::INTEGER AS "new_suggestion_count",
jbe@478 199 TRUE AS "featured",
jbe@478 200 NOT EXISTS (
jbe@478 201 SELECT NULL FROM "initiative" AS "better_initiative"
jbe@478 202 WHERE
jbe@478 203 ("better_initiative"."harmonic_weight", -"better_initiative"."id") >
jbe@478 204 ("initiative"."harmonic_weight", -"better_initiative"."id")
jbe@478 205 ) AS "leading",
jbe@478 206 "initiative".*
jbe@478 207 FROM "member" CROSS JOIN "area"
jbe@478 208 CROSS JOIN LATERAL
jbe@478 209 "featured_initiative"("member"."id", "area"."id") AS "initiative";
jbe@478 210
jbe@478 211 CREATE VIEW "leading_complement_initiative" AS
jbe@478 212 SELECT * FROM (
jbe@478 213 SELECT DISTINCT ON ("seen_by_member_id", "initiative"."issue_id")
jbe@478 214 "updated_or_featured_initiative"."seen_by_member_id",
jbe@478 215 FALSE AS "supported",
jbe@478 216 NULL::BOOLEAN AS "new_draft",
jbe@478 217 NULL::INTEGER AS "new_suggestion_count",
jbe@478 218 FALSE AS "featured",
jbe@478 219 TRUE AS "leading",
jbe@478 220 "initiative".*
jbe@478 221 FROM "updated_or_featured_initiative"
jbe@478 222 JOIN "initiative"
jbe@478 223 ON "updated_or_featured_initiative"."issue_id" = "initiative"."issue_id"
jbe@478 224 ORDER BY
jbe@478 225 "seen_by_member_id",
jbe@478 226 "initiative"."issue_id",
jbe@478 227 "initiative"."harmonic_weight" DESC,
jbe@478 228 "initiative"."id"
jbe@478 229 ) AS "subquery"
jbe@478 230 WHERE NOT EXISTS (
jbe@478 231 SELECT NULL FROM "updated_or_featured_initiative" AS "other"
jbe@478 232 WHERE "other"."seen_by_member_id" = "subquery"."seen_by_member_id"
jbe@478 233 AND "other"."id" = "subquery"."id"
jbe@478 234 );
jbe@478 235
jbe@478 236 CREATE VIEW "initiative_for_notification" AS
jbe@478 237 SELECT * FROM "updated_or_featured_initiative"
jbe@478 238 UNION ALL
jbe@478 239 SELECT * FROM "leading_complement_initiative";
jbe@478 240
jbe@478 241 COMMIT;

Impressum / About Us