liquid_feedback_core
diff core-update.beta16-beta17.sql @ 16:359d2b311f2c
Version beta17
New view timeline
More indicies (for timeline)
New table setting_map to store more complicated frontend user settings
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 diff
1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000 1.2 +++ b/core-update.beta16-beta17.sql Fri Jan 22 12:00:00 2010 +0100 1.3 @@ -0,0 +1,163 @@ 1.4 + 1.5 +CREATE OR REPLACE VIEW "liquid_feedback_version" AS 1.6 + SELECT * FROM (VALUES ('beta17', NULL, NULL, NULL)) 1.7 + AS "subquery"("string", "major", "minor", "revision"); 1.8 + 1.9 +COMMENT ON TABLE "setting" IS 'Place to store a frontend specific member setting as a string'; 1.10 + 1.11 +CREATE TABLE "setting_map" ( 1.12 + PRIMARY KEY ("member_id", "key", "subkey"), 1.13 + "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.14 + "key" TEXT NOT NULL, 1.15 + "subkey" TEXT NOT NULL, 1.16 + "value" TEXT NOT NULL ); 1.17 +CREATE INDEX "setting_map_key_idx" ON "setting_map" ("key"); 1.18 + 1.19 +COMMENT ON TABLE "setting_map" IS 'Place to store a frontend specific member setting as a map of key value pairs'; 1.20 + 1.21 +COMMENT ON COLUMN "setting_map"."key" IS 'Name of the setting, preceded by a frontend specific prefix'; 1.22 +COMMENT ON COLUMN "setting_map"."subkey" IS 'Key of a map entry'; 1.23 +COMMENT ON COLUMN "setting_map"."value" IS 'Value of a map entry'; 1.24 + 1.25 +CREATE INDEX "issue_created_idx" ON "issue" ("created"); 1.26 +CREATE INDEX "issue_accepted_idx" ON "issue" ("accepted"); 1.27 +CREATE INDEX "issue_half_frozen_idx" ON "issue" ("half_frozen"); 1.28 +CREATE INDEX "issue_fully_frozen_idx" ON "issue" ("fully_frozen"); 1.29 +CREATE INDEX "issue_closed_idx" ON "issue" ("closed"); 1.30 +CREATE INDEX "issue_closed_idx_canceled" ON "issue" ("closed") WHERE "fully_frozen" ISNULL; 1.31 +CREATE INDEX "initiative_created_idx" ON "initiative" ("created"); 1.32 +CREATE INDEX "initiative_revoked_idx" ON "initiative" ("revoked"); 1.33 +CREATE INDEX "draft_created_idx" ON "draft" ("created"); 1.34 +CREATE INDEX "suggestion_created_idx" ON "suggestion" ("created"); 1.35 + 1.36 +CREATE TYPE "timeline_event" AS ENUM ( 1.37 + 'issue_created', 1.38 + 'issue_canceled', 1.39 + 'issue_accepted', 1.40 + 'issue_half_frozen', 1.41 + 'issue_finished_without_voting', 1.42 + 'issue_voting_started', 1.43 + 'issue_finished_after_voting', 1.44 + 'initiative_created', 1.45 + 'initiative_revoked', 1.46 + 'draft_created', 1.47 + 'suggestion_created'); 1.48 + 1.49 +COMMENT ON TYPE "timeline_event" IS 'Types of event in timeline tables'; 1.50 + 1.51 +CREATE VIEW "timeline_issue" AS 1.52 + SELECT 1.53 + "created" AS "occurrence", 1.54 + 'issue_created'::"timeline_event" AS "event", 1.55 + "id" AS "issue_id" 1.56 + FROM "issue" 1.57 + UNION ALL 1.58 + SELECT 1.59 + "closed" AS "occurrence", 1.60 + 'issue_canceled'::"timeline_event" AS "event", 1.61 + "id" AS "issue_id" 1.62 + FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" ISNULL 1.63 + UNION ALL 1.64 + SELECT 1.65 + "accepted" AS "occurrence", 1.66 + 'issue_accepted'::"timeline_event" AS "event", 1.67 + "id" AS "issue_id" 1.68 + FROM "issue" WHERE "accepted" NOTNULL 1.69 + UNION ALL 1.70 + SELECT 1.71 + "half_frozen" AS "occurrence", 1.72 + 'issue_half_frozen'::"timeline_event" AS "event", 1.73 + "id" AS "issue_id" 1.74 + FROM "issue" WHERE "half_frozen" NOTNULL 1.75 + UNION ALL 1.76 + SELECT 1.77 + "fully_frozen" AS "occurrence", 1.78 + 'issue_voting_started'::"timeline_event" AS "event", 1.79 + "id" AS "issue_id" 1.80 + FROM "issue" 1.81 + WHERE "fully_frozen" NOTNULL AND "closed" != "fully_frozen" 1.82 + UNION ALL 1.83 + SELECT 1.84 + "closed" AS "occurrence", 1.85 + CASE WHEN "fully_frozen" = "closed" THEN 1.86 + 'issue_finished_without_voting'::"timeline_event" 1.87 + ELSE 1.88 + 'issue_finished_after_voting'::"timeline_event" 1.89 + END AS "event", 1.90 + "id" AS "issue_id" 1.91 + FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" NOTNULL; 1.92 + 1.93 +COMMENT ON VIEW "timeline_issue" IS 'Helper view for "timeline" view'; 1.94 + 1.95 +CREATE VIEW "timeline_initiative" AS 1.96 + SELECT 1.97 + "created" AS "occurrence", 1.98 + 'initiative_created'::"timeline_event" AS "event", 1.99 + "id" AS "initiative_id" 1.100 + FROM "initiative" 1.101 + UNION ALL 1.102 + SELECT 1.103 + "revoked" AS "occurrence", 1.104 + 'initiative_revoked'::"timeline_event" AS "event", 1.105 + "id" AS "initiative_id" 1.106 + FROM "initiative" WHERE "revoked" NOTNULL; 1.107 + 1.108 +COMMENT ON VIEW "timeline_initiative" IS 'Helper view for "timeline" view'; 1.109 + 1.110 +CREATE VIEW "timeline_draft" AS 1.111 + SELECT 1.112 + "created" AS "occurrence", 1.113 + 'draft_created'::"timeline_event" AS "event", 1.114 + "id" AS "draft_id" 1.115 + FROM "draft"; 1.116 + 1.117 +COMMENT ON VIEW "timeline_draft" IS 'Helper view for "timeline" view'; 1.118 + 1.119 +CREATE VIEW "timeline_suggestion" AS 1.120 + SELECT 1.121 + "created" AS "occurrence", 1.122 + 'suggestion_created'::"timeline_event" AS "event", 1.123 + "id" AS "suggestion_id" 1.124 + FROM "suggestion"; 1.125 + 1.126 +COMMENT ON VIEW "timeline_suggestion" IS 'Helper view for "timeline" view'; 1.127 + 1.128 +CREATE VIEW "timeline" AS 1.129 + SELECT 1.130 + "occurrence", 1.131 + "event", 1.132 + "issue_id", 1.133 + NULL AS "initiative_id", 1.134 + NULL::INT8 AS "draft_id", -- TODO: Why do we need a type-cast here? Is this due to 32 bit architecture? 1.135 + NULL::INT8 AS "suggestion_id" 1.136 + FROM "timeline_issue" 1.137 + UNION ALL 1.138 + SELECT 1.139 + "occurrence", 1.140 + "event", 1.141 + NULL AS "issue_id", 1.142 + "initiative_id", 1.143 + NULL AS "draft_id", 1.144 + NULL AS "suggestion_id" 1.145 + FROM "timeline_initiative" 1.146 + UNION ALL 1.147 + SELECT 1.148 + "occurrence", 1.149 + "event", 1.150 + NULL AS "issue_id", 1.151 + NULL AS "initiative_id", 1.152 + "draft_id", 1.153 + NULL AS "suggestion_id" 1.154 + FROM "timeline_draft" 1.155 + UNION ALL 1.156 + SELECT 1.157 + "occurrence", 1.158 + "event", 1.159 + NULL AS "issue_id", 1.160 + NULL AS "initiative_id", 1.161 + NULL AS "draft_id", 1.162 + "suggestion_id" 1.163 + FROM "timeline_suggestion"; 1.164 + 1.165 +COMMENT ON VIEW "timeline" IS 'Aggregation of different events in the system'; 1.166 +