liquid_feedback_core

changeset 510:fd330c887a83

Updated update script to v3.2.0
author jbe
date Sat Apr 16 19:52:19 2016 +0200 (2016-04-16)
parents 3b684315c724
children 48761b189274
files update/core-update.v3.1.0-v3.2.0.sql
line diff
     1.1 --- a/update/core-update.v3.1.0-v3.2.0.sql	Sat Apr 16 19:41:37 2016 +0200
     1.2 +++ b/update/core-update.v3.1.0-v3.2.0.sql	Sat Apr 16 19:52:19 2016 +0200
     1.3 @@ -4,22 +4,34 @@
     1.4    SELECT * FROM (VALUES ('3.2.0', 3, 2, 0))
     1.5    AS "subquery"("string", "major", "minor", "revision");
     1.6  
     1.7 --- TODO: preliminary script
     1.8 +ALTER TABLE "member" ADD COLUMN "disable_notifications"    BOOLEAN NOT NULL DEFAULT FALSE;
     1.9 +ALTER TABLE "member" ADD COLUMN "notification_counter"     INT4    NOT NULL DEFAULT 1;
    1.10 +ALTER TABLE "member" ADD COLUMN "notification_sample_size" INT4    NOT NULL DEFAULT 3;
    1.11 +ALTER TABLE "member" ADD COLUMN "notification_dow"         INT4    CHECK ("notification_dow" BETWEEN 0 AND 6);
    1.12 +ALTER TABLE "member" ADD COLUMN "notification_hour"        INT4    CHECK ("notification_hour" BETWEEN 0 AND 23);
    1.13 +ALTER TABLE "member" ADD COLUMN "notification_sent"        TIMESTAMP;
    1.14 +ALTER TABLE "member" ADD
    1.15 +  CONSTRAINT "notification_dow_requires_notification_hour"
    1.16 +  CHECK ("notification_dow" ISNULL OR "notification_hour" NOTNULL);
    1.17  
    1.18 -ALTER TABLE "member" ADD COLUMN "disable_notifications" BOOLEAN NOT NULL DEFAULT FALSE;
    1.19 -ALTER TABLE "member" ADD COLUMN "notification_counter" INT4 NOT NULL DEFAULT 1;
    1.20 -ALTER TABLE "member" ADD COLUMN "notification_sample_size" INT4 NOT NULL DEFAULT 3;
    1.21 -ALTER TABLE "member" ADD COLUMN "notification_dow" INT4 CHECK ("notification_dow" BETWEEN 0 AND 6);
    1.22 -ALTER TABLE "member" ADD COLUMN "notification_hour" INT4 CHECK ("notification_hour" BETWEEN 0 AND 23);
    1.23 -ALTER TABLE "member" ADD COLUMN "notification_sent" TIMESTAMP;
    1.24 +UPDATE "member" SET "disable_notifications" = TRUE WHERE "notify_level" = 'none'::"notify_level";
    1.25  
    1.26 -UPDATE "member" SET "disable_notifications" = TRUE WHERE "notify_level" = 'none';
    1.27 +ALTER TABLE "member" DROP COLUMN "notify_level";
    1.28  
    1.29  DROP VIEW "selected_event_seen_by_member";
    1.30  DROP VIEW "event_seen_by_member";
    1.31 -ALTER TABLE "member" DROP COLUMN "notify_level";
    1.32  DROP TYPE "notify_level";
    1.33 - 
    1.34 +
    1.35 +COMMENT ON COLUMN "member"."disable_notifications"    IS 'TRUE if member does not want to receive notifications';
    1.36 +COMMENT ON COLUMN "member"."notification_counter"     IS 'Sequential number of next scheduled notification message (used as a seed for pseudo-random initiative selection algorithm)';
    1.37 +COMMENT ON COLUMN "member"."notification_sample_size" IS 'Number of featured initiatives per issue in scheduled notification messages';
    1.38 +COMMENT ON COLUMN "member"."notification_dow"         IS 'Day of week for scheduled notifications (NULL to receive a daily digest)';
    1.39 +COMMENT ON COLUMN "member"."notification_hour"        IS 'Time of day when scheduled notifications are sent out';
    1.40 +COMMENT ON COLUMN "member"."notification_sent"        IS 'Timestamp of last scheduled notification mail that has been sent out';
    1.41 +
    1.42 +ALTER TABLE "rendered_member_statement" ALTER COLUMN "member_id" SET DATA TYPE INT4;
    1.43 +ALTER TABLE "session" ALTER COLUMN "member_id" SET DATA TYPE INT4;
    1.44 +
    1.45  CREATE TABLE "subscription" (
    1.46          PRIMARY KEY ("member_id", "unit_id"),
    1.47          "member_id"             INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    1.48 @@ -36,13 +48,29 @@
    1.49  
    1.50  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';
    1.51  
    1.52 -CREATE TABLE "initiative_notification_sent" (
    1.53 +ALTER TABLE "ignored_initiative" DROP CONSTRAINT "ignored_initiative_pkey";
    1.54 +DROP INDEX "ignored_initiative_member_id_idx";
    1.55 +
    1.56 +ALTER TABLE "ignored_initiative" ADD PRIMARY KEY ("member_id", "initiative_id");
    1.57 +CREATE INDEX "ignored_initiative_initiative_id_idx" ON "ignored_initiative" ("initiative_id");
    1.58 +
    1.59 +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';
    1.60 +
    1.61 +ALTER TABLE "notification_sent" RENAME TO "notification_event_sent";
    1.62 +ALTER INDEX "notification_sent_singleton_idx" RENAME TO "notification_event_sent_singleton_idx";
    1.63 +
    1.64 +CREATE TABLE "notification_initiative_sent" (
    1.65          PRIMARY KEY ("member_id", "initiative_id"),
    1.66          "member_id"             INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    1.67          "initiative_id"         INT4            REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    1.68          "last_draft_id"         INT8            NOT NULL,
    1.69          "last_suggestion_id"    INT8 );
    1.70 -CREATE INDEX "initiative_notification_sent_initiative_idx" ON "initiative_notification_sent" ("initiative_id");
    1.71 +CREATE INDEX "notification_initiative_sent_initiative_idx" ON "notification_initiative_sent" ("initiative_id");
    1.72 +
    1.73 +COMMENT ON TABLE "notification_initiative_sent" IS 'Information which initiatives have been promoted to a member in a scheduled notification mail';
    1.74 +
    1.75 +COMMENT ON COLUMN "notification_initiative_sent"."last_draft_id"      IS 'Current (i.e. last) draft_id when initiative had been promoted';
    1.76 +COMMENT ON COLUMN "notification_initiative_sent"."last_suggestion_id" IS 'Current (i.e. last) draft_id when initiative had been promoted';
    1.77  
    1.78  CREATE TABLE "newsletter" (
    1.79          "id"                    SERIAL4         PRIMARY KEY,
    1.80 @@ -56,6 +84,107 @@
    1.81  CREATE INDEX "newsletter_all_units_published_idx" ON "newsletter" ("published") WHERE "unit_id" ISNULL;
    1.82  CREATE INDEX "newsletter_published_idx" ON "newsletter" ("published");
    1.83  
    1.84 +COMMENT ON TABLE "newsletter" IS 'Contains newsletters created by administrators to be sent out and for further reference';
    1.85 +
    1.86 +COMMENT ON COLUMN "newsletter"."published"           IS 'Timestamp when the newsletter is to be sent out (and made available in the frontend)';
    1.87 +COMMENT ON COLUMN "newsletter"."unit_id"             IS 'If set, only members with voting right in the given unit are considered to be recipients';
    1.88 +COMMENT ON COLUMN "newsletter"."include_all_members" IS 'TRUE = include all members regardless of their ''disable_notifications'' setting';
    1.89 +COMMENT ON COLUMN "newsletter"."sent"                IS 'Timestamp when the newsletter has been mailed out';
    1.90 +COMMENT ON COLUMN "newsletter"."subject"             IS 'Subject line (e.g. to be used for the email)';
    1.91 +COMMENT ON COLUMN "newsletter"."content"             IS 'Plain text content of the newsletter';
    1.92 +
    1.93 +CREATE OR REPLACE FUNCTION "issue_requires_first_initiative_trigger"()
    1.94 +  RETURNS TRIGGER
    1.95 +  LANGUAGE 'plpgsql' VOLATILE AS $$
    1.96 +    BEGIN
    1.97 +      IF NOT EXISTS (
    1.98 +        SELECT NULL FROM "initiative" WHERE "issue_id" = NEW."id"
    1.99 +      ) THEN
   1.100 +        RAISE EXCEPTION 'Cannot create issue without an initial initiative.' USING
   1.101 +          ERRCODE = 'integrity_constraint_violation',
   1.102 +          HINT    = 'Create issue, initiative, and draft within the same transaction.';
   1.103 +      END IF;
   1.104 +      RETURN NULL;
   1.105 +    END;
   1.106 +  $$;
   1.107 +
   1.108 +CREATE OR REPLACE FUNCTION "initiative_requires_first_draft_trigger"()
   1.109 +  RETURNS TRIGGER
   1.110 +  LANGUAGE 'plpgsql' VOLATILE AS $$
   1.111 +    BEGIN
   1.112 +      IF NOT EXISTS (
   1.113 +        SELECT NULL FROM "draft" WHERE "initiative_id" = NEW."id"
   1.114 +      ) THEN
   1.115 +        RAISE EXCEPTION 'Cannot create initiative without an initial draft.' USING
   1.116 +          ERRCODE = 'integrity_constraint_violation',
   1.117 +          HINT    = 'Create issue, initiative and draft within the same transaction.';
   1.118 +      END IF;
   1.119 +      RETURN NULL;
   1.120 +    END;
   1.121 +  $$;
   1.122 +
   1.123 +CREATE OR REPLACE FUNCTION "suggestion_requires_first_opinion_trigger"()
   1.124 +  RETURNS TRIGGER
   1.125 +  LANGUAGE 'plpgsql' VOLATILE AS $$
   1.126 +    BEGIN
   1.127 +      IF NOT EXISTS (
   1.128 +        SELECT NULL FROM "opinion" WHERE "suggestion_id" = NEW."id"
   1.129 +      ) THEN
   1.130 +        RAISE EXCEPTION 'Cannot create a suggestion without an opinion.' USING
   1.131 +          ERRCODE = 'integrity_constraint_violation',
   1.132 +          HINT    = 'Create suggestion and opinion within the same transaction.';
   1.133 +      END IF;
   1.134 +      RETURN NULL;
   1.135 +    END;
   1.136 +  $$;
   1.137 +
   1.138 +CREATE OR REPLACE FUNCTION "forbid_changes_on_closed_issue_trigger"()
   1.139 +  RETURNS TRIGGER
   1.140 +  LANGUAGE 'plpgsql' VOLATILE AS $$
   1.141 +    DECLARE
   1.142 +      "issue_id_v" "issue"."id"%TYPE;
   1.143 +      "issue_row"  "issue"%ROWTYPE;
   1.144 +    BEGIN
   1.145 +      IF EXISTS (
   1.146 +        SELECT NULL FROM "temporary_transaction_data"
   1.147 +        WHERE "txid" = txid_current()
   1.148 +        AND "key" = 'override_protection_triggers'
   1.149 +        AND "value" = TRUE::TEXT
   1.150 +      ) THEN
   1.151 +        RETURN NULL;
   1.152 +      END IF;
   1.153 +      IF TG_OP = 'DELETE' THEN
   1.154 +        "issue_id_v" := OLD."issue_id";
   1.155 +      ELSE
   1.156 +        "issue_id_v" := NEW."issue_id";
   1.157 +      END IF;
   1.158 +      SELECT INTO "issue_row" * FROM "issue"
   1.159 +        WHERE "id" = "issue_id_v" FOR SHARE;
   1.160 +      IF (
   1.161 +        "issue_row"."closed" NOTNULL OR (
   1.162 +          "issue_row"."state" = 'voting' AND
   1.163 +          "issue_row"."phase_finished" NOTNULL
   1.164 +        )
   1.165 +      ) THEN
   1.166 +        IF
   1.167 +          TG_RELID = 'direct_voter'::regclass AND
   1.168 +          TG_OP = 'UPDATE'
   1.169 +        THEN
   1.170 +          IF
   1.171 +            OLD."issue_id"  = NEW."issue_id"  AND
   1.172 +            OLD."member_id" = NEW."member_id" AND
   1.173 +            OLD."weight" = NEW."weight"
   1.174 +          THEN
   1.175 +            RETURN NULL;  -- allows changing of voter comment
   1.176 +          END IF;
   1.177 +        END IF;
   1.178 +        RAISE EXCEPTION 'Tried to modify data after voting has been closed.' USING
   1.179 +          ERRCODE = 'integrity_constraint_violation';
   1.180 +      END IF;
   1.181 +      RETURN NULL;
   1.182 +    END;
   1.183 +  $$;
   1.184 +
   1.185  CREATE VIEW "event_for_notification" AS
   1.186    SELECT
   1.187      "member"."id" AS "recipient_id",
   1.188 @@ -86,6 +215,10 @@
   1.189      ( "event"."event" = 'initiative_revoked'::"event_type" AND
   1.190        "supporter"."member_id" NOTNULL ) );
   1.191  
   1.192 +COMMENT ON VIEW "event_for_notification" IS 'Entries of the "event" table which are of interest for a particular notification mail recipient';
   1.193 +
   1.194 +COMMENT ON COLUMN "event_for_notification"."recipient_id" IS 'member_id of the recipient of a notification mail';
   1.195 +
   1.196  CREATE VIEW "updated_initiative" AS
   1.197    SELECT
   1.198      "supporter"."member_id" AS "recipient_id",
   1.199 @@ -94,7 +227,7 @@
   1.200    FROM "supporter"
   1.201    JOIN "initiative" ON "supporter"."initiative_id" = "initiative"."id"
   1.202    JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
   1.203 -  LEFT JOIN "initiative_notification_sent" AS "sent" ON
   1.204 +  LEFT JOIN "notification_initiative_sent" AS "sent" ON
   1.205      "sent"."member_id" = "supporter"."member_id" AND
   1.206      "sent"."initiative_id" = "supporter"."initiative_id"
   1.207    LEFT JOIN "ignored_initiative" ON
   1.208 @@ -127,6 +260,8 @@
   1.209      )
   1.210    );
   1.211  
   1.212 +COMMENT ON VIEW "updated_initiative" IS 'Helper view for view "updated_or_featured_initiative"';
   1.213 +
   1.214  CREATE FUNCTION "featured_initiative"
   1.215    ( "recipient_id_p" "member"."id"%TYPE,
   1.216      "area_id_p"      "area"."id"%TYPE )
   1.217 @@ -218,6 +353,11 @@
   1.218      END;
   1.219    $$;
   1.220  
   1.221 +COMMENT ON FUNCTION "featured_initiative"
   1.222 +  ( "recipient_id_p" "member"."id"%TYPE,
   1.223 +    "area_id_p"      "area"."id"%TYPE )
   1.224 +  IS 'Helper function for view "updated_or_featured_initiative"';
   1.225 +
   1.226  CREATE VIEW "updated_or_featured_initiative" AS
   1.227    SELECT
   1.228      "subquery".*,
   1.229 @@ -244,6 +384,13 @@
   1.230    ) AS "subquery"
   1.231    JOIN "initiative" ON "initiative"."id" = "subquery"."initiative_id";
   1.232  
   1.233 +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';
   1.234 +
   1.235 +COMMENT ON COLUMN "updated_or_featured_initiative"."recipient_id" IS '"id" of the member who receives the notification mail';
   1.236 +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")';
   1.237 +COMMENT ON COLUMN "updated_or_featured_initiative"."initiative_id" IS '"id" of the initiative to be included in the notification mail';
   1.238 +COMMENT ON COLUMN "updated_or_featured_initiative"."leading" IS 'TRUE if the initiative has the highest "supporter_count" in the issue';
   1.239 +
   1.240  CREATE VIEW "leading_complement_initiative" AS
   1.241    SELECT * FROM (
   1.242      SELECT DISTINCT ON ("uf_initiative"."recipient_id", "initiative"."issue_id")
   1.243 @@ -269,6 +416,11 @@
   1.244      AND "other"."initiative_id" = "subquery"."initiative_id"
   1.245    );
   1.246  
   1.247 +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';
   1.248 +COMMENT ON COLUMN "leading_complement_initiative"."featured" IS 'Always FALSE in this view';
   1.249 +COMMENT ON COLUMN "leading_complement_initiative"."initiative_id" IS '"id" of the initiative to be included in the notification mail';
   1.250 +COMMENT ON COLUMN "leading_complement_initiative"."leading" IS 'Always TRUE in this view';
   1.251 +
   1.252  CREATE VIEW "unfiltered_initiative_for_notification" AS
   1.253    SELECT
   1.254      "subquery".*,
   1.255 @@ -309,10 +461,16 @@
   1.256    LEFT JOIN "supporter" ON
   1.257      "supporter"."member_id" = "subquery"."recipient_id" AND
   1.258      "supporter"."initiative_id" = "subquery"."initiative_id"
   1.259 -  LEFT JOIN "initiative_notification_sent" AS "sent" ON
   1.260 +  LEFT JOIN "notification_initiative_sent" AS "sent" ON
   1.261      "sent"."member_id" = "subquery"."recipient_id" AND
   1.262      "sent"."initiative_id" = "subquery"."initiative_id";
   1.263  
   1.264 +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';
   1.265 +
   1.266 +COMMENT ON COLUMN "unfiltered_initiative_for_notification"."supported"            IS 'TRUE if initiative is supported by the recipient';
   1.267 +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)';
   1.268 +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")';
   1.269 +
   1.270  CREATE VIEW "initiative_for_notification" AS
   1.271    SELECT "unfiltered1".*
   1.272    FROM "unfiltered_initiative_for_notification" "unfiltered1"
   1.273 @@ -330,6 +488,16 @@
   1.274      AND ("unfiltered2"."new_draft" OR "unfiltered2"."new_suggestion_count" > 0 )
   1.275    );
   1.276  
   1.277 +COMMENT ON VIEW "initiative_for_notification" IS 'Initiatives to be included in a scheduled notification mail';
   1.278 +
   1.279 +COMMENT ON COLUMN "initiative_for_notification"."recipient_id"         IS '"id" of the member who receives the notification mail';
   1.280 +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")';
   1.281 +COMMENT ON COLUMN "initiative_for_notification"."initiative_id"        IS '"id" of the initiative to be included in the notification mail';
   1.282 +COMMENT ON COLUMN "initiative_for_notification"."leading"              IS 'TRUE if the initiative has the highest "supporter_count" in the issue';
   1.283 +COMMENT ON COLUMN "initiative_for_notification"."supported"            IS 'TRUE if initiative is supported by the recipient';
   1.284 +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)';
   1.285 +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")';
   1.286 +
   1.287  CREATE VIEW "scheduled_notification_to_send" AS
   1.288    SELECT * FROM (
   1.289      SELECT
   1.290 @@ -368,6 +536,11 @@
   1.291    ) AS "subquery2"
   1.292    WHERE "pending" > '0'::INTERVAL;
   1.293  
   1.294 +COMMENT ON VIEW "scheduled_notification_to_send" IS 'Set of members where a scheduled notification mail is pending';
   1.295 +
   1.296 +COMMENT ON COLUMN "scheduled_notification_to_send"."recipient_id" IS '"id" of the member who needs to receive a notification mail';
   1.297 +COMMENT ON COLUMN "scheduled_notification_to_send"."pending"      IS 'Duration for which the notification mail has already been pending';
   1.298 +
   1.299  CREATE VIEW "newsletter_to_send" AS
   1.300    SELECT
   1.301      "member"."id" AS "recipient_id",
   1.302 @@ -391,6 +564,23 @@
   1.303      "privilege"."member_id" NOTNULL OR
   1.304      "subscription"."member_id" NOTNULL );
   1.305  
   1.306 +COMMENT ON VIEW "newsletter_to_send" IS 'List of "newsletter_id"s for each member that are due to be sent out';
   1.307 +
   1.308 +CREATE OR REPLACE FUNCTION "require_transaction_isolation"()
   1.309 +  RETURNS VOID
   1.310 +  LANGUAGE 'plpgsql' VOLATILE AS $$
   1.311 +    BEGIN
   1.312 +      IF
   1.313 +        current_setting('transaction_isolation') NOT IN
   1.314 +        ('repeatable read', 'serializable')
   1.315 +      THEN
   1.316 +        RAISE EXCEPTION 'Insufficient transaction isolation level' USING
   1.317 +          HINT = 'Consider using SET TRANSACTION ISOLATION LEVEL REPEATABLE READ.';
   1.318 +      END IF;
   1.319 +      RETURN;
   1.320 +    END;
   1.321 +  $$;
   1.322 + 
   1.323  CREATE FUNCTION "get_initiatives_for_notification"
   1.324    ( "recipient_id_p" "member"."id"%TYPE )
   1.325    RETURNS SETOF "initiative_for_notification"
   1.326 @@ -412,7 +602,7 @@
   1.327          SELECT "id" INTO "last_suggestion_id_v" FROM "suggestion"
   1.328            WHERE "suggestion"."initiative_id" = "result_row"."initiative_id"
   1.329            ORDER BY "id" DESC LIMIT 1;
   1.330 -        INSERT INTO "initiative_notification_sent"
   1.331 +        INSERT INTO "notification_initiative_sent"
   1.332            ("member_id", "initiative_id", "last_draft_id", "last_suggestion_id")
   1.333            VALUES (
   1.334              "recipient_id_p",
   1.335 @@ -421,21 +611,21 @@
   1.336              "last_suggestion_id_v" )
   1.337            ON CONFLICT ("member_id", "initiative_id") DO UPDATE SET
   1.338              "last_draft_id" = CASE
   1.339 -              WHEN "initiative_notification_sent"."last_draft_id" > "last_draft_id_v"
   1.340 -              THEN "initiative_notification_sent"."last_draft_id"
   1.341 +              WHEN "notification_initiative_sent"."last_draft_id" > "last_draft_id_v"
   1.342 +              THEN "notification_initiative_sent"."last_draft_id"
   1.343                ELSE "last_draft_id_v"
   1.344              END,
   1.345              "last_suggestion_id" = CASE
   1.346 -              WHEN "initiative_notification_sent"."last_suggestion_id" > "last_suggestion_id_v"
   1.347 -              THEN "initiative_notification_sent"."last_suggestion_id"
   1.348 +              WHEN "notification_initiative_sent"."last_suggestion_id" > "last_suggestion_id_v"
   1.349 +              THEN "notification_initiative_sent"."last_suggestion_id"
   1.350                ELSE "last_suggestion_id_v"
   1.351              END;
   1.352          RETURN NEXT "result_row";
   1.353        END LOOP;
   1.354 -      DELETE FROM "initiative_notification_sent"
   1.355 +      DELETE FROM "notification_initiative_sent"
   1.356          USING "initiative", "issue"
   1.357 -        WHERE "initiative_notification_sent"."member_id" = "recipient_id_p"
   1.358 -        AND "initiative"."id" = "initiative_notification_sent"."initiative_id"
   1.359 +        WHERE "notification_initiative_sent"."member_id" = "recipient_id_p"
   1.360 +        AND "initiative"."id" = "notification_initiative_sent"."initiative_id"
   1.361          AND "issue"."id" = "initiative"."issue_id"
   1.362          AND ( "issue"."closed" NOTNULL OR "issue"."fully_frozen" NOTNULL );
   1.363        UPDATE "member" SET
   1.364 @@ -446,4 +636,139 @@
   1.365      END;
   1.366    $$;
   1.367  
   1.368 +-- TODO: comment missing
   1.369 +
   1.370 +CREATE OR REPLACE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)
   1.371 +  RETURNS VOID
   1.372 +  LANGUAGE 'plpgsql' VOLATILE AS $$
   1.373 +    BEGIN
   1.374 +      UPDATE "member" SET
   1.375 +        "last_login"                   = NULL,
   1.376 +        "last_delegation_check"        = NULL,
   1.377 +        "login"                        = NULL,
   1.378 +        "password"                     = NULL,
   1.379 +        "authority"                    = NULL,
   1.380 +        "authority_uid"                = NULL,
   1.381 +        "authority_login"              = NULL,
   1.382 +        "locked"                       = TRUE,
   1.383 +        "active"                       = FALSE,
   1.384 +        "notify_email"                 = NULL,
   1.385 +        "notify_email_unconfirmed"     = NULL,
   1.386 +        "notify_email_secret"          = NULL,
   1.387 +        "notify_email_secret_expiry"   = NULL,
   1.388 +        "notify_email_lock_expiry"     = NULL,
   1.389 +        "disable_notifications"        = NULL,
   1.390 +        "notification_counter"         = NULL,
   1.391 +        "notification_sample_size"     = NULL,
   1.392 +        "notification_dow"             = NULL,
   1.393 +        "notification_hour"            = NULL,
   1.394 +        "login_recovery_expiry"        = NULL,
   1.395 +        "password_reset_secret"        = NULL,
   1.396 +        "password_reset_secret_expiry" = NULL,
   1.397 +        "organizational_unit"          = NULL,
   1.398 +        "internal_posts"               = NULL,
   1.399 +        "realname"                     = NULL,
   1.400 +        "birthday"                     = NULL,
   1.401 +        "address"                      = NULL,
   1.402 +        "email"                        = NULL,
   1.403 +        "xmpp_address"                 = NULL,
   1.404 +        "website"                      = NULL,
   1.405 +        "phone"                        = NULL,
   1.406 +        "mobile_phone"                 = NULL,
   1.407 +        "profession"                   = NULL,
   1.408 +        "external_memberships"         = NULL,
   1.409 +        "external_posts"               = NULL,
   1.410 +        "statement"                    = NULL
   1.411 +        WHERE "id" = "member_id_p";
   1.412 +      -- "text_search_data" is updated by triggers
   1.413 +      DELETE FROM "setting"            WHERE "member_id" = "member_id_p";
   1.414 +      DELETE FROM "setting_map"        WHERE "member_id" = "member_id_p";
   1.415 +      DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p";
   1.416 +      DELETE FROM "member_image"       WHERE "member_id" = "member_id_p";
   1.417 +      DELETE FROM "contact"            WHERE "member_id" = "member_id_p";
   1.418 +      DELETE FROM "ignored_member"     WHERE "member_id" = "member_id_p";
   1.419 +      DELETE FROM "session"            WHERE "member_id" = "member_id_p";
   1.420 +      DELETE FROM "area_setting"       WHERE "member_id" = "member_id_p";
   1.421 +      DELETE FROM "issue_setting"      WHERE "member_id" = "member_id_p";
   1.422 +      DELETE FROM "ignored_initiative" WHERE "member_id" = "member_id_p";
   1.423 +      DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p";
   1.424 +      DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p";
   1.425 +      DELETE FROM "membership"         WHERE "member_id" = "member_id_p";
   1.426 +      DELETE FROM "delegation"         WHERE "truster_id" = "member_id_p";
   1.427 +      DELETE FROM "non_voter"          WHERE "member_id" = "member_id_p";
   1.428 +      DELETE FROM "direct_voter" USING "issue"
   1.429 +        WHERE "direct_voter"."issue_id" = "issue"."id"
   1.430 +        AND "issue"."closed" ISNULL
   1.431 +        AND "member_id" = "member_id_p";
   1.432 +      RETURN;
   1.433 +    END;
   1.434 +  $$;
   1.435 +
   1.436 +CREATE OR REPLACE FUNCTION "delete_private_data"()
   1.437 +  RETURNS VOID
   1.438 +  LANGUAGE 'plpgsql' VOLATILE AS $$
   1.439 +    BEGIN
   1.440 +      DELETE FROM "temporary_transaction_data";
   1.441 +      DELETE FROM "member" WHERE "activated" ISNULL;
   1.442 +      UPDATE "member" SET
   1.443 +        "invite_code"                  = NULL,
   1.444 +        "invite_code_expiry"           = NULL,
   1.445 +        "admin_comment"                = NULL,
   1.446 +        "last_login"                   = NULL,
   1.447 +        "last_delegation_check"        = NULL,
   1.448 +        "login"                        = NULL,
   1.449 +        "password"                     = NULL,
   1.450 +        "authority"                    = NULL,
   1.451 +        "authority_uid"                = NULL,
   1.452 +        "authority_login"              = NULL,
   1.453 +        "lang"                         = NULL,
   1.454 +        "notify_email"                 = NULL,
   1.455 +        "notify_email_unconfirmed"     = NULL,
   1.456 +        "notify_email_secret"          = NULL,
   1.457 +        "notify_email_secret_expiry"   = NULL,
   1.458 +        "notify_email_lock_expiry"     = NULL,
   1.459 +        "disable_notifications"        = NULL,
   1.460 +        "notification_counter"         = NULL,
   1.461 +        "notification_sample_size"     = NULL,
   1.462 +        "notification_dow"             = NULL,
   1.463 +        "notification_hour"            = NULL,
   1.464 +        "login_recovery_expiry"        = NULL,
   1.465 +        "password_reset_secret"        = NULL,
   1.466 +        "password_reset_secret_expiry" = NULL,
   1.467 +        "organizational_unit"          = NULL,
   1.468 +        "internal_posts"               = NULL,
   1.469 +        "realname"                     = NULL,
   1.470 +        "birthday"                     = NULL,
   1.471 +        "address"                      = NULL,
   1.472 +        "email"                        = NULL,
   1.473 +        "xmpp_address"                 = NULL,
   1.474 +        "website"                      = NULL,
   1.475 +        "phone"                        = NULL,
   1.476 +        "mobile_phone"                 = NULL,
   1.477 +        "profession"                   = NULL,
   1.478 +        "external_memberships"         = NULL,
   1.479 +        "external_posts"               = NULL,
   1.480 +        "formatting_engine"            = NULL,
   1.481 +        "statement"                    = NULL;
   1.482 +      -- "text_search_data" is updated by triggers
   1.483 +      DELETE FROM "setting";
   1.484 +      DELETE FROM "setting_map";
   1.485 +      DELETE FROM "member_relation_setting";
   1.486 +      DELETE FROM "member_image";
   1.487 +      DELETE FROM "contact";
   1.488 +      DELETE FROM "ignored_member";
   1.489 +      DELETE FROM "session";
   1.490 +      DELETE FROM "area_setting";
   1.491 +      DELETE FROM "issue_setting";
   1.492 +      DELETE FROM "ignored_initiative";
   1.493 +      DELETE FROM "initiative_setting";
   1.494 +      DELETE FROM "suggestion_setting";
   1.495 +      DELETE FROM "non_voter";
   1.496 +      DELETE FROM "direct_voter" USING "issue"
   1.497 +        WHERE "direct_voter"."issue_id" = "issue"."id"
   1.498 +        AND "issue"."closed" ISNULL;
   1.499 +      RETURN;
   1.500 +    END;
   1.501 +  $$;
   1.502 +
   1.503  COMMIT;

Impressum / About Us