
jbe@25: BEGIN;
jbe@16: 
jbe@16: CREATE OR REPLACE VIEW "liquid_feedback_version" AS
jbe@16:   SELECT * FROM (VALUES ('beta17', NULL, NULL, NULL))
jbe@16:   AS "subquery"("string", "major", "minor", "revision");
jbe@16: 
jbe@16: COMMENT ON TABLE "setting" IS 'Place to store a frontend specific member setting as a string';
jbe@16: 
jbe@16: CREATE TABLE "setting_map" (
jbe@16:         PRIMARY KEY ("member_id", "key", "subkey"),
jbe@16:         "member_id"             INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@16:         "key"                   TEXT            NOT NULL,
jbe@16:         "subkey"                TEXT            NOT NULL,
jbe@16:         "value"                 TEXT            NOT NULL );
jbe@16: CREATE INDEX "setting_map_key_idx" ON "setting_map" ("key");
jbe@16: 
jbe@16: COMMENT ON TABLE "setting_map" IS 'Place to store a frontend specific member setting as a map of key value pairs';
jbe@16: 
jbe@16: COMMENT ON COLUMN "setting_map"."key"    IS 'Name of the setting, preceded by a frontend specific prefix';
jbe@16: COMMENT ON COLUMN "setting_map"."subkey" IS 'Key of a map entry';
jbe@16: COMMENT ON COLUMN "setting_map"."value"  IS 'Value of a map entry';
jbe@16: 
jbe@16: CREATE INDEX "issue_created_idx" ON "issue" ("created");
jbe@16: CREATE INDEX "issue_accepted_idx" ON "issue" ("accepted");
jbe@16: CREATE INDEX "issue_half_frozen_idx" ON "issue" ("half_frozen");
jbe@16: CREATE INDEX "issue_fully_frozen_idx" ON "issue" ("fully_frozen");
jbe@16: CREATE INDEX "issue_closed_idx" ON "issue" ("closed");
jbe@16: CREATE INDEX "issue_closed_idx_canceled" ON "issue" ("closed") WHERE "fully_frozen" ISNULL;
jbe@16: CREATE INDEX "initiative_created_idx" ON "initiative" ("created");
jbe@16: CREATE INDEX "initiative_revoked_idx" ON "initiative" ("revoked");
jbe@16: CREATE INDEX "draft_created_idx" ON "draft" ("created");
jbe@16: CREATE INDEX "suggestion_created_idx" ON "suggestion" ("created");
jbe@16: 
jbe@16: CREATE TYPE "timeline_event" AS ENUM (
jbe@16:   'issue_created',
jbe@16:   'issue_canceled',
jbe@16:   'issue_accepted',
jbe@16:   'issue_half_frozen',
jbe@16:   'issue_finished_without_voting',
jbe@16:   'issue_voting_started',
jbe@16:   'issue_finished_after_voting',
jbe@16:   'initiative_created',
jbe@16:   'initiative_revoked',
jbe@16:   'draft_created',
jbe@16:   'suggestion_created');
jbe@16: 
jbe@16: COMMENT ON TYPE "timeline_event" IS 'Types of event in timeline tables';
jbe@16: 
jbe@16: CREATE VIEW "timeline_issue" AS
jbe@16:     SELECT
jbe@16:       "created" AS "occurrence",
jbe@16:       'issue_created'::"timeline_event" AS "event",
jbe@16:       "id" AS "issue_id"
jbe@16:     FROM "issue"
jbe@16:   UNION ALL
jbe@16:     SELECT
jbe@16:       "closed" AS "occurrence",
jbe@16:       'issue_canceled'::"timeline_event" AS "event",
jbe@16:       "id" AS "issue_id"
jbe@16:     FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" ISNULL
jbe@16:   UNION ALL
jbe@16:     SELECT
jbe@16:       "accepted" AS "occurrence",
jbe@16:       'issue_accepted'::"timeline_event" AS "event",
jbe@16:       "id" AS "issue_id"
jbe@16:     FROM "issue" WHERE "accepted" NOTNULL
jbe@16:   UNION ALL
jbe@16:     SELECT
jbe@16:       "half_frozen" AS "occurrence",
jbe@16:       'issue_half_frozen'::"timeline_event" AS "event",
jbe@16:       "id" AS "issue_id"
jbe@16:     FROM "issue" WHERE "half_frozen" NOTNULL
jbe@16:   UNION ALL
jbe@16:     SELECT
jbe@16:       "fully_frozen" AS "occurrence",
jbe@16:       'issue_voting_started'::"timeline_event" AS "event",
jbe@16:       "id" AS "issue_id"
jbe@16:     FROM "issue"
jbe@16:     WHERE "fully_frozen" NOTNULL AND "closed" != "fully_frozen"
jbe@16:   UNION ALL
jbe@16:     SELECT
jbe@16:       "closed" AS "occurrence",
jbe@16:       CASE WHEN "fully_frozen" = "closed" THEN
jbe@16:         'issue_finished_without_voting'::"timeline_event"
jbe@16:       ELSE
jbe@16:         'issue_finished_after_voting'::"timeline_event"
jbe@16:       END AS "event",
jbe@16:       "id" AS "issue_id"
jbe@16:     FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" NOTNULL;
jbe@16: 
jbe@16: COMMENT ON VIEW "timeline_issue" IS 'Helper view for "timeline" view';
jbe@16: 
jbe@16: CREATE VIEW "timeline_initiative" AS
jbe@16:     SELECT
jbe@16:       "created" AS "occurrence",
jbe@16:       'initiative_created'::"timeline_event" AS "event",
jbe@16:       "id" AS "initiative_id"
jbe@16:     FROM "initiative"
jbe@16:   UNION ALL
jbe@16:     SELECT
jbe@16:       "revoked" AS "occurrence",
jbe@16:       'initiative_revoked'::"timeline_event" AS "event",
jbe@16:       "id" AS "initiative_id"
jbe@16:     FROM "initiative" WHERE "revoked" NOTNULL;
jbe@16: 
jbe@16: COMMENT ON VIEW "timeline_initiative" IS 'Helper view for "timeline" view';
jbe@16: 
jbe@16: CREATE VIEW "timeline_draft" AS
jbe@16:   SELECT
jbe@16:     "created" AS "occurrence",
jbe@16:     'draft_created'::"timeline_event" AS "event",
jbe@16:     "id" AS "draft_id"
jbe@16:   FROM "draft";
jbe@16: 
jbe@16: COMMENT ON VIEW "timeline_draft" IS 'Helper view for "timeline" view';
jbe@16: 
jbe@16: CREATE VIEW "timeline_suggestion" AS
jbe@16:   SELECT
jbe@16:     "created" AS "occurrence",
jbe@16:     'suggestion_created'::"timeline_event" AS "event",
jbe@16:     "id" AS "suggestion_id"
jbe@16:   FROM "suggestion";
jbe@16: 
jbe@16: COMMENT ON VIEW "timeline_suggestion" IS 'Helper view for "timeline" view';
jbe@16: 
jbe@16: CREATE VIEW "timeline" AS
jbe@16:     SELECT
jbe@16:       "occurrence",
jbe@16:       "event",
jbe@16:       "issue_id",
jbe@16:       NULL AS "initiative_id",
jbe@16:       NULL::INT8 AS "draft_id",  -- TODO: Why do we need a type-cast here? Is this due to 32 bit architecture?
jbe@16:       NULL::INT8 AS "suggestion_id"
jbe@16:     FROM "timeline_issue"
jbe@16:   UNION ALL
jbe@16:     SELECT
jbe@16:       "occurrence",
jbe@16:       "event",
jbe@16:       NULL AS "issue_id",
jbe@16:       "initiative_id",
jbe@16:       NULL AS "draft_id",
jbe@16:       NULL AS "suggestion_id"
jbe@16:     FROM "timeline_initiative"
jbe@16:   UNION ALL
jbe@16:     SELECT
jbe@16:       "occurrence",
jbe@16:       "event",
jbe@16:       NULL AS "issue_id",
jbe@16:       NULL AS "initiative_id",
jbe@16:       "draft_id",
jbe@16:       NULL AS "suggestion_id"
jbe@16:     FROM "timeline_draft"
jbe@16:   UNION ALL
jbe@16:     SELECT
jbe@16:       "occurrence",
jbe@16:       "event",
jbe@16:       NULL AS "issue_id",
jbe@16:       NULL AS "initiative_id",
jbe@16:       NULL AS "draft_id",
jbe@16:       "suggestion_id"
jbe@16:     FROM "timeline_suggestion";
jbe@16: 
jbe@16: COMMENT ON VIEW "timeline" IS 'Aggregation of different events in the system';
jbe@16: 
jbe@25: COMMIT;




