1 BEGIN;

2

3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS

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

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

6

7 ALTER TABLE "policy" ADD COLUMN "polling" BOOLEAN NOT NULL DEFAULT FALSE;

8 ALTER TABLE "policy" ALTER COLUMN "admission_time" DROP NOT NULL;

9 ALTER TABLE "policy" ALTER COLUMN "discussion_time" DROP NOT NULL;

10 ALTER TABLE "policy" ALTER COLUMN "verification_time" DROP NOT NULL;

11 ALTER TABLE "policy" ALTER COLUMN "voting_time" DROP NOT NULL;

12 ALTER TABLE "policy" ADD CONSTRAINT "timing" CHECK (

13 ( "polling" = FALSE AND

14 "admission_time" NOTNULL AND "discussion_time" NOTNULL AND

15 "verification_time" NOTNULL AND "voting_time" NOTNULL ) OR

16 ( "polling" = TRUE AND

17 "admission_time" ISNULL AND "discussion_time" NOTNULL AND

18 "verification_time" NOTNULL AND "voting_time" NOTNULL ) OR

19 ( "polling" = TRUE AND

20 "admission_time" ISNULL AND "discussion_time" ISNULL AND

21 "verification_time" ISNULL AND "voting_time" ISNULL ) );

22 COMMENT ON COLUMN "policy"."polling" IS 'TRUE = special policy for non-user-generated issues, i.e. polls ("admission_time" MUST be set to NULL, the other timings may be set to NULL altogether, allowing individual timing for issues)';

23

24 ALTER TABLE "initiative" ADD COLUMN "polling" BOOLEAN NOT NULL DEFAULT FALSE;

25 COMMENT ON COLUMN "initiative"."polling" IS 'Initiative is an option for a poll (see "policy"."polling"), and does not need to pass the initiative quorum';

26

27 ALTER TABLE "privilege" RENAME COLUMN "voting_right_manager" TO "member_manager";

28 ALTER TABLE "privilege" ADD COLUMN "initiative_right" BOOLEAN NOT NULL DEFAULT TRUE;

29 ALTER TABLE "privilege" ADD COLUMN "polling_right" BOOLEAN NOT NULL DEFAULT FALSE;

30 UPDATE "privilege" SET "initiative_right" = "voting_right";

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

32 COMMENT ON COLUMN "privilege"."member_manager" IS 'Adding/removing members from the unit, granting or revoking "initiative_right" and "voting_right"';

33 COMMENT ON COLUMN "privilege"."initiative_right" IS 'Right to create an initiative';

34 COMMENT ON COLUMN "privilege"."voting_right" IS 'Right to support initiatives, create and rate suggestions, and to vote';

35 COMMENT ON COLUMN "privilege"."polling_right" IS 'Right to create polls (see "policy"."polling" and "initiative"."polling")';

36

37 DROP TABLE "rendered_issue_comment";

38 DROP TABLE "issue_comment";

39

40 CREATE FUNCTION "non_voter_deletes_direct_voter_trigger"()

41 RETURNS TRIGGER

42 LANGUAGE 'plpgsql' VOLATILE AS $$

43 BEGIN

44 DELETE FROM "direct_voter"

45 WHERE "issue_id" = NEW."issue_id" AND "member_id" = NEW."member_id";

46 RETURN NULL;

47 END;

48 $$;

49

50 CREATE TRIGGER "non_voter_deletes_direct_voter"

51 AFTER INSERT OR UPDATE ON "non_voter"

52 FOR EACH ROW EXECUTE PROCEDURE

53 "non_voter_deletes_direct_voter_trigger"();

54

55 COMMENT ON FUNCTION "non_voter_deletes_direct_voter_trigger"() IS 'Implementation of trigger "non_voter_deletes_direct_voter" on table "non_voter"';

56 COMMENT ON TRIGGER "non_voter_deletes_direct_voter" ON "non_voter" IS 'An entry in the "non_voter" table deletes an entry in the "direct_voter" table (and vice versa due to trigger "direct_voter_deletes_non_voter" on table "direct_voter")';

57

58 CREATE FUNCTION "direct_voter_deletes_non_voter_trigger"()

59 RETURNS TRIGGER

60 LANGUAGE 'plpgsql' VOLATILE AS $$

61 BEGIN

62 DELETE FROM "non_voter"

63 WHERE "issue_id" = NEW."issue_id" AND "member_id" = NEW."member_id";

64 RETURN NULL;

65 END;

66 $$;

67

68 CREATE TRIGGER "direct_voter_deletes_non_voter"

69 AFTER INSERT OR UPDATE ON "direct_voter"

70 FOR EACH ROW EXECUTE PROCEDURE

71 "direct_voter_deletes_non_voter_trigger"();

72

73 COMMENT ON FUNCTION "direct_voter_deletes_non_voter_trigger"() IS 'Implementation of trigger "direct_voter_deletes_non_voter" on table "direct_voter"';

