jbe@205: BEGIN; jbe@205: jbe@212: -- update version number: jbe@211: CREATE OR REPLACE VIEW "liquid_feedback_version" AS jbe@211: SELECT * FROM (VALUES ('2.0.1', 2, 0, 1)) jbe@211: AS "subquery"("string", "major", "minor", "revision"); jbe@205: jbe@212: -- fix errors in update script from v1.3.1 to v1.4.0: jbe@212: ALTER TABLE "issue" ALTER "state" SET DEFAULT 'admission'; jbe@205: jbe@212: -- change comments in "member" table: jbe@211: 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: COMMENT ON COLUMN "member"."statement" IS 'Freely chosen text of the member for his/her profile'; jbe@211: jbe@212: -- change comments in "policy" table: jbe@211: COMMENT ON COLUMN "policy"."admission_time" IS 'Maximum duration of issue state ''admission''; Maximum time an issue stays open without being "accepted"'; jbe@211: 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: 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: 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: 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: 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: jbe@212: -- change comment in "unit" table: jbe@212: COMMENT ON COLUMN "unit"."active" IS 'TRUE means new issues can be created in areas of this unit'; jbe@211: jbe@212: -- new table "unit_setting": jbe@211: CREATE TABLE "unit_setting" ( jbe@211: PRIMARY KEY ("member_id", "key", "unit_id"), jbe@211: "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE, jbe@211: "key" TEXT NOT NULL, jbe@211: "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE, jbe@211: "value" TEXT NOT NULL ); jbe@211: COMMENT ON TABLE "unit_setting" IS 'Place for frontend to store unit specific settings of members as strings'; jbe@211: jbe@212: -- change comments in "initiative" table: jbe@212: COMMENT ON COLUMN "initiative"."revoked" IS 'Point in time, when one initiator decided to revoke the initiative'; jbe@212: 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: 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: jbe@212: -- change comments in "privilege" table: jbe@211: COMMENT ON COLUMN "privilege"."admin_manager" IS 'Grant/revoke admin privileges to/from other members'; jbe@211: COMMENT ON COLUMN "privilege"."unit_manager" IS 'Create and disable sub units'; jbe@211: COMMENT ON COLUMN "privilege"."area_manager" IS 'Create and disable areas and set area parameters'; jbe@211: COMMENT ON COLUMN "privilege"."voting_right_manager" IS 'Select which members are allowed to discuss and vote within the unit'; jbe@211: jbe@212: -- add comment to "supporter" table: jbe@211: 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: jbe@212: -- add column "draft_id" to table "direct_supporter_snapshot": jbe@211: ALTER TABLE "direct_supporter_snapshot" ADD COLUMN "draft_id" INT8; jbe@211: 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: 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: 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: ALTER TABLE "direct_supporter_snapshot" ALTER COLUMN "draft_id" SET NOT NULL; jbe@211: jbe@212: -- add comment to "direct_supporter_snapshot" table: jbe@212: 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: jbe@212: -- add comment to "vote" table: jbe@211: 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: jbe@212: -- change function "create_interest_snapshot" to set "direct_supporter_snapshot"."draft_id": jbe@211: CREATE OR REPLACE FUNCTION "create_interest_snapshot" jbe@211: ( "issue_id_p" "issue"."id"%TYPE ) jbe@211: RETURNS VOID jbe@211: LANGUAGE 'plpgsql' VOLATILE AS $$ jbe@211: DECLARE jbe@211: "member_id_v" "member"."id"%TYPE; jbe@211: BEGIN jbe@211: DELETE FROM "direct_interest_snapshot" jbe@211: WHERE "issue_id" = "issue_id_p" jbe@211: AND "event" = 'periodic'; jbe@211: DELETE FROM "delegating_interest_snapshot" jbe@211: WHERE "issue_id" = "issue_id_p" jbe@211: AND "event" = 'periodic'; jbe@211: DELETE FROM "direct_supporter_snapshot" jbe@211: WHERE "issue_id" = "issue_id_p" jbe@211: AND "event" = 'periodic'; jbe@211: INSERT INTO "direct_interest_snapshot" jbe@211: ("issue_id", "event", "member_id") jbe@211: SELECT jbe@211: "issue_id_p" AS "issue_id", jbe@211: 'periodic' AS "event", jbe@211: "member"."id" AS "member_id" jbe@211: FROM "issue" jbe@211: JOIN "area" ON "issue"."area_id" = "area"."id" jbe@211: JOIN "interest" ON "issue"."id" = "interest"."issue_id" jbe@211: JOIN "member" ON "interest"."member_id" = "member"."id" jbe@211: JOIN "privilege" jbe@211: ON "privilege"."unit_id" = "area"."unit_id" jbe@211: AND "privilege"."member_id" = "member"."id" jbe@211: WHERE "issue"."id" = "issue_id_p" jbe@211: AND "member"."active" AND "privilege"."voting_right"; jbe@211: FOR "member_id_v" IN jbe@211: SELECT "member_id" FROM "direct_interest_snapshot" jbe@211: WHERE "issue_id" = "issue_id_p" jbe@211: AND "event" = 'periodic' jbe@211: LOOP jbe@211: UPDATE "direct_interest_snapshot" SET jbe@211: "weight" = 1 + jbe@211: "weight_of_added_delegations_for_interest_snapshot"( jbe@211: "issue_id_p", jbe@211: "member_id_v", jbe@211: '{}' jbe@211: ) jbe@211: WHERE "issue_id" = "issue_id_p" jbe@211: AND "event" = 'periodic' jbe@211: AND "member_id" = "member_id_v"; jbe@211: END LOOP; jbe@211: INSERT INTO "direct_supporter_snapshot" jbe@211: ( "issue_id", "initiative_id", "event", "member_id", jbe@211: "draft_id", "informed", "satisfied" ) jbe@211: SELECT jbe@211: "issue_id_p" AS "issue_id", jbe@211: "initiative"."id" AS "initiative_id", jbe@211: 'periodic' AS "event", jbe@211: "supporter"."member_id" AS "member_id", jbe@211: "supporter"."draft_id" AS "draft_id", jbe@211: "supporter"."draft_id" = "current_draft"."id" AS "informed", jbe@211: NOT EXISTS ( jbe@211: SELECT NULL FROM "critical_opinion" jbe@211: WHERE "initiative_id" = "initiative"."id" jbe@211: AND "member_id" = "supporter"."member_id" jbe@211: ) AS "satisfied" jbe@211: FROM "initiative" jbe@211: JOIN "supporter" jbe@211: ON "supporter"."initiative_id" = "initiative"."id" jbe@211: JOIN "current_draft" jbe@211: ON "initiative"."id" = "current_draft"."initiative_id" jbe@211: JOIN "direct_interest_snapshot" jbe@211: ON "supporter"."member_id" = "direct_interest_snapshot"."member_id" jbe@211: AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id" jbe@211: AND "event" = 'periodic' jbe@211: WHERE "initiative"."issue_id" = "issue_id_p"; jbe@211: RETURN; jbe@211: END; jbe@211: $$; jbe@211: jbe@212: -- change function "delete_private_data" to delete invite codes in "member" instead of "invite_code" table: jbe@211: CREATE OR REPLACE FUNCTION "delete_private_data"() jbe@211: RETURNS VOID jbe@211: LANGUAGE 'plpgsql' VOLATILE AS $$ jbe@211: BEGIN jbe@211: UPDATE "member" SET jbe@211: "invite_code" = NULL, jbe@211: "last_login" = NULL, jbe@211: "login" = NULL, jbe@211: "password" = NULL, jbe@211: "notify_email" = NULL, jbe@211: "notify_email_unconfirmed" = NULL, jbe@211: "notify_email_secret" = NULL, jbe@211: "notify_email_secret_expiry" = NULL, jbe@211: "notify_email_lock_expiry" = NULL, jbe@211: "password_reset_secret" = NULL, jbe@211: "password_reset_secret_expiry" = NULL, jbe@211: "organizational_unit" = NULL, jbe@211: "internal_posts" = NULL, jbe@211: "realname" = NULL, jbe@211: "birthday" = NULL, jbe@211: "address" = NULL, jbe@211: "email" = NULL, jbe@211: "xmpp_address" = NULL, jbe@211: "website" = NULL, jbe@211: "phone" = NULL, jbe@211: "mobile_phone" = NULL, jbe@211: "profession" = NULL, jbe@211: "external_memberships" = NULL, jbe@211: "external_posts" = NULL, jbe@211: "statement" = NULL; jbe@211: -- "text_search_data" is updated by triggers jbe@211: DELETE FROM "setting"; jbe@211: DELETE FROM "setting_map"; jbe@211: DELETE FROM "member_relation_setting"; jbe@211: DELETE FROM "member_image"; jbe@211: DELETE FROM "contact"; jbe@211: DELETE FROM "ignored_member"; jbe@211: DELETE FROM "area_setting"; jbe@211: DELETE FROM "issue_setting"; jbe@211: DELETE FROM "ignored_initiative"; jbe@211: DELETE FROM "initiative_setting"; jbe@211: DELETE FROM "suggestion_setting"; jbe@211: DELETE FROM "non_voter"; jbe@211: DELETE FROM "direct_voter" USING "issue" jbe@211: WHERE "direct_voter"."issue_id" = "issue"."id" jbe@211: AND "issue"."closed" ISNULL; jbe@211: RETURN; jbe@211: END; jbe@211: $$; jbe@211: jbe@205: COMMIT;