liquid_feedback_core

annotate update/core-update.v2.2.3-v2.2.4.sql @ 423:73c2ab2d068f

find changesets by author, revision, files, or words in the commit message
Work on configuration of complexity of counting of the votes (extent of tie-breaking):
- added configuration field in "policy" table
- calculation of secondary criterion for the defeat strength (tie-breaking of the links) based on initiative id's
author jbe
date Thu Apr 10 00:20:03 2014 +0200 (2014-04-10)
parents ae69cf82c05f
children
rev   line source
jbe@387 1 BEGIN;
jbe@387 2
jbe@387 3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
jbe@387 4 SELECT * FROM (VALUES ('2.2.4', 2, 2, 4))
jbe@387 5 AS "subquery"("string", "major", "minor", "revision");
jbe@387 6
jbe@387 7
jbe@387 8 ALTER TABLE "member" ADD COLUMN "last_delegation_check" TIMESTAMPTZ;
jbe@387 9 ALTER TABLE "member" ADD COLUMN "login_recovery_expiry" TIMESTAMPTZ;
jbe@387 10
jbe@387 11 COMMENT ON COLUMN "member"."last_delegation_check" IS 'Timestamp of last delegation check (i.e. confirmation of all unit and area delegations)';
jbe@387 12 COMMENT ON COLUMN "member"."login_recovery_expiry" IS 'Date/time after which another login recovery attempt is allowed';
jbe@387 13 COMMENT ON COLUMN "member"."password_reset_secret" IS 'Secret string sent via e-mail for password recovery';
jbe@387 14 COMMENT ON COLUMN "member"."password_reset_secret_expiry" IS 'Date/time until the password recovery secret is valid, and date/time after which another password recovery attempt is allowed';
jbe@387 15
jbe@387 16 ALTER TABLE "session" ADD COLUMN "needs_delegation_check" BOOLEAN NOT NULL DEFAULT FALSE;
jbe@387 17 COMMENT ON COLUMN "session"."needs_delegation_check" IS 'Set to TRUE, if member must perform a delegation check to proceed with login; see column "last_delegation_check" in "member" table';
jbe@387 18
jbe@387 19 CREATE OR REPLACE VIEW "event_seen_by_member" AS
jbe@387 20 SELECT
jbe@387 21 "member"."id" AS "seen_by_member_id",
jbe@387 22 CASE WHEN "event"."state" IN (
jbe@387 23 'voting',
jbe@387 24 'finished_without_winner',
jbe@387 25 'finished_with_winner'
jbe@387 26 ) THEN
jbe@387 27 'voting'::"notify_level"
jbe@387 28 ELSE
jbe@387 29 CASE WHEN "event"."state" IN (
jbe@387 30 'verification',
jbe@387 31 'canceled_after_revocation_during_verification',
jbe@387 32 'canceled_no_initiative_admitted'
jbe@387 33 ) THEN
jbe@387 34 'verification'::"notify_level"
jbe@387 35 ELSE
jbe@387 36 CASE WHEN "event"."state" IN (
jbe@387 37 'discussion',
jbe@387 38 'canceled_after_revocation_during_discussion'
jbe@387 39 ) THEN
jbe@387 40 'discussion'::"notify_level"
jbe@387 41 ELSE
jbe@387 42 'all'::"notify_level"
jbe@387 43 END
jbe@387 44 END
jbe@387 45 END AS "notify_level",
jbe@387 46 "event".*
jbe@387 47 FROM "member" CROSS JOIN "event"
jbe@387 48 LEFT JOIN "issue"
jbe@387 49 ON "event"."issue_id" = "issue"."id"
jbe@387 50 LEFT JOIN "membership"
jbe@387 51 ON "member"."id" = "membership"."member_id"
jbe@387 52 AND "issue"."area_id" = "membership"."area_id"
jbe@387 53 LEFT JOIN "interest"
jbe@387 54 ON "member"."id" = "interest"."member_id"
jbe@387 55 AND "event"."issue_id" = "interest"."issue_id"
jbe@387 56 LEFT JOIN "ignored_member"
jbe@387 57 ON "member"."id" = "ignored_member"."member_id"
jbe@387 58 AND "event"."member_id" = "ignored_member"."other_member_id"
jbe@387 59 LEFT JOIN "ignored_initiative"
jbe@387 60 ON "member"."id" = "ignored_initiative"."member_id"
jbe@387 61 AND "event"."initiative_id" = "ignored_initiative"."initiative_id"
jbe@387 62 WHERE (
jbe@387 63 "interest"."member_id" NOTNULL OR
jbe@387 64 ( "membership"."member_id" NOTNULL AND
jbe@387 65 "event"."event" IN (
jbe@387 66 'issue_state_changed',
jbe@387 67 'initiative_created_in_new_issue',
jbe@387 68 'initiative_created_in_existing_issue',
jbe@387 69 'initiative_revoked' ) ) )
jbe@387 70 AND "ignored_member"."member_id" ISNULL
jbe@387 71 AND "ignored_initiative"."member_id" ISNULL;
jbe@387 72
jbe@387 73 CREATE OR REPLACE VIEW "selected_event_seen_by_member" AS
jbe@387 74 SELECT
jbe@387 75 "member"."id" AS "seen_by_member_id",
jbe@387 76 CASE WHEN "event"."state" IN (
jbe@387 77 'voting',
jbe@387 78 'finished_without_winner',
jbe@387 79 'finished_with_winner'
jbe@387 80 ) THEN
jbe@387 81 'voting'::"notify_level"
jbe@387 82 ELSE
jbe@387 83 CASE WHEN "event"."state" IN (
jbe@387 84 'verification',
jbe@387 85 'canceled_after_revocation_during_verification',
jbe@387 86 'canceled_no_initiative_admitted'
jbe@387 87 ) THEN
jbe@387 88 'verification'::"notify_level"
jbe@387 89 ELSE
jbe@387 90 CASE WHEN "event"."state" IN (
jbe@387 91 'discussion',
jbe@387 92 'canceled_after_revocation_during_discussion'
jbe@387 93 ) THEN
jbe@387 94 'discussion'::"notify_level"
jbe@387 95 ELSE
jbe@387 96 'all'::"notify_level"
jbe@387 97 END
jbe@387 98 END
jbe@387 99 END AS "notify_level",
jbe@387 100 "event".*
jbe@387 101 FROM "member" CROSS JOIN "event"
jbe@387 102 LEFT JOIN "issue"
jbe@387 103 ON "event"."issue_id" = "issue"."id"
jbe@387 104 LEFT JOIN "membership"
jbe@387 105 ON "member"."id" = "membership"."member_id"
jbe@387 106 AND "issue"."area_id" = "membership"."area_id"
jbe@387 107 LEFT JOIN "interest"
jbe@387 108 ON "member"."id" = "interest"."member_id"
jbe@387 109 AND "event"."issue_id" = "interest"."issue_id"
jbe@387 110 LEFT JOIN "ignored_member"
jbe@387 111 ON "member"."id" = "ignored_member"."member_id"
jbe@387 112 AND "event"."member_id" = "ignored_member"."other_member_id"
jbe@387 113 LEFT JOIN "ignored_initiative"
jbe@387 114 ON "member"."id" = "ignored_initiative"."member_id"
jbe@387 115 AND "event"."initiative_id" = "ignored_initiative"."initiative_id"
jbe@387 116 WHERE (
jbe@387 117 ( "member"."notify_level" >= 'all' ) OR
jbe@387 118 ( "member"."notify_level" >= 'voting' AND
jbe@387 119 "event"."state" IN (
jbe@387 120 'voting',
jbe@387 121 'finished_without_winner',
jbe@387 122 'finished_with_winner' ) ) OR
jbe@387 123 ( "member"."notify_level" >= 'verification' AND
jbe@387 124 "event"."state" IN (
jbe@387 125 'verification',
jbe@387 126 'canceled_after_revocation_during_verification',
jbe@387 127 'canceled_no_initiative_admitted' ) ) OR
jbe@387 128 ( "member"."notify_level" >= 'discussion' AND
jbe@387 129 "event"."state" IN (
jbe@387 130 'discussion',
jbe@387 131 'canceled_after_revocation_during_discussion' ) ) )
jbe@387 132 AND (
jbe@387 133 "interest"."member_id" NOTNULL OR
jbe@387 134 ( "membership"."member_id" NOTNULL AND
jbe@387 135 "event"."event" IN (
jbe@387 136 'issue_state_changed',
jbe@387 137 'initiative_created_in_new_issue',
jbe@387 138 'initiative_created_in_existing_issue',
jbe@387 139 'initiative_revoked' ) ) )
jbe@387 140 AND "ignored_member"."member_id" ISNULL
jbe@387 141 AND "ignored_initiative"."member_id" ISNULL;
jbe@387 142
jbe@387 143 CREATE OR REPLACE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)
jbe@387 144 RETURNS VOID
jbe@387 145 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@387 146 BEGIN
jbe@387 147 UPDATE "member" SET
jbe@387 148 "last_login" = NULL,
jbe@387 149 "last_delegation_check" = NULL,
jbe@387 150 "login" = NULL,
jbe@387 151 "password" = NULL,
jbe@387 152 "locked" = TRUE,
jbe@387 153 "active" = FALSE,
jbe@387 154 "notify_email" = NULL,
jbe@387 155 "notify_email_unconfirmed" = NULL,
jbe@387 156 "notify_email_secret" = NULL,
jbe@387 157 "notify_email_secret_expiry" = NULL,
jbe@387 158 "notify_email_lock_expiry" = NULL,
jbe@387 159 "login_recovery_expiry" = NULL,
jbe@387 160 "password_reset_secret" = NULL,
jbe@387 161 "password_reset_secret_expiry" = NULL,
jbe@387 162 "organizational_unit" = NULL,
jbe@387 163 "internal_posts" = NULL,
jbe@387 164 "realname" = NULL,
jbe@387 165 "birthday" = NULL,
jbe@387 166 "address" = NULL,
jbe@387 167 "email" = NULL,
jbe@387 168 "xmpp_address" = NULL,
jbe@387 169 "website" = NULL,
jbe@387 170 "phone" = NULL,
jbe@387 171 "mobile_phone" = NULL,
jbe@387 172 "profession" = NULL,
jbe@387 173 "external_memberships" = NULL,
jbe@387 174 "external_posts" = NULL,
jbe@387 175 "statement" = NULL
jbe@387 176 WHERE "id" = "member_id_p";
jbe@387 177 -- "text_search_data" is updated by triggers
jbe@387 178 DELETE FROM "setting" WHERE "member_id" = "member_id_p";
jbe@387 179 DELETE FROM "setting_map" WHERE "member_id" = "member_id_p";
jbe@387 180 DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p";
jbe@387 181 DELETE FROM "member_image" WHERE "member_id" = "member_id_p";
jbe@387 182 DELETE FROM "contact" WHERE "member_id" = "member_id_p";
jbe@387 183 DELETE FROM "ignored_member" WHERE "member_id" = "member_id_p";
jbe@387 184 DELETE FROM "session" WHERE "member_id" = "member_id_p";
jbe@387 185 DELETE FROM "area_setting" WHERE "member_id" = "member_id_p";
jbe@387 186 DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p";
jbe@387 187 DELETE FROM "ignored_initiative" WHERE "member_id" = "member_id_p";
jbe@387 188 DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p";
jbe@387 189 DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p";
jbe@387 190 DELETE FROM "membership" WHERE "member_id" = "member_id_p";
jbe@387 191 DELETE FROM "delegation" WHERE "truster_id" = "member_id_p";
jbe@387 192 DELETE FROM "non_voter" WHERE "member_id" = "member_id_p";
jbe@387 193 DELETE FROM "direct_voter" USING "issue"
jbe@387 194 WHERE "direct_voter"."issue_id" = "issue"."id"
jbe@387 195 AND "issue"."closed" ISNULL
jbe@387 196 AND "member_id" = "member_id_p";
jbe@387 197 RETURN;
jbe@387 198 END;
jbe@387 199 $$;
jbe@387 200
jbe@387 201 CREATE OR REPLACE FUNCTION "delete_private_data"()
jbe@387 202 RETURNS VOID
jbe@387 203 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@387 204 BEGIN
jbe@387 205 DELETE FROM "temporary_transaction_data";
jbe@387 206 DELETE FROM "member" WHERE "activated" ISNULL;
jbe@387 207 UPDATE "member" SET
jbe@387 208 "invite_code" = NULL,
jbe@387 209 "invite_code_expiry" = NULL,
jbe@387 210 "admin_comment" = NULL,
jbe@387 211 "last_login" = NULL,
jbe@387 212 "last_delegation_check" = NULL,
jbe@387 213 "login" = NULL,
jbe@387 214 "password" = NULL,
jbe@387 215 "lang" = NULL,
jbe@387 216 "notify_email" = NULL,
jbe@387 217 "notify_email_unconfirmed" = NULL,
jbe@387 218 "notify_email_secret" = NULL,
jbe@387 219 "notify_email_secret_expiry" = NULL,
jbe@387 220 "notify_email_lock_expiry" = NULL,
jbe@387 221 "notify_level" = NULL,
jbe@387 222 "login_recovery_expiry" = NULL,
jbe@387 223 "password_reset_secret" = NULL,
jbe@387 224 "password_reset_secret_expiry" = NULL,
jbe@387 225 "organizational_unit" = NULL,
jbe@387 226 "internal_posts" = NULL,
jbe@387 227 "realname" = NULL,
jbe@387 228 "birthday" = NULL,
jbe@387 229 "address" = NULL,
jbe@387 230 "email" = NULL,
jbe@387 231 "xmpp_address" = NULL,
jbe@387 232 "website" = NULL,
jbe@387 233 "phone" = NULL,
jbe@387 234 "mobile_phone" = NULL,
jbe@387 235 "profession" = NULL,
jbe@387 236 "external_memberships" = NULL,
jbe@387 237 "external_posts" = NULL,
jbe@387 238 "formatting_engine" = NULL,
jbe@387 239 "statement" = NULL;
jbe@387 240 -- "text_search_data" is updated by triggers
jbe@387 241 DELETE FROM "setting";
jbe@387 242 DELETE FROM "setting_map";
jbe@387 243 DELETE FROM "member_relation_setting";
jbe@387 244 DELETE FROM "member_image";
jbe@387 245 DELETE FROM "contact";
jbe@387 246 DELETE FROM "ignored_member";
jbe@387 247 DELETE FROM "session";
jbe@387 248 DELETE FROM "area_setting";
jbe@387 249 DELETE FROM "issue_setting";
jbe@387 250 DELETE FROM "ignored_initiative";
jbe@387 251 DELETE FROM "initiative_setting";
jbe@387 252 DELETE FROM "suggestion_setting";
jbe@387 253 DELETE FROM "non_voter";
jbe@387 254 DELETE FROM "direct_voter" USING "issue"
jbe@387 255 WHERE "direct_voter"."issue_id" = "issue"."id"
jbe@387 256 AND "issue"."closed" ISNULL;
jbe@387 257 RETURN;
jbe@387 258 END;
jbe@387 259 $$;
jbe@387 260
jbe@387 261 COMMIT;

Impressum / About Us