liquid_feedback_core

annotate update/core-update.beta26-v1.0.0.sql @ 96:07e6a4f11b5b

find changesets by author, revision, files, or words in the commit message
Removed unneccessary JOIN in "create_interest_snapshot"(...)

The INSERT INTO "direct_supporter_snapshot" in function "create_interest_snapshot"(...) does not need to check if members are active.
The previous step ensures that the joined table "direct_interest_snapshot" does not contain entries from disabled members.
author jbe
date Mon Dec 06 23:50:32 2010 +0100 (2010-12-06)
parents 5a01d558565b
children
rev   line source
jbe@45 1 BEGIN;
jbe@45 2
jbe@45 3 DROP VIEW "liquid_feedback_version";
jbe@45 4 CREATE VIEW "liquid_feedback_version" AS
jbe@45 5 SELECT * FROM (VALUES ('1.0.0', 1, 0, 0))
jbe@45 6 AS "subquery"("string", "major", "minor", "revision");
jbe@45 7
jbe@45 8 ALTER TABLE "member" ALTER COLUMN "login" DROP NOT NULL;
jbe@45 9
jbe@45 10 ALTER TABLE "member_history" ALTER COLUMN "login" DROP NOT NULL;
jbe@45 11
jbe@45 12 CREATE INDEX "member_history_member_id_idx" ON "member_history" ("member_id");
jbe@45 13
jbe@45 14 ALTER TABLE "direct_population_snapshot" DROP
jbe@45 15 CONSTRAINT "direct_population_snapshot_member_id_fkey";
jbe@45 16 ALTER TABLE "direct_population_snapshot" ADD
jbe@45 17 CONSTRAINT "direct_population_snapshot_member_id_fkey"
jbe@45 18 FOREIGN KEY ("member_id")
jbe@45 19 REFERENCES "member"("id") ON DELETE RESTRICT ON UPDATE RESTRICT;
jbe@45 20
jbe@45 21 ALTER TABLE "delegating_population_snapshot" DROP
jbe@45 22 CONSTRAINT "delegating_population_snapshot_member_id_fkey";
jbe@45 23 ALTER TABLE "delegating_population_snapshot" ADD
jbe@45 24 CONSTRAINT "delegating_population_snapshot_member_id_fkey"
jbe@45 25 FOREIGN KEY ("member_id")
jbe@45 26 REFERENCES "member"("id") ON DELETE RESTRICT ON UPDATE RESTRICT;
jbe@45 27
jbe@45 28 ALTER TABLE "direct_interest_snapshot" DROP
jbe@45 29 CONSTRAINT "direct_interest_snapshot_member_id_fkey";
jbe@45 30 ALTER TABLE "direct_interest_snapshot" ADD
jbe@45 31 CONSTRAINT "direct_interest_snapshot_member_id_fkey"
jbe@45 32 FOREIGN KEY ("member_id")
jbe@45 33 REFERENCES "member"("id") ON DELETE RESTRICT ON UPDATE RESTRICT;
jbe@45 34
jbe@45 35 ALTER TABLE "delegating_interest_snapshot" DROP
jbe@45 36 CONSTRAINT "delegating_interest_snapshot_member_id_fkey";
jbe@45 37 ALTER TABLE "delegating_interest_snapshot" ADD
jbe@45 38 CONSTRAINT "delegating_interest_snapshot_member_id_fkey"
jbe@45 39 FOREIGN KEY ("member_id")
jbe@45 40 REFERENCES "member"("id") ON DELETE RESTRICT ON UPDATE RESTRICT;
jbe@45 41
jbe@45 42 ALTER TABLE "direct_supporter_snapshot" DROP
jbe@45 43 CONSTRAINT "direct_supporter_snapshot_member_id_fkey";
jbe@45 44 ALTER TABLE "direct_supporter_snapshot" ADD
jbe@45 45 CONSTRAINT "direct_supporter_snapshot_member_id_fkey"
jbe@45 46 FOREIGN KEY ("member_id")
jbe@45 47 REFERENCES "member"("id") ON DELETE RESTRICT ON UPDATE RESTRICT;
jbe@45 48
jbe@45 49 ALTER TABLE "direct_voter" DROP
jbe@45 50 CONSTRAINT "direct_voter_member_id_fkey";
jbe@45 51 ALTER TABLE "direct_voter" ADD
jbe@45 52 CONSTRAINT "direct_voter_member_id_fkey"
jbe@45 53 FOREIGN KEY ("member_id")
jbe@45 54 REFERENCES "member"("id") ON DELETE RESTRICT ON UPDATE RESTRICT;
jbe@45 55
jbe@45 56 ALTER TABLE "delegating_voter" DROP
jbe@45 57 CONSTRAINT "delegating_voter_member_id_fkey";
jbe@45 58 ALTER TABLE "delegating_voter" ADD
jbe@45 59 CONSTRAINT "delegating_voter_member_id_fkey"
jbe@45 60 FOREIGN KEY ("member_id")
jbe@45 61 REFERENCES "member"("id") ON DELETE RESTRICT ON UPDATE RESTRICT;
jbe@45 62
jbe@45 63 CREATE OR REPLACE FUNCTION "write_member_history_trigger"()
jbe@45 64 RETURNS TRIGGER
jbe@45 65 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@45 66 BEGIN
jbe@45 67 IF
jbe@45 68 ( NEW."login" NOTNULL AND OLD."login" NOTNULL AND
jbe@45 69 NEW."login" != OLD."login" ) OR
jbe@45 70 ( NEW."login" NOTNULL AND OLD."login" ISNULL ) OR
jbe@45 71 ( NEW."login" ISNULL AND OLD."login" NOTNULL ) OR
jbe@45 72 NEW."active" != OLD."active" OR
jbe@45 73 NEW."name" != OLD."name"
jbe@45 74 THEN
jbe@45 75 INSERT INTO "member_history"
jbe@45 76 ("member_id", "login", "active", "name")
jbe@45 77 VALUES (NEW."id", OLD."login", OLD."active", OLD."name");
jbe@45 78 END IF;
jbe@45 79 RETURN NULL;
jbe@45 80 END;
jbe@45 81 $$;
jbe@45 82
jbe@45 83 CREATE FUNCTION "delete_member_data"("member_id_p" "member"."id"%TYPE)
jbe@45 84 RETURNS VOID
jbe@45 85 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@45 86 BEGIN
jbe@45 87 UPDATE "member" SET
jbe@45 88 "login" = NULL,
jbe@45 89 "password" = NULL,
jbe@45 90 "notify_email" = NULL,
jbe@45 91 "notify_email_unconfirmed" = NULL,
jbe@45 92 "notify_email_secret" = NULL,
jbe@45 93 "notify_email_secret_expiry" = NULL,
jbe@45 94 "password_reset_secret" = NULL,
jbe@45 95 "password_reset_secret_expiry" = NULL,
jbe@45 96 "organizational_unit" = NULL,
jbe@45 97 "internal_posts" = NULL,
jbe@45 98 "realname" = NULL,
jbe@45 99 "birthday" = NULL,
jbe@45 100 "address" = NULL,
jbe@45 101 "email" = NULL,
jbe@45 102 "xmpp_address" = NULL,
jbe@45 103 "website" = NULL,
jbe@45 104 "phone" = NULL,
jbe@45 105 "mobile_phone" = NULL,
jbe@45 106 "profession" = NULL,
jbe@45 107 "external_memberships" = NULL,
jbe@45 108 "external_posts" = NULL,
jbe@45 109 "statement" = NULL
jbe@45 110 WHERE "id" = "member_id_p";
jbe@45 111 -- "text_search_data" is updated by triggers
jbe@45 112 UPDATE "member_history" SET "login" = NULL
jbe@45 113 WHERE "member_id" = "member_id_p";
jbe@45 114 DELETE FROM "setting" WHERE "member_id" = "member_id_p";
jbe@45 115 DELETE FROM "setting_map" WHERE "member_id" = "member_id_p";
jbe@45 116 DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p";
jbe@45 117 DELETE FROM "member_image" WHERE "member_id" = "member_id_p";
jbe@45 118 DELETE FROM "contact" WHERE "member_id" = "member_id_p";
jbe@45 119 DELETE FROM "area_setting" WHERE "member_id" = "member_id_p";
jbe@45 120 DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p";
jbe@45 121 DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p";
jbe@45 122 DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p";
jbe@45 123 RETURN;
jbe@45 124 END;
jbe@45 125 $$;
jbe@45 126 COMMENT ON FUNCTION "delete_member_data"("member_id_p" "member"."id"%TYPE) IS 'Clear certain settings and data of a particular member (data protection)';
jbe@45 127
jbe@45 128 CREATE OR REPLACE FUNCTION "delete_private_data"()
jbe@45 129 RETURNS VOID
jbe@45 130 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@45 131 BEGIN
jbe@45 132 PERFORM "delete_member_data"("id") FROM "member";
jbe@45 133 DELETE FROM "invite_code";
jbe@45 134 DELETE FROM "session";
jbe@45 135 DELETE FROM "direct_voter" USING "issue"
jbe@45 136 WHERE "direct_voter"."issue_id" = "issue"."id"
jbe@45 137 AND "issue"."closed" ISNULL;
jbe@45 138 RETURN;
jbe@45 139 END;
jbe@45 140 $$;
jbe@45 141
jbe@45 142 COMMIT;

Impressum / About Us