1.1 --- a/core.sql	Sat Jan 21 01:47:09 2012 +0100
     1.2 +++ b/core.sql	Sat Jan 28 23:06:03 2012 +0100
     1.3 @@ -375,7 +375,7 @@
     1.4  COMMENT ON TABLE "unit" IS 'Organizational units organized as trees; Delegations are not inherited through these trees.';
     1.5  
     1.6  COMMENT ON COLUMN "unit"."parent_id"    IS 'Parent id of tree node; Multiple roots allowed';
     1.7 -COMMENT ON COLUMN "unit"."active"       IS 'TRUE means new issues can be created in areas of this area';
     1.8 +COMMENT ON COLUMN "unit"."active"       IS 'TRUE means new issues can be created in areas of this unit';
     1.9  COMMENT ON COLUMN "unit"."member_count" IS 'Count of members as determined by column "voting_right" in table "privilege"';
    1.10  
    1.11  

     2.1 --- a/update/core-update.v2.0.0-v2.0.1.sql	Sat Jan 21 01:47:09 2012 +0100
     2.2 +++ b/update/core-update.v2.0.0-v2.0.1.sql	Sat Jan 28 23:06:03 2012 +0100
     2.3 @@ -1,14 +1,18 @@
     2.4  BEGIN;
     2.5  
     2.6 +-- update version number:
     2.7  CREATE OR REPLACE VIEW "liquid_feedback_version" AS
     2.8    SELECT * FROM (VALUES ('2.0.1', 2, 0, 1))
     2.9    AS "subquery"("string", "major", "minor", "revision");
    2.10  
    2.11 -ALTER TABLE "issue" ALTER "state" SET DEFAULT 'admission';  -- fixes wrong update script from v1.3.1 to v1.4.0
    2.12 +-- fix errors in update script from v1.3.1 to v1.4.0:
    2.13 +ALTER TABLE "issue" ALTER "state" SET DEFAULT 'admission';
    2.14  
    2.15 +-- change comments in "member" table:
    2.16  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';
    2.17  COMMENT ON COLUMN "member"."statement" IS 'Freely chosen text of the member for his/her profile';
    2.18  
    2.19 +-- change comments in "policy" table:
    2.20  COMMENT ON COLUMN "policy"."admission_time"    IS 'Maximum duration of issue state ''admission''; Maximum time an issue stays open without being "accepted"';
    2.21  COMMENT ON COLUMN "policy"."discussion_time"   IS 'Duration of issue state ''discussion''; Regular time until an issue is "half_frozen" after being "accepted"';
    2.22  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"';
    2.23 @@ -16,56 +20,46 @@
    2.24  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''';
    2.25  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''';
    2.26  
    2.27 -COMMENT ON COLUMN "unit"."active" IS 'TRUE means new issues can be created in areas of this area';
    2.28 +-- change comment in "unit" table:
    2.29 +COMMENT ON COLUMN "unit"."active" IS 'TRUE means new issues can be created in areas of this unit';
    2.30  
    2.31 +-- new table "unit_setting":
    2.32  CREATE TABLE "unit_setting" (
    2.33          PRIMARY KEY ("member_id", "key", "unit_id"),
    2.34          "member_id"             INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    2.35          "key"                   TEXT            NOT NULL,
    2.36          "unit_id"               INT4            REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    2.37          "value"                 TEXT            NOT NULL );
    2.38 -
    2.39  COMMENT ON TABLE "unit_setting" IS 'Place for frontend to store unit specific settings of members as strings';
    2.40  
    2.41 -COMMENT ON COLUMN "initiative"."discussion_url"         IS 'URL pointing to a discussion platform for this initiative';
    2.42 -COMMENT ON COLUMN "initiative"."revoked"                IS 'Point in time, when one initiator decided to revoke the initiative';
    2.43 -COMMENT ON COLUMN "initiative"."revoked_by_member_id"   IS 'Member, who decided to revoke the initiative';
    2.44 -COMMENT ON COLUMN "initiative"."admitted"               IS 'TRUE, if initiative reaches the "initiative_quorum" when freezing the issue';
    2.45 -COMMENT ON COLUMN "initiative"."positive_votes"         IS 'Calculated from table "direct_voter"';
    2.46 -COMMENT ON COLUMN "initiative"."negative_votes"         IS 'Calculated from table "direct_voter"';
    2.47 -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"';
    2.48 -COMMENT ON COLUMN "initiative"."indirect_majority"      IS 'Same as "direct_majority", but also considering indirect beat paths';
    2.49 -COMMENT ON COLUMN "initiative"."schulze_rank"           IS 'Schulze-Ranking without tie-breaking';
    2.50 -COMMENT ON COLUMN "initiative"."better_than_status_quo" IS 'TRUE, if initiative has a schulze-ranking better than the status quo (without tie-breaking)';
    2.51 -COMMENT ON COLUMN "initiative"."worse_than_status_quo"  IS 'TRUE, if initiative has a schulze-ranking worse than the status quo (without tie-breaking)';
    2.52 -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';
    2.53 -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';
    2.54 -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"';
    2.55 -COMMENT ON COLUMN "initiative"."winner"                 IS 'Winner is the "eligible" initiative with best "schulze_rank" and in case of ties with lowest "id"';
    2.56 -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';
    2.57 +-- change comments in "initiative" table:
    2.58 +COMMENT ON COLUMN "initiative"."revoked"           IS 'Point in time, when one initiator decided to revoke the initiative';
    2.59 +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';
    2.60 +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"';
    2.61  
    2.62 +-- change comments in "privilege" table:
    2.63  COMMENT ON COLUMN "privilege"."admin_manager"        IS 'Grant/revoke admin privileges to/from other members';
    2.64  COMMENT ON COLUMN "privilege"."unit_manager"         IS 'Create and disable sub units';
    2.65  COMMENT ON COLUMN "privilege"."area_manager"         IS 'Create and disable areas and set area parameters';
    2.66  COMMENT ON COLUMN "privilege"."voting_right_manager" IS 'Select which members are allowed to discuss and vote within the unit';
    2.67  
    2.68 +-- add comment to "supporter" table:
    2.69  COMMENT ON COLUMN "supporter"."issue_id" IS 'WARNING: No index: For selections use column "initiative_id" and join via table "initiative" where neccessary';
    2.70  
    2.71 +-- add column "draft_id" to table "direct_supporter_snapshot":
    2.72  ALTER TABLE "direct_supporter_snapshot" ADD COLUMN "draft_id" INT8;
    2.73 -
    2.74  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";
    2.75  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;
    2.76 -
    2.77  ALTER TABLE "direct_supporter_snapshot" ADD FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE NO ACTION ON UPDATE CASCADE;
    2.78  ALTER TABLE "direct_supporter_snapshot" ALTER COLUMN "draft_id" SET NOT NULL;
    2.79  
    2.80 -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';
    2.81 -
    2.82 -COMMENT ON COLUMN "direct_voter"."weight" IS 'Weight of member (1 or higher) according to "delegating_voter" table';
    2.83 +-- add comment to "direct_supporter_snapshot" table:
    2.84 +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';
    2.85  
    2.86 +-- add comment to "vote" table:
    2.87  COMMENT ON COLUMN "vote"."issue_id" IS 'WARNING: No index: For selections use column "initiative_id" and join via table "initiative" where neccessary';
    2.88 -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.';
    2.89  
    2.90 +-- change function "create_interest_snapshot" to set "direct_supporter_snapshot"."draft_id":
    2.91  CREATE OR REPLACE FUNCTION "create_interest_snapshot"
    2.92    ( "issue_id_p" "issue"."id"%TYPE )
    2.93    RETURNS VOID
    2.94 @@ -142,6 +136,7 @@
    2.95      END;
    2.96    $$;
    2.97  
    2.98 +-- change function "delete_private_data" to delete invite codes in "member" instead of "invite_code" table:
    2.99  CREATE OR REPLACE FUNCTION "delete_private_data"()
   2.100    RETURNS VOID
   2.101    LANGUAGE 'plpgsql' VOLATILE AS $$