74 COMMENT ON TRIGGER "direct_voter_deletes_non_voter" ON "direct_voter" IS 'An entry in the "direct_voter" table deletes an entry in the "non_voter" table (and vice versa due to trigger "non_voter_deletes_direct_voter" on table "non_voter")';

75

76 CREATE OR REPLACE FUNCTION "freeze_after_snapshot"

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

78 RETURNS VOID

79 LANGUAGE 'plpgsql' VOLATILE AS $$

80 DECLARE

81 "issue_row" "issue"%ROWTYPE;

82 "policy_row" "policy"%ROWTYPE;

83 "initiative_row" "initiative"%ROWTYPE;

84 BEGIN

85 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";

86 SELECT * INTO "policy_row"

87 FROM "policy" WHERE "id" = "issue_row"."policy_id";

88 PERFORM "set_snapshot_event"("issue_id_p", 'full_freeze');

89 FOR "initiative_row" IN

90 SELECT * FROM "initiative"

91 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL

92 LOOP

93 IF

94 "initiative_row"."polling" OR (

95 "initiative_row"."satisfied_supporter_count" > 0 AND

96 "initiative_row"."satisfied_supporter_count" *

97 "policy_row"."initiative_quorum_den" >=

98 "issue_row"."population" * "policy_row"."initiative_quorum_num"

99 )

100 THEN

101 UPDATE "initiative" SET "admitted" = TRUE

102 WHERE "id" = "initiative_row"."id";

103 ELSE

104 UPDATE "initiative" SET "admitted" = FALSE

105 WHERE "id" = "initiative_row"."id";

106 END IF;

107 END LOOP;

108 IF EXISTS (

109 SELECT NULL FROM "initiative"

110 WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE

111 ) THEN

112 UPDATE "issue" SET

113 "state" = 'voting',

114 "accepted" = coalesce("accepted", now()),

115 "half_frozen" = coalesce("half_frozen", now()),

116 "fully_frozen" = now()

117 WHERE "id" = "issue_id_p";

118 ELSE

119 UPDATE "issue" SET

120 "state" = 'canceled_no_initiative_admitted',

121 "accepted" = coalesce("accepted", now()),

122 "half_frozen" = coalesce("half_frozen", now()),

123 "fully_frozen" = now(),

124 "closed" = now(),

125 "ranks_available" = TRUE

126 WHERE "id" = "issue_id_p";

127 -- NOTE: The following DELETE statements have effect only when

128 -- issue state has been manipulated

129 DELETE FROM "direct_voter" WHERE "issue_id" = "issue_id_p";

130 DELETE FROM "delegating_voter" WHERE "issue_id" = "issue_id_p";

131 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";

132 END IF;

133 RETURN;

134 END;

135 $$;

136

137 CREATE OR REPLACE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE)

138 RETURNS VOID

139 LANGUAGE 'plpgsql' VOLATILE AS $$

140 DECLARE

141 "issue_row" "issue"%ROWTYPE;

142 BEGIN

143 SELECT * INTO "issue_row"

144 FROM "issue" WHERE "id" = "issue_id_p"

145 FOR UPDATE;

146 IF "issue_row"."cleaned" ISNULL THEN

147 UPDATE "issue" SET

148 "state" = 'voting',

149 "closed" = NULL,

150 "ranks_available" = FALSE

151 WHERE "id" = "issue_id_p";

152 DELETE FROM "voting_comment"

153 WHERE "issue_id" = "issue_id_p";

154 DELETE FROM "delegating_voter"

155 WHERE "issue_id" = "issue_id_p";

156 DELETE FROM "direct_voter"

157 WHERE "issue_id" = "issue_id_p";

158 DELETE FROM "delegating_interest_snapshot"

159 WHERE "issue_id" = "issue_id_p";

160 DELETE FROM "direct_interest_snapshot"

161 WHERE "issue_id" = "issue_id_p";

162 DELETE FROM "delegating_population_snapshot"

163 WHERE "issue_id" = "issue_id_p";

164 DELETE FROM "direct_population_snapshot"

165 WHERE "issue_id" = "issue_id_p";

166 DELETE FROM "non_voter"

167 WHERE "issue_id" = "issue_id_p";

168 DELETE FROM "delegation"

169 WHERE "issue_id" = "issue_id_p";

170 DELETE FROM "supporter"

171 WHERE "issue_id" = "issue_id_p";

172 UPDATE "issue" SET

173 "state" = "issue_row"."state",

174 "closed" = "issue_row"."closed",

175 "ranks_available" = "issue_row"."ranks_available",

176 "cleaned" = now()

177 WHERE "id" = "issue_id_p";

178 END IF;

179 RETURN;

180 END;

181 $$;

182

183 COMMENT ON FUNCTION "delete_private_data"() IS 'Used by lf_export script. DO NOT USE on productive database, but only on a copy! This function deletes all data which should not be publicly available, and can be used to create a database dump for publication. See source code to see which data is deleted. If you need a different behaviour, copy this function and modify lf_export accordingly, to avoid data-leaks after updating.';

184

185 COMMIT;