Commits

jbe  committed 359d2b3

Version beta17

New view timeline

More indicies (for timeline)

New table setting_map to store more complicated frontend user settings

  • Participants
  • Parent commits 6f6ec6b
  • Tags beta17

Comments (0)

Files changed (2)

File core-update.beta16-beta17.sql

+
+CREATE OR REPLACE VIEW "liquid_feedback_version" AS
+  SELECT * FROM (VALUES ('beta17', NULL, NULL, NULL))
+  AS "subquery"("string", "major", "minor", "revision");
+
+COMMENT ON TABLE "setting" IS 'Place to store a frontend specific member setting as a string';
+
+CREATE TABLE "setting_map" (
+        PRIMARY KEY ("member_id", "key", "subkey"),
+        "member_id"             INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
+        "key"                   TEXT            NOT NULL,
+        "subkey"                TEXT            NOT NULL,
+        "value"                 TEXT            NOT NULL );
+CREATE INDEX "setting_map_key_idx" ON "setting_map" ("key");
+
+COMMENT ON TABLE "setting_map" IS 'Place to store a frontend specific member setting as a map of key value pairs';
+
+COMMENT ON COLUMN "setting_map"."key"    IS 'Name of the setting, preceded by a frontend specific prefix';
+COMMENT ON COLUMN "setting_map"."subkey" IS 'Key of a map entry';
+COMMENT ON COLUMN "setting_map"."value"  IS 'Value of a map entry';
+
+CREATE INDEX "issue_created_idx" ON "issue" ("created");
+CREATE INDEX "issue_accepted_idx" ON "issue" ("accepted");
+CREATE INDEX "issue_half_frozen_idx" ON "issue" ("half_frozen");
+CREATE INDEX "issue_fully_frozen_idx" ON "issue" ("fully_frozen");
+CREATE INDEX "issue_closed_idx" ON "issue" ("closed");
+CREATE INDEX "issue_closed_idx_canceled" ON "issue" ("closed") WHERE "fully_frozen" ISNULL;
+CREATE INDEX "initiative_created_idx" ON "initiative" ("created");
+CREATE INDEX "initiative_revoked_idx" ON "initiative" ("revoked");
+CREATE INDEX "draft_created_idx" ON "draft" ("created");
+CREATE INDEX "suggestion_created_idx" ON "suggestion" ("created");
+
+CREATE TYPE "timeline_event" AS ENUM (
+  'issue_created',
+  'issue_canceled',
+  'issue_accepted',
+  'issue_half_frozen',
+  'issue_finished_without_voting',
+  'issue_voting_started',
+  'issue_finished_after_voting',
+  'initiative_created',
+  'initiative_revoked',
+  'draft_created',
+  'suggestion_created');
+
+COMMENT ON TYPE "timeline_event" IS 'Types of event in timeline tables';
+
+CREATE VIEW "timeline_issue" AS
+    SELECT
+      "created" AS "occurrence",
+      'issue_created'::"timeline_event" AS "event",
+      "id" AS "issue_id"
+    FROM "issue"
+  UNION ALL
+    SELECT
+      "closed" AS "occurrence",
+      'issue_canceled'::"timeline_event" AS "event",
+      "id" AS "issue_id"
+    FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" ISNULL
+  UNION ALL
+    SELECT
+      "accepted" AS "occurrence",
+      'issue_accepted'::"timeline_event" AS "event",
+      "id" AS "issue_id"
+    FROM "issue" WHERE "accepted" NOTNULL
+  UNION ALL
+    SELECT
+      "half_frozen" AS "occurrence",
+      'issue_half_frozen'::"timeline_event" AS "event",
+      "id" AS "issue_id"
+    FROM "issue" WHERE "half_frozen" NOTNULL
+  UNION ALL
+    SELECT
+      "fully_frozen" AS "occurrence",
+      'issue_voting_started'::"timeline_event" AS "event",
+      "id" AS "issue_id"
+    FROM "issue"
+    WHERE "fully_frozen" NOTNULL AND "closed" != "fully_frozen"
+  UNION ALL
+    SELECT
+      "closed" AS "occurrence",
+      CASE WHEN "fully_frozen" = "closed" THEN
+        'issue_finished_without_voting'::"timeline_event"
+      ELSE
+        'issue_finished_after_voting'::"timeline_event"
+      END AS "event",
+      "id" AS "issue_id"
+    FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" NOTNULL;
+
+COMMENT ON VIEW "timeline_issue" IS 'Helper view for "timeline" view';
+
+CREATE VIEW "timeline_initiative" AS
+    SELECT
+      "created" AS "occurrence",
+      'initiative_created'::"timeline_event" AS "event",
+      "id" AS "initiative_id"
+    FROM "initiative"
+  UNION ALL
+    SELECT
+      "revoked" AS "occurrence",
+      'initiative_revoked'::"timeline_event" AS "event",
+      "id" AS "initiative_id"
+    FROM "initiative" WHERE "revoked" NOTNULL;
+
+COMMENT ON VIEW "timeline_initiative" IS 'Helper view for "timeline" view';
+
+CREATE VIEW "timeline_draft" AS
+  SELECT
+    "created" AS "occurrence",
+    'draft_created'::"timeline_event" AS "event",
+    "id" AS "draft_id"
+  FROM "draft";
+
+COMMENT ON VIEW "timeline_draft" IS 'Helper view for "timeline" view';
+
+CREATE VIEW "timeline_suggestion" AS
+  SELECT
+    "created" AS "occurrence",
+    'suggestion_created'::"timeline_event" AS "event",
+    "id" AS "suggestion_id"
+  FROM "suggestion";
+
+COMMENT ON VIEW "timeline_suggestion" IS 'Helper view for "timeline" view';
+
+CREATE VIEW "timeline" AS
+    SELECT
+      "occurrence",
+      "event",
+      "issue_id",
+      NULL AS "initiative_id",
+      NULL::INT8 AS "draft_id",  -- TODO: Why do we need a type-cast here? Is this due to 32 bit architecture?
+      NULL::INT8 AS "suggestion_id"
+    FROM "timeline_issue"
+  UNION ALL
+    SELECT
+      "occurrence",
+      "event",
+      NULL AS "issue_id",
+      "initiative_id",
+      NULL AS "draft_id",
+      NULL AS "suggestion_id"
+    FROM "timeline_initiative"
+  UNION ALL
+    SELECT
+      "occurrence",
+      "event",
+      NULL AS "issue_id",
+      NULL AS "initiative_id",
+      "draft_id",
+      NULL AS "suggestion_id"
+    FROM "timeline_draft"
+  UNION ALL
+    SELECT
+      "occurrence",
+      "event",
+      NULL AS "issue_id",
+      NULL AS "initiative_id",
+      NULL AS "draft_id",
+      "suggestion_id"
+    FROM "timeline_suggestion";
+
+COMMENT ON VIEW "timeline" IS 'Aggregation of different events in the system';
+
 BEGIN;
 
 CREATE VIEW "liquid_feedback_version" AS
