jbe@396: BEGIN;
jbe@396: 
jbe@396: CREATE OR REPLACE VIEW "liquid_feedback_version" AS
jbe@396:   SELECT * FROM (VALUES ('2.2.6', 2, 2, 6))
jbe@396:   AS "subquery"("string", "major", "minor", "revision");
jbe@396: 
jbe@398: CREATE TABLE "issue_order" (
jbe@400:         "id"                    INT8            PRIMARY KEY, --REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@398:         "order_in_admission_state" INT4,
jbe@398:         "order_in_open_states"  INT4 );
jbe@396: 
jbe@398: 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';
jbe@398: 
jbe@400: COMMENT ON COLUMN "issue_order"."id"                       IS 'References "issue" ("id") but has no referential integrity trigger associated, due to performance/locking issues';
jbe@398: 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"';
jbe@398: 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"';
jbe@396: 
jbe@396: CREATE VIEW "issue_supporter_in_admission_state" AS
jbe@396:   SELECT DISTINCT
jbe@396:     "issue"."area_id",
jbe@396:     "issue"."id" AS "issue_id",
jbe@396:     "supporter"."member_id",
jbe@396:     "direct_interest_snapshot"."weight"
jbe@396:   FROM "issue"
jbe@396:   JOIN "supporter" ON "supporter"."issue_id" = "issue"."id"
jbe@396:   JOIN "direct_interest_snapshot"
jbe@396:     ON  "direct_interest_snapshot"."issue_id" = "issue"."id"
jbe@396:     AND "direct_interest_snapshot"."event" = "issue"."latest_snapshot_event"
jbe@396:     AND "direct_interest_snapshot"."member_id" = "supporter"."member_id"
jbe@396:   WHERE "issue"."state" = 'admission'::"issue_state";
jbe@396: 
jbe@396: 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: 
jbe@397: CREATE VIEW "open_issues_ordered_with_minimum_position" AS
jbe@397:   SELECT
jbe@397:     "area_id",
jbe@397:     "id" AS "issue_id",
jbe@397:     "order_in_admission_state" * 2 - 1 AS "minimum_position"
jbe@398:   FROM "issue" NATURAL LEFT JOIN "issue_order"
jbe@397:   WHERE "closed" ISNULL
jbe@397:   ORDER BY
jbe@397:     coalesce(
jbe@397:       "fully_frozen" + "voting_time",
jbe@397:       "half_frozen" + "verification_time",
jbe@397:       "accepted" + "discussion_time",
jbe@397:       "created" + "admission_time"
jbe@397:     ) - now();
jbe@397: 
jbe@397: 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: 
jbe@396: COMMIT;