
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;




