liquid_feedback_core
changeset 478:2f79434d0226
Added preliminary update script
author | jbe |
---|---|
date | Thu Mar 31 20:35:10 2016 +0200 (2016-03-31) |
parents | eff90049fcff |
children | 8ded0e66a542 |
files | update/core-update.v3.1.0-v3.2.0.sql |
line diff
1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000 1.2 +++ b/update/core-update.v3.1.0-v3.2.0.sql Thu Mar 31 20:35:10 2016 +0200 1.3 @@ -0,0 +1,241 @@ 1.4 +BEGIN; 1.5 + 1.6 +CREATE OR REPLACE VIEW "liquid_feedback_version" AS 1.7 + SELECT * FROM (VALUES ('3.2.0', 3, 2, 0)) 1.8 + AS "subquery"("string", "major", "minor", "revision"); 1.9 + 1.10 +ALTER TABLE "member" ADD COLUMN "disable_notifications" BOOLEAN NOT NULL DEFAULT FALSE; 1.11 +ALTER TABLE "member" ADD COLUMN "notification_counter" INT4 NOT NULL DEFAULT 0; 1.12 +ALTER TABLE "member" ADD COLUMN "sample_size" INT4 NOT NULL DEFAULT 3; 1.13 +ALTER TABLE "member" ADD COLUMN "last_notified_suggestion_id" INT8; 1.14 + 1.15 +UPDATE "member" SET "disable_notifications" = TRUE WHERE "notify_level" = 'none'; 1.16 + 1.17 +CREATE TABLE "subscription_time" ( 1.18 + "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.19 + "day_of_week" INT2 CONSTRAINT "day_of_week_range" CHECK ("day_of_week" BETWEEN 0 AND 6), 1.20 + "time_of_day" TIME NOT NULL ); 1.21 +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); 1.22 +CREATE UNIQUE INDEX "subscription_time_day_of_week_time_of_day_idx" ON "subscription_time" ("day_of_week", "time_of_day", "member_id"); 1.23 + 1.24 +CREATE TABLE "subscription" ( 1.25 + PRIMARY KEY ("member_id", "unit_id"), 1.26 + "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.27 + "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE ); 1.28 +CREATE INDEX "subscription_unit_id_idx" ON "subscription" ("unit_id"); 1.29 + 1.30 +DROP VIEW "selected_event_seen_by_member"; 1.31 + 1.32 +CREATE VIEW "updated_initiative" AS 1.33 + SELECT 1.34 + "member"."id" AS "seen_by_member_id", 1.35 + CASE WHEN "event"."state" IN ( 1.36 + 'voting', 1.37 + 'finished_without_winner', 1.38 + 'finished_with_winner' 1.39 + ) THEN 1.40 + 'voting'::"notify_level" 1.41 + ELSE 1.42 + CASE WHEN "event"."state" IN ( 1.43 + 'verification', 1.44 + 'canceled_after_revocation_during_verification', 1.45 + 'canceled_no_initiative_admitted' 1.46 + ) THEN 1.47 + 'verification'::"notify_level" 1.48 + ELSE 1.49 + CASE WHEN "event"."state" IN ( 1.50 + 'discussion', 1.51 + 'canceled_after_revocation_during_discussion' 1.52 + ) THEN 1.53 + 'discussion'::"notify_level" 1.54 + ELSE 1.55 + 'all'::"notify_level" 1.56 + END 1.57 + END 1.58 + END AS "notify_level", 1.59 + "event".* 1.60 + FROM "member" CROSS JOIN "event" 1.61 + LEFT JOIN "issue" 1.62 + ON "event"."issue_id" = "issue"."id" 1.63 + LEFT JOIN "membership" 1.64 + ON "member"."id" = "membership"."member_id" 1.65 + AND "issue"."area_id" = "membership"."area_id" 1.66 + LEFT JOIN "interest" 1.67 + ON "member"."id" = "interest"."member_id" 1.68 + AND "event"."issue_id" = "interest"."issue_id" 1.69 + LEFT JOIN "ignored_member" 1.70 + ON "member"."id" = "ignored_member"."member_id" 1.71 + AND "event"."member_id" = "ignored_member"."other_member_id" 1.72 + LEFT JOIN "ignored_initiative" 1.73 + ON "member"."id" = "ignored_initiative"."member_id" 1.74 + AND "event"."initiative_id" = "ignored_initiative"."initiative_id" 1.75 + WHERE ( 1.76 + ( "member"."notify_level" >= 'all' ) OR 1.77 + ( "member"."notify_level" >= 'voting' AND 1.78 + "event"."state" IN ( 1.79 + 'voting', 1.80 + 'finished_without_winner', 1.81 + 'finished_with_winner' ) ) OR 1.82 + ( "member"."notify_level" >= 'verification' AND 1.83 + "event"."state" IN ( 1.84 + 'verification', 1.85 + 'canceled_after_revocation_during_verification', 1.86 + 'canceled_no_initiative_admitted' ) ) OR 1.87 + ( "member"."notify_level" >= 'discussion' AND 1.88 + "event"."state" IN ( 1.89 + 'discussion', 1.90 + 'canceled_after_revocation_during_discussion' ) ) ) 1.91 + TRUE AS "supported", 1.92 + EXISTS ( 1.93 + SELECT NULL FROM "draft" 1.94 + WHERE "draft"."initiative_id" = "initiative"."id" 1.95 + AND "draft"."id" > "supporter"."draft_id" 1.96 + ) AS "new_draft", 1.97 + ( SELECT count(1) FROM "suggestion" 1.98 + WHERE "suggestion"."initiative_id" = "initiative"."id" 1.99 + AND COALESCE( 1.100 + "suggestion"."id" > "member"."last_notified_suggestion_id", 1.101 + TRUE 1.102 + ) 1.103 + ) AS "new_suggestion_count", 1.104 + FALSE AS "featured", 1.105 + NOT EXISTS ( 1.106 + SELECT NULL FROM "initiative" AS "better_initiative" 1.107 + WHERE 1.108 + ("better_initiative"."harmonic_weight", -"better_initiative"."id") > 1.109 + ("initiative"."harmonic_weight", -"better_initiative"."id") 1.110 + ) AS "leading", 1.111 + "initiative".* 1.112 + FROM "member" CROSS JOIN "initiative" 1.113 + JOIN "issue" ON "issue"."id" = "initiative"."issue_id" 1.114 + JOIN "supporter" ON 1.115 + "supporter"."member_id" = "member"."id" AND 1.116 + "supporter"."initiative_id" = "initiative"."id" 1.117 + WHERE "issue"."state" IN ('admission', 'discussion') ISNULL 1.118 + AND ( 1.119 + EXISTS ( 1.120 + SELECT NULL FROM "draft" 1.121 + WHERE "draft"."initiative_id" = "initiative"."id" 1.122 + AND "draft"."id" > "supporter"."draft_id" 1.123 + ) OR EXISTS ( 1.124 + SELECT NULL FROM "suggestion" 1.125 + WHERE "suggestion"."initiative_id" = "initiative"."id" 1.126 + AND COALESCE( 1.127 + "suggestion"."id" > "member"."last_notified_suggestion_id", 1.128 + TRUE 1.129 + ) 1.130 + ) 1.131 + ); 1.132 + 1.133 +CREATE FUNCTION "featured_initiative" 1.134 + ( "member_id_p" "member"."id"%TYPE, 1.135 + "area_id_p" "area"."id"%TYPE ) 1.136 + RETURNS SETOF "initiative" 1.137 + LANGUAGE 'plpgsql' STABLE AS $$ 1.138 + DECLARE 1.139 + "sample_size_v" INT4; 1.140 + "member_id_v" "member"."id"%TYPE; 1.141 + "seed_v" TEXT; 1.142 + "result_row" "initiative"%ROWTYPE; 1.143 + "match_v" BOOLEAN; 1.144 + "initiative_id_ary" INT4[]; --"initiative"."id"%TYPE[] 1.145 + BEGIN 1.146 + SELECT INTO "sample_size_v" "sample_size" FROM "member" WHERE "id" = "member_id_p"; 1.147 + "initiative_id_ary" := '{}'; 1.148 + LOOP 1.149 + "match_v" := FALSE; 1.150 + FOR "member_id_v", "seed_v" IN 1.151 + SELECT * FROM ( 1.152 + SELECT DISTINCT 1.153 + "supporter"."member_id", 1.154 + md5("member_id" || '-' || "member"."notification_counter" || '-' || "area_id_p") AS "seed" 1.155 + FROM "supporter" 1.156 + JOIN "member" ON "member"."id" = "supporter"."member_id" 1.157 + JOIN "initiative" ON "initiative"."id" = "supporter"."initiative_id" 1.158 + JOIN "issue" ON "issue"."id" = "initiative"."issue_id" 1.159 + WHERE "supporter"."member_id" != "member_id_p" 1.160 + AND "issue"."area_id" = "area_id_p" 1.161 + AND "issue"."state" IN ('admission', 'discussion', 'verification') 1.162 + ) AS "subquery" 1.163 + ORDER BY "seed" 1.164 + LOOP 1.165 + SELECT "initiative".* INTO "result_row" 1.166 + FROM "initiative" 1.167 + JOIN "issue" ON "issue"."id" = "initiative"."issue_id" 1.168 + JOIN "supporter" ON "supporter"."initiative_id" = "initiative"."id" 1.169 + LEFT JOIN "supporter" AS "self_support" ON 1.170 + "self_support"."initiative_id" = "initiative"."id" AND 1.171 + "self_support"."member_id" = "member_id_p" 1.172 + WHERE "supporter"."member_id" = "member_id_v" 1.173 + AND "issue"."area_id" = "area_id_p" 1.174 + AND "issue"."state" IN ('admission', 'discussion', 'verification') 1.175 + AND "self_support"."member_id" ISNULL 1.176 + AND NOT "initiative_id_ary" @> ARRAY["initiative"."id"] 1.177 + ORDER BY md5("seed_v" || '-' || "initiative"."id") 1.178 + LIMIT 1; 1.179 + IF FOUND THEN 1.180 + "match_v" := TRUE; 1.181 + "initiative_id_ary" := "initiative_id_ary" || "result_row"."id"; 1.182 + RETURN NEXT "result_row"; 1.183 + IF array_length("initiative_id_ary", 1) >= "sample_size_v" THEN 1.184 + RETURN; 1.185 + END IF; 1.186 + END IF; 1.187 + END LOOP; 1.188 + EXIT WHEN NOT "match_v"; 1.189 + END LOOP; 1.190 + RETURN; 1.191 + END; 1.192 + $$; 1.193 + 1.194 +CREATE VIEW "updated_or_featured_initiative" AS 1.195 + SELECT * FROM "updated_initiative" 1.196 + UNION ALL 1.197 + SELECT 1.198 + "member"."id" AS "seen_by_member_id", 1.199 + FALSE AS "supported", 1.200 + NULL::BOOLEAN AS "new_draft", 1.201 + NULL::INTEGER AS "new_suggestion_count", 1.202 + TRUE AS "featured", 1.203 + NOT EXISTS ( 1.204 + SELECT NULL FROM "initiative" AS "better_initiative" 1.205 + WHERE 1.206 + ("better_initiative"."harmonic_weight", -"better_initiative"."id") > 1.207 + ("initiative"."harmonic_weight", -"better_initiative"."id") 1.208 + ) AS "leading", 1.209 + "initiative".* 1.210 + FROM "member" CROSS JOIN "area" 1.211 + CROSS JOIN LATERAL 1.212 + "featured_initiative"("member"."id", "area"."id") AS "initiative"; 1.213 + 1.214 +CREATE VIEW "leading_complement_initiative" AS 1.215 + SELECT * FROM ( 1.216 + SELECT DISTINCT ON ("seen_by_member_id", "initiative"."issue_id") 1.217 + "updated_or_featured_initiative"."seen_by_member_id", 1.218 + FALSE AS "supported", 1.219 + NULL::BOOLEAN AS "new_draft", 1.220 + NULL::INTEGER AS "new_suggestion_count", 1.221 + FALSE AS "featured", 1.222 + TRUE AS "leading", 1.223 + "initiative".* 1.224 + FROM "updated_or_featured_initiative" 1.225 + JOIN "initiative" 1.226 + ON "updated_or_featured_initiative"."issue_id" = "initiative"."issue_id" 1.227 + ORDER BY 1.228 + "seen_by_member_id", 1.229 + "initiative"."issue_id", 1.230 + "initiative"."harmonic_weight" DESC, 1.231 + "initiative"."id" 1.232 + ) AS "subquery" 1.233 + WHERE NOT EXISTS ( 1.234 + SELECT NULL FROM "updated_or_featured_initiative" AS "other" 1.235 + WHERE "other"."seen_by_member_id" = "subquery"."seen_by_member_id" 1.236 + AND "other"."id" = "subquery"."id" 1.237 + ); 1.238 + 1.239 +CREATE VIEW "initiative_for_notification" AS 1.240 + SELECT * FROM "updated_or_featured_initiative" 1.241 + UNION ALL 1.242 + SELECT * FROM "leading_complement_initiative"; 1.243 + 1.244 +COMMIT;