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)
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  

Impressum / About Us