liquid_feedback_core
changeset 16:359d2b311f2c beta17
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 | 6f6ec6b5eee9 |
children | 1cba764373d6 |
files | core-update.beta16-beta17.sql core.sql |
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 +
2.1 --- a/core.sql Wed Jan 13 12:00:00 2010 +0100 2.2 +++ b/core.sql Fri Jan 22 12:00:00 2010 +0100 2.3 @@ -6,7 +6,7 @@ 2.4 BEGIN; 2.5 2.6 CREATE VIEW "liquid_feedback_version" AS 2.7 - SELECT * FROM (VALUES ('beta16', NULL, NULL, NULL)) 2.8 + SELECT * FROM (VALUES ('beta17', NULL, NULL, NULL)) 2.9 AS "subquery"("string", "major", "minor", "revision"); 2.10 2.11 2.12 @@ -150,11 +150,26 @@ 2.13 "value" TEXT NOT NULL ); 2.14 CREATE INDEX "setting_key_idx" ON "setting" ("key"); 2.15 2.16 -COMMENT ON TABLE "setting" IS 'Place to store frontend specific member settings'; 2.17 +COMMENT ON TABLE "setting" IS 'Place to store a frontend specific member setting as a string'; 2.18 2.19 COMMENT ON COLUMN "setting"."key" IS 'Name of the setting, preceded by a frontend specific prefix'; 2.20 2.21 2.22 +CREATE TABLE "setting_map" ( 2.23 + PRIMARY KEY ("member_id", "key", "subkey"), 2.24 + "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 2.25 + "key" TEXT NOT NULL, 2.26 + "subkey" TEXT NOT NULL, 2.27 + "value" TEXT NOT NULL ); 2.28 +CREATE INDEX "setting_map_key_idx" ON "setting_map" ("key"); 2.29 + 2.30 +COMMENT ON TABLE "setting_map" IS 'Place to store a frontend specific member setting as a map of key value pairs'; 2.31 + 2.32 +COMMENT ON COLUMN "setting_map"."key" IS 'Name of the setting, preceded by a frontend specific prefix'; 2.33 +COMMENT ON COLUMN "setting_map"."subkey" IS 'Key of a map entry'; 2.34 +COMMENT ON COLUMN "setting_map"."value" IS 'Value of a map entry'; 2.35 + 2.36 + 2.37 CREATE TYPE "member_image_type" AS ENUM ('photo', 'avatar'); 2.38 2.39 COMMENT ON TYPE "member_image_type" IS 'Types of images for a member'; 2.40 @@ -329,7 +344,13 @@ 2.41 CHECK ("snapshot" NOTNULL = "latest_snapshot_event" NOTNULL) ); 2.42 CREATE INDEX "issue_area_id_idx" ON "issue" ("area_id"); 2.43 CREATE INDEX "issue_policy_id_idx" ON "issue" ("policy_id"); 2.44 +CREATE INDEX "issue_created_idx" ON "issue" ("created"); 2.45 +CREATE INDEX "issue_accepted_idx" ON "issue" ("accepted"); 2.46 +CREATE INDEX "issue_half_frozen_idx" ON "issue" ("half_frozen"); 2.47 +CREATE INDEX "issue_fully_frozen_idx" ON "issue" ("fully_frozen"); 2.48 +CREATE INDEX "issue_closed_idx" ON "issue" ("closed"); 2.49 CREATE INDEX "issue_created_idx_open" ON "issue" ("created") WHERE "closed" ISNULL; 2.50 +CREATE INDEX "issue_closed_idx_canceled" ON "issue" ("closed") WHERE "fully_frozen" ISNULL; 2.51 2.52 COMMENT ON TABLE "issue" IS 'Groups of initiatives'; 2.53 2.54 @@ -375,6 +396,8 @@ 2.55 CHECK ("positive_votes" NOTNULL = "negative_votes" NOTNULL AND "positive_votes" NOTNULL = "agreed" NOTNULL), 2.56 CONSTRAINT "non_agreed_initiatives_cant_get_a_rank" 2.57 CHECK (("agreed" NOTNULL AND "agreed" = TRUE) OR "rank" ISNULL) ); 2.58 +CREATE INDEX "initiative_created_idx" ON "initiative" ("created"); 2.59 +CREATE INDEX "initiative_revoked_idx" ON "initiative" ("revoked"); 2.60 CREATE INDEX "initiative_text_search_data_idx" ON "initiative" USING gin ("text_search_data"); 2.61 CREATE TRIGGER "update_text_search_data" 2.62 BEFORE INSERT OR UPDATE ON "initiative" 2.63 @@ -406,6 +429,7 @@ 2.64 "formatting_engine" TEXT, 2.65 "content" TEXT NOT NULL, 2.66 "text_search_data" TSVECTOR ); 2.67 +CREATE INDEX "draft_created_idx" ON "draft" ("created"); 2.68 CREATE INDEX "draft_author_id_created_idx" ON "draft" ("author_id", "created"); 2.69 CREATE INDEX "draft_text_search_data_idx" ON "draft" USING gin ("text_search_data"); 2.70 CREATE TRIGGER "update_text_search_data" 2.71 @@ -436,6 +460,7 @@ 2.72 "plus1_fulfilled_count" INT4, 2.73 "plus2_unfulfilled_count" INT4, 2.74 "plus2_fulfilled_count" INT4 ); 2.75 +CREATE INDEX "suggestion_created_idx" ON "suggestion" ("created"); 2.76 CREATE INDEX "suggestion_author_id_created_idx" ON "suggestion" ("author_id", "created"); 2.77 CREATE INDEX "suggestion_text_search_data_idx" ON "suggestion" USING gin ("text_search_data"); 2.78 CREATE TRIGGER "update_text_search_data" 2.79 @@ -1345,6 +1370,143 @@ 2.80 COMMENT ON VIEW "member_contingent_left" IS 'Amount of text entries or initiatives which can be posted now instantly by a member. This view should be used by a frontend to determine, if the contingent for posting is exhausted.'; 2.81 2.82 2.83 +CREATE TYPE "timeline_event" AS ENUM ( 2.84 + 'issue_created', 2.85 + 'issue_canceled', 2.86 + 'issue_accepted', 2.87 + 'issue_half_frozen', 2.88 + 'issue_finished_without_voting', 2.89 + 'issue_voting_started', 2.90 + 'issue_finished_after_voting', 2.91 + 'initiative_created', 2.92 + 'initiative_revoked', 2.93 + 'draft_created', 2.94 + 'suggestion_created'); 2.95 + 2.96 +COMMENT ON TYPE "timeline_event" IS 'Types of event in timeline tables'; 2.97 + 2.98 + 2.99 +CREATE VIEW "timeline_issue" AS 2.100 + SELECT 2.101 + "created" AS "occurrence", 2.102 + 'issue_created'::"timeline_event" AS "event", 2.103 + "id" AS "issue_id" 2.104 + FROM "issue" 2.105 + UNION ALL 2.106 + SELECT 2.107 + "closed" AS "occurrence", 2.108 + 'issue_canceled'::"timeline_event" AS "event", 2.109 + "id" AS "issue_id" 2.110 + FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" ISNULL 2.111 + UNION ALL 2.112 + SELECT 2.113 + "accepted" AS "occurrence", 2.114 + 'issue_accepted'::"timeline_event" AS "event", 2.115 + "id" AS "issue_id" 2.116 + FROM "issue" WHERE "accepted" NOTNULL 2.117 + UNION ALL 2.118 + SELECT 2.119 + "half_frozen" AS "occurrence", 2.120 + 'issue_half_frozen'::"timeline_event" AS "event", 2.121 + "id" AS "issue_id" 2.122 + FROM "issue" WHERE "half_frozen" NOTNULL 2.123 + UNION ALL 2.124 + SELECT 2.125 + "fully_frozen" AS "occurrence", 2.126 + 'issue_voting_started'::"timeline_event" AS "event", 2.127 + "id" AS "issue_id" 2.128 + FROM "issue" 2.129 + WHERE "fully_frozen" NOTNULL AND "closed" != "fully_frozen" 2.130 + UNION ALL 2.131 + SELECT 2.132 + "closed" AS "occurrence", 2.133 + CASE WHEN "fully_frozen" = "closed" THEN 2.134 + 'issue_finished_without_voting'::"timeline_event" 2.135 + ELSE 2.136 + 'issue_finished_after_voting'::"timeline_event" 2.137 + END AS "event", 2.138 + "id" AS "issue_id" 2.139 + FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" NOTNULL; 2.140 + 2.141 +COMMENT ON VIEW "timeline_issue" IS 'Helper view for "timeline" view'; 2.142 + 2.143 + 2.144 +CREATE VIEW "timeline_initiative" AS 2.145 + SELECT 2.146 + "created" AS "occurrence", 2.147 + 'initiative_created'::"timeline_event" AS "event", 2.148 + "id" AS "initiative_id" 2.149 + FROM "initiative" 2.150 + UNION ALL 2.151 + SELECT 2.152 + "revoked" AS "occurrence", 2.153 + 'initiative_revoked'::"timeline_event" AS "event", 2.154 + "id" AS "initiative_id" 2.155 + FROM "initiative" WHERE "revoked" NOTNULL; 2.156 + 2.157 +COMMENT ON VIEW "timeline_initiative" IS 'Helper view for "timeline" view'; 2.158 + 2.159 + 2.160 +CREATE VIEW "timeline_draft" AS 2.161 + SELECT 2.162 + "created" AS "occurrence", 2.163 + 'draft_created'::"timeline_event" AS "event", 2.164 + "id" AS "draft_id" 2.165 + FROM "draft"; 2.166 + 2.167 +COMMENT ON VIEW "timeline_draft" IS 'Helper view for "timeline" view'; 2.168 + 2.169 + 2.170 +CREATE VIEW "timeline_suggestion" AS 2.171 + SELECT 2.172 + "created" AS "occurrence", 2.173 + 'suggestion_created'::"timeline_event" AS "event", 2.174 + "id" AS "suggestion_id" 2.175 + FROM "suggestion"; 2.176 + 2.177 +COMMENT ON VIEW "timeline_suggestion" IS 'Helper view for "timeline" view'; 2.178 + 2.179 + 2.180 +CREATE VIEW "timeline" AS 2.181 + SELECT 2.182 + "occurrence", 2.183 + "event", 2.184 + "issue_id", 2.185 + NULL AS "initiative_id", 2.186 + NULL::INT8 AS "draft_id", -- TODO: Why do we need a type-cast here? Is this due to 32 bit architecture? 2.187 + NULL::INT8 AS "suggestion_id" 2.188 + FROM "timeline_issue" 2.189 + UNION ALL 2.190 + SELECT 2.191 + "occurrence", 2.192 + "event", 2.193 + NULL AS "issue_id", 2.194 + "initiative_id", 2.195 + NULL AS "draft_id", 2.196 + NULL AS "suggestion_id" 2.197 + FROM "timeline_initiative" 2.198 + UNION ALL 2.199 + SELECT 2.200 + "occurrence", 2.201 + "event", 2.202 + NULL AS "issue_id", 2.203 + NULL AS "initiative_id", 2.204 + "draft_id", 2.205 + NULL AS "suggestion_id" 2.206 + FROM "timeline_draft" 2.207 + UNION ALL 2.208 + SELECT 2.209 + "occurrence", 2.210 + "event", 2.211 + NULL AS "issue_id", 2.212 + NULL AS "initiative_id", 2.213 + NULL AS "draft_id", 2.214 + "suggestion_id" 2.215 + FROM "timeline_suggestion"; 2.216 + 2.217 +COMMENT ON VIEW "timeline" IS 'Aggregation of different events in the system'; 2.218 + 2.219 + 2.220 2.221 -------------------------------------------------- 2.222 -- Set returning function for delegation chains --