1 BEGIN;

2

3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS

4 SELECT * FROM (VALUES ('2.2.6', 2, 2, 6))

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

6

7 CREATE TABLE "issue_order" (

8 "id" INT8 PRIMARY KEY REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,

9 "order_in_admission_state" INT4,

10 "order_in_open_states" INT4 );

11

12 COMMENT ON TABLE "issue_order" IS 'Ordering information for issues that are not stored in the "issue" table to avoid locking of multiple issues at once';

13

14 COMMENT ON COLUMN "issue_order"."order_in_admission_state" IS 'To be used for sorting issues within an area, when showing only issues in admission state; NULL values sort last; updated by "lf_update_issue_order"';

15 COMMENT ON COLUMN "issue_order"."order_in_open_states" IS 'To be used for sorting issues within an area, when showing all open issues; NULL values sort last; updated by "lf_update_issue_order"';

16

17 CREATE VIEW "issue_supporter_in_admission_state" AS

18 SELECT DISTINCT

19 "issue"."area_id",

20 "issue"."id" AS "issue_id",

21 "supporter"."member_id",

22 "direct_interest_snapshot"."weight"

23 FROM "issue"

24 JOIN "supporter" ON "supporter"."issue_id" = "issue"."id"

25 JOIN "direct_interest_snapshot"

26 ON "direct_interest_snapshot"."issue_id" = "issue"."id"

27 AND "direct_interest_snapshot"."event" = "issue"."latest_snapshot_event"

28 AND "direct_interest_snapshot"."member_id" = "supporter"."member_id"

29 WHERE "issue"."state" = 'admission'::"issue_state";

30

31 COMMENT ON VIEW "issue_supporter_in_admission_state" IS 'Helper view for "lf_update_issue_order" to allow a (proportional) ordering of issues within an area';

32

33 CREATE VIEW "open_issues_ordered_with_minimum_position" AS

34 SELECT

35 "area_id",

36 "id" AS "issue_id",

37 "order_in_admission_state" * 2 - 1 AS "minimum_position"

38 FROM "issue" NATURAL LEFT JOIN "issue_order"

39 WHERE "closed" ISNULL

40 ORDER BY

41 coalesce(

42 "fully_frozen" + "voting_time",

43 "half_frozen" + "verification_time",

44 "accepted" + "discussion_time",

45 "created" + "admission_time"

46 ) - now();

47

48 COMMENT ON VIEW "open_issues_ordered_with_minimum_position" IS 'Helper view for "lf_update_issue_order" to allow a (mixed) ordering of issues within an area';

49

50 COMMIT;