liquid_feedback_core
annotate update/core-update.v2.0.0-v2.0.1.sql @ 297:779ba19a07df
Different locking levels and different locking order in function "lock_issue"("issue"."id") to avoid deadlocks
(includes update of update/core-update.v2.0.11-v2.1.0.sql)
(includes update of update/core-update.v2.0.11-v2.1.0.sql)
author | jbe |
---|---|
date | Tue Sep 25 02:32:51 2012 +0200 (2012-09-25) |
parents | c23b288fd771 |
children |
rev | line source |
---|---|
jbe@205 | 1 BEGIN; |
jbe@205 | 2 |
jbe@212 | 3 -- update version number: |
jbe@211 | 4 CREATE OR REPLACE VIEW "liquid_feedback_version" AS |
jbe@211 | 5 SELECT * FROM (VALUES ('2.0.1', 2, 0, 1)) |
jbe@211 | 6 AS "subquery"("string", "major", "minor", "revision"); |
jbe@205 | 7 |
jbe@212 | 8 -- fix errors in update script from v1.3.1 to v1.4.0: |
jbe@212 | 9 ALTER TABLE "issue" ALTER "state" SET DEFAULT 'admission'; |
jbe@205 | 10 |
jbe@212 | 11 -- change comments in "member" table: |
jbe@211 | 12 COMMENT ON COLUMN "member"."activated" IS 'Timestamp of first activation of account (i.e. usage of "invite_code"); required to be set for "active" members'; |
jbe@211 | 13 COMMENT ON COLUMN "member"."statement" IS 'Freely chosen text of the member for his/her profile'; |
jbe@211 | 14 |
jbe@212 | 15 -- change comments in "policy" table: |
jbe@211 | 16 COMMENT ON COLUMN "policy"."admission_time" IS 'Maximum duration of issue state ''admission''; Maximum time an issue stays open without being "accepted"'; |
jbe@211 | 17 COMMENT ON COLUMN "policy"."discussion_time" IS 'Duration of issue state ''discussion''; Regular time until an issue is "half_frozen" after being "accepted"'; |
jbe@211 | 18 COMMENT ON COLUMN "policy"."verification_time" IS 'Duration of issue state ''verification''; Regular time until an issue is "fully_frozen" (e.g. entering issue state ''voting'') after being "half_frozen"'; |
jbe@211 | 19 COMMENT ON COLUMN "policy"."voting_time" IS 'Duration of issue state ''voting''; Time after an issue is "fully_frozen" but not "closed" (duration of issue state ''voting'')'; |
jbe@211 | 20 COMMENT ON COLUMN "policy"."issue_quorum_num" IS 'Numerator of potential supporter quorum to be reached by one initiative of an issue to be "accepted" and enter issue state ''discussion'''; |
jbe@211 | 21 COMMENT ON COLUMN "policy"."issue_quorum_den" IS 'Denominator of potential supporter quorum to be reached by one initiative of an issue to be "accepted" and enter issue state ''discussion'''; |
jbe@211 | 22 |
jbe@212 | 23 -- change comment in "unit" table: |
jbe@212 | 24 COMMENT ON COLUMN "unit"."active" IS 'TRUE means new issues can be created in areas of this unit'; |
jbe@211 | 25 |
jbe@212 | 26 -- new table "unit_setting": |
jbe@211 | 27 CREATE TABLE "unit_setting" ( |
jbe@211 | 28 PRIMARY KEY ("member_id", "key", "unit_id"), |
jbe@211 | 29 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE, |
jbe@211 | 30 "key" TEXT NOT NULL, |
jbe@211 | 31 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE, |
jbe@211 | 32 "value" TEXT NOT NULL ); |
jbe@211 | 33 COMMENT ON TABLE "unit_setting" IS 'Place for frontend to store unit specific settings of members as strings'; |
jbe@211 | 34 |
jbe@212 | 35 -- change comments in "initiative" table: |
jbe@212 | 36 COMMENT ON COLUMN "initiative"."revoked" IS 'Point in time, when one initiator decided to revoke the initiative'; |
jbe@212 | 37 COMMENT ON COLUMN "initiative"."reverse_beat_path" IS 'TRUE, if there is a beat path (may include ties) from this initiative to the status quo'; |
jbe@212 | 38 COMMENT ON COLUMN "initiative"."eligible" IS 'Initiative has a "direct_majority" and an "indirect_majority", is "better_than_status_quo" and depending on selected policy the initiative has no "reverse_beat_path" or "multistage_majority"'; |
jbe@211 | 39 |
jbe@212 | 40 -- change comments in "privilege" table: |
jbe@211 | 41 COMMENT ON COLUMN "privilege"."admin_manager" IS 'Grant/revoke admin privileges to/from other members'; |
jbe@211 | 42 COMMENT ON COLUMN "privilege"."unit_manager" IS 'Create and disable sub units'; |
jbe@211 | 43 COMMENT ON COLUMN "privilege"."area_manager" IS 'Create and disable areas and set area parameters'; |
jbe@211 | 44 COMMENT ON COLUMN "privilege"."voting_right_manager" IS 'Select which members are allowed to discuss and vote within the unit'; |
jbe@211 | 45 |
jbe@212 | 46 -- add comment to "supporter" table: |
jbe@211 | 47 COMMENT ON COLUMN "supporter"."issue_id" IS 'WARNING: No index: For selections use column "initiative_id" and join via table "initiative" where neccessary'; |
jbe@211 | 48 |
jbe@212 | 49 -- add column "draft_id" to table "direct_supporter_snapshot": |
jbe@211 | 50 ALTER TABLE "direct_supporter_snapshot" ADD COLUMN "draft_id" INT8; |
jbe@211 | 51 UPDATE "direct_supporter_snapshot" SET "draft_id" = "supporter"."draft_id" FROM "supporter" WHERE "direct_supporter_snapshot"."initiative_id" = "supporter"."initiative_id" AND "direct_supporter_snapshot"."member_id" = "supporter"."member_id"; |
jbe@211 | 52 UPDATE "direct_supporter_snapshot" SET "draft_id" = "current_draft"."id" FROM "current_draft" WHERE "direct_supporter_snapshot"."initiative_id" = "current_draft"."initiative_id" AND "direct_supporter_snapshot"."draft_id" ISNULL; |
jbe@211 | 53 ALTER TABLE "direct_supporter_snapshot" ADD FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE NO ACTION ON UPDATE CASCADE; |
jbe@211 | 54 ALTER TABLE "direct_supporter_snapshot" ALTER COLUMN "draft_id" SET NOT NULL; |
jbe@211 | 55 |
jbe@212 | 56 -- add comment to "direct_supporter_snapshot" table: |
jbe@212 | 57 COMMENT ON COLUMN "direct_supporter_snapshot"."issue_id" IS 'WARNING: No index: For selections use column "initiative_id" and join via table "initiative" where neccessary'; |
jbe@211 | 58 |
jbe@212 | 59 -- add comment to "vote" table: |
jbe@211 | 60 COMMENT ON COLUMN "vote"."issue_id" IS 'WARNING: No index: For selections use column "initiative_id" and join via table "initiative" where neccessary'; |
jbe@211 | 61 |
jbe@212 | 62 -- change function "create_interest_snapshot" to set "direct_supporter_snapshot"."draft_id": |
jbe@211 | 63 CREATE OR REPLACE FUNCTION "create_interest_snapshot" |
jbe@211 | 64 ( "issue_id_p" "issue"."id"%TYPE ) |
jbe@211 | 65 RETURNS VOID |
jbe@211 | 66 LANGUAGE 'plpgsql' VOLATILE AS $$ |
jbe@211 | 67 DECLARE |
jbe@211 | 68 "member_id_v" "member"."id"%TYPE; |
jbe@211 | 69 BEGIN |
jbe@211 | 70 DELETE FROM "direct_interest_snapshot" |
jbe@211 | 71 WHERE "issue_id" = "issue_id_p" |
jbe@211 | 72 AND "event" = 'periodic'; |
jbe@211 | 73 DELETE FROM "delegating_interest_snapshot" |
jbe@211 | 74 WHERE "issue_id" = "issue_id_p" |
jbe@211 | 75 AND "event" = 'periodic'; |
jbe@211 | 76 DELETE FROM "direct_supporter_snapshot" |
jbe@211 | 77 WHERE "issue_id" = "issue_id_p" |
jbe@211 | 78 AND "event" = 'periodic'; |
jbe@211 | 79 INSERT INTO "direct_interest_snapshot" |
jbe@211 | 80 ("issue_id", "event", "member_id") |
jbe@211 | 81 SELECT |
jbe@211 | 82 "issue_id_p" AS "issue_id", |
jbe@211 | 83 'periodic' AS "event", |
jbe@211 | 84 "member"."id" AS "member_id" |
jbe@211 | 85 FROM "issue" |
jbe@211 | 86 JOIN "area" ON "issue"."area_id" = "area"."id" |
jbe@211 | 87 JOIN "interest" ON "issue"."id" = "interest"."issue_id" |
jbe@211 | 88 JOIN "member" ON "interest"."member_id" = "member"."id" |
jbe@211 | 89 JOIN "privilege" |
jbe@211 | 90 ON "privilege"."unit_id" = "area"."unit_id" |
jbe@211 | 91 AND "privilege"."member_id" = "member"."id" |
jbe@211 | 92 WHERE "issue"."id" = "issue_id_p" |
jbe@211 | 93 AND "member"."active" AND "privilege"."voting_right"; |
jbe@211 | 94 FOR "member_id_v" IN |
jbe@211 | 95 SELECT "member_id" FROM "direct_interest_snapshot" |
jbe@211 | 96 WHERE "issue_id" = "issue_id_p" |
jbe@211 | 97 AND "event" = 'periodic' |
jbe@211 | 98 LOOP |
jbe@211 | 99 UPDATE "direct_interest_snapshot" SET |
jbe@211 | 100 "weight" = 1 + |
jbe@211 | 101 "weight_of_added_delegations_for_interest_snapshot"( |
jbe@211 | 102 "issue_id_p", |
jbe@211 | 103 "member_id_v", |
jbe@211 | 104 '{}' |
jbe@211 | 105 ) |
jbe@211 | 106 WHERE "issue_id" = "issue_id_p" |
jbe@211 | 107 AND "event" = 'periodic' |
jbe@211 | 108 AND "member_id" = "member_id_v"; |
jbe@211 | 109 END LOOP; |
jbe@211 | 110 INSERT INTO "direct_supporter_snapshot" |
jbe@211 | 111 ( "issue_id", "initiative_id", "event", "member_id", |
jbe@211 | 112 "draft_id", "informed", "satisfied" ) |
jbe@211 | 113 SELECT |
jbe@211 | 114 "issue_id_p" AS "issue_id", |
jbe@211 | 115 "initiative"."id" AS "initiative_id", |
jbe@211 | 116 'periodic' AS "event", |
jbe@211 | 117 "supporter"."member_id" AS "member_id", |
jbe@211 | 118 "supporter"."draft_id" AS "draft_id", |
jbe@211 | 119 "supporter"."draft_id" = "current_draft"."id" AS "informed", |
jbe@211 | 120 NOT EXISTS ( |
jbe@211 | 121 SELECT NULL FROM "critical_opinion" |
jbe@211 | 122 WHERE "initiative_id" = "initiative"."id" |
jbe@211 | 123 AND "member_id" = "supporter"."member_id" |
jbe@211 | 124 ) AS "satisfied" |
jbe@211 | 125 FROM "initiative" |
jbe@211 | 126 JOIN "supporter" |
jbe@211 | 127 ON "supporter"."initiative_id" = "initiative"."id" |
jbe@211 | 128 JOIN "current_draft" |
jbe@211 | 129 ON "initiative"."id" = "current_draft"."initiative_id" |
jbe@211 | 130 JOIN "direct_interest_snapshot" |
jbe@211 | 131 ON "supporter"."member_id" = "direct_interest_snapshot"."member_id" |
jbe@211 | 132 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id" |
jbe@211 | 133 AND "event" = 'periodic' |
jbe@211 | 134 WHERE "initiative"."issue_id" = "issue_id_p"; |
jbe@211 | 135 RETURN; |
jbe@211 | 136 END; |
jbe@211 | 137 $$; |
jbe@211 | 138 |
jbe@212 | 139 -- change function "delete_private_data" to delete invite codes in "member" instead of "invite_code" table: |
jbe@211 | 140 CREATE OR REPLACE FUNCTION "delete_private_data"() |
jbe@211 | 141 RETURNS VOID |
jbe@211 | 142 LANGUAGE 'plpgsql' VOLATILE AS $$ |
jbe@211 | 143 BEGIN |
jbe@211 | 144 UPDATE "member" SET |
jbe@211 | 145 "invite_code" = NULL, |
jbe@211 | 146 "last_login" = NULL, |
jbe@211 | 147 "login" = NULL, |
jbe@211 | 148 "password" = NULL, |
jbe@211 | 149 "notify_email" = NULL, |
jbe@211 | 150 "notify_email_unconfirmed" = NULL, |
jbe@211 | 151 "notify_email_secret" = NULL, |
jbe@211 | 152 "notify_email_secret_expiry" = NULL, |
jbe@211 | 153 "notify_email_lock_expiry" = NULL, |
jbe@211 | 154 "password_reset_secret" = NULL, |
jbe@211 | 155 "password_reset_secret_expiry" = NULL, |
jbe@211 | 156 "organizational_unit" = NULL, |
jbe@211 | 157 "internal_posts" = NULL, |
jbe@211 | 158 "realname" = NULL, |
jbe@211 | 159 "birthday" = NULL, |
jbe@211 | 160 "address" = NULL, |
jbe@211 | 161 "email" = NULL, |
jbe@211 | 162 "xmpp_address" = NULL, |
jbe@211 | 163 "website" = NULL, |
jbe@211 | 164 "phone" = NULL, |
jbe@211 | 165 "mobile_phone" = NULL, |
jbe@211 | 166 "profession" = NULL, |
jbe@211 | 167 "external_memberships" = NULL, |
jbe@211 | 168 "external_posts" = NULL, |
jbe@211 | 169 "statement" = NULL; |
jbe@211 | 170 -- "text_search_data" is updated by triggers |
jbe@211 | 171 DELETE FROM "setting"; |
jbe@211 | 172 DELETE FROM "setting_map"; |
jbe@211 | 173 DELETE FROM "member_relation_setting"; |
jbe@211 | 174 DELETE FROM "member_image"; |
jbe@211 | 175 DELETE FROM "contact"; |
jbe@211 | 176 DELETE FROM "ignored_member"; |
jbe@211 | 177 DELETE FROM "area_setting"; |
jbe@211 | 178 DELETE FROM "issue_setting"; |
jbe@211 | 179 DELETE FROM "ignored_initiative"; |
jbe@211 | 180 DELETE FROM "initiative_setting"; |
jbe@211 | 181 DELETE FROM "suggestion_setting"; |
jbe@211 | 182 DELETE FROM "non_voter"; |
jbe@211 | 183 DELETE FROM "direct_voter" USING "issue" |
jbe@211 | 184 WHERE "direct_voter"."issue_id" = "issue"."id" |
jbe@211 | 185 AND "issue"."closed" ISNULL; |
jbe@211 | 186 RETURN; |
jbe@211 | 187 END; |
jbe@211 | 188 $$; |
jbe@211 | 189 |
jbe@205 | 190 COMMIT; |