jbe@478: BEGIN;
jbe@478: 
jbe@478: CREATE OR REPLACE VIEW "liquid_feedback_version" AS
jbe@478:   SELECT * FROM (VALUES ('3.2.0', 3, 2, 0))
jbe@478:   AS "subquery"("string", "major", "minor", "revision");
jbe@478: 
jbe@510: ALTER TABLE "member" ADD COLUMN "disable_notifications"    BOOLEAN NOT NULL DEFAULT FALSE;
jbe@510: ALTER TABLE "member" ADD COLUMN "notification_counter"     INT4    NOT NULL DEFAULT 1;
jbe@510: ALTER TABLE "member" ADD COLUMN "notification_sample_size" INT4    NOT NULL DEFAULT 3;
jbe@510: ALTER TABLE "member" ADD COLUMN "notification_dow"         INT4    CHECK ("notification_dow" BETWEEN 0 AND 6);
jbe@510: ALTER TABLE "member" ADD COLUMN "notification_hour"        INT4    CHECK ("notification_hour" BETWEEN 0 AND 23);
jbe@510: ALTER TABLE "member" ADD COLUMN "notification_sent"        TIMESTAMP;
jbe@510: ALTER TABLE "member" ADD
jbe@510:   CONSTRAINT "notification_dow_requires_notification_hour"
jbe@510:   CHECK ("notification_dow" ISNULL OR "notification_hour" NOTNULL);
jbe@499: 
jbe@510: UPDATE "member" SET "disable_notifications" = TRUE WHERE "notify_level" = 'none'::"notify_level";
jbe@478: 
jbe@512: DROP VIEW "selected_event_seen_by_member";
jbe@512: DROP VIEW "event_seen_by_member";
jbe@512: 
jbe@510: ALTER TABLE "member" DROP COLUMN "notify_level";
jbe@499: 
jbe@499: DROP TYPE "notify_level";
jbe@510: 
jbe@510: COMMENT ON COLUMN "member"."disable_notifications"    IS 'TRUE if member does not want to receive notifications';
jbe@510: COMMENT ON COLUMN "member"."notification_counter"     IS 'Sequential number of next scheduled notification message (used as a seed for pseudo-random initiative selection algorithm)';
jbe@510: COMMENT ON COLUMN "member"."notification_sample_size" IS 'Number of featured initiatives per issue in scheduled notification messages';
jbe@510: COMMENT ON COLUMN "member"."notification_dow"         IS 'Day of week for scheduled notifications (NULL to receive a daily digest)';
jbe@510: COMMENT ON COLUMN "member"."notification_hour"        IS 'Time of day when scheduled notifications are sent out';
jbe@510: COMMENT ON COLUMN "member"."notification_sent"        IS 'Timestamp of last scheduled notification mail that has been sent out';
jbe@510: 
jbe@510: ALTER TABLE "rendered_member_statement" ALTER COLUMN "member_id" SET DATA TYPE INT4;
jbe@510: ALTER TABLE "session" ALTER COLUMN "member_id" SET DATA TYPE INT4;
jbe@510: 
jbe@478: CREATE TABLE "subscription" (
jbe@478:         PRIMARY KEY ("member_id", "unit_id"),
jbe@478:         "member_id"             INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@478:         "unit_id"               INT4            REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
jbe@478: CREATE INDEX "subscription_unit_id_idx" ON "subscription" ("unit_id");
jbe@492: 
jbe@492: COMMENT ON TABLE "subscription" IS 'An entry in this table denotes that the member wishes to receive notifications regardless of his/her privileges in the given unit';
jbe@492: 
jbe@492: CREATE TABLE "ignored_area" (
jbe@492:         PRIMARY KEY ("member_id", "area_id"),
jbe@492:         "member_id"             INT4            REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@492:         "area_id"               INT4            REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
jbe@492: CREATE INDEX "ignored_area_area_id_idx" ON "ignored_area" ("area_id");
jbe@492: 
jbe@492: COMMENT ON TABLE "ignored_area" IS 'An entry in this table denotes that the member does not wish to receive notifications for the given subject area unless he/she declared interested in a particular issue';
jbe@492: 
jbe@510: ALTER TABLE "ignored_initiative" DROP CONSTRAINT "ignored_initiative_pkey";
jbe@510: DROP INDEX "ignored_initiative_member_id_idx";
jbe@510: 
jbe@510: ALTER TABLE "ignored_initiative" ADD PRIMARY KEY ("member_id", "initiative_id");
jbe@510: CREATE INDEX "ignored_initiative_initiative_id_idx" ON "ignored_initiative" ("initiative_id");
jbe@510: 
jbe@510: COMMENT ON TABLE "ignored_initiative" IS 'An entry in this table denotes that the member does not wish to receive notifications for the given initiative';
jbe@510: 
jbe@510: ALTER TABLE "notification_sent" RENAME TO "notification_event_sent";
jbe@510: ALTER INDEX "notification_sent_singleton_idx" RENAME TO "notification_event_sent_singleton_idx";
jbe@510: 
jbe@510: CREATE TABLE "notification_initiative_sent" (
jbe@492:         PRIMARY KEY ("member_id", "initiative_id"),
jbe@492:         "member_id"             INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@492:         "initiative_id"         INT4            REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@492:         "last_draft_id"         INT8            NOT NULL,
jbe@495:         "last_suggestion_id"    INT8 );
jbe@510: CREATE INDEX "notification_initiative_sent_initiative_idx" ON "notification_initiative_sent" ("initiative_id");
jbe@510: 
jbe@510: COMMENT ON TABLE "notification_initiative_sent" IS 'Information which initiatives have been promoted to a member in a scheduled notification mail';
jbe@510: 
jbe@510: COMMENT ON COLUMN "notification_initiative_sent"."last_draft_id"      IS 'Current (i.e. last) draft_id when initiative had been promoted';
jbe@510: COMMENT ON COLUMN "notification_initiative_sent"."last_suggestion_id" IS 'Current (i.e. last) draft_id when initiative had been promoted';
jbe@478: 
jbe@496: CREATE TABLE "newsletter" (
jbe@496:         "id"                    SERIAL4         PRIMARY KEY,
jbe@496:         "published"             TIMESTAMPTZ     NOT NULL,
jbe@496:         "unit_id"               INT4            REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@496:         "include_all_members"   BOOLEAN         NOT NULL,
jbe@496:         "sent"                  TIMESTAMPTZ,
jbe@496:         "subject"               TEXT            NOT NULL,
jbe@496:         "content"               TEXT            NOT NULL );
jbe@496: CREATE INDEX "newsletter_unit_id_idx" ON "newsletter" ("unit_id", "published");
jbe@496: CREATE INDEX "newsletter_all_units_published_idx" ON "newsletter" ("published") WHERE "unit_id" ISNULL;
jbe@496: CREATE INDEX "newsletter_published_idx" ON "newsletter" ("published");
jbe@496: 
jbe@510: COMMENT ON TABLE "newsletter" IS 'Contains newsletters created by administrators to be sent out and for further reference';
jbe@510: 
jbe@510: COMMENT ON COLUMN "newsletter"."published"           IS 'Timestamp when the newsletter is to be sent out (and made available in the frontend)';
jbe@510: COMMENT ON COLUMN "newsletter"."unit_id"             IS 'If set, only members with voting right in the given unit are considered to be recipients';
jbe@510: COMMENT ON COLUMN "newsletter"."include_all_members" IS 'TRUE = include all members regardless of their ''disable_notifications'' setting';
jbe@510: COMMENT ON COLUMN "newsletter"."sent"                IS 'Timestamp when the newsletter has been mailed out';
jbe@510: COMMENT ON COLUMN "newsletter"."subject"             IS 'Subject line (e.g. to be used for the email)';
jbe@510: COMMENT ON COLUMN "newsletter"."content"             IS 'Plain text content of the newsletter';
jbe@510: 
jbe@510: CREATE OR REPLACE FUNCTION "issue_requires_first_initiative_trigger"()
jbe@510:   RETURNS TRIGGER
jbe@510:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@510:     BEGIN
jbe@510:       IF NOT EXISTS (
jbe@510:         SELECT NULL FROM "initiative" WHERE "issue_id" = NEW."id"
jbe@510:       ) THEN
jbe@510:         RAISE EXCEPTION 'Cannot create issue without an initial initiative.' USING
jbe@510:           ERRCODE = 'integrity_constraint_violation',
jbe@510:           HINT    = 'Create issue, initiative, and draft within the same transaction.';
jbe@510:       END IF;
jbe@510:       RETURN NULL;
jbe@510:     END;
jbe@510:   $$;
jbe@510: 
jbe@510: CREATE OR REPLACE FUNCTION "initiative_requires_first_draft_trigger"()
jbe@510:   RETURNS TRIGGER
jbe@510:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@510:     BEGIN
jbe@510:       IF NOT EXISTS (
jbe@510:         SELECT NULL FROM "draft" WHERE "initiative_id" = NEW."id"
jbe@510:       ) THEN
jbe@510:         RAISE EXCEPTION 'Cannot create initiative without an initial draft.' USING
jbe@510:           ERRCODE = 'integrity_constraint_violation',
jbe@510:           HINT    = 'Create issue, initiative and draft within the same transaction.';
jbe@510:       END IF;
jbe@510:       RETURN NULL;
jbe@510:     END;
jbe@510:   $$;
jbe@510: 
jbe@510: CREATE OR REPLACE FUNCTION "suggestion_requires_first_opinion_trigger"()
jbe@510:   RETURNS TRIGGER
jbe@510:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@510:     BEGIN
jbe@510:       IF NOT EXISTS (
jbe@510:         SELECT NULL FROM "opinion" WHERE "suggestion_id" = NEW."id"
jbe@510:       ) THEN
jbe@510:         RAISE EXCEPTION 'Cannot create a suggestion without an opinion.' USING
jbe@510:           ERRCODE = 'integrity_constraint_violation',
jbe@510:           HINT    = 'Create suggestion and opinion within the same transaction.';
jbe@510:       END IF;
jbe@510:       RETURN NULL;
jbe@510:     END;
jbe@510:   $$;
jbe@510: 
jbe@510: CREATE OR REPLACE FUNCTION "forbid_changes_on_closed_issue_trigger"()
jbe@510:   RETURNS TRIGGER
jbe@510:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@510:     DECLARE
jbe@510:       "issue_id_v" "issue"."id"%TYPE;
jbe@510:       "issue_row"  "issue"%ROWTYPE;
jbe@510:     BEGIN
jbe@510:       IF EXISTS (
jbe@510:         SELECT NULL FROM "temporary_transaction_data"
jbe@510:         WHERE "txid" = txid_current()
jbe@510:         AND "key" = 'override_protection_triggers'
jbe@510:         AND "value" = TRUE::TEXT
jbe@510:       ) THEN
jbe@510:         RETURN NULL;
jbe@510:       END IF;
jbe@510:       IF TG_OP = 'DELETE' THEN
jbe@510:         "issue_id_v" := OLD."issue_id";
jbe@510:       ELSE
jbe@510:         "issue_id_v" := NEW."issue_id";
jbe@510:       END IF;
jbe@510:       SELECT INTO "issue_row" * FROM "issue"
jbe@510:         WHERE "id" = "issue_id_v" FOR SHARE;
jbe@510:       IF (
jbe@510:         "issue_row"."closed" NOTNULL OR (
jbe@510:           "issue_row"."state" = 'voting' AND
jbe@510:           "issue_row"."phase_finished" NOTNULL
jbe@510:         )
jbe@510:       ) THEN
jbe@510:         IF
jbe@510:           TG_RELID = 'direct_voter'::regclass AND
jbe@510:           TG_OP = 'UPDATE'
jbe@510:         THEN
jbe@510:           IF
jbe@510:             OLD."issue_id"  = NEW."issue_id"  AND
jbe@510:             OLD."member_id" = NEW."member_id" AND
jbe@510:             OLD."weight" = NEW."weight"
jbe@510:           THEN
jbe@510:             RETURN NULL;  -- allows changing of voter comment
jbe@510:           END IF;
jbe@510:         END IF;
jbe@510:         RAISE EXCEPTION 'Tried to modify data after voting has been closed.' USING
jbe@510:           ERRCODE = 'integrity_constraint_violation';
jbe@510:       END IF;
jbe@510:       RETURN NULL;
jbe@510:     END;
jbe@510:   $$;
jbe@510: 
jbe@499: CREATE VIEW "event_for_notification" AS
jbe@499:   SELECT
jbe@499:     "member"."id" AS "recipient_id",
jbe@499:     "event".*
jbe@499:   FROM "member" CROSS JOIN "event"
jbe@499:   JOIN "issue" ON "issue"."id" = "event"."issue_id"
jbe@499:   JOIN "area" ON "area"."id" = "issue"."area_id"
jbe@499:   LEFT JOIN "privilege" ON
jbe@499:     "privilege"."member_id" = "member"."id" AND
jbe@499:     "privilege"."unit_id" = "area"."unit_id" AND
jbe@499:     "privilege"."voting_right" = TRUE
jbe@499:   LEFT JOIN "subscription" ON
jbe@499:     "subscription"."member_id" = "member"."id" AND
jbe@499:     "subscription"."unit_id" = "area"."unit_id"
jbe@499:   LEFT JOIN "ignored_area" ON
jbe@499:     "ignored_area"."member_id" = "member"."id" AND
jbe@499:     "ignored_area"."area_id" = "issue"."area_id"
jbe@499:   LEFT JOIN "interest" ON
jbe@499:     "interest"."member_id" = "member"."id" AND
jbe@499:     "interest"."issue_id" = "event"."issue_id"
jbe@499:   LEFT JOIN "supporter" ON
jbe@499:     "supporter"."member_id" = "member"."id" AND
jbe@499:     "supporter"."initiative_id" = "event"."initiative_id"
jbe@499:   WHERE ("privilege"."member_id" NOTNULL OR "subscription"."member_id" NOTNULL)
jbe@499:   AND ("ignored_area"."member_id" ISNULL OR "interest"."member_id" NOTNULL)
jbe@499:   AND (
jbe@499:     "event"."event" = 'issue_state_changed'::"event_type" OR
jbe@499:     ( "event"."event" = 'initiative_revoked'::"event_type" AND
jbe@499:       "supporter"."member_id" NOTNULL ) );
jbe@499: 
jbe@510: COMMENT ON VIEW "event_for_notification" IS 'Entries of the "event" table which are of interest for a particular notification mail recipient';
jbe@510: 
jbe@510: COMMENT ON COLUMN "event_for_notification"."recipient_id" IS 'member_id of the recipient of a notification mail';
jbe@510: 
jbe@478: CREATE VIEW "updated_initiative" AS
jbe@478:   SELECT
jbe@499:     "supporter"."member_id" AS "recipient_id",
jbe@478:     FALSE AS "featured",
jbe@499:     "supporter"."initiative_id"
jbe@499:   FROM "supporter"
jbe@499:   JOIN "initiative" ON "supporter"."initiative_id" = "initiative"."id"
jbe@478:   JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
jbe@510:   LEFT JOIN "notification_initiative_sent" AS "sent" ON
jbe@499:     "sent"."member_id" = "supporter"."member_id" AND
jbe@499:     "sent"."initiative_id" = "supporter"."initiative_id"
jbe@499:   LEFT JOIN "ignored_initiative" ON
jbe@499:     "ignored_initiative"."member_id" = "supporter"."member_id" AND
jbe@499:     "ignored_initiative"."initiative_id" = "supporter"."initiative_id"
jbe@480:   WHERE "issue"."state" IN ('admission', 'discussion')
jbe@503:   AND "initiative"."revoked" ISNULL
jbe@499:   AND "ignored_initiative"."member_id" ISNULL
jbe@478:   AND (
jbe@478:     EXISTS (
jbe@478:       SELECT NULL FROM "draft"
jbe@499:       LEFT JOIN "ignored_member" ON
jbe@499:         "ignored_member"."member_id" = "supporter"."member_id" AND
jbe@499:         "ignored_member"."other_member_id" = "draft"."author_id"
jbe@499:       WHERE "draft"."initiative_id" = "supporter"."initiative_id"
jbe@478:       AND "draft"."id" > "supporter"."draft_id"
jbe@499:       AND "ignored_member"."member_id" ISNULL
jbe@478:     ) OR EXISTS (
jbe@478:       SELECT NULL FROM "suggestion"
jbe@492:       LEFT JOIN "opinion" ON
jbe@492:         "opinion"."member_id" = "supporter"."member_id" AND
jbe@492:         "opinion"."suggestion_id" = "suggestion"."id"
jbe@499:       LEFT JOIN "ignored_member" ON
jbe@499:         "ignored_member"."member_id" = "supporter"."member_id" AND
jbe@499:         "ignored_member"."other_member_id" = "suggestion"."author_id"
jbe@499:       WHERE "suggestion"."initiative_id" = "supporter"."initiative_id"
jbe@492:       AND "opinion"."member_id" ISNULL
jbe@499:       AND COALESCE("suggestion"."id" > "sent"."last_suggestion_id", TRUE)
jbe@499:       AND "ignored_member"."member_id" ISNULL
jbe@478:     )
jbe@478:   );
jbe@478: 
jbe@510: COMMENT ON VIEW "updated_initiative" IS 'Helper view for view "updated_or_featured_initiative"';
jbe@510: 
jbe@478: CREATE FUNCTION "featured_initiative"
jbe@499:   ( "recipient_id_p" "member"."id"%TYPE,
jbe@499:     "area_id_p"      "area"."id"%TYPE )
jbe@499:   RETURNS SETOF "initiative"."id"%TYPE
jbe@478:   LANGUAGE 'plpgsql' STABLE AS $$
jbe@478:     DECLARE
jbe@499:       "counter_v"         "member"."notification_counter"%TYPE;
jbe@499:       "sample_size_v"     "member"."notification_sample_size"%TYPE;
jbe@499:       "initiative_id_ary" INT4[];  --"initiative"."id"%TYPE[]
jbe@499:       "match_v"           BOOLEAN;
jbe@478:       "member_id_v"       "member"."id"%TYPE;
jbe@478:       "seed_v"            TEXT;
jbe@499:       "initiative_id_v"   "initiative"."id"%TYPE;
jbe@478:     BEGIN
jbe@499:       SELECT "notification_counter", "notification_sample_size"
jbe@499:         INTO "counter_v", "sample_size_v"
jbe@499:         FROM "member" WHERE "id" = "recipient_id_p";
jbe@478:       "initiative_id_ary" := '{}';
jbe@478:       LOOP
jbe@478:         "match_v" := FALSE;
jbe@478:         FOR "member_id_v", "seed_v" IN
jbe@478:           SELECT * FROM (
jbe@478:             SELECT DISTINCT
jbe@478:               "supporter"."member_id",
jbe@499:               md5(
jbe@499:                 "recipient_id_p" || '-' ||
jbe@499:                 "counter_v"      || '-' ||
jbe@499:                 "area_id_p"      || '-' ||
jbe@499:                 "supporter"."member_id"
jbe@499:               ) AS "seed"
jbe@478:             FROM "supporter"
jbe@478:             JOIN "initiative" ON "initiative"."id" = "supporter"."initiative_id"
jbe@478:             JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
jbe@499:             WHERE "supporter"."member_id" != "recipient_id_p"
jbe@478:             AND "issue"."area_id" = "area_id_p"
jbe@478:             AND "issue"."state" IN ('admission', 'discussion', 'verification')
jbe@478:           ) AS "subquery"
jbe@478:           ORDER BY "seed"
jbe@478:         LOOP
jbe@499:           SELECT "initiative"."id" INTO "initiative_id_v"
jbe@478:             FROM "initiative"
jbe@478:             JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
jbe@499:             JOIN "area" ON "area"."id" = "issue"."area_id"
jbe@478:             JOIN "supporter" ON "supporter"."initiative_id" = "initiative"."id"
jbe@478:             LEFT JOIN "supporter" AS "self_support" ON
jbe@478:               "self_support"."initiative_id" = "initiative"."id" AND
jbe@499:               "self_support"."member_id" = "recipient_id_p"
jbe@499:             LEFT JOIN "privilege" ON
jbe@499:               "privilege"."member_id" = "recipient_id_p" AND
jbe@499:               "privilege"."unit_id" = "area"."unit_id" AND
jbe@499:               "privilege"."voting_right" = TRUE
jbe@499:             LEFT JOIN "subscription" ON
jbe@499:               "subscription"."member_id" = "recipient_id_p" AND
jbe@499:               "subscription"."unit_id" = "area"."unit_id"
jbe@499:             LEFT JOIN "ignored_initiative" ON
jbe@499:               "ignored_initiative"."member_id" = "recipient_id_p" AND
jbe@499:               "ignored_initiative"."initiative_id" = "initiative"."id"
jbe@478:             WHERE "supporter"."member_id" = "member_id_v"
jbe@478:             AND "issue"."area_id" = "area_id_p"
jbe@478:             AND "issue"."state" IN ('admission', 'discussion', 'verification')
jbe@503:             AND "initiative"."revoked" ISNULL
jbe@478:             AND "self_support"."member_id" ISNULL
jbe@478:             AND NOT "initiative_id_ary" @> ARRAY["initiative"."id"]
jbe@499:             AND (
jbe@499:               "privilege"."member_id" NOTNULL OR
jbe@499:               "subscription"."member_id" NOTNULL )
jbe@499:             AND "ignored_initiative"."member_id" ISNULL
jbe@499:             AND NOT EXISTS (
jbe@499:               SELECT NULL FROM "draft"
jbe@499:               JOIN "ignored_member" ON
jbe@499:                 "ignored_member"."member_id" = "recipient_id_p" AND
jbe@499:                 "ignored_member"."other_member_id" = "draft"."author_id"
jbe@499:               WHERE "draft"."initiative_id" = "initiative"."id"
jbe@499:             )
jbe@478:             ORDER BY md5("seed_v" || '-' || "initiative"."id")
jbe@478:             LIMIT 1;
jbe@478:           IF FOUND THEN
jbe@478:             "match_v" := TRUE;
jbe@499:             RETURN NEXT "initiative_id_v";
jbe@499:             IF array_length("initiative_id_ary", 1) + 1 >= "sample_size_v" THEN
jbe@478:               RETURN;
jbe@478:             END IF;
jbe@499:             "initiative_id_ary" := "initiative_id_ary" || "initiative_id_v";
jbe@478:           END IF;
jbe@478:         END LOOP;
jbe@478:         EXIT WHEN NOT "match_v";
jbe@478:       END LOOP;
jbe@478:       RETURN;
jbe@478:     END;
jbe@478:   $$;
jbe@478: 
jbe@510: COMMENT ON FUNCTION "featured_initiative"
jbe@510:   ( "recipient_id_p" "member"."id"%TYPE,
jbe@510:     "area_id_p"      "area"."id"%TYPE )
jbe@510:   IS 'Helper function for view "updated_or_featured_initiative"';
jbe@510: 
jbe@478: CREATE VIEW "updated_or_featured_initiative" AS
jbe@478:   SELECT
jbe@499:     "subquery".*,
jbe@478:     NOT EXISTS (
jbe@478:       SELECT NULL FROM "initiative" AS "better_initiative"
jbe@499:       WHERE "better_initiative"."issue_id" = "initiative"."issue_id"
jbe@484:       AND
jbe@502:         ( COALESCE("better_initiative"."supporter_count", -1),
jbe@484:           -"better_initiative"."id" ) >
jbe@502:         ( COALESCE("initiative"."supporter_count", -1),
jbe@485:           -"initiative"."id" )
jbe@499:     ) AS "leading"
jbe@499:   FROM (
jbe@499:     SELECT * FROM "updated_initiative"
jbe@499:     UNION ALL
jbe@499:     SELECT
jbe@499:       "member"."id" AS "recipient_id",
jbe@499:       TRUE AS "featured",
jbe@499:       "featured_initiative_id" AS "initiative_id"
jbe@499:     FROM "member" CROSS JOIN "area"
jbe@499:     CROSS JOIN LATERAL
jbe@499:       "featured_initiative"("member"."id", "area"."id") AS "featured_initiative_id"
jbe@499:     JOIN "initiative" ON "initiative"."id" = "featured_initiative_id"
jbe@499:   ) AS "subquery"
jbe@499:   JOIN "initiative" ON "initiative"."id" = "subquery"."initiative_id";
jbe@478: 
jbe@510: COMMENT ON VIEW "updated_or_featured_initiative" IS 'Initiatives to be included in a scheduled notification mail because (a) they have been updated or (b) they are featured';
jbe@510: 
jbe@510: COMMENT ON COLUMN "updated_or_featured_initiative"."recipient_id" IS '"id" of the member who receives the notification mail';
jbe@510: COMMENT ON COLUMN "updated_or_featured_initiative"."featured" IS 'TRUE if the initiative has been included because it was selected by the "featured_initiative" algorithm (see source of function "featured_initiative")';
jbe@510: COMMENT ON COLUMN "updated_or_featured_initiative"."initiative_id" IS '"id" of the initiative to be included in the notification mail';
jbe@510: COMMENT ON COLUMN "updated_or_featured_initiative"."leading" IS 'TRUE if the initiative has the highest "supporter_count" in the issue';
jbe@510: 
jbe@478: CREATE VIEW "leading_complement_initiative" AS
jbe@478:   SELECT * FROM (
jbe@499:     SELECT DISTINCT ON ("uf_initiative"."recipient_id", "initiative"."issue_id")
jbe@499:       "uf_initiative"."recipient_id",
jbe@478:       FALSE AS "featured",
jbe@499:       "uf_initiative"."initiative_id",
jbe@499:       TRUE AS "leading"
jbe@492:     FROM "updated_or_featured_initiative" AS "uf_initiative"
jbe@499:     JOIN "initiative" AS "uf_initiative_full" ON
jbe@499:       "uf_initiative_full"."id" = "uf_initiative"."initiative_id"
jbe@492:     JOIN "initiative" ON
jbe@499:       "initiative"."issue_id" = "uf_initiative_full"."issue_id"
jbe@503:     WHERE "initiative"."revoked" ISNULL
jbe@478:     ORDER BY
jbe@499:       "uf_initiative"."recipient_id",
jbe@478:       "initiative"."issue_id",
jbe@502:       "initiative"."supporter_count" DESC,
jbe@478:       "initiative"."id"
jbe@478:   ) AS "subquery"
jbe@478:   WHERE NOT EXISTS (
jbe@478:     SELECT NULL FROM "updated_or_featured_initiative" AS "other"
jbe@499:     WHERE "other"."recipient_id" = "subquery"."recipient_id"
jbe@499:     AND "other"."initiative_id" = "subquery"."initiative_id"
jbe@478:   );
jbe@478: 
jbe@510: COMMENT ON VIEW "leading_complement_initiative" IS 'Helper view for view "unfiltered_initiative_for_notification" in order to always include the most supported initiative of an issue';
jbe@510: COMMENT ON COLUMN "leading_complement_initiative"."featured" IS 'Always FALSE in this view';
jbe@510: COMMENT ON COLUMN "leading_complement_initiative"."initiative_id" IS '"id" of the initiative to be included in the notification mail';
jbe@510: COMMENT ON COLUMN "leading_complement_initiative"."leading" IS 'Always TRUE in this view';
jbe@510: 
jbe@492: CREATE VIEW "unfiltered_initiative_for_notification" AS
jbe@499:   SELECT
jbe@499:     "subquery".*,
jbe@499:     "supporter"."member_id" NOTNULL AS "supported",
jbe@499:     CASE WHEN "supporter"."member_id" NOTNULL THEN
jbe@499:       EXISTS (
jbe@499:         SELECT NULL FROM "draft"
jbe@499:         WHERE "draft"."initiative_id" = "subquery"."initiative_id"
jbe@499:         AND "draft"."id" > "supporter"."draft_id"
jbe@499:       )
jbe@499:     ELSE
jbe@499:       EXISTS (
jbe@499:         SELECT NULL FROM "draft"
jbe@499:         WHERE "draft"."initiative_id" = "subquery"."initiative_id"
jbe@499:         AND COALESCE("draft"."id" > "sent"."last_draft_id", TRUE)
jbe@499:       )
jbe@499:     END AS "new_draft",
jbe@499:     CASE WHEN "supporter"."member_id" NOTNULL THEN
jbe@499:       ( SELECT count(1) FROM "suggestion"
jbe@499:         LEFT JOIN "opinion" ON
jbe@499:           "opinion"."member_id" = "supporter"."member_id" AND
jbe@499:           "opinion"."suggestion_id" = "suggestion"."id"
jbe@499:         WHERE "suggestion"."initiative_id" = "subquery"."initiative_id"
jbe@499:         AND "opinion"."member_id" ISNULL
jbe@499:         AND COALESCE("suggestion"."id" > "sent"."last_suggestion_id", TRUE)
jbe@499:       )
jbe@499:     ELSE
jbe@499:       ( SELECT count(1) FROM "suggestion"
jbe@499:         WHERE "suggestion"."initiative_id" = "subquery"."initiative_id"
jbe@499:         AND COALESCE("suggestion"."id" > "sent"."last_suggestion_id", TRUE)
jbe@499:       )
jbe@499:     END AS "new_suggestion_count"
jbe@499:   FROM (
jbe@499:     SELECT * FROM "updated_or_featured_initiative"
jbe@499:     UNION ALL
jbe@499:     SELECT * FROM "leading_complement_initiative"
jbe@499:   ) AS "subquery"
jbe@499:   LEFT JOIN "supporter" ON
jbe@499:     "supporter"."member_id" = "subquery"."recipient_id" AND
jbe@499:     "supporter"."initiative_id" = "subquery"."initiative_id"
jbe@510:   LEFT JOIN "notification_initiative_sent" AS "sent" ON
jbe@499:     "sent"."member_id" = "subquery"."recipient_id" AND
jbe@499:     "sent"."initiative_id" = "subquery"."initiative_id";
jbe@478: 
jbe@510: COMMENT ON VIEW "unfiltered_initiative_for_notification" IS 'Helper view which simply combines the views "updated_or_featured_initiative" and "leading_complement_initiative" and adds columns "supported", "new_draft", and "new_suggestion_count';
jbe@510: 
jbe@510: COMMENT ON COLUMN "unfiltered_initiative_for_notification"."supported"            IS 'TRUE if initiative is supported by the recipient';
jbe@510: COMMENT ON COLUMN "unfiltered_initiative_for_notification"."new_draft"            IS 'TRUE if a new draft exists (using the "draft_id" column of the "supporter" table in case of "supported" initiatives and the "last_draft_id" column of the "notification_initiative_sent" table in all other cases)';
jbe@510: COMMENT ON COLUMN "unfiltered_initiative_for_notification"."new_suggestion_count" IS 'Number of new suggestions (using the "last_suggestion_id" column of the "notification_initiative_sent" table while ignoring suggestions with an "opinion")';
jbe@510: 
jbe@492: CREATE VIEW "initiative_for_notification" AS
jbe@499:   SELECT "unfiltered1".*
jbe@499:   FROM "unfiltered_initiative_for_notification" "unfiltered1"
jbe@499:   JOIN "initiative" AS "initiative1" ON
jbe@499:     "initiative1"."id" = "unfiltered1"."initiative_id"
jbe@499:   JOIN "issue" AS "issue1" ON "issue1"."id" = "initiative1"."issue_id"
jbe@492:   WHERE EXISTS (
jbe@492:     SELECT NULL
jbe@499:     FROM "unfiltered_initiative_for_notification" "unfiltered2"
jbe@499:     JOIN "initiative" AS "initiative2" ON
jbe@499:       "initiative2"."id" = "unfiltered2"."initiative_id"
jbe@499:     JOIN "issue" AS "issue2" ON "issue2"."id" = "initiative2"."issue_id"
jbe@499:     WHERE "unfiltered1"."recipient_id" = "unfiltered2"."recipient_id"
jbe@492:     AND "issue1"."area_id" = "issue2"."area_id"
jbe@499:     AND ("unfiltered2"."new_draft" OR "unfiltered2"."new_suggestion_count" > 0 )
jbe@492:   );
jbe@492: 
jbe@510: COMMENT ON VIEW "initiative_for_notification" IS 'Initiatives to be included in a scheduled notification mail';
jbe@510: 
jbe@510: COMMENT ON COLUMN "initiative_for_notification"."recipient_id"         IS '"id" of the member who receives the notification mail';
jbe@510: COMMENT ON COLUMN "initiative_for_notification"."featured"             IS 'TRUE if the initiative has been included because it was selected by the "featured_initiative" algorithm (see source of function "featured_initiative")';
jbe@510: COMMENT ON COLUMN "initiative_for_notification"."initiative_id"        IS '"id" of the initiative to be included in the notification mail';
jbe@510: COMMENT ON COLUMN "initiative_for_notification"."leading"              IS 'TRUE if the initiative has the highest "supporter_count" in the issue';
jbe@510: COMMENT ON COLUMN "initiative_for_notification"."supported"            IS 'TRUE if initiative is supported by the recipient';
jbe@510: COMMENT ON COLUMN "initiative_for_notification"."new_draft"            IS 'TRUE if a new draft exists (using the "draft_id" column of the "supporter" table in case of "supported" initiatives and the "last_draft_id" column of the "notification_initiative_sent" table in all other cases)';
jbe@510: COMMENT ON COLUMN "initiative_for_notification"."new_suggestion_count" IS 'Number of new suggestions (using the "last_suggestion_id" column of the "notification_initiative_sent" table while ignoring suggestions with an "opinion")';
jbe@510: 
jbe@504: CREATE VIEW "scheduled_notification_to_send" AS
jbe@505:   SELECT * FROM (
jbe@505:     SELECT
jbe@505:       "id" AS "recipient_id",
jbe@505:       now() - CASE WHEN "notification_dow" ISNULL THEN
jbe@505:         ( "notification_sent"::DATE + CASE
jbe@505:           WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour"
jbe@505:           THEN 0 ELSE 1 END
jbe@505:         )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour"
jbe@505:       ELSE
jbe@505:         ( "notification_sent"::DATE +
jbe@505:           ( 7 + "notification_dow" -
jbe@505:             EXTRACT(DOW FROM
jbe@505:               ( "notification_sent"::DATE + CASE
jbe@505:                 WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour"
jbe@505:                 THEN 0 ELSE 1 END
jbe@505:               )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour"
jbe@505:             )::INTEGER
jbe@505:           ) % 7 +
jbe@505:           CASE
jbe@505:             WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour"
jbe@505:             THEN 0 ELSE 1
jbe@505:           END
jbe@505:         )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour"
jbe@505:       END AS "pending"
jbe@505:     FROM (
jbe@505:       SELECT
jbe@505:         "id",
jbe@505:         COALESCE("notification_sent", "activated") AS "notification_sent",
jbe@505:         "notification_dow",
jbe@505:         "notification_hour"
jbe@505:       FROM "member"
jbe@505:       WHERE "disable_notifications" = FALSE
jbe@505:       AND "notification_hour" NOTNULL
jbe@505:     ) AS "subquery1"
jbe@505:   ) AS "subquery2"
jbe@505:   WHERE "pending" > '0'::INTERVAL;
jbe@504: 
jbe@510: COMMENT ON VIEW "scheduled_notification_to_send" IS 'Set of members where a scheduled notification mail is pending';
jbe@510: 
jbe@510: COMMENT ON COLUMN "scheduled_notification_to_send"."recipient_id" IS '"id" of the member who needs to receive a notification mail';
jbe@510: COMMENT ON COLUMN "scheduled_notification_to_send"."pending"      IS 'Duration for which the notification mail has already been pending';
jbe@510: 
jbe@497: CREATE VIEW "newsletter_to_send" AS
jbe@497:   SELECT
jbe@499:     "member"."id" AS "recipient_id",
jbe@499:     "newsletter"."id" AS "newsletter_id"
jbe@497:   FROM "newsletter" CROSS JOIN "member"
jbe@497:   LEFT JOIN "privilege" ON
jbe@497:     "privilege"."member_id" = "member"."id" AND
jbe@497:     "privilege"."unit_id" = "newsletter"."unit_id" AND
jbe@497:     "privilege"."voting_right" = TRUE
jbe@497:   LEFT JOIN "subscription" ON
jbe@497:     "subscription"."member_id" = "member"."id" AND
jbe@497:     "subscription"."unit_id" = "newsletter"."unit_id"
jbe@498:   WHERE "newsletter"."published" <= now()
jbe@497:   AND "newsletter"."sent" ISNULL
jbe@497:   AND "member"."locked" = FALSE
jbe@497:   AND (
jbe@497:     "member"."disable_notifications" = FALSE OR
jbe@497:     "newsletter"."include_all_members" = TRUE )
jbe@497:   AND (
jbe@497:     "newsletter"."unit_id" ISNULL OR
jbe@497:     "privilege"."member_id" NOTNULL OR
jbe@497:     "subscription"."member_id" NOTNULL );
jbe@497: 
jbe@510: COMMENT ON VIEW "newsletter_to_send" IS 'List of "newsletter_id"s for each member that are due to be sent out';
jbe@510: 
jbe@510: CREATE OR REPLACE FUNCTION "require_transaction_isolation"()
jbe@510:   RETURNS VOID
jbe@510:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@510:     BEGIN
jbe@510:       IF
jbe@510:         current_setting('transaction_isolation') NOT IN
jbe@510:         ('repeatable read', 'serializable')
jbe@510:       THEN
jbe@510:         RAISE EXCEPTION 'Insufficient transaction isolation level' USING
jbe@510:           HINT = 'Consider using SET TRANSACTION ISOLATION LEVEL REPEATABLE READ.';
jbe@510:       END IF;
jbe@510:       RETURN;
jbe@510:     END;
jbe@510:   $$;
jbe@510:  
jbe@492: CREATE FUNCTION "get_initiatives_for_notification"
jbe@501:   ( "recipient_id_p" "member"."id"%TYPE )
jbe@492:   RETURNS SETOF "initiative_for_notification"
jbe@492:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@492:     DECLARE
jbe@492:       "result_row"           "initiative_for_notification"%ROWTYPE;
jbe@492:       "last_draft_id_v"      "draft"."id"%TYPE;
jbe@492:       "last_suggestion_id_v" "suggestion"."id"%TYPE;
jbe@492:     BEGIN
jbe@492:       PERFORM "require_transaction_isolation"();
jbe@501:       PERFORM NULL FROM "member" WHERE "id" = "recipient_id_p" FOR UPDATE;
jbe@492:       FOR "result_row" IN
jbe@492:         SELECT * FROM "initiative_for_notification"
jbe@501:         WHERE "recipient_id" = "recipient_id_p"
jbe@492:       LOOP
jbe@492:         SELECT "id" INTO "last_draft_id_v" FROM "draft"
jbe@499:           WHERE "draft"."initiative_id" = "result_row"."initiative_id"
jbe@492:           ORDER BY "id" DESC LIMIT 1;
jbe@492:         SELECT "id" INTO "last_suggestion_id_v" FROM "suggestion"
jbe@499:           WHERE "suggestion"."initiative_id" = "result_row"."initiative_id"
jbe@492:           ORDER BY "id" DESC LIMIT 1;
jbe@510:         INSERT INTO "notification_initiative_sent"
jbe@492:           ("member_id", "initiative_id", "last_draft_id", "last_suggestion_id")
jbe@492:           VALUES (
jbe@501:             "recipient_id_p",
jbe@499:             "result_row"."initiative_id",
jbe@493:             "last_draft_id_v",
jbe@493:             "last_suggestion_id_v" )
jbe@492:           ON CONFLICT ("member_id", "initiative_id") DO UPDATE SET
jbe@492:             "last_draft_id" = CASE
jbe@510:               WHEN "notification_initiative_sent"."last_draft_id" > "last_draft_id_v"
jbe@510:               THEN "notification_initiative_sent"."last_draft_id"
jbe@492:               ELSE "last_draft_id_v"
jbe@492:             END,
jbe@492:             "last_suggestion_id" = CASE
jbe@510:               WHEN "notification_initiative_sent"."last_suggestion_id" > "last_suggestion_id_v"
jbe@510:               THEN "notification_initiative_sent"."last_suggestion_id"
jbe@492:               ELSE "last_suggestion_id_v"
jbe@492:             END;
jbe@492:         RETURN NEXT "result_row";
jbe@492:       END LOOP;
jbe@510:       DELETE FROM "notification_initiative_sent"
jbe@492:         USING "initiative", "issue"
jbe@510:         WHERE "notification_initiative_sent"."member_id" = "recipient_id_p"
jbe@510:         AND "initiative"."id" = "notification_initiative_sent"."initiative_id"
jbe@492:         AND "issue"."id" = "initiative"."issue_id"
jbe@492:         AND ( "issue"."closed" NOTNULL OR "issue"."fully_frozen" NOTNULL );
jbe@505:       UPDATE "member" SET
jbe@506:         "notification_counter" = "notification_counter" + 1,
jbe@505:         "notification_sent" = now()
jbe@501:         WHERE "id" = "recipient_id_p";
jbe@492:       RETURN;
jbe@492:     END;
jbe@492:   $$;
jbe@492: 
jbe@511: COMMENT ON FUNCTION "get_initiatives_for_notification"
jbe@511:   ( "member"."id"%TYPE )
jbe@511:   IS 'Returns rows from view "initiative_for_notification" for a given recipient while updating table "notification_initiative_sent" and columns "notification_counter" and "notification_sent" of "member" table';
jbe@510: 
jbe@510: CREATE OR REPLACE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)
jbe@510:   RETURNS VOID
jbe@510:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@510:     BEGIN
jbe@510:       UPDATE "member" SET
jbe@510:         "last_login"                   = NULL,
jbe@510:         "last_delegation_check"        = NULL,
jbe@510:         "login"                        = NULL,
jbe@510:         "password"                     = NULL,
jbe@510:         "authority"                    = NULL,
jbe@510:         "authority_uid"                = NULL,
jbe@510:         "authority_login"              = NULL,
jbe@510:         "locked"                       = TRUE,
jbe@510:         "active"                       = FALSE,
jbe@510:         "notify_email"                 = NULL,
jbe@510:         "notify_email_unconfirmed"     = NULL,
jbe@510:         "notify_email_secret"          = NULL,
jbe@510:         "notify_email_secret_expiry"   = NULL,
jbe@510:         "notify_email_lock_expiry"     = NULL,
jbe@510:         "disable_notifications"        = NULL,
jbe@510:         "notification_counter"         = NULL,
jbe@510:         "notification_sample_size"     = NULL,
jbe@510:         "notification_dow"             = NULL,
jbe@510:         "notification_hour"            = NULL,
jbe@510:         "login_recovery_expiry"        = NULL,
jbe@510:         "password_reset_secret"        = NULL,
jbe@510:         "password_reset_secret_expiry" = NULL,
jbe@510:         "organizational_unit"          = NULL,
jbe@510:         "internal_posts"               = NULL,
jbe@510:         "realname"                     = NULL,
jbe@510:         "birthday"                     = NULL,
jbe@510:         "address"                      = NULL,
jbe@510:         "email"                        = NULL,
jbe@510:         "xmpp_address"                 = NULL,
jbe@510:         "website"                      = NULL,
jbe@510:         "phone"                        = NULL,
jbe@510:         "mobile_phone"                 = NULL,
jbe@510:         "profession"                   = NULL,
jbe@510:         "external_memberships"         = NULL,
jbe@510:         "external_posts"               = NULL,
jbe@510:         "statement"                    = NULL
jbe@510:         WHERE "id" = "member_id_p";
jbe@510:       -- "text_search_data" is updated by triggers
jbe@510:       DELETE FROM "setting"            WHERE "member_id" = "member_id_p";
jbe@510:       DELETE FROM "setting_map"        WHERE "member_id" = "member_id_p";
jbe@510:       DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p";
jbe@510:       DELETE FROM "member_image"       WHERE "member_id" = "member_id_p";
jbe@510:       DELETE FROM "contact"            WHERE "member_id" = "member_id_p";
jbe@510:       DELETE FROM "ignored_member"     WHERE "member_id" = "member_id_p";
jbe@510:       DELETE FROM "session"            WHERE "member_id" = "member_id_p";
jbe@510:       DELETE FROM "area_setting"       WHERE "member_id" = "member_id_p";
jbe@510:       DELETE FROM "issue_setting"      WHERE "member_id" = "member_id_p";
jbe@510:       DELETE FROM "ignored_initiative" WHERE "member_id" = "member_id_p";
jbe@510:       DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p";
jbe@510:       DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p";
jbe@510:       DELETE FROM "membership"         WHERE "member_id" = "member_id_p";
jbe@510:       DELETE FROM "delegation"         WHERE "truster_id" = "member_id_p";
jbe@510:       DELETE FROM "non_voter"          WHERE "member_id" = "member_id_p";
jbe@510:       DELETE FROM "direct_voter" USING "issue"
jbe@510:         WHERE "direct_voter"."issue_id" = "issue"."id"
jbe@510:         AND "issue"."closed" ISNULL
jbe@510:         AND "member_id" = "member_id_p";
jbe@510:       RETURN;
jbe@510:     END;
jbe@510:   $$;
jbe@510: 
jbe@510: CREATE OR REPLACE FUNCTION "delete_private_data"()
jbe@510:   RETURNS VOID
jbe@510:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@510:     BEGIN
jbe@510:       DELETE FROM "temporary_transaction_data";
jbe@510:       DELETE FROM "member" WHERE "activated" ISNULL;
jbe@510:       UPDATE "member" SET
jbe@510:         "invite_code"                  = NULL,
jbe@510:         "invite_code_expiry"           = NULL,
jbe@510:         "admin_comment"                = NULL,
jbe@510:         "last_login"                   = NULL,
jbe@510:         "last_delegation_check"        = NULL,
jbe@510:         "login"                        = NULL,
jbe@510:         "password"                     = NULL,
jbe@510:         "authority"                    = NULL,
jbe@510:         "authority_uid"                = NULL,
jbe@510:         "authority_login"              = NULL,
jbe@510:         "lang"                         = NULL,
jbe@510:         "notify_email"                 = NULL,
jbe@510:         "notify_email_unconfirmed"     = NULL,
jbe@510:         "notify_email_secret"          = NULL,
jbe@510:         "notify_email_secret_expiry"   = NULL,
jbe@510:         "notify_email_lock_expiry"     = NULL,
jbe@510:         "disable_notifications"        = NULL,
jbe@510:         "notification_counter"         = NULL,
jbe@510:         "notification_sample_size"     = NULL,
jbe@510:         "notification_dow"             = NULL,
jbe@510:         "notification_hour"            = NULL,
jbe@510:         "login_recovery_expiry"        = NULL,
jbe@510:         "password_reset_secret"        = NULL,
jbe@510:         "password_reset_secret_expiry" = NULL,
jbe@510:         "organizational_unit"          = NULL,
jbe@510:         "internal_posts"               = NULL,
jbe@510:         "realname"                     = NULL,
jbe@510:         "birthday"                     = NULL,
jbe@510:         "address"                      = NULL,
jbe@510:         "email"                        = NULL,
jbe@510:         "xmpp_address"                 = NULL,
jbe@510:         "website"                      = NULL,
jbe@510:         "phone"                        = NULL,
jbe@510:         "mobile_phone"                 = NULL,
jbe@510:         "profession"                   = NULL,
jbe@510:         "external_memberships"         = NULL,
jbe@510:         "external_posts"               = NULL,
jbe@510:         "formatting_engine"            = NULL,
jbe@510:         "statement"                    = NULL;
jbe@510:       -- "text_search_data" is updated by triggers
jbe@510:       DELETE FROM "setting";
jbe@510:       DELETE FROM "setting_map";
jbe@510:       DELETE FROM "member_relation_setting";
jbe@510:       DELETE FROM "member_image";
jbe@510:       DELETE FROM "contact";
jbe@510:       DELETE FROM "ignored_member";
jbe@510:       DELETE FROM "session";
jbe@510:       DELETE FROM "area_setting";
jbe@510:       DELETE FROM "issue_setting";
jbe@510:       DELETE FROM "ignored_initiative";
jbe@510:       DELETE FROM "initiative_setting";
jbe@510:       DELETE FROM "suggestion_setting";
jbe@510:       DELETE FROM "non_voter";
jbe@510:       DELETE FROM "direct_voter" USING "issue"
jbe@510:         WHERE "direct_voter"."issue_id" = "issue"."id"
jbe@510:         AND "issue"."closed" ISNULL;
jbe@510:       RETURN;
jbe@510:     END;
jbe@510:   $$;
jbe@510: 
jbe@478: COMMIT;