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;

Impressum / About Us