liquid_feedback_core
changeset 536:750b0be5acb6
Work on unit/area/policy events: added column "policy_id" to "event" table, added new "event_type"s
author | jbe |
---|---|
date | Mon Jun 26 15:54:26 2017 +0200 (2017-06-26) |
parents | 8b6433096a58 |
children | aa261389c993 |
files | core.sql update/core-update.v3.2.2-v4.0.0.sql |
line diff
1.1 --- a/core.sql Sun Jun 18 02:24:50 2017 +0200 1.2 +++ b/core.sql Mon Jun 26 15:54:26 2017 +0200 1.3 @@ -738,6 +738,7 @@ 1.4 1.5 CREATE TABLE "issue" ( 1.6 UNIQUE ("area_id", "id"), -- index needed for foreign-key on table "event" 1.7 + UNIQUE ("policy_id", "id"), -- index needed for foreign-key on table "event" 1.8 "id" SERIAL4 PRIMARY KEY, 1.9 "area_id" INT4 NOT NULL REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.10 "policy_id" INT4 NOT NULL REFERENCES "policy" ("id") ON DELETE RESTRICT ON UPDATE CASCADE, 1.11 @@ -804,7 +805,6 @@ 1.12 --("accepted" ISNULL OR "admission_snapshot_id" NOTNULL) AND 1.13 ("half_frozen" ISNULL OR "half_freeze_snapshot_id" NOTNULL) AND 1.14 ("fully_frozen" ISNULL OR "full_freeze_snapshot_id" NOTNULL) ) ); 1.15 -CREATE INDEX "issue_policy_id_idx" ON "issue" ("policy_id"); 1.16 CREATE INDEX "issue_state_idx" ON "issue" ("state"); 1.17 CREATE INDEX "issue_created_idx" ON "issue" ("created"); 1.18 CREATE INDEX "issue_accepted_idx" ON "issue" ("accepted"); 1.19 @@ -1382,6 +1382,15 @@ 1.20 1.21 1.22 CREATE TYPE "event_type" AS ENUM ( 1.23 + 'unit_created', 1.24 + 'unit_updated', 1.25 + 'unit_removed', 1.26 + 'subject_area_created', 1.27 + 'subject_area_updated', 1.28 + 'subject_area_removed', 1.29 + 'policy_created', 1.30 + 'policy_updated', 1.31 + 'policy_removed', 1.32 'issue_state_changed', 1.33 'initiative_created_in_new_issue', 1.34 'initiative_created_in_existing_issue', 1.35 @@ -1416,8 +1425,10 @@ 1.36 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.37 "area_id" INT4, 1.38 FOREIGN KEY ("unit_id", "area_id") REFERENCES "area" ("unit_id", "id") ON DELETE CASCADE ON UPDATE CASCADE, 1.39 + "policy_id" INT4 REFERENCES "policy" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.40 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.41 FOREIGN KEY ("area_id", "issue_id") REFERENCES "issue" ("area_id", "id") ON DELETE CASCADE ON UPDATE CASCADE, 1.42 + FOREIGN KEY ("policy_id", "issue_id") REFERENCES "issue" ("policy_id", "id") ON DELETE CASCADE ON UPDATE CASCADE, 1.43 "state" "issue_state", 1.44 "initiative_id" INT4, 1.45 "draft_id" INT8, 1.46 @@ -1444,6 +1455,7 @@ 1.47 "scope" ISNULL AND 1.48 "unit_id" NOTNULL AND 1.49 "area_id" NOTNULL AND 1.50 + "policy_id" NOTNULL AND 1.51 "issue_id" NOTNULL AND 1.52 "state" NOTNULL AND 1.53 "initiative_id" ISNULL AND 1.54 @@ -1465,6 +1477,7 @@ 1.55 "scope" ISNULL AND 1.56 "unit_id" NOTNULL AND 1.57 "area_id" NOTNULL AND 1.58 + "policy_id" NOTNULL AND 1.59 "issue_id" NOTNULL AND 1.60 "state" NOTNULL AND 1.61 "initiative_id" NOTNULL AND 1.62 @@ -1481,6 +1494,7 @@ 1.63 "scope" ISNULL AND 1.64 "unit_id" NOTNULL AND 1.65 "area_id" NOTNULL AND 1.66 + "policy_id" NOTNULL AND 1.67 "issue_id" NOTNULL AND 1.68 "state" NOTNULL AND 1.69 "initiative_id" NOTNULL AND 1.70 @@ -1497,6 +1511,7 @@ 1.71 "scope" ISNULL AND 1.72 "unit_id" NOTNULL AND 1.73 "area_id" NOTNULL AND 1.74 + "policy_id" NOTNULL AND 1.75 "issue_id" NOTNULL AND 1.76 "state" NOTNULL AND 1.77 "initiative_id" NOTNULL AND 1.78 @@ -1518,6 +1533,7 @@ 1.79 "scope" ISNULL AND 1.80 "unit_id" ISNULL AND 1.81 "area_id" ISNULL AND 1.82 + "policy_id" ISNULL AND 1.83 "issue_id" ISNULL AND 1.84 "state" ISNULL AND 1.85 "initiative_id" ISNULL AND 1.86 @@ -1534,6 +1550,7 @@ 1.87 "scope" ISNULL AND 1.88 "unit_id" ISNULL AND 1.89 "area_id" ISNULL AND 1.90 + "policy_id" ISNULL AND 1.91 "issue_id" ISNULL AND 1.92 "state" ISNULL AND 1.93 "initiative_id" ISNULL AND 1.94 @@ -1550,6 +1567,7 @@ 1.95 "scope" ISNULL AND 1.96 "unit_id" ISNULL AND 1.97 "area_id" ISNULL AND 1.98 + "policy_id" ISNULL AND 1.99 "issue_id" ISNULL AND 1.100 "state" ISNULL AND 1.101 "initiative_id" ISNULL AND 1.102 @@ -1566,6 +1584,7 @@ 1.103 "scope" ISNULL AND 1.104 "unit_id" NOTNULL AND 1.105 "area_id" NOTNULL AND 1.106 + "policy_id" NOTNULL AND 1.107 "issue_id" NOTNULL AND 1.108 "state" NOTNULL AND 1.109 "initiative_id" ISNULL AND 1.110 @@ -1582,6 +1601,7 @@ 1.111 "scope" ISNULL AND 1.112 "unit_id" NOTNULL AND 1.113 "area_id" NOTNULL AND 1.114 + "policy_id" NOTNULL AND 1.115 "issue_id" NOTNULL AND 1.116 "state" NOTNULL AND 1.117 "initiative_id" NOTNULL AND 1.118 @@ -1598,6 +1618,7 @@ 1.119 "scope" ISNULL AND 1.120 "unit_id" NOTNULL AND 1.121 "area_id" NOTNULL AND 1.122 + "policy_id" NOTNULL AND 1.123 "issue_id" NOTNULL AND 1.124 "state" NOTNULL AND 1.125 "initiative_id" NOTNULL AND 1.126 @@ -1614,6 +1635,7 @@ 1.127 "scope" ISNULL AND 1.128 "unit_id" NOTNULL AND 1.129 "area_id" NOTNULL AND 1.130 + "policy_id" NOTNULL AND 1.131 "issue_id" NOTNULL AND 1.132 "state" NOTNULL AND 1.133 "initiative_id" NOTNULL AND 1.134 @@ -1630,6 +1652,7 @@ 1.135 "scope" ISNULL AND 1.136 "unit_id" NOTNULL AND 1.137 "area_id" NOTNULL AND 1.138 + "policy_id" NOTNULL AND 1.139 "issue_id" NOTNULL AND 1.140 "state" NOTNULL AND 1.141 "initiative_id" NOTNULL AND 1.142 @@ -1647,6 +1670,7 @@ 1.143 "scope" NOTNULL AND 1.144 "unit_id" NOTNULL AND 1.145 ("area_id" NOTNULL) = ("scope" != 'unit'::"delegation_scope") AND 1.146 + "policy_id" ISNULL AND 1.147 ("issue_id" NOTNULL) = ("scope" = 'issue'::"delegation_scope") AND 1.148 ("state" NOTNULL) = ("scope" = 'issue'::"delegation_scope") AND 1.149 "initiative_id" ISNULL AND 1.150 @@ -1663,6 +1687,7 @@ 1.151 "scope" ISNULL AND 1.152 "unit_id" ISNULL AND 1.153 "area_id" ISNULL AND 1.154 + "policy_id" ISNULL AND 1.155 "issue_id" ISNULL AND 1.156 "state" ISNULL AND 1.157 "initiative_id" ISNULL AND 1.158 @@ -1768,10 +1793,11 @@ 1.159 FOR SHARE; 1.160 INSERT INTO "event" ( 1.161 "event", 1.162 - "unit_id", "area_id", "issue_id", "state" 1.163 + "unit_id", "area_id", "policy_id", "issue_id", "state" 1.164 ) VALUES ( 1.165 'issue_state_changed', 1.166 - "area_row"."unit_id", NEW."area_id", NEW."id", NEW."state" 1.167 + "area_row"."unit_id", NEW."area_id", NEW."policy_id", 1.168 + NEW."id", NEW."state" 1.169 ); 1.170 END IF; 1.171 RETURN NULL; 1.172 @@ -1821,11 +1847,11 @@ 1.173 END IF; 1.174 INSERT INTO "event" ( 1.175 "event", "member_id", 1.176 - "unit_id", "area_id", "issue_id", "state", 1.177 + "unit_id", "area_id", "policy_id", "issue_id", "state", 1.178 "initiative_id", "draft_id" 1.179 ) VALUES ( 1.180 "event_v", NEW."author_id", 1.181 - "area_row"."unit_id", "issue_row"."area_id", 1.182 + "area_row"."unit_id", "issue_row"."area_id", "issue_row"."policy_id", 1.183 "initiative_row"."issue_id", "issue_row"."state", 1.184 NEW."initiative_id", NEW."id" 1.185 ); 1.186 @@ -1858,11 +1884,12 @@ 1.187 WHERE "initiative_id" = NEW."id" FOR SHARE; 1.188 INSERT INTO "event" ( 1.189 "event", "member_id", 1.190 - "unit_id", "area_id", "issue_id", "state", 1.191 + "unit_id", "area_id", "policy_id", "issue_id", "state", 1.192 "initiative_id", "draft_id" 1.193 ) VALUES ( 1.194 'initiative_revoked', NEW."revoked_by_member_id", 1.195 "area_row"."unit_id", "issue_row"."area_id", 1.196 + "issue_row"."policy_id", 1.197 NEW."issue_id", "issue_row"."state", 1.198 NEW."id", "draft_id_v" 1.199 ); 1.200 @@ -1895,11 +1922,11 @@ 1.201 WHERE "id" = "issue_row"."area_id" FOR SHARE; 1.202 INSERT INTO "event" ( 1.203 "event", "member_id", 1.204 - "unit_id", "area_id", "issue_id", "state", 1.205 + "unit_id", "area_id", "policy_id", "issue_id", "state", 1.206 "initiative_id", "suggestion_id" 1.207 ) VALUES ( 1.208 'suggestion_created', NEW."author_id", 1.209 - "area_row"."unit_id", "issue_row"."area_id", 1.210 + "area_row"."unit_id", "issue_row"."area_id", "issue_row"."policy_id", 1.211 "initiative_row"."issue_id", "issue_row"."state", 1.212 NEW."initiative_id", NEW."id" 1.213 ); 1.214 @@ -1932,11 +1959,12 @@ 1.215 WHERE "id" = "issue_row"."area_id" FOR SHARE; 1.216 INSERT INTO "event" ( 1.217 "event", 1.218 - "unit_id", "area_id", "issue_id", "state", 1.219 + "unit_id", "area_id", "policy_id", "issue_id", "state", 1.220 "initiative_id", "suggestion_id" 1.221 ) VALUES ( 1.222 'suggestion_removed', 1.223 "area_row"."unit_id", "issue_row"."area_id", 1.224 + "issue_row"."policy_id", 1.225 "initiative_row"."issue_id", "issue_row"."state", 1.226 OLD."initiative_id", OLD."id" 1.227 ); 1.228 @@ -1984,6 +2012,7 @@ 1.229 END IF; 1.230 IF 1.231 OLD."activated" NOTNULL AND 1.232 + -- TODO: NEW."activated" ISNULL? OLD."login" NOTNULL? 1.233 NEW."last_login" ISNULL AND 1.234 NEW."login" ISNULL AND 1.235 NEW."authority_login" ISNULL AND 1.236 @@ -2100,11 +2129,12 @@ 1.237 IF "issue_row"."id" NOTNULL THEN 1.238 INSERT INTO "event" ( 1.239 "event", "member_id", 1.240 - "unit_id", "area_id", "issue_id", "state", 1.241 + "unit_id", "area_id", "policy_id", "issue_id", "state", 1.242 "boolean_value" 1.243 ) VALUES ( 1.244 'interest', OLD."member_id", 1.245 "area_row"."unit_id", "issue_row"."area_id", 1.246 + "issue_row"."policy_id", 1.247 OLD."issue_id", "issue_row"."state", 1.248 FALSE 1.249 ); 1.250 @@ -2117,11 +2147,12 @@ 1.251 WHERE "id" = "issue_row"."area_id" FOR SHARE; 1.252 INSERT INTO "event" ( 1.253 "event", "member_id", 1.254 - "unit_id", "area_id", "issue_id", "state", 1.255 + "unit_id", "area_id", "policy_id", "issue_id", "state", 1.256 "boolean_value" 1.257 ) VALUES ( 1.258 'interest', NEW."member_id", 1.259 "area_row"."unit_id", "issue_row"."area_id", 1.260 + "issue_row"."policy_id", 1.261 NEW."issue_id", "issue_row"."state", 1.262 TRUE 1.263 ); 1.264 @@ -2166,11 +2197,12 @@ 1.265 WHERE "id" = "issue_row"."area_id" FOR SHARE; 1.266 INSERT INTO "event" ( 1.267 "event", "member_id", 1.268 - "unit_id", "area_id", "issue_id", "state", 1.269 + "unit_id", "area_id", "policy_id", "issue_id", "state", 1.270 "initiative_id", "boolean_value" 1.271 ) VALUES ( 1.272 'initiator', OLD."member_id", 1.273 "area_row"."unit_id", "issue_row"."area_id", 1.274 + "issue_row"."policy_id", 1.275 "issue_row"."id", "issue_row"."state", 1.276 OLD."initiative_id", FALSE 1.277 ); 1.278 @@ -2187,11 +2219,12 @@ 1.279 WHERE "id" = "issue_row"."area_id" FOR SHARE; 1.280 INSERT INTO "event" ( 1.281 "event", "member_id", 1.282 - "unit_id", "area_id", "issue_id", "state", 1.283 + "unit_id", "area_id", "policy_id", "issue_id", "state", 1.284 "initiative_id", "boolean_value" 1.285 ) VALUES ( 1.286 'initiator', NEW."member_id", 1.287 "area_row"."unit_id", "issue_row"."area_id", 1.288 + "issue_row"."policy_id", 1.289 "issue_row"."id", "issue_row"."state", 1.290 NEW."initiative_id", TRUE 1.291 ); 1.292 @@ -2228,11 +2261,12 @@ 1.293 WHERE "id" = "issue_row"."area_id" FOR SHARE; 1.294 INSERT INTO "event" ( 1.295 "event", "member_id", 1.296 - "unit_id", "area_id", "issue_id", "state", 1.297 + "unit_id", "area_id", "policy_id", "issue_id", "state", 1.298 "initiative_id", "draft_id" 1.299 ) VALUES ( 1.300 'support_updated', NEW."member_id", 1.301 "area_row"."unit_id", "issue_row"."area_id", 1.302 + "issue_row"."policy_id", 1.303 "issue_row"."id", "issue_row"."state", 1.304 NEW."initiative_id", NEW."draft_id" 1.305 ); 1.306 @@ -2251,11 +2285,12 @@ 1.307 WHERE "id" = "issue_row"."area_id" FOR SHARE; 1.308 INSERT INTO "event" ( 1.309 "event", "member_id", 1.310 - "unit_id", "area_id", "issue_id", "state", 1.311 + "unit_id", "area_id", "policy_id", "issue_id", "state", 1.312 "initiative_id", "boolean_value" 1.313 ) VALUES ( 1.314 'support', OLD."member_id", 1.315 "area_row"."unit_id", "issue_row"."area_id", 1.316 + "issue_row"."policy_id", 1.317 "issue_row"."id", "issue_row"."state", 1.318 OLD."initiative_id", FALSE 1.319 ); 1.320 @@ -2268,11 +2303,12 @@ 1.321 WHERE "id" = "issue_row"."area_id" FOR SHARE; 1.322 INSERT INTO "event" ( 1.323 "event", "member_id", 1.324 - "unit_id", "area_id", "issue_id", "state", 1.325 + "unit_id", "area_id", "policy_id", "issue_id", "state", 1.326 "initiative_id", "draft_id", "boolean_value" 1.327 ) VALUES ( 1.328 'support', NEW."member_id", 1.329 "area_row"."unit_id", "issue_row"."area_id", 1.330 + "issue_row"."policy_id", 1.331 "issue_row"."id", "issue_row"."state", 1.332 NEW."initiative_id", NEW."draft_id", TRUE 1.333 ); 1.334 @@ -2325,12 +2361,13 @@ 1.335 WHERE "id" = "issue_row"."area_id" FOR SHARE; 1.336 INSERT INTO "event" ( 1.337 "event", "member_id", 1.338 - "unit_id", "area_id", "issue_id", "state", 1.339 + "unit_id", "area_id", "policy_id", "issue_id", "state", 1.340 "initiative_id", "suggestion_id", 1.341 "boolean_value", "numeric_value" 1.342 ) VALUES ( 1.343 'suggestion_rated', OLD."member_id", 1.344 "area_row"."unit_id", "issue_row"."area_id", 1.345 + "issue_row"."policy_id", 1.346 "initiative_row"."issue_id", "issue_row"."state", 1.347 OLD."initiative_id", OLD."suggestion_id", 1.348 NULL, 0 1.349 @@ -2346,12 +2383,13 @@ 1.350 WHERE "id" = "issue_row"."area_id" FOR SHARE; 1.351 INSERT INTO "event" ( 1.352 "event", "member_id", 1.353 - "unit_id", "area_id", "issue_id", "state", 1.354 + "unit_id", "area_id", "policy_id", "issue_id", "state", 1.355 "initiative_id", "suggestion_id", 1.356 "boolean_value", "numeric_value" 1.357 ) VALUES ( 1.358 'suggestion_rated', NEW."member_id", 1.359 "area_row"."unit_id", "issue_row"."area_id", 1.360 + "issue_row"."policy_id", 1.361 "initiative_row"."issue_id", "issue_row"."state", 1.362 NEW."initiative_id", NEW."suggestion_id", 1.363 NEW."fulfilled", NEW."degree"
2.1 --- a/update/core-update.v3.2.2-v4.0.0.sql Sun Jun 18 02:24:50 2017 +0200 2.2 +++ b/update/core-update.v3.2.2-v4.0.0.sql Mon Jun 26 15:54:26 2017 +0200 2.3 @@ -1,3 +1,12 @@ 2.4 +ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'unit_created'; 2.5 +ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'unit_updated'; 2.6 +ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'unit_removed'; 2.7 +ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'subject_area_created'; 2.8 +ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'subject_area_updated'; 2.9 +ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'subject_area_removed'; 2.10 +ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'policy_created'; 2.11 +ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'policy_updated'; 2.12 +ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'policy_removed'; 2.13 ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'suggestion_removed'; 2.14 ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'member_activated'; 2.15 ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'member_removed'; 2.16 @@ -472,6 +481,8 @@ 2.17 2.18 ALTER TABLE "issue" ADD UNIQUE ("area_id", "id"); 2.19 DROP INDEX "issue_area_id_idx"; 2.20 +ALTER TABLE "issue" ADD UNIQUE ("policy_id", "id"); 2.21 +DROP INDEX "issue_policy_id_idx"; 2.22 2.23 ALTER TABLE "issue" RENAME COLUMN "snapshot" TO "calculated"; 2.24 2.25 @@ -653,6 +664,7 @@ 2.26 ALTER TABLE "event" ADD COLUMN "scope" "delegation_scope"; 2.27 ALTER TABLE "event" ADD COLUMN "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE; 2.28 ALTER TABLE "event" ADD COLUMN "area_id" INT4; 2.29 +ALTER TABLE "event" ADD COLUMN "policy_id" INT4 REFERENCES "policy" ("id") ON DELETE CASCADE ON UPDATE CASCADE; 2.30 ALTER TABLE "event" ADD COLUMN "boolean_value" BOOLEAN; 2.31 ALTER TABLE "event" ADD COLUMN "numeric_value" INT4; 2.32 ALTER TABLE "event" ADD COLUMN "text_value" TEXT; 2.33 @@ -660,6 +672,7 @@ 2.34 2.35 ALTER TABLE "event" ADD FOREIGN KEY ("unit_id", "area_id") REFERENCES "area" ("unit_id", "id") ON DELETE CASCADE ON UPDATE CASCADE; 2.36 ALTER TABLE "event" ADD FOREIGN KEY ("area_id", "issue_id") REFERENCES "issue" ("area_id", "id") ON DELETE CASCADE ON UPDATE CASCADE; 2.37 +ALTER TABLE "event" ADD FOREIGN KEY ("policy_id", "issue_id") REFERENCES "issue" ("policy_id", "id") ON DELETE CASCADE ON UPDATE CASCADE; 2.38 2.39 ALTER TABLE "event" DROP CONSTRAINT "event_initiative_id_fkey1"; 2.40 ALTER TABLE "event" DROP CONSTRAINT "null_constr_for_issue_state_changed"; 2.41 @@ -677,6 +690,7 @@ 2.42 "scope" ISNULL AND 2.43 "unit_id" NOTNULL AND 2.44 "area_id" NOTNULL AND 2.45 + "policy_id" NOTNULL AND 2.46 "issue_id" NOTNULL AND 2.47 "state" NOTNULL AND 2.48 "initiative_id" ISNULL AND 2.49 @@ -698,6 +712,7 @@ 2.50 "scope" ISNULL AND 2.51 "unit_id" NOTNULL AND 2.52 "area_id" NOTNULL AND 2.53 + "policy_id" NOTNULL AND 2.54 "issue_id" NOTNULL AND 2.55 "state" NOTNULL AND 2.56 "initiative_id" NOTNULL AND 2.57 @@ -714,6 +729,7 @@ 2.58 "scope" ISNULL AND 2.59 "unit_id" NOTNULL AND 2.60 "area_id" NOTNULL AND 2.61 + "policy_id" NOTNULL AND 2.62 "issue_id" NOTNULL AND 2.63 "state" NOTNULL AND 2.64 "initiative_id" NOTNULL AND 2.65 @@ -730,6 +746,7 @@ 2.66 "scope" ISNULL AND 2.67 "unit_id" NOTNULL AND 2.68 "area_id" NOTNULL AND 2.69 + "policy_id" NOTNULL AND 2.70 "issue_id" NOTNULL AND 2.71 "state" NOTNULL AND 2.72 "initiative_id" NOTNULL AND 2.73 @@ -751,6 +768,7 @@ 2.74 "scope" ISNULL AND 2.75 "unit_id" ISNULL AND 2.76 "area_id" ISNULL AND 2.77 + "policy_id" ISNULL AND 2.78 "issue_id" ISNULL AND 2.79 "state" ISNULL AND 2.80 "initiative_id" ISNULL AND 2.81 @@ -767,6 +785,7 @@ 2.82 "scope" ISNULL AND 2.83 "unit_id" ISNULL AND 2.84 "area_id" ISNULL AND 2.85 + "policy_id" ISNULL AND 2.86 "issue_id" ISNULL AND 2.87 "state" ISNULL AND 2.88 "initiative_id" ISNULL AND 2.89 @@ -783,6 +802,7 @@ 2.90 "scope" ISNULL AND 2.91 "unit_id" ISNULL AND 2.92 "area_id" ISNULL AND 2.93 + "policy_id" ISNULL AND 2.94 "issue_id" ISNULL AND 2.95 "state" ISNULL AND 2.96 "initiative_id" ISNULL AND 2.97 @@ -799,6 +819,7 @@ 2.98 "scope" ISNULL AND 2.99 "unit_id" NOTNULL AND 2.100 "area_id" NOTNULL AND 2.101 + "policy_id" NOTNULL AND 2.102 "issue_id" NOTNULL AND 2.103 "state" NOTNULL AND 2.104 "initiative_id" ISNULL AND 2.105 @@ -815,6 +836,7 @@ 2.106 "scope" ISNULL AND 2.107 "unit_id" NOTNULL AND 2.108 "area_id" NOTNULL AND 2.109 + "policy_id" NOTNULL AND 2.110 "issue_id" NOTNULL AND 2.111 "state" NOTNULL AND 2.112 "initiative_id" NOTNULL AND 2.113 @@ -831,6 +853,7 @@ 2.114 "scope" ISNULL AND 2.115 "unit_id" NOTNULL AND 2.116 "area_id" NOTNULL AND 2.117 + "policy_id" NOTNULL AND 2.118 "issue_id" NOTNULL AND 2.119 "state" NOTNULL AND 2.120 "initiative_id" NOTNULL AND 2.121 @@ -847,6 +870,7 @@ 2.122 "scope" ISNULL AND 2.123 "unit_id" NOTNULL AND 2.124 "area_id" NOTNULL AND 2.125 + "policy_id" NOTNULL AND 2.126 "issue_id" NOTNULL AND 2.127 "state" NOTNULL AND 2.128 "initiative_id" NOTNULL AND 2.129 @@ -863,6 +887,7 @@ 2.130 "scope" ISNULL AND 2.131 "unit_id" NOTNULL AND 2.132 "area_id" NOTNULL AND 2.133 + "policy_id" NOTNULL AND 2.134 "issue_id" NOTNULL AND 2.135 "state" NOTNULL AND 2.136 "initiative_id" NOTNULL AND 2.137 @@ -880,6 +905,7 @@ 2.138 "scope" NOTNULL AND 2.139 "unit_id" NOTNULL AND 2.140 ("area_id" NOTNULL) = ("scope" != 'unit'::"delegation_scope") AND 2.141 + "policy_id" ISNULL AND 2.142 ("issue_id" NOTNULL) = ("scope" = 'issue'::"delegation_scope") AND 2.143 ("state" NOTNULL) = ("scope" = 'issue'::"delegation_scope") AND 2.144 "initiative_id" ISNULL AND 2.145 @@ -896,6 +922,7 @@ 2.146 "scope" ISNULL AND 2.147 "unit_id" ISNULL AND 2.148 "area_id" ISNULL AND 2.149 + "policy_id" ISNULL AND 2.150 "issue_id" ISNULL AND 2.151 "state" ISNULL AND 2.152 "initiative_id" ISNULL AND 2.153 @@ -925,10 +952,11 @@ 2.154 FOR SHARE; 2.155 INSERT INTO "event" ( 2.156 "event", 2.157 - "unit_id", "area_id", "issue_id", "state" 2.158 + "unit_id", "area_id", "policy_id", "issue_id", "state" 2.159 ) VALUES ( 2.160 'issue_state_changed', 2.161 - "area_row"."unit_id", NEW."area_id", NEW."id", NEW."state" 2.162 + "area_row"."unit_id", NEW."area_id", NEW."policy_id", 2.163 + NEW."id", NEW."state" 2.164 ); 2.165 END IF; 2.166 RETURN NULL; 2.167 @@ -971,11 +999,11 @@ 2.168 END IF; 2.169 INSERT INTO "event" ( 2.170 "event", "member_id", 2.171 - "unit_id", "area_id", "issue_id", "state", 2.172 + "unit_id", "area_id", "policy_id", "issue_id", "state", 2.173 "initiative_id", "draft_id" 2.174 ) VALUES ( 2.175 "event_v", NEW."author_id", 2.176 - "area_row"."unit_id", "issue_row"."area_id", 2.177 + "area_row"."unit_id", "issue_row"."area_id", "issue_row"."policy_id", 2.178 "initiative_row"."issue_id", "issue_row"."state", 2.179 NEW."initiative_id", NEW."id" 2.180 ); 2.181 @@ -1001,11 +1029,12 @@ 2.182 WHERE "initiative_id" = NEW."id" FOR SHARE; 2.183 INSERT INTO "event" ( 2.184 "event", "member_id", 2.185 - "unit_id", "area_id", "issue_id", "state", 2.186 + "unit_id", "area_id", "policy_id", "issue_id", "state", 2.187 "initiative_id", "draft_id" 2.188 ) VALUES ( 2.189 'initiative_revoked', NEW."revoked_by_member_id", 2.190 "area_row"."unit_id", "issue_row"."area_id", 2.191 + "issue_row"."policy_id", 2.192 NEW."issue_id", "issue_row"."state", 2.193 NEW."id", "draft_id_v" 2.194 ); 2.195 @@ -1031,11 +1060,11 @@ 2.196 WHERE "id" = "issue_row"."area_id" FOR SHARE; 2.197 INSERT INTO "event" ( 2.198 "event", "member_id", 2.199 - "unit_id", "area_id", "issue_id", "state", 2.200 + "unit_id", "area_id", "policy_id", "issue_id", "state", 2.201 "initiative_id", "suggestion_id" 2.202 ) VALUES ( 2.203 'suggestion_created', NEW."author_id", 2.204 - "area_row"."unit_id", "issue_row"."area_id", 2.205 + "area_row"."unit_id", "issue_row"."area_id", "issue_row"."policy_id", 2.206 "initiative_row"."issue_id", "issue_row"."state", 2.207 NEW."initiative_id", NEW."id" 2.208 ); 2.209 @@ -1061,11 +1090,12 @@ 2.210 WHERE "id" = "issue_row"."area_id" FOR SHARE; 2.211 INSERT INTO "event" ( 2.212 "event", 2.213 - "unit_id", "area_id", "issue_id", "state", 2.214 + "unit_id", "area_id", "policy_id", "issue_id", "state", 2.215 "initiative_id", "suggestion_id" 2.216 ) VALUES ( 2.217 'suggestion_removed', 2.218 "area_row"."unit_id", "issue_row"."area_id", 2.219 + "issue_row"."policy_id", 2.220 "initiative_row"."issue_id", "issue_row"."state", 2.221 OLD."initiative_id", OLD."id" 2.222 ); 2.223 @@ -1229,11 +1259,12 @@ 2.224 IF "issue_row"."id" NOTNULL THEN 2.225 INSERT INTO "event" ( 2.226 "event", "member_id", 2.227 - "unit_id", "area_id", "issue_id", "state", 2.228 + "unit_id", "area_id", "policy_id", "issue_id", "state", 2.229 "boolean_value" 2.230 ) VALUES ( 2.231 'interest', OLD."member_id", 2.232 "area_row"."unit_id", "issue_row"."area_id", 2.233 + "issue_row"."policy_id", 2.234 OLD."issue_id", "issue_row"."state", 2.235 FALSE 2.236 ); 2.237 @@ -1246,11 +1277,12 @@ 2.238 WHERE "id" = "issue_row"."area_id" FOR SHARE; 2.239 INSERT INTO "event" ( 2.240 "event", "member_id", 2.241 - "unit_id", "area_id", "issue_id", "state", 2.242 + "unit_id", "area_id", "policy_id", "issue_id", "state", 2.243 "boolean_value" 2.244 ) VALUES ( 2.245 'interest', NEW."member_id", 2.246 "area_row"."unit_id", "issue_row"."area_id", 2.247 + "issue_row"."policy_id", 2.248 NEW."issue_id", "issue_row"."state", 2.249 TRUE 2.250 ); 2.251 @@ -1295,11 +1327,12 @@ 2.252 WHERE "id" = "issue_row"."area_id" FOR SHARE; 2.253 INSERT INTO "event" ( 2.254 "event", "member_id", 2.255 - "unit_id", "area_id", "issue_id", "state", 2.256 + "unit_id", "area_id", "policy_id", "issue_id", "state", 2.257 "initiative_id", "boolean_value" 2.258 ) VALUES ( 2.259 'initiator', OLD."member_id", 2.260 "area_row"."unit_id", "issue_row"."area_id", 2.261 + "issue_row"."policy_id", 2.262 "issue_row"."id", "issue_row"."state", 2.263 OLD."initiative_id", FALSE 2.264 ); 2.265 @@ -1316,11 +1349,12 @@ 2.266 WHERE "id" = "issue_row"."area_id" FOR SHARE; 2.267 INSERT INTO "event" ( 2.268 "event", "member_id", 2.269 - "unit_id", "area_id", "issue_id", "state", 2.270 + "unit_id", "area_id", "policy_id", "issue_id", "state", 2.271 "initiative_id", "boolean_value" 2.272 ) VALUES ( 2.273 'initiator', NEW."member_id", 2.274 "area_row"."unit_id", "issue_row"."area_id", 2.275 + "issue_row"."policy_id", 2.276 "issue_row"."id", "issue_row"."state", 2.277 NEW."initiative_id", TRUE 2.278 ); 2.279 @@ -1357,11 +1391,12 @@ 2.280 WHERE "id" = "issue_row"."area_id" FOR SHARE; 2.281 INSERT INTO "event" ( 2.282 "event", "member_id", 2.283 - "unit_id", "area_id", "issue_id", "state", 2.284 + "unit_id", "area_id", "policy_id", "issue_id", "state", 2.285 "initiative_id", "draft_id" 2.286 ) VALUES ( 2.287 'support_updated', NEW."member_id", 2.288 "area_row"."unit_id", "issue_row"."area_id", 2.289 + "issue_row"."policy_id", 2.290 "issue_row"."id", "issue_row"."state", 2.291 NEW."initiative_id", NEW."draft_id" 2.292 ); 2.293 @@ -1380,11 +1415,12 @@ 2.294 WHERE "id" = "issue_row"."area_id" FOR SHARE; 2.295 INSERT INTO "event" ( 2.296 "event", "member_id", 2.297 - "unit_id", "area_id", "issue_id", "state", 2.298 + "unit_id", "area_id", "policy_id", "issue_id", "state", 2.299 "initiative_id", "boolean_value" 2.300 ) VALUES ( 2.301 'support', OLD."member_id", 2.302 "area_row"."unit_id", "issue_row"."area_id", 2.303 + "issue_row"."policy_id", 2.304 "issue_row"."id", "issue_row"."state", 2.305 OLD."initiative_id", FALSE 2.306 ); 2.307 @@ -1397,11 +1433,12 @@ 2.308 WHERE "id" = "issue_row"."area_id" FOR SHARE; 2.309 INSERT INTO "event" ( 2.310 "event", "member_id", 2.311 - "unit_id", "area_id", "issue_id", "state", 2.312 + "unit_id", "area_id", "policy_id", "issue_id", "state", 2.313 "initiative_id", "draft_id", "boolean_value" 2.314 ) VALUES ( 2.315 'support', NEW."member_id", 2.316 "area_row"."unit_id", "issue_row"."area_id", 2.317 + "issue_row"."policy_id", 2.318 "issue_row"."id", "issue_row"."state", 2.319 NEW."initiative_id", NEW."draft_id", TRUE 2.320 ); 2.321 @@ -1454,12 +1491,13 @@ 2.322 WHERE "id" = "issue_row"."area_id" FOR SHARE; 2.323 INSERT INTO "event" ( 2.324 "event", "member_id", 2.325 - "unit_id", "area_id", "issue_id", "state", 2.326 + "unit_id", "area_id", "policy_id", "issue_id", "state", 2.327 "initiative_id", "suggestion_id", 2.328 "boolean_value", "numeric_value" 2.329 ) VALUES ( 2.330 'suggestion_rated', OLD."member_id", 2.331 "area_row"."unit_id", "issue_row"."area_id", 2.332 + "issue_row"."policy_id", 2.333 "initiative_row"."issue_id", "issue_row"."state", 2.334 OLD."initiative_id", OLD."suggestion_id", 2.335 NULL, 0 2.336 @@ -1475,12 +1513,13 @@ 2.337 WHERE "id" = "issue_row"."area_id" FOR SHARE; 2.338 INSERT INTO "event" ( 2.339 "event", "member_id", 2.340 - "unit_id", "area_id", "issue_id", "state", 2.341 + "unit_id", "area_id", "policy_id", "issue_id", "state", 2.342 "initiative_id", "suggestion_id", 2.343 "boolean_value", "numeric_value" 2.344 ) VALUES ( 2.345 'suggestion_rated', NEW."member_id", 2.346 "area_row"."unit_id", "issue_row"."area_id", 2.347 + "issue_row"."policy_id", 2.348 "initiative_row"."issue_id", "issue_row"."state", 2.349 NEW."initiative_id", NEW."suggestion_id", 2.350 NEW."fulfilled", NEW."degree"