liquid_feedback_core
changeset 112:1b1e266df99b
Column "revoked_by_member_id"; Implemented event system
- Added column "revoked_by_member_id" to table "initiative"
- Implemented event system (table "event" and triggers)
- Timeline deprecated (replaced by event system)
- Added column "revoked_by_member_id" to table "initiative"
- Implemented event system (table "event" and triggers)
- Timeline deprecated (replaced by event system)
author | jbe |
---|---|
date | Fri Mar 04 17:15:33 2011 +0100 (2011-03-04) |
parents | 844c442c5a80 |
children | 76ffbafb23b5 |
files | core.sql |
line diff
1.1 --- a/core.sql Thu Mar 03 00:01:37 2011 +0100 1.2 +++ b/core.sql Fri Mar 04 17:15:33 2011 +0100 1.3 @@ -386,7 +386,11 @@ 1.4 COMMENT ON TYPE "snapshot_event" IS 'Reason for snapshots: ''periodic'' = due to periodic recalculation, ''end_of_admission'' = saved state at end of admission period, ''half_freeze'' = saved state at end of discussion period, ''full_freeze'' = saved state at end of verification period'; 1.5 1.6 1.7 -CREATE TYPE "issue_state" AS ENUM ('admission', 'discussion', 'verification', 'voting', 'canceled_all_initiatives_revoked', 'canceled_issue_not_accepted', 'calculation', 'canceled_no_initiative_admitted', 'finished_without_winner', 'finished_with_winner'); 1.8 +CREATE TYPE "issue_state" AS ENUM ( 1.9 + 'admission', 'discussion', 'verification', 'voting', 1.10 + 'canceled_all_initiatives_revoked', 'canceled_issue_not_accepted', 1.11 + 'calculation', 'canceled_no_initiative_admitted', 1.12 + 'finished_without_winner', 'finished_with_winner'); 1.13 1.14 COMMENT ON TYPE "issue_state" IS 'State of issues'; 1.15 1.16 @@ -495,6 +499,7 @@ 1.17 "discussion_url" TEXT, 1.18 "created" TIMESTAMPTZ NOT NULL DEFAULT now(), 1.19 "revoked" TIMESTAMPTZ, 1.20 + "revoked_by_member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE, 1.21 "suggested_initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.22 "admitted" BOOLEAN, 1.23 "supporter_count" INT4, 1.24 @@ -506,6 +511,8 @@ 1.25 "agreed" BOOLEAN, 1.26 "rank" INT4, 1.27 "text_search_data" TSVECTOR, 1.28 + CONSTRAINT "all_or_none_of_revoked_and_revoked_by_member_id_must_be_null" 1.29 + CHECK ("revoked" NOTNULL = "revoked_by_member_id" NOTNULL), 1.30 CONSTRAINT "non_revoked_initiatives_cant_suggest_other" 1.31 CHECK ("revoked" NOTNULL OR "suggested_initiative_id" ISNULL), 1.32 CONSTRAINT "revoked_initiatives_cant_be_admitted" 1.33 @@ -527,9 +534,10 @@ 1.34 1.35 COMMENT ON TABLE "initiative" IS 'Group of members publishing drafts for resolutions to be passed; Frontends must ensure that initiatives of half_frozen issues are not revoked, and that initiatives of fully_frozen or closed issues are neither revoked nor created.'; 1.36 1.37 -COMMENT ON COLUMN "initiative"."discussion_url" IS 'URL pointing to a discussion platform for this initiative'; 1.38 -COMMENT ON COLUMN "initiative"."revoked" IS 'Point in time, when one initiator decided to revoke the initiative'; 1.39 -COMMENT ON COLUMN "initiative"."admitted" IS 'TRUE, if initiative reaches the "initiative_quorum" when freezing the issue'; 1.40 +COMMENT ON COLUMN "initiative"."discussion_url" IS 'URL pointing to a discussion platform for this initiative'; 1.41 +COMMENT ON COLUMN "initiative"."revoked" IS 'Point in time, when one initiator decided to revoke the initiative'; 1.42 +COMMENT ON COLUMN "initiative"."revoked_by_member_id" IS 'Member, who decided to revoked the initiative'; 1.43 +COMMENT ON COLUMN "initiative"."admitted" IS 'TRUE, if initiative reaches the "initiative_quorum" when freezing the issue'; 1.44 COMMENT ON COLUMN "initiative"."supporter_count" IS 'Calculated from table "direct_supporter_snapshot"'; 1.45 COMMENT ON COLUMN "initiative"."informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"'; 1.46 COMMENT ON COLUMN "initiative"."satisfied_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"'; 1.47 @@ -1000,15 +1008,70 @@ 1.48 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)'; 1.49 1.50 1.51 -CREATE TABLE "event" (); 1.52 - 1.53 -COMMENT ON TABLE "event" IS 'TODO'; 1.54 - 1.55 - 1.56 - 1.57 --------------------------------- 1.58 --- Writing of history entries -- 1.59 --------------------------------- 1.60 +CREATE TYPE "event_type" AS ENUM ( 1.61 + 'issue_state_changed', 1.62 + 'initiative_created_in_new_issue', 1.63 + 'initiative_created_in_existing_issue', 1.64 + 'initiative_revoked', 1.65 + 'new_draft_created', 1.66 + 'suggestion_created'); 1.67 + 1.68 +COMMENT ON TYPE "event_type" IS 'Type used for column "event" of table "event"'; 1.69 + 1.70 + 1.71 +CREATE TABLE "event" ( 1.72 + "id" SERIAL8 PRIMARY KEY, 1.73 + "occurrence" TIMESTAMPTZ NOT NULL DEFAULT now(), 1.74 + "event" "event_type" NOT NULL, 1.75 + "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE, 1.76 + "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.77 + "state" "issue_state" CHECK ("state" != 'calculation'), 1.78 + "initiative_id" INT4, 1.79 + "draft_id" INT8, 1.80 + "suggestion_id" INT8, 1.81 + FOREIGN KEY ("issue_id", "initiative_id") 1.82 + REFERENCES "initiative" ("issue_id", "id") 1.83 + ON DELETE CASCADE ON UPDATE CASCADE, 1.84 + FOREIGN KEY ("initiative_id", "draft_id") 1.85 + REFERENCES "draft" ("initiative_id", "id") 1.86 + ON DELETE CASCADE ON UPDATE CASCADE, 1.87 + FOREIGN KEY ("initiative_id", "suggestion_id") 1.88 + REFERENCES "suggestion" ("initiative_id", "id") 1.89 + ON DELETE CASCADE ON UPDATE CASCADE, 1.90 + CONSTRAINT "null_constraints_for_issue_state_changed" CHECK ( 1.91 + "event" != 'issue_state_changed' OR ( 1.92 + "member_id" ISNULL AND 1.93 + "issue_id" NOTNULL AND 1.94 + "initiative_id" ISNULL AND 1.95 + "draft_id" ISNULL AND 1.96 + "suggestion_id" ISNULL )), 1.97 + CONSTRAINT "null_constraints_for_initiative_creation_or_revocation_or_new_draft" CHECK ( 1.98 + "event" NOT IN ( 1.99 + 'initiative_created_in_new_issue', 1.100 + 'initiative_created_in_existing_issue', 1.101 + 'initiative_revoked', 1.102 + 'new_draft_created' 1.103 + ) OR ( 1.104 + "member_id" NOTNULL AND 1.105 + "issue_id" NOTNULL AND 1.106 + "initiative_id" NOTNULL AND 1.107 + "draft_id" NOTNULL AND 1.108 + "suggestion_id" ISNULL )), 1.109 + CONSTRAINT "null_constraints_for_suggestion_creation" CHECK ( 1.110 + "event" != 'suggestion_created' OR ( 1.111 + "member_id" NOTNULL AND 1.112 + "issue_id" NOTNULL AND 1.113 + "initiative_id" NOTNULL AND 1.114 + "draft_id" ISNULL AND 1.115 + "suggestion_id" NOTNULL )) ); 1.116 + 1.117 +COMMENT ON TABLE "event" IS 'Event table, automatically filled by triggers'; 1.118 + 1.119 + 1.120 + 1.121 +---------------------------------------------- 1.122 +-- Writing of history entries and event log -- 1.123 +---------------------------------------------- 1.124 1.125 CREATE FUNCTION "write_member_history_trigger"() 1.126 RETURNS TRIGGER 1.127 @@ -1034,6 +1097,127 @@ 1.128 COMMENT ON TRIGGER "write_member_history" ON "member" IS 'When changing certain fields of a member, create a history entry in "member_history" table'; 1.129 1.130 1.131 +CREATE FUNCTION "write_event_issue_state_changed_trigger"() 1.132 + RETURNS TRIGGER 1.133 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.134 + BEGIN 1.135 + IF NEW."state" != OLD."state" AND NEW."state" != 'calculation' THEN 1.136 + INSERT INTO "event" ("event", "issue_id", "state") 1.137 + VALUES ('issue_state_changed', NEW."id", NEW."state"); 1.138 + END IF; 1.139 + RETURN NULL; 1.140 + END; 1.141 + $$; 1.142 + 1.143 +CREATE TRIGGER "write_event_issue_state_changed" 1.144 + AFTER UPDATE ON "issue" FOR EACH ROW EXECUTE PROCEDURE 1.145 + "write_event_issue_state_changed_trigger"(); 1.146 + 1.147 +COMMENT ON FUNCTION "write_event_issue_state_changed_trigger"() IS 'Implementation of trigger "write_event_issue_state_changed" on table "issue"'; 1.148 +COMMENT ON TRIGGER "write_event_issue_state_changed" ON "issue" IS 'Create entry in "event" table on "state" change'; 1.149 + 1.150 + 1.151 +CREATE FUNCTION "write_event_initiative_or_draft_created_trigger"() 1.152 + RETURNS TRIGGER 1.153 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.154 + DECLARE 1.155 + "initiative_row" "initiative"%ROWTYPE; 1.156 + "event_v" "event_type"; 1.157 + BEGIN 1.158 + SELECT * INTO "initiative_row" FROM "initiative" 1.159 + WHERE "id" = NEW."initiative_id"; 1.160 + IF EXISTS ( 1.161 + SELECT NULL FROM "draft" 1.162 + WHERE "initiative_id" = NEW."initiative_id" 1.163 + AND "id" != NEW."id" 1.164 + ) THEN 1.165 + "event_v" := 'new_draft_created'; 1.166 + ELSE 1.167 + IF EXISTS ( 1.168 + SELECT NULL FROM "initiative" 1.169 + WHERE "issue_id" = "initiative_row"."issue_id" 1.170 + AND "id" != "initiative_row"."id" 1.171 + ) THEN 1.172 + "event_v" := 'initiative_created_in_existing_issue'; 1.173 + ELSE 1.174 + "event_v" := 'initiative_created_in_new_issue'; 1.175 + END IF; 1.176 + END IF; 1.177 + INSERT INTO "event" ( 1.178 + "event", "member_id", 1.179 + "issue_id", "initiative_id", "draft_id" 1.180 + ) VALUES ( 1.181 + "event_v", 1.182 + NEW."author_id", 1.183 + "initiative_row"."issue_id", 1.184 + "initiative_row"."id", 1.185 + NEW."id" ); 1.186 + RETURN NULL; 1.187 + END; 1.188 + $$; 1.189 + 1.190 +CREATE TRIGGER "write_event_initiative_or_draft_created" 1.191 + AFTER INSERT ON "draft" FOR EACH ROW EXECUTE PROCEDURE 1.192 + "write_event_initiative_or_draft_created_trigger"(); 1.193 + 1.194 +COMMENT ON FUNCTION "write_event_initiative_or_draft_created_trigger"() IS 'Implementation of trigger "write_event_initiative_or_draft_created" on table "issue"'; 1.195 +COMMENT ON TRIGGER "write_event_initiative_or_draft_created" ON "draft" IS 'Create entry in "event" table on draft creation'; 1.196 + 1.197 + 1.198 +CREATE FUNCTION "write_event_initiative_revoked_trigger"() 1.199 + RETURNS TRIGGER 1.200 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.201 + BEGIN 1.202 + IF OLD."revoked" ISNULL AND NEW."revoked" NOTNULL THEN 1.203 + INSERT INTO "event" ( 1.204 + "event", "member_id", "issue_id", "initiative_id" 1.205 + ) VALUES ( 1.206 + 'initiative_revoked', 1.207 + NEW."revoked_by_member_id", 1.208 + NEW."issue_id", 1.209 + NEW."id" ); 1.210 + END IF; 1.211 + RETURN NULL; 1.212 + END; 1.213 + $$; 1.214 + 1.215 +CREATE TRIGGER "write_event_initiative_revoked" 1.216 + AFTER UPDATE ON "initiative" FOR EACH ROW EXECUTE PROCEDURE 1.217 + "write_event_initiative_revoked_trigger"(); 1.218 + 1.219 +COMMENT ON FUNCTION "write_event_initiative_revoked_trigger"() IS 'Implementation of trigger "write_event_initiative_revoked" on table "issue"'; 1.220 +COMMENT ON TRIGGER "write_event_initiative_revoked" ON "initiative" IS 'Create entry in "event" table, when an initiative is revoked'; 1.221 + 1.222 + 1.223 +CREATE FUNCTION "write_event_suggestion_created_trigger"() 1.224 + RETURNS TRIGGER 1.225 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.226 + DECLARE 1.227 + "initiative_row" "initiative"%ROWTYPE; 1.228 + BEGIN 1.229 + SELECT * INTO "initiative_row" FROM "initiative" 1.230 + WHERE "id" = NEW."initiative_id"; 1.231 + INSERT INTO "event" ( 1.232 + "event", "member_id", 1.233 + "issue_id", "initiative_id", "suggestion_id" 1.234 + ) VALUES ( 1.235 + 'suggestion_created', 1.236 + NEW."author_id", 1.237 + "initiative_row"."issue_id", 1.238 + "initiative_row"."id", 1.239 + NEW."id" ); 1.240 + RETURN NULL; 1.241 + END; 1.242 + $$; 1.243 + 1.244 +CREATE TRIGGER "write_event_suggestion_created" 1.245 + AFTER INSERT ON "suggestion" FOR EACH ROW EXECUTE PROCEDURE 1.246 + "write_event_suggestion_created_trigger"(); 1.247 + 1.248 +COMMENT ON FUNCTION "write_event_suggestion_created_trigger"() IS 'Implementation of trigger "write_event_suggestion_created" on table "issue"'; 1.249 +COMMENT ON TRIGGER "write_event_suggestion_created" ON "suggestion" IS 'Create entry in "event" table on suggestion creation'; 1.250 + 1.251 + 1.252 1.253 ---------------------------- 1.254 -- Additional constraints -- 1.255 @@ -1766,7 +1950,7 @@ 1.256 'draft_created', 1.257 'suggestion_created'); 1.258 1.259 -COMMENT ON TYPE "timeline_event" IS 'Types of event in timeline tables'; 1.260 +COMMENT ON TYPE "timeline_event" IS 'Types of event in timeline tables (DEPRECATED)'; 1.261 1.262 1.263 CREATE VIEW "timeline_issue" AS 1.264 @@ -1812,7 +1996,7 @@ 1.265 "id" AS "issue_id" 1.266 FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" NOTNULL; 1.267 1.268 -COMMENT ON VIEW "timeline_issue" IS 'Helper view for "timeline" view'; 1.269 +COMMENT ON VIEW "timeline_issue" IS 'Helper view for "timeline" view (DEPRECATED)'; 1.270 1.271 1.272 CREATE VIEW "timeline_initiative" AS 1.273 @@ -1828,7 +2012,7 @@ 1.274 "id" AS "initiative_id" 1.275 FROM "initiative" WHERE "revoked" NOTNULL; 1.276 1.277 -COMMENT ON VIEW "timeline_initiative" IS 'Helper view for "timeline" view'; 1.278 +COMMENT ON VIEW "timeline_initiative" IS 'Helper view for "timeline" view (DEPRECATED)'; 1.279 1.280 1.281 CREATE VIEW "timeline_draft" AS 1.282 @@ -1838,7 +2022,7 @@ 1.283 "id" AS "draft_id" 1.284 FROM "draft"; 1.285 1.286 -COMMENT ON VIEW "timeline_draft" IS 'Helper view for "timeline" view'; 1.287 +COMMENT ON VIEW "timeline_draft" IS 'Helper view for "timeline" view (DEPRECATED)'; 1.288 1.289 1.290 CREATE VIEW "timeline_suggestion" AS 1.291 @@ -1848,7 +2032,7 @@ 1.292 "id" AS "suggestion_id" 1.293 FROM "suggestion"; 1.294 1.295 -COMMENT ON VIEW "timeline_suggestion" IS 'Helper view for "timeline" view'; 1.296 +COMMENT ON VIEW "timeline_suggestion" IS 'Helper view for "timeline" view (DEPRECATED)'; 1.297 1.298 1.299 CREATE VIEW "timeline" AS 1.300 @@ -1888,7 +2072,7 @@ 1.301 "suggestion_id" 1.302 FROM "timeline_suggestion"; 1.303 1.304 -COMMENT ON VIEW "timeline" IS 'Aggregation of different events in the system'; 1.305 +COMMENT ON VIEW "timeline" IS 'Aggregation of different events in the system (DEPRECATED)'; 1.306 1.307 1.308