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",