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