liquid_feedback_core
changeset 588:6f427a8f8061
Added "posting" table and follower concept for members (column "following" in table "contact")
author | jbe |
---|---|
date | Wed Dec 05 00:35:30 2018 +0100 (2018-12-05) |
parents | 31d7cb4991ef |
children | aa23fa17604d |
files | core.sql |
line diff
1.1 --- a/core.sql Mon Dec 03 21:11:20 2018 +0100 1.2 +++ b/core.sql Wed Dec 05 00:35:30 2018 +0100 1.3 @@ -3,7 +3,9 @@ 1.4 1.5 BEGIN; 1.6 1.7 -CREATE EXTENSION IF NOT EXISTS latlon; -- load pgLatLon extenstion 1.8 +CREATE EXTENSION IF NOT EXISTS btree_gist; 1.9 +CREATE EXTENSION IF NOT EXISTS latlon; 1.10 +CREATE EXTENSION IF NOT EXISTS conflux; 1.11 1.12 CREATE VIEW "liquid_feedback_version" AS 1.13 SELECT * FROM (VALUES ('4.1.0', 4, 1, 0)) 1.14 @@ -370,6 +372,7 @@ 1.15 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.16 "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.17 "public" BOOLEAN NOT NULL DEFAULT FALSE, 1.18 + "following" BOOLEAN NOT NULL DEFAULT TRUE, 1.19 CONSTRAINT "cant_save_yourself_as_contact" 1.20 CHECK ("member_id" != "other_member_id") ); 1.21 CREATE INDEX "contact_other_member_id_idx" ON "contact" ("other_member_id"); 1.22 @@ -379,6 +382,7 @@ 1.23 COMMENT ON COLUMN "contact"."member_id" IS 'Member having the contact list'; 1.24 COMMENT ON COLUMN "contact"."other_member_id" IS 'Member referenced in the contact list'; 1.25 COMMENT ON COLUMN "contact"."public" IS 'TRUE = display contact publically'; 1.26 +COMMENT ON COLUMN "contact"."following" IS 'TRUE = actions of contact are shown in personal timeline'; 1.27 1.28 1.29 CREATE TABLE "ignored_member" ( 1.30 @@ -1409,6 +1413,46 @@ 1.31 COMMENT ON COLUMN "vote"."first_preference" IS 'Value is automatically set after voting is finished. For positive grades, this value is set to true for the highest (i.e. best) grade.'; 1.32 1.33 1.34 +CREATE TABLE "posting" ( 1.35 + "id" SERIAL8 PRIMARY KEY, 1.36 + "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE, 1.37 + "created" TIMESTAMPTZ NOT NULL DEFAULT now(), 1.38 + "message" TEXT NOT NULL, 1.39 + "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.40 + "area_id" INT4, 1.41 + FOREIGN KEY ("unit_id", "area_id") REFERENCES "area" ("unit_id", "id") ON DELETE CASCADE ON UPDATE CASCADE, 1.42 + "policy_id" INT4 REFERENCES "policy" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.43 + "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.44 + FOREIGN KEY ("area_id", "issue_id") REFERENCES "issue" ("area_id", "id") ON DELETE CASCADE ON UPDATE CASCADE, 1.45 + FOREIGN KEY ("policy_id", "issue_id") REFERENCES "issue" ("policy_id", "id") ON DELETE CASCADE ON UPDATE CASCADE, 1.46 + "initiative_id" INT4, 1.47 + "suggestion_id" INT8, 1.48 + -- NOTE: no referential integrity for suggestions because those are 1.49 + -- actually deleted 1.50 + -- FOREIGN KEY ("initiative_id", "suggestion_id") 1.51 + -- REFERENCES "suggestion" ("initiative_id", "id") 1.52 + -- ON DELETE CASCADE ON UPDATE CASCADE, 1.53 + CONSTRAINT "area_requires_unit" CHECK ( 1.54 + "area_id" ISNULL OR "unit_id" NOTNULL ), 1.55 + CONSTRAINT "policy_set_when_issue_set" CHECK ( 1.56 + ("policy_id" NOTNULL) = ("issue_id" NOTNULL) ), 1.57 + CONSTRAINT "issue_requires_area" CHECK ( 1.58 + "issue_id" ISNULL OR "area_id" NOTNULL ), 1.59 + CONSTRAINT "initiative_requires_issue" CHECK ( 1.60 + "initiative_id" ISNULL OR "issue_id" NOTNULL ), 1.61 + CONSTRAINT "suggestion_requires_initiative" CHECK ( 1.62 + "suggestion_id" ISNULL OR "initiative_id" NOTNULL ) ); 1.63 +CREATE INDEX "posting_global_idx" ON "posting" USING gist ((pstamp("author_id", "id"))); 1.64 +CREATE INDEX "posting_unit_idx" ON "posting" USING gist ("unit_id", (pstamp("author_id", "id"))) WHERE "unit_id" NOTNULL; 1.65 +CREATE INDEX "posting_area_idx" ON "posting" USING gist ("area_id", (pstamp("author_id", "id"))) WHERE "area_id" NOTNULL; 1.66 +CREATE INDEX "posting_policy_idx" ON "posting" USING gist ("policy_id", (pstamp("author_id", "id"))) WHERE "policy_id" NOTNULL; 1.67 +CREATE INDEX "posting_issue_idx" ON "posting" USING gist ("issue_id", (pstamp("author_id", "id"))) WHERE "issue_id" NOTNULL; 1.68 +CREATE INDEX "posting_initiative_idx" ON "posting" USING gist ("initiative_id", (pstamp("author_id", "id"))) WHERE "initiative_id" NOTNULL; 1.69 +CREATE INDEX "posting_suggestion_idx" ON "posting" USING gist ("suggestion_id", (pstamp("author_id", "id"))) WHERE "suggestion_id" NOTNULL; 1.70 + 1.71 +COMMENT ON TABLE "posting" IS 'Text postings of members; a text posting may optionally be associated to a unit, area, policy, issue, initiative, or suggestion'; 1.72 + 1.73 + 1.74 CREATE TYPE "event_type" AS ENUM ( 1.75 'unit_created', 1.76 'unit_updated', 1.77 @@ -1435,7 +1479,8 @@ 1.78 'support_updated', 1.79 'suggestion_rated', 1.80 'delegation', 1.81 - 'contact' ); 1.82 + 'contact', 1.83 + 'posting_created' ); 1.84 1.85 COMMENT ON TYPE "event_type" IS 'Type used for column "event" of table "event"'; 1.86 1.87 @@ -1444,6 +1489,7 @@ 1.88 "id" SERIAL8 PRIMARY KEY, 1.89 "occurrence" TIMESTAMPTZ NOT NULL DEFAULT now(), 1.90 "event" "event_type" NOT NULL, 1.91 + "posting_id" INT8 REFERENCES "posting" ("id") ON DELETE RESTRICT ON UPDATE CASCADE, 1.92 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE, 1.93 "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE, 1.94 "scope" "delegation_scope", 1.95 @@ -1475,6 +1521,7 @@ 1.96 -- ON DELETE CASCADE ON UPDATE CASCADE, 1.97 CONSTRAINT "constr_for_issue_state_changed" CHECK ( 1.98 "event" != 'issue_state_changed' OR ( 1.99 + "posting_id" ISNULL AND 1.100 "member_id" ISNULL AND 1.101 "other_member_id" ISNULL AND 1.102 "scope" ISNULL AND 1.103 @@ -1497,6 +1544,7 @@ 1.104 'initiative_revoked', 1.105 'new_draft_created' 1.106 ) OR ( 1.107 + "posting_id" ISNULL AND 1.108 "member_id" NOTNULL AND 1.109 "other_member_id" ISNULL AND 1.110 "scope" ISNULL AND 1.111 @@ -1514,6 +1562,7 @@ 1.112 "old_text_value" ISNULL )), 1.113 CONSTRAINT "constr_for_suggestion_creation" CHECK ( 1.114 "event" != 'suggestion_created' OR ( 1.115 + "posting_id" ISNULL AND 1.116 "member_id" NOTNULL AND 1.117 "other_member_id" ISNULL AND 1.118 "scope" ISNULL AND 1.119 @@ -1531,6 +1580,7 @@ 1.120 "old_text_value" ISNULL )), 1.121 CONSTRAINT "constr_for_suggestion_removal" CHECK ( 1.122 "event" != 'suggestion_deleted' OR ( 1.123 + "posting_id" ISNULL AND 1.124 "member_id" ISNULL AND 1.125 "other_member_id" ISNULL AND 1.126 "scope" ISNULL AND 1.127 @@ -1553,6 +1603,7 @@ 1.128 'member_profile_updated', 1.129 'member_image_updated' 1.130 ) OR ( 1.131 + "posting_id" ISNULL AND 1.132 "member_id" NOTNULL AND 1.133 "other_member_id" ISNULL AND 1.134 "scope" ISNULL AND 1.135 @@ -1570,6 +1621,7 @@ 1.136 "old_text_value" ISNULL )), 1.137 CONSTRAINT "constr_for_member_active" CHECK ( 1.138 "event" != 'member_active' OR ( 1.139 + "posting_id" ISNULL AND 1.140 "member_id" NOTNULL AND 1.141 "other_member_id" ISNULL AND 1.142 "scope" ISNULL AND 1.143 @@ -1587,6 +1639,7 @@ 1.144 "old_text_value" ISNULL )), 1.145 CONSTRAINT "constr_for_member_name_updated" CHECK ( 1.146 "event" != 'member_name_updated' OR ( 1.147 + "posting_id" ISNULL AND 1.148 "member_id" NOTNULL AND 1.149 "other_member_id" ISNULL AND 1.150 "scope" ISNULL AND 1.151 @@ -1604,6 +1657,7 @@ 1.152 "old_text_value" NOTNULL )), 1.153 CONSTRAINT "constr_for_interest" CHECK ( 1.154 "event" != 'interest' OR ( 1.155 + "posting_id" ISNULL AND 1.156 "member_id" NOTNULL AND 1.157 "other_member_id" ISNULL AND 1.158 "scope" ISNULL AND 1.159 @@ -1621,6 +1675,7 @@ 1.160 "old_text_value" ISNULL )), 1.161 CONSTRAINT "constr_for_initiator" CHECK ( 1.162 "event" != 'initiator' OR ( 1.163 + "posting_id" ISNULL AND 1.164 "member_id" NOTNULL AND 1.165 "other_member_id" ISNULL AND 1.166 "scope" ISNULL AND 1.167 @@ -1638,6 +1693,7 @@ 1.168 "old_text_value" ISNULL )), 1.169 CONSTRAINT "constr_for_support" CHECK ( 1.170 "event" != 'support' OR ( 1.171 + "posting_id" ISNULL AND 1.172 "member_id" NOTNULL AND 1.173 "other_member_id" ISNULL AND 1.174 "scope" ISNULL AND 1.175 @@ -1655,6 +1711,7 @@ 1.176 "old_text_value" ISNULL )), 1.177 CONSTRAINT "constr_for_support_updated" CHECK ( 1.178 "event" != 'support_updated' OR ( 1.179 + "posting_id" ISNULL AND 1.180 "member_id" NOTNULL AND 1.181 "other_member_id" ISNULL AND 1.182 "scope" ISNULL AND 1.183 @@ -1672,6 +1729,7 @@ 1.184 "old_text_value" ISNULL )), 1.185 CONSTRAINT "constr_for_suggestion_rated" CHECK ( 1.186 "event" != 'suggestion_rated' OR ( 1.187 + "posting_id" ISNULL AND 1.188 "member_id" NOTNULL AND 1.189 "other_member_id" ISNULL AND 1.190 "scope" ISNULL AND 1.191 @@ -1690,6 +1748,7 @@ 1.192 "old_text_value" ISNULL )), 1.193 CONSTRAINT "constr_for_delegation" CHECK ( 1.194 "event" != 'delegation' OR ( 1.195 + "posting_id" ISNULL AND 1.196 "member_id" NOTNULL AND 1.197 (("other_member_id" ISNULL) OR ("boolean_value" = TRUE)) AND 1.198 "scope" NOTNULL AND 1.199 @@ -1707,6 +1766,7 @@ 1.200 "old_text_value" ISNULL )), 1.201 CONSTRAINT "constr_for_contact" CHECK ( 1.202 "event" != 'contact' OR ( 1.203 + "posting_id" ISNULL AND 1.204 "member_id" NOTNULL AND 1.205 "other_member_id" NOTNULL AND 1.206 "scope" ISNULL AND 1.207 @@ -1721,8 +1781,34 @@ 1.208 "boolean_value" NOTNULL AND 1.209 "numeric_value" ISNULL AND 1.210 "text_value" ISNULL AND 1.211 + "old_text_value" ISNULL )), 1.212 + CONSTRAINT "constr_for_posting_created" CHECK ( 1.213 + "event" != 'posting_created' OR ( 1.214 + "posting_id" NOTNULL AND 1.215 + "member_id" NOTNULL AND 1.216 + "other_member_id" ISNULL AND 1.217 + "scope" ISNULL AND 1.218 + "state" ISNULL AND 1.219 + ("area_id" ISNULL OR "unit_id" NOTNULL) AND 1.220 + ("policy_id" NOTNULL) = ("issue_id" NOTNULL) AND 1.221 + ("issue_id" ISNULL OR "area_id" NOTNULL) AND 1.222 + ("state" NOTNULL) = ("issue_id" NOTNULL) AND 1.223 + ("initiative_id" ISNULL OR "issue_id" NOTNULL) AND 1.224 + "draft_id" ISNULL AND 1.225 + ("suggestion_id" ISNULL OR "initiative_id" NOTNULL) AND 1.226 + "boolean_value" ISNULL AND 1.227 + "numeric_value" ISNULL AND 1.228 + "text_value" ISNULL AND 1.229 "old_text_value" ISNULL )) ); 1.230 CREATE INDEX "event_occurrence_idx" ON "event" ("occurrence"); 1.231 +CREATE INDEX "event_tl_global_idx" ON "event" USING gist ((pstamp("member_id", "id"))); 1.232 +CREATE INDEX "event_tl_unit_idx" ON "event" USING gist ("unit_id", (pstamp("member_id", "id"))) WHERE "unit_id" NOTNULL; 1.233 +CREATE INDEX "event_tl_area_idx" ON "event" USING gist ("area_id", (pstamp("member_id", "id"))) WHERE "area_id" NOTNULL; 1.234 +CREATE INDEX "event_tl_policy_idx" ON "event" USING gist ("policy_id", (pstamp("member_id", "id"))) WHERE "policy_id" NOTNULL; 1.235 +CREATE INDEX "event_tl_issue_idx" ON "event" USING gist ("issue_id", (pstamp("member_id", "id"))) WHERE "issue_id" NOTNULL; 1.236 +CREATE INDEX "event_tl_initiative_idx" ON "event" USING gist ("initiative_id", (pstamp("member_id", "id"))) WHERE "initiative_id" NOTNULL; 1.237 +CREATE INDEX "event_tl_suggestion_idx" ON "event" USING gist ("suggestion_id", (pstamp("member_id", "id"))) WHERE "suggestion_id" NOTNULL; 1.238 + 1.239 1.240 COMMENT ON TABLE "event" IS 'Event table, automatically filled by triggers'; 1.241 1.242 @@ -2665,6 +2751,31 @@ 1.243 COMMENT ON TRIGGER "write_event_contact" ON "contact" IS 'Create entry in "event" table when adding or removing public contacts'; 1.244 1.245 1.246 +CREATE FUNCTION "write_event_posting_trigger"() 1.247 + RETURNS TRIGGER 1.248 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.249 + BEGIN 1.250 + INSERT INTO "event" ( 1.251 + "event", "posting_id", "member_id", 1.252 + "unit_id", "area_id", "policy_id", 1.253 + "issue_id", "initiative_id", "suggestion_id" 1.254 + ) VALUES ( 1.255 + 'posting_created', NEW."id", NEW."author_id", 1.256 + NEW."unit_id", NEW."area_id", NEW."policy_id", 1.257 + NEW."issue_id", NEW."initiative_id", NEW."suggestion_id" 1.258 + ); 1.259 + RETURN NULL; 1.260 + END; 1.261 + $$; 1.262 + 1.263 +CREATE TRIGGER "write_event_posting" 1.264 + AFTER INSERT ON "posting" FOR EACH ROW EXECUTE PROCEDURE 1.265 + "write_event_posting_trigger"(); 1.266 + 1.267 +COMMENT ON FUNCTION "write_event_posting_trigger"() IS 'Implementation of trigger "write_event_posting" on table "posting"'; 1.268 +COMMENT ON TRIGGER "write_event_posting" ON "posting" IS 'Create entry in "event" table when creating a new posting'; 1.269 + 1.270 + 1.271 CREATE FUNCTION "send_event_notify_trigger"() 1.272 RETURNS TRIGGER 1.273 LANGUAGE 'plpgsql' VOLATILE AS $$ 1.274 @@ -3340,6 +3451,18 @@ 1.275 COMMENT ON VIEW "member_to_notify" IS 'Filtered "member" table containing only members that are eligible to and wish to receive notifications; NOTE: "notify_email" may still be NULL and might need to be checked by frontend (this allows other means of messaging)'; 1.276 1.277 1.278 +CREATE VIEW "follower" AS 1.279 + SELECT 1.280 + "id" AS "follower_id", 1.281 + ( SELECT ARRAY["member"."id"] || array_agg("contact"."other_member_id") 1.282 + FROM "contact" 1.283 + WHERE "contact"."member_id" = "member"."id" AND "contact"."following" ) 1.284 + AS "following_ids" 1.285 + FROM "member"; 1.286 + 1.287 +COMMENT ON VIEW "follower" IS 'Provides the contacts of each member that are being followed (including the member itself) as an array of IDs'; 1.288 + 1.289 + 1.290 CREATE VIEW "area_quorum" AS 1.291 SELECT 1.292 "area"."id" AS "area_id",