liquid_feedback_core

view core-update.beta16-beta17.sql @ 16:359d2b311f2c

find changesets by author, revision, files, or words in the commit message
Version beta17

New view timeline

More indicies (for timeline)

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

Impressum / About Us