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