-  SELECT * FROM (VALUES ('beta16', NULL, NULL, NULL))
+  SELECT * FROM (VALUES ('beta17', NULL, NULL, NULL))
   AS "subquery"("string", "major", "minor", "revision");
 
 
         "value"                 TEXT            NOT NULL );
 CREATE INDEX "setting_key_idx" ON "setting" ("key");
 
-COMMENT ON TABLE "setting" IS 'Place to store frontend specific member settings';
+COMMENT ON TABLE "setting" IS 'Place to store a frontend specific member setting as a string';
 
 COMMENT ON COLUMN "setting"."key" IS 'Name of the setting, preceded by a frontend specific prefix';
 
 
+CREATE TABLE "setting_map" (
+        PRIMARY KEY ("member_id", "key", "subkey"),
+        "member_id"             INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
+        "key"                   TEXT            NOT NULL,
+        "subkey"                TEXT            NOT NULL,
+        "value"                 TEXT            NOT NULL );
+CREATE INDEX "setting_map_key_idx" ON "setting_map" ("key");
+
+COMMENT ON TABLE "setting_map" IS 'Place to store a frontend specific member setting as a map of key value pairs';
+
+COMMENT ON COLUMN "setting_map"."key"    IS 'Name of the setting, preceded by a frontend specific prefix';
+COMMENT ON COLUMN "setting_map"."subkey" IS 'Key of a map entry';
+COMMENT ON COLUMN "setting_map"."value"  IS 'Value of a map entry';
+
+
 CREATE TYPE "member_image_type" AS ENUM ('photo', 'avatar');
 
 COMMENT ON TYPE "member_image_type" IS 'Types of images for a member';
           CHECK ("snapshot" NOTNULL = "latest_snapshot_event" NOTNULL) );
 CREATE INDEX "issue_area_id_idx" ON "issue" ("area_id");
 CREATE INDEX "issue_policy_id_idx" ON "issue" ("policy_id");
+CREATE INDEX "issue_created_idx" ON "issue" ("created");
+CREATE INDEX "issue_accepted_idx" ON "issue" ("accepted");
+CREATE INDEX "issue_half_frozen_idx" ON "issue" ("half_frozen");
+CREATE INDEX "issue_fully_frozen_idx" ON "issue" ("fully_frozen");
+CREATE INDEX "issue_closed_idx" ON "issue" ("closed");
 CREATE INDEX "issue_created_idx_open" ON "issue" ("created") WHERE "closed" ISNULL;
+CREATE INDEX "issue_closed_idx_canceled" ON "issue" ("closed") WHERE "fully_frozen" ISNULL;
 
 COMMENT ON TABLE "issue" IS 'Groups of initiatives';
 
           CHECK ("positive_votes" NOTNULL = "negative_votes" NOTNULL AND "positive_votes" NOTNULL = "agreed" NOTNULL),
         CONSTRAINT "non_agreed_initiatives_cant_get_a_rank"
           CHECK (("agreed" NOTNULL AND "agreed" = TRUE) OR "rank" ISNULL) );
+CREATE INDEX "initiative_created_idx" ON "initiative" ("created");
+CREATE INDEX "initiative_revoked_idx" ON "initiative" ("revoked");
 CREATE INDEX "initiative_text_search_data_idx" ON "initiative" USING gin ("text_search_data");
 CREATE TRIGGER "update_text_search_data"
   BEFORE INSERT OR UPDATE ON "initiative"
         "formatting_engine"     TEXT,
         "content"               TEXT            NOT NULL,
         "text_search_data"      TSVECTOR );
+CREATE INDEX "draft_created_idx" ON "draft" ("created");
 CREATE INDEX "draft_author_id_created_idx" ON "draft" ("author_id", "created");
 CREATE INDEX "draft_text_search_data_idx" ON "draft" USING gin ("text_search_data");
 CREATE TRIGGER "update_text_search_data"
         "plus1_fulfilled_count"    INT4,
         "plus2_unfulfilled_count"  INT4,
         "plus2_fulfilled_count"    INT4 );
+CREATE INDEX "suggestion_created_idx" ON "suggestion" ("created");
 CREATE INDEX "suggestion_author_id_created_idx" ON "suggestion" ("author_id", "created");
 CREATE INDEX "suggestion_text_search_data_idx" ON "suggestion" USING gin ("text_search_data");
 CREATE TRIGGER "update_text_search_data"
 COMMENT ON VIEW "member_contingent_left" IS 'Amount of text entries or initiatives which can be posted now instantly by a member. This view should be used by a frontend to determine, if the contingent for posting is exhausted.';
 
 
+CREATE TYPE "timeline_event" AS ENUM (
+  'issue_created',
+  'issue_canceled',
+  'issue_accepted',
+  'issue_half_frozen',
+  'issue_finished_without_voting',
+  'issue_voting_started',
+  'issue_finished_after_voting',
+  'initiative_created',
+  'initiative_revoked',
+  'draft_created',
+  'suggestion_created');
+
+COMMENT ON TYPE "timeline_event" IS 'Types of event in timeline tables';
+
+
+CREATE VIEW "timeline_issue" AS
+    SELECT
+      "created" AS "occurrence",
+      'issue_created'::"timeline_event" AS "event",
+      "id" AS "issue_id"
+    FROM "issue"
+  UNION ALL
+    SELECT
+      "closed" AS "occurrence",
+      'issue_canceled'::"timeline_event" AS "event",
+      "id" AS "issue_id"
+    FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" ISNULL
+  UNION ALL
+    SELECT
+      "accepted" AS "occurrence",
+      'issue_accepted'::"timeline_event" AS "event",
+      "id" AS "issue_id"
+    FROM "issue" WHERE "accepted" NOTNULL
+  UNION ALL
+    SELECT
+      "half_frozen" AS "occurrence",
+      'issue_half_frozen'::"timeline_event" AS "event",
+      "id" AS "issue_id"
+    FROM "issue" WHERE "half_frozen" NOTNULL
+  UNION ALL
+    SELECT
+      "fully_frozen" AS "occurrence",
+      'issue_voting_started'::"timeline_event" AS "event",
+      "id" AS "issue_id"
+    FROM "issue"
+    WHERE "fully_frozen" NOTNULL AND "closed" != "fully_frozen"
+  UNION ALL
+    SELECT
+      "closed" AS "occurrence",
+      CASE WHEN "fully_frozen" = "closed" THEN
+        'issue_finished_without_voting'::"timeline_event"
+      ELSE
+        'issue_finished_after_voting'::"timeline_event"
+      END AS "event",
+      "id" AS "issue_id"
+    FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" NOTNULL;
+
+COMMENT ON VIEW "timeline_issue" IS 'Helper view for "timeline" view';
+
+
+CREATE VIEW "timeline_initiative" AS
+    SELECT
+      "created" AS "occurrence",
+      'initiative_created'::"timeline_event" AS "event",
+      "id" AS "initiative_id"
+    FROM "initiative"
+  UNION ALL
+    SELECT
+      "revoked" AS "occurrence",
+      'initiative_revoked'::"timeline_event" AS "event",
+      "id" AS "initiative_id"
+    FROM "initiative" WHERE "revoked" NOTNULL;
+
+COMMENT ON VIEW "timeline_initiative" IS 'Helper view for "timeline" view';
+
+
+CREATE VIEW "timeline_draft" AS
+  SELECT
+    "created" AS "occurrence",
+    'draft_created'::"timeline_event" AS "event",
+    "id" AS "draft_id"
+  FROM "draft";
+
+COMMENT ON VIEW "timeline_draft" IS 'Helper view for "timeline" view';
+
+
+CREATE VIEW "timeline_suggestion" AS
+  SELECT
+    "created" AS "occurrence",
+    'suggestion_created'::"timeline_event" AS "event",
+    "id" AS "suggestion_id"
+  FROM "suggestion";
+
+COMMENT ON VIEW "timeline_suggestion" IS 'Helper view for "timeline" view';
+
+
+CREATE VIEW "timeline" AS
+    SELECT
+      "occurrence",
+      "event",
+      "issue_id",
+      NULL AS "initiative_id",
+      NULL::INT8 AS "draft_id",  -- TODO: Why do we need a type-cast here? Is this due to 32 bit architecture?
+      NULL::INT8 AS "suggestion_id"
+    FROM "timeline_issue"
+  UNION ALL
+    SELECT
+      "occurrence",
+      "event",
+      NULL AS "issue_id",
+      "initiative_id",
+      NULL AS "draft_id",
+      NULL AS "suggestion_id"
+    FROM "timeline_initiative"
+  UNION ALL
+    SELECT
+      "occurrence",
+      "event",
+      NULL AS "issue_id",
+      NULL AS "initiative_id",
+      "draft_id",
+      NULL AS "suggestion_id"
+    FROM "timeline_draft"
+  UNION ALL
+    SELECT
+      "occurrence",
+      "event",
+      NULL AS "issue_id",
+      NULL AS "initiative_id",
+      NULL AS "draft_id",
+      "suggestion_id"
+    FROM "timeline_suggestion";
+
+COMMENT ON VIEW "timeline" IS 'Aggregation of different events in the system';
+
+
 
 --------------------------------------------------
 -- Set returning function for delegation chains --