liquid_feedback_core

changeset 211:b52a65f5b1d3

Update script from v2.0.0 to v2.0.1
author jbe
date Sat Jan 21 01:47:09 2012 +0100 (2012-01-21)
parents c8c5ac91f30a
children c23b288fd771
files update/core-update.v2.0.0-v2.0.1.sql
line diff
     1.1 --- a/update/core-update.v2.0.0-v2.0.1.sql	Tue Dec 27 21:19:14 2011 +0100
     1.2 +++ b/update/core-update.v2.0.0-v2.0.1.sql	Sat Jan 21 01:47:09 2012 +0100
     1.3 @@ -1,7 +1,195 @@
     1.4  BEGIN;
     1.5  
     1.6 --- work in progress
     1.7 +CREATE OR REPLACE VIEW "liquid_feedback_version" AS
     1.8 +  SELECT * FROM (VALUES ('2.0.1', 2, 0, 1))
     1.9 +  AS "subquery"("string", "major", "minor", "revision");
    1.10  
    1.11  ALTER TABLE "issue" ALTER "state" SET DEFAULT 'admission';  -- fixes wrong update script from v1.3.1 to v1.4.0
    1.12  
    1.13 +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';
    1.14 +COMMENT ON COLUMN "member"."statement" IS 'Freely chosen text of the member for his/her profile';
    1.15 +
    1.16 +COMMENT ON COLUMN "policy"."admission_time"    IS 'Maximum duration of issue state ''admission''; Maximum time an issue stays open without being "accepted"';
    1.17 +COMMENT ON COLUMN "policy"."discussion_time"   IS 'Duration of issue state ''discussion''; Regular time until an issue is "half_frozen" after being "accepted"';
    1.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"';
    1.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'')';
    1.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''';
    1.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''';
    1.22 +
    1.23 +COMMENT ON COLUMN "unit"."active" IS 'TRUE means new issues can be created in areas of this area';
    1.24 +
    1.25 +CREATE TABLE "unit_setting" (
    1.26 +        PRIMARY KEY ("member_id", "key", "unit_id"),
    1.27 +        "member_id"             INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    1.28 +        "key"                   TEXT            NOT NULL,
    1.29 +        "unit_id"               INT4            REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    1.30 +        "value"                 TEXT            NOT NULL );
    1.31 +
    1.32 +COMMENT ON TABLE "unit_setting" IS 'Place for frontend to store unit specific settings of members as strings';
    1.33 +
    1.34 +COMMENT ON COLUMN "initiative"."discussion_url"         IS 'URL pointing to a discussion platform for this initiative';
    1.35 +COMMENT ON COLUMN "initiative"."revoked"                IS 'Point in time, when one initiator decided to revoke the initiative';
    1.36 +COMMENT ON COLUMN "initiative"."revoked_by_member_id"   IS 'Member, who decided to revoke the initiative';
    1.37 +COMMENT ON COLUMN "initiative"."admitted"               IS 'TRUE, if initiative reaches the "initiative_quorum" when freezing the issue';
    1.38 +COMMENT ON COLUMN "initiative"."positive_votes"         IS 'Calculated from table "direct_voter"';
    1.39 +COMMENT ON COLUMN "initiative"."negative_votes"         IS 'Calculated from table "direct_voter"';
    1.40 +COMMENT ON COLUMN "initiative"."direct_majority"        IS 'TRUE, if "positive_votes"/("positive_votes"+"negative_votes") is strictly greater or greater-equal than "direct_majority_num"/"direct_majority_den", and "positive_votes" is greater-equal than "direct_majority_positive", and ("positive_votes"+abstentions) is greater-equal than "direct_majority_non_negative"';
    1.41 +COMMENT ON COLUMN "initiative"."indirect_majority"      IS 'Same as "direct_majority", but also considering indirect beat paths';
    1.42 +COMMENT ON COLUMN "initiative"."schulze_rank"           IS 'Schulze-Ranking without tie-breaking';
    1.43 +COMMENT ON COLUMN "initiative"."better_than_status_quo" IS 'TRUE, if initiative has a schulze-ranking better than the status quo (without tie-breaking)';
    1.44 +COMMENT ON COLUMN "initiative"."worse_than_status_quo"  IS 'TRUE, if initiative has a schulze-ranking worse than the status quo (without tie-breaking)';
    1.45 +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';
    1.46 +COMMENT ON COLUMN "initiative"."multistage_majority"    IS 'TRUE, if either (a) this initiative has no better rank than the status quo, or (b) there exists a better ranked initiative X, which directly beats this initiative, and either more voters prefer X to this initiative than voters preferring X to the status quo or less voters prefer this initiative to X than voters preferring the status quo to X';
    1.47 +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"';
    1.48 +COMMENT ON COLUMN "initiative"."winner"                 IS 'Winner is the "eligible" initiative with best "schulze_rank" and in case of ties with lowest "id"';
    1.49 +COMMENT ON COLUMN "initiative"."rank"                   IS 'Unique ranking for all "admitted" initiatives per issue; lower rank is better; a winner always has rank 1, but rank 1 does not imply that an initiative is winner; initiatives with "direct_majority" AND "indirect_majority" always have a better (lower) rank than other initiatives';
    1.50 +
    1.51 +COMMENT ON COLUMN "privilege"."admin_manager"        IS 'Grant/revoke admin privileges to/from other members';
    1.52 +COMMENT ON COLUMN "privilege"."unit_manager"         IS 'Create and disable sub units';
    1.53 +COMMENT ON COLUMN "privilege"."area_manager"         IS 'Create and disable areas and set area parameters';
    1.54 +COMMENT ON COLUMN "privilege"."voting_right_manager" IS 'Select which members are allowed to discuss and vote within the unit';
    1.55 +
    1.56 +COMMENT ON COLUMN "supporter"."issue_id" IS 'WARNING: No index: For selections use column "initiative_id" and join via table "initiative" where neccessary';
    1.57 +
    1.58 +ALTER TABLE "direct_supporter_snapshot" ADD COLUMN "draft_id" INT8;
    1.59 +
    1.60 +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";
    1.61 +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;
    1.62 +
    1.63 +ALTER TABLE "direct_supporter_snapshot" ADD FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE NO ACTION ON UPDATE CASCADE;
    1.64 +ALTER TABLE "direct_supporter_snapshot" ALTER COLUMN "draft_id" SET NOT NULL;
    1.65 +
    1.66 +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';
    1.67 +
    1.68 +COMMENT ON COLUMN "direct_voter"."weight" IS 'Weight of member (1 or higher) according to "delegating_voter" table';
    1.69 +
    1.70 +COMMENT ON COLUMN "vote"."issue_id" IS 'WARNING: No index: For selections use column "initiative_id" and join via table "initiative" where neccessary';
    1.71 +COMMENT ON COLUMN "vote"."grade"    IS 'Values smaller than zero mean reject, values greater than zero mean acceptance, zero or missing row means abstention. Preferences are expressed by different positive or negative numbers.';
    1.72 +
    1.73 +CREATE OR REPLACE FUNCTION "create_interest_snapshot"
    1.74 +  ( "issue_id_p" "issue"."id"%TYPE )
    1.75 +  RETURNS VOID
    1.76 +  LANGUAGE 'plpgsql' VOLATILE AS $$
    1.77 +    DECLARE
    1.78 +      "member_id_v" "member"."id"%TYPE;
    1.79 +    BEGIN
    1.80 +      DELETE FROM "direct_interest_snapshot"
    1.81 +        WHERE "issue_id" = "issue_id_p"
    1.82 +        AND "event" = 'periodic';
    1.83 +      DELETE FROM "delegating_interest_snapshot"
    1.84 +        WHERE "issue_id" = "issue_id_p"
    1.85 +        AND "event" = 'periodic';
    1.86 +      DELETE FROM "direct_supporter_snapshot"
    1.87 +        WHERE "issue_id" = "issue_id_p"
    1.88 +        AND "event" = 'periodic';
    1.89 +      INSERT INTO "direct_interest_snapshot"
    1.90 +        ("issue_id", "event", "member_id")
    1.91 +        SELECT
    1.92 +          "issue_id_p"  AS "issue_id",
    1.93 +          'periodic'    AS "event",
    1.94 +          "member"."id" AS "member_id"
    1.95 +        FROM "issue"
    1.96 +        JOIN "area" ON "issue"."area_id" = "area"."id"
    1.97 +        JOIN "interest" ON "issue"."id" = "interest"."issue_id"
    1.98 +        JOIN "member" ON "interest"."member_id" = "member"."id"
    1.99 +        JOIN "privilege"
   1.100 +          ON "privilege"."unit_id" = "area"."unit_id"
   1.101 +          AND "privilege"."member_id" = "member"."id"
   1.102 +        WHERE "issue"."id" = "issue_id_p"
   1.103 +        AND "member"."active" AND "privilege"."voting_right";
   1.104 +      FOR "member_id_v" IN
   1.105 +        SELECT "member_id" FROM "direct_interest_snapshot"
   1.106 +        WHERE "issue_id" = "issue_id_p"
   1.107 +        AND "event" = 'periodic'
   1.108 +      LOOP
   1.109 +        UPDATE "direct_interest_snapshot" SET
   1.110 +          "weight" = 1 +
   1.111 +            "weight_of_added_delegations_for_interest_snapshot"(
   1.112 +              "issue_id_p",
   1.113 +              "member_id_v",
   1.114 +              '{}'
   1.115 +            )
   1.116 +          WHERE "issue_id" = "issue_id_p"
   1.117 +          AND "event" = 'periodic'
   1.118 +          AND "member_id" = "member_id_v";
   1.119 +      END LOOP;
   1.120 +       INSERT INTO "direct_supporter_snapshot"
   1.121 +        ( "issue_id", "initiative_id", "event", "member_id",
   1.122 +          "draft_id", "informed", "satisfied" )
   1.123 +        SELECT
   1.124 +          "issue_id_p"            AS "issue_id",
   1.125 +          "initiative"."id"       AS "initiative_id",
   1.126 +          'periodic'              AS "event",
   1.127 +          "supporter"."member_id" AS "member_id",
   1.128 +          "supporter"."draft_id"  AS "draft_id",
   1.129 +          "supporter"."draft_id" = "current_draft"."id" AS "informed",
   1.130 +          NOT EXISTS (
   1.131 +            SELECT NULL FROM "critical_opinion"
   1.132 +            WHERE "initiative_id" = "initiative"."id"
   1.133 +            AND "member_id" = "supporter"."member_id"
   1.134 +          ) AS "satisfied"
   1.135 +        FROM "initiative"
   1.136 +        JOIN "supporter"
   1.137 +        ON "supporter"."initiative_id" = "initiative"."id"
   1.138 +        JOIN "current_draft"
   1.139 +        ON "initiative"."id" = "current_draft"."initiative_id"
   1.140 +        JOIN "direct_interest_snapshot"
   1.141 +        ON "supporter"."member_id" = "direct_interest_snapshot"."member_id"
   1.142 +        AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
   1.143 +        AND "event" = 'periodic'
   1.144 +        WHERE "initiative"."issue_id" = "issue_id_p";
   1.145 +      RETURN;
   1.146 +    END;
   1.147 +  $$;
   1.148 +
   1.149 +CREATE OR REPLACE FUNCTION "delete_private_data"()
   1.150 +  RETURNS VOID
   1.151 +  LANGUAGE 'plpgsql' VOLATILE AS $$
   1.152 +    BEGIN
   1.153 +      UPDATE "member" SET
   1.154 +        "invite_code"                  = NULL,
   1.155 +        "last_login"                   = NULL,
   1.156 +        "login"                        = NULL,
   1.157 +        "password"                     = NULL,
   1.158 +        "notify_email"                 = NULL,
   1.159 +        "notify_email_unconfirmed"     = NULL,
   1.160 +        "notify_email_secret"          = NULL,
   1.161 +        "notify_email_secret_expiry"   = NULL,
   1.162 +        "notify_email_lock_expiry"     = NULL,
   1.163 +        "password_reset_secret"        = NULL,
   1.164 +        "password_reset_secret_expiry" = NULL,
   1.165 +        "organizational_unit"          = NULL,
   1.166 +        "internal_posts"               = NULL,
   1.167 +        "realname"                     = NULL,
   1.168 +        "birthday"                     = NULL,
   1.169 +        "address"                      = NULL,
   1.170 +        "email"                        = NULL,
   1.171 +        "xmpp_address"                 = NULL,
   1.172 +        "website"                      = NULL,
   1.173 +        "phone"                        = NULL,
   1.174 +        "mobile_phone"                 = NULL,
   1.175 +        "profession"                   = NULL,
   1.176 +        "external_memberships"         = NULL,
   1.177 +        "external_posts"               = NULL,
   1.178 +        "statement"                    = NULL;
   1.179 +      -- "text_search_data" is updated by triggers
   1.180 +      DELETE FROM "setting";
   1.181 +      DELETE FROM "setting_map";
   1.182 +      DELETE FROM "member_relation_setting";
   1.183 +      DELETE FROM "member_image";
   1.184 +      DELETE FROM "contact";
   1.185 +      DELETE FROM "ignored_member";
   1.186 +      DELETE FROM "area_setting";
   1.187 +      DELETE FROM "issue_setting";
   1.188 +      DELETE FROM "ignored_initiative";
   1.189 +      DELETE FROM "initiative_setting";
   1.190 +      DELETE FROM "suggestion_setting";
   1.191 +      DELETE FROM "non_voter";
   1.192 +      DELETE FROM "direct_voter" USING "issue"
   1.193 +        WHERE "direct_voter"."issue_id" = "issue"."id"
   1.194 +        AND "issue"."closed" ISNULL;
   1.195 +      RETURN;
   1.196 +    END;
   1.197 +  $$;
   1.198 +
   1.199  COMMIT;

Impressum / About Us