liquid_feedback_core
annotate update/core-update.beta16-beta17.sql @ 52:3fbf9e18ee52
Bugfix: Corrected table for index "delegating_voter_member_id_idx"
Version change to 1.0.3
Version change to 1.0.3
author | jbe |
---|---|
date | Thu Jun 17 23:10:55 2010 +0200 (2010-06-17) |
parents | f0460e206bc6 |
children |
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; |