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