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
|