# HG changeset patch # User jbe # Date 1264158000 -3600 # Node ID 359d2b311f2ce96901448b06fb4c5a314f9004d8 # Parent 6f6ec6b5eee95f555501931f723899899f0be481 Version beta17 New view timeline More indicies (for timeline) New table setting_map to store more complicated frontend user settings diff -r 6f6ec6b5eee9 -r 359d2b311f2c core-update.beta16-beta17.sql --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/core-update.beta16-beta17.sql Fri Jan 22 12:00:00 2010 +0100 @@ -0,0 +1,163 @@ + +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'; + diff -r 6f6ec6b5eee9 -r 359d2b311f2c core.sql --- a/core.sql Wed Jan 13 12:00:00 2010 +0100 +++ b/core.sql Fri Jan 22 12:00:00 2010 +0100 @@ -6,7 +6,7 @@ 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"); @@ -150,11 +150,26 @@ "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'; @@ -329,7 +344,13 @@ 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'; @@ -375,6 +396,8 @@ 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" @@ -406,6 +429,7 @@ "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" @@ -436,6 +460,7 @@ "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" @@ -1345,6 +1370,143 @@ 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 --