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: