liquid_feedback_core

annotate core-update.beta16-beta17.sql @ 23:137c98fa0b4f

find changesets by author, revision, files, or words in the commit message
Added tables allowing frontends to store member-relation, area, issue and suggestion settings
author jbe
date Sat Feb 06 03:32:04 2010 +0100 (2010-02-06)
parents 359d2b311f2c
children
rev   line source
jbe@16 1
jbe@16 2 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
jbe@16 3 SELECT * FROM (VALUES ('beta17', NULL, NULL, NULL))
jbe@16 4 AS "subquery"("string", "major", "minor", "revision");
jbe@16 5
jbe@16 6 COMMENT ON TABLE "setting" IS 'Place to store a frontend specific member setting as a string';
jbe@16 7
jbe@16 8 CREATE TABLE "setting_map" (
jbe@16 9 PRIMARY KEY ("member_id", "key", "subkey"),
jbe@16 10 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@16 11 "key" TEXT NOT NULL,
jbe@16 12 "subkey" TEXT NOT NULL,
jbe@16 13 "value" TEXT NOT NULL );
jbe@16 14 CREATE INDEX "setting_map_key_idx" ON "setting_map" ("key");
jbe@16 15
jbe@16 16 COMMENT ON TABLE "setting_map" IS 'Place to store a frontend specific member setting as a map of key value pairs';
jbe@16 17
jbe@16 18 COMMENT ON COLUMN "setting_map"."key" IS 'Name of the setting, preceded by a frontend specific prefix';
jbe@16 19 COMMENT ON COLUMN "setting_map"."subkey" IS 'Key of a map entry';
jbe@16 20 COMMENT ON COLUMN "setting_map"."value" IS 'Value of a map entry';
jbe@16 21
jbe@16 22 CREATE INDEX "issue_created_idx" ON "issue" ("created");
jbe@16 23 CREATE INDEX "issue_accepted_idx" ON "issue" ("accepted");
jbe@16 24 CREATE INDEX "issue_half_frozen_idx" ON "issue" ("half_frozen");
jbe@16 25 CREATE INDEX "issue_fully_frozen_idx" ON "issue" ("fully_frozen");
jbe@16 26 CREATE INDEX "issue_closed_idx" ON "issue" ("closed");
jbe@16 27 CREATE INDEX "issue_closed_idx_canceled" ON "issue" ("closed") WHERE "fully_frozen" ISNULL;
jbe@16 28 CREATE INDEX "initiative_created_idx" ON "initiative" ("created");
jbe@16 29 CREATE INDEX "initiative_revoked_idx" ON "initiative" ("revoked");
jbe@16 30 CREATE INDEX "draft_created_idx" ON "draft" ("created");
jbe@16 31 CREATE INDEX "suggestion_created_idx" ON "suggestion" ("created");
jbe@16 32
jbe@16 33 CREATE TYPE "timeline_event" AS ENUM (
jbe@16 34 'issue_created',
jbe@16 35 'issue_canceled',
jbe@16 36 'issue_accepted',
jbe@16 37 'issue_half_frozen',
jbe@16 38 'issue_finished_without_voting',
jbe@16 39 'issue_voting_started',
jbe@16 40 'issue_finished_after_voting',
jbe@16 41 'initiative_created',
jbe@16 42 'initiative_revoked',
jbe@16 43 'draft_created',
jbe@16 44 'suggestion_created');
jbe@16 45
jbe@16 46 COMMENT ON TYPE "timeline_event" IS 'Types of event in timeline tables';
jbe@16 47
jbe@16 48 CREATE VIEW "timeline_issue" AS
jbe@16 49 SELECT
jbe@16 50 "created" AS "occurrence",
jbe@16 51 'issue_created'::"timeline_event" AS "event",
jbe@16 52 "id" AS "issue_id"
jbe@16 53 FROM "issue"
jbe@16 54 UNION ALL
jbe@16 55 SELECT
jbe@16 56 "closed" AS "occurrence",
jbe@16 57 'issue_canceled'::"timeline_event" AS "event",
jbe@16 58 "id" AS "issue_id"
jbe@16 59 FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" ISNULL
jbe@16 60 UNION ALL
jbe@16 61 SELECT
jbe@16 62 "accepted" AS "occurrence",
jbe@16 63 'issue_accepted'::"timeline_event" AS "event",
jbe@16 64 "id" AS "issue_id"
jbe@16 65 FROM "issue" WHERE "accepted" NOTNULL
jbe@16 66 UNION ALL
jbe@16 67 SELECT
jbe@16 68 "half_frozen" AS "occurrence",
jbe@16 69 'issue_half_frozen'::"timeline_event" AS "event",
jbe@16 70 "id" AS "issue_id"
jbe@16 71 FROM "issue" WHERE "half_frozen" NOTNULL
jbe@16 72 UNION ALL
jbe@16 73 SELECT
jbe@16 74 "fully_frozen" AS "occurrence",
jbe@16 75 'issue_voting_started'::"timeline_event" AS "event",
jbe@16 76 "id" AS "issue_id"
jbe@16 77 FROM "issue"
jbe@16 78 WHERE "fully_frozen" NOTNULL AND "closed" != "fully_frozen"
jbe@16 79 UNION ALL
jbe@16 80 SELECT
jbe@16 81 "closed" AS "occurrence",
jbe@16 82 CASE WHEN "fully_frozen" = "closed" THEN
jbe@16 83 'issue_finished_without_voting'::"timeline_event"
jbe@16 84 ELSE
jbe@16 85 'issue_finished_after_voting'::"timeline_event"
jbe@16 86 END AS "event",
jbe@16 87 "id" AS "issue_id"
jbe@16 88 FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" NOTNULL;
jbe@16 89
jbe@16 90 COMMENT ON VIEW "timeline_issue" IS 'Helper view for "timeline" view';
jbe@16 91
jbe@16 92 CREATE VIEW "timeline_initiative" AS
jbe@16 93 SELECT
jbe@16 94 "created" AS "occurrence",
jbe@16 95 'initiative_created'::"timeline_event" AS "event",
jbe@16 96 "id" AS "initiative_id"
jbe@16 97 FROM "initiative"
jbe@16 98 UNION ALL
jbe@16 99 SELECT
jbe@16 100 "revoked" AS "occurrence",
jbe@16 101 'initiative_revoked'::"timeline_event" AS "event",
jbe@16 102 "id" AS "initiative_id"
jbe@16 103 FROM "initiative" WHERE "revoked" NOTNULL;
jbe@16 104
jbe@16 105 COMMENT ON VIEW "timeline_initiative" IS 'Helper view for "timeline" view';
jbe@16 106
jbe@16 107 CREATE VIEW "timeline_draft" AS
jbe@16 108 SELECT
jbe@16 109 "created" AS "occurrence",
jbe@16 110 'draft_created'::"timeline_event" AS "event",
jbe@16 111 "id" AS "draft_id"
jbe@16 112 FROM "draft";
jbe@16 113
jbe@16 114 COMMENT ON VIEW "timeline_draft" IS 'Helper view for "timeline" view';
jbe@16 115
jbe@16 116 CREATE VIEW "timeline_suggestion" AS
jbe@16 117 SELECT
jbe@16 118 "created" AS "occurrence",
jbe@16 119 'suggestion_created'::"timeline_event" AS "event",
jbe@16 120 "id" AS "suggestion_id"
jbe@16 121 FROM "suggestion";
jbe@16 122
jbe@16 123 COMMENT ON VIEW "timeline_suggestion" IS 'Helper view for "timeline" view';
jbe@16 124
jbe@16 125 CREATE VIEW "timeline" AS
jbe@16 126 SELECT
jbe@16 127 "occurrence",
jbe@16 128 "event",
jbe@16 129 "issue_id",
jbe@16 130 NULL AS "initiative_id",
jbe@16 131 NULL::INT8 AS "draft_id", -- TODO: Why do we need a type-cast here? Is this due to 32 bit architecture?
jbe@16 132 NULL::INT8 AS "suggestion_id"
jbe@16 133 FROM "timeline_issue"
jbe@16 134 UNION ALL
jbe@16 135 SELECT
jbe@16 136 "occurrence",
jbe@16 137 "event",
jbe@16 138 NULL AS "issue_id",
jbe@16 139 "initiative_id",
jbe@16 140 NULL AS "draft_id",
jbe@16 141 NULL AS "suggestion_id"
jbe@16 142 FROM "timeline_initiative"
jbe@16 143 UNION ALL
jbe@16 144 SELECT
jbe@16 145 "occurrence",
jbe@16 146 "event",
jbe@16 147 NULL AS "issue_id",
jbe@16 148 NULL AS "initiative_id",
jbe@16 149 "draft_id",
jbe@16 150 NULL AS "suggestion_id"
jbe@16 151 FROM "timeline_draft"
jbe@16 152 UNION ALL
jbe@16 153 SELECT
jbe@16 154 "occurrence",
jbe@16 155 "event",
jbe@16 156 NULL AS "issue_id",
jbe@16 157 NULL AS "initiative_id",
jbe@16 158 NULL AS "draft_id",
jbe@16 159 "suggestion_id"
jbe@16 160 FROM "timeline_suggestion";
jbe@16 161
jbe@16 162 COMMENT ON VIEW "timeline" IS 'Aggregation of different events in the system';
jbe@16 163

Impressum / About Us