liquid_feedback_core

annotate update/core-update.v1.0.3-v1.1.0.sql @ 142:54ac8c473263

find changesets by author, revision, files, or words in the commit message
Use an improved definition for "disqualified" initiatives

"initiative"."disqualified" is TRUE, if the initiative may not win, because it either (a) has no better rank than the status quo, or (b) because there exists a better ranked initiative X, which directly beats this initiative, and either more voters prefer X to this initiative than voters preferring X to the status quo or less voters prefer this initiative to X than voters preferring the status quo to X
author jbe
date Wed Jun 01 16:58:00 2011 +0200 (2011-06-01)
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