liquid_feedback_core
view update/core-update.beta16-beta17.sql @ 78:4fc1779a9c98
add test policies
the test policies have values that move them quickly to the target area for frontend development
the test policies have values that move them quickly to the target area for frontend development
| author | Daniel Poelzleithner <poelzi@poelzi.org> |
|---|---|
| date | Mon Oct 04 14:24:29 2010 +0200 (2010-10-04) |
| parents | f0460e206bc6 |
| 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;