liquid_feedback_core

annotate update/core-update.v1.0.3-v1.1.0.sql @ 112:1b1e266df99b

find changesets by author, revision, files, or words in the commit message
Column "revoked_by_member_id"; Implemented event system

- Added column "revoked_by_member_id" to table "initiative"
- Implemented event system (table "event" and triggers)
- Timeline deprecated (replaced by event system)
author jbe
date Fri Mar 04 17:15:33 2011 +0100 (2011-03-04)
parents b63515611a60
children
rev   line source
jbe@54 1 BEGIN;
jbe@54 2
jbe@54 3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
jbe@54 4 SELECT * FROM (VALUES ('1.1.0', 1, 1, 0))
jbe@54 5 AS "subquery"("string", "major", "minor", "revision");
jbe@54 6
jbe@55 7 ALTER TABLE "member" ADD COLUMN "notify_email_lock_expiry" TIMESTAMPTZ;
jbe@55 8 COMMENT ON COLUMN "member"."notify_email_lock_expiry" IS 'Date/time until no further email confirmation mails may be sent (abuse protection)';
jbe@55 9
jbe@54 10 ALTER TABLE "direct_population_snapshot" DROP COLUMN "interest_exists";
jbe@54 11
jbe@54 12 CREATE OR REPLACE FUNCTION "create_population_snapshot"
jbe@54 13 ( "issue_id_p" "issue"."id"%TYPE )
jbe@54 14 RETURNS VOID
jbe@54 15 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@54 16 DECLARE
jbe@54 17 "member_id_v" "member"."id"%TYPE;
jbe@54 18 BEGIN
jbe@54 19 DELETE FROM "direct_population_snapshot"
jbe@54 20 WHERE "issue_id" = "issue_id_p"
jbe@54 21 AND "event" = 'periodic';
jbe@54 22 DELETE FROM "delegating_population_snapshot"
jbe@54 23 WHERE "issue_id" = "issue_id_p"
jbe@54 24 AND "event" = 'periodic';
jbe@54 25 INSERT INTO "direct_population_snapshot"
jbe@54 26 ("issue_id", "event", "member_id")
jbe@54 27 SELECT
jbe@54 28 "issue_id_p" AS "issue_id",
jbe@54 29 'periodic'::"snapshot_event" AS "event",
jbe@54 30 "member"."id" AS "member_id"
jbe@54 31 FROM "issue"
jbe@54 32 JOIN "area" ON "issue"."area_id" = "area"."id"
jbe@54 33 JOIN "membership" ON "area"."id" = "membership"."area_id"
jbe@54 34 JOIN "member" ON "membership"."member_id" = "member"."id"
jbe@54 35 WHERE "issue"."id" = "issue_id_p"
jbe@54 36 AND "member"."active"
jbe@54 37 UNION
jbe@54 38 SELECT
jbe@54 39 "issue_id_p" AS "issue_id",
jbe@54 40 'periodic'::"snapshot_event" AS "event",
jbe@54 41 "member"."id" AS "member_id"
jbe@54 42 FROM "interest" JOIN "member"
jbe@54 43 ON "interest"."member_id" = "member"."id"
jbe@54 44 WHERE "interest"."issue_id" = "issue_id_p"
jbe@54 45 AND "member"."active";
jbe@54 46 FOR "member_id_v" IN
jbe@54 47 SELECT "member_id" FROM "direct_population_snapshot"
jbe@54 48 WHERE "issue_id" = "issue_id_p"
jbe@54 49 AND "event" = 'periodic'
jbe@54 50 LOOP
jbe@54 51 UPDATE "direct_population_snapshot" SET
jbe@54 52 "weight" = 1 +
jbe@54 53 "weight_of_added_delegations_for_population_snapshot"(
jbe@54 54 "issue_id_p",
jbe@54 55 "member_id_v",
jbe@54 56 '{}'
jbe@54 57 )
jbe@54 58 WHERE "issue_id" = "issue_id_p"
jbe@54 59 AND "event" = 'periodic'
jbe@54 60 AND "member_id" = "member_id_v";
jbe@54 61 END LOOP;
jbe@54 62 RETURN;
jbe@54 63 END;
jbe@54 64 $$;
jbe@54 65
jbe@55 66 COMMENT ON FUNCTION "freeze_after_snapshot"
jbe@55 67 ( "issue"."id"%TYPE )
jbe@55 68 IS 'This function freezes an issue (fully) and starts voting, but must only be called when "create_snapshot" was called in the same transaction.';
jbe@55 69
jbe@55 70 COMMENT ON FUNCTION "manual_freeze"
jbe@55 71 ( "issue"."id"%TYPE )
jbe@55 72 IS 'Freeze an issue manually (fully) and start voting';
jbe@55 73
jbe@54 74 DROP FUNCTION "delete_member_data"("member"."id"%TYPE);
jbe@54 75
jbe@54 76 CREATE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)
jbe@54 77 RETURNS VOID
jbe@54 78 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@54 79 BEGIN
jbe@54 80 UPDATE "member" SET
jbe@54 81 "login" = NULL,
jbe@54 82 "password" = NULL,
jbe@54 83 "active" = FALSE,
jbe@54 84 "notify_email" = NULL,
jbe@54 85 "notify_email_unconfirmed" = NULL,
jbe@54 86 "notify_email_secret" = NULL,
jbe@54 87 "notify_email_secret_expiry" = NULL,
jbe@54 88 "password_reset_secret" = NULL,
jbe@54 89 "password_reset_secret_expiry" = NULL,
jbe@54 90 "organizational_unit" = NULL,
jbe@54 91 "internal_posts" = NULL,
jbe@54 92 "realname" = NULL,
jbe@54 93 "birthday" = NULL,
jbe@54 94 "address" = NULL,
jbe@54 95 "email" = NULL,
jbe@54 96 "xmpp_address" = NULL,
jbe@54 97 "website" = NULL,
jbe@54 98 "phone" = NULL,
jbe@54 99 "mobile_phone" = NULL,
jbe@54 100 "profession" = NULL,
jbe@54 101 "external_memberships" = NULL,
jbe@54 102 "external_posts" = NULL,
jbe@54 103 "statement" = NULL
jbe@54 104 WHERE "id" = "member_id_p";
jbe@54 105 -- "text_search_data" is updated by triggers
jbe@54 106 UPDATE "member_history" SET "login" = NULL
jbe@54 107 WHERE "member_id" = "member_id_p";
jbe@54 108 DELETE FROM "setting" WHERE "member_id" = "member_id_p";
jbe@54 109 DELETE FROM "setting_map" WHERE "member_id" = "member_id_p";
jbe@54 110 DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p";
jbe@54 111 DELETE FROM "member_image" WHERE "member_id" = "member_id_p";
jbe@54 112 DELETE FROM "contact" WHERE "member_id" = "member_id_p";
jbe@54 113 DELETE FROM "area_setting" WHERE "member_id" = "member_id_p";
jbe@54 114 DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p";
jbe@54 115 DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p";
jbe@54 116 DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p";
jbe@54 117 DELETE FROM "membership" WHERE "member_id" = "member_id_p";
jbe@54 118 DELETE FROM "delegation" WHERE "truster_id" = "member_id_p";
jbe@54 119 RETURN;
jbe@54 120 END;
jbe@54 121 $$;
jbe@54 122
jbe@54 123 COMMENT ON FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE) IS 'Clear certain settings and data of a particular member (data protection)';
jbe@54 124
jbe@54 125 CREATE OR REPLACE FUNCTION "delete_private_data"()
jbe@54 126 RETURNS VOID
jbe@54 127 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@54 128 BEGIN
jbe@54 129 UPDATE "member" SET
jbe@54 130 "login" = NULL,
jbe@54 131 "password" = NULL,
jbe@54 132 "notify_email" = NULL,
jbe@54 133 "notify_email_unconfirmed" = NULL,
jbe@54 134 "notify_email_secret" = NULL,
jbe@54 135 "notify_email_secret_expiry" = NULL,
jbe@54 136 "password_reset_secret" = NULL,
jbe@54 137 "password_reset_secret_expiry" = NULL,
jbe@54 138 "organizational_unit" = NULL,
jbe@54 139 "internal_posts" = NULL,
jbe@54 140 "realname" = NULL,
jbe@54 141 "birthday" = NULL,
jbe@54 142 "address" = NULL,
jbe@54 143 "email" = NULL,
jbe@54 144 "xmpp_address" = NULL,
jbe@54 145 "website" = NULL,
jbe@54 146 "phone" = NULL,
jbe@54 147 "mobile_phone" = NULL,
jbe@54 148 "profession" = NULL,
jbe@54 149 "external_memberships" = NULL,
jbe@54 150 "external_posts" = NULL,
jbe@54 151 "statement" = NULL;
jbe@54 152 -- "text_search_data" is updated by triggers
jbe@54 153 UPDATE "member_history" SET "login" = NULL;
jbe@54 154 DELETE FROM "invite_code";
jbe@54 155 DELETE FROM "setting";
jbe@54 156 DELETE FROM "setting_map";
jbe@54 157 DELETE FROM "member_relation_setting";
jbe@54 158 DELETE FROM "member_image";
jbe@54 159 DELETE FROM "contact";
jbe@54 160 DELETE FROM "session";
jbe@54 161 DELETE FROM "area_setting";
jbe@54 162 DELETE FROM "issue_setting";
jbe@54 163 DELETE FROM "initiative_setting";
jbe@54 164 DELETE FROM "suggestion_setting";
jbe@54 165 DELETE FROM "direct_voter" USING "issue"
jbe@54 166 WHERE "direct_voter"."issue_id" = "issue"."id"
jbe@54 167 AND "issue"."closed" ISNULL;
jbe@54 168 RETURN;
jbe@54 169 END;
jbe@54 170 $$;
jbe@54 171
jbe@54 172 COMMIT;

Impressum / About Us