| rev |
line source |
|
jbe@396
|
1 BEGIN;
|
|
jbe@396
|
2
|
|
jbe@396
|
3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
|
|
jbe@396
|
4 SELECT * FROM (VALUES ('2.2.6', 2, 2, 6))
|
|
jbe@396
|
5 AS "subquery"("string", "major", "minor", "revision");
|
|
jbe@396
|
6
|
|
jbe@396
|
7 ALTER TABLE "issue" ADD COLUMN "order_in_admission_state" INT4;
|
|
jbe@396
|
8 ALTER TABLE "issue" ADD COLUMN "order_in_open_states" INT4;
|
|
jbe@396
|
9
|
|
jbe@396
|
10 COMMENT ON COLUMN "issue"."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"';
|
|
jbe@396
|
11 COMMENT ON COLUMN "issue"."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"';
|
|
jbe@396
|
12
|
|
jbe@396
|
13 CREATE VIEW "issue_supporter_in_admission_state" AS
|
|
jbe@396
|
14 SELECT DISTINCT
|
|
jbe@396
|
15 "issue"."area_id",
|
|
jbe@396
|
16 "issue"."id" AS "issue_id",
|
|
jbe@396
|
17 "supporter"."member_id",
|
|
jbe@396
|
18 "direct_interest_snapshot"."weight"
|
|
jbe@396
|
19 FROM "issue"
|
|
jbe@396
|
20 JOIN "supporter" ON "supporter"."issue_id" = "issue"."id"
|
|
jbe@396
|
21 JOIN "direct_interest_snapshot"
|
|
jbe@396
|
22 ON "direct_interest_snapshot"."issue_id" = "issue"."id"
|
|
jbe@396
|
23 AND "direct_interest_snapshot"."event" = "issue"."latest_snapshot_event"
|
|
jbe@396
|
24 AND "direct_interest_snapshot"."member_id" = "supporter"."member_id"
|
|
jbe@396
|
25 WHERE "issue"."state" = 'admission'::"issue_state";
|
|
jbe@396
|
26
|
|
jbe@396
|
27 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';
|
|
jbe@396
|
28
|
|
jbe@397
|
29 CREATE VIEW "open_issues_ordered_with_minimum_position" AS
|
|
jbe@397
|
30 SELECT
|
|
jbe@397
|
31 "area_id",
|
|
jbe@397
|
32 "id" AS "issue_id",
|
|
jbe@397
|
33 "order_in_admission_state" * 2 - 1 AS "minimum_position"
|
|
jbe@397
|
34 FROM "issue"
|
|
jbe@397
|
35 WHERE "closed" ISNULL
|
|
jbe@397
|
36 ORDER BY
|
|
jbe@397
|
37 coalesce(
|
|
jbe@397
|
38 "fully_frozen" + "voting_time",
|
|
jbe@397
|
39 "half_frozen" + "verification_time",
|
|
jbe@397
|
40 "accepted" + "discussion_time",
|
|
jbe@397
|
41 "created" + "admission_time"
|
|
jbe@397
|
42 ) - now();
|
|
jbe@397
|
43
|
|
jbe@397
|
44 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';
|
|
jbe@397
|
45
|
|
jbe@396
|
46 COMMIT;
|