1 BEGIN;

2

3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS

4 SELECT * FROM (VALUES ('2.0.1', 2, 0, 1))

5 AS "subquery"("string", "major", "minor", "revision");

6

7 ALTER TABLE "issue" ALTER "state" SET DEFAULT 'admission'; -- fixes wrong update script from v1.3.1 to v1.4.0

8

9 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';

10 COMMENT ON COLUMN "member"."statement" IS 'Freely chosen text of the member for his/her profile';

11

12 COMMENT ON COLUMN "policy"."admission_time" IS 'Maximum duration of issue state ''admission''; Maximum time an issue stays open without being "accepted"';

13 COMMENT ON COLUMN "policy"."discussion_time" IS 'Duration of issue state ''discussion''; Regular time until an issue is "half_frozen" after being "accepted"';

14 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"';

15 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'')';

16 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''';

17 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''';

18

19 COMMENT ON COLUMN "unit"."active" IS 'TRUE means new issues can be created in areas of this area';

20

21 CREATE TABLE "unit_setting" (

22 PRIMARY KEY ("member_id", "key", "unit_id"),

23 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,

24 "key" TEXT NOT NULL,

25 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,

26 "value" TEXT NOT NULL );

27

28 COMMENT ON TABLE "unit_setting" IS 'Place for frontend to store unit specific settings of members as strings';

29

30 COMMENT ON COLUMN "initiative"."discussion_url" IS 'URL pointing to a discussion platform for this initiative';

31 COMMENT ON COLUMN "initiative"."revoked" IS 'Point in time, when one initiator decided to revoke the initiative';

32 COMMENT ON COLUMN "initiative"."revoked_by_member_id" IS 'Member, who decided to revoke the initiative';

33 COMMENT ON COLUMN "initiative"."admitted" IS 'TRUE, if initiative reaches the "initiative_quorum" when freezing the issue';

34 COMMENT ON COLUMN "initiative"."positive_votes" IS 'Calculated from table "direct_voter"';

35 COMMENT ON COLUMN "initiative"."negative_votes" IS 'Calculated from table "direct_voter"';

36 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"';

37 COMMENT ON COLUMN "initiative"."indirect_majority" IS 'Same as "direct_majority", but also considering indirect beat paths';

38 COMMENT ON COLUMN "initiative"."schulze_rank" IS 'Schulze-Ranking without tie-breaking';

39 COMMENT ON COLUMN "initiative"."better_than_status_quo" IS 'TRUE, if initiative has a schulze-ranking better than the status quo (without tie-breaking)';

40 COMMENT ON COLUMN "initiative"."worse_than_status_quo" IS 'TRUE, if initiative has a schulze-ranking worse than the status quo (without tie-breaking)';

41 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';

42 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';

43 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"';

44 COMMENT ON COLUMN "initiative"."winner" IS 'Winner is the "eligible" initiative with best "schulze_rank" and in case of ties with lowest "id"';

45 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';

46

47 COMMENT ON COLUMN "privilege"."admin_manager" IS 'Grant/revoke admin privileges to/from other members';

48 COMMENT ON COLUMN "privilege"."unit_manager" IS 'Create and disable sub units';

49 COMMENT ON COLUMN "privilege"."area_manager" IS 'Create and disable areas and set area parameters';

50 COMMENT ON COLUMN "privilege"."voting_right_manager" IS 'Select which members are allowed to discuss and vote within the unit';

51

52 COMMENT ON COLUMN "supporter"."issue_id" IS 'WARNING: No index: For selections use column "initiative_id" and join via table "initiative" where neccessary';

53

54 ALTER TABLE "direct_supporter_snapshot" ADD COLUMN "draft_id" INT8;

55

56 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";

57 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;

58

59 ALTER TABLE "direct_supporter_snapshot" ADD FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE NO ACTION ON UPDATE CASCADE;

60 ALTER TABLE "direct_supporter_snapshot" ALTER COLUMN "draft_id" SET NOT NULL;

61

62 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';

63

64 COMMENT ON COLUMN "direct_voter"."weight" IS 'Weight of member (1 or higher) according to "delegating_voter" table';

65

66 COMMENT ON COLUMN "vote"."issue_id" IS 'WARNING: No index: For selections use column "initiative_id" and join via table "initiative" where neccessary';

67 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.';

68

69 CREATE OR REPLACE FUNCTION "create_interest_snapshot"

70 ( "issue_id_p" "issue"."id"%TYPE )

71 RETURNS VOID

72 LANGUAGE 'plpgsql' VOLATILE AS $$

73 DECLARE

74 "member_id_v" "member"."id"%TYPE;

75 BEGIN

76 DELETE FROM "direct_interest_snapshot"

77 WHERE "issue_id" = "issue_id_p"

78 AND "event" = 'periodic';

79 DELETE FROM "delegating_interest_snapshot"

80 WHERE "issue_id" = "issue_id_p"

81 AND "event" = 'periodic';

82 DELETE FROM "direct_supporter_snapshot"

83 WHERE "issue_id" = "issue_id_p"

84 AND "event" = 'periodic';

85 INSERT INTO "direct_interest_snapshot"

86 ("issue_id", "event", "member_id")

87 SELECT

88 "issue_id_p" AS "issue_id",

89 'periodic' AS "event",

90 "member"."id" AS "member_id"

91 FROM "issue"

92 JOIN "area" ON "issue"."area_id" = "area"."id"

93 JOIN "interest" ON "issue"."id" = "interest"."issue_id"

94 JOIN "member" ON "interest"."member_id" = "member"."id"

95 JOIN "privilege"

96 ON "privilege"."unit_id" = "area"."unit_id"

97 AND "privilege"."member_id" = "member"."id"

98 WHERE "issue"."id" = "issue_id_p"

99 AND "member"."active" AND "privilege"."voting_right";

100 FOR "member_id_v" IN

101 SELECT "member_id" FROM "direct_interest_snapshot"

102 WHERE "issue_id" = "issue_id_p"

103 AND "event" = 'periodic'

104 LOOP

105 UPDATE "direct_interest_snapshot" SET

106 "weight" = 1 +

107 "weight_of_added_delegations_for_interest_snapshot"(

108 "issue_id_p",

109 "member_id_v",

110 '{}'

111 )

112 WHERE "issue_id" = "issue_id_p"

113 AND "event" = 'periodic'

114 AND "member_id" = "member_id_v";

115 END LOOP;

116 INSERT INTO "direct_supporter_snapshot"

117 ( "issue_id", "initiative_id", "event", "member_id",

118 "draft_id", "informed", "satisfied" )

119 SELECT

120 "issue_id_p" AS "issue_id",

121 "initiative"."id" AS "initiative_id",

122 'periodic' AS "event",

123 "supporter"."member_id" AS "member_id",

124 "supporter"."draft_id" AS "draft_id",

125 "supporter"."draft_id" = "current_draft"."id" AS "informed",

126 NOT EXISTS (

127 SELECT NULL FROM "critical_opinion"

128 WHERE "initiative_id" = "initiative"."id"

129 AND "member_id" = "supporter"."member_id"

130 ) AS "satisfied"

131 FROM "initiative"

132 JOIN "supporter"

133 ON "supporter"."initiative_id" = "initiative"."id"

134 JOIN "current_draft"

135 ON "initiative"."id" = "current_draft"."initiative_id"

136 JOIN "direct_interest_snapshot"

137 ON "supporter"."member_id" = "direct_interest_snapshot"."member_id"

138 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"

139 AND "event" = 'periodic'

140 WHERE "initiative"."issue_id" = "issue_id_p";

141 RETURN;

142 END;

143 $$;

144

145 CREATE OR REPLACE FUNCTION "delete_private_data"()

146 RETURNS VOID

147 LANGUAGE 'plpgsql' VOLATILE AS $$

148 BEGIN

149 UPDATE "member" SET

150 "invite_code" = NULL,

151 "last_login" = NULL,

152 "login" = NULL,

153 "password" = NULL,

154 "notify_email" = NULL,

155 "notify_email_unconfirmed" = NULL,

156 "notify_email_secret" = NULL,

157 "notify_email_secret_expiry" = NULL,

158 "notify_email_lock_expiry" = NULL,

159 "password_reset_secret" = NULL,

160 "password_reset_secret_expiry" = NULL,

161 "organizational_unit" = NULL,

162 "internal_posts" = NULL,

163 "realname" = NULL,

164 "birthday" = NULL,

165 "address" = NULL,

166 "email" = NULL,

167 "xmpp_address" = NULL,

168 "website" = NULL,

169 "phone" = NULL,

170 "mobile_phone" = NULL,

171 "profession" = NULL,

172 "external_memberships" = NULL,

173 "external_posts" = NULL,

174 "statement" = NULL;

175 -- "text_search_data" is updated by triggers

176 DELETE FROM "setting";

177 DELETE FROM "setting_map";

178 DELETE FROM "member_relation_setting";

179 DELETE FROM "member_image";

180 DELETE FROM "contact";

181 DELETE FROM "ignored_member";

182 DELETE FROM "area_setting";

183 DELETE FROM "issue_setting";

184 DELETE FROM "ignored_initiative";

185 DELETE FROM "initiative_setting";

186 DELETE FROM "suggestion_setting";

187 DELETE FROM "non_voter";

188 DELETE FROM "direct_voter" USING "issue"

189 WHERE "direct_voter"."issue_id" = "issue"."id"

190 AND "issue"."closed" ISNULL;

191 RETURN;

192 END;

193 $$;

194

195 COMMIT;