liquid_feedback_core
changeset 25:f0460e206bc6
Moved core-update files to update directory; Added BEGIN and COMMIT to core-update.beta16-beta17.sql
author | jbe |
---|---|
date | Sat Feb 06 03:37:55 2010 +0100 (2010-02-06) |
parents | 85ee75f90ecd |
children | 66971fefaba2 |
files | core-update.beta16-beta17.sql core-update.beta17-beta18.sql update/core-update.beta16-beta17.sql update/core-update.beta17-beta18.sql |
line diff
1.1 --- a/core-update.beta16-beta17.sql Sat Feb 06 03:34:09 2010 +0100 1.2 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000 1.3 @@ -1,163 +0,0 @@ 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-update.beta17-beta18.sql Sat Feb 06 03:34:09 2010 +0100 2.2 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000 2.3 @@ -1,50 +0,0 @@ 2.4 -BEGIN; 2.5 - 2.6 -CREATE OR REPLACE VIEW "liquid_feedback_version" AS 2.7 - SELECT * FROM (VALUES ('beta18', NULL, NULL, NULL)) 2.8 - AS "subquery"("string", "major", "minor", "revision"); 2.9 - 2.10 -CREATE OR REPLACE VIEW "timeline_issue" AS 2.11 - SELECT 2.12 - "created" AS "occurrence", 2.13 - 'issue_created'::"timeline_event" AS "event", 2.14 - "id" AS "issue_id" 2.15 - FROM "issue" 2.16 - UNION ALL 2.17 - SELECT 2.18 - "closed" AS "occurrence", 2.19 - 'issue_canceled'::"timeline_event" AS "event", 2.20 - "id" AS "issue_id" 2.21 - FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" ISNULL 2.22 - UNION ALL 2.23 - SELECT 2.24 - "accepted" AS "occurrence", 2.25 - 'issue_accepted'::"timeline_event" AS "event", 2.26 - "id" AS "issue_id" 2.27 - FROM "issue" WHERE "accepted" NOTNULL 2.28 - UNION ALL 2.29 - SELECT 2.30 - "half_frozen" AS "occurrence", 2.31 - 'issue_half_frozen'::"timeline_event" AS "event", 2.32 - "id" AS "issue_id" 2.33 - FROM "issue" WHERE "half_frozen" NOTNULL 2.34 - UNION ALL 2.35 - SELECT 2.36 - "fully_frozen" AS "occurrence", 2.37 - 'issue_voting_started'::"timeline_event" AS "event", 2.38 - "id" AS "issue_id" 2.39 - FROM "issue" 2.40 - WHERE "fully_frozen" NOTNULL 2.41 - AND ("closed" ISNULL OR "closed" != "fully_frozen") 2.42 - UNION ALL 2.43 - SELECT 2.44 - "closed" AS "occurrence", 2.45 - CASE WHEN "fully_frozen" = "closed" THEN 2.46 - 'issue_finished_without_voting'::"timeline_event" 2.47 - ELSE 2.48 - 'issue_finished_after_voting'::"timeline_event" 2.49 - END AS "event", 2.50 - "id" AS "issue_id" 2.51 - FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" NOTNULL; 2.52 - 2.53 -COMMIT;
3.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000 3.2 +++ b/update/core-update.beta16-beta17.sql Sat Feb 06 03:37:55 2010 +0100 3.3 @@ -0,0 +1,165 @@ 3.4 +BEGIN; 3.5 + 3.6 +CREATE OR REPLACE VIEW "liquid_feedback_version" AS 3.7 + SELECT * FROM (VALUES ('beta17', NULL, NULL, NULL)) 3.8 + AS "subquery"("string", "major", "minor", "revision"); 3.9 + 3.10 +COMMENT ON TABLE "setting" IS 'Place to store a frontend specific member setting as a string'; 3.11 + 3.12 +CREATE TABLE "setting_map" ( 3.13 + PRIMARY KEY ("member_id", "key", "subkey"), 3.14 + "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 3.15 + "key" TEXT NOT NULL, 3.16 + "subkey" TEXT NOT NULL, 3.17 + "value" TEXT NOT NULL ); 3.18 +CREATE INDEX "setting_map_key_idx" ON "setting_map" ("key"); 3.19 + 3.20 +COMMENT ON TABLE "setting_map" IS 'Place to store a frontend specific member setting as a map of key value pairs'; 3.21 + 3.22 +COMMENT ON COLUMN "setting_map"."key" IS 'Name of the setting, preceded by a frontend specific prefix'; 3.23 +COMMENT ON COLUMN "setting_map"."subkey" IS 'Key of a map entry'; 3.24 +COMMENT ON COLUMN "setting_map"."value" IS 'Value of a map entry'; 3.25 + 3.26 +CREATE INDEX "issue_created_idx" ON "issue" ("created"); 3.27 +CREATE INDEX "issue_accepted_idx" ON "issue" ("accepted"); 3.28 +CREATE INDEX "issue_half_frozen_idx" ON "issue" ("half_frozen"); 3.29 +CREATE INDEX "issue_fully_frozen_idx" ON "issue" ("fully_frozen"); 3.30 +CREATE INDEX "issue_closed_idx" ON "issue" ("closed"); 3.31 +CREATE INDEX "issue_closed_idx_canceled" ON "issue" ("closed") WHERE "fully_frozen" ISNULL; 3.32 +CREATE INDEX "initiative_created_idx" ON "initiative" ("created"); 3.33 +CREATE INDEX "initiative_revoked_idx" ON "initiative" ("revoked"); 3.34 +CREATE INDEX "draft_created_idx" ON "draft" ("created"); 3.35 +CREATE INDEX "suggestion_created_idx" ON "suggestion" ("created"); 3.36 + 3.37 +CREATE TYPE "timeline_event" AS ENUM ( 3.38 + 'issue_created', 3.39 + 'issue_canceled', 3.40 + 'issue_accepted', 3.41 + 'issue_half_frozen', 3.42 + 'issue_finished_without_voting', 3.43 + 'issue_voting_started', 3.44 + 'issue_finished_after_voting', 3.45 + 'initiative_created', 3.46 + 'initiative_revoked', 3.47 + 'draft_created', 3.48 + 'suggestion_created'); 3.49 + 3.50 +COMMENT ON TYPE "timeline_event" IS 'Types of event in timeline tables'; 3.51 + 3.52 +CREATE VIEW "timeline_issue" AS 3.53 + SELECT 3.54 + "created" AS "occurrence", 3.55 + 'issue_created'::"timeline_event" AS "event", 3.56 + "id" AS "issue_id" 3.57 + FROM "issue" 3.58 + UNION ALL 3.59 + SELECT 3.60 + "closed" AS "occurrence", 3.61 + 'issue_canceled'::"timeline_event" AS "event", 3.62 + "id" AS "issue_id" 3.63 + FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" ISNULL 3.64 + UNION ALL 3.65 + SELECT 3.66 + "accepted" AS "occurrence", 3.67 + 'issue_accepted'::"timeline_event" AS "event", 3.68 + "id" AS "issue_id" 3.69 + FROM "issue" WHERE "accepted" NOTNULL 3.70 + UNION ALL 3.71 + SELECT 3.72 + "half_frozen" AS "occurrence", 3.73 + 'issue_half_frozen'::"timeline_event" AS "event", 3.74 + "id" AS "issue_id" 3.75 + FROM "issue" WHERE "half_frozen" NOTNULL 3.76 + UNION ALL 3.77 + SELECT 3.78 + "fully_frozen" AS "occurrence", 3.79 + 'issue_voting_started'::"timeline_event" AS "event", 3.80 + "id" AS "issue_id" 3.81 + FROM "issue" 3.82 + WHERE "fully_frozen" NOTNULL AND "closed" != "fully_frozen" 3.83 + UNION ALL 3.84 + SELECT 3.85 + "closed" AS "occurrence", 3.86 + CASE WHEN "fully_frozen" = "closed" THEN 3.87 + 'issue_finished_without_voting'::"timeline_event" 3.88 + ELSE 3.89 + 'issue_finished_after_voting'::"timeline_event" 3.90 + END AS "event", 3.91 + "id" AS "issue_id" 3.92 + FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" NOTNULL; 3.93 + 3.94 +COMMENT ON VIEW "timeline_issue" IS 'Helper view for "timeline" view'; 3.95 + 3.96 +CREATE VIEW "timeline_initiative" AS 3.97 + SELECT 3.98 + "created" AS "occurrence", 3.99 + 'initiative_created'::"timeline_event" AS "event", 3.100 + "id" AS "initiative_id" 3.101 + FROM "initiative" 3.102 + UNION ALL 3.103 + SELECT 3.104 + "revoked" AS "occurrence", 3.105 + 'initiative_revoked'::"timeline_event" AS "event", 3.106 + "id" AS "initiative_id" 3.107 + FROM "initiative" WHERE "revoked" NOTNULL; 3.108 + 3.109 +COMMENT ON VIEW "timeline_initiative" IS 'Helper view for "timeline" view'; 3.110 + 3.111 +CREATE VIEW "timeline_draft" AS 3.112 + SELECT 3.113 + "created" AS "occurrence", 3.114 + 'draft_created'::"timeline_event" AS "event", 3.115 + "id" AS "draft_id" 3.116 + FROM "draft"; 3.117 + 3.118 +COMMENT ON VIEW "timeline_draft" IS 'Helper view for "timeline" view'; 3.119 + 3.120 +CREATE VIEW "timeline_suggestion" AS 3.121 + SELECT 3.122 + "created" AS "occurrence", 3.123 + 'suggestion_created'::"timeline_event" AS "event", 3.124 + "id" AS "suggestion_id" 3.125 + FROM "suggestion"; 3.126 + 3.127 +COMMENT ON VIEW "timeline_suggestion" IS 'Helper view for "timeline" view'; 3.128 + 3.129 +CREATE VIEW "timeline" AS 3.130 + SELECT 3.131 + "occurrence", 3.132 + "event", 3.133 + "issue_id", 3.134 + NULL AS "initiative_id", 3.135 + NULL::INT8 AS "draft_id", -- TODO: Why do we need a type-cast here? Is this due to 32 bit architecture? 3.136 + NULL::INT8 AS "suggestion_id" 3.137 + FROM "timeline_issue" 3.138 + UNION ALL 3.139 + SELECT 3.140 + "occurrence", 3.141 + "event", 3.142 + NULL AS "issue_id", 3.143 + "initiative_id", 3.144 + NULL AS "draft_id", 3.145 + NULL AS "suggestion_id" 3.146 + FROM "timeline_initiative" 3.147 + UNION ALL 3.148 + SELECT 3.149 + "occurrence", 3.150 + "event", 3.151 + NULL AS "issue_id", 3.152 + NULL AS "initiative_id", 3.153 + "draft_id", 3.154 + NULL AS "suggestion_id" 3.155 + FROM "timeline_draft" 3.156 + UNION ALL 3.157 + SELECT 3.158 + "occurrence", 3.159 + "event", 3.160 + NULL AS "issue_id", 3.161 + NULL AS "initiative_id", 3.162 + NULL AS "draft_id", 3.163 + "suggestion_id" 3.164 + FROM "timeline_suggestion"; 3.165 + 3.166 +COMMENT ON VIEW "timeline" IS 'Aggregation of different events in the system'; 3.167 + 3.168 +COMMIT;
4.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000 4.2 +++ b/update/core-update.beta17-beta18.sql Sat Feb 06 03:37:55 2010 +0100 4.3 @@ -0,0 +1,50 @@ 4.4 +BEGIN; 4.5 + 4.6 +CREATE OR REPLACE VIEW "liquid_feedback_version" AS 4.7 + SELECT * FROM (VALUES ('beta18', NULL, NULL, NULL)) 4.8 + AS "subquery"("string", "major", "minor", "revision"); 4.9 + 4.10 +CREATE OR REPLACE VIEW "timeline_issue" AS 4.11 + SELECT 4.12 + "created" AS "occurrence", 4.13 + 'issue_created'::"timeline_event" AS "event", 4.14 + "id" AS "issue_id" 4.15 + FROM "issue" 4.16 + UNION ALL 4.17 + SELECT 4.18 + "closed" AS "occurrence", 4.19 + 'issue_canceled'::"timeline_event" AS "event", 4.20 + "id" AS "issue_id" 4.21 + FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" ISNULL 4.22 + UNION ALL 4.23 + SELECT 4.24 + "accepted" AS "occurrence", 4.25 + 'issue_accepted'::"timeline_event" AS "event", 4.26 + "id" AS "issue_id" 4.27 + FROM "issue" WHERE "accepted" NOTNULL 4.28 + UNION ALL 4.29 + SELECT 4.30 + "half_frozen" AS "occurrence", 4.31 + 'issue_half_frozen'::"timeline_event" AS "event", 4.32 + "id" AS "issue_id" 4.33 + FROM "issue" WHERE "half_frozen" NOTNULL 4.34 + UNION ALL 4.35 + SELECT 4.36 + "fully_frozen" AS "occurrence", 4.37 + 'issue_voting_started'::"timeline_event" AS "event", 4.38 + "id" AS "issue_id" 4.39 + FROM "issue" 4.40 + WHERE "fully_frozen" NOTNULL 4.41 + AND ("closed" ISNULL OR "closed" != "fully_frozen") 4.42 + UNION ALL 4.43 + SELECT 4.44 + "closed" AS "occurrence", 4.45 + CASE WHEN "fully_frozen" = "closed" THEN 4.46 + 'issue_finished_without_voting'::"timeline_event" 4.47 + ELSE 4.48 + 'issue_finished_after_voting'::"timeline_event" 4.49 + END AS "event", 4.50 + "id" AS "issue_id" 4.51 + FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" NOTNULL; 4.52 + 4.53 +COMMIT;