liquid_feedback_core
annotate update/core-update.v1.3.1-v1.4.0_rc1.sql @ 121:8a1a488a1a67
Bugfix in function "freeze_after_snapshot"; Simplified function "close_voting"
Constraint "valid_state" on table "issue" requires, that "ranks_available" is set to true, if state is set to 'canceled_no_initiative_admitted'.
Function "close_voting" assumes that there was at least one initiative to be voted on (ensured by "freeze_after_snapshot").
Constraint "valid_state" on table "issue" requires, that "ranks_available" is set to true, if state is set to 'canceled_no_initiative_admitted'.
Function "close_voting" assumes that there was at least one initiative to be voted on (ensured by "freeze_after_snapshot").
author | jbe |
---|---|
date | Mon Mar 07 19:15:14 2011 +0100 (2011-03-07) |
parents | 7d6c5032262f |
children | 2a1f0bd5fcff |
rev | line source |
---|---|
jbe@115 | 1 BEGIN; -- NOTE: file contains additional statements AFTER this BEGIN/COMMIT block! |
jbe@115 | 2 |
jbe@115 | 3 |
jbe@115 | 4 -- Update version information: |
jbe@115 | 5 |
jbe@115 | 6 CREATE OR REPLACE VIEW "liquid_feedback_version" AS |
jbe@115 | 7 SELECT * FROM (VALUES ('1.4.0_rc1', 1, 4, -1)) |
jbe@115 | 8 AS "subquery"("string", "major", "minor", "revision"); |
jbe@115 | 9 |
jbe@115 | 10 |
jbe@115 | 11 -- New columns "notify_level" and "notify_event_id" in "member" table: |
jbe@115 | 12 |
jbe@115 | 13 CREATE TYPE "notify_level" AS ENUM |
jbe@115 | 14 ('none', 'voting', 'verification', 'discussion', 'all'); |
jbe@115 | 15 |
jbe@115 | 16 COMMENT ON TYPE "notify_level" IS 'Level of notification: ''none'' = no notifications, ''voting'' = notifications about finished issues and issues in voting, ''verification'' = notifications about finished issues, issues in voting and verification phase, ''discussion'' = notifications about everything except issues in admission phase, ''all'' = notifications about everything'; |
jbe@115 | 17 |
jbe@115 | 18 ALTER TABLE "member" ADD "notify_level" "notify_level" NOT NULL DEFAULT 'none'; |
jbe@115 | 19 ALTER TABLE "member" ADD "notify_event_id" INT8; |
jbe@115 | 20 |
jbe@115 | 21 COMMENT ON COLUMN "member"."notify_level" IS 'Selects which event notifications are to be sent to the "notify_email" mail address'; |
jbe@115 | 22 COMMENT ON COLUMN "member"."notify_event_id" IS 'Latest "id" of an "event" the member was notified about'; |
jbe@115 | 23 |
jbe@115 | 24 |
jbe@115 | 25 -- Add primary key with type SERIAL8 (INT8) for "invite_code" table: |
jbe@115 | 26 |
jbe@115 | 27 ALTER TABLE "invite_code" DROP CONSTRAINT "invite_code_pkey"; |
jbe@115 | 28 ALTER TABLE "invite_code" ALTER "code" SET NOT NULL; |
jbe@115 | 29 ALTER TABLE "invite_code" ADD UNIQUE ("code"); |
jbe@115 | 30 ALTER TABLE "invite_code" ADD "id" SERIAL8 PRIMARY KEY; |
jbe@115 | 31 |
jbe@115 | 32 |
jbe@115 | 33 -- Add index for "other_member_id" column of "contact" table: |
jbe@115 | 34 |
jbe@115 | 35 CREATE INDEX "contact_other_member_id_idx" ON "contact" ("other_member_id"); |
jbe@115 | 36 |
jbe@115 | 37 |
jbe@115 | 38 -- New table "ignored_member": |
jbe@115 | 39 |
jbe@115 | 40 CREATE TABLE "ignored_member" ( |
jbe@115 | 41 PRIMARY KEY ("member_id", "other_member_id"), |
jbe@115 | 42 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE, |
jbe@115 | 43 "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE ); |
jbe@115 | 44 CREATE INDEX "ignored_member_other_member_id_idx" ON "ignored_member" ("other_member_id"); |
jbe@115 | 45 |
jbe@115 | 46 COMMENT ON TABLE "ignored_member" IS 'Possibility to filter other members'; |
jbe@115 | 47 |
jbe@115 | 48 COMMENT ON COLUMN "ignored_member"."member_id" IS 'Member ignoring someone'; |
jbe@115 | 49 COMMENT ON COLUMN "ignored_member"."other_member_id" IS 'Member being ignored'; |
jbe@115 | 50 |
jbe@115 | 51 |
jbe@115 | 52 -- New table "unit" with default entry: |
jbe@115 | 53 |
jbe@115 | 54 CREATE TABLE "unit" ( |
jbe@115 | 55 "id" SERIAL4 PRIMARY KEY, |
jbe@115 | 56 "parent_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE, |
jbe@115 | 57 "active" BOOLEAN NOT NULL DEFAULT TRUE, |
jbe@115 | 58 "name" TEXT NOT NULL, |
jbe@115 | 59 "description" TEXT NOT NULL DEFAULT '', |
jbe@115 | 60 "member_count" INT4, |
jbe@115 | 61 "text_search_data" TSVECTOR ); |
jbe@115 | 62 CREATE INDEX "unit_root_idx" ON "unit" ("id") WHERE "parent_id" ISNULL; |
jbe@115 | 63 CREATE INDEX "unit_parent_id_idx" ON "unit" ("parent_id"); |
jbe@115 | 64 CREATE INDEX "unit_active_idx" ON "unit" ("active"); |
jbe@115 | 65 CREATE INDEX "unit_text_search_data_idx" ON "unit" USING gin ("text_search_data"); |
jbe@115 | 66 CREATE TRIGGER "update_text_search_data" |
jbe@115 | 67 BEFORE INSERT OR UPDATE ON "unit" |
jbe@115 | 68 FOR EACH ROW EXECUTE PROCEDURE |
jbe@115 | 69 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', |
jbe@115 | 70 "name", "description" ); |
jbe@115 | 71 |
jbe@115 | 72 COMMENT ON TABLE "unit" IS 'Organizational units organized as trees; Delegations are not inherited through these trees.'; |
jbe@115 | 73 |
jbe@115 | 74 COMMENT ON COLUMN "unit"."parent_id" IS 'Parent id of tree node; Multiple roots allowed'; |
jbe@115 | 75 COMMENT ON COLUMN "unit"."active" IS 'TRUE means new issues can be created in units of this area'; |
jbe@115 | 76 COMMENT ON COLUMN "unit"."member_count" IS 'Count of members as determined by column "voting_right" in table "privilege"'; |
jbe@115 | 77 |
jbe@115 | 78 INSERT INTO "unit" ("name") VALUES ('Main'); -- NOTE: gets id 1 |
jbe@115 | 79 |
jbe@115 | 80 |
jbe@115 | 81 -- New column "unit_id" in table "area": |
jbe@115 | 82 |
jbe@115 | 83 ALTER TABLE "area" ADD "unit_id" INT4 DEFAULT 1 |
jbe@115 | 84 NOT NULL REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE; |
jbe@115 | 85 ALTER TABLE "area" ALTER "unit_id" DROP DEFAULT; |
jbe@115 | 86 |
jbe@115 | 87 CREATE INDEX "area_unit_id_idx" ON "area" ("unit_id"); |
jbe@115 | 88 |
jbe@115 | 89 |
jbe@115 | 90 -- Issue states: |
jbe@115 | 91 |
jbe@115 | 92 CREATE TYPE "issue_state" AS ENUM ( |
jbe@115 | 93 'admission', 'discussion', 'verification', 'voting', |
jbe@115 | 94 'canceled_revoked_before_accepted', |
jbe@115 | 95 'canceled_issue_not_accepted', |
jbe@115 | 96 'canceled_after_revocation_during_discussion', |
jbe@115 | 97 'canceled_after_revocation_during_verification', |
jbe@115 | 98 'calculation', |
jbe@115 | 99 'canceled_no_initiative_admitted', |
jbe@115 | 100 'finished_without_winner', 'finished_with_winner'); |
jbe@115 | 101 |
jbe@115 | 102 COMMENT ON TYPE "issue_state" IS 'State of issues'; |
jbe@115 | 103 |
jbe@115 | 104 ALTER TABLE "issue" ADD "state" "issue_state"; |
jbe@115 | 105 |
jbe@115 | 106 -- NOTE: Filling new column with values is done after this transaction (see below) |
jbe@115 | 107 |
jbe@115 | 108 |
jbe@115 | 109 -- New column "revoked_by_member_id" in table "initiative": |
jbe@115 | 110 |
jbe@115 | 111 ALTER TABLE "initiative" ADD "revoked_by_member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE; |
jbe@115 | 112 |
jbe@115 | 113 COMMENT ON COLUMN "initiative"."revoked_by_member_id" IS 'Member, who decided to revoked the initiative'; |
jbe@115 | 114 |
jbe@115 | 115 -- NOTE: Filling new column with values is done after this transaction (see below) |
jbe@115 | 116 |
jbe@115 | 117 |
jbe@115 | 118 -- New table "ignored_initiative": |
jbe@115 | 119 |
jbe@115 | 120 CREATE TABLE "ignored_initiative" ( |
jbe@115 | 121 PRIMARY KEY ("initiative_id", "member_id"), |
jbe@115 | 122 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE, |
jbe@115 | 123 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE ); |
jbe@115 | 124 CREATE INDEX "ignored_initiative_member_id_idx" ON "ignored_initiative" ("member_id"); |
jbe@115 | 125 |
jbe@115 | 126 COMMENT ON TABLE "ignored_initiative" IS 'Possibility to filter initiatives'; |
jbe@115 | 127 |
jbe@115 | 128 |
jbe@115 | 129 -- New table "invite_code_unit": |
jbe@115 | 130 |
jbe@115 | 131 CREATE TABLE "invite_code_unit" ( |
jbe@115 | 132 PRIMARY KEY ("invite_code_id", "unit_id"), |
jbe@115 | 133 "invite_code_id" INT8 REFERENCES "invite_code" ("id") ON DELETE CASCADE ON UPDATE CASCADE, |
jbe@115 | 134 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE ); |
jbe@115 | 135 |
jbe@115 | 136 COMMENT ON TABLE "invite_code_unit" IS 'Units where accounts created with a given invite codes get voting rights'; |
jbe@115 | 137 |
jbe@117 | 138 INSERT INTO "invite_code_unit" ("invite_code_id", "unit_id") |
jbe@117 | 139 SELECT "id" AS "invite_code_id", 1 AS "unit_id" |
jbe@117 | 140 FROM "invite_code" WHERE "used" ISNULL; |
jbe@115 | 141 |
jbe@115 | 142 |
jbe@115 | 143 -- New table "privilege": |
jbe@115 | 144 |
jbe@115 | 145 CREATE TABLE "privilege" ( |
jbe@115 | 146 PRIMARY KEY ("unit_id", "member_id"), |
jbe@115 | 147 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE, |
jbe@115 | 148 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE, |
jbe@115 | 149 "admin_manager" BOOLEAN NOT NULL DEFAULT FALSE, |
jbe@115 | 150 "unit_manager" BOOLEAN NOT NULL DEFAULT FALSE, |
jbe@115 | 151 "area_manager" BOOLEAN NOT NULL DEFAULT FALSE, |
jbe@115 | 152 "voting_right_manager" BOOLEAN NOT NULL DEFAULT FALSE, |
jbe@115 | 153 "voting_right" BOOLEAN NOT NULL DEFAULT TRUE ); |
jbe@115 | 154 |
jbe@115 | 155 COMMENT ON TABLE "privilege" IS 'Members rights related to each unit'; |
jbe@115 | 156 |
jbe@115 | 157 COMMENT ON COLUMN "privilege"."admin_manager" IS 'Grant/revoke admin privileges to/from other users'; |
jbe@115 | 158 COMMENT ON COLUMN "privilege"."unit_manager" IS 'Create or lock sub units'; |
jbe@115 | 159 COMMENT ON COLUMN "privilege"."area_manager" IS 'Create or lock areas and set area parameters'; |
jbe@115 | 160 COMMENT ON COLUMN "privilege"."voting_right_manager" IS 'Select which members are allowed to discuss and vote inside the unit'; |
jbe@115 | 161 COMMENT ON COLUMN "privilege"."voting_right" IS 'Right to discuss and vote'; |
jbe@115 | 162 |
jbe@115 | 163 |
jbe@115 | 164 -- Remove table "ignored_issue", which is no longer existent: |
jbe@115 | 165 |
jbe@115 | 166 DROP TABLE "ignored_issue"; |
jbe@115 | 167 |
jbe@115 | 168 |
jbe@115 | 169 -- Replace TYPE "delegation_scope" with a new type, where 'global' is replaced by 'unit': |
jbe@115 | 170 |
jbe@115 | 171 ALTER TYPE "delegation_scope" RENAME TO "delegation_scope_old"; -- NOTE: dropped later |
jbe@115 | 172 CREATE TYPE "delegation_scope" AS ENUM ('unit', 'area', 'issue'); |
jbe@115 | 173 COMMENT ON TYPE "delegation_scope" IS 'Scope for delegations: ''unit'', ''area'', or ''issue'' (order is relevant)'; |
jbe@115 | 174 |
jbe@115 | 175 |
jbe@115 | 176 -- Delete views and functions being dependent on TYPE "delegation_scope": |
jbe@115 | 177 |
jbe@115 | 178 DROP FUNCTION "delegation_chain" |
jbe@115 | 179 ( "member_id_p" "member"."id"%TYPE, |
jbe@115 | 180 "area_id_p" "area"."id"%TYPE, |
jbe@115 | 181 "issue_id_p" "issue"."id"%TYPE ); |
jbe@115 | 182 |
jbe@115 | 183 DROP FUNCTION "delegation_chain" |
jbe@115 | 184 ( "member_id_p" "member"."id"%TYPE, |
jbe@115 | 185 "area_id_p" "area"."id"%TYPE, |
jbe@115 | 186 "issue_id_p" "issue"."id"%TYPE, |
jbe@115 | 187 "simulate_trustee_id_p" "member"."id"%TYPE ); |
jbe@115 | 188 |
jbe@115 | 189 DROP TYPE "delegation_chain_row"; |
jbe@115 | 190 |
jbe@115 | 191 DROP VIEW "issue_delegation"; |
jbe@115 | 192 DROP VIEW "area_delegation"; |
jbe@115 | 193 DROP VIEW "global_delegation"; |
jbe@115 | 194 DROP VIEW "active_delegation"; |
jbe@115 | 195 |
jbe@115 | 196 |
jbe@115 | 197 -- Modify "delegation" table to use new "delegation_scope" TYPE: |
jbe@115 | 198 |
jbe@115 | 199 ALTER TABLE "delegation" DROP CONSTRAINT "no_global_delegation_to_null"; |
jbe@115 | 200 ALTER TABLE "delegation" DROP CONSTRAINT "area_id_and_issue_id_set_according_to_scope"; |
jbe@115 | 201 |
jbe@115 | 202 DROP INDEX "delegation_global_truster_id_unique_idx"; |
jbe@115 | 203 |
jbe@115 | 204 ALTER TABLE "delegation" ALTER "scope" TYPE "delegation_scope" |
jbe@115 | 205 USING CASE WHEN "scope" = 'global' |
jbe@115 | 206 THEN 'unit'::"delegation_scope" |
jbe@115 | 207 ELSE "scope"::text::"delegation_scope" END; |
jbe@115 | 208 |
jbe@115 | 209 ALTER TABLE "delegation" ADD "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE; |
jbe@115 | 210 |
jbe@115 | 211 ALTER TABLE "delegation" ADD CONSTRAINT "no_unit_delegation_to_null" |
jbe@115 | 212 CHECK ("trustee_id" NOTNULL OR "scope" != 'unit'); |
jbe@115 | 213 |
jbe@115 | 214 ALTER TABLE "delegation" ADD UNIQUE ("unit_id", "truster_id"); |
jbe@115 | 215 |
jbe@115 | 216 COMMENT ON COLUMN "delegation"."unit_id" IS 'Reference to unit, if delegation is unit-wide, otherwise NULL'; |
jbe@115 | 217 |
jbe@115 | 218 -- NOTE: Column "unit_id" filled after transaction (see below) |
jbe@115 | 219 |
jbe@115 | 220 |
jbe@115 | 221 -- Modify snapshot tables to use new "delegation_scope" TYPE: |
jbe@115 | 222 |
jbe@115 | 223 ALTER TABLE "delegating_population_snapshot" ALTER "scope" TYPE "delegation_scope" |
jbe@115 | 224 USING CASE WHEN "scope" = 'global' |
jbe@115 | 225 THEN 'unit'::"delegation_scope" |
jbe@115 | 226 ELSE "scope"::text::"delegation_scope" END; |
jbe@115 | 227 |
jbe@115 | 228 ALTER TABLE "delegating_interest_snapshot" ALTER "scope" TYPE "delegation_scope" |
jbe@115 | 229 USING CASE WHEN "scope" = 'global' |
jbe@115 | 230 THEN 'unit'::"delegation_scope" |
jbe@115 | 231 ELSE "scope"::text::"delegation_scope" END; |
jbe@115 | 232 |
jbe@115 | 233 ALTER TABLE "delegating_voter" ALTER "scope" TYPE "delegation_scope" |
jbe@115 | 234 USING CASE WHEN "scope" = 'global' |
jbe@115 | 235 THEN 'unit'::"delegation_scope" |
jbe@115 | 236 ELSE "scope"::text::"delegation_scope" END; |
jbe@115 | 237 |
jbe@115 | 238 |
jbe@115 | 239 -- New table "non_voter": |
jbe@115 | 240 |
jbe@115 | 241 CREATE TABLE "non_voter" ( |
jbe@115 | 242 PRIMARY KEY ("issue_id", "member_id"), |
jbe@115 | 243 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE, |
jbe@115 | 244 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE ); |
jbe@115 | 245 CREATE INDEX "non_voter_member_id_idx" ON "non_voter" ("member_id"); |
jbe@115 | 246 |
jbe@115 | 247 COMMENT ON TABLE "non_voter" IS 'Members who decided to not vote directly on an issue'; |
jbe@115 | 248 |
jbe@115 | 249 |
jbe@115 | 250 -- New table "issue_comment": |
jbe@115 | 251 |
jbe@115 | 252 CREATE TABLE "issue_comment" ( |
jbe@115 | 253 PRIMARY KEY ("issue_id", "member_id"), |
jbe@115 | 254 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE, |
jbe@115 | 255 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE, |
jbe@115 | 256 "changed" TIMESTAMPTZ NOT NULL DEFAULT now(), |
jbe@115 | 257 "formatting_engine" TEXT, |
jbe@115 | 258 "content" TEXT NOT NULL, |
jbe@115 | 259 "text_search_data" TSVECTOR ); |
jbe@115 | 260 CREATE INDEX "issue_comment_member_id_idx" ON "issue_comment" ("member_id"); |
jbe@115 | 261 CREATE INDEX "issue_comment_text_search_data_idx" ON "issue_comment" USING gin ("text_search_data"); |
jbe@115 | 262 CREATE TRIGGER "update_text_search_data" |
jbe@115 | 263 BEFORE INSERT OR UPDATE ON "issue_comment" |
jbe@115 | 264 FOR EACH ROW EXECUTE PROCEDURE |
jbe@115 | 265 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "content"); |
jbe@115 | 266 |
jbe@115 | 267 COMMENT ON TABLE "issue_comment" IS 'Place to store free comments of members related to issues'; |
jbe@115 | 268 |
jbe@115 | 269 COMMENT ON COLUMN "issue_comment"."changed" IS 'Time the comment was last changed'; |
jbe@115 | 270 |
jbe@115 | 271 |
jbe@115 | 272 -- New table "rendered_issue_comment": |
jbe@115 | 273 |
jbe@115 | 274 CREATE TABLE "rendered_issue_comment" ( |
jbe@115 | 275 PRIMARY KEY ("issue_id", "member_id", "format"), |
jbe@115 | 276 FOREIGN KEY ("issue_id", "member_id") |
jbe@115 | 277 REFERENCES "issue_comment" ("issue_id", "member_id") |
jbe@115 | 278 ON DELETE CASCADE ON UPDATE CASCADE, |
jbe@115 | 279 "issue_id" INT4, |
jbe@115 | 280 "member_id" INT4, |
jbe@115 | 281 "format" TEXT, |
jbe@115 | 282 "content" TEXT NOT NULL ); |
jbe@115 | 283 |
jbe@115 | 284 COMMENT ON TABLE "rendered_issue_comment" IS 'This table may be used by frontends to cache "rendered" issue comments (e.g. HTML output generated from wiki text)'; |
jbe@115 | 285 |
jbe@115 | 286 |
jbe@115 | 287 -- New table "voting_comment": |
jbe@115 | 288 |
jbe@115 | 289 CREATE TABLE "voting_comment" ( |
jbe@115 | 290 PRIMARY KEY ("issue_id", "member_id"), |
jbe@115 | 291 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE, |
jbe@115 | 292 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE, |
jbe@115 | 293 "changed" TIMESTAMPTZ, |
jbe@115 | 294 "formatting_engine" TEXT, |
jbe@115 | 295 "content" TEXT NOT NULL, |
jbe@115 | 296 "text_search_data" TSVECTOR ); |
jbe@115 | 297 CREATE INDEX "voting_comment_member_id_idx" ON "voting_comment" ("member_id"); |
jbe@115 | 298 CREATE INDEX "voting_comment_text_search_data_idx" ON "voting_comment" USING gin ("text_search_data"); |
jbe@115 | 299 CREATE TRIGGER "update_text_search_data" |
jbe@115 | 300 BEFORE INSERT OR UPDATE ON "voting_comment" |
jbe@115 | 301 FOR EACH ROW EXECUTE PROCEDURE |
jbe@115 | 302 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "content"); |
jbe@115 | 303 |
jbe@115 | 304 COMMENT ON TABLE "voting_comment" IS 'Storage for comments of voters to be published after voting has finished.'; |
jbe@115 | 305 |
jbe@115 | 306 COMMENT ON COLUMN "voting_comment"."changed" IS 'Is to be set or updated by the frontend, if comment was inserted or updated AFTER the issue has been closed. Otherwise it shall be set to NULL.'; |
jbe@115 | 307 |
jbe@115 | 308 |
jbe@115 | 309 -- New table "rendered_voting_comment": |
jbe@115 | 310 |
jbe@115 | 311 CREATE TABLE "rendered_voting_comment" ( |
jbe@115 | 312 PRIMARY KEY ("issue_id", "member_id", "format"), |
jbe@115 | 313 FOREIGN KEY ("issue_id", "member_id") |
jbe@115 | 314 REFERENCES "voting_comment" ("issue_id", "member_id") |
jbe@115 | 315 ON DELETE CASCADE ON UPDATE CASCADE, |
jbe@115 | 316 "issue_id" INT4, |
jbe@115 | 317 "member_id" INT4, |
jbe@115 | 318 "format" TEXT, |
jbe@115 | 319 "content" TEXT NOT NULL ); |
jbe@115 | 320 |
jbe@115 | 321 COMMENT ON TABLE "rendered_voting_comment" IS 'This table may be used by frontends to cache "rendered" voting comments (e.g. HTML output generated from wiki text)'; |
jbe@115 | 322 |
jbe@115 | 323 |
jbe@115 | 324 -- New table "event": |
jbe@115 | 325 |
jbe@115 | 326 CREATE TYPE "event_type" AS ENUM ( |
jbe@115 | 327 'issue_state_changed', |
jbe@115 | 328 'initiative_created_in_new_issue', |
jbe@115 | 329 'initiative_created_in_existing_issue', |
jbe@115 | 330 'initiative_revoked', |
jbe@115 | 331 'new_draft_created', |
jbe@115 | 332 'suggestion_created'); |
jbe@115 | 333 |
jbe@115 | 334 COMMENT ON TYPE "event_type" IS 'Type used for column "event" of table "event"'; |
jbe@115 | 335 |
jbe@115 | 336 CREATE TABLE "event" ( |
jbe@115 | 337 "id" SERIAL8 PRIMARY KEY, |
jbe@115 | 338 "occurrence" TIMESTAMPTZ NOT NULL DEFAULT now(), |
jbe@115 | 339 "event" "event_type" NOT NULL, |
jbe@115 | 340 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE, |
jbe@115 | 341 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE, |
jbe@115 | 342 "state" "issue_state" CHECK ("state" != 'calculation'), |
jbe@115 | 343 "initiative_id" INT4, |
jbe@115 | 344 "draft_id" INT8, |
jbe@115 | 345 "suggestion_id" INT8, |
jbe@115 | 346 FOREIGN KEY ("issue_id", "initiative_id") |
jbe@115 | 347 REFERENCES "initiative" ("issue_id", "id") |
jbe@115 | 348 ON DELETE CASCADE ON UPDATE CASCADE, |
jbe@115 | 349 FOREIGN KEY ("initiative_id", "draft_id") |
jbe@115 | 350 REFERENCES "draft" ("initiative_id", "id") |
jbe@115 | 351 ON DELETE CASCADE ON UPDATE CASCADE, |
jbe@115 | 352 FOREIGN KEY ("initiative_id", "suggestion_id") |
jbe@115 | 353 REFERENCES "suggestion" ("initiative_id", "id") |
jbe@115 | 354 ON DELETE CASCADE ON UPDATE CASCADE, |
jbe@115 | 355 CONSTRAINT "null_constraints_for_issue_state_changed" CHECK ( |
jbe@115 | 356 "event" != 'issue_state_changed' OR ( |
jbe@115 | 357 "member_id" ISNULL AND |
jbe@115 | 358 "issue_id" NOTNULL AND |
jbe@115 | 359 "state" NOTNULL AND |
jbe@115 | 360 "initiative_id" ISNULL AND |
jbe@115 | 361 "draft_id" ISNULL AND |
jbe@115 | 362 "suggestion_id" ISNULL )), |
jbe@115 | 363 CONSTRAINT "null_constraints_for_initiative_creation_or_revocation_or_new_draft" CHECK ( |
jbe@115 | 364 "event" NOT IN ( |
jbe@115 | 365 'initiative_created_in_new_issue', |
jbe@115 | 366 'initiative_created_in_existing_issue', |
jbe@115 | 367 'initiative_revoked', |
jbe@115 | 368 'new_draft_created' |
jbe@115 | 369 ) OR ( |
jbe@115 | 370 "member_id" NOTNULL AND |
jbe@115 | 371 "issue_id" NOTNULL AND |
jbe@115 | 372 "state" NOTNULL AND |
jbe@115 | 373 "initiative_id" NOTNULL AND |
jbe@115 | 374 "draft_id" NOTNULL AND |
jbe@115 | 375 "suggestion_id" ISNULL )), |
jbe@115 | 376 CONSTRAINT "null_constraints_for_suggestion_creation" CHECK ( |
jbe@115 | 377 "event" != 'suggestion_created' OR ( |
jbe@115 | 378 "member_id" NOTNULL AND |
jbe@115 | 379 "issue_id" NOTNULL AND |
jbe@115 | 380 "state" NOTNULL AND |
jbe@115 | 381 "initiative_id" NOTNULL AND |
jbe@115 | 382 "draft_id" ISNULL AND |
jbe@115 | 383 "suggestion_id" NOTNULL )) ); |
jbe@115 | 384 |
jbe@115 | 385 COMMENT ON TABLE "event" IS 'Event table, automatically filled by triggers'; |
jbe@115 | 386 |
jbe@115 | 387 COMMENT ON COLUMN "event"."occurrence" IS 'Point in time, when event occurred'; |
jbe@115 | 388 COMMENT ON COLUMN "event"."event" IS 'Type of event (see TYPE "event_type")'; |
jbe@115 | 389 COMMENT ON COLUMN "event"."member_id" IS 'Member who caused the event, if applicable'; |
jbe@115 | 390 COMMENT ON COLUMN "event"."state" IS 'If issue_id is set: state of affected issue; If state changed: new state'; |
jbe@115 | 391 |
jbe@115 | 392 |
jbe@115 | 393 -- Triggers to fill "event" table: |
jbe@115 | 394 |
jbe@115 | 395 CREATE FUNCTION "write_event_issue_state_changed_trigger"() |
jbe@115 | 396 RETURNS TRIGGER |
jbe@115 | 397 LANGUAGE 'plpgsql' VOLATILE AS $$ |
jbe@115 | 398 BEGIN |
jbe@115 | 399 IF NEW."state" != OLD."state" AND NEW."state" != 'calculation' THEN |
jbe@115 | 400 INSERT INTO "event" ("event", "issue_id", "state") |
jbe@115 | 401 VALUES ('issue_state_changed', NEW."id", NEW."state"); |
jbe@115 | 402 END IF; |
jbe@115 | 403 RETURN NULL; |
jbe@115 | 404 END; |
jbe@115 | 405 $$; |
jbe@115 | 406 |
jbe@115 | 407 CREATE TRIGGER "write_event_issue_state_changed" |
jbe@115 | 408 AFTER UPDATE ON "issue" FOR EACH ROW EXECUTE PROCEDURE |
jbe@115 | 409 "write_event_issue_state_changed_trigger"(); |
jbe@115 | 410 |
jbe@115 | 411 COMMENT ON FUNCTION "write_event_issue_state_changed_trigger"() IS 'Implementation of trigger "write_event_issue_state_changed" on table "issue"'; |
jbe@115 | 412 COMMENT ON TRIGGER "write_event_issue_state_changed" ON "issue" IS 'Create entry in "event" table on "state" change'; |
jbe@115 | 413 |
jbe@115 | 414 CREATE FUNCTION "write_event_initiative_or_draft_created_trigger"() |
jbe@115 | 415 RETURNS TRIGGER |
jbe@115 | 416 LANGUAGE 'plpgsql' VOLATILE AS $$ |
jbe@115 | 417 DECLARE |
jbe@115 | 418 "initiative_row" "initiative"%ROWTYPE; |
jbe@115 | 419 "issue_row" "issue"%ROWTYPE; |
jbe@115 | 420 "event_v" "event_type"; |
jbe@115 | 421 BEGIN |
jbe@115 | 422 SELECT * INTO "initiative_row" FROM "initiative" |
jbe@115 | 423 WHERE "id" = NEW."initiative_id"; |
jbe@115 | 424 SELECT * INTO "issue_row" FROM "issue" |
jbe@115 | 425 WHERE "id" = "initiative_row"."issue_id"; |
jbe@115 | 426 IF EXISTS ( |
jbe@115 | 427 SELECT NULL FROM "draft" |
jbe@115 | 428 WHERE "initiative_id" = NEW."initiative_id" |
jbe@115 | 429 AND "id" != NEW."id" |
jbe@115 | 430 ) THEN |
jbe@115 | 431 "event_v" := 'new_draft_created'; |
jbe@115 | 432 ELSE |
jbe@115 | 433 IF EXISTS ( |
jbe@115 | 434 SELECT NULL FROM "initiative" |
jbe@115 | 435 WHERE "issue_id" = "initiative_row"."issue_id" |
jbe@115 | 436 AND "id" != "initiative_row"."id" |
jbe@115 | 437 ) THEN |
jbe@115 | 438 "event_v" := 'initiative_created_in_existing_issue'; |
jbe@115 | 439 ELSE |
jbe@115 | 440 "event_v" := 'initiative_created_in_new_issue'; |
jbe@115 | 441 END IF; |
jbe@115 | 442 END IF; |
jbe@115 | 443 INSERT INTO "event" ( |
jbe@115 | 444 "event", "member_id", |
jbe@115 | 445 "issue_id", "state", "initiative_id", "draft_id" |
jbe@115 | 446 ) VALUES ( |
jbe@115 | 447 "event_v", |
jbe@115 | 448 NEW."author_id", |
jbe@115 | 449 "initiative_row"."issue_id", |
jbe@115 | 450 "issue_row"."state", |
jbe@115 | 451 "initiative_row"."id", |
jbe@115 | 452 NEW."id" ); |
jbe@115 | 453 RETURN NULL; |
jbe@115 | 454 END; |
jbe@115 | 455 $$; |
jbe@115 | 456 |
jbe@115 | 457 CREATE TRIGGER "write_event_initiative_or_draft_created" |
jbe@115 | 458 AFTER INSERT ON "draft" FOR EACH ROW EXECUTE PROCEDURE |
jbe@115 | 459 "write_event_initiative_or_draft_created_trigger"(); |
jbe@115 | 460 |
jbe@115 | 461 COMMENT ON FUNCTION "write_event_initiative_or_draft_created_trigger"() IS 'Implementation of trigger "write_event_initiative_or_draft_created" on table "issue"'; |
jbe@115 | 462 COMMENT ON TRIGGER "write_event_initiative_or_draft_created" ON "draft" IS 'Create entry in "event" table on draft creation'; |
jbe@115 | 463 |
jbe@115 | 464 CREATE FUNCTION "write_event_initiative_revoked_trigger"() |
jbe@115 | 465 RETURNS TRIGGER |
jbe@115 | 466 LANGUAGE 'plpgsql' VOLATILE AS $$ |
jbe@115 | 467 DECLARE |
jbe@115 | 468 "issue_row" "issue"%ROWTYPE; |
jbe@115 | 469 BEGIN |
jbe@115 | 470 SELECT * INTO "issue_row" FROM "issue" |
jbe@115 | 471 WHERE "id" = NEW."issue_id"; |
jbe@115 | 472 IF OLD."revoked" ISNULL AND NEW."revoked" NOTNULL THEN |
jbe@115 | 473 INSERT INTO "event" ( |
jbe@115 | 474 "event", "member_id", "issue_id", "state", "initiative_id" |
jbe@115 | 475 ) VALUES ( |
jbe@115 | 476 'initiative_revoked', |
jbe@115 | 477 NEW."revoked_by_member_id", |
jbe@115 | 478 NEW."issue_id", |
jbe@115 | 479 "issue_row"."state", |
jbe@115 | 480 NEW."id" ); |
jbe@115 | 481 END IF; |
jbe@115 | 482 RETURN NULL; |
jbe@115 | 483 END; |
jbe@115 | 484 $$; |
jbe@115 | 485 |
jbe@115 | 486 CREATE TRIGGER "write_event_initiative_revoked" |
jbe@115 | 487 AFTER UPDATE ON "initiative" FOR EACH ROW EXECUTE PROCEDURE |
jbe@115 | 488 "write_event_initiative_revoked_trigger"(); |
jbe@115 | 489 |
jbe@115 | 490 COMMENT ON FUNCTION "write_event_initiative_revoked_trigger"() IS 'Implementation of trigger "write_event_initiative_revoked" on table "issue"'; |
jbe@115 | 491 COMMENT ON TRIGGER "write_event_initiative_revoked" ON "initiative" IS 'Create entry in "event" table, when an initiative is revoked'; |
jbe@115 | 492 |
jbe@115 | 493 CREATE FUNCTION "write_event_suggestion_created_trigger"() |
jbe@115 | 494 RETURNS TRIGGER |
jbe@115 | 495 LANGUAGE 'plpgsql' VOLATILE AS $$ |
jbe@115 | 496 DECLARE |
jbe@115 | 497 "initiative_row" "initiative"%ROWTYPE; |
jbe@115 | 498 "issue_row" "issue"%ROWTYPE; |
jbe@115 | 499 BEGIN |
jbe@115 | 500 SELECT * INTO "initiative_row" FROM "initiative" |
jbe@115 | 501 WHERE "id" = NEW."initiative_id"; |
jbe@115 | 502 SELECT * INTO "issue_row" FROM "issue" |
jbe@115 | 503 WHERE "id" = "initiative_row"."issue_id"; |
jbe@115 | 504 INSERT INTO "event" ( |
jbe@115 | 505 "event", "member_id", |
jbe@115 | 506 "issue_id", "state", "initiative_id", "suggestion_id" |
jbe@115 | 507 ) VALUES ( |
jbe@115 | 508 'suggestion_created', |
jbe@115 | 509 NEW."author_id", |
jbe@115 | 510 "initiative_row"."issue_id", |
jbe@115 | 511 "issue_row"."state", |
jbe@115 | 512 "initiative_row"."id", |
jbe@115 | 513 NEW."id" ); |
jbe@115 | 514 RETURN NULL; |
jbe@115 | 515 END; |
jbe@115 | 516 $$; |
jbe@115 | 517 |
jbe@115 | 518 CREATE TRIGGER "write_event_suggestion_created" |
jbe@115 | 519 AFTER INSERT ON "suggestion" FOR EACH ROW EXECUTE PROCEDURE |
jbe@115 | 520 "write_event_suggestion_created_trigger"(); |
jbe@115 | 521 |
jbe@115 | 522 COMMENT ON FUNCTION "write_event_suggestion_created_trigger"() IS 'Implementation of trigger "write_event_suggestion_created" on table "issue"'; |
jbe@115 | 523 COMMENT ON TRIGGER "write_event_suggestion_created" ON "suggestion" IS 'Create entry in "event" table on suggestion creation'; |
jbe@115 | 524 |
jbe@115 | 525 |
jbe@115 | 526 -- Modified views: |
jbe@115 | 527 |
jbe@115 | 528 CREATE VIEW "unit_delegation" AS |
jbe@115 | 529 SELECT |
jbe@115 | 530 "unit"."id" AS "unit_id", |
jbe@115 | 531 "delegation"."id", |
jbe@115 | 532 "delegation"."truster_id", |
jbe@115 | 533 "delegation"."trustee_id", |
jbe@115 | 534 "delegation"."scope" |
jbe@115 | 535 FROM "unit" |
jbe@115 | 536 JOIN "delegation" |
jbe@115 | 537 ON "delegation"."unit_id" = "unit"."id" |
jbe@115 | 538 JOIN "member" |
jbe@115 | 539 ON "delegation"."truster_id" = "member"."id" |
jbe@115 | 540 JOIN "privilege" |
jbe@115 | 541 ON "delegation"."unit_id" = "privilege"."unit_id" |
jbe@115 | 542 AND "delegation"."truster_id" = "privilege"."member_id" |
jbe@115 | 543 WHERE "member"."active" AND "privilege"."voting_right"; |
jbe@115 | 544 |
jbe@115 | 545 COMMENT ON VIEW "unit_delegation" IS 'Unit delegations where trusters are active and have voting right'; |
jbe@115 | 546 |
jbe@115 | 547 CREATE VIEW "area_delegation" AS |
jbe@115 | 548 SELECT DISTINCT ON ("area"."id", "delegation"."truster_id") |
jbe@115 | 549 "area"."id" AS "area_id", |
jbe@115 | 550 "delegation"."id", |
jbe@115 | 551 "delegation"."truster_id", |
jbe@115 | 552 "delegation"."trustee_id", |
jbe@115 | 553 "delegation"."scope" |
jbe@115 | 554 FROM "area" |
jbe@115 | 555 JOIN "delegation" |
jbe@115 | 556 ON "delegation"."unit_id" = "area"."unit_id" |
jbe@115 | 557 OR "delegation"."area_id" = "area"."id" |
jbe@115 | 558 JOIN "member" |
jbe@115 | 559 ON "delegation"."truster_id" = "member"."id" |
jbe@115 | 560 JOIN "privilege" |
jbe@115 | 561 ON "area"."unit_id" = "privilege"."unit_id" |
jbe@115 | 562 AND "delegation"."truster_id" = "privilege"."member_id" |
jbe@115 | 563 WHERE "member"."active" AND "privilege"."voting_right" |
jbe@115 | 564 ORDER BY |
jbe@115 | 565 "area"."id", |
jbe@115 | 566 "delegation"."truster_id", |
jbe@115 | 567 "delegation"."scope" DESC; |
jbe@115 | 568 |
jbe@115 | 569 COMMENT ON VIEW "area_delegation" IS 'Area delegations where trusters are active and have voting right'; |
jbe@115 | 570 |
jbe@115 | 571 CREATE VIEW "issue_delegation" AS |
jbe@115 | 572 SELECT DISTINCT ON ("issue"."id", "delegation"."truster_id") |
jbe@115 | 573 "issue"."id" AS "issue_id", |
jbe@115 | 574 "delegation"."id", |
jbe@115 | 575 "delegation"."truster_id", |
jbe@115 | 576 "delegation"."trustee_id", |
jbe@115 | 577 "delegation"."scope" |
jbe@115 | 578 FROM "issue" |
jbe@115 | 579 JOIN "area" |
jbe@115 | 580 ON "area"."id" = "issue"."area_id" |
jbe@115 | 581 JOIN "delegation" |
jbe@115 | 582 ON "delegation"."unit_id" = "area"."unit_id" |
jbe@115 | 583 OR "delegation"."area_id" = "area"."id" |
jbe@115 | 584 OR "delegation"."issue_id" = "issue"."id" |
jbe@115 | 585 JOIN "member" |
jbe@115 | 586 ON "delegation"."truster_id" = "member"."id" |
jbe@115 | 587 JOIN "privilege" |
jbe@115 | 588 ON "area"."unit_id" = "privilege"."unit_id" |
jbe@115 | 589 AND "delegation"."truster_id" = "privilege"."member_id" |
jbe@115 | 590 WHERE "member"."active" AND "privilege"."voting_right" |
jbe@115 | 591 ORDER BY |
jbe@115 | 592 "issue"."id", |
jbe@115 | 593 "delegation"."truster_id", |
jbe@115 | 594 "delegation"."scope" DESC; |
jbe@115 | 595 |
jbe@115 | 596 COMMENT ON VIEW "issue_delegation" IS 'Issue delegations where trusters are active and have voting right'; |
jbe@115 | 597 |
jbe@115 | 598 CREATE VIEW "unit_member_count" AS |
jbe@115 | 599 SELECT |
jbe@115 | 600 "unit"."id" AS "unit_id", |
jbe@115 | 601 sum("member"."id") AS "member_count" |
jbe@115 | 602 FROM "unit" |
jbe@115 | 603 LEFT JOIN "privilege" |
jbe@115 | 604 ON "privilege"."unit_id" = "unit"."id" |
jbe@115 | 605 AND "privilege"."voting_right" |
jbe@115 | 606 LEFT JOIN "member" |
jbe@115 | 607 ON "member"."id" = "privilege"."member_id" |
jbe@115 | 608 AND "member"."active" |
jbe@115 | 609 GROUP BY "unit"."id"; |
jbe@115 | 610 |
jbe@115 | 611 COMMENT ON VIEW "unit_member_count" IS 'View used to update "member_count" column of "unit" table'; |
jbe@115 | 612 |
jbe@115 | 613 DROP VIEW "area_member_count"; |
jbe@115 | 614 CREATE VIEW "area_member_count" AS |
jbe@115 | 615 SELECT |
jbe@115 | 616 "area"."id" AS "area_id", |
jbe@115 | 617 count("member"."id") AS "direct_member_count", |
jbe@115 | 618 coalesce( |
jbe@115 | 619 sum( |
jbe@115 | 620 CASE WHEN "member"."id" NOTNULL THEN |
jbe@115 | 621 "membership_weight"("area"."id", "member"."id") |
jbe@115 | 622 ELSE 0 END |
jbe@115 | 623 ) |
jbe@115 | 624 ) AS "member_weight", |
jbe@115 | 625 coalesce( |
jbe@115 | 626 sum( |
jbe@115 | 627 CASE WHEN "member"."id" NOTNULL AND "membership"."autoreject" THEN |
jbe@115 | 628 "membership_weight"("area"."id", "member"."id") |
jbe@115 | 629 ELSE 0 END |
jbe@115 | 630 ) |
jbe@115 | 631 ) AS "autoreject_weight" |
jbe@115 | 632 FROM "area" |
jbe@115 | 633 LEFT JOIN "membership" |
jbe@115 | 634 ON "area"."id" = "membership"."area_id" |
jbe@115 | 635 LEFT JOIN "privilege" |
jbe@115 | 636 ON "privilege"."unit_id" = "area"."unit_id" |
jbe@115 | 637 AND "privilege"."member_id" = "membership"."member_id" |
jbe@115 | 638 AND "privilege"."voting_right" |
jbe@115 | 639 LEFT JOIN "member" |
jbe@115 | 640 ON "member"."id" = "privilege"."member_id" -- NOTE: no membership here! |
jbe@115 | 641 AND "member"."active" |
jbe@115 | 642 GROUP BY "area"."id"; |
jbe@115 | 643 |
jbe@115 | 644 COMMENT ON VIEW "area_member_count" IS 'View used to update "direct_member_count", "member_weight" and "autoreject_weight" columns of table "area"'; |
jbe@115 | 645 |
jbe@115 | 646 |
jbe@115 | 647 -- New view "event_seen_by_member": |
jbe@115 | 648 |
jbe@115 | 649 CREATE VIEW "event_seen_by_member" AS |
jbe@115 | 650 SELECT |
jbe@115 | 651 "member"."id" AS "seen_by_member_id", |
jbe@115 | 652 CASE WHEN "event"."state" IN ( |
jbe@115 | 653 'voting', |
jbe@115 | 654 'finished_without_winner', |
jbe@115 | 655 'finished_with_winner' |
jbe@115 | 656 ) THEN |
jbe@115 | 657 'voting'::"notify_level" |
jbe@115 | 658 ELSE |
jbe@115 | 659 CASE WHEN "event"."state" IN ( |
jbe@115 | 660 'verification', |
jbe@115 | 661 'canceled_after_revocation_during_verification', |
jbe@115 | 662 'canceled_no_initiative_admitted' |
jbe@115 | 663 ) THEN |
jbe@115 | 664 'verification'::"notify_level" |
jbe@115 | 665 ELSE |
jbe@115 | 666 CASE WHEN "event"."state" IN ( |
jbe@115 | 667 'discussion', |
jbe@115 | 668 'canceled_after_revocation_during_discussion' |
jbe@115 | 669 ) THEN |
jbe@115 | 670 'discussion'::"notify_level" |
jbe@115 | 671 ELSE |
jbe@115 | 672 'all'::"notify_level" |
jbe@115 | 673 END |
jbe@115 | 674 END |
jbe@115 | 675 END AS "notify_level", |
jbe@115 | 676 "event".* |
jbe@115 | 677 FROM "member" CROSS JOIN "event" |
jbe@115 | 678 LEFT JOIN "issue" |
jbe@115 | 679 ON "event"."issue_id" = "issue"."id" |
jbe@115 | 680 LEFT JOIN "membership" |
jbe@115 | 681 ON "member"."id" = "membership"."member_id" |
jbe@115 | 682 AND "issue"."area_id" = "membership"."area_id" |
jbe@115 | 683 LEFT JOIN "interest" |
jbe@115 | 684 ON "member"."id" = "interest"."member_id" |
jbe@115 | 685 AND "event"."issue_id" = "interest"."issue_id" |
jbe@115 | 686 LEFT JOIN "supporter" |
jbe@115 | 687 ON "member"."id" = "supporter"."member_id" |
jbe@115 | 688 AND "event"."initiative_id" = "supporter"."initiative_id" |
jbe@115 | 689 LEFT JOIN "ignored_member" |
jbe@115 | 690 ON "member"."id" = "ignored_member"."member_id" |
jbe@115 | 691 AND "event"."member_id" = "ignored_member"."other_member_id" |
jbe@115 | 692 LEFT JOIN "ignored_initiative" |
jbe@115 | 693 ON "member"."id" = "ignored_initiative"."member_id" |
jbe@115 | 694 AND "event"."initiative_id" = "ignored_initiative"."initiative_id" |
jbe@115 | 695 WHERE ( |
jbe@115 | 696 "supporter"."member_id" NOTNULL OR |
jbe@115 | 697 "interest"."member_id" NOTNULL OR |
jbe@115 | 698 ( "membership"."member_id" NOTNULL AND |
jbe@115 | 699 "event"."event" IN ( |
jbe@115 | 700 'issue_state_changed', |
jbe@115 | 701 'initiative_created_in_new_issue', |
jbe@115 | 702 'initiative_created_in_existing_issue', |
jbe@115 | 703 'initiative_revoked' ) ) ) |
jbe@115 | 704 AND "ignored_member"."member_id" ISNULL |
jbe@115 | 705 AND "ignored_initiative"."member_id" ISNULL; |
jbe@115 | 706 |
jbe@115 | 707 COMMENT ON VIEW "event_seen_by_member" IS 'Events as seen by a member, depending on its memberships, interests and support'; |
jbe@115 | 708 |
jbe@115 | 709 |
jbe@115 | 710 -- New view "pending_notification": |
jbe@115 | 711 |
jbe@115 | 712 CREATE VIEW "pending_notification" AS |
jbe@115 | 713 SELECT |
jbe@115 | 714 "member"."id" AS "seen_by_member_id", |
jbe@115 | 715 "event".* |
jbe@115 | 716 FROM "member" CROSS JOIN "event" |
jbe@115 | 717 LEFT JOIN "issue" |
jbe@115 | 718 ON "event"."issue_id" = "issue"."id" |
jbe@115 | 719 LEFT JOIN "membership" |
jbe@115 | 720 ON "member"."id" = "membership"."member_id" |
jbe@115 | 721 AND "issue"."area_id" = "membership"."area_id" |
jbe@115 | 722 LEFT JOIN "interest" |
jbe@115 | 723 ON "member"."id" = "interest"."member_id" |
jbe@115 | 724 AND "event"."issue_id" = "interest"."issue_id" |
jbe@115 | 725 LEFT JOIN "supporter" |
jbe@115 | 726 ON "member"."id" = "supporter"."member_id" |
jbe@115 | 727 AND "event"."initiative_id" = "supporter"."initiative_id" |
jbe@115 | 728 LEFT JOIN "ignored_member" |
jbe@115 | 729 ON "member"."id" = "ignored_member"."member_id" |
jbe@115 | 730 AND "event"."member_id" = "ignored_member"."other_member_id" |
jbe@115 | 731 LEFT JOIN "ignored_initiative" |
jbe@115 | 732 ON "member"."id" = "ignored_initiative"."member_id" |
jbe@115 | 733 AND "event"."initiative_id" = "ignored_initiative"."initiative_id" |
jbe@115 | 734 WHERE ( |
jbe@115 | 735 "member"."notify_event_id" ISNULL OR |
jbe@115 | 736 ( "member"."notify_event_id" NOTNULL AND |
jbe@115 | 737 "member"."notify_event_id" < "event"."id" ) ) |
jbe@115 | 738 AND ( |
jbe@115 | 739 ( "member"."notify_level" >= 'all' ) OR |
jbe@115 | 740 ( "member"."notify_level" >= 'voting' AND |
jbe@115 | 741 "event"."state" IN ( |
jbe@115 | 742 'voting', |
jbe@115 | 743 'finished_without_winner', |
jbe@115 | 744 'finished_with_winner' ) ) OR |
jbe@115 | 745 ( "member"."notify_level" >= 'verification' AND |
jbe@115 | 746 "event"."state" IN ( |
jbe@115 | 747 'verification', |
jbe@115 | 748 'canceled_after_revocation_during_verification', |
jbe@115 | 749 'canceled_no_initiative_admitted' ) ) OR |
jbe@115 | 750 ( "member"."notify_level" >= 'discussion' AND |
jbe@115 | 751 "event"."state" IN ( |
jbe@115 | 752 'discussion', |
jbe@115 | 753 'canceled_after_revocation_during_discussion' ) ) ) |
jbe@115 | 754 AND ( |
jbe@115 | 755 "supporter"."member_id" NOTNULL OR |
jbe@115 | 756 "interest"."member_id" NOTNULL OR |
jbe@115 | 757 ( "membership"."member_id" NOTNULL AND |
jbe@115 | 758 "event"."event" IN ( |
jbe@115 | 759 'issue_state_changed', |
jbe@115 | 760 'initiative_created_in_new_issue', |
jbe@115 | 761 'initiative_created_in_existing_issue', |
jbe@115 | 762 'initiative_revoked' ) ) ) |
jbe@115 | 763 AND "ignored_member"."member_id" ISNULL |
jbe@115 | 764 AND "ignored_initiative"."member_id" ISNULL; |
jbe@115 | 765 |
jbe@115 | 766 COMMENT ON VIEW "pending_notification" IS 'Events to be sent to "notify_email" address of member referred to by "seen_by_member_id"'; |
jbe@115 | 767 |
jbe@115 | 768 |
jbe@115 | 769 COMMENT ON TYPE "timeline_event" IS 'Types of event in timeline tables (DEPRECATED)'; |
jbe@115 | 770 COMMENT ON VIEW "timeline_issue" IS 'Helper view for "timeline" view (DEPRECATED)'; |
jbe@115 | 771 COMMENT ON VIEW "timeline_initiative" IS 'Helper view for "timeline" view (DEPRECATED)'; |
jbe@115 | 772 COMMENT ON VIEW "timeline_draft" IS 'Helper view for "timeline" view (DEPRECATED)'; |
jbe@115 | 773 COMMENT ON VIEW "timeline_suggestion" IS 'Helper view for "timeline" view (DEPRECATED)'; |
jbe@115 | 774 COMMENT ON VIEW "timeline" IS 'Aggregation of different events in the system (DEPRECATED)'; |
jbe@115 | 775 |
jbe@115 | 776 |
jbe@115 | 777 -- Modified "delegation_chain" functions: |
jbe@115 | 778 |
jbe@115 | 779 CREATE TYPE "delegation_chain_row" AS ( |
jbe@115 | 780 "index" INT4, |
jbe@115 | 781 "member_id" INT4, |
jbe@115 | 782 "member_valid" BOOLEAN, |
jbe@115 | 783 "participation" BOOLEAN, |
jbe@115 | 784 "overridden" BOOLEAN, |
jbe@115 | 785 "scope_in" "delegation_scope", |
jbe@115 | 786 "scope_out" "delegation_scope", |
jbe@115 | 787 "disabled_out" BOOLEAN, |
jbe@115 | 788 "loop" "delegation_chain_loop_tag" ); |
jbe@115 | 789 |
jbe@115 | 790 COMMENT ON TYPE "delegation_chain_row" IS 'Type of rows returned by "delegation_chain"(...) functions'; |
jbe@115 | 791 |
jbe@115 | 792 COMMENT ON COLUMN "delegation_chain_row"."index" IS 'Index starting with 0 and counting up'; |
jbe@115 | 793 COMMENT ON COLUMN "delegation_chain_row"."participation" IS 'In case of delegation chains for issues: interest, for areas: membership, for global delegation chains: always null'; |
jbe@115 | 794 COMMENT ON COLUMN "delegation_chain_row"."overridden" IS 'True, if an entry with lower index has "participation" set to true'; |
jbe@115 | 795 COMMENT ON COLUMN "delegation_chain_row"."scope_in" IS 'Scope of used incoming delegation'; |
jbe@115 | 796 COMMENT ON COLUMN "delegation_chain_row"."scope_out" IS 'Scope of used outgoing delegation'; |
jbe@115 | 797 COMMENT ON COLUMN "delegation_chain_row"."disabled_out" IS 'Outgoing delegation is explicitly disabled by a delegation with trustee_id set to NULL'; |
jbe@115 | 798 COMMENT ON COLUMN "delegation_chain_row"."loop" IS 'Not null, if member is part of a loop, see "delegation_chain_loop_tag" type'; |
jbe@115 | 799 |
jbe@115 | 800 |
jbe@115 | 801 CREATE FUNCTION "delegation_chain" |
jbe@115 | 802 ( "member_id_p" "member"."id"%TYPE, |
jbe@115 | 803 "unit_id_p" "unit"."id"%TYPE, |
jbe@115 | 804 "area_id_p" "area"."id"%TYPE, |
jbe@115 | 805 "issue_id_p" "issue"."id"%TYPE, |
jbe@115 | 806 "simulate_trustee_id_p" "member"."id"%TYPE ) |
jbe@115 | 807 RETURNS SETOF "delegation_chain_row" |
jbe@115 | 808 LANGUAGE 'plpgsql' STABLE AS $$ |
jbe@115 | 809 DECLARE |
jbe@115 | 810 "scope_v" "delegation_scope"; |
jbe@115 | 811 "unit_id_v" "unit"."id"%TYPE; |
jbe@115 | 812 "area_id_v" "area"."id"%TYPE; |
jbe@115 | 813 "visited_member_ids" INT4[]; -- "member"."id"%TYPE[] |
jbe@115 | 814 "loop_member_id_v" "member"."id"%TYPE; |
jbe@115 | 815 "output_row" "delegation_chain_row"; |
jbe@115 | 816 "output_rows" "delegation_chain_row"[]; |
jbe@115 | 817 "delegation_row" "delegation"%ROWTYPE; |
jbe@115 | 818 "row_count" INT4; |
jbe@115 | 819 "i" INT4; |
jbe@115 | 820 "loop_v" BOOLEAN; |
jbe@115 | 821 BEGIN |
jbe@115 | 822 IF |
jbe@115 | 823 "unit_id_p" NOTNULL AND |
jbe@115 | 824 "area_id_p" ISNULL AND |
jbe@115 | 825 "issue_id_p" ISNULL |
jbe@115 | 826 THEN |
jbe@115 | 827 "scope_v" := 'unit'; |
jbe@115 | 828 "unit_id_v" := "unit_id_p"; |
jbe@115 | 829 ELSIF |
jbe@115 | 830 "unit_id_p" ISNULL AND |
jbe@115 | 831 "area_id_p" NOTNULL AND |
jbe@115 | 832 "issue_id_p" ISNULL |
jbe@115 | 833 THEN |
jbe@115 | 834 "scope_v" := 'area'; |
jbe@115 | 835 "area_id_v" := "area_id_p"; |
jbe@115 | 836 SELECT "unit_id" INTO "unit_id_v" |
jbe@115 | 837 FROM "area" WHERE "id" = "area_id_v"; |
jbe@115 | 838 ELSIF |
jbe@115 | 839 "unit_id_p" ISNULL AND |
jbe@115 | 840 "area_id_p" ISNULL AND |
jbe@115 | 841 "issue_id_p" NOTNULL |
jbe@115 | 842 THEN |
jbe@115 | 843 "scope_v" := 'issue'; |
jbe@115 | 844 SELECT "area_id" INTO "area_id_v" |
jbe@115 | 845 FROM "issue" WHERE "id" = "issue_id_p"; |
jbe@115 | 846 SELECT "unit_id" INTO "unit_id_v" |
jbe@115 | 847 FROM "area" WHERE "id" = "area_id_v"; |
jbe@115 | 848 ELSE |
jbe@115 | 849 RAISE EXCEPTION 'Exactly one of unit_id_p, area_id_p, or issue_id_p must be NOTNULL.'; |
jbe@115 | 850 END IF; |
jbe@115 | 851 "visited_member_ids" := '{}'; |
jbe@115 | 852 "loop_member_id_v" := NULL; |
jbe@115 | 853 "output_rows" := '{}'; |
jbe@115 | 854 "output_row"."index" := 0; |
jbe@115 | 855 "output_row"."member_id" := "member_id_p"; |
jbe@115 | 856 "output_row"."member_valid" := TRUE; |
jbe@115 | 857 "output_row"."participation" := FALSE; |
jbe@115 | 858 "output_row"."overridden" := FALSE; |
jbe@115 | 859 "output_row"."disabled_out" := FALSE; |
jbe@115 | 860 "output_row"."scope_out" := NULL; |
jbe@115 | 861 LOOP |
jbe@115 | 862 IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN |
jbe@115 | 863 "loop_member_id_v" := "output_row"."member_id"; |
jbe@115 | 864 ELSE |
jbe@115 | 865 "visited_member_ids" := |
jbe@115 | 866 "visited_member_ids" || "output_row"."member_id"; |
jbe@115 | 867 END IF; |
jbe@115 | 868 IF "output_row"."participation" THEN |
jbe@115 | 869 "output_row"."overridden" := TRUE; |
jbe@115 | 870 END IF; |
jbe@115 | 871 "output_row"."scope_in" := "output_row"."scope_out"; |
jbe@115 | 872 IF EXISTS ( |
jbe@115 | 873 SELECT NULL FROM "member" JOIN "privilege" |
jbe@115 | 874 ON "privilege"."member_id" = "member"."id" |
jbe@115 | 875 AND "privilege"."unit_id" = "unit_id_v" |
jbe@115 | 876 WHERE "id" = "output_row"."member_id" |
jbe@115 | 877 AND "member"."active" AND "privilege"."voting_right" |
jbe@115 | 878 ) THEN |
jbe@115 | 879 IF "scope_v" = 'unit' THEN |
jbe@115 | 880 SELECT * INTO "delegation_row" FROM "delegation" |
jbe@115 | 881 WHERE "truster_id" = "output_row"."member_id" |
jbe@115 | 882 AND "unit_id" = "unit_id_v"; |
jbe@115 | 883 ELSIF "scope_v" = 'area' THEN |
jbe@115 | 884 "output_row"."participation" := EXISTS ( |
jbe@115 | 885 SELECT NULL FROM "membership" |
jbe@115 | 886 WHERE "area_id" = "area_id_p" |
jbe@115 | 887 AND "member_id" = "output_row"."member_id" |
jbe@115 | 888 ); |
jbe@115 | 889 SELECT * INTO "delegation_row" FROM "delegation" |
jbe@115 | 890 WHERE "truster_id" = "output_row"."member_id" |
jbe@115 | 891 AND ( |
jbe@115 | 892 "unit_id" = "unit_id_v" OR |
jbe@115 | 893 "area_id" = "area_id_v" |
jbe@115 | 894 ) |
jbe@115 | 895 ORDER BY "scope" DESC; |
jbe@115 | 896 ELSIF "scope_v" = 'issue' THEN |
jbe@115 | 897 "output_row"."participation" := EXISTS ( |
jbe@115 | 898 SELECT NULL FROM "interest" |
jbe@115 | 899 WHERE "issue_id" = "issue_id_p" |
jbe@115 | 900 AND "member_id" = "output_row"."member_id" |
jbe@115 | 901 ); |
jbe@115 | 902 SELECT * INTO "delegation_row" FROM "delegation" |
jbe@115 | 903 WHERE "truster_id" = "output_row"."member_id" |
jbe@115 | 904 AND ( |
jbe@115 | 905 "unit_id" = "unit_id_v" OR |
jbe@115 | 906 "area_id" = "area_id_v" OR |
jbe@115 | 907 "issue_id" = "issue_id_p" |
jbe@115 | 908 ) |
jbe@115 | 909 ORDER BY "scope" DESC; |
jbe@115 | 910 END IF; |
jbe@115 | 911 ELSE |
jbe@115 | 912 "output_row"."member_valid" := FALSE; |
jbe@115 | 913 "output_row"."participation" := FALSE; |
jbe@115 | 914 "output_row"."scope_out" := NULL; |
jbe@115 | 915 "delegation_row" := ROW(NULL); |
jbe@115 | 916 END IF; |
jbe@115 | 917 IF |
jbe@115 | 918 "output_row"."member_id" = "member_id_p" AND |
jbe@115 | 919 "simulate_trustee_id_p" NOTNULL |
jbe@115 | 920 THEN |
jbe@115 | 921 "output_row"."scope_out" := "scope_v"; |
jbe@115 | 922 "output_rows" := "output_rows" || "output_row"; |
jbe@115 | 923 "output_row"."member_id" := "simulate_trustee_id_p"; |
jbe@115 | 924 ELSIF "delegation_row"."trustee_id" NOTNULL THEN |
jbe@115 | 925 "output_row"."scope_out" := "delegation_row"."scope"; |
jbe@115 | 926 "output_rows" := "output_rows" || "output_row"; |
jbe@115 | 927 "output_row"."member_id" := "delegation_row"."trustee_id"; |
jbe@115 | 928 ELSIF "delegation_row"."scope" NOTNULL THEN |
jbe@115 | 929 "output_row"."scope_out" := "delegation_row"."scope"; |
jbe@115 | 930 "output_row"."disabled_out" := TRUE; |
jbe@115 | 931 "output_rows" := "output_rows" || "output_row"; |
jbe@115 | 932 EXIT; |
jbe@115 | 933 ELSE |
jbe@115 | 934 "output_row"."scope_out" := NULL; |
jbe@115 | 935 "output_rows" := "output_rows" || "output_row"; |
jbe@115 | 936 EXIT; |
jbe@115 | 937 END IF; |
jbe@115 | 938 EXIT WHEN "loop_member_id_v" NOTNULL; |
jbe@115 | 939 "output_row"."index" := "output_row"."index" + 1; |
jbe@115 | 940 END LOOP; |
jbe@115 | 941 "row_count" := array_upper("output_rows", 1); |
jbe@115 | 942 "i" := 1; |
jbe@115 | 943 "loop_v" := FALSE; |
jbe@115 | 944 LOOP |
jbe@115 | 945 "output_row" := "output_rows"["i"]; |
jbe@115 | 946 EXIT WHEN "output_row" ISNULL; -- NOTE: ISNULL and NOT ... NOTNULL produce different results! |
jbe@115 | 947 IF "loop_v" THEN |
jbe@115 | 948 IF "i" + 1 = "row_count" THEN |
jbe@115 | 949 "output_row"."loop" := 'last'; |
jbe@115 | 950 ELSIF "i" = "row_count" THEN |
jbe@115 | 951 "output_row"."loop" := 'repetition'; |
jbe@115 | 952 ELSE |
jbe@115 | 953 "output_row"."loop" := 'intermediate'; |
jbe@115 | 954 END IF; |
jbe@115 | 955 ELSIF "output_row"."member_id" = "loop_member_id_v" THEN |
jbe@115 | 956 "output_row"."loop" := 'first'; |
jbe@115 | 957 "loop_v" := TRUE; |
jbe@115 | 958 END IF; |
jbe@115 | 959 IF "scope_v" = 'unit' THEN |
jbe@115 | 960 "output_row"."participation" := NULL; |
jbe@115 | 961 END IF; |
jbe@115 | 962 RETURN NEXT "output_row"; |
jbe@115 | 963 "i" := "i" + 1; |
jbe@115 | 964 END LOOP; |
jbe@115 | 965 RETURN; |
jbe@115 | 966 END; |
jbe@115 | 967 $$; |
jbe@115 | 968 |
jbe@115 | 969 COMMENT ON FUNCTION "delegation_chain" |
jbe@115 | 970 ( "member"."id"%TYPE, |
jbe@115 | 971 "unit"."id"%TYPE, |
jbe@115 | 972 "area"."id"%TYPE, |
jbe@115 | 973 "issue"."id"%TYPE, |
jbe@115 | 974 "member"."id"%TYPE ) |
jbe@115 | 975 IS 'Helper function for frontends to display delegation chains; Not part of internal voting logic'; |
jbe@115 | 976 |
jbe@115 | 977 |
jbe@115 | 978 CREATE FUNCTION "delegation_chain" |
jbe@115 | 979 ( "member_id_p" "member"."id"%TYPE, |
jbe@115 | 980 "unit_id_p" "unit"."id"%TYPE, |
jbe@115 | 981 "area_id_p" "area"."id"%TYPE, |
jbe@115 | 982 "issue_id_p" "issue"."id"%TYPE ) |
jbe@115 | 983 RETURNS SETOF "delegation_chain_row" |
jbe@115 | 984 LANGUAGE 'plpgsql' STABLE AS $$ |
jbe@115 | 985 DECLARE |
jbe@115 | 986 "result_row" "delegation_chain_row"; |
jbe@115 | 987 BEGIN |
jbe@115 | 988 FOR "result_row" IN |
jbe@115 | 989 SELECT * FROM "delegation_chain"( |
jbe@115 | 990 "member_id_p", "area_id_p", "issue_id_p", NULL |
jbe@115 | 991 ) |
jbe@115 | 992 LOOP |
jbe@115 | 993 RETURN NEXT "result_row"; |
jbe@115 | 994 END LOOP; |
jbe@115 | 995 RETURN; |
jbe@115 | 996 END; |
jbe@115 | 997 $$; |
jbe@115 | 998 |
jbe@115 | 999 COMMENT ON FUNCTION "delegation_chain" |
jbe@115 | 1000 ( "member"."id"%TYPE, |
jbe@115 | 1001 "unit"."id"%TYPE, |
jbe@115 | 1002 "area"."id"%TYPE, |
jbe@115 | 1003 "issue"."id"%TYPE ) |
jbe@115 | 1004 IS 'Shortcut for "delegation_chain"(...) function where 4th parameter is null'; |
jbe@115 | 1005 |
jbe@115 | 1006 |
jbe@115 | 1007 -- Modified core functions: |
jbe@115 | 1008 |
jbe@115 | 1009 CREATE OR REPLACE FUNCTION "lock_issue" |
jbe@115 | 1010 ( "issue_id_p" "issue"."id"%TYPE ) |
jbe@115 | 1011 RETURNS VOID |
jbe@115 | 1012 LANGUAGE 'plpgsql' VOLATILE AS $$ |
jbe@115 | 1013 BEGIN |
jbe@115 | 1014 LOCK TABLE "member" IN SHARE MODE; |
jbe@115 | 1015 LOCK TABLE "privilege" IN SHARE MODE; |
jbe@115 | 1016 LOCK TABLE "membership" IN SHARE MODE; |
jbe@115 | 1017 LOCK TABLE "policy" IN SHARE MODE; |
jbe@115 | 1018 PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE; |
jbe@115 | 1019 -- NOTE: The row-level exclusive lock in combination with the |
jbe@115 | 1020 -- share_row_lock_issue(_via_initiative)_trigger functions (which |
jbe@115 | 1021 -- acquire a row-level share lock on the issue) ensure that no data |
jbe@115 | 1022 -- is changed, which could affect calculation of snapshots or |
jbe@115 | 1023 -- counting of votes. Table "delegation" must be table-level-locked, |
jbe@115 | 1024 -- as it also contains issue- and global-scope delegations. |
jbe@115 | 1025 LOCK TABLE "delegation" IN SHARE MODE; |
jbe@115 | 1026 LOCK TABLE "direct_population_snapshot" IN EXCLUSIVE MODE; |
jbe@115 | 1027 LOCK TABLE "delegating_population_snapshot" IN EXCLUSIVE MODE; |
jbe@115 | 1028 LOCK TABLE "direct_interest_snapshot" IN EXCLUSIVE MODE; |
jbe@115 | 1029 LOCK TABLE "delegating_interest_snapshot" IN EXCLUSIVE MODE; |
jbe@115 | 1030 LOCK TABLE "direct_supporter_snapshot" IN EXCLUSIVE MODE; |
jbe@115 | 1031 RETURN; |
jbe@115 | 1032 END; |
jbe@115 | 1033 $$; |
jbe@115 | 1034 |
jbe@115 | 1035 CREATE OR REPLACE FUNCTION "calculate_member_counts"() |
jbe@115 | 1036 RETURNS VOID |
jbe@115 | 1037 LANGUAGE 'plpgsql' VOLATILE AS $$ |
jbe@115 | 1038 BEGIN |
jbe@115 | 1039 LOCK TABLE "member" IN SHARE MODE; |
jbe@115 | 1040 LOCK TABLE "member_count" IN EXCLUSIVE MODE; |
jbe@115 | 1041 LOCK TABLE "unit" IN EXCLUSIVE MODE; |
jbe@115 | 1042 LOCK TABLE "area" IN EXCLUSIVE MODE; |
jbe@115 | 1043 LOCK TABLE "privilege" IN SHARE MODE; |
jbe@115 | 1044 LOCK TABLE "membership" IN SHARE MODE; |
jbe@115 | 1045 DELETE FROM "member_count"; |
jbe@115 | 1046 INSERT INTO "member_count" ("total_count") |
jbe@115 | 1047 SELECT "total_count" FROM "member_count_view"; |
jbe@115 | 1048 UPDATE "unit" SET "member_count" = "view"."member_count" |
jbe@115 | 1049 FROM "unit_member_count" AS "view" |
jbe@115 | 1050 WHERE "view"."unit_id" = "unit"."id"; |
jbe@115 | 1051 UPDATE "area" SET |
jbe@115 | 1052 "direct_member_count" = "view"."direct_member_count", |
jbe@115 | 1053 "member_weight" = "view"."member_weight", |
jbe@115 | 1054 "autoreject_weight" = "view"."autoreject_weight" |
jbe@115 | 1055 FROM "area_member_count" AS "view" |
jbe@115 | 1056 WHERE "view"."area_id" = "area"."id"; |
jbe@115 | 1057 RETURN; |
jbe@115 | 1058 END; |
jbe@115 | 1059 $$; |
jbe@115 | 1060 |
jbe@115 | 1061 CREATE OR REPLACE FUNCTION "create_population_snapshot" |
jbe@115 | 1062 ( "issue_id_p" "issue"."id"%TYPE ) |
jbe@115 | 1063 RETURNS VOID |
jbe@115 | 1064 LANGUAGE 'plpgsql' VOLATILE AS $$ |
jbe@115 | 1065 DECLARE |
jbe@115 | 1066 "member_id_v" "member"."id"%TYPE; |
jbe@115 | 1067 BEGIN |
jbe@115 | 1068 DELETE FROM "direct_population_snapshot" |
jbe@115 | 1069 WHERE "issue_id" = "issue_id_p" |
jbe@115 | 1070 AND "event" = 'periodic'; |
jbe@115 | 1071 DELETE FROM "delegating_population_snapshot" |
jbe@115 | 1072 WHERE "issue_id" = "issue_id_p" |
jbe@115 | 1073 AND "event" = 'periodic'; |
jbe@115 | 1074 INSERT INTO "direct_population_snapshot" |
jbe@115 | 1075 ("issue_id", "event", "member_id") |
jbe@115 | 1076 SELECT |
jbe@115 | 1077 "issue_id_p" AS "issue_id", |
jbe@115 | 1078 'periodic'::"snapshot_event" AS "event", |
jbe@115 | 1079 "member"."id" AS "member_id" |
jbe@115 | 1080 FROM "issue" |
jbe@115 | 1081 JOIN "area" ON "issue"."area_id" = "area"."id" |
jbe@115 | 1082 JOIN "membership" ON "area"."id" = "membership"."area_id" |
jbe@115 | 1083 JOIN "member" ON "membership"."member_id" = "member"."id" |
jbe@115 | 1084 JOIN "privilege" |
jbe@115 | 1085 ON "privilege"."unit_id" = "area"."unit_id" |
jbe@115 | 1086 AND "privilege"."member_id" = "member"."id" |
jbe@115 | 1087 WHERE "issue"."id" = "issue_id_p" |
jbe@115 | 1088 AND "member"."active" AND "privilege"."voting_right" |
jbe@115 | 1089 UNION |
jbe@115 | 1090 SELECT |
jbe@115 | 1091 "issue_id_p" AS "issue_id", |
jbe@115 | 1092 'periodic'::"snapshot_event" AS "event", |
jbe@115 | 1093 "member"."id" AS "member_id" |
jbe@115 | 1094 FROM "issue" |
jbe@115 | 1095 JOIN "area" ON "issue"."area_id" = "area"."id" |
jbe@115 | 1096 JOIN "interest" ON "issue"."id" = "interest"."issue_id" |
jbe@115 | 1097 JOIN "member" ON "interest"."member_id" = "member"."id" |
jbe@115 | 1098 JOIN "privilege" |
jbe@115 | 1099 ON "privilege"."unit_id" = "area"."unit_id" |
jbe@115 | 1100 AND "privilege"."member_id" = "member"."id" |
jbe@115 | 1101 WHERE "issue"."id" = "issue_id_p" |
jbe@115 | 1102 AND "member"."active" AND "privilege"."voting_right"; |
jbe@115 | 1103 FOR "member_id_v" IN |
jbe@115 | 1104 SELECT "member_id" FROM "direct_population_snapshot" |
jbe@115 | 1105 WHERE "issue_id" = "issue_id_p" |
jbe@115 | 1106 AND "event" = 'periodic' |
jbe@115 | 1107 LOOP |
jbe@115 | 1108 UPDATE "direct_population_snapshot" SET |
jbe@115 | 1109 "weight" = 1 + |
jbe@115 | 1110 "weight_of_added_delegations_for_population_snapshot"( |
jbe@115 | 1111 "issue_id_p", |
jbe@115 | 1112 "member_id_v", |
jbe@115 | 1113 '{}' |
jbe@115 | 1114 ) |
jbe@115 | 1115 WHERE "issue_id" = "issue_id_p" |
jbe@115 | 1116 AND "event" = 'periodic' |
jbe@115 | 1117 AND "member_id" = "member_id_v"; |
jbe@115 | 1118 END LOOP; |
jbe@115 | 1119 RETURN; |
jbe@115 | 1120 END; |
jbe@115 | 1121 $$; |
jbe@115 | 1122 |
jbe@115 | 1123 CREATE OR REPLACE FUNCTION "create_interest_snapshot" |
jbe@115 | 1124 ( "issue_id_p" "issue"."id"%TYPE ) |
jbe@115 | 1125 RETURNS VOID |
jbe@115 | 1126 LANGUAGE 'plpgsql' VOLATILE AS $$ |
jbe@115 | 1127 DECLARE |
jbe@115 | 1128 "member_id_v" "member"."id"%TYPE; |
jbe@115 | 1129 BEGIN |
jbe@115 | 1130 DELETE FROM "direct_interest_snapshot" |
jbe@115 | 1131 WHERE "issue_id" = "issue_id_p" |
jbe@115 | 1132 AND "event" = 'periodic'; |
jbe@115 | 1133 DELETE FROM "delegating_interest_snapshot" |
jbe@115 | 1134 WHERE "issue_id" = "issue_id_p" |
jbe@115 | 1135 AND "event" = 'periodic'; |
jbe@115 | 1136 DELETE FROM "direct_supporter_snapshot" |
jbe@115 | 1137 WHERE "issue_id" = "issue_id_p" |
jbe@115 | 1138 AND "event" = 'periodic'; |
jbe@115 | 1139 INSERT INTO "direct_interest_snapshot" |
jbe@115 | 1140 ("issue_id", "event", "member_id", "voting_requested") |
jbe@115 | 1141 SELECT |
jbe@115 | 1142 "issue_id_p" AS "issue_id", |
jbe@115 | 1143 'periodic' AS "event", |
jbe@115 | 1144 "member"."id" AS "member_id", |
jbe@115 | 1145 "interest"."voting_requested" |
jbe@115 | 1146 FROM "issue" |
jbe@115 | 1147 JOIN "area" ON "issue"."area_id" = "area"."id" |
jbe@115 | 1148 JOIN "interest" ON "issue"."id" = "interest"."issue_id" |
jbe@115 | 1149 JOIN "member" ON "interest"."member_id" = "member"."id" |
jbe@115 | 1150 JOIN "privilege" |
jbe@115 | 1151 ON "privilege"."unit_id" = "area"."unit_id" |
jbe@115 | 1152 AND "privilege"."member_id" = "member"."id" |
jbe@115 | 1153 WHERE "issue"."id" = "issue_id_p" |
jbe@115 | 1154 AND "member"."active" AND "privilege"."voting_right"; |
jbe@115 | 1155 FOR "member_id_v" IN |
jbe@115 | 1156 SELECT "member_id" FROM "direct_interest_snapshot" |
jbe@115 | 1157 WHERE "issue_id" = "issue_id_p" |
jbe@115 | 1158 AND "event" = 'periodic' |
jbe@115 | 1159 LOOP |
jbe@115 | 1160 UPDATE "direct_interest_snapshot" SET |
jbe@115 | 1161 "weight" = 1 + |
jbe@115 | 1162 "weight_of_added_delegations_for_interest_snapshot"( |
jbe@115 | 1163 "issue_id_p", |
jbe@115 | 1164 "member_id_v", |
jbe@115 | 1165 '{}' |
jbe@115 | 1166 ) |
jbe@115 | 1167 WHERE "issue_id" = "issue_id_p" |
jbe@115 | 1168 AND "event" = 'periodic' |
jbe@115 | 1169 AND "member_id" = "member_id_v"; |
jbe@115 | 1170 END LOOP; |
jbe@115 | 1171 INSERT INTO "direct_supporter_snapshot" |
jbe@115 | 1172 ( "issue_id", "initiative_id", "event", "member_id", |
jbe@115 | 1173 "informed", "satisfied" ) |
jbe@115 | 1174 SELECT |
jbe@115 | 1175 "issue_id_p" AS "issue_id", |
jbe@115 | 1176 "initiative"."id" AS "initiative_id", |
jbe@115 | 1177 'periodic' AS "event", |
jbe@115 | 1178 "supporter"."member_id" AS "member_id", |
jbe@115 | 1179 "supporter"."draft_id" = "current_draft"."id" AS "informed", |
jbe@115 | 1180 NOT EXISTS ( |
jbe@115 | 1181 SELECT NULL FROM "critical_opinion" |
jbe@115 | 1182 WHERE "initiative_id" = "initiative"."id" |
jbe@115 | 1183 AND "member_id" = "supporter"."member_id" |
jbe@115 | 1184 ) AS "satisfied" |
jbe@115 | 1185 FROM "initiative" |
jbe@115 | 1186 JOIN "supporter" |
jbe@115 | 1187 ON "supporter"."initiative_id" = "initiative"."id" |
jbe@115 | 1188 JOIN "current_draft" |
jbe@115 | 1189 ON "initiative"."id" = "current_draft"."initiative_id" |
jbe@115 | 1190 JOIN "direct_interest_snapshot" |
jbe@115 | 1191 ON "supporter"."member_id" = "direct_interest_snapshot"."member_id" |
jbe@115 | 1192 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id" |
jbe@115 | 1193 AND "event" = 'periodic' |
jbe@115 | 1194 WHERE "initiative"."issue_id" = "issue_id_p"; |
jbe@115 | 1195 RETURN; |
jbe@115 | 1196 END; |
jbe@115 | 1197 $$; |
jbe@115 | 1198 |
jbe@115 | 1199 CREATE OR REPLACE FUNCTION "freeze_after_snapshot" |
jbe@115 | 1200 ( "issue_id_p" "issue"."id"%TYPE ) |
jbe@115 | 1201 RETURNS VOID |
jbe@115 | 1202 LANGUAGE 'plpgsql' VOLATILE AS $$ |
jbe@115 | 1203 DECLARE |
jbe@115 | 1204 "issue_row" "issue"%ROWTYPE; |
jbe@115 | 1205 "policy_row" "policy"%ROWTYPE; |
jbe@115 | 1206 "initiative_row" "initiative"%ROWTYPE; |
jbe@115 | 1207 BEGIN |
jbe@115 | 1208 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"; |
jbe@115 | 1209 SELECT * INTO "policy_row" |
jbe@115 | 1210 FROM "policy" WHERE "id" = "issue_row"."policy_id"; |
jbe@115 | 1211 PERFORM "set_snapshot_event"("issue_id_p", 'full_freeze'); |
jbe@115 | 1212 FOR "initiative_row" IN |
jbe@115 | 1213 SELECT * FROM "initiative" |
jbe@115 | 1214 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL |
jbe@115 | 1215 LOOP |
jbe@115 | 1216 IF |
jbe@115 | 1217 "initiative_row"."satisfied_supporter_count" > 0 AND |
jbe@115 | 1218 "initiative_row"."satisfied_supporter_count" * |
jbe@115 | 1219 "policy_row"."initiative_quorum_den" >= |
jbe@115 | 1220 "issue_row"."population" * "policy_row"."initiative_quorum_num" |
jbe@115 | 1221 THEN |
jbe@115 | 1222 UPDATE "initiative" SET "admitted" = TRUE |
jbe@115 | 1223 WHERE "id" = "initiative_row"."id"; |
jbe@115 | 1224 ELSE |
jbe@115 | 1225 UPDATE "initiative" SET "admitted" = FALSE |
jbe@115 | 1226 WHERE "id" = "initiative_row"."id"; |
jbe@115 | 1227 END IF; |
jbe@115 | 1228 END LOOP; |
jbe@115 | 1229 IF EXISTS ( |
jbe@115 | 1230 SELECT NULL FROM "initiative" |
jbe@115 | 1231 WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE |
jbe@115 | 1232 ) THEN |
jbe@115 | 1233 UPDATE "issue" SET |
jbe@115 | 1234 "state" = 'voting', |
jbe@115 | 1235 "accepted" = coalesce("accepted", now()), |
jbe@115 | 1236 "half_frozen" = coalesce("half_frozen", now()), |
jbe@115 | 1237 "fully_frozen" = now() |
jbe@115 | 1238 WHERE "id" = "issue_id_p"; |
jbe@115 | 1239 ELSE |
jbe@115 | 1240 UPDATE "issue" SET |
jbe@121 | 1241 "state" = 'canceled_no_initiative_admitted', |
jbe@121 | 1242 "accepted" = coalesce("accepted", now()), |
jbe@121 | 1243 "half_frozen" = coalesce("half_frozen", now()), |
jbe@121 | 1244 "fully_frozen" = now(), |
jbe@121 | 1245 "closed" = now(), |
jbe@121 | 1246 "ranks_available" = TRUE |
jbe@115 | 1247 WHERE "id" = "issue_id_p"; |
jbe@115 | 1248 -- NOTE: The following DELETE statements have effect only when |
jbe@115 | 1249 -- issue state has been manipulated |
jbe@115 | 1250 DELETE FROM "direct_voter" WHERE "issue_id" = "issue_id_p"; |
jbe@115 | 1251 DELETE FROM "delegating_voter" WHERE "issue_id" = "issue_id_p"; |
jbe@115 | 1252 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p"; |
jbe@115 | 1253 END IF; |
jbe@115 | 1254 RETURN; |
jbe@115 | 1255 END; |
jbe@115 | 1256 $$; |
jbe@115 | 1257 |
jbe@115 | 1258 CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE) |
jbe@115 | 1259 RETURNS VOID |
jbe@115 | 1260 LANGUAGE 'plpgsql' VOLATILE AS $$ |
jbe@115 | 1261 DECLARE |
jbe@115 | 1262 "area_id_v" "area"."id"%TYPE; |
jbe@115 | 1263 "unit_id_v" "unit"."id"%TYPE; |
jbe@115 | 1264 "member_id_v" "member"."id"%TYPE; |
jbe@115 | 1265 BEGIN |
jbe@115 | 1266 PERFORM "lock_issue"("issue_id_p"); |
jbe@115 | 1267 SELECT "id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p"; |
jbe@115 | 1268 SELECT "id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v"; |
jbe@115 | 1269 DELETE FROM "delegating_voter" |
jbe@115 | 1270 WHERE "issue_id" = "issue_id_p"; |
jbe@115 | 1271 DELETE FROM "direct_voter" |
jbe@115 | 1272 WHERE "issue_id" = "issue_id_p" |
jbe@115 | 1273 AND "autoreject" = TRUE; |
jbe@115 | 1274 DELETE FROM "direct_voter" |
jbe@115 | 1275 USING ( |
jbe@115 | 1276 SELECT |
jbe@115 | 1277 "direct_voter"."member_id" |
jbe@115 | 1278 FROM "direct_voter" |
jbe@115 | 1279 JOIN "member" ON "direct_voter"."member_id" = "member"."id" |
jbe@115 | 1280 LEFT JOIN "privilege" |
jbe@115 | 1281 ON "privilege"."unit_id" = "unit_id_v" |
jbe@115 | 1282 AND "privilege"."member_id" = "direct_voter"."member_id" |
jbe@115 | 1283 WHERE "direct_voter"."issue_id" = "issue_id_p" AND ( |
jbe@115 | 1284 "member"."active" = FALSE OR |
jbe@115 | 1285 "privilege"."voting_right" ISNULL OR |
jbe@115 | 1286 "privilege"."voting_right" = FALSE |
jbe@115 | 1287 ) |
jbe@115 | 1288 ) AS "subquery" |
jbe@115 | 1289 WHERE "direct_voter"."issue_id" = "issue_id_p" |
jbe@115 | 1290 AND "direct_voter"."member_id" = "subquery"."member_id"; |
jbe@115 | 1291 UPDATE "direct_voter" SET "weight" = 1 |
jbe@115 | 1292 WHERE "issue_id" = "issue_id_p"; |
jbe@115 | 1293 PERFORM "add_vote_delegations"("issue_id_p"); |
jbe@115 | 1294 FOR "member_id_v" IN |
jbe@115 | 1295 SELECT "interest"."member_id" |
jbe@115 | 1296 FROM "interest" |
jbe@115 | 1297 JOIN "member" |
jbe@115 | 1298 ON "interest"."member_id" = "member"."id" |
jbe@115 | 1299 LEFT JOIN "direct_voter" |
jbe@115 | 1300 ON "interest"."member_id" = "direct_voter"."member_id" |
jbe@115 | 1301 AND "interest"."issue_id" = "direct_voter"."issue_id" |
jbe@115 | 1302 LEFT JOIN "delegating_voter" |
jbe@115 | 1303 ON "interest"."member_id" = "delegating_voter"."member_id" |
jbe@115 | 1304 AND "interest"."issue_id" = "delegating_voter"."issue_id" |
jbe@115 | 1305 WHERE "interest"."issue_id" = "issue_id_p" |
jbe@115 | 1306 AND "interest"."autoreject" = TRUE |
jbe@115 | 1307 AND "member"."active" |
jbe@115 | 1308 AND "direct_voter"."member_id" ISNULL |
jbe@115 | 1309 AND "delegating_voter"."member_id" ISNULL |
jbe@115 | 1310 UNION SELECT "membership"."member_id" |
jbe@115 | 1311 FROM "membership" |
jbe@115 | 1312 JOIN "member" |
jbe@115 | 1313 ON "membership"."member_id" = "member"."id" |
jbe@115 | 1314 LEFT JOIN "interest" |
jbe@115 | 1315 ON "membership"."member_id" = "interest"."member_id" |
jbe@115 | 1316 AND "interest"."issue_id" = "issue_id_p" |
jbe@115 | 1317 LEFT JOIN "direct_voter" |
jbe@115 | 1318 ON "membership"."member_id" = "direct_voter"."member_id" |
jbe@115 | 1319 AND "direct_voter"."issue_id" = "issue_id_p" |
jbe@115 | 1320 LEFT JOIN "delegating_voter" |
jbe@115 | 1321 ON "membership"."member_id" = "delegating_voter"."member_id" |
jbe@115 | 1322 AND "delegating_voter"."issue_id" = "issue_id_p" |
jbe@115 | 1323 WHERE "membership"."area_id" = "area_id_v" |
jbe@115 | 1324 AND "membership"."autoreject" = TRUE |
jbe@115 | 1325 AND "member"."active" |
jbe@115 | 1326 AND "interest"."autoreject" ISNULL |
jbe@115 | 1327 AND "direct_voter"."member_id" ISNULL |
jbe@115 | 1328 AND "delegating_voter"."member_id" ISNULL |
jbe@115 | 1329 LOOP |
jbe@115 | 1330 INSERT INTO "direct_voter" |
jbe@115 | 1331 ("member_id", "issue_id", "weight", "autoreject") VALUES |
jbe@115 | 1332 ("member_id_v", "issue_id_p", 1, TRUE); |
jbe@115 | 1333 INSERT INTO "vote" ( |
jbe@115 | 1334 "member_id", |
jbe@115 | 1335 "issue_id", |
jbe@115 | 1336 "initiative_id", |
jbe@115 | 1337 "grade" |
jbe@115 | 1338 ) SELECT |
jbe@115 | 1339 "member_id_v" AS "member_id", |
jbe@115 | 1340 "issue_id_p" AS "issue_id", |
jbe@115 | 1341 "id" AS "initiative_id", |
jbe@115 | 1342 -1 AS "grade" |
jbe@115 | 1343 FROM "initiative" WHERE "issue_id" = "issue_id_p"; |
jbe@115 | 1344 END LOOP; |
jbe@115 | 1345 PERFORM "add_vote_delegations"("issue_id_p"); |
jbe@115 | 1346 UPDATE "issue" SET |
jbe@115 | 1347 "state" = 'calculation', |
jbe@115 | 1348 "closed" = now(), |
jbe@115 | 1349 "voter_count" = ( |
jbe@115 | 1350 SELECT coalesce(sum("weight"), 0) |
jbe@115 | 1351 FROM "direct_voter" WHERE "issue_id" = "issue_id_p" |
jbe@115 | 1352 ) |
jbe@115 | 1353 WHERE "id" = "issue_id_p"; |
jbe@115 | 1354 UPDATE "initiative" SET |
jbe@115 | 1355 "positive_votes" = "vote_counts"."positive_votes", |
jbe@115 | 1356 "negative_votes" = "vote_counts"."negative_votes", |
jbe@115 | 1357 "agreed" = CASE WHEN "majority_strict" THEN |
jbe@115 | 1358 "vote_counts"."positive_votes" * "majority_den" > |
jbe@115 | 1359 "majority_num" * |
jbe@115 | 1360 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes") |
jbe@115 | 1361 ELSE |
jbe@115 | 1362 "vote_counts"."positive_votes" * "majority_den" >= |
jbe@115 | 1363 "majority_num" * |
jbe@115 | 1364 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes") |
jbe@115 | 1365 END |
jbe@115 | 1366 FROM |
jbe@115 | 1367 ( SELECT |
jbe@115 | 1368 "initiative"."id" AS "initiative_id", |
jbe@115 | 1369 coalesce( |
jbe@115 | 1370 sum( |
jbe@115 | 1371 CASE WHEN "grade" > 0 THEN "direct_voter"."weight" ELSE 0 END |
jbe@115 | 1372 ), |
jbe@115 | 1373 0 |
jbe@115 | 1374 ) AS "positive_votes", |
jbe@115 | 1375 coalesce( |
jbe@115 | 1376 sum( |
jbe@115 | 1377 CASE WHEN "grade" < 0 THEN "direct_voter"."weight" ELSE 0 END |
jbe@115 | 1378 ), |
jbe@115 | 1379 0 |
jbe@115 | 1380 ) AS "negative_votes" |
jbe@115 | 1381 FROM "initiative" |
jbe@115 | 1382 JOIN "issue" ON "initiative"."issue_id" = "issue"."id" |
jbe@115 | 1383 JOIN "policy" ON "issue"."policy_id" = "policy"."id" |
jbe@115 | 1384 LEFT JOIN "direct_voter" |
jbe@115 | 1385 ON "direct_voter"."issue_id" = "initiative"."issue_id" |
jbe@115 | 1386 LEFT JOIN "vote" |
jbe@115 | 1387 ON "vote"."initiative_id" = "initiative"."id" |
jbe@115 | 1388 AND "vote"."member_id" = "direct_voter"."member_id" |
jbe@115 | 1389 WHERE "initiative"."issue_id" = "issue_id_p" |
jbe@115 | 1390 AND "initiative"."admitted" -- NOTE: NULL case is handled too |
jbe@115 | 1391 GROUP BY "initiative"."id" |
jbe@115 | 1392 ) AS "vote_counts", |
jbe@115 | 1393 "issue", |
jbe@115 | 1394 "policy" |
jbe@115 | 1395 WHERE "vote_counts"."initiative_id" = "initiative"."id" |
jbe@115 | 1396 AND "issue"."id" = "initiative"."issue_id" |
jbe@115 | 1397 AND "policy"."id" = "issue"."policy_id"; |
jbe@115 | 1398 -- NOTE: "closed" column of issue must be set at this point |
jbe@115 | 1399 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p"; |
jbe@115 | 1400 INSERT INTO "battle" ( |
jbe@115 | 1401 "issue_id", |
jbe@115 | 1402 "winning_initiative_id", "losing_initiative_id", |
jbe@115 | 1403 "count" |
jbe@115 | 1404 ) SELECT |
jbe@115 | 1405 "issue_id", |
jbe@115 | 1406 "winning_initiative_id", "losing_initiative_id", |
jbe@115 | 1407 "count" |
jbe@115 | 1408 FROM "battle_view" WHERE "issue_id" = "issue_id_p"; |
jbe@115 | 1409 END; |
jbe@115 | 1410 $$; |
jbe@115 | 1411 |
jbe@115 | 1412 CREATE OR REPLACE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE) |
jbe@115 | 1413 RETURNS VOID |
jbe@115 | 1414 LANGUAGE 'plpgsql' VOLATILE AS $$ |
jbe@115 | 1415 DECLARE |
jbe@115 | 1416 "dimension_v" INTEGER; |
jbe@115 | 1417 "vote_matrix" INT4[][]; -- absolute votes |
jbe@115 | 1418 "matrix" INT8[][]; -- defeat strength / best paths |
jbe@115 | 1419 "i" INTEGER; |
jbe@115 | 1420 "j" INTEGER; |
jbe@115 | 1421 "k" INTEGER; |
jbe@115 | 1422 "battle_row" "battle"%ROWTYPE; |
jbe@115 | 1423 "rank_ary" INT4[]; |
jbe@115 | 1424 "rank_v" INT4; |
jbe@115 | 1425 "done_v" INTEGER; |
jbe@115 | 1426 "winners_ary" INTEGER[]; |
jbe@115 | 1427 "initiative_id_v" "initiative"."id"%TYPE; |
jbe@115 | 1428 BEGIN |
jbe@115 | 1429 PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE; |
jbe@115 | 1430 SELECT count(1) INTO "dimension_v" FROM "initiative" |
jbe@115 | 1431 WHERE "issue_id" = "issue_id_p" AND "agreed"; |
jbe@115 | 1432 IF "dimension_v" = 1 THEN |
jbe@115 | 1433 UPDATE "initiative" SET "rank" = 1 |
jbe@115 | 1434 WHERE "issue_id" = "issue_id_p" AND "agreed"; |
jbe@115 | 1435 ELSIF "dimension_v" > 1 THEN |
jbe@115 | 1436 -- Create "vote_matrix" with absolute number of votes in pairwise |
jbe@115 | 1437 -- comparison: |
jbe@115 | 1438 "vote_matrix" := "square_matrix_init_string"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4) |
jbe@115 | 1439 "i" := 1; |
jbe@115 | 1440 "j" := 2; |
jbe@115 | 1441 FOR "battle_row" IN |
jbe@115 | 1442 SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p" |
jbe@115 | 1443 ORDER BY "winning_initiative_id", "losing_initiative_id" |
jbe@115 | 1444 LOOP |
jbe@115 | 1445 "vote_matrix"["i"]["j"] := "battle_row"."count"; |
jbe@115 | 1446 IF "j" = "dimension_v" THEN |
jbe@115 | 1447 "i" := "i" + 1; |
jbe@115 | 1448 "j" := 1; |
jbe@115 | 1449 ELSE |
jbe@115 | 1450 "j" := "j" + 1; |
jbe@115 | 1451 IF "j" = "i" THEN |
jbe@115 | 1452 "j" := "j" + 1; |
jbe@115 | 1453 END IF; |
jbe@115 | 1454 END IF; |
jbe@115 | 1455 END LOOP; |
jbe@115 | 1456 IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN |
jbe@115 | 1457 RAISE EXCEPTION 'Wrong battle count (should not happen)'; |
jbe@115 | 1458 END IF; |
jbe@115 | 1459 -- Store defeat strengths in "matrix" using "defeat_strength" |
jbe@115 | 1460 -- function: |
jbe@115 | 1461 "matrix" := "square_matrix_init_string"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4) |
jbe@115 | 1462 "i" := 1; |
jbe@115 | 1463 LOOP |
jbe@115 | 1464 "j" := 1; |
jbe@115 | 1465 LOOP |
jbe@115 | 1466 IF "i" != "j" THEN |
jbe@115 | 1467 "matrix"["i"]["j"] := "defeat_strength"( |
jbe@115 | 1468 "vote_matrix"["i"]["j"], |
jbe@115 | 1469 "vote_matrix"["j"]["i"] |
jbe@115 | 1470 ); |
jbe@115 | 1471 END IF; |
jbe@115 | 1472 EXIT WHEN "j" = "dimension_v"; |
jbe@115 | 1473 "j" := "j" + 1; |
jbe@115 | 1474 END LOOP; |
jbe@115 | 1475 EXIT WHEN "i" = "dimension_v"; |
jbe@115 | 1476 "i" := "i" + 1; |
jbe@115 | 1477 END LOOP; |
jbe@115 | 1478 -- Find best paths: |
jbe@115 | 1479 "i" := 1; |
jbe@115 | 1480 LOOP |
jbe@115 | 1481 "j" := 1; |
jbe@115 | 1482 LOOP |
jbe@115 | 1483 IF "i" != "j" THEN |
jbe@115 | 1484 "k" := 1; |
jbe@115 | 1485 LOOP |
jbe@115 | 1486 IF "i" != "k" AND "j" != "k" THEN |
jbe@115 | 1487 IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN |
jbe@115 | 1488 IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN |
jbe@115 | 1489 "matrix"["j"]["k"] := "matrix"["j"]["i"]; |
jbe@115 | 1490 END IF; |
jbe@115 | 1491 ELSE |
jbe@115 | 1492 IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN |
jbe@115 | 1493 "matrix"["j"]["k"] := "matrix"["i"]["k"]; |
jbe@115 | 1494 END IF; |
jbe@115 | 1495 END IF; |
jbe@115 | 1496 END IF; |
jbe@115 | 1497 EXIT WHEN "k" = "dimension_v"; |
jbe@115 | 1498 "k" := "k" + 1; |
jbe@115 | 1499 END LOOP; |
jbe@115 | 1500 END IF; |
jbe@115 | 1501 EXIT WHEN "j" = "dimension_v"; |
jbe@115 | 1502 "j" := "j" + 1; |
jbe@115 | 1503 END LOOP; |
jbe@115 | 1504 EXIT WHEN "i" = "dimension_v"; |
jbe@115 | 1505 "i" := "i" + 1; |
jbe@115 | 1506 END LOOP; |
jbe@115 | 1507 -- Determine order of winners: |
jbe@115 | 1508 "rank_ary" := "array_init_string"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4) |
jbe@115 | 1509 "rank_v" := 1; |
jbe@115 | 1510 "done_v" := 0; |
jbe@115 | 1511 LOOP |
jbe@115 | 1512 "winners_ary" := '{}'; |
jbe@115 | 1513 "i" := 1; |
jbe@115 | 1514 LOOP |
jbe@115 | 1515 IF "rank_ary"["i"] ISNULL THEN |
jbe@115 | 1516 "j" := 1; |
jbe@115 | 1517 LOOP |
jbe@115 | 1518 IF |
jbe@115 | 1519 "i" != "j" AND |
jbe@115 | 1520 "rank_ary"["j"] ISNULL AND |
jbe@115 | 1521 "matrix"["j"]["i"] > "matrix"["i"]["j"] |
jbe@115 | 1522 THEN |
jbe@115 | 1523 -- someone else is better |
jbe@115 | 1524 EXIT; |
jbe@115 | 1525 END IF; |
jbe@115 | 1526 IF "j" = "dimension_v" THEN |
jbe@115 | 1527 -- noone is better |
jbe@115 | 1528 "winners_ary" := "winners_ary" || "i"; |
jbe@115 | 1529 EXIT; |
jbe@115 | 1530 END IF; |
jbe@115 | 1531 "j" := "j" + 1; |
jbe@115 | 1532 END LOOP; |
jbe@115 | 1533 END IF; |
jbe@115 | 1534 EXIT WHEN "i" = "dimension_v"; |
jbe@115 | 1535 "i" := "i" + 1; |
jbe@115 | 1536 END LOOP; |
jbe@115 | 1537 "i" := 1; |
jbe@115 | 1538 LOOP |
jbe@115 | 1539 "rank_ary"["winners_ary"["i"]] := "rank_v"; |
jbe@115 | 1540 "done_v" := "done_v" + 1; |
jbe@115 | 1541 EXIT WHEN "i" = array_upper("winners_ary", 1); |
jbe@115 | 1542 "i" := "i" + 1; |
jbe@115 | 1543 END LOOP; |
jbe@115 | 1544 EXIT WHEN "done_v" = "dimension_v"; |
jbe@115 | 1545 "rank_v" := "rank_v" + 1; |
jbe@115 | 1546 END LOOP; |
jbe@115 | 1547 -- write preliminary ranks: |
jbe@115 | 1548 "i" := 1; |
jbe@115 | 1549 FOR "initiative_id_v" IN |
jbe@115 | 1550 SELECT "id" FROM "initiative" |
jbe@115 | 1551 WHERE "issue_id" = "issue_id_p" AND "agreed" |
jbe@115 | 1552 ORDER BY "id" |
jbe@115 | 1553 LOOP |
jbe@115 | 1554 UPDATE "initiative" SET "rank" = "rank_ary"["i"] |
jbe@115 | 1555 WHERE "id" = "initiative_id_v"; |
jbe@115 | 1556 "i" := "i" + 1; |
jbe@115 | 1557 END LOOP; |
jbe@115 | 1558 IF "i" != "dimension_v" + 1 THEN |
jbe@115 | 1559 RAISE EXCEPTION 'Wrong winner count (should not happen)'; |
jbe@115 | 1560 END IF; |
jbe@115 | 1561 -- straighten ranks (start counting with 1, no equal ranks): |
jbe@115 | 1562 "rank_v" := 1; |
jbe@115 | 1563 FOR "initiative_id_v" IN |
jbe@115 | 1564 SELECT "id" FROM "initiative" |
jbe@115 | 1565 WHERE "issue_id" = "issue_id_p" AND "rank" NOTNULL |
jbe@115 | 1566 ORDER BY |
jbe@115 | 1567 "rank", |
jbe@115 | 1568 "vote_ratio"("positive_votes", "negative_votes") DESC, |
jbe@115 | 1569 "id" |
jbe@115 | 1570 LOOP |
jbe@115 | 1571 UPDATE "initiative" SET "rank" = "rank_v" |
jbe@115 | 1572 WHERE "id" = "initiative_id_v"; |
jbe@115 | 1573 "rank_v" := "rank_v" + 1; |
jbe@115 | 1574 END LOOP; |
jbe@115 | 1575 END IF; |
jbe@115 | 1576 -- mark issue as finished |
jbe@115 | 1577 UPDATE "issue" SET |
jbe@115 | 1578 "state" = |
jbe@121 | 1579 CASE WHEN "dimension_v" = 0 THEN |
jbe@121 | 1580 'finished_without_winner'::"issue_state" |
jbe@115 | 1581 ELSE |
jbe@121 | 1582 'finished_with_winner'::"issue_state" |
jbe@115 | 1583 END, |
jbe@115 | 1584 "ranks_available" = TRUE |
jbe@115 | 1585 WHERE "id" = "issue_id_p"; |
jbe@115 | 1586 RETURN; |
jbe@115 | 1587 END; |
jbe@115 | 1588 $$; |
jbe@115 | 1589 |
jbe@115 | 1590 CREATE OR REPLACE FUNCTION "check_issue" |
jbe@115 | 1591 ( "issue_id_p" "issue"."id"%TYPE ) |
jbe@115 | 1592 RETURNS VOID |
jbe@115 | 1593 LANGUAGE 'plpgsql' VOLATILE AS $$ |
jbe@115 | 1594 DECLARE |
jbe@115 | 1595 "issue_row" "issue"%ROWTYPE; |
jbe@115 | 1596 "policy_row" "policy"%ROWTYPE; |
jbe@115 | 1597 "voting_requested_v" BOOLEAN; |
jbe@115 | 1598 BEGIN |
jbe@115 | 1599 PERFORM "lock_issue"("issue_id_p"); |
jbe@115 | 1600 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"; |
jbe@115 | 1601 -- only process open issues: |
jbe@115 | 1602 IF "issue_row"."closed" ISNULL THEN |
jbe@115 | 1603 SELECT * INTO "policy_row" FROM "policy" |
jbe@115 | 1604 WHERE "id" = "issue_row"."policy_id"; |
jbe@115 | 1605 -- create a snapshot, unless issue is already fully frozen: |
jbe@115 | 1606 IF "issue_row"."fully_frozen" ISNULL THEN |
jbe@115 | 1607 PERFORM "create_snapshot"("issue_id_p"); |
jbe@115 | 1608 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"; |
jbe@115 | 1609 END IF; |
jbe@115 | 1610 -- eventually close or accept issues, which have not been accepted: |
jbe@115 | 1611 IF "issue_row"."accepted" ISNULL THEN |
jbe@115 | 1612 IF EXISTS ( |
jbe@115 | 1613 SELECT NULL FROM "initiative" |
jbe@115 | 1614 WHERE "issue_id" = "issue_id_p" |
jbe@115 | 1615 AND "supporter_count" > 0 |
jbe@115 | 1616 AND "supporter_count" * "policy_row"."issue_quorum_den" |
jbe@115 | 1617 >= "issue_row"."population" * "policy_row"."issue_quorum_num" |
jbe@115 | 1618 ) THEN |
jbe@115 | 1619 -- accept issues, if supporter count is high enough |
jbe@115 | 1620 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission'); |
jbe@115 | 1621 -- NOTE: "issue_row" used later |
jbe@115 | 1622 "issue_row"."state" := 'discussion'; |
jbe@115 | 1623 "issue_row"."accepted" := now(); |
jbe@115 | 1624 UPDATE "issue" SET |
jbe@115 | 1625 "state" = "issue_row"."state", |
jbe@115 | 1626 "accepted" = "issue_row"."accepted" |
jbe@115 | 1627 WHERE "id" = "issue_row"."id"; |
jbe@115 | 1628 ELSIF |
jbe@115 | 1629 now() >= "issue_row"."created" + "issue_row"."admission_time" |
jbe@115 | 1630 THEN |
jbe@115 | 1631 -- close issues, if admission time has expired |
jbe@115 | 1632 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission'); |
jbe@115 | 1633 UPDATE "issue" SET |
jbe@115 | 1634 "state" = 'canceled_issue_not_accepted', |
jbe@115 | 1635 "closed" = now() |
jbe@115 | 1636 WHERE "id" = "issue_row"."id"; |
jbe@115 | 1637 END IF; |
jbe@115 | 1638 END IF; |
jbe@115 | 1639 -- eventually half freeze issues: |
jbe@115 | 1640 IF |
jbe@115 | 1641 -- NOTE: issue can't be closed at this point, if it has been accepted |
jbe@115 | 1642 "issue_row"."accepted" NOTNULL AND |
jbe@115 | 1643 "issue_row"."half_frozen" ISNULL |
jbe@115 | 1644 THEN |
jbe@115 | 1645 SELECT |
jbe@115 | 1646 CASE |
jbe@115 | 1647 WHEN "vote_now" * 2 > "issue_row"."population" THEN |
jbe@115 | 1648 TRUE |
jbe@115 | 1649 WHEN "vote_later" * 2 > "issue_row"."population" THEN |
jbe@115 | 1650 FALSE |
jbe@115 | 1651 ELSE NULL |
jbe@115 | 1652 END |
jbe@115 | 1653 INTO "voting_requested_v" |
jbe@115 | 1654 FROM "issue" WHERE "id" = "issue_id_p"; |
jbe@115 | 1655 IF |
jbe@115 | 1656 "voting_requested_v" OR ( |
jbe@115 | 1657 "voting_requested_v" ISNULL AND |
jbe@115 | 1658 now() >= "issue_row"."accepted" + "issue_row"."discussion_time" |
jbe@115 | 1659 ) |
jbe@115 | 1660 THEN |
jbe@115 | 1661 PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze'); |
jbe@115 | 1662 -- NOTE: "issue_row" used later |
jbe@115 | 1663 "issue_row"."state" := 'verification'; |
jbe@115 | 1664 "issue_row"."half_frozen" := now(); |
jbe@115 | 1665 UPDATE "issue" SET |
jbe@115 | 1666 "state" = "issue_row"."state", |
jbe@115 | 1667 "half_frozen" = "issue_row"."half_frozen" |
jbe@115 | 1668 WHERE "id" = "issue_row"."id"; |
jbe@115 | 1669 END IF; |
jbe@115 | 1670 END IF; |
jbe@115 | 1671 -- close issues after some time, if all initiatives have been revoked: |
jbe@115 | 1672 IF |
jbe@115 | 1673 "issue_row"."closed" ISNULL AND |
jbe@115 | 1674 NOT EXISTS ( |
jbe@115 | 1675 -- all initiatives are revoked |
jbe@115 | 1676 SELECT NULL FROM "initiative" |
jbe@115 | 1677 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL |
jbe@115 | 1678 ) AND ( |
jbe@115 | 1679 -- and issue has not been accepted yet |
jbe@115 | 1680 "issue_row"."accepted" ISNULL OR |
jbe@115 | 1681 NOT EXISTS ( |
jbe@115 | 1682 -- or no initiatives have been revoked lately |
jbe@115 | 1683 SELECT NULL FROM "initiative" |
jbe@115 | 1684 WHERE "issue_id" = "issue_id_p" |
jbe@115 | 1685 AND now() < "revoked" + "issue_row"."verification_time" |
jbe@115 | 1686 ) OR ( |
jbe@115 | 1687 -- or verification time has elapsed |
jbe@115 | 1688 "issue_row"."half_frozen" NOTNULL AND |
jbe@115 | 1689 "issue_row"."fully_frozen" ISNULL AND |
jbe@115 | 1690 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time" |
jbe@115 | 1691 ) |
jbe@115 | 1692 ) |
jbe@115 | 1693 THEN |
jbe@115 | 1694 -- NOTE: "issue_row" used later |
jbe@115 | 1695 IF "issue_row"."accepted" ISNULL THEN |
jbe@115 | 1696 "issue_row"."state" := 'canceled_revoked_before_accepted'; |
jbe@115 | 1697 ELSIF "issue_row"."half_frozen" ISNULL THEN |
jbe@115 | 1698 "issue_row"."state" := 'canceled_after_revocation_during_discussion'; |
jbe@115 | 1699 ELSE |
jbe@115 | 1700 "issue_row"."state" := 'canceled_after_revocation_during_verification'; |
jbe@115 | 1701 END IF; |
jbe@115 | 1702 "issue_row"."closed" := now(); |
jbe@115 | 1703 UPDATE "issue" SET |
jbe@115 | 1704 "state" = "issue_row"."state", |
jbe@115 | 1705 "closed" = "issue_row"."closed" |
jbe@115 | 1706 WHERE "id" = "issue_row"."id"; |
jbe@115 | 1707 END IF; |
jbe@115 | 1708 -- fully freeze issue after verification time: |
jbe@115 | 1709 IF |
jbe@115 | 1710 "issue_row"."half_frozen" NOTNULL AND |
jbe@115 | 1711 "issue_row"."fully_frozen" ISNULL AND |
jbe@115 | 1712 "issue_row"."closed" ISNULL AND |
jbe@115 | 1713 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time" |
jbe@115 | 1714 THEN |
jbe@115 | 1715 PERFORM "freeze_after_snapshot"("issue_id_p"); |
jbe@115 | 1716 -- NOTE: "issue" might change, thus "issue_row" has to be updated below |
jbe@115 | 1717 END IF; |
jbe@115 | 1718 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"; |
jbe@115 | 1719 -- close issue by calling close_voting(...) after voting time: |
jbe@115 | 1720 IF |
jbe@115 | 1721 "issue_row"."closed" ISNULL AND |
jbe@115 | 1722 "issue_row"."fully_frozen" NOTNULL AND |
jbe@115 | 1723 now() >= "issue_row"."fully_frozen" + "issue_row"."voting_time" |
jbe@115 | 1724 THEN |
jbe@115 | 1725 PERFORM "close_voting"("issue_id_p"); |
jbe@115 | 1726 -- calculate ranks will not consume much time and can be done now |
jbe@115 | 1727 PERFORM "calculate_ranks"("issue_id_p"); |
jbe@115 | 1728 END IF; |
jbe@115 | 1729 END IF; |
jbe@115 | 1730 RETURN; |
jbe@115 | 1731 END; |
jbe@115 | 1732 $$; |
jbe@115 | 1733 |
jbe@115 | 1734 CREATE OR REPLACE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE) |
jbe@115 | 1735 RETURNS VOID |
jbe@115 | 1736 LANGUAGE 'plpgsql' VOLATILE AS $$ |
jbe@115 | 1737 DECLARE |
jbe@115 | 1738 "issue_row" "issue"%ROWTYPE; |
jbe@115 | 1739 BEGIN |
jbe@115 | 1740 SELECT * INTO "issue_row" |
jbe@115 | 1741 FROM "issue" WHERE "id" = "issue_id_p" |
jbe@115 | 1742 FOR UPDATE; |
jbe@115 | 1743 IF "issue_row"."cleaned" ISNULL THEN |
jbe@115 | 1744 UPDATE "issue" SET |
jbe@115 | 1745 "closed" = NULL, |
jbe@115 | 1746 "ranks_available" = FALSE |
jbe@115 | 1747 WHERE "id" = "issue_id_p"; |
jbe@115 | 1748 DELETE FROM "delegating_voter" |
jbe@115 | 1749 WHERE "issue_id" = "issue_id_p"; |
jbe@115 | 1750 DELETE FROM "direct_voter" |
jbe@115 | 1751 WHERE "issue_id" = "issue_id_p"; |
jbe@115 | 1752 DELETE FROM "delegating_interest_snapshot" |
jbe@115 | 1753 WHERE "issue_id" = "issue_id_p"; |
jbe@115 | 1754 DELETE FROM "direct_interest_snapshot" |
jbe@115 | 1755 WHERE "issue_id" = "issue_id_p"; |
jbe@115 | 1756 DELETE FROM "delegating_population_snapshot" |
jbe@115 | 1757 WHERE "issue_id" = "issue_id_p"; |
jbe@115 | 1758 DELETE FROM "direct_population_snapshot" |
jbe@115 | 1759 WHERE "issue_id" = "issue_id_p"; |
jbe@115 | 1760 DELETE FROM "non_voter" |
jbe@115 | 1761 WHERE "issue_id" = "issue_id_p"; |
jbe@115 | 1762 DELETE FROM "delegation" |
jbe@115 | 1763 WHERE "issue_id" = "issue_id_p"; |
jbe@115 | 1764 DELETE FROM "supporter" |
jbe@115 | 1765 WHERE "issue_id" = "issue_id_p"; |
jbe@115 | 1766 UPDATE "issue" SET |
jbe@115 | 1767 "closed" = "issue_row"."closed", |
jbe@115 | 1768 "ranks_available" = "issue_row"."ranks_available", |
jbe@115 | 1769 "cleaned" = now() |
jbe@115 | 1770 WHERE "id" = "issue_id_p"; |
jbe@115 | 1771 END IF; |
jbe@115 | 1772 RETURN; |
jbe@115 | 1773 END; |
jbe@115 | 1774 $$; |
jbe@115 | 1775 |
jbe@115 | 1776 CREATE OR REPLACE FUNCTION "check_issue" |
jbe@115 | 1777 ( "issue_id_p" "issue"."id"%TYPE ) |
jbe@115 | 1778 RETURNS VOID |
jbe@115 | 1779 LANGUAGE 'plpgsql' VOLATILE AS $$ |
jbe@115 | 1780 DECLARE |
jbe@115 | 1781 "issue_row" "issue"%ROWTYPE; |
jbe@115 | 1782 "policy_row" "policy"%ROWTYPE; |
jbe@115 | 1783 "voting_requested_v" BOOLEAN; |
jbe@115 | 1784 BEGIN |
jbe@115 | 1785 PERFORM "lock_issue"("issue_id_p"); |
jbe@115 | 1786 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"; |
jbe@115 | 1787 -- only process open issues: |
jbe@115 | 1788 IF "issue_row"."closed" ISNULL THEN |
jbe@115 | 1789 SELECT * INTO "policy_row" FROM "policy" |
jbe@115 | 1790 WHERE "id" = "issue_row"."policy_id"; |
jbe@115 | 1791 -- create a snapshot, unless issue is already fully frozen: |
jbe@115 | 1792 IF "issue_row"."fully_frozen" ISNULL THEN |
jbe@115 | 1793 PERFORM "create_snapshot"("issue_id_p"); |
jbe@115 | 1794 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"; |
jbe@115 | 1795 END IF; |
jbe@115 | 1796 -- eventually close or accept issues, which have not been accepted: |
jbe@115 | 1797 IF "issue_row"."accepted" ISNULL THEN |
jbe@115 | 1798 IF EXISTS ( |
jbe@115 | 1799 SELECT NULL FROM "initiative" |
jbe@115 | 1800 WHERE "issue_id" = "issue_id_p" |
jbe@115 | 1801 AND "supporter_count" > 0 |
jbe@115 | 1802 AND "supporter_count" * "policy_row"."issue_quorum_den" |
jbe@115 | 1803 >= "issue_row"."population" * "policy_row"."issue_quorum_num" |
jbe@115 | 1804 ) THEN |
jbe@115 | 1805 -- accept issues, if supporter count is high enough |
jbe@115 | 1806 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission'); |
jbe@115 | 1807 -- NOTE: "issue_row" used later |
jbe@115 | 1808 "issue_row"."state" := 'discussion'; |
jbe@115 | 1809 "issue_row"."accepted" := now(); |
jbe@115 | 1810 UPDATE "issue" SET |
jbe@115 | 1811 "state" = "issue_row"."state", |
jbe@115 | 1812 "accepted" = "issue_row"."accepted" |
jbe@115 | 1813 WHERE "id" = "issue_row"."id"; |
jbe@115 | 1814 ELSIF |
jbe@115 | 1815 now() >= "issue_row"."created" + "issue_row"."admission_time" |
jbe@115 | 1816 THEN |
jbe@115 | 1817 -- close issues, if admission time has expired |
jbe@115 | 1818 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission'); |
jbe@115 | 1819 UPDATE "issue" SET |
jbe@115 | 1820 "state" = 'canceled_issue_not_accepted', |
jbe@115 | 1821 "closed" = now() |
jbe@115 | 1822 WHERE "id" = "issue_row"."id"; |
jbe@115 | 1823 END IF; |
jbe@115 | 1824 END IF; |
jbe@115 | 1825 -- eventually half freeze issues: |
jbe@115 | 1826 IF |
jbe@115 | 1827 -- NOTE: issue can't be closed at this point, if it has been accepted |
jbe@115 | 1828 "issue_row"."accepted" NOTNULL AND |
jbe@115 | 1829 "issue_row"."half_frozen" ISNULL |
jbe@115 | 1830 THEN |
jbe@115 | 1831 SELECT |
jbe@115 | 1832 CASE |
jbe@115 | 1833 WHEN "vote_now" * 2 > "issue_row"."population" THEN |
jbe@115 | 1834 TRUE |
jbe@115 | 1835 WHEN "vote_later" * 2 > "issue_row"."population" THEN |
jbe@115 | 1836 FALSE |
jbe@115 | 1837 ELSE NULL |
jbe@115 | 1838 END |
jbe@115 | 1839 INTO "voting_requested_v" |
jbe@115 | 1840 FROM "issue" WHERE "id" = "issue_id_p"; |
jbe@115 | 1841 IF |
jbe@115 | 1842 "voting_requested_v" OR ( |
jbe@115 | 1843 "voting_requested_v" ISNULL AND |
jbe@115 | 1844 now() >= "issue_row"."accepted" + "issue_row"."discussion_time" |
jbe@115 | 1845 ) |
jbe@115 | 1846 THEN |
jbe@115 | 1847 PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze'); |
jbe@115 | 1848 -- NOTE: "issue_row" used later |
jbe@115 | 1849 "issue_row"."state" := 'verification'; |
jbe@115 | 1850 "issue_row"."half_frozen" := now(); |
jbe@115 | 1851 UPDATE "issue" SET |
jbe@115 | 1852 "state" = "issue_row"."state", |
jbe@115 | 1853 "half_frozen" = "issue_row"."half_frozen" |
jbe@115 | 1854 WHERE "id" = "issue_row"."id"; |
jbe@115 | 1855 END IF; |
jbe@115 | 1856 END IF; |
jbe@115 | 1857 -- close issues after some time, if all initiatives have been revoked: |
jbe@115 | 1858 IF |
jbe@115 | 1859 "issue_row"."closed" ISNULL AND |
jbe@115 | 1860 NOT EXISTS ( |
jbe@115 | 1861 -- all initiatives are revoked |
jbe@115 | 1862 SELECT NULL FROM "initiative" |
jbe@115 | 1863 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL |
jbe@115 | 1864 ) AND ( |
jbe@115 | 1865 -- and issue has not been accepted yet |
jbe@115 | 1866 "issue_row"."accepted" ISNULL OR |
jbe@115 | 1867 NOT EXISTS ( |
jbe@115 | 1868 -- or no initiatives have been revoked lately |
jbe@115 | 1869 SELECT NULL FROM "initiative" |
jbe@115 | 1870 WHERE "issue_id" = "issue_id_p" |
jbe@115 | 1871 AND now() < "revoked" + "issue_row"."verification_time" |
jbe@115 | 1872 ) OR ( |
jbe@115 | 1873 -- or verification time has elapsed |
jbe@115 | 1874 "issue_row"."half_frozen" NOTNULL AND |
jbe@115 | 1875 "issue_row"."fully_frozen" ISNULL AND |
jbe@115 | 1876 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time" |
jbe@115 | 1877 ) |
jbe@115 | 1878 ) |
jbe@115 | 1879 THEN |
jbe@115 | 1880 -- NOTE: "issue_row" used later |
jbe@115 | 1881 IF "issue_row"."accepted" ISNULL THEN |
jbe@115 | 1882 "issue_row"."state" := 'canceled_revoked_before_accepted'; |
jbe@115 | 1883 ELSIF "issue_row"."half_frozen" ISNULL THEN |
jbe@115 | 1884 "issue_row"."state" := 'canceled_after_revocation_during_discussion'; |
jbe@115 | 1885 ELSE |
jbe@115 | 1886 "issue_row"."state" := 'canceled_after_revocation_during_verification'; |
jbe@115 | 1887 END IF; |
jbe@115 | 1888 "issue_row"."closed" := now(); |
jbe@115 | 1889 UPDATE "issue" SET |
jbe@115 | 1890 "state" = "issue_row"."state", |
jbe@115 | 1891 "closed" = "issue_row"."closed" |
jbe@115 | 1892 WHERE "id" = "issue_row"."id"; |
jbe@115 | 1893 END IF; |
jbe@115 | 1894 -- fully freeze issue after verification time: |
jbe@115 | 1895 IF |
jbe@115 | 1896 "issue_row"."half_frozen" NOTNULL AND |
jbe@115 | 1897 "issue_row"."fully_frozen" ISNULL AND |
jbe@115 | 1898 "issue_row"."closed" ISNULL AND |
jbe@115 | 1899 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time" |
jbe@115 | 1900 THEN |
jbe@115 | 1901 PERFORM "freeze_after_snapshot"("issue_id_p"); |
jbe@115 | 1902 -- NOTE: "issue" might change, thus "issue_row" has to be updated below |
jbe@115 | 1903 END IF; |
jbe@115 | 1904 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"; |
jbe@115 | 1905 -- close issue by calling close_voting(...) after voting time: |
jbe@115 | 1906 IF |
jbe@115 | 1907 "issue_row"."closed" ISNULL AND |
jbe@115 | 1908 "issue_row"."fully_frozen" NOTNULL AND |
jbe@115 | 1909 now() >= "issue_row"."fully_frozen" + "issue_row"."voting_time" |
jbe@115 | 1910 THEN |
jbe@115 | 1911 PERFORM "close_voting"("issue_id_p"); |
jbe@115 | 1912 -- calculate ranks will not consume much time and can be done now |
jbe@115 | 1913 PERFORM "calculate_ranks"("issue_id_p"); |
jbe@115 | 1914 END IF; |
jbe@115 | 1915 END IF; |
jbe@115 | 1916 RETURN; |
jbe@115 | 1917 END; |
jbe@115 | 1918 $$; |
jbe@115 | 1919 |
jbe@115 | 1920 CREATE OR REPLACE FUNCTION "delete_private_data"() |
jbe@115 | 1921 RETURNS VOID |
jbe@115 | 1922 LANGUAGE 'plpgsql' VOLATILE AS $$ |
jbe@115 | 1923 BEGIN |
jbe@115 | 1924 UPDATE "member" SET |
jbe@115 | 1925 "last_login" = NULL, |
jbe@115 | 1926 "login" = NULL, |
jbe@115 | 1927 "password" = NULL, |
jbe@115 | 1928 "notify_email" = NULL, |
jbe@115 | 1929 "notify_email_unconfirmed" = NULL, |
jbe@115 | 1930 "notify_email_secret" = NULL, |
jbe@115 | 1931 "notify_email_secret_expiry" = NULL, |
jbe@115 | 1932 "notify_email_lock_expiry" = NULL, |
jbe@115 | 1933 "password_reset_secret" = NULL, |
jbe@115 | 1934 "password_reset_secret_expiry" = NULL, |
jbe@115 | 1935 "organizational_unit" = NULL, |
jbe@115 | 1936 "internal_posts" = NULL, |
jbe@115 | 1937 "realname" = NULL, |
jbe@115 | 1938 "birthday" = NULL, |
jbe@115 | 1939 "address" = NULL, |
jbe@115 | 1940 "email" = NULL, |
jbe@115 | 1941 "xmpp_address" = NULL, |
jbe@115 | 1942 "website" = NULL, |
jbe@115 | 1943 "phone" = NULL, |
jbe@115 | 1944 "mobile_phone" = NULL, |
jbe@115 | 1945 "profession" = NULL, |
jbe@115 | 1946 "external_memberships" = NULL, |
jbe@115 | 1947 "external_posts" = NULL, |
jbe@115 | 1948 "statement" = NULL; |
jbe@115 | 1949 -- "text_search_data" is updated by triggers |
jbe@115 | 1950 DELETE FROM "invite_code"; |
jbe@115 | 1951 DELETE FROM "setting"; |
jbe@115 | 1952 DELETE FROM "setting_map"; |
jbe@115 | 1953 DELETE FROM "member_relation_setting"; |
jbe@115 | 1954 DELETE FROM "member_image"; |
jbe@115 | 1955 DELETE FROM "contact"; |
jbe@115 | 1956 DELETE FROM "ignored_member"; |
jbe@115 | 1957 DELETE FROM "session"; |
jbe@115 | 1958 DELETE FROM "area_setting"; |
jbe@115 | 1959 DELETE FROM "issue_setting"; |
jbe@115 | 1960 DELETE FROM "ignored_initiative"; |
jbe@115 | 1961 DELETE FROM "initiative_setting"; |
jbe@115 | 1962 DELETE FROM "suggestion_setting"; |
jbe@115 | 1963 DELETE FROM "non_voter"; |
jbe@115 | 1964 DELETE FROM "direct_voter" USING "issue" |
jbe@115 | 1965 WHERE "direct_voter"."issue_id" = "issue"."id" |
jbe@115 | 1966 AND "issue"."closed" ISNULL; |
jbe@115 | 1967 RETURN; |
jbe@115 | 1968 END; |
jbe@115 | 1969 $$; |
jbe@115 | 1970 |
jbe@115 | 1971 |
jbe@115 | 1972 -- Delete old "delegation_scope" TYPE: |
jbe@115 | 1973 |
jbe@115 | 1974 DROP TYPE "delegation_scope_old"; |
jbe@115 | 1975 |
jbe@115 | 1976 |
jbe@115 | 1977 COMMIT; |
jbe@115 | 1978 |
jbe@115 | 1979 |
jbe@115 | 1980 -- Generate issue states: |
jbe@115 | 1981 |
jbe@115 | 1982 UPDATE "issue" SET "state" = |
jbe@115 | 1983 CASE |
jbe@115 | 1984 WHEN "closed" ISNULL THEN |
jbe@115 | 1985 CASE |
jbe@115 | 1986 WHEN "accepted" ISNULL THEN |
jbe@115 | 1987 'admission'::"issue_state" |
jbe@115 | 1988 WHEN "half_frozen" ISNULL THEN |
jbe@115 | 1989 'discussion'::"issue_state" |
jbe@115 | 1990 WHEN "fully_frozen" ISNULL THEN |
jbe@115 | 1991 'verification'::"issue_state" |
jbe@115 | 1992 ELSE |
jbe@115 | 1993 'voting'::"issue_state" |
jbe@115 | 1994 END |
jbe@115 | 1995 WHEN "fully_frozen" NOTNULL THEN |
jbe@115 | 1996 CASE |
jbe@115 | 1997 WHEN "fully_frozen" = "closed" THEN |
jbe@115 | 1998 'canceled_no_initiative_admitted'::"issue_state" |
jbe@115 | 1999 ELSE |
jbe@115 | 2000 'finished_without_winner'::"issue_state" -- NOTE: corrected later |
jbe@115 | 2001 END |
jbe@115 | 2002 WHEN "half_frozen" NOTNULL THEN |
jbe@115 | 2003 'canceled_after_revocation_during_verification'::"issue_state" |
jbe@115 | 2004 WHEN "accepted" NOTNULL THEN |
jbe@115 | 2005 'canceled_after_revocation_during_discussion'::"issue_state" |
jbe@115 | 2006 ELSE |
jbe@115 | 2007 'canceled_revoked_before_accepted'::"issue_state" -- NOTE: corrected later |
jbe@115 | 2008 END; |
jbe@117 | 2009 UPDATE "issue" SET "state" = 'finished_with_winner' |
jbe@117 | 2010 FROM "initiative" |
jbe@117 | 2011 WHERE "issue"."id" = "initiative"."issue_id" |
jbe@117 | 2012 AND "issue"."state" = 'finished_without_winner' |
jbe@117 | 2013 AND "initiative"."agreed"; |
jbe@117 | 2014 UPDATE "issue" SET "state" = 'canceled_issue_not_accepted' |
jbe@117 | 2015 FROM "initiative" |
jbe@117 | 2016 WHERE "issue"."id" = "initiative"."issue_id" |
jbe@117 | 2017 AND "issue"."state" = 'canceled_revoked_before_accepted' |
jbe@117 | 2018 AND "initiative"."revoked" ISNULL; |
jbe@115 | 2019 |
jbe@115 | 2020 ALTER TABLE "issue" ALTER "state" SET NOT NULL; |
jbe@115 | 2021 |
jbe@115 | 2022 ALTER TABLE "issue" DROP CONSTRAINT "valid_state"; |
jbe@115 | 2023 ALTER TABLE "issue" ADD CONSTRAINT "valid_state" CHECK (( |
jbe@115 | 2024 ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR |
jbe@115 | 2025 ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR |
jbe@115 | 2026 ("accepted" NOTNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR |
jbe@115 | 2027 ("accepted" NOTNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR |
jbe@115 | 2028 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR |
jbe@115 | 2029 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR |
jbe@115 | 2030 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR |
jbe@115 | 2031 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR |
jbe@115 | 2032 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = TRUE) |
jbe@115 | 2033 ) AND ( |
jbe@115 | 2034 ("state" = 'admission' AND "closed" ISNULL AND "accepted" ISNULL) OR |
jbe@115 | 2035 ("state" = 'discussion' AND "closed" ISNULL AND "accepted" NOTNULL AND "half_frozen" ISNULL) OR |
jbe@115 | 2036 ("state" = 'verification' AND "closed" ISNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL) OR |
jbe@115 | 2037 ("state" = 'voting' AND "closed" ISNULL AND "fully_frozen" NOTNULL) OR |
jbe@115 | 2038 ("state" = 'canceled_revoked_before_accepted' AND "closed" NOTNULL AND "accepted" ISNULL) OR |
jbe@115 | 2039 ("state" = 'canceled_issue_not_accepted' AND "closed" NOTNULL AND "accepted" ISNULL) OR |
jbe@115 | 2040 ("state" = 'canceled_after_revocation_during_discussion' AND "closed" NOTNULL AND "half_frozen" ISNULL) OR |
jbe@115 | 2041 ("state" = 'canceled_after_revocation_during_verification' AND "closed" NOTNULL AND "fully_frozen" ISNULL) OR |
jbe@115 | 2042 ("state" = 'calculation' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = FALSE) OR |
jbe@115 | 2043 ("state" = 'canceled_no_initiative_admitted' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE) OR |
jbe@115 | 2044 ("state" = 'finished_without_winner' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE) OR |
jbe@115 | 2045 ("state" = 'finished_with_winner' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE) |
jbe@115 | 2046 )); |
jbe@115 | 2047 |
jbe@115 | 2048 |
jbe@115 | 2049 -- Guess "revoked_by_member_id" values based on author of current draft: |
jbe@115 | 2050 |
jbe@115 | 2051 UPDATE "initiative" SET "revoked_by_member_id" = "author_id" |
jbe@115 | 2052 FROM "current_draft" |
jbe@115 | 2053 WHERE "initiative"."id" = "current_draft"."initiative_id" |
jbe@115 | 2054 AND "initiative"."revoked" NOTNULL; |
jbe@115 | 2055 |
jbe@115 | 2056 ALTER TABLE "initiative" ADD |
jbe@115 | 2057 CONSTRAINT "all_or_none_of_revoked_and_revoked_by_member_id_must_be_null" |
jbe@115 | 2058 CHECK ("revoked" NOTNULL = "revoked_by_member_id" NOTNULL); |
jbe@115 | 2059 |
jbe@115 | 2060 |
jbe@115 | 2061 -- Fill "unit_id" column with default value where neccessary: |
jbe@115 | 2062 |
jbe@115 | 2063 UPDATE "delegation" SET "unit_id" = 1 WHERE "scope" = 'unit'; |
jbe@115 | 2064 |
jbe@115 | 2065 ALTER TABLE "delegation" ADD CONSTRAINT "area_id_and_issue_id_set_according_to_scope" |
jbe@115 | 2066 CHECK ( |
jbe@115 | 2067 ("scope" = 'unit' AND "unit_id" NOTNULL AND "area_id" ISNULL AND "issue_id" ISNULL ) OR |
jbe@115 | 2068 ("scope" = 'area' AND "unit_id" ISNULL AND "area_id" NOTNULL AND "issue_id" ISNULL ) OR |
jbe@115 | 2069 ("scope" = 'issue' AND "unit_id" ISNULL AND "area_id" ISNULL AND "issue_id" NOTNULL) ); |
jbe@117 | 2070 |
jbe@117 | 2071 |
jbe@118 | 2072 -- Filling of "event" table with old (reconstructed) events: |
jbe@118 | 2073 |
jbe@118 | 2074 DELETE FROM "event"; |
jbe@118 | 2075 SELECT setval('event_id_seq', 1, false); |
jbe@118 | 2076 |
jbe@118 | 2077 INSERT INTO "event" |
jbe@118 | 2078 ( "occurrence", "event", "member_id", "issue_id", "state", |
jbe@118 | 2079 "initiative_id", "draft_id", "suggestion_id" ) |
jbe@118 | 2080 SELECT * FROM ( |
jbe@118 | 2081 SELECT * FROM ( |
jbe@118 | 2082 SELECT DISTINCT ON ("initiative"."id") |
jbe@118 | 2083 "timeline"."occurrence", |
jbe@118 | 2084 CASE WHEN "issue_creation"."issue_id" NOTNULL THEN |
jbe@118 | 2085 'initiative_created_in_new_issue'::"event_type" |
jbe@118 | 2086 ELSE |
jbe@118 | 2087 'initiative_created_in_existing_issue'::"event_type" |
jbe@118 | 2088 END, |
jbe@118 | 2089 "draft"."author_id", |
jbe@118 | 2090 "issue"."id", |
jbe@118 | 2091 CASE |
jbe@118 | 2092 WHEN "timeline"."occurrence" < "issue"."accepted" THEN |
jbe@118 | 2093 'admission'::"issue_state" |
jbe@118 | 2094 WHEN "timeline"."occurrence" < "issue"."half_frozen" THEN |
jbe@118 | 2095 'discussion'::"issue_state" |
jbe@118 | 2096 ELSE |
jbe@118 | 2097 'verification'::"issue_state" |
jbe@118 | 2098 END, |
jbe@118 | 2099 "initiative"."id", |
jbe@118 | 2100 "draft"."id", |
jbe@118 | 2101 NULL::INT8 |
jbe@118 | 2102 FROM "timeline" |
jbe@118 | 2103 JOIN "initiative" ON "timeline"."initiative_id" = "initiative"."id" |
jbe@118 | 2104 JOIN "issue" ON "issue"."id" = "initiative"."issue_id" |
jbe@118 | 2105 LEFT JOIN "timeline" AS "issue_creation" |
jbe@118 | 2106 ON "initiative"."issue_id" = "issue_creation"."issue_id" |
jbe@118 | 2107 AND "issue_creation"."event" = 'issue_created' |
jbe@118 | 2108 AND "timeline"."occurrence" = "issue_creation"."occurrence" |
jbe@118 | 2109 JOIN "draft" |
jbe@118 | 2110 ON "initiative"."id" = "draft"."initiative_id" |
jbe@118 | 2111 WHERE "timeline"."event" = 'initiative_created' |
jbe@118 | 2112 ORDER BY "initiative"."id", "draft"."id" |
jbe@118 | 2113 ) AS "subquery" -- NOTE: subquery needed due to DISTINCT/ORDER |
jbe@118 | 2114 UNION ALL |
jbe@118 | 2115 SELECT |
jbe@118 | 2116 "timeline"."occurrence", |
jbe@118 | 2117 'issue_state_changed'::"event_type", |
jbe@118 | 2118 NULL, |
jbe@118 | 2119 "issue"."id", |
jbe@118 | 2120 CASE |
jbe@118 | 2121 WHEN "timeline"."event" IN ( |
jbe@118 | 2122 'issue_canceled', |
jbe@118 | 2123 'issue_finished_without_voting', |
jbe@118 | 2124 'issue_finished_after_voting' |
jbe@118 | 2125 ) THEN |
jbe@118 | 2126 "issue"."state" |
jbe@118 | 2127 WHEN "timeline"."event" = 'issue_accepted' THEN |
jbe@118 | 2128 'discussion'::"issue_state" |
jbe@118 | 2129 WHEN "timeline"."event" = 'issue_half_frozen' THEN |
jbe@118 | 2130 'verification'::"issue_state" |
jbe@118 | 2131 WHEN "timeline"."event" = 'issue_voting_started' THEN |
jbe@118 | 2132 'voting'::"issue_state" |
jbe@118 | 2133 END, |
jbe@118 | 2134 NULL, |
jbe@118 | 2135 NULL, |
jbe@118 | 2136 NULL |
jbe@118 | 2137 FROM "timeline" |
jbe@118 | 2138 JOIN "issue" ON "timeline"."issue_id" = "issue"."id" |
jbe@118 | 2139 WHERE "timeline"."event" IN ( |
jbe@118 | 2140 'issue_canceled', |
jbe@118 | 2141 'issue_accepted', |
jbe@118 | 2142 'issue_half_frozen', |
jbe@118 | 2143 'issue_finished_without_voting', |
jbe@118 | 2144 'issue_voting_started', |
jbe@118 | 2145 'issue_finished_after_voting' ) |
jbe@118 | 2146 UNION ALL |
jbe@118 | 2147 SELECT |
jbe@118 | 2148 "timeline"."occurrence", |
jbe@118 | 2149 'initiative_revoked'::"event_type", |
jbe@118 | 2150 "initiative"."revoked_by_member_id", |
jbe@118 | 2151 "issue"."id", |
jbe@118 | 2152 CASE |
jbe@118 | 2153 WHEN "timeline"."occurrence" < "issue"."accepted" THEN |
jbe@118 | 2154 'admission'::"issue_state" |
jbe@118 | 2155 WHEN "timeline"."occurrence" < "issue"."half_frozen" THEN |
jbe@118 | 2156 'discussion'::"issue_state" |
jbe@118 | 2157 ELSE |
jbe@118 | 2158 'verification'::"issue_state" |
jbe@118 | 2159 END, |
jbe@118 | 2160 "initiative"."id", |
jbe@118 | 2161 "current_draft"."id", |
jbe@118 | 2162 NULL |
jbe@118 | 2163 FROM "timeline" |
jbe@118 | 2164 JOIN "initiative" ON "timeline"."initiative_id" = "initiative"."id" |
jbe@118 | 2165 JOIN "issue" ON "issue"."id" = "initiative"."issue_id" |
jbe@118 | 2166 JOIN "current_draft" ON "initiative"."id" = "current_draft"."initiative_id" |
jbe@118 | 2167 WHERE "timeline"."event" = 'initiative_revoked' |
jbe@118 | 2168 UNION ALL |
jbe@118 | 2169 SELECT |
jbe@118 | 2170 "timeline"."occurrence", |
jbe@118 | 2171 'new_draft_created'::"event_type", |
jbe@118 | 2172 "draft"."author_id", |
jbe@118 | 2173 "issue"."id", |
jbe@118 | 2174 CASE |
jbe@118 | 2175 WHEN "timeline"."occurrence" < "issue"."accepted" THEN |
jbe@118 | 2176 'admission'::"issue_state" |
jbe@118 | 2177 WHEN "timeline"."occurrence" < "issue"."half_frozen" THEN |
jbe@118 | 2178 'discussion'::"issue_state" |
jbe@118 | 2179 ELSE |
jbe@118 | 2180 'verification'::"issue_state" |
jbe@118 | 2181 END, |
jbe@118 | 2182 "initiative"."id", |
jbe@118 | 2183 "draft"."id", |
jbe@118 | 2184 NULL |
jbe@118 | 2185 FROM "timeline" |
jbe@118 | 2186 JOIN "draft" ON "timeline"."draft_id" = "draft"."id" |
jbe@118 | 2187 JOIN "initiative" ON "draft"."initiative_id" = "initiative"."id" |
jbe@118 | 2188 JOIN "issue" ON "initiative"."issue_id" = "issue"."id" |
jbe@118 | 2189 LEFT JOIN "timeline" AS "initiative_creation" |
jbe@118 | 2190 ON "initiative"."id" = "initiative_creation"."initiative_id" |
jbe@118 | 2191 AND "initiative_creation"."event" = 'initiative_created' |
jbe@118 | 2192 AND "timeline"."occurrence" = "initiative_creation"."occurrence" |
jbe@118 | 2193 WHERE "timeline"."event" = 'draft_created' |
jbe@118 | 2194 AND "initiative_creation"."initiative_id" ISNULL |
jbe@118 | 2195 UNION ALL |
jbe@118 | 2196 SELECT |
jbe@118 | 2197 "timeline"."occurrence", |
jbe@118 | 2198 'suggestion_created'::"event_type", |
jbe@118 | 2199 "suggestion"."author_id", |
jbe@118 | 2200 "issue"."id", |
jbe@118 | 2201 CASE |
jbe@118 | 2202 WHEN "timeline"."occurrence" < "issue"."accepted" THEN |
jbe@118 | 2203 'admission'::"issue_state" |
jbe@118 | 2204 WHEN "timeline"."occurrence" < "issue"."half_frozen" THEN |
jbe@118 | 2205 'discussion'::"issue_state" |
jbe@118 | 2206 ELSE |
jbe@118 | 2207 'verification'::"issue_state" |
jbe@118 | 2208 END, |
jbe@118 | 2209 "initiative"."id", |
jbe@118 | 2210 NULL, |
jbe@118 | 2211 "suggestion"."id" |
jbe@118 | 2212 FROM "timeline" |
jbe@118 | 2213 JOIN "suggestion" ON "timeline"."suggestion_id" = "suggestion"."id" |
jbe@118 | 2214 JOIN "initiative" ON "suggestion"."initiative_id" = "initiative"."id" |
jbe@118 | 2215 JOIN "issue" ON "initiative"."issue_id" = "issue"."id" |
jbe@118 | 2216 WHERE "timeline"."event" = 'suggestion_created' |
jbe@118 | 2217 ) AS "subquery" |
jbe@118 | 2218 ORDER BY "occurrence"; |
jbe@118 | 2219 |
jbe@118 | 2220 |