liquid_feedback_core
diff core.sql @ 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 |
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"