1 BEGIN;

2

3 -- update version number:

4 CREATE OR REPLACE VIEW "liquid_feedback_version" AS

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

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

7

8 -- fix errors in update script from v1.3.1 to v1.4.0:

9 ALTER TABLE "issue" ALTER "state" SET DEFAULT 'admission';

10

11 -- change comments in "member" table:

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

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

14

15 -- change comments in "policy" table:

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

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

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

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

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

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

22

23 -- change comment in "unit" table:

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

25

26 -- new table "unit_setting":

27 CREATE TABLE "unit_setting" (

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

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

30 "key" TEXT NOT NULL,

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

32 "value" TEXT NOT NULL );

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

34

35 -- change comments in "initiative" table:

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

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

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

39

40 -- change comments in "privilege" table:

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

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

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

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

45

46 -- add comment to "supporter" table:

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

48

49 -- add column "draft_id" to table "direct_supporter_snapshot":

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

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

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

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

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

55

56 -- add comment to "direct_supporter_snapshot" table:

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

58

59 -- add comment to "vote" table:

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

61

62 -- change function "create_interest_snapshot" to set "direct_supporter_snapshot"."draft_id":

63 CREATE OR REPLACE FUNCTION "create_interest_snapshot"

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

65 RETURNS VOID

66 LANGUAGE 'plpgsql' VOLATILE AS $$

67 DECLARE

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

69 BEGIN

70 DELETE FROM "direct_interest_snapshot"

71 WHERE "issue_id" = "issue_id_p"

72 AND "event" = 'periodic';

73 DELETE FROM "delegating_interest_snapshot"

74 WHERE "issue_id" = "issue_id_p"

75 AND "event" = 'periodic';

76 DELETE FROM "direct_supporter_snapshot"

77 WHERE "issue_id" = "issue_id_p"

78 AND "event" = 'periodic';

79 INSERT INTO "direct_interest_snapshot"

80 ("issue_id", "event", "member_id")

81 SELECT

82 "issue_id_p" AS "issue_id",

83 'periodic' AS "event",

84 "member"."id" AS "member_id"

85 FROM "issue"

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

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

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

89 JOIN "privilege"

90 ON "privilege"."unit_id" = "area"."unit_id"

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

92 WHERE "issue"."id" = "issue_id_p"

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

94 FOR "member_id_v" IN

95 SELECT "member_id" FROM "direct_interest_snapshot"

96 WHERE "issue_id" = "issue_id_p"

97 AND "event" = 'periodic'

98 LOOP

99 UPDATE "direct_interest_snapshot" SET

100 "weight" = 1 +

101 "weight_of_added_delegations_for_interest_snapshot"(

102 "issue_id_p",

103 "member_id_v",

104 '{}'

105 )

106 WHERE "issue_id" = "issue_id_p"

107 AND "event" = 'periodic'

108 AND "member_id" = "member_id_v";

109 END LOOP;

110 INSERT INTO "direct_supporter_snapshot"

111 ( "issue_id", "initiative_id", "event", "member_id",

112 "draft_id", "informed", "satisfied" )

113 SELECT

114 "issue_id_p" AS "issue_id",

115 "initiative"."id" AS "initiative_id",

116 'periodic' AS "event",

117 "supporter"."member_id" AS "member_id",

118 "supporter"."draft_id" AS "draft_id",

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

120 NOT EXISTS (

121 SELECT NULL FROM "critical_opinion"

122 WHERE "initiative_id" = "initiative"."id"

123 AND "member_id" = "supporter"."member_id"

124 ) AS "satisfied"

125 FROM "initiative"

126 JOIN "supporter"

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

128 JOIN "current_draft"

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

130 JOIN "direct_interest_snapshot"

131 ON "supporter"."member_id" = "direct_interest_snapshot"."member_id"

132 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"

133 AND "event" = 'periodic'

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

135 RETURN;

136 END;

137 $$;

138

139 -- change function "delete_private_data" to delete invite codes in "member" instead of "invite_code" table:

140 CREATE OR REPLACE FUNCTION "delete_private_data"()

141 RETURNS VOID

142 LANGUAGE 'plpgsql' VOLATILE AS $$

143 BEGIN

144 UPDATE "member" SET

145 "invite_code" = NULL,

146 "last_login" = NULL,

147 "login" = NULL,

148 "password" = NULL,

149 "notify_email" = NULL,

150 "notify_email_unconfirmed" = NULL,

151 "notify_email_secret" = NULL,

152 "notify_email_secret_expiry" = NULL,

153 "notify_email_lock_expiry" = NULL,

154 "password_reset_secret" = NULL,

155 "password_reset_secret_expiry" = NULL,

156 "organizational_unit" = NULL,

157 "internal_posts" = NULL,

158 "realname" = NULL,

159 "birthday" = NULL,

160 "address" = NULL,

161 "email" = NULL,

162 "xmpp_address" = NULL,

163 "website" = NULL,

164 "phone" = NULL,

165 "mobile_phone" = NULL,

166 "profession" = NULL,

167 "external_memberships" = NULL,

168 "external_posts" = NULL,

169 "statement" = NULL;

170 -- "text_search_data" is updated by triggers

171 DELETE FROM "setting";

172 DELETE FROM "setting_map";

173 DELETE FROM "member_relation_setting";

174 DELETE FROM "member_image";

175 DELETE FROM "contact";

176 DELETE FROM "ignored_member";

177 DELETE FROM "area_setting";

178 DELETE FROM "issue_setting";

179 DELETE FROM "ignored_initiative";

180 DELETE FROM "initiative_setting";

181 DELETE FROM "suggestion_setting";

182 DELETE FROM "non_voter";

183 DELETE FROM "direct_voter" USING "issue"

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

185 AND "issue"."closed" ISNULL;

186 RETURN;

187 END;

188 $$;

189

190 COMMIT;