liquid_feedback_core
changeset 119:d6a145a5c9d3
Removed update scripts to old beta versions
author | jbe |
---|---|
date | Mon Mar 07 14:10:25 2011 +0100 (2011-03-07) |
parents | 7d6c5032262f |
children | 92fc60ed705d |
files | update/core-update.beta16-beta17.sql update/core-update.beta17-beta18.sql update/core-update.beta21-beta22.sql update/core-update.beta22-beta23.sql update/core-update.beta23-beta24.sql update/core-update.beta24-beta25.sql update/prepare-beta18-beta19.sql update/prepare-beta25-beta26.sql |
line diff
1.1 --- a/update/core-update.beta16-beta17.sql Mon Mar 07 13:54:43 2011 +0100 1.2 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000 1.3 @@ -1,165 +0,0 @@ 1.4 -BEGIN; 1.5 - 1.6 -CREATE OR REPLACE VIEW "liquid_feedback_version" AS 1.7 - SELECT * FROM (VALUES ('beta17', NULL, NULL, NULL)) 1.8 - AS "subquery"("string", "major", "minor", "revision"); 1.9 - 1.10 -COMMENT ON TABLE "setting" IS 'Place to store a frontend specific member setting as a string'; 1.11 - 1.12 -CREATE TABLE "setting_map" ( 1.13 - PRIMARY KEY ("member_id", "key", "subkey"), 1.14 - "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.15 - "key" TEXT NOT NULL, 1.16 - "subkey" TEXT NOT NULL, 1.17 - "value" TEXT NOT NULL ); 1.18 -CREATE INDEX "setting_map_key_idx" ON "setting_map" ("key"); 1.19 - 1.20 -COMMENT ON TABLE "setting_map" IS 'Place to store a frontend specific member setting as a map of key value pairs'; 1.21 - 1.22 -COMMENT ON COLUMN "setting_map"."key" IS 'Name of the setting, preceded by a frontend specific prefix'; 1.23 -COMMENT ON COLUMN "setting_map"."subkey" IS 'Key of a map entry'; 1.24 -COMMENT ON COLUMN "setting_map"."value" IS 'Value of a map entry'; 1.25 - 1.26 -CREATE INDEX "issue_created_idx" ON "issue" ("created"); 1.27 -CREATE INDEX "issue_accepted_idx" ON "issue" ("accepted"); 1.28 -CREATE INDEX "issue_half_frozen_idx" ON "issue" ("half_frozen"); 1.29 -CREATE INDEX "issue_fully_frozen_idx" ON "issue" ("fully_frozen"); 1.30 -CREATE INDEX "issue_closed_idx" ON "issue" ("closed"); 1.31 -CREATE INDEX "issue_closed_idx_canceled" ON "issue" ("closed") WHERE "fully_frozen" ISNULL; 1.32 -CREATE INDEX "initiative_created_idx" ON "initiative" ("created"); 1.33 -CREATE INDEX "initiative_revoked_idx" ON "initiative" ("revoked"); 1.34 -CREATE INDEX "draft_created_idx" ON "draft" ("created"); 1.35 -CREATE INDEX "suggestion_created_idx" ON "suggestion" ("created"); 1.36 - 1.37 -CREATE TYPE "timeline_event" AS ENUM ( 1.38 - 'issue_created', 1.39 - 'issue_canceled', 1.40 - 'issue_accepted', 1.41 - 'issue_half_frozen', 1.42 - 'issue_finished_without_voting', 1.43 - 'issue_voting_started', 1.44 - 'issue_finished_after_voting', 1.45 - 'initiative_created', 1.46 - 'initiative_revoked', 1.47 - 'draft_created', 1.48 - 'suggestion_created'); 1.49 - 1.50 -COMMENT ON TYPE "timeline_event" IS 'Types of event in timeline tables'; 1.51 - 1.52 -CREATE VIEW "timeline_issue" AS 1.53 - SELECT 1.54 - "created" AS "occurrence", 1.55 - 'issue_created'::"timeline_event" AS "event", 1.56 - "id" AS "issue_id" 1.57 - FROM "issue" 1.58 - UNION ALL 1.59 - SELECT 1.60 - "closed" AS "occurrence", 1.61 - 'issue_canceled'::"timeline_event" AS "event", 1.62 - "id" AS "issue_id" 1.63 - FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" ISNULL 1.64 - UNION ALL 1.65 - SELECT 1.66 - "accepted" AS "occurrence", 1.67 - 'issue_accepted'::"timeline_event" AS "event", 1.68 - "id" AS "issue_id" 1.69 - FROM "issue" WHERE "accepted" NOTNULL 1.70 - UNION ALL 1.71 - SELECT 1.72 - "half_frozen" AS "occurrence", 1.73 - 'issue_half_frozen'::"timeline_event" AS "event", 1.74 - "id" AS "issue_id" 1.75 - FROM "issue" WHERE "half_frozen" NOTNULL 1.76 - UNION ALL 1.77 - SELECT 1.78 - "fully_frozen" AS "occurrence", 1.79 - 'issue_voting_started'::"timeline_event" AS "event", 1.80 - "id" AS "issue_id" 1.81 - FROM "issue" 1.82 - WHERE "fully_frozen" NOTNULL AND "closed" != "fully_frozen" 1.83 - UNION ALL 1.84 - SELECT 1.85 - "closed" AS "occurrence", 1.86 - CASE WHEN "fully_frozen" = "closed" THEN 1.87 - 'issue_finished_without_voting'::"timeline_event" 1.88 - ELSE 1.89 - 'issue_finished_after_voting'::"timeline_event" 1.90 - END AS "event", 1.91 - "id" AS "issue_id" 1.92 - FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" NOTNULL; 1.93 - 1.94 -COMMENT ON VIEW "timeline_issue" IS 'Helper view for "timeline" view'; 1.95 - 1.96 -CREATE VIEW "timeline_initiative" AS 1.97 - SELECT 1.98 - "created" AS "occurrence", 1.99 - 'initiative_created'::"timeline_event" AS "event", 1.100 - "id" AS "initiative_id" 1.101 - FROM "initiative" 1.102 - UNION ALL 1.103 - SELECT 1.104 - "revoked" AS "occurrence", 1.105 - 'initiative_revoked'::"timeline_event" AS "event", 1.106 - "id" AS "initiative_id" 1.107 - FROM "initiative" WHERE "revoked" NOTNULL; 1.108 - 1.109 -COMMENT ON VIEW "timeline_initiative" IS 'Helper view for "timeline" view'; 1.110 - 1.111 -CREATE VIEW "timeline_draft" AS 1.112 - SELECT 1.113 - "created" AS "occurrence", 1.114 - 'draft_created'::"timeline_event" AS "event", 1.115 - "id" AS "draft_id" 1.116 - FROM "draft"; 1.117 - 1.118 -COMMENT ON VIEW "timeline_draft" IS 'Helper view for "timeline" view'; 1.119 - 1.120 -CREATE VIEW "timeline_suggestion" AS 1.121 - SELECT 1.122 - "created" AS "occurrence", 1.123 - 'suggestion_created'::"timeline_event" AS "event", 1.124 - "id" AS "suggestion_id" 1.125 - FROM "suggestion"; 1.126 - 1.127 -COMMENT ON VIEW "timeline_suggestion" IS 'Helper view for "timeline" view'; 1.128 - 1.129 -CREATE VIEW "timeline" AS 1.130 - SELECT 1.131 - "occurrence", 1.132 - "event", 1.133 - "issue_id", 1.134 - NULL AS "initiative_id", 1.135 - NULL::INT8 AS "draft_id", -- TODO: Why do we need a type-cast here? Is this due to 32 bit architecture? 1.136 - NULL::INT8 AS "suggestion_id" 1.137 - FROM "timeline_issue" 1.138 - UNION ALL 1.139 - SELECT 1.140 - "occurrence", 1.141 - "event", 1.142 - NULL AS "issue_id", 1.143 - "initiative_id", 1.144 - NULL AS "draft_id", 1.145 - NULL AS "suggestion_id" 1.146 - FROM "timeline_initiative" 1.147 - UNION ALL 1.148 - SELECT 1.149 - "occurrence", 1.150 - "event", 1.151 - NULL AS "issue_id", 1.152 - NULL AS "initiative_id", 1.153 - "draft_id", 1.154 - NULL AS "suggestion_id" 1.155 - FROM "timeline_draft" 1.156 - UNION ALL 1.157 - SELECT 1.158 - "occurrence", 1.159 - "event", 1.160 - NULL AS "issue_id", 1.161 - NULL AS "initiative_id", 1.162 - NULL AS "draft_id", 1.163 - "suggestion_id" 1.164 - FROM "timeline_suggestion"; 1.165 - 1.166 -COMMENT ON VIEW "timeline" IS 'Aggregation of different events in the system'; 1.167 - 1.168 -COMMIT;
2.1 --- a/update/core-update.beta17-beta18.sql Mon Mar 07 13:54:43 2011 +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 --- a/update/core-update.beta21-beta22.sql Mon Mar 07 13:54:43 2011 +0100 3.2 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000 3.3 @@ -1,16 +0,0 @@ 3.4 -BEGIN; 3.5 -CREATE OR REPLACE VIEW "liquid_feedback_version" AS 3.6 - SELECT * FROM (VALUES ('beta22', NULL, NULL, NULL)) 3.7 - AS "subquery"("string", "major", "minor", "revision"); 3.8 -ALTER TABLE "issue" DROP CONSTRAINT "valid_state"; 3.9 -ALTER TABLE "issue" ADD CONSTRAINT "valid_state" CHECK ( 3.10 - ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR 3.11 - ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR 3.12 - ("accepted" NOTNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR 3.13 - ("accepted" NOTNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR 3.14 - ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR 3.15 - ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR 3.16 - ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR 3.17 - ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR 3.18 - ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = TRUE) ); 3.19 -COMMIT;
4.1 --- a/update/core-update.beta22-beta23.sql Mon Mar 07 13:54:43 2011 +0100 4.2 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000 4.3 @@ -1,199 +0,0 @@ 4.4 -BEGIN; 4.5 - 4.6 -CREATE OR REPLACE VIEW "liquid_feedback_version" AS 4.7 - SELECT * FROM (VALUES ('beta23', NULL, NULL, NULL)) 4.8 - AS "subquery"("string", "major", "minor", "revision"); 4.9 - 4.10 -CREATE OR REPLACE FUNCTION "create_snapshot" 4.11 - ( "issue_id_p" "issue"."id"%TYPE ) 4.12 - RETURNS VOID 4.13 - LANGUAGE 'plpgsql' VOLATILE AS $$ 4.14 - DECLARE 4.15 - "initiative_id_v" "initiative"."id"%TYPE; 4.16 - "suggestion_id_v" "suggestion"."id"%TYPE; 4.17 - BEGIN 4.18 - PERFORM "global_lock"(); 4.19 - PERFORM "create_population_snapshot"("issue_id_p"); 4.20 - PERFORM "create_interest_snapshot"("issue_id_p"); 4.21 - UPDATE "issue" SET 4.22 - "snapshot" = now(), 4.23 - "latest_snapshot_event" = 'periodic', 4.24 - "population" = ( 4.25 - SELECT coalesce(sum("weight"), 0) 4.26 - FROM "direct_population_snapshot" 4.27 - WHERE "issue_id" = "issue_id_p" 4.28 - AND "event" = 'periodic' 4.29 - ), 4.30 - "vote_now" = ( 4.31 - SELECT coalesce(sum("weight"), 0) 4.32 - FROM "direct_interest_snapshot" 4.33 - WHERE "issue_id" = "issue_id_p" 4.34 - AND "event" = 'periodic' 4.35 - AND "voting_requested" = TRUE 4.36 - ), 4.37 - "vote_later" = ( 4.38 - SELECT coalesce(sum("weight"), 0) 4.39 - FROM "direct_interest_snapshot" 4.40 - WHERE "issue_id" = "issue_id_p" 4.41 - AND "event" = 'periodic' 4.42 - AND "voting_requested" = FALSE 4.43 - ) 4.44 - WHERE "id" = "issue_id_p"; 4.45 - FOR "initiative_id_v" IN 4.46 - SELECT "id" FROM "initiative" WHERE "issue_id" = "issue_id_p" 4.47 - LOOP 4.48 - UPDATE "initiative" SET 4.49 - "supporter_count" = ( 4.50 - SELECT coalesce(sum("di"."weight"), 0) 4.51 - FROM "direct_interest_snapshot" AS "di" 4.52 - JOIN "direct_supporter_snapshot" AS "ds" 4.53 - ON "di"."member_id" = "ds"."member_id" 4.54 - WHERE "di"."issue_id" = "issue_id_p" 4.55 - AND "di"."event" = 'periodic' 4.56 - AND "ds"."initiative_id" = "initiative_id_v" 4.57 - AND "ds"."event" = 'periodic' 4.58 - ), 4.59 - "informed_supporter_count" = ( 4.60 - SELECT coalesce(sum("di"."weight"), 0) 4.61 - FROM "direct_interest_snapshot" AS "di" 4.62 - JOIN "direct_supporter_snapshot" AS "ds" 4.63 - ON "di"."member_id" = "ds"."member_id" 4.64 - WHERE "di"."issue_id" = "issue_id_p" 4.65 - AND "di"."event" = 'periodic' 4.66 - AND "ds"."initiative_id" = "initiative_id_v" 4.67 - AND "ds"."event" = 'periodic' 4.68 - AND "ds"."informed" 4.69 - ), 4.70 - "satisfied_supporter_count" = ( 4.71 - SELECT coalesce(sum("di"."weight"), 0) 4.72 - FROM "direct_interest_snapshot" AS "di" 4.73 - JOIN "direct_supporter_snapshot" AS "ds" 4.74 - ON "di"."member_id" = "ds"."member_id" 4.75 - WHERE "di"."issue_id" = "issue_id_p" 4.76 - AND "di"."event" = 'periodic' 4.77 - AND "ds"."initiative_id" = "initiative_id_v" 4.78 - AND "ds"."event" = 'periodic' 4.79 - AND "ds"."satisfied" 4.80 - ), 4.81 - "satisfied_informed_supporter_count" = ( 4.82 - SELECT coalesce(sum("di"."weight"), 0) 4.83 - FROM "direct_interest_snapshot" AS "di" 4.84 - JOIN "direct_supporter_snapshot" AS "ds" 4.85 - ON "di"."member_id" = "ds"."member_id" 4.86 - WHERE "di"."issue_id" = "issue_id_p" 4.87 - AND "di"."event" = 'periodic' 4.88 - AND "ds"."initiative_id" = "initiative_id_v" 4.89 - AND "ds"."event" = 'periodic' 4.90 - AND "ds"."informed" 4.91 - AND "ds"."satisfied" 4.92 - ) 4.93 - WHERE "id" = "initiative_id_v"; 4.94 - FOR "suggestion_id_v" IN 4.95 - SELECT "id" FROM "suggestion" 4.96 - WHERE "initiative_id" = "initiative_id_v" 4.97 - LOOP 4.98 - UPDATE "suggestion" SET 4.99 - "minus2_unfulfilled_count" = ( 4.100 - SELECT coalesce(sum("snapshot"."weight"), 0) 4.101 - FROM "issue" CROSS JOIN "opinion" 4.102 - JOIN "direct_interest_snapshot" AS "snapshot" 4.103 - ON "snapshot"."issue_id" = "issue"."id" 4.104 - AND "snapshot"."event" = "issue"."latest_snapshot_event" 4.105 - AND "snapshot"."member_id" = "opinion"."member_id" 4.106 - WHERE "issue"."id" = "issue_id_p" 4.107 - AND "opinion"."suggestion_id" = "suggestion_id_v" 4.108 - AND "opinion"."degree" = -2 4.109 - AND "opinion"."fulfilled" = FALSE 4.110 - ), 4.111 - "minus2_fulfilled_count" = ( 4.112 - SELECT coalesce(sum("snapshot"."weight"), 0) 4.113 - FROM "issue" CROSS JOIN "opinion" 4.114 - JOIN "direct_interest_snapshot" AS "snapshot" 4.115 - ON "snapshot"."issue_id" = "issue"."id" 4.116 - AND "snapshot"."event" = "issue"."latest_snapshot_event" 4.117 - AND "snapshot"."member_id" = "opinion"."member_id" 4.118 - WHERE "issue"."id" = "issue_id_p" 4.119 - AND "opinion"."suggestion_id" = "suggestion_id_v" 4.120 - AND "opinion"."degree" = -2 4.121 - AND "opinion"."fulfilled" = TRUE 4.122 - ), 4.123 - "minus1_unfulfilled_count" = ( 4.124 - SELECT coalesce(sum("snapshot"."weight"), 0) 4.125 - FROM "issue" CROSS JOIN "opinion" 4.126 - JOIN "direct_interest_snapshot" AS "snapshot" 4.127 - ON "snapshot"."issue_id" = "issue"."id" 4.128 - AND "snapshot"."event" = "issue"."latest_snapshot_event" 4.129 - AND "snapshot"."member_id" = "opinion"."member_id" 4.130 - WHERE "issue"."id" = "issue_id_p" 4.131 - AND "opinion"."suggestion_id" = "suggestion_id_v" 4.132 - AND "opinion"."degree" = -1 4.133 - AND "opinion"."fulfilled" = FALSE 4.134 - ), 4.135 - "minus1_fulfilled_count" = ( 4.136 - SELECT coalesce(sum("snapshot"."weight"), 0) 4.137 - FROM "issue" CROSS JOIN "opinion" 4.138 - JOIN "direct_interest_snapshot" AS "snapshot" 4.139 - ON "snapshot"."issue_id" = "issue"."id" 4.140 - AND "snapshot"."event" = "issue"."latest_snapshot_event" 4.141 - AND "snapshot"."member_id" = "opinion"."member_id" 4.142 - WHERE "issue"."id" = "issue_id_p" 4.143 - AND "opinion"."suggestion_id" = "suggestion_id_v" 4.144 - AND "opinion"."degree" = -1 4.145 - AND "opinion"."fulfilled" = TRUE 4.146 - ), 4.147 - "plus1_unfulfilled_count" = ( 4.148 - SELECT coalesce(sum("snapshot"."weight"), 0) 4.149 - FROM "issue" CROSS JOIN "opinion" 4.150 - JOIN "direct_interest_snapshot" AS "snapshot" 4.151 - ON "snapshot"."issue_id" = "issue"."id" 4.152 - AND "snapshot"."event" = "issue"."latest_snapshot_event" 4.153 - AND "snapshot"."member_id" = "opinion"."member_id" 4.154 - WHERE "issue"."id" = "issue_id_p" 4.155 - AND "opinion"."suggestion_id" = "suggestion_id_v" 4.156 - AND "opinion"."degree" = 1 4.157 - AND "opinion"."fulfilled" = FALSE 4.158 - ), 4.159 - "plus1_fulfilled_count" = ( 4.160 - SELECT coalesce(sum("snapshot"."weight"), 0) 4.161 - FROM "issue" CROSS JOIN "opinion" 4.162 - JOIN "direct_interest_snapshot" AS "snapshot" 4.163 - ON "snapshot"."issue_id" = "issue"."id" 4.164 - AND "snapshot"."event" = "issue"."latest_snapshot_event" 4.165 - AND "snapshot"."member_id" = "opinion"."member_id" 4.166 - WHERE "issue"."id" = "issue_id_p" 4.167 - AND "opinion"."suggestion_id" = "suggestion_id_v" 4.168 - AND "opinion"."degree" = 1 4.169 - AND "opinion"."fulfilled" = TRUE 4.170 - ), 4.171 - "plus2_unfulfilled_count" = ( 4.172 - SELECT coalesce(sum("snapshot"."weight"), 0) 4.173 - FROM "issue" CROSS JOIN "opinion" 4.174 - JOIN "direct_interest_snapshot" AS "snapshot" 4.175 - ON "snapshot"."issue_id" = "issue"."id" 4.176 - AND "snapshot"."event" = "issue"."latest_snapshot_event" 4.177 - AND "snapshot"."member_id" = "opinion"."member_id" 4.178 - WHERE "issue"."id" = "issue_id_p" 4.179 - AND "opinion"."suggestion_id" = "suggestion_id_v" 4.180 - AND "opinion"."degree" = 2 4.181 - AND "opinion"."fulfilled" = FALSE 4.182 - ), 4.183 - "plus2_fulfilled_count" = ( 4.184 - SELECT coalesce(sum("snapshot"."weight"), 0) 4.185 - FROM "issue" CROSS JOIN "opinion" 4.186 - JOIN "direct_interest_snapshot" AS "snapshot" 4.187 - ON "snapshot"."issue_id" = "issue"."id" 4.188 - AND "snapshot"."event" = "issue"."latest_snapshot_event" 4.189 - AND "snapshot"."member_id" = "opinion"."member_id" 4.190 - WHERE "issue"."id" = "issue_id_p" 4.191 - AND "opinion"."suggestion_id" = "suggestion_id_v" 4.192 - AND "opinion"."degree" = 2 4.193 - AND "opinion"."fulfilled" = TRUE 4.194 - ) 4.195 - WHERE "suggestion"."id" = "suggestion_id_v"; 4.196 - END LOOP; 4.197 - END LOOP; 4.198 - RETURN; 4.199 - END; 4.200 - $$; 4.201 - 4.202 -COMMIT;
5.1 --- a/update/core-update.beta23-beta24.sql Mon Mar 07 13:54:43 2011 +0100 5.2 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000 5.3 @@ -1,60 +0,0 @@ 5.4 -BEGIN; 5.5 - 5.6 -CREATE OR REPLACE VIEW "liquid_feedback_version" AS 5.7 - SELECT * FROM (VALUES ('beta24', NULL, NULL, NULL)) 5.8 - AS "subquery"("string", "major", "minor", "revision"); 5.9 - 5.10 -COMMENT ON TABLE "setting" IS 'Place to store a frontend specific setting for members as a string'; 5.11 - 5.12 -COMMENT ON TABLE "member_relation_setting" IS 'Place to store a frontend specific setting related to relations between members as a string'; 5.13 - 5.14 -CREATE OR REPLACE FUNCTION "delete_private_data"() 5.15 - RETURNS VOID 5.16 - LANGUAGE 'plpgsql' VOLATILE AS $$ 5.17 - DECLARE 5.18 - "issue_id_v" "issue"."id"%TYPE; 5.19 - BEGIN 5.20 - UPDATE "member" SET 5.21 - "login" = 'login' || "id"::text, 5.22 - "password" = NULL, 5.23 - "notify_email" = NULL, 5.24 - "notify_email_unconfirmed" = NULL, 5.25 - "notify_email_secret" = NULL, 5.26 - "notify_email_secret_expiry" = NULL, 5.27 - "password_reset_secret" = NULL, 5.28 - "password_reset_secret_expiry" = NULL, 5.29 - "organizational_unit" = NULL, 5.30 - "internal_posts" = NULL, 5.31 - "realname" = NULL, 5.32 - "birthday" = NULL, 5.33 - "address" = NULL, 5.34 - "email" = NULL, 5.35 - "xmpp_address" = NULL, 5.36 - "website" = NULL, 5.37 - "phone" = NULL, 5.38 - "mobile_phone" = NULL, 5.39 - "profession" = NULL, 5.40 - "external_memberships" = NULL, 5.41 - "external_posts" = NULL, 5.42 - "statement" = NULL; 5.43 - -- "text_search_data" is updated by triggers 5.44 - UPDATE "member_history" SET "login" = 'login' || "member_id"::text; 5.45 - DELETE FROM "invite_code"; 5.46 - DELETE FROM "setting"; 5.47 - DELETE FROM "setting_map"; 5.48 - DELETE FROM "member_relation_setting"; 5.49 - DELETE FROM "member_image"; 5.50 - DELETE FROM "contact"; 5.51 - DELETE FROM "session"; 5.52 - DELETE FROM "area_setting"; 5.53 - DELETE FROM "issue_setting"; 5.54 - DELETE FROM "initiative_setting"; 5.55 - DELETE FROM "suggestion_setting"; 5.56 - DELETE FROM "direct_voter" USING "issue" 5.57 - WHERE "direct_voter"."issue_id" = "issue"."id" 5.58 - AND "issue"."closed" ISNULL; 5.59 - RETURN; 5.60 - END; 5.61 - $$; 5.62 - 5.63 -COMMIT;
6.1 --- a/update/core-update.beta24-beta25.sql Mon Mar 07 13:54:43 2011 +0100 6.2 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000 6.3 @@ -1,5 +0,0 @@ 6.4 -BEGIN; 6.5 - 6.6 --- no changes in database between beta24 to beta25 6.7 - 6.8 -COMMIT;
7.1 --- a/update/prepare-beta18-beta19.sql Mon Mar 07 13:54:43 2011 +0100 7.2 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000 7.3 @@ -1,60 +0,0 @@ 7.4 -BEGIN; 7.5 - 7.6 -CREATE OR REPLACE VIEW "liquid_feedback_version" AS 7.7 - SELECT * FROM (VALUES ('incomplete_update_from_beta18_to_beta19', NULL, NULL, NULL)) 7.8 - AS "subquery"("string", "major", "minor", "revision"); 7.9 - 7.10 -ALTER TABLE "issue" RENAME COLUMN "latest_snapshot_event" TO "tmp"; 7.11 -ALTER TABLE "direct_population_snapshot" RENAME COLUMN "event" TO "tmp"; 7.12 -ALTER TABLE "delegating_population_snapshot" RENAME COLUMN "event" TO "tmp"; 7.13 -ALTER TABLE "direct_interest_snapshot" RENAME COLUMN "event" TO "tmp"; 7.14 -ALTER TABLE "delegating_interest_snapshot" RENAME COLUMN "event" TO "tmp"; 7.15 -ALTER TABLE "direct_supporter_snapshot" RENAME COLUMN "event" TO "tmp"; 7.16 - 7.17 -ALTER TABLE "issue" ADD COLUMN "latest_snapshot_event" TEXT; 7.18 -ALTER TABLE "direct_population_snapshot" ADD COLUMN "event" TEXT; 7.19 -ALTER TABLE "delegating_population_snapshot" ADD COLUMN "event" TEXT; 7.20 -ALTER TABLE "direct_interest_snapshot" ADD COLUMN "event" TEXT; 7.21 -ALTER TABLE "delegating_interest_snapshot" ADD COLUMN "event" TEXT; 7.22 -ALTER TABLE "direct_supporter_snapshot" ADD COLUMN "event" TEXT; 7.23 - 7.24 -ALTER TABLE "issue" ADD COLUMN "admission_time" INTERVAL; 7.25 -ALTER TABLE "issue" ADD COLUMN "discussion_time" INTERVAL; 7.26 -ALTER TABLE "issue" ADD COLUMN "verification_time" INTERVAL; 7.27 -ALTER TABLE "issue" ADD COLUMN "voting_time" INTERVAL; 7.28 - 7.29 -UPDATE "issue" SET "latest_snapshot_event" = "tmp"; 7.30 -UPDATE "direct_population_snapshot" SET "event" = "tmp"; 7.31 -UPDATE "delegating_population_snapshot" SET "event" = "tmp"; 7.32 -UPDATE "direct_interest_snapshot" SET "event" = "tmp"; 7.33 -UPDATE "delegating_interest_snapshot" SET "event" = "tmp"; 7.34 -UPDATE "direct_supporter_snapshot" SET "event" = "tmp"; 7.35 - 7.36 -UPDATE "issue" SET "latest_snapshot_event" = 'full_freeze' WHERE "latest_snapshot_event" = 'start_of_voting'; 7.37 -UPDATE "direct_population_snapshot" SET "event" = 'full_freeze' WHERE "event" = 'start_of_voting'; 7.38 -UPDATE "delegating_population_snapshot" SET "event" = 'full_freeze' WHERE "event" = 'start_of_voting'; 7.39 -UPDATE "direct_interest_snapshot" SET "event" = 'full_freeze' WHERE "event" = 'start_of_voting'; 7.40 -UPDATE "delegating_interest_snapshot" SET "event" = 'full_freeze' WHERE "event" = 'start_of_voting'; 7.41 -UPDATE "direct_supporter_snapshot" SET "event" = 'full_freeze' WHERE "event" = 'start_of_voting'; 7.42 - 7.43 -UPDATE "issue" SET 7.44 - "admission_time" = "policy"."admission_time", 7.45 - "discussion_time" = "policy"."discussion_time", 7.46 - "verification_time" = "policy"."verification_time", 7.47 - "voting_time" = "policy"."voting_time" 7.48 - FROM "policy" WHERE "issue"."policy_id" = "policy"."id"; 7.49 - 7.50 --- remove "tmp" columns indirectly 7.51 -DROP TYPE "snapshot_event" CASCADE; 7.52 - 7.53 -COMMIT; 7.54 - 7.55 --- Complete the update as follows: 7.56 --- ========================================= 7.57 --- pg_dump --disable-triggers --data-only DATABASE_NAME > tmp.sql 7.58 --- dropdb DATABASE_NAME 7.59 --- createdb DATABASE_NAME 7.60 --- psql -v ON_ERROR_STOP=1 -f core.sql DATABASE_NAME 7.61 --- psql -v ON_ERROR_STOP=1 -f tmp.sql DATABASE_NAME 7.62 --- rm tmp.sql 7.63 -
8.1 --- a/update/prepare-beta25-beta26.sql Mon Mar 07 13:54:43 2011 +0100 8.2 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000 8.3 @@ -1,25 +0,0 @@ 8.4 -BEGIN; 8.5 - 8.6 -CREATE OR REPLACE VIEW "liquid_feedback_version" AS 8.7 - SELECT * FROM (VALUES ('incomplete_update_from_beta25_to_beta26', NULL, NULL, NULL)) 8.8 - AS "subquery"("string", "major", "minor", "revision"); 8.9 - 8.10 -ALTER TABLE "member" ADD COLUMN "last_login" TIMESTAMPTZ; 8.11 -ALTER TABLE "member_history" ADD COLUMN "active" BOOLEAN; 8.12 - 8.13 -UPDATE "member_history" SET "active" = TRUE; 8.14 -INSERT INTO "member_history" ("member_id", "login", "active", "name") 8.15 - SELECT "id", "login", TRUE AS "active", "name" 8.16 - FROM "member" WHERE "active" = FALSE; 8.17 - 8.18 -COMMIT; 8.19 - 8.20 --- Complete the update as follows: 8.21 --- ========================================= 8.22 --- pg_dump --disable-triggers --data-only DATABASE_NAME > tmp.sql 8.23 --- dropdb DATABASE_NAME 8.24 --- createdb DATABASE_NAME 8.25 --- psql -v ON_ERROR_STOP=1 -f core.sql DATABASE_NAME 8.26 --- psql -v ON_ERROR_STOP=1 -f tmp.sql DATABASE_NAME 8.27 --- rm tmp.sql 8.28